Oracle 12c: RAC, Pluggable Databases und Services

8. Dezember 2014 Aus Von Markus Flechtner

In einer Oracle 12c Multitenant Datenbank werden die Pluggable Databases (PDBs) standardmäßig nicht automatisch gestartet. in 12.1.0.1 kann man sich da mit einem After-Startup-Of-Database-Trigger behelfen („ALTER PLUGGABLE DATABASE ALL OPEN;“). Mit dem Patchset 12.1.0.2 kann man den Status einer PDB sichern („ALTER PLUGGABLE DATABASE SAVE STATE;“) und dieser Status wird nach einem Neustart der CDB wiederhergestellt. Im RAC kann man eine PDB auch mithilfe von Datenbank-Services starten, die in der Grid Infrastruktur definiert sind, obwohl die PDB keine Clusterresource ist.

Unsere Testumgebung ist ein Zwei-Knoten-RAC (Knoten: rac1node1 und rac1node2), einer Datenbank RACCDB (Instanzen RACCDB1 und RACCDB2) sowie den beiden PDBs RACPDB1 und RACPDB2.

Unser Ausgangszustand sieht wie folgt aus:

SQL> select inst_id,name,open_mode from gv$pdbs where name='RACPDB1' order by inst_id; INST_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 1 RACPDB1 MOUNTED 2 RACPDB1 MOUNTED
==> die PDB ist auf keiner der beiden Instanzen geöffnet

oracle@rac1node1:~/ [RACCDB1] srvctl status service -d RACCDB oracle@rac1node1:~/ [RACCDB1]
==> es gibt keine Applikations-Services für die Cluster-Datenbank RACCDB

