generate awr report for database

// generate awr report for database ( not instance) for period 8 -9 am

conn sys/manager@&1 as sysdba
variable begin_snap_id number
variable end_snap_id number
variable db_id number
begin
  select distinct snap_id into :begin_snap_id from dba_hist_snapshot
  where BEGIN_INTERVAL_TIME like '01-SEP-13 07%AM%';
  :end_snap_id := :begin_snap_id + 1;
  select DBID into :db_id from v$database;
end;
/
define  num_days     = 1;
define  db_name      = &1;
define  dbid         = :db_id;
define  begin_snap   = :begin_snap_id;
define  end_snap     = :end_snap_id;
define  report_type  = 'html';
define  instance_numbers_or_ALL = '1,2,3'
define  time         = 8am
define  report_name  = /tmp/AWR/awr_&1._&time..html
@@?/rdbms/admin/awrgrpti
exit

dynamic tablespaces creation script

// dynamic tablespaces creation script

// crtbs.sh

while read tbs

   do

   echo "CREATE SMALLFILE TABLESPACE "$tbs " DATAFILE

   '/oradata01/oracle/oradata/${ORACLE_SID}/$tbs.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

   LOGGING

   ONLINE

   EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 10M

   SEGMENT SPACE MANAGEMENT AUTO

   ;"

   echo " "

   done< crtbs.dat

// crtbs.dat

MAX_DATA

MAX_INDX

list table parent / childs

//  finds child table in recursive . input parent table

Prompt By default starting tables are those that does not referenced other one
Accept Table_ CHAR PROMPT "Starting tables (% allowed) ? "
Set Termout OFF
Column table_ NOPRINT NEW_VALUE Table_
Select nvl(upper('&Table_'),'*NO REF*') table_ from dual
/
Set Termout ON
Column line_ FORMAT A120 HEADING "Foreign Key Tree"
Prompt
Set feedback off
With
  data as (
      /* Tables/FK along with the referenced table/constraints */
    select decode(b.table_name, a.table_name, '*SELF REF*', b.table_name)
             table_name,
           b.constraint_name,
           a.table_name r_table_name,
           a.constraint_name r_unq_name
    from user_constraints a, user_constraints b
    where b.r_owner = a.owner
      and b.r_constraint_name = a.constraint_name
      and b.constraint_type = 'R'
      and a.constraint_type in ('P', 'U')
    union all
      /* Referenced tables that referenced noone */
    select a.table_name, null, null, null
    from user_constraints a
    where exists ( select 1 from user_constraints b
                   where b.r_owner = a.owner
                     and b.r_constraint_name = a.constraint_name
                     and b.constraint_type = 'R'
                     and a.constraint_type in ('P', 'U') )
      and not exists ( select 1 from user_constraints b
                       where b.table_name = a.table_name
                         and b.constraint_type = 'R' )
  )
Select lpad(' ',3*(level-1)) ||
       decode(table_name, '*SELF REF*', r_table_name, table_name) ||
       decode(r_table_name,
              null, '',
              ' ('||constraint_name||' ref. '||
              r_table_name||'/'||r_unq_name||')' )||
       decode(table_name,
              '*SELF REF*', ' ...',
              decode(connect_by_iscycle, 1, ' ...'))
from data
connect by nocycle prior table_name = r_table_name
start with    ( '&Table_' = '*NO REF*' and r_table_name is null )
           or ( '&Table_' != '*NO REF*' and table_name like '&Table_' )
/

compile invalids

// get_dependencies.sql

// dependencies for packages etc

SELECT distinct referenced_name, referenced_type
  FROM  dba_dependencies
  WHERE
    REFERENCED_OWNER=upper('&owner')
  and name =upper('&objname') order by referenced_type ;

//
exec dbms_utility.get_dependency('PACKAGE BODY', 'SCOTT','MYPKG');

// get_ddl.sql
SET LONG 2000000
SET PAGESIZE 0
select DBMS_METADATA.GET_DDL('PACKAGE_BODY','MYPKG') from dual;

// gen_compile.sql

set pages 0
spool gen_compile.out
accept inuser char prompt 'Enter Userid: '
select 'alter '||case when object_type='PACKAGE BODY' then 'PACKAGE' else object_type end||' '||owner||'.'||object_name||' compile;'
from dba_objects
where owner like upper('&inuser')
and status='INVALID'
order by object_type
/
spool off

//  count_invalids.sql

rem
ttitle 'Count of Invalid Objects'
rem
col owner format a8 heading 'OWNER'
col invalids format 999,999 heading '# of INVALIDS OBJECTS'
break on owner skip 1
rem
select owner,
       object_type,
       count(*)  invalids
  from   dba_objects
 where  owner like upper('&owner')
  and   status ='INVALID'
 group by rollup(owner, object_type);rem  count_invalids.sql



//  list_invalids.sql

rem
set time on
set timing on
set echo off
set feedback on
rem
break on owner on type skip 1
rem
select substr(owner,1,12) owner
       ,substr(object_type,1,30) type
       ,substr(object_name,1,30) object
       ,status
  from
    dba_objects
 where
   owner like upper('&owner')
 and
   object_type like upper('&type')
 and
   status <> 'VALID'
 order by owner,object_type,object_name;
rem
rem end of script


// object not recompiling as it is in use

   COLUMN lock_id2 FORMAT A30
   select to_char(SESSION_ID,'999') sid ,
      substr(LOCK_TYPE,1,30) Type,
      substr(lock_id1,1,45) Object_Name,
      substr(mode_held,1,4) HELD,
      substr(mode_requested,1,4) REQ,
      lock_id2 lock_addr
   FROM dba_lock_internal;


you may try following steps, too

// step 1

Identify sessions that are using PACKAGES using the query below.

The following query displays all objects currently being accessed by each session:

set lines 120
col username format a20
col owner    format a15
col object   format a30
col type     format a15

select a.sid, a.serial#, a.username, b.owner, b.object, b.type
  from
    v$session a,
    v$access  b
  where
    a.sid = b.sid
  and
    b.object like upper('%&object%');

// step2

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Usefull Unix ls commands

Don’t you hate when you want to see the specific time on a file and ls -l gives you this on older files:

oracle@ > ls -ltr
total 2328
-rw-r--r-- 1 oracle oinstall  22413 Jun  5 10:59 ashrpt_1_0605_0900.txt

-rw-r--r-- 1 oracle oinstall    247 Jun  5 11:31 afiedt.buf

-rw-r--r-- 1 oracle oinstall 255934 Jun  5 11:33 awrrpt_1_11390_11391.txt

-rw-r--r-- 1 oracle oinstall  14594 Jun  5 11:33 addmrpt_1_11390_11391.txt

Fortunately, ls gives us some options:

oracle@  > ls -ltr --full-time
total 2328

-rw-r--r-- 1 oracle oinstall  22413 2013-06-05 10:59:55.000000000 -0400 ashrpt_1_0605_0900.txt

-rw-r--r-- 1 oracle oinstall    247 2013-06-05 11:31:29.000000000 -0400 afiedt.buf

-rw-r--r-- 1 oracle oinstall 255934 2013-06-05 11:33:09.000000000 -0400 awrrpt_1_11390_11391.txt

-rw-r--r-- 1 oracle oinstall  14594 2013-06-05 11:33:25.000000000 -0400 addmrpt_1_11390_11391.txt

or this using the same format options the date command uses. In this case year-month-day.24 hour:minute

oracle@ > ls -ltr --time-style=+%F.%R
total 2328

-rw-r--r-- 1 oracle oinstall  22413 2013-06-05.10:59 ashrpt_1_0605_0900.txt

-rw-r--r-- 1 oracle oinstall    247 2013-06-05.11:31 afiedt.buf

