ORA-27102: out of memory followed by Linux-x86_64 Error: 28: No space left on device during database startup

Leave a Comment / Database Administration, Troubleshooting /
One-liner Solution

Check and set the value of SHMALL as below, to solve ORA-27102 :
SHMALL = Sum of all the SGAs on the system / page size

Scenarios

Scenario 1
You are configuring a database and using Automatic Shared Memory Management (ASMM). While trying to start up the database instance, you get the error shown in the below code block.

Scenario 2
You have increased the memory capacity (Random Access Memory, RAM) of the System. Then increased the value of System Global Area (SGA) up to 50% of the RAM, as per Oracles’ best practice. You also set the value of SHMMAX equal to the value of target SGA. But getting the error in the below code block, while trying to startup the instance.
SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Root Cause

The parameter SHMALL is not set properly or, too small.

SHMALL indicates the total amount of shared memory (in pages) that can be used by the system. The default value of SHMALL in Red Hat Enterprise Linux is 2097152 (in pages). If PAGE_SIZE is 4096 bytes, this value (2097152) indicates that, 8 GB of shared memory is available to the system. So for this setup, to avoid ORA-27102, you must tune the values of SHMALL and SHMMAX with the increase in the total SGA of the system over 8 GB.

Detailed Solution
Task 1 - Calculate the Value of SHMALL
1. Login as the root user
2. Determine the PAGE_SIZE
# getconf PAGE_SIZE
4096
3. Determine the sum of all the SGAs in the system Suppose there are two database instances in your system. To determine the SGA for each of them, run the following from SQL*Plus :
determine SGA to fix ora-27102

Which is 14 GB of SGA, for one instance. Suppose, the value of SGA for another instance is 4 GB. So the sum of all the SGAs in the system will be 18 GB.

Note: In this scenario, the value of SHMMAX should be 18GB or more.

4. Calculate the value of SHMALL
SHMALL = Sum of all the SGAs on the system / page size
= 18 GB / 4096 bytes
= ‭19327352832 bytes ‬/ 4096 bytes‬
= 4718592 pages‬
Task 2 - Set the value of SHMALL
Permanent Workaround:
1. Login as the root user 2. Set the SHMALL to 4718592 in the /etc/sysctl.conf file
# vi /etc/sysctl.conf

kernel.shmall = 4718592
3. Run the following command to load the new value
# sysctl –p
NOTE: If you run this command, no system reboot is required.
4. Check the changed value
# cat /proc/sys/kernel/shmall
4718592
Non-persistent Workaround:
If you don’t want to change the value permanently, for some reason you may do the following:
# echo 4718592 > /proc/sys/kernel/shmall
NOTE: The value will reset to previous, once you reboot the system.

After the workaround is performed, switch back to the oracle user and retry the startup command. I hope, after going through this post, you solved ORA-27102. That’s all for toady. Take Care!

For more learning Click Me!

References
My Oracle Support (MOS) Doc ID – 301830.1
RedHat Customer Portal – Setting SHMALL Parameter

ORA-27102: out of memory followed by Linux-x86_64 Error: 28: No space left on device during database startup Read More »

Bandwidth Calculation for DC-DR Replication in Dataguard

Leave a Comment / Dataguard /
Long Story Short
The formula for Bandwidth calculation :
Required bandwidth = ((REDO rate (in bytes per second) / 0.75) * 8) / 1,000,000 (in Mbps)

Two ways to calculate the REDO rate:

Internet Suggested -
SQL>SELECT AVG(VALUE)
FROM V$SYSMETRIC_HISTORY
WHERE METRIC_NAME = 'Redo Generated Per Sec';

I Suggest -
SQL>SELECT MAX(AVERAGE)
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME='Redo Generated Per Sec';
Detailed Story
Once I was looped in an email describing how to calculate the Bandwidth for DC-DR replication in a dataguard setup. Let’s go through that email first, 
Dear Sir,
Please do the following to calculate the Bandwidth needed for DC-DR replication:

1. Connect to production db as sysdba and run the below query:
SQL> select avg (value) from v$sysmetric_history where metric_name = 'Redo Generated Per Sec';

2. Take the value from the above script. Put the value in the below equation's Redo rate bytes per sec :
Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 = bandwidth in Mbps

This would be your minimum bandwidth requirement for the Data Guard setup.

The person who sent this mail found this method of bandwidth calculation from a Google search. But two questions arose in my mind :

  1. V$SYSMETRIC_HISTORY only provides the metric history for the past hour. Will this value reflect the exact redo generation rate every time?
  2. Why should I divide the derived value with 0.75 ?

I found interesting answers:

  1. The exact moment of calculating the value of redo generation matters. For example, you should not take this value at the busiest business hour (Peak hour) of a typical business day, as unusual amount of redo may be generated during that time. Also, you can’t take this value at the weekends, as minimal redo generation rate should not be out target value. But, if you calculate the value from “V$SYSMETRIC_HISTORY” view, you will only get the value of the past hour (Max). Thus, I fear you may get a misleading value, while calculate using this view.
  2. We are considering a 25% loss at the internal cabling (LAN) level. From here, we got the number 0.75.

Point 2 seems “ok”. But I think we can calculate the value for “point 1” more accurately using “DBA_HIST_SYSMETRIC_SUMMARY” view, as it holds the metric data (like redo generation rate) in an hourly basis, for a week.

Let’s get some practical demonstration !

Bandwidth calculation using "V$_SYSMETRIC_HISTORY"
First we will find out the average value of the metric “redo generated per second” from “V$SYSMETRIC_HISTORY” view, for the past hour. 
SQL>SELECT AVG(VALUE)
FROM V$SYSMETRIC_HISTORY
WHERE METRIC_NAME = 'Redo Generated Per Sec';

AVG(VALUE)
------------------
78140.5136
So, Redo rate bytes per sec = 78140.5136
Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((78140.5136/0.75)*8)/ 1,000,000 = 0.8335 Mbps

So, based on this calculation, the bandwidth requirement is 0.8335 Mbps.
Bandwidth calculation using "DBA_HIST_SYSMETRIC_SUMMARY"
Now we will find out two things from the view “DBA_HIST_SYSMETRIC_SUMMARY” –
  1. At each hour, what is the maximum value of the metric “redo generated per second”.
  2. At each hour, what is the average value of the metric “redo generated per second”.
You may use the following SQL query : 
SQL> select INSTANCE_NUMBER,
BEGIN_TIME, 
END_TIME,
MAXVAL,
AVERAGE
from dba_hist_sysmetric_summary
where METRIC_NAME='Redo Generated Per Sec';
Sample Output :
Bandwidth calculation using "DBA_HIST_SYSMETRIC_SUMMARY"
Note
My suggestion is to consider the maximum value from the AVERAGE column. The maximum value of the MAXVAL column will provide an unnecessarily high value. 
SQL> SELECT MAX(AVERAGE)
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME='Redo Generated Per Sec';

MAX(AVERAGE)
--------------------
6636609.29
So, Redo rate =  6636609.29 bytes per second
Required bandwidth = ((Redo rate in bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((6636609.29/0.75)*8)/ 1,000,000 = 70.79 Mbps = 71 Mbps Approximately.
Finally ,
Huge difference between the two results!

First one using “V$SYSMETRIC_HISTORY” view gives 0.8335 Mbps while the later using “DBA_HIST_SYSMETRIC_SUMMARY” view provides 71 Mbps. You can use either solution. But the later made more sense to me. Now the choice is yours!

That’s all for today! Click here for more learning.

Bandwidth Calculation for DC-DR Replication in Dataguard Read More »

SQL Join

Leave a Comment / SQL Fundamentals /

Why use SQL Joins?

SQL Join combines the rows from two or more tables to visualize an expected result. The associated tables should have at least one column that contains common data. An alternative to SQL Joining is the usage of subqueries. But, in most cases, the retrieval time of the queries using SQL join is faster than the queries that use a subquery.

Effects of Joining

SQL Join forms a temporary table and visualizes the final output from it. So there is no possibility of alteration in associated tables’ structure and data.

SQL Join has the reverse effect of normalization. During normalization, tables get split into more specific tables with a relationship between them. Also, there will be a minimal amount of duplicate and NULL values in each table. Join invokes the same relationship to construct a temporary table that contains the expected result. For several types of joins, this temporary table is de-normalized in nature containing NULL and duplicate values.

List of Joins

ANSI standard for SQL specifies five types of joins.

  1. Inner Join
  2. Left Outer Join / Left Join
  3. Right Outer Join / Right Join
  4. Full Outer Join
  5. Cross Join

Note:

  • In Oracle we have another join named “Natural Join”. But in Microsoft SQL Server, Natural Join is not available.
  • It’s possible to join a table with “itself” using any joining technique, which is known as Self Joining.   

Brief Definition

  1. Natural Join: Returns only matched records from both tables. But, there is a condition. The heading of joining columns must be the same.
  2. Inner Join: Returns only matched records from the associated tables.
  3. Outer Join:
    • Left Outer Join: Returns matched records from the associated tables and unmatched records only from the table on left side.
    • Right Outer Join: Returns matched records from the associated tables and unmatched records only from the table on right side.
    • Full Outer Join: Returns matched and unmatched records from the associated tables with NULL for unmatched records from both tables.
  4. Cross Join: Returns the Cartesian Product of the rows of the associated tables.

In a nutshell,
Natural Join = Matched, with same column heading.
Inner Join = Matched.
Left Outer Join = Matched plus Left Unmatched.
Right Outer Join = Matched plus Right Unmatched.
Full Outer Join = Matched plus Left Unmatched plus Right Unmatched.
Cross Join = Cartesian Product.

Visual Illustration

Syntax

Natural Join

SELECT columns
FROM table1
NATURAL JOIN table2;

Inner Join

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Left Outer join

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Right Outer Join

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Full Outer Join

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

Cross join

SELECT columns
FROM table1
CROSS JOIN table2;

Experiment and Result

Consider the following two tables named EMPLOYEES and DEPARTMENTS

EMPLOYEES

EMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_ID
10001LOREM202
10002IPSUM207
10003DOLOR205
10004SIT211
10005AMET201

 

DEPARTMENTS

DEPARTMENT_ID DEPARTMENT_NAME
201 HR
202 SALES
203 FINANCE
204 SERVICE
205 IT
NATURAL JOIN
(Matched Only)

SELECT *
FROM Employees
NATURAL JOIN Departments;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
INNER JOIN
(Matched Only)

SELECT E.*,
D.department_name
FROM Employees E
INNER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
LEFT OUTER JOIN
(Matched+Left Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
LEFT OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
20710002IPSUMNULL
21110004SITNULL
RIGHT OUTER JOIN
(Matched+Right Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
RIGHT OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_NAME
201 10005 AMET HR
202 10001 LOREM SALES
205 10003 DOLOR IT
NULL NULL NULL SERVICE
NULL NULL NULL FINANCE
FULL OUTER JOIN
(Matched+Left Unmatched+Right Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
FULL OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_NAME
201 10005 AMET HR
202 10001 LOREM SALES
205 10003 DOLOR IT
NULL NULL NULL SERVICE
NULL NULL NULL FINANCE
207 10002 IPSUM NULL
211 10004 SIT NULL
CROSS JOIN
(Cartesian Product)

SELECT *
FROM Employees
CROSS JOIN Departments;

EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAME
10001LOREM202201HR
10001LOREM202202SALES
10001LOREM202203FINANCE
10001LOREM202204SERVICE
10001LOREM202205IT
10002IPSUM207201HR
10002IPSUM207202SALES
10002IPSUM207203FINANCE
10002IPSUM207204SERVICE
10002IPSUM207205IT
10002DOLOR205201HR
10003DOLOR205202SALES
10003DOLOR205203FINANCE
10003DOLOR205204SERVICE
10003DOLOR205205IT
10004SIT211201HR
10004SIT211202SALES
10004SIT211203FINANCE
10004SIT211204SERVICE
10004SIT211205IT
10005AMET201201HR
10005AMET201202SALES
10005AMET201203FINANCE
10005AMET201204SERVICE
10005AMET201205IT

That’s all for today.

Click here for more learning !

SQL Join Read More »

Audit Vault Server Installation – Software Download

Leave a Comment / Database Security, Oracle AVDF /

In this post, I am sharing the procedure of downloading Audit Vault Server (AVS) Software from Oracle Software Delivery Cloud. You may consider this post as the first step of the Audit Vault Server Installation. Please have a look at my previous post titled Audit Vault Server (AVS) – Installation Prerequisites. You will get an overview of the prerequisites to take care of before installing the Audit Vault Server, compiled based on my own experience.

Downloading Steps

Downloading software from Oracle Software Delivery Cloud (popularly known as E-Delivery Cloud) is not that complicated. You have to follow some simple steps mentioned below:

Step 1 –  Go to Oracle Software Delivery Cloud from your favorite Web Browser. Click Sign in if you already have an Oracle Account and provide your Username and Password. Otherwise, create one.

Step 2 – Search with the text Oracle Audit Vault and Database Firewall. It will list all available AVDF versions. Click on the version you want to download. Here my Target AVDF version is 12.2.0.9.0. So I am clicking on that one.

Step 3 – The software is added to the Download Queue. Go to the top right corner of the page. Press the Continue button. On the landing page choose a value for Platforms/Languages option. Press the Continue button.

Step 4 – Accept Oracle Standard Terms and Restrictions at the very bottom of the next landing page. Press the Continue button afterward. The download page will appear. We only need three files named V980012-01.iso, V980013-01.iso and, V980014-01.iso. For now, the Audit Vault Server installation is our only goal, so we can skip the other files to save a little bit of time and disk space.

Step 5 – Click on the Download button. A file prefixed Oracle_SSN_DLM will be download. Click on that file. Choose the destination you want to save the software iso files. Click on the Next button.

Step 6 – The download will start. You can pause or resume the download but don’t close the window. It will abort the download. Once the download is complete, you are ready for the Audit Vault Server installation. 

That’s all for today. My upcoming post titled Audit Vault Server Installation – Step-by-Step Procedure will cover a detailed software  installation process.

Stay Tuned!

Audit Vault Server Installation – Software Download Read More »

Audit Vault Server (AVS) – Installation Prerequisites

Leave a Comment / Database Security, Oracle AVDF /

In this post, I am sharing the prerequisites for installing the Audit Vault Server, obviously, on the basis of my own experience. Note that, this information is most relevant to AVDF (Audit Vault and Database Firewall) Release 12.2 (Exactly, I was working with AVDF version 12.2.0.9.0, while writing this post).

Hardware Requirements

Audit Vault needs a dedicated Intel x86-64 bit-based Server or Virtual Machine. Yes, dedicated! That means you can’t use that specific Server to serve any other purpose. It’s a good idea to investigate the Version/Release information of the Embedded Operating System of any specific Audit Vault Server you want to install. For example, version 12.2.0.9.0 includes Oracle Linux release 6 with UEK version 4 (kernel-uek-4.1.12-124.19.7.el6uek.x86_64). Now if you buy/arrange such a Server or Virtual Machine which don’t support this Release of Oracle Linux, you will not be able to install the software there. Hardware Certification List (HCL) will be helpful to sort out this dependency issue.

Audit Vault Server

Memory Requirements

The minimum requirement is 8 GB. But for smoother operation, try to allocate at least 2x of the minimum requirement i.e. at least 16 GB of memory. Personally, I like to allocate around 25 GB, if memory is not an issue for the client I am working with.

Disk Space Requirements

The minimum requirement is 220 GB though Oracle recommends 300 GB at least. Obviously, you have to decide it yourself based on the size of your Audit data. For most of my clients, I proposed 500 GB+. But don’t worry! AVDF will let you enhance the storage capacity later.

Target Requirements

In AVS, the “Target” means each Server/Machine from which audit data will be collected. Java version 8 is the minimum JAVA version you will need in the Targets. AVS supports a variety of Target OS platforms including Oracle Linux, Redhat Linux, IBM AIX, HP-UX, Windows Server, etc.

That’s all for today. You should go through Oracle Audit Vault and Database Firewall Installation Guide for more details on installation prerequisites. My upcoming post titled Audit Vault Server – Installation Guide will demonstrate a step by step procedure with all relevant installation snapshots.

Stay Tuned !

Audit Vault Server (AVS) – Installation Prerequisites Read More »