DBA : Backup and Recovery

Showing posts with label Backup and Recovery. Show all posts

Sunday, June 22, 2014

Recovery of the database when any one of the controlfile is lost

In this article, we will see how to recover the database when one of the multiplexed controlfile is lost.

Controlfile is the critical component of Oracle database and this stores the names and location of the data files, log files, database name, block size, RMAN catalog information and character set.

1. List the controlfiles in the database.

SQL> select name from v$controlfile;

NAME

-----------------------------------------------------------------

/data1/noasmdb/redofiles/NOASMDB/control01.ctl

/data1/noasmdb/control02.ctl

/data1/noasmdb/redofiles/NOASMDB/controlfile/control03.ctl

2. Next, we will see the status of the datafile in v$datafile and v$datafile_header views.

SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

FILE# NAME STATUS TS# BLOCK_SIZE

---------- ----------------------------------------------------------------- ------- ---------- ----------

1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192

2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192

3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7 8192

4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192

5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192

6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192

7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192

7 rows selected.

SQL> select file#,tablespace_name,name,status from v$datafile_header;

FILE# TABLESPACE_NAME NAME STATUS

---------- ------------------------------ ----------------------------------------------------------------- -------

1 SYSTEM /data1/noasmdb/datafile/system01.dbf ONLINE

2 SYSAUX /data1/noasmdb/datafile/sysaux01.dbf ONLINE

3 TEST_BKP /data1/noasmdb/datafile/testbkp01.dbf ONLINE

5 UNDOTBS /data1/noasmdb/datafile/undotbs01.dbf ONLINE

6 NEWTEST /data1/noasmdb/datafile/newtest01.dbf ONLINE

7 NOVALIDTBS /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE

7 rows selected.

3. Next, we will remove one of the control file “control02.ct1” .

[oracle@vm1 noasmdb]$ ls -ltrh

total 10M

drwxr-xr-x 2 oracle oinstall 4.0K Feb 11 14:43 datafile

drwxr-xr-x 2 oracle oinstall 4.0K Feb 14 12:32 archivelog

drwxr-xr-x 3 oracle oinstall 4.0K Feb 14 12:49 redofiles

-rw-r----- 1 oracle oinstall 9.9M Feb 14 14:01 control02.ctl

[oracle@vm1 noasmdb]$ rm control02.ctl

[oracle@vm1 noasmdb]$

4. Lets try to query v$datafile, v$datafile_header views now. I am able to query the views . This mean that the database is operating fine. We will be getting alert log errors also.

SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

FILE# NAME STATUS TS# BLOCK_SIZE

---------- ----------------------------------------------------------------- ------- ---------- ----------

1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192

2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192

3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7 8192

4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192

5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192

6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192

7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192

7 rows selected.

Fri Feb 14 14:04:40 2014

Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_m000_10799.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/noasmdb/control02.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

This concludes that this controlfile is not a subset and it is not the main file used by the datafiles.

5. Now, lets remove another control file and see.

[oracle@vm1 NOASMDB]$ ls -ltrh

total 10M

drwxr-x--- 2 oracle asmadmin 4.0K Feb 8 20:58 onlinelog

drwxr-x--- 2 oracle asmadmin 4.0K Feb 8 21:24 datafile

drwxr-x--- 2 oracle asmadmin 4.0K Feb 14 12:48 controlfile

-rw-r----- 1 oracle oinstall 9.9M Feb 14 14:07 control01.ctl

[oracle@vm1 NOASMDB]$ rm control01.ctl

6. Now, when we try to login using RMAN , we get following error. But the database is not crashed as we were able to query.

[oracle@vm1 backup]$ rman target sys/sys catalog=rman_cat/rman_cat@testdb

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 14 14:11:46 2014

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-06003: ORACLE error from target database:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Also, when you try for a fresh SQLPLUS session to connect, we get the following error.

SQL> select file#,tablespace_name,name,status from v$datafile_header;

select file#,tablespace_name,name,status from v$datafile_header

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Now, we have simulated in losing of one of the controlfile.

7. Next, we have to see the parameter of control file to check the locations of the controlfiles.

