Replicate Oracle data to Amazon RDS using AWS DMS services

Below are the steps to migrate to replicate Oracle tables to Amazon RDS Oracle database:

Setup Source database

1.       Mount the database

SQL> startup mount

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1341312 bytes

Variable Size             427821184 bytes

Database Buffers          629145600 bytes

Redo Buffers               13025280 bytes

Database mounted.

SQL>

2.       Enable Archive Log feature:

SQL> alter database archivelog;

Database altered.

SQL>

3.       Modify init.ora file to set below parameter:

                  log_archive_start             = true

4.       Open the database:

SQL> alter database open;

Database altered.

5.       Enable Supplemental log data:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

6.       Enable Table level identification key supplemental logging for specific tables:

SQL> alter table ONT.OE_ORDER_LINES_ALL add supplemental log data (unique) columns;

Table altered.

7.       Create a tablespace AWSDMS_DATA

Create a database user account AWSDMS:
create user AWSDMS identified by awsdms default tablespace awsdms_data quota unlimited on awsdms_data temporary tablespace temp1;

8.       Grant below privileges to AWSDMS user.

grant CREATE SESSION  to awsdms;

grant SELECT ANY TRANSACTION  to awsdms;

grant SELECT on V_$ARCHIVED_LOG  to awsdms;

grant SELECT on V_$LOG  to awsdms;

grant SELECT on V_$LOGFILE  to awsdms;

grant SELECT on V_$DATABASE  to awsdms;

grant SELECT on V_$THREAD  to awsdms;

grant SELECT on V_$PARAMETER  to awsdms;

grant SELECT on V_$NLS_PARAMETERS  to awsdms;

grant SELECT on V_$TIMEZONE_NAMES  to awsdms;

grant SELECT on V_$TRANSACTION  to awsdms;

grant SELECT on ALL_INDEXES  to awsdms;

grant SELECT on ALL_OBJECTS  to awsdms;

grant SELECT on ALL_TABLES  to awsdms;

grant SELECT on ALL_USERS  to awsdms;

grant SELECT on ALL_CATALOG  to awsdms;

grant SELECT on ALL_CONSTRAINTS  to awsdms;

grant SELECT on ALL_CONS_COLUMNS  to awsdms;

grant SELECT on ALL_TAB_COLS  to awsdms;

grant SELECT on ALL_IND_COLUMNS  to awsdms;

grant SELECT on ALL_LOG_GROUPS  to awsdms;

grant SELECT on SYS.DBA_REGISTRY  to awsdms;

grant SELECT on SYS.OBJ$  to awsdms;

grant SELECT on DBA_TABLESPACES  to awsdms;

grant SELECT on ALL_TAB_PARTITIONS  to awsdms;

grant SELECT on ALL_ENCRYPTED_COLUMNS  to awsdms;

grant SELECT on ont.oe_order_lines_all   to awsdms;

grant EXECUTE on DBMS_LOGMNR  to awsdms;

grant SELECT on V_$LOGMNR_LOGS to awsdms;

grant SELECT on V_$LOGMNR_CONTENTS to awsdms;

Setup Target RDS Database:

1.       Go to AWS RDS console and click on “Get Started Now”

2.       Select Oracle -> “Oracle SE Two”

3.       Select “Dev/Test” option on next page

4.       Specify DB details as below:

5.       Configure Advanced Settings:

6.       Click on Launch DB Instance button

7.       Create replication instance

Open browser with url https://console.aws.amazon.com/dms

Click on “Create Migration”

Click Next

Create Source and target database endpoints

Click Next

 

Create on “Create Task”

Now, you can test making a change in the table at source database and see it populated in target database.

Start your own Oracle Vision (Demo) instance R12.2.4 by transferring Oracle VM Virtual appliance image to Amazon AWS services

• Start a Windows instance from AWS Console using AMI "Windows_Server-2016-English-Full-Base-2017.01.11 (ami-7f2e031f)" with 500GB space.

• Download and start Firefox.

• Go to http://edelivery.oracle.com

• Sign up or log on to edeliver.oracle.com

• Search for “Oracle VM Virtual Appliances for Oracle E-Business Suite 12.2.4”

• Select all files with the description "Oracle E-Business Suite Release 12.2.4 Single Node Vision Install X86 (64bit)".

• Once above files are downloaded, unzip all the files and run below command from Dos prompt to concatenate above files into one single ova file.  List all the file names separated by a space in below command.

                  type Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.00 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.01 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.02 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.03 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.04 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.05 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.06 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.07 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.08 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.09 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.10 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.11 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.12 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.13 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.14 Oracle-E-Business-Suite-12.2.4_VISION_INSTALL.ova.15>R1224.ova

• Log on to AWS and choose S3 service.

