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

7. Oracle net configuration assistant (netca)

Hello everyone and welcome back. So in the previous lectures we understand how to configure the Listener and also the Tsunamis . And we said that we have two type of service registrations. We have a dynamic registrations and we have aesthetic registrations. In order to configure the dynamic registrations, we have to configure the Listener aura, tsunami Aura local Listener. And you can refer to a listener called the Creative Dynamic Listener. And also we have a static registrations. In static registrations we configure Listener Aura by adding the Sid list. Also you can refer to the listen called Recreating Static Listener example. Actually we have other methods to configure the Oracle network environment. Actually, we have something called Oracle net manager net MGR. We have Oracle net configuration, assistant net CA. We have Listener Control Utility.

We have the Enterprise Manager cloud control. This is in case if we have our database on the cloud. And also we have the database configuration assistance. And the Database Configuration Assistance allow you to create a Listener when you create a container database. So this means that we have two main tools, the Oracle Net Manager and the Oracle net Configuration Assistance. Actually this tool will help you more and more to create and to configure Listener.

So in the previous lectures we learn how to configure the listeners and create the listeners manually by updating the files Listener Aura and also the TNS names Aura. But actually Oracle recommend you to use these tools because these tools will help you a lot and it will eliminate the mistakes for the data entry. So now let’s go and try to understand the Oracle Net Configuration Assistance. Actually this will be very easy for you. So now I will go to the Virtual machine and I am on this path u Zero one app, Oracle product 18 DB one Network admin. I have the Listener dot ora I will open this file and you can see that I have many listeners. I have Listener Two, I have Listener underscore PDBs and this is Aesthetic Listener because I have a set list and we have the default Listener, which is this one. Okay, very nice.

Now in order to configure a new Listener using the Nitca, we will do the following. Now I will close this file and I will make a right click and I will open a terminal. Now I will make Net Cait Configuration Assistant when I press Enter. Now Oracle will create backup file for the Listener and for the SQL net Aura and also the Listener. Ora you can see that I have a backup for the TNS name backup for the SQL net and also for the Listener. We will talk about Silent Oracle later. But anyway, when you start the Oracle net Configuration Assistant, you will have many options. The first option is Listener configuration. This is in order to configure a new Listener and to add a new Listener. We have naming method configuration and this is to update the file SQL net aura and the third option is local net service name configuration. The third option is to update this file which is the Tnsnames Or.

Now let’s try to add a new listener. So I will keep the first option which is listener configuration. I will click next. Then it will give you options. Do you want to add a new listener or to reconfigure or delete or rename? Actually I will add a new listener. I will click next. Then you have to give the listener name. I will name it for example listener underscore test. I will click next. Here it will show you the protocols. Just keep the default protocol which is TCP. I will click next. Here it will ask you do you want to use the standard port for example 1521 or you need to use another port. For example I will use another port and I will for example I will make it 1522. I will click next. Now it will give you this message would you like to configure another listener? I will keep it now. Then I will click next. Then I will select the same listener listener test to start this listener. I will click Next and you can see listener configuration complete. Very nice. Now I will click next, then finish. That’s it.

Now if you open the listener aura you will find the new listener added to the file. I will open it and you can see that I have this listener, listener and the score test. And you will find this is the address protocol TCP and test and 1522. So actually it makes your life easy and simple. Okay. After that you can configure this listener to be a static listener or a dynamic listener. And you can refer to my lectures in order to do this. Okay, very nice. Now I will use the same net CA to remove this listener. So I will close this file. Then I will open this one and I will make knit CA again. So it will also create pack of files. Never mind about this. So just wait a little and I will choose the first option listener configuration. Then I will click next. Then I will click delete. Then next. Then I will choose this one which is listener underscore test. Then I will click Next and it will give you this message. Click yes.

Then click Next, next and finish. Now if you go to the listener ora and try to open it, you will not find listener test again. So very nice now now we will learn how to add entry to the TNS names at aura. I will open this file so you will find for example I have entry called Orclpdb. I have also entry called PDB one. I have entry called ORCL. Now let’s suppose that we created a blogger build database, okay. And this is a blogger database called for example, Haled underscore test. Okay, very nice. Let’s try to add entry for this database. So what I will do, I will close this, I will open the terminal and I will make net CA. I will press enter. I will wait a little.

Now I will go to the third option local net service name configuration. I will click next. Then you have many options to add, to reconfigure, to delete, rename test. Actually, this will update the file which is tnsnames or remember this. So I will click Add. Then next now, what is the service name? Suppose that we have a created database called Halidanderscoretest. So it will be, for example, Halidanderscoretist. com. Okay, so this is the service name. I will click next. I will choose the protocol TCP Next. Now I will put the hostname. My hostname is test. Then I will click next. But before that you can use a standard port or you can use another port. I will keep the standard port, which is one five to one. I will click next. Now, do you want to test? I will not test now because I don’t have the bloggy database yet. So I will choose no. Then I will click next.

