PRIMARY SERVER SETUP

Step 1:- 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;

Step 2:- Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

Step 3:- 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.

Step 4:- Service Setup

Primary=>

Step 4a:- Editing tnsnames.ora

=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
(CONNECT_DATA =
(SID = )
)
)

=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)

Secondary=>

=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = ) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)

=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = ) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)

Step 5:- Backup Primary Database

$ rman target=/

RMAN BACKUP DATABASE PLUS ARCHIVELOG;

Step 6:- Create Standby Controlfile and PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '';
CREATE PFILE='' FROM SPFILE;

Step 7:- Amend the PFILE making the entries relevant for the standby database.

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

 

STANDBY SERVER SETUP

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

Step 9:- Copy the files from the primary to the standby server.

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

Step 10:- Start Listener

$ lsnrctl start

Step 11:- Restore Backup

$ export ORACLE_SID= $ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='';

$ export ORACLE_SID= $ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Step 12:- Create REDO logs, if not available, select member from V$logfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Step 13:- 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 ('standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('standby_redo04.log') SIZE 50M;

Step 14:- Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

That’s all, you have successfully configured Physical Standby.

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;

In case of any issues following these steps to configure a physical standby in your env. please feel free to comment so that we can accordingly assist you.