Oracle 基于用户管理的不完全恢复（五）

案例4——误删除表空间（有备份）

通过备份的控制文件找到与表空间有关的信息进行恢复，因为新的控制文件里面已经没有该表空间的信息了。实际上在整个恢复过程中还是利用归档日志进行恢复，如果删除表空间之前的操作有及时写入到归档信息，就会全部恢复出来。下面的案例分切换日志和不切换日志两种。

1、基于backup control 的不完全恢复

SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME

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

5 /u01/app/oracle/oradata/anny/text01.dbf TEXT

4 /u01/app/oracle/oradata/anny/user01.dbf USERS

3 /u01/app/oracle/oradata/anny/sysaux01.dbf SYSAUX

2 /u01/app/oracle/oradata/anny/lx02.dbf LX02

1 /u01/app/oracle/oradata/anny/system01.dbf SYSTEM

6 /u01/app/oracle/oradata/anny/lx01.dbf LX01

7 /u01/app/oracle/oradata/anny/undotbs01.dbf UNDOTBS

8 /u01/app/oracle/oradata/anny/lx03.dbf LX03

9 /u01/app/oracle/oradata/anny/lx04.dbf LX04

10 /u01/app/oracle/oradata/anny/index01.dbf INDEXES

10 rows selected.

SQL> conn scott/tiger

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

DEPT USERS

EMP USERS

BONUS USERS

SALGRADE USERS

EMPLOYEES USERS

EMP2 USERS

TB01 TEXT

ADMIN_EXT_EMPLOYEES

EMP1 USERS

9 rows selected.

SQL> select * from tb01;

----------

SQL> insert into tb01 select * from tb01;

3 rows created.

SQL> select * from tb01;

----------

6 rows selected.

SQL> commit;

Commit complete.

——生成控制文件备份

SQL> conn /as sysdba

Connected.

SQL> alter database backup controlfile to '/disk1/backup/anny/anny_control.bak';

Database altered.

——不切换日志

SQL> insert into scott.tb01 values(4);

1 row created.

SQL> insert into scott.tb01 values(5);

1 row created.

SQL> commit;

Commit complete.

===================分割线上面是不切换日志，下面是切换日志=================================================

——切换日志

SQL> insert into scott.tb01 values(4);

1 row created.

SQL> insert into scott.tb01 values(5);

1 row created.

SQL> alter system switch logfile;

System altered.

SQL> commit;

Commit complete.

SQL> insert into scott.tb01 values(6);

1 row created.

SQL> alter system switch logfile;

System altered.

SQL> commit;

Commit complete.

——误删除了cuug的表空间

SQL> drop tablespace text including contents and datafiles;

Tablespace dropped.

SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME

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

4 /u01/app/oracle/oradata/anny/user01.dbf USERS

3 /u01/app/oracle/oradata/anny/sysaux01.dbf SYSAUX

2 /u01/app/oracle/oradata/anny/lx02.dbf LX02

1 /u01/app/oracle/oradata/anny/system01.dbf SYSTEM

6 /u01/app/oracle/oradata/anny/lx01.dbf LX01

7 /u01/app/oracle/oradata/anny/undotbs01.dbf UNDOTBS

8 /u01/app/oracle/oradata/anny/lx03.dbf LX03

9 /u01/app/oracle/oradata/anny/lx04.dbf LX04

10 /u01/app/oracle/oradata/anny/index01.dbf INDEXES

9 rows selected.

查看告警日志信息：

——查看 tablespace的删除的时间点

Tue Mar 20 15:57:07 2012

drop tablespace text including contents and datafiles

Tue Mar 20 15:57:09 2012

Deleted file /u01/app/oracle/oradata/anny/text01.dbf

Completed: drop tablespace text including contents and datafiles

===================分割线上面是不切换日志，下面是切换日志=================================================

Tue Mar 20 17:10:06 2012

drop tablespace text including contents and datafiles

Tue Mar 20 17:10:08 2012

