Wednesday 18 July 2018

Oracle Database Appliance : Monitor Hardware Components

Oracle Database Appliance (ODA) is an entry level Engineered System. ODA is a pre-configured, highly available Oracle Database Engineered system. ODA system consists of hardware, software, storage and networking. The hardware configuration is designed to provide redundancy and protection against single points of failures in the system.

ODA consists of two physical servers (Node 0 and Node 1), a storage shelf and optionally an additional storage shelf. The two independent physical servers are interconnected and direct attached to SAS and SSD storage.

ODA is basically a 2-node RAC cluster database system running Oracle Linux operating (OEL), Oracle Database Enterprise Edition, Oracle Grid Infrastructure (Clusterware and ASM). All these together provides the Oracle Database high availability running on ODA.

ODA consists of several hardware components such as Mother Board, Processor, Memory, Power Supply, FAN, Network cards and so on. You can monitor the hardware status of these components using OAKCLI command for both Bare Metal and Virtualized platform.

Note: ODACLI is used for Hardware monitoring and administrative tasks on the Oracle Database Appliance on X6-2 S/M/L & X7-2 S/M.


In this article we will demonstrate how to monitor different hardware component status on ODA nodes.

Using OAKCLI command to Get Hardware Status


  • Execute the following command to display ODA server details

[root@odanoden1 ~]# oakcli show server

        Power State              : On
        Open Problems            : 0
        Model                    : ODA X4-2
        Type                     : Rack Mount
        Part Number              : 33060862+1+1
        Serial Number            : 1440XXXXXX
        Primary OS               : Not Available
        ILOM Address             : 10.10.20.1
        ILOM MAC Address         : 00:10:E0:62:3F:F6
        Description              : Oracle Database Appliance X4-2 1440XXXXX
        Locator Light            : Off
        Actual Power Consumption : 261 watts
        Ambient Temperature      : 18.500 degree C
        Open Problems Report     : System is healthy

[root@odanoden2 ~]# oakcli show server

        Power State              : On
        Open Problems            : 0
        Model                    : ODA X4-2
        Type                     : Rack Mount
        Part Number              : 33060862+1+1
        Serial Number            : 1440XXXXXX
        Primary OS               : Not Available
        ILOM Address             : 10.10.20.2
        ILOM MAC Address         : 00:10:E0:62:41:D6
        Description              : Oracle Database Appliance X4-2 1440XXXXX
        Locator Light            : Off
        Actual Power Consumption : 269 watts
        Ambient Temperature      : 17.750 degree C
        Open Problems Report     : System is healthy


  • Execute the following command to display ODA model

[root@odanoden1 ~]# oakcli show env_hw
BM ODA X4-2
Public interface : COPPER


  • Execute the following command to display ODA software version details

[root@odanoden1 ~]# oakcli show version -detail
Reading the metadata. It takes a while...
System Version  Component Name            Installed Version         Supported Version
--------------  ---------------           ------------------        -----------------
12.1.2.10.0
                Controller_INT            11.05.03.00               Up-to-date
                Controller_EXT            11.05.03.00               Up-to-date
                Expander                  0018                      Up-to-date
                SSD_SHARED                944A                      Up-to-date
                HDD_LOCAL                 A72A                      Up-to-date
                HDD_SHARED                A72A                      Up-to-date
                ILOM                      3.2.8.25 r114493          Up-to-date
                BIOS                      25040100                  Up-to-date
                IPMI                      1.8.12.4                  Up-to-date
                HMP                       2.3.5.2.8                 Up-to-date
                OAK                       12.1.2.10.0               Up-to-date
                OL                        6.8                       Up-to-date
                GI_HOME                   12.1.0.2.170117(2473      Up-to-date
                                          2082,24828633)
                DB_HOME                   12.1.0.2.170117(2473      Up-to-date
                                          2082,24828633)


  • Execute the following command to display 'oakcli show' help

[root@odanoden1 ~]# oakcli show -h
Usage:
oakcli show {disk|diskgroup|expander|fs|raidsycstatus|controller|server|processor|memory|power|cooling|network|enclosure|storage|core_config_key|version|dbhomes|dbstorage|databases|db_config_params|asr|env_hw} [<options>]
where:
        disk                     - About the disk
        diskgroup                - ASM disk group
        expander                 - Expander
        fs                       - Filesystem
        controller               - Controller
        storage                  - All storage components
        version                  - Running software version
        dbhomes                  - Installed oracle database homes
        dbstorage                - Details of ACFS storage setup for the databases
        databases                - Database names
        db_config_params         - db_config_params file
        asr                      - ASR configuration
        env_hw                   - Environment and Hardware information
        server                   - Details of server sub-system
        processor                - Details of processor sub-system
        memory                   - Details of memory sub-system
        power                    - Details of power supply sub-system
        cooling                  - Details of cooling sub-system
        network                  - Details of network sub-system
        enclosure                - Details of enclosure sub-system
        raidsyncstatus           - RAID sync status information
        core_config_key          - Core configuration
For detailed help on each command and object and its options use:
oakcli <command> <object> -h


  • Execute the following command to monitor the Processor Status

[root@odanoden1 ~]# oakcli show processor

        NAME  HEALTH HEALTH_DETAILS PART_NO. LOCATION   MODEL                         MAX_CLK_SPEED TOTAL_CORES ENABLED_CORES

        CPU_0 OK     -              060E     P0 (CPU 0) Intel(R) Xeon(R) CPU E5-2697  2.700 GHz       12        NA
        CPU_1 OK     -              060E     P1 (CPU 1) Intel(R) Xeon(R) CPU E5-2697  2.700 GHz       12        NA


  • Execute the following command to monitor the Memory Status

[root@odanoden1 ~]# oakcli show memory

        NAME    HEALTH HEALTH_DETAILS PART_NO.         SERIAL_NO.         LOCATION MANUFACTURER MEMORY_SIZE CURR_CLK_SPEED ECC_Errors

        DIMM_0  OK     -              M393B2G70DB0-YK0 00CE03143317593248 P0/D0    Samsung      16 GB       1600 MHz       0
        DIMM_1  OK     -              M393B2G70DB0-YK0 00CE0314331759238B P0/D1    Samsung      16 GB       1600 MHz       0
        DIMM_10 OK     -              M393B2G70DB0-YK0 00CE031433175926CD P1/D2    Samsung      16 GB       1600 MHz       0
        DIMM_11 OK     -              M393B2G70DB0-YK0 00CE031433175927AD P1/D3    Samsung      16 GB       1600 MHz       0
        DIMM_12 OK     -              M393B2G70DB0-YK0 00CE031433175922C3 P1/D4    Samsung      16 GB       1600 MHz       0
        DIMM_13 OK     -              M393B2G70DB0-YK0 00CE03143317593250 P1/D5    Samsung      16 GB       1600 MHz       0
        DIMM_14 OK     -              M393B2G70DB0-YK0 00CE0314331759367A P1/D6    Samsung      16 GB       1600 MHz       0
        DIMM_15 OK     -              M393B2G70DB0-YK0 00CE03143317593319 P1/D7    Samsung      16 GB       1600 MHz       0
        DIMM_2  OK     -              M393B2G70DB0-YK0 00CE031433175927A8 P0/D2    Samsung      16 GB       1600 MHz       0
        DIMM_3  OK     -              M393B2G70DB0-YK0 00CE03143317592B31 P0/D3    Samsung      16 GB       1600 MHz       0
        DIMM_4  OK     -              M393B2G70DB0-YK0 00CE03143317592B35 P0/D4    Samsung      16 GB       1600 MHz       0
        DIMM_5  OK     -              M393B2G70DB0-YK0 00CE03143317591C3C P0/D5    Samsung      16 GB       1600 MHz       0
        DIMM_6  OK     -              M393B2G70DB0-YK0 00CE031433175922C7 P0/D6    Samsung      16 GB       1600 MHz       0
        DIMM_7  OK     -              M393B2G70DB0-YK0 00CE0314331759324E P0/D7    Samsung      16 GB       1600 MHz       0
        DIMM_8  OK     -              M393B2G70DB0-YK0 00CE0314331759324B P1/D0    Samsung      16 GB       1600 MHz       0
        DIMM_9  OK     -              M393B2G70DB0-YK0 00CE0314331759331A P1/D1    Samsung      16 GB       1600 MHz       0


  • Execute the following command to monitor Power Status

[root@odanoden1 ~]# oakcli show power

        NAME            HEALTH HEALTH_DETAILS PART_NO. SERIAL_NO.         LOCATION INPUT_POWER OUTPUT_POWER INLET_TEMP     EXHAUST_TEMP

        Power_Supply_0  OK     -              7079395  476856Z+1435CE00EU PS0      Present     119 watts    32.250 degree C 36.562 degree C
        Power_Supply_1  OK     -              7079395  476856Z+1435CE00F6 PS1      Present     112 watts    37.000 degree C 40.375 degree C


  • Execute the following command to monitor Network Status

[root@odanoden1 ~]# oakcli show network

        NAME           HEALTH HEALTH_DETAILS LOCATION PART_NO MANUFACTURER MAC_ADDRESS        LINK_DETECTED DIE_TEMP

        Ethernet_NIC_0 OK     -              NET0     X540    INTEL        00:10:E0:62:3F:F2  yes (eth2)    46.250 degree C
        Ethernet_NIC_1 OK     -              NET1     X540    INTEL        00:10:E0:62:3F:F3  no (eth3)     46.250 degree C
        Ethernet_NIC_2 OK     -              NET2     X540    INTEL        00:10:E0:62:3F:F4  no (eth4)     51.000 degree C
        Ethernet_NIC_3 OK     -              NET3     X540    INTEL        00:10:E0:62:3F:F5  no (eth5)     51.500 degree C
        Ethernet_NIC_4 -      -              NET4     X540    INTEL        90:E2:BA:81:2B:B4  yes (eth0)    -
        Ethernet_NIC_5 -      -              NET5     X540    INTEL        90:E2:BA:81:2B:B5  yes (eth1)    -


  • Execute the following command to monitor Storage Status

[root@odanoden1 ~]# oakcli show storage
==== BEGIN STORAGE DUMP ========
Host Description: Oracle Corporation:SUN SERVER X4-2
Total number of controllers: 3
        Id         = 1
        Serial Num = 500605b008030030
        Vendor     = LSI Logic
        Model      = SGX-SAS6-EXT-Z
        FwVers     = 11.05.03.00
        strId      = mpt2sas:30:00.0

        Id         = 2
        Serial Num = 500605b00802fbc0
        Vendor     = LSI Logic
        Model      = SGX-SAS6-EXT-Z
        FwVers     = 11.05.03.00
        strId      = mpt2sas:40:00.0

        Id         = 0
        Serial Num = 500605b008071240
        Vendor     = LSI Logic
        Model      = SGX-SAS6-INT-Z
        FwVers     = 11.05.03.00
        strId      = mpt2sas:50:00.0

Total number of expanders: 2
        Id         = 1
        Serial Num = 50800200019f0002
        Vendor     = ORACLE
        Model      = DE2-24P
        FwVers     = 0018
        strId      = Primary
        WWN        = 5080020001a6b97e

        Id         = 0
        Serial Num = 50800200019f0002
        Vendor     = ORACLE
        Model      = DE2-24P
        FwVers     = 0018
        strId      = Secondary
        WWN        = 5080020001a6babe

Total number of PDs: 24
        /dev/sdl        LSI Logic         HDD  900gb slot:  0  exp:  0
        /dev/sdn        LSI Logic         HDD  900gb slot:  1  exp:  0
        /dev/sdah       LSI Logic         HDD  900gb slot:  2  exp:  0
        /dev/sdai       LSI Logic         HDD  900gb slot:  3  exp:  0
        /dev/sdaj       LSI Logic         HDD  900gb slot:  4  exp:  0
        /dev/sdak       LSI Logic         HDD  900gb slot:  5  exp:  0
        /dev/sdal       LSI Logic         HDD  900gb slot:  6  exp:  0
        /dev/sdam       LSI Logic         HDD  900gb slot:  7  exp:  0
        /dev/sdan       LSI Logic         HDD  900gb slot:  8  exp:  0
        /dev/sdao       LSI Logic         HDD  900gb slot:  9  exp:  0
        /dev/sdap       LSI Logic         HDD  900gb slot: 10  exp:  0
        /dev/sdaq       LSI Logic         HDD  900gb slot: 11  exp:  0
        /dev/sdar       LSI Logic         HDD  900gb slot: 12  exp:  0
        /dev/sdaa       LSI Logic         HDD  900gb slot: 13  exp:  0
        /dev/sdab       LSI Logic         HDD  900gb slot: 14  exp:  0
        /dev/sdac       LSI Logic         HDD  900gb slot: 15  exp:  0
        /dev/sdad       LSI Logic         HDD  900gb slot: 16  exp:  0
        /dev/sdae       LSI Logic         HDD  900gb slot: 17  exp:  0
        /dev/sdaf       LSI Logic         HDD  900gb slot: 18  exp:  0
        /dev/sdag       LSI Logic         HDD  900gb slot: 19  exp:  0
        /dev/sda        LSI Logic         SSD  200gb slot: 20  exp:  0
        /dev/sdb        LSI Logic         SSD  200gb slot: 21  exp:  0
        /dev/sdc        LSI Logic         SSD  200gb slot: 22  exp:  0
        /dev/sdd        LSI Logic         SSD  200gb slot: 23  exp:  0
==== END STORAGE DUMP =========


  • Execute the following command to monitor Shared Disk Status

[root@odanoden1 ~]# oakcli show disk
        NAME            PATH            TYPE            STATE           STATE_DETAILS

        e0_pd_00        /dev/sdl        HDD             ONLINE          Good
        e0_pd_01        /dev/sdn        HDD             ONLINE          Good
        e0_pd_02        /dev/sdah       HDD             ONLINE          Good
        e0_pd_03        /dev/sdai       HDD             ONLINE          Good
        e0_pd_04        /dev/sdaj       HDD             ONLINE          Good
        e0_pd_05        /dev/sdak       HDD             ONLINE          Good
        e0_pd_06        /dev/sdal       HDD             ONLINE          Good
        e0_pd_07        /dev/sdam       HDD             ONLINE          Good
        e0_pd_08        /dev/sdan       HDD             ONLINE          Good
        e0_pd_09        /dev/sdao       HDD             ONLINE          Good
        e0_pd_10        /dev/sdap       HDD             ONLINE          Good
        e0_pd_11        /dev/sdaq       HDD             ONLINE          Good
        e0_pd_12        /dev/sdar       HDD             ONLINE          Good
        e0_pd_13        /dev/sdaa       HDD             ONLINE          Good
        e0_pd_14        /dev/sdab       HDD             ONLINE          Good
        e0_pd_15        /dev/sdac       HDD             ONLINE          Good
        e0_pd_16        /dev/sdad       HDD             ONLINE          Good
        e0_pd_17        /dev/sdae       HDD             ONLINE          Good
        e0_pd_18        /dev/sdaf       HDD             ONLINE          Good
        e0_pd_19        /dev/sdag       HDD             ONLINE          Good
        e0_pd_20        /dev/sda        SSD             ONLINE          Good
        e0_pd_21        /dev/sdb        SSD             ONLINE          Good
        e0_pd_22        /dev/sdc        SSD             ONLINE          Good
        e0_pd_23        /dev/sdd        SSD             ONLINE          Good


  • Execute the following command to monitor ODA server enclosure

[root@odanoden1 ~]# oakcli show enclosure

        NAME        SUBSYSTEM         STATUS      METRIC

        E0_FAN0     Cooling           OK          3450 rpm
        E0_FAN1     Cooling           OK          3070 rpm
        E0_FAN2     Cooling           OK          3070 rpm
        E0_FAN3     Cooling           OK          3070 rpm
        E0_IOM0     Encl_Electronics  OK          -
        E0_IOM1     Encl_Electronics  OK          -
        E0_PSU0     Power_Supply      OK          -
        E0_PSU1     Power_Supply      OK          -
        E0_TEMP0    Amb_Temp          OK          19 C
        E0_TEMP1    Midplane_Temp     OK          27 C
        E0_TEMP2    PCM0_Inlet_Temp   OK          32 C
        E0_TEMP3    PCM0_Hotspot_Temp OK          38 C
        E0_TEMP4    PCM1_Inlet_Temp   OK          27 C
        E0_TEMP5    PCM1_Hotspot_Temp OK          36 C
        E0_TEMP6    IOM0_Temp         OK          38 C
        E0_TEMP7    IOM1_Temp         OK          45 C


