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.

Oracle PGA Memory Management

Theory

By default, Oracle Database automatically manages the total amount of memory dedicated to the instance PGA. 

You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET.

This sets a logical ceiling to Oracle for all database server processes and background processes , but this ceiling can be exceeded.

What if the value is not set

If PGA_AGGREGATE_LIMIT is not set, then Oracle Database determines an appropriate default limit.

If PGA_AGGREGATE_TARGET is not set, the database chooses a default value for it.

Type of Control:

PGA_AGGREGATE_TARGET (Target): Acts as a target, not a strict limit. Oracle uses this to estimate the ideal memory for SQL operations.

PGA_AGGREGATE_LIMIT (Limit): Sets an absolute, hard maximum on the aggregate PGA memory that can be allocated.

Behavior When Exceeded:

Target: PGA can exceed the target, potentially causing swapping if it grows too high.

Limit: If this limit is hit, Oracle immediately terminates sessions consuming the most "untunable" PGA memory.

Calculate Values:

Target: Usually set manually by the DBA based on system memory.

Limit: Defaults to the greater of either 2GB, 200% of PGA_AGGREGATE_TARGET, or 3MB per process (up to 120% of physical memory minus SGA).

How to Calculate PGA_AGGREGATE_TARGET:

PGA_AGGREGATE_TARGET

is calculated based on available physical memory.

Take 80% of total OS memory(reserving 20% for the OS)

For OLTP systems it is typically set to 20% of the taken memory.      (Total Physical Memory * 0.8) * 0.2

For DSS/Data Warehouse systems it is set to 50% of the taken memory.  (Total Physical Memory * 0.8) * 0.5

PGA_AGGREGATE_LIMIT

SELECT limit_value*3 as 3x_proc_limit_mb 

  FROM V$RESOURCE_LIMIT 

 WHERE resource_name = 'processes';

Take the higher of these 3 values:

A. 2*PGA_AGGREGATE_TARGET,
B. 3x_proc_limit_mb (above)

C. 2Gb

But... do not exceed 90% of physical memory minus total SGA.

To see the total SGA:

SELECT ROUND(SUM(value)/1024/1024) "TOTAL SGA (MB)" FROM V$SGA;

Purpose:

Target: Used for managing performance and tuning automatic memory usage in work areas (sorts, hashes).

Limit: Used to prevent overall PGA consumption from taking down the server by exhausting physical memory.

Monitor:

See Sizing estimated performance:
SELECT * FROM V$PGA_TARGET_ADVICE 

 ORDER BY pga_target_for_estimate;

 

See Current Usage:

SELECT ROUND(value/1024/1024) AS curr_pga_target_mb 

  FROM V$PGASTAT 

 WHERE name = 'aggregate PGA target parameter';

Maximum instance PGA allocated in megabytes since the database was started:

SELECT ROUND(value/1048576) as value_mb 

  FROM V$PGASTAT 

 WHERE name='maximum PGA allocated';

If AMM is used

If AMM (Automatic Memory Management) is used ( == MEMORY_TARGET set),  only the memory_target is set, and Oracle manages the PGA, and this parameter acts as a minimum.

With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA.

By setting both of these to zero, there are no minimums, and the SGA and instance PGA can grow as needed.

The sizing of SQL work areas remains automatic.

In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management. 

For AMM use:

SELECT * FROM V$MEMORY_TARGET_ADVICE ORDER BY memory_size;

If you are hitting ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 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 know 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;

DBMS_STATS Starhome Stuff

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_IMSI_PREFIXES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2

   );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_COUNTRIES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );
END;
/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_NETWORKS_T', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_MERGE_NETWORKS', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_MERGE_NW_MAPPING', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>'XXX', 

   tabname=>'GSM_NETWORKS_SEG_T', 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );

END;

/

BEGIN

  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_LANGUAGES', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_SERVICE_MERGED_NETWORKS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GSM_NETWORKS_EXCEPTIONS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );
END;
/

