恢复篇：数据库丢失了redo log与controlfile时，又被shutdow abort了如何恢复

一、环境

数据库:11G 服务器系统：CENTOS 5.4 工具：securCRT SQLPLUS

二、故障

数据库在操作的过程中突然被shutdown abort了，且丢失了联机重做日志与控制文件，控制文件有做trace备份

三、目的

不完全恢复、启动数据库

四、演示过程

1、数据库正常运行中，做trace备份

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             356518272 bytes
Database Buffers           58720256 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace as '/tmp/sale.sql';
Database altered.
SQL>

2、随便插入一条记录

SQL> select * from usr1.t;
        ID NAME
---------- ------------------------------
         1 boobooke
         2 boobooke
         3 boobooke
         5 boobooke
         4 boobooke
         7 boobooke
         6 boobooke
         0 boobooke
8 rows selected.
SQL> insert into usr1.t values(8,'markw');
1 row created.
SQL> commit;
Commit complete.

3、此时记录只存在于联机重做日志中

SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ------------------------------------------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED
[oracle@joyce sales]$ strings redo02.log | grep markw
markw
[oracle@joyce sales]$

4、此时我们把数据库模拟断电

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

5、我们将联机重做日志与控制文件全部删掉（做之前请先全备）

[oracle@joyce sales]$ ll
total 1753516
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:40 app1_01.dbf
-rw-r----- 1 oracle oinstall  10493952 Nov 19 10:32 app2.dbf
-rw-r----- 1 oracle oinstall  10076160 Nov 19 10:42 control01.ctl
-rw-r----- 1 oracle oinstall  10076160 Nov 19 10:42 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:32 example01.dbf
drwx------ 2 oracle oinstall      4096 Oct 31 20:10 lost+found
-rw-r----- 1 oracle oinstall  52429312 Nov 19 10:32 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Nov 19 10:42 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Nov 19 10:32 redo03.log
-rw-r----- 1 oracle oinstall 566239232 Nov 19 10:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 723525632 Nov 19 10:37 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Nov  4 06:25 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 Nov 19 10:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov 19 10:32 users01.dbf
[oracle@joyce sales]$ rm redo0*
[oracle@joyce sales]$ rm control0*
[oracle@joyce sales]$ ll
total 1580024
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:40 app1_01.dbf
-rw-r----- 1 oracle oinstall  10493952 Nov 19 10:32 app2.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:32 example01.dbf
drwx------ 2 oracle oinstall      4096 Oct 31 20:10 lost+found
-rw-r----- 1 oracle oinstall 566239232 Nov 19 10:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 723525632 Nov 19 10:37 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Nov  4 06:25 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 Nov 19 10:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov 19 10:32 users01.dbf
[oracle@joyce sales]$

6、修改备份的trace来重建控制文件，修改里面的参数

[oracle@joyce tmp]$ strings sale.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SALES" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/sales/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/sales/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/sales/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oradata/sales/system01.dbf',
  '/u01/oradata/sales/sysaux01.dbf',
  '/u01/oradata/sales/undotbs01.dbf',
  '/u01/oradata/sales/users01.dbf',
  '/u01/oradata/sales/example01.dbf',
  '/u01/oradata/sales/app1_01.dbf',
  '/u01/oradata/sales/app2.dbf'
CHARACTER SET AL32UTF8
[oracle@joyce tmp]$

7、重建控制文件

SQL> conn /as sysdba
Connected to an idle instance.
SQL> @/tmp/sale.sql
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             356518272 bytes
Database Buffers           58720256 bytes
Redo Buffers                6094848 bytes
Control file created.
SQL>
[oracle@joyce sales]$ ll
total 1599736
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:40 app1_01.dbf
-rw-r----- 1 oracle oinstall  10493952 Nov 19 10:32 app2.dbf
-rw-r----- 1 oracle oinstall  10076160 Nov 19 10:48 control01.ctl
-rw-r----- 1 oracle oinstall  10076160 Nov 19 10:48 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Nov 19 10:32 example01.dbf
drwx------ 2 oracle oinstall      4096 Oct 31 20:10 lost+found
-rw-r----- 1 oracle oinstall 566239232 Nov 19 10:42 sysaux01.dbf
-rw-r----- 1 oracle oinstall 723525632 Nov 19 10:37 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Nov  4 06:25 temp01.dbf
-rw-r----- 1 oracle oinstall  99622912 Nov 19 10:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov 19 10:32 users01.dbf
[oracle@joyce sales]$

8、查看数据库的状态

SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
MOUNTED
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1            1165636
         2            1165636
         3            1165636
         4            1165636
         5            1165636
         6            1165636
         7            1165636
7 rows selected.
SQL>

9、关闭数据库

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>

10、在SPFILE参数中添加隐含参数

[oracle@joyce dbs]$ vi initsales.ora
[oracle@joyce dbs]$ strings initsales.ora | grep _allow_resetlogs_corruption
_allow_resetlogs_corruption=true

11、使用pfile来启动数据库至MOUNT状态

SQL> startup pfile='/u01/oracle/dbs/initsales.ora' mount
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             356518272 bytes
Database Buffers           58720256 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL>

12、恢复数据库

[oracle@joyce sales]$ ll -htr /u01/arch/ 
total 48M
-rw-r----- 1 oracle oinstall 48M Nov 18 22:34 sales_1_1_831852240.arc
[oracle@joyce sales]$
所需归档日志不存在
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1165636 generated at 11/19/2013 10:32:00 needed for thread 1
ORA-00289: suggestion : /u01/arch/sales_2_1_831852240.arc
ORA-00280: change 1165636 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/arch/sales_2_1_831852240.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/arch/sales_2_1_831852240.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/sales/system01.dbf'
SQL>

13、使用resetlogs来打开数据库

SQL> alter database open resetlogs;

此方面有时可成，有时会提示内部错误

转载于:https://blog.51cto.com/opendba/1328188