Microsoft has done a great job of documenting how to create a VM with the appropriate VM image and Oracle version, then how to log in, startup the listener and create the database. I just have some enhancements I’d like to make to it, hoping to help it move up one level.
All instructions provided by the Microsoft documentation show how to connect to the database using a bequeath, (BEQ) connection. This is done by the following command, using SQL Plus, (or similar):
sqlplus / as sysdba
It bypasses the need for a connection string, only requiring the SID to be set at the environment level:
export ORACLE_SID=
This information, for Oracle DBAs, is often gathered from a running database server executing the following command:
ps -ef | grep pmon
For the standard database user, accessing an Oracle database in a standard capacity, the connection is performed via TNS, (Transparent Network Substrate). This requires a running Oracle listener to listen for incoming connections, then connecting to information stored in the sqlnet.ora and the tnsnames.ora files. There are a couple of additional steps that either an Oracle DBA without Azure experience may not know or an Azure specialist without Oracle experience might miss that I’m going to document to make it easier, which can result in a chicken before the egg challenge that we’ll take on in this blog post.
Using the documentation and performing the steps to create the VM, but stopping at the “Create the Database” step, (no logging into the VM, su over to the Oracle user and starting the listener.) Instead, lets log into the Azure Portal and go to the VM you’ve just created.
Why do you want to go to the portal instead of starting the listener, (and if you’ve started the listener, why do you want to shut it back down and go to the portal)?
If you do log into the VM and start the listener, you could verify the name of the host that is set by default as part of the VM creation by querying the status of the running listener:
https://dbakevlar.com/wp-content/uploads/2019/08/vmblog2-1-300x52.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog2-1-768x134.jpg 768w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog2-1.jpg 1673w" sizes="(max-width: 800px) 100vw, 800px" />
Host=oracledb2.qzvde3m5s4y….. what?? cx.internal.cloudapp.net? Aw, hell no.
Not the best of names and not something you want to have for any length of time if you want to try to connect with TNS. So you may be asking how to fix this as an Oracle DBA?
Although there is a way to do this as part of the azure VM creation, if you’re following the directions, it wouldn’t have occurred, so let’s log into the portal and address the DNS issue.
In the top section of the overview, last one on the right hand side, along with pertinent information about the VM, Private and Public IP Address, you’ll also see the option to configure the DNS Name:
https://dbakevlar.com/wp-content/uploads/2019/08/vmblog1-300x98.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog1-768x250.jpg 768w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog1.jpg 1440w" sizes="(max-width: 800px) 100vw, 800px" />
Click on Configure and then you’ll be taken to the page to configure it.
Again, make sure your Listener is shut down, as it relies on the updates we’re making to understand it’s “location”, so this will save you from having to run a kill command on the Listener PID for force it to shutdown.
https://dbakevlar.com/wp-content/uploads/2019/08/vmblog4-300x77.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog4-768x196.jpg 768w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog4.jpg 1419w" sizes="(max-width: 800px) 100vw, 800px" />
The portal will now look similar to the following, (although you will have a private and public IP address showing, along with the DNS Name):
https://dbakevlar.com/wp-content/uploads/2019/08/vmblog5-300x68.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog5-768x175.jpg 768w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog5.jpg 1141w" sizes="(max-width: 800px) 100vw, 800px" />
Go back to your ssh terminal, log into the VM and check the host name by typing in the following command :
uname -a
Now let’s configure the /etc/hosts file with the new information. This must be done as the server admin login you use to ssh into the host. It has the sudo privileges needed to perform these steps:
sudo vi /etc/hosts
You should see the following entries:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
Append the following information on a new line to the file:
Example:
40.17.124.235 oracledb2 oracledb2.eastus2.cloudapp.azure.com
Save the file, (ESC, :wq) and SU, (switch user) to Oracle.
https://dbakevlar.com/wp-content/uploads/2019/08/vmblog6-300x189.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/08/vmblog6-768x484.jpg 768w" sizes="(max-width: 819px) 100vw, 819px" />
Much better!!
Now follow the directions in the documentation to create the database with DBCA, (the Database Configuration Assistant)
Once you’ve completed all the steps from the documentation, we can then return to complete the TNS configuration.
then proceed to setting up the TNS connection.
vi tnsnames.ora
Format of the entry, placing you alias/database/host shortname in where appropriate:
= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )
Once finished, type in :wq to write to the file and quit.
Let’s say you don’t know what the service name is for your database, (again for those Azure folks that are just starting to acclimate to Oracle…) There’s a few different ways to do this. You can use hour bequeath login to verify:
sqlplus / as sysdba SQL> select name from dba_services 2 where name not in ('SYS$BACKGROUND','SYS$USERS') 3 and name not like '%XDB';
OR gather the info from the listener:
lsnrctl services
Both of these actions must be performed as the ORACLE user and the first one requires the ORACLE_SID to be set for the syntax used.
Now let’s create the sqlnet.ora file:
vi sqlnet.ora
Format of the entry:
NAMES.DIRECTORY_PATH= (TNSNAMES)
Again, type in :wq to write and quit when completed.
This tells the listener to use TNS as it’s choice to connect. There are numerous other options for connections, but I haven’t included them in this post. Let’s test our connection now:
sqlplus
Enter in the password for the user and you should connect.
Now your Oracle VM is set up to support basic TNS connections to the Listener. If there are more advanced connections security setup, such as Kerberos, then this will have to be installed and configured on the Linux VM, but that’s a post for another day.
I’m headed out this week to speak at NoCOUG, (Northern California Oracle User Group) on Thursday and will then be heading to Louisiana to speak at SQL Saturday Baton Rouge this weekend. Looking forward to seeing everyone at both these events and customers inbetween!
Tags: azure, oracle, TNS on Azure
Recent comments
1 year 46 weeks ago
2 years 6 weeks ago
2 years 10 weeks ago
2 years 11 weeks ago
2 years 15 weeks ago
2 years 37 weeks ago
3 years 5 weeks ago
3 years 34 weeks ago
4 years 19 weeks ago
4 years 19 weeks ago