Oracle RAC & Dataguard & RMAN

• ASM Key Features and Benefits

-Stripes files rather than logical volumes
-Provides redundancy on a file basis
-Enables online disk reconfiguration and dynamic rebalancing
-Reduces the time significantly to resynchronize a transient failure by -tracking changes while disk is offline
-Provides adjustable rebalancing speed
-Is cluster-aware
-Supports reading from mirrored copy instead of primary copy for extended clusters.
-Is automatically installed as part of the Grid Infrastructure

ASM Memory components and Background Processes.

The SGA in an ASM instance is different in memory allocation and usage than the SGA in a database instance. The SGA in the ASM instance is divided into four primary areas as follows:

• Shared Pool: Used for metadata information
• Large Pool: Used for parallel operations
• ASM Cache: Used for reading and writing blocks during rebalance operations
• Fee Memory: Unallocated memory available

The minimum recommended amount of memory for an ASM instance is 256 MB. Automatic memory management is enabled by default on an ASM instance and will dynamically tune the sizes of the individual SGA memory components. 

Process	Description
RBAL	Opens all device files as part of discovery and coordinates the rebalance activity
ARBn	One or more slave processes that do the rebalance activity
GMON	Responsible for managing the disk-level activities such as drop or offline and advancing the ASM disk group compatibility
MARK	Marks ASM allocation units as stale following a missed write to an offline disk
Onnn	One or more ASM slave processes forming a pool of connections to the ASM instance for exchanging messages
PZ9n	One or more parallel slave processes used in fetching data on clustered ASM installation from GV$ views

• INSTANCE_TYPE=ASM is the only mandatory parameter setting for ASM instance.

•  How does clusterware starts when spfile and voting disk resides on ASM  ?

Ref : http://oracleinaction.com/asm-spfile-on-asm/

Beginning with the version 11g Release 2, the ASM spfile is stored automatically in the first disk group created during Grid Infrastructure installation.

Since voting disk/OCR are stored on ASM, ASM needs to be started on the node. To startup ASM, its SPfile is needed. But SPFILE is again located on ASM diskgroup only.  How does clusterware resolve this issue?

- When a node of an Oracle Clusterware cluster restarts, OHASD is started by platform-specific means. OHASD accesses OLR (Oracle Local Registry) stored on the local file system to get the data needed to complete OHASD initialization

-  OHASD brings up GPNPD and CSSD. CSSD accesses the GPNP Profile stored on the local file system which contains the following vital bootstrap data;
a.ASM_DISKSTRING parameter (if specified) to locate the disks on which ASM disks are configured.
b. ASM SPFILE location : Name of the diskgroup containing ASM spfile

c. Location of  Voting Files : ASM

– CSSD scans the headers of all ASM disks ( as indicated in ASM_DISKSTRING in GPnP profile) to identify the disk containing the voting file.  Using the pointers in ASM disk headers, the Voting Files locations on ASM Disks are accessed by CSSD and CSSD is able to complete initialization and start or join an existing cluster.

–To read the ASM spfile during the ASM instance startup, it is not necessary to open the disk group. All information necessary to access the data is stored in the device’s header. OHASD reads the header of the ASM disk containing ASM SPfile (as read from GPnP profile) and using the pointers in disk header, contents of ASM spfile are read. Thereafter, ASM instance is started.

–  With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.

–  OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

–  Clusterware completes initialization and brings up other services under its control.

Demonstration :

In my environment, the ASM disk group DATA created with EXTERNAL  redundancy is used exclusively for ASM spfile, voting and OCR files:

- Let us read  gpnp profile to find out the location of ASM SPfile

[grid@host01 peer]$ cd /u01/app/11.2.0/grid/gpnp/host01/profiles/peer

                                  gpnptool getpval -asm_spf

Warning: some command line parameters were defaulted. Resulting command line:

         /u01/app/11.2.0/grid/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-

+DATA/cluster01/asmparameterfile/registry.253.793721441

– Let us find out the disks in DATA diskgroup

[grid@host01 peer]$ asmcmd lsdsk -G DATA

Path

ORCL:ASMDISK01

ORCL:ASMDISK010

ORCL:ASMDISK02

ORCL:ASMDISK03

ORCL:ASMDISK04

ORCL:ASMDISK09

– Let us find out which ASM disk maps to which partition

   Note down major/minor device numbers of the disks in DATA diskgroup

[root@host01 ~]# ls -lr /dev/oracleasm/disks/*

brw-rw—- 1 grid asmadmin 8, 26 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK09

brw-rw—- 1 grid asmadmin 8, 25 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK08

brw-rw—- 1 grid asmadmin 8, 24 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK07

