How To Import A Schema on Amazon RDS
As you know, there are two types of cloud services AWS provides (EC2 & RDS) while EC2 let you have the full control over the Operating System OS including root access, RDS doesn't give you any kind of OS access. Because RDS instance is managed by AWS they provide you a master admin user , this user has limited admin privileges (neither a SYSDBA nor DBA), making regular DBA tasks such as importing a schema a bit challenging.
Without having an OS access you won't be able to use commands like: exp ,expdp, imp, impdp and rman.
Below are the stAPPS how to import a schema into RDS using Oracle built-in packages. Luckily Oracle provides many built-in packages enable you to perform lots of tasks without the need to have an OS access.
Below is the Amazon document importing a schema into RDS:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html
Task Summary:
Export a schema with name "APPS_TRX" on an 11.2.0.3 database resides on AWS EC2 Linux instance and upload the export dump file to S3 bucket, then import the dump file into a 12.2.0.1 AWS RDS database along with changing the schema name to "APPS.
Prerequisites:
- An AWS S3 bucket must be created and Both Source EC2 and Target RDS must have RW access to it through a role. [S3 bucket is a kind of a shared storage between AWS cloud systems where you can upload/download the files to/from it, it will be used during this demo to transfer the export dump file between EC2 source instance and RDS target instance].
Step1: Export the schema on Source [EC2 instance]:
I've already an OS access to oracle user on the source EC2 instance so I used exportdata script to export APPS_TRX schema.
Note: In case you are importing from Enterprise Edition DB to Standard Edition DB make sure to reset all tables having COMPRESSION option enabled to NOCOMPRESS before exporting the data:
i.e.
alter table APPS_TRX.compressed_table NOCOMPRESS;
This is because Standard Edition doesn't have COMPRESSION feature. Otherwise the table creation will fail with ORA-39083 error during the import on the Standard Edition DB.
Step2: Upload the export file to S3 Bucket from Source [EC2 instance]:
In case the bucket is not yet configured on the source machine you can use the following AWSCLI command to configure it providing the bucket's "Access Key" and "Secret Access Key":
# aws configure
AWS Access Key ID [None]: XXXXXXXXXXXXXXXX
AWS Secret Access Key [None]: XXXXXXXXXXXXXXXXX
Default region name [None]:
Default output format [None]:
Note: The keys above are dummy ones, you have to put your own bucket key.
Upload the export dump files to the S3 bucket:
# cd /backup
# aws s3 cp EXPORT_APPS_TRX_STG_04-03-19.dmp s3://APPS-bucket
In case you are using S3 Browser from a Windows machine, configure the bucket using this flow:
Open S3 Browser -> Accounts -> Add New Account:
<you will use your bucket details here I'm just giving an example>
Account Name: APPS-bucket
Account Type: Amazon S3 Storage
Access Key ID: ***********
Secret Access Key: ************
Click "Add New Account"
Accounts -> click "APPS-bucket" -> Click "Yes" to add 'External bucket' -> Bucket Name: "APPS-bucket"
Note: S3 Browser is a Windows GUI tool provided by AWS that help you deal with uploading/downlading the file to/from S3 bucket. you can download it from here:
https://s3browser.com/download.aspx
Step2: Download the export file from the S3 Bucket to the Target [RDS instance]:
Remember, there is no OS access on RDS, so we will connect to the database using any tools such as SQL Developer using the RDS master user credentials.
Use the AWS built-in package "rdsadmin.rdsadmin_s3_tasks" to download the dump file from S3 bucket to DATA_PUMP_DIR:
Warning: The following command will download all the files in the bucket, so make sure before running this command to remove all the files except the export dump files.
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'APPS-bucket',
p_directory_name => 'DATA_PUMP_DIR')
AS TASK_ID FROM DUAL;
In case you have the export files stored under a specific directory, you can tell the download procedure to download all the files under that specific directory by using p_s3_prefix parameters like this: [don't forget the slash / after the directory name]
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'APPS-bucket',
p_s3_prefix => 'export_files/',
p_directory_name => 'DATA_PUMP_DIR')
AS TASK_ID FROM DUAL;
Or, in case you only want to download one named file at a time under a specific directory, just provide that file name as shown to p_prefix parameter:
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'APPS-bucket',
p_s3_prefix => 'export_files',
p_prefix => 'EXPORT_APPS_TRX_STG_04-03-19.dmp',
p_directory_name => 'DATA_PUMP_DIR')
AS TASK_ID FROM DUAL;
Above command will return a TASK ID:
TASK_ID
--------------------------
1866786876865468-797
Use that TASK_ID to monitor the download progress by running this statement:
SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1866786876865468-797.log'));
In case you get this error:
ORA-00904: "RDSADMIN"."RDSADMIN_S3_TASKS"."DOWNLOAD_FROM_S3": invalid identifier
This means S3 integration is not configured with your RDS.
To configure S3 integration: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html
##################STEPS TO CREATE POLOCY,ROLE AND ASSIGN ROLE DB##############
FROM CONSOLE
============
Open the IAM Management Console: https://console.aws.amazon.com/iam/home?#/home
In the navigation pane, choose Policies -> Create policy On the Visual editor tab, choose Choose a service, and then choose S3 -> Check All S3 actions
Choose Resources, and choose Add ARN for the bucket -> Enter the Bucket name: APPS-bucket
Click Review Policy -> Give it a name "APPS-s3-integration" -> Create Policy
Associate your IAM role with your RDS DB:
Sign in to the AWS Management Console: https://console.aws.amazon.com/rds/
Choose the Oracle DB instance name -> On the Connectivity & security tab -> Manage IAM roles section:
IAM roles to this instance: -> "APPS-s3-integration"
Feature -> S3_INTEGRATION
Click "Add role"
Make sure that your database is running with "rds-s3-integration" option group parameters.
FROM CLI
========
POLICY CREATION:
The following AWS CLI command creates an IAM policy named rds-s3-integration-policy with these options. It grants access to a bucket named your-s3-bucket-arn.
aws iam create-policy \
--policy-name rds-s3-integration-policy \
--policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3integration",
"Action": [
"s3:GetObject",
"s3:ListBucket",
"s3:PutObject"
],
"Effect": "Allow",
"Resource": [
"arn:aws:s3:::your-s3-bucket-arn",
"arn:aws:s3:::your-s3-bucket-arn/*"
]
}
]
}'
ROLE CREATION:
The following AWS CLI command creates the rds-s3-integration-role for this purpose.
aws iam create-role \
--role-name rds-s3-integration-role \
--assume-role-policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}'
ATTACH ROLE TO POLICY:
The following AWS CLI command attaches the policy to the role named rds-s3-integration-role.
aws iam attach-role-policy \
--policy-arn your-policy-arn \
--role-name rds-s3-integration-role
ADD ROLE TO DB INSTANCE:
The following AWS CLI command adds the role to an Oracle DB instance named mydbinstance.
aws rds add-role-to-db-instance \
--db-instance-identifier mydbinstance \
--feature-name S3_INTEGRATION \
--role-arn your-role-arn
Once the download is complete, query the downloaded files under DATA_PUMP_DIR using this query:
select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;
Any file having "incomplete" keyword, means it still getting downloaded.
Now the AWS related tasks are done, let's jump to the import part which is purely Oracle's.
Step3: Create the tablespace and the target schema user on the Target [RDS instance]:
In case the target user does not yet exist on the target RDS database, you can go ahead and create it along with its tablespace.
-- Create a tablespace: [Using Oracle Managed Files OMF]
CREATE SMALLFILE TABLESPACE "TBS_APPS" DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M LOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
-- In case you need to create a Password Verify Function on RDS:
Note: As you cannot create objects under SYS in RDS you have to use the following ready made procedure by AWS to create the Verify Function:
Note: The verify function name should contains one of these keywords: "PASSWORD", "VERIFY", "COMPLEXITY", "ENFORCE", or "STRENGTH"
begin
rdsadmin.rdsadmin_password_verify.create_verify_function(
p_verify_function_name => 'CUSTOM_PASSWORD_VFY_FUNCTION',
p_min_length => 8,
p_max_length => 256,
p_min_letters => 1,
p_min_lowercase => 1,
p_min_uppercase => 1,
p_min_digits => 3,
p_min_special => 2,
p_disallow_simple_strings => true,
p_disallow_whitespace => true,
p_disallow_username => true,
p_disallow_reverse => true,
p_disallow_db_name => true,
p_disallow_at_sign => false);
end;
/
-- In case you want to create a new profile:
create profile APP_USERS limit
LOGICAL_READS_PER_SESSION DEFAULT
PRIVATE_SGA DEFAULT
CPU_PER_SESSION DEFAULT
PASSWORD_REUSE_TIME DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_VERIFY_FUNCTION CUSTOM_PASSWORD_VFY_FUNCTION
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_LIFE_TIME 90
SESSIONS_PER_USER DEFAULT
CONNECT_TIME DEFAULT
CPU_PER_CALL DEFAULT
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_REUSE_MAX 12
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT;
-- Create the user: [Here the user as per my business requirements will be different than the original user on the Source DB]
CREATE USER APPS IDENTIFIED BY "test123" DEFAULT TABLESPACE TBS_APPS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TBS_APPS PROFILE APP_USERS;
GRANT CREATE SESSION TO APPS;
GRANT CREATE JOB TO APPS;
GRANT CREATE PROCEDURE TO APPS;
GRANT CREATE SEQUENCE TO APPS;
GRANT CREATE TABLE TO APPS;
Step4: Import the dump file on the Target [RDS instance]:
Open a session from SQL Developer and make sure this session will not disconnect as far as the import is running, by the RDS master user execute the following block of code which will keep running in the foreground allowing you to monitor the import job on the fly and see any incoming errors:
DECLARE
ind NUMBER; -- Loop index
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
BEGIN
h1 := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
-- Specify the single dump file and its directory DBMS_DATAPUMP.ADD_FILE(handle => h1, directory => 'DATA_PUMP_DIR', filename => 'EXPORT_APPS_TRX_STG_04-03-19.dmp');
-- Specify the logfile for the import process: [Very important to read it later after the completion of the import] DBMS_DATAPUMP.ADD_FILE(handle => h1, directory => 'DATA_PUMP_DIR', filename => 'import_APPS_TRX_STG_04-03-19.LOG', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- Disable Archivelog for the import: [12c new feature] DBMS_DATAPUMP.metadata_transform ( handle => h1, name => 'DISABLE_ARCHIVE_LOGGING', value => 1);
-- REMAP SCHEMA:
-- DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA','APPS_TRX','APPS');
-- If a table already exists: [SKIP, REPLACE, TRUNCATE]
DBMS_DATAPUMP.SET_PARAMETER(h1,'TABLE_EXISTS_ACTION','SKIP');
-- REMAP TABLESPACE: DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_TABLESPACE','APPS','TBS_APPS');
-- Start the job. An exception is returned if something is not set up properly. DBMS_DATAPUMP.START_JOB(h1);
-- The following loop will monitor the job until it get complete.meantime the progress information will be displayed:
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value. if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or Error messages were received for the job, display them. if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and gracefully detach from it. dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(h1);
END;
/
In case you have used wrong parameters or bad combination e.g. using METADATA_FILTER instead of METDATA_REMAP when importing to a schema having a different name, you will get a bunch of errors similar to the below cute vague ones:
ORA-31627: API call succeeded but more information is available
ORA-06512: at "SYS.DBMS_DATAPUMP", line 7143
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4932
ORA-06512: at "SYS.DBMS_DATAPUMP", line 7137
ORA-06512: at line 7
You can also monitor the execution of the import job using this query:
SQL> SELECT owner_name, job_name, operation, job_mode,DEGREE, state FROM dba_datapump_jobs where state='EXECUTING';
In case you want to Kill the job: <Provide the '<JOB_NAME>','<OWNER>'>
SQL> DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','APPS');
DBMS_DATAPUMP.STOP_JOB (h1, 1, 0);
END;
/
Once the job is complete compare the number of objects between source and target DBs:
SQL> select object_type,count(*) from dba_objects where owner='APPS' group by object_type;
Also you can view the import log on RDS using this query:
SQL> set lines 10000 pages 0
SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','import_APPS_TRX_STG_04-03-19.LOG'));
Or: You can upload the log to S3 bucket and get it from there:
SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;
SQL> SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3( p_bucket_name => '<bucket_name>', p_prefix => '<file_name>', prefix => '', p_directory_name => 'DATA_PUMP_DIR') AS TASK_ID FROM DUAL;
Run the After Import script that generated by exportdata script at Step 1 after replacing the original exported schema name APPS_TRX with the target imported schema name APPS.
Check the invalid objects:
SQL> col object_name for a45
select object_name,object_type,status from dba_objects where owner='APPS' and status<>'VALID';
Compile invalid object: [If found]
SQL> EXEC SYS.UTL_RECOMP.recomp_parallel(4, 'APPS');
Step5: [Optional] Delete the dump file from the Target [RDS instance]:
Check the exist files under DATA_PUMP_DIR directory:
SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;
Generate delete script for all files:
SQL> select 'exec utl_file.fremove(''DATA_PUMP_DIR'','''||filename||''');' from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;
Run the output script:
e.g. exec utl_file.fremove('DATA_PUMP_DIR','EXPORT_APPS_TRX_STG_04-03-19.dmp');
For more reading on a similar common DBA tasks on RDS:
http://dba-tips.blogspot.com/2020/02/the-dba-guide-for-managing-oracle.html
References:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html
S3 Bucket creation:
https://docs.aws.amazon.com/AmazonS3/latest/gsg/CreatingABucket.html
DBMS_DATAPUMP:
https://docs.oracle.com/database/121/ARPLS/d_datpmp.htm#ARPLS356
RDS Master Admin User:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html
Import
