 |
|
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. |
|
