Calvin and Hobbes with Emacs

I am a big fan of Calvin and Hobbes and wanted to see one comic strip every day when I started my emacs.
You have to install imagemagick to resize the comic strip before displaying.

;; 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*")))))

Features list of all databases

Did you ever want to know what all features you are actually using in your databases, and what features are unnecessarily installed in your databases?
OEM holds all the this information. You just need the right query.
In this example, I had to hack the SQL instead of using LISTAGG since the database was pre 11.2 version.

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

Mass promotion of OEM12c targets

Once you have all the OEM12c agents installed on the servers, the next step is to get all the targets added on to OEM12c. OEM12c auto gathers the properties of the targets that you configure the agents to collect data on. Once done, you should see all the targets under auto configure results. The results shoudl include database instances, listeners, oracle homes, clusters, oracle HAS, rac databases.
There is no option to mass promote targets from OEM12c console so that these targets can be then monitored.

However emcli can run script files with multiple commands and a SQL can be used to generate the commands.

To generate the SQL commands, connect to the OMS database and run below SQLs to generate the commands.

alter session set CURRENT_SCHEMA=SYSMAN;

For Oracle Databases

select '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 Listeners

select '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 Clusters

select
'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 databases

select '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 instances

select '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 targets

emcli login -username=OEMDBA -password=sTrOnGPassWd
Login successful

emcli sync
Synchronized successfully

emcli argfile discover.dat

Mass deployment of OEM12c agents

Oracle Enterprise Manager has a cool feature that allows mass deployment of agents across multiple hosts. It has certain limitations ; for instance it cannot use dynamic paths for ORACLE_HOME locations.
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"

2. Create a password file with your password file. Encrypt it with Blowfish(optional).

#!/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);

3. Have a list file with all the server names.

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 = and $flag==0) {
# 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;} \/\ ' .$inventory_file;


$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;} \/\ ' .$inventory_afile;

$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";
}
}
}
}
}

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

OGG-01031 Pump Abending

A fairly common occurance in the Golden Gate world. The pump abends as the target destination becomes full.
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.

We tell the pump to Rollover onto a new trace file.

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.

LUNs size and ASM

It is fairly straight forward to query ASM instance to find which path and what their sizes are. I found myself increasingly looking at a fallen ASM instance(storage configuration problems) and trying to figure out which disk belongs to which LUN and what size it is.
After doing this for 2 times manually; I had to write a script to do this.

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;

It goes without saying, you need to customize for your environment and setup. Here it is Linux multipath.

Data Guard Real time apply check

Physical standby can be in two modes. Real-time apply or Archive log shipping mode.
In real-time apply mode, the LGWR from the primary writes to the standby redo log at the target site.
Comparing archive log sequence numbers is not the correct way to check if the dataguard is operating correctly.
I have written the below script to check if the real time apply is working properly.

This works on the dataguard physical standby database.
It checks two columns (value and Datum_time) in V$dataguard_stats.

#!/bin/bash
################################################################################
## File Name : /adg_lag_check.sh #
## Description : Job to check gap in real time apply for DG #
## Author : Harris #
## Call Syntax : No parameters, writes local log file #
## Last Modified: 28/Mar/2013 By Harris #
################################################################################
export ORACLE_HOME=/u01/app/mydb01/oracle/product/11.2.0.3/db
export ORACLE_SID=mydb01
export PATH=$ORACLE_HOME/bin:$PATH
DATE=`date '+%m%d%Y%H%M%S'`
DBA_GROUP='harris@company.com'
sqlplus -s / as sysdba << EOF > adg_lag.log
set head off
set numf 99999999999
set feedback off
set echo off
set serveroutput on
select CASE WHEN
((extract(second from to_dsinterval(value)) + extract(minute from to_dsinterval(value)) * 60
+ extract(hour from to_dsinterval(value)) *60*60 + extract(day from to_dsinterval(value)) *60*60*24)
> 900) THEN 'Critical Data Guard lag more than 15mins'
WHEN value is null THEN ' Critical Data Broken status'
WHEN (((sysdate - to_date(DATUM_TIME,'MM/DD/YYYY HH24:MI:SS'))*24*60) > 15) THEN 'Critical Data Guard Network broken'
ELSE 'Data Guard OK'
END
from v\$dataguard_stats where name='apply lag';

EOF

if grep -c 'Critical' adg_lag.log; then
cat adg_lag.log | mailx -s "DataGuard Problem" $DBA_GROUP
fi;

Redo logs on Physical Standby

Redo logs on Physical Standby Online redo logs on a physical standby database is not really required. For real-time apply , standby redo logs are a must on standby site.

Standby redo logs are optional at the source site, but always advisable to be prepared for switchover or failovers.

If DB_CREATE_ONLINE_LOG_DEST_1 is not set and
DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST is specified,
the redo logs are created multiplexed each under DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST

If you need to move the redo log to another diskgroup or another filesystem on standby database, these are the steps

--cancel the recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--change the Standby File Management
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

--Clear the redo logs
ALTER DATABASE CLEAR LOGFILE GROUP 2;

--Drop the redo logs
ALTER DATABASE DROP LOGFILE GROUP 2;

--Add the new logs at the correct locations
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+REDO_DG1','+REDO_DG2') size 1024M;

-- If you need to add standby redo logs, do so...
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+REDO_DG','+REDO_DG2') size 1024M;

Put the standby database back into action

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

EMACS and SQLPLUS

I have recently discovered EMACS thanks to the unfaultering effort by my co-brother Roy. I have only one word for EMACS- fan-"bleedy"-tastic.
Although not very intuitive, it is immensely powerful and extremely great to improve productivity.

I will break this blog into three sections for easier reading.

Section 1 - How does this fit into my work?

---

Remember the days spent with a SQL in a notepad, and server has no scripts. The day drags on with you running Ctrl+C, Ctrl+V , then right click into the Putty Console.


Now here is how it happens in Emacs.

So start the SQL-Plus buffer in Emacs. This should be inbuilt inside the 24.2 version onwards.

M-x sql-oracle
It should prompt for a userid, password and host name. Hostnames is the TNS names entry in your local tnsnames.ora file.

If you get the below error, like I did
ORA-12557: TNS:protocol adapter not loadable

