ORACLE异机表空间基于时间点恢复 (TSPITR) Method(恢复部分表空间)
RMAN Tablespace Point-In-Time Recovery ( TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.Prior to 11.2 version the TSPITR had a restriction of not being able to recover the dropped tablespace.From 11.2 this limitation no longer exists.RMAN TSPITR requires that the tablespace be self-contained and that no SYS -owned objects reside in the tablespace.
To perform TSPITR, RMAN requires the database to be in archivelog mode and existing backup of database that was created before the point in time recovery, and all the archivelogs and online logs created from the time of the backup until the point in time recovery . Also the controlfile recovery (from autobackup) must matches with the database physical schema at the point in time when recovery ends.
RMAN TSPITR is most useful for recovery the following situations: DDL\DML corrupt the data in only one or a few tablespace, droped table purge or droped tablespace etc..
Automatic or Manual Model
Automatic Instance Creation for RMAN TSPITR :
oracle automatic Restores the control file and data files from the recovery set and the auxiliary set to the auxiliary instance. The RMAN job restores the auxiliary and recovery sets. Then, it recovers the clone database to the specified point in time. RMAN opens the clone database with resetlogs, and it performs an export of the tablespace(s) to be recovered. Finally, it imports an export dump file into target database completing recovery process, Deletes all auxiliary set files. Automatic mode TSPITR Here I is not to demo ,just using the following command
RECOVER TABLESPACE users UNTIL LOGSEQ 2400 THREAD 1 AUXILIARY DESTINATION '/u01/oradata/auxdest';
Manual Instance Creation for RMAN TSPITR:
The below example is an attempt to show the steps and functioning of TSPITR to recover a tablespace to another ORACLE DB SERVER(oracle software version on tow host must be same).my case db version 11.2.0.3.
scp Rman backup files from source to target db path ‘/backup/db40’.
# on target db
# change ORACLE_SID
export ORACLE_SID=pora40
# create a pfile
vi pfile
DB_NAME=pora40
DB_UNIQUE_NAME=pora40
CONTROL_FILES=/oradata/db40/control01.ctl
log_archive_dest_1='location=/oradata/db40/'
DB_FILE_NAME_CONVERT=('/oracle/oradata/','/oradata/db40/')
LOG_FILE_NAME_CONVERT=('/oracle/oradata/','/oradata/db40/')
REMOTE_LOGIN_PASSWORDFILE=exclusive
COMPATIBLE =11.2.0
DB_BLOCK_SIZE=8192
RMAN> startup nomount
RMAN> restore controlfile from '/backup/db40/pora40_ctrl_20140521_3178_bak';
RMAN> alter database mount;
RMAN> catalog start with '/backup/db40';
RMAN> run
{
SET NEWNAME FOR DATABASE to '/oradata/db40/%b';
SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
SET UNTIL TIME 'May 20 2014 14:00:00';
restore tablespace system,sysaux,undotbs1,users;
SWITCH DATAFILE 1;
SWITCH DATAFILE 2;
SWITCH DATAFILE 3;
SWITCH DATAFILE 4;
SWITCH DATAFILE 5;
SWITCH DATAFILE 12;
switch tempfile all;
recover tablespace system,sysaux,undotbs1,users;
}
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/23/2014 11:12:53
RMAN-06067: RECOVER DATABASE required with a backup or created control file
Note:
Oracle Error: RMAN-06067
Error Description:
RECOVER DATABASE required with a backup or created control file
Error Cause:
The control file has been restored from a backup or was created via ALTER DATABASE CREATE CONTROLFILE.
Action:
Use the RECOVER DATABASE command to perform the recovery.
sys@PORA40>select wm_concat(name) from v$tablespace;
SYSTEM,SYSAUX,UNDOTBS1,USERS,TEMP,XMSB,ZYY,IPTV,MEDICAL,GPPX,JSPX,SCZY,XY_HENAN,
XIANGYI,CMS,PX_PLATFORM
RMAN> run
{
SET NEWNAME FOR DATABASE to '/oradata/db40/%b';
SET NEWNAME FOR TEMPFILE 1 TO '/oradata/db40/temp01.dbf' ;
SET NEWNAME FOR TEMPFILE 2 TO '/oradata/db40/temp02.dbf' ;
SET UNTIL TIME 'May 20 2014 14:00:00';
restore tablespace system,sysaux,undotbs1,users;
SWITCH DATAFILE 1;
SWITCH DATAFILE 2;
SWITCH DATAFILE 3;
SWITCH DATAFILE 4;
SWITCH DATAFILE 5;
SWITCH DATAFILE 12;
switch tempfile all;
recover database skip tablespace XMSB,ZYY,IPTV,MEDICAL,GPPX,JSPX,SCZY,XY_HENAN,XIANGYI,CMS,PX_PLATFORM;
}
... output truncated
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Executing: alter database datafile 6 offline
Executing: alter database datafile 7 offline
Executing: alter database datafile 8 offline
Executing: alter database datafile 9 offline
Executing: alter database datafile 10 offline
Executing: alter database datafile 11 offline
Executing: alter database datafile 13 offline
Executing: alter database datafile 14 offline
Executing: alter database datafile 15 offline
Executing: alter database datafile 16 offline
Executing: alter database datafile 17 offline
Executing: alter database datafile 18 offline
Executing: alter database datafile 19 offline
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8865
...
RMAN> alter database open resetlogs;
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name PORA40
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 15360 SYSTEM *** /oradata/db40/system01.dbf
2 2040 SYSAUX *** /oradata/db40/sysaux01.dbf
3 195 UNDOTBS1 *** /oradata/db40/undotbs01.dbf
4 6601 USERS *** /oradata/db40/users01.dbf
5 94 UNDOTBS1 *** /oradata/db40/undotbs02.dbf
6 0 XMSB *** /oradata/pora40/datafile/xmsb01.dbf
7 0 XMSB *** /oradata/pora40/datafile/xmsb02.dbf
8 0 ZYY *** /oradata/pora40/datafile/zyy01.dbf
9 0 IPTV *** /oradata/pora40/datafile/iptv01.dbf
10 0 MEDICAL *** /oradata/pora40/datafile/medical01.dbf
11 0 MEDICAL *** /oradata/pora40/datafile/medical02.dbf
12 3704 USERS *** /oradata/db40/users02.dbf
13 0 GPPX *** /oradata/pora40/datafile/gppx.dbf
14 0 JSPX *** /oradata/pora40/datafile/jspx01.dbf
15 0 SCZY *** /oradata/pora40/datafile/sczy01.dbf
16 0 XY_HENAN *** /oradata/pora40/datafile/xy_henan01.dbf
17 0 XIANGYI *** /oradata/pora40/datafile/xiangyi01.dbf
18 0 CMS *** /oradata/pora40/datafile/cms01.dbf
19 0 PX_PLATFORM *** /oradata/pora40/datafile/px_platform01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /oradata/db40/temp01.dbf
2 2 TEMP 32767 /oradata/db40/temp02.dbf
# check physical datafile of the new instance
$ ll /oradata/db40/ total 28709184 -rw-r--r-- 1 root root 0 May 22 18:49 0 -rw-r----- 1 oracle oinstall 12664832 May 23 11:51 control01.ctl -rw-r----- 1 oracle oinstall 2139103232 May 23 11:50 sysaux01.dbf -rw-r----- 1 oracle oinstall 16106135552 May 23 11:49 system01.dbf -rw-r----- 1 oracle oinstall 20979712 May 23 11:44 temp01.dbf -rw-r----- 1 oracle oinstall 2105344 May 23 11:47 temp02.dbf -rw-r----- 1 oracle oinstall 204480512 May 23 11:50 undotbs01.dbf -rw-r----- 1 oracle oinstall 98574336 May 23 11:49 undotbs02.dbf -rw-r----- 1 oracle oinstall 6921920512 May 23 11:44 users01.dbf -rw-r----- 1 oracle oinstall 3884457984 May 23 11:44 users02.dbf
Tip:
You can also use Flashback Database to rewind data, but you must rewind the entire database rather than just a subset. Also, unlike TSPITR, the Flashback Database feature necessitates the overhead of maintaining flashback logs. The point in time to which you can flash back the database is more limited than the TSPITR window, which extends back to your earliest recoverable backup.
源库备份在磁带时的恢复
如果源库备份到磁带tape中，无法把备份集从磁带复制到目标主机的磁盘， MOS Alternative option to Restore Rman backuppiece from Tape to disk (Doc ID 2123833.1),同时有时目标库无法挂载磁带库时，可以挂载NFS到源库，源库直接从磁带恢复需要的少量表空间数据文件到NFS，再restore archivelog，scp到目标库做基于时间点的恢复。
You cannot restore or backup a backuppiece from tape to disk using rman.
When the backups are written to tape they have a particular pattern written in first few blocks which are written by their specific MML.
So these will never be recognized by the disk channels if these backuppiece are copied manually from tape to disk ..
Its not possible to copy these backuppiece from tape to tape or from tape to disk using rman commands
–以下操作在souce端操作， 注意千万不要覆盖了生产的数据文件、控制文件。如/home/oracle/rman 是NFS挂载的新路径，而不是原生产数据文件。
# RESTORE DATAFILE ON SOURCE
RMAN> run
{
SET NEWNAME FOR datafile 1 to '/home/oracle/rman/system01.dbf';
set NEWNAME FOR datafile 3 to '/home/oracle/rman/undotbs01.dbf';
restore datafile 1,3;
}2> 3> 4> 5> 6>
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2024-05-09 17:12:36
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/rman/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/rman/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/db_0u2qcrnp_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/rman/db_0u2qcrnp_1_1.bak tag=TAG20240509T170809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2024-05-09 17:12:51
RMAN> exit
-- or --
注意： ORACLE 10G 不支持 SET NEWNAME FOR DATABASE ，需要指定所有需要的文件SET NEWNAME FOR DATAFILE ；
RMAN> run
{
SET NEWNAME FOR DATABASE to '/home/oracle/rman/%b';
SET UNTIL TIME "to_date('20240509 17:14','yyyymmdd hh24:mi')";
restore tablespace system,UNDOTBS1;
}2> 3> 4> 5> 6>
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 2024-05-09 17:19:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/rman/system.256.1164469933
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/rman/undotbs1.258.1164469933
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/db_0u2qcrnp_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/rman/db_0u2qcrnp_1_1.bak tag=TAG20240509T170809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 2024-05-09 17:20:09
# restore archivelog ON SOURCE
RMAN> RUN {
allocate channel c1 type 'sbt_tape';
#New location for archive logs.
set archivelog destination to '/home/oracle/rman';
#Restore archive logs as per sequence number.
restore archivelog from sequence 109056 until sequence 109118 thread 1;
release channel c1;
}
-- or --
RMAN> restore archivelog from logseq 200 until logseq 210;
-- or --
RMAN> RESTORE ARCHIVELOG FROM TIME 'SYSDATE-10' UNTIL TIME 'SYSDATE-1';
# recover TBPITR ON TARGET
RMAN> catalog start with '/home/oracle/rman/';
RMAN> run{
SET UNTIL TIME 'May 20 2014 14:00:00';
recover database skip tablespace TBS1,TBS2,...;
}
Note:
注意如果恢复过rm后再次恢复记的crosscheck datafilecopy。
另外注意RMAN的单个命令行有4000个字符的长度限制， 如果数据库表空间较多， 可能会超出限制，对于recover database skip tablespace TBS1,TBS2,…;超长，可以手动先把不需要的datafile offline. 再直接做recover database;
The maximum length for an RMAN command in a single line is 4000 characters. When a command exceeds this length, you can either split the command into multiple commands or use multiple lines for the command (use the Enter key to make the command span multiple lines).
References ORACLE DOC
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。