Copy database to another server ( without shutdown source database )

--Main script
#!/bin/sh
#
echo -e "\033[1m\033[41m           WARNING !!!!!!!!!!!!!!                 \033[0m"
echo -e "\033[1m\033[41m                                                  \033[0m"
echo -e "\033[1m\033[41m       entering wrong source and destination      \033[0m"
echo -e "\033[1m\033[41m        will caused serious problem !!!!          \033[0m"
echo -e "\033[1m\033[41m                                                  \033[0m"
echo -e "\033[1m\033[41m                                                  \033[0m"
echo -e "\033[1m\033[41m            ENTER source :  (lowercase)           \033[0m"


read ORACLESID
ORACLESID2=${ORACLESID^^}

echo -e "\033[1m\033[41m  ENTER DISTINATION instance(UPPERCASE)               \033[0m"
echo -e "\033[1m\033[41m  Notice ! DISTINATION FOLDER SHOULD BE EMPTY         \033[0m"
read DESTINATION

SRC_DIR="/u04/arch-src"
SCDIR="/u04/arch-src/scripts"
LOCKF="$SCDIR/${ORACLESID}_prc.lock"    #lock file used by script

while [ -f $LOCKF ]; do
              sleep 5
done
touch $LOCKF

# Do whatever you want here...

 cp -n /u01/oradata/$ORACLESID2/*  /x01/oradata/$DESTINATION/
 cp -n /u02/oradata/$ORACLESID2/*  /x02/oradata/$DESTINATION/

rm $LOCKF
echo "\033[1m\034[41m  IT IS DONE SUCCESSFULLY   \034[0m"
exit

--main_remote

#!/bin/sh

#

echo -e "\033[1m\033[41m           WARNING !!!!!!!!!!!!!!                 \033[0m"

echo -e "\033[1m\033[41m                                                  \033[0m"

echo -e "\033[1m\033[41m       entering wrong source and destination      \033[0m"

echo -e "\033[1m\033[41m        will caused serious problem !!!!          \033[0m"

echo -e "\033[1m\033[41m                                                  \033[0m"

echo -e "\033[1m\033[41m                                                  \033[0m"

echo -e "\033[1m\033[41m            ENTER source :  (lowercase)           \033[0m"

read ORACLESID

ORACLESID2=${ORACLESID^^}

echo -e "\033[1m\033[41m  ENTER DISTINATION instance(UPPERCASE)               \033[0m"

echo -e "\033[1m\033[41m  Notice ! DISTINATION FOLDER SHOULD BE EMPTY         \033[0m"

read DESTINATION

SRC_DIR="/u04/arch-src"

SCDIR="/u04/arch-src/scripts"

LOCKF="$SCDIR/${ORACLESID}_prc.lock"    #lock file used by script

while [ -f $LOCKF ]; do

              sleep 5

done

touch $LOCKF

# Do whatever you want here...

  scp -4 -c arcfour -C -r /u01/oradata/$ORACLESID2/*  oracle@oxdbstst2:/x01/oradata/$DESTINATION/

  scp -4 -c arcfour -C -r  /u02/oradata/$ORACLESID2/*  oracle@oxdbstst2:/x02/oradata/$DESTINATION/

rm $LOCKF

echo "\033[1m\034[41m  IT IS DONE SUCCESSFULLY   \034[0m"

exit

RMAN Backup status and history

Login as sysdba –

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 –

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
where input_type='DB INCR'
order by session_key;

FULL history

select ctime "Date",
       decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
        bsize "Size MB"
 from (select trunc(bp.completion_time) ctime
              , backup_type
              , round(sum(bp.bytes/1024/1024),2) bsize
       from v$backup_set bs, v$backup_piece bp
       where bs.set_stamp = bp.set_stamp
       and bs.set_count  = bp.set_count
       and bp.status = 'A'
       group by trunc(bp.completion_time), backup_type)
order by 1, 2;

--detail query got from web -----------------------

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

11g statistic gadther auto job info

In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all

maintenance windows. New maintenance windows introduced with 11g are
You can check the name and state of this job using following query

SQL> SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
Window - Description

MONDAY_WINDOW - Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW - Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW -Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW - Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW -Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW - Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW -Starts at 6 a.m. on Sunday and is 20 hours long.

To enable the job (in case it is disabled)

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/
You can check history/run duration of these jobs by using following query

select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY where JOB_START_TIME >systimestamp -7 and client_name='sql tuning advisor'

CLIENT_NAME       JOB_NAME      JOB_STATUS      JOB_START_TIME JOB_DURATION
------------------------------ ------------------------------ --------------- -------------------------------------------------- --------------------
sql tuning advisor       ORA$AT_SQ_SQL_SW_521      SUCCEEDED       30-MAR-12 04.00.01.698038 PM AMERICA/LOS_ANGELES +000 00:22:13
sql tuning advisor       ORA$AT_SQ_SQL_SW_493      SUCCEEDED       01-APR-12 04.00.02.701398 PM AMERICA/LOS_ANGELES +000 00:42:26
sql tuning advisor       ORA$AT_SQ_SQL_SW_522      SUCCEEDED       31-MAR-12 04.00.07.642613 PM AMERICA/LOS_ANGELES +000 00:00:37

select * from dba_autotask_client where client_name =
"auto optimizer stats collection" 

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