Upgrading TimeZone error ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading

when you upgrade your database, there are post-upgrade actions….among them is upgrading the “time zone“, the following errors were thrown when i successfully upgraded database from 12cR1 to 18c:

ERROR at line 1:

ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1390

ORA-06512: at line 1

Solution:

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

 

Oracle import error ORA-56935: existing datapump jobs are using a different version of time zone data file

while performing an import datapump (impdp) to an Oracle 18c database from 12cR2 database dump…..the following errors were thrown:

ORA-39006: internal error

ORA-39065: unexpected master process exception in DISPATCH

ORA-56935: existing datapump jobs are using a different version of time zone data file

ORA-06512: at “SYS.DBMS_DST”, line 1855

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1818

ORA-06512: at “SYS.KUPM$MCP”, line 19693

ORA-06512: at “SYS.KUPM$MCP”, line 12495

ORA-06512: at “SYS.KUPM$MCP”, line 11882

ORA-06512: at “SYS.KUPM$MCP”, line 14507

ORA-06512: at “SYS.KUPM$MCP”, line 9794

SOLUTION:

sqlplus / as sysdba

SQL> SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value

FROM   DATABASE_PROPERTIES

WHERE  PROPERTY_NAME LIKE ‘DST_%’

ORDER  BY PROPERTY_NAME;

 

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

**** Important Remark: make sure DST_UPGRADE_STATE is set to “NONE“….you may need to execute the following multiple times: SQL> exec dbms_dst.unload_secondary;

 

Patching Oracle 18c database in Windows

Download the database patch from Oracle support

Also, download the latest OPatch utility for Windows OS as shown below:

Picture1

Check your environment variables:

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

set ORACLE_HOME=D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home

set PERL5LIB=

Shutdown Oracle database services:

Picture2

Stop Distributed Transaction Coordinator

net stop msdtc

Go to the directory where the patch is downloaded

cd D:\ORACLE_WINDOWS_PATCHING\29589622

D:\OPATCH_DOWNLOAD\OPatch\opatch apply

When you try to apply the patch the following  error will occur if the OPatch utility is NOT placed within the Oracle database home binary

Picture3

Error in Text: The Oracle Home D:\OPATCH is not OUI based home. Please give proper Oracle Home

after placing the OPatch utility under Oracle Home, apply the patch:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch apply

Picture4

To resolve this error I will rollback this patch first:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch rollback -id 28267731

Picture5

And attempting to re-apply the patch will succeed

Picture6

Startup the Oracle services again:

Picture7

Post-Patch activity:

sqlplus / as sysdba

SQL> startup

SQL> alter pluggable database all open;

exit;

cd D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch

datapatch -verbose

sqlplus / as sysdba

SQL> @?/rdbms/admin/utlrp.sql

SQL> select * from dba_registry_sqlpatch;

Picture8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

RMAN recovery catalog registration errors RMAN-03009 ORA-01403

In a new Oracle 12cR2 database, i was attempting to register it with the Recovery Catalog:

RMAN> register database;

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of register command at 06/24/2019 06:56:42

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of full resync command on default channel at 06/24/2019 06:56:42

ORA-01403: no data found

In order to solve this problem I needed to re-create the control file:

SQL> alter database backup controlfile to trace as ‘/orcl/backup/CTL_DEF.txt’;

SQL> exit;

 

// A text file with control file definition is created, I will only use the NORESETLOG section and create a sql based file as shown below (this is template):

vi create_controlfile.sql

CREATE CONTROLFILE REUSE DATABASE “DBT11” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 25

MAXLOGMEMBERS 5

MAXDATAFILES 850

MAXINSTANCES 1

MAXLOGHISTORY 18697

LOGFILE

