DBA survival BLOG

DBA stuff and 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

Oracle Active Data Guard and Global Data Services in Action!

Posted on December 4, 2015 by Ludovico

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

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

Credits

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

Slides

Demo recording

Demo script

clear

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

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

db

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

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


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


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


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

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


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

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

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


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

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

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

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

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

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

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


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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

clear

function db {

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

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

}

function gsm {

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

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

}

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

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "next: GSM config"

read -p ""

gsm

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

echo "GDS COMMAND:

config"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config

exit

EOF

echo "next: ADD GDSPOOL"

read -p ""

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

echo "GDS COMMAND:

add gdspool -gdspool sales"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add gdspool -gdspool sales

exit

EOF

echo "next: ADD BROKERCONFIG"

read -p ""

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

echo "GDS COMMAND:

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

gdsctl <<EOF

connect gsm_admin/password1@gsm1

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

exit

EOF

echo "next: config databases"

read -p ""

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

echo "GDS COMMAND:

config database"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config database

exit

EOF

echo "next: modify databases"

read -p ""

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

echo "GDS COMMAND:

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

gdsctl <<EOF

connect gsm_admin/password1@gsm1

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

config database

exit

EOF

echo "next: add service read/write"

read -p ""

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

echo "GDS COMMAND:

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

start service -service gsales_rw

services"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

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

start service -service gsales_rw

services

exit

EOF

echo "next: ADD SERVICE R/O"

read -p ""

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

echo "GDS COMMAND:

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

start service -service gsales_ro

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

start service -service gsales_ro

services

exit

EOF

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

read -p ""

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

dgmgrl -echo sys/password1@oltp_de <<EOF

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

EOF

echo "next: gds services"

read -p ""

gsm

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

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

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

read -p ""

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

dgmgrl -echo sys/password1@oltp_de <<EOF

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

EOF

echo "next: gds services"

read -p ""

gsm

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

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

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

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: start apply ch1 and ch2"

read -p ""

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

dgmgrl -echo sys/password1@oltp_de <<EOF

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

EOF

echo "sleeping 5"

sleep 5

dgmgrl -echo sys/password1@oltp_de <<EOF

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

EOF

echo "next: gds services"

read -p ""

gsm

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

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

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

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

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

read -p ""

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

dgmgrl -echo sys/password1@oltp_de <<EOF

validate database oltp_ch1;

EOF

echo "next: switchover"

read -p ""

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

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_ch1;

EOF

echo "next: gds services"

read -p ""

And the script to revert the demo:

clear

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

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

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

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

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

echo "DEMO reverted."
read -p ""

clear

function db {

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

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

}

function gsm {

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

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

}

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_de;

EOF

gsm

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

gdsctl <<EOF

connect gsm_admin/password1@gsm1

stop service -service gsales_ro

stop service -service gsales_rw

remove service -service gsales_ro

remove service -service gsales_rw

remove brokerconfig

remove gdspool -gdspool sales

config

exit

EOF

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "DEMO reverted."

read -p ""

Cheers

—

Ludovico

My Collaborate 14 articles about Active Data Guard 12c and Policy Managed Databases

Posted on January 20, 2015 by Ludovico

After almost 1 year, I’ve decided to publish these articles on my Slideshare account. You may have already seen them in the IOUG Collaborate 14 conference content or in the SOUG Newsletter 2014/4. Nothing really new, but I hope you’ll still enjoy them.

Cheers

—

Ludo

Cloning a PDB with ASM and Data Guard (no ADG) without network transfer

Posted on December 16, 2014 by Ludovico

Ok, if you’re reading this post, you may want to read also the previous one that explains something more about the problem.

Briefly said, if you have a CDB running on ASM in a MAA architecture and you do not have Active Data Guard, when you clone a PDB you have to “copy” the datafiles somehow on the standby. The only solution offered by Oracle (in a MOS Note, not in the documentation) is to restore the PDB from the primary to the standby site, thus transferring it over the network. But if you have a huge PDB this is a bad solution because it impacts your network connectivity. (Note: ending up with a huge PDB IMHO can only be caused by bad consolidation. I do not recommend to consolidate huge databases on Multitenant).

So I’ve worked out another solution, that still has many defects and is almost not viable, but it’s technically interesting because it permits to discover a little more about Multitenant and Data Guard.

The three options

