GoldenGate Encrypt Password using Keygen Utility

Introduction 

Keygen is the utility that generates one or more encryption keys, these keys can be used to encrypt the password provided in GoldenGate parameter file. This provides enhanced security and protects the password information provided in the parameter files

Generate Encykeys using Keygen utility 

Syntax

KEYGEN   key_length   n
key_length) :- encryption key length (up to 128 bits)
n :- represents the number of keys to generate

Run the Keygen Utility

Step 1:- Invoke the Keygen Utility

[oracle@prim goldengate]$ ./keygen  128 4
0x453302332121116F655BB7529DD7F839

0x20D79B2348B03674E5FB2B52360A7510

0xA5253014E1227540100E2D1D4AA09104

0x80C9C90408B29A4590AEA11CE2D20D5B

Step 2:- Now save this output in ENCKEYS file. Contents of the file must look like this

[oracle@prim goldengate]$ cat ENCKEYS 
enc_key1 0x453302332121116F655BB7529DD7F839
enc_key2 0x20D79B2348B03674E5FB2B52360A7510
enc_key3 0xA5253014E1227540100E2D1D4AA09104
enc_key4 0x80C9C90408B29A4590AEA11CE2D20D5B
[oracle@prim goldengate]$

Note :- This ENCKEYS file needs to be copies to all the GoldenGate target server, else data replication may break

Step 3:- Generate the encrypted password

GGSCI (prim.localdomain) 7> encrypt password ggowner ENCRYPTKEY enc_key1

Encrypted password:  AADAAAAAAAAAAAHAQFVFMCVEOBVHXIAAPFGGVJYEXFGILEDDADJHHIMIDHREUCQGXIOEXBWFVAWHGEYH

Algorithm used:  AES128

GGSCI (prim.localdomain) 8>

Step 4:- Now test the password and verify

GGSCI (prim.localdomain) 9> dblogin USERID GGOWNER, PASSWORD AADAAAAAAAAAAAHAQFVFMCVEOBVHXIAAPFGGVJYEXFGILEDDADJHHIMIDHREUCQGXIOEXBWFVAWHGEYH, encryptkey enc_key1

Successfully logged into database.

GGSCI (prim.localdomain as GGOWNER@GGDB01) 10>

Step 5:- Add the encrypted password on to the GoldenGate parameter file 

GSCI (prim.localdomain as GGOWNER@GGDB01) 20> view params GGEXT01

EXTRACT GGEXT01

USERID GGOWNER, PASSWORD AADAAAAAAAAAAAHAQFVFMCVEOBVHXIAAPFGGVJYEXFGILEDDADJHHIMIDHREUCQGXIOEXBWFVAWHGEYH, encryptkey enc_key1

DISCARDFILE /u01/app/oracle/product/11.2.0/goldengate/dirrpt/ggext01.dsc

EXTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt

TABLE GGOWNER.EMPLOYEE;

GGSCI (prim.localdomain as GGOWNER@GGDB01) 21>

Oracle 11gR2 RAC Architecture

Introduction 

Oracle RAC(Real Application Cluster) is high availability and scalability solution provided by Oracle for enterprise applications. This feature was introduced in Oracle 9i since then it has been evolved.
Oracle RAC system enables multiple servers or nodes to access the data from single database that resides in the shared storage i.e. multiple oracle database instances accessing single database. In Oracle 11gR2, Grid Infrastructure was introduced that is used to the implement cluster. Grid infrastructure software bundle includes the software for both Oracle Clusterware and Oracle ASM

What is SCAN in Oracle RAC?

Scan provides single name for the client to access databases running in the cluster. When a new node joins the cluster, clusterware obtains the VIP address, updates the cluster resource and makes it accessible through DNS. Advantage of using SCAN, is that connection information is not required to change on the client, in case a node gets added or removed within the cluster. SCAN provides load balancing and failover to the client connections to the database

What is Cache fusion?

Oracle Cache fusion is a mechanism where Oracle RAC logically combines each instance's buffer cache to enable applications to process data as if it resides in the single cache. Components of Cache fusion mechanism as mentioned below

• GES - Global Enque Service
• GCS - Global Cache Service
• GRD - Global Resource Directory

     Cache fusion process is managed through two services GCS and GES. Both (GEC and GCS) records status of each cached block using Global Resource Directory (GRD). GRD contents are shared across all the  members of Oracle RAC cluster. All these actvities are performed using some of the background processes mentioned below

Background Process for Cache Fusion 

• ACMS (Atomic Controlfile to Memory Service) - This process is an agent process that runs on each nodes of the cluster and it ensures distributed SGA update is committed Globally on success or Globally abrted in case of failure occurs
• LMON (GES) - It maintains GES memory structure in case of process failure. It takes care of locks reconfiguration and cluster reconfiguration when ever a node joins or leaves the cluster
• LMS (GCS) - It maintains the records of all the blocks that have been cached in the memory and this information is stored in GRD (Global Resource Directory). LMS also controls and manages the data blocks access that are cached, it also transmits the blocks between buffer cache of different instances
• LMD (GES) - It manages incoming remote resource requests within the each instance
• LCK0 (GES) - This process manages non-Cache Fusion resource requests such as library and row cache requests.
• GTX0-j: Global Transaction Process :- This process provides transparent support for XA global transactions in an Oracle RAC environment. The database auto tunes the number of these processes based on the workload of XA global transactions.

Components of Oracle Clusterware

• Voting Disks
• OCR - Oracle Cluster Registry
• Oracle Clusterware Stack

Voting Disks

Voting disk stores nodes membership information and provides fencing. Oracle recommends to have at least three voting disks but not more than five and at-most we can use 15 voting disks. It is stored in shared location(shared storage) within the cluster and is shared by all the nodes that belongs to the cluster.
     In Oracle RAC, CSSD monitors the health of RAC nodes via heart beat (Network heart beat and Disk heart beat). Heart beat information from each node is constantly written on to the voting disks, in case a node that cannot access the voting disk, then that particular node gets evicted from the cluster. This happens to avoid the split brain phenomenon. Voting disk decides what nodes should be part of the cluster. Nodes with quorum(a number, usually a majority of members of a body) will maintain active membership of the cluster and other node(s) will be fenced/rebooted.
Possible failure scenario are mentioned below

• Network heart beat is fine, but disk heart beat is failed
• Disk heart beat is fine, but network heart beat is failed
• Both heart beats failed
• Few nodes in the cluster have split in to subsets of nodes, communicating within that set but not with other members
• One of the node is unhealthy

OCR - Oracle Cluster Registry

Oracle clusterware uses the OCR - Oracle Cluster Registry to store and manage information of cluster resources such as Oracle RAC database, listeners, VIP etc. OCR resides on shared storage location which is accessible to all the nodes of the clusters.
     OCR is a major component of the cluster and its automatically backed up every 4 hours. You can check backup using command
$ ocrconfig -showbackup 

     OCSSd (Oracle CSS deamon) uses the OCR extensively and writes the changes to the registry. OCR is loaded in the cache of each node, every node in the cluster will update the cache and one node at a time writes the cache to OCR file. CRSd updates the OCR about the status of each nodes in the cluster during reconfiguration and failures.

Oracle Clusterware Stack

Oracle Clusterware has two stacks such as cluster ready services stack and High availability services stack

Cluster Ready Services Stack

1. Cluster ready services (CRS) :-  CRSd deamon manages cluster resources using information stored in the OCR for each resources. It generates the event whenever there is change in status of the resource. CRSd monitors the resources such as database, listener etc in case there is failure it restarts these components. 
2. Cluster syncronization services (CSS) :- It manages the node membership on nodes in the cluster and also notfies the members of node when ever a nodes leaves or joins the cluster. CSSd monitors the cluster and provides the i/o fencing. Prior to Oracle 11gR2 this was taken care by OPRCd daemon.
3. Oracle ASM :- Provides disk management for cluster and databases.
4. Cluster Time Synchronization Service :- Provides time management for the cluster.
5. Event Management (EVM) :- It publishes events that clusterware generates.
6. Oracle Notification Service :- Its publish and subscribe service for Fast Application Notification (FAN) events.
7. Oracle Agent (oraagent) :- Extends clusterware to support Oracle specific requirements and runs server call out scripts when FAN events occur. 
8. Oracle Root Agent (orarootagent) :- oraroot agent helps CRSd to maintain all the resources owned by root such as network Grid VIP etc

High availability Services Stack

1. Cluster Logger Service (ologgerd) :- Receives information from all the nodes and writes the information to CHM repository database. This service runs on only two nodes
2. System Monitor Service (osysmond) :- This service collects operating system metrics and monitoring data and this data is send to cluster logger service. It runs on all the nodes of the cluster
3. Grid Plug and Play (GPNPD) :- It prvode access to Grid plug and play profile and coordinates the profile updates among the nodes in the cluster. This makes sure all the nodes in the cluster have most recent profile 
4. Grid Inter-process Communication :- It enables redundant interconnect usage 
5. Multicast Domain Name Service :- This is used by grid plug and play to locate the profiles in the cluster
6. Oracle Grid Naming Service :- Handles request sent by external DNS servers and perform name resolution for names provided in the cluster

Utilities to Manage Oracle RAC Environment 

SRVCTL - Server Control

Its the command line utility used to manage services such as database, listener and services in the cluster.

CRSCTL - Oracle Clusterware Control

This is command line utility used to manage the clusterware and its resources.

OIFCFG - Oracle Interface Configuration Tool

This utility is used to allocate and de-allocate network resources or component for the cluster.

OCRCONFIG - Oracle Cluster Registry Configuration Tool

Its the command line utility to manage OCR component of RAC. Also we can use OCRCHECK and OCRDUMP to troubleshoot issues related to OCR.

CHM - Cluster Health Monitor

It is a tool that tracks operating system resource consumption such as processes, devices etc. It collects and analyzes clusterware data.

CVU - Cluster Verification Utility 

This is command line utility used to verify resources such as shared storage devices, networking configurations, system requirements, and Oracle Clusterware, and operating system groups and users.

OEM - Oracle Enterprise Manager

Its is as GUI portal where we can monitor standalone and RAC databases along with all the components of Grid infrastructure.

Changing Data Guard Protection Modes

Overview

Maximum Protection:- It uses SYNC redo transport and commit acknowledgment will not happen in primary until transactions are not applies on to the atleast one standby database. Standby database in this mode guarantees data protection but it causes overhead to the primary database as it was to wait to acknowledge the commit. In case primary is not in sync with standby or there is some transaction faults then the primary database will be shutdown to ensure data protection.

Maximum Performance:- When you configure the standby database, default it operates in maximum performance mode. It allows transactions to be commited as soon as redo data required to recover is written on to the online redolog file. Primary database continues to function even when there is gap between primary and standby database or standby is in out of sync. It uses ASYNC redo transport.

Maximum Availability:- This mode works like the maximum protection mode by default i.e. transactions will not commit on the primary database until transactions are applied to atleast one standby database. But primary database will not be shutdown in case there is transaction fault or standby getting out of sync. When standby database gets out of sync it will wait for number of seconds specified in NET_TIMEOUT parameter, if it exceeds number of NET_TIMEOUT seconds then standby database will start to operate in Maximum performance mode.

Changing Dataguard to Maximum Availability Mode

Step 1:- (Standby Database) - Check the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM PERFORMANCE

Step 2:- (Standby Database) - Cancel the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:- (Primary Database) - Set log_archive_dest_2 parameter 

SQL> alter system set log_archive_dest_2='SERVICE=PRODSTBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=PRODSTBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

Step 4:- (Primary Database) - Now set the standby protection mode to maximum availability

SQL> alter database set standby database to maximize availability;

Database altered.

Step 5:-  (Standby Database) - Start the recovery using current logfile

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Step 6:-  (Standby Database) - Verify the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM AVAILABILITY

Changing Dataguard to Maximum Protection Mode

Step 1:- (Standby Database) - Check the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM AVAILABILITY

Step 2:- (Standby Database) - Cancel the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:- (Primary Database) - Now set the standby protection mode to maximum protection

SQL> 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

Step 5:-  (Standby Database) - Start the recovery using current logfile

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Step 6:-  (Standby Database) - Verify the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

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

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM PROTECTION

ORA-19527: physical standby redo log must be renamed

Issue Reported:-

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 5
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 0: '/u01/app/oracle/oradata/PRODPRIM/stndby2.log'

Cause of Issue:-

This issue occurs when we don't set log_file_name_convert parameter in the standby database

SQL> sho parameter log_file

NAME                                 TYPE        VALUE

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

log_file_name_convert                string

Resolution:-

Set the LOG_FILE_NAME_CONVERT parameter in spfile

SQL>  alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PRODPRIM/','/u01/app/oracle/oradata/PRODPRIM/' scope=spfile;

System altered.

Shutdown the database

SQL> shu immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Mount the standby database

SQL> startup mount

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2217992 bytes

Variable Size             494929912 bytes

Database Buffers          339738624 bytes

Redo Buffers                2396160 bytes

Database mounted.

 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;

Database altered.

Recover Standby Database Using Incremental Backup

This article demonstrates how to resolve standby database issues, in case there is any corrupted archivelog which has caused the data guard to go out of sync. Same procedure can be used in case there is huge archivelog gap between primary and standby database

Step 1 :- (Standby Side) - Cancel the recovery process and get current SCN of standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1018738

Step 2 :- (Primary Side) - Run RMAN incremental backup from SCN on standby side and Copy those backup pieces to standby side

RMAN> BACKUP INCREMENTAL FROM SCN 1018738 DATABASE FORMAT '/tmp/Standby_%U';

Starting backup at 09-SEP-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

backup will be obsolete on date 16-SEP-17

archived logs will not be kept or backed up

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/PRODPRIM/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/PRODPRIM/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/PRODPRIM/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/PRODPRIM/users01.dbf

channel ORA_DISK_1: starting piece 1 at 09-SEP-17

channel ORA_DISK_1: finished piece 1 at 09-SEP-17

piece handle=/tmp/Standby_05se20su_1_1 tag=TAG20170909T191021 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

using channel ORA_DISK_1

backup will be obsolete on date 16-SEP-17

archived logs will not be kept or backed up

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

channel ORA_DISK_1: starting piece 1 at 09-SEP-17

channel ORA_DISK_1: finished piece 1 at 09-SEP-17

piece handle=/tmp/Standby_06se20to_1_1 tag=TAG20170909T191021 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 09-SEP-17

RMAN> exit

[oracle@prim tmp]$ scp Standby_0* oracle@192.168.56.102:/tmp/

oracle@192.168.56.102's password:

Standby_05se20su_1_1                              100%   28MB  27.7MB/s   00:00

Standby_06se20to_1_1                              100% 9600KB   9.4MB/s   00:00

[oracle@prim tmp]$

Step 3 :- (Standby Side) - Catalog the backup pieces and recover the database using the incremental backup

RMAN> CATALOG START WITH '/tmp/Standby';

using target database control file instead of recovery catalog

searching for all files that match the pattern /tmp/Standby

List of Files Unknown to the Database

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

File Name: /tmp/Standby_05se20su_1_1

File Name: /tmp/Standby_06se20to_1_1

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

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /tmp/Standby_05se20su_1_1

File Name: /tmp/Standby_06se20to_1_1

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 09-SEP-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /u01/app/oracle/oradata/PRODPRIM/system01.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/PRODPRIM/sysaux01.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/PRODPRIM/undotbs01.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/PRODPRIM/users01.dbf

channel ORA_DISK_1: reading from backup piece /tmp/Standby_05se20su_1_1

channel ORA_DISK_1: piece handle=/tmp/Standby_05se20su_1_1 tag=TAG20170909T191021

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 09-SEP-17

Step 4 :- (Primary Side) - Run the current control file backup for standby and copy the backup piece to standby side

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/StandbyCTRL.bkp';

Starting backup at 09-SEP-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 09-SEP-17

channel ORA_DISK_1: finished piece 1 at 09-SEP-17

piece handle=/tmp/StandbyCTRL.bkp tag=TAG20170909T192131 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 09-SEP-17

[oracle@prim tmp]$ scp /tmp/StandbyCTRL.bkp oracle@192.168.56.102:/tmp/

oracle@192.168.56.102's password:

StandbyCTRL.bkp                              100% 9600KB   9.4MB/s   00:00

Step 5 :- (Standby Side) - Restore the standby controlfile and restart the database

Check the datafile names in standby if there is any change in datafile when compared to primary

select file#, name from v$datafile order by file# ;

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database dismounted

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2217992 bytes

Variable Size                494929912 bytes

Database Buffers             339738624 bytes

Redo Buffers                   2396160 bytes

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/StandbyCTRL.bkp';

Starting restore at 09-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/PRODSTBY/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/PRODSTBY/control02.ctl

Finished restore at 09-SEP-17

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     839282688 bytes

Fixed Size                     2217992 bytes

Variable Size                494929912 bytes

Database Buffers             339738624 bytes

Redo Buffers                   2396160 bytes

In case there is any change in directory structure. Perform below mentioned steps

RMAN> CATALOG START WITH '/u01/app/oracle/oradata/PRODPRIM/';

Starting implicit crosscheck backup at 09-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

Crosschecked 6 objects

Finished implicit crosscheck backup at 09-SEP-17

Starting implicit crosscheck copy at 09-SEP-17

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 09-SEP-17

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_ncsnf_TAG20170815T142045_ds5fklmm_.bkp

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_nnndf_TAG20170815T142045_ds5fh5fc_.bkp

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_annnn_TAG20170815T142041_ds5fh218_.bkp

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_annnn_TAG20170815T142204_ds5fknfn_.bkp

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_13_ds5j8fgh_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_14_ds5j8g27_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_9_ds5j87jl_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_11_ds5j8bg4_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_7_ds5j6bgs_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_10_ds5j888f_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_16_ds5j8jyq_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_8_ds5j6dn5_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_15_ds5j8jj2_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_12_ds5j8c68_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_21_dv7vbwbt_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_24_dv7vbxfj_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_19_dv7vbvtp_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_23_dv7vbx0s_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_20_dv7vbvsx_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_25_dv7vbxjr_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_22_dv7vbwob_.arc

File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_17_dv7vbxwy_.arc

searching for all files that match the pattern /u01/app/oracle/oradata/PRODPRIM/

no files found to be unknown to the database

In Case of unknows files found use this command 

RMAN> SWITCH DATABASE TO COPY;

Step 6 :- (Standby Side) - Clear all the standby redolog groups

SQL> select GROUP#, member from v$logfile;

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/PRODPRIM/redo03.log

         2 /u01/app/oracle/oradata/PRODPRIM/redo02.log

         1 /u01/app/oracle/oradata/PRODPRIM/redo01.log

         4 /u01/app/oracle/oradata/PRODPRIM/stndby1.log

         5 /u01/app/oracle/oradata/PRODPRIM/stndby2.log

         6 /u01/app/oracle/oradata/PRODPRIM/stndby3.log

         7 /u01/app/oracle/oradata/PRODPRIM/stndby4.log

SQL>  ALTER DATABASE CLEAR LOGFILE GROUP 4;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

Database altered.

 Step 7:- (Standby Side) - Start the recovery process on standby side and verify the system

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Media recovery complete.

Verify the standby database

SQL> select PROCESS, PID, STATUS, SEQUENCE#, BLOCK# from v$managed_standby;

PROCESS          PID STATUS        SEQUENCE#     BLOCK#

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

ARCH            3809 CLOSING              28          1

ARCH            3811 CLOSING              29          1

ARCH            3813 CONNECTED             0          0

ARCH            3815 CLOSING              30          1

MRP0            3825 WAIT_FOR_LOG         31          0

RFS             7025 IDLE                  0          0

RFS             7037 IDLE                 31         18

Compare the sequence and SCN with primary database

SQL>

select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

            30

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1069536

Overview of Oracle Data Guard Architecture

Introduction

Oracle data guard is high availability and disaster recovery solution provided by Oracle. Here we refer production database as primary database and there is another copy of database running which is referred as standby database. Any data guard configuration consists of one primary and one or many standby databases. Standby databases are always in the state of recovery mode and it gets automatically re-synchronized, in case standby goes out of sync, due to network issue etc. There are different types of standby databases

Physical Standby:-

Its is physically identical copy of the primary database on block-for-block basis. Physical standby database gets synchronized with primary via redo apply. Redo data is received from primary database and applied or recovered on to the standby database through redo apply

Logical Standby:-

It contains same logical information as of primary, however physical structure of data can be different. Logical standby database is kept in sync via SQL Apply, which transforms data received from primary into SQL statements and then execute those SQL statements on standby database

Snapshot Standby(New in Oracle 11gR2):-

Snapshot standby allows database to opened in read/write mode. This is new feature in Oracle 11gR2. Physical standby database is converted into snapshot standby and to do this database has to be enabled with flashback mode. Snapshot standby database can be used to perform application testing on production data and transaction processing is allowed on to the database. This snapshot standby database can be converted back to the physical standby

Active Data Guard(New in Oracle 11gR2):- 

Its the Oracle 11g new feature which allows standby database to opened while redo apply is still in progress. You can offload reporting on active data guard so that, load can be reduced on the primary database 

Protection Modes in Oracle Data Guard

Maximum Protection:- It uses SYNC redo transport and commit acknowlegment will not happen in primary until transactions are not applies on to the atleast one standby database. Standby database in this mode guarantees data protection but it causes overhead to the primary database as it was to wait to acknowledge the commit. In case primary is not in sync with standby or there is some transaction faults then the primary database will be shutdown to ensure data protection.

Maximum Performance:- When you configure the standby database, default it operates in maximum performance mode. It allows transactions to be commited as soon as redo data required to recover is written on to the online redolog file. Primary database continues to function even when there is gap between primary and standby database or standby is in out of sync. It uses ASYNC redo transport.

Maximum Availability:- This mode works like the maximum protection mode by default i.e. transactions will not commit on the primary database until transactions are applied to atleast one standby database. But primary database will not be shutdown in case there is transaction fault or standby getting out of sync. When standby database gets out of sync it will wait for number of seconds specified in NET_TIMEOUT parameter, if it exceeds number of NET_TIMEOUT seconds then standby database will start to operate in Maximum performance mode.

Data Guard Services

Redo Transport Service

This service is responsible transfer of redo data from primary to standby database.

LNS (Log Network Service):- reads data from redolog buffer and passes to Oracle Net server for transmission to standby database
RFS (Remote File Server):- receives data from LNS and writes it to the standby redolog file

LNS transfers the data in two modes

Synchronous Mode:- This is also called as "Zero loss method", because LGWR will not commit transaction until LNS confirms transactions have been applied to standby side 
Asynchronous Mode:- In this mode transaction will not wait commit to be successful on the standby, regardless of transaction status on standby database and primary will continue to commit all the transactions happening on the primary side. One of the disadvantage is in case there is a lag between primary to standby database and if primary is lost then there is chance of significant loss of data

Log Apply Service

There are two methods to apply for standby database i.e. Redo apply(physical standby) and SQL apply(logical standby) 

Redo Apply:- Redo apply maintains block-to-block copy of standby database which is in par with primary database. It uses media recovery process(MRP process) that reads records from standby redologs and apply the data received to the standby database

SQL Apply:- SQL apply uses LSP process to apply changes received from primary on to the standby database. It requires more processing than the redo apply and also consumes more system resources like CPU, memory etc. Advantage of using this apply process is standby database can be kept in read-write mode and starting from Oracle 11g logical standby database can be used to perform rolling upgrade

Role Transitions

In Oracle Data Guard, role of the database can be changed either via switchover or failover operation.

Switchover:- Its the role transition from primary to standby and standby database gets converted on to the primary. Switchover ensures there is no data loss occurs and usually it would be planned activity

Failover:- It occurs when primary database is not available or its crashed, this will result in conversion of standby database into primary role

Taking Hot Backup of Oracle Database

Introduction

Hot backup is run when the database is up and running, due to which its also called as inconsistent backup. Prerequisites for running hot backup is that, database should be in the archivelog mode. When we run hot backup there are chances of split block. Split block phenomenon occurs when the block which is getting copied is modified by DBWR during the backup operation. Any split blocks during the backup will be recovered using archivelogs or redologs. We have to enable begin backup mode for the database before intiating the copy of files. At this stage checkpoint happens and datafiles headers will be froze. However all the changes, I/O will be enabled on the datafiles and checkpoint increment will not happen 
Caution : Archivelog generation will be more during full backup

Running Hot Backup of Oracle Database

Step 1:- Check the list of data files, control files and redolog files in the database

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/GGDB01/users01.dbf

/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

/u01/app/oracle/oradata/GGDB01/system01.dbf

/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/GGDB01/redo03.log

/u01/app/oracle/oradata/GGDB01/redo02.log

/u01/app/oracle/oradata/GGDB01/redo01.log

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/GGDB01/control01.ctl

/u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl

Step 2:- Enable begin backup mode for the database

[oracle@prim backups]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 22 19:35:45 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database begin backup;


Database altered.

Step 3:- Now manually copy all the data files, control files and redolog files to backup location

$ cp /u01/app/oracle/oradata/GGDB01/users01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/undotbs01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/system01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/sysaux01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/ggdata01.dbf /u01/app/backups/
--------------------------------------------------------------------------------

$ cp /u01/app/oracle/oradata/GGDB01/redo01.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo02.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo03.log /u01/app/backups/

--------------------------------------------------------------------------------
$ cp /u01/app/oracle/oradata/GGDB01/control01.ctl /u01/app/backups/

$ cp /u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl /u01/app/backups/

Step 4:- Go to backup location and list the files. This is to verify if all the files are present 

[oracle@prim backups]$ ls -ltr

total 1898144

-rw-r----- 1 oracle oinstall   5251072 Aug 22 19:36 users01.dbf

-rw-r----- 1 oracle oinstall  31465472 Aug 22 19:36 undotbs01.dbf

-rw-r----- 1 oracle oinstall 702554112 Aug 22 19:37 system01.dbf

-rw-r----- 1 oracle oinstall 503324672 Aug 22 19:38 sysaux01.dbf

-rw-r----- 1 oracle oinstall 524296192 Aug 22 19:38 ggdata01.dbf

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo03.log

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control01.ctl

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control02.ctl

[oracle@prim backups]$

Step 5:- After copy completes end the backup mode for database

SQL> alter database end backup;

Database altered.

Now we have successfully completed the Hot backup of Oracle database :)

During recovery below mentioned command can be used
SQL>recover database using contolfile autobackup untill cancel;

Taking Cold Backup of Oracle Database

Introduction

Cold backup is consistent backup, as the backup is run when the database is shutdown. Its exact image copy of the database that can be used to clone database on other server. We can take Oracle database cold backup in two ways

1. Cold backup can be taken using OS command such as cp
2. Using RMAN utility also we can take cold backups

Taking Cold Backup of Oracle Database - Manual

Step 1:- Check the list of data files, control files and redolog files in the database

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/GGDB01/users01.dbf

/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

/u01/app/oracle/oradata/GGDB01/system01.dbf

/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/GGDB01/redo03.log

/u01/app/oracle/oradata/GGDB01/redo02.log

/u01/app/oracle/oradata/GGDB01/redo01.log

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/GGDB01/control01.ctl

/u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl

Step 2:- Shutdown the database using graceful shutdown

[oracle@prim backups]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 22 19:35:45 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

Step 3:- Now manually copy all the data files, control files and redolog files

$ cp /u01/app/oracle/oradata/GGDB01/users01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/undotbs01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/system01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/sysaux01.dbf /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/ggdata01.dbf /u01/app/backups/
--------------------------------------------------------------------------------

$ cp /u01/app/oracle/oradata/GGDB01/redo01.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo02.log /u01/app/backups/

$ cp /u01/app/oracle/oradata/GGDB01/redo03.log /u01/app/backups/

--------------------------------------------------------------------------------
$ cp /u01/app/oracle/oradata/GGDB01/control01.ctl /u01/app/backups/

$ cp /u01/app/oracle/flash_recovery_area/GGDB01/control02.ctl /u01/app/backups/

Step 4:- Go to backup location and list the files. This step is to verify all the files are present 

[oracle@prim backups]$ ls -ltr

total 1898144

-rw-r----- 1 oracle oinstall   5251072 Aug 22 19:36 users01.dbf

-rw-r----- 1 oracle oinstall  31465472 Aug 22 19:36 undotbs01.dbf

-rw-r----- 1 oracle oinstall 702554112 Aug 22 19:37 system01.dbf

-rw-r----- 1 oracle oinstall 503324672 Aug 22 19:38 sysaux01.dbf

-rw-r----- 1 oracle oinstall 524296192 Aug 22 19:38 ggdata01.dbf

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Aug 22 19:39 redo03.log

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control01.ctl

-rw-r----- 1 oracle oinstall   9748480 Aug 22 19:40 control02.ctl

[oracle@prim backups]$

Now we have completed the cold backup for Oracle database :)

Step 5:- Once the backup is complete, start the database

Taking Cold Backup of Oracle Database using RMAN

Here we will demonstrate the procedure of taking RMAN cold backup for Oracle database

Step 1:- Shutdown the database and bring it to mount state

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size                  2217992 bytes

Variable Size             532678648 bytes

Database Buffers          301989888 bytes

Redo Buffers                2396160 bytes

Database mounted.

SQL> 

Step 2:- Connect to RMAN then run full database backup and control file backup

[oracle@prim ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 22 22:52:34 2017

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

connected to target database: GGDB01 (DBID=1637703886, not open)

RMAN> backup database;

Starting backup at 22-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/GGDB01/system01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/GGDB01/ggdata01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/GGDB01/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/GGDB01/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/GGDB01/users01.dbf

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_nnndf_TAG20170822T225245_dsrt35w5_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16

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 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncsnf_TAG20170822T225245_dsrt5lvt_.bkp tag=TAG20170822T225245 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN> backup current controlfile;

Starting backup at 22-AUG-17

using channel ORA_DISK_1

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

channel ORA_DISK_1: starting piece 1 at 22-AUG-17

channel ORA_DISK_1: finished piece 1 at 22-AUG-17

piece handle=/u01/app/oracle/flash_recovery_area/GGDB01/backupset/2017_08_22/o1_mf_ncnnf_TAG20170822T225457_dsrt7boz_.bkp tag=TAG20170822T225457 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-AUG-17

RMAN>

Once the database is complete, open the database

SQL> alter database open;

Now we can successfully completed full :)