fix for sar command does not work for non root user on IBM AIX:

At one of the client I wasn't able to use sar for oracle and grid user for performance monitoring and with help of System admin we fixed the issue and below is the solution and workaround for it.


Problem statement:

oracle@TESTDB02:[/home/oracle]$sar 1 10
sar: The file access permissions do not allow the specified action.


Solution:

root@TESTDB02:[/var/adm/sa]#cd /usr/lib/sa
root@TESTDB02:[/usr/lib/sa]#ls -lrt
total 72
-r-xr-x---    1 bin      adm            1106 Jun 01 2015  sa2
-r-xr-x---    1 bin      adm            1033 Jun 01 2015  sa1
-r-sr-x---    1 root     adm           25576 Oct 14 21:57 sadc

root@TESTDB02:[/usr/lib/sa]#chmod o+x /usr/lib/sa/sadc

root@TESTDB02:[/usr/lib/sa]#ls -lrt
total 72
-r-xr-x---    1 bin      adm            1106 Jun 01 2015  sa2
-r-xr-x---    1 bin      adm            1033 Jun 01 2015  sa1
-r-sr-x--x    1 root     adm           25576 Oct 14 21:57 sadc

root@TESTDB02:[/usr/lib/sa]#su - oracle
oracle@TESTDB02:[/home/oracle]$ls -lrt
total 8
-rw-r--r--    1 oracle   oinstall          0 Feb 03 13:35 smit.script
-rw-r--r--    1 oracle   oinstall          0 Feb 03 13:35 smit.transaction
-rw-r--r--    1 oracle   oinstall        197 Feb 03 13:35 smit.log

oracle@TESTDB02:[/home/oracle]$sar 1 10

AIX TESTDB02 2 7 00C6C7004B00    02/03/20

System configuration: lcpu=32 ent=2.00 mode=Uncapped

14:07:22    %usr    %sys    %wio   %idle   physc   %entc
14:07:23       1       1       0      98    0.16     8.2
14:07:24       2       1       0      98    0.18     9.2
14:07:25       2       2       0      96    0.30    15.1


Optionally you can use alternative to sar for AIX which is lparstat command without rustling with any permission or ownership issue:


oracle@TESTDB02:[/home/oracle]$lparstat 1 10

System configuration: type=Shared mode=Uncapped smt=8 lcpu=32 mem=102400MB psize=10 ent=2.00

%user  %sys  %wait  %idle physc %entc  lbusy   app  vcsw phint  %nsp  %utcyc
----- ----- ------ ------ ----- ----- ------   --- ----- ----- -----  ------
  2.0   1.0    0.3   96.7  0.20   9.9    0.6  9.31  2480     0   150  33.19
  1.5   0.7    0.1   97.7  0.19   9.4    0.3  9.44  2324     0   150  33.24
  2.5   1.3    0.1   96.2  0.27  13.5    0.8  9.40  2315     0   150  33.21
  2.2   0.9    0.0   96.8  0.21  10.4    0.5  9.36  2363     0   149  33.11
  7.3   2.7    0.1   89.9  0.68  34.1    2.2  8.95  3103     0   150  33.14
  2.7   1.6    0.1   95.6  0.31  15.5    0.8  9.37  2526     0   150  33.19
  1.6   0.7    0.0   97.7  0.17   8.7    0.6  9.42  2472     0   150  33.22
  1.5   0.7    0.0   97.8  0.17   8.6    0.5  9.50  2416     0   150  33.22
  2.6   1.3    0.0   96.1  0.27  13.7    0.7  9.32  2506     0   150  33.12

  1.6   1.8    0.0   96.6  0.26  13.0    0.6  9.28  3419     0   150  33.16

ORA-00205: error in identifying control file after GI PSU 12.2.0.1.200114 upon instance startup

Problem Statement:

Today, I encountered interesting but weird problem while successfully applying Patch 30501932 - GI Jan 2020 Release Update 12.2.0.1.200114 on node-1  when I tried to shutdown immediate for instance -2 for applying patch, it did not shutdown and session and was keep waiting for shutdown so I had to abnormally stop shutdown and issued shutdown abort, but post shutdown when I tried to start up instance-2, it throw below set of errors:

SQL> startup

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/inittestdb2.ora'
SQL> exit


