Start/Stop Oracle Enterprise Manager 12c

To Start enterprise manager 12c you need to follow the below steps :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export OMS_HOME=/u03/app/oracle/Middleware/om12c/oms/
export AGENT_HOME=/u03/app/oracle/Middleware/agent12c/agent_inst/

  • ensure database and listener is running
  • Run the below command from $OMS_HOME

emctl start oms

  • Run the below command from $AGENT_HOME

emctl start agent 

to Stop all the services you need to do the below :

 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export OMS_HOME=/u03/app/oracle/Middleware/om12c/oms/
export AGENT_HOME=/u03/app/oracle/Middleware/agent12c/agent_inst/

  • Run the below command from $OMS_HOME

emctl stop oms -all

  • Run the below command from $AGENT_HOME

emctl stop agent 

  • Now you can shutdown database and listener.

Thank you
Osama mustafa


Short Look for Oracle Privileges

Oracle Database Contain two type of privileges one called System and other called Objects each one grant it to use as they need, But interesting entity called Public. it’s more Role than users , and every user created in database assign to Public by default unfortunately nothing can be done to change that. so we can say it’s seems like User Group, so if you grant anything of privileges to public this means that everyone of users database will have this privilege ( System or Object ) So you need to becareful of you doing while dealing with public.

Public is everyone access to database .PUBLIC is an entity that can be granted any privilege and assigned any role. All database users automatically inherit all the rights assigned to PUBLIC. It’s a simple concept designed for convenience. Granting permissions to PUBLIC is easy to do, and often the easiest way to get a system working, however, the security implications of using the PUBLIC group to assign permissions are significant.PUBLIC should be treated with care and used sparinglyPUBLIC should be treated with care and used sparingly.

Before go deeply with this subject we need to understand Role and Privileges in oracle database,as you all know Oracle Privileges control the rights to see, you could Modify, create or alter database depend on privileged you have,as i mention earlier two Privileges in database exists System and Object.

System privileges are not related to any specific object or schema. Object privileges are just the opposite, those that are directly related to a specific object or schema.check the examples below

System Privilege
Object Privilege

to check system privileges in database you need to check the following DBA_ DICTIONARY :

SQL> desc dba_sys_privs;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                            VARCHAR2(3)

Sample data From table :

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
SYS                            CREATE OPERATOR                                 NO
SYS                            EXECUTE ANY OPERATOR                     NO
SYS                            CREATE DIMENSION                                NO
SYS                            ADMINISTER RESOURCE MANAGER     NO

Another example i want to check scott and his system privileges :

SQL> select * from dba_sys_privs where GRANTEE = ‘SCOTT’;
GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
SCOTT                          UNLIMITED TABLESPACE                     NO

Now Let’s Talk About Object Privileges :

SQL> desc dba_tab_privs;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 GRANTEE                                   NOT NULL  VARCHAR2(30)
 OWNER                                     NOT NULL  VARCHAR2(30)
 TABLE_NAME                          NOT NULL  VARCHAR2(30)
 GRANTOR                                 NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 GRANTABLE                                                  VARCHAR2(3)
 HIERARCHY                                                   VARCHAR2(3)

more complex huh ? Read Oracle Documentation Here

SQL> select OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE from dba_tab_privs where GRANTEE = ‘SCOTT’;
no rows selected.

What about Roles ? as you we knows Oracle Comes with Pre defined such as DBA, Resource , and connect also you can create your own Role which is Set of privileges to manage Users Privileges.
 user can be a member of more then one role, and roles can even be members of other roles.
Users in company leaves their jobs , Travel or even don’t need privileges in futures Create Oracle Role will make all this Manage is easy since . Privileges are then granted only to the roles, never to a specific user,if the privileges ever change, they are changed on the role and automatically take effect for all users with the role.and you check

SQL> desc dba_roles ;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ROLE                                      NOT NULL         VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)

For information about this table you can Read Oracle documentation here.

SQL> desc dba_role_privs
 Name                                      Null?    Type
 —————————————– ——– —————————-
 GRANTEE                                                   VARCHAR2(30)
 GRANTED_ROLE                  NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)

The above table checks the privileges granted to Role, you can refer to Oracle Documentation here
check the below query to determine Which User Have DBA Role :

SQL> select GRANTEE, ADMIN_OPTION, DEFAULT_ROLE from dba_role_privs where
GRANTEE                        ADM DEF
—————————— — —
SYS                                  YES YES
SYSMAN                         NO  YES
SYSTEM                         YES YES

As conclusion Public can be Granted any of Roles or users,While it is easy and fully supported to grant roles to PUBLIC i don’t recommended to do that  give one case why to do that ?
Check the user who has Public  :

SQL> select * from dba_role_privs where GRANTEE = ‘PUBLIC’;
no rows selected

And this exactly what we want not Public roles to any users.
Thank you 
Osama Mustafa

Disable Auditing Using Oradebug

in 2011 Laszlo Toth Show some Oracle tricks to disable Auditing In Oracle Database included with SYS auditing using simple command Called oradebug, Just as note oradebug is undocumented in all oracle versions and if you have sysdba role privileges you can do a lot of things with this command :

the below is the demonstration :

sqlplus / as sysdba

SQL> — get the offset for oradebug

SQL> select fsv.KSMFSNAM,sga.*
from x$ksmfsv fsv, x$ksmmem sga
where sga.addr=fsv.KSMFSADR
and fsv.ksmfsnam like ‘kzaflg_%’;

—————- ———- ———- —————-
kzaflg_ 0000000060031BB0 26652 1 0000000000000001

SQL> show parameter audit;

———————————— ———– ——————————
audit_file_dest string /u01/app/oracle/admin/PSALES/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED

SQL> oradebug poke 0x60031bb0 1 0
BEFORE: [060031BB0, 060031BB4) = 00000001
AFTER: [060031BB0, 060031BB4) = 00000000

Just as note with oradebug you Audit vault become useless. another tricks that we can use oradebug to call Database command using OS 🙂 

SQL> oradebug call system    “ls -la >/tmp/hacktivity.txt”

Thank you
Osama mustafa

Basic Database Securiy GuideLines

Implementing any Database Security is not easy process which it’s Complex since the security has to be looked at OS Layer, Database, Network , application code, and backup lot of things you need to check if you need secure database you can’t ignore one part of this components.

You need to know even experts/Guru dose not have complete understanding for this list, maybe they have knowledge about it but not that much, But the most important things in securing database ( new one ) or securing application is early understanding security model very early in the development process  and how to develop it. you can read the this book  “Oracle security: Step-by-step” by Pete Finnigan he mentioned lot of basic steps to secure oracle database.

From a high level perspective, security is always about risk. If you think that your system is impenetrable, think again. Someone else will always be building a better mousetrap.

For example if we asked ourself as DBA About SQL injection one of the TOP ten Threats in Database
it happens because somebody is writing a Web application that accesses your database which mean if this code writes incorrectly, what happened if the hacker get access to your database thru this code.because of this DBA needs to understand who is access to database  and make sure the developers are accessing in a secure way.

Internal/External hacker going to search for Holes in your application to get in,as we know there’s one of attack type used called Buffer Overflow because of this you need to keep you system is up to date patched like usual people most of them not doing that since they worried about if that patch will broke their system or application.

Today a lot of people are ignoring the security side of the job. Many companies don’t have the resources. Until you get hacked, and until you lose data, then you think you can get by with minimal security.

I mention before the in my article about Basic steps to secure Oracle Database some of this article provided with examples i will advise today to bookmark this to keep updated with Oracle Security patches lot of tips to talk about and need to share it here as soon as i can.

Thank you
Osama Mustafa

Check total Size for Ram/memory For Oracle

Check total size of Ram/Memory For Each Database :

select decode( grouping(nm), 1, ‘total’, nm ) nm, round(sum(val/1024/1024)) mb
  select ‘sga’ nm, sum(value) val
     from v$sga
   union all
   select ‘pga’, sum(value)
    from v$sysstat
   where name = ‘session pga memory’
   group by rollup(nm)

The Orginial Post from Tom Kyte.

Thank you
Osama Mustafa

Connect to Oracle without Tnsnames.ora

Regarding to oracle documentation :

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

Example how connection define :

orcl1 =
     (ADDRESS = (PROTOCOL = TCP)(HOST = my-server )(PORT = 1521))
   (SERVICE_NAME = orcl1)

Sometimes you can Bypass the tnsnames.ora and connect to sqlplus without even Create new connection, all you have to do is  put all of the connectivity information in your connection string this type of connection called “EZCONNECT”.

sqlplus username/password@[//]host[:port][/service_name]

 and to enable EZCONNECT you should add the below line in sqlnet.ora which is located in the same directory $ORACLE_HOME/network/admin
check the below examples :

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

The Below using the default listener port 1521 :

1- sqlplus scott/tiger@myservername/orcl1

Different Listener port will be like the below :

2-  sqlplus scott/tiger@myservername:1522/orcl1

3-   sqlplus USER/PASSWORD@//myservername:1521/orcl1

and if you want to disable EZCONNECT For Secuirty Reasons add the below line in :


Thank you
Osama Mustafa