OS：Linux
DB：10.2.0.5 （主库RAC+ASM ，备库 单机+文件系统）
错误现象：
启动DG的时候运行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
这个命令之后，DG没有正常同步，
查看alert日志发现了以下错误：
- Sat Dec 26 21:52:42 CST 2015
- Errors in file /oracle/admin/sjdb/bdump/sjdb_mrp0_4741.trc:
- ORA-01111: name for data file 163 is unknown - rename to correct file
- ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
- ORA-01157: cannot identify/lock data file 163 - see DBWR trace file
- ORA-01111: name for data file 163 is unknown - rename to correct file
- ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
- Managed Standby Recovery not using Real Time Apply
- Sat Dec 26 21:52:42 CST 2015
- Errors in file /oracle/admin/sjdb/bdump/sjdb_mrp0_4741.trc:
- ORA-01111: name for data file 163 is unknown - rename to correct file
- ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
- ORA-01157: cannot identify/lock data file 163 - see DBWR trace file
- ORA-01111: name for data file 163 is unknown - rename to correct file
- ORA-01110: data file 163: '/oracle/db/dbs/UNNAMED00163'
- Sat Dec 26 21:52:42 CST 2015
- MRP0: Background Media Recovery process shutdown (sjdb)
-
SQL> select name from v$datafile where file#=162;
-
-
NAME
-
------------------------------------------------------
- /oracle/db/dbs/UNNAMED00163
- Case Study
- 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.
- 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.
- This is what happened in this case. Now I am going to show you how to solve the problem.
- How to resolve ORA-01111, ORA-01110, ORA-01157
- 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.
- 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.
- 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的错误
-
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
-
- System altered.
-
SQL> alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf';
-
-
Database altered.
-
-
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
-
- System altered.
-
SQL> alter database recover managed standby database using current logfile disconnect;
-
- Database altered.
- Sat Dec 26 21:58:24 CST 2015
- alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
- Sat Dec 26 21:58:24 CST 2015
- ORA-1275 signalled during: alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'...
- Sat Dec 26 21:59:07 CST 2015
- ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
- Sat Dec 26 21:59:15 CST 2015
- alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
- Sat Dec 26 21:59:15 CST 2015
- Completed: alter database create datafile '/oracle/db/dbs/UNNAMED00163' as '/oradata2/sjdb/gdyjzs_lob14.dbf'
- Sat Dec 26 22:00:23 CST 2015
- ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
- alter database recover managed standby database using current logfile disconnect;^H^H^H^H^HSat Dec 26 22:00:42 CST 2015
- alter database recover managed standby database using current logfile disconnect
- Sat Dec 26 22:00:42 CST 2015
- Attempt to start background Managed Standby Recovery process (sjdb)
- MRP0 started with pid=27, OS id=5912
- Sat Dec 26 22:00:42 CST 2015
- MRP0: Background Managed Standby Recovery process started (sjdb)
- Managed Standby Recovery starting Real Time Apply
- parallel recovery started with 16 processes
- Sat Dec 26 22:00:48 CST 2015
- Waiting for all non-current ORLs to be archived...
- Media Recovery Log /oradata2/arch/1_150731_779903527.dbf
至此，此故障处理完成，DG同步正常。