Saturday , May 28 2022

How To Setup Physical Standby Database

In today’s article, we will look for an answer to the question of How To Setup Physical Standby Database.

In this article, DataGuard installation on Primary and Standby databases in Oracle 11g R2 architecture is explained using SQL*Plus and RMAN without using OEM.

Operating system   : RedHat 5.9
Database Software : Oracle 11g R2
Cluster Software     : Oracle 11g R2 Grid Infrastructure
Storage Structure   : Oracle ASM
Server RAM             : 12G
Server CPU              : 4 cores

1. First, we install RedHat Linux 5.9 O.S on both the Primary and Standby sides on all four NODEs.

2. We are making the necessary configuration changes on the Primary and Standby RAC nodes.

3. We install Grid Infrastructure on all RAC nodes on both the Primary and Standby sides.

4. We need to install RDBMS software on all RAC nodes on both the Primary and Standby sides.

5. We should install Oracle 11 g R2 database only on the Primary side.

6. We created new listeners named EMR_LISTENER in both databases separately.

You can find the installations at dbtut.com.

7. Information about the installed systems is as follows.

Primary party information.

Standby party information.

8.The point to be noted here is that both Grid Infrastructure and new listener EMR_LISTENER, Oracle RDBMS software and Oracle database are installed on the Primary side.

On the standby side, Grid Infrastructure and Oracle RDBMS are installed. On the standby side, the database is not installed.

9.Installation checklist;
Primary database will be “FORCE LOGGING”.
SPFILE will be used in the primary database.
There will be Standby Redo Log files in the primary database.
For standby servers, a non-case sensitive password file will be created with the Password File ignorecase=y parameter.
Necessary directories will be created on the standby side.

Primary database must be in ARCHIVE MODE. If it is not in ARCHIVE MODE, we can put it in archive mode as follows. (Step 1 – Primary)

First, we check if the database is in archive mode. (Primary)

We determine where the database archive log files will be saved (for the FRA field)  (Primary 1);

We determine where to save database archive log files (File System Location);

If the database is not in archive mode, we can put it in archive mode as follows.

Now let’s check the database, is it in archive mode? (Primary 1-2)

We perform a log switch operation to check whether it is archived to the correct location. (Primary 1)

Primary database must be FORCE LOGGING. If the FORCE LOGGING feature of the database is not active, we need to activate it. (Step 2 – Primary)
This is important because logging can still be performed even if users run queries with the NOLOGGING hint.

If the FORCE LOGGING feature is not active in the database as above, we can activate it as follows. Since this parameter is activated for newly created sessions, the connected sessions must be terminated first.

For this, the database is closed and brought to mount mode. (Primary)

Now we check again. (Primary)

We configure the Redo Log transport direction in the database from the Primary database to the Standby database. (Step 4 – Primary)

We can see the config change made as follows.

The “LOG_ARCHIVE_FORMAT” parameter of the Database is set. (Step 5 – Primary)

We set FAL_SERVER. This parameter is required after Switchover. Since the Standby database will be Primary after Switchover, we write Standby because the flow of ARCHIVEs will be from it. (Primary)

A STANDBY REDO LOG is created in the primary database. The number of Standby Redo Logs created is equal to the number of Redo Logs in our database. (Step 3 – Primary)

First, let’s look at the number and size of REDO LOGs we have.

Now we are creating a Standby Redo Log (Primary).

Now let’s check the Standby log status(Primary).

The database is initialized to set the parameters (Primary).

15.  The primary database should be backed up and we will create a directory to put this backup file (Primary1 – Oracle).

16.   We create a PFILE parameter file for the Standby database from the parameter file. (Primary)

17.   In this step, we will edit the TNS files. In the figure below, all 4 NODEs must be present. Otherwise, we will get an error as follows;

On the primary side, we update the tnsnames.ora file on both NODEs. And the result is as follows (Step 6 – Primary 1 – 2).

We transfer the PFILE parameter file that we previously created for Standby to the Standby Node-1 side. (Primary)

Then the BACKUP files are moved to the Standby side (Primary 1 – Oracle).

3.Password files on the primary side are transferred to the Standby side with SCP (Primary 1 – Oracle).

4. We must create DUMP directories on the standby side (Standby 1 – 2).

5.Now we are making updates to the Standby PFILE file we created earlier. (Standby1) ** No errors here.

6. We copy the TNS files to the Standby side (Step 7 – Primary).

7. We need to create ASM folders. (Standby 1)

8. TNS files have been updated. On all nodes. (Standby 1) (Standby 2)

9. This process will be done on standby 1.hhuyanlab.localhost. (Standby 1)

10. We check the remote connection and parameters to the database with TNS. This will be done on standby1.hhuyanlab.local host. (Standby1)

11. Then, we open the database in mount mode on the standby side. This will be done on standby1.hhuyanlab.local host. (Standby1)

12.   We will connect to two databases at the same time from the standby side and complete the database copying process. This will be done on standby1.hhuyanlab.local host. (Standby1)

13.   We will copy the database RESTORE operation with RMAN using the DUPLICATE DATABASE option. For this process, in the previous step, we connected to PRIMARY, which is the TARGET database, and to STANDBY, which is the AUXILIARY database, via standby1.hhuyanlab.local host. (Standby1)

14. We create an SPFILE parameter file from the PFILE parameter file in the standby database. (Standby1)

15. Then, on the standby side, we update the PFILE parameter files on both NODEs as follows. (Standby 1 – 2)

16.   Now we complete the process of adding the Standby database (Standby 1 – Oracle). (in a new SESSION)

If it does not open;

At this stage, the database did not stand up. By connecting both nodes one by one, I opened the database with the following command (Standby1 – 2).

17.   Now we can check the database. (Standby 1)

18. We are checking the Primary side now. (Primary 1)

19.   Finally, we activate the Standby Database by running the command below (Standby1 or Standby2).

20. We check the Primary and Standby side.

21. We control the Primary and Standby side.

 

 

 

Onur ARDAHANLI
Author: Onur ARDAHANLI

About Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *