Creating Physical Standby (Oracle DataGuard)

• Share on LinkedIn
• Share on Facebook
• Share on Twitter

PRIMARY SERVER SETUP

Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;

If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters, should be same for PRIMARY
DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.
DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(<PRIMARY>,<STANDBY>)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=<STANDBY> NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<STANDBY>';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
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;
ALTER SYSTEM SET FAL_SERVER=<STANDBY>;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='<STANDBY>’,'<PRIMARY>’ SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='<STANDBY>’,'<PRIMARY>’ SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Restart the database for static parameters to take effect.

Service Setup

Primary=>

tnsnames.ora

<STANDBY> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <STANDBY HOSTNAME/IP>)(PORT = 1521))
(CONNECT_DATA =
(SID = <PRIMARY>)
)
)

<PRIMARY> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <PRIMARY HOSTNAME/IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <PRIMARY>)
)
)

Secondary=>

<STANDBY> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = <STANDBY HOSTNAME/IP>) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <STANDBY>)
)
)
<PRIMARY> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = <PRIMARY HOSTNAME/IP>) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <PRIMARY>)
)
)

Backup Primary Database

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘<temp path>';
CREATE PFILE='<temp path>’ FROM SPFILE;

Amend the PFILE making the entries relevant for the standby database.
*.db_unique_name='<STANDBY>’
*.fal_server='<PRIMARY>’
*.log_archive_dest_2=’SERVICE=<PRIMARY> ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<PRIMARY>’

STANDBY SERVER SETUP

Create the necessary directories on the standby server.
ORADATA and FLASH_RECOVERY_AREA etc.. (AUDIT DUMP)

Copy the files from the primary to the standby server.

# Standby controlfile to all locations.
# Archivelogs and backups
# Parameter file.
# Remote login password file.

Start Listener
$ lsnrctl start

Restore Backup
$ export ORACLE_SID=<PRIMARY>
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='<Copied from PRIMARY>';
$ export ORACLE_SID=<PRIMARY>
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create REDO logs, if not available, select member from V$logfile;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE (‘<PATH>online_redo01.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘<PATH>online_redo02.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘<PATH>online_redo03.log’) SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Create STANBY Redo Logs on both PRIMARY and SECONDARY
SIZE should be same as online redo log files and 1 more in number

ALTER DATABASE ADD STANDBY LOGFILE (‘<PATH>standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘<PATH>standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘<PATH>standby_redo03.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘<PATH>standby_redo04.log’) SIZE 50M;

Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

— Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

— Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

— Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;