(unsupported) DST_UPGRADE_STATE is DATAPUMP(1) but no data pump jobs are running. How to fix?

This blog post contains unsupported commands. Do not try to use them without the supervision of Oracle Support!

I have just run across an Oracle Database who had a broken configuration in database_properties.

The database was in process of being upgraded to 18c, but the DST upgrade step was not working because of wrong entries in the view DATABASE_PROPERTIES:

The MOS note Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1) states that I had to check note How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1) to solve the problem.

In fact, there should have  been an orphan data pump job trying to import the timezone file. But in my case, no jobs at all, do data pump job tables.

Second, the secondary time zone being lower than the primary one was, to me, sign of an old upgrade went wrong.

Trying to begin a new prepare phase was failing with:

Trying to end the old one was failing as well:

Trying to unload the secondary was failing as well:

I double-checked ALL the notes to clean-up the situation and made sure that there was nothing actually running regarding a DST upgrade.

I am pretty evil trying unsupported stuff. So I have decided to check the underlying table:

Fixed tables are not writable, but sys.props$ is, and it was containing the same bad data:

So I did what I knew was wrong, after taking a guaranteed restore point. Do not try this at home without the supervision of Oracle Support!

Trying again:

The rest of the upgrade procedure went smoothly.

Ludovico

First draft of a Common Oracle Environment… for the Cloud Database (and not only)

I have just published on GitHub a draft of a common Oracle environment scripts that make the shell environment a little bit smarter than what it is by default. It uses some function and aliases that I have published during the past years.

You can start playing with:

Ideal for the Oracle Cloud Infrastructure

If you are new to the Oracle Cloud, probably you do not have environment scripts that makes it easy to interact with the database.

The environment scripts that I have published work out-of the box in the cloud (just make sure that you have rlwrap installed so that you can have a better CLI experience).

Actually, they work great as well on-premises, but I assume that you already have something automatic there.

Some examples

  • My famous Smart Prompt 😉 (including version, edition, exit code, etc)

  • u : gets the status of the databases

  • pmon: just displays the running pmon processes

  • db : sets the environment for a specific DB_NAME, DB_UNIQUE_NAME or SID

  • svcstat : shows the running services (and the corresponding pdb, host, etc) as I described in my previous post

  • s_ : smart alias for sqlplus: connects as sysdba/sysasm by default, or with any arguments that you pass:

  • adr_, dg_ rman_, cm_, lsn_ : aliases for common oracle binaries
  • genpasswd : generates random passwords (default length 30)

  • lsoh: lists the Oracle Homes attached to the inventory

  • setoh: sets the Oracle Home given its name in the inventory

 

You might want to install the same environment for oracle, grid (if you have role separation, it should be the case for Cloud DB Systems) and (eventually) root.

I am curious to know if it works well for your environment.

Cheers

Ludo

The new era of spam? Technical-related comments that defeat anti-spam filters

Part of the tasks of every blogger is to update the anti-spam filters (a WordPress plugin in my case, akismet), read the comments in quarantine and either approve them, delete them or mark them as spam (to that the anti-spam plugin can enrich the spam detection algorithms).

So far, the main factor to me to decide to approve or not, is to read the content of the message and find it appropriate or not for the blog post.

Until today.

Some time ago I have written this blog post about diagnosing problems in deleting the Audit Trail in Oracle 12c:

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL not working on 12c? Here’s why…

Last week I have got this comment:

Technical. Talking about Oracle Audit (which is relevant to my blog post) and with  technical details that only an Oracle professional might know.

So I have approved it and read my blog post again to find a proper answer.

Except that my blog post was not talking about Unified Audit, relink of binaries or exports!

I have realized then that the “link” of the blogger was in fact a website selling sex toys… (yeah, writing this here will bring even more spam… I take the risk 🙂 )

So where does it come from? Actually, the comment has been copied from another blog:

https://petesdbablog.wordpress.com/2013/07/20/12c-new-feature-unified-auditing/

A new type of spam (maybe it exists since a while? I am not a spam expert).

The spammer (bot?) copies technical comments from blog posts with similar keywords and just changes the name and the link of the author.

Beware, bloggers.

Ludo