BEGIN
  DBMS_STATS.gather_table_stats
  (ownname=>'XXX', 
   tabname=>'GA_ACCOUNTS', 
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   cascade => DBMS_STATS.AUTO_CASCADE,

   degree=>2 );
END;
/

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS( ownname => NULL, degree=>2);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS 

   (ownname=> 'SCHEMA_NAME',

    degree=>2, 

    cascade=>TRUE, 

    no_invalidate=>FALSE);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(

    ownname          => 'YOUR_SCHEMA_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 

    degree           => 2,      

    granularity      => 'AUTO',

    cascade          => TRUE,

    no_invalidate    => TRUE,   

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',

    options          => 'GATHER STALE' -- Only touches changed tables

  );

END;

/

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS(

    ownname          => 'YOUR_SCHEMA_NAME',

    tabname          => 'TABLE_A',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 

    degree           => 2,      

    granularity      => 'AUTO',

    cascade          => TRUE,

    no_invalidate    => TRUE,   

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'

  );

END;

/

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

Gather stats for few schemas

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

CREATE OR REPLACE PROCEDURE schema_stats_a2b IS 

  v_stats varchar2(2000);

BEGIN

  for i in (SELECT username FROM DBA_USERS WHERE username like 'A%' OR username like 'B%' AND account_status='OPEN') LOOP

    v_stats := 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS (ownname=> '''||i.username||''',degree=>2,cascade=>TRUE,no_invalidate=>FALSE, force=>TRUE);  end;';

    EXECUTE IMMEDIATE v_stats;

  END LOOP;

END;

/

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

See execution plan of an SQL

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

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT sql_id, child_number 

  FROM V$SQL 

WHERE sql_id = 'fw4tmpkt79r4r';

SELECT sql_id, child_number 

  FROM V$SQL 

WHERE sql_text LIKE 'MERGE into SGA_W_IPN_SUBSCRIBER o using%';

SET LINESIZE 120

SET PAGESIZE 0

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('fw4tmpkt79r4r',3,'OUTLINE'));

See estimated execution plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fw4tmpkt79r4r', 0, 'ALL'));

See actual execution plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fw4tmpkt79r4r', 0,'ALLSTATS LAST' ));

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

How to Flush a single SQL

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

--DBA_HIST_ACTIVE_SESS_HISTORY 
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY 

 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs') 

   AND SNAP_ID = (SELECT MAX(snap_id) from DBA_HIST_SNAPSHOT);

--V$SESSION

SELECT * FROM V$SESSION 

 WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--V$SQLAREA 

SELECT sql_text, sql_id, last_active_time, executions, 

       disk_reads, buffer_gets, user_io_wait_time

FROM V$SQLAREA 

WHERE sql_id IN ('ccgg2ap67ptbp','0gn14c20ay0qs');

--Flush Shared Pool for SQLs in question

SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='ccgg2ap67ptbp';

SELECT 'EXEC DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','||HASH_VALUE||''',''C'');' FROM  V$SQLAREA where SQL_Id='0gn14c20ay0qs';

SQL> EXEC DBMS_SHARED_POOL.PURGE ('00000000FD2BC510,1283122549','C');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SHARED_POOL.PURGE ('000000009A0252B8,2158953176','C');

PL/SQL procedure successfully completed.

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

See that there are no invalid objects

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

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 

  FROM DBA_OBJECTS WHERE status <> 'VALID';

SELECT 'ALTER '||OBJECT_TYPE||' '||owner||'.'||object_name||' COMPILE;' 

FROM  DBA_OBJECTS WHERE status <> 'VALID';

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

Gather runtime stats during execution

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

EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level = ALL';

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

Hints

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

Problem: Oracle is using V$SQL as a driving table to fetch sql_text for a few sql_id, which is not efficient.
Runtime was reduced from 3 seconds to 0.1 of a second after using the hint to use the small table with few sql_id as the driving table.

Option A.

SELECT  /*+ ORDERED */ *

FROM MISSING_SQLS, VSQL

