This note describes how to create, manually, a standby database for a 12cR2 primary database running on a DBsystem on Oracle Cloud Infrastructure.
#1 Provision two DBsystems with preferably the same database name, both with Entreprise Edition to benefits from the DG broker feature. At the end of the provisioning, put down their unique database name. For this note, the primary is db01_fra3bs running from the db01p host, and the secondary is db01_fra34d, running from the db01s host.
Note that the database name could be different, say chicago and boston, for the same db_name being network, but it is probably more elegant and more intuitive to keep the same prefix.
#2 Drop the second database
sqlplus -s / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
STARTUP MOUNT RESTRICT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
DROP DATABASE;
EOF
#3 Create a new init.ora file initdb01_init for the secondary node, here db01_fra34d
db_name=db01
db_unique_name=db01_fra34d
enable_pluggable_database=true
db_create_file_dest='+DATA'
db_create_online_log_dest_1='+DATA'
db_recovery_file_dest='+RECO'
#4 Start the secondary database in nomount, using the spfile
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE
create spfile='?/dbs/spfiledb01.ora' from pfile='?/dbs/initdb01_init.ora';
startup nomount;
EOF
#5 Copy the password file from the primary
cd $ORACLE_HOME/dbs 10.x.x.x:/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapwdb01 .
#6 Copy the walletfile from the primary
cd /opt/oracle/dcs/commonstore/wallets/tde
mv db01_fra34d db01_fra34d.old
mkdir db01_fra34d
cd db01_fra34d
scp 10.x.x.x:/opt/oracle/dcs/commonstore/wallets/tde/db01_fra3bs/* .
#7 Add a first static TNS entry in the grid listener.ora file on the stanmdby server for the duplicate command to work and a second for the DG broker
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db01_fra34d.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = db01)
)
(SID_DESC =
(GLOBAL_DBNAME = db01_fra34d_dgmgrl.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = db01) )
)
#8 Add a static on the primary server for the DG broker
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db01_fra3bs_dgmgrl.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = db01)
)
)
#9 Reload the grid listener on both the primary and standby server
lnrctl reload
#10 Add the following entries on the primary TNS tnsnames.ora file
db01_fra34d =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = db01s.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01_fra34d.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
)
)
#11 Add the following entries in the standby TNS tnsnames.ora file
db01_fra3ds =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = db01p.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01_fra3ds.subxxxxxxxxxxx.vcnxxxxxxxxxx.oraclevcn.com)
)
)
#12 From the primary, check that both connection are working properly:
sqlplus / AS SYSDBA
connect sys/<pwd>@db01_fra3bs as sysdba
connect sys/<pwd>@db01_fra34d as sysdba
EOF
#13 Add standby logfilles on the primary
sqlplus / as sysdba <<EOF
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
alter database add standby logfile thread 1 '+RECO' size 1024M;
EOF
#14 Start the duplicate command
rman target sys/<pwd>@db01_fra3bs auxiliary sys/<pwd>@db01_fra34d <<EOF
run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate channel prim3 type disk;
allocate channel prim4 type disk;
allocate auxiliary channel stdby1 type disk;
duplicate target database for standby from active database dorecover;
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
}
EOF
#15 Alternate duplicate command
The following command may also consolidate the 3 latest commands into a single one
rman target sys/<pwd>@db01_fra3bs auxiliary sys/<pwd>@db01_fra34d <<EOF
run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate channel prim3 type disk;
allocate channel prim4 type disk;
allocate auxiliary channel stdby1 type disk;
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
duplicate target database for standby from active database dorecover;
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel stdby1 "alter database add standby logfile thread 1 ''+RECO'' size 1024M";
sql channel prim1 "alter system archive log current";
sql channel stdby1 "alter database recover managed standby database disconnect";
}
EOF
#16 Restart the standby in active dg mode
sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
EOF
#17 Setup DG
sqlplus / AS SYSDBA <<EOF
connect sys/<pwd>@db01_fra3bs as sysdba
ALTER SYSTEM SET log_archive_config='dg_config=(db01_fra3bs,db01_fra34d)' SCOPE=BOTH;
connect sys/<pwd>@db01_fra34d as sysdba
ALTER SYSTEM SET log_archive_config='dg_config=(db01_fra3bs,db01_fra34d)' SCOPE=BOTH;
connect sys/<pwd>@db01_fra3bs as sysdba
ALTER SYSTEM SET log_archive_dest_2='service=db01_fra34d async valid_for=(all_logfiles,primary_role) db_unique_name=db01_fra34d' SCOPE=BOTH;
connect sys/<pwd>@db01_fra34d as sysdba
ALTER SYSTEM SET log_archive_dest_5='service=db01_fra3bs async valid_for=(all_logfiles,primary_role) db_unique_name=db01_fra3bs' SCOPE=BOTH;
EOF
The specific order above is to avoid occasional ORA-16047 warnings.
#18 Check the DG status
dgmgrl <<EOF
connect sys/<pwd>
show configuration
validate database db01_fra3bs;
validate database db01_fra34d;
EOF
Make sure there is no error or warning.
#19 Test a switchover
dgmgrl
DGMGRL> connect sys/<pwd>
DGMGRL> switchover to db01_fra34d
