Fixing ORA-01113: file x needs media recovery Error

The following steps are intended for a quick recovery of the database.

Symptom:
Oracle mounts the database, but refuses to open the database with an ORA-01113: file x needs media recovery Error.
% sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 24 09:29:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startupORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 1981064 bytes
Variable Size 436209016 bytes
Database Buffers 2231369728 bytes
Redo Buffers 14794752 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/OraData/SIAMST/siamst_system.dbf'
% oerr ora 01113
01113, 00000, "file %s needs media recovery"
// *Cause: An attempt was made to online or open a database with a file that
// is in need of media recovery.
// *Action: First apply media recovery to the file.

Resolution:

1. Startup the database with the mount option
SQL> startup mount
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size 1981064 bytes
Variable Size 436209016 bytes
Database Buffers 2231369728 bytes
Redo Buffers 14794752 bytes
Database mounted.

2. Find the name of the redo log file which belongs to the active group
SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# 2 AND G.STATUS = 'CURRENT';
MEMBER--------------------------------------------------------------------------------/OraRedo/RedoLogFiles/siamst_log01.dbf

3. Using a backup control file, start the cancel based recovery. The system may suggest a non-existing archive log
-- ignore it and specify the log file name {with full path} that you got in the above step
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 21375038 generated at 09/21/2007 20:12:47 needed for thread 1
ORA-00289: suggestion : /export/pspp/oracle/dbs/arch1_4_633901491.dbf
ORA-00280: change 21375038 for thread 1 is in sequence #4
Specify log: {=suggested filename AUTO CANCEL}
/OraRedo/RedoLogFiles/siamst_log01.dbf
Log applied.
Media recovery complete.

4. Finally open the database in RESETLOGS mode.
It is recommended to reset the online logs after incomplete recovery or recovery with a backup control file
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

Take a backup of the database as soon as the recovery is done.

Overview of 10g Application Server Installation

– infrastructure & Middle Tier Should (MUST) be installed under separate Oracle Homes. Though they can be on Same Physical Machine or on different Machines
– First you have to install Infrastructure Tier then only you can install Middle Tier
– You can install Portal/Wireless or IM Repository in an existing database (Choose RepCA, Repository Creation Assistance tool for this option. If you are new to 10g AS skip this for time being as this is optional feature)
– Before Installation, Make sure all prereq. are complete (Check installation Document mentioned at bottom of this page)
– You can use eDelivery.oracle.com for software or use link http://www.oracle.com/technology/software/htdocs/devlic.html?=/technology/software/products/ias/htdocs/101202.html
– For Portal 10.1.4 , you need 10.1.2.0.2 & then Upgrade portal to 10.1.4 (P.S. Portal version is different than Application Server Version)

Options in Installation

While Installing Infrastructure Tier, you will get three options
- Identity Management & Metadata Repository
- Identity Management only
- Metadata Repository only
If you are new select option 1 i.e. IM+MR as option 2 & 3 are for advanced configuration like Infrastructure on Multiple Machines

While Installing Middle Tier, you will again get three options
- J2ee & Webcache
- Portal & Wireless
- Business Intelligence & Forms

BI & Form type Installation include first two options so this selection will include all J2ee, Webcache, Portal, Wireless, BI & Forms

Portal & Wireless include first option so this selection will include J2ee, Webcache, Portal, Wireless

J2ee & Webcache will install only j2ee & webcache. So if you are not aware of at this minute what you want better select option three i.e. Business Intelligence & Forms

Installation Document
Installation Document from oracle for Application Server version 10.1.2.0.2 is available at
http://download-uk.oracle.com/docs/cd/B14099_19/getstart.htm . Select as per your Operating System.

Hope this helps.....

Overview of 10g Application Server

Overview of 10g Application Server
10g Application Server Consist of Infrastructure Tier & Middle Tier.

What is Infrastructure Tier ?
Infrastructure Tier provides Infrastructure Services to 10g Application Server & consist of following technical components
- Database : To Store User/Applications Identity Data (Portal,Wireless,OID data)
- OID : Oracle Internet Directory, to store User Credentials, Schema passwords, Application details & lot more ...
- HTTP Server : This Web Server (OHS, Oracle HTTP Server) on Infra Tier is used to access SSO (Single Sign-On Server) & OIDDAS (OID Delegated Administrative Services)
- OC4J : Oracle Container for J2EE (OC4J_Security for SSO), You have additional OC4J_OCA if you have OCA (Oracle Certifying Autority). OCA is Optional component in Infrastructure Tier.

What is Middle Tier ?
Middle Tier is tier where application Server & applications logic resides. It consist of following Technical Components
- HTTP Server : This OHS (Oracle HTTP Server) is separate from above OHS & this is OHS from where users access application Server like portal, wireless, j2ee
- OC4J : These are container which provide place holder for j2ee or web applications . Applications are deployed on OC4J's. 10g AS comes with default OC4J name "home", you create more OC4J depending on your requirement
- Web Cache : Web Cache acts as cache for fast response to web server.

Logical Services for Infrastructure Tier
Above was technical components of Infrastructure Tier , Logically Infrastructure Tier consist of Two main categories which are
1. Identity Management
2.Metadata Repository

What is Metadata Repository?
MR is similar to Database Metadata & MR you can further classify as Metadata repository for Products (Like Portal, Wireless, BI) This MR usually is user data. Lets assume I created some pages, documents in Portal or User entered some data in any application , such data when stored in database is called as Product MR.
MR for OID : Oracle Internet Directory contain some data & data related to this , one can classify as MR for IM (as OID is part of Identity Management - discussed later).
MR for SSO : Oracle Single Sign-On data & schema definitions , one can classify as MR for IM (as SSO is part of IM).

So broadly there are three kind of MR (that's how I classify MR)

What is Identity Management?
Logical Grouping of Services which help in Managing Identity is called as Identity Management.Identity management Consist of following Services like :
- OID: Oracle Internet Directory
- DAS: Delegated Administrative Services
- DIP: Directory Integration & Provisioning Services
- SSO: Single Sign-On Service
- OCA: Oracle Certifying Authority

Installation of 10g Application Server in next post.....

orapwd

ORAPWD is a utility used to create a password file for an Oracle Database.

Run orapwd utility before:
-setting REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED
-Grant OSDBA/OSOPER to users

Check V$PWFILE_USERS to see who was granted OSDBA/OSOPER access.

Examples :

Create a new password file:
orapwd file=orapwSID password=oracle entries=5

If the password file already exists:
orapwd file=orapwSID password=oracle entries=5 FORCE=Y

The FORCE parameter is available starting from Oracle 10g.

"Bean not found" error in webutil???

"Bean not found" bascially comes if you have not configured the formsweb.cfg and default.env file properly.

Solution:

Follow the below two steps:

1. In the default.env file add the path of "rt.jar" in the "CLASSPATH" variable like shown below:
CLASSPATH=E:\OracleDeveloper\j2ee\OC4J_BI_Forms\applications\formsapp\formsweb\WEB-INF\lib\frmsrv.jar;E:\OracleDeveloper\jlib\repository.jar;
E:\OracleDeveloper\jlib\ldapjclnt10.jar;E:\OracleDeveloper\jlib\debugger.jar;
E:\OracleDeveloper\jlib\ewt3.jar;E:\OracleDeveloper\jlib\share.jar;
E:\OracleDeveloper\jlib\utj.jar;E:\OracleDeveloper\jlib\zrclient.jar;
E:\OracleDeveloper\reports\jlib\rwrun.jar;E:\OracleDeveloper\forms\java\frmwebutil.jar;
E:\OracleDeveloper\jdk\jre\lib\rt.jar

2. In the formsweb.cfg file add ",frmwebutil.jar,jacob.jar" to "archive_jini=frmall_jinit.jar" like shown below:
Earlier :- archive_jini=frmall_jinit.jar
After Changing :- archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar

Hope this helps in solving your problem....

If you want to know how to configure webutil properly, refer to my previous post or click on the below link
http://planetofsolutions.blogspot.com/2008/12/steps-to-configure-webutil.html

How to use Client_Host to execute .exe???

In forms 10g to execute any thing on the client side we have to use client_host().

But now the question arises how to use client_host() to execute .exe on the client.

See below for the solution:

Client_Host('cmd /c START "" ' "your .exe");

Now one more thing is there,if you want to execute the .exe and want that it should wait for the .exe to complete execution before doing anything else.
Execute the .exe as shown below


client_host('cmd /c start "" /wait ' "your .exe");

Hope this helps.....

call_form not working in Oracle Forms???

When you are migrating the Oracle Forms Application to 10g, you may come accross a problem that call_form in the form code may not work.

Cause:
The problem is caused by the windows named ROOT_WINDOW.

Solution:
Rename the windows which have the ROOT_WINDOW name.

Oracle Log files

Redo log files record all changes made on the database
Used for recovery

Require at least
2 Groups
1 member

Should try and divide up I/O
Try and keep data file and log files on seperat drives.

Min size 50k
Max size OS dependant

Make larger for many changes

You must enable arch in pfile before you can use the archiver.

LOG_ARCHIVE_DEST_1='location=pathToArchiveFolder'
LOG_ARCHIVE_START=true

Then you must shutdown the database and startup in mount mode and run:
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Switch log file online
ALTER SYSTEM SWITCH LOGFILE;

Forced check points
FAST_START_MTTR_TARGET = 600 -- 10min range

Alter system checkpoint
ALTER SYSTEM CHECKPOINT;

Adding online redo log file groups
ALTER DATABASE ADD LOGFILE GROUP 4
('PATH\log3a.rdo', 'PATH\log3b.rdo')SIZE 1M;

Adding online redo log file members
ALTER DATABASE ADD LOGFILE MEMBER
'PATH\log1c.rdo' TO GROUP 1,
'PATH\log2c.rdo' TO GROUP 2,
'PATH\log3c.rdo' TO GROUP 3;

Dropping redo log file groups
ALTER DATABASE DROP LOGFILE GROUP 4;

Dropping redo log file members
ALTER DATABASE DROP LOGFILE MEMBER
'PATH\log4c.rdo';

Relocate or rename redo log files
-Shutdown the database
-move the file to the new location
-Startup database in mount mode
-run script to rename file
ALTER DATABASE RENAME FILE
'PATH\log2a.rdo'
TO 'PATH\log3b.rdo';

Clearing online redo log files
ALTER DATABASE CLEAR LOGFILE GROUP 3;
-- might be used to reinitalize log file

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP3;
-- used to avoid archiving corupt log file.

Data Dictionary views
V$LOG -- more for group info
Status types
-unused
-current
-active
-clearing
-clearing current
-inactive

V$LOGFILE -- more for member info
Status types
-invalid
-stale
-deleted"blank"

V$instance (archive) -- what mode is the archive in

archive log list -- gives info about log archive mode

Oracle ORADIM

ORADIM is a Windows only utility used for the creation, modification and deletion of the Windows Service

Definitions:-
ASMSID:- ASM instance name
ASMSRVC (service_name):- ASM service name
NEW:- Indicates that a new instance is being created
PFILE (file_name):- Parameter File Path and Name
RUNAS:- Options are OSUSR and OSPASS
SHUTMODE:- How the Windows Service should shutdown the database. Options are ABORT, NORMAL, and IMMEDIATE
SID:- Name of the Oracle instance
SPFILE:- Indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE
SRVC (service_name):- Name of the Oracle service
SRVCSTART:- Options are SYSTEM and DEMAND
STARTMODE:- Should Windows start the instance when the Database service is started.Options are AUTO and MANUAL
SYSPWD (password):- System password
TIMEOUT (seconds):- Sets the maximum time to wait (in seconds) before the service for a particular SID stops

DROP:-
ORADIM -DELETE sid -ASMSID sid -SRVC srvc -ASMSRVC srvc
Example:-
$ORACLE_HOME\bin\oradim -DELETE -SID orabase

HELP:-
ORADIM -h $ORACLE_HOME\bin\oradim -?
Example:-
$ORACLE_HOME\bin\oradim -h
$ORACLE_HOME\bin\oradim -help

MODIFY Shutmode:-
ORADIM -EDIT -SID sid -ASMSID sid
[-SYSPWD pass]
[-STARTMODE auto manual]
[SRVCSTART systemdemand]
[-PFILE file -SPFILE]
[-SHUTMODE normal immediate abort]
[-SHUTTYPE srvc inst]
--RUNAS osusr/ospass]
Example:-
$ORACLE_HOME\bin\oradim -EDIT -SID orabase -SHUTMODE immediate

NEW:-
ORADIM -NEW -SID sid -SRVC srvc -ASMSID sid -ASMSRVC srvc
[-SYSPWD pass]
[-STARTMODE auto manual]
[SRVCSTART system demand]
[-PFILE file -SPFILE]
[-SHUTMODE normal immediate abort]
[-TIMEOUT secs]
--RUNAS osusr/ospass]
Example:-
$ORACLE_HOME\bin\oradim -NEW -SID orabase -SRVC orabase
-SYSPWD uwpwd -STARTMODE manual -SRVCSTART system
-PFILE c:\oracle\product\ora102\database\initorabase.ora
-SHUTMODE immediate

SPFILE Selection:-
ORADIM -STARTUP sid -ASMSID sid [-SYSPWD pass]
[STARTTYPE srvc inst srvc,inst]
[-PFILE filename -SPFILE]
Example:-
$ORACLE_HOME\bin\oradim -EDIT -SID orabase -SPFILE

STARTUP:-
ORADIM -STARTUP sid -ASMSID sid [-SYSPWD pass]
[STARTTYPE srvc inst srvc,inst]
[-PFILE filename -SPFILE]
Example:-
$ORACLE_HOME/bin/oradim -STARTUP -SID orabase
-STARTTYPE tstc -SPFILE

