Tuesday, April 09, 2013

Point-in-time recvoery with current control file and online redo log

Summary:  This happens when current online redo log is not archived.




orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 11:24:22 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> insert into liqy.incr_changes values (5, 'change no apply');

1 row created.

SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 11:24:52 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> set time on
11:24:58 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1367765

11:25:07 SYS@TTS> exi
SP2-0042: unknown command "exi" - rest of line ignored.
11:25:21 SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 11:25:39 2013

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

connected to target database: TTS (DBID=1769654909)

RMAN>  backup incremental level 1 for recover of copy with tag daily_incr database;

Starting backup at 2013-04-04:11:25:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/TTS/system01.dbf
input datafile file number=00002 name=/u02/oradata/TTS/sysaux01.dbf
input datafile file number=00003 name=/u02/oradata/TTS/undotbs01.dbf
input datafile file number=00005 name=/u02/oradata/TTS/tts1_01.bdf
input datafile file number=00006 name=/u02/oradata/TTS/testtbs1.dbf
input datafile file number=00004 name=/u02/oradata/TTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-04-04:11:25:46
channel ORA_DISK_1: finished piece 1 at 2013-04-04:11:26:21
piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswotrp_.bkp tag=DAILY_INCR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2013-04-04:11:26:21

Starting Control File and SPFILE Autobackup at 2013-04-04:11:26:22
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811855582_8oswpyro_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-04-04:11:26:23

RMAN> backup as backupset archivelog all not backed up delete all input;

Starting backup at 2013-04-04:11:29:30
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=54 STAMP=811855770
channel ORA_DISK_1: starting piece 1 at 2013-04-04:11:29:31
channel ORA_DISK_1: finished piece 1 at 2013-04-04:11:29:32
piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_annnn_TAG20130404T112930_8oswwv3k_.bkp tag=TAG20130404T112930 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_04/o1_mf_1_11_8oswwtrn_.arc RECID=54 STAMP=811855770
Finished backup at 2013-04-04:11:29:32

Starting Control File and SPFILE Autobackup at 2013-04-04:11:29:32
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811855772_8oswwwbr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-04-04:11:29:33

RMAN> recover device type disk copy of database with tag daily_incr;

Starting recover at 2013-04-04:11:29:43
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8oqzctbg_.dbf
recovering datafile copy file number=00002 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8oqzdxo7_.dbf
recovering datafile copy file number=00003 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8oqzg18b_.dbf
recovering datafile copy file number=00004 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8oqzgm55_.dbf
recovering datafile copy file number=00005 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8oqzgjgw_.dbf
recovering datafile copy file number=00006 name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8oqzgks6_.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswotrp_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswotrp_.bkp tag=DAILY_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2013-04-04:11:29:44

Starting Control File and SPFILE Autobackup at 2013-04-04:11:29:45
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811855785_8oswx997_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-04-04:11:29:46

RMAN> exit


Recovery Manager complete.
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 11:30:18 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> set time on
11:30:22 SYS@TTS> insert into liqy.incr_changes values (6, 'last change');

1 row created.

11:30:41 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1367977

11:30:48 SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 11:30:51 2013

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

connected to target database: TTS (DBID=1769654909)

RMAN>  backup incremental level 1 for recover of copy with tag daily_incr database;

Starting backup at 2013-04-04:11:30:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/TTS/system01.dbf
input datafile file number=00002 name=/u02/oradata/TTS/sysaux01.dbf
input datafile file number=00003 name=/u02/oradata/TTS/undotbs01.dbf
input datafile file number=00005 name=/u02/oradata/TTS/tts1_01.bdf
input datafile file number=00006 name=/u02/oradata/TTS/testtbs1.dbf
input datafile file number=00004 name=/u02/oradata/TTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-04-04:11:30:58
channel ORA_DISK_1: finished piece 1 at 2013-04-04:11:31:33
piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswzlpr_.bkp tag=DAILY_INCR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 2013-04-04:11:31:33

Starting Control File and SPFILE Autobackup at 2013-04-04:11:31:33
piece handle=/u02/oradata/TTS/FRA/TTS/autobackup/2013_04_04/o1_mf_s_811855893_8osx0pc1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-04-04:11:31:34

RMAN> exit


Recovery Manager complete.

--but online redo log is not archived.

orarac2poc:TTS:/u02/oradata/TTS> echo "last incremental backup is not applied to disk copy"
last incremental backup is not applied to disk copy
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 11:33:49 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> set time on
11:33:55 SYS@TTS> drop table liqy.incr_changes ;

Table dropped.

11:34:19 SYS@TTS> select current_scn from v$database;

CURRENT_SCN
-----------
    1368102

11:34:22 SYS@TTS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11:36:26 SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> ls -lrt
total 2005144
drwxr-xr-x 3 oracle oinstall      4096 Mar 27 09:33 FRA
drwxr-xr-x 2 oracle oinstall      4096 Apr  2 16:57 bak
drwxr-xr-x 2 oracle oinstall      4096 Apr  2 18:23 redo
-rw-r----- 1 oracle oinstall  85991424 Apr  4 00:54 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  4 11:23 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 11:29 redo02.log
-rw-r----- 1 oracle oinstall   5251072 Apr  4 11:34 users01.dbf
-rw-r----- 1 oracle oinstall 387981312 Apr  4 11:34 undotbs01.dbf
-rw-r----- 1 oracle oinstall  20979712 Apr  4 11:34 tts1_01.bdf
-rw-r----- 1 oracle oinstall  10493952 Apr  4 11:34 testtbs1.dbf
-rw-r----- 1 oracle oinstall 734011392 Apr  4 11:34 system01.dbf
-rw-r----- 1 oracle oinstall 629153792 Apr  4 11:34 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Apr  4 11:34 redo03.log
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control02.ctl
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control01.ctl
orarac2poc:TTS:/u02/oradata/TTS> cp -p  control01.ctl control01.ctl.bak
orarac2poc:TTS:/u02/oradata/TTS> gzip *.*
orarac2poc:TTS:/u02/oradata/TTS> gunzip control01.ctl.bak.gz
orarac2poc:TTS:/u02/oradata/TTS> cp -p control01.ctl.bak control01.ctl
orarac2poc:TTS:/u02/oradata/TTS> cp -p control01.ctl.bak control02.ctl
orarac2poc:TTS:/u02/oradata/TTS> ls -l
total 268212
drwxr-xr-x 2 oracle oinstall      4096 Apr  2 16:57 bak
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control01.ctl
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control01.ctl.bak
-rw-r----- 1 oracle oinstall     46773 Apr  4 11:35 control01.ctl.gz
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control02.ctl
-rw-r----- 1 oracle oinstall     46773 Apr  4 11:35 control02.ctl.gz
drwxr-xr-x 3 oracle oinstall      4096 Mar 27 09:33 FRA
drwxr-xr-x 2 oracle oinstall      4096 Apr  2 18:23 redo
-rw-r----- 1 oracle oinstall   9548038 Apr  4 11:23 redo01.log.gz
-rw-r----- 1 oracle oinstall   8635076 Apr  4 11:29 redo02.log.gz
-rw-r----- 1 oracle oinstall   8278005 Apr  4 11:34 redo03.log.gz
-rw-r----- 1 oracle oinstall  76678173 Apr  4 11:34 sysaux01.dbf.gz
-rw-r----- 1 oracle oinstall 127967092 Apr  4 11:34 system01.dbf.gz
-rw-r----- 1 oracle oinstall   6117241 Apr  4 00:54 temp01.dbf.gz
-rw-r----- 1 oracle oinstall     24376 Apr  4 11:34 testtbs1.dbf.gz
-rw-r----- 1 oracle oinstall   1137516 Apr  4 11:34 tts1_01.bdf.gz
-rw-r----- 1 oracle oinstall   5753888 Apr  4 11:34 undotbs01.dbf.gz
-rw-r----- 1 oracle oinstall     13674 Apr  4 11:34 users01.dbf.gz
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 11:52:40 2013

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     663908352 bytes

Fixed Size                     2229440 bytes
Variable Size                281021248 bytes
Database Buffers             373293056 bytes
Redo Buffers                   7364608 bytes


RMAN> exit


Recovery Manager complete.
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 11:54:38 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> select controlfile_change# from v$database;

CONTROLFILE_CHANGE#
-------------------
            1368071

SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 11:54:44 2013

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

connected to target database: TTS (DBID=1769654909, not open)

run {
set until time '2013-04-04:11:33:00';
 restore database;
 recover database;
}

executing command: SET until clause

Starting restore at 2013-04-04:11:56:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=72 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8oqzctbg_.dbf
destination for restore of datafile 00001: /u02/oradata/TTS/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/u02/oradata/TTS/system01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=71 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8oqzdxo7_.dbf
destination for restore of datafile 00002: /u02/oradata/TTS/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/u02/oradata/TTS/sysaux01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=70 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8oqzg18b_.dbf
destination for restore of datafile 00003: /u02/oradata/TTS/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/u02/oradata/TTS/undotbs01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=67 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8oqzgm55_.dbf
destination for restore of datafile 00004: /u02/oradata/TTS/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u02/oradata/TTS/users01.dbf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=69 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8oqzgjgw_.dbf
destination for restore of datafile 00005: /u02/oradata/TTS/tts1_01.bdf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/u02/oradata/TTS/tts1_01.bdf RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=68 STAMP=811855784 file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8oqzgks6_.dbf
destination for restore of datafile 00006: /u02/oradata/TTS/testtbs1.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/u02/oradata/TTS/testtbs1.dbf RECID=0 STAMP=0
Finished restore at 2013-04-04:11:57:42

Starting recover at 2013-04-04:11:57:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/TTS/system01.dbf
destination for restore of datafile 00002: /u02/oradata/TTS/sysaux01.dbf
destination for restore of datafile 00003: /u02/oradata/TTS/undotbs01.dbf
destination for restore of datafile 00004: /u02/oradata/TTS/users01.dbf
destination for restore of datafile 00005: /u02/oradata/TTS/tts1_01.bdf
destination for restore of datafile 00006: /u02/oradata/TTS/testtbs1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswzlpr_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/TTS/FRA/TTS/backupset/2013_04_04/o1_mf_nnnd1_DAILY_INCR_8oswzlpr_.bkp tag=DAILY_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/04/2013 11:57:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'APR 04 2013 11:33:00'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/oradata/TTS/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

RMAN> exit


Recovery Manager complete.

-- provide online redo log

orarac2poc:TTS:/u02/oradata/TTS> ls -l
total 2015916
drwxr-xr-x 2 oracle oinstall      4096 Apr  2 16:57 bak
-rw-r----- 1 oracle oinstall  10010624 Apr  4 12:02 control01.ctl
-rw-r----- 1 oracle oinstall  10010624 Apr  4 11:35 control01.ctl.bak
-rw-r----- 1 oracle oinstall     46773 Apr  4 11:35 control01.ctl.gz
-rw-r----- 1 oracle oinstall  10010624 Apr  4 12:02 control02.ctl
-rw-r----- 1 oracle oinstall     46773 Apr  4 11:35 control02.ctl.gz
drwxr-xr-x 3 oracle oinstall      4096 Mar 27 09:33 FRA
drwxr-xr-x 2 oracle oinstall      4096 Apr  4 12:02 redo
-rw-r----- 1 oracle oinstall   9548038 Apr  4 11:23 redo01.log.gz
-rw-r----- 1 oracle oinstall   8635076 Apr  4 11:29 redo02.log.gz
-rw-r----- 1 oracle oinstall   8278005 Apr  4 11:34 redo03.log.gz

-rw-r----- 1 oracle asmadmin 629153792 Apr  4 11:57 sysaux01.dbf
-rw-r----- 1 oracle oinstall  76678173 Apr  4 11:34 sysaux01.dbf.gz
-rw-r----- 1 oracle asmadmin 734011392 Apr  4 11:57 system01.dbf
-rw-r----- 1 oracle oinstall 127967092 Apr  4 11:34 system01.dbf.gz
-rw-r----- 1 oracle oinstall   6117241 Apr  4 00:54 temp01.dbf.gz
-rw-r----- 1 oracle asmadmin  10493952 Apr  4 11:57 testtbs1.dbf
-rw-r----- 1 oracle oinstall     24376 Apr  4 11:34 testtbs1.dbf.gz
-rw-r----- 1 oracle asmadmin  20979712 Apr  4 11:57 tts1_01.bdf
-rw-r----- 1 oracle oinstall   1137516 Apr  4 11:34 tts1_01.bdf.gz
-rw-r----- 1 oracle asmadmin 387981312 Apr  4 11:57 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5753888 Apr  4 11:34 undotbs01.dbf.gz
-rw-r----- 1 oracle asmadmin   5251072 Apr  4 11:57 users01.dbf
-rw-r----- 1 oracle oinstall     13674 Apr  4 11:34 users01.dbf.gz
orarac2poc:TTS:/u02/oradata/TTS> ls -l redo*.gz
-rw-r----- 1 oracle oinstall 9548038 Apr  4 11:23 redo01.log.gz
-rw-r----- 1 oracle oinstall 8635076 Apr  4 11:29 redo02.log.gz
-rw-r----- 1 oracle oinstall 8278005 Apr  4 11:34 redo03.log.gz
orarac2poc:TTS:/u02/oradata/TTS> cp -p redo*.gz ./redo
orarac2poc:TTS:/u02/oradata/TTS> gunzip redo*.gz
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 12:03:37 2013

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

connected to target database: TTS (DBID=1769654909, not open)

{
run {
 set until time '2013-04-04:11:33:00';
 # restore database;
  recover database;
 }

executing command: SET until clause

Starting recover at 2013-04-04:12:03:41
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2013-04-04:12:03:43

RMAN> sql 'alter database open read only';

sql statement: alter database open read only
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/04/2013 12:45:03
RMAN-11003: failure during parse/execution of SQL statement: alter database open read only
ORA-16005: database requires recovery

RMAN> exit


Recovery Manager complete.
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 12:45:18 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> select controlfile_change# from v$database;

CONTROLFILE_CHANGE#
-------------------
            1368071


SYS@TTS> select file#, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1368072
         2            1368072
         3            1368072
         4            1368072
         5            1368072
         6            1368072

6 rows selected.

SYS@TTS> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SYS@TTS> alter database open resetlogs;

Database altered.

SYS@TTS> select file#, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1368076
         2            1368076
         3            1368076
         4            1368076
         5            1368076
         6            1368076

6 rows selected.

SYS@TTS> select controlfile_change# from v$database;

CONTROLFILE_CHANGE#
-------------------
            1368186

SYS@TTS> select file#, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1368076
         2            1368076
         3            1368076
         4            1368076
         5            1368076
         6            1368076

6 rows selected.

SYS@TTS> set time on
12:48:01 SYS@TTS> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS  RESETLOGS_ID
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------
PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------------ --------------------------
           1                 1 2013-03-20:16:41:33                       0                     PARENT     810578493
                 0 NO

           2            721427 2013-03-21:14:12:46                       1 2013-03-20:16:41:33 PARENT     810655966
                 1 NO

           3           1009055 2013-03-27:09:44:13                  721427 2013-03-21:14:12:46 PARENT     811158253
                 2 NO

           4           1019618 2013-03-27:15:11:48                 1009055 2013-03-27:09:44:13 PARENT     811177908
                 3 NO

           5           1021343 2013-03-27:15:43:03                 1019618 2013-03-27:15:11:48 PARENT     811179783
                 4 NO

           6           1147625 2013-03-28:17:35:21                 1021343 2013-03-27:15:43:03 PARENT     811272921
                 5 NO

           7           1368073 2013-04-04:12:47:03                 1147625 2013-03-28:17:35:21 CURRENT    811860423
                 6 NO


7 rows selected.

12:49:01 SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 12:49:10 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> select * from  liqy.incr_changes ;

        C1 CHANGES
---------- --------------------
         3 some changes
         4 some more changes
         5 change no apply
         6 last change
         1 init backup copy
         2 change before crash

6 rows selected.


 SYS@TTS>  exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:TTS:/u02/oradata/TTS> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 4 13:31:35 2013

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

connected to target database: TTS (DBID=1769654909)

RMAN> list copy;

using target database control file instead of recovery catalog
specification does not match any control file copy in the repository
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
72      1    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8oqzctbg_.dbf
        Tag: DAILY_INCR

71      2    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8oqzdxo7_.dbf
        Tag: DAILY_INCR

70      3    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8oqzg18b_.dbf
        Tag: DAILY_INCR

67      4    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8oqzgm55_.dbf
        Tag: DAILY_INCR

69      5    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8oqzgjgw_.dbf
        Tag: DAILY_INCR

68      6    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8oqzgks6_.dbf
        Tag: DAILY_INCR

List of Archived Log Copies for database with db_unique_name TTS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
55      1    12      A 2013-04-04:11:29:30
        Name: /u02/oradata/TTS/FRA/TTS/archivelog/2013_04_04/o1_mf_1_12_8ot1g77f_.arc


RMAN> crosscheck copy;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
specification does not match any control file copy in the repository
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8oqzctbg_.dbf RECID=72 STAMP=811855784
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8oqzdxo7_.dbf RECID=71 STAMP=811855784
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8oqzg18b_.dbf RECID=70 STAMP=811855784
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8oqzgm55_.dbf RECID=67 STAMP=811855784
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8oqzgjgw_.dbf RECID=69 STAMP=811855784
validation succeeded for datafile copy
datafile copy file name=/u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8oqzgks6_.dbf RECID=68 STAMP=811855784
validation succeeded for archived log
archived log file name=/u02/oradata/TTS/FRA/TTS/archivelog/2013_04_04/o1_mf_1_12_8ot1g77f_.arc RECID=55 STAMP=811860423
Crosschecked 7 objects


RMAN> list copy;

specification does not match any control file copy in the repository
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
72      1    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_system_8oqzctbg_.dbf
        Tag: DAILY_INCR

71      2    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_sysaux_8oqzdxo7_.dbf
        Tag: DAILY_INCR

70      3    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_undotbs1_8oqzg18b_.dbf
        Tag: DAILY_INCR

67      4    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_users_8oqzgm55_.dbf
        Tag: DAILY_INCR

69      5    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_tts1_8oqzgjgw_.dbf
        Tag: DAILY_INCR

68      6    A 2013-04-04:11:29:44 1367797    2013-04-04:11:25:46
        Name: /u02/oradata/TTS/FRA/TTS/datafile/o1_mf_testtbs_8oqzgks6_.dbf
        Tag: DAILY_INCR

List of Archived Log Copies for database with db_unique_name TTS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
55      1    12      A 2013-04-04:11:29:30
        Name: /u02/oradata/TTS/FRA/TTS/archivelog/2013_04_04/o1_mf_1_12_8ot1g77f_.arc


RMAN> exit


Recovery Manager complete.
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 4 13:45:48 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@TTS> insert into liqy.incr_changes values (7, 'after PITR');

1 row created.

SYS@TTS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options