DBMS_DST Package updating Database Time Zone File

Users normally see below message in alert.log file when upgrading the Database. You can use DBMS_DST package to upgrade the time zone file.

Database is using a timezone file older than version xx

The timezone has 2 file i.e large file and small file and these are located under oracore/zoneinfo under ORACLE HOME directory.

• The large versions are designated as timezlrg_version_number.dat., this file has all the time zones defined in the database

• The small versions are designated as timezone_version_number.dat, this file has all most commonly used time zones

You can run query V$TIMEZONE_FILE to identify time zone file version used by the database.

SQL> select filename, version, from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_26.dat 26

You can get the primary and secondary time zone versions details from DATABASE_PROPERTIES

SQL> column property_name format a40

SQL>column property_value format a30

SQL>select property_name, property_value from database_properties where property_name like 'DST_%' order by property_name;

PROPERTY_NAME PROPERTY_VALUE
---------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

After upgrade you can check upg_summary.log to identify Database time zone version and current release time zone version

Oracle Database Release 19 Post-Upgrade Status Tool 04-30-2021 11:07:0
Database Name: TDB1
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.10.0.0.0 00:09:54
JServer JAVA Virtual Machine VALID 19.10.0.0.0 00:01:12
Oracle XDK UPGRADED 19.10.0.0.0 00:00:38
Oracle Database Java Packages UPGRADED 19.10.0.0.0 00:00:05
Oracle Text UPGRADED 19.10.0.0.0 00:00:23
Oracle Workspace Manager UPGRADED 19.10.0.0.0 00:00:22
Oracle Real Application Clusters OPTION OFF 19.10.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.10.0.0.0 00:00:51
Oracle Multimedia UPGRADED 19.10.0.0.0 00:01:47
Datapatch 00:01:41
Final Actions 00:01:44
Post Upgrade 00:00:16

Total Upgrade Time: 00:17:31

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time: [0d:0h:20m:20s]

You can also get latest time zone version using below query

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
32

To upgrade time zone follow below steps

1. Shutdown the database and startup in upgrade mode

SQL> Shutdown immediate
SQL> Startup upgrade

2. Prepare the time zone version upgrade using BEGIN_PREPARE procedure

SQL> set serveroutput on

SQL> declare

l_tz_version pls_integer;

begin

l_tz_version := dbms_dst.get_latest_timezone_version;

dbms_output.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.BEGIN_PREPARE(l_tz_version);

end;

/

PL/SQL procedure successfully completed.

The below query will show the upgrade version we are attempting to

SQL> column property_name format a40

SQL>column property_value format a30

SQL>select property_name, property_value from database_properties where property_name like 'DST_%' order by property_name;

PROPERTY_NAME PROPERTY_VALUE

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

DST_PRIMARY_TT_VERSION 26

DST_SECONDARY_TT_VERSION 32

DST_UPGRADE_STATE PREPARE

3. You can find the affected tables by running below queries

SQL> exec DBMS_DST.find_affected_tables;
PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;
COUNT(*)
----------
0
SQL> select * from sys.dst$error_table;
no rows selected

4. Use END_PREPARE procedure to end the prepare stage and begin upgrade using BEGIN_UPGRADE procedure

SQL> EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> declare

l_tz_version pls_integer;

begin

l_tz_version := dbms_dst.get_latest_timezone_version;

dbms_output.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.BEGIN_UPGRADE(l_tz_version);

end;

/

l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

5. Open in normal mode and upgrade database time zone file using DBMS_DST.UPGRADE_DATABASE

SQL> shutdown immediate
SQL> startup

sql> set serveroutput on

sql> declare

l_failures pls_integer;

begin

DBMS_DST.UPGRADE_DATABASE(l_failures);

dbms_output.put_line('dbms_dst.upgrade_database : l_failures=' || l_failures);

DBMS_DST.END_UPGRADE(l_failures);

dbms_output.put_line('dbms_dst.end_upgrade : l_failures=' || l_failures);

end;

/

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

Verify the time zone upgrade by running below query

SQL> column property_name format a40

SQL>column property_value format a30

SQL>select property_name, property_value from database_properties where property_name like 'DST_%' order by property_name;

PROPERTY_NAME PROPERTY_VALUE

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

DST_PRIMARY_TT_VERSION 32

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

Thanks & Regards,

https://oracleracexpert.com, Oracle ACE

Oracle Data pump import stuck Processing object type DATABASE_EXPORT/SCHEMA

You might come across issues when importing data using DataPump. I have faced the issue several times while importing Domain Index and other objects. You can encounter DOMAIN INDEX issue  related to CTXSYS schema. Note that data import may complete quickly but import gets stuck when creating DOMAIN INDEXES or INDEX and it may still run even after few days. 

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/CODE_BASE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Pls note that domain or normal index rebuild may take time based upon the index size during the import and also other reasons may effect the operation. Here are few reasons that you need to look …

1. Did you gather Stats before the import – If yes, exclude STATS and manually gather stats after import completed.

2. Make sure all tablespaces have enough space – Sometimes imports get stuck due to not having enough space

3. Make sure you have enough STREAMS_POOL_SIZE - The value should be at least 512M or more

If you are still facing the issue then you need to run below commands to identify the SQL Statement A Data Pump Process Is Executing

a) Find out the the datapump import job running or not using below SQL

SQL> select owner_name, job_name, operation, job_mode, from dba_datapump_jobs where state='EXECUTING' ;

You can run below command to identify the session used by datapump job.

SQL> select owner_name, job_name, session_type from dba_datapump_sessions;

b)  If the job is still running on step 4 then Identify The Current SQL Statement A Data Pump Process Is Executing (refer Oracle support Doc ID 1528301.1) and identify the object

After you have tried all the options if the index creating is still taking time then the last option to EXCLUDE the object taking time and manually create after import operation is successful…

You can get the object DDL command using below query

SQL> select dbms_metadata.get_ddl('INDEX','<Index_Name>','<Schema_Name>') from dual;

or 

SQL> select dbms_metadata.get_ddl('TABLE','<Table_Name>','<Schema_Name') from dual;

You can exclude the index by adding below clause in the import command…

Exclude= INDEX:"LIKE ‘Index_name _that got stuck_%'"

After import is successful you can create the object manually in my case it is index…

To monitor Data Pump jobs query views DBA_DATAPUMP_JOBS AND DBA_DATAPUMP_SESSIONS. You can also query V$SESSION_LONGOPS to see the progress of data pump job.

The below script very useful to identify database role, version, registry status, patch level and Invalid objects. I would highly suggest to run this script for any maintenance activity you perform on a Database.

SET PAGESIZE 2000
SET LINESIZE 500
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A30
COL COMP_ID FORMAT A10

COL COMP_NAME FORMAT A45  
COL OWNER FORMAT A15
COL STATUS FORMAT A10

COL VERSION FORMAT A10  
/* Database Role and Version */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Database patch Level*/
select * from dba_registry_history;
/* INVALID objects in the DB count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;

Hope this helps

Regards,

http://oracleracexpert.com, Oracle ACE

Automatic Temporary Tablespace and undo tablespace Shrink in Oracle21c

Automatic Temporary Tablespace Shrink

Temporary tablespaces are used for storing temporary data and Users will notice high temporary tablespace usage when using sorts, hash joins and query transformations and DBA needs to manually size the temporary tablespace.

The automatic temporary tablespace helps to take care of below operations automatically

• Shrink temporary tablespace to reclaim unused space
• Grow temporary tablespace based upon high temp usage

You can run below query to identify the free space

SQL> SELECT * FROM dba_temp_free_space;

Or

SQL> SELECT TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

Automatic Undo  Tablespace Shrink

Oracle Database creates and manages the information needed to roll back, or undo, changes before they are committed. These undo records are stored in the undo segments in an undo tabs pace.

An undo tablespace is used for undo management, it helps to undo data or rollback any transaction. In 11g Oracle introduced automatic undo management it helps to manage undo segments in a database. The UNDO_MANAGEMENT parameter is set to AUTO or null enables automatic undo management and UNDO_TABLESPACE specifies the name of the undo tablespace.

Use UNDO_RETENTION initialization parameter to specify minimum undo retention period in seconds and you will see better results with a fixed-size undo tablespace when using Automatic tuning of undo retention. The Undo Advisor can help you estimate the capacity and you can access through Oracle Enterprise Manager or using DBMS_ADVISOR or DBMS_UNDO_ADV package.

The undo tablespace can grow large and the easy way to reclaim the space from undo tablespace is to create a new undo tablespace and set the database with new undo tablespace and drop the old undo tablespace.

SQL> CREATE UNDO TABLESPACE UNDO2 DATAFILE '/u01/oradata/ORCL/undo02.dbf' SIZE 5G AUTOEXTEND ON ;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDO2;
SQL> DROP TABLESPACE UNDO1 INCLUDING CONTENTS AND DATAFILES;

Oracle 21c introduces automated way to recover the space, this feature shrinks the undo tablespace by dropping the expired segments and extents, also it performs the data file shrink if possible. Note that data file shrink is based upon allocated extents.

This feature takes care of below operations automatically

• Recovery space from transactions that are not active
• Allow large transactions to run successfully by recovering space from expired undo

Please see the example for using DBMS_ADVISOR for UNDO.

DECLARE
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.EXECUTE_TASK(tname);
END;
/

Where

TARGET_OBJECTS is the undo tablespace of the system
START_SNAPSHOT Starting snapshot in the AWR to perform analysis
END_SNAPSHOT Ending snapshot in the AWR to perform analysis
BEGIN_TIME_SEC The beginning time of the period and now.
END_TIME_SEC The ending time of the period and now

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE