How to load data from Oracle DB to Postgres destination

1. Install Oracle Database Utilities: Ensure that you have Oracle Database utilities like SQL*Plus and Data Pump (expdp and impdp) installed on your Oracle server.

2. Install PostgreSQL: Make sure PostgreSQL is installed on the target server.

3. Access Credentials: Have the necessary credentials (username, password, database names, hostnames, port numbers) for both Oracle and PostgreSQL databases at hand.

1. Identify Data to Migrate: Determine which schemas, tables, or data need to be migrated.

2. Data Type Mapping: Analyze the data types used in Oracle and determine the equivalent PostgreSQL data types.

3. Character Set Considerations: Ensure that the character sets are compatible between Oracle and PostgreSQL or plan for conversion if they are not.

1. Prepare for Export: Disable any foreign keys, triggers, or other dependencies that might interfere with the export process.

2. Export with SQL*Plus:

- Connect to Oracle using SQL*Plus:

```shell

sqlplus username/password@//hostname:port/SID

```

- Spool the data to a flat file:

```sql

SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SPOOL /path/to/exported_data.txt

SELECT * FROM schema_name.table_name;

SPOOL OFF

EXIT

```

- Repeat the above step for each table you wish to export.

1. Create Database and Schema: If not already present, create the database and schema in PostgreSQL.

2. Create Tables: Based on the data type mapping, create the corresponding tables in PostgreSQL with appropriate data types.

3. Adjust PostgreSQL Settings: Modify `postgresql.conf` if necessary to increase settings like `max_allowed_packet` to accommodate large data imports.

1. Prepare for Import: Disable triggers, foreign keys, and indexes in PostgreSQL to speed up the import process.

2. Import Using psql:

- Connect to PostgreSQL using psql:

```shell

psql -U username -d database_name -h hostname -p port

```

- Use the COPY command to import data:

```sql

\COPY schema_name.table_name FROM '/path/to/exported_data.txt' WITH (FORMAT csv, DELIMITER '|', NULL 'NULL');

```

- Repeat the above step for each exported file corresponding to a table.

1. Check Row Counts: Compare the row counts in both Oracle and PostgreSQL to ensure they match.

2. Check Data Consistency: Run sample queries on both databases to verify that the data is consistent.

3. Re-enable Constraints: Re-enable foreign keys, triggers, and indexes in PostgreSQL and validate them.

1. Performance Tuning: Analyze the imported tables and run `VACUUM ANALYZE` to update statistics for the PostgreSQL query planner.

2. Test Applications: Update your application connection strings and thoroughly test to ensure that the applications work as expected with the new PostgreSQL database.

3. Backup: Take a backup of the PostgreSQL database after the migration is confirmed to be successful.

Additional Tips

- Always perform the migration first on a test environment before applying it to production.

- For large datasets, consider using PostgreSQL's `pg_dump` and `pg_restore` utilities with custom scripts to handle data type conversion.

- Thoroughly document the migration process, including any data type transformations and issues encountered.

Remember, this is a high-level guide, and actual migration may involve additional complexities depending on the specific use case and data involved. Always ensure you have a backup and recovery strategy in place before beginning any migration.