- 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 |
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>
[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>
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
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.
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
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>
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!!
0 comments:
Post a Comment