Solution ORA-01940: cannot drop a user that is currently connected

SQL> drop user sprtsg4 cascade;
drop user sprtsg4 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected



///
Before the kill session lock the account for further connection establish.


SQL>
SQL> alter user sprtsg4 account lock;

User altered.

SQL>
SQL> SET LINESIZE 100
SQL> COLUMN spid FORMAT A10
SQL> COLUMN username FORMAT A10
SQL> COLUMN program FORMAT A45
SQL>
SQL> SELECT s.inst_id,
  2         s.sid,
  3         s.serial#,
  4         p.spid,
       s.username,
  5    6         s.program
  7  FROM   gv$session s
  8         JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';  9

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         49       7543 4360       SYS        sqlplus@acs7 (TNS V1-V3)
         1        100       4663 4153       SYS        sqlplus@acs7 (TNS V1-V3)
         1        141      15044 4626       SPRTSG4    JDBC Thin Client
         1         16      33035 4628       SPRTSG4    JDBC Thin Client
         1         71      16974 4630       SPRTSG4    JDBC Thin Client
         1         83      58119 4632       SPRTSG4    JDBC Thin Client
         1         90      61930 4634       SPRTSG4    JDBC Thin Client
         1        121      28526 3065       SYSMAN     OMS
         1         31      44985 3076       SYSMAN     OMS
         1        122      31487 3078       SYSMAN     OMS
         1        140      11579 3366       DBSNMP     emagent@acs7 (TNS V1-V3)

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1        129        121 25590      DBSNMP     emagent@acs7 (TNS V1-V3)
         1        152       3737 4636       SPRTSG4    JDBC Thin Client
         1         79        615 4615       SYSMAN     OMS
         1        119      22358 4638       SPRTSG4    JDBC Thin Client
         1          6       6899 4640       SPRTSG4    JDBC Thin Client
         1         46       7585 4644       SPRTSG4    JDBC Thin Client
         1        151      38346 4648       SPRTSG4    JDBC Thin Client

18 rows selected.

SQL> alter system kill session '&id,&serial';
Enter value for id: 151
Enter value for serial: 38346
old   1: alter system kill session '&id,&serial'
new   1: alter system kill session '151,38346'

System altered.

SQL> /
Enter value for id: 46
Enter value for serial: 7585
old   1: alter system kill session '&id,&serial'
new   1: alter system kill session '46,7585'

System altered.

SQL>

SQL>

SQL> drop user sprtsg4 cascade;

User dropped.

SQL> SQL> SQL> SQL> SQL> SQL>

ERROR DURING OEM CONFIGURE:SEVERE: Dbcontrol Repository already exists. Fix the error(s) and run EM Configuration Assistant again in standalone mode

bash-4.3$
bash-4.3$ emca -config dbcontrol db -repos create

STARTED EMCA at Apr 25, 2015 9:07:52 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: SBDV
Listener port number: 1522
Listener ORACLE_HOME [ /u01/app/oracle/product ]:
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 ................ /u01/app/oracle/product

Local hostname ................ HMSISBDVDB
Listener ORACLE_HOME ................ /u01/app/oracle/product
Listener port number ................ 1522
Database SID ................ SBDV
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 25, 2015 9:08:21 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/SBDV/emca_2015_04_25_21_07_52.log.
Apr 25, 2015 9:08:22 PM oracle.sysman.emcp.DatabaseChecks performReposChecks
SEVERE: Dbcontrol Repository already exists.  Fix the error(s) and run EM Configuration Assistant again in standalone mode.
bash-4.3$


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





bash-4.3$
bash-4.3$ hostname
HMSISBDVDB
bash-4.3$
bash-4.3$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 25, 2015 9:14:09 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: SBDV
Listener port number: 1522
Listener ORACLE_HOME [ /u01/app/oracle/product ]:
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 ................ /u01/app/oracle/product

Local hostname ................ HMSISBDVDB
Listener ORACLE_HOME ................ /u01/app/oracle/product
Listener port number ................ 1522
Database SID ................ SBDV
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 25, 2015 9:14:32 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/SBDV/emca_2015_04_25_21_14_08.log.
Apr 25, 2015 9:14:33 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 25, 2015 9:15:39 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Apr 25, 2015 9:15:39 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...

Apr 25, 2015 9:18:42 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 25, 2015 9:18:45 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...

Apr 25, 2015 9:20:06 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 25, 2015 9:20:08 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 25, 2015 9:20:17 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 25, 2015 9:20:17 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 25, 2015 9:20:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 25, 2015 9:20:39 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://HMSISBDVDB:1158/em <<<<<<<<<<<
Apr 25, 2015 9:20:43 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: /u01/app/oracle/product/HMSISBDVDB_SBDV/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 Apr 25, 2015 9:20:43 PM
bash-4.3$
bash-4.3$
bash-4.3$
bash-4.3$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://HMSISBDVDB:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/HMSISBDVDB_SBDV/sysman/log
bash-4.3$
bash-4.3$

CREATE AND DROP ORACLE ENTERPRISE MANAGER IN ORACLE 11GR2

#####################DROP OEM##############################

[oracle@node1 ~]$
[oracle@node1 ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 24, 2015 9:35:26 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

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

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 24, 2015 9:35:49 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ora11g/emca_2015_04_24_21_35_23.log.
Apr 24, 2015 9:35:49 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Apr 24, 2015 9:35:49 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 24, 2015 9:35:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 24, 2015 9:38:11 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 24, 2015 9:38:11 PM
[oracle@node1 ~]$
[oracle@node1 ~]$

###########################CREATE OEM######################


During First time installation you give sysman user password give any password as usual

[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Apr 24, 2015 11:57:19 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ora11g
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
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 ................ /u01/app/oracle/product/11.2.0/db_1

Local hostname ................ node1
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ ora11g
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 24, 2015 11:57:47 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ora11g/emca_2015_04_24_23_57_18.log.
Apr 24, 2015 11:57:49 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
^[OB^[OB^[OB^[OB^[OB^[OB

Apr 25, 2015 12:03:37 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 25, 2015 12:03:40 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 25, 2015 12:04:47 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 25, 2015 12:04:49 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Apr 25, 2015 12:04:49 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Apr 25, 2015 12:05:13 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Apr 25, 2015 12:05:13 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 25, 2015 12:06:24 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 25, 2015 12:06:24 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 25, 2015 12:08:00 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 25, 2015 12:08:00 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://node1:1158/em <<<<<<<<<<<
Apr 25, 2015 12:08:14 AM 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: /u01/app/oracle/product/11.2.0/db_1/node1_ora11g/sysman/config/emkey.ora.   Please 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 Apr 25, 2015 12:08:14 AM
[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$
login as: oracle
oracle@192.168.68.40's password:
Last login: Sat Apr 25 00:04:45 2015 from 192.168.68.44
[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://node1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/node1_ora11g/sysman/log
[oracle@node1 ~]$
[oracle@node1 ~]$
[oracle@node1 ~]$

AUTO START STOP ORACLE DATABASE SCRIPT

Save this script with oracle.sh

 execute with root user.


#! /bin/sh
##! /usr/bin/ksh
# Description: Starts and stops the Oracle database and listeners
# See how we were called.
export ORACLE_HOME=/u01/app/oracle/product
export LISTENER_NAME=LISTENER
case "$1" in
  start)
        echo -n "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start ${LISTENER_NAME}" >> /var/log/oracle
        echo "Done."
        echo ""
        echo -n "Starting Oracle Databases: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbstart ${ORACLE_HOME} >> /var/log/oracle
        echo "Done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  stop)
        echo -n "Shutting Down Oracle Listeners: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c "lsnrctl stop ${LISTENER_NAME}" >> /var/log/oracle
        echo "Done."
        echo -n "Shutting Down Oracle Databases: "
        su - oracle -c dbshut ${ORACLE_HOME} >> /var/log/oracle
        echo "Done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  *)
        echo "Usage: oracle {start|stop}"
        exit 1
esac
bash-4.3# ./oracle.sh start
bash-4.3# ./oracle.sh stop

Enjoy