Oracle DBA课程系列笔记（6_2）

原创

客居天涯 2012-02-28 14:41:15 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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

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

Connected to an idle instance.

06:17:51 SQL> startup
ORACLE instance started.

告警日志：
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

Connected to an idle instance.

00:44:11 SQL>
00:44:11 SQL> startup
ORACLE instance started.

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>