SQL> show parameter control;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /data1/noasmdb/redofiles/NOASM

DB/control01.ctl, /data1/noasm

db/control02.ctl, /data1/noasm

db/redofiles/NOASMDB/controlfi

le/control03.ctl

8. The only option is to copy the unaffected controlfile “control03.ctl” to all these locations and rename it. To do this, we need to shutdown the database. Even the “shutdown immediate” will not happen.

SQL> shut immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/data1/noasmdb/redofiles/NOASMDB/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shut abort;

ORACLE instance shut down.

[oracle@vm1 controlfile]$ cp control03.ctl /data1/noasmdb/redofiles/NOASMDB/control03.ctl

[oracle@vm1 controlfile]$ cp control03.ctl /data1/noasmdb/control03.ctl

[oracle@vm1 controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/

[oracle@vm1 NOASMDB]$ ls

[oracle@vm1 noasmdb]$ pwd

/data1/noasmdb

[oracle@vm1 noasmdb]$ mv control03.ctl control02.ctl

[oracle@vm1 controlfile]$ cd /data1/noasmdb/redofiles/NOASMDB/

[oracle@vm1 NOASMDB]$ mv control03.ctl control01.ctl

9. Let’s start the database now and see what happens.

SQL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2227456 bytes

Variable Size 188744448 bytes

Database Buffers 67108864 bytes

Redo Buffers 4968448 bytes

Database mounted.

Database opened.

Now, we are able to query the database.

SQL> select file#,tablespace_name,name,status from v$datafile_header;

FILE# TABLESPACE_NAME NAME STATUS

---------- ------------------------------ ----------------------------------------------------------------- -------

1 SYSTEM /data1/noasmdb/datafile/system01.dbf ONLINE

2 SYSAUX /data1/noasmdb/datafile/sysaux01.dbf ONLINE

3 TEST_BKP /data1/noasmdb/datafile/testbkp01.dbf ONLINE

5 UNDOTBS /data1/noasmdb/datafile/undotbs01.dbf ONLINE

6 NEWTEST /data1/noasmdb/datafile/newtest01.dbf ONLINE

7 NOVALIDTBS /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE

7 rows selected.

SQL> select file#,name,STATUS,TS#,block_size from v$datafile;

FILE# NAME STATUS TS# BLOCK_SIZE

---------- ----------------------------------------------------------------- ------- ---------- ----------

1 /data1/noasmdb/datafile/system01.dbf SYSTEM 0 8192

2 /data1/noasmdb/datafile/sysaux01.dbf ONLINE 1 8192

3 /data1/noasmdb/datafile/testbkp01.dbf ONLINE 7 8192

4 /data1/noasmdb/datafile/users01.dbf ONLINE 4 8192

5 /data1/noasmdb/datafile/undotbs01.dbf ONLINE 6 8192

6 /data1/noasmdb/datafile/newtest01.dbf ONLINE 8 8192

7 /data1/noasmdb/datafile/novalidtbs01.dbf ONLINE 9 8192

7 rows selected.

[oracle@vm1 backup]$ rman target sys/sys catalog=rman_cat/rman_cat@testdb

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 14 14:27:16 2014

connected to target database: NOASMDB (DBID=1704132256)

connected to recovery catalog database

RMAN>

Conclusion

If one of the multiplexed control file is lost, we need to perform following.

1. Shutdown the database

2. Copy the unaffected files.

3. Start the database.

Step by Step procedure to restore database to another host using RMAN

Before going to restore the database, we need to install the binaries for Oracle database and ASM (if used for storage). Also, create the required directories like adump,bdump, cdump, pfile, redo log files, etc. The scenario is as follows.

Ø The database is old server got crashed and this was using Server Parameter file (SPfile)

Ø We have another fresh server with media manager installed to make the facility restore from media manager.

Ø The database name of the new db should be as old database which got crashed.

Ø We have all recoverable backups on tape of all datafiles.

Ø We have backups of all archived logs to recover the datafiles.

Ø We have controlfile and spfile backup available on tape.

1. To restore a database to another host using RMAN

Enter the following commands to obtain the db_id value (database ID) of the database you want to restore from the RMAN catalog:

sqlplus <rman user>/<rman password>@<rman service>

SQL> select db_key, db_id, bs_key, recid, stamp, backup_type, start_time, status from rc_backup_set;

Identify the db_id value corresponding to the database you want to restore.

If you are not able to access rman catalog, the another method is to find the db id from the controlfile autobackup format configured to disk using

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO '?/oradata/cf_%F';

The format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of FFFor example, you can run the following command:

By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of FF

2. Set the ORACLE_SID environment variable on Target Host to the same value used on Original/old/source host

[oracle@vm1 backup]$ export ORACLE_SID=trgt db_name>

3. Start RMAN and connect to the target instance without connecting to the recovery catalog.

[oracle@vm1 backup]$ rman target / nocatalog

4. Enter the following command to start the destination database with the nomount option:

RMAN> set dbid=<source database db_id value>;

RMAN> startup nomount;

RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file. Sample output follows:

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittrgt.ora'

starting Oracle instance without parameter file for retrieval of spfile

Total System Global Area 158662656 bytes

Fixed Size 2226456 bytes

Variable Size 104859368 bytes

Database Buffers 46137344 bytes

Redo Buffers 5439488 bytes

5. Restore and edit the server parameter file.

Because you enabled the control file autobackup feature when making your backups, the server parameter file is included in the backup sets.

Allocate a channel to the media manager, then restore the server parameter file (SPFILE) as a client-side pararameter file (PFILE).

RMAN> run

{ allocate channel c1 device type sbt_tape PARMS='ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)';

restore spfile to pfile '/u01/app/oracle/admin/trgt/pfile/inittrgt.ora' from autobackup;

shutdown abort;

}

6. Next, edit the restored PFILE. Change any location-specific parameters, for example, those ending in _DEST and _PATH, to reflect the new directory structure. For example, edit the following parameters:

- IFILE

- *_DUMP_DEST

- LOG_ARCHIVE_DEST*

- CONTROL_FILES

7. Then restart the instance, using the edited PFILE:

startup force nomount ='/u01/app/oracle/admin/trgt/pfile/inittrgt.ora';

8. Restore the control file from an autobackup and then mount the database. RMAN restores the control file to whatever location you specified in the CONTROL_FILES initialization parameter. For example:

RUN

{

allocate channel c1 device type sbt_tape PARMS='ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)';

restore controlfile from autobackup;

alter database mount;

}

9. Query the database filenames recorded in the control file on the new host. Note, the controlfile was restored in step 8.

sqlplus ‘ / as sysdba’

SQL > set lines 150

SQL > col name format a60

SQL> select file# ,name from v$datafile;

SQL> col member format a60

SQL >select group#,member from v$logfile;

SQL> exit

10. Now, from the above output , we need create RMAN script to recover the database.

Ø For each datafile, if we need to have different path in the new host, we need to user SET NEWNAME command to specify the different path in the host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.)

