How much memory can be allocated to Oracle on Linux host?

How much memory can be allocated to Oracle on Linux host?

1. Rule of thumb:

33% of RAM is allocated to oracle server, leaving 66% of memory to:

33% OS

33% database related processes, such as session processes, RMAN, expdp, jobs, running on host, and are not part of the memory allocated to oracle

2. Oracle memory can be increased to 50% of host memory

Per oracle technote "Doc ID 567506.1  Maximum SHMMAX values for Linux x86 and x86-64"

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of "1/2 of physical RAM".

The "theoretical limit" for SHMMAX is the amount of physical RAM that you have.  However, to actually attempt to use such a value could potentially lead to a situation where no system memory is available for anything else.  Therefore a more realistic "physical limit" for SHMMAX would probably be "physical RAM - 2Gb".

In an Oracle RDBMS application, this "physical limit" still leaves inadequate system memory for other necessary functions. Therefore, the common "Oracle maximum" for SHMMAX that you will often see is "1/2 of physical RAM". 

Many Oracle customers chose a higher fraction, at their discretion.

3. Additional Notes

- The SHMMAX should be larger than SGA and a maximum of 1/2 of physical RAM.

- The shared memory is implemented via /dev/shm (POSIX) using standard 4 KB memory pages, or kernel hugepages, which uses 2 MB pages.

-  Oracle AMM (memory_target and memory_max_target) requires POSIX shared memory (/dev/shm).  it is allocated out of dev/shm as needed.

- /dev/shm uses standard 4k memory pages and can be swapped to disk. 

   In theory,  for Oracle database with more than 4 GB of SGA, performance can decrease due to the memory required to manage the memory pages.

- Oracle AMM manages SGA and PGA automatically. 

- PGA is not allocated out of SGA. 

- System V IPC parameters do not apply to Posix shared memory.

- AMM uses /dev/shm, which is max. 50 % by default. 

- The SGA under AMM needs to fit into /dev/shm.

- ASM requires AMM

- Shared memory can be System V or Posix. SHMALL applies to System V only.

- Only a few processes use shared memory, Oracle is one of them.

Remove non ASCII character in bash and sql

Remove non ASCII character in bash

remove_non_ascii.sh
#!/bin/bash
WORK_DIR=$1
FILE=$2
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
input=${WORK_DIR}/${FILE}
output=${WORK_DIR}/${FILE}_${RUN_DATE}
while IFS= read -r line
do
  echo "$line" | tr -cd [:print:] >> ${output}
  echo >> ${output}
done < "$input"
mv ${output} ${input}

Usage:

./remove_non_ascii.sh "/some/path" "some_file.txt"

Find non ASCII character in SQL

SELECT * FROM MY_TABLE 

 WHERE column_name != ASCIISTR(column_name);

For example:

SELECT 'GSM_COUNTRIES' AS table_name, 'country_name' as column_name, country_name as column_value  

  FROM GSM_COUNTRIES my_table   

 WHERE country_name != ASCIISTR(country_name);

To generate the code:

SELECT 'SELECT '||''''||USER_TAB_COLUMNS.table_name||''' AS table_name, '||''''||USER_TAB_COLUMNS.column_name||''' as column_name, '||USER_TAB_COLUMNS.column_name||' as column_value  FROM '||USER_TAB_COLUMNS.table_name||' my_table   WHERE '||USER_TAB_COLUMNS.column_name||' != ASCIISTR('||USER_TAB_COLUMNS.column_name||');'

  FROM USER_TAB_COLUMNS 

 WHERE table_name = 'GSM_COUNTRIES'  AND data_type = 'VARCHAR2'

 ORDER BY column_name;

Golden Gate 101

DBLOGIN

DBLOGIN USER XXX, PASSWORD YYY

Remove + Install DataPump

STOP EXTRACT DPM_I_01
DELETE EXTRACT DPM_I_01
ADD EXTRACT DPM_I_01 EXTTRAILSOURCE /software/ogg/191/dirdat/01/out/ei
ADD RMTTRAIL /software/ogg/191/dirdat/01/in/ei EXTRACT DPM_I_01
START EXTRACT DPM_I_01
INFO EXTRACT DPM_I_01

Resync  DataPump after Install

STOP DPM_I_01
ALTER EXTRACT DPM_I_01, EXTSEQNO 9313
START EXTRACT DPM_I_01
INFO EXTRACT DPM_I_01
SEND DPM_I_01  STATS

Resync  Replicat
STOP REPLICAT REP_I_01
ALTER REPLICAT REP_I_01, EXTSEQNO 4616
START REPLICAT REP_I_01
INFO REP_I_01
SEND REP_I_01 STATS

Oracle COMMIT options from 11gR2

COMMIT_LOGGING and COMMIT_WAIT

Since Oracle 11gR2 new options to COMMIT were added:

===================

COMMIT_LOGGING

===================
COMMIT_LOGGING specifies whether the log writer writes redo data in batches or at commit.

Options are IMMEDIATE and BATCH

IMMEDIATE is the default behavior.

COMMIT_LOGGING=IMMEDIATE;
The IMMEDIATE parameter causes the log writer process (LGWR) to write the transaction's redo information to the log. 

This operation option forces a disk I/O and Oracle does the log writer operation for each commit. 

COMMIT_LOGGING=BATCH;
The BATCH parameter causes the redo to be buffered to the redo log, along with other concurrently executing transactions. 
When sufficient redo information is collected, a disk write of the redo log is initiated. 
This behavior is called "group commit", as redo for multiple transactions is written to the log in a single I/O operation.

This causes less traffic, but indices risk because an instance crash may cause loss of data that is being batched inside the log buffer.

===================
COMMIT_WAIT

===================
Options are WAIT and NOWAIT
WAIT is the default behavior.

This clause specify when control returns to the user.


COMMIT_WAIT=WAIT
The WAIT parameter ensures that the commit will return only after the corresponding redo is persistent in the online redo log. 
Whether in BATCH or IMMEDIATE mode, when the client receives a successful return from this COMMIT statement, the transaction has been committed to durable media. 

COMMIT_WAIT=NOWAIT
The NOWAIT parameter causes the commit to return to the client whether or not the write to the redo log has completed. 
This behavior can increase transaction throughput.
With NOWAIT, a crash occurring after the commit message is received, but before the redo log record(s) are written, 
Can falsely indicate to a transaction that its changes are persistent.

===================

Syntax

===================

ALTER SESSION | SYSTEM SET COMMIT_WAIT=NOWAIT;
ALTER SESSION | SYSTEM SET COMMIT_WAIT=WAIT;

ALTER SESSION | SYSTEM SET COMMIT_LOGGING=BATCH;
ALTER SESSION | SYSTEM SET COMMIT_LOGGING=IMMEDIETE;

===================

Example

===================

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

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

commit_wait             string

SQL> ALTER SYSTEM SET COMMIT_WAIT=NOWAIT;

System altered.

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

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

commit_wait             string                NOWAIT

================

Golden Gate

================

In Golden Gate Replicat:

REPLICAT SOME_REPLICAT

USERID xxxxxx, PASSWORD xxxxxx

INSERTALLRECORDS

DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

SQLEXEC "alter session set commit_wait = 'NOWAIT'";

MAP....

TRUNCATE PARTITION by example

Example to Truncate a partition with indexes.
When doing Truncate to a partition, must do it with UPDATE GLOBAL INDEXES;

Code example:

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example without UPDATE GLOBAL INDEXES:

ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 
INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     UNUSABLE YES
SFI_CUSTOMER_PROFILE_PK            UNUSABLE YES
 
ALTER INDEX SCP_MSISDN_IDX REBUILD ONLINE;
ALTER INDEX SFI_CUSTOMER_PROFILE_PK REBUILD ONLINE;

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Code example with UPDATE GLOBAL INDEXES:
ALTER TABLE SFI_CUSTOMER_PROFILE TRUNCATE PARTITION AFFILIATE_82 UPDATE GLOBAL INDEXES;
 
Table truncated
 

SQL> SELECT index_name, status, global_stats 

FROM USER_INDEXES 

WHERE table_name = 'SFI_CUSTOMER_PROFILE';

 

INDEX_NAME                         STATUS   GLOBAL_STATS
---------------------------------- -------- ------------
SCP_MSISDN_IDX                     VALID    YES
SFI_CUSTOMER_PROFILE_PK            VALID    YES

Oracle Support Levels and Oracle Supported Versions

Oracle has Three Support Levels

Oracle Extended Support
Provides comprehensive maintenance and software upgrades for five years from the general availability (GA) date.

Oracle Premier Support

Oracle Premier Support delivers security features, comprehensive maintenance, features, and functionality.

Oracle Sustaining Support
There are no more new patches or upgrades released. 
You will still receive technical support through the Oracle support site, so you can still log service requests. 
However, should you run into an (unknown) problem, Oracle will no longer provide a new fix / patch and will probably kindly request you to upgrade.

Oracle Support Matrix pdf

Oracle Supported Versions

Oracle Supported Versions are listed in Oracle document :
Oracle Database (RDBMS) Releases Support Status Summary (Doc ID 161818.1)

Convert LONG to VARCHAR2 in Oracle

Example of using Function co convert LONG to VARCHAR2

CREATE OR REPLACE FUNCTION LONG_2_VARCHAR 

   (p_table_name IN VARCHAR2,
    p_partition_name IN  VARCHAR2) RETURN VARCHAR2 IS
 v_ret_str VARCHAR2(32767);
BEGIN
  SELECT high_value INTO v_ret_str 

    FROM USER_TAB_PARTITIONS 

   WHERE table_name = p_table_name 

     AND partition_name = p_partition_name;

  RETURN REPLACE (v_ret_str,'''','');

EXCEPTION 
  WHEN OTHERS THEN
    RETURN 'Unexpected Error! Unable to Convert Long to Varchar2 !';
END;

/

Usage example:

SELECT table_name, 

       partition_name, 

       LONG_2_VARCHAR(table_name, partition_name) high_value

  FROM USER_TAB_PARTITIONS 

WHERE table_name = 'MY_TABLE';