1z0-082 Oracle Database Administration – Creating PDBs Part 2
- Dropping pluggable using SQL
Hello everyone and welcome back. I hope that you are doing fine. In the previous lectures we understand how to create new bloggers and databases from the seed. And also we learn how to connect to this bloggabel. Now we will learn how to drop the blogger database. But before that why we need to drop a bloggable database actually. Because maybe we created this Blogger Bell for training purpose. Or maybe we created this bloggle to test a patch or to test assumption. So that’s why we drop a bloggabel database. But in real life we never drop a bloggle database that contain information. So remember that. So, anyway, I have PDB One and I have PDB two.
Now let’s try to drop a blog a database PDB two. So I will open a terminal and I will log in SQL plus as Csdba. I will make now column name format a 20. Then I will make select name open mode from the dollar sign PDPs. So you can see now the PDB two is read and write. Now I will make showcon name. So I am in the root database. And remember, in order to drop the bluggable you should be in the root database. Now the following command will drop the blogger bill database. So you will find drop blogger bill database PDB two including data files. This means that I will drop the database and also the data files. So this is normally the command we are using. Now when I do this command, you will find or you will face errors. I’ll press enter.
You can see now bloggle database PDB two is not closed in all instance. So the first thing you have to close the bloggabel database first. So you will make now alter bloggabel database PDP two close. So this is the first step. I will wait a little. You will find now bloggabel database altered. Now I will execute the command drop blogger bell database PDB two including data files. I will press Enter and you will find the bloggable database dropped. If you go now to PDB two and open it, you will find the folder is empty. Now you can make right click here and if you want to remove this folder, delete. And that’s it. Okay, very nice. So in this lecture we learn that if we need to drop a blog about database, we have to be in the route. Then we have to close this bloggabel database. Then we drop it using drop a Blogger Bell database including data files. Actually we can keep the data files and we have options for keeping this. But anyway, I just want you to understand only this in this lecture. Thank you for listening and see you in the next video.
- Dropping pluggable using DBCA
Hello everyone. In the previous lecture we understand how to drop the blogger bill database using the SQL plus in this lecture we will learn how to drop the Blogger bill database using the database configuration assistant. So I have a PDB one. Now I will teach you how to drop this blogger bill. Using the DBCA I will make right click open a terminal then I will make DBCA database configuration assistant. I will wait a little. Now you will go the option manage blogable databases then click Next. Then you will select the second option which is delete a bloggle database. Click Next.
Now it will show you that you have the root database or RCL. Click Next. Then you will find now the blogger bill databases. For example you will click this one, you will select this one then click Next. Then it will show you summary that the container is ORCL the pluggable is PDB one and it will tell you the following data files will be deleted then you will click Finish. That’s it, I will not delete it. Want you to show you the steps. So thank you for listening and see you in the next video.
- Cloning PDBs part 1
Hello everyone and welcome back. In this lecture we will understand what is the meaning of Cloning bloggable database. In the previous lectures we understand how to create a new bloggable from the C database. In this lecture we will learn how to clone bloggle database. First thing, what is the the meaning of Cloning bloggabel database? Actually, Cloning is copying a source bloggle database from a container database and plugging the copy into the same CDB or another CDB. For example, I have a root database and I have a C debatable database.
I have a bloggable database called orclpdb actually I can make a simple comment and I can copy this Blogger bill database and I can give it another name. For example, PDB. Five. So PDB five, it will be a copy from Orclpdb. So this is the cloning. Actually I can make this copy in the same container, or I can put it in another container. But in our course in our lecture, we will focus only in the same root database or in the same container database. Very nice.
Now, what is the condition? In order to clone a bloggable database, first thing you must be connected to the container database and the current container must be the root. So this is a common rule. If you want to create any bloggle database or clone Bluggable database, you have to be in the root database. Also, you must have the create bloggabel database system privileges. So this user, in most of cases he will be the one who creates bloggle database. Also, the container database in which the blogger database is being created must be in read write mode. So this means that these three conditions the same exactly conditions of creating a Blogger Bell database from the seed. Now you must put the bloggerbill database being ecloned and read only mode before you can celom it.
But actually this is not mandatory. It is bitter, but not mandatory. And we will see this in the example. So in this lecture we understand the concept for Cloning Blogger Bell database. Now the question why we need to clone a Blogger Bell database. Actually we have many reasons. Maybe you want for example, to apply a patch so you don’t want to damage your original Blogger Bell database. So you will make a copy, then you will make and apply the patch on that copy, test everything. Then you will pack again to apply the patch on your original Blogger bill database. For example, you want to test performance for something so you can clone the bloggers and try to do some performance in the new copy.
If everything sucks, then you can apply this performance on the original Blogger bill. So, we have many reasons why we need to clone a bloggable database. Another important reason, maybe you have application. You created a nice core application in this slough. So suppose that another customer need this application or need this design. For example, so you can clone this slough and give it to another customer. So this is the power of Oracle twelve C and the upper releases like 18 and 19 C. So thank you for listening and see you in the next lecture. And we will do a nice example in the next lecture.
- Cloning PDBs part 2
Hello everyone and welcome. In the previous lecture we took an overview about cloning bloggerbill database. Today we will do a nice exercise. I just want you to focus with me 100%. 1st thing, my virtual machine is up and running and also my database is up and running. I will log in to oracle user and everything is fine and my database is up and running and also the services is up and running. Now I will go to the SQL developer in my machine and I am connected remotely and I am connected as sys. As Sysgba. Now let’s start doing these exercises. First thing I will make sure con name. You will find now that I am in the root database I will make SelectCon ID name open module from video sign PDBs. I will execute this and you can see that I have three bloggle the seed orclpdb and PDB one and the status is mounted, you can open it if you like. Anyway I will move to the blogger bill database orclpdb and I will make a clone from this blogger bill. So I will make alter session set container equal orclpdb I will execute and you can see now that session altered. I will make sure con name to make sure. So you can see now that I am in the orclpdb. Okay very nice. Now I will make select user name default tablespace comma common from DBA users. I will execute this and you can see that I have all these users. You can see that I have a common user and not common user.
For example the user HR is not common. So this is a specific for the bloggle database orclpdb this means when I make a clone this user also will be included in the new blogger build. Okay remember this okay. And I will make now select star from DBA underscore tables where honor equal HR. So I have a user called HR in the blogger build database orclpdb and this user has many tables. So I will do this select statement and you can see that I have seven tables belong to the user HR. I will make SelectCon ID file ID name from video or sign data file. I will execute this statement and you can see that I have four data files system CSOX and do and user. This means when I clone this debugger database also these files will be copied. Okay very nice. I will make select star from video or sign tablespace. I will execute this and you can see that I have five tablespace system CSOX and do and temp and users. Okay very nice. Now let us create a clone or RCL PDB and name it PDB five.
So I will make copy from this database and I will name it PDB five. But we have to do something before I will go to virtual books. Then I will log in as oracle user. I will make directory for this blockable database. So I will make directory use one app oracle, Aura, data or Rclpdb five. I will take this Controlc. Then I will go to my virtual machine. I will go to home and I will go to other location computer. Then I will go to use one app oracle, Auradata or RCL. And I have this blogger bill. So I have to create a new directory. So make sure that you have this path. So now I will make a right click, open a terminal. Then I will paste that command make directory use one app oracle, Aura data orclpdb five I will execute. And you can see that I already created this directory and it is empty. Wow, wow, very nice. Now I will back to the exercise. Now log in to the Orclpdb and do some uncommitted transaction. So now I have to log in and I need to do some transaction and I will not comment this transaction. Okay. And we will see what will happen. So for example make this update. Update HR employee said salary equals salary plus one where employee ID equal 100. I will take this as copy control C. Now I will go to a virtual machine. I will make SQL plus as Csdba. I will move to the bloggabel alter session set container equal orclpdp I will make sure con name. You will find that I am in the ORC lpdb.
Now I will paste this comment. Update h r dot employees set salary equal salary plus one where employee ID equal 100. So you will find one row updated. I will not make commit. Why? I will do this because I want to prove to you that any uncommitted transaction will not be copied to the new blogger bill, okay. Any dirty buffer will not be copied. So this is a very important information. Now I will go to SQL developer and I will make this query. I just want to make sure from the salary for this employee. So I will make control C and I will paste here. So I will select the salary from employees where employee ID equal 100. So now the salary for the employee 100 is 24,001. Okay, very nice. So I increment only one dollars for his salary. Okay. And remember that I still didn’t do any comment. I didn’t save the data.
Now I will pack again to the SQL developer and I will make alter session set container equal CDB dollar sign root. Why? Because in order to clone the blogger bell I have to be in the root database. I will execute this command session altered. I will make sure con name you can see CDB root. Okay. Now I will do this command in order to create a blogable database from the Orclpdb. So create bloggle database PDB five from Orclpdb. This means that I need to copy this database to this new blogger database. Then file underscore name underscore convert and you will put the path for the Orclpdb and the path for the PDB file. And we do this for file naming. And I describe this in details before. So the file name convert has two phrase. The first phrase the original orclpdb the second one, the new one. Okay, actually you can do this command using this one.
Create bloggable database PDB five from Orclpdb. Then you have to mention create file destination and you will put this path. This means that you will let Oracle do what to name your file. So you can do this if you want oracle manage files to name the files. But I prefer to do this. Okay, so this is better for you. Now I will execute this command, it will take only seconds to finish. Wait a little. So bloggable database PDB file created. So this is exactly a copy from the Orclpdb. If you go now to the virtual machine. Now if you go here to the PDB five, you will find that all the data files has been copied from the Orclpdb. So this blockable database is ready. I will back to the exercise. Now I will make SelectCon ID name, open mode, different visuals and PDP as I will execute this. So you can see that I have container ID five which contain the bloggle database five and the status is mounted. So now I will move to this blogger bell. I will make alter sessions set container equal PDP five. Then I will open it. I will make alter bluggable database open. I will execute this command. You can see now that the blogger bell database has been opened. Wait a little. So bloggable database open altered. Now I will make sure from this secretary select salary from HR employees where employee ID equal 100.
I will execute this. You can see the salary is 24,000. This means only clean data will be cobbt. So if you go to the virtual machine and if you go to the SQL plus here you can see that the salary is here 24,001. Why? Because here I didn’t commit the transaction. That’s why the new copy will not take these changes. So when you make a clone that loan only take the clear data and committed transactions. So this is a very important note. Now I just need to make roll pack here. I will not save this and very nice. Now I will pack again to the SQL developer and I will make sure now that the new bloggable database PDB five contains everything that I need. I will make select username default tablespace common from DBA users. I will execute this and you will find the same users and even the user HR has been copied. Also if I make now select star from DB and the score tables where honor equal HR, I will execute this. You can see also that this new blogable will have the same tables of the Orclpdb. I will make SelectCon ID file ID name from video assigned data files.
I will execute this and you can see the same information. I have four data files. I will make select style from visual or sign table spaces. I will execute this and you can see that I have five table spaces. I will back again to the virtual machine. I will make exit. I will make now listener status. And you can see that I have a new service called PDB Five. Wow, very nice. Now I will go to my original machine and I will go to the TNS names. I will take this control C. Then I will make control V. So it will be here five and here five. Okay. And I will make save. Now I will go to the SQL developer. I will make now a new connection. I will make HR, HR, HR and I will make TNS. Then I will go to PDP Five. I will test and you will find success. So it copied everything in the Orclpdb, including the users, and also all the old passwords for these users. So I enlisted H-R-H-R on PDB five. So it is exactly a copy from the old one, which is Orclpdb. So that’s it. So I hope that that you enjoy in this lecture and you understand very well how to clone a bloggerbill database. Thank you for listening and see you in the next video.