Home
July 28, 2013

Using Oracle 11g on Amazon EC2

Goals

  • Get a working instance of Oracle 11g online for future use as a PeopleSoft Campus Solutions database server.
  • Ensure Oracle Net connectivity on port 1521.
  • Ensure database server comes back online after instance reboot.

Background

  • Originally I was going to set this up on an LPAR running AIX 7.1 at LPARBox.com. After evaluating that instance, it was too cost prohibitive for my needs. I intend to load a ~19GB database into Oracle 11g, which would have cost me over $200/mo with a CPU affinity of only 0.3.
  • I eventually found an AMI provided by Oracle running Oracle Linux that has Oracle 11g installed on it. The rest of this post covers configuration of an instance of this AMI.

Procedure

  • Added instance of ami-3d739c54 as m1.small.
    • Security group set to a newly created group called ORACLE_DB_SERVER (opened up TCP 22 and ICMP to all).
    • This instance requires you to wait longer than usual before it’s ready to accept SSH connections.
    • Although it’s not required, I assigned an Elastic IP to this instance to simplify connection strings.
  • After SSH'ing in:
    • Agreed to license prompts with y
    • Set new UNIX password to <your_new_UNIX_password> when prompted.
    • Said n when prompted to create new database (we’ll do this after additional configuration).
    • Opened ~/readme
    • Ran /usr/bin/passwd root; changed password for UNIX user root to <root's_new_UNIX_password>
    • Ran /usr/bin/passwd oracle; changed password for UNIX user oracle to <oracle's_new_UNIX_password>
    • In /etc/ssh/ssd_config, changed PasswordAuthentication no to PasswordAuthentication yes. This is necessary to allow future SSH connections with the oracle UNIX user rather than root.
    • Ran sudo service sshd restart
    • Opened a temporary terminal window to login as oracle; login attempt was successful.
    • Returned to the terminal window connected to the instance as root.
    • Ran /home/oracle/scripts/run_dbca.sh to begin database setup.
    • Selected default disk device (option 1) to store datafiles and redo logs.
    • Selected default disk device (option 1) to store flash recovery area files.
    • Prompted for Oracle SID, typed ENTPSDB
    • Prompted for SYS admin account password, used <redacted_password>
    • Prompted for SYSTEM admin account password, used <redacted_password>
    • Prompted for DBSNMP admin account password, used <redacted_password>
    • Prompted for SYSMAN admin account password, used <redacted_password>
    • Prompted for ADMIN admin account password for Apex, used <redacted_password>
    • Waited as database was created (the entire process from start to return of control to the shell will take 10 to 15 minutes).
      • Eventually the process informed me that Enterprise Manager was started on port 1158. I went to https://<ip_of_your_instance>:1158/em and logged in using SYS as the username and the account password for SYS that was set above. It’s also important that you connect as SYSDBA.
    • Important: The run_dbca.sh process eventually prompted me to setup Backup Manager, which I didn’t want to setup but I also needed the process to finish. I entered bogus credentials for Backup Manager, which it accepted, and eventually the process finished up successfully.
  • To verify the ability to connect to the newly created ENTPSDB, I installed SQL Developer on a Windows machine and connected to the remote database using the following configuration:
    • Username: sys
    • Password: <account_password_for_SYS_set_above>
    • Connection Type: Basic
    • Role: SYSDBA
    • Hostname: <ip_of_your_instance>
    • Port: 1521
    • SID: ENTPSDB
  • Using the above credentials, I was able to establish a connection to Oracle 11g on the instance.

  • Also on the Windows machine, I opened a command window and cd'ed to C:\oracle_instantclient, where I had extracted the contents of the (64-bit) versions of the Basic Lite and SQL *Plus Oracle Instant Client packages. Using the following command (all one line), I was able to successfully connect to the Oracle 11g instance:

    sqlplus SYS/<account_password_for_SYS_set_above>@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip_of_your_instance>)(PORT=1521)))(CONNECT_DATA=(SID=ENTPSDB)))' as sysdba

  • Running SELECT * FROM ALL_USERS; from the sqlplus session established above successfully returned the appropriate results.

Addendum

  • After the initial install, I rebooted the instance, feeling that some services would not start or work correctly on reboot. I was correct; connecting via SQL Developer failed. I found that I needed to modify listeners.ora in $ORACLE_HOME/network/admin (important note: you must login as the oracle UNIX user when doing this; the shell will not load the $ORACLE_HOME environment variable for a user other than oracle). I prepended the following text to listeners.ora:
# MQUINN 07-20-2013
# BEGIN MODIFICATION BLOCK
# Added this block because after install
# and first post-install restart, I could not
# connect on port 1521 due to an unrecognized
# SID error.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ENTPSDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=ENTPSDB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=ENTPSDB)
    )
  )
# END MODIFICATION BLOCK
  • I then bounced lsncrtl using lsnrctl stop followed by lsnrctl start.

  • That resolved the issue and I could connect via SQL Developer again.

  • Remember to login as oracle or su - oracle in order to access the environment variables that are only available to the oracle user.

  • Enterprise Manager does not start at start-up; it’s trivial to add it to rc.d but EM consumes alot of resources. For my situation it’s best to start it manually on an as-needed basis. To so (as the oracle UNIX user), use emctl start dbconsole.

← return home