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

Posted by at No comments:

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;












Posted by



at





No comments:
  








































          

        

          

        

          

        









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" 














Posted by



at





No comments:
  


































        

      









Subscribe to:
Posts (Atom)