I have dataguard configuration such as 2 node rac(11.2.0.2) on aix 6.1 as primary, standalone standby database.

I noticed that Primary can transport archive log to standby but standby can not apply archive log and giving errors at standby alert.log such as:
MRP0: Background Media Recovery terminated with error 1111 and MRP0: Background Media Recovery process shutdown (PROD00DG)

On primary alert.log
————————
RC8: Archive log rejected (thread 1 sequence 75698) at host ‘PROD00DG_private_odm_izm’
FAL[server, ARC8]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance PROD001 – Archival Error. Archiver continuing.

I noticed that MPR has been stopped and standby.

On standby:
—————
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

at alert:
————–
Slave exiting with ORA-1111 exception
Errors in file /oracle11g/app/oracle/diag/rdbms/PROD00dg/PROD00DG/trace/PROD00DG_pr00_17891406.trc:
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (PROD00DG)

at standby trace:
———————-
MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’

*** 2016-07-21 19:41:03.428
Completed Media Recovery
Managed Recovery: Not Active posted.

*** 2016-07-21 19:41:04.133
Slave exiting with ORA-1111 exception
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’

I have checked below query ouputs:

a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;
Outputs are:
——————

SQL> select file#, error from v$recover_file;

FILE# ERROR
———- —————————–
1268
1269
1270
1275
1276
1277
1278
1279
1280
1281
1282

FILE# ERROR
———- ——————————
1283
1284
1285 FILE MISSING

SQL> select file#, name, status from v$datafile;

file# name status
—— ———– ———
1285 /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285 RECOVER

After make some search, I have found MOS Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]

A backup of the one datafile from the primary can be made and then used to restore on the standby database, as indicated in the following note:

The document walks you through the process starting about one-half way down, in the section titled:
“Recovering the Standby’s Datafile”

I followed below steps:

1. Backup related file at primary

On primary:
——————–
$ rman target /

RMAN> backup datafile 1285 format ‘/tmp/1285_pr.bk’ tag ‘PRIMARY_1285′;

2. Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc

3. At the standby site, catalog the backuppiece and confirm it’s available for use:

On standby:
——————–
$ rman target /

RMAN> catalog backuppiece’/tmp/1285_pr.bk’;
RMAN> list backuppiece’/tmp/1285_pr.bk’;
RMAN> list backup of datafile 1285;

4. Stop redo apply on the physical standby database

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For my issue my redo apply has been already stopped.

5. On the standby site restore the datafile:

On standby:
——————–
$ rman target /
RMAN> restore datafile 1285;

At steps 5 I got error:

RMAN> restore datafile 1285;

Starting restore at 01-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=438 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 2016-07-21 21:46:50
RMAN-06085: must use SET NEWNAME command to restore datafile /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285

So I need to run below command for can restore datafile 1285:

RUN {
SET NEWNAME FOR DATAFILE 1285 to ‘+ORADATA’;
RESTORE DATAFILE 1285;
SWITCH DATAFILE 1285;
}

6. Check status of files:Restart redo apply on the physical standby database

On standby:
——————–
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
from log:

Successfully added datafile 1285 to media recovery
Datafile #1285: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2016_ernst.1574.800921533’

Successfully added datafile 1285 to media recovery
Datafile #1286: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2016_ernst.1574.800921533′
SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

THREAD#    MAX(SEQUENCE#)
———-         ————–
1                        75677
2                         72871

Reference:
—————
Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]
How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]
MRP0: Background Media Recovery terminated with error 1274 [ID 739618.1]

Advertisements