Hemant K Chitale
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
Updated: 20 hours 59 min ago
Cross-Instance Deadlock in RAC
I've created a video demonstration of a Cross-Instance Deadlock in a 2-node RAC environment and how to read the trace file.
Note : Only the SQL that has been returned the "deadlock" error is failed, the session and transaction are still valid.
Categories: DBA Blogs
An introduction to Oracle Cloud Infrastructure
Description:
This session will cover the fundamentals of Oracle Cloud Infrastructure (OCI) including cloud concepts, OCI structures, and core OCI services. OCI service areas that will be discussed include Identity and Access Management (IAM), networking, compute, storage, and database. You will also learn about Users, Groups, and Policies, along with the core tenets of virtual network design that will help set the foundations for application deployment to OCI. We will also cover terms and definitions for each of the topic areas to establish a foundation for additional sessions.
Categories: DBA Blogs
Configuring a RAC Service tied to a PDB and specific Instance
I have created a new Video demonstrating how you can add new Services to an existing database. In RAC, you can choose to have the Service running on all or selected or a single node of the Cluster. The service can be bound to a PDB.
This allows you to separate different applications / groups of users via Services and manage availability via Startup/Shutdown/Relocation/Failover of services.
The comand to configure a service to a single PDB and Instance is :
srvctl add service -db RACDB -service prdapps1 -preferred RACDB1 -available RACDB2 -tafpolicy BASIC -failovertype SELECT -clbgoal SHORT -pdb PDAPPTST
Categories: DBA Blogs
Converting a "NO Standby" to "YES Standby" database
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 !
Categories: DBA Blogs
Creating a new PDB in RAC without Standby Protection
I've published a new Video demonstration when you need to create a new PDB (even as a clone of an existing one) without requiring a Standby copy of the PDB.
(Use cases : Test environment for a day or two, it would take too long to create a new Test Server and clone the database, it would take too long to change Firewall rules to allow Applications to connect to the Test Server)
This in RAC with DataGuard Useful commands : Create new PDB :
CREATE PLUGGABLE DATABASE PRDAPPTST FROM PDB1 STANDBYS=NONE
Create Service :
srvctl add service -db RACDB -service newtestpdb -preferred RACDB1,RACDB2 -tafpolicy BASIC -failovertype SELECT -clbgoal SHORT -pdb PRDAPPTSTStart Service :
srvctl enable service -db RACDB -service newtestpdb srvctl status service -db RACDB -service newtestpdb srvctl start service -db RACDB -service newtestpdb srvctl status service -db RACDB -service newtestpdbAlways use custom Service Names instead of the default name derived from the PDB Name.
UPDATE 02-July : In response to a question on x.com : "One question: what if we want to create standby in future. Do we have to change standby parameter using any command or we can directly create standby normally"
My reply is :
The STANDBYS=NONE applies only to this PDB (PRDAPPTST). Any new PDB would still default to STANDBYS=ALL.
Although PRDAPPTST is intended to be dropped in a day or two, if you want it to be protected by Standby for longer or permanently, you can restore it to the Standby CDB (as it is in the controlfile and datadictionary) and resume recovery with ENABLE RECOVERY
See this next Blog Post where the Standby for PRDAPPTST is also setup.
Also see this series of posts on Refreshable Clone PDBs that I published a few years ago.
Categories: DBA Blogs
Backup a Backup that is in ASM
In RAC or Single Instance environments with ASM, it is possible to run database backups to ASM (typically the FRA diskgroup).
However, you would need the ability to copy the backup *out of ASM* -- e.g. using non-Oracle methods to copy to backup storage / offsite storage / tape drive.
Note : Timestamps are in UTC in the listings below
First, I run a Fresh Backup that, by default, goes to the designated FRA
[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 18 14:34:21 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> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 13332M
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[oracle@srv1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 18 14:34:49 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 database plus archivelog delete input;
backup as compressed backupset database plus archivelog delete input;
Starting backup at 18-JUN-25
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 instance=RACDB1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=381 RECID=1257 STAMP=1199502152
input archived log thread=2 sequence=334 RECID=1258 STAMP=1199502185
input archived log thread=1 sequence=382 RECID=1260 STAMP=1199502404
...
...
...
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/RACDB/DATAFILE/system.257.1164519439
input datafile file number=00003 name=+DATA/RACDB/DATAFILE/sysaux.258.1164519463
input datafile file number=00004 name=+DATA/RACDB/DATAFILE/undotbs1.259.1164519479
input datafile file number=00009 name=+DATA/RACDB/DATAFILE/undotbs2.269.1164519893
input datafile file number=00007 name=+DATA/RACDB/DATAFILE/users.260.1164519479
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 tag=TAG20250618T143546 comment=NONE
...
...
...
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/system.274.1164520333
input datafile file number=00011 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/sysaux.275.1164520333
input datafile file number=00012 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undotbs1.273.1164520333
input datafile file number=00013 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undo_2.277.1164520345
input datafile file number=00015 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users2.279.1167388913
input datafile file number=00016 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users3.280.1167388955
input datafile file number=00014 name=+DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users.278.1164520353
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 tag=TAG20250618T143546 comment=NONE
...
...
...
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+FRA/RACDB/ARCHIVELOG/2025_06_18/thread_1_seq_403.402.1204123149 thread=1 sequence=403
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+FRA/RACDB/ARCHIVELOG/2025_06_18/thread_2_seq_348.515.1204123149 thread=2 sequence=348
Finished backup at 18-JUN-25
Starting Control File and SPFILE Autobackup at 18-JUN-25
piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JUN-25
RMAN>
RMAN> list backupset completed after "trunc(sysdate)";
list backupset completed after "trunc(sysdate)";
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
337 57.35M DISK 00:00:03 18-JUN-25
BP Key: 337 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143542
Piece Name: +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943
List of Archived Logs in backup set 337
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
...
...
...
1 401 11161346 01-MAY-25 11165463 18-JUN-25
1 402 11165463 18-JUN-25 11177493 18-JUN-25
...
...
...
2 346 11161357 01-MAY-25 11161847 18-JUN-25
2 347 11161847 18-JUN-25 11177499 18-JUN-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
338 Full 1.07G DISK 00:00:57 18-JUN-25
BP Key: 338 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143546
Piece Name: +FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947
List of Datafiles in backup set 338
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/system.257.1164519439
3 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/sysaux.258.1164519463
4 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs1.259.1164519479
7 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/users.260.1164519479
9 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs2.269.1164519893
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
339 Full 775.09M DISK 00:00:42 18-JUN-25
BP Key: 339 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143546
Piece Name: +FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013
List of Datafiles in backup set 339
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
10 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/system.274.1164520333
11 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/sysaux.275.1164520333
12 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undotbs1.273.1164520333
13 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undo_2.277.1164520345
14 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users.278.1164520353
15 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users2.279.1167388913
16 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users3.280.1167388955
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
340 Full 812.50M DISK 00:00:42 18-JUN-25
BP Key: 340 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143546
Piece Name: +FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057
List of Datafiles in backup set 340
Container ID: 5, PDB Name: TSTPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
25 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/system.281.1199369077
26 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/sysaux.283.1199369077
27 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undotbs1.282.1199369077
28 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undo_2.285.1199369085
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
341 Full 812.46M DISK 00:00:42 18-JUN-25
BP Key: 341 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143546
Piece Name: +FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103
List of Datafiles in backup set 341
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1164519659
6 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1164519659
8 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1164519659
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
342 674.00K DISK 00:00:00 18-JUN-25
BP Key: 342 Status: AVAILABLE Compressed: YES Tag: TAG20250618T143909
Piece Name: +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149
List of Archived Logs in backup set 342
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 403 11177493 18-JUN-25 11179239 18-JUN-25
2 348 11177499 18-JUN-25 11179236 18-JUN-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
343 Full 19.17M DISK 00:00:00 18-JUN-25
BP Key: 343 Status: AVAILABLE Compressed: NO Tag: TAG20250618T143910
Piece Name: +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151
SPFILE Included: Modification time: 18-JUN-25
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 11179258 Ckp time: 18-JUN-25
RMAN>
So I have run a backup from the first node of the RAC Cluster "srv1". These are Backup Sets 337 to 343. Since I didn't provide a TAG name in the BACKUP command, they have default TAG names based on Date and Time (TAG20250618T143542, TAG20250618T143546, TAG20250618T143546, TAG20250618T143546, TAG20250618T143546, TAG20250618T143909, TAG20250618T143910)
Next I want to copy (i.e. backup) this backup to filesystem on "srv2" being the second node of the RAC Cluster. As they are on the (shared) ASM DiskGroup, I can crosscheck them from srv2 first before I backup to filesystem.
[oracle@srv2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 18 14:45:19 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> crosscheck backup completed after "trunc(sysdate)";
crosscheck backup completed after "trunc(sysdate)";
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=229 instance=RACDB2 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943 RECID=337 STAMP=1204122943
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 RECID=338 STAMP=1204122946
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 RECID=339 STAMP=1204123012
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057 RECID=340 STAMP=1204123057
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103 RECID=341 STAMP=1204123102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149 RECID=342 STAMP=1204123149
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 RECID=343 STAMP=1204123150
Crosschecked 7 objects
RMAN>
RMAN> backup device type disk format '/u02/RMAN_To_Disk/%U' backupset completed after "trunc(sysdate)";
backup device type disk format '/u02/RMAN_To_Disk/%U' backupset completed after "trunc(sysdate)";
Starting backup at 18-JUN-25
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=380, stamp=1204122942, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943
piece handle=/u02/RMAN_To_Disk/bs3sat9u_380_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=381, stamp=1204122946, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947
piece handle=/u02/RMAN_To_Disk/bt3sata2_381_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=382, stamp=1204123011, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013
piece handle=/u02/RMAN_To_Disk/bu3satc3_382_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=383, stamp=1204123057, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057
piece handle=/u02/RMAN_To_Disk/bv3satdh_383_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=384, stamp=1204123102, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103
piece handle=/u02/RMAN_To_Disk/c03sateu_384_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=385, stamp=1204123149, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149
piece handle=/u02/RMAN_To_Disk/c13satgd_385_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=386, stamp=1204123150, piece=1
channel ORA_DISK_1: starting piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151
piece handle=/u02/RMAN_To_Disk/c23satge_386_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 18-JUN-25
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
Finished backup at 18-JUN-25
Starting Control File and SPFILE Autobackup at 18-JUN-25
piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JUN-25
RMAN>
RMAN> crosscheck backup completed after "trunc(sysdate)";
crosscheck backup completed after "trunc(sysdate)";
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943 RECID=337 STAMP=1204122943
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/bs3sat9u_380_1_2 RECID=344 STAMP=1204123823
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 RECID=338 STAMP=1204122946
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/bt3sata2_381_1_2 RECID=345 STAMP=1204123825
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 RECID=339 STAMP=1204123012
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/bu3satc3_382_1_2 RECID=346 STAMP=1204123826
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057 RECID=340 STAMP=1204123057
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/bv3satdh_383_1_2 RECID=347 STAMP=1204123827
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103 RECID=341 STAMP=1204123102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/c03sateu_384_1_2 RECID=348 STAMP=1204123828
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149 RECID=342 STAMP=1204123149
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/c13satgd_385_1_2 RECID=349 STAMP=1204123829
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 RECID=343 STAMP=1204123150
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/RMAN_To_Disk/c23satge_386_1_2 RECID=350 STAMP=1204123830
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831 RECID=351 STAMP=1204123831
Crosschecked 15 objects
RMAN>RMAN> list backup summary completed after "trunc(sysdate)";
list backup summary completed after "trunc(sysdate)";
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
337 B A A DISK 18-JUN-25 1 2 YES TAG20250618T143542
338 B F A DISK 18-JUN-25 1 2 YES TAG20250618T143546
339 B F A DISK 18-JUN-25 1 2 YES TAG20250618T143546
340 B F A DISK 18-JUN-25 1 2 YES TAG20250618T143546
341 B F A DISK 18-JUN-25 1 2 YES TAG20250618T143546
342 B A A DISK 18-JUN-25 1 2 YES TAG20250618T143909
343 B F A DISK 18-JUN-25 1 2 NO TAG20250618T143910
344 B F A DISK 18-JUN-25 1 1 NO TAG20250618T145031
RMAN>
RMAN> list backupset completed after "trunc(sysdate)";
list backupset completed after "trunc(sysdate)";
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size
------- ----------
337 57.35M
List of Archived Logs in backup set 337
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
...
...
...
1 401 11161346 01-MAY-25 11165463 18-JUN-25
1 402 11165463 18-JUN-25 11177493 18-JUN-25
...
...
...
2 346 11161357 01-MAY-25 11161847 18-JUN-25
2 347 11161847 18-JUN-25 11177499 18-JUN-25
Backup Set Copy #1 of backup set 337
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:03 18-JUN-25 YES TAG20250618T143542
List of Backup Pieces for backup set 337 Copy #1 ### Shown as Copy #1 for Backupset 337
BP Key Pc# Status Piece Name
------- --- ----------- ----------
337 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943
Backup Set Copy #2 of backup set 337
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:03 18-JUN-25 YES TAG20250618T143542
List of Backup Pieces for backup set 337 Copy #2 ### Shown as Copy #2 for Backupset 337
BP Key Pc# Status Piece Name
------- --- ----------- ----------
344 1 AVAILABLE /u02/RMAN_To_Disk/bs3sat9u_380_1_2
BS Key Type LV Size
------- ---- -- ----------
338 Full 1.07G
List of Datafiles in backup set 338
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/system.257.1164519439
3 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/sysaux.258.1164519463
4 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs1.259.1164519479
7 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/users.260.1164519479
9 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs2.269.1164519893
Backup Set Copy #1 of backup set 338
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:57 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 338 Copy #1 ### Shown as Copy #1 for Backupset 338
BP Key Pc# Status Piece Name
------- --- ----------- ----------
338 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947
Backup Set Copy #2 of backup set 338
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:57 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 338 Copy #2 ### Shown as Copy #2 for Backupset 338
BP Key Pc# Status Piece Name
------- --- ----------- ----------
345 1 AVAILABLE /u02/RMAN_To_Disk/bt3sata2_381_1_2
BS Key Type LV Size
------- ---- -- ----------
339 Full 775.09M
List of Datafiles in backup set 339
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
10 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/system.274.1164520333
11 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/sysaux.275.1164520333
12 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undotbs1.273.1164520333
13 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undo_2.277.1164520345
14 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users.278.1164520353
15 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users2.279.1167388913
16 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users3.280.1167388955
Backup Set Copy #1 of backup set 339
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 339 Copy #1 ### Shown as Copy #1 for Backupset 339
BP Key Pc# Status Piece Name
------- --- ----------- ----------
339 1 AVAILABLE +FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013
Backup Set Copy #2 of backup set 339
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 339 Copy #2 ### Shown as Copy #2 for Backupset 339
BP Key Pc# Status Piece Name
------- --- ----------- ----------
346 1 AVAILABLE /u02/RMAN_To_Disk/bu3satc3_382_1_2
BS Key Type LV Size
------- ---- -- ----------
340 Full 812.50M
List of Datafiles in backup set 340
Container ID: 5, PDB Name: TSTPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
25 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/system.281.1199369077
26 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/sysaux.283.1199369077
27 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undotbs1.282.1199369077
28 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undo_2.285.1199369085
Backup Set Copy #1 of backup set 340
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 340 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
340 1 AVAILABLE +FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057
Backup Set Copy #2 of backup set 340
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 340 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
347 1 AVAILABLE /u02/RMAN_To_Disk/bv3satdh_383_1_2
BS Key Type LV Size
------- ---- -- ----------
341 Full 812.46M
List of Datafiles in backup set 341
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1164519659
6 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1164519659
8 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1164519659
Backup Set Copy #1 of backup set 341
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 341 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
341 1 AVAILABLE +FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103
Backup Set Copy #2 of backup set 341
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 341 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
348 1 AVAILABLE /u02/RMAN_To_Disk/c03sateu_384_1_2
BS Key Size
------- ----------
342 674.00K
List of Archived Logs in backup set 342
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 403 11177493 18-JUN-25 11179239 18-JUN-25
2 348 11177499 18-JUN-25 11179236 18-JUN-25
Backup Set Copy #1 of backup set 342
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 YES TAG20250618T143909
List of Backup Pieces for backup set 342 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
342 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149
Backup Set Copy #2 of backup set 342
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 YES TAG20250618T143909
List of Backup Pieces for backup set 342 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
349 1 AVAILABLE /u02/RMAN_To_Disk/c13satgd_385_1_2
BS Key Type LV Size
------- ---- -- ----------
343 Full 19.17M
SPFILE Included: Modification time: 18-JUN-25
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 11179258 Ckp time: 18-JUN-25
Backup Set Copy #1 of backup set 343
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 NO TAG20250618T143910
List of Backup Pieces for backup set 343 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
343 1 AVAILABLE +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151
Backup Set Copy #2 of backup set 343
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 NO TAG20250618T143910
List of Backup Pieces for backup set 343 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
350 1 AVAILABLE /u02/RMAN_To_Disk/c23satge_386_1_2
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
344 Full 19.17M DISK 00:00:00 18-JUN-25
BP Key: 351 Status: AVAILABLE Compressed: NO Tag: TAG20250618T145031
Piece Name: +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831
SPFILE Included: Modification time: 18-JUN-25
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 11185112 Ckp time: 18-JUN-25
RMAN>
[oracle@srv2 ~]$ cd /u02/RMAN_To_Disk
[oracle@srv2 RMAN_To_Disk]$ ls -l
total 3656024
-rw-r-----. 1 oracle oinstall 60140032 Jun 18 14:50 bs3sat9u_380_1_2
-rw-r-----. 1 oracle oinstall 1146150912 Jun 18 14:50 bt3sata2_381_1_2
-rw-r-----. 1 oracle oinstall 812752896 Jun 18 14:50 bu3satc3_382_1_2
-rw-r-----. 1 oracle oinstall 851976192 Jun 18 14:50 bv3satdh_383_1_2
-rw-r-----. 1 oracle oinstall 851935232 Jun 18 14:50 c03sateu_384_1_2
-rw-r-----. 1 oracle oinstall 690688 Jun 18 14:50 c13satgd_385_1_2
-rw-r-----. 1 oracle oinstall 20119552 Jun 18 14:50 c23satge_386_1_2
[oracle@srv2 RMAN_To_Disk]$
Thus, RMAN on Server "srv2" sees 2 copies of each backupset (337 to 343) (while 344 is the new BackupsSet for the Controlfile + SPFILE Autobackp created on FRA)
However srv1 will not "see" the additional BackupSet Copies ("Copy #2") created on FileSystem on srv2 -- although they will appear in the controlfile, a CROSSCHECK executed from srv1 will fail to find them.
[oracle@srv1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 18 15:05:16 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> list backup completed after "trunc(sysdate)";
list backup completed after "trunc(sysdate)";
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size
------- ----------
337 57.35M
List of Archived Logs in backup set 337
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
...
...
...
1 401 11161346 01-MAY-25 11165463 18-JUN-25
1 402 11165463 18-JUN-25 11177493 18-JUN-25
...
...
...
2 346 11161357 01-MAY-25 11161847 18-JUN-25
2 347 11161847 18-JUN-25 11177499 18-JUN-25
Backup Set Copy #1 of backup set 337
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:03 18-JUN-25 YES TAG20250618T143542
List of Backup Pieces for backup set 337 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
337 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943
Backup Set Copy #2 of backup set 337
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:03 18-JUN-25 YES TAG20250618T143542
List of Backup Pieces for backup set 337 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
344 1 AVAILABLE /u02/RMAN_To_Disk/bs3sat9u_380_1_2
BS Key Type LV Size
------- ---- -- ----------
338 Full 1.07G
List of Datafiles in backup set 338
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/system.257.1164519439
3 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/sysaux.258.1164519463
4 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs1.259.1164519479
7 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/users.260.1164519479
9 Full 11177541 18-JUN-25 NO +DATA/RACDB/DATAFILE/undotbs2.269.1164519893
Backup Set Copy #1 of backup set 338
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:57 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 338 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
338 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947
Backup Set Copy #2 of backup set 338
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:57 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 338 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
345 1 AVAILABLE /u02/RMAN_To_Disk/bt3sata2_381_1_2
BS Key Type LV Size
------- ---- -- ----------
339 Full 775.09M
List of Datafiles in backup set 339
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
10 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/system.274.1164520333
11 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/sysaux.275.1164520333
12 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undotbs1.273.1164520333
13 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/undo_2.277.1164520345
14 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users.278.1164520353
15 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users2.279.1167388913
16 Full 11177736 18-JUN-25 NO +DATA/RACDB/1476C653214704CFE0635A38A8C08494/DATAFILE/users3.280.1167388955
Backup Set Copy #1 of backup set 339
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 339 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
339 1 AVAILABLE +FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013
Backup Set Copy #2 of backup set 339
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 339 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
346 1 AVAILABLE /u02/RMAN_To_Disk/bu3satc3_382_1_2
BS Key Type LV Size
------- ---- -- ----------
340 Full 812.50M
List of Datafiles in backup set 340
Container ID: 5, PDB Name: TSTPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
25 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/system.281.1199369077
26 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/sysaux.283.1199369077
27 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undotbs1.282.1199369077
28 Full 11083998 25-APR-25 NO +DATA/RACDB/339BC12E8011538DE0635A38A8C0C92A/DATAFILE/undo_2.285.1199369085
Backup Set Copy #1 of backup set 340
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 340 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
340 1 AVAILABLE +FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057
Backup Set Copy #2 of backup set 340
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 340 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
347 1 AVAILABLE /u02/RMAN_To_Disk/bv3satdh_383_1_2
BS Key Type LV Size
------- ---- -- ----------
341 Full 812.46M
List of Datafiles in backup set 341
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1164519659
6 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1164519659
8 Full 9669002 26-NOV-24 NO +DATA/RACDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1164519659
Backup Set Copy #1 of backup set 341
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 341 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
341 1 AVAILABLE +FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103
Backup Set Copy #2 of backup set 341
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:42 18-JUN-25 YES TAG20250618T143546
List of Backup Pieces for backup set 341 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
348 1 AVAILABLE /u02/RMAN_To_Disk/c03sateu_384_1_2
BS Key Size
------- ----------
342 674.00K
List of Archived Logs in backup set 342
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 403 11177493 18-JUN-25 11179239 18-JUN-25
2 348 11177499 18-JUN-25 11179236 18-JUN-25
Backup Set Copy #1 of backup set 342
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 YES TAG20250618T143909
List of Backup Pieces for backup set 342 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
342 1 AVAILABLE +FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149
Backup Set Copy #2 of backup set 342
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 YES TAG20250618T143909
List of Backup Pieces for backup set 342 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
349 1 AVAILABLE /u02/RMAN_To_Disk/c13satgd_385_1_2
BS Key Type LV Size
------- ---- -- ----------
343 Full 19.17M
SPFILE Included: Modification time: 18-JUN-25
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 11179258 Ckp time: 18-JUN-25
Backup Set Copy #1 of backup set 343
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 NO TAG20250618T143910
List of Backup Pieces for backup set 343 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
343 1 AVAILABLE +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151
Backup Set Copy #2 of backup set 343
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 18-JUN-25 NO TAG20250618T143910
List of Backup Pieces for backup set 343 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
350 1 AVAILABLE /u02/RMAN_To_Disk/c23satge_386_1_2
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
344 Full 19.17M DISK 00:00:00 18-JUN-25
BP Key: 351 Status: AVAILABLE Compressed: NO Tag: TAG20250618T145031
Piece Name: +FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831
SPFILE Included: Modification time: 18-JUN-25
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 11185112 Ckp time: 18-JUN-25
RMAN>
RMAN> crosscheck backup completed after "trunc(sysdate)";
crosscheck backup completed after "trunc(sysdate)";
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 instance=RACDB1 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943 RECID=337 STAMP=1204122943
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/bs3sat9u_380_1_2 RECID=344 STAMP=1204123823
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 RECID=338 STAMP=1204122946
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/bt3sata2_381_1_2 RECID=345 STAMP=1204123825
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 RECID=339 STAMP=1204123012
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/bu3satc3_382_1_2 RECID=346 STAMP=1204123826
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057 RECID=340 STAMP=1204123057
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/bv3satdh_383_1_2 RECID=347 STAMP=1204123827
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103 RECID=341 STAMP=1204123102
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/c03sateu_384_1_2 RECID=348 STAMP=1204123828
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149 RECID=342 STAMP=1204123149
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/c13satgd_385_1_2 RECID=349 STAMP=1204123829
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 RECID=343 STAMP=1204123150
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_To_Disk/c23satge_386_1_2 RECID=350 STAMP=1204123830
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831 RECID=351 STAMP=1204123831
Crosschecked 15 objects
RMAN>
RMAN> delete noprompt expired backup; delete noprompt expired backup; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 344 337 1 2 EXPIRED DISK /u02/RMAN_To_Disk/bs3sat9u_380_1_2 345 338 1 2 EXPIRED DISK /u02/RMAN_To_Disk/bt3sata2_381_1_2 346 339 1 2 EXPIRED DISK /u02/RMAN_To_Disk/bu3satc3_382_1_2 347 340 1 2 EXPIRED DISK /u02/RMAN_To_Disk/bv3satdh_383_1_2 348 341 1 2 EXPIRED DISK /u02/RMAN_To_Disk/c03sateu_384_1_2 349 342 1 2 EXPIRED DISK /u02/RMAN_To_Disk/c13satgd_385_1_2 350 343 1 2 EXPIRED DISK /u02/RMAN_To_Disk/c23satge_386_1_2 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/bs3sat9u_380_1_2 RECID=344 STAMP=1204123823 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/bt3sata2_381_1_2 RECID=345 STAMP=1204123825 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/bu3satc3_382_1_2 RECID=346 STAMP=1204123826 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/bv3satdh_383_1_2 RECID=347 STAMP=1204123827 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/c03sateu_384_1_2 RECID=348 STAMP=1204123828 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/c13satgd_385_1_2 RECID=349 STAMP=1204123829 deleted backup piece backup piece handle=/u02/RMAN_To_Disk/c23satge_386_1_2 RECID=350 STAMP=1204123830 Deleted 7 EXPIRED objects RMAN>
RMAN's LIST BACKUP command on srv1 does list the Copy #2 backups to filesystem /u02/RMAN_To_Disk that exist only on srv2.
However, the CROSSCHECK BACKUP command marks them as "EXPIRED" as it cannot "see" (i.e find) the Backup Pieces on srv1.
I can "reCatalog" them from srv2.
[oracle@srv2 RMAN_To_Disk]$ pwd /u02/RMAN_To_Disk [oracle@srv2 RMAN_To_Disk]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 18 15:12:33 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> crosscheck backup completed after "trunc(sysdate)"; crosscheck backup completed after "trunc(sysdate)"; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943 RECID=337 STAMP=1204122943 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 RECID=338 STAMP=1204122946 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 RECID=339 STAMP=1204123012 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057 RECID=340 STAMP=1204123057 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103 RECID=341 STAMP=1204123102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149 RECID=342 STAMP=1204123149 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 RECID=343 STAMP=1204123150 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831 RECID=351 STAMP=1204123831 Crosschecked 8 objects RMAN> RMAN> catalog start with '/u02/RMAN_To_Disk'; catalog start with '/u02/RMAN_To_Disk'; searching for all files that match the pattern /u02/RMAN_To_Disk List of Files Unknown to the Database ===================================== File Name: /u02/RMAN_To_Disk/bs3sat9u_380_1_2 File Name: /u02/RMAN_To_Disk/bt3sata2_381_1_2 File Name: /u02/RMAN_To_Disk/bu3satc3_382_1_2 File Name: /u02/RMAN_To_Disk/bv3satdh_383_1_2 File Name: /u02/RMAN_To_Disk/c03sateu_384_1_2 File Name: /u02/RMAN_To_Disk/c13satgd_385_1_2 File Name: /u02/RMAN_To_Disk/c23satge_386_1_2 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u02/RMAN_To_Disk/bs3sat9u_380_1_2 File Name: /u02/RMAN_To_Disk/bt3sata2_381_1_2 File Name: /u02/RMAN_To_Disk/bu3satc3_382_1_2 File Name: /u02/RMAN_To_Disk/bv3satdh_383_1_2 File Name: /u02/RMAN_To_Disk/c03sateu_384_1_2 File Name: /u02/RMAN_To_Disk/c13satgd_385_1_2 File Name: /u02/RMAN_To_Disk/c23satge_386_1_2 RMAN> RMAN> crosscheck backup completed after "trunc(sysdate)"; crosscheck backup completed after "trunc(sysdate)"; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143542_0.473.1204122943 RECID=337 STAMP=1204122943 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/bs3sat9u_380_1_2 RECID=352 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.442.1204122947 RECID=338 STAMP=1204122946 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/bt3sata2_381_1_2 RECID=353 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/1476C653214704CFE0635A38A8C08494/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.453.1204123013 RECID=339 STAMP=1204123012 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/bu3satc3_382_1_2 RECID=354 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/339BC12E8011538DE0635A38A8C0C92A/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.460.1204123057 RECID=340 STAMP=1204123057 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/bv3satdh_383_1_2 RECID=355 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/14769E258FBB5FD8E0635A38A8C09D43/BACKUPSET/2025_06_18/nnndf0_tag20250618t143546_0.349.1204123103 RECID=341 STAMP=1204123102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/c03sateu_384_1_2 RECID=356 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/BACKUPSET/2025_06_18/annnf0_tag20250618t143909_0.492.1204123149 RECID=342 STAMP=1204123149 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/c13satgd_385_1_2 RECID=357 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123150.388.1204123151 RECID=343 STAMP=1204123150 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u02/RMAN_To_Disk/c23satge_386_1_2 RECID=358 STAMP=1204125222 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+FRA/RACDB/AUTOBACKUP/2025_06_18/s_1204123831.443.1204123831 RECID=351 STAMP=1204123831 Crosschecked 15 objects RMAN>
Thus even though I executed a "DELETE" from srv1, I can re-Catalog them because the BackupPieces of these Copy #2 Backups still exist on filesystem /u02/RMAN_To_Disk on srv2.
Categories: DBA Blogs
Backup and Restore a Standby Database
I have seen some I.T. managers that decide to backup only the Primary Database and not the Standby. The logic is "if the Storage or Server for the Standby go down, we can rebuild the database from the Primary". OR "we haven't allocated storage / tape drive space at the Standby site" OR "our third-party backup tool does not know how to backup a Standby database and handle the warning about Archive Logs that is generated when it issues a "PLUS ARCHIVELOG" {see the warning below when I run the backup command)
Do they factor the time that is required to run Backup, Copy, Restore commands OR run the Duplicate command to rebuild the Standby ? All that while their Critical database is running without a Standby -- without a D.R. site.
Given a moderately large Database, it can be faster to restore from a "local" Backup at the Standby then to copy / duplicate across the network. Also, this method does NOT require rebuilding DataGuard Broker configuration.
Firstly, you CAN backup a Standby even while Recovery (i.e. Redo Apply) is running. The only catch is the "PLUS ARCHIVELOG" clause in "BACKUP ... DATABASE PLUS ARCHIVELOG" returns a minor error because a Standby cannot issue "ALTER SYSTEM ARCHIVE LOG CURRENT" (or "ALTER SYSTEM SWITCH LOGFILE")
Here's my Backup command at the Standby (while Redo Apply -- i.e. Media Recovery -- is running) without issuing a CANCEL RECOVERY.
RMAN> backup as compressed backupset 2> database 3> format '/tmp/STDBY_Backup/DB_DataFiles_%U.bak' 4> plus archivelog 5> format '/tmp/STDBY_Backup/DB_ArchLogs_%U.bak'; .... .... RMAN> backup current controlfile 2> format '/tmp/STDBY_Backup/standby_controlfile.bak';
So, when I run the Backup, it starts of with and also ends with :
RMAN-06820: warning: failed to archive current log at primary database cannot connect to remote database .... .... .... RMAN-06820: warning: failed to archive current log at primary database cannot connect to remote database using channel ORA_DISK_1 specification does not match any archived log in the repository backup cancelled because there are no files to backup
because it cannot issue an "ALTER DATABASE ARCHIVE LOG COMMAND" -- which can only be done at a Primary. These warnings do not trouble me.
A LIST BACKUP command at the Standby *does* show Backups created locally (it will not show Backups at the Primary unless I connect to the same Recovery Catalog that is being used by the Primary) ( I have excluded listing each ArchiveLog / Datafile from the output here)
So I can confirm that I have *local* backups (including ArchiveLogs present at the Standby and backed up before the Datafile backup begins). The last ArchiveLog backed up at the Standby is SEQ#394 for Thread#1 and SEQ#339 for Thread#2
RMAN> list backup;
list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
311 44.10M DISK 00:00:02 01-MAY-25
BP Key: 311 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074832
Piece Name: /tmp/STDBY_Backup/DB_ArchLogs_ad3objeg_333_1_1.bak
List of Archived Logs in backup set 311
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 393 11126161 01-MAY-25 11126287 01-MAY-25
1 394 11126287 01-MAY-25 11127601 01-MAY-25
...
...
2 338 11126158 01-MAY-25 11126290 01-MAY-25
2 339 11126290 01-MAY-25 11127596 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
312 Full 1.07G DISK 00:00:57 01-MAY-25
BP Key: 312 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074835
Piece Name: /tmp/STDBY_Backup/DB_DataFiles_ae3objej_334_1_1.bak
List of Datafiles in backup set 312
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
....
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
313 Full 831.00M DISK 00:00:43 01-MAY-25
BP Key: 313 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074835
Piece Name: /tmp/STDBY_Backup/DB_DataFiles_af3objgk_335_1_1.bak
List of Datafiles in backup set 313
Container ID: 3, PDB Name: PDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
....
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
314 Full 807.77M DISK 00:00:42 01-MAY-25
BP Key: 314 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074835
Piece Name: /tmp/STDBY_Backup/DB_DataFiles_ag3obji1_336_1_1.bak
List of Datafiles in backup set 314
Container ID: 5, PDB Name: TSTPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
....
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
315 Full 807.75M DISK 00:00:43 01-MAY-25
BP Key: 315 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074835
Piece Name: /tmp/STDBY_Backup/DB_DataFiles_ah3objje_337_1_1.bak
List of Datafiles in backup set 315
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
....
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
317 Full 19.58M DISK 00:00:01 01-MAY-25
BP Key: 317 Status: AVAILABLE Compressed: NO Tag: TAG20250501T075522
Piece Name: /tmp/STDBY_Backup/standby_controlfile.bak
Standby Control File Included: Ckp SCN: 11128626 Ckp time: 01-MAY-25
RMAN>
So I can confirm that I have *local* backups (including ArchiveLogs present at the Standby and backed up before the Datafile backup begins). The last ArchiveLog backed up at the Standby is SEQ#394 for Thread#1 and SEQ#339 for Thread#2
Meanwhile, the Standby has already applied subsequent ArchiveLogs as Recovery had not been cancelled.
Now I simulate loss / corruption of the filesystem holding the Standby datafiles and controlfiles and attempt a Restore (if the Standby Redo Logs are also lost, I must add them again later before I resume recovery).
I do a SHUTDOWN ABORT at the Standby if he instance seems to be running.
(not shown here)
First I stop Redo Shipping from the Primary
Next I Restore the *standby* controlfile at my Standby server (note that I connect to "target" and specify "standby controlfile"). Note : If my SPFILE or PFILE is not available at the Standby, I have to restore that as well before I STARTUP NOMOUNT.
In this case, the Standby Controlfile backup was taken *after* the Datafile and ArchiveLog backups, so this Controlfile is already "aware" of the backups (they are already included in the controlfile). Neverthless, I can do some verification : ( I have excluded listing each ArchiveLog / Datafile from the output here)
For good measure, I can also verify that this "database" is a Standby (only the controlfile is presently restored" is a *Standby Database* (that a database is a Primary or a Standby is information in the *Controlfile*, not in the Datafiles)
I can return to RMAN and RESTORE the Database. (I still invoke RMAN to connect to "target", not "auxiliary"
Next, I restore the ArchiveLogs that I have in the local backup instead of having to wait for them to be shipped from the Primary during the Recover Phase
DGMGRL> connect sys Password: Connected to "RACDB" Connected as SYSDBA. DGMGRL> EDIT DATABASE 'RACDB' SET STATE='TRANSPORT-OFF'; Succeeded. DGMGRL>
Next I Restore the *standby* controlfile at my Standby server (note that I connect to "target" and specify "standby controlfile"). Note : If my SPFILE or PFILE is not available at the Standby, I have to restore that as well before I STARTUP NOMOUNT.
[oracle@stdby ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 1 08:27:23 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; startup nomount; 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 RMAN> restore standby controlfile from '/tmp/STDBY_Backup/standby_controlfile.bak'; restore standby controlfile from '/tmp/STDBY_Backup/standby_controlfile.bak'; Starting restore at 01-MAY-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=310 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/Standby_DB/oradata/control01.ctl output file name=/Standby_DB/FRA/control02.ctl Finished restore at 01-MAY-25 RMAN>I am now ready to CATALOG the Backups and RESTORE the Database
RMAN> alter database mount; alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> catalog start with '/tmp/STDBY_Backup'; catalog start with '/tmp/STDBY_Backup'; Starting implicit crosscheck backup at 01-MAY-25 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK Crosschecked 13 objects Finished implicit crosscheck backup at 01-MAY-25 Starting implicit crosscheck copy at 01-MAY-25 using channel ORA_DISK_1 Finished implicit crosscheck copy at 01-MAY-25 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /tmp/STDBY_Backup List of Files Unknown to the Database ===================================== File Name: /tmp/STDBY_Backup/standby_controlfile.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/STDBY_Backup/standby_controlfile.bak RMAN>
In this case, the Standby Controlfile backup was taken *after* the Datafile and ArchiveLog backups, so this Controlfile is already "aware" of the backups (they are already included in the controlfile). Neverthless, I can do some verification : ( I have excluded listing each ArchiveLog / Datafile from the output here)
RMAN> list backup ;
list backup ;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
311 44.10M DISK 00:00:02 01-MAY-25
BP Key: 311 Status: AVAILABLE Compressed: YES Tag: TAG20250501T074832
Piece Name: /tmp/STDBY_Backup/DB_ArchLogs_ad3objeg_333_1_1.bak
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
312 Full 1.07G DISK 00:00:57 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
313 Full 831.00M DISK 00:00:43 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
314 Full 807.77M DISK 00:00:42 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
315 Full 807.75M DISK 00:00:43 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
316 Full 19.61M DISK 00:00:01 01-MAY-25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
317 Full 19.58M DISK 00:00:01 01-MAY-25
BP Key: 317 Status: AVAILABLE Compressed: NO Tag: TAG20250501T075522
Piece Name: /tmp/STDBY_Backup/standby_controlfile.bak
Standby Control File Included: Ckp SCN: 11128626 Ckp time: 01-MAY-25
RMAN>
For good measure, I can also verify that this "database" is a Standby (only the controlfile is presently restored" is a *Standby Database* (that a database is a Primary or a Standby is information in the *Controlfile*, not in the Datafiles)
RMAN> exit exit RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/host_4144547424_110/trace/ora_4560_140406053321216.trc Recovery Manager complete. [oracle@stdby ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 1 08:37:54 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 open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBY SQL>
I can return to RMAN and RESTORE the Database. (I still invoke RMAN to connect to "target", not "auxiliary"
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 [oracle@stdby ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 1 08:40:32 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> RMAN> restore database; restore database; Starting restore at 01-MAY-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=2 device type=DISK 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 00001 to /Standby_DB/oradata/STDBY/datafile/o1_mf_system_m33j9fqn_.dbf ... ... ... channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_system_m33jb79n_.dbf channel ORA_DISK_1: restoring datafile 00006 to /Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_sysaux_m33jbbgz_.dbf channel ORA_DISK_1: restoring datafile 00008 to /Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_undotbs1_m33jbgrs_.dbf channel ORA_DISK_1: reading from backup piece /tmp/STDBY_Backup/DB_DataFiles_ah3objje_337_1_1.bak channel ORA_DISK_1: piece handle=/tmp/STDBY_Backup/DB_DataFiles_ah3objje_337_1_1.bak tag=TAG20250501T074835 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 01-MAY-25 RMAN>
Next, I restore the ArchiveLogs that I have in the local backup instead of having to wait for them to be shipped from the Primary during the Recover Phase
RMAN> restore archivelog from time "trunc(sysdate)";
restore archivelog from time "trunc(sysdate)";
Starting restore at 01-MAY-25
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=391
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=392
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=336
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=337
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=338
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=393
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=394
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=339
channel ORA_DISK_1: reading from backup piece /tmp/STDBY_Backup/DB_ArchLogs_ad3objeg_333_1_1.bak
channel ORA_DISK_1: piece handle=/tmp/STDBY_Backup/DB_ArchLogs_ad3objeg_333_1_1.bak tag=TAG20250501T074832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01-MAY-25
RMAN>
RMAN> list archivelog all completed after "trunc(sysdate)";
list archivelog all completed after "trunc(sysdate)";
List of Archived Log Copies for database with db_unique_name STDBY
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
675 1 391 A 27-APR-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_391_n16fcchs_.arc
667 1 391 A 27-APR-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_391_n169cng5_.arc
682 1 392 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_392_n16fcbh7_.arc
670 1 392 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_392_n169fh7s_.arc
678 1 393 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_393_n16fcckd_.arc
671 1 393 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_393_n169g77l_.arc
677 1 394 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_394_n16fccjb_.arc
674 1 394 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_1_394_n169my72_.arc
680 2 336 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_336_n16fccnv_.arc
668 2 336 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_336_n169d0fm_.arc
681 2 337 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_337_n16fcbhy_.arc
669 2 337 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_337_n169fh6j_.arc
679 2 338 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_338_n16fccm6_.arc
672 2 338 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_338_n169g790_.arc
676 2 339 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_339_n16fcchw_.arc
673 2 339 A 01-MAY-25
Name: /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_339_n169mxfp_.arc
RMAN>
(the output shows duplicate entries if either the ArchiveLogs were already present at the Standby OR the Restore was executed twice)So, I also have the ArchiveLogs now.
I add Standby Logs first (Add for each Thread in the Primary, and at the same size as Online Logs at the Primary)
Now I can begin Recovery of the Standby
I resume Redo Shipping from the Primary
RMAN> exit
exit
sqlRMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/host_4144547424_110/trace/ora_5380_139777366395392.trc
Recovery Manager complete.
[oracle@stdby ~]$ sqlplus / as sysdba
SQL> select thread#, group# from v$standby_log;
THREAD# GROUP#
---------- ----------
1 5
2 6
0 7
1 8
1 9
1 10
2 11
2 12
8 rows selected.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database drop standby logfile group 8;
alter database drop standby logfile group 8
*
ERROR at line 1:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/Standby_DB/FRA/STDBY/onlinelog/o1_mf_8_mb6rdbos_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00312: online log 8 thread 1: '/Standby_DB/oradata/STDBY/onlinelog/o1_mf_8_mb6rd9h8_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SQL> alter database drop standby logfile group 9;
Database altered.
SQL> alter database drop standby logfile group 10;
Database altered.
SQL> alter database drop standby logfile group 11;
alter database drop standby logfile group 11
*
ERROR at line 1:
ORA-00313: open failed for members of log group 11 of thread 2
ORA-00312: online log 11 thread 2: '/Standby_DB/FRA/STDBY/onlinelog/o1_mf_11_mb6rf8ob_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00312: online log 11 thread 2: '/Standby_DB/oradata/STDBY/onlinelog/o1_mf_11_mb6rf7hs_.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SQL> alter database drop standby logfile group 12;
Database altered.
SQL>
SQL> select thread#, group# from v$standby_log;
THREAD# GROUP#
---------- ----------
1 8
2 11
SQL>
SQL> alter database clear logfile group 8;
Database altered.
SQL> alter database clear logfile group 11;
Database altered.
SQL>
SQL> alter database drop standby logfile group 8;
Database altered.
SQL> alter database drop standby logfile group 11;
Database altered.
SQL> select thread#, group# from v$standby_log;
no rows selected
SQL>
SQL> alter database add standby logfile thread 1 size 512M;
Database altered.
SQL> alter database add standby logfile thread 1 size 512M;
Database altered.
SQL> alter database add standby logfile thread 2 size 512M;
Database altered.
SQL> alter database add standby logfile thread 2 size 512M;
Database altered.
SQL> alter database add standby logfile thread 2 size 512M;
Database altered.
SQL>
SQL> select thread#, group# from v$standby_log order by 1,2;
THREAD# GROUP#
---------- ----------
1 5
1 6
1 7
2 8
2 9
2 10
6 rows selected.
SQL>
I have to clear and then drop and recreate one Standby Log of each Thread that were last being used just before all the files were lost -- so the controlfile expected Group 8 and Group 11 to be present. These were the entries in the alert log for the last set of Recover commands before the storage was lost :2025-05-01T08:10:41.554409+00:00 Recovery of Online Redo Log: Thread 2 Group 11 Seq 343 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_11_mb6rf7hs_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_11_mb6rf8ob_.log 2025-05-01T08:10:41.557828+00:00 ARC1 (PID:1813): Archived Log entry 680 added for B-1164519547.T-1.S-397 LOS:0x0000000000a9f8a3 NXS:0x0000000000a9f8d2 NAB:12 ID 0x46c5be03 LAD:1 2025-05-01T08:10:41.563027+00:00 rfs (PID:1825): Selected LNO:8 for T-1.S-398 dbid 1162136313 branch 1164519547 2025-05-01T08:10:41.642227+00:00 PR00 (PID:1863): Media Recovery Waiting for T-1.S-398 (in transit) 2025-05-01T08:10:41.642508+00:00 Recovery of Online Redo Log: Thread 1 Group 8 Seq 398 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_8_mb6rd9h8_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_8_mb6rdbos_.log 2025-05-01T08:14:02.648081+00:00 Shutting down ORACLE instance (abort) (OS id: 3584)
Now I can begin Recovery of the Standby
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> 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 database recover managed standby database using current logfile disconnect from session; Database altered. SQL> exit
I resume Redo Shipping from the Primary
[oracle@srv1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu May 1 09:11:56 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 sys
Password:
Connected to "RACDB"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE 'RACDB' SET STATE='TRANSPORT-ON';
Succeeded.
DGMGRL>
DGMGRL> show configuration;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 34 seconds ago)
DGMGRL> show configuration lag;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 37 seconds ago)
DGMGRL>
Note : I have to wait for a few seconds to a few minutes for the SHOW CONFIGURATION and SHOW CONFIGURATION LAG commands to return the correct information. Initially, they may show that there are errors but once Primary and Standby are "talking to each other", these errors would clear.Now my Standby is syncing with the Primary
2025-05-01T09:19:30.530588+00:00 Recovery of Online Redo Log: Thread 2 Group 8 Seq 344 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_8_n16gb9wm_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_8_n16gbb4m_.log 2025-05-01T09:19:30.611573+00:00 rfs (PID:7642): krsr_rfs_atc: Identified database type as 'PHYSICAL': Client is ASYNC (PID:11557) 2025-05-01T09:19:30.623795+00:00 rfs (PID:7642): Selected LNO:5 for T-1.S-399 dbid 1162136313 branch 1164519547 2025-05-01T09:19:30.631133+00:00 PR00 (PID:7486): Media Recovery Waiting for T-1.S-399 (in transit) 2025-05-01T09:19:30.631475+00:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 399 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_5_n16g90qv_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_5_n16g910n_.log 2025-05-01T09:20:51.263052+00:00 ARC2 (PID:7470): Archived Log entry 691 added for B-1164519547.T-2.S-344 LOS:0x0000000000aa394b NXS:0x0000000000aa3b02 NAB:102 ID 0x46c5be03 LAD:1 2025-05-01T09:20:51.274060+00:00 rfs (PID:7640): Selected LNO:8 for T-2.S-345 dbid 1162136313 branch 1164519547 2025-05-01T09:20:51.285312+00:00 PR00 (PID:7486): Media Recovery Log /Standby_DB/FRA/STDBY/archivelog/2025_05_01/o1_mf_2_344_n16h7m85_.arc PR00 (PID:7486): Media Recovery Waiting for T-2.S-345 (in transit) 2025-05-01T09:20:51.387005+00:00 Recovery of Online Redo Log: Thread 2 Group 8 Seq 345 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_8_n16gb9wm_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_8_n16gbb4m_.log 2025-05-01T09:20:51.433894+00:00 ARC0 (PID:7462): Archived Log entry 692 added for B-1164519547.T-1.S-399 LOS:0x0000000000aa394e NXS:0x0000000000aa3b06 NAB:265 ID 0x46c5be03 LAD:1 2025-05-01T09:20:51.445431+00:00 rfs (PID:7642): Selected LNO:5 for T-1.S-400 dbid 1162136313 branch 1164519547 2025-05-01T09:20:51.514317+00:00 PR00 (PID:7486): Media Recovery Waiting for T-1.S-400 (in transit) 2025-05-01T09:20:51.514664+00:00 Recovery of Online Redo Log: Thread 1 Group 5 Seq 400 Reading mem 0 Mem# 0: /Standby_DB/oradata/STDBY/onlinelog/o1_mf_5_n16g90qv_.log Mem# 1: /Standby_DB/FRA/STDBY/onlinelog/o1_mf_5_n16g910n_.logI see that the SEQ# have already advanced to 399 and 345 for Thread 1 and 2 respectively.
Categories: DBA Blogs
Flashback a Pluggable Database in RAC with Data Guard
I have published a new video Flashback PDB in RAC with DG.
Here are some important considerations / commands when you want to use Flashback :
1. Ensure that you have FLASHBACK_ON in the Database (at both Primary and Standby). If you don't have FLASHBACK_ON, ensure that you create the RESTORE POINT WITH GUARANTEE FLASHBACK DATABASE at both Primary and Standby. (In the latter case, the Flashback SCN / Point In Time of the Standby must be lower than that of the Primary).
3. Monitor the FRA (Recovery Area) (V$FLASH_RECOVERY_AREA_USAGE) and Oldest Flashback Time (in V$FLASHBACK_DATABASE_LOG). If necessary, increase DB_FLASHBACK_RETENTION_TARGET and DB_RECOVERY_FILE_DEST_SIZE to ensure that you don't run out of space for the Flashback Logs !
2. If you have used dgmgrl -- i.e. Data Guard Broker Configuration, all commands to Stop Redo Shipping / Resume Redo Shipping, Stop Redo Apply / Resume Redo Apply must be issued through dgmgrl. Do not use SQL commands.
3. At the Standby the Flashback command uses the "STANDBY" clause to identify that you are doing a Flashback for a Standby.
Some older Blog Posts on FLASHBACK DATABASE are at Blog Series on Flashback Database.
Categories: DBA Blogs
Video on Client Connectivity options for RAC
Last month, I had posted a new Video on Client Connectivity options in RAC.
It shows different methods of Client (ie. Applications as well !) -- not just using SCAN Name in an Oracle RAC Cluster
On Client-Side Load Balancing see the sections "Generic Database Clients" and "Client-Side Load Balancing" in the Chapter on Workload Management athttps://docs.oracle.com/en/database/oracle/oracle-database/19/racad/workload-management-with-dynamic-database-services.html
A few blog posts about services in RAC :
https://hemantoracledba.blogspot.com/2014/07/rac-commands-1-viewing-configuration.html
https://hemantoracledba.blogspot.com/2014/07/rac-commands-2-updating-configuration.html
https://hemantoracledba.blogspot.com/2017/04/12cr1-rac-posts-9-adding-service-to-pdb.html
https://hemantoracledba.blogspot.com/2024/02/adding-pdb-and-service-to-rac-database.html
Categories: DBA Blogs
Domains in 23ai Schema Development
I had heard of Data Use Case Domains in 23ai. However, the New Features nd Database Concepts documentation didn't provide enough examples for me to build on.
However, this blog post by Ulrike Schwinn (which was shared by @thatjeffsmith on X) helped me explore domains.
In this demo, I am using the Pre-Seeded Domains. However, you can see the example posted by Ulrike Schwimm or even read in the Database Concepts documentation to help build your own custom Domains.
A Data Use Case Domain is like defining a Custom DataType such that only valid values are permitted. The Domain name can be a self-identifier (just as "DATE" or "NUMBER" identifies the type of data being stored).
Here is my demonstration (I also use the Annotations feature -- the Data Use Case Domains documentation links above also lead to this feature)
SQL> set pages600 linesize 132
SQL> col contact_person format a32
SQL> col contact_email format a24
SQL>
SQL> drop table forex_rates_contacts;
Table dropped.
SQL>
SQL>
SQL> create table forex_rates_contacts
2 (
3 country_iso_code varchar2(3) domain country_code_d, -- preseeded SYS domain
4 currency_code varchar2(3) domain currency_code_d, -- preseeded SYS domain
5 contact_person varchar2(128),
6 contact_email varchar2(4000) domain email_d -- preseed SYS domain
7 )
8 annotations (display 'Forex Contact Persons')
9 /
Table created.
SQL>
SQL> desc forex_rates_contacts
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
COUNTRY_ISO_CODE VARCHAR2(3) SYS.COUNTRY_CODE_D
CURRENCY_CODE VARCHAR2(3) SYS.CURRENCY_CODE_D
CONTACT_PERSON VARCHAR2(128)
CONTACT_EMAIL VARCHAR2(4000) SYS.EMAIL_D
SQL>
SQL>
SQL> set long 1000
SQL> set longc 1000
SQL> set serveroutput on
SQL>
SQL> rem FROM clause is no longer required in 23ai
SQL> select dbms_metadata.get_ddl('TABLE','FOREX_RATES_CONTACTS','HEMANT');
DBMS_METADATA.GET_DDL('TABLE','FOREX_RATES_CONTACTS','HEMANT')
------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "HEMANT"."FOREX_RATES_CONTACTS"
( "COUNTRY_ISO_CODE" VARCHAR2(3) DOMAIN "SYS"."COUNTRY_CODE_D",
"CURRENCY_CODE" VARCHAR2(3) DOMAIN "SYS"."CURRENCY_CODE_D",
"CONTACT_PERSON" VARCHAR2(128),
"CONTACT_EMAIL" VARCHAR2(4000) DOMAIN "SYS"."EMAIL_D"
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
ANNOTATIONS("DISPLAY" 'Forex Contact Persons')
SQL>
SQL>
SQL>
SQL> rem MULTI-ROW Insert
SQL> insert into forex_rates_contacts
2 values
3 ('US','USD','Mr Unknown','unknown@nowhere.gov'),
4 ('IN','INR','Someone at RBI','someone@rbi.gov.in')
5 /
2 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
SQL>
SQL> -- Note that the country_code_d and currency_code_d do not check validity against really ISO codes
SQL> -- thus, it does not disallow "ZZ" and "ZZZ"
SQL> insert into forex_rates_contacts
2 values
3 ('ZZ','ZZZ','Mr Unknown','unknown@nowhere.zz')
4 /
1 row created.
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
ZZ ZZZ Mr Unknown unknown@nowhere.zz
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- But the rules for email validation are encoded
SQL> insert into forex_rates_contacts
2 values
3 ('UK','GBP','Mr Someone','someone@x')
4 /
insert into forex_rates_contacts
*
ERROR at line 1:
ORA-11534: check constraint (HEMANT.SYS_C0013464) involving column CONTACT_EMAIL due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> select * from forex_rates_contacts
2 order by country_iso_code
3 /
COU CUR CONTACT_PERSON CONTACT_EMAIL
--- --- -------------------------------- ------------------------
IN INR Someone at RBI someone@rbi.gov.in
US USD Mr Unknown unknown@nowhere.gov
ZZ ZZZ Mr Unknown unknown@nowhere.zz
SQL>
SQL> spool off
I haven't added my own custom Domains but used the PreSeeded domains for Country, Currency and Email. Look at "10.1.12 Built-In Use Case Domains" in the documentation.
Categories: DBA Blogs
23ai New Feature : Partition HIGH_VALUE in JSON format
A quick demonstration of the new HIGH_VALUE_JSON column in the USER_TAB_PARTITIONS view in 23ai :
[oracle@localhost Hemant]$ sqlplus hemant/hemant@freepdb1
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sun Jan 26 10:07:09 2025
Version 23.6.0.24.10
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Sun Jan 26 2025 10:05:18 +00:00
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
SQL> @demo_part_high_value.sql
SQL> set pages600 linesize 132
SQL> set long 10000
SQL>
SQL> spool demo_part_high_value
SQL>
SQL> -- 23ai has two new columns in the USER_TAB_PARTTIIONS view
SQL> -- HIGH_VALUE_CLOB and HIGH_VALUE_JSON
SQL> --- unlike HIGH_VALUE which is a LONG, these two can be used programmatically
SQL> -- here I show HIGH_VALUE_JSON along with the HIGH_VALUE
SQL>
SQL> set pages600 linesize 132
SQL> set long 10000
SQL> col partition_name format a8 hea 'P_Name'
SQL> col high_value format a56 trunc hea 'High_Value_LONG' trunc
SQL> col high_value_json format a48 hea 'High_Value_JSON'
SQL>
SQL>
SQL> drop table hkc_test_intvl;
Table dropped.
SQL>
SQL> create table hkc_test_intvl
2 (date_column date,
3 data_column varchar2(50))
4 partition by range (date_column)
5 interval (numtoyminterval(1,'MONTH'))
6 (partition P_1 values less than (to_date('01-FEB-2024','DD-MON-YYYY')))
7 /
Table created.
SQL>
SQL>
SQL> insert into hkc_Test_intvl
2 values (to_date('15-AUG-2024','DD-MON-YYYY'), 'August Row')
3 /
1 row created.
SQL>
SQL>
SQL> insert into hkc_test_intvl
2 values (to_date('15-OCT-2024','DD-MON-YYYY'),'October Row')
3 /
1 row created.
SQL>
SQL> insert into hkc_test_intvl
2 values (to_date('15-DEC-2024','DD-MON-YYYY'),'December Row')
3 /
1 row created.
SQL>
SQL> select partition_name, high_value, high_value_json
2 from user_tab_partitions
3 where table_name = 'HKC_TEST_INTVL'
4 /
P_Name High_Value_LONG High_Value_JSON
-------- -------------------------------------------------------- ------------------------------------------------
P_1 TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' {"high_value":"2024-02-01T00:00:00"}
SYS_P447 TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' {"high_value":"2024-09-01T00:00:00"}
SYS_P448 TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' {"high_value":"2024-11-01T00:00:00"}
SYS_P449 TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' {"high_value":"2025-01-01T00:00:00"}
SQL>
SQL>
SQL> spool off
Earlier, HIGH_VALUE was presented as a LONG.
23ai adds two columns to the USER_TAB_PARTITIONS data dictionary view :
HIGH_VALUE_CLOB
and
HIGH_VALUE_JSON
These make it easier to query the data dictionary, for example, for Partition Life Cycle management.
Categories: DBA Blogs
Querying the Alert log in RAC
Although v$diag_alert_ext works for a single instance, there is no corresponding gv$ view for RAC.
However, the gv$ function can be used to execute a cursor across all instances. Here I query for the alert log in the two nodes srv1 and srv2 and order the results by timestamp to capture messages in the last 15 minutes : Note all the activity that happens when I CLOSE and later OPEN a PDB in one node, resulting in the other node also responding to maintain Global Resources.
SQL> l 1 select * from table(gv$(cursor 2 (select originating_timestamp, host_id, con_id, message_text 3 from v$diag_alert_ext 4 where originating_timestamp > sysdate - 0.25/24 -- time filter 5 and message_text like '%' -- message filter 6 ) 7 ) 8 ) 9* order by 1,2,3 SQL> / ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- HOST_ID CON_ID ------------------------------------------------------------------- ---------- MESSAGE_TEXT ------------------------------------------------------------------------------------------------------------------------------------ 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Thread 2 cannot allocate new log, sequence 278 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Checkpoint not complete 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Current log# 3 seq# 277 mem# 0: +DATA/RACDB/ONLINELOG/group_3.270.1164520171 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Current log# 3 seq# 277 mem# 1: +FRA/RACDB/ONLINELOG/group_3.259.1164520171 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Thread 1 cannot allocate new log, sequence 322 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Current log# 1 seq# 321 mem# 1: +FRA/RACDB/ONLINELOG/group_1.257.1164519549 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Current log# 1 seq# 321 mem# 0: +DATA/RACDB/ONLINELOG/group_1.263.1164519549 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Checkpoint not complete 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Thread 2 advanced to log sequence 278 (LGWR switch), current SCN: 10841502 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Current log# 4 seq# 278 mem# 0: +DATA/RACDB/ONLINELOG/group_4.271.1164520171 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Current log# 4 seq# 278 mem# 1: +FRA/RACDB/ONLINELOG/group_4.260.1164520173 07-JAN-25 02.23.40.976000000 PM +00:00 srv2 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_1_seq_240.500.1183975081 07-JAN-25 02.23.40.977000000 PM +00:00 srv2 1 ARC2 (PID:17940): Archived Log entry 1023 added for B-1164519547.T-2.S-277 LOS:0x0000000000a568cc NXS:0x0000000000a56d9e NAB:372 ID 0x46c5be03 LAD:1 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Thread 1 advanced to log sequence 322 (LGWR switch), current SCN: 10841505 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Current log# 2 seq# 322 mem# 1: +FRA/RACDB/ONLINELOG/group_2.258.1164519549 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Current log# 2 seq# 322 mem# 0: +DATA/RACDB/ONLINELOG/group_2.262.1164519549 07-JAN-25 02.23.41.117000000 PM +00:00 srv2 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_2_seq_221.499.1183975181 07-JAN-25 02.23.41.138000000 PM +00:00 srv1 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_2_seq_222.498.1183975205 07-JAN-25 02.23.41.139000000 PM +00:00 srv1 1 NET (PID:15641): Archived Log entry 1025 added for B-1164519547.T-1.S-321 LOS:0x0000000000a568c9 NXS:0x0000000000a56da1 NAB:369 ID 0x46c5be03 LAD:1 07-JAN-25 02.23.41.209000000 PM +00:00 srv1 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_1_seq_241.497.1183975551 07-JAN-25 02.27.41.986000000 PM +00:00 srv1 1 alter pluggable database pdb1 close 07-JAN-25 02.27.41.997000000 PM +00:00 srv1 3 Pluggable database PDB1 closing 07-JAN-25 02.27.42.004000000 PM +00:00 srv1 3 Increasing priority of 2 RS 07-JAN-25 02.27.42.011000000 PM +00:00 srv1 3 JIT: pid 19878 requesting stop 07-JAN-25 02.27.42.044000000 PM +00:00 srv1 3 Stopped service mypdb 07-JAN-25 02.27.42.208000000 PM +00:00 srv1 3 Closing sequence subsystem (3300983922). 07-JAN-25 02.27.42.332000000 PM +00:00 srv1 3 Buffer Cache flush started: 3 07-JAN-25 02.27.42.345000000 PM +00:00 srv1 3 Buffer Cache flush finished: 3 07-JAN-25 02.27.42.347000000 PM +00:00 srv2 1 Increasing priority of 2 RS 07-JAN-25 02.27.42.350000000 PM +00:00 srv1 3 queued detach DA request 0x934eafc8 for pdb 3, ospid 19878 07-JAN-25 02.27.42.351000000 PM +00:00 srv1 1 Domain Action Reconfiguration started (domid 3, new da inc 3, cluster inc 8) 07-JAN-25 02.27.42.351000000 PM +00:00 srv1 1 Instance 1 is detaching from domain 3 (lazy abort? 0, recovery member? 0) 07-JAN-25 02.27.42.352000000 PM +00:00 srv1 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.27.42.352000000 PM +00:00 srv1 1 * domain detach - domain 3 valid ? 1 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Domain Action Reconfiguration started (domid 3, new da inc 3, cluster inc 8) 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Instance 1 is detaching from domain 3 (lazy abort? 0, recovery member? 0) 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 * domain detach - domain 3 valid ? 1 07-JAN-25 02.27.42.432000000 PM +00:00 srv2 1 Non-local Process blocks cleaned out 07-JAN-25 02.27.42.434000000 PM +00:00 srv2 1 Set master node info 07-JAN-25 02.27.42.435000000 PM +00:00 srv2 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.27.42.435000000 PM +00:00 srv2 1 All grantable enqueues granted 07-JAN-25 02.27.42.437000000 PM +00:00 srv1 1 Non-local Process blocks cleaned out 07-JAN-25 02.27.42.438000000 PM +00:00 srv1 1 Set master node info 07-JAN-25 02.27.42.439000000 PM +00:00 srv1 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.27.42.440000000 PM +00:00 srv1 1 All grantable enqueues granted 07-JAN-25 02.27.42.440000000 PM +00:00 srv2 1 Domain Action Reconfiguration complete (total time 0.0 secs) 07-JAN-25 02.27.42.440000000 PM +00:00 srv2 1 Decreasing priority of 2 RS 07-JAN-25 02.27.42.444000000 PM +00:00 srv1 1 freeing the fusion rht of pdb 3 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 freeing the pdb enqueue rht 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 Decreasing priority of 2 RS 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.27.42.542000000 PM +00:00 srv1 1 Pluggable database PDB1 closed 07-JAN-25 02.27.42.571000000 PM +00:00 srv1 1 Completed: alter pluggable database pdb1 close 07-JAN-25 02.28.10.713000000 PM +00:00 srv1 1 alter pluggable database pdb1 open 07-JAN-25 02.28.10.715000000 PM +00:00 srv1 3 Pluggable database PDB1 opening in read write 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 Increasing priority of 2 RS 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 Autotune of undo retention is turned on. 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18 07-JAN-25 02.28.10.929000000 PM +00:00 srv1 3 queued attach DA request 0x934eaf60 for pdb 3, ospid 19878 07-JAN-25 02.28.10.929000000 PM +00:00 srv2 1 Increasing priority of 2 RS 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Domain Action Reconfiguration started (domid 3, new da inc 4, cluster inc 8) 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Instance 1 is attaching to domain 3 07-JAN-25 02.28.10.940000000 PM +00:00 srv2 1 Domain Action Reconfiguration started (domid 3, new da inc 4, cluster inc 8) 07-JAN-25 02.28.10.940000000 PM +00:00 srv2 1 Instance 1 is attaching to domain 3 07-JAN-25 02.28.10.941000000 PM +00:00 srv2 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.28.10.943000000 PM +00:00 srv2 1 Non-local Process blocks cleaned out 07-JAN-25 02.28.10.946000000 PM +00:00 srv2 1 Set master node info 07-JAN-25 02.28.10.947000000 PM +00:00 srv1 1 Non-local Process blocks cleaned out 07-JAN-25 02.28.10.947000000 PM +00:00 srv2 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.28.10.948000000 PM +00:00 srv1 1 Set master node info 07-JAN-25 02.28.10.949000000 PM +00:00 srv2 1 All grantable enqueues granted 07-JAN-25 02.28.10.952000000 PM +00:00 srv1 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.28.10.953000000 PM +00:00 srv1 1 All grantable enqueues granted 07-JAN-25 02.28.11.034000000 PM +00:00 srv2 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.28.11.034000000 PM +00:00 srv2 1 Decreasing priority of 2 RS 07-JAN-25 02.28.11.038000000 PM +00:00 srv1 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.28.11.038000000 PM +00:00 srv1 1 Decreasing priority of 2 RS 07-JAN-25 02.28.11.135000000 PM +00:00 srv1 3 Endian type of dictionary set to little 07-JAN-25 02.28.11.213000000 PM +00:00 srv1 3 Undo initialization recovery: err:0 start: 3329988 end: 3329989 diff: 1 ms (0.0 seconds) 07-JAN-25 02.28.11.352000000 PM +00:00 srv1 3 [19878] Successfully onlined Undo Tablespace 2. 07-JAN-25 02.28.11.352000000 PM +00:00 srv1 3 Undo initialization online undo segments: err:0 start: 3329989 end: 3330129 diff: 140 ms (0.1 seconds) 07-JAN-25 02.28.11.357000000 PM +00:00 srv1 3 Undo initialization finished serial:0 start:3329988 end:3330134 diff:146 ms (0.1 seconds) 07-JAN-25 02.28.11.361000000 PM +00:00 srv1 3 Database Characterset for PDB1 is AL32UTF8 07-JAN-25 02.28.11.627000000 PM +00:00 srv1 3 SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog) 07-JAN-25 02.28.11.891000000 PM +00:00 srv1 3 Started service mypdb/mypdb/mypdb 07-JAN-25 02.28.11.925000000 PM +00:00 srv1 3 Opening pdb with no Resource Manager plan active 07-JAN-25 02.28.12.097000000 PM +00:00 srv1 3 joxcsys_required_dirobj_exists: directory object exists with required path /u02/app/oracle/product/19.0.0/dbhome_1/javavm/admin/, pi d 19878 cid 3 07-JAN-25 02.28.12.118000000 PM +00:00 srv1 1 Pluggable database PDB1 opened read write 07-JAN-25 02.28.12.122000000 PM +00:00 srv1 1 Completed: alter pluggable database pdb1 open 07-JAN-25 02.33.26.501000000 PM +00:00 srv1 1 Control autobackup written to DISK device 07-JAN-25 02.33.26.502000000 PM +00:00 srv1 1 handle '+FRA/RACDB/AUTOBACKUP/2025_01_07/s_1189780406.497.1189780407' 93 rows selected. SQL>At 07-JAN-25 02.27.41.986000000 PM +00:00 : I issued a command to close PDB1 (CON_ID=3) on host srv1
You can also see the "Stopped service mypdb" message on srv1 at 07-JAN-25 02.27.42
Host srv2 started writing it's messages at 07-JAN-25 02.27.42.347000000 PM +00:00 from which you can see Domain Action Reconfiguration (07-JAN-25 02.27.42.351000000 PM +00:00), Global Resource Directory partially frozen (07-JAN-25 02.27.42.430000000 PM +00:00)/
Finally, srv1 logged "Completed: alter pluggable database pdb1 close" at 07-JAN-25 02.27.42.571000000 PM +00:00 (under CON_ID=1).
When I issued the "alter pluggable database pdb1 open" on srv1 at 07-JAN-25 02.28.10.713000000 PM +00:00, Domain Actions and Global Resource Directory actions were executed again on srv2
Finally, srv1 logged "Completed: alter pluggable database pdb1 open" at 07-JAN-25 02.28.12.122000000 PM +00:00
So, it is obvious that closing and opening a PDB in RAC also results in a number of steps to maintain consistency (Resources etc) across the instances.
Categories: DBA Blogs
3million PageViews, 303thousand VideoViews
This blog, begun in December 2006, has now hit a cumulative count of 3million PageViews.
This Chart shows counts from the year 2011 :
My YouTube Channel, begun in January 2014, has hit a cumulative count of 303thousand views :
A Big Thank You to all the Viewers an Subscribers.
Categories: DBA Blogs
DataPump with CheckSum in 21c and above
Oracle introduced a CheckSum parameter in 21c. Here is a demo in 23.6.
First I run the datapump export without the CheckSum:
[oracle@localhost ~]$ expdp hemant/hemant@freepdb1 schemas=HEMANT dumpfile=HEMANT Export: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:31:26 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Starting "HEMANT"."SYS_EXPORT_SCHEMA_01": hemant/********@freepdb1 schemas=HEMANT dumpfile=HEMANT Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HEMANT"."MY_LOBS" 7.9 MB 75929 rows Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is: /opt/oracle/admin/FREE/dpdump/2917EBB8705B3129E0630100007F3D2B/HEMANT.dmp Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 28 12:32:06 2024 elapsed 0 00:00:38 [oracle@localhost ~]$
Now this is an export WITH the CheckSum (after verifying that COMPATIBLE is 20.0 or higher) by specifying CHECKSUM=YES :
[oracle@localhost ~]$ sqlplus hemant/hemant@freepdb1 SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Dec 28 12:35:20 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle. All rights reserved. Last Successful login time: Sat Dec 28 2024 12:31:26 +00:00 Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.6.0.24.10 SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 23.6.0 noncdb_compatible boolean FALSE SQL> quit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.6.0.24.10 [oracle@localhost ~]$ expdp hemant/hemant@freepdb1 schemas=HEMANT dumpfile=HEMANT_With_CheckSum checksum=YES Export: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:35:55 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Starting "HEMANT"."SYS_EXPORT_SCHEMA_01": hemant/********@freepdb1 schemas=HEMANT dumpfile=HEMANT_With_CheckSum checksum=YES Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA/LOGREP Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX . . exported "HEMANT"."MY_LOBS" 7.9 MB 75929 rows Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded Generating checksums for dump file set ****************************************************************************** Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is: /opt/oracle/admin/FREE/dpdump/2917EBB8705B3129E0630100007F3D2B/HEMANT_With_CheckSum.dmp Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 28 12:36:33 2024 elapsed 0 00:00:36 [oracle@localhost ~]$
After I transfer the dumpfile to another server, I verify the CheckSum with VERIFY_ONLY=YES:
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Dec 28 12:40:54 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.6.0.24.10 SQL> create directory impdp_check as '/tmp'; Directory created. SQL> quit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.6.0.24.10 [oracle@localhost ~]$ impdp directory=impdp_check dumpfile=HEMANT_With_CheckSum.dmp verify_only=YES Import: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:42:28 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. Verifying dump file checksums Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded dump file set is complete verified checksum for dump file "/tmp/HEMANT_With_CheckSum.dmp" dump file set is consistent Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Dec 28 12:42:39 2024 elapsed 0 00:00:05 [oracle@localhost ~]$ [oracle@localhost ~]$ cd /tmp [oracle@localhost tmp]$ ls -l import.log -rw-r--r--. 1 oracle oinstall 600 Dec 28 12:42 import.log [oracle@localhost tmp]$ cat import.log ;;; Import: Release 23.0.0.0.0 - Limited Availability on Sat Dec 28 12:42:28 2024 Version 23.6.0.24.10 Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Verifying dump file checksums Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded dump file set is complete verified checksum for dump file "/tmp/HEMANT_With_CheckSum.dmp" dump file set is consistent Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Dec 28 12:42:39 2024 elapsed 0 00:00:05 [oracle@localhost tmp]$
The VERIFY_ONLY parameter verifies the file without actually importing the dump file.
The default CheckSum algorithm is SHA256.
You can override this by specifying CHECKSUM_ALGORITHM set to either of CRC32, SHA256, SHA384 or SHA512.
Categories: DBA Blogs
The GROUP BY column_position enhancement in 23ai
Oracle 23ai allows specifying a Column Position (or Alias) in the GROUP BY clause.
For backward compatibility, the "group_by_position_enabled" parameter is a new feature that defaults to FALSE.
SQL> show parameter group_by_position_enabled NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ group_by_position_enabled boolean FALSE SQL> alter session set group_by_position_enabled=TRUE; Session altered. SQL> SQL> select p.prod_name, t.day_name, sum(s.amount_sold) 2 from sh.products p, sh.times t, sh.sales s 3 where p.prod_id=s.prod_id 4 and t.time_id=s.time_id 5 and t.calendar_month_name = 'January' 6 group by 1, 2 7 order by 1, 2 8 / PROD_NAME DAY_NAME SUM(S.AMOUNT_SOLD) -------------------------------------------------- --------- ------------------ 11" Youth Field Master Glove Friday 4635.73 11" Youth Field Master Glove Monday 2903.62 11" Youth Field Master Glove Saturday 3636.85 11" Youth Field Master Glove Sunday 6602.18 11" Youth Field Master Glove Thursday 5696.37 11" Youth Field Master Glove Tuesday 2843.81 11" Youth Field Master Glove Wednesday 6072.04 11.5" Youth Triple Stripe Series Glove Friday 6695.84 11.5" Youth Triple Stripe Series Glove Monday 5436.28 11.5" Youth Triple Stripe Series Glove Saturday 5653.8 11.5" Youth Triple Stripe Series Glove Sunday 10909.86 ... ... multiple rows returned ... ... PROD_NAME DAY_NAME SUM(S.AMOUNT_SOLD) -------------------------------------------------- --------- ------------------ Wicket Keeper Gloves Thursday 1550.94 Wicket Keeper Gloves Tuesday 3049.62 Wicket Keeper Gloves Wednesday 2583.16 Wide Brim Hat Friday 189.28 Wide Brim Hat Monday 1656.35 Wide Brim Hat Saturday 1689.48 Wide Brim Hat Sunday 560.7 Wide Brim Hat Thursday 1088.44 Wide Brim Hat Tuesday 2855.67 Wide Brim Hat Wednesday 250.19 461 rows selected. SQL>
This helps developers who already use the Column Position in the ORDER BY clause and can be consistent when writing the GROUP BY clause.
The HAVING clause also supports Column Aliases.
Categories: DBA Blogs
Using Oracle's Autonomous Health Framework to get an "Insight" into a RAC Cluster
I've posted a video demonstration on using Oracle's Autonomous Health Framework ("AHF") get an "Insight" into a RAC Cluster.
AHF (Oracle Support Doc ID 2550798.1) is Oracle's current diagnostic tool which includes Orachk and TFA (Trace File Analyzer). Explore it.
Categories: DBA Blogs
Video on DataGuard Snapshot Standby
I have posted a new video on creating a "Snapshot Standby" database in DataGuard.
Typically such a Standby can be used for "destructive testing" like D.R. simulation with dummy transactions, application upgrades, schema changes.
If your concern is about the Physical Standby being unavailable for long note :
(1) Redo Shipping to the Standby can continue as ArchiveLogs (of course, this means the Standby will be lagging the Primary by the typical interval between Archive Log switches)
(2) You can configure 2 Standbys of which one continues to be a Physical Standby with real time redo shipping and the other is periodically used to test schema changes / application upgrades and even for frequent D.R. testing
I have previously posted a few Blog posts on scenarios like Multiple Standbys, Standbys with Lag, Destructive testing in Standbys. See the list of posts here.
Categories: DBA Blogs
Video on DataGuard Switchover -- with RAC and Single Instance
I've posted a demonstration of DataGuard Switchover, using RAC and Single Instance as the Primary/Standby pair.
Fundamentally, there is no difference if either or both of the databases are RAC or Single Instance.
A Switchover is a Graceful operation, with No-Data-Loss as the Primary sends the "End-Of-Redo Marker" to the Standby at which point Reversal of Roles happens. Therefore, you can execute Switchover between the two databases (servers / data centres / sites) multiple times without loss of data.
A Failover, on the other hand, involves data loss and the erstwhile Primary does not revert to a Standby role but must be recreated / refreshed as a Standby from the new Primary.
Categories: DBA Blogs
A few dgmgrl (DataGuard Broker) commands
I haven't create a video with commentary but here are a few dgmgrl commands.
In the output "RACDB" (or "racdb") is a 2-node RAC database (managed by ClusterWare) and "STDBY" (or "stdby") is a Single Node non-RAC database not using Oracle Restart. Both are 19c (19.23)
Initial Setup :
[oracle@srv1 ~]$ dgmgrl sys
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jul 27 02:31:53 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "RACDB"
Connected as SYSDBA.
DGMGRL> remove configuration;
Removed configuration
DGMGRL> create configuration racdb_dg as primary database is RACDB connect identifier is RACDB;
Configuration "racdb_dg" created with primary database "racdb"
DGMGRL> add database STDBY as connect identifier is STDBY maintained as physical;
Database "stdby" added
DGMGRL> show configuration;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
Warning: ORA-16905: The member was not enabled yet.
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 177 seconds ago)
DGMGRL> show configuration ;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
Warning: ORA-16905: The member was not enabled yet.
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 182 seconds ago)
DGMGRL>
DGMGRL> show configuration ;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 44 seconds ago)
DGMGRL>
DGMGRL> show configuration lag;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 23 seconds ago)
DGMGRL>
In the initial setup, it takes time to actually enable the databases so we see the "ORA-16095: the member was not enabled yet" warning messages.Later, it takes time for communication of the Transport and Apply lag information so I present the lag only some significant seconds later.
Note that DataGuard is configured as "Maximum Performance" mode (not Maximum Availability as I show in my previous video)
The VALIDATE commands :
GMGRL> validate database racdb;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
racdb: YES
DGMGRL> validate database stdby;
Database Role: Physical standby database
Primary Database: racdb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
racdb: On
stdby: Off
Capacity Information:
Database Instances Threads
racdb 2 2
stdby 1 2
Warning: the target standby has fewer instances than the
primary database, this may impact application performance
Managed by Clusterware:
racdb: YES
stdby: NO
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(racdb) (stdby)
1 512 MBytes 100 MBytes
2 512 MBytes 100 MBytes
DGMGRL>
Here we see some information :(1) The configuration is Ready for Swtichover (from "racdb" as Primary to "stdb")
(2) Flashback Database is configured on "racdb" but not on "stdby"
(3) Although "racdb" has two instances (two redo threads), "stdby" has only 1 instance (and redo thread) {Thus we know it is possible to have a Single Instance Standby Database for a MultiNode RAC --- I won't go into a debate / discussion on such a configuration if comments appear in response to this blog post, suffice to say that IMHO, it can make sense to do so}
(4) "racdb" is managed by ClusterWare but "stdby" is not {Thus we know that the Standby does not have to be in ClusterWare / Oracle Restart configuration}
(5) Although "racdb" has Redo Log files of 512MB, "stdby" is, unfortunately, configured with some smaller Redo Logs
Some "Verbose" Configuration Information :
DGMGRL> show configuration verbose
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverLagGraceTime = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'RACDB_CFG'
FastStartFailoverLagType = 'APPLY'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose racdb;
Database - racdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RACDB1
RACDB2
Properties:
DGConnectIdentifier = 'racdb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show instance verbose "RACDB1"
Instance 'RACDB1' of database 'racdb'
PFILE:
Properties:
HostName = 'srv1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.91)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACDB_DGMGRL)(INSTANCE_NAME=RACDB1)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/alert_RACDB1.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/drcRACDB1.log
Instance Status:
SUCCESS
DGMGRL>
Here we see some more information :(1) Timeouts (which I've left at defaults)
(2) Fast Start Failover and Observer information (which I am not using in this configuration)
(3) That Log Transport is Async, with 0 Delay and Compression Disabled.
(3) The Alert Log and DG Broker Log file location (for "racdb", I extract the information for only the first instance, "RACDB1")
Connect Identifiers :
DGMGRL> validate static connect identifier for racdb; Oracle Clusterware on database "racdb" is available for database restart. DGMGRL> validate static connect identifier for stdby; Oracle Clusterware is not configured on database "stdby". Connecting to database "stdby" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "stdby".For "racdb" being managed by ClusterWare, I had specified "RACDB" {which is the SCAN address} as the Connect Identifier (with "primary database is RACDB connect identifier is RACDB" in the first configuration command) For "stdby", not being managed by ClusterWare or Oracle Restart, it DGMGRL entry for the Listener is required in this manner :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDBY)
(SID_NAME = STDBY)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = STDBY_DGMGRL)
(SID_NAME = STDBY)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
)
)
So, if either your Primary or Standby or both are Not managed by ClusterWare or Oracle Restart, you need to have a DGMGRL entry for the Listener !
Categories: DBA Blogs
Video on OCR and OLR commands in RAC GI/ClusterWare
Last week I published a new video on OCR and OLR commands.
ocrcheck : Lists the locations of the OCR and checks for corruption (run as root to check for logical corruption as well)
ocrconfig -add DG Name (e.g. ocrconfig -add +DATA) : Adds a new copy of the OCR in the stated ASM DG
ocrconfig -delete DG Name : Deletes a copy of the OCR from the ASM DG
cat /etc/oracle/olr.loc : Shows the location of the OLR
ocrcheck -local : Checks the OLR
ocrconfig -showbackup : Shows the default location of OCR backups
ocrconfig -manualbackup : Create a manual backup of the OCR
(use asmcmd to copy the backup out from ASM to Filesystem)
ocrconfig -local -showbackuploc : Shows the location of OLR backups
ocrconfig -local -manualbackup : Create a manual backup of the OLR
ocrconfig -local -export : Create an Export backup of the OLR
Categories: DBA Blogs