第六章: 控制文件(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>