Expdp parameters Include and Query

1) Expdp  with Include.

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"

or

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log
include=table:"in(select table_name from user_tables where table_name like 'ITEM_'||to_char(sysdate-1,'mmddyy')||'%')"

Export: Release 11.2.0.1.0 - Production on Tue Dec 31 05:42:19 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "RESEARCH"."SYS_EXPORT_SCHEMA_01":  research/******** directory=dp_dir dumpfile=tabs_research_31dec13.dmp log tabs_research_31dec13.log include=TABLE:"LIKE '%2013%'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30.57 GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

2)  Expdp with Include and query

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"  query="where bus_date between to_date('01-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS') and to_date('31-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS')"

Enabling Explain Plan at Session level

SQL>  set autotrace traceonly explain;
SQL>
SQL> select count(0) from tab;

Execution Plan
----------------------------------------------------------
Plan hash value: 1728992512

-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     1 |    44 |   106   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE         |         |     1 |    44 |            |          |
|*  2 |   FILTER                |         |       |       |            |          |
|   3 |    NESTED LOOPS OUTER   |         |  1270 | 55880 |   106   (1)| 00:00:02 |
|*  4 |     HASH JOIN           |         |  1270 | 49530 |    31   (4)| 00:00:01 |
|   5 |      INDEX FULL SCAN    | I_USER2 |    93 |  2046 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN   | I_OBJ5  |  1270 | 21590 |    29   (0)| 00:00:01 |
|   7 |     TABLE ACCESS CLUSTER| TAB$    |     1 |     5 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN  | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |
|   9 |    NESTED LOOPS         |         |     1 |    29 |     2   (0)| 00:00:01 |
|* 10 |     INDEX SKIP SCAN     | I_USER2 |     1 |    20 |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN    | I_OBJ4  |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11
              AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5) AND
              ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE
              ' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2"
              WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
              "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."OWNER#"="U"."USER#")
   6 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."TYPE#">=2 AND
              "O"."LINKNAME" IS NULL AND "O"."TYPE#"<=5)
       filter("O"."TYPE#"<=5 AND "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
   8 - access("O"."OBJ#"="T"."OBJ#"(+))
  10 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
  11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
              "O2"."OWNER#"="U2"."USER#")

SQL>

Shell script to send alert for database status check

#! /bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export HOST_NAME=`hostname`
export MAIL_FILE=/home/oracle/dba_scripts/dbstatcheck.log
export MAIL_ID_LIST="database-team-vsoft@vsoftcorp.com"
export ALERT_LOG=/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
dbchk=`sqlplus -s / as sysdba <set feed off lines 1000 pages 200 tab off space 1 head off FLUSH OFF TRIMSPOOL ON echo off verify off termout off
select status from v\\$instance;
exit
EOF`
dbchk1=`echo $dbchk|sed s/^$//`
echo $dbchk1
if [ "$dbchk1" != "OPEN" ]
then
echo "database is not open"
echo "Sending recent errors in alert log : ${ALERT_LOG} " > $MAIL_FILE
echo "############################################################" >> $MAIL_FILE
grep -A 3 -B 3 "ORA-" ${ALERT_LOG}|tail -n 20 >> $MAIL_FILE

cat $MAIL_FILE|mail -s "### Severity High : DATABASE IS DOWN : ${HOST_NAME}:${ORACLE_SID} ###" "${MAIL_ID_LIST}"
fi

Script to configure User equivalence across the RAC nodes using SSH

echo "Enter Node1 name :"
read node1
echo "Enter No Of Nodes in RAC[1/2]:"
read noofnodes
echo "Make sure that /etc/hosts file contains all nodes ip addresess and hostnames "
parm="($node1)"
mkdir -p ~/.ssh
chmod 755 ~/.ssh
cd ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa
cat id_rsa.pub id_dsa.pub >> $node1
cat id_rsa.pub id_dsa.pub >> authorized_keys

if [ $noofnodes -eq 2 ]
then
echo "Enter Node2 name :"
read node2

scp $node1 $node2:~/.ssh/.
ssh $node2 "mkdir -p ~/.ssh;chmod 755 ~/.ssh;cd ~/.ssh;/usr/bin/ssh-keygen -t rsa;/usr/bin/ssh-keygen -t dsa;cat id_rsa.pub id_dsa.pub >> $node2;cat $node1 $node2 >> authorized_keys;chmod 644 authorized_keys"
scp $node2:~/.ssh/authorized_keys  ~/.ssh/.
echo "Execute Following Commands in Node $node2 as user `logname`"
echo "cd ~/.ssh"
echo "exec /usr/bin/ssh-agent \$SHELL"
echo "/usr/bin/ssh-add"
echo "Press Enter after executing above commands in node $node2 as user `logname`"
read aa
parm="($node1)|($node2)"
fi

cd ~/.ssh
chmod 644 authorized_keys
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

for lv in `cat /etc/hosts|grep -v "^#"|grep -i -E "$parm"`
do
echo $lv
ssh $lv date
ssh $lv date
done

Configuring DNS server on linux for 11gr2 SCAN name

1) Disable firewall
service iptables stop
service ip6tables stop

2) Install following rpms
bind-libs
bind
bind-utils

2) edit /etc/named.conf file (Make sure that dns name server name and scan name is different and scan ips are not being already used)

[root@linuxdns named]# cat /etc/named.conf
//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

options {
        listen-on port 53 { 127.0.0.1; 10.10.20.6; };
        listen-on-v6 port 53 { ::1; };
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        #allow-query     { localhost; };
        allow-query     { any; };
        query-source port 53;
        query-source-v6 port 53;
        recursion yes;

        dnssec-enable yes;
        dnssec-validation yes;
        dnssec-lookaside auto;

        /* Path to ISC DLV key */
        bindkeys-file "/etc/named.iscdlv.key";
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "." IN {
        type hint;
        file "named.ca";
};

