PL/SQL example for Procedure with PRAGMA AUTONOMOUS_TRANSACTION

===========================================
General
===========================================
Example of  PRAGMA AUTONOMOUS_TRANSACTION Procedure.

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

Procedure to write to a log table.

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

PROCEDURE WRITE_LOG(p_module_name IN VARCHAR2, p_msg_text IN VARCHAR2) IS

   PRAGMA AUTONOMOUS_TRANSACTION;

-- Purpose : Insert new record to the trace table

BEGIN

INSERT INTO MY_TRACE(MODULE, msg_date, msg_text)

VALUES(p_module_name , SYSDATE, p_msg_text);

COMMIT;

END WRITE_LOG;

Archive log files grow very fast

What can cause Archive logs to grow fast?

=====================
General
=====================
Log Writer Process (LGWR)
Redo Log Contents
Archiver Processes (ARCn)
What can cause Archive logs to grow fast?

=====================
Log Writer Process (LGWR)
=====================
The log writer process (LGWR) is responsible for redo log buffer management—writing the redo log buffer to a redo log file on disk. 
LGWR writes all redo entries that have been copied into the buffer since the last time it wrote.
The redo log buffer is a circular buffer. When LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. 
LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries.
When a user issues a COMMIT statement, LGWR puts a commit record in the redo log buffer and writes it to disk immediately. 
The corresponding changes to data blocks are deferred until it is more efficient to write them

What about non commited transactions?
Per Oracle documentation, The entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than do transaction entries handled individually. 

=====================
Redo Log Contents
=====================
Redo log files are filled with redo records. 
A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments.
Redo records can also be written to a redo log file before the corresponding transaction is committed. 
If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.



=====================
Archiver Processes (ARCn)
=====================
The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. 
ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. 

==================================
What can cause Archive logs to grow fast?
==================================
Generally speaking, heavy DML operations.

Consider following scenario:
Database is running heavy DML Transaction, for example delete millions of rows at once.

This TX fails, with:
 ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

Now, the data for all these changes was already written to redo log, and then archived, as Oracle has to free up space in redo log buffer, and does not wait till the end of transaction.
If that process is run again and again, for example from a scheduled job, the archive destination would fill up quite fast...

In this case the archive log files would reach the maximum limit, and would be all of same size.
The MAX size of the archive log would be size of redo log:

SELECT group#, bytes/1024/1024 AS Mb 
FROM V$LOG;

Oracle Shared Server (MTS) vs Dedicated mode

============================
General
============================
The Server can be configured to run at Dedicated or Shared mode,
The Client can be also configured, to run at Dedicated or Shared mode.
So you can have a server configured to run in Shared mode running sessions configured to run in a dedicated mode.

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

How to tell if a server is running in Dedicated or Shared/MTS mode?
============================
Need to check:
 - server configuration.
 - client configuration
 - the actual sessions status.

=========================
Check server configuration
=========================
check parameters:
- dispatcheres
- shared_servers

dispatchers configures dispatcher processes in the shared server architecture.
max_dispatchers is optional, and must be larger than the number of dispatchers.
shared_servers 0, meaning that shared server is not on. 
                                     1 meaning that shared server is on, or that there are dispatchers.

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name LIKE '%dispatcher%';

NAME              VALUE

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

dispatchers      (PROTOCOL=TCP) (SERVICE=igtXDB)

max_dispatchers

SELECT COUNT(*) FROM V$DISPATCHER;

  COUNT(*)

----------

         1

SELECT name, value FROM V$PARAMETER 
WHERE name LIKE '%shared_servers%'

NAME             VALUE
---------------- ----------
shared_servers   1

=========================
Check client configuration

=========================
In tnsnames.ora
conn_str =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orainst)
    )
  )

=========================
Check the actual sessions status
=========================
See in what configuration sessions are running.

SQL> SELECT DISTINCT (server) FROM V$SESSION ;

SERVER
---------
DEDICATED


=========================
Short Oracle Reference

=========================
Per Oracle documentation: Oracle® Database Net Services Reference Guide 10G

What is parameter SERVER?
Use the parameter SERVER to instruct the listener to connect the client to a specific type of service handler.
service handler handles client requests: either by DEDICATED server or by SHARED server.

What is a service handler?
Service handler is a process that acts a connection point from the listener to the database server. 
A service handler can be a dispatcher or dedicated server.

What is a dispatcher?
A process that enables many clients to connect to the same server without the need for a dedicated server process for each client. 
A dispatcher handles and directs multiple incoming network session requests to shared server processes. 

What is a shared server

A database server that is configured to allow many user processes to share very few server processes, so the number of users that can be supported is increased. 
With shared server configuration, many user processes connect to a dispatcher. 
The dispatcher directs multiple incoming network session requests to a common queue. 
An idle shared server process from a shared pool of server processes picks up a request from the queue. 
This means that a small pool of server processes can serve a large number of clients. 
Contrast with dedicated server.

What is a dedicated server?

A server process that is dedicated to one client connection. 
Contrast with shared server.

Useful DBA SQLs. Compile and Recompile Objects.

=======================
Compile Invalid Objects.
=======================

=======================
Packages
=======================

ALTER PACKAGE XXX COMPILE SPECIFICATION;

ALTER PACKAGE XXX COMPILE BODY;

To see compilation errors:
SELECT * FROM USER_ERRORS;


SELECT object_type, 'ALTER '||
       DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||
       ' ' ||owner||'.'||object_name||' COMPILE '||
       decode(object_type,'PACKAGE BODY','BODY','')||';' "ALTER ... COMPILE"
FROM DBA_OBJECTS
WHERE status='INVALID'
  AND object_type NOT IN('JAVA CLASS','JAVA SOURCE')
  AND (object_type != 'SYNONYM' AND OWNER!='PUBLIC')
  AND owner <> 'SYS'

UNION ALL

SELECT object_type, 'ALTER TRIGGER '||' "'||owner||'.'||objest_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('TRIGGER')

UNION ALL

SELECT object_type, 'ALTER '||object_type||' "'||owner||'.'||object_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('JAVA CLASS','JAVA SOURCE')

UNION ALL

SELECT object_type, 'ALTER ' || OWNER || ' ' || OBJECT_TYPE ||' '||OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
  AND OBJECT_TYPE='SYNONYM'
  AND owner = 'PUBLIC'
--order by object_type, name
ORDER BY 1,2;



=======================
Synonyms
=======================

ALTER SYNONYM MY_SYNONYM COMPILE;

Oracle VPD - Virtual Personal Database by Example

==============================
General
==============================
This is a short example of implementation of Oracle VPD - Virtual Personal Database.

==============================
VPD Overview
==============================
From Oracle docs:
Virtual private database (VPD) enables to enforce security, to a fine level of granularity, directly on tables, views, or synonyms. 
Because security policies are attached directly to tables, views, or synonyms and automatically applied whenever a user accesses data, there is no way to bypass security.
When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. 
The modification creates a WHERE predicate returned by a function implementing the security policy. 
The statement is modified dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. 
VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

The VPD is implemented via DBMS_RLS package.

==============================
Implementation Example
==============================
Per each table, there is a dedicated function, which returns a predicate.

The implementation of the function is below.

In this function, there is a reference to privileged users, that are listed inside an applicative table DB_PRIV_USERS.
For these privileged users the returned predicate is NULL.

For other users, the predicate is being built from another applicative table, DB_PRODUCT_USERS.

DBA_POLICIES
SELECT object_name, 
       policy_group, 
       policy_name, 
       pf_owner, package, 
       function, 
       sel, ins, upd, enable
FROM DBA_POLICIES
WHERE rownum < 11;

OBJECT_NAME         POLICY_GROUP POLICY_NAME PF_OWNER PACKAGE FUNCTION             SEL INS UPD ENABLE 
------------------- ------------ ----------- -------- ------- -------------------- --- --- --- ------ 
DAILY_PROD_AGG       SYS_DEFAULT  PROD_POL01  DWNG             BUILD_PRD1_PREDICATE YES YES YES YES   
REP_DAILY_VISITORS   SYS_DEFAULT  PROD_POL01  DWNG             BUILD_PRD1_PREDICATE YES YES YES YES   
DAILY_PROD_TRAFFIC   SYS_DEFAULT  PROD_POL02  DWNG             BUILD_PRD2_PREDICATE YES YES YES YES   
REP_DAILY_PROD       SYS_DEFAULT  PROD_POL02  DWNG             BUILD_PRD2_PREDICATE YES YES YES YES   
DAILY_PROD_ABANDONED SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DAILY_USER_AGG       SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DAILY_CONCAT_PROD    SYS_DEFAULT  PROD_POL03  DWNG             BUILD_PRD3_PREDICATE YES YES YES YES   
DWH_PROD             SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   
DAILY_PROD_ORIG      SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   
PROD_COUNTER_DATA    SYS_DEFAULT  PROD_POL04  DWNG             BUILD_PRD4_PREDICATE YES YES YES YES   

FUNCTION BUILD_PRD1_PREDICATE
CREATE OR REPLACE FUNCTION BUILD_PRD1_PREDICATE (obj_schema VARCHAR2, 
                                                 obj_name   VARCHAR2)
RETURN VARCHAR2
IS
   v_predicate   VARCHAR2(2000);
   v_userName    VARCHAR2(30);
   v_cnt         PLS_INTEGER;
   v_productList VARCHAR2(200);

   CURSOR prd_cur IS
          SELECT PRODUCT_ID 
    FROM DB_PRODUCT_USERS
   WHERE USERNAME = SYS_CONTEXT('USERENV', 'SESSION_USER');
