Configure physical standby database Oracle Database 11G release 2 (11.2)

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.

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
standby_physical_11g_01


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

ARCHIVE LOG   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