rman表空间基于时间点的不完全恢复

最新推荐文章于 2021-07-28 10:04:52 发布

cucanqi9387

最新推荐文章于 2021-07-28 10:04:52 发布

阅读量117

点赞数

数据库版本：9204
目标数据库名称 paul
辅助实例 AUX1

1.备份数据库用带恢复目录的模式
C:\Documents and Settings\Paul Yi>rman target / catalog paultest/paultest@dev89

Recovery Manager: Release 9.2.0.4.0 - Production

connected to target database: PAUL (DBID=1605165889)
connected to recovery catalog database

RMAN> backup database;

Starting backup at 02-JAN-08
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-JAN-08
channel ORA_DISK_1: finished piece 1 at 02-JAN-08
piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 02-JAN-08

Starting Control File and SPFILE Autobackup at 02-JAN-08
piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONE
Finished Control File and SPFILE Autobackup at 02-JAN-08

RMAN>

2在paul数据库表空间ts_test下创建测试用户
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:15:45 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
sql> create tablespace ts_test datafile 'd:\oracle\oradata\paul\ts_test01.dbf' size 50M;

SQL> create user test identified by test default tablespace ts_test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table stu(no number,name varchar2(10));

Table created.

SQL> insert into stu values(109,'abcd');

1 row created.

SQL> select * from stu;

NO NAME
---------- --------------------
109 abcd

SQL> commit;

Commit complete.

SQL> conn /as sysdba
Connected.
备份控制文件
SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;

Database altered.

SQL>

SQL> conn test/test
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STU TABLE

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

记住删除此表的时刻是
SQL> select sysdate from dual;

SYSDATE
-------------------
2008-01-02 15:14:08

SQL> drop table stu;

Table dropped.

3.为辅助实例创建初始化参数文件
新建 d:\oracle\oradata\aux1 目录
复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件
然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：
control_files='d:\control02.ctl'
修改log_archive_start=false
辅助实例必须为非归档模式。
添加如下两个参数：
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务用oradmin工具
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manual

5.启动辅助实例到nomount状态
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:36:02 2008

Connected to an idle instance.
SQL> create spfile from pfile;
file created
SQL> startup nomount
Total System Global Area 101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

6.开始表空间不完全恢复
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

C:\Documents and Settings\Paul Yi>rman target sys/abcdefg@paul catalog paultest/
paultest@dev89 auxiliary /

Recovery Manager: Release 9.2.0.4.0 - Production

connected to target database: PAUL (DBID=1605165889)
connected to recovery catalog database
connected to auxiliary database: paul (not mounted)

RMAN> run{
2> allocate auxiliary channel a1 type disk;
3> allocate channel c1 type disk;
4> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mm
-dd hh24:mi:ss')";
5> }

allocated channel: a1
channel a1: sid=10 devtype=DISK

allocated channel: c1
channel c1: sid=9 devtype=DISK

Starting recover at 02-JAN-08

printing stored script. Memory Script
{
# set the until clause
set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# 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;';
# resync catalog after controlfile restore
resync catalog;
}
executing script. Memory Script

executing command: SET until clause

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restore
channel a1: restoring controlfile
output filename=D:\CONTROL02.CTL
channel a1: restored backup piece 1
piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULL
channel a1: restore complete
Finished restore at 02-JAN-08

replicating controlfile
input filename=D:\CONTROL02.CTL

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

starting full resync of recovery catalog
full resync complete

printing stored script. Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2008-01-02 15:14:08','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 '|| 'TS_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
"D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";
# set a destination filename for restore
set newname for datafile 6 to
"D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
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 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TS_TEST", "SYSTEM", "UNDOTBS1";
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 script. Memory Script

executing command: SET until clause

sql statement: alter tablespace TS_TEST offline for recover

executing command: SET NEWNAME

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF
channel a1: restored backup piece 1
piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULL
channel a1: restore complete
Finished restore at 02-JAN-08

datafile 6 switched to datafile copy
input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_T
EST01.DBF

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

Starting recover at 02-JAN-08

starting media recovery

archive log thread 1 sequence 206 is already on disk as file D:\ARCHPAUL\1_206.D
BF
archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206
media recovery complete
Finished recover at 02-JAN-08

database opened

printing stored script. Memory Script
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
TS_TEST file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/abcdefg@paul as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TS_TEST online";
sql "alter tablespace TS_TEST offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing script. Memory Script

Export: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:12 2008

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TS_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table STU
. 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 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "STU"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TS_TEST online

sql statement: alter tablespace TS_TEST offline

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

starting full resync of recovery catalog
full resync complete
Finished recover at 02-JAN-08
released channel: c1

RMAN>

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 15:35:58 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> alter tablespace ts_test online;

Tablespace altered.

SQL> conn test/test
Connected.
SQL> select * from stu;

        NO NAME
---------- --------------------
       109 abcd

      至此，已经恢复

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-79054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-79054/