How to compile invalid objects???
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
1. Identifying Invalid Objects
2. The Manual Approach
3. Custom Script
4. DBMS_UTILITY.compile_schema
5. UTL_RECOMP
6. utlrp.sql and utlprp.sql
7. Magic Script
1. Identifying Invalid Objects:
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,object_type,object_name,status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.
2. The Manual Approach:
For small numbers of objects you may decide that a manual recompilation is sufficient.
The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations:
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]
Syntax
Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username
Name : Objects name
Example
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed
For more detail see below.
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.
3. Custom Script:
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects.
The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner, object_name,object_type,
DECODE(object_type, 'PACKAGE', 1,'PACKAGE BODY', 2, 2)
AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' cur_rec.object_type ' "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE';
ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type ' : ' cur_rec.owner ' : ' cur_rec.object_name);
END;
END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
4. DBMS_UTILITY.compile_schema:
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema.
Syntax
Exec dbms_utility.compile_schema ( schema,compile all)
Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)
The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
5. UTL_RECOMP:
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.
Their definitions are listed below:
PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below:
•schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
•threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
•flags - Used for internal diagnostics and testing only.
The following examples show how these procedures care used:
-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:
•Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
•The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
•The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
•Runnig DDL operations at the same time as this package may result in deadlocks.
6.utlrp.sql and utlprp.sql:
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
•0 - The level of parallelism is derived based on the CPU_COUNT parameter.
•1 - The recompilation is run serially, one object at a time.
•N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
7. Magic Script
Now last but not the least,below is the query which i use to compile the invalid objects at one go:
SELECT CASE object_type
WHEN 'PACKAGE' THEN
'ALTER 'object_type' 'owner'.'object_name' COMPILE;'
ELSE
'ALTER PACKAGE 'owner'.'object_name' COMPILE BODY;'
END
FROM dba_objects
WHERE STATUS = 'INVALID'
AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');
Hope this helps....
Monday, May 30, 2011
Thursday, May 26, 2011
Determing the optimal UNDO tablespace size
###############Determing the optimal UNDO tablespace size############################################3
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
set feedback off
set heading off
set lines 132
declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) *
g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) ||
'To optimize UNDO you have two choices :');
dbms_output.put_line('==================================================
==' || chr(10));
for rec1 in get_undo_stat loop
dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'
|| chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' || ltrim(TO_CHAR(rec1.c2/60,'999999'))
|| ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :'
|| chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||
' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' ||
TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/
select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
set feedback off
set heading off
set lines 132
declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) *
g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) ||
'To optimize UNDO you have two choices :');
dbms_output.put_line('==================================================
==' || chr(10));
for rec1 in get_undo_stat loop
dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'
|| chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||
TO_CHAR(rec1.c1,'999999') || ' MEGS');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION
(' || ltrim(TO_CHAR(rec1.c2/60,'999999'))
|| ' MINUTES)
',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :'
|| chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||
' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE
(' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS) ',65,'.') || ' : ' ||
TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/
select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;
oracle dba blogs
http://gavinsoorma.com/2009/06/list-all-tablespaces-with-free-space-10/
http://askdba.org/
http://sysdbaonline.com/
http://jonathanlewis.wordpress.com/all-postings/
http://askdba.org/
http://sysdbaonline.com/
http://jonathanlewis.wordpress.com/all-postings/
Monitor the Flashback area space usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
SELECT * FROM V$RECOVERY_FILE_DEST;
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name format a40
select name, round(space_limit/1048576),round(space_used/1048576)
from v$RECOVERY_FILE_DEST;
SELECT * FROM V$RECOVERY_FILE_DEST;
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name format a40
select name, round(space_limit/1048576),round(space_used/1048576)
from v$RECOVERY_FILE_DEST;
List all tablespaces with free space < 10%
set pagesize 300
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Identify OS process ID based on database SID
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
Identify database SID based on OS Process ID
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
Identify database idle sessions
set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;
Locked Sessions and Locked Objects
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 sid IN (select sid
from v$lock
where block != 0
and type = 'TX')
/
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 sid IN (select sid
from v$lock
where block != 0
and type = 'TX')
/
Identify active transactions in undo and rollback segments
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
/
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
/
Recreate online redo logs and standby logs
#############Recreate online redo logs and standby logs#####################
In a DATAGUARD ENVIRONMENT, If online redologs are resized, the standby logs also need to be resized to match the online redo logs in the Primary database.
STEPS TO ACHIEVE THIS -
1. Drop and recreate online redologs on the PRIMARY site.
2. On primary site, create the standby controlfile -
alter database create standby controlfile as ‘/tmp/standby01.ctl’;
3. Copy the standby control file to standby site…
4. Cancel recovery, shutdown immediate the standby database.
5. Startup and mount the standby database -using the new standby controlfile.
5. Defer recovery until standby logs are created..…do not start managed recovery.
6. Add the standby logs with same size as Primary online redologs.
7. The online logs on the standby side will be created at switchover time so no need to do anything now.
8. Start recovery on standby site..
To recreate ONLY the STANDBY logs only…follow from point 2 above.
In a DATAGUARD ENVIRONMENT, If online redologs are resized, the standby logs also need to be resized to match the online redo logs in the Primary database.
STEPS TO ACHIEVE THIS -
1. Drop and recreate online redologs on the PRIMARY site.
2. On primary site, create the standby controlfile -
alter database create standby controlfile as ‘/tmp/standby01.ctl’;
3. Copy the standby control file to standby site…
4. Cancel recovery, shutdown immediate the standby database.
5. Startup and mount the standby database -using the new standby controlfile.
5. Defer recovery until standby logs are created..…do not start managed recovery.
6. Add the standby logs with same size as Primary online redologs.
7. The online logs on the standby side will be created at switchover time so no need to do anything now.
8. Start recovery on standby site..
To recreate ONLY the STANDBY logs only…follow from point 2 above.
Export and Import using unix pipes and compression
EXPORT AND IMPORT USING UNIX PIPES.
Sometimes, the space on disk may not be enough to hold a full export dump if uncompressed.
EXAMPLE – export schema ARJUN from PROD database and import into DEV database.
To avoid space running out, unix pipes and compression can be used.
EXPORT IN PROD DATABASE
cd /u02/oradata/export
CREATE UNIX PIPE IN THIS AREA – WHERE THE EXPORT DUMP WILL BE WRITTEN TO.
mknod pipe p
CREATE PAR FILE -
parfile is arjun.par
vi arjun.par
buffer=2097152
recordlength=65535
consistent=y
owner=arjun
log=/u02/oradata/export/arjun.log
file=/u02/oradata/export/pipe
Now export schema ARJUN.
1. nohup gzip -c /u02/oradata/export/arjun.dmp.gz &
Immediately enter next command -
2. nohup exp \’/ as sysdba\’ parfile=/u02/oradata/export/arjun.par &
Export of ARJUN schema completes – compressed dump arjun.dmp.gz created.
ftp or copy the dump file arjun.dmp.gz to the DEV database box.
IMPORT IN DEV DATABASE – – Presume same directory structure exists on DEV box.
Create UNIX PIPE in area where dump is copied to in DEV box.
cd /u02/oradata/export
mknod import_pipe p
Create import parfile – called imp.par
vi imp.par
fromuser=arjun
touser=arjun
commit=y
buffer=2097152
ignore=y
file=/u02/oradata/export/import_pipe
log=/u02/oradata/export//imp_arjun.log
Enter commands -
1. nohup gzip -dc import_pipe &
Immediately enter next command -
2. nohup imp \’/ as sysdba\’ parfile=/u02/oradata/export/imp.par
Check the logs for output of import.
Sometimes, the space on disk may not be enough to hold a full export dump if uncompressed.
EXAMPLE – export schema ARJUN from PROD database and import into DEV database.
To avoid space running out, unix pipes and compression can be used.
EXPORT IN PROD DATABASE
cd /u02/oradata/export
CREATE UNIX PIPE IN THIS AREA – WHERE THE EXPORT DUMP WILL BE WRITTEN TO.
mknod pipe p
CREATE PAR FILE -
parfile is arjun.par
vi arjun.par
buffer=2097152
recordlength=65535
consistent=y
owner=arjun
log=/u02/oradata/export/arjun.log
file=/u02/oradata/export/pipe
Now export schema ARJUN.
1. nohup gzip -c /u02/oradata/export/arjun.dmp.gz &
Immediately enter next command -
2. nohup exp \’/ as sysdba\’ parfile=/u02/oradata/export/arjun.par &
Export of ARJUN schema completes – compressed dump arjun.dmp.gz created.
ftp or copy the dump file arjun.dmp.gz to the DEV database box.
IMPORT IN DEV DATABASE – – Presume same directory structure exists on DEV box.
Create UNIX PIPE in area where dump is copied to in DEV box.
cd /u02/oradata/export
mknod import_pipe p
Create import parfile – called imp.par
vi imp.par
fromuser=arjun
touser=arjun
commit=y
buffer=2097152
ignore=y
file=/u02/oradata/export/import_pipe
log=/u02/oradata/export//imp_arjun.log
Enter commands -
1. nohup gzip -dc import_pipe &
Immediately enter next command -
2. nohup imp \’/ as sysdba\’ parfile=/u02/oradata/export/imp.par
Check the logs for output of import.
Monitor Data Guard Log Shipping
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'
);
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'
);
Monitor long running operations using v$session_longops
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;
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
Data Guard switchover checklist
In some establishments, Dataguard switchovers are manual -
Please perform these pre-requisite checks before undertaking a switchover to primary.
1. ON STANDBY SITE:
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY…………….PROCEED.
If you receive a reply like the one below, then do not proceed- you are most probably firing the sql command in the primary site:
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY…………….do not proceed if this message is received …in this case you are most probably on the primary site..
Again on STANDBY SITE:
SQL> select name,value from v$parameter where name in (‘log_archive_dest_1′,’log_archive_dest_state_1′, ‘log_archive_dest_2′,’log_archive_dest_state_2′);
NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_1
LOCATION=/opt/oracle/opsdb9i/arch
log_archive_dest_2
SERVICE=opsdb9i_blade07 lgwr sync affirm nodelay……….Make sure lgwr and not arch is mentioned here, otherwise new primary database will not open after switchover ( if the protection_mode is Maximum availability).
log_archive_dest_state_1
ENABLE
log_archive_dest_state_2
DEFER …………..DO NOT PROCEED.
While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alert_log , but make sure it is set to ENABLE before starting a switchover.
This command can be issued to convert it to ENABLE
sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;
Now check again…
SQL>NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_state_2
ENABLE…………………………………PROCEED .
SQL> select distinct status from v$datafile;
STATUS
——-
ONLINE
SYSTEM
If any file/files are in RECOVER status, DO NOT PROCEED with the switchover…
SQL> select distinct open_mode from v$database;
OPEN_MODE
———-
MOUNTED………This is the correct response ..PROCEED with switchover.
If the STANDBY database has been opened in READ ONLY mode, you will receive the following message…
SQL> select distinct open_mode from v$database;
OPEN_MODE
———-
READ ONLY……………….DO NOT PROCEED with switchover.
To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…
On PRIMARY site
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;
LAST_LOG_GENERATED
——————
14
Now on STANDBY site.
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;
LAST_LOG_APPLIED
—————-
14
SAFE TO PROCEED.
Please perform these pre-requisite checks before undertaking a switchover to primary.
1. ON STANDBY SITE:
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY…………….PROCEED.
If you receive a reply like the one below, then do not proceed- you are most probably firing the sql command in the primary site:
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY…………….do not proceed if this message is received …in this case you are most probably on the primary site..
Again on STANDBY SITE:
SQL> select name,value from v$parameter where name in (‘log_archive_dest_1′,’log_archive_dest_state_1′, ‘log_archive_dest_2′,’log_archive_dest_state_2′);
NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_1
LOCATION=/opt/oracle/opsdb9i/arch
log_archive_dest_2
SERVICE=opsdb9i_blade07 lgwr sync affirm nodelay……….Make sure lgwr and not arch is mentioned here, otherwise new primary database will not open after switchover ( if the protection_mode is Maximum availability).
log_archive_dest_state_1
ENABLE
log_archive_dest_state_2
DEFER …………..DO NOT PROCEED.
While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alert_log , but make sure it is set to ENABLE before starting a switchover.
This command can be issued to convert it to ENABLE
sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;
Now check again…
SQL>NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_state_2
ENABLE…………………………………PROCEED .
SQL> select distinct status from v$datafile;
STATUS
——-
ONLINE
SYSTEM
If any file/files are in RECOVER status, DO NOT PROCEED with the switchover…
SQL> select distinct open_mode from v$database;
OPEN_MODE
———-
MOUNTED………This is the correct response ..PROCEED with switchover.
If the STANDBY database has been opened in READ ONLY mode, you will receive the following message…
SQL> select distinct open_mode from v$database;
OPEN_MODE
———-
READ ONLY……………….DO NOT PROCEED with switchover.
To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…
On PRIMARY site
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;
LAST_LOG_GENERATED
——————
14
Now on STANDBY site.
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;
LAST_LOG_APPLIED
—————-
14
SAFE TO PROCEED.
List Objects being accessed by a particular SID
set pagesize 300
select sid,serial#,username,status from v$session
WHERE USERNAME IS NOT NULL
order by STATUS DESC;
col sid format 999
col owner format a10
col object format a20
col type format a10
set linesize 300
/* ENTER THE SID TO CHECK */
select sid,owner,object,type
from v$access where sid = &sid
order by owner,type
;
select sid,serial#,username,status from v$session
WHERE USERNAME IS NOT NULL
order by STATUS DESC;
col sid format 999
col owner format a10
col object format a20
col type format a10
set linesize 300
/* ENTER THE SID TO CHECK */
select sid,owner,object,type
from v$access where sid = &sid
order by owner,type
;
List all recent DDL modifications
set pagesize 200 colsep ' '
col Owner format a20
col Object_Name format a30
col Last_DDL format a20
break on Owner
select Owner, Object_type
Object_Name,
Timestamp Last_DDL
from DBA_OBJECTS
where SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1
order by Owner, Object_Name
/
col Owner format a20
col Object_Name format a30
col Last_DDL format a20
break on Owner
select Owner, Object_type
Object_Name,
Timestamp Last_DDL
from DBA_OBJECTS
where SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1
order by Owner, Object_Name
/
Query the RMAN catalog to list backup completion status
####Note – run this query connected as the owner of the RMAN catalog####
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
Friday, May 20, 2011
Identifying Oracle Tables with Migrated/Chained Rows
The following code generated the report on migrated/chained rows. Note that the use of this script is predicated on the use of Oracle’s ANALYZE command to populate the chain_cnt and num_rows columns of the dba_tables data dictionary view.
spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;
spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by chain_cnt desc
;
Killing an Oracle job at the OS level
1. Find the job we what to kill and the session associated with the job
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from ( select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE,dj.THIS_SEC,dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL,
dj.WHAT from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
2. Mark the job as Broken
begin
DBMS_JOB.BROKEN(job#,TRUE);
commit;
end;
NOTE: After executing this command the job is still running.
3. Kill the Oracle Session
ALTER SYSTEM KILL SESSION 'sid, serial#';
NOTE: In many situations the session is marked 'KILLED' but is not killed.
4. Kill the O/S Process
For UNIX:
kill -9 spid
For Windows at the DOS Prompt:
orakill sid spid
Note: The article ( named Killing an Oracle job at the OS level ) was taken from www.in-oracle.com.
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from ( select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE,dj.THIS_SEC,dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL,
dj.WHAT from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
2. Mark the job as Broken
begin
DBMS_JOB.BROKEN(job#,TRUE);
commit;
end;
NOTE: After executing this command the job is still running.
3. Kill the Oracle Session
ALTER SYSTEM KILL SESSION 'sid, serial#';
NOTE: In many situations the session is marked 'KILLED' but is not killed.
4. Kill the O/S Process
For UNIX:
kill -9 spid
For Windows at the DOS Prompt:
orakill sid spid
Note: The article ( named Killing an Oracle job at the OS level ) was taken from www.in-oracle.com.
Thursday, May 19, 2011
Export Script
==========================================Export Script========================================================
[oracle@xxxxx:AIT] $ more exp_AIT.par
CONSISTENT=Y
FULL=Y
LOG=/u05/oradata/AIL_data/daily_exp_AIT/export/exp_ait.log
FILE=/u05/oradata/AIL_data/daily_exp_AIT/export/exp_ait.dmp
STATISTICS=NONE
indexes=y
constraints=y
compress=no
[oracle@xxxxx:AIT] $ more exp_AIT.par
CONSISTENT=Y
FULL=Y
LOG=/u05/oradata/AIL_data/daily_exp_AIT/export/exp_ait.log
FILE=/u05/oradata/AIL_data/daily_exp_AIT/export/exp_ait.dmp
STATISTICS=NONE
indexes=y
constraints=y
compress=no
health_check_script
==================================================health_check_script===============================================
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool healthcheck.lst
select tablespace_name, sum(bytes/1024/1024) as "FREE MB" from dba_free_space group by tablespace_name;
select tablespace_name, sum(bytes/1024/1024) as "USED MB" from dba_data_files group by tablespace_name;
spool off
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool healthcheck.lst
select tablespace_name, sum(bytes/1024/1024) as "FREE MB" from dba_free_space group by tablespace_name;
select tablespace_name, sum(bytes/1024/1024) as "USED MB" from dba_data_files group by tablespace_name;
spool off
QUERY TO LAST ANALYZE OF A PARTICULAR TABLE FROM A SCHEMA
QUERY TO LAST ANALYZE OF A PARTICULAR TABLE FROM A SCHEMA
------------------------------------------------------------------------------------------------------------
SQL> select distinct trunc(last_analyzed) from dba_tables where owner ='' ;
------------------------------------------------------------------------------------------------------------
SQL> select distinct trunc(last_analyzed) from dba_tables where owner ='
CORRUPTED BLOCK DETECTION
CORRUPTED BLOCK DETECTION
-----------------------------------------------------
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE WHERE BLOCK_ID=;
SQL> DESC V$DATABASE_BLOCK_CORRUPTION;
-----------------------------------------------------
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE WHERE BLOCK_ID=
SQL> DESC V$DATABASE_BLOCK_CORRUPTION;
TO FIND OUT THE DATABASES RUNNING ON WINDOWS MACHINE
USE OF TASK LIST COMMAND – TO FIND OUT THE DATABASES RUNNING ON WINDOWS MACHINE
TASKLIST /SVC /FI "SERVICES eq Oracle*"
TASKLIST /SVC /FI "SERVICES eq Oracle*"
IDENTIFY THE INVALID SESSIONS AT DATABASE LEVEL
TO IDENTIFY THE INVALID SESSIONS AT DATABASE LEVEL
------------------------------------------------------------------------------------------
SQL > select 'alter system kill session'||''''||sid||','||serial#||''''||';' from v$session where status like 'INACTIVE';
------------------------------------------------------------------------------------------
SQL > select 'alter system kill session'||''''||sid||','||serial#||''''||';' from v$session where status like 'INACTIVE';
SCRIPT TO FIND THE LOCKS IN THE DATABASE
CRIPT TO FIND THE LOCKS IN THE DATABASE
------------------------------------------------------------------------
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
------------------------------------------------------------------------
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
SCRIPT TO FIND OUT THE TOP SQL STATEMENTS IN YOUR DATABASE
SCRIPT TO FIND OUT THE TOP SQL STATEMENTS IN YOUR DATABASE
----------------------------------------------------------------------------------------------------------
SELECT b.username username, a.disk_reads phyreads, a.executions noexec,
a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions) rds_ex_ratio,
a.command_type, a.sql_text sqlqry
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 10000
ORDER BY a.disk_reads DESC;
----------------------------------------------------------------------------------------------------------
SELECT b.username username, a.disk_reads phyreads, a.executions noexec,
a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions) rds_ex_ratio,
a.command_type, a.sql_text sqlqry
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 10000
ORDER BY a.disk_reads DESC;
Viewing Memory Use for Each User Session
Viewing Memory Use for Each User Session
The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use for each session:
SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';
USERNAME Current UGA memory
------------------------------ ---------------------------------------------
18636bytes
17464bytes
19180bytes
18364bytes
39384bytes
35292bytes
17696bytes
15868bytes
USERSCOTT 42244bytes
SYS 98196bytes
SYSTEM 30648bytes
11 rows selected.
To see the maximum UGA memory ever allocated to each session since the instance started,
replace 'session uga memory' in the preceding query with 'session uga memory max'.
The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use for each session:
SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';
USERNAME Current UGA memory
------------------------------ ---------------------------------------------
18636bytes
17464bytes
19180bytes
18364bytes
39384bytes
35292bytes
17696bytes
15868bytes
USERSCOTT 42244bytes
SYS 98196bytes
SYSTEM 30648bytes
11 rows selected.
To see the maximum UGA memory ever allocated to each session since the instance started,
replace 'session uga memory' in the preceding query with 'session uga memory max'.
Checking the load on the database
Script for checking the load on the database(FISLIVE)
--------------------------------------------------------
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
--------------------------------------------------------
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
Tablespace resize - (To reduce the size)
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
To identify blockers
col "SID" format a10
col "Lock Type" format a15
col "Mode Held" format a15
col "Blocking?" format a15
SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",
SUBSTR(lock_type,1,15) "Lock Type",
SUBSTR(mode_held,1,15) "Mode Held",
SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
where blocking_others='Blocking';
no rows selected
SQL>
SQL> select sid, serial#, username, status, program, module, sql_id from v$session
2 where sid=293;
select sql_text from v$sqlarea where sql_id='4m9tcwtvdfqj5';
col "Lock Type" format a15
col "Mode Held" format a15
col "Blocking?" format a15
SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",
SUBSTR(lock_type,1,15) "Lock Type",
SUBSTR(mode_held,1,15) "Mode Held",
SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
where blocking_others='Blocking';
no rows selected
SQL>
SQL> select sid, serial#, username, status, program, module, sql_id from v$session
2 where sid=293;
select sql_text from v$sqlarea where sql_id='4m9tcwtvdfqj5';
Lock details
SELECT NVL(S.USERNAME,'INTERNAL') USERNAME,
NVL(S.TERMINAL,'NONE') TERMINAL,L.SID || ',' || S.SERIAL# KILL,
U1.NAME|| '.' || SUBSTR(T1.NAME,1,20) TA_BLE,
DECODE(L.LMODE,1, 'NO LOCK',2,'ROW SHARE',3,'ROW EXCLUSIVE',
4,'SHARE',5,'SHARE ROW EXCLUSIVE',6,'EXCLUSIVE',NULL) LMODE,
DECODE(L.REQUEST,1,'NO LOCK',2,'ROW SHARE',3,'ROW EXCLUSIVE',
4,'SHARE',5,'SHARE ROW EXCLUSIVE',6,'EXLUSIVE',NULL) REQUEST,
S.LOGON_TIME
FROM V$LOCK L,V$SESSION S,SYS.USER$ U1,SYS.OBJ$ T1
WHERE L.SID=S.SID
AND T1.OBJ#=DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 6,1,2,5;
NVL(S.TERMINAL,'NONE') TERMINAL,L.SID || ',' || S.SERIAL# KILL,
U1.NAME|| '.' || SUBSTR(T1.NAME,1,20) TA_BLE,
DECODE(L.LMODE,1, 'NO LOCK',2,'ROW SHARE',3,'ROW EXCLUSIVE',
4,'SHARE',5,'SHARE ROW EXCLUSIVE',6,'EXCLUSIVE',NULL) LMODE,
DECODE(L.REQUEST,1,'NO LOCK',2,'ROW SHARE',3,'ROW EXCLUSIVE',
4,'SHARE',5,'SHARE ROW EXCLUSIVE',6,'EXLUSIVE',NULL) REQUEST,
S.LOGON_TIME
FROM V$LOCK L,V$SESSION S,SYS.USER$ U1,SYS.OBJ$ T1
WHERE L.SID=S.SID
AND T1.OBJ#=DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
ORDER BY 6,1,2,5;
To find out tablelocks.
SET ECHO off
REM NAME: TFSLKILL.SQL
REM USAGE:"@path/tfslkill"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The report generated by this script gives information on sessions
REM which are holding locks and gives the information needed to kill
REM using the ALTER SYSTEM KILL SESSION command.
REM ------------------------------------------------------------------------
REM Main text of script follows:
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;
/
REM NAME: TFSLKILL.SQL
REM USAGE:"@path/tfslkill"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The report generated by this script gives information on sessions
REM which are holding locks and gives the information needed to kill
REM using the ALTER SYSTEM KILL SESSION command.
REM ------------------------------------------------------------------------
REM Main text of script follows:
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13 column res heading 'Resource Type' format 999 column id1 format 9999990 column id2 format 9999990 column lmode heading 'Lock Held' format a20 column request heading 'Lock Requested' format a20 column serial# format 99999 column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;
/
To determine tablespace usage.
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool tablespace_usage.lst
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
spool off
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool tablespace_usage.lst
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
spool off
To Find ideal sga size.
SQL> SELECT * FROM V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
6144 1 674124 1 265406859
1536 .25 1784541 2.6472 517729160
3072 .5 943032 1.3989 326211570
12288 2 543748 .8066 226816702
7680 1.25 652282 .9676 262195436
9216 1.5 623834 .9254 253967823
10752 1.75 554534 .8226 226816702
4608 .75 730144 1.0831 277748278
8 rows selected.
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
6144 1 674124 1 265406859
1536 .25 1784541 2.6472 517729160
3072 .5 943032 1.3989 326211570
12288 2 543748 .8066 226816702
7680 1.25 652282 .9676 262195436
9216 1.5 623834 .9254 253967823
10752 1.75 554534 .8226 226816702
4608 .75 730144 1.0831 277748278
8 rows selected.
To Find ideal sga size.
SQL> SELECT * FROM V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
6144 1 674124 1 265406859
1536 .25 1784541 2.6472 517729160
3072 .5 943032 1.3989 326211570
12288 2 543748 .8066 226816702
7680 1.25 652282 .9676 262195436
9216 1.5 623834 .9254 253967823
10752 1.75 554534 .8226 226816702
4608 .75 730144 1.0831 277748278
8 rows selected.
-----------------------------------------------
V$SGA_TARGET_ADVICEV$SGA_TARGET_ADVICE provides information about the SGA_TARGET initialization parameter.
Column Datatype Description
SGA_SIZE NUMBER Size of the SGA
SGA_SIZE_FACTOR NUMBER Ratio between the SGA_SIZE and the current size of the SGA
ESTD_DB_TIME NUMBER Estimated DB_TIME for this SGA_SIZE
ESTD_DB_TIME_FACTOR NUMBER Ratio between ESTD_DB_TIME and DB_TIME for the current size of the SGA
ESTD_PHYSICAL_READS NUMBER Estimated number of physical reads
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
6144 1 674124 1 265406859
1536 .25 1784541 2.6472 517729160
3072 .5 943032 1.3989 326211570
12288 2 543748 .8066 226816702
7680 1.25 652282 .9676 262195436
9216 1.5 623834 .9254 253967823
10752 1.75 554534 .8226 226816702
4608 .75 730144 1.0831 277748278
8 rows selected.
-----------------------------------------------
V$SGA_TARGET_ADVICEV$SGA_TARGET_ADVICE provides information about the SGA_TARGET initialization parameter.
Column Datatype Description
SGA_SIZE NUMBER Size of the SGA
SGA_SIZE_FACTOR NUMBER Ratio between the SGA_SIZE and the current size of the SGA
ESTD_DB_TIME NUMBER Estimated DB_TIME for this SGA_SIZE
ESTD_DB_TIME_FACTOR NUMBER Ratio between ESTD_DB_TIME and DB_TIME for the current size of the SGA
ESTD_PHYSICAL_READS NUMBER Estimated number of physical reads
Subscribe to:
Posts (Atom)