ORACLE 11g TSPITR恢复被删除的表空间

在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复,下面通过一个实例来演示这个功能。
1.创建测试表空间test

SQL> create tablespace test datafile '/u03/app/oracle/oradata/db/test01.dbf' size 50M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/example01.dbf
/u03/app/oracle/oradata/db/test01.dbf

6 rows selected.

2.创建测试用户test与测试表t1

SQL> create user test identified by "test" default tablespace test temporary tablespace temp;

User created.

SQL> create table test.t1 as select * from dba_objects;

Table created.

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

  COUNT(*)
----------
     86031

3.对整个数据库的执行完全备份

RMAN> backup as backupset database plus archivelog;


Starting backup at 2015-06-01 22:05:12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:14
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:21
piece handle=/u02/backup/0kq8ea8q_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:21
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:22
piece handle=/u02/backup/0lq8ea91_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:23
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:26
piece handle=/u02/backup/0mq8ea92_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:26
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:27
piece handle=/u02/backup/0nq8ea96_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:28
piece handle=/u02/backup/0oq8ea97_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:28
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:29
piece handle=/u02/backup/0pq8ea98_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:05:29

Starting backup at 2015-06-01 22:05:29
using channel ORA_DISK_1
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=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:31
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:16
piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
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 2015-06-01 22:09:20
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:21
piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:09:21

Starting backup at 2015-06-01 22:09:21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:30
piece handle=/u02/backup/0sq8eagn_1_1 tag=TAG20150601T220926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2015-06-01 22:09:30

4.删除表空间test,并记录删除操作执行之前的系统SCN与时间

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIME CURRENT_SCN
------------------- -----------
2015-06-01 22:11:45      751203


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1548000
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:01 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:05 redo02.log
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:14 users01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:14 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:14 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:14 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:14 redo01.log
-rw-r----- 1 oracle11 oinstall   9748480 Jun  1 22:14 control01.ctl

从上面的查询可以看到表空间test已经被删除了。

5.现在执行TSPITR将表空间test恢复到被删除之前的时间点

RMAN> recover tablespace "TEST" until time '2015-06-01 22:11:45' auxiliary destination '/u02/auxiliary';

Starting recover at 2015-06-01 22:22:25
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1

Creating automatic instance, with SID='jAvb'

initialization parameters used for automatic instance:
db_name=DB
db_unique_name=jAvb_tspitr_DB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u02/auxiliary
log_archive_dest_1='location=/u02/auxiliary'
#No auxiliary parameter file used


starting up automatic instance DB

Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1369420 bytes
Variable Size                281021108 bytes
Database Buffers             784334848 bytes
Redo Buffers                   4608000 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-06-01 22:22:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0rq8eagd_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl
Finished restore at 2015-06-01 22:22:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/auxiliary/DB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-06-01 22:22:51
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/auxiliary/DB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/auxiliary/DB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/auxiliary/DB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0qq8ea9b_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2015-06-01 22:25:07

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 2015-06-01 22:25:08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u03/archivelog/1_9_881232587.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u03/archivelog/1_10_881232587.dbf
archived log file name=/u03/archivelog/1_9_881232587.dbf thread=1 sequence=9
archived log file name=/u03/archivelog/1_10_881232587.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:16
Finished recover at 2015-06-01 22:25:25

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TEST" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jAvb":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_jAvb" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jAvb is:
   EXPDP>   /u02/auxiliary/tspitr_jAvb_29236.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /u03/app/oracle/oradata/db/test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jAvb" successfully completed at Mon Jun 1 22:27:28 2015 elapsed 0 00:01:12
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_jAvb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_jAvb":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_jAvb" successfully completed at Mon Jun 1 22:28:23 2015 elapsed 0 00:00:14
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TEST" read write';
sql 'alter tablespace  "TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_temp_bprtv5bo_.tmp deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_3_bprttwvr_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_2_bprttt0k_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_1_bprttp4g_.log deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl deleted
Finished recover at 2015-06-01 22:28:45
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/test01.dbf

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1599328
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:22 users01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:22 redo01.log
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:28 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52436992 Jun  1 22:28 test01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:28 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:28 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:28 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:29 redo02.log
-rw-r----- 1 oracle11 oinstall   9814016 Jun  1 22:30 control01.ctl

从上面的查询结果可以看到表空间test已经恢复,但现在test表空间是脱机状态。

6.将表空间test联机,并查询表test.t1来验证恢复是否真正成功.

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

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

  COUNT(*)
----------
     86031

从上面的查询结果可以看到被删除的表空间test已经恢复到被删除之前的时间点,表test.t1的记录已经恢复回来。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.