Nov 15, 2009

Configure Recovery Catalog - Rman


> we can use same instance for catalog db. my recommendation is separate instance is better.
> create separate schema and grant 'recovery_catalog_owner' privilege
> configure password file and listener file

1. configure recovery catalog.

$> export oracle_sid=rmandb
$> rman catalog rmanc/rmanc

rman> create catalog tablespace tbsnam;
rman> exit;

2. Register target db with catalog db

$> rman catalog rmanc/rmanc target sys@db1
target database password:
connected to target.....

rman> register database;
database register in recovery catalog...
rman> exit

3. check and confirm the db register with catalog db

login rman schema

$ sqlplus rmanc/rmanc
$ select * from db;

you can see the target db id and incarnation information..

now target db was configured with catalog db.. next we have to configure rman for target db...


Nov 14, 2009

ORA-00704 bootstrap process failure

while i create fresh database using 10.2.0. i faced bootstrap failure. i was scared. most of them asked me to contact oracle. but again i tried with upgrade option and run catalog.sql and catproc.sql script, the problem was solved, now the db is working fine. But still i am not able to find the root cause why it was happen. If any one know please share the details..

ORA-00704: bootstrap process failure
ORA-39700: database must be opened with upgrade option

platform : Redhat 4

error in invoking target 'all_no_orcl ihsodbc' of makefile

I'm installing Oracle 10.2.0 on RHEL 4 ES. The Linux runs on VMWare 1.0.5. The Virtual Machine has 800 Mb of RAM, and 12Gb of hard drive space (/dev/sda).When I was running the Oracle Universal Installer, I got the following error message:

error in invoking target 'all_no_orcl ihsodbc' of makefile '/rman/oracle/product/10.2.0/rdbms/lib/

I was wounder what happen actually. i investigate the error log.. two library files are missing, so OUI not able to link files..

'/rman/oracle/product/10.2.0/rdbms/lib/'.See '/u01/app/oracle/oraInventory/logs/installActions2009-11-14_06-41-37PM.log'


Install the following rpm


note: check all mandatory rpm before install..

Aug 18, 2009

Oracle 10g upgradation Outline

Platform: Sun Solaris 5.8
Architecture: Sun Sparc 64bit
Oracle Version: 64bit
Upgradation path: --> -->
How to get RDBMS: Download from OTN / Metalink

Upgradation Steps:

1. Shutdown oracle db [shutdown immediate]

2. Take complete backup [cold backup]

3. Stop all services

Listener, OEM console, Cronjobs

4. Backup oracle home and inventory files

a. $ tar -cvf 10102.tar $OH/10102

b. $ tar –cvf Inventory.tar $OH/oraInventory

c. Save it in backup location

5. Install new Oracle home in separate folder

6. Startup database with previous home []

a. Export ORACLE_HOME=/u01/app/oracle/product/10.1.0

b. Startup normal;

7. Check the compatibility

a. sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/utlu102i.sql

b. Examine spool file and correct if any errors.

8. Startup db with new home in upgrade mode.

a. $ export ORACCLE_HOME=/u01/app/oracle/product/10.2.0

b. Sql> Startup upgrade; (copy spfile from pervious to new home)

9. Upgrade the database

a. Sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/catupgrade.sql

b. Verify the spool file and correct the errors if any. c. Bounce the database.

10. Examine and compile invalid object

a. Sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/utlrp.sql

b. Examine spool file and re-compaile if any invalid objects.

11. Database has upgraded to and now want to apply the patchset on top of

12. Omit step 5 and 6. Repeat remaining steps. Before install patchset backup the following files " utlu102i.sql and catupgrade.sql "

May 17, 2009

Enable Archive Log in 10g

Oracle version : (64b)
Platform : Sun Sparc 5.8 (64b)
Server : Solaris Fire v20
Storage : SAN

before enabling archivelog mode take complete backup after database shutdown (cold backup).

1. backup spfile : create pfile='/path/pfile.ora' from spfile;

2. enable archive destination

sql> alter system set log_archive_dest_1='Locatin=\path' scope=spfile;

sql> shutdown immediate;

3. perform complete backup

5. connect exclusive mode

$sqlplus "/as sysdba"
sql> startup mount;

alter database archivelog;

alter database open;

archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination \path\archive
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

6. after enable archive, better to another complete backup

