Monday, 13 July 2015
Oracle Database Monitoring Scripts
/*List of Accessed Objects */
sET PAGESIZE 60
SET LINESIZE 300
COLUMN type FORMAT a40
COLUMN sid FORMAT 9999
COLUMN object FORMAT a40
COLUMN owner FORMAT a20
SELECT a.type, Substr(a.owner,1,30) owner, a.sid,Substr(a.object,1,30) object
FROM v$access a WHERE a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;
/*CPU Usage for Active Sessions*/
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000
SELECT s.username, t.sid, s.serial#, SUM(VALUE/100) as "cpu usage (seconds)"
FROM v$session s, v$sesstat t, v$statname n WHERE t.STATISTIC# = n.STATISTIC#
AND NAME like '%CPU used by this session%' AND t.SID = s.SID AND s.status='ACTIVE'
AND s.username is not null GROUP BY username,t.sid,s.serial#;
/*Display all logged sessions*/
SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A35
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username, s.osuser, s.sid,s.serial#, p.spid,s.lockwait,
s.status,s.service_name,s.module,s.machine,s.program,TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v$session s, v$process p WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;
/*Displays Last Analyzed Details for a given Schema,All schema owners if 'ALL' specified*/
SET PAGESIZE 60
SET LINESIZE 300
SELECT t.owner, t.table_name AS "Table Name", t.num_rows AS "Rows", t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB", to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed" FROM dba_tables t,v$parameter p WHERE t.owner = Decode(Upper('&&Table_Owner'), 'ALL', t.owner, Upper('&&Table_Owner')) AND p.name = 'db_block_size' ORDER by t.owner,t.last_analyzed,t.table_name ;
/*Lists the volume of archived redo by hour for the specified day */
SET VERIFY OFF PAGESIZE 30
WITH hours AS (
SELECT TRUNC(SYSDATE) - &1 + ((level-1)/24) AS hours
FROM dual CONNECT BY level < = 24
)
SELECT h.hours AS date_hour,
ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb FROM hours h
LEFT OUTER JOIN v$archived_log al ON h.hours = TRUNC(al.first_time, 'HH24')
GROUP BY h.hours ORDER BY h.hours;
/* Archived logs list*/
sELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1)
Count#, Min(RECID) Min#, Max(RECID) Max# FROM
v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD')
ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#,
Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ;
/*Archive Generation History*/
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
/* Archivelog history*/
col "MONTH" FOR a14
col "DAY" for a28
select to_char(trunc(first_time), 'Month') Month, to_char(trunc(first_time), 'Day : DD-Mon-YYYY') Day, count(*) counts from v$log_history where trunc(first_time) > last_day(sysdate-100) +1 group by trunc(first_time);
/*Cache hit ratio*/
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';
/* Check database locks and blockings*/
sELECT SUBSTR(TO_CHAR(session_id),1,5) "SID", SUBSTR(lock_type,1,15) "Lock Type", SUBSTR(mode_held,1,15) "Mode Held", SUBSTR(blocking_others,1,15) "Blocking?" FROM dba_locks ;
/*Displays information on the current wait states for all active database sessions*/
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT a.username,a.osuser,a.sid,a.serial#, d.spid AS process_id, a.wait_class,a.seconds_in_wait, a.state,a.blocking_session,a.blocking_session_status,a.module,TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM v$session a,v$process d WHERE a.paddr = d.addr AND a.status = 'ACTIVE' ORDER BY 1,2;
/*Displays the recovery status of each datafile */
SET LINESIZE 500
SET PAGESIZE 500
SET FEEDBACK OFF
col Datafile for a44
SELECT Substr(a.name,1,60) "Datafile", b.status "Status" FROM v$datafile a,v$backup b WHERE a.file# = b.file#;
SET PAGESIZE 14
SET FEEDBACK ON
/*Displays datafiles information */
SET LINESIZE 200
COL FILE_NAME FOR a48
SELECT file_id, file_name,ROUND(bytes/1024/1024/1024) AS size_gb,ROUND(maxbytes/1024/1024/1024) AS max_size_gb,autoextensible,increment_by,status FROM dba_data_files ORDER BY file_name;
/*Displays general information about the database*/
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF
SELECT * FROM v$database;
SELECT * FROM v$instance;
SELECT * FROM v$version;
SELECT a.name,a.value FROM v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",NVL(c.status,'UNKNOWN') "Status" FROM v$controlfile c ORDER BY 1;
SELECT Substr(d.name,1,60) "Datafile",NVL(d.status,'UNKNOWN') "Status",d.enabled "Enabled",LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d ORDER BY 1;
SELECT l.group# "Group",Substr(l.member,1,60) "Logfile",NVL(l.status,'UNKNOWN') "Status" FROM v$logfile l ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
/*Database Object Counts*/
prompt
col owner for a18
select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(case when a.object_type = 'TABLE' then 1 else null end) "Tables",
count(case when a.object_type = 'INDEX' then 1 else null end) "Indexes",
count(case when a.object_type = 'PACKAGE' then 1 else null end) "Packages",
count(case when a.object_type = 'SEQUENCE' then 1 else null end) "Sequences",
count(case when a.object_type = 'TRIGGER' then 1 else null end) "Triggers",
count(case when a.object_type not in ('PACKAGE','TABLE','INDEX','SEQUENCE','TRIGGER') then 1 else null end) "Other",count(case when 1 = 1 then 1 else null end) "Total" from dba_objects a group by rollup(a.owner) ;
/*Database size*/
prompt
with dbsize as
(select ' '||tablespace_name tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_data_files group by tablespace_name
union all
select ' '||tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_temp_files group by tablespace_name
union all
select 'LOGFILES',sum(bytes)/(1024*1024) size_mb from v$log
)
select * from dbsize
union all
select 'Total',sum(size_mb) from dbsize order by 1;
/*check ITL waits*/
Set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
select owner,object_name||' '||subobject_name object_name, value from v$segment_statistics where statistic_name = 'ITL waits' and value > 0 order by 3,1,2;
/* check log sizes*/
SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1)
Count#, Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#, Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ;
/*Provides information about memory resize operations*/
SET LINESIZE 200
COLUMN parameter FORMAT A25
SELECT start_time,end_time,component,oper_type,oper_mode,parameter,ROUND(initial_size/1024/1204) AS initial_size_mb,
ROUND(target_size/1024/1204) AS target_size_mb,ROUND(final_size/1024/1204) AS final_size_mb,status
FROM v$memory_resize_ops ORDER BY start_time;
/*memory allocation to all db sessions*/
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A20
COLUMN module FORMAT A50
COLUMN program FORMAT A50
SELECT NVL(a.username,'(oracle)') AS username,a.module,a.program,Trunc(b.value/1024) AS Memory_KB FROM v$session a,v$sesstat b,v$statname c WHERE a.sid = b.sid AND b.statistic# = c.statistic# AND c.name = 'session pga memory' AND a.program IS NOT NULL ORDER BY b.value DESC;
Thursday, 13 November 2014
Create Trigger to Monitor Database Errors
First, we have to create a table where the errors are stored, but make sure it's a use who has global rights on the database:
CREATE TABLE error_log (
server_error VARCHAR2(100),
osuser VARCHAR2(30),
username VARCHAR2(30),
machine VARCHAR2(64),
process VARCHAR2(24),
program VARCHAR2(48),
stmt VARCHAR2(4000),
msg VARCHAR2(4000),
date_created DATE
);
After we've created the table, we simply add a trigger with is fired by "AFTER SERVERERROR ON DATABASE":
CREATE OR REPLACE
TRIGGER error_log_trigger
AFTER SERVERERROR ON DATABASE
DECLARE
username_ error_log.username%TYPE;
osuser_ error_log.osuser%TYPE;
machine_ error_log.machine%TYPE;
process_ error_log.process%TYPE;
program_ error_log.program%TYPE;
stmt_ VARCHAR2(4000);
msg_ VARCHAR2(4000);
sql_text_ ora_name_list_t;
BEGIN
FOR i IN 1..NVL(ora_sql_txt(sql_text_), 0) LOOP
stmt_ := SUBSTR(stmt_ || sql_text_(i) ,1,4000);
END LOOP;
FOR i IN 1..ora_server_error_depth LOOP
msg_ := SUBSTR(msg_ || ora_server_error_msg(i) ,1,4000);
END LOOP;
SELECT osuser, username, machine, process, program
INTO osuser_, username_, machine_, process_, program_
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID');
INSERT INTO error_log VALUES (dbms_standard.server_error(1), osuser_, username_, machine_, process_, program_, stmt_, msg_, SYSDATE);
END;
Tuesday, 11 November 2014
Tablespace and datafile monitoring
To check Tablespace free space:
Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.
- SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
- SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id;
- SELECT /* + RULE */ df.tablespace_name "Tablespace",df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace,fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC;
- Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;
- SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;
Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T
he script is used in Oracle version 10g onwards.
- SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM') GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname ORDER BY ts.tsname, days;
- Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add from (select tablespace_name,0 tots,sum(bytes) sumb from dba_free_space a group by tablespace_name union Select tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name having sum(a.sumb)*100/sum(a.tots) < 10 order by pct_free;
- Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments where TABLESPACE_NAME = 'SDH_HRMS_DBF' group by OWNER, SEGMENT_NAME;
- Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg where obj.owner = seg.owner(+) order by 3 desc ,2 desc, 1;
- Select * from database_properties where PROPERTY_NAME like '%DEFAULT%';
- Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS';
- Select default_tablespace,temporary_tablespace,username from dba_users;
- SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;
- SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM V$temp_space_header GROUP BY tablespace_name;
- 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;
- 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 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;
- 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 v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_nameORDER BY S.sid;
- SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,NVL(s.username, 'None') orauser,s.program, r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size';
- SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks;
- Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;
- SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
- WITH total_io AS (SELECT SUM (phyrds + phywrts) sum_io FROM v$filestat) SELECT NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT, phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c WHERE a.file# = b.file# ORDER BY a.file#;
- SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes FROM dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to FROM dba_extents GROUP by file_id) b WHERE a.file_id = b.file_id ORDER BY a.tablespace_name, a.file_name;
- Select SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name tabsp_name,fn.name file_name,ddf.bytes/1024/1024 cur_size, decode(fex.maxextend,NULL,ddf.bytes/1024 1024,fex.maxextend*tn.blocksize/1024/1024) max_size,nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024) unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024 inc_by from sys.v_$dbfile fn, sys.ts$ tn, sys.filext$ fex, sys.file$ ft, dba_data_files ddf where fn.file# = ft.file# and fn.file# = ddf.file_id and tn.ts# = ft.ts# and fn.file# = fex.file#(+) order by 1;
How to solve shutdown hangs oracle
In order to check reason why shutdown immediate hangs
SQL>connect / as SYSDBA
SQL>Select * from x$ktuxe where ktuxecfl =
'DEAD';
This shows dead transactions that SMON is looking to
rollback.
Now Plan to shutdown again and gather some information.
Before issuing the shutdown immediate command set some events as follows:
SQL>alter session set events '10046 trace name context forever, level 12';
SQL>alter session set events '10400 trace name
context forever, level 1';
SQL>shutdown immediate;
10046 turns on extended SQL_TRACE for the shutdown
process.
10400 dumps a system state every 5 minutes.
The trace files should show where the time is going. To
check the progress of SMON is very important in this case. You can find it with
the below query.
SQL>SELECT r.NAME "RB Segment Name",
dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,
v$session.PROGRAM, v$session.module, action
FROM v$lock l, v$process p, v$rollname r, v$session,
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg
WHERE l.SID = p.pid(+) AND v$session.SID = l.SID AND
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND
-- l.lmode(+) = 6
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,
v$session.PROGRAM, v$session.module, action
FROM v$lock l, v$process p, v$rollname r, v$session,
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg
WHERE l.SID = p.pid(+) AND v$session.SID = l.SID AND
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND
-- l.lmode(+) = 6
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;
Reason: Shut down
immediate may hang because of various reasons.
- Processes still continue to be connected to the database and do not terminate.
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transactions are being rolled back.
Debugging a hung database in
oracle version 11g
Back in oracle 10g a hung database was real problem,
especially could not connect via SQL*plus release the source of the hanging.
There is a new feature in Oracle 11g SQL*Plus called the “prelim” option. This
option is very useful for running oradebug and other utilities that do not
require a real connection to the database.
C:\>sqlplus –prelim
-or- in SQL you can set
SQL>Set _prelim on
SQL>connect / as sysdba
Now you are able to run oradebug commands to diagnose a hung
database issue:
SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to identify process
state changes.
SQL>oradebug hanganalyze 3
Open a separate SQL session and immediately generate a
system state dump.
SQL>alter session set events 'immediate trace
name SYSTEMSTATE level 10';
How to Check why shutdown immediate taking longer time to
shutdown?
Ref. 1076161.6: Shutdown immediate or shutdown Normal hangs. SMON disabling TX recovery
Ref. Note 375935.1: What to do and not to do when shutdown
immediate hangs.
Ref. Note 428688.1: Shutdown immediate very slow to close database.
When shutdown immediate taking longer time as compare to the
normal time usually it is taking. You must perform following task before
performing actual shutdown immediate.
- All active session.
- Temporary Tablespace Recover.
- Long Running Query in Database.
- Large Transaction.
- Progress of the Transaction that oracle is recovering.
- Parallel Transaction Recovery.
- SQL> Select sid, serial#, username, status, schemaname, logon_time from v$session where status='ACTIVE' and username is not null;
If Active session is exist then, try to find out what
is doing in the database by this session. Active session makeshutdown
slower
- SQL> Select f.R "Recovered", u.nr "Need Recovered" from (select count(block#) R , 1 ch from sys.fet$ ) f,(selectcount(block#) NR, 1 ch from sys.uet$) u where f.ch=u.ch;
Check to see any long query is running into the database
while you are trying to shutdown the database.
- SQL> Select * from v$session_longops where time_remaining>0 order by username;
Check to ensure large transaction is not going on while you
are trying to shutdown the database.
- SQL>Select sum(used_ublk) from v$transaction;
Check the progress of the transaction that oracle is
recovering.
- SQL>Select * from v$fast_start_transactions;
Check to ensure that any parallel transaction recovery is
going on before performing shutdown immediate.
- SQL>Select * from v$fast_start_servers;
Finally if you do not understand the reason why the shutdown
is hanging or taking longer time to shutdown then try to shutdown your database
with ‘abort’ option and startup with ‘restrict’ option and try shutdown with
‘immediate’ option.
Check the alert.log, if you find any error related ‘Thread
1 cannot allocate new log, sequence’ then you need to enable your
archival process. Your archival is disable due to any reason.
Process:
1. In command prompt set the oracle_sid first
- $ SET ORACLE_SID = ‘your db_name’
2. Now start the SQL*plus:
- $ sqlplus /nolog
- SQL>connect sys/***@instance_name
- SQL>Select instance_name from v$instance;
3. Try to checkpoint before shutdown abort
- SQL>alter system checkpoint;
- SQL> shutdown abort;
4. Start the database with ‘restrict’ option so that no other
user is able to connect you in the mean time.
- SQL>startup restrict;
- SQL>select logins from v$instance;
RESTRICTED
- SQL>shutdown immediate;
5. Mount the database and ensure archive process is enabling
by using archive log list command. If it is disabling then enable it.
- SQL>startup mount;
- SQL> archive log list; --if disable then enable it
- SQL>Alter database archivelog;
- SQL> Alter system archive log start;
Note: If your archivelog
destination and format is already set no need to set again. After setting check
with the ‘archive log list’ command archival is enable or not.
- SQL> alter database open;
Now check if your database is still in restricted mode then
remove the restriction.
- SQL>select logins from v$instance;
- SQL>alter system disable restricted session;
Note: Now try to generate archivelog with any command
- SQL>alter system archivelog current;
- SQL>alter system switch logfile;
How to Change DBTIMEZONE after Database Creation
DBTIMEZONE is a function which returns
the current value of Database Time Zone. It can be queried using the example
below:
SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
-07:00
Please note the return type of function is Time Zone Offset.
The format ([+|-] TZH: TZM) contains the lead (+) and lag (-) with
hour and minutes specifications.
Notes:
1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.
Example:
Notes:
1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.
Example:
SELECT TZ_OFFSET('America/Menominee') FROM DUAL;
TZ_OFFS
--------
-06:00
3. Time zone is set during database creation or using CREATE
DATABASE. It can be altered using ALTER DATABASE
command. Database time zone cannot be altered if a
column of type TIMESTAMP WITH [LOCAL] TIMEZONE exists in the database because TIMESTAMP
with LOCAL
TIMEZONE columns are stored to normalize the database. Time zone can be
set in Location zone format or [+|-]HH:MM format.
In the case when you have any column with TIMESTAMP LOCAL
TIMEZONE (TSLTZ) then you have to follow the backup–drop that table–change
the timezone then restore that table. To check run the below query and
notice the output:
Select u.name || '.' || o.name || '.' || c.name "Col TSLTZ"
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;
Col TSLTZ
--------------
ASSETDVP.TEST.TSTAMP
For Example follow the below steps:
For Example follow the below steps:
1- Backup the table that contains this column (ASSETDVP.TEST.TSTAMP
Table).
2- Drop the table or the column only
3- Issue again the alter database to change the DB time Zone
4- Add the dropped column and restore the data OR restore
the table if it's dropped
Example:
SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM
DUAL;
SESSIONTIMEZONE DBTIMEZONE
+06:00 -07:00
SQL> ALTER DATABASE SET
TIME_ZONE='America/Menominee';
Database altered.
SQL> ALTER DATABASE SET TIME_ZONE='-06:00';
Database altered.
SQL> Shutdown;
SQL> Startup;
SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM
DUAL;
SESSIONTIMEZONE DBTIMEZONE
+06:00 +06:00
Note: Once the time zone is set, database must be bounced
back to reflect this changes because alter database didn’t change the init.ora
parameter.4. Difference between SYSDATE and DBTIMEZONE- SYSDATE shows the date-time details provided by the OS on the server. It has nothing to do with TIMEZONE of the database.
5. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.
Oracle database silent-mode installation
You can automate the installation and configuration of Oracle software, either
fully or partially, by specifying a response file when you start the Oracle
Universal Installer. The Installer uses the values contained in the response
file to provide answers to some or all of the Installer prompts:
If you include responses for all of the prompts in the
response file and specify the -silent option when starting the Installer, then
the Installer runs in silent mode. During a silent-mode installation, the
Installer does not display any screens. Instead, it displays progress
information in the terminal that you used to start it.
PREREQUEST
Adding dba group
[root@carol root]# groupadd -g 200 dba
oracle User creation
[root@carol root]# useradd -g dba -d /home/oracle -s /bin/bash -c Oracle Software Owner -m -u 300 oracle
Kernel Settings
vi /etc/sysctl.conf
SET THE ORACLE RECOMENDED KERNEL PARAMETERS IN THIS FILE
OraInst file
[root@carol root]# mkdir -p /var/opt/oracle
[root@carol root]# cd /var/opt/oracle
[root@carol oracle]# vi oraInst.loc
and enter the values
inventory_loc=home/oracle/oraInventory
inst_group=
(save and exit)
[root@carol root]# cat /var/opt/oracle/oraInst.loc
inventory_loc=home/oracle/oraInventory
inst_group=
[root@carol oracle]# chown oracle:dba oraInst.loc
[root@carol oracle]# chmod 664 oraInst.loc
[root@carol oracle]# su - oracle
[oracle@carol oracle]$ cd database/response/
[oracle@carol response]$ vi enterprise.rsp
Modify the Below Three Values for SOFTWARE ONLY INSTALLATION
ORACLE_HOME=/home/oracle/product/10.2.0.1
ORACLE_HOME_NAME=orcl
n_configurationOption=3
[oracle@carol database]$ ./runInstaller -silent -responsefile /home/oracle/database/response/enterprise.rsp
Starting Oracle Universal Installer...
...............
skipped ....
Installation in progress (Thu May 15 23:54:45 IST 2008)
............................................................... 18% Done.
............................................................... 36% Done.
............................................................... 54% Done.
............................................................... 73% Done.
............ 76% Done.
Install successful
Linking in progress (Thu May 15 23:59:36 IST 2008)
Link successful
Setup in progress (Fri May 16 00:06:30 IST 2008)
.............. 100% Done.
Setup successful
The following configuration scripts
/home/oracle/product/10.2.0.1/root.sh
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.
Open A new Window with root user and execute the below script
/home/oracle/product/10.2.0.1/root.sh
need to be executed as root for configuring the system. If you skip the execution of the configuration tools, the configuration will not be complete and the product wont function properly. In order to get the product to function properly, you will be required to execute the scripts and the configuration tools after exiting the OUI.
[root@carol ]# sh /home/oracle/product/10.2.0.1/root.sh
Running Oracle10 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /home/oracle/product/10.2.0.1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@carol ]#
Test Oracle Installation From Oracle User
[oracle@carol oracle]$ source .bash_profile
[oracle@carol oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 00:10:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
Steps to create a database manually on Linux
This article shows you steps to create a database manually on Linux.
Step 1:
First create all the necessary directories. Followings are my directories:
NB:Make sure you create I have them already and therefore I will just list them
testdb1]$ ls
admin backup archive
admin]$ ls
adump bdump cdump udump
Step 2:
Next prepare the database creation script. Following is my script "testdb1.sql"
CREATE DATABASE "testdb1"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d02/carol/testdb1/redo1.log' SIZE 10M,
GROUP 2 '/d02/carol/testdb1/redo2.log' SIZE 10M,
GROUP 3 '/d02/carol/testdb1/redo3.log' SIZE 10M
DATAFILE
'/d02/carol/testdb1/system.dbf' size 100m,
'/d02/carol/testdb1/usr04.dbf' size 10m
sysaux datafile '/d02/carol/testdb1/sysaux.dbf' size 100m
undo tablespace undotbs
datafile '/d02/carol/testdb1/undo.dbf' size 50m
CHARACTER SET US7ASCII
;
Step 3:
Prepare the init file. Like this one [inittestdb1.ora]
*.audit_file_dest='/d02/carol/testdb1/admin/adump'
*.background_dump_dest='/d02/carol/testdb1/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/d02/carol/testdb1/control01.ctl',
'/d02/carol/testdb1/control02.ctl','/d02/carol/testdb1/control03.ctl'
*.core_dump_dest='/d02/carol/testdb1/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/d02/carol/testdb1/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/d02/carol/testdb1/admin/udump'
*.db_recovery_file_dest='/d02/carol/testdb1/backup'
*.db_recovery_file_dest_size=2147483648
Step 4:
Now perform the following steps:
$ export ORACLE_SID=testdb1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittestdb1.ora nomount
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes
SQL> @testdb1.sql
Database created.
Step 5:
So your database is create. Now just run the catalog.sql and catproc.sql scripts.
You will find the in $ cd $ORACLE_HOME/rdbms/admin
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
---------
TESTDB1
Okay, now your database is ready to use.
Step 1:
First create all the necessary directories. Followings are my directories:
NB:Make sure you create I have them already and therefore I will just list them
testdb1]$ ls
admin backup archive
admin]$ ls
adump bdump cdump udump
Step 2:
Next prepare the database creation script. Following is my script "testdb1.sql"
CREATE DATABASE "testdb1"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d02/carol/testdb1/redo1.log' SIZE 10M,
GROUP 2 '/d02/carol/testdb1/redo2.log' SIZE 10M,
GROUP 3 '/d02/carol/testdb1/redo3.log' SIZE 10M
DATAFILE
'/d02/carol/testdb1/system.dbf' size 100m,
'/d02/carol/testdb1/usr04.dbf' size 10m
sysaux datafile '/d02/carol/testdb1/sysaux.dbf' size 100m
undo tablespace undotbs
datafile '/d02/carol/testdb1/undo.dbf' size 50m
CHARACTER SET US7ASCII
;
Step 3:
Prepare the init file. Like this one [inittestdb1.ora]
*.audit_file_dest='/d02/carol/testdb1/admin/adump'
*.background_dump_dest='/d02/carol/testdb1/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/d02/carol/testdb1/control01.ctl',
'/d02/carol/testdb1/control02.ctl','/d02/carol/testdb1/control03.ctl'
*.core_dump_dest='/d02/carol/testdb1/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/d02/carol/testdb1/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/d02/carol/testdb1/admin/udump'
*.db_recovery_file_dest='/d02/carol/testdb1/backup'
*.db_recovery_file_dest_size=2147483648
Step 4:
Now perform the following steps:
$ export ORACLE_SID=testdb1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittestdb1.ora nomount
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes
SQL> @testdb1.sql
Database created.
Step 5:
So your database is create. Now just run the catalog.sql and catproc.sql scripts.
You will find the in $ cd $ORACLE_HOME/rdbms/admin
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
---------
TESTDB1
Okay, now your database is ready to use.
Subscribe to:
Posts (Atom)