brw-rw—- 1 grid asmadmin 8, 23 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK06

brw-rw—- 1 grid asmadmin 8, 22 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK05

brw-rw—- 1 grid asmadmin 8, 21 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK04

brw-rw—- 1 grid asmadmin 8, 19 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK03

brw-rw—- 1 grid asmadmin 8, 18 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK02

brw-rw—- 1 grid asmadmin 8, 31 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK014

brw-rw—- 1 grid asmadmin 8, 30 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK013

brw-rw—- 1 grid asmadmin 8, 29 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK012

brw-rw—- 1 grid asmadmin 8, 28 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK011

brw-rw—- 1 grid asmadmin 8, 27 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK010

brw-rw—- 1 grid asmadmin 8, 17 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK01

- Let us find out the major/minor device numbers of various disk partitions

[root@host01 ~]# ls -lr /dev/sdb*

brw-r—– 1 root disk 8, 25 Nov  8 09:35 /dev/sdb9

brw-r—– 1 root disk 8, 24 Nov  8 09:35 /dev/sdb8

brw-r—– 1 root disk 8, 23 Nov  8 09:35 /dev/sdb7

brw-r—– 1 root disk 8, 22 Nov  8 09:35 /dev/sdb6

brw-r—– 1 root disk 8, 21 Nov  8 09:35 /dev/sdb5

brw-r—– 1 root disk 8, 20 Nov  8 09:35 /dev/sdb4

brw-r—– 1 root disk 8, 19 Nov  8 09:35 /dev/sdb3

brw-r—– 1 root disk 8, 18 Nov  8 09:35 /dev/sdb2

brw-r—– 1 root disk 8, 31 Nov  8 09:35 /dev/sdb15

brw-r—– 1 root disk 8, 30 Nov  8 09:35 /dev/sdb14

brw-r—– 1 root disk 8, 29 Nov  8 09:35 /dev/sdb13

brw-r—– 1 root disk 8, 28 Nov  8 09:35 /dev/sdb12

brw-r—– 1 root disk 8, 27 Nov  8 09:35 /dev/sdb11

brw-r—– 1 root disk 8, 26 Nov  8 09:35 /dev/sdb10

brw-r—– 1 root disk 8, 17 Nov  8 09:35 /dev/sdb1

brw-r—– 1 root disk 8, 16 Nov  8 09:35 /dev/sdb

– Now we can find out the partitions mapping to various ASM disks by matching their

   major/minor device numbers

 ASMDISK01    8,17     /dev/sdb1

 ASMDISK02    8,18     /dev/sdb2

 ASMDISK03    8,19     /dev/sdb3

 ASMDISK04    8,21     /dev/sdb5

 ASMDISK09    8,26     /dev/sdb10

 ASMDISK10    8,27     /dev/sdb11

– Let’s scan the headers of those devices:

[root@host01 ~]#  kfed read /dev/sdb1 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

[root@host01 ~]#  kfed read /dev/sdb2 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

[root@host01 ~]#  kfed read /dev/sdb3 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                       16 ; 0x0f4: 0x00000010

kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001

[root@host01 ~]#  kfed read /dev/sdb5 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

[root@host01 ~]#  kfed read /dev/sdb10 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

[root@host01 ~]#  kfed read /dev/sdb11 | grep -E ‘spf|ausize’

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

In the output above, we see that

     the device /dev/sdb3 contains a copy of the ASM spfile (spfflg=1).

     The ASM spfile location starts at the disk offset of 16 (spfile=16)

Considering the allocation unit size (kfdhdb.ausize = 1M), let’s dump the ASM spfile from the device:

[root@host01 ~]#  dd if=/dev/sdb3 of=spfileASM_Copy2.ora skip=16  bs=1M count=1

1+0 records in

1+0 records out

1048576 bytes (1.0 MB) copied, 0.170611 seconds, 6.1 MB/s

[root@host01 ~]# strings spfileASM_Copy2.ora

+ASM1.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value

+ASM2.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value

+ASM3.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value

+ASM3.asm_diskgroups=’FRA’#Manual Mount

+ASM2.asm_diskgroups=’FRA’#Manual Mount

+ASM1.asm_diskgroups=’FRA’#Manual Mount

*.asm_power_limit=1

*.diagnostic_dest=’/u01/app/grid’

*.instance_type=’asm’

*.large_pool_size=12M

*.remote_login_passwordfile=’EXCLUSIVE’

 The same technique is used to access the Clusterware voting files which are also stored in an ASM disk group. In this case, Clusterware does not need a running ASM instance to access the cluster voting files:

Let’s check the location of voting disk :

[grid@host01 peer]$ crsctl query css votedisk

##  STATE    File Universal Id                File Name Disk group

–  —–    —————–                ——— ———

 1. ONLINE   243ec3b2a3cf4fbbbfed6f20a1ef4319 (ORCL:ASMDISK01) [DATA]

Located 1 voting disk(s).

– Since above query shows that voting disk is stored on ASMDISK01 which maps to /dev/sdb1,

   we will scan the header of /dev/sdb1

[root@host01 ~]#  kfed read /dev/sdb1 | grep vf

kfdhdb.vfstart:                      96 ; 0x0ec: 0x00000060

kfdhdb.vfend:                       128 ; 0x0f0: 0x00000080

Here we can see that voting disk resides on /dev/sdb1 .

Once the voting disk is accessible and ASM is started using the SPfile read above, rest of the resources on the node can be started after reading the Oracle Local Registry (OLR) on the node.

Important buffer states for cache fusion in V$BH.STATUS are:

Shared Current: The buffer contains a block image that matches the one on disk. One or more instances may have images for the same block in SCUR state. After an instance has one in this state, cache fusion is used if another instance reads the same block for read purposes.

Exclusive Current: The buffer contains a block image that is about to be updated, or has been updated. It may or may not have been written by the database writer. Only one instance may have an XCUR image for a block

Consistent Read: The buffer contains a block image that is consistent with an earlier point in time. This image may have been created in the same way as in single-instance databases, but copying a block into an available buffer and using undo to roll back the changes in order to create the older image. It may also get created by converting a block image from SCUR or PI.

Past Image: The buffer contains a block image that was XCUR but then shipped to another instance using cache fusion. A later image of this block now exists in another buffer cache. Once DBWn writes the later image to disk from the other instance, the PI image becomes a CR image.

RMAN : 

FILEPERSET :

dbms_backup_restore.resetcfilesection 

how can we provide the new execution plan for an existing query without changing the code?

Oracle provides three mechanism to deal with execution plans: SQL Profiles, SQL Baselines and SQL Patches.
Profiles are proposed by the Tuning Advisor and its mostly based on adapting the cardinalities to match the reality.
Baselines allows us to provide a list of accepted execution plan for a statement.
SQL Patches are part of the SQL Repair Advisor and adds hints to a specific statement.

What is BCT ( Block Change Tracking ) ? 

When you enable block change tracking in Oracle 11g, Oracle tracks the physical location of all database changes for incremental backups. RMAN automatically uses the change tracking file to determine which blocks need to be read during an incremental backup, and directly accesses those blocks to back them up. When block change tracking is not enabled, the entire datafile is read during each incremental backup to find and back up only the changed blocks, even if just a very small part of that datafile has changed since the previous backup.

The change tracking file is a binary file and resides in the fast recovery area (FRA). This file should be located in the same directory as your database files.

RMAN : 

Backup Set

Logical structure where the backup is stored. It is a logical container. A backup set can store one or multiple database files, spfiles, control files, etc. Do not think physically. One backup set can be stored in one or multiple files. Each of those files is called a backup piece. Usually, one backup set has only one backup piece.

Backup Piece

Physical structure where the backup is stored. A backup set is composed by one or more physical binary pieces. If you backup to disk, each file generated is a backup piece.

Multiplexed Backup Set

A backup set that contains multiple database files. For example, you could multiplex 10 datafiles into one backup set. Only whole files, never partial files, are included in a backup set.

So, do not think that a part of your SYSTEM datafile will be in one backup set and another part of it will be on another backup set. This is impossible. If your TABLESPACE have more than one datafile, than you can have each datafile in a different backup set.
Also, if your backup set is physically stored in more than one backup pieces (files), you can have your backed up datafile stored on multiple backup pieces (but not sets).

Level of Multiplexing

This is the number of input files simultaneously read and then written into the same backup piece. So if you have a channel that is reading 4 datafiles at same time and writing them to a backup set, your level of multiplexing is 4. This level is directly interfered by some configuration parameters, like the max files that a set can have, the max open files read by a channel and the # of files you are currently backing up.

Justification for filesperset=1 is faster recovery and filesperset>1 is to reduce management of backup, e.g. crosscheck.

File Section

A contiguous range of blocks in a datafile. One datafile is made by multiple sections.

Multisection Backup


A backup set in which each backup piece contains a file section. A multisection backup set contains multiple backup pieces, but a backup set never contains only a part of a datafile.

https://www.dbarj.com.br/en/2015/02/rman-real-usage-maxsetsize-maxpiecesize-filesperset-section-size-maxopenfiles/

Dataguard : 

