ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用RESETLOGS重建控制文件恢复数据库(二)

使用RESETLOGS重建控制文件恢复数据库(二)

原创 Linux操作系统 作者:wailon 时间:2013-11-22 23:43:22 0 删除 编辑

控制文件丢失,但数据文件及联机日志文件还在,并且数据库是正常关闭。

-- 模拟交易产生
22:20:46 SYS@wailon> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         3          0 UNUSED
         2          0 UNUSED

22:21:23 SYS@wailon> create table scott.a3 as select * from scott.a;

Table created.

22:21:54 SYS@wailon> alter system switch logfile;

System altered.

22:22:01 SYS@wailon> insert into scott.a3 select * from scott.a3;

23 rows created.

22:22:51 SYS@wailon> commit;

Commit complete.

22:22:55 SYS@wailon> alter system switch logfile;

System altered.

-- 备份CONTROLFILE TO TRACE可以从里面查到控制文件的创建语句
22:23:15 SYS@wailon> alter database backup controlfile to trace;

Database altered.

22:23:31 SYS@wailon> select * from v$diag_info;

  1 Default Trace File
/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_20993.trc
 
22:23:38 SYS@wailon> host view /u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_20993.trc

-- 省略部分输出

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/system01.dbf',
  '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/users01.dbf',
  '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- No tempfile entries found to add.
--

22:24:23 SYS@wailon> select * from v$controlfile;

STATUS  NAME                                          IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- --------------------------------------------- --- ---------- --------------
        /u01/app/oracle/oradata/wailon/control01.ctl  NO       16384            614
        /u01/app/oracle/oradata/wailon/control02.ctl  NO       16384            614

-- 正常关闭数据库,删除所有控制文件
22:24:35 SYS@wailon> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:24:49 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

22:25:08 SYS@wailon> host ls /u01/app/oracle/oradata/wailon/control*.ctl
ls: cannot access /u01/app/oracle/oradata/wailon/control*.ctl: No such file or directory

-- 启动数据库到NOMOUNT状态
22:25:16 SYS@wailon> startup nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             360713520 bytes
Database Buffers           46137344 bytes
Redo Buffers                8466432 bytes

-- 使用RESETLOGS创建控制文件
22:25:37 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
22:25:41   2      MAXLOGFILES 16
22:25:41   3      MAXLOGMEMBERS 3
22:25:41   4      MAXDATAFILES 100
22:25:41   5      MAXINSTANCES 8
22:25:41   6      MAXLOGHISTORY 292
22:25:41   7  LOGFILE
22:25:41   8    GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log'  SIZE 50M BLOCKSIZE 512,
22:25:41   9    GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log'  SIZE 50M BLOCKSIZE 512
22:25:41  11  -- STANDBY LOGFILE
22:25:41  12  DATAFILE
22:25:41  13    '/u01/app/oracle/oradata/system01.dbf',
22:25:41  14    '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/users01.dbf',
  '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
22:25:41  18  CHARACTER SET ZHS16GBK
22:25:41  19  ;

Control file created.

-- 控制文件创建成功后,直接使用RESETLOGS打开数据库;由于正常关闭数据库,所以不需要recover
22:25:45 SYS@wailon> alter database open resetlogs;

Database altered.

22:26:06 SYS@wailon> select status from v$instance;

STATUS
------------
OPEN

-- 日志序号从1开始编号
22:29:59 SYS@wailon> select group#,sequence#,status from v$Log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         3          0 UNUSED
         2          0 UNUSED

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-777383/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    291503