实验：基于cancel的不完全恢复

实验：基于cancel的不完全恢复

1、实验环境检查

1）监听与数据库的状态

[oracle@ORCL ~]$ lsnrctl status    --查看监听状态
sys@ORCL> select open_mode from v$database;    --查看数据库状态

2)闪回与归档的配置

sys@ORCL> show parameter recovery;    --查看回复路径
sys@ORCL> select flashback_on from v$database;    --查看是否已经打开闪回功能
sys@ORCL> archive log list;    --查看归档状态

2、完全备份数据库(包括数据文件，当前的控制文件及spfile)

RMAN> backup full database;    --完全备份数据库

3、构造测试数据

sys@ORCL> create table test_t1 as select * from dba_objects;    --创建表
sys@ORCL> alter system archive log current;    --归档当前的重做日志文件
sys@ORCL> create table test_t2 as select * from dba_objects;    --创建表
sys@ORCL> alter system archive log current;    --归档当前的重做日志文件
sys@ORCL> create table test_t3 as select * from dba_objects;    --创建表
sys@ORCL> alter system archive log current;    --归档当前的重做日志文件

完成测试数据构造之后，去存放归档日志的位置，查看新生成的三个归档日志：

[oracle@ORCL ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/    --到存放归档日志的位置
[oracle@ORCL ~]$ ls -lrt    --查看新生成的三个归档日志

4、删除数据文件及联机重做日志文件

[oracle@ORCL ~]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@ORCL ~]$ rm *.dbf
[oracle@ORCL ~]$ rm *.log

可用笔记里的另一种方法:

SYS@ORCL> select name from v$datafile;    --查看数据文件
SYS@ORCL> select member from v$logfile;    --查看日志文件
SYS@ORCL> !rm /u01/app/oracle/oradata/ORCL/datafile/*.dbf    --删除数据文件
SYS@ORCL> !rm /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf*.log    --删除日志文件
SYS@ORCL> !ls /u01/app/oracle/oradata/ORCL/datafile/*.dbf
SYS@ORCL> !ls /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf*.log

5、重启数据库

sys@ORCL> shutdown immediate;    --关闭数据库
              --或shutdown abort;    
sys@ORCL> startup mount;    --启动数据库mount阶段

6、还原数据库

RMAN> restore database;    --还原数据库

7、不完全恢复

sys@ORCL> recover database until cancel;    --最有用的一条命令
...回车....    --一直输入回车直到没有归档日志文件可用为止，可以恢复所有数据；恢复过程中输入cancel，可以只恢复一部分数据。
sys@ORCL> alter database open resetlogs;    --打开数据库时，重置重做日志
sys@ORCL> select table_name,tablespace_name from dba_tables where table_name like ‘TEST%’;    --查询建立在TEST表空间上的所有表名
sys@ORCL> select GROUP#,THREAD#,SEQUENCE# from v$log;    --查看当前的日志状态

8、实验结束

 完整过程：

[oracle@ORCL ~]$ rman target/    --进入RMAN环境

 

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 7 20:43:03 2014

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1385186917)

 

RMAN> show all;    --显示当前的配置信息

 

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORCL.f'; # default

 

SYS@ORCL>select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

RMAN> list backup summary;

 

 

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

1       B  F  A DISK        07-OCT-14       1       1       NO         TAG20141007T204444

2       B  F  A DISK        07-OCT-14       1       1       NO         TAG20141007T204444

 

RMAN> list copy;

 

specification does not match any datafile copy in the repository

List of Control File Copies

===========================

 

Key     S Completion Time Ckp SCN    Ckp Time       

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

2       A 26-SEP-14       1141720    26-SEP-14      

        Name: /home/oracle/bak_ctl.t

        Tag: TAG20140926T124825

 

List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================

 

Key     Thrd Seq     S Low Time 

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

1       1    5       A 04-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_04/o1_mf_1_5_b0j8v0ot_.arc

 

2       1    6       A 04-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_15/o1_mf_1_6_b1f1vd9c_.arc

 

3       1    7       A 15-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_15/o1_mf_1_7_b1fj487n_.arc

 

4       1    8       A 15-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_16/o1_mf_1_8_b1hp37o4_.arc

 

5       1    9       A 16-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_9_b29jsfg6_.arc

 

6       1    10      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_10_b29jxbl4_.arc

 

7       1    11      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_11_b29jxx0c_.arc

 

8       1    12      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_12_b29jy3m0_.arc

 

9       1    13      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_13_b29k0nnc_.arc

 

10      1    14      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_14_b29k0xo6_.arc

 

11      1    15      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_15_b29lkw71_.arc

 

12      1    16      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_16_b29lmmv4_.arc

 

13      1    17      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_17_b29lncsy_.arc

 

14      1    18      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_26/o1_mf_1_18_b29lnqjz_.arc

 

15      1    19      A 26-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_28/o1_mf_1_19_b2klg9td_.arc

 

16      1    20      A 28-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_09_28/o1_mf_1_20_b2kmq233_.arc

 

17      1    21      A 28-SEP-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_21_b391zyvx_.arc

 

18      1    22      A 07-OCT-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_22_b392k221_.arc

 

19      1    23      A 07-OCT-14

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_23_b392nq92_.arc

 

RMAN> backup full database;

 

Starting backup at 07-OCT-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_b0j86znk_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_b0j86zsh_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_b0j86zsr_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_b0j8dl22_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_test_dat_b1hswdpf_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/ind_tbs.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_b0j86ztl_.dbf

channel ORA_DISK_1: starting piece 1 at 07-OCT-14

channel ORA_DISK_1: finished piece 1 at 07-OCT-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_10_07/o1_mf_nnndf_TAG20141007T204444_b3927yhn_.bkp tag=TAG20141007T204444 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 07-OCT-14

channel ORA_DISK_1: finished piece 1 at 07-OCT-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_10_07/o1_mf_ncsnf_TAG20141007T204444_b392cyt3_.bkp tag=TAG20141007T204444 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 07-OCT-14

 

SYS@ORCL>create table test_t1 as select * from dba_objects;

 

Table created.

 

SYS@ORCL>alter system archive log current;

 

System altered.

 

SYS@ORCL>

[root@ORCL ~]# su - oracle

[oracle@ORCL ~]$ cd /u01/app/oracle/flash_recovery_area/

[oracle@ORCL flash_recovery_area]$ ls

ORCL  RITA

[oracle@ORCL flash_recovery_area]$ cd ORCL/

[oracle@ORCL ORCL]$ ls

archivelog  backupset  controlfile  onlinelog

[oracle@ORCL ORCL]$ cd archivelog/

[oracle@ORCL archivelog]$ ls

2014_09_04  2014_09_15  2014_09_16  2014_09_26  2014_09_28  2014_10_07

[oracle@ORCL archivelog]$ cd 2014_10_07/

[oracle@ORCL 2014_10_07]$ ls

o1_mf_1_21_b391zyvx_.arc  o1_mf_1_22_b392k221_.arc

[oracle@ORCL 2014_10_07]$ 

 

SYS@ORCL>create table test_t2 as select * from dba_objects;

 

Table created.

 

SYS@ORCL>alter system archive log current;

 

System altered.

 

SYS@ORCL>create table test_t3 as select * from dba_objects;

 

Table created.

 

SYS@ORCL>alter system archive log current;

 

System altered.

 

 

[oracle@ORCL 2014_10_07]$ ls -lrt

total 35116

-rw-r-----. 1 oracle oinstall  6445568 Oct  7 20:40 o1_mf_1_21_b391zyvx_.arc

-rw-r-----. 1 oracle oinstall  9504768 Oct  7 20:49 o1_mf_1_22_b392k221_.arc

-rw-r-----. 1 oracle oinstall 10491904 Oct  7 20:51 o1_mf_1_23_b392nq92_.arc

-rw-r-----. 1 oracle oinstall  9508352 Oct  7 20:56 o1_mf_1_24_b392y5to_.arc

[oracle@ORCL 2014_10_07]$ 

SYS@ORCL>select table_name,tablespace_name from dba_tables where table_name in ('TEST_T1','TEST_T2','TEST_T3');

 

TABLE_NAME        TABLESPACE_NAME

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

TEST_T3         SYSTEM

TEST_T2         SYSTEM

TEST_T1         SYSTEM

 

日志序列号：

SYS@ORCL>desc v$log;

 Name    Null?    Type

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

 GROUP#      NUMBER

 THREAD#     NUMBER

 SEQUENCE#     NUMBER

 BYTES     NUMBER

 BLOCKSIZE     NUMBER

 MEMBERS     NUMBER

 ARCHIVED     VARCHAR2(3)

 STATUS      VARCHAR2(16)

 FIRST_CHANGE#     NUMBER

 FIRST_TIME     DATE

 NEXT_CHANGE#     NUMBER

 NEXT_TIME     DATE

 

SYS@ORCL>select GROUP#,THREAD#,SEQUENCE# from v$log;

 

    GROUP#    THREAD#  SEQUENCE#

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

 1     1       25

 2     1       23

 3     1       24

 

SYS@ORCL>select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_b0j86znk_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_b0j86zsh_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_b0j86zsr_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_b0j86ztl_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_b0j8dl22_.dbf

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_test_dat_b1hswdpf_.dbf

/u01/app/oracle/oradata/ORCL/ind_tbs.dbf

 

7 rows selected.

 

SYS@ORCL>select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_b0j8cl7p_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_b0j8cnl3_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_b0j8cj5m_.log

/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_b0j8c8o9_.log

/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_b0j8cchh_.log

/u01/app/oracle/oradata/ORCL/onlinelog/o1_m.log

 

6 rows selected.

 

SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/datafile/*.dbf

 

SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf*.log

 

SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/datafile/*.dbf

ls: cannot access /u01/app/oracle/oradata/ORCL/datafile/*.dbf: No such file or directory

 

SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf*.log

ls: cannot access /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf*.log: No such file or directory

 

 

 

SYS@ORCL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORCL>

 

SYS@ORCL>startup mount;

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size     2213856 bytes

Variable Size   331352096 bytes

Database Buffers    67108864 bytes

Redo Buffers     8519680 bytes

Database mounted.

SYS@ORCL>

RMAN> exit

 

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows

ORACLE error from target database: 

ORA-03135: connection lost contact

Process ID: 3419

Session ID: 20 Serial number: 23

 

 

Recovery Manager complete.

[oracle@ORCL ~]$ rman target/

 

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 7 21:11:46 2014

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1385186917, not open)

 

RMAN> 

 

RMAN还原数据库：

 

RMAN> restore database;

 

Starting restore at 07-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_b0j86znk_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_b0j86zsh_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_b0j86zsr_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_b0j86ztl_.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_b0j8dl22_.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_test_dat_b1hswdpf_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/ind_tbs.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_10_07/o1_mf_nnndf_TAG20141007T204444_b3927yhn_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_10_07/o1_mf_nnndf_TAG20141007T204444_b3927yhn_.bkp tag=TAG20141007T204444

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:56

Finished restore at 07-OCT-14

 

RMAN> 

 

基于cancel恢复：

 

SYS@ORCL>recover database until cancel;

ORA-00279: change 1199008 generated at 10/07/2014 20:44:45 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_22_b392k2

21_.arc

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

 

 

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

 

回车

ORA-00279: change 1199234 generated at 10/07/2014 20:49:38 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_23_b392nq

92_.arc

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

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_22_b392k

221_.arc' no longer needed for this recovery

 

 

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

 

回车

ORA-00279: change 1199528 generated at 10/07/2014 20:51:35 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_24_b392y5

to_.arc

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

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/o1_mf_1_23_b392n

q92_.arc' no longer needed for this recovery

 

 

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

 

cancel

Media recovery cancelled.

SYS@ORCL>

 

 

[oracle@ORCL ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_10_07/

[oracle@ORCL 2014_10_07]$ ls

o1_mf_1_21_b391zyvx_.arc  o1_mf_1_23_b392nq92_.arc

o1_mf_1_22_b392k221_.arc  o1_mf_1_24_b392y5to_.arc

 

SYS@ORCL>alter database open resetlogs;

 

Database altered.

 

[oracle@ORCL ~]$ cd /u01/app/oracle/oradata/ORCL/

[oracle@ORCL ORCL]$ ls

controlfile  datafile  ind_tbs.dbf  onlinelog

[oracle@ORCL ORCL]$ 

 

SYS@ORCL>select GROUP#,THREAD#,SEQUENCE# from v$log;

 

    GROUP#    THREAD#  SEQUENCE#

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

 1     1        1

 2     1        0

 3     1        0

 

SYS@ORCL>select table_name,tablespace_name from dba_tables where table_name like 'TEST%';

 

TABLE_NAME        TABLESPACE_NAME

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

TEST_T2         SYSTEM

TEST_T1         SYSTEM