At the primary site, the process is always the same: Oracle copies the datafiles of the source, and it modifies the headers so that they can be used by the new PDB (so it changes CON_ID, DBID, FILE#, and so on).

On the standby site, by opposite, it changes depending on the option you choose:

Option 1: Active Data Guard

If you have ADG, the ADG itself will take care of copying the datafile on the standby site, from the source standby pdb to the destination standby pdb. Once the copy is done, the MRP0 will continue the recovery. The modification of the header block of the destination PDB is done by the MRP0 immediately after the copy (at least this is what I understand).

ADG_PDB_copy

Option 2: No Active Data Guard, but STANDBYS=none

In this case, the copy on the standby site doesn’t happen, and the recovery process just add the entry of the new datafiles in the controlfile, with status OFFLINE and name UNKNOWNxxx. However, the source file cannot be copied anymore, because the MRP0 process will expect to have a copy of the destination datafile, not the source datafile. Also, any tentative of restore of the datafile 28 (in this example) will give an error because it does not belong to the destination PDB. So the only chance is to restore the destination PDB from the primary.

Option 3: No Active Data Guard, no STANDBYS=none

This is the case that I want to explain actually. Without the flag STANDBYS=none, the MRP0 process will expect to change the header of the new datafile, but because the file does not exist yet, the recovery process dies.
We can then copy it manually from the source standby pdb, and restart the recovery process, that will change the header. This process needs to be repeated for each datafile. (that’s why it’s not a viable solution, right now).

Let’s try it together:

The Environment

Primary

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBATL                         CDBATL_2
CDBATL                         CDBATL_1

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME INSTANCE_NAME

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

CDBATL CDBATL_2

CDBATL CDBATL_1

Standby

07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBGVA                         CDBGVA_1
CDBGVA                         CDBGVA_2

07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME INSTANCE_NAME

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

CDBGVA CDBGVA_1

CDBGVA CDBGVA_2

The current user PDB (any resemblance to real people is purely coincidental 😉 #haveUSeenMaaz):

08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';

OPEN_MODE  NAME
---------- ------------------------------
OPEN       MAAZ
OPEN       MAAZ

08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';

OPEN_MODE NAME

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

OPEN MAAZ

Cloning the PDB on the primary

First, make sure that the source PDB is open read-only

08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;

Pluggable database altered.

08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;

OPEN_MODE  NAME
---------- ------------------------------
READ ONLY  MAAZ
READ ONLY  MAAZ

08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;

Pluggable database altered.

08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;

OPEN_MODE NAME

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

READ ONLY MAAZ

Then, clone the PDB on the primary without the clause STANDBYS=NONE:

08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;

Pluggable database created.

08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;

Pluggable database created.

Review the clone on the Standby

At this point, on the standby the alert log show that the SYSTEM datafile is missing, and the recovery process stops.

Mon Dec 15 17:46:11 2014
Recovery created pluggable database LUDO
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
Recovery was unable to create the file as:
'+DATA'
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
Managed Standby Recovery not using Real Time Apply
Mon Dec 15 17:46:11 2014
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
MRP0: Background Media Recovery process shutdown (CDBGVA_2)

Mon Dec 15 17:46:11 2014

Recovery created pluggable database LUDO

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01565: error in identifying file '+DATA'

ORA-17503: ksfdopn:2 Failed to open file +DATA

ORA-15045: ASM file name '+DATA' is not in reference form

Recovery was unable to create the file as:

'+DATA'

MRP0: Background Media Recovery terminated with error 1274

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'

Mon Dec 15 17:46:11 2014

Managed Standby Recovery not using Real Time Apply

Mon Dec 15 17:46:11 2014

Recovery interrupted!

Recovery stopped due to failure in applying recovery marker (opcode 17.34).

Datafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'

Mon Dec 15 17:46:11 2014

MRP0: Background Media Recovery process shutdown (CDBGVA_2)

One remarkable thing, is that in the standby controlfile, ONLY THE SYSTEM DATAFILE exists:

18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';

    CON_ID
----------
         4

18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765

18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';

CON_ID

----------

18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765

We need to fix the datafiles one by one, but most of the steps can be done once for all the datafiles.

Copy the source PDB from the standby

What do we need to do? Well, the recovery process is stopped, so we can safely copy the datafiles of the source PDB from the standby site because they have not moved yet. (meanwhile, we can put the primary source PDB back in read-write mode).

-- on primary
08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;

Pluggable database altered.

-- on primary

08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;

Pluggable database altered.

Copy the datafiles:

## on the standby:
RMAN> backup as copy pluggable database MAAZ;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00029 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.463.857404625
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00028 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.283.857404623
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866396771.865.866397053 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

## on the standby:

RMAN> backup as copy pluggable database MAAZ;

Starting backup at 15-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00029 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.463.857404625

output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00028 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.283.857404623

output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14

piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866396771.865.866397053 comment=NONE

Finished Control File and SPFILE Autobackup at 15-DEC-14

Do the magic

Now there’s the interesting part: we need to assign the datafile copies of the maaz PDB to LUDO.

Sadly, the OMF will create the copies on the bad location (it’s a copy, to they are created on the same location as the source PDB).

We cannot try to uncatalog and recatalog the copies, because they will ALWAYS be affected to the source PDB. Neither we can use RMAN because it will never associate the datafile copies to the new PDB. We need to rename the files manually.

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
55 28 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.86639709
 Tag: TAG20141215T175041

54 29 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.86639703
 Tag: TAG20141215T175041


RMAN> select name, guid from v$pdbs;

NAME       GUID
---------- --------------------------------
PDB$SEED   FFBCECBB503D606BE043334EA8C019B7
MAAZ       0243BF7B39D4440AE053334EA8C0E471
LUDO       0A4A0048D5321597E053334EA8C0E40A

RMAN> list datafilecopy all;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

55 28 A 15-DEC-14 10295232 14-DEC-14

Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.86639709

Tag: TAG20141215T175041

54 29 A 15-DEC-14 10295232 14-DEC-14

Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.86639703

Tag: TAG20141215T175041

RMAN> select name, guid from v$pdbs;

NAME GUID

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

PDB$SEED FFBCECBB503D606BE043334EA8C019B7

MAAZ 0243BF7B39D4440AE053334EA8C0E471

LUDO 0A4A0048D5321597E053334EA8C0E40A

It’s better to uncatalog the datafile copies before, so we keep the catalog clean:

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 RECID=55 STAMP=866397051
Uncataloged 1 objects


RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 RECID=54 STAMP=866397046
Uncataloged 1 objects

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049' uncatalog;

uncataloged datafile copy

datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 RECID=55 STAMP=866397051

Uncataloged 1 objects

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043' uncatalog;

uncataloged datafile copy

datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 RECID=54 STAMP=866397046

Uncataloged 1 objects

Then, because we cannot rename files on a standby database with standby file management set to AUTO, we need to put it temporarily to MANUAL.

10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';
alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049'
*
ERROR at line 1:
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049'

ERROR at line 1:

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';

NAME                                                         ISPDB
------------------------------------------------------------ -----
standby_archive_dest                                         FALSE
standby_file_management                                      FALSE

10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';

NAME ISPDB

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

standby_archive_dest FALSE

standby_file_management FALSE

standby_file_management is not PDB modifiable, so we need to do it for the whole CDB.

10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

Database altered.

10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

Database altered.

then we need to set back the standby_file_management=auto or the recover will not start:

10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto;
System altered.

1 2	10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto; System altered.

We can now restart the recovery.

The recovery process will:
– change the new datafile by modifying the header for the new PDB
– create the entry for the second datafile in the controlfile
– crash again because the datafile is missing

18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'
ORA-01157: cannot identify/lock data file 61 - see DBWR trace file
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'


18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061

18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;

alter database recover managed standby database

ERROR at line 1:

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 61 is unknown - rename to correct file

ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'

ORA-01157: cannot identify/lock data file 61 - see DBWR trace file

ORA-01111: name for data file 61 is unknown - rename to correct file

ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'

18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049

/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061

We already have the SYSAUX datafile, right? So we can alter the name again:

18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:14:29 SYS@CDBGVA_2> alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043';

Database altered.

18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;

System altered.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:14:29 SYS@CDBGVA_2> alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043';

Database altered.

18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;

System altered.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

This time all the datafiles have been copied (no user datafile for this example) and the recovery process will continue!! 🙂 so we can hit ^C and start it in background.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-16043: Redo apply has been canceled.
ORA-01013: user requested cancel of current operation

 

18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

18:18:19 SYS@CDBGVA_2>

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

alter database recover managed standby database

ERROR at line 1:

ORA-16043: Redo apply has been canceled.

ORA-01013: user requested cancel of current operation

18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

18:18:19 SYS@CDBGVA_2>

The Data Guard configuration reflects the success of this operation.

Do we miss anything?

Of course, we do!! The datafile names of the new PDB reside in the wrong ASM path. We need to fix them!

18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;

Database altered.

RMAN> backup as copy pluggable database ludo;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00061 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00060 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866398689.868.866398945 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

RMAN> switch pluggable database ludo to copy;

using target database control file instead of recovery catalog
datafile 60 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941"
datafile 61 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933"

18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933

18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;

Database altered.

RMAN> backup as copy pluggable database ludo;

Starting backup at 15-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00061 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043

output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00060 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049

output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14

piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866398689.868.866398945 comment=NONE

Finished Control File and SPFILE Autobackup at 15-DEC-14

RMAN> switch pluggable database ludo to copy;

using target database control file instead of recovery catalog

datafile 60 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941"

datafile 61 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933"

18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941

+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933

I know there’s no practical use of this procedure, but it helps a lot in understanding how Multitenant has been implemented.

I expect some improvements in 12.2!!

Cheers

—

Ludo

Tales from the Demo Grounds part 2: cloning a PDB with ASM and Data Guard (no ADG)

Posted on November 20, 2014 by Ludovico

In my #OOW14 presentation about MAA and Multitenant, more precisely at slide #59, “PDB Creation from other PDB without ADG*”, I list a few commands that you can use to achieve a “correct” Pluggable Database clone in case you’re not using Active Data Guard.

What’s the problem with cloning a PDB in a MAA environment without ADG? If you’ve attended my session you should know the answer…

If you read the book “Data Guard Concepts and Administration 12c Release 1 (12.1)“, paragraph 3.5 Creating a PDB in a Primary Database, you’ll see that:

If you plan to create a PDB as a clone from a different PDB, then copy the data files that belong to the source PDB over to the standby database. (This step is not necessary in an Active Data Guard environment because the data files are copied automatically when the PDB is created on the standby database.)

But because there are good possibilities (99%?) that in a MAA environment you’re using ASM, this step is not so simple: you cannot copy the datafiles exactly where you want, it’s OMF, and the recovery process expects the files to be where the controlfile says they should be.

So, if you clone the PDB, the recovery process on the standby doesn’t find the datafiles at the correct location, thus the recovery process will stop and will not start until you fix manually. That’s why Oracle has implemented the new syntax “STANDBYS=NONE” that disables the recovery on the standby for a specific PDB: it lets you disable the recovery temporarily while the recovery process continues to apply logs on the remaining PDBs. (Note, however, that this feature is not intended as a generic solution for having PDBs not replicated. The recommended solution in this case is having two distinct CDBs, one protected by DG, the other not).

With ADG, when you clone the PDB on the primary, on the standby the ADG takes care of the following steps, no matter if on ASM or FS:

recover up to the point where the file# is registered in the controlfile
copy the datafiles from the source DB ON THE STANDBY DATABASE (so no copy over the network)
rename the datafile in the controlfile
continue with the recovery

If you don’t have ADG, and you’re on ASM, Oracle documentation says nothing with enough detail to let you solve the problem. So in August I’ve worked out the “easy” solution that I’ve also included in my slides (#59 and #60):

SQL> create pluggable database DEST from SRC standbys=none;
RMAN> backup as copy pluggable database DEST format ‘/tmp/dest%f.dbf’;
$ scp /tmp/dest*.dbf remote:/tmp
RMAN> catalog start with ‘/tmp/dest’
RMAN> set newnamefor pluggable database DEST to new;
RMAN> restore pluggable database DEST;
RMAN> switch pluggable database DEST to copy;
DGMGRL> edit database ‘STBY’ set state=’APPLY-OFF’;
SQL> Alter pluggable database DEST enable recovery;
DGMGRL> edit database ‘STBY’ set state=’APPLY-ON’;

Once at #OOW14, after endless conversations at the Demo Grounds, I’ve discovered that Oracle has worked out the very same solution requiring network transfer and that it has been documented in a new note.

Making Use of the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)

This note is very informative and I recommend to read it carefully!

What changes (better) in comparison with my first solution, is that Oracle suggests to use the new feature “restore from service”:

RMAN> run{
2> set newname for pluggable database DEST to new;
3> restore pluggable database DEST from service prim;
4> switch datafile all;
5> }

RMAN> run{

2> set newname for pluggable database DEST to new;

3> restore pluggable database DEST from service prim;

4> switch datafile all;

5> }

I’ve questioned the developers at the Demo Grounds about the necessity to use network transfer (I had the chance to speak directly with the developer who has written this piece of code!! :-)) and they said that they had worked out only this solution. So, if you have a huge PDB to clone, the network transfer from the primary to standby may impact severely your Data Guard environment and/or your whole infrastructure, for the time of the transfer.

Of course, I have a complex, undocumented solution, I hope I will find the time to document it, so stay tuned if you’re curious! 🙂

Tales from Demo Grounds part 1: Clone PDBs while open READ-WRITE

Posted on November 6, 2014 by Ludovico

DISCLAIMER: I’ve got this information by chatting with Oracle developers at the Demo Grounds. The functionality is not documented yet and Oracle may change it at its sole discretion. Please refer to the documentation if/when it will be updated 😉

In one of my previous posts named “A PDB is cloned while in read-write, Data Guard loose its marbles (12.1.0.2, ORA-19729)” I’ve blogged about a weird behaviour:

The documentation states that you can create a pluggable database from another one only if the source PDB is open read-only.

Indeed, If I try to clone it when the source PDB is MOUNTED, I get error ORA-65036:

15:44:24 SYS@CDBATL_1> select inst_id, name, open_mode from gv$pdbs where name='MAAZ';

INST_ID NAME OPEN_MODE
---------- ------------------------------ ----------
1 MAAZ MOUNTED
2 MAAZ MOUNTED

15:53:43 SYS@CDBATL_1> create pluggable database ludo from maaz;
create pluggable database ludo from maaz
*
ERROR at line 1:
ORA-65036: pluggable database MAAZ not open in required mode

15:44:24 SYS@CDBATL_1> select inst_id, name, open_mode from gv$pdbs where name='MAAZ';

INST_ID NAME OPEN_MODE

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

1 MAAZ MOUNTED

2 MAAZ MOUNTED

15:53:43 SYS@CDBATL_1> create pluggable database ludo from maaz;

create pluggable database ludo from maaz

ERROR at line 1:

ORA-65036: pluggable database MAAZ not open in required mode

The weird behavior is that if you do it when the source is in read-write mode, it works from release 12.1.0.2 (onward?)

I’ve questioned the developers at the DEMO Grounds and they have confirmed that:

With the 12.1.0.2, they have initially planned to disclose this functionality (clone PDBS in READ-WRITE).
That they had problems in making it work with an Active Data Guard environment (a-ah! so my post was not completely wrong)
Finally they have released it as undocumented feature
In the next release “they will fix it, maybe” and document it
The process of cloning the PDB anyway freeze the transactions on the source

I hope that this update helps clarifying both the behavior and my previous post about this problem! 🙂

Cheers

—

Ludo

Oracle RAC, Oracle Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant (OOW14)

Posted on October 2, 2014 by Ludovico

Here you can find the material related to my session at Oracle Open World 2014. I’m sorry I’m late in publishing them, but I challenge you to find spare time during Oracle Open World! It’s the busiest week of the year! (Hard Work, Hard Play)

Slides

Demo 1 video

Demo 2 video

Demo 1 script

clear

function pause () {
	echo
	read -p "$*"
	echo
}

tnsping cdbatl

pause "next... status db"
clear
echo \$ srvctl status database -db CDBATL

srvctl status database -db CDBATL

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select INST_ID, CON_ID, name, OPEN_MODE
	 from gv\$pdbs
	  where con_id!=2
	  order by name, inst_id;
	exit
EOF

pause "next... add singleton service"

clear

###  add service MAAZAPP SINGLETON
cmd="srvctl add service -db CDBATL -service  maazapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 2 -failoverretry 180 -pdb maaz"
echo \$ $cmd
eval $cmd

pause "next... start service"
clear

cmd="srvctl start service -db CDBATL -service maazapp -instance CDBATL_1"
echo \$ $cmd
eval $cmd

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select INST_ID, CON_ID, name, OPEN_MODE
	 from gv\$pdbs
	  where con_id!=2
	  order by name, inst_id;
	exit
EOF


cmd="srvctl status database -db cdbatl"
echo
echo \$ $cmd
eval $cmd

cmd="srvctl status service -service maazapp -db cdbatl"
echo
echo \$ $cmd
output=`$cmd`
echo $output

current=`echo $output | awk '{print $NF}'`
if [ $current == "raca01" ] ; then
	target="raca02"
else
	target="raca01"
fi


pause "pause... please launch demo1_client.sh"
pause "next... relocate service from $current to $target"

clear

cmd="srvctl relocate service -db CDBATL -service maazapp -currentnode $current -targetnode $target"
echo \$ $cmd
eval $cmd


pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select INST_ID, CON_ID, name, OPEN_MODE
	 from gv\$pdbs
	  where con_id!=2
	  order by name, inst_id;
	exit
EOF

pause "next... close pdb immediate on old inst"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
 alter pluggable database maaz close immediate instances=('CDBATL_1');
	exit
EOF

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select INST_ID, CON_ID, name, OPEN_MODE
	 from gv\$pdbs
	  where con_id!=2
	  order by name, inst_id;
	exit
EOF

pause "next... modify service to uniform"

clear

cmd="srvctl modify service -db CDBATL -service maazapp -cardinality uniform"
echo \$ $cmd
eval $cmd

pause "next... status pdb"
clear
sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select INST_ID, CON_ID, name, OPEN_MODE
	 from gv\$pdbs
	  where con_id!=2
	  order by name, inst_id;
	exit
EOF

echo
cmd="srvctl status service -service maazapp -db cdbatl"
echo \$ $cmd
eval $cmd 

exit

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

clear

function pause () {

echo

read -p "$*"

echo

}

tnsping cdbatl

pause "next... status db"

clear

echo \$ srvctl status database -db CDBATL

srvctl status database -db CDBATL

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select INST_ID, CON_ID, name, OPEN_MODE

from gv\$pdbs

where con_id!=2

order by name, inst_id;

exit

EOF

pause "next... add singleton service"

clear

### add service MAAZAPP SINGLETON

cmd="srvctl add service -db CDBATL -service maazapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 2 -failoverretry 180 -pdb maaz"

echo \$ $cmd

eval $cmd

pause "next... start service"

clear

cmd="srvctl start service -db CDBATL -service maazapp -instance CDBATL_1"

echo \$ $cmd

eval $cmd

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select INST_ID, CON_ID, name, OPEN_MODE

from gv\$pdbs

where con_id!=2

order by name, inst_id;

exit

EOF

cmd="srvctl status database -db cdbatl"

echo

echo \$ $cmd

eval $cmd

cmd="srvctl status service -service maazapp -db cdbatl"

echo

echo \$ $cmd

output=`$cmd`

echo $output

current=`echo $output | awk '{print $NF}'`

if [ $current == "raca01" ] ; then

target="raca02"

else

target="raca01"

pause "pause... please launch demo1_client.sh"

pause "next... relocate service from $current to $target"

clear

cmd="srvctl relocate service -db CDBATL -service maazapp -currentnode $current -targetnode $target"

echo \$ $cmd

eval $cmd

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select INST_ID, CON_ID, name, OPEN_MODE

from gv\$pdbs

where con_id!=2

order by name, inst_id;

exit

EOF

pause "next... close pdb immediate on old inst"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

alter pluggable database maaz close immediate instances=('CDBATL_1');

exit

EOF

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select INST_ID, CON_ID, name, OPEN_MODE

from gv\$pdbs

where con_id!=2

order by name, inst_id;

exit

EOF

pause "next... modify service to uniform"

clear

cmd="srvctl modify service -db CDBATL -service maazapp -cardinality uniform"

echo \$ $cmd

eval $cmd

pause "next... status pdb"

clear

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select INST_ID, CON_ID, name, OPEN_MODE

from gv\$pdbs

where con_id!=2

order by name, inst_id;

exit

EOF

echo

cmd="srvctl status service -service maazapp -db cdbatl"

echo \$ $cmd

eval $cmd

exit

Demo 2 script

txtblk='\e[0;30m' # Black - Regular
txtred='\e[0;31m' # Red
txtgrn='\e[0;32m' # Green
txtrst='\e[0m'    # Text Reset

function echop () {
	echo
	echo -e "${txtgrn}$*${txtrst}"
}

function echos () {
	echo
	echo -e "${txtred}$*${txtrst}"
}

function pause() {
	echo
	read -p "$*"
	echo
}

clear

echop "Status of the PRIMARY DATABASE"
sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	select db_unique_name, database_role from v\$database;
	select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;
	exit
EOF

pause "next... standby status"
clear
echos "Status of the STANDBY DATABASE"

sqlplus sys/racattack@cdbgva as sysdba <<EOF
	set echo on
	select db_unique_name, database_role from v\$database;
	select open_mode from v\$database;
	select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;
	exit
EOF



pause "next... dgmgrl status"
clear
echos "Data Guard configuration and status of the STANDBY database"
dgmgrl <<EOF
connect sys/racattack
show configuration;
show database 'CDBGVA';
exit
EOF


pause "please do tail -f on the apply instance"
pause "next... create new pdb ludo on primary "
clear

echop "Create new pluggable database on the primary: "
echop "create pluggable database ludo admin user ludoadmin identified by ludoadmin;"
sqlplus sys/racattack@cdbatl as sysdba <<EOF
	set echo on
	create pluggable database ludo admin user ludoadmin identified by ludoadmin;

	select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;
	exit
EOF

pause "next... create service for primary on both clusters"
clear

echop "Create service for primary ROLE on the primary cluster (CDBATL) via SSH"

cmd="srvctl add service -db CDBATL -service  ludoapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"
echo "\$ ssh raca01 $cmd"
ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echos "Create service for primary ROLE on the standby cluster (CDBGVA)"

cmd="srvctl add service -db CDBGVA -service  ludoapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"
echo "\$ $cmd"
eval $cmd


pause "next... start service on primary"
clear
echop "Starting service on the primary via SSH"
cmd="srvctl start service -db CDBATL -service  ludoapp"
echo "\$ ssh raca01 $cmd"
ssh raca01 ". /home/oracle/.bash_profile ; $cmd"



pause "next... create read only service for physical standby on both clusters"
clear

echop "Creating temporarily the readonly service for PRIMARY ROLE on the primary cluster (CDBATL) via SSH"
cmd="srvctl add service -db CDBATL -service  ludoread -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"
echo "\$ ssh raca01 $cmd"
ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echop "Starting the readonly service for PRIMARY ROLE on the primary cluster (CDBATL) via SSH"
cmd="srvctl start service -db CDBATL -service  ludoread"
echo "\$ ssh raca01 $cmd"
ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echop "Modifying the readonly service from PRIMARY ROLE to PHYSICAL STANDBY on the primary cluster (CDBATL) via SSH"
cmd="srvctl modify service -db CDBATL -service  ludoread -role physical_standby -pdb ludo"
echo "\$ ssh raca01 $cmd"
ssh raca01 ". /home/oracle/.bash_profile ; $cmd"


echos "Creating the readonly service for PHYSICAL STANDBY on the standby cluster (CDBGVA)"
cmd="srvctl add service -db CDBGVA -service  ludoread -serverpool CDBPOOL -cardinality singleton -role physical_standby -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"
echo "\$ $cmd"
eval $cmd

pause "next... start read only service"

clear
echos "Starting the readonly service on the standby cluster"

cmd="srvctl start service -db CDBGVA -service  ludoread"
echo "\$ $cmd"
eval $cmd


pause "next... standby status"
clear

echos "Standby status"
sqlplus sys/racattack@cdbgva as sysdba <<EOF
	select db_unique_name, database_role from v\$database;
	select open_mode from v\$database;
	select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;
	exit
EOF

pause "please connect to the RW service"

pause "next... dgmgrl status and validate"
clear

echos "Validate Standby database"

dgmgrl <<EOF
connect sys/racattack
show configuration;
validate database 'CDBGVA';
exit
EOF

pause "next... switchover to CDBGVA"
clear

echos "Switchover to CDBGVA! (it takes a while)"
dgmgrl <<EOF
connect sys/racattack
switchover to 'CDBGVA';
exit
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

txtblk='\e[0;30m' # Black - Regular

txtred='\e[0;31m' # Red

txtgrn='\e[0;32m' # Green

txtrst='\e[0m' # Text Reset

function echop () {

echo

echo -e "${txtgrn}$*${txtrst}"

}

function echos () {

echo

echo -e "${txtred}$*${txtrst}"

}

function pause() {

echo

read -p "$*"

echo

}

clear

echop "Status of the PRIMARY DATABASE"

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

select db_unique_name, database_role from v\$database;

select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;

exit

EOF

pause "next... standby status"

clear

echos "Status of the STANDBY DATABASE"

sqlplus sys/racattack@cdbgva as sysdba <<EOF

set echo on

select db_unique_name, database_role from v\$database;

select open_mode from v\$database;

select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;

exit

EOF

pause "next... dgmgrl status"

clear

echos "Data Guard configuration and status of the STANDBY database"

dgmgrl <<EOF

connect sys/racattack

show configuration;

show database 'CDBGVA';

exit

EOF

pause "please do tail -f on the apply instance"

pause "next... create new pdb ludo on primary "

clear

echop "Create new pluggable database on the primary: "

echop "create pluggable database ludo admin user ludoadmin identified by ludoadmin;"

sqlplus sys/racattack@cdbatl as sysdba <<EOF

set echo on

create pluggable database ludo admin user ludoadmin identified by ludoadmin;

select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;

exit

EOF

pause "next... create service for primary on both clusters"

clear

echop "Create service for primary ROLE on the primary cluster (CDBATL) via SSH"

cmd="srvctl add service -db CDBATL -service ludoapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"

echo "\$ ssh raca01 $cmd"

ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echos "Create service for primary ROLE on the standby cluster (CDBGVA)"

cmd="srvctl add service -db CDBGVA -service ludoapp -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"

echo "\$ $cmd"

eval $cmd

pause "next... start service on primary"

clear

echop "Starting service on the primary via SSH"

cmd="srvctl start service -db CDBATL -service ludoapp"

echo "\$ ssh raca01 $cmd"

ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

pause "next... create read only service for physical standby on both clusters"

clear

echop "Creating temporarily the readonly service for PRIMARY ROLE on the primary cluster (CDBATL) via SSH"

cmd="srvctl add service -db CDBATL -service ludoread -serverpool CDBPOOL -cardinality singleton -role primary -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"

echo "\$ ssh raca01 $cmd"

ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echop "Starting the readonly service for PRIMARY ROLE on the primary cluster (CDBATL) via SSH"

cmd="srvctl start service -db CDBATL -service ludoread"

echo "\$ ssh raca01 $cmd"

ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echop "Modifying the readonly service from PRIMARY ROLE to PHYSICAL STANDBY on the primary cluster (CDBATL) via SSH"

cmd="srvctl modify service -db CDBATL -service ludoread -role physical_standby -pdb ludo"

echo "\$ ssh raca01 $cmd"

ssh raca01 ". /home/oracle/.bash_profile ; $cmd"

echos "Creating the readonly service for PHYSICAL STANDBY on the standby cluster (CDBGVA)"

cmd="srvctl add service -db CDBGVA -service ludoread -serverpool CDBPOOL -cardinality singleton -role physical_standby -failovertype select -failovermethod basic -policy automatic -failoverdelay 1 -failoverretry 180 -pdb ludo"

echo "\$ $cmd"

eval $cmd

pause "next... start read only service"

clear

echos "Starting the readonly service on the standby cluster"

cmd="srvctl start service -db CDBGVA -service ludoread"

echo "\$ $cmd"

eval $cmd

pause "next... standby status"

clear

echos "Standby status"

sqlplus sys/racattack@cdbgva as sysdba <<EOF

select db_unique_name, database_role from v\$database;

select open_mode from v\$database;

select inst_id, con_id, name,open_mode from gv\$pdbs where con_id!=2 order by con_id, inst_id;

exit

EOF

pause "please connect to the RW service"

pause "next... dgmgrl status and validate"

clear

echos "Validate Standby database"

dgmgrl <<EOF

connect sys/racattack

show configuration;

validate database 'CDBGVA';

exit

EOF

pause "next... switchover to CDBGVA"

clear

echos "Switchover to CDBGVA! (it takes a while)"

dgmgrl <<EOF

connect sys/racattack

switchover to 'CDBGVA';

exit

EOF

There’s one slide describing the procedure for cloning one PDB using the standbys clause. Oracle has released a Note while I was preparing my slides (one month ago) and I wasn’t aware of it, so you may also checkout this note on MOS:

Making Use of the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)