SHUTDOWN:-
ORADIM -SHUTDOWN sid -ASMSID sid [-SYSPWD pass]
[-SHUTTYPE srvc inst srvc,inst]
[-SHUTMODE normal immediate abort]
Example:-
$ORACLE_HOME\bin\oradim -SHUTDOWN -SID orabase -SHUTMODE abort

More Examples and General Information about ORADIM
oradim is a tool that can be used to create Oracle specific windows services and to start an instance.
oradim can be given the option -sid which identifies the system identifier.

Creating a service
c:\>oradim -new -sid SOME_SID -intpwd PASSWORD -startmode MODE -pfile PFILE
PASSWORD is the password that is used to connect / as sysdba.

MODE indicates how the service is started. It must either be auto or manual. auto tells Windows to automatically start the service when Windows is booted. manual, on the other hand, requires someone to manually start the service.

Starting things with oradim
Starting the service
c:\some\arbitrary\path> oradim -startup -sid -starttype srvc

Starting the instance
c:\some\arbitrary\path> oradim -startup -sid -starttype inst

Starting both the service and the instance
c:\some\arbitrary\path> oradim -startup -sid -starttype srvc,inst

Logging
ORADIM messages are not printed to the command prompt, instead, they're logged to %ORACLE_HOME%\database\oradim.log. It is possible to change the directory where the log file is stored by adding the ORA_CWD registry parameter.

How to uninstall a Windows Service???

Now the question comes....
How do I uninstall a Windows Service when there is no executable for it left on the system?

You should be able to uninstall it using sc.exe (I think it is included in the Windows Resource Kit) by calling

sc.exe delete service_ name

where service_name is the name of the service itself as you see it in the service management console, not of the exe.

Steps to re-name the database

The follwing are the steps to re-name the Database....

1. First of all take the database to the "Mount" stage.
-->Alter database close;

2. Trace the control file.
The Trace file will be created in the "udump" directory.
-->Alter database backup controlfile to trace;
Note:-Trace the control file in "Mount" stage only.

3. Now shutdown the database gracefully and exit from sql.
-->shutdown immediate.
-->exit

4. Go to "udump" directory and pick the lastest trace file created.
-->You can use the following command to get the latest trace file in linux.
Issue this command in the "udump" directory.
-->ls -lrt

