恢复表空间到某个时刻

xsbuqun

测试将表空间恢复到某个时刻

os:redhat5.5 64bit
rac :oracle 10.2.0.5
归档开启

一、用户u9先创建一张表 a1(b number,c sysdate); 表空间为users
插入数据后如下：
SQL> select * from a1 order by b;

         B C
---------- -----------------
        11 20130930 14:39:33
        12 20130930 14:39:44
        13 20130930 14:50:09
        14 20130930 14:50:19
        21 20130930 14:55:40
        22 20130930 14:55:49
        31 20130930 15:10:03
        32 20130930 15:10:13
        41 20130930 15:38:59
        42 20130930 15:39:11

获取次数的scn
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    9811054

使用rman做全量备份:
sql 'alter system checkpoint';
sql 'alter system switch logfile';
backup as backupset incremental level 0 FORMAT '/oracle/app/bakdata/Level0_%I_%d_%T_%u_s%s_p%p' database;
BACKUP ARCHIVELOG ALL FORMAT '/oracle/app/bakdata/archlog_%I_%d_%T_%u_s%s_p%p_AL' DELETE INPUT;

二、再插入两条数据
SQL> insert into a1 values(51,sysdate);

1 row created.

SQL> insert into a1 values(52,sysdate);

1 row created.

SQL> commit;
做数据库增量备份:
sql 'alter system checkpoint';
sql 'alter system switch logfile';
backup as backupset incremental level 1 cumulative FORMAT '/oracle/app/bakdata/Level1_%I_%d_%T_%u_s%s_p%p' database;
BACKUP ARCHIVELOG ALL FORMAT '/oracle/app/bakdata/archlog_%I_%d_%T_%u_s%s_p%p_AL' DELETE INPUT;

三、现在将表空间恢复到scn 为9811054的时刻
命令与日志如下：最后日志报错

RMAN>   recover tablespace users until scn 9811054 auxiliary destination '/oracle/app/tmpbak';
日志部分请见附件，最后出错信息如下：

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/30/2013 16:19:44
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

网上搜索，基本归因于是数据库没开归档，可是我的归档是开启的
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/funodb/archivelog/
Oldest online log sequence     117
Next log sequence to archive   118
Current log sequence           118

指点一下，什么原因，谢谢！

Maclean Liu(刘相兵

基于时间点的表空间恢复  TSPITR  ，你参照哪一个文档来做这个恢复？

ALLSTARS_ORACLE

Definition
Tablespace Point-in-Time Recovery (TSPITR) is a process to recover one or more
tablespaces (other than the SYSTEM tablespace) to a time that is prior to the rest of the
database. The following scenarios illustrate situations where TSPITR is needed to follow:

Recover a dropped or truncated table.

Recover a logically corrupted table.

Recover a dropped TABLESPACE.

Recover from incorrect batch job or other DML statement that has affected only a subset
of the database
This is involved clone a new instance/database either on the same server or different server
with the minimum required datafiles, recover it to the desired time, extract the object, and
then move it back to the primary.
Terminologies:
Terminology Description
TSPITR Tablespace Point-in-Time Recovery
Auxiliary Database
or Instance
A copy of the current database that is restored from a backup. Usually
reside on Auxiliary host
Recovery Set All the tablespaces on the primary (source) database that require pointin-
time recovery usually known as Transportable Tablspase Set (TTS)
Auxiliary Set All other files required for restoring the auxiliary database (System,
Sysaux, and Undo tablespaces)
Methods
User-managed TSPITR on the same server
1- Find out the file Ids and/or names for the Aux Set along with the TSs you
want to recover back in time from the primary
2- Restore Auxiliary Set (system, sysaux, and undos) to a temporary location on
the same server
3- Copy the primary’s init.ora file to the temporary location
4- Edit the copied init.ora file and modify all required init parameters for the new
instance






5- Startup the temporary instance / clone mode
$ rman target sys/oracle10g@test10g catalog rman/rman@rcvcat10
RMAN> run {
set newname for datafile 1 to '/u01/tspitr/system.299.708546925';
set newname for datafile 2 to '/u01/tspitr/undotbs1.262.725629799';
set newname for datafile 3 to '/u01/tspitr/sysaux.298.708546925';
set newname for datafile 6 to '/u01/tspitr/t2.318.725626177';
restore controlfile to '/u01/tspitr/ctrl01.ctl';
restore datafile 1, 2, 3, 6; }
SQL> col file_id head "FID" for 999
col TABLESPACE_NAME for a15
col FILE_NAME for a50
set linesize 100
break on tablespace_name
select TABLESPACE_NAME, FILE_ID,FILE_NAME
from dba_data_files
order by TABLESPACE_NAME;
control_files='/u01/tspitr/ctrl01.ctl'
db_unique_name='tspitr'
*_dump_dest (optional)
log_archive_dest_N (optional)
Disable archive log shipment in case of standby
$ export ORACLE_SID=tspitr
SQL> startup nomount pfile=/u01/tspitr/inittspitr.ora
SQL> alter database mount CLONE database;
6- Update the new (restored) controlfile to point to the new location/name of the
datafiles and online redologs (sample script to generate the sql commands)


























7- Recover the database






8- Open the database with resetlogs



9- Exp the desired object/s from the cloned DB, then import it back into the
primary DB
10- Temporary Instance/database can be cleaned at this point.
Rename the AUX SET and datafiles needed for the TSPITR procedure
SQL> alter database rename file '+DATA/test10g/datafile/system.299.708546925' to
'/u01/tspitr/system.299.708546925';
SQL> alter database rename file '+DATA/test10g/datafile/undotbs1.262.725629799' to
'/u01/tspitr/undotbs1.262.725629799';
SQL> alter database rename file '+DATA/test10g/datafile/sysaux.298.708546925' to
'/u01/tspitr/sysaux.298.708546925';
SQL> alter database rename file '+DATA/test10g/datafile/t2.318.725626177' to '/u01/tspitr/t2.318.725626177';
Online the new set of datafile:
SQL> alter database datafile '/u01/tspitr/system.299.708546925' online;
SQL> alter database datafile '/u01/tspitr/undotbs1.262.725629799' online;
SQL> alter database datafile '/u01/tspitr/sysaux.298.708546925' online;
SQL> alter database datafile'/u01/tspitr/t2.318.725626177' online;
Offline the unwanted datafiles:
SQL> ALTER DATABASE datafile '+DATA/test10g/datafile/users.296.708546925' offline drop;
SQL> ALTER DATABASE datafile '+DATA/test10g/datafile/t1.268.725626169' offline drop;
Rename the online redo logs:
SQL> ALTER DATABASE RENAME FILE '+DATA/test10g/onlinelog/group_1.294.708547123' to
'/u01/tspitr/group_1.294.708547123';
SQL> ALTER DATABASE RENAME FILE '+DATA/test10g/onlinelog/group_2.293.708547129' to
'/u01/tspitr/group_2.293.708547129';
SQL> ALTER DATABASE RENAME FILE '+DATA/test10g/onlinelog/group_3.292.708547133' to
'/u01/tspitr/group_3.292.708547133';
SQL> recover database using backup controlfile until time '2009-04-30:10:03:00';
Or you can use RMAN to recover
RMAN> run {
set until time "to_date('2009-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')";
recover database; }
SQL> alter database open resetlogs;

         



1- Ship copy of init.ora from the primary to the target server
2- Edit the copied init.ora file and modify all required init parameters for the new instance
3- Ship the backupsets to the target server, and create softlink,
4- Create soft link to the new backupset location if not the same as the source
5- Start the temp instance
6- Connect to the temp instance using RMAN with nocatalog option, restore the controlfil,
and mount the instance
scp inittspitr.ora sscnjlnx5:/home/oracle/ray/tspitr
control_files='/u02/oradata/tspitr/ctrl01.ctl'
db_unique_name='tspitr' (Not Needed)
*_dump_dest (optional)
log_archive_dest_N (optional)
Disable archive log shipment in case of standby
scp * sscnjlnx5:/home/oracle/ray/bkp
export ORACLE_SID=tet10g
startup nomount pfile=/home/oracle/ray/tspitr/inittspitr.ora
$ rman target / nocatalog
RMAN> restore controlfile to '/u02/oradata/tspitr/ctrl01.ctl' from
'/home/oracle/ray/bkp/c-959778546-20100731-00';
RMAN> alter database mount;
- Backup was exist at /u02/backup/test10g on the source host
- Backup was moved to /home/oracle/ray/bkp on the target host
ln -s /home/oracle/ray/bkp /u02/backup/test10g
7- Restore and recover the Aux/temp database
8- Exp the desired object/s from the cloned DB, then import it back into the primary DB.
9- Temporary Instance/database can be cleaned at this point.
run {
set until time "to_date('2008-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')";
set newname for datafile 1 to '/u02/oradata/tspitr/system.299.708546925';
set newname for datafile 2 to '/u02/oradata/tspitr/undotbs1.262.725629799';
set newname for datafile 3 to '/u02/oradata/tspitr/sysaux.298.708546925';
set newname for datafile 6 to '/u02/oradata/tspitr/t2.318.725626177';
restore datafile 1,2,3,6;
# Rename the AUX SET and datafiles needed for the TSPITR procedure
sql "alter database rename file ''+DATA/test10g/datafile/system.299.708546925'' to
''/u02/oradata/tspitr/system.299.708546925''";
sql "alter database rename file ''+DATA/test10g/datafile/undotbs1.262.725629799'' to
''/u02/oradata/tspitr/undotbs1.262.725629799''";
sql "alter database rename file ''+DATA/test10g/datafile/sysaux.298.708546925'' to
''/u02/oradata/tspitr/sysaux.298.708546925''";
sql "alter database rename file ''+DATA/test10g/datafile/t2.318.725626177'' to
''/u02/oradata/tspitr/t2.318.725626177''";
# Online the new set of datafile:
sql "alter database datafile ''/u02/oradata/tspitr/system.299.708546925'' online";
sql "alter database datafile ''/u02/oradata/tspitr/undotbs1.262.725629799'' online";
sql "alter database datafile ''/u02/oradata/tspitr/sysaux.298.708546925'' online";
sql "alter database datafile ''/u02/oradata/tspitr/t2.318.725626177'' online";
# Offline the unwanted datafiles:
sql "ALTER DATABASE datafile ''+DATA/test10g/datafile/users.296.708546925'' offline drop";
sql "ALTER DATABASE datafile ''+DATA/test10g/datafile/t1.268.725626169'' offline drop";
# Rename the online redo logs:
sql "ALTER DATABASE RENAME FILE ''/u01/tspitr/group_1.294.708547123'' to
''/u02/oradata/tspitr/group_1.294.708547123''";
sql "ALTER DATABASE RENAME FILE ''/u01/tspitr/group_2.293.708547129'' to
''/u02/oradata/tspitr/group_2.293.708547129''";
sql "ALTER DATABASE RENAME FILE ''/u01/tspitr/group_3.292.708547133'' to
''/u02/oradata/tspitr/group_3.292.708547133''";
# Recover the Aux database until specified time skipping all unwanted TSs, then open:
recover database skip forever tablespace t1, users;
alter database open resetlogs; }
Using RMAN
RMAN carries out the following steps to perform TSPITR:
a- RMAN automatically create auxiliary instance, starts (nomount) it and connects to it
b- Offline the TTS in the target DB
c- Restore the controlfile to the Aux. Inst.
d- Restore the Zux set to the Zux instance, and restore the TTS to the original location
or to a location you specify
e- Recover the Aux instance ti the specified time, open it with reset log options
f- Export the dictionary metadata for TTS from the Aux instance, then shut it down.
g- Switch the target db controlfile to point to the TTS
h- Import the metadata
i- Clean the Aux. Instance and Aux set
 

32  


RMAN manage and control all aspects of the transportable TS process. However this
method leaves the Aux. Destination requirements for you to determine the location
RMAN should use for Aux. Sets. Optionally you can also have a little control over the
datafiles naming and location. Recovery set (bug 4090065)
In this method Rman client use the following init parameters to create the Aux instance
1- Connect to rman and recover the TTS
$ rman target sys/oracle10g@test10g catalog rman/rman@rcvcat10
Note: RMAN uses the configured channels of the target database to configure the
auxiliary instance
RMAN> run {
recover tablespace t1, t2
until time "to_date('2008-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')"
auxiliary destination '/u02/auxdest'; }
Or
RMAN> run {
set newname for datafile 5 to '/tmp/t1.dbf';
set newname for datafile 6 to '/tmp/t2.dbf';
set newname for datafile 1 to '/tmp/system01.dbf';
recover tablespace t1, t2
until time "to_date('2008-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')"
auxiliary destination '/u02/auxdest'; }
initialization parameters used for automatic instance:
db_name=TEST10G -- Same as the source db
compatible=10.2.0.3.0 -- Same as the source db
db_block_size=8192 -- Same as the source db
db_files=200 -- Same as the source db
db_unique_name=tspitr_TEST10G_pdts -- Generated, based on the DB_NAME at the Source
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u02/auxdest -- same as auxiliary destination argument
control_files=/u02/auxdest/cntrl_tspitr_TEST10G_pdts.f -- Generated, based on the DB_NAME at the Source
2- Backup all TTS, and online them.
 

  !32  


To use this method, “SET AUXILIARY INSTANCE PARAMETER FILE TO ‘/?/?..’” muse be
used in the run block to tell RMAN client what parameter to use to create the AUX instance,
otherwise RMAN Client looks for initialization parameters in
$OH/rdbms/admin/params_auxint.ora (default name/location). If not found RMAN use
memory init parameters (with default values). See Fully automated TSPITR
In this method:

You can use Aux init parm file to have a control over some init parameters .

You can use combination of the following to control DFs naming and location:
o SET NEWNAME (1)
o CONFIGURE AUXNAME (2)
CONFIGURE AUXNAME FOR DATAFILE ‘n’ TO auxname_’n’;
o DB_FILE_NAME_CONVERT (3)
o AUXILIARY DESTINATION (4)
I.e

          
"!#
In this method, you have full control over the Aux instance.
1- Prepare your Aux instance to be used by RMAN client
a. Create password file
b. Create init parameter file
i. DB_NAME
ii. DB_UNIQUE_NAME
iii. CONTROL_FILES
iv. DB_FILE_NAME_CONVERT
v. LOG_FILE_NAME_CONVERT
vi. REMOTE_LOGIN_PASSWORDFILE
vii. COMPATIBLE
viii. DB_BLOCK_SIZE
a. Setup the network connectivity
2- Start and connect to the Aux instance, and
RMAN> backup tablespace t1,t2;
RMAN> sql "alter tablespace t1 online";
RMAN> sql "alter tablespace t2 online";
RMAN> run {
set auxiliary instance parameter file to '/tmp/auxinstparams.ora';
set newname for datafile 1 to '/tmp/system01.dbf';
recover tablespace t1, t2
until time "to_date('2008-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')"
auxiliary destination '/u02/auxdest'; }
SQL> startup nomount pfile=/tmp/inittspitr.ora
3- Perform TSPITR
4- Backup all TTS, and online them.
RMAN> backup tablespace t1,t2;
RMAN> sql "alter tablespace t1 online";
RMAN> sql "alter tablespace t2 online";
$ rman target sys/oracle10g@test10g catalog rman/rman@rcvcat10
RMAN> run {
# Specify NEWNAMES for recovery set datafiles (optional)
set newname for datafile 5 to '/tmp/t1.dbf';
set newname for datafile 6 to '/tmp/t2.dbf';
# Specified newnames for some of the auxiliary set (optional)
set newname for datafile 1 to '/tmp/system01.dbf';.
# Specified the disk and/or SBT channels to use (as many as needed)
allocate auxiliary channel c1 device type disk;
recover tablespace t1, t2
until time "to_date('2008-04-30:10:03:00','YYYY-MON-DD:HH24:MI:SS')"= }
Script$
!rm up_new_ctrl.lst
set echo off
set feedback off
SET VERIFY OFF;
set heading off
set pagesize 10000
accept dest default '/tmp' prompt 'Enter the new or Aux datafile/Logfile distination? </tmp> :'
spool up_new_ctrl
select 'alter database rename file '
||''''||name||''' to '||''''
||'&dest'||'/'||substr(name,instr(name,'/',-1)+1,vsize(name)-vsize(substr(name,1,instr(name,'/',-
1)))+1)||''';'
from v$datafile
where FILE# in (1,2,3,6);
select 'alter database datafile '||''''||'&dest'||'/'||substr(name,instr(name,'/',-1)+1,vsize(name)-
vsize(substr(name,1,instr(name,'/',-1)))+1)||''' online ;'
from v$datafile
where FILE# in (1,2,3,6);
select 'alter database datafile '||''''||name||' offline drop'' ;'
from v$datafile
where FILE# not in (1,2,3,6);
select 'alter database rename file '
||''''||member||''' to '||''''
||'&dest'||'/'||substr(member,instr(member,'/',-1)+1,vsize(member)-
vsize(substr(member,1,instr(member,'/',-1)))+1)||''';'
from v$logfile;
spool off

Maclean Liu(刘相兵

3楼已提供 TSPITR的正确方法， 此贴结贴