Oracle DBA by Example. Code samples, Scripts, Reference.

Thursday, December 5, 2024

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 42 into a target database with TSTZ version 32.

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

Issue

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

During import from oracle 19.22 to oracle 19.10, following error came during import.

Apparently, even if Oracle servers are of same major version, an export file from higher TZ VERSION database cannot be imported into lower TZ_VERSION database.

Once updated, the import was successful.

Import: Release 19.0.0.0.0 - Production on Wed Dec 4 17:03:27 2024
Version 19.10.2.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 42 into a target database with TSTZ version 32.

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

Evidences

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

Check Current Time Zone Version

The V$TIMEZONE_FILE view displays the zone file version being used by the database.

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION

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

SQL> SELECT * FROM V$VERSION;

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.10.2.0.0

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

Solution

==============
Option 1

Step A - apply patch 35220732, to upgrade TZ VERSION to version 42.

Patch 35220732: RDBMS - DSTV42 UPDATE - TZDATA2023C

Step B - Implement steps described in "Datetime Data Types and Time Zone Support", "4.7.2 Upgrading the Time Zone Data Using the utltz_* Scripts" section

Option 2
Upgrade to Oracle 19.27

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

Option 1 Steps

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

Step A - apply patch 35220732, to upgrade to version 42

mkdir patches

mv p35220732_190000_Linux-x86-64.zip patches

unzip p35220732_190000_Linux-x86-64.zip

cd 35220732

SQL> SHUTDOWN IMMEDIATE;

$ORACLE_HOME/OPatch/opatch apply -report

$ORACLE_HOME/OPatch/opatch apply

oracle@myhost:~/patches/35220732>% $ORACLE_HOME/OPatch/opatch apply -report

Oracle Interim Patch Installer version 12.2.0.1.25

Oracle Home : /software/oracle/1910

Central Inventory : /software/oracle/oraInventory

from : /software/oracle/1910/oraInst.loc

OPatch version : 12.2.0.1.25

OUI version : 12.2.0.7.0

Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-12-05_09-33-33AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 35220732

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

You are calling OPatch with -ocmrf option while this OPatch is generic, not beine calling OPatch.

Backing up files...

Applying interim patch '35220732' to OH '/software/oracle/1910'

Users request no RAC file generation. Do not create MP files.

Skip patching component oracle.oracore.rsf, 19.0.0.0.0 and its actions.

The actions are reported here, but are not performed.

ApplySession skipping inventory update.

Patch 35220732 successfully applied.

Log file location: /software/oracle/1910/cfgtoollogs/opatch/opatch2024-12-05_09-33-33AM_1.log

OPatch succeeded.

SQL> STARTUP;

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION

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

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

Even after applying the patch, the v$timezone_file is still not updated.

ls -ltr $ORACLE_HOME/oracore/zoneinfo/

-rw-r--r-- 1 oracle dba 779003 Aug 10 2016 timezlrg_17.dat

-rw-r--r-- 1 oracle dba 800913 Aug 10 2016 timezlrg_16.dat

-rw-r--r-- 1 oracle dba 791476 Aug 10 2016 timezlrg_15.dat

-rw-r--r-- 1 oracle dba 782475 Aug 10 2016 timezlrg_13.dat

-rw-r--r-- 1 oracle dba 785621 Aug 10 2016 timezlrg_12.dat

-rw-r--r-- 1 oracle dba 787272 Aug 10 2016 timezlrg_11.dat

-rw-r--r-- 1 oracle dba 351525 Aug 10 2016 timezone_9.dat

-rw-r--r-- 1 oracle dba 286815 Aug 10 2016 timezone_7.dat

-rw-r--r-- 1 oracle dba 286217 Aug 10 2016 timezone_6.dat

-rw-r--r-- 1 oracle dba 286310 Aug 10 2016 timezone_5.dat

