Starting and Connecting to Database Control (OEM via browser)

After the installation of the database,you will get a link like below for OEM(Oracle Enterprise Manager) via which you can monitor the database from browser.


The link will be something like:


https://hostname:port/em


Ex:  https://localhost:1521/em


Where hostname is the name of the machine on which database control is ruuning,and port is the TCP port on whihc it is listening for incoming connection requests.


To find the port details either you can check tnsnames.ora or listener.ora file ($ORACLE_HOME/network/admin) or best way is to go to $ORACLE_HOME/install/portlist.ini which lists all the ports configured by the OUI and DBCA.


Description About Database Control:


Database Control is a tool for managing one database(though this database can be clustered).

If there are several instances runing from the same ORACLE_HOME,each instance should have their own database control.


All communications with database control are over HTPPS.

To start the Database Control,use emctl utility located in the $ORACLE_HOME/bin directory.The 3 commands to start or stop Database Conctrol and to check it's status are


$ORACLE_HOME/bin> emctl start dbconsole

$ORACLE_HOME/bin> emctl stop dbconsole

$ORACLE_HOME/bin> emctl status dbconsole


P.N-> For above commands to work,3 environments variables must be set: PATH,ORACLE_HOME and ORACLE_SID.

PATH is needed to allow the OS to find emctl utility.

The ORACLE_HOME & ORACLE_SID are needed so that emctl can find the Database Control Configuration files.

These configuration files are in 3 places :


 $ORACLE_HOME/sysman/config ---> this is general  configuration directives that will apply to all Database Control instances running from the Oracle Home.(one per database).

 The $ORACLE_HOME/hostname_sid/sysman/config   and a similarly named directory beneath $ORACLE_HOME/oc4j/j2ee contain details for the database control that manages one particualr database.


Unless and until we will not start the database control the OEM link will never gonna work for us.

Sometimes you will get a request from your their OEM link is not working.As a DBA you have to ask whether the issue is same for all the users or only he/she alone facing the issue.If answer is for all,then please do the below workaround to resolve the issue.


servername@oracle> set the profile like . bash.profile  or . /oracle.profile  <<<as per yor db environment>>


or 


servername@oracle> set ORACLE_SID=PROD


servername@oracle> emctl status dbconsole


if the status showing: ORACLE ENTERPRISER MANAGER 11g is not running.


servername@oracle>emctl start dbconsole


The status will show:

starting ORACLE ENTERPRISER MANAGER 11g  database control......the ORACLEDBCONSOLEOCP11g service is starting...........

The Oracle DBCconsoleocp11g service was started successfully.


servername@oracle>emctl status dbconsole


The status will be: Oracle Enterpriser Manager 11g is running.


And you are done & problem solved :) :) :)






 

Oracle User Audit- Logon & Log Off Trigger

Step1: Check audit is enabled or not .If not enabled it and bounce the database.

Audit can be done in two ways:  Database level or OS level.

SQL> show parameter audit

Check paramter audit_trail is =(DB|OS|db,extended | xml | xml,extended) means audit is enabled otherwise if it is NONE means audit trail is not enabled.

If audit_trail is NONE please make it enable using below steps (by default is disabled)

SQL>ALTER SYSTEM SET audit_trail=db or os SCOPE=SPFILE;
SQL>shu immediate;
SQL>startup;

"Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS.

SQL>@$ORACLE_HOME/rdbms/admin/cataudit.sql


P.N Oracle recommends that you use the OS setting, particularly if you are using an ultra-secure database configuration.

Step 2: Crating user audit table which hold the login and logoff information of the users.

sqlplus
connect / AS SYSDBA;

create table
   LOGIN_AUDIT_INFO_ALL
(
   user_id           varchar2(100),
   session_id           number,
   host              varchar2(1000),
   logon_day                 date,
   logon_time        varchar2(100),
   logoff_day                date,
   logoff_time       varchar2(10)
) tablespace USERS
;

Step3: Creating Logon Trigger

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END;
/



If want  to exclude  login details of SYS,SYSTEM,DBSNMP and SYSMAN user.

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
if UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))
NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') -- list of users to ignore
THEN
insert into LOGIN_AUDIT_INFO_ALL values(
   SYS_CONTEXT('USERENV', 'SESSION_USER'),
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END IF;
END;
/

STEP4: Creating LOGOFF trigger

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END;
/

If want to exclude the logoff details of SYS,SYSMAN,DBSNMP and SYSTEM user

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
if UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))
NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') -- list of users to ignore
THEN
insert into LOGIN_AUDIT_INFO_ALL values(
  SYS_CONTEXT('USERENV', 'SESSION_USER'),
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END IF;
END;
/









Just to confirm:

Trigger name and created date 
select CREATED,OBJECT_NAME from dba_objects where OBJECT_TYPE='TRIGGER' and owner='SYS';
Table name and created date
SQL> select created,object_name from dba_objects where object_name='LOGIN_AUDIT_INFO_ALL' and object_type='TABLE';

Step5- Check the audit table after opening another session

COLUMN HOST FORMAT A45
COLUMN USER_ID FORMAT A18
SELECT * FROM SYS.LOGIN_AUDIT_INFO_ALL;

Step6- To disable and enable the trigger

ALTER TRIGGER SYS.LOGON_AUDIT_TRIGGER DISABLE;
ALTER TRIGGER SYS.LOGOFF_AUDIT_TRIGGER DISABLE;

ALTER TRIGGER SYS.LOGON_AUDIT_TRIGGER ENABLE;
ALTER TRIGGER SYS.LOGOFF_AUDIT_TRIGGER ENABLE;

Step7- To Purge Audit Data

TRUNCATE TABLE LOGIN_AUDIT_INFO_ALL;

Step8: To drop Trigger and table

DROP TRIGGER SYS.LOGON_AUDIT_TRIGGER;
DROP TRIGGER SYS.LOGOFF_AUDIT_TRIGGER;
DROP TABLE SYS.LOGIN_AUDIT_INFO_ALL;


Hope my blog help you !! Thanks and enjoy :) :)  




 







 








 














 

Copying Oracle Users,Roles,Privileges from DBMS_METADATA.GET_DDL()

In my today's blog I am going to highlight the usage of  utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary. Using this powerful utility, you can punch individual objects or an entire Oracle schema.

Sometimes you get an requirement to create a Y user as like of X user and provide all the priviliges and roles X user does have to Y user. In this case the DBMS_METADATA.GET_DDL() has the best utility to use rather than manually thinking about what sql commands you goona used to create Y user as like of X user.

So here we go.

USER CREATION DDL

First check what is the create statement which include the default tablespace and temporary tablespace and in which profile it is set to be.

Here X user: IBM  :)
and Y user: TCS  :)

set head off
set pages 0
set long 9999999

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username';

 CREATE USER "IBM" IDENTIFIED BY VALUES 'S:8EF6C6807D8B18E063CE1AA5ACC6C4165BA0893EA4DE660394B2049C41;96556E541B38CFEC'
    DEFAULT TABLESPACE "USERS"
    TEMPORARY TABLESPACE "TEMP"

Now create user TCS user like above with default tablespace and temporary tablespace.

ROLE ASSIGNED TO X USER and provide the same roles to Y user

SELECT DBMS_METADATA.GET_granted_DDL('ROLE_GRANT',username) as script from DBA_USERS where username='&username';

 GRANT "CONNECT" TO "IBM"

 GRANT "RESOURCE" TO "IBM"

Get the system grants and object grants using function GET_GRANTED_DLL

 SELECT DBMS_METADATA.GET_granted_DDL('SYSTEM_GRANT',username) as script from DBA_USERS where username='&username';

SAMPLE OUTPUT

  GRANT ADMINISTER SQL TUNING SET TO "IBM"
 GRANT ALTER ANY SQL PROFILE TO "IBM"
GRANT DROP ANY SQL PROFILE TO "IBM"
GRANT SELECT ANY TRANSACTION TO "IBM"

>> To generate the script for object privileges

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','IBM') from dual;

>> To generate the script for tablespace quota

SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'IBM') from dual;

  In a single command

Note : ORA-31608 and ORA-06512 will occur if there is no output ( no rows selected) for any of these statements.

-- This will generate the DDL for the user and add his objects,system and role grants

SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1

UNION ALL

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;

Oracle Database Up Time

The below query help  you out to find the open_mode,log_mode and startup time of the oracle database.

set linesize 200
set trimout on
set space 1
col INSTANCE for a10
col MODE for a11
col ROLE for a10
col HOST for a20
col STATUS for a7
col STARTUP_TIME for a20

select NAME "DB_NAME",INSTANCE_NAME "INSTANCE",OPEN_MODE,LOG_MODE,STATUS,DATABASE_ROLE "ROLE",HOST_NAME "HOST", to_char(STARTUP_TIME,'MON/DD/YYYY hh24:mi:ss')startup_time from gv$database NATURAL JOIN gv$instance; 

DB_NAME   INSTANCE    OPEN_MODE   LOG_MODE       STATUS   ROLE           HOST                 STARTUP_TIME
--------- ---------- ----------- ------- ---------- -------------------- ------------------------------------------------------------

PROD         PROD     READ WRITE        ARCHIVELOG    OPEN    PRIMARY   ibxdsdgfhhf         AUG/10/2015 12:10:26

 Checking for Blocking Locks

 Connect to you Database and query the "dba_blockers" view

SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
           121

 Query to check which Session  is blocking

SQL> select s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' ) is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
 from v$lock l1, v$session s1, v$lock l2, v$session s2
 where s1.sid=l1.sid and s2.sid=l2.sid
 and l1.BLOCK=1 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------

 ( SID=121 ) is blocking  ( SID=345 )
 Killing Blocking Locks

ALTER SYSTEM KILL SESSION '121';

How to get list of grantee who does have two different roles and comabine all grantee and granted_role to one output

Sometimes the requirement comes from Apps Team to provide the list of users whose role starts with XYZ or ABCD for a specific profile say DEFAULT.

Usually what we are doing, we are fetching all the list of users who does have XYZ role make one report and agaon for ABCD roles and making another report..But in my today's blog how to combine both the report to one.

Scenario 1: I am finding all the grantee who does have roles starts with JAVA

P.N - KHATAI is my database name :) :)

SQL> select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
granted_role like 'JAVA%' and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;


NAME      USERNAME                       ACCOUNT_STATUS
--------- ------------------------------ -----------------------
PROFILE                        GRANTED_ROLE
------------------------------ ------------------------------
KHATAI    ORDSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVAIDPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVADEBUGPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVAUSERPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_DEPLOY

KHATAI    SYS                            OPEN
DEFAULT                        JAVASYSPRIV

KHATAI    XDB                            EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV


10 rows selected.

SQL>

Scenario 2: I am finding all the grantee who does have roles starts with OLAP


select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
granted_role like 'OLAP%' and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;


NAME      USERNAME                       ACCOUNT_STATUS                   PROFIL
E                        GRANTED_ROLE
--------- ------------------------------ -------------------------------- ------
------------------------ ------------------------------
KHATAI    OLAPSYS                        EXPIRED & LOCKED                 DEFAUL
T                        OLAP_DBA
KHATAI    OWBSYS                         EXPIRED & LOCKED                 DEFAUL
T                        OLAP_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_XS_ADMIN
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAPI_TRACE_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_DBA

6 rows selected.


Now I want to combine both the roles and want a single output with (10rows for JAVA+6 rows of OLAP role) = 16rows.

select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
REGEXP_LIKE(w.granted_role,'^(JAVA|OLAP)') and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;



NAME      USERNAME                       ACCOUNT_STATUS
--------- ------------------------------ ------------------------------
PROFILE                        GRANTED_ROLE
------------------------------ ------------------------------
KHATAI    OLAPSYS                        EXPIRED & LOCKED
DEFAULT                        OLAP_DBA

KHATAI    ORDSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVA_ADMIN

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        OLAP_USER

KHATAI    SYS                            OPEN
DEFAULT                        JAVAUSERPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVADEBUGPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAPI_TRACE_USER

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_DBA

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVAIDPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_USER

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_DEPLOY

KHATAI    SYS                            OPEN
DEFAULT                        JAVASYSPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_XS_ADMIN

KHATAI    XDB                            EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV


16 rows selected.

SQL>


Hope it will helpful to you !! Enjoy !!

Nth highest salary from table

SQL> select * from emp order by salary desc;

EMPNAME                  SALARY
-------------------- ----------
ra                       566565
su                        89899
fg                        89899
am                        34343
dk                         1000
hjh                        1000

6 rows selected.

Now I want to find the Nth highest salary.


Query 1:

2nd highest: give n =2

SELECT empname, salary
FROM emp e1
WHERE '&N'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2
WHERE e2.salary > e1.salary);

EMPNAME                  SALARY
-------------------- ----------
su                        89899
fg                        89899

for 1st highest salary: N=1 

SQL> /
Enter value for n: 1
old   3: WHERE '&N'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2
new   3: WHERE '1'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2

EMPNAME                  SALARY
-------------------- ----------
ra                       566565

for better explanation,please follow below link

http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server


 Query2: 

select * from (select empname,salary,dense_rank() over(order by salary desc) as rk from emp) where rk ='&h';

1st highest salary
 
 SQL> select * from (select empname,salary,dense_rank() over(order by salary desc
) as rk from emp) where rk ='&n';
Enter value for n: 1
old   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='&n'
new   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='1'

EMPNAME                  SALARY         RK
-------------------- ---------- ----------
ra                       566565          1

2nd highest salary

SQL> /
Enter value for n: 2
old   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='&n'
new   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='2'

EMPNAME                  SALARY         RK
-------------------- ---------- ----------
su                        89899          2
fg                        89899          2

SQL>