1z0-082 Oracle Database Administration – Configuring the Oracle Network Environment

1. Establishing a connection and session

Hi, welcome back. Welcome to this new chapter. In this chapter we will talk about configuring the Oracle network environment. And this is related for the listener, local listener and default listener. I think that you know a lot of information about this. But in this chapter we will go go deep and deep to understand more about this concept. So now let’s start by establishing a connection and session. So for example, I have a client machine and this client trying to access a service called PDB One. Example. So this is a service. Now this is the database server.

I have the listener, I have the innocence and I have the database. In the database, I have the root container. And also, for example, I have a bloggable database, PDB One. And the service related for this bloggle is PDB One. Example. Example. com is the domain name. Okay, very nice. So now let’s understand the steps for establishing a connection and session. In step number one, the listener receives connect packet. So I have here the listener and I have the client machine. The client machine will send a connect packet. This connect packet contain information about the host, about the protocol, and about the database service. The Oracle listener will receive this packet. So this is the first step. And step number two, if the service name is valid, then the listener creates a dedicated server process. But if the service is not valid, then it will return error, right? So it’s very simple. So this is the second step. In third step, the listener connect to the server process and pass the initialization information to this service process. Okay, very nice.

So what is the meaning of initialization information? It contains the authentications, for example, the username and the password for this connection, right? In step number four, the server process shake the authentication. If okay, then it will create a session. But if the authentication is not correct, for example, the username and password is not correct, then in this case it will return an error. That’s it, it’s very simple. Now finally, in step number five, the service process is acting like agent. So this server process will serve the client. So it will be responsible for any request from the client, like any SQL statement, select, update and read. So that’s it.

So this is the basic things you have to know about establishing a connection and session. Now remember that the server process responsible for many things. You can read this information and I told you about this information before, but I just want you to focus about that. The server process is responsible for parsing and running any SQL statement issued throughout the application and also returning results to the user process in such a way that the application can process the information. So you can read this information for the exam. And in the next lecture we will talk about the default listener. Thank you for listening and see you in the next video.

2. The default listener

Hello everyone and welcome back. In the previous lecture we understand how to establish the connection and to how to establish the session. In this lecture we will talk about the default listener and we understand that the listener is listening to any request from the client. Now let’s read this information and understand it 100%. 1st thing during an Oracle database installation, oracle universal installer launches oracle net configuration assistant. So it’s called net CA and to create a local listener named Listener. So this is a default Listener. Okay, very nice. Now Listener is automatically populated with available database services through a feature called dynamic service registrations. And this is very important. So actually we have a process called L ridge and this process is registering all the services for the listener. Okay, very nice. Now actually dynamic service registration started in Oracle nine I. Before that it was a concept called static registrations and we will talk about this later. But actually this feature is very nice and very simple and it reduced many efforts for the database administrator. OK, now the default listener listens to the following TCP IP protocol.

So the protocol will be TCP and this is the host and the default port number is 1521. Now a very important note, without any configuration you can access your database instance immediately through Listener. So this means that listener is not important after Oracle nine I, the listener is important if you want to use another port and advanced feature. But even if you don’t have any listener, the default listener will work and it will listen to the TCP protocol on port one five to one and we will see that in the example. So if the listener name is Listener and it cannot be resolved, a protocol address of TCP IP and port number of one five to one is assumed. Okay, very nice, now let’s go to the virtual machine and do some exercises. I will go to the virtual machine, I will log in as Oracle user and I will wait a little. Okay, very nice. So now I will make rightclick and open a terminal.

First thing I will make change directory CD then I will make Oracle underscore home. I will press enter. So currently I am on the Oracle home. If you make now PWD, you will find that you are currently in this path u zero one app oracle product 18 DB one. I will make LS, so I can find many directories. But now I will go to a directory called network. So I will make CD network. So currently I am in the network, I will make LS. So I will find admin and install and many directories. I will go to admin CD admin. So currently I am in the admin. I will make LS.

So you will find that I have the following files. Now I want to view this file which is listener aura. So I will make simply cat then listener aura and I will press Enter. So you can see this is the default listener. I have a listener called listener and this listener is listening to any protocol TCP and this is the host and the port is 1521. So this is the default and also I have a protocol called IPC. Okay very nice. So this is the default listener. Okay very nice. You can go also to home then you can go to other location computer one app Oracle products 18 DB one. Then you go to network then admin and you can see that I have the listener aura and also I have the Tnsnames aura. The listener is responsible for receiving client requests and the TNS name aura we already know about this information. Okay, now I will go to the terminal again and I will make SQL plus as SDBA.

So I will wait a little. You can see that I am connected to an ideal innocence. So I will make now startup. Now a very important note. Sometimes after the startup you will find also the listener is down so there is no relation between the database and the listener. So anyway I will make a startup and after startup I will do listener start. So I will wait a little. So now the database is up. Now I will make exit then I will make Lsnrctl and I will make start. So I will start the listener. Now if the listener has already started you will find error. Anyway this information is very nice. So you will find that the listener name is listener and this is the version for the listener and this is the start time and up time. It does mean that the listener is now started only started. Now if you repeat the comment again and make for example status, you will find now this is all the services related for the listener.

We said before that sometimes the L rig needs 60 seconds in order to register this information. So the listener register all these services. Okay very nice. So you can see here in the description that the listener is listening to this protocol TCP and this is the host and this is one five to one. So this is the default and you can see here the listener parameter file. So this means that this is the original file for the listener listener ora okay very nice. Now what I will do now I will go to the listener utility. So I will make only lsnrctl I will press enter. So currently now I am in the listener utility I can make help and you can find all this information for example start, stop status services and a lot of things and also show for example if I make show now it will tell you that after show you have to give this information. For example you can make show current listener. So I just want to see what is the current listener. Now I will make now show. Then I will take this as copy and I will paste it here and I will press Enter. So you will find that current listener is listener.

And this is the same of the documentation I show you. If I go to the documentation, you can see that Oracle net configuration assistant create a local listener named Listener. And this is the default. Now I will pack again to the virtual machine. I will make now for example status. So you can see now here all the services. Okay, I can make stop. So currently I am stopping the listener. So this means that I will not receive any incoming connections from the client.

Okay, I will make now start. And you can see now that it started. And you can see the listener support no services because the L rig need around 60 seconds. But you can do for example sq all the plus as sys DBA sorry, I have to exit first. Then I will make SQL plus as Sysdba. Then I will make alter system register. Then I will exit, then I will make now Lsnrctl status. So you can see now all the services. So we use the alter system register in order to not to wait 60 seconds and order the listener to register this information. So this is the basic information I want you to know today in the next lecture we will do a nice exercise. Thank you and see you in the next video.

3. The default listener exercise

Hello everyone and welcome back. In the previous lecture, we understand the default listener. In this lecture we will do a very important and nice exercise. I just want you to focus with me 100% in this lecture. I just want to prove to you that the listener is is not important in order to connect to the Oracle database in case you need to use the default port and the protocol TCP IP. Now, first thing, I am in this path, which is use one app, Oracle product, 18 DB. Underscore one network admin, I will make right click and open a terminal. Now, if I make this command listener status, you can see that the service is up and running. And you can see here that the listener parameter file. And you can see this is the path for this is the path for the listener. So you can see now I can see listener ora okay, so this is the listener parameter file. Now, what I will do, I will close this terminal. I will go to the listener and I will make right click and I want to rename it. For example, I will put in the beginning, for example X-Y-Z and I will rename it. Now. I will open a terminal and I will make now listener stop.

Then I will make listener start. You can see now that I have only listener log file. But I don’t see the listener file itself right? Because I already changed it to XY is it? Now you can see that the listener support no services. If you wait around 60 seconds, you will find that the listener registered the services. So, I will make now listener status. Still I don’t see because I am running the listener for 27 seconds only. Just wait a little and you will find that the listener will register all the information. Just wait a little, I will try again still. Because now currently 74 seconds. Wait a little, I will try now. Wow. So the services is up and running. So even the client can connect without any issue. Very nice. Now to prove that for you, I will go to my original machine. If I go to the SQL developer and if I go for example to this connection sys user, I will make right to click properties. So you can see test the port 1521. And this is the service name. If I make test, you will find success. So without the listener ora I can connect using the default port and the default TCP protocol. Okay, very nice. Now let’s back to the virtual machine. Now the question now how the listener register the services? Actually, this is called dynamic registrations. And this is because we are using something called local listener. Now, if I go now to SQL plus as sysdba, if you make the following command which is show parameter local and I will press enter, you can see that I have a local listener. And the value is listener underscore ORCL actually the default name is listener underscore the instance name.

But actually, where is this information? It’s very simple. Actually, Oracle put this information in the file called Tnsnames Aura. So actually, this file is very important for the local listener. If you open the TNS names at Aura on the server level, you will find that I have this code. This code is for the local listener. And this is responsible for registering the services. Okay, so that’s why I find this code. Okay, very nice. Now what I will do, I will do something very strange. Now I will go to the SQL Plus. I will make now alter system, alter system sit. I will take this as copy and I will paste it equal. Then I will put empty string like this and I will press Enter. I will make again show parameter local. So you will find no value in the local listener. Now let’s try to stop the listener and open it again. I will make now exit. Then I will make listener stop. Then I will make listener start.

You can see the listener is started, but the listener support no services. If you wait around 60 seconds, you will find that the listener will register all the services without any issue. Why? Because again, a default. We have a default board and we have a default TCP. Now, for example, if I take this and I make copy, and if I go to Chrome, for example, Internet to browser, I will go to Google and I will search for local listener. For example, 12. 2 I’ll press enter. If I open this, you can see a very nice information that the default value for the local listener is TCP IP and the port is so that’s why it’s working fine. If I pack again to the virtual machine and make now listener status, I can see the services is up and running. So this means if you need to use the default port and the default TCP IP, no need for the listener and also no need for the local listener.

Okay, very nice. So now I will go to SQL plus as says DBA and I will make now show parameter local. I will make now alter system sit, paste local listener equal. And I will take it from here to make sure that I am taking careful to make sure that everything is okay. I will take it copy, then I will close, then I pack again here and I will paste and I will make now show parameter local and everything is working fine. So you can stop the listener and start it again. So, this is the lecture for today. In the next lecture we will see how to use another port. Okay, very nice. Thank you and see you in the next video.