Wednesday, February 23, 2011

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure
========================================


Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later


Problem:- (trying to upgrade a older database via patch set fail.)
===================================================================


today i face this problem when my junior dba trying to ready a test
database with oracle database 10.2.0.3 version .

first he install oracle 10.2.0.3 then
he trying to install patch set for 10.2.0.4 .

but somehow the installation of patch set fail...then he found this problem.



Recovery of an older database version backup results in error upon database open:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
....
ORA-01092: ORACLE instance terminated. Disconnection forced



Cause
===================

Error occurs because the current database version is newer than the backup datafiles, upgrade
steps must be taken which require the database to be started with the UPGRADE option.

When a database is upgraded from one version to another, the database must be started with UPGRADE
option to execute the necessary upgrade steps. The database cannot be opened in read write under
the new version until these steps are taken. This justifies cause.

If incomplete recovery is required, the database should be opened with:

SQL> alter database open resetlogs upgrade;

If an attempt to open the database without the upgrade option, the error will occur. This can be resolved with the steps documented below.


========================================================
Solution
========================================================


To implement the solution, please execute the following steps:

1. Perform restore and recovery.(if necessary)

or can do

 SQL> startup nomount pfile=d:/pfile/init.4646464666.ora ;  
SQL> alter database mount ; --or alter database mount exclusive ;
SQL> alter database open upgrade ;
SQL> @catupgrd.sql ---and catproc.sql---(from oracle_home/rdbms/admin/)
----Perform necessary upgrade steps as documented in the readme file for the
----patch/upgrade you applied. For example, catupgrd.sql.
SQL> create spfile from pfile='d:/pfile/init.4646464666.ora' ;
SQL> shutdown immediate ;
SQL> startup ;
SQL> ---take a backup




NOTE: any attempts to recover after the upgrade steps (especially with 'using backup
controlfile') will result in error. In addition, there is no need to use the RESETLOGS option
upon open. The key here is after the upgrade, the database should be opened with STARTUP.
Instance recovery and the fact that the controlfile is now CURRENT, will allow Oracle to apply the
necessary information in the online redo log files and startup the database.

Tuesday, February 22, 2011

How to determine the correct setting for JOB_QUEUE_PROCESSES

How to determine the correct setting for JOB_QUEUE_PROCESSES
============================================================
For this, you can create a table to insert the count value of running job in a particular time. then monitor few days or weeks , then decide what would be the correct value........
like below.

Parameter JOB_QUEUE_PROCESSES is maximum number of jobs that would ever be run concurrently on your Database.

1) First set JOB_QUEUE_PROCESSES higher than the most number of jobs that could ever run concurrently

2) Login as SYS

3) Create the table to do the monitoring

CREATE TABLE highest_concurrent_job (
SAMPLE_DATE DATE,
JOBS_RUNNING NUMBER);

4) Create the job to do the monitoring

CREATE OR REPLACE PROCEDURE DRP_highest_concurrent_job IS
BEGIN
INSERT INTO highest_concurrent_job
SELECT SYSDATE, COUNT(*) FROM  DBA_JOBS_RUNNING;
COMMIT;
END;
/

5) Create the job queue job to do the monitoring

DECLARE
v_jobno number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => v_jobno,
WHAT => 'DRP_highest_concurrent_job;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

6) select * from MAX_CONCURRENT_JOBS ;