That’s Me
|
Oracle Certified Professional
|
|
7.3, 8i, 9i,10g, 11g
|
|
11i Apps DBA OCP
|
|
10g RAC OCE
|
|
Certified GoldenGate Implementation Specialist
|
|
10g OCM
|
|
11g OCM
|
Feedback
1,650,000 hits
Thanks A MILLION for your support!
Please send me your valuable feedback and suggestions
|
PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time”
from v$flashback_database_log;
PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;
PROMPT
PROMPT Flashback Area Usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
PROMPT
col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name Heading “Flashback Location” format a40
select name, round(space_limit/1048576),round(space_used/1048576)
from v$RECOVERY_FILE_DEST;
How Far Back Can We Flashback To (Time)?
Oldest Flashback Time
—————————–
05-jul-2009 22:53:07
How Far Back Can We Flashback To (SCN)?
OLDEST_FLASHBACK_SCN
—————————
15321928761
Flashback Area Usage
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
———— —————— ————————- —————
CONTROLFILE 0
Continue reading Script – Monitor Flashback Logs
– Listing of temp segments.–
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
– Temp segment usage per session.–
SELECT
Continue reading Script – Temporary tablespace usage
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
Note: the same query can be used to monitor RMAN backup status
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0
AND SOFAR != TOTALWORK
;
SID SERIAL# OPNAME SOFAR TOTALWORK COMPLETE
———- ———- ——————– ———- ———- ———-
604 13371 Table Scan 6311 24498
Continue reading Monitor long running operations using v$session_longops
Note: This query needs to be run on the Primary database.
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,’.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
);
DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
——– ———— ———— ———– ———— ——-
CPSPRD PRDU009N1 11213 11213 30-JUN/08:48 0
col o format a10
col u format a10
select osuser o, username u, sid,
segment_name s, substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
And substr(sa.sql_text,1,200) is not null
order by 3;
col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti ‘Active transactions’
select username, osuser,
t.start_time, r.name, t.used_ublk “ROLLB BLKS”,
decode(t.space, ‘YES’, ‘SPACE TX’,
decode(t.recursive, ‘YES’, ‘RECURSIVE TX’,
decode(t.noundo, ‘YES’, ‘NO UNDO TX’, t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
PROMPT Blocked and Blocker Sessions
col blocker_sid format 99999999999
col blocked_sid format 99999999999
col min_blocked format 99999999999
col request format 9999999
select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = ‘TX’) blocker
, v$lock blocked
where
blocked.type=’TX’
and blocked.block = 0
and blocked.id1 = blocker.id1;
prompt blocked objects from V$LOCK and SYS.OBJ$
set lines 132
col BLOCKED_OBJ format a35 trunc
select /*+ ORDERED */
l.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||’.'||o.NAME blocked_obj
from (select *
from v$lock
where type=’TM’
and sid in (select sid
from v$lock
where block!=0)) l
, sys.obj$ o
, sys.user$ u
where o.obj# = l.ID1
and o.OWNER# = u.user#
/
prompt blocked sessions from V$LOCK
select /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
from (select *
from v$lock
where block != 0
and type = ‘TX’) blocker
, v$lock blocked
where blocked.type=’TX’
and blocked.block = 0
and blocked.id1 = blocker.id1
/
prompt blokers session details from V$SESSION
set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc
SELECT sid
, serial#
, username
, osuser
, machine
FROM v$session
WHERE
Continue reading Locked Sessions and Locked Objects
select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1;
prompt
prompt “Morning ……….”
select to_char(first_time,’DD/MON’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’07′,1,0)),’000′)”07″,
to_char(sum(decode(to_char(first_time,’HH24′),’08′,1,0)),’000′)”08″,
to_char(sum(decode(to_char(first_time,’HH24′),’09′,1,0)),’000′)”09″,
to_char(sum(decode(to_char(first_time,’HH24′),’10′,1,0)),’000′)”10″,
to_char(sum(decode(to_char(first_time,’HH24′),’11′,1,0)),’000′)”11″,
to_char(sum(decode(to_char(first_time,’HH24′),’12′,1,0)),’000′)”12″,
to_char(sum(decode(to_char(first_time,’HH24′),’13′,1,0)),’000′)”13″,
to_char(sum(decode(to_char(first_time,’HH24′),’14′,1,0)),’000′)”14″,
to_char(sum(decode(to_char(first_time,’HH24′),’15′,1,0)),’000′)”15″,
to_char(sum(decode(to_char(first_time,’HH24′),’16′,1,0)),’000′)”16″,
to_char(sum(decode(to_char(first_time,’HH24′),’17′,1,0)),’000′)”17″,
to_char(sum(decode(to_char(first_time,’HH24′),’18′,1,0)),’000′)”18″
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) – 7
group by to_char(first_time,’DD/MON’);
prompt
prompt
Prompt “Evening ……..”
prompt
select to_char(first_time,’DD/MON’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’19′,1,0)),’000′)”19″,
to_char(sum(decode(to_char(first_time,’HH24′),’20′,1,0)),’000′)”20″,
to_char(sum(decode(to_char(first_time,’HH24′),’21′,1,0)),’000′)”21″,
to_char(sum(decode(to_char(first_time,’HH24′),’22′,1,0)),’000′)”22″,
to_char(sum(decode(to_char(first_time,’HH24′),’23′,1,0)),’000′)”23″,
to_char(sum(decode(to_char(first_time,’HH24′),’00′,1,0)),’000′) “00″,
to_char(sum(decode(to_char(first_time,’HH24′),’01′,1,0)),’000′)”01″,
to_char(sum(decode(to_char(first_time,’HH24′),’02′,1,0)),’000′)”02″,
to_char(sum(decode(to_char(first_time,’HH24′),’03′,1,0)),’000′)”03″,
to_char(sum(decode(to_char(first_time,’HH24′),’04′,1,0)),’000′)”04″,
to_char(sum(decode(to_char(first_time,’HH24′),’05′,1,0)),’000′)”05″,
to_char(sum(decode(to_char(first_time,’HH24′),’06′,1,0)),’000′)”06″
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) – 7
group by to_char(first_time,’DD/MON’);
|
That’s Us
|
|
24x7 Remote/Onsite DBA Support
|
|
Performance Auditing
|
|
Security Auditing
|
|
High Availability Solutions
|
|
Disaster Recovery
|
|
Database Upgrades & Migrations
|
|
Data Migration using GoldenGate
|
|
More about our services ...
|
|
Popular Posts