Analysis and solution of the problem that MRP can not start in dataguard 01/18 Update SLTechnology News&Howtos

Analysis and solution of the problem that MRP can not start in dataguard

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

I had a dataguard environment on hand, because it was useless for some days, but I suddenly got ready to start learning on a whim. I suddenly found that after typing the command recover managed standby database disconnect from session, the command ran normally, but the background reported an ora error.

Sat Jun 27 23:16:39 2015

Recovery Slave PR00 previously 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 Universe DG11g Universe oradata Universe DG11G Universe 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

From the log above, we can see that the MRP process reported an ora error ora-01157 while doing data recovery.

However, there is no problem with RFS. RFS mainly transfers archive files from the main database. You can see that archive logs can be transferred normally from the main database, and the archive logs with sequence# number 171173172 have been transferred to the standby database.

Originally, this problem did not attract much attention. I wondered which archived files might not be used, but I found that MRP could not be used at all. Therefore, although the archive transfer is completed, the data changes still cannot be applied to the standby database.

Check that there is no record in v$archive_gap, indicating that there is a problem when there is no archived log apply.

Let's take a look at some of the details of this ora problem, indicating that an ora-01157 error was reported in 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 Universe DG11g Universe oradata Universe DG11G Universe new01.dbf'

From the official description of the problem, it seems that there is something wrong with the data file.

$oerr ora 01157

01157, 00000, "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 the

/ / operating system describes why the file could 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 around, switched and tested over and over again, and I can't remember which special operations caused the problem. So this problem has to be analyzed from the beginning.

First I looked at the file / u02/dg11g/oradata/DG11G/test_new01.dbf and found that it didn't exist in the file system.

However, it exists in the data dictionary information, and the sql statement used is that the corresponding record can be returned.

Select name,file# from v$datafile where file#=7

From this point of view, it may be caused by mistakenly deleting this data file on the standby side. How do we evaluate the deleted data files? first, we have to look at the main library and check the files in the main library, but the data files and tablespaces do not exist in the main library at all.

This makes the problem a little tricky.

If you can fix the MRP problem, it seems that the problem will be solved. If it cannot be fixed, the dataguard may not be available, and you may have to consider rebuilding a physical repository.

We take a conservative attitude towards this and try to see if the backup library can be started to the open read only state.

But the results of these three operations made me a little confused.

Open no, said that may need to restore, restore the file turned out to be system01.dbf, try recover until cancel also failed.

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 scarp dg11g Universe oradata Universe DG11GAccord 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 scarp dg11g Universe oradata Universe DG11GAccord system01.dbf'

For this problem, it would be nice if there is a sql statement that can solve the problem right to the point. After repeated attempts, I found that there are still some. The solution to the problem is to solve the ORA-01157 problem first, and then the MRP problem in dataguard can be easily solved.

For ora-01157, the data file in the problem does not exist in the main database, but it exists in the data dictionary of the standby database, so we can solve the problem in the data dictionary directly in the standby database.

Idle > alter database datafile'/ u02 alter database datafile'/ u02 offline drop DG11g Grey oradata

Database altered.

Then the dataguard log appears to transfer the machine, and the problem of verifying the file in the background just throws a warning. Warning: Datafile 7 (/ u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered

Then the MRP starts normally. The background began to use archived files for data recovery.

Alter database datafile'/ u02 DG11g offline drop DG11G offline drop

Completed: alter database datafile'/ u02 alter database datafile'/ u02 offline drop DG11g Universe oradata 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/1_121_880742847.dbf

Completed: ALTER DATABASE RECOVER managed standby database disconnect from session

Media Recovery Log / u02/dg11g/flash_recovery_area/DG11G/archivelog/1_122_880742847.dbf

Sat Jun 27 23:24:31 2015

Media Recovery Log / u02/dg11g/flash_recovery_area/DG11G/archivelog/1_123_880742847.dbf

Recovery deleting file # 7 Velcro from controlfile U02 DG 11g oradata.