Ever wanted to set a variable to the name of another variable, and from there, somehow get the value of the other variable? I did, recently, and this is what I had to do. I work with numerous databases but of all the ones I have, there are only 18 different types and these cover all possible (at present) systems in production or development. The first 3 characters of $ORACLE_SID define the system name and we use a script that duplicates any of 18 template databases to create the desired new one.

There is, out there in Oracle Land, a silent database killer. You never know when it will strike and it affects all databases right up to and including 12c. When it strikes, it does so silently, there is no evidence of its passing, until it is far too late. What is This Killer? The database killer is any code which runs in NOLOGGING or UNRECOVERABLE or, in some cases prior to 11g, DIRECT PATH loads.

The following error popped up in an RMAN backup which was attempting to delete archived logs that had been backed up twice, at least, and were created more than two days ago: 1 2 RMAN-03009: failure of delete command on default channel at 09/12/2014 08:43:50 ORA-15028: ASM file '+FRA/MY\_DBNAME/archivelog/2014\_09\_09/thread\_1\_seq\_35804.3258.857840113' not dropped; currently being accessed (Database names changed to protect the innocent, as usual.) David Marcos has a blog entry from September 2010 on this very matter at http://davidalejomarcos.

It was a simple enough request, flashback this particular database to a guaranteed restore point. What could possibly go wrong? Database names etc have been changed to protect the innocent, and me! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 $ sqlplus / as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. ... Database mounted.

Have you ever wanted to use a tool to parse the manually typed up “stuff” that lives in a tnsnames.ora file, to be absolutely certain that it is correct? Ever wanted some tool to count all the opening and closing brackets match? I may just have the very thing for you. Download the binary file Tnsnames.Parser.zip and unzip it. Source code is also available on Github. When unzipped, you will see the following files:

A simple exercise to refresh a schema in a test database caused no end of problems when it hung at 99% complete. The last message on screen indicated that it was importing the Materialized Views (yes, with a ‘Z’). After a long time, the DBA running the import killed it, cleaned out, and restarted the whole process. Exactly the same happened. Background The databases in question were both 11.2.0.3 Enterprise Edition.

While checking out a dataguarded database prior to being handed over into production, I needed to test that both OEM and dgmgrl could carry out a switchover and failover from the (stand-alone) primary db (ORCL_PDB) to the physical standby database (ORCL_SBY), and back again. The Problem Database and server names have been changed, to protect the innocent, and me! OEM had no problems, other than the usual “bug” whereby the credentials used for the standby server were those for the primary server, but hey, that’s OEM for you, it’s nothing if not inconsistent!

The Oracle database allows the users to change their passwords as follows: SQL> ALTER USER me IDENTIFIED BY my_new_password; or, alternatively, to use the PASSWORD command, which prompts for the old and new passwords. Of course, if the user has forgotten their old password, the system manager can do the necessary: SQL> ALTER USER forgetful_user IDENTIFIED BY a_new_password; Now, if there are profiles in use, as there are, and these profiles have a password verification function defined, these passwords will be validated to ensure that they adhere to the installation standards.

In this, the second part of the Introduction to Oracle Datapump mini-series, we take a look at importing dump files using impdp. If you missed the first part which concentrated on exporting with expdp, have a read of it here. Once again, the following is a quick introduction for people like me - running Oracle on Linux and slightly averse to change! ;-) Introduction to Datapump Imports All of the following is based on 11.

Oracle Datapump, aka expdp and impdp were introduced at Oracle 10g to replace the old faithful exp and imp utilities. Many DBAs around the world find that it’s hard to change from what we know like the back of our hand, to something new. We need to change because exp is deprecated from 10g onwards and might even already have vanished from 12c - which I have to install as one of my upcoming tasks.

A slight variation on the incremental backups. In this (short) article, I demonstrate the use of database file copy backups which are themselves updated on a regular basis to avoid having to restore and recover using numerous incremental backups. What’s Going On Here? This took me a wee while to get my head around. We can take a backup of the database, in incremental form, and then, use our nightly incremental backups to update the backup itself to the latest state of the database.

It’s been a while since the previous post in this series, but I’m back again. This time out, we are looking at incremental backups. What they are, how they work, and how - of course - to take them and use the to restore and recover your databases. More Terminology What exactly is an incremental backup? Previously, this series has shown you how to take a full backup be that of the database, archived logs, tablespaces and data files.

This post has also been categorised under “rants and raves” as you will see below! Oracle 10g was the first time that proxy users could be used easily from SQL. Prior to that only Java and/or OCI programs could use them. They’ve been around since 8i, but not (well) documented. Want to know more? Read on…. A Bit of Background Many years ago, a software company I worked in - as a DBA - was taken over and we inherited a system (no names - you will see why later) which allowed numerous users the ability to use the system, and some of them got to create documents from within the application.