Monday, September 24, 2018

Maintain Standby Databases when Performing PDB online Clones | standby_pdb_source_file_dblink

In Oracle 12.2 a lot of new features are introduced for Multitenant 12.2 environments One of the possibility is to clone a pluggable databases while they are open for read/write operations. When we want to do this in a MAA environment this will introduce some problems Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)

For this Oracle has some new parameters which can be used to overcome these problems
the parameter  "standby_pdb_source_file_dblink"


Using Hot PDB cloning with standby_pdb_source_file_dblink in a Dataguard environment

Consider the following configuration:

  •  Primary container database CDB1
  •  Physical Standby container database CDB2 for CDB1
  •  Standalone container database PDB1 
  •  CDB2 is running in read only mode

Step by Step hot online cloning

On primary Database the manual steps
  • create db link my_copy_link connect to system identified by xxx using 'cdb1';
  • create pluggable database joords_tmp_PDB from PDB1 standbys=none;
  • alter pluggable database joords_tmp_PDB open;
  • alter pluggable database joords_tmp_PDB close;
  • alter pluggable database joords_tmp_PDB open read only;
  • create pluggable database JOORDS from joords_tmp_PDB;
  • alter pluggable database JOORDS open;
  • close pluggable database joords_tmp_PDB;
  • drop pluggable database joords_tmp_PDB including datafiles;

1. Preparation at database level
Create database link from standby to production

Edit the tnsnames.ora on standby host
  • prim_db =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = primary)
        )
      )
Edit the tnsnames.ora on primary host
  • secd_db =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = secondary)
        )
      )
  • Startup cdb$root in read only mode, Keep all PDB's in mount state
  • Create databasse links between the two CDB bi directional on PRIM:
    
    create database link my_copy_link_sp connect to system identified by oracle using 'prim_db';
    create database link my_copy_link_ps connect to system identified by oracle using 'secd_db';
    
  • Edit parameter "standby_pdb_source_file_dblink"
    SECD
    :
    alter system set standby_pdb_source_file_dblink='my_copy_link_sp';
    PRIM:
    alter system set standby_pdb_source_file_dblink='my_copy_link_ps';
2. Execute the steps for HOT cloning in MAA Oracle configuration

On PRIM:

create pluggable database joords_tmp from pdb1 standbys=none;
alter  pluggable database joords_tmp open;
alter  pluggable database joords_tmp close;
alter  pluggable database joords_tmp open read only;

create pluggable database mypdb_clone from joords_tmp;
alter  pluggable database mypdb_clone open;

alter  pluggable database joords_tmp close;
drop   pluggable database joords_tmp including datafiles;

Validate with dgmgrl the status of the configuration
     after DML's on a table on the PDB => mypdb_clone.