Creating tablespace with 16K blocksize

Creating tablespace with 16K blocksize

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

How To Create 16K tablespace

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

Check on current status:

SELECT TABLESPACE_NAME, BLOCK_SIZE FROM DBA_TABLESPACES;

TABLESPACE_NAME                BLOCK_SIZE

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

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS                              8192

TEMPORARY                            8192

IGT_TABLE                            8192

IGT_INDEX                            8192

WORKAREA                             8192

IGT_TABLE_BIG                       16384

GG_TBS                               8192

Check on space usage

SELECT TABLESPACE_NAME, 

       ROUND(SUM(BYTES)/1024/1024) used_mb, 

       ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

  FROM DBA_DATA_FILES 

GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                   USED_MB     MAX_MB

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

IGT_TABLE                            9200      24000

IGT_INDEX                            3000      20000

WORKAREA                              900       6000

SYSAUX                               1000       6000

GG_TBS                                100          0

UNDOTBS                              9400      12000

SYSTEM                                700       6000

IGT_TABLE_BIG                         100          0

 SELECT FILE_NAME, 

        ROUND(SUM(BYTES)/1024/1024) used_mb, 

        ROUND(SUM(MAXBYTES)/1024/1024) max_mb 

   FROM DBA_DATA_FILES  

   GROUP BY FILE_NAME;

FILE_NAME                                          USED_MB  MAX_MB

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

/oracle_db/db1/db_igt/ora_igt_table_01.dbf            9200   24000

/oracle_db/db1/db_igt/ora_igt_index_01.dbf            3000   20000

/oracle_db/db1/db_igt/ora_sysaux_01.dbf               1000    6000

/oracle_db/db1/db_igt/ora_workarea_01.dbf              900    6000

/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf         100       0

/oracle_db/db1/db_igt/data/ora_gg_tbs_01.dbf           100       0

/oracle_db/db1/db_igt/ora_system_01.dbf                700    6000

/oracle_db/db1/db_igt/ora_undotbs_01.dbf              9400   12000

ALTER SYSTEM SET db_16k_cache_size = 1G SCOPE=BOTH;

SELECT name, value 
  FROM V$PARAMETER 
 WHERE name = 'db_16k_cache_size';

db_16k_cache_size
-----------------
1073741824

CREATE TABLESPACE IGT_TABLE_BIG LOGGING DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_big_01.dbf' SIZE 100M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO  BLOCKSIZE 16k;

Use the new tablespace:

impdp user/passw@orainst DIRECTORY=IG_EXP_DIR LOGFILE=imp_user.log DUMPFILE=exp_full_20210729.dmp TABLES=BIG_TABLE REMAP_TABLESPACE=IGT_TABLE:IGT_TABLE_BIG

ALTER TABLE USER.SGA_W_IPN_SUBSCRIBER MOVE TABLESPACE IGT_TABLE_BIG;

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

When to use  tablespace with 16k block

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

As rule of Thumb:

1. Use 8K block size for OLTP applications

2. Use 16K Data Warehouse applications. Optionally use 32K

3. For mixed block sizes, it is possible, provided there is enough SGA to create buffer caches for each block size in the database.

In Details, when to use and not to use 16k blocksize

1. Full Table Scans - no difference between 8k and 16k

2. For Index Range scans - no major difference between 8k and 16k

3. When there is row chaining having 16K block size might be applicable. And need to review application design.

4. The minus in using 8k and 16k tablespaces, is the need to manage their buffer cache manually.

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)