Automate Oracle DBA Login with a Custom Alias on Linux

Managing Oracle databases on an Amazon Linux 2 EC2 instance often requires switching users and setting environment variables before running SQL*Plus. This guide will walk you through setting up a custom alias (s) that lets you instantly connect to Oracle as sysdba without manual switching.

Note– You can also use this for all other Linux OS.

Step 1: Log in as ec2-user

First, log in to your Amazon Linux 2 EC2 instance using SSH:

[ec2-user@ip-172-31-46-238 ~]$ uname -a
Linux ip-172-31-46-238.ap-south-1.compute.internal 5.10.234-225.910.amzn2.x86_64 #1 SMP Fri Feb 14 16:52:40 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
[ec2-user@ip-172-31-46-238 ~]$
[ec2-user@ip-172-31-46-238 ~]$ whoami
ec2-user
[ec2-user@ip-172-31-46-238 ~]$

Step 2: Grant Sudo Access to ec2-user

To allow ec2-user to switch to oracle and run SQL*Plus without a password, run:

[ec2-user@ip-172-31-46-238 ~]$ whoami
ec2-user
[ec2-user@ip-172-31-46-238 ~]$ sudo visudo

Add the following line at the end:

ec2-user ALL=(oracle) NOPASSWD: /bin/bash, /u01/app/oracle/product/19c/db_1/bin/sqlplus

Note: Before adding this line, verify your SQL*Plus version and correct path by running:

[oracle@ip-172-31-46-238 ~]$ which sqlplus
/u01/app/oracle/product/19c/db_1/bin/sqlplus

If your SQL*Plus is located in a different directory, update the path accordingly in the sudoers file.

Save and exit the editor.

For your reference:

Step 3: Modify the .bashrc File for ec2-user

To make the alias work every time you log in, edit the .bashrc file:

Add the following alias at the end of the file:

alias s="sudo -u oracle bash -c 'source /home/oracle/.bash_profile; read -p \"Enter ORACLE_SID: \" ORACLE_SID; export ORACLE_SID; echo \"Connecting to ORACLE_SID=\$ORACLE_SID...\"; sqlplus / as sysdba'"

Explanation: What this above command is doing exactly…

1. alias s=”…”
   • Creates an alias named s, so when you type s, it executes the enclosed command.
2. sudo -u oracle bash -c ‘…’
   • Runs the command inside single quotes ('...') as the oracle user.
   • -u oracle means switch to the oracle user.
   • bash -c ‘…’ runs the given commands in a new bash shell.
3. source /home/oracle/.bash_profile;
   • Loads the Oracle environment variables defined in .bash_profile for the oracle user.
4. read -p \”Enter ORACLE_SID: \” ORACLE_SID;
   • Prompts the user to enter the ORACLE_SID (Database System Identifier).
   • Stores the user input in the variable ORACLE_SID.
5. export ORACLE_SID;
   • Exports the entered ORACLE_SID so that SQL*Plus uses it.
6. echo \”Connecting to ORACLE_SID=\$ORACLE_SID…\”;
   • Displays a message indicating which ORACLE_SID is being used.
7. sqlplus / as sysdba
   • Launches SQL*Plus in SYSDBA mode, allowing administrative database operations.

How It Works:

• Every time you type s in the terminal:
  1. It switches to the oracle user.
  2. Loads Oracle environment variables.
  3. Asks for the ORACLE_SID.
  4. Exports it for the session.
  5. Displays which SID it’s connecting to.
  6. Opens SQL*Plus as sysdba.

This ensures that even if multiple databases are running, you manually select the correct one each time! ✅

Step 4: Apply the Changes

After saving the file, apply the changes:

source ~/.bashrc

Step 5: Test the New Alias from ec2-user

Now, simply run: s and see the magic…

1. When database already up and running.

2. When database is down then also it will ask for Oracle SID.

Conclusion

Now, with just a single command (s), you can quickly access SQL*Plus as sysdba without manually switching users or setting environment variables. This saves time and ensures consistency in database administration on Amazon Linux 2.

Let me know if you need any further refinements!