Is your PDB state saved?
This might save you a couple of minutes if you wonder how multitenant works in combination with Oracle Data Guard.
The question I got was, is your PDB state saved after a switchover? The short answer is: Yes it is.
No statements without proving them of course, so here we go.
The primary database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
13:54:30 SQL> select INSTANCE_NAME,HOST_NAME,database_role from v$instance, v$database; INSTANCE_NAME HOST_NAME DATABASE_ROLE ---------------- ---------------------------------------------------------------- ---------------- dgdemovm1 oelvm1.localdomain PRIMARY 13:54:46 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MYDGPDB READ WRITE NO 13:54:58 SQL> |
I have the state of my pub always saved in my post creation script, but you can easily retrieve in what state the PDB will be brought up when the database starts
|
1 2 3 4 5 6 7 |
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states; CON_NAME INSTANCE_NAME STATE -------------------- -------------------- -------------- MYDGPDB dgdemovm1 OPEN SQL> |
So this PDB in the primary database will automatically be put in OPEN mode when the database starts. I confirm, it does 😉
The standby database, is a simple physical standby database in mount mode
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
13:54:26 SQL> select INSTANCE_NAME,HOST_NAME,database_role from v$instance, v$database; INSTANCE_NAME HOST_NAME DATABASE_ROLE ---------------- ---------------------------------------------------------------- ---------------- dgdemovm2 oelvm2.localdomain PHYSICAL STANDBY 13:54:50 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 MYDGPDB MOUNTED 13:55:01 SQL> |
The database is in mount, so that also means we cannot query the dba_pdb_saved_states view.
Then a role switch is performed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DGMGRL> switchover to dgdemovm2 Performing switchover NOW, please wait... Operation requires a connection to database "dgdemovm2" Connecting ... Connected to "dgdemovm2" Connected as SYSDBA. New primary database "dgdemovm2" is opening... Operation requires start up of instance "dgdemovm1" on database "dgdemovm1" Starting instance "dgdemovm1"... Connected to an idle instance. ORACLE instance started. Database mounted. Connected to "dgdemovm1" Switchover succeeded, new primary is "dgdemovm2" DGMGRL> |
Quickly check it
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DGMGRL> show configuration Configuration - dgdemo Protection Mode: MaxPerformance Members: dgdemovm2 - Primary database dgdemovm1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 73 seconds ago) DGMGRL> |
And indeed, the roles are reversed, so now we can check if the PDB is open or mounted. First of all, the second VM, it was the former standby, now the new primary:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
13:57:22 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MYDGPDB READ WRITE NO 13:57:26 SQL> select INSTANCE_NAME,HOST_NAME,database_role from v$instance, v$database; INSTANCE_NAME HOST_NAME DATABASE_ROLE ---------------- ---------------------------------------------------------------- ---------------- dgdemovm2 oelvm2.localdomain PRIMARY 13:57:33 SQL> |
And the PDB is just open in read/write mode. So the old primary, the new standby, we expect the PDB to be mounted:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
13:57:22 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 MYDGPDB MOUNTED 13:57:26 SQL> select INSTANCE_NAME,HOST_NAME,database_role from v$instance, v$database; INSTANCE_NAME HOST_NAME DATABASE_ROLE ---------------- ---------------------------------------------------------------- ---------------- dgdemovm1 oelvm1.localdomain PHYSICAL STANDBY 13:57:33 SQL> |
And it works like expected.
In short, when you reverse the role of the database, the state of the PDB is preserved for the primary database.
As always, questions, remarks? find me on twitter @vanpupi