AWS: Oracle DB Import

We recently moved to AWS as our Oracle db provider and hopefully this guide helps all AWS newbies..

Few things to keep in mind

• You do not have physical access to Oracle db server.
• You do not have SYS or SYSTEM privileges to Oracle db, instead you get SA_MASTER account.
• SA_MASTER is a cooked up account with certain privileges. 
• You need to call predefined functions to do SYS level tasks such as killing session, flush pool etc. Refer here for the functions/procedures

Oracle DB Dump import.

This is tricky as you don't have access to OS server running Oracle. So you cannot physically move the dump file to Oracle directory (DATA_PUMP_DIR)

By default AWS Admin will provide you a "Jump box" and SA_MASTER credentials for the target AWS DB. Typically you will have admin access to this Jump box and you manage all target AWS Oracle OS  related activities through this Jump Box. You need to install an Oracle db on this Jump box, and since you are the admin, you will have SYS/SYSTEM privileges on this Jump box db.

Lets call our Jump Box Oracle db as "Local" db from now on.

Let's say I want to create a schema called RMANNI on AWS DB

Connect to AWS DB as SA_Master

1. Create data and index tablespaces. By default they will be created as "BigFiles", so you don't have to specify data files or location. Once you create the user, you can grant whatever privileges you want. In my example, I am granting bare minimum

• Create Tablespace RMANNI_DATA;
• Create Tablespace RMANNI_INDX;
• Create User RMANNI Identified by RMANNI;
• Alter User RMANNI Default Tablespace RMANNI_DATA Temporary Tablespace TEMP Quota Unlimited on RMANNI_DATA Quota Unlimited on RMANNI_INDX;
• Grant Connect, Create Session,Alter Session to RMANNI;

Connect to "Local" (Jump box db) as SYS/SYSTEM

1. Use default DATA_PUMP_DIR or map any directory on jump box. In my case i mapped "D:\datadump" folder to DATA_ PUMP_LOCAL
2. Copy your data dump to above folder on jump box.
3. Here is the fun part. From local db, create a database link pointing to target AWS db. Replace HOST,PORTand SID according to your setup
            Create Database Link TO_AWS_PROD  
            Connect To RMANNI
            Identified By RMANNI
            Using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)              (HOST=your_host_address*rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=      (SID=ORCLDB)))';
4. Verify if the database link works
   Select * from dual@TO_AWS_PROD
5. Transfer the dump. Command below will transfer the file you placed in your local data pump folder to AWS Server data pump folder using the Database link you created in previous step. Depending upon the size of your dump, this step may take a while to complete.
   BEGIN
             DBMS_FILE_TRANSFER.PUT_FILE(
               source_directory_object       => 'DATA_PUMP_LOCAL',
               source_file_name              => 'MY_SOURCE.DMP',
               destination_directory_object  => 'DATA_PUMP_DIR',
               destination_file_name         => 'MY_SOURCE.DMP',
               destination_database          => 'TO_AWS_PROD'
             );
           END;

Once the dump is transferred, you import the dump as usual. You can either import as RMANNI or SA_MASTER. If you import as RMANNI, make sure to grant read,write access to the data pump folder on AWS db.
        DIRECTORY=DATA_PUMP_DIR 
        DUMPFILE=MY_SOURCE.DMP 
        LOGFILE=MY_SOURCE.log 
        REMAP_SCHEMA=<>:RMANNI 
        REMAP_TABLESPACE=<>_DATA:RMANNI_DATA,<>_INDX:RMANNI_INDX                    SCHEMAS=<source>

After the import, review log file. Again, since you don't have access to db server, you retrieve log file using Select statement and copy/paste output to file or spool the output from SQLPlus.

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','MY_SOURCE.log'));

or

SET HEADING OFF
 spool D:\RMANNI\RMANNI.LOG
select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR','MY_SOURCE.log'));
spool off

Hope this helps.

Oracle export unwrapped object's DDL

i had to mass extract source code for all unwrapped objects from the db, and here is what I used

select   u.name,  u.type,   dbms_metadata.get_ddl(replace(u.type,' BODY'),u.name) ddl_source
from    user_source u
where   line = 1
and     instr(text, 'wrapped') = 0
and type  in ( 'PACKAGE BODY'   ,   'PROCEDURE','FUNCTION')
order by u.type,    u.name

I ran above sql to extract Package, Procedure and Function and ran it in Toad/SQL Developer and then export the output

Here is an example to extract table definition from scott schema

Select t.table_name ,dbms_metadata.get_ddl('TABLE',t.table_name) ddl_source
from user_tables t

ORA-04043 object XXXXXX does not exist

When I tried to drop my user, I got the error

drop user mrrame cascade;

ORA-00604: error occurred at recursive SQL level 1
ORA-04043: object SYS_PLSQL_816EF5E1_361_1 does not exist

Not sure what is causing it, but quick workaround is to drop the TYPE and replace the numeric value with Dummy as shown

drop type SYS_PLSQL_816EF5E1_DUMMY_1;

drop user mrrame cascade;
user dropped;

ODP.NET: ORA-03111: Break received on communication channel

I am using Oracle Managed dlls (Oracle.ManagedDataAccess.dll) in program to export a data set into XML and lately I've been getting  "ORA-03111: Break received on communication channel" errors.

I was obviously looking into network and TNS to figure out where the issue is, but it seems ODP.Net is masking the exception thrown from my program.

Actual issue was due to dirty characters in a string, and escaping them to XML was failing.

so, If you get this error, start from your SQL. Its probably a SQL error, but masked to something totally different.

Oracle Patch: OCI.DLL is active

I had to patch Oracle 12c to latest patch set today and it took couple of attempts to me it work.
In our setup, Oracle is running on Windows server.

1) Downloaded patch 26161726 from MetaLink.
2) Go to Windows Services and stop all Oracle services
3) first attempt on "opatch apply" resulted in following error

Following files are active :
                             D:\app\oracle\product\121~1.0\dbhome_1\bin\oci.dll

A quick search in ProcessExplorer.exe for oci.dll, resulted in 4 processess.



I had to stop "VMware Tools" and "COM+ System Application" services to kill above processes.




Once the OCI.dll reference was cleared, opatch finished successfully.

C# System.TypeInitialization error

I was working on a project and it failed to run at client site. When client launches the program, they get an error like below







Obviously, I have an issue with some class initialization, and neither event viewer nor the crash report had anything useful.


Quick fix is to add an Exception clause and InnerException exactly tells you the nature of the problem