环境 rac+双standby
standby
standby2
按时间恢复报找不到归档
Media Recovery Log /home/oracle/oradata/rac/archive/3_59241_681301512.arc
Errors with log /home/oracle/oradata/rac/archive/3_59241_681301512.arc
ORA-279 signalled during: alter database recover automatic standby database until time '2013-04-26 00:00:59'...
*************************************************************
Warning: Recovery session ended without issuing ALTER DATABASE RECOVER CANCEL
登录standby2尝试手工执行
SQL> alter database recover automatic standby database until time '2013-04-26 00:00:59';
alter database recover automatic standby database until time '2013-04-26 00:00:59'
*
ERROR at line 1:
ORA-00279: change 32513458385 generated at 04/25/2013 16:06:45 needed for
thread 3
ORA-00289: suggestion : /home/oracle/oradata/rac/archive/3_59247_681301512.arc
ORA-00280: change 32513458385 for thread 3 is in sequence #59247
ORA-00278: log file '/home/oracle/oradata/rac/archive/3_59247_681301512.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/oradata/rac/archive/3_59247_681301512.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 6056574976 bytes
Fixed Size 2092808 bytes
Variable Size 671088888 bytes
Database Buffers 5368709120 bytes
Redo Buffers 14684160 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
登录检查断点恢复到的数据时间：
[oracle@autodb ~]$ sqlplus db1/password
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 09:53:18 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(max(log_time),'yyyy-mm-dd hh24:mi:ss') from log partition(RL_201304);
TO_CHAR(MAX(LOG_TIM
-------------------
2013-04-25 16:55:48
归档卡在了4月25日下午，当时rac节点有出现过负载过高被剔除集群重启的问题
尝试拷贝报告缺失的归档过来后再指定时间恢复，可以恢复，但继续报缺失
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@autodb ~]$ dba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 10:03:42 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 6056574976 bytes
Fixed Size 2092808 bytes
Variable Size 671088888 bytes
Database Buffers 5368709120 bytes
Redo Buffers 14684160 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover automatic standby database until time '2013-04-26 00:00:59';
alter database recover automatic standby database until time '2013-04-26 00:00:59'
*
ERROR at line 1:
ORA-00279: change 32513835030 generated at 04/25/2013 16:55:49 needed for
thread 3
ORA-00289: suggestion : /home/oracle/oradata/rac/archive/3_59248_681301512.arc
ORA-00280: change 32513835030 for thread 3 is in sequence #59248
ORA-00278: log file '/home/oracle/oradata/rac/archive/3_59248_681301512.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/oradata/rac/archive/3_59248_681301512.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 6056574976 bytes
Fixed Size 2092808 bytes
Variable Size 671088888 bytes
Database Buffers 5368709120 bytes
Redo Buffers 14684160 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover automatic standby database until time '2013-04-26 00:00:59';
alter database recover automatic standby database until time '2013-04-26 00:00:59'
*
ERROR at line 1:
ORA-00279: change 32514318200 generated at 04/25/2013 17:49:11 needed for
thread 3
ORA-00289: suggestion : /home/oracle/oradata/rac/archive/3_59249_681301512.arc
ORA-00280: change 32514318200 for thread 3 is in sequence #59249
ORA-00278: log file '/home/oracle/oradata/rac/archive/3_59249_681301512.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/oradata/rac/archive/3_59249_681301512.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@autodb ~]$ ssh root@192.168.50.3
root@192.168.50.53's password:
[root@rac3 ~]#
[root@rac3 ~]#
[root@rac3 ~]# su - oracle
[oracle@rac3 ~]$ dba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 10:33:37 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/home/oracle/oradata/
newrac1/arch/rac3
log_archive_dest_10 string
log_archive_dest_2 string service=standby
log_archive_dest_3 string ##原来的standby2不见了，原因为dest_3参数修改的时候没加both写到所有配置
log_archive_dest_4 string
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
spfile string /home/oracle/10.2.0.4/db/dbs/s
pfilerac3.ora
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac3 ~]$ cat /home/oracle/10.2.0.4/db/dbs/initrac3.ora |grep log_archive_dest
*.log_archive_dest_1=''
rac3.log_archive_dest_1='LOCATION=/home/oracle/oradata/newrac1/arch/rac3'
*.log_archive_dest_2='service=standby'
*.log_archive_dest_state_2='ENABLE'
[oracle@rac3 ~]$ dba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 10:37:02 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE=standby2' SCOPE=BOTH SID='rac3';
System altered.
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/home/oracle/oradata/
newrac1/arch/rac3
log_archive_dest_10 string
log_archive_dest_2 string service=standby
log_archive_dest_3 string SERVICE=standby2
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac3 ~]$ bd
[oracle@rac3 bdump]$ tail -100 alert_rac3.log
ALTER SYSTEM SET log_archive_dest_3='SERVICE=standby2' SCOPE=BOTH SID='rac3';
[oracle@rac3 bdump]$ exit
logout
修改后回到standby2检查
日志进程RFS开始传送缺失日志
[root@rac3 ~]# exit
logout
Connection to 192.168.50.53 closed.
[oracle@autodb ~]$ bd
[oracle@autodb bdump]$ tail -f alert_esal.log
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59256_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59257_681301512.arc'
Fri Apr 26 10:38:36 2013
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59258_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59259_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59260_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59261_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59262_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59263_681301512.arc'
RFS[4]: Archived Log: '/home/oracle/oradata/rac/archive/3_59264_681301512.arc'
[oracle@autodb bdump]$ dba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 10:45:21 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
再次做手工指定时间点恢复
SQL> alter database recover automatic standby database until time '2013-04-26 00:00:59';
Database altered.
打开只读模式检查恢复到的时间点与指定时间点接近，恢复完成，故障解决
SQL> alter database open read only;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@autodb bdump]$ sqlplus db1/password
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 10:56:08 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(max(log_time),'yyyy-mm-dd hh24:mi:ss') from log partition(RL_201304);
TO_CHAR(MAX(LOG_TIM
-------------------
2013-04-26 00:00:58
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
检查rac1 rac2 rac4分别检查后，全部补全
[oracle@rac1 dbs]$ dba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 26 11:31:18 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE=standby2' SCOPE=BOTH SID='rac1';
System altered.
SQL> set linesize 200
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/home/oracle/oradata/
newrac1/arch/rac1
log_archive_dest_10 string
log_archive_dest_2 string service=standby
log_archive_dest_3 string SERVICE=standby2
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL> create pfile from spfile;
File created.
By cycsa http://www.cnblogs.com/cycsa
