I’ve created standby databases many times. They are not simple but, once you’ve done it a few times it’s not too bad at least for single host non-RAC standby databases.
But recently, I was working at a Client that used “VIP” hostnames for single host DBs (not RAC) and wanted me to use them… so that their DBs were not tied to physical hosts… novel idea huh? Kind of like RAC VIPs and SCAN but no RAC…
Well Oracle has bugs when you do stuff like this cuz – it’s expecting the physical hostname – I assume / I’m guessing. Here’s one of the problems I ran into.
The duplicate database script for standby started fine but during one of it’s restarts – it kept kicking out an ora-12514 listener does not currently know of service requested in connect descriptor… when doing something like this:
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name=’stbytst’
set standby_file_management=’AUTO’,
set db_file_name_convert…
nofilenamecheck;
I was using the SPFILE clause in the duplicate command and defining the local_listener in the duplicate command – correctly.
To make a long story shorter… I knew the listeners where setup correctly.
Finally, to resolved the issue ON THE STANDBY SIDE, I created an initSID.ora and set the local_listener etc. in the init.ora file then I did the following on the standby to pre-create the spfile on the standby. This allowed me to take the “spfile” clause out of the duplicate command.
cd $ORACLE_HOME/dbs # optional
sqlplus / as sysdba
# connect to an idle instance – no db here yet
startup nomount;
create spfile from pfile; # you could hard code the paths… that is also optional
shutdown immediate;
startup nomount;
show parameter spfile # just to verify you have started with the new spfile
Now run your duplicate database for standby WITHOUT the spfile clause… now everything you need should be in the init.ora you use to create the spfile e.g. db_file_name_convert etc.
duplicate target database
for standby
from active database
dorecover
nofilenamecheck;
and voila… no more ora-12514…
helpful views – v$listener_network