sql> create spfile from pfile='/path/pfile.ora'

sql> shutdown immediate;

$ cold backup

sql> startup

Note :

log_archive_format must be in this format '%t_%s_%r
%t - thread number
%s - sequence number
%r - resetlog id

Error :

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORA-19905: log_archive_format must contain %s, %t and %r

Metalink Doc:

Doc ID: 274302.1
Doc ID: 371139.1
Doc ID: 69739.1
Doc ID: 420371.1

Good Luck!

May 11, 2009

Configure OEM in 10G R1/R2


a. Password file is required.
b. Set remote_login_passwordfile is set to EXCLUSIVE or SHARED.
c. Increase the job_queue_processes more that 1.
d. Emca is located in $ORACLE_HOME/bin

Create DB Console repository object and configuration files

Oracle 10.1

Emca –r (create repository object only)
Emca (create both)

Oracle 10.2

emca –repos create (create repository object only)
emca –config dbcontrol db (create config file only) (or)
emca –config dbcontrol db –repos create (create both)

Delete and recreate database console

There are 5 way to remove db control objects.

Delete db control config files using emca script
Delete db control config files manually
Delete db control repository using RepManager
Delete db control repository manually
Delete both config and repository using emca script.

Using emca script

Oracle 10.1 > Emca –x

Oracle 10.2 >

Emctl stop dbconsole

Emca –deconfig dbcontrol db (won’t drop repository) (Or)
Emca -deconfig dbcontrol db –repose drop (drop the repository also)


Remove the following files from your filesystem
On windows you also need to remove console service from registry

Run > Regedit
Navigate > HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleDBConsolesid

Action > delete (Or )
Command line : sc delete
Eg : service_name = OracleDBConsole

Using RepManager (not recommended)

RepManager –sys_password –action drop
But don't delete by RepManager. It puts the database in quiescence mode.

Both at the same time

Oracle 10.1 > emca –x sid
Repmanager hostname listner_port sid –action drop
Oracle 10.2 > emca –deconfig dbcontrol db –repos drop

Recreate config files and repository

Config files only

Emca –repose recreate
Emca –config dbcontrol db
Emca –reconfig ports –DBCONTROL_HTTP_PORT_

Config files and repository

Emca -config dbcontrol db –repose recreate (or)
Emca –config dbcontrol db –repose recreate –SID -PORT -ORACLE_HOME -DBCONTROL_HTTP_PORT_

4. How to Drop Misconfigured repository

a. ORACLE_HOME/sysman/admin/emdrep/bin
b. Set SID and Run the following command

RepManager –sys_password –action drop

5. Commands to Star, Stop and check the Status of Console

a. Emctl start dbconsole
b. Emctl status dbconsole
c. Emctl stop dbconsole

6. Commands to start, stop and check status of EM Agent

a. Emctl start agent
b. Emctl status agent
c. Emctl stop agent

7. Connect to database control portal


8. Files and Folders

Local Configuration files.

Port Number is updated in config file, this file is updated by emca while installing.

Alert Log file location

$ORACLE_HOME/ cfgtoollogs/emca/

In Windows you can check Services.msc for running services

Apr 26, 2009

ORA - 16179

ORA - 16179 : incremental changes to "log_archive_dest_1" not allowed with spfile

SQL> alter system set log_archive_dest_1='d:\archive\' scope=spfile; 
alter system set log_archive_dest_1='d:\ora\' scope=spfile 
ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: 
incremental changes to "log_archive_dest_1" not allowed with SPFILE

When i try to enable Archivelog mode in production server, i faced this ORA-16179. 
production server is version and host is solaris sparc 64bit.

Incremental changes to a log_archive_dest_n parameter cannot be made when using an SPFILE. 

Specify either LOCATION or SERVICE plus all other attributes to  be set in one ALTER SYSTEM/SESSION SET command.

Action :
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/db1/archive';

System altered.

Reff: Metalink Doc ID: 194494.1

Apr 12, 2009

EM : Policy violation

Policy rule 1 : Insufficient Number of Control Files

Cause : there is only one control file in the database

Solution : Control file multiplexed..

Action : Shutdown immediate;

Startup mount;

alter system set control_files='/path/control01.ctl','/path/newcontrol02.ctl' scope=spfile;

shutdown immediate;