-rw-r--r-- 1 oracle oinstall 255934 2013-06-05.11:33 awrrpt_1_11390_11391.txt

-rw-r--r-- 1 oracle oinstall  14594 2013-06-05.11:33 addmrpt_1_11390_11391.txt

Got to love it.

11gR2 Clusterware and Grid Home - What You Need to Know

1050908.1 - troubleshoot grid startup issue
http://oracledbazone.wordpress.com/2012/07/17/useful-11gr2-rac-commands/
11gR2 Clusterware and Grid Home - What You Need to Know (Doc ID 1053147.1)


-- check  cluster name
   export GRID_HOME=/ora01/grid/11.2.0.2/grid
   export DB_HOME=/ora01/oracle/product/11.2.0.2/db
 
   $GRID_HOME/bin/cemutlo -n
 
--  Check Clusterware Resources status
   ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
   ps -ef|grep d.bin
   ps -ef | egrep 'init|d.bin'
 
   cluvfy comp -list
 
   crsctl check crs
   $GRID_HOME/bin/crsctl stat res -t                 #all
   $GRID_HOME/bin/crsctl stat res -t -init           #only initialize
   $GRID_HOME/bin/crsctl stat res -p                 #long format
   $GRID_HOME/bin/crsctl query css votedisk
   $GRID_HOME/bin/ocrcheck
   $GRID_HOME/bin/oifcfg getif
   $GRID_HOME/bin/crsctl check crs
   crsctl check has
   crsctl query crs activeversion
   crsctl check cluster -all
   crs_stat -t -v


--  To find out config  information
     srvctl config network
     srvctl config nodeapps
     srvctl config asm
     srvctl config listener
     srvctl config scan
     srvctl config scan_listener
     $GRID_HOME/bin/srvctl config nodeapps -a
     $GRID_HOME/bin/srvctl config scan
     $GRID_HOME/bin/srvctl config network
     $GRID_HOME/bin/srvctl config asm -a
     $GRID_HOME/bin/srvctl config listener -l <listener-name> -a
     $DB_HOME/bin/srvctl config database -d <dbname> -a
     $DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v

--  To find out non-grid status
     srvctl status nodeapps
     srvctl status asm
     srvctl status listener
     srvctl status scan
     srvctl status scan_listener
crs_stat -t -v
 
-- to start all resource (CRS, ASM , Listener and Databases)
   cd /ora01/grid/11.2.0.2/grid/bin
   ./crsctl start resource -all
   ./crsctl stop has ( will stop on node1 and move vip to node2)
   ./crs_stop -all ( will stop on both nodes)
   ./crsctl stop cluster -n node_name ( will stop asm, crs)
   ./crsctl stop crs ( will stop asm, crs)
 
   srvctl start cvu
   srvctl start oc4j
   srvctl status cvu
   alternate command
   crsctl start resource ora.cvu
   crsctl status resource ora.cvu



 -- start / stop non-rac
   # appsdbaworkshop.blogspot.com

    crsctl 11.2.0.2 to start/stop/check on standalone node

    crsctl config has
    crsctl stop has
    crsctl start has
    crsctl status resource
    crs_stop -all
    crs_start -all
 
   -- start resource manually
   crsctl start resource ora.cvu -n <nodename>
   crsctl start res ora.crsd -init
 
-- check network

  1. Find out Subnet ID from oifcfg from grid

    cd /ora01/grid/11.2.0.2/grid/bin
   
    oifcfg iflist -p -n
    1st col - network adapter name
    2nd col - subnet id
    3rd col - public/private
    4th col - netmask

  2. from root
     # ifconfig -a

  3. Subnet Info in Oracle Clusterware - OCR
     cd /ora01/grid/11.2.0.2/grid/bin
     oifcfg getif

-- check cluster nodes
   olsnodes -n
   olsnodes -c
   olsnodes -i  -s -n

-- OCR
   ocrcheck

-- Check voting disk
   crsctl query css votedisk

- check VIP
   srvctl status nodeapps
   srvctl start nodeapps
   crsctl stat res -t

-- check SCAN
   cluvfy comp scan -verbose
   srvctl config scan
   srvctl status scan

 
   srvctl config scan_listener
   srvctl status scan_listener
   lsnrctl status LISTENER_SCAN1
   lsnrctl service LISTENER_SCAN
   show parameter remote_listener
 
   crsctl stat res -w "TYPE = ora.scan_listener.type"
 
-- check listener
   srvctl status listener

-- check HAIP
   crsctl stat res -init -w "TYPE = ora.haip.type"
   oifcfg iflist -p -n
     will show   "bond1  169.254.0.0  UNKNOWN  255.255.0.0" for HAIP
 
   col host_name format a10
   sqlplus
     SELECT a.host_name, a.instance_name, b.name, b.ip_address
     FROM gv$cluster_interconnects b, gv$instance a
     WHERE a.inst_id=b.inst_id
     ORDER BY 1,2,3;
 
-- collect config
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl stat res -p
$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/oifcfg getif
$GRID_HOME/bin/srvctl config nodeapps -a
$GRID_HOME/bin/srvctl config scan
$GRID_HOME/bin/srvctl config asm -a
$GRID_HOME/bin/srvctl config listener -l <listener-name> -a
$DB_HOME/bin/srvctl config database -d <dbname> -a
$DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v

 
-- steps to  stop ( asm, instance, nodeapps, in 11gr2)
   $ srvctl stop instance -d RACDB -n racnode1
   $ srvctl stop vip -n racnode1 -f

--  check if the CRS/OHAS & services are enabled to autostart
   # $GRID_HOME/bin/crsctl config crs
   if not then enable
   # $GRID_HOME/bin/crsctl enable crs

--  shutdown crs services
   # $GRID_HOME/bin/crsctl stop crs
 
-- verify
   $GRID_HOME/bin/crsctl status resource -t

-- Change IP Interconnect address

-- Change VIP


-- get database name
   $ORACLE_HOME/bin/srvctl status home -o $ORACLE_HOME -s /tmp/oracle_home.stat

-- start databases using new command
   $ORACLE_HOME/bin/srvctl start home -o $ORACLE_HOME -s /tmp/oracle_home.stat

-- stop databases
   $ORACLE_HOME/bin/srvctl stop home -o $ORACLE_HOME -s /tmp/oracle_home.stat


  # http://surachartopun.com/2009/10/how-to-create-oracle-service-on-11gr2.html

  lnx20012@mzmd:/ora01/oracle $ cat /opt/oracle/admin/scripts/gridstat
     #!/usr/bin/ksh
     #
     # Check 11G RAC database Up/Down Status script
     #
     # Description:
     #    - Returns formatted version of crs_stat -t, in tabular
     #      format, with the complete rsc names and filtering keywords
     #   - The argument, $RSC_KEY, is optional and if passed to the script, will
     #     limit the output to HA resources whose names match $RSC_KEY.
     # Requirements:
     #   - $ORA_CRS_HOME should be set in your environment
     # Modifications
     #  RLM 5/2010 - use oraenv to set the environment, grid must be in oratab
     #             - check if search argument is null and substitute .* to create a valid syntax for Solaris
     ORACLE_SID=grid;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
     # If search argument is null, substitute .*, meaning match anything
     if [[ -z $1 ]]; then
       RSC_KEY='.*'
     else
       RSC_KEY=$1
     fi
     #QSTAT=-u
     QSTAT=-l
     AWK=/bin/awk
     $ORACLE_HOME/bin/crs_stat $QSTAT | $AWK \
       'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
               printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";
               FS="="; state = 0 }
       $1~/NAME/ && $2~/'$RSC_KEY'/ { appname=$2; state=1}
       state == 0 {next}
       $1~/TARGET/ && state == 1 {apptarget = $2; state=2}
       $1~/STATE/ && state == 2 {appstate = $2; state=3}
       state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0}'