Set ORACLE_HOME and PATH to same Oracle home(I have two Oracle Homes in my laptop) .Restart EMacs
Try M-x sql-oracle, again
This time hopefully you should get connected.
You are on the command prompt so you try any of the SQLs.

Now the interesting bit of working efficiently.
C-X, C-f

Then enter a existing or new SQL file name.

Your screen should split with the SQL file and the SQLPlus buffer in another.

Use C- to set the mark and move the keys to select the whole SQL

To send the selected SQL into SQLPlus buffer use the C-C C-r

Section 2 - Errors I encountered

---

Some of the errors I encountered were --

If you get the below error,

No SQL process started

You can try below

M-x sql-set-sqli-buffer
select *SQL* ( should be the default)

That lead me to another error

There is not suitable SQLi buffer

To fix this error, note the bottom of the opened SQL file. The status bar should say SQL[Oracle].

If it says SQL[ANSI], it needs to be changed by doing

M-x sql-set-product
key in "oracle"

Now the "No SQL process started" error can be fixed by using the M-x sql-set-sqli-buffer like above.

Now use C-X C-r to send the SQL from the file to the SQL buffer.

Section 3 - Couple of other hacks.

---

You must have noticed that pressing the backspace deletes the "SQL>" prompt. To fix this , add this to your .emacs file

(setq comint-prompt-regexp "^SQL>")