Deleted file /u01/app/oracle/oradata/anny/text01.dbf

Completed: drop tablespace text including contents and datafiles

——关闭数据库，启动到no mount状态

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1279964 bytes

Variable Size 58722340 bytes

Database Buffers 251658240 bytes

Redo Buffers 2912256 bytes

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/anny/c

ontrol01.ctl, /disk1/oradata/a

nny/control02.ctl, /disk2/orad

ata/anny/control03.ctl

06:29:17 SQL>

——用备份的控制文件覆盖当前的controlfile

[oracle@solaris10 ~]$cp /disk1/backup/anny/anny_control.bak /u01/app/oracle/oradata/anny/control01.ctl

[oracle@solaris10 ~]$cp /disk1/backup/anny/anny_control.bak /disk1/oradata/anny/control02.ctl

[oracle@solaris10 ~]$cp /disk1/backup/anny/anny_control.bak /disk2/oradata/anny/control03.ctl

——启动到mount状态，转储所有的数据文件

SQL> alter database mount;

Database altered.

[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/*.dbf /u01/app/oracle/oradata/anny/

——基于backup controlfile的不完全恢复

SQL> recover database until time '2012-03-20 15:57:07' using backup controlfile;——第一次选择auto

ORA-00279: change 601174 generated at 03/19/2012 16:26:11 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_19_777487681.log

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

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

auto

ORA-00279: change 601275 generated at 03/19/2012 16:32:05 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_20_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_19_777487681.log' no longer needed for this recovery

ORA-00279: change 602936 generated at 03/19/2012 17:32:24 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_21_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_20_777487681.log' no longer needed for this recovery

ORA-00279: change 602948 generated at 03/19/2012 17:32:54 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_22_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_21_777487681.log' no longer needed for this recovery

ORA-00279: change 602966 generated at 03/19/2012 17:33:46 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_23_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_22_777487681.log' no longer needed for this recovery

ORA-00279: change 602973 generated at 03/19/2012 17:33:59 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_24_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_23_777487681.log' no longer needed for this recovery

ORA-00279: change 622999 generated at 03/19/2012 17:57:46 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_25_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_24_777487681.log' no longer needed for this recovery

ORA-00279: change 644792 generated at 03/19/2012 18:59:48 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_26_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_25_777487681.log' no longer needed for this recovery

ORA-00279: change 665127 generated at 03/20/2012 10:59:23 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_27_777487681.log

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

ORA-00278: log file '/disk1/arch/anny/arch_1_26_777487681.log' no longer needed for this recovery

ORA-00308: cannot open archived log '/disk1/arch/anny/arch_1_27_777487681.log'

ORA-27037: unable to obtain file status

Intel SVR4 UNIX Error: 2: No such file or directory

Additional information: 3

SQL> recover database until time '2012-03-20 15:57:07' using backup controlfile;——第二次选择cancel

ORA-00279: change 665127 generated at 03/20/2012 10:59:23 needed for thread 1

ORA-00289: suggestion : /disk1/arch/anny/arch_1_27_777487681.log

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

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

cancel

Media recovery cancelled.

===================分割线上面是不切换日志，下面是切换日志=================================================

——进行日志切换的情况下，只需要执行一次恢复语句

SQL> recover database until time '2012-03-20 17:10:06' using backup controlfile;

——不能利用该时间点恢复表空间，因为在这之前也有一次drop tablespace的命令，恢复到这个时间点后的状态，表也是被删除状态

——虽然是利用之前的时间点恢复，但是这个时间点后面的dml操作都记录在日志中，也会被一起恢复

查看告警日志：

Tue Mar 20 16:34:54 2012

ALTER DATABASE RECOVER database until time '2012-03-20 15:57:07' using backup controlfile

Tue Mar 20 16:34:54 2012

Media Recovery Start

ORA-279 signalled during: ALTER DATABASE RECOVER database until time '2012-03-20 15:57:07' using backup controlfile ...

Tue Mar 20 16:35:35 2012