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