This forces Oracle to use access tables in the order the sql is written.

Option B.

This is more explicit, it forces Oracle to start with MISSING_SQLS and use nested loop into VSQL

SELECT /*+ LEADING(MISSING_SQLS) USE_NL(VSQL) */

FROM MISSING_SQLS, VSQL

SHUTDOWN ABORT, IMMEDIATE, NORMAL, TRANSACTIONAL

SHUTDOWN ABORT;

Fastest.

Uncommitted transactions are not rolled back.

All users currently connected to the database are implicitly disconnected.

The next database startup will require instance recovery.

SHUTDOWN IMMEDIATE;

Does not wait for current calls to complete or users to disconnect from the database.

Further connects are prohibited. 

The database is closed and dismounted. 

The instance is shutdown and no instance recovery is required on the next database startup.

SHUTDOWN NORMAL;

NORMAL is the default option which waits for users to disconnect from the database.

Further connects are prohibited. 

The database is closed and dismounted. 

The instance is shutdown and no instance recovery is required on the next database startup.

SHUTDOWN TRANSACTIONAL;

Performs a planned shutdown of an instance while allowing active transactions to complete first. 

It prevents clients from losing work without requiring all users to log off.

No client can start a new transaction on this instance.

After completion of all transactions, any client still connected to the instance is disconnected.

Now the instance shuts down just as it would if a SHUTDOWN IMMEDIATE

The next startup of the database will not require any instance recovery procedures.

$ lsnrctl start 

You must be connected to a database as SYSDBA, SYSOPER, SYSBACKUP, or SYSDG.

SYSDBA - database management commands

SYSOPER - Limited to database shutdown, startup, open, recover, create spfile.

SYSBACKUP - similar to SYSDBA. with few limitations.

SYSDG - Data Guards Related

Rebuild database by export import

Rebuild database by export import

SELECT username FROM ALL_USERS ORDER BY username;

COL_SPARX

IND_MOCOQ

IPN_STRESS

SELECT DISTINCT owner 

  FROM DBA_SEGMENTS 

 WHERE tablespace_name IN ('IGT_TABLE','IGT_INDEX');

COL_SPARX

IND_MOCOQ

IPN_STRESS

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_COL_SPARX_20230912.dmp LOGFILE=expdp_COL_SPARX_20230912.log SCHEMAS=COL_SPARX

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IND_MOCOQ_20230912.dmp LOGFILE=expdp_IND_MOCOQ_20230912.log SCHEMAS=IND_MOCOQ

expdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IPN_STRESS_20230912.dmp LOGFILE=expdp_IPN_STRESS_20230912.log SCHEMAS=IPN_STRESS

DROP USER COL_SPARX CASCADE;

DROP USER IPN_STRESS CASCADE;

DROP USER IND_MOCOQ CASCADE;

DROP TABLESPACE IGT_INDEX INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE IGT_TABLE INCLUDING CONTENTS AND DATAFILES;

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

CREATE TABLESPACE IGT_TABLE_TEMP DATAFILE '/oracle_db/db2/db_igt/data_files/igt_table_temp_01.dbf' size 100M AUTOEXTEND ON MAXSIZE 20000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE_TEMP;

DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;

Now, recreate all tablespaces from scratch

CREATE TABLESPACE IGT_TABLE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

CREATE TABLESPACE IGT_INDEX DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

ALTER DATABASE DEFAULT TABLESPACE IGT_TABLE;

DROP TABLESPACE IGT_TABLE_TEMP INCLUDING CONTENTS AND DATAFILES;

Optionally, move location for TEMPORARY tablespace 

CREATE TEMPORARY TABLESPACE TEMP_TBS_02 TEMPFILE '/oracle_db/db2/db_igt/data_files/ora_temporary_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 30000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_TBS_02;

DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES;

Optionally, move location for UNDO Tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle_db/db2/db_igt/data_files/ora_undotbs2_01.dbf' SIZE 100M  AUTOEXTEND ON MAXSIZE 10000M;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

