crontab to delete old dmp files

crontab

11 11 * * * /software/oracle/oracle/scripts/del_old_dmp_files/del_old_dmp.sh

del_old_dmp.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/del_old_dmp_files

EXP_DIR=/backup/ora_exp

LOG_FILE=${WORK_DIR}/del_old_dmp.log

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`

cd ${WORK_DIR}

echo "=========================" >> ${LOG_FILE}

echo "Start Delete old dmp files at ${RUN_DATE}" >> ${LOG_FILE}

for f in `ls -1 ${EXP_DIR}  | grep export | grep dmp | grep igt`

do

 echo "Deleting File $f" >> ${LOG_FILE}

 echo "rm -f ${EXP_DIR}/$f" >> ${LOG_FILE}

 rm -f ${EXP_DIR}/$f

done

echo "=========================" >> ${LOG_FILE}

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

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

Error in oracle startup

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

SQL> Connected to an idle instance.

SQL> ORA-01078: failure in processing system parameters

ORA-00093: pga_aggregate_limit must be between 16384M and 100000G

SQL> Disconnected

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

What to check

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

COL name for A30
COL VALUE for A30
SELECT name, value 

  FROM V$PARAMETER 

 WHERE name = 'pga_aggregate_limit';

NAME                           VALUE
------------------------------ ------------------------------
pga_aggregate_limit            3145728000

3145728000=3000M

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

Solution

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

SQL> ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;
System altered.

Issue resolved

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

Theory

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

As per Oracle documentation

In Oracle Database 12c, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance. 

When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session.

If you are hitting this error message you have 2 options
Option 1:
Set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.

It behave like pre12c database.

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH;

Option 2:
Set this value to a higher value if you have enough physical memory on your system by using below command the server
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6000M SCOPE=BOTH;

No need restart the database, as this is a dynamic parameter.

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

How to now if pga_aggregate_limit is set ot not?

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

CREATE PFILE='/software/oracle/122/dbs/pfileigt.ora_20231121' FROM SPFILE='/software/oracle/122/dbs/spfileigt.ora';

oracle@qanfv-2-dbs-01:~>% less /software/oracle/122/dbs/pfileigt.ora_20231121 | grep -i pga

igt.__pga_aggregate_target=18790481920

*._pga_max_size=5368709120

*.pga_aggregate_target=15728640000

SQL> SHOW PARAMETER PGA_AGGREGATE

NAME                         VALUE

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

pga_aggregate_limit          56G

pga_aggregate_target         15000M

SQL> SHOW PARAMETER MEMORY

NAME                         VALUE

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

memory_max_target            56G

memory_target                50G

PGA_AGGREGATE_LIMIT Theory

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.

Default value

If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.

If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.

If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.

In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).

Range of values

Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail. 

However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. 

If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.

Gather stats from crontab - once a month

crontab (run at 22:05 on second of each month)

5 22 2 * * bash -l /software/oracle/oracle/scripts/shared_pool_latch/gather_stats.sh

gather_stats.sh

#!/bin/bash

ORA_VER=1120
ORACLE_SID=igt
ORACLE_BASE=/software/oracle
ORA_NLS33=/software/oracle/112/ocommon/nls/admin/data
ORACLE_HOME=/software/oracle/112
WORK_DIR=/software/oracle/oracle/scripts/shared_pool_latch
LOG_FILE=${WORK_DIR}/gather_stats.log
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
cd $WORK_DIR
echo "===============================" >> ${LOG_FILE}
echo "Start Gather Stats at ${RUN_DATE}" >> ${LOG_FILE}
sqlplus / as sysdba @gather_stats.sql
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
echo "Finished Gather Stats at ${RUN_DATE}" >> ${LOG_FILE}
echo "===============================" >> ${LOG_FILE}

gather_stats.sql

spool gather_stats.log append

PROMPT DBMS_STATS.GATHER_SCHEMA_STATS ('SYS')

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

PROMPT DBMS_STATS.GATHER_DICTIONARY_STATS

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

PROMPT DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_AAAAA')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_AAAAA');

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_BBBBB')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_BBBBB');

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_CCCCC')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_CCCCC');

END;

/

EXIT;