Day to Day dba tasks required scripts and commands

Find database SID with unix process
##########################
prompt "Please Enter The UNIX Process ID"
accept spid
set verify off
set pagesize 50000
set linesize 30000
set long 500000
set head off
SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '|| s.process||' (Client)  '||
       p.spid||' - '||p.pid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  FROM V$PROCESS P,V$SESSION s
 WHERE p.addr = s.paddr
AND p.spid ='&spid';
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;

##############################
lsof /vah/oracle/dbdata/app02
################################
free up space
procmap 168098
procfiles 168098
##############################
to check mountpoint unmounted or not
######################
lsvg -l fdocumcmmk1
mount | grep /linuxhyd1

To Clean .patch_storage
################
opatch util cleanup
To find directory size
################
du -sh *
Before running slibclean
##################
genld -l | grep /documtmmk1/oracle/product/10.2.0
genkld | grep /documtmmk1/oracle/product/10.2.0
/usr/sbin/slibclean
##################
Enable tracing 10046
#####################

tkprof eq2_ora_20876.trc econdq2_ora_20876.out sys=y waits=y explain=userid/password
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql
create or replace trigger set_system_event_trace
after logon on database
declare
  v_user dba_users.username%TYPE := user;
 sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
if (v_user='SCHEMANAME') THEN
 execute immediate sql_stmt1;
 end if;
exception
 when others then
   null;
  end;
  /
set autotrace traceonly explain
select instance_name,to_char(startup_time,'dd/mm/yy hh24:mi:ss') from v$instance;
#######################
FTP script
#########
more ftp_schema_hyddbo.ksh
#! /bin/ksh
ftp -n shyd11 << !
user arun arun##
cd /mnt/exports/hyd2
bin
prompt
mget exp_schema_hyddbo.dmp.Z
!
##################
TOP CPU process
###########################
 ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Use dd for pre creating the db files
###########################
dd if=/dev/zero of=ofihrtTT03.dbf bs=1024 count=5120000 creates 5000m file
find bigest files in the filesystem
##########################
find . -type f -xdev -print|  xargs ls -ladtr | cut -c34- | sort -n -r | head -25
############################
Undo sizing
############################
Optimal Undo retention =  Actual undo size/(db block size * undo block per second)

Actual Undo Size
---------------
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#;

DB BLOCK SIZE
----------------------------
You can get this from parameter file

Or

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

UNDO BLOCKS PER SECOND
-----------------------------

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
###############################################
Verify Standby database.
###############################################
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='NO'  order by sequence#;
select DEST_ID,DEST_NAME,status ,ERROR from v$archive_dest_status;
select sequence#, applied, to_char(first_time,'dd-mon-yyyy hh:mi:ss'), to_char(next_time,'dd-mon-yyyy hh:mi:ss') from v$archived_log  where APPLIED='NO' order by sequence#;
select process,status from v$managed_standby;
SELECT MESSAGE FROM V$DATAGUARD_STATUS ;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm
select 'DR IS STALE!!' from v$archived_log where applied = 'NO'
  and to_char(first_time,'yyyymmddhh24miss')< to_char(sysdate-1,'yyyymmddhh24miss');
SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM    V$MANAGED_STANDBY;
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='YES'  order by sequence#;
delete archivelog until sequence 7495 thread 1;

Finds locked objects
####################################################
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
#############################
To mail a file from unix level
uuencode drop.log | mailx -s "DROP log @sHYD11 by ArunM" 20280280@h.com

export MAILTO="20280280@h.com"
export CONTENT="README.html"
export SUBJECT="README.html"
(
 echo "Subject: $SUBJECT"
 echo "MIME-Version: 1.0"
 echo "Content-Type: text/html"
 echo "Content-Disposition: inline"
 cat $CONTENT
) | /usr/sbin/sendmail $MAILTO

/* This shows waits */
#############################
select s.username, s.osuser, s.machine, s.serial#, w.*
from v$session_wait w, v$session s
where s.sid=w.sid
and w.event <> 'SQL*Net message from client'
and w.event<>'rdbms ipc message'
order by s.username ;

Raw filesystem used or not
++++++++++++++++++++
fuser -u <raw device path > / <device name>
vxprint filename

################
run sql from OS
##################
sqlplus /nolog  << EOFL
connect arun/arun
select  'I am in ** '|| a.name || ' ** Database and my SID is : ' || b.sid  " " from v\$database a, (select distinct sid from v\$mystat) b ;
@drop_mat_views.sql > $LOGDIR/test.out
@mat_views.sql >> $LOGDIR/test.out
EOFL
exit