Now, run the impdp

impdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_COL_SPARX_20230912.dmp LOGFILE=impdp_COL_SPARX_20230912.log SCHEMAS=COL_SPARX

impdp system/syspass@orainst DIRECTORY=IG_EXP 

DUMPFILE=expdp_IPN_STRESS_20230912.dmp LOGFILE=impdp_IPN_STRESS_20230912.log SCHEMAS=IPN_STRESS

impdp system/syspass@orainst DIRECTORY=IG_EXP DUMPFILE=expdp_IND_MOCOQ_20230912.dmp LOGFILE=impdp_IND_MOCOQ_20230912.log SCHEMAS=IND_MOCOQ

Last step, run DBMS_STATS.GATHER_SCHEMA_STATS.

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'COL_SPARX', 

  estimate_percent => 25);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'IPN_STRESS', 

  estimate_percent => 25);

END;

/

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'IND_MOCOQ', 

  estimate_percent => 25);

END;

/

MTTR Finding - use fast_start_mttr_target instead of log_checkpoint_timeout

When running AWR, the ADDM recommendation section suggest below findings:

Finding 3: Checkpoints Due to MTTR

Impact is .07 active sessions, 4.91% of total activity.
-------------------------------------------------------
Buffer cache writes due to setting of the obsolete parameters
"fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"
were consuming significant database time.
   Recommendation 1: Database Configuration
   Estimated benefit is .07 active sessions, 4.91% of total activity.
   ------------------------------------------------------------------
   Action
      Oracle's recommended solution is to control MTTR setting using the
      "fast_start_mttr_target" parameter instead of the
      "fast_start_io_target", "log_checkpoint_interval" and
      "log_checkpoint_timeout" parameters.

See related parameters in database:
show parameter fast_start_mttr_target
show parameter fast_start_io_target
show parameter log_checkpoint_interval
show parameter log_checkpoint_timeout
show parameter statistics_level
show parameter disk_asynch_io
show parameter db_writer_processes

  
fast_start_mttr_target               integer     0
fast_start_io_target                 integer     0
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
statistics_level                     string      TYPICAL
disk_asynch_io                       boolean     TRUE
db_writer_processes                  integer     2

SELECT target_mttr, estimated_mttr
  FROM  V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR
----------- --------------
          0              0

Some Theory

The FAST_START_MTTR_TARGET

Initialization parameter is used to specify the number of seconds crash recovery should take. Oracle uses this target time to configure the the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters to reduce crash recovery time to a level as close to the target time as possible. 

The FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters should not be set as they may interfere with the process.   
For Example:

ALTER SYSTEM SET fast_start_mttr_target=180 SCOPE=BOTH

LOG_CHECKPOINT_INTERVAL 
Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. 
This number refers to physical operating system blocks, not database blocks.

LOG_CHECKPOINT_TIMEOUT 
Specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. 
This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. 
Setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.

FAST_START_MTTR_TARGET 
Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.
When specified, FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
The default for fast_start_mttr_target is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Will it work in SE...? - No

Since the observation in the ADDM states that "Buffer cache writes due to setting of the obsolete parameters "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"  were consuming significant database time. ", 
 need to set the MTTR for the database writer (DBWn) processes for the purpose of writing changed data blocks  from the Oracle buffer cache to disk and advancing the thread-checkpoint.

Sounds good, right...?

But oracle forget in recommendations, to tel you, that fast_start_mttr_target is available only in EE.

Attempt to use it in SE, will result in :

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH;

ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00439: feature not enabled: Fast-Start Fault Recovery

Example:
ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH;
ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT=0 SCOPE=BOTH;
 
The RDBMS Standard Edition does not support FAST_START_MTTR_TARGET (one would need a Enterprise Edition for that).

SELECT parameter,value  

  FROM V$OPTION 

 WHERE parameter = 'Fast-Start Fault Recovery';

PARAMETER                      VALUE                          
------------------------------ ----------------
Fast-Start Fault Recovery      FALSE