Ø For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.)

Ø Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.

Ø Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles

Ø Restore and recover the database

[oracle@vm1 ~]$ export ORACLE_SID=trgt

[oracle@vm1 ~]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 20 11:57:26 2014

The RMAN command to execute is as follows when we want to rename the datafiles and redo log file locations.

RUN

{

# allocate a channel to the tape device

ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS=''ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=trgt,OB2BARLIST=Hot-backup,OB2BARHOSTNAME=newhost)'';

# rename the datafiles and online redo logs

SET NEWNAME FOR DATAFILE 1 TO '/data1/trgt/datafile/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO /data1/trgt/datafile/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '/data1/trgt/datafile/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 4 TO '/data1/trgt/datafile/users01.dbf';

SET NEWNAME FOR DATAFILE 5 TO '/data1/trgt/datafile/newtest01.dbf';

SET NEWNAME FOR DATAFILE 6 TO '/data1/trgt/datafile/blkcorrupt.dbf';

SET NEWNAME FOR DATAFILE 7 TO '/data1/trgt/datafile/test_uncommit01.dbf';

SET NEWNAME FOR DATAFILE 8 TO '/data1/trgt/datafile/users01.dbf';

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g01a.log''

TO ''/data1/trgt/redofiles/redo01b.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g01b.log''

TO ''/data1/trgt/redofiles/redo01b.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g02a.log''

