案例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;
ID
----------
1
2
3
SQL> insert into tb01 select * from tb01;
3 rows created.
SQL> select * from tb01;
ID
----------
1
2
3
1
2
3
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
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:35 2012
Media Recovery Log /disk1/arch/anny/arch_1_19_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:36 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:36 2012
Media Recovery Log /disk1/arch/anny/arch_1_20_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:36 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:36 2012
Media Recovery Log /disk1/arch/anny/arch_1_21_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_22_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_23_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_24_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_25_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_26_777487681.log
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Mar 20 16:35:37 2012
Media Recovery Log /disk1/arch/anny/arch_1_27_777487681.log
Errors with log /disk1/arch/anny/arch_1_27_777487681.log
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Mar 20 16:35:37 2012
ALTER DATABASE RECOVER CANCEL
Tue Mar 20 16:35:39 2012
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Tue Mar 20 16:38:09 2012
ALTER DATABASE RECOVER database until time '2012-03-20 15:57:07' using backup controlfile
Tue Mar 20 16:38:09 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:38:19 2012
ALTER DATABASE RECOVER CANCEL
Tue Mar 20 16:38:19 2012
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
——验证：
SQL> alter database open resetlogs;
Database altered.
SQL> col file_name for a50
SQL> col tablespace_name for a15
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> select * from scott.tb01;
ID
----------
1
2
3
===================分割线上面是不切换日志，下面是切换日志=================================================
SQL> select * from tb01;
ID
----------
1
2
3
1
2
3
4
5
8 rows selected.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
转载于:https://blog.51cto.com/19880614/1211808
1377
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
