Refreshing PDB from Primary

 

1. Introduction

One of the key features in Dbvisit Standby Version 10.0.x is the support of refresh of a pluggable database to standby. 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.

2. DDC Parameters

There are two new parameters which 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\standby\backup\{DDC} directory.

BCKDIR parameters hold the backup of the pluggable database that was recently added in the primary and its 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.

3. Example

3.1 Environment Details

Primary Server: Kiwi701

Database: DEV

Database Instance in ASM

ASM Instance : +ASM

Standby Server: Kiwi702

Database: DEV

Database Instance in ASM

ASM Instance : +ASM

For this example, the default location of BCKDIR and BCKDIR_DR parameter is used

1 2 3 4 [oracle@kiwi701 /usr/dbvisit/standby]$ ./dbvctl -d DEV -f print_var -a name=BCKDIR /usr/dbvisit/standby/backup [oracle@kiwi701 /usr/dbvisit/standby]$ ./dbvctl -d DEV -f print_var -a name=BCKDIR_DR /usr/dbvisit/standby/backup

3.2 Add pluggable database in the primary

Now, let's add a pluggable database in the primary from seed database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 SQL> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVPDB READ WRITE NO SQL> select host_name from v$instance; HOST_NAME ---------------------------------------------------------------- kiwi701.oraclekiwi.co.nz SQL> create pluggable database DEVPDB2 admin user DEVPDB2 identified by devpdb2 ; Pluggable database created. SQL> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DEVPDB READ WRITE NO 4 DEVPDB2 MOUNTED SQL> alter pluggable database DEVPDB2 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 DEVPDB READ WRITE NO 4 DEVPDB2 READ WRITE NO SQL> select name,guid from v$pdbs; NAME GUID ------------------------------ -------------------------------- PDB$SEED 87C9DB3334B2180EE053AB02000A067B DEVPDB 87CA561EA0712598E053AB02000AF73A DEVPDB2 B70775868225222CE053AB02000AB1E0 SQL> alter session set container=DEVPDB2; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/system.628.1059847255 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/sysaux.623.1059847257 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/undotbs1.548.1059847255 SQL> create tablespace users; Tablespace created. SQL> alter tablespace users add datafile; Tablespace altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/system.628.1059847255 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/sysaux.623.1059847257 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/undotbs1.548.1059847255 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/users.632.1059847503 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/users.631.1059847527

3.3 Send archivelogs using CLI

The next step is to send archivelogs from primary, this step will take the backup of the pluggable database DEVPDB2. The backup is taken in the default location of BCKDIR (/usr/dbvisit/standby/backup/{DDC}) and the backup files are sent to standby. This is done by running just the dbvctl command to send archivelogs to standby.

This step does not have to be done manually if your daemon service is running this step would automatically be taken care during the next scheduled send of archivelogs to standby. But, if your pluggable database is not created from seed database, we recommend stopping the daemon service and run the below command manually.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 [oracle@kiwi701 /usr/dbvisit/standby]$ ./dbvctl -d DEV ============================================================= Dbvisit Standby Database Technology (10.0.0RCFINAL_0_g6d0007e9) (pid 9606) dbvctl started on kiwi701: Tue Dec 22 18:15:46 2020 ============================================================= >>> Obtaining information from standby database (RUN_INSPECT=Y)... done Thread: 1 Archive log gap: 1. Transfer log gap: 0 Note FORCE_LOGGING is disabled in the primary database. >>> Exporting PDB B70775868225222CE053AB02000AB1E0... done >>> Transferring Log file(s) from DEV on kiwi701 to kiwi702: thread 1 sequence 249 (thread_1_seq_249.380.1059848245)... done thread 1 sequence 250 (thread_1_seq_250.580.1059848245)... done thread 1 sequence 251 (thread_1_seq_251.629.1059848247)... done ============================================================= dbvctl ended on kiwi701: Tue Dec 22 18:17:47 2020 =============================================================
1 2 3 4 5 6 7 8 9 [oracle@kiwi701 /usr/dbvisit/standby/backup/DEV]$ ls -lrt total 163536 -rw-r-----. 1 oracle asmadmin 54329344 Dec 22 18:16 DEV_DEVPDB2_DBF_35_bfvinvv4_1_1.RMAN -rw-r-----. 1 oracle asmadmin 109682688 Dec 22 18:16 DEV_DEVPDB2_DBF_36_bhvinvvr_1_1.RMAN -rw-r-----. 1 oracle asmadmin 1294336 Dec 22 18:17 DEV_DEVPDB2_DBF_37_bjvio00r_1_1.RMAN -rw-r-----. 1 oracle asmadmin 1073152 Dec 22 18:17 DEV_DEVPDB2_DBF_38_blvio015_1_1.RMAN -rw-r-----. 1 oracle asmadmin 1073152 Dec 22 18:17 DEV_DEVPDB2_DBF_39_bnvio01d_1_1.RMAN -rw-r--r--. 1 oracle oinstall 185 Dec 22 18:17 DEV_DEVPDB2_B70775868225222CE053AB02000AB1E0.txt [oracle@kiwi701 /usr/dbvisit/standby/backup/DEV]$

The backup files are automatically removed as soon as the files are transferred to the standby server.

3.4 Apply archivelogs using CLI

The next step is to run the default apply archivelogs command in the standby. This command would restore the PDB backup that was taken earlier and also recover the database.

1 2 3 4 5 6 7 8 9 10 [oracle@kiwi702 ~]$ cd /usr/dbvisit/standby/backup/DEV/ [oracle@kiwi702 /usr/dbvisit/standby/backup/DEV]$ ls -lrt total 163532 -rw-r--r-- 1 oracle oinstall 54329344 Dec 22 18:17 DEV_DEVPDB2_DBF_35_bfvinvv4_1_1.RMAN -rw-r--r-- 1 oracle oinstall 109682688 Dec 22 18:17 DEV_DEVPDB2_DBF_36_bhvinvvr_1_1.RMAN -rw-r--r-- 1 oracle oinstall 1294336 Dec 22 18:17 DEV_DEVPDB2_DBF_37_bjvio00r_1_1.RMAN -rw-r--r-- 1 oracle oinstall 1073152 Dec 22 18:17 DEV_DEVPDB2_DBF_38_blvio015_1_1.RMAN -rw-r--r-- 1 oracle oinstall 1073152 Dec 22 18:17 DEV_DEVPDB2_DBF_39_bnvio01d_1_1.RMAN -rw-r--r-- 1 oracle oinstall 185 Dec 22 18:17 DEV_DEVPDB2_B70775868225222CE053AB02000AB1E0.txt [oracle@kiwi702 /usr/dbvisit/standby/backup/DEV]$
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 [oracle@kiwi702 /usr/dbvisit/standby]$ ./dbvctl -d DEV ============================================================= Dbvisit Standby Database Technology (10.0.0RCFINAL_0_g6d0007e9) (pid 14676) dbvctl started on kiwi702: Tue Dec 22 18:25:20 2020 ============================================================= >>> Applying Log file(s) from kiwi701 to DEV on kiwi702: thread 1 sequence 249 (1_249_1007124153.arc)... done thread 1 sequence 250 (1_250_1007124153.arc)... done thread 1 sequence 251 (1_251_1007124153.arc)... done thread 1 sequence 252 (1_252_1007124153.arc)... done >>> Restoring PDB B70775868225222CE053AB02000AB1E0... done thread 1 sequence 249 (1_249_1007124153.arc)... done thread 1 sequence 250 (1_250_1007124153.arc)... done thread 1 sequence 251 (1_251_1007124153.arc)... done thread 1 sequence 252 (1_252_1007124153.arc)... done Last applied log(s): thread 1 sequence 252 Next SCN required for recovery 5240089 generated at 2020-12-22:18:25:07 +13:00. Next required log thread 1 sequence 253 ============================================================= dbvctl ended on kiwi702: Tue Dec 22 18:27:09 2020 =============================================================
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SQL> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 3 DEVPDB MOUNTED 4 DEVPDB2 MOUNTED SQL> alter session set container=DEVPDB2; Session altered. SQL> col Name for a100 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/system.289.1059848759 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/sysaux.257.1059848773 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/undotbs1.299.1059848799 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/users.339.1059848807 +DATA/DEV/B70775868225222CE053AB02000AB1E0/DATAFILE/users.260.1059848813 SQL>

After the pluggable database is refreshed the backup created under the $DBVISIT_BASE/standby/backup/DEV is automatically deleted.

4. 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.

1 2 3 4 [oracle@kiwi701 /usr/dbvisit/standby]$ ./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

1 2 3 4 5 6 7 8 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 [oracle@kiwi701 /usr/dbvisit/standby]$ ./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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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>