Yearly Archives: 2015

CloneDB: terminating the instance due to error 17514

While getting adventureous and trying to enable clonedb on a RAC database running on ASM (set clonedb parameter=true and restart the database), I got this error on alert log:

Mon Nov 16 11:11:40 2015
CloneDB: created bitmap file RAC_bitmap.dbf
USER (ospid: 15767): terminating the instance due to error 17514

RAC is the name of the database. The <SID>_bitmap.dbf file was correctly created on $ORACLE_HOME/dbs

Then it just crash the instance. Google and Metalink did not yet indexed this error. But here its description:

$ oerr ora 17514
17514, 00000, "Access to clonedb bitmap file failed"
// *Cause: Accessing the bitmap block file in clonedb environment has
// encountered an error
// *Action: check additional error messages in the trace file and call
// Oracle Support Services

The trace files did not help much. I did not investigate the problem further.

Thin PDB clones on ext4 filesystem with Oracle

Oracle brings a great new functionality: you can create thin clones – that is, not copying the source files, just pointers – on ext4 filesystem, the current default on Red Hat Linux 6.

Requirements for example below:
– Oracle
– Data files are on ext4 filesystem (or other supporting sparse files)
– Source PDB on read only mode

It is very simple to set up:

SQL> alter system set clonedb=TRUE scope=spfile;
SQL> shutdown immediate
SQL> startup

SQL> show parameter clonedb
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
clonedb                              boolean     TRUE

I will create a clone of the PDB2 database.

SQL> select name,con_id,open_mode,SNAPSHOT_PARENT_CON_ID from v$pdbs;

NAME                               CON_ID OPEN_MODE  SNAPSHOT_PARENT_CON_ID
------------------------------ ---------- ---------- ----------------------
PDB$SEED                                2 READ ONLY                       0
PDB1                                    3 MOUNTED                         0
PDB2                                    4 READ ONLY                       0

For this I need first to create the target directory for the datafiles.

SQL> ! mkdir -p /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1

Then I can do the clone using the ‘snapshot copy’ clause. It took 9 seconds.

SQL> create pluggable database PDB2_C1 from PDB2 snapshot copy file_name_convert=('/u01/app/oracle/oradata/NONRAC/PDB2','/u01/app/oracle/oradata/NONRAC/PDB2_CLONE1');
Pluggable database created.

Elapsed: 00:00:09.35

Note that the new PDB remains in MOUNT state and without showing a connection to its parent. I find unfortunate the lack of that information.

SQL> select name,con_id,open_mode,SNAPSHOT_PARENT_CON_ID from v$pdbs;

NAME                               CON_ID OPEN_MODE  SNAPSHOT_PARENT_CON_ID
------------------------------ ---------- ---------- ----------------------
PDB$SEED                                2 READ ONLY                       0
PDB1                                    3 MOUNTED                         0
PDB2                                    4 READ ONLY                       0
PDB2_C1                                 5 MOUNTED                         0

After we open the clone in READ WRITE mode, then we can see the relationship with the parent.

SQL> alter pluggable database PDB2_C1 open read write;
Pluggable database altered.
Elapsed: 00:00:02.12

SQL> select name,con_id,open_mode,SNAPSHOT_PARENT_CON_ID from v$pdbs;

NAME                               CON_ID OPEN_MODE  SNAPSHOT_PARENT_CON_ID
------------------------------ ---------- ---------- ----------------------
PDB$SEED                                2 READ ONLY                       0
PDB1                                    3 MOUNTED                         0
PDB2                                    4 READ ONLY                       0
PDB2_C1                                 5 READ WRITE                      4

And below we verify that the clone uses 4MB instead of 800MB of the source database.

SQL> ! du -m /u01/app/oracle/oradata/NONRAC/PDB2/*
6       /u01/app/oracle/oradata/NONRAC/PDB2/PDB2_users01.dbf
586     /u01/app/oracle/oradata/NONRAC/PDB2/sysaux01.dbf
261     /u01/app/oracle/oradata/NONRAC/PDB2/system01.dbf
1       /u01/app/oracle/oradata/NONRAC/PDB2/temp01.dbf

SQL> ! du -m /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1/*
1       /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1/PDB2_users01.dbf
1       /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1/sysaux01.dbf
1       /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1/system01.dbf
1       /u01/app/oracle/oradata/NONRAC/PDB2_CLONE1/temp01.dbf

We can also clone non-CDB databases with some more steps, as explained at:

The script (see documentation here) can also be used, even though you might find easier to script yourself to your own environment.

How to drop corrupted PDB?

Today on my test system I did delete by mistake all the datafiles from one PDB on Oracle

Then, when I wanted to drop it from the CDB, I was getting the error:

SQL> drop pluggable database PDB2_C2 including datafiles;
drop pluggable database PDB2_C2 including datafiles
ERROR at line 1:
ORA-01116: error in opening database file 19
ORA-01110: data file 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

After I tried to see if RMAN would propose any solution, knowing that there were no backups or archivelogs.

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
62         CRITICAL OPEN      16-NOV-15     System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is corrupt
22         CRITICAL OPEN      16-NOV-15     System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
1. If file /shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf was unintentionally renamed or moved, restore it
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
3. Please contact Oracle Support Services to resolve failure 62: System datafile 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf' is corrupt

Optional Manual Actions
no manual actions available

Automated Repair Options
no automatic repair options available

With the help of my Trivadis colleague Daniele Massimi quickly we found the solution.
The CDB was now in mount mode.

$ sqlplus / as sysdba

SQL*Plus: Release Production on Mon Nov 16 17:28:43 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             973082528 bytes
Database Buffers          520093696 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01110: data file 19: '/shared/oradata/NONRAC/PDB2_CLONE2/system01.dbf'

SQL> alter database datafile 19 offline drop;
alter database datafile 19 offline drop
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "19"

SQL> alter session set container=PDB2_C2;
Session altered.

SQL> alter database datafile 19 offline drop;
Database altered.

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter database open;
Database altered.

SQL> drop pluggable database PDB2_C2 including datafiles;
Pluggable database dropped.

Moral of the story: be careful when deleting files. ūüôā

Install latest version of Oracle Goldengate 12

With Oracle Goldengate 12 there is now a big change on the way to install: there is an installer!

Up to Oracle Goldengate 11 every new patch was a new complete version. With Oracle Goldengate 12 you need to install a base version and then the latest patch.

Here is a quick sum up of the instructions (done on AIX, but is similar on Linux):

  1. From Oracle website download the Goldengate for your operating system
  2. From Oracle support note 1645495.1 download the latest patchset for Goldengate. Check the availability for your OS.
  3. Unzip both downloaded files
  4. Call the <download_dir>/ggs_AIX_ppc_shiphome/Disk1/runInstaller
  5. Follow the instructions by choosing the target DB version and install directory (I recommend $ORACLE_BASE/product/gg)
  6. Oracle GoldenGate - Install Wizard - Step 1 of 5

    Oracle GoldenGate - Install Wizard - Step 2 of 5

    Oracle GoldenGate - Install Wizard - Step 3 of 5

    Oracle GoldenGate - Install Wizard - Step 4 of 5

    Oracle GoldenGate - Install Wizard - Step 5 of 5

  7. Check it works
  8. # set ORACLE_HOME to an existing Oracle database installation:
    export ORACLE_HOME=$ORACLE_BASE/product/
    # set LD_LIBRARY_PATH to use libraries from $ORACLE_HOME:
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/product/gg
    # Start Goldengate:
    cd $ORACLE_BASE/product/gg
    Oracle GoldenGate Command Interpreter for Oracle
    Version 19797716 OGGCORE_12.
    AIX 6, ppc, 64bit (optimized), Oracle 11g on Oct 27 2014 00:19:39
    Operating system character set identified as ISO-8859-1.
    Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
    GGSCI (anjovm1) 1> info all
    Program Status Group Lag at Chkpt Time Since Chkpt
    GGSCI (anjovm1) 2> exit
  9. Install the latest patchset with OPatch
  10. cd <download_dir>/21785294
    $ORACLE_BASE/product/gg/OPatch/opatch apply -oh $ORACLE_BASE/product/gg
    Invoking OPatch
    Oracle Interim Patch Installer version
    Copyright (c) 2011, Oracle Corporation.  All rights reserved.
    Oracle Home       : /oracle/app/product/gg121
    Central Inventory : /oracle/oraInventory
       from           : /etc/oraInst.loc
    OPatch version    :
    OUI version       :
    Log file location : /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    Applying interim patch '21785294' to OH '/oracle/app/product/gg121'
    Verifying environment and performing prerequisite checks...
    Do you want to proceed? [y|n] y
    User Responded with: Y
    All checks passed.
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/oracle/app/product/gg121')
    Is the local system ready for patching? [y|n] y
    User Responded with: Y
    Backing up files...
    Patching component oracle.oggcore.ora11g,
    Patch 21785294 successfully applied
    Log file location: /oracle/app/product/gg121/cfgtoollogs/opatch/opatch2015-11-11_08-32-55AM.log
    OPatch succeeded.
  11. Check it is updated
# Start Goldengate:
cd $ORACLE_BASE/product/gg

Oracle GoldenGate Command Interpreter for Oracle
Version 21604177 OGGCORE_12.
AIX 6, ppc, 64bit (optimized), Oracle 11g on Sep  2 2015 16:29:21
Operating system character set identified as ISO-8859-1.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (anjovm1) 1> 

We have now Oracle Goldengate installed.

Interesting enough is that Oracle Goldengate 12.1 still uses the OPatch version, taken probably from Oracle, as we can see from the line “OUI version :”.

Oracle October 2015 CPU – quick and dirty install on and 4

[Update 17.11.2015 – Thanks¬†Lars Johan Ulveseth for the extra testing and comment – see below. In fact I’ve only tested the PSU installation on a non-CDB 12c database. There is a missing step, added now, when you are in a multitenant configuration with pluggable databases. Script updated based on Lars comments.]

Following my January post, here are basic instructions to install the latest Oracle security patch. While one should always read the documentation, below you can find how to install CPU Oct 2015  on single instance. Please count about 5 minutes downtime on your database (a bit more if it is the first time you are doing this).

If you are single instance database (no RAC) and either or here are the quick summary of the installation. Tested on Linux and AIX:

Oracle РOctober 2015 CPU installation

OPatch: You need to update the OPatch tool, minimum version is Direct download from Oracle support.

Bundle – Patch 21520444 –¬†Combo OJVM PSU and Database PSU for UNIX

  • Patch 21359755 – Database Patch Set Update (Oct2015) –> RAC-Rolling Installable
  • Patch 21555660 – Oracle JavaVM Component Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"

1. Download + Upgrade OPatch (Opatch download direct link)


2. Stop database+listeners+agent

3. Apply DB PSU (21359755 ) but do NOT run DB PSU post install steps

unzip p2*
cd 2*
cd 21359755/
$ORACLE_HOME/OPatch/opatch apply

3. Apply OJVM PSU patch

cd ../21555660/
$ORACLE_HOME/OPatch/opatch apply

4. Restart database [and open pluggable databases] in upgrade mode

SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;

5. Run post install steps

./datapatch -verbose

6. Restart database [and open pluggable databases] in normal mode

SQL> shutdown;
SQL> startup;
SQL> alter pluggable database all open;

Oracle РOctober 2015 CPU installation

Bundle – Patch 21744335 –¬†Combo OJVM PSU and Database SPU (CPUOct2015)

  • Patch 21352646 – Database Security Patch Update (CPUOCT2015) –> RAC-Rolling Installable
  • Patch 21555791 – Oracle JavaVM Component Database PSU (OCT2015) –> Non RAC-Rolling Installable
export PATCH_LOC="/tmp"
unzip p2*.zip
cd 2*

1. Shutdown databases and services
2. Apply DB PSU (21352646) but do NOT run DB PSU post install steps

cd 21352646
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

3. Apply OJVM PSU patch

cd ../21555791/
$ORACLE_HOME/OPatch/opatch apply

4. Run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.

cd $ORACLE_HOME/sqlpatch/21555791
sqlplus / as sysdba 
SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown immediate
SQL> exit;
cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> startup
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> exit;


Oracle wallet on Windows registry – share with other users

I installed Oracle wallet to access passwordless to the remote databases, so I could do backups without specifying the password on any script.

The sqlnet.ora has the following information concerning the wallet:

(METHOD = reg)


The other day, I got the need of giving access to remote databases to another windows user, so he could run a refresh schema/database script.

He was getting the following error when trying to access any database:

ORA-12578: TNS:wallet open failed

While I could just have set a different TNS_ADMIN variable pointing to a sqlnet.ora with


this was not the solution for him to use an automatic script.

The solution was to export/import the following branch of the registry from my windows user to the other windows user:


Note that these entries contain the encrypted password to the wallet, allowing the user to add/remove/modify/see the contents, including passwords, of the current wallet.

Solution for slow login after installing Oracle Linux

After installing Oracle Linux on Virtualbox, I noticed that login in was quite slow. Between the time we enter the login and the moment it asks the password it takes a few seconds.

The solution was to disable the usage of reverse IP resolution on the ssh server.

It can be done by adding/changing the following line on /etc/ssh/sshd_config

UseDNS no

And restart the ssh deamon:

# service sshd restart
Stopping sshd:                                [ OK ]
Starting sshd:                                [ OK ]


Surprises happen when you go to the latest version of Oracle without deep testing.
My client just hit the bug:

Basically the client was getting a ORA-600 [QERHJDISABLEHJCBK: NOT A HASH JOIN] when doing a certain query. Looking at it, it was an UPDATE of a view. This view had a “instead of update” trigger, which was doing several actions. After enabling trace on the session, the bug does not happen!

Fortunately someone had already open the bug at Metalink. The workaround is to disable the new “groundbreaking feature” (dixit Oracle) called Adaptive Plans.

SQL> alter system set "_optimizer_adaptive_plans"=false;
System altered.

Hopefully there will be soon a patch for this problem.

Oracle automatic maintenance, expdp and ORA-01466

At a client request I’ve implemented a daily consistent export of few schemas, using something like:

expdp "/ as sysdba" SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp

I was set to run at 22:00 every night.

Next day the log showed some errors like:

ORA-31693: Table data object "USER1"."TABLE_TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed

Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!

To avoid any problem in the future we decided to change the maintenance window time and lenght.
On Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute => 'DURATION',value     => numtodsinterval(2, 'hour'));

Goldengate initial load checks

These days I’m performing a near zero downtime upgrade of Oracle database from to Both on Windows, source is 2003 and target 2012.

More information can be seen at the presentation here (not yet posted).

Now I want to call your attention to the initial load. There are a small but important checklist to follow to avoid bad surprises. On a proof-of-concept, using a source database without activity you are likely not to find any problem. If the source database is actively used by dozens of users, then attention!

I will not repeat all steps described at the Goldengate Documentation. These are the just the main reminders for a successful initial load.

Before starting the extract:
1. Supplemental log groups. In case using table level loggroups (source Oracle DB <= – Check that all tables have log groups:

select owner,table_name from dba_tables where owner in ('<USER1>','<USER2>','<USER3>')
select owner,table_name from dba_log_groups where owner in ('<USER1>','<USER2>','<USER3>');

Reason: on a active database, if the table is locked by some user, Goldengate will simply skip to add the log group (in fact, it gets “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” but in the middle of adding the ADD TRANDATA command you will likely skip the error).

Note: if there is DDL replication, make sure that a log group is always created when a new table is created. The schema level log group should solve this problem.

After starting the extract
2. Goldengate only captures transactions that begin after the extract starts:

2015-03-31 08_27_16-16 Instantiating Oracle GoldenGate with an Initial Load (12c (12.1.2))

Extract of Goldengate 12c documentation.

So you need to find the time Extract started:

GGSCI> info extract E_*

And make sure that all transactions started after that time:

select b.inst_id, b.sid, b.serial#,b.username,b.machine ,b.status,b.prev_sql_id,c.sql_text,d.object_id,e.object_name,
a.start_time,to_char(b.logon_time,'MM/DD/YY HH24:MI:SS') logon_time
from    gv$transaction a, gv$session b , gv$sql c, v$locked_object d, all_objects e
where a.inst_id = b.inst_id
and a.ses_addr = b.SADDR
and b.prev_sql_addr = c.address(+)
and b.prev_hash_value = c.hash_value(+)
and b.prev_child_number = c.child_number(+)
and b.inst_id = c.inst_id(+)
and b.prev_sql_id=c.sql_id
and d.object_id=e.object_id
and d.session_id=b.sid(+);

The previous piece of SQL not only shows the starting time of the transaction, but also shows the user and machine holding that transaction open. It is useful to identify the person and contact her instead of just killing the session. ūüôā

3. Get the current SCN and performing the consistent export of the database.

alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
col current_scn for 99999999999999999
select sysdate, current_scn from v$database;

expdp DIRECTORY=migration DUMPFILE=expdp_MIGRATION_%U.dmp LOGFILE=expdp_MIGRATION.log parallel=8 filesize=5G flashback_scn=<previous-scn> SCHEMAS='<USER1>','<USER2>','<USER3>'

4. On the replicat side, just to assure it does not start at the wrong SCN, it is better you do not have on the manager configuration the parameter: