Recover Standby using incremental backup

• 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:

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

Now proceed for recovery activity step by step:

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>

At Standby:

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.

As per above output we can see the gap between Primary & Standy database. Also we have observed the difference in SCN.
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.

3. Now transfer the backup on Standby.

[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]$

4. Now take Report schema output & control file backup (post shutdown) at Standby.

[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]$

5. Now start the Standby database in nomount.

[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

.6 Now restore the controlfile from the incremental backup taken at Primary. Also mount the Standby database:

[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>

7. Now catalog the backup & Start the recovery:

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>

Exception: We might face RMAN-06094: datafile 1 must be restored error while doing the recovery. This may be because we have restored the controlfile from Primary and it is having the datafile location of Primary instead of Standby. In this scenario, we will see the output of Report Schema like below where datafile size will be shown 0. To resolve the same, follow the Oracle Document:Doc ID 1339439.1. We can also rename the datafile (alter database rename file 'PrimaryLocation' to 'StandbyLocation';) from Primary location to Standby location (as taken the Report Schema output in step-4).

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>

7. Now check the SCN at standby and start the MRP. Also do some log switches on Primary and check the sync.

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>

Thanks for visiting!!

Read More

Oracle DB Parameters

• Introduction: Oracle database parameters are settings that define how the database operates, influencing aspects like memory allocation, resource limits, security, and performance. These parameters are typically stored in a parameter file, which is a text file (PFILE) or a binary file (SPFILE).

• We can use parameters to:
• Set limits for the entire database.
• Set limits on database resources.
• Set user or process limits.
• Control the database's behaviour and performance.

• Parameter Files:
• PFILE (Parameter File- init[SID].ora): A text file containing initialization parameters.
• SPFILE (Server Parameter File- spfile[SID].ora): A binary file that stores initialization parameters.

• Scope: SCOPE parameter in ALTER SYSTEM statements determines where and when parameter changes take effect. Below are the available options to be used.
• MEMORY: It will change the parameter value only in the current instance's memory, and the change will be lost post restart of DB instance.
• SPFILE: It will change the parameter value in the Server Parameter File (SPFILE). DB restart is required to take change effects. 
• BOTH:  It will update the parameter value in both the current instance's memory and the SPFILE. The change will reflect instantly, but only applicable for parameters that can be modified dynamically. 

• Dynamic vs. Static Parameters: Some parameters are dynamic that means those can be changed while the database is running while others are static that means those required Database restart to reflect. 

• Modifying parameters: Dynamic parameters can be modified while the database is running using the ALTER SYSTEM or ALTER SESSION statement, while others require a database restart.
• ALTER SYSETM: Whenever a parameter is modified using this statement, the Oracle Database records the statement that modifies the parameter in the alert log.
• ALTER SESSION: This statement changes the value of the specified parameter for the duration of the session that invokes the statement. The value of the parameter does not change for other sessions in the instance.
  
• How to identify dynamic parameter:  For this we need to query the v$parameter view and refer the value of ISSYS_MODIFIABLE column. If the value is IMMEDIATE, then the parameter can be modified dynamically otherwise not.

    Example:

SQL>set lines 333 pages 999
SQL>col name for a30
SQL>col value for a10
SQL>col ISSYS_MODIFIABLE for a30
SQL>select NAME, VALUE, ISSYS_MODIFIABLE
from V$PARAMETER where           NAME='archive_lag_target';

NAME                           VALUE     ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
archive_lag_target             1200      IMMEDIATE

SQL> select NAME, VALUE, ISSYS_MODIFIABLE from V$PARAMETER where NAME='processes';

NAME                           VALUE     ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
processes                      300       FALSE

 As per above output archive_lag_target is dynamic & processes is static.

List of Dynamic/Static Parameter

• Sample commands for changing Parameter:

At session level:
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';

At system level:

Dynamic changes:
alter system set archive_lag_target =900;
alter system set archive_lag_target =900 scope= memory sid=’*’;
alter system set archive_lag_target =900 scope= both sid=’*’;

SID is used in RAC which helps to set parameter on al nodes.

                        Static changes:
                        alter system set processes=1000 scope=spfile;
                        alter system set processes=1000 scope=spfile sid=’*’;

 

 

Thanks for visiting!!

Read More

Standby File Management is set to MANUAL

• Introduction: STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled or set to TRUE, operating system file additions and deletions on the primary database are replicated on the standby database. This parameter has following values:
  
  • MANUAL : disables automatic standby file management.
  • AUTO : enables automatic standby file management.


• Scenario: On the Standby Database STANDBY_FILE_MANAGEMENT was set to FALSE by mistake or for any xyz reason. Post that on Primary some datafiles were added & Primary-Standby is out of sync.


• Prerequisites:
• Oracle Data Guard should be configured.
• Set STANDBY_FILE_MANAGEMENT to FALSE on standby & add datafiles on Primary. (For testing purpose only).
• Environment:

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

Now proceed for troubleshooting step by step:

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> set lines 200 pages 300
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
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#;

SQL>
Session altered.

SQL>
DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        241         236 11-APR-2025 12:19:56          5

SQL>

[oracle@source ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 13:52:05 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> 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
14   500      ORCLPDB:TEST         NO      /u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf
15   500      ORCLPDB:USERS        NO      /u01/app/oracle/oradata/ORCL/orclpdb/users02.dbf
16   500      ORCLPDB:TEST         NO      /u01/app/oracle/oradata/ORCL/orclpdb/test03.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>

At Standby:

[oracle@target ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 11 13:48:53 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> 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> set lines 200 pages 300
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
SQL> (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and 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#;
Session altered.

SQL>

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        241         236          5 11-APR-2025 12:19:56       89.25           5355

SQL>

[oracle@target ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 11 13:52:16 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
14   0        ORCLPDB:TEST         ***     /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014

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>

As per above output we can see that on Primary Datafiles 14,15 & 16 were added but same is not getting reflected at Standby.
2. Now check MRP & DB alert log on Standby:

SQL> select process, status, thread#, sequence#, block# from v$managed_standby where process like '%MRP%';
no rows selected
SQL>

== DR Alert log ==
2025-04-11T12:23:02.622396+05:30  
ORCLPDB(3):File #14 added to control file as 'UNNAMED00014' because  
ORCLPDB(3):the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL  
ORCLPDB(3):The file should be manually created to continue.  
PR00 (PID:11496): MRP0: Background Media Recovery terminated with error 1274  
2025-04-11T12:23:02.696283+05:30  
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr/trace/orcldr_pr00_11496.trc:  
ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf'  
PR00 (PID:11496): Managed Standby Recovery not using Real Time Apply  
Recovery interrupted!  
Recovery stopped due to failure in applying recovery marker (opcode 17.30).  
Datafiles are recovered to a consistent state at change 4262537 but controlfile could be ahead of datafiles.  
Stopping change tracking  
2025-04-11T12:23:03.327811+05:30  
Errors in file /u01/app/oracle/diag/rdbms/orcldr/orcldr/trace/orcldr_pr00_11496.trc:  
ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf'  
2025-04-11T12:23:03.478947+05:30  
Background Media Recovery process shutdown (orcldr)  
2025-04-11T12:34:56.947874+05:30

3. Now to resolve the same we need to create 1st datafile manually on Standby with below command:
alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as 'file_name_with_location'; --> For Normal File System.
alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as '+DATA'; ---> For ASM or OMF.
Note: If datafile belongs to PDB, then run the above in the that PDB only.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ORCLPDB                        MOUNTED

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> alter database create datafile '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00014' as '/u01/app/oracle/oradata/ORCLDR/orclpdb/test02.dbf';
Database altered.

4. Now set Standby File Management to TRUE and Start MRP on Standby:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ORCLPDB                        MOUNTED

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

SQL> alter system set standby_file_management=AUTO;
System altered.

SQL> show parameter standby_file_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1        237        452

SQL> /
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1        243        922

5. Now do some log switches on Primary and check the sync:
At Primary:

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  LAST_APP_TIMESTAMP      ARC_DIFF
------------------------------ ----------------       -------  --------  -----------  --------------------    ----------
ORCLDC                         PRIMARY                    1         243         243    11-APR-2025 14:04:57           0

SQL>

At Stanby:

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,
            b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
            round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins,
            round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
     FROM
     (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
      from gv$archived_log
      where REGISTRAR='RFS' and 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        243         243         0     11-APR-2025 14:04:57       13.63            818

SQL>

Thanks for visiting!!

Read More

Oracle Statspack

• Introduction: STATSPACK is a performance diagnosis tool, available since Oracle8i. Along with additional reporting, statspack can store snapshots of system statistics over time, allowing greater accuracy and flexibility. This guide will walk you through the STATSPACK utility step-by-step.
  
  
• Collecting Snapshots: Oracle Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Snapshots can be collected a various levels, each increasing level collecting a better information about the database.So to collect the better information we can increase the snaspshot level. Below table will describe the levels



Level	Information Collected
0	General Performance Statistics
5	Addition Data: SQL Statements
6	Addition Data: SQL Plans and SQL Plan Usage
7	Addition Data: Segment Level Statistics
10	Addition Data: Parent and Child Latches



• Prerequisites:
o control_management_pack_access should be set NONE


• Environment:
  
Hostname: apex
IP: 192.168.101.7
ORACLE_SID : APEXDB
DB NAME: APEXDB
PDB NAME: APEXPDB


• Now let's start the STATSPACK configuration step by step:
  

1. First check the parameter. Also verify if statspack is already configured or not:
Parameter check

[oracle@apex ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 15:55:13 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL>

SQL> show pdbs
    CON_ID CON_NAME                         OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                        READ ONLY  NO
         3 APEXPDB                         READ WRITE NO

SQL> show parameter control_management_pack_access

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      NONE

Verify STATSPACK configuration exists or not by using below queries.

SQL> select username from dba_users where username like '%STAT%';
no rows selected
SQL>

SQL> select * from stats$level_description;
select * from stats$level_description
              *
ERROR at line 1:
ORA-00942: table or view does not exist

2. Now create a tablespace with name PERFSTAT which will be used to store STATSPACK data :

SQL> col file_name for a150
SQL>

SQL> set lines 200 pages 1000
SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf

SQL> CREATE TABLESPACE PERFSTAT DATAFILE '/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf' SIZE 500M autoextend on;

Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf

7 rows selected.

SQL> exit
Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

3. Now run spcreate.sql script from $ORACLE_HOME/rdbms/admin to configure STATSPACK:

[oracle@apex ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@apex admin]$
[oracle@apex admin]$ ll | grep -i spcreate.sql
-rwxr-xr-x. 1 oracle oinstall    1796 May 29  2017 spcreate.sql
[oracle@apex admin]$
[oracle@apex admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 16:00:26 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APEXPDB                        READ WRITE NO

SQL> alter session set container=APEXPDB;

Session altered.

SQL> !pwd
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin

SQL> @spcreate.sql

Session altered.

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: 
 PERF##stat#123  --> Provide the password for perfstat user & note it.
PERF##stat#123

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT user's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS
------------------------------ ---------------------
STATSPACK DEFAULT TABLESPACE
----------------------------
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT
*
TABLESPACE_NAME                CONTENTS
------------------------------ ---------------------
STATSPACK DEFAULT TABLESPACE
----------------------------
USERS                          PERMANENT

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: 
 PERFSTAT  --> Provide the default tablespace.

Using tablespace PERFSTAT as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP                           TEMPORARY             *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:
TEMP  --> Provide the Temporary tablespace.

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL>

SQL> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;

Session altered.

... output continue ...
... output continue ...

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>

SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;

Session altered.

4. Now verify the STATSPACK configuration:

SQL> set pages 999 lines 180
SQL> col DESCRIPTION for a60
SQL> select * from stats$level_description;

SNAP_LEVEL DESCRIPTION
---------- ------------------------------------------------------------
         0 This level captures general statistics, including rollback segment,
           row cache, SGA, system events, background events, session events,
           system statistics, wait statistics, lock statistics, and Latch information

         5 This level includes capturing high resource usage SQL Statements,
           along with all data captured by lower levels

         6 This level includes capturing SQL plan and SQL plan usage information
           for high resource usage SQL Statements, along with all data captured
           by lower levels

         7 This level captures segment level statistics, including logical and
           physical reads, row lock, itl and buffer busy waits, along with all
           data captured by lower levels

        10 This level includes capturing Child Latch statistics, along with all
           data captured by lower levels

5 rows selected.

SQL> 
SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE

5. Now take a manual snap and check the created snap:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 APEXPDB                        READ WRITE NO

SQL>
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.

SQL>
SQL> select name, snap_id, to_char(snap_time, 'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
     from stats$snapshot, v$database;

NAME         SNAP_ID Date/Time
--------- ---------- -----------------------------
APEXDB            1 04-APR-2025:16:14:01
1 row selected.

6. Now Schedule automatic STATSPACK:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APEXPDB                        READ WRITE NO

SQL> alter session set container=APEXPDB;
Session altered.

SQL> show user
USER is "SYS"

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 APEXPDB                        READ WRITE NO

SQL> GRANT CREATE JOB TO PERFSTAT ;
Grant succeeded.

SQL> conn PERFSTAT/PERF##stat#123@APEXPDB
Connected.

SQL> @?/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
        63      
---> Note the Job No

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     80

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
        63 05-APR-25 22:00:00

SQL> 
SQL> SELECT job, what, next_date, broken FROM dba_jobs WHERE what LIKE '%statspack.snap%';

       JOB WHAT                           NEXT_DATE            B
---------- ------------------------------ -------------------- -
        61 statspack.snap;                04-APR-2025 17:00:00 N
1 row selected.

7. Now wait for sometime and check generated snaps:

select name, snap_id, to_char(snap_time, 'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
from stats$snapshot, v$database 
order by 2;

NAME    SNAP_ID  Date/Time
--------- ---------- -----------------------------
APEXDB    1       04-APR-2025:16:14:01
APEXDB    2       04-APR-2025:17:00:00
APEXDB    3       04-APR-2025:18:00:00

8. Now generate the STATSPACK report with available snaps by using spreport:

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 APEXPDB                       READ WRITE NO

SQL> @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1196724728 APEXDB      1       APEXDB

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1196724728        1 APEXDB      APEXDB       APEXDB

Using 1196724728 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent (n) days of snapshots being listed.
Pressing  without specifying a number lists all completed snapshots.

Listing all Completed Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
APEXDB      APEXDB              1 04 Apr 2025 16:14     5
                                2 05 Apr 2025 17:00     5
                                3 05 Apr 2025 18:00     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 2
Begin Snapshot Id specified: 2

Enter value for end_snap: 3
End Snapshot Id specified: 3

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_2_3. To use this name, press  to continue, otherwise enter an alternative.

Enter value for report_name: sp_2_3

Using the report name sp_2_3

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3003259501 apexdb              1 02-Apr-25 20:14 19.0.0.0.0  NO

Now your report will be saved on the server location from where you run the spreport.


Thanks for visiting!!

Read More

Recover Standby using service

• Introduction: From 12c onwards, Oracle provides  a new commands RECOVER FROM SERVICE for make the database sync if standby database is out of sync by reducing the steps.
  
  
• Prerequisites:
• Oracle Data Guard should be configured.
• Disable DG broker if any as it will start MRP automatically.
• Oracle version should be 12c or later.
  
• Environment:

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

Now proceed for recovery activity step by step:

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> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; 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#; Session altered. SQL> DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF ------------------------------ ---------------- ---------- ---------- ----------- -------------------- ---------- ORCLDC PRIMARY 1 210 104 08-FEB-2025 20:39:23 106 SQL> SQL> select current_scn from v$database; CURRENT_SCN ----------- 4123407 SQL> SQL> set lines 333 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/ORCL/system01.dbf 4122798 3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf 4122798 4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 4122798 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 4122798 8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf 2345308 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf 4122847 10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf 4123033 11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf 4123159 12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf 4123189 13 /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf 4123195 12 rows selected. SQL>

At Standby:

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 READ ONLY WITH APPLY SQL> SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp, round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and 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#; Session altered. SQL> 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 210 104 106 08-FEB-2025 20:39:23 71667.78 4300067 SQL> select current_scn from v$database; CURRENT_SCN ----------- 2950967 SQL> set lines 333 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 2950968 3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 2950968 4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 2950968 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 2950968 8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308 9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 2950968 10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 2950968 11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 2950968 12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 2950968 13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 2950968 12 rows selected. SQL>

As per above output we can see the gap between Primary & Standy database. Also we have observed the difference in SCN.
2. Now stop the MRP process at Standby. Also shutdown the DB & start in nomount.

SQL> alter database recover managed standby database cancel; Database altered. SQL> SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. 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>

3. Now on Standby login in RMAN & restore the controlfile using service.

[oracle@target ~]$ rman target sys Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 30 15:39:31 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. target database Password: connected to target database: ORCL (not mounted) RMAN> restore standby controlfile from service orcldc; Starting restore at 30-MAR-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: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service orcldc 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 30-MAR-25 RMAN> RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> report schema; Starting implicit crosscheck backup at 30-MAR-25 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=39 device type=DISK Crosschecked 15 objects Finished implicit crosscheck backup at 30-MAR-25 Starting implicit crosscheck copy at 30-MAR-25 using channel ORA_DISK_1 Finished implicit crosscheck copy at 30-MAR-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_03_30/o1_mf_1_208_myl27nc7_.arc File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_209_myl33owm_.arc File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_210_myl3zxlm_.arc File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_211_myl4vyvz_.arc File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_23/o1_mf_s_1191149732_ms3npt02_.bkp File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191234421_ms67n42x_.bkp File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191235588_ms68kdwf_.bkp File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191236506_ms69n194_.bkp 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 890 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>

4. Now recover the standby database using scn.
At new Primary:

RMAN> recover database from service orcldc noredo using compressed backupset; Starting recover at 30-MAR-25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK skipping datafile 5; already restored to SCN 2345308 skipping datafile 6; already restored to SCN 2345308 skipping datafile 8; already restored to SCN 2345308 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCLDR/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:02:15 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCLDR/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00009: /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:02:25 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00010: /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:01:27 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00011: /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00012: /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service orcldc destination for restore of datafile 00013: /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 30-MAR-25 RMAN> RMAN> exit Recovery Manager complete.

5. Now check the SCN at standby and start the MRP. Also do some log switches on Primary and check the sync.

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 ----------- 4121015 SQL> SQL> set lines 333 col file for a60 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 4122274 3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 4122323 4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 4122531 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 4122616 8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308 9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 4122847 10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 4123033 11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 4123159 12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 4123189 13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 4123195 12 rows selected. SQL> SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> set lines 200 pages 300 alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp, round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and 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#; SQL> Session altered. 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 217 217 0 30-MAR-2025 15:59:50 5.1 306 SQL>

Thanks for visiting!!

Read More

How to change Apex Admin Password

• Introduction: Oracle APEX is a low-code development platform that helps you build scalable, secure enterprise apps. To access the APEX, we need ADMIN credential. This guide will walk you through to change Apex Admin Password.

• Prerequisites: Oracle Apex should be installed.

• Environment Used:
  Hostname: apex
  IP: 192.168.101.10
  ORACLE_SID : APEXDB
  PDB: APEXPDB
  Apex: 24.1
  ORDS: 24.2
  
  
• Now let's change the ADMIN password.
  
  

1. You will get below error if ADMIN password is incorrect.

2. Stop running ORDS services by killing its PID.

3. Now change the password using below steps:

[oracle@apex ~]$ cd /u02/app/oracle/apex24_1/apex/
[oracle@apex apex]$ ls
apexins1.sql 			apex_rest_config_nocdb.sql  apxdvins_nocdb.sql 
apxremov_nocdb.sql  		builder  devins.sql	apxrtins_nocdb.sql
apexins2.sql            	apex_rest_config.sql apxdvins.sql      
apexins3.sql            	appins.sql          apxdwngrd.sql      
apexins_adb.sql         	apxappcon.sql       apxpatch_cdb.sql   
apexins_cdb.sql         	apxchpwd.sql        apxpatch_nocdb.sql 
apexins_nocdb.sql       	apxdevrm_cdb.sql    apxpatch.sql       
apexins.sql             	apxdevrm_nocdb.sql  apxremov1.sql      
apex_rest_config_cdb.sql	apxdevrm.sql       apxremov2.sql      
apex_rest_config_core.sql 	apxdvins_cdb.sql  apxremov_cdb.sql   
apxremov.sql       		core          	images
apxrtins1.sql      		coreins2.sql  	install2024-09-25_04-11-58.log
apxrtins2.sql      		coreins3.sql  	LICENSE.txt
apxrtins3.sql      		coreins4.sql  	load_trans.sql
apxrtins_cdb.sql  		coreins5.sql  	utilities
coreins.sql			apxrtins.sql       dbcsconf.sql
apxsilentins.sql   dbcsins.sql	
[oracle@apex apex]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 24 18:12:21 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> show pdbs
     CON_ID CON_NAME                             OPEN MODE  RESTRICTED
---------- ------------------------------------- ---------- ----------
         2 PDB$SEED                             READ ONLY  NO
         3 APEXPDB                              READ WRITE NO

SQL> alter session set container=APEXPDB;

Session altered.

SQL> show pdbs
     CON_ID CON_NAME                             OPEN MODE  RESTRICTED
---------- ------------------------------------- ---------- ----------
         3 APEXPDB                              READ WRITE NO

SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Oracle APEX
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" exists.
Enter ADMIN's email [xxxxxxxxxxx@gmail.com]
Enter ADMIN's password []    
 ----> Provide the password & keep a note of it.
Changed password of instance administrator ADMIN.

SQL>

4.  Now Access the Apex Admin via browser with new password:
http://192.168.101.10:8080/ords/apex_admin

Apex Admin Login

Admin Home

Thanks for visiting!!

Read More