Now, what is the net service name? I will name it Halit underscore test. And this is the entry name in the tnsname aura. I will click next. Then I will click next. Then finish and finish. Okay, now if you go to the T in is name aura and open it and go to the here you will find this is the entry. So I have entry in the TNS names called Halite and the scorched and it is NTCP and this is the host and this is the port and this is the service name which is Halidandascotes. com. Wow, very nice. So we added entry in the Tnstname aura. Okay, but now this subbugel database does not exist. So you have to create a blogger bell database Haled underscore test in order to connect to this sublegal database. Okay, I will not waste time because we did a lot of a practice about this. So in this lecture we understand what is the benefits of netter. It make your life more easy and simple. Okay, very nice. So thank you for listening and see you in the next video.

8. Oracle Net Manager (netmgr )

Hello everyone and welcome back. In the previous lecture we understand the knit CA. In this lecture we will understand the net manager. Actually it is the same exactly. But here we have more options and we have more advanced option. So that purpose from the net manager and the net CA it is the same to make your life easy. To update the listener aura and the TNS names Aura and also the Sqlnet Aura. Now I go to this path which is U zero one app, oracle product 18 DB one network admin. I will make right to click and I will open a terminal. Now I will write the following command net MGR net MGR and I will press Enter. You have to wait a little. Then the screen will be loaded. Now this is the Oracle net configuration. You will find here something called local.

 Press here and you will find a profile. This is for updating the SQL net. And we have service name in order to add entry to the TNS name Aura. And we have the listener. If you go and click here on the listener you will find that I have the following listeners. I have listener the default. I have listener two and I have listener PDPs. And this is the details for all this. Now in order to create a new listener, we have to go here to the listener. Then we will go and depress this sign. And you will find here the listener name. For example, I will name it listener underscore for example Halit. After that I will click OK. Now you will keep the option listening location. Then you will go here to add address. Here you will put the host test. Then you have to put the port. Or you can change the default port.

I will make for example 1601. After that I will go to file and I have to make save network configuration. I will click save. Now if you go to the listener ora and open it. You will find this entry added. So you can here see that I have listener and the score had it. And this is the description. And this is the address which is TCP. And this is the host. And the port is 1601. Very nice. Also you will find that this tool adds you this line which is the ADR pace listener. And this is called the automatic diagnostic repository. So it’s only store the look file about the connection. Forget about it. Anyway, so this is the way how to add a new listener. I will close this file. Now I will go to the Oracle net manager. Now suppose that I want to add a new entry to the key in streams with Aura. I will go here. I will click here. So you can find that I have many entries. For example had a test. I have had one, two, three. I have ORCL I have ORCL PDB. And I have PDB one. Suppose that I have created a new pluggable called PDB Two and you need to add this to the TNS names at all. It’s very simple and it is in the same technique. You have to go here to the service name. Click the sign and here you put the net service name. Okay, so here it is the entry in the TNS names or for example PDP Two. Okay, very nice.

Click Next then choose the protocol. I will keep the first protocol which is TCP IP. Click Next. Here you have to put the hostname. It is test. Then you can keep the default port 1521 or you can change it. I will keep the same port. Click Next. And here you have to put the service name and the service name it will be for example PDP Two. And I will go to connection type and I will select dedicated server. Because in this course we are focusing on dedicated server.

We will talk about shared service later on. I will click Next and finish. Now you can see that a new one created called PDB Two. You have to go to file and you have to save. Now if you open that T in its name aura and open it like this, you will find that I have entry called PDB Two and listening to this host TCP. And also this is the port and this is the service name. That’s it. It’s very simple. So actually it’s up to you. Do you want to use the tools or you can update the TNS name and listener manually. It’s up to you. So I will close this and this is our lecture today. Thank you for listening and see you in the next video.

9. CREATE DATABASE LINK

Hello everyone and welcome back. Today we will start a new subject, which is the database link. So what is the meaning of database link? A database link is a schema object in one database that enables you to access object objects on another database. This means I have two database, database One and database Two. The database one need to access some objects in database two. So in this case we have to create something called Database Link. Okay, the second point the other database need not to be an Oracle database system. This means that you can create a links between two Oracle databases and also you can create from Oracle to another type of Oracle database or another type of arid database like MySQL or SQL Server. So it is not necessary to create a link only to read it from Oracle database. You can create a link to read it from another type of databases.

However, to access non Oracle systems, you must use Oracle Heterogeneous Services and this service is responsible for another type of databases. Anyway, we will not talk about this. We will talk only about Oracle databases. Okay, after you have created a database link, you can use it in SQL statement to refer to tables, views and PL SQL objects in the other database by a bend link at the database link name. And we will see that in the example. Now, to create a private database link, you must have a privileges called Create Database Link. To create a public database link, you must have the Create public data based link system privileges. So we have to type private and public and we will see that in the details in the example and we will understand what is the difference between private link and public link. Now, let’s move to the next slide. So I have two databases. The first one is broad one, the second one is broad two. Inside the broad one I have a user called broad one underscore admin. In the broad two I have a user called broad two underscore admin. Suppose that this user has a table called employees. Now the user brought one underscore admin in this database, he need to read this table. How he can read this table? We have two separate databases. Actually, we have to create a database link in this database. For example, I will name the link read underscore Broad. So now the user Pod one underscore admin he can write this selective statement select star from Employees at and you will mention the link name which is read and the score brought two.

So this means that this user are using this link in order to access the other databases. The syntax is very simple create Database Link. Then you have to mention the link name. For example, create database link read underscore proddo connect to in this case I have to connect to the user Two, which is a broad to underscore Admin. So it’s like that. Broad one is connecting on behalf of the user prod two underscore admin. So create database link read underscore pro to connect to underscore admin, identify by the password. So you have to know the password for this user also using. And here you have to put the connect string, which is the TNS name entry.

 So you have to know that t in its name entry which exists in this database. That’s it. It’s very easy and simple. We will understand this in details in the lecture. So actually we have a dictionary table called DBA underscore DB underscore links that store the information about the database links. Anyway, so we understand the concept. I have two databases. The first database need to access the second database. In this case, I have to create a link in database one and it will be acting like database two. That’s it. It’s very simple and easy. We will understand this in details in a real example. Thank you for listening and see you in the next video.

10. CREATE DATABASE LINK example part 1

Hello everyone and welcome back. In the previous lecture we understand the concept for the database link. Now we will do a nice example. But wait a minute, you said that the database link should be between two different databases. But actually we have owned only one database and we have a blogger build databases. How we can apply this example? Actually we will deal with the blogger build databases like as remote databases. Okay, so we will create now two bloggers databases. And remember that in Oracle twelve C and 18 C the Blogger Bell database are totally isolated. So it is like two different databases. So we can do this example without any issue. Okay, very nice. So now we will create a database called Prod One and Prod Two. I will go to the virtual machine. I will make right click open a terminal. Now I will use the database configuration assistant in order to create the blogger build databases. I will make now DBCA. I will press enter.

I will wait a little until the database configuration assistant started. Wait a little and you can see now it is started. Now we will go to the last option which is manage bloggable databases. I will click next. Now I will choose the first option which is create a blog a build database. Click next and you can see I will choose the container which is ORCL click next. Now I will create a new blogger bill database from the seat. Okay, I will click next. I will name the plugable database as Prod One. And I will make the administrator prod one underscore admin and I will give it the password welcome. And I will repeat the password again welcome. Okay, so it is exactly like the exercise.

So Prod one underscore admin and the database name called Prod One.

I will pad again. So Prod one prod one underscore admin I will click next. It will give you this message that this password is not standard. Click yes. And here I will choose to create default user table space. And I will click next and you can see here the details and the summary. Click finish and wait a little until the creating for the Blogger Bell database finished. Wait a little and you can see here blogger Bell database brought to one blogged successfully. I will click close and that’s it. Now what I will do, I will make SQLPlus as sysdba.

I will make surecon underscore name. So currently I am in the root database. Now I will make column name format a 20. I will make the following query select name comma open mode from the dollar sign PDPs. So you can see that I have a new blogger build database called the Prod One and it is read and write. Wow, very nice. Now I will make now alter session sit container equal broad one. I will make surecon name so you can see that I am in the broad one database. Or a blogger build database. Now, if you remember that we created a user called the prod one underscore Admin. But actually, this user currently doesn’t have any privileges. So what I will do, I will pack again to the SQL and I will give the prodoone DBA role. So I will make a grant DBA two, two broad, one underscore admin grand DBA two broad underscore admin I will execute, you will find grant succeeded. So this means that the broad one underscore Admin, he is a database administrator and he has a role called DBA. Okay, very nice. So this is the first part of this lecture. We will continue in the next lecture.

11. CREATE DATABASE LINK example part 2