Check the status of the cluster

Check the status of the cluster

[oracle@rac1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@rac1 ~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@rac1 ~]$ crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Check the status of the services on the cluster

[oracle@rac1 ~]$ crsctl status resource -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE rac1
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ora.asm
ONLINE ONLINE rac1
ora.gsd
OFFLINE OFFLINE rac1
ora.net1.network
ONLINE ONLINE rac1
ora.ons
ONLINE ONLINE rac1
ora.registry.acfs
ONLINE ONLINE rac1
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 OFFLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE rac1
ora.testrac.db
1 OFFLINE OFFLINE Instance Shutdown

Stop crs on all nodes (only one node up in this example)

[root@rac1 bin]# ./crsctl stop cluster -all
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.registry.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.rac1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.rac1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cvu’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.registry.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘rac1'
CRS-2677: Stop of ‘ora.ons’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘rac1'
CRS-2677: Stop of ‘ora.net1.network’ on ‘rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘rac1' has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘rac1'
CRS-2677: Stop of ‘ora.evmd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘rac1'
CRS-2677: Stop of ‘ora.cssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘rac1'
CRS-2677: Stop of ‘ora.diskmon’ on ‘rac1' succeeded

Stop crs on specifc node

[root@rac1 bin]# ./crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.registry.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.rac1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.rac1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cvu’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.registry.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘rac1'
CRS-2677: Stop of ‘ora.ons’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘rac1'
CRS-2677: Stop of ‘ora.net1.network’ on ‘rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘rac1' has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.crf’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘rac1'
CRS-2677: Stop of ‘ora.evmd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘rac1'
CRS-2677: Stop of ‘ora.cssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘rac1'
CRS-2677: Stop of ‘ora.gipcd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rac1'
CRS-2677: Stop of ‘ora.diskmon’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Disable CRS from starting on reboot

[root@rac1 bin]# ./crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

Enable CRS to start on reboot

[root@rac1 bin]# ./crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.

Start crs on specifc node

[root@rac1 bin]# ./crsctl start has
CRS-4123: Oracle High Availability Services has been started.

Check database status

[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop an instance

[oracle@rac1 ~]$ srvctl stop instance -i RACDB1 -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is running on node rac2

Start an instance

[oracle@rac1 ~]$ srvctl start instance -i RACDB1 -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop the database

[oracle@rac1 ~]$ srvctl stop database -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is not running on node rac2

Start the database

[oracle@rac1 ~]$ srvctl start database -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop the listener on a Node

[oracle@rac1 ~]$ srvctl status listener -n rac1
Listener LISTENER is enabled on node(s): rac1
Listener LISTENER is not running on node(s): rac1

Start the listener on a Node

[oracle@rac1 ~]$ srvctl status listener -n rac1
Listener LISTENER is enabled on node(s): rac1
Listener LISTENER is running on node(s): rac1



Check the voting disk

[oracle@rac1 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
– —– —————– ——— ———
1. ONLINE 91b18024f9674f83bf5117d3c0996a05 (ORCL:VOL1) [DATA]
Located 1 voting disk(s).

Query the network addresses

[oracle@rac1 ~]$ oifcfg getif
eth0 192.168.245.0 global public
eth1 192.168.126.0 global cluster_interconnect

Show OCR backup – the voting file (disk) is also backed up with the OCR backup

[root@rac1 bin]# ./ocrconfig -showbackup

rac1 2012/07/17 07:47:17 /u01/app/11.2.0/grid/cdata/rac-cluster/backup00.ocr

rac1 2012/07/17 03:47:15 /u01/app/11.2.0/grid/cdata/rac-cluster/backup01.ocr

rac1 2012/07/16 23:47:13 /u01/app/11.2.0/grid/cdata/rac-cluster/backup02.ocr

rac1 2012/07/16 19:47:11 /u01/app/11.2.0/grid/cdata/rac-cluster/day.ocr

rac1 2012/07/16 19:47:11 /u01/app/11.2.0/grid/cdata/rac-cluster/week.ocr

rac1 2012/03/23 11:30:19 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120323_113019.ocr

Take a manual backup of the OCR

[root@rac1 bin]# ./ocrconfig -manualbackup

rac1 2012/07/17 10:08:28 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120717_100828.ocr

rac1 2012/07/17 10:07:39 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120717_100739.ocr

rac1 2012/03/23 11:30:19 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120323_113019.ocr

ASM Useful commands

-- LUN size

SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
    , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- will show provisioned dasd too


col DISK_FILE_PATH format a20    
SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- check provisioned dasd using asmcmd

asmcmd >  lsdsk --candidate -p

   
-- ToTal Utlizatization size

 SELECT
      name                                     group_name
    , type                                     type
    , total_mb                                 total_mb
    , (total_mb - free_mb)                     used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
  FROM
      v$asm_diskgroup
  ORDER BY
     name;

-- find flash area usage

select database_name "Database",
         sum(space)/1024/1024 "Size in MB" FROM (
  SELECT
      CONNECT_BY_ROOT db_name as database_name, space
  FROM
      ( SELECT
            a.parent_index       pindex
          , a.name               db_name
          , a.reference_index    rindex
          , f.bytes              bytes
          , f.space              space
          , f.type               type
        FROM
            v$asm_file f RIGHT OUTER JOIN v$asm_alias a
                         USING (group_number, file_number) where GROUP_NUMBER=2
      )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24))) = 0
      CONNECT BY PRIOR rindex = pindex)
group by rollup(database_name)
order by database_name
/

DBMS_SCHEDULER create schedule create job

- create schedule

 -- Creating a common schedule which could be used to schedule a JOB to run Daily at 1:00 AM
     -- US/Eastern Time.
DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'DLY0100_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=daily; byhour=1;'                    ,
                                    comments        => 'Runtime - Everyday (Mon-Sun) at 01:00 AM US/Eastern Time'
                                   );


 --
     -- Create a job with an inline program and a defined schedule to
     -- do daily purge for g  tables.
     --
     DBMS_SCHEDULER.create_job(
                               job_name      => 'DLY0100_DAILY_PURGE_GRP1'         ,
                               schedule_name => 'DLY0100_COMMON_SCHED'             ,
                               job_type      => 'PLSQL_BLOCK'                      ,
                               job_action    => 'BEGIN daily_purge(null,1); END;'  ,
                               enabled       => TRUE                               ,
                               auto_drop     => FALSE                              ,
                               comments      => 'Daily job to purge group 1 tables.'
                              );

 -- Creating a common schedule which could be used to schedule a JOB to run Weekly at 3:00 AM
     -- US/Central Time.
     --
     DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'WKLY0300_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=weekly; byday=sun; byhour=2;'                    ,
                                    comments        => 'Runtime - Every Sun at 3:00AM '
                                   );

-- Create a job with  proecdure
   DBMS_SCHEDULER.create_job(
                               job_name      => 'WKLY0300_SP_COLLECT_MIS_STATS'       ,
                               schedule_name => 'WKLY0300_COMMON_SCHED'              ,
                               job_type      => 'STORED_PROCEDURE'                       ,
                               job_action    => 'sp_collect_mis_stats',
                               enabled       => TRUE                                ,
                               auto_drop     => FALSE                               ,
                               comments      => 'Weekly job to collect mis stats'
                              );

Scheduler disable/enable

exec dbms_scheduler.drop_job('SCOTT.TESTJOB');
exec dbms_scheduler.disable('TESTJOB');
exec dbms_scheduler.enable('"TESTJOB"');
exec dbms_scheduler.stop_job('TESTJOB');

Scheduler Jobs report

SPOOL verify_scheduler_changes.log
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
  FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify:    &TEST'
PROMPT 'User:      &_USER'
PROMPT 'Database:  &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT  '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether the scheduler jobs have been created successfully.'
PROMPT '   b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT '   c) Check the state column, it should be 'SCHEDULED''
PROMPT '   d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT '   e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT '   f) Check the run_count column to see how many times the job ran.'

--
  SELECT  job_name
         ,enabled
         ,state
         ,run_count
         ,failure_count
         ,rpad(last_start_date,39) AS "LAST_START_DATE"
         ,rpad(next_run_date,39)   AS "NEXT_RUN_DATE"
    FROM  user_scheduler_jobs
ORDER BY  job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether each job ran at the requested scheduled time.'
PROMPT '   b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
         ,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
         ,rpad(run_duration,25) AS "JOB_RUN_DURATION"
    FROM  user_scheduler_job_run_details
ORDER BY  log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many jobs are in failed or broken status.'
PROMPT '   b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,additional_info
    FROM  user_scheduler_job_run_details
   WHERE  status != 'SUCCEEDED'
ORDER BY  log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the schedules have been created.'
PROMPT '   b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped

  SELECT  schedule_name
         ,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
         ,repeat_interval
         ,comments
    FROM  user_scheduler_schedules
ORDER BY  schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the programs have been created.'
PROMPT '   b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT '   c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT '   b) All the programs should be in Enables state'
PROMPT ' '
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
   WHERE  enabled != 'TRUE'
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT '   b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT '      tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT '   c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT '      This script is not part of the rebuild script as it had to be run only once.'
PROMPT '   d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT  job
       ,what
  FROM  user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
  FROM DUAL;
--
PROMPT '*****  END Of  Report *****'
--
SPOOL OFF;
QUIT

Blocking Issue

// *******************************************************************************--  blocking issue : enq: TX - row lock contention
// *******************************************************************************
step 1
  // get blocking session and blocked sessions

  set lines 180
  col USERNAME format a10
  col event    format a15
  col WAIT_CLASS format a15
  Select  blocking_session, sid, serial#, username, event, wait_class, seconds_in_wait siw
    from v$session  where blocking_session is not NULL  order by blocking_session;    

step 2
 //  detail locking information :  sid, serial and object name    
 
   set linesize 150;
    set head on;
    col sid_serial form a13
    col ora_user for a15;
    col object_name for a35;
    col object_type for a10;
    col lock_mode for a15;
    col last_ddl for a8;
    col status for a10;
   
    break on sid_serial;
   
   SELECT l.session_id||','||v.serial# sid_serial,
          l.ORACLE_USERNAME ora_user,
          o.object_name,
          o.object_type,
          DECODE(l.locked_mode, 
                  0, 'None', 
                  1, 'Null', 
                  2, 'Row-S (SS)', 
                  3, 'Row-X (SX)',
                  4, 'Share',
                  5, 'S/Row-X (SSX)',
                  6, 'Exclusive',
                 TO_CHAR(l.locked_mode)
              ) lock_mode,
             o.status,
             to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
   FROM dba_objects o, gv$locked_object l, v$session v
   WHERE o.object_id = l.object_id
     and l.SESSION_ID=v.sid
   order by 2,3;



step 3
//  which sid and stmt is waiting for lock

with blocking_tab as ( 
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
),
blocking_sess as (
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.sid=b.sid
  ),
blocked_sess as (    
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.blocking_session=b.sid    ),
wait_sess  as  (
  select sid, serial#, sql_id, prev_sql_id from blocking_sess  union  select sid, serial#, sql_id,   prev_SQL_id from blocked_sess  )
select s.sid, s.serial#, sql_text from v$sql q, wait_sess s where 
q.sql_id = s.sql_id or
    q.sql_id = s.prev_sql_id
/ 
 
step 4
// kill blocking session

with temptbl1 as (
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
)
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session natural  join  temptbl1 ;


// *******************************************************************************