Creating and configuring Oracle standby without using Oracle Data Guard

This post will be of interest to those who are faced with the task of setting up Oracle standby, but for some reason the Data Guard extension is missing (usually Enterprise Edition is required for its operation, but in many cases you can find Standard Edition). How to set up standby with Data Guard and what it is all about – you can read in this wonderful post. I, in turn, will tell you how to do it in Spartan normal conditions.

So, given:

  • Server/VM with CentOS 7 and Oracle Standard Edition DBMS (without Data Guard) under primary

  • Similar VM server/clone with CentOS 7 and Oracle Standard Edition DBMS under standby

Task:

Further in the text there will be examples of commands and SQL queries. At the beginning, you will see the following symbols:

oracle$ – the command is entered in the command shell bash from user Oracle;
root$ – the command is entered in the command shell bash from user root;
SQL> – the command is entered in the console sql plusrunning in administrator mode (sqlplus / as sysdba);
RMAN> – the command is entered into rman, launched with the command rman target /;
#
— comment/explanation to the command.

First of all, you need to make sure that the main database is running in mode archivelog:

SQL> select name, open_mode, log_mode from v$database;

If there is no ARCHIVELOG value in the LOG_MODE field, you must switch the database to this mode:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Next, you need to configure the channel through which the primary server will transmit archive logs to standby. This can be done using a remote NFS storage, to configure it on the Primary server you will need:

# Установить NFS, подготовить директорию для записи архивлогов, 
# сделать владельцем директории пользователя oracle группы oracle
root$ yum install nfs-utils -y
root$ mkdir /mnt/logs_for_standby
root$ chown -R oracle:oracle /mnt/logs_for_standby

# Сделать backup файла /etc/exports на всякий случай 
# Записать конфигурацию NFS в файлик /etc/exports, вместо <IP_STANDBY> 
# подставить реальный IP-адрес standby-сервера
root$ cp /etc/exports /etc/exports.backup
root$ echo "/mnt/logs_for_standby <IP_STANDBY>(rw,sync,no_root_squash)" > /etc/exports 

# Включить службу NFS
root$ systemctl enable nfs-server
root$ systemctl start nfs-server

# Добавить правила firewall для работы службы и обновить firewall
root$ firewall-cmd --permanent --zone=public --add-service=nfs
root$ firewall-cmd --permanent --zone=public --add-service=mountd
root$ firewall-cmd --permanent --zone=public --add-service=rpc-bind
root$ firewall-cmd --reload

Next, set up the standby server:

# Подготовить директорию, в которую будем монтировать NFS-шару, 
# сделать владельцем директории пользователя oracle группы oracle
root$ mkdir /mnt/archivelog 
root$ chown -R oracle:oracle /mnt/archivelog 

# Добавить правила firewall для работы службы и обновить firewall
root$ firewall-cmd --permanent --zone=public --add-service=nfs
root$ firewall-cmd --permanent --zone=public --add-service=mountd
root$ firewall-cmd --permanent --zone=public --add-service=rpc-bind
root$ firewall-cmd --reload

# Примонтировать NFS-шару в созданную директорию 
# Вместо <IP_PRIMARY> подставить реальный IP-адрес primary-сервера
root$ mount -t nfs <IP_PRIMARY>:/mnt/logs_for_standby /mnt/archivelog

To check that NFS is working, you can drop it on the primary server into the directory /mnt/logs_for_standby any file, after performing the above steps, it should appear in the folder /mnt/archivelog on the standby server. After setting up NFS and verifying that it works, you can move on to setting up the DBMS.

First, let’s make changes to pfile on the primary server (instead of you need to substitute the value of the $ORACLE_HOME environment variable):

# Переходим в директорию, где хранится spfile, делаем его backup, 
# создаем из него pfile "initTEST.ora" (SID нашей базы - TEST)
oracle$ cd $ORACLE_HOME/dbs
oracle$ cp spfileTEST.ora spfileTEST.ora.backup
oracle$ sqlplus / as sysdba
SQL> create pfile="<ORACLE_HOME>/dbs/initTEST.ora" from spfile;
SQL> exit
oracle$ vim initTEST.ora

After opening the file initTEST.ora To edit it, add the following lines:

*.log_archive_dest_1='LOCATION=/backup/archivelog OPTIONAL REOPEN=300'
*.log_archive_dest_2='LOCATION=/mnt/logs_for_stby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format="arch_%t_%s_%r.arc"

*.log_archive_dest_1 – this is the path for regular Oracle backups, including the archive logs created when creating incremental backups.
*.log_archive_dest_1 — this is the directory where the archive logs should be duplicated (for their use on the standby server). This is the same directory that we shared over NFS.
The following options describe the state of the directories and the format of the archive logs. After changing the file, you need to restart the database instance (instead of , you need to substitute the value of the $ORACLE_HOME environment variable):

oracle$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile="<ORACLE_HOME>/dbs/initTEST.ora";
SQL> create spfile from pfile="<ORACLE_HOME>/dbs/initTEST.ora";
SQL> shutdown immediate;
SQL> startup;
SQL> exit

Perhaps in the future we will want to connect to the standby database from the main server, so we will edit another file tnsnames.ora:

oracle$ cd $ORACLE_HOME/network/admin
oracle$ vim tnsnames.ora

Add the following lines to it (instead of you need to substitute the IP address of the standby server):

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP_STANDBY>)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

The last step on the primary server is to create backups:

$oracle rman target /
RMAN> backup current controlfile for standby format '/backup/standbycontrol.ctl';
RMAN> run
2> {
3> allocate channel c1 device type disk format '/backup/%u';
4> backup database plus archivelog;
5> }

After creating backups on the primary server, they need to be moved to exactly the same directories on the standby server.

At this point, we can say that we have done half the work. Next, go to the standby server and proceed to the DBMS settings. Let’s edit the file first. tnsnames.ora in case we want to connect to both databases:

oracle$ cd $ORACLE_HOME/network/admin
oracle$ vim tnsnames.ora

Add the following lines (instead of and , of course, there must be real IP addresses):

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP_STANDBY>)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP_PRIMARY>)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

Let’s start listener and prepare pfileSTBY.ora (instead of you need to substitute the value of the $ORACLE_HOME environment variable):

oracle$ lsnrctl start LISTENER
oracle$ sqlplus / as sysdba
SQL> create pfile="<ORACLE_HOME>/dbs/pfileSTBY.ora" from spfile;
SQL> exit
oracle$ vim pfileSTBY.ora

V pfileSTBY.ora lines need to be added:

*.control_files="/backup/standbycontrol.ctl" 
*.log_archive_dest_1='LOCATION=/backup/TEST/archivelog OPTIONAL REOPEN=300'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format="arch_%t_%s_%r.arc"
*.standby_archive_dest="/mnt/archivelog"

*.control_files – location of the control file (its backup was created on the primary server and had to be moved to the same directory on standby)
*.standby_archive_dest – the location of the archive logs that will need to be applied on standby (an NFS share is mounted into it)

Before proceeding to the next step, you need to make sure that all paths and files with permissions are present on the standby server. oracle:oraclespecified in pfileSTBY.ora.

We create spfile and start the instance in number-mode (instead of you need to substitute the value of the $ORACLE_HOME environment variable):

oracle$ sqlplus / as sysdba
SQL> startup nomount pfile="<ORACLE_HOME>/dbs/pfileSTBY.ora";
SQL> create spfile from pfile="<ORACLE_HOME>/dbs/pfileSTBY.ora";
SQL> shutdown immediate;
SQL> startup nomount;

Backups from the primary server must be transferred to the same directory on the standby server (possible via NFS), deploy the standby instance from the backup:

oracle$ rman target /
RMAN> restore controlfile from "/backup/standbycontrol.ctl";
RMAN> startup mount;
RMAN> catalog start with '/backup/';
RMAN> restore database;
RMAN> recover database;
RMAN> quit;

Next, we will create an RMAN script that will run on schedule and roll new archivelogs onto standby. You can name the script whatever you like (for example, rman_stby_recover.cmd), it should contain the following lines:

# RECONFIGURE Archivelog deletion policy for safety reasons
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
catalog start with '/mnt/archivelog/' noprompt;

# Wait a little for archive logs to apply
host 'sleep 300';

# Delete applied archivelogs
delete archivelog all;

quit;

You can run the script with the command:

rman target sys/<PASSWORD> cmdfile rman_stby_recover.cmd 

Of course, instead of , the password from SYS. This command can be added to a shell script and called according to a schedule CRON. Thus, the task can be called completed. The archive logs will be transferred over NFS and used on the standby base, keeping it up to date. Then you can experiment with checking the availability of NFS shares, setting up monitoring, etc. (I will not write about this in this post).

That’s all, I hope that the post will be useful to someone. Thank you for your attention, I will be glad to comments and questions.

Similar Posts

Leave a Reply