Connecting to EBS Database in Oracle Cloud using SQL Developer

10
3099

Based on some user requests, I am posting this to show to how to connect to Oracle EBS Database running in Oracle Cloud. I have already done below:

  • Launched EBS 12.2.9 Instance using Oracle Image in Oracle Cloud
  • Completed running hostname apps.example.com , running updatehosts.sh
  • Started the EBS database

You can see detailed instruction to Launch EBS instance in my previous post:

[oracle@apps 19.0.0]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 24 15:57:36 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS

ebscdb OPEN

Firstly, open the Database Port from the VM using opc user as below:

[opc@ebs1229 ~]$ sudo firewall-cmd --permanent --add-port=1521/tcp
success

Now, add a security rule to access 1521 port in the specific subnet

Now open the SSH tunnel port as below using putty or mobaxterm

Use SQL developer to connect to the localhost:1521 using the credentials as below. Remember that the new image of EBS 12.2.9 is coming with 19c (CDB) so you need to connect to the PDB i.e. ebsdb not ebscdb.

10 COMMENTS

  1. Hi Vishnu.
    small help needed. i have installed oracle 12c database in oracle free tier cloud not able to connect with sql developer.
    my hostname details-
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=instance-20191224-1131.subnet.vcn.oraclevcn.com)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary…
    Service “cdb2” has 1 instance(s).
    Instance “cdb2”, status READY, has 1 handler(s) for this service…
    Service “cdb2XDB” has 1 instance(s).
    Instance “cdb2”, status READY, has 1 handler(s) for this service…
    Service “pdb2” has 1 instance(s).
    Instance “cdb2”, status READY, has 1 handler(s) for this service…
    The command completed successfully

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ———- —————————— ———- ———-
    2 PDB$SEED READ ONLY NO
    3 PDB2 READ WRITE NO
    i have added the port 1521 in ingress rule to allow.

    • Sorry! Missed your comment.
      1. Open port
      2. Create SSH tunnel using localhost:1521. Try telnet localhost 1521. If this works then you are ready to connect.
      3. Then use SQL developer to connect

  2. Hi Vishnu,

    Thanks for great tutorial. I tried to connect to ebs vision demo 12.2.9 database. I follow your step, but when it come to open to ssh tunnel port : ssh opc@xxx.xx -i .ssh/id_rsa -L 1521:localhost:1521 I got Error.
    This is the Error :
    Warning: Identity file .ssh/id_rsa not accessible: No such file or directory.
    ssh: Could not resolve hostname : Name or service not known.

    do you have any idea why I got this error? Please advise.

    many thanks for your answer.

  3. Hi Vishnu,
    I saw you use the user id apps in the sql developer. May I know what is the default password or how to reset the password?

  4. Hi Vishnu, thanks for the guide. it is very useful. However, i have a problem here. When i follow your guide, sqlplus command is not found. May I know how could we enable it?

  5. Hi vishnu,
    Thank you very much for all of the details in this tutorial. I managed to solve most of my issues. Thank you very much.

    Now I am facing the issue below

    I am able to connect sqlplus using apps/apps. In our case, I need to apply a patch to the instance.
    As a part of the patch pre-req steps, i need to run the script adgrants.sql. This script has to be run as a sys user. So if I connect using sqlplus as sysdba it is asking for a password. I am not able to bypass it. The system never asked for a password when i did the install.

    How do i run the script ?

    Thank you once again
    Uma

LEAVE A REPLY

Please enter your comment!
Please enter your name here