Database in Cloud: Quickest Procedure to Clone a Schema

Hey all!
So, we all know that operating with files/dump files can be tricky when using DBaaS in Public Cloud. In some situations, like Amazon RDS service, we simply don’t have access to SO.

In this scenario, how can we quickly clone a schema in the database? Using IMPDP with Database Link.

Also note that when working on AWS environments, avoiding to use dumpfiles when dealing with expdp/impdp is also encouraged to save IOPS from local disks (it is capped based on machine type).

This is, of course, also valid for On-Premise environments with limited area for dump files.

For this to work, we only need to create a database link pointing to the same database.
Also, of course, it is mandatory to use remap_schema, with optional clause remap_tablespace.

In the following example the link name is loop and proceed with impdp from a jumpbox with an Oracle client and tns configuration to RDS database.

1. Creating Database Link:

SQL> create database link loop connect to my_sysdba identified by "***" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service-name)))';

Database link created.

SQL> select * from dual@loop;

D
-
X

2. Running IMPDP:

[oracle@jumpbox ~]$ impdp schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log

Import: Release 11.2.0.4.0 - Production on Wed Fev 7 21:03:54 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYS"."SYS_IMPORT_SCHEMA_02": /******** AS SYSDBA schemas=ORIGINAL_SCHEMA network_link=loop remap_schema=ORIGINAL_SCHEMA:NEW_SCHEMA remap_tablespace=ORIGINAL_TBS:NEW_TBS TRANSFORM=oid:n directory=ANY_DIR logfile=duplicate_original.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.481 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NEW_SCHEMA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
...
. . imported "NEW_SCHEMA"."TABLE1" 0 rows
. . imported "NEW_SCHEMA"."TABLE2" 0 rows
. . imported "NEW_SCHEMA"."TABLE3" 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Wed Fev 7 21:08:52 2018 elapsed 0 00:04:54

Hope it helps,
Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s