- Introduction: RMAN provides us facility to take the incremental backup from a specified System Change Number (SCN). By using this we can recover the Standby Database where archive gap is huge or archives are missing. This method allows us to efficiently resynchronize a standby database with minimal downtime and reduced network impact.
- Prerequisites:
- Oracle Data Guard should be configured.
- Disable DG broker if any as it will start MRP automatically.
- Environment:
- Now proceed for recovery activity step by step:
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
1. Check difference of SCN at DB and file level between Primary and Standby
At Primary:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDC PRIMARY READ WRITE
SQL>
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC PRIMARY 1 228 217 30-MAR-2025 15:59:50 11
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4247921
SQL> select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORCL/system01.dbf 4247359
3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf 4247359
4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 4247359
5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCL/users01.dbf 4247359
8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf 4247359
10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf 4247359
11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf 4247359
12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf 4247359
13 /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf 4247359
12 rows selected.
SQL>
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED
SQL>
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDR PHYSICAL STANDBY 1 228 217 30-MAR-2025 15:59:50 11
SQL>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4125211
SQL>
SQL> col file for a60
SQL> select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 4125212
3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 4125212
4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 4125212
5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 4125212
8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 4125212
10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 4125212
11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 4125212
12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 4125212
13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 4125212
12 rows selected.
2. Now we need to take the incremental backup on Primary with the SCN of Standby.
Please note: Always consider the earlier SCN for backup instead of the exact available SCN at Standby. As for example the available SCN is 4125211 so we need to take backup from SCN 4120211.
[oracle@source rmanscn]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 10:57:53 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1718803653)
RMAN>
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLDC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM YES /u01/app/oracle/oradata/ORCL/system01.dbf
3 920 SYSAUX NO /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 710 UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5 450 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/app/oracle/oradata/ORCL/users01.dbf
8 230 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM YES /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX NO /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS NO /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST NO /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 181 TEMP 32767 /u01/app/oracle/oradata/ORCL/temp01.dbf
2 174 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCL/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
RMAN>
RMAN> run
{
allocate channel c1 type disk;
backup incremental from scn 4120211 database format '/u01/app/oracle/rmanscn/database_%d_%u_%s';
backup current controlfile for standby format '/u01/app/oracle/rmanscn/stnbyControl_%d_%u_%s';
release channel c1;
}
allocated channel: c1
channel c1: SID=285 device type=DISK
Starting backup at 11-APR-25
RMAN-06755: warning: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 2345308 instead
RMAN-06755: warning: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 2345308 instead
RMAN-06755: warning: datafile 8: incremental-start SCN is too recent; using checkpoint SCN 2345308 instead
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel c1: starting piece 1 at 11-APR-25
channel c1: finished piece 1 at 11-APR-25
piece handle=/u01/app/oracle/rmanscn/database_ORCL_423mkimi_130 tag=TAG20250411T105826 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:16
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
channel c1: starting piece 1 at 11-APR-25
channel c1: finished piece 1 at 11-APR-25
piece handle=/u01/app/oracle/rmanscn/database_ORCL_433mkiou_131 tag=TAG20250411T105826 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
skipping datafile 00008 because it has not changed
channel c1: backup cancelled because all files were skipped
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 11-APR-25
channel c1: finished piece 1 at 11-APR-25
piece handle=/u01/app/oracle/rmanscn/database_ORCL_453mkiqm_133 tag=TAG20250411T105826 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-APR-25
Starting backup at 11-APR-25
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 11-APR-25
channel c1: finished piece 1 at 11-APR-25
piece handle=/u01/app/oracle/rmanscn/stnbyControl_ORCL_463mkiqv_134 tag=TAG20250411T110047 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-APR-25
Starting Control File and SPFILE Autobackup at 11-APR-25
piece handle=/u01/app/oracle/fast_recovery_area/ORCLDC/autobackup/2025_04_11/o1_mf_s_1198148451_mzkb8d4d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-APR-25
released channel: c1
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@source rmanscn]$ ls -lrth
total 130M
-rw-r-----. 1 oracle oinstall 77M Apr 11 10:59 database_ORCL_423mkimi_130
-rw-r-----. 1 oracle oinstall 16M Apr 11 11:00 database_ORCL_433mkiou_131
-rw-r-----. 1 oracle oinstall 19M Apr 11 11:00 database_ORCL_453mkiqm_133
-rw-r-----. 1 oracle oinstall 19M Apr 11 11:00 stnbyControl_ORCL_463mkiqv_134
[oracle@source rmanscn]$
[oracle@source rmanscn]$ scp * oracle@target:/u01/app/oracle/rmandr
oracle@target's password:
database_ORCL_423mkimi_130 100% 77MB 29.3MB/s 00:02
database_ORCL_433mkiou_131 100% 16MB 58.2MB/s 00:00
database_ORCL_453mkiqm_133 100% 18MB 61.9MB/s 00:00
stnbyControl_ORCL_463mkiqv_134 100% 18MB 61.5MB/s 00:00
[oracle@source rmanscn]$
[oracle@target ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 11:04:24 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1718803653, not open)
RMAN>
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 920 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 450 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST *** /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN> exit
Recovery Manager complete.
[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 11 11:05:09 2025
Version 19.24.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.24.0.0.0
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target ~]$ cd /u01/app/oracle/oradata/ORCLDR/
[oracle@target ORCLDR]$ ls -lrth
total 4.1G
drwxr-x---. 2 oracle oinstall 111 Jan 23 10:45 pdbseed
drwxr-x---. 2 oracle oinstall 122 Jan 23 10:45 orclpdb
-rw-r-----. 1 oracle oinstall 33M Jan 23 10:46 temp01.dbf
-rw-r-----. 1 oracle oinstall 201M Jan 24 10:41 standby_redo04.log
-rw-r-----. 1 oracle oinstall 201M Jan 24 10:55 redo01.log
-rw-r-----. 1 oracle oinstall 201M Jan 24 10:55 redo02.log
-rw-r-----. 1 oracle oinstall 201M Jan 24 10:55 redo03.log
-rw-r-----. 1 oracle oinstall 201M Mar 30 15:36 standby_redo03.log
-rw-r-----. 1 oracle oinstall 1.1G Mar 30 16:09 system01.dbf
-rw-r-----. 1 oracle oinstall 921M Mar 30 16:09 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 711M Mar 30 16:09 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5.1M Mar 30 16:09 users01.dbf
-rw-r-----. 1 oracle oinstall 201M Mar 30 16:09 standby_redo01.log
-rw-r-----. 1 oracle oinstall 201M Apr 11 11:05 standby_redo02.log
-rw-r-----. 1 oracle oinstall 19M Apr 11 11:05 control01.ctl
[oracle@target ORCLDR]$ cp control01.ctl /home/oracle/control01bkp.ctl
[oracle@target ORCLDR]$
[oracle@target rmandr]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 11 11:30:30 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 1224736768 bytes
Database Buffers 218103808 bytes
Redo Buffers 7598080 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target rmandr]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 11:32:25 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN>
RMAN> restore controlfile from '/u01/app/oracle/rmandr/stnbyControl_ORCL_463mkiqv_134';
Starting restore at 11-APR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
Finished restore at 11-APR-25
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
RMAN> report schema;
Starting implicit crosscheck backup at 11-APR-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 11-APR-25
Starting implicit crosscheck copy at 11-APR-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-APR-25
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_227_mzk99fh4_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_228_mzk99fl7_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_226_mzk99fs4_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_229_mzk9yzyv_.arc
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 920 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 450 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST *** /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN>
RMAN> catalog start with '/u01/app/oracle/rmandr';
searching for all files that match the pattern /u01/app/oracle/rmandr
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/rmandr/database_ORCL_423mkimi_130
File Name: /u01/app/oracle/rmandr/database_ORCL_433mkiou_131
File Name: /u01/app/oracle/rmandr/database_ORCL_453mkiqm_133
File Name: /u01/app/oracle/rmandr/stnbyControl_ORCL_463mkiqv_134
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/rmandr/database_ORCL_423mkimi_130
File Name: /u01/app/oracle/rmandr/database_ORCL_433mkiou_131
File Name: /u01/app/oracle/rmandr/database_ORCL_453mkiqm_133
File Name: /u01/app/oracle/rmandr/stnbyControl_ORCL_463mkiqv_134
RMAN> run
{
allocate channel c1 type disk;
recover database;
release channel c1;
}
allocated channel: c1
channel c1: SID=237 device type=DISK
Starting recover at 11-APR-25
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCLDR/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel c1: reading from backup piece /u01/app/oracle/rmandr/database_ORCL_423mkimi_130
channel c1: piece handle=/u01/app/oracle/rmandr/database_ORCL_423mkimi_130 tag=TAG20250411T105826
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
destination for restore of datafile 00010: /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
destination for restore of datafile 00011: /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
destination for restore of datafile 00012: /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
destination for restore of datafile 00013: /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
channel c1: reading from backup piece /u01/app/oracle/rmandr/database_ORCL_433mkiou_131
channel c1: piece handle=/u01/app/oracle/rmandr/database_ORCL_433mkiou_131 tag=TAG20250411T105826
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
starting media recovery
archived log for thread 1 with sequence 230 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_230_mzkd4tr5_.arc
archived log for thread 1 with sequence 231 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_231_mzkd4trz_.arc
archived log for thread 1 with sequence 232 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_232_mzkdm9v3_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_230_mzkd4tr5_.arc thread=1 sequence=230
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_231_mzkd4trz_.arc thread=1 sequence=231
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_04_11/o1_mf_1_232_mzkdm9v3_.arc thread=1 sequence=232
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-APR-25
released channel: c1
RMAN>
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM YES /u01/app/oracle/oradata/ORCL/system01.dbf
3 0 SYSAUX NO /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 0 UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5 0 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6 0 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7 0 USERS NO /u01/app/oracle/oradata/ORCL/users01.dbf
8 0 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9 0 ORCLPDB:SYSTEM YES /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10 0 ORCLPDB:SYSAUX NO /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11 0 ORCLPDB:UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12 0 ORCLPDB:USERS NO /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
13 0 ORCLPDB:TEST NO /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 /u01/app/oracle/oradata/ORCL/temp01.dbf
2 0 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCL/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 0 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf
RMAN>
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4256971
SQL>
SQL> set lines 333 pages 333
SQL> col file for a60
SQL>
SQL> select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
---------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 4256972
3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 4256972
4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 4256972
5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 4256972
8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 4256972
10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 4256972
11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 4256972
12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 4256972
13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 4256972
12 rows selected.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 234 234 0 11-APR-2025 11:49:56 5.8 348
SQL>
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Comments
Post a Comment