Analyze and solve the problem that MRP cannot be started in DataGuard

Source: Internet
Author: User

Analyze and solve the problem that MRP cannot be started in DataGuard

I have a dedicated uard environment on hand, because it has been useless for some days. As a result, I was suddenly prepared to start learning, and suddenly found that after I typed the command recover managed standby database disconnect from session, the command runs normally, but the ora error is reported in the background.

Sat Jun 27 23:16:39 2015
Recovery Slave PR00 previusly exited with exception 1157
Errors in file/u02/dg11g/diag/rdbms/dg11g/DG11G/trace/DG11G_mrp0_6514.trc:
ORA-01157: cannot identify/lock data file 7-see DBWR trace file
ORA-01110: data file 7: '/u02/dg11g/oradata/DG11G/test_new01.dbf'
MRP0: Background Media Recovery process shutdown (DG11G)
Sat Jun 27 23:16:39 2015
Completed: alter database recover managed standby database disconnect from session
RFS [162]: Opened log for thread 1 sequence 171 dbid 1028247664 branch 880742847
RFS [161]: Opened log for thread 1 sequence 173 dbid 1028247664 branch 880742847
RFS [160]: Opened log for thread 1 sequence 172 dbid 1028247664 branch 880742847
Through the above log we can see that the MRP process is in the data recovery when the ora error ora-01157 is reported
However, there is no problem with RFS. RFS transfers archive files from the master database. You can see that archive logs are transmitted from the master database normally, all the archived logs with sequence #171,173,172 are transmitted to the slave database.

Originally, this problem did not attract much attention. I thought about which archive files were not used, but I found that MRP was useless at all. Therefore, even though the archive transfer is complete, the data changes still cannot be applied to the slave database.
Check that v $ archive_gap does not have any records, indicating that there is a problem when no archived logs are applied.

Let's take a look at some details of this ora problem, prompting that the ora-01157 error was reported at the place of the data file 7.
Errors in file/u02/dg11g/diag/rdbms/dg11g/DG11G/trace/DG11G_mrp0_6514.trc:
ORA-01157: cannot identify/lock data file 7-see DBWR trace file
ORA-01110: data file 7: '/u02/dg11g/oradata/DG11G/test_new01.dbf'
From the official description of this problem, it seems that there is a problem with the data file.
$ Oerr ora 1, 01157
01157,000 00, "cannot identify/lock data file % s-see DBWR trace file"
// * Cause: The background process was either unable to find one of the data
// Files or failed to lock it because the file was already in use.
// The database will prohibit access to this file but other files will
// Be unaffected. However the first instance to open the database will
// Need to access all online data files. Accompanying error from
// Operating system describes why the file cocould not be identified.
// * Action: Have operating system make file available to database. Then either
// Open the database or do alter system check datafiles.
I don't know how many times this environment has been tossed. I have repeated switching and tests. I don't even remember what special operations have caused this problem. Therefore, this problem must be analyzed from the beginning.
First, check the/u02/dg11g/oradata/DG11G/test_new01.dbf file and find that it does not exist in the file system.
However, the data dictionary information exists. The corresponding records can be returned for the SQL statement used.
Select name, file # from v $ datafile where file # = 7;

In this case, the data file may have been accidentally deleted from the standby database. How can we evaluate the deleted data files? First, we need to check the files in the master database, but the data files and tablespaces in the master database do not exist.
In this way, this problem becomes a little tricky.
If the MRP problem can be fixed, it seems that the problem is solved. If the problem cannot be fixed, the replicuard may not be available. You may have to consider rebuilding a physical standby database.
In this regard, we adopt a conservative attitude, with a slight attempt to see if the slave database can be started to open read only.
However, I am confused about the results of these three operations.
If the file cannot be opened, it may need to be restored. The recovered file is system01.dbf, and the attempt to recover until cancel fails.
Idle> alter database open read only;
Alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u02/dg11g/oradata/DG11G/system01.dbf'

Idle> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done

Idle> alter database open read only;
Alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u02/dg11g/oradata/DG11G/system01.dbf'

 

For this problem, if there is a SQL statement can solve the problem, just fine, I found that after repeated attempts or some, the problem is to solve the problem first ORA-01157 problem, then, the MRP problem in dataguard can be solved.
The data file in the problem of ora-01157 does not exist in the master database, but in the backup database data dictionary, we can directly solve the problem in the backup database.
Idle> alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop;
Database altered.
Then there is a turning point in the mongouard log, and the file will be verified in the background, but a warning is thrown. Warning: Datafile 7 (/u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered
Then MRP starts normally. The background began to use archive files for data recovery.

Alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop
Completed: alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop
Sat Jun 27 23:24:08 2015
Alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (DG11G)
Sat Jun 27 23:24:08 2015
MRP0 started with pid = 25, OS id = 8431
MRP0: Background Managed Standby Recovery process started (DG11G)
Started logmerger process
Sat Jun 27 23:24:13 2015
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Warning: Datafile 7 (/u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/41012316880742847.dbf
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410122_880742847.dbf
Sat Jun 27 23:24:31 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410123_880742847.dbf
Recovery ing file #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.
Deleted file/u02/dg11g/oradata/DG11G/test_new01.dbf
Recovery dropped tablespace 'test _ new'
Recovery created file/u02/dg11g/oradata/DG11G/test_new01.dbf
Successfully added datafile 7 to media recovery
Datafile #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf'
Recovery ing file #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.
Deleted file/u02/dg11g/oradata/DG11G/test_new01.dbf
Recovery dropped tablespace 'test _ new'
Recovery ing file #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.
Deleted file/u02/dg11g/oradata/DG11G/test_new01.dbf
Recovery dropped tablespace 'test _ new'
Recovery ing file #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.
Deleted file/u02/dg11g/oradata/DG11G/test_new01.dbf
Recovery dropped tablespace 'test _ new'
Media Recovery Log/u02/dg11g/switchover/DG11G/archivelog/410124_880742847.dbf
Media Recovery Log/u02/dg11g/switchover/DG11G/archivelog/127125_880742847.dbf
Recovery ing file #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.
Deleted file/u02/dg11g/oradata/DG11G/test_new01.dbf
Recovery dropped tablespace 'test _ new'
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410126_880742847.dbf
Sat Jun 27 23:24:49 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/1_127_880742847.dbf
Sat Jun 27 23:25:01 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/2017128_880742847.dbf
Sat Jun 27 23:25:17 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410129_880742847.dbf
Sat Jun 27 23:25:29 2015

It is interesting to see that the data files have been repeatedly created and deleted many times. End with drop.
Then we began to use a lot of archive files for data recovery.

Sat Jun 27 23:28:30 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415172_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415173_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415174_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415175_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415176_880742847.dbf
Sat Jun 27 23:28:40 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410177_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415178_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/410179_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/109180_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/41518347880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415182_880742847.dbf
Sat Jun 27 23:28:52 2015
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415183_880742847.dbf
Media Recovery Log/u02/dg11g/flash_recovery_area/DG11G/archivelog/415184_880742847.dbf

In the master database, check that the redo serial number is 185, and the serial number in the slave database is 184.
Sys @ TEST11G> select sequence #, status from v $ log;
SEQUENCE # STATUS
--------------------------
184 INACTIVE
185 CURRENT
183 INACTIVE


Check the background process in the slave database. You can see that the MRP has been recorded.
Idle> select process, status, sequence # from v $ managed_standby;
Process status sequence #
-------------------------------
Arch connected 0
Arch connected 0
Arch connected 0
Arch connected 0
MRP0 wait_for_log186

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.