DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Get the Most out of Oracle Data Guard – The material

Posted on September 29, 2017 by Ludovico

Here we go: as usual, the feedback that I usually get after my talks (specifically, after POUG High Five conference), is if I will share my demo scripts and material.

Sadly, the demos I am doing for my presentation “Get the most out of Oracle Data Guard” are quite tied to an environment built for the purpose of the demos. So, do not expect to get scripts easy to use as is, but rather to get some ideas beyond the demo themselves.

I hope they will help to get the whole picture.

Of course, if you need to implement a cloning strategy based on Data Guard or any other solution that I describe in this post, please feel free to contact me, I will be glad to help you implement it in your environment.

Slides

Demo 1

Video:

Scripts:

#!/bin/bash

function tt () {
  title=$@
  pad=$(printf '%0.1s' "-"{1..60})
  echo
  echo
  echo $pad
  echo "- $title"
  echo $pad
}

. .bash_profile

PAUSE=/home/oracle/pause.sh
SYSPWD=Vagrant1_

clear

sid sour_ludo


sudo sed -i -e '/sour-s/d' /var/named/trivadistraining.com
sudo sed -i '$ a\
sour-s1 IN CNAME ludo01\
sour-s2 IN CNAME ludo01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"
tnsping sour_smart

nslookup sour-s1
nslookup sour-s2


$PAUSE

tt "Connect to sour_smart in another terminal"

$PAUSE
clear

tt "Creating Data Guard Configuration resolution"
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  show configuration;
EOF

$PAUSE
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  create configuration sour as primary database is sour_ludo connect identifier is sour_ludo.trivadistraining.com;
  add database sour_vico as connect identifier is sour_vico.trivadistraining.com;
  enable database sour_vico;
  enable configuration;
  host sleep 5;
  show configuration;
EOF

$PAUSE
clear

tt "Modifying the DNS configuration"

sudo sed -i -e '/sour-s2/d' /var/named/trivadistraining.com

sudo sed -i '$ a\
sour-s2 IN CNAME vico01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"
tnsping sour_smart

nslookup sour-s1
nslookup sour-s2

$PAUSE
clear
tt "Switchover to sour_vico"
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  switchover to sour_vico;
EOF

$PAUSE
tt "Did the session fail over?"
$PAUSE

clear

tt "Modifying the DNS configuration"

sudo sed -i -e '/sour-s1/d' /var/named/trivadistraining.com

sudo sed -i '$ a\
sour-s1 IN CNAME vico01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"
tnsping sour_smart

nslookup sour-s1
nslookup sour-s2

$PAUSE

tt "Removing Data Guard configuration"

dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  remove configuration;
  show configuration;
EOF

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#!/bin/bash

function tt () {

title=$@

pad=$(printf '%0.1s' "-"{1..60})

echo

echo $pad

echo "- $title"

echo $pad

}

. .bash_profile

PAUSE=/home/oracle/pause.sh

SYSPWD=Vagrant1_

clear

sid sour_ludo

sudo sed -i -e '/sour-s/d' /var/named/trivadistraining.com

sudo sed -i '$ a\

sour-s1 IN CNAME ludo01\

sour-s2 IN CNAME ludo01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"

tnsping sour_smart

nslookup sour-s1

nslookup sour-s2

$PAUSE

tt "Connect to sour_smart in another terminal"

$PAUSE

clear

tt "Creating Data Guard Configuration resolution"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

show configuration;

EOF

$PAUSE

dgmgrl -echo <<EOF

connect sys/$SYSPWD

create configuration sour as primary database is sour_ludo connect identifier is sour_ludo.trivadistraining.com;

add database sour_vico as connect identifier is sour_vico.trivadistraining.com;

enable database sour_vico;

enable configuration;

host sleep 5;

show configuration;

EOF

$PAUSE

clear

tt "Modifying the DNS configuration"

sudo sed -i -e '/sour-s2/d' /var/named/trivadistraining.com

sudo sed -i '$ a\

sour-s2 IN CNAME vico01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"

tnsping sour_smart

nslookup sour-s1

nslookup sour-s2

$PAUSE

clear

tt "Switchover to sour_vico"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

switchover to sour_vico;

EOF

$PAUSE

tt "Did the session fail over?"

$PAUSE

clear

tt "Modifying the DNS configuration"

sudo sed -i -e '/sour-s1/d' /var/named/trivadistraining.com

sudo sed -i '$ a\

sour-s1 IN CNAME vico01' /var/named/trivadistraining.com

sudo systemctl reload named.service

tt "Naming resolution"

tnsping sour_smart

nslookup sour-s1

nslookup sour-s2

$PAUSE

tt "Removing Data Guard configuration"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

remove configuration;

show configuration;

EOF

Demo 2

Video:

Scripts:

#!/bin/bash

function tt () {
  title=$@
  pad=$(printf '%0.1s' "-"{1..60})
  echo
  echo
  echo $pad
  echo "- $title"
  echo $pad
}

. .bash_profile

clear

sid stout_vico
SYSPWD=Vagrant1_

PAUSE=/home/oracle/pause.sh

tt "Current configuration"
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  show configuration;
EOF

$PAUSE

clear

tt "Instance and redo apply status"
sqlplus / as sysdba <<EOF
  select instance_name, status from v\$instance;
  select db_unique_name, database_role from v\$database;
  select process, status, client_process, sequence#, block#, delay_mins from v\$managed_standby order by process;
EOF

$PAUSE
clear 
tt "Inserting something in the primary"
sqlplus ludo/ludo@stout_ludo <<EOF
  DROP TABLE demo1;
  CREATE TABLE demo1 ( id NUMBER GENERATED AS IDENTITY 
     , foo DATE DEFAULT (sysdate)
     , CONSTRAINT demo1_pk PRIMARY KEY (id)
  );

  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  COMMIT;
  ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  SELECT * FROM demo1 ORDER BY id;
  exit
EOF


$PAUSE
clear
tt "Converting physical standby to snapshot standby"
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  show configuration;
  convert database stout_vico to snapshot standby;
  show configuration;
EOF


$PAUSE
tt "Let's check the alert log (another window)"

$PAUSE
clear
tt "Instance and redo apply status"
sqlplus / as sysdba <<EOF
  SELECT instance_name, status FROM v\$instance;
  SELECT db_unique_name, database_role FROM v\$database;
  set lines 180
  col name for a80
  SELECT scn, name FROM v\$restore_point;
  SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby ORDER BY process;
  set feedback off
  SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';
  EXEC dbms_lock.sleep(2);
  SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';
  EXEC dbms_lock.sleep(2);
  SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';
EOF


$PAUSE
clear
tt "Let's do something in the PRIMARY database!"
sqlplus ludo/ludo@stout_ludo <<EOF
  ALTER TABLE demo1 ADD test VARCHAR(20) DEFAULT ('PRIMARY'); 
  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  COMMIT;
  ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  SELECT * FROM demo1 ORDER BY id;
  exit
EOF


$PAUSE
clear
tt "Let's do something in the snapshot standby!"
sqlplus ludo/ludo@stout_vico <<EOF
  ALTER TABLE demo1 ADD test VARCHAR(20) DEFAULT ('STANDBY'); 
  INSERT INTO demo1 (foo) VALUES(sysdate);
  INSERT INTO demo1 (foo) VALUES(sysdate);
  COMMIT;
  ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  SELECT * FROM demo1 ORDER BY id;
  exit
EOF

$PAUSE
clear

tt "Convert back to physical standby"
dgmgrl -echo <<EOF
  connect sys/$SYSPWD
  show configuration;
  convert database stout_vico to physical standby;
  show configuration;
EOF

$PAUSE
clear
tt "Instance and redo apply status"
sqlplus / as sysdba <<EOF
  SELECT instance_name, status FROM v\$instance;
  SELECT db_unique_name, database_role FROM v\$database;
  set lines 180
  col name for a80
  SELECT scn, name FROM v\$restore_point;
  SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby ORDER BY process;
EOF

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

#!/bin/bash

function tt () {

title=$@

pad=$(printf '%0.1s' "-"{1..60})

echo

echo $pad

echo "- $title"

echo $pad

}

. .bash_profile

clear

sid stout_vico

SYSPWD=Vagrant1_

PAUSE=/home/oracle/pause.sh

tt "Current configuration"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

show configuration;

EOF

$PAUSE

clear

tt "Instance and redo apply status"

sqlplus / as sysdba <<EOF

select instance_name, status from v\$instance;

select db_unique_name, database_role from v\$database;

select process, status, client_process, sequence#, block#, delay_mins from v\$managed_standby order by process;

EOF

$PAUSE

clear

tt "Inserting something in the primary"

sqlplus ludo/ludo@stout_ludo <<EOF

DROP TABLE demo1;

CREATE TABLE demo1 ( id NUMBER GENERATED AS IDENTITY

, foo DATE DEFAULT (sysdate)

, CONSTRAINT demo1_pk PRIMARY KEY (id)

);

INSERT INTO demo1 (foo) VALUES(sysdate);

COMMIT;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT * FROM demo1 ORDER BY id;

exit

EOF

$PAUSE

clear

tt "Converting physical standby to snapshot standby"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

show configuration;

convert database stout_vico to snapshot standby;

show configuration;

EOF

$PAUSE

tt "Let's check the alert log (another window)"

$PAUSE

clear

tt "Instance and redo apply status"

sqlplus / as sysdba <<EOF

SELECT instance_name, status FROM v\$instance;

SELECT db_unique_name, database_role FROM v\$database;

set lines 180

col name for a80

SELECT scn, name FROM v\$restore_point;

SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby ORDER BY process;

set feedback off

SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';

EXEC dbms_lock.sleep(2);

SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';

EXEC dbms_lock.sleep(2);

SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby WHERE client_process='LGWR';

EOF

$PAUSE

clear

tt "Let's do something in the PRIMARY database!"

sqlplus ludo/ludo@stout_ludo <<EOF

ALTER TABLE demo1 ADD test VARCHAR(20) DEFAULT ('PRIMARY');

INSERT INTO demo1 (foo) VALUES(sysdate);

COMMIT;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT * FROM demo1 ORDER BY id;

exit

EOF

$PAUSE

clear

tt "Let's do something in the snapshot standby!"

sqlplus ludo/ludo@stout_vico <<EOF

ALTER TABLE demo1 ADD test VARCHAR(20) DEFAULT ('STANDBY');

INSERT INTO demo1 (foo) VALUES(sysdate);

COMMIT;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT * FROM demo1 ORDER BY id;

exit

EOF

$PAUSE

clear

tt "Convert back to physical standby"

dgmgrl -echo <<EOF

connect sys/$SYSPWD

show configuration;

convert database stout_vico to physical standby;

show configuration;

EOF

$PAUSE

clear

tt "Instance and redo apply status"

sqlplus / as sysdba <<EOF

SELECT instance_name, status FROM v\$instance;

SELECT db_unique_name, database_role FROM v\$database;

set lines 180

col name for a80

SELECT scn, name FROM v\$restore_point;

SELECT process, status, client_process, sequence#, block#, delay_mins FROM v\$managed_standby ORDER BY process;

EOF

Demo 3

Video:

Scripts:

Preparation:

#!/bin/bash

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

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

[[ -L /u02/$NEWNAME ]] && rm $/u02/$NEWNAME
ln -s /u02/acfs/.ACFS/snaps/$NEWNAME /u02/$NEWNAME

set -x
$ORACLE_HOME/bin/srvctl add database -db $NEWNAME -oraclehome $ORACLE_HOME -dbtype SINGLE -instance $NEWNAME -spfile /u02/$NEWNAME/spfile$NEWNAME.ora -dbname $NEWNAME -policy MANUAL -acfspath "/u02/acfs,/u02/fra" -node $HOSTNAME

set +x

#!/bin/bash

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

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

export ORACLE_SID=$NEWNAME

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

[[ -L /u02/$NEWNAME ]] && rm $/u02/$NEWNAME

ln -s /u02/acfs/.ACFS/snaps/$NEWNAME /u02/$NEWNAME

set -x

$ORACLE_HOME/bin/srvctl add database -db $NEWNAME -oraclehome $ORACLE_HOME -dbtype SINGLE -instance $NEWNAME -spfile /u02/$NEWNAME/spfile$NEWNAME.ora -dbname $NEWNAME -policy MANUAL -acfspath "/u02/acfs,/u02/fra" -node $HOSTNAME

set +x

snap_acfs.pl

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl
#
# Purpose..........: Create a new snapshot with rotating name
# 
# snap_acfs.pl 
#        -p <parent> : name of the parent snapshot
#        -n <name>   : prefix of the snapshot
#        -s <suffix> : optional, use "weekday" to have the day name as suffix (Sun - Sat)
#
# e.g. snap_acfs.pl -p stout -n stout  -s "weekday"
#      will clone from /u02/acfs/.ACFS/snaps/stout
#                   to /u02/acfs/.ACFS/snaps/stout.Tue (or whatever the day is)
#      
# e.g. snap_acfs.pl -n stout -p stout.Mon 
#      will clone from /u02/acfs/.ACFS/snaps/stout.Mon
#                   to /u02/acfs/.ACFS/snaps/stout
#      
# e.g. snap_acfs.pl -n stout2 -p stout
#      will clone from /u02/acfs/.ACFS/snaps/stout
#                   to /u02/acfs/.ACFS/snaps/stout2
#      
# EXISTING SNAPSHOT WILL BE DROPPED!!
#
#
#

use strict;
use File::Copy;
use Net::SMTP;
use Sys::Hostname;
use Getopt::Std 'getopts';
use File::Basename;

my $CloneDIR;                             # predefine rootDir variable
BEGIN {
  use FindBin qw($Bin);                   # get the current path of script
  use Cwd 'abs_path';
  $CloneDIR    = abs_path("$Bin/..");     # get the absolut rood path to clone directory
}

my $CloneLOGDir = $CloneDIR."/log";       # LOG Directory
my $baseACFS = "/u02/acfs/";
my $ORA_CRS_HOME = "/u01/app/grid/12.2.0.1";
my $acfsutil = "/usr/sbin/acfsutil";
my $basename    = basename($0, ".pl");
my $ParentSnapName;
my $ParentSnap=0; ## no parent snapshots by default
my $PrefixName;
my $NewName;
my $SuffixName;
my %opts;
my $MountPoint;
my $SnapCreate;

################################################################################
#  Main
################################################################################
my $StartDate = localtime;
&DoMsg ("Start of $basename.pl");
unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {
	&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");
    exit 1;
}

# Process command line arguments
if  ( ! defined @ARGV ) { &Usage; exit 1; } 
getopts('n:p:s:b:', \%opts);

if ($opts{"p"}) {
   $ParentSnapName    = lc($opts{"p"});
} else {
   &DoMsg ("Parent snapshot name not given!");
   &Usage;
   exit 1;
}
if ($opts{"n"}) {
   $PrefixName    = lc($opts{"n"});
} else {
   &DoMsg ("New snapshot prefix not given! Defaults to ${ParentSnapName}");
   $PrefixName    = "${ParentSnapName}";
}

if ($opts{"s"}) {
   $SuffixName    = lc($opts{"s"});
   if ( $SuffixName eq "weekday" ) {
      $SuffixName    = lc(&getWeekDay);
   }
   $SuffixName  = "." . $SuffixName;
} else {
   $SuffixName = "";
}

$NewName = "${PrefixName}${SuffixName}";


&DoMsg ("Parent: $ParentSnapName");
&DoMsg ("Prefix: $PrefixName");
&DoMsg ("Suffix: $SuffixName");
&DoMsg ("New Name: $NewName");


$MountPoint = $baseACFS;
$SnapCreate = "$acfsutil snap create -w -p $ParentSnapName $NewName $MountPoint";
&DoMsg ("Create Command: $SnapCreate ");


my $cmd = "$acfsutil snap info $NewName $MountPoint";
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("", <CMD>));
close CMD;
if ( $? != 0 ) {
   &DoMsg ("Snapshot $NewName does not exist inside mount point $MountPoint. Continuing.");
} else {
   &DoMsg ("Snapshot $NewName already exists inside mount point $MountPoint. Now it will be deleted.");
   $cmd = "$acfsutil snap delete $NewName $MountPoint";
   &DoMsg ($cmd);
   open( CMD, $cmd . " |");
   &DoMsg (join("", <CMD>));
   close CMD;
   if ( $? != 0 ) {
      &DoMsg ("Cannot delete Snapshot $NewName in mount point $MountPoint. Script will exit.");
      exit 1;
   }
}

&DoMsg ("Creating the new snapshot:");
&DoMsg ($SnapCreate);
open( CMD, $SnapCreate . " |");
&DoMsg (join("", <CMD>));
close CMD;
if ( $? != 0 ) {
   &DoMsg ("Cannot create Snapshot $NewName in mount point $MountPoint. Script will exit.");
   exit 1;
} #else {
   #&DoMsg ("Current snapshots:");
   #open( CMD, "$acfsutil snap info $MountPoint |");
   #&DoMsg (join("", <CMD>));
   #close CMD;
#}



#-------------------------------------------------------------------------------
# DoMsg
#
# PURPOSE    : echo with timestamp YYYY-MM-DD_H24:MI:SS
# PARAMS     : $*: the messages
# GLOBAL VARS: none
#-------------------------------------------------------------------------------   
sub DoMsg {

   my $msg = shift;
   my $timestamp = &getTimestamp;
   
   print ("$timestamp $msg\n");
   if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}
}


#-------------------------------------------------------------------------------
# getTimestamp
#
# PURPOSE    : returns timestamp in different formats
# PARAMS     : format_parm
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getTimestamp {
   #
   # Format 1:  dd-mm-yyyy_hh24:mi:ss
   # Format 2:  dd.mm.yyyy_hh24miss
   # Format 3:  dd.mm.yyyy
   # Format 4:  hh24:mi:ss
   # Rest:      dd.mm.yyyy hh24:mi:ss  (default)
   #
   my $Parm = shift;
   my $date;
   my $date2;
   my $heure;
   my $heure2;
   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

   if ( length($Parm) > 1 ) {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);
   }
   else {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
   }
   
   $date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);
   $date =~ s/ /0/g;
   $date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);
   $date2 =~ s/ /0/g;
   $heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));
   $heure =~ s/ /0/g;
   $heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));
   $heure2 =~ s/ /0/g;
   
   if    ($Parm eq "1") { return ($date2."_".$heure) }
   elsif ($Parm eq "2") { return ($date."_".$heure2) }
   elsif ($Parm eq "3") { return ($date) }
   elsif ($Parm eq "4") { return ($heure) }
   else { return ($date." ".$heure) };

}


#-------------------------------------------------------------------------------
# getWeekDay
#
# PURPOSE    : returns weekday (Sun - Sat)
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getWeekDay{
   my @date = split(" ", localtime(time));
   my $day = $date[0];
   return ($day);
}


#-------------------------------------------------------------------------------
# Usage
#
# PURPOSE    : print the Usage
# PARAMS     : none
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub Usage {

   print <<EOF
   
Usage:  $basename -b <base>  [Optional Arguments]
          -p <parent> : name of the parent snapshot
       
           Optional Arguments:
          -n <prefix_name> : prefix of the new snapshot name (defaults to parent.18H)
          -s <suffix>      : use "weekday" to have the day name as suffix (Sun - Sat)


 e.g. snap_acfs.pl -p scprod -n stout  -s "weekday"
      will clone from /u02/acfs/.ACFS\snaps\stout
                   to /u02/acfs/.ACFS\snaps\stout.Tue (or whatever the day is)
      
 e.g. snap_acfs.pl -n stout -p stout.Mon 
      will clone from /u02/acfs/.ACFS\snaps\stout.Mon
                   to /u02/acfs/.ACFS\snaps\stout
      
 e.g. snap_acfs.pl -n stout2 -p stout
      will clone from /u02/acfs/.ACFS\snaps\stout
                   to /u02/acfs/.ACFS\snaps\stout2
           
  EXISTING SNAPSHOT WILL BE DROPPED!!
EOF

}

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

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl

# Purpose..........: Create a new snapshot with rotating name

# snap_acfs.pl

# -p <parent> : name of the parent snapshot

# -n <name> : prefix of the snapshot

# -s <suffix> : optional, use "weekday" to have the day name as suffix (Sun - Sat)

# e.g. snap_acfs.pl -p stout -n stout -s "weekday"

# will clone from /u02/acfs/.ACFS/snaps/stout

# to /u02/acfs/.ACFS/snaps/stout.Tue (or whatever the day is)

# e.g. snap_acfs.pl -n stout -p stout.Mon

# will clone from /u02/acfs/.ACFS/snaps/stout.Mon

# to /u02/acfs/.ACFS/snaps/stout

# e.g. snap_acfs.pl -n stout2 -p stout

# will clone from /u02/acfs/.ACFS/snaps/stout

# to /u02/acfs/.ACFS/snaps/stout2

# EXISTING SNAPSHOT WILL BE DROPPED!!

use strict;

use File::Copy;

use Net::SMTP;

use Sys::Hostname;

use Getopt::Std 'getopts';

use File::Basename;

my $CloneDIR; # predefine rootDir variable

BEGIN {

use FindBin qw($Bin); # get the current path of script

use Cwd 'abs_path';

$CloneDIR = abs_path("$Bin/.."); # get the absolut rood path to clone directory

}

my $CloneLOGDir = $CloneDIR."/log"; # LOG Directory

my $baseACFS = "/u02/acfs/";

my $ORA_CRS_HOME = "/u01/app/grid/12.2.0.1";

my $acfsutil = "/usr/sbin/acfsutil";

my $basename = basename($0, ".pl");

my $ParentSnapName;

my $ParentSnap=0; ## no parent snapshots by default

my $PrefixName;

my $NewName;

my $SuffixName;

my %opts;

my $MountPoint;

my $SnapCreate;

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

# Main

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

my $StartDate = localtime;

&DoMsg ("Start of $basename.pl");

unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {

&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");

exit 1;

}

# Process command line arguments

if ( ! defined @ARGV ) { &Usage; exit 1; }

getopts('n:p:s:b:', \%opts);

if ($opts{"p"}) {

$ParentSnapName = lc($opts{"p"});

} else {

&DoMsg ("Parent snapshot name not given!");

&Usage;

exit 1;

}

if ($opts{"n"}) {

$PrefixName = lc($opts{"n"});

} else {

&DoMsg ("New snapshot prefix not given! Defaults to ${ParentSnapName}");

$PrefixName = "${ParentSnapName}";

}

if ($opts{"s"}) {

$SuffixName = lc($opts{"s"});

if ( $SuffixName eq "weekday" ) {

$SuffixName = lc(&getWeekDay);

}

$SuffixName = "." . $SuffixName;

} else {

$SuffixName = "";

}

$NewName = "${PrefixName}${SuffixName}";

&DoMsg ("Parent: $ParentSnapName");

&DoMsg ("Prefix: $PrefixName");

&DoMsg ("Suffix: $SuffixName");

&DoMsg ("New Name: $NewName");

$MountPoint = $baseACFS;

$SnapCreate = "$acfsutil snap create -w -p $ParentSnapName $NewName $MountPoint";

&DoMsg ("Create Command: $SnapCreate ");

my $cmd = "$acfsutil snap info $NewName $MountPoint";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

if ( $? != 0 ) {

&DoMsg ("Snapshot $NewName does not exist inside mount point $MountPoint. Continuing.");

} else {

&DoMsg ("Snapshot $NewName already exists inside mount point $MountPoint. Now it will be deleted.");

$cmd = "$acfsutil snap delete $NewName $MountPoint";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

if ( $? != 0 ) {

&DoMsg ("Cannot delete Snapshot $NewName in mount point $MountPoint. Script will exit.");

exit 1;

}

&DoMsg ("Creating the new snapshot:");

&DoMsg ($SnapCreate);

open( CMD, $SnapCreate . " |");

&DoMsg (join("", <CMD>));

close CMD;

if ( $? != 0 ) {

&DoMsg ("Cannot create Snapshot $NewName in mount point $MountPoint. Script will exit.");

exit 1;

} #else {

#&DoMsg ("Current snapshots:");

#open( CMD, "$acfsutil snap info $MountPoint |");

#&DoMsg (join("", <CMD>));

#close CMD;

#-------------------------------------------------------------------------------

# DoMsg

# PURPOSE : echo with timestamp YYYY-MM-DD_H24:MI:SS

# PARAMS : $*: the messages

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub DoMsg {

my $msg = shift;

my $timestamp = &getTimestamp;

print ("$timestamp $msg\n");

if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}

}

#-------------------------------------------------------------------------------

# getTimestamp

# PURPOSE : returns timestamp in different formats

# PARAMS : format_parm

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getTimestamp {

# Format 1: dd-mm-yyyy_hh24:mi:ss

# Format 2: dd.mm.yyyy_hh24miss

# Format 3: dd.mm.yyyy

# Format 4: hh24:mi:ss

# Rest: dd.mm.yyyy hh24:mi:ss (default)

my $Parm = shift;

my $date;

my $date2;

my $heure;

my $heure2;

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

if ( length($Parm) > 1 ) {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);

}

else {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;

}

$date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);

$date =~ s/ /0/g;

$date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);

$date2 =~ s/ /0/g;

$heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));

$heure =~ s/ /0/g;

$heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));

$heure2 =~ s/ /0/g;

if ($Parm eq "1") { return ($date2."_".$heure) }

elsif ($Parm eq "2") { return ($date."_".$heure2) }

elsif ($Parm eq "3") { return ($date) }

elsif ($Parm eq "4") { return ($heure) }

else { return ($date." ".$heure) };

}

#-------------------------------------------------------------------------------

# getWeekDay

# PURPOSE : returns weekday (Sun - Sat)

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getWeekDay{

my @date = split(" ", localtime(time));

my $day = $date[0];

return ($day);

}

#-------------------------------------------------------------------------------

# Usage

# PURPOSE : print the Usage

# PARAMS : none

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub Usage {

print <<EOF

Usage: $basename -b <base> [Optional Arguments]

-p <parent> : name of the parent snapshot

Optional Arguments:

-n <prefix_name> : prefix of the new snapshot name (defaults to parent.18H)

-s <suffix> : use "weekday" to have the day name as suffix (Sun - Sat)

e.g. snap_acfs.pl -p scprod -n stout -s "weekday"

will clone from /u02/acfs/.ACFS\snaps\stout

to /u02/acfs/.ACFS\snaps\stout.Tue (or whatever the day is)

e.g. snap_acfs.pl -n stout -p stout.Mon

will clone from /u02/acfs/.ACFS\snaps\stout.Mon

to /u02/acfs/.ACFS\snaps\stout

e.g. snap_acfs.pl -n stout2 -p stout

will clone from /u02/acfs/.ACFS\snaps\stout

to /u02/acfs/.ACFS\snaps\stout2

EXISTING SNAPSHOT WILL BE DROPPED!!

EOF

}

snap_databasae.pl

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl
#
# Purpose..........: Create a new snapshot of a standby database by apply-off, backup controlfile to trace, copy init, acfs snap, apply-on
# 
# snap_database.pl 
#        -b <base>
#        -n <name>   : prefix of the snapshot
#        -s <suffix> : optional, use "weekday" to have the day name as suffix (Sun - Sat)
#
# e.g. snap_database.pl -b stout -n stout_save  -s "weekday"
#      will clone from /u02/acfs/.ACFS/snaps/stout
#                   to /u02/acfs/.ACFS/snaps/stout_save.Tue (or whatever the day is)
#      
# EXISTING SNAPSHOT WILL BE DROPPED!!
#

#use strict;
use File::Copy;
use Net::SMTP;
use Sys::Hostname;
use Getopt::Std 'getopts';
use File::Basename;
use DBI;
use DBD::Oracle qw(:ora_session_modes);

my $CloneDIR;                             # predefine rootDir variable
BEGIN {
  use FindBin qw($Bin);                   # get the current path of script
  use Cwd 'abs_path';
  $CloneDIR    = abs_path("$Bin/..");     # get the absolut rood path to clone directory
}

my $CloneLOGDir = $CloneDIR."/log";       # LOG Directory
my $baseACFS = "/u02/acfs";
my $basename    = basename($0, ".pl");
my $PrefixName;
my $BaseDB;
my $SuffixName;
my $SnapshotName;
my %opts;
my $dbh;
my $db_create_file_dest;
my $db_unique_name;
my $cmd;
my $syspwd="Vagrant1_";
my $SnapError=0;
my $SnapDir;
my $ControlfileTrace = "control.trc";
my $ORACLE_HOME = "/u01/app/oracle/product/12.2.0.1/dbhome_1";
my $InitName = "init.ora";
my $warnings = 0;

################################################################################
#  Main
################################################################################
my $StartDate = localtime;
&DoMsg ("Start of $basename.pl");
unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {
	&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");
    exit 1;
}

# Process command line arguments
if  ( ! defined @ARGV ) { &Usage; exit 1; } 
getopts('b:n:s:', \%opts);

if ($opts{"b"}) {
   $BaseDB = lc($opts{"b"});
} else {
   &DoMsg ("Base DB not given!");
   &Usage;
   exit 1;
}
if ($opts{"n"}) {
   $PrefixName    = lc($opts{"n"});
} else {
   $PrefixName    = "${BaseDB}_save";
}
if ($opts{"s"}) {
   $SuffixName    = lc($opts{"s"});
   if ( $SuffixName eq "weekday" ) {
      $SuffixName    = lc(&getWeekDay);
   }
   $SuffixName  = "." . $SuffixName;
} else {
   $SuffixName = "";
}

$SnapshotName = "${PrefixName}${SuffixName}";


&DoMsg ("Base: $BaseDB");
&DoMsg ("SnapshotName: $SnapshotName");

&ConnectDB ;

### checking that the database is mounted and physical standby

my $DBstatus= &QueryOneValue('select status from v$instance');
unless ( $DBstatus eq "MOUNTED" ) {
   &DoMsg ("Database is not in MOUNTED status, this is unexpected. Exiting.");
   exit 1
}

my $DBrole= &QueryOneValue('SELECT database_role FROM v$database');
unless ( $DBrole eq "PHYSICAL STANDBY" ) {
   &DoMsg ("Database role is not PHYSICAL STANDBY, this is unexpected. Exiting.");
   exit 1
}


$db_create_file_dest= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_create_file_dest'});
 &DoMsg ("db_create_file_dest: $db_create_file_dest");

$db_unique_name= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_unique_name'});
 &DoMsg ("db_unique_name: $db_unique_name");

#unless ($dbh->do(qq{ALTER SESSION SYNC WITH PRIMARY}) ) {
#   &DoMsg ("Error in syncing the session with the primary");
#   $warnings++;
#}

$cmd = qq{dgmgrl -echo sys/$syspwd "edit database $db_unique_name set state=\\\"APPLY-OFF\\\";"};
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("",<CMD>));
close (CMD);
my $a=$?;
#if ( $? != 0 ) {
#   &DoMsg ("Error in stopping apply on standby $BaseDB. Exiting.");
#   exit 1
#}


$cmd = $CloneDIR."/bin/snap_acfs.pl -p $BaseDB -n $SnapshotName";
&DoMsg($cmd);
open( CMD, $cmd . " |");
print (join("", <CMD>)); ## only print here as it logs and echoes its time as well
close CMD;
#if ( $? != 0 ) {
#   # track if error in creating the snapshot: we continue and do the apply-on anyway!
#   $SnapError=1;
#}

$SnapDir = $baseACFS . "/.ACFS/snaps/" . $SnapshotName;
$ControlfileTrace = $SnapDir . "/" . $ControlfileTrace;
$InitName = $SnapDir . "/" . $InitName;

unless ($dbh->do(qq{ ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '$ControlfileTrace' REUSE RESETLOGS}) ) {
   &DoMsg ("Error in taking the controlfile trace $ControlfileTrace.");
   $warnings++;
}

unless ($dbh->do(qq{ CREATE PFILE='$InitName' FROM SPFILE }) ) {
   &DoMsg ("Error in creating the pfile $InitName.");
   $warnings++;
}

$cmd = qq{dgmgrl -echo sys/$syspwd "edit database $db_unique_name set state=\"APPLY-ON\""};
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("", <CMD>));
close CMD;
#if ( $? != 0 ) {
#   &DoMsg ("Error in starting apply on standby $BaseDB. MANUAL INTERVENTION REQUIRED");
#   exit 1
#}

if ( $SnapError == 1 ) {
	&DoMsg ("There was an error in creating the snapshot. Exiting.");
        exit 1;
}



if ( $warnings != 0 ) {
   &DoMsg("There have been some warnings, but the procedure completed.");
} else {
   &DoMsg("The procedure completed successfully.");
}

&DisconnectDB ;


#-------------------------------------------------------------------------------
# DoMsg
#
# PURPOSE    : echo with timestamp YYYY-MM-DD_H24:MI:SS
# PARAMS     : $*: the messages
# GLOBAL VARS: none
#-------------------------------------------------------------------------------   
sub DoMsg {

   my $msg = shift;
   my $timestamp = &getTimestamp;
   
   print ("$timestamp $msg\n");
   if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}
}


#-------------------------------------------------------------------------------
# getTimestamp
#
# PURPOSE    : returns timestamp in different formats
# PARAMS     : format_parm
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getTimestamp {
   #
   # Format 1:  dd-mm-yyyy_hh24:mi:ss
   # Format 2:  dd.mm.yyyy_hh24miss
   # Format 3:  dd.mm.yyyy
   # Format 4:  hh24:mi:ss
   # Rest:      dd.mm.yyyy hh24:mi:ss  (default)
   #
   my $Parm = shift;
   my $date;
   my $date2;
   my $heure;
   my $heure2;
   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

   if ( length($Parm) > 1 ) {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);
   }
   else {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
   }
   
   $date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);
   $date =~ s/ /0/g;
   $date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);
   $date2 =~ s/ /0/g;
   $heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));
   $heure =~ s/ /0/g;
   $heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));
   $heure2 =~ s/ /0/g;
   
   if    ($Parm eq "1") { return ($date2."_".$heure) }
   elsif ($Parm eq "2") { return ($date."_".$heure2) }
   elsif ($Parm eq "3") { return ($date) }
   elsif ($Parm eq "4") { return ($heure) }
   else { return ($date." ".$heure) };

}


#-------------------------------------------------------------------------------
# getWeekDay
#
# PURPOSE    : returns weekday (Sun - Sat)
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getWeekDay{
   my @date = split(" ", localtime(time));
   my $day = $date[0];
   return ($day);
}



#-------------------------------------------------------------------------------
# Usage
#
# PURPOSE    : print the Usage
# PARAMS     : none
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub Usage {

   print <<EOF
   
Usage:  $basename -b <base>  [Optional Arguments]
           -b <base>       : name of the base database
       
        Purpose:
          Create a new snapshot of a standby database by apply-off, acfs snap, backup controlfile to trace, copy init, apply-on.

        Optional Arguments:
          -n <prefix_name> : prefix of the new snapshot name
          -s <suffix>      : use "weekday" to have the day name as suffix (Sun - Sat)

        examples:
            snap_database.pl -b stout -n stout.18h  -s "weekday"
            will clone from /u02/acfs/.ACFS/snaps/stout
                         to /u02/acfs/.ACFS/snaps/stout.18h.Tue (or whatever the day is)

      
            $basename -b stout -s "weekday"
            will clone from /u02/acfs/.ACFS/snaps/stout
                         to /u02/acfs/.ACFS/snaps/stout_save.Wed  (or whatever the day is)
      
  EXISTING SNAPSHOT WILL BE DROPPED!!

EOF

}


sub ConnectDB {

   # DB connection #
   $ENV{ORACLE_SID}=$BaseDB;
   $ENV{ORACLE_HOME}=$ORACLE_HOME;
   delete $ENV{TWO_TASK};

   &DoMsg ("Connecting to DB $BaseDB");
   unless ($dbh = DBI->connect('dbi:Oracle:', "sys", $syspwd, {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA}))  {
      &DoMsg ("Error connecting to DB: ". $DBI::errstr);
      exit(1);
   }

   #&DoMsg ("Connected to DB $BaseDB");

}

sub QueryOneValue {

   my $sth;
   my $query = shift;

   unless ($sth = $dbh->prepare ($query)) {
      &DoMsg ("Error preparing statement $query: ".$dbh->errstr);
   }
   $sth->execute;
   my ($result) = $sth->fetchrow_array;

   return $result;
}

sub DisconnectDB {
   $dbh->disconnect;
}

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

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl

# Purpose..........: Create a new snapshot of a standby database by apply-off, backup controlfile to trace, copy init, acfs snap, apply-on

# snap_database.pl

# -b <base>

# -n <name> : prefix of the snapshot

# -s <suffix> : optional, use "weekday" to have the day name as suffix (Sun - Sat)

# e.g. snap_database.pl -b stout -n stout_save -s "weekday"

# will clone from /u02/acfs/.ACFS/snaps/stout

# to /u02/acfs/.ACFS/snaps/stout_save.Tue (or whatever the day is)

# EXISTING SNAPSHOT WILL BE DROPPED!!

#use strict;

use File::Copy;

use Net::SMTP;

use Sys::Hostname;

use Getopt::Std 'getopts';

use File::Basename;

use DBI;

use DBD::Oracle qw(:ora_session_modes);

my $CloneDIR; # predefine rootDir variable

BEGIN {

use FindBin qw($Bin); # get the current path of script

use Cwd 'abs_path';

$CloneDIR = abs_path("$Bin/.."); # get the absolut rood path to clone directory

}

my $CloneLOGDir = $CloneDIR."/log"; # LOG Directory

my $baseACFS = "/u02/acfs";

my $basename = basename($0, ".pl");

my $PrefixName;

my $BaseDB;

my $SuffixName;

my $SnapshotName;

my %opts;

my $dbh;

my $db_create_file_dest;

my $db_unique_name;

my $cmd;

my $syspwd="Vagrant1_";

my $SnapError=0;

my $SnapDir;

my $ControlfileTrace = "control.trc";

my $ORACLE_HOME = "/u01/app/oracle/product/12.2.0.1/dbhome_1";

my $InitName = "init.ora";

my $warnings = 0;

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

# Main

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

my $StartDate = localtime;

&DoMsg ("Start of $basename.pl");

unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {

&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");

exit 1;

}

# Process command line arguments

if ( ! defined @ARGV ) { &Usage; exit 1; }

getopts('b:n:s:', \%opts);

if ($opts{"b"}) {

$BaseDB = lc($opts{"b"});

} else {

&DoMsg ("Base DB not given!");

&Usage;

exit 1;

}

if ($opts{"n"}) {

$PrefixName = lc($opts{"n"});

} else {

$PrefixName = "${BaseDB}_save";

}

if ($opts{"s"}) {

$SuffixName = lc($opts{"s"});

if ( $SuffixName eq "weekday" ) {

$SuffixName = lc(&getWeekDay);

}

$SuffixName = "." . $SuffixName;

} else {

$SuffixName = "";

}

$SnapshotName = "${PrefixName}${SuffixName}";

&DoMsg ("Base: $BaseDB");

&DoMsg ("SnapshotName: $SnapshotName");

&ConnectDB ;

### checking that the database is mounted and physical standby

my $DBstatus= &QueryOneValue('select status from v$instance');

unless ( $DBstatus eq "MOUNTED" ) {

&DoMsg ("Database is not in MOUNTED status, this is unexpected. Exiting.");

exit 1

}

my $DBrole= &QueryOneValue('SELECT database_role FROM v$database');

unless ( $DBrole eq "PHYSICAL STANDBY" ) {

&DoMsg ("Database role is not PHYSICAL STANDBY, this is unexpected. Exiting.");

exit 1

}

$db_create_file_dest= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_create_file_dest'});

&DoMsg ("db_create_file_dest: $db_create_file_dest");

$db_unique_name= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_unique_name'});

&DoMsg ("db_unique_name: $db_unique_name");

#unless ($dbh->do(qq{ALTER SESSION SYNC WITH PRIMARY}) ) {

# &DoMsg ("Error in syncing the session with the primary");

# $warnings++;

$cmd = qq{dgmgrl -echo sys/$syspwd "edit database $db_unique_name set state=\\\"APPLY-OFF\\\";"};

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("",<CMD>));

close (CMD);

my $a=$?;

#if ( $? != 0 ) {

# &DoMsg ("Error in stopping apply on standby $BaseDB. Exiting.");

# exit 1

$cmd = $CloneDIR."/bin/snap_acfs.pl -p $BaseDB -n $SnapshotName";

&DoMsg($cmd);

open( CMD, $cmd . " |");

print (join("", <CMD>)); ## only print here as it logs and echoes its time as well

close CMD;

#if ( $? != 0 ) {

# # track if error in creating the snapshot: we continue and do the apply-on anyway!

# $SnapError=1;

$SnapDir = $baseACFS . "/.ACFS/snaps/" . $SnapshotName;

$ControlfileTrace = $SnapDir . "/" . $ControlfileTrace;

$InitName = $SnapDir . "/" . $InitName;

unless ($dbh->do(qq{ ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '$ControlfileTrace' REUSE RESETLOGS}) ) {

&DoMsg ("Error in taking the controlfile trace $ControlfileTrace.");

$warnings++;

}

unless ($dbh->do(qq{ CREATE PFILE='$InitName' FROM SPFILE }) ) {

&DoMsg ("Error in creating the pfile $InitName.");

$warnings++;

}

$cmd = qq{dgmgrl -echo sys/$syspwd "edit database $db_unique_name set state=\"APPLY-ON\""};

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

#if ( $? != 0 ) {

# &DoMsg ("Error in starting apply on standby $BaseDB. MANUAL INTERVENTION REQUIRED");

# exit 1

if ( $SnapError == 1 ) {

&DoMsg ("There was an error in creating the snapshot. Exiting.");

exit 1;

}

if ( $warnings != 0 ) {

&DoMsg("There have been some warnings, but the procedure completed.");

} else {

&DoMsg("The procedure completed successfully.");

}

&DisconnectDB ;

#-------------------------------------------------------------------------------

# DoMsg

# PURPOSE : echo with timestamp YYYY-MM-DD_H24:MI:SS

# PARAMS : $*: the messages

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub DoMsg {

my $msg = shift;

my $timestamp = &getTimestamp;

print ("$timestamp $msg\n");

if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}

}

#-------------------------------------------------------------------------------

# getTimestamp

# PURPOSE : returns timestamp in different formats

# PARAMS : format_parm

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getTimestamp {

# Format 1: dd-mm-yyyy_hh24:mi:ss

# Format 2: dd.mm.yyyy_hh24miss

# Format 3: dd.mm.yyyy

# Format 4: hh24:mi:ss

# Rest: dd.mm.yyyy hh24:mi:ss (default)

my $Parm = shift;

my $date;

my $date2;

my $heure;

my $heure2;

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

if ( length($Parm) > 1 ) {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);

}

else {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;

}

$date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);

$date =~ s/ /0/g;

$date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);

$date2 =~ s/ /0/g;

$heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));

$heure =~ s/ /0/g;

$heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));

$heure2 =~ s/ /0/g;

if ($Parm eq "1") { return ($date2."_".$heure) }

elsif ($Parm eq "2") { return ($date."_".$heure2) }

elsif ($Parm eq "3") { return ($date) }

elsif ($Parm eq "4") { return ($heure) }

else { return ($date." ".$heure) };

}

#-------------------------------------------------------------------------------

# getWeekDay

# PURPOSE : returns weekday (Sun - Sat)

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getWeekDay{

my @date = split(" ", localtime(time));

my $day = $date[0];

return ($day);

}

#-------------------------------------------------------------------------------

# Usage

# PURPOSE : print the Usage

# PARAMS : none

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub Usage {

print <<EOF

Usage: $basename -b <base> [Optional Arguments]

-b <base> : name of the base database

Purpose:

Create a new snapshot of a standby database by apply-off, acfs snap, backup controlfile to trace, copy init, apply-on.

Optional Arguments:

-n <prefix_name> : prefix of the new snapshot name

-s <suffix> : use "weekday" to have the day name as suffix (Sun - Sat)

examples:

snap_database.pl -b stout -n stout.18h -s "weekday"

will clone from /u02/acfs/.ACFS/snaps/stout

to /u02/acfs/.ACFS/snaps/stout.18h.Tue (or whatever the day is)

$basename -b stout -s "weekday"

will clone from /u02/acfs/.ACFS/snaps/stout

to /u02/acfs/.ACFS/snaps/stout_save.Wed (or whatever the day is)

EXISTING SNAPSHOT WILL BE DROPPED!!

EOF

}