Hello everyone. Welcome back. So in the previous lecture we finished the first part. We created a blogger bill database called the Prod One. And the Prod One underscore admin has been granted a DBA rule. Now we will create the second bloggle database. We will pack again to the virtual machine. I will click Exit from the SQL Plus. And I will make DBCA. And I will press enter. And I will wait a little until the database configuration assistant started. I will wait a little. I will go to the option manage Bluggable databases. I will click next. Create a Bluggable database. Next I will keep the ORCL as the container database. Click Next. I will create a new bloggable database. From the seed, I will click next. Now I will name it Prod Two. And the administrator user is Prod Two underscore admin. And I will put the password welcome. And I will repeat the password welcome. I will click next. I will click here. Yes. Because this is not a standard password.

And I will create a default user table space. I will click next. This is the summary and I will click Finish. I will wait a little. Then the blogger based database will be created. Wait a little. So now it has been finished. Click close and that’s it. Now I will make SQL plus as sysdba. I will make column name format a 20. Then I will make select name comma open mode from v dollar sign PDBs. And I can see that I have brought to created and it is read and write. Now I will move to this blogger bill. And I will grant DBA also. So I will make now alter session set container equal Prod Two. I will make showdown underscore name. So I am in prod two. Now I will do the same. I will make grant DBA to prod to underscore admin. So I give it a database role. Very nice. Now what I will do, I will go to the Tnstname Aura and I will open the file. Now I will go for example to this one which is Orclpdp. I will take it copy. Now I will paste a new entry. And I will name it here prod One. And the service name is Prod One. I will take it again CTRL C and I will paste it here. I will put here prod two. And also the service name is Proddoocom. I will save. I will exit. I will pack again here. I will exit. Then I will make now. Then I will make listener status. And I can see that I have a Prod One services as ready. And also I have a Prodo. com as ready. Let’s try to connect to each of these sublugabelle databases. I will make SQL Plus. Then Prod One underscore admin slash the password is welcome at Prod One. Remember, the Prod One is the T in its name entry. I’ll press enter. It is connected without any issue. I will try to connect for the second blogger bell database. I will make now connect prod two admin welcome at prod two. And remember the prod two, it is the t in its name entry I will click enter and I am connected without any issue. So in the presentation I created this blogger bin. And I have this user. And I have this blogger bill. And I have this user. Very nice. Now what I will do. I will create a table called Employees. In the Bloggable database.

I will pack to the SQL. I will make surecon name. So currently I am in pro two. I will make show user. And the user is the pro two. Underscore admin let’s create a simple table. I will make create table create table employees ID is a number and the name varcar 2100. So table created. I will try to answer to one record. I will make answered into employees values. One holiday, one row created. I will make comment. And I will make select star from employees. Wow I have everything is working fine. But before that let’s make call name format a 30. Then I will repeat the select statement again. So I have the ID one and had very nice. Okay very nice and sweet. Now I will pack to the presentation. So I created this subplugable. I created this debugger bill. I have a table called employees. Now I have to create a database link, right. So I have to be in the first blog a bill database which is the broad one. So I will pack to the virtual machine. And I will make and I will make connect. Then I will take all these copy then paste. So I am connected showcon name, show user and everything is fine. I am in the prod one admin inside the bloggible database. The plot one.

Now I have to create the database link. So what I will do. I will make create data base link. And I will name it read underscore prod two. So it is exactly the same of the presentation. Read underscore prod two. I will pack here create database link read underscore prod two. Then if you can see the syntax you have to make connect to. So I will make connect to. I will connect to the other user called prod two. Underscore admin identify will come. So I will use the user for the pro two and his password which is welcome. Okay very nice. Now using what if you can see here in the presentation. Using the connection string for this subbugel database. Which is a prod to TNS name entry. So I will make here. Now using prod two like this. Okay. So create database link read underscore pro two. Connect to pro two. Underscore admin identify by welcome.

Using the connection string pro two which is the connection detail for the other database which is a prod two. I will press enter. You can see here database link created. And this is a private database link. What is the meaning of a private? This means that the user approved one underscore admin. He is the only one who can use this link. No other users in this blogger build can use this database link. So this is the difference between private and public. If you create a public, this means that all users can use this database link. Now, I want to read this table, right? How can I do this? It’s very simple. What I will do, I will make select star from employees. Then I will make at read underscore pro two, which is the database link. And this database link, this means that I am collected exactly the same prod two admin. I will press enter and you can see that ID one and Hallid.

So this means that I am in this database and I can access this table which exists in the employees. So actually, this means that broad one underscore admin is exactly acting like a broad to underscore admin. He can answer delete update data exactly the same. He will have the same privileges. Okay, very nice. And you can query the data. Basic information from DBA underscore DB underscore links. So I think that I give you a very nice example. You have to practice a lot to understand this concept. It’s not easy. You have to focus only. Thank you for listening and see you in the next video.