maandag 19 mei 2014

Reverse Engineer PostgreSQL with Oracle Data Modeler

In this blog I will show you how to reverse engineer a PostreSQL database using Oracle Sql Developer Data Modeler. I used PostgreSQL 9.1 and Data Modeler 4.1.0 to test this, but it should also work with other versions.

Download the correct JDBC

To be able to connect from Data Modeler to PostgreSQL you need to install a Postgresql JDBC driver which can be downloaded here:

PostgreSQL JDBC Driver

Install the JDBC driver in Data Modeler

Start Data Modeler.
In the menu click [Tools] - [Preferences] - [Data Modeler] - [Third party JDBC driver]

Now click [Add], the plus sign, and select the JDBC driver you downloaded.












Click [Ok] to close the preferences window

Setup connectivity to the PostgreSQL database

In the Data Modeler menu click [File] - [Import] - [Data dictionary] - [Add] 
Enter the JDBC connection settings. Make sure to click the JDBC tab in stead of the Oracle tab. Database Type can be left empty. Sample:

















Start reverse engineering

In the Data Modeler menu click [File] - [Import] - [Data dictionary]
Select the new connection you created in the previous step and click [Next].
Select the schemas and click [Next].




Select the objects and click [Next].













Click [Finish]













Close the Log Screen




... Finished ...

Clone Oracle home using tar

In this blog we will show you how to clone an Oracle home to another node using tar. The Oracle home binaries are 11.2.0.3.5 on Linux 6.3. See Also: Database Installation Guide 11g Release 2 (11.2) for Linux: B Cloning an Oracle Home
 On source server as user oracle: Stop everything running in the Oracle home. Sample:
$ dbshut
$ lsnrctl stop listener
$ ps -ef|grep oracle
On source server as user root: tar the Oracle home to a location with sufficient space 
Note: We also tried this using zip, but with unzip -d all files will be wrongly owned by root
# cd /tmp
# tar -zcvf ohome_112.tar.gz /u01/app/oracle/11.2.0/db_3
On source server as user root copy tarfile to target 
Now copy the tarfile to the target server. Make sure there is enough space on the target location.
# scp ohome_112.tar.gz oracle@target.dom.org:/tmp
On target server as user rootuntar, ownerships are preserved
# cd /
# tar -zxvf /ohome_112.tar.gz
On target server as user oracle run clone.pl.
$ export ORACLE_HOME=/u01/app/oracle/11.2.0/db_3
$ export ORACLE_BASE=/u01/app/oracle
$ $ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/11.2.0/db_3" OSDBA_GROUP=dba OSOPER_GROUP=dba -defaultHomeName
[oracle@targetserver.dom.org:bin]$h)`perl': $ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/11.2.0/db_3" OSDBA_GROUP=dba OSOPER_GROUP=dba -defaultHomeName
./runInstaller -clone -waitForCompletion "ORACLE_BASE=/u01/app/oracle/" "ORACLE_HOME=/u01/app/oracle/11.2.0/db_3" "oracle_install_OSDBA=dba" "oracle_install_OSOPER=dba" -defaultHomeName -defaultHomeName -silent -noConfig -nowait 
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB. Actual 12079 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-03-27_03-30-18PM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.
You can find the log of this install session at:
/u01/app/oraInventory/logs/cloneActions2014-03-27_03-30-18PM.log
.................................................................................................... 100% Done.
 
Installation in progress (Thursday, March 27, 2014 3:30:26 PM CET)
............................................................................... 79% Done.
Install successful
Linking in progress (Thursday, March 27, 2014 3:30:30 PM CET)
Link successful
Setup in progress (Thursday, March 27, 2014 3:30:52 PM CET)
Setup successful
End of install phases.(Thursday, March 27, 2014 3:31:16 PM CET)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/u01/app/oracle/11.2.0/db_3/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/u01/app/oracle/11.2.0/db_3/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
-------------------------------------- WARNING: The following configuration scripts need to be executed as the "root" user. /u01/app/oracle/11.2.0/db_3/root.sh To execute the configuration scripts: 1. Open a terminal window 2. Log in as "root" 3. Run the scripts The cloning of OraHome6 was successful. Please check '/u01/app/oraInventory/logs/cloneActions2014-03-27_03-30-18PM.log' for more details. 
On target server as user root run root.sh
# /u01/app/oracle/11.2.0/db_3/root.sh
Check /u01/app/oracle/11.2.0/db_3/install/root_target.dom.org_2014-03-27_15-46-01.log for the output of root script
# cat /u01/app/oracle/11.2.0/db_3/install/root_target.dom.org_2014-03-27_15-46-01.log
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/11.2.0/db_3
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
On source and target server as user root: remove tarfile
# cd /tmp
# rm ohome_112.tar.gz 
rm: remove regular file `ohome_112.tar.gz'? y
On target server as user oracle: validate ownerschip and Set Bits...
$ cd $ORACLE_HOME/bin
$ ls -ltra o*
-rwxr-xr-x 1 oracle dba   3779782 Sep 17  2011 orabase
-rwxr-xr-x 1 oracle dba        46 Nov  7  2000 oracg
-rwsr-s--x 1 oracle dba 232537570 Mar 27 15:30 oracle
-rwxr-x--- 1 oracle dba 232537570 Jun  5  2013 oracleO
-rwsr-x--- 1 root   dba     71758 Sep 17  2011 oradism
-rwxr-xr-x 1 oracle dba      6183 Jan  1  2000 oraenv


... Finished ...