;; Calvin and Hobbes modules (defun kill-if-buffer-exists (bufname) (when (get-buffer bufname) (kill-buffer bufname))) (defun get-calvin () "Fetch the most recent Calvin image and show it in the buffer." (interactive) (save-excursion (let ((cmd "/usr/local/bin/wget -q -P /tmp/calvin http://calvinhobbesdaily.tumblr.com/rss > /dev/null 2>&1")) (shell-command "rm -rf /tmp/calvin") (kill-if-buffer-exists "rss") (kill-if-buffer-exists "*calvin*") (shell-command cmd) (find-file "/tmp/calvin/rss") (goto-char (point-min)) (when (search-forward-regexp "img src=\\\"\\\(.+?\\\)\\\"" (point-max) t 1) (message "%s" (match-string 1)) (shell-command (format "/usr/local/bin/wget -q -P /tmp/calvin %s > /dev/null 2>&1" (match-string 1))) (switch-to-buffer (get-buffer-create "*calvin*")) (setq cmdStr (concat "/usr/local/bin/convert -scale 200% -quality 85% " (car (directory-files "/tmp/calvin" t "gif")) " " (file-name-sans-extension(car (directory-files "/tmp/calvin" t "gif"))) "-c.gif")) (shell-command cmdStr) (insert-image (create-image (car (directory-files "/tmp/calvin" t "gif")))) (kill-if-buffer-exists "rss") (kill-if-buffer-exists "*Shell Command Output*")))))
DONT BOUNCE THAT DB
Stop-Start(Restart) is not the only way to fix problems. All code and scripts are on https://github.com/harry2040
Thursday, January 9, 2014
Calvin and Hobbes with Emacs
Tuesday, November 26, 2013
Features list of all databases
create or replace type clobagg_type as object( text clob, static function ODCIAggregateInitialize( sctx in out clobagg_type ) return number, member function ODCIAggregateIterate( self in out clobagg_type, value in clob ) return number, member function ODCIAggregateTerminate( self in clobagg_type, returnvalue out clob, flags in number ) return number, member function ODCIAggregateMerge( self in out clobagg_type, ctx2 in clobagg_type ) return number ); / create or replace type body clobagg_type is static function ODCIAggregateInitialize( sctx in out clobagg_type ) return number is begin sctx := clobagg_type(null) ; return ODCIConst.Success ; end; member function ODCIAggregateIterate( self in out clobagg_type, value in clob ) return number is begin self.text := self.text || value ; return ODCIConst.Success; end; member function ODCIAggregateTerminate( self in clobagg_type, returnvalue out clob, flags in number ) return number is begin returnValue := self.text; return ODCIConst.Success; end; member function ODCIAggregateMerge( self in out clobagg_type , ctx2 in clobagg_type ) return number is begin self.text := self.text || ctx2.text; return ODCIConst.Success; end; end; / create or replace function clobagg( input clob ) return clob deterministic parallel_enable aggregate using clobagg_type; /
I nicked the above package queries from Oracle forum; credits to BluShadow. Now for the query which pulls all the information from the OMR database.select trim(',' from clobagg(name||',')) as feature_name, host , database_name, target_type , instance_name , currently_used , max(first_usage_date) ,version as VERSION from mgmt$db_featureusage GROUP BY host, database_name, target_type, INSTANCE_NAME, CURRENTLY_USED, VERSION
Tuesday, November 12, 2013
Mass promotion of OEM12c targets
alter session set CURRENT_SCHEMA=SYSMAN;
For Oracle Databasesselect 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_database" -host="' || ManagementEntityEO.HOST_NAME || '" -credentials="UserName:dbsnmp;password:dbsnmp;Role:Normal" -properties="SID:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%SID')) || ';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%PORT')) || ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) || ';MachineName:' || ManagementEntityEO.HOST_NAME || ';"' FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO WHERE ManagementEntityEO.PROMOTE_STATUS=1 AND ManagementEntityEO.MANAGE_STATUS=1 AND ManagementEntityEO.ENTITY_TYPE!= 'host' AND ManagementEntityEO.ENTITY_TYPE='oracle_database' AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE AND (NOT EXISTS(SELECT 1 FROM mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND mtp.property_value='1'));
For Listenersselect 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_listener" -host="' || ManagementEntityEO.HOST_NAME ||'" -properties="LsnrName:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%LSNRNAME')) || ';ListenerOraDir:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%LISTENERORADIR')) || ';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%PORT')) || ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) || ';Machine:' || ManagementEntityEO.HOST_NAME || ';"' FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO WHERE ManagementEntityEO.PROMOTE_STATUS=1 AND ManagementEntityEO.MANAGE_STATUS=1 AND ManagementEntityEO.ENTITY_TYPE!= 'host' AND ManagementEntityEO.ENTITY_TYPE='oracle_listener' AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE AND (NOT EXISTS(SELECT 1 FROM mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND mtp.property_value='1'));
For Clustersselect 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="cluster" -host="' ||ManagementEntityEO.HOST_NAME || '" -monitor_mode="1" -properties="OracleHome:' || (SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) || ';eonsPort:' || (SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE 'EONSPORT%')) || ';scanName:' || (SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE 'SCANNAME%')) || ';scanPort:' || (SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE 'SCANPORT%')) || ';" -instances="' ||ManagementEntityEO.HOST_NAME||':host;'|| (select decode(b.host_name,null,'"',b.host_name||':host"') FROM SYSMAN.GC_MANAGEABLE_ENTITIES a , SYSMAN.GC_MANAGEABLE_ENTITIES b WHERE a.PROMOTE_STATUS=1 AND a.MANAGE_STATUS=1 AND a.ENTITY_TYPE!= 'host' AND a.ENTITY_TYPE='cluster' and b.HOST_NAME like REGEXP_REPLACE(a.HOST_NAME, '([a-z]+)[0-9].*', '\1%') and b.host_name <> a.host_name and b.entity_type='host' and a.host_name=ManagementEntityEO.host_name ) FROM SYSMAN.GC_MANAGEABLE_ENTITIES ManagementEntityEO,SYSMAN.MGMT_TARGET_TYPES ManagementEntityTypeEO WHERE ManagementEntityEO.PROMOTE_STATUS=1 AND ManagementEntityEO.MANAGE_STATUS=1 AND ManagementEntityEO.ENTITY_TYPE!= 'host' AND ManagementEntityEO.ENTITY_TYPE='cluster' AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE AND (NOT EXISTS(SELECT 1 FROM SYSMAN.mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND mtp.property_value='1'))
For RAC databasesselect 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="rac_database" -host="' || ManagementEntityEO.HOST_NAME || '" -monitor_mode="1" -properties="ServiceName:'|| (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%SERVICENAME%')) ||';ClusterName:'|| (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%CLUSTERNAME%')) ||'" -instances="'|| (select listagg(sub.entity_name,':oracle_database;') within group (order by sub.entity_name) FROM GC_MANAGEABLE_ENTITIES sub where sub.ENTITY_TYPE='oracle_database' and sub.entity_name like ManagementEntityEO.ENTITY_NAME||'%')||':oracle_database”' FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO WHERE ManagementEntityEO.PROMOTE_STATUS=1 AND ManagementEntityEO.MANAGE_STATUS=1 AND ManagementEntityEO.ENTITY_TYPE!= 'host' AND ManagementEntityEO.ENTITY_TYPE='rac_database' AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE AND (NOT EXISTS(SELECT 1 FROM mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND mtp.property_value='1'))
For ASM instancesselect 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="osm_instance" -host="' || ManagementEntityEO.HOST_NAME || '" -credentials="UserName:sys;password:sTroNGPassWd;Role:sysdba" -properties="OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) || ';MachineName:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%MACHINENAME')) || ';SID:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%SID%'))|| ';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%PORT%')) ||'"' FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO WHERE ManagementEntityEO.PROMOTE_STATUS=1 AND ManagementEntityEO.MANAGE_STATUS=1 AND ManagementEntityEO.ENTITY_TYPE!= 'host' AND ManagementEntityEO.ENTITY_TYPE='osm_instance' AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE AND (NOT EXISTS(SELECT 1 FROM mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND mtp.property_value='1'))
The output of this can be saved into an command argument file (in this example discover.dat) EMCLI can then be invoked to run this command file to mass promore targetsemcli login -username=OEMDBA -password=sTrOnGPassWd Login successful emcli sync Synchronized successfully emcli argfile discover.dat
Friday, November 8, 2013
Mass deployment of OEM12c agents
For deployment of 5 servers you might spend about 5 x 20mins =100mins. But imagine for 600 servers that is 200 hours.
So here is a wrapper script that will login to every server create a designated ORACLE_HOME two levels below the ORACLE_HOME of the database and then install the OEM agent on the server. It creates a sumfile(Summary file)
It also does some cool things like strip out the OEM 10g agent details from the inventory so that pre-requisites dont fail.
Im sure there are plenty of tweaks and improvements that can be done. Love to hear your thoughts.
Couple of things before we start
1. Create the named credentials for each of your password
./emcli create_named_credential
-cred_name=DBA_B0
-auth_target_type=host
-cred_type=HostCreds
-attributes="HostUserName:oracle;HostPassword:B0password"
#!/usr/bin/perl
use Crypt::Blowfish;
use strict;
use warnings;
# Password File - Blowfish encyrpted
open (PASSWDFILE, " > pass.dat") or die "Could not open pass.dat because $!";
my $key = pack("H16","0123456789ABCDEF"); # put your key here
my $crypt = new Crypt::Blowfish $key;
my $ciphertext = $crypt->encrypt('B0password'); # Your password here. Multiple lines of your passwords
print PASSWDFILE $decrypt->decrypt($ciphertext);
4. Run this from the OMS App server
#!/usr/bin/env perl
use strict;
use warnings;
use Net::OpenSSH;
use Crypt::Blowfish;
my $key = pack("H16","0123456789ABCDEF"); # Put your password file key here
my $cipher = new Crypt::Blowfish $key;
my $username = "oracle";
my $cmd = "uname -a";
my $count = 0;
my $flag=0; #Variable to check if last password was ok.
# Host file
open (HOSTFILE, "hosts.lstpreprod.full") or die "Could not open host.dat because $!";
# Password File - Blowfish encyrpted
open (PASSWDFILE, "pass.dat") or die "Could not open pass.dat because $!";
# Open the log file
open (LOGFILE, "> logfile.dat") or die "Could not open logfile.dat because $!";
# Open the summary file
open (SUMFILE, "> sumfile.dat") or die "Could not open logfile.dat because $!";
# Open the Server_pass file - Use this if you want to generate a host,passwd file for future references instead of trying new passwords
# open (SERVPASS, "> serv_pass.dat") or die "Could not open logfile.dat because $!";
# Loop the Hosts File
while (my $row =
#Bring the flag to reset
$flag=0;
# counter
$count = $count + 1;
chomp($row); #remove garbage characters at trailing or leading end.
seek(PASSWDFILE,0,0); #move marker to first line of Password file
print LOGFILE "-----------------------------------------------------------------\n";
print LOGFILE "Server - $row\n";
print SUMFILE "-----------------------------------------------------------------\n";
print SUMFILE "$count. Server - $row\n";
#Loop through the password file
while (my $epassword =
# Get the decrypted password
my $password = $cipher->decrypt($epassword);
my $ssh = Net::OpenSSH->new($row, user=>"oracle", passwd=>$password,strict_mode=>1, timeout => 10,kill_ssh_on_timeout => 1);
if($ssh->error)
{
if (($ssh->error =~ m/password/) || ($ssh->error =~ m/timeout/))
{
next;
}
else
{
print LOGFILE $ssh->error."\n";
last;
}
}
#If you are here it means that the password worked. Set the flag so that you skip to next host.
$flag=1;
#generate server password file- if you want to
#print SERVPASS $row . "," . $password;
#Get the Oracle home
my $cmd = "export DB_NAME=`ps -ef|grep pmon| grep -v grep| awk '{print \$8}'|cut -c10-\$NF|grep -v AS | sed 's/[1-3]\$//'` && grep -i \"^\$DB_NAME\" /etc/oratab | grep -v \"^#\" | grep -v \"^\*\"| grep \":\" | awk -F\":\" '{print \$2}'|uniq";
print LOGFILE $cmd;
# Get Oracle Home
my $ohome = $ssh->capture($cmd);
print LOGFILE "Oracle home is ".$ohome;
if ($ohome ne "") # No Oracle_HOME, then get out!
{
chomp($ohome);
$ssh->system('cd '.$ohome)
or print LOGFILE "Error getting to oracle home directory" and last;
#inventory - Rip of the existing Agent home from inventory, this to overcome pre-requisites.
my $inventory = $ssh-> capture("cat /etc/oraInst.loc | grep -i inventory_loc | awk -F= '{print \$2}'");
print LOGFILE "Inventory found to be " . $inventory . "\n";
chomp($inventory);
#Strip out agent home information
my $inventory_file = $inventory . "/ContentsXML/inventory.xml";
my $inventory_bfile = $inventory . "/ContentsXML/inventory.xml_bak";
my $inventory_afile = $inventory . "/ContentsXML/inventory.agent";
print LOGFILE "Inventory file ". $inventory_file;
print LOGFILE "\nInventory backupfile". $inventory_bfile;
print LOGFILE "\nInventory agent file". $inventory_afile . "\n";
# Create backup of inventory file
$ssh->system('cp '.$inventory_file. ' '.$inventory_bfile)
or print LOGFILE "Error backing up inventory file\n" and last;
#Strip the inventory agent details
my $cmd="awk \'BEGIN {IGNORECASE = 1; discard = 0;} \/\
$ssh->system($cmd)
or print LOGFILE "Error stripping agent inventory\n";
print LOGFILE "Command to strip the inventory ". $cmd . "\n";
#Send stripped data to another file
$cmd="awk 'BEGIN {IGNORECASE = 1; discard = 0;} \/\
$ssh->system($cmd)
or print LOGFILE "Error stripping agent inventory to another file\n";
print LOGFILE "Command to send the agent inventory to another file". $cmd . "\n";
# Create directory for OEM 12c agent home
$cmd="mkdir -p ". $ohome . "/../../agent12c";
# print LOGFILE "Command is ". $cmd. "\n";
$ssh->system($cmd)
or print LOGFILE "Error creating agent home directory" and last;
#switch to new directory
$cmd="cd ". $ohome . "/../../agent12c";
#print LOGFILE "Command is ". $cmd."\n";
$ssh->system($cmd)
or print LOGFILE "Error getting to agent home directory" and last;
my $ahome = $ssh->capture($cmd ."&& pwd"); # Get the Agent Oracle Home
print LOGFILE "Agent home is ". $ahome."\n";
chomp($ahome);
# Login to OEM 12c
print LOGFILE "Starting the OEM 12c part\n";
my $emcliop = `/app/oracle/Middleware12cr3/oms/bin/emcli login -username=MasterDBA -password=maximumSecurity 2>&1`;
print LOGFILE "Login result - ".$emcliop."\n";
if ($emcliop =~ m/successful/ )
{
# Sync with repository
$emcliop = `/app/oracle/Middleware12cr3/oms/bin/emcli sync`;
print LOGFILE "Sync result - ".$emcliop."\n";
if ($emcliop =~ m/successful/ )
{
# Sumbit the push job
my $passfrag = substr $password, 0, 2;
print LOGFILE "Shredded Password - ". $passfrag."\n";
#Sumbit the job
my $emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli submit_add_host -host_names=".$row." -platform=226 -port=3872 -installation_base_dir=".$ahome." -credential_name=DBA_".$passfrag." -session_name=\"Agent-".$row."\" -wait_for_completion";
print LOGFILE "Agent install command run is " .$emclicmd."\n";
$emcliop = `$emclicmd`;
print LOGFILE $emcliop;
#get the job status
$emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli get_add_host_status -session_name=\"Agent-".$row."\" -format=\"name:csv\" | awk -F, '{if(NR>3)print \$3,\$4,\$5,\$6}'";
print LOGFILE "Status Command run is " .$emclicmd."\n";
$emcliop = `$emclicmd`;
print LOGFILE $emcliop;
if ($emcliop =~ /Succeeded Succeeded Succeeded/)
{
print LOGFILE "All done! SUCCESS\n";
print SUMFILE "All done! SUCCESS\n";
}
else
{
print LOGFILE "Error in installing the agent- DBA to intervene\n";
print SUMFILE "Error in installing the agent - DBA to intervene\n";
#restore the backup inventory file
$ssh->system('mv '. $inventory_file .' '. $inventory_file .'.script')
or print LOGFILE "Error moving existing inventory file for restore";
$ssh->system('cp '.$inventory_bfile. ' '.$inventory_file)
or print LOGFILE "Error restoring the inventory from backup file" and last;
}
#logout irrespective of success or failure
$emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli logout";
$emcliop = `$emclicmd`;
print LOGFILE "Logout result - ".$emcliop."\n";
}
}
}
}
}
Sunday, July 28, 2013
Setting up High Availability for Golden Gate
The first step is to setup a virtual IP
The VIP needs to reside on the public network subnet. To find the subnet, you can do the below
crsctl stat res -p | grep -i .network
NETWORK = ora.net1.network
crsctl stat res -p | grep -i subnet
USR_ORA_SUBNET=10.2.170.128
Add the VIP to the /etc/hosts file
10.2.170.157 prdgg-vip.prd1.company.com prdgg-vip
Add the VIP into the cluster
GRID_HOME/bin/appvipcfg create -network=1 -ip=10.2.170.157 -vipname=prdgg-vip -user=root
usage: tail [+/-[n][lbc][f]] [file]
tail [+/-[n][l][r|f]] [file]
Production Copyright 2007, 2008, Oracle.All rights reserved
2013-05-29 13:51:03: Creating Resource Type
2013-05-29 13:51:03: Executing cmd: /u01/app/crs/11.2.0/grid/bin/crsctl add type app.appvip.type -basetype cluster_resource -file /u01/app/crs/11.2.0/grid/crs/template/appvip.type
2013-05-29 13:51:04: Create the Resource
2013-05-29 13:51:04: Executing cmd: /u01/app/crs/11.2.0/grid/bin/crsctl add resource prdgg-vip -type app.appvip.type -attr USR_ORA_VIP=10.2.170.157,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x'
Check if the VIP has been added into the cluster
GRID_HOME/bin/crsctl stat res prdgg-vip
NAME=prdgg-vip
TYPE=app.appvip.type
TARGET=OFFLINE
STATE=OFFLINE
Give Oracle user the privilege.
GRID_HOME/bin/crsctl setperm resource prdgg-vip -u user:oracle:r-x
Start the VIP
GRID_HOME/bin/crsctl start res prdgg-vip
CRS-2672: Attempting to start 'prdgg-vip' on 'prdnode2'
CRS-2676: Start of 'prdgg-vip' on 'prdnode2' succeeded
Try switching the VIP between the nodes
GRID_HOME/bin/crsctl relocate res prdgg-vip
CRS-2673: Attempting to stop 'prdgg-vip' on 'prdnode2'
CRS-2677: Stop of 'prdgg-vip' on 'prdnode2' succeeded
CRS-2672: Attempting to start 'prdgg-vip' on 'prdnode1'
CRS-2676: Start of 'prdgg-vip' on 'prdnode1' succeeded
GRID_HOME/bin/crsctl stat res prdgg-vip
NAME=prdgg-vip
TYPE=app.appvip.type
TARGET=ONLINE
STATE=ONLINE on prdnode1
Create the action script
#!/bin/sh
set -x
#GG auto restart
OGG_HOME=/u01/app/ogg/GG11g
export OGG_HOME;
start_delay_secs=5
#Include the GoldenGate home in the library path to start GGSCI
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${OGG_HOME}
#set the oracle home to the database to ensure GoldenGate will get the
#right environment settings to be able to connect to the database
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1
JAVA_HOME=/usr/java/default
PATH=$PATH:/usr/java/default/bin
#check_process validates that a manager process is running at the PID
#that GoldenGate specifies.
check_process () {
if ( [ -f "${OGG_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${OGG_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f2` ]
then
#manager process is running on the PID . exit success
exit 0
else
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f1` ]
then
#manager process is running on the PID . exit success
exit 0
else
#manager process is not running on the PID
exit 1
fi
fi
else
#manager is not running because there is no PID file
exit 1
fi
}
#call_ggsci is a generic routine that executes a ggsci command
call_ggsci () {
ggsci_command=$1
ggsci_output=`${OGG_HOME}/ggsci << EOF
${ggsci_command}
exit
EOF`
}
case $1 in
'start')
#start manager
call_ggsci 'start manager'
#there is a small delay between issuing the start manager command
#and the process being spawned on the OS . wait before checking
sleep ${start_delay_secs}
#Start the Extract and Replicat processes
call_ggsci 'start *'
#check whether manager is running and exit accordingly
check_process
;;
'stop')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'check')
check_process
;;
'clean')
#attempt a clean stop for all non-manager processes
call_ggsci 'stop er *'
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
'abort')
#ensure everything is stopped
call_ggsci 'stop er *!'
#in case there are lingering processes
call_ggsci 'kill er *'
#stop manager without (y/n) confirmation
call_ggsci 'stop manager!'
#exit success
exit 0
;;
esac
[prdnode1.prd1.company: /u01/oraogg/prdnode/scripts] ls -ltr 11gr2_gg_action.scr
-rwxr-xr-x 1 oracle oinstall 2353 May 29 13:59 11gr2_gg_action.scr
Add the GoldenGate Application to the cluster
GRID_HOME/bin/crsctl add resource ggateapp -type cluster_resource -attr "ACTION_SCRIPT=/u01/oraogg/prdnode/scripts/11gr2_gg_action.scr,CHECK_INTERVAL=30, START_DEPENDENCIES='hard(prdgg-vip)pullup(prdgg-vip)', STOP_DEPENDENCIES='hard(prdgg-vip)'"
Check the GoldenGate Application in the cluster
GRID_HOME/bin/crsctl stat res ggateapp -p
NAME=ggateapp
TYPE=cluster_resource
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=/u01/oraogg/prdnode/scripts/11gr2_gg_action.scr
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/scriptagent
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=30
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION=
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=balanced
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=1
SCRIPT_TIMEOUT=60
SERVER_POOLS=
START_DEPENDENCIES=hard(prdgg-vip)pullup(prdgg-vip)
START_TIMEOUT=0
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(prdgg-vip)
STOP_TIMEOUT=0
UPTIME_THRESHOLD=1h
Start the cluster resource on the node where GoldenGate is running.
GRID_HOME/bin/crsctl start resource ggateapp -n prdnode1
CRS-2672: Attempting to start 'ggateapp' on 'prdnode1'
CRS-2676: Start of 'ggateapp' on 'prdnode1' succeeded
Check if it running
GRID_HOME/bin/crsctl stat res ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on prdnode1
Tuesday, June 4, 2013
OGG-01031 Pump Abending
In this scenario, simply clearing space on the drive will not restart the pump.
The pump complains as below
2013-05-14 10:41:42 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, p1ppppd1.prm: EXTRACT P1PPPPD1 started.
2013-05-14 10:41:47 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, p1ppppd1.prm: Socket buffer size set to 27985 (flush size 27985).
2013-05-14 10:41:58 ERROR OGG-01031 Oracle GoldenGate Capture for Oracle, p1ppppd1.prm: There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Unable to open file "/u01/oraogg/dirdat/rp/rp000014" (error 13, Permission denied)).
2013-05-14 10:41:58 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, p1ppppd1.prm: PROCESS ABENDING.
alter extract P1PPPPD1 etrollover
2013-05-14 10:57:19 INFO OGG-01520 Oracle GoldenGate Command Interpreter for Oracle: Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
Run info pumpname to get the new trail file name.
At the target end, the replicat should have parsed all the data received until the drive became full and data stopped coming through.
To let the target know to switch to the new file, use below command.
alter replicat R1PPPPD2 ,EXTSEQNO 15 , EXTRBA 0 --- replicat was reading the seqno 14. moved to 15.
Wednesday, April 24, 2013
LUNs size and ASM
for i in `ls /dev/oracleasm/disks`
do
fp=$(/etc/init.d/oracleasm querydisk -p $i |grep /dev/mapper| awk -F ":" '{ print $1 }')
echo "----------$i--------------"
echo "$fp"
p=$(echo "$fp"| awk -F "/" '{ print substr($4,1, length($4) - 2) }')
sudo /sbin/multipath -ll| grep -A 1 $p|awk '{ print $1 }'
done;