DBA survival BLOG

DBA stuff and Oracle Data Guard

About Ludovico

Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

2015 in numbers…

Posted on January 31, 2016 by Ludovico

I am spending good times since I have moved in Switzerland 3 years ago. 2015 has been as good as 2014. Now that January ends, I am officially late in publishing this information, but it’s MY blog, so who cares? 😛

A few numbers:

25 blog posts (+1)
~52000 page views (+40%), ~43000 visits (+48%)
Speaker at 2 major conferences (#C15LV, #UKOUG_TECH15)
Speaker at 2 Trivadis internal conferences
Speaker at 1 local user group event
Delegate of EOUC at DOAG 2015
A total of 14 public speeches (same as 2014)
I’ve been elected RAC SIG Vice President
2 RAC Attack workshops organized
1 roundtable as organizer, 1 panel
2 T-shirt designed as gifts for 2 RAC Attack workshops
2 articles published
Launched the RAC SIG website and the new session agenda
Countless new friends and/or contacts

I hope that 2016 will be as good as 2015

Configuring the MySQL Database Plug-In for Oracle Enterprise Manager 12c

Posted on January 31, 2016 by Ludovico

I have blogged in the past about MySQL Enterprise Monitor 3.0 and I was quite happy at the very beginning, but after a while I have to admit that I was missing many of the Oracle Enterprise Manager 12c features.

In particular, MEM 3.0 does not have a usable database. In MEM all the tables are crypted and it is not possible to list, for example, all the targets monitored, nor it is possible via API or REST web services because MEM 3.0 lacks these features.

What makes EM12c a GREAT product comparing to MEM, are many features like blackouts, a usable command line interface (emcli), integrated reporting, scheduler, automatic groups… the list would be just huge.

Luckily, Oracle has officially released a MySQL plugin for EM12c, provided that the EM is at least in version 12.1.0.4.

So I’ve upgraded (a while ago) my customer’s EM12c to 12.1.0.5. and decided to try the plugin.

The first step is to download the last version of plugin for MySQL.

I can verify that you have the last version by going to

Setup -> Extensibility -> Self-Update -> Plugins:

The agent has been downloaded, but in order to make it available on the targets, I first need to deploy it on the management servers (2 OMSes in my case):

Check the plugin name and version:

Verify the prerequisites check (here I have one column per OMS):
2015-08-19 13_55_26-Deploy Plug-ins on Management Servers

Specify the credentials for the Management Repository:
Execute the deploy:

If everything went OK, I’m able to check the status of the deployment:

Now I can see that the plugin is correctly deployed on the OMSes, I can do the same for the agents:

I must select one by one the agents that run on the hosts where I have MySQL running. I may select all agents as well, but it’s better to be neat…

again, there are prerequisite checks and confirmations:

The plugin deployment went well:

Now I can run the target discovery on the agent:

But the discovery does not find my MySQL targets. What went wrong?

Each agent has a default list of “discovery modules” used for the discovery, but by default the MySQL one is not enabled after I install the plugin:

so it is necessary to activate it and deactivate the discovery modules I do not need:

Tada! at the next discovery, I have my target available:

The target name is automatically set to hostname:mysqlport:

as all discovered targets, I need to promote it to have it available for monitoring with EM12c:

The target is available, now I can use most of the EM12c features to monitor my MySQL environment.

HTH

—

Ludovico

Recording of “Rapid Home Provisioning” webinar for the RAC SIG

Posted on January 14, 2016 by Ludovico

Yesterday I have presented the Oracle Rapid Home Provisioning technology for the RAC SIG, you can find the recording on YouTube:

Cheers

—

Ludo

Rapid Home Provisioning

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Rapid Home Provisioning, it will be the first time that I present this session in public.

I usually like to give the link to the material to my audience, so here we go:

Slides:

Demo:

Enjoy
—
Ludovico

Oracle Active Data Guard and Global Data Services in Action!

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Global Data Services, it will be the first time that I present this session.

I usually like to give the link to the material to my audience, so here we go:

Credits

I have to give special credits to my colleague Robert Bialek. I’ve got a late confirmation for this session and my slide deck was not ready at all, so I have used a big part of his original work. Most of the content included in the slides has been created by Robert, not me. (Thank you for your help! :-))

Slides

Demo recording

Demo script

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF
echo "next: GSM config"
read -p ""

gsm
echo "#### GSM CONFIGURATION ####"
echo "GDS COMMAND:
config"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config
exit
EOF
echo "next: ADD GDSPOOL"
read -p ""


echo "#### ADD GDSPOOL ####"
echo "GDS COMMAND:
add gdspool -gdspool sales"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add gdspool -gdspool sales
exit
EOF
echo "next: ADD BROKERCONFIG"
read -p ""


echo "#### ADD BROKERCONFIG ####"
echo "GDS COMMAND:
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany
exit
EOF
echo "next: config databases"
read -p ""


echo "#### CONFIG DATABASES ####"
echo "GDS COMMAND:
config database"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config database
exit
EOF
echo "next: modify databases"
read -p ""

echo "#### MODIFY DATABASES ####"
echo "GDS COMMAND: 
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
config database
exit
EOF
echo "next: add service read/write"
read -p ""


echo "#### ADD SERVICE R/W ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services
exit
EOF
echo "next: ADD SERVICE R/O"
read -p ""

echo "#### ADD SERVICE R/O ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
exit
EOF
echo "next: stop apply ch1 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-off';
EOF
echo "next: gds services"
read -p ""


gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: stop apply ch2 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-off';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: start apply ch1  and ch2"
read -p ""

db
echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-on';
EOF
echo "sleeping 5"
sleep 5
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-on';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"
read -p ""


db
echo "#### VALIDATE DATABASE OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
validate database oltp_ch1;
EOF
echo "next: switchover"
read -p ""
echo "#### SWITCHOVER TO OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_ch1;
EOF
echo "next: gds services"
read -p ""

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "next: GSM config"

read -p ""

gsm

echo "#### GSM CONFIGURATION ####"

echo "GDS COMMAND:

config"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config

exit

EOF

echo "next: ADD GDSPOOL"

read -p ""

echo "#### ADD GDSPOOL ####"

echo "GDS COMMAND:

add gdspool -gdspool sales"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add gdspool -gdspool sales

exit

EOF

echo "next: ADD BROKERCONFIG"

read -p ""

echo "#### ADD BROKERCONFIG ####"

echo "GDS COMMAND:

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany

exit

EOF

echo "next: config databases"

read -p ""

echo "#### CONFIG DATABASES ####"

echo "GDS COMMAND:

config database"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config database

exit

EOF

echo "next: modify databases"

read -p ""

echo "#### MODIFY DATABASES ####"

echo "GDS COMMAND:

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

gdsctl <<EOF

connect gsm_admin/password1@gsm1

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

config database

exit

EOF

echo "next: add service read/write"

read -p ""

echo "#### ADD SERVICE R/W ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services

exit

EOF

echo "next: ADD SERVICE R/O"

read -p ""

echo "#### ADD SERVICE R/O ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_ro

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

start service -service gsales_ro

services

exit

EOF

echo "next: stop apply ch1 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: stop apply ch2 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: start apply ch1 and ch2"

read -p ""

echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-on';

EOF

echo "sleeping 5"

sleep 5

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-on';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"

read -p ""

echo "#### VALIDATE DATABASE OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

validate database oltp_ch1;

EOF

echo "next: switchover"

read -p ""

echo "#### SWITCHOVER TO OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_ch1;

EOF

echo "next: gds services"

read -p ""

And the script to revert the demo:

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_de;
EOF

gsm
echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"
 
gdsctl <<EOF
connect gsm_admin/password1@gsm1
stop service -service gsales_ro
stop service -service gsales_rw
remove service -service gsales_ro
remove service -service gsales_rw
remove brokerconfig
remove gdspool -gdspool sales
config
exit
EOF

db
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF

echo "DEMO reverted."
read -p ""

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_de;

EOF

gsm

echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

stop service -service gsales_ro

stop service -service gsales_rw

remove service -service gsales_ro

remove service -service gsales_rw

remove brokerconfig

remove gdspool -gdspool sales

config

exit

EOF

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "DEMO reverted."

read -p ""

Cheers

—

Ludovico

Oracle Database on ACFS: a perfect marriage?

Posted on November 26, 2015 by Ludovico

Update: I will give this presentation at UKOUG Tech15, Wed 9 December at 14:30.

This presentation has had a very poor score in selections for conferences (no OOW, no DOAG) but people liked it very much at Paris Oracle Meetup. The Database on ACFS is mainstream now, thanks to the new ODA releases. Having some knowledge about why and how you should run (not) Databases on ACFS is definitely worth a read.

Slides

Demo 1 recording

Demo 2 recording

Demo script (DB ACFS clone from Standby Database)

#!/bin/bash
####################
# create the clone #
####################

set -x

NUM=`echo $$ | cut -c 1-4`
export NEWNAME=${1:-SN$NUM}

cat <<EOF 
################################################
################################################
##
## CLONING DATABASE USING NEW SID: $NEWNAME
##
################################################
################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-OFF";
exit
EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-ON";
exit
EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF
alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;
create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;
exit
EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*
sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora


#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat  >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF
*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'
*.db_name='$NEWNAME'
*.db_unique_name='$NEWNAME'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.sga_target=1900M
$NEWNAME.instance_number=1
$NEWNAME.undo_tablespace=UNDOTBS1
*.service_names='$NEWNAME'
*.cluster_database=false
EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

 
sqlplus / as sysdba <<EOF
create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';
@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc
--recover automatic database using backup controlfile until cancel;
--CANCEL
alter database open resetlogs;
alter tablespace temp add tempfile size 50M ;
EOF

#!/bin/bash

####################

# create the clone #

####################

set -x

NUM=`echo $$ | cut -c 1-4`

export NEWNAME=${1:-SN$NUM}

cat <<EOF

################################################

## CLONING DATABASE USING NEW SID: $NEWNAME

################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-OFF";

exit

EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-ON";

exit

EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF

alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;

create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;

exit

EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*

sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF

*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'

*.db_name='$NEWNAME'

*.db_unique_name='$NEWNAME'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

*.sga_target=1900M

$NEWNAME.instance_number=1

$NEWNAME.undo_tablespace=UNDOTBS1

*.service_names='$NEWNAME'

*.cluster_database=false

EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

sqlplus / as sysdba <<EOF

create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';

@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

--recover automatic database using backup controlfile until cancel;

--CANCEL

alter database open resetlogs;

alter tablespace temp add tempfile size 50M ;

EOF

Comments are, as always, very appreciated 🙂

—

Ludo

Migrating Oracle RAC from SuSE to OEL (or RHEL) live

Posted on November 10, 2015 by Ludovico

I have a customer that needs to migrate its Oracle RAC cluster from SuSE to OEL.

I know, I know, there is a paper from Dell and Oracle named:

How Dell Migrated from SUSE Linux to Oracle Linux

That explains how Dell migrated its many RAC clusters from SuSE to OEL. The problem is that they used a different strategy:

– backup the configuration of the nodes
– then for each node, one at time
– stop the node
– reinstall the OS
– restore the configuration and the Oracle binaries
– relink
– restart

What I want to achieve instead is:
– add one OEL node to the SuSE cluster as new node
– remove one SuSE node from the now-mixed cluster
– install/restore/relink the RDBMS software (RAC) on the new node
– move the RAC instances to the new node (taking care to NOT run more than the number of licensed nodes/CPUs at any time)
– repeat (for the remaining nodes)

because the customer will also migrate to new hardware.

In order to test this migration path, I’ve set up a SINGLE NODE cluster (if it works for one node, it will for two or more).

oracle@sles01:~> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

oracle@sles01:~> crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE sles01 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

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

oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

I have to setup the new node addition carefully, mainly as I would do with a traditional node addition:

Add new ip addresses (public, private, vip) to the DNS/hosts
Install the new OEL server
Keep the same user and groups (uid, gid, etc)
Verify the network connectivity and setup SSH equivalence
Check that the multicast connection is ok
Add the storage, configure persistent naming (udev) and verify that the disks (major, minor, names) are the very same
The network cards also must be the very same

Once the new host ready, the cluvfy stage -pre nodeadd will likely fail due to

Kernel release mismatch
Package mismatch

Here’s an example of output:

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

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


Checking user equivalence...
User equivalence check passed for user "oracle"
Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...
Location check passed for: "/u01/app/12.1.0/grid"
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"
Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01
TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity using interfaces on subnet "172.16.100.0"
Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01
TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.56.0".
Subnet mask consistency check passed for subnet "172.16.100.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.
Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"
Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"
Check for multiple users with UID value 1101 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

WARNING:
PRVF-7524 : Kernel version is not consistent across all the nodes.
Kernel version = "3.0.101-63-default" found on nodes: sles01.
Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "make"
Package existence check passed for "libaio"
Package existence check passed for "binutils"
Package existence check passed for "gcc(x86_64)"
Package existence check passed for "gcc-c++(x86_64)"
Package existence check passed for "glibc"
Package existence check passed for "glibc-devel"
Package existence check passed for "ksh"
Package existence check passed for "libaio-devel"
Package existence check failed for "libstdc++33"
Check failed on nodes:
        rhel01
Package existence check failed for "libstdc++43-devel"
Check failed on nodes:
        rhel01
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check failed for "libstdc++46"
Check failed on nodes:
        rhel01
Package existence check failed for "libgcc46"
Check failed on nodes:
        rhel01
Package existence check passed for "sysstat"
Package existence check failed for "libcap1"
Check failed on nodes:
        rhel01
Package existence check failed for "nfs-kernel-server"
Check failed on nodes:
        rhel01
Check for multiple users with UID value 0 passed
Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed
Group existence check passed for "asmadmin"
Group existence check passed for "asmoper"
Group existence check passed for "asmdba"

Checking ASMLib configuration.
Check for ASMLib configuration 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...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed


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

"domain" and "search" entries do not coexist in any  "/etc/resolv.conf" file
All nodes have same "search" order defined in file "/etc/resolv.conf"
PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed


Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Pre-check for node addition was unsuccessful on all the nodes.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

Checking node reachability...

Node reachability check passed from node "sles01"

Checking user equivalence...

User equivalence check passed for user "oracle"

Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...

Location check passed for: "/u01/app/12.1.0/grid"

Shared resources check for node addition passed

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"

Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01

TCP connectivity check passed for subnet "192.168.56.0"

Check: Node connectivity using interfaces on subnet "172.16.100.0"

Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01

TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...

Subnet mask consistency check passed for subnet "192.168.56.0".

Subnet mask consistency check passed for subnet "172.16.100.0".

Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...

Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Total memory check passed

Available memory check passed

Swap space check passed

Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"

Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"

Check for multiple users with UID value 1101 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

WARNING:

PRVF-7524 : Kernel version is not consistent across all the nodes.

Kernel version = "3.0.101-63-default" found on nodes: sles01.

Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.

Kernel version check passed

Kernel parameter check passed for "semmsl"

Kernel parameter check passed for "semmns"

Kernel parameter check passed for "semopm"

Kernel parameter check passed for "semmni"

Kernel parameter check passed for "shmmax"

Kernel parameter check passed for "shmmni"

Kernel parameter check passed for "shmall"

Kernel parameter check passed for "file-max"

Kernel parameter check passed for "ip_local_port_range"

Kernel parameter check passed for "rmem_default"

Kernel parameter check passed for "rmem_max"

Kernel parameter check passed for "wmem_default"

Kernel parameter check passed for "wmem_max"

Kernel parameter check passed for "aio-max-nr"

Package existence check passed for "make"

Package existence check passed for "libaio"

Package existence check passed for "binutils"

Package existence check passed for "gcc(x86_64)"

Package existence check passed for "gcc-c++(x86_64)"

Package existence check passed for "glibc"

Package existence check passed for "glibc-devel"

Package existence check passed for "ksh"

Package existence check passed for "libaio-devel"

Package existence check failed for "libstdc++33"

Check failed on nodes:

rhel01

Package existence check failed for "libstdc++43-devel"

Check failed on nodes:

rhel01

Package existence check passed for "libstdc++-devel(x86_64)"

Package existence check failed for "libstdc++46"

Check failed on nodes:

rhel01

Package existence check failed for "libgcc46"

Check failed on nodes:

rhel01

Package existence check passed for "sysstat"

Package existence check failed for "libcap1"

Check failed on nodes:

rhel01

Package existence check failed for "nfs-kernel-server"

Check failed on nodes:

rhel01

Check for multiple users with UID value 0 passed

Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed

Group existence check passed for "asmadmin"

Group existence check passed for "asmoper"

Group existence check passed for "asmdba"

Checking ASMLib configuration.

Check for ASMLib configuration 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...

No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed

User "oracle" is not part of "root" group. Check passed

Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any "/etc/resolv.conf" file

All nodes have same "search" order defined in file "/etc/resolv.conf"

PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...

Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed

Pre-check for node addition was unsuccessful on all the nodes.

So the problem is not if the check succeed or not (it will not), but what fails.

Solving all the problems not related to the difference SuSE-OEL is crucial, because the addNode.sh will fail with the same errors. I need to run it using -ignorePrereqs and -ignoreSysPrereqs switches. Let’s see how it works:

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 27479 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2032 MB    Passed

Prepare Configuration in progress.

Prepare Configuration successful.
..................................................   9% Done.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.
..................................................   15% Done.

Copying files to node in progress.

Copying files to node successful.
..................................................   79% Done.

Saving cluster inventory in progress.
..................................................   87% Done.

Saving cluster inventory successful.
The Cluster Node Addition of /u01/app/12.1.0/grid was successful.
Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:
[rhel01]
Execute /u01/app/12.1.0/grid/root.sh on the following nodes:
[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.
..........
Update Inventory in progress.
..................................................   100% Done.

Update Inventory successful.
Successfully Setup Software.

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 27479 MB Passed

Checking swap space: must be greater than 150 MB. Actual 2032 MB Passed

Prepare Configuration in progress.

Prepare Configuration successful.

.................................................. 9% Done.

You can find the log of this install session at:

/u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.

.................................................. 15% Done.

Copying files to node in progress.

Copying files to node successful.

.................................................. 79% Done.

Saving cluster inventory in progress.

.................................................. 87% Done.

Saving cluster inventory successful.

The Cluster Node Addition of /u01/app/12.1.0/grid was successful.

Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):

1. /u01/app/oraInventory/orainstRoot.sh

2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:

[rhel01]

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:

[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.

..........

Update Inventory in progress.

.................................................. 100% Done.

Update Inventory successful.

Successfully Setup Software.

Then, as stated by the addNode.sh, I run the root.sh and I expect it to work:

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/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.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'
CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'
CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'
CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'
CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'
CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'
CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'
CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded
CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'
CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'
CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'
CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rhel01'
CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rhel01'
CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'
CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded
CRS-6017: Processing resource auto-start for servers: rhel01
CRS-2672: Attempting to start 'ora.ons' on 'rhel01'
CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded
CRS-6016: Resource auto-start has completed for server rhel01
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Preparing packages for installation...
cvuqdisk-1.0.9-1
2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh

Performing root user operation for Oracle 12c

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/12.1.0/grid

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/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.

Relinking oracle with rac_on option

Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params

2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful

2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Starting Oracle High Availability Services-managed resources

CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'

CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'

CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'

CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'

CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'

CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'

CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'

CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded

CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'

CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'

CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'

CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rhel01'

CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rhel01'

CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'

CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded

CRS-6017: Processing resource auto-start for servers: rhel01

CRS-2672: Attempting to start 'ora.ons' on 'rhel01'

CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded

CRS-6016: Resource auto-start has completed for server rhel01

CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources

CRS-4123: Oracle High Availability Services has been started.

2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.

clscfg: version 5 is 12c Release 1.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

Preparing packages for installation...

cvuqdisk-1.0.9-1

2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Bingo! Let’s check if everything is up and running:

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       rhel01                   Started,STABLE
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.rhel01.vip
      1        ONLINE  ONLINE       rhel01                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE rhel01 Started,STABLE

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.rhel01.vip

1 ONLINE ONLINE rhel01 STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

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

[oracle@rhel01 ~]$ olsnodes -s
sles01  Active
rhel01  Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r
3.8.13-16.2.1.el6uek.x86_64
[oracle@rhel01 ~]$ ssh sles01 uname -r
3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue
Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

[oracle@rhel01 ~]$ olsnodes -s

sles01 Active

rhel01 Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r

3.8.13-16.2.1.el6uek.x86_64

[oracle@rhel01 ~]$ ssh sles01 uname -r

3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

So yes, it works, but remember that it’s not a supported long-term configuration.

In my case I expect to migrate the whole cluster from SLES to OEL in one day.

NOTE: using OEL6 as new target is easy because the interface names do not change. The new OEL7 interface naming changes, if you need to migrate without cluster downtime you need to setup the new OEL7 nodes following this post: http://ask.xmodulo.com/change-network-interface-name-centos7.html

Otherwise, you need to configure a new interface name for the cluster with oifcfg.

HTH

—

Ludovico

Get information about Cursor Sharing for a SQL_ID

Posted on October 29, 2015 by Ludovico

Yesterday I’ve got a weird problem with Adaptive Cursor Sharing. I’m not sure yet about the issue, but it seems to be related to cursor sharing histograms. Hopefully one day I will blog about what I’ve learnt from this experience.

To better monitor the problem on that specific query, I’ve prepared this script (tested on 12.1.0.2):

COLUMN Shareable HEADING 'S|H|A|R|E|A|B|L|E'
COLUMN "Bind-Aware" HEADING 'B|I|N|D| |A|W|A|R|E'
COLUMN Sensitive HEADING 'S|E|N|S|I|T|I|V|E'
COLUMN Reoptimizable HEADING 'R|E|O|P|T|I|M|I|Z|A|B|L|E'
BREAK on child_number on Execs on "Gets/Exec" on "Ela/Exec" on "Sensitive" on "Shareable" on "Bind-Aware" on bucket0 on bucket1 on bucket2 on cnt on "Reoptimizable" on is_resolved_adaptive_plan

select * from (select *
  from (
select 
s.child_number,
  s.plan_hash_value,
  executions as Execs, 
  round(buffer_gets/executions) as "Gets/Exec",
  round(elapsed_time/executions) as "Ela/Exec",
  is_bind_sensitive as "Sensitive",
  is_shareable as "Shareable",
  is_bind_aware as "Bind-Aware",
  to_char(h.bucket_id) as bucket, h.count as cnt,
  is_reoptimizable as "Reoptimizable",
  is_resolved_adaptive_plan,
  "UNBOUND_CURSOR",  "SQL_TYPE_MISMATCH",  "OPTIMIZER_MISMATCH",
  "OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",
  "FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",
  "PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",
  "TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",
  "DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",
  "BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",
  "REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",
  "OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",
  "USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",
  "FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",
  "TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",
  "DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",
  "CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",
  "PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",
  "BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",
  "OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",
  "FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",
  "LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",
  "LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",
  "HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",
  "USE_FEEDBACK_STATS"
from v$sql s
  join v$sql_cs_histogram h
    on (s.sql_id=h.sql_id and
	s.child_number=h.child_number and
	s.con_id=h.con_id
	)
  join v$sql_shared_cursor shc
    on (shc.sql_id=h.sql_id and 
	shc.child_number=h.child_number and
	s.con_id=shc.con_id
	)
	where s.sql_id='&sql_id'
)
pivot (sum(cnt) for (bucket) IN ('0' AS Bucket0,'1' AS Bucket1,'2' AS Bucket2))
)
unpivot (result FOR reason_type IN ("UNBOUND_CURSOR",
  "SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",
  "OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",
  "FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",
  "PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",
  "TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",
  "DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",
  "BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",
  "REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",
  "OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",
  "USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",
  "FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",
  "TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",
  "DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",
  "CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",
  "PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",
  "BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",
  "OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",
  "FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",
  "LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",
  "LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",
  "HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",
  "USE_FEEDBACK_STATS"))
where result='Y'
order by child_number;

COLUMN Shareable HEADING 'S|H|A|R|E|A|B|L|E'

COLUMN "Bind-Aware" HEADING 'B|I|N|D| |A|W|A|R|E'

COLUMN Sensitive HEADING 'S|E|N|S|I|T|I|V|E'

COLUMN Reoptimizable HEADING 'R|E|O|P|T|I|M|I|Z|A|B|L|E'

BREAK on child_number on Execs on "Gets/Exec" on "Ela/Exec" on "Sensitive" on "Shareable" on "Bind-Aware" on bucket0 on bucket1 on bucket2 on cnt on "Reoptimizable" on is_resolved_adaptive_plan

select * from (select *

from (

select

s.child_number,

s.plan_hash_value,

executions as Execs,

round(buffer_gets/executions) as "Gets/Exec",

round(elapsed_time/executions) as "Ela/Exec",

is_bind_sensitive as "Sensitive",

is_shareable as "Shareable",

is_bind_aware as "Bind-Aware",

to_char(h.bucket_id) as bucket, h.count as cnt,

is_reoptimizable as "Reoptimizable",

is_resolved_adaptive_plan,

"UNBOUND_CURSOR", "SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",

"OUTLINE_MISMATCH", "STATS_ROW_MISMATCH", "LITERAL_MISMATCH",

"FORCE_HARD_PARSE", "EXPLAIN_PLAN_CURSOR", "BUFFERED_DML_MISMATCH",

"PDML_ENV_MISMATCH", "INST_DRTLD_MISMATCH", "SLAVE_QC_MISMATCH",

"TYPECHECK_MISMATCH", "AUTH_CHECK_MISMATCH", "BIND_MISMATCH",

"DESCRIBE_MISMATCH", "LANGUAGE_MISMATCH", "TRANSLATION_MISMATCH",

"BIND_EQUIV_FAILURE", "INSUFF_PRIVS", "INSUFF_PRIVS_REM",

"REMOTE_TRANS_MISMATCH", "LOGMINER_SESSION_MISMATCH", "INCOMP_LTRL_MISMATCH",

"OVERLAP_TIME_MISMATCH", "EDITION_MISMATCH", "MV_QUERY_GEN_MISMATCH",

"USER_BIND_PEEK_MISMATCH", "TYPCHK_DEP_MISMATCH", "NO_TRIGGER_MISMATCH",

"FLASHBACK_CURSOR", "ANYDATA_TRANSFORMATION", "PDDL_ENV_MISMATCH",

"TOP_LEVEL_RPI_CURSOR", "DIFFERENT_LONG_LENGTH", "LOGICAL_STANDBY_APPLY",

"DIFF_CALL_DURN", "BIND_UACS_DIFF", "PLSQL_CMP_SWITCHS_DIFF",

"CURSOR_PARTS_MISMATCH", "STB_OBJECT_MISMATCH", "CROSSEDITION_TRIGGER_MISMATCH",

"PQ_SLAVE_MISMATCH", "TOP_LEVEL_DDL_MISMATCH", "MULTI_PX_MISMATCH",

"BIND_PEEKED_PQ_MISMATCH", "MV_REWRITE_MISMATCH", "ROLL_INVALID_MISMATCH",

"OPTIMIZER_MODE_MISMATCH", "PX_MISMATCH", "MV_STALEOBJ_MISMATCH",

"FLASHBACK_TABLE_MISMATCH", "LITREP_COMP_MISMATCH", "PLSQL_DEBUG",

"LOAD_OPTIMIZER_STATS", "ACL_MISMATCH", "FLASHBACK_ARCHIVE_MISMATCH",

"LOCK_USER_SCHEMA_FAILED", "REMOTE_MAPPING_MISMATCH", "LOAD_RUNTIME_HEAP_FAILED",

"HASH_MATCH_FAILED", "PURGED_CURSOR", "BIND_LENGTH_UPGRADEABLE",

"USE_FEEDBACK_STATS"

from v$sql s

join v$sql_cs_histogram h

on (s.sql_id=h.sql_id and

s.child_number=h.child_number and

s.con_id=h.con_id

)

join v$sql_shared_cursor shc

on (shc.sql_id=h.sql_id and

shc.child_number=h.child_number and

s.con_id=shc.con_id

)

where s.sql_id='&sql_id'

)

pivot (sum(cnt) for (bucket) IN ('0' AS Bucket0,'1' AS Bucket1,'2' AS Bucket2))

)

unpivot (result FOR reason_type IN ("UNBOUND_CURSOR",

"SQL_TYPE_MISMATCH", "OPTIMIZER_MISMATCH",