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
[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
[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. :)
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