All About Oracle...: August 2010

Hi

There are two script i have created
1) Linux
2) solaris and other platform.

First one i just wrote with modified logic and less processing using files.
Second script is specially for other Unix OS ,due to tac command does not work there.

Please inform me on jonyjt@gmail.com , if you find problem regarding it.

1 script

#!/bin/ksh
###############################
# monitor gg process
###############################
export ORACLE_HOME=/u00/app/oracle/product/10.2.0/DB
export GG_HOME=/ORAGG/product/11.2.1/gghome10
export PATH=$GG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH
export LOGFILE=/tmp/testoutput.log
export DT=`date +%d/%m/%Y\ %k:%M:%S`
export BOX=`hostname -s`
export COLUMNS=130
#echo "############################################################"
#echo `date +%d/%m/%Y\ %k:%M:%S`
################################
echo $DT
rm -rf /tmp/testoutput.log
###############################
#check if process has abended.
${GG_HOME}/ggsci <<!| while read;do printf '%s\n' ;egrep -i '(EXTRACT|REPLICAT)'|tr ":" " " | tr -s '[:space:]' | awk '{if ($2=="ABENDED" || $5>=05 || $7 >=05 ) {print $1" "$3" is "$2" with lag of " $4"HR:"$5"MI and checkpoint of "$7"HR:"$8"MI" "\n">> "/tmp/testoutput.log"} else {print "No lag for"$3 > "/tmp/nolag.log"} }' ;  done
info all
exit
!
###############################
if [ -f ${LOGFILE} ]; then
        x=$( grep -c ABENDED /tmp/testoutput.log )
        y=$( grep -c RUNNING  /tmp/testoutput.log )
        if [ $x -gt 0 ]; then
                echo "please refer below logs" >> $LOGFILE
                echo "##################################################################" >> $LOGFILE
                tac ${GG_HOME}/ggserr.log | grep -m 1 -A 2 -B 2 ABEND >> $LOGFILE
                mailx -s "Alert GG Abended on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
        if [ $y -gt 0 ]; then
                ##echo "process running "
                mailx -s "Lag Found on $BOX" fistname.lastname@company.com < $LOGFILE
        fi
else
        echo "NO LAG FOUND"
fi
###################################

2 script It was created on Solaris platform for ksh shell. ggs_lag.ksh is main script which calls ggs.ksh , you can also merge both into one script also.

#!/bin/ksh
############################################
# Name: ggs_lag.ksh                             #
# PURPOSE: TO MONITOR LAG OF GOLDEN GATE        #
# NOTE: THIS SCRIPT CALLS ggs.ksh               #
# THIS SCRIPT NOTIFY IF LAG IS MORE THEN 30 MIN #
# ONLY FOR FOR EXT AND PMP PROCESS GROUP #
###########################################
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;./ggsci'
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
LOGDIR=/export/home/oracle/dba_scripts/ggs/logs
EMAILFile=$LOGDIR/ggs_email.log
BOX=$(uname -a | awk '{print $2}')

##########################################################################
# RUNNING SCRIPT TO GET GOLDEN GATE INFORMATION #
##########################################################################

/export/home/oracle/dba_scripts/ggs/ggs.ksh > $LOGDIR/ggs_1.log

#to check when script was running

echo "script ggsksh completed from ggs_lag at `date`" >> /tmp/ggs_check.log

##################################################################################
## FORMATING INFORMATION: change cut -d":" -f 1,4 TO cut -d":" -f 1,2 ##
## to getinformation about lag instead of checkpoint ##
## this command grep only EXT_ and PMP_ if you need more pattern ##
## if you need more pattern to be greped please add to '(EXT_|PMP_|pattern)' ##
##################################################################################


cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag and checkpoint both information #

#cat $LOGDIR/ggs_1.log|egrep -i '(EXT_|PMP_|DART)'|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 > $LOGDIR/ggs_2.log

# uncomment below command if you want to get lag information about running process #

#cat $LOGDIR/ggs_1.log|grep RUNNING|cut -d":" -f 1,2,4| tr ":" " "|tr -s '[:space:]'|cut -d" " -f1,2,3,4,5,6 >
$LOGDIR/ggs_2.log

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################

##########################################################################
## CHECKING FOR LAG MORE THEN 30 MIN FOR ABENDED PROCESS ##
##########################################################################


awk '{if ( $4 > 00 || $5 >=30 ) {print $1 " " $3 " HAS LAG of " $4" hour " $5 " min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " " d >> "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

# uncomment below command if you want to get lag and checkpoint both information #

#awk '{if ($4 >=30 || $5>=30 ) {print $1 " " $3 " has lag of "$4" min with checkpoint of "$5" min -- at -- " d "\n"} else {print "NO LAG FOR " $3 " "d > "/tmp/ggs_lag_fine.log" }}' d="$(date)" $LOGDIR/ggs_2.log > $LOGDIR/ggs_email.log

##########################################################
## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###
##########################################################

if [ -s $EMAILFile ]; then
#echo "ERRORS FOUND"
mailx -s "GG LAG FOUND ON: $BOX" your.email@gmail.com team.email@gmail.com < $EMAILFile else cat /dev/null > $EMAILFile
#echo "ERRORS NOT FOUND"
fi

################# SCRIPT END ######################

This is ggs.ksh which calls by above ggs_lag.ksh

#!/bin/ksh
#########################################
#Name: ggs.ksh #
#THIS SCRIPT WILL CALLED BY ggs_lag.ksh #
#########################################
#alias gate='clear;cd $GGATE;./ggsci'
echo "ggsksh started `date`" >> /tmp/ggs_check.log
export GGATE=/opt/oracle/u01/app/oracle/ggs
alias gate='clear;cd $GGATE;ggsci'
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/oracle/u01/app/oracle/ggs
export PATH=/opt/oracle/u01/app/oracle/ggs:$PATH
#cd $GGATE
/opt/oracle/u01/app/oracle/ggs/ggsci <<>
info all
exit
EOF
echo "ggsksh completed at `date` " >> /tmp/ggs_check.log

------ON HOST

==================================================================================================

ENABLE BLOCK CHANGE TRACKING

===================================================================================================

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

==================================================================================================

CHECK EACH LOCATION OF FILE ON HOST (PRODASM)

===================================================================================================

select MEMBER from v$logfile;

select NAME from v$datafile;

select NAME from v$tempfile;

+DG1/prodasm/datafile/users.259.727034915

+DG1/prodasm/datafile/sysaux.257.727034911

+DG1/prodasm/datafile/undotbs1.258.727034913

+DG1/prodasm/datafile/system.256.727034907

+DG1/prodasm/controlfile/current.260.727035019

==================================================================================================

PUT DATABASE IN BEGIN BACKUPMODE

===================================================================================================

SQL> alter database begin backup;

Database altered.

==================================================================================================

ON HOST (PRODASM) CONVERT ALL DBFILE + CONTROLFILE ASM FILE TO FILESYSTEMS ( DONT CONVERT TEMPFILE)

===================================================================================================

----------- CAN NOT CONVERT TEMPFILE......

run {

copy current controlfile to '/u01/copy/controlfile.ctl';

convert datafile '+DG1/prodasm/datafile/users.259.727034915' format '/u01/copy/users.dbf';

convert datafile '+DG1/prodasm/datafile/sysaux.257.727034911' format '/u01/copy/sysaux.dbf';

convert datafile '+DG1/prodasm/datafile/undotbs1.258.727034913' format '/u01/copy/undotbs1.dbf';

convert datafile '+DG1/prodasm/datafile/system.256.727034907' format '/u01/copy/system.dbf';

convert datafile '+DG1/DG1/prodasm/tempfile/temp.264.727035059' format '/u01/copy/tempfile.dbf';

}

----------------------OUTPUT--------------------------------

run {

copy current controlfile to '/u01/copy/controlfile.ctl';

convert datafile '+DG1/prodasm/datafile/users.259.727034915' format '/u01/copy/users.dbf';

RMAN> 2> 3> 4> convert datafile '+DG1/prodasm/datafile/sysaux.257.727034911' format '/u01/copy/sysaux.dbf';

5> convert datafile '+DG1/prodasm/datafile/undotbs1.258.727034913' format '/u01/copy/undotbs1.dbf';

convert datafile '+DG1/prodasm/datafile/system.256.727034907' format '/u01/copy/system.dbf';

6> 7> }

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=/u01/copy/controlfile.ctl tag=TAG20100815T204830 recid=26 stamp=727130911

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=+DG1/prodasm/datafile/users.259.727034915

converted datafile=/u01/copy/users.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:10

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=+DG1/prodasm/datafile/sysaux.257.727034911

converted datafile=/u01/copy/sysaux.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=+DG1/prodasm/datafile/undotbs1.258.727034913

converted datafile=/u01/copy/undotbs1.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=+DG1/prodasm/datafile/system.256.727034907

converted datafile=/u01/copy/system.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26

Finished backup at 15-AUG-10

RMAN> exit

===================================================================================

COPY EACH CONVERTEDFILE + INITFILE TO REMOTE SERVER FILESYSTEM OF PRODASM

===================================================================================

---ON HOST

copy each file to remote destination plus initfile

scp * DEVASM:/u01/copy

PRDASM-> scp * DEVASM:/u01/copy

controlfile.ctl 100% 7088KB 3.5MB/s 00:02

sysaux.dbf 100% 230MB 4.3MB/s 00:53

system.dbf 100% 480MB 4.7MB/s 01:42

undotbs1.dbf 100% 25MB 5.0MB/s 00:05

users.dbf 100% 5128KB 5.0MB/s 00:01

===================================================================================

ON REMOTE SERVER(DEVASM) CHANGE INIT FILE AND GIVE CONTROLFILE LOCATION THERE..

===================================================================================

PRODASM.__db_cache_size=121634816

PRODASM.__java_pool_size=4194304

PRODASM.__large_pool_size=4194304

PRODASM.__shared_pool_size=54525952

PRODASM.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/PRODASM/adump'

*.background_dump_dest='/u01/app/oracle/admin/PRODASM/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/copy/controlfile.ctl' ---------------> changed

*.core_dump_dest='/u01/app/oracle/admin/PRODASM/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DG2'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='+DG1','+DG2'

*.db_name='PRODASM'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODASMXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='location=+DG2/DEVASM/ARCH'

*.open_cursors=300

*.pga_aggregate_target=62914560

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=188743680

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/PRODASM/udump'

=======================================================================================

ON REMOTE SERVER(DEVASM) STARTUP DATABASE IN NOMOUNT MODE WITH HELP OF PROD INITFILE

=======================================================================================

DEVASM-> echo $ORACLE_SID

DEVASM

DEVASM->

SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initPRODASM.ora';

ORACLE instance started.

Total System Global Area 188743680 bytes

Fixed Size 1218436 bytes

Variable Size 62916732 bytes

Database Buffers 121634816 bytes

Redo Buffers 2973696 bytes

Database mounted.

------------------------------------------------------------------------

=======================================================================================

ON REMOTE SERVER(DEVASM) : CONVERT FILE AGAIN BACK TO REMOTE ASM (DEVASM)

=======================================================================================

devasm

run {

convert datafile '/u01/copy/undotbs1.dbf' format '+DG2';

convert datafile '/u01/copy/system.dbf' format '+DG2';

convert datafile '/u01/copy/users.dbf' format '+DG2';

convert datafile '/u01/copy/sysaux.dbf' format '+DG2';

}

--------------------------OUTPUT------------------------------------------

DEVASM-> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 15 20:59:20 2010

connected to target database: PRODASM (DBID=3528672201, not open)

RMAN> run {

convert datafile '/u01/copy/undotbs1.dbf' format '+DG2';

convert datafile '/u01/copy/system.dbf' format '+DG2';

convert datafile '/u01/copy/users.dbf' format '+DG2';

convert datafile '/u01/copy/sysaux.dbf' format '+DG2';

}2> 3> 4> 5> 6>

Starting backup at 15-AUG-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/copy/undotbs1.dbf

converted datafile=+DG2/prodasm/datafile/undotbs1.273.727131573

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:09

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/copy/system.dbf

converted datafile=+DG2/prodasm/datafile/system.274.727131575

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:12

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/copy/users.dbf

converted datafile=+DG2/prodasm/datafile/users.275.727131711

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08

Finished backup at 15-AUG-10

Starting backup at 15-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/copy/sysaux.dbf

converted datafile=+DG2/prodasm/datafile/sysaux.276.727131715

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25

Finished backup at 15-AUG-10

=======================================================================================

ON REMOTE SERVER(DEVASM) : CHANGE NAME OF DATAFILES IN CONTROLFILE

=======================================================================================

SQL> select MEMBER from v$logfile;

SQL> select NAME from v$datafile;

SQL> select NAME from v$tempfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/users.259.727034915' to '+DG2/prodasm/datafile/users.275.727131711';

ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/sysaux.257.727034911' to '+DG2/prodasm/datafile/sysaux.276.727131715';

ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/undotbs1.258.727034913' to '+DG2/prodasm/datafile/undotbs1.273.727131573';

ALTER DATABASE RENAME FILE '+DG1/prodasm/datafile/system.256.727034907' to '+DG2/prodasm/datafile/system.274.727131575';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

=======================================================================================

ON REMOTE SERVER(DEVASM) : DISABLE BLOCK CHANGE TRACKING

=======================================================================================

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-19751: could not create the change tracking file

ORA-19750: change tracking file:

'+DG1/prodasm/changetracking/ctf.267.727101715'

ORA-17502: ksfdcre:1 Failed to create file

+DG1/prodasm/changetracking/ctf.267.727101715

ORA-17501: logical block size 4294967295 is invalid

ORA-17503: ksfdopn:2 Failed to open file

+DG1/prodasm/changetracking/ctf.267.727101715

ORA-15001: diskgroup "DG1" does not exist or is not mounted

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '+DG2/prodasm/datafile/system.274.727131575'

=======================================================================================

ON REMOTE SERVER(DEVASM) : SHUTDOWN IMMEDIATE;

=======================================================================================

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

=======================================================================================

ON REMOTE SERVER(DEVASM) : MOUNT DATABASE AND OPEN IT NORMALLY

=======================================================================================

SQL> startup mount;

ORACLE instance started.

Total System Global Area 188743680 bytes

Fixed Size 1218436 bytes

Variable Size 62916732 bytes

Database Buffers 121634816 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> alter database open;

Database altered.

=======================================================================================

ON REMOTE SERVER(DEVASM) : CONTROLFILE AND SPFILE CREATED AUTOMATICALLY

=======================================================================================

SQL> show parameter CONTROL

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string +DG2/devasm/controlfile/current.260.727044801

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DG2/devasm/spfiledevasm.ora

All About Oracle...

Monday, August 23, 2010

oracle search: ASM CLONING

GOLDEN GATE LAG MONITORING

Monday, August 16, 2010

ASM CLONING