TEMP tablespace usage

Temporary Segments in Database
SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;

Database Session that is using space in a sort segment

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,S.program,SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_ops FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE    T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.program, 
TBS.block_size, T.tablespace ORDER BY sid_serial;
Sort Space Usage by Statement
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used,  T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid;

Who is using UNDO tablespace??

select s.sid, 
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username order by undo_size_mb desc

To find UNDO tablespace active transactions

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
       NVL(s.username, 'None') orauser,
       s.program,
       r.name undoseg,
       t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
  FROM sys.v_$rollname    r,
       sys.v_$session     s,
       sys.v_$transaction t,
       sys.v_$parameter   x
 WHERE s.taddr = t.addr
   AND r.usn   = t.xidusn(+)
   AND x.name  = 'db_block_size'

Extract tablespace DDL

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tbs_list.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) || '/'
from dba_tablespaces tb;
spool off

Kill, restart or resume datapump expdp/impdp jobs

Find datapump jobs

SELECT owner_name, job_name, operation, job_mode, 

state, attached_sessions 

FROM dba_datapump_jobs 

WHERE job_name NOT LIKE 'BIN$%' 

ORDER BY 1,2

  Killing or stopping datapump jobs

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!

When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB orSTOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure.

Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

Resuming a stopped job

Identify your job using above query

> expdp system ATTACH=SYS_EXPORT_SCHEMA_01

Start/restart the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip

the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT

KILL Session

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'username'

ALTER SYSTEM KILL SESSION '14,30';

ORA-00020 maximum number of processes exceeded

ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba
    
2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining  these parameter values is as follows:
    
        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1
        
4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=100 scope=spfile;
    sql> alter system set sessions=115 scope=spfile;
    sql> alter system set transactions=126 scope=spfile;
    sql> shutdown abort
    sql> startup

Interview Questions

What is a DBA?

A DBA is a Database Administrator, and this is the most common job that you find a database specialist doing. There are Development DBAs and Production DBAs.
A Development DBA usually works closely with a team of developers and gets more involved in design decisions, giving advice on performance and writing good SQL.
That can be satisfying at a human level because you are part of a team and you share the satisfaction of the team's accomplishments.
A Production DBA (on the other hand) is responsible for maintaining Databases within an organization, so it is a very difficult and demanding job. He or she, often gets involved when all the design decisions have been made, and has simply to keep things up and running.
Therefore, of course, it is also a rewarding job, both financially and in terms of job satisfaction. But it's a more 'lonely' job than being a Development DBA. 
1. What DBA activities did you do today?

This is a loaded question and almost begs for you to answer it with "What DBA activities do you LIKE to do on a daily basis?." And that is how I would answer this question. Again, do not get caught up in the "typical" day-to-day operational issues of database administration. Sure, you can talk about the index you rebuilt, the monitoring of system and session waits that were occurring, or the space you added to a data file, these are all good and great and you should convey that you understand the day-to-day operational issues. What you should also throw into this answer are the meetings that you attend to provide direction in the database arena, the people that you meet and talk with daily to answer adhoc questions about database use, the modeling of business needs within the database, and the extra time you spend early in the morning or late at night to get the job done. Just because the question stipulates "today" do not take "today" to mean "today." Make sure you wrap up a few good days into "today" and talk about them. This question also begs you to ask the question of "What typical DBA activities are performed day to day within X Corporation?" 
2. Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain, as the database is not cutting archive logs to disk. 
3. Explain the difference between a data block, an extent and a segment. 
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called  extents. All the extents that an object takes when grouped together are considered the segment of the database object.
4. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete. 
5. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each. 
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly. 
6. What command would you use to create a backup control file?

Alter database backup control file to trace. 
7. Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened 
8. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message. 
9. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside. 
10. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual; 
11. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application. 
12. When a user process fails, what background process cleans up after it?

PMON 
13. Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database. 
14. How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE; 
15. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database. 

