shrinking (resizing) undo tablespace in oracle database

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs_2 DATAFILE ‘/orad001/data_files/undotbs2_02.dbf’ size 10000M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs_2;

3. Drop the old UNDO tablespace

DROP TABLESPACE UNDOTBS1 including contents;

****** if you face an error that you can’t drop the undo tablespace then you need to check the sessions that requires (commit or rollback).

you may face the error “Undo Tablespace X moved to Pending Switch-Out state”

To Fix this use the following query to check the session,oracleuser, program that requires either a commit transaction or a rollback.

**********************
SELECT
a.usn,a.name,b.status,
c.tablespace_name,d.addr,e.sid,e.serial#,
e.username,e.program,e.machine,e.osuser
FROM
v$rollname a,v$rollstat b,
dba_rollback_segs c,v$transaction d,
v$session e
WHERE
a.usn=b.usn AND a.name=c.segment_name AND
a.usn=d.xidusn AND d.addr=e.taddr AND
b.status=’PENDING OFFLINE’;
**************************

4. do verification using this command:

SQL> show parameter undo_tablespace


********************************************************************************

Undo Segments

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

Check the overall status for undos
********************************************************************************
select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

Undo Blocks per Second
********************************************************************************
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;

Optimal Undo Retention and  Calculate Needed UNDO Size for given Database Activity
********************************************************************************
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL 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';

Converting a single comma separated row into multiple rows

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

CPU/Wait/Acrive sessions average for given time slot

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_session_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

CPU Utilization by instance - daily summary

set echo off feedback off timing off pause off verify off
set pagesize 500 linesize 90 trimout on trimspool on
col inst_id format 90 heading "Inst"
col begin_tm format a12 heading "Begin Time"
col end_tm format a12 heading "End Time"
col ela_secs format 999,999,990 heading "Wall-Clock|Secs|Elapsed"
col cpu_ela_secs format 999,999,990 heading "CPU Secs|Elapsed|(Secs*CPUs)"
col used_secs format 999,999,990 heading "CPU Secs|Used"
col pct format 99,990.00 heading "% Used"
col arrow format a5 heading "When?"
col name new_value V_DBNAME noprint

define V_PCT_THRESHOLD="40"

select name from v$database;
clear breaks computes
break on inst_id skip 1
compute max of pct on inst_id
--ttitle center '&&V_DBNAME - CPU Utilization by instance - daily summary' skip line

