Identify and Preserve Objects That Are Lost After TSPITR
需要的时间点 scn 是 44929642
SELECT OWNER, NAME, TABLESPACE_NAME,
TO_CHAR(CREATION_TIME,'YYYY-MM-DD:HH24:MI:SS')
FROM TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('CCDATA')
AND CREATION_TIME > TO_DATE(TO_CHAR(SCN_TO_TIMESTAMP(44929642 ),
'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;
执行表空间TSPITR恢复
[oracle@my2950 bin]$ mkdir /tmp/tspitr
[oracle@my2950 bin]$
[oracle@my2950 bin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 16:02:02 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB1 (DBID=1497461080)
RMAN>
RMAN> recover tablespace CCDATA until scn 44929642 auxiliary destination '/tmp/tspimr';
RMAN> recover tablespace CCDATA until scn 44929642 auxiliary destination '/tmp/tspimr';
Starting recover at 09-JUN-2014 16:04:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1579 device type=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 UNDOTBS1
Creating automatic instance, with SID='zqDv'
initialization parameters used for automatic instance:
db_name=MYDB1
db_unique_name=zqDv_tspitr_MYDB1
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/tmp/tspimr
log_archive_dest_1='location=/tmp/tspimr'
#No auxiliary parameter file used
starting up automatic instance MYDB1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/09/2014 16:04:50
RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory
RMAN>
[oracle@my2950 bin]$ mkdir /u04/tspitr
[oracle@my2950 bin]$ ll -d /u04/tspitr/
drwxr-xr-x 2 oracle dba 6 Jun 9 16:11 /u04/tspitr//
[oracle@my2950 bin]$
[oracle@my2950 bin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 9 16:18:48 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB1 (DBID=1497461080)
RMAN> recover tablespace CCDATA until scn 44929642 auxiliary destination '/u04/tspitr';
Starting recover at 09-JUN-2014 16:19:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1533 device type=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 UNDOTBS1
Creating automatic instance, with SID='Blig'
initialization parameters used for automatic instance:
db_name=MYDB1
db_unique_name=Blig_tspitr_MYDB1
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u04/tspitr
log_archive_dest_1='location=/u04/tspitr'
#No auxiliary parameter file used
starting up automatic instance MYDB1
Oracle instance started
Total System Global Area 292278272 bytes
Fixed Size 2227744 bytes
Variable Size 100663776 bytes
Database Buffers 184549376 bytes
Redo Buffers 4837376 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
contents of Memory Script:
{
# set requested point in time
set until scn 44929642;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
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 09-JUN-2014 16:19:59
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=58 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/DB_87pack2f_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/DB_87pack2f_1_1 tag=TAG20140609T043024
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u04/tspitr/MYDB1/controlfile/o1_mf_9sbvkj93_.ctl
Finished restore at 09-JUN-2014 16:20:01
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:
{
# set requested point in time
set until scn 44929642;
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'CCDATA' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to
"/u02/oradata/mydb1/ccdata.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace CCDATA offline immediate
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u04/tspitr/MYDB1/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 09-JUN-2014 16:20:09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u04/tspitr/MYDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/DB_7rpa9vj0_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/DB_7rpa9vj0_1_1 tag=TAG20140608T043023
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u04/tspitr/MYDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u02/oradata/mydb1/ccdata.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/DB_83pacjv1_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/DB_83pacjv1_1_1 tag=TAG20140609T043024
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u04/tspitr/MYDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/DB_85pacjv1_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/DB_85pacjv1_1_1 tag=TAG20140609T043024
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 09-JUN-2014 16:24:14
datafile 1 switched to datafile copy
input datafile copy RECID=29 STAMP=849803054 file name=/u04/tspitr/MYDB1/datafile/o1_mf_system_9sbvkso7_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=30 STAMP=849803055 file name=/u04/tspitr/MYDB1/datafile/o1_mf_undotbs1_9sbvo2s3_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=31 STAMP=849803055 file name=/u04/tspitr/MYDB1/datafile/o1_mf_sysaux_9sbvqft6_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 44929642;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "CCDATA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 5 online
Starting recover at 09-JUN-2014 16:24:15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file /u04/orafra/fast_recovery_area/mydb1/MYDB1/archivelog/2014_06_09/o1_mf_1_17_9sb8tk3m_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u04/orafra/fast_recovery_area/mydb1/MYDB1/archivelog/2014_06_09/o1_mf_1_18_9sbq1zjl_.arc
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/arch_80pa9vo4_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/arch_80pa9vo4_1_1 tag=TAG20140608T043308
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u04/tspitr/1_11_849545305.dbf thread=1 sequence=11
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_11_849545305.dbf RECID=1265 STAMP=849803057
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/arch_81pacjug_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/arch_81pacjug_1_1 tag=TAG20140609T043008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u04/tspitr/1_12_849545305.dbf thread=1 sequence=12
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_12_849545305.dbf RECID=1267 STAMP=849803072
archived log file name=/u04/tspitr/1_13_849545305.dbf thread=1 sequence=13
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_13_849545305.dbf RECID=1266 STAMP=849803071
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/arch_82pacjug_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/arch_82pacjug_1_1 tag=TAG20140609T043008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=/u04/tspitr/1_14_849545305.dbf thread=1 sequence=14
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_14_849545305.dbf RECID=1269 STAMP=849803105
archived log file name=/u04/tspitr/1_15_849545305.dbf thread=1 sequence=15
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_15_849545305.dbf RECID=1268 STAMP=849803102
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_AUX_DISK_1: reading from backup piece /u05/rman/arch_89pack40_1_1
channel ORA_AUX_DISK_1: piece handle=/u05/rman/arch_89pack40_1_1 tag=TAG20140609T043304
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u04/tspitr/1_16_849545305.dbf thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=/u04/tspitr/1_16_849545305.dbf RECID=1270 STAMP=849803118
archived log file name=/u04/orafra/fast_recovery_area/mydb1/MYDB1/archivelog/2014_06_09/o1_mf_1_17_9sb8tk3m_.arc thread=1 sequence=17
archived log file name=/u04/orafra/fast_recovery_area/mydb1/MYDB1/archivelog/2014_06_09/o1_mf_1_18_9sbq1zjl_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:51
Finished recover at 09-JUN-2014 16:26:10
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace CCDATA read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u04/tspitr''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u04/tspitr''";
}
executing Memory Script
sql statement: alter tablespace CCDATA read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u04/tspitr''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u04/tspitr''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_Blig":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_Blig" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Blig is:
EXPDP> /u04/tspitr/tspitr_Blig_41313.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace CCDATA:
EXPDP> /u02/oradata/mydb1/ccdata.dbf
EXPDP> Job "SYS"."TSPITR_EXP_Blig" successfully completed at 16:27:57
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace CCDATA including contents keep datafiles';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace CCDATA including contents keep datafiles
Performing import of metadata...
.................
sql statement: alter tablespace CCDATA read write
sql statement: alter tablespace CCDATA offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u04/tspitr/MYDB1/datafile/o1_mf_temp_9sbvxk80_.tmp deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_6_9sbvx60g_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_5_9sbvx51b_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_4_9sbvx3v1_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_3_9sbvx2kn_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_11_9sbvxg07_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_10_9sbvxd7t_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_9_9sbvxbho_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_8_9sbvx8r8_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/onlinelog/o1_mf_7_9sbvx703_.log deleted
auxiliary instance file /u04/tspitr/MYDB1/datafile/o1_mf_sysaux_9sbvqft6_.dbf deleted
auxiliary instance file /u04/tspitr/MYDB1/datafile/o1_mf_undotbs1_9sbvo2s3_.dbf deleted
auxiliary instance file /u04/tspitr/MYDB1/datafile/o1_mf_system_9sbvkso7_.dbf deleted
auxiliary instance file /u04/tspitr/MYDB1/controlfile/o1_mf_9sbvkj93_.ctl deleted
Finished recover at 09-JUN-2014 16:28:44
RMAN>
alert 日志
Mon Jun 09 16:20:06 2014
ALTER SYSTEM ARCHIVE LOG
Mon Jun 09 16:20:06 2014
Thread 1 advanced to log sequence 20 (LGWR switch)
Current log# 11 seq# 20 mem# 0: /u03/oraredo/mydb1/onlinelog/redoaa05.log
Archived Log entry 1268 added for thread 1 sequence 19 ID 0xe9e52847 dest 1:
alter tablespace CCDATA offline immediate
Completed: alter tablespace CCDATA offline immediate
Mon Jun 09 16:28:15 2014
drop tablespace CCDATA including contents keep datafiles
Completed: drop tablespace CCDATA including contents keep datafiles
Mon Jun 09 16:28:31 2014
DM00 started with pid=45, OS id=4036, job SYS.TSPITR_IMP_Blig
Mon Jun 09 16:28:32 2014
DW00 started with pid=46, OS id=4038, wid=1, job SYS.TSPITR_IMP_Blig
Plug in tablespace CCDATA with datafile
'/u02/oradata/mydb1/ccdata.dbf'
Mon Jun 09 16:28:42 2014
alter tablespace CCDATA read write
Completed: alter tablespace CCDATA read write
alter tablespace CCDATA offline
Completed: alter tablespace CCDATA offline
RMAN> quit
Recovery Manager complete.
[oracle@my2950 bin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 9 16:47:17 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn cc/cc
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$+2NdEQy0eLPgQxEAZQpyxA==$0 TABLE
CA SYNONYM
CALLPACKAGES TABLE
CC SYNONYM
CC_PARAMS TABLE
CK SYNONYM
CP SYNONYM
CUSTACCOUNTS TABLE
CUSTCLIS TABLE
CUSTKEYS TABLE
DC SYNONYM
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DIALCODES TABLE
PACKAGECLIS TABLE
PACKAGESLOTS TABLE
PC SYNONYM
PS SYNONYM
16 rows selected.
SQL> select count(*) from PS;
select count(*) from PS
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u02/oradata/mydb1/ccdata.dbf'
SQL> conn / as sysdba
Connected.
SQL> alter tablespace CCDATA ONLINE;
Tablespace altered.
SQL> conn cc/cc
Connected.
SQL> select count(*) from PS;
COUNT(*)
----------
100
SQL>
该备份的备份.......
end