• Create a bucket with the name "r1224ovm" to store R1224.ova

• Upload R1224.ova  file to S3 Bucket r1224ovm.     

• Import the VM into AWS by following steps :

              1.  Create a file called contain.json with below contents:

                        [ 

                         {

                            "Description": "Oracle Demo Vision Instance R12.2.4",

                            "Format": "ova",

                            "UserBucket": {

                            "S3Bucket": "r1224ovm",

                            "S3Key": "R1224EBS.ova"

                          }

                         }]
               2.  On Windows Server, go to Start->Windows Powershell for AWS
               3.   Provide your Access key and secret access key (your AWS Account) at the prompts from "Windows Powershell for AWS".
               4.   Take care of creating role and policy as per steps given in http://docs.aws.amazon.com/vm-import/latest/userguide/vmimport-image-import.html#vmimport-iam-permissions
               5.   Run below command:
                        aws ec2 import-image --description "Oracle Demo Vision Instance R12.2.4" --disk-containers file://containers.json
               6.   Check the status of import with below command:
                       aws ec2 describe-import-image-tasks --import-task-ids import-ami-xxxxxx

• Go to EC2 service in Amazon AWS console.

• At this point, you can terminate your Windows instance in AWS, since we have the image imported into AWS.  You may also want to remove any storage associated with this Windows instance.

• Start an instance using the image imported in above steps.  I suggest using at minimum t2-large instance type.

• Once this instance is up, login as oracle by following instructions from this document: http://www.oracle.com/technetwork/articles/servers-storage-admin/evaluating-linux-vb-1934676.html

• Login to root using below command:

                su -

                You will be prompted to change the password of oracle, root and applmgr users.

• Note down the private IP Address on your ec2 instance. 

• Open /etc/hosts file in vi editor and add below entry:

• Your private ip_address ebs.example.com ebs

• Type below command to change your host name.

• hostname ebs

• Login as oracle

• ln –s /u01/install/VISION/EBSapps.env .

• Steps to startup the database:

              cd /u01/install/VISION/11.2.0

              . ./EBSDB_ebs.env

              sqlplus '/ as sysdba'<<EOF

              startup

              EOF

              lsnrctl start EBSDB

• Steps to startup the application:

              cd /u01/install/VISION

              . ./EBSapps.env run

              cd $ADMIN_SCRIPTS_HOME

              ./adstrtal.sh apps/apps

              Default password for weblogic is ‘welcome1’

• On your computer, open C:\Windows\System32\drivers\etc\hosts file in a notepad and add below entry so that your computer can reach ebs.example.com:

                xx.xx.xxx.xxx ebs.example.com ebs

                xx.xx.xxx.xxx is an ip address attached to your EC2 instance.

• Open a browser and login to application using url: http://ebs.example.com:8000

•  You are all set.

               

Oracle R12 DBA scripts for troubleshooting - part 1

SQL Sript to view database sessions:

  SELECT s.Inst_id,
    s.sid,
    s.module,
    s.action,
    s.program,
    s.process,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    s.blocking_session,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+) ;

If there is a performance issue, you can look at columns like sql_text,optimizer_cost, event etc to figure out cause for slowness.


SQL script to list scheduled concurrent requests:

SELECT fcrsv.REQUEST_ID,
    fcrsv.program,
    fcrsv.REQUESTED_START_DATE,
    ROUND(MIN_RUN_TIME,2) min_run_time,
    ROUND(MAX_RUN_TIME,2) max_run_time,
    ROUND(AVG_RUN_TIME,2) avg_run_time,
    fu.description user_description,
    fcrsv.argument_text,
    fcrsv.program_short_name,
    fcrsv.requestor,
    fcrsv.printer,
    fcr.resubmit_interval
    ||' '
    || fcr.resubmit_interval_unit_code
    ||', '
    ||DECODE(fcr.resubmit_interval_type_code,'START','From the start of prior run','END','from the end of prior run',fcr.resubmit_interval_type_code) Interval_type
  FROM FND_CONC_REQ_SUMMARY_V fcrsv,
    fnd_concurrent_requests fcr,
    FND_CONC_PROG_ONSITE_INFO fcpoi,
    fnd_user fu
  WHERE fcrsv.PROGRAM_APPLICATION_ID=fcpoi.PROGRAM_APPLICATION_ID
  AND fcrsv.CONCURRENT_PROGRAM_ID   =fcpoi.CONCURRENT_PROGRAM_ID
  AND fcr.request_id                =fcrsv.request_id
  AND fcrsv.PHASE_CODE              ='P'
  AND fcrsv.STATUS_CODE             ='I'
  AND fcrsv.requested_start_date    >sysdate -1
    --  AND requested_start_date         <= sysdate+1/24
  AND fcrsv.hold_flag ='N'
  AND fu.user_id      =fcrsv.requested_by
  ORDER BY 3 ;

SQL script view the details of database sessions associated with concurrent request:

  SELECT fcr.request_id,
    s.Inst_id,
    s.sid,
    s.module,
    s.program,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p,
    fnd_concurrent_requests fcr
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+)
  AND p.spid         =Fcr.oracle_process_id
  AND s.process      =fcr.os_process_id ;

SQL script to view list of active application users:

  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    S.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    FRM.USER_FORM_NAME,
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_FORM_TL FRM,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R,
    FND_LOGIN_RESP_FORMS F,
    GV$SESSION S
  WHERE R.LOGIN_ID        = F.LOGIN_ID
  AND R.LOGIN_RESP_ID     = F.LOGIN_RESP_ID
  AND L.LOGIN_ID          = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND F.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND FRM.FORM_ID         = F.FORM_ID
  AND FRM.APPLICATION_ID  = F.FORM_APPL_ID
  AND FRM.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = F.AUDSID
  UNION
  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    s.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(R.START_TIME, L.START_TIME) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    'FNDSCSGN',
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R ,
    GV$SESSION S
  WHERE L.LOGIN_ID        = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = R.AUDSID
  ORDER BY 6 ;


SQL script view list database sessions with top usage of CPU:

  SELECT "INST_ID",
    "SID",
    "SPID",
    "CPU",
    "EVENT",
    "PROGRAM",
    "MODULE",
    "ACTION",
    "LAST_CALL_ET",
    "SQL_TEXT"
  FROM
    (SELECT s.inst_id,
      s.sid,
      p.spid,
      (SELECT value
      FROM v$sesstat
      WHERE v$sesstat.sid = s.sid
      AND statistic#      =
        (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session'
        )
      ) cpu,
      s.event,
      s.program,
      s.module,
      s.action,
      s.last_call_et,
      sa.sql_text--,p.pga_user_mem,p.pga_alloc_mem
    FROM gv$session s,
      gv$process p,
      gv$sqlarea sa
    WHERE s.paddr    =p.addr
    AND s.sql_address=sa.address
    AND s.status     ='ACTIVE'
    )
  WHERE cpu  IS NOT NULL
  AND module IS NOT NULL
  AND sql_text NOT LIKE 'begin fndcp%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_GSM%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_OPP%'
  AND rownum < 21
  ORDER BY 4 DESC ;

Note: This blog has been moved to http://www.oamonitor.com/wordpress/blog

Oracle Applications on Amazon's Elasitc Cloud Computing (EC2)

New Post: Start your own Oracle Vision (Demo) instance R12.2.4 using Amazon AWS services

Amazon Elastic Compute Cloud (Amazon EC2) is a web service that provides resizable compute capacity in the cloud.  The EC2 web service interface allows users to create linux / windows instance with different configurations of CPU and memory, in minutes.  Using the web services interface, users can also scale the capacity of instances both up and down.  For more information on Amazon EC2 service and functionality, please visit http://aws.amazon.com/ec2/#functionality .

Subscribing to EC2 service

Subscribing to EC2 service is easy.  Just go to aws.amazon.com and sign up.  You will have to do the following steps, before you can start an instance.  These are high level steps based on my experience.

1. Subscribe for EC2 service. 
2. Log in to "AWS management console".
3. Click on VPC tab and click on Get started creating a VPC to create VPC.  You are creating a Virtual Private Cloud in this step which will provide you a subnet private to your account.
4. In left side bar, under "Network & Security", click on Security Groups to create a VPC based security group.
5. In left side bar, under "Network & Security", click on Key Pairs to create a new key pair.  Key pair will be used to access Linux instance via ssh.

Starting up Oracle E-Business Vision Instance

1. Click on EC2 tab
2. In left side bar, under IMAGES, Click on  AMI.
3. Change the viewing filter to "All Images"  and "All Platforms"
4. Search for image id ami-57c1013e (Oracle E-Business Suite 12.1.3 Database 64-bit - OVM" .
5. Right click on the row and select "Launch Instance"
6. Go through the wizard for creating an instance. 
7. Once the instance is created, create another instance by selecting image id ami-227a8c4b (Oracle-E-Business-Suite-12.1.3-App-Tier).
8. Here is the link from Amazon's AWS website on how to connect to linux instance using SSH : http://docs.amazonwebservices.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux.html
9. Once you are able to connect to both linux instances,
10. Visit the link https://aws.amazon.com/amis/oracle-e-business-suite-12-1-3-app-tier-64-bit on documentation of oracle images which provides "quick start" guide and other information on starting the vision instance.

If you need more information or help on starting up the vision instance using Oracle image, please let me know via email: naresh.awasthi@gmail.com

Thanks
Naresh