分类目录归档：Oracle备份恢复

sysaux数据文件异常恢复

发表于 2011 年 12 月 17 日 由 惜分飞

案例背景:在我接手这个库之前，因为某种原因sysaux表空间的数据文件离线，该库非归档模式，无备份
一、sysaux数据文件离线原因

Mon Jun  7 03:03:22 2010
KCF: write/open error block=0x67009 online=1
     file=3 /opt/app/oracle/oradata/BAS/sysaux01.dbf
     error=27072 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 421897
Additional information: -1'
Automatic datafile offline due to write error on
file 3: /opt/app/oracle/oradata/BAS/sysaux01.dbf

因为该数据库是非归档模式，估计以前的dba也是一段时间后发现sysaux被离线，因为不是归档模式，无法恢复，就一直放置着，让库处于这样的状态中。

二、sysaux数据文件online
1、使用bbed修改sysaux数据文件的scn，见:
bbed 修改datafile header
Oracle Recovery Tools快速解决sysaux文件不能online问题

2、尝试online过程日志如下

Sat Dec 17 19:33:36 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
Sat Dec 17 19:33:36 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:33:37 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 19:38:38 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
Sat Dec 17 19:38:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:38:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 19:39:47 2011
ALTER DATABASE RECOVER  datafile 3  
Sat Dec 17 19:39:47 2011
Media Recovery Start
 parallel recovery started with 7 processes
Sat Dec 17 19:39:47 2011
Recovery of Online Redo Log: Thread 1 Group 6 Seq 13545 Reading mem 0
  Mem# 0 errs 0: /opt/app/oracle/oradata/BAS/redo0602.log
  Mem# 1 errs 0: /opt/app/oracle/oradata/BAS/redo0601.log
Sat Dec 17 19:39:47 2011
Recovery of Online Redo Log: Thread 1 Group 7 Seq 13546 Reading mem 0
  Mem# 0 errs 0: /opt/app/oracle/oradata/BAS/redo0702.log
  Mem# 1 errs 0: /opt/app/oracle/oradata/BAS/redo0701.log
Sat Dec 17 19:39:47 2011
Media Recovery Complete (BAS)
Completed: ALTER DATABASE RECOVER  datafile 3  
Sat Dec 17 19:39:58 2011
alter database datafile 3 online
Sat Dec 17 19:39:58 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'
Sat Dec 17 19:39:58 2011
Completed: alter database datafile 3 online

这个过程虽然在sqlplus中提示online成功，但是alert中的错误警告，以及smon进程占用100%的cup资源，最终导致数据库hang住。

2、分析alert日志和trace文件

alert日志中
Sat Dec 17 19:38:38 2011
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.

bas_smon_27197.trc中
[oracle@bas bdump]$ more /opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc
/opt/app/oracle/admin/BAS/bdump/bas_smon_27197.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 27197, image: oracle@bas (SMON)

*** SERVICE NAME:() 2011-12-17 19:23:33.179
*** SESSION ID:(5490.1) 2011-12-17 19:23:33.179
SMON: about to recover undo segment 70
ORACLE Instance BAS (pid = 17) - Error 376 encountered while recovering transaction (70, 41) on object 8964.
*** 2011-12-17 19:23:33.188
ksedmp: internal or fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'

通过这些证明smon在利用undo segment 70在回滚sysaux中的内容，但是因为某种原因该回滚段异常，不能进行回滚，是的smon一直尝试回滚，但是始终不成功，最后数据库hang住，需要解决sysaux的问题，首先需要解决这个回滚段问题(删除异常回滚段)

3、删除异常回滚段,online datafile 3

强制kill掉smon进程，重启数据库
[oracle@bas bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 17 19:52:14 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2034520 bytes
Variable Size            1124074664 bytes
Database Buffers         7398752256 bytes
Redo Buffers               65073152 bytes
Database mounted.

--为了数据库不hang掉，先offline datafile 3
SQL> alter database datafile 3 offline;

Database altered.

SQL> select segment_name,status from dba_rollback_segs;
select segment_name,status from dba_rollback_segs
                                *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database open;

Database altered.

SQL> select segment_name,status from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU70$                     NEEDS RECOVERY

SQL> create pfile='/tmp/pfile' from spfile;

File created.

关闭数据库，在pfile中增加
*._corrupted_rollback_segments=(_SYSSMU70$)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2034520 bytes
Variable Size            1124074664 bytes
Database Buffers         7398752256 bytes
Redo Buffers               65073152 bytes
Database mounted.
Database opened.
SQL> drop  rollback segment "_SYSSMU70$";

Rollback segment dropped.

SQL> alter database datafile 3 online;
alter database datafile 3 online
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/opt/app/oracle/oradata/BAS/sysaux01.dbf'


SQL> recover  datafile 3 ;
Media recovery complete.
SQL> alter database datafile 3 online;

Database altered.

三、解决坏块问题
1、alert日志中坏块记录

Sat Dec 17 20:33:31 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27772.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'
Sat Dec 17 20:33:54 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28027.trc:
Sat Dec 17 20:43:32 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_smon_27772.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4571)
ORA-01110: data file 1: '/opt/app/oracle/oradata/BAS/system01.dbf'

2、查询坏块对象

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
 WHERE FILE_ID = &FILE_ID
   AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;  3    4  
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4571
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4571 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
                                           
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
SYS
SMON_SCN_TIME_SCN_IDX
INDEX              SYSTEM

SQL> alter index SMON_SCN_TIME_SCN_IDX rebulid online;
alter index SMON_SCN_TIME_SCN_IDX rebulid online
                                  *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> select table_name from dba_indexes where index_name='SMON_SCN_TIME_SCN_IDX';

TABLE_NAME
------------------------------
SMON_SCN_TIME

3、解决坏块问题

SQL> truncate table SMON_SCN_TIME;
truncate table SMON_SCN_TIME
               *
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster

SQL> truncate  cluster SMON_SCN_TIME;
truncate  cluster SMON_SCN_TIME
                  *
ERROR at line 1:
ORA-00943: cluster does not exist

SQL> SELECT dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM dual;
 
DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS')
--------------------------------------------------------------------------------
 
  CREATE TABLE "SYS"."SMON_SCN_TIME"
   (    "THREAD" NUMBER,
        "TIME_MP" NUMBER,
        "TIME_DP" DATE,
        "SCN_WRP" NUMBER,
        "SCN_BAS" NUMBER,
        "NUM_MAPPINGS" NUMBER,
        "TIM_SCN_MAP" RAW(1200),
        "SCN" NUMBER DEFAULT 0,
        "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD")

SQL> truncate cluster smon_scn_to_time;

Cluster truncated.

SQL> alter system flush buffer_cache;

System altered.

四、解决AUTO_SPACE_ADVISOR_JOB引起bug

Sat Dec 17 21:00:38 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:41 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:44 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:47 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:50 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:54 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:00:57 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:01:00 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []
Sat Dec 17 21:01:03 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_28144.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []

查看MOS发现(430223.1,785899.1)Segment advisor带来的buffer坏块，可以禁用AUTO_SPACE_ADVISOR_JOB并清空buffer来解决，最终解决办法，升级数据库

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB'); 

PL/SQL procedure successfully completed.

SQL> alter system flush buffer_cache;

System altered.

至此这次数据库sysaux数据文件异常恢复完全结束。再次提醒各位，数据库一定要做好备份和归档工作。

发表在 Oracle备份恢复 | 2 条评论

ORA-06553: PLS-801: internal error [56319]

发表于 2011 年 12 月 15 日 由 惜分飞

开发那边有台Linux 32位服务器因为网卡问题，准备把数据库迁移到一台新服务器(Linux 64)上。因为是开发环境(对稳定性要求不是非常高，停机时间几乎没有要求)，还有我比较懒，准备两边安装相同版本，打上相同的补丁，然后直接拷贝datafile/redo/controlfile/spfile的方法完成数据文件迁移
一、源端信息

--系统信息
[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-194.el5PAE #1 SMP Tue Mar 16 22:00:21 EDT 2010 i686 i686 i386 GNU/Linux

--数据库信息
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 13:38:51 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

二、目标端信息

--操作系统信息
[oracle@gongantest ~]$ uname -a
Linux gongantest 2.6.18-194.el5 #1 SMP Fri Apr 2 14:58:14 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

--数据库信息
[oracle@gongantest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 13:43:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

三、迁移数据库
1.修改目标库ORACLE_SID变量等环境变量
2.目标库建立相关目录(参考源库)
3.使用shutdown immediate关闭源库
4.拷贝spfile和oradata中文件

四、启动目标端数据库

[oracle@gongantest gaxt]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 12:46:19 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2084136 bytes
Variable Size             369099480 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

五、收集系统统计信息

SQL> exec dbms_stats.delete_system_stats();
BEGIN dbms_stats.delete_system_stats(); END;

*
ERROR at line 1:
ORA-06553: PLS-801: internal error [56319]

六、重新编译对象

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @?/rdbms/admin/utlirp.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> shutdown immediate;
SQL> startup;

七、出现ORA-07445[_intel_fast_memcpy.J()+250]错误

--alert日志
Thu Dec 15 13:07:22 2011
Errors in file /opt/oracle/admin/gaxt/udump/gaxt_ora_13898.trc:
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()+250] [SIGSEGV] [Address not mapped to object] [0x2BA0E731928F] [] []

--trace文件
*** 2011-12-15 12:58:49.883
SERVER COMPONENT id=UTLRP_BGN: timestamp=2011-12-15 12:58:49
*** 2011-12-15 13:07:22.063
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x2ba0e731928f, PC: [0x2b9fdf587cd8, _intel_fast_memcpy.J()+250]
*** 2011-12-15 13:07:22.063
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()+250] [SIGSEGV] [Address not mapped to object] [0x2BA0E731928F] [] []
Current SQL statement for this session:
 declare 
     rc sys_refcursor; 
   begin 
     :1 := "SYS"."OLAPIMPL_T"."ODCITABLEDESCRIBE"(:2 ,'SYS.AWMD duration query','olapsys.ALL_OLAP2_AW_METADATA_T','ACTIVE_CATALOG ''ALL_CATALOGS'' ''ALL''','
MEASURE AWOWNER FROM sys.awmd!CAT_AWOWNER
                        MEASURE AWNAME FROM sys.awmd!CAT_AWNAME
                        MEASURE COL5 FROM sys.awmd!CAT_CATALOG_ID
                        MEASURE COL1 FROM sys.awmd!CAT_MEASFOLDERNAME
                        MEASURE COL2 FROM sys.awmd!CAT_MEASFOLDERDESC
                        MEASURE COL4 FROM sys.awmd!CAT_PARENTFOLDERNAME
                        DIMENSION AWMDKEY FROM sys.awmd!AWMDKEY_CAT'); 
   end;

查看MOS，发现 Oracle OLAP AWs(Analytical Workspace)在迁移过程中没有正确处理导致，不过该功能该库中没有使用到，直接忽略

八、查询组件是否都正常

SQL> col comp_name for a40
SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

COMP_NAME                                STATUS
---------------------------------------- ----------------------
Spatial                                  VALID
Oracle interMedia                        VALID
OLAP Catalog                             VALID
Oracle Enterprise Manager                VALID
Oracle XML Database                      VALID
Oracle Text                              VALID
Oracle Expression Filter                 VALID
Oracle Rules Manager                     VALID
Oracle Workspace Manager                 VALID
Oracle Data Mining                       VALID
Oracle Database Catalog Views            VALID

COMP_NAME                                STATUS
---------------------------------------- ----------------------
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  VALID
Oracle OLAP API                          VALID

九、收集系统信息

SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'START');

PL/SQL procedure successfully completed.

--一段时间后

SQL> exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');

PL/SQL procedure successfully completed.
发表在 Oracle备份恢复 | 标签为 | 评论关闭

模拟跨resetlogs恢复

发表于 2011 年 12 月 12 日 由 惜分飞

一、模拟跨越resetlog恢复环境

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:09:29 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN>  list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       CURRENT 995548     2011-10-31 16:05:14

RMAN> backup database format '/tmp/ora11g_%U.rman';

Starting backup at 2011-12-12 13:10:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=161 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:10:50
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:17
piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:13:20
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:22
piece handle=/tmp/ora11g_02mu1avd_1_1.rman tag=TAG20111212T131049 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2011-12-12 13:13:22

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes

RMAN> recover database;

Starting recover at 2011-12-12 13:14:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2011-12-12 13:14:58

RMAN>  alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 13:15:14
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:16:02 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> recover database until cancel; 
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:17:47 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:18:34 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:18:53 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=4162194039)

RMAN> backup database format '/tmp/ora11g_new_%U';

Starting backup at 2011-12-12 13:19:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:19:31
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:56
piece handle=/tmp/ora11g—_new_03mu1bb3_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:20:57
channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:58
piece handle=/tmp/ora11g—_new_04mu1bdo_1_1 tag=TAG20111212T131931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-12-12 13:20:58

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile
--备份成功后还切换日志，为了证明可以利用这些日志恢复

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> exit