oracle@rac1node1:~/ [RACCDB1] lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-DEC-2014 18:09:03 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 08-DEC-2014 17:28:55 Uptime 0 days 0 hr. 40 min. 8 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u00/app/grid/product/12.1.0.2/network/admin/listener.ora Listener Log File /u00/app/oracle/diag/tnslsnr/rac1node1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.111)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.112)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1node1.markusflechtner.local)(PORT=5510))(Security=(my_wallet_directory=/u00/app/oracle/product/12.1.0.2/admin/RACCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "RACCDB.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "RACCDBXDB.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "racpdb1.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "racpdb2.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... The command completed successfully

==> auf Listener-Ebene gibt es nur den Default-Service der PDBs (obwohl die PDBS nicht geöffnet sind)

Über diesen Service können wir uns bei der PDB anmelden, um sie zu öffnen oder zu schließen:

oracle@rac1node1:~/ [RACCDB1] sqlplus sys/manager@rac1node2:1521/racpdb1.markusflechtner.local as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 8 18:12:00 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name
CON_NAME
——————————
RACPDB1
SQL> alter database open;
Database altered.

SQL> shutdown immediate;
Pluggable Database closed.

Jetzt legen wir einen Service für die PDB RACPDB1 an:

oracle@rac1node1:~/ [RACCDB1] srvctl add service -db RACCDB -pdb racpdb1 -service racpdb1_appl -failovertype select -failovermethod basic -tafpolicy basic -preferred RACCDB1 -available RACCDB2 oracle@rac1node1:~/ [RACCDB1] srvctl config service -db RACCDB -service racpdb1_appl Service name: racpdb1_appl Server pool: Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Failover type: SELECT Failover method: BASIC TAF failover retries: TAF failover delay: Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: racpdb1 Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Replay Initiation Time: 300 seconds Session State Consistency: GSM Flags: 0 Service is enabled Preferred instances: RACCDB1 Available instances: RACCDB2 oracle@rac1node1:~/ [RACCDB1] srvctl status service -db RACCDB -service racpdb1_appl Service racpdb1_appl is not running.

oracle@rac1node1:~/ [RACCDB1] srvctl start service -db RACCDB -service racpdb1_appl
oracle@rac1node1:~/ [RACCDB1] srvctl status service -db RACCDB -service racpdb1_appl
Service racpdb1_appl is running on instance(s) RACCDB1

Wie ist jetzt der Zustand unserer PDB RACPDB1?

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select inst_id,name,open_mode from gv$pdbs where name=’RACPDB1′ order by inst_id;
INST_ID NAME OPEN_MODE
———- —————————— ———-
1 RACPDB1 READ WRITE
2 RACPDB1 MOUNTED

Und der Service ist auch beim Listener registriert (in 12c macht dies der LREG-Hintergrundprozess)

oracle@rac1node1:~/ [RACCDB1] lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-DEC-2014 18:21:22 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 08-DEC-2014 17:28:55 Uptime 0 days 0 hr. 52 min. 27 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u00/app/grid/product/12.1.0.2/network/admin/listener.ora Listener Log File /u00/app/oracle/diag/tnslsnr/rac1node1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.111)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.112)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac1node1.markusflechtner.local)(PORT=5510))(Security=(my_wallet_directory=/u00/app/oracle/product/12.1.0.2/admin/RACCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "RACCDB.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "RACCDBXDB.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "racpdb1.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "racpdb1_appl.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... Service "racpdb2.markusflechtner.local" has 1 instance(s). Instance "RACCDB1", status READY, has 1 handler(s) for this service... The command completed successfully

Das alert.log zeigt, dass die PDB durch den Agenten der Clusterware geöffnet wurde

Mon Dec 08 18:18:01 2014 ALTER PLUGGABLE DATABASE racpdb1 OPEN /* svc agent *//* {1:11542:1601} */ Mon Dec 08 18:18:01 2014 This instance was first to open pluggable database RACPDB1 (container=3) Database Characterset for RACPDB1 is WE8MSWIN1252 Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32 Opening pdb RACPDB1 (3) with no Resource Manager plan active Pluggable database RACPDB1 opened read write Completed: ALTER PLUGGABLE DATABASE racpdb1 OPEN /* svc agent *//* {1:11542:1601} */

Was passiert, wenn wir den Service auf den anderen Knoten verschieben?

oracle@rac1node1:~/ [RACCDB1] srvctl relocate service -d RACCDB -s racpdb1_appl -oldinst RACCDB1 -newinst RACCDB2oracle@rac1node1:~/ [RACCDB1] srvctl status service -d RACCDB Service racpdb1_appl is running on instance(s) RACCDB2

oracle@rac1node1:~/ [RACCDB1] sqlplus „/ as sysdba“
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 8 18:22:55 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select inst_id,name,open_mode from gv$pdbs where name=’RACPDB1′ order by inst_id;
INST_ID NAME OPEN_MODE
———- —————————— ———-
1 RACPDB1 READ WRITE
2 RACPDB1 READ WRITE

Jetzt ist die PDB also auf beiden Instanzen/Knoten geöffnet, obwohl der Service nur auf dem 2.Knoten läuft.

Was passiert, wenn wir den Service stoppen?
oracle@rac1node1:~/ [RACCDB1] srvctl stop service -d RACCDB -s RACPDB1_APPL oracle@rac1node1:~/ [RACCDB1] srvctl status service -d RACCDB Service racpdb1_appl is not running. oracle@rac1node1:~/ [RACCDB1] sqlplus "/ as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 8 18:23:37 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> select inst_id,name,open_mode from gv$pdbs where name=’RACPDB1′ order by inst_id;
INST_ID NAME OPEN_MODE
———- —————————— ———-
1 RACPDB1 READ WRITE
2 RACPDB1 READ WRITE

In den Eigenschaften der Cluster-Resource ist die PDB RACPDB1 mit vermerkt; allerdings nicht bei den Abhängigkeiten:

oracle@rac1node1:~/ [grid12102] crsctl status resource ora.raccdb.racpdb1_appl.svc -p NAME=ora.raccdb.racpdb1_appl.svc TYPE=ora.service.type .. .. PLUGGABLE_DATABASE=racpdb1 .. SERVER_POOLS=ora.RACCDB_racpdb1_appl SERVICE_NAME=racpdb1_appl .. START_DEPENDENCIES=hard(ora.raccdb.db,type:ora.cluster_vip_net1.type) weak(type:ora.listener.type) pullup(type:ora.cluster_vip_net1.type) pullup:always(ora.raccdb.db) .. STOP_DEPENDENCIES=hard(intermediate:ora.raccdb.db,type:ora.cluster_vip_net1.type) ..

Zusammengefasst:

Auch wenn die Pluggable Databases keine Cluster-Resource sind, wird die Verbindung „CDB -> PDB -> Service“ beim Anlegen eines Services hergestellt und in der OCR vermerkt. Die Clusterware sorgt dafür, dass eine PDB beim Starten eines zugehörigen Service auch geöffnet wird. Es ist auch nachvollziehbar, dass die Clusterware eine PDB beim Umzug des Service auf einen anderen Knoten oder beim Stoppen des Service nicht schließt, denn es kann ja andere DB-Sessions geben, die mit der PDB arbeiten und die nicht über einen von der Clusterware kontrollierten Service mit der PDB verbunden sind.

KategorieMultitenant RAC