MRP0: Background Media Recovery terminated with error 1111

MRP0: Background Media Recovery terminated with error 1111

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’
*** 2012-12-01 19:41:03.428
Completed Media Recovery
Managed Recovery: Not Active posted.
*** 2012-12-01 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 12/01/2012 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_2012_ernst.1574.800921533’
Successfully added datafile 1285 to media recovery
Datafile #1286: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2012_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]

ORA-16649: possible failover to another database prevents this database from being opened

ORA-16649: possible failover to another database prevents this database from being opened

We have two-node primary RAC database & two-node standby RAC database. For the business testing purpose, they will want both side of the database in the READ WRITE Mode. So we have decided to disabled the log shipping and made the standby database as a new primary database using failover database.

During the standby failover process, we made the existing primary database down. We have successfully failover the standby database as a new primary database. Once the process completed, we have disabled the DG configuration.

When we try to start the old primary database, we go the below error message. After analyzing further, we found the DG configuration file has been update from the standby database DG configuration file. Since the standby database become a new primary. So it has update the DG configuration file in the old primary database.

ORA-16649: possible failover to another database prevents this database from being opened

SOLUTION

After disabling the DG_BROKER_START=FALSE;  the old primary database able to start in the READ WRITE mode.

SQL> startup force

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL> alter database open;

Database altered.

Standby Database Commands

  Standby Database Commands

Open Standby In Read Only :

    SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
    SQL> ALTER DATABASE OPEN READ ONLY;

Back to Mount Point ( Same as Before Open in Read Only ) :

    SQL > shutdown;
    SQL > startup nomount
    SQL > alter database mount standby database
    SQL > alter database recover managed standby database disconnect from session;

Switch Over Primary As Standby , And Standby As Primary

Primary Database : Prim
Standby Database : Stdby

On Prim :

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

On standby:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN;
SQL> STARTUP;

On Prim :

    SQL> ALTER DATABASE RECOVDR MANAGED STANDBY DATABASE;



On standby:

SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE

Script to find redo generated by current sessions

#################################################

Redo generated by current sessions

#################################################

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

To check INACTIVE sessions with HIGH DISK IO

To check INACTIVE sessions with HIGH DISK IO

=============================================

select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) “Last SQL”
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;

=================================================================

Performance tuning links

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62dba-1741123.html

http://docs.oracle.com/cd/E11882_01/server.112/e10822/toc.htm

Command to list larger files in Unix

Command to list larger files in Unix 

du -sh * |sort -n -r |head -10

RMAN Incremental Backups to Roll Forward a Physical Standby Database

RMAN Incremental Backups to Roll Forward a Physical Standby Database

RMAN Incremental Backups to Roll Forward a Physical Standby Database

What Is an Archive Gap:-

An archive gap is a range of archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary database. For example, an archive gap occurs when the network becomes unavailable and automatic archiving from the primary database to the standby database stops. When the network is available again, automatic transmission of the redo data from the primary database to the failed standby database resumes.

Causes:-

The steps in this section can used to resolve problems if a physical standby database has

1) Loss of Archive log

2) Corrupted Archive Redo Data

3) Unresolvable Archive GAP’s

4) Out of Retention policy

WorkArounds:-

1) If archive exist on primary Troubleshoot the issue, It will automatically shipped if the archive is not corrupted.

2) If archive is corrupted at OS level, if we do have backup, Then Restore archivelogs from Backup.

3) Incremental SCN for standby/ Roll Forward.

Environment:-

DB Version:- 11.1.0.7.0

OS version: x86_64 GNU/Linux

In Archive GAP issues, familiarly we can see such general errors below in alert log file from standby database.

Analysis

Check the archives generated on Primary for each thread.

Check the archives applied on Standby for each thread.

Here we can see lot of difference in primary & standby around 1300 of archives for each thread, and caused because one of the archive is missing on primary database, i.e. Thread#1 and sequence#63590

We can query from v$archive_gap.

Of course, we have missed only one file, But standby is far behind than primary, Instead of copying those archives from primary & standby, Here Rollforward is very quick.

Here primary & standby redo difference is around

Thread #1 (50mb logfile * 1300) = 65000 mb

Thread # 2 (50mb logfile *1300) = 65000 mb

Its around 130gb of redo data need to apply on standby.

If the difference between primary and standby is more & more, recommended to take Incremental SCN/roll forward to reduce recovery window.

Stop the MRP process.

Capture the CURRENT_SCN from Standby & Primary.

Compare the SCN in primary & standby databases.

SCN Difference = 8405028062 – 8276807480 = 128220582

Now take the backup of increments & controlfile.

1) From SCN 8276807480 until database.

- RMAN> backup incremental from scn 8276807480 database;

2) Backup of standby controlfile.

- RMAN> backup current controlfile for standby;

Backup incremental.

Backup current control file for standby.

Copy the backup’s to standby server.

Tasks before restore.

1) Bounce instance in “NOMOUNT” status.

2) Remove old control files as per the value of “control_files” in PFILE/SPFILE.

3) We can use either RMAN/SQL to create controlfile, I have been used RMAN , and restored using RMAN.

4) Mount the standby database.

Bounce in “NOMOUNT”

Restore standby control file from BACKUP.

Mount the database and check the status, database role.

Register backups with new control file.

Perform media recovery from RMAN as below.

Check the alert log file as each of the datafile is being recovered.

After successful recovery, Start MRP once again

Now Archives are transporting to standby and also performing media recovery with new archives.

Check the Archive sync in primary & standby databases.

Primary:-

Standby:-

Gaps:-

Useful Views for DataGuard:-

V$managed_standby                                                        v$archive_dest

V$dataguard_status                                                         v$archive_dest_status

v$archive_gap                                                                    v$database

v$dataguard_stats                                                            v$database_incarnation

v$archived_log                                                                   v$datafile

v$dataguard_config                                                          v$fs_failover_stats

v$logfile                                                                                 v$log_history

v$redo_dest_resp_histogram                                         v$standby_log

Reference Links:-

Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary andstandby are in ASM filesystem [ID 836986.1]

10.2: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

11.1: http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/rman.htm#SBYDB00759

11.2: http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/rman.htm#CIHIAADC