-rw-r--r-- 1 oracle dba 782585 Sep 28 2016 timezlrg_28.dat

-rw-r--r-- 1 oracle dba 341401 Sep 28 2016 timezone_28.dat

-rw-r--r-- 1 oracle dba 788462 Dec 5 2016 timezlrg_29.dat

-rw-r--r-- 1 oracle dba 341401 Dec 5 2016 timezone_29.dat

-rw-r--r-- 1 oracle dba 340884 May 2 2017 timezone_30.dat

-rw-r--r-- 1 oracle dba 785841 May 2 2017 timezlrg_30.dat

-rw-r--r-- 1 oracle dba 340892 Nov 6 2017 timezone_31.dat

-rw-r--r-- 1 oracle dba 786708 Nov 6 2017 timezlrg_31.dat

-rw-r--r-- 1 oracle dba 52931 Jun 14 2018 timezdif.csv

-rw-r--r-- 1 oracle dba 59574 Jun 14 2018 readme.txt

-rw-r--r-- 1 oracle dba 340869 Jun 19 2018 timezone_32.dat

-rw-r--r-- 1 oracle dba 786909 Jun 19 2018 timezlrg_32.dat

-rw-r--r-- 1 oracle dba 408795 Apr 14 2023 timezone_42.dat

-rw-r--r-- 1 oracle dba 944613 Apr 14 2023 timezlrg_42.dat

-rw-r--r-- 1 oracle dba 74717 Apr 14 2023 readme_42.txt

Step B - Implement steps described in Datetime Data Types and Time Zone Support, 4.7.2 Upgrading the Time Zone Data Using the utltz_* Scripts section

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

Session altered.

Amount of TSTZ data using num_rows stats info in DBA_TABLES.

For SYS tables first ...

Note: empty tables are not listed.

Stat date - Owner.TableName.ColumnName - num_rows

04/12/2024 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4

04/12/2024 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4

04/12/2024 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4

20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3

20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3

20/10/2021 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3

20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1

20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1

20/10/2021 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1

04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.CREATION_TIME - 1

04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.DELETION_TIME - 1

04/12/2024 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_S.MODIFICATION_TIME - 1

20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1

20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1

20/10/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1

20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1

20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1

20/10/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1

04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3

04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3

04/12/2024 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3

20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1

20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1

20/10/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1

21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1

21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1

21/10/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1

04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1

04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1

04/12/2024 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1

20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4

20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4

20/10/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4

20/10/2021 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 2

04/12/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1

04/12/2024 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1

04/12/2024 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1

04/12/2024 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1

04/12/2024 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1

04/12/2024 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7

04/12/2024 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7

04/12/2024 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 1

04/12/2024 - SYS.KET$_CLIENT_TASKS.LG_DATE - 1

04/12/2024 - SYS.KET$_CLIENT_TASKS.LT_DATE - 1

04/12/2024 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45

04/12/2024 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45

04/12/2024 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 163860

04/12/2024 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72

20/10/2021 - SYS.RADM_FPTM$.TSWTZ_COL - 1

04/12/2024 - SYS.REG$.NTFN_GROUPING_START_TIME - 4

04/12/2024 - SYS.REG$.REG_TIME - 4

04/12/2024 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 2668

04/12/2024 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11

04/12/2024 - SYS.SCHEDULER$_JOB.END_DATE - 29

04/12/2024 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 29

04/12/2024 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 29

04/12/2024 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 29

04/12/2024 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 29

04/12/2024 - SYS.SCHEDULER$_JOB.START_DATE - 29

02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 1372

02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 1372

02/12/2024 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 1372

21/10/2021 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4

21/10/2021 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4

04/12/2024 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9

04/12/2024 - SYS.SCHEDULER$_WINDOW.END_DATE - 9

04/12/2024 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9

04/12/2024 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9

04/12/2024 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9

04/12/2024 - SYS.SCHEDULER$_WINDOW.START_DATE - 9

03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 30

03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 30

03/12/2024 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 30

04/12/2024 - SYS.STATS_TARGET$.END_TIME - 2839

04/12/2024 - SYS.STATS_TARGET$.START_TIME - 2839

04/12/2024 - SYS.TAB_STATS$.SPARE6 - 1179

04/12/2024 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 3

04/12/2024 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 3

04/12/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 277020

04/12/2024 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 277020

04/12/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 25069

04/12/2024 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 25069

04/12/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 9589

04/12/2024 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 9589

04/12/2024 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 1005

04/12/2024 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1005

04/12/2024 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 1005

04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 38636

04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 38636

04/12/2024 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 38636

04/12/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 16324

04/12/2024 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 16324

04/12/2024 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 2

04/12/2024 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 213

04/12/2024 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 213

27/05/2024 - SYS.XS$PRIN.END_DATE - 15

27/05/2024 - SYS.XS$PRIN.START_DATE - 15

Total numrows of SYS TSTZ columns is : 953487

There are in total 166 SYS TSTZ columns.

For non-SYS tables ...

Note: empty tables are not listed.

Stat date - Owner.Tablename.Columnname - num_rows

20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1

20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1

20/10/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -

20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1

20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1

20/10/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1

20/10/2021 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1

20/10/2021 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1

Total numrows of non-SYS TSTZ columns is : 8

There are in total 17 non-SYS TSTZ columns.

Total Minutes elapsed : 0

Session altered.

SQL>

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.

INFO: NO actual RDBMS DST update will be done by this script.

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: Doing checks for known issues ...

INFO: Database version is 19.0.0.0 .

INFO: Database RDBMS DST version is DSTv32 .

INFO: No known issues detected.

INFO: Now detecting new RDBMS DST version.

A prepare window has been successfully started.

INFO: Newest RDBMS DST version detected is DSTv42 .

INFO: Next step is checking all TSTZ data.

INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.

INFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.

INFO: Note that the utltz_upg_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL>

The utltz_upg_apply.sql script automatically restarts the database multiple times during its execution.

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.

INFO: The database RDBMS DST version will be updated to DSTv42 .

WARNING: This script will restart the database 2 times

WARNING: WITHOUT asking ANY confirmation.

WARNING: Hit control-c NOW if this is not intended.

INFO: Restarting the database in UPGRADE mode to start the DST upgrade.

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 8589930592 bytes

Fixed Size 8917088 bytes

Variable Size 6174015488 bytes

Database Buffers 2399141888 bytes

Redo Buffers 7856128 bytes

Database mounted.

Database opened.

INFO: Starting the RDBMS DST upgrade.

INFO: Upgrading all SYS owned TSTZ data.

INFO: It might take time before any further output is seen ...

An upgrade window has been successfully started.

INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.

INFO: Do NOT start any application yet that uses TSTZ data!

INFO: Next is a list of all upgraded tables:

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv42 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this SQL*Plus session.

INFO: Do not use it for timezone related selects.

Session altered.

SQL>

The TZ_VERSION column in the REGISTRY$DATABASE and v$timezone_file now gets updated with the new time zone version.

SQL> SELECT * FROM REGISTRY$DATABASE;

PLATFORM_ID PLATFORM_NAME EDITION TZ_VERSION

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

13 Linux x86 64-bit SE2 42

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

Tuesday, December 3, 2024

Create perfstat user, with permissions and jobs

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

Create perfstat user and jobs

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

set_statspack.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus /nolog << EOF

connect / as sysdba

define perfstat_password=passwd

define default_tablespace=WORKAREA

define temporary_tablespace=TEMPORARY

@?/rdbms/admin/spcreate.sql

GRANT CREATE JOB TO PERFSTAT;

connect perfstat/&&perfstat_password

ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

SET SERVEROUTPUT ON;

SET ECHO ON;

INSERT INTO STATS\$IDLE_EVENT

SELECT name FROM V\$EVENT_NAME WHERE wait_class='Idle'

MINUS

SELECT event FROM STATS\$IDLE_EVENT;

commit;

DECLARE

v_hourly_job NUMBER;

v_purge_job NUMBER;

BEGIN

DBMS_OUTPUT.put_line('Create Statspack Job');

DBMS_JOB.submit

(job=> v_hourly_job,

what=>'DECLARE snap number; BEGIN snap := STATSPACK.snap (i_snap_level=>7); END;',

next_date=>TRUNC(SYSDATE+1/24,'HH'),

interval=>'TRUNC(SYSDATE+1/24,''HH'')'

);

commit;

DBMS_OUTPUT.put_line('Run Job');

DBMS_JOB.run(v_hourly_job);

DBMS_OUTPUT.put_line('Create purge statspack data older than 14 days');

DBMS_JOB.submit

(job=> v_purge_job,

what=>'STATSPACK.purge(i_purge_before_date=>sysdate-14,i_extended_purge=>true);',

next_date => TRUNC(SYSDATE+1)+3/24,

interval => 'TRUNC(SYSDATE+1)+3/24'

);

commit;

END;

EOF

exit

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

Note 1 : Drop user perfstat

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

DROP USER perfstat CASCADE;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$X$KCBFWAIT;

DROP public synonym STATS$X$KSPPSV;

DROP public synonym STATS$X$KSPPI;

DROP public synonym STATS$X$KSXPPING;

DROP public synonym STATS$V$FILESTATXS;

DROP public synonym STATS$V$TEMPSTATXS;

DROP public synonym STATS$V$SQLXS;

DROP public synonym STATS$V$SQLSTATS_SUMMARY;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$SNAPSHOT;

DROP public synonym STATS$DB_CACHE_ADVICE;

DROP public synonym STATS$FILESTATXS;

DROP public synonym STATS$TEMPSTATXS;

DROP public synonym STATS$LATCH;

DROP public synonym STATS$LATCH_CHILDREN;

DROP public synonym STATS$LATCH_PARENT;

DROP public synonym STATS$LATCH_MISSES_SUMMARY;

DROP public synonym STATS$LIBRARYCACHE;

DROP public synonym STATS$BUFFER_POOL_STATISTICS;

DROP public synonym STATS$ROLLSTAT;

DROP public synonym STATS$ROWCACHE_SUMMARY;

DROP public synonym STATS$SGA;

DROP public synonym STATS$SGASTAT;

DROP public synonym STATS$SYSSTAT;

DROP public synonym STATS$SESSTAT;

DROP public synonym STATS$SYSTEM_EVENT;

DROP public synonym STATS$SESSION_EVENT;

DROP public synonym STATS$WAITSTAT;

DROP public synonym STATS$ENQUEUE_STATISTICS;

DROP public synonym STATS$SQL_SUMMARY;

DROP public synonym STATS$SQLTEXT;

DROP public synonym STATS$SQL_STATISTICS;

DROP public synonym STATS$RESOURCE_LIMIT;

DROP public synonym STATS$DLM_MISC;

DROP public synonym STATS$CR_BLOCK_SERVER;

DROP public synonym STATS$CURRENT_BLOCK_SERVER;

DROP public synonym STATS$INSTANCE_CACHE_TRANSFER;

DROP public synonym STATS$UNDOSTAT;

DROP public synonym STATS$SQL_PLAN_USAGE;

DROP public synonym STATS$SQL_PLAN;

DROP public synonym STATS$SEG_STAT;

DROP public synonym STATS$SEG_STAT_OBJ;

DROP public synonym STATS$PGASTAT;

DROP public synonym STATS$PARAMETER;

DROP public synonym STATS$INSTANCE_RECOVERY;

DROP public synonym STATS$STATSPACK_PARAMETER;

DROP public synonym STATS$SHARED_POOL_ADVICE;

