Managed Recovery Process (MRP) terminates on Standby database upon adding datafile on the Primary database

Upon adding a datafile to a tablespace or upon creating a tablespace which again requires you to add datafile on the Primary database, the MRP on the Physical standby database might terminate as soon as the redo information from the primary ships to the standby database. This happens mainly because of the initialization parameter STANDBY_FILE_MANAGEMENT being set to MANUAL in the Standby Database pfile/spfile.

STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.

Note: Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERTso that existing standby files will not be accidentally overwritten.

Here is a scenario where you try to create a tablespace on the Primary database and the initialization parameter STANDBY_FILE_MANAGEMENT is set to MANUAL on the Physiacl Standby Database.

Primary database : sspm

Physical Standby database: sssb

On the Primary database:

I create a tablespace called DUMMY and cross verify if its successfully created or not.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> create tablespace dummy datafile size 10m; Tablespace created. SQL> select d.name "DATAFILE",ts.name "TABLESPACE" from v$datafile d,v$tablespace ts where d.ts#=ts.ts#; DATAFILE                                          TABLESPACE ------------------------------------------------  -------------------- +DATA_NEW/sspm/datafile/system.256.778803539      SYSTEM +DATA_NEW/sspm/datafile/sysaux.257.778803541      SYSAUX +DATA_NEW/sspm/datafile/undotbs1.258.778803541    UNDOTBS1 +DATA_NEW/sspm/datafile/users.259.778803543       USERS +DATA_NEW/sspm/datafile/sample.266.779367821      SAMPLE +DATA_NEW/sspm/datafile/dummy.267.779368093       DUMMY 6 rows selected.

Later, I perform the log switch to generate an archive which would be shipped to the Standby database.

1 2 3 4 5 6 7 8 9

SQL> alter system switch logfile; System altered. SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD#  MAX(SEQUENCE#) -------  ---------------- 1        1013

On the Standby Database:

I check the maximum sequence# that is applied on the Standby Database.

1 2 3 4 5

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1       1012

So, it is clear the log sequence# 1013 generated at Primary is not applied to the Standby Database. So, when I check the reason for this log not getting applied in the Standby database alert log file, was able to discover the below message.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[oracle@uat trace]$ pwd /u01/app/oracle/diag/rdbms/sssb/sssb/trace [oracle@uat trace]$ tail -30 alert_sssb.log Sat Mar 31 11:07:39 2012 Media Recovery Log +ARCH/sssb/archivelog/2012_03_31/thread_1_seq_1013.1072.779367799 File #6 added to control file as 'UNNAMED00006' because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file should be manually created to continue. Errors with log +ARCH/sssb/archivelog/2012_03_31/thread_1_seq_1013.1072.779367799 MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/sssb/sssb/trace/sssb_mrp0_23320.trc: ORA-01274: cannot add datafile '+DATA_NEW/sspm/datafile/dummy.267.779368093' - file could not be created Recovery interrupted! Recovery stopped due to failure in applying recovery marker (opcode 17.30). Datafiles are recovered to a consistent state at change 1001582 but controlfile could be ahead of datafiles. RFS[3]: Opened log for thread 1 sequence 1014 dbid 1624493265 branch 778803733 Errors in file /u01/app/oracle/diag/rdbms/sssb/sssb/trace/sssb_mrp0_23320.trc: ORA-01274: cannot add datafile '+DATA_NEW/sspm/datafile/dummy.267.779368093' - file could not be created MRP0: Background Media Recovery process shutdown (sssb)

I cross verify to check if the STANDBY_FILE_MANAGEMENT is set to MANUAL on the standby database.

1 2 3 4 5

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

So, here are the steps I followed to have the datafile get created on the Standby Database.

Step 1: Get the file# and name from the Primary database and check what is the file name that is created on the Standby Database. It would be of the name as UNNAMED at the location “$ORACLE_HOME/dbs” location on LINUX and on windows it would be created at the location “%ORACLE_HOME%\database”

Primary Database:

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select file#,name from v$datafile; FILE# NAME ----- ------------------------------------------------- 1     +DATA_NEW/sspm/datafile/system.256.778803539 2     +DATA_NEW/sspm/datafile/sysaux.257.778803541 3     +DATA_NEW/sspm/datafile/undotbs1.258.778803541 4     +DATA_NEW/sspm/datafile/users.259.778803543 5     +DATA_NEW/sspm/datafile/sample.266.779367821 6     +DATA_NEW/sspm/datafile/dummy.267.779368093 6 rows selected.

Standby Database:

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select file#,name from v$datafile; FILE# NAME ----- ----------------------------------------------------------- 1     +DATA/sssb/datafile/system.274.778865099 2     +DATA/sssb/datafile/sysaux.275.778865193 3     +DATA/sssb/datafile/undotbs1.276.778865259 4     +DATA/sssb/datafile/users.277.778865273 5     +DATA/sssb/datafile/sample.284.779367805 6     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006 6 rows selected.

Here you can notice that file# 6 on the Standby database is created at the location $ORACLE_HOME/dbs with the name as UNNAMED rather than getting created at the specified location (Diskgroup “+DATA”).

Step 2:

On the Standby database, I recreate the unnamed datafile using the “alter database create datafile” option as shown below.

Standby Database:

1 2 3

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as NEW; Database altered.

The syntax of the above statement is as follows “alter database create datafile ‘<UNNAMED_file_name_with_location>’ as <Required_file_name_with_location>’;“

If its an OMF or on ASM, then the syntax would be as “alter database create datafile ‘<UNNAMED_file_name_with_location>’ as NEW;“

After executing the above command, Oracle creates the datafile giving its own name as here OMF is being used.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> select file#,name from v$datafile; FILE# NAME ----- --------------------------------------------- 1     +DATA/sssb/datafile/system.274.778865099 2     +DATA/sssb/datafile/sysaux.275.778865193 3     +DATA/sssb/datafile/undotbs1.276.778865259 4     +DATA/sssb/datafile/users.277.778865273 5     +DATA/sssb/datafile/sample.284.779367805 6     +DATA/sssb/datafile/dummy.285.779368485 6 rows selected. SQL> select d.name "DATAFILE",ts.name "TABLESPACE" from v$datafile d,v$tablespace ts where d.ts#=ts.ts#; DATAFILE                                      TABLESPACE --------------------------------------------- ------------- +DATA/sssb/datafile/system.274.778865099      SYSTEM +DATA/sssb/datafile/sysaux.275.778865193      SYSAUX +DATA/sssb/datafile/undotbs1.276.778865259    UNDOTBS1 +DATA/sssb/datafile/users.277.778865273       USERS +DATA/sssb/datafile/sample.284.779367805      SAMPLE +DATA/sssb/datafile/dummy.285.779368485       DUMMY 6 rows selected.

Step 3: Set the parameter STANDBY_FILE_MANAGEMENT to AUTO in the standby database and start the MRP.

Standby Database:

1 2 3 4 5 6 7

SQL> alter system set standby_file_management=auto; System altered. SQL> alter database recover managed standby database disconnect from session; Database altered.

Now, the archives from the Primary would be applied on the standby database.