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.

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.