Using ILOM CLI to Get the Hardware Status


  • Execute the following command to connect to ILOM and monitor Hardware Status

[root@odanoden2 ~]# ssh odanoden2-ilom
Password:

Oracle(R) Integrated Lights Out Manager

Version 3.2.8.25 r114493

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

Warning: password is set to factory default.

Warning: HTTPS certificate is set to factory default.

Hostname: odanoden2-ilom

-> show -level all -output table /SP/faultmgmt
Target                          | Property                             | Value
--------------------------------+--------------------------------------+---------------------------------------------------------

-> show -l all /SYS type=='Hard Disk'

 /SYS/DBP0/HDD0
    Targets:
        OK2RM
        PRSNT
        SERVICE
        STATE

    Properties:
        type = Hard Disk
        ipmi_name = HDD0

    Commands:
        cd
        show

 /SYS/DBP0/HDD1
    Targets:
        OK2RM
        PRSNT
        SERVICE
        STATE

    Properties:
        type = Hard Disk
        ipmi_name = HDD1

    Commands:
        cd
        show


Using ILOM GUI to Get the Hardware Status




Conclusion

In this article we have learned how to monitor various hardware components status on ODA nodes using oakcli and ILOM. ODA server comes with different hardware components and monitoring them is key for ODA availability.

Tuesday 17 July 2018

Oracle Database Appliance: Resize CloudFS ACFS File System Manually

During Oracle Database Appliance Deployment you can optionally configure CloudFS file system. The default mount point is /cloudfs and set to default size of 50GB. Oracle Database Appliance uses the Oracle Automatic Storage Management Cluster File System (Oracle ACFS) for database and virtual machine files storage. ACFS can only be used to configure shared storage file system on ODA. Oracle ACFS provides both servers with concurrent access to /cloudfs shared file system. The default size of 50GB may not sufficient and must be increased to store big files for business requirement.





In this article we will demonstrate how to resize the /cloudfs file system manually using ACFS commands.


Steps to resize the /cloudfs file system


Step 1: Login to node 1 as grid user the owner of Grid Infrastructure software

[grid@odanoden1 ~]$ id
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1003(racoper),1004(asmdba),1005(asmoper),1006(asmadmin)

Step 2: Verify the existing size of /cloudfs. Here is my case the /cloufs is 200GB and it was resized in the past from 50GB to 200GB

[grid@odanoden1 ~]$ df -h /cloudfs
Filesystem           Size  Used Avail Use% Mounted on
/dev/asm/acfsvol-23  200G  483M  200G   1% /cloudfs

Step 3: Set the ORACLE SID to +ASM1

[grid@odanoden1 ~]$ echo $ORACLE_SID

[grid@odanoden1 ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid

[grid@odanoden1 ~]$ echo $ORACLE_SID

+ASM1

Step 4: List the ACFS Mounts. Here we can see that /cloudfs volume is /dev/asm/acfsvol-23

[grid@odanoden1 ~]$ mount |grep asm
/dev/asm/acfsvol-23 on /cloudfs type acfs (rw)
/dev/asm/datastore-272 on /u01/app/oracle/oradata/datastore type acfs (rw)
/dev/asm/datastore-97 on /u02/app/oracle/oradata/datastore type acfs (rw)
/dev/asm/datastore-23 on /u01/app/oracle/fast_recovery_area/datastore type acfs (rw)

Step 5: Get the size of the volume /dev/asm/acfsvol-23

[grid@odanoden1 ~]$ /sbin/advmutil volinfo /dev/asm/acfsvol-23
Device: /dev/asm/acfsvol-23
Interface Version: 1
Size (MB): 204800
Resize Increment (MB): 64
Redundancy: high
Stripe Columns: 8
Stripe Width (KB): 1024
Disk Group: RECO
Volume: ACFSVOL
Compatible.advm: 12.1.0.2.0

Step 6: Resize the /cloudfs as follows. Here we are increasing /cloudfs by 50GB

[grid@odanoden1 ~]$ /sbin/acfsutil size +50g /cloudfs
acfsutil size: new file system size: 268435456000 (256000MB)

Step 7: Verify the new size of the volume /dev/asm/acfsvol-23

[grid@odanoden1 ~]$ /sbin/advmutil volinfo /dev/asm/acfsvol-23
Device: /dev/asm/acfsvol-23
Interface Version: 1
Size (MB): 256000
Resize Increment (MB): 64
Redundancy: high
Stripe Columns: 8
Stripe Width (KB): 1024
Disk Group: RECO
Volume: ACFSVOL
Compatible.advm: 12.1.0.2.0

Step 8: Verify the new size of /cloudfs file system

[grid@odanoden1 ~]$ df -h /cloudfs
Filesystem           Size  Used Avail Use% Mounted on
/dev/asm/acfsvol-23  250G  585M  250G   1% /cloudfs


Conclusion

In this article we have learned how to resize/increase the size of /cloudfs shared file system on ODA. The cloudfs file system is configured during the ODA deployment and it is set to 50GB which is not sufficient for storing the big files. The cloudfs is build using ACFS and it can be resized easily using ACFS commands.

Thursday 12 July 2018

How to Establish Connection to AWS RDS Oracle Database

Overview

Amazon Web Services is a comprehensive, evolving cloud computing platform provided by Amazon. Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.

In this article we demonstrate how to establish connection to AWS RDS Oracle Database Using SQL Client.

Prerequisites
  • Install Oracle SQL Client on your client machine (Windows/Linux)
  • Use can also use Oracle SQL*Developer tool to establish connection


Procedure to Connect to AWS RDS Oracle Database

  • Put tns entry in tnsnames.ora file client machine. 
    • Review the blog article on how to find end point details of AWS RDS instance at http://netsoftmate.blogspot.com/2018/07/how-to-find-hostname-from-aws-console.html

nsmprd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nsmprd)
    )
  )

  • Check connectivity to AWS RDS Oracle instance using tnsping utility
C:\Users>tnsping nsmprd

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-JUN-2018 16:19:34

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle12c\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
TNS-12535: TNS:operation timed out




Follow the steps below to resolve the timed out issue. 

  • Check security group and add rules to VPC security group. While Database instance creation if it is default security group then this instance firewall prevents connections.

To know more about security group please go through following link
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html

Add rule to security group

  • Login to console and click on VPC under Networking and content delivery
 



  • Under vpc dashboard navigation pane click on security group 



  • Select the security group for update.


  • Click on inbound rules and edit to add new rules.


  • Click on add another rule.


  • Select from drop down list


  • Choose oracle port in in-bound traffic


  • Click on save

Now check the connectivity again using tnsping

C:\Users>tnsping nsmprd

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 13-JUN-2018 18:37:22

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle12c\product\12.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = nsmprd.cjhtbujgykil.us-east-1.rds.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
OK (1500 msec)

We can see that the tnsping is now successful.


  • Now Establish the connection to AWS RDS Oracle Instance

C:\Users>sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 13 18:37:30 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: nsmsystem@nsmprd
Enter password:
Last Successful login time: Wed Jun 13 2018 14:31:06 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,INSTANCE_NAME,OPEN_MODE,HOST_NAME,DATABASE_STATUS,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,v$instance;

NAME      INSTANCE_NAME    OPEN_MODE            HOST_NAME                      DATABASE_STATUS   LOGINS     UP TIME
--------- ---------------- -------------------- ------------------------------ ----------------- ---------- -------------------------
NSMPRD    NSMPRD           READ WRITE           ip-10-1-2-24                   ACTIVE            ALLOWED    10-JUN-2018 09:27:22




Conclusion

In this article we have learned that how to establish connection to to AWS RDS Oracle Instance using SQL Client and also we have seen how to add rules to the VPC security group.





By
Name : Omer
Designation : Senior Database Engineer
Organization: Netsoftmate IT Solutions
.

Comparing Oracle Database Appliance X8-2 Model Family

September 2019 Oracle announced Oracle Database Appliance X8-2 (Small, Medium and HA). ODA X8-2 comes with more computing resources com...