Configuring Physical Standby Database Using Data Guard
Primary Database - Dhee
Standby Database - Dheedr
STEP 1:
Check the Primary DB is in archive log mode or not.If it is not then enable it.
Sql>starup mount
Sql>alter database archivelog
Sql>archive log list
STEP 2:
Enable force logging in the Primary Database.
Sql>alter database force logging;
Ensure fore logging enabled or not
Select force_logging from v$database;
STEP 3 :
Verifying the database initialization parameters on primary,
Show parameter db_name
Show parameter db_unique_name
STEP 4:
Set the Log_archive_config for datagaurd configuration.
Sql>alter system set log_archive_config=’DG_config=(dhee,dheedr)’;
Show parameter log_archive_config
STEP 5:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dheedr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dheedr';
Show parameter log_archive_dest_2
Alter system set log_archive_dest_2=enable;
Show parameter log_archive_dest_state_2.
STEP 6:
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile
Show parameter log_archive_format.
STEP 7:
Set log_Archive_max_processes=30
Sql>alter system set log_archive_max_processes=30;
Show parameter log_archive_max_processes
STEP 8:
Set remote_login_passwordfile to exclusive
Sql>alter system set remote_login_passwordfile=exclusive;
Show parameter remote_login_passwordfile
STEP 9:
Set fal_server and fal_client for the primary database.
Sql>alter system set fal_server=dheedr
Sql>alter system set fal_client=dhee
STEP 10:
Set standby_file_management to auto
Sql>alter system set standby_file_management = auto;
Show parameter standby_file_management
STEP 11:
Configuring the listener and tns entries in both the servers
Listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dhee)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)
(SID_NAME = dhee)
) )
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dheedr)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
STEP 12:
Tnsnames.ora:
dhee =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dhee)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dhee)
(GLOBAL_NAME = dhee)
(UR=A)
) )
dheedr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =dheedr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dhee)
(GLOBAL_NAME = dhee)
(UR=A)
) )
STEP 13:
Checking the tnsping utility on Primary and Standby.
Tnsping dhee
Tnsping dheedr
STEP 14:
Backup the primary database via RMAN utility.
RMAN>backup database plus archivelog;
STEP 15:
Create the standby control file on the primary database.
RMAN>alter database create standby controlfil as ‘$ORACLE_HOME/dbs’
Create the passwordfile on the primary database.
Orapwd file=’$ORACLE_HOME/dbs/orapwdhee Password=Doyen@123
Entries=10.
Create pfile from spfile.
Create pfile=’$ORACLE_HOME/dbs/initdhee.ora’ from spfile
STEP 16:
Moving the files like standby controlfile,passwordfile & backup pieces to the standby database through scp connection.
STEP 17:
Connect the database using the pfile by changing some parameters:
*.db_unique_name='dheedr'
*.fal_server='dhee'
*.log_archive_dest_2='SERVICE=dhee ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dhee'
STEP 18:
Taking the database to nomount stage.
Sql>startup nomount pfile=’$ORACLE_HOME/dbs/initdhee.ora’
database to mount stage.
RMAN>restore controlfile from ‘$ORACLE_HOME/dbs/standbyctl.ctl’
STEP 19:
Sql>alter database mount;
STEP 20:
Create the Standby database
RMAN>duplicate database ‘dhee’ for standby backup location ‘$ORACLE_HOME/dbs/’;
Apply the redo logs from the primary to standby database.
STEP 21:
