I must thank my fellow DBA César Aguilar and Joel Pérez for his publication in Spanish OTN.
Continuing with the sequence of Oracle Data Guard 12c articles in “Multitenant” Architecture: PDBs behavior, we will end the series by analyzing what happens in the Data Guard “DG” when we delete and when we do an “unplug” of a PDB in the primary CDB.
We will propose the following objectives and scenarios:
Scenario 1: Delete the PDB1 on the primary CDB1 with “DG” configured.
Scenario 2: Unplug the PDB2 of the primary CDB1 with “DG” configured.
Usage Tool: SQL * Plus
Our test environment:
- The primary CDB1 with “DG” configured has four pluggables: PDB1, PDB2, PDB_SERA and PDB_FINA.
- The standby CDB1 that forms part of the “DG”.
Scenario 1: Delete PDB1 from the primary CDB1.
We entered the CDB1 of the DG.
[Oracle @ rfcg ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 Production on Sat Apr 8 11:42:57 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name, cdb from v $ database; NAME CDB (I.e. CDB1 YES SQL> select database_role from v $ database; DATABASE_ROLE ---------------- PRIMARY
We list the PDBs of the primary CDB1.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB_SERA READ WRITE NO 6 PDB_FINA READ WRITE NO
We proceed to delete the PDB1.
SQL> alter pluggable database pdb1 close; Pluggable database altered. SQL> drop pluggable database pdb1 including datafiles; Pluggable database dropped.
We verified that the PDB1 was deleted.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 4 PDB2 READ WRITE NO 5 PDB_SERA READ WRITE NO 6 PDB_FINA READ WRITE NO
We enter the standby server of the “DG” where we have the standby CDB1.
[Oracle @ rfcg2 ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg2 ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 Production on Sat Apr 8 11: 49: 182017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select status, instance_namefromv $ instance; STATUS INSTANCE_NAME ------------ ---------------- MOUNTED cdb1 SQL> select database_role from v $ database; DATABASE_ROLE ---------------- PHYSICAL STANDBY
We verify when listing the PDBs of our standby server that the PDB1 no longer exists as part of the “DG” solution.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED MOUNTED 4 PDB2 MOUNTED 5 PDB_SERA MOUNTED 6 PDB_FINA MOUNTED
Deleting the PDB1 in the primary CDB1 with the option “including datafiles” the datafiles were deleted in the servers: Primary and “Standby”
Scenario 2: “Unplug” the PDB2 of the primary CDB1.
We proceed to disconnect the PDB2 from the primary CDB1.
[Oracle @ rfcg ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 Production on Sat Apr 8 11:53:36 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 4 PDB2 READ WRITE NO 5 PDB_SERA READ WRITE NO 6 PDB_FINA READ WRITE NO SQL> alter pluggable database PDB2 close immediate; Pluggable database altered. SQL> alter pluggable database PDB2 UNPLUG INTO '/home/oracle/pdb2.xml'; Pluggable database altered.
We list the PDBs, as we can see the PDB2 is in “MOUNTED” mode.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 4 PDB2 MOUNTED 5 PDB_SERA READ WRITE NO 6 PDB_FINA READ WRITE NO
We will enter the CDB1standby to demonstrate that the datafiles of PDB2 remain.
[Oracle @ rfcg2 ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg2 ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 Production on Sat Apr 8 11: 59: 352017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select status, instance_name from v $ instance; STATUS INSTANCE_NAME ------------ ---------------- MOUNTED cdb1 SQL> select database_role fromv $ database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED MOUNTED 4 PDB2 MOUNTED 5 PDB_SERA MOUNTED 6 PDB_FINA MOUNTED SQL> alter session set container = pdb2; Session altered. SQL> select name, status, enabled from v $ datafile; NAME STATUS ENABLED -------------------------------------------------- --------------- -------- /u01/app/oracle/oradata/CDB1/datafile/pdb2/pdb2_system.dbfSYSTEM READ WRITE /u01/app/oracle/oradata/CDB1/datafile/pdb2/pdb2_sysaux.dbfRECOVER UNKNOWN /u01/app/oracle/oradata/CDB1/datafile/o1_mf_usersdbfRECOVER UNKNOWN
When performing the unplug of the PDB2 on the primary CDB1, the datafiles of the PDB2 are maintained in the primary CDB1 and in the standby similar to the operation of deleting a PDB with the “keep datafiles” option.
The datafiles of a PDB that has been “unplugged” will be present since they should be copied to a destination where the “plug” would be made, these could be deleted later if they are not going to be used by deleting the PDB that has been “unplugged “.
Conclusion:
In this series of articles we analyzed different behaviors of the PDBs in a DG solution, we could see that under the multitenant architecture of Oracle 12c the PDBs associated to the primary CDB have different behaviors within the DG depending on the way they are created, connected, Deleted and disconnected.
