Scripts « Oracle DBA – Tips and Techniques

DBUPGDIAG script to check integrity of database before 11g upgrade

Script to check integrity of a 9i or 10g database before upgrade to 11g.

Run this script connected as sysdba in the 9i or 10g database.

NAME: DBUPGDIAG.SQL

This script can be downloaded from Oracle Metalink note 556610.1

– PURPOSE:
– This script is intended to provide a user friendly output to diagonise
– the status of the database before (or) after upgrade. The script will
– create a file called db_upg_diag__.log in your local
– working directory. This does not make any DDL / DML modifications.
–
– This script will work in both Windows and Unix platforms from database
– version 9.2 or higher.

col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,’fmMonth DD, YYYY’) TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,’dd-Mon-yyyy_hhmi’) timecol,’.log’ spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || ‘_’ output FROM v$parameter WHERE name

Continue reading DBUPGDIAG script to check integrity of database before 11g upgrade

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups -

This script will be run in the database, not the catalog.

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

Script – Sessions with high physical reads

set linesize 120
col os_user format a10
col username format a15

col pid format 9999999999
PROMPT SESSIONS SORTED BY PHYSICAL READS
PROMPT
select
OSUSER os_user,username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status=’ACTIVE’
order by PHYSICAL_READS;

Script – Monitor Flashback Logs

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

Script – Database structure and file location

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading “Control Files”

select name
from sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp format 9999
col member format a50 heading “Online REDO Logs”
col File# format 9999
col name format a50 heading “Online REDO Logs”
break on Grp
select group#,member
from sys.v_$logfile
/

Prompt Data Files Locations >>>>

col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading “Database Data Files”
col Reads format 99,999,999
col Writes format 99,999,999

break on report
compute sum label ‘Total(MB)’ of Mbyte on report

select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,

Continue reading Script – Database structure and file location

Script – Tablespace free space and fragmentation

set linesize 150
column tablespace_name format a20 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(MB)’
column Pct_Free format 999.99 heading ‘% Free’
column Chunks_Free format 9999 heading ‘No Of Ext.’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,

Continue reading Script – Tablespace free space and fragmentation

Data Guard Switchover Unix shell script

The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.

It is very important that the scripts are run in the correct order and on right machine.

These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” and there is another file called “set$ORACLE_SID” which is located under “/var/opt/oracle/cronjobs”. This script sets the environment for the individual Oracle instance like the $ORACLE_SID and the $ORACLE_HOME.

On the machine where the Primary Database is running we need to run the following scripts

$ cd /var/opt/oracle/dataguard
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh

On the machine where the Standby Database is running we need to run the following script:

$ cd /var/opt/oracle/dataguard
$ ./make_me_primary.sh

After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:

$ cd /var/opt/oracle/dataguard
$ ./start_recovery.sh

pre_switchover_check.sh

!/bin/ksh

if [ "$1" = "" ]
then
echo

Continue reading Data Guard Switchover Unix shell script

Script – Latch Contention (top 5 latches)

This script will display the top 5 latches with the most sleeps.

Script can be changed to even sort the display on misses instead.

set linesize 120
col name format a30

select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;

Script – Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads

set serverout on size 1000000
set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————-’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Script – Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads

set serverout on size 1000000

declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
order by buffer_gets desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration