Oracle 11g R2 Dataguard configuration step by step

Oracle 11g R2 Dataguard configuration step by step, before starting DG configuration you must read the below note.

Subscribe on YouTube

Oracle Database RDBMS software installed with one database on the PRIMARY server and on STANDBY server installed only RDBMS software without any Database.

PRIMARYSTANDBY
IP Address: 192.168.1.10IP Address: 192.168.1.20
DB_NAME=db11gDB_NAME=db11g
DB_UNIQUE_NAME=db11gDB_UNIQUE_NAME=std

Required parameters:

DB_NAME Must be same on primary and on all standby
DB_UNIQUE_NAME Must be different on primary and all standby
LOG_ARCHIVE_CONFIGThis parameter includes db_unique_name which are part of the Dataguard configuration
LOG_ARCHIVE_DEST_nDefine local and remote archive log file location
LOG_ARCHIVE_DEST_STATE_nDefine state of archiving (ENABLE or DIFFER)
REMOTE_LOGIN_PASSWORDFILEMust be in EXCLUSIVE mode
FAL_SERVER Use for archive log gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT Required when directory structure is a different datafile
LOG_FILE_NAME_CONVERTRequired when directory structure is the different log file
STANDBY_FILE_MANAGEMENTKeep auto to create the file automatically on standby

You must read the below articles:

Oracle 19c Data Guard Configuration step by step

How to Convert Physical Standby To Snapshot Standby?

Resolve huge archive gap between primary and standby

Step by Step to configure Oracle 12c Data Guard Physical Standby

Oracle 11g R2 Dataguard Configuration with video step by step

Perform the following steps on the primary database:

Make sure the primary database is running in archive log mode. Check your database mode using the following command:

SQL> select log_mode from v$database;
              OR
SQL> archive log list

If your database is not running in archive log mode use the following command to change to archive mode of your database:

SQL> SHU IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Now your database is running in archive log mode.

Note: – Make sure the database is in force logging mode.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

If not then run the below command to enable force logging.

SQL> ALTER DATABASE FORCE LOGGING;

Now verify DB_NAME and DB_UNIQUE_NAME of the primary database

SQL> show parameter db_name
SQL> show parameter db_unique_name

Now Make DB_UNIQUE_NAME to be part of the data guard. (std service we will create soon)

SQL> alter system set log_archive_config=’DG_CONFIG=(db11g,std)’; 

(db11g primary service name & std standby service name)

Now it’s time to create a TNS service using “netmgr”.

$netmgr –> service add for std (+) –> net service name std –> hostname standby machine ip –> service name std -> save

Start production listener:

$lsnrctl start

Set archive dest 2 location on production.

SQL> alter system set log_archive_dest_2=’service=std
     Valid_for=(online_logfiles, primary_role) db_unique_name=std’;

SQL>alter system set log_archive_dest_state_2=enable;

You must set remote login password to in exclusive mode:

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> show parameter remote_login

Set fail server and file name convert parameter in case if the directory structure is different in primary and standby databases.

SQL> ALTER SYSTEM SET FAL_SERVER=std;
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’std’,’db11g’ scope=spfile;
SQL> ALTER SYSTEM SET LOG_FILES_NAME_CONVERT=’std’,’db11g’ scope=spfile;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Now tack the backup of the primary database using RMAN

$rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create standby control file and pfile

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/stdcontrol.ctl’;
SQL> CREATE PFILE=’/u01/initstd.ora’ from spfile;

Edit backup pfile and make some changes for standby, after making changes the standby pfile look like this:

std.__db_cache_size=318767104
std.__java_pool_size=4194304
std.__large_pool_size=4194304
std.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
std.__pga_aggregate_target=335544320
std.__sga_target=503316480
std.__shared_io_pool_size=0
std.__shared_pool_size=159383552
std.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/fast_recovery_area/std/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='db11g','std'
*.db_name='db11g'
*.db_unique_name='std'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db11gXDB)'
*.fal_server='DB11G'
*.log_archive_config='DG_CONFIG=(db11g,std)'
*.log_archive_dest_2='SERVICE=db11g VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='db11g','std'
*.memory_target=836763648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Create required directories on the standby side.

$mkdir -p /u01/app/oracle/admin/std/adump
$mkdir -p /u01/app/oracle/oradata/std
$mkdir -p /u01/app/oracle/fast_recovery_area/std

After creating an appropriate directory on physical standby and copy the backupset, archive log, pfile, standby control file, and password file to the physical standby database.

