1z0-082 Oracle Database Administration – Database Startup, shutdown and connections

13. Moving from container to pluggable

Hello everyone and welcome back. In the previous lecture we learned how to connect as Sys. As Sysdba. In this lecture we will connect also as Sysdba and we will do some nice things. We will learn how to move to the Blogger build databases. Remember that Oracle twelve C, 18 C, that we have the concept of multitenant database. I have a big database, a rule database. Inside this database. I have bloggable databases. Okay, remember this very well.

So now I will log in as Oracle user. I will make right click and I will open a terminal. Now I will log in SQL plus as Sysdba. I will press enter. So I already log in to the Oracle database. I will make showcon name. So the container now I am now on the root database. I just want to know the Blogger build databases that I have in my database. I will write this command show PDPs and I will enter. So currently I have two bloggable databases.

The first one has container ID too. PDB dollar sign seed. And the open mode is read only. And this database is a template. And I told you about this before. So we use this database in order to create another bloggable database. So it is a read only. We don’t do anything inside this bloggable database. Okay, very nice. I have another bloggable database container ID, three orclpdb. And remember that when we do the installations, we give this name in order to create bloggable database in the installation phase. Okay, so the status for this now is mounted. So this means it’s closed, so no one can login to this database. So now I just want to make this database open. So the database administrator, he will execute the following command altar Blogger database and he will mention the name ORCL PDP and I will make open. So alter Bloggable database ORCL PDB open. I will press enter.

Now you will find now that the bloggable database altered and it is now open. I will make the following comment show PDPs again and you will find now that the bloggable database now is open for reads and write. Okay, another comment that the database administrator can do. He can make alter bloggable database all open. This means that the database administrator will open all the pluggable database for read and write. I will press Enter and you will find the command has been executed. Now if I want to close the Bloggabel database orclpdb it will be the same alter Bloggabel database orclpdp and I will make a close and I will press Enter.

So this means that I close the Blogger bill database orclpdb I will make sure, I will make sure PDBs. And you will find now that it is mounted, another way to open the bloggable database or Rclpdb by moving to this bloggable database. So how the database administrator move from the container database to the pluggable database? The command is very simple. I will make alter session set container equal. Then you will mention the bloggable data name. So I will make Orclpdp and I will press Enter and you can see now session altered. I will make now show con name and you will find now that I am on the bloggable database orclpdb so remember that the status for the blogger database is closed or mounted, so I want to open it. I can do the following comment alter bloggable database or Pin.

In this case, no need to mention the bloggable database name because I am already in the bloggable database. I will press Enter and you will find now that the Blogger Bell database has been altered and it’s now open. Now I want to back again to the root. I will make showcon name and you will find now that I am on the root database, don’t worry, it will be a full chapter. Talking about bloggable database, how we can drop bloggable database, how we can create a new bloggable database. But this information is very important, at least to know how to move from bloggable to blogger bill, how to open it, how to close it. So this information is enough for today. Thank you for listening and see you in the next video.

14. Starting and stopping the listener

Hello everyone and welcome back. I hope that you are doing fine. In the previous lecture we understand how to start up the listener and how to stop the listener. And also we learn the listener status in order to see the running services. Okay, very nice. In this lecture we will learn something called Easy Connect. But before that, I want to give you a very nice information for the exam. Now I login as Oracle user. You can see now that I am logging as Oracle user, I will make right click open terminal. Now I told you before that the Sys user can login using OS authentication. So he will make SQL plus as sigma.

And now I am connected. But there is important note. In order the database administrator to log in using this method, which is the OS authentication, the Sys user should be logging on the same machine that the Oracle instance exists. So this condition is met. The second condition, the Oracle OS user should be in a group called DBA. I will exit from here and I will make ID Oracle. You can see now that the Oracle user is a member of a group called DBA.

So these two conditions should meet an order. The database administrator to login using OS authentication, which is this method. Remember this for the exam details. Okay, very nice. Now I will make it clear and I will connect again SQL plus as Csgba and I am connected. Now I will make disconnect from the SQL. I will make this connect, sorry, this connect.

Okay, very nice. So I am disconnected from the Oracle database. I want to connect again. But now I will use another method called easy connect. The Easy connect is a simple connections. You will make the following connect. Then you will put the user the password welcome. For example, because I changed the user or I changed the password for CIS in the previous lectures to Pwelcome. Then you will make at and here. Now you will mention the hostname. So my hostname is test. Then you will make colon 1521.

You will mention the port number. Now you will mention the service name which is. So again user password at hostcollon, the port number I will press Enter. It gives me this message connection as sys should be sysdba or sysoper. So I will repeat it again connect sys 1521 as sysdba and I will press Enter. So I am connected. So in this way I provide the user, the password, the hostname, the port number and the services exactly the same way in the SQL developer that I show you in the previous lecture. So I will minimize this. If I open my SQL developer and I try to make a new connection and I will put Sys, the password will come. I will come here and I will make the role Sysdba. And I will put the hostname test and I will put the port number 1521 and the service name is Orcl. Now if I make test connection you will find success. Remember that this method needs the listener to be up and running.

Because I am depending on the host name and port number and the services. So in this method you should start up the listener. So this means the listener is already up and running. I will pack again to the virtual machine and I will try to connect for the Bloggable database. So remember that the services for the Bluggable database is Orclpdb. com. To make sure I will exit from here. And now I will teach you something very important I told you before. In order to see the listener service, you can make Lsnrctl. Then you will make for example services. Right, another method. Just write lsnrctl and press enter. You can see now that this is called listener utility. If you make help, you will find all the command that you can do with the listener. You can start, you can stop, you can make status, you can make services. So now I will write services. I’ll press enter.

So I have a service called Orcl . And also I have a services called Orclpdb. com. Okay, very nice. So now I will exit and I will make SQL plus as Sysdba. Now I will disconnect. Now I will make connect sys 1521 orclpdp. com as saysdba and I will press Enter. So I am connected. So in this lecture we learn how to connect using easy connect. And remember that the Ezconnect needs the listener to be up and running. To prove that for you, I will make for example exit and I will make now listener stop. Now I will log in as Sysdba. I will log in without any issue. Why? Because the Sys user can log in even if the listener is down. But he is logging using this method which is all as authentication. This means that he didn’t use the listener. Okay, very nice. So now what I will do, I will take this command for example copy. And I will try to connect.

Now it will give you no listener. So this method of connecting which is the easy connect need the listener to pee up and running. So I will make exit. Then I will make listener start and the listener will be started. And remember that the El rig needs around 60 seconds in order to register the services. But there is another nice method to not to wait 60 seconds. I will make SQL plus as says DBA. Now you will make alter system register. In this way you will force the listener to register all the services. Now I will make exit. Then I will make now listener status. And you will find now that all the services is up and running. I will connect now. Now I will make paste to see that if this command is working, you can see connected. So in this lecture we understand 100% what is the meaning of Easy Connect? You will put the user password at the hostname column port the service name thank you for listening and see you in the next video.

15. Easy connect method

Hello everyone and welcome. In the previous lecture we understand how to move from the container database to the bloggable database. In this lecture we will learn how to start the listener and stop the listener. And this lecture is very very important. First thing, let’s go go and see the files for the Tnsnames aura and also the listener aura. So I will go to the home and I will go to the other locations. I will go to computer, I will go to u zero one app oracle product 18 DB one. You will find a folder called network. I will make click and you will find a folder called admin. Then you will find this file called listener aura. And I have a file called tnsname aura and I have Sqlnet Aura. Now I will open the listener aura, I will make right click and I will open with another applications. I will go and choose text editor or you can do for example view all applications and you will search for for example the text editor. Click select and it will open like this. So this means that I have a configurations listener ora I have a listener called listener and this is the default listener.

And this listener is listening to any connections that point to this address or this address. I just want you to care about the fair address. So any client trying to point to this address with TCP protocol and for the host this on the port 1521, this mean that this is a valid connection. Okay, very nice. So actually the port one five to one and the protocol TCP, it is the default. So this means even if you delete this file listener dot ora or you rename it again, it will work without any issue. The listener will work. Okay, so when you are using the default port and the default TCP, the listener aura is not important. But I just want you not try to delete this file. Okay, very nice. And I have another file called TNS names aura and I describe that for you in detail. So actually this file can be exist on the client and also can exist on the server in order to do a connection as client style. If you try to open this file, you will find first thing this is entry for ORCL and this is entry for orclpdb and you will find also the listener ORCL. Actually we call this as local listener and we will talk about this in detail.

Okay, very nice. So maybe in your case you don’t have this entry. So what I will do, I will remove this entry. Okay, no problem. Very nice. So I have entry called ORCL and this entry ORCL it is a TNS name entry. This means that I can login using this Tnsname entry. And this entry pointing to the address TCP hostthis. com port 1521. And the service name is Oracle. com. And this service is pointing to a database called ORCL, which is the main database for my database. Okay, very nice. So what I will do, I will save and I will close this one. Okay. And I will close this one. Now I will make a right click open a terminal. Now, I just want to tell you a very important note. Whenever you start up your virtual machine, this will start up the listener automatically. Remember this.

So now I will stop the listener. So what I will do, I will write the following command lsnrctl and I will make stop. So I will make listener stop. I will press enter and you will find now the command completed successfully. So now the listener is down. So this means that no one can connect to the Oracle database. So now I will approve that for you. So what we will do, we will minimize this one. I will open the SQL developer. So now I am assumed. Now I am as Oracle client. So I am a user that wants to connect to the Oracle database remotely. I will go to the Oracle connection and I will press discipline to create a new connection. I will put the username sys as Csdba.

I will put the password and I will come here to the connection type. I can connect using the Tnsname aura and also I can connect with basic connection. The basic connection is very simple. You have to provide the hostname, the port and the Sid or service name. Okay, very nice. So now what I will do, I will make the hostname is test, right? Okay. Because my hostname is test. And remember that the IP address for this is already in my host machine, in the original windows machine. So if you go here on the windows and you make here run, then you put the drivers and click OK. And if you go to the ATC and if you try to open the host file using for example, not bad, you will find that this. com is pointing to this IB address which is the server address. Okay. So you can use this or you can use this one. Okay, I will take this one copy and I will show you how we can do this.

Okay, very nice. So now I will pack again here. And here I will put the Sid, which is mean the innocence name or the service name related to the Oracle database. Now let’s try to put the Sid. Actually, the Sid is ORCL I will put ORCL. Now maybe you will ask me, I don’t remember what is the Sid which is in the instance name. It’s very simple. We will go again to the virtual machine and we will try to connect SQL plus as sysdba. And you can see now that the sys user is connected, even if the listener is down. You remember that I make the listener stop, but the sys user can log into the database even if the listener is down. So now we will write the following query. Select an instance underscore name from the dollar sign innocence. So I have this view, it is a dictionary view.

This review will show you what is the innocence name which means the Sid. I will press Enter and you will find the innocence name is ORCL and it is also the database name. So now I will go to here to the SQL developer and I will try to make tests for the connection and it will show you error. The network adapter couldn’t establish the connection. So when you see this error, this means that you have a problem in the network or the listener is down. So now we will pack again to the virtual machine. And we will exit here. And we will make now listener start. And I will press enter. So now the listener has been started. You can see now this is the earliest for the listener. The listener name is listener and you have the virgin and you will find many information for this. But I just want you to focus on one thing. You will find here the listener support no services. Why? Because actually the packaging ground process which is L rig which is the listener registrations. It needs 60 seconds in order to register the services inside this database to the listener. So you have to wait 60 seconds. There is another way to do it without waiting. But I just want you to wait for 60 seconds.

So now I will make listener stay tas and I will press Enter. You can see now that the process L rig register all these services. So I have a service called Orclpdb. com and this is related to database called Orclpdb. And I have a surface called Orcl. com related to a database called ORCL. But I don’t understand what is the. com because this is the global name for the database. So when you create a database oracle create a services with the same name but with the domain. Okay, very nice. We will understand this more and more. So I just want you to know that I have now currently two services orcl. com which is pointing to the container database, ORCL which is my main database. And I have a services name called Orclpdb. com which is pointing to another bloggerbill database called Orclpdb. That’s it. So now if I go to the SQL developer and I make now TSD, you will find now that I have invalid username and password. So now I know the problem. I will make sys as sys DBA and I will put the password. I remember you remember that in the previous lecture I changed the sys user to welcome.

 So I will put now welcome. I will make now test and you can see now the connection has been successfully. Wow. Now what I will do, I will try to move to the service name and I will put Orcl. com. I will make this connection and it’s working fine. Success. So I can login using the Sid or the service name. Now if I want to connect to the blogger database. So what I will do remember I will go here. And you can see now that the service name for the blogger build database called Orclbdb. com. So I will take this one copy. And if I go here and I put now this one orclbdb. com then I make test also working without any issue. So in this lecture we learn a lot of things.

Final things. We can go now to put the IB address for the host name. So again I will go to run drivers ATC host. I will open it with notepad. And I will take this one the IB address. This one CTRL C and I will go to the SQL developer. And I will put the hostname like this. I will make this and it’s also working fine. Remember that this IB address. It is the same IB address for the virtual machine. So if I go here and I put if confconfig you will find now that this is the IB address. Now a very important note. You should always be connected to the internet. So if you go here you can see now that I am connected. If you try to make turn off this means that you will lose your IB address. So remember to be always connected to the internet. In order the virtual machine to take IB address. Thank you for listening and see you in the next video.