Created pfile from SPFILE on node-1 and copied to node-2 and tried starting instance 2 using newly created pfile:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/inittestdb2.ora'
SQL>
SQL>  startup pfile='/tmp/inittestdb.ora';
ORACLE instance started.

Total System Global Area 5.5298E+10 bytes
Fixed Size                 29964104 bytes
Variable Size            8724154552 bytes
Database Buffers         4.6439E+10 bytes
Redo Buffers              104251392 bytes

ORA-00205: error in identifying control file, check alert log for more info

Below is the list of Error reported in  alert.log file:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WARNING: failed to register ASMB0 with ASM instance
2020-01-30T14:32:27.133429+03:00
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_asmb_31261082.trc:
ORA-12547: TNS:lost contact
ORA-12547: TNS:lost contact
WARNING: ASMB0 exiting with error
2020-01-30T14:32:27.137941+03:00
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/testdb/CONTROLFILE/current.261.1030897861'
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb/CONTROLFILE/current.261.1030897861
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-12547: TNS:lost contact
ORA-12547: TNS:lost contact
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/testdb/CONTROLFILE/current.262.1030897861'
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb/CONTROLFILE/current.262.1030897861
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-12547: TNS:lost contact
ORA-12547: TNS:lost contact
2020-01-30T14:32:27.145437+03:00
ORA-205 signalled during: ALTER DATABASE   MOUNT...
2020-01-30T14:44:12.824562+03:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Issue Observation:

I observed that even though +ASM2 was up and all disks were mounted on node-2 still Instance-2 was not able to access diskgroups and hence upon DB startup  it could not access SPFILE located on ASM diskgroup and hence oracle tried to start Instance with default PFILE but still it couldn't start as controlfile was not accessible due to diskgroup accessibility issue. 

Further more Observed that after successfully applying GI Jan 2020 PATCH on node-1, even without applying patch on node-2, it had changed setuid permission  for GI oracle binary file on node-2 and because of that Oracle was not able to access diskgroup upon instance startup, please find below evidence:


setuid permission for owner and group is changed from s to x and that's the issue.

On Node-2:

oracle@TESTHOST02:[/u01/app/12.2.0/grid/bin]$ls -rlta |grep oracle
-rw-------    1 grid     oinstall  579571814 Jan 27 13:45 oracleO
-rwxr-x--x    1 grid     oinstall  579571814 Jan 27 14:29 oracle
oracle@TESTHOST02:[/u01/app/12.2.0/grid/bin]$


Please observe setuid for GI Oracle binary on node-1, which is proper.

On Node-1:
===================
root@TESTHOST01:[/]#ls -lrta /u01/app/12.2.0/grid/bin/oracle
-rwsr-s--x    1 grid     oinstall  583710697 Jan 30 12:27 /u01/app/12.2.0/grid/bin/oracle
root@TESTHOST01:[/]#

Solution:

Perform below steps on node-2, (affected node), which should fix the issue and Instance on node-2 should come up just fine without any issue.

1.shutdown CRS as root

root@TESTHOST02:[/]#/u01/app/12.2.0/grid/bin/crsctl stop crs

2. Set setuid  for Grid oracle binary. 


root@TESTHOST02:[/u01/app/12.2.0/grid/bin]$ls -rlta |grep oracle
-rwxr-x--x    1 grid     oinstall  579571814 Jan 27 14:29 oracle


root@TESTHOST02:[/]#chmod 6751 /u01/app/12.2.0/grid/bin/oracle

root@TESTHOST02:[/]#ls -lrta /u01/app/12.2.0/grid/bin/oracle
-rwsr-s--x    1 grid     oinstall  579571814 Jan 27 14:29 /u01/app/12.2.0/grid/bin/oracle

3. Start the CRS

root@TESTHOST02:[/]#/u01/app/12.2.0/grid/bin/crsctl start crs

Oracle RAC Cluster Startup Sequence

     In an Oracle 10g clusterware, remember how oracle clusterware was locating OCR and Voting file in? You probably know that location of these files were maintained in /etc/oracle/ocr.loc file and clusterware in 10g was able to start OCSSD (Oracle Cluster Synchronization Service Daemon) using voting disks/files and OCRSD  (Oracle Cluster Registry Service Daemon) using OCR files upon startup.

      However from Oracle 11gR2, clusterware architecture and startup process have changed a bit, which is described in this article.

       Let's first understand the main components involved in 11gR2 (and did not change much in 12c) clusterware startup.

