DB: Manual standby setup

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, to be configured as a standby

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'
db_recovery_file_dest_size=300G
compatible=12.1.0.2.0
db_files=1000

 

#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 wallet files 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 standby 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 logfiles 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
set encryption off;
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 
set encryption off
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

export LCDB_UNQNAME=osc_fraxxx
export LCDB_UNQNAME_PRIMARY=osc_frayyy

sqlplus / AS SYSDBA <<EOF
connect sys/${LPWD}@${LCDB_UNQNAME} as sysdba;
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

connect sys/${LPWD}@${LCDB_UNQNAME_PRIMARY} as sysdba;
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;
EOF

dgmgrl / <<EOF
REMOVE CONFIGURATION;
CREATE CONFIGURATION osc AS PRIMARY DATABASE IS ${LCDB_UNQNAME_PRIMARY} CONNECT IDENTIFIER IS '${LCDB_UNQNAME_PRIMARY}';
ADD DATABASE ${LCDB_UNQNAME} AS CONNECT IDENTIFIER IS '${LCDB_UNQNAME}';
ENABLE CONFIGURATION;
EOF

 

#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