手工不完全恢复（非归档模式下，日志被覆盖）

实验环境：red hat 5.8

oracle环境：11.2.0.3

历史日志没有被覆盖，只坏一个文件。

 

主要操作步骤：切四次日志组，把日志覆盖。

 

1、确认数据库是否为归档模式，如果是，则改为非归档模式。

sys@TEST0910> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     53

Next log sequence to archive   55

Current log sequence           55

sys@TEST0910> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TEST0910> startup mount;

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

sys@TEST0910> alter database noarchivelog;

Database altered.

sys@TEST0910> alter database open;

Database altered.

sys@TEST0910> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     53

Current log sequence           55

sys@TEST0910> select log_mode from v$database;

LOG_MODE

------------

NOARCHIVELOG

2、重新备份，非归档模式下，只能做冷备份。

冷备份脚本参考：http://blog.csdn.net/rlhua/article/details/11850445

sys@TEST0910> @/u01/app/oracle/bak/cold_bak.sql

host cp /u01/app/oracle/oradata/test0910/disk1/users01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/example01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/lxtb01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/sysaux01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/system01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/testtb.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/undotbs01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/fast_recovery_area/test0910/control02.ctl /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/control01.ctl /u01/app/oracle/bak/cold_bak

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

Database opened.

 

 

3、插入三次数据，分别是提交切换日志，提交不切日志，不提交不切日志。

sys@TEST0910> select count(*) from scott.test6;

  COUNT(*)

----------

        14

sys@TEST0910> insert into scott.test6 select * from scott.emp;

14 rows created.

sys@TEST0910> commit;

Commit complete.

sys@TEST0910> alter system switch logfile;

System altered.

sys@TEST0910> insert into scott.test6 select * from scott.emp;

14 rows created.

sys@TEST0910> commit;

Commit complete.

sys@TEST0910> select count(*) from scott.test6;

  COUNT(*)

----------

        42

sys@TEST0910> insert into scott.test6 select * from scott.emp;

14 rows created.

sys@TEST0910>  select count(*) from scott.test6;

  COUNT(*)

----------

        56

 

4、一共有3组日志组，切4次，覆盖日志

sys@TEST0910> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1         58   52428800        512          1 NO  CURRENT                1857565 21-SEP-13   2.8147E+14

         2          1         56   52428800        512          1 NO  INACTIVE               1837164 21-SEP-13      1857173 21-SEP-13

         3          1         57   52428800        512          1 NO  ACTIVE                 1857173 21-SEP-13      1857565 21-SEP-13

sys@TEST0910>  alter system switch logfile;                                                                         

System altered.

sys@TEST0910>  alter system switch logfile;

System altered.

sys@TEST0910>  alter system switch logfile;

System altered.

sys@TEST0910>  alter system switch logfile;

System altered.

 

5、模拟断电。

sys@TEST0910> shutdown abort;

ORACLE instance shut down.

 

6、只坏一个文件

[oracle@rtest ~]$ ls /u01/app/oracle/oradata/test0910/disk1/users01.dbf

ls: /u01/app/oracle/oradata/test0910/disk1/users01.dbf: No such file or directory

 

7、转储数据文件

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/users01.dbf /u01/app/oracle/oradata/test0910/disk1/users01.dbf

 

8、恢复数据文件

sys@TEST0910> recover datafile 4;

 

报错。此时需要将所有的数据文件拷贝到原来的位置，保证scn一致。

即v$database,v$datafile,v$datafile_header这三个的scn要一致，才能开库。

 

9、拷贝数据文件。

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/lxtb01.dbf /u01/app/oracle/oradata/test0910/lxtb01.dbf

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf

[oracle@rtest ~]$  cp /u01/app/oracle/bak/cold_bak/testtb.dbf  /u01/app/oracle/oradata/test0910/testtb.dbf

[oracle@rtest ~]$ cp /u01/app/oracle/bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf

 

10、恢复数据库，用resetlogs打开数据库

idle> recover database until cancel;

ORA-00279: change 1836458 generated at 09/21/2013 12:24:29 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_21/o1_mf_1_55_%u_.arc

ORA-00280: change 1836458 for thread 1 is in sequence #55

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

idle> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

idle> alter database open resetlogs;

Database altered.

 

 

如果在第9步将控制文件也拷贝到原来的位置，则也会报错。

起库时报错为

idle> startup

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/u01/app/oracle/oradata/test0910/disk1/users01.dbf'

ORA-01207: file is more recent than control file - old control file --重建控制文件

 

这时需要重建控制文件：

idle> alter database backup controlfile to trace;

Database altered.

idle> oradebug setmypid

Statement processed.

idle> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/test0910/test0910/trace/test0910_ora_30109.trc

idle> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

idle> startup nomount;

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

idle> CREATE CONTROLFILE REUSE DATABASE "TEST0910" NORESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

  6      MAXLOGHISTORY 292

LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/test0910/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/test0910/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/test0910/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

12  DATAFILE

  '/u01/app/oracle/oradata/test0910/system01.dbf',

  '/u01/app/oracle/oradata/test0910/sysaux01.dbf',

15    '/u01/app/oracle/oradata/test0910/undotbs01.dbf',

  '/u01/app/oracle/oradata/test0910/disk1/users01.dbf',

  '/u01/app/oracle/oradata/test0910/example01.dbf',

  '/u01/app/oracle/oradata/test0910/testtb.dbf',

  '/u01/app/oracle/oradata/test0910/lxtb01.dbf'

20  CHARACTER SET AL32UTF8

;

RECOVER DATABASE

ALTER DATABASE OPEN;

Control file created.

idle> 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/test0910/system01.dbf'

idle> recover database until cancel;

ORA-00279: change 1836458 generated at 09/21/2013 12:24:29 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_21/o1_mf_1_55_%u_.arc

ORA-00280: change 1836458 for thread 1 is in sequence #55

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

idle> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

idle> alter database open resetlogs;

Database altered.

 

最后添加临时表空间

sys@TEST0910> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/test0910/temp01.dbf' REUSE;

Tablespace altered.

 

sys@TEST0910> select count(*) from scott.test6;

  COUNT(*)

----------

        14