What is the Fast Sync Oracle Data Guard feature?

FAST SYNC (SYNC NOAFFIRM) :

FAST SYNC is a new Data guard feature introduced in Oracle 12cR1 and it's required Oracle Active Data Guard(ADG) license to use for production. 

Maximum Availability mode now allows the LOG_ARCHIVE_DEST_n attributes SYNC and NOAFFIRM to be used together for redo transport service. This enables asynchronous standby database to be deployed at a farther distance from the primary site without increasing the impact on primary DB performance. 

The attribute NOAFFIRM in LOG_ARCHIVE_DEST_n parameter  instructs the standby to acknowledge the receipt of redo changes without waiting for the Remote File Server (RFS) to write to a Standby  Redo Logs (SRL).

This mode is only available in maximum availability protection mode.

example of SYNC NOAFFIRM attribute in LOG_ARCHIVE_DEST_n param on primary DB:

SQL> show parameter log_archive_dest_2

NAME                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                    string       service="STNDBYDB", SYNC NOAFFIRM
                                                   delay=0 optional compression=
                                                   disable max_failure=0 max_conn
                                                   ections=1 reopen=300 db_unique
                                                   _name="PRIMEDB" net_timeout=30,
                                                   valid_for=(online_logfile,all_
                                                   roles)

References:

https://oracleagent.wordpress.com/2021/12/08/data-guard-architecture/

https://uhesse.com/2015/08/01/fastsync-redo-transport-for-data-guard-in-oracle-12c/

https://mandysandhu.com/2018/02/20/dataguard-12-1-fastsycn-and-farsync/

http://joordsblog.vandenoord.eu/2017/09/dataguard-fastsync-define-redo.html

https://www.wysheid.com/blog/new-features-on-oracle-12c-dataguard/

What is the Far Sync Oracle Data Guard feature?

Far Sync: 

Far Sync feature was  first introduced in 12c Release 1 and it requires Oracle Active Data Guard (ADG) License to use for production purpose.

An Oracle Standby Far SYNC instance is a  remote log transport proxy Oracle standby instance that accepts redo changes from the primary DB and write them into Standby Redo Logs (SRLs) at Far Sync instance's  local destination and then ships that redo changes to the target standby DB. It also archives those Standby Redo Logs(SRLs) to local destination at Far SYNC instance.

A far SYNC instance requires only control file and SRLs and it does not have data files and hence you can't open far sync instance to read/write data.

 Far Sync feature will require an Oracle Active Data Guard (ADG) license to use for production purpose.

It's recommended that Far Sync instance to be placed near(1-150 miles apart) to the primary DB so that there is low network latency between primary and Far Sync instance, which will help in minimizing impact on commit response time and guarantees higher data protection.

References:

ludovicocaldara.net/dba/farsync-and-fsfo/
https://docs.oracle.com/database/121/SBYDB/create_fs.htm#SBYDB5416
https://oracledbwr.com/step-by-step-configure-oracle-12c-data-guard-far-sync-standby-database/

Why Extra Standby Redo Log Group is required at Oracle Standby Database?

If You create fewer or equal Standby Redo Log (SRL) groups than Oracle Redo Log (ORL) groups, then you may run into trouble when the primary has a high rate of redo generation, especially if the primary is RAC db.  You should have enough SRL groups so that the Network Server SYNC(NSSn) process involved in maximum protection mode  and Network Server ASYNC(NSAn) process involved in maximum performance mode can write from all of the ORL groups from Primary database to SRLS at Standby database. 

For better understanding purpose consider below scenario:

In standalone (Non-RAC) DB, if the primary DB has 2 ORL groups #1 and #2 and redo switches are high due to heavy DML activities , in that case, we want to make sure that standby DB can keep up with the primary. If LGWR on primary just finished #1 and switched to #2, and now it needs to switch back to #1 again because #2 just become full, the standby must catch up, otherwise the primary LGWR cannot reuse #1 because standby is still archiving the standby's #1 SRL. Now, if you have the extra SRL group #3 on standby, then standby in this case can start to use #3 while its #1 SRL is being archived. That way, the primary can reuse the primary's #1 without delay.  

Reference: 

Nice article on Why and  How SRL by Brian Peasland:

https://community.oracle.com/docs/DOC-1007036

https://grokbase.com/t/freelists.org/oracle-l/0944n2j7ek/why-extra-standby-redo-log-group

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8746767700346579512

Creating SQL Profile

You can use below code to create the sql profile:

set long 99999 head off pagesize 0 linesize 500

 set longchunksize 1000

 

exec dbms_sqltune.drop_tuning_task('5dj81jnf8t03a_AWR_tuning_task');

set serveroutput on

declare

   l_sql_tune_task_id varchar2(100);

begin

   l_sql_tune_task_id :=dbms_sqltune.create_tuning_task(

sql_id    =>'5dj81jnf8t03a',

scope      => dbms_sqltune.scope_comprehensive,

time_limit =>120,

task_name  =>'5dj81jnf8t03a_AWR_tuning_task',

description=>'Tuning ask for statement 5dj81jnf8t03a in AWR.');

dbms_output.put_line('l_sql_tune_task_id :' || l_sql_tune_task_id);

end;

/

exec dbms_sqltune.execute_tuning_task('5dj81jnf8t03a_AWR_tuning_task');

select task_name,status from dba_advisor_log where task_name='5dj81jnf8t03a_AWR_tuning_task';

You can use below statement to check the detail of tuning task:

select dbms_sqltune.report_tuning_task('5dj81jnf8t03a_AWR_tuning_task') as recommendations from dual;

