Data Guard Physical Standby Setup in Oracle Database 11g Release 2

-
System Configuration:

  • Two VMS 

       1. Primary Server
       
         OS - Redhat Linux 5.11
         Oracle - 11.2
         IP - 192.168.1.100
         Hostname - sourcenode01

       2. Standby Server

          OS - Redhat Linux 5.11
         Oracle - 11.2 (Only software has installed)
         IP - 192.168.1.101
         Hostname - targetnode01

Primary Server Setup

Logging
Check that the primary database is in archivelog mode.

SQL> select name from v$database;

NAME
---------
SRC01

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

If it is noarchivelog mode, switch is to archivelog mode.

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

Enable force logging if not enabled:

SQL> select force_logging from v$database;

FOR
---
NO

SQL>

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL>  select force_logging from v$database;

FOR
---
YES

SQL>

Modification of Initialization Parameter

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "src01" on the primary database.

SQL> show parameter db_name

NAME                                 TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_name                              string src01
SQL> show parameter db_unique_name

NAME                                 TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string src01
SQL>

Note: DB_NAME of standby database will be similar as of primary database but it must  have different DB_UNIQUE_NAME value 

DB_UNIQUE_NAME values of primary and standby database should be used in the DG_CONFIG setting of  the LOG_ARCHIVE_CONFIG parameter.

Here. standby database will have the value "src01_stdby"

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(SRC01,SRC01_STDBY)';

System altered.


SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(SRC01,SRC01_STDBY)
SQL>


Configure remote archive log destinations:

SQL>  show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string

SQL>  show parameter LOG_ARCHIVE_DEST_STATE_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      enable

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=src01_stdby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRC01_STDBY';

System altered.


System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL>

After:

SQL> show parameter  LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=src01_stdby NOAFFIRM A
                                                 SYNC VALID_FOR=(ONLINE_LOGFILE
                                                 S,PRIMARY_ROLE) DB_UNIQUE_NAME
                                                 =SRC01_STDBY


SQL> show parameter LOG_ARCHIVE_DEST_STATE_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE


In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.


SQL> show parameter LOG_ARCHIVE_FORMAT

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

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.

SQL> ALTER SYSTEM SET FAL_SERVER=SRC01_STDBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='SRC01_STDBY','SRC01' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='SRC01_STDBY','SRC01'  SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>

Restart database to reflect the changes.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size                  2214456 bytes
Variable Size             503317960 bytes
Database Buffers         1375731712 bytes
Redo Buffers                6086656 bytes
Database mounted.
Database opened.
SQL>

SQL> show parameter %convert%

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      SRC01_STDBY, SRC01
log_file_name_convert                string      SRC01_STDBY, SRC01
SQL>

Make TNSentry either manually or through netca

Need to make an entry in $OACLE_HOME/tnsnames.ora or get $TNS_NAME/tnsnames.ora

src01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = src01)
    )
  )

src01_stdby=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = src01)
    )
  )

[oracle@sourcenode01 admin]$ tnsping src01

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-SEP-2018 11:58:10

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = src01)))
OK (20 msec)


Backup Primary database:

[oracle@sourcenode01 admin]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 28 12:00:07 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SRC01 (DBID=3185193303)

RMAN> backup database plus archivelog;


Starting backup at 28-SEP-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=66 RECID=1 STAMP=987683451
input archived log thread=1 sequence=67 RECID=2 STAMP=987683469
input archived log thread=1 sequence=68 RECID=3 STAMP=987683478
input archived log thread=1 sequence=69 RECID=4 STAMP=987683486
input archived log thread=1 sequence=70 RECID=5 STAMP=987683493
input archived log thread=1 sequence=71 RECID=6 STAMP=987683506
input archived log thread=1 sequence=72 RECID=7 STAMP=987683524
input archived log thread=1 sequence=73 RECID=8 STAMP=987683539
input archived log thread=1 sequence=74 RECID=9 STAMP=987683554
input archived log thread=1 sequence=75 RECID=10 STAMP=987683572
input archived log thread=1 sequence=76 RECID=11 STAMP=987683593
input archived log thread=1 sequence=77 RECID=12 STAMP=987683619
input archived log thread=1 sequence=78 RECID=13 STAMP=987683635
input archived log thread=1 sequence=79 RECID=14 STAMP=987683689
input archived log thread=1 sequence=80 RECID=15 STAMP=987685002
input archived log thread=1 sequence=81 RECID=16 STAMP=988017811
input archived log thread=1 sequence=82 RECID=17 STAMP=988024336
input archived log thread=1 sequence=83 RECID=18 STAMP=988025792
input archived log thread=1 sequence=84 RECID=19 STAMP=988026775
input archived log thread=1 sequence=85 RECID=20 STAMP=988026775
input archived log thread=1 sequence=86 RECID=21 STAMP=988027218
channel ORA_DISK_1: starting piece 1 at 28-SEP-18
channel ORA_DISK_1: finished piece 1 at 28-SEP-18
piece handle=/u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/o1_mf_annnn_TAG20180928T120018_ftvlmtwb_.bkp tag=TAG20180928T120018 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 28-SEP-18

Starting backup at 28-SEP-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/SRC01/datafile/o1_mf_undotbs1_ftjz6z34_.dbf
input datafile file number=00001 name=/oradata/SRC01/datafile/o1_mf_system_ftjz65gh_.dbf
input datafile file number=00002 name=/oradata/SRC01/datafile/o1_mf_sysaux_ftjz6nhw_.dbf
input datafile file number=00004 name=/oradata/SRC01/datafile/o1_mf_users_ftjz7b9y_.dbf
channel ORA_DISK_1: starting piece 1 at 28-SEP-18
channel ORA_DISK_1: finished piece 1 at 28-SEP-18
piece handle=/u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/o1_mf_nnndf_TAG20180928T120144_ftvlpjj6_.bkp tag=TAG20180928T120144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-SEP-18
channel ORA_DISK_1: finished piece 1 at 28-SEP-18
piece handle=/u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/o1_mf_ncsnf_TAG20180928T120144_ftvlv33w_.bkp tag=TAG20180928T120144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-SEP-18

Starting backup at 28-SEP-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=87 RECID=22 STAMP=988027452
channel ORA_DISK_1: starting piece 1 at 28-SEP-18
channel ORA_DISK_1: finished piece 1 at 28-SEP-18
piece handle=/u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/o1_mf_annnn_TAG20180928T120412_ftvlv49t_.bkp tag=TAG20180928T120412 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-SEP-18

RMAN>

Create Standby controlfile and PFILE

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/src01_stdby.ctl';

Database altered.

SQL>

Create a parameter file for the standby database.

SQL> CREATE PFILE='/tmp/initsrc01_stdby.ora' FROM SPFILE;

File created.

SQL>

Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='SRC01_STDBY'
*.fal_server='SRC01'
*.log_archive_dest_2='SERVICE=src01 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

 Standby Server Setup (Manual)

 Below query will help to create required directories

 SQL> select name,value from v$parameter where name in ('audit_file_dest','user_dump_dest','core_dump_dest','background_dump_dest');

 NAME                                     VALUE
---------------------------------------- ------------------------------------------------------------
background_dump_dest                     /u01/app/oracle/diag/rdbms/src01/src01/trace
user_dump_dest                           /u01/app/oracle/diag/rdbms/src01/src01/trace
core_dump_dest                           /u01/app/oracle/diag/rdbms/src01/src01/cdump
audit_file_dest                          /u01/app/oracle/admin/src01/adump
SQL>

$ mkdir -p /u01/app/oracle/diag/rdbms/src01/src01/trace
$ mkdir -p  /u01/app/oracle/diag/rdbms/src01/src01/cdump
$ mkdir -p  /u01/app/oracle/admin/src01/adump

Copy the files from the primary to the standby server.

