Hi all!
After add a datafile in PRIMARY database, the STANDBY MRP stopped. An “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” does not solved te problem, as you see:
SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED 'YES' and SEQUENCE# ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED 'YES' and SEQUENCE# < (select max(SEQUENCE#) -1 from V$ARCHIVED_LOG); SEQUENCE# NAME APPLIED ---------- -------------------------------------------------------------------------------- --------- 15075 /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15075.arc NO
Ok, this happen when setting standby_file_management to MANUAL, lets check:
SQL> show parameters standby_file_management NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ standby_file_management string MANUAL
That’s right. Let’s see alert log what is happening:
Thu May 05 19:26:21 2016 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Attempt to start background Managed Standby Recovery process (MYDB_DG) Thu May 05 19:26:21 2016 MRP0 started with pid=25, OS id=5670 MRP0: Background Managed Standby Recovery process started (MYDB_DG) started logmerger process Thu May 05 19:26:26 2016 Managed Standby Recovery not using Real Time Apply MRP0: Background Media Recovery terminated with error 1111 Errors in file /db/u1001/oracle/diag/rdbms/MYDB_DG/MYDB_DG/trace/MYDB_DG_pr00_5672.trc: ORA-01111: name for data file 15 is unknown - rename to correct file ORA-01110: data file 15: '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' ORA-01157: cannot identify/lock data file 15 - see DBWR trace file ORA-01111: name for data file 15 is unknown - rename to correct file ORA-01110: data file 15: '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' Slave exiting with ORA-1111 exception Errors in file /db/u1001/oracle/diag/rdbms/MYDB_DG/MYDB_DG/trace/MYDB_DG_pr00_5672.trc: ORA-01111: name for data file 15 is unknown - rename to correct file ORA-01110: data file 15: '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' ORA-01157: cannot identify/lock data file 15 - see DBWR trace file ORA-01111: name for data file 15 is unknown - rename to correct file ORA-01110: data file 15: '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Recovery Slave PR00 previously exited with exception 1111 MRP0: Background Media Recovery process shutdown (MYDB_DG)
Precisely. Now, how to fix?
Let’s first add the datafile, with the same name added on primary.
Another thing is that standby_file_management setted as MANUAL only makes sense when using rawdevices on standby. This is not my case, so, let’s set it to AUTO too.
This way, it’s not going to happen again. 🙂
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' AS '/db/u1002/oradata/MYDB/EZM_DATA_08.dbf'; Database altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both; System altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED 'YES' and SEQUENCE# < (select max(SEQUENCE#) -1 from V$ARCHIVED_LOG); 2 no rows selected
Solved!
See alert log:
Thu May 05 19:36:29 2016 ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' AS '/db/u1002/oradata/MYDB/EZM_DATA_08.dbf' Completed: ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/11.2/dbs/UNNAMED00015' AS '/db/u1002/oradata/MYDB/EZM_DATA_08.dbf' Thu May 05 19:37:31 2016 ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH; Thu May 05 19:37:49 2016 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Attempt to start background Managed Standby Recovery process (MYDB_DG) Thu May 05 19:37:49 2016 MRP0 started with pid=25, OS id=8148 MRP0: Background Managed Standby Recovery process started (MYDB_DG) started logmerger process Thu May 05 19:37:54 2016 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 16 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15075.arc Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION Thu May 05 19:38:05 2016 Media Recovery Log /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15076.arc Thu May 05 19:38:35 2016 Media Recovery Log /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15077.arc
KB:
Managing Primary Database Events That Affect the Standby Database
Matheus.