DROP public synonym STATS$SQL_WORKAREA_HISTOGRAM;

DROP public synonym STATS$PGA_TARGET_ADVICE;

DROP public synonym STATS$JAVA_POOL_ADVICE;

DROP public synonym STATS$THREAD;

DROP public synonym STATS$FILE_HISTOGRAM;

DROP public synonym STATS$EVENT_HISTOGRAM;

DROP public synonym STATS$TIME_MODEL_STATNAME;

DROP public synonym STATS$SYS_TIME_MODEL;

DROP public synonym STATS$SESS_TIME_MODEL;

DROP public synonym STATS$STREAMS_CAPTURE;

DROP public synonym STATS$STREAMS_APPLY_SUM;

DROP public synonym STATS$PROPAGATION_SENDER;

DROP public synonym STATS$PROPAGATION_RECEIVER;

DROP public synonym STATS$BUFFERED_QUEUES;

DROP public synonym STATS$BUFFERED_SUBSCRIBERS;

DROP public synonym STATS$RULE_SET;

DROP public synonym STATS$OSSTATNAME;

DROP public synonym STATS$OSSTAT;

DROP public synonym STATS$PROCESS_ROLLUP;

DROP public synonym STATS$PROCESS_MEMORY_ROLLUP;

DROP public synonym STATS$SGA_TARGET_ADVICE;

DROP public synonym STATS$STREAMS_POOL_ADVICE;

DROP public synonym STATS$MUTEX_SLEEP;

DROP public synonym STATS$DYNAMIC_REMASTER_STATS;

DROP public synonym STATS$TEMP_SQLSTATS;

DROP public synonym STATS$IOSTAT_FUNCTION_NAME;

DROP public synonym STATS$IOSTAT_FUNCTION;

DROP public synonym STATS$IOSTAT_FUNCTION_DETAIL;

DROP public synonym STATS$MEMORY_TARGET_ADVICE;

DROP public synonym STATS$MEMORY_DYNAMIC_COMPS;

DROP public synonym STATS$MEMORY_RESIZE_OPS;

DROP public synonym STATS$INTERCONNECT_PINGS;

DROP public synonym STATS$IDLE_EVENT;

DROP public synonym STATSPACK;

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

Note 2 - scripts

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

All perfstat objects are created from these 3 scripts, under $ORACLE_HOME

1. Create a dedicated Tablespace

CREATE TABLESPACE statspack_data 
DATAFILE '/oracle_db/db1/db_igt/statspack_data01.dbf' 
SIZE 500M AUTOEXTEND ON MAXSIZE 6000M;

Also - take the TEMPORARYS tablespace name
SELECT tablespace_name

FROM DBA_TABLESPACES WHERE contents = 'TEMPORARY';

2. $ORACLE_HOME/rdbms/admin/spcusr.sql

SQL*PLUS command file which creates the STATSPACK user, tables and package for the performance diagnostic tool STATSPACK

must be run from INTERNAL connection

sqlplus / as sysdba

@$ORACLE_HOME/rdbms/admin/spcusr.sql

exit;

3. $ORACLE_HOME/rdbms/admin/spctab.sql
SQL*PLUS command file to create tables to hold start and end "snapshot" statistical information

Must be run as STATSPACK user, PERFSTAT

sqlplus perfstat/xxxxx@igt

$ORACLE_HOME/rdbms/admin/spctab.sql

exit;

4. $ORACLE_HOME/rdbms/admin/spcpkg.sql

SQL*PLUS command file to create statistics package

Must be run as the STATSPACK owner, PERFSTAT

sqlplus perfstat/xxxxx@igt

$ORACLE_HOME/rdbms/admin/spcpkg.sql

exit;

5. To fix the error
INVALID PUBLIC SYNONYM STATSPACK;

sqlplus perfstat/xxx@igt

DROP PUBLIC SYNONYM STATSPACK;

CREATE PUBLIC SYNONYM STATSPACK FOR STATSPACK;

NLS Settings in SQL Developer

NLS Settings in SQL Developer can be out of sync with database defaults.

By default, in SQL Developer, the NLS Length is set to byte.

This can lead to unexpected behavior.

In database NLS Length is set to CHAR:

SELECT 'NLS_DATABASE_PARAMETERS' as param_source, parameter, value FROM NLS_DATABASE_PARAMETERS

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_INSTANCE_PARAMETERS' as param_source, parameter, value FROM NLS_INSTANCE_PARAMETERS

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_SESSION_PARAMETERS' as param_source, parameter, value FROM NLS_SESSION_PARAMETERS

WHERE PARAMETER='NLS_LENGTH_SEMANTICS';

PARAM_SOURCE PARAMETER VALUE

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

NLS_DATABASE_PARAMETERS NLS_LENGTH_SEMANTICS CHAR

NLS_INSTANCE_PARAMETERS NLS_LENGTH_SEMANTICS CHAR

NLS_SESSION_PARAMETERS NLS_LENGTH_SEMANTICS CHAR

Consider command

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400);

In SQL DEVELOPER it will be translated to:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 BYTE);

SELECT char_used, char_length, data_length

FROM USER_TAB_COLUMNS

WHERE table_name = 'MY_TABLE'

AND column_name = 'MY_COLUMN'

char_used char_length data_length

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

B 200 200

But, when running same command in sqlplus:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(200);

SELECT char_used, char_length, data_length

FROM USER_TAB_COLUMNS

WHERE table_name = 'MY_TABLE'

AND column_name = 'MY_COLUMN';

char_used char_length data_length

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

C 200 800

To fix this behaviour:

Option 1. Add CHAR to ALTER TABLE statements:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 CHAR);

Option 2. Change in SQL Developer NLS Length to CHAR.

Tools -> Preferences -> Database -> NLS -> Length -> CHAR

Thursday, November 21, 2024

How to test a connection to SMTP server

CREATE OR REPLACE FUNCTION TEST_SMTP_MAIL RETURN TSTRINGS PIPELINED IS

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

-- Usage: SELECT column_value as LINE from table(TEST_SMTP_MAIL);

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

v_server VARCHAR2(100);

v_port INTEGER;

v_smtp UTL_SMTP.connection;

v_reply UTL_SMTP.reply;

BEGIN

v_server := '10.20.30.40';

v_port := 25;

-- attempt to connect to mail server

pipe row( 'connecting to server '||v_server||' on port: '||v_port||'/tcp' );

v_reply := UTL_SMTP.open_connection( host=>v_server, port=>v_port, c=>v_smtp );

pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

-- if a successful connect, gracefully disconnect

if v_reply.code < 400 then

v_reply := UTL_SMTP.quit( smtp );

pipe row( 'Reply Code: '||v_reply.code||'. 'Reply Text: '|| reply.text );

end if;

END TEST_SMTP_MAIL;

Monday, September 30, 2024

Oracle PL/SQL send mail with attached File using UTL_SMTP

CREATE OR REPLACE PROCEDURE send_mail_with_attach_file
(p_from IN VARCHAR2,
p_to IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_directory IN VARCHAR2,
p_file_name IN VARCHAR2
)
AS
v_mail_conn UTL_SMTP.connection;
v_boundary VARCHAR2(50) := '----=abc1234321cba=';
v_step PLS_INTEGER := 57;
-------------------------------------
--Put here real IP!
-------------------------------------
v_smtp_server CONSTANT VARCHAR2(30) := '10.20.30.40';
v_smtp_server_port CONSTANT INTEGER := 25;

--File Reading
c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;
v_amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
v_bfile BFILE;
v_file_length PLS_INTEGER;
v_buf RAW(2100);
v_modulo PLS_INTEGER;
v_pieces PLS_INTEGER;
v_file_pos pls_integer := 1;
v_to_list VARCHAR2(1000);
v_to VARCHAR2(1000);

BEGIN
v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);
UTL_SMTP.helo(v_mail_conn, v_smtp_server);
UTL_SMTP.mail(v_mail_conn, p_from);

