模拟控制文件全部丢失与归档日志文件丢失的不完全恢复

--控制文件全部丢失，归档日志文件丢失

--模拟实验环境

SYS@PROD2> create table scott.c1 as select * from scott.dept;  --创建c1表

Table created.

SYS@PROD2> alter system switch logfile;

System altered.

SYS@PROD2> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     34
Next log sequence to archive   36
Current log sequence	       36  --c1表存放在36号归档日志中
SYS@PROD2> create table scott.c2 as select * from scott.dept;  --创建c2表

Table created.

SYS@PROD2> alter system switch logfile;

System altered.

SYS@PROD2> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     35
Next log sequence to archive   37
Current log sequence	       37  --c2表存放在37号归档日志中
SYS@PROD2> create table scott.c3 as select * from scott.dept;  --创建c3表

Table created.

SYS@PROD2> alter system switch logfile;

System altered.

SYS@PROD2> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36
Next log sequence to archive   38
Current log sequence	       38  --c3表存放在37号归档日志中

[oracle@ocm1 ~]$ rm /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_11/o1_mf_1_36_d4to1rrn_.arc  --删除36号归档

SYS@PROD2> select name from v$controlfile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

[oracle@ocm1 ~]$ rm /u01/app/oracle/oradata/PROD2/control01.ctl   --删除所有控制文件
[oracle@ocm1 ~]$ rm /u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
故障模拟完成。

解决过程：

[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 14 20:59:20 2016

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database: 
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD2/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

因为控制文件全部丢失，所以无法进入RMAN。

SYS@PROD2> shutdown abort
ORACLE instance shut down.
SYS@PROD2> exit    
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 14 21:05:42 2016

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

connected to target database: PROD2 (not mounted)

RMAN> restore controlfile from '/home/oracle/bak/PROD2/control01.ctl';  --还原控制文件

Starting restore at 14-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
Finished restore at 14-DEC-16

RMAN> alter database mount;  --启动mount阶段

database mounted
released channel: ORA_DISK_1

SYS@PROD2> alter database open;  
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD2/system01.dbf'
ORA-01207: file is more recent than control file - old control file

仍然无法打开，尝试重建控制文件

SYS@PROD2> alter database backup controlfile to trace as '/tmp/c.sql' reuse;

Database altered.

SYS@PROD2> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

[oracle@ocm1 ~]$ cat /tmp/c1.sql 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD2" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PROD2/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PROD2/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PROD2/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PROD2/system01.dbf',
  '/u01/app/oracle/oradata/PROD2/sysaux01.dbf',
  '/u01/app/oracle/oradata/PROD2/undotbs01.dbf',
  '/u01/app/oracle/oradata/PROD2/users01.dbf',
  '/u01/app/oracle/oradata/PROD2/example01.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_14/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD2/temp01.dbf' REUSE;
-- End of tempfile additions.

执行脚本

SYS@PROD2> start /tmp/c1.sql
ORACLE instance started.

Total System Global Area  958341120 bytes
Fixed Size		    1348972 bytes
Variable Size		  322964116 bytes
Database Buffers	  629145600 bytes
Redo Buffers		    4882432 bytes

Control file created.

ORA-00279: change 4299918079 generated at 12/14/2016 20:49:21 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_14/o1_mf_1_51_%u_.arc
ORA-00280: change 4299918079 for thread 1 is in sequence #51


ORA-00308: cannot open archived log '--'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log 'ALTER'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '--'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '--'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '--'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log 'ALTER'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '--'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '--'   --提示无法找到归档日志文件
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory 
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel   --输入cancel，基于取消的恢复。
Media recovery cancelled.

所以脚本仅执行到RECOVER DATABASE USING BACKUP CONTROLFILE
手动执行脚本内剩余内容。

SYS@PROD2> alter database open resetlogs; 
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD2/system01.dbf'

日志文件丢失导致无法恢复。

SYS@PROD2> create pfile='/tmp/a.ora' from spfile;

File created.

SYS@PROD2> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
在a.ora中添加以下行，允许数据库在损坏情况在打开
*._allow_resetlogs_corruption=true

SYS@PROD2> startup mount pfile='/tmp/a.ora';
ORACLE instance started.

Total System Global Area  958341120 bytes
Fixed Size		    1348972 bytes
Variable Size		  322964116 bytes
Database Buffers	  629145600 bytes
Redo Buffers		    4882432 bytes
Database mounted.
SYS@PROD2> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SYS@PROD2> recover database using backup controlfile;
ORA-00279: change 4299834935 generated at 12/11/2016 21:26:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_11/o1_mf_1_36_%u_.arc
ORA-00280: change 4299834935 for thread 1 is in sequence #36


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel  
Media recovery cancelled.

SYS@PROD2> alter database open resetlogs;
ERROR:
ORA-03114: not connected to ORACLE


alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [1], [4867651], [1], [4867657], [12583040], [],
[], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [1], [4867650], [1], [4867657], [12583040], [],
[], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [1], [4867647], [1], [4867657], [12583040], [],
[], [], [], [], []
Process ID: 6406
Session ID: 1 Serial number: 5

多次重启数据库解决，http://blog.youkuaiyun.com/u013169075/article/details/53648616

SYS@PROD2> alter database open resetlogs;

Database altered.

SYS@PROD2> select count(*) from scott.c1;

  COUNT(*)
----------
	 4

SYS@PROD2> select count(*) from scott.c2;
select count(*) from scott.c2
                           *
ERROR at line 1:
ORA-08103: object no longer exists


SYS@PROD2> select count(*) from scott.c3;
select count(*) from scott.c3
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

由于删除了36号归档，所以在只有在35号归档的c1表存在，其他数据全部丢失。
至此不完全恢复完成。