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