Check Memory:
#####################
/usr/sbin/prtconf | grep Mem
Check PRocessor:
##################
psrinfo -v

Import speed
##############
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
          rows_processed,
          round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
          trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
   from   sys.v_$sqlarea
   where  sql_text like 'INSERT %INTO "%'
     and  command_type = 2
     and  open_versions > 0;

session waits and blockers
##################################
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
#######################################

cloning the oracle binaries:
##################
cpio -idcmv < aix_64bit_11107_PSU5_p9952228.cpio
change the oraInst.loc  in /var/opt/oracle for inventory_loc=/oracle/product/11.1.0/oraInventory
change location to the new home location and comment the existing one if it is 9i.
For solaris
###############
perl clone.pl ORACLE_HOME="/oracle/product/11.1.0" ORACLE_HOME_NAME="Ora11g" ORACLE_BASE="/home/oracle" -invPtrLoc /var/opt/oracle/oraInst.loc
 run this from ORACLE_HOME/clone/bin
For AIX
##############
perl clone.pl ORACLE_HOME="/oracle/product/10.2.0" ORACLE_HOME_NAME="Ora10g" ORACLE_BASE="/home/oracle" -invPtrLoc /etc/oraInst.loc
#####################################################
Re-Creating Invalid Synonyms
set echo off
set heading off
set linesize 200
set pagesize 0

spool val_syn.txt
select 'create or replace synonym ' || owner || '.' || synonym_name || ' for ' ||  table_owner || '.' || table_name || ' ; '
from dba_synonyms where  (owner,synonym_name) in
      (select owner, object_name from dba_objects where status = 'INVALID' and object_type = 'SYNONYM')
/
spool off ;
set echo on
set heading on
set feedback on
spool val_syn
@ val_syn.txt
spool off ;
######################################################
spool drop_ARUN.sql
set heading off;
select 'DROP TABLE '||OWNER||'.'||TABLE_NAME||' CASCADE CONSTRAINTS ;' from dba_tables where owner='ARUN';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='VIEW' ;
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='PROCEDURE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='TRIGGER';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='MATERIALIZED VIEW';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='FUNCTION';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SYNONYM';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='PACKAGE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SEQUENCE';
select 'spool off' from dual;
spool off;

Day to Day dba tasks required scripts and commands

Find database SID with unix process
##########################
prompt "Please Enter The UNIX Process ID"
accept spid
set verify off
set pagesize 50000
set linesize 30000
set long 500000
set head off
SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '|| s.process||' (Client)  '||
       p.spid||' - '||p.pid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  FROM V$PROCESS P,V$SESSION s
 WHERE p.addr = s.paddr
AND p.spid ='&spid';
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;

##############################
lsof /vah/oracle/dbdata/app02
################################
free up space
procmap 168098
procfiles 168098
##############################
to check mountpoint unmounted or not
######################
lsvg -l fdocumcmmk1
mount | grep /linuxhyd1

To Clean .patch_storage
################
opatch util cleanup
To find directory size
################
du -sh *
Before running slibclean
##################
genld -l | grep /documtmmk1/oracle/product/10.2.0
genkld | grep /documtmmk1/oracle/product/10.2.0
/usr/sbin/slibclean
##################
Enable tracing 10046
#####################

tkprof eq2_ora_20876.trc econdq2_ora_20876.out sys=y waits=y explain=userid/password
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql
create or replace trigger set_system_event_trace
after logon on database
declare
  v_user dba_users.username%TYPE := user;
 sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
if (v_user='SCHEMANAME') THEN
 execute immediate sql_stmt1;
 end if;
exception
 when others then
   null;
  end;
  /
set autotrace traceonly explain
select instance_name,to_char(startup_time,'dd/mm/yy hh24:mi:ss') from v$instance;
#######################
FTP script
#########
more ftp_schema_hyddbo.ksh
#! /bin/ksh
ftp -n shyd11 << !
user arun arun##
cd /mnt/exports/hyd2
bin
prompt
mget exp_schema_hyddbo.dmp.Z
!
##################
TOP CPU process
###########################
 ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Use dd for pre creating the db files
###########################
dd if=/dev/zero of=ofihrtTT03.dbf bs=1024 count=5120000 creates 5000m file
find bigest files in the filesystem
##########################
find . -type f -xdev -print|  xargs ls -ladtr | cut -c34- | sort -n -r | head -25
############################
Undo sizing
############################
Optimal Undo retention =  Actual undo size/(db block size * undo block per second)

Actual Undo Size
---------------
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#;

DB BLOCK SIZE
----------------------------
You can get this from parameter file

Or

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

UNDO BLOCKS PER SECOND
-----------------------------

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
###############################################
Verify Standby database.
###############################################
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='NO'  order by sequence#;
select DEST_ID,DEST_NAME,status ,ERROR from v$archive_dest_status;
select sequence#, applied, to_char(first_time,'dd-mon-yyyy hh:mi:ss'), to_char(next_time,'dd-mon-yyyy hh:mi:ss') from v$archived_log  where APPLIED='NO' order by sequence#;
select process,status from v$managed_standby;
SELECT MESSAGE FROM V$DATAGUARD_STATUS ;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm
select 'DR IS STALE!!' from v$archived_log where applied = 'NO'
  and to_char(first_time,'yyyymmddhh24miss')< to_char(sysdate-1,'yyyymmddhh24miss');
SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM    V$MANAGED_STANDBY;
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='YES'  order by sequence#;
delete archivelog until sequence 7495 thread 1;

Finds locked objects
####################################################
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
#############################
To mail a file from unix level
uuencode drop.log | mailx -s "DROP log @sHYD11 by ArunM" 20280280@h.com

export MAILTO="20280280@h.com"
export CONTENT="README.html"
export SUBJECT="README.html"
(
 echo "Subject: $SUBJECT"
 echo "MIME-Version: 1.0"
 echo "Content-Type: text/html"
 echo "Content-Disposition: inline"
 cat $CONTENT
) | /usr/sbin/sendmail $MAILTO

/* This shows waits */
#############################
select s.username, s.osuser, s.machine, s.serial#, w.*
from v$session_wait w, v$session s
where s.sid=w.sid
and w.event <> 'SQL*Net message from client'
and w.event<>'rdbms ipc message'
order by s.username ;

Raw filesystem used or not
++++++++++++++++++++
fuser -u <raw device path > / <device name>
vxprint filename

################
run sql from OS
##################
sqlplus /nolog  << EOFL
connect arun/arun
select  'I am in ** '|| a.name || ' ** Database and my SID is : ' || b.sid  " " from v\$database a, (select distinct sid from v\$mystat) b ;
@drop_mat_views.sql > $LOGDIR/test.out
@mat_views.sql >> $LOGDIR/test.out
EOFL
exit

Check Memory:
#####################
/usr/sbin/prtconf | grep Mem
Check PRocessor:
##################
psrinfo -v

Import speed
##############
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
          rows_processed,
          round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
          trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
   from   sys.v_$sqlarea
   where  sql_text like 'INSERT %INTO "%'
     and  command_type = 2
     and  open_versions > 0;

session waits and blockers
##################################
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
#######################################

cloning the oracle binaries:
##################
cpio -idcmv < aix_64bit_11107_PSU5_p9952228.cpio
change the oraInst.loc  in /var/opt/oracle for inventory_loc=/oracle/product/11.1.0/oraInventory
change location to the new home location and comment the existing one if it is 9i.
For solaris
###############
perl clone.pl ORACLE_HOME="/oracle/product/11.1.0" ORACLE_HOME_NAME="Ora11g" ORACLE_BASE="/home/oracle" -invPtrLoc /var/opt/oracle/oraInst.loc
 run this from ORACLE_HOME/clone/bin
For AIX
##############
perl clone.pl ORACLE_HOME="/oracle/product/10.2.0" ORACLE_HOME_NAME="Ora10g" ORACLE_BASE="/home/oracle" -invPtrLoc /etc/oraInst.loc
#####################################################
Re-Creating Invalid Synonyms
set echo off
set heading off
set linesize 200
set pagesize 0

spool val_syn.txt
select 'create or replace synonym ' || owner || '.' || synonym_name || ' for ' ||  table_owner || '.' || table_name || ' ; '
from dba_synonyms where  (owner,synonym_name) in
      (select owner, object_name from dba_objects where status = 'INVALID' and object_type = 'SYNONYM')
/
spool off ;
set echo on
set heading on
set feedback on
spool val_syn
@ val_syn.txt
spool off ;
######################################################
spool drop_ARUN.sql
set heading off;
select 'DROP TABLE '||OWNER||'.'||TABLE_NAME||' CASCADE CONSTRAINTS ;' from dba_tables where owner='ARUN';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='VIEW' ;
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='PROCEDURE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='TRIGGER';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='MATERIALIZED VIEW';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='FUNCTION';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SYNONYM';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='PACKAGE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SEQUENCE';
select 'spool off' from dual;
spool off;

#############
 Archive logs generated per day
 SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;


##########
Delete millions of records without  shortage of UNDO. Worked for me.

BEGIN
LOOP
delete FROM abcc.emp
   WHERE DATE_COLMN > TO_DATE('01/12/2016','dd/mm/yyyy') and rownum < 1000001;
  exit when SQL%rowcount =0;
commit;
END LOOP;

END;
/

Rman DR datafile restore and doing an Incremental restore.

###################
BACKUP OF DATAFILE RMAN
###################

rman  @rman_backup_database_ARUN1_dbf#9.rman
connect target /
CONFIGURE DEFAULT DEVICE TYPE TO 'DISK';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
run
{
allocate channel db_ch1 type DISK PARMS="BLKSIZE=1048576" format '/fisc/oracle/backup/d-db_01_ch1_%d_%T_%t_%s_%c_%p' ;
setlimit channel db_ch1 maxopenfiles 8;
backup datafile 9;
}
CONFIGURE DEVICE TYPE DISK CLEAR;
exit;
rman > catalog backuppiece '/fisc/oracle/backup/d-db_01_ch1_ARUN1_20101109_734618278_9262_1_1';
rman > list backup of datafile 9 device type DISK;
##############################
Taking incremental backup of the database
##############################
rman @rman_backup_database_ARUN1.rcv trace rman_backup_database_ARUN1.log
 more rman_backup_database_ARUN1.rcv
connect target /
CONFIGURE DEFAULT DEVICE TYPE TO 'DISK';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
run
{
allocate channel db_ch1 type DISK PARMS="BLKSIZE=1048576" format '/fisc/oracle/backup/d-db_01_ch1_%d_%T_%t_%s_%c_%p' ;
setlimit channel db_ch1 maxopenfiles 8;
allocate channel db_ch2 type DISK PARMS="BLKSIZE=1048576" format '/fisc/oracle/backup/d-db_02_ch1_%d_%T_%t_%s_%c_%p' ;
setlimit channel db_ch2 maxopenfiles 8;
allocate channel db_ch3 type DISK PARMS="BLKSIZE=1048576" format '/fisc/oracle/backup/d-db_03_ch1_%d_%T_%t_%s_%c_%p' ;
setlimit channel db_ch3 maxopenfiles 8;
allocate channel db_ch4 type DISK PARMS="BLKSIZE=1048576" format '/fisc/oracle/backup/d-db_04_ch1_%d_%T_%t_%s_%c_%p' ;
setlimit channel db_ch4 maxopenfiles 8;
backup
incremental level 1 cumulative
database not backed up since time 'sysdate - 0/24'
TAG TAG20101109T123257
filesperset 8;
sql 'alter system archive log current';
backup current controlfile;
}
CONFIGURE DEVICE TYPE DISK CLEAR;
sql 'alter session set optimizer_mode=RULE';
exit;
######################################
##############################################
##############################################
restore the datafile and restore from incremantal backup
##############################################
run
{
allocate channel ch1 type DISK;
set newname for datafile 9 to '/dev/roARUN1DT16';
restore datafile 9;
switch datafile 9;
release channel ch1;
}
rm d-db_01_ch1_ARUN1_20101109_734618278_9262_1_1
standby rman > catalog backuppiece '/fisc/oracle/backup/*';
catalog backuppiece '/fisc/oracle/backup/d-db_01_ch1_ARUN1_20101109_734620070_9263_1_1';
catalog backuppiece '/fisc/oracle/backup/d-db_02_ch1_ARUN1_20101109_734620070_9264_1_1';
catalog backuppiece '/fisc/oracle/backup/d-db_03_ch1_ARUN1_20101109_734620070_9265_1_1';
catalog backuppiece '/fisc/oracle/backup/d-db_04_ch1_ARUN1_20101109_734620070_9266_1_1';
catalog backuppiece '/fisc/oracle/backup/d-db_01_ch1_ARUN1_20101109_734624845_9267_1_1';
nohup rman.ksh &
rman target / @recover_db_ARUN1.rcv trace recover_db_ARUN1.log
rman.rcv
run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
recover database from tag 'TAG20101109T123257';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
rman_watch
nohup scp d-db_02_ch1_ARUN1_20101109_734620070_9264_1_1 oracle@ahyd1:/fisc/oracle/backup > /dev/null 2>&1
nohup scp d-db_03_ch1_ARUN1_20101109_734620070_9265_1_1 oracle@ahyd1:/fisc/oracle/backup > /dev/null 2>&1
nohup scp d-db_04_ch1_ARUN1_20101109_734620070_9266_1_1 oracle@ahyd1:/fisc/oracle/backup > /dev/null 2>&1
nohup scp d-db_04_ch1_ARUN1_20101109_734620070_9266_1_1 oracle@ahyd1:/fisc/oracle/backup 2>&1 &
#################################################################################
Restore script for DR build
#################################
 more rman_restore_fihrp1.rcv
