表空间时间点恢复(二)

二 .rman表空间时间点恢复举例:

(0) 检查需要恢复的表空间的数据文件备份和控制文件备份是否存在
RMAN>backup database include current controlfile
       plus all archivedlog ;
(1)验证表空间的可传输性

18:51:41 SQL> DROP TABLE A PURGE;
Table dropped.
18:52:01 SQL> CONN / AS SYSDBA
Connected.

18:53:35 SQL> select obj1_name,ts1_name,obj2_name,ts2_name from sys.ts_pitr_check WHERE TS2_NAME='TEST';

no rows selected

(2) 准备辅助实例
               control_files 设置为辅助实例上的文件名
               db_name = 目标实例
               DB_UNIQUE_NAMe 如果辅助实例与目标数据库同一主机,必须设置
               instance_name service_names 取一新名,如 AUX
               db_file_name_convert 数据文件名的转换
               log_file_name_convert 日志文件名的转换
                注释掉 log_archive_start 参数
(3) 执行实际的 TSPITR
[oracle@localhost ~]$ sqlplus SYS/rgsong@aux as sysdba
SQL> STARTUP FORCE NOMOUNT;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1220432 bytes
Variable Size             163578032 bytes
Database Buffers          369098752 bytes
Redo Buffers                2973696 bytes

[oracle@localhost ~]$ rman target / auxiliary sys/rgsong@aux

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 15 18:54:29 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: RGSONG (DBID=432983198)
connected to auxiliary database: RGSONG (not mounted)

        RMAN> RUN{
                    allocate auxiliary channel c1 type disk;
                    recover tablespace 'TEST' until time
                    "to_date('08/15/2008 18:51:41','mm/dd/yyyy hh24:mi:ss')"      
                    AUXILIARY DESTINATION '/opt/ora10g/oradata/aux/';
                    release channel c1;
                  }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=155 devtype=DISK

Starting recover at 15-AUG-08
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

contents of Memory Script:
{
# set the until clause
set until  time "to_date('08/15/2008 18:51:41','mm/dd/yyyy 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 15-AUG-08

channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece /opt/ora10g/flash_recovery_area/RGSONG/autobackup/2008_08_15/o1_mf_n_662842252_4bbqwdsg_.bkp
channel c1: restored backup piece 1
piece handle=/opt/ora10g/flash_recovery_area/RGSONG/autobackup/2008_08_15/o1_mf_n_662842252_4bbqwdsg_.bkp tag=TAG20080815T185052
channel c1: restore complete, elapsed time: 00:00:02
output filename=/opt/ora10g/oradata/aux/control01.ctl
output filename=/opt/ora10g/oradata/aux/control02.ctl
output filename=/opt/ora10g/oradata/aux/control03.ctl
Finished restore at 15-AUG-08

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  time "to_date('08/15/2008 18:51:41','mm/dd/yyyy 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 '||  'TEST' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set a destination filename for restore
set newname for datafile  1 to
 "/opt/ora10g/oradata/aux/system01.dbf";
# set a destination filename for restore
set newname for datafile  2 to
 "/opt/ora10g/oradata/aux/undotbs01.dbf";
# set a destination tempfile
set newname for tempfile  1 to
 "/opt/ora10g/oradata/aux/temp01.dbf";
# set a destination filename for restore
set newname for datafile  7 to
 "/opt/ora10g/oradata/rgsong/test01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 7;
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  7 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TEST", "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 TEST offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME


Starting restore at 15-AUG-08

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/ora10g/oradata/aux/system01.dbf
restoring datafile 00002 to /opt/ora10g/oradata/aux/undotbs01.dbf
restoring datafile 00007 to /opt/ora10g/oradata/rgsong/test01.dbf
channel c1: reading from backup piece /opt/ora10g/flash_recovery_area/RGSONG/backupset/2008_08_15/o1_mf_nnndf_TAG20080815T183944_4bbq7jqr_.bkp
channel c1: restored backup piece 1
piece handle=/opt/ora10g/flash_recovery_area/RGSONG/backupset/2008_08_15/o1_mf_nnndf_TAG20080815T183944_4bbq7jqr_.bkp tag=TAG20080815T183944
channel c1: restore complete, elapsed time: 00:00:45
Finished restore at 15-AUG-08

datafile 7 switched to datafile copy
input datafile copy recid=34 stamp=662842588 filename=/opt/ora10g/oradata/rgsong/test01.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  7 online

Starting recover at 15-AUG-08

starting media recovery

archive log thread 1 sequence 21 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_21_4bblqww3_.arc
archive log thread 1 sequence 22 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_22_4bblzbc9_.arc
archive log thread 1 sequence 23 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_23_4bbncs5f_.arc
archive log thread 1 sequence 24 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_24_4bbocsxx_.arc
archive log thread 1 sequence 25 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_25_4bbolx7x_.arc
archive log thread 1 sequence 26 is already on disk as file /opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_26_4bbr565t_.arc
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_21_4bblqww3_.arc thread=1 sequence=21
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_22_4bblzbc9_.arc thread=1 sequence=22
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_23_4bbncs5f_.arc thread=1 sequence=23
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_24_4bbocsxx_.arc thread=1 sequence=24
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_25_4bbolx7x_.arc thread=1 sequence=25
archive log filename=/opt/ora10g/flash_recovery_area/RGSONG/archivelog/2008_08_15/o1_mf_1_26_4bbr565t_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-AUG-08

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"sys/rgsong@aux as sysdba\" point_in_time_recover=y tablespaces=
 TEST 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  TEST online";
sql "alter tablespace  TEST 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.1.0 - Production on Fri Aug 15 18:56:54 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              A
. 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.1.0 - Production on Fri Aug 15 18:57:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining 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 WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing RGSONG's objects into RGSONG
. . importing table                            "A"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace  TEST online

sql statement: alter tablespace  TEST offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file /opt/ora10g/oradata/aux/control01.ctl deleted
auxiliary instance file /opt/ora10g/oradata/aux/control02.ctl deleted
auxiliary instance file /opt/ora10g/oradata/aux/control03.ctl deleted
auxiliary instance file /opt/ora10g/oradata/aux/system01.dbf deleted
auxiliary instance file /opt/ora10g/oradata/aux/undotbs01.dbf deleted
auxiliary instance file /opt/ora10g/oradata/aux/temp01.dbf deleted
Finished recover at 15-AUG-08
(4) 在目标数据库上执行 TSPITR 后的操作。(验证)
[oracle@localhost ~]$ sqlplus sys/rgsong@RGSONG as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 15 18:58:41 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL>  SELECT * FROM RGSONG.A;
 SELECT * FROM RGSONG.A
                      *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/opt/ora10g/oradata/rgsong/test01.dbf'


SQL> ALTER TABLESPACE TEST ONLINE;

Tablespace altered.

SQL>  SELECT * FROM RGSONG.A;

no rows selected

SQL>