Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Untouchable Parameters


Oracle Tips by Burleson
 

In contrast to parameters that must be changed, there are those that should never be changed. Let’s look at a few of them.

_COMPATIBLE_NO_RECOVERY. Usually set to 0.0.0, which defaults to the current version. If you set it to something else and the DB crashes, you may need to do a media recovery instead of just an instance recovery.

_ALLOW_ERROR_SIMULATION. Used by Oracle for internal testing. If you set it, there’s no telling what it will do to your instance.

_DB_BLOCK_CACHE_PROTECT. Will cause a db crash rather than let corruption get to the database. It may result in many ORA-0600 errors and other unpleasant things if set in a regular production database. This is for debugging only!

_IPC_FAIL_NETWORK. Simulates network failure; for testing only.

_IPC_TEST_FAILOVER. Tests transparent cluster network failover; for testing only.

_IPC_TEST_MULT_NETS. Simulates multiple cluster networks; for testing only.

_LOG_BUFFERS_CORRUPT. Corrupts redo buffers after write; for testing only.

_MTS_LOAD_CONSTANTS. A complex set of constants that governs the multithreaded server load balancing. It contains six different values dealing with how the load is balanced across servers and dispatchers.

_CPU_TO_IO. The multiplier for converting CPU cost to I/O cost. Change this and you will directly affect the CBO cost calculation.

_LOG_BUFFERS_CORRUPT. Corrupts redo buffers before write; used only for testing. A sure way to bring your database to its knees is to set this to TRUE.

_SINGLE_PROCESS. Run without detached processes; if you want single-user Oracle, this will give it to you.

_WAIT_FOR_SYNC. Wait for checkpoint sync on commit must always be TRUE; if set to FALSE, will cause mismatch between headers and SCN on DB crash or shutdown abort.

_NO_OBJECTS. Tells Oracle that no objects are being used; set to FALSE. If you set it to TRUE, Oracle will probably crash since the data dictionary uses objects.

_PMON_LOAD_CONSTANTS. As with MTS, these are PMON Server load-balancing constants and directly affect the operation of PMON; don’t mess with them.

This list contains only those parameters that stand out. There are many more that, if you change them, will have a negative effect on how Oracle behaves. When in doubt, don’t touch it!

Recovering Using _ALLOW_RESETLOGS_CORRUPTION

Let’s now look at a detailed example using _allow_resetlogs_corruption to recover a database. Recovery of a database using the undocumented parameter _allow_resetlogs_corruption should be regarded as a last-ditch, emergency recovery scenario only, and should not be attempted until all other avenues of recovery have been exhausted.

Note: Oracle will not support a database that has been recovered using this method unless it is subsequently exported and rebuilt.

Essentially, using _allow_resetlogs_corruption forces the opening of the datafiles even if their SCNs do not match up; then, on the next checkpoint, the old SCN values are overwritten. This could leave the database in an unknown state as far as concurrency.

This type of recovery is usually required when a datafile has been left in hot backup mode through several backup cycles without an intervening shutdown and startup. Upon shutdown and startup, the database will complain that a file (usually file id#1 the SYSTEM tablespace) needs more recovery, and asks for logs past all available archive logs and online logs.

An alternative scenario would be that the database is recovered from a hot backup and the above scenario occurs, or that the database asks for an archive log dated earlier than any that are available (usually for the rollback segment tablespace datafiles.) I have also seen this happen when creating a standby database using a hot backup.

A typical error stack would resemble:

SVRMGR> connect internal

Connected.

SVRMGR> @sycrectl

ORACLE instance started.

Total System Global Area 113344192 bytes

Fixed Size 69312 bytes

Variable Size 92704768 bytes

Database Buffers 20480000 bytes

Redo Buffers 90112 bytes

Statement processed.

ALTER DATABASE OPEN resetlogs

*

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u03/oradata/tstc/dbsyst01.dbf'
       Or:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 48 needs more recovery to be consistent

ORA-01110: data file 48: '/vol06/oradata/testdb/ard01.dbf'

If all available archive logs and all available online redo logs are applied, and the error is not corrected, only then should you consider using the parameter _allow_resetlogs_corruption. Make sure a good backup of the database in a closed state (all files) is taken before attempting recovery using this parameter.

Note: It cannot be stressed firmly enough that the database will no longer be supported by Oracle until it is rebuilt after using _allow_resetlogs_corruption for recovery.

Procedure

The following details the recovery process using _allow_resetlogs_corruption:

1.      If no recovery attempts have been made, shut down and back up the database (all files) as-is to provide a fallback position should recovery fail.

2.      If recovery attempts have been made, recover the database to the state just before any other recovery attempts were made.

3.      Use svrmgrl, sqlplus, or appropriate interface to start up the database in a mounted, but not open, condition:

a.   STARTUP MOUNT

4.      Ensure all datafiles are set to END BACKUP status:

a.   SET PAGES 0 FEEDBACK OFF LINES 132

b.   SPOOL alter_df.sql

c.   SELECT ‘alter database datafile ‘||file_name||’ END BACKUP;’ from v$datafile;

d.   SPOOL OFF

e.   @alter_df.sql

5.      Alter the database into open condition:

a.   ALTER DATABASE OPEN;

6.      If the database asks for recovery, use an UNTIL CANCEL-type recovery and apply all available archive and online redo logs; then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; commands.

7.      If the database asks for logs that are no longer available, or the preceding still resulted in errors, shut down the database.

8.      Insert into the initialization file the following line:

a.   _allow_resetlogs_corruption=TRUE

9.      Use svrmgrl, sqlplus, or appropriate interface to start up the database in a mounted, but not open, condition:

a.   STARTUP MOUNT

10.  Ensure all datafiles are set to END BACKUP status:

a.   SET PAGES 0 FEEDBACK OFF LINES 132

b.   SPOOL alter_df.sql

c.   SELECT ‘alter database datafile ‘||file_name||’ END BACKUP;’ from v$datafile;

d.   SPOOL OFF

e.   @alter_df.sql

11.  Alter the database into open condition:

a.   ALTER DATABASE OPEN;

12.  If the database asks for recovery, use an UNTIL CANCEL-type recovery and apply all available archive and online redo logs; then issue the CANCEL and reissue the ALTER DATATBASE OPEN RESETLOGS; commands.

13.  Once the database is open, immediately do a full export of the database or an export of the schemas you need to recover.

14.  Shut down the database and remove the parameter _allow_resetlogs_corruption.

15.  Rebuild the database.

16.  Import to finish the recovery.

17.  Implement a proper backup plan and procedure.

18.  It may be advisable to perform an ANALYZE TABLE…VALIDATE STRUCTURE CASCADE on critical application tables after the recovery and before the export.

Note: Uncommitted records that had been written to disk will possibly be marked as committed by this procedure.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter