Deploying Oracle active Data Guard 12c on Windows servers part I

1. INTRODUCTION

Data Guard started with a very simple database function designed to keep a synchronized database copy with a lot of manual work.

It has now become a tool with automation, recovery and failover features.
Oracle Data Guard remains the only solution available today with the best RPO and RTO benefits for businesses.

This document describes the steps required to prepare and configure an Oracle 12c Data Guard on Windows server 2012, providing the necessary redundancy to our database.

This lab journey through Oracle 12c Data guard will be split in 3 blog posts. Each post will represent a major element of the deployment as  follows:  

1. Part I :  Implementation
2. Part II : Administration (switchover and failover)
3. Part III : Explore Features (Fast Start failover)
4. Troubleshooting : Resolve Log gap Error: ORA-16724

What will be covered in Part I ?

In this post we will focus on the following steps:

2. Considerations

2.1  Data Guard Configuration Types

There are two criteria to differentiate the common configurations of the Data Guard databases: 
The first concerns the type of functionality implemented while the other concerns the redo synchronization mode between the nodes.

2.1.1 Types of standby databases

fig1

  Fig1- Physical and logical Standby Databases

a) Physical standby database (Redo Apply)

Identical to the primary database at the block /disk level, the physical standby database is updated by MRP (Media Recovery Process).
It can be opened as read-only for queries only if the redo application is stopped beforehand.

b) Logical standby database (SQL Apply)

This standby database is updated by transforming the redo data received from the primary Database into SQL transactions then executing them.
This provides the advantage of being open for users to view or modify (as well as for reporting) at the same time as the changes are applied. One can even create elements specific to this standby database (indexes, tables, diagrams …).

Note: The only difference between the two Standby databases is the way to apply the redo at the reception of the redo from the primary Database. 

2.1.2 Synchronization Mode of the transactions

a) Synchronous Redo Transport

fig2

                                                                        Fig2- Synchronous Redo Log transport

Requires the primary database to wait for confirmation from the standby database that the shipped LGWR redo data have been received and written to disk (remote redo file) before applying the ‘commit’. This mode guarantees zero data loss if the primary database fails unexpectedly.


b)  Asynchronous Redo Transport

fig4.2                                                                          Fig3- Asynchronous Redo Log transport

Avoids any impact on the primary database performance by committing as early as the shipped redo data from the redo buffer or redo logs are written to the local Standby Redo Log (SRL) , without waiting for the commit feedback from the standby database. This ensures maximum performance, but does not guarantee that everything that is committed is copied at a given time.
It also allows to update the data in real time (either in Redo Apply or SQL Apply mode) .

2.1.3 New in Oracle 12c

§ Fast Sync : Improves the performance of a synchronous configuration because the standby database  only needs to receive the redo change in memory to validate the commit on the primary side. This Avoids the wait for the redo writing on the primary redo log files (disk) . The round trips between primary and standby database are thus reduced and the zero data loss is ensured.

§ Far sync : Enables the zero data loss failover  to a standby database even if it is thousands of miles away, without affecting the primary database or adding hardware complexity /deployment cost.

2.2 Data Guard Concepts                                                                        Go to Top


a) Switchover
This action makes it possible to switch the roles between the primary database and the standby database and to change the direction of log shipping (redo).

b) Failover
This operation opens the access of a standby database to users in read / write mode, when the primary database becomes
unreachable (failure).

c) Data Guard Broker
his agent automates and centralizes the creation/maintenance and monitoring of Data Guard databases. It checks that everything is functional and corrects what’s needed if actions are to be performed for a return to normal (in theory).


fig4                                                                          Fig4– Oracle data Guard Architecture
» Background processes:

  FAL (Fetch Archive Log) :  Sends archived redo log files to standby destinations (gap resolution and synchronization)
  LNS (log network server) :  Is responsible of redo transport (synch / asynchronous)
  MRP (Managed Recovery process) : Applies archived redo log files to the physical standby database.
  RFS (Remote file server) :  Receives redo data over the network from the Primary’s LGWR process and writes the redo data
   to the   standby redo log files.
  LSP (Logical standby process) : Applies the archived redo log files to the logical standby database, using SQL interfaces
  (convert redo data to SQL transactions).

Note: The LNS process is replaced in In 12c by NSSync for SYNC shipping and by TTnn for ASYNC shipping where the parallelisms is introduced.

2.3 Protection Modes                                                                               Go to Top

fig4.1Fig5- Protection modes specifications

                                                                         


3. PREPARATION                                                                 Go to Top

3.1 Topology

fig5


Fig6-
Topology of our lab test

3.2 Hardware and Software Prerequisites                                             Go to Top

This solution has been deployed and tested according to the following hardware and software configuration:

fig6

Fig7– Details about the lab hosts configuration

  • The Oracle 12c (grid, database) installation files are available on the following Oracle link: https://edelivery.oracle.com

    The following operational requirements must be observed for any DATA GUARD installation:

+ All databases must be from the same Oracle Edition (Enterprise)                             
+ The Oracle Software version must be the same
+
Same type of operating system, but not necessarily the same version
+ Same hardware and OS architecture (32-bit to 32-bit, Sun to Sun, etc.)
+
User accounts must have sysdba privilege in each database

3.2.1 Storage Requirement

   a) Installation prerequisites

     Let’s look at the Installation requirement in terms of storage and RAM

image

Fig8– Storage and Memory requirement for the installation

b) ASM discs creation

  • knowing the hosts are virtual machines (Vmware), the ideal storage distribution will be as follows

ASM disc Group

SCSI Virtual controller (VSCSI) Discs  Discs size  Total Size 
+FRA 1:0 3x 15GB

45GB

+DATA 2:0 3x

10 GB

30GB
+REDO 3:0 2x

1 GB

2GB

Vmware Note: In the case where the VDMK disks are formatted (NTFS) during the delivery of the virtual machine, it is necessary to proceed with the space layout reconfiguration through 2 tools provided for this purpose, Diskpart and asmtool.

Diskpart
Is a Windows command-line utility that allows the management of disks / partitions (creation, deletion, formatting)

Asmtool
Is an Oracle command-line utility that (stamps) disks with persistent/meaningful names to facilitate using those disks with Oracle Automatic Storage Management.

    Syntax : $ asmtool[option] [device] [label]

Command                                       Description

-add                                                  Adds or changes stamps.
-addprefix                                        Adds or changes stamps using a common prefix to generate stamps automatically.
-list                                                   Lists available disks. The stamp, windows device name, and disk size are shown.
-delete                                             Removes existing stamps from disks.
  •   Here is the storage distribution on the hosting machine:

fig8 
Fig9-
  Storage devices of the provisioned VM

  • We will split the storage according to the bellow layout (2 x discs of 15GB and 4 discs of 10GB)

    Disk 0 : 1x 15Go + 2x 10Go + 1Go

    Disk 1 : 2x 10Go + 1x 15Go + 1Go


PS C:UsersAdministrator> diskpart

DISKPART> list disk
Disk ###  Status         Size     Free     Dyn  Gpt
——–  ————-  ——-  ——-  —  —
Disk 0    Online           80 GB      0 B
Disk 1    Online           40 GB  1024 KB
Disk 2    Online         5120 MB  1024 KB

DISKPART> list volume
Volume ###  Ltr   Label       Fs     Type         Size    Status     Info
———-  —  ———–  —–  ———-  ——-  ———  ——
Volume 0     D                       DVD-ROM         0 B  No Media
Volume 1         System Rese  NTFS   Partition    350 MB  Healthy    System
Volume 2     C   OS           NTFS   Partition     79 GB  Healthy    Boot
Volume 3     E   DATA         NTFS   Partition     39 GB  Healthy
Volume 4     F   REDO         NTFS   Partition   5117 MB  Healthy

  • Create the partitions
DISKPART> select disk 0
DISKPART> create partition extended (1st)
DISKPART> create partition logical size=15360  
DISKPART> create partition logical size=10240 
DISKPART> create partition logical (10gb)
---
DISKPART> select disk 1
DISKPART> create partition extended (1st)
DISKPART> create partition logical size=10240
DISKPART> create partition logical size=15360  
DISKPART> create partition logical size=15360  
---
DISKPART> select disk 2
DISKPART> create partition extended (1st)
DISKPART> create partition logical size=1024
DISKPART> create partition logical size=1024 
  • Verify the created partitions
DISKPART> list volume

Volume ###  Ltr  Label        Fs     Type        Size     Status     Info

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

Volume 1         System Rese  NTFS   Partition    350 MB  Healthy    System

Volume 2     C   OS           NTFS   Partition     44 GB  Healthy    Boot

Volume 3                      RAW    Partition     15 GB  Healthy

Volume 5                      RAW    Partition     10 GB  Healthy

Volume 6                      RAW    Partition     10 GB  Healthy

Volume 7                      RAW    Partition   1024 MB  Healthy

Volume 8                      RAW    Partition     15 GB  Healthy

Volume 9                      RAW    Partition     15 GB  Healthy

Volume 10                     RAW    Partition     10 GB  Healthy                  
Volume 11                     RAW    Partition   1024 MB  Healthy

    • ASM disk stamping using asmtool

F:Grid12102gridasmtool> .asmtool -list
NTFS                      DeviceHarddisk0Partition1         350M
NTFS                      DeviceHarddisk0Partition2       45714M
                          DeviceHarddisk0Partition3       15360M  -- +FRA
                          DeviceHarddisk0Partition4       10240M  -- +DATA
                          DeviceHarddisk0Partition5       10240M  -- +DATA
                          DeviceHarddisk0Partition6        1024M  -- +REDO
                          DeviceHarddisk1Partition1       15360M  -- +FRA
                          DeviceHarddisk1Partition2       15360M  -- +FRA
                          DeviceHarddisk1Partition3       10240M  -- +DATA
                          DeviceHarddisk1Partition4        1024M  -- +REDO

------------ ASM Disks Creation
F:..asmtool> .asmtool -add DeviceHarddisk1Partition1 FRA1 
F:..asmtool> .asmtool -add DeviceHarddisk1Partition2 FRA2
F:..asmtool> .asmtool -add DeviceHarddisk0Partition3 FRA3
F:..asmtool> .asmtool -add DeviceHarddisk0Partition4 DATA1
F:..asmtool> .asmtool -add DeviceHarddisk0Partition5 DATA2
F:..asmtool> .asmtool -add DeviceHarddisk1Partition3 DATA3
F:..asmtool> .asmtool -add DeviceHarddisk0Partition6 REDO1
F:..asmtool> .asmtool -add DeviceHarddisk1Partition4 REDO2
 ------------ASM disks Verification    
PS F:Grid12102gridasmtool> .asmtool -list
NTFS                             DeviceHarddisk0Partition1  350M
NTFS                             DeviceHarddisk0Partition2    45714M 
ORCLDISKFRA3                     DeviceHarddisk0Partition3    14336M
ORCLDISKDATA1                    DeviceHarddisk0Partition4    10240M
ORCLDISKDATA2                    DeviceHarddisk0Partition5    10240M 
ORCLDISKREDO1                    DeviceHarddisk0Partition6    1024M
ORCLDISKFRA1                     DeviceHarddisk1Partition1    14336M
ORCLDISKFRA2                     DeviceHarddisk1Partition2    14336M 
ORCLDISKDATA3                    DeviceHarddisk1Partition3    10240M 
ORCLDISKREDO2                    DeviceHarddisk1Partition4    1024M

4. INSTALLATION                                                                 Go to Top

Now that all prerequisites are gathered, we can proceed with the installation.

4.1 Primary host

4.1.1 Installation

The following variables must be defined before any installation of Oracle products in the machine:

   Variable                                 Value

Oracle BASE                           C:APPORACLE                       
Oracle home 1                        C:APPORACLEPRODUCT11.1.0grid
Oracle home 2                        C:APPORACLEPRODUCT11.1.0DB_1

A) Grid Infrastructure

First, add the oracle user

PS F:Grid12102grid> net user oracle1 xxxxxxx /add 
F:Grid12102grid> net localgroup Administrators oracle /add

After downloading and extracting zip files corresponding to the grid 12c software, two installation methods are possible:
           – Via a script
           – GUI Installation

» Script method

Run notepad.exe to create the following installation script (the parameters must be on a single line):

PS F:grid12201gridstage> type Ginstal.bat

.setup.exe -silent selected_languages=en
oracle.install.option=HA_CONFIG
ORACLE_BASE= C:APPORACLE
ORACLE_HOME= C:APPORACLEPRODUCT11.1.0grid_1
oracle.install.IsBuiltInAccount=true 
oracle.install.OracleHomeUserName=”NT AUTHORITYSYSTEM”
oracle.install.asm.diskGroup.name=DATA
oracle.install.asm.diskGroup.redundancy=EXTERNAL
oracle.install.asm.diskGroup.disks= \.ORCLDISKDATA1,\.ORCLDISKDATA2,\.ORCLDISKDATA3 oracle.install.asm.SYSASMPassword=xxxxxxxx
oracle.install.asm.monitorPassword=xxxxxxx
     

» GUI method

  • Just run the setup.exe file and follow the instructions

F:Grid12102grid> setupe.exe 

fig9

  • Create the DATA disc group using 3 disc groups of 10GB each

fig10

  • Create the Oracle Base and Grid Home directories

fig11

  • Select the Windows Built-in Account as Oracle database Home owner


fig14

  • At the end of the installation you can connect to the ASM instance to create the FRA and REDO group

PS F:Grid12102grid> $env:ORACLE_SID=+ASM
PS F:Grid12102grid> sqlplus “/as sysasm”
SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY  DISK ‘\.ORCLDISKFRA1′,’\.ORCLDISKFRA2′,’\.ORCLDISKFRA3’ ;
SQL> CREATE DISKGROUP REDO EXTERNAL REDUNDANCY  DISK ‘\.ORCLDISKREDO1′,’\.ORCLDISKREDO2’ ; 

B) Database software

     Same process for the database software, two installation methods are possible:

– Via a script

– GUI Installation

» Script method

Start notepad.exe to create the following installation script sample (adapt as needed; the parameters must be on a single line):

PS F:DB12201databasestage> type Ginstal.bat

.setup.exe -silent  -debug -promptForPassword ^
oracle.install.option=INSTALL_DB_SWONLY ^
ORACLE_HOSTNAME=WIN-OQ40SUNBJQU ^
INVENTORY_LOCATION=”C:Program FilesOracleInventory” ^
SELECTED_LANGUAGES=en ^
ORACLE_HOME=C:APPORACLEPRODUCT11.1.0db_1^
ORACLE_BASE=E:apphomeowner4 ^
oracle.install.db.InstallEdition=EE ^
oracle.install.IsBuiltInAccount=false ^
oracle.install.OracleHomeUserName=Oracle ^
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false ^
DECLINE_SECURITY_UPDATES=true ^
oracle.installer.autoupdates.option=SKIP_UPDATES           

» GUI method

  • After unzipping the downloaded Oracle database installation files, just run the setup.exe and follow the instructions

F:BD12102grid> setup.exe

  • Select install software only then select single instance database

fig12

fig13

  • Choose the Windows built-in Account as database Oracle Home owner

fig14

  •   Choose the Oracle Base and DB Software location  [C:APPORACLE; C:APPORACLEproduct12.1.0.2db_1]

fig15

  • The rest of the flow is pretty self explanatory. Click install when you get to the last section


fig16


C) The database instance

  • Run the DBCA wizard and follow the instructions

C:APPORACLEproduct12.2.0.1db_1bin> DBCA.exe 

fig17

  • Choose the SID and GLOBAL DB Name

fig18

  • Select the default listener for the database (choose the grid listener) 

fig19

  • Choose the file storage setting : data files and FRA ASM disc groups

fig20

  • Initialization parameters setting

fig21

  • Check and finalize the installation

fig23


  • Click close when the installation is finished


fig24



Note concerning ASM
As of Oracle 11.2 the ASM stripping attribute of the redo logs template is set to ‘COARSE’ by default. It is nevertheless recommended to change the value by replacing it with ‘Fine-Grained’ for a better performance in redo writes and a lower write latency on Highly active Databases.

To do this, perform the following :

SQL> ALTER DISKGROUP FRA ALTER TEMPLATE onlinelog ATTRIBUTES (FINE);

4.2 Standby host

4.2.1 Installation

The installation is identical to that of the primary node:

   Variable                                 Value

Oracle BASE                           C:APPORACLE                       
Oracle home 1                        C:APPORACLEPRODUCT11.1.0grid
Oracle home 2                        C:APPORACLEPRODUCT11.1.0DB_1

a) Grid Infrastructure

The installation is identical to that of the primary host.

b) Database software

The installation is identical to that of the primary host.

c) Database instance

IMPORTANT: The only difference here is that the database will not be created through DBCA, but via an RMAN backup of the remote primary database.


5.  DATA GUARD CONFIGURATION                                   Go to Top


5.1 Activities and Configuration Related to the Physical Standby Database

The following tasks will be performed to complete and validate the configuration

– Create the physical standby database

– Check that the data flow arrives at the secondary base (standby)

» Here are the existing database files on the primary database

  • DATAFILES

FILE_NAME                                          TABLESPACE_NAME  FREE_SPACE
————————————————– —————- ———
+DATA/MTLDB/DATAFILE/sysaux.262.907771563            SYSAUX            50.1875
+DATA/MT LDB/DATAFILE/system.260.907771689           SYSTEM            10.3125
+DATA/MTLDB/DATAFILE/undotbs1.258.907771837          UNDOTBS          631.0625
+DATA/MTLDB/hetDATAFILE/users.266.907771835          USERS              3.625

  • LOGFILES

GROUP# MEMBER                                                  
———- ———————————————–                          
         1 +REDO/MTLDB/ONLINELOG/group_1.262.907771999             
         1 +FRA/MTLDB/ONLINELOG/group_1.348.908384663       
         2 +REDO/MTLDB/ONLINELOG/group_2.263.907771999             
         2 +FRA/MTLDB/ONLINELOG/group_2.349.908384665             
         3 +FRA/MTLDB/ONLINELOG/group_3.350.908384665
         3 +REDO/MTLDB/ONLINELOG/group_3.257.907772001               


Note : 
If the log groups have only 1 member execute the following


SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE MEMBER ‘+FRA’ TO GROUP 3;

  • CONTROLFILES

NAME
———————————————-
+FRA/MTLDB/CONTROLFILE/current.346.908382665
+REDO/MTLDB/CONTROLFILE/current.256.907771995

  • SPFILE

+DATA/MTLDB/PARAMETERFILE/spfile.257.907772105

5.2 Configuring Primary Database Initialization Parameters               Go to Top


  • · Enable logging (archive log mode)

SQL> SHUTDOWN IMMEDIATE;
  => STARTUP MOUNT ;
  => ALTER DATABASE ARCHIVELOG;
  => ALTER database OPEN;
  => SELECT LOG_MODE FROM V$DATABASE;


  • Enable logging mode

SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

  • Verify DB_NAME and DB_UNIQUE_NAME values from primary database

SQL> SHOW PARAMETER DB_NAME
SQL> SHOW PARAMETER DB_UNIQUE_NAME         —- MTLDB/MTLSTD

  • Configure the FAL server and parameter file_name_convert (primary to secondary)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(mtldb,mtlstd)’ scope=both;

  • Destinations

In our case we are using an ASM based configuration

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(mtldb,mtlstd)’ scope=both;
ALTER SYSTEM set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mtldb’ scope=both;
ALTER SYSTEM set LOG_ARCHIVE_DEST_2=’SERVICE=boisbriand ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtlstd’ scope=both;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT=’+DATA/mtldb/DATAFILE’,’+DATA/mtlstd/DATAFILE’ scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=’+FRA/mtldb/ONLINELOG’,’+FRA/mtlstd/ONLINELOG’, ‘+REDO/mtldb/ONLINELOG’,’+REDO/mtlstd/ONLINELOG’ scope=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
ALTER SYSTEM SET FAL_SERVER=Boisbriand  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;

  • Set the Remote_Login_Password parameter

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

  • Confirm the entered values:

SQL> SELECT NAME, VALUE FROM V$SPPARAMETER WHERE UPPER(NAME) IN        (‘REMOTE_LOGIN_PASSWORDFILE’,’STANDBY_FILE_MANAGEMENT’,’LOG_ARCHIVE_DEST_1′,’LOG_ARCHIVE_DEST_2′,’DB_FILE_NAME_CONVERT’,’LOG_FILE_NAME_CONVERT’,’FAL_SERVER’);

NAME                                               VALUE
————————————————– ——————–
db_file_name_convert                               +DATA/mtldb
db_file_name_convert                               +DATA/mtlstd
log_file_name_convert                              +FRA/mtldb
log_file_name_convert                              +FRA/mtlstd
log_file_name_convert                              +REDO/mtldb
log_file_name_convert                              +REDO/mtlstd
log_archive_dest_1                                 LOCATION=+FRA VALID_
                                                   FOR=(ALL_LOGFILES,ALL_ROLES)
                                                   DB_UNIQUE_NAME=mtldb log_archive_dest_2                                 SERVICE=mtlstd ASYNC VALID_FOR=
                                                   ONLINE_LOGFILES,PRIMARY_ROLE)
                                                   DB_UNIQUE_NAME=mtlstd    
fal_server                                         MTLSTD
standby_file_management                            AUTO             
remote_login_passwordfile                          EXCLUSIVE

SQL> SELECT DEST_NAME,DESTINATION,TARGET FROM V$ARCHIVE_DEST WHERE DEST_NAME IN (‘LOG_ARCHIVE_DEST_1′,’LOG_ARCHIVE_DEST_2’); 

DEST_NAME            DESTINATION                              TARGET             
——————- —————————————- ——————–
LOG_ARCHIVE_DEST_1   +FRA                                     PRIMARY
LOG_ARCHIVE_DEST_2   mtlstd                                   STANDBY

5.3  Oracle NET Configuration

Listener.ora: Add the following for the two services (MTLDB et MTLSTD) respectively


Montreal (primary)
Listener configuration

LISTENER =
(DESCRIPTION_LIST =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = montreal-01.evilcorp.com)   
              (PORT = 1521))))
SID_LIST_LISTENER =
     (SID_LIST =
     (SID_DESC =(GLOBAL_DBNAME = mtldb.evilcorp.com)
     (ORACLE_HOME =C:APPORACLEproduct12.1.0.2db_1)(SID_NAME =MTLDB)))

BoisBriand (standby) Listener configuration

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = montreal-02.evilcorp.com)
      (PORT = 1521))))                                                  

SID_LIST_LISTENER=                                                                    
   (SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = MTLSTD.evilcorp.com) – pas avant la creation MTLSTD
  (ORACLE_HOME = C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLSTD)))

Tnsnames.ora: Add the following content for both servers

Montreal =
(DESCRIPTION =
    (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = montreal-01.evilcorp.com) 
    (PORT = 1521)))
  (CONNECT_DATA =(SID = mtldb)(SERVER = dedicated)))
Boisbriand =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = montreal-02.evilcorp.com)
    (PORT = 1521)))
(CONNECT_DATA =(SID = mtlstd)(SERVER = dedicated)))

5.4 Backup the primary database via RMAN                                         Go to Top

Rman target=/
RMAN>BACKUP DATABASE PLUS  ARCHIVELOG;   

5.5 Prepare the standby Database directories

Unified Auditing (12c) allows you to audit even if the database is closed or in Mount / read only mode. The content is written on 

$ORACLE_BASE/audit/SID_NAME directory.

The following query checks the status of this option (FALSE=No directory) :

SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER=’UNIFIED AUDITING’;  

  • Creation of the new database directories

The directories corresponding to the parameters : audit_file_dest,core_dump_dest, (background_dump_dest ,user_dump_dest <11.2g)

#mkdir -p  C:apporacleadminmtlstdadump                          
#mkdir -p  C:apporaclediagrdbmsmtlstdmtlstdcdump
#mkdir -p  C:apporaclediagrdbmsmtlstdmtlstdtrace
#mkdir -p  C:APPORACLEauditmtldb                                               

  • ASM directories

ASMCMD> mkdir –p +DATA/MTLSTD/DATAFILE                                 
ASMCMD> mkdir -p +REDO/MTLSTD/ONLINELOG
ASMCMD> mkdir -p +FRA/MTLSTD/ONLINELOG
ASMCMD> mkdir -p +FRA/MTLSTD/CONTROLFILE
ASMCMD> mkdir -p +REDO/MTLSTD/CONTROLFILE
ASMCMD> mkdir -p +DATA/MTLSTD/PARAMETERFILE

  • Password file creation

On the standby server run the following command:

orapwd file=%ORACLE_HOME%databasePWDmtlstd.ora password=racattack  ENTRIES=30 FORCE=Y IGNORECASE=Y                  

  • Pfile creation

Notepad %ORACLE_HOME%databasetempini.ora «db_name=mtlstd”         

  • Start the Standby database in Nomount Mode

ORACLE_SID variable being MTLSTD  we should first create the standby database service:

C:> Oradim -NEW -SID mtlstd -STARTMODE manual -PFILE  “C:APPORACLEPRODUCT12.1.0.2db_1databasetempini.ora
C:> set ORACLE_SID=MTLSTD   
C:> sqlplus  ‘/ as sysdba’
SQL> STARTUP PFILE=’C:APPORACLEPRODUCT12.1.0.2db_1databasetempini.ora‘ NOMOUNT;

5.6 Duplication of the primary database using RMAN

This method can be beneficial, fast and simple especially when ASM is configured:

  • From the primary server, run the following RMAN script

rman <<EOF
set echo on
connect target sys/racattack@montreal; # Primary
connect auxiliary sys/racattack@boisbriand; # Standby: used by primary
run {
  # Create channels for parallelism
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
#DORECOVER
spfile
parameter_value_convert ‘mtldb’,’mtlstd’
set ‘db_unique_name’=’mtlstd’
set ‘Fal_server’=’Montreal’
set log_Archive_dest_2=’SERVICE=montreal ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtldb’
nofilenamecheck
   ;  }
Exit
EOF      

Note : Additional care should be given to non-OMF files (requires an explicit new name set)

Possible Errors

a) RMAN does not recognize the spfiles hosted in ASM

RMAN-03009: failure of backup command on prmy2 channel at 04/08/2016 
ORA-19505: failed to identify file “+DATA/MTLDB/spfilemtldb.ora”
ORA-15173: entry ‘spfilemtldb.ora’ does not exist in directory ‘MTLDB
      

    • Solution: Create an alias for the file

ASMCMD> mkalias +DATA/MTLDB/PARAMETERFILE/spfile.257.907772105   +DATA/MTLDB/PARAMETERFILE/spfilemtldb.ora

b) Local listener not recognized

RMAN-04014: startup failed: ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_MTLDB’

    • Solution: reset the LOCAL_LISTENER parameter on the primary database

SQL> ALTER SYSTEM SET LOCAL_LISTENER=” SCOPE=BOTH;

    
5.7  Standby redo logs Creation (Real-time apply)

This step is only necessary if you want to apply the maximum protection or maximum availability mode. Transactions will be sent directly from the redo buffer instead of the archives. The total number to be created = (Nbr primary redo groups +1) = 4

— group 4/5/6/7 ‘+FRA’ size 50m;   /// 
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M; (4x)
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE size 50M;

5.8 Standby database check after the RMAN duplication

Confirm that each database has the appropriate role

  • Primary database

SQL@MTLDB> SELECT NAME, OPEN_MODE, DATABASE_ROLE,DB_UNIQUE_NAME ,PROTECTION_MODE FROM V$DATABASE;
NAME  OPEN_MODE   DATABASE_ROLE  DB_UNIQUE_NAME   PROTECTION_MODE
—– ———– ————– ————— ——————–
MTLDB READ WRITE  PRIMARY        MTLDB           MAXIMUM PERFORMANCE

  • Standby database

SQL@MTLSTD> SELECT NAME, OPEN_MODE, DATABASE_ROLE,DB_UNIQUE_NAME ,PROTECTION_MODE FROM V$DATABASE;
NAME  OPEN_MODE   DATABASE_ROLE  DB_UNIQUE_NAME   PROTECTION_MODE
—– ———– ————– ————— ——————–
MTLDB READ WRITE  PRIMARY        MTLDB           MAXIMUM PERFORMANCE

At this point you are ready to mess with Data guard Broker click the Part II.                                                        Go to Top
giddy up Smile!
                                                                                                                       >>Part II : Administration