Blog

How to change the Apex Application from LTR to RTL?

If you are developing Arabic application using Oracle Apex and want to change the the direction of the whole application from LTR(Left-To_Right) to RTL (Right-To-Left) , heer are the quick steps:

 

1- Select the application Select Shared Components.

1

then select “Globalization”

23

Then set the “Document Direction” attribute to “Right-To-Left”

That is all!

Ransomware in Arabic فيروس دفع الفدية بالعربي

بالنسبة للناس إللي إتصابت فعلا بفيروس الكومبيوتر اللي منتشر اليومين دول, يا تري هل هناك أى فرصة لإسترجاع ملفاتهم؟

الإجابة نعم قد تكون هناك فرصة للبعض (وليس للكل).

عشان نفهم الإجابة لازم نفهم الأول هو الفيروس ده بيعمل إيه?

الفيروس ده إسمه  Ransomeware (WannaCry)  ومعناه بالعربى دفع الفدية.

الفيروس بيقوم بتشفيرملفاتك الشخصية وبيطلب منك دفع مبلغ 300 دولار لفك التشفير.

اذا لم تقم بالدفغ خلال 3 أيام بيقوم برفع المبلغ الى 600 دولار واذا لم تدفع بعد 7 أيام  فلا يمكن إستعادة ملفاتك بعد كده أبدأ لأنهم ببساطة بيقوموا بمسح “مفتاح التشفير” من عندهم..

الفيروس ده أصاب عشرات الألاف من الأجهزة سواء الشخصية أو الشركات على مستوى العالم كله.

النقطة المهمة اللي لازم نفهمها فى طريقة عمل الفيروس ده هى أنه بيعمل نسخة جديدة مشفرة من ملفاتك ويقوم بمسح النسخة الأصلية. بالنسبة للنسخة المشفرة فهذه لا يوجد طريقة لإسترجاعها (حتي  الأن). وعلي فكرة حتي لو دفعت مش هترجع لأن هناك مشكلة فنية فى عملية إسترجاع البيانات بعد الدفع.

طيب بالنسبة للنسخة الأصلية اللي تم مسحها هل يمكن إسترجاعها؟ الإجابة نعم فى بعض الحالات:

بالنسبة للملفات اللي موجودة علي فلاشة أو ديسك خارجي أو موجودة فى ال Desktop  أو Documents  فاللأسف لا يمكن إسترجاعها لأنه بيتم مسحها بطريقة “عميقة”.

بالنسبة للملفات الأصلية الممسوحة فى الأماكن الأخرى (الديسكات الداخلية) فده بيتم مسحها بطريقة عادية (أمر delete) وبالتالي فهناك فرصة كبيرة جدا لإسترجاعها بأى برنامج مجاني بيعمل undelete

طبعا الوقاية خير من العلاج, ودى بعض النصائح العامة:

إحتفظ بنسخ إضافية من ملفاتك المهمة علي ديسكات خارج جهازك أو علي الأنترنت.

إستعمل برنامج مقاوم فيروسات Antivirus

نزل أخر تحديثات للويندوز.

لو تقدر تسيبك من الويندو خالص و تشتغل علي اللينكس يبقى أحسن بكتير!.

Ransomware

sqlcl Multi-Line Paste Issue

What is the issue?

I noticed that sometimes when I copy SQL statement from web and paste it on sqlcl , it is copied on many lines and generating errors.

For example, when I copied the following statement:

1

And pasted into sqlcl running on the windows command window, it was pasted as follows:

2

So, it was copied as three separate statements instead of one statement, which led to the errors shows above.

 

If you paste the same above statement on the sql*plus running on the same windows command window, it will accept it as one statement, like:

3

So, what is reason behind that?

After a lot of research and after discussing it with Jeff Smith and getting inputs from “TR Rudkin”:

http://www.thatjeffsmith.com/ask-a-question/comment-page-3/#comment-131653

I found the following interesting remarks:

If I compares the statement with the above issue with another statement that is working fine, I found that both are having a different end of lines special characters.

