11g Release 2 has new features,
which enables to recover from a dropped tablespace using TSPITR. In the earlier
version of oracle we could not recover a dropped tablespace using TSPITR
(tablespace point in time recovery)
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1
SQL> create user tom identified by tom default tablespace samtbs1;
User created.
SQL> grant connect,resource to tom;
Grant succeeded.
SQL> conn tom/tom
Connected.
SQL> create table table1 ( id number);
Table created.
SQL> insert into table1 values (123);
1 row created.
SQL> create table table2 ( id number);
Table created.
SQL> insert into table2 values (987);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1 TABLE
TABLE2 TABLE
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1022086
[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 12:42:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: JATWAY (DBID=3196377116)
RMAN>
RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T';
Starting backup at 21-SEP-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=10 STAMP=793527386
input archived log thread=1 sequence=12 RECID=11 STAMP=793527425
input archived log thread=1 sequence=13 RECID=12 STAMP=793527610
input archived log thread=1 sequence=14 RECID=13 STAMP=793530126
input archived log thread=1 sequence=15 RECID=14 STAMP=793530172
input archived log thread=1 sequence=16 RECID=15 STAMP=793530212
input archived log thread=1 sequence=17 RECID=16 STAMP=793531476
input archived log thread=1 sequence=18 RECID=17 STAMP=793532833
input archived log thread=1 sequence=19 RECID=18 STAMP=793532851
input archived log thread=1 sequence=20 RECID=19 STAMP=793533374
input archived log thread=1 sequence=21 RECID=20 STAMP=793533390
input archived log thread=1 sequence=22 RECID=21 STAMP=793533982
input archived log thread=1 sequence=23 RECID=22 STAMP=793545410
input archived log thread=1 sequence=24 RECID=23 STAMP=793545414
input archived log thread=1 sequence=25 RECID=24 STAMP=793545621
input archived log thread=1 sequence=26 RECID=25 STAMP=794570848
input archived log thread=1 sequence=27 RECID=26 STAMP=794570853
input archived log thread=1 sequence=28 RECID=27 STAMP=794580052
input archived log thread=1 sequence=29 RECID=28 STAMP=794580314
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_29_3196377116_20120921 tag=TAG20120921T124515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/jetway/jatway/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/jetway/jatway/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=29 STAMP=794580386
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_32_3196377116_20120921 tag=TAG20120921T124627 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12
RMAN> exit
Recovery Manager complete.
[oracle@sfo ~]$
[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$sqlplus "/as sysdba"
SQL> conn tom/tom
Connected.
SQL> create table table3 ( id number);
table created
SQL> select * from table3;
ID
----------
654
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1022829
SQL> drop tablespace samtbs1 including contents and datafiles;
Tablespace dropped.
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jatway] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ rman target=/
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 13:03:47 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: JATWAY (DBID=3196377116)
RMAN> recover tablespace samtbs1 until scn 1022829 auxiliary destination '/u01/app/oracle/backup/backupjatway';
Starting recover at 21-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 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='csxD'
initialization parameters used for automatic instance:
db_name=JATWAY
db_unique_name=csxD_tspitr_JATWAY
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/backup/backupjatway
log_archive_dest_1='location=/u01/app/oracle/backup/backupjatway'
#No auxiliary parameter file used
starting up automatic instance JATWAY
Oracle instance started
Total System Global Area 292278272 bytes
Fixed Size 2212736 bytes
Variable Size 100666496 bytes
Database Buffers 184549376 bytes
Redo Buffers 4849664 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace samtbs1
contents of Memory Script:
{
# set requested point in time
set until scn 1022829;
# 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 21-SEP-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl
Finished restore at 21-SEP-12
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 1022829;
# 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
"/u01/app/oracle/oradata/jetway/jatway/samtbs01.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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 21-SEP-12
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 /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 21-SEP-12
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 1022829;
# 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 "SAMTBS1", "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 21-SEP-12
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf thread=1 sequence=30
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf thread=1 sequence=31
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf thread=1 sequence=32
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-SEP-12
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace SAMTBS1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
}
executing Memory Script
sql statement: alter tablespace SAMTBS1 read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_csxD":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_csxD" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_csxD is:
EXPDP> /u01/app/oracle/backup/backupjatway/tspitr_csxD_55279.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace SAMTBS1:
EXPDP> /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_csxD" successfully completed at 13:06:40
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_csxD" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_csxD":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_csxD" successfully completed at 13:06:57
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace SAMTBS1 read write';
sql 'alter tablespace SAMTBS1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace SAMTBS1 read write
sql statement: alter tablespace SAMTBS1 offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_85sl4ssp_.tmp deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_3_85sl4q6w_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_2_85sl4pbv_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_1_85sl4oct_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl deleted
Finished recover at 21-SEP-12
RMAN>exit
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SAMTBS1 OFFLINE
SQL> alter tablespace samtbs1 online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SAMTBS1 ONLINE
SQL> conn tom/tom
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1 TABLE
TABLE2 TABLE
TABLE3 TABLE
No comments:
Post a Comment