tablespace point in time recover(TSPITR)
对于不完全恢复 不是常用的 代价太大,所有数据文件 都要丢失数据
TSPITR 就是将某个或多个出问题的tablespace回退到 要恢复的时间点,其它tablespace不变
简单介绍下TSPIRT :
tspitr只适用于archivelog mode
primary database:也是target database ,就是指 包含要恢复tablespace的tablespace
recovery set:需要执行recovry的tablespace set(必须自包含)
auxiliary database:是target database的一个副本database,当执行tspITRd的时候,auxixiary 把recovery set恢复到过去的时间点,auxiliary database 必须有system ,undo
及recovery set 的tablespace
auxiliary SET:就是auxixiary database 所需要的除去recovery set 外的 其他文件,需要controlfile,system datafile,undo datafile
先用手动方式操作一次就明白原理了,然后用rman ,rman操作很简单一条命令就可以
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> col file_name format a40
SQL> col tablespace_name format a20
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
D:\TEST.DBF TEST
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EX EXAMPLE
AMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\US USERS
ERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY SYSAUX
SAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UN UNDOTBS1
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
DOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY SYSTEM
STEM01.DBF
已选择6行。
SQL> alter database begin backup;
数据库已更改。
SQL> @d:\backup\backupscript.txt
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
SQL> alter database end backup;
数据库已更改。
SQL> alter database backup controlfile to 'd:\auxiliary\control01.ctl';
数据库已更改。
SQL> conn xh/a831115
已连接。
SQL> create table t1 (a int) tablespace users;
表已创建。
SQL> create table t2 (a int) tablespace test;
表已创建。
SQL> insert into t1 values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into t2 values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
862980
SQL> truncate table t2;
表被截断。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
863158
SQL> insert into t1 values (2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from t2;
未选定行
SQL> alter system switch logfile;
系统已更改。
SQL> select group#,status ,first_change# from v$log
2 ;
GROUP# STATUS FIRST_CHANGE#
---------- ---------------- -------------
1 ACTIVE 857906
2 INACTIVE 836721
3 CURRENT 863177
SQL> alter system checkpoint;
系统已更改。
SQL> select group#,status ,first_change# from v$log
2 ;
GROUP# STATUS FIRST_CHANGE#
---------- ---------------- -------------
1 INACTIVE 857906
2 INACTIVE 836721
3 CURRENT 863177
SQL> select count(*) from v$archived_log where 862980 between first_change# and nex
t_change#;
COUNT(*)
----------
1
要求:恢复test tablespace上 t2表 到未truncate前(SCN 862980),其它tablespace 不恢复(t1 表 不跟着一起恢复到 SCN 862980)
SQL> execute sys.dbms_tts.transport_set_check('TEST',true);检查能否移动 是否符合条件
PL/SQL 过程已成功完成。
SQL>
SQL> select * from sys.transport_set_violations;
未选定行
如果包含sys object 不行,另外表空间必须自包含比如一个表上有个INDEX INDEX 所在表空间必须是该表空间(还有如分区表,lob列)
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object T2 in tablespace TEST not allowed in pluggable set~~~~~~~~~~~~~~~~~~~~~~~~~~~~SYS OBJECT 不行
create pfile='d:\initauxixh.ora' from spfile
进行修改
*.__db_cache_size=373293056
orcl.__db_cache_size=310378496
*.__java_pool_size=4194304
orcl.__java_pool_size=4194304
*.__large_pool_size=4194304
orcl.__large_pool_size=4194304
*.__shared_pool_size=222298112
orcl.__shared_pool_size=285212672
*.__streams_pool_size=0
orcl.__streams_pool_size=0
*.audit_file_dest='d:\auxiliary'
*.background_dump_dest='d:\auxiliary'
*.compatible='10.2.0.1.0'
*.control_files='d:\auxiliary\CONTROL01.CTL'
*.core_dump_dest='d:\auxiliary'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='orcl'*************************************db_name 必须等于主库 因为要参照主库
*.db_recovery_file_dest='d:\auxiliary'
*.db_recovery_file_dest_size=2147483648
*.log_archive_dest_1=''
*.log_archive_format='ARC%S_%R.%T.arc'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\auxiliary'
*.db_unique_name='auxixh'***********default为db_name,数据库的唯一名~~使用auxiliary 时or standby 必须设置与primary 不同名字
*.db_file_name_convert=('E:\oracle\product\10.2.0\oradata\orcl','d:\auxiliary','d:\','d:\auxili')****映射target database目录到auxiliary(要是与priamry目录一样
不用设置)
*.log_file_name_convert=('E:\oracle\product\10.2.0\oradata\orcl','d:\auxiliary\')***********************同上(oracle会在auxiliary自动建立logfile)
C:\>oradim -new -sid auxixh -intpwd xh123
实例已创建。
C:\>set oracle_sid=auxixh
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 28 10:07:40
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount pfile='d:\initauxixh.ora'
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 230689668 bytes
Database Buffers 373293056 bytes
Redo Buffers 7135232 bytes
SQL> host copy d:\backup\system01.dbf d:\auxiliary\system01.dbf
已复制 1 个文件。
SQL> host copy d:\backup\UNDOTBS01.DBF d:\auxiliary\undotbs01.dbf
已复制 1 个文件。
SQL> host copy d:\backup\TEST.DBF d:\auxiliary\test.dbf
已复制 1 个文件。
SQL> alter database mount clone database;
数据库已更改。
SQL> col name format a40
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------- -------
D:\AUXILIARY\SYSTEM01.DBF SYSOFF
D:\AUXILIARY\UNDOTBS01.DBF OFFLINE
D:\AUXILIARY\SYSAUX01.DBF OFFLINE
D:\AUXILIARY\USERS01.DBF OFFLINE
D:\AUXILIARY\EXAMPLE01.DBF OFFLINE
D:\AUXILIARY\TEST.DBF OFFLINE
已选择6行。
SQL> alter database datafile 'D:\AUXILIARY\SYSTEM01.DBF' online;
数据库已更改。
SQL> alter database datafile 'D:\AUXILIARY\undotbs01.DBF' online;
数据库已更改。
SQL> alter database datafile 'D:\AUXILIARY\test.DBF' online;
数据库已更改。
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
862791
862791
862791
862791
862791
862791
已选择6行。
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
862791
862791
0
0
0
862791
已选择6行。
SQL> recover database until change 862980 using backup controlfile;
ORA-00279: ?? 862791 (? 08/28/2009 09:53:59 ??) ???? 1 ????
ORA-00289: ??: E:\ARCHIVELOG\ARC00008_0695918991.001.ARC
ORA-00280: ?? 862791 (???? 1) ??? #8 ?
指定日志: {=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
862983
862983
0
0
0
862983
已选择6行。
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
862983
862983
0
0
0
862983
已选择6行。
数据库已更改。
SQL> select * from t2;
select * from t2
*
第 1 行出现错误:
ORA-00942: ??????????????
SQL> select * from xh.t2;
A
----------
2
C:\>set oracle_sid=auxixh
C:\>exp 'system/a123 as sysdba' point_in_time_recover=y tablespaces=test file=e:\te
st.dmp
Export: Release 10.2.0.1.0 - Production on 星期五 8月 28 10:15:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出表空间时间点恢复对象...
对于表空间 TEST...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 T2
. 正在导出引用完整性约束条件
. 正在导出触发器
. 终止时间点恢复
成功终止导出, 没有出现警告。
SQL> alter tablespace test offline;(target database)
表空间已更改。
SQL> host copy d:\auxiliary\test.dbf D:\TEST.DBF(target database)~~~~~~~~~~~~将在auxiliary修改好的datafile 复制回primary
已复制 1 个文件。
SQL> host imp 'sys/a831115 as sysdba' point_in_time_recover=y datafiles=D:\TEST.DBF(target database)
file=e:\test.dmp
Import: Release 10.2.0.1.0 - Production on 星期五 8月 28 10:20:07 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入表空间时间点恢复对象...
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 SYS 的对象导入到 SYS
. 正在将 XH 的对象导入到 XH
. . 正在导入表 "T2"
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 但出现警告。
SQL>
SQL> alter tablespace test online;(target database)
表空间已更改。
SQL> select * from t1;(target database)
A
----------
1
2
SQL> select * from t2;(target database)
A
----------
2
SQL> select name,checkpoint_change# from v$datafile;(target database)
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB 863255
F
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB 863255
F
D:\TEST.DBF 864569
已选择6行。
SQL> select name,checkpoint_change# from v$datafile_header;(target database)
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB 863255
F
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 863255
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DB 863255
F
D:\TEST.DBF 864569
已选择6行。
小结:原理很简单,就是将要recover的tablespace 放到auxiliary database上 recover 然后在通过EXP/IMP导入回primary
SQL> desc sys.TS_PITR_objects_to_be_dropped;(这个可以查看 当你恢复tablespace该tablespace 要丢失的,可以通过exp imp解决)
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
CREATION_TIME NOT NULL DATE
TABLESPACE_NAME VARCHAR2(30)
~~~~~~~~~~~~解释及使用 转自Oracle® Database Backup and Recovery User's Guide 11g Release 1 (11.1)
Column Name Meaning
OWNER
Owner of the object to be dropped.
NAME
The name of the object that will be lost as a result of undergoing TSPITR
CREATION_TIME
Creation timestamp for the object.
TABLESPACE_NAME
Name of the tablespace containing the object.
Filter the view for objects whose CREATION_TIME is after the target time for TSPITR. For example, with a recovery set consisting of users and tools, and a
recovery point in time of November 2, 2007, 7:03:11 AM, issue the statement shown in Example 20-4.
Example 20-4 Querying TS_PITR_OBJECTS_TO_BE_DROPPED
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-06:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
SQL> desc sys.TS_PITR_CHECK ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------~~~~~~~~检查是否符合条件
OBJ1_OWNER VARCHAR2(30)
OBJ1_NAME VARCHAR2(30)
OBJ1_SUBNAME VARCHAR2(30)
OBJ1_TYPE VARCHAR2(16)
TS1_NAME VARCHAR2(30)
OBJ2_NAME VARCHAR2(30)
OBJ2_SUBNAME VARCHAR2(30)
OBJ2_TYPE VARCHAR2(15)
OBJ2_OWNER VARCHAR2(30)
TS2_NAME VARCHAR2(30)
CONSTRAINT_NAME VARCHAR2(30)
REASON VARCHAR2(81)
使用例子 专自oracle Oracle® Database Backup and Recovery User's Guide 11g Release 1 (11.1)
You can use the TS_PITR_CHECK view to identify relationships between objects that span the recovery set boundaries. If this view returns rows when queried,
then investigate and correct the problem. Proceed with TSPITR only when TS_PITR_CHECK view returns no rows for the tablespaces not in the recovery set.
Record all actions performed during this step so that you can re-create any suspended or removed relationships after completing TSPITR.
The query in Example 20-1 illustrates how to use the TS_PITR_CHECK view. For an example with an initial recovery set consisting of tools and users, the
SELECT statement against TS_PITR_CHECK would be as follows:
Example 20-1 Querying TS_PITR_CHECK for a Subset of Tablespaces
SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN ('USERS','TOOLS')
AND TS2_NAME NOT IN ('USERS','TOOLS')
)
OR (
TS1_NAME NOT IN ('USERS','TOOLS')
AND TS2_NAME IN ('USERS','TOOLS')
);
To run a complete TSPITR check on all the tablespaces in the database (not just the tablespaces in the recovery set), you can run the query in Example 20-2.
Example 20-2 Querying TS_PITR_CHECK for All Tablespaces
SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
'SYSTEM' IN (TS1_NAME, TS2_NAME)
AND TS1_NAME <> TS2_NAME
AND TS2_NAME <> '-1'
)
OR (
TS1_NAME <> 'SYSTEM'
AND TS2_NAME = '-1'
);
Because of the number and width of the columns in the TS_PITR_CHECK view, you may want to format the columns as follows when running the query:
SET LINESIZE 120
COLUMN OBJ1_OWNER HEADING "own1"
COLUMN OBJ1_OWNER FORMAT a6
COLUMN OBJ1_NAME HEADING "name1"
COLUMN OBJ1_NAME FORMAT a5
COLUMN OBJ1_SUBNAME HEADING "subname1"
COLUMN OBJ1_SUBNAME FORMAT a8
COLUMN OBJ1_TYPE HEADING "obj1type"
COLUMN OBJ1_TYPE FORMAT a8 word_wrapped
COLUMN TS1_NAME HEADING "ts1_name"
COLUMN TS1_NAME FORMAT a6
COLUMN OBJ2_NAME HEADING "name2"
COLUMN OBJ2_NAME FORMAT a5
COLUMN OBJ2_SUBNAME HEADING "subname2"
COLUMN OBJ2_SUBNAME FORMAT a8
COLUMN OBJ2_TYPE HEADING "obj2type"
COLUMN OBJ2_TYPE FORMAT a8 word_wrapped
COLUMN OBJ2_OWNER HEADING "own2"
COLUMN OBJ2_OWNER FORMAT a6
COLUMN TS2_NAME HEADING "ts2_name"
COLUMN TS2_NAME FORMAT a6
COLUMN CONSTRAINT_NAME HEADING "cname"
COLUMN CONSTRAINT_NAME FORMAT a5
COLUMN REASON HEADING "reason"
COLUMN REASON FORMAT a25 word_wrapped
Assume a case in which the partitioned table tp has two partitions, p1 and p2, that exist in tablespaces users and tools respectively. Also assume that a
partitioned index called tpind is defined on tp, and that the index has two partitions id1 and id2 (that exist in tablespaces id1 and id2 respectively). In
this case, you would get the output shown in Example 20-3 when you run the query in Example 20-1.
Example 20-3 Output for Query of TS_PITR_CHECK
own1 name1 subname1 obj1type ts1_name name2 subname2 obj2type own2 ts2_name cname reason
--- ---- ----- ------ ------- ---- ------ -------- --- -------- --- ------
SYSTEM TP P1 TABLE USER TPIND IP1 INDEX PARTITION PARTITION SYS ID1 Partitioned Objects not fully contained in the recovery set
SYSTEM TP P2 TABLE TOOLS TPIND IP2 INDEX PARTITION PARTITION SYS ID2 Partitioned Objects not fully contained in the recovery set
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> shutdown immediate;(auxiliary database )
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount pfile='d:\initauxixh.ora'(auxiliary database )
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 230689668 bytes
Database Buffers 373293056 bytes
Redo Buffers 7135232 bytes
SQL> alter database mount;
alter database mount
*
第 1 行出现错误:
ORA-01697: ???????????
SQL> alter database mount clone database;(auxiliary database ) auxiliary 关闭后 再打开 必须按这个步骤
数据库已更改。
SQL> alter database open;(auxiliary database )
SQL> conn xh/a831115
已连接。
SQL> select * from t1;
select * from t1
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 4
ORA-01110: 数据文件 4: 'D:\AUXILIARY\USERS01.DBF'
SQL> select * from t2;
A
----------
2
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-613473/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-613473/
扫一扫
3195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