v_to_list := p_to;
WHILE (INSTR(v_to_list, ',') > 0) LOOP
--get first from list
v_to := SUBSTR(v_to_list, 1, INSTR(v_to_list,',')-1);
--get remaining of list
v_to_list := SUBSTR(v_to_list, INSTR(v_to_list, ',')+1);
--call rcpt for mail
UTL_SMTP.rcpt(v_mail_conn, v_to);
END LOOP;
--last element does not have trailing ','
UTL_SMTP.rcpt(v_mail_conn, v_to_list);

UTL_SMTP.open_data(v_mail_conn);

UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(v_mail_conn, p_text_msg);
UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

--Read blob
IF p_attach_blob IS NOT NULL THEN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP
UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);
END LOOP;

ELSIF p_directory IS NOT NULL AND p_file_name IS NOT NULL THEN

--Read File
v_bfile := BFILENAME(p_directory, p_file_name);
-- Get the size of the file to be attached
v_file_length := DBMS_LOB.GETLENGTH(v_bfile);
-- Calculate the number of pieces the file will be split up into
v_pieces := TRUNC(v_file_length / v_amt);
-- Calculate the remainder after dividing the file into v_amt chunks
v_modulo := MOD(v_file_length, v_amt);
IF (v_modulo <> 0) THEN
v_pieces := v_pieces + 1;
END IF;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);

--FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP
FOR i IN 1 .. v_pieces LOOP

v_buf := NULL;
DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);
v_file_pos := I * v_amt + 1;
UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_buf))|| UTL_TCP.crlf);
--Exxample
--UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

END LOOP;

DBMS_LOB.FILECLOSE(v_bfile);

END IF;


UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(v_mail_conn);

UTL_SMTP.quit(v_mail_conn);
END send_mail_with_attach_file;
/

Sunday, September 22, 2024

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Issue:
==========

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Solution:

==========

Clean up old tasks

Check Current Status
col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
SELECT task_name,

parameter_name,

parameter_value

FROM DBA_ADVISOR_PARAMETERS

WHERE task_name='AUTO_STATS_ADVISOR_TASK'

AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE UNLIMITED

Set limit for old tasks
BEGIN
DBMS_ADVISOR.SET_TASK_PARAMETER(

task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE',
value => 30);
END;
/

Check Status again

SELECT task_name,

parameter_name,

parameter_value

FROM DBA_ADVISOR_PARAMETERS

WHERE task_name='AUTO_STATS_ADVISOR_TASK'

AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30

Check oldest task date

SELECT MIN(execution_start)

FROM DBA_ADVISOR_EXECUTIONS WHERE task_name='AUTO_STATS_ADVISOR_TASK';

MIN(EXECUTION_STAR
------------------
27-AUG-19

Purge the expired tasks.

This step might take time

BEGIN
PRVT_ADVISOR.delete_expired_tasks;
END;
/

Check status in DBA_ADVISOR_EXECUTIONS

SELECT task_id, task_name, execution_name, execution_start

FROM DBA_ADVISOR_EXECUTIONS

WHERE task_name='AUTO_STATS_ADVISOR_TASK'

ORDER BY execution_start;

Monday, September 16, 2024

scp backup files to backup mng server

General

On oracle server
RMAN script running at 03:00

expdp script running at 02:00

scp scripts running at 05:00 and 05:30

On backup server
Clean old backups script running at 01:00

scripts on oracle server

00 02 * * * /etc/sh/backup/export_all_instances.sh
00 03 * * * /etc/sh/backup/jobs/run_rman_backup_cron.sh igt
00 05 * * * /home/user/scripts/send_to_mng.sh
30 05 * * * /home/user/scripts/send_to_mng_exp.sh

/home/user/scripts/send_to_mng.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

DIR_PREFIX=$RUN_DATE

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/ora_online

LOCAL_DIR=/backup/ora_online/for_backup

KEEP_DAYS=10

is_active_server=`ps -ef | grep ora_pmon_igt | grep -v grep | wc -l`

if [[ $is_active_server -eq 0 ]]; then

 echo "Oracle is not running on this server" 

 exit 0

fi

cd ${LOCAL_DIR}

last_backup_dir=`ls -ltr | grep ${DIR_PREFIX} | tail -1 | awk '{print $9}'`

if [[ -d $last_backup_dir ]]; then

 echo "tar -cvf ${last_backup_dir}.tar ${last_backup_dir}"

 tar -cvf ${last_backup_dir}.tar ${last_backup_dir}

 echo "scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar"

 scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar

 rm -f ${LOCAL_DIR}/${last_backup_dir}.tar

else

 echo "No Archive Directory to Send to mng"

fi

/home/user/scripts/send_to_mng_exp.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`
FILE_PREFIX=export_igt
FILE_SUFFIX=dmp
FILE_SUFFIX_LOG=log
REMOTE_USER=user
REMOTE_SERVER=mng_server-01
REMOTE_PATH=/backup/ipn_backup/exp_backup
LOCAL_DIR=/backup/ora_exp/for_backup
LOCAL_LOG_DIR=/backup/ora_exp/for_backup/old_log

cd ${LOCAL_DIR}
last_backup_file=`ls -ltr | grep ${FILE_PREFIX} | grep ${FILE_SUFFIX} | tail -1 | awk '{print $9}'`

if [[ -f $last_backup_file ]]; then 
 #echo "scp ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} ${LOCAL_DIR}/${last_backup_file}"
 scp ${LOCAL_DIR}/${last_backup_file} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} 
else
 echo "No export dmp file to send to mng"
fi
log_files=`ls -1 ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} 2>/dev/null | wc -l`
if [[ $log_files -gt 0 ]]; then
 mv -f ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} ${LOCAL_LOG_DIR}
fi

KEEP_BACKUPS=1
cd ${LOCAL_DIR}
#echo "expdp dmp files: ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l"
backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`
#echo "backups_number = $backups_number"
while [[ $backups_number -gt $KEEP_BACKUPS ]]; do
 backup_name=`ls -ltr ${FILE_PREFIX}*${FILE_SUFFIX} | head -1 | awk '{print $9}'`
 #echo "rm -f $backup_name"
 rm -f $backup_name
 backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`
done

scripts on mng server

00 01 * * * /home/user/scripts/del_old_backups.sh

/home/user/scripts/del_old_backups.sh

#!/bin/bash

LOCAL_DIR=/backup/ipn_backup/ora_online
RUN_DATE=`date +"%Y%m%d"`

BACKUP_SUFFIX=tar
KEEP_BACKUPS=4

cd ${LOCAL_DIR}
backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`
while [[ $backups_number -gt $KEEP_BACKUPS ]]; do
 backup_name=`ls -ltr | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`
 rm -f $backup_name
 backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`
done

LOCAL_DIR=/backup/ipn_backup/exp_backup
FILE_PREFIX=export_igt
BACKUP_SUFFIX=dmp
KEEP_BACKUPS=2

cd ${LOCAL_DIR}
backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`
while [[ $backups_number -gt $KEEP_BACKUPS ]]; do
 backup_name=`ls -ltr | grep ${FILE_PREFIX} | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`
 rm -f $backup_name
 backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`
done

Pages

Thursday, December 5, 2024

Tuesday, December 3, 2024

Thursday, November 21, 2024

Monday, September 30, 2024

Sunday, September 22, 2024

Monday, September 16, 2024