If SQL Tuning advisor find sqlprofile or baseline appropriate to improve performance of the query/sqlid it will suggest  to create sqlprofile and baseline as it find appropriate.

You can use below statement to drop tuning task:
execute dbms_sqltune.drop_tuning_task(task_name =>'5dj81jnf8t03a_AWR_tuning_task');

To drop sql profile:
execute DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'my_sql_profile');

Oracle RAC Interview Questions

Q-1 : What is the split-brain scenario?
A-1 : In Oracle RAC, split-brain is the scenario when one or more nodes updates to the database files w/o considering the integrity with other nodes. so in that scenario there is high possiblity of compromissing of database integrity and introducing the corruption to the database.

Q-2: What is the role of voting disk/file in RAC?
A-2: In Oracle RAC, voting disk file is used to determine the state of each nodes in the cluster. Each node should write heartbeat to the voting disk in predetermine interval i.e. 1 sec, so other nodes in the the cluster know that the node is alive. If node could not register the heartbeat to voting disk in stipulated time frame then it should be fence out from cluster to avoid split-brain scenario, which might introduce corruption to the database. Oracle Cluster Synchronization Service Daemon(OCSSD) is responsible to maintain synchronization of the cluster using voting disk.

Q-3: History of RAC and main components of RAC?
A-3: Please refer article -  Oracle Real Application Clusters (RAC) and main Components of RAC.


Q-4: Please describe the Oracle Clusterware startup sequence?
A-4: Please refer blog -  Oracle Clusterware Startup Sequence

Q-5:  Describe method to apply Patch on RAC database.
A-5: Please refer blog - Steps to apply GI and RDBMS patch to Oracle Grid & RDBMS home and database

Q-6: How to restore Oracle Local Registry ?
A-6: Please refer article : How to restore local OLR in Oracle 11gR2 RAC?

How to restore local OLR in Oracle 11gR2 RAC?

http://www.rachelp.nl/index_kb.php?menu=articles&actie=show&id=62

When you see the following Error: PROCL-26 , OHAS00106 in your OHSD log file under $CRS_HOME/cdata/

2009-10-16 15:02:43.664: [ default][3046311632] OHASD Daemon Starting. Command string :restart2009-10-16 15:02:43.668: [ default][3046311632] Initializing OLR2009-10-16 15:02:43.672: [ OCROSD][3046311632]utopen:6m':failed in stat OCR file/disk /u01/app/11.2.0/grid/cdata/server1.olr, errno=2, os err string=No such file or directory2009-10-16 15:02:43.672: [ OCROSD][3046311632]utopen:7:failed to open any OCR file/disk, errno=2, os err string=No such file or directory2009-10-16 15:02:43.673: [ OCRRAW][3046311632]proprinit: Could not open raw device2009-10-16 15:02:43.673: [ OCRAPI][3046311632]a_init:16!: Backend init unsuccessful : [26]2009-10-16 15:02:43.673: [ CRSOCR][3046311632] OCR context init failure. Error: PROCL-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]2009-10-16 15:02:43.673: [ default][3046311632] OLR initalization failured, rc=262009-10-16 15:02:43.674: [ default][3046311632]Created alert : (:OHAS00106:) : Failed to initialize Oracle Local Registry2009-10-16 15:02:43.674: [ default][3046311632][PANIC] OHASD exiting; Could not init OLR2009-10-16 15:02:43.674: [ default][3046311632] Done.

cd /oracle_crs/product/11.2.0/crs_1/cdata

touch lkcme25070.olr

cd /oracle_crs/product/11.2.0/crs_1/bin

./ocrconfig -local –restore /oracle_crs/product/11.2.0/crs_1/cdata/lkcme25070/backup_20101130_154551.olr

Shell script to get the information from multiple Oracle databases

I will demonstrate in the following example, how to get the informaiton from the many oracle databases quickly and easly using the Unix/Linux shell script.

Example 1: You want to get the information about the version of the oracle database for many(100s of oracle databases in very quick and efficient manner using shell script, assuming that use have common user id with same passwor in all the databases i.e. scott/tiger.
You will need i. DB list: which will be input to your shell script ii. SQL file containing oracle query iii. Shell script ,iv. Log file, which is output of the execution of the shell script.
Input file 1: db_list.txt: which will contain list of the databses i.e
$cat db_list.txt
PLNTD1.US.COM
PTDBD1.US.COM
PTDBD2.US.COM

Input file 2: db_version.sql: which will contain SQL query i.e.
$cat db_version.sql
set feedback off
set line 200
set pagesize 0
set echo off
set heading off

select d.global_name, v.versionfrom global_name d, product_component_version v where product like 'Oracle Database%';

File 3: Shell Script:get_dbs_info.sh - Main Korn shell script i.e
$>cat get_dbs_info.sh
#!/bin/ksh
DB_INPUT=db_list.txt
LOG=Db_version_info_`date +"%m%d%y%H%M%S"`.log
echo "Log File Name->"${PWD}${LOG} >$LOG
cat $DB_INPUT while read line do
#echo $line >>$LOG
sqlplus -s mailto:ora_usr/passwrd@$line <<"EOC">> ./$LOG
@db_version.sql
"EOC"
echo $?
done

Now lets execute the shell script, -x option is to run the script with debug option.
$>ksh -x get_dbs_info.sh
will generate the following output show in File4:

File 4: O/P or Log file: Will give you the list of database with oracle version, when you execute the shell script.
$>cat Db_version_info_071910175552.log
Log File Name->/balvant/exp/Db_version_info_071910175552.log
PLNTD1.US.COM
10.2.0.4.0
PTDBD1.US.COM
10.2.0.4.0
PTDBD2.US.COM
10.2.0.4.0