ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

If you notice any one of these errors in Alert.log during startup then the issue is related to “aio-max-nr" kernel limit. You will see this error in 10.2.0.4

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call

You will encounter this issue when the "aio-max-nr" kernel limit is too low.

Here is how you can check the value
#/sbin/sysctl -a |grep aio

Solution : Change the "aio-max-nr" kernel limit as per Oracle recommendation
fs.aio-max-nr= 3145728

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Flashback Data Archive (Oracle Total Recall)

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive:-
• CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs
QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive:-
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1
• ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G
• ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
• ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive:-
Remove Flashback Data Archive flashback_archive1 and all its historical data
• DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive:-
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
• CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20))
FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive 1
• CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20))
FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
• ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
• ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
• ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

How to find Master Node in Oracle RAC

I have seen many users asking how to find “Master node” in Oracle RAC, let me clear some of their doubts.

There are two types of Masters in Oracle RAC, one is Mater node at Oracle Clusterware level and other is Master node for specific resource or block or object.

The node which gets the active state during startup is authorized to be a master node by Cluster Synchronization Service.

Run the below command to find which node is master at Clusterware level
$cat $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* |grep master
or
$ for x in `ls -tr $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* `; do grep -i "master node" $x ; done | tail -1

The OCR Automatic backups are taken only by master node. If the Master fails, the OCR backups will be created on the new Master. The Master node which has OCR backups goes down due to failure then we cannot be recover the OCR that’s why Oracle recommends taking backups using “ocrconfig” and also integrating OCR backups with backup strategy.

Run the below command to find which node is OCR Master and taking automatic backups.
$ocrconfig –showbackup
testrac02 2010/08/30 16:29:52 /oracle/crs/cdata/crs
testrac02 2010/08/30 16:29:52 /oracle/crs/cdata/crs
testrac02 2010/08/30 12:29:49 /oracle/crs/cdata/crs
testrac02 2010/08/30 08:29:46 /oracle/crs/cdata/crs
testrac02 2010/08/29 00:29:23 /oracle/crs/cdata/crs

The block level masters are used by Cache fusion while transferring the block. Any node can become the master node of a particular block and you can also see which node acting as master in V$GES_RESOURCE table (MASTER_NODE column)

You can manually remaster an object with oradebug command:
SQL> oradebug lkdebug -m pkey "object_id"

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Oracle Heterogeneous Services

Oracle offers two solutions to access any number of non-Oracle systems from an Oracle environment in a heterogeneously distributed environment. They are

1. Oracle Generic Connectivity
2. Oracle Transparent Gateways.

Heterogeneous Connectivity Process - The Oracle Database server accesses the non-Oracle Database using Oracle Heterogeneous Services in conjunction with an agent. Heterogeneous Services (HS) is an integrated component within the Oracle Database server and the enabling technology for the current suite of Oracle Transparent Gateway products.

Oracle Generic Connectivity is a generic solution for any ODBC or OLEDB compliant non-Oracle system. It enables connectivity using industry standards such as ODBC and OLEDB driver. Using Generic connectivity it possible to access low-end data stores such as Access, dBase, FoxPro and non-relational targets like Excel. Generic connectivity is provided free of charge with the database server, but it is and limited in functionality and slower compared to Oracle's Gateways.

If Oracle database running on windows, setup the HS ODBC/HS OLEDB agent on the same server to access non-oracle systems. If Oracle database running on UNIX, you must install Oracle server on the windows where non-oracle database is running and setup the HS ODBC/HS OLEDB agent on the same server. If you don’t want to install Oracle server on non-oracle system then you can Install Oracle server on middle tier windows system or system that supports the ODBC or OLE DB protocols and setup the HS agent.

See the below links to setup Oracle Generic connectivity.
Oracle Generic connectivity setup in Oracle 9i
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/gencon.htm#1656
Oracle Generic connectivity setup in Oracle 10g
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm#g1012648

Oracle Transparent Gateways is another solution to access non-oracle database and the HS agent is a system-specific application. For example, if you include a MSSQL Sybase database in an Oracle Database distributed system then you need to obtain a Sybase-specific transparent gateway so that the Oracle Database in the system can communicate with it.

Oracle transparent Gateways are licensed separately and agents can be installed on any machine. They can be on the same machine as the Oracle database system or on the same machine as the non-Oracle system or on a third machine as a stand alone.

Following are dedicated Gateways for non-Oracle data sources:-
- Microsoft SQL*Server - Database Gateway for SQL*Server (DG4Msql)
- Sybase - Database Gateway for Sybase (DG4Sybase)
- Informix - Database Gateway for Informix (DG4Ifmx)
- IBM DB2 - Database Gateway for DRDA (DG4DRDA)
- Teradata - Database Gateway for Teradata (DG4Teradata)
- Websphere MQ - Database Gateway for Websphere MQ (DG4MQ)
- Remote online transaction processors (OLTPs) - Database Gateway for APPC (DG4APPC)
- IMS - Database Gateway for IMS (DG4IMS)
- VSAM - Database Gateway for VSAM (Dg4VSAM)
- Adabas - Database Gateway for Adabas (DG4Adabas)

See the below links to setup Transparent Gateways.
Oracle Transparent Gateways setup in Oracle 9i
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/admin.htm#170863
Oracle Transparent Gateways setup in Oracle 10g
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/admin.htm

Non-Oracle data sources for which a dedicated transparent gateway is not available can be accessed by using Database Gateway for ODBC (DG4ODBC), which uses third party ODBC drivers to make the connection to the non-Oracle data source

Database Gateway for ODBC (DG4ODBC) is the 11g version of the generic connectivity and certified for older versions (9i, 10g). However, this does require that the Oracle 10g database accessing non-Oracle database through it must be patched to at least 10.2.0.4.

See the below link for Heterogeneous connectivity setup for Oracle Database Gateways
http://www.oracle.com/pls/db111/portal.portal_db?selected=15

There are differences in licensing and functionality of the Database Gateway for ODBC and the other gateways. For more information see the Metalink notes.
Note.232482.1 Gateway and Generic Connectivity Licensing Considerations
Note.252364.1 Functional Differences between Generic Connectivity and Database Gateways

The earlier gateway versions are de-supported. For more information see the Metalink notes.
Note.549796.1 Desupport of Oracle Transparent Gateways

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Modifying the VIP Address or VIP Hostname in Oracle RAC Node

Oracle 10g or 11g uses Virtual IP address (VIP) in clustered environment for clients to connect to the database. During the installation of Oracle Clusterware users are prompted to enter VIP and VIP hostname for each node in the cluster. The VIP is a static IP with a hostname defined and also resolved through DNS.

The VIP information is stored in OCR (Oracle Cluster Registry) and also in different HA framework. Changing the VIP Address or VIP Hostname involves modification of the nodeapps, which includes the VIP,GSD, Listener, and ONS(Oracle Notification Services).The VIP can be changed while the nodeapps are running, but the changes will take effect only when nodeapps are restated.

Note that stopping nodeapps may cause other resources to be stopped for ex: - ASM, instance or database, so the change should be made during scheduled outage.

Follow the steps to change the VIP address or VIP hostname.

Step 1:- Check the original configuration before change
$ srvctl config nodeapps -n -a
Using '-a' will give you the current VIP hostname, VIP address and interface

Example:
# srvctl config nodeapps -n testrac01 -a
VIP exists.: /testrac01-vip/10.11.12.01/255.255.255.0/eth0

The VIP Hostname is 'testrac01-vip’
The VIP IP address is '10.11.12.01'
The VIP subnet mask is '255.255.255.0'
The Interface Name used by the VIP is called 'eth0'

Step 2:- Stop Instance,ASM,Nodeapps resources
$srvctl stop instance -d testdb -i tetdb1
$srvctl stop asm -n testrac01
$srvctl stop nodeapps -n testrac01

Step 3:- Verify the VIP Address is no longer running by using below command
$ifconfig -a

You can also check the resources status using crs_stat command.

Step 4:- Update /etc/hosts file with new VIP Address or VIP hostname on node1 and also update DNS to associate the new IP address with VIP hostname as per /etc/hosts file.

Step 5:- Modify VIP Address or VIP hostname on nodeapps by using srvctl command (Run as root)
#srvctl modify nodeapps -n [-o ] [-A ]
Where
-n < node_name> - Node name.
-o - Oracle Home for the cluster software (CRS-Home).
-A The node level VIP address (/netmask[/if1[|if2|...]]).

Example:- Modify the VIP Address to 10.11.12.11
#srvctl modify nodeapps -n testrac01 -A 10.11.12.11/255.255.255.0/eth0

Use below command to change to change VIP address using VIP hostname.The srvctl command will resolve the IP to hostname or the hostname to IP address. You can use the same command to change the VIP hostname from ”testrac01-vip” to “testrac01-v”

#srvctl modify nodeapps -n testrac01 -A testrac01-v/255.255.255.0/eth0

Step 6:- Verify the change by running below command
$srvctl config nodeapps -n testrac01 –a

Step7:- Start all resources
$srvct start nodeapps -n testrac01
$srvctl start asm -n testrac01
$srvctl start instance -d testdb –i testdb1

Step8:- Repeat the same steps on all remaining nodes in the cluster.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com