Creating manual standby in Oracle 12c SE with PDB/CDB Multitenant architecture

Creating a manual standby with Oracle 12c SE is a cost-effective way to create entry level fault tolerance. With manual standby the worst case scenario is that you lose updates of a couple of last minutes in case of server failure. This depends of how you have configured your database to do log switches and archive log copying to standby server. If you do log switches every 5 minutes and rsync every minute at worst you can loose 6 minutes worth of updates. Manual standby also has to be activated manually. There will be no automatic failover.


With Standard Edition you are only allowed to create manual standby, not Managed Standby. This means you have to copy the archive logs manually and do the recovery manually. If you keep also the standby mounted but not in open mode, you don't have to have a license for a standby server.
The Care and Feeding of a Standby Database

"The Managed Standby normally does not require a separate Oracle License because it is not open for use.  Normally only one database is active at a time.  Notice the use of the word normally.  If you use your standby for reporting or run it open-read only (11g) you will need to licenses the database."
"To create a Managed Standby database, you must be using the Enterprise Edition of the database.  If you are using the Standard Edition of the database, you can still create a manual standby database, but not a Managed Standby."

With Standard Edition  you also can create Pluggable Databases but are only allowed for one PDB per CDB. You will not get all benefits of Multitenant architecture but you will be able to upgrade the database with little less downtime.
Multitenant with Only One Pluggable Database: An Upgrade Option to Consider

"In using a single PDB, the features of plugging and unplugging are available. Being able to do this provides another patching option in that you can unplug the PDB and plug it into an already-patched CDB. This can provide a patching scenario with less downtime and stronger testing plans."
Because the redo logs and archive logs are in the CDB level, the standby database has to be copy of the whole CDB database also. There are no separate redo logs for each PDB so you cannot replicate a single PDB only.


I suggest using at least version 12.1.0.2 because that version contains a feature to automatically open PDB's to open state when CDB is restarted.
12.1.0.2 New Features: PDB State Management Across CDB Restart

I prefer to use a separate staging area for creating a standby database. You can then first create a copy of primary database to staging area and as a separate step create a new standby database from that copy. 

Step 1: Prepare primary database

 

Create Environment file

 

It will be easier to create scripts if you have all the variables of your system in one file. When you then run your scripts, you simply run the environment file before that.
Environment variables I have used:

#ORACLE_SID of primary CDB database
PRIMARY_SID=TEST

# Directory where Standby files are. Same directory in Standby and Primary server.
STANDBYLOGS =/u02/Standby

#ORACLE_SID of standby CDB database
STANDBY_SID=TEST

#Root dir of Oracle database files (for all databases)
ORACLE_DATA_DIR=/u02/oradata

#Root dir of Oracle admin files. 
ORACLE_ADMIN_DIR=/u01/app/oracle/admin

# Primary FRA directory
PRIMARY_FRA=/u02/fast_recovery_area

Mount Standby directory to Primary database. 

 

This directory will be used for transferring copy of data files and archive logs. It is easiest to create a "Standby" directory to Standby server and mount that directory to same name and path in Primary server. Just remember not to use a directory in Primary server because you will then lose that data in server failure.

Remember to check that 'oracle' user has the same user id in both servers so that access permissions are the same for all the directories and files in the shared Standby-directory.

Set Primary Database to Archive Log mode

 

export ORACLE_SID=$PRIMARY_SID
sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

 

Enable force logging

 

alter database force logging;

 

Create and edit standby init.ora


#Get pfile from primary database 
export ORACLE_SID=$PRIMARY_SID
sqlplus / as sysdbaCREATE PFILE='$STANDBYLOGS/init$PRIMARY_SID.Primary.ora' FROM SPFILE;
quit;
 
cp  $STANDBYLOGS/init$PRIMARY_SID.Primary.ora
$STANDBYLOGS/init$STANDBY_SID.Standby.ora

Edit init$STANDBY_SID.Standby.ora

Change controlfiles to standby controlfile location. Remember to change directories, you cannot use Unix variables in here. 
*.control_files='/u02/oradata/TEST/controlfile/standby1.ctl', '/u02/oradata/TEST/controlfile/standby2.ctl','/u02/oradata/TEST/controlfile/standby3.ctl'

Change FRA  to standby's FRA
*.db_recovery_file_dest='/u02/standby/fast_recovery_area'

It's easiest (IMHO) to use listener configured in tnsnames.ora. (TEST is the database sid.)
*.local_listener='LISTENER_TEST'

Configure listener alias in tnsnames.ora in primary and standby servers. Both have their own hostname in HOST setting(servername.domainname) . TEST is the database sid. This way you don't have to set hostname's in init.ora's.
LISTENER_TEST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = servername.domainname)(PORT = 1521))

 

No database autostart in primary server

 

Please check there is no autostart of production database in primary server. If there's is a failure in primary server and standby is activated, there is no quick turning back to primary database. Primary database has to be recreated from currently active standby database meaning you have to stop standby, take backup and move that backup to primary database. Therefore if somehow primary server is back online, and primary database is autostarted, the users start to use it instead of standby and you end up with two databases used simultaneously that are then not in sync.

 

Step 2: Create copy of primary database

 


These commands run in primary server will create a file copy of primary database online. No need for shutdown and downtime. But this is a backup so I advice not to run this at a busy daytime hours.

#Switch log file and start backup
sqlplus / as sysdba << END
alter system archive log current;
alter system switch logfile;
alter database begin backup;
quit;
END
 

