Configure physical standby database Oracle Database 12C release 1 (12.1)

This article presents how to configure physical standby database for Oracle Database 12C release 1 (12.1). 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 12C Release 1 (12.1) on Oracle Linux 6 (OEL6).

Architecture

Final standby configuration is very simple:

  • one host: oel6.dbaora.com – already configured with latest binaries Oracle Database 12C
  • primary database: ORA12C – already installed container database has one pluggable database PORA12C1 and SEED template database
  • listener LISTENER – already installed
  • standby database: SORA12C – this article shows how to configure it
  • standby configuration will work in default mode MAXIMUM PERFORMANCE

standby_physical_12c_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@oel6 ~]$

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=ORA12C
sqlplus / as sysdba

--default prompt should like this
SQL>

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

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@ORA12C>

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 ORA12C

SYS@ORA12C>

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 ORA12C. It’s optional step however it allows to avoid problems with unrecoverable transactions executed on primary database that are alter applied on standby. There is option to specify it on tablespace level instead of database (usually used in warehouses)

SYS@ORA12C>

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 ORA12C

SYS@ORA12C> 

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 ORA12C. 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_PASSWORDFILE – must be set to EXCLUSIVE or SHARED
SYS@ORA12C>

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                      ORA12C
db_unique_name               ORA12C
log_archive_format           %t_%s_%r.dbf
remote_login_passwordfile    EXCLUSIVE

set extra parameters on primary database ORA12C

  • 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@ORA12C>
 
ALTER SYSTEM SET 
LOG_ARCHIVE_CONFIG ='DG_CONFIG=(ORA12C,SORA12C)' 
SCOPE=BOTH;

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

Set extra parameters when primary database ORA12C 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@ORA12C>
 
ALTER SYSTEM SET 
FAL_SERVER = SORA12C 
SCOPE=BOTH;

ALTER SYSTEM SET 
DB_FILE_NAME_CONVERT = '/SORA12C/','/ORA12C/' 
SCOPE=SPFILE;
 
ALTER SYSTEM SET 
LOG_FILE_NAME_CONVERT = '/SORA12C/','/ORA12C/' 
SCOPE=SPFILE;
 
ALTER SYSTEM SET 
STANDBY_FILE_MANAGEMENT = AUTO 
SCOPE=BOTH;

STARTUP FORCE;

Create standby control file on primary database ORA12C for standby database SORA12C. 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@ORA12C>
 
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/sora12.ctl';

Create pfile from spfile for standby database on primary database ORA12C

SYS@ORA12C>
 
CREATE PFILE='/tmp/initSORA12C.ora' FROM SPFILE;

Modify generated standby pfile.

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

[oracle@oel6 ~]$

vi /tmp/initSORA12C.ora

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

copy the modified standby pfile to $ORACLE_HOME/dbs

[oracle@oel6 ~]$

cp /tmp/initSORA12C.ora $ORACLE_HOME/dbs

copy primary database password file for standby password file

[oracle@oel6 ~]$

cp $ORACLE_HOME/dbs/orapwORA12C $ORACLE_HOME/dbs/orapwSORA12C

To make successful cloning proper directories must be created

[oracle@oel6 ~]$

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

--directories for standby database container files
mkdir /ora01/app/oracle/oradata/SORA12C
mkdir /ora01/app/oracle/recovery_area/SORA12C

--directories for seed template and pluggable databases

mkdir /ora01/app/oracle/oradata/SORA12C/pdbseed
mkdir /ora01/app/oracle/oradata/SORA12C/PORA12C1

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

File before modifications

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

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

[oracle@oel6 ~]$

vi $ORACLE_HOME/network/admin/listener.ora

File after modifications. Both ORA12C and SORA12C are using static registration in the LISTENER.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA12C.dbaora.com)
      (ORACLE_HOME = /ora01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = ORA12C)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = SORA12C.dbaora.com)
      (ORACLE_HOME = /ora01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = SORA12C)
    )
  )

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

ADR_BASE_LISTENER = /ora01/app/oracle

Reload listener

[oracle@oel6 ~]$

lsnrctl reload

Modify $ORACLE_HOME/network/admin/tnsnames.ora

Before modifications

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

Modify it

[oracle@oel6 ~]$

vi $ORACLE_HOME/network/admin/tnsnames.ora

After modifications

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

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

check response from listeners

[oracle@oel6 ~]$ tnsping ora12c
TNS Ping Utility for Linux: Version 12.1.0.2.0 - 
Production on 05-DEC-2014 21:49:25
Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/ora01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora

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

[oracle@oel6 ~]$ tnsping sora12c
TNS Ping Utility for Linux: Version 12.1.0.2.0 - 
Production on 05-DEC-2014 21:49:29
Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/ora01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora

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

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

[oracle@oel6 ~]$

export ORACLE_SID=SORA12C
sqlplus / as sysdba

SYS@SORA12C>

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. I had to increase my virtual box memory to 6GB :). 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 ORA12C(target database) and to standby database SORA12C(auxiliary database).

[oracle@oel6 admin]$ rman

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

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

once connected start duplicate command

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

start recovery mode on standby database SORA12C

SYS@SORA12C>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;

verify recovery process on standby database SORA12C

SYS@SORA12C>
 
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@ORA12C>

SELECT protection_mode 
  FROM v$database;

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

switch logfile on primary database ORA12C

SYS@ORA12C> 

alter system switch logfile;

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

SYS@SORA12C>
 
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

 

3 thoughts on “Configure physical standby database Oracle Database 12C release 1 (12.1)

  1. Hi, Thomasz, how are you..

    Well, you description it’s much more completed in present day..However this step “RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE; ” is very important while description is display screen, because the new DBA have not experience about the passage and development..Another observation it’s..This execution is in same server with one VM instance with different names?..If yes, Okay..Understood..
    Thanks a lot.

    regards

  2. I have been searching for a good instruction to set up my first physical standby in the past weeks and this is the best with all the details. Thank you very much for the sharing. Jim

  3. Hello Tomasz.
    Thanks a lot for this guide – it helps me bringing up my 1st physical Standby DB.
    There’s a tiny fuzziness in the section
    “Set instance parameters for primary database”: In your text you wrote:
    “REMOTE_LOGIN_PASSWORD – must be set to …” instead of “REMOTE_LOGIN_PASSWORDFILE – must be set to …”
    Greetings and THX very much!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.