How to Clone Oracle Home

zip -r dbhome_1.zip /u01/app/oracle/product/11.2.0/dbhome_1

unzip -d / dbhome_1.zip

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

cd $ORACLE_HOME/clone/bin 

$ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1" OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

Oracle Fine Grained Auditing  At Schema Level

1). Add a policy on a table FGA_TEST in the SCOTT schema
2). The policy will report on any dml actions on this table affecting its 2 columns 'esal' and 'designation'
3). Another user HACKER will execute dml queries on this table and we will try and investigate whether the actions are reported
4). The corresponding event handler of this policy will be in the FGA_HANDLER  schema.we will also find out if the audit event was handled properly

--conn sys / as sysdba
grant select any table to scott;
grant create user to scott;
grant resource,connect to scott;
--create a new schema FGA_HANDLER which will contain the event handler

SQL> create user fga_handler1 identified by fga_handler1;

conn sys
grant resource,connect to fga_handler1;
grant execute on DBMS_FGA to fga_handler1;

--create a new table ,FGA_TEST in SCOTT schema, on which we will enforce the audit conditions(policy) with the help of the DBMS_FGA package

SQL> create table fga_test (empno number,empname varchar2(30),age number,designation varchar2(20));

--Let us insert some prototype table rows
 
 insert into FGA_TEST values(10000,'Carol',100,'Developer');
 insert into FGA_TEST values(10001,'Esther',200,'Analyst' );
 insert into FGA_TEST values(10002,'Bob',300,'Manager') ;

 --ADD_POLICY Procedure

BEGIN DBMS_FGA.ADD_POLICY ( object_schema => 'SCOTT', object_name => 'FGA_TEST', policy_name => 'FGA_TEST_POLICY1', audit_condition => NULL, audit_column => 'AGE,DESIGNATION', handler_schema => 'FGA_HANDLER1', handler_module => 'sp_audit', enable => true,statement_types => 'INSERT,UPDATE,DELETE' );end;
/
--connect to the fga_handler1 schema

SQL> conn fga_handler1/fga_handler1

--create the table to store audit records

SQL> create table audit_event (audit_event_no number);

--The procedure adds a record to the table above any time it executeds and the column audit_event_no acts as counter displaying the number of times the procedure has been executed

SQL> create or replace procedure sp_audit(object_schema in varchar2,object_name in varchar2,policy_name in varchar2) as count number;begin select nvl(max(audit_event_no),0) into count from audit_event;insert into audit_event values (count+1); commit; end;
/
--Finally create another schema ‘HACKER’ which tries to manipulate the values of the ‘age’ or ‘designation’ columns of the FGA_TEST table

SQL> conn sys / as sysdba

SQL>  create user hacker1 identified by hacker1;
            grant resource,connect to hacker1;
            grant all on scott.fga_test to hacker1;

 --Connect as hacker and update the policed columns(s)


SQL> conn hacker/hacker;
SQL> update scott.fga_test set designation='CIO' where empname='carol';

--connect with SCOTT to see the dba_fga_audit_trail view to find if the event was recorded

SQL>  conn scott /scott

SQL> col DB_USER for a12
SQL> col OS_USER for a14
SQL> col POLICY_NAME for a16
SQL> col SQL_TEXT for a70
SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from      dba_fga_audit_trail where POLICY_NAME='FGA_TEST_POLICY1';

--Connect to the FGA_HANDLER schema to see if the event handler(sp_audit) was called

SQL> conn hacker/hacker;

--Now, execute the following from HACKER schema

SQL> select * from scott.fga_test;

--Attack to change designation
    
update SCOTT.FGA_TEST set designation='HR' where name='Bob';

--conn as sysdba to see who did what and when

conn sys / as sysdba

SQL> col DB_USER for a12
SQL> col OS_USER for a14
SQL> col POLICY_NAME for a16
SQL> col SQL_TEXT for a70
SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='FGA_TEST_POLICY';


--Have Fun,

Install OEM on a virtualBox using Oracle Linux 6

Steps:

1.install Oracle VirtualBox

2.Setup a virtual machine with 4-6 GB

3.Install Linux Software - ISO

4.Update kernel

#  yum install oracle-rdbms-server-11gR2-preinstall

5.Prepare the environment to install OEM repository Database

a).Check for updates (this will take a while to refresh):

# yum update

b).Create group and user

groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
useradd -u 1100 -g oinstall -G dba,oper oracle

c).Create Passwd for oracle user
passwd oracle

d).Create Directories

mkdir -p /u01/app/oracle
mkdir -p /u01/tmp
chown -R oracle:oinstall /u01/
chmod -R 755 /u01/
e).Change the /etc/security/limits.conf file and add;

oracle   soft   nofile    4096

f).Change the  /etc/security/limits.d/90-nproc.conf

  -- Change this line
                *           soft    nproc    1024
--add this instead  this
                *          -       nproc    16384

g).Disable secure linux by editing the "/etc/selinux/config

vim /etc/selinux/config
--change line
              SELINUX=enforcing
--to this
              SELINUX=disabled

h).Disable Firewall

# service iptables save
# service iptables stop
# chkconfig iptables off

i).Add IP's to the hosts file

vim /etc/hosts
192.168.136.131     db.db.com      db          --the db repostory
192.168.136.138     upgrade         upgrade  --the OEM

j).Add the following lines to the “vim /etc/security/limits.conf” file.

oracle              soft     nproc   2047

oracle              hard    nproc   16384
oracle              soft     nofile   4096
oracle              hard    nofile   65536
oracle              soft     stack    10240

k).Add or amend the following lines to the “/etc/sysctl.conf” file

fs.aio-max-nr = 1048576
fs.file-max = 6815744
 kernel.shmall = 2097152
#kernel.shmmax = 1054504960
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
 kernel.sem = 250 32000 100 128
 net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
 net.core.wmem_default=262144
 net.core.wmem_max=1048586

l).Run the following command to change the current kernel parameters

 /sbin/sysctl –p

m).Add this to the vim /etc/pam.d/login

 session    required     pam_limits.so

n).Login as Oracle User and update .bash_profile variables

$ vim .bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/sqldeveloper/sqldeveloper  /bin:$ORACLE_HOME/jdk/bin
export PATH
ORACLE_SID=dblab
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE ORACLE_SID TMPDIR TMP
LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib
export LD_LIBRARY_PATH
NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
export NLS_DATE_FORMAT
set -o vi
EDITOR=vim
export EDITOR
ORAENV_ASK=NO
#. oraenv

o).Copy  binaries ,unzip and run the Installer to create the OMR - Oracle Management Repostory

               ./runInstaller

Run NETCA and make listener static after its complete

$netca

SID_LIST_LISTENER =
           (SID_LIST =
             (SID_DESC =
               (GLOBAL_DBNAME = dblab.world)
               (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
               (SID_NAME = dblab)
             )
           )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.4)(PORT = 1521))
    )
  )
#ADR_BASE_LISTENER = /u01/app/oracle

Run DBCA to create database

$dbca 

Check if the Listener is UP (MUST be up and the database as well)

$lsnrctl status
$snrctl start

On the Repository-OMR

 sqlplus / AS SYSDBA
ALTER SYSTEM SET processes=300 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
ALTER SYSTEM SET job_queue_processes=20 SCOPE=SPFILE;
 

Restart the instance

shu imediate

startup 

INSTALL OMS -Oracle Management Service

6.Install Linux software,update Kernel and set up the files as required for a standard installation---As above

7.Create groups and user

groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
useradd -u 1100 -g oinstall -G dba,oper oracle

8. Create Passwd for oracle user
passwd oracle

9.set .bash_profile variables for user Oracle

vim .bash_profile

OMS_HOME=/u01/app/oracle/oms12cr4; export OMS_HOME
AGENT_BASE=/u01/app/oracle/agent_base; export AGENT_BASE
export PATH
set -o vi
EDITOR=vim
export EDITOR
ORAENV_ASK=NO
#. oraenv
10.Create Directories

$ mkdir -p /u01/app/oracle/oms12cr4
$ mkdir -p /u01/app/oracle/agent_base

11.Download the OEM binaries from oracle: em12104_linux64_disk1, em12104_linux64_disk2, em12104_linux64_disk3, copy the binaries and unzip to install

$ mkdir em12cr4
$ unzip -d em12cr4 em12104_linux64_disk1.zip
$ unzip -d em12cr4 em12104_linux64_disk2.zip
$ unzip -d em12cr4 em12104_linux64_disk3.zip
$ cd em12cr4

12.Run the installer
$ ./runInstaller

13.a) Deconfigure/Drop  the EM from the repostory-- for single instance  ON THE DATABASE

cd $ORACLE_HOME/bin
$./emca -deconfig dbcontrol db -repos drop -SYS_PWD  xxxxxx  -SYSMAN_PWD  xxxxxx

 b)Deconfigure/Drop  the EM from the repostory-- for RAC database
cd $ORACLE_HOME/bin
$./emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD xxxxxx -SYSMAN_PWD xxxxxx
 

 14.Redirect Management Agent to another host
--View OEM version
Setup menu, select Manage Cloud Control, then select Management Services.
Setup menu, select Extensibility, then select Plug-ins.
15.Add TARGET  & Discover

Add target
setup>add target manually>add host>+add>enter ipadd or domain for the host-eg 192.168.136.134> select platform/os
the host run-if not available download>click next>enter the agent_base directory-/u01/app/oracle/agent_base >+add user
credentials- use OS user eg: user:oracle,pass:oracle123(go to the db:/etc/sudoers & add oracle ALL=(ALL) ALL> next>click deploy

--Discover targets
setup>add target manually>select agent type>oracle database,listener,automatic storage management>click add using guided process>discover
the target>click search and select the host to discover from the list>click next>select target,enter password for user DBSNMP-if the user
is locked,go to the db and unlock>next to discover

Installation snapshots at a glance

Installation Details

UPGRADE Enterprise Manager 12c Cloud Control from 12.1.0.3 to 12.1.0.5

NB: Kindly note that the Repository DB  and OEM is ready installed and running

Environments: Repos db                  OEM  
 Repo_host: db.db.com                       Hostname:upgrade.db.com
 Repos_sid: DBLAB                        

--check if SYSMAN and DBSNMP has execute privilege to DBMS_RANDOM package and
--PUBLIC role has NO access to DBMS_RANDOM

SQL> GRANT EXECUTE ON dbms_random TO dbsnmp;
SQL> GRANT EXECUTE ON dbms_random TO sysman;
SQL> REVOKE EXECUTE ON dbms_random FROM public;

--Run emctl to copy EMKey from emkey.ora file to the management repository database

./emctl config emkey -copy_to_repos_from_file -repos_host db.db.com -repos_port 1521 -repos_sid DBLAB -repos_user sysman -emkey_file $OMS_HOME/sysman/config/emkey.ora

--check for invalid packages on repository database

SELECT owner, object_name, object_type,status FROM   dba_objects WHERE  status = 'INVALID' AND owner IN ('SYS', 'SYSTEM', 'SYSMAN', 'MGMT_VIEW', 'DBSNMP', 'SYSMAN_MDS');

--Compile invalid objects

SQL> EXEC UTL_RECOMP.recomp_serial('SYS');
SQL> EXEC UTL_RECOMP.recomp_serial('DBSNMP');
SQL> EXEC UTL_RECOMP.recomp_serial('SYSMAN');

--Stop the OMS

cd $OMS_HOME/oms/bin
./emctl stop oms -all

----INCASE of ERROR : Insufficient privileges to access Database Vault features

1.SQL> GRANT SELECT_CATALOG_ROLE to sys;
2.--Stop the database, Database Control console process, and listener
                               $ sqlplus sys as sysdba
                               SQL> shu immediate                       
                               $ emctl stop dbconsole
                               $ lsnrctl stop
3.--For Oracle RAC installations, shut down each database instance as follows:
                              $ srvctl stop database -d db_name
4.--Disable the Oracle Database Vault option --on the repostory DB
                             cd $ORACLE_HOME/rdbms/lib
                             $ make -f ins_rdbms.mk dv_off
                             $ cd $ORACLE_HOME/bin
                             relink all
5.--After the above is completed run;
                             $ chopt disable dv
6.--Stop the  listener ,database and the Database Control console process
                             $ lsnrctl start
                             $ sqlplus sys as sysdba
                             SQL> startup
                             $ emctl start dbconsole
---INCASE of  processes ERROR: run the alter statements and proceed
  sqlplus / as sysdba
  ALTER SYSTEM SET processes=300 SCOPE=SPFILE;
  ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;
  ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;
  ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;
  ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
  ALTER SYSTEM SET job_queue_processes=20 SCOPE=SPFILE;

--UPGRADE AGENT after a sucessiful upgrade --on the Console

Setup>cloud control>agent upgade task> select the agents you want to upgrade>next>......>click done
Setup>cloud control>post >agent upgade task>select the agents from the list>click submit

--Before upgrade

[oracle@upgrade bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.3.0
OMS Version       : 12.1.0.3.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/agent_base/agent_inst
Agent Binaries    : /u01/app/oracle/agent_base/core/12.1.0.3.0
Agent Process ID  : 3187
Parent Process ID : 3140
Agent URL         : https://upgrade.db.com:3872/emd/main/
Repository URL    : https://upgrade.db.com:4904/empbs/upload
Started at        : 2015-10-15 17:25:59
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2015-10-15 21:48:21
Last attempted upload                        : 2015-10-15 21:48:21
Total Megabytes of XML files uploaded so far : 0.43
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 45.06%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2015-10-15 21:54:25
Last successful heartbeat to OMS             : 2015-10-15 21:54:25
Next scheduled heartbeat to OMS              : 2015-10-15 21:55:25

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

--After upgrade

[oracle@upgrade bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : 12.1.0.5.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/agent_base/agent_inst
Agent Log Directory    : /u01/app/oracle/agent_base/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/agent_base/core/12.1.0.5.0
Agent Process ID       : 18623
Parent Process ID      : 18579
Agent URL              : https://upgrade.db.com:3872/emd/main/
Local Agent URL in NAT : https://upgrade.db.com:3872/emd/main/
Repository URL         : https://upgrade.db.com:4904/empbs/upload
Started at             : 2015-10-16 08:04:03
Started by user        : oracle
Operating System       : Linux version 3.8.13-16.2.1.el6uek.x86_64 (amd64)
Last Reload            : 2015-10-16 08:06:49
Last successful upload                       : 2015-10-16 08:49:38
Last attempted upload                        : 2015-10-16 08:49:38
Total Megabytes of XML files uploaded so far : 0.29
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 47.48%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2015-10-16 08:53:20
Last successful heartbeat to OMS             : 2015-10-16 08:53:20
Next scheduled heartbeat to OMS              : 2015-10-16 08:54:20

---------------------------------------------------------------
Agent is Running and Ready
Have fun and learn more !!!

Oracle Database Monitoring Scripts

 /*List of Accessed Objects */
sET PAGESIZE 60
SET LINESIZE 300
COLUMN type FORMAT a40
COLUMN sid FORMAT 9999
COLUMN object FORMAT a40
COLUMN owner FORMAT a20
SELECT a.type, Substr(a.owner,1,30) owner, a.sid,Substr(a.object,1,30) object
FROM   v$access a WHERE  a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;

/*CPU Usage for Active Sessions*/
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000
SELECT   s.username,  t.sid,  s.serial#, SUM(VALUE/100) as "cpu usage (seconds)"
FROM   v$session s, v$sesstat t, v$statname n WHERE   t.STATISTIC# = n.STATISTIC#
AND   NAME like '%CPU used by this session%' AND    t.SID = s.SID AND    s.status='ACTIVE'
AND    s.username is not null GROUP BY username,t.sid,s.serial#;

/*Display all logged sessions*/
 SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A35
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username, s.osuser, s.sid,s.serial#, p.spid,s.lockwait,
 s.status,s.service_name,s.module,s.machine,s.program,TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM   v$session s, v$process p WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

/*Displays Last Analyzed Details for a given Schema,All schema owners if 'ALL' specified*/
 SET PAGESIZE 60
SET LINESIZE 300
SELECT t.owner, t.table_name AS "Table Name", t.num_rows AS "Rows", t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB", to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed" FROM   dba_tables t,v$parameter p WHERE t.owner = Decode(Upper('&&Table_Owner'), 'ALL', t.owner, Upper('&&Table_Owner')) AND   p.name = 'db_block_size' ORDER by t.owner,t.last_analyzed,t.table_name ;

/*Lists the volume of archived redo by hour for the specified day */
SET VERIFY OFF PAGESIZE 30
WITH hours AS (
 SELECT TRUNC(SYSDATE) - &1 + ((level-1)/24) AS hours
 FROM   dual  CONNECT BY level < = 24
)
SELECT h.hours AS date_hour,
 ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb FROM   hours h
 LEFT OUTER JOIN v$archived_log al ON h.hours = TRUNC(al.first_time, 'HH24')
GROUP BY h.hours ORDER BY h.hours;

/* Archived logs list*/
 sELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,  Count(1)
Count#,  Min(RECID) Min#, Max(RECID) Max# FROM
v$log_history GROUP BY  To_Char(First_Time,'YYYY-MM-DD')
ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#,
Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ;
 
/*Archive Generation History*/
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
 
/* Archivelog history*/
col "MONTH" FOR a14
col "DAY" for a28
select to_char(trunc(first_time), 'Month') Month, to_char(trunc(first_time), 'Day : DD-Mon-YYYY') Day, count(*) counts from v$log_history where trunc(first_time) > last_day(sysdate-100) +1 group by trunc(first_time);

/*Cache hit ratio*/
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';

/* Check database locks and blockings*/
sELECT SUBSTR(TO_CHAR(session_id),1,5) "SID", SUBSTR(lock_type,1,15) "Lock Type",       SUBSTR(mode_held,1,15) "Mode Held",  SUBSTR(blocking_others,1,15) "Blocking?"  FROM dba_locks ;

/*Displays information on the current wait states for all active database sessions*/
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT a.username,a.osuser,a.sid,a.serial#, d.spid AS process_id, a.wait_class,a.seconds_in_wait,       a.state,a.blocking_session,a.blocking_session_status,a.module,TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM   v$session a,v$process d WHERE  a.paddr  = d.addr AND    a.status = 'ACTIVE' ORDER BY 1,2;

/*Displays the recovery status of each datafile  */ 
SET LINESIZE 500
SET PAGESIZE 500
SET FEEDBACK OFF
col Datafile for a44
SELECT Substr(a.name,1,60) "Datafile", b.status "Status" FROM   v$datafile a,v$backup b WHERE  a.file# = b.file#;
SET PAGESIZE 14
SET FEEDBACK ON

/*Displays datafiles information   */
SET LINESIZE 200
COL FILE_NAME FOR a48
SELECT file_id, file_name,ROUND(bytes/1024/1024/1024) AS size_gb,ROUND(maxbytes/1024/1024/1024) AS max_size_gb,autoextensible,increment_by,status FROM   dba_data_files ORDER BY file_name;

/*Displays general information about the database*/
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF
SELECT * FROM   v$database;
SELECT * FROM   v$instance;
SELECT * FROM   v$version;
SELECT a.name,a.value FROM   v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",NVL(c.status,'UNKNOWN') "Status" FROM   v$controlfile c ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",NVL(d.status,'UNKNOWN') "Status",d.enabled "Enabled",LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d ORDER BY 1;
SELECT l.group# "Group",Substr(l.member,1,60) "Logfile",NVL(l.status,'UNKNOWN') "Status" FROM   v$logfile l ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

/*Database Object Counts*/
prompt
col owner for a18
select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(case when a.object_type = 'TABLE' then 1 else null end) "Tables",
count(case when a.object_type = 'INDEX' then 1 else null end) "Indexes",
count(case when a.object_type = 'PACKAGE' then 1 else null end) "Packages",
count(case when a.object_type = 'SEQUENCE' then 1 else null end) "Sequences",
count(case when a.object_type = 'TRIGGER' then 1 else null end) "Triggers",
count(case when a.object_type not in ('PACKAGE','TABLE','INDEX','SEQUENCE','TRIGGER') then 1 else null end) "Other",count(case when 1 = 1 then 1 else null end) "Total" from dba_objects a group by rollup(a.owner) ;

/*Database size*/
prompt
with dbsize as
(select ' '||tablespace_name tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_data_files group by tablespace_name
union all
select ' '||tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_temp_files group by tablespace_name
union all
select 'LOGFILES',sum(bytes)/(1024*1024) size_mb from v$log
)
select * from dbsize
union all
select 'Total',sum(size_mb) from dbsize order by 1;

/*check ITL waits*/
Set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
select owner,object_name||' '||subobject_name object_name, value from v$segment_statistics where statistic_name = 'ITL waits' and value > 0 order by 3,1,2;

/* check log sizes*/
SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,  Count(1)
Count#,  Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY  To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#, Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ; 

/*Provides information about memory resize operations*/
SET LINESIZE 200
COLUMN parameter FORMAT A25
SELECT start_time,end_time,component,oper_type,oper_mode,parameter,ROUND(initial_size/1024/1204) AS initial_size_mb,
ROUND(target_size/1024/1204) AS target_size_mb,ROUND(final_size/1024/1204) AS final_size_mb,status
FROM   v$memory_resize_ops ORDER BY start_time;

/*memory allocation to all db sessions*/
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A20
COLUMN module FORMAT A50
COLUMN program FORMAT A50
SELECT NVL(a.username,'(oracle)') AS username,a.module,a.program,Trunc(b.value/1024) AS Memory_KB FROM   v$session a,v$sesstat b,v$statname c WHERE  a.sid = b.sid AND    b.statistic# = c.statistic# AND    c.name = 'session pga memory' AND    a.program IS NOT NULL ORDER BY b.value DESC;

Create Trigger to Monitor Database Errors

First, we have to create a table where the errors are stored, but make sure it's a use who has global rights on the database:

CREATE TABLE error_log (
  server_error VARCHAR2(100),
  osuser VARCHAR2(30),
  username VARCHAR2(30),
  machine VARCHAR2(64),
  process VARCHAR2(24),
  program VARCHAR2(48),
  stmt VARCHAR2(4000),
  msg VARCHAR2(4000),
  date_created DATE  
);

After we've created the table, we simply add a trigger with is fired by "AFTER SERVERERROR ON DATABASE":

CREATE OR REPLACE
TRIGGER error_log_trigger 
 AFTER SERVERERROR ON DATABASE
DECLARE
 username_  error_log.username%TYPE;
 osuser_    error_log.osuser%TYPE;
 machine_   error_log.machine%TYPE;
 process_   error_log.process%TYPE;
 program_   error_log.program%TYPE;
  stmt_      VARCHAR2(4000);
  msg_       VARCHAR2(4000);
  sql_text_  ora_name_list_t;
BEGIN
   FOR i IN 1..NVL(ora_sql_txt(sql_text_), 0) LOOP  
    stmt_ := SUBSTR(stmt_ || sql_text_(i) ,1,4000);
  END LOOP;
   FOR i IN 1..ora_server_error_depth LOOP
    msg_ := SUBSTR(msg_ || ora_server_error_msg(i) ,1,4000);
  END LOOP;
   SELECT osuser, username, machine, process, program
  INTO   osuser_, username_, machine_, process_, program_
  FROM   sys.v_$session
  WHERE  audsid = USERENV('SESSIONID');
   INSERT INTO error_log VALUES (dbms_standard.server_error(1), osuser_, username_, machine_, process_, program_, stmt_, msg_, SYSDATE);
END;

Tablespace and datafile monitoring

To check Tablespace free space:

• SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

To check Tablespace by datafile:

• SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id;

To Check Tablespace used and free space %:

• SELECT /* + RULE */  df.tablespace_name "Tablespace",df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes  FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace,fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+)  = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC;

--or--

• Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Tablespace (File wise) used and Free space

• SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;

To check Growth rate of  Tablespace
 
Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.

• SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM') GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname ORDER BY ts.tsname, days;

List all Tablespaces with free space < 10% or full space> 90%

• Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add from (select tablespace_name,0 tots,sum(bytes) sumb from dba_free_space a group by tablespace_name union Select tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name having sum(a.sumb)*100/sum(a.tots) < 10 order by pct_free;

Script to find all object Occupied space for a Tablespace

• Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments where TABLESPACE_NAME = 'SDH_HRMS_DBF' group by OWNER, SEGMENT_NAME;

Which schema are taking how much space

• Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size  from dba_segments group by owner) seg  where obj.owner  = seg.owner(+)  order    by 3 desc ,2 desc, 1; 

 To Check Default Temporary Tablespace Name:

• Select * from database_properties where PROPERTY_NAME like '%DEFAULT%'; 

 To know default and Temporary Tablespace for particualr User:

• Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS'; 

 To know Default Tablespace for All User:

• Select default_tablespace,temporary_tablespace,username from dba_users; 

To Check Datafiles used and Free Space:  

• SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;  

To check Used free space in Temporary Tablespace: 

• SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM   V$temp_space_header GROUP  BY tablespace_name; 
• SELECT   A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM     v$sort_segment A, ( SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM   v$tablespace B, v$tempfile C WHERE    B.ts#= C.ts#  GROUP BY B.name, C.block_size ) D WHERE    A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;  

Sort (Temp) space used by Session

• SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; 

 Sort (Temp) Space Usage by Statement

• SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_nameORDER BY S.sid;  

Who is using which UNDO or TEMP segment?  

• SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,NVL(s.username, 'None') orauser,s.program, r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = 'db_block_size';  

Who is using the Temp Segment?  

• SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name  = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;  

Total Size and Free Size of Database:

• Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v$datafile  union all select bytes from v$tempfile   union all  select bytes from v$log) used,  (select sum(bytes) as p from dba_free_space) free group by free.p;  

To find used space of datafiles:

• SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments; 

IO status of all of the datafiles in database:

• WITH total_io AS  (SELECT SUM (phyrds + phywrts) sum_io FROM v$filestat) SELECT   NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT,  phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio  FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c WHERE a.file# = b.file# ORDER BY a.file#;  

Displays Smallest size the datafiles can shrink to without a re-organize.

• SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes FROM   dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to  FROM   dba_extents  GROUP by file_id) b  WHERE  a.file_id = b.file_id ORDER BY a.tablespace_name, a.file_name; 

Scripts to Find datafiles increment details:

• Select  SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name   tabsp_name,fn.name   file_name,ddf.bytes/1024/1024 cur_size, decode(fex.maxextend,NULL,ddf.bytes/1024 1024,fex.maxextend*tn.blocksize/1024/1024) max_size,nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024  inc_by from  sys.v_$dbfile fn,    sys.ts$  tn,    sys.filext$ fex,    sys.file$  ft,    dba_data_files ddf where    fn.file# = ft.file# and  fn.file# = ddf.file_id and    tn.ts# = ft.ts# and    fn.file# = fex.file#(+) order by 1;