ITPub博客

首页 > Linux操作系统 > Linux操作系统 > TSPIRT:tablespace point-in-time-recovery

TSPIRT:tablespace point-in-time-recovery

原创 Linux操作系统 作者:g644516804 时间:2012-01-11 16:04:07 0 删除 编辑

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 <<<-- tspitr_2

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

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

注册时间:2011-03-04

  • 博文量
    104
  • 访问量
    231611