Pages

Tuesday, 28 February 2012

Deconfigure Enterprise Manager Database Control in 11g

We can deconfigure Enterprise Manager Database Control using below command.

Syntax:
emca -deconfig dbcontrol db -repos drop -SYS_PWD -SYSMAN_PWD


Eg:
$ emca -deconfig dbcontrol db -repos drop -SYS_PWD sys123 -SYSMAN_PWD sys123


STARTED EMCA at Feb 28, 2012 3:11:53 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: omsdb12c
Listener port number: 1521

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 28, 2012 3:12:06 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/cfgtoollogs/emca/omsdb12c/emca_2012_02_28_03_11_52.log.
Feb 28, 2012 3:12:06 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Feb 28, 2012 3:12:26 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Feb 28, 2012 3:13:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 28, 2012 3:13:32 AM



Monday, 27 February 2012

How to create Oracle OWB repository in Linux

We can create Oracle OWB repository in Linux by executing reposinst.sh available at $ORACLE_HOME/owb/bin/unix

Eg:
cd /orasw/app/oracle/product/11.1.0.6/owb/bin/unix
./reposinst.sh

This will open GUI  and then follow the steps accordingly.

Installing a New Oracle XML DB Manually without using DBCA


Follow the below steps to Install New Oracle XML DB Manually

1. If XML DB is installed can be verified through dba_registry: 

SQL> select comp_name "Component" from dba_registry;

Component
--------------------------------------------------------------------------------
Oracle Enterprise Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine

It shows that XML DB is not installed.

2. Create a new tablespace for XML DB as below.

create tablespace  xmldb_ts
datafile '/orasw/oracle/data/oradaxmldb_ts.dbf' size 10M autoextend on maxsize unlimited
extent management local uniform size 1M;

3.Install the Oracle XML DB by connecting to database as SYS user and executing the script catqm.sql available at $ORACLE_HOME/rdbms/admin as below


Syntax is :catqm.sql

Eg:

SQL> @?/rdbms/admin/catqm xdbpwd xmldb_ts TEMP

Table created.


Table created.

4.Once script is executed successfully, you can verify the installation as below.

#> sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 27 04:11:26 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>select comp_name "Component" from dba_registry;
Component
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle XML Database

6 rows selected.



Re-installing XML DB

To reinstall Oracle XML DB, run following SQL commands connecting to SYS to drop Oracle XML DB user and tablespace:

SQL> @?/rdbms/admin/catnoqm
SQL> alter tablespace tablespace_name offline;
SQL> drop tablespace tablespace_name including contents;











Workflow Configuration has completed with error for OWF 2.6.4 on 11g DB



Workflow Configuration completes with error for OWF 2.6.4 on 11g DB as below


WorkflowCA: default_temp TEMP
WorkflowCA: default_tablespace USERS
WorkflowCA: SQLPLUS:SYS/ /nolog @/orasw/app/oracle/product/11.1.0.6/owb/wf/sql/wfsysgnt.sql OWFMGR USERS TEMP
WorkflowCA: ORA-01775: looping chain of synonyms

WorkflowCA: WorkflowCA: SQLException during execution: java.sql.SQLException: ORA-01775: looping chain of synonyms
: grant execute on xmldom to OWFMGR

WorkflowCA: File: /orasw/app/oracle/product/11.1.0.6/owb/wf/sql/wfsysgnt.sql.
WorkflowCA: oracle.apps.fnd.wf.install.utils.sql.SQLProcessorException: SQLException during execution: java.sql.SQLException: ORA-01775: looping chain of synonyms
: grant execute on xmldom to OWFMGR

oracle.apps.fnd.wf.install.utils.sql.SQLProcessorException: SQLException during execution: java.sql.SQLException: ORA-01775: looping chain of synonyms
: grant execute on xmldom to OWFMGR

        at oracle.apps.fnd.wf.install.utils.sql.SQLProcessor.processLine(SQLProcessor.java:2115)
        at oracle.apps.fnd.wf.install.utils.sql.SQLProcessor.executeSQLFile(SQLProcessor.java:2298)
        at oracle.apps.fnd.wf.install.utils.sql.SQLProcessor.runSQLFile(SQLProcessor.java:359)
        at oracle.apps.fnd.wf.install.utils.sql.SQLProcessor.runSQLFile(SQLProcessor.java:329)
        at oracle.apps.fnd.wf.install.db.WorkflowCA.handleSQL(WorkflowCA.java:5541)
        at oracle.apps.fnd.wf.install.db.WorkflowCA.executeProcess(WorkflowCA.java:3498)
        at oracle.apps.fnd.wf.install.db.WorkflowCA.grantPrivilege(WorkflowCA.java:5315)
        at oracle.apps.fnd.wf.install.db.WorkflowCA.schemaCreation(WorkflowCA.java:1401)
        at oracle.apps.fnd.wf.install.db.WfFrame.actionPerformed(WfFrame.java:2051)
        at oracle.ewt.button.PushButton.processActionEvent(Unknown Source)
        at oracle.ewt.lwAWT.LWButton.processActionEvent(Unknown Source)
        at oracle.ewt.button.PushButton.processEventImpl(Unknown Source)

Solution:

This was caused as Oracle XML DB is not installed. Follow the below steps to solve this.

1.First check whetehr Oracle XML DB is installed or not. you can check this as below


SQL> select comp_name "Component" from dba_registry;

Component
--------------------------------------------------------------------------------
Oracle Enterprise Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine

SQL> select * from dba_synonyms where SYNONYM_NAME like '%XMLDOM%';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
PUBLIC                         /118a4e7f_XMLDOMImplementation SYS                            /118a4e7f_XMLDOMImplementation
PUBLIC                         /2137556a_XMLDOMEvent          SYS                            /2137556a_XMLDOMEvent
PUBLIC                         /64bcbda7_XMLDOMUserData       SYS                            /64bcbda7_XMLDOMUserData
PUBLIC                         /7113b128_XMLDOMConfiguration  SYS                            /7113b128_XMLDOMConfiguration
PUBLIC                         /765a7982_XMLDOMStringList     SYS                            /765a7982_XMLDOMStringList
PUBLIC                         /79e741b8_XMLDOMImplementation SYS                            /79e741b8_XMLDOMImplementation
PUBLIC                         /de3a73b0_XMLDOMMutationEvent  SYS                            /de3a73b0_XMLDOMMutationEvent
PUBLIC                         /e537a441_XMLDOMException      SYS                            /e537a441_XMLDOMException
PUBLIC                         XMLDOM                         SYS                            XMLDOM

2.Install the Oracle XML db as mentioned in the below link.

Installing Oracle XML DB manually


3.Then check the installation using below commands.

SQL> select comp_name "Component" from dba_registry;

Component
--------------------------------------------------------------------------------
Oracle Enterprise Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK

SQL> select * from dba_synonyms where SYNONYM_NAME like '%XMLDOM%';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SYS                            XMLDOM                         XDB                            DBMS_XMLDOM
PUBLIC                         /118a4e7f_XMLDOMImplementation SYS                            /118a4e7f_XMLDOMImplementation
PUBLIC                         /2137556a_XMLDOMEvent          SYS                            /2137556a_XMLDOMEvent
PUBLIC                         /64bcbda7_XMLDOMUserData       SYS                            /64bcbda7_XMLDOMUserData
PUBLIC                         /7113b128_XMLDOMConfiguration  SYS                            /7113b128_XMLDOMConfiguration
PUBLIC                         /765a7982_XMLDOMStringList     SYS                            /765a7982_XMLDOMStringList
PUBLIC                         /79e741b8_XMLDOMImplementation SYS                            /79e741b8_XMLDOMImplementation
PUBLIC                         /de3a73b0_XMLDOMMutationEvent  SYS                            /de3a73b0_XMLDOMMutationEvent
PUBLIC                         /e537a441_XMLDOMException      SYS                            /e537a441_XMLDOMException
PUBLIC                         DBMS_XMLDOM                    XDB                            DBMS_XMLDOM
PUBLIC                         XMLDOM                         XDB                            DBMS_XMLDOM

11 rows selected.


4.Drop the workflow user as below

SQL> drop user OWFMGR cascade;

User dropped.


5.Start the workflow installation again using below command

$ORACLE_HOME/owb/wf/install/wfinstall.csh

Friday, 24 February 2012

Oracle 10g workflow configuration assistant fails with "emoms.properties doesn't exist" error

Oracle 10g workflow configuration assistant fails with error emoms.properties doesn't exist while configuring user as below.





Solution: Since EM database control is not configured for this database, it's unable to find emoms.properties file at $ORACLE_HOME/hostname_$ORACLE_SID/sysman/config so configure the Oracle EM database control. You can refer my another article to configure EM at http://oracledba-sharinganexperience.blogspot.in/2012/02/manually-configure-oracle-10g.html

Once EM database control is configured, drop the workflow user that was created while configuring workflow user as configuration was not successful and restart configuration from $ORACLE_HOME/wf/install/wfinstall.csh

Manually Configure Oracle 10g Enterprise Manager dbconsole



Below is the procedure to configure Oracle 10g Enterprise Manager Database Control manually without Database Configuration Assistant.
Using EM database control,you can monitor and administer single database.

The Database Control Framework consists of the Database Control and its underlying technologies:

 * A local Oracle Management Repository installed in the local database and designed to store management data for the Database Control.

 * A local version of the Oracle Management Service designed to work with the local database or clustered database.
     
 
Steps to configure Oracle 10g Enterprise Manager Database Control

1.Ensure that you can connect to the Repository Database. Test it with SQL*Plus or TNSPING Utility.


#> tnsping TESTDB

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 23-FEB-2012 22:13:41

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:
/spl/intbase/DB/oracletns/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sf-lindb-10)(PORT = 1521)) (CONNECT_DATA = (SID = TESTDB)))
OK (50 msec)
= TESTDB Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/bin


#> . oraenv
ORACLE_SID = [TESTDB] ?
The /orasw/app/oracle/product/10.2.0.3/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
= TESTDB Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/bin
#> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Feb 23 22:13:32 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit



2.Start the Oracle enterprise manager dbconsole Build Script ($ORACLE_HOME/bin/emca for Linux and and $ORACLE_HOME\Bin\emca.bat for Windows).

a.Create EM repository as below.


#> ./emca -repos create

STARTED EMCA at Feb 23, 2012 10:32:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TESTDB
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 23, 2012 10:33:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/TESTDB/emca_2012-02-23_10-32-43-PM.log.
Feb 23, 2012 10:33:07 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 23, 2012 10:35:20 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 23, 2012 10:35:20 PM
= TESTDB Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/bin

b.Configure the db control as below.

#> ./emca -config dbcontrol db

STARTED EMCA at Feb 23, 2012 11:06:17 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TESTDB
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /orasw/app/oracle/product/10.2.0.3

Database hostname ................ testdb.sangam.com
Listener port number ................ 1521
Database SID ................ TESTDB
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Feb 23, 2012 11:08:17 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/TESTDB/emca_2012-02-23_11-06-16-PM.log.
Feb 23, 2012 11:08:55 PM oracle.sysman.emcp.ParamsManager getLocalListener
WARNING: Error retrieving listener for testdb.sangam.com
Feb 23, 2012 11:11:40 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Feb 23, 2012 11:16:42 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Feb 23, 2012 11:16:43 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://testdb.sangam.com:5510/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 23, 2012 11:16:43 PM
= TESTDB Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/bin

3.Try to connect to the database Control using an URL http://testdb.sangam.com:5510/em


Note: If you get any error while creating repository, you may refer my another article at  http://oracledba-sharinganexperience.blogspot.in/2012/02/oracle-10g-em-dbconsole-configuration.html





Thursday, 23 February 2012

Oracle 10g EM dbconsole configuration failed with ORA-20001: SYSMAN already exists

While creating dbconsole repository(Oracle Managament repository)  for configuring db console using below command, it fails with error as
"oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already exists"


#> ./emca -repos create

STARTED EMCA at Feb 23, 2012 10:15:00 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TESTDB
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 23, 2012 10:20:45 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-15-00-PM.log.
Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error creating the repository
Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_repos_create_.log for more details.
Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error creating the repository
Refer to the log file at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-15-00-PM.log for more details.
Could not complete the configuration. Refer to the log file at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-15-00-PM.log for more details.
= TESTDB-SANGAM Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/bin

When we checked in log /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-15-00-PM.log, it's found that


Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 23, 2012 10:20:48 PM oracle.sysman.emcp.EMReposConfig createRepository
CONFIG: ORA-20001: SYSMAN already exists..
ORA-06512: at line 17

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already exists..
ORA-06512: at line 17

        at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1467)
        at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:841)
        at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:265)
        at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:306)
        at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:389)
        at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:191)

Reason:SYSMAN is the user for the dbconsole user and it is already present in your database.During the repository creation the SYSMAN user gets created. 

SQL> select username from dba_users where username='SYSMAN';

USERNAME
------------------------------
SYSMAN

Since it's already exists, it's not allowing you to proceed.

The second reason might be the dbconsole repository is already created in your database.You have to configure the dbconsole from the created repository. 

Solution 

 Drop the existing Oracle Management repository and recreate it as below.

Drop the existing repository as below

#> ./emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Feb 23, 2012 10:27:57 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID:TESTDB
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 23, 2012 10:28:27 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-27-57-PM.log.
Feb 23, 2012 10:28:31 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Feb 23, 2012 10:28:31 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...


Note: This will drop the SYSMAN user
SQL> select * from dba_users where username='SYSMAN';

no rows selected

SQL> exit

Create the new Oracle Management repository as below

#> ./emca -repos create

STARTED EMCA at Feb 23, 2012 10:32:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TESTDB
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Feb 23, 2012 10:33:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /orasw/app/oracle/product/10.2.0.3/cfgtoollogs/emca/ TESTDB/emca_2012-02-23_10-32-43-PM.log.
Feb 23, 2012 10:33:07 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Feb 23, 2012 10:35:20 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Feb 23, 2012 10:35:20 PM

SQL> select username from dba_users where username='SYSMAN';

USERNAME
------------------------------
SYSMAN


Oracle OHS Standalone can not be installed into an existing Oracle RDBMS 9.2.0.1.0 or higher Oracle home

We got error as "Oracle OHS Standalone can not be installed into an existing Oracle RDBMS 9.2.0.1.0 or higher Oracle home"  while installing Oracle Database 10 Companion Products.



Solution: Select $ORACLE_HOME which is different from existing $ORACLE_HOME on which database is running.

Eg:


If $ORACLE_HOME is /orasw/app/oracle/product/10.2.0.3 

then select new $ORACLE_HOME like 

/orasw/app/oracle/product/10.2.0.3/ohs_home for Oracle Database 10g companion product installation

Error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

I got error as " Error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory"  while configuring Oracle HTTP server and Oracle workflow middle tier using Oracle 10g(10.2.0.1) companion cd



#> /orasw/app/oracle/product/10.2.0.3/ohs_home2/opmn/bin/opmnctl status

Processes in Instance: standalone
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status
-------------------+--------------------+---------+---------
HTTP_Server        | HTTP_Server        |     N/A | Down
LogLoader          | logloaderd         |     N/A | Down
dcm-daemon         | dcm-daemon         |     N/A | Down

= SF-LINDB-10 Shared DB server = oracle@testdb.sangam.com /orasw/app/oracle/product/10.2.0.3/ohs_home2/cfgtoollogs


Error in HTTP server log is

#> more /orasw/app/oracle/product/10.2.0.3/ohs_home2/opmn/logs/HTTP_Server~1

--------
12/02/23 07:57:48 Start process
--------
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/apachectl start: execing httpd
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

--------
12/02/23 07:57:50 Start process
--------
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/apachectl start: execing httpd
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

--------
12/02/23 08:01:44 Start process
--------
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/apachectl start: execing httpd
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

--------
12/02/23 08:01:46 Start process
--------
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/apachectl start: execing httpd
/orasw/app/oracle/product/10.2.0.3/ohs_home2/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory




When I tried to bring all components manually, time out happened and got again same above error in the log

#> /orasw/app/oracle/product/10.2.0.3/ohs_home2/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=testdb.sangam.com:6201
    0 of 1 processes started.

ias-instance id=standalone
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=17501)
    failed to start a managed process after the maximum retry limit
    Log:
    /orasw/app/oracle/product/10.2.0.3/ohs_home2/opmn/logs/HTTP_Server~1


Solution:
This happened because http web server was unable to start due to missing library libdb.so.2.  We can resolve this issue by creating a softlink with root user as below.


ln -s   /usr/lib/libgdbm.so.2.0.0  /usr/lib/libdb.so.2

Wednesday, 15 February 2012

How to check character set in Oracle Database


04:28:57 SYS@TESTDB>SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UTF8

Elapsed: 00:00:00.00

04:30:09 SYS@TESTDB>SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         UTF8
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.

Elapsed: 00:00:00.04

Monday, 13 February 2012

Resuming failed or stopped Data Pump Import execution

When datapump import stops or fails, may be because putty was closed by mistake or Job got stopped due some db errors then we can resume datapump as below.

1.Identify the datapump import Job which you had started as below.

SYS@TESTDB1 AS SYSDBA 13-FEB-12> select * from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE                       STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYSTEM                         SYS_IMPORT_SCHEMA_01           IMPORT                         SCHEMA                         EXECUTING                               8                 0          9

SYS@TESTDB1 AS SYSDBA 13-FEB-12> exit

2.Then resume this import by attaching the Job and then enter continue as below.

$ impdp system/manager attach=SYS_IMPORT_SCHEMA_01

Import: Release 11.2.0.2.0 - Production on Mon Feb 13 00:15:40 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_01
  Owner: SYSTEM
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: B8D498476B797D54E040F10A07255A96
  Start Time: Sunday, 12 February, 2012 23:40:40
  Mode: SCHEMA
  Instance: TESTDB1
  Max Parallelism: 8
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** dumpfile=expdp_TESTDB_20121302.dmp logfile=expdp_TESTDB_20121302.log directory=dump_dir1 schemas=user parallel=8
  IMPORT Job Parameters:
     CLIENT_COMMAND        system/******** dumpfile=expdp_TESTDB_20121302.dmp logfile=impdp_expdp_TESTDB_TESTDB1_20121302.log directory=dump_dir schemas=user remap_schema=user:user_gr parallel=8
  State: EXECUTING
  Bytes Processed: 10,378,558,440
  Percent Done: 99
  Current Parallelism: 8
  Job Error Count: 0
  Dump File: /bi_oradata_01/dumps/expdp_TESTDB_20121302.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: USER_GR
  Object Name: Q1_USZIP54004_SDX
  Object Type: SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
  Completed Objects: 21
  Worker Parallelism: 8

Worker 2 Status:
  Process Name: DW01
  State: WORK WAITING

Worker 3 Status:
  Process Name: DW02
  State: WORK WAITING

Worker 4 Status:
  Process Name: DW03
  State: WORK WAITING

Worker 5 Status:
  Process Name: DW04
  State: WORK WAITING

Worker 6 Status:
  Process Name: DW05
  State: WORK WAITING

Worker 7 Status:
  Process Name: DW06
  State: WORK WAITING

Worker 8 Status:
  Process Name: DW07
  State: WORK WAITING


Import> continue


Thursday, 9 February 2012

How to import tables to a different tablespace using original import utility

Oracle offers no parameter to specify a different tablespace to import data into while using original export and import utilities for taking logical backup. Objects will be re-created in the tablespace they were originally exported from. We can alter this behaviour as below.

Note:We can specify a different tablespace while using data pump using remap_tablespace option

    * Import the dump file using the INDEXFILE option


    imp username/password>@db_tns_name file=filename.dmp indexfile=index.sql full=y

    * Edit the indexfile. Remove remarks and specify the correct tablespaces.

    * Run this indexfile against your database, this will create the required tables in the appropriate tablespaces

sqlplus username/password@db_tns_name @index.sql

    * Import the table(s) with the IGNORE=Y option.

imp username/password@db_tns_name file=filename.dmp fromuser=from_user  touser=to_user ignore=y 

    Note: you can also import objects to different tablespace by
changing the default tablespace for the user to new tablespace where objects need to be imported and Revoke the user's quota from the tablespace from where the objects were exported. This will force the import utility to create tables in the user's default tablespace.