undo tablespace info
select distinct
s.sid,
s.osuser,
s.process,
s.sql_id,
round((u.undoblks*32768) / (1024*1024*1024)) GB,
u.tuned_undoretention
from v$undostat u, v$session s
where u.maxqueryid in s.sql_id
group by
s.sid,
s.osuser,
s.process,
s.sql_id,
u.undoblks,
u.tuned_undoretention
order by s.sid
select distinct
u.maxqueryid,
u.maxquerylen,
u.tuned_undoretention
from v$undostat u, v$session s
where u.tuned_undoretention > 20000 -- <<<-- second retention
and u.maxqueryid not in s.sql_id
group by u.maxqueryid, u.maxquerylen, u.tuned_undoretention;
find the SQL_ID retention period and not in active
select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';
SQL> select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';
SQL_TEXT
--------------------------------------------------------------------------------
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
select s.sid from v$undostat u, v$session s
where u.maxqueryid='0rc4km05kgzb9'
and u.maxqueryid = s.prev_sql_id;
to reset the undo stat
begin
reset_undo_stat();
end;
SYS AS SYSDBA>select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
5015
SYS AS SYSDBA>show parameter undo
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1
if MAX(MAXQUERYLEN)> undo_retention then you will get error
undo_retention =maxquerylen + 300 sec
================
some sql to monitor
set pages 200
col user0 form a15
col comm0 form a15
col name0 form a30
col extents0 form 999 Heading "Extents"
col shrinks0 form 999 Heading "Shrinks"
col waits form 9999 heading "Wraps"
select
rn.name name0,
s.username user0,
r.rssize ,
r.waits,
r.extents extents0,
r.shrinks shrinks0,
r.optsize,
decode (s.command,1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
10,'DROP INDEX',
12,'DROP INDEX',
26,'LOCK TABLE',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
48,'SET TRANSACTION',
NULL, NULL,
'look it up '||to_char(s.command)) comm0,
sql_id
from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.taddr (+) = t.addr
and t.xidusn (+) = r.usn
and rn.usn = r.usn
order by rn.name
/
SELECT rn.name name0
, p.pid
,p.spid
, NVL (p.username, 'NO TRANSACTION') user0
, p.terminal
FROM v$lock l, v$process p, v$rollname rn
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = rn.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY rn.name;
PROMPT
PROMPT Time since last WRAP
PROMPT WRAPS The number of times a rollback segment entry has wrapped from one extent to another.
select n.name, round( 24*((sysdate-startup_time) - trunc(sysdate-startup_time)) / (s.writes/s.rssize),1) "Hours"
from v$instance ,v$rollname n,v$rollstat s
where n.usn = s.usn
and s.status = 'ONLINE'
/
Search This Blog
Total Pageviews
Thursday 29 September 2011
Monday 26 September 2011
Oracle Undo statistics
Oracle Undo statistics ..
-- single instance set lines 1000 pages 200 column inst_id head inst# format 99 column hour head "hour|(dd-mm-yyyy-hh24)" justify centre column "activesize" head "active|size(mb)" format 99,999 justify right column undosize head "undo|size(mb)" format 99,999 justify right column expiredsize head "expired|size(mb)" format 99,999 justify right column unexpiredsize head "unexpired|size(mb)" format 99,999 justify right column expiredrelsize head "expired rel|size(mb)" format 99,999 justify right column maxconcurrency head "max |concurrent|txn(#)" format 99,999 justify right column txncount head "total|txn (#)" format 99,999 justify right column maxquerylen head "max query|length(sec)" format 99,999 justify right column nospaceerrcnt head "nospace|errors (#)" format 99,999 justify right column ssolderrcnt head "ora-01555|errors(#)" format 99,999 justify right select to_char(a.begin_time, 'dd-mm-yyyy-hh24') "hour" , round(sum(a.activeblks* 8 )/1024) "activesize" , round(sum(a.undoblks * 8 )/1024) "undosize" , round(sum(a.expiredblks * 8 )/1024) "expiredsize" , round(sum(a.unexpiredblks * 8 )/1024) "unexpiredsize" , round(sum(a.expblkrelcnt * 8 )/1024) "expiredrelsize" , max(maxconcurrency) maxconcurrency , sum(txncount) txncount , max(maxquerylen) maxquerylen , sum(nospaceerrcnt) nospaceerrcnt , sum(ssolderrcnt) ssolderrcnt from v$undostat a group by to_char(a.begin_time, 'dd-mm-yyyy-hh24') order by 1 / -- for Rac set lines 1000 pages 200 column inst_id head inst# format 99 column hour head "hour|(dd-mm-yyyy-hh24)" justify centre column activesize head "active|size(mb)" format 99,999 justify right column undosize head "undo|size(mb)" format 99,999 justify right column expiredsize head "expired|size(mb)" format 99,999 justify right column unexpiredsize head "unexpired|size(mb)" format 99,999 justify right column expiredrelsize head "expired rel|size(mb)" format 99,999 justify right column maxconcurrency head "max |concurrent|txn(#)" format 99,999 justify right column txncount head "total|txn (#)" format 99,999 justify right column maxquerylen head "max query|length(sec)" format 99,999 justify right column nospaceerrcnt head "nospace|errors (#)" format 99,999 justify right column ssolderrcnt head "ora-01555|errors(#)" format 99,999 justify right select a.inst_id , to_char(a.begin_time, 'dd-mm-yyyy-hh24') hour , round(sum(a.activeblks* 8 )/1024) activesize , round(sum(a.undoblks * 8 )/1024) undosize , round(sum(a.expiredblks * 8 )/1024) expiredsize , round(sum(a.unexpiredblks * 8 )/1024) unexpiredsize , round(sum(a.expblkrelcnt * 8 )/1024) expiredrelsize , max(maxconcurrency) maxconcurrency , sum(txncount) txncount , max(maxquerylen) maxquerylen , sum(nospaceerrcnt) nospaceerrcnt , sum(ssolderrcnt) ssolderrcnt from gv$undostat a group by a.inst_id, to_char(a.begin_time, 'dd-mm-yyyy-hh24') order by 2 ;
hour active undo expired unexpired expired rel concurrent total max query nospace ora-01555 (dd-mm-yyyy-h size(mb) size(mb) size(mb) size(mb) size(mb) txn(#) txn (#) length(sec) errors (#) errors(#) ------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- --------- 12-09-2016-09 488 2 570 4 0 4 181 1,125 0 0 12-09-2016-10 1,463 2 1,717 4 0 4 362 1,121 0 0 12-09-2016-11 1,463 2 1,711 6 0 4 346 1,110 0 0
Rac Output ..
max hour active undo expired unexpired expired rel concurrent total max query nospace ora-01555 inst# (dd-mm-yyyy-h size(mb) size(mb) size(mb) size(mb) size(mb) txn(#) txn (#) length(sec) errors (#) errors(#) ----- ------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- --------- 1 12-09-2016-09 976 2 1,141 7 0 4 232 1,128 0 0 2 12-09-2016-09 63 2 1,697 3 0 3 171 1,070 0 0 1 12-09-2016-10 1,463 2 1,717 4 0 4 362 1,121 0 0
Oracle ASM balanced files report
set pagesize 55
set linesize 90
SELECT group_kffxp Group#
, number_kffxp file#
, MAX(count1) MAX
, MIN(count1) MIN
FROM
(SELECT group_kffxp
, number_kffxp
, disk_kffxp
, COUNT(XNUM_KFFXP) count1
FROM x$kffxp
WHERE group_kffxp = &diskgroup_number
ANd disk_kffxp != 65534
GROUP BY group_kffxp, number_kffxp, disk_kffxp
ORDER BY group_kffxp
, number_kffxp
, disk_kffxp
)
GROUP BY group_kffxp, number_kffxp;
set linesize 90
SELECT group_kffxp Group#
, number_kffxp file#
, MAX(count1) MAX
, MIN(count1) MIN
FROM
(SELECT group_kffxp
, number_kffxp
, disk_kffxp
, COUNT(XNUM_KFFXP) count1
FROM x$kffxp
WHERE group_kffxp = &diskgroup_number
ANd disk_kffxp != 65534
GROUP BY group_kffxp, number_kffxp, disk_kffxp
ORDER BY group_kffxp
, number_kffxp
, disk_kffxp
)
GROUP BY group_kffxp, number_kffxp;
Oracle ASM usage by file type
ASM usage by file type
set term on
SET pages 32767
SET lines 255
SET numf 999,999
COLUMN NAME HEAD "DiskGroup" FORMAT A15
COLUMN type HEAD "FileType" FORMAT A20
COLUMN SizeGB HEAD "Size|(GB)"
TTITLE LEFT "ASM SPACE USAGE BY FILETYPE"
BREAK ON REPORT
BREAK ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON REPORT
select dg.name
, f.type
, ROUND(sum(bytes)/1024/1024/1024) SizeGB
from v$asm_file f
, v$asm_diskgroup dg
where dg.group_number = f.group_number
group by dg.name, f.type
ORDER BY dg.name, f.type ;
TTITLE OFF
set term on
SET pages 32767
SET lines 255
SET numf 999,999
COLUMN NAME HEAD "DiskGroup" FORMAT A15
COLUMN type HEAD "FileType" FORMAT A20
COLUMN SizeGB HEAD "Size|(GB)"
TTITLE LEFT "ASM SPACE USAGE BY FILETYPE"
BREAK ON REPORT
BREAK ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON REPORT
select dg.name
, f.type
, ROUND(sum(bytes)/1024/1024/1024) SizeGB
from v$asm_file f
, v$asm_diskgroup dg
where dg.group_number = f.group_number
group by dg.name, f.type
ORDER BY dg.name, f.type ;
TTITLE OFF
Oracle ASM Disk fail group
ASM Disk fail group
set term on
set lines 250
set pages 50
COLUMN DiskGroup FORMAT A10
COLUMN Disk FORMAT A30
COLUMN Partner_Disk FORMAT A30
COLUMN DiskGroup FORMAT A0
COLUMN FAILGROUP FORMAT A20
COLUMN PARTNER_FAILGROUP FORMAT A20
COLUMN path FORMAT A30
COLUMN PARTNER_PATH FORMAT A30
SELECT dg1.name DiskGroup
, d1.NAME Disk
-- , d1.path
, d1.FAILGROUP
, d2.name Partner_Disk
, d2.FAILGROUP PARTNER_FAILGROUP
-- , d2.path PARTNER_PATH
FROM x$kfdpartner p
, v$asm_disk d1
, v$asm_diskgroup dg1
, v$asm_disk d2
WHERE dg1.group_number = d1.group_number
AND p.GRP = dg1.group_number
AND p.disk = d1.DISK_NUMBER
AND p.GRP = d2.group_number (+)
AND p.NUMBER_KFDPARTNER = d2.DISK_NUMBER (+)
AND dg1.name like '%%' --DiskGroup Name
AND d1.name like '%%' --Disk Name
ORDER BY dg1.name , d1.NAME;
set term on
set lines 250
set pages 50
COLUMN DiskGroup FORMAT A10
COLUMN Disk FORMAT A30
COLUMN Partner_Disk FORMAT A30
COLUMN DiskGroup FORMAT A0
COLUMN FAILGROUP FORMAT A20
COLUMN PARTNER_FAILGROUP FORMAT A20
COLUMN path FORMAT A30
COLUMN PARTNER_PATH FORMAT A30
SELECT dg1.name DiskGroup
, d1.NAME Disk
-- , d1.path
, d1.FAILGROUP
, d2.name Partner_Disk
, d2.FAILGROUP PARTNER_FAILGROUP
-- , d2.path PARTNER_PATH
FROM x$kfdpartner p
, v$asm_disk d1
, v$asm_diskgroup dg1
, v$asm_disk d2
WHERE dg1.group_number = d1.group_number
AND p.GRP = dg1.group_number
AND p.disk = d1.DISK_NUMBER
AND p.GRP = d2.group_number (+)
AND p.NUMBER_KFDPARTNER = d2.DISK_NUMBER (+)
AND dg1.name like '%%' --DiskGroup Name
AND d1.name like '%%' --Disk Name
ORDER BY dg1.name , d1.NAME;
Oracle ASM diskgrop report
Oracle ASM diskgrop report ..
set feedback off pagesize 1000 head on underline _ linesize 170
var v number ;
begin :v := nvl('&v',95); end;
/
col total_gb format 99999.99 heading 'total gb '
col mb_avail format 999,999,999 heading 'mb avail'
col used format 999.99 heading '% used'
col volume_name format a10
col volume_device format a25
col mountpath format a15
col attention format a20
col name format a15
col COMPATIBILITY for a12
col DATABASE_COMPATIBILITY for a15
col total_gb format 99999.99 heading 'total gb '
col host_name for a15
set linesize 400
select
substr(max (SYS_CONTEXT('USERENV','HOST')),1,15) host_name
,g.group_number
, g.name
, g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
,max(allocation_unit_size/1024/1024) allocation_unit_size_MB
, g.total_mb/1024 total_gb
, trunc((g.free_mb/1024),2) free_gb
, usable_file_mb usable_file_mb
, count(DISK_NUMBER) Total_Disks
, trunc(max(d.TOTAL_MB/1024),2) disk_size
, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
else 'Good'
end as attention
from v$asm_diskgroup g,v$asm_disk d
where 1=1
and d.group_number = g.group_number
-- and g.Name not in ('GRID','ACFS' )
--and g.Name in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2))
--ORDER BY "TotalUsed%" desc
order by 2
;
set term on pages 200 lines 255 numf 999,999 COLUMN NAME HEAD "DiskGroup" FORMAT A15 COLUMN type HEAD "Type" FORMAT A6 COLUMN compatibility HEAD "ASM|Compat" FORMAT A10 COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10 COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 99999999 COLUMN offline_disks HEAD "Offline|Disks" COLUMN TOTAL_GB HEAD "Total|(GB)" COLUMN FREE_GB HEAD "Free|(GB)" COLUMN used_GB HEAD "Used|(GB)" COLUMN hot_used_GB HEAD "Hot|Used|(GB)" COLUMN cold_used_GB HEAD "Cold|Used|(GB)" COLUMN REQUIRED_MIRROR_FREE_GB HEAD "Required|Free|Mirror|(GB)" JUSTIFY RIGHT COLUMN USABLE_GB HEAD "Usable|Free|(GB)" TTITLE LEFT "ASM DISKGROUP SPACE USAGE REPORT" BREAK ON REPORT COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT -- if disk not mounted with instance their values will be 0. SELECT NAME , state , type , compatibility , database_compatibility , allocation_unit_size/1024/1024 allocation_unit_size_MB , offline_disks , ROUND(TOTAL_MB/1024) TOTAL_GB , ROUND(FREE_MB/1024) FREE_GB , ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB , ROUND(hot_used_mb/1024) HOT_USED_GB , ROUND(cold_used_mb/1024) COLD_USED_GB , ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB , ROUND(USABLE_FILE_MB /1024) USABLE_GB FROM v$asm_diskgroup;
===
var v number ;
begin :v := nvl('&v',90); end;
/
/
set term on pages 200 lines 255 numf 999,999
col name head "diskgroup" format a10
col type head "type" format a6
col compatibility head "asm|compat" format a10
col database_compatibility head "rdbms|compat" format a10
col allocation_unit_size_mb head "au|size|(mb)" format 999
col offline_disks head "offline|disks"
col total_gb head "total|(gb)"
col free_gb head "free|(gb)"
col used_gb head "used|(gb)"
col hot_used_gb head "hot|used|(gb)"
col cold_used_gb head "cold|used|(gb)"
col attention for a12
with disk as (select
d.group_number gp_num
, count(d.DISK_NUMBER) Total_Disks
, trunc(max(d.TOTAL_MB/1024),2) disk_size
from v$asm_disk d
group by d.group_number )
select
g.name
, di.total_disks
, di.disk_size
, g.state
, g.type
, g.compatibility
, g.database_compatibility
, g.allocation_unit_size/1024/1024 allocation_unit_size_MB
, g.offline_disks
, round(g.total_mb/1024) total_gb
, round(g.free_mb/1024) free_gb
, round((g.hot_used_mb + cold_used_mb) /1024) used_gb
, round(g.hot_used_mb/1024) hot_used_gb
, round(g.cold_used_mb/1024) cold_used_gb
, round(g.required_mirror_free_mb/1024) required_mirror_free_gb
, round(g.usable_file_mb /1024) usable_gb
, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
else 'Good'
end as attention
FROM v$asm_diskgroup g, disk di
where 1=1
and g.Name not in ('GRID' )
and g.group_number=di.gp_num ;
=====================
SET LINESIZE 200 PAGESIZE 9999
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 9999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / nullif(b.total_mb,0)))*100, 2) ||'%' pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY a.name
/
Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used
-------------------- ----------------- -------------------- -------------------- -------------- -------------- -----------------------------------------
DATA ORCL:FLSH_DATA0 FLSH_DATA0 FLSH_DATA0 102,401 44,067 43.03%
ORCL:FLSH_DATA1 FLSH_DATA1 FLSH_DATA1 102,401 44,058 43.02%
ORCL:FLSH_DATA2 FLSH_DATA2 FLSH_DATA2 102,401 44,053 43.02%
ORCL:FLSH_DATA3 FLSH_DATA3 FLSH_DATA3 102,401 44,055 43.02%
******************** -------------- --------------
409,604 176,233
DUMPS ORCL:FLSH_DUMPS0 FLSH_DUMPS0 FLSH_DUMPS0 512,000 501,900 98.03%
******************** -------------- --------------
512,000 501,900
GRID ORCL:FLSH_GRID0 FLSH_GRID0 FLSH_GRID0 40,961 396 .97%
******************** -------------- --------------
40,961 396
LOGS ORCL:FLSH_LOGS0 FLSH_LOGS0 FLSH_LOGS0 102,401 38,704 37.8%
******************** -------------- --------------
102,401 38,704
MGMT ORCL:FLSH_MGMT0 FLSH_MGMT0 FLSH_MGMT0 102,400 55,080 53.79%
******************** -------------- --------------
102,400 55,080
[CANDIDATE] ORCL:FLSH_RECO0 0 0 %
******************** -------------- --------------
0 0
-------------- --------------
Grand Total: 1,167,366 772,313
9 rows selected.
============
COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance
set lines 600
col state format a9
col dgname format a15
col sector format 999990
col block format 999990
col label format a25
col path format a40
col redundancy format a25
col pct_used format 990
col pct_free format 990
col voting format a6
BREAK ON REPORT
COMPUTE SUM OF raw_gb ON REPORT
COMPUTE SUM OF usable_total_gb ON REPORT
COMPUTE SUM OF usable_used_gb ON REPORT
COMPUTE SUM OF usable_free_gb ON REPORT
COMPUTE SUM OF required_mirror_free_gb ON REPORT
COMPUTE SUM OF usable_file_gb ON REPORT
COL name NEW_V _hostname NOPRINT
select lower(host_name) name from v$instance;
select
trim('&_hostname') hostname,
name as dgname,
state,
type,
sector_size sector,
block_size block,
allocation_unit_size au,
round(total_mb/1024,2) raw_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * total_mb, 'NORMAL', .5 * total_mb, total_mb))/1024,2) usable_total_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * (total_mb - free_mb), 'NORMAL', .5 * (total_mb - free_mb), (total_mb - free_mb)))/1024,2) usable_used_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * free_mb, 'NORMAL', .5 * free_mb, free_mb))/1024,2) usable_free_gb,
round((DECODE(TYPE, 'HIGH', 0.3333 * required_mirror_free_mb, 'NORMAL', .5 * required_mirror_free_mb, required_mirror_free_mb))/1024,2) required_mirror_free_gb,
round(usable_file_mb/1024,2) usable_file_gb,
round((total_mb - free_mb)/total_mb,2)*100 as "PCT_USED",
round(free_mb/total_mb,2)*100 as "PCT_FREE",
offline_disks,
voting_files voting
from v$asm_diskgroup
where total_mb != 0
order by 1;
===
set numf 99999.99
col DiskCnt for 9999
col "Group" for 999
SELECT g.group_number "Group" , g.name "Group Name" , g.state "State" , g.type "Type" , g.total_mb/1024 "Total GB" , g.free_mb/1024 "Free GB" , 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
, 100*(min(d.free_mb/d.total_mb)) "MinFree"
, 100*(max(d.free_mb/d.total_mb)) "MaxFree"
, count(*) "DiskCnt"
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number
and d.group_number <> 0
and d.state = 'NORMAL'
and d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
=====
from web
set linesize 300
sset term on
SSET numf 999,999
DEFINE size_label=GB
DEFINE size_divider="1024/1024/1024"
PROMPT **********************************************************************
PROMPT * A S M D I S K G R O U P S P A C E U S A G E R E P O R T *
PROMPT **********************************************************************
COLUMN NAME HEAD "DiskGroup" FORMAT A16
COLUMN group_number HEAD "G#" FORMAT 99
COLUMN type HEAD "Redundancy|Type" FORMAT A6
COLUMN compatibility HEAD "ASM|Compat" FORMAT A10
COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10
COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 999
COLUMN offline_disks HEAD "Offline|Disks"
COLUMN separator HEAD "!|!|!|!|!|!" FORMAT a1
COLUMN mirrored_total_GB HEAD "(E)||Mirrored|Total|(&size_label)"
COLUMN USABLE_TOTAL_GB HEAD "(A-D)|/REDUN||Safely|Usable|Total|(&size_label)"
COLUMN USABLE_USED_GB HEAD "(B-D)|/REDUN||Safely|Usable|Used|(&size_label)"
COLUMN USABLE_FREE_GB HEAD "(C-D)|/REDUN||Safely|Usable|Free|(&size_label)"
COLUMN USABLE_USED_PERCENT HEAD "Safely|Usable|Used|(%)" FORMAT 999.99
COLUMN USABLE_FREE_PERCENT HEAD "Safely|Usable|Free|(%)" FORMAT 999.99
COLUMN mirrored_free_GB HEAD "Mirrored|Free|(&size_label)"
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "(D)|Required|Free|ForMirror|(&size_label)" JUSTIFY RIGHT
COLUMN TOTAL_GB HEAD "(A)||Total|Avail|(&size_label)"
COLUMN used_GB HEAD "(B)||Total|Used|(&size_label)"
COLUMN hot_used_GB HEAD "Hot|Used|(&size_label)"
COLUMN cold_used_GB HEAD "Cold|Used|(&size_label)"
COLUMN FREE_GB HEAD "(C)||Total|Free|(&size_label)"
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_TOTAL_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_USED_GB FORMAT 9,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_FREE_GB FORMAT 9,999,999 ON REPORT
PROMPT
PROMPT . <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->
-- Note:
-- The GROUP_NUMBER, TOTAL_MB, and FREE_MB columns are only
-- meaningful if the disk group is mounted by the instance. Otherwise, their values will be 0.
SELECT NAME
, group_number
, type
, '!' separator
, ROUND(TOTAL_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) mirrored_TOTAL_GB
, ROUND((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) USABLE_TOTAL_GB
, ROUND((hot_used_mb + cold_used_mb)/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3) /1024) USABLE_USED_GB
, ROUND(USABLE_FILE_MB /1024) USABLE_FREE_GB
, ROUND((USABLE_FILE_MB + ROUND(REQUIRED_MIRROR_FREE_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) ) /1024) mirrored_free_GB
, ROUND( (hot_used_mb + cold_used_mb) / (TOTAL_MB - REQUIRED_MIRROR_FREE_MB ) * 100 , 2) USABLE_USED_PERCENT
, ROUND(USABLE_FILE_MB /((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) * 100 ,2) USABLE_FREE_PERCENT
, '!' separator
, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB
, ROUND(TOTAL_MB/1024) TOTAL_GB
, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB
, ROUND(hot_used_mb/1024) HOT_USED_GB
, ROUND(cold_used_mb/1024) COLD_USED_GB
, ROUND(FREE_MB/1024) FREE_GB
, '!' separator
FROM v$asm_diskgroup_stat;
Sunday 25 September 2011
Oracle default user
some Oracle default user
Where
owner not in
(
'ADAMS',
'ADLDEMO',
'ADMIN',
'APPLSYS',
'APPLYSYSPUB',
'APPS',
'AQ',
'AQDEMO',
'AQJAVA',
'AQUSER',
'AUDIOUSER',
'AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED',
'BC4J',
'BLAKE',
'CATALOG',
'CDEMO82',
'CDEMOCOR',
'CDEMORID',
'CDEMOUCB',
'CENTRA',
'CIDS',
'CIS',
'CISINFO',
'CLARK',
'COMPANY',
'COMPIERE',
'CQSCHEMAUSER',
'CSMIG',
'CTXDEMO',
'CTXSYS',
'DBA',
'DBI',
'DBSNMP',
'DEMO',
'DEMO8',
'DEMO9',
'DES',
'EJSADMIN',
'EMP',
'ESTOREUSER',
'EVENT',
'FINANCE',
'FND',
'FROSTY',
'GPFD',
'GPLD',
'HCPARK',
'HLW',
'HR',
'IMAGEUSER',
'IMEDIA',
'JMUSER',
'JONES',
'LBACSYS',
'LIBRARIAN',
'MASTER',
'MDSYS',
'MFG',
'MIGRATE',
'MILLER',
'MMO2',
'MODTEST',
'MOREAU',
'MTS_USER',
'MTSSYS',
'MXAGENT',
'NAMES',
'OAS_PUBLIC',
'OCITEST',
'ODS',
'ODSCOMMON',
'OE',
'OEMADM',
'OLAPDBA',
'OLAPSVR',
'OLAPSYS',
'OMWB_EMULATION',
'OPENSPIRIT',
'ORACACHE',
'ORAREGSYS',
'ORDPLUGINS',
'ORDSYS',
'ORACLE',
'OSE$HTTP$ADMIN',
'OSP22',
'OUTLN',
'OWA',
'OWA_PUBLIC',
'PANAMA',
'PATROL',
'PERFSTAT',
'PLSQL',
'PM',
'PO',
'PO7',
'PO8',
'PORTAL30',
'PORTAL30_DEMO',
'PORTAL30_PUBLIC',
'PORTAL30_SSO',
'PORTAL30_SSO_PS',
'PORTAL30_SSO_PUBLIC',
'POWERCARTUSER',
'PRIMARY',
'PUBSUB',
'QS',
'QS_ADM',
'QS_CB',
'QS_CBADM',
'QS_CS',
'QS_ES',
'QS_OS',
'QS_WS',
'RE',
'REP_MANAGER',
'REP_OWNER',
'REP_OWNER',
'REPADMIN',
'RMAIL',
'RMAN',
'SAMPLE',
'SAP',
'SCOTT',
'SDOS_ICSAP',
'SECDEMO',
'SH',
'SITEMINDER',
'SLIDE',
'STARTER',
'STRAT_USER',
'SWPRO',
'SWUSER',
'SYMPA',
'SYS',
'SYSADM',
'SYSMAN',
'SYSTEM',
'TAHITI',
'TDOS_ICSAP',
'TESTPILOT',
'TRACESVR',
'TRAVEL',
'TSDEV',
'TSUSER',
'TURBINE',
'ULTIMATE',
'USER',
'USER0',
'USER1',
'USER2',
'USER3',
'USER4',
'USER5',
'USER6',
'USER7',
'USER8',
'USER9',
'UTLBSTATU',
'VIDEOUSER',
'VIF_DEVELOPER',
'VIRUSER',
'VRR1',
'WEBCAL01',
'WEBDB',
'WEBREAD',
'WKSYS',
'WWW',
'WWWUSER',
'XPRT',
)
Where
owner not in
(
'ADAMS',
'ADLDEMO',
'ADMIN',
'APPLSYS',
'APPLYSYSPUB',
'APPS',
'AQ',
'AQDEMO',
'AQJAVA',
'AQUSER',
'AUDIOUSER',
'AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED',
'BC4J',
'BLAKE',
'CATALOG',
'CDEMO82',
'CDEMOCOR',
'CDEMORID',
'CDEMOUCB',
'CENTRA',
'CIDS',
'CIS',
'CISINFO',
'CLARK',
'COMPANY',
'COMPIERE',
'CQSCHEMAUSER',
'CSMIG',
'CTXDEMO',
'CTXSYS',
'DBA',
'DBI',
'DBSNMP',
'DEMO',
'DEMO8',
'DEMO9',
'DES',
'EJSADMIN',
'EMP',
'ESTOREUSER',
'EVENT',
'FINANCE',
'FND',
'FROSTY',
'GPFD',
'GPLD',
'HCPARK',
'HLW',
'HR',
'IMAGEUSER',
'IMEDIA',
'JMUSER',
'JONES',
'LBACSYS',
'LIBRARIAN',
'MASTER',
'MDSYS',
'MFG',
'MIGRATE',
'MILLER',
'MMO2',
'MODTEST',
'MOREAU',
'MTS_USER',
'MTSSYS',
'MXAGENT',
'NAMES',
'OAS_PUBLIC',
'OCITEST',
'ODS',
'ODSCOMMON',
'OE',
'OEMADM',
'OLAPDBA',
'OLAPSVR',
'OLAPSYS',
'OMWB_EMULATION',
'OPENSPIRIT',
'ORACACHE',
'ORAREGSYS',
'ORDPLUGINS',
'ORDSYS',
'ORACLE',
'OSE$HTTP$ADMIN',
'OSP22',
'OUTLN',
'OWA',
'OWA_PUBLIC',
'PANAMA',
'PATROL',
'PERFSTAT',
'PLSQL',
'PM',
'PO',
'PO7',
'PO8',
'PORTAL30',
'PORTAL30_DEMO',
'PORTAL30_PUBLIC',
'PORTAL30_SSO',
'PORTAL30_SSO_PS',
'PORTAL30_SSO_PUBLIC',
'POWERCARTUSER',
'PRIMARY',
'PUBSUB',
'QS',
'QS_ADM',
'QS_CB',
'QS_CBADM',
'QS_CS',
'QS_ES',
'QS_OS',
'QS_WS',
'RE',
'REP_MANAGER',
'REP_OWNER',
'REP_OWNER',
'REPADMIN',
'RMAIL',
'RMAN',
'SAMPLE',
'SAP',
'SCOTT',
'SDOS_ICSAP',
'SECDEMO',
'SH',
'SITEMINDER',
'SLIDE',
'STARTER',
'STRAT_USER',
'SWPRO',
'SWUSER',
'SYMPA',
'SYS',
'SYSADM',
'SYSMAN',
'SYSTEM',
'TAHITI',
'TDOS_ICSAP',
'TESTPILOT',
'TRACESVR',
'TRAVEL',
'TSDEV',
'TSUSER',
'TURBINE',
'ULTIMATE',
'USER',
'USER0',
'USER1',
'USER2',
'USER3',
'USER4',
'USER5',
'USER6',
'USER7',
'USER8',
'USER9',
'UTLBSTATU',
'VIDEOUSER',
'VIF_DEVELOPER',
'VIRUSER',
'VRR1',
'WEBCAL01',
'WEBDB',
'WEBREAD',
'WKSYS',
'WWW',
'WWWUSER',
'XPRT',
)
Saturday 24 September 2011
Friday 23 September 2011
Oracle Index metadata for given table
Index metadata for given table .... create index statement for given table ORCL\sys> !cat indx_meta.sql set heading off set feedback off set verify off prompt set linesize 200 prompt set long 2000 select 'select dbms_metadata.get_ddl(' || '''TABLE'',' || '''' ||table_name||''',' || '''' || owner||''') from dual ;' from dba_tables where table_name ='&Table_name' ; set verify on set heading on set feedback on ----- out put ORCL\sys> @indx_meta set linesize 200 set long 2000 Enter value for table_name: USR_SITES select dbms_metadata.get_ddl('TABLE','USR_SITES','PROD1') from dual ; out put of the script select dbms_metadata.get_ddl('TABLE','USR_SITES','CPROD1') from dual ; DBMS_METADATA.GET_DDL('TABLE','USER_SITES','CCCPROD1') -------------------------------------------------------------------------------- CREATE TABLE "PROD1"."USR_SITES" ( "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE, "SITE_NO" VARCHAR2(12) NOT NULL ENABLE, "SITE_DATE_ADDED" DATE NOT NULL ENABLE, "TANDC_DATE_ACCEPTED" DATE, CONSTRAINT "USR_SITES_USPK1" PRIMARY KEY ("USER_OBJECT_ID", "SITE_NO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DM_CCCPROD1_DOCBASE" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PROD1" 1 row selected. === or SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) FROM DBA_indexes WHERE TABLE_NAME='USR_SITES' / DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) -------------------------------------------------------------------------------- CREATE UNIQUE INDEX "PROD1"."USR_SITES_USPK1" ON "PROD1"."USER_SITES" ( "USER_OBJECT_ID", "SITE_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PROD1" 1 row selected. ========= metadata for other objects !!!!!!!!!!!!!!!!!! Explanation of the script if you want to print ' in sqlplus then ORCL\sys>; select ' '' ' from dual; ''' --- ' why four ' for one ' ??? if you want to print x the wt will be sqlplus apt-lnxtst-01:ORCL\sys> select 'x' from dual; ' - x replace x with ' ORCL\sys> select ''' from dual; SELECT 'select dbms_metadata.get_ddl(' || ',' || ' ) FROM dual ;' FROM dba_indexes where rownum SELECT 'select dbms_metadata.get_ddl(' || ',' || ' ) FROM dual ;' FROM dba_indexes where rownum SELECT 'select dbms_metadata.get_ddl(' || '''index'''||',' ||index_name|| ' ) FROM dual ;' FROM dba_indexes where rownum SELECT 'select dbms_metadata.get_ddl(' || '''index'''||',' || ' ) FROM dual ;' FROM dba_indexes where rownum select ''' ) FROM dual ;' from dual; ==================================================================================================
Oracle Metadata !!!!
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end; / define vowner='SCOTT'; ----<<<<<<<<<< Change owner col view1 for a100 select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' view1 from dba_OBJECTS where object_type='INDEX' and owner='&vowner' ; col TABLE1 for a100 select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TABLE1 from dba_OBJECTS where object_type='TABLE' and owner='&vowner' ; col PROCEDURE1 for a100 select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' PROCEDURE1 from dba_OBJECTS where object_type='PROCEDURE' and owner='&vowner' ; col FUNCTION1 for a100 select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' FUNCTION1 from dba_OBJECTS where object_type='FUNCTION' and owner='&vowner' ; col TRIGGER1 for a100 select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TRIGGER1 from dba_OBJECTS where object_type='TRIGGER' and owner='&vowner' ; col VIEW1 for a100 select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' VIEW1 from dba_OBJECTS where object_type='VIEW' and owner='&vowner' ; col CONSTRAINT1 for a100 select 'select dbms_metadata.get_ddl(''CONSTRAINT'',''' || CONSTRAINT_NAME || ''',''' || owner|| ''') from dual;' CONSTRAINT1 from dba_constraints where 1=1 and owner='&vowner' ; ========================================================================================= With created date !! alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss'; set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end; / select '/*' || created || '*/' || dbms_metadata.get_ddl('INDEX',object_name, owner) from dba_objects where object_type = 'INDEX' and object_name='XXXX' --- <<< Index name order by created, object_name;
========= SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner) FROM all_indexes WHERE 1=1 --and owner = UPPER('&1') AND table_name = 'XXXX';
View Metadataset long 200000 pages 0 lines 150 select dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) from all_views u where 1=1 and owner IN ('SYS') --and owner IN ('ANUJ') and view_name='ALL_IND_STATISTICS' order by owner,view_name ; Or select dbms_metadata.get_ddl('VIEW', 'ALL_IND_STATISTICS') stmt from dual;
====================== Set pagesize 0 Long 90000 Set Feedback off Set echo off -- Spool Schema_ddl.sql SELECT Dbms_metadata.GET_DDL ('TABLE', u.table_name,u.owner) from Dba_tables u; SELECT Dbms_metadata.GET_DDL ('VIEW', u.view_name,u.owner) from Dba_views u; SELECT Dbms_metadata.GET_DDL ('INDEX', u.index_name,u.owner) from Dba_indexes u; Select Dbms_metadata.get_ddl ('PROCEDURE', U.object_name, U.owner) from dba_objects u where U.object_type = 'PROCEDURE'; Select Dbms_metadata.get_ddl ('FUNCTION', U.object_name, U.owner) from dba_objects u where U.object_type = 'FUNCTION';
Thursday 22 September 2011
Oracle user bash_profile file
.bash_profile on linux bash_profile .profile on solaris profile on solaris !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! on linux oracle@apt-amd-02:~> cat .bash_profile export ORACLE_BASE=/opt/app/oracle #export ORACLE_HOME=/opt/app/oracle/product/10.2 export ORACLE_HOME=/opt/app/oracle/product/11.2 export TNS_ADMIN=/opt/app/oracle/product/11.2/network/admin # export ORACLE_HOME=/opt/app/oracle/product/app10.2 export ORACLE_SID=orcl export ORACLE_HOME=/opt/oracle/product/10.2 export ORACLE_SID=orcl export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_SID=orcl export PATH=$ORACLE_HOME/bin:$PATH:. export LIBXCB_ALLOW_SLOPPY_LOCK=1 export TZ=GMT export GGATE=/opt/app/oracle/product/gg export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u01/app/oracle/product/gg === on solaris -bash-3.00$ cat .bashrc map! A map! B map! ^[OD ^[ map! ^[OC ^[ll alias aaa='sqlplus / as sysdba' # prompt before overwrite alias alert='tail -100 /opt/oracle/admin/db/bdump/alert_db.log|more' alias rma='rm -i' # prompt before overwrite alias cpa='cp -i' # alias mva='mv -i' alias arch1='cd /opt/oracle/admin/db/arch' alias spfile='cd /opt/oracle/product/10.2/dbs' # /opt/oracle/admin/db/arch alias sid='env|grep ORACLE_SID' alias aalert='vi + $(printf "set heading off pages 0 feedback off select value from v\$parameter where name='\''background_dump_dest'\'';"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log' alias xalert='/usr/bin/vi + $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log' -bash-3.00$ cat .profile # This is the default standard profile provided to a user. # They are expected to edit it to meet their own needs. MAIL=/usr/mail/${LOGNAME:?} ORACLE_BASE=/opt/oracle; export ORACLE_BASE ORACLE_HOME=${ORACLE_BASE}/product/10.2; export ORACLE_HOME ORACLE_SID=db; export ORACLE_SID PATH=/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin:$ORACLE_HOME/jdk:$ORACLE_HOME/perl5:$PATH; export PATH TMPDIR=/var/tmp TERM=vt220 ORACLE_TERM=vt220 UMASK=022 EDITOR=vi; export EDITOR NLS_LANG="ENGLISH_UNITED KINGDOM.UTF8"; export NLS_LANG # Use the following line for import/export only #NLS_LANG="ENGLISH_UNITED KINGDOM.AL32UTF8"; export NLS_LANG LC_ALL=en_US.UTF-8; export LC_ALL ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33 LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/ctx/bin:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH . \.brc ========== export ORACLE_PDB_SID=ORCLPDB <<<<< change based on requirement ORACLE_SID_PDB - Connect as SYSDBA without password into a PDB directly sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 25 08:00:17 2021 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 ORCLPDB READ WRITE NO ======================================================================================= stty erase ^? stty erase ^H cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=prod ## export ORACLE_SID=ORCL RAC=NO export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 export ORACLE_BASE=/u01/app/oracle ## based on your requirement !!!! export EDITOR=vi PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib SORACLE_SID=`echo $ORACLE_SID | tr '[:upper:]' '[:lower:]'` if [ $RAC = 'NO' ] ; then SDBNAME=$SORACLE_SID ORACLE_UNQNAME=$ORACLE_SID else SDBNAME=`echo $SORACLE_SID | sed s/.$//` ORACLE_UNQNAME=`echo $ORACLE_SID | sed s/.$//` fi export ORACLE_UNQNAME alias cdo='cd $ORACLE_HOME' alias cdb='cd $ORACLE_BASE' alias bdump='cd $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace' alias udump='cd $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace' alias alert='tail -f -n100 $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias sqlme='sqlplus / as sysdba' alias home='cd $ORACLE_HOME' alias db='export ORACLE_SID=orcl' alias alert_db='tail -100f /u01/oracle/11.2.0/diag/rdbms/testdb/testdb/trace/alert_testdb.log'
## End ***************************************************
cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMOUT= export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 #export ORACLE_SID= PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin alias DBV='cd /u01/app/dbvisit/dbvisit/standby' export PATH cat /etc/oratab # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMOUT= export ORACLE_BASE=/u01/app/oracle #export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 export ORACLE_SID=ibrac1 export GG_HOME=/dumps/gghome_capture export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME:$PATH:$HOME/.local/bin:$HOME/bin export PATH echo " -MGMTDB /u01/app/12.2.0/grid " echo " +ASM1 /u01/app/12.2.0/grid " echo " ibrac1 /u01/app/oracle/product/12.2.0/dbhome_1 " echo " capture /u01/app/oracle/product/12.1.0/dbhome_1 " echo " " echo " " echo " " echo " " EDITOR=vi SQLPATH=/home/oracle/dba export PATH EDITOR SQLPATH export ORACLE_PATH=/home/oracle/dba/ setsid () { unset ORATAB unset ORACLE_BASE unset ORACLE_HOME unset ORACLE_SID ORACLE_BASE=/u01/app/oracle ORATAB=/etc/oratab if tty -s then if [ -f $ORATAB ] then line_count=`cat $ORATAB | grep -v ^# | sed 's/:.*//' | wc -l` # check that the oratab file has some contents if [ $line_count -ge 1 ] then sid_selected=0 while [ $sid_selected -eq 0 ] do sid_available=0 for i in `cat $ORATAB | grep -v ^# | sed 's/:.*//'` do sid_available=`expr $sid_available + 1` sid[$sid_available]=$i done # get the required SID case ${SETSID_AUTO:-""} in YES) # Auto set use 1st entry sid_selected=1 ;; *) i=1 while [ $i -le $sid_available ] do printf "%2d- %10s\n" $i ${sid[$i]} i=`expr $i + 1` done echo "" echo "Select the Oracle SID with given number [1]:" read entry if [ -n "$entry" ] then entry=`echo "$entry" | sed "s/[a-z,A-Z]//g"` if [ -n "$entry" ] then entry=`expr $entry` if [ $entry -ge 1 ] && [ $entry -le $sid_available ] then sid_selected=$entry fi fi else sid_selected=1 fi esac done # # SET ORACLE_SID # export ORACLE_SID=${sid[$sid_selected]} echo "Your profile configured for $ORACLE_SID with information below:" echo "" export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin unset LD_LIBRARY_PATH ORAENV_ASK=NO . oraenv alias oh="cd ${ORACLE_HOME}" ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'` if [ $ora_version -lt 11 ] then alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump" alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log" alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log" fi if [ $ora_version -ge 11 ] then export ORACLE_UNQNAME=$ORACLE_SID alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log' alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log' alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace' alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace' fi alias pmon='ps -fu oracle | grep pmon | grep -v grep' alias tns='ps -fu oracle | grep tns | grep -v grep' alias oradiag='cd $ORACLE_BASE/diag/' alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/' unset ORAENV_ASK echo # #GIVE MESSAGE # else echo "No entries in $ORATAB. no environment set" fi fi fi } setsid showsid() { echo "" echo "ORACLE_SID=$ORACLE_SID" echo "ORACLE_BASE=$ORACLE_BASE" echo "ORACLE_HOME=$ORACLE_HOME" echo "alias oh=cd ${ORACLE_HOME}" echo "alias pmon='ps -fu oracle | grep pmon | grep -v grep'" echo "alias tns='ps -fu oracle | grep tns | grep -v grep'" ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'` if [ $ora_version -ge 11 ] then echo "alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'" echo "alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'" echo "alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'" echo "alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'" echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'" echo "alias oradiag='cd $ORACLE_BASE/diag/'" fi if [ $ora_version -lt 11 ] then echo "alias bdump=cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump" echo "alias talert=tail -f \"/u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\"" echo "alias valert=\"view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\"" echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'" fi echo "" } showsid sqlplus / as sysdba
=============================[oracle@wcp12cr2 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export TMOUT= export ORACLE_BASE=/oracle/db export ORACLE_HOME=/oracle/db/ohome export ORACLE_SID=orcl export GG_HOME=/dumps/gghome_capture export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME:$PATH:$HOME/.local/bin:$HOME/bin export PATH echo " orcl /oracle/db/ohome " echo " " echo " " echo " " echo " " EDITOR=vi SQLPATH=/home/oracle/dba export PATH EDITOR SQLPATH export ORACLE_PATH=/home/oracle/dba/ setsid () { unset ORATAB unset ORACLE_BASE unset ORACLE_HOME unset ORACLE_SID ORACLE_BASE=/u01/app/oracle ORATAB=/etc/oratab if tty -s then if [ -f $ORATAB ] then line_count=`cat $ORATAB | grep -v ^# | sed 's/:.*//' | wc -l` # check that the oratab file has some contents if [ $line_count -ge 1 ] then sid_selected=0 while [ $sid_selected -eq 0 ] do sid_available=0 for i in `cat $ORATAB | grep -v ^# | sed 's/:.*//'` do sid_available=`expr $sid_available + 1` sid[$sid_available]=$i done # get the required SID case ${SETSID_AUTO:-""} in YES) # Auto set use 1st entry sid_selected=1 ;; *) i=1 while [ $i -le $sid_available ] do printf "%2d- %10s\n" $i ${sid[$i]} i=`expr $i + 1` done echo "" echo "Select the Oracle SID with given number [1]:" read entry if [ -n "$entry" ] then entry=`echo "$entry" | sed "s/[a-z,A-Z]//g"` if [ -n "$entry" ] then entry=`expr $entry` if [ $entry -ge 1 ] && [ $entry -le $sid_available ] then sid_selected=$entry fi fi else sid_selected=1 fi esac done # # SET ORACLE_SID # export ORACLE_SID=${sid[$sid_selected]} echo "Your profile configured for $ORACLE_SID with information below:" echo "" export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin unset LD_LIBRARY_PATH ORAENV_ASK=NO . oraenv alias oh="cd ${ORACLE_HOME}" ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'` if [ $ora_version -lt 11 ] then alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump" alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log" alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log" fi if [ $ora_version -ge 11 ] then export ORACLE_UNQNAME=$ORACLE_SID alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log' alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log' alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace' alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace' fi alias pmon='ps -fu oracle | grep pmon | grep -v grep' alias tns='ps -fu oracle | grep tns | grep -v grep' alias oradiag='cd $ORACLE_BASE/diag/' alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/' unset ORAENV_ASK echo # #GIVE MESSAGE # else echo "No entries in $ORATAB. no environment set" fi fi fi } stty erase "^?" stty erase "^H" [oracle@wcp12cr2 ~]$
[oracle@wcp12cr2 ~]$ alias alias l.='ls -d .* --color=auto' alias ll='ls -l --color=auto' alias ls='ls --color=auto' alias sys='sqlplus "/ as sysdba"' alias vi='vim' alias vmctl='/oracle/scripts/vmctl.sh' alias which='alias | /usr/bin/which --tty-only --read-alias --show-dot --show-tilde' [oracle@wcp12cr2 ~]$
Wednesday 21 September 2011
Oracle Active undo segment from system datafile for recovery
Oracle Active undo segment
Oracle Active rollback segment
oracle@amd-0:/opt/app/oracle/oradata/orcl> strings system01.dbf | grep _SYSSMU|cut -d $ -f 1 |sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU10_3550978943
_SYSSMU10_3904554333
_SYSSMU11_286947212
_SYSSMU12_3068564564
_SYSSMU13_2761193625
_SYSSMU1_3780397527
_SYSSMU14_2421411996
_SYSSMU15_1683924174
_SYSSMU16_2313212396
_SYSSMU17_2041439332
_SYSSMU1_783380902
_SYSSMU18_2800789714
_SYSSMU19_53723967
_SYSSMU20_3850939844
_SYSSMU2_2232571081
_SYSSMU2_3138176977
_SYSSMU3_1645411166
_SYSSMU3_2097677531
_SYSSMU4_1152005954
_SYSSMU4_870421980
_SYSSMU5_1527469038
_SYSSMU5_2525172762
_SYSSMU6_2443381498
_SYSSMU6_3753507049
_SYSSMU7_1260614213
_SYSSMU7_3286610060
_SYSSMU8_2012382730
_SYSSMU8_2806087761
_SYSSMU9_1424341975
_SYSSMU9_973944058
Edit Init file
# parameter undo_management and undo_tablespace
add this parameter :
UNDO_MANAGEMENT=MANUAL
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1,_SYSSMU2,_SYSSMU3, ...)
===========
- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE
SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;
* Now the database already startup with Manual undo management.
* Create new UNDO Tablespace
SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;
* Take offline the OLD Undo Tablespace :
SQL> alter tablespace OLD_UNDOTS offline;
* Take online the NEW Undo Tablespace :
SQL> alter tablespace NEW_UNDOTS ;
* Shutdown the database :
SQL> shutdown immediate;
* Edit the initMYDB.ora :
+ Remark the parameter :
- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE
+ Add and edit the parameter :
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=NEW_UNDOTS
* Startup the database :
SQL> startup
* The database will startup with the NEW Undo tablespace, change the default undo tablespace :
SQL> alter system set undo_tablespace=NEW_UNDOTS;
* Then we can drop the OLD Undo tablespace :
SQL> drop tablespace OLD_UNDOTS including contents and datafiles;
Oracle Active rollback segment
oracle@amd-0:/opt/app/oracle/oradata/orcl> strings system01.dbf | grep _SYSSMU|cut -d $ -f 1 |sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU10_3550978943
_SYSSMU10_3904554333
_SYSSMU11_286947212
_SYSSMU12_3068564564
_SYSSMU13_2761193625
_SYSSMU1_3780397527
_SYSSMU14_2421411996
_SYSSMU15_1683924174
_SYSSMU16_2313212396
_SYSSMU17_2041439332
_SYSSMU1_783380902
_SYSSMU18_2800789714
_SYSSMU19_53723967
_SYSSMU20_3850939844
_SYSSMU2_2232571081
_SYSSMU2_3138176977
_SYSSMU3_1645411166
_SYSSMU3_2097677531
_SYSSMU4_1152005954
_SYSSMU4_870421980
_SYSSMU5_1527469038
_SYSSMU5_2525172762
_SYSSMU6_2443381498
_SYSSMU6_3753507049
_SYSSMU7_1260614213
_SYSSMU7_3286610060
_SYSSMU8_2012382730
_SYSSMU8_2806087761
_SYSSMU9_1424341975
_SYSSMU9_973944058
Edit Init file
# parameter undo_management and undo_tablespace
add this parameter :
UNDO_MANAGEMENT=MANUAL
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1,_SYSSMU2,_SYSSMU3, ...)
===========
- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE
SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;
* Now the database already startup with Manual undo management.
* Create new UNDO Tablespace
SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;
* Take offline the OLD Undo Tablespace :
SQL> alter tablespace OLD_UNDOTS offline;
* Take online the NEW Undo Tablespace :
SQL> alter tablespace NEW_UNDOTS ;
* Shutdown the database :
SQL> shutdown immediate;
* Edit the initMYDB.ora :
+ Remark the parameter :
- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE
+ Add and edit the parameter :
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=NEW_UNDOTS
* Startup the database :
SQL> startup
* The database will startup with the NEW Undo tablespace, change the default undo tablespace :
SQL> alter system set undo_tablespace=NEW_UNDOTS;
* Then we can drop the OLD Undo tablespace :
SQL> drop tablespace OLD_UNDOTS including contents and datafiles;
Subscribe to:
Posts (Atom)
Oracle DBA
anuj blog Archive
- ▼ 2011 (362)