For the statement that is working without the above issue, the end of line is: “CRLF” which is Carriage Return + Line Feed (Hexa  codes: 0D0A).

4

For the statement that is having the above paste issue, the end of line is: “LF” which is Line Feed (Hexa  codes: 0A).

5

I guess the reason behind this issue may be the source of the statement. Some statements may be formatted with Unix end of line format while others are formatted for Widnows.

It worth mentioning that sqlplus absorbs this difference without issue, but sqlcl error out with lines that end up with LF only.

Also, it worth mentioning that if you just copy/paste the code that has this issue into a text editor like “Notepad++’, it will automatically adjust the end of lines to CRLF, then if you copied that code from Notepad++, it will work fine.

Oracle tnsnames.ora Distribution Management System!

What?

Does tnsnames.ora file need a Distribution Management System?

In our case, the answer is yes. It is a headache to implement our client requirements without some sort of automation.

What is tnsnames.ora file?

tnsnames.ora is a text file that resides on the ORACLE_HOME/network/admin and contains some simple information about our databases connection information, it is like:

1

What is problem that we need to fix?

Our client has two production Exadata machines (total database nodes =5= two separate clusters), plus a normal linux server for DR, so we have total of 6 database nodes.

Following is the high level architecture:

2

There are many databases consolidated in these servers (about 40 databases), and these databases have many database links referring to other non-Exadata databases.

In each database server, our client has 3 database homes for: 12c,11.2.0.4 & 11.2.0.3.

Each database home contains its own tnsnames.ora file related to the databases resides on this home. So, we have 18 tnsnames.ora file across all the database servers/homes.

Due to the huge number of connection strings (about 150 tns entries and keep increasing each day with consolidation,the tnsnames.ora file is currently about 1500 line) and the difficulty of mapping each TNS entry to which database/home/node/cluster, so, our client decided to create only one master tnsnames.ora file that contains all connection strings across all database nodes/database homes and maintain only this file.

Now, the main points we want to address are:

  • How will we create the consolidated master tnsnames.ora?
  • How will we validate the consolidated tnsnames.ora has no typo errors?
  • Where we will keep this master tnsnames.ora file?
  • If there is a change in the master tnsnames.ora file (e.g adding/deleting/modifying new entries), then how will we distribute the new file across all nodes/homes without much manual intervention?

The solution that I will present below is my solution with my colleague “Moustafa Hassan”, but I’m quite sure that there is another better solutions/options.

1-     Creating the Consolidated Master tnsnames.ora file

Actually, this step is a little bit manual. We simply copied the tnsnames .ora file for each database home from all nodes/homes into one big master file, then manually we started to search about any repetition and remove it.

This is simply done using some powerful editors like “Notepad ++ or sublime or even vi”.

Please note that in the current situation, each database home has its own tnsnames.ora file which may be different or partially similar to that of other homes.

Now, we have one consolidated file contains all TNS entries from all local tnsnames.ora files at all nodes/homes.

2-     Validating the Consolidated Master tnsnames.ora file

Because we were afraid of having any typo errors while preparing the master tnsnames.ora file, so, we thought of a way to validate this file. The best option is to use the “tnsping” utility to ping each entry in this file and ensure a successful ping. Doing this manually for about 150 entry takes time, so, we wrote the following shell script command to do it for us:

for t in `egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1`

echo “=========”

do echo $t;

echo “=========”

tnsping $t

done | tee tnsping.out

Simply this shell command/script is parsing the tnsnames.ora file for lines that are not starting with space (i.e lines that contain the connect string names).

Let us take the following tnsnames.ora as an example:

3a

So, the first line in our script will return the names of the connection strings, like:

egrep ‘^[a-zA-Z].*=’ Master_tnsnames_Exadata_v1.ora| cut -d”=” -f1

4

Then it will pass this set of tns entries to the “tnsping” utility and then spool the output to a file, while seeing it on the screen also.

This will produce the following output:

=========

XPS

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS)))

OK (20 msec)

=========

XPS10

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS10)))

OK (0 msec)

=========

XPS20

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS20)))

OK (0 msec)

=========

RAC

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:57:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-cluster-scan.rac.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XPS.rac.com)))

TNS-12535: TNS:operation timed out

=========

mcwb

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.97.58.45)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mcwb)))

TNS-12543: TNS:destination host unreachable

=========

SPROD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:48

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = insightproddb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = SPROD)))

TNS-12545: Connect failed because target host or object does not exist

=========

PROD.WORLD

=========

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 27-APR-2017 13:58:52

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

D:\app\12cdb\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = afattah-lap)(PORT = 1524))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))

TNS-12545: Connect failed because target host or object does not exist

Doing that, it will be very simple to catch any wrong tns entries and fix them before going forward. Also, this command enable you to find out any outdated entries that reference severs or databases that are no longer exist.

 

3-     Location of the Consolidated Master tnsnames.ora file

As we have three different environment. Two Exaxdata machines and another standalone DR server, the question was where we should locate our master file?.

Some options:

  • For each machine put the master tnsnames.ora file in any location and then adjust the TNS_ADMIN for all oracle homes to point to that location.
  • Another option which is similar to the above one, but with using a symbolic link under the oracle home to point to the master file.
  • One option was to create a NFS mount point that is seen from all servers and putting the master file there and adjusting the TNS_ADMIN also.
  • The last option that I chose was to keep the master file in one server offline, and when you need to update it, just update it in that server then run a script to distribute it to all servers/homes (you need to validate it before distribution as we did above). Also, the script will backup the existent local tnsnames.ora files under each ORACL_HOME before replacing them.

 

4-     Maintaining the Consolidated Master tnsnames.ora file

 

Now, assume that we have the master tnsnames.ora file at server1 (db_node1) with name “Master_tnsnames_Exadata_v1.ora” and we want to modify it and add new entries, the procedure will be as follows:
1- Copy the Master_tnsnames_Exadata_v1.ora to Master_tnsnames_Exadata_v2.ora
2- Edit the new file “Master_tnsnames_Exadata_v2.ora”
3- Add the new entries
4- Run the following script distribute_tnsnames.sh to complete the mission:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The above script will deploy the new master file across all servers and database homes.

Note that you will need to setup a trust ssh link between the server that contains the master tnsnames.ora file “db_node1) and all the other servers, so, you will not be prompted to enter the password for each server.

This is simply done by copying the entry in the “id_rsa.pub” file in the master server, to the “authorized_keys” file in all other servers. Please note that these files exist under /home/oracle/.ssh directory.

5-     Distributing the Consolidated Master tnsnames.ora file

The following shell script “distribute_tnsnames.sh” will take care of backing up the current local tnsnames.ora file files before replacing them with the new master tnsnames.ora file.

This script accepts only one parameter which is the name of the new master file.

Usage should be as follows:

./distribute_tnsnames.sh Master_tnsnames_Exadata_v2.ora

The source code of this script is listed below:

#!/bin/bash

script_dir=`dirname $0`

script_name=`basename $0`

if [ $# -ne 1 ]; then

echo “Usage: $script_name {master_tns_file_nama}”

exit 1

fi

master_file=$1

if [ ! -f $master_file ]; then

echo “Error: master file: $script_dir/$master_file not found”

exit 2

fi

for node in `cat $script_dir/db_nodes | egrep -v “^#”`; do

if [ $node != “db_node1″ ]; then

scp -p /home/oracle/master_tns/$master_file $node:/home/oracle/master_tns/

fi

done

OH3=”/u01/app/oracle/product/11.2.0.3/dbhome_1″

OH4=”/u01/app/oracle/product/11.2.0.4/dbhome_1″

OH12=”/u01/app/oracle/product/12.1.0/dbhome_1″

TNS3=$OH3″/network/admin”

TNS4=$OH4″/network/admin”

TNS12=$OH12″/network/admin”

# Backup current tnsnames.ora

# ===========================

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS3/tnsnames.ora $TNS3/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS4/tnsnames.ora $TNS4/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

dcli -g $script_dir/db_nodes  -l oracle “cp -p $TNS12/tnsnames.ora $TNS12/tnsnames.ora_bkup_`date +%d%b%y_%H%M`”

# Distribute the new tnsnames.ora

# ===============================

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS3/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS4/tnsnames.ora”

dcli -g $script_dir/db_nodes  -l oracle “cp -p /home/oracle/master_tns/$master_file $TNS12/tnsnames.ora”

We are using the “scp” command to copy the new master tnsnames.ora file from the first node to a specific directory “/home/oracle/master_tns/” across all servers.

The dcli is a powerful utility available with Exadata, that enables you to run the same command across all nodes (or subset of nodes).

We are simply using the “dcli” utility to do two things:

  • Backup the current local tnsnames.ora file under each oracle home at all servers.
  • Replace the current local tnsnames.ora file under each oracle home at all servers with the new master consolidated tnsnames.ora file.

The db_nodes file is a simple test file that contains the names of the database servers, it is like:

5

Also, if you want to partially distribute the new master file to subset of the above nodes, you can simply comment the un-wanted servers, like:

6

Caution:

Don’t use this script before validating the new master tnsnames.ora!

Use this procedure at your own risk!.

Oracle Database 32-bit Funny Migration to 64-bit

Objective:

Migrating one 11.1.0.7 32-bit database that resides on an old Windows 2008 (32-bit) (4GB memory) to Windows 2008 64-bit (no upgrade) on a brand new server with 64GB memory.

{At 2017 and still there is 11.1.0.7 database ==> yes there is!}

 

Introduction and Background

I have a client with the following very old environment:

O.S.:                      Windows 2008 32-bit

Oracle:                  11.1.0.7 32-bit

This environment was initially installed at 2002 (with Oracle 901) and then it went with a couple of upgrades until they reached 11.1.0.7, then they stopped upgrade, why?

This was actually because of the application vendor closed completely and disappeared !!

Since that date, client decided to stop any further database upgrades to avoid any issues with the application (as there is no application support going forward).

In parallel, he started to develop a new application with a new vendor with the latest technologies, but they didn’t finish yet.

Until the new application is completed, he wants to keep the old system running as it is!.

But, given the increased workload and data volumes and the 32-bit memory limitations ( < 4GB), he started to suffer heavily from the memory shortage.

So, to work around the memory issue, he decided to move the database from 32-bit platform to 64-bit platform without upgrade to minimize any impact on the old application.

So, it looks like a simple migration, let us see!

 

Which Windows Version?:

Ok, as we are going to move to another new powerful server, so, let us move to the latest Windows version available. Let us go with Windows 2012 R2. This is fine, but wait, as Oracle 11.1.0.7 is not certified with Windows 2012, actually there was no Windows 2012 at that time!.

Ok, no problem so let us go with Windows 2008 R2 64-bit.

  • Unfortunately, Oracle 11.1.0.7 is not certified with this version, it is certified only with Windows 2008 64-bit (without R2)!.

Ok, go ahead and install Windows 2008 (without R2) on the new server and start the migration.

No, wait please, the new hardware is not compatible with the Windows 2008 , it is only compatible with Windows 2012 !!!!

Ok, no problem, let us install Windows 2012 R2 Enterprise Edition (64-bit) on the new server and then create a Virtual Machine on top of it with Windows 2008 64-bit , as a workaround.

So, I used the Windows Hyper-V virtualization technology to implement this and created a new Windows 2008 64-bit virtual machine.

So far so good, let us move on.!

Where is the Database Software?

Client has only 11.1.0.6 (+ 11.2.0.7 patchset) 32-bit, he doesn’t have the 64-bit version.

Ok, no problem, go ahead and download it from OTN or edelivery ==>  unfortunately, Oracle already removed these old versions from its sites!.

The only way to get them, is to send Oracle a request and they will send you a physical DVD with the required software. The other option is to ask one of your Oracle friends to download it for you (on friendly basis) from Oracle Internal sites {ok thanks for my friend!]

For Windows 2008 64-bit, we are lucky, as we have 11.1.0.7 full version that can be installed directly. For any other Windows 64-bit platform, you have to install 11.1.0.6 first and then patch it to 11.1.0.7.

Install Hyper-V

123456789101112

 

Create new virtual Machine

131415161718192021222324252627282930

Disable Firewall.

Assign  static IP

Oracle 11.1.0.7 64-bit Installation

313233343536373839

 

Database Migration

Assumptions:

  • Same data file directory structure
  • Same characterset
  • Physical – as it is – migration from 11.1.0.7 32-bit (Windows 2003 32-bit) to 11.1.0.7 64-bit (Windows 2008 Enterprise Edition 64-bit).

Note that the new server version OS version is Windows 2008 Enterprise Edition 64-bit, not Windows 2008 Enterprise Edition 64-bit R2 ( as Oracle 11.1.0.7 is not certified with R2)

Migration Procedure:

  • Shutdown the source PIS database (take it offline in case of Fail Safe environment)
  • Take Full database offline backup (Database size is small , about 20 GB)
  • Copy the production database full backup to the new server  ( it takes about 5 minutes over network).
  • Copy initXXX.ora & Password file
  • Optionally, copy the tnsnames.ora, listener.ora & sqlnet.ora
  • Edit initXXX.ora file:

Remove all _ parameters

Create all the referenced directories

Enlarge the SGA  and configure ASMM

 

  • Copy the datafiles, redo files, control files to the same directories as that on the production

 

  • Configure default listener using the “Net Configuration Assistant”
  • Create the Oracle service on the 64bit system, using the ORADIM command

        oradim -new -sid XXX -startmode auto -SRVCSTART SYSTEM -intpwd ****** –   pfile M:\app\Administrator\product\11.1.0\db_1\database\initXXX.ora

You may need to write the above command manually, not copy/paste!                                                Instance created and started

 

Set environment

      set ORACLE_HOME=M:\app\Administrator\product\11.1.0\db_1         

      set PATH=%ORACLE_HOME%\bin;%PATH%                          

      set ORACLE_SID=XXX

 

If the service started the database in the “READ WRITE” mode then:

Leave the Windows service running

From command window:

                                sqlplus / as sysdba

shutdown immediate

startup mount

 

 

To avoid the issue of FRA being 100% utilized:

                                 rman target /

crosscheck archivelog all;

crosscheck backupset;

delete expired backupset;

shutdown immediate

 

OLAP:

If you have OLAP installed, then you may encounter many errors [ORA-07445/ORA-03113] later on. So, to avoid these errors, we will remove the OLAP and then add it again:

{Please refer to:

DB CONVERSION: 32 bit –>64 Bit Broke OLAP OPTION [ID 386990.1]

http://www.orafaq.com/forum/t/170415/

 

                                sqlplus / as sysdba

startup upgrade

 

Remove OLAP

                                                @?/olap/admin/catnoamd.sql

@?/olap/admin/catnoaps.sql

@?/olap/admin/catnoxoq.sql

@?/olap/admin/olapidrp.plb

 

Add OLAP  ( if it is required) or add it later after migration using :                                                            @?/olap/admin/olap.sql SYSAUX TEMP;

 

{{If the utlrp script hang, this is mainly due to OLAP being installed}}

 

        Migrate:

@?\rdbms\admin\utlip.sql

@?\rdbms\admin\utlrp.sql                         ==> 5 minutes

39a

All of the above 70 invalid objects are related to the OLAP, so , don’t worry!

     select count(*) from dba_objects where status=’INVALID’;

shutdown immediate

startup

select name,platform_name,open_mode from v$database;

Congratulation ,  Migration completed !

 

If you want to add the OLAP option:

@?/olap/admin/olap.sql SYSAUX TEMP;                               ==> 3 minutes

@?\rdbms\admin\utlrp.sql                                         è zero invalid object

{But note that adding OLAP will produce many core dumps and trace files, it is better to keep OLAP deleted unless you really need it}

 

Post Migration

 

Configure EM DB Control on the New Server

 

Ensure that the listener is up and running

emca -deconfig dbcontrol db -repos drop

40

emca -config dbcontrol db -repos create

4142

Emctl status dbconsole

42a

43

 

Windows Services:

44

Done!

Oracle Data Pump Import Log File Analysis

In most (or all) cases , when you fully import the database using oracle data pump or the old “imp” import utility , you will get some errors. Some of them are expected and could be ignored and some need close attention to investigate and fix.

For big databases, the import log file may be huge and it may take a lot of time to review. In this blog I’ll show you a quick way to analyze the errors exist in the import log file.

For Unix Platforms:

Execute the following two commands:

tail -20 import.log

This will report the last 20 lines in the import log file, with the number of errors, if any.

For example:

1

 

If you executed the following command:

awk ‘($1 ~ /ORA-/)’ TMS_full_import.log|awk ‘{print $1}’|sort|uniq -c|sort –fr

you will get a summary report of all errors in the import log file and how many times they occurred. For example:

2

From here, you can drill down and investigate why each category of the above errors occurred.

For example, for the erros “ORA-31681” which was reported 32 times during the import:

ORA-31684: Object type ROLE:”AQ_ADMINISTRATOR_ROLE” already exists

So, we are importing an object that is already exist in the target database è could be ignored safely.

 

For Windows Platforms:

I found a similar commands developed for Windows Power Shell by “Marco Mischke”.

So, you may move import log file to the windows and run the following two commands from the power shell:

Get-Content import.log -tail 20

3

Get-Content import.log | %{$_.split(‘:’)[0]} | Select-String -pattern ^ORA- | Group-Object | Format-Table count, name

 

4

You will get a similar results.

Oracle SQL: Normalize the UnNormalized

What is the problem?

Sometimes you are faced with a bad (poorly normalized) schema design. For example:

1

But, you may need the output to be in the following normalized form:

2

The correct solution may be to fix the schema design, but in most cases this will not be feasible.

Test Case 1:

 

We need to write our query to present the data as it should be stored, in spite of how it is being actually stored.

Let us build the following test case:

CREATE TABLE Cust_Phones

(

CustomerID INT PRIMARY KEY,

Phone1 VARCHAR(32),

Phone2 VARCHAR(32),

Phone3 VARCHAR(32)

);

INSERT into Cust_Phones  (CustomerID, Phone1, Phone2, Phone3)

VALUES   (1,’705-491-1111′, ‘705-491-1110′, NULL);

INSERT into Cust_Phones   (CustomerID, Phone1, Phone2, Phone3)

VALUES  (2,’613-492-2222′, NULL, NULL);

INSERT into Cust_Phones   (CustomerID, Phone1, Phone2, Phone3)

VALUES  (3,’416-493-3333’, ‘416-493-3330’, ‘416-493-3339’);

Commit;

Select * from cust_phones;

1

Now to normalize the output, we will write our query to use the unpivot operator as follows:

SELECT CustomerID, Phone

FROM

( SELECT CustomerID, Phone1, Phone2, Phone3  FROM Cust_Phones )

UNPIVOT ( Phone FOR Phones IN (Phone1, Phone2, Phone3));

2

The magic operator here is the “unpivot”

The “Phones” alias  is saying: “extract a new row for every value you find in the columns Phone1, Phone2, and Phone3.

Test Case 2:

What if we have a more complicated case of 3 phone numbers with different types, like:

3

And we need to normalize the output to be as follows:

4

Let us build the following test case:

CREATE TABLE Cust_Phones2

(

CustomerID INT PRIMARY KEY,

Phone1 VARCHAR(32),

PhoneType1 CHAR(4),

Phone2 VARCHAR(32),

PhoneType2 CHAR(4),

Phone3 VARCHAR(32),

PhoneType3 CHAR(4)

);

INSERT into Cust_Phones2 VALUES (1,’705-491-1111′, ‘cell’, ‘705-491-1110’, ‘home’, NULL,NULL);

INSERT into Cust_Phones2 VALUES  (2,’613-492-2222′, ‘home’, NULL, NULL, NULL, NULL);

INSERT into Cust_Phones2 VALUES   (3,’416-493-3333′, ‘work’, ‘416-493-3330’, ‘cell’,’416-493-3339′, ‘home’);

commit;

select * from Cust_Phones2;

3

Now to normalize the output, we will write our query to use the unpivot operator as follows:

SELECT CustomerID, Phone, PhoneType

FROM

(  SELECT CustomerID, Phone, PhoneType, Phones , PhoneTypes

, SUBSTR(Phones, LENGTH(Phones) – REGEXP_INSTR( REVERSE(Phones) , ‘[^0-9]’) + 2, 32) as idp,

SUBSTR(PhoneTypes,  LENGTH(PhoneTypes) – REGEXP_INSTR(REVERSE(PhoneTypes) , ‘[^0-9]’) + 2, 32) as idpt

FROM ( SELECT CustomerID, Phone1, Phone2, Phone3, PhoneType1, PhoneType2, PhoneType3 FROM Cust_Phones2 )

UNPIVOT (Phone FOR Phones IN (PHONE1, PHONE2, PHONE3))

  UNPIVOT (PhoneType FOR PhoneTypes IN (PhoneType1, PhoneType2, PhoneType3)) )

WHERE idp = idpt;

4

We used one “unpivot” operator to normalize the “phone” è produced number of rows = number of phones of each customer.

We used another “unpivot” operator to normalize the “PhoneType” è produced number of rows = number of phone types of each customer.

Note that if you run only the middle select query:

SELECT CustomerID, Phone, PhoneType, Phones , PhoneTypes

, SUBSTR(Phones, LENGTH(Phones) – REGEXP_INSTR( REVERSE(Phones) , ‘[^0-9]’) + 2, 32) as idp,

SUBSTR(PhoneTypes,  LENGTH(PhoneTypes) – REGEXP_INSTR(REVERSE(PhoneTypes) , ‘[^0-9]’) + 2, 32) as idpt

FROM ( SELECT CustomerID, Phone1, Phone2, Phone3, PhoneType1, PhoneType2, PhoneType3 FROM Cust_Phones2 )

UNPIVOT (Phone FOR Phones IN (PHONE1, PHONE2, PHONE3))

  UNPIVOT (PhoneType FOR PhoneTypes IN (PhoneType1, PhoneType2, PhoneType3))

 You will get the following output:

5.jpg

Which is like a Cartesian product of the number of phones * the number of corresponding phone types

To get only the relevant rows, we added the last where condition:

WHERE idp = idpt;

The following part of the query:

    SUBSTR(Phones, LENGTH(Phones) – REGEXP_INSTR( REVERSE(Phones) , ‘[^0-9]’) + 2, 32) as idp,

Tills us it is phone1 or phone2 or phone3. For example:

select substr(‘PHONE1’,LENGTH(‘PHONE1’) – REGEXP_INSTR(REVERSE(‘PHONE1′),'[^0-9]’)+2,32) from dual;

Will produce 1

While:

select substr(‘PHONE2’,LENGTH(‘PHONE2’) – REGEXP_INSTR(REVERSE(‘PHONE2′),'[^0-9]’)+2,32) from dual;

Will produce 2

and so on.

The following part of the query:

      SUBSTR(PhoneTypes,  LENGTH(PhoneTypes) – REGEXP_INSTR(REVERSE(PhoneTypes) , ‘[^0-9]’) + 2, 32) as idpt

Tills us it is phonetype1 or phonetype2 or phonetype3. For example:

select substr(‘PHONETYPE1’,LENGTH(‘PHONETYPE1’) – REGEXP_INSTR(REVERSE(‘PHONETYPE1′),'[^0-9]’)+2,32) from dual;

  • Will produce 1

 

Select REGEXP_INSTR(REVERSE(‘PHONETYPE1′),'[^0-9]’) from dual;

  • Will return 2, which is the position of the first non-numeric character from the end of the string.

 

Test Case 3:

What if you add a new phone number to the above table “Phone4” & “PhoneType4”,in this case you have to modify your queries. But is there a more dynamic way to build the UNPIVOT queries without advanced knowledge of the number of Phone/PhoneTypes? . This may be a subject of another future blog !

References:

A similar SQL Server case:

https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/