How Informix data is imported into the Oracle database

Objective:

Different types of database interoperability data, due to different database mechanisms, can not be achieved through simple copy and paste, but also due to different file formats, can not simply export files, import database.

About Informix Export data, import to Oracle method, after experiment can be used as follows: (although a bit stupid, but for small-volume requirements, can work ^_^)

(Once used Perl script implementation, but because of debugging Perl environment and the dependency of PL a bit of trouble, failed to pass smoothly, the stupid method is recorded as follows)

1. Export data to UNL file format in Informix

Unload to '/INFORMIX/SYSTEM_SETUP20150617.UNL ' select * from System_setup;

Dbschema-d nmosdb-t Tfa_alarm_relation–ss

2 Making CTL control files Circuit.ctl

Load data

infile ' CIRCUIT20150610.UNL ' --exported unl file

into table circuit --Imported table name

Fields terminated by ' | ' --Separators

TRAILING nullcols --List all fields in a table

(Object_class char (10000),

Resourcesid Char (10000),

int_id Char (10000),

Circuitno Char (10000),

......

Bnodeport_rdn Char (10000),

Localnode_rdn Char (10000),

Romotenode_rdn char (10000))

3 Configure the environment variables that the SQLLDR command can use

User Environment variables:

Oracle_unqname=oracle

oracle_home=/opt/oracle/product/11g

Ora_nls10=/opt/oracle/product/11g/nls/data
Oracle_sid=orcl
Oracle_base=/opt/oracle

Path=/opt/oracle/product/11g/bin:/usr/sbin

Ld_library_path=/opt/oracle/product/11g/lib:/lib:/usr/lib

Oracle Client Configuration:

Tnsnames.ora

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.0.9) (PORT = 1521)) (Connect_data =       (SERVER = dedicated)   (service_name = ORCL)) )

Sid_list_listener = (Sid_list = (Sid_desc = (Global_dbname = orcl) (Oracle_home =/opt/a pp/oracle/product/11.2.0/db_1) (Sid_name = orcl)))

Listener.ora

LISTENER = (Description_list = (DESCRIPTION = (address = (PROTOCOL = IPC) (KEY = EXTPROC1521)) (address = (PROTOCOL = TCP) (HOST = 10.10.0.9)     (PORT = 1521)) )   )

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = Plsextproc)

(Oracle_home = /opt/oracle/product/11g)

(program = Extproc)

(envs= "Extproc_dlls=any")

)

(Sid_desc =

(Global_dbname = ORCL)

(Oracle_home = /opt/oracle/product/11g)

(Sid_name = ORCL)

)

)

Sqlldr database user name/password @ Instance name Test

4 Execute command, import file

Sqlldr Userid=nrmdb/[email protected] control=/opt/informix/data/circuit.ctl

Data=/OPT/INFORMIX/DATA/CIRCUIT20150610.UNL

Additional:

Informix Date fields are imported into Oracle and cannot be imported directly because they are not of the same type

A relay was made through a field.

For example, the Informix date field is time_stamp;

Create a TIME_STAMP1 varchar (255) Date field in Oracle to receive Informix;

Also creates a time_stamp date in Oracle for converting date fields from varchar to date in Oracle

Update circuit Set time_stamp=to_date (TIME_STAMP1, ' yyyy-mm-dd hh24:mi:ss ')

Delete the over field time_stamp1.

The type conversion is complete.

Conclusion:

In the age of DBA scarcity, people who can put their hands on the ground are more scarce ... Do more hands-on practice, and then discuss what is feasible and persuasive

Because, only in the process of practice, can encounter a variety of problems, need to go to conquer it ~ ~ ~

Come on, ~~~trouble shooter ^_^.

How Informix data is imported into the Oracle database