The DBMS_SYSTEM Package (ksdwrt,set_sql_trace_in_session)

I have found a very useful package. DBMS_SYSTEM which has several functions, but I have choose two of them ksdwrt and SET_SQL_TRACE_IN_SESSION.

Story:

I had the following problem: I have configured EM alert on ORA- errors. But EM was not catching error ORA-20018. As you see error code is between [-20000]-[-20999], so it is user-defined error.
EM catches errors that are generated in alert.log. Not all errors are going to alert.log especially user defined errors.

So let’s introduce ksdwrt that helped me to solve this problem.

ksdwrt

Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);

Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Anther usefull method:

SET_SQL_TRACE_IN_SESSION

Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

The values for SID and SERIAL# can be found using the V$SESSION view.

End of the story:

I have run:

begin
dbms_system.ksdwrt(2,’ORA-20118: myerror’);
raise_application_error(-20118, ‘myerror’);
end;

And error was placed in alert.log. So EM was able to see it.

Source: https://oracle-base.com/articles/8i/dbms_system

EM: CPU chart Error: Invalid BoundDataSource: no class, method or dataSource provided.

Error:

CPU_Chart

Also no other tabs are available to click.

Reason:

One of the reason could be that you converted single instance database to RAC.

or you created EM repository without indicating -cluster option.

run:

emca -config dbcontrol db -repos recreate -cluster

answer the questions carefully 🙂

My settings:

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Database instance hostname ……………. Listener ORACLE_HOME ……………. /u01/app/11.2.0/grid
Listener port number ……………. 1521
Cluster name ……………. oracle-db
Database unique name ……………. orcl
Email address for notifications …………… mariam.kupa@gmail.com
Outgoing Mail (SMTP) server for notifications …………… mail.ar.ge
ASM ORACLE_HOME ……………. /u01/app/11.2.0/grid
ASM port ……………. 1521
ASM user role ……………. SYSDBA
ASM username ……………. ASMSNMP

 

Recreate Oracle 11g OEM DBConsole manually for RAC

If you have problems with existing OEM, the best way is to reconfigure it. Here are the steps, how to do it correctly:

$ emca -config dbcontrol db -repos recreate -cluster

STARTED EMCA at Jan 22, 2013 6:04:10 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database unique name: orcl
Service name: orcl
Listener ORACLE_HOME [ /u01/app/11.2.0/grid ]:
Password for SYS user:
Database Control is already configured for the database orcl
You have chosen to configure Database Control for managing the database orcl
This will remove the existing configuration and the default settings and perform a fresh configuration
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: y
Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: oracle-db

!!!Stop here for a while: if you don’t know your cluster name run the following command:

$ su – grid
cemutlo -n

…continuing configuration

Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/app/11.2.0/grid ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
Jan 22, 2013 6:05:02 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
WARNING: Error during db connection : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Database instance hostname ……………. Listener ORACLE_HOME ……………. /u01/app/11.2.0/grid
Listener port number ……………. 1521
Cluster name ……………. oracle-db
Database unique name ……………. orcl
Email address for notifications …………… mariam.kupa@gmail.com
Outgoing Mail (SMTP) server for notifications …………… mail.tbilisi.gov.ge
ASM ORACLE_HOME ……………. /u01/app/11.2.0/grid
ASM port ……………. 1521
ASM user role ……………. SYSDBA
ASM username ……………. ASMSNMP

—————————————————————–
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 22, 2013 6:05:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2013_01_22_18_04_10.log.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Jan 22, 2013 6:06:01 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Jan 22, 2013 6:08:00 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Jan 22, 2013 6:08:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Jan 22, 2013 6:11:39 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 22, 2013 6:11:44 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) …
Jan 22, 2013 6:12:17 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jan 22, 2013 6:12:18 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_oracle-node1_orcl to remote nodes . ..
Jan 22, 2013 6:12:20 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_oracle-node2_orcl to remote nodes . ..
Jan 22, 2013 6:12:26 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oracle-node1_orcl to remote nodes …
Jan 22, 2013 6:12:28 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oracle-node2_orcl to remote nodes …
Jan 22, 2013 6:12:31 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Jan 22, 2013 6:13:00 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Jan 22, 2013 6:13:20 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 22, 2013 6:13:20 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://oracle-node1.mr.gov.ge:1158/em <<<<<<<<<<<
Jan 22, 2013 6:13:22 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
**************** Current Configuration ****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
———- ———- ———————

orcl oracle-node1 oracle-node1.mr.gov.ge
orcl oracle-node2 oracle-node1.mr.gov.ge
Jan 22, 2013 6:13:22 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encry ption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/oracle-node1_orcl/sysman/config/emkey.o ra. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 22, 2013 6:13:22 PM

For me to access OEM the URL is  https://oracle-node1.mr.gov.ge:1158/em

Good Luck!