一个数据库完全一致恢复的测试实例

一个oracle数据库的恢复测试

1,关闭数据库完全备份

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

$ cp *.dbf backup1

$cp control01.ctl backup1

2,创建表并插入数据

SQL> startup

ORACLE instance started.

 

Total System Global Area 1511484856 bytes

Fixed Size                   736696 bytes

Variable Size             553648128 bytes

Database Buffers      956301312 bytes

Redo Buffers              798720 bytes

Database mounted.

Database opened.

 

SQL> create table mytable (f1 varchar2(2));

 

Table created.

 

SQL> alter system switch logfile;

 

System altered.

 

查看当前sequence和归档的sequence

SQL>select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#

 

 SEQUENCE#       NAME

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

   1              /oradata/backup/archivelogbackup/1_1.dbf

 

 

SQL> insert into mytable

  2  select '01' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

 

SQL> insert into mytable

  2  select '02' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

select group#,sequence#,status,first_change# from v$log;

1     1     2     INACTIVE     6612478016

2     2     3     ACTIVE  6612478685

3     3     4     CURRENT     6612480148

 

select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#

1     5     1     /oradata/backup/archivelogbackup/1_1.dbf

2     6     2     /oradata/backup/archivelogbackup/1_2.dbf

3     7     3     /oradata/backup/archivelogbackup/1_3.dbf

 

SQL> insert into mytable

  2  select '03' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> insert into mytable

  2  select '04' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL>

SQL>

SQL> insert into mytable

  2  select '05' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> insert into mytable   

  2  select '06' from dual;

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

 

select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER
from v$log l,v$logfile lf
where l.GROUP# = lf.GROUP#;

1    1    8    CURRENT  6612481053    /oradata/REDO1_01.LOG

2    1    8    CURRENT  6612481053    /oradata/REDO1_02.LOG

3    2    6    INACTIVE 6612480890    /oradata/REDO2_01.LOG

4    2    6    INACTIVE 6612480890    /oradata/REDO2_02.LOG

5    3    7    ACTIVE   6612480971    /oradata/REDO3_01.LOG

6    3    7    ACTIVE   6612480971    /oradata/REDO3_02.LOG

select l.RECID,l.SEQUENCE#,l.NAME
from v$database d,v$archived_log l
where d.RESETLOGS_CHANGE# = l.RESETLOGS_CHANGE#
1    5    1    /oradata/backup/archivelogbackup/1_1.dbf

2    6    2    /oradata/backup/archivelogbackup/1_2.dbf

3    7    3    /oradata/backup/archivelogbackup/1_3.dbf

4    8    4    /oradata/backup/archivelogbackup/1_4.dbf

5    9    5    /oradata/backup/archivelogbackup/1_5.dbf

6    10   6    /oradata/backup/archivelogbackup/1_6.dbf

7    11   7    /oradata/backup/archivelogbackup/1_7.dbf

 

3,关闭数据库,并用备份的数据文件恢复

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

$ rm *.dbf

$ rm *.ctl

$ cd backup1

$ cp *.dbf /oradata

$ cp *.ctl /oradata

$ cd ..

$ pwd

/oradata

$ ls       

CTL01.dbf        REDO1_0101.LOG   REDO3_02.LOG     backup1          directory.out    system01.dbf

FBI.dbf          REDO1_0102.LOG   RPTD01.dbf       backup_datafile  drsys01.dbf      temp01.dbf

HAPPYTREE.dbf    REDO1_02.LOG     RPTI01.dbf       bea              example01.dbf    tmp

HTEC.dbf         REDO2_01.LOG     TEMP1_01.dbf     bea80            file             tools01.dbf

OD01.dbf         REDO2_02.LOG     ant              control01.ctl    indx01.dbf       undotbs03.dbf

REDO1_01.LOG     REDO3_01.LOG     backup           cwmlite01.dbf    lost+found       users01.dbf

$ cp control01.ctl control02.ctl

$ cp control01.ctl control03.ctl

 

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 1511484856 bytes

Fixed Size                   736696 bytes

Variable Size             553648128 bytes

Database Buffers      956301312 bytes

Redo Buffers              798720 bytes