select inst_id,
begin_tm,
ela_secs,
cpu_ela_secs,
used_secs,
pct,
decode(greatest(this_pct,40),this_pct,'<==','') arrow
from (select inst_id,
to_char(to_date(begin_tm,'DD-MON HH24:MI'), 'DD-MON') begin_tm,
sum(ela_secs) ela_secs,
sum(cpu_ela_secs) cpu_ela_secs,
sum(used_secs) used_secs,
(sum(used_secs)/sum(cpu_ela_secs))*100 pct,
max(nvl(this_pct,0)) this_pct,
max_pct
from (select inst_id,
begin_tm,
end_tm,
ela_secs,
cpu_ela_secs,
sum(used_secs) used_secs,
sum(pct) this_pct,
max(sum(pct)) over () max_pct
from (select stm.instance_number inst_id,
to_char(s.begin_interval_time,'DD-MON HH24:MI') begin_tm,
to_char(s.end_interval_time,'DD-MON HH24:MI') end_tm,
(to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
- to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400 ela_secs,
((to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
 - to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400)*
p.value cpu_ela_secs,
decode(greatest(stm.value, lag(stm.value,1,999999999999999999)
  over (partition by stm.dbid,
stm.instance_number,
stm.stat_name
order by stm.snap_id)),
999999999999999999, 0,
stm.value, ((stm.value - lag(stm.value,1,null)
over (partition by stm.dbid,
stm.instance_number,
stm.stat_name
order by stm.snap_id))/1000000),
stm.value/1000000) used_secs,
(decode(greatest(stm.value, lag(stm.value,1,999999999999999999)
   over (partition by stm.dbid,
stm.instance_number,
stm.stat_name
 order by stm.snap_id)),
999999999999999999, 0,
stm.value, ((stm.value - lag(stm.value,1,null)
over (partition by stm.dbid,
stm.instance_number,
stm.stat_name
order by stm.snap_id))/1000000),
stm.value/1000000) /
(((to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
   - to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400)*p.value))
*100 pct
from dba_hist_sys_time_model stm,
dba_hist_snapshot s,
gv$parameter p,
                                dbsnmp.caw_dbid_mapping m
where stm.stat_name in ('DB CPU','background cpu time')
                         and    LOWER(m.target_name)= '&dbname'
                         and    s.dbid= m.new_dbid
and s.snap_id = stm.snap_id
and s.dbid = stm.dbid
and s.instance_number = stm.instance_number
and p.name = 'cpu_count'
and p.inst_id = s.instance_number)
group by inst_id,
 begin_tm,
 end_tm,
 ela_secs,
 cpu_ela_secs)
group by inst_id,
 to_char(to_date(begin_tm,'DD-MON HH24:MI'), 'DD-MON'),
 max_pct)
/*
where max_pct >= &&V_PCT_THRESHOLD
*/
order by inst_id,
begin_tm;

--ttitle center '&&V_DBNAME - CPU Utilization by instance - AWR snapshot detail' skip line
select inst_id,
begin_tm,
end_tm,
ela_secs,
cpu_ela_secs,
used_secs,
pct,
decode(greatest(40,nvl(pct,0)),pct,'<==','')
from (select inst_id,
begin_tm,
end_tm,
ela_secs,
cpu_ela_secs,
sum(used_secs) used_secs,
sum(pct) pct,
max(sum(pct)) over () max_pct
from (select stm.instance_number inst_id,
to_char(s.begin_interval_time,'DD-MON HH24:MI') begin_tm,
to_char(s.end_interval_time,'DD-MON HH24:MI') end_tm,
(to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
- to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400 ela_secs,
((to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
 - to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400)*
p.value cpu_ela_secs,
decode(greatest(stm.value, lag(stm.value,1,999999999999999999)
  over (partition by stm.dbid, stm.instance_number, stm.stat_name
order by stm.snap_id)),
999999999999999999, to_number(null),
stm.value, ((stm.value - lag(stm.value,1,null)
over (partition by stm.dbid, stm.instance_number, stm.stat_name
order by stm.snap_id))/1000000),
stm.value/1000000) used_secs,
(decode(greatest(stm.value, lag(stm.value,1,999999999999999999)
   over (partition by stm.dbid, stm.instance_number, stm.stat_name
 order by stm.snap_id)),
999999999999999999, to_number(null),
stm.value, ((stm.value - lag(stm.value,1,null)
over (partition by stm.dbid, stm.instance_number, stm.stat_name
order by stm.snap_id))/1000000),
stm.value/1000000) /
(((to_date(to_char(s.end_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
   - to_date(to_char(s.begin_interval_time,'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS'))*86400)*p.value))
*100 pct
from dba_hist_sys_time_model stm,
dba_hist_snapshot s,
gv$parameter p,
                        dbsnmp.CAW_DBID_MAPPING m
where stm.stat_name in ('DB CPU','background cpu time')
                 and    LOWER(m.target_name)= '&&dbname'
                 and    s.dbid=m.new_dbid
and s.snap_id = stm.snap_id
and s.dbid = stm.dbid
and s.instance_number = stm.instance_number
and p.name = 'cpu_count'
and p.inst_id = s.instance_number)
group by inst_id,
 begin_tm,
 end_tm,
 ela_secs,
 cpu_ela_secs)
/*
where max_pct >= &&V_PCT_THRESHOLD
*/
order by inst_id,
begin_tm;

clear breaks computes
set feedback 6 pagesize 100 linesize 130 verify on
ttitle off

Restore/Create Database from file copy method -- Method 2

Assumption
source database exist and target database is not exist , and all the folders have been created in the target server.

source db = sourcedb
target db = targetdb


1.execute the "alter database backup controlfile to trace" in source database and save as .SQL file.
  this will generate a script containing a create controlfile command and store it in the trace  directory identified in the user_dump_dest parameter of the init.ora file.

given below is the sample script, and you can change the data file and log file paths.

2. Cold backup of sourcedb( shutdown db and take a OS copy of .dbf files only)
3. Copy .DBF files from sourcedb to targetdb (only all .dbf files to targetdb datafile folder)
4.In terminal export ORACLE_SID=targetdb in order to Recreate control files in targetdb by running the SQL script attached.

    > run the script generated in step 1



step 1 output script

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE  "targetdb "  RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'E:\oracle\oradata\targetdb \redo011.log',
    'E:\oracle\oradata\targetdb \redo012.log'
  ) SIZE 50M,
  GROUP 2 (
    'E:\oracle\oradata\targetdb \redo021.log',
    'E:\oracle\oradata\targetdb \redo022.log'
  ) SIZE 50M,
  GROUP 3 (
    'E:\oracle\oradata\targetdb \redo031.log',
    'E:\oracle\oradata\targetdb \redo032.log'
  ) SIZE 50M
DATAFILE
  'E:\oracle\oradata\targetdb \system01.dbf',
  'E:\oracle\oradata\targetdb \undotbs01.dbf',
  'E:\oracle\oradata\targetdb \sysaux01.dbf',
  'E:\oracle\oradata\targetdb \STREAMS_TBS01.dbf',
  'E:\oracle\oradata\targetdb \users01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\oracle\oradata\targetdb \temp01.dbf'
     SIZE 209715200  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; 

Resolving common Oracle Wait Events

http://gavinsoorma.com/wp-content/uploads/2011/12/Resolving-common-Oracle-Wait-Events-using-the-Wait-Interface.htm

Tablespace Growth History and Forecast

set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
/

Index performacne (Clustering factor)

Clustering factor 

Clustering factor should be low and nearly equal  to no of block in the table, if the clustering factor is high then index scan inefficient and it will effect to query performance. always try to make cluster factor in low level , to check the cluster factor use the below query

select ind.index_name,ind.table_name,ind.clustering_factor,tab.num_rows,tab.blocks from all_indexes ind,all_tables tab
where ind.table_name=tab.table_name and ind.table_name ='<table_name>';

if your accessing more than 10% of data from a table where you have high cluster factor index then optimizer will ignore the index and will do the full table scan.

Configure to run two-or-more databases in different timezones on the same machine -- part 2

1) Create a file call dbora under the /etc/init.d/ and put the following content as root user


#! /bin/bash
#
# network Bring up/down networking
#
# chkconfig: 345 20 80
# description: Starts and stops the Oracle database and listeners
#
# /etc/rc.d/init.d/oracle
# See how we were called


export ORACLE_SID=grdprd
export TZ=UTC-08
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

su oracle -c "$ORACLE_HOME/bin/lsnrctl stop LISTENER_UTC8"
su oracle -c "$ORACLE_HOME/bin/lsnrctl start LISTENER_UTC8"

echo $ORACLE_SID

su oracle -c "$ORACLE_HOME/bin/sqlplus '/ as sysdba'" << EOF
startup
EOF


2)

execute below as root user

chmod +x /etc/init.d/dbora

chkconfig --add dbora