Data Pump or Data Pain Part04 – The End

Please take a look at my notes below:
DataPump: Schema/DB Migration Notes


Data Pump or Data Pain Part03 – user/db object

You might be thinking, why is it necessary to specify schemas versus perform full import or why create SQL file?

$ cat impdp_full02_user.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_user.log
schemas=GGS_ADMIN,DEMO
include=USER
sqlfile=user.sql

There are 5 users imported which correspond to EXU8USR view.
If users already exists in the new database, wouldn’t create user just fail?

From demo below #schemas=GGS_ADMIN,DEMO (commented) which will
import SYS/SYSTEM/OUTLN

$ impdp parfile=impdp_full02_user.par

Import: Release 11. (more...)

Data Pump or Data Pain Part02 – tablespace/user

This is still all WIP and if you stay with me, I will provide all the sequences for export and import.

Too much info put in one post.

EXP-10 Usernames Which Cannot Be Exported (Doc ID 217135.1)
Did you know from $ORACLE_HOME/rdbms/admin/catexp.sql there is view EXU8USR providing which schemas will not be in full export?

This looks rather similar to column oracle_maintained from dba_users for 12c database.

Schema bold red will not be (more...)

Oracle Service Bus: Pipeline alerts in Splunk using SNMP traps

Oracle Service Bus provides a reporting activity called Alert. The OSB pipeline alerts use a persistent store. This store is file based. Changing the persistent store to JDBC based, does not cause pipeline alerts to be stored in a database instead of on disk. When the persistent store on disk becomes large, opening pipeline alerts in the Enterprise Manager (12c) or Service Bus console (11g) can suffer from poor performance. If you put an archive (more...)

Virtual Development Server: Provide Docker images in docker registry for Oracle XE Database and others

For later creation of containers as needed in the deployment process we have first to build docker images whenever we do not find suitable one in docker hub.

For most of my development I need a oracle database. On the internet you can find a lot of Dockerfile’s for this, but no ready image. This is because for running a database you have to accept a license and this happens when you download the installation (more...)

AIA Rides Again?

canonicaldatamodel

For those who have been using the Application Integration Architecture on top of Oracle SOA Suite, will probably know that Oracle have sunset AIA as of 12c.  For 12.1 there are Core Extensions to help transition onto the 12c platform but 12.2 leaves these behind.

One of the more valuable parts of AIA for many has been the prebuilt but extensible canonical data model, which are then used by the Prebuilt (more...)

INSERT ALL caveat

Why you might want to think twice about using INSERT ALL.

One of those things I knew and then forgot.

So, let’s say you’ve got three tables or a partitioned table or something like that.

Let’s use regional tables for simplicity.

drop table t1_r1;
drop table t1_r2;
drop table t1_r3;

create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));

create table t1_r2
(col1 varchar2(2) not null
,col2 number not  (more...)

PROCESSED Messages not clearing from Oracle Queue

I was contacted by Dev team to look into Development database where Oracle queue size kept  on increasing. As per them messages were not getting cleared.They were running count on Queue table and the count kept increasing. To find out exact cause I included the msg_state in the query and found out that there were…

APEX 5.0 Beyond basics

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.

This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about (more...)

MRP process on standby stops with ORA-00600

A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was 11.2.0.3 on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.

The error message was something like this

MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/11.2.0.3/diag/diag/rdbms/xxxprd/xxxprd1/trace/xxxprd1_pr00_6342.trc:
ORA-00600: internal error code, arguments: [2619], [539], [], [], [], [], [],  (more...)

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
(more...)

ORA-01873 error running SAP pre-upgrade scripts

While trying to run pre_upgrade_status.sql script for DB upgrade (10.2.0.4 to 11.2.0.4), we got following error @pre_upgrade_status.sql ERROR at line 1: ORA-01873: the leading precision of the interval is too small ORA-06512: at line 8 On debugging further , found out that it was due to limit imposed on "INTERVAL DAY TO SECOND" datatype. By…

LGWR terminating instance due to error 338

Recently we came across a issue where our DB crashed with ORA-00338 error . Errors in file /oracle/diag/rdbms/orcl11g/orc11g/trace/orc11g_lgwr_24118.trc: ORA-00338: log 2 of thread 1 is more recent than control file ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl11g/redo02.log' LGWR (ospid: 24118): terminating the instance due to error 338 DB couldn't be restarted as it gave same errors…

Permissions for both HDFS and local fileSystem paths

| Jul 18, 2014

Hi All,

Permission issues is one of the key error , while setting up Hadoop Cluster, while debugging some error found below table on http://hadoop.apache.org/ . It’s a good scorecard to keep handy.

 

Permissions for both HDFS and local fileSystem paths

The following table lists various paths on HDFS and local filesystems (on all nodes) and recommended permissions:

Filesystem Path User:Group Permissions
local dfs.namenode.name.dir hdfs:hadoop drwx——
local dfs.datanode.data.dir (more...)

Rolling upgrads using logical standby database.

Couple of weeks ago there was a Twitter discussion started by Martin Bach (@MartinDBA) about cases for logical standby implementation. A rolling upgrade was mentioned by Tim Gorman (@timothyjgormanas) as one of potential recommendations for using this rare use product. I have been involved in such project in the past and I prepared an instruction and did quite large number of rolling upgrades from 11.1 into 11.2.

There are couple of my “gotchas”

Oracle Direct NFS and Infiniband: A Less-Than-Perfect Match

Readers of an earlier post on this blog will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.

To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)

Best of Oracle Security 2013

I just uploaded my DOAG 2013 presentation “Best of Oracle Security 2013“.

 

This presentation shows how to bypass Oracle Data Redaction, become DBA using CREATE ANY INDEX, Hide information from Oracle Auding using VPD and more…

—————————————————

SQL> select * from scott.credit_card where 1=ordsys.ord_dicom.getmappingxpath((card_id),user,user);

(more...)

Decrypt Oracle 11.2.0.3 and 12.1.0.1 database link passwords

At Derbycon 3.0, László Tóth and Ferenc Spala  gave a a new presentation “What’s common in Oracle and Samsung? They tried to think differently… ” (Video). The main focus of the presentation was the Samsung encryption and a new framework called sandy but there was also a small (more...)

BI Mobile HD App 11.1.1.7

Finally got around to writing my long overdue post on the new BI Mobile HD app. To get on with it, without much ado, not to loiter, and all that... here goes.

The new BI Mobile HD app, version 11.1.1.7.0.2094, can be downloaded from (more...)

Creating your first app with the BI Mobile App Designer

While creating sophisticated mobile apps that provide a rich feature set takes time, expectedly, what is wonderful about the recently launched Oracle Business Intelligence Mobile App Designer is the fact that you can be up and running without requiring a week-long training and a Master's degree in Computer Engineering.

Here's (more...)