DBA survival BLOG

DBA stuff and Oracle Data Guard

OOW14… I know I’m late… but it’s worth to blog about it, at least I won’t forget!

Posted on October 31, 2014 by Ludovico

One month after the Open World, and still I’ve got no time to blog about it… The first reason is… well… I’ve been working on new stuff (a couple of new websites are coming), then I’ve had to adapt old slides with the great content got at the conference (ZDLRA and other topics). Finally, I was also tired about working off-hours, I needed to spend good time with my family after this summer’s rush (moved to the new house and meanwhile worked on one gazillion different topics).

Open World 2014, a different personal feeling

The Open World has been again THE big conference that no Oracle professional can miss. But this time it has been very different to me comparing to 2013.

It wasn’t my first time as attendee, so it has been less surprising. I’ve attended as a Speaker (that made me feel part of the conference, and not just a spectator). I’ve attended also as an Oracle ACE (I’ll blog about thisin another post, soon or later). I was already friend with many people that I was eager to see again (and I feel the same now, waiting for the next conference).

My sessions

Surprisingly, from the very beginning (at the time of the session acceptance from Oracle) I felt very relaxed about my presentations. One presentation after the other, I’m getting aware of my limits (especially my bad English! ;-)) but I’m also getting experienced. So I went to Open World without stress, even knowing that the first one, on Sunday, was already full booked a couple of weeks before the beginning of the conference.

Oracle has assigned to my sessions the VERY FIRST and the VERY LAST slots of the conference, so I’ve had less attendees than expected (around 170 for both sessions out of 230 and 270 registered people respectively). At least, I’ve had the chance to open and close the conference by saying “Welcome to Open World” and “Thank you, see you next year!” 🙂

I’ve got very few feedback from the official OOW website, just 3 people for each session (anyway with a good, statistically irrelevant score: 4.33 and 4.67 out of 5), but I’ve been much more excited by the direct feedback of people that joined me for a chat right after the sessions and by the feedback on Twitter. I have to say that the interactions on this social media made my week, again.

@ludodba Great Multitenant with RAC and DG presentation @oracleopenworld. New to me: Cloned PDB is not propagated in DG

— Johannes Ahrends (@streetkiter) September 29, 2014

@ludodba pre session setup. Yes, I am close enough 🙂 #oow14 pic.twitter.com/H0ypxdX0NM — Andrejs Karpovs (@AndrejsKarpovs) October 2, 2014

Best idea yet! My boy @ludodba. #oow14 pic.twitter.com/S2nwZQGEjw — Seth Miller (@Seth_M_Miller) October 2, 2014

This was easily the funniest slide from all the #OOW14 presentations I attended cc @maaz_anjum @ludodba pic.twitter.com/n9spxFrOXI

— Rene Antunez (@grantunez) October 10, 2014

Thanks for the excellent presentation @ludodba – loved the ppt pics 😉 safe travels and hope to catch up again soon — Mike Donovan (@dbvisitmike) October 2, 2014

Great opening session on RAC and Multitenant, great demos!! @ludodba

— Brian Pawlik (@brianpawlik) September 28, 2014

RAC Attack

This year we organized (or… Bobby Curtis and Laura Ramsey did) the RAC Attack at the OTN Lounge. It’s always a great meeting point for all the bloggers and Oracle ACEs, but due to the day (Sunday) and the location (the mezzanine of the Moscone South), we’ve got less people than expected. Anyway, it’s always my favorite project and workshop. We’ve got T-shirts, bandannas, famous ninjas and the most famous attendee ever. Who? Try to figure it out yourself:

Don’t forget to say hi to all #RACAttack geeks at #oow14 It is on now! pic.twitter.com/pydQOnfDJs — Yury Velikanov (@yvelik) September 28, 2014

Networking events

Saturday I’ve missed a great bike ride, and Sunday I had to miss the “Official” Golden Gate Run organized by Jeff Smith because it was overlapping with my session. So the first big event has been the ACE Dinner on Sunday, kindly sponsored by the Oracle ACE Program. A dream come true!

Monday morning, despite a very bad weather, I’ve been part of the great Swim in the Bay organized by Oraclenerd. The air was very cold (15°C) and the water was even colder!

On Monday evening there was the Tech Fest organized at the Oracle Plaza (once Howard St.), I’ve been there with friends and colleagues and then we headed together to the Mikkeller Bar for a beer.

I’ve planned my Tuesday longtime ago, when I’ve realized that there was a concert of the Royal Blood and the Pixies at the Masonic Center (Nob Hill) I’ve bought a couple of tickets for my and my ex-colleague and friend Franck Pachot.

DSC02682_2
DSC02687_2

Wednesday morning I’ve organized the replica of the Golden Gate Run. Franck and Sten Vesterli joined me. Doing it early in the morning is beautiful!

Wednesday evening was the time of the great Blogger Meetup organized by Pythian and the OTN. Nowhere else you can find the same concentration of Oracle bloggers! Sadly this year the gadget was electronic (an app on the smartphone requiring network connectivity, so almost unusable and much less interactive, IMHO). I’ve finally met my good tweep Kevin Closson that wasn’t there in 2013.

After the meetup, me and a team of very good friends have organized a dinner at the Fisherman’s Wharf, far from the crowd going to the traditional Oracle Appreciation Event. Try to recognize them in the picture below, I couldn’t ask for a better companionship!! 🙂

Thursday is always sad, everybody leaves and after the conference the Oracle Plaza gets immediately so empty! The afternoon we’ve organized a meeting of the RAC SIG, and the evening I’ve managed to meet one more time many friends. Thank you guys 🙂

My ride on Friday

I’ve ended my week with a long bike ride. I’ve under estimated the size of San Francisco, after more than four hours, 50km and almost 800m of cumulated ascent, I was completely out of order! But I’ve got the chance to visit for the first time most attractions… The City Hall, the Painted Ladies, the Golden Gate Park, the beach on the ocean side, the Coastal Trail, the Legion of Honor, the Presidio and the Hawk Hill on the other side of the bridge.

DSC_0121_2

To resume

Listing all the friends I would like to thank for the OOW week is long… Heli, Ovind, Kyle, Martin, Yury, Björn, Franck, Tim, Kellyn, Rene, Leighton, Seth, Laura, Vikki, Jennifer, Mina, Marc, Nelson, Markus, Larry, Edelweiss, Emiliano, Roman, Daniele, Konrad, Chris, Christian, Sten, Hans, Rooq, Andrejs, Paul, Michelle, Kai, Ian, Michael, Alex, Vanessa, Mark, Osama, Bobby, Rick, Gurkan, Laurent, Kevin, Jason, Chet, Carlos, Mauro, Danny, Don, Jan, Vit, Biju, Stacey, Mike, Henning, Jeff, Christophe, Dominique, Hervé… I know I’m forgetting too many, you know who you are, thank you!

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

My agenda at Oracle Open World 2014

Posted on September 25, 2014 by Ludovico

It’s time to prepare my luggage for the OOW, it will be my second time in San Francisco and the first as ACE and speaker. I still need to figure out if I’ll manage to get my badge Sunday morning, because Saturday I won’t be in the city before the registration desks close.

If you want to reach me during the conference, this is my “expected” plan:

I’m looking forward particularly to meet my many community friends at the ACE dinner (the first as non-infiltrated ;-)), the blogger meetup and the crazy swim in the bay.

See you on Sunday! 🙂

RAC Attack 12c in Switzerland, it’s a wrap!

Posted on September 21, 2014 by Ludovico

Last Wednesday, September 17th, we’ve done the first RAC Attack in Switzerland (as far as I know!). I have to say that it has been a complete success like all other RAC Attacks I’ve been involved in.

This time I’ve been particularly happy and proud because I’ve organized it almost all alone. Trivadis, my employer, has kindly sponsored everything: the venue (the new, cool Trivadis offices in Geneva), the T-shirts (I’ve done the design, very similar to the one I’ve designed for Collaborate 14), beers and pizza!

For beer lovers,we’ve got the good “Blanche des Neiges” from Belgium, “La Helles” and “La Rossa” from San Martino Brewery, Ticino (Italian speaking region of Switzerland). People have appreciated 🙂

We’ve had 4 top-class Ninjas and 10 people actively installing Oracle RAC (plus a famous blogger that joined for networking), sadly two people have renounced at the last minute. For the very first time, all the participants have downloaded the Oracle Software in advance. When they’ve registered I’ve reminded twice that the software was necessary because we cannot provide it due to legal constraints.

People running the lab on Windows laptops have reported problems with VirtualBox 4.3.16 (4.3.14 has been skipped directly because of known problems). So every one had to fallback to version 4.3.12 (the last stable release, IMO).

The best praise I’ve got has been the presence of a Senior DBA coming from Nanterre! 550Km (> 5h00 by public transport door-to-door) and an overnight stay just for this event, can you believe it? 🙂

This makes me think seriously about the real necessity of organizing this kind of events around the world.

Off course, we’ve got a photo session with a lot of jumps 😉 We could not miss this RAC Attack tradition!

We’ve wrapped everything around 10:30pm, after a bit more than 5 hours of event. We’ve enjoyed a lot and had good time together chatting about Oracle RAC and about our work in general.

Thank you again to all participants!! 🙂

Upcoming presentations and workshops (Fall 2014)

Posted on September 10, 2014 by Ludovico

My community involvment will see its busiest season. It will start today with a webinar in Italian for the RAC SIG, then three conferences and 4 user group meetings, for a total of twelve sessions and workshops before the end of the year.

The updated list of upcoming events can be found here.

BTW, this is the list of events from today to December:


	
	
    
        
			Date/Time
			Event
		
   	
    
			
                10/09/2014

                4:00 pm - 5:00 pm
            
            
                RAC SIG webinar in Italian: Gestisci Oracle RAC più facilmente con i Policy-Managed Database
                
            
        

			
                12/09/2014

                5:15 pm - 6:05 pm
            
            
                Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant
                
TechEvent 09.2014, Mövenpick Hotel, Regensdorf Zurich
            
        

			
                13/09/2014

                11:05 am - 11:55 am
            
            
                Oracle Database Backup Log Recovery Appliance: a quick preview
                
TechEvent 09.2014, Mövenpick Hotel, Regensdorf Zurich
            
        

			
                28/09/2014

                8:00 am - 8:45 am
            
            
                Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant
                
Oracle Open World 2014, San Francisco California
            
        

			
                28/09/2014

                9:00 am - 2:00 pm
            
            
                RAC Attack at OOW14
                
Oracle Open World 2014, San Francisco California
            
        

			
                02/10/2014

                2:30 pm - 3:15 pm
            
            
                Oracle Active Data Guard 12c: Far Sync Instance, Real-Time Cascade Standby, and Other Goodies 
                
Oracle Open World 2014, San Francisco California
            
        

			
                16/10/2014

                9:00 am
            
            
                Oracle Database Backup Logging Recovery Appliace: a quick preview
                
SOUG SIG 10.2014 (Engineered Systems, Infrastruktur, Cloud), ABB Segelhof, Baden, Dättwil AG
            
        

			
                25/10/2014

                12:00 am
            
            
                Costruire siti con Wordpress
                
Italian Linux Day 2014, Aosta 
            
        

			
                06/11/2014

                9:00 am - 12:30 pm
            
            
                Oracle Active Data Guard 12c: Far Sync Instance, Real-Time Cascade Standby, and Other Goodies
                
SOUG-R SIG 11.2014, Continental Hotel, Lausanne, Lausanne 
            
        

			
                08/12/2014 - 10/12/2014

                12:00 am
            
            
                RAC Attack at UKOUG TECH14
                
UKOUG Tech 2014, ACC Liverpool, Liverpool 
            
        

			
                10/12/2014

                9:00 am - 9:50 am
            
            
                Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant
                
UKOUG Tech 2014, ACC Liverpool, Liverpool

Date/Time	Event
10/09/2014 4:00 pm - 5:00 pm	RAC SIG webinar in Italian: Gestisci Oracle RAC più facilmente con i Policy-Managed Database
12/09/2014 5:15 pm - 6:05 pm	Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant TechEvent 09.2014, Mövenpick Hotel, Regensdorf Zurich
13/09/2014 11:05 am - 11:55 am	Oracle Database Backup Log Recovery Appliance: a quick preview TechEvent 09.2014, Mövenpick Hotel, Regensdorf Zurich
28/09/2014 8:00 am - 8:45 am	Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant Oracle Open World 2014, San Francisco California
28/09/2014 9:00 am - 2:00 pm	RAC Attack at OOW14 Oracle Open World 2014, San Francisco California
02/10/2014 2:30 pm - 3:15 pm	Oracle Active Data Guard 12c: Far Sync Instance, Real-Time Cascade Standby, and Other Goodies Oracle Open World 2014, San Francisco California
16/10/2014 9:00 am	Oracle Database Backup Logging Recovery Appliace: a quick preview SOUG SIG 10.2014 (Engineered Systems, Infrastruktur, Cloud), ABB Segelhof, Baden, Dättwil AG
25/10/2014 12:00 am	Costruire siti con Wordpress Italian Linux Day 2014, Aosta
06/11/2014 9:00 am - 12:30 pm	Oracle Active Data Guard 12c: Far Sync Instance, Real-Time Cascade Standby, and Other Goodies SOUG-R SIG 11.2014, Continental Hotel, Lausanne, Lausanne
08/12/2014 - 10/12/2014 12:00 am	RAC Attack at UKOUG TECH14 UKOUG Tech 2014, ACC Liverpool, Liverpool
10/12/2014 9:00 am - 9:50 am	Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant UKOUG Tech 2014, ACC Liverpool, Liverpool

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!! 🙂

Boost your Oracle RAC manageability with Policy-Managed Databases

Posted on August 2, 2014 by Ludovico

The slides of my presentation about Policy-managed databases. I’ve used them to present at Collaborate14 (#C14LV).

The same abstract has been refused by OOW14 and UKOUG_TECH14 selection committees, so it’s time to publish them 🙂

In-memory Columnar Store hands-on

Posted on July 24, 2014 by Ludovico

As I’ve written in my previous post, the inmemory_size parameter is static, so you need to restart your instance to activate it or change its size. Let’s try to set it at 600M.

SQL> show parameter inmem

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0

SQL> alter system set inmemory_size=600M scope=spfile;

SQL> shutdown

...

SQL> startup

...

SQL> show parameter inmem

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_query                       string      ENABLE
inmemory_size                        big integer 608M

SQL> show parameter inmem

NAME TYPE VALUE

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

inmemory_clause_default string

inmemory_force string DEFAULT

inmemory_query string ENABLE

inmemory_size big integer 0

SQL> alter system set inmemory_size=600M scope=spfile;

SQL> shutdown

...

SQL> startup

...

SQL> show parameter inmem

NAME TYPE VALUE

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

inmemory_clause_default string

inmemory_force string DEFAULT

inmemory_query string ENABLE

inmemory_size big integer 608M

First interesting thing: it has been rounded to 608M so it works in chunks of 16M. (to be verified)

Which views can you select for further information?

SQL> select view_name from dba_views where view_name like 'V_$IM%';

VIEW_NAME
--------------------------------------------------------------------------------
V_$IM_SEGMENTS_DETAIL
V_$IM_SEGMENTS
V_$IM_USER_SEGMENTS
V_$IM_TBS_EXT_MAP
V_$IM_SEG_EXT_MAP
V_$IM_HEADER
V_$IM_COL_CU
V_$IM_SMU_HEAD
V_$IM_SMU_CHUNK
V_$IM_COLUMN_LEVEL

10 rows selected

SQL> select view_name from dba_views where view_name like 'V_$IM%';

VIEW_NAME

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

V_$IM_SEGMENTS_DETAIL

V_$IM_SEGMENTS

V_$IM_USER_SEGMENTS

V_$IM_TBS_EXT_MAP

V_$IM_SEG_EXT_MAP

V_$IM_HEADER

V_$IM_COL_CU

V_$IM_SMU_HEAD

V_$IM_SMU_CHUNK

V_$IM_COLUMN_LEVEL

10 rows selected

V$IM_SEGMENTS gives a few information about the segments that have a columnar version, including the segment size, the actual memory allocated, the population status and other compression indicators.

The other views help understand the various memory chunks and the status for each column in the segment.

Let’s create a table with a few records:

SQL> create table ludovico.tinmem
as
select
a.OWNER ,
a.TABLE_NAME ,
b.owner owner2,
b.table_name table_name2,
a.TABLESPACE_NAME ,
a.STATUS ,
a.PCT_FREE ,
a.PCT_USED ,
a.INI_TRANS ,
a.MAX_TRANS ,
a.INITIAL_EXTENT ,
a.NEXT_EXTENT ,
a.MIN_EXTENTS ,
a.MAX_EXTENTS ,
a.PCT_INCREASE ,
a.FREELISTS ,
a.FREELIST_GROUPS ,
a.LOGGING
22 from all_tables a, all_tables b;

Table created.

SQL> select count(*) from ludovico.tinmem;

COUNT(*)
----------
5470921

SQL>

SQL> create table ludovico.tinmem

select

a.OWNER ,

a.TABLE_NAME ,

b.owner owner2,

b.table_name table_name2,

a.TABLESPACE_NAME ,

a.STATUS ,

a.PCT_FREE ,

a.PCT_USED ,

a.INI_TRANS ,

a.MAX_TRANS ,

a.INITIAL_EXTENT ,

a.NEXT_EXTENT ,

a.MIN_EXTENTS ,

a.MAX_EXTENTS ,

a.PCT_INCREASE ,

a.FREELISTS ,

a.FREELIST_GROUPS ,

a.LOGGING

22 from all_tables a, all_tables b;

Table created.

SQL> select count(*) from ludovico.tinmem;

COUNT(*)

----------

5470921

SQL>

The table is very simple, it’s a cartesian of two “all_tables” views.

Let’s also create an index on it:

SQL> create index ludovico.tinmem_ix1 on ludovico.tinmem (table_name, pct_increase);

 SQL> select segment_name, bytes/1024/1024 from dba_segments where owner='LUDOVICO';

SEGMENT_NAME      BYTES/1024/1024
----------------- ---------------
TINMEM                        621
TINMEM_IX1                    192

SQL> create index ludovico.tinmem_ix1 on ludovico.tinmem (table_name, pct_increase);

SQL> select segment_name, bytes/1024/1024 from dba_segments where owner='LUDOVICO';

SEGMENT_NAME BYTES/1024/1024

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

TINMEM 621

TINMEM_IX1 192

The table uses 621M and the index 192M.

How long does it take to do a full table scan almost from disk?

SQL> select distinct tablespace_name from ludovico.tinmem order by 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
USERS

Elapsed: 00:00:15.05

SQL> select distinct tablespace_name from ludovico.tinmem order by 1;

TABLESPACE_NAME

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

SYSAUX

SYSTEM

USERS

Elapsed: 00:00:15.05

15 seconds! Ok, I’m this virtual machine is on an external drive 5400 RPM… 🙁

Once the table is fully cached in the buffer cache, the query performance progressively improves to ~1 sec.

SQL> r
1* select distinct tablespace_name from ludovico.tinmem order by 1

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
USERS


Elapsed: 00:00:01.42
SQL> r
1* select distinct tablespace_name from ludovico.tinmem order by 1

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
USERS


Elapsed: 00:00:00.99

SQL> r

1* select distinct tablespace_name from ludovico.tinmem order by 1

TABLESPACE_NAME

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

SYSAUX

SYSTEM

USERS

Elapsed: 00:00:01.42

SQL> r

1* select distinct tablespace_name from ludovico.tinmem order by 1

TABLESPACE_NAME

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

SYSAUX

SYSTEM

USERS

Elapsed: 00:00:00.99

There is no inmemory segment yet:

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION

2 FROM V$IM_SEGMENTS; 

no rows selected

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION

2 FROM V$IM_SEGMENTS;

no rows selected

You have to specify it at table level:

SQL> alter table ludovico.tinmem inmemory;

Table altered.

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
FROM V$IM_SEGMENTS; 2

OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
INMEMORY INMEMORY_COMPRESS
-------- -----------------
LUDOVICO
TINMEM
HIGH     FOR QUERY

SQL> alter table ludovico.tinmem inmemory;

Table altered.

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION

FROM V$IM_SEGMENTS; 2

OWNER

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

SEGMENT_NAME

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

INMEMORY INMEMORY_COMPRESS

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

LUDOVICO

TINMEM

HIGH FOR QUERY

The actual creation of the columnar store takes a while, especially if you don’t specify to create it with high priority. You may have to query the table before seeing the columnar store and its population will also take some time and increase the overall load of the database (on my VBox VM, the performance overhead of columnar store population is NOT negligible).

Once the in-memory store created, the optimizer is ready to use it:

SQL> explain plan for select distinct tablespace_name from ludovico.tinmem order by 1;

Explained.


SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1243998285

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    13 | 26132   (2)| 00:00:02 |
|   1 |  SORT UNIQUE                |        |     1 |    13 | 25993   (1)| 00:00:02 |
|   2 |   TABLE ACCESS INMEMORY FULL| TINMEM |  5470K|    67M|    26 (100)| 00:00:01 |
--------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for select distinct tablespace_name from ludovico.tinmem order by 1;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

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

Plan hash value: 1243998285

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 26132 (2)| 00:00:02 |

| 1 | SORT UNIQUE | | 1 | 13 | 25993 (1)| 00:00:02 |

| 2 | TABLE ACCESS INMEMORY FULL| TINMEM | 5470K| 67M| 26 (100)| 00:00:01 |

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

9 rows selected.

The previous query now takes half the time on the first attempt!

SQL> select distinct tablespace_name from ludovico.tinmem order by 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
USERS

Elapsed: 00:00:00.50

SQL> select distinct tablespace_name from ludovico.tinmem order by 1;

TABLESPACE_NAME

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

SYSAUX

SYSTEM

USERS

Elapsed: 00:00:00.50

The columnar store for the whole table uses 23M out of 621M, so the compression ratio is very good compared to the non-compressed index previously created!

SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, INMEMORY_SIZE, BYTES, BYTES_NOT_POPULATED, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION

2 from V$IM_SEGMENTS;

OWNER    SEGMENT_NAME SEGMENT_TYPE INMEMORY_SIZE BYTES     BYTES_NOT_POPULATED INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
-------- ------------ ------------ ------------- --------- ------------------- -------- --------------- -----------------
LUDOVICO TINMEM       TABLE        23527424      651165696 0                   HIGH     AUTO DISTRIBUTE FOR QUERY

Elapsed: 00:00:00.07

SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, INMEMORY_SIZE, BYTES, BYTES_NOT_POPULATED, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION

2 from V$IM_SEGMENTS;

OWNER SEGMENT_NAME SEGMENT_TYPE INMEMORY_SIZE BYTES BYTES_NOT_POPULATED INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS

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

LUDOVICO TINMEM TABLE 23527424 651165696 0 HIGH AUTO DISTRIBUTE FOR QUERY

Elapsed: 00:00:00.07

This is a very short example. The result here (2x improvement) is influenced by several factors. It is safe to think that with “normal” production conditions the gain will be much higher in almost all the cases.
I just wanted to demonstrate that in-memory columnar store is space efficient and really provides higher speed out of the box.

Now that you know about it, can you live without? 😛

Oracle Database 12c in-memory option, a quick overview

Posted on July 23, 2014 by Ludovico

Oracle Database 12.1.0.2 is finally out, and as we all knew in advance, it contains the new in-memory option.

I think that, despite its cost ($23k per processor), this is another great improvement! 🙂

Consistent savings!

This new feature is not to be confused with Times Ten. In-memory is a feature that enable a new memory area inside the SGA that is used to contain a columnar organized copy of segments entirely in memory. Columnar stores organize the data as columns instead of rows and they are ideal for queries that involve a few columns on many rows, e.g. for analytic reports, but they work great also for all extemporary queries that cannot make use of existing indexes.

Columnar stores don’t replace traditional indexes for data integrity or fast single-row look-ups, but they can replace many additional indexes created for the solely purpose of reporting. Hence, if from one side it seems a waste of memory, on the other side using in-memory can lead to consistent memory savings due to all the indexes that have no more reason to exist.

Let’s take an example of a table (in RED) with nine indexes (other colors).

If you try to imagine all the blocks in the buffer cache, you may think about something like this:

Now, with the in-memory columnar store, you can get the rid of many indexes because they’ve been created just for reporting and they are now superseded by the performance of the new feature:

In this case, you’re not only saving blocks on disk, but also in the buffer cache, making room for the in-memory area. With columnar store, the compression factor may allow to easily fit your entire table in the same space that was previously required for a few, query-specific indexes. So you’ll have the buffer cache with traditional row-organized blocks (red, yellow, light and dark blue) and the separate in-memory area with a columnar copy of the segment (gray).

The in-memory store doesn’t make use of undo segments and redo buffer, so you’re also saving undo block buffers and physical I/O!

The added value

In my opinion this option will have much more attention from the customers than Multitenant for a very simple reason.

How many customers (in percentage) would pay to achieve better consolidation of hundreds of databases? A few.

How many would pay or are already paying for having better performance for critical applications? Almost all the customers I know!

Internal mechanisms

In-memory is enabled on a per-segment basis: you can specify a table or a partition to be stored in-memory.

Each column is organized in separate chunks of memory called In Memory Compression Units (IMCU). The number of IMCUs required for each column may vary.

Each IMCU contains the data of the column and a journal used to guarantee read consistency with the blocks in the buffer cache. The data is not modified on the fly in the IMCU, but the row it refers to is marked as stale in a journal that is stored inside the IMCU itself. When the stale data grows above a certain threshold the space efficiency of the columnar store decreases and the in-memory coordinator process ([imco]) may force a re-population of the store.
Re-population may also occur after manual intervention or at the instance startup: because it is memory-only, the data actually need to be populated in the in-memory store from disk.

Whether the data is populated immediately after the startup or not, it actually depends on the priority specified for the specific segment. The higher the priority, the sooner the segment will be populated in-memory. The priority attribute also drives which segments would survive in-memory in case of “in-memory pressure”. Sadly, the parameter inmemory_size that specifies the size of the in-memory area is static and an instance restart is required in order to change it, that’s why you need to plan carefully the size prior to its activation. There is a compression advisor for in-memory that can help out on this.

Conclusion

In this post you’ve seen a small introduction about in-memory. I hope I can publish very soon another post with a few practical examples.