Oracle Database Appliance (ODA) Installation, Configuration and Deployment Steps

The Oracle Database Appliance saves time and money by simplifying deployment, maintenance, and support of database solutions for organizations of every size. Optimized for the world’s most popular database–Oracle Database–it integrates software, compute, storage, and network resources to deliver database services for a wide range of custom and packaged online transaction processing (OLTP), in-memory database, and data warehousing applications. All hardware and software components are engineered and supported by Oracle, offering customers a reliable and secure system with built-in automation and best practices. In addition to accelerating the time to value when deploying database solutions, the Oracle Database Appliance offers flexible Oracle Database licensing options and reduces operational expenses associated with maintenance and support.

All Oracle Database Appliance Models are optimized to run Oracle Database Standard Edition and Enterprise Edition. The Oracle Database Appliance X7-2S and Oracle Database Appliance X7-2M are ideal for customers who require single instance databases on an engineered platform. The Oracle Database Appliance X7-2-HA is ideal for customers who are looking to consolidate multiple databases onto a highly available and scalable-engineered platform.

In this blog post, I want to write step by step documentation for Oracle Database Appliance (ODA) Installation, Configuration and Deployment. The installation of Oracle Database Appliance (ODA) is very easy and done by the ODA configuration manager. After filled out all the necessary information in the ODA configuration manager screens, you will click the “install” button, and the installation progress will start. It is 33 steps and each of them executes some commands/scripts in the background.  You can see all the detailed status of each steps in the ODA configuration manager screen.

ODA is x86 based engineered system. The minimum SW release for ODA X6-2 is version 12.1.2.7.0. The Oracle Database Appliance X6-2 can only be deployed as a Non-Virtualized (Bare Metal) Configuration. The Bare Metal image will be installed by the factory. For the latest ISO images, check My Oracle Support, Patches & Updates tab. The ODA X6-2 images are listed under patches for Oracle Database Appliance Software, and clearly marked for ODA X6-2. Since you will not know which version is installed on the ODA before proceeding with the installation it is recommended to download all the patches that might be needed to save a lot of download time whilst at the customer site. It is strongly recommended that you access MOS Document ID 888888.1 which contains links to the above patches. The downloaded files can be copied onto a USB stick for access by the system.

DOWNLOADING NECESSARY PATCHES

Download the following files (where xxxxxx represents the current version
number e.g. 121280 for ODA SW 12.1.2.8.0):
• p23530609_xxxxxx_Linux-x86-64.zip – ODA ISO Bare Metal Restore
file – only be necessary if a complete re-image is required.
• p23494985_xxxxxx_Linux-x86-64.zip – (two parts 1of 2 and 2of 2) GI
Clone files. This file is required for deployment.
• p24391219_121280_Linux-x86-64.zip – ODA Patch Bundle
Depending on the version of the database that the customer would like to
provision, you can download both of them, or either one of the following two
patches:
• p23494992_xxxxxx_Linux-x86-64.zip – (two parts 1of 2 and 2of 2)
These clone files are for creating a new database home running
12.1.0.2.160719 DBBP. (DBBP – Database Bundle Patch).
• p23494997_xxxxxx_Linux-x86-64.zip
These clone files are for creating a new database home running
11.2.0.4.160719 DBBP. (DBBP – Database Bundle Patch).

If you are using a KVM or directly attached monitor and keyboard, the ILOM may be configured using the web configurator. This will save the task of connecting to the serial port to configure the ILOM net management port manually. When the SP initially boots it will attempt to obtain an IP address from a DHCP server. You must have a cable connected to the SP Network Management port (NET MGT). If DHCP server is available you can connect to the SP via network (ssh or https), otherwise you have to connect to the SP via a serial line (see below) and specify a static Ethernet configuration.

Obtain the Service Processor IP address from one of the following locations. Record the IP address for future reference:
• The system BIOS setup screen.
• Command-line interface – connect to serial port via terminal device (such as a laptop). Log into the SP via CLI and display the IP address.

This will be the address assigned by the DHCP server.

Advanced → BMC Network Configuration →Current Ipv4 address in BMC

SERVER POWER ON

Power on the server. There are several ways to do this:
• Press the power-on button on the front panel (with clock symbol).
• From the CLI on the SP issue: start /SYS

-> start /SYS

• On the ILOM GUI expand the Host Management link in the left column then select Power Control. In the Main panel you will see Host is currently off Select Power On and then save.

If using CLI: gain host console access. To stop the host console press ESC followed by ( i.e. opening round bracket. To leave the secure shell: exit

From server’s SP:

-> start /HOST/console

At the OS login prompt:User root with welcome1 as password.

CONFIGURING ILOM VIA CLI AND A SERIAL CONNECTION

To set a static IP address for the SP using the CLI and a serial line connection perform the steps below.

Log in to the ILOM SP.

User = root
Pwd = changeme

login as: root
Using keyboard-interactive authentication.
Password:
Oracle(R) Integrated Lights Out Manager
Version 3.2.7.26 r112010
Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
Warning: password is set to factory default.
Warning: HTTPS certificate is set to factory default.
Hostname: oda-x6-m-ilom

-> ls

/
Targets:
HOST
System
SP

Properties:

Commands:
cd
show

-> cd SP
/SP

-> ls

/SP
Targets:
alertmgmt
cli
clients
clock
config
diag
faultmgmt
firmware
logs
network
policy
powermgmt
preferences
serial
services
sessions
users

Properties:
check_physical_presence = true
current_hostname = oda-x6-m-ilom
hostname = oda-x6-m-ilom
reset_to_defaults = none
system_contact = (none)
system_description = ORACLE SERVER X6-2, ILOM v3.2.7.26, r112010
system_identifier = Oracle Database Appliance X6-2 Medium 1639NM10PK
system_location = (none)

Commands:
cd
reset
set
show
version

-> cd network
/SP/network

-> ls

/SP/network
Targets:
interconnect
ipv6
test

Properties:
commitpending = (Cannot show property)
dhcp_clientid = none
dhcp_server_ip = none
ipaddress = 192.168.3.101
ipdiscovery = static
ipgateway = 192.168.3.1
ipnetmask = 255.255.255.0
macaddress = 00:10:E0:BD:C8:20
managementport = MGMT
outofbandmacaddress = 00:10:E0:BD:C8:20
pendingipaddress = 192.168.3.101
pendingipdiscovery = static
pendingipgateway = 192.168.3.1
pendingipnetmask = 255.255.255.0
pendingmanagementport = MGMT
pendingvlan_id = (none)
sidebandmacaddress = 00:10:E0:BD:C8:21
state = ipv4-only
vlan_id = (none)

Commands:
cd
set
show

-> set pendingipdiscovery = static
-> set pendingipaddress = 192.168.3.101
-> set pendingipgateway = 192.168.3.1
-> set pendingipnetmask = 255.255.255.0
-> set commitpending = true
-> set state = enabled
-> ls

–Booting
start /SYS
or
Press the power-on button on the front pane

Note that the commitpending command requires several seconds to complete. It is also possible to configure ILOM network settings via the system BIOS in the Advanced Menu tab under BMC Network.

Verify the changes.

-> show /SP/network

CONFIGURING ILOM VIA GUI & ETHERNET CONNECTION

To set a static IP address for the SP using the GUI perform the steps below.

1- Determine the current IP address of the SP using one of the methods described at the top of this page. For this to work IP assignment by DHCP must have succeeded!

2- Connect to the SP via a web browser running on a remote system (Firefox or Internet Explorer).

3- Log into the web GUI using the default user name & password.

https://192.168.3.101
User = root
Pwd = changeme

4- Select ILOM Administration and then Connectivity to display information about the current network configuration of your ILOM. The network configuration can be modified then press the Save button.

CONFIGURING THE NETWORK INTERFACE

Run the command configure-firstnet. Respond to the prompts to configure the public network interface. Also provide the netmask and gateway IP address.

[root@oak ~]# configure-firstnet
Select the Interface to configure the network on (btbond1 btbond2
sfpbond1) [btbond1]:
Configure DHCP on btbond1 (yes/no) [no]:
INFO: You have chosen Static configuration
Enter the IP address to configure : 192.168.3.102
Enter the Netmask address to configure : 255.255.252.0
Enter the Gateway address to configure[192.168.3.1] :
INFO: Plumbing the IPs now
INFO: Restarting the network
Shutting down interface btbond1: [ OK ]
Shutting down interface btbond2: [ OK ]
Shutting down interface p3p1: [ OK ]
Shutting down interface p3p2: [ OK ]
Shutting down interface sfpbond1: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface btbond1: Determining if ip address
192.168.3.102 is already in use for device btbond1…
[ OK ]
Bringing up interface btbond2: [ OK ]
Bringing up interface sfpbond1: [ OK ]

To verify the current ODA system version, run the following command:

[root@oak ~]# cat /opt/oracle/oak/pkgrepos/System/VERSION
version=12.1.2.8.0

To re-image with a newer version, you will require the Bare Metal ISO image. The file can be unzipped on a Linux.
# unzip p23530609_xxxxxx_Linux-x86-64.zip

UPLOADING PATCHES TO THE SERVER NODE

Now that you have an active network interface, you can use scp/ftp/filezilla to transfer the following files to the server node.

p23494997_121280_Linux-x86-64.zip –> 11.2.0.4 RDBMS
p23494992_121280_Linux-x86-64.zip –> 12.1.0.2 RDBMS
p23494985_121280_Linux-x86-64_2of2.zip –> grid
p23494985_121280_Linux-x86-64_1of2.zip –> grid
p23530609_121280_Linux-x86-64 –> OS image

UNPACK/CONCATENATE AND INSTALL THE GI PATCH

[root@oak ~]# unzip p23494985_121280_Linux-x86-64_1of2.zip
[root@oak ~]# unzip p23494985_121280_Linux-x86-64_2of2.zip
[root@oak A]# ls
oda-sm-12.1.2.8.0-160817-GI-12.1.0.2_1of2.zippart p23494985_121280_Linux-x86-64_1of2.zip p23494992_121280_Linux-x86-64.zip README.html
oda-sm-12.1.2.8.0-160817-GI-12.1.0.2_2of2.zippart p23494985_121280_Linux-x86-64_2of2.zip p23494997_121280_Linux-x86-64.zip
[root@oak A]# cat oda-sm-12.1.2.8.0-160817-GI-12.1.0.2_1of2.zippart oda-sm-12.1.2.8.0-160817-GI-12.1.0.2_2of2.zippart > GI.zip

Apply the update:

[root@oak A]# update-image –image-files GI.zip
Unpacking will take some time, Please wait…
Unzipping GI.zip

UNPACK/CONCATENATE AND INSTALL THE DATABASE PATCH

There are 2 database versions that can be provisioned:
• 12.1.0.2.160719 DBBP (database bundle patch)
• 11.2.0.4.160719 DBPSU (database PSU)
Depending on the version of the database that you would like to provision, you can install both, or either one of them. The below example is for 12.1.0.2, which only has one file and did not need to be concatenated like the GI patch. If the patch comes with two files, they must first need to be concatenated before installing with the image-update command:

[root@oak ~]# unzip p23494992_121280_Linux-x86-64.zip
[root@oak ~]# update-image –image-files oda-sm-12.1.2.8.0-160817-DB-12.1.0.2.zip
Unpacking will take some time, Please wait…
Unzipping oda-sm-12.1.2.8.0-160817-DB-12.1.0.2.zip

DEPLOYING THE ORACLE DATABASE APPLIANCE

You are now ready to deploy the Oracle Database Appliance. Using the Chrome or IE browser, enter the following URL:

1- https://192.168.3.102:7093/mgmt/index.html
Username: oda-admin
Password (default):welcome1

2- On the first screen, click on Create Appliance

3- Mandatory fields have  star sign *. The hostname can contain alphanumeric characters and dashes (-). The hostname cannot exceed 63 characters. The password must begin with an alpha character and cannot contain quotation marks. The password cannot exceed 30 characters.

4- Write Client Access Network inf.

5- Database information details

6- If Yes, ASR User Name and Password is required. If Yes to HTTP Proxy, the Proxy Server Name and Proxy Port is required. If Yes to HTTP Proxy Requires Authentication, Proxy User Name and Proxy Password is required. Click NO and Submit button.

7- Select Yes

8- Provisioning job will submitted. Click the link to display job details.

9-  Provisioning service creation steps. To monitor deployment progress, you can monitor the dcs-agent.log file:

# tail -f /opt/oracle/dcs/log/dcs-agent.log

If there is an error during deployment, we will see it in the dcs-agent.log file

It is ready to use.

[root@veridata ~]# su – oracle
oracle@ veridata /home/oracle>
oracle@ veridata /home/oracle>
oracle@ veridata /home/oracle> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 22:22:08 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,log_mode,open_mode from v$database;

NAME LOG_MODE OPEN_MODE
——— ———— ——————–
VERIDATA ARCHIVELOG READ WRITE

SQL> select name from v$datafile;

NAME
——————————————————————————–
+DATA/VERIDATA/DATAFILE/system.261.968623315
+DATA/VERIDATA/DATAFILE/sysaux.260.968623281
+DATA/VERIDATA/DATAFILE/undotbs1.259.968623279
+DATA/VERIDATA/DATAFILE/users.262.968623351

[root@veridata ~]# su – grid
grid@ veridata /home/grid>
grid@ veridata /home/grid> asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 4194304 4894016 4879956 0 2434880 0 Y DATA/
MOUNTED NORMAL N 512 4096 4194304 1231176 1218588 0 604180 0 N RECO/
grid@ veridata /home/grid>

[root@veridata ~]# /opt/oracle/dcs/bin/odacli list-databases

ID DB Name DB Version CDB Class Shape Storage Status
—————————————- ———- ——————– ———- ——– ——– ———- ———-
e2292352-b538-4d79-8a70-e1cc9a0c18b7 VERIDATA 12.1.0.2 false OLTP odb6 ASM Configured

[root@veridata ~]# /opt/oracle/dcs/bin/odacli describe-appliance

Appliance Information
—————————————————————-
ID: 71358f0e-d684-44f0-9e74-d8436e0a6778
Platform: OdaliteM
Data Disk Count: 2
CPU Core Count: 20
Created: February 20, 2018 9:39:24 PM EET

System Information
—————————————————————-
Name: veridata
Domain Name:
Time Zone: Europe/Istanbul
DB Edition: EE
DNS Servers: 192.168.3.29
NTP Servers: 192.168.3.29

Disk Group Information
—————————————————————-
DG Name Redundancy Percentage
————————- ————————- ————
Data Normal 80
Reco Normal 20

[root@veridata ~]#

Advertisements

How to Rewind an OPEN RESETLOGS Operation with Flashback Database?

Hi,

Steps for using Flashback Database to reverse an unwanted “ALTER DATABASE OPEN RESETLOGS” statement is similar to “FLASHBACK DATABASE”. You will use “FLASHBACK DATABASE TO BEFORE RESETLOGS” statement. Of course you must enable flashback database before performing “ALTER DATABASE OPEN RESETLOGS” operation.

To undo an OPEN RESETLOGS operation:

1. Connect SQL*Plus to the target database and verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS. Run the following queries:

SELECT RESETLOGS_CHANGE#
FROM V$DATABASE;

SELECT OLDEST_FLASHBACK_SCN
FROM V$FLASHBACK_DATABASE_LOG;

If RESETLOGS_CHANGE# is greater than OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS operation

2. Shut down the database,

SHUTDOWN IMMEDIATE;

3. mount it

STARTUP MOUNT;

4. Recheck the flashback window. If the resetlogs SCN is still within the flashback window, then proceed to the next step.
Connect RMAN to the target database.
rman target /

5. Perform a flashback to the SCN immediately before the RESETLOGS. You can use the following FLASHBACK DATABASE command:

FLASHBACK DATABASE TO BEFORE RESETLOGS;

As with other uses of FLASHBACK DATABASE (until SCN, until TIME, etc.), if the target SCN is before the beginning of the flashback database window, an error is returned and the database is not modified. If the command completes successfully, then the database is left mounted and recovered to the most recent SCN before the OPEN RESETLOGS operation in the previous incarnation.

6. Open the database read-only in SQL*Plus and perform queries to ensure that the effects of the logical corruption have been reversed.

ALTER DATABASE OPEN READ ONLY;

7. To make the database available for updates again, shut down the database, mount, and open the database with RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

ORA-28043: invalid bind credentials for DB-OID connection

In this article, I will write about resolving ORA-28043 error. Our databases integrated with Oracle Internet Directory and registered in OID under domain dc=tholdap,dc=local
Some users in my company reported that they can’t login to database using OID user accounts. They get ORA-28043 error when attempt to login database. In this situation, first thing to do is enable a trace for this error in database as below.

sqlplus / as sysdba

SQL> alter system set events ‘28033 trace name context forever, level 9’;

Try to reconnect with OID user account and get ORA-28043 error again.

Go trace file location under directory $ORACLE_BASE/diag/rdbms/$SID/$SID/trace

The sample trace file contains following lines.

vi /u01/app/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_ora_9951.trc

ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: SunOS
Node name: dbtest
Release: 5.10
Version: Generic_150400-09
Machine: sun4v
Instance name: TESTDB
Redo thread mounted by this instance: 1
Oracle process number: 2610
Unix process pid: 9951, image: oracle@dbtest

*** ACTION NAME:() 2016-02-24 09:04:40.463
*** MODULE NAME:(Toad.exe) 2016-02-24 09:04:40.463
*** SERVICE NAME:(TESTDB) 2016-02-24 09:04:40.463
*** SESSION ID:(1212.44043) 2016-02-24 09:04:40.463
kzld_discover received ldaptype: OID
kzld found pwd in wallet
KZLD_ERR: Failed to bind to LDAP server. Err=49
KZLD_ERR: 49
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.
~
~

According to trace file, the error message is related to binding to LDAP Server.

kzld_discover received ldaptype: OID
kzld found pwd in wallet
KZLD_ERR: Failed to bind to LDAP server. Err=49
KZLD_ERR: 49
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.

Let me check, if I can connect to ldap server using username and password stored in database wallet.

Now, there is a question: How can we find the username and password stored in our database wallet ?

$ mkstore -wrl $ORACLE_BASE/admin/TESTDB/wallet -viewEntry ORACLE.SECURITY.DN
Oracle Secret Store Tool : Version 11.2.0.3.0 – Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: abcd678xx_

ORACLE.SECURITY.DN = cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local
$ mkstore -wrl $ORACLE_BASE/admin/TESTDB/wallet -viewEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool : Version 11.2.0.3.0 – Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: abcd678xx_

ORACLE.SECURITY.PASSWORD = +HKRbmQ7

mkstore will ask you for wallet password which you entered during database OID registration.

Now, we have the username and password as below.

ORACLE.SECURITY.PASSWORD = +HKRbmQ7
ORACLE.SECURITY.DN = cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local

Now it is needed to verify database connection to LDAP Server.

First let me get port number from ldap.ora file located in directory $ORACLE_HOME/network/admin.

$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/ldap.ora
# ldap.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/ldap.ora
# Generated by Oracle configuration tools.

DIRECTORY_SERVERS= (idmoid.vodafone.local:1389:1636)

DEFAULT_ADMIN_CONTEXT = “dc=tholdap,dc=local”

DIRECTORY_SERVER_TYPE = OID

Run ldapbind command to check connection to LDAP Server.

$ ldapbind -h idmoid.vodafone.local -p 1389 -D cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local -w kSlIt+n2
ldap_bind: Invalid credentials

So the problem was due to Invalid credentials. All right! How we can solve this issue?

There is two way:

1- Go to OID and update password for cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local at attribute userPassword with value received from ORACLE.SECURITY.PASSWORD

2- Re-register the database using DBCA as below

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=dirManager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

dbca -silent -configureDatabase -sourceDB TESTDB -registerWithDirService true -dirServiceUserName cn=dirManager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

After re-registering database, ldapbind will be successful

$ ldapbind -h idmoid.vodafone.local -p 1389 -D cn=TESTDB,cn=OracleContext,dc=tholdap,dc=local -w +HKRbmQ7
bind successful

Now, users can login database successfully.

I want to note that, if there is spaces in your CN (Directory Service username) then you must write \20 instead of spaces.

For example: Lets assume that our directory service username is “directory manager”. So we must write it as below in DBCA

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=directory\20manager -dirServicePassword OracleTHO11 walletPassword abcd678xx_

You will get an error without “\20” as below

dbca -silent -configureDatabase -sourceDB TESTDB -unregisterWithDirService true -dirServiceUserName cn=directory manager -dirServicePassword OracleTHO11 walletPassword abcd678xx_
manager is an invalid command line argument.

Migration From SQL Server To Oracle Using SQL Developer

Migration is the process of copying the schema objects and data from a non-Oracle database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, to an Oracle database.

To migrate a third-party database to Oracle easily, you can choose the following options using SQL Developer:

– Migrating using the Migration Wizard

– Copying tables to Oracle

Migrating Using the Migration Wizard

The Migration wizard provides a screen to manage all the steps needed for the migration to Oracle database. These steps are as follows:

– Capturing the source database (MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, IBM DB2),

– Converting it to Oracle format,

– Generating DDL,

– Performing the conversion.

After a brief explanation about the process of migration, let’s do a simple migration from SQL Server database to Oracle database.

1- I created sample SQL Server database named TALIPTEST.

2- The migration repository is a collection of schema objects that SQL Developer uses to manage metadata for migrations. For a migration repository create a database connection to convenient Oracle database and give following grants.

 CREATE USER MIGRATIONS IDENTIFIED BY “migration”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

grant create session to migrations;

grant resource to migrations;

grant create view to migrations;

For multischema migrations, you must grant the privileges with the ADMIN option as follows.

grant resource to migrations with admin option;

grant create role to migrations with admin option;

grant alter any trigger to migrations with admin option;

grant create user to migrations with admin option;

3- Download SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

4- Unzip the file named sqldeveloper-3.2.20.09.87.zip and extract it. Open  sqldeveloper.exe file to open SQL Developer.

5- Right click to the “Connections” and click the “New Connections”.

6-  Create a database connection named Migration_Repository that connects to the MIGRATIONS user.

7- Right-click the Migration_Repository connection, and select “Migration Repository” menu , then “Associate Migration Repository” to create the repository.

8- Schema objects needed for migration will be created in MIGRATIONS schema.

9- To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . Extract the dowloaded zip file named jtds-1.2-dist.zip

10- Click “Tools” and then “Prefenrences”.

11- Select “Third Party JDBC Drivers” and click “Add Entry” button to add jTDS driver for SQL Server.

12- Select jar file located in jTDS driver folder.

13- Click “OK” button.

14- Now you can connect to SQL Server or Sybase database using SQL Developer. Connect to the SQL Server database as follows.

15- And finally, let us create the database connection which we will migrate to.

16- The migration wizard is invoked in a variety of contexts. You can right-click a third-party database connection and select “Migrate to Oracle”  or you can click “Tools>Migration>Migrate…”.

17- Click “Next” button.

18- Select migration repository database connection.

19- Give a name for migration project and select a directory to write output.

20– Select third party database connection. You can migrate database on online or offline mode. If you choose online migration then Migration Wizard will perform all needed operations. If you choose offline migration then Migration Wizard will generate all needed DDL scripts.

21- Select SQL Server database which we want to migrate to Oracle.

22- Specify the conversion options. And click “Advanced Options” link to ensure “Microsoft SQL Server : Is quoted identifier on” option is selected.

23- Select target database connection.

24- If you select offline migration then offline migration script will be generated in the project output directory.

25- Select the connections to be used for online data move.

26- Click “Finish” button so start migration.

27- Migration and conversion process will be perfomed as follows.

Now, Our SQL Server database in Oracle 🙂

Copying tables to Oracle

Right click the table located in third party database and click “Copy To Oracle”

Select destination (Oracle) database connection and click “Apply” button. If you choose “Include Data” check box it will create table structure and move data.

Copy to Oracle database is finished.

Note: This method doesn’t move indexes, triggers, etc. It only moves table structure and data.

Talip Hakan Öztürk

My Second ORACLE Book is Published in Turkish…

In this book,  I addressed the DBA tasks  in separate sections such as Monitoring, RMAN backup and recovery, Oracle ASM, Data Guard, Database Security, and Version upgrade.

The best method of learning is the practical learning. With this in mind I gave particular importance to the screen shots. Thus, you can do practic by following the book.

Comments and suggestions about the book is very valuable for me. You can send your comments and suggestions to my e-mail address talip_hakan_ozturk@hotmail.com

Hope to get the most from my Oracle books…

http://www.idefix.com/kitap/oracle-database-11g-r2-ileri-veritabani-yonetimi-talip-hakan-ozturk/tanim.asp?sid=Y6RLXNTFSV6V5T0ZZV0V

Talip Hakan Öztürk

Oracle ACE, 10g/11g OCP