第六章: 控制文件（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.ctl
06: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 sysdba
SQL*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#
------------------
325588
00:44:35 SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
325588
325588
325588
325588
00:44:38 SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
325588
325588
325588
325588
00: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-11
00: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.log
00: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
11
10 rows selected.
00:47:05 SQL>
