Oracle Newbie DBA / Apps DBA: EMCA

Showing posts with label EMCA. Show all posts

Monday, September 12, 2011

How to delete/remove Oracle Enterprise Manager Console in 10GR2 on Linux

Linux Oracle 10gr2

Dropping OEM repository through EMCA command.

Example:

-bash-3.00$ set ORACLE_SID=orcl

-bash-3.00$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Sep 12, 2011 4:35:56 PM

EM Configuration Assistant, Version 10.2.0.5.0 Production

Enter the following information:

Database SID: orcl

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Sep 12, 2011 4:36:15 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /db/app/oracle/Db_1/cfgtoollogs/emca/orcl/emca_2011-09-12_04-35-56-PM.log.

Sep 12, 2011 4:36:17 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

Sep 12, 2011 4:36:37 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Sep 12, 2011 4:36:37 PM oracle.sysman.emcp.EMReposConfig dropRepository

INFO: Dropping the EM repository (this may take a while) ...

Sep 12, 2011 4:39:05 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Enterprise Manager configuration completed successfully

FINISHED EMCA at Sep 12, 2011 4:39:05 PM

NOTE:

1. Do this operation in off peak hours.

2. This command only work beginning with 10.2.0.4.0 or later version of oracle.

How to change SYSMAN user password in 10gr2 on linux (oracle enterprise repository)

Changing SYSMAN user password

Old password: manager1

New password: oracle

OS: LINUX

ORACLE 10GR2

Step

1. stop oem services

2. check db control is stopped

3. connect to the database as a user with DBA privilege and change sysman password

4. connect with new password to check

5. go to $ORACLE_HOME/bin directory and execuate emctl setpasswd command

Details:

-bash-3.00$ set ORACLE_SID=orcl

-bash-3.00$ emctl status dbconsole

TZ set to Asia/Dubai

Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0

https://spioradev.sterling.local:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

------------------------------------------------------------------

Logs are generated in directory /db/app/oracle/Db_1/spioradev.sterling.local_orc l/sysman/log

-bash-3.00$ emctl stop dbconsole

TZ set to Asia/Dubai

Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0

https://spioradev.sterling.local:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

... Stopped.

-bash-3.00$ sqlplus sysman/manager1

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 12 16:31:30 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> password

Changing password for SYSMAN

Old password:

New password:

Retype new password:

Password changed

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Pr oduction

With the Partitioning, OLAP, Data Mining and Real Application Testing options

-bash-3.00$ emctl setpasswd dbconsole

TZ set to Asia/Dubai

Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0

https://spioradev.sterling.local:1158/em/console/aboutApplication

Please enter new repository password:

Repository password successfully updated.

-bash-3.00$ emctl start dbconsole

TZ set to Asia/Dubai

Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0

https://spioradev.sterling.local:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 10g Database Control ............. started.

------------------------------------------------------------------

Logs are generated in directory /db/app/oracle/Db_1/spioradev.sterling.local_orc l/sysman/log

SEVERE: Password file may be missing or configured incorrectly. Oracle 10gr2

When i was configure oracle enterprise manager then received following error.

-bash-3.00$ emca -config dbcontrol db

STARTED EMCA at Sep 12, 2011 3:46:52 PM

EM Configuration Assistant, Version 10.2.0.5.0 Production

Enter the following information:

Database SID: orcl

Listener port number: 1521

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN 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 ................ /db/app/oracle/Db_1

Local hostname ................ spioradev.dummy.local

Listener port number ................ 1521

Database SID ................ orcl

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Sep 12, 2011 3:47:12 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /db/app/oracle/Db_1/cfgtoollogs/emca/orcl/emca_2011-09-12_03-46-52-PM.log.

Sep 12, 2011 3:47:14 PM oracle.sysman.emcp.EMConfig perform

SEVERE: Password file may be missing or configured incorrectly.

Refer to the log file at /db/app/oracle/Db_1/cfgtoollogs/emca/orcl/emca_2011-09-12_03-46-52-PM.log for more details.

Could not complete the configuration. Refer to the log file at /db/app/oracle/Db_1/cfgtoollogs/emca/orcl/emca_2011-09-12_03-46-52-PM.log for more details.

Reason: password file is missing or incorrectly configured.

Password file located under $ORACLE_HOME /dbs directory

File name: orapw$ORACLE_SID

Solution: Recreate password file

-bash-3.00$ orapwd file=orapworcl entries=10

Enter password for SYS: ...enter sys password here...

SEVERE: Dbcontrol Repository already exists. Oracle 10gr2

When trying to create oracle enterprise manager console on linux oracle 10gr2 emca command ended with the following error

-bash-3.00$ export ORACLE_SID=orcl

-bash-3.00$ ./emctl status dbconsole

TZ set to Asia/Dubai

OC4J Configuration issue. /db/app/oracle/Db_1/oc4j/j2ee/OC4J_DBConsole_spioradev .dummy.local_orcl not found.

-bash-3.00$ ./emca -config dbcontrol db -repos create

STARTED EMCA at Sep 12, 2011 3:39:49 PM

EM Configuration Assistant, Version 10.2.0.5.0 Production

Enter the following information:

Database SID: orcl

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 ................ /db/app/oracle/Db_1

Local hostname ................ spioradev.dummy.local

Listener port number ................ 1521

Database SID ................ orcl

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Sep 12, 2011 3:42:06 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /db/app/oracle/Db_1/cfgtoollogs/emca/orc l/emca_2011-09-12_03-39-49-PM.log.

Sep 12, 2011 3:42:07 PM oracle.sysman.emcp.DatabaseChecks performReposChecks

SEVERE: Dbcontrol Repository already exists. Fix the error(s) and run EM Config uration Assistant again in standalone mode.

This error occurred because we are trying to create enterprise repository and configuration files both. but in this case "oem repository is already exist" (sysman user already exist).

Solution: just remove the following parameter from the emca command

./emca -config dbcontrol db -repos create

and reexecute the emca command like below

-bash-3.00$ emca -config dbcontrol db

STARTED EMCA at Sep 12, 2011 4:11:05 PM

EM Configuration Assistant, Version 10.2.0.5.0 Production

Enter the following information:

Database SID: orcl

Listener port number: 1521

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN 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 ................ /db/app/oracle/Db_1

Local hostname ................ spioradev.dummy.local

Listener port number ................ 1521

Database SID ................ orcl

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Sep 12, 2011 4:11:21 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /db/app/oracle/Db_1/cfgtoollogs/emca/orcl/emca_2011-09-12_04-11-05-PM.log.

Sep 12, 2011 4:11:33 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Sep 12, 2011 4:12:33 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Sep 12, 2011 4:12:33 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Sep 12, 2011 4:14:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Sep 12, 2011 4:14:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://spioradev.dummy.local:1158/em <<<<<<<<<<<

Enterprise Manager configuration completed successfully

FINISHED EMCA at Sep 12, 2011 4:14:30 PM

Saturday, September 10, 2011

How to create DB control configuration files and DB Control Repository Objects

Step

1. set oracle_sid=[sid]

2. go to %ORACLE_HOME%/bin directory

3. execute EMCA executable on commandline with the following options

cmd>emca -config dbcontrol db -repos create

NOTE: before execute above statement make sure SQLNET.AUTHENTICATION_SERVICES = (NTS) set in the SQLNET.ORA file.

SQLNET.ORA file located in %ORACLE_HOME%/network/admin directory.

& You must drop the DB control repository objects.

D:\app\Administrator\11.2.0\db\BIN>set oracle_sid=fakpropr

D:\app\Administrator\11.2.0\db\BIN>emca -config dbcontrol db -repos create

STARTED EMCA at Sep 10, 2011 1:48:38 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: fakpropr

Listener port number: 1521

Listener ORACLE_HOME [ D:\app\Administrator\11.2.0\db ]:

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 ................ D:\app\Administrator\11.2.0\db

Local hostname ................ fakproora.dummy.local

Listener ORACLE_HOME ................ D:\app\Administrator\11.2.0\db

Listener port number ................ 1521

Database SID ................ fakpropr

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Sep 10, 2011 1:49:07 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at D:\app\Administrator\cfgtoollogs\emca\fa

kpropr\emca_2011_09_10_13_48_37.log.

Sep 10, 2011 1:49:09 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Sep 10, 2011 2:03:43 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Sep 10, 2011 2:03:52 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepos

itory

INFO: Uploading configuration data to EM repository (this may take a while) ...

Sep 10, 2011 2:05:45 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Sep 10, 2011 2:05:46 PM oracle.sysman.emcp.ParamsManager getLocalListener

WARNING: Error retrieving listener for fakproora.dummy.local

Sep 10, 2011 2:05:50 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Sep 10, 2011 2:06:02 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Sep 10, 2011 2:06:02 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Sep 10, 2011 2:07:48 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Sep 10, 2011 2:07:48 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://fakproora.dummy.local

:1158/em <<<<<<<<<<<

Sep 10, 2011 2:07:52 PM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager

data will be encrypted. The encryption key has been placed in the file: D:/app/

Administrator/11.2.0/db/fakproora.dummy.local_fakpropr/sysman/config/emkey.

ora. 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 Sep 10, 2011 2:07:52 PM

How to delete/remove Oracle Enterprise Manager Console in 11GR2

I received email for asking "how to delete/remove OEM console on 11gr2 windows platform"

How to drop ORACLE ENTERPRISE MANAGER repository and configuration files manually on 11gr2

Step

1. delete DB control repository objects manually

logon as sysdba user

and execute following sql statement

exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>>'SYSMAN.MGMT_NOTIFY_QTABLE',force =>>TRUE);

2. login sys or system and drop SYSMAN AND MANAGEMENT OBJECTS

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

EXEC sysman.setEMUserContext('',5);

REVOKE dba FROM sysman;

DECLARE

CURSOR c1 IS

SELECT owner, synonym_name name

FROM dba_synonyms

WHERE table_owner = 'SYSMAN';

BEGIN

FOR r1 IN c1 LOOP

IF r1.owner = 'PUBLIC' THEN

EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;

ELSE

EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;

END IF;

END LOOP;

END;

DROP USER mgmt_view CASCADE;

DROP ROLE mgmt_user;

DROP USER sysman CASCADE;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

3. Delete DB control configuration files manually

Remove the following directories from your filesystem:

[ORACLE_HOME]/[hostname_sid]

[ORACLE_HOME]/oc4j/j2ee/OC4J_DBConsole_[hostname]_[sid]

If the dbcontrol is upgraded from lower version, for example, from

11.1.0.2.0 to 11.2.0.1.0, then the following directory also needs to be removed from the file system.

[ORACLE_HOME]/[hostname_sid].upgrade

[ORACLE_HOME]/oc4j/j2ee/OC4J_DBConsole_[hostname]_[sid].upgrade

NOTE:

On Windows you also need to delete the DB Console service:

- run regedit

- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

- locate the OracleDBConsole[sid] entry and delete it

Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:

'sc delete [service_name]'

- where [service_name] is the DB Control service name (typically: OracleDBConsole[sid])

###############################################################################

C:\Documents and Settings\Administrator>>set oracle_sid=fakpropr

C:\Documents and Settings\Administrator>>sqlplus sys/manager1 as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 10 12:54:52 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>>'SYSMAN.MGMT_NOTIFY_QTABLE',f

orce =>>TRUE);

PL/SQL procedure successfully completed.

SQL>> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>> startup restrict;

ORACLE instance started.

Total System Global Area 945786880 bytes

Fixed Size 1384328 bytes

Variable Size 637534328 bytes

Database Buffers 301989888 bytes

Redo Buffers 4878336 bytes

Database mounted.

Database opened.

SQL>> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL>> EXEC sysman.setEMUserContext('',5);

PL/SQL procedure successfully completed.

SQL>> REVOKE dba FROM sysman;

REVOKE dba FROM sysman

ERROR at line 1:

ORA-01951: ROLE 'DBA' not granted to 'SYSMAN'

SQL>> DECLARE

2 CURSOR c1 IS

3 SELECT owner, synonym_name name

4 FROM dba_synonyms

5 WHERE table_owner = 'SYSMAN';

6 BEGIN

7 FOR r1 IN c1 LOOP

8 IF r1.owner = 'PUBLIC' THEN

9 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;

10 ELSE

11 EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;

12 END IF;

13 END LOOP;

14 END;

15 /

PL/SQL procedure successfully completed.

SQL>> DROP USER mgmt_view CASCADE;

User dropped.

SQL>> DROP ROLE mgmt_user;

Role dropped.

SQL>> DROP USER sysman CASCADE;

User dropped.

SQL>> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

IMPORTANT: delete the configuration files as mentioned in above Step# 3

C:\>>sc delete OracleDBConsolefakpropr

[SC] DeleteService SUCCESS

DB console cannot connect to the database due to ORA-12516 errors

Oracle 11gr2 on win 32 bit, after upgrading from oracle 11gr1 to 11gr2 ORACLE ENTERPRISE MANAGER was not getting started and failed with ORA-12516 error.

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Cause: processes parameter was set on default value i.e.: 150

Solution: Increase processes parameter value to appropriate digit.

How to increase processes parameter value?

1. connect to database with SYSDBA user

2. execute the following sql statement

alter system set processes = [appropriate value] scope=spfile;

3. shutdown database

shutdown immediate;

4. startup database

startup

5. check processes parameter value

show parameter processes

Now try to restart ORACLE ENTERPRISE MANAGER SERVICES

cmd> set oracle_sid =[sid]

cmd> emctl start dbconsole

Thursday, September 8, 2011

How to change SYSMAN user password in 11gr1 on linux (oracle enterprise respository)

Changing SYSMAN user password.

OS Linux

Oracle 11gr1

Step

1. stop oem services

2. check db control is stopped

3. connect to the database as a user with DBA privilege and change sysman password

4. connect with new password to check

5. go to $ORACLE_HOME/bin directory and execuate emctl setpasswd command

Details:

stop dbconsole services

[oracle@testorapd bin]$ ./emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://testorapd.dummy.local:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

... Stopped.

check dbconsole services stopped properly

[oracle@testorapd bin]$ ./emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://testorapd.dummy.local:1158/em/console/aboutApplication

Oracle Enterprise Manager 11g is not running.

connect with DBA privilege user and change sysman password

[oracle@testorapd bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 8 13:37:07 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sysman identified by oracle;

User altered.

SQL> --check new password to connect sysman with new password

SQL> conn sysman/oracle

Connected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr oduction

With the Partitioning, OLAP, Data Mining and Real Application Testing options

execute emctl setpasswd command to set new password in oem repository.

[oracle@testorapd bin]$ ./emctl setpasswd dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://testorapd.dummy.local:1158/em/console/aboutApplication

Please enter new repository password:

Repository password successfully updated.

start dbconsole services

[oracle@testorapd bin]$ ./emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://testorapd.dummy.local:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ...... started.

------------------------------------------------------------------

Logs are generated in directory /db/product/11.2.0/dbhome_1/testorapd.dummy. local_oramfe/sysman/log

NOTE: Same steps also applicable for WINDOWS platform.

Enterprise Manager is not able to connect to the database instance. The state of the components are listed below.

Today when i try to connect to Oracle Enterprise Manager in 11gr2 on widows server then got below message

Enterprise Manager is not able to connect to the database instance. The state of the components are listed below.

to check what could be the reason i check emoms.trc file located under

%ORACLE_HOME%\hostname.domainname_dbname\sysman\log\emoms.trc

and found the following error

[SystemThreadGroup-8] ERROR app.SessionObjectManager sessionDestroyed.128 - java.sql.SQLException: ORA-28000: the account is locked

java.sql.SQLException: ORA-28000: the account is locked

After that i checked locked account in db and found the following two account is locked.

1. SYSTEM / 2. SYSMAN

SQL> select username,account_status from dba_users

2 where account_status ='LOCKED';

USERNAME ACCOUNT_STATUS

------------------------------ --------------------------------

SYSTEM LOCKED

SYSMAN LOCKED

Once i unlocked the account EM working fine

SQL> alter user SYSMAN account unlock;

User altered.

Tuesday, July 26, 2011

E-Mail Notification Configuration

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Database Version : 10.1.0.2.0
OS Platform : Windows XP sp-2

First Configure SYS AS SYSDBA(super user account)

Database Home Page
---> Administration
--------> Enterprise Manager Administration
------------->ADMINISTRATOR (option)

select SYS user and click on "NOTIFICATION METHODS" and configure SMTP(outgoing mail server)

Note : Add click on "APPLY".

Sign out from SYS user.

and Sign In to NORMAL USER.

If we receive below error because TAJ is not super user privs. we have to first make TAJ as a super user.

Again Sign In to SYS AS SYSDBA user

and on Administrator page click on "CREATE"
select TAJ user and click on finish.

then sign out from SYS user and sign in to TAJ user

Database Home Page
---> Administration
--------> Enterprise Manager Administration
------------->ADMINISTRATION
---------------------> click on PREFERENCES (bottom on current page) (option)

Click on
--->NOTIFICATION
------->Rules

Create New rules for notification

Note: Click on APPLY

Click on
--->NOTIFICATION
------->Schedule

Wednesday, August 20, 2008

ORA-01422 + ORA-06512 + "SYSMAN.MGMT_TIME_SYNC"

Platform: Windows2003
During Patchset applying process from 10gr2(10.2.0.1.0) to 10gr2(10.2.0.4.0) "catupgrd.sql" ended with below error.
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 193
ORA-06512: at line 2
All oracle component was successfully upgraded except OEM becuase of above error.
Clause: After searching on metalink and google didn't find proper reason why it is happened.
Solution: Re-Run catupgrd.sql script

Tuesday, February 5, 2008

Email Notification: Export Log Error

Steps

1. Create directory
2. Create table in database for exportlog through external table
3. Create procedure for sent to email or check err msg In exportlog table
4. Create sql script which daily import data from exportlog file to exportlog table.
5. Schedule SQL script to run after export command.

SQL> conn system/oracle
SQL> create directory log as 'c:\log';
SQL> grant read,write on directory LOG to username;

SQL> -- sql script
Create sql script which create exportlog table and import exportlog file to database

DROP TABLE EXPORTLOG;

CREATE TABLE exportlog (msg varchar2(200))
organization external (
type oracle_loader
default directory log
access parameters (
records delimited by newline
)
location ('exportlog.log')
)
reject limit 1000;

exec pro_exportlog;

SQL>-- Create procedure which check error msg in exportlog table and sent email

Procedure: PRO_EXPORTLOG

CREATE OR REPLACE PROCEDURE "SCOTT"."PRO_EXPORTLOG"
AS
SendorAddress Varchar2(30) := 'xxx.xxxx.local';
ReceiverAddress varchar2(30) := 'm.taj@fakhruddin.ae';
EmailServer varchar2(30) := 'xxx.xxxxxx.local';
Port number := 25;
conn UTL_SMTP.CONNECTION;
v_result varchar2(2000);

BEGIN
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress );
utl_smtp.open_data (conn);
utl_smtp.write_data(conn, chr(13));
utl_smtp.write_data(conn, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )||chr(13));
utl_smtp.write_data(conn, 'From:'||SendorAddress||chr(13));
utl_smtp.write_data(conn, 'To: '||ReceiverAddress ||chr(13));
utl_smtp.write_data(conn, 'Subject: Mail Through ORACLE Database!' ||chr(13));
utl_smtp.write_data(conn, chr(13));
FOR rec IN (select msg from exportlog
where msg LIKE 'EXP-%'
OR msg LIKE '%ORA-%'
OR msg LIKE '%warning%')
LOOP

v_result := rec.msg;
utl_smtp.write_data( conn,v_result||chr(13) );
END LOOP ;
UTL_SMTP.close_data(conn);
UTL_SMTP.quit(conn);
END;

----------------------------------------------------
4. Create bat file and schedule this sql script

I tested above all and the result:

Date: 05 Feb 08 11:26:24
From:xxx.xxxxx.local
To: m.taj@xxxxxxxx.xx
Subject: Mail Through ORACLE Database!

Export terminated successfully without warnings.
----------------------------------------------------
Date: 05 Feb 08 11:23:47
From:xxxx.xxxxxx.local
To: m.taj@xxxxx.xx
Subject: Mail Through ORACLE Database!

EXP-00011: FGC.YYYYY does not exist
Export terminated successfully with warnings.

Any query or comment are welcome.

Wednesday, September 26, 2007

Sending-email-from-an-Oracle-10g-Server

Database 10gr1

When we configure Oracle E-MAIL notification method through OEM then it is not include notification for DB shutdown or startup events. For these two events we have to configure manually.

Connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql

Package created.

Synonym created.

SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'smtp_exchange_server_name' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

Create two trigger for startup and shutdown event on database which send us email notification

SQL> create or replace trigger scott.db_shutdown
2 before shutdown on database
3 begin
4 sys.utl_mail.send (
5 sender =>'star_taj@yahoo.com',
6 recipients =>'star_taj@yahoo.com',
7 subject => 'Oracle Database Server DOWN',
8 message => 'May be DB Server Down for maintenance’||
‘ but also contact to DBA for further details. ’
9 );
10 end;
11 /

Trigger created.

SQL> create or replace trigger scott.db_startup
2 after startup on database
3 begin
4 sys.utl_mail.send (
5 sender =>'star_taj@yahoo.com',
6 recipients =>'star_taj@yahoo.com',
7 subject => 'Oracle Database Server UP',
8 message => 'DB Server OPEN for normal use.'
9 );
10 end;
11 /

Trigger created.

Tuesday, July 31, 2007

error during dbconsole shutting down...

Database Version : 10.1.0.3.0
OS : Linux Update 4

If you getting below error message during shutdown dbconsole services on Linux
then please refer Metalink Note : 335532.1

[oracle@ora bin]$ export ORACLE_SID=orcl
[oracle@ora bin]$ ./emctl status dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.1.0.3.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://xxx.xxxxx.local:5500/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /db/app/oracle/OraHome_1/xxxx.xxxxxx.local_orcl/sysman/log

[oracle@ora bin]$ ./emctl stop dbconsole

Oracle Enterprise Manager 10g Database Control Release 10.1.0.3.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://xxx.xxxxx.local:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...

--- Failed to shutdown DBConsole Gracefully ---
failed.

Sunday, July 22, 2007

How to Change SYSMAN User Password

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Carefully when change SYSMAN password
through below procedure we can change SYSMAN user password

1.connect to database server with SYSMAN user

SQL> conn sysman/sysman1@oem
Connected.
SQL> password
Changing password for SYSMAN
Old password:
New password:
Retype new password:
Password changed\

2. stop DBCONSOLE services

C:\>emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.5.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Localhost:5501/em/console/aboutApplication
The OracleDBConsoleoem service is stopping............
The OracleDBConsoleoem service was stopped successfully.

3. edit emoms.properties , targets.xml files
Located : emoms.properties ===$ORACLE_HOME/localhost.domainname_sid/sysman/config/
Located : targets.xml ========$ORACLE_HOME/localhost.domainname_sid/sysman/emd/

emoms.properties

oracle.sysman.eml.mntr.emdRepPwd="c7021fd3720a0f18" replace with PASSWORD
oracle.sysman.eml.mntr.emdRepPwdEncrypted="TRUE" replace with FALSE

oracle.sysman.eml.mntr.emdRepPwd=ORACLE
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

targets.xml

[Property NAME="UserName" VALUE="80ec9025e45b2d20" ENCRYPTED="TRUE"/]

[Property NAME="password" VALUE="94124d177df7c5d9" ENCRYPTED="TRUE"/]

Replace username value with “SYSMAN” and password value with “SYSMAN PASSWORD”

[Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="TRUE"/]

[Property NAME="password" VALUE="ORACLE" ENCRYPTED="TRUE"/]

4. startup dbconsole services

C:\>set oracle_sid=oem

C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.5.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://taj.domainname.local:5501/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The OracleDBConsoleoe
m service is starting................
The OracleDBConsoleoem service was started successfully.

5.Unlock SYSMAN account

SQL> alter user sysman account unlock;

User altered.

E-Mail Notification

This capability allows you to implement specific IT practices in response to specific alerts.

Getting Email Notifications

http://www.oracle.com/technology/products/oem/pdf/BestPractices-Notifications.pdf

http://www.oracle.com/technology/products/oem/htdocs/demos.html

Email Example

Name=taj.domainname.local
Type=Host
Host=taj.domainname.local
Metric=Filesystem Space Available (%)
Mount Point=C:\
Timestamp=Jul 9, 2007 11:30:27 AM GST
Severity=Clear
Message=CLEARED - Filesystem C:\ has only 42.98% available space
Rule Name=Host Availability and Critical States
Rule Owner=SYSMAN

Enterprise Manager Directory Structure for DBConsole

Search This Blog

Monday, September 12, 2011

Saturday, September 10, 2011

Thursday, September 8, 2011

Tuesday, July 26, 2011

Wednesday, August 20, 2008

Tuesday, February 5, 2008

Wednesday, September 26, 2007

Tuesday, July 31, 2007

Sunday, July 22, 2007