Oracle RAC, ASM, Exadata, Cloud, and More..

ORA-00338: log 32 of thread 3 is more recent than control file

The solution is to apply patch 12770551.

We got this error ORA-338 in the Data Guard environment with ASYNC redo transport. The connection with the remote database is closed and will not reopen until the next log switch. Although it's not a critical situation and is not affect the database operation, applying the patch 12770551 is simple to get rid of the error.

Summary steps to apply PSU 11.2.0.3.2

It’s recommended to patch the PSU manually on IBM Linux on z and other platform. The opatch auto is still testing by Oracle support. and it won’t be stable (at least from what we’ve been tested) until July 2013 PSU patchsets. There are couple note that worth to mention:

· Before applying GI home, you must run rootcrs.pl to unlock the permissions. Otherwise, the patching process will be failed with Opatch failed with error code 39.

· When applying a GI patch to either the Grid Home or Database Home, you use "napply"

· When applying a Database patch to either Database Home or Grid Home, you use "apply"

1) export PATH=$ORACLE_HOME/OPatch:$PATH

2) opatch version

OPatch Version: 11.2.0.3.4 ß-------- Make sure you have the latest opatch version.

3) opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./13696251

Oracle Interim Patch Installer version 11.2.0.3.4

…….

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

4) sudo -u root $ORACLE_HOME/crs/install/rootcrs.pl –unlock Don’t forget this step!

[sudo] password for oracle:

Using configuration parameter file: /u01/app/oracle/11.2.0.3/grid/crs/install/crsconfig_params

Grid Home Patch:

5) opatch napply -oh /u01/app/oracle/11.2.0.3/grid -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696251

6) opatch apply -oh /u01/app/oracle/11.2.0.3/grid -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696216

Database Patch:

7) opatch napply -oh /u01/app/oracle/11.2.0.3/racdb -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696251/custom/server/13696251

8) opatch apply -oh /u01/app/oracle/11.2.0.3/racdb -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696216

9) /data/oracle/bkup1/software/psu11.2.0.3.2/13696251/custom/server/13696251/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/11.2.0.3/racdb

10) as root: /u01/app/oracle/11.2.0.3/grid/rdbms/install/rootadd_rdbms.sh

11) as root : /u01/app/oracle/11.2.0.3/grid/crs/install/rootcrs.pl -patch

OPatch failed to locate Central Inventory

Problem:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo
OPatch failed with error code 73

Solution:

+ASM2 > /aps/oracle/product/11.2.0.3/grid/oui/bin/runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/aps/oracle/product/11.2.0.3/grid ORACLE_HOME_NAME=Ora11g_gridinfrahome1 CLUSTER_NODES=glscind01,glscind02 "INVENTORY_LOCATION=/aps/oracle/oraInventory" -invPtrLoc "/aps/oracle/product/11.2.0.3/grid/oraInst.loc" LOCAL_NODE=glscind02
Starting Oracle Universal Installer...

    Checking swap space: must be greater than 500 MB.   Actual 7844 MB    Passed
    The inventory pointer is located at /aps/oracle/product/11.2.0.3/grid/oraInst.loc
    The inventory is located at /apps/oracle/oraInventory
    'AttachHome' was successful.

Frequency v.s Heigh Balanced Histogram

Frequency

· Each bucket has its own size (counts of rows with that value)

· NDV <= Buckets request <=254

· One distinct value per bucket

· Bucket_size = rows with “that” particular value

Height Balanced

· Bucket size = (num_rows – num_nulls) / num_buckets

· Values are sorted before splitting them into buckets

· One or more distinct values per bucket

· Only values of end-points are stored in histogram

· Bucket_size = (Orig_CarD – Nnulls) / Buckets_total

dbms_stats

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

31 => this is the default value

If you like to change it to 45 days, then use the ALTER_STATS_HISTORY_RETENTION procedure

SQL> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual; => this query will return the date statistics have been purged up to.

GET_STATS_HISTORY_AVAILABILITY

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

18-APR-13 07.20.37.099029000 PM -05:00

SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = ‘GLS’; è this query shows the time when given tables’ statistics were re-gathered

TABLE_NAME STATS_UPDATE_TIME

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

GLS_CONTACT_NEW 19-MAY-13 09.56.39.845243 PM -05:00

GLS_CONTACT_NEW 06-MAY-13 05.22.14.338999 PM -05:00

To restore table statistics: For example, for GLS_CONTACT_NEW table, the statistics on May 19 is bad but May 6^th is good, so you like to restore it to May 6^th.

SQL> execute dbms_stats.restore_table_stats('GLS','GLS_CONTACT_NEW','06-MAY-13 05.22.14.338999 PM -05:00');

PL/SQL procedure successfully completed.

To compare statistics for a table from two timestamps or date in the past

select * from table(dbms_stats.diff_table_stats_in_history
(ownname => upper('&ownname'),
tabname => upper('&tabname'),
time1 => systimestamp,
time2 => to_timestamp('&time2','mm/dd/yyyy'),
pctthreshold => 0))

Whenever it is necessary, statistics can be restored from the history. The dbms_stats provides:

Execute dbms_stats.restore_table_stats(‘owner’, ‘table’, date)

Execute dbms_stats.restore_database_stats(date)

Execute dbms_stats.restore_dictionary_stats(date)

Execute dbms_stats.restore_fixed_objects_stats(date)

Execute dbms_stats.restore_schemas_stats(‘owner’,date)

Execute dbms_stats.restore_system_stats(date)

To set table statistics, you can use dbms_stats.set_table_stats

SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE', numrows=>140);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE',numblks=>10);
PL/SQL procedure successfully completed.

To set index statistics, you can use dbms_stats.set_index_stats to set number rows in index, number of leaf blocks, distinct keys, clustering factor, heigh of index

exec dbms_stats.set_index_stats (ownname=>'GLS',tabname=>'PROD_NEW',numdist=>1000);

DBMS_SQLTUNE package to Run the Sql Tuning Advisor (ID 262687.1)

Check out the SQL TUNING document is from metalink (ID 262687.1). It’s very handy when you need to get some advices to tune a poor SQL query using dbms_sqltune package.

DECLARE

my_task_name VARCHAR2(30);

my_sqltext CLOB;

BEGIN

my_sqltext := 'SELECT * ' ||

'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => 'SH',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => ‘gls_sql_tuning_task’,

description => 'Task to tune a query on a specified PRODUCT');

END;

Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => ‘gls_sql_tuning_task’);

Check the status of the task using following query:

select status from dba_advisor_log where task_name=‘gls_sql_tuning_task’;

View the Recommendations

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task(‘gls_sql_tuning_task’) from dual;

The report can suggest to add index, or gather statistics, or SQL profile. In my case, the sql_profile was recommended and to accept it, the performance was improved 99.9%

execute dbms_sqltune.accept_sql_profile(task_name =>‘gls_sql_tuning_task’, task_owner => 'SYSTEM', replace => TRUE);

12c Database cheat sheet

This sheet is work in progress. Please come back for the update...

select view_name from dab_views where view_name like 'CDB%'

select name, pdb from cdb_services;

show con_name

alter pluggable database all open;

startup nomount

select name, open_mode from v$pdbs;

alter database mount; --> select name, open_mode from v$pdbs; all mount

alter database open; -->PDBs$SEED mount mode

alter pluggable database all open: all pluggable databases in read/write

select name, pdb from cdb_services;

connect sys/oracle@pdb1 as sysdba

show con_name

show parameter ddl_lock_timeout

There is a single SPFILE per CDB to store prameters.

SQL> connect sys/oracle@pdb1 as sysdba
Connected.

Instance PDB changes example:

SQL> show parameter ddl_lock_timeout

NAME                     TYPE   VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer   0
SQL> alter system set ddl_lock_timeout=10;
System altered.

SQL> show parameter timeout

NAME                     TYPE   VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer   10

SQL> alter system set sessions=150;
System altered.

select name, value, ispdb_modifiable, con_id from v$system_parameter where ispdb_modifiable='TRUE' ==> there are 174 parameters

select p.con_id, p.name, p.dbid, s.name, s.value, s.ispdb_modifiable

from v$system_parameter s, v$pdbs p

where s.con_id = p.con_id and

s.name = 'sessions';

CREATE Permanent tablespaces in a CDB

SQL> create tablespace cdb_tools datafile '/u01/app/oracle/oradata/cdb1/cdb_tools01.dbf' size 10m;
Tablespace created.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> create tablespace pdb2_tools datafile '/u01/app/oracle/oradata/cdb1/pdb2/pdb2_tools01.dbf' size 5m;
Tablespace created.

Create user in Container ==> has to be inside the container DB

SQL> create user C##_MYID_ALL identified by ali container=ALL;
User created.

Create user in PDB ==> from PDB

SQL> create user MYID_DBA identified by ali container=CURRENT;
User created.

Create Tablespaces CDB and PDB

Prior version of Oracle, all the tablespaces belong to one database. In the CDB, however, there is one set of tablespaces belong to the root container and each PDB has its own set of tablespaces. All common objects are created in the root container's tablespace and is visible in the PDBs through links.

There is only one active UNDO tablespace per CDB, and it's common to all PDBs.

Create a tablespace in a root CDB

SQL> create tablespace cdb_tools datafile '/u01/app/oracle/oradata/cdb1/cdb_tools01.dbf' size 10m;

Tablespace created.

Create a permanent tablespace in a PDB

SQL> show con_name

CON_NAME
------------------------------
PDB2

SQL> create tablespace pdb2_tools datafile '/u01/app/oracle/oradata/cdb1/pdb2/pdb2_tools01.dbf' size 5m;

Tablespace created.

Managing CDB and PDB Databases

When you startup a CDB, the sequence of operations performed as following:

Instance is started -->Control files are opened --> The root container is opened redo logs and root data files -->The seed pluggable database in READ ONLY mode-->PDBs are stilled in MOUNTED mode--> Triggers can fire to open other PDBs

SQL> alter database cdb1 open;

SQL> alter pluggable database all close;

SQL> alter pluggable database all open;

SQL> alter pluggable database all close;

SQL > alter pluggable database all except GLS close;

SQL> alter pluggable database all open read only;

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ ONLY

PDB2 READ ONLY

GLS READ ONLY

SQL> alter pluggable database gls close;

SQL> alter pluggable database gls open;

SQL> select * from cdb_pdbs;

SQL > select * from cdb_tablespaces;

SQL> select * from cdb_data_files;

select name, pdb from cdb_services

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED MOUNTED

PDB1 MOUNTED

PDB2 MOUNTED

GLS MOUNTED

SQL > select name, pdb, network_name from cdb_pdb;

SQL> connect sys/oracle123@pdb1 as sysdba

Connected.

SQL > show con_name

To be continued...

Container and Pluggable Databases

Pluggable Database is the new feature and allows the DBA to consolidate larget number of small database applications departmental applications into a single larger RDBMS installation. It's a set of database schemas that appears logically to users and applications as a separate database. The Oracle Metadata and user data are totally separated into two sections. One is Container DB (CDB) stores metadata and One is Pluggable DB(PDB) which stores user data. This will reduce significant percentage of hardware deployment, patch and upgrade, instance and storage overhead and DBA time.

With Oracle 11g, there are non container databases and share nothing. As a result, there are too many background processes with high shared memory and many copies of Oracle metadata. When you need to upgrade/patch the database applications, it's time consuming as you have to upgrade each database.

Benefits of Pluggable Databases (PDBs):
Multiple databases in a centrally managed platform
Easy and Fast provisioning, patching and upgrading time savings
Central Management and administration of databases (backups, restore, disaster recovery, patching/upgrading)
Secure separation of duties (the administrator of an application can do all the required tasks, but cannot see other PDBs in the same Container Database(CDB)

From the Enterprise Manager
A Container Database (CDB) can be defined as target and a Pluggable database can be setup as a sub target of a CDB target. After the creation of the Oracle database, only objects in the data dictionary ar the Oracle-supplied objects.

Create a new CDB either via DBCA or SQLPLUS:

Configure an instance with init.ora parameter file then STARTUP NOMOUNT. Be sure to check (DB_NAME, CONTROL_FILES, DB_BLOCK_SIZE, _ENABLE_PLUGGABLE_DATABASE=TRUE
Create CBD using the CREATE DATABASE command with a new clause ENABLE PLUGGABLE DATABASE. This will create the root container with the control files during the mount phase, the redo log files and root data files. The root data files are used for the SYSTEM (metadata & dictionary) and SYSAUX (AWR). It also create the seed pluggable database with its own data files used for SYSTEM and SYSAUX. The FILE_NAME_CONVERT, like RMAN clone, to rename the data files of the seed pluggable database while copying from the root container to another location (see demo below)

SQL> connect / as sysdba
SQL > Startup nomount
SQL > Create database ORDER user sys identified by passcode user SYSTEM identified by passcode2 EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE UNDOTBS DEFAULT tablespace users ENABLE PLUGGABLE DATABASE

Create Pluggable database:

SQL> create pluggable database GLS admin user admin identified by admin file_name_convert= ('/pdbseed/','gls/');

Pluggable database created

SQL> alter pluggable database GLS open;

If you create the database with DBCA, then select the "Create As Container Database"

Enterprise Manager (EM) Cloud Control and Database Express

Enterprise Manager Database Control is not available in 12c database. There are enhancement in Enterprise Manager Cloud Control Interface and Database Express Navigation. The enterprise manager cloud control is composed of Four main components

The Oracle Management Repository (OMR)
The Oracle Management Service (OMS)
The Oracle Management Agent (OMA or agent) with target specific plug-ins
The Cloud Control Console

To start the whole enterprise manager cloud control frame work, follow the steps below:

Startup the repository database listener: lnsrctl start
Startup the repository database: $ORACLE_HOME/bin/sqlplus / as sysdba -> Startup
Start the OMS including OHS and WebLogic Managed Server: emctl start oms
Start the AGENT on OMS repository host: emctl start agent
Start the AGENT on the managed/target servers: emctl start agent

To stop the enterprise manager cloud control, perform the above steps (bottom to top)

Type of Credentials and Three levels of access

Named Credentials: You can store credential username/password, public key-private key pair. It's used when performing operation like system management tasks (running jobs, patching, changing password, etc)

Preferred Credentials: You can store access of managed targets login credentials in the Management Repository to simplify the access

Default Credentials: You can set a particular target type and is available for all the targets of the target type.

There are three level of access: View access, Edit access (change the credentials, name/password), and Full access (complete access including the ability to delete the named credential)

Enterprise Manager Database Express: It's a replacement of EM Database Control and is created via DBCA with XMLDB components installed. The homepage represents an overall view of the database instance status and activity. The EM Database Express is built on the Common Reporting Framework in the database and is available when the database is opened.

Extended Statistics

Starting 11g, you can create statistics for two or more related columns or an expression on a column that are part of a join condition are corrected.

Extended statistics on multi-column statistics

DECLARE
exst_name VARCHAR2(30);
BEGIN
exst_name := DBMS_STATS.CREATE_EXTENDED_STATS('HR','emp',
'(last_name,department_id)');
END;
/

SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('hr','emp',
'(last_name,department_id)') col_group_name
FROM DUAL
/

COL_GROUP_NAME
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L

SQL> select dbms_stats.create_extended_stats('hr','emp','(last_name,department_id)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS('HR','EMP','(LAST_NAME,DEPARTMENT_ID)')
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L

Extended statistics on Expression:

SQL> execute dbms_stats.gather_table_stats('HR','EMP', method_opt =>'for all columns size skewonly for columns(lower(last_name)) size skewonly');

To drop extended stats:

SQL> exec dbms_stats.drop_extended_stats('hr','emp','(last_name,department_id)');

Gathering statistics on Column Groups:
Method_opt enables you to gather statistics on column group:
FOR ALL COLUMNS SIZE AUTO: optimizer gathers statistics on all existing column groups
FOR COLUMNS: The column group is automatically created as part of statistics gathering

exec dbms_stats.gather_table_stats('HR','EMP',METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (LAST_NAME, DEPARTMENT_ID) SIZE SKEWONLY');

To monitor column groups about multi column or expression statstistics
SQL> select * from user_stat_extensions;

TABLE_NAME EXTENSION_NAME                 EXTENSION    CREATO DRO
---------- ------------------------------ ------------ ------ ---
EMP        SYS_STUAV0Z346SKNRSGSE2XA1MM6L ("LAST_NAME" USER   YES
                                          ,"DEPARTMENT
                                          _ID")

EMP        SYS_STU$OGQI#ACVJROJQ#33ZBENKN (LOWER("LAST USER   YES
                                          _NAME")

SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE e.EXTENSION_NAME=t.COLUMN_NAME
AND    e.TABLE_NAME=t.TABLE_NAME
AND    t.TABLE_NAME='EMP';

Cloud Control Components

Cloud Control core components:

· OMR: Oracle Management Repository

· OMS: Oracle Management Services

· OMA/Agent: Oracle Management Agent with Plug-ins

· GUI interface / Cloud Control and EMCLI command line interface

Definition:

Target: is any software or system for which there is a plug-in. Each agent plug-in is specific to a particular target type and offers special management capabilities to suite that target type

Target Type: managed by cloud control includes Exadata, Exadata Logic, Oracle database, listener, fusion middle ware, Oracle application server web logic, PeopleSoft, E-Business Suite, SOA, and third party products.

Communication between the core components:

Agent -->uploads data àthe OMS via http/4889 or https/4900.

OMSàcommunicates with the Agent via http or https on port 3872

OMS àcommunicate with OMR via JDBC on port 1521. OMS and OMR should have good bandwidth and low-latency connection

OMAs or Agents: Java applications are installed on their own ORACLE_HOME. They run on hosts and gather metrics data about the monitored hosts. They communicate via HTTP or secured HTTPS traffic with OMS to upload metric data. As Administrator, you can use Plug-in or initiate a discovery or configure Auto Discovery to promote the discovered target and managed targets.

Plug-in: Have both the agent-side and OMS-side components. It’s installed by default and gathers configuration, monitor availablity and performance

OMS: Is J2EE application deployed on Oracle Web Logic Server. You can push the agent to any host that can access across the network using a secure SSH connection.

OMR: Is in Oracle database. It includes about 4000 schemas objects belonging to the sysman user. It can be installed in an existing database or in RAC database for HA requirements.

Auto Discovery of Targets: Agent-based (needs host name(s) and Agent-less (IP scan over the network

dig, nslookup to determine scan name is mapped to the correct IP addresses

With 11gR2 Grid Infrastructure we need 3 SCAN IPs on the same subnet with the Virtual IPs resolving the same name with DNS round-robin resolution. The SCAN name and IP addresses are not listed in the /etc/hosts file and not ping-able unit the Grid Infrastructural is configured.

How to determine that our SCAN name is mapped to the right IP addresses? 2 methods

Method 1:

srvctl config scan

SCAN name: lapqa-scan, Network: 1/10.112.14.0/255.255.255.0/bond0
SCAN VIP name: scan1, IP: /lapqa-scan/10.112.14.101
SCAN VIP name: scan2, IP: /lapqa-scan/10.112.14.102
SCAN VIP name: scan3, IP: /lapqa-scan/10.112.14.103

nslookup lapqa-scan
Server: 10.210.121.15
Address: 10.210.121.15#53

Name: lapqa-scan.ctt.com
Address: 10.112.14.103
Name: lapqa-scan.ctt.com
Address: 10.112.14.101
Name: lapqa-scan.ctt.com
Address: 10.112.14.102

Method 2:

crsctl query dns –servers

CRS-10018: the following configuration was found on the system:
CRS-10019: There are 1 domains in search order. They are:
ctt.com
CRS-10022: There are 2 name servers. They are:
10.210.121.15
10.101.11.109
CRS-10020: number of retry attempts for name lookup is: 4
CRS-10021: timeout for each name lookup is: 5

Alternatively,

cat /etc/resolv.conf
domain ctt.com
nameserver 10.210.121.15
nameserver 10.101.11.109

nslookup 10.210.121.15
Server: 10.210.121.15
Address: 10.210.121.15#53

79.129.250.10.in-addr.arpa name = xy1.cttxyx.local.

+ASM1 - oracle: dig @xy1.cttxyx.local lapqa-scan.ctt.com

; <<>> DiG 9.3.6-P1-RedHat-9.3.6-16.P1.el5 <<>> @xy1.cttxyx.local lapqa-scan.ctt.com
; (1 server found)
;; global options: printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 50505
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 2, ADDITIONAL: 2

;; QUESTION SECTION:
;lapqa-scan.ctt.com. IN A

;; ANSWER SECTION:
lapqa-scan.ctt.com. 30 IN A 10.112.14.102
lapqa-scan.ctt.com. 3600 IN A 10.112.14.103
lapqa-scan.ctt.com. 30 IN A 10.112.14.101

;; AUTHORITY SECTION:
ctt.com. 3600 IN NS xy1.cttxyx.local.
ctt.com. 3600 IN NS xy2.cttxyx.local.

;; ADDITIONAL SECTION:
xy1.cttxyx.local. 86400 IN A 10.210.121.15
xy2.cttxyx.local. 86400 IN A 10.101.11.109

;; Query time: 20 msec
;; SERVER: 10.210.121.15#53(10.210.121.15)
;; WHEN: Tue Mar 5 11:46:24 2013
;; MSG SIZE rcvd: 166

shmmax, shmall, swaping/paging related parameters

In general, if a current parameter settings in /etc/sysctl.conf is higher than the value in the below table, you just leave it alone. However, the parameters like SHMMAX should be adjust to ½ or more physical memory on the server and the SHMALL is based on physical memory size / page size.

Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename

# Useful for debugging multi-threaded applications

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes

kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 48719476736 (For example: the DB server has 90GB)

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 23592960 (For example: sum of all the SGAs on the system=90GBs, devided by the pagesize 4Kb)

SQL> select 90*1024*1024*1024/4096 from dual;

90*1024*1024*1024/4096

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

23592960

To tune swaping / paging of the server, you need to work with Sys Admin to adjust these parameters appropriately

#Reduce swapping:

vm.swappiness = 10

#Maximum percentage of active memory that can have dirty pages:

vm.dirty_background_ratio=3

#Maximum percentage of total memory that can have dirty pages:

vm.dirty_ratio=15

#How long data can be in page cache before being expired:

vm.dirty_expire_centisecs=500

#How often pdflush is activated to clean dirty pages:

vm.dirty_writeback_centisecs=100