I must thank my fellow DBA César Aguilar and Joel Pérez for his publication in Spanish OTN.
In previous article: Oracle Data Guard 12c in “Multitenant” Architecture: Behavior of PDBs (Part I) we observed how the process of configuration Data Guard 12c in multitenant architecture is reduced, simplifying thus habitual tasks of the previous versions to 12c, we had a Scenario 1 in which we proceeded to create a new plug-in database PDB_SERA in CDB1 and checked how it automatically was part of the configuration.
In the following article, we will continue analyzing the behavior of pluggable databases in Oracle Data Guard12c and the respective advantages of these in a multitenant architecture.
We will propose the following objective and scenario:
Scenario 1:
“Unplug” a PDB_FINA pluggable database from a CDB2 container database in which there is no Data Guard configuration, and then plug it into the container database CDB1 with Data Guard configured.
We will create a user in the pluggable PDB_FINA named “ca”, in this user will create a table with a record.
We will configure the “Active Data Guard” option and check the availability of the objects created in the pluggable PDB_FINA in the standby container.
Usage Tool: SQL * Plus
Our test environment:
- A CDB1 database with Data Guard configured which has three pluggable databases: PDB1, PDB2 and PDB_SERA.
- We will now have a CDB2 container in which there is no Data Guard configuration with a pluggable database called PDB_FINA, both container databases (CDB1 and CDB2) reside on the same server and the standby database CDB1 that is part of the Data Guard.
Scenario 1:
We will disconnect the PDB_FINA pluggable database from the CDB2 container.
[Oracle @ rfcg ~] $ export ORACLE_SID = CDB2 [Oracle @ rfcg ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 ProductiononTueApr 4 15:44:07 2017Copyright (c) 1982, 2014, Oracle. Allrightsreserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name, cdb from v $ database; NAME CDB (I.e. CDB2 YES SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 3 PDB_FINA READ WRITE NO SQL> alter pluggable database PDB_FINA close immediate; Pluggable database altered. SQL> alter pluggable database PDB_FINA UNPLUG INTO '/home/oracle/pdb_fina.xml'; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 3 PDB_FINA MOUNTED
We checked the location of the datafiles of the PDB_FINA pluggable database; This can be obtained from the generated xml file.
[Oracle @ rfcg ~] $ grepdbf /home/oracle/pdb_fina.xml /u01/app/oracle/oradata/cdb2/pdb_fina/system01.dbf /u01/app/oracle/oradata/cdb2/pdb_fina/sysaux01.dbf /u01/app/oracle/oradata/cdb2/pdb_fina/pdb_fina_temp012017-04-04.dbf /u01/app/oracle/oradata/cdb2/pdb_fina/SAMPLE_SCHEMA_users01.dbf /u01/app/oracle/oradata/cdb2/pdb_fina/example01.dbf
When creating a new pluggable in the primary container as seen in the article Oracle Data Guard 12c in “Multitenant” Architecture: PDBs behavior (Part I) the datafiles of the new pluggable are automatically created in the standby container and the process Synchronization is transparent, however the behavior of the pluggable is different in the Data Guard when plugging a pluggable into the primary container, the datafiles of the pluggable must be copied to the primary container and the standby container before being plugged in.
Previously you should create the directory where the datafiles of the pluggable PDB_FINA will be located.
[Oracle @ rfcg2 ~] $ mkdir -p / u01 / app / oracle / oradata / CDB1 / FINA /
We proceed to copy the datafiles of the PDB_FINA pluggable database to the standby server
[Oracle @ rfcg ~] $ cd / u01 / app / oracle / oradata / cdb2 / pdb_fina [Oracle @ rfcgpdb_fina] $ scp * .dbf oracle @ rfcg2: / u01 / app / oracle / oradata / CDB1 / FINA Oracle @ rfcg2's password: Example01.dbf 100% 1244MB 11.1MB / s 01:52 Pdb_fina_temp012017-04-04.dbf 100% 20MB 20.0MB / s 00:00 SAMPLE_SCHEMA_users01.dbf 100% 5128KB 5.0MB / s 00:01 Sysaux01.dbf 100% 520MB 16.8MB / s 00:31 System01.dbf 100% 260MB 13.0MB / s 00:20
In the primary server where the CDB1 is located, we also create the directory in which the datafiles of the pluggable PDB_FINA database will be located.
[Oracle @ rfcg ~] $ mkdir -p / u01 / app / oracle / oradata / CDB1 / FINA /
We enter the CDB1, we will proceed to connect the pluggable PDB_FINA, we use the parameter “copy” to copy the datafiles of the pluggable PDB_FINA to the CDB1 of the Data Guard and the parameter “file_name_convert” because the directories between the containers CDB1 and CDB2 are Different.
[Oracle @ rfcg ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 ProductiononTueApr 4 17:08:08 2017Copyright (c) 1982, 2014, Oracle. Allrightsreserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With 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 SQL> create pluggable database pdb_fina 2 using '/home/oracle/pdb_fina.xml' 3 copy 4 file_name_convert = ('/ u01 / app / oracle / oradata / cdb2 / pdb_fina /', 5 '/ u01 / app / oracle / oradata / CDB1 / FINA /'); Pluggable database created. 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 MOUNTED
After connecting the PDB_FINA to the CDB1, we proceed to place it in reading and writing mode.
SQL> alter pluggable database pdb_fina open read write; Pluggable database altered. 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 enter the pluggable PDB_FINA of the primary container and create a user named “ca”, with this user we will create a table with a register and then we will verify that these objects are available in the pluggable PDB_FINA of the standby container.
We create the user “ca” in PDB_FINA of the primary container.
[Oracle @ rfcg ~] $ sqlplus system / oracle @ rfcg: 1521 / pdb_fina SQL * Plus: Release 12.1.0.2.0 Production on Tue Apr 4 17:28:09 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create user ca identified by oracle; User created. SQL> grant connect, resource, dba to ca; Grant succeeded.
We enter the pluggable PDB_FINA with the user “ca”, create a table called “clients” and add a record.
[Oracle @ rfcg ~] $ sqlplus ca / oracle @ rfcg: 1521 / pdb_fina SQL * Plus: Release 12.1.0.2.0 Production on TueApr 4 17: 33: 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> CREATE TABLE clients (Id number (10) NOT NULL, Name varchar2 (50) NOT NULL, City varchar2 (50) ); 2. 3. 4. 5 Table created. SQL> insert into clients values (1, 'test', 'caracas'); 1 row created. SQL> commit; Commit complete.
We enter the standby server where we have the CDB1.
[Oracle @ rfcg2 ~] $ export ORACLE_SID = CDB1 [Oracle @ rfcg2 ~] $ sqlplus / as sysdba SQL * Plus: Release 12.1.0.2.0 ProductiononTue Apr 4 17:37:40 2017Copyright (c) 1982, 2014, Oracle. Allrightsreserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select status, instance_name fromv $ instance; STATUS INSTANCE_NAME ------------ ---------------- MOUNTED cdb1 SQL> select database_role fromv $ database; DATABASE_ROLE ---------------- PHYSICAL STANDBY
As we can see, listing the pluggable databases of our standby server is already the PDB_FINA as part of the Data Guard solution; The pluggable PDB_FINA is in the “mount” state.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED MOUNTED 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB_SERA MOUNTED 6 PDB_FINA MOUNTED
We proceed to open the pluggables in read and write mode and we would have the configuration of an “Active Data Guard”
SQL> alter pluggable database all open read only; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB $ SEED READ ONLY NO 3 PDB1 READ ONLY NO 4 PDB2 READ ONLY NO 5 PDB_SERA READ ONLY NO 6 PDB_FINA READ ONLY NO
Enter the PDB_FINA of the standby container and verify that the record created in the client’s table is available.
[Oracle @ rfcg2 ~] $ sqlplus ca / oracle @ rfcg2: 1521 / pdb_fina SQL * Plus: Release 12.1.0.2.0 Production Tue Apr 4 17:50:15 2017 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, OLAP, Advanced Analytics, and Real Application Testing options SQL> select * from clients; ID NAME CITY --- -------------- 1 test caracas
Conclusion:
As you can see, when we have a configuration with Oracle Data Guard in “Multitenant” architecture, the mechanism of obtaining a database “Standby” of a PDB is quite simple, we just have to add the same to the primary “container”, depending Of the case we will have to copy the datafiles to the standby server (s) and complete the “plug” process, one of the main advantages of this architecture is that we can have the corresponding “Standby “Without reconfiguring the solution.