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?

oracle@rac1node1:~/ [RACCDB1] sqplplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 8 18:18:25 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 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.

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

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.