(custom-set-variables
'(comint-prompt-read-only t)
'(comint-use-prompt-regexp t))
(custom-set-faces)

---

PS:

This blog was written in EMACS

Roy's awesome blog on technology can be found here

DataGuard ReadOnly to Apply and back

Dataguard and Reporting Before the days of Active Dataguard, were the days of read-only reporting off the physical standby. It pains to see data-centers with production like hardware and unused physical standby databases doing nothing but receive redo content once every few hours. Active Dataguard is a boon in that way.

In older versions, reporting environments typically received all the archive logs through the day and then, applied them overnight. The database is available through the day as read only , for querying and taken offline once a day for applying the logs and bringing back online to read-only mode.

Below is a shell script to do exactly that.

Its self explanatory

#!/bin/ksh
################################################################
################################################################
########## AUTHOR          : Harris
########## DATE            : 13 Aug 2012
########## VERSION         : 1.0
########## DESCRIPTION     :This script is used to stop physical
#### standby in readonly mode, apply the logs and bring back
##### online
################################################################
# VERSION |    DATE    |   Author   | DETAIL OF CHANGES
#---------------------------------------------------------------
# V0.1    | 08/13/2012 |Harris Baskaran |Initial Script
################################################################
################################################################
set -x
ORATAB=/var/opt/oracle/oratab
##Entries to update##########
export ORACLE_SID=STANDBYTNS
export CONNECTSTR=dg/dg@PRODTNS
export ENVIRON=DB service name
#####Review before editing parameters below this

export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
export ORACLE_HOME=`grep -i "$ORACLE_SID:" $ORATAB | grep -v "^#" | grep -v "^\*"| grep ":" | awk -F":" '{print $2}'`
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

touch error.log
touch shutdown_report.log
touch apply_report.log
touch check_report.log
touch switch2ro.log
touch handshake.log


## Email functionality

mail_it_to_all()
{
#Email the report
SEND_DATE=`date`
RECIPIENTS=dba@company.com
MESSAGE_TITLE="Reporting DB ${ORACLE_SID} on ${ENVIRON} ${SEND_DATE}"
/usr/bin/mailx -s "${MESSAGE_TITLE}" ${RECIPIENTS}  << EOF
${SEND_DATE}
`cat error.log`
`cat shutdown_report.log`
`cat apply_report.log`
`cat check_report.log`
`cat switch2ro.log`
`cat handshake.log`
EOF
}


##Find Sequence of logs shipped
log_shipping_check()
{
sqlplus -s -l / as sysdba  << LOGSHIP
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
set heading off
SELECT SEQUENCE# FROM V\$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#);
exit
LOGSHIP
}

## function to get Production Sequence
runsqlprod()
{
sqlplus -s -l ${CONNECTSTR}  << PRDCHK
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
set heading off
SELECT distinct SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#);
exit
PRDCHK
}

## function to get Standby Sequence
runsqlstdby()
{
sqlplus -s -l / as sysdba  << STDCHK
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
set heading off
select max(sequence#) from V\$ARCHIVED_LOG where applied='YES';
exit
STDCHK
}

shutdown_db()
{
sqlplus -s -l / as sysdba <<E1
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
spool shutdown_report.log
select name, open_mode from V\$database;
select username, machine from v\$session where status='ACTIVE';
shutdown immediate;
spool off
exit
E1
}

apply_logs()
{
sqlplus -s -l / as sysdba <<E2
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
spool apply_report.log
startup nomount;
alter database mount standby database;
recover managed standby database using current logfile disconnect from session;
spool off
exit
E2
}


#### custom handshake function with whatever reporting job you run
handshake()
{
sqlplus -s -l / as sysdba <<E4
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
##Replace with code here
##@handshake.sql
exit
E4
}

switch_to_ro()
{
sqlplus -s -l / as sysdba <<E2
whenever sqlerror exit sql.sqlcode;
whenever oserror exit FAILURE
spool switch2ro.log
recover managed standby database cancel;
alter database open read only;
spool off
exit
E2
}



##Find out if the logs are shipped already, if not no point continuing

PRODSEQ=`runsqlprod`
SHIPPED=`log_shipping_check`

if [ ${SHIPPED} -lt ${PRODSEQ} ]
then
echo "The "${ENVIRON}" Reporting and Source Logs are not shipped through the day. Need DBA intervention" >> error.log
echo "Production sequence is "${PRODSEQ} >> error.log
echo "Standby Sequence shipped is "${SHIPPED} >> error.log
mail_it_to_all()
exit 1
fi




####Shutdown the reporting to allow log application

shutdown_db

if grep -c 'ORA-' shutdown_report.log
then
echo ${ENVIRON}"-Error in bringing down reporting database..."
mail_it_to_all
exit 1
else
echo ${ENVIRON}"-Reporting Shutdown..."
fi


####Apply the archive logs and bring to current

apply_logs

if grep -c 'ORA-' apply_report.log
then
echo ${ENVIRON}"-Error in applying the logs on reporting database..."
mail_it_to_all
exit 1
else
echo ${ENVIRON}"-Reporting Logs Applying..."
fi




####Ensure it is synched with Production



### Sleep 15 minutes 10 times until Standby catches up with Prod site
### Increase freq as required


for i in 1 2 3 4 5 6 7 8 9 10
do
STDBSEQ=`runsqlstdby`
if [ ${STDBSEQ} -lt ${PRODSEQ} ]
then
sleep 60
else
break
fi
done


## If catch up still doesnt happen, send email and die!
if [ ${STDBSEQ} -lt ${PRODSEQ} ]
then
echo "The ${ENVIRON} Reporting and Source could not be synched. Need DBA intervention" >> error.log
echo "Production sequence is ${PRODSEQ} " >> error.log
echo "Standby Sequence is ${STDBSEQ} " >> error.log
mail_it_to_all()
exit 1
EEOF
fi

## Bring to read only

switch_to_ro

####Handshake with Application

#handshake()

if grep -c 'ORA-' handshake.log
then
echo ${ENVIRON}"-Error in handshaking with reporting module..."
mail_it_to_all
exit 1
else
echo ${ENVIRON}"-Reporting handshake done..."
fi

mail_it_to_all
mkdir `date "+%m%d%y"`
/usr/bin/mv *.log `date "+%m%d%y"`\/.

SCN headroom and CVE-2012-0082

CVE-2012-0082 is a security flaw in Oracle Databases; which manifests in some scenarios which can significantly increase the rate SCN increases. InfoWorld also has documented the problem in details - InfoWorld(http://www.infoworld.com/d/security/fundamental-oracle-flaw-revealed-184163-0)
Oracle’s CPU2012Jan addresses this problem. Oracle Document 1374524.1

To understand the problem it is necessary to define SCN headroom.
The SCN value is an ever increasing value for a database. The threshold for this SCN value is also ever increasing number too. The max limit of SCN is defined by the number of seconds past the year 1988 X 16k, hence as time passes this value also keeps increasing. This is the soft-limit. There is also a hard limit of 218 trillion.

It is also necessary to understand that when a DB link operation between two databases, Oracle automatically selects the higher SCN out of the two databases and syncs them between the two databases.

--On first database
SQL> select name from V$database;

NAME
---------
Database1

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
398829972857 -- note the SCN number



--On second database
SQL> select name from V$database;

NAME
---------
Database2

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
391409782850


SQL> select sysdate from dual@Database1.WORLD; -- DB link query

SYSDATE
---------
19-JAN-12



SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
398829973230 -- notice the significant increase for synchronizing the SCNs

InfoWorld also documents that Oracle Hot backups can significantly increase the scn generation. To find the headroom at any given time use the below query.

select
version,
to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') CURR_TIME,
((((
((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) + ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) + (to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) + (to_number(to_char(sysdate,'SS')))) * (16*1024))
- dbms_flashback.get_system_change_number)/ (16*1024*60*60*24)) scn_headroom
from v$instance;

See Oracle document : ID 1393363.1
Reading through Oracle’ patch if the headroom is less than 62, urgent attention is required. i.e. Apply patch immediately and contact Oracle.
To ensure that the headroom is not being reached rapidly, run the below query.

select max(first_change#)-min(first_change#), to_char(first_time,'YYYY-MM-DD') from V$log_history
group by to_char(first_time,'YYYY-MM-DD')
order by 2 desc;

Sometimes there may be a significant jump in the SCN which can be associated with a DBlink activity. Hot backups can also significantly increase the SCN generation rate as detailed by InfoWorld
If you notice that the SCN rate is very high there is plenty to worry about. If the high SCN rate database is interconnected via DBlink with other databases it can bring down a lot of databases together.
Along with the patch, Oracle have also added a hidden parameter _external_scn_rejection_threshold_hours which needs to be set to 24. There is not much details about this hidden parameter , but it might have something to do with the distributed transaction SCN synch.
As always please ensure you have read and understood the Oracle documentation before applying.

Quiescing a Database

There are times when a DBA needs to perform work on the database that requires no other ACTIVE sessions. This is very common is development databases, where a DBA tries to run a package or a modify a table when the application is till running.
There are also frequent occurances where too much IT support folks time is spent in bringing lot of application servers down to perform a deployment or some database maintainence. That is where Quiescing comes in.
To put a database(run on one instance and it affects all the instances of the RAC database).

ALTER SYSTEM QUIESCE RESTRICTED;

Quiescing puts the database in mode that does not allow any further connections to become ACTIVE state. It would however wait for all ACTIVE connections to become INACTIVE or terminate. Gladly Oracle provides a query to find sessions which are stopping it from enter into QUIESCE mode.

select bq.sid, ss.user, ss.osuser, ss.type, ss.program
from v$blocking_quiesce bq, v$session ss
where bq.sid = ss.sid;

Once in QUIESCE mode, DBA connections are the only ones that are allowed to be in ACTIVE state. The DBA can then easily lock any object to perform his tasks.

Once done to release the QUIESCE;
the DBA runs

ALTER SYSTEM UNQUIESCE;

What ever you do DBAs, for heaven sake just dont bounce that db unnecessarily.

Adaptive Cursor Sharing

Oracle 11g has introduced the new mode of cursor sharing to handle bind peeking.
Bind-peeking is the occurrence of shared cursors leading to non-optimal explain plans.
For eg.

A SQL "select name from emp where dept=:B". This SQL is executed from a package which is called multiple times for different values.
On the first execution, the cursor are cached in the shared pool. The first execution is

'select name from emp where dept=5'

and returns 3 rows. This execution plan involves a index which is optimal give that only 3 out of 253 rows are returned.

On the second execution, the cached cursor gets reused.

'select name from emp where dept=10'

But returns 250 rows out of 253 rows in the table. But sadly the same execution plan gets called because the cursor is shared.

In 11g, Oracle uses an adaptive cursor sharing where Oracle creates child cursors for the matching cursors in the shared pool. This then checks for any optimizations that can be done to plan apart from the parent cursor.
Hence when upgrading its necessary to have some extra memory allocations for the shared pool. In Oracle 11g, cursor_sharing should only be used in two modes EXACT and FORCE.

EXACT mode, this is the default in Oracle 11g. Oracle creates child cursors for SQLs which differ in bind variables. This is desirable approach. The application is making best use of bind variables and Oracle is making sure there is no bind peeking.

In FORCE mode, Oracle forces reuse of the cursors. This is best for legacy applications bind variables are not effectively used.
In this mode Oracle, forces SQLs with similar literal and binds to reuse the same cursors. It does not generate child cursors for different bind variables.

The SIMILAR mode although available in Oracle 11g is not recommended as it forces Oracle to replace literals with binds and allow similar child cursors. Oracle also says it heavily impacts the number of child cursor creation and upsets the optimizer operations.

Although there is not explicit child cursor limit(can be controlled with _ parameter), they can fill up your shared pool quiet rapidly with one bad SQL. Oracle reckons that excessive child cursors may be more detrimental to performance than using separate cursors. We noticed that SIMILAR floods the shared pool and leads to some bugs like Bug 11657468 - Excessive mutex waits with adaptive cursor sharing .

Thanks to Senthil for assisting in the research

IOT and Upgrade

IOTs created when the DB is lesser than 9.2 compatible and then later upgraded to 10g and above, they become corrupt. We noticed this problem in a different area; in Advanced Queues.
Queue tables by default have some underlying attached to them.

Oracle documents them as
AQ$_"queue_table"_I (the dequeue IOT)
AQ$_"queue_table"_T (the time-management IOT)
AQ$_"queue_table"_S (the subscriber table)
AQ$_"queue_table"_H (the history IOT)

After an upgrade we noticed that the queues just would not function until they were dropped and recreated.
This seemed a little ruthless so we then exported the queue tables along with these IOTS.
Then dropped the queues and tables in the upgraded system and imported them.
Easy fix :)

Adding a node to a 11gr2 cluster

Pre-checks

cluvfy stage -pre nodeadd -n NODEc -verbose
cluvfy comp peer -refnode NODEb -n NODEc -verbose
cluvfy stage -post hwos -n NODEc -verbose

Only if the above return successful , we can proceed with the next steps. In the above, space comparisions mismatches can be ignored.
You may get SEVERE error:Java NullPointerException if firewall is turned on. Remember to turn
Remember to set XDISPLAY before you start

First the clusterware has to be replicated over to the new node.

oracle@NODEa:/apps/product/11gr2/grid/oui/bin ./addNode.sh "CLUSTER_NEW_NODES={NODEc}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={NODEc-vip}"

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "NODEa"


Checking user equivalence...
User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "bge0"
Node connectivity passed for interface "bge0"

Node connectivity check passed


Checking CRS integrity...

CRS integrity check passed

Checking shared resources...

Checking CRS home location...
The location "/apps/product/11gr2/grid" is not shared but is present/creatable on all nodes
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "bge0"
Node connectivity passed for interface "bge0"

Check: Node connectivity for interface "bge1"
Node connectivity passed for interface "bge1"

Check: Node connectivity for interface "ce1"
Node connectivity passed for interface "ce1"

Node connectivity check passed

Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "NODEc:/var/tmp/"
Free disk space check passed for "NODEa:/var/tmp/"
Check for multiple users with UID value 2001 passed
User existence check passed for "oracle"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "project.max-sem-ids"
Kernel parameter check passed for "process.max-sem-nsems"
Kernel parameter check passed for "project.max-shm-memory"
Kernel parameter check passed for "project.max-shm-ids"
Kernel parameter check passed for "tcp_smallest_anon_port"
Kernel parameter check passed for "tcp_largest_anon_port"
Kernel parameter check passed for "udp_smallest_anon_port"
Kernel parameter check passed for "udp_largest_anon_port"
Package existence check passed for "SUNWarc-...( sparc)"
Package existence check passed for "SUNWbtool-...( sparc)"
Package existence check passed for "SUNWhea-...( sparc)"
Package existence check passed for "SUNWlibm-...( sparc)"
Package existence check passed for "SUNWlibms-...( sparc)"
Package existence check passed for "SUNWsprot-...( sparc)"
Package existence check passed for "SUNWtoo-...( sparc)"
Package existence check passed for "SUNWi1of-...( sparc)"
Package existence check passed for "SUNWi1cs-...( sparc)"
Package existence check passed for "SUNWi15cs-...( sparc)"
Package existence check passed for "SUNWxwfnt-...( sparc)"
Package existence check passed for "SUNWlibC-...( sparc)"
Package existence check passed for "SUNWcsl-...( sparc)"
Operating system patch check passed for "Patch 119963-14"
Operating system patch check passed for "Patch 120753-06"
Operating system patch check passed for "Patch 139574-03"
Operating system patch check passed for "Patch 141414-02"
Check for multiple users with UID value 0 passed
Current group ID check passed

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "xntpd"
Check for NTP daemon or service alive passed on all nodes

NTP daemon slewing option check passed

NTP daemon's boot time configuration check for slewing option passed

NTP common Time Server Check started...
Check of common NTP Time Server passed

Clock time offset check from NTP Time Server started...
Clock time offset check passed

Clock synchronization check using Network Time Protocol(NTP) passed


User "oracle" is not part of "root" group. Check passed
Checking consistency of file "/etc/resolv.conf" across nodes

File "/etc/resolv.conf" does not have both domain and search entries defined
domain entry in file "/etc/resolv.conf" is consistent across nodes
search entry in file "/etc/resolv.conf" is consistent across nodes
All nodes have one search entry defined in file "/etc/resolv.conf"
The DNS response time for an unreachable node is within acceptable limit on all nodes

File "/etc/resolv.conf" is consistent across nodes

Checking VIP configuration.
Checking VIP Subnet configuration.
Check for VIP Subnet configuration passed.
Checking VIP reachability
Check for VIP reachability passed.

Pre-check for node addition was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 13193 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Oracle Universal Installer, Version 11.2.0.2.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.


Performing tests to see whether nodes NODEb,NODEc are available
............................................................... 100% Done.

...
-----------------------------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /apps/product/11gr2/grid
New Nodes
Space Requirements
New Nodes
NODEc
/: Required 11.39GB : Available 33.37GB
Installed Products
Product Names
Oracle Grid Infrastructure 11.2.0.2.0
Sun JDK 1.5.0.24.08
Installer SDK Component 11.2.0.2.0
Oracle One-Off Patch Installer 11.2.0.0.2
Oracle Universal Installer 11.2.0.2.0
Oracle USM Deconfiguration 11.2.0.2.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Enterprise Manager Common Core Files 10.2.0.4.3
Oracle DBCA Deconfiguration 11.2.0.2.0
Oracle RAC Deconfiguration 11.2.0.2.0
Oracle Quality of Service Management (Server) 11.2.0.2.0
Installation Plugin Files 11.2.0.2.0
Universal Storage Manager Files 11.2.0.2.0
Oracle Text Required Support Files 11.2.0.2.0
Automatic Storage Management Assistant 11.2.0.2.0
Oracle Database 11g Multimedia Files 11.2.0.2.0
Oracle Multimedia Java Advanced Imaging 11.2.0.2.0
Oracle Globalization Support 11.2.0.2.0
Oracle Multimedia Locator RDBMS Files 11.2.0.2.0
Oracle Core Required Support Files 11.2.0.2.0
Bali Share 1.1.18.0.0
Oracle Database Deconfiguration 11.2.0.2.0
Oracle Quality of Service Management (Client) 11.2.0.2.0
Expat libraries 2.0.1.0.1
Oracle Containers for Java 11.2.0.2.0
Perl Modules 5.10.0.0.1
Secure Socket Layer 11.2.0.2.0
Oracle JDBC/OCI Instant Client 11.2.0.2.0
Oracle Multimedia Client Option 11.2.0.2.0
LDAP Required Support Files 11.2.0.2.0
Character Set Migration Utility 11.2.0.2.0
Perl Interpreter 5.10.0.0.1
PL/SQL Embedded Gateway 11.2.0.2.0
OLAP SQL Scripts 11.2.0.2.0
Database SQL Scripts 11.2.0.2.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
SSL Required Support Files for InstantClient 11.2.0.2.0
SQL*Plus Files for Instant Client 11.2.0.2.0
Oracle Net Required Support Files 11.2.0.2.0
Oracle Database User Interface 2.2.13.0.0
RDBMS Required Support Files for Instant Client 11.2.0.2.0
RDBMS Required Support Files Runtime 11.2.0.2.0
XML Parser for Java 11.2.0.2.0
Oracle Security Developer Tools 11.2.0.2.0
Oracle Wallet Manager 11.2.0.2.0
Enterprise Manager plugin Common Files 11.2.0.2.0
Platform Required Support Files 11.2.0.2.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
RDBMS Required Support Files 11.2.0.2.0
Oracle Ice Browser 5.2.3.6.0
Oracle Help For Java 4.2.9.0.0
Enterprise Manager Common Files 10.2.0.4.3
Deinstallation Tool 11.2.0.2.0
Oracle Java Client 11.2.0.2.0
Cluster Verification Utility Files 11.2.0.2.0
Oracle Notification Service (eONS) 11.2.0.2.0
Oracle LDAP administration 11.2.0.2.0
Cluster Verification Utility Common Files 11.2.0.2.0
Oracle Clusterware RDBMS Files 11.2.0.2.0
Oracle Locale Builder 11.2.0.2.0
Oracle Globalization Support 11.2.0.2.0
Buildtools Common Files 11.2.0.2.0
Oracle RAC Required Support Files-HAS 11.2.0.2.0
SQL*Plus Required Support Files 11.2.0.2.0
XDK Required Support Files 11.2.0.2.0
Agent Required Support Files 10.2.0.4.3
Parser Generator Required Support Files 11.2.0.2.0
Precompiler Required Support Files 11.2.0.2.0
Installation Common Files 11.2.0.2.0
Required Support Files 11.2.0.2.0
Oracle JDBC/THIN Interfaces 11.2.0.2.0
Oracle Multimedia Locator 11.2.0.2.0
Oracle Multimedia 11.2.0.2.0
HAS Common Files 11.2.0.2.0
Assistant Common Files 11.2.0.2.0
PL/SQL 11.2.0.2.0
HAS Files for DB 11.2.0.2.0
Oracle Recovery Manager 11.2.0.2.0
Oracle Database Utilities 11.2.0.2.0
Oracle Notification Service 11.2.0.2.0
SQL*Plus 11.2.0.2.0
Oracle Netca Client 11.2.0.2.0
Oracle Net 11.2.0.2.0
Oracle JVM 11.2.0.2.0
Oracle Internet Directory Client 11.2.0.2.0
Oracle Net Listener 11.2.0.2.0
Cluster Ready Services Files 11.2.0.2.0
Oracle Database 11g 11.2.0.2.0
-----------------------------------------------------------------------------


Instantiating scripts for add node (Monday, 5 September 2011 11:46:03 o'clock BST)
. 1% Done.
Instantiation of add node scripts complete

Copying to remote nodes (Monday, 5 September 2011 11:46:15 o'clock BST)
............................................................................................... 96% Done.
Home copied to new nodes

Saving inventory on nodes (Monday, 5 September 2011 11:55:39 o'clock BST)
. 100% Done.
Save inventory complete
WARNING:A new inventory has been created on one or more nodes in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script at '/apps/oracle/home/oraInventory/orainstRoot.sh' with root privileges on nodes 'NODEc'.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user in each cluster node.
/apps/oracle/home/oraInventory/orainstRoot.sh #On nodes NODEc
/apps/product/11gr2/grid/root.sh #On nodes NODEc
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster node

The Cluster Node Addition of /apps/product/11gr2/grid was successful.
Please check '/tmp/silentInstall.log' for more details.

Next the RDBMS home needs to be migrated over.

oracle@NODEa:/apps/oracle/product/11gr2/db/oui/bin ./addNode.sh "CLUSTER_NEW_NODES={NODEc}"

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "NODEa"


Checking user equivalence...
User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "bge0"
Node connectivity passed for interface "bge0"

Node connectivity check passed


Checking CRS integrity...

CRS integrity check passed

Checking shared resources...

Checking CRS home location...
The location "/apps/product/11gr2/grid" is not shared but is present/creatable on all nodes
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "bge0"
Node connectivity passed for interface "bge0"

Check: Node connectivity for interface "bge1"
Node connectivity passed for interface "bge1"

Check: Node connectivity for interface "ce1"
Node connectivity passed for interface "ce1"

Node connectivity check passed

Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "NODEc:/var/tmp/"
Free disk space check passed for "NODEa:/var/tmp/"
Check for multiple users with UID value 2001 passed
User existence check passed for "oracle"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "project.max-sem-ids"
Kernel parameter check passed for "process.max-sem-nsems"
Kernel parameter check passed for "project.max-shm-memory"
Kernel parameter check passed for "project.max-shm-ids"
Kernel parameter check passed for "tcp_smallest_anon_port"
Kernel parameter check passed for "tcp_largest_anon_port"
Kernel parameter check passed for "udp_smallest_anon_port"
Kernel parameter check passed for "udp_largest_anon_port"
Package existence check passed for "SUNWarc-...( sparc)"
Package existence check passed for "SUNWbtool-...( sparc)"
Package existence check passed for "SUNWhea-...( sparc)"
Package existence check passed for "SUNWlibm-...( sparc)"
Package existence check passed for "SUNWlibms-...( sparc)"
Package existence check passed for "SUNWsprot-...( sparc)"
Package existence check passed for "SUNWtoo-...( sparc)"
Package existence check passed for "SUNWi1of-...( sparc)"
Package existence check passed for "SUNWi1cs-...( sparc)"
Package existence check passed for "SUNWi15cs-...( sparc)"
Package existence check passed for "SUNWxwfnt-...( sparc)"
Package existence check passed for "SUNWlibC-...( sparc)"
Package existence check passed for "SUNWcsl-...( sparc)"
Operating system patch check passed for "Patch 119963-14"
Operating system patch check passed for "Patch 120753-06"
Operating system patch check passed for "Patch 139574-03"
Operating system patch check passed for "Patch 141414-02"
Check for multiple users with UID value 0 passed
Current group ID check passed

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "xntpd"
Check for NTP daemon or service alive passed on all nodes

NTP daemon slewing option check passed

NTP daemon's boot time configuration check for slewing option passed

NTP common Time Server Check started...
Check of common NTP Time Server passed

Clock time offset check from NTP Time Server started...
Clock time offset check passed

Clock synchronization check using Network Time Protocol(NTP) passed


User "oracle" is not part of "root" group. Check passed
Checking consistency of file "/etc/resolv.conf" across nodes

File "/etc/resolv.conf" does not have both domain and search entries defined
domain entry in file "/etc/resolv.conf" is consistent across nodes
search entry in file "/etc/resolv.conf" is consistent across nodes
All nodes have one search entry defined in file "/etc/resolv.conf"
The DNS response time for an unreachable node is within acceptable limit on all nodes

File "/etc/resolv.conf" is consistent across nodes


Pre-check for node addition was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 13192 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Oracle Universal Installer, Version 11.2.0.2.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.


Performing tests to see whether nodes NODEb,NODEc are available
............................................................... 100% Done.

....
-----------------------------------------------------------------------------
Cluster Node Addition Summary
Global Settings
Source: /apps/oracle/product/11gr2/db
New Nodes
Space Requirements
New Nodes
NODEc
/: Required 6.10GB : Available 24.03GB
Installed Products
Product Names
Oracle Database 11g 11.2.0.2.0
Sun JDK 1.5.0.24.08
Installer SDK Component 11.2.0.2.0
Oracle One-Off Patch Installer 11.2.0.0.2
Oracle Universal Installer 11.2.0.2.0
Oracle USM Deconfiguration 11.2.0.2.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle DBCA Deconfiguration 11.2.0.2.0
Oracle RAC Deconfiguration 11.2.0.2.0
Oracle Database Deconfiguration 11.2.0.2.0
Oracle Configuration Manager Client 10.3.2.1.0
Oracle Configuration Manager 10.3.3.1.1
Oracle ODBC Driverfor Instant Client 11.2.0.2.0
LDAP Required Support Files 11.2.0.2.0
SSL Required Support Files for InstantClient 11.2.0.2.0
Bali Share 1.1.18.0.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle Real Application Testing 11.2.0.2.0
Oracle Database Vault J2EE Application 11.2.0.2.0
Oracle Label Security 11.2.0.2.0
Oracle Data Mining RDBMS Files 11.2.0.2.0
Oracle OLAP RDBMS Files 11.2.0.2.0
Oracle OLAP API 11.2.0.2.0
Platform Required Support Files 11.2.0.2.0
Oracle Database Vault option 11.2.0.2.0
Oracle RAC Required Support Files-HAS 11.2.0.2.0
SQL*Plus Required Support Files 11.2.0.2.0
Oracle Display Fonts 9.0.2.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle JDBC Server Support Package 11.2.0.2.0
Oracle SQL Developer 11.2.0.2.0
Oracle Application Express 11.2.0.2.0
XDK Required Support Files 11.2.0.2.0
RDBMS Required Support Files for Instant Client 11.2.0.2.0
SQLJ Runtime 11.2.0.2.0
Database Workspace Manager 11.2.0.2.0
RDBMS Required Support Files Runtime 11.2.0.2.0
Oracle Globalization Support 11.2.0.2.0
Exadata Storage Server 11.2.0.1.0
Provisioning Advisor Framework 10.2.0.4.3
Enterprise Manager Database Plugin -- Repository Support 11.2.0.2.0
Enterprise Manager Repository Core Files 10.2.0.4.3
Enterprise Manager Database Plugin -- Agent Support 11.2.0.2.0
Enterprise Manager Grid Control Core Files 10.2.0.4.3
Enterprise Manager Common Core Files 10.2.0.4.3
Enterprise Manager Agent Core Files 10.2.0.4.3
RDBMS Required Support Files 11.2.0.2.0
regexp 2.1.9.0.0
Agent Required Support Files 10.2.0.4.3
Oracle 11g Warehouse Builder Required Files 11.2.0.2.0
Oracle Notification Service (eONS) 11.2.0.2.0
Oracle Text Required Support Files 11.2.0.2.0
Parser Generator Required Support Files 11.2.0.2.0
Oracle Database 11g Multimedia Files 11.2.0.2.0
Oracle Multimedia Java Advanced Imaging 11.2.0.2.0
Oracle Multimedia Annotator 11.2.0.2.0
Oracle JDBC/OCI Instant Client 11.2.0.2.0
Oracle Multimedia Locator RDBMS Files 11.2.0.2.0
Precompiler Required Support Files 11.2.0.2.0
Oracle Core Required Support Files 11.2.0.2.0
Sample Schema Data 11.2.0.2.0
Oracle Starter Database 11.2.0.2.0
Oracle Message Gateway Common Files 11.2.0.2.0
Oracle XML Query 11.2.0.2.0
XML Parser for Oracle JVM 11.2.0.2.0
Oracle Help For Java 4.2.9.0.0
Installation Plugin Files 11.2.0.2.0
Enterprise Manager Common Files 10.2.0.4.3
Expat libraries 2.0.1.0.1
Deinstallation Tool 11.2.0.2.0
Oracle Quality of Service Management (Client) 11.2.0.2.0
Perl Modules 5.10.0.0.1
JAccelerator (COMPANION) 11.2.0.2.0
Oracle Containers for Java 11.2.0.2.0
Perl Interpreter 5.10.0.0.1
Oracle Net Required Support Files 11.2.0.2.0
Secure Socket Layer 11.2.0.2.0
Oracle Universal Connection Pool 11.2.0.2.0
Oracle JDBC/THIN Interfaces 11.2.0.2.0
Oracle Multimedia Client Option 11.2.0.2.0
Oracle Java Client 11.2.0.2.0
Character Set Migration Utility 11.2.0.2.0
Oracle Code Editor 1.2.1.0.0I
PL/SQL Embedded Gateway 11.2.0.2.0
OLAP SQL Scripts 11.2.0.2.0
Database SQL Scripts 11.2.0.2.0
Oracle Locale Builder 11.2.0.2.0
Oracle Globalization Support 11.2.0.2.0
SQL*Plus Files for Instant Client 11.2.0.2.0
Required Support Files 11.2.0.2.0
Oracle Database User Interface 2.2.13.0.0
Oracle ODBC Driver 11.2.0.2.0
Oracle Notification Service 11.2.0.2.0
XML Parser for Java 11.2.0.2.0
Oracle Security Developer Tools 11.2.0.2.0
Oracle Wallet Manager 11.2.0.2.0
Cluster Verification Utility Common Files 11.2.0.2.0
Oracle Clusterware RDBMS Files 11.2.0.2.0
Oracle UIX 2.2.24.6.0
Enterprise Manager plugin Common Files 11.2.0.2.0
HAS Common Files 11.2.0.2.0
Precompiler Common Files 11.2.0.2.0
Installation Common Files 11.2.0.2.0
Oracle Help for the Web 2.0.14.0.0
Oracle LDAP administration 11.2.0.2.0
Buildtools Common Files 11.2.0.2.0
Assistant Common Files 11.2.0.2.0
Oracle Recovery Manager 11.2.0.2.0
PL/SQL 11.2.0.2.0
Generic Connectivity Common Files 11.2.0.2.0
Oracle Database Gateway for ODBC 11.2.0.2.0
Oracle Programmer 11.2.0.2.0
Oracle Database Utilities 11.2.0.2.0
Enterprise Manager Agent 10.2.0.4.3
SQL*Plus 11.2.0.2.0
Oracle Netca Client 11.2.0.2.0
Oracle Multimedia Locator 11.2.0.2.0
Oracle Call Interface (OCI) 11.2.0.2.0
Oracle Multimedia 11.2.0.2.0
Oracle Net 11.2.0.2.0
Oracle XML Development Kit 11.2.0.2.0
Database Configuration and Upgrade Assistants 11.2.0.2.0
Oracle JVM 11.2.0.2.0
Oracle Advanced Security 11.2.0.2.0
Oracle Internet Directory Client 11.2.0.2.0
Oracle Enterprise Manager Console DB 11.2.0.2.0
HAS Files for DB 11.2.0.2.0
Oracle Net Listener 11.2.0.2.0
Oracle Text 11.2.0.2.0
Oracle Net Services 11.2.0.2.0
Oracle Database 11g 11.2.0.2.0
Oracle OLAP 11.2.0.2.0
Oracle Spatial 11.2.0.2.0
Oracle Partitioning 11.2.0.2.0
Enterprise Edition Options 11.2.0.2.0
-----------------------------------------------------------------------------


Instantiating scripts for add node (Monday, 5 September 2011 12:09:04 o'clock BST)
. 1% Done.
Instantiation of add node scripts complete

Copying to remote nodes (Monday, 5 September 2011 12:09:19 o'clock BST)
............................................................................................... 96% Done.
Home copied to new nodes

Saving inventory on nodes (Monday, 5 September 2011 12:20:17 o'clock BST)
. 100% Done.
Save inventory complete
WARNING:A new inventory has been created on one or more nodes in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script at '/apps/oracle/home/oraInventory/orainstRoot.sh' with root privileges on nodes 'NODEc'.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user in each cluster node.
/apps/oracle/home/oraInventory/orainstRoot.sh #On nodes NODEc
/apps/oracle/product/11gr2/db/root.sh #On nodes NODEc
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts in each cluster node

The Cluster Node Addition of /apps/oracle/product/11gr2/db was successful.
Please check '/tmp/silentInstall.log' for more details.
you have mail in /var/mail//oracle

Go to the DB HOME first and run root.sh and root user

root@NODEc # ./root.sh
Running Oracle 11g root script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /apps/oracle/product/11gr2/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Creating /usr/local/bin directory...
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Then run the root.sh under the GRID HOME as root user. This actually starts up ASM and the clusterware and does the node addition(takes a little bit longer)

Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /apps/product/11gr2/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
OLR initialization - successful
Adding daemon to inittab
ACFS-9200: Supported
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node NODEc, number 3, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Ensure ASM is running and all the diskgroup and disks are visible and mounted.

Finally add the instance to the cluster.

oracle@NODEc:/apps/oracle/product/11gr2/db/dbs srvctl add instance -d dbtest -i dbtest2 -n NODEc
oracle@NODEc:/apps/oracle/product/11gr2/db/dbs srvctl status database -d dbtest
Instance dbtest3 is running on node NODEa
Instance dbtest4 is running on node NODEb
Instance dbtest2 is not running on node NODEc
oracle@NODEc:/apps/oracle/product/11gr2/db/dbs srvctl start instance -d dbtest -i dbtest2

Oracle Clusterware restore

ASM and Clusterware are interleaved. ASM cannot startup without the clusterware running first and the clusterware can store all its voting disk and OCR in ASM
This is a bit of a complex intergration. I was in a scenario where I lost all my disks in ASM. When I say lost, we were trying a proof of concept. :)

When I tried to start ASM as a standalone with a pfile it complained that it couldnt communicate with the CSS
And I couldnt start the CRS without the voting disk and OCR, which I had now lost.
I was able to see all the LUNs on the server under /dev/rdsk

Below is how to recover.


Start CRS in exclusive NOCRS mode. This should be run only on one node of the cluster.

oracle@NODEA:/var/opt/oracle sudo crsctl start crs -excl -nocrs
Password:
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'NODEA'
CRS-2676: Start of 'ora.mdnsd' on 'NODEA' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'NODEA'
CRS-2676: Start of 'ora.gpnpd' on 'NODEA' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'NODEA'
CRS-2672: Attempting to start 'ora.gipcd' on 'NODEA'
CRS-2676: Start of 'ora.cssdmonitor' on 'NODEA' succeeded
CRS-2676: Start of 'ora.gipcd' on 'NODEA' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'NODEA'
CRS-2672: Attempting to start 'ora.diskmon' on 'NODEA'
CRS-2676: Start of 'ora.diskmon' on 'NODEA' succeeded
CRS-2676: Start of 'ora.cssd' on 'NODEA' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'NODEA'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'NODEA'
CRS-2672: Attempting to start 'ora.ctssd' on 'NODEA'
CRS-2676: Start of 'ora.ctssd' on 'NODEA' succeeded
CRS-2676: Start of 'ora.drivers.acfs' on 'NODEA' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'NODEA' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'NODEA'
CRS-2676: Start of 'ora.asm' on 'NODEA' succeeded

Re-create all the diskgroups.

SQL> CREATE DISKGROUP ORCLCLU HIGH REDUNDANCY
2 FAILGROUP fg1 DISK '/dev/rdsk/c6t001738000CE8002Ad0s0'
3 FAILGROUP fg2 DISK '/dev/rdsk/c6t001738000CE8002Bd0s0'
4 FAILGROUP fg3 DISK '/dev/rdsk/c6t001738000CE8002Cd0s0'
5 FAILGROUP fg4 DISK '/dev/rdsk/c6t001738000CE8002Dd0s0'
6 QUORUM FAILGROUP fg5 DISK '/dev/rdsk/c6t001738000CE8002Ed0s0'
7 ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';

Diskgroup created.

create diskgroup DATA_FAST external redundancy disk '/dev/rdsk/c6t001738000CE80026d0s0' attribute 'COMPATIBLE.ASM' = '11.2';

create diskgroup ARCHFLASH external redundancy disk '/dev/rdsk/c6t001738000CE80029d0s0' attribute 'COMPATIBLE.ASM' = '11.2';

create diskgroup DATA_RW external redundancy disk '/dev/rdsk/c6t001738000CE80028d0s0' attribute 'COMPATIBLE.ASM' = '11.2';
............

Run queries on V$asm_disk and V$asm_diskgroup to check all disks and diskgroups are available.

Time to restore OCR and voting disks.
OCR is first.
Find out where the OCR backups are located.

oracle@NODEA:/var/opt/oracle sudo ocrconfig -showbackup
Password:
PROT-26: Oracle Cluster Registry backup locations were retrieved from a local copy

NODEA 2011/09/05 17:36:47 /apps/product/11gr2/grid/cdata/test-clu/backup00.ocr

NODEA 2011/09/05 13:36:46 /apps/product/11gr2/grid/cdata/test-clu/backup01.ocr

NODEA 2011/09/05 09:36:44 /apps/product/11gr2/grid/cdata/test-clu/backup02.ocr

NODEA 2011/09/04 01:36:33 /apps/product/11gr2/grid/cdata/test-clu/day.ocr

NODEA 2011/08/23 01:08:30 /apps/product/11gr2/grid/cdata/test-clu/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available

oracle@NODEA:/var/opt/oracle sudo ocrconfig -restore /apps/product/11gr2/grid/cdata/test-clu/backup00.ocr
oracle@NODEA:/var/opt/oracle

Now the voting disk. In 11g Voting disks are autobacked up with the OCR.

oracle@NODEA:/var/opt/oracle sudo crsctl replace votedisk +ORCLCLU
Successful addition of voting disk 142da6336d704f7fbf0fbebd482506d2.
Successful addition of voting disk 2477e787c9dd4f72bfa50bc3e88fc8d1.
Successful addition of voting disk 72c3a35d987a4f2ebf2e27c97ee69946.
Successful addition of voting disk 6c3a9fd3b0b84f06bfed66c9b9604f1b.
Successful addition of voting disk 56db0703046b4f22bf614ec7ab1bd716.
Successfully replaced voting disk group with +ORCLCLU.
CRS-4266: Voting file(s) successfully replaced

Create a SPFILE for ASM from its local PFILE. Place it on the shared storage.

sqlplus / as sysasm

create spfile='+DATA_RW' from pfile;

Clusterware restore done. Time to stop the exclusive running clusterware and make it work for all nodes

Stop the clusterware. -f forces the stop

oracle@NODEA:/apps/product/11gr2/grid/dbs sudo crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'NODEA'
CRS-2673: Attempting to stop 'ora.ctssd' on 'NODEA'
CRS-2673: Attempting to stop 'ora.asm' on 'NODEA'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'NODEA'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'NODEA'
CRS-2677: Stop of 'ora.asm' on 'NODEA' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'NODEA'
CRS-2677: Stop of 'ora.drivers.acfs' on 'NODEA' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'NODEA' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'NODEA' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'NODEA' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'NODEA'
CRS-2677: Stop of 'ora.cssd' on 'NODEA' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'NODEA'
CRS-2677: Stop of 'ora.gipcd' on 'NODEA' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'NODEA'
CRS-2677: Stop of 'ora.gpnpd' on 'NODEA' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'NODEA' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the clusterware.

oracle@NODEA:/apps/product/11gr2/grid/dbs sudo crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

finally check the status of the cluster on all nodes.

oracle@NODEB:/apps/product/11gr2/grid/log/NODEB/evmd sudo crsctl check cluster -all
Password:
**************************************************************
NODEA:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
NODEB:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

My first try in restarting clusterware did not help as the server had some hanging cluster threads. To clear them, I had to restart the server and let the init scripts start clusterware cleanly.

Use crsctl stat res -t to check the status of all cluster resources.