Debate on a Facebook DBA group that leads to a better understanding of Oracle Database

Debate on a Facebook DBA group that leads to a better understanding of Oracle Database

I recently had a debate with someone on a DBA group who says that it is possible to recover a datafile without any sort of backups.This does not tally with any of my understanding at all! He claims that he have successfully done it on his laptop and challenged me to try it myself. So I took the challenge 🙂

Setting up the stage for the testing.

SQL> create tablespace tbs_test datafile '/u01/oradata/orcl/tbstest1.dbf' size 5M;
SQL> alter tablespace tbs_test add datafile '/u01/oradata/orcl/tbstest2.dbf' size 5M;

SQL> alter database datafile '/u01/oradata/orcl/tbstest1.dbf' offline;
SQL> create table testtable2 tablespace tbs_test as select * from dba_data_files;

SQL> select count(*) from testtable2;
COUNT(*)
----------
7

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TESTTABLE2';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS TESTTABLE2 TBS_TEST

SQL> select B.FILE_NAME from dba_segments a, dba_data_files b where a.header_file=b.file_ID and segment_name='TESTTABLE2';
FILE_NAME
----------------------------------------
/u01/oradata/orcl/tbstest2.dbf

The above ensures that the table only exists in ‘/u01/oradata/orcl/tbstest2.dbf’ datafile. Now comes test 1 which successfully restored the datafile without any backups!

 SQL> !rm '/u01/oradata/orcl/tbstest2.dbf'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 01:19:54 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 310381952 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/orcl/tbstest2.dbf'
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' offline;
Database altered.
SQL> alter database create datafile '/u01/oradata/orcl/tbstest2.dbf';
Database altered.

SQL> recover datafile '/u01/oradata/orcl/tbstest2.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' online;
Database altered.

OMG! Why did it WORK??!! I must admit I was quite intrigued at this point. Please be patient and read on about my second test!

SQL> !rm '/u01/oradata/orcl/tbstest2.dbf'

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 310381952 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/orcl/tbstest2.dbf'
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' offline;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> !ls -al /u01/archive
total 732
drwxr-xr-x. 2 oracle dba 4096 Oct 30 01:23 .
drwxr-xr-x. 6 root root 4096 Oct 30 00:39 ..
-rw-r-----. 1 oracle dba 262656 Oct 30 01:22 1_21_838508177.dbf
-rw-r-----. 1 oracle dba 466944 Oct 30 01:23 1_22_838508177.dbf
-rw-r-----. 1 oracle dba 1024 Oct 30 01:23 1_23_838508177.dbf
-rw-r-----. 1 oracle dba 2048 Oct 30 01:23 1_24_838508177.dbf

SQL> !rm /u01/archive/*

SQL> alter database create datafile '/u01/oradata/orcl/tbstest2.dbf';
Database altered.

SQL> recover datafile '/u01/oradata/orcl/tbstest2.dbf';
ORA-00279: change 1447147 generated at 10/30/2014 00:49:02 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_20_838508177.dbf
ORA-00280: change 1447147 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
^C

So the reason for the successful restore was due to the archivelogs or the data that resides in the redo log. I almost forgotten about my basics!

Regards,
Wei Shan

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s