Dbvisit Community Dbvisit home       

Go Back   Dbvisit Community > Standby Database and Disaster Recovery > Standby Database General Discussion

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 03-28-2008, 03:19 PM
Arjen Visser Arjen Visser is offline
Dbvisit Team
 
Join Date: Jan 2008
Location: Auckland, New Zealand
Posts: 380
Default Check for unrecoverable changes in datafiles

SQL query to check for unrecoverable changes in datafiles on the standby database:

SQL> select name, unrecoverable_change#, unrecoverable_time from v$datafile;

If unrecoverable_change# is not 0 that indicates that unrecoverable/nologging operations were used with object(s) in that datafile. In that case, if unrecoverable_time shows date after you created the standby database, then when you activate the standby database you will probably get the following error messages for one or more objects in that datafile:

ORA-01578: ORACLE data block corrupted (file # string, block # string)
ORA-01110: data file string: "string"
ORA-26040: Data block was loaded using the NOLOGGING option


Nologging operations should not be used with objects in that datafile.
Oracle introduced the "force logging" feature in 9.2 to avoid this problem.
SQL> ALTER DATABASE FORCE LOGGING;

In order to fix the issue follow the following steps:

To determine which datafile needs to be copied from the primary to the standby:
1.
Query the primary database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/tbs01_1.dbf 5216
/oracle/oradata/dbs/tbs01_2.dbf 0
/oracle/oradata/dbs/tbs01_3.dbf 0
/oracle/oradata/dbs/tbs01_4.dbf 0
4 rows selected.


2.
Query the standby database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/oradata/dbs/stdby/tbs_1.dbf 5186
/oracle/oradata/dbs/stdby/tbs_2.dbf 0
/oracle/oradata/dbs/stdby/tbs_3.dbf 0
/oracle/oradata/dbs/stdby/tbs_4.dbf 0
4 rows selected.

3.
Compare the query results of the primary and standby databases.
Compare the value of the UNRECOVERABLE_CHANGE# column in both query results.
If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database,
then the datafile needs to be copied from the primary site to the standby site.
In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs01_1.dbf datafile is greater, so you need to copy the tbs01_1.dbf datafile to the standby site.

When copying the datafile, use the following method:
SQL> ALTER TABLESPACE tbs01 BEGIN BACKUP;
SQL> EXIT;
% scp tbs01_1.dbf oracle@standby_server:/backup
SQL> ALTER TABLESPACE tbs01 END BACKUP;


Shutdown the standby database and replace the existing datafile with the new datafile (make a backup copy first).
Restart the standby database and run Dbvisit.

Avoid this issue in future by setting your primary database to force logging:
SQL> ALTER DATABASE FORCE LOGGING;

Last edited by Arjen Visser; 09-17-2010 at 11:36 AM.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT +12. The time now is 04:31 AM.