第六章: 控制文件（2）
6）控制文件恢复
单个文件丢失： [oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl [oracle@oracle dbs]$ sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:14:54 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:14:54 SQL> startup
ORACLE instance started.Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes Variable Size 88082628 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes ORA-00205: error in identifying control file, check alert log for more info通过告警日志获得信息：
ALTER DATABASE MOUNT
Mon Aug 1 06:14:57 2011 ORA-00202: control file: '/disk2/lx02/oradata/control03.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
06:14:57 SQL> shutdown
ORA-01507: database not mounted ORACLE instance shut down. 06:15:14 SQL> ! [oracle@oracle dbs]$ cp /disk1/lx02/oradata/control02.ctl /disk2/lx02/oradata/control03.ctl [oracle@oracle dbs]$ sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:15:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:15:37 SQL> startup
ORACLE instance started.Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes Variable Size 88082628 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. 06:15:47 SQL> select name from v$controlfile;NAME
------------------------------------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/lx02/control01.ctl /disk1/lx02/oradata/control02.ctl /disk2/lx02/oradata/control03.ctl06:16:00 SQL>
所有的文件丢失：
06:16:00 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 06:17:22 SQL> ! [oracle@oracle dbs]$ rm /u01/app/oracle/oradata/lx02/control01.ctl [oracle@oracle dbs]$ rm /disk1/lx02/oradata/control02.ctl [oracle@oracle dbs]$ rm /disk2/lx02/oradata/control03.ctl [oracle@oracle dbs]$ sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 1 06:17:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
06:17:51 SQL> startup
ORACLE instance started.Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes Variable Size 88082628 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes ORA-00205: error in identifying control file, check alert log for more info告警日志：
ALTER DATABASE MOUNT Mon Aug 1 06:17:54 2011 ORA-00202: control file: '/u01/app/oracle/oradata/lx02/control01.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Mon Aug 1 06:17:54 2011利用trace 文件重建
在nomount 状态 06:19:51 SQL>CREATE CONTROLFILE REUSE DATABASE "LX02" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 1 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/lx02/redo01a.log' SIZE 10M, GROUP 2 '/u01/app/oracle/oradata/lx02/redo02a.log' SIZE 10M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/lx02/system01.dbf', '/u01/app/oracle/oradata/lx02/rtbs01.dbf', '/u01/app/oracle/oradata/lx02/sysaux01.dbf', '/u01/app/oracle/oradata/lx02/user01.dbf', '/u01/app/oracle/oradata/lx02/example01.dbf', '/u01/app/oracle/oradata/lx02/indx01.dbf', '/u01/app/oracle/oradata/lx02/OLTP01.DBF' CHARACTER SET ZHS16GBK 06:21:23 20 ;Control file created.
06:21:27 SQL> alter database open resetlogs;
Database altered.
06:21:39 SQL>
--------------非正常关库，重建控制文件
00:43:07 SQL> insert into scott.test values (10);1 row created.
00:43:22 SQL> insert into scott.test values (11);
1 row created.
00:43:25 SQL> commit;
Commit complete.
00:43:27 SQL> alter system switch logfile;
System altered.
00:43:29 SQL> /
System altered.
00:43:31 SQL> /
System altered.
00:43:36 SQL> shutdown abort ;database 异常关闭
ORACLE instance shut down. 00:43:44 SQL> ! [oracle@work dbs]$ rm /disk1/oradata/test/control02.ctl [oracle@work dbs]$ rm /disk2/oradata/test/control03.ctl [oracle@work dbs]$ rm /u01/app/oracle/oradata/test/control01.ctl [oracle@work dbs]$ !sql sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 27 00:44:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
00:44:11 SQL>
00:44:11 SQL> startup ORACLE instance started.Total System Global Area 176160768 bytes
Fixed Size 1218364 bytes Variable Size 88082628 bytes Database Buffers 83886080 bytes Redo Buffers 2973696 bytes ORA-00205: error in identifying control file, check alert log for more info 00:44:15 SQL> @/home/oracle/control.sql 00:44:25 SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG 00:44:25 2 MAXLOGFILES 16 00:44:25 3 MAXLOGMEMBERS 4 00:44:25 4 MAXDATAFILES 100 00:44:25 5 MAXINSTANCES 1 00:44:25 6 MAXLOGHISTORY 20 00:44:25 7 LOGFILE 00:44:25 8 GROUP 1 '/u01/app/oracle/oradata/test/redo01a.log' SIZE 10M, 00:44:25 9 GROUP 2 '/u01/app/oracle/oradata/test/redo02a.log' SIZE 10M, 00:44:25 10 GROUP 3 '/u01/app/oracle/oradata/test/redo03a.log' SIZE 10M 00:44:25 11 -- STANDBY LOGFILE 00:44:25 12 DATAFILE 00:44:25 13 '/u01/app/oracle/oradata/test/system01.dbf', 00:44:25 14 '/u01/app/oracle/oradata/test/rtbs01.dbf', 00:44:25 15 '/u01/app/oracle/oradata/test/sysaux01.dbf', 00:44:25 16 '/u01/app/oracle/oradata/test/users01.dbf' 00:44:25 17 CHARACTER SET ZHS16GBK 00:44:25 18 ;Control file created.
00:44:26 SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------ 32558800:44:35 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------ 325588 325588 325588 32558800:44:38 SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------ 325588 325588 325588 32558800:44:41 SQL> alter database open;
alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf' 00:44:47 SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch Oldest online log sequence 57 Current log sequence 59 00:45:27 SQL> recover database until cancel; 或者 23:35:59 SQL> recover database until cancel using backup controlfile; ORA-00279: change 325588 generated at 08/27/2011 00:43:36 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_59_759630389.dbf ORA-00280: change 325588 for thread 1 is in sequence #59 00:45:43 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel 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/app/oracle/oradata/test/system01.dbf' ORA-01112: media recovery not started--------通过当前redo 日志进行database recover
00:45:52 SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 58 10485760 1 NO INACTIVE 325585 27-AUG-11 3 1 57 10485760 1 NO INACTIVE 325583 27-AUG-11 2 1 59 10485760 1 NO CURRENT 325588 27-AUG-1100:46:02 SQL> col member for a50
00:46:12 SQL> select group# ,member from v$logfile;GROUP# MEMBER
---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/test/redo02a.log 1 /u01/app/oracle/oradata/test/redo01a.log 3 /u01/app/oracle/oradata/test/redo03a.log00:46:18 SQL> recover database until cancel;
ORA-00279: change 325588 generated at 08/27/2011 00:43:36 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_59_759630389.dbf ORA-00280: change 325588 for thread 1 is in sequence #59 00:46:23 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/test/redo02a.log Log applied. Media recovery complete. 00:46:30 SQL> alter database open resetlogs;Database altered.
00:46:59 SQL> select * from scott.test;
ID
---------- 1 2 3 4 5 6 7 9 10 1110 rows selected.
00:47:05 SQL>