using bad backup by hidden parameter _allow_resetlogs_corruption

Case: how to use bad backup

To learn this, first we need to take a bad backup that we will use later to recover the database. Let’s checking the database.

C:\Users\jayva>echo %ORACLE_SID%

target

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:16:14 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$backup

  2  ;

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE                  0

SQL> exit

So we have 4 datafiles and all in open status. We will prefer to use user manage backup and do we will do without taking datafilesin backup mode. Also same time, I will execute a  pl/sql block that will push incremental data in a table. This I have done to keep changing the SCN number with respect of datafiles.

So what we will do is we will keep database up and running with executing the block i said above and copy the datafiles to backup location. Once done with datafiles copy we will also backup the controlfile. 

C:\app\JAYVA\oradata\target>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:26:32 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system archive log current;

System altered.

SQL> alter database backup controlfile to 'C:\app\JAYVA\backup\control.ctl';

Database altered.

Once done will the backup, let shutdown the database.

SQL> shu abort

ORACLE instance shut down.

SQL>

Now what we need to is to delete all the datafiles and restore the files from what backup that we have taken previously. Once done with restoring the bakcup, let us try to start the database.

SQL> startup mount

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             322962660 bytes

Database Buffers           88080384 bytes

Redo Buffers                6066176 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Now we know these files we need recovery, so trying that.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 6610452 generated at 07/06/2012 19:22:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_108

_7ZFVMWOZ_.ARC

ORA-00280: change 6610452 for thread 1 is in sequence #108

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

7_7ZFVFL8O_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6672502 generated at 07/06/2012 19:26:09 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_109

_7ZFVNZJ0_.ARC

ORA-00280: change 6672502 for thread 1 is in sequence #109

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

8_7ZFVMWOZ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6751368 generated at 07/06/2012 19:26:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_110

_7ZFVO6QJ_.ARC

ORA-00280: change 6751368 for thread 1 is in sequence #110

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

9_7ZFVNZJ0_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6768172 generated at 07/06/2012 19:26:53 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_111

_7ZFVOL8M_.ARC

ORA-00280: change 6768172 for thread 1 is in sequence #111

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

0_7ZFVO6QJ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6850266 generated at 07/06/2012 19:27:05 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_112

_7ZFVPC9X_.ARC

ORA-00280: change 6850266 for thread 1 is in sequence #112

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

1_7ZFVOL8M_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6931017 generated at 07/06/2012 19:27:30 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_113

_7ZFVPSLT_.ARC

ORA-00280: change 6931017 for thread 1 is in sequence #113

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

2_7ZFVPC9X_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7012625 generated at 07/06/2012 19:27:45 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_114

_7ZFVQ5WF_.ARC

ORA-00280: change 7012625 for thread 1 is in sequence #114

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

3_7ZFVPSLT_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7094066 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7094066 for thread 1 is in sequence #115

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

4_7ZFVQ5WF_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

Once done with the recovery, let try to open the database.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'C:\APP\JAYVA\ORADATA\TARGET\SYSTEM01.DBF'

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 7111011 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7111011 for thread 1 is in sequence #115

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

SQL>

We tried to open the database with all the option avalliable but none works. Also checking the alert log files, we is what we get the error.

In alert log +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Media Recovery Start

 started logmerger process

Fri Jul 06 21:42:19 2012

WARNING! Recovering data file 1 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 2 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 3 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 4 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

Parallel Media Recovery started with 2 slaves

ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile  ...

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

Media Recovery Log C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc  (incident=62563):

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62563\target_pr00_17832_i62563.trc

Recovery interrupted!

Fri Jul 06 21:42:24 2012

Trace dumping is performing id=[cdmp_20120706214224]

Recovered data files to a consistent state at change 7111011

Media Recovery failed with error 354

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc:

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Fri Jul 06 21:42:25 2012

Sweep [inc][62563]: completed

ORA-283 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_m000_14772.trc  (incident=62603):

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62603\target_m000_14772_i62603.trc

Trace dumping is performing id=[cdmp_20120706214227]

Fri Jul 06 21:42:49 2012

Sweep [inc][62603]: completed

Sweep [inc2][62603]: completed

Sweep [inc2][62563]: completed

 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

So alert log confirms we are using the bad backup. So now we will use hidden parameter _allow_resetlogs_corrution=yes in pfile and try to open the database with that.

C:\app\JAYVA\oradata\target>type pfile.ora

target.__db_cache_size=92274688

target.__java_pool_size=4194304

target.__large_pool_size=4194304

target.__oracle_base='C:\app\JAYVA'#ORACLE_BASE set from environment

target.__pga_aggregate_target=146800640

target.__sga_target=272629760

target.__shared_io_pool_size=0

target.__shared_pool_size=155189248

target.__streams_pool_size=8388608

*.audit_file_dest='C:\app\JAYVA\admin\target\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='C:\app\JAYVA\oradata\target\control01.ctl','C:\app\JAYVA\oradat

a\target\control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='C:\APP\JAYVA\ORADATA\TARGET'

*.db_domain=''

*.db_name='target'

*.db_recovery_file_dest_size=21474836480

*.db_recovery_file_dest='C:\app\JAYVA\flash_recovery_area\target'

*.diagnostic_dest='C:\app\JAYVA'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=targetXDB)'

*.local_listener='LISTENER_TARGET'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=10485760

*.sga_target=0

*.shared_pool_size=104857600

*.undo_tablespace='UNDOTBS1'

*._allow_resetlogs_corruption=yes

C:\app\JAYVA\oradata\target>

SQL> startup mount pfile=C:\app\JAYVA\oradata\target\pfile.ora

ORACLE instance started.

Total System Global Area  418484224 bytes

Fixed Size                  1375004 bytes

Variable Size             318768356 bytes

Database Buffers           92274688 bytes

Redo Buffers                6066176 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

So now we have open the database, lets checking the data.

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

MAGIC1                         TABLE

SALGRADE                       TABLE

TESTTABLE1                     TABLE

6 rows selected.

SQL> select count(1) from TESTTABLE1;

  COUNT(1)

----------

    450927

SQL>

We have recovered the data now.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated. As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all.Using this hidden should only used when we have confirm statement form Oracle Suppport. 

TABLESPACE POINT IN TIME RECOVERY

RMAN TABLESPACE POINT IN TIME RECOVERY

Issue: In production database wrong batch executed and updated numbers of tables. Application team immediately asking to revert the commited changes from all the tables. Options is to go for flashback query but since long list of tables and doing so would be tough task. Other way is to do the database point in time recovery but that will make lost of many others data, thus not acceptable.

Solution: Oracle as provided a RMAN TABLESPACE POINT IN TIME RECOVERY which can do quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

Let’s try to learn this. To simulate the case, I will create a table with 1000 rows. And will re-execute the batch (same like wrong batch) that will again insert 1000 rows in the table, which is need to be rollback.  Also to confirm, TSPITR (TABLESPACE POINT IN TIME RECOVERY) use the last backup, so I too have taken a database backup before to start the testing.

Now creating a separate tablespace and user for testing.

SQL> select * from v$version;

BANNER                                                                         

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production         

PL/SQL Release 11.2.0.1.0 - Production                                         

CORE  11.2.0.1.0  Production                                                     

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production                         

NLSRTL Version 11.2.0.1.0 - Production                                         

22:28:29 SQL> create tablespace rmantest datafile 'C:\APP\JAYVA\ORADATA\TARGET\rman.dbf' size 2G;

Tablespace created.

22:29:44 SQL> create user rman identified by rman default tablespace rmantest;

User created.

22:30:30 SQL> grant dba to rman;

Grant succeeded.

SQL> conn rman/rman

Connected.

SQL> create table t1 (x number);

Table created.

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values (i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(1) from t1;

  COUNT(1)

----------

      1000

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY                                                            

-------------------                                                            

2012-06-13:21:55:00

SQL> select count(1) from t1;

  COUNT(1)                                                                     

----------                                                                      

      1000                                                                     

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values (i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(1) from t1;

  COUNT(1)                                                                     

----------                                                                      

      2000                                                                     

SQL>

SQL>

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY                                                             

-------------------                                                            

2012-06-13:22:01:18                                                            

SQL> create table t2 (x number);

Table created.

So now we have a table with 1000 rows in which wrong batch executed and made 1000 rows entry. Also later to I have created pne more table t2.

Starting the TSIPTR, we have to check for objects whose CREATION_TIME is after the target time for TSPITR.

SQL> SELECT OWNER, NAME, TABLESPACE_NAME,

  2         TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')

  3         FROM TS_PITR_OBJECTS_TO_BE_DROPPED

  4  WHERE TABLESPACE_NAME IN ('RMANTEST')

  5  AND CREATION_TIME > TO_DATE('2012-06-13:21:55:00','YYYY-MM-DD:HH24:MI:SS')

  6  ORDER BY TABLESPACE_NAME, CREATION_TIME;

OWNER                          NAME

------------------------------ ------------------------------

TABLESPACE_NAME                TO_CHAR(CREATION_TI

------------------------------ -------------------

RMAN                           T2

RMANTEST                       2012-06-13:22:13:21

SQL>

Since table T2 is created after the time on what we are doing the TSPITR. So if we start the process this table will be drop, so if needed it can be backup. Not important for me, I will proceed with recovery process.

There are 3 modes of TSPITR:

Fully automated: This is default process and RMAN will manage the entire process.

Automated:  This is similar to automated process but location of auxiliary files and parameter will be different.

Manual: In this it will be entirely managed by DBA.

Process that is important in TSPITR:

Target instance: Instance which containing the tablespace, which need recovery.

Target time: Time to which recovery is needed.

Auxiliary instance: Database instance that will be used by TSPITR.

Auxiliary destination: Location where auxiliary instances will create files.

Before starting the recovery process, let’s see what steps RMAN follows:

Ø  Creates auxiliary instance (including the pfile etc..)

Ø  Mounts the auxiliary instance

Ø  Makes the candidate tablespace into OFFLINE

Ø  Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace

Ø  Applies archives (completes recovery)

Ø  Opens the database

Ø  Performs an export of the objects residing in the tablespace

Ø  Shutdown aux instance

Ø  Import the objects into the target database

Ø  Remove aux instance and cleanup

Now starting the process:

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 13 22:18:40 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN>

connected to target database: TARGET (DBID=3177713851)

RMAN> recover tablespace RMANTEST until time "to_date('2012-06-13:21:55:00','YYYY-MM-DD:HH24:MI:SS')" auxiliary destination ’C:\Users\jayva\Desktop\pending’;

RMAN>

Starting recover at 13-JUN-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 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='ghas'

initialization parameters used for automatic instance:

db_name=TARGET

db_unique_name=ghas_tspitr_TARGET

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=C:\Users\jayva\Desktop\pending

log_archive_dest_1='location=C:\Users\jayva\Desktop\pending'

#No auxiliary parameter file used

starting up automatic instance TARGET

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1374164 bytes

Variable Size                100665388 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6344704 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  time "to_date('2012-06-13:21:55:00','YYYY-MM-DD:HH24:MI:SS')";

# 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 13-JUN-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 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 C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\BACKUPSET\2012_06_13\O1_MF_NCSNF_TAG20120613T214941_7XKHK95D_.BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\BACKUPSET\2012_06_13\O1_MF_NCSNF_TAG20120613T214941_7XKHK95D_.BKP tag=TAG20120613T214941

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\CONTROLFILE\O1_MF_7XKK67HZ_.CTL

Finished restore at 13-JUN-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  time "to_date('2012-06-13:21:55:00','YYYY-MM-DD: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 '||  'RMANTEST' ||' 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  6 to

 "C:\APP\JAYVA\ORADATA\TARGET\RMAN.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, 6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace RMANTEST 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 C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 13-JUN-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 C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSTEM_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00003 to C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00002 to C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSAUX_%U_.DBF

channel ORA_AUX_DISK_1: restoring datafile 00006 to C:\APP\JAYVA\ORADATA\TARGET\RMAN.DBF

channel ORA_AUX_DISK_1: reading from backup piece C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\BACKUPSET\2012_06_13\O1_MF_NNNDF_TAG20120613T214941_7XKHDY7D_.BKP

channel ORA_AUX_DISK_1: piece handle=C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\BACKUPSET\2012_06_13\O1_MF_NNNDF_TAG20120613T214941_7XKHDY7D_.BKP tag=TAG20120613T214941

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25

Finished restore at 13-JUN-12

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=785888577 file name=C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSTEM_7XKK6QW8_.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=785888577 file name=C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_UNDOTBS1_7XKK6QVF_.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=785888577 file name=C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSAUX_7XKK6QX0_.DBF

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2012-06-13:21:55:00','YYYY-MM-DD:HH24:MI:SS')";

# 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  6 online";

# recover and open resetlogs

recover clone database tablespace  "RMANTEST", "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  6 online

Starting recover at 13-JUN-12

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 97 is already on disk as file C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_06_13\O1_MF_1_97_7XKK6K4D_.ARC

archived log file name=C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_06_13\O1_MF_1_97_7XKK6K4D_.ARC thread=1 sequence=97

media recovery complete, elapsed time: 00:00:04

Finished recover at 13-JUN-12

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  RMANTEST read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

C:\Users\jayva\Desktop\pending''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

C:\Users\jayva\Desktop\pending''";

}

executing Memory Script

sql statement: alter tablespace  RMANTEST read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''C:\Users\jayva\Desktop\pending''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''C:\Users\jayva\Desktop\pending''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_ghas": 

   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_ghas" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_ghas is:

   EXPDP>   C:\USERS\JAYVA\DESKTOP\PENDING\TSPITR_GHAS_39076.DMP

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace RMANTEST:

   EXPDP>   C:\APP\JAYVA\ORADATA\TARGET\RMAN.DBF

   EXPDP> Job "SYS"."TSPITR_EXP_ghas" successfully completed at 22:24:47

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace  RMANTEST including contents keep datafiles';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace  RMANTEST including contents keep datafiles

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_ghas" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_ghas": 

   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_ghas" successfully completed at 22:25:24

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  RMANTEST read write';

sql 'alter tablespace  RMANTEST offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace  RMANTEST read write

sql statement: alter tablespace  RMANTEST offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_TEMP_7XKKD0J6_.TMP deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\ONLINELOG\O1_MF_3_7XKKCT2J_.LOG deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\ONLINELOG\O1_MF_2_7XKKCR09_.LOG deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\ONLINELOG\O1_MF_1_7XKKCP4T_.LOG deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSAUX_7XKK6QX0_.DBF deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_UNDOTBS1_7XKK6QVF_.DBF deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\DATAFILE\O1_MF_SYSTEM_7XKK6QW8_.DBF deleted

auxiliary instance file C:\USERS\JAYVA\DESKTOP\PENDING\TARGET\CONTROLFILE\O1_MF_7XKK67HZ_.CTL deleted

Finished recover at 13-JUN-12

RMAN>

RMAN>

RMAN>

Recovery Manager complete.

Also below is the part from alert logfile:

alter tablespace RMANTEST offline immediate

Completed: alter tablespace RMANTEST offline immediate

Wed Jun 13 22:25:09 2012

drop tablespace  RMANTEST including contents keep datafiles

Completed: drop tablespace  RMANTEST including contents keep datafiles

Wed Jun 13 22:25:18 2012

DM00 started with pid=36, OS id=13420, job SYS.TSPITR_IMP_ghas

Wed Jun 13 22:25:19 2012

DW00 started with pid=37, OS id=12164, wid=1, job SYS.TSPITR_IMP_ghas

Plug in tablespace RMANTEST with datafile

  'C:\APP\JAYVA\ORADATA\TARGET\RMAN.DBF'

Wed Jun 13 22:25:26 2012

alter tablespace  RMANTEST read write

Completed: alter tablespace  RMANTEST read write

alter tablespace  RMANTEST offline

Completed: alter tablespace  RMANTEST offline

Once completing the process, checking the datafile.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select tablespace_name,status from dba_data_files where tablespace_name='RM

ANTEST';

TABLESPACE_NAME                STATUS

------------------------------ ---------

RMANTEST                       AVAILABLE

SQL>

SQL>

SQL> conn rman/rman

Connected.

SQL> select count(1) from t1;

select count(1) from t1

                     *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: 'C:\APP\JAYVA\ORADATA\TARGET\RMAN.DBF'

SQL> select tablespace_name,status,file_name from dba_data_files where tablespac

e_name='RMANTEST';

TABLESPACE_NAME                STATUS

------------------------------ ---------

FILE_NAME

--------------------------------------------------------------------------------

RMANTEST                       AVAILABLE

C:\APP\JAYVA\ORADATA\TARGET\RMAN.DBF

SQL> alter tablespace rmantest online;

Tablespace altered.

SQL> select count(1) from t1;

  COUNT(1)

----------

      1000

SQL>

So we have rollback the changes where exactly required.

Now it also got some limitation:

Database must be in archive mode.

Full database backup must be available before the time of target time.

It don’t recover the statistics, so we must gather the fresh statistics.

Once recovering the TSPITR, no old backup will be applied for this particular tablespace. So full backup is recommended after completion of the process.

For more information: please follow the Oracle document.

Suggestions are always welcome J.