5. Open the lastest trace file.
-->vi latest_trace_file.trc
Note: In this trace file there will be two scripts.
1st one is used create the control file(which i have told i the previous post)
2nd one is used to rename the database(while i'm telling in this post)

6. Remove everything in the trace file except the 2nd script.
In the 2nd script change "REUSE" to "SET" and database name which will be in the starting of the script.
For Example:-
Create controlfile "SET" database "ORCLXYZ"

7. Save the trace file.

8. Copy the trace file to the home directory.
-->cp ~/rename.sql

9. Change the name of the database in the paramater file.

10. Remove the old control file.

11. Connect to datbase as sysdba.
-->sqlplus / as sysdba

12. Start the database to "nomont" stage.
-->startup nomont

13. Run the script file that we have created from the trace file.
-->@rename.sql

14. Now open the database with resetlogs option.
-->Alter database open resetlogs;

15. Now check the name of the database to confirm.
-->select name from v$database;

Hope it helps....

Steps to Re-Create the Control File

The follwing are the steps to re-create the control file....

1. First of all take the database to the "Mount" stage.
-->Alter database close;

2. Trace the control file.
The Trace file will be created in the "udump" directory.
-->Alter database backup controlfile to trace;
Note:-Trace the control file in "Mount" stage only.

3. Now shutdown the database gracefully and exit from sql.
-->shutdown immediate.
-->exit

4. Go to "udump" directory and pick the lastest trace file created.
-->You can use the following command to get the latest trace file in linux.
Issue this command in the "udump" directory.
-->ls -lrt

5. Open the lastest trace file.
-->vi latest_trace_file.trc
Note: In this trace file there will be two scripts.
1st one is used create the control file(which i'm telling in this post)
2nd one is used to rename the database(while i will be telling in the next post)

6. Remove everything in the trace file except the 1st script.

7. Save the trace file.

8. Copy the trace file to the home directory.
-->cp ~/cont.sql

9. Remove any old control file if it is there.

10. Connect to datbase as sysdba.
-->sqlplus / as sysdba

11. Start the database to "nomont" stage.
-->startup nomont.

12. Run the script file that we have created from the trace file.
-->@cont.sql

13. Now open the database.
-->Alter database open.

Now the crotrol file has been created, you can go to the control file diretory and check.

How to Check Spatial Index???

SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, ITYP_OWNER, ITYP_NAME, DOMIDX_STATUS, DOMIDX_OPSTATUS
FROM user_indexes
WHERE ITYP_NAME IS NOT NULL;

How to Rebuild Spatial Index???

When I excute "truncate table ..." or "delete from ...", sometimes I encounter ORA-29859 or ORA-29861 error. In most cases, I just rebuild the spatial index on the geometry column and the problem will be fixed.

For Example:
DROP INDEX GEO_PS_POSITION_SX;
CREATE INDEX GEO_PS_POSITION_SX ON GEO_PS_POSITION (ROUTE) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

How to compile invalid objects???

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

1. Identifying Invalid Objects
2. The Manual Approach
3. Custom Script
4. DBMS_UTILITY.compile_schema
5. UTL_RECOMP
6. utlrp.sql and utlprp.sql
7. Magic Script

1. Identifying Invalid Objects:
The DBA_OBJECTS view can be used to identify invalid objects using the following query:

COLUMN object_name FORMAT A30
SELECT owner,object_type,object_name,status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

With this information you can decide which of the following recompilation methods is suitable for you.

2. The Manual Approach:
For small numbers of objects you may decide that a manual recompilation is sufficient.
The following example shows the compile syntax for several object types:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations:
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]
Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username
Name : Objects name
Example
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed

For more detail see below.

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

This method is limited to PL/SQL objects, so it is not applicable for views.

3. Custom Script:
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects.
The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner, object_name,object_type,
DECODE(object_type, 'PACKAGE', 1,'PACKAGE BODY', 2, 2)
AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' cur_rec.object_type ' "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE';
ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type ' : ' cur_rec.owner ' : ' cur_rec.object_name);
END;
END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

4. DBMS_UTILITY.compile_schema:
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax
Exec dbms_utility.compile_schema ( schema,compile all)
Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)

The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

5. UTL_RECOMP:
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.

Their definitions are listed below:
PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below:
•schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
•threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
•flags - Used for internal diagnostics and testing only.

The following examples show how these procedures care used:

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

There are a number of restrictions associated with the use of this package including:
•Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
•The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
•The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
•Runnig DDL operations at the same time as this package may result in deadlocks.

6.utlrp.sql and utlprp.sql:
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
•0 - The level of parallelism is derived based on the CPU_COUNT parameter.
•1 - The recompilation is run serially, one object at a time.
•N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

7. Magic Script
Now last but not the least,below is the query which i use to compile the invalid objects at one go:

SELECT CASE object_type
WHEN 'PACKAGE' THEN
'ALTER 'object_type' 'owner'.'object_name' COMPILE;'
ELSE
'ALTER PACKAGE 'owner'.'object_name' COMPILE BODY;'
END
FROM dba_objects
WHERE STATUS = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

Hope this helps....

Steps to configure Webutil

What is WebUtil???

"WebUtil is a pre-packaged set of components that can be used to add a great deal of extra functionality to Web-deployed Forms applications. WebUtil addresses common challenges faced by Oracle Forms developers who wish to build applications which integrate tightly with the client browser - the computer at which the end user is actually located."

Configuration :
1- Downlaod the file from oracle site or from the attachments as I've attached the version 1.0.6 also Jacob files.
2- Extract the webutil_106.zip file in the ( ORACLE_HOME\forms90 or [forms in 10g] )You've to get direcotries like this inside forms folder:
■ doc
■ java
■ server
■ webutil
■ Webutil.pll, Webutil.olb and the create_webutil_db.sql exist in the Forms directory

--> Also extract jacov.dll into webutil directory and jacob.jar into java directory from Jacob_18.zip

3- Create user named webutil in your database and give appropiate privilge.

4- conenct with the user and run the file create_webutil_db.sql5- Create public synonym for webutil_db
Please see this example :

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 17 14:51:55 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdbaConnected.

SQL> create user webutil identified by webutil default tablespace users;
User created.

SQL> grant connect,resource to webutil;
Grant succeeded.

SQL> conn webutil/webutil
Connected.

SQL> @C:\Dev10g\forms\create_webutil_db.sql
Package created.
Package body created.

SQL> conn / as sysdba
Connected.

SQL> create public synonym webutil_db for webutil.webutil_db;
Synonym created.

SQL> grant execute on webutil_db to public;
Grant succeeded.

SQL> revoke connect,resource from webutil;
Revoke succeeded.

6- Configuring the files :
a- Create virtual directory :
Add the following code in : forms/server/forms.conf
# Virtual path for webutil
AliasMatch ^/forms/webutil/(..*) "C:\Dev10g/forms/webutil/$1"

Check if the above line is not there, then add it else leave.

b- in forms/server/default.env file add this line :

# webutil config file path
WEBUTIL_CONFIG=C:\Dev10g\forms\server\webutil.cfg

Also append the following to CLASSPATH variable which reside in the same file
;C:\Dev10g\forms\java\frmwebutil.jar

it will look like this :
CLASSPATH=C:\Dev10g\j2ee\OC4J_BI_Forms\applications\formsapp\formsweb\WEB-INF\lib\frmsrv.jar;C:\Dev10g\jlib\repository.jar;C:\Dev10g\jlib\ldapjclnt10.jar;C:\Dev10g\jlib\debugger.jar; C:\Dev10g\jlib\ewt3.jar;C:\Dev10g\jlib\share.jar;C:\Dev10g\jlib\utj.jar;C:\Dev10g\jlib\zrclient.jar; C:\Dev10g\reports\jlib\rwrun.jar;C:\Dev10g\forms\java\frmwebutil.jar

c- Configuring formsweb.cfg :
Please insure that these files are in server directory : webutilbase.htm,webutiljini.htm,webutiljpi.htm,webutil.cfg

Open formsweb.cfg
:-- Add these lines to your application configuration which will use Webutil :
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljini.htm
baseHTMLjpi=webutiljpi.htm

or add this config to your file if you want to run seperate FMX against it :
[webutil]
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljini.htm
baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar
archive=frmall.jar
lookAndFeel=oracle

7-Please add the below to archive_jini=frmall_jinit.jar in formsweb.cfg file like
archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar

8- Siging the JAR files :
a- Open a Command window and change to the ORACLE_HOME\forms\webutil directory.
b- Check that ORACLE_HOME/jdk/bin is in the path. If it is not, add it by runing this :
C:\Dev10g\forms\webutil>set path=c:\Dev10g\jdk\bin;%path%

c- call the sign batch file :
C:\Dev10g\forms\webutil>sign_webutil.bat c:\dev10g\forms\java\frmwebutil.jar
=> Also sign the jacob file
C:\Dev10g\forms\webutil>sign_webutil.bat c:\dev10g\forms\java\jacob.jar

you can test your configuration by calling like this :
http://yourserver/forms/frmservlet?config=webutil&form=testwebutil.fmx

To download jacob_18.zip click on the below link.
http://prdownloads.sourceforge.net/jacob-project/jacob_18.zip

To download webutil_106.zip click on the below link.
http://www.oracle.com/technology/software/products/forms/files/webutil/webutil_106.zip

To download webutil_demo.zip click on the below link.
http://www.oracle.com/technology/products/forms/htdocs/webutil/Webutil_demo.zip

To see webutil demo click on below link.
http://www.oracle.com/technology/sample_code/products/forms/demo/9i/javabeans_pjc_samples/webutil/viewlet/WebUtil_Simple_viewlet_swf.html

Please post comment if you need any help.

SALAAM

You can donate Rs.3/- to the martyr's families of Mumbai terroist attack victims by sending the message

SALAAM to 56388.

It is an NDTV initiative.

You will also receive a textback for this. I tried it and it works.


Jai Hind!!!

Anti terror Squad Helpline

DEAR ALL,

In case you come across any suspicious activity, any suspicious movement
or have any information to tell to the Anti-Terror Squad (ATS),
please take a note of the new
ALL INDIA TOLL-FREE Terror Help-line "1090".
Your city's Police or Anti-Terror squad will take action as quickly as possible.

Remember that this single number 1090 is valid all over India
This is a toll free number and can be dialed from mobile phones also.

Moreover, the identity of the caller will be kept a secret.
Let us make each and every citizen of India aware about this facility.

"Jai Hind"