Adding Pluggable Databases to an existing Multitenant Data Guard environment

We all know the benefits of the Oracle Multitenant cosolidation “Many-as-One”, one container (CDB) operation for wich many Pluggable Databases (PDBs) can take benefit; for example one CDB backup protects all PDBs stored inside the container itself.

While among the DBAs the setup of Oracle Data Guard is become more than a standandard routine,  described in thousands of Internet pages and blogs (one example available here), this post explains how to add new Pluggable Databases (PDBs) to an existing Multitenant environment protected by Data Guard.

 

How to create PDBs in Oracle Multitenant environment protected by Data Guard

There are multiple scenarios of PDB creation and they differently integrate within the Data Guard architecture. The easiest way to proceed consists in creating a new Pluggable Database using the SEED PDB:

  • PDB creation from SEED
    The creation of a brand new empty pluggable database is automatically replicated to each physical standby database. No additional action is required.

 

Unfortunately, this option is not always applicable because the new PDB should be a clone of an existing one. Therefore, it is important for the DBA to understand how integrating new non-empty Pluggable Database on a Multitenant and Data Guard environment without impacting the pre-existing setup.

 

  • PDB clone 

Cloning a PDB in a Data Guard environment requires few additional steps, which changes across the different Oracle versions and when Active Data Guard option is in use. While remote cloning the PDB the option STANDBYS=NONE should be used to defer the PDB replica to the Standby container. Then it is possible to replicate and protect the newly cloned PDB with Data Guard.

A full example of how to perform those tasks is reported below.

 

Cloning a PDB via DB Link using the STANDBYS=NONE option

create pluggable database PCJORD from PCLORD@ccls01_PCLORD tempfile reuse STANDBYS=NONE;

 

Open the newly cloned PDB

alter pluggable database PCJORD open instances=all;

 

On the Standby Container Restore PDB from Primary

run{
set newname for pluggable database PCJORD to new;
restore pluggable database PCJORD from service CMJP01;
switch datafile all;
}

 

Connect to the Standby container and STOP the Apply Process

dgmgrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-OFF';

 

If Active Data Guard is in use, re-start the Standby container in MOUNT

srvctl stop database -db CMJS01 

sqlplus / as sysdba
startup mount

 

Enable the PDB recovery on the Standby Container

alter session set container=PCJORD;
alter pluggable database enable recovery;

 

Connect to the Standby container and RE-START the Apply Process

dgmdrl
connect sys/xxxxxxxx@CMJP01
edit database 'CMJS01' set state='APPLY-ON';

 

If Active Data Guard is in use Open the Container in Read Only

alter database open;

 

 

 

Advertisements

Data Guard Broker

###################################################
##      How to configure DATA GUARD BROKER    ##
###################################################

# Create the data guard broker files

--Primary
alter system set dg_broker_config_file1 = '+DATA1/TEFOXTR/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+FRA1/TEFOXTR/DATAGUARDCONFIG/brokerconfig02.dat';

--Standby
alter system set dg_broker_config_file1 = '+DATA1/TEFOXZH/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+FRA1/TEFOXZH/DATAGUARDCONFIG/brokerconfig02.dat';


--Start the broker on both databases
alter system set dg_broker_start=true;


--From the primary database connect to the broker and create the configuration
#dgmgrl

DGMGRL>
connect sys/xxxxxxx@TEFOXTR.emilianofusaglia.net

--Create the configuration for primary database
create configuration 'BRKTEFOX' as primary database is 'TEFOXTR' connect identifier is TEFOXTR_DGBHA.emilianofusaglia.net;

--Add standby database
add database 'TEFOXZH' as connect identifier is TEFOXZH_DGBHA.emilianofusaglia.net;

--Setup the properties
edit database 'TEFOXTR' set property 'LogXptMode' = 'SYNC';
edit database 'TEFOXZH' set property 'LogXptMode' = 'SYNC';
--or
edit database 'TEFOXTR' set property 'LogXptMode' = 'ASYNC';
edit database 'TEFOXZH' set property 'LogXptMode' = 'ASYNC';

edit configuration set protection mode as maxprotection;
--or
edit configuration set protection mode as maxavailability;
--or
edit configuration set protection mode as maxperformance;

edit database 'TEFOXTR' set property 'NetTimeout' = '20';
edit database 'TEFOXZH' set property 'NetTimeout' = '20';

edit database 'TEFOXTR' set property 'Binding' = 'MANDATORY';
edit database 'TEFOXZH' set property 'Binding' = 'MANDATORY';

enable configuration;

-- Switchover command:
DGMGRL> SWITCHOVER to "TEFOXZH";

--Stop Recovery
edit database 'TEFOXZH' set state = 'APPLY-OFF';
--Start Recovery
edit database 'TEFOXZH' set state = 'APPLY-ON';
edit database 'TEFOXZH' set state = 'APPLY-ON' WITH APPLY INSTANCE ='TEFOXZH1';

--Enable ArchiveLog Tracing on Primary and Standby for Troubleshooting
edit instance 'TEFOXTR1' on database 'TEFOXTR' set property 'LogArchiveTrace' = '1';    
edit instance 'TEFOXZH1' on database 'TEFOXZH' set property 'LogArchiveTrace' = '6345';