UPDATE: I’ve blogged about it in a more recent post: Tales from the Demo Grounds part 2: cloning a PDB with ASM and Data Guard (no ADG)

UPDATE 2: I’ve written another blog post about these topics: Cloning a PDB with ASM and Data Guard (no ADG) without network transfer

Cheers!

—

Ludovico

Oracle Active Data Guard 12c: Far Sync Instance, Real-Time Cascade Standby, and Other Goodies

Posted on October 2, 2014 by Ludovico

Here you can find the content related to my second presentation at Oracle Open World 2014.

Slides

Demo video1: Real-Time Cascade

Demo video2: Far Sync Instance

Demo 1 Script

clear

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD 2 DATABASES ####"

dgmgrl -echo sys/manager <<EOF
show configuration
EOF
read -p ""


echo "#### ADDING DATABASE REP ####"
dgmgrl -echo sys/manager <<EOF
add database 'REP' as connect identifier is 'REP';
EOF
read -p ""

echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration
EOF
read -p ""


echo
echo "#### EDIT REDOROUTES ####"
echo
dgmgrl -echo sys/manager <<EOF
edit database 'PROD' set property redoroutes='(PROD:DR)'
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit database 'REP' set property redoroutes='(REP:DR)' 
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit database 'DR' set property redoroutes='(PROD:REP)(REP:PROD)(DR:REP,PROD)'
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit database 'REP' set property 'NetTimeout'=15;
edit database 'REP' set property 'ReopenSecs'=5;
EOF
read -p ""
echo

echo "#### ENABLE DATABASE REP ####"
dgmgrl -echo sys/manager <<EOF
enable database 'REP'
EOF
read -p ""

echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration 
EOF
echo
echo
echo "#### IS IT WORKING?? ####"
read -p ""

echo "#### ENABLE REL-TIME CASCADE ####"
dgmgrl -echo sys/manager <<EOF
edit database 'DR' set property redoroutes='(PROD:REP ASYNC)(REP:PROD ASYNC)(DR:REP,PROD)'
EOF
read -p ""


echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration 
EOF
echo
echo "#### NOTICE THE NEW BEHAVIOR ####"
read -p ""

echo "#### SWITCHOVER TO REP ####"
dgmgrl -echo sys/manager <<EOF
switchover to 'REP'
EOF
read -p ""

echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration 
EOF
read -p ""


echo "#### SWITCHOVER TO PROD ####"
dgmgrl -echo sys/manager <<EOF
switchover to 'PROD'
EOF

clear

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD 2 DATABASES ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

read -p ""

echo "#### ADDING DATABASE REP ####"

dgmgrl -echo sys/manager <<EOF

add database 'REP' as connect identifier is 'REP';

EOF

read -p ""

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

read -p ""

echo

echo "#### EDIT REDOROUTES ####"

echo

dgmgrl -echo sys/manager <<EOF

edit database 'PROD' set property redoroutes='(PROD:DR)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit database 'REP' set property redoroutes='(REP:DR)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit database 'DR' set property redoroutes='(PROD:REP)(REP:PROD)(DR:REP,PROD)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit database 'REP' set property 'NetTimeout'=15;

edit database 'REP' set property 'ReopenSecs'=5;

EOF

read -p ""

echo

echo "#### ENABLE DATABASE REP ####"

dgmgrl -echo sys/manager <<EOF

enable database 'REP'

EOF

read -p ""

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

echo

echo "#### IS IT WORKING?? ####"

read -p ""

echo "#### ENABLE REL-TIME CASCADE ####"

dgmgrl -echo sys/manager <<EOF

edit database 'DR' set property redoroutes='(PROD:REP ASYNC)(REP:PROD ASYNC)(DR:REP,PROD)'

EOF

read -p ""

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

echo

echo "#### NOTICE THE NEW BEHAVIOR ####"

read -p ""

echo "#### SWITCHOVER TO REP ####"

dgmgrl -echo sys/manager <<EOF

switchover to 'REP'

EOF

read -p ""

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

read -p ""

echo "#### SWITCHOVER TO PROD ####"

dgmgrl -echo sys/manager <<EOF

