Oracle DataGuard：ORA-01111: name for data file xxx is unknown – rename to correct file

Posted on: 2016年6月29日 2016年6月29日
Categories: Oracle Dataguard
Tags: dataguard, DG, ORA-01111: name for data file xxx is unknown - rename to correct file, Oracle

在DG的使用过程中，可能会因为备库的空间不够的问题导致备库日志应用（MRP）失败。

这种情况下，备库的Alert日志可能会呈现如下的报错：

Thu Jun 23 11:15:35 2016
Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_6684884.trc:
ORA-19502: write error on file "/oracle/ORCL/mydata23/xer_288/xer.data288", block number 1476480 (block size=8192)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 1048576
File #317 added to control file as 'UNNAMED00317'.
Originally created as:
'/oracle/ORCL/mydata23/xer_288/xer.data288'
Recovery was unable to create the file as:
'/oracle/ORCL/mydata23/xer_288/xer.data288'
Errors with log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801776_705619077.arch
MRP0: Background Media Recovery terminated with error 1274
Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_6684884.trc:
ORA-01274: cannot add datafile '/oracle/ORCL/mydata23/xer_288/xer.data288' - file could not be created
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 4564125927924 but controlfile could be ahead of datafiles.
Thu Jun 23 11:15:38 2016
MRP0: Background Media Recovery process shutdown (ORCL)

Thu Jun 23 11:15:35 2016

Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_6684884.trc:

ORA-19502: write error on file "/oracle/ORCL/mydata23/xer_288/xer.data288", block number 1476480 (block size=8192)

ORA-27063: number of bytes read/written is incorrect

IBM AIX RISC System/6000 Error: 28: No space left on device

Additional information: -1

Additional information: 1048576

File #317 added to control file as 'UNNAMED00317'.

Originally created as:

'/oracle/ORCL/mydata23/xer_288/xer.data288'

Recovery was unable to create the file as:

'/oracle/ORCL/mydata23/xer_288/xer.data288'

Errors with log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801776_705619077.arch

MRP0: Background Media Recovery terminated with error 1274

Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_6684884.trc:

ORA-01274: cannot add datafile '/oracle/ORCL/mydata23/xer_288/xer.data288' - file could not be created

Recovery interrupted!

Recovery stopped due to failure in applying recovery marker (opcode 17.30).

Datafiles are recovered to a consistent state at change 4564125927924 but controlfile could be ahead of datafiles.

Thu Jun 23 11:15:38 2016

MRP0: Background Media Recovery process shutdown (ORCL)

通过报错，你可以看到：
因为空间不足，DG备库跟着主库创建文件“/oracle/ORCL/mydata23/xer_288/xer.data288”的时候失败，导致备库的日志应用进程终止。

在你修复了备库的空间问题之后，你尝试重新拉起MRP进程，则依旧可能遇到问题。
Alert日志记录可能会如下所示：

Wed Jun 29 11:40:32 2016
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (ORCL)
Wed Jun 29 11:40:32 2016
MRP0 started with pid=46, OS id=4916352 
MRP0: Background Managed Standby Recovery process started (ORCL)

Wed Jun 29 11:40:34 2016
Archived Log entry 294597 added for thread 1 sequence 810208 rlc 705619077 ID 0xb3081c3 dest 4:
 started logmerger process
Wed Jun 29 11:40:37 2016
Managed Standby Recovery not using Real Time Apply

MRP0: Background Media Recovery terminated with error 1111
Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_5570834.trc:
ORA-01111: name for data file 317 is unknown - rename to correct file
ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'
ORA-01157: cannot identify/lock data file 317 - see DBWR trace file
ORA-01111: name for data file 317 is unknown - rename to correct file
ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'
Slave exiting with ORA-1111 exception
Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_5570834.trc:
ORA-01111: name for data file 317 is unknown - rename to correct file
ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'
ORA-01157: cannot identify/lock data file 317 - see DBWR trace file
ORA-01111: name for data file 317 is unknown - rename to correct file
ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'
Wed Jun 29 11:40:43 2016
Completed: alter database recover managed standby database disconnect from session