BEGIN

  SELECT COUNT(*)
    INTO v_cnt
    FROM DB_PRIV_USERS
   WHERE USERNAME = SYS_CONTEXT('USERENV', 'SESSION_USER');

  IF (v_cnt > 0) THEN
    v_predicate := NULL;
  ELSE
    v_cnt := 0;
    FOR i IN prd_cur LOOP
      v_cnt := v_cnt + 1;
      IF (v_cnt > 1) THEN
v_productList := v_productList ||',';
      END IF;
      v_productList := v_productList || ''''||i.product_id||'''';
    END LOOP;

    IF (v_cnt = 0) THEN
      v_predicate := '1=2';

    ELSIF (v_cnt = 1) THEN
      v_predicate := 'PRODUCT_ID = ' || v_productList;

    ELSE
      v_predicate := 'PRODUCT_ID IN (' || v_productList || ')';

    END IF;

  END IF;
  
  RETURN v_predicate;

END BUILD_PRD1_PREDICATE ;


DB_PRODUCT_USERS 
SELECT * FROM DB_PRODUCT_USERS WHERE ROWNUM < 11;

USERNAME          PRODUCT_ID
----------------- ------------
USER_A            008
USER_B            057
USER_C            030
USER_D            001
USER_E            557
USER_F            053
USER_G            062
USER_H            562
USER_K            011
USER_L            064

DB_PRIV_USERS
SELECT * FROM DB_PRIV_USERS;

USERNAME          DESCRIPTION
----------------- -------------------------
SYSTEM            DBA User
MANAGER           DBA User
ADMIN_PORTAL      User for internal portal

Mount, Nomount, Restricted and Open

=============================
Mount, Nomount, Restricted and Open 
=============================

NOMOUNT
Oracle reads initialization files in this order:
1. spfile.
2. If not found read from pfile.
3. If not found read from init.ora file.

According to the settings database instance is started:
- Memory structures (SGA) allocated.
- Background processes started.
- Alert<SID>.log and trace file are opened for writing.
- Control is not loaded.

MOUNT
Control Files are loaded.
Datafiles and Online Redo Log files are not opened yet.

OPEN
Datafiles are opened.
Online Redo Log file are opened.


Privilege needed for Oracle Startup:
To startup Oracle instance one need to login as sysdba

How to switch between MOUNT/NOMOUNT modes:
Switch to NOMOUNT mode:
STARTUP NOMOUNT;

Switch to MOUNT mode:
STARTUP MOUNT;
ALTER DATABASE MOUNT; (from NOMOUNT mode)

Switch to OPEN mode:
STARTUP;
ALTER DATABASE OPEN; (from MOUNT mode)

Restricted Mode
In Restricted mode - the database is open, but only privileged user can connect.

Switch to RESTRICT mode:
STARTUP RESTRICT;
or
ALTER SYSTEM ENABLE RESTRICTED SESSION;

To go out from the restricted mode:
ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle memory structures by example

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

Oracle AAM

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

Was introduced in Oracle 11.
Oracle Reference for ASMM

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. 
To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). 
The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). 
The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high.
Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low

In short:When using AMM (Automatic Memory Management) feature of Oracle, one is setting only the MEMORY_TARGET parameter, and letting Oracle handle the memory allocation to the various internal components (SPA and PGA).

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

Oracle ASMM

====================
Was introduced in Oracle 10.

When using ASMM (Automatic Shared Memory Management) feature of Oracle, one is setting only the SGA_TARGET parameter, and letting Oracle handle the internal SGA memory allocation to the various parts of SGA.

=====================
Example working with AMM
=====================

Setting MEMORY_TARGET

ALTER SYSTEM SET MEMORY_TARGET = 2000M SCOPE = SPFILE;

Then need to restart the instance.

Setting MEMORY_MAX_TARGET 

ALTER SYSTEM SET MEMORY_MAX_TARGET = 2000M SCOPE = SPFILE;

- If it value is not specified, it defaults to MEMORY_TARGET.

- If it value is larger than MEMORY_TARGET - initial allocation is to size defined by MEMORY_TARGET, and can grow to a value set by MEMORY_MAX_TARGET.

- If it value is smaller than MEMORY_TARGET - you are in (not so) deep shit.

The Oracle Instance would not start, after changing the parameter and reboot.

  See below the steps to do in this case.

======================
Example working with AAMM

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

Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile). 
And set DB_CACHE_SIZE and SHARED_POOL_SIZE to zero, so Oracle would auto allocate memory to these SGA internal memory areas.
If any value is set to any auto tuned pools after switching to ASMM, that value would be a lower memory threshold for that pool.

SQL> ALTER SYSTEM SET SGA_MAX_SIZE=320M SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET SGA_TARGET=160M SCOPE=BOTH;
System altered.

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=0;
System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=0;
System altered.



Using ASMM

V$SGA_TARGET_ADVICE

V$SGA_TARGET_ADVICE view gives information that one would need when estimating the size for SGA_TARGET parameter.
It lists SGA_TARGET options, and the effect on DB performance, i.e. on physical reads.
For example:

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
-------- --------------- ------------ ------------------- -------------------
120                 0.75          421                   1               26042
160                 1             421                   1                8223
240                 1.5           421                   1                7340
280                 1.75          421                   1                7340
200                 1.25          421                   1                7340
320                 2             421                   1                7340

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

Memory target Tables

====================
V$SGA
V$SGAINFO
V$MEMORY_DYNAMIC_COMPONENTS
V$PGASTAT

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

Memory target SQLs

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

SELECT name, bytes/1024/1024 as MB 

FROM V$SGAINFO 

WHERE name LIKE 'Maximum SGA%' OR name LIKE 'Free SGA%';

NAME                                 MB

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

Maximum SGA Size                     2038

Free SGA Memory Available            704

SELECT component, current_size/1024/1024 AS SIZE_MB

  FROM V$MEMORY_DYNAMIC_COMPONENTS  

 WHERE component LIKE '%Target%';

COMPONENT                            SIZE_MB

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

SGA Target                           1344

PGA Target                           704

SELECT V$MEMORY_DYNAMIC_COMPONENTS.current_size/1024/1024 AS SIZE_MB 

  FROM V$MEMORY_DYNAMIC_COMPONENTS 

 WHERE V$MEMORY_DYNAMIC_COMPONENTS.component = 'shared pool';

   SIZE_MB

----------

       352

                  

From the above, we can summarize the following:

MEMORY_TARGET: total was 2048 Mb , or 2GB. 

SGA has been allocated 1344MB

PGA has been allocated 704MB.

1344+704=2048.

Out of SGA total of 1344MB, 352MB were allocated to the shared_pool.

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

Additional commands to check Oracle Memory.

===============================================================
SQL> SELECT * FROM V$SGA;
NAME                      VALUE
------------------        ----------
Fixed Size                  1247780
Variable Size             124319196
Database Buffers           41943040
Redo Buffers                 262144

To sum up the SGA size:
SELECT SUM(value)/1024/1024 AS Mb 
  FROM V$SGA;


SELECT component, current_size
  FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT              CURRENT_SIZE
---------------------- ------------
shared pool                92274688
large pool                  8388608
java pool                   8388608
streams pool               12582912
DEFAULT buffer cache       33554432
KEEP buffer cache           4194304
RECYCLE buffer cache        4194304


sho sga
Total System Global Area 1586708480 bytes
Fixed Size 2228744 bytes
Variable Size 956304888 bytes
Database Buffers 620756992 bytes
Redo Buffers 7417856 bytes

sho parameter memory
NAME              TYPE        VALUE
----------------- ----------- -----
memory_max_target big integer 1520M
memory_target     big integer 1520M

ipcs -m | grep oracle
0x00000000 1230929923 oracle 640 4096 0
0x00000000 1230962698 oracle 640 4096 0
0xc60e6588 1230995467 oracle 640 4096 0

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

Oracle Instance would not start, after changing MEMORY_TARGET.

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

alter system set memory_target = 1024M scope = spfile;

Connected to an inactive instance.
SQL>startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL>alter system set memory_max_target = 2G scope = spfile;
ORA-01034: ORACLE not available

Steps in this case:

Find spfile and pfile

These should be under $ORACLE_BASE/admin/SID/pfile/

sqlplus / as sysdba

sql> create pfile='/full/path/to/file/myinit.ora' from spfile='/full/path/to/file/spfileSID.ora';

create pfile='/software/oracle/admin/igt/pfile/pfile_20201201_initigt.ora' from spfile='/software/oracle/admin/igt/pfile/spfileigt.ora';
File created.

Manually backup '/full/path/to/file/spfileSID.ora' to '/full/path/to/file/spfileSID.ora_YYYYMMDD'

Manually edit MEMORY_TARGET and/or MEMORY_MAX_TARGET so that MEMORY_MAX_TARGET = MEMORY_TARGET.

memory_max_target = nM
memory_target = mM

For example:

Before
*.memory_max_target=31457280000

*.memory_target=1073741824

After

*.memory_max_target=31457280000

*.memory_target=4294967296

sql> startup pfile='/full/path/to/file/myinit.ora';

sql> create spfile='/full/path/to/file/spfileSID.ora' from pfile='/full/path/to/file/myinit.ora';

From now on, you should be able to startup the database as usual.

sql> shutdown immediate;
sql> startup;

Appendix

Using Automatic Memory Management