This article presents how to configure physical standby database for Oracle Database 11G release 2 (11.2). It’s not focusing on transportation method, duplication method, protection modes nor extra functionality available for physical standbys. All above will be part of future articles :).

This presentation is based on the following article Install Oracle 11G Release 2 (11.2) on Oracle Linux 7 (OEL7).

Architecture

Final standby configuration is very simple:

• one host: oel7.dbaora.com – already configured with latest binaries Oracle Database 11G
• primary database: ORA11G
• listener LISTENER – already installed
• standby database: SORA11G – this article shows how to configure it
  
• standby configuration will work in default mode MAXIMUM PERFORMANCE

Configuration consists of the following steps:

For primary database

• prepare space for archivelog and turn on archivelog mode
• switch to force logging on database level or tablespace level
• add standby redo logs
• prepare initialization parameters
• prepare database for quick backups

General steps

• network configuration

For standby database

• generate standby controlfile(on primary database)
• copy password file from primary database
• copy spfile(from primary database) as pfile and modify parameters
• prepare directory structure
• duplicate primary database
• start recover process

Before starting

Modify default prompt for sqlplus to show where code is executed. Modify following file.

[oracle@oel7 ~]$

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

and put there following line

SET SQLPROMPT "_user'@'_connect_identifier'> '"

so every time you connect to a database you should see more details about your connection

--so for following connection
export ORACLE_SID=ORA11G
sqlplus / as sysdba

--default prompt should like this
SQL>

--new prompt looks like this :)
SYS@ORA11G>

It will help identify where commands are executed and by who

Configuration steps

Prepare space for archivelogs on primary database. Increase default settings to 8GB in db recovery area.

SYS@ORA11G>

SELECT name, value 
  FROM v$parameter
 WHERE name LIKE 'db_recovery%';

NAME                       VALUE
---------------------      ------------------------------
db_recovery_file_dest     /ora01/app/oracle/recovery_area
db_recovery_file_dest_size  4560M

ALTER SYSTEM SET db_recovery_file_dest_size=8196m SCOPE=BOTH;

Turn on archivelog mode on primary database ORA11G

SYS@ORA11G>

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

SELECT log_mode, open_mode 
  FROM v$database;

LOG_MODE     OPEN_MODE
------------ ------------
ARCHIVELOG   READ WRITE

Turn on FORCE LOGGING on primary database ORA11G. It’s optional step however it allows to avoid problems with unrecoverable transactions executed on primary database that are later applied on standby database. There is option to specify it on tablespace level instead of database (usually used in warehouses)

SYS@ORA11G>

ALTER DATABASE FORCE LOGGING;

SELECT log_mode, open_mode, force_logging 
  FROM v$database;

LOG_MODE     OPEN_MODE   FORCE_LOGGING
------------ ----------- ---------------
ARCHIVELOG   READ WRITE  YES

Add standby logfiles on primary database ORA11G

SYS@ORA11G> 

SELECT group#, type 
  FROM v$logfile;

    GROUP# TYPE
---------- -------
     3 ONLINE
     2 ONLINE
     1 ONLINE

ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 500m;

SELECT group#, type 
  FROM v$logfile;

    GROUP# TYPE
---------- -------
     3     ONLINE
     2     ONLINE
     1     ONLINE
     4     STANDBY
     5     STANDBY
     6     STANDBY
     7     STANDBY

Set instance parameters for primary database ORA11G. Following parameters are already set on my database

• DB_NAME – specify name of database when it was created
• DB_UNIQUE_NAME – specify unique name for database. It sets parameter INSTANCE_NAME(if not set manually) to the same value.
• LOG_ARCHIVE_FORMAT – specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%r)
• REMOTE_LOGIN_PASSWORD – must be set to EXCLUSIVE or SHARED

SYS@ORA11G>

SELECT name, value 
  FROM v$parameter 
 WHERE name IN
  ( 'db_name', 
    'db_unique_name', 
    'log_archive_format', 
    'remote_login_passwordfile' )
ORDER BY NAME;

NAME                         VALUE
---------------------------  ---------------
db_name                      ORA11G
db_unique_name               ORA11G
log_archive_format           %t_%s_%r.dbf
remote_login_passwordfile    EXCLUSIVE

set extra parameters on primary database ORA11G

• LOG_ARCHIVE_CONFIG – specify all databases in data guard configuration. it is list of all DB_UNIQUE_NAME separated by comma
• LOG_ARCHIVE_DEST_n – specify where redo data are archived
  • LOG_ARCHIVE_DEST_1 – valid for both roles. Specify where to store redo data generated by the primary database in local archived redo log files
  • LOG_ARCHIVE_DEST_2 – is valid only for the primary role. This destination transmits redo data to the remote physical standby destination

SYS@ORA11G>
 
ALTER SYSTEM SET 
LOG_ARCHIVE_CONFIG ='DG_CONFIG=(ORA11G,SORA11G)' 
SCOPE=BOTH;

ALTER SYSTEM SET 
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ORA11G' 
SCOPE=BOTH;
  
ALTER SYSTEM SET 
LOG_ARCHIVE_DEST_2=
 'SERVICE=SORA11G ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=SORA11G' 
SCOPE=BOTH;

Set extra parameters when primary database ORA11G becomes standby. It requires to restart database.

• FAL_SERVER – specify server name where to request missing archived logs
• DB_FILE_NAME_CONVERT – specify how to convert datafile names from primary database
• LOG_FILE_NAME_CONVERT – specify how to convert logfile names from primary database
• STANDBY_FILE_MANAGEMENT – if set to AUTO files added or dropped on primary database are automatically added or dropped on standby

SYS@ORA11G>
 
ALTER SYSTEM SET 
FAL_SERVER = SORA11G 
SCOPE=BOTH;

ALTER SYSTEM SET 
DB_FILE_NAME_CONVERT = '/SORA11G/','/ORA11G/' 
SCOPE=SPFILE;
 
ALTER SYSTEM SET 
LOG_FILE_NAME_CONVERT = '/SORA11G/','/ORA11G/' 
SCOPE=SPFILE;
 
ALTER SYSTEM SET 
STANDBY_FILE_MANAGEMENT = AUTO 
SCOPE=BOTH;

STARTUP FORCE;

Create standby control file on primary database ORA11G for standby database SORA11G. It’s optional step and during cloning method used in this article the generated control file won’t be used but it’s worth to know this method in case you need to restore standby control file.

SYS@ORA11G>
 
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/sora11.ctl';

Create pfile from spfile for standby database on primary database ORA11G

SYS@ORA11G>
 
CREATE PFILE='/tmp/initSORA11G.ora' FROM SPFILE;

Modify generated standby pfile.

!!! Be careful here to avoid mistakes !!!

[oracle@oel7 ~]$

vi /tmp/initSORA11G.ora

*.audit_file_dest='/ora01/app/oracle/admin/SORA11G/adump'
*.control_files=
  '/ora01/app/oracle/oradata/SORA11G/control01.ctl',
  '/ora01/app/oracle/recovery_area/SORA11G/control02.ctl'
*.db_file_name_convert='/ORA11G/','/SORA11G/'
*.db_name='ORA11G'
*.db_unique_name='SORA11G'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GXDB)'
*.fal_server='ORA11G'
*.log_archive_config='DG_CONFIG=(ORA11G,SORA11G)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=SORA11G'
*.log_archive_dest_2='SERVICE=ORA11G ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=ORA11G'
*.log_file_name_convert='/ORA11G/','/SORA11G/'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

copy the modified standby pfile to $ORACLE_HOME/dbs

[oracle@oel7 ~]$

cp /tmp/initSORA11G.ora $ORACLE_HOME/dbs

copy primary database password file for standby password file

[oracle@oel7 ~]$

cp $ORACLE_HOME/dbs/orapwORA11G $ORACLE_HOME/dbs/orapwSORA11G

To make successful cloning proper directories must be created

[oracle@oel7 ~]$

--directories used by standby instance SORA11G
mkdir -p /ora01/app/oracle/admin/SORA11G/adump
mkdir -p /ora01/app/oracle/admin/SORA11G/dpdump
mkdir -p /ora01/app/oracle/admin/SORA11G/pfile

--directories for standby database files
mkdir /ora01/app/oracle/oradata/SORA11G
mkdir /ora01/app/oracle/fast_recovery_area/SORA11G

Modify $ORACLE_HOME/network/admin/listener.ora file

File before modifications

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /ora01/app/oracle

Modify it. If you are lazy you can use netmgr tool :).

[oracle@oel7 ~]$

vi $ORACLE_HOME/network/admin/listener.ora

File after modifications. Both ORA11G and SORA11G are using static registration in the LISTENER.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA11G.dbaora.com)
      (ORACLE_HOME = /ora01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORA11G)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SORA11G.dbaora.com)
      (ORACLE_HOME = /ora01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = SORA11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /ora01/app/oracle

Reload listener

[oracle@oel7 ~]$

lsnrctl reload

Modify $ORACLE_HOME/network/admin/tnsnames.ora

Before modifications

ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA11G.dbaora.com)
    )
  )

Modify it

[oracle@oel7 ~]$

vi $ORACLE_HOME/network/admin/tnsnames.ora

After modifications

SORA11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SORA11G.dbaora.com)
    )
  )

ORA11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA11G.dbaora.com)
    )
  )

check response from listeners

[oracle@oel7 ~]$ tnsping ora11g

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = oel7.dbaora.com)(PORT = 1521))) 
(CONNECT_DATA = (SERVER = DEDICATED) 
(SERVICE_NAME = ORA11G.dbaora.com)))
OK (0 msec)

[oracle@oel7 ~]$ tnsping sora11g

Used TNSNAMES adapter to resolve the alias
Attempting to contact 
(DESCRIPTION = (ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = oel7.dbaora.com)
(PORT = 1521))) 
(CONNECT_DATA = (SERVICE_NAME = SORA11G.dbaora.com)))
OK (0 msec)

Connect to standby database SORA11G using OS authentication then generate spfile from pfile and start database in nomount mode.

[oracle@oel7 ~]$

export ORACLE_SID=SORA11G
sqlplus / as sysdba

SYS@SORA11G>

CREATE SPFILE FROM PFILE;
STARTUP NOMOUNT;

In case you have received “ORA-00845: MEMORY_TARGET not supported on this system” during staring standby database increase your host memory. You can as well try to change database memory parameters (decrease them) if you are short with memory.

It’s time to clone primary database to standby database.

Use rman to connect primary database ORA11G(target database) and to standby database SORA11G(auxiliary database).

[oracle@oel7 admin]$ rman

RMAN> CONNECT TARGET sys@ORA11G
target database Password: 
connected to target database: ORA11G (DBID=242648173)

RMAN> CONNECT AUXILIARY sys@SORA11G
auxiliary database Password: 
connected to auxiliary database: ORA11G (not mounted)

once connected start duplicate command

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

start recovery mode on standby database SORA11G

SYS@SORA11G>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

verify recovery process on standby database SORA11G

SYS@SORA11G>
 
SELECT client_process, process, thread#, sequence#, status 
  FROM v$managed_standby 
 WHERE client_process='LGWR' or process='MRP0'
ORDER BY PROCESS;

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
N/A     MRP0            1          29 APPLYING_LOG
LGWR     RFS            1          29 IDLE

check protection mode – as default it’s set to MAXIMUM PERFORMANCE

SYS@ORA11G>

SELECT protection_mode 
  FROM v$database;

PROTECTION_MODE    
--------------------
MAXIMUM PERFORMANCE

switch logfile on primary database ORA11G

SYS@ORA11G> 

ALTER SYSTEM SWITCH LOGFILE;

it’s applied on standby. Previously archivelog sequence was 29.

SYS@SORA11G>
 
SELECT client_process, process, thread#, sequence#, status 
  FROM v$managed_standby 
 WHERE client_process='LGWR' or process='MRP0'
ORDER BY PROCESS;

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
N/A     MRP0            1          30 APPLYING_LOG
LGWR     RFS            1          30 IDLE

Have a fun 🙂

Tomasz