ERROR: PRVF-7573 Sufficient Swap Size Is Not Available

Swap Size - This is a prerequisite condition to test whether sufficient total swap space is available on the system.
    Expected Value : 7.9062GB (8290304KB)
    Actual Value      : 2GB (2097144KB)
    Details               : PRVF-7573 : Sufficient swap size is not available on node "carol.db.com" [Required = 7.9062GB (8290304KB) ; Found = 2GB (2097144KB)               
Cause:  The swap size found does not meet the minimum requirement. 
Action:  Increase swap size to at least meet the minimum swap space requirement.7.9062 - 2 = 5.9GB (minimum swap need)

FIX:

1). Check what swap size
    # swapon –s
    Filename                                Type            Size    Used    Priority
    /dev/xvda3                              partition       2097144 0       -1

2). Create a file that you’ll use for swap with dd command with   as root
   
     dd if=/dev/zero of={/swapfile path} bs={size of swap}  count=1048576
   
    Example:-
   
    [root@carol]#dd if=/dev/zero of=/home/swapfile bs=6048 count=1048576
    Note :- Above command will be create the 6Gb swapfile on /home location as  swapfile

3). Set up a Linux swap area
    #mkswap /home/swapfile

4). Enabling the swap file
    #swapon /home/swapfile
    #swapon –a

5). Status of add swap
    [root@carol]# swapon -s
    Filename                                Type            Size    Used    Priority
    /dev/xvda3                              partition       2097144 0       -1
    /home/swapfile                          file             6097144 0        -2

6). Update /etc/fstab  
    #vi /etc/fstab
    /home/swapfile        none             swap   sw      0 0

Then retry the installation  

Hope it helps!!..

Make ERROR during Oracle Database installation

Exception String: Error in invoking target 'agent nmhs' of makefile '/opt/oracle/app/product/11.2.0/db_1/sysman/lib/ins_emagent.mk'.

FIX:

Edit the file $ORACLE_HOME/sysman/lib/ins_emagent.mk

 #===========================
 #  emdctl
 #===========================

    $(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL)
       
To

#===========================
#  emdctl
#===========================

    $(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL) -lnnz11
       
       
Now save and click “RETRY” in installer prompt. The installation should successfully complete.

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;