#scp /u01/stdcontrol.ctl oracle@192.168.1.20:/u01/app/oracle/oradata/std/control01.ctl
#scp /u01/stdcontrol.ctl oracle@192.168.1.20:/u01/app/oracle/fast_recovery_area/std /control02.ctl

Transfer archive log and backups

#scp –r /u01/app/oracle/fast_recovery_area/DB11G oracle@192.168.1.20:/u01/app/oracle/fast_recovery_area/

Copy Parameter file

#scp /u01/initstd.ora oracle@192.168.1.20:/u01/initstd.ora

Transfer password file

#scp /u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwdb11g oracle@192.168.1.20:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwstd

On physical standby server

Set ORACLE_HOME

$export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1

make TNS service using below the command

$netmgr
 =>service naming
           + add new
              Net service name (db11g)
                      Host name (server ip)
                             Service name (db11g)
 Test your connection then finish
Add one more service for standby server
        + add new
           Net service name (std)
                 Host name (standby ip)
                         Service name (std)
                                    Then finish

Then save click on file => save network configuration

Start listener:

$lsnrctl start

Now update /etc/oratab file on standby machine

$vi /etc/oratab  (add below line in end of file) 

Std:/u01/app/oracle/product/11.2.0.4/db_1:N

Create spfile from pfile:

$export ORACLE_SID=std
$sqlplus / as sysdba
Sql> create spfile from pfile=’/u01/initstd.ora’;

Restore backup on standby machine:

Now exit from SQL prompt and login with RMAN then restore backup

$rman target=/
RMAN>startup mount
RMAN> restore database;
RMAN> exit

Note:- After finishing the restore database we need to create a standby redo log files on the standby server, and it should be one extra either than online redo log file.

$sqlplus / as sysdab
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo01.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo02.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo03.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/std/standby_redo04.log’) size 50m;

Note:- we need to add four redo log files because we have three online redo log files, always add one extra standby log file.

Check your log members and you can confirm using the below command.

SQL> select member from v$logfile  where type=’STANDBY’;
SQL> select member from v$logfile;

Note:- Now we need to create the same online redo log files on the PRIMARY machine also in case you planing switch the role so if your primary become STANDBY then you need to have STANDBY redo log files.

On PRIMARY server and add redo log files.

SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo01.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo02.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo03.log’) size 50m;
SQL> alter database add standby logfile (‘/u01/app/oracle/oradata/db11g/standby_redo04.log’) size 50m;

Check standby redo details:

SQL> select member from v$logfile  where type=’STANDBY’;

Now start the redo apply process on standby

Note: before applying redo log files, open the alert log file on a different terminal for monitoring standby database activity.

On-standby machine:

QL> alter database recover managed standby database disconnect from session;

Run the below command and check the current redo sequence number on primary side:

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

Now switch the log file using the below command and check it’s applying on the standby server or not.

SQL> alter system switch logfile;

Then check your current sequence number on the PRIMARY machine:

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

Then switch on the STANDBY machine and check the redo is coming on the standby machine or not.

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Now going on the PRIMARY machine and run the switch logfile command one more time.

SQL> alter system switch logfile;

Now check DB mode and protection mode run below command on both machines:

SQL> desc v$database
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

Now your Oracle 11g R2 Dataguard configuration is completed.

Steps to configure read-only STANDBY – Action On STANDBY machine

Now I’m going to convert the physical standby database into a read-only standby database. In this case, what happens to your database will be in read-only mode. Let me show you how to convert the physical standby server into read-only mode.

SQL>Shu immediate
SQL>startup mount;
SQL>alter database open read only;

After running the above commands your database will be open in read-only mode.

SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
SQL> select * from scott.emp;   (now you able to read your database)

Now login on the PRIMARY machine and run the switch logfile command.

SQL>alter system switch logfile;

On STANDBY check redo applying or not

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Note:-You can see redo files but it’s not applied, So it is simple if you’re a standby database in read-only mode then archives is not applying.

Let’s bring back to physical standby using the below steps:

SQL> shu immediate
SQL> startup mount
SQL> alter database recover managed standby database disconnect from session;

Now the archive is applied on the standby side.

Active Data Guard

How to convert physical standby database into active data guard step by step.

Oracle 11g has a new feature called ACTIVE DATAGUARD.

In the ACTIVE DATAGUARD feature, we can open the standby database in read-only mode and also can apply log files. Steps are almost the same as a read-only standby database.

SQL>shu immediate
SQL>startup mount;
SQL>alter database open read only;
SQL>alter database recover managed standby database disconnect from session;

Now you can check open mode:

 SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

And check redo apply

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks