Monday, August 23, 2010

oracle search: ASM CLONING

Links to this post
oracle search: ASM CLONING: "Links to this post"

GOLDEN GATE LAG MONITORING

Links to this post
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

Monday, August 16, 2010

ASM CLONING

Links to this post
------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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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
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