sub ConnectDB {

# DB connection #

$ENV{ORACLE_SID}=$BaseDB;

$ENV{ORACLE_HOME}=$ORACLE_HOME;

delete $ENV{TWO_TASK};

&DoMsg ("Connecting to DB $BaseDB");

unless ($dbh = DBI->connect('dbi:Oracle:', "sys", $syspwd, {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA})) {

&DoMsg ("Error connecting to DB: ". $DBI::errstr);

exit(1);

}

#&DoMsg ("Connected to DB $BaseDB");

}

sub QueryOneValue {

my $sth;

my $query = shift;

unless ($sth = $dbh->prepare ($query)) {

&DoMsg ("Error preparing statement $query: ".$dbh->errstr);

}

$sth->execute;

my ($result) = $sth->fetchrow_array;

return $result;

}

sub DisconnectDB {

$dbh->disconnect;

}

clone_from_snap.pl

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl

use File::Copy;
use File::Path qw(mkpath rmtree);
use Net::SMTP;
use Sys::Hostname;
use Getopt::Std 'getopts';
use File::Basename;
use DBI;
use DBD::Oracle qw(:ora_session_modes);

my $CloneDIR;                             # predefine rootDir variable
BEGIN {
  use FindBin qw($Bin);                   # get the current path of script
  use Cwd 'abs_path';
  $CloneDIR    = abs_path("$Bin/..");     # get the absolut rood path to clone directory
}

my $CloneLOGDir = $CloneDIR."/log";       # LOG Directory
my $baseACFS = "/u02/acfs";
my $basename    = basename($0, ".pl");
my $BaseDB;
my $SnapshotName;
my $DestDB;
my $DestPath; # contains the final snapshot destination
my $oraenv = '/usr/local/bin/oraenv';
my $crsctl = '/u01/app/grid/12.2.0.1/bin/crsctl';
my $ORACLE_HOME = '/u01/app/oracle/product/12.2.0.1/dbhome_1';
my %opts;
my $dbh;
my $db_create_file_dest;
my $db_unique_name;
my $cmd;
my $SnapError=0;
my $SnapDir;
my $ControlfileTrace = "control.trc";
my $InitName = "init.ora";
my $warnings = 0;
my $foo;
my $dbUniqueName;

################################################################################
#  Main
################################################################################
my $StartDate = localtime;
&DoMsg ("Start of $basename.pl");
unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {
	&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");
    exit 1;
}

# b: base db
# u: source database db_unique_name. if empty, will try to get it dynamically
# s: snapshot name
# d: destination name

# Process command line arguments
if  ( ! defined @ARGV ) { &Usage; exit 1; } 
getopts('b:s:d:u:', \%opts);

if ($opts{"b"}) {
   $BaseDB = $opts{"b"};
} else {
   &DoMsg ("Base DB not given!");
   &Usage;
   exit 1;
}
if ($opts{"s"}) {
   $SnapshotName = $opts{"s"};
} else {
   &DoMsg ("Snapshot Name not given!");
   &ListSnapshots;
   exit 1;
}
if ($opts{"d"}) {
   $DestDB = $opts{"d"};
} else {
   &DoMsg ("Dest DB not given!");
   &Usage;
   exit 1;
}


if ($opts{"u"}) {
   $dbUniqueName = $opts{"u"};
} else {
   &DoMsg ("db_unique_name not given, try to get it dynamically");
   
   &ConnectDB ;
   $dbUniqueName= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_unique_name'});
   &DisconnectDB ;
}

# show the parameters
&DoMsg ("Base: $BaseDB");
&DoMsg ("SnapshotName: $SnapshotName");
&DoMsg ("Dest: $DestDB");
&DoMsg ("db_unique_name: $dbUniqueName");


# try to get the ORACLE_HOME of the resource
my $cmd = "$crsctl status resource ora.".$DestDB.".db -f";
&DoMsg ($cmd);
open( CMD, $cmd . " |");
my @output = <CMD>;
close CMD;
#if ( $? != 0 ) {
#   &DoMsg ("Destination database does not exist, please configure it with srvctl");
#   exit 1;
#} 
foreach (@output) {
   chomp($_);
   if ($_ =~ /^ORACLE_HOME=/) {
      ($foo, $ORACLE_HOME) = split (/=/);
      $ENV{ORACLE_HOME}=$ORACLE_HOME;
      &DoMsg ("OH = $ORACLE_HOME");
   }
} 

# try to get the status of the resource using srvctl
my $cmd = "$ORACLE_HOME/bin/srvctl status database -d $DestDB";
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("", <CMD>));
close CMD;
#if ( $? != 0 ) {
#   &DoMsg ("Destination database does not exist, please configure it");
#   exit 1;
#} 

# try to stop the dest db (will ignore errors)
my $cmd = "$ORACLE_HOME/bin/srvctl stop database -d $DestDB -o abort -f";
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("", <CMD>));
close CMD;


# drop/recreate the snapshot using snap_acfs.pl
$cmd = "tvd_perl ".$CloneDIR."/bin/snap_acfs.pl -p $SnapshotName -n $DestDB";
&DoMsg($cmd);
open( CMD, $cmd . " |");
print (join("", <CMD>)); ## only print here as it logs and echoes its time as well
close CMD;
#if ( $? != 0 ) {
#   &DoMsg("Error creating the new snapshot for $DestDB. Exiting.");
#   exit(1);
#}

$DestPath = $baseACFS . '/.ACFS/snaps/' . $DestDB;
$ControlfileTrace = $DestPath.'/'.$ControlfileTrace;
$InitName = $DestPath.'/'.$InitName;

&DoMsg("Control file trace: $ControlfileTrace");
&DoMsg("Init file: $InitName");

### remove old archives, redo_logs and control files!
rmtree($baseACFS . '/fra/' . $DestDB , 1, 1 );
mkpath($baseACFS . '/fra/' . $DestDB );

## HERE WE HAVE THE CONTROL AND INIT READY TO BE MODIFIED

open(FILE, "<$ControlfileTrace");
my @ControlLines = <FILE>;
close(FILE);

# sed controlfile
my @NewControlLines;
push(@NewControlLines,"SET ECHO ON;\n");
push(@NewControlLines,"WHENEVER SQLERROR EXIT FAILURE;\n");
push(@NewControlLines,"CREATE SPFILE FROM PFILE='$InitName';\n");

foreach(@ControlLines) {
   # change the snapshot name in the paths
   $_ =~ s/u02\/$BaseDB/u02\/$DestDB/gi;
   # change the db_unique_name in the REDO paths
   $_ =~ s/fra\/$dbUniqueName/fra\/$DestDB/gi;


   # change the dbname in the create controlfile line
   $_ =~ s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE "$DestDB" RESETLOGS NOARCHIVELOG/;
   # everything after and including "recover database" can be skipped
   if ($_ =~ /^RECOVER DATABASE /) {
      last;
   }
   print ($_);
   push(@NewControlLines, $_);
}
push(@NewControlLines,"ALTER DATABASE OPEN RESETLOGS;\n");
push(@NewControlLines,"ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 1G;\n");
push(@NewControlLines,"SELECT status FROM v\$instance;\n");
push(@NewControlLines,"QUIT;\n");

# write the new controlfile:
open(FILE, ">$ControlfileTrace");
print FILE @NewControlLines;
close(FILE);

# delete old controlfile
# no more necessary, deleted above  unlink ($DestPath.'/control01.ctl');

# sed init file
open(FILE, "<$InitName");
my @InitLines = <FILE>;
close(FILE);

@InitLines = grep(!/^$BaseDB/i, @InitLines);
@InitLines = grep(!/^\*\.db_name/, @InitLines);
@InitLines = grep(!/^\*\.db_unique_name/, @InitLines);
@InitLines = grep(!/^\*\.dispatchers/, @InitLines);
@InitLines = grep(!/^\*\.audit_file_dest/, @InitLines);
@InitLines = grep(!/^\*\.fal_server/, @InitLines);
@InitLines = grep(!/^\*\.fal_client/, @InitLines);
@InitLines = grep(!/^\*\.log_archive_config/, @InitLines);
@InitLines = grep(!/^\*\.log_archive_dest/, @InitLines);
@InitLines = grep(!/^\*\.memory_target/, @InitLines);
@InitLines = grep(!/^\*\.sga_target/, @InitLines);
@InitLines = grep(!/^\*\.pga_aggregate_target/, @InitLines);
@InitLines = grep(!/^\*\.service_names/, @InitLines);
@InitLines = grep(!/^\*\.dg_broker_start/, @InitLines);

my @NewInitLines;
foreach(@InitLines ) {
   # change only the snapshot name in the paths
   $_ =~ s/u02\/$BaseDB/u02\/$DestDB/gi;
   $_ =~ s/fra\/$dbUniqueName/fra\/$DestDB/gi;
   print ($_);
   push(@NewInitLines, $_);
}   

push(@NewInitLines, "*.db_name='$DestDB'\n");
push(@NewInitLines, "*.db_unique_name='$DestDB'\n");
push(@NewInitLines, "*.dispatchers='(PROTOCOL=TCP)(SERVICE=${DestDB}XDB)'\n");
push(@NewInitLines, "*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'\n");
push(@NewInitLines, "*.sga_target=1G\n");
push(@NewInitLines, "*.pga_aggregate_target=100M\n");
push(@NewInitLines, "*.service_names='$DestDB'\n");
#push(@NewInitLines, "*.\n");

# write the new init file
open(FILE, ">$InitName");
print FILE @NewInitLines;
close(FILE);

$ENV{ORACLE_SID}=$DestDB;
$cmd = "$ORACLE_HOME/bin/sqlplus / as sysdba \@$ControlfileTrace";
&DoMsg($cmd);
open( CMD, $cmd . " |");
print (join("", <CMD>)); ## only print here as it logs and echoes its time as well
close CMD;
#if ( $? != 0 ) {
#   &DoMsg("Error creating the new snapshot for $DestDB. Exiting.");
#   exit(1);
#}

&DoMsg("New database snapshot $DestDB created successfully!");
&DoMsg("Starting using srvctl:");

my $cmd = "$ORACLE_HOME/bin/srvctl start database -d $DestDB";
&DoMsg ($cmd);
open( CMD, $cmd . " |");
&DoMsg (join("", <CMD>));
close CMD;
#if ( $? != 0 ) {
#   &DoMsg ("Destination database cannot be started using srvctl");
#   exit 1;
#} 

# 

#-------------------------------------------------------------------------------
# DoMsg
#
# PURPOSE    : echo with timestamp YYYY-MM-DD_H24:MI:SS
# PARAMS     : $*: the messages
# GLOBAL VARS: none
#-------------------------------------------------------------------------------   
sub DoMsg {

   my $msg = shift;
   my $timestamp = &getTimestamp;
   
   print ("$timestamp $msg\n");
   if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}
}


#-------------------------------------------------------------------------------
# getTimestamp
#
# PURPOSE    : returns timestamp in different formats
# PARAMS     : format_parm
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getTimestamp {
   #
   # Format 1:  dd-mm-yyyy_hh24:mi:ss
   # Format 2:  dd.mm.yyyy_hh24miss
   # Format 3:  dd.mm.yyyy
   # Format 4:  hh24:mi:ss
   # Rest:      dd.mm.yyyy hh24:mi:ss  (default)
   #
   my $Parm = shift;
   my $date;
   my $date2;
   my $heure;
   my $heure2;
   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

   if ( length($Parm) > 1 ) {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);
   }
   else {
      ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
   }
   
   $date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);
   $date =~ s/ /0/g;
   $date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);
   $date2 =~ s/ /0/g;
   $heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));
   $heure =~ s/ /0/g;
   $heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));
   $heure2 =~ s/ /0/g;
   
   if    ($Parm eq "1") { return ($date2."_".$heure) }
   elsif ($Parm eq "2") { return ($date."_".$heure2) }
   elsif ($Parm eq "3") { return ($date) }
   elsif ($Parm eq "4") { return ($heure) }
   else { return ($date." ".$heure) };

}


#-------------------------------------------------------------------------------
# getWeekDay
#
# PURPOSE    : returns weekday (Sun - Sat)
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub getWeekDay{
   my @date = split(" ", localtime(time));
   my $day = $date[0];
   return ($day);
}


#-------------------------------------------------------------------------------
# callSQLPLUS
#
# PURPOSE    : calls the rman utility
# PARAMS     : rman script name
# GLOBAL VARS: ReturnStatus, LogFile
#-------------------------------------------------------------------------------
#sub callSQLPLUS {
#    my $script = shift;
#	open( SQL, "$ORACLE_HOME/bin/sqlplus /nolog  \@$script |");  
#    &DoMsg (join("", <SQL>));
#    if ( $? != 0 ) { $rc = 1; } # RC if last call create an error
#    close SQL;
#}



#-------------------------------------------------------------------------------
# Usage
#
# PURPOSE    : print the Usage
# PARAMS     : none
# GLOBAL VARS: none
#-------------------------------------------------------------------------------
sub Usage {

   print <<EOF
   
Usage:  $basename -b <base>  [Optional Arguments]
           -b <base>       : db_name of the source database 
           -d <base>       : name of the destination database
           -s <snapshot>   : name of the snapshot to be used

        Purpose:
          Create a new snapshot of a standby database by apply-off, backup controlfile to trace, copy init, acfs snap, apply-on.


        Optional Arguments:
           -u <db_unique_name>   : name of the db_unique_name of the source database. if not specified, it will be taked from the source db, but it must be mounted!
                                   this parameter is used only for pattern replacement inside control file trace and init file.

        examples:
            $basename -b stout -s stout_save.Wed -d poug2648
            will clone stout from snapshot $baseACFS/.ACFS/snaps/stout_save.Wed to poug2648 
      
  THE EXISTING DESTINATION DATABASE SNAPSHOT WILL BE DROPPED!!
EOF

}


sub ConnectDB {

   # DB connection #
   $ENV{ORACLE_HOME}=$ORACLE_HOME;
   $ENV{ORACLE_SID}=$BaseDB;
   delete $ENV{TWO_TASK};

   &DoMsg ("Connecting to DB $BaseDB");
   &DoMsg ("OH: $ORACLE_HOME");
   &DoMsg ("SID: $BaseDB");
   unless ($dbh = DBI->connect('dbi:Oracle:', "sys", "Vagrant1_", {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA}))  {
      &DoMsg ("Error connecting to DB: ". $DBI::errstr);
      exit(1);
   }

   #&DoMsg ("Connected to DB $BaseDB");

}

sub QueryOneValue {

   my $sth;
   my $query = shift;

   unless ($sth = $dbh->prepare ($query)) {
      &DoMsg ("Error preparing statement $query: ".$dbh->errstr);
   }
   $sth->execute;
   my ($result) = $sth->fetchrow_array;

   return $result;
}

sub DisconnectDB {
   $dbh->disconnect;
}

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

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

#!/u01/app/oracle/tvdtoolbox/tvdperl-Linux-x86-64-02.04.00-05.08.04/bin/tvd_perl

use File::Copy;

use File::Path qw(mkpath rmtree);

use Net::SMTP;

use Sys::Hostname;

use Getopt::Std 'getopts';

use File::Basename;

use DBI;

use DBD::Oracle qw(:ora_session_modes);

my $CloneDIR; # predefine rootDir variable

BEGIN {

use FindBin qw($Bin); # get the current path of script

use Cwd 'abs_path';

$CloneDIR = abs_path("$Bin/.."); # get the absolut rood path to clone directory

}

my $CloneLOGDir = $CloneDIR."/log"; # LOG Directory

my $baseACFS = "/u02/acfs";

my $basename = basename($0, ".pl");

my $BaseDB;

my $SnapshotName;

my $DestDB;

my $DestPath; # contains the final snapshot destination

my $oraenv = '/usr/local/bin/oraenv';

my $crsctl = '/u01/app/grid/12.2.0.1/bin/crsctl';

my $ORACLE_HOME = '/u01/app/oracle/product/12.2.0.1/dbhome_1';

my %opts;

my $dbh;

my $db_create_file_dest;

my $db_unique_name;

my $cmd;

my $SnapError=0;

my $SnapDir;

my $ControlfileTrace = "control.trc";

my $InitName = "init.ora";

my $warnings = 0;

my $foo;

my $dbUniqueName;

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

# Main

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

my $StartDate = localtime;

&DoMsg ("Start of $basename.pl");

unless ( open (MAINLOG, ">>$CloneLOGDir/$basename.log") ) {

&DoMsg ("Can't open Main Logfile $CloneLOGDir/$basename.log");

exit 1;

}

# b: base db

# u: source database db_unique_name. if empty, will try to get it dynamically

# s: snapshot name

# d: destination name

# Process command line arguments

if ( ! defined @ARGV ) { &Usage; exit 1; }

getopts('b:s:d:u:', \%opts);

if ($opts{"b"}) {

$BaseDB = $opts{"b"};

} else {

&DoMsg ("Base DB not given!");

&Usage;

exit 1;

}

if ($opts{"s"}) {

$SnapshotName = $opts{"s"};

} else {

&DoMsg ("Snapshot Name not given!");

&ListSnapshots;

exit 1;

}

if ($opts{"d"}) {

$DestDB = $opts{"d"};

} else {

&DoMsg ("Dest DB not given!");

&Usage;

exit 1;

}

if ($opts{"u"}) {

$dbUniqueName = $opts{"u"};

} else {

&DoMsg ("db_unique_name not given, try to get it dynamically");

&ConnectDB ;

$dbUniqueName= &QueryOneValue(qq{SELECT value FROM v\$parameter2 WHERE name='db_unique_name'});

&DisconnectDB ;

}

# show the parameters

&DoMsg ("Base: $BaseDB");

&DoMsg ("SnapshotName: $SnapshotName");

&DoMsg ("Dest: $DestDB");

&DoMsg ("db_unique_name: $dbUniqueName");

# try to get the ORACLE_HOME of the resource

my $cmd = "$crsctl status resource ora.".$DestDB.".db -f";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

my @output = <CMD>;

close CMD;

#if ( $? != 0 ) {

# &DoMsg ("Destination database does not exist, please configure it with srvctl");

# exit 1;

foreach (@output) {

chomp($_);

if ($_ =~ /^ORACLE_HOME=/) {

($foo, $ORACLE_HOME) = split (/=/);

$ENV{ORACLE_HOME}=$ORACLE_HOME;

&DoMsg ("OH = $ORACLE_HOME");

}

# try to get the status of the resource using srvctl

my $cmd = "$ORACLE_HOME/bin/srvctl status database -d $DestDB";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

#if ( $? != 0 ) {

# &DoMsg ("Destination database does not exist, please configure it");

# exit 1;

# try to stop the dest db (will ignore errors)

my $cmd = "$ORACLE_HOME/bin/srvctl stop database -d $DestDB -o abort -f";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

# drop/recreate the snapshot using snap_acfs.pl

$cmd = "tvd_perl ".$CloneDIR."/bin/snap_acfs.pl -p $SnapshotName -n $DestDB";

&DoMsg($cmd);

open( CMD, $cmd . " |");

print (join("", <CMD>)); ## only print here as it logs and echoes its time as well

close CMD;

#if ( $? != 0 ) {

# &DoMsg("Error creating the new snapshot for $DestDB. Exiting.");

# exit(1);

$DestPath = $baseACFS . '/.ACFS/snaps/' . $DestDB;

$ControlfileTrace = $DestPath.'/'.$ControlfileTrace;

$InitName = $DestPath.'/'.$InitName;

&DoMsg("Control file trace: $ControlfileTrace");

&DoMsg("Init file: $InitName");

### remove old archives, redo_logs and control files!

rmtree($baseACFS . '/fra/' . $DestDB , 1, 1 );

mkpath($baseACFS . '/fra/' . $DestDB );

## HERE WE HAVE THE CONTROL AND INIT READY TO BE MODIFIED

open(FILE, "<$ControlfileTrace");

my @ControlLines = <FILE>;

close(FILE);

# sed controlfile

my @NewControlLines;

push(@NewControlLines,"SET ECHO ON;\n");

push(@NewControlLines,"WHENEVER SQLERROR EXIT FAILURE;\n");

push(@NewControlLines,"CREATE SPFILE FROM PFILE='$InitName';\n");

foreach(@ControlLines) {

# change the snapshot name in the paths

$_ =~ s/u02\/$BaseDB/u02\/$DestDB/gi;

# change the db_unique_name in the REDO paths

$_ =~ s/fra\/$dbUniqueName/fra\/$DestDB/gi;

# change the dbname in the create controlfile line

$_ =~ s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE "$DestDB" RESETLOGS NOARCHIVELOG/;

# everything after and including "recover database" can be skipped

if ($_ =~ /^RECOVER DATABASE /) {

last;

}

print ($_);

push(@NewControlLines, $_);

}

push(@NewControlLines,"ALTER DATABASE OPEN RESETLOGS;\n");

push(@NewControlLines,"ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 1G;\n");

push(@NewControlLines,"SELECT status FROM v\$instance;\n");

push(@NewControlLines,"QUIT;\n");

# write the new controlfile:

open(FILE, ">$ControlfileTrace");

print FILE @NewControlLines;

close(FILE);

# delete old controlfile

# no more necessary, deleted above unlink ($DestPath.'/control01.ctl');

# sed init file

open(FILE, "<$InitName");

my @InitLines = <FILE>;

close(FILE);

@InitLines = grep(!/^$BaseDB/i, @InitLines);

@InitLines = grep(!/^\*\.db_name/, @InitLines);

@InitLines = grep(!/^\*\.db_unique_name/, @InitLines);

@InitLines = grep(!/^\*\.dispatchers/, @InitLines);

@InitLines = grep(!/^\*\.audit_file_dest/, @InitLines);

@InitLines = grep(!/^\*\.fal_server/, @InitLines);

@InitLines = grep(!/^\*\.fal_client/, @InitLines);

@InitLines = grep(!/^\*\.log_archive_config/, @InitLines);

@InitLines = grep(!/^\*\.log_archive_dest/, @InitLines);

@InitLines = grep(!/^\*\.memory_target/, @InitLines);

@InitLines = grep(!/^\*\.sga_target/, @InitLines);

@InitLines = grep(!/^\*\.pga_aggregate_target/, @InitLines);

@InitLines = grep(!/^\*\.service_names/, @InitLines);

@InitLines = grep(!/^\*\.dg_broker_start/, @InitLines);

my @NewInitLines;

foreach(@InitLines ) {

# change only the snapshot name in the paths

$_ =~ s/u02\/$BaseDB/u02\/$DestDB/gi;

$_ =~ s/fra\/$dbUniqueName/fra\/$DestDB/gi;

print ($_);

push(@NewInitLines, $_);

}

push(@NewInitLines, "*.db_name='$DestDB'\n");

push(@NewInitLines, "*.db_unique_name='$DestDB'\n");

push(@NewInitLines, "*.dispatchers='(PROTOCOL=TCP)(SERVICE=${DestDB}XDB)'\n");

push(@NewInitLines, "*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'\n");

push(@NewInitLines, "*.sga_target=1G\n");

push(@NewInitLines, "*.pga_aggregate_target=100M\n");

push(@NewInitLines, "*.service_names='$DestDB'\n");

#push(@NewInitLines, "*.\n");

# write the new init file

open(FILE, ">$InitName");

print FILE @NewInitLines;

close(FILE);

$ENV{ORACLE_SID}=$DestDB;

$cmd = "$ORACLE_HOME/bin/sqlplus / as sysdba \@$ControlfileTrace";

&DoMsg($cmd);

open( CMD, $cmd . " |");

print (join("", <CMD>)); ## only print here as it logs and echoes its time as well

close CMD;

#if ( $? != 0 ) {

# &DoMsg("Error creating the new snapshot for $DestDB. Exiting.");

# exit(1);

&DoMsg("New database snapshot $DestDB created successfully!");

&DoMsg("Starting using srvctl:");

my $cmd = "$ORACLE_HOME/bin/srvctl start database -d $DestDB";

&DoMsg ($cmd);

open( CMD, $cmd . " |");

&DoMsg (join("", <CMD>));

close CMD;

#if ( $? != 0 ) {

# &DoMsg ("Destination database cannot be started using srvctl");

# exit 1;

#-------------------------------------------------------------------------------

# DoMsg

# PURPOSE : echo with timestamp YYYY-MM-DD_H24:MI:SS

# PARAMS : $*: the messages

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub DoMsg {

my $msg = shift;

my $timestamp = &getTimestamp;

print ("$timestamp $msg\n");

if (fileno(MAINLOG)) {print MAINLOG "$timestamp $msg\n";}

}

#-------------------------------------------------------------------------------

# getTimestamp

# PURPOSE : returns timestamp in different formats

# PARAMS : format_parm

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getTimestamp {

# Format 1: dd-mm-yyyy_hh24:mi:ss

# Format 2: dd.mm.yyyy_hh24miss

# Format 3: dd.mm.yyyy

# Format 4: hh24:mi:ss

# Rest: dd.mm.yyyy hh24:mi:ss (default)

my $Parm = shift;

my $date;

my $date2;

my $heure;

my $heure2;

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

if ( length($Parm) > 1 ) {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($Parm);

}

else {

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;

}

$date = (sprintf "%2.0d",($mday)).".".(sprintf "%2.0d",($mon+1)).".".($year+1900);

$date =~ s/ /0/g;

$date2 = (sprintf "%2.0d",($mday))."-".(sprintf "%2.0d",($mon+1))."-".($year+1900);

$date2 =~ s/ /0/g;

$heure = (sprintf "%2.0d",($hour)).":".(sprintf "%2.0d",($min)).":".(sprintf "%2.0d",($sec));

$heure =~ s/ /0/g;

$heure2 = (sprintf "%2.0d",($hour)).(sprintf "%2.0d",($min)).(sprintf "%2.0d",($sec));

$heure2 =~ s/ /0/g;

if ($Parm eq "1") { return ($date2."_".$heure) }

elsif ($Parm eq "2") { return ($date."_".$heure2) }

elsif ($Parm eq "3") { return ($date) }

elsif ($Parm eq "4") { return ($heure) }

else { return ($date." ".$heure) };

}

#-------------------------------------------------------------------------------

# getWeekDay

# PURPOSE : returns weekday (Sun - Sat)

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub getWeekDay{

my @date = split(" ", localtime(time));

my $day = $date[0];

return ($day);

}

#-------------------------------------------------------------------------------

# callSQLPLUS

# PURPOSE : calls the rman utility

# PARAMS : rman script name

# GLOBAL VARS: ReturnStatus, LogFile

#-------------------------------------------------------------------------------

#sub callSQLPLUS {

# my $script = shift;

# open( SQL, "$ORACLE_HOME/bin/sqlplus /nolog \@$script |");

# &DoMsg (join("", <SQL>));

# if ( $? != 0 ) { $rc = 1; } # RC if last call create an error

# close SQL;

#-------------------------------------------------------------------------------

# Usage

# PURPOSE : print the Usage

# PARAMS : none

# GLOBAL VARS: none

#-------------------------------------------------------------------------------

sub Usage {

print <<EOF

Usage: $basename -b <base> [Optional Arguments]

-b <base> : db_name of the source database

-d <base> : name of the destination database

-s <snapshot> : name of the snapshot to be used

Purpose:

Create a new snapshot of a standby database by apply-off, backup controlfile to trace, copy init, acfs snap, apply-on.

Optional Arguments:

-u <db_unique_name> : name of the db_unique_name of the source database. if not specified, it will be taked from the source db, but it must be mounted!

this parameter is used only for pattern replacement inside control file trace and init file.

examples:

$basename -b stout -s stout_save.Wed -d poug2648

will clone stout from snapshot $baseACFS/.ACFS/snaps/stout_save.Wed to poug2648

THE EXISTING DESTINATION DATABASE SNAPSHOT WILL BE DROPPED!!

EOF

}

sub ConnectDB {

# DB connection #

$ENV{ORACLE_HOME}=$ORACLE_HOME;

$ENV{ORACLE_SID}=$BaseDB;

delete $ENV{TWO_TASK};

&DoMsg ("Connecting to DB $BaseDB");

&DoMsg ("OH: $ORACLE_HOME");

&DoMsg ("SID: $BaseDB");

unless ($dbh = DBI->connect('dbi:Oracle:', "sys", "Vagrant1_", {PrintError=>0, AutoCommit => 0, ora_session_mode => ORA_SYSDBA})) {

&DoMsg ("Error connecting to DB: ". $DBI::errstr);

exit(1);

}

#&DoMsg ("Connected to DB $BaseDB");

}

sub QueryOneValue {

my $sth;

my $query = shift;

unless ($sth = $dbh->prepare ($query)) {

&DoMsg ("Error preparing statement $query: ".$dbh->errstr);

}

$sth->execute;

my ($result) = $sth->fetchrow_array;

return $result;

}

sub DisconnectDB {

$dbh->disconnect;

}

Cheers

—

Ludovico

12.1.0.2 Bundle Patch 170718 breaks Data Guard and Duplicate from active database

Posted on September 14, 2017 by Ludovico

Recently my customer patched its 12.1.0.2 databases with the Bundle Patch 170718 on the new servers (half of the customer’s environment). The old servers are still on 161018 Bundle Patch.

We realized that we could not move anymore the databases from the old servers to the new ones because the duplicate from active database was failing with this error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/11/2017 15:59:32
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 09/11/2017 15:59:32
ORA-17629: Cannot connect to the remote database server
ORA-17630: Mismatch in the remote file protocol version client 2 server 3

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 09/11/2017 15:59:32

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on prmy1 channel at 09/11/2017 15:59:32

ORA-17629: Cannot connect to the remote database server

ORA-17630: Mismatch in the remote file protocol version client 2 server 3

The last lines shows the same error that Franck blogged about some months ago.

Oracle 12.2 had introduced incompatibility with previous releases in remote file transfer via SQL*Net. At least this is what it seems. According to Oracle, this is due to a bugfix present in Oracle 12.2

Now, the bundle patch that we installed on BP 170718 contains the same bugfix (Patch for bug 18633374).

So, the incompatibility happens now between databases of the same “Major Release” (12.1.0.2).

There are two possible workarounds:

Apply the same patch level on both sides (BP170718 in my case)
Apply just the patch 18633374 on top of your current PSU/DBBP (a merge might be necessary).

We used the second approach and now we can setup Data Guard again to move our databases without downtime:

oracle@oldserver $ opatch lspatches
18633374;   <<<<<< FIX!
24340679;DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)

oracle@newserver $ opatch lspatches
22652097;
22243983;
25869760;DATABASE BUNDLE PATCH: 12.1.0.2.170718 (25869760)

oracle@oldserver $ opatch lspatches

18633374; <<<<<< FIX!

24340679;DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)

oracle@newserver $ opatch lspatches

22652097;

22243983;

25869760;DATABASE BUNDLE PATCH: 12.1.0.2.170718 (25869760)

HTH

—

Ludovico

Which Oracle Databases use most CPU on my server?

Posted on May 24, 2017 by Ludovico

Assumptions

You have many (hundreds) of instances and more than a couple of servers
One of your servers have high CPU Load
You have Enterprise Manager 12c but the Database Load does not filter by server
You want to have an historical representation of the user CPU utilization, per instance

Getting the data from the EM Repository

With the following query, connected to the SYSMAN schema of your EM repository, you can get the hourly max() and/or avg() of user CPU by instance and time.

SELECT entity_name,
  ROUND(collection_time,'HH') AS colltime,
  ROUND(avg_value,2)/16*100   AS avgv, -- 16 is my number of CPU
  ROUND(max_value,2)/16*100   AS maxv  -- same here
FROM gc$metric_values_hourly mv
JOIN em_targets t
ON (t.target_name         =mv.entity_name)
WHERE t.host_name         ='myserver1'  -- myserver1 is the server that has high CPU Usage
AND mv.metric_column_name = 'user_cpu_time_cnt' -- let's get the user cpu time
AND collection_time>sysdate-14  -- for the lase 14 days
ORDER BY entity_name,
  ROUND(collection_time,'HH');

SELECT entity_name,

ROUND(collection_time,'HH') AS colltime,

ROUND(avg_value,2)/16*100 AS avgv, -- 16 is my number of CPU

ROUND(max_value,2)/16*100 AS maxv -- same here

FROM gc$metric_values_hourly mv

JOIN em_targets t

ON (t.target_name =mv.entity_name)

WHERE t.host_name ='myserver1' -- myserver1 is the server that has high CPU Usage

AND mv.metric_column_name = 'user_cpu_time_cnt' -- let's get the user cpu time

AND collection_time>sysdate-14 -- for the lase 14 days

ORDER BY entity_name,

ROUND(collection_time,'HH');

Suppose you select just the max value: the result will be similar to this:

ENTITY_ COLLTIME          MAXV
------- ----------------  ------
mydbone	10.05.2017 16:00  0.3125
mydbone	10.05.2017 17:00  0.1875
mydbone	10.05.2017 18:00  0.1875
mydbone	10.05.2017 19:00  0.1875
mydbone	10.05.2017 20:00  0.25
mydbone	10.05.2017 21:00  0.125
mydbone	10.05.2017 22:00  0.125
mydbone	10.05.2017 23:00  0.125
mydbone	11.05.2017 00:00  0.1875
mydbone	11.05.2017 01:00  0.125
mydbone	11.05.2017 02:00  0.1875
mydbone	11.05.2017 03:00  0.1875
....                      
mydbone	23.05.2017 20:00  0.125
mydbone	23.05.2017 21:00  0.125
mydbone	23.05.2017 22:00  0.125
mydbone	23.05.2017 23:00  0.0625
mydbtwo	10.05.2017 16:00  0.3125
mydbtwo	10.05.2017 17:00  0.25
mydbtwo	10.05.2017 18:00  0.1875
mydbtwo	10.05.2017 19:00  0.1875
mydbtwo	10.05.2017 20:00  0.3125
mydbtwo	10.05.2017 21:00  0.125
mydbtwo	10.05.2017 22:00  0.125
mydbtwo	10.05.2017 23:00  0.125
.....                     
mydbtwo	14.05.2017 19:00  0.125
mydbtwo	14.05.2017 20:00  0.125
mydbtwo	14.05.2017 21:00  0.125
mydbtwo	14.05.2017 22:00  0.125
mydbtwo	14.05.2017 23:00  0.125
dbthree	10.05.2017 16:00  1.1875
dbthree	10.05.2017 17:00  0.6875
dbthree	10.05.2017 18:00  0.625
dbthree	10.05.2017 19:00  0.5625
dbthree	10.05.2017 20:00  0.8125
dbthree	10.05.2017 21:00  0.5
dbthree	10.05.2017 22:00  0.4375
dbthree	10.05.2017 23:00  0.4375
...

ENTITY_ COLLTIME MAXV

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

mydbone 10.05.2017 16:00 0.3125

mydbone 10.05.2017 17:00 0.1875

mydbone 10.05.2017 18:00 0.1875

mydbone 10.05.2017 19:00 0.1875

mydbone 10.05.2017 20:00 0.25

mydbone 10.05.2017 21:00 0.125

mydbone 10.05.2017 22:00 0.125

mydbone 10.05.2017 23:00 0.125

mydbone 11.05.2017 00:00 0.1875

mydbone 11.05.2017 01:00 0.125

mydbone 11.05.2017 02:00 0.1875

mydbone 11.05.2017 03:00 0.1875

....

mydbone 23.05.2017 20:00 0.125

mydbone 23.05.2017 21:00 0.125

mydbone 23.05.2017 22:00 0.125

mydbone 23.05.2017 23:00 0.0625

mydbtwo 10.05.2017 16:00 0.3125

mydbtwo 10.05.2017 17:00 0.25

mydbtwo 10.05.2017 18:00 0.1875

mydbtwo 10.05.2017 19:00 0.1875

mydbtwo 10.05.2017 20:00 0.3125

mydbtwo 10.05.2017 21:00 0.125

mydbtwo 10.05.2017 22:00 0.125

mydbtwo 10.05.2017 23:00 0.125

.....

mydbtwo 14.05.2017 19:00 0.125

mydbtwo 14.05.2017 20:00 0.125

mydbtwo 14.05.2017 21:00 0.125

mydbtwo 14.05.2017 22:00 0.125

mydbtwo 14.05.2017 23:00 0.125

dbthree 10.05.2017 16:00 1.1875

dbthree 10.05.2017 17:00 0.6875

dbthree 10.05.2017 18:00 0.625

dbthree 10.05.2017 19:00 0.5625

dbthree 10.05.2017 20:00 0.8125

dbthree 10.05.2017 21:00 0.5

dbthree 10.05.2017 22:00 0.4375

dbthree 10.05.2017 23:00 0.4375

...

Putting it into excel

There are one million ways to do something more reusable than excel (like rrdtool scripts, gnuplot, R, name it), but Excel is just right for most people out there (including me when I feel lazy).

Configure an Oracle Client and add the ODBC data source to the EM repository:

Open Excel, go to “Data” – “Connections” and add a new connection:
- Search…
- New Source
- DSN ODBC
Select your new ODBC data source, user, password
Uncheck “Connection to a specific table”
Give a name and click Finish
On the DSN -> Properties -> Definition, enter the SQL text I have provided previously

The result should be something similar: ( but much longer :-))

Pivoting the results

Create e new sheet and name it “pivot”, Click on “Create Pivot Table”, select your data and your dimensions:

The result:

Creating the Graph

Now that the data is correctly formatted, it’s easyy to add a graph:

just select the entire pivot table and create a new stacked area graph.

The result will be similar to this:

With such graph, it is easy to spot which databases consumed most CPU on the system in a defined period, and to track the progress if you start a “performance campaign”.

For example, you can see that the “green” and “red” databases were consuming constantly some CPU up to 17.05.2017 and then some magic solved the CPU problem for those instances.

It is also quite convenient for checking the results of new instance caging settings…

The resulting CPU will not necessarily be 100%: the SYS CPU time is not included, as well as the user CPU of all the other processes that are either not DB or not monitored with Enterprise Manager.

HTH

—

Ludovico

Another problem with “KSV master wait” and “ASM file metadata operation”

Posted on March 24, 2017 by Ludovico

My customer today tried to do a duplicate on a cluster. When preparing the auxiliary instance, she noticed that the startup nomount was hanging forever: Nothing in the alert, nothing in the trace files.

Because the database and the spfile were stored inside ASM, I’ve been quite suspicious…

The ASM trace files had the following entries:

kfgbDiscoverNow: called for group 1/0x9f5bfe53 (ACFS)

*** 2017-03-24 12:42:13.327
2017-03-24 12:42:13.327: [    GPNP]clsgpnp_dbmsGetItem_profile: [at clsgpnp_dbms.c:345] Result: (0) CLSGPNP_OK. (:GPNP00401:)got ASM-Profile.DiscoveryString='/dev/mapper/asm_*,/dev/asm_*'

*** 2017-03-24 12:42:15.386
kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:18.387
kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:21.393
kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:24.398
kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:27.403
kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

kfgbDiscoverNow: called for group 1/0x9f5bfe53 (ACFS)

*** 2017-03-24 12:42:13.327

2017-03-24 12:42:13.327: [ GPNP]clsgpnp_dbmsGetItem_profile: [at clsgpnp_dbms.c:345] Result: (0) CLSGPNP_OK. (:GPNP00401:)got ASM-Profile.DiscoveryString='/dev/mapper/asm_*,/dev/asm_*'

*** 2017-03-24 12:42:15.386

kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:18.387

kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:21.393

kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:24.398

kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

*** 2017-03-24 12:42:27.403

kfgbTryFn: failed to acquire DG.1.3 for kfgbRefreshNow (of group 1/0x9f5bfe53)

The ASM instance had the following sessions waiting:

SQL>  select inst_id, sid, serial#, status, event, wait_class, wait_time, logon_time , program, machine from gv$session where wait_class!='Idle' order by sid;

INST_ID  SID SERIAL# STATUS  EVENT                        WAIT_CLASS WAIT_TIME LOGON_TIME          PROGRAM                             MACHINE
------- ---- ------- ------- ---------------------------- ---------- --------- ------------------- ----------------------------------- --------
      2   36   41916 ACTIVE  ASM file metadata operation  Other              0 24.03.2017 13:47:28 oracle@clusrv02 (O001)              clusrv02
      2  266   64885 ACTIVE  KSV master wait              Other              0 24.03.2017 13:47:25 oracletorcl01v@clusrv02 (TNS V1-V3) clusrv02
      1  483   63446 ACTIVE  KSV master wait              Other              0 24.03.2017 13:31:14 oracletorcl01v@clusrv01 (TNS V1-V3) clusrv01
      1  497   31202 ACTIVE  ASM file metadata operation  Other              0 24.03.2017 13:39:07 oracletorcl01v@clusrv01 (TNS V1-V3) clusrv01
      3  708     484 ACTIVE  ASM file metadata operation  Other              0 24.03.2017 12:38:56 OMS                                 omssrv01

SQL> select inst_id, sid, serial#, status, event, wait_class, wait_time, logon_time , program, machine from gv$session where wait_class!='Idle' order by sid;

INST_ID SID SERIAL# STATUS EVENT WAIT_CLASS WAIT_TIME LOGON_TIME PROGRAM MACHINE

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

2 36 41916 ACTIVE ASM file metadata operation Other 0 24.03.2017 13:47:28 oracle@clusrv02 (O001) clusrv02

2 266 64885 ACTIVE KSV master wait Other 0 24.03.2017 13:47:25 oracletorcl01v@clusrv02 (TNS V1-V3) clusrv02

1 483 63446 ACTIVE KSV master wait Other 0 24.03.2017 13:31:14 oracletorcl01v@clusrv01 (TNS V1-V3) clusrv01

1 497 31202 ACTIVE ASM file metadata operation Other 0 24.03.2017 13:39:07 oracletorcl01v@clusrv01 (TNS V1-V3) clusrv01

3 708 484 ACTIVE ASM file metadata operation Other 0 24.03.2017 12:38:56 OMS omssrv01

OMS?

Around 12:38:56, another colleague in the office added a disk to one of the disk groups, through Enterprise Manager 12c!

But there were no rebalance operations:

SQL> select * from gv$asm_operation;

no rows selected

SQL> select * from gv$asm_operation;

no rows selected

It’s not the first time that I hit this type of problems. Sadly, sometimes it requires a full restart of the cluster or of ASM (because of different bugs).

This time, however, I have tried to kill only the foreground sessions waiting on “ASM file metadata operation”, starting with the one coming from the OMS.

Surprisingly, after killing that session, everything was fine again:

-- on +ASM3
SQL> alter system kill session '708,484';

System altered.

SQL>

SQL>  select inst_id, sid, serial#, status, event, wait_class, wait_time, logon_time , program, machine from gv$session where wait_class!='Idle' order by sid;

no rows selected

SQL>

-- on +ASM3

SQL> alter system kill session '708,484';

System altered.

SQL>

SQL> select inst_id, sid, serial#, status, event, wait_class, wait_time, logon_time , program, machine from gv$session where wait_class!='Idle' order by sid;

no rows selected

SQL>

I never add disks via OMS (I’m a sqlplus guy ;-)) , I wonder what went wrong with it 🙂

—

Ludovico

RMAN Catalog Housekeeping: how to purge the old incarnations

Posted on February 21, 2017 by Ludovico

First, let me apologize because every post in my blog starts with a disclaimer… but sometimes it is really necessary. 😉

Disclaimer: this blog post contains PL/SQL code that deletes incarnations from your RMAN recovery catalog. Please DON’T use it unless you deeply understand what you are doing, as it can compromise your backup and recovery strategy.

Small introduction

You may have a central RMAN catalog that stores all the backup metadata for your databases. If it is the case, you will have a database entry for each of your databases and a new incarnation entry for each duplicate, incomplete recovery or flashback (or whatever).

You should also have a delete strategy that deletes the obsolete backups from either your DISK or SBT_TAPE media. If you have old incarnations, however, after some time you will notice that their information never goes away from your catalog, and you may end up soon or later to do some housekeeping. But there is nothing more tedious than checking and deleting the incarnations one by one, especially if you have average big numbers like this catalog:

SQL> SELECT count(*) FROM db;

  COUNT(*)
----------
      1843

SQL> SELECT count(*) FROM dbinc;

  COUNT(*)
----------
      3870

SQL> SELECT count(*) FROM bdf;

  COUNT(*)
----------
   4130959

SQL> SELECT count(*) FROM brl;


  COUNT(*)
----------
  14876291

SQL> SELECT count(*) FROM db;

COUNT(*)

----------

1843

SQL> SELECT count(*) FROM dbinc;

COUNT(*)

----------

3870

SQL> SELECT count(*) FROM bdf;

COUNT(*)

----------

4130959

SQL> SELECT count(*) FROM brl;

COUNT(*)

----------

14876291

Where db, dbinc, bdf and brl contain reslectively the registered databases, incarnations, datafile backups and archivelog backups.

Different incarnations?

Consider the following query:

col dbinc_key_ for a60
set pages 100 lines 200
SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY_,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY
ORDER BY db_name , db_key, level
;

col dbinc_key_ for a60

set pages 100 lines 200

SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY_,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY

ORDER BY db_name , db_key, level

;

You can run it safely: it returns the list of incarnations hierarchically connected to their parent, by database name, key and level.

Then you have several types of behaviors:

Normal databases (created once, never restored or flashed back) will have just one or two incarnations (it depends on how they are created):

DBINC_KEY                      DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
-------------------------- ---------- -------- ------------------- --------
104547357                   104546534 VxxxxxxP 2010-09-05 05:49:10 PARENT
  104546535                 104546534 VxxxxxxP 2012-01-18 09:31:01 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

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

104547357 104546534 VxxxxxxP 2010-09-05 05:49:10 PARENT

104546535 104546534 VxxxxxxP 2012-01-18 09:31:01 CURRENT

They are usually the ones that you may want to keep in your catalog, unless the database no longer exist: in this case perhaps you omitted the deletion from the catalog when you have dropped your database?

Flashed back databases (flashed back multiple times) will have as many incarnations as the number of flashbacks, but all connected with the incarnation prior to the flashback:

DBINC_KEY                                                        DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
------------------------------------------------------------ ---------- -------- ------------------- --------
1164696351                                                   1164696336 VxxxxxxD 2014-07-07 05:38:47 PARENT
  1164696337                                                 1164696336 VxxxxxxD 2014-12-10 07:39:14 PARENT
    1328815631                                               1164696336 VxxxxxxD 2016-05-12 08:22:23 PARENT
      1329299866                                             1164696336 VxxxxxxD 2016-05-13 13:02:35 PARENT
        1329299867                                           1164696336 VxxxxxxD 2016-05-13 14:05:53 PARENT
          1329299833                                         1164696336 VxxxxxxD 2016-05-13 18:26:59 PARENT
            1331239226                                       1164696336 VxxxxxxD 2016-05-17 08:09:04 PARENT
              1331395102                                     1164696336 VxxxxxxD 2016-05-17 13:20:17 PARENT
                1331815030                                   1164696336 VxxxxxxD 2016-05-18 07:32:13 PARENT
                  1331814966                                 1164696336 VxxxxxxD 2016-05-18 10:57:45 PARENT
                    1387023006                               1164696336 VxxxxxxD 2016-07-13 09:33:05 PARENT
                      1407484366                             1164696336 VxxxxxxD 2016-09-09 13:25:31 PARENT
                        1419007163                           1164696336 VxxxxxxD 2016-10-17 14:32:59 PARENT
                          1436430179                         1164696336 VxxxxxxD 2016-12-12 15:13:55 PARENT
                            1436430034                       1164696336 VxxxxxxD 2016-12-12 16:28:57 PARENT
                              1437118959                     1164696336 VxxxxxxD 2016-12-14 14:48:53 PARENT
                                1437365509                   1164696336 VxxxxxxD 2016-12-15 09:45:00 PARENT
                                  1437365456                 1164696336 VxxxxxxD 2016-12-15 11:11:06 PARENT
                                    1437484026               1164696336 VxxxxxxD 2016-12-15 13:26:37 PARENT
                                      1437483983             1164696336 VxxxxxxD 2016-12-15 17:21:11 PARENT
                                        1437822754           1164696336 VxxxxxxD 2016-12-16 12:07:46 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

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

1164696351 1164696336 VxxxxxxD 2014-07-07 05:38:47 PARENT

1164696337 1164696336 VxxxxxxD 2014-12-10 07:39:14 PARENT

1328815631 1164696336 VxxxxxxD 2016-05-12 08:22:23 PARENT

1329299866 1164696336 VxxxxxxD 2016-05-13 13:02:35 PARENT

1329299867 1164696336 VxxxxxxD 2016-05-13 14:05:53 PARENT

1329299833 1164696336 VxxxxxxD 2016-05-13 18:26:59 PARENT

1331239226 1164696336 VxxxxxxD 2016-05-17 08:09:04 PARENT

1331395102 1164696336 VxxxxxxD 2016-05-17 13:20:17 PARENT

1331815030 1164696336 VxxxxxxD 2016-05-18 07:32:13 PARENT

1331814966 1164696336 VxxxxxxD 2016-05-18 10:57:45 PARENT

1387023006 1164696336 VxxxxxxD 2016-07-13 09:33:05 PARENT

1407484366 1164696336 VxxxxxxD 2016-09-09 13:25:31 PARENT

1419007163 1164696336 VxxxxxxD 2016-10-17 14:32:59 PARENT

1436430179 1164696336 VxxxxxxD 2016-12-12 15:13:55 PARENT

1436430034 1164696336 VxxxxxxD 2016-12-12 16:28:57 PARENT

1437118959 1164696336 VxxxxxxD 2016-12-14 14:48:53 PARENT

1437365509 1164696336 VxxxxxxD 2016-12-15 09:45:00 PARENT

1437365456 1164696336 VxxxxxxD 2016-12-15 11:11:06 PARENT

1437484026 1164696336 VxxxxxxD 2016-12-15 13:26:37 PARENT

1437483983 1164696336 VxxxxxxD 2016-12-15 17:21:11 PARENT

1437822754 1164696336 VxxxxxxD 2016-12-16 12:07:46 CURRENT

Here, despite you have several incarnations, they all belong to the same database (same DB_KEY and DBID), then you must also keep it inside the recovery catalog.

Non-production databases that are frequently refreshed from the production database (via duplicate) will have several incarnations with different DBIDs and DB_KEY:

DBINC_KEY                   DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
----------------------- ---------- -------- ------------------- --------
1173852671              1173852633 VxxxxxxV 2014-07-07 05:38:47 PARENT
  1173852635            1173852633 VxxxxxxV 2015-01-16 07:29:01 PARENT
    1188550385          1173852633 VxxxxxxV 2015-03-16 16:06:00 CURRENT
1220896058              1220896027 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1220896028            1220896027 VxxxxxxV 2015-07-17 08:06:00 CURRENT
1233975755              1233975724 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1233975725            1233975724 VxxxxxxV 2015-09-10 11:23:53 CURRENT
1244346785              1244346754 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1244346755            1244346754 VxxxxxxV 2015-10-23 07:46:34 CURRENT
1281775847              1281775816 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1281775817            1281775816 VxxxxxxV 2016-02-08 09:44:03 CURRENT
1317447322              1317447257 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1317447258            1317447257 VxxxxxxV 2016-04-07 12:20:56 CURRENT
1323527351              1323527316 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1323527317            1323527316 VxxxxxxV 2016-04-29 10:09:12 CURRENT
1437346753              1437346718 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1437346719            1437346718 VxxxxxxV 2016-12-12 13:33:31 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

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

1173852671 1173852633 VxxxxxxV 2014-07-07 05:38:47 PARENT

1173852635 1173852633 VxxxxxxV 2015-01-16 07:29:01 PARENT

1188550385 1173852633 VxxxxxxV 2015-03-16 16:06:00 CURRENT

1220896058 1220896027 VxxxxxxV 2015-02-27 16:25:13 PARENT

1220896028 1220896027 VxxxxxxV 2015-07-17 08:06:00 CURRENT

1233975755 1233975724 VxxxxxxV 2015-02-27 16:25:13 PARENT

1233975725 1233975724 VxxxxxxV 2015-09-10 11:23:53 CURRENT

1244346785 1244346754 VxxxxxxV 2015-02-27 16:25:13 PARENT

1244346755 1244346754 VxxxxxxV 2015-10-23 07:46:34 CURRENT

1281775847 1281775816 VxxxxxxV 2015-02-27 16:25:13 PARENT

1281775817 1281775816 VxxxxxxV 2016-02-08 09:44:03 CURRENT

1317447322 1317447257 VxxxxxxV 2015-02-27 16:25:13 PARENT

1317447258 1317447257 VxxxxxxV 2016-04-07 12:20:56 CURRENT

1323527351 1323527316 VxxxxxxV 2015-02-27 16:25:13 PARENT

1323527317 1323527316 VxxxxxxV 2016-04-29 10:09:12 CURRENT

1437346753 1437346718 VxxxxxxV 2015-02-27 16:25:13 PARENT

1437346719 1437346718 VxxxxxxV 2016-12-12 13:33:31 CURRENT

This is usually the most frequent case: here you want to delete the old incarnations, but only as far as there are no backups attached to them that are still in the recovery window.

You may also have orphaned incarnations:

DBINC_KEY                                                        DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
------------------------------------------------------------ ---------- -------- ------------------- --------
1262827482                                                   1262827435 TxxxxxxT 2014-07-07 05:38:47 PARENT
  1262827436                                                 1262827435 TxxxxxxT 2016-01-05 12:15:22 PARENT
    1267262014                                               1262827435 TxxxxxxT 2016-01-19 09:15:47 PARENT
      1267290962                                             1262827435 TxxxxxxT 2016-01-19 11:09:05 PARENT
        1284933855                                           1262827435 TxxxxxxT 2016-02-11 11:18:52 PARENT
          1299685647                                         1262827435 TxxxxxxT 2016-02-23 13:40:18 ORPHAN
          1299837528                                         1262827435 TxxxxxxT 2016-02-23 17:08:36 CURRENT
          1299767977                                         1262827435 TxxxxxxT 2016-02-23 15:34:13 ORPHAN
          1298269640                                         1262827435 TxxxxxxT 2016-02-22 13:16:46 ORPHAN
            1299517249                                       1262827435 TxxxxxxT 2016-02-23 10:37:29 ORPHAN

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

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

1262827482 1262827435 TxxxxxxT 2014-07-07 05:38:47 PARENT

1262827436 1262827435 TxxxxxxT 2016-01-05 12:15:22 PARENT

1267262014 1262827435 TxxxxxxT 2016-01-19 09:15:47 PARENT

1267290962 1262827435 TxxxxxxT 2016-01-19 11:09:05 PARENT

1284933855 1262827435 TxxxxxxT 2016-02-11 11:18:52 PARENT

1299685647 1262827435 TxxxxxxT 2016-02-23 13:40:18 ORPHAN

1299837528 1262827435 TxxxxxxT 2016-02-23 17:08:36 CURRENT

1299767977 1262827435 TxxxxxxT 2016-02-23 15:34:13 ORPHAN

1298269640 1262827435 TxxxxxxT 2016-02-22 13:16:46 ORPHAN

1299517249 1262827435 TxxxxxxT 2016-02-23 10:37:29 ORPHAN

In this case, again, it depends whether the DBID and DB_KEY are the same as the current incarnation or not.

What do you need to delete?

Basically:

Incarnations of databases that no longer exist
Incarnations of existing databases where the database has a more recent current incarnation, only if there are no backups still in the retention window

How to do it?

In order to be sure 100% that you can delete an incarnation, you have to verify that there are no recent backups (for instance, no backups more rercent than the current recovery window for that database). If the database does not have a specified recovery window but rather a default “CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default”, it is a bit more problematic… in this case let’s assume that we consider “old” an incarnation that does not backup since 1 year (365 days), ok?

Getting the last backup of each database

Sadly, there is not a single table where you can verify that. You have to collect the information from several tables. I think bdf, al, cdf, bs would suffice in most cases.

When you delete an incarnation you specify a db_key: you have to get the last backup for each db_key, with queries like this:

SELECT dbinc_key
     ,max(completion_time) max_al_time
  FROM al
    GROUP by dbinc_key;

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key;

Putting together all the tables:

WITH
   incs AS (
      SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_
	     ,dbinc_key
         ,db_key
	     ,db_name
	     ,reset_time
	     ,dbinc_status
      FROM rman.dbinc
        START WITH parent_dbinc_key IS NULL
      CONNECT BY PRIOR dbinc_key   = parent_dbinc_key
        ORDER BY db_name, db_key, level
    ),
   mbdf AS (
      SELECT dbinc_key
	     ,max(completion_time) max_bdf_time
	  FROM bdf
	     GROUP by dbinc_key
   ),
   mbrl AS (
      SELECT dbinc_key
	     ,max(next_time) max_brl_time
	  FROM brl
	     GROUP by dbinc_key
   ),
   mal AS (
      SELECT dbinc_key
	     ,max(completion_time) max_al_time
	  FROM al
	     GROUP by dbinc_key
   ),
   mcdf AS (
      SELECT dbinc_key
	     ,max(completion_time) max_cdf_time
	  FROM cdf
	     GROUP by dbinc_key
   ),
   mbs AS (
      SELECT db_key
	     ,max(completion_time) max_bs_time
	  FROM bs
	     GROUP by db_key
   )
SELECT incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name, dbinc_status
  ,greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) AS last_bck
FROM incs
  JOIN db ON (db.db_key=incs.db_key)
  LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)
  LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)
  LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)
  LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)
  LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)
;

WITH

incs AS (

SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_

,dbinc_key

,db_key

,db_name

,reset_time

,dbinc_status

FROM rman.dbinc

START WITH parent_dbinc_key IS NULL

CONNECT BY PRIOR dbinc_key = parent_dbinc_key

ORDER BY db_name, db_key, level

mbdf AS (

SELECT dbinc_key

,max(completion_time) max_bdf_time

FROM bdf

GROUP by dbinc_key

mbrl AS (

SELECT dbinc_key

,max(next_time) max_brl_time

FROM brl

GROUP by dbinc_key

mal AS (

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key

mcdf AS (

SELECT dbinc_key

,max(completion_time) max_cdf_time

FROM cdf

GROUP by dbinc_key

mbs AS (

SELECT db_key

,max(completion_time) max_bs_time

FROM bs

GROUP by db_key

)

SELECT incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name, dbinc_status

,greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) AS last_bck

FROM incs

JOIN db ON (db.db_key=incs.db_key)

LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)

LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)

LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)

LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)

LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)

;

Getting the recovery window

The configuration information for each database is stored inside the conf table, but the retention information is stored in a VARCHAR2, either ‘TO RECOVERY WINDOW OF % DAYS’ or ‘TO REDUNDANCY %’

You need to convert it to a number when the retention policy is recovery windows, otherwise you default it to 365 days wher the redundancy is used. You can add a column and a join to the query:

-- new column in the projection
,nvl(to_number(substr(c.value,23,length(c.value)-27)),365) as days

-- new join in the "from"
LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

-- new column in the projection

,nvl(to_number(substr(c.value,23,length(c.value)-27)),365) as days

-- new join in the "from"

LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

and eventually, either display if it the incarnation is no more used or filter by usage:

-- display if the incarnation is still used
,CASE WHEN
     greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
	 THEN 'OLD ONE!'
	 ELSE 'USED'
  END AS USED

-- or filter it
WHERE greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

-- display if the incarnation is still used

,CASE WHEN

greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

THEN 'OLD ONE!'

ELSE 'USED'

END AS USED

-- or filter it

WHERE greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

Delete the incarnations!

You can delete the incarnations with this procedure:

BEGIN
  dbms_rcvcat.unregisterdatabase(DB_KEY=>:db_key, DB_ID=>:db_id);
END;

BEGIN

dbms_rcvcat.unregisterdatabase(DB_KEY=>:db_key, DB_ID=>:db_id);

END;

This procedure will raise an exception (-20001, ‘Database not found’) when a database does not exist anymore (either already deleted by this procedure or by another session), so you need to handle it.

Putting all together:

col db_uq_name for a12
col dbinc_key_ for a30
set pages 100 lines 200
set serveroutput on
DECLARE

  e_dbatabase_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_dbatabase_not_found, -20001);

  CURSOR c_old_incarnations IS
  WITH
   incs AS (
      SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_
             ,dbinc_key
         ,db_key
             ,db_name
             ,reset_time
             ,dbinc_status
      FROM rman.dbinc
        START WITH parent_dbinc_key IS NULL
      CONNECT BY PRIOR dbinc_key   = parent_dbinc_key
        ORDER BY db_name, db_key, level
    ),
   mbdf AS (
      SELECT dbinc_key
             ,max(completion_time) max_bdf_time
          FROM bdf
             GROUP by dbinc_key
   ),
   mbrl AS (
      SELECT dbinc_key
             ,max(next_time) max_brl_time
          FROM brl
             GROUP by dbinc_key
   ),
   mal AS (
      SELECT dbinc_key
             ,max(completion_time) max_al_time
          FROM al
             GROUP by dbinc_key
   ),
   mcdf AS (
      SELECT dbinc_key
             ,max(completion_time) max_cdf_time
          FROM cdf
             GROUP by dbinc_key
   ),
   mbs AS (
      SELECT db_key
             ,max(completion_time) max_bs_time
          FROM bs
             GROUP by db_key
   )
  SELECT distinct incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name
  ,greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) AS last_bck
  ,CASE WHEN
     greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
         THEN 'OLD ONE!'
         ELSE 'USED'
  END AS USED
FROM incs
  JOIN db ON (db.db_key=incs.db_key)
  LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)
  LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)
  LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)
  LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)
  LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)
  LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')
 WHERE 1=1
 AND greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time ,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
  order by 4,3, 5
  ;

   r_old_incarnation    c_old_incarnations%ROWTYPE;

   BEGIN
        OPEN c_old_incarnations;
        LOOP
                FETCH c_old_incarnations INTO r_old_incarnation;
                EXIT WHEN  c_old_incarnations%NOTFOUND;

                dbms_output.put('Purging db: ' || r_old_incarnation.db_name);
                dbms_output.put('       IncKey: ' || r_old_incarnation.db_key);
                dbms_output.put('       DBID: ' || r_old_incarnation.db_id);
                dbms_output.put_line('  Last BCK: ' || to_char(r_old_incarnation.last_bck,'YYYY-MM-DD'));
                BEGIN
                   dbms_rcvcat.unregisterdatabase(DB_KEY => r_old_incarnation.db_key, DB_ID => r_old_incarnation.db_id);
                EXCEPTION
                    WHEN e_dbatabase_not_found THEN
                    dbms_output.put_line('Database already unregistered');
                END;
        END LOOP;

        CLOSE c_old_incarnations;
	
END;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

col db_uq_name for a12

col dbinc_key_ for a30

set pages 100 lines 200

set serveroutput on

DECLARE

e_dbatabase_not_found EXCEPTION;

PRAGMA EXCEPTION_INIT (e_dbatabase_not_found, -20001);

CURSOR c_old_incarnations IS

WITH

incs AS (

SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_

,dbinc_key

,db_key

,db_name

,reset_time

,dbinc_status

FROM rman.dbinc

START WITH parent_dbinc_key IS NULL

CONNECT BY PRIOR dbinc_key = parent_dbinc_key

ORDER BY db_name, db_key, level

mbdf AS (

SELECT dbinc_key

,max(completion_time) max_bdf_time

FROM bdf

GROUP by dbinc_key

mbrl AS (

SELECT dbinc_key

,max(next_time) max_brl_time

FROM brl

GROUP by dbinc_key

mal AS (

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key

mcdf AS (

SELECT dbinc_key

,max(completion_time) max_cdf_time

FROM cdf

GROUP by dbinc_key

mbs AS (

SELECT db_key

,max(completion_time) max_bs_time

FROM bs

GROUP by db_key

)

SELECT distinct incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name

,greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) AS last_bck

,CASE WHEN

greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

THEN 'OLD ONE!'

ELSE 'USED'

END AS USED

FROM incs

JOIN db ON (db.db_key=incs.db_key)

LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)

LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)

LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)

LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)

LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)

LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

WHERE 1=1

AND greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time ,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

order by 4,3, 5

;

r_old_incarnation c_old_incarnations%ROWTYPE;

BEGIN

OPEN c_old_incarnations;

LOOP

FETCH c_old_incarnations INTO r_old_incarnation;

EXIT WHEN c_old_incarnations%NOTFOUND;

dbms_output.put('Purging db: ' || r_old_incarnation.db_name);

dbms_output.put(' IncKey: ' || r_old_incarnation.db_key);

dbms_output.put(' DBID: ' || r_old_incarnation.db_id);

dbms_output.put_line(' Last BCK: ' || to_char(r_old_incarnation.last_bck,'YYYY-MM-DD'));

BEGIN

dbms_rcvcat.unregisterdatabase(DB_KEY => r_old_incarnation.db_key, DB_ID => r_old_incarnation.db_id);

EXCEPTION

WHEN e_dbatabase_not_found THEN

dbms_output.put_line('Database already unregistered');

END;

END LOOP;

CLOSE c_old_incarnations;

END;

I have used this procedure today for the first time and it worked like a charm.

However, if you have any adjustment or suggestion, don’t hesitate to comment it 🙂

HTH

DBMS_QOPATCH, datapatch, rollback, apply force

Posted on November 21, 2016 by Ludovico

I am working for a customer on a quite big implementation of Cold Failover Cluster with Oracle Grid Infrastructure on Linux. I hope to have some material to publish soon about it! However, in this post I will be talking about patching the database in a cold-failover environment.

DISCLAIMER: I use massively scripts provided in this great blog post by Simon Pane:

https://www.pythian.com/blog/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/

Thank you Simon for sharing this 🙂

Intro

We are not yet in the process of doing out-of-place patching; at the moment the customer prefers to do in-place patching:

evacuate a node by relocating all the databases on other nodes
patching the node binaries
move back the databases and patch them with datapatch
do the same for the remaining nodes

I beg to disagree with this method, being a fan of having many patched golden copies distributed on all servers and patching the databases by just changing the ORACLE_HOME and running datapatch (like Rapid Home Provisioning does). But, this is the situation today, and we have to live with it.

Initial situation

Server 1, 2 and 3: one-off 20139391 applied
New database created

When the DBCA creates a new database, in 12.1.0.2, it does not run datapatch by default, thus, the database does not have any patches installed.

However, this specific one-off patch does not modify anything in the database (sql_patch=false)

SQL> -- Patches installed in the oracle home
SQL> r
  1   with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2   select x.patch_id, x.patch_uid, x.description
  3     from a,
  4          xmltable('InventoryInstance/patches/*'
  5             passing a.patch_output
  6             columns
  7                patch_id number path 'patchID',
  8                patch_uid number path 'uniquePatchID',
  9                description varchar2(80) path 'patchDescription',
 10                sql_patch varchar2(8) path 'sqlPatch'
 10          ) x
 11 *

  PATCH_ID  PATCH_UID DESCRIPTION               SQL_PATCH
---------- ---------- ------------------------- ---------
  20139391   18466820                           false

SQL> -- Patches installed in the database
SQL> select s.patch_id, s.patch_uid, s.description from dba_registry_sqlpatch s;
no rows selected

SQL>

SQL> -- Patches installed in the oracle home

SQL> r

1 with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

2 select x.patch_id, x.patch_uid, x.description

3 from a,

4 xmltable('InventoryInstance/patches/*'

5 passing a.patch_output

6 columns

7 patch_id number path 'patchID',

8 patch_uid number path 'uniquePatchID',

9 description varchar2(80) path 'patchDescription',

10 sql_patch varchar2(8) path 'sqlPatch'

10 ) x

11 *

PATCH_ID PATCH_UID DESCRIPTION SQL_PATCH

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

20139391 18466820 false

SQL> -- Patches installed in the database

SQL> select s.patch_id, s.patch_uid, s.description from dba_registry_sqlpatch s;

no rows selected

SQL>

and the datapatch runs without touching the db:

oracle1> $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.0.0 on Wed Nov  2 13:34:10 2016
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...done

Current state of SQL patches:

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  Nothing to apply

SQL Patching tool complete on Wed Nov  2 13:34:13 2016
oracle1>

oracle1> $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 12.2.0.0.0 on Wed Nov 2 13:34:10 2016

Connecting to database...OK

Determining current state...done

Current state of SQL patches:

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

Nothing to apply

SQL Patching tool complete on Wed Nov 2 13:34:13 2016

oracle1>

Next step: I evacuate the server 2 and patch it, then I relocate my database on it

oracle2> $ORACLE_HOME/OPatch/opatch lspatches
24340679;DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)

OPatch succeeded.
oracle2>
oracle2> crsctl relocate res theludot.db -n oracle2
CRS-2673: Attempting to stop 'theludot.db' on 'oracle1'
CRS-2677: Stop of 'theludot.db' on 'oracle1' succeeded
CRS-2672: Attempting to start 'theludot.db' on 'oracle2'
CRS-2676: Start of 'theludot.db' on 'oracle2' succeeded
oracle2>

oracle2> $ORACLE_HOME/OPatch/opatch lspatches

24340679;DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)

OPatch succeeded.

oracle2>

oracle2> crsctl relocate res theludot.db -n oracle2

CRS-2673: Attempting to stop 'theludot.db' on 'oracle1'

CRS-2677: Stop of 'theludot.db' on 'oracle1' succeeded

CRS-2672: Attempting to start 'theludot.db' on 'oracle2'

CRS-2676: Start of 'theludot.db' on 'oracle2' succeeded

oracle2>

Now the database is not at the same level of the binaries and need to be patched:

SQL> -- Patches installed in the oracle home
SQL> r
  1  with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2   select x.*
  3     from a,
  4   xmltable('InventoryInstance/patches/*'
  5   passing a.patch_output
  6   columns
  7      patch_id number path 'patchID',
  8      patch_uid number path 'uniquePatchID',
  9      description varchar2(80) path 'patchDescription',
 10    constituent number path 'constituent',
 11    patch_type varchar2(20) path 'patchType',
 12    rollbackable varchar2(20) path 'rollbackable',
 13    sql_patch varchar2(8) path 'sqlPatch',
 14    DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'
 15*  ) x

  PATCH_ID  PATCH_UID DESCRIPTION                                        CONSTITUENT PATCH_TYPE           ROLLBACKABLE SQL_PATC DBSTARTMOD
---------- ---------- -------------------------------------------------- ----------- -------------------- ------------ -------- ----------
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)     24340679 singleton            true         true     normal
  23144544   20247727 DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)     24340679 singleton            true         true     normal
  22806133   19983161 DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133)     24340679 singleton            true         true     normal
  21949015   19576071 DATABASE BUNDLE PATCH: 12.1.0.2.160119 (21949015)     24340679 singleton            true         true     normal
  21694919   19338504 DATABASE BUNDLE PATCH: 12.1.0.2.13 (21694919)         24340679 singleton            true         true     normal
  21527488   19238856 DATABASE BUNDLE PATCH: 12.1.0.2.12 (21527488)         24340679 singleton            true         true     normal
  21359749   19147148 DATABASE BUNDLE PATCH: 12.1.0.2.11 (21359749)         24340679 singleton            true         true     normal
  21125181   18992109 DATABASE BUNDLE PATCH: 12.1.0.2.10 (21125181)         24340679 singleton            true         true     normal
  20950328   18903184 DATABASE BUNDLE PATCH: 12.1.0.2.9 (20950328)          24340679 singleton            true         true     normal
  20788771   18810992 DATABASE BUNDLE PATCH: 12.1.0.2.8 (20788771)          24340679 singleton            true         true     normal
  20594149   18687526 DATABASE BUNDLE PATCH: 12.1.0.2.7 (20594149)          24340679 singleton            true         true     normal
  20415006   18565812 DATABASE BUNDLE PATCH: 12.1.0.2.6 (20415006)          24340679 singleton            true         true     normal
  20243804   18468778 DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)          24340679 singleton            true         true     normal

SQL> -- Patches installed in the oracle home

SQL> r

1 with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

2 select x.*

3 from a,

4 xmltable('InventoryInstance/patches/*'

5 passing a.patch_output

6 columns

7 patch_id number path 'patchID',

8 patch_uid number path 'uniquePatchID',

9 description varchar2(80) path 'patchDescription',

10 constituent number path 'constituent',

11 patch_type varchar2(20) path 'patchType',

12 rollbackable varchar2(20) path 'rollbackable',

13 sql_patch varchar2(8) path 'sqlPatch',

14 DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'

15* ) x

PATCH_ID PATCH_UID DESCRIPTION CONSTITUENT PATCH_TYPE ROLLBACKABLE SQL_PATC DBSTARTMOD

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

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 24340679 singleton true true normal

23144544 20247727 DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544) 24340679 singleton true true normal

22806133 19983161 DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133) 24340679 singleton true true normal

21949015 19576071 DATABASE BUNDLE PATCH: 12.1.0.2.160119 (21949015) 24340679 singleton true true normal

21694919 19338504 DATABASE BUNDLE PATCH: 12.1.0.2.13 (21694919) 24340679 singleton true true normal

21527488 19238856 DATABASE BUNDLE PATCH: 12.1.0.2.12 (21527488) 24340679 singleton true true normal

21359749 19147148 DATABASE BUNDLE PATCH: 12.1.0.2.11 (21359749) 24340679 singleton true true normal

21125181 18992109 DATABASE BUNDLE PATCH: 12.1.0.2.10 (21125181) 24340679 singleton true true normal

20950328 18903184 DATABASE BUNDLE PATCH: 12.1.0.2.9 (20950328) 24340679 singleton true true normal

20788771 18810992 DATABASE BUNDLE PATCH: 12.1.0.2.8 (20788771) 24340679 singleton true true normal

20594149 18687526 DATABASE BUNDLE PATCH: 12.1.0.2.7 (20594149) 24340679 singleton true true normal

20415006 18565812 DATABASE BUNDLE PATCH: 12.1.0.2.6 (20415006) 24340679 singleton true true normal

20243804 18468778 DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804) 24340679 singleton true true normal

The column CONSTITUENT is important here because it tells us what the parent patch_id is. This is the column that we have to check when we want to know if the patch has been applied on the database.

oracle2> $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Wed Nov  2 13:47:49 2016
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_63956_2016_11_02_13_47_49/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
  ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_THELUDOT_2016Nov02_13_48_03.log (no errors)
SQL Patching tool complete on Wed Nov  2 13:49:51 2016
oracle2>

oracle2> $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Wed Nov 2 13:47:49 2016

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_63956_2016_11_02_13_47_49/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series DBBP:

ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

The following patches will be applied:

24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...

Patch 24340679 apply: SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_THELUDOT_2016Nov02_13_48_03.log (no errors)

SQL Patching tool complete on Wed Nov 2 13:49:51 2016

oracle2>

Now the patch is visible inside the dba_registry_sqlpatch:

SQL> r
  1* select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id  from dba_registry_sqlpatch

  PATCH_ID  PATCH_UID DESCRIPTION                                        ACTION_TIME                    ACTION          STATUS   BUNDLE_SERIES  BUNDLE_ID
---------- ---------- -------------------------------------------------- ------------------------------ --------------- -------- ------------- ----------
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)  02-NOV-16 01.49.51.664800 PM   APPLY           SUCCESS  DBBP              161018

SQL> r

1* select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id from dba_registry_sqlpatch

PATCH_ID PATCH_UID DESCRIPTION ACTION_TIME ACTION STATUS BUNDLE_SERIES BUNDLE_ID

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

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 02-NOV-16 01.49.51.664800 PM APPLY SUCCESS DBBP 161018

Notice that the child patches are not listed in thie view.

Rolling back

Now, one node is patched, but the others are not. What happen if I relocate the patched database to a non-patched node?

oracle1> crsctl relocate res theludot.db -n oracle1
CRS-2673: Attempting to stop 'theludot.db' on 'oracle2'
CRS-2677: Stop of 'theludot.db' on 'oracle2' succeeded
CRS-2672: Attempting to start 'theludot.db' on 'oracle1'
CRS-2676: Start of 'theludot.db' on 'oracle1' succeeded
oracle1>

oracle1> crsctl relocate res theludot.db -n oracle1

CRS-2673: Attempting to stop 'theludot.db' on 'oracle2'

CRS-2677: Stop of 'theludot.db' on 'oracle2' succeeded

CRS-2672: Attempting to start 'theludot.db' on 'oracle1'

CRS-2676: Start of 'theludot.db' on 'oracle1' succeeded

oracle1>

The patch is applied inside the database but not in the binaries!

SQL>  select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id
  2   from dba_registry_sqlpatch;

  PATCH_ID  PATCH_UID DESCRIPTION                                        ACTION_TIME                    ACTION          STATUS   BUNDLE_SERIES  BUNDLE_ID
---------- ---------- -------------------------------------------------- ------------------------------ --------------- -------- ------------- ----------
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)  02.11.16 13:49:51.664800       APPLY           SUCCESS  DBBP              161018

SQL> r
  1  with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2   select x.*
  3     from a,
  4   xmltable('InventoryInstance/patches/*'
  5   passing a.patch_output
  6   columns
  7      patch_id number path 'patchID',
  8      patch_uid number path 'uniquePatchID',
  9      description varchar2(80) path 'patchDescription',
 10    constituent number path 'constituent',
 11    patch_type varchar2(20) path 'patchType',
 12    rollbackable varchar2(20) path 'rollbackable',
 13    sql_patch varchar2(8) path 'sqlPatch',
 14    DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'
 15* ) x

  PATCH_ID  PATCH_UID DESCRIPTION                                        CONSTITUENT PATCH_TYPE           ROLLBACKABLE SQL_PATC DBSTARTMOD
---------- ---------- -------------------------------------------------- ----------- -------------------- ------------ -------- ----------
  20139391   18466820                                                                singleton            true         false

SQL> select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id

2 from dba_registry_sqlpatch;

PATCH_ID PATCH_UID DESCRIPTION ACTION_TIME ACTION STATUS BUNDLE_SERIES BUNDLE_ID

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

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 02.11.16 13:49:51.664800 APPLY SUCCESS DBBP 161018

SQL> r

1 with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

2 select x.*

3 from a,

4 xmltable('InventoryInstance/patches/*'

5 passing a.patch_output

6 columns

7 patch_id number path 'patchID',

8 patch_uid number path 'uniquePatchID',

9 description varchar2(80) path 'patchDescription',

10 constituent number path 'constituent',

11 patch_type varchar2(20) path 'patchType',

12 rollbackable varchar2(20) path 'rollbackable',

13 sql_patch varchar2(8) path 'sqlPatch',

14 DBStartMode varchar2(10) path 'sqlPatchDatabaseStartupMode'

15* ) x

PATCH_ID PATCH_UID DESCRIPTION CONSTITUENT PATCH_TYPE ROLLBACKABLE SQL_PATC DBSTARTMOD

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

20139391 18466820 singleton true false

If I run datapatch again, the patch is rolled back:

oracle1> $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.0.0 on Wed Nov  2 14:48:50 2016
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...done

Current state of SQL patches:

Adding patches to installation queue and performing prereq checks...
Installation queue:
  The following patches will be rolled back:
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))
  Nothing to apply

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_24776.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 24340679 rollback: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_rollback_THELUDOT_2016Nov. 02_14_48_53.log (no errors)
SQL Patching tool complete on Wed Nov  2 14:48:53 2016
oracle1>

oracle1> $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 12.2.0.0.0 on Wed Nov 2 14:48:50 2016

Connecting to database...OK

Determining current state...done

Current state of SQL patches:

Adding patches to installation queue and performing prereq checks...

Installation queue:

The following patches will be rolled back:

24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Nothing to apply

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_24776.lst

catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts

catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...

Patch 24340679 rollback: SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_rollback_THELUDOT_2016Nov. 02_14_48_53.log (no errors)

SQL Patching tool complete on Wed Nov 2 14:48:53 2016

oracle1>

The patch has been rolled back according to the datapatch, and the action is shown in the dba_registry_sqlpatch:

SQL> r
  1   select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id
  2*  from dba_registry_sqlpatch

  PATCH_ID  PATCH_UID DESCRIPTION                                        ACTION_TIME                    ACTION          STATUS   BUNDLE_SERIES  BUNDLE_ID
---------- ---------- -------------------------------------------------- ------------------------------ --------------- -------- ------------- ----------
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)  02.11.16 13:49:51.664800       APPLY           SUCCESS  DBBP              161018
  24340679   20646358                                                    02.11.16 14:48:53.760632       ROLLBACK        SUCCESS

SQL> r

1 select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id

2* from dba_registry_sqlpatch

PATCH_ID PATCH_UID DESCRIPTION ACTION_TIME ACTION STATUS BUNDLE_SERIES BUNDLE_ID

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

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 02.11.16 13:49:51.664800 APPLY SUCCESS DBBP 161018

24340679 20646358 02.11.16 14:48:53.760632 ROLLBACK SUCCESS

But if I look at the logfile, the patch had some errors:

oracle1> grep "ORA-\|PLS-" /tmp/sqlpatch_catcon_0.log
ORA-20001: set_patch_metadata not called
ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 621
ORA-06512: a ligne 2
IGNORABLE ERRORS: ORA-02303
IGNORABLE ERRORS: ORA-01418
IGNORABLE ERRORS: ORA-01435
IGNORABLE ERRORS: ORA-01435
IGNORABLE ERRORS: ORA-01435
IGNORABLE ERRORS: ORA-01435
IGNORABLE ERRORS: ORA-01435
IGNORABLE ERRORS: ORA-01435
ORA-01555: cliches trop vieux : rollback segment no , nomme "", trop petit
ORA-22924: cliche trop ancien
ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 102
ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 663
ORA-06512: a ligne 1

oracle1> grep "ORA-\|PLS-" /tmp/sqlpatch_catcon_0.log

ORA-20001: set_patch_metadata not called

ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 621

ORA-06512: a ligne 2

IGNORABLE ERRORS: ORA-02303

IGNORABLE ERRORS: ORA-01418

IGNORABLE ERRORS: ORA-01435

ORA-01555: cliches trop vieux : rollback segment no , nomme "", trop petit

ORA-22924: cliche trop ancien

ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 102

ORA-06512: a "SYS.DBMS_SQLPATCH", ligne 663

ORA-06512: a ligne 1

Indeed, the patch looks still there:

SQL> r
  1  SELECT dbms_sqlpatch.sql_registry_state
  2* FROM dual

SQL_REGISTRY_STATE
--------------------------------------------------------------------------------
<sql_registry_state>
  <!-- Non bundle patches -->
  <!-- Bundle patches -->
  <patch bundle="yes" id="24340679" uid="20646358" action="APPLY" status="SUCCES
S" bundle_series="DBBP" bundle_id="161018">DBBP bundle patch 161018 (DATABASE BU
NDLE PATCH: 12.1.0.2.161018 (24340679))</patch>
</sql_registry_state>

SQL> r

1 SELECT dbms_sqlpatch.sql_registry_state

2* FROM dual

SQL_REGISTRY_STATE

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

<sql_registry_state>

<patch bundle="yes" id="24340679" uid="20646358" action="APPLY" status="SUCCES

S" bundle_series="DBBP" bundle_id="161018">DBBP bundle patch 161018 (DATABASE BU

NDLE PATCH: 12.1.0.2.161018 (24340679))</patch>

</sql_registry_state>

If I try to run it again, it does nothing/it fails saying the patch is not there:

oracle1> $ORACLE_HOME/OPatch/datapatch -rollback 24340679
SQL Patching tool version 12.2.0.0.0 on Wed Nov  2 16:10:49 2016
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  Nothing to roll back
  Nothing to apply

SQL Patching tool complete on Wed Nov  2 16:10:51 2016

oracle1> $ORACLE_HOME/OPatch/datapatch -rollback 24340679 -force
SQL Patching tool version 12.2.0.0.0 on Wed Nov  2 16:11:01 2016
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...done

Error: prereq checks failed!
  patch 24340679: Could not determine unique patch ID for patch 24340679 because it is not present in the SQL registry
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.

SQL Patching tool complete on Wed Nov  2 16:11:01 2016

oracle1> $ORACLE_HOME/OPatch/datapatch -rollback 24340679

SQL Patching tool version 12.2.0.0.0 on Wed Nov 2 16:10:49 2016

Connecting to database...OK

Determining current state...done

Adding patches to installation queue and performing prereq checks...done

Installation queue:

Nothing to roll back

Nothing to apply

SQL Patching tool complete on Wed Nov 2 16:10:51 2016

oracle1> $ORACLE_HOME/OPatch/datapatch -rollback 24340679 -force

SQL Patching tool version 12.2.0.0.0 on Wed Nov 2 16:11:01 2016

Connecting to database...OK

Determining current state...done

Error: prereq checks failed!

patch 24340679: Could not determine unique patch ID for patch 24340679 because it is not present in the SQL registry

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.

SQL Patching tool complete on Wed Nov 2 16:11:01 2016

What does it say on the patched node?

oracle2> crsctl relocate res theludot.db -n oracle2
CRS-2673: Attempting to stop 'theludot.db' on 'oracle1'
CRS-2677: Stop of 'theludot.db' on 'oracle1' succeeded
CRS-2672: Attempting to start 'theludot.db' on 'oracle2'
CRS-2676: Start of 'theludot.db' on 'oracle2' succeeded
oracle2>
oracle2> $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Wed Nov  2 16:15:36 2016
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7878_2016_11_02_16_15_36/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
  ID 161018 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  Nothing to apply

SQL Patching tool complete on Wed Nov  2 16:15:49 2016

oracle2> crsctl relocate res theludot.db -n oracle2

CRS-2673: Attempting to stop 'theludot.db' on 'oracle1'

CRS-2677: Stop of 'theludot.db' on 'oracle1' succeeded

CRS-2672: Attempting to start 'theludot.db' on 'oracle2'

CRS-2676: Start of 'theludot.db' on 'oracle2' succeeded

oracle2>

oracle2> $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Wed Nov 2 16:15:36 2016

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7878_2016_11_02_16_15_36/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series DBBP:

ID 161018 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

Nothing to apply

SQL Patching tool complete on Wed Nov 2 16:15:49 2016

Whaaat? datapatch there says that the patch IS in the registry and there’s nothing to do. Let’s try to force its apply again:

oracle2> $ORACLE_HOME/OPatch/datapatch -verbose -apply 24340679 -force
SQL Patching tool version 12.1.0.2.0 on Wed Nov  2 16:17:40 2016
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12726_2016_11_02_16_17_40/sqlpatch_invocation.log

Connecting to database...OK
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
  ID 161018 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_THELUDOT_2016Nov02_16_17_40.log (no errors)
SQL Patching tool complete on Wed Nov  2 16:18:50 2016

oracle2> $ORACLE_HOME/OPatch/datapatch -verbose -apply 24340679 -force

SQL Patching tool version 12.1.0.2.0 on Wed Nov 2 16:17:40 2016

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12726_2016_11_02_16_17_40/sqlpatch_invocation.log

Connecting to database...OK

Determining current state...done

Current state of SQL patches:

Bundle series DBBP:

ID 161018 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

The following patches will be applied:

24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...

Patch 24340679 apply: SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_THELUDOT_2016Nov02_16_17_40.log (no errors)

SQL Patching tool complete on Wed Nov 2 16:18:50 2016

SQL> r
  1  select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id
  2* from dba_registry_sqlpatch

  PATCH_ID  PATCH_UID DESCRIPTION                                        ACTION_TIME                    ACTION          STATUS   BUNDLE_SERIES  BUNDLE_ID
---------- ---------- -------------------------------------------------- ------------------------------ --------------- -------- ------------- ----------
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)  02-NOV-16 01.49.51.664800 PM   APPLY           SUCCESS  DBBP              161018
  24340679   20646358                                                    02-NOV-16 02.48.53.760632 PM   ROLLBACK        SUCCESS
  24340679   20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)  02-NOV-16 04.18.50.320745 PM   APPLY           SUCCESS  DBBP              161018

SQL> r

1 select patch_id, patch_uid, description, action_time, action, status, bundle_series, bundle_id

2* from dba_registry_sqlpatch

PATCH_ID PATCH_UID DESCRIPTION ACTION_TIME ACTION STATUS BUNDLE_SERIES BUNDLE_ID

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

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 02-NOV-16 01.49.51.664800 PM APPLY SUCCESS DBBP 161018

24340679 20646358 02-NOV-16 02.48.53.760632 PM ROLLBACK SUCCESS

24340679 20646358 DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679) 02-NOV-16 04.18.50.320745 PM APPLY SUCCESS DBBP 161018

Conclusion

I’m not sure whether it is safe to run the patched database in a non-patched Oracle Home. I guess it is time for a new SR 🙂

Meanwhile, we will try hard not to relocate the databases once they have been patched.

Cheers

—

Ludo

Getting the Oracle Homes in a server from the oraInventory

Posted on November 21, 2016 by Ludovico

The information contained in the oratab should always be updated, but it is not always reliable. If you want to know what Oracle installations you have in a server, better to get it from the Oracle Universal Installer or, if you want some shortcuts, do some grep magics inside the inventory with the shell.

The following diagram is a simplified structure of the inventory that shows what entries are present in the central inventory (one per server) and the local inventories (one per Oracle Home).

You can use this simple function to get some content out of it, including the edition (that information is a step deeper in the local inventory).

# [ oracle@testlab:/u01/app/oracle/ [17:53:48] [12.1.0.2.0 EE SID=theludot] 0 ] #
# type lsoh
lsoh is a function
lsoh ()
{
    CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`;
    IFS='
';
    echo;
    printf "%-22s %-55s %-12s %-9s\n" HOME LOCATION VERSION EDITION;
    echo ---------------------- ------------------------------------------------------- ------------ ---------;
    for line in `grep "<HOME NAME=" ${CENTRAL_ORAINV}/ContentsXML/inventory.xml 2>/dev/null`;
    do
        unset ORAVERSION;
        unset ORAEDITION;
        OH=`echo $line | tr ' ' '\n' | grep ^LOC= | awk -F\" '{print $2}'`;
        OH_NAME=`echo $line | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;
        comp_file=$OH/inventory/ContentsXML/comps.xml;
        comp_xml=`grep "COMP NAME" $comp_file | head -1`;
        comp_name=`echo $comp_xml | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;
        comp_vers=`echo $comp_xml | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
        case $comp_name in
            "oracle.crs")
                ORAVERSION=$comp_vers;
                ORAEDITION=GRID
            ;;
            "oracle.sysman.top.agent")
                ORAVERSION=$comp_vers;
                ORAEDITION=AGT
            ;;
            "oracle.server")
                ORAVERSION=`grep "PATCH NAME=\"oracle.server\"" $comp_file 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
                ORAEDITION="DBMS";
                if [ -z "$ORAVERSION" ]; then
                    ORAVERSION=$comp_vers;
                fi;
                ORAMAJOR=`echo $ORAVERSION |  cut -d . -f 1`;
                case $ORAMAJOR in
                    11 | 12)
                        ORAEDITION="DBMS "`grep "oracle_install_db_InstallType" $OH/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
                    ;;
                    10)
                        ORAEDITION="DBMS "`grep "s_serverInstallType" $OH/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
                    ;;
                esac
            ;;
        esac;
        [[ -n $ORAEDITION ]] && printf "%-22s %-55s %-12s %-9s\n" $OH_NAME $OH $ORAVERSION $ORAEDITION;
    done;
    echo
}
# [ oracle@testlab:/u01/app/oracle/sbin [17:53:48] [12.1.0.2.0 EE SID=theludot] 0 ] #
# lsoh

HOME                   LOCATION                                                VERSION      EDITION
---------------------- ------------------------------------------------------- ------------ ---------
OraHome12C             /u01/app/oracle/product/12.1.0.2                        12.1.0.2.0   DBMS EE
OraDb11g_home1         /u01/app/oracle/product/11.2.0.4                        11.2.0.4.0   DBMS EE
OraGI12Home1           /u01/app/grid/product/grid                              12.1.0.2.0   GRID
agent12c1              /u01/app/oracle/product/agent12c/core/12.1.0.5.0        12.1.0.5.0   AGT

# [ oracle@testlab:/u01/app/oracle/ [17:53:48] [12.1.0.2.0 EE SID=theludot] 0 ] #

# type lsoh

lsoh is a function

lsoh ()

{

CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`;

IFS='

echo;

printf "%-22s %-55s %-12s %-9s\n" HOME LOCATION VERSION EDITION;

echo ---------------------- ------------------------------------------------------- ------------ ---------;

for line in `grep "<HOME NAME=" ${CENTRAL_ORAINV}/ContentsXML/inventory.xml 2>/dev/null`;

unset ORAVERSION;

unset ORAEDITION;

OH=`echo $line | tr ' ' '\n' | grep ^LOC= | awk -F\" '{print $2}'`;

OH_NAME=`echo $line | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;

comp_file=$OH/inventory/ContentsXML/comps.xml;

comp_xml=`grep "COMP NAME" $comp_file | head -1`;

comp_name=`echo $comp_xml | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;

comp_vers=`echo $comp_xml | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

case $comp_name in

"oracle.crs")

ORAVERSION=$comp_vers;

ORAEDITION=GRID

;;

"oracle.sysman.top.agent")

ORAVERSION=$comp_vers;

ORAEDITION=AGT

;;

"oracle.server")

ORAVERSION=`grep "PATCH NAME=\"oracle.server\"" $comp_file 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

ORAEDITION="DBMS";

if [ -z "$ORAVERSION" ]; then

ORAVERSION=$comp_vers;

fi;

ORAMAJOR=`echo $ORAVERSION | cut -d . -f 1`;

case $ORAMAJOR in

11 | 12)

ORAEDITION="DBMS "`grep "oracle_install_db_InstallType" $OH/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

10)

ORAEDITION="DBMS "`grep "s_serverInstallType" $OH/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

esac

;;

esac;

[[ -n $ORAEDITION ]] && printf "%-22s %-55s %-12s %-9s\n" $OH_NAME $OH $ORAVERSION $ORAEDITION;

done;

echo

}

# [ oracle@testlab:/u01/app/oracle/sbin [17:53:48] [12.1.0.2.0 EE SID=theludot] 0 ] #

# lsoh

HOME LOCATION VERSION EDITION

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

OraHome12C /u01/app/oracle/product/12.1.0.2 12.1.0.2.0 DBMS EE

OraDb11g_home1 /u01/app/oracle/product/11.2.0.4 11.2.0.4.0 DBMS EE

OraGI12Home1 /u01/app/grid/product/grid 12.1.0.2.0 GRID

agent12c1 /u01/app/oracle/product/agent12c/core/12.1.0.5.0 12.1.0.5.0 AGT

HTH

Loading resolved Adaptive Plans in the SQL Plan Management

Posted on November 18, 2016 by Ludovico

In my previous post, I have shown that loading Adaptive Plans in the SQL Plan Baseline leads to using the original plan. Well, actually, this is true when you capture them via the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter.

Thanks to a tweet by Neil Chandler, I’ve realized that it was a good idea to show also the case when the plan is loaded manually.

When the adaptive plan switches to the alternative plan, the plan_hash_value also changes, and can be loaded manually in the baseline with DBMS_SPM.

Let’s reset everything and retry quickly to:

Capture the plan automatically (this will lead to the original plan)
Load the plan manually (I will specify to load the alternative plan, if resolved)
Drop the plan captured automatically
Use the newly accepted baseline

SQL> connect / as sysdba
Connected.

SQL> DECLARE
  2    v_dropped_plans number;
  3  BEGIN
  4    v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  5       sql_handle => 'SQL_6c4c6680810dd01a'
  6  );
  7    DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

no rows selected

SQL> connect ludo/ludo
Connected.
SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...
30 rows selected.

SQL> r
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...
30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER
--------------------------------------- --------------- ------------
1km5kczcgr0fr                                2672205743            1

SELECT /*+ GATHER_PLAN_STATISTICS */
       a.data AS tab1_data,
  2    3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...
30 rows selected.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER
--------------------------------------- --------------- ------------
1km5kczcgr0fr                                2672205743            1
1km5kczcgr0fr                                2672205743            2

SQL> ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...
30 rows selected.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID                                  PLAN_HASH_VALUE CHILD_NUMBER
--------------------------------------- --------------- ------------
1km5kczcgr0fr                                1599395313            0
1km5kczcgr0fr                                2672205743            1
1km5kczcgr0fr                                2672205743            2


SQL> connect / as sysdba
Connected.
SQL> VARIABLE cnt NUMBER
SQL> EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1km5kczcgr0fr',plan_hash_value => '1599395313');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                PLAN_NAME                           SQL_TEXT                               ENABLED   ACCEPTED  FIXED
------------------------- ----------------------------------- -------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a      SQL_PLAN_6sm36h20hvn0u55a25f73      SELECT /*+ GATHER_PLAN_STATISTICS */   YES       YES       NO
                                                                     a.data AS tab1_data,
                                                                     b.data A

SQL_6c4c6680810dd01a      SQL_PLAN_6sm36h20hvn0ud64ac9be      SELECT /*+ GATHER_PLAN_STATISTICS */   YES       YES       NO
                                                                     a.data AS tab1_data,
                                                                     b.data A


SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_6c4c6680810dd01a
SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
          a.id WHERE  a.code = 'ONE'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |
|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |
|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0ud64ac9be         Plan id: 3595225534
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 1599395313

----------------------------------------------------------------------------------------------------
|   Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                     |           |       |       |     3 (100)|          |
|  *  1 |  HASH JOIN                           |           |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1      |     1 |    11 |     2   (0)| 00:00:01 |
|  *  3 |    INDEX RANGE SCAN                  | TAB1_CODE |     1 |       |     1   (0)| 00:00:01 |
|     4 |   TABLE ACCESS FULL                  | TAB2      |    25 |   150 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   3 - access("A"."CODE"='ONE')

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

65 rows selected.


SQL> VARIABLE cnt NUMBER
SQL>  EXECUTE :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE( SQL_HANDLE => 'SQL_6c4c6680810dd01a',plan_name => 'SQL_PLAN_6sm36h20hvn0u55a25f73');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                PLAN_NAME                           SQL_TEXT                               ENABLED   ACCEPTED  FIXED
------------------------- ----------------------------------- -------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a      SQL_PLAN_6sm36h20hvn0ud64ac9be      SELECT /*+ GATHER_PLAN_STATISTICS */   YES       YES       NO
                                                                     a.data AS tab1_data,
                                                                     b.data A

SQL> conn ludo/ludo
Connected.
SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...
30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 1599395313

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |      1 |        |     24 |00:00:00.01 |      71 |       |       |          |
|*  1 |  HASH JOIN                           |           |      1 |     25 |     24 |00:00:00.01 |      71 |  1888K|  1888K| 1921K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1      |      1 |      1 |  10001 |00:00:00.01 |      62 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | TAB1_CODE |      1 |      1 |  10001 |00:00:00.01 |      37 |       |       |          |
|   4 |   TABLE ACCESS FULL                  | TAB2      |      1 |    100 |    100 |00:00:00.01 |       9 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   3 - access("A"."CODE"='ONE')

Note
-----
   - SQL plan baseline SQL_PLAN_6sm36h20hvn0ud64ac9be used for this statement


28 rows selected.

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

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

SQL> connect / as sysdba

Connected.

SQL> DECLARE

2 v_dropped_plans number;

3 BEGIN

4 v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

5 sql_handle => 'SQL_6c4c6680810dd01a'

6 );

7 DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');

8 END;

9 /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

no rows selected

SQL> connect ludo/ludo

Connected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID PLAN_HASH_VALUE CHILD_NUMBER

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

1km5kczcgr0fr 2672205743 1

SELECT /*+ GATHER_PLAN_STATISTICS */

a.data AS tab1_data,

2 3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID PLAN_HASH_VALUE CHILD_NUMBER

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

1km5kczcgr0fr 2672205743 1

1km5kczcgr0fr 2672205743 2

SQL> ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> select sql_id, plan_hash_value, child_number from v$sql where sql_id='1km5kczcgr0fr';

SQL_ID PLAN_HASH_VALUE CHILD_NUMBER

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

1km5kczcgr0fr 1599395313 0

1km5kczcgr0fr 2672205743 1

1km5kczcgr0fr 2672205743 2

SQL> connect / as sysdba

Connected.

SQL> VARIABLE cnt NUMBER

SQL> EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '1km5kczcgr0fr',plan_hash_value => '1599395313');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0ud64ac9be SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

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

SQL handle: SQL_6c4c6680810dd01a

SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

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

Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

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

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

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

Plan name: SQL_PLAN_6sm36h20hvn0ud64ac9be Plan id: 3595225534

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

Plan rows: From dictionary

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

Plan hash value: 1599395313

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| * 1 | HASH JOIN | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 3 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

3 - access("A"."CODE"='ONE')

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

65 rows selected.

SQL> VARIABLE cnt NUMBER

SQL> EXECUTE :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE( SQL_HANDLE => 'SQL_6c4c6680810dd01a',plan_name => 'SQL_PLAN_6sm36h20hvn0u55a25f73');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0ud64ac9be SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

SQL> conn ludo/ludo

Connected.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 2

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 1599395313

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

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

| 0 | SELECT STATEMENT | | 1 | | 24 |00:00:00.01 | 71 | | | |

|* 1 | HASH JOIN | | 1 | 25 | 24 |00:00:00.01 | 71 | 1888K| 1888K| 1921K (0)|

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 10001 |00:00:00.01 | 62 | | | |

|* 3 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 10001 |00:00:00.01 | 37 | | | |

| 4 | TABLE ACCESS FULL | TAB2 | 1 | 100 | 100 |00:00:00.01 | 9 | | | |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

3 - access("A"."CODE"='ONE')

Note

-----

- SQL plan baseline SQL_PLAN_6sm36h20hvn0ud64ac9be used for this statement

28 rows selected.

To recap:

The capture process will always load the original plan
It is possible to decide to load manually the original one or the alternative one (if resolved)
Using automatic capture is a bad idea

HTH

—

Ludo

How Adaptive Plans work with SQL Plan Baselines?

Posted on November 18, 2016 by Ludovico

Disclaimer: after writing this post (but before publishing it) I have seen that other people already blogged about it, so I am ashamed of publishing it anyway… but that’s blogger’s life 🙂

Wednesday I have got a nice question after my presentation about Adaptive Features at the DOAG16 conference:

What happens when you load an adaptive plan in a SQL Plan Baseline?
Does it load only the final plan or does it load the whole plan including the inactive operations? Will the plan be evaluated again using the inflection point?

I have decided to do some tests in order to give the best possible answer. I did not spend the time to rethink about producing an adaptive plan. Tim Hall already did an excellent test case to create and alter an adaptive plan in his blog, so I have reused massively most of its code. Thanks Tim :-).

I will not post all the code (please find it in Tim’s post), I will go straight to the plans.

First: I have an adaptive plan that resolves to NESTED LOOPS:

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ 
  2    a.data AS tab1_data,
  4    b.data AS tab2_data
  5  FROM   tab1 a
  6         JOIN tab2 b ON b.tab1_id = a.id
  7  WHERE  a.code = 'ONE';

...
 
30 rows selected.

SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a
    JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-----------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |       |       |     3 (100)|          |
|- *  1 |  HASH JOIN                              |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                          |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR                |               |       |       |            |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
|-    9 |   TABLE ACCESS FULL                     | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

4 b.data AS tab2_data

5 FROM tab1 a

6 JOIN tab2 b ON b.tab1_id = a.id

7 WHERE a.code = 'ONE';

...

30 rows selected.

SQL> SET LINESIZE 200 PAGESIZE 100

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4r3harjun4dvz, child number 0

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

SELECT a.data AS tab1_data, b.data AS tab2_data FROM tab1 a

JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|- * 1 | HASH JOIN | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

|- 4 | STATISTICS COLLECTOR | | | | | |

| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |

| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

|- 9 | TABLE ACCESS FULL | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

6 - access("A"."CODE"='ONE')

7 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

33 rows selected.

Second: I load the plan (lazy way: using baseline capture at session level)

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r 
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                          SQL_TEXT                                   ENA ACC FIX
-------------------- ---------------------------------- ------------------------------------------ --- --- ---
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73     SELECT /*+ GATHER_PLAN_STATISTICS */       YES YES NO
                                                                       a.data AS tab1_data,
                                                                       b.data A

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

Third: re-run the statement and check the plan

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 3
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement


30 rows selected.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 3

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement

30 rows selected.

It does not look adaptive, but I can also check from the function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_6c4c6680810dd01a
SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
          a.id WHERE  a.code = 'ONE'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |
|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |
|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_6c4c6680810dd01a

SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

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

Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

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

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

Again, despite in the Note section it says it is adaptive, it does not look like an adaptive plan.

Can I trust this information? Of course I did not and tried to check the plan with and without baseline after changing the rows to force a plan switch to HJ (again taking Tim’s example):

SQL> INSERT /*+ APPEND */ INTO tab1
  2   SELECT tab1_seq.nextval,
  2         'ONE',
  4         level
  5  FROM   dual
  6  CONNECT BY level <= 10000;

  10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 1599395313

------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |      1 |        |     30 |00:00:00.01 |     142 |       |       |          |
|  *  1 |  HASH JOIN                              |               |      1 |     25 |     30 |00:00:00.01 |     142 |  2261K|  2261K| 2180K (0)|
|-    2 |   NESTED LOOPS                          |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |
|-    3 |    NESTED LOOPS                         |               |      1 |     25 |  20001 |00:00:00.01 |     124 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |  20001 |00:00:00.01 |     124 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     124 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      74 |       |       |          |
|- *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|     9 |   TABLE ACCESS FULL                     | TAB2          |      1 |     25 |    100 |00:00:00.01 |      18 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


34 rows selected.

SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 2
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |     30 |00:00:00.01 |   30889 |
|   1 |  NESTED LOOPS                         |               |      1 |     25 |     30 |00:00:00.01 |   30889 |
|   2 |   NESTED LOOPS                        |               |      1 |     25 |     30 |00:00:00.01 |   30886 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  20001 |00:00:00.01 |     125 |
|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  20001 |00:00:00.01 |      75 |
|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |  20001 |     25 |     30 |00:00:00.02 |   30761 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |     30 |     25 |     30 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement


30 rows selected.

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

SQL> INSERT /*+ APPEND */ INTO tab1

2 SELECT tab1_seq.nextval,

2 'ONE',

4 level

5 FROM dual

6 CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 0

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 1599395313

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

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

| 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 142 | | | |

| * 1 | HASH JOIN | | 1 | 25 | 30 |00:00:00.01 | 142 | 2261K| 2261K| 2180K (0)|

|- 2 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | |

|- 3 | NESTED LOOPS | | 1 | 25 | 20001 |00:00:00.01 | 124 | | | |

|- 4 | STATISTICS COLLECTOR | | 1 | | 20001 |00:00:00.01 | 124 | | | |

| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 124 | | | |

| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 74 | | | |

|- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 25 | 0 |00:00:00.01 | 0 | | | |

|- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | | | |

| 9 | TABLE ACCESS FULL | TAB2 | 1 | 25 | 100 |00:00:00.01 | 18 | | | |

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

Predicate Information (identified by operation id):

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

1 - access("B"."TAB1_ID"="A"."ID")

6 - access("A"."CODE"='ONE')

7 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

34 rows selected.

SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1km5kczcgr0fr, child number 2

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

SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 1 | | 30 |00:00:00.01 | 30889 |

| 1 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30889 |

| 2 | NESTED LOOPS | | 1 | 25 | 30 |00:00:00.01 | 30886 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 20001 |00:00:00.01 | 125 |

|* 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 20001 |00:00:00.01 | 75 |

|* 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 20001 | 25 | 30 |00:00:00.02 | 30761 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 30 | 25 | 30 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- SQL plan baseline SQL_PLAN_6sm36h20hvn0u55a25f73 used for this statement

30 rows selected.

After changing the rows:

when I do not use the baseline, the plan resolves to HASH JOIN
when I use it, the baseline forces to NESTED LOOPS.

So the plan in the baseline is not adaptive and it forces to what has been loaded. Is it the final plan or the original one? I have to capture it again to see if a new baseline appears:

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED
-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */                                             YES       YES       NO
                                                           a.data AS tab1_data,
                                                           b.data A

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

A new baseline does not appear, so it looks that the original plan is considered by the capture process and not the resolved one! To be 100% sure, let’s try to drop the existing one and redo the test:

SQL> connect / as sysdba
Connected
SQL> DECLARE
  2   v_dropped_plans number;
  3 BEGIN
  4   v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  5      sql_handle => 'SQL_6c4c6680810dd01a'
  6 );
  7 END;
  8 /
PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> conn ludo/ludo
Connected.
SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6  WHERE  a.code = 'ONE';

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL> r
  1  SELECT /*+ GATHER_PLAN_STATISTICS */
  2         a.data AS tab1_data,
  3         b.data AS tab2_data
  4  FROM   tab1 a
  5         JOIN tab2 b ON b.tab1_id = a.id
  6* WHERE  a.code = 'ONE'

 TAB1_DATA  TAB2_DATA
---------- ----------
...

30 rows selected.

SQL>  ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                                                         ENABLED   ACCEPTED  FIXED
-------------------- ------------------------------ -------------------------------------------------------------------------------- --------- --------- ---------
SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */                                             YES       YES       NO
                                                           a.data AS tab1_data,
                                                           b.data A

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_6c4c6680810dd01a
SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
            b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
          a.id WHERE  a.code = 'ONE'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73         Plan id: 1436704627
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |               |    25 |   425 |     3   (0)| 00:00:01 |
|     1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)| 00:00:01 |
|     6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

100

101

102

103

SQL> connect / as sysdba

Connected

SQL> DECLARE

2 v_dropped_plans number;

3 BEGIN

4 v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

5 sql_handle => 'SQL_6c4c6680810dd01a'

6 );

7 END;

8 /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL> conn ludo/ludo

Connected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6 WHERE a.code = 'ONE';

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> r

1 SELECT /*+ GATHER_PLAN_STATISTICS */

2 a.data AS tab1_data,

3 b.data AS tab2_data

4 FROM tab1 a

5 JOIN tab2 b ON b.tab1_id = a.id

6* WHERE a.code = 'ONE'

TAB1_DATA TAB2_DATA

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

...

30 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED

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

SQL_6c4c6680810dd01a SQL_PLAN_6sm36h20hvn0u55a25f73 SELECT /*+ GATHER_PLAN_STATISTICS */ YES YES NO

a.data AS tab1_data,

b.data A

SQL> select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_6c4c6680810dd01a', format=>'+adaptive'));

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_6c4c6680810dd01a

SQL text: SELECT /*+ GATHER_PLAN_STATISTICS */ a.data AS tab1_data,