switchover to 'PROD'

EOF

Demo 2 script

clear

echo "#### CURRENT CONFIGURATION: 3 CASCADE STANDBY DATABASES ####"

dgmgrl -echo sys/manager <<EOF
show configuration
EOF
read -p ""


echo "#### CREATE CONTROLFILE ####"
sqlplus "/ as sysdba" @create_fs_ctl.sql 
read -p ""

echo "#### COPY FAR SYNC CONTROLFILE TO FAR SYNC HOSTS ####"
scp /tmp/control01.ctl oracle@o12f01:/u01/app/oracle/oradata/PRODFS/controlfile/control01.ctl

scp /tmp/control01.ctl oracle@o12f02:/u01/app/oracle/oradata/DRFS/controlfile/control01.ctl

read -p "#### START FS INSTANCES, CLEAR STANDBY LOGS, THEN CONTINUE HERE ####"



echo "#### ADD FAR_SYNC INSTANCED ####"
dgmgrl -echo sys/manager <<EOF
add far_sync 'PRODFS' as connect identifier is 'PRODFS_DG';
EOF
read -p ""
dgmgrl -echo sys/manager <<EOF
add far_sync 'DRFS' as connect identifier is 'DRFS_DG';
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit far_sync 'PRODFS' set property 'NetTimeout'=15;
edit far_sync 'PRODFS' set property 'ReopenSecs'=5;
edit far_sync 'DRFS' set property 'NetTimeout'=15;
edit far_sync 'DRFS' set property 'ReopenSecs'=5;
EOF
read -p ""
echo

echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration 
EOF
read -p ""


echo "#### EDIT REDOROUTES ####"
dgmgrl -echo sys/manager <<EOF
edit database 'PROD' set property redoroutes='(PROD:PRODFS SYNC)'
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit far_sync 'PRODFS' set property redoroutes='(PROD:DR ASYNC)'
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit database 'DR' set property redoroutes='(PROD:REP ASYNC)(REP:DRFS ASYNC)(DR:REP SYNC, DRFS SYNC)' 
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit database 'REP' set property redoroutes='(REP:DR SYNC)'
EOF
read -p ""

dgmgrl -echo sys/manager <<EOF
edit far_sync 'DRFS' set property redoroutes='(DR:PROD ASYNC)(REP:PROD ASYNC)'
EOF
read -p ""

echo
echo "#### ENABLE FAR_SYNCS ####"
dgmgrl -echo sys/manager <<EOF
enable far_sync 'PRODFS'
EOF
read -p ""
dgmgrl -echo sys/manager <<EOF
enable far_sync 'DRFS'
EOF
read -p ""

echo "#### NEW CONFIGURATION ####"
dgmgrl -echo sys/manager <<EOF
show configuration 
EOF
echo
echo "#### IT MAY TAKE SOME MINUTES BEFORE EVERYTHING START WORKING ####"
read -p ""

dgmgrl -echo sys/manager <<EOF
show configuration 
EOF

clear

echo "#### CURRENT CONFIGURATION: 3 CASCADE STANDBY DATABASES ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

read -p ""

echo "#### CREATE CONTROLFILE ####"

sqlplus "/ as sysdba" @create_fs_ctl.sql

read -p ""

echo "#### COPY FAR SYNC CONTROLFILE TO FAR SYNC HOSTS ####"

scp /tmp/control01.ctl oracle@o12f01:/u01/app/oracle/oradata/PRODFS/controlfile/control01.ctl

scp /tmp/control01.ctl oracle@o12f02:/u01/app/oracle/oradata/DRFS/controlfile/control01.ctl

read -p "#### START FS INSTANCES, CLEAR STANDBY LOGS, THEN CONTINUE HERE ####"

echo "#### ADD FAR_SYNC INSTANCED ####"

dgmgrl -echo sys/manager <<EOF

add far_sync 'PRODFS' as connect identifier is 'PRODFS_DG';

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

add far_sync 'DRFS' as connect identifier is 'DRFS_DG';

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit far_sync 'PRODFS' set property 'NetTimeout'=15;

edit far_sync 'PRODFS' set property 'ReopenSecs'=5;

edit far_sync 'DRFS' set property 'NetTimeout'=15;

edit far_sync 'DRFS' set property 'ReopenSecs'=5;

EOF

read -p ""

echo

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

read -p ""

echo "#### EDIT REDOROUTES ####"

dgmgrl -echo sys/manager <<EOF

edit database 'PROD' set property redoroutes='(PROD:PRODFS SYNC)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit far_sync 'PRODFS' set property redoroutes='(PROD:DR ASYNC)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit database 'DR' set property redoroutes='(PROD:REP ASYNC)(REP:DRFS ASYNC)(DR:REP SYNC, DRFS SYNC)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit database 'REP' set property redoroutes='(REP:DR SYNC)'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

edit far_sync 'DRFS' set property redoroutes='(DR:PROD ASYNC)(REP:PROD ASYNC)'

EOF

read -p ""

echo

echo "#### ENABLE FAR_SYNCS ####"