Linux: Delete Files Older than x number of days

Delete files older than x number of days (in my case 5 days)

find /temp -type f -name '*.dmp' -mtime +5 -exec rm -rf {} \;

Find and KILL Orphand Jobs - DATAPUMP

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2

drop table SYSTEM.SYS_EXPORT_SCHEMA_01;
drop table SYSTEM.SYS_EXPORT_TABLE_01;

Schema Size in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

Schema Data Space and Index Space in GB

SELECT owner,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'TABLE%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) DATASPACE,
  Round(Sum(CASE WHEN SEGMENT_TYPE LIKE 'INDEX%' THEN BYTES ELSE 0 END)/ (1024*1024*1024),2) INDEXSPACE
FROM dba_SEGMENTS GROUP BY owner order by owner

TEMP Usage

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid

Tablespace Usage Report

col tablespace_name format A22 heading "Tablespace"
col objects format 999999 heading "Objects"
col files format 9999
col allocated_mb format 9,999,990.000 heading "Allocated Size|(Mb)"
col used_mb format 9,999,990.000 heading "Used Size|(Mb)"
col ts_type format A6 heading "TS|type"
col max_size_mb format 9,999,990.000 heading "Max Size|(Mb)"
col max_free_mb format 9,999,990.000 heading "Max Free|(Mb)"
col max_free_pct format 999 heading "Max Free|%"

BREAK ON REPORT
COMPUTE SUM LABEL "Total SUM:" OF objects files allocated_mb used_mb max_size_mb MAX_FREE_MB ON REPORT
COMPUTE AVG LABEL "Average %:" OF FREE_PCT MAX_FREE_PCT ON REPORT

