ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (恢复-表空间基于时间点恢复(手动))

oracle实验记录 (恢复-表空间基于时间点恢复(手动))

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-28 10:59:25 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427895