dgmgrl -echo sys/manager <<EOF

enable far_sync 'PRODFS'

EOF

read -p ""

dgmgrl -echo sys/manager <<EOF

enable far_sync 'DRFS'

EOF

read -p ""

echo "#### NEW CONFIGURATION ####"

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

echo

echo "#### IT MAY TAKE SOME MINUTES BEFORE EVERYTHING START WORKING ####"

read -p ""

dgmgrl -echo sys/manager <<EOF

show configuration

EOF

For the demo I’ve used 5 machines running 3 database instances and 2 Far Sync instances. I cannot provide the documentation for creating the demo environment, but the scripts may be useful to understand how the demo works.

Cheers

—

Ludo

A PDB is cloned while in read-write, Data Guard loose its marbles (12.1.0.2, ORA-19729)

Posted on August 29, 2014 by Ludovico

UPDATE: please check my more recent post about this problem and the information I’ve got at the Oracle Demo Grounds during OOW14: https://www.ludovicocaldara.net/dba/demo-grounds-clone-pdb-rw/

I feel the strong need to blog abut this very recent problem because I’ve spent a lot of time debugging it… especially because there’s no information about this error on the MOS.

Introduction
For a lab, I have prepared two RAC Container databases in physical stand-by.
Real-time query is configured (real-time apply, standby in read-only mode).

Following the doc, http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#CCHDFDDG, I’ve cloned one local pluggable database to a new PDB and, because Active Data Guard is active, I was expecting the PDB to be created on the standby and its files copied without problems.

BUT! I’ve forgot to put my source PDB in read-only mode on the primary and, strangely:

The pluggable database has been created on the primary WITHOUT PROBLEMS (despite the documentation explicitly states that it needs to be read-only)
The recovery process on the standby stopped with error.

Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATA/CDBGVA/01B838F74693443FE053334EA8C03527/DATAFILE/system.437.856805523 from +DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413
MRP0: Background Media Recovery terminated with error 1274
Thu Aug 28 17:32:05 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace/CDBGVA_1_mrp0_13949.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/01B838F74693443FE053334EA8C03527/DATAFILE/system.477.856805517'
ORA-19729: File 22 is not the initial version of the plugged in datafile
Thu Aug 28 17:32:05 2014

Recovery copied files for tablespace SYSTEM

Recovery successfully copied file +DATA/CDBGVA/01B838F74693443FE053334EA8C03527/DATAFILE/system.437.856805523 from +DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413

MRP0: Background Media Recovery terminated with error 1274

Thu Aug 28 17:32:05 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace/CDBGVA_1_mrp0_13949.trc:

ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/01B838F74693443FE053334EA8C03527/DATAFILE/system.477.856805517'

ORA-19729: File 22 is not the initial version of the plugged in datafile

Thu Aug 28 17:32:05 2014

Now, the primary had all its datafiles (the new PDB has con_id 4):

CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 +DATA/CDB/DATAFILE/system.283.854626623
1 +DATA/CDB/DATAFILE/undotbs1.290.854627639
1 +DATA/CDB/DATAFILE/users.291.854627695
1 +DATA/CDB/DATAFILE/undotbs2.287.854627063
1 +DATA/CDB/DATAFILE/sysaux.285.854626879
2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.286.854627011
2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.284.854626785
3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.390.855681795
3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.389.855681795
4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061
4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061

CON_ID NAME

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

1 +DATA/CDB/DATAFILE/system.283.854626623

1 +DATA/CDB/DATAFILE/undotbs1.290.854627639

1 +DATA/CDB/DATAFILE/users.291.854627695

1 +DATA/CDB/DATAFILE/undotbs2.287.854627063

1 +DATA/CDB/DATAFILE/sysaux.285.854626879

2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.286.854627011

2 +DATA/CDB/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.284.854626785

3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.390.855681795

3 +DATA/CDBATL/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.389.855681795

4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061

4 +DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061

and the standby was missing the datafiles of the new PDB:

1* select con_id, name from v$datafile order by 1

CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 +DATA/CDBGVA/DATAFILE/system.319.855054997
1 +DATA/CDBGVA/DATAFILE/undotbs2.283.855055141
1 +DATA/CDBGVA/DATAFILE/users.285.855055149
1 +DATA/CDBGVA/DATAFILE/undotbs1.284.855055145
1 +DATA/CDBGVA/DATAFILE/sysaux.281.855055061
2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.282.855055127
2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.280.855055053
3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.363.855681865
3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.362.855681863

1* select con_id, name from v$datafile order by 1

CON_ID NAME

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

1 +DATA/CDBGVA/DATAFILE/system.319.855054997

1 +DATA/CDBGVA/DATAFILE/undotbs2.283.855055141

1 +DATA/CDBGVA/DATAFILE/users.285.855055149

1 +DATA/CDBGVA/DATAFILE/undotbs1.284.855055145

1 +DATA/CDBGVA/DATAFILE/sysaux.281.855055061

2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/sysaux.282.855055127

2 +DATA/CDBGVA/FFBCECBB503D606BE043334EA8C019B7/DATAFILE/system.280.855055053

3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/sysaux.363.855681865

3 +DATA/CDBGVA/00B29F47A2D71CC2E053334EA8C03B13/DATAFILE/system.362.855681863

But, on the standby database, the PDB somehow was existing.

16:20:58 SYS@CDBGVA_1> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
MAAZ
LUDO

16:20:58 SYS@CDBGVA_1> select name from v$pdbs;

NAME

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

PDB$SEED

MAAZ

LUDO

I’ve tried to play a little, and finally decided to disable the recovery for the PDB (new in 12.1.0.2).
But to disable the recovery I was needing to connect to the PDB, but the PDB was somehow “inexistent”:

16:21:35 SYS@CDBGVA_1> alter session set container=LUDO;
ERROR:
ORA-65011: Pluggable database LUDO does not exist.

16:21:39 SYS@CDBGVA_1> select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
MAAZ MOUNTED
LUDO MOUNTED

16:21:35 SYS@CDBGVA_1> alter session set container=LUDO;

ERROR:

ORA-65011: Pluggable database LUDO does not exist.

16:21:39 SYS@CDBGVA_1> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

MAAZ MOUNTED

LUDO MOUNTED

So I’ve tried to drop it, but off course, the standby was read-only and I could not drop the PDB:

16:22:01 SYS@CDBGVA_1> drop pluggable database ludo;
drop pluggable database ludo
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

16:22:01 SYS@CDBGVA_1> drop pluggable database ludo;

drop pluggable database ludo

ERROR at line 1:

ORA-16000: database or pluggable database open for read-only access

Then I’ve shutted down the standby, but one instance hung and I’ve needed to do a shutdown abort (I don’t know if it was related with my original problem..)

# [ oracle@racb02:/u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace [16:22:45] [12.1.0.2.0 EE SID=CDBGVA_1] 1 ] #
# srvctl stop database -d CDBGVA -o immediate
[HANGS]

# [ oracle@racb02:/u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_1/trace [16:22:45] [12.1.0.2.0 EE SID=CDBGVA_1] 1 ] #

# srvctl stop database -d CDBGVA -o immediate

[HANGS]

After mounting again the standby, the PDB was also accessible:

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 28 16:30:19 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

16:30:19 SYS@CDBGVA_1> alter session set container=LUDO;

Session altered.

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 28 16:30:19 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

16:30:19 SYS@CDBGVA_1> alter session set container=LUDO;

Session altered.

So I’ve been able to disable the recovery:

16:31:19 SYS@CDBGVA_1> alter pluggable database ludo disable recovery;

Pluggable database altered.

16:31:19 SYS@CDBGVA_1> alter pluggable database ludo disable recovery;

Pluggable database altered.

Then, on the primary, I’ve took a fresh backup of the involved datafiles:

RMAN> backup as copy datafile 16,17 format '/tmp/%f.dbf';

Starting backup at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=88 instance=CDBATL_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061
output file name=/tmp/17.dbf tag=TAG20140828T163251 RECID=4 STAMP=856801976
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061
output file name=/tmp/16.dbf tag=TAG20140828T163251 RECID=5 STAMP=856801981
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-AUG-14

Starting Control File and SPFILE Autobackup at 28-AUG-14
piece handle=+DATA/CDBATL/AUTOBACKUP/2014_08_28/s_856801982.471.856801983 comment=NONE
Finished Control File and SPFILE Autobackup at 28-AUG-14

RMAN> backup as copy datafile 16,17 format '/tmp/%f.dbf';

Starting backup at 28-AUG-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=88 instance=CDBATL_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00017 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.459.856788061

output file name=/tmp/17.dbf tag=TAG20140828T163251 RECID=4 STAMP=856801976

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00016 name=+DATA/CDBATL/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.458.856788061

output file name=/tmp/16.dbf tag=TAG20140828T163251 RECID=5 STAMP=856801981

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 28-AUG-14

Starting Control File and SPFILE Autobackup at 28-AUG-14

piece handle=+DATA/CDBATL/AUTOBACKUP/2014_08_28/s_856801982.471.856801983 comment=NONE

Finished Control File and SPFILE Autobackup at 28-AUG-14

and I’ve copied and cataloged the copies to the controlfile:

RMAN> catalog start with '/tmp/1';

searching for all files that match the pattern /tmp/1

List of Files Unknown to the Database
=====================================
File Name: /tmp/17.dbf
File Name: /tmp/16.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/17.dbf
File Name: /tmp/16.dbf

RMAN> catalog start with '/tmp/1';

searching for all files that match the pattern /tmp/1

List of Files Unknown to the Database

=====================================

File Name: /tmp/17.dbf

File Name: /tmp/16.dbf

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /tmp/17.dbf

File Name: /tmp/16.dbf

but the restore was impossible, because the controlfile was not knowing these datafiles!!

16:38:48 SYS@CDBGVA_1> select file# from v$datafile;

FILE#
----------
1
2
3
4
5
6
7
10
11

RMAN> run {
2> set newname for datafile 16 to new;
3> set newname for datafile 17 to new;
4> restore datafile 16,17;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 instance=CDBGVA_1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/28/2014 16:37:02
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 17

RMAN> exit

16:38:48 SYS@CDBGVA_1> select file# from v$datafile;

FILE#

----------

RMAN> run {

2> set newname for datafile 16 to new;

3> set newname for datafile 17 to new;

4> restore datafile 16,17;

5> }

executing command: SET NEWNAME

Starting restore at 28-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=64 instance=CDBGVA_1 device type=DISK

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

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

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

RMAN-03002: failure of restore command at 08/28/2014 16:37:02

RMAN-20201: datafile not found in the recovery catalog

RMAN-06010: error while looking up datafile: 17

RMAN> exit

So I’ve RESTARTED the recovery for a few seconds, and because the PDB had the recovery disabled, the recovery process has added the datafiles and set them offline.

16:38:08 SYS@CDBGVA_1> alter database recover managed standby database ;
alter database recover managed standby database
*
ERROR at line 1:
ORA-16043: Redo apply has been canceled.
ORA-01013: user requested cancel of current operation

16:38:48 SYS@CDBGVA_1> select file# from v$datafile;

FILE#
----------
1
2
3
4
5
6
7
10
11
16
17

16:38:08 SYS@CDBGVA_1> alter database recover managed standby database ;

alter database recover managed standby database

ERROR at line 1:

ORA-16043: Redo apply has been canceled.

ORA-01013: user requested cancel of current operation

16:38:48 SYS@CDBGVA_1> select file# from v$datafile;

FILE#

----------

Then I’ve been able to restore the datafiles 🙂

RMAN> run {
2> set newname for datafile 16 to new;
3> set newname for datafile 17 to new;
4> restore datafile 16,17;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=CDBGVA_1 device type=DISK

channel ORA_DISK_1: restoring datafile 00016
input datafile copy RECID=21 STAMP=856802136 file name=/tmp/16.dbf
destination for restore of datafile 00016: +DATA
channel ORA_DISK_1: copied datafile copy of datafile 00016
output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413 RECID=22 STAMP=856802416
channel ORA_DISK_1: restoring datafile 00017
input datafile copy RECID=20 STAMP=856802136 file name=/tmp/17.dbf
destination for restore of datafile 00017: +DATA
channel ORA_DISK_1: copied datafile copy of datafile 00017
output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417 RECID=23 STAMP=856802421
Finished restore at 28-AUG-14

RMAN>

RMAN> switch datafile 16, 17 to copy;

datafile 16 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413"
datafile 17 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417"

RMAN>

RMAN> run {

2> set newname for datafile 16 to new;

3> set newname for datafile 17 to new;

4> restore datafile 16,17;

5> }

executing command: SET NEWNAME

Starting restore at 28-AUG-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 instance=CDBGVA_1 device type=DISK

channel ORA_DISK_1: restoring datafile 00016

input datafile copy RECID=21 STAMP=856802136 file name=/tmp/16.dbf

destination for restore of datafile 00016: +DATA

channel ORA_DISK_1: copied datafile copy of datafile 00016

output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413 RECID=22 STAMP=856802416

channel ORA_DISK_1: restoring datafile 00017

input datafile copy RECID=20 STAMP=856802136 file name=/tmp/17.dbf

destination for restore of datafile 00017: +DATA

channel ORA_DISK_1: copied datafile copy of datafile 00017

output file name=+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417 RECID=23 STAMP=856802421

Finished restore at 28-AUG-14

RMAN>

RMAN> switch datafile 16, 17 to copy;

datafile 16 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/system.435.856802413"

datafile 17 switched to datafile copy "+DATA/CDBGVA/01B431F9BDF51AB7E053334EA8C06877/DATAFILE/sysaux.355.856802417"

RMAN>

Finally, I’ve enabled again the recovery for the PDB and restarted the apply process.

16:41:14 SYS@CDBGVA_1> alter session set container=LUDO;

Session altered.

16:41:19 SYS@CDBGVA_1> alter pluggable database ludo enable recovery;

Pluggable database altered.

16:41:14 SYS@CDBGVA_1> alter session set container=LUDO;

Session altered.

16:41:19 SYS@CDBGVA_1> alter pluggable database ludo enable recovery;

Pluggable database altered.

Lesson learned: if you want to clone a PDB never, ever, forget to put your source PDB in read-only mode or you’ll have to deal with it!! 🙂