TSPIRT:tablespace point-in-time-recovery
使用表时间点恢复可以将一个或多个非system表空间恢复到与数据库不同的某个时间点上。
该技术在实际中运用的并不多
1. 确定还原时间点，如果没有使用catalog则恢复是一次性的过程。如果错误地表示了恢复的时间点，则不能重新来过。如果有catalog则不存在该限制
2. 确定传送集中的对象是自我包含的
SELECT obj1_owner, obj1_name, obj1_type,reasonFROM sys.ts_pitr_check
WHERE (ts1_name IN ('TEST2') AND ts2_name NOT IN (' TEST2')) OR (ts1_name NOT IN (' TEST2') AND Ts2_Name IN (' TEST2'))
3. 保存可能丢失的对象或数据
如果我们将test2表空间恢复到之前的某个时间点，那么该时间点之后的任何改变都会丢失，因此建议保留之后的数据或是到另外一个数据库中恢复
SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('USERS','TOOLS')
AND CREATION_TIME > TO_DATE('02-NOV-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
4. 利用TSPITR
SQL> create tablespace test2 datafile '/ora105/oradata/STCSTEST/test2.dbf' size 100M;
Tablespace created.
SQL> create user test2 indentified by test2 default tablespace test2;
create user test2 indentified by test2 default tablespace test2 *
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> c/ind/id
1* create user test2 identified by test2 default tablespace test2
SQL> /
User created.
SQL> grant resource,connect to test2;
Grant succeeded.
SQL> create table test2.t1 as select * from dba_objects;
Table created.
SQL> create table test2.t2 as select * from dba_objects;
Table created.
$RMAN target /
RMAN>backup database format ‘/ora105/backup/db_%u.bak’;
RMAN>backup tablespace test2;
SQL> drop table test2.t1;
Table dropped.
SQL> drop table test2.t2;
Table dropped.
SQL> select * from test2.t1;
select * from test.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from test2.t2;
select * from test.t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
$rman target /
RMAN> recover tablespace test2 until time "to_date('2011-11-1 08:58:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/ora105';
Starting recover at 01-NOV-11
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
Creating automatic instance, with SID='Arce'
initialization parameters used for automatic instance:
db_name=STCSTEST
compatible=10.2.0.3.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_STCSTEST_Arce
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/ora105
control_files=/ora105/cntrl_tspitr_STCSTEST_Arce.f
starting up automatic instance STCSTEST
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 2082856 bytes
Variable Size 146802648 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
Automatic instance created
contents of Memory Script.:
{
# set the until clause
set until time "to_date('2011-11-1 08:58:00','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 01-NOV-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /ora105/flash_recovery_area/STCSTEST/autobackup/2011_11_01/o1_mf_s_766054676_7byk0nbk_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/ora105/flash_recovery_area/STCSTEST/autobackup/2011_11_01/o1_mf_s_766054676_7byk0nbk_.bkp tag=TAG20111101T085756
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/ora105/cntrl_tspitr_STCSTEST_Arce.f
Finished restore at 01-NOV-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script.:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2011-11-1 08:58:00','yyyy-mm-dd hh24:mi:ss')";
plsql <<
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TEST2' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 5 to
"/ora105/oradata/STCSTEST/test2.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 5;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 5 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TEST2", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TEST2 offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 01-NOV-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /ora105/backup/db_01mqi473.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/ora105/backup/db_01mqi473.bak tag=TAG20111101T085707
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /ora105/oradata/STCSTEST/test2.dbf
channel ORA_AUX_DISK_1: reading from backup piece /ora105/backup/test2.03mqi48j.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/ora105/backup/test2.03mqi48j.bak tag=TAG20111101T085755
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 01-NOV-11
datafile 1 switched to datafile copy
input datafile copy recid=4 stamp=766055881 filename=/ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_system_7byl64tf_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=5 stamp=766055881 filename=/ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_undotbs1_7byl64tg_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 5 online
Starting recover at 01-NOV-11
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 25 is already on disk as file /ora105/archive/STCSTEST/1_25_766005807.dbf
archive log filename=/ora105/archive/STCSTEST/1_25_766005807.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-NOV-11
database opened
contents of Memory Script.:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/ora105/10.2/bin/oracle\)\(ARGV0=oracleArce\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=Arce^'\)\)\(CONNECT_DATA=\(SID=Arce\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
TEST2 file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TEST2 online";
sql "alter tablespace TEST2 offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
Export: Release 10.2.0.4.0 - Production on Tue Nov 1 09:18:04 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace TEST2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T1
. . exporting table T2
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.4.0 - Production on Tue Nov 1 09:18:24 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing TEST2's objects into TEST2
. . importing table "T1"
. . importing table "T2"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TEST2 online
sql statement: alter tablespace TEST2 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora105/cntrl_tspitr_STCSTEST_Arce.f deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_system_7byl64tf_.dbf deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_undotbs1_7byl64tg_.dbf deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/datafile/o1_mf_temp_7byl6cvk_.tmp deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/onlinelog/o1_mf_1_7byl6cgl_.log deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/onlinelog/o1_mf_2_7byl6ck5_.log deleted
auxiliary instance file /ora105/TSPITR_STCSTEST_ARCE/onlinelog/o1_mf_3_7byl6cm5_.log deleted
Finished recover at 01-NOV-11
SQL> select * from test2.t2
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/ora105/oradata/STCSTEST/test2.dbf
SQL> select tablespace_name,online_status from dba_data_files;
TABLESPACE_NAME ONLINE_
------------------------------ -------
USERS ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
SYSTEM SYSTEM
TEST2 OFFLINE
SQL> alter datafile 5 read only;
alter datafile 5 read only *
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter tablespace test2 read write;
alter tablespace test2 read write
*
ERROR at line 1:
ORA-01646: tablespace 'TEST2' is not read only - cannot make read write
SQL> alter tablespace test2 online;
Tablespace altered.
SQL> select count(*) from test2.t1;
COUNT(*)
----------
50033
5. 删除辅助实例
SQL>exec dbms_backup_restore.manageauxinstance(‘'Arce’,1);
6. 文檔：
http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/backup.102/b14191/rcmtspit.htm
Oracle® Database Backup and Recovery Advanced User's Guide
10g Release 2 (10.2)
Part Number B14191-02
7.
transport tablespace test2 tablespace destination '/ora105/oradata'
auxiliary destination '/ora105' until time="to_date('2011-11-01 11:03:55','yyyy-mm-dd hh24:mi:ss')";
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-714753/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24849178/viewspace-714753/
780
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
