Search This Blog

Total Pageviews

Thursday 29 September 2011

ORA-01555, snapshot too old errors on transactions

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'
/

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;

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

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;

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',
)

Saturday 24 September 2011

Oracle RAC FAQ metalink 220970.1

RAC: Frequently Asked Questions [ID 220970.1]

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 Metadata
  set 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 DBA

anuj blog Archive