TO ''/data1/trgt/redofiles/redo02a.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g02b.log''

TO ''/data1/trgt/redofiles/redo02b.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g03a.log''

TO ''/data1/trgt/redofiles/redo03a.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/data1/noasmdb/redofiles/redo_g03b.log''

TO ''/data1/trgt/redofiles/redo03b.log'' ";

# restore the database and switch the datafile names

RESTORE DATABASE;

SWITCH DATAFILE ALL;

# recover the database

RECOVER DATABASE;

}

RMAN commands to restore & recover the database in the same path .

RMAN> restore database;

RMAN>recover database;

11. Open the database with RESETLOGS options.

RMAN>alter database open resetlogs;

12. Once the database is opened, take a fresh backup immediately.

Friday, May 23, 2014

Block Recovery using RMAN with Valid backup when datafile header is not corrupted

In this demo, we will see how to perform a block recovery using RMAN utility.

Block recovery feature can be used when database is a 24/7 and cannot be shutdown. We can use block recovery either in catalog-mode or no-catalog mode.

Catalog-mode might be required if we need to restore from an old backup which is not known to RMAN repository information in the control file anymore.

Block corruption errors are not detected in oracle until we use dbv (db verify),RMAN,analyze or export command.

Important views for checking block corruption

a. v$database_block_corruption

Steps.

1. Create a table in users tablespace

SQL> create table blktest as select * from dba_tables;

Table created.

2. Get the count of the records.

SQL> select count(1) from blktest;

COUNT(1)

----------

2777

3. Before corrupting the block, we will have all these information (Just for information).

Identify the blocks belonging to that table.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from blktest) where rownum <10;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

------------------------------------

299

300

301

302

303

304

305

306

307

9 rows selected.

SQL> select file#,blocks,name from v$datafile;

FILE# BLOCKS NAME

---------- ---------- ---------------------------------------------

1 92160 /data1/noasmdb/datafile/system01.dbf

2 72960 /data1/noasmdb/datafile/sysaux01.dbf

3 448000 /data1/noasmdb/datafile/testbkp01.dbf

4 800 /data1/noasmdb/datafile/users01.dbf

5 25600 /data1/noasmdb/datafile/undotbs01.dbf

SQL> select * from v$database_block_corruption;

no rows selected

4. Now, we will corrupt the blocks for USERS tablespace. (Simulating block corruption)

SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||

to_number(S.HEADER_BLOCK + 1) || ' << EOF',

'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',

'EOF'

FROM DBA_SEGMENTS S, dba_data_files f

WHERE f.tablespace_name = 'USERS'

and S.SEGMENT_NAME = 'BLKTEST'

dd of=/data1/noasmdb/datafile/users01.dbf bs=8192 conv=notrunc seek=299 << EOF

CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT

CORRUPT CORRUPT corrupt corrupt

EOF

[oracle@vm1 backup]$ dd of=/data1/noasmdb/datafile/users01.dbf bs=8192 conv=notrunc seek=299 << EOF

> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT

> CORRUPT CORRUPT corrupt corrupt

> EOF

0+1 records in

0+1 records out

112 bytes (112 B) copied, 0.000192 seconds, 583 kB/s

5. Check the block is corrupted.

SQL> alter system flush buffer_cache;

System altered.

6. To confirm, we will try to query the table now and let’see what is the result is.

select count(1) from blktest

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 299)

ORA-01110: data file 4: '/data1/noasmdb/datafile/users01.dbf'

7. Alert log give following message.

Hex dump of (file 4, block 299) in trace file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_ora_10185.trc

Corrupt block relative dba: 0x0100012b (file 4, block 299)

Bad header found during multiblock buffer read

Data in bad block:

type: 67 format: 7 rdba: 0x20545055

last change scn: 0x7075.72726f63 seq: 0x74 flg: 0x20

spare1: 0x52 spare2: 0x52 spare3: 0x5252

consistency value in tail: 0x8ac00601

check value in block header: 0x4f43

block checksum disabled

