VirtualBox 6.1 : No compatible version of Vagrant yet!

Uncategorized
| Jan 1, 2020

VirtualBox 6.1 was released on the 11th of December and I totally missed it.

The downloads and changelog are in the usual places.

I spotted it this morning, downloaded it and installed in straight away. I had no installation dramas on Windows 10, macoS Catalina and Oracle Linux 7 hosts.

The problem *for me* was the current version of Vagrant (2.2.6) doesn’t support VirtualBox 6.1 yet. I can’t live without Vagrant (more...)

Finally, sftp from an Oracle database using plain plsql

Uncategorized
| Dec 24, 2019

Just a quick blog to get me through the Christmas eve. More than 5 years ago I wrote a blog about FTPS from an Oracle databse. In that blog I already mentioned that SFTP using plsql is possible. Not loading some Java classes in the database and adding some plsql wrappers. No, just plain old plsql. At that time I had a “prove of concept”-script which was huge.

But, after moving almost all encrypting/decrypting code (more...)

Unique all the things … including your pluggables

Uncategorized
| Dec 18, 2019

A quick tip just in time for Christmas Smile

I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database!

That’s because I was! Smile Here is how it happened:

  • I have a 19c database, called it DB19, and it had a (more...)

Datapump Import Partitioned Tables ORA-00600 qesmaGetPamR-NullCtx

Uncategorized
| Dec 18, 2019

I have not yet had time to build a test case and prove this out, but I wanted to document one last bug that we found so far in our 11.2.0.4 to 19c upgrade. We tried copying a bunch of partitioned tables on our source database to the new one using Datapump Import (impdp) over a database link. We got a boatload of errors like this:

ORA-00600: internal error code, arguments: [qesmaGetPamR-NullCtx], 

(more...)

db_securefile PREFERRED results in ORA-60019 with small uniform extents

Uncategorized
| Dec 17, 2019

Last 19c upgrade issue. Working on our new 19c database, several things died off with errors like this:

SQL> execute DBMS_STATS.CREATE_STAT_TABLE ('MYSCHEMA','MYSTATTAB','MYTS');
BEGIN DBMS_STATS.CREATE_STAT_TABLE ('MYSCHEMA','MYSTATTAB','MYTS'); END;

*
ERROR at line 1:
ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8
ORA-06512: at "SYS.DBMS_STATS", line 20827
ORA-06512: at "SYS.DBMS_STATS", line 20770
ORA-06512: at "SYS.DBMS_STATS", line 20765
ORA-06512: at line 1

Our tablespaces had small uniform extents and (more...)

Datapump Import Fails on Tables With Extended Statistics

Uncategorized
| Dec 17, 2019

Quick post before I leave on vacation. We used Datapump to import a schema from an 11.2 HP-UX database to a 19c Linux database and got errors on a few tables like these:

ORA-39083: Object type TABLE:"MYSCHEMA"."TEST" failed to create with error:
ORA-00904: "SYS_STU0S46GP2UUQY#45F$7UBFFCM": invalid identifier

Failing sql is:
ALTER TABLE "MYSCHEMA"."TEST"  MODIFY ("SYS_STU0S46GP2UUQY#45F$7UBFFCM" NUMBER GENERATED
ALWAYS AS (SYS_OP_COMBINED_HASH("COL1","COL2","COL3")) VIRTUAL )

Workaround was to create the table first empty with no indexes, (more...)

Merge Always Updates Sequence Number

Uncategorized
| Dec 17, 2019

This is nothing new, but I wanted to throw out a quick post to document it. If you have a sequence.nextval in the insert part of a merge statement the merge calls nextval for all the updated rows as well.

Oracle has a bug report about this from a 9.2 issue, so this is nothing new:

Bug 6827003 : SEQUENCE # IN MERGE BEING UPDATED FOR BOTH INSERT AND UPDATE

I created a (more...)

Listener log data mining with SQL

Uncategorized
| Dec 15, 2019

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.


16-DEC-2019 09:54:20 * service_update * db18 * 0
2019-12-16T09:54:23. (more...)

Database Link to 9.2 Database from 19c

Uncategorized
| Dec 13, 2019

I have mentioned in previous posts that I am working on migrating a large 11.2 database on HP Unix to 19c on Linux. I ran across a database link to an older 9.2 database in the current 11.2 database. That link does not work in 19c so I thought I would blog about my attempts to get it to run in 19c. It may not be that useful to other people because (more...)

dbca silent mode – Windows

Uncategorized
| Dec 12, 2019

Just a quick tip that often catches me out. If you are like me, you have long since tired of clicking Next, Next, Next, … through the GUI when you want to quickly create a database. Many people work around this by storing a set of database creation scripts. However, you can do even better. The Database Creation Assistant (dbca) can also be used at the command line and in silent mode.

On Windows, this (more...)

Video : Oracle REST Data Services (ORDS) : RESTful Web Services Handling Media Files

Uncategorized
| Dec 11, 2019

In today’s video we take a look at RESTful web services handling media files built using Oracle REST Data Services (ORDS).

This is based on this article.

There is more information about related stuff here.

2019-what grabbed your attention

Uncategorized
| Dec 9, 2019

Here are the blog posts that you hit on most this year, with the most viewed entry on top. Unsurprisingly is it related to my bugbear with the OpenWorld catalog. I mean, every conference organizer must know that the one thing the attendees will always want is to get access to all of the content. Questions on UTL_FILE often come up on AskTOM, so it is unsurprising to see UTL_FILE pop up on the list. (more...)

So Loooooong, #OneNeck!

Uncategorized
| Dec 4, 2019
Today was my “official” last day at OneNeck. After a dozen interesting and enriching years at OneNeck, it is a bittersweet goodbye. I am truly thankful for all the support and goodwill I enjoyed at OneNeck over the years. Y’all have been so good to me and helped me grow in so many ways over …

What’s Taking So Long For Autoupgrade

Uncategorized
| Nov 20, 2019

Directory for autoupgrade/log: $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log, where $ORACLE_UNQNAME=db_unique_name

Create upgrade.config as shown: $ORACLE_BASE/admin/$ORACLE_UNQNAME/${ORACLE_UNQNAME}_upgrade.config

global.autoupg_log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
upg1.log_dir=$ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log
upg1.dbname=$ORACLE_UNQNAME
upg1.sid=$ORACLE_SID
upg1.start_time=NOW
upg1.source_home=/oracle/app/product/11.2/dbhome_1
upg1.target_home=/oracle/app/product/12.2/dbhome_1
upg1.upgrade_node=localhost
upg1.target_version=12.2
upg1.timezone_upg=no
upg1.restoration=yes

Let’s take a took at summary for autograde job process 102.
Find autoupgrade directories.

$ export JOBNO=102
$ ls -l $ORACLE_BASE/admin/$ORACLE_UNQNAME/autoupgrade/log/*/*/*
-rwx------    1 oracle   dba           73349 Nov 04 12:46 ORACLE_BASE/admin/ORACLE_UNQNAME/autoupgrade/100/autoupgrade_20191104.log
-rwx------    1 oracle   dba             233 Nov  (more...)

2100 detection error on hdd0 (main hdd) lenevo thinkpad t440p

Uncategorized
| Nov 19, 2019

Today I faced serious issues about my laptop external SSD harddrive suddenly failed. oops I don’t have backup of recent data. We are tried to restore from windows automatic recovery and tried many method and also we used  to remove the  SSD and inserted again but still we have same issue and HDD not detected.

So We thought it SSD Failed and data gone. But luckily One of our IT support guy (named Rasheed) helped (more...)

DOAG Conference 2019 Presentation: “LINUX Know-How for DBAs (german)”

Uncategorized
| Nov 19, 2019

I have uploaded my presentation plus the recorded demos to the website.

Exadata storage indexes

Uncategorized
| Nov 19, 2019

We had a question on AskTOM inquiring about how to handle the issue of only 8 storage indexes being possible on an Exadata engineered system. If you are unfamiliar with what a storage index is, they are part of the suite of features often referred to as the “secret sauce” that can improve query performance on Exadata systems by holding more metadata about the data that is stored on disk. You can get an introduction (more...)

Do you work in I.T?

Uncategorized
| Nov 13, 2019

If you do, then you know that only 20% of your I.T work is spent at the office. And no, I’m not talking about telecommuting or working remotely here. I’m talking about the 80% of our time we spend handling all those I.T calls from friends and family Smile

So I was motivated to put together this little ode – dedicated to all of us that have to go the extra mile to keep (more...)

Cloning a schema with one line

Uncategorized
| Nov 6, 2019

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional (more...)

DML error logging redux

Uncategorized
| Oct 30, 2019

I posted a nice little feature of DML error logging recently, and a comment came in mentioned that caution is needed with DML error logging because the errors are logged persistently. Hence if you ran a load multiple times, or multiple sessions were utilizing the facility, then the error logging table can quickly become a soup of data that cannot be tracked back to the code your own session was running.

By default this is (more...)