# It is expected that correct control file is available
run
{
#     allocate channel disk1 type disk format '/fisc/dba/rman_backups/%d_%T_%t_%s_%p.save_set';
#
     allocate channel disk1 type disk;
     allocate channel disk2 type disk;
#     restore database from tag 'TAG20080731T141431';
     restore datafile 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 from tag 'TAG20080731T141431';
}
host "echo Restoring finishing at `date`";

Oracle Database Switch over and failover steps

Switchover and Failover for Oracle RAC Database Using Physical Standby

1. Switchover

1)      This is for:

a.       Scheduled and unscheduled maintenance at primary site (e.g., OS patching, database patching).

b.      A primary database that is running normally --- the switchover operation must start from the primary site.

c.       Application DR Tests --- switchover can be used for a DR test that tests non-database parts (Failover is the right procedure for database DR test).

2)      Steps

a.       Shutdown all primary instances except one.

b.      Shutdown all standby instances except one.

c.       On the primary site

                                                                                 i.            SQL> select switchover_status from V$database;

                                                                                ii.            If switchover_status shows ‘sessions active’ go to step iii. If switchover_status shows ‘TO_STANDBY’ go to step v.

                                                                              iii.            SQL> select sid, process, program from v$session where type='user' and sid <>(select distinct sid from V$mystat);

                                                                              iv.            If there are rows returned, contact customers and ask if the proceses can be killed. If yes go to step v. Otherwise stop here until they log out.

                                                                               v.            SQL> alter database commit to switchover to physical standby with session shutdown;

                                                                              vi.            SQL>shutdown immediate;

                                                                            vii.            SQL>exit;

                                                                           viii.            Restart the instance and mount it as standby

         SQL> startup nomount;

         SQL> alter database mount standby database;

                                                                               ix.            Start the recovery

         SQL> alter database recover managed standby database disconnect;

d.      On the DR site.

                                                                                 i.            SQL>alter database recover managed standby database cancel;

                                                                                ii.            SQL>alter database recover managed standby database nodelay disconnect from session through last switchover; --- this is needed when a time lag used.

                                                                              iii.            SQL> alter database commit to switchover to primary with session shutdown;

                                                                              iv.            SQL> alter database open;

e.       On the original primary site mount the other instances as standby.

f.        On the original DR site start up and open the other instances (as primary).

g.       Verify the new data guard configuration

                                                                                 i.            on new primary

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

                                                                               ii.            on new standby --- make sure the log file is copied over and applied or will be applied (if time lag in apply is used).

2. Failover

1)      This is for:

a.       A situation when primary database is completely gone and can’t be recovered within a time window as defined by business units.

b.      A real DR test --- failover operation is performed completely on DR site. There is no dependency on DR site.

c.       Repairing or trouble shooting a database due to human mistakes or data corruption --- time lag has to be used in this case.  

d.      Documentum is currently using 4 hr time lag.

2)      Steps

a.       Identify and resolve any gaps in the archived redo logfiles.

                                                                                 i.            SQL>  select thread#, low_sequence#, high_ sequence# from v$archive_gap

                                                                                ii.            SQL>  alter database register physical logfile ‘2_820_657623969.log’

b.      Identify, copy and register any other missing archived redo logfiles.

                                                                                 i.            SQL>  select unique thread# as thread, max(sequence#) over (partition by thread#) as last from v$archived_log;

                                                                                ii.            SQL>  alter database register physical logfile ‘1_820_657623970.log’

c.       We should use log writer (lgwr) and standby redo for log transfer, which will help avoid steps a and b.

d.      On DR site shutdown all the standby instances except one.

e.       On DR site initiate a failover.

                                                                                 i.            SQL> alter database recover managed standby database finish force;

                                                                                ii.            SQL> alter database commit to switchover to primary;

                                                                              iii.            SQL> alter database open;

                                                                              iv.            SQL> shutdown immediate;

                                                                               v.            SQL> startup;

f.    Failover with a time lag and without left logs applied. This is only for repairing or trouble shooting a database due to human mistakes or data corruption.

                                                                                 i.            SQL> alter database activate physical standby database;

                                                                                ii.            SQL> shutdown immediate;

                                                                              iii.            SQL> startup;

g.       Startup and open other instances on DR site.

h.   Rebuild physical standby on the original primary site after it comes ba