Refresh PDB from Primary

Follow

1. Introduction

Oracle has announced the support of up to three pluggable databases in Standard Edition 2 from 19c. With Dbvisit Standby 10.0.x, any new pluggable databases added to primary is automatically refreshed in the standby database when you send archivelogs from primary and apply them in standby without having to re-create the standby database.

Adding a snapshot pluggable database to primary is not supported for this process.

There are two new parameters that need to be set to appropriate values before creating the pluggable database or before sending archivelogs from primary to standby after adding the pluggable database. The parameters are

BCKDIR

BCKDIR_DR

The above parameters by default have the value of $DBVISIT_BASE/standbymp/oracle/backup/{DDC} directory.

BCKDIR parameters hold the backup of the pluggable database that was recently added in the primary and it's recommended to provide a proper location unless the pluggable database is added from the seed database. The backups taken are by default compressed RMAN backup, and we recommend providing a location with enough free space if the pluggable database is refreshed from an existing pluggable database or it is attached from a different server.

BCKDIR_DR parameter is the location in the standby server where the backup of the pluggable database is transferred from the primary. This location too must have enough free space to accommodate the backup of the pluggable database. The parameters can be found in Primary Server Settings (BCKDIR) and Standby Server Settings(BCKDIR_DR).

The Parameter PDB_SYNC is set to Y by default. This is to ensure that any PDB added in the primary is refreshed to standby without any manual intervention. If this parameter is set to N then the PDB’s created in the primary are not refreshed in the standby.

2. Example

2.1 Add pluggable database in primary

SQL> sho pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL> create pluggable database PDB2 admin user PDB2admin identified by PDB2admin;

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  MOUNTED
SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> alter pluggable database all save state;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
SQL> select name,guid from v$pdbs;

NAME			       GUID
------------------------------ --------------------------------
PDB$SEED		       CEBBFFF0A9CF56F7E0536601000AE0D1
PDB1			       D0C7417AED7E68E1E0536601000A94FD
PDB2			       D0C827189B6208CDE0536601000ADAD1

SQL>  alter session set container=PDB2;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/system.2934.1088681057
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/sysaux.2935.1088681057
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/undotbs1.2933.1088681057

SQL> create tablespace users;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/system.2934.1088681057
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/sysaux.2935.1088681057
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/undotbs1.2933.1088681057
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/users.2937.1088681181

SQL>

2.2 Send Archivelog from Primary

A new task is created in the task bar for exporting the PDB and the regular send archivelogs from primary to standby will proceed without any interruptions.

oracle@standby: /usr/dbvisit/standbymp/oracle/backup/DEV : cd D0C827189B6208CDE0536601000ADAD1/
oracle@standby: /usr/dbvisit/standbymp/oracle/backup/DEV/D0C827189B6208CDE0536601000ADAD1 : ls -lrt
total 120824
-rw-r--r--. 1 oracle oinstall 62054400 Nov 15 11:29 DEV_PDB2_DBF_12_2n0e7tbq_1_1.RMAN
-rw-r--r--. 1 oracle oinstall 38936576 Nov 15 11:29 DEV_PDB2_DBF_13_2p0e7tcc_1_1.RMAN
-rw-r--r--. 1 oracle oinstall 21651456 Nov 15 11:29 DEV_PDB2_DBF_14_2r0e7tcm_1_1.RMAN
-rw-r--r--. 1 oracle oinstall  1073152 Nov 15 11:29 DEV_PDB2_DBF_15_2t0e7tcs_1_1.RMAN
-rw-r--r--. 1 oracle oinstall      980 Nov 15 11:29 DEV_PDB2.json
oracle@standby: /usr/dbvisit/standbymp/oracle/backup/DEV/D0C827189B6208CDE0536601000ADAD1 :

2.2 Apply Archivelog in Standby

The PDB is refreshed from the backup that was transferred from primary to standby.

SQL> sho pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 3 PDB1 			  MOUNTED
	 4 PDB2 			  MOUNTED
SQL> alter session set container=PDB2;

Session altered.

SQL> col name for a100
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/system.276.1088681839
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/sysaux.281.1088681855
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/undotbs1.275.1088681871
+DATA/DEV/D0C827189B6208CDE0536601000ADAD1/DATAFILE/users.285.1088681877

SQL>

3. Refresh using refresh_pdb procedure

The pluggable database that is created in the primary can be refreshed using the procedure refresh_pdb as well in the standby. In below example, we see how this is done.

[oracle@kiwi701 /usr/dbvisit/standbymp/oracle]$ ./dbvctl -f refresh_pdb -h
FUNCTION refresh_pdb
Refresh a standby PDB from primary
dbvctl -f refresh_pdb -d <ddc> [-a guid=<comma separated list of PDB GUID>]

Find the GUID of the pluggable database

SQL> col name for a40
SQL> select name,guid from v$pdbs;

NAME					 GUID
---------------------------------------- --------------------------------
PDB$SEED				 87C9DB3334B2180EE053AB02000A067B
DEVPDB					 87CA561EA0712598E053AB02000AF73A
DEVPDB2 				 B707E993AB982BB9E053AB02000A7EF7

Run the PDB refresh command

[oracle@kiwi701 /usr/dbvisit/standbymp/oracle]$ ./dbvctl -d DEV -f refresh_pdb -a guid=B707E993AB982BB9E053AB02000A7EF7
>>> Exporting PDB B707E993AB982BB9E053AB02000A7EF7... done

Note FORCE_LOGGING is disabled in the primary database.

>>> Transferring Log file(s) from DEV on kiwi701 to kiwi702:

    thread 1 sequence 254 (thread_1_seq_254.652.1059849591)... done
    thread 1 sequence 255 (thread_1_seq_255.646.1059849591)... done
    thread 1 sequence 256 (thread_1_seq_256.320.1059849595)... done
    thread 1 sequence 257 (thread_1_seq_257.330.1059849607)... done

Starting Dbvisit Standby remotely on kiwi702...
Feedback will only be given once process has completed.
This may take some time. Please wait...
  Output of Dbvisit Standby on kiwi702:
  =============================================================

  >>> Applying Log file(s) from kiwi701 to DEV on kiwi702:

      thread 1 sequence 254 (1_254_1007124153.arc)... done
      thread 1 sequence 255 (1_255_1007124153.arc)... done
      thread 1 sequence 256 (1_256_1007124153.arc)... done
      thread 1 sequence 257 (1_257_1007124153.arc)... done
  >>> Restoring PDB B707E993AB982BB9E053AB02000A7EF7... done
      thread 1 sequence 254 (1_254_1007124153.arc)... done
      thread 1 sequence 255 (1_255_1007124153.arc)... done
      thread 1 sequence 256 (1_256_1007124153.arc)... done
      thread 1 sequence 257 (1_257_1007124153.arc)... done
      Last applied log(s):
      thread 1 sequence 257

      Next SCN required for recovery 5242018 generated at 2020-12-22:18:40:06 +13:00.
      Next required log thread 1 sequence 258
  =============================================================
  Dbvisit Standby on kiwi702 completed.
Completed.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>  sho pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  MOUNTED
	 3 DEVPDB			  MOUNTED
	 5 DEVPDB2			  MOUNTED
SQL>

Have more questions? Submit a request

Comments