關于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


PURPOSE
-------

The following is a working example of RMAN TSPITR and was performed using 
Oracle 8.1.7.0.0 on Microsoft Windows 2000. 
 
SCOPE & APPLICATION
-------------------

Intended audience is for DBA's using RMAN as a backup solution.


RMAN Tablespace Point in Time Recovery Example
----------------------------------------------
 
RMAN Tablespace Point in Time Recovery Example

The following is a working example of RMAN TSPITR and was performed using 
Oracle 8.1.7.0.0 on Microsoft Windows 2000. 

This example assumes:

1. A tns alias is used to connect to the RMAN catalog and target databases only.
   We use a local connection for the auxiliary instance. You can use a tns alias
   to connect to the auxiliary database if you wish, but it’s not necessary. 

2. The production instance database files are located on 
   D:\ORACLE8I\ORA817\ORADATA\ORCL817.

3. The production instance log files are located on 
   D:\ORACLE8I\ORA817\ORADATA\ORCL817.

4. The auxiliary instance files are located on D:\ORACLE8I\ORA817\ADMIN\AUX1.
   We have chosen to store all auxiliary files in one location to help simplify
   the process. 

5. RMAN backups are on disk; so all channels are allocated to disk.

====================================
I. Create the auxiliary initSID.ora.
====================================

1. The auxiliary instance MUST reside on the same machine as the target database. 

2. Precreate one directory for all of the auxiliary files to be stored in. 
   This example uses D:\ORACLE8I\ORA817\ADMIN\AUX1.
  
3. Copy the initSID.ora from the target database to 
   D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora.

4. Modify the initAUX1.ora file to include the following:

LOCK_NAME_SPACE=AUX1

DB_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817", “D:\ORACLE8I\ORA817\ADMIN\AUX1")

LOG_FILE_NAME_CONVERT=("D:\ORACLE8I\ORA817\ORADATA\ORCL817","D:\ORACLE8I\ORA817\ADMIN\AUX1") 

Note: Using LOCK_NAME_SPACE allows us to have two databases with the same name 
      on the same NT machine. 

Note: Using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT converts one string 
      to another. Database files in D:\ORACLE8I\ORA817\ORADATA\ORCL817 will
      retain their same name, but will be restored to D:\ORACLE8I\ORA817\ADMIN\AUX1.
      You could also use "configure auxname" in RMAN to perform. the datafile
      conversions.

5. Modify the initAUX1.ora file to change the controlfile location: 

control_files = ("d:\oracle8i\ora817\admin\AUX1\control01.ctl")

6. Modify the initAUX1.ora file to comment out the following:

#log_archive_start = true
#log_archive_dest_1 = “location=…
#log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

=================================
II. Create the auxiliary instance
=================================

1. From a command prompt type the following:

	C:\> oradim -new -sid AUX1 -intpwd oracle -startmode manual -pfile D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora

2. Verify the instance has been created in “Services” and verify it is started.

=================================
III. Start the auxiliary instance
=================================

From a command prompt:

1. Set ORACLE_SID=AUX1
2. Start sqlplus and connect internal
3. Startup nomount pfile=$D:\ORACLE8I\ORA817\ADMIN\AUX1\initAUX1.ora
4. After the instance is successfully started, exit the session.

======================================================================
IV. Connect to the catalog, target, and auxiliary databases using RMAN
======================================================================

1. Make sure the target database is open.
2. Make sure the auxiliary instance is started and in no mount mode.

From a command prompt:

3. Set ORACLE_SID=AUX1 
4. Set NLS_DATE_FORMAT=MON-DD-YYYY HH:MI:SS
5. Connect to RMAN:

   rman target sys/password@target catalog rman/rman@rcat auxiliary  / trace=d:\tspitr.log  

(This will connect you to the target, catalog, and auxiliary database. 
 It will also log the TSPITR session to "d:\tspitr.log")

===============
V. Start TSPITR
===============

run {
ALLOCATE AUXILIARY CHANNEL A1 TYPE DISK;
ALLOCATE CHANNEL c1 TYPE DISK;
recover tablespace users until time "TO_DATE('MAR-05-2002 11:27:00', 'MON-DD-YYYY HH:MI:SS')";
}

========================
VI. Testing the Recovery
========================

1. Connect as sys 
2. Put the recovered tablespace online
3. Verify the object(s) exists.



RELATED DOCUMENTS
-----------------

Oracle8i Backup and Recovery Guide
Oracle8i Recovery Manager User's Guide and Reference

以下是我RECOVER時的日志

RMAN> run{
2> allocate auxiliary channel a1 type disk;
3> allocate channel c1 type disk;
4> recover tablespace ttaom until time "to_date('2010-12-13 16:02:17','yyyy-mm-dd hh24:mi:ss')";
5> }

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博客

Oracle 8i/9i表空間不完整恢復

最新文章