Thursday, December 19, 2013

Script to Recover the Datafile having no Backup Set

I have created a Shell Script which will recover the Datafile which has no Backup Set.  The script uses the RMAN utility to recover the Datafile.

I'll use the Following tasks to recover the same.

1. Find the Corrupted Datafile.
2. Bounce the Database and open it in MOUNT mode.
3. Put the Corrupted Datafile in OFFLINE mode.
4. Open the Database.
5. Create the NEW Datafile copy at the Database Level.
6. Recover the Datafile.
7. Put the Datafile in ONLINE mode.


First I'll create a scenario where I'll take the whole database backup and then will create a Tablespace and a table into that and then will delete the datafile from the OS level and will recover the same using Archived log.
Note. You can recover only the NON-System Tablespaces with this method.

Lets take the Database backup using the below Script. You can download the script from here.

#!/bin/bash

clear scr

echo "******************************************************************"
echo "******************* Database Backup Script ***********************"
echo "******************************************************************"
echo -e "\n"

echo "Do you want to create the Backup Directory???"
read ans
echo -e "\n"

if [[ $ans =~ Y|y ]]; then

echo "Enter the Dir path"
read path

mkdir -p $path
echo "Backup Dir created"
fi

echo -e "\n"

echo "Checking whether the Database is in Archivelog mode or Noarchivelog mode"
echo -e "\n"

val1=`sqlplus -S / as sysdba<<EOF
set head off
select log_mode from v\\$database;
EOF`
echo "Database is in "$val1" mode"

if [ $val1 = 'NOARCHIVELOG' ]; then
echo "Do you want to change the Database mode to Archive Log Mode???"
read a

if [[ $a =~ Y|y ]]; then
sqlplus -S / as sysdba <<EOF
shut immediate
startup mount
alter database archivelog;
alter database open;
prompt Database Mode changed to Archivelog Mode;
exit;
EOF
fi

echo -e "\n"

echo "Taking the Database Full BKP using RMAN"
echo -e "\n"

rman target / <<EOF
run
{
allocate channel ch1 type
disk format '$path/%U.bkp';
backup database plus archivelog;
release channel ch1;
}

exit;
EOF

else

echo -e "\n"
echo "Taking the Database Full BKP using RMAN"
echo -e "\n"

rman target / <<EOF
run
{
allocate channel ch1 type
disk format '$path/%U.bkp';
backup database plus archivelog;
release channel ch1;
}
exit;
EOF

fi               




OUTPUT




[oracle@test script]$ sh DB_BKP_script.sh
******************************************************************
******************* Database Backup Script ***********************
******************************************************************


Do you want to create the Backup Directory???
yep


Enter the Dir path
/u01/bkp
Backup Dir created


Checking whether the Database is in Archivelog mode or Noarchivelog mode


Database is in  ARCHIVELOG mode


Taking the Database Full BKP using RMAN



Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 19 20:21:26 2013

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

connected to target database: ORCL (DBID=1352132643)

RMAN> 2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=262 device type=DISK


Starting backup at 19-DEC-13
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=925 RECID=77 STAMP=834610889
channel ch1: starting piece 1 at 19-DEC-13
channel ch1: finished piece 1 at 19-DEC-13
piece handle=/u01/bkp/1coru9ma_1_1.bkp tag=TAG20131219T202129 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-DEC-13

Starting backup at 19-DEC-13
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/mgmt.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/mgmt_depot.dbf
channel ch1: starting piece 1 at 19-DEC-13

piece handle=/u01/bkp/1doru9mb_1_1.bkp tag=TAG20131219T202131 comment=NONE
channel ch1: backup set complete, elapsed time: 00:02:06
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 19-DEC-13
channel ch1: finished piece 1 at 19-DEC-13
piece handle=/u01/bkp/1eoru9q9_1_1.bkp tag=TAG20131219T202131 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-DEC-13

Starting backup at 19-DEC-13
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=926 RECID=78 STAMP=834611020
channel ch1: starting piece 1 at 19-DEC-13
channel ch1: finished piece 1 at 19-DEC-13
piece handle=/u01/bkp/1foru9qc_1_1.bkp tag=TAG20131219T202340 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-DEC-13

released channel: ch1

RMAN>

Recovery Manager complete.




Now create a tablespace named "TEST" and then will create a USER and TABLE into that tablespace.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/mgmt_depot.dbf
/u01/app/oracle/oradata/orcl/mgmt.dbf
/u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf

8 rows selected.

SQL> create tablespace_name from dba_tablespaces;
create tablespace_name from dba_tablespaces
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
MGMT_ECM_DEPOT_TS
MGMT_TABLESPACE
MGMT_AD4J_TS

9 rows selected.

SQL> create tablespace test
  2  datafile '/u01/app/oracle/oradata/orcl/test.dbf'
  3  size 500m autoextend on maxsize 700m;

Tablespace created.


SQL> create user test identified by test
  2  default tablespace test;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table test
  2  (
  3  id number(10)
  4  );

Table created.

SQL> begin
  2  for i in 1..1000000 loop
  3  insert into test
  4  values(i);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
   1000000

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.



Now we have the Tablespace named "TEST" which doesn't has the RMAN backup. 

Lets drop the newly created datafile at OS level and then recover it through the script.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/mgmt_depot.dbf
/u01/app/oracle/oradata/orcl/mgmt.dbf
/u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf
/u01/app/oracle/oradata/orcl/test.dbf

9 rows selected.
 

[oracle@test script]$ rm -f /u01/app/oracle/oradata/orcl/test.dbf

Lets try DML operation over the table belongs to that datafile.

SQL> conn test/test
Connected.
SQL> delete test
  2  where id between 1 and 100;
delete test
       *
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/test.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
 

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/test.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
 


Now I'll use the below script to recover the datafile and the data. You can download the script from here.


#!/bin/bash

clear scr


echo "**************************************************************"
echo "********************* NON SYSTEM Datafile Recovery **********************"
echo "**************************************************************"
echo -e "\n"

echo "Checking for the Datafile Corruption"
echo -e "\n"

val1=`rman target / <<EOF
backup check logical database;
exit;
EOF`
echo "$val1"

echo -e "\n"
if echo $val1 | grep -q "RMAN-06056" ;
then
echo "Datafile is corrupted"
else
echo "No Datafile is corrupted"
exit;
fi

echo -e "\n"

echo "Check the status for the Corrupted Datafile in the database"
echo -e "\n"

sqlplus -S / as sysdba<<EOF
set line 100 pagesize 100
col NAME for a50
select FILE#,NAME,STATUS,BYTES/1024 as "SIZE" from v\$datafile;
prompt checking the block corruption list;
select * from v\$database_block_corruption;
prompt checking the file needed for recovery;
select * from v\$recover_file;
exit;
EOF

echo -e "\n"

echo "Verifying the Datafile Corruption using DBV utility"
echo -e "\n"
echo "Enter the Datafile Name"
read datafile

dbv file=$datafile blocksize=8192

echo -e "\n"

echo "DO YOU WANT TO BOUNCE THE DATABASE"
read ans
echo -e "\n"

if [[ $ans =~ Y|y ]]; then
echo "Starting the database in MOUNT mode and then Performing the Recovery of the CORRUPTED Datafile"

sqlplus -S / as sysdba<<EOF

startup force mount
prompt put the datafile in offline mode;
alter database datafile '$datafile' OFFLINE;
prompt OPEN the Database;
alter database open;
prompt Check the Datafile Status;
set line 100 pagesize 100
col NAME for a50
select FILE#,NAME,STATUS,BYTES/1024 as "SIZE" from v\$datafile;
prompt checking the block corruption list;
select * from v\$database_block_corruption;
prompt checking the file needed for recovery;
select * from v\$recover_file;
prompt Create the Corrupted Datafile at Database level;
alter database create datafile '$datafile';
prompt Perform the Recovery for the Corrupted Datafile;
recover datafile '$datafile';
auto
prompt Bring the Recovered Datafile to ONLINE;
alter database datafile '$datafile' online;
prompt Check the Datafile status;
select FILE#,NAME,STATUS,BYTES/1024 as "SIZE" from v\$datafile;
exit;
EOF
fi






OUTPUT



[oracle@test script]$ sh DB_FILE_RECOVER.sh
**************************************************************
********************* NON SYSTEM Datafile Recovery **********************
**************************************************************


Checking for the Datafile Corruption



Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 19 20:38:00 2013

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

connected to target database: ORCL (DBID=1352132643)

RMAN>
Starting backup at 19-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
RMAN-06169: could not read file header for datafile 9 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/19/2013 20:38:01
RMAN-06056: could not access datafile 9

RMAN>

Recovery Manager complete.


Datafile is corrupted


Check the status for the Corrupted Datafile in the database



     FILE# NAME                                               STATUS        SIZE
---------- -------------------------------------------------- ------- ----------
         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM      901120
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE     1310720
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE      849920
         4 /u01/app/oracle/oradata/orcl/users01.dbf           ONLINE     8376320
         5 /u01/app/oracle/oradata/orcl/example01.dbf         ONLINE      353920
         6 /u01/app/oracle/oradata/orcl/mgmt_depot.dbf        ONLINE      184320
         7 /u01/app/oracle/oradata/orcl/mgmt.dbf              ONLINE     4096000
         8 /u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf         ONLINE      204800
         9 /u01/app/oracle/oradata/orcl/test.dbf              ONLINE           0

9 rows selected.

checking the block corruption list

no rows selected

checking the file needed for recovery

no rows selected



Verifying the Datafile Corruption using DBV utility


Enter the Datafile Name

/u01/app/oracle/oradata/orcl/test.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Dec 19 20:38:30 2013

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


DBV-00100: Specified FILE (/u01/app/oracle/oradata/orcl/test.dbf) not accessible


DO YOU WANT TO BOUNCE THE DATABASE
yep


Starting the database in MOUNT mode and then Performing the Recovery of the CORRUPTED Datafile
ORACLE instance started.

Total System Global Area 2142679040 bytes
Fixed Size                  1346140 bytes
Variable Size            1342178724 bytes
Database Buffers          788529152 bytes
Redo Buffers               10625024 bytes
Database mounted.
put the datafile in offline mode

Database altered.

OPEN the Database

Database altered.

Check the Datafile Status

     FILE# NAME                                               STATUS        SIZE
---------- -------------------------------------------------- ------- ----------
         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM      901120
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE     1310720
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE      849920
         4 /u01/app/oracle/oradata/orcl/users01.dbf           ONLINE     8376320
         5 /u01/app/oracle/oradata/orcl/example01.dbf         ONLINE      353920
         6 /u01/app/oracle/oradata/orcl/mgmt_depot.dbf        ONLINE      184320
         7 /u01/app/oracle/oradata/orcl/mgmt.dbf              ONLINE     4096000
         8 /u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf         ONLINE      204800
         9 /u01/app/oracle/oradata/orcl/test.dbf              RECOVER          0

9 rows selected.

checking the block corruption list

no rows selected

checking the file needed for recovery

     FILE# ONLINE  ONLINE_ ERROR
---------- ------- ------- -----------------------------------------------------------------
   CHANGE# TIME
---------- ---------
         9 OFFLINE OFFLINE FILE NOT FOUND
         0


Create the Corrupted Datafile at Database level

Database altered.

Perform the Recovery for the Corrupted Datafile
ORA-00279: change 25305681 generated at 12/19/2013 20:26:47 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_927_9c62g07w_.arc
ORA-00280: change 25305681 for thread 1 is in sequence #927


ORA-00279: change 25308130 generated at 12/19/2013 20:29:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_928_9c62g5do_.arc
ORA-00280: change 25308130 for thread 1 is in sequence #928


ORA-00279: change 25309723 generated at 12/19/2013 20:29:17 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_929_9c62gcon_.arc
ORA-00280: change 25309723 for thread 1 is in sequence #929


ORA-00279: change 25311318 generated at 12/19/2013 20:29:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_930_9c62glh6_.arc
ORA-00280: change 25311318 for thread 1 is in sequence #930


ORA-00279: change 25312991 generated at 12/19/2013 20:29:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_931_9c62gt8d_.arc
ORA-00280: change 25312991 for thread 1 is in sequence #931


ORA-00279: change 25314584 generated at 12/19/2013 20:29:37 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_12_19/o1_mf_1_932_9c62j2jl_.arc
ORA-00280: change 25314584 for thread 1 is in sequence #932


Log applied.
Media recovery complete.
Bring the Recovered Datafile to ONLINE

Database altered.

Check the Datafile status

     FILE# NAME                                               STATUS        SIZE
---------- -------------------------------------------------- ------- ----------
         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM      901120
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE     1310720
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE      849920
         4 /u01/app/oracle/oradata/orcl/users01.dbf           ONLINE     8376320
         5 /u01/app/oracle/oradata/orcl/example01.dbf         ONLINE      353920
         6 /u01/app/oracle/oradata/orcl/mgmt_depot.dbf        ONLINE      184320
         7 /u01/app/oracle/oradata/orcl/mgmt.dbf              ONLINE     4096000
         8 /u01/app/oracle/oradata/orcl/mgmt_ad4j.dbf         ONLINE      204800
         9 /u01/app/oracle/oradata/orcl/test.dbf              ONLINE      512000

9 rows selected.


SQL> conn test/test
Connected.
SQL> select count(*) from test;

  COUNT(*)
----------
   1000000
 


Thanks for your time. :)

No comments:

Post a Comment