Oracle 11gR1 Data Guard Setup

############################################################
## DATA GUARD IMPLEMENTATION on 11g R1 RAC ##
############################################################

--Primary DB: USA10
--Standby DB: EURO10

############################
--From the Primary Database:
alter database force logging;

alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;

select * from v$standby_log;

 alter system set parallel_execution_message_size=8192 scope=spfile;
 alter system set fast_start_mttr_target=3600;
 
############################
--From the Standby Site:

--Dump the pfile and change the following parameters for the Standby:
*.control_files='+DATA1/EURO10/CONTROLFILE/CURRENT01.CTR','+FRA1/EURO10/CONTROLFILE/CURRENT02.CTR'
*.db_file_name_convert='/USA10/','/EURO10/'
*.log_file_name_convert='/USA10/','/EURO10/'
*.db_unique_name='EURO10'
--AS this is a single instance RAC
_disable_interface_checking = TRUE


LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01.emilianofusaglia.net)(PORT = 1526))
  )

alter system set local_listener='LISTENER' scope=spfile sid='EURO11';
---
mkdir -p /u01/oracle/admin/EURO10/adump
mkdir -p /u01/oracle/diag/rdbms/euro10/EURO11/cdump


######################################################################
 ################## #Network Congiguration ########################
######################################################################


##########################################
## Static Listener Entries:
##########################################

--Primary Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
  )

--Primary Cluster Node 2
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
  )

--Primary Cluster Node 3
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
  )

--Standby Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
  )


##########################################
##TNS Entries Primary & Standby Cluster
##########################################
EURO11.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
      (INSTANCE_NAME = EURO11)
    )
  )

EURO10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
    )
  )

USA10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan01-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan02-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan03-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = USA10.emilianofusaglia.net)
    )
  )


##########################################
## Standby Controlfile Setup ##
##########################################

alter database create standby controlfile as '/u01/oracle/emiliano/USA10.stby.ctl';

# scp  /u01/oracle/emiliano/USA10.stby.ctl lneuron01:/tmp


### Execute the following steps:
Startup nomount pfile='/u01/oracle/emiliano/PFILES/EURO10_for_GD.ora';
Create the spfile on ASM
Startup nomount exclusive;


## Do not RESTORE the Controlfile automatically DONE by the CLONE Procedure
## Which update also Contro_file parameter into SPFIE!!!!
rman target /
RMAN> restore controlfile from '/u01/oracle/emiliano/USA10.stby.ctl';

---
##########################################
## Duplicate the Database
##########################################

##From Primary DB Restore the DB to the Standby Side:
rman
connect target sys/xxxxxxx@USA10                             ----PS No domain for Target
connect auxiliary sys/xxxxxxx@EURO10.emilianofusaglia.net      ----PS Use domain for Auxiliary

run {
   allocate channel p1 type disk;
   allocate auxiliary channel s1 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

--or

run {
   allocate channel p1 type disk;
   allocate channel p2 type disk;
   allocate channel p3 type disk;
   allocate channel p4 type disk;
   allocate auxiliary channel s1 type disk;
   allocate auxiliary channel s2 type disk;
   allocate auxiliary channel s3 type disk;
   allocate auxiliary channel s4 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

##########################################
## Register the Standby Database to CRS
##########################################

srvctl add database -d EURO10 -o $ORACLE_HOME
srvctl add instance -d EURO10 -i EURO11 -n lneuron01

##########################################
## Data Guard Broker Configuration
##########################################

--Primary
alter system set dg_broker_config_file1 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig02.dat';

--Standby
alter system set dg_broker_config_file1 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig02.dat';

--on both databases
alter system set dg_broker_start=true;

--From a primary node connect to the broker and create the configuration

#dgmgrl

DGMGRL> connect sys/xxxxxxxx@USA10.emilianofusaglia.net
Connected.

create configuration 'CONFDG10' as primary database is 'USA10' connect identifier is USA10.emilianofusaglia.net;
   
add database 'EURO10' as connect identifier is EURO10.emilianofusaglia.net;

edit database 'USA10' set property 'LogXptMode' = 'SYNC';
edit database 'EURO10' set property 'LogXptMode' = 'SYNC';

edit database 'USA10' set property 'LogXptMode' = 'ASYNC';
edit database 'EURO10' set property 'LogXptMode' = 'ASYNC';

--edit configuration set protection mode as maxavailability;
edit configuration set protection mode as maxprotection;
--edit configuration set protection mode as maxavailability;
--edit configuration set protection mode as maxperformance;
enable configuration;

edit database 'USA10' set property 'NetTimeout' = '20';
edit database 'EURO10' set property 'NetTimeout' = '20';

exit;

DGMGRL> SWITCHOVER to "EURO10";

--Stop Recovery
edit database 'EURO10' set state = 'APPLY-OFF';
--Start Recovery
edit database 'EURO10' set state = 'APPLY-ON';

--Enabling ArchiveLog Tracing on Primary and Standby Good for Troubleshooting!!
edit instance 'USA11' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA12' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA13' on database 'USA10' set property 'LogArchiveTrace' = '1';    

edit instance 'EURO11' on database 'EURO10' set property 'LogArchiveTrace' = '6345';    

##############################################################################