GROUP 1 (

‘/opt/DBT11/t11LOGA1.log’,

‘/opt/DBT11/t11LOGA2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 2 (

‘/opt/DBT11/t11LOGB1.log’,

‘/opt/DBT11/t11LOGB2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 3 (

‘/opt/DBT11/t11LOGC1.log’,

‘/opt/DBT11/t11LOGC2.log’

) SIZE 256M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

‘/data1/DBT11/SYSTEM.ORA’,

‘/data2/DBT11/SYSAUX.ORA’,

‘/data2/DBT11/UNDO010.ORA’,

‘/data3/DBT11/USERD010.ORA’,

‘/data3/DBT11/AMS_TS.DBF’,

‘/data3/DBT11/SSDPROD_TS.DBF’,

‘/data3/DBT11/USERS02.DBF’

CHARACTER SET WE8MSWIN1252

;

 

SQL> STARTUP NOMOUNT

SQL> @create_controlfile.sql

SQL> alter database open;

 

// re-create the temp tablespace as follows:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data1/DBT11/TEMP01.DBF’

SIZE 6557M REUSE AUTOEXTEND ON ;

 

Now try registering the database again:

rman target /

RMAN> connect catalog catalog01/XXXXXX@catalogdb

RMAN> register database;

 

 

 

 

 

 

 

 

 

 

 

 

Oracle Database Migration for ESRI ArcGIS Geo-Database

Oracle database platform is a popular database to host ESRI ArcGIS Geo-Database, so I am going here to list the best approach to migrate your database for ESRI ArcGIS SDE:

From the source database, use expdp to fully export the Oracle database using SDE account (you need to temporary grant “DBA” role for such action):

expdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR FULL=Y PARALLEL=4 compression=all logfile=FULL_EXP_LOG.log

 

Now, in the target database start importing ONLY SDE schema first (SDE schema holds GIS data metadata and management scripts so its very important that this schema gets imported successfully):

impdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR SCHEMAS=SDE PARALLEL=4 logfile=SDE_IMP.log

After the import, check the log file and make sure that all objects were imported successfully…..you can compare the number of SDE schema objects by executing the following query in both source & target database:

SQL> select count(*) from dba_objects where OWNER=’SDE’;

In my case some objects were not created successfully !

One failed object is indextype ST_SPATIAL_INDEX , to view the definition of the index type from the source database:

SQL> set long 100000

SQL> set head off

SQL> set echo off

SQL> set pagesize 0

SQL> set verify off

SQL> set feedback off

SQL> SELECT dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’,’SDE’) FROM dual;

 

I will execute the definition of the indextype:

 

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR

“SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER

),

“SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER)

,

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER,

VARCHAR2),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

 

“SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

“SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)

USING “SDE”.”ST_DOMAIN_METHODS”

WITH LOCAL RANGE PARTITION;

 

The following error will be thrown:

ORA-29829: implementation type does not exist

After digging….the following two “types” are missing:

ST_DOMAIN_METHODS

ST_DOMAIN_STATS

 

Take their SQL definition from the source database and create them in the destination, and try re-creating the indextype ST_SPATIAL_INDEX..it should be created successfully.

 

Now after ensuring SDE schema is fully mimicking the source database, start importing other schemas (user geodatabase schemas) and test it using ArcGIS Desktop.

Oracle Database New Auto-Upgrade Tool Simulation

Oracle has released a new tool “Auto-Upgrade” to automate the process of upgrading Oracle Database Management System. This tool is much needed now, as the upgrade process is “lengthy” procedure ,and having such automation capability will shorten the period of upgrading any organization’s Large Oracle database infrastructure.

The tool will let you upgrade from 11g (11.2.0.4) as the “source” minimum version to either 12cR2, 18c , 19c releases as “target” version.

 

target versions

In this simulation of Oracle Auto-Upgrade tool, I am simulating the upgrade from 18c (18.6) to 19c (19.3) release.

Pre-requisite checks:

Check Java version under Oracle Home, it should be version 8. Java 8 is available in Oracle database homes for Oracle 12cR2 release and beyond. If your Oracle release is 11g (11.2.0.4) either install java 8 to your Oracle Home or use the “target” release java Oracle Home directory.

To check which java release you have , execute the following command:

$ORACLE_HOME/jdk/bin/java -version

java8

Since my “source” database is 18c , so its expected to have java 8 version available.

Preparations before upgrading the database:

I will install and setup Oracle 19c binaries within the same database server:

Will prepare the profile:

cd

cp .bashrc .bashrc_original

cp .bashrc .bashrc_19c

// edit the 19c profile with new Oracle Home

vi .bashrc_19c

profile

Then overwrite it the main profile:

cp .bashrc_19c .bashrc

source .bashrc

I will unzip the 19c oracle binaries under the new Oracle Home Path:

unzip LINUX.X64_193000_db_home.zip -d /opt/oracle/product/19c/dbhome_2

And then I will perform silent-installation:

./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /opt/oracle/product/19c/dbhome_2/install/response/db_install.rsp

*** run root.sh after successful installation

/opt/oracle/product/19c/dbhome_2/root.sh

Reset your profile to point back to 18c database binaries, and startup the 18c database.

Auto-Upgrade Setup:

*** go to the location where you downloaded autoupgrade.jar file , the file can be downloaded from Oracle Support (Doc ID 2485457.1) , and create the configuration file

cd /vagrant/autoupgrade

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -create_sample_file config

config file

*** create your sample configuration file – the following is a TEMPLATE I have used:

cp sample_config.cfg my_config.txt

vi my_config.txt

#

# sample config file

#

#

# Global configurations

#

# This directory will include the following

#   (1) AutoUpgrade’s global directory

#   (2) Any logs, not directly tied to a job

#   (3) Config files

#   (4) progress.json and status.json

global.autoupg_log_dir=/vagrant/autoupgrade/autoupgrade_log

 #

# Database number 1

#

upg1.dbname=ORCLCDB

upg1.start_time=NOW

upg1.source_home=/opt/oracle/product/18c/dbhome_1

upg1.target_home=/opt/oracle/product/19c/dbhome_2

upg1.sid=ORCLCDB

upg1.log_dir=/vagrant/autoupgrade/autoupgrade_log

upg1.upgrade_node=localhost

upg1.target_version=19

#upg1.run_utlrp=yes

#upg1.timezone_upg=yes

*** you can execute the following help command to check the options available:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -help

help-command

The following image illustrate different auto-upgrade modes:

auto-upgrade-modes

I will start with “analyze” mode:

The analyze mode will basically perform the pre-checks required in for the upgrade to succeed.

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode analyze -noconsole

*** please note that -noconsole option specified in the command, will make AutoUpgrade runs based ONLY on the settings in the configuration file, without requiring console input.

analyze-mode

The command executes successfully and the Job number is 100 !

I will go to the location where the logs are generated to grab the produced HTML file:

cd /vagrant/autoupgrade/autoupgrade_log/ORCLCDB/100/prechecks

Inspecting orclcdb_preupgrade.html file….the report will be as shown below:

htmil_report

In the report you will see different severity levels: INFO , WARNING , ERROR , RECOMMEND

Of course “ERROR” indicates that you need Fix the issue before attempting to upgrade the database.

I will explore now “FIXUPS” mode:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode fixups -noconsole

fixups_mode

I will configure flashback, and guaranteed restore point:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =’/opt/oracle/product/18c/dbhome_1/fra’ SCOPE=BOTH;

SQL> create restore point before_19c_upgrade_emad guarantee flashback database;

I will explore now “UPGRADE” mode:

Shutdown your current database, and switch your environment variables to point to the target binaries (in my case 19c).

Don’t forget to copy your init.ora file from old binaries to new binaries…. also anything under $ORACLE_HOME/network/admin from old binaries to new binaries for illustration:

