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
...
Popular Posts
-
configured "CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK; " when RMAN> delete noprompt archivelog u...
-
I have one PT env using storage sync for database sync. Thought need to re-register DB after DB refresh, so unregister and register again....
-
[symptoms] orarac2poc:ORCL2:/home/oracle> . ./oms_agent_env orarac2poc:ORCL2:/home/oracle> $AGENT_HOME/bin/emctl start agent Oracl...
-
Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second f...
-
SQL> select * from dba_data_files where tablespace_name='TESTBS'; FILE_NAME ...
-
As I have CPU issue with physical standby database in previous post, I have to remove it before I have the solution. In 12c Enterprise Man... -
after rounds of recovery testing, I believer there are tons of obsolete files in FRA. There are some stubborn datafile copy can't be de...
-
This installation extended to my home hour, since both Postgresql and Zabbix are new to me. Followed few articles without much difficulty.... -
RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # de...
-
I hit the error when using RMAN duplicate command to create a physical standby database, with purpose of using same directory structure in...