Changes between Version 2 and Version 3 of OracleToPg

Timestamp:

03/26/14 13:03:31 (11 years ago)

Author:

niles

Comment:

--

OracleToPg

@@ -22 +22 @@
 Typically the following settings need to be changed :
 
-1. ORACLE_HOME - While I'm not 100% certain that this needs to be set, it can't hurt, and it's easy to do - go to the machine on which Oracle is installed and look at the file /etc/oratab
-1. ORACLE_DSN  - Typically set to "dbi:Oracle:"
-1. ORACLE_USER needs to be set to the user who accesses the database
-1. ORACLE_PWD needs to be set to their password
-1. The line reading "#WHERE 1=1" needs to be un-commented so it reads "WHERE 1=1" (ie. delete the "#")
-1. Depending on your version of postgres, you may need to set PG_SUPPORTS_MVIEW to "0", although this may not be very relevant for our work
+1. '''ORACLE_HOME''' - While I'm not 100% certain that this needs to be set, it can't hurt, and it's easy to do - go to the machine on which Oracle is installed and look at the file '''/etc/oratab'''
+1. '''ORACLE_DSN'''  - Typically set to "'''dbi:Oracle:'''"
+1. '''ORACLE_USER''' needs to be set to the user who accesses the database
+1. '''ORACLE_PWD''' needs to be set to their password
+1. The line reading "'''#WHERE 1=1'''" needs to be un-commented so it reads "'''WHERE 1=1'''" (ie. delete the "'''#'''")
+1. Depending on your version of postgres, you may need to set '''PG_SUPPORTS_MVIEW''' to "0", although this may not be very relevant for our work
 
-Once ora2pg is configured, it is theoretically possible to dump all the contents of an Oracle database, but in practice it's probably best done one part at a time (incrementally) because that allows for more stringent checks to be done. It also allows for tables that are continually being updated in Oracle to be moved to postgres as the means to update them in postgres becomes available. This is done with the '''--allow''' argument when running ora2pg (see the bash script below).
+Once ora2pg is configured, it is theoretically possible to dump all the contents of an Oracle database, but in practice it's probably best done one part at a time (incrementally) because that allows for more stringent checks to be done. It also allows for tables that are continually being updated in Oracle to be moved to postgres as the means to update them in postgres becomes available. This is done with the '''--allow''' argument when running ora2pg (see the bash script below). This specifies a comma separated list of entities to export.
 
 For example, in Oracle there is a table named "'''NOAA_ACTIVE_REGION_LOCATIONS'''". There are also three Oracle procedures that are used to update this table, somewhat unimaginatively named "'''UPDATE_ACTIVE_REGION'''", "'''UPDATE_ACTIVE_REGION2'''" and "'''UPDATE_ACTIVE_REGION3'''". To extract this into the postgres database, we use ora2pg to extract the SQL to create the table, then the SQL to populate the table using '''INSERT''' statements (which can be a large SQL file) and finally the three procedures. This can be done in a bash script as follows :