When encountered database corruption. There are basically a couple of things that we can do. We will not describe the details here but here are some links to some of the documentation ( for 10gR2 )
1. DBVERIFY: Offline Database Verification Utility
This is a utility provided by Oracle to verify the status of your datafiles but does not repair the corrupted block. What it does is basically scanning through the datafile and marking the corrupted blocks.
If you suspect that a datafile is corrupted. Do a cold backup before you run this utility.
See more :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbverify.htm#sthref1830
2. Using DBMS_REPAIR to Repair Data Block Corruption
A DBMS_REPAIR PL/SQL package provide by Oracle to repair data block corruption in database schema objects. However it is not necessary that it is able to recover all the data. Sometimes DBMS_REPAIR just makes the object usable by ignoring corruptions during table and index scans.
Taking note that DBMS_REPAIR does not work with LOBs.
See more :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#i1006505
3. If you have a proper RMAN backup, you might be able to recover the corrupted block using RMAN. I have not tried this but below is a documentation with some details on how it can be done :
http://www.scribd.com/doc/2674976/Oracle-Database-Corruption
Monday, March 22, 2010
Sunday, March 21, 2010
Oracle : ORA-01578: ORACLE data block corrupted, ORA-26040: Data block was loaded using the NOLOGGING option
When a object is set to nologging, it formats complete blocks of data itself, and writes them directly to the data file. Each time a commit is issued, the object's high water mark is raised so the new blocks are included in the object. These blocks do not go through the buffer cache in the Shared Global Area (SGA). The redo logs do not contain enough data to make the corresponding changes on a standby database, or roll forward in a recovery event. They only contain enough information to maintain the data dictionary and mark the new blocks as "invalidated" due to a nologging action.
Instance recovery uses the online redo logs to reconstruct the SGA after a crash, and to roll forward through the committed changes reflected there to make sure the data blocks are good. Since nologging operations write directly to the data files - bypassing the SGA - and each commit raises the high water mark to include the new blocks, instance recovery does not result in invalidated blocks on your production database. If a nologging operation is executing and the database suffers a crash, no new problems will be introduced to the environment due to nologging.
However, Oracle's database media recovery and Data Guard standby database technologies rely on the archived redo logs generated from database operations to replicate all changes to the data files. When you recover from a backup or activate a standby, you must roll forward through the redo logs to recreate these changes, to bring the restored or standby database to the desired point in time. Since nologging operations write directly to the data files and bypass the redo logs, rolling forward through redo for a nologging operation will result in invalidated blocks on your restored or standby database.
After you open your database, you will see the following errors if you rolled forward through a nologging operation while recovering from a backup or opening a standby:
ORA-1578: ORACLE data block corrupted (file # %s, block # %s)
ORA-26040: Data block was loaded using the NOLOGGING option
To make the standby or backup completely usable after a nologging statement is run, a mechanism other than database recovery must be used to get or create current copies of the affected blocks. There are two options, each of which may be appropriate depending on the specific circumstances:
* Create a new copy of the data files, either by backing up the tablespace again or by refreshing the specific data files on the standby.
* Drop and recreate the object with the invalidated blocks, using the program that maintains the object.
Release 9.2 of the database includes the ability to force logging across the database, or for particular tablespaces. With this or a later version, you can use the “force logging” feature to make sure all data changes are written to your database redo logs in a way that can be replayed on your restored backup, or propagated to a physical standby. You should test patches and affected production jobs with this feature invoked to be sure the performance impact is acceptable, before putting it into production. If you do this, you no longer need to do the work described in this document.
Nologging in the E-Business Suite [ID 216211.1]
Although oracle provides DBMS_REPAIR PL/SQL package to repair data block corruption in database schema objects, it is not necessary that it is able to recover all the data. Sometimes DBMS_REPAIR makes the object usable by ignoring corruptions during table and index scans. Taking note that DBMS_REPAIR does not work with LOBs.
It is therefore possible that there are no way to recover the data in an event of corruption as a result of the above described. So as a good practice to look out for nologging objects if the database is replicating using Dataguard.
Instance recovery uses the online redo logs to reconstruct the SGA after a crash, and to roll forward through the committed changes reflected there to make sure the data blocks are good. Since nologging operations write directly to the data files - bypassing the SGA - and each commit raises the high water mark to include the new blocks, instance recovery does not result in invalidated blocks on your production database. If a nologging operation is executing and the database suffers a crash, no new problems will be introduced to the environment due to nologging.
However, Oracle's database media recovery and Data Guard standby database technologies rely on the archived redo logs generated from database operations to replicate all changes to the data files. When you recover from a backup or activate a standby, you must roll forward through the redo logs to recreate these changes, to bring the restored or standby database to the desired point in time. Since nologging operations write directly to the data files and bypass the redo logs, rolling forward through redo for a nologging operation will result in invalidated blocks on your restored or standby database.
After you open your database, you will see the following errors if you rolled forward through a nologging operation while recovering from a backup or opening a standby:
ORA-1578: ORACLE data block corrupted (file # %s, block # %s)
ORA-26040: Data block was loaded using the NOLOGGING option
To make the standby or backup completely usable after a nologging statement is run, a mechanism other than database recovery must be used to get or create current copies of the affected blocks. There are two options, each of which may be appropriate depending on the specific circumstances:
* Create a new copy of the data files, either by backing up the tablespace again or by refreshing the specific data files on the standby.
* Drop and recreate the object with the invalidated blocks, using the program that maintains the object.
Release 9.2 of the database includes the ability to force logging across the database, or for particular tablespaces. With this or a later version, you can use the “force logging” feature to make sure all data changes are written to your database redo logs in a way that can be replayed on your restored backup, or propagated to a physical standby. You should test patches and affected production jobs with this feature invoked to be sure the performance impact is acceptable, before putting it into production. If you do this, you no longer need to do the work described in this document.
Nologging in the E-Business Suite [ID 216211.1]
Although oracle provides DBMS_REPAIR PL/SQL package to repair data block corruption in database schema objects, it is not necessary that it is able to recover all the data. Sometimes DBMS_REPAIR makes the object usable by ignoring corruptions during table and index scans. Taking note that DBMS_REPAIR does not work with LOBs.
It is therefore possible that there are no way to recover the data in an event of corruption as a result of the above described. So as a good practice to look out for nologging objects if the database is replicating using Dataguard.
Wednesday, March 17, 2010
Oracle : In 10g listener log WARNING: Subscription for node down event still pending
Symptoms of the Problem
In the listener log file you constantly get the following warning message.
WARNING: Subscription for node down event still pending
If you have oracle database greater than 10g or newer version or 11g then in the listener log file you get the warning message.
Cause of the Problem
The warning messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). This subscription to ONS is introduced in Oracle 10g for RAC environment. Listener subscription to ONS is useful to use advanced features like Fast Application Notification events(FAN) , Fast Application Fail over (FAF) and Fast Connection Failover (FCN) in RAC. So in a non-RAC environment subscription to ONS is not needed. So in a standalone system we can disable it and thus avoid warning message.
Solution of the Problem
Disable subscription for listener to ONS. This can be done by setting the following parameter in the listener.ora.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
After that you need to stop and start the listener by,
lsnrctl stop
lsnrctl start
Alternatively you can reload the listener if availability is important to you. Do it just by,
lsnrctl reload
This will prevent the messages from being written to the log file.
This changes also prevent the TNS listener hanging intermittently which will be discussed in another topic.
Note that, setting the above parameter OFF in listener.ora disables a necessary RAC functionality.
http://www.ora600.be/node/1206
Subscribe to:
Posts (Atom)