Oracle Info Hub: July 2018

Monday, 30 July 2018

Step by Step Physical Standby database configuration in 12C.

This document describes how to configure Physical Standby database in 12C

Assumptions:

Two servers (Physical or VMs)
One Server(Primary) having oracle software and database installed and is running
Second server(Standby) having only oracle binaries (software) installed.

Primary database setup:

Step 1: Check database is running in archivelog mode. If not enable it. Please click Here to find how to change database mode from noarchivelog to Archivelog.

SQL> select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11

Next log sequence to archive 13

Current log sequence 13

Step 2: Enable force logging by issuing below command.

SQL> select name,force_logging from v$database;

NAME FORCE_LOGGING

--------- ---------------------------------------

PRIMARY NO

SQL> alter database force logging;

Database altered.

SQL> select name,force_logging from v$database;

NAME FORCE_LOGGING
--------- ---------------------------------------
PRIMARY YES

Step 3: Verify the parameters db_name and db_unique_name in primary database. In my case both db_name and db_unique_name are same. I.e., PRIMARY

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string PRIMARY

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PRIMARY

Step 4: Configure log_archive_config parameter in primary database.

SQL> alter system set log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)';

System altered.

SQL> show parameter log_archive_config

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(PRIMARY,STANDBY)

Step 5: Configure log_archive_dest_2 parameter in primary database. This refers to standby database archive location. In my case it is at flash recovery area in standby database.

SQL> alter system set log_archive_dest_2='service=STANDBY noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=STANDBY';

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=STANDBY noaffirm async
valid_for=(online_logfiles,pr
imary_role) db_unique_name=STA
NDBY

Step 6: Set below parameters in primary database to configure physical standby.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> show parameter log_archive_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf

SQL> alter system set log_archive_max_processes=30;

System altered.

SQL> show parameter log_archive_max_processes

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_max_processes integer 30

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

remote_login_passwordfile string EXCLUSIVE

SQL> alter system set fal_server=STANDBY;

System altered.

SQL> show parameter fal_server

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_server string STANDBY

SQL> alter system set fal_client=PRIMARY;

System altered.

SQL> show parameter fal_client;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fal_client string PRIMARY

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

standby_file_management string AUTO

Step 7: Bounce the database.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1560281088 bytes

Fixed Size 2924784 bytes

Variable Size 939527952 bytes

Database Buffers 603979776 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

Step 8: Add standby database tns details in tnsnames.ora file of Primary database and make sure that tnsping is happening fine.

TNS entry in Primary database:

tnsnames.ora file location: $ORACLE_HOME/network/admin

[oracle@primary admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary.apps.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PRIMARY.apps.com)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.apps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = STANDBY.apps.com)

)

[oracle@primary admin]$ tnsping primary

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2018 09:45:27

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.apps.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIMARY.apps.com)))

OK (0 msec)

[oracle@primary admin]$ tnsping standby

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2018 09:45:31

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.apps.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = STANDBY.apps.com)))

OK (0 msec)

Step 9: Create standby control file in Primary database.

SQL> alter database create standby controlfile as '/u02/backup/stndycntrl01.ctl';

Database altered.

Step 10: Create pfile from spfile.

SQL> create pfile='/u02/backup/initPRIMARY' from spfile;

File created.

Step 11: Modify the above created pfile and copy it to standby database.

*.db_unique_name='STANDBY'

*.fal_server='PRIMARY'

*.log_archive_dest_2='service=PRIMARY noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=PRIMARY'

[oracle@primary backup]$ scp initPRIMARY.ora oracle@standby.apps.com:/u01/app/oracle/product/12.1.0/dbhome_1/dbs

The authenticity of host 'standby.apps.com (192.168.1.160)' can't be established.

RSA key fingerprint is 15:9d:9e:42:c1:97:45:2b:82:53:b1:45:84:27:6c:03.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'standby.apps.com,192.168.1.160' (RSA) to the list of known hosts.

oracle@standby.apps.com's password:

initPRIMARY.ora

Step 12: Create directory structure in standby location as per pfile(init.STANDBY.ora).

[oracle@standby dbs]$ mkdir -p /u01/app/oracle/admin/PRIMARY/adump

[oracle@standby dbs]$ mkdir -p /u02/oradata/PRIMARY

[oracle@standby dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area

Step 13: Copy above standby control file to standby server as per pfile.

[oracle@primary backup]$ [oracle@primary backup]$ pwd

/u02/backup

[oracle@primary backup]$ scp stndycntrl01.ctl oracle@standby.apps.com:/u02/oradata/PRIMARY/control01.ctl

oracle@standby.apps.com's password:

stndycntrl01.ctl 100% 9808KB 9.6MB/s 00:00

[oracle@primary backup]$ scp stndycntrl01.ctl oracle@standby.apps.com:/u01/app/oracle/fast_recovery_area/PRIMARY/control02.ctl

oracle@standby.apps.com's password:

stndycntrl01.ctl 100% 9808KB 9.6MB/s 00:00

[oracle@primary backup]$

Step 14: Copy password file to standby location.

[oracle@primary dbs]$ pwd

/u01/app/oracle/product/12.1.0/dbhome_1/dbs

[oracle@primary dbs]$ ls -ltr orapwPRIMARY

-rw-r-----. 1 oracle dba 7680 May 27 20:06 orapwPRIMARY

[oracle@primary dbs]$ scp orapwPRIMARY oracle@standby.apps.com:/u01/app/oracle/product/12.1.0/dbhome_1/dbs

oracle@standby.apps.com's password:

orapwPRIMARY 100% 7680 7.5KB/s 00:00

[oracle@primary dbs]$

Step 15: Backup primary database with archive logs.

[oracle@primary ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 30 10:13:49 2018

connected to target database: PRIMARY (DBID=1814738684)

RMAN> backup database plus archivelogs;

Step 16: Copy backup from Primary database location to Standby database location.

[oracle@primary PRIMARY]$ pwd

/u01/app/oracle/fast_recovery_area

[oracle@primary fast_recovery_area]$ scp -r PRIMARY oracle@standby.apps.com:/u01/app/oracle/fast_recovery_area

Step 17: Add below environment related parameters in .bash_profile

[oracle@standby ~]$ vi .bash_profile

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export ORACLE_SID=PRIMARY

PATH=$PATH:$HOME/binz:/u01/app/oracle/product/12.1.0/dbhome_1/bin

Step 18: listener.ora and tnsnames.ora files entries in standby database.

Listener:

[oracle@standby admin]$ pwd

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin

[oracle@standby admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.apps.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

TNS:

[oracle@standby admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.apps.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = STANDBY.apps.com)

)

PRIMARY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary.apps.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PRIMARY.apps.com)

)

Step 19: Start the database in mount stage.

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 30 11:29:23 2018

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1560281088 bytes

Fixed Size 2924784 bytes

Variable Size 939527952 bytes

Database Buffers 603979776 bytes

Redo Buffers 13848576 bytes

Database mounted.

Step 20: Create spfile from pfile.

SQL> create spfile from pfile;

File created.

Step 21: Stat the listener in standby database.

[oracle@standby ~]$ lsnrctl start

Step 22: Catalog the copied backup.

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/PRIMARY';

searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/PRIMARY

List of Files Unknown to the Database

=====================================

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/backupset/2018_07_30/o1_mf_ncsnf_TAG20180730T101524_fox61w05_.bkp

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/backupset/2018_07_30/o1_mf_annnn_TAG20180730T101653_fox61xtj_.bkp

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/backupset/2018_07_30/o1_mf_nnndf_TAG20180730T101524_fox5z5pk_.bkp

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/backupset/2018_07_30/o1_mf_annnn_TAG20180730T101458_fox5ycg5_.bkp

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_18/o1_mf_1_14_flgb0jr2_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_07_30/o1_mf_1_19_fox35yh4_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_07_30/o1_mf_1_18_fowtgf3v_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_07_30/o1_mf_1_21_fox61x7r_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_07_30/o1_mf_1_20_fox5y8yy_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_27/o1_mf_1_16_fm637d94_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_17/o1_mf_1_13_flcpb4xc_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_01/o1_mf_1_11_fk33mo7l_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_01/o1_mf_1_12_fk33o5qf_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_01/o1_mf_1_10_fk33krqy_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_28/o1_mf_1_17_fmb8dsc4_.arc

File Name: /u01/app/oracle/fast_recovery_area/PRIMARY/archivelog/2018_06_21/o1_mf_1_15_flqrlc5l_.arc

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================