Daily Scripts Syntax

Set Environment Variables:
=================================================
export PATH=$ORACLE_HOME/bin:$PATH
set path=%ORACLE_HOME%\bin;%PATH%
export TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN=/scripts/db_backup/params/remote

AWR, ADDM and utlrp:
=============================================
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@?/rdbms/admin/utlrp.sql

DB Status:
===========================
set pages 200 lines 200;
col logins for a10;
col status for a10;
col host_name for a30;
col instance_name for a14;
select instance_name,host_name,status,logins, to_char(startup_time,’DD_MON_YYYY HH24:MI:SS’) STARTUP_TIME from gv$instance;

set pages 200;
set lines 200;
col logins for a10;
col status for a10;
col host_name for a30;
col instance_name for a15;
select name “Database Name”,(select to_char(sysdate,’DD-MM-YYYY HH24:MI:SS’) from dual) “Collected” from v$database;
select instance_name,host_name,status,logins, to_char(startup_time,’DD_MON_YYYY HH24:MI:SS’) STARTUP_TIME from gv$instance;
select name, open_mode from v$database;

Blocking Sessions:
===================================================================
select ‘SID ‘ || l1.sid ||’ is blocking ‘ || l2.sid blocking
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

select inst_id,sid,username,program,blocking_session from gv$session where blocking_session is not null;

select * from gv$lock where block=1;

select sa.username as blocker,
a.sid,
‘ is blocking ‘,
sb.username as blockee,
b.sid
from gv$lock a,
gv$lock b,
gv$session sa,
gv$session sb
where b.request > 0
and a.block = 1
and a.id1 = b.id1
and a.id2 = b.id2
and sb.sid = b.sid
and sa.sid = a.sid;

session Status
+++++++++++++++++++++++++++++++++++++++
select sid,serial#,status from v$session where sid=’&sid’;

For active and inactive session details.
———————————————————————–
alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
select sid,serial#,username,program,status,logon_time,sql_id from gv$session where username is not null;

For All active session details at DB level
====================================================================
set lines 200 pages 200;
col username for a25;
col program for a50;
col LOGON_TIME for a30;
select INST_ID,sid,username,program,status,to_char(logon_time,’DD_MON_YYYY HH24:MI:SS’) LOGON_TIME,sql_id from gv$session where username is not null and status=’ACTIVE’;

SQL information using sqlid
========================================================================
select sql_text from gv$sqltext where sql_id=’&sqlid’ order by piece;

Wait Event for SID
===================================================================
select inst_id,SID,EVENT,wait_class from gv$session_wait where sid=’&sid’;

To Kill session in OS level
==========================================================
select a.sid,a.username,a.program,b.spid from gv$session a,gv$process b where a.paddr=b.addr and a.sid=’&a’;
ps -ef | grep
kill -9

Table locks:
===============================================================
set linesize 180;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a20;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
col terminal for a15;
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, gv$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
order by 2,3;

Long Running Operations:
==========================================================
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;

=================================================================================
set pages 50000 lines 32767
col “ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)||’KB'” for a40;
col table_name for a20;
col ‘Actual size’ for a20;
col ‘Fragmented size’ for a25;
col ‘reclaimable space %’ for a25;
select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/

Archive Generates an hour basis at DB level
===============================================================
SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
‘YYYY-MM-DD’) AS Start_Date,
To_Char(Vlh.First_Time,
‘HH24’) || ‘:00’ AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
‘YYYY-MM-DD’),
To_Char(Vlh.First_Time,
‘HH24’) || ‘:00′) Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;

TKPROF
================================================================================
TKPROF syntax is:
tkprof filename1 filename2 [waits=yesno] [sort=option] [print=n]
[aggregate=yesno] [insert=filename3] [sys=yesno] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]
Here is the example to use the TKPROF utility.
tkprof dmdprod_ora_11154.trc traceoutput.txt sys=no
schema size:
====================================================================================
select OWNER,sum(bytes)/1024/1024/1024 “SIZE_IN_GB” from dba_segments where owner=’&owner’ group by owner;
Table Size:
==========================================================================
col owner for a20;
col table_name for a30;
col ‘TABLE SIZE’ for a30;
col ‘ACTUAL DATA’ for a30;
select owner,table_name,round((blocks*8),2)||’ kb’ “TABLE SIZE”,round((num_rows*avg_row_len/1024),2)||’ kb’ “ACTUAL DATA”
from dba_tables
where table_name =’&table_name’;

RAC kill session:
====================================================================================
Kill session
select ‘ALTER SYSTEM KILL SESSION ”’||SID||’,’||SERIAL#||’,@’||INST_ID||”’ IMMEDIATE ;’
from gv$session
where SID = 2217 ;

SYSTEM Date
================================================================================
SELECT TO_CHAR
(SYSDATE, ‘MM-DD-YYYY HH24:MI:SS’) “NOW”
FROM DUAL;
RMAN Duplicate script

===================================================================================================
$ORACLE_HOME/bin/rman target ‘sys/New20#2sys@RIMUSP.world’ auxiliary / catalog rman/cofee#time2@RMANP1 << EOF | tee $RMAN_TMPFILE set echo on run { sql "alter session set optimizer_mode=RULE"; ALLOCATE auxiliary CHANNEL dev1 DEVICE TYPE SBT FORMAT 'DB_%d_%Y_%M_%D_%U' parms 'ENV=(TDPO_OPTFILE=/u001t/tsm/dsm/tdpo.opt.RIMUSP)'; ALLOCATE auxiliary CHANNEL dev2 DEVICE TYPE SBT FORMAT 'DB_%d_%Y_%M_%D_%U' parms 'ENV=(TDPO_OPTFILE=/u001t/tsm/dsm/tdpo.opt.RIMUSP)'; DUPLICATE TARGET DATABASE TO RIMUST until time "to_date('2013-05-26:05:00:00','YYYY-MM-DD:HH24:MI:SS')"; } quit EOF Tablespace show as per given percentage: ========================================================================= set pages 100; set lines 180; col tablespace_name for a30; col file_name for a65;set lines 200; select distinct a.tablespace_name, SUM(a.bytes)/1024/1024 "CurMb", SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb", (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed", (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree", round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent" from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c where a.file_id = b.file#(+) and a.tablespace_name = c.tablespace_name having round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) >=20
GROUP by a.tablespace_name, c.”Free”/1024
order by round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

DB Files for particular tablespace:
=================================================================
col file_name for a66;
select tablespace_name,file_name,file_id,bytes/1024/1024/1024,MAXBYTES/1024/1024/1024, autoextensible from dba_data_files where tablespace_name=’&tablespace_name’;

Tempfile information:
==================================================================
select file_name,TABLESPACE_NAME, BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_temp_files;

to remove old trace, logs or unnecessary files at OS level:
==============================================================================
remote Unix older files:
find . -mtime +15 -exec rm -Rf {} \;

DB directories location:
==========================================
set lines 200 pages 200;
col owner for a25;
col directory_name for a25;
col directory_path for a76;
select * from dba_directories;

ASM diskgroup information:
===============================================
select name, total_mb/1024,free_mb/1024 from v$asm_diskgroup;

RMAN
===============================================
This script will report on all backups – full, incremental and archivelog backups –
set lines 180;
col STATUS for a10;
col hrs format 999.99
col input_type for a15;
col start_time for a20;
col status for a30;
col end_time for a20;
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups –
set lines 180;
col STATUS for a10;
col hrs format 999.99
col input_type for a15;
col start_time for a20;
col status for a30;
col end_time for a20;
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type=’DB FULL’
order by session_key;

Only Archive log Backup Information.
set lines 180;
col STATUS for a10;
col hrs format 999.99
col input_type for a15;
col start_time for a20;
col status for a30;
col end_time for a20;
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type=’ARCHIVELOG’
order by session_key;

Check RMAN Backup Status
SQL to report on all backups i.e., Full, Incremental and Archive log backups:
col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

SQL to report on all Full and Incremental backup but not Archive log backups:

col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type like ‘%DB%’
order by session_key;

To check progress of RMAN Backup:
=================================

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE ‘%aggregate%’
AND opname like ‘RMAN%’;

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

To check RMAN backup size
=========================

select ctime “Date” , decode(backup_type, ‘L’, ‘Archive Log’, ‘D’, ‘Full’, ‘Incremental’)
backup_type, bsize “Size MB” from (select trunc(bp.completion_time) ctime,
backup_type, round(sum(bp.bytes/1024/1024),2) bsize from v$backup_set bs,
v$backup_piece bp where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = ‘A’
group by trunc(bp.completion_time), backup_type) order by 1, 2;

Archives generates per Hours
=======================================

SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
‘YYYY-MM-DD’) AS Start_Date,
To_Char(Vlh.First_Time,
‘HH24’) || ‘:00’ AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
‘YYYY-MM-DD’),
To_Char(Vlh.First_Time,
‘HH24’) || ‘:00′) Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;
=======================================================================================

select owner, table_name, index_name from dba_indexes where table_name=’&table_name’;
exec dbms_stats.gather_schema_stats(‘SIEBELWB’, cascade=>TRUE, degree=>4, estimate_percent=>10);
exec dbms_stats.gather_schema_stats(‘SIEBEL’, cascade=>TRUE, degree=>4, estimate_percent=>10);

DATApump
===================================================
set pages 200 lines 200;
col table_name for a35;
select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like ‘INSERT %INTO “%’
and command_type = 2
and open_versions > 0;

impdp wb_remote_monitor/remote#4 dumpfile=SIEBELWB_01092015.dmp logfile=SIEBELWB_01092015_imp.log SCHEMAS=SIEBELWB EXCLUDE=STATISTICS COMMIT=N BUFFER=102343 PARALLEL=4 directory=DB_REFRESH
*._optimizer_compute_index_stats=’FALSE’

Please make sure you revert back the hidden parameter after import. This parameter can be set dynamically also.
nohup EXPdp command
——————-
nohup expdp “‘/ as sysdba'” directory=DATAPUMP_DIR dumpfile=test_23rdjan.dmp full=y parallel=5 logfile=test_23rdjan.log &
date=”$(date +’%Y_%m_%d_%H_%M_%S’)”
nohup /your/job >logfile.${date} 2>&1 &

eg:
nohup expdp “‘/ as sysdba'” directory=EXPORTS dumpfile=4SP2_EYAPI_18_AUG_2015.DMP LOGFILE=4SP2_EYAPI_18_AUG_2015.LOG SCHEMAS=EYAPI >logfile.${date} 2>&1 &
estimate size of dump file:

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

expdp full=y estimate_only=Y estimate=BLOCKS
expdp directory=expdp_mig FULL=y ESTIMATE_ONLY=y
expdp directory=MIG_DUMP schemas=IEMAESTRO_USER,RMAESTRO_USER ESTIMATE_ONLY=y

Parallel
=================
expdp directory=DATAPUMP_DIR dumpfile=MACSUSD_24112015_new%u.dmp logfile=MACSUSD_24112015_new.log parallel=3 schemas=APEX_040200,


Betting site http://cbetting.co.uk more info for Coral Offers

Support Us?

Our only Source of Income via clicking Ads.Click our Web Ads to help our Community!




Like us in Facebook!

Love Our Articles?

Follow us in Facebook to find our Latest Stuff!

Powered by WordPress Popup