1z0-082 Oracle Database Administration – Creating PDBs
- About creating PDBs
Hello everyone and welcome back. Today we will start a new chapter and this chapter is very important. In this chapter we will talk about creating a bloggill databases. Actually we have many methods to create a bloggle databases. We can create a bloggle database using the seed. We said before that we have a bloggable database called Seed and it’s like a template. We use this template in order to create new bloggable databases. So others can make your life easy by providing this seed database. Another method that we can create a blogger database from an uncontained database. Actually you can configure Oracle twelve C or 18 C or 19 C to be non-container database. So this means that you are removing the concept for the container and the pluggable.
But why to use this if Oracle gives you an ICE’s features in Oracle twelve C and releases after twelve C to make container database and bloggable database. So why to deal with Oracle twelve C like eleven G. So I don’t recommend you this. Anyway, creating a bloggable database from an uncontrolled database, this is out of this course. So it’s not a part of this course. Another method that you can clone an existing bloggable database or noncable. For example, maybe you have a nice database or a nice bloggable database contain a nice application. So you can clone this database to be another database and give it another name. So it’s like a copy database to another database. Very nice.
Also you can plug and unblocked blog a database into a different container database. Actually, this is not part of this course. But I will teach you how to unblock bloggable database and plug it in the same container. Because we don’t have many containers, we have only one container in our lectures. So I will teach you all the skills you need to unblock and bloggle databases. Also, we can reallocate a bloggle database to a different container database. Actually, also this is not part of this course. We can create a blogger database as a proxy blogger database and it’s not part of this course. So in this course we will focus on how to create a bloggable database using the Seed, how to clone an existing bloggle database and how to block and unblock blogger database.
This is all you need to know in this course. And this is a part of the exam. Other topics are very advanced topics and believe me you will not use it. Now, what is the tools that you can use in order to create a blogger build databases? Actually the first tool is the SQL Plus and this is the preferred tool for the database administrator. Also we have the SQL developer. We have the Enterprise Manager cloud control. This is in case if your database on the cloud and also you have the DBCA which is the database configuration assistant. So this is only overview about creating bloggable database in the next lecture we will will start to learn how to create a bloggable database using the seed database. Thank you for listening and see you in the next video.
- Creating PDB from seed part 1
Hi. In the previous lecture we took an overview about creating bloggable database. We understand that we have many methods in order to create bloggle databases and also we learn what is the tools we need in order to create a bloggabel database. Now we will start by the first topic which is creating bloggable databases from Seed. Actually it’s very simple. I have the container database. The container database should contain one root container and it should contain a seed bloggable database. So this is the minimum requirements for the container database. I have a root and I have a seed bluggable database. Now we can create a Bluggable database, for example PDB one from the seed bloggable database. So this is the first topic in this lecture.
Now we do this by create a bloggle database statement. So in SQL plus or SQL developer we can execute the following command which is create a blogger bill database. Now when we do this, this will copy data files from Seed to the new location. And also this will create system and seasox tapelli spaces. And we talk about what is the purpose of each of these tapelli spaces in the beginning of this course. And also this will create a default schema and common users, sys user will be a super user and System user will manage the blockable database and this will create the database services automatically. I told you before that each database should have a service. So when you create a blog a database statement, it will automatically create the service for this Bluggable database. Now, what is the prerequisite for using the create bloggable database statement?
Actually the current container must be root. So in order to execute the following comment which is a create blogger able database, you should be in the root database and also you must have created blogger build database privileges. So most of cases the CIS user who create new blogger build databases and also the container database must be in read write mode and this is very important. So this is the conditions in order to create a new bloggle databases. Now let’s go to the virtual machine and I will describe for you some basic information.
So now I will go to my virtual machine, I will log in as Oracle user, I will go to home, I will go to other location, I will go to computer, then use one app Oracle. And now there is a very important folder which is Aura data and this folder contain the data files for your database. I will open this, you will find ORCL I will open it so you will see the bloggable database orclpdb and this is the PDB seed. Okay, very nice. If you open this folder you will find the data files for this blogable database.
So I have Sysco file, we have System file, we have Temp and we have and do table space. So this is the basic information and I just want you to review the information for the data files and the table spaces before we continue in this chapter. So this is the basic information that I need you to know in this lecture. So in the next lecture, we will create a new Blogger Bill database. So this means that we will copy all these files to the new Blogger Bill database. So thank you for listening and see you in the next video.
- Creating PDB from seed part 2
Hello everyone and welcome back. Today we will do a very nice exercise. We will create a new bloggable database from the seed. I just want you to focus with me 100% today, okay? Very nice. First thing, my virtual machine is up and running and also my database is up and running. Now I open the SQL developer in my machine and I am connected remotely to the server. I am connected as SIS, as Sysdba. Now I will start to do the following command. Now I will make sure con name so you will find that I am in the root database. I will make the following command select conid comma name open mode from video sign containers I will execute this statement and you can see that I have the container database, the root, it is a read write. So we made the condition in order to create a new Blogger database the root should be read and write. We have the seed database and it’s read only. And we have one blogabl database called Orclpdb and it is read and write. Nice.
Now I will move to the seed database. So I will make alter session setcontainer equal PDB dollar sign seed. I will execute the following command. So session altered. Now I will make select CommAid comma username default underscore tablespace comma common from CDB users. I will execute this and you can see now this is all the users in the container ID too, which is the seed database. So if you make CDB underscore or DB underscore it will give you the same result. Okay, very nice. So I will execute this and you can see it is the same. If you make count rows you will find that I have 35 users and all the users are common users. Okay, now I will make SelectCon ID comma file number comma name from video sign data files. I will execute this statement and you can see that I have the three data files. System one, Sysocs one and ando tablespace one.
If you go to the virtual machine and if you go to the PDP seed you will find the same information. So this is the see Socks and this is the system. And this is the and do. And this is the temp file. Very nice. Okay, now I will make now select star from the dollar sign table space. I will execute this and you can see that I have four tablespace. I have system C socks and do and temp. And you can see here the container ID is two. This means that when I create a new Blogger database I will copy all these informations to the new Blogger database. Now in order to create a new Blogger Bell database, I have to be in the route. So now I will make alter session set container equals CDB dollar sign root. I will execute the following command. You can see now session altered. Now we have to create directory for the new blogger database. I will create a blog, a database called PDB One.
So I have to make make directory and I have to give it this path. So I will take this copy control and see, then I will go to my virtual machine, I will come here. So, make sure that you are in this path u one app Oracle oradata ORCL if you make now open a terminal and you make now PWD. So this is the path. So make sure that you are using this path. Maybe in your situation you have another path, maybe you name your instance or your database different names. So anyway, make sure that you are in this path. So this means that I have to create a new folder. So I have here orclpdb and I have here PDP. Seed. I need to create a new folder for the new blog about database. So I will make now paste. So I will make a new directory. Use one app oracle, Aura data or RCL PDB One. I will execute the following comment and you can see now that I have PDB One.
Wow, very nice. Now I will pack again to the SQL developer and I will execute the following command create bloggerbill database PDP One so this is very simple. I need to create a new Blogger bell database. This is a Blogger Bell database called PDP One admin user PDB One admin identify by welcome. This means that I want to create a new blogger database and I want to create admin user that manage this blogger database. The name for this user is PDB One admin and the password for this user is welcome. And I want to give it roles DBA. So this is the three lines. It’s very simple. This is the new blogger build database name. This is the admin user. This is the privileges or roles that belong to the admin user which is PDB One admin. Remember that this is the default script from Oracle. Now you have to give a default table space for the database. Now I will create a new table space called users. And this is very important because the tablespace users contain the actual data.
Okay, very nice. So when you create tables like employees, HR, employees, department table, whatever, you created using a default tablespace. So Oracle prefer you to name the user table space users, you can name it whatever you like, whatever. But this is the default to name the new table space as users now, because the table space need datafiles, because the relation between tablespace and data files, I show you this relation in the beginning of this course. Okay, so I have to create a data file for the tablespace users. So you have to mention this phrase data file. Then you will put the path using one app oracle or a data ORCL PDB One. Then you will mention that this is the data file for this table space. So when you create a new tables and enter data, this data will exist on this data file. Okay, very nice. So you have to put this path and this path we already created before 1 minute. And this is the name of the database file. And remember, the database file contains the actual data. You have to give it a size, for example 250 meg. And you have to mention auto extend on. So this means that after 250 meg it will be auto extend. Okay, after that you have to put this phrase which is file underscore name convert. What is the meaning of this clause? Actually the file name convert use this clause to determine how database generates the names of files such as data files and wallet files for the Blogger bell database. So here it’s like how to name the new data files. So you have to mention two phrases, the first phrase, the location or the directory for the seed and the directory for the new blogger database which is PDB one.
So I put in the first phrase, use one app oracle aura data ORCL PDB seed. And here you put the new location for the new blogger database. So it’s like a replacement. So whenever you find this path, replace it by this path. So that’s it. So it’s very simple. So file name convert determine how database generates the names of files. So this is the concept of this command. You will use this command always in order to create a new blogger database from the seat. So you will mention the bloggle database name, then the admin user, the role for the admin user, you have to mention default tablespace for this database, you have to mention the data file and you have to mention the naming method or the file name convert. So now I will execute the following create blogger build database statement. And remember, you have to be in the root database I will execute and you have to wait a little around 1 minute or two minutes maximum. Then you will find bloggable database PDB one created. So now if we go to the virtual machine and we log in again as oracle user, now you will find that I have PDB one. If you open it, you will find all the information.
So you will have Seasox system and this is the temporary and we have the Andrew. Also you will have the user templatespace. I will continue, maybe I have to connect first to the new blogger database. Anyway, what I will do now I will make select conid name open mode from video sign containers. I will execute this and you can see that I have a new container called four and PDB one and the database is mounted. So now I will make alter session set container PDB one. So I will move to the PDB one. I will execute this command session altered. Now I will open the blogger build database. So I will make alter blogger database open. I will execute this and you can see now blogger build database open altered. If I go to virtual machine I can see now the user’s database file. That’s it. So this is the concept. I will pack again here. Now I will do the same statement that I did before. Now remember that I am currently in the blogger bell database PDB One. I will make now select conid username default underscore tablespace comma common from CDB users. I will execute this and this is the result. If you make now count rows you will find now 37. Why? Because you will have a new user called PDP one admin and this is not a common user and this is the person who will manage the blogger build database. So it’s called PDP One admin and we create this in the create statement.
If you remember, okay, if you do this also from DB users you will find the same. So this is the result. And now I will make SelectCon ID comma file number name from v data files. I will execute this. So you will find that I have in the container database four. I have four data files system CSOX and do and user one. If you make now select a style from the Doler signs tablespacess, I will execute this. You will find that I have now five tablespaces system, CSOX and Do Temp. All these are copied from the previous seat but I have only new tablespace called users. And this is I created in the create a blogger build database statement. Wow, very nice. So in this lecture we learn how to create a new blogger build databases from the seed. In the next lecture we will learn how to connect using this blogger build database which is PDB One. I just want you to focus very well in this lecture because this is all you need to know related to creating blogger bell from seed. Thank you and see you in the next video.
- Connecting to the new PDB
Hello everyone and welcome back. In the previous lecture we learned how to create a new bloggable database from the seed. We created a new bloggle database called PDB One. And there is a new user called PDB one admin created in this bloggerl database. Very nice. Now we will do a very nice exercise to learn how we can connect to this new blogger bill database. I will go to my virtual machine and I will make right click and open a terminal. Now the first thing we have to do if we make now listener status, you can see that I have a new service called PDB One. com. And remember the. com is the domain name. Okay, very nice. So I have a new services called PDB One. So I can use this service in order to connect to the new blogger build database. Very nice. Now what I will do, I will make SQL plus as siesta. I connected to the root database. I will make showcon name. You can see now I am in the root database. I will make little format before I do the following query.
I will make call name format a 20. Then I will make select name comma open mode from the dollar sign PDP as. So I have a small error. I will make edits and I will make open mode. Then I will execute it again. So, I have now three blogger bill database. I have the PDB seed, I have orclpdb and I have PDB one. Very nice. Now I will alter the session and I will move to the bloggerbased database PDB one. So I will make alter session fitcontainer equal PDP one. I will make now showcon name. So now I am in the PDP one. Another way to know the service name for this blogger build database. To execute the following query, I will make select name from the donor sign services. Sorry. I will make edit serves. I will execute so the service name is PDB one.
But you have to mention which is the domain name. To know the domain name is very simple, you can make show parameter domain. And you can see now the domain name is. com so it will be the servicedb one. That’s it. So we learn how to query the service name pi two method by the listener and by this query which is select name from video sign services. Now let’s try to connect to this blogger bell database using the easy connect. Now maybe you don’t remember the easy connect. It’s very simple. I will make connect, then I will mention the user. I have a new user called PDP one admin. And remember that I do this in the previous lecture when I create the blogger build database, I mentioned that I have admin user called PDB one admin. Okay, very nice. So now I will pack again slash the password was welcome [email protected] com which is the hostname.
Then colon, you will mention the port which is 1521. Then you will mention the service name which is PDP One. And I will press enter. And I am connected. Wow very nice. Now if I go now to the SQL developer. I will close this one. Okay. And I will open it again. So I will go to the SQL developer. I will press for a new connection. Now I will make the connection which is PDP One admin. The username will be PDB one admin. The password is welcome and the hostname will be test. com the port 1521. I have to put now. PDP one. So that’s it. I will make test connection and you can see is success. Wow. So now I learned also how to connect using the easy connect using the SQL developer. Now I want to connect using the Tsunami.
So I will cancel this one. I will minimize. I will go to the tsunami aura. I will take a copy from this one. And I will paste here. I will make the connection name is PDP One and the service name is PDP One. Wow it’s very simple. So I go to the Tasman Aura in my local machine. And I add this entry. I will close and I will save. I will pack again to the SQL developer. I will make a new connection. I will put the connection name is PDP One admin the username same, the password will come. Then I will go to TNS. Then I will choose PDP One. I will test and it is success. Wow very nice. Now I will go to my virtual machine. Now if I try to make for example now connect PDP One admin welcome at PDP One.
I would execute. It will give me error. Why? Because I am trying to connect using the client style. So also I have to update the TNS name entry in my virtual machine. So what I will do. I will go to home other location computer one app, oracle product 18 DB one. Then I will go to network. Then I will go to admin. Then I will go to Test Aura. I will open it. I will take this entry Controlc. Then I will put it here controlv. I will make now PDP One and the service name is PDP One. I will save and I will close. Now if I go to the SQL plus and I try to take this command copy. Then I paste. I press enter. I am going wow wow. So it’s very simple and easy steps. So thank you for listening and see you in the next video.
- Creating PDB from seed (DBCA)
Hello everyone and welcome back. I hope that you are doing fine. In the previous two lectures we understand how to create a new bloggerbell database from the seed and also we learn how to connect to this new blogger database. In this lecture we will learn another easy method to create a blogger build database from the seed which is the database configuration assistant and it’s very nice visual tool to make your life more easy. But I recommend you to use the create statement in SQL Plus because this will make you expert. Okay, very nice. So this is the statement for creating the blogger bill database. Now what we will do, we will go to the virtual machine, I will make a right click and I will open a terminal. Now make sure that your database is up and running first.
Okay, very nice. Now we will write DBCA database configuration assistant and I will press Enter. Now this command will launch the database configuration assistant. Wait a little and you can see now the DBCA started. Okay, I will wait a little and you can see now the following screen. Okay, you will find here many options. You have to go to the last option which is manage a bloggable databases. Then you will click next. Now it will give you many options. I will choose the first option which is create a bloggable database. Click Next. Now it will give you that this is the container which is ORCL. Click Next. Leave this username and password. Okay, this is not important.
Click Next. Now it will show you this screen. Now create a new blogger build database from another PDB. Now select the PDB dollar sign seed. So this means that I will create a new bloggable database using the seed database. It’s very simple. Click Next. Now you have to give the name for the new blog a database. I will name it PDP two. Now you have to give the admin user. So I will name it PDP two admin. Put the password. I will put welcome and I will repeat it welcome.
I will click now next it will give you this message that this part would is not the standard ignored and click yes. Now you will find now the storage type is file system keybit and here the database location. It will be user one app oracle oradata ORCL and PDB name which is the one you put in the beginning. And you will find this option create default user tablespace. It is exactly the same. This is script exactly but in a visual method. I will pack again. Now, I will click next. And here it is the summary. So the container is ORCL the blogger database name is PDB two and the bloggle database source default. And this is the default location. That’s it. Now click finish and you have to wait a little until the Oracle configuration assistant to create this database for you. Just wait a little. So you can see now blog a database PDB Two plugged successfully. I will make it close.
And now if I go to home other location computer one app oracle, Aura data or RCL. You will find that I have PDB Two. If you open it, you will find all you need. This is the data file Susan system that temp and do tablespace and users one database file. It’s very simple and easy. Now I will open a terminal and I will connect now SQL plus as SDBA. I will make now format column name format a 20. Then I will make select name comma open mode from V dollar sign PDPs.
So you will find that I have a new bloggle database PDP Two. And it is read and write if I want to know the service name. It’s very simple. I can make exit. Then I make now listener status. And you will find that I have a service name called PDB Two. Wow. So this is the things that I need you to know. Please. I just want you to do the same exercises how to connect to this sublegal database. Try to update the TNS name entry and try to connect using the PDP Two admin. And you will find it the same things I told you before. Thank you for listening and see you in the next video.