ITPub博客

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

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

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

-- 模拟交易产生
22:44:06 SYS@wailon> create table scott.a4 as select * from scott.a;

Table created.

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

System altered.

22:45:30 SYS@wailon> select group#,sequence#,status from v$log;

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

22:45:41 SYS@wailon> insert into scott.a4 select * from scott.a4;   -- 此事务未提交

23 rows created.

22:46:07 SYS@wailon> alter system switch logfile;

System altered.

22:46:15 SYS@wailon> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         3          3 CURRENT
         2          2 ACTIVE

22:46:18 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

-- 插入a4表的23行未提交,数据库意外断电,并且控制文件丢失
22:47:06 SYS@wailon> shutdown abort;
ORACLE instance shut down.
22:47:14 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

22:47:28 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:47:32 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:47:41 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
22:47:45   2      MAXLOGFILES 16
22:47:45   3      MAXLOGMEMBERS 3
22:47:45   4      MAXDATAFILES 100
22:47:45   5      MAXINSTANCES 8
22:47:45   6      MAXLOGHISTORY 292
22:47:45   7  LOGFILE
22:47:45   8    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
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
22:47:46  19  ;

Control file created.

-- 尝试加载数据库,创建控制文件后已经自动加载
22:47:51 SYS@wailon> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

-- 打开数据库,提示需要恢复
22:47:58 SYS@wailon> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf'

-- 使用USING BACKUP CONTROLFILE恢复,由于有活动事务未提交,需要指定联机日志执行不完全恢复
22:48:16 SYS@wailon> recover database using backup controlfile;
ORA-00279: change 3028602 generated at 09/29/2013 22:25:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_94jh8b2c_.arc
ORA-00280: change 3028602 for thread 1 is in sequence #1


22:48:24 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo01.log
ORA-00279: change 3030164 generated at 09/29/2013 22:45:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_2_94jh9qxy_.arc
ORA-00280: change 3030164 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo01.log' no longer needed for this recovery


22:48:58 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo02.log
ORA-00279: change 3030195 generated at 09/29/2013 22:46:15 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_3_%u_.arc
ORA-00280: change 3030195 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo02.log' no longer needed for this recovery


22:49:08 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo03.log
Log applied.
Media recovery complete.

-- 恢复完成后,使用OPEN RESETLOGS打开数据库
22:49:12 SYS@wailon> alter database open resetlogs;

Database altered.

-- 未提交的数据已经丢失
22:52:59 SYS@wailon> select count(*) from scott.a4;

  COUNT(*)
----------
        23

-- 添加临时表空间
22:41:02 SYS@wailon> select * from v$tempfile;

no rows selected

22:42:00 SYS@wailon> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP01
WAILON

6 rows selected.

22:42:33 SYS@wailon> select name from v$datafile;

NAME
---------------------------------------------
/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/datafil
e/o1_mf_wailon_94g6p2k8_.dbf

22:43:46 SYS@wailon> alter tablespace temp01 add tempfile '/u01/app/oracle/oradata/wailon/temp01.dbf' size 10m reuse;

Tablespace altered.

22:43:57 SYS@wailon> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIM        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------
BLOCK_SIZE NAME
---------- ---------------------------------------------
         1          3030098 29-SEP-13             7          1 ONLINE  READ WRITE   10485760       1280     10485760
      8192 /u01/app/oracle/oradata/wailon/temp01.dbf

 

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

上一篇: 博客索引贴
请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290570