zone "kmccorp.com" IN {
        type master;
        file "kmccorp.com.zone";
        allow-update { none; };
};

zone "20.10.10.in-addr.arpa." IN {
        type master;
        file "20.10.10.in-addr.arpa";
        allow-update { none; };
};

include "/etc/named.rfc1912.zones";

[root@linuxdns named]#

3)
In the zone definitions we defined the file containing the zone configuration. These files are located in the "/var/named/" directory.
For a RAC installation create/edit the file associated with your zone its look like bellow (/var/named/kmccorp.zone )

[root@linuxdns named]# pwd
/var/named
[root@linuxdns named]# cat kmccorp.com.zone
$TTL    86400
@               IN SOA  localhost root.localhost (
                                        42              ; serial (d. adams)
                                        3H              ; refresh
                                        15M             ; retry
                                        1W              ; expiry
                                        1D )            ; minimum
                IN NS           localhost
localhost       IN A            127.0.0.1
linuxrac        IN A    10.10.20.1
linuxrac-priv   IN A    192.168.70.1
linuxrac-vip    IN A    10.10.20.3
linuxdns-scan   IN A    10.10.20.10
linuxdns-scan   IN A    10.10.20.11
linuxdns-scan   IN A    10.10.20.12
[root@linuxdns named]#

4) Next I need to create the "/var/named/20.10.10.in-addr.arpa" file for my public network reverse lookups.
This file has the following contents, where "linuxdns.kmccorp.com" is the name of the DNS server

[root@linuxdns named]# cat 20.10.10.in-addr.arpa
$ORIGIN 20.10.10.in-addr.arpa.
$TTL 1H
@       IN      SOA     linuxdns.kmccorp.com.     root.linuxdns.kmccorp.com. (      2
                                                3H
                                                1H
                                                1W
                                                1H )
20.10.10.in-addr.arpa.         IN NS      linuxdns.kmccorp.com.

1     IN PTR  linuxrac.kmccorp.com
3     IN PTR  linuxrac-vip.kmccorp.com
10    IN PTR  linuxdns-scan.kmccorp.com
11    IN PTR  linuxdns-scan.kmccorp.com
12    IN PTR  linuxdns-scan.kmccorp.com

[root@linuxdns named]#
[root@linuxdns named]#
[root@linuxdns named]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.10.20.6 linuxdns.kmccorp.com linuxdns
[root@linuxdns named]#

5) Start the dns service
[root@linuxdns named]#service named start
checkconfig named on

6) On the client side include following content in file /etc/resolv.conf

nameserver 10.10.20.6
search kmccorp.com

7) Test it

# nslookup linuxdns-scan.kmccorp.com
Server:         10.10.20.6
Address:        10.10.20.6#53

Name:   linuxdns-scan.kmccorp.com
Address: 10.10.20.10
Name:   linuxdns-scan.kmccorp.com
Address: 10.10.20.11
Name:   linuxdns-scan.kmccorp.com
Address: 10.10.20.12

scsi_id /dev/sda is not giving any output for linux 6 on vmware

1) If you were running ESXi 4.1, that bit of re-configuration consists of

shut down your virtual machine
right-click the VM’s entry in the left-hand panel and select Edit Settings
click the Options tab
Select the Advanced -> General item on the left and click the Configuration Parameters… button you then see displayed on the right
Click the Add Row button
Add disk.EnableUUID as the name of the new row, and the word TRUE as its value (don’t use quotation marks around either of these entries).
Click OK to make the new parameter addition ‘stick’.
You can then reboot your virtual machine.

2) Incase of  VMware Workstation we have to do it manually  using a text editor. Open vmware configuration file as mentioned bellow

First, find the directory where the files representing your virtual machine are stored. One of them will be called the name of your VM,
with an extension of .vmx. In my case, for example, the file is called OEL6.vmx, because when I created my VM, I called it “OEL6?. The file will be 3 or 4KB in size.
Open it in the text editor of your choice and at the very end of the file, add this line:

disk.EnableUUID = "TRUE"

Query to Find Re-sizable Data files

REM Script is meant for Oracle version 9 and higher
REM -----------------------------------------------

set lines 200
set serveroutput on
exec dbms_output.enable(1000000);
create table dbf_resize(statement varchar2(1000));
declare

cursor c_dbfile is
select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent
,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size
from dba_data_files f,
dba_tablespaces t
where f.tablespace_name = t.tablespace_name
and t.status = 'ONLINE'
order by f.tablespace_name,f.file_id;

cursor c_freespace(v_file_id in number) is
select block_id, block_id+blocks max_block
from dba_free_space
where file_id = v_file_id
order by block_id desc;

/* variables to check settings/values */
dummy number;
checkval varchar2(10);
block_correction number;
STMT_CNT number;

/* running variable to show (possible) end-of-file */
file_min_block number;

/* variables to check if recycle_bin is on and if extent as checked is in ... */
recycle_bin boolean:=false;
extent_in_recycle_bin boolean;

/* exception handler needed for non-existing tables note:344940.1 */
sqlstr varchar2(100);
table_does_not_exist exception;
pragma exception_init(table_does_not_exist,-942);

/* variable to spot space wastage in datafile of uniform tablespace */
space_wastage number;

begin

/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */
begin
select value into checkval from v$parameter where name = 'recyclebin';
if checkval = 'on'
then
recycle_bin := true;
end if;
exception
when no_data_found
then
recycle_bin := false;
end;

/* main loop */
for c_file in c_dbfile
loop
/* initialization of loop variables */
dummy :=0;
extent_in_recycle_bin := false;
file_min_block := c_file.blocks;

begin

delete from dbf_resize;
commit;

space_wastage:=0; /* reset for every file check */



for c_free in c_freespace(c_file.file_id)
loop
/* if blocks is an uneven value there is a need to correct
with -1 to compare with end-of-file which is even */
block_correction := (0-mod(c_free.max_block,2));
if file_min_block = c_free.max_block+block_correction
then

/* free extent is at end so file can be resized */
file_min_block := c_free.block_id;

/* Uniform sized tablespace check if space at end of file
is less then uniform extent size */
elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)
then

/* uniform tablespace which has a wastage of space in datafile
due to fact that space at end of file is smaller than uniform extent size */

space_wastage:=c_file.blocks - c_free.max_block;
file_min_block := c_free.block_id;

else
/* no more free extent at end of file, file cannot be further resized */
exit ;
end if;
end loop;
end;

/* check if file can be resized, minimal size of file 128 {+ initial_extent} blocks */
if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)
then

dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name||' cannot be resized as no free extents found.');

else

/* file needs minimal no of blocks which does vary over versions,
using safe value of 128 {+ initial_extent} */
if file_min_block < c_file.file_min_size
then
file_min_block := c_file.file_min_size;
end if;


dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
dbms_output.put_line('current size: '||(c_file.blocks*c_file.block_size)/1024/1024||'M'||' can be resized to: '||round((file_min_block*c_file.block_size)/1024/1024)||'M (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)*100,2)||' %)');


/* below is only true if recyclebin is on */
if recycle_bin
then
begin
sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id;
execute immediate sqlstr into dummy;

if dummy > 0
then

dbms_output.put_line('Extents found in recyclebin for above file/tablespace');
dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');
dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');
end if;
exception
when no_data_found
then null;
when table_does_not_exist
then null;
end;
end if;
dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024/1024)||'M;');

insert into dbf_resize values ('alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*c_file.block_size)/1024/1024)||'M');
commit;
if space_wastage!=0
then
dbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');
dbms_output.put_line('Size of datafile is not a multiple of NN*uniform_extent_size + overhead');
dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastage*c_file.block_size)/1024)||'K');
dbms_output.put_line('For optimal usage of space in file either resize OR increase to: '||round(((c_file.blocks+(c_file.uni_extent-space_wastage))*c_file.block_size)/1024/1024)||'M');
end if;

dbms_output.put_line('.');

end if;
end loop;

/* select nvl(count(0),0) into stmt_cnt from dbf_resize;
if stmt_cnt >0 then
for rsz_stmt in (select * from dbf_resize) loop
dbms_output.put_line('Executing stmt: '||rsz_stmt.statement);
execute immediate rsz_stmt.statement;
end loop;
end if;
*/
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/

Audit Vault Disabling / Enabling

############################################
AUDIT VAULT DISABLING
############################################

DB server orcl  where the agent has installed
========================================
export ORACLE_HOME=/u01/app/orascp/product/10.3.0/avagent
export PATH=$ORACLE_HOME/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/java/jdk1.6.0_33/bin

STOP The Agent in db server
===========================
 avctl show_agent_status
 avctl show_oc4j_status
./avctl stop_agent
Stopping agent...
Agent stopped successfully.

=====================================
STOP THE COLLECTIORS IN AV server  avserver01
======================================
avctl show_collector_status -collname DBAUD_Collector -srcname orclfin01
avctl show_collector_status -collname OSAUD_Collector -srcname orclhr02
avctl show_collector_status -collname OSAUD_Collector -srcname orcltech03

avctl stop_collector -collname DBAUD_Collector -srcname orclfin01
avctl stop_collector -collname OSAUD_Collector -srcname orclhr02
avctl stop_collector -collname OSAUD_Collector -srcname orcltech03

############################################
AUDIT VAULT  ENABLING
############################################

DB server orcl where the agent has installed
========================================
export ORACLE_HOME=/u01/app/orascp/product/10.3.0/avagent
export PATH=$ORACLE_HOME/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/java/jdk1.6.0_33/bin

START The Agent in db server
===========================
 avctl show_agent_status
 avctl show_oc4j_status
./avctl start_agent
Starting agent...
Agent started successfully.

avctl show_agent_status

=====================================
START THE COLLECTIORS IN AV server  (ct11bzappdb007)
======================================
avctl show_collector_status -collname DBAUD_Collector -srcname orclfin01
avctl show_collector_status -collname OSAUD_Collector -srcname orclhr02
avctl show_collector_status -collname OSAUD_Collector -srcname orcltech03

avctl start_collector -collname DBAUD_Collector -srcname orclfin01
avctl start_collector -collname OSAUD_Collector -srcname orclhr02
avctl start_collector -collname OSAUD_Collector -srcname orcltech03

Database Vault Disabling / Enabling

#####################################
DB VAULT Disabling
#####################################
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_off ioracle
Disable Oracle label Security
$ cd $ORACLE_HOME/rdbms/lib
$ chopt disable lbac

-----------IN BOTH NODES  ---------------checking

ar -tv $ORACLE_HOME/rdbms/lib/libknlopt.a

Oracle Label Security ON= kzlilbac.o
Oracle Label Security OFF= kzlnlbac.o

Oracle Database Vault ON= kzvidv.o
Oracle Database Vault OFF= kzvndv.o

SELECT * FROM gV$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

#####################################

DB VAULT Enabling

#####################################

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk dv_on ioracle

enable Oracle label Security

$ cd $ORACLE_HOME/rdbms/lib

$ chopt enable lbac 

-----------IN BOTH NODES --------------- checking

ar -tv $ORACLE_HOME/rdbms/lib/libknlopt.a

Script to Perform Pre validations for before upgrade Oracle RAC from 11.2.0 to later

#! /bin/ksh


export TOD_DATE=`date +%Y%m%d`

echo "Specify ORACLE_HOME path : "
read ORACLE_HOME
echo "specify GRID_HOME path :"
read GRID_HOME
echo "Specify ORACLE_BASE path :"
read ORACLE_BASE
echo "Specify DBNAME : "
read DBNAME
echo "Specify SID name :"
read SID
echo "Specify ASM SID name :"
read ASM
echo "Specify backup location for prechecks:"
read PRECHKS

export ORACLE_HOME=$ORACLE_HOME
export GRID_HOME=$GRID_HOME
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=$SID



echo "###################### sqlplus begin #####################"
sqlplus -s / as sysdba  << EOF
set lines 1000
set pages 500
set serverout on
prompt
prompt ################################################################
prompt

spool '$PRECHKS/spool_ver_iv_reg.log'

prompt SQL> SELECT * FROM V\$VERSION;
SELECT * FROM V\$VERSION;
prompt
col COMP_NAME for a50
col VERSION for a30
col STATUS for a30
prompt SQL>select COMP_NAME,VERSION,STATUS from dba_registry

select COMP_NAME,VERSION,STATUS from dba_registry;

prompt SQL>select status,count(*) from dba_objects group by status

select status,count(*) from dba_objects group by status;

prompt SQL>select owner,OBJECT_NAME,CREATED,OBJECT_TYPE,LAST_DDL_TIME,status from dba_invalid_objects

col object_name for a40

select owner,OBJECT_NAME,CREATED,OBJECT_TYPE,LAST_DDL_TIME,status from dba_invalid_objects;

prompt SQL>select owner,count(*) from dba_objects where status='INVALID' group by owner;

select owner,count(*) from dba_objects where status='INVALID' group by owner;

prompt SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID' order by object_type;

select owner,object_name,object_type,status from dba_objects where status='INVALID' order by object_type;

prompt SQL>select sum(bytes)/1024/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024/1024 from dba_data_files;

prompt SQL>select sum(bytes)/1024/1024/1024 from dba_segments;

select sum(bytes)/1024/1024/1024 from dba_segments;

prompt SQL>Show parameter spfile

Show parameter spfile

prompt SQL>show parameter cluster_database

show parameter cluster_database

prompt SQL>show parameter listener

show parameter listener

prompt SQL> show parameter background

show parameter background

prompt SQL>show parameter service_names

show parameter service_names

prompt SQL>CREATE TABLE dbaobj_bkp_bef_upgrd_$TOD_DATE AS (select * from dba_objects);

CREATE TABLE dbaobj_bkp_bef_upgrd_$TOD_DATE AS (select * from dba_objects);

prompt SQL>SELECT * FROM V\$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

SELECT * FROM V\$OPTION WHERE PARAMETER in ('Oracle Database Vault','Oracle Label Security');

spool off

spool '$PRECHKS/spool_dblnks.log'

prompt SQL>select  count(0) from dba_db_links;

select count(0) from dba_db_links;

prompt generating dynamic sql for checking status of db links

select 'select count(0) from user_tables@'||db_link from dba_db_links;

prompt ###Create db link statement ####

select d.owner, 'create database link "'||d.db_link||'" connect to "'||d.username||'" identified by "'||l.password||'" using '|| '''' ||d.host|| '''' ||';'
from dba_db_links d, sys.link$ l
where d.db_link = l.name
and   d.host = l.host
and   d.username = l.userid
and   d.created = l.ctime
and  d.owner <> 'PUBLIC'
order by d.owner, d.db_link, d.host;

spool off

prompt ################ Creating pfile from spfile in $PRECHKS/pfile_init$TOD_DATE.ora #####

create pfile='$PRECHKS/pfile_init$TOD_DATE.ora' from spfile;

spool '$PRECHKS/spool_show_dbparms.log'
set lines 1000
show parameters

exit
EOF

echo "#############################sqlplus end########"


echo "############################ crc check start #####" >> $PRECHKS/spool_crschk.log

echo "### crsctl query crs softwareversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs softwareversion >> $PRECHKS/spool_crschk.log

echo "##### crsctl query crs releaseversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs releaseversion >> $PRECHKS/spool_crschk.log

echo "##### crsctl query crs activeversion#####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crsctl query crs activeversion >> $PRECHKS/spool_crschk.log

echo "###### crs_stat -t #######" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/crs_stat -t >> $PRECHKS/spool_crschk.log

echo "#### crs_stat -t | grep -i OFFLINE ###" >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crs_stat -t | grep -i OFFLINE >> $PRECHKS/spool_crschk.log

echo "#### crs_stat -t | grep -i ONLINE ###"  >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crs_stat -t | grep -i ONLINE >> $PRECHKS/spool_crschk.log

echo "##### crsctl status resource -t ####" >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crsctl status resource -t >> $PRECHKS/spool_crschk.log

echo "##### crsctl stat res -t ####" >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crsctl stat res -t >> $PRECHKS/spool_crschk.log

echo " #### crsctl stat res -p  ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crsctl stat res -p >> $PRECHKS/spool_crschk.log

echo " #### crsctl get css diagwait ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/crsctl get css diagwait  >> $PRECHKS/spool_crschk.log

echo " #### oifcfg iflist -p -n ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/oifcfg iflist -p -n >> $PRECHKS/spool_crschk.log

echo " ####  oifcfg getif ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/oifcfg getif >> $PRECHKS/spool_crschk.log

echo " ####  ocrcheck ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/ocrcheck >> $PRECHKS/spool_crschk.log

echo " #### olsnodes ###### " >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/olsnodes  >> $PRECHKS/spool_crschk.log


echo "##### srvctl config database -d $DBNAME ###" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config database -d $DBNAME >> $PRECHKS/spool_crschk.log

echo "##### srvctl config scan #####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config scan >> $PRECHKS/spool_crschk.log

echo "#### srvctl config  listener ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config  listener >> $PRECHKS/spool_crschk.log

echo "#### srvctl config scan_listener ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config scan_listener >> $PRECHKS/spool_crschk.log

echo "#### srvctl config service -d  $DBNAME ####" >> $PRECHKS/spool_crschk.log
$GRID_HOME/bin/srvctl config service -d  $DBNAME  >> $PRECHKS/spool_crschk.log

echo "#### srvctl config nodeapps ####" >> $PRECHKS/spool_crschk.log

$GRID_HOME/bin/srvctl config nodeapps  >> $PRECHKS/spool_crschk.log

echo "############################ crc check end  #####"

echo "###### Checking PSU and CPU patches applied on oracle home ####" >> $PRECHKS/spool_opatch.log

echo "###### opatch lsinventory #######" >> $PRECHKS/spool_opatch.log

$ORACLE_HOME/OPatch/opatch lsinventory >> $PRECHKS/spool_opatch.log

echo "###### opatch lsinventory -bugs_fixed | grep -i psu #######" >> $PRECHKS/spool_opatch.log

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i psu >> $PRECHKS/spool_opatch.log

echo "####### opatch lsinventory -bugs_fixed | grep -i cpu ######" >> $PRECHKS/spool_opatch.log

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i cpu >> $PRECHKS/spool_opatch.log

echo "################### checking for ORA errors in alertlog $ORACLE_BASE/diag/rdbms/$DBNAME/$SID/trace/alert_$SID.log ####"  >> $PRECHKS/spool_ORAerrors.log

tail -n 200000 $ORACLE_BASE/diag/rdbms/$DBNAME/$SID/trace/alert_$SID.log |grep -B 2 -A 3 "ORA-"  >> $PRECHKS/spool_ORAerrors.log

echo "################### checking for ORA errors in alertlog /u01/app/oragrid/diag/asm/+asm/$ASM/trace/alert_$ASM.log  ####"  >> $PRECHKS/spool_ORAerrors.log

tail -n 20000 /u01/app/oragrid/diag/asm/+asm/$ASM/trace/alert_$ASM.log  |grep -B 2 -A 3 "ORA-" >> $PRECHKS/spool_ORAerrors.log

echo "####### df -h /tmp ###### " >> $PRECHKS/spool_os_space.log
df -h /tmp >> $PRECHKS/spool_os_space.log
echo "####### df -h /u01#####" >> $PRECHKS/spool_os_space.log
df -h /u01 >> $PRECHKS/spool_os_space.log
echo "#### free -mto ####" >> $PRECHKS/spool_os_space.log
free -mto >> $PRECHKS/spool_os_space.log
echo "#### grep MemTotal /proc/meminfo ###" >> $PRECHKS/spool_os_space.log
grep MemTotal /proc/meminfo >> $PRECHKS/spool_os_space.log
echo "#### grep SwapTotal /proc/meminfo ####" >> $PRECHKS/spool_os_space.log
grep SwapTotal /proc/meminfo >> $PRECHKS/spool_os_space.log
echo "#### df -h /dev/shm ####" >> $PRECHKS/spool_os_space.log
df -h /dev/shm >> $PRECHKS/spool_os_space.log

echo "#### db processes ###" >> $PRECHKS/spool_os_space.log
ps -ef | egrep -i "crs|tns|emagent|cpmgr|pmon|fndlibr" | egrep -v "root|grep|ssh"  >> $PRECHKS/spool_os_space.log
echo "#### all process ####" >> $PRECHKS/spool_os_space.log
ps -ef >> $PRECHKS/spool_os_space.log
echo "#### mount points #### " >> $PRECHKS/spool_os_space.log
df -h >> $PRECHKS/spool_os_space.log

ps -ef | egrep -i "crs|tns|emagent|cpmgr|pmon|fndlibr" | egrep -v "root|grep|ssh"  > $PRECHKS/dbprocess.`date +%Y%m%d`.$$
ps -ef > $PRECHKS/allprocess.`date +%Y%m%d`.$$
df -h > $PRECHKS/mountpoints.`date +%Y%m%d`.$$
crontab -l > $PRECHKS/crontab.`date +%Y%m%d`.$$
cat /etc/hosts > $PRECHKS/hosts.`date +%Y%m%d`.$$
cat /etc/oratab > $PRECHKS/oratab.`date +%Y%m%d`.$$

echo "### /etc/init.d/oracleasm listdisks -d #### "  >> $PRECHKS/spool_oracleasm.log

/etc/init.d/oracleasm listdisks -d >> $PRECHKS/spool_oracleasm.log

echo "##### kernal parameters ##### " >> $PRECHKS/spool_kernal_limits.log

cat /etc/sysctl.conf |grep 'sem\|shm\|file-max\|ip_local_port_range\|rmem_default\|rmem_max\|wmem_default\|wmem_max\|aio-max-nr' >> $PRECHKS/spool_kernal_limits.log

echo "##### security limits  ##### " >> $PRECHKS/spool_kernal_limits.log

cat /etc/security/limits.conf |grep -v "#" >> $PRECHKS/spool_kernal_limits.log

echo "## scripts running on server `hostname` #### " >> $PRECHKS/spool_lsnrctl.log
echo "##### ps -ef|grep tns ####" >> $PRECHKS/spool_lsnrctl.log

ps -ef|grep tns >> $PRECHKS/spool_lsnrctl.log

for lsnr in `ps -ef|grep tns |grep product|cut -d" " -f23`
do
export TNS_ADMIN=$ORACLE_HOME/network/admin

echo "#### lsnrctl status $lsnr #### " >> $PRECHKS/spool_lsnrctl.log

$ORACLE_HOME/bin/lsnrctl status $lsnr >> $PRECHKS/spool_lsnrctl.log

echo "#### lsnrctl services $lsnr #### " >> $PRECHKS/spool_lsnrctl.log

$ORACLE_HOME/bin/lsnrctl services $lsnr >> $PRECHKS/spool_lsnrctl.log

done

for lsnr2 in `ps -ef|grep tns |grep oragrid|cut -d" " -f22`
do

export TNS_ADMIN=$GRID_HOME/network/admin

echo "#### lsnrctl status $lsnr2 #### " >> $PRECHKS/spool_lsnrctl.log

$GRID_HOME/bin/lsnrctl status $lsnr2 >> $PRECHKS/spool_lsnrctl.log

echo "#### lsnrctl services $lsnr2 #### " >> $PRECHKS/spool_lsnrctl.log

$GRID_HOME/bin/lsnrctl services $lsnr2 >> $PRECHKS/spool_lsnrctl.log

done

Queries For Temporary Space Usage

1)Sort Space Usage 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
and s.sid=2616
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;

2) Monitoring Temporary Space Usage

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;

3) Sort 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 gv$sort_usage T, gv$session S, gv$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;

Sending Mail Alert for Linux Disk space Usage

#!/bin/sh
# set -x
# Shell script to monitor or watch the disk space
# -------------------------------------------------------------------------
# Set admin email so that you can get email.
MAIL_LIST="xxxx@gmail.com"
# set alert level 90% is default
ALERT=80
#
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#

for lv1 in `df -h | grep -vE "^Filesystem|tmpfs|cdrom"|grep G|sed 's/ //g'|sed 's/G/G,/g'|sed 's/%/,/g'|cut -d, -f4-`
do
#echo $lv1
usep=`echo $lv1| cut -d',' -f1`
partition=`echo $lv1|cut -d',' -f2`

if [ $usep -ge $ALERT ] ; then
echo "Running out of space partitoin ${partition} used% (${usep}%) on server $(hostname)"|mail -s "Alert: Almost out of disk space $usep%" $MAIL_LIST

fi
done