Oracle Data Guard: Creating a Physical Standby Database

Posted: April 21, 2014 in Database Administration, High Availability
Tags:

Good morning, Oracle addicts!

I have been promoting the use of OEM12C as a hands free, easy approach to configure and create Oracle Data Guard on your most important databases. Yesterday, in a “round table” interview with several other DBAs, I was called on my bluff. This individual asked me what steps I would take in order to configure both the standby and the primary database for Standby Database Creation if you couldn’t use the OEM. Without access to my notes and the latest Oracle Documentation on the subject, I was unprepared to answer the question. As I have stated before, if it can be done better with OEM12C, I am going to do it with 12C. One side affect of this philosophy is we can lose the valuable knowledge that is ascertained by doing it the old fashioned way. So I am going to answer his question on my blog, albeit a few days after the fact.

The below link is Oracle’s Documentation on the subject. It is a great read but it is very lengthy.

http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

Oracle® Data Guard Concepts and Administration
11g Release 2 (11.2)

I am also referencing an article on Oracle-Base that is much more user friendly. If I had to use a shell script, just in case OEM12C was not up to the task, this is where I would begin.

http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php

The below reference is how to set up Data Guard with OEM11g Grid Control. Let’s face  it folks, the future is now. Using the OEM is the preferred method if you have any kind of deadline. With OEM12C, you don’t even need a graphical, step by step guide. Just navigate to the database you want to create a standby for, then Availability, then Add a standby database. It will guide you through the process.

http://www.oracle-base.com/articles/11g/data-guard-setup-using-grid-control-11gr2.php

However, I know there is still a need to know how to do it manually, with  shell script, so I am summarizing Tim Hall with Oracle Base as well. This is not to be confused with Tim Gorman of EVDBT, whom I refer to frequently.

Requirements: Before you begin, there are a few requirements that need to be addressed.

  1. You must have two physical or virtual servers with an operating system and the Oracle Database installed. I am currently using Linux(SLES11) and Oracle 11.2.0.3.
  2. The Primary server must have a running instance.
  3. The Standby server must, at a minimum, have a software only installation.

Set up the Primary Server:

  • Logging

Ensure that the primary database is in archivelog mode. If it is in noarchivelog mode, switch to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
  • Initialization Parameters
  1. Check the settings for DB_NAME and DB_UNIQUE_NAME
SQL> show parameter db_name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 DB_TST
SQL> show parameter db_unique_name
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DB_TST
SQL>

 

2.  The DB_NAME will be the same for the primary and standby database but they must have a different DB_UNIQUE_NAME.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB_TST,DB_TST_STBY)';

3. Set remote archive log destinations.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db_tst_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

4. Set LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

5. It is recommended that you make sure the primary is ready to switch rolse to become a standby.

ALTER SYSTEM SET FAL_SERVER=DB_TST_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB_TST_STBY','DBTST' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB_TST_STBY','DBTST'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
  • Service Setup

Make entries in the tnsnames.ora files on both servers.

DBTST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbtst01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbstdby01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DBTST.WORLD)
    )
  )
  • Backup Primary Database
$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
  • Create Standby Controlfile and Pfile
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/dbtst_stby.ctl';
CREATE PFILE='/tmp/initDBTST_stby.ora' FROM SPFILE;

Amend the PFILE making entries relevant for the standby database.

*.db_unique_name='DBTST_STBY'
*.fal_server='DBTST'
*.log_archive_dest_2='SERVICE=dbtst ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTST'

Set up the Standby Server (Manual)

  • Copy Files
  1. Create the directories on the standby server and copy the files to the standby server.
$ mkdir -p /u01/app/oracle/oradata/DBTST
$ mkdir -p /u01/app/oracle/fast_recovery_area/DBTST
$ mkdir -p /u01/app/oracle/admin/DBTST/adump

$ # Standby controlfile to all locations.
$ scp oracle@dbtst01:/tmp/dbtst_stby.ctl /u01/app/oracle/oradata/DBTST/control01.ctl
$ cp /u01/app/oracle/oradata/DBTST/control01.ctl /u01/app/oracle/fast_recovery_area/DBTST/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@dbtst01:/u01/app/oracle/fast_recovery_area/DBTST/archivelog /u01/app/oracle/fast_recovery_area/DBTST
$ scp -r oracle@dbtst01:/u01/app/oracle/fast_recovery_area/DBTST/backupset /u01/app/oracle/fast_recovery_area/DBTST

$ # Parameter file.
$ scp oracle@dbtst01:/tmp/initDBTST_stby.ora /tmp/initDBTST_stby.ora

$ # Remote login password file.
$ scp oracle@dbtst01:$ORACLE_HOME/dbs/orapwDBTST $ORACLE_HOME/dbs
  • Start the listener on the standby server
$ lsnrctl start
  • Restore Backup by first creating an SPFILE from the amended PFILE
$ export ORACLE_SID=DBTST
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initDBTST_stby.ora';

$ export ORACLE_SID=DBTST
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
  • Create Redo Logs
  1. Create online redo logs for the standby database.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DBTST/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DBTST/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DBTST/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

2. Create standby redo logs on both the standby and the primary database to account for switchovers.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DBTST/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DBTST/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DBTST/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DBTST/standby_redo04.log') SIZE 50M;

Start Apply Process

  • Start the apply process on the standby server
# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  • If you need to cancer the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  • You can set a delay between the arrival of the archived redo log and it being applied on the standby server using…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
  • Start real-time apply using…

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Test Log Transport

  • Check the latest archived redo log on the primary server and force a log switch
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

  • Check the new archived redo log has arrived at the standby server and been applied.

Choose Your Protection Mode

  • Maximum Protection: This mode ensures that zero data loss occurs if a primary database fails. **Because this mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used with this protection mode.**
  • Maximum Performance: This is the default mode for a standby database. This mode provides the highest lever of data protection without affecting the performance of a primary database.
  • Maximum Availability: This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database.

 Congratulations!! If you have made it this far, you have created your first standby database. As always, save your script in your filesystem for future use.

At this point it is advisable to allow OEM to discover your new target or targets. This will allow you to perform Switchover or Failover Manually. Only a SYSDBA should have the requisite privilege to perform this operation. 

To perform Switchover manually…

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database…

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

In the event of a failover, the primary database may not be available. To activate the standby database as a primary database…

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Immediately backup the new primary database.

It is also recommended that you enable flashback on the primary database. In the case of a failover, the failover renders the original primary useless for converting to a standby database. If flashback is enabled, the primary can be flashed back to the time before the failover and quickly converted to a standby database.

Again, Tim Hall at Oracle-Base or the Oracle Documentation are the primary resource for this article. Having said that, I am here to help wherever possible. I blog frequently and check this blog for feedback daily. Thank you for those who challenged me to research the non-OEM way to set up Data Guard. It has made me a better DBA and, hopefully, it will make you a better DBA as well.

Thanks for reading!!!

~Jason

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s