Oracle 8i/9i表空間不完整恢復-优快云博客

關于oracle 表空間不完全恢復

方法一 oracle自動表空間不完全恢復

8i\9i的表空間不完全恢復比較復雜﹐需要用戶建立輔助庫的初始參數文件

10g 的auto tspitr比較更加自動化﹐只需要用戶在recover命令中指定輔助庫的路徑即可﹐參考另外一篇日志

原理都是一樣﹐不需要用戶招待restore操作﹐也可以在RAC環境下。

oracle自動將system,undo表空間恢復到auxiliary庫的路徑和將需要恢復的表空間恢復到原有路徑下﹐然后執行輔助庫中的不完全恢復﹐然后將整個恢復出來的表空間導出﹐再全部導入到原生產DB下

恢復過程中﹐不會影響數據中其它表空間的操作﹐僅需要恢復的表空間會自動offline

需要注意的是﹐恢復之前﹐最好先備份原備份的備份集文件和control file備份﹐因為操作renew datafile/switch datafile操作﹐恢復完成后會修改備份集﹐導致原有備份集中的該表空間不可用

恢復之后﹐也要對數據庫做全備﹐總之﹐備份很重要

方法二用戶手工不完全恢復

這里也有兩種方式可以實現﹐通常用于僅表空間中的表的恢復﹐也可以實現表空間的不完全恢復﹕

1.將所有文件復制到另外一台機﹐用相同的環境﹐然后重建控制文件﹐將不需要的表空間剃掉﹐重建controlfile時只保留system/undo和需要恢復的表空間﹐然后只對這些表空間手工restore/TSPITR﹐將后將恢復出來的表空間中的數據exp﹐然后再imp到原庫中。

2.第二種方式與上一種方式﹐只是不需要重建控制文件﹐先將不需要的表空間offline﹐然后在restore/recover步驟用skip關鍵字將不需要恢復的表空跳過﹐最后也將恢復出來的數據導入原庫中。

以下8i（我實驗在9i）采用oracle自動表空間不完全恢復的方法轉至metalink

RMAN Tablespace Point in Time Recovery Example [ID 180436.1]

修改时间 08-DEC-2010 类型 BULLETIN 状态 PUBLISHED

using target database controlfile instead of recovery catalog
allocated channel: a1
channel a1: sid=9 devtype=DISK

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

Starting recover at 13-DEC-10

printing stored script. Memory Script
{
# set the until clause
set until time "to_date('2010-12-13 16:02:17','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;';
}
executing script. Memory Script

executing command: SET until clause

Starting restore at 13-DEC-10

channel a1: starting datafile backupset restore
channel a1: restoring controlfile
output filename=/u01/oracle/oradata/aux1/control01.ctl
channel a1: restored backup piece 1
piece handle=/u01/oracle/102/dbs/02lvfd48_1_1 tag=TAG20101213T155136 params=NULL
channel a1: restore complete
Finished restore at 13-DEC-10

replicating controlfile
input filename=/u01/oracle/oradata/aux1/control01.ctl

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

printing stored script. Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2010-12-13 16:02:17','yyyy-mm-dd hh24:mi:ss')";
# set a destination filename for restore
set newname for datafile 1 to
"/u01/oracle/oradata/aux1/system01.dbf";
# set a destination filename for restore
set newname for datafile 2 to
"/u01/oracle/oradata/aux1/undotbs01.dbf";
# set a destination filename for restore
set newname for datafile 3 to
"/u01/oracle/102/dbs/ttaom.dbf";
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 3;
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 3 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TTAOM", "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

executing command: SET NEWNAME

Starting restore at 13-DEC-10

channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/aux1/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/aux1/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/102/dbs/ttaom.dbf
channel a1: restored backup piece 1
piece handle=/u01/oracle/102/dbs/02lvfd48_1_1 tag=TAG20101213T155136 params=NULL
channel a1: restore complete
Finished restore at 13-DEC-10

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 3 online

Starting recover at 13-DEC-10

starting media recovery

archive log thread 1 sequence 29 is already on disk as file /u01/oracle/102/dbs/arch1_29.dbf
archive log thread 1 sequence 30 is already on disk as file /u01/oracle/102/dbs/arch1_30.dbf
archive log thread 1 sequence 31 is already on disk as file /u01/oracle/102/dbs/arch1_31.dbf
archive log thread 1 sequence 32 is already on disk as file /u01/oracle/102/dbs/arch1_32.dbf
archive log filename=/u01/oracle/102/dbs/arch1_29.dbf thread=1 sequence=29
archive log filename=/u01/oracle/102/dbs/arch1_30.dbf thread=1 sequence=30
archive log filename=/u01/oracle/102/dbs/arch1_31.dbf thread=1 sequence=31
archive log filename=/u01/oracle/102/dbs/arch1_32.dbf thread=1 sequence=32
media recovery complete
Finished recover at 13-DEC-10

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=
TTAOM file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/oracle@wind as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TTAOM online";
sql "alter tablespace TTAOM offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing script. Memory Script

Export: Release 9.2.0.4.0 - Production on Mon Dec 13 17:51:18 2010

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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TTAOM ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TTAOM
. 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 Mon Dec 13 17:51:33 2010

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 US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TTAOM's objects into TTAOM
. . importing table "TTAOM"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TTAOM online

sql statement: alter tablespace TTAOM offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Finished recover at 13-DEC-10
released channel: c1

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

转载于:http://blog.itpub.net/7608831/viewspace-681675/