Wed Jun 29 11:40:44 2016
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (ORCL)

Wed Jun 29 11:40:32 2016

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (ORCL)

Wed Jun 29 11:40:32 2016

MRP0 started with pid=46, OS id=4916352

MRP0: Background Managed Standby Recovery process started (ORCL)

Wed Jun 29 11:40:34 2016

Archived Log entry 294597 added for thread 1 sequence 810208 rlc 705619077 ID 0xb3081c3 dest 4:

started logmerger process

Wed Jun 29 11:40:37 2016

Managed Standby Recovery not using Real Time Apply

MRP0: Background Media Recovery terminated with error 1111

Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_5570834.trc:

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

ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'

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

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

ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'

Slave exiting with ORA-1111 exception

Errors in file /oracle/ORCL/saptrace/diag/rdbms/standby1/ORCL/trace/ORCL_pr00_5570834.trc:

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

ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'

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

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

ORA-01110: data file 317: '/oracle/ORCL/112_64/dbs/UNNAMED00317'

Wed Jun 29 11:40:43 2016

Completed: alter database recover managed standby database disconnect from session

Wed Jun 29 11:40:44 2016

Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (ORCL)

解决方法：
首先查看下出错的317的状况：

SQL> set linesize 400
SQL> col name for a80
SQL> select file#,name from v$datafile where file#=317;

     FILE# NAME
---------- --------------------------------------------------------------------------------
       317 /oracle/ORCL/112_64/dbs/UNNAMED00317

SQL>

SQL> set linesize 400

SQL> col name for a80

SQL> select file#,name from v$datafile where file#=317;

FILE# NAME

---------- --------------------------------------------------------------------------------

317 /oracle/ORCL/112_64/dbs/UNNAMED00317

SQL>

文件系统中的状态：

uxgdc106:oraern 2> ls -ltr /oracle/ORCL/112_64/dbs/ | grep 317
uxgdc106:oraern 3>

1 2	uxgdc106:oraern 2> ls -ltr /oracle/ORCL/112_64/dbs/ \| grep 317 uxgdc106:oraern 3>

文件系统里并不存在。

之前传输失败的数据文件：

uxgdc106:oraern 4> ls -ltr /oracle/ORCL/mydata23/xer_288/
uxgdc106:oraern 5>

1 2	uxgdc106:oraern 4> ls -ltr /oracle/ORCL/mydata23/xer_288/ uxgdc106:oraern 5>

数据库修复：

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ORCL              MOUNTED

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL       PHYSICAL STANDBY

SQL> 
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> 
SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>                
SQL> set linesize 400
SQL> col name for a50
SQL> select file#,name from v$datafile where file#=317;

     FILE# NAME
---------- --------------------------------------------------
       317 /oracle/ORCL/112_64/dbs/UNNAMED00317

SQL> 
SQL> alter database create datafile '/oracle/ORCL/112_64/dbs/UNNAMED00317' as '/oracle/ORCL/mydata23/xer_288/xer.data288';

Database altered.

SQL> select file#,name from v$datafile where file#=317;

     FILE# NAME
---------- --------------------------------------------------
       317 /oracle/ORCL/mydata23/xer_288/xer.data288

SQL> alter system set standby_file_management=auto;

System altered.

SQL>

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

ORCL MOUNTED

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE

--------- ----------------

ORCL PHYSICAL STANDBY

SQL>

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

SQL>

SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string MANUAL

SQL>

SQL> set linesize 400

SQL> col name for a50

SQL> select file#,name from v$datafile where file#=317;

FILE# NAME

---------- --------------------------------------------------

317 /oracle/ORCL/112_64/dbs/UNNAMED00317

SQL>

SQL> alter database create datafile '/oracle/ORCL/112_64/dbs/UNNAMED00317' as '/oracle/ORCL/mydata23/xer_288/xer.data288';

Database altered.

SQL> select file#,name from v$datafile where file#=317;

FILE# NAME

---------- --------------------------------------------------

317 /oracle/ORCL/mydata23/xer_288/xer.data288

SQL> alter system set standby_file_management=auto;

System altered.

SQL>

再查看之前缺少的数据文件：

uxgdc106:oraORCL 4> ls -ltr /oracle/ORCL/mydata23/xer_288/
total 61441144
-rw-------    1 oraORCL   dba      31457288192 Jun 29 12:45 xer.data288
uxgdc106:oraORCL 5>

uxgdc106:oraORCL 4> ls -ltr /oracle/ORCL/mydata23/xer_288/

total 61441144

-rw------- 1 oraORCL dba 31457288192 Jun 29 12:45 xer.data288

uxgdc106:oraORCL 5>

恢复日志应用：
恢复前的日志应用状态：

SQL> select sequence#,to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where sequence# between 801774 and 801779 order by sequence#;

 SEQUENCE# TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ---------
    801774 2016-06-23 11:20:41 YES
    801775 2016-06-23 11:21:02 YES
    801776 2016-06-23 11:21:35 NO
    801777 2016-06-23 11:21:38 NO
    801778 2016-06-23 11:22:06 NO
    801779 2016-06-23 11:22:30 NO

6 rows selected.

SQL>

SQL> select sequence#,to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where sequence# between 801774 and 801779 order by sequence#;

SEQUENCE# TO_CHAR(NEXT_TIME,' APPLIED

---------- ------------------- ---------

801774 2016-06-23 11:20:41 YES

801775 2016-06-23 11:21:02 YES

801776 2016-06-23 11:21:35 NO

801777 2016-06-23 11:21:38 NO

801778 2016-06-23 11:22:06 NO

801779 2016-06-23 11:22:30 NO

6 rows selected.

SQL>

查看MRP进程状态：

SQL> set linesize 400
SQL> col client_pid for a20
SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby;

PROCESS          PID CLIENT_PID           STATUS       DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ---------- -------------------- ------------ ---------- ------------ -------------
ARCH         6422908 6422908              CONNECTED             0            0             0
ARCH         6620154 6620154              CONNECTED             0            0             0
ARCH         4129556 4129556              CONNECTED             0            0             0
ARCH         6357556 6357556              CONNECTED             0            0             0
RFS          5243866 15794592             RECEIVING             0            0             0
RFS          3015402 27984308             IDLE                  0            0             0
RFS          4719454 17825812             RECEIVING             0            0             0
RFS          3932406 12451942             RECEIVING             0            0             0

8 rows selected.

SQL>

SQL> set linesize 400

SQL> col client_pid for a20

SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby;

PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

--------- ---------- -------------------- ------------ ---------- ------------ -------------

ARCH 6422908 6422908 CONNECTED 0 0 0

ARCH 6620154 6620154 CONNECTED 0 0 0

ARCH 4129556 4129556 CONNECTED 0 0 0

ARCH 6357556 6357556 CONNECTED 0 0 0

RFS 5243866 15794592 RECEIVING 0 0 0

RFS 3015402 27984308 IDLE 0 0 0

RFS 4719454 17825812 RECEIVING 0 0 0

RFS 3932406 12451942 RECEIVING 0 0 0

8 rows selected.

SQL>

重新拉起MRP进程：

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL>

Alert日志：

Wed Jun 29 12:56:09 2016
ALTER DATABASE RECOVER  managed standby database disconnect from session  
Attempt to start background Managed Standby Recovery process (ORCL)
Wed Jun 29 12:56:09 2016
MRP0 started with pid=46, OS id=4456472 
MRP0: Background Managed Standby Recovery process started (ORCL)

started logmerger process
Wed Jun 29 12:56:14 2016
Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 20 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.

（... ... 过多的输出。）
Clearing online redo logfile 4 complete
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801776_705619077.arch
Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session  
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801777_705619077.arch
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801778_705619077.arch
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801779_705619077.arch
Wed Jun 29 12:56:32 2016
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801780_705619077.arch
Wed Jun 29 12:56:45 2016
Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801781_705619077.arch

Wed Jun 29 12:56:09 2016

ALTER DATABASE RECOVER managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (ORCL)

Wed Jun 29 12:56:09 2016

MRP0 started with pid=46, OS id=4456472

MRP0: Background Managed Standby Recovery process started (ORCL)

started logmerger process

Wed Jun 29 12:56:14 2016

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 20 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

（... ... 过多的输出。）

Clearing online redo logfile 4 complete

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801776_705619077.arch

Completed: ALTER DATABASE RECOVER managed standby database disconnect from session

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801777_705619077.arch

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801778_705619077.arch

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801779_705619077.arch

Wed Jun 29 12:56:32 2016

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801780_705619077.arch

Wed Jun 29 12:56:45 2016

Media Recovery Log /oracle/ORCL/oraarch/ORCLarch/standby1_1_801781_705619077.arch

再次查看MRP的进程状态：

SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby;

PROCESS          PID CLIENT_PID           STATUS       DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ---------- -------------------- ------------ ---------- ------------ -------------
ARCH         6422908 6422908              CONNECTED             0            0             0
ARCH         6620154 6620154              CONNECTED             0            0             0
ARCH         4129556 4129556              CONNECTED             0            0             0
ARCH         6357556 6357556              CONNECTED             0            0             0
RFS          5243866 15794592             RECEIVING             0            0             0
RFS          3015402 27984308             IDLE                  0            0             0
RFS          4719454 17825812             RECEIVING             0            0             0
RFS          3932406 12451942             RECEIVING             0            0             0
MRP0         4456472 N/A                  APPLYING_LOG          0           21            21

9 rows selected.

SQL>

SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby;

PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS

--------- ---------- -------------------- ------------ ---------- ------------ -------------

ARCH 6422908 6422908 CONNECTED 0 0 0

ARCH 6620154 6620154 CONNECTED 0 0 0

ARCH 4129556 4129556 CONNECTED 0 0 0

ARCH 6357556 6357556 CONNECTED 0 0 0

RFS 5243866 15794592 RECEIVING 0 0 0

RFS 3015402 27984308 IDLE 0 0 0

RFS 4719454 17825812 RECEIVING 0 0 0

RFS 3932406 12451942 RECEIVING 0 0 0

MRP0 4456472 N/A APPLYING_LOG 0 21 21

9 rows selected.

SQL>

查看日志应用的情况：

SQL> select sequence#,to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where sequence# between 801774 and 801779 order by sequence#;

 SEQUENCE# TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ---------
    801774 2016-06-23 11:20:41 YES
    801775 2016-06-23 11:21:02 YES
    801776 2016-06-23 11:21:35 YES
    801777 2016-06-23 11:21:38 YES
    801778 2016-06-23 11:22:06 YES
    801779 2016-06-23 11:22:30 YES

6 rows selected.

SQL>

SQL> select sequence#,to_char(next_time,'yyyy-mm-dd hh24:mi:ss'),applied from v$archived_log where sequence# between 801774 and 801779 order by sequence#;

SEQUENCE# TO_CHAR(NEXT_TIME,' APPLIED

---------- ------------------- ---------

801774 2016-06-23 11:20:41 YES

801775 2016-06-23 11:21:02 YES

801776 2016-06-23 11:21:35 YES

801777 2016-06-23 11:21:38 YES

801778 2016-06-23 11:22:06 YES

801779 2016-06-23 11:22:30 YES

6 rows selected.

SQL>

可以看到，日志应用进程恢复。

至此，该问题的解决，完成。
————————————
Done。