Four Important Clusterware files used during startup:

1. Voting Files:  Stores information about node membership. You can have multiple voting disks/files and each files must be accessible by all the nodes in the cluster for nodes to be members of the cluster. When cluster is running, voting files are used to check heartbeat among all available nodes in a cluster over cluster interconnect network to avoid split brain scenario. 

2. OCR (Oracle Cluster Registry) files : Stores Oracle clusterware and RAC database configuration information such as node membership, s/w version, configuration and status of RAC DB resources i.e. instances, listener, services, etc.

3. OLR (Oracle Local Registry) file: In Oracle 11gR2, additional component related to the OCR called the OLR is installed on each node of the cluster.

  The OLR is a local registry for node specific resources. Location of OLR is CRS_HOME/cdata/<hostname>.olr and the location of the OLR is stored in /etc/oracle/olr.loc and used by OHASD (Oracle High Availability Service Daemon) to startup cluster resources.

     Few important info which OLR Contains are active CRS version, CRS_HOMES, GPnP details, Node names, OCR latest backup time and location etc. 

     If OLR is missing or corrupted, clusterware can't be started on that node.

Note: Please use this article- "How to restore local OLR in Oracle 11gR2 RAC?",in case your OLR gets corrupted or missing.

4. GPnP Profile: Grid Plug aNd Play (GPnP) is a XML file located at CRS_HOME/profiles/peer/profile.xml which is known as GPnP profile. Each node in the cluster maintains a copy of this profile locally and is maintained by ora.gpnpd (GPnP daemon) together with ora.mdnsd (mdns daemon).

This GPNP profile.xml contains info like: Network interfaces/IPs for public and private interconnects, ASM Server Parameter (SP) file, CSS voting disks, cluster name, cluster id, hostname, ASM diskgroup discovery string, Name of the ASM diskgroup containing voting files, etc.

     Now You know 4 important file components of the clusterware, now it will be easy for us to understand the sequence of clusterware startup.

     There are many clusterware processes, however we will concentrate here mainly 4 important processes involved in clusterware startup, namely init.d/systemd, OHASD, OCCSD and OCRSD. 

     

1. init.d/systemd :  Once your OS finishes the bootstarp process, it reads /etc/init.d file by the initialization daemon init.d.  init.d daemon triggers the startup of OHASD.

Note: In latest Linux distributions init.d has been replaced by systemd daemon. For more details on init.d and systemd , please read this very good article- The Story Behind ‘init’ and ‘systemd’: Why ‘init’ Needed to be Replaced with ‘systemd’ in Linux

2. OHASD : is the root for bringing up Oracle clusterware. OHASD has access to the OLR stored on the local file system. OLR provides needed data to complete OHASD initialization.

     OHASD triggers GPnPD and CSSD. CSSD daemon has access of GPNP profile from which it locates the well-known pointers for the voting files locations in the ASM disk headers and CSSD is able to complete initialization and joins an existing cluster.

     OHASD now starts ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFile assuming it's stored in a Diskgroup. 

     With an ASM instance operating and it's diskgroup mounted, access to clusterware OCR file is available to CRSD and hence OHASD starts CRSD. Clusterware completes initialization and brings up other services under it's control.


Please note that ASM does not depend on on OCR or OLR to be online. ASM depends on OCCSD (voting disk/file) to be online.
Also note that there are many more processes/daemons involved in clusterware startup and normal working of it, which is displayed in below mentioned diagram, however we have only covered most important components of clusterware in this article.

For more information on clusterware  startup sequence, please refer Oracle support Doc id:1053147.1

Please refer below 12c Clusterare startup sequence diagram for more info:

References:
https://anandoracledba.blogspot.com/2017/06/oracle-clusterware-rac-startup.html
http://oracledatabasetutorial.blogspot.com/2017/08/rac-oracle-clusterware-startup-sequence.html
http://oracle-help.com/oracle-rac/rac-11gr2-clusterware-startup-sequence/
https://www.tecmint.com/systemd-replaces-init-in-linux/

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');