oracle active data guard,Oracle Active Data Guard调整案例

客户的Oracle 11gR2 Active Data Guard环境，主数据库的standby_file_management=AUTO，备用数据库的standby_file_management=MANUAL，导致在主数据库为表空间添加的数据文件操作没有同步到备用数据库，在$ORACLE_HOME/dbs目录下也没有创建类似UNNAMED00003的文件，备用数据库有如下的告警日志：

Tue Sep 02 17:37:36 2014

File #3 added to control file as 'UNNAMED00003' because

the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

The file should be manually created to continue.

MRP0: Background Media Recovery terminated with error 1274

Errors in file /u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_5702078.trc:

ORA-01274: cannot add datafile '/oradata1/d012band/tsmisc06.dbf' - file could not be created

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 866102511

Tue Sep 02 17:37:46 2014

MRP0: Background Media Recovery process shutdown (d012band)

Tue Sep 02 17:52:14 2014

RFS[1]: Selected log 8 for thread 1 sequence 19136 dbid 2134147111 branch 809469738

Tue Sep 02 17:52:25 2014

Archived Log entry 511 added for thread 1 sequence 19135 ID 0x7f340827 dest 1:

Tue Sep 02 17:53:23 2014

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (d012band)

Tue Sep 02 17:53:23 2014

MRP0 started with pid=42, OS id=7471452

MRP0: Background Managed Standby Recovery process started (d012band)

started logmerger process

Tue Sep 02 17:53:29 2014

Managed Standby Recovery starting Real Time Apply

Tue Sep 02 17:53:30 2014

Errors in file /u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_dbw0_4784178.trc:

ORA-01186: file 3 failed verification tests

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01111: name for data file 3 is unknown - rename to correct file

ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

如果能够找到/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003文件，可以参考文章：《11gR2 Active Data Guard调整案例[1]》http://blog.itpub.net/23135684/viewspace-759592/

File 3 not verified due to error ORA-01157

MRP0: Background Media Recovery terminated with error 1111

Errors in file /u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_8716760.trc:

ORA-01111: name for data file 3 is unknown - rename to correct file

ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01111: name for data file 3 is unknown - rename to correct file

ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

Managed Standby Recovery not using Real Time Apply

Slave exiting with ORA-1111 exception

Errors in file /u01/app/oracle/diag/rdbms/d012dg/d012band/trace/d012band_pr00_8716760.trc:

ORA-01111: name for data file 3 is unknown - rename to correct file

ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01111: name for data file 3 is unknown - rename to correct file

ORA-01110: data file 3: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00003'

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (d012band)

Completed: alter database recover managed standby database using current logfile disconnect from session

解决这个问题的关键是手动创建新添加的数据文件，参考如下的内容解决该问题：

How to resolve MRP stuck issues on a physical standby database? (文档 ID 1221163.1)

......

Solution 10 Add the new datafiles to the standby database manually.

1) Please take a hot backup of new datafiles from the primary database.

2) Create a new standby controlfile from the primary database by

SQL>alter database create standby controlfile as '/tmp/controlf.ctl';

If datafiles are on ASM, please follow the note below and you could ignore the rest of steps:

Note 734862.1 Step By Step Guide On How To Recreate Standby Control File

When Datafiles Are On ASM And Using Oracle Managed Files

Or you could modify the wrong datafile name in the standby controlfile by alter database rename command. For example,

SQL> ALTER DATABASE RENAME FILE '' to '';

3) If the new datafile location on the primary is different from the standby, please make sure

db_file_name_convert init parameter is set on the standby database.

Note 47325.1 Init.ora Parameter "DB_FILE_NAME_CONVERT" Reference Note

If db_file_name_convert init parameter has already been set, then you could ignore this step.

4) Cancel the managed recovery

SQL>alter database recover managed standby database cancel;

5) set standby_file_management=manual on the standby database and shutdown the standby database.

SQL>alter system set standby_file_management=manual sid='*';

SQL>shutdown immediate;

6) Copy the hot backup of the new datafiles and the new standby controlfile to the standby.

Please make sure the controlfiles are located in the right location with right names

according to the init parameter control_files. Please make sure the copied datafiles are

located in the right location as well according to name from v$datafile.

7) startup the standby database in mount mode and set standby_file_management=auto.

SQL>startup mount;

SQL>alter system set standby_file_management=auto sid='*';

8) Start the managed recovery.

SQL>alter database recover managed standby database disconnect;

......

--end--

posted on 2014-09-09 10:23 顺其自然EVO 阅读(329) 评论(0)  编辑  收藏 所属分类: 数据库