Dbagley: /* Summary Table */

2025-05-28T10:54:28Z

‎Summary Table

Dbagley: Created page with "= Specification: Oracle Data Pump Schema Migration Between AWS Instances = == 1. Prerequisites == * '''Source and target Oracle databases''' running in AWS (RDS, EC2, or sim..."

2025-05-28T10:42:33Z

Created page with "= Specification: Oracle Data Pump Schema Migration Between AWS Instances = == 1. Prerequisites == * '''Source and target Oracle databases''' running in AWS (RDS, EC2, or sim..."

New page

= Specification: Oracle Data Pump Schema Migration Between AWS Instances =

== 1. Prerequisites ==

* '''Source and target Oracle databases''' running in AWS (RDS, EC2, or similar).
* '''Network connectivity''' between your admin workstation and both databases (or work entirely on AWS EC2 if using that).
* '''Admin privileges''' to create and use DIRECTORY objects on both source and target.
* '''Oracle client tools (`expdp`, `impdp`)''' installed where you run the export/import (on an EC2 server or on your local workstation if it can connect).
* '''Sufficient disk space''' on both source and target for the dump files.

----

== 2. Create a DIRECTORY Object ==

You need a DIRECTORY object in both source and target databases.

On each database, as a DBA:

<pre>
CREATE OR REPLACE DIRECTORY dpdir AS '/tmp/dpdump';
GRANT READ, WRITE ON DIRECTORY dpdir TO your_db_user;
</pre>

* The <code>/tmp/dpdump</code> directory must exist and be writable by the Oracle OS user if using EC2.
* '''On Amazon RDS:''' You can only use pre-defined directories like <code>DATA_PUMP_DIR</code>.

----

== 3. Export the Schema with Data Pump (`expdp`) ==

On the Source DB:

<pre lang="bash">
expdp your_db_user/your_password@source_db_service \
schemas=YOUR_SCHEMA \
directory=DPDIR \
dumpfile=your_schema_2024.dmp \
logfile=your_schema_2024_exp.log
</pre>

* If using '''Amazon RDS''', connect using the endpoint and use <code>DATA_PUMP_DIR</code> as the directory.

----

== 4. Retrieve the Dump File ==

=== A. On EC2-hosted Oracle: ===
* The dump file is on the EC2 server’s <code>/tmp/dpdump</code> directory.
* Use <code>scp</code> or <code>sftp</code> to transfer the file to your workstation, or directly to the target server.

=== B. On Amazon RDS: ===
* Use the AWS console or [https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html RDS procedures] to copy the file to and from an S3 bucket:
** Use the <code>rdsadmin.rdsadmin_util</code> PL/SQL package to copy files between <code>DATA_PUMP_DIR</code> and S3.
** Example:
<pre>
BEGIN
rdsadmin.rdsadmin_util.upload_to_s3(
p_directory => 'DATA_PUMP_DIR',
p_s3_prefix => 's3://your-bucket/dumps/',
p_filename => 'your_schema_2024.dmp'
);
END;
/
</pre>
** To import, use <code>download_from_s3</code> on the target RDS instance.

----

== 5. Import the Schema on the Target DB ==

=== A. Make the dump file available in the target's dump directory: ===
* On EC2: Copy file into the target’s dump directory (e.g., <code>/tmp/dpdump</code>).
* On RDS: Download from S3 to <code>DATA_PUMP_DIR</code> as above.

=== B. Grant necessary permissions to the import user: ===

<pre>
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, ... TO your_db_user;
GRANT READ, WRITE ON DIRECTORY dpdir TO your_db_user;
</pre>

=== C. Run the Import (`impdp`): ===

<pre lang="bash">
impdp your_db_user/your_password@target_db_service \
schemas=YOUR_SCHEMA \
directory=DPDIR \
dumpfile=your_schema_2024.dmp \
logfile=your_schema_2024_imp.log
</pre>

----

== 6. Post-Import Tasks ==

* Recompile invalid objects if needed:
<pre>
EXEC DBMS_UTILITY.compile_schema('YOUR_SCHEMA');
</pre>
* Check object counts, grants, and data.
* Resolve any external dependencies (DB links, directory paths, etc.).

----

== 7. Clean Up ==

* Remove dump/log files from <code>/tmp</code> or S3 as appropriate.
* Drop DIRECTORY objects if no longer needed (optional).

----

== Special Notes for AWS RDS ==

* Use only Amazon RDS allowed directories (<code>DATA_PUMP_DIR</code>).
* Use '''S3 integration''' for file movement.
* Procedures: [https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html RDS Oracle Data Pump and S3]

----

== Summary Table ==

{| class=\"wikitable\"
|-
! Step
! EC2-Hosted Oracle
! Amazon RDS Oracle
|-
| Directory
| Custom path (e.g. /tmp/dpdump)
| Use DATA_PUMP_DIR only
|-
| File move
| SCP/SFTP
| RDS S3 integration
|-
| Tools
| expdp/impdp CLI
| expdp/impdp CLI or SQL*Plus
|}

----

''Need sample scripts for S3 file transfer or want a checklist for a particular AWS scenario? Just let me know!''

← Older revision		Revision as of 10:54, 28 May 2025
Line 140:		Line 140:

	----		----
−
−	~~''Need sample scripts for S3 file transfer or want a checklist for a particular AWS scenario? Just let me know!''~~

Oracle Schema backup on AWS - Revision history

Dbagley: /* Summary Table */

Dbagley: Created page with "= Specification: Oracle Data Pump Schema Migration Between AWS Instances = == 1. Prerequisites == * '''Source and target Oracle databases''' running in AWS (RDS, EC2, or sim..."