After adding Datafile in Primary the MRP Stopped in Physical Standby (Dataguard)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s