1. startup database after restore from cold backup but failed.
testdb05d:/oradata/dmdb> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 2 11:21:54 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@dmdb> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 638947328 bytes
Fixed Size 2224008 bytes
Variable Size 515899512 bytes
Database Buffers 104857600 bytes
Redo Buffers 15966208 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 30933094
Session ID: 254 Serial number: 3
SYS@dmdb> exit
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1b_spda.log'
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1a_spda.log'
Errors in file /oradump/diag/rdbms/dmdb/dmdb/trace/dmdb_lgwr_23199918.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1b_spda.log'
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1a_spda.log'
Errors in file /oradump/diag/rdbms/dmdb/dmdb/trace/dmdb_ora_30933094.trc:
ORA-00338: log 1 of thread is more recent than control file
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1a_spda.log'
ORA-00312: online log 1 thread 1: '/oralog/redo/dmdb/redo_1b_spda.log'
USER (ospid: 30933094): terminating the instance due to error 338
System state dump requested by (instance=1, osid=30933094), summary=[abnormal instance termination].
System State dumped to trace file /oradump/diag/rdbms/dmdb/dmdb/trace/dmdb_diag_9502970.trc
Dumping diagnostic data in directory=[cdmp_20131002112203], requested by (instance=1, osid=30933094), summary=[abnormal insta
nce termination].
Instance terminated by USER, pid = 30933094
2. investigate and realized the cold backup set didn't include REDO files
testdb05d:/oralog/redo/dmdb> ls -l
total 2457696
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:48 redo_1a_spda.log
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:48 redo_1b_spda.log
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:55 redo_2a_spda.log
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:55 redo_2b_spda.log
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:33 redo_3a_spda.log
-rw-rw---- 1 oracle8 dba 209715712 Oct 02 10:33 redo_3b_spda.log
testdb05d:/oralog/redo/dmdb> ps -fe |grep dmdb
oracle8 21758072 5505062 0 11:26:53 pts/0 0:00 grep dmdb
testdb05d:/oralog/redo/dmdb> df -k ./
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/oralog 5242880 4012868 24% 25 1% /oralog
3. continue to clear REDO files and succeeded.
testdb05d:/oralog/redo/dmdb> cd ..
testdb05d:/oralog/redo> ls
dmdb
testdb05d:/oralog/redo> mv dmdb dmdb_1002
testdb05d:/oralog/redo> mkdir dmdb
testdb05d:/oralog/redo> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 2 11:28:54 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SYS@blus> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
testdb05d:/oralog/redo> dmdb
testdb05d:/oralog/redo> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 2 11:29:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@dmdb> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 638947328 bytes
Fixed Size 2224008 bytes
Variable Size 515899512 bytes
Database Buffers 104857600 bytes
Redo Buffers 15966208 bytes
Database mounted.
SYS@dmdb> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@dmdb> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
1 1 6070 209715200 512 2 NO
INACTIVE 3703950 2013-09-27 22:18:32 3704357
2013-09-27 22:33:33
3 1 6072 209715200 512 2 NO
CURRENT 3704765 2013-09-27 22:48:34 2.8147E+14
2 1 6071 209715200 512 2 NO
INACTIVE 3704357 2013-09-27 22:33:33 3704765
2013-09-27 22:48:34
SYS@dmdb> alter database clear logfile group 1;
Database altered.
SYS@dmdb> alter database clear logfile group 2;
Database altered.
SYS@dmdb> alter database clear logfile group 3;
Database altered.
SYS@dmdb> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------
/oralog/redo/dmdb/redo_1a_spda.log
/oralog/redo/dmdb/redo_1b_spda.log
/oralog/redo/dmdb/redo_2a_spda.log
/oralog/redo/dmdb/redo_2b_spda.log
/oralog/redo/dmdb/redo_3a_spda.log
/oralog/redo/dmdb/redo_3b_spda.log
6 rows selected.
SYS@dmdb> alter database open;
Database altered.
SYS@dmdb> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dmdb> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 638947328 bytes
Fixed Size 2224008 bytes
Variable Size 515899512 bytes
Database Buffers 104857600 bytes
Redo Buffers 15966208 bytes
Database mounted.
Database opened.
SYS@dmdb> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
testdb05d:/oralog/redo> df -k /oralog
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/oralog 5242880 2784004 47% 32 1% /oralog
MMNL started with pid=16, OS id=24379452
ORACLE_BASE from environment = /oracle/product
Wed Oct 02 11:37:59 2013
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 2345657111
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Wed Oct 02 11:38:03 2013
ALTER DATABASE OPEN
Block change tracking file is current.
Thread 1 opened at log sequence 6074
Current log# 2 seq# 6074 mem# 0: /oralog/redo/dmdb/redo_2a_spda.log
Current log# 2 seq# 6074 mem# 1: /oralog/redo/dmdb/redo_2b_spda.log
Successful open of redo thread 1
Starting background process CTWR
Wed Oct 02 11:38:03 2013
CTWR started with pid=18, OS id=30933216
Block change tracking service is active.
SMON: enabling cache recovery
[13893792] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:3198839111 end:3198839153 diff:42 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Oct 02 11:38:04 2013
QMNC started with pid=19, OS id=8323112
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
Wed Oct 02 11:38:04 2013
CJQ0 started with pid=22, OS id=19988686
Incremental checkpoint up to RBA [0x17ba.f88e.0], current log tail at RBA [0x17ba.f8e2.0]
Wed Oct 02 11:38:34 2013
Thread 1 cannot allocate new log, sequence 6075
Private strand flush not complete
Current log# 2 seq# 6074 mem# 0: /oralog/redo/dmdb/redo_2a_spda.log
Current log# 2 seq# 6074 mem# 1: /oralog/redo/dmdb/redo_2b_spda.log
Beginning log switch checkpoint up to RBA [0x17bb.2.10], SCN: 3718854
Thread 1 advanced to log sequence 6075 (LGWR switch)
Current log# 3 seq# 6075 mem# 0: /oralog/redo/dmdb/redo_3a_spda.log
Current log# 3 seq# 6075 mem# 1: /oralog/redo/dmdb/redo_3b_spda.log
Showing posts with label backup and recovery. Show all posts
Showing posts with label backup and recovery. Show all posts
Wednesday, October 09, 2013
Wednesday, October 02, 2013
Tips of RMAN Fast Incremental backup
•Available
in Enterprise Edition Only
•Also
works with NOARCHIVELOG database, but needs MOUNT state
Thursday, April 18, 2013
recovery of datafile which has no backup
Objective: recovery of datafile which has no backup
1. create the test tablepspace TBS1 and test table liqy.t1. Note that the datafile number is 5
10:40:05 SYS@TTS> create tablespace tbs1 datafile '/u02/oradata/TTS/tbs1.dbf' size 5m ;
Tablespace created.
10:40:38 SYS@TTS> create table liqy.t1 tablespace tbs1 as select * from dba_data_files;
Table created.
10:41:17 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:41:32 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
2. Rename the datafile to simulate losing datafile with no backup
10:41:42 SYS@TTS> host
[oracle@orarac2poc TTS]$ ls -lrt
total 1903004
drwxr-xr-x 3 oracle oinstall 4096 Mar 27 09:33 FRA
-rw-r--r-- 1 oracle oinstall 394 Apr 4 16:25 manual_copy.sh
-rw-r--r-- 1 oracle oinstall 12 Apr 5 14:51 login.sql
drwxr-xr-x 2 oracle oinstall 4096 Apr 5 15:05 bak
drwxr-xr-x 2 oracle oinstall 4096 Apr 10 10:53 redo
-rw-r----- 1 oracle asmadmin 52429312 Apr 17 03:00 redo03.log
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 00:03 redo01.log
-rw-r----- 1 oracle asmadmin 11804672 Apr 18 00:08 users01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Apr 18 01:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 629153792 Apr 18 10:35 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 387981312 Apr 18 10:39 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 734011392 Apr 18 10:39 system01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:41 tbs1.dbf
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 10:41 redo02.log
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:42 control02.ctl
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:42 control01.ctl
[oracle@orarac2poc TTS]$ mv tbs1.dbf tbs1.dbf.bak
[oracle@orarac2poc TTS]$ exit
exit
3. But It seems still accessible, no matter I flush the buffer_cache. Note that invoke manual checkpoint crashes the database by default.
10:42:40 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
10:42:43 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:42:46 SYS@TTS> alter system flush buffer_cache;
System altered.
10:42:57 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:42:59 SYS@TTS> alter system flush buffer_cache;
System altered.
10:43:05 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:43:07 SYS@TTS> select tablespace_name from dba_segments where segment_name='T1' and owner='LIQY';
TABLESPACE_NAME
------------------------------
TBS1
10:43:37 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:43:44 SYS@TTS> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 473
Session ID: 73 Serial number: 4209
10:43:53 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
4. Confirm the db is crashed and start recovery
orarac2poc:TTS:/u02/oradata/TTS> ps -ef |grep pmon
oracle 895 32555 0 10:44 pts/1 00:00:00 grep pmon
grid 22664 1 0 Apr12 ? 00:01:49 asm_pmon_+ASM2
oracle 23098 1 0 Apr12 ? 00:01:56 ora_pmon_RCAT
oracle 24642 1 0 Apr15 ? 00:00:57 ora_pmon_DGS
oracle 25015 1 0 Apr15 ? 00:01:03 ora_pmon_DGLOGI
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 18 10:44:03 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected.
10:44:03 SYS@TTS> shutdown abort
ORACLE instance shut down.
10:44:10 SYS@TTS> startup
ORA-00443: background process "PMON" did not start
10:44:24 SYS@TTS> shutdown abort
ORACLE instance shut down.
10:44:30 SYS@TTS> startup mount
ORACLE instance started.
Total System Global Area 663908352 bytes
Fixed Size 2229440 bytes
Variable Size 293604160 bytes
Database Buffers 360710144 bytes
Redo Buffers 7364608 bytes
Database mounted.
10:44:50 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 18 10:44:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TTS (DBID=1769654909, not open)
RMAN> sql 'alter database datafile 5 offline';
using target database control file instead of recovery catalog
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5 ;
Starting restore at Apr 18 2013 10:46:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK
creating datafile file number=5 name=/u02/oradata/TTS/tbs1.dbf
restore not done; all files read only, offline, or already restored
Finished restore at Apr 18 2013 10:46:09
RMAN> host 'ls -lrt ';
total 1908144
drwxr-xr-x 3 oracle oinstall 4096 Mar 27 09:33 FRA
-rw-r--r-- 1 oracle oinstall 394 Apr 4 16:25 manual_copy.sh
-rw-r--r-- 1 oracle oinstall 12 Apr 5 14:51 login.sql
drwxr-xr-x 2 oracle oinstall 4096 Apr 5 15:05 bak
drwxr-xr-x 2 oracle oinstall 4096 Apr 10 10:53 redo
-rw-r----- 1 oracle asmadmin 52429312 Apr 17 03:00 redo03.log
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 00:03 redo01.log
-rw-r----- 1 oracle asmadmin 20979712 Apr 18 01:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:42 tbs1.dbf.bak
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 10:43 redo02.log
-rw-r----- 1 oracle asmadmin 11804672 Apr 18 10:43 users01.dbf
-rw-r----- 1 oracle asmadmin 387981312 Apr 18 10:43 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 734011392 Apr 18 10:43 system01.dbf
-rw-r----- 1 oracle asmadmin 629153792 Apr 18 10:43 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:46 tbs1.dbf
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:46 control02.ctl
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:46 control01.ctl
host command complete
RMAN> recover datafile 5 ;
Starting recover at Apr 18 2013 10:46:48
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at Apr 18 2013 10:46:48
RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN> alter database open;
database opened
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 18 10:47:26 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
10:47:26 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:47:33 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
5. The alert log
orarac2poc:TTS:/u01/app/oracle/diag/rdbms/tts/TTS/trace> tail -f alert_TTS.log
ALTER DATABASE MOUNT
Thu Apr 18 10:44:46 2013
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1521))))' SCOPE=MEMORY SID='TTS';
Successful mount of redo thread 1, with mount id 1772146525
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Apr 18 10:45:26 2013
alter database datafile 5 offline
Completed: alter database datafile 5 offline
Thu Apr 18 10:46:08 2013
Checker run found 1 new persistent data failures
Thu Apr 18 10:46:48 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 5
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u02/oradata/TTS/redo02.log
Media Recovery Complete (TTS)
Completed: alter database recover if needed
datafile 5
Thu Apr 18 10:47:04 2013
alter database datafile 5 online
Completed: alter database datafile 5 online
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 11, block 47487, scn 1954234
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u02/oradata/TTS/redo02.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 11, block 47487, scn 1974235
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Apr 18 10:47:11 2013
LGWR: STARTING ARCH PROCESSES
Thu Apr 18 10:47:11 2013
ARC0 started with pid=30, OS id=1292
...
1. create the test tablepspace TBS1 and test table liqy.t1. Note that the datafile number is 5
10:40:05 SYS@TTS> create tablespace tbs1 datafile '/u02/oradata/TTS/tbs1.dbf' size 5m ;
Tablespace created.
10:40:38 SYS@TTS> create table liqy.t1 tablespace tbs1 as select * from dba_data_files;
Table created.
10:41:17 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:41:32 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
2. Rename the datafile to simulate losing datafile with no backup
10:41:42 SYS@TTS> host
[oracle@orarac2poc TTS]$ ls -lrt
total 1903004
drwxr-xr-x 3 oracle oinstall 4096 Mar 27 09:33 FRA
-rw-r--r-- 1 oracle oinstall 394 Apr 4 16:25 manual_copy.sh
-rw-r--r-- 1 oracle oinstall 12 Apr 5 14:51 login.sql
drwxr-xr-x 2 oracle oinstall 4096 Apr 5 15:05 bak
drwxr-xr-x 2 oracle oinstall 4096 Apr 10 10:53 redo
-rw-r----- 1 oracle asmadmin 52429312 Apr 17 03:00 redo03.log
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 00:03 redo01.log
-rw-r----- 1 oracle asmadmin 11804672 Apr 18 00:08 users01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Apr 18 01:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 629153792 Apr 18 10:35 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 387981312 Apr 18 10:39 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 734011392 Apr 18 10:39 system01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:41 tbs1.dbf
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 10:41 redo02.log
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:42 control02.ctl
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:42 control01.ctl
[oracle@orarac2poc TTS]$ mv tbs1.dbf tbs1.dbf.bak
[oracle@orarac2poc TTS]$ exit
exit
3. But It seems still accessible, no matter I flush the buffer_cache. Note that invoke manual checkpoint crashes the database by default.
10:42:40 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
10:42:43 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:42:46 SYS@TTS> alter system flush buffer_cache;
System altered.
10:42:57 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:42:59 SYS@TTS> alter system flush buffer_cache;
System altered.
10:43:05 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:43:07 SYS@TTS> select tablespace_name from dba_segments where segment_name='T1' and owner='LIQY';
TABLESPACE_NAME
------------------------------
TBS1
10:43:37 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:43:44 SYS@TTS> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 473
Session ID: 73 Serial number: 4209
10:43:53 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
4. Confirm the db is crashed and start recovery
orarac2poc:TTS:/u02/oradata/TTS> ps -ef |grep pmon
oracle 895 32555 0 10:44 pts/1 00:00:00 grep pmon
grid 22664 1 0 Apr12 ? 00:01:49 asm_pmon_+ASM2
oracle 23098 1 0 Apr12 ? 00:01:56 ora_pmon_RCAT
oracle 24642 1 0 Apr15 ? 00:00:57 ora_pmon_DGS
oracle 25015 1 0 Apr15 ? 00:01:03 ora_pmon_DGLOGI
orarac2poc:TTS:/u02/oradata/TTS> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 18 10:44:03 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected.
10:44:03 SYS@TTS> shutdown abort
ORACLE instance shut down.
10:44:10 SYS@TTS> startup
ORA-00443: background process "PMON" did not start
10:44:24 SYS@TTS> shutdown abort
ORACLE instance shut down.
10:44:30 SYS@TTS> startup mount
ORACLE instance started.
Total System Global Area 663908352 bytes
Fixed Size 2229440 bytes
Variable Size 293604160 bytes
Database Buffers 360710144 bytes
Redo Buffers 7364608 bytes
Database mounted.
10:44:50 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 18 10:44:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TTS (DBID=1769654909, not open)
RMAN> sql 'alter database datafile 5 offline';
using target database control file instead of recovery catalog
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5 ;
Starting restore at Apr 18 2013 10:46:08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK
creating datafile file number=5 name=/u02/oradata/TTS/tbs1.dbf
restore not done; all files read only, offline, or already restored
Finished restore at Apr 18 2013 10:46:09
RMAN> host 'ls -lrt ';
total 1908144
drwxr-xr-x 3 oracle oinstall 4096 Mar 27 09:33 FRA
-rw-r--r-- 1 oracle oinstall 394 Apr 4 16:25 manual_copy.sh
-rw-r--r-- 1 oracle oinstall 12 Apr 5 14:51 login.sql
drwxr-xr-x 2 oracle oinstall 4096 Apr 5 15:05 bak
drwxr-xr-x 2 oracle oinstall 4096 Apr 10 10:53 redo
-rw-r----- 1 oracle asmadmin 52429312 Apr 17 03:00 redo03.log
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 00:03 redo01.log
-rw-r----- 1 oracle asmadmin 20979712 Apr 18 01:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:42 tbs1.dbf.bak
-rw-r----- 1 oracle asmadmin 52429312 Apr 18 10:43 redo02.log
-rw-r----- 1 oracle asmadmin 11804672 Apr 18 10:43 users01.dbf
-rw-r----- 1 oracle asmadmin 387981312 Apr 18 10:43 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 734011392 Apr 18 10:43 system01.dbf
-rw-r----- 1 oracle asmadmin 629153792 Apr 18 10:43 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Apr 18 10:46 tbs1.dbf
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:46 control02.ctl
-rw-r----- 1 oracle asmadmin 10010624 Apr 18 10:46 control01.ctl
host command complete
RMAN> recover datafile 5 ;
Starting recover at Apr 18 2013 10:46:48
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at Apr 18 2013 10:46:48
RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN> alter database open;
database opened
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 18 10:47:26 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
10:47:26 SYS@TTS> select count(*) from liqy.t1;
COUNT(*)
----------
5
10:47:33 SYS@TTS> select * from liqy.t1;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS ONLINE_
---------- ----------- -------
/u02/oradata/TTS/system01.dbf
1 SYSTEM 734003200 89600 AVAILABLE 1 YES 3.4360E+10 4194302 1280
732954624 89472 SYSTEM
/u02/oradata/TTS/sysaux01.dbf
2 SYSAUX 629145600 76800 AVAILABLE 2 YES 3.4360E+10 4194302 1280
628097024 76672 ONLINE
/u02/oradata/TTS/undotbs01.dbf
3 UNDOTBS1 387973120 47360 AVAILABLE 3 YES 3.4360E+10 4194302 640
386924544 47232 ONLINE
/u02/oradata/TTS/users01.dbf
4 USERS 11796480 1440 AVAILABLE 4 YES 3.4360E+10 4194302 160
10747904 1312 ONLINE
/u02/oradata/TTS/tbs1.dbf
5 TBS1 5242880 640 AVAILABLE 5 NO 0 0 0
4194304 512 ONLINE
5. The alert log
orarac2poc:TTS:/u01/app/oracle/diag/rdbms/tts/TTS/trace> tail -f alert_TTS.log
ALTER DATABASE MOUNT
Thu Apr 18 10:44:46 2013
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.139.92.42)(PORT=1521))))' SCOPE=MEMORY SID='TTS';
Successful mount of redo thread 1, with mount id 1772146525
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Apr 18 10:45:26 2013
alter database datafile 5 offline
Completed: alter database datafile 5 offline
Thu Apr 18 10:46:08 2013
Checker run found 1 new persistent data failures
Thu Apr 18 10:46:48 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 5
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u02/oradata/TTS/redo02.log
Media Recovery Complete (TTS)
Completed: alter database recover if needed
datafile 5
Thu Apr 18 10:47:04 2013
alter database datafile 5 online
Completed: alter database datafile 5 online
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 11, block 47487, scn 1954234
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u02/oradata/TTS/redo02.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 11, block 47487, scn 1974235
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Apr 18 10:47:11 2013
LGWR: STARTING ARCH PROCESSES
Thu Apr 18 10:47:11 2013
ARC0 started with pid=30, OS id=1292
...
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.
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
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
Subscribe to:
Posts (Atom)