• Data Guard Redo Transport Services coordinate the transmission of redo from the primary database to the standby database, at the same time the LGWR is processing redo, a separate Data Guard process called the Log Network Server (LNS) is reading from the redo buffer in the SGA and passes redo to Oracle Net Services for transmission to a standby database, it is possible to direct the redo data to Thirty standby databases. The process Remote File Server (RFS) receives the redo from LNS and writes it to a sequential file called a standby redo log file (SRL), the LNS process support two modes synchronous and asynchronous.
• Automatic Gap Detection by Archiver (ARCH) process.
• Protection Modes : 

Maximum Performance

This mode requires ASYNC redo transport so that the LGWR process never waits for acknowledgment from the standby database.

There is a probability of data loss if the primary fails and full synchronization has not occurred.This is the default mode. 

-- Maximum Performance.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Maximum Availability

Its first priority is to be available its second priority is zero loss protection.

It requires the SYNC redo transport.When standby server is unavailable the primary will wait the specified time in the NET_TIMEOUT parameter before giving up on the standby server and allowing the primary to continue to process.

-- Maximum Availability.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Maximum Protection 

The priority for this mode is data protection, even to the point that it will affect the primary database.This mode uses the SYNC redo transport and the primary will not issue a commit acknowledgment to the application unless it receives an acknowledgment from at least one standby database.The primary will stall and eventually abort preventing any unprotected commits from occurring.This guarantees complete data protection.

-- Maximum Protection.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

ALTER DATABASE OPEN;

Important Note :  

• DB should have Archive mode on and force logging enabled.
• Must have different DB_UNIQUE_NAME for Standby DB.
• REMOTE_LOGIN_PASSWORDFILE must be set to exclusive
• It is recommended to setup STANDBY_FILE_MANAGEMENT=AUTO.
• Set ARCHIVE_LAG_TARGET to force log switch on primary.
• Always create restore point first on Standby and then Primary.
• Always flashback Primary DB first and then Standby.

Log_archive_dest_n parameter attributes : 

AFFIRM—specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log.

NOAFFIRM—specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log.

- If neither the AFFIRM nor the NOAFFIRM attribute is specified, then the default is AFFIRM when the SYNC attribute is specified and NOAFFIRM when the ASYNC attribute is specified.

- Specification of the AFFIRM attribute without the SYNC attribute is deprecated and will not be supported in future releases.

What is standby redo logs ?

Maximum Protection mode, you must have SRLs configured. 
If you are using Maximum Performance mode, If you can enable Real time time apply if SRLs are configured.
To minimize data loss, You should configure SRLs.

In order to avoid any wait in reuse of standby redo logs, it is recommended that one more standby redo log group be retained than the number of online redo log groups configured on the primary database.

Data guard Switchover Best Practices using SQLPLUS :

1. Verify command : 

alter database switchover to <target standby db_unique_name> verify;

2.  Make sure db_file_convert and log_file_convert parameters are set correctly.

3. Primary and standby temp files are matching. (use v$tempfile).

4. Make sure you receive End of Redo message in primary alert log.

5. Verify log shipping happening between primary-standby db.

For Oracle version 12.2 use v$dataguard_process instead of v$managed_standby which is deprecated in Oracle 12.2

Force Logging : 

Force logging is not default.

In Force logging mode Oracle database must write the redo records even when NOLOGGING is used with DDL Statements.

It will force the write of REDO records even when no-logging is specified.

If we write the NOLOGGING option with DDLs command then our database will not generate redo for that DDLs but in case of Dataguard or media recovery has negative impact of it. So before implement Dataguard or standby server we need to enable the FORCE LOGGING mode of the Oracle Database.

So every changes should be recorded and updated in standby server while syncing.

what is snapshot controlfile and when we can make use of it??

RMAN needs to get a consistent view of the control file when it sets out to make backup of every datafile. It only needs to know the most recent checkpoint information and file schematic information at the time backup begins. After the backup starts, RMAN needs this information to stay consistent for duration of the backup operation; in other words, it needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible - unless RMAN were to lock the control file for the duration of the backup. But that would mean the database could not advance checkpoint or switch logs or produce new archive logs. Impossible.

To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. At the beginning of these operations, RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity.

ie , When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file only when resynchronizing with the recovery catalog or when making a backup of the current control file.

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

EXADATA

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

Smart Scan :  Offload resource intensive workloads to Storage servers.

• Predicate filtering
• Columns projection
• Join Filtering

Smart scan pre-requisites : 

• Query must undergo full table scan.
• Show parameter _serial_direct_read  to ALWAYS
• cell_offload_processing is set to TRUE

** If you see table access storage full in explain plan , that means query is using smart scan functionality.