Policy rule 2 : Insufficient Number of Redo Logs

cause : i have two groups in my database, but each group has only one member.

Solution : Log member multiplexed

Action : Using EM control log members added...

Policy rule 3 : EXECUTE UTL_FILE privileges to PUBLIC

Cause : bydefault 'UTL_FILE' privilege grant to PUBLIC profile.

Action : Revoke execute utl_file from public;

But still policy violation still shown.
Reason, there are many other polices are set to public profile ( UTL_SMTP, UTL_TCP, UTL_HTTP and DBMS_RANDOM).

Apr 11, 2009

EM- java.lang.Exception:UnknownHostException

Environment : Windows XP sp3, Oracle

Problem : When i connect to EM console, with SYS user. it thrown an exception error message

Error : java.lang.Exception: UnknownHostException sending request :: naachi-514ccc13

Cause : Recently I was changed the server hostname. previously it was "naachi-514ccc13", now it is "naachi".

Solution : d:\> emca -config dbcontrol db -repose recreate

The following action will be performed automatically by the above single line.

>>> Stop the database control
>>> Drop the database control
>>> Create the database control
>>> Start the database control

Status : successfully problem solved.

Apr 5, 2009

Clone using Export Dump

create a new database or clone a existing database or recover using oracle export Dump.

1. You must have full backup dump

2. List the content from export dump

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y show=y
It will list all sql statement in log (imp.log)

c. Open the imp.log file, search and find the tablespace and users sql statements

i. Create tablespace .......
ii. Create users ....
iii. Create index..
iv. And what every you want

d. Copy and past it in a file.sql. now u will the script for creating a database.

e. Copy and save ‘create user.. ‘ is seperate file and ‘create tablespace..’ in sepereate sql file.

3. Create required folders (bdump,udump, also oradata)

4. Edit the pfile, use it from backup, if not. copy it from old db alert log or statspack report if u have.
5. Create oracle service

Windows : oradim –new –sid xxx
Solaris : export oracle_sid= xxx

6. Startup the database

Sqlplus “/as sysdba”
Startup nomount pfile=’/pfile.ora’

7. Run the script

$> @ file.sql
It will create all tablespaces

8. Open the database

Alter database mount;
Alter database open;

9. Create the users

Run the script to create users

10. Import the datas

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y

After import data, update the database metrics using analyze command..

Mar 14, 2009

Oracle EM Console 10g


OEM is a set of system management tool for managing, monitoring and automate tasks
of oracle database environment. database control was first introduced in oracle 10g.

For windows/Unix you can follow the same method to configure EM console

1. Pre-Reuestits

a. Password file is required.
b. Set remote_login_passwordfile is set to EXCLUSIVE or SHARED.
c. Increase the job_queue_processes more that 1.
d. Unlock sysman and dbsnmp user account if already exist.
e. Emca is located in $ORACLE_HOME/bin

2. Create the repository for OEM console

emca –repos create

3. Configure the database control

emca –config dbcontrol db

4. Connect to the database control


5. Commands to Star, Stop and check the Status of dbConsole

Emctl start dbconsole
Emctl status dbconsole
Emctl stop dbconsole

6. Commands to start, stop and check status of EM Agent

Emctl start agent
Emctl status agent
Emctl stop agent

7. how to Drop the database consol

Emca –deconfig dbcontrol db

8. What are the file Files and Folders create for your db console. lets check.

Local Configuration files


Port Number


Log location


9. Services

In windows you can check the services is services.msc
service like : OracleDBConsole

10. Add OS user in "Log on as a batch job" for windows

Windows user must add their name in batch job in user policies folder

Control panel -> Admin Tool -> Local security Policy -> Local Policies -> User Right Assignment
-> Log on as a batch job -> Local security setting

11. How does it work

WebBrowser <--> Http/Https <--> J2EE (OC4J) AppServer <-- Thin JDBC --> Sysman schema.

OEM create a sysman schema to store dbconsole metadata and statistics.

12. Management Packs

Oracle Congfiguration management pack

1. Track h/w and s/w configuration for hosts and database.
2. Cloning for database instance and oracle home for deployments.

Oracle Change management pack

1. Changes to support new application requirements
2. Eliminate error or data loss when making changes and minimize downtime.

Oracle Diagnostics pack and tuning pack

1. Reduce the complex performance tasks.