oracle standby 创建数据文件报ORA-01111解决

Oracle 的physical standby节点上的介质恢复操作终止，报错ORA-01111错误，一个新文件不能创建。

在alert.log中记录的错误信息如下：

Tue Nov 20 17:16:48 2012
MRP0: Background Media Recovery terminated with error 1111
Tue Nov 20 17:16:48 2012
Errors in file /u01/oracle/app/oracle/admin/testdb/bdump/testdb1_mrp0_31483.trc:
ORA-01111: name for data file 42 is unknown - rename to correct file
ORA-01110: data file 42: '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042'
ORA-01157: cannot identify/lock data file 42 - see DBWR trace file
ORA-01111: name for data file 42 is unknown - rename to correct file
ORA-01110: data file 42: '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042'
Tue Nov 20 17:16:48 2012
Errors in file /u01/oracle/app/oracle/admin/testdb/bdump/testdb1_mrp0_31483.trc:
ORA-01111: name for data file 42 is unknown - rename to correct file
ORA-01110: data file 42: '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042'
ORA-01157: cannot identify/lock data file 42 - see DBWR trace file
ORA-01111: name for data file 42 is unknown - rename to correct file
ORA-01110: data file 42: '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042'
Tue Nov 20 17:16:48 2012
MRP0: Background Media Recovery process shutdown (testdb1)
Tue Nov 20 17:16:48 2012
Completed: alter database recover managed standby database disconnect from session
Tue Nov 20 17:16:53 2012

 

检查备库节点上standby_file_management的配置值，如果是manual的话，表示系统不能自动创建数据文件；而如果是auto的话，则系统会自动创建，但创建时可能因这样那样的因素而失败。

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

 

数据库初始化参数standby_file_management的值是auto。

于是，我们手工重建它。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1731218 )

第一步，将standby_file_management修改为manual，在auto下不允许自己创建文件。

SQL> alter system set standby_file_management=manual;

System altered.

 

在auto下操作会失败，错误会是这样：

SQL> alter database create datafile '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042' as '+VDG1/testdb/datafile/tbs_test12301.dbf';
alter database create datafile '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042' as '+VDG1/testdb/datafile/tbs_test12301.dbf'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.

 

第二步，使用alter database create datafile 'old filename' as 'new filename';重建数据文件。

这个'new filename'的名称从主库去查，保持一致即可。

SQL> alter database create datafile '/u01/oracle/app/oracle/product/10.2.0/db/dbs/UNNAMED00042' as '+VDG1/testdb/datafile/tbs_test12301.dbf';

Database altered.

 

第三步，将standby_file_management修改回来。

SQL> alter system set standby_file_management=auto;

 

这个问题是因为新建文件在VDG1下没有目录/testdb/datafile，手工创建一下，就可以解决掉。