一 模拟环境
1.Create environment
sqlplus / as sysdba
sql> create tablespace myexample
sql> conn tiger/tiger
sql> create table mysales tablespace myexample as select * from dba_tables;
sql> select count(*) from mysales;
2 备份
[oracle@paynode2 new]$ rman target sys@PAYRAC
RMAN>backup database format '+ARCHIVELOG/back/%d_%p_%s.FULL'
3.truncate 表
sqlplus / as sysdba
sql > alter system switch logfile;
sql > /
sql > select current_scn from v$database;
sql > select count(*) from table tiger. mysales;
sql > set time on
10:15:41 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
317123764
10:15:51 SQL> truncate table tiger. mysales;
二 利用rman备份和TSPITR 恢复rac truncate的表数据
(1). 建立密码文件
orapwd file=initaux.ora entries=4 password=aux
(2).建立参数文件
1.连接主库,建立参数文件
sqlplus sys/man@payrac as sysdba
create pfile='/data/app/oracle/product/11.2.0/dbhome_1/dbs/initaux.ora' from spfile;
2.编辑 initaux.ora 文件
修改
*.log_file_name_convert=('+DATA_FILE','/data/aux','+ARCHIVELOG','/data/aux')
*.db_file_name_convert=('+DATA_FILE','/data/aux')
*.control_files='/data/aux/aux.ctl'
*.db_unique_name='aux'
加入
*.instance_number=1
改小
*.memory_target=3488881664
删除有关内存分配的参数
(3).restore 文件
rman target sys/man@payrac
rman > list backup of datafile 1,3,4,22;
1 Full 317120994 21-OCT-11 +DATA_FILE/payrac/datafile/system.259.735856809
3 Full 317120994 21-OCT-11 +DATA_FILE/payrac/datafile/undotbs1.261.735856815
4 Full 317120994 21-OCT-11 +DATA_FILE/payrac/datafile/undotbs2.263.735856825
22 Full 317120994 21-OCT-11 +DATA_FILE/payrac/datafile/myexample.264.764951973
( 注:另开一个窗口 mkdir -p /data/aux/payrac/datafile)
rman > restore controlfile to '/data/aux/aux.ctl' from '+ARCHIVELOG/back/control.c-1544998241-20111021-00';
RMAN> run {
set newname for datafile 1 to '/data/aux/payrac/datafile/system.259.735856809';
set newname for datafile 3 to '/data/aux/payrac/datafile/undotbs1.261.735856815';
set newname for datafile 4 to '/data/aux/payrac/datafile/undotbs2.263.735856825';
set newname for datafile 22 to '/data/aux/payrac/datafile/myexample.264.764951973'
restore datafile 1,3,4,22;
switch datafile all;
}
(4). 启动辅助例程aux
export ORACLE_SID=aux
sqlplus / as sysdba
sql > startup nomount pfile=/data/app/oracle/product/11.2.0/dbhome_1/dbs/initaux.ora
sql > alter database mount clone database;
sql > alter database datafile 1 online;
sql > alter database datafile 3 online;
sql > alter database datafile 4 online;
sql > alter database datafile 22 online;
(5). 修改 log_archive_dest_1之后 做恢复
sql > alter system set log_archive_dest_1='LOCATION=+ARCHIVELOG/arch';
sql > recover database until change 317123764 using backup controlfile;
(6). 打开数据库
sql > alter database open read only;
(7). 导出表数据,并在正式库上导入
exp system/oracle tablespace=MYEXAMPLE file=use01.dmp
或者
exp system/oracle tables=tiger.mysales file=use02.dmp
或者
expdp system/oracle
用imp,impdp 导入即可
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22392018/viewspace-709960/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22392018/viewspace-709960/