Reading datafile '/data1/noasmdb/datafile/users01.dbf' for corruption at rdba: 0x0100012b (file 4, block 299)

Reread (file 4, block 299) found same corrupt data (no logical check)

Hex dump of (file 4, block 303) in trace file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_ora_10185.trc

Corrupt block relative dba: 0x0100012f (file 4, block 303)

Completely zero block found during multiblock buffer read

Reading datafile '/data1/noasmdb/datafile/users01.dbf' for corruption at rdba: 0x0100012f (file 4, block 303)

Reread (file 4, block 303) found same corrupt data (no logical check)

Tue Feb 11 12:05:18 2014

Corrupt Block Found

TSN = 4, TSNAME = USERS

RFN = 4, BLK = 299, RDBA = 16777515

OBJN = 75642, OBJD = 75642, OBJECT = BLKTEST, SUBOBJECT =

SEGMENT OWNER = TEST1, SEGMENT TYPE = Table Segment

Corrupt Block Found

TSN = 4, TSNAME = USERS

RFN = 4, BLK = 303, RDBA = 16777519

OBJN = 75642, OBJD = 75642, OBJECT = BLKTEST, SUBOBJECT =

SEGMENT OWNER = TEST1, SEGMENT TYPE = Table Segment

Tue Feb 11 12:05:20 2014

Checker run found 1 new persistent data failures

Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_ora_10185.trc (incident=19475):

ORA-01578: ORACLE data block corrupted (file # 4, block # 299)

ORA-01110: data file 4: '/data1/noasmdb/datafile/users01.dbf'

Incident details in: /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/incident/incdir_19475/noasmdb_ora_10185_i19475.trc

Tue Feb 11 12:05:32 2014

Dumping diagnostic data in directory=[cdmp_20140211120532], requested by (instance=1, osid=10185), summary=[incident=19475].

Tue Feb 11 12:05:33 2014

Sweep [inc][19475]: completed

Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/incident/incdir_19475/noasmdb_m000_10248_i19475_a.trc:

ORA-19583: conversation terminated due to error

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/data1/noasmdb/datafile/users01.dbf'

ORA-01565: error in identifying file '/data1/noasmdb/datafile/users01.dbf'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

8. You can see, we can’t find any information in v$database_block_corruption view eventhough we have one of our datafile corrupted.

SQL> select * from v$database_block_corruption;

no rows selected

9. Now , let us update v$database_block_corruption by below procedure.

RMAN> backup validate tablespace users;

Starting backup at 11-FEB-14

starting full resync of recovery catalog

full resync complete

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/11/2014 12:37:29

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/data1/noasmdb/datafile/users01.dbf'

ORA-01565: error in identifying file '/data1/noasmdb/datafile/users01.dbf'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

10. Let us use dbverify utility to check the corruption.

[oracle@vm1 backup]$ dbv file=/data1/noasmdb/datafile/users01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Tue Feb 11 12:11:39 2014

DBV-00600: Fatal Error - [21] [5] [0] [0]

11. From the above messages when querying for the table, we find the file#4 and block# 299 is corrupted.

12. Now login to target database using RMAN

RMAN> blockrecover datafile 4 block 299;

Starting recover at 11-FEB-14

starting full resync of recovery catalog

full resync complete

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

searching flashback logs for block images until SCN 1145158

finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/FullDBNOASM_3bp0biap_1_1

channel ORA_DISK_1: piece handle=/home/oracle/backup/FullDBNOASM_3bp0biap_1_1 tag=FULL DB

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:04

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 11-FEB-14

13. Once the above step is done, check the count of the blktest table now.

SQL> select count(1) from blktest;

COUNT(1)

----------

2777

Conclusion :

When a block is corrupted, there is no need taking the tablespace offline. If we have a valid backup, we can recover the block and TABLE can be recovered back to normal except when the FIRST block of the datafile with datafile header is

DBA

Pages

Sunday, June 22, 2014

Recovery of the database when any one of the controlfile is lost

Step by Step procedure to restore database to another host using RMAN

Friday, May 23, 2014

Block Recovery using RMAN with Valid backup when datafile header is not corrupted

Pages

Blog Archive