Gather Stats for schemas

set serveroutput on;

set echo on;

set timing on;

set time on;

EXECUTE dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_PROC_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_AA_PROC', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MAPS_PG_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXEC DBMS_STATS.gather_dictionary_stats;

--EXEC DBMS_STATS.gather_fixed_objects_stats;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

exit;

Call above sql script through shell script and crontab entry is below the reference.

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dbadba098#@MERUAT2 @/orabackup/scripts/mer_pos_eee_schema_stats.sql >/orabackup/scripts/logs/mer_pos_eee_schema_stats-${fdate}-execution.log

exit;

Health Check SQL Queries

set lines 999 pages 999;

set echo off;

prompt #########################Tablespace Monitoring:#########################

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round( ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_data_files group by tablespace_name) A,

(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space

from dba_free_space a group by tablespace_name) B where b.tablespace_name(+)=a.tablespace_name

union all 

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round(((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,

Sum(Decode(Sign(Maxbytes - Bytes), -1, Bytes, Maxbytes))/(1024*1024) - Sum(Bytes)/(1024*1024) Incrm From Dba_Temp_Files Group By Tablespace_Name) A,

(Select  Tablespace_Name, Count(*) Num2, (Sum(free_space)/(1024*1024)) Free_Space

FROM DBA_TEMP_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE B.TABLESPACE_NAME(+)=A.TABLESPACE_NAME 

Order By "% Full" Desc;

prompt #########################Process Utilization:#########################

set echo on;

SELECT INST_ID,upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM gv$resource_limit WHERE resource_name in ('processes', 'sessions');

set echo off;

prompt #########################Check archive log usage FRA:#########################

col name for a25;

set echo on;

select name,(SPACE_LIMIT/1024/1024/1024), (SPACE_USED/1024/1024/1024), (SPACE_RECLAIMABLE/1024/1024/1024) from v$recovery_file_dest;

set echo off;

prompt #########################Instance Startup :#########################

set echo on;

SELECT inst_id,INSTANCE_NAME,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.gv_$instance;

set echo off;

prompt #########################ASM Disk usage:#########################

set echo on;

SELECT NAME,TOTAL_MB,FREE_MB,ROUND((1- (FREE_MB / TOTAL_MB))*100, 2)  "percentage used" FROM v$asm_diskgroup order by 4 desc;

set echo off;

prompt #########################Instance_name:#########################

set echo on;

select instance_name, host_name from gv$instance order by 1;

set echo off;

prompt #########################Open_mode:##########################

set echo on;

select name, open_mode from gv$database  order by 1;

set echo off;

prompt #########################Information About Each Container in a CDB:##########################

COLUMN NAME FORMAT A8

set echo on;

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

set echo off;

prompt #########################Name and Open Mode of Each PDB:#########################

COLUMN NAME FORMAT A15

COLUMN RESTRICTED FORMAT A10

COLUMN OPEN_TIME FORMAT A40

set echo on;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

set echo off;

prompt #########################Locked Object:#########################

set echo on;

select object_name,os_user_name,session_id from all_objects A ,v$locked_object L where A.object_id=L.object_id group by object_name,os_user_name,session_id order by 1,3;

set echo off;

prompt #########################Block corruption:#########################

set echo on;

select count(*) from v$database_block_corruption;

set echo off;

prompt #########################Table_Stats:#########################

set echo on;

select distinct OWNER,TRUNC(LAST_ANALYZED) from DBA_TABLES where owner like '%AA%' group by owner,last_analyzed order by 1;

exit;

Copy all above queries in sql file and call from shell script for each schema in the database...

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERSIT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERUAT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERUAT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSSIT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSUAT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSUAT_monitor-${fdate}-execution.log

exit;

Script to find temp tablespace free and used space

tempfree.sql

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;

tempspace.sql

-- GE Restricted

select v.sid, v.serial#, v.status, v.username, s.tablespace, s.segtype

from v$session v, v$sort_usage s

where v.saddr=s.session_addr

/

Script Name : temp_top_sql.sql

description : Run below script to monitor the temp tablespace usage and check what running sqltext.

set linesize 400

column osuser format a20

column sid format 9999

column serial# format 99999

column username format a20

column program format a20

column vp.value format 99999999

        select s.osuser, s.sid, s.serial#,s.username, s.program,

               sum(u.blocks)*vp.value/1024/1024 sort_size_MB

        from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

        where  s.saddr = u.session_addr

          and  vp.name = 'db_block_size'

            group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value;

SELECT s.sid, s.status, sq.address,  sq.sql_id, sq.sql_text

FROM v$session s, v$sql sq

WHERE s.status = 'ACTIVE'

and s.sql_hash_value = sq.hash_value

and s.sql_address = sq.address

and s.sid IN (

select s.sid

     from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

where  s.saddr = u.session_addr

            and  vp.name = 'db_block_size'

         group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value);

Script to check all tablespace details in the database

set linesize 300

set pagesize 300

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

 where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

order by pctused

/

Script to find top10active jobs in database.

 /* top10active.sql
    shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET     format 99,999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132 pages 20
set verify off
accept trgtuser char default ALL prompt 'Limit to what userid <ALL> : '
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
  s.sid, s.status,
  floor(last_call_et/60) "LastCallET",
 s.username, s.osuser, 
 p.spid, s.module || ' - ' || s.program uprogram, 
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
  and s.type = 'USER'
  and s.username is not null
  and s.status = 'ACTIVE'
  and (s.username = upper('&trgtuser') or upper('&trgtuser') = 'ALL')
order by 4 desc)
where rownum < 31;

Script to check tablespace details.

 

column file_name format a55

set linesize 132

set pages 200

column tablespace_name format a11

set verify off

accept tbs prompt 'What is the Tablespace Name: '

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')

"Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes

* 100, 0), '990.00') free_pct,

decode(sign(

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /

1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB"

FROM 

sys.dba_tablespaces dts, 

(select tablespace_name, sum(bytes) bytes 

from dba_data_files group by tablespace_name) ddf, 

(select tablespace_name, sum(bytes) bytes 

from dba_free_space group by tablespace_name) dfs 

WHERE 

dts.tablespace_name = ddf.tablespace_name(+) 

AND dts.tablespace_name = dfs.tablespace_name(+) 

and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files

where tablespace_name = '&tbs';

To find information about locks in the database

To check locks in the database:

col schema format a8

col object format a20

select substr(a.os_user_name,1,9)    "OS User" 

,      substr(a.oracle_username,1,8) "DB User" 

,      substr(b.owner,1,8)           "Schema" 

,      e.sid      "Sid"

,      substr(b.object_name,1,20)    "Object" 

,      substr(b.object_type,1,10)    "Type" 

,      substr(c.segment_name,1,5)    "RBS" 

,      substr(d.used_urec,1,12)      "# of Records" 

from v$locked_object a 

,    dba_objects b 

,    dba_rollback_segs c 

,    v$transaction d 

,    v$session e 

where a.object_id =  b.object_id 

  and a.xidusn    =  c.segment_id 

  and a.xidusn    =  d.xidusn 

  and a.xidslot   =  d.xidslot 

  and d.addr      =  e.taddr;

Locksid

====

select substr(a.os_user_name,1,9)    "OS User"

,      substr(a.oracle_username,1,8) "DB User"

,      substr(b.owner,1,8)           "Schema"

,      substr(b.object_name,1,20)    "Object"

,      substr(b.object_type,1,10)    "Type"

,      substr(c.segment_name,1,5)    "RBS"

,      substr(d.used_urec,1,12)      "# of Records"

, e.sid

from v$locked_object a

,    dba_objects b

,    dba_rollback_segs c

,    v$transaction d

,    v$session e

where a.object_id =  b.object_id

  and a.xidusn    =  c.segment_id

  and a.xidusn    =  d.xidusn

  and a.xidslot   =  d.xidslot

  and d.addr      =  e.taddr

/

comment : usefull to check sql for sid or locksql

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

Check the size of different pools in database using SQL command and database parameter file.

set linesize 500
set pages 200
col name for a35
col value for a35

select name, value from v$parameter2 where name in ('sga_target','java_pool_size','large_pool_size','shadow_core_dump','db_cache_size','sga_max_size','sga_target','shared_pool_reserved_size','shared_pool_size');

Checking at OS Level.

egrep -i 'shared_pool_size|shared_pool_reserved_size|db_cache_size|sga_max_size|sga_target' initTEST.ora

*.db_cache_size=2G
*.sga_max_size=0
*.sga_target=0
*.shared_pool_reserved_size=320M
*.shared_pool_size=3500M
*pga_aggregate

Solaris tar and untar commands

 ++++++++++++++++++++++++

solaris tar commands

++++++++++++++++++++++

nohup tar -cvf - appl |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/appl.tar.gz &

nohup tar -cvf - 10.1.2 |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/1012.tar.gz &

 nohup tar -cvf - comn |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/comn.tar.gz &

 nohup tar -cvf - 10.1.3 |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/1013.tar.gz &

+++++++++++++++++++++++++++

solaris untar commands

+++++++++++++++++++++++++

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/comn.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1012.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/appl.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1013.tar.gz | tar -xvf - &

 nohup  gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1120.tar.gz | tar -xvf - &

Change database global name

  Global database name

=========================

alter database rename global_name to abcgapn1.bh.com;

Linux tar and untar command

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

Linux Tar Command:

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

cd /u01/oracle/

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Sep2013/PGAP1_1120.tar.gz 11.2.0 > /bhapgag1/staging/bin_gpap1_01Oct2013/1120.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_appl.tar.gz appl > /bhapgag1/staging/bin_gpap1_01Oct2013/appl.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/GAP1_comn.tar.gz comn > /bhapgag1/staging/bin_gpap1_01Oct2013/comn.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1012.tar.gz 10.1.2 > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.2.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1013.tar.gz 10.1.3 > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.3.log &

Linux Untar Command:

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

cd /u01

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Sep2013/PGAP1_1120.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/1120_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_appl.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/appl_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_comn.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/comn_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1012.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.2_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1013.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.3_untar.log & 

setup ssh

 setup ssh

++++++++++

on Node1:

---------

mkdir $HOME/.ssh

ssh-keygen -t rsa

copy content of id_rsa.pub to authorized_keys on other (node2) server

on Node2:

---------

and do the same on node2

mkdir $HOME/.ssh

ssh-keygen -t rsa

copy content of id_rsa.pub to authorized_keys on other (node1) server 

How to to find out toad session

 ==========================

To find out toad session

==========================

select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser

from V$process p,V$session s where s.paddr = p.addr and s.status = 'ACTIVE' and s.module like '%TOAD%';

How to get encrypted password of a user.

 ====================================

To get encrypted password of a user.

===================================

select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password from  dba_users where username = 'GLOGOWNER';

How to remove archives for a specific date?

 ++++++++++++++++

How to remove archives for a specific date?

++++++++++++++++

$cd /u01/archive

$rm `ls -l|grep "Apr 13" |awk '{print $9}'`