ORA-600 [QKAFFSINDEX5] and Oracle 12c

Problem:

You are getting ORA-00600: internal error code, arguments: [qkaffsindex5] in Oracle 12.1.0.1 or higher.

This seems like a bug in 12c and it happens because your query is using a function based index and DESC.

How to Configure Oracle RAC and TAF

This article describes Transparent Application Failover (TAF) and provides configuration details for 11gR2(11.2) cluster database.

There are two types of failover:

SESSION: Session Failover re-creates the connections and sessions to the surviving instance.

SELECT:  In addition to recreating the session, Select Failover also replays the select queries that were in progress.

There are two methods in which TAF establishes the failover connection:

BASIC: The second connection is re-established only after the first connection to the instance failed.

PRECONNECT: Two connections are established when the client logs in to the database. A login to database will create two connection at the same time. For this to work, clusterware actually starts two services. One main service and another shadow service.

TAF can be configured at the Oracle Client side in tnsnames.ora or at the Database Server side using the SRVCTL utility.  Configuring it at the server is preferred.. This article covers the configuration at the server using SRVCTL.

1. Create service using SRVCTL

$ srvctl add service -d BKISDB -s HR -r BKISDB1,BKISDB2 -P BASIC -e SELECT

2. Start service using SRVCTL

$ srvctl start service -d BKISDB -s HR

3. Create new tns entry for HR service:

HR =  (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))     (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = HR)     )  )

4. Test new service:

$ sqlplus system/password@HR

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.

How to Backup and Restore a RAC Database

This article explains how to backup and recover a 2-node, 11gr2 or 12c RAC cluster on Linux. Backup and recovery operations for RAC databases are similar to single instance databases, however there are some differences.

1. Setup FRA or recovery destination
2. Enable archivelog mode
3. Multiple backup channels configuration for RAC
4. Backup your RAC database
5. Stop your RAC database and remove users01.dbf file
6. Restore and recover your RAC database

DB performance is slow due to sys.dba_audit_session job

Problem:

When running Enterprise Manager Database or Grid Console, the following Query is showing high I/O Waiting for the User the following query takes a lot of time to run:

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count FROM sys.dba_audit_session WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

How to Change Default Listener and SCAN Listener Port in RAC

Some sites require using a different port than 1521 for the listener. When initially installing Grid

Infrastructure, the administrator is not given the choice of which port she wants to use. This means that

changing the listener port must happen after the installation has completed successfully.

Here are the steps:

Oracle Single Client Access Name (SCAN) How to

Single Client Access Name (SCAN) is an Oracle Real Application Clusters (Oracle RAC) feature that provides a single name for clients to access Oracle Databases running in a cluster.The benefit  is that the client’s connect information does not need to change if you add or remove nodes or databases in the cluster.

In Oracle RAC 11gR2 or higher there are two listeners: traditional and scan listener. The management of both listeners should be done via srvctl command under Grid Infrastructure Home.

Managing Oracle Cluster Registry (OCR)

The Cluster Registry is the “database the Grid Infrastructure maintains of the resources the Grid Infrastructure must administer and maintain. The GI automatically backs it up every four hours and keeps at least 3 backups. In this article, I will show how to view available OCR backups, manually backup the OCR, and view the content of the OCR.