In a previous post we had seen how to setup and create an Oracle 12c Data Guard Physical Standby Database.
Remember Data Guard is set up at the CONTAINER database level and not at the PLUGGABLE database level.
In this example we will see how we can simply unplug a database from a non Data Guard container database and plug it into the container database where Data Guard has been configured and automatically the pluggable database will become part of a highly available environment.
Before 12c if we had 10 databases we needed to setup Data Guard for we would need to go through all the Data Guard setup procedures ten times, have 10 separate Data Guard Broker configurations and so on.
Here in Oracle 12c we setup and configure Data Guard just once at the container database level and as and when we need to have a database to become part of this highly available environment we just plug it to the container database and we are good to go.
So in our previous example we had a container database where we had set up Data Guard called CONDB1 and a pluggable database PDB1.
Now we have another pluggable database PDB2 part of a non Data Guard container database CONDB2 and we need to move it into the existing Data Guard container database CONDB1.
The first thing we need to do is to unplug the pluggable database PDB2 from it’s current container and plug it in to CONDB1.
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB2 READ WRITE SQL> alter pluggable database pdb2 close immediate; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB2 MOUNTED SQL> alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml'; Pluggable database altered.
We now need to copy the pluggable database PDB2 files to the standby site.
[oracle@orasql-001-dev ~]$ cd /u01/app/oracle/oradata/condb2/pdb2/ [oracle@orasql-001-dev pdb2]$ ls -lrt total 1319140 -rw-r----- 1 oracle oinstall 91234304 Nov 8 08:48 pdb2_temp01.dbf -rw-r----- 1 oracle oinstall 283123712 Nov 8 09:30 system01.dbf -rw-r----- 1 oracle oinstall 723525632 Nov 8 09:30 sysaux01.dbf -rw-r----- 1 oracle oinstall 5251072 Nov 8 09:30 SAMPLE_SCHEMA_users01.dbf -rw-r----- 1 oracle oinstall 338829312 Nov 8 09:30 example01.dbf [oracle@orasql-001-dev pdb2]$ cd .. [oracle@orasql-001-dev condb2]$ scp -rp pdb2 oracle@orasql-001-test:/u01/app/oracle/oradata/condb1 oracle@orasql-001-test's password: example01.dbf 100% 323MB 35.9MB/s 00:09 pdb2_temp01.dbf 100% 87MB 29.0MB/s 00:03 SAMPLE_SCHEMA_users01.dbf 100% 5128KB 5.0MB/s 00:00 system01.dbf 100% 270MB 38.6MB/s 00:07 sysaux01.dbf
Now plug the unplugged database PDB2 into the container database CONDB1.
Note that since the directory structures in the two container databases is different, when we plug in the database we have to use the FILE_NAME_CONVERT parameter.
[oracle@orasql-001-dev condb1]$ echo $ORACLE_SID
condb1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database pdb2
2 using '/home/oracle/pdb2.xml'
3 copy
4 file_name_convert=('/u01/app/oracle/oradata/condb2/pdb2/','/u01/app/oracle/oradata/condb1/pdb2/');
Pluggable database created.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 MOUNTED
SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;
PDB_NAME DBID CON_ID STATUS
--------------- ---------- ---------- -------------
PDB1 3338455196 1 NORMAL
PDB$SEED 4073382782 1 NORMAL
PDB2 3897194249 1 NEW
After plugging in the database, open it in read write mode
SQL> conn / as sysdba Connected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter pluggable database pdb2 open read write; Pluggable database altered. SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS; PDB_NAME DBID CON_ID STATUS --------------- ---------- ---------- ------------- PDB1 3338455196 1 NORMAL PDB$SEED 4073382782 1 NORMAL PDB2 3897194249 1 NORMAL
When we connect to the standby site, we can see that the PDB2 database is now in a mount state. We can open it in read only mode if this is part of an Active Standby configuration.
SQL> select process,status,thread#,sequence#,blocks from v$managed_standby where process like '%MRP%'; PROCESS STATUS THREAD# SEQUENCE# BLOCKS --------- ------------ ---------- ---------- ---------- MRP0 APPLYING_LOG 1 38 102400 SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 READ ONLY PDB2 MOUNTED SQL> alter pluggable database all open read only; Pluggable database altered. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 READ ONLY PDB2 READ ONLY
Let us now test this.
We connect to PDB2 and create a test table and populate it with some rows. We will then connect to the standby site and see if the changes have been propagated across.
Note – we have not done any Data Guard set up explicitly for the pluggable database PDB2. It has inherited the Data Guard configuration when we plugged it into the container database CONDB1 where data guard had been setup.
Connect to Primary ….
[oracle@orasql-001-dev condb1]$ sqlplus sh/sh@localhost:1525/pdb2
SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 8 10:02:12 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sat May 25 2013 04:25:15 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create table customers_dr as select * from customers;
Table created.
SQL> select count(*) from customers_dr;
COUNT(*)
----------
55500
Connect to standby and confirm ….
[oracle@orasql-001-test condb1]$ sqlplus sh/sh@localhost:1523/pdb2
SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 8 10:03:14 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Nov 08 2013 10:02:12 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from customers_dr;
COUNT(*)
----------
55500