# Take backup of datafiles
# This takes a backup of all datafiles in CDB and PDBrm -rf $STANDBYLOGS$ORACLE_DATA_DIR/$PRIMARY_SID
mkdir -p $STANDBYLOGS$ORACLE_DATA_DIR/$PRIMARY_SID
cp -r $DATA_DIR/* $STANDBYLOGS$ORACLE_DATA_DIR/$PRIMARY_SID
rm -rd $STANDBYLOGS$ORACLE_DATA_DIR/$PRIMARY_SID/onlinelog
rm -rf $STANDBYLOGS
$ORACLE_DATA_DIR/$PRIMARY_SID/controlfile

# Stop backup and create standby control file
sqlplus / as sysdba << END
alter database end backup;
 
rm -rf $STANDBYLOGS/standby_$PRIMARY_SID.ctl
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '$STANDBYLOGS/standby_$PRIMARY_SID.ctl';
# Create also pfile. We don't need it but it's good to have a copy in case you need to check quickly parameters. 

CREATE PFILE='$STANDBYLOGS/init$PRIMARY_SID.Primary.ora' FROM SPFILE;
alter system archive log current;
alter system switch logfile;

quit;
END

 

Step 3:Create standby database from primary database copy

 

These commands are run in standby server and they create the standby database.

If you activate standby, it creates an automatic backup. If you after that create the standby database again, you cannot start recovery when RMAN registers that old backup. The standby database has then different incarnation than primary database.Therefore when we recreate the standby, we have to remove any earlier backups from standby's FRA.

#Shutdown standby database if it exists 
export ORACLE_SID=$STANDBY_SID
lsnrctl stop listener
sqlplus / as sysdba << END
shutdown immediate;
quit;
END
 

# Create directories and copy files
rm -rf $
ORACLE_DATA_DIR/$STANDBY_SID
mkdir -p $
ORACLE_DATA_DIR/$STANDBY_SID
cp -r $STANDBYLOGS$
ORACLE_DATA_DIR/$PRIMARY_SID/* $ORACLE_DATA_DIR/$STANDBY_SID
mkdir $
ORACLE_DATA_DIR/$STANDBY_SID/onlinelog

# Copy control files
mkdir -p $
ORACLE_DATA_DIR/$STANDBY_SID/controlfile
cp $STANDBYLOGS/standby_$1.ctl $
ORACLE_DATA_DIR/$STANDBY_SID/controlfile/standby1.ctl
cp $STANDBYLOGS/standby_$1.ctl $
ORACLE_DATA_DIR/$STANDBY_SID/controlfile/standby2.ctl
cp $STANDBYLOGS/standby_$1.ctl $
ORACLE_DATA_DIR/$STANDBY_SID/controlfile/standby3.ctl
#
mkdir -p $ADMIN_DIR/$STANDBY_SID/pfile
# DB Parameter file
cp $STANDBYLOGS/init$STANDBY_SID.Standby.ora $ADMIN_DIR/$STANDBY_SID/pfile


# Remove earlier standby backups  

rm -rf $STANDBYLOGS/fast_recovery_area/$STANDBY_SID/autobackuprm -rf $STANDBYLOGS/fast_recovery_area/$STANDBY_SID/onlinelog

# Avaa kanta
sqlplus / as sysdba << END
STARTUP NOMOUNT pfile='$ADMIN_DIR/$STANDBY_SID/pfile/init$STANDBY_SID.Standby.ora';
create spfile from pfile='$ADMIN_DIR/$STANDBY_SID/pfile/init$STANDBY_SID.Standby.ora';
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL;
alter database recover cancel;
END

 

Step 4:Keeping up with the primary database


Copy archive logs to standby

 

In primary server you have to run in cron these commands to replicate archive logs to shared standby directory.

mkdir -p $STANDBYLOGS/fast_recovery_area/$STANDBY_SID/archivelog
rsync -aqz --delete $PRIMARY_FRA/
$PRIMARY_SID/archivelog/ $STANDBYLOGS/fast_recovery_area/$STANDBY_SID/archivelog

 

Apply Archive logs to standby

 

In standby server you have to run redo apply in cron.

# Register the archive logs found in FRA
export ORACLE_SID=$STANDBY_SID
rman << EOF
connect target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
quit;
EOF
 

# Apply recover to standby
sqlplus / as sysdba > /dev/null << END
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL;
alter database recover cancel;
quit;


 

Delete used archive logs

 

You also have to delete old archive logs from primary database but that depends how you backup the primary database. I prefer to keep archive logs for a week. That way you can always create a standby database again from a week old copy and just apply the archive logs from a week. It will take a long time, but at least you dont't have to do anything heavy in primary server.

 

Step 5. Activating the standby. 


I'm not posting complete instructions of failover to standby because in real world situations at first you try to get primary back online first. Then you try to get as much archive logs out of primary server as possible and then you start to activate standby server.

In testing you can activate the standby this way:
sqlplus / AS SYSDBA << END
ALTER DATABASE ACTIVATE STANDBY DATABASE;
shutdown immediate
startup;
END

 

Conclusion

 

Using multitenant architecture with manual standby needed only minor changes in scripts, mainly in the location of datafiles. In Non-CDB database all the datafiles normally are in one /datafile-directory. In multitenant architecture the CDB datafiles are in /datafile-directory and each PDB(in this case only one) has it's own directory where /datafile -subdirectory resides.

Comments