b.data AS tab2_data FROM tab1 a JOIN tab2 b ON b.tab1_id =

a.id WHERE a.code = 'ONE'

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

Plan name: SQL_PLAN_6sm36h20hvn0u55a25f73 Plan id: 1436704627

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

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

Plan hash value: 2672205743

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

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

| 0 | SELECT STATEMENT | | 25 | 425 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 25 | 425 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 11 | 2 (0)| 00:00:01 |

| * 4 | INDEX RANGE SCAN | TAB1_CODE | 1 | | 1 (0)| 00:00:01 |

| * 5 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 25 | | 0 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 25 | 150 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."CODE"='ONE')

5 - access("B"."TAB1_ID"="A"."ID")

Note

-----

- this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.

So, despite the fact that I have an adaptive plan that switches from NL to HJ, only the NESTED LOOPS operations are captured in the baseline, I can infer the only the original plan is loaded as SQL Plan Baseline.

References:

How to fix CPU usage problem in 12c due to DBMS_FEATURE_AWR

Posted on June 6, 2016 by Ludovico

I love my job because I always have suprises. This week’s surprise has been another problem related to SQL Plan Directives in 12c. Because it is a common problem that potentially affects ALL the customers, I am glad to share the solution on my blog 😀

Symptom of the problem: High CPU usage on the server

My customer’s DBA team has spotted a consistent high CPU utilisation on its servers:

Everyday, at the same time, and for 20-40 minutes, the servers hosting the Oracle databases run literally out of CPU.

Troubleshooting

Ok, it would be too easy to give the solution now. If you cannot wait, jump at the end of this post. But what I like more is to explain how I came to it.

First, I gave a look at the processes consuming CPU. Most of the servers have many consolidated databases on them. Surprisingly, this is what I have found:

It seems that the source of the problem is not a single database, but all of them. Isn’t it? And I see another pattern here: the CPU usage comes always from the [m001] process, so it is not related to a user process.

My customer has Diagnostic Pack so it is easy to go deeper, but you can get the same result with other free tools like s-ash, statspack and snapper. However, this is what I have found in the Instance Top Activity:

Ok, everything comes from a single query with sql_id auyf8px9ywc6j. This is the full sql_text:

(SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER ,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP ,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE
FROM DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST
WHERE SN.BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME ) ,DELTA_DATA AS
(SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE + (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS DELTA_TIME
FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2 ,SNAP_RANGES SR
WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID = SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM2.SNAP_ID = SR.MAX_SNAP AND TM2.STAT_ID = SR.STAT_ID )
SELECT STAT_NAME ,ROUND(SUM(DELTA_TIME/1000000),2) AS SECS
FROM DELTA_DATA GROUP BY STAT_NAME

(SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER ,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP ,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE

FROM DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST

WHERE SN.BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME ) ,DELTA_DATA AS

(SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE + (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS DELTA_TIME

FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2 ,SNAP_RANGES SR

WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID = SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.INSTANCE_NUMBER = SR.INSTANCE_NUMBER AND TM2.SNAP_ID = SR.MAX_SNAP AND TM2.STAT_ID = SR.STAT_ID )

SELECT STAT_NAME ,ROUND(SUM(DELTA_TIME/1000000),2) AS SECS

FROM DELTA_DATA GROUP BY STAT_NAME

It looks like something made by a DBA, but it comes from the MMON.

Looking around, it seems closely related to two PL/SQL calls that I could find in the SQL Monitor and that systematically fail every day:

DBMS_FEATURE_AWR function calls internally the SQL auyf8px9ywc6j.

The MOS does not know anything about that query, but the internet does:

Oh no, not Franck again! He always discovers new stuff and blogs about it before I do 🙂

In his blog post, he points out that the query fails because of error ORA-12751 (resource plan limiting CPU usage) and that it is a problem of Adaptive Dynamic Sampling. Is it true?

What I like to do when I have a problematic sql_id, is to run sqld360 from Mauro Pagano, but the resulting zip file does not contain anything useful, because actually there are no executions and no plans.

SQL> select sql_id,  executions, loads, cpu_time from v$sqlstats where sql_id='auyf8px9ywc6j';

SQL_ID        EXECUTIONS      LOADS   CPU_TIME
------------- ---------- ---------- ----------
auyf8px9ywc6j          0         11          0

SQL> select sql_id,  child_number from v$sql where sql_id='auyf8px9ywc6j';

no rows selected

SQL>

SQL> select sql_id, executions, loads, cpu_time from v$sqlstats where sql_id='auyf8px9ywc6j';

SQL_ID EXECUTIONS LOADS CPU_TIME

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

auyf8px9ywc6j 0 11 0

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

no rows selected

SQL>

During the execution of the statement (or better, during the period with high CPU usage), there is an entry in v$sql, but no plans associated:

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

SQL_ID        CHILD_NUMBER
------------- ------------
auyf8px9ywc6j            0

SQL> select * from table (dbms_xplan.display_cursor('auyf8px9ywc6j',0, 'ALL +NOTE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auyf8px9ywc6j, child number 0

WITH SNAP_RANGES AS (SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER
,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP
,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS
MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE FROM
DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST WHERE SN.BEGIN_INTERVAL_TIME >
TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND
SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY
SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME )
,DELTA_DATA AS (SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE
WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE
+ (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS
DELTA_TIME FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2
,SNAP_RANGES SR WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER =
SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID =
SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.

NOTE: cannot fetch plan for SQL_ID: auyf8px9ywc6j, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


22 rows selected.

SQL> select sql_id, child_number from v$sql where sql_id='auyf8px9ywc6j';

SQL_ID CHILD_NUMBER

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

auyf8px9ywc6j 0

SQL> select * from table (dbms_xplan.display_cursor('auyf8px9ywc6j',0, 'ALL +NOTE'));

PLAN_TABLE_OUTPUT

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

SQL_ID auyf8px9ywc6j, child number 0

WITH SNAP_RANGES AS (SELECT /*+ FULL(ST) */ SN.DBID ,SN.INSTANCE_NUMBER

,SN.STARTUP_TIME ,ST.STAT_ID ,ST.STAT_NAME ,MIN(SN.SNAP_ID) AS MIN_SNAP

,MAX(SN.SNAP_ID) AS MAX_SNAP ,MIN(CAST(BEGIN_INTERVAL_TIME AS DATE)) AS

MIN_DATE ,MAX(CAST(END_INTERVAL_TIME AS DATE)) AS MAX_DATE FROM

DBA_HIST_SNAPSHOT SN ,WRH$_STAT_NAME ST WHERE SN.BEGIN_INTERVAL_TIME >

TRUNC(SYSDATE) - 7 AND SN.END_INTERVAL_TIME < TRUNC(SYSDATE) AND

SN.DBID = ST.DBID AND ST.STAT_NAME IN ('DB time', 'DB CPU') GROUP BY

SN.DBID,SN.INSTANCE_NUMBER,SN.STARTUP_TIME,ST.STAT_ID,ST.STAT_NAME )

,DELTA_DATA AS (SELECT SR.DBID ,SR.INSTANCE_NUMBER ,SR.STAT_NAME ,CASE

WHEN SR.STARTUP_TIME BETWEEN SR.MIN_DATE AND SR.MAX_DATE THEN TM1.VALUE

+ (TM2.VALUE - TM1.VALUE) ELSE (TM2.VALUE - TM1.VALUE) END AS

DELTA_TIME FROM WRH$_SYS_TIME_MODEL TM1 ,WRH$_SYS_TIME_MODEL TM2

,SNAP_RANGES SR WHERE TM1.DBID = SR.DBID AND TM1.INSTANCE_NUMBER =

SR.INSTANCE_NUMBER AND TM1.SNAP_ID = SR.MIN_SNAP AND TM1.STAT_ID =

SR.STAT_ID AND TM2.DBID = SR.DBID AND TM2.

NOTE: cannot fetch plan for SQL_ID: auyf8px9ywc6j, CHILD_NUMBER: 0

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan is no longer in cursor cache (check v$sql_plan)

22 rows selected.

And this is very likely because the statement is still parsing, and all the time is due to the Dynamic Sampling. But because the plan is not there yet, I cannot check it in the DBMS_XPLAN.DISPLAY_CURSOR.

I decided then to trace it with those two statements:

SQL> alter system set events 'sql_trace [sql:auyf8px9ywc6j]';

SQL> alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:auyf8px9ywc6j]';

SQL> alter system set events 'sql_trace [sql:auyf8px9ywc6j]';

SQL> alter system set events 'trace[rdbms.SQL_Optimizer.*][sql:auyf8px9ywc6j]';

At the next execution I see indeed the Adaptive Dynamic Sampling in the trace file, the errror due to the exhausted CPU in the resource plan, and the directives that caused the Adaptive Dynamic Sampling:

=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$3877D5D0 (#3)
Applicable DS directives:
   dirid = 17707367266596005344, state = 5, flags = 1, loc = 1 {CJ(8694)[1, 2]}
   dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}
   dirid = 10027833930063681981, state = 1, flags = 5, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]; (8436)[1, 5]; (8436)[1, 5]}
Checking valid directives for the query block
  SPD: Directive valid: dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}
  SPD: Return code in qosdDSDirSetup: EXISTS, estType = GROUP_BY
  SPD: Return code in qosdDSDirSetup: NODIR, estType = HAVING
  SPD: Return code in qosdDSDirSetup: NODIR, estType = QUERY_BLOCK

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

SPD: BEGIN context at query block level

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

Query Block SEL$3877D5D0 (#3)

Applicable DS directives:

dirid = 17707367266596005344, state = 5, flags = 1, loc = 1 {CJ(8694)[1, 2]}

dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}

dirid = 10027833930063681981, state = 1, flags = 5, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]; (8436)[1, 5]; (8436)[1, 5]}

Checking valid directives for the query block

SPD: Directive valid: dirid = 17748238338555778238, state = 5, flags = 1, loc = 4 {(8694)[2, 3, 4]; (8460)[2, 3]}

SPD: Return code in qosdDSDirSetup: EXISTS, estType = GROUP_BY

SPD: Return code in qosdDSDirSetup: NODIR, estType = HAVING

SPD: Return code in qosdDSDirSetup: NODIR, estType = QUERY_BLOCK

PARSING IN CURSOR #139834781881608 len=1106 dep=4 uid=0 oct=3 lid=0 tim=3349661181783 hv=4280474888 ad='95770310' sqlid='8w3h8fvzk5r88'
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery")  */ 1 AS C1 FROM (SELECT /*+ FULL ("ST") */ "WRM$_SNAPSHOT"."DBID" "DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER" "INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME" "STARTUP_TIME","ST"."STAT_ID" "STAT_ID","ST"."STAT_NAME" "STAT_NAME",MIN("WRM$_SNAPSHOT"."SNAP_ID") "MIN_SNAP",MAX("WRM$_SNAPSHOT"."SNAP_ID") "MAX_SNAP",MIN(CAST("WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME" AS DATE)) "MIN_DATE",MAX(CAST("WRM$_SNAPSHOT"."END_INTERVAL_TIME" AS DATE)) "MAX_DATE" FROM SYS."WRM$_SNAPSHOT" "WRM$_SNAPSHOT","WRH$_STAT_NAME" "ST" WHERE "WRM$_SNAPSHOT"."DBID"="ST"."DBID" AND ("ST"."STAT_NAME"='DB CPU' OR "ST"."STAT_NAME"='DB time') AND "WRM$_SNAPSHOT"."STATUS"=0 AND "WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME">TRUNC(SYSDATE@!)-7 AND "WRM$_SNAPSHOT"."END_INTERVAL_TIME"<TRUNC(SYSDATE@!) GROUP BY "WRM$_SNAPSHOT"."DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME","ST"."STAT_ID","ST"."STAT_NAME") "VW_DIS_1") innerQuery
END OF STMT
...
>> Query Blk Card adjusted from 3.000000 to 2.000000 due to adaptive dynamic sampling

PARSING IN CURSOR #139834781881608 len=1106 dep=4 uid=0 oct=3 lid=0 tim=3349661181783 hv=4280474888 ad='95770310' sqlid='8w3h8fvzk5r88'

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM (SELECT /*+ FULL ("ST") */ "WRM$_SNAPSHOT"."DBID" "DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER" "INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME" "STARTUP_TIME","ST"."STAT_ID" "STAT_ID","ST"."STAT_NAME" "STAT_NAME",MIN("WRM$_SNAPSHOT"."SNAP_ID") "MIN_SNAP",MAX("WRM$_SNAPSHOT"."SNAP_ID") "MAX_SNAP",MIN(CAST("WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME" AS DATE)) "MIN_DATE",MAX(CAST("WRM$_SNAPSHOT"."END_INTERVAL_TIME" AS DATE)) "MAX_DATE" FROM SYS."WRM$_SNAPSHOT" "WRM$_SNAPSHOT","WRH$_STAT_NAME" "ST" WHERE "WRM$_SNAPSHOT"."DBID"="ST"."DBID" AND ("ST"."STAT_NAME"='DB CPU' OR "ST"."STAT_NAME"='DB time') AND "WRM$_SNAPSHOT"."STATUS"=0 AND "WRM$_SNAPSHOT"."BEGIN_INTERVAL_TIME">TRUNC(SYSDATE@!)-7 AND "WRM$_SNAPSHOT"."END_INTERVAL_TIME"<TRUNC(SYSDATE@!) GROUP BY "WRM$_SNAPSHOT"."DBID","WRM$_SNAPSHOT"."INSTANCE_NUMBER","WRM$_SNAPSHOT"."STARTUP_TIME","ST"."STAT_ID","ST"."STAT_NAME") "VW_DIS_1") innerQuery

END OF STMT

...

>> Query Blk Card adjusted from 3.000000 to 2.000000 due to adaptive dynamic sampling

*** KEWUXS - encountered error: (ORA-12751: violation du temps UC ou des règles relatives au temps d'exécution
ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 14
ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 92
ORA-06512: à ligne 1
ORA-06512: à "SYS.DBMS_SQL", ligne 1707
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 312
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 522
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 694
ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 791
ORA-06512: à ligne 1
)

*** KEWUXS - encountered error: (ORA-12751: violation du temps UC ou des règles relatives au temps d'exécution

ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 14

ORA-06512: à "SYS.DBMS_FEATURE_AWR", ligne 92

ORA-06512: à ligne 1

ORA-06512: à "SYS.DBMS_SQL", ligne 1707

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 312

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 522

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 694

ORA-06512: à "SYS.DBMS_FEATURE_USAGE_INTERNAL", ligne 791

ORA-06512: à ligne 1

)

So, there are some SQL Plan Directives that force the CBO to run ADS for this query.

SQL> select TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED, LAST_USED from dba_sql_plan_directives where directive_id in (10027833930063681981, 17707367266596005344, 17748238338555778238);

TYPE             ENA STATE      AUT REASON                               CREATED
---------------- --- ---------- --- ------------------------------------ ---------------------------------------------------------------------------
LAST_MODIFIED                                                               LAST_USED
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     03-JUN-16 02.10.41.000000 PM
03-JUN-16 04.14.32.000000 PM

DYNAMIC_SAMPLING YES USABLE     YES SINGLE TABLE CARDINALITY MISESTIMATE 27-MAR-16 09.01.20.000000 AM
17-APR-16 09.13.01.000000 AM                                                17-APR-16 09.13.01.000000000 AM

DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     13-FEB-16 06.07.36.000000 AM
27-FEB-16 06.03.09.000000 AM                                                03-JUN-16 02.10.41.000000000 PM

SQL> select TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED, LAST_USED from dba_sql_plan_directives where directive_id in (10027833930063681981, 17707367266596005344, 17748238338555778238);

TYPE ENA STATE AUT REASON CREATED

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

LAST_MODIFIED LAST_USED

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

DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 03-JUN-16 02.10.41.000000 PM

03-JUN-16 04.14.32.000000 PM

DYNAMIC_SAMPLING YES USABLE YES SINGLE TABLE CARDINALITY MISESTIMATE 27-MAR-16 09.01.20.000000 AM

17-APR-16 09.13.01.000000 AM 17-APR-16 09.13.01.000000000 AM

DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 13-FEB-16 06.07.36.000000 AM

27-FEB-16 06.03.09.000000 AM 03-JUN-16 02.10.41.000000000 PM

This query touches three tables, so instead of relying on the DIRECTIVE_IDs, it’s better to get the directives by object name:

SQL> r
  1  select distinct d.directive_id, TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED
  2  from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on
  3*     (d.directive_id=o.directive_id) where o.owner='SYS' and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT')

DIRECTIVE_ID TYPE             ENA STATE      AUT REASON                               CREATED
------------ ---------------- --- ---------- --- ------------------------------------ ---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
  8.8578E+18 DYNAMIC_SAMPLING YES USABLE     YES JOIN CARDINALITY MISESTIMATE         14-FEB-16 08.11.29.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7748E+19 DYNAMIC_SAMPLING YES USABLE     YES GROUP BY CARDINALITY MISESTIMATE     19-MAR-16 02.15.17.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7170E+19 DYNAMIC_SAMPLING YES USABLE     YES JOIN CARDINALITY MISESTIMATE         14-FEB-16 08.11.29.000000 AM
06-JUN-16 01.57.35.000000 PM

  1.7707E+19 DYNAMIC_SAMPLING YES USABLE     YES SINGLE TABLE CARDINALITY MISESTIMATE 13-MAR-16 08.04.38.000000 AM
06-JUN-16 01.57.35.000000 PM

SQL> r

1 select distinct d.directive_id, TYPE, ENABLED, STATE, AUTO_DROP, REASON, CREATED, LAST_MODIFIED

2 from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on

3* (d.directive_id=o.directive_id) where o.owner='SYS' and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT')

DIRECTIVE_ID TYPE ENA STATE AUT REASON CREATED

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

LAST_MODIFIED

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

8.8578E+18 DYNAMIC_SAMPLING YES USABLE YES JOIN CARDINALITY MISESTIMATE 14-FEB-16 08.11.29.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7748E+19 DYNAMIC_SAMPLING YES USABLE YES GROUP BY CARDINALITY MISESTIMATE 19-MAR-16 02.15.17.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7170E+19 DYNAMIC_SAMPLING YES USABLE YES JOIN CARDINALITY MISESTIMATE 14-FEB-16 08.11.29.000000 AM

06-JUN-16 01.57.35.000000 PM

1.7707E+19 DYNAMIC_SAMPLING YES USABLE YES SINGLE TABLE CARDINALITY MISESTIMATE 13-MAR-16 08.04.38.000000 AM

06-JUN-16 01.57.35.000000 PM

Solution

At this point, the solution is the same already pointed out in one of my previous blog posts: disable the directives individually!

BEGIN
  FOR rec in (select d.directive_id as did 
    from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on
    (d.directive_id=o.directive_id) where o.owner='SYS'
      and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT'))
  LOOP
    DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');
    DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'AUTO_DROP','NO');
  END LOOP;
END;
/

BEGIN

FOR rec in (select d.directive_id as did

from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on

(d.directive_id=o.directive_id) where o.owner='SYS'

and o.object_name in ('WRH$_SYS_TIME_MODEL','WRH$_STAT_NAME','WRM$_SNAPSHOT'))

LOOP

DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');

DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'AUTO_DROP','NO');

END LOOP;

END;

This very same PL/SQL block must be run on ALL the 12c databases affected by this Adaptive Dynamic Sampling problem on the sql_id auyf8px9ywc6j.

If you have just migrated the database to 12c, it would make even more sense to programmatically “inject” the disabled SQL Plan Directives into every freshly created or upgraded 12c database (until Oracle releases a patch for this non-bug).

-- export from a source where the directives exist and have been disabled
SET SERVEROUTPUT ON
DECLARE
  my_list  DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab();
  dir_cnt  NUMBER;
BEGIN
  DBMS_SPD.CREATE_STGTAB_DIRECTIVE  (table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM' );
  my_list.extend(3);
 
  -- TAB table
  my_list(1).owner := 'SYS';
  my_list(1).object_name := 'WRH$_SYS_TIME_MODEL';
  my_list(1).object_type := 'TABLE';
  my_list(2).owner := 'SYS';
  my_list(2).object_name := 'WRH$_STAT_NAME';
  my_list(2).object_type := 'TABLE';
  my_list(3).owner := 'SYS';
  my_list(3).object_name := 'WRM$_SNAPSHOT';
  my_list(3).object_type := 'TABLE';

  dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM', obj_list => my_list);
   DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt);
END;
/

expdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=expdp_AUYF8PX9YWC6J_DIRECTIVES.log tables=system.AUYF8PX9YWC6J_DIRECTIVES

-- import into the freshly upgraded/created 12c database
impdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=impdp_AUYF8PX9YWC6J_DIRECTIVES.log

SELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM') FROM DUAL;

-- export from a source where the directives exist and have been disabled

SET SERVEROUTPUT ON

DECLARE

my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab();

dir_cnt NUMBER;

BEGIN

DBMS_SPD.CREATE_STGTAB_DIRECTIVE (table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM' );

my_list.extend(3);

-- TAB table

my_list(1).owner := 'SYS';

my_list(1).object_name := 'WRH$_SYS_TIME_MODEL';

my_list(1).object_type := 'TABLE';

my_list(2).owner := 'SYS';

my_list(2).object_name := 'WRH$_STAT_NAME';

my_list(2).object_type := 'TABLE';

my_list(3).owner := 'SYS';

my_list(3).object_name := 'WRM$_SNAPSHOT';

my_list(3).object_type := 'TABLE';

dir_cnt := DBMS_SPD.PACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM', obj_list => my_list);

DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt);

END;

expdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=expdp_AUYF8PX9YWC6J_DIRECTIVES.log tables=system.AUYF8PX9YWC6J_DIRECTIVES

-- import into the freshly upgraded/created 12c database

impdp directory=data_pump_dir dumpfile=AUYF8PX9YWC6J_DIRECTIVES.dmp logfile=impdp_AUYF8PX9YWC6J_DIRECTIVES.log

SELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE(table_name => 'AUYF8PX9YWC6J_DIRECTIVES', table_owner=> 'SYSTEM') FROM DUAL;

It comes without saying that the next execution has been very quick, consuming almost no CPU and without using ADS.

HTH

—

Ludovico