查看日志是否在备库中应用，如下语句：
Sql>select sequence#,applied from v$archived_log;
SEQUENCE# APPLIE
---------- ------
6311 YES
6312 YES
6313 YES
6314 YES
6315 YES
6316 YES
6317 YES
6318 YES
6319 NO
6320 NO
6321 NO
SEQUENCE# APPLIE
---------- ------
6322 NO
6323 NO
6324 NO
6325 NO
6326 NO
6327 NO
6328 NO
6329 NO
6330 NO
6331 NO
6332 NO
从运行的结果看，备库中一些归档日志未被应用。刚开始以为系统慢影响的，但在下班时又运行此sql语句，却发现越来越多的日志没有应用，感到日志应用出现了问题。就去查看数据库的日志文件alter_orcl.log，发现MRP0进程无法启动。如下：
Physical Standby Database mounted.
Completed: alter database mount standby database
Fri Dec 31 10:33:05 2010
ALTER DATABASE RECOVER managed standby database disconnect
Fri Dec 31 10:33:05 2010
Attempt to start background Managed Standby Recovery process (orcl)
MRP0 started with pid=21, OS id=15612
Fri Dec 31 10:33:05 2010
MRP0: Background Managed Standby Recovery process started (orcl)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Fri Dec 31 10:33:10 2010
Errors in file /home/oracle/admin/orcl/bdump/orcl_mrp0_15612.trc:
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/oracle/OraHome1/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/oracle/OraHome1/dbs/UNNAMED00005'
Fri Dec 31 10:33:10 2010
Errors in file /home/oracle/admin/orcl/bdump/orcl_mrp0_15612.trc:
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/oracle/OraHome1/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/oracle/OraHome1/dbs/UNNAMED00005'
Fri Dec 31 10:33:10 2010
MRP0: Background Media Recovery process shutdown (orcl)
Fri Dec 31 10:33:11 2010
同时使用此sql语句查看备库上相关DG的进程如下，没有MRP0进程：
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
------------------ ------------------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
接下来就排查ORA-16136错误，在google和baidu中搜ORA-16136，出现的答案都不能解决问题。
然后搜索MRP0不能启动的问题，然后出现的答案还是不能解决问题。最后在日志中找到MRP0: Background Media Recovery terminated with error 1111此语句在GOOGLE中进行搜索，找到了答案进行尝试，可以了。其解决方法如下：
1.将自动管理改为手动；
Sql> ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=MEMORY;
2.恢复日志中提示的文件；
Sql> alter database create datafile '/home/oracle/OraHome1/dbs/UNNAMED00005'
as '/oradata/orcl_std/sys06.dbf';
此文件大约为60G;且说明sys06.dbf文件是一个新文件，按此命名即可；
3.关闭备库
Sql>shutdown immediate;
4.重新备库
Sql>startup nomount;
5.切换到standby 状态；
Sql>alter database mount standby database;
6.切换到应用归档日志状态
Sql>recover managed standby database disconnect;
此时再看查alert_orcl.log发现MRP0进程正常启动了，同时开始应用归档日志了。
7.使用引语句查看MRP0进程是否启动，如下已启动。
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
------------------ ------------------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
MRP0 APPLYING_LOG 6325
在此归档日志应用过程中，用户反映ERP操作太慢，但从OEM上看主库很正常，说明什么问题呢？
其原理：
正常备库应用的是归档文件，但当主数据库强行关机再重新启动后，主数据库会进行恢复工作，此时会有一部分数据不在归档日志中，但这部分数据会等主数据库恢复完后以UNNAME0005等这样的文件命名保存此数据，此文件保存在DBS中，但从linux中进去查看却看不到这个文件。同时这个数据也会传到备库对应的目录中。
若数据库应用日志的先后顺序是这样：redo1->redo2->redo3，但在主库强行关机时会有一部分数据unname在两个日志间，如redo1->redo2->unname-redo3。因此若不处理unname这部分数据文件的话，则备库在应用redo3时就会出错，提示找到文件，就会出现不能应用日志的情况。要想能正常应用日志，先处理unname的文件，处理后备库先应用unname文件，然后才能按日志的顺序应用。
原解决方案文档如下：
I came across this Data Guard situation lately where a datafile was added to the primary database, but failed during creation on the standby. The database version was 10.2.0.3 and standby_file_management was set to AUTO.
Standby_file_management is a parameter, and when set to AUTO on the standby site, will create datafiles automatically on the standby site, for every datafile created on the primary site.
I started getting alerts in a few minutes on the standby for the following error.
WARNING: File being created with same name as in Primary Existing file may be overwritten File #309 added to control file as 'UNNAMED00309'. Originally created as: '/disk1/oradata/primarydb01/sys06.dbf' Recovery was unable to create the file as: '/disk1/oradata/primary/sys06.dbf' Errors with log /disk1/archive/stbydb/sf2_1_215423_528599320.arc MRP0: Background Media Recovery terminated with error 1119 Tue Feb 5 07:42:09 2008 Errors in file /disk1/oracle/admin/stbydb2/bdump/stbydb2_mrp0_12552.trc: ORA-01119: error in creating database file '/disk1/oradata/sf12/sys06.dbf' ORA-27054: NFS file system where the file is created or resides is not mounted with correct options SVR4 Error: 13: Permission denied Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail Tue Feb 5 07:42:13 2008 Errors in file /disk1/oracle/admin/stbydb2/bdump/stbydb2_mrp0_12552.trc: ORA-01119: error in creating database file '/ora-disk/oradata/sf12/sys06.dbf' ORA-27054: NFS file system where the file is created or resides is not mounted with correct options SVR4 Error: 13: Permission denied Tue Feb 5 07:42:13 2008
From the message it was clear that the datafile creation had failed on the standby, even though standby_file_management was set to AUTO. It failed for the reason that the directory the datafile was created in, "/disk1/oradata/primary/", was not there on the Physical Standby. However, we want the directory "/disk1/oradata/primary/" to be "/disk1/oradata/standby/" on the standby. This is taken care of by "DB_FILE_NAME_CONVERT" parameter set on the standby. However, if you have several lines for this parameter,
SQL> Show parameter db_file_name_convert
will display as empty. At that point, the only way to check that value is to view the parameter file.
So login to standby database and issue
Step 1: SQL> create pfile from spfile;
Now go to $ORACLE_HOME/dbs directory for that initialization parameter file.
In our case, we had clearly missed the directory that the datafile was created on the standby.
Change the pfile to add that destination
Db_file_name_convert= '/disk1/oradata/primary','/disk1/oradata/standby'
Step 2: Now login to the standby and cancel the recovery
SQL> alter database recover standby managed database cancel;
Database altered
Step 3: Shutdown and startup the standby database using new parameter file.
SQL> shutdown immediate; ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount pfile=$ORACLE_HOME/dbs/initstandby.ora ORACLE instance started.
Total System Global Area 2147483648 bytes Fixed Size 2074152 bytes Variable Size 1056967128 bytes Database Buffers 1073741824 bytes Redo Buffers 14700544 bytes Database mounted.
SQL> create spfile from pfile;
Step 4: Start the managed recovery on the standby.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
The above command will tell standby to start applying archivelogs from where it was missing. I got the following errors in the alert log this time:
MRP0: Background Managed Standby Recovery process started (stbydb2) Managed Standby Recovery not using Real Time Apply MRP0: Background Media Recovery terminated with error 1111 Wed Feb 6 13:50:05 2008 Errors in file /disk1/oracle/admin/stbydb2/bdump/stbydb2_mrp0_22855.trc: ORA-01111: name for data file 309 is unknown - rename to correct file ORA-01110: data file 309: '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' ORA-01157: cannot identify/lock data file 309 - see DBWR trace file ORA-01111: name for data file 309 is unknown - rename to correct file ORA-01110: data file 309: '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' Wed Feb 6 13:50:05 2008 Errors in file /disk1/oracle/admin/stbydb2/bdump/stbydb2_mrp0_22855.trc: ORA-01111: name for data file 309 is unknown - rename to correct file ORA-01110: data file 309: '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' ORA-01157: cannot identify/lock data file 309 - see DBWR trace file ORA-01111: name for data file 309 is unknown - rename to correct file ORA-01110: data file 309: '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' Wed Feb 6 13:50:05 2008 MRP0: Background Media Recovery process shutdown (stbydb2) Wed Feb 6 13:50:05 2008 Completed: alter database recover managed standby database disconnect from session Wed Feb 6 13:52:09 2008 Using STANDBY_ARCHIVE_DEST parameter default value as /disk2/archive/stbydb2/ Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 23024
This is because whenever datafile creation fails, Oracle server will create an invisible datafile called UNNAMED and make it look like it is there under $ORACLE_HOME/dbs. When the automatic recovery is resumed, it looks at that UNNAMED file, and the standby fails again with the message:
ORA-01111: name for data file 309 is unknown - rename to correct file
Now you need to rename that file to the one that you want to create in order for the physical standby to continue applying remaining logs.
Step 5: So now when you try to issue the "rename" command, you will get this error:
SQL> alter database rename file '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' to '/disk1/oradata/stbydb2/sys06.dbf' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
This is because RENAME (ADD/DROP,CREATE) operations on the standby is not allowed, when standby_file_manageent is set to AUTO. Now you need to change the parameter to MANUAL for the above command to work.
Step 6: SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL scope=memory;
System altered.
Step 7: SQL> alter database rename file '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' to '/disk1/oradata/stbydb2/sys06.dbf'; alter database rename file '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' to '/disk1/oradata/stbydb2/sys06.dbf' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 309 - new file '/disk1/oradata/stbydb2/sys06.dbf' not found ORA-01111: name for data file 309 is unknown - rename to correct file ORA-01110: data file 309: '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
Oh... What now? Well, we can not rename to a file that doesn't already exist, so we actually need to create the datafile.
Step 8: SQL> alter database create datafile '/disk1/oracle/product/10.2.0/dbs/UNNAMED00309' as '/disk1/oradata/stbydb2/sys06.dbf';
Database altered.
Yes, finally! Ok, datafile created. Let's put the automatic recovery back on the standby.
Step 9: SQL> alter database recover standby database; alter database recover standby database * ERROR at line 1: ORA-00279: change 9999250009 generated at 02/05/2008 07:28:43 needed for thread 1 ORA-00289: suggestion : /disk2/archive/stbydb2/sf2_1_215423_528599320.arc ORA-00280: change 9999250009 for thread 1 is in sequence #215423
Now Oracle wants me to manually specify the archive log files to be applied. This is because I changed the standby_file_management parameter earlier to MANUAL. I need to put it back to AUTO.
Step 10: SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=memory;
System altered.
Step 11: Now issue the command SQL> recover automatic standby database;
Media recovery complete.
Check the alert log
Tail –f alert*
RFS[1]: Assigned to RFS process 27167 RFS[1]: Identified database type as 'physical standby' Wed Feb 6 14:48:32 2008 RFS LogMiner: Client disabled from further notification Wed Feb 6 14:48:45 2008 Media Recovery Log /disk2/archive/stbydb2/sf2_1_215425_528599320.arc Wed Feb 6 14:50:04 2008 RFS[1]: No standby redo logfiles created Wed Feb 6 14:51:32 2008 RFS[1]: Archived Log: '/disk2/archive/stbydb2/sf2_1_215542_528599320.arc' Wed Feb 6 14:56:42 2008 Recovery created file /disk1/oradata/stbydb2/users121.dbf Successfully added datafile 310 to media recovery Datafile #310: '/disk1/oradata/stbydb2/users121.dbf'
Yes, it successfully created the datafile and the recovery resumed. All the missing 105 archive log files were applied against 2.5TB OLTP database.
Best practices and lesson learned: Whenever you add a datafile to the primary, login to standby and make sure it was created successfully. The best way to do that is to issue "tail –f alert*" in the background_dump_dest directory. If for some reason the datafile creation has failed, cancel the recovery on the standby, and do only the steps 6, 8, 10 and 11 in this document.
About the Author: Rama Balaji is a Oracle Certified Professional with 16+ years of Oracle experience in maintaining and trouble shooting very critical production databases. She has been working as an Oracle consultant for various clients in finance, telecommunications, and the government agencies, specializing in database tuning with extensive experience in RMAN, Data Guard, RAC and ASM. Rama Balaji is currently employed with TUSC (The Ultimate Software Consultants)-A Rolta Company.
参考文档：http://cuuzhang.blog.163.com/blog/static/60811529201011311378125/
select severity,error_code,message from v$dataguard_status select sequence#,applied from v$archived_log; select process,status,sequence# from v$managed_standby;
