DBA survival BLOG

DBA stuff and Oracle Data Guard

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

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

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 🙂

RAC Attack 12c arrive en Suisse en Septembre!

Posted on June 27, 2014 by Ludovico

carte_suisse - Copie Après Oracle Open World, IOUG Collaborate et d’autres grandes conférences, RAC Attack arrive également à Genève! Installez l’environnement Oracle 12c RAC sur votre laptop. Des volontaires expérimentés (ninjas) vous aideront à résoudre
toutes les énigmes apparentés et vous guideront à travers le processus
d’installation.

Ninjas
Ludovico Caldara – Oracle ACE, RAC SIG European Chair & RAC Attack co-writer
Luca Canali – OAK Table Member & frequent speaker
Eric Grancher – OAK Table member
Jacques Kostic – OCM 11g & Senior Consultant at Trivadis

Où? nouveaux bureaux Trivadis, Chemin Château-Bloch 11, CH1219 Geneva
Quand? Mercredi 17 September 2014, dès 17h00
Coût? C’est un évènement GRATUIT! C’est un atelier communautaire, plaisant et
informel. Vous n’avez qu’à apporter votre laptop et votre bonne humeur!
Inscription: TVD_LS_ADMIN@trivadis.com

Places limitées! Réservez votre place & votre T-shirt dès à présent: TVD_LS_ADMIN@trivadis.com

Agenda:
17.00 – Bienvenue
17.30 – RAC Attack 12c – 1ere partie
19.30 – Pizza et Bières! (sponsorisés par Trivadis)
20.00 – RAC Attack 12c – 2eme partie
22.00 – distribution des T-shirt et photo de groupe!!

TRES IMPORTANT: La participation à cet évènement requière l’apport de votre propre laptop!
Spécifications requises:
a) 64 bit OS qui supporte Oracle Virtual Box
b) 8GB RAM, 50GB free HDD space.
En raison de contraintes juridiques, merci de télécharger à l’avance Oracle Database 12c ainsi que Grid Infrastructure pour Linux x86-64 depuis https://edelivery.oracle.com/ (et pour
plus d’informations : http://goo.gl/pqavYh).

RAC Attack comes to Switzerland in September!!

Posted on June 27, 2014 by Ludovico

carte_suisse - Copie After Oracle Open World, IOUG Collaborate and all major conferences, RAC Attack comes to Geneva! Set up Oracle 12c RAC environment on your laptop. Experienced volunteers (ninjas) will help you address any related issues and guide you through the setup process.

Ninjas
Ludovico Caldara – Oracle ACE, RAC SIG European Chair & RAC Attack co-writer
Luca Canali – OAK Table Member and frequent speaker
Eric Grancher – OAK Table member
Jacques Kostic – OCM 11g & Senior Consultant at Trivadis

Where? new Trivadis office, Chemin Château-Bloch 11, CH1219 Geneva
When? Wednesday September 17th 2014, from 17h00 onwards
Cost? It is a FREE event! It is a community based, informal and enjoyable workshop.
You just need to bring your laptop and your desire to have fun!
Registration: TVD_LS_ADMIN@trivadis.com

Limited places! Reserve your seat and T-shirt now: TVD_LS_ADMIN@trivadis.com

Agenda:
17.00 – Welcome
17.30 – RAC Attack 12c part I
19.30 – Pizza and Beers! (kindly sponsored by Trivadis)
20.00 – RAC Attack 12c part II
22.00 – T-shirt distribution and group photo!!

VERY IMPORTANT: To participate in the workshop, you need to bring your own laptop.
Required specification:
a) any 64 bit OS that supports Oracle Virtual Box
b) 8GB RAM, 50GB free HDD space.
Due to legal constraints, please pre-download Oracle Database 12c and Grid Infrastructure for Linux x86-64 from https://edelivery.oracle.com/ web site (further
information here: http://goo.gl/pqavYh).

Oracle Database Backup Logging Recovery Appliance – a preview

Posted on May 3, 2014 by Ludovico

Please see the disclaimer at the end of the post.

Oracle has announced the new Oracle Database Backup Logging Recovery Appliance at the last Open World 2013, but since then it has not been released to the market yet, and very few information is available on the Oracle website.

During the last IOUG Collaborate 14, Oracle master product manager of Data Guard and MAA, Larry Carpenter, has unveiled something more about the DBRLA (call it “Debra” to simplify your life 🙂 ) , and I’ve had the chance to discuss about it directly with Larry.

At Trivadis we think that this appliance will be a game changer in the world of backup management.

Why?

Well, if you have ever worked for a big company with many hundreds of databases, you have certainly encountered many of those common problems:

Oracle Backup and restore penalized by a shared infrastructure
Poor backup or restore performance
Tape drives busy when you need them urgently
Complex management of backup retentions

That’s not all. As of now, your best recovery point in case of restore is directly related to your backup archive frequency. Oh yes, you have to low down your archive_lag_target parameter, increase your log switch frequency (and thus, the I/O) and still have… 10, 15, 30 minutes of possible data loss? Unless you protect your transactions with a Data Guard. But this will cost you money. For the additional server and storage. For the licenses. And for the effort required to put in place a Data Guard instance for every database that you want to protect. You want to protect your transactions from a storage failure and there’s a price to pay.

The Database Backup Logging Recovery Appliance (wow, I need to copy and paste the name to save time! :-)) overcomes these problems with a simple but brilliant idea: leveraging the existing redo log transport processes and ship the redo stream directly to the backup appliance (the DBLRA, off course) or to its Cloud alter ego, hosted by Oracle.

As you can infer from the picture, 12c databases will work natively with the appliance, while previous releases will have a plugin that will enable all the capabilities.

Backups can be mirrored selectively to another DBLRA, or copied to the cloud or to a 3rd party (Virtual) Tape Library.

The backup retention is enforced by the appliance and the expiration and deletion is done automatically using the embedded RMAN catalog.

Lightning fast backups and restores are guaranteed by the hardware: DBLRA is based on the same hardware used by Exadata, with High Capacity disks. Optional storage extensions can be added to increase the capacity, but all the data, as I’ve said, can be offloaded to VTLs in order to use a cheaper storage for older backups.

To resume, the key values are:

No transaction loss!!
Lightning fast backups and restores
Integrated, Oracle engineered, scalable solution for hundreds to thousands of databases

Looking forward to see it in action!

I cannot cover all the information I have in a single post, but Trivadis is working actively to be ready to implement it at the time of the launch to the market (estimated in 2014), so feel free to contact me if you are interested in boosting your backup environment. 😉

By the way, I expect that the competitors (IBM, Microsoft?) will try to develop a solution with the same characteristics in terms of reliability, or they will lose terrain.

Cheers!

Ludovico

Disclaimer: This post is intended to outline Oracle’s general product direction based on the information gathered through public conferences. It is intended for informational purposes only. The development and release of these functionalities and features including the release dates remain at the sole discretion of Oracle and no documentation is available at this time. The features and commands shown may or may not be accurate when the final product release goes GA (General Availability).
Please refer Oracle documentation when it becomes available.