A] TSPITR Too easy
|
Goal
|
Tablespace Point In Time
Recovery
|
|
Version
|
10gR2
|
|
Context
|
Media DISK / NO CATALOG
|
|
Log as
|
/ AS SYSDBA
|
We need to
bring back in time a schema’s Data ( after a logical corruption for instance,
or wrong drop table … ). RMAN is THE tool for this, provided that the data are
located on isolated tablespaces.
10g RMAN
fully automatises the process ( vs. 9i and the fastidious step of auxiliary
instance setup ) :
Only one
RMAN command is needed !
0] Limitations of RMAN TSPITR
Reminder for
myself :
!! IT WILL BRING BACK ONLY
DATA !!
From [Metallica ID 304305.1]
This document discusses the limitations of
TSPITR using RMAN in 9i and 10g
- You cannot recover dropped
tablespaces.
- You cannot recover a renamed tablespace to a point in time before
it was renamed. If you try to perform a TSPITR to an SCN
earlier than the rename operation, RMAN cannot find the new tablespace
name in the repository as of that earlier SCN (because the tablespace did
not have that name at that SCN). In this situation, you must recover the
entire database to a point in time before the tablespace was renamed. The
tablespace will be found under the name it had at that earlier time.
- You cannot recover a tablespace that has been dropped and
re-created with the same name.
- You cannot recover tables without their associated constraints, or
constraints without the associated table
·
You cannot use TSPITR to
recover any of the following:
- Replicated master tables
- Partial tables (for example, if you perform RMAN TSPITR on
partitioned tables and spread partitions across multiple
tablespaces, then you must recover all tablespaces which include
partitions of the table.)
- Tables with VARRAY columns, nested tables, or external files
- Snapshot logs and snapshot tables
- Tablespaces containing undo or rollback segments
- Tablespaces that contain objects owned by SYS, including rollback
segments
- If a datafile was added after the point to which RMAN is
recovering, an empty datafile by the same name will be
included in the tablespace after RMAN TSPITR.
- TSPITR will not recover query optimizer statistics for
recovered objects. You must gather new statistics after the TSPITR.
- Assume that you run TSPITR on a tablespace, and then bring the
tablespace online at time t. Backups of the tablespace created
before time t are no longer usable for recovery with a current control
file. You cannot run TSPITR again on this tablespace to recover it to any
time less than or equal to time t, nor can you use the current control
file to recover the database to any time less than or equal to t.
Therefore, you must back up the tablespace as soon as TSPITR is complete.
====================================
See RCAT post
1] Test-Case
SQL>
create table auc.t1 ( a int ) tablespace auc_dat ;
Table
created.
SQL>
create index auc.t1_i1 on auc.t1 ( a ) tablespace auc_idx ;
Index
created.
SQL>
insert into auc.t1 values ('10') ;
1 row
created.
SQL>
insert into auc.t1 values ('20') ;
1 row
created.
SQL>
insert into auc.t1 values ('30') ;
1 row created.
SQL>
commit ;
SQL>
select * from auc.t1 ;
A
----------
10
20
30
WRONG INSERT
SQL>
!date
Fri Jan 6 18:08:15 GMT+01:00 2012
SQL>
insert into auc.t1 values ('40') ;
1 row
created.
SQL>
commit ;
Commit
complete.
SQL>
select * from auc.t1 order by 1 ;
A
----------
10
20
30
40
SQL> -- '40'
wrong insert -> TSPITR Jan 6 2012 18:05:00
>>> TSPITR of AUC_DAT & AUC_IDX
tablespaces <<<
2] Pre-requisite on target server
- Instance
Open
- Auxiliary
Instance Directory created
$
mkdir /rman/tspitr
- Tablespaces
to be restored are « self-contained » ; this check should
return No Row :
Ascertain and resolve dependencies on the Primary
select * from sys.ts_pitr_check
where (ts1_name in ('AUC_DAT','AUC_IDX') and ts2_name not in ('AUC_DAT','AUC_IDX'))
or (ts1_name not in ('AUC_DAT','AUC_IDX') and ts2_name in ('AUC_DAT','AUC_IDX'))
- RMAN
Dumps Directory : /rman
$ ls
-ltr /rman
total
111681976
drwxr-xr-x 2 oracle
dba 256 Dec 22 15:17 tspitr
-rw-r----- 1 oracle
dba 19906560 Dec 30 22:21
c-2299686464-20111230-00
-rw-r----- 1 oracle
dba 19906560 Dec 31 22:23
c-2299686464-20111231-00
-rw-r----- 1 oracle
dba 18768896 Jan 01 22:01
ar_F_<DB_NAME>_s5532_Ucsmvn1ds_1_1_20120101
-rw-r----- 1 oracle
dba 35801600 Jan 01 22:01
ar_F_<DB_NAME>_s5531_Ucrmvn1ds_1_1_20120101
-rw-r----- 1 oracle
dba 12811264 Jan 01 22:01
ar_F_<DB_NAME>_s5533_Uctmvn1e1_1_1_20120101
-rw-r-----
1 oracle dba
9356800 Jan 01 22:01 ar_F_<DB_NAME>_s5534_Ucumvn1e3_1_1_20120101
-rw-r----- 1 oracle
dba 60137472 Jan 01 22:01
ar_F_<DB_NAME>_s5530_Ucqmvn1ds_1_1_20120101
-rw-r----- 1 oracle
dba 68181504 Jan 01 22:01
ar_F_<DB_NAME>_s5529_Ucpmvn1ds_1_1_20120101
-rw-r----- 1 oracle
dba 7122624512 Jan 01 22:29
db_F_<DB_NAME>_s5536_Ud0mvn1ef_1_1_20120101
-rw-r----- 1 oracle
dba 7564312576 Jan 01 22:36
db_F_<DB_NAME>_s5535_Ucvmvn1ef_1_1_20120101
-rw-r----- 1
oracle dba 10304585728 Jan 01 22:41 db_F_<DB_NAME>_s5537_Ud1mvn1eg_1_1_20120101
-rw-r----- 1 oracle
dba 11967062016 Jan 01 22:44
db_F_<DB_NAME>_s5538_Ud2mvn1eg_1_1_20120101
-rw-r----- 1 oracle
dba 9348325376 Jan 01 23:07
db_F_<DB_NAME>_s5539_Ud3mvn32a_1_1_20120101
-rw-r----- 1 oracle
dba 326144 Jan 01 23:07
ar_F_<DB_NAME>_s5542_Ud6mvn59l_1_1_20120101
-rw-r----- 1 oracle
dba 1714176 Jan 01 23:07
ar_F_<DB_NAME>_s5541_Ud5mvn59l_1_1_20120101
-rw-r----- 1
oracle dba 1774592 Jan 01 23:07 ar_F_<DB_NAME>_s5540_Ud4mvn59l_1_1_20120101
-rw-r----- 1 oracle
dba 19906560 Jan 01 23:07
c-2299686464-20120101-00
-rw-r----- 1 oracle
dba 19906560 Jan 02 22:30
c-2299686464-20120103-00
-rw-r----- 1 oracle
dba 19906560 Jan 03 22:30
c-2299686464-20120103-01
-rw-r----- 1 oracle
dba 19906560 Jan 04 22:29
c-2299686464-20120104-00
-rw-r----- 1 oracle
dba 1046595072 Jan 05 22:06
ar_I_<DB_NAME>_s5586_Uein01j1n_1_1_20120105
-rw-r----- 1 oracle
dba 1245743616 Jan 05 22:07
ar_I_<DB_NAME>_s5585_Uehn01j1n_1_1_20120105
-rw-r----- 1 oracle
dba 1437286400 Jan 05 22:07
ar_I_<DB_NAME>_s5583_Uefn01j1n_1_1_20120105
-rw-r----- 1 oracle
dba 1606221824 Jan 05 22:08
ar_I_<DB_NAME>_s5584_Uegn01j1n_1_1_20120105
-rw-r----- 1 oracle
dba 633181184 Jan 05 22:08
ar_I_<DB_NAME>_s5587_Uejn01j7t_1_1_20120105
-rw-r----- 1 oracle
dba 216072192 Jan 05 22:22
db_I_<DB_NAME>_s5591_Uenn01jbu_1_1_20120105
-rw-r----- 1 oracle
dba 871587840 Jan 05 22:23
db_I_<DB_NAME>_s5589_Ueln01jbt_1_1_20120105
-rw-r----- 1 oracle
dba 414228480 Jan 05 22:23
db_I_<DB_NAME>_s5588_Uekn01jbs_1_1_20120105
-rw-r----- 1 oracle
dba 2234712064 Jan 05 22:25
db_I_<DB_NAME>_s5590_Uemn01jbt_1_1_20120105
-rw-r----- 1 oracle
dba 63102976 Jan 05 22:28
db_I_<DB_NAME>_s5592_Ueon01k4t_1_1_20120105
-rw-r----- 1 oracle
dba 98483200 Jan 05 22:29
ar_I_<DB_NAME>_s5594_Ueqn01khj_1_1_20120105
-rw-r----- 1 oracle
dba 100597248 Jan 05 22:29
ar_I_<DB_NAME>_s5593_Uepn01khj_1_1_20120105
-rw-r----- 1 oracle
dba 19906560 Jan 05 22:29
c-2299686464-20120105-00
$
3] Run Scripts
3.1 Preview
|
No
|
Operation
|
Scope
|
Recovery Catalog
|
Information
needed
|
Archivelog
|
|
1.
|
TSPITR
Preview
|
Tablespaces
|
No
|
Until Time
|
NA
|
Are all the
needed backups available ? Run RMAN Preview command to find out if the
dumps are on Disk :
$ cat rma_rts_pit_<DB_NAME>_noc_disk_preview.scr
run {
set until time "to_date('Jan 6 2012 18:05:00','MON
DD YYYY HH24:MI:SS')" ;
#set until logseq 319097 ; # logseq max + 1
RESTORE tablespace AUC_DAT , AUC_IDX , SYSTEM ,
"UNDO" , SYSAUX , XDB PREVIEW;
}
$ rman
target / @rma_rts_pit_<DB_NAME>_noc_disk_preview.scr |grep 'Piece Name'
|awk '{ print $3}' | xargs ls -l
-rw-r----- 1 oracle
dba 7564312576 Jan 01 22:36 /rman/db_F_<DB_NAME>_s5535_Ucvmvn1ef_1_1_20120101
-rw-r----- 1 oracle
dba 7122624512 Jan 01 22:29 /rman/db_F_<DB_NAME>_s5536_Ud0mvn1ef_1_1_20120101
-rw-r----- 1 oracle
dba 10304585728 Jan 01 22:41 /rman/db_F_<DB_NAME>_s5537_Ud1mvn1eg_1_1_20120101
-rw-r----- 1 oracle
dba 11967062016 Jan 01 22:44 /rman/db_F_<DB_NAME>_s5538_Ud2mvn1eg_1_1_20120101
-rw-r----- 1 oracle
dba 9348325376 Jan 01 23:07 /rman/db_F_<DB_NAME>_s5539_Ud3mvn32a_1_1_20120101
-rw-r----- 1 oracle
dba 100597248 Jan 05 22:29 /rman/ar_I_<DB_NAME>_s5593_Uepn01khj_1_1_20120105
-rw-r----- 1 oracle
dba 98483200 Jan 05 22:29 /rman/ar_I_<DB_NAME>_s5594_Ueqn01khj_1_1_20120105
-rw-r----- 1 oracle
dba 414228480 Jan 05 22:23 /rman/db_I_<DB_NAME>_s5588_Uekn01jbs_1_1_20120105
-rw-r----- 1 oracle
dba 871587840 Jan 05 22:23 /rman/db_I_<DB_NAME>_s5589_Ueln01jbt_1_1_20120105
-rw-r----- 1 oracle
dba 2234712064 Jan 05 22:25 /rman/db_I_<DB_NAME>_s5590_Uemn01jbt_1_1_20120105
$
3.2 TSPITR
|
No
|
Operation
|
Scope
|
Recovery
Catalog
|
Information
needed
|
Archivelog
|
|
2.
|
TSPITR
|
Tablespaces
|
No
|
Until Time
|
YES
|
$ cat rma_rts_pit_<DB_NAME>_noc_disk.scr
run {
# 10g fully automated TSPITR
recover tablespace AUC_DAT , AUC_IDX until time
"to_date('Jan 6 2012 18:05:00','MON DD YYYY HH24:MI:SS')" auxiliary
destination '/rman/tspitr' ;
}
$ export
NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'
$ rman
target / @rma_rts_pit_<DB_NAME>_noc_disk.scr |tee ../log/rma_rts_pit_<DB_NAME>_noc_disk_060112.log
Recovery
Manager: Release 10.2.0.5.0 - Production on Fri Jan 6 18:42:29 2012
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
connected
to target database: <DB_NAME> (DBID=2299686464)
RMAN>
run {
2>
# 10g fully automated TSPITR
3>
recover tablespace AUC_DAT , AUC_IDX until time "to_date('Jan 6 2012
18:05:00','MON DD YYYY HH24:MI:SS')" auxiliary destination '/rman/tspitr'
;
4>
}
5>
Starting
recover at JAN 06 2012 18:42:30
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=1117 devtype=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: sid=807 devtype=DISK
allocated
channel: ORA_DISK_3
channel
ORA_DISK_3: sid=1155 devtype=DISK
allocated
channel: ORA_DISK_4
channel
ORA_DISK_4: sid=1350 devtype=DISK
RMAN-05026:
WARNING: presuming following set of tablespaces applies to specified point in
time
List
of tablespaces expected to have UNDO segments
tablespace
SYSTEM
tablespace
UNDO
Creating
automatic instance, with SID='qfEp'
initialization
parameters used for automatic instance:
db_name=<DB_NAME>
compatible=10.2.0.5
db_block_size=8192
db_files=500
db_unique_name=tspitr_<DB_NAME>_qfEp
sga_target=180M
processes=50
#No
auxiliary parameter file used
db_create_file_dest=/rman/tspitr
control_files=/rman/tspitr/cntrl_tspitr_<DB_NAME>_qfEp.f
starting
up automatic instance <DB_NAME>
Oracle
instance started
Total
System Global Area 188743680 bytes
Fixed
Size 2095064 bytes
Variable
Size 75499560 bytes
Database
Buffers 104857600 bytes
Redo
Buffers 6291456 bytes
Automatic
instance created
contents
of Memory Script:
{
# set
the until clause
set
until time "to_date('Jan 6 2012
18:05:00','MON DD YYYY HH24:MI:SS')";
#
restore the controlfile
restore
clone controlfile;
#
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
Memory Script
executing
command: SET until clause
Starting
restore at JAN 06 2012 18:42:35
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: sid=47 devtype=DISK
allocated
channel: ORA_AUX_DISK_2
channel
ORA_AUX_DISK_2: sid=46 devtype=DISK
allocated
channel: ORA_AUX_DISK_3
channel
ORA_AUX_DISK_3: sid=45 devtype=DISK
allocated
channel: ORA_AUX_DISK_4
channel
ORA_AUX_DISK_4: sid=44 devtype=DISK
channel
ORA_AUX_DISK_1: starting datafile backupset restore
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: reading from backup piece /rman/c-2299686464-20120105-00
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/rman/c-2299686464-20120105-00 tag=TAG20120105T222924
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output
filename=/rman/tspitr/cntrl_tspitr_<DB_NAME>_qfEp.f
Finished
restore at JAN 06 2012 18:42:42
sql
statement: alter database mount clone database
sql
statement: alter system archive log current
sql
statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents
of Memory Script:
{
#
generated tablespace point-in-time recovery script
# set
the until clause
set
until time "to_date('Jan 6 2012
18:05:00','MON DD YYYY HH24:MI:SS')";
plsql
<<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed,
-01539);
begin
sqlstatement := 'alter tablespace '|| 'AUC_DAT' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end;
>>>;
plsql
<<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed,
-01539);
begin
sqlstatement := 'alter tablespace '|| 'AUC_IDX' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end;
>>>;
# set
an omf destination filename for restore
set
newname for clone datafile 1 to new;
# set
an omf destination filename for restore
set
newname for clone datafile 2 to new;
# set
an omf destination tempfile
set
newname for clone tempfile 2 to new;
# set
a destination filename for restore
set
newname for datafile 260 to
"/data/<DB_NAME>/auc_dat.dbf";
# set
a destination filename for restore
set
newname for datafile 262 to
"/data/<DB_NAME>/auc_idx.dbf";
#
rename all tempfiles
switch
clone tempfile all;
#
restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore
clone datafile 1, 2, 260, 262;
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 260
online";
#online
the datafiles restored or flipped
sql
clone "alter database datafile 262
online";
#
make the controlfile point at the restored datafiles, then recover them
recover
clone database tablespace
"AUC_DAT", "AUC_IDX", "SYSTEM",
"UNDO" delete archivelog;
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
Memory Script
executing
command: SET until clause
sql
statement: alter tablespace AUC_DAT offline for recover
sql
statement: alter tablespace AUC_IDX offline for recover
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
renamed
temporary file 2 to /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_temp_%u_.tmp
in control file
Starting
restore at JAN 06 2012 18:42:53
using
channel ORA_AUX_DISK_1
using
channel ORA_AUX_DISK_2
using
channel ORA_AUX_DISK_3
using
channel ORA_AUX_DISK_4
channel
ORA_AUX_DISK_1: starting datafile backupset restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring
datafile 00002 to /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_undo_%u_.dbf
channel
ORA_AUX_DISK_1: reading from backup piece /rman/db_F_<DB_NAME>_s5536_Ud0mvn1ef_1_1_20120101
channel
ORA_AUX_DISK_2: starting datafile backupset restore
channel
ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring
datafile 00260 to /data/<DB_NAME>/auc_dat.dbf
channel
ORA_AUX_DISK_2: reading from backup piece /rman/db_F_<DB_NAME>_s5535_Ucvmvn1ef_1_1_20120101
channel
ORA_AUX_DISK_3: starting datafile backupset restore
channel
ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
restoring
datafile 00262 to /data/<DB_NAME>/auc_idx.dbf
channel
ORA_AUX_DISK_3: reading from backup piece /rman/db_F_<DB_NAME>_s5538_Ud2mvn1eg_1_1_20120101
channel
ORA_AUX_DISK_4: starting datafile backupset restore
channel
ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
restoring
datafile 00001 to /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_system_%u_.dbf
channel
ORA_AUX_DISK_4: reading from backup piece /rman/db_F_<DB_NAME>_s5539_Ud3mvn32a_1_1_20120101
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/rman/db_F_<DB_NAME>_s5536_Ud0mvn1ef_1_1_20120101
tag=TAG20120101T220149
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:25
channel
ORA_AUX_DISK_4: restored backup piece 1
piece
handle=/rman/db_F_<DB_NAME>_s5539_Ud3mvn32a_1_1_20120101
tag=TAG20120101T220149
channel
ORA_AUX_DISK_4: restore complete, elapsed time: 00:10:20
channel
ORA_AUX_DISK_2: restored backup piece 1
piece
handle=/rman/db_F_<DB_NAME>_s5535_Ucvmvn1ef_1_1_20120101
tag=TAG20120101T220149
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:13:25
channel
ORA_AUX_DISK_3: restored backup piece 1
piece
handle=/rman/db_F_<DB_NAME>_s5538_Ud2mvn1eg_1_1_20120101
tag=TAG20120101T220149
channel
ORA_AUX_DISK_3: restore complete, elapsed time: 00:15:60
Finished
restore at JAN 06 2012 18:58:54
datafile
1 switched to datafile copy
input
datafile copy recid=5 stamp=771879534 filename=/rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_system_7jgdny9d_.dbf
datafile
2 switched to datafile copy
input
datafile copy recid=6 stamp=771879534 filename=/rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_undo_7jgdny8d_.dbf
sql
statement: alter database datafile 1
online
sql
statement: alter database datafile 2
online
sql
statement: alter database datafile 260
online
sql
statement: alter database datafile 262
online
Starting
recover at JAN 06 2012 18:58:56
using
channel ORA_AUX_DISK_1
using
channel ORA_AUX_DISK_2
using
channel ORA_AUX_DISK_3
using
channel ORA_AUX_DISK_4
channel
ORA_AUX_DISK_2: starting incremental datafile backupset restore
channel
ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00262: /data/<DB_NAME>/auc_idx.dbf
destination
for restore of datafile 00001: /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_system_7jgdny9d_.dbf
destination
for restore of datafile 00002: /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_undo_7jgdny8d_.dbf
channel
ORA_AUX_DISK_2: reading from backup piece /rman/db_I_<DB_NAME>_s5590_Uemn01jbt_1_1_20120105
channel
ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination
for restore of datafile 00260: /data/<DB_NAME>/auc_dat.dbf
channel
ORA_AUX_DISK_1: reading from backup piece /rman/db_I_<DB_NAME>_s5589_Ueln01jbt_1_1_20120105
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/rman/db_I_<DB_NAME>_s5589_Ueln01jbt_1_1_20120105
tag=TAG20120105T220857
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel
ORA_AUX_DISK_2: restored backup piece 1
piece
handle=/rman/db_I_<DB_NAME>_s5590_Uemn01jbt_1_1_20120105 tag=TAG20120105T220857
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:08:40
starting
media recovery
archive
log thread 1 sequence 919076 is already on disk as file /work/<DB_NAME>/arch/A9190761593855296.arch
…
archive
log thread 1 sequence 919157 is already on disk as file /work/<DB_NAME>/arch/A9191571593855296.arch
channel
ORA_AUX_DISK_1: starting archive log restore to default destination
channel
ORA_AUX_DISK_2: starting archive log restore to default destination
channel
ORA_AUX_DISK_1: restoring archive log
archive
log thread=1 sequence=919074
channel
ORA_AUX_DISK_1: reading from backup piece /rman/ar_I_<DB_NAME>_s5594_Ueqn01khj_1_1_20120105
channel
ORA_AUX_DISK_2: restoring archive log
archive
log thread=1 sequence=919075
channel
ORA_AUX_DISK_2: reading from backup piece /rman/ar_I_<DB_NAME>_s5593_Uepn01khj_1_1_20120105
channel
ORA_AUX_DISK_1: restored backup piece 1
piece
handle=/rman/ar_I_<DB_NAME>_s5594_Ueqn01khj_1_1_20120105 tag=I
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
archive
log filename=/oracle/10GDB/dbs/arch1_919074_593855296.dbf thread=1
sequence=919074
channel
clone_default: deleting archive log(s)
archive
log filename=/oracle/10GDB/dbs/arch1_919074_593855296.dbf recid=965892
stamp=771880081
channel
ORA_AUX_DISK_2: restored backup piece 1
piece
handle=/rman/ar_I_<DB_NAME>_s5593_Uepn01khj_1_1_20120105 tag=I
channel
ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:29
archive
log filename=/oracle/10GDB/dbs/arch1_919075_593855296.dbf thread=1
sequence=919075
channel
clone_default: deleting archive log(s)
archive
log filename=/oracle/10GDB/dbs/arch1_919075_593855296.dbf recid=965893
stamp=771880081
archive
log filename=/work/<DB_NAME>/arch/A9190761593855296.arch thread=1
sequence=919076
…
archive
log filename=/work/<DB_NAME>/arch/A9191571593855296.arch thread=1
sequence=919157
media
recovery complete, elapsed time: 00:02:39
Finished
recover at JAN 06 2012 19:10:50
database
opened
contents
of Memory Script:
{
#
export the tablespaces in the recovery set
host
'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/oracle/10GDB/bin/oracle\)\(ARGV0=oracleqfEp\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=qfEp^'\)\)\(CONNECT_DATA=\(SID=qfEp\)\)\)
as sysdba\" point_in_time_recover=y tablespaces=
AUC_DAT,
AUC_IDX file=
tspitr_a.dmp';
#
shutdown clone before import
shutdown
clone immediate
#
import the tablespaces in the recovery set
host
'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
#
online/offline the tablespace imported
sql
"alter tablespace AUC_DAT online";
sql
"alter tablespace AUC_DAT
offline";
#
enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql
'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
#
online/offline the tablespace imported
sql
"alter tablespace AUC_IDX online";
sql
"alter tablespace AUC_IDX
offline";
#
enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql
'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing
Memory Script
Export:
Release 10.2.0.5.0 - Production on Fri Jan 6 19:11:05 2012
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
Connected
to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Export
done in WE8MSWIN1252 character set and UTF8 NCHAR character set
Note:
table data (rows) will not be exported
About
to export Tablespace Point-in-time Recovery objects...
For
tablespace AUC_DAT ...
.
exporting cluster definitions
.
exporting table definitions
. .
exporting table
T1
For
tablespace AUC_IDX ...
.
exporting cluster definitions
.
exporting table definitions
.
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 10.2.0.5.0 - Production on Fri Jan 6 19:12:20 2012
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
Connected
to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Export
file created by EXPORT:V10.02.01 via conventional path
About
to import Tablespace Point-in-time Recovery objects...
import
done in WE8MSWIN1252 character set and UTF8 NCHAR character set
.
importing SYS's objects into SYS
.
importing AUC's objects into AUC
. .
importing table "T1"
.
importing SYS's objects into SYS
Import
terminated successfully without warnings.
host
command complete
sql
statement: alter tablespace AUC_DAT online
sql
statement: alter tablespace AUC_DAT
offline
sql
statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
sql
statement: alter tablespace AUC_IDX online
sql
statement: alter tablespace AUC_IDX
offline
sql
statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing
automatic instance
Automatic
instance removed
auxiliary
instance file /rman/tspitr/cntrl_tspitr_<DB_NAME>_qfEp.f deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_system_7jgdny9d_.dbf
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_undo_7jgdny8d_.dbf
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/datafile/o1_mf_temp_7jgg9od3_.tmp
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/onlinelog/o1_mf_1_7jgg9byy_.log
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/onlinelog/o1_mf_2_7jgg9ff5_.log
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/onlinelog/o1_mf_3_7jgg9hnm_.log
deleted
auxiliary
instance file /rman/tspitr/TSPITR_<DB_NAME>_QFEP/onlinelog/o1_mf_4_7jgg9kth_.log
deleted
Finished
recover at JAN 06 2012 19:12:37
Recovery
Manager complete.
4] Bring Online & Check Results
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jan 6
19:12:44 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release
10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> alter tablespace AUC_DAT online ;
Tablespace altered.
SQL> alter tablespace AUC_IDX online ;
Tablespace altered.
SQL> select * from auc.t1 order by 1 ;
A
----------
10
20
30
SQL> exit
B] What if I have not RMAN to do TSPITR
|
Goal
|
Restore Schemas level
|
|
Version
|
10gR2
|
|
Context
|
OS DataFile Backup
|
|
Log as
|
/ AS SYSDBA
|
I needed to
restore one schema without using RMAN TSPITR feature.
It’s
possible to restore only a subset of the whole database by another way than
RMAN ( Media Manager ) and yet open the instance.
1] Restore the database subset
1.1 Check the segments location
To find out
which tablespace(s) to restore.
select distinct tablespace_name from dba_segments
where owner = 'SND' ;
1.2 Restore
Spfile / Controlfile / Redos / applicative + technical Tablespaces
Use Same OS
& Oracle Version.
SUBSET = CTL RDOs SYSAUX SYSTEM
UNDO XDB
-rw-r----- 1
oracle dba 536871424 Nov 08 23:30 log_01_1.rdo
-rw-r----- 1
oracle dba 536871424 Nov 08 23:33 log_03_1.rdo
-rw-r----- 1
oracle dba 8704 Nov 08 23:41 spfile<DB_NAME>.ora
-rw-r----- 1
oracle dba 536871424 Nov 08 23:41 log_04_1.rdo
-rw-r----- 1
oracle dba 50470912 Nov 08 23:47 xdb01.dbf
-rw-r----- 1
oracle dba 4429193216 Nov 08 23:47 snd_dat.dbf
-rw-r----- 1
oracle dba 2715820032 Nov 08 23:48 sysaux.dbf
-rw-r----- 1
oracle dba 1887444992 Nov 08 23:49 syst<DB_NAME>.dbf
-rw-r----- 1
oracle dba 157294592 Nov 08 23:49 users.dbf
-rw-r----- 1
oracle dba 26214408192 Nov 08 23:49 undo.dbf
-rw-r----- 1
oracle dba 536871424 Nov 08 23:49 log_02_1.rdo
-rw-r----- 1
oracle dba 19865600 Nov 08 23:49 ctrl2<DB_NAME>.ctl
-rw-r----- 1
oracle dba 19865600 Nov 08 23:49 ctrl1<DB_NAME>.ctl
2] Open the database
2.1 Edit Init file
to modify
unnecessary parameters ( Standby , Filepath , Memory size … ).
create pfile= '<pfile_path>' from spfile='<spfile_path>'
;
2.2 Mount and remove the missing datafiles
- Check if Datafiles are
consistent
SQL> startup
mount
SQL> select
distinct(checkpoint_change#) from v$datafile ;
CHECKPOINT_CHANGE#
------------------
9709938274
SQL> set
pages 5000
SQL>
spool off_drop.sql
SQL> select 'alter database datafile '''||df.name||''' offline drop ; ' from
v$datafile df, v$recover_file rf where rf.FILE#=df.FILE# and rf.error like
'FILE%' order by rf.file# ;
-- 254 files
SQL>
spool off_drop_exe.lst
SQL>
@off_drop.sql
SQL> !grep
–c altered off_drop_exe.lst
- Recover Database ( Backup was
taken from an Online NetApp Snapshot in Hot-backup mode )
SQL>
recover database ;
Media
recovery complete.
SQL>
alter database open ;
Database
altered.
- Recreate TEMP tablespace
SQL> CREATE
TEMPORARY TABLESPACE TEMP_AUC TEMPFILE '/data/<DB_NAME>/temp_auc.dbf'
SIZE 128M AUTOEXTEND ON NEXT 64M MAXSIZE 1024M TABLESPACE GROUP '' EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M;
SQL> alter
database default temporary tablespace temp_auc ;
- Then, proceed with normal
Datapump Exp/Imp …
Note :
How to
remove tablespaces that have Offline datafiles :
select 'drop tablespace '||a.tablespace_name||' including contents and
datafiles ;' -- , name , b.status
from dba_tablespaces a , v$datafile b , dba_data_files c where a.tablespace_name = c.tablespace_name and b.name = c.file_name
and b.status = 'OFFLINE' order by 1 ;
drop
tablespace HRTE including contents and datafiles ;
drop
tablespace HRTT including contents and datafiles ;
drop
tablespace HRXE including contents and datafiles ;
drop
tablespace HRXT including contents and datafiles ;
C] Never stop a TSPITR with unix kill !!
- My Oracle instance gets
corrupted after TSPITR Unix killing :
SQL> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR
CHANGE# TIME
---------- ------- ------- -----------------------------------------------------------------
-------
318 ONLINE ONLINE CORRUPT HEADER
0
SQL> select name from v$datafile where
file#=318 ;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/data/<base>/iqd_dat.dbf
SQL> !ls -l /data/<base>/iqd_dat.dbf
-rw-r-----
1 oracle dba 737222656 2012-01-17 13:47 /data/<base>/iqd_dat.dbf
SQL> alter database datafile 318
offline drop ;
Database altered.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication
channel
Instance
crash, this looks very bad !
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 17 17:37:19 2012
SQL> drop tablespace iqd_dat including contents and datafiles ;
drop tablespace iqd_dat including contents and datafiles
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
- I realize, the auxiliary
instance was not ended properly => killl process & remove ipcs
$ ps -ef | grep pmon
oracle 548930
1 0 Jan 16
- 0:15 ora_pmon_PPRTR
oracle 188878
1 0 Jan 16
- 0:17 ora_pmon_PPRQU
oracle 2032120 1 0 11:51:11 -
0:01 ora_pmon_ckaA
oracle 2118108
1986908 0 17:39:02 pts/1
0:00 grep pmon
$ kill 2032120
$ kill -9
2032120
$ ps -ef | grep pmon
oracle 548930
1 0 Jan 16
- 0:15 ora_pmon_PPRTR
oracle 188878
1 0 Jan 16
- 0:17 ora_pmon_PPRQU
oracle 2118134
1986908 0 17:39:33 pts/1
0:00 grep pmon
$ ipcs -am | grep ora
m 1048576
0x69faf798 --rw-r----- oracle dba
oracle dba 23 536879104 168384 1814580 17:35:00
17:36:00 5:40:42
m 1048577
0xdca9a274 --rw-r----- oracle dba
oracle dba 56 1073754112 438728 1892518 17:39:50
17:39:50 5:40:25
m 638582789 0xdc126618 --rw-r----- oracle
dba oracle dba
9 188747776 2048468 2032120 11:51:21 17:39:31 11:51:11
$ ipcrm -m 638582789
$ ipcs -am | grep ora
m 1048576
0x69faf798 --rw-r----- oracle dba
oracle dba 24 536879104 168384 1831018 17:40:00
17:40:00 5:40:42
m 1048577
0xdca9a274 --rw-r----- oracle dba
oracle dba 57 1073754112 438728 2040222 17:40:00
17:40:00 5:40:25
m 638582789 0xffffffff D-rw-r----- oracle
dba oracle dba
9 188747776 2048468 2032120 11:51:21 17:39:31 17:40:03
$ ps -ef | grep '11:51:11'
oracle 2077166
1986908 0 17:40:21 pts/1
0:00 grep 11:51:11
- I still can’t start instance
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production
on Tue Jan 17 17:40:29 2012
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete and/or deprecated
parameter(s) specified
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2099816 bytes
Variable Size 1560282520 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14675968 bytes
Database mounted.
ORA-03113: end-of-file on communication
channel
SQL> startup mount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef
extension doesn't exist
SQL> Disconnected from Oracle Database
10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining
and Real Application Testing options
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production
on Tue Jan 17 17:41:54 2012
Connected to:
Oracle Database 10g Enterprise Edition
Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining
and Real Application Testing options
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL> Disconnected from Oracle Database
10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
§
« Offline drop » still doesn’t work,
same failure as seen above ORA-03113: end-of-file on communication
channel
Let’s try a
last thing.
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production
on Tue Jan 17 17:46:05 2012
Connected to an idle instance.
SQL> startup mount
ORA-32004: obsolete and/or deprecated
parameter(s) specified
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2099816 bytes
Variable Size 1560282520 bytes
Database Buffers 1644167168 bytes
Redo Buffers 14675968 bytes
Database mounted.
SQL> alter database datafile 318
offline drop ;
Database altered.
SQL> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ----------
--------------------
318 OFFLINE OFFLINE CORRUPT HEADER
0
SQL> !ls -l
/data/<base>/iqd_dat.dbf
-rw-r-----
1 oracle dba 737222656 2012-01-17 13:47 /data/<base>/iqd_dat.dbf
SQL>
!mv /data/<base>/iqd_dat.dbf /data/<base>/iqd_dat.dbf_bad
SQL> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR
CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ----------
--------------------
318 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> alter database open ;
Database altered.
ð SAVED !!
- Let’s finish the dirty work
SQL> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- -------
----------------------------------------------------------------- ----------
--------------------
318 OFFLINE OFFLINE FILE NOT FOUND
0
SQL> drop tablespace iqd_dat ;
Tablespace dropped.
SQL> select * from v$recover_file ;
no rows selected
SQL> drop user iqd ;
User dropped.
SQL> exit
- Alert.log
Tue Jan 17
13:40:26 NFT 2012
Errors in
file /work/oracle/<base>/udump/<base>_ora_1851404.trc:
ORA-00600:
internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [],
[], []
Tue Jan 17
13:40:28 NFT 2012
Errors in
file /work/oracle/<base>/udump/<base>_ora_1851404.trc:
ORA-00600:
internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [],
[], []
Tue Jan 17
17:37:36 NFT 2012
drop
tablespace iqd_dat including contents and datafiles
Tue Jan 17
17:37:48 NFT 2012
Read of rdba:
0x4f800001 (file 318, block 1) failed with ORA-01205.
Trying reread
from disk.
Reread of
rdba: 0x4f800001 (file 318, block 1) failed with ORA-01205
Tue Jan 17
17:37:48 NFT 2012
Errors in
file /work/oracle/<base>/udump/<base>_ora_1953794.trc:
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x110980000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:38:12 NFT 2012
Read of rdba:
0x4f800001 (file 318, block 1) failed with ORA-01205.
Trying reread
from disk.
Reread of
rdba: 0x4f800001 (file 318, block 1) failed with ORA-01205
Tue Jan 17
17:38:12 NFT 2012
Errors in
file /work/oracle/<base>/bdump/<base>_pmon_364698.trc:
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x110950000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:38:14 NFT 2012
Errors in
file /work/oracle/<base>/bdump/<base>_pmon_364698.trc:
ORA-07445:
exception encountered: core dump [kghstack_err+0070] [SIGSEGV] [Address not
mapped to object] [0xFFFFFFFFFFFFFFE4] [] []
ORA-00602:
internal programming exception
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x110950000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:38:14 NFT 2012
Errors in
file /work/oracle/<base>/bdump/<base>_pmon_364698.trc:
ORA-07445:
exception encountered: core dump [kghstack_err+0070] [SIGSEGV] [Address not
mapped to object] [0xFFFFFFFFFFFFFFE4] [] []
ORA-00602:
internal programming exception
ORA-07445:
exception encountered: core dump [kghstack_err+0070] [SIGSEGV] [Address not
mapped to object] [0xFFFFFFFFFFFFFFE4] [] []
ORA-00602:
internal programming exception
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x110950000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:38:17 NFT 2012
PSP0:
terminating instance due to error 472
Instance
terminated by PSP0, pid = 393358
ALTER
DATABASE OPEN
Tue Jan 17
17:40:39 NFT 2012
Read of rdba:
0x4f800001 (file 318, block 1) failed with ORA-01205.
Trying reread
from disk.
Reread of
rdba: 0x4f800001 (file 318, block 1) failed with ORA-01205
Tue Jan 17
17:40:39 NFT 2012
Errors in
file /work/oracle/<base>/udump/<base>_ora_426146.trc:
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x1113B0000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:41:57 NFT 2012
Shutting down
instance (abort)
License high
water mark = 2
Tue Jan 17
17:43:46 NFT 2012
Errors in
file /work/oracle/<base>/bdump/<base>_smon_2122006.trc:
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-00600:
internal error code, arguments: [kghGetHpSz1], [0x1104706F0], [], [], [], [],
[], []
ORA-00600:
internal error code, arguments: [kgh_heap_sizes:ds], [0x1104706E0], [], [], [],
[], [], []
ORA-07445:
exception encountered: core dump [0000E898] [SIGSEGV] [Address not mapped to
object] [0x110500000] [] []
ORA-01205:
not a data file - type number in header is 0
Tue Jan 17
17:48:10 NFT 2012
SMON:
enabling tx recovery
Tue Jan 17
17:48:10 NFT 2012
Database
Characterset is WE8MSWIN1252
Tue Jan 17
17:48:10 NFT 2012
Errors in
file /work/oracle/<base>/bdump/<base>_dbw0_1831148.trc:
ORA-01157: cannot
identify/lock data file 318 - see DBWR trace file
ORA-01110:
data file 318: '/data/<base>/iqd_dat.dbf'
ORA-27037:
unable to obtain file status
IBM AIX RISC
System/6000 Error: 2: No such file or directory
Additional
information: 3
ORA-27037:
unable to obtain file status
IBM AIX RISC
System/6000 Error: 2: No such file or directory
Additional
information: 3
Opening with
internal Resource Manager plan
D] Transportable Tablespace from RMAN dumps
|
Goal
|
Restore Schemas level
|
|
Version
|
10gR2
|
|
Context
|
No Impact on Source DB
|
|
Log as
|
/ AS SYSDBA
|
Let’s try to
plug some Tablespace onto another DB without putting Source TS READ-ONLY. Hence
there is no impact on Source Instance.
But the
downside is we have to restore an Auxiliary Instance to get the TS before the plug
in Destination. That is it’s not possible to directly « read » the
RMAN dumps to get the Transportable TS DBF.
1] On Source, create the Transportable TS Set
and Scripts
1.1 Pre-requisite
-
Source DB : ARCHIVELOG Mode
-
Source DB : Have valid RMAN
Dumps and remaining Archivelogs on Disk
-
Same Oracle Version on Source and
Destination
-
Tablespace to Transfer are
self-contained.
select
distinct tablespace_name from dba_segments where owner = 'PEL_DEV_6I'
select
distinct owner from dba_segments where tablespace_name = 'PEL_DEV_6I_DAT'
1.2 Run RMAN script on Source Server
Create an auxiliary Instance
Restore and Recover Tablespaces
Export the Transportable TS
At that point, we got
- DBF to copy to destination
- Datapump Export TT file
- Sample script