cp -r /opt/oracle/product/18c/dbhome_1/dbs/*  $ORACLE_HOME/dbs

cp -r /opt/oracle/product/18c/dbhome_1/network/admin/*  $ORACLE_HOME/network/admin/

sqlplus / as sysdba

SQL> startup upgrade

exit;

Then, execute the following command to upgrade:

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config /vagrant/autoupgrade/my_config.txt -mode upgrade -noconsole

upgrade_mode

In my simulation I had a problem with FRA size…so I fixed it and the process completed successfully.

Important Remark: in “upgrade” mode No post-upgrade actions will be performed. The post-upgrade actions are expected to be handled by the DBA. For example timezone upgrade will be implemented manually by the DBA. [check the image of different modes at the beginning of the blog and what stages each mode will cover, “deploy” mode will run all stages]

In case you want to roll-back to previous release (in my case 18c):

shutdown your current database(19c) and listener.

set your environment variables to point to 18c binaries.

sqlplus / as sysdba;

SQL> select current_scn from v$database;

SQL> shutdown immediate;

SQL> startup mount;

SQL> select * from v$restore_point;

SQL> flashback database to restore point before_19c_upgrade_emad;

SQL> alter database open resetlogs;

SQL> select current_scn from v$database;

 

I hope this blog post about Oracle Auto-Upgrade tool will be a good starting point for your to try it out !

Resources/References:

The new AutoUpgrade utility in Oracle 19c

AutoUpgrade Tool (Doc ID 2485457.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle datapatch errors ORA-29913 ORA-25153

After successfully applying the database patch on the binary, the next step is to run the datapatch, however when running it….it failed with below errors:

./datapatch –verbose

SQL Patching tool version 12.1.0.2.0 Production on Wed May  1 10:03:02 2019

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

Connecting to database…OK

Bootstrapping registry and package to current versions…done

Queryable inventory could not determine the current opatch status.

Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

and/or check the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for the complete error.

Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log

/orcl/oradbx26/cfgtoollogs/sqlpatch/sqlpatch_79352_2019_05_01_10_03_02/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Wed May  1 10:03:08 2019

Solution:

After reviewing the log file sqlpatch_invocation.log:

verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

so I have executed the following query:

SQL> select * from OPATCH_XML_INV ;

ERROR:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-25153: Temporary Tablespace is Empty

So this is an indication of temporary tabelspace issue, so I have first created a “new” temporary tablespace:

SQL> CREATE TEMPORARY TABLESPACE TEMP_DATA_02 TEMPFILE

  ‘/oradata1/oradbx26/TEMPTD01.dbf’ SIZE 120M AUTOEXTEND ON

TABLESPACE GROUP ”

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Then, I have made the new temporary tablespace the default one:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_DATA_02;

And finally dropped the old temporary tablespace:

SQL> DROP TABLESPACE TEMP_DATA_01 INCLUDING CONTENTS AND DATAFILES;

 

I hope this helps someone !

 

Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

 

 

 

 

 

 

ORA-00600 & ORA-48254 when upgrading to Oracle 12cR2

while upgrading a database from Oracle 12cR1 to 12cR2….the following errors were thrown in the alert log file:

Errors in file /oraclex/orcl/diag/rdbms/orcl/orcl/trace/orcl_m000_125839.trc (incident=708075):

ORA-00600: internal error code, arguments: [Migrate Relation Failure], [], [], [], [], [], [], [], [], [], [], []

ORA-48254: Migration Error [dbgrmmdmr_max_length_change] [PDB_SPACE_MGMT] [2] [66] [32] [9]

Incident details in: /oraclex/orcl/diag/rdbms/orcl/orcl/incident/incdir_708075/orcl_m000_125839_i708075.trc

Use ADRCI or Support Workbench to package the incident.

 

This is due to old ADR setup, so you need to reset ADR location

SQL> show parameter diagnostic_dest
SQL> ALTER SYSTEM SET diagnostic_dest=/orcl/ora23/diagnostic/ scop=spfile;

ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1] in the alert log !

one of the databases i am supporting ( on version 12cR1 12.1.0.2) was throwing ORA-600 in the alert log:

Archived Log entry 94742 added for thread 1 sequence 48014 ID 0x6a20c315 dest 2:
Errors in file /oracle/orcl12/diag/rdbms/orcl12/orcl12/trace/orcl12_j003_115141.trc (incident=582200):
ORA-00600: internal error code, arguments: [qerixAddNotNullStopKeyPredicate:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc
Non critical error ORA-48913 caught while writing to trace file “/oraclex/orcl12/diag/rdbms/orcl12/orcl12/incident/incdir_582200/orcl12_j003_115141_i582200.trc”
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Solution:

if this error is infrequent then you can ignore, otherwise as a workaround is to disable the Automatic SQL Tuning Tasks by executing the following:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL );
END;
/

As a consequence, this will disable all automatic SQL tuning tasks but you can still perform “on-demand” SQL tuning to get advice on tuning specific SQL statements.