SELECT ts.tablespace_name, status,
DECODE(dt.contents,'PERMANENT',DECODE(dt.extent_management,'LOCAL',DECODE(dt.allocation_type,'UNIFORM','LM-UNI','LM-SYS'),'DM'),'TEMPORARY','TEMP',dt.contents) ts_type,
NVL(s.count,0) objects,
ts.files,
ts.allocated/1024/1024 allocated_mb,
ROUND((ts.allocated-nvl(ts.free_size,0))/1024/1024,3) used_mb,
ROUND(maxbytes/1024/1024,3) max_size_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))/1024/1024,3) max_free_mb,
ROUND((maxbytes-(ts.allocated-nvl(ts.free_size,0)))*100/maxbytes,2) max_free_pct
FROM
(
SELECT dfs.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT fs.tablespace_name, sum(fs.bytes) free_size
FROM dba_free_space fs
GROUP BY fs.tablespace_name)
dfs,
(SELECT df.tablespace_name, count(*) files, sum(df.bytes) allocated,
sum(DECODE(df.maxbytes,0,df.bytes,df.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_data_files df
WHERE df.status = 'AVAILABLE'
GROUP BY df.tablespace_name)
ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
UNION
SELECT dtf.tablespace_name,files,allocated,free_size,maxbytes
FROM
(SELECT tf.tablespace_name, count(*) files, sum(tf.bytes) allocated,
sum(DECODE(tf.maxbytes,0,tf.bytes,tf.maxbytes)) maxbytes, max(autoextensible) autoextensible
FROM dba_temp_files tf
GROUP BY tf.tablespace_name)
dtf,
(SELECT th.tablespace_name, SUM (th.bytes_free) free_size
FROM v$temp_space_header th
GROUP BY tablespace_name)
tsh
WHERE dtf.tablespace_name = tsh.tablespace_name
) ts,
( SELECT s.tablespace_name, count(*) count
FROM dba_segments s
GROUP BY s.tablespace_name) s,
dba_tablespaces dt,
v$parameter p
WHERE p.name = 'db_block_size'
AND ts.tablespace_name = dt.tablespace_name
AND ts.tablespace_name = s.tablespace_name (+)
ORDER BY 1
/

Alert Log Monitoring

#!/bin/bash
#Alert Log Monitoring Script

ALERT_LOG_FILE=/u01/app/oracle/diag/rdbms/XXXX/trace/alert_XXXX.log
EMAIL_FILE=/tmp/alert_mail.log
HOSTNAME=$(hostname)
#Error out if Alert Log is missing
if [  ! -f $ALERT_LOG_FILE ] ; then
 echo "**** $ALERT_LOG_FILE FILE MISSING FROM ALERT LOG MONITOR ******" > $EMAIL_FILE
 cat $EMAIL_FILE|mailx -s "Alert Log Errors" test@test.com test1@test.com
 exit 1
fi
ALERT_LOG_LINE_CNT_FILE=${ALERT_LOG_FILE}.ctr
#First run of the script or somebody deleted it.Therefore start from zero.
if [  ! -f $ALERT_LOG_LINE_CNT_FILE ] ; then
echo 0 > $ALERT_LOG_LINE_CNT_FILE
fi
STORED_LINE_CTR=`cat $ALERT_LOG_LINE_CNT_FILE`
ALERT_LOG_LINE_CTR=`cat $ALERT_LOG_FILE|wc -l`
#check to see whether somebody has recycled alert log file.
#in this case the STORED_LINE_CTR will be higher than ALERT_LOG_LINE_CTR
#If so just reset STORED_LINE_CTR to 0.
if [ $ALERT_LOG_LINE_CTR -lt $STORED_LINE_CTR ] ; then
STORED_LINE_CTR=0
fi
LINES_TO_MONITOR=`expr $ALERT_LOG_LINE_CTR - $STORED_LINE_CTR`
tail -n $LINES_TO_MONITOR $ALERT_LOG_FILE|grep -i "ora-"  > /tmp/alert_mail.log
#Reset line number to ctr file to skip the scanned rows in the next run
echo $ALERT_LOG_LINE_CTR > $ALERT_LOG_LINE_CNT_FILE
#Email only if the file is not empty
if [ -s $EMAIL_FILE ] ; then
 cat $EMAIL_FILE|mailx -s "$HOSTNAME - Alert Log Errors"  test@test.com test1@test.com
fi

FileSystem Monitoring - Threshold 85%

#!/bin/bash
# set usage alert threshold
THRESHOLD=85

#hostname
HOSTNAME=$(hostname)

#mail client
MAIL=/bin/mailx

# store all disk info here
EMAIL=""

for line in $(df -hP | egrep '^/dev/' | awk '{ print $6 "_:_" $5 }')
do

        part=$(echo "$line" | awk -F"_:_" '{ print $1 }')
        part_usage=$(echo "$line" | awk -F"_:_" '{ print $2 }' | cut -d'%' -f1 )

        if [ $part_usage -ge $THRESHOLD -a -z "$EMAIL" ];
        then
                EMAIL="$(date): Running out of diskspace on $HOSTNAME\n"
                EMAIL="$EMAIL\n$part ($part_usage%) >= (Threshold = $THRESHOLD%)"

        elif [ $part_usage -ge $THRESHOLD ];
        then
                EMAIL="$EMAIL\n$part ($part_usage%) >= (Threshold = $THRESHOLD%)"
        fi
done

if [ -n "$EMAIL" ];
then
        echo -e "$EMAIL" | $MAIL -s "Alert: Partition(s) almost out of diskspace on $HOSTNAME" "test@test.com test1@test.com"
fi

ORA-01653 error while upgrading APEX from 4.2 to 5.0 and Application Express is already installed (APEX_050000)

Trying to upgrading from 4.2 to 5.0

Steps followed

- Initiated @apexins.sql APEX SYSAUX TEMP /i/ 
- In the middle of the upgrade I got failed due to 
ERROR at line 1: 
ORA-01653: unable to extend table APEX_050000.WWV_FLOW_UPGRADE_PROGRESS by 8 in 
tablespace APEX 
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 973 
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 1659 
ORA-01658: unable to create INITIAL extent for segment in tablespace APEX 
ORA-06512: at "APEX_050000.WWV_FLOW_UPGRADE", line 3715 
ORA-06512: at line 4 

- I have added more space to APEX 
- Now tried to initiated @apexins.sql APEX SYSAUX TEMP /i/ but I got Error: This version of Application Express is already installed (APEX_050000). 


How to fix this issue? 

• I am getting "Error: This version of Application Express is already installed" error when i am re initiating apex upgrade, so commented "execute immediate 'bogus statement to force exit';" line in "apxprereq.sql" 

• Next I got "ORA-01920: user name 'APEX_040200' conflicts with another user or role name", this time commented "create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;" line in "coreins.sql" 

• Now initiate upgrade using "@apexins.sql APEX SYSAUX TEMP /i/" 

Everything went fine. And all existing applications working fine with new look and feel in APEX5.

How to upgrade APEX from 4.2 to 5.0?

Pre-Installation checks

select comp_name, version from dba_registry;

COMP_NAME                                          VERSION

-------------------------------------------------- --------------------------

Oracle Enterprise Manager                          11.2.0.3.0

Oracle Application Express                         4.2.6.00.03

OWB                                                11.2.0.3.0

OLAP Catalog                                       11.2.0.3.0

Spatial                                            11.2.0.3.0

Oracle Multimedia                                  11.2.0.3.0

Oracle XML Database                                11.2.0.3.0

Oracle Text                                        11.2.0.3.0

Oracle Expression Filter                           11.2.0.3.0

Oracle Rules Manager                               11.2.0.3.0

Oracle Workspace Manager                           11.2.0.3.0

COMP_NAME                                          VERSION

-------------------------------------------------- --------------------------

Oracle Database Catalog Views                      11.2.0.3.0

Oracle Database Packages and Types                 11.2.0.3.0

JServer JAVA Virtual Machine                       11.2.0.3.0

Oracle XDK                                         11.2.0.3.0

Oracle Database Java Packages                      11.2.0.3.0

OLAP Analytic Workspace                            11.2.0.3.0

Oracle OLAP API                                    11.2.0.3.0

select account_status from dba_users where username = 'APEX_PUBLIC_USER';

ACCOUNT_STATUS

--------------------------------

OPEN

select account_status from dba_users where username = 'ANONYMOUS';

ACCOUNT_STATUS

--------------------------------

OPEN

select default_tablespace, temporary_tablespace from dba_users where username = 'APEX_040200';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

APEX                                                     TEMP

The script we need to run to do the installation – apexins.sql – takes three parameters :

• The default tabelspace of the APEX owner schema
• The default tablespace of FLOWS_FILES schema
• A temporary tablespace in the database
• A virtual directory for APEX images.

We already know that the default tablespace for the APEX owner is APEX

We also know that the temporary tablespace is called TEMP

As for the FLOWS_FILES schema…

select default_tablespace from dba_users where username = 'FLOWS_FILES';

DEFAULT_TABLESPACE

------------------------------

SYSAUX

cd /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0/apex

@apexins.sql APEX SYSAUX TEMP /i/

@apxchpwd.sql

@apxldimg.sql /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0

For embedded PL/SQL Gateway

@apex_epg_config.sql /media/mnt01/app/oracle/product/11.2.0/db_1/apex5.0

select dbms_xdb.gethttpport from dual;

http://hostname:port/apex/apex_admin

HugePage Implementation

- Single database instance 

- Mem: 528265812k (528 GB) 

Recommended values for SGA, PGA, SHARED_POOL, DB_CACHE and hugepages

SGA_TARGET=150G

SGA_MAX_SIZE=150G

PGA_AGGREGATE_TARGET=100G

SHARED_POOL_SIZE=50G

DB_CACHE_SIZE=70G

vm.nr_hugepages=77000

STEPS

• Log into DB as SYS user.
• create pfile from spfile;
• Take the backup of pfile;
• Change parameters 

              ALTER SYSTEM SET USE_LARGE_PAGES=ONLY SCOPE=SPFILE; 
                  ALTER SYSTEM SET SGA_TARGET=150G SCOPE=SPFILE; 
                  ALTER SYSTEM SET SGA_MAX_SIZE=150G SCOPE=SPFILE; 
                  ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100G SCOPE=SPFILE; 
                  ALTER SYSTEM SET SHARED_POOL_SIZE=50G SCOPE=SPFILE; 
                  ALTER SYSTEM SET DB_CACHE_SIZE=70G SCOPE=SPFILE; 
                  ALTER SYSTEM SET MEMORY_MAX_TARGET=0 SCOPE=SPFILE; 
                  ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE; 
                  ALTER SYSTEM SET SORT_AREA_SIZE=0 SCOPE=SPFILE;

• create pfile from spfile
• shut immediate;
• As root add below valules in sysctl.conf

              vm.nr_hugepages=77000

                  vm.hugetlb_shm_group=501 (To know the group use fgrep dba /etc/group)  

• As a root sysctl -w vm.max_map_count=262144
• As a root sysctl -p
• reboot server
• Edit pfile and Remove memory_max_target, memory_target and sort_area_size from pfile
• start database with pfile

                startup pfile=/u01/app/oracle/product/11.3.0/db_1/dbs/initXXX.ora

• create spfile from pfile
• shut immediate
• startup

Large Pages used by this instance (#pages) = HugePages_Total – HugePages_Free + HugePages_Rsvd

Ref.

• Huge Page Implementation Note 361468.1
• Large Pages Information in the Alert Log Note: 1392543.1
• Oracle Not Utilizing Hugepages (Doc ID 803238.1)
• Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
• USE_LARGE_PAGES To Enable HugePages (Doc ID 1392497.1)
• Maximum SHMMAX values for Linux x86 and x86-64 (Doc ID 567506.1)
• Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device (Doc ID 301830.1)

Why HugePages?

HugePages is a feature integrated into the Linux kernel with release 2.6. This feature basically provides the alternative to the 4K page size (16K for IA64) providing bigger pages.

Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory.

• HugePages can be allocated on-the-fly but they must be reserved during system startup. Otherwise the allocation might fail as the memory is already paged in 4K mostly.

• HugePage sizes vary from 2MB to 256MB based on kernel version and HW architecture (See related section below.)

• HugePages are not subject to reservation /  release after the system startup unless there is system administrator intervention, basically changing the hugepages configuration (i.e. number of pages available or pool size)

The Size of a HugePage

The size of a single HugePage varies according to:

• Kernel version/linux distribution
• HW Platform

The actual size of the HugePage on a specific system can be checked by:
      $ grep Hugepagesize /proc/meminfo

-->Following command to determine the current HugePage usage. The default HugePage size is 2MB on Oracle Linux 5.x and as you can see from the output below, by default no HugePages are defined.

        $ grep Huge /proc/meminfo

1. Large Pages used by this instance (#pages) = HugePages_Total – HugePages_Free + HugePages_Rsvd
2. (HugePages_Total- HugePages_Free)*2MB will be the approximate size of SGA
3. Disable AMM (Automatic Memory Management)
4. Check the MEMORY_TARGET parameters are not set for the database and SGA_TARGET and PGA_AGGREGATE_TARGET parameters are being used instead.
5. If you are running Oracle 11.2.0.2 or later, you can set the USE_LARGE_PAGES initialization parameter to "only" so the database fails to start if it is not backed by HugePages.

Ref.

• Huge Page Implementation Note 361468.1
• Large Pages Information in the Alert Log Note: 1392543.1
• Oracle Not Utilizing Hugepages (Doc ID 803238.1)
• Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
• USE_LARGE_PAGES To Enable HugePages (Doc ID 1392497.1)
• Maximum SHMMAX values for Linux x86 and x86-64 (Doc ID 567506.1)
• Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device (Doc ID 301830.1)