Oracle flashback dropped tablespace(原创)

Oracle官方并不推荐在数据库物理结构发生改变的情况下进行Flashback，但有些特殊情况。例如项目上线时出现异常需要快速的回滚到表空间被删除之前的操作。那么flashback tablespace想比于进行全库的restore和recover能更快速的满足要求，减少宕机时间。

具体演示

SQL> create tablespace tt datafile '/home/oracle/tt.dbf' size 10m;

Tablespace created.

SQL>  conn hr/hr;
Connected.
SQL> create table t1 tablespace tt as select * from hr.employees;

Table created.

SQL> conn / as sysdba
Connected.
SQL> create restore point aa guarantee flashback database;

Restore point created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     854968

SQL> alter tablespace tt offline;

Tablespace altered.

SQL> drop tablespace tt including contents and datafiles;

Tablespace dropped.
 
SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1874841600 bytes
Fixed Size                  1337268 bytes
Variable Size            1073743948 bytes
Database Buffers          788529152 bytes
Redo Buffers               11231232 bytes
Database mounted.

SQL> flashback database to restore point aa;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> col error for a20
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
         6 OFFLINE OFFLINE FILE NOT FOUND                0

6 rows selected.

SQL> col name for a60
SQL> select name, file#, status from v$datafile;

NAME                                                              FILE# STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/PROD/system01.dbf                             1 SYSTEM
/u01/app/oracle/oradata/PROD/sysaux01.dbf                             2 ONLINE
/u01/app/oracle/oradata/PROD/undotbs01.dbf                            3 ONLINE
/u01/app/oracle/oradata/PROD/users01.dbf                              4 ONLINE
/u01/app/oracle/oradata/PROD/example01.dbf                            5 ONLINE
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006              6 OFFLINE

6 rows selected.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.dbf';

Database altered.

SQL> select name, file#, status from v$datafile;

NAME                                                              FILE# STATUS
------------------------------------------------------------ ---------- -------
/u01/app/oracle/oradata/PROD/system01.dbf                             1 SYSTEM
/u01/app/oracle/oradata/PROD/sysaux01.dbf                             2 ONLINE
/u01/app/oracle/oradata/PROD/undotbs01.dbf                            3 ONLINE
/u01/app/oracle/oradata/PROD/users01.dbf                              4 ONLINE
/u01/app/oracle/oradata/PROD/example01.dbf                            5 ONLINE
/home/oracle/tt.dbf                                                   6 OFFLINE

6 rows selected.

SQL> recover datafile 6; 
ORA-00279: change 854654 generated at 11/15/2015 21:49:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_11_15/o1_mf_1_7_c4k3f1g
g_.arc
ORA-00280: change 854654 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> desc hr.t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> select count(*) from hr.t1;

  COUNT(*)
----------
       107

相关日志

Completed: ALTER DATABASE   MOUNT
Sun Nov 15 21:50:17 2015
flashback database to restore point aa       #flashback database
Flashback Restore Start
Flashback: created tablespace #7: 'TT' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00006' for tablespace #7 in the controlfile.
Filename was:
'/home/oracle/tt.dbf' when dropped.
File will have to be restored from a backup and recovered.
Flashback Restore Complete
Flashback Media Recovery Start

Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
Incomplete Recovery applied until change 854954 time 11/15/2015 21:49:24
Flashback Media Recovery Complete
Completed: flashback database to restore point aa
alter database open resetlogs
Archived Log entry 1 added for thread 1 sequence 7 ID 0x10d32bc0 dest 1:
Archived Log entry 2 added for thread 1 sequence 5 ID 0x10d32bc0 dest 1:
Sun Nov 15 21:50:28 2015
Archived Log entry 3 added for thread 1 sequence 6 ID 0x10d32bc0 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 854954
Resetting resetlogs activation ID 282274752 (0x10d32bc0)
Sun Nov 15 21:50:35 2015
Setting recovery target incarnation to 3

......................................................

Dictionary check beginning
File #6 is offline, but is part of an online tablespace.
data file 6: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006'  #发现datafile 6 offline
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed

......................................................

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.dbf'   #rename datafile 6
Completed: alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as '/home/oracle/tt.db

.................................

ALTER DATABASE RECOVER  datafile 6   #recover datafile 6

Media Recovery Start
Serial Media Recovery started
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 854954
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 6  ...
Sun Nov 15 21:52:57 2015
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_11_15/o1_mf_1_7_c4k3f1gg_.arc
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
Media Recovery Complete (PROD)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
alter database datafile 6 online
Completed: alter database datafile 6 online

 

本文原创，转载请注明出处，作者

如有错误，欢迎指正

邮箱:[e-mail]