Database mounted.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00338: log 1 of thread 1 is more recent than controlfile

ORA-00312: online log 1 thread 1: '/oradata/REDO1_01.LOG'

ORA-00312: online log 1 thread 1: '/oradata/REDO1_02.LOG'

 

 

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 6612477360 generated at 02/26/2009 15:51:32 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_1.dbf

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

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 6612478016 generated at 02/26/2009 15:57:20 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_2.dbf

ORA-00280: change 6612478016 for thread 1 is in sequence #2

ORA-00278: log file '/oradata/backup/archivelogbackup/1_1.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612478685 generated at 02/26/2009 16:01:38 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_3.dbf

ORA-00280: change 6612478685 for thread 1 is in sequence #3

ORA-00278: log file '/oradata/backup/archivelogbackup/1_2.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612480148 generated at 02/26/2009 16:10:36 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_4.dbf

ORA-00280: change 6612480148 for thread 1 is in sequence #4

ORA-00278: log file '/oradata/backup/archivelogbackup/1_3.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612480791 generated at 02/26/2009 16:14:28 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_5.dbf

ORA-00280: change 6612480791 for thread 1 is in sequence #5

ORA-00278: log file '/oradata/backup/archivelogbackup/1_4.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612480890 generated at 02/26/2009 16:14:55 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_6.dbf

ORA-00280: change 6612480890 for thread 1 is in sequence #6

ORA-00278: log file '/oradata/backup/archivelogbackup/1_5.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612480971 generated at 02/26/2009 16:15:20 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_7.dbf

ORA-00280: change 6612480971 for thread 1 is in sequence #7

ORA-00278: log file '/oradata/backup/archivelogbackup/1_6.dbf' no longer needed

for this recovery

 

 

ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf

ORA-00280: change 6612481053 for thread 1 is in sequence #8

ORA-00278: log file '/oradata/backup/archivelogbackup/1_7.dbf' no longer needed

for this recovery

 

 

ORA-00328: archived log ends at change 6612393963, need later change 6612481053

ORA-00334: archived log: '/oradata/backup/archivelogbackup/1_8.dbf'

 

提示说当前恢复到scn: 6612393963,需要更多的恢复直到scn 6612481053

SQL> select l.group#,l.sequence#,l.status,l.first_change# ,lf.MEMBER

from v$log l,v$logfile lf

where l.GROUP# = lf.GROUP#;  2    3 

 

    GROUP#  SEQUENCE# STATUS                            FIRST_CHANGE#

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

MEMBER

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

        1        0 UNUSED                                    0

/oradata/REDO1_01.LOG

 

        2        0 UNUSED                                    0

/oradata/REDO2_01.LOG

 

        3        1 CURRENT                          6612476308

/oradata/REDO3_01.LOG

 

3  rows selected.

 

 

我们看到当前文档为group 3,使用当前文件恢复.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf

ORA-00280: change 6612481053 for thread 1 is in sequence #8

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

/oradata/REDO3_01.LOG

ORA-00310: archived log contains sequence 7; sequence 8 required

ORA-00334: archived log: '/oradata/REDO3_01.LOG'

 

 

提示说还差一步了,使用归档日志1恢复.

 

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 6612481053 generated at 02/26/2009 16:15:45 needed for thread

1

ORA-00289: suggestion : /oradata/backup/archivelogbackup/1_8.dbf

ORA-00280: change 6612481053 for thread 1 is in sequence #8

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

/oradata/REDO1_01.LOG

Log applied.

Media recovery complete.

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> select * from mytable;

 

F1

----

01

02

03

04

05

06

 

6 rows selected. SQL> alter database open resetlogs;

 

6 rows selected.

 

/*小结*/

在出现介质错误时,如果存在一致的完全备份和备份之后的所有归档,则可以考虑使用这种方法恢复.

恢复分为三个步骤.

1,文件覆盖;

2,recover database using backup controlfile until cancel选择auto,自动使用已经备份的东东恢复.

3,查询v$log,从status为current的日志开始(其他应该为unused,因为其他的应该已经归档,并在上一步中成功恢复).

4,使用current的下一个要归档的日志文件完全恢复过程.