Recovery Manager complete.
[oracle@node1 ~]$ cd /opt/oracle/oradata/ora11g/
[oracle@node1 ora11g]$ ll
总计 2568524
-rw-r----- 1 oracle oinstall   9846784 12-12 13:27 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 12-12 13:22 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo01.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:22 redo02.log
-rw-r----- 1 oracle oinstall  52429312 12-12 13:26 redo03.log
-rw-r----- 1 oracle oinstall 828383232 12-12 13:22 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 12-12 13:22 system01.dbf
-rw-r----- 1 oracle oinstall 235937792 12-11 18:05 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 12-12 13:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 169091072 12-12 13:22 users01.dbf

[oracle@node1 ora11g]$ rm redo0*
[oracle@node1 ora11g]$ rm *.dbf
[oracle@node1 ora11g]$ ll
总计 9636
-rw-r----- 1 oracle oinstall 9846784 12-12 13:31 control01.ctl
--删除除控制文件外的所有文件

--今天产生的归档日志，从这个里面也可以看到resetlogs操作的界限
[oracle@node1 ora11g]$ ll /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/
总计 152120
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_104_7gc3cogp_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_105_7gc3co97_.arc
-rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_106_7gc3cv1w_.arc
-rw-r----- 1 oracle oinstall 11425792 12-12 13:17 o1_mf_1_107_7gc3mbpr_.arc
-rw-r----- 1 oracle oinstall   984576 12-12 13:18 o1_mf_1_1_7gc3ojqw_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:18 o1_mf_1_2_7gc3okx8_.arc
-rw-r----- 1 oracle oinstall     4608 12-12 13:18 o1_mf_1_3_7gc3onnq_.arc
-rw-r----- 1 oracle oinstall     1536 12-12 13:22 o1_mf_1_4_7gc3wnvf_.arc
-rw-r----- 1 oracle oinstall     1024 12-12 13:22 o1_mf_1_5_7gc3wt48_.arc

二、跨越resetlogs恢复

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:22:50 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size               1493174472 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4947968 bytes

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORA11G   4162194039       PARENT  1          2011-09-17 09:46:04
2       2       ORA11G   4162194039       PARENT  995548     2011-10-31 16:05:14
3       3       ORA11G   4162194039       CURRENT 12881971   2011-12-12 13:17:30

--指定incarnation恢复
RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> restore database;

Starting restore at 2011-12-12 13:33:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ora11g_01mu1aqq_1_1.rman
channel ORA_DISK_1: piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 2011-12-12 13:34:51
--证明恢复会使用incarnation 2对应的备份集

RMAN> recover database;

Starting recover at 2011-12-12 13:35:13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 106 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc
archived log for thread 1 with sequence 107 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc thread=1 sequence=106
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc thread=1 sequence=107
media recovery complete, elapsed time: 00:00:03
Finished recover at 2011-12-12 13:35:18
--incarnation 2数据还原恢复完成

--指定恢复incarnation 3归档日志
RMAN>  reset database to incarnation 3;

database reset to incarnation 3

RMAN> recover database;

Starting recover at 2011-12-12 13:49:36
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc thread=1 sequence=1
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc thread=1 sequence=2
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc thread=1 sequence=3
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc thread=1 sequence=4
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/12/2011 13:49:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 12882851
--缺少归档日志，恢复完seq=5的归档日志，属于正常情况

--因为没有redo，进行的是不完全恢复，使用resetlogs开打数据库
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2011 14:06:04
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Process ID: 26406
Session ID: 96 Serial number: 7
--发现意外出现ORA-00600[2898]错误，打开数据库终止

三、查看alert日志

Mon Dec 12 14:05:59 2011
SMON: enabling cache recovery
[26406] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1208679594 end:1208679684 diff:90 (0 seconds)
Dictionary check beginning
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc  (incident=4953):
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_4953/ora11g_ora_26406_i4953.trc
Mon Dec 12 14:06:02 2011
Dumping diagnostic data in directory=[cdmp_20111212140602], requested by (instance=1, osid=26406), summary=[incident=4953].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc:
ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 26406): terminating the instance due to error 600
Mon Dec 12 14:06:03 2011
Instance terminated by USER, pid = 26406
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (26406) as a result of ORA-1092
Mon Dec 12 14:06:04 2011
ORA-1092 : opitsk aborting process

发现ORA-00600[2898]错误，导致数据库abort，因为这个错误暂时未找到权威说明。初步怀疑是因为在resetlogs时候，遇到File #5 is offline, but is part of an online tablespace导致

四、重新开启数据库

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:08:28 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         5 OFFLINE      12881970

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5             0

SQL> select FILE#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      12902896
         2      12902896
         3      12902896
         4      12902896
         5      12881970

意外的发现数据库竟然open成功，从这里可以看到datafile 5处于offline状态，而且其数据文件头部scn比其他文件小，怀疑没有恢复到一致状态

五、查看重启数据库后alert日志

Archived Log entry 109 added for thread 1 sequence 1 ID 0xf84e7829 dest 1:
File #5 is offline, but is part of an online tablespace.
data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
Mon Dec 12 14:09:01 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc:
ORA-01186: ?? 201 ??????
ORA-01157: ????/?????? 201 - ??? DBWR ????
ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf'
File 201 not verified due to error ORA-01157
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /opt/oracle/oradata/ora11g/temp01.dbf
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28225.trc  (incident=6153):
ORA-25319: Queue table repartitioning aborted
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6153/ora11g_ora_28225_i6153.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Mon Dec 12 14:09:03 2011
QMNC started with pid=31, OS id=28288 
LOGSTDBY: Validating controlfile with logical metadata
Mon Dec 12 14:09:04 2011
Dumping diagnostic data in directory=[cdmp_20111212140904], requested by (instance=1, osid=28225), summary=[incident=6153].
LOGSTDBY: Validation complete
Completed: alter database open
Mon Dec 12 14:09:04 2011
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6121):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6121/ora11g_mmon_28190_i6121.trc
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6122):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6122/ora11g_mmon_28190_i6122.trc
Dumping diagnostic data in directory=[cdmp_20111212140906], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6121].
error 25319 happened during Queue table repartitioning
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc  (incident=6123):
ORA-25319: 队列表重新分区已中止
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6123/ora11g_mmon_28190_i6123.trc
Dumping diagnostic data in directory=[cdmp_20111212140907], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6122].
error 25319 happened during Queue table repartitioning
Dumping diagnostic data in directory=[cdmp_20111212140908], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6123].
Mon Dec 12 14:09:08 2011
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Dec 12 14:09:08 2011
Starting background process CJQ0
Mon Dec 12 14:09:08 2011
CJQ0 started with pid=32, OS id=28308 
Mon Dec 12 14:09:39 2011
Sweep [inc][6153]: completed
Sweep [inc][6123]: completed
Sweep [inc][6122]: completed

因为第一个resetlogs没有成功，所以temp文件没有创建，这里先创建了temp文件.还有个ORA-25319的错误，和datafile 5 offline有关系

六、trace文件中内容

*** 2011-12-12 14:17:46.627
Started Serial Media Recovery
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Media Recovery apply resetlogs offline range for datafile 5, incarnation : 1
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000.00c493a4 12/12/2011 14:05:53
Resetlogs 1 scn and time: 0x0000.00c49033 12/12/2011 13:17:30
Recovery target incarnation = 4, activation ID = 0
Influx buffer limit = 37449 min(50% x 74898, 100000)
Start recovery at thread 1 ckpt scn 12881971 logseq 1 block 2
Initial buffer sizes: read 1024K, overflow 832K, change 805K

*** 2011-12-12 14:17:46.725
Media Recovery add redo thread 1

*** 2011-12-12 14:18:47.348
Media Recovery Log 2011_12_12/o1_mf_1_1_7gc3ojqw_.arc

*** 2011-12-12 14:19:00.198
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea

*** 2011-12-12 14:19:15.911
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2011-12-12 14:19:22.638
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2011-12-12 14:19:31.007
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea

*** 2011-12-12 14:19:37.116
Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Thread 1 initialized for new incarnation 1 at scn 12882852
Media Recovery current incarnation depth : 0
File 5 (stop scn 12882852) completed recovery at checkpoint scn 12882852

从这里可以知道，datafile 5，没有应用o1_mf_1_5_7gc3wt48_.arc日志恢复，那么恢复datafile 5 需要o1_mf_1_5_7gc3wt48_.arc日志文件

七、恢复数据文件5，打开数据库

SQL> recover datafile 5 ;
ORA-00279: change 12881971 generated at 12/12/2011 13:14:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.
arc
ORA-00280: change 12881971 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log applied.
Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL>
发表在 rman备份/恢复 | 标签为 | 一条评论