Linux HugePages for Oracle

HugePages is a feature in the Linux kernel with release 2.6. This feature provides the alternative to the 4K page size providing bigger pages, and is a very useful feature when we have a big SGA configured for the Oracle database.


To setup HugePages, the following changes must be completed:

Set the vm.nr_hugepages kernel parameter to a required value.

In this test case we will use a 20GB SGA , we can calculate the vm.nr_hugepages as below

Assuming we have only one instance running in the box, and also we have a 2MB hugepage for Linux x86_64.

(20480 MB [SGA] + 260 [buffer+ reserve for other applications to use shared memory] )/2 MB = 10500

and from the above derivation we set the value as below:

sysctl -w vm.nr_hugepages=10500


/etc/securities/limits.conf must be updated to increase soft and hard memlock values for oracle userid.

oracle soft memlock 20971520
oracle hard memlock 20971520

After setting this up, we will test to see if SGA is using HugePages.

The value, (HugePages_Total- HugePages_Free)*2MB will be the approximate size of SGA.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1536M
sga_target big integer 1536M

SQL> alter system set sga_max_size=20g scope=spfile sid='*';

srvctl stop database -d test
srvctl start database -d test

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 20G
sga_target big integer 1536M

$ cat /proc/meminfo |grep HugePages
HugePages_Total: 10500
HugePages_Free: 10297
HugePages_Rsvd: 10038

SQL> alter system set sga_target=20g scope=both sid='test2';

$ cat /proc/meminfo |grep HugePages
HugePages_Total: 10500
HugePages_Free: 818
HugePages_Rsvd: 559

Note : When started up using sqlplus the hugepages is not being used, bu twhen started up using srvctl it works.

[Metalink Reference HugePages on Linux: What It Is... and What It Is Not... [ID 361323.1]]
[Metalink Reference Shell Script to Calculate Values Recommended HugePages / HugeTLB Configuration [ID 401749.1]]

Oracle 10132 trace

Almost all of the DBA's use the 10046 trace and many use 10053 trace, one of traces not often used is the 10132 trace.This is generally used to generate the execution plan for the hard parses.Which can be enabled at the system level to get/ baseline the plans for the hard parsed statements after the event is set.We can also use it for session level as a shorter version of 10053 trace.

And most importantly the trace file is well formatted and easily readable even without using any utility like tkprof.


The event 10132 can be enabled and disabled in the following ways:

Enable and disable the event for the current session.

ALTER SESSION SET events '10132 trace name context forever'
ALTER SESSION SET events '10132 trace name context off'

Enable and disable the event for the whole database.

Note: this setting does not take effect immediately but only for sessions created after the modification.

ALTER SYSTEM SET events '10132 trace name context forever'
ALTER SYSTEM SET events '10132 trace name context off'


Sample output:

*** SERVICE NAME:(SYS$USERS) 2009-11-25 10:23:15.898
*** SESSION ID:(2160.19196) 2009-11-25 10:23:15.898


Current SQL statement for this session:
SELECT so_status_name
FROM job_queue jq,
so_job_queue sjq
WHERE jq.module = :sModule
AND jq.arg_3 = :sUser
AND jq.arg_4 = :sFile
AND jq.arg_5 = :sDate
AND jq.jobid = sjq.so_jobid
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | | | 6 | | | | | | |
| NESTED LOOPS | | 1 | 48 | 6 | | | | | | |
| TABLE ACCESS FULL | JOB_QUEUE | 1 | 33 | 5 | | | | | | |
| TABLE ACCESS BY INDEX ROWID | SO_JOB_QUEUE | 1 | 15 | 1 | | | | | | |
| INDEX UNIQUE SCAN | SO_JOB_QUEUE_IDX | 1 | | | | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------

How to upload huge files to Oracle support

Oracle's ftp site can be used to do the job by following the below steps:

Move the file to your local machine and tar/zip them appropriately.

Open a command window.

Start ftp:
C:\T> ftp
ftp>

Connect to the oracle ftp server and connect as user anonymous:
ftp> open ftp.oracle.com
User (bigip-ftp.oracle.com:(none)): anonymous

Use as a password a valid email address used in metalink:

331 Please specify the password.
Password:
230 Login successful.

Move into the directory "support/incoming":

ftp> pwd
257 "/"
ftp> cd support/incoming
250 Directory successfully changed.

Create a new directory with the name of your service request number:
ftp> mkdir
257 "/support/incoming/" created
ftp> cd
250 Directory successfully changed.

Set the transfer mode to binary:
ftp> bin
200 Switching to Binary mode.

Put the files on the ftp server:
ftp> put

Use the command pwd to check the current directory:
ftp> pwd
257 "/support/incoming/"

REMARK: For security reasons the command ls cannot be executed

Exit from ftp site:
ftp> quit

Inform Support that the file has been uploaded in the following location:
ftp://ftp.oracle.com/ftp/anonymous/support/incoming//filename

vnetd + Oracle RMAN restore from tape using NetBackup

I had a unique issue when working on a retore using rman, the issue was the restore was started with 10 channels and the restore was happening with one channel and the other channels were not reading any data and finally failed with “cannot connect socket” error from netbackup side.

The rman side failed with a generic MML error as below.

ORA-19624: operation failed, retry possible
ORA-19507: failed to retrieve sequential file, handle="AAAPRD_dbf_sokhhtgt_689501725", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file


This issue went for a day or two and finally with the help of a system admin we resolved the issue.


The issue was like below :

1.The database server failed to establish more vnetd connections from master/media servers of Netbackup.

1. vnetd is used by default from NBU 6.0 and up. So NetBackup uses the firewalled configuration even if you do not have a firewall.

2. If vnetd fails NBU failback to normal deamon port connection (bpcd, bpbrm etc).


This was finally fixed by changing the per_source value in /etc/xinetd.conf from 10 to UNLIMITED after whcih all the channels running fine.

Oracle 10.1.0.5 database opened with 10.2.0.4 home

We had a case where a dba had opened a 10.1.0.5 database with a 10.2.0.4 Home and with the init parameter compatible=10.2.0.4 by mistake.

After that the dba attempted to start the db using 10.1.0.5, which resulted in the following error as the compatibility information had been written allover
the controlfiles, datafiles etc.


/rdbms/v10.1/dbs >sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 4 05:37:07 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1323752 bytes
Variable Size 164351256 bytes
Database Buffers 436207616 bytes
Redo Buffers 2097152 bytes
SQL> @ /tmp/ctrl.sql
CREATE CONTROLFILE SET DATABASE "RETRDEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 10.2.0.4.0 incompatible with ORACLE version
10.1.0.4.0
ORA-01110: data file 1: '+DG8_DEV/retrdev/datafile/system.256.629212191'



Since compatibility parameter cannot be rolled back in this case, we dint have any backups as this was a developement environment.To workaround this we had

to do the following:

1.Upgrade the database to 10.2.0.4
2.Export the database.
3.Recreate a 10.1.0.5 database and import it.

So we have to be carefullwhicle settign such parameters.

Oracle 10g RAC check/modify private interconnect information

Query the private interconnect information from the database:

SQL> select * from gv$cluster_interconnects ;

INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1 eth0 192.168.124.186 NO OS dependent software

Query the private interconnect information using oifcfg:

$ oifcfg getif
eth1 192.168.128.0 global public
eth2 192.168.127.0 global cluster_interconnect

Check the interconnect information from the alert log

And also the alert log will throw the information during start up just after displaying the non-default init parameters:

open_cursors = 300
pga_aggregate_target = 73400320
Cluster communication is configured to use the following interface(s) for this instance
192.168.124.186


You can check the available interfaces using

$ oifcfg iflist
eth0 192.168.124.0
eth1 192.168.128.0
eth2 192.168.127.0
ib1 10.192.2.0



To change the setings follow the below steps:

delete the wrong settings

oifcfg delif -global eth1/192.168.128.0
oifcfg delif -global eth2/192.168.127.0



oifcfg setif -global eth0/192.168.124.0:public
oifcfg setif -global ib1/10.192.2.0:cluster_interconnect

The new settings would take effect after the database bounce.


Note: The crs picks the interconnect information from the /etc/hosts and the database picks from the configuration of oifcfg.