ITPub博客

首页 > 数据库 > Oracle > DATA GUARD的STANDBY数据库ORA-01111 ORA-01110 ORA-01157错误

DATA GUARD的STANDBY数据库ORA-01111 ORA-01110 ORA-01157错误

原创 Oracle 作者:yangi1314 时间:2016-10-17 22:22:46 0 删除 编辑
环境: 
OS:Linux  
DB:10.2.0.5 (主库RAC+ASM ,备库 单机+文件系统) 
  
错误现象: 
启动DG的时候运行 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
这个命令之后,DG没有正常同步,
查看alert日志发现了以下错误: 

  1. Sat Dec 26 21:52:42 CST 2015
  2. Errors in file /oracle/admin/sjdb/bdump/sjdb_mrp0_4741.trc:
  3. ORA-01111: name for data file 163 is unknown - rename to correct file
  4. ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
  5. ORA-01157: cannot identify/lock data file 163 - see DBWR trace file
  6. ORA-01111: name for data file 163 is unknown - rename to correct file
  7. ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
  8. Managed Standby Recovery not using Real Time Apply
  9. Sat Dec 26 21:52:42 CST 2015
  10. Errors in file /oracle/admin/sjdb/bdump/sjdb_mrp0_4741.trc:
  11. ORA-01111: name for data file 163 is unknown - rename to correct file
  12. ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
  13. ORA-01157: cannot identify/lock data file 163 - see DBWR trace file
  14. ORA-01111: name for data file 163 is unknown - rename to correct file
  15. ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
  16. Sat Dec 26 21:52:42 CST 2015
  17. MRP0: Background Media Recovery process shutdown (sjdb)
查询数据文件视图,看到有这个文件,但是这个文件并不符合我的数据文件命名方式。
 
  1. SQL> select name from v$datafile where file#=162;

  2. NAME
  3. ------------------------------------------------------
  4. /oracle/db/dbs/UNNAMED00163
查看原因:
  1. Case Study
  2. Today I got a new problem in one of our banking client. After verifying the error, alert.log & Trace file I got the actual problem that on production database one datafile is created which is not created on desired location. Now we have to solve the error because bank need to synchronize database on urgent basis.
  3. Note: If your STANDBY_FILE_MANAGEMENT parameter is not configured Auto in Data Guard environment and file is created then file will created on standby server with unnamed file name in dbs directory.
  4. This is what happened in this case. Now I am going to show you how to solve the problem.
  5. How to resolve ORA-01111, ORA-01110, ORA-01157
  6. There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) improper parameter settings related to file management.
  7. STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
  8. For example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL , While recovery process(MRP) is trying to apply archives, Due to that parameter setting it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.
处理过程:
修改STANDBY_FILE_MANAGEMENT为MANUAL,这一步一定要修改,否则会出现ORA-1275的错误 
  1. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

  2. System altered.
重建数据文件,再修改STANDBY_FILE_MANAGEMENT为AUTO,
  1. SQL> alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf';
  2.  
  3. Database altered.
  4.  
  5. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
  6.  
  7. System altered.
启动备库同步。
  1. SQL> alter database recover managed standby database using current logfile disconnect;
  2.  
  3. Database altered.
查看Alert日志:

  1. Sat Dec 26 21:58:24 CST 2015
  2. alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
  3. Sat Dec 26 21:58:24 CST 2015
  4. ORA-1275 signalled during: alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'...
  5. Sat Dec 26 21:59:07 CST 2015
  6. ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
  7. Sat Dec 26 21:59:15 CST 2015
  8. alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
  9. Sat Dec 26 21:59:15 CST 2015
  10. Completed: alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
  11. Sat Dec 26 22:00:23 CST 2015
  12. ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
  13. alter database recover managed standby database using current logfile disconnect;^H^H^H^H^HSat Dec 26 22:00:42 CST 2015
  14. alter database recover managed standby database using current logfile disconnect
  15. Sat Dec 26 22:00:42 CST 2015
  16. Attempt to start background Managed Standby Recovery process (sjdb)
  17. MRP0 started with pid=27, OS id=5912
  18. Sat Dec 26 22:00:42 CST 2015
  19. MRP0: Background Managed Standby Recovery process started (sjdb)
  20. Managed Standby Recovery starting Real Time Apply
  21. parallel recovery started with 16 processes
  22. Sat Dec 26 22:00:48 CST 2015
  23. Waiting for all non-current ORLs to be archived...
  24. Media Recovery Log /oradata2/arch/1_150731_779903527.dbf

 至此,此故障处理完成,DG同步正常。
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/205377/viewspace-2126612/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-11

  • 博文量
    63
  • 访问量
    198932