Login to secondary server through oracle user

 # Standby controlfile to all locations.

[oracle@targetnode01 ~]$ scp oracle@192.168.1.100:/tmp/src01_stdby.ctl /oradata/SRC01/controlfile/control01.ctl
oracle@192.168.1.100's password:
src01_stdby.ctl                                      100% 9520KB   9.3MB/s   00:00
[oracle@targetnode01 ~]$ cp /oradata/SRC01/controlfile/control01.ctl /u01/app/oracle/flash_recovery_area/SRC01/controlfile/control02.ctl
[oracle@targetnode01 ~]$

# Archivelogs and backups

[oracle@sourcenode01 ~]$ scp -r oracle@192.168.1.100:/u01/app/oracle/flash_recovery_area/SRC01/archivelog/ /u01/app/oracle/flash_recovery_area/SRC01/archivelog/2018_09_28/
oracle@192.168.1.100's password:
o1_mf_1_84_ftvl5zmq_.arc                             100%  924KB 924.0KB/s   00:00
o1_mf_1_86_ftvlmt26_.arc                             100%  374KB 374.0KB/s   00:00
o1_mf_1_83_ftvk78wl_.arc                             100% 1682KB   1.6MB/s   00:00
o1_mf_1_82_ftvhsqxj_.arc                             100% 8088KB   7.9MB/s   00:00
o1_mf_1_85_ftvl5zpf_.arc                             100%   45KB  45.0KB/s   00:00
o1_mf_1_81_ftv9fvkx_.arc                             100% 1024     1.0KB/s   00:00
o1_mf_1_87_ftvlv461_.arc                             100%  145KB 145.0KB/s   00:00
o1_mf_1_74_ftk309qk_.arc                             100%   46MB  15.3MB/s   00:03
o1_mf_1_68_ftk2xwyh_.arc                             100%   48MB  47.9MB/s   00:01
o1_mf_1_75_ftk30vr6_.arc                             100%   47MB  11.8MB/s   00:04
o1_mf_1_67_ftk2xnlv_.arc                             100%   48MB  23.9MB/s   00:02
o1_mf_1_71_ftk2yscp_.arc                             100%   39MB   9.8MB/s   00:04
o1_mf_1_70_ftk2yds9_.arc                             100%   48MB  23.9MB/s   00:02
o1_mf_1_76_ftk31jrn_.arc                             100%   47MB  23.3MB/s   00:02
o1_mf_1_66_ftk2x2s0_.arc                             100%   47MB  15.8MB/s   00:03
o1_mf_1_80_ftk4fk9l_.arc                             100%   38MB  19.1MB/s   00:02
o1_mf_1_78_ftk32tsj_.arc                             100%   45MB  11.3MB/s   00:04
o1_mf_1_72_ftk2zcd5_.arc                             100%   44MB  14.5MB/s   00:03
o1_mf_1_73_ftk2ztf9_.arc                             100%   43MB  14.3MB/s   00:03
o1_mf_1_79_ftk34jvj_.arc                             100%   39MB   9.8MB/s   00:04
o1_mf_1_77_ftk32bsh_.arc                             100%   48MB  23.9MB/s   00:02
o1_mf_1_69_ftk2y56y_.arc                             100%   48MB  16.0MB/s   00:03
[oracle@sourcenode01 ~]$

backup location

Server: primary 192.168.1.100
backup location : /u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/

login to standby server:

[oracle@targetnode01 ~]$  scp -r oracle@192.168.1.100:/u01/app/oracle/flash_recovery_area/SRC01/backupset/2018_09_28/*.* /u01/app/oracle/flash_recovery_area/SRC01/backupset/
oracle@192.168.1.100's password:
o1_mf_annnn_TAG20180928T120018_ftvlmtwb_.bkp         100%  686MB  24.5MB/s   00:28
o1_mf_annnn_TAG20180928T120412_ftvlv49t_.bkp         100%  147KB 146.5KB/s   00:00
o1_mf_ncsnf_TAG20180928T120144_ftvlv33w_.bkp         100% 9600KB   9.4MB/s   00:00
o1_mf_nnndf_TAG20180928T120144_ftvlpjj6_.bkp         100%  980MB  23.9MB/s   00:41
[oracle@targetnode01 ~]$

# Parameter file.

[oracle@argetnode01 ~]$ scp oracle@192.168.1.100:/tmp/initsrc01_stdby.ora /tmp/initsrc01_stdby.ora
oracle@192.168.1.100's password:
initsrc01_stdby.ora                                  100% 1595     1.6KB/s   00:00
[oracle@targetnode01 ~]$

# Remote login password file

[oracle@targetnode01 ~]$ scp oracle@192.168.1.100:$ORACLE_HOME/dbs/orapwsrc01 $ORACLE_HOME/dbs
oracle@192.168.1.100's password:
orapwsrc01                                           100% 1536     1.5KB/s   00:00
[oracle@targetnode01 ~]$

Start Listener

Make sure the listener is started on the standby server.

[oracle@targetnode01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-SEP-2018 12:51:22

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/sourcenode01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.101)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-SEP-2018 12:51:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/sourcenode01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.101)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@targetnode01 admin]$

Restore Backup
Create the SPFILE form the amended PFILE.

[oracle@targetnode01 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 28 12:57:51 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create  spfile from pfile='/tmp/initsrc01_stdby.ora';

File created.

SQL>

Restore the backup files.


Comments

Post a Comment

Popular posts from this blog

Installation of Oracle10g on LINUX

-
Table of Contents 1.  Pre-installation Tasks for the ORACLE 10g Database. 2. Installing the ORACLE 10g Database. 3. Post-installation tasks for the ORACLE 10g Database. 4. Testing the installation.  Pre-installation Tasks for the ORACLE 10g Database. Before starting the oracle 10g installation ,several parameters must be checked. To perform these checks login as the root user or switch user to root using the command su – root. Checking Memory and Swap space. For successful installation of Oracle 10g, the system is required to have atleast 512MB of RAM and 1 GB of Swap space.You can verify this using :-- grep MemTotal /proc/meminfo  grep SwapTotal /proc/meminfo Checking /tmp space. The Oracle Universal Installer requires 400 MB of free space in the /tmp directory. The command to check this is as follows:-- df /tmp Checking The Existence of following Softwares/RPM Packages.The following packages or later versions must be installed :--- · make-3.79.1· gcc-3.2.3-...
Read more

RMAN compression

-
RMAN Compression: There are 3 three types of compression by default: 1.       Null compression ·         Usages only till Oracle version 10.1 by default ·         RMAN skips the block that have never been used when backing up datafiles into backup sets. ·         E.g. let’s tablespace having once datafile of size 100MB and out of 100 MB only 50 is used. Then RMAN will backup only 50 MB. 2.       Unused block compression ·         Oracle version 10.2 forward, uses ‘Null’ and ‘unused block’ compression. ·         More compact backups of datafiles by skipping datafile blocks that are not currently used to store data. ·         In previous release, RMAN only supported NULL compression, which skipped space in ...
Read more

Database upgrade -11.2.0.1 to 11.2.0.4

-
Database upgrade -11.2.0.1 to 11.2.0.4 Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1) STEP 1) Download the 11.2.0.4 or RDBMS software. STEP 2) Install the latest 11.2.0.4 RDBMS Software into a new ORACLE_HOME. STEP 3 ) After installing the latest 11.2.0.4 RDBMS software, run Pre-upgrade tool (utlu112i.sql) on database (11.2.0.1). You need to review pre-upgrade output and fix if any issues before upgrading to the latest release of 11.2.0.4. STEP 4) Run  dbupgdiag.sql  script from the below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects exist in dba_objects. Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information ( dbupgdiag.sql ) $ sqlplus / as sysdba sql> alter session set nls_language=’American’; sql> @ dbupgdiag.sql sql> exit If the  db...
Read more