Oracle DataGuard:ORA-01111: name for data file xxx is unknown – rename to correct file
在DG的使用过程中,可能会因为备库的空间不够的问题导致备库日志应用(MRP)失败。
这种情况下,备库的Alert日志可能会呈现如下的报错:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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日志记录可能会如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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的状况:
1 2 3 4 5 6 7 8 9 |
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> |
文件系统中的状态:
1 2 |
uxgdc106:oraern 2> ls -ltr /oracle/ORCL/112_64/dbs/ | grep 317 uxgdc106:oraern 3> |
文件系统里并不存在。
之前传输失败的数据文件:
1 2 |
uxgdc106:oraern 4> ls -ltr /oracle/ORCL/mydata23/xer_288/ uxgdc106:oraern 5> |
数据库修复:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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> |
再查看之前缺少的数据文件:
1 2 3 4 |
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> |
恢复日志应用:
恢复前的日志应用状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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进程状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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进程:
1 2 3 |
SQL> recover managed standby database disconnect from session; Media recovery complete. SQL> |
Alert日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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的进程状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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> |
查看日志应用的情况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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。