Changes between Version 1 and Version 2 of OracleToPg

Timestamp:

03/26/14 12:59:35 (10 years ago)

Author:

niles

Comment:

--

OracleToPg

@@ -29 +29 @@
 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
 
-While it is theoretically possible to dump all the contents of an Oracle database, in practice it's probably best done one part at a time, 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).
 
 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 :
@@ -75 +75 @@
 
 This creates the table in postgres. Any cron-driven scripts that were updating that table in realtime - "spiders" - must also be modified so that they use the postgres database rather than the Oracle one.
+
+It is also possible, for '''INSERT''' SQL exports, to skip the generation of intermediate .psql files used in the examples above and connect directly to a postgres database, although I have not tried this. This involves setting the '''PG_DSN''', '''PG_USER''' and '''PG_PWD''' settings in the ora2pg.conf file. '''NOTE''' that you can use an alternate configuration file with the '''--conf''' argument on the command line, so you can try this without disturbing the default configuration.
+
+ora2pg has a man page and is documented somewhat at its web site. Help is also available by running '''ora2pg --help''' and the file /etc/ora2pg/ora2pg.conf is pretty well documented.