In my previous blog post and video, I had demonstrated creating a PDB (as a clone from an existing PDB) to be used for some testing/validation for a short period of time, without needing protection of a Standby database.
But what if you do decide to provide Standby protection ?
First, here's the status of the Pluggable Database at the Primary :
SQL> select con_id, pdb_name, creation_time, force_logging
2 from cdb_pdbs
3 where pdb_name = 'PRDAPPTST'
4 /
CON_ID PDB_NAME CREATION_ FORCE_LOGGING
---------- ------------ --------- ---------------------------------------
4 PRDAPPTST 29-JUN-25 NO
SQL>
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
SQL> alter pluggable database prdapptst enable force logging;
Pluggable database altered.
SQL>
SQL> select con_id, pdb_name, creation_time, force_logging
2 from cdb_pdbs
3 where pdb_name = 'PRDAPPTST'
4 /
CON_ID PDB_NAME CREATION_ FORCE_LOGGING
---------- ------------ --------- ---------------------------------------
4 PRDAPPTST 29-JUN-25 YES
SQL>
Note :In this case, the "ENABLE FORCE LOGGING" was strictly not required because the CDB-wide FORCE LOGGING was already enforced (query on V$DATABASE at the CDB$ROOT).
Here's the status at the Standby :
SQL> l
1 select con_id, name, open_mode, recovery_status
2 from v$pdbs
3* where name ='PRDAPPTST'
SQL> /
CON_ID NAME OPEN_MODE RECOVERY
---------- ------------ ---------- --------
4 PRDAPPTST MOUNTED DISABLED
SQL>
SQL> l
1 select con_id, file#, status, plugged_in, name
2 from v$datafile
3 where con_id=4
4* order by file#
SQL> /
CON_ID FILE# STATUS PLUGGED_IN NAME
---------- ---------- ------- ---------- --------------------------------
4 60 SYSOFF 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00060
4 61 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00061
4 62 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00062
4 63 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00063
4 64 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00064
SQL>
Meaning that although the Standby is "aware" of the PDB, not datafiles are present so it is not being recovered. If you recall from the Video demonstration, the alert log at the Standby presented these entries when the PDB was created at the Primary with STANDBYS=NONE :
Recovery created pluggable database PRDAPPTST
PRDAPPTST(4):Tablespace-SYSTEM during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):File #60 added to control file as 'UNNAMED00060'. Originally created as:
PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/system.299.1205032267'
PRDAPPTST(4):because the pluggable database was created with nostandby
PRDAPPTST(4):or the tablespace belonging to the pluggable database is
PRDAPPTST(4):offline.
PRDAPPTST(4):Tablespace-SYSAUX during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):File #61 added to control file as 'UNNAMED00061'. Originally created as:
PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/sysaux.280.1205032267'
PRDAPPTST(4):because the pluggable database was created with nostandby
PRDAPPTST(4):or the tablespace belonging to the pluggable database is
PRDAPPTST(4):offline.
PRDAPPTST(4):Tablespace-UNDOTBS1 during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):File #62 added to control file as 'UNNAMED00062'. Originally created as:
PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/undotbs1.285.1205032267'
PRDAPPTST(4):because the pluggable database was created with nostandby
PRDAPPTST(4):or the tablespace belonging to the pluggable database is
PRDAPPTST(4):offline.
PRDAPPTST(4):Tablespace-TEMP during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):Tablespace-UNDO_2 during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):File #63 added to control file as 'UNNAMED00063'. Originally created as:
PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/undo_2.284.1205032267'
PRDAPPTST(4):because the pluggable database was created with nostandby
PRDAPPTST(4):or the tablespace belonging to the pluggable database is
PRDAPPTST(4):offline.
PRDAPPTST(4):Tablespace-USERS during PDB create skipped since source is in r/w mode or this is a refresh clone
PRDAPPTST(4):File #64 added to control file as 'UNNAMED00064'. Originally created as:
PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/users.279.1205032267'
PRDAPPTST(4):because the pluggable database was created with nostandby
PRDAPPTST(4):or the tablespace belonging to the pluggable database is
PRDAPPTST(4):offline.
So, the current status is that the PDB *is* registered at the Standby but, in the absence of datafiles, no Redo Apply is being done at the Standby.
For the purposes of today's test, I created a few more rows in my target table in that PDB :
SQL> connect hemant/hemant@newtestpdb
Connected.
SQL> @INS_QRY
1 row created.
SQL> l
1* insert into my_txn_table select systimestamp, substr(p.name,1,12), substr(i.host_name,1,12), 'XXX' from v$pdbs p, v$instance i
SQL> set pages600 linesize 132
SQL> select * from my_txn_table;
TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS
--------------------------------------------------------------------------- --------------- --------------- ---------------
29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX
29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX
06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX
29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX
SQL> select * from my_txn_table order by txn_timestamp;
TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS
--------------------------------------------------------------------------- --------------- --------------- ---------------
29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX
29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX
29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX
06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX
SQL> commit;
Commit complete.
SQL> connect hemant/hemant@newtestpdb
Connected.
SQL> @INS_QRY
1 row created.
SQL> commit;
Commit complete.
SQL> connect hemant/hemant@newtestpdb
Connected.
SQL> @INS_QRY
1 row created.
SQL> commit;
Commit complete.
SQL> select * from my_txn_table order by txn_timestamp;
TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS
--------------------------------------------------------------------------- --------------- --------------- ---------------
29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX
29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX
29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX
06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX
06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX
06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX
6 rows selected.
SQL>
So, today, 06-Jul-25, I have created 3 new rows, with connections to both instances.
(To understand "newtestpdb" as the connect-string or service name for the PDB PRDAPPTST see the previous
Blog Post or
Video)
Now, I run a backup of this PDB at the Primary and will then copy it and restore it to the Standby.
[oracle@srv1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul 6 08:35:56 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313)
RMAN> backup as compressed backupset
backup as compressed backupset
2> pluggable database PRDAPPTST
pluggable database PRDAPPTST
3> format '/tmp/PRDAPPTST_DB_%U.bak';
And restore it at the Standby (after copying the backup pieces)
[oracle@stdby tmp]$ ls -ltr *bak
-rw-r-----. 1 oracle oinstall 849739776 Jul 6 08:38 PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
[oracle@stdby tmp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul 6 08:39:12 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313, not open)
RMAN> catalog start with '/tmp';
catalog start with '/tmp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp
List of Files Unknown to the Database
=====================================
File Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
RMAN>
RMAN> list backup of pluggable database PRDAPPTST;
list backup of pluggable database PRDAPPTST;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
319 Full 810.37M DISK 00:00:43 06-JUL-25
BP Key: 319 Status: AVAILABLE Compressed: YES Tag: TAG20250706T083635
Piece Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
List of Datafiles in backup set 319
Container ID: 4, PDB Name: PRDAPPTST
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
60 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00060
61 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00061
62 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00062
63 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00063
64 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00064
RMAN>
RMAN> run
run
2> {
{
3> set newname for pluggable database PRDAPPTST to '/Standby_DB/oradata/STDBY/PRDAPPTST/%U.dbf';
set newname for pluggable database PRDAPPTST to '/Standby_DB/oradata/STDBY/PRDAPPTST/%U.dbf';
4> restore pluggable database PRDAPPTST;
restore pluggable database PRDAPPTST;
5> switch datafile all;
switch datafile all;
6> }
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00060 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
channel ORA_DISK_1: restoring datafile 00061 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
channel ORA_DISK_1: restoring datafile 00062 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
channel ORA_DISK_1: restoring datafile 00063 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
channel ORA_DISK_1: restoring datafile 00064 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
channel ORA_DISK_1: reading from backup piece /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
channel ORA_DISK_1: piece handle=/tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak tag=TAG20250706T083635
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 06-JUL-25
datafile 60 switched to datafile copy
input datafile copy RECID=54 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
datafile 61 switched to datafile copy
input datafile copy RECID=55 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
datafile 62 switched to datafile copy
input datafile copy RECID=56 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
datafile 63 switched to datafile copy
input datafile copy RECID=57 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
datafile 64 switched to datafile copy
input datafile copy RECID=58 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
RMAN>
Note :If you see my previous blog posts and videos on RAC, you can see that I have demonstrated that
1. A RAC Primary can have a Non-RAC (Single Instance) Standby and vice-versa as well in my video demonstrations of DataGuard Switchover
2. A Primary on ASM can have a Standby on Filesystem and vice-versa
Now that I have restored the database to the Standby, I must ENABLE RECOVERY for this PDB
[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 10:32:27 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> alter pluggable database prdapptst enable recovery;
alter pluggable database prdapptst enable recovery
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database
SQL> alter session set container=PRDAPPTST;
Session altered.
SQL> alter pluggable database prdapptst enable recovery;
Pluggable database altered.
SQL> connect hemant/hemant@newtestpdb
Connected.
SQL> @INS_QRY
1 row created.
SQL> set pages600 linesize 132
SQL> commit;
Commit complete.
SQL> select * from my_txn_table order by 1;
TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS
--------------------------------------------------------------------------- --------------- --------------- ---------------
29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX
29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX
29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX
06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX
06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX
06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX
06-JUL-25 09.53.06.164510 AM PRDAPPTST srv1 XXX
06-JUL-25 10.33.28.132217 AM PRDAPPTST srv2 XXX
8 rows selected.
SQL>
SQL> connect / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL>
I can now go to the Standby, Bring Datafiles Online, Resume Recovery and query the PDB
[oracle@stdby trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 10:39:50 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147480256 bytes
Fixed Size 9179840 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7593984 bytes
Database mounted.
SQL> alter session set container=PRDAPPTST;
Session altered.
SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf' online;
alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf' online
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf' online;
alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf' online
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf' online;
alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf' online
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf' online;
alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf' online
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf' online;
alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf' online
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL>
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147480256 bytes
Fixed Size 9179840 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7593984 bytes
Database mounted.
SQL> alter session set container=PRDAPPTST;
Session altered.
SQL> alter pluggable database enable recovery;
alter pluggable database enable recovery
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[oracle@stdby trace]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jul 6 11:05:42 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / as sysdba
Connected to "STDBY"
Connected as SYSDBA.
DGMGRL> edit database STDBY set state='apply-off';
Succeeded.
DGMGRL> exit
[oracle@stdby trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 11:06:48 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> alter session set container=PRDAPPTST;
Session altered.
SQL> alter pluggable database enable recovery;
Pluggable database altered.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[oracle@stdby trace]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jul 6 11:07:32 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect / as sysdba
Connected to "STDBY"
Connected as SYSDBA.
DGMGRL> edit database stdby set state='apply-on';
Succeeded.
DGMGRL> exit
[oracle@stdby trace]$
[oracle@stdby trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 11:08:09 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> select name, open_mode from v$pdbs;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
MOUNTED
PDB1
MOUNTED
PRDAPPTST
MOUNTED
SQL> alter database open read only;
Database altered.
SQL> alter pluggable database prdapptst open read only;
Pluggable database altered.
SQL>
SQL> alter session set container=PRDAPPTST;
Session altered.
SQL> set pages600 linesize 132
SQL> select * from hemant.my_txn_table order by 1;
TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS
--------------------------------------------------------------------------- --------------- --------------- ---------------
29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX
29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX
29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX
06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX
06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX
06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX
06-JUL-25 09.53.06.164510 AM PRDAPPTST srv1 XXX
06-JUL-25 10.33.28.132217 AM PRDAPPTST srv2 XXX
8 rows selected.
SQL>
Now that the database is restored and recovery resumed, data is available in PRDAPPTST at the Standby as well !