1z0-082 Oracle Database Administration – Creating PDBs Part 3

10. Unplugging and plugging in PDBs part 1

Hello everyone and welcome back. In the previous lectures, we understand the cloning of bloggabil databases. In this lecture we will understand a very nice topic which is unblogging and blogging in blogabill databases. What is the meaning of this? Now suppose that I have a server and in this server I have two container databases container Database One and Container Database Two. Remember that each container should have at least one root and one bloggable database seed and zero or more bloggle databases. Okay, very nice. Now suppose that I have in the container database one, I have a blogable database called PDP one. I can unblock this bloggable database from this container and move it, or put it in the another container database too. So when I take it off, I call this unblock.

And when I put it in another container, I call it blog in. So unblogging a Bloggerbill database is disassociating the bloggable database from CDB. Plugging in a Bloggered database is associating a bloggable database with a CDB. A very important note you can plug a BDB into the same or another container database. So I can remove it from CDB One and also put it again in CDB one. So I just want you to imagine the scenario. Like I have computer One and computer two. I have for example, a hard disk. I can take this hard disk from here and put it in another computer, right? So this is the scenario. And also I can take this hard disk outside.

Then I pack it again to the same container or the same computer. So just imagine it that you want to remove something and put it in another container. So that’s it. Now the question why we need to do this? Actually, we have many reasons. For example, the database administrator, he need to test this container database without this sublough. Maybe he finds that the performance is very bad for the database. So he think that the reason for the bad performance. This is a bloggable database, so he just want to remove it. Then he will check, for example, what is the problem and he will try to solve these issues. So this is one of the reasons to test performance. Okay, very nice. Now what is the steps needed in order to do these steps? Actually, it’s very simple. In order to unblock and blogging in a bloggable database, first thing we have to close the BDB one. So I want to take this one and put it here. First thing, I have to close it, right? I cannot move it without closing this subbuggable database. The second step, I will generate XML from this subbuggable database one.

So unblock PDB One to XML. Then I drop the PDB one. But I will keep the data files, because the XML will not contain the data files. It only contains the definitions for the database, but it doesn’t contain the actual data. Remember this. Now when I want to remove this one and put it here. For example, I have to check compatibility. Is it compatible with this database? This is a very important again, suppose that I have computer one and computer two. You want to remove the hard disk from here and put it here. Maybe the other computer is not compatible with your old hard disk. So this is the most important thing you should know. Actually, if you want to unplug it and plug it in the same container database, most of cases 100%, it will be compatible, right? So this is a very important note, because we will do only this exercise because we don’t have many databases.

Okay, very nice. And the last step, I have to block the BDB one using the XML. So after I generate the XML from here, I will unblock this XML here by create database statement and we will see that in the exercise. So again, close it, unplug it using XML, drop it, keep the data file files, shake if it’s compatible, then move it to the BDB one using the XML. That’s it. It’s very easy and simple. In the next lecture, we will do a full exercise to understand this concept. Thank you for listening and see you in the next video.

11. Unplugging and plugging in PDBs part 2

Hello everyone and welcome back. In the previous lecture we took an overview about unblogging and blogging in Blogger Bell database. Today we will do a full exercise about this. I want you to focus with me 100%. Now I will go to my virtual machine and I will go to this path. I have this bloggable database orclpdb I have PDB One and I have PDB Five and I have PDB Seed. Remember that I create PDB Five as a clone from Orclpdb. Now I will unblock this Blogger database and plug it again in the same container and I will give it another name. So I just want you to focus on me. First thing, my virtual machine is up and running and my database is up and running. I will go to the SQL developer in my original machine and I am connected sys as Sysdba. Now I will start doing this exercise. First thing I will make sure con name. You can see that I am in the root database.

Now I will make alter bloggable database all open. I will open all the bloggabil databases. I will execute this and you will find bloggle database all altered. I will make now select conid common name open mode from video sign PDBs. I will execute this and you can see that I have a bloggable database five and the mode is read and write. Now I will move to this subligable database. I will make alter session set container equal PDB five. I will execute this and you can see now that the session altered. I will make for example this security to make sure that I have the tables and everything is working fine. I will make select star from HR employees. I will make this selective statement and you can see this is the result. Wow, everything is fine. Now in order to unblock or in order to create a Blogger bill or drop a blogger, I have to be in the root database.

So I will make alter session set container equal CDB dollar sign root. I will execute this statement and you can see session alter. Now if we go to the presentation, I told you first thing we have to close it, right? So now we need to unlock PDB five. Step one, alter Blogger database PDB five the close immediate. So I have to close it first. I will execute this statement and you can see now bloggable database PDP five altered. I will make sure select an ID name open mode from the dollar sign PDPs. I will execute this and you can see now it is mounted. Now we apply step number one. Now step number two, unblock BDB one to XML. How we can do this? I will park the exercise and we will execute the following command alter bloggable database PDB five unlock into and I will put for example this path. I will generate XML file and I will put it in this location which is use one app, Oracle Auradata, and I will name the XML PDP five XML. So this is very simple.

So I will alter the Blogger database PDP five and I will generate XML file. That’s it, it’s very simple. Now I will execute this command and I will wait a little. And you can see now that bloggers database PDB five altered. Now if I go to my virtual machine and I go to U zero one, then App, then Oracle, then Aura Data, I will find PDB Five XML. So this is contained the structure for the bloggle database, but it doesn’t contain any actual data. Remember this information, if you try to open this file PDB Five XML, for example, I will try to open it and you will see information and you will not see a big information. It’s very simple information. For example, it will contain the bloggable database name, for example PDB five, and it contains the name for the data files. For example, I have a data file called System One DBF and you will find, for example CSOX. You will find and do and also temp and also user one. So again, do you see any information related to employees table or data? Nothing, right? So it is a structure only. So I will close this and I will pack again to the SQL developer. Now if I make now select conid name, open mode from video or sign PDPs, I will execute this. You can see that I still see PDB five. I only generate XML from this Blogger bill, but this blogger still exists. Okay, very nice. In step three I will drop this Blogger bill, but I will keep the data files. And this is very important.

So I will make drop bloggable database PDB Five keep data files. Before that I will go to the virtual machine, I will go to ORCL then I will go to PDB five. If you try to open it, you will find this is the data files, right? So now I will pack to the exercise and I will drop the blurb database PDB Five key data files. I will execute this statement. And you can see now that the Blogger bell database PDB five dropped. But if you go to the virtual machine and try to open it, you will find the data files still exist. So this means that I save myself, I drop the structure for the database, but the data files still in my machine or in the server. Now I will pack again to the SQL developer. I will make now select an ID name, open mode from video, sign PDBs. I will not see the PDB five. Now, if we go to the presentation now we have to check compatibility. Actually we have a script from Oracle, this one, in order to check if this bloggle is compatible. But actually, unfortunately I didn’t see that this option work in Oracle 18 C, maybe we have a small bug. I don’t understand.

But anyway, this step four is optional. It is only tell you if this is compatible or not compatible. I would describe for you the script. Actually this code is a Plcql code and Plcql is a procedural language for Oracle. And it is advanced SQL. So it is extension to SQL. But it has designed features like other programming languages like Java or ASP net. So it contain structure, begin and end. You can create a statement and do whatever you like. So I think that you know information about PL SQL. And I think as a DBA you should have a basic understanding of a Plcql. I have a full course in PL SQL. But anyway, the database administrator doesn’t need a huge information about Plc equal only basic information. So actually this code is very simple. I have declare to define a variable. I have begin and I have end. We call this execution section and this is declarative section. I define a variable boolean.

Then begin. Now I will put this variable equal this package DBMS underscore PDB shake blog compatibility. Actually this is a package from Oracle and this is a function. Inside this package you will pass this package the PDP description file which is the XML and also the PDP name. So the result for this it will be true or false. So this means that you are passing this XML to this package and the result it will be stored in this variable. This variable will contain true or false. If the variable true, then just a print compatible, else a print incompatible. So that’s it. It’s very easy and simple. And this is not mandatory. So this is step only to check that’s it now 100% our XML will be compatible. Why? Because we are shaking in the same container database. That’s it.

Okay, very nice. So now we will move to the last step. The last step. If we go to the presentation blog, the PDB one using the XML. So what I will do, I will create bloggle database PDP test. So this means that I will give it another name using and you will put the path for the XML. That’s it. So create Bluggable database, PDP test using the path for the XML and you will put file name convert in order to change the file name. So you will put the path for PDB five, the bloggle database. And you will put the path for the new bloggable database which is PDB test. Actually this directory, it doesn’t exist. But even if it doesn’t exist, Oracle will be created. So now if you go to the virtual machine, if you go here, you will not find any directory for PDB tests. You can create it and if you don’t want to create it, Oracle will be created for you. Okay, so now I will pack again here and I will execute the following command. I will execute and wait a little and you can see Blogger build database Pdbist created.

If I go to virtual machine, you will find this is the PDB test. If you open it, you will find all the data files. So this means that you have the all data files here, PDB five in case you need a pickup. And also you will have the data files also in PDP test. Wow, wow. Very nice. Very, very nice. Okay, now I will pack to the SQL developer and I will make this. Now select on ID name open mode from video sign PTP. As I will execute this. So I will have a new Blogger Bell database and the status is mounted. I will move to this bloggable database. I will make alter session set container equal PDB test.

Then I will open it alter a Blogger Bell database open so you will find it’s open. Now I will make select star from HR employees to make sure that everything’s fine. Wow. Also I will make SelectCon ID file ID name from video sign data file. I will execute this and you can see that I have four data files. System CSOX and do and user. And also I have the temp files. So we learn in this picture how to unplug and blog it again. So actually it’s like a cloning. Exactly. But here you can move it to another container. So this is the strength of the unplugging and blogging. Okay, very nice. So this is the step and I describe for you this concept in detail. Thank you for listening and see you in the next video.

12. Unplugging and plugging in PDBs part 3

Hello everyone and welcome back. In the previous lecture we did exercise about unblogging and blogging. So in the previous lecture we unblock BDB five and we blog it again to BDB test. Today we will unblock the BDB test. We will do this the same exercise in the previous lecture but we will do small changes. Now I will go to the SQL developer and I will do the same exercise but I have only small changes here. Okay? So now let’s do the exercise again to understand the concept more and more I will make sure on name. Then I will open all the bloggle database. I will make sure from this selected statement. So I have that all these subbuggable database is open and I have the PDB test. Now I will move to the PDB test alter session set container equal PDB test. I will execute this session altered. I will make select star from HR employees. I will execute this and this is the data. Now I will move to the route. Okay, I will close the PDB test in order to unplug it.

 I will execute the following comment which is alter blogger database PDB test immediate. After that I will make sure from this query I will execute this so I can see now the PDP test is mounted. Now I will make alter bloggable database and I will put here sorry I will make control C, alter Bluggable database PDB test and blog into and I will generate XML file called pdptest XML. I will execute this statement and you can see bloggers database Pdptest altered. If I go to virtual machine and I go to Auradata, I will find this is the XML four PDP test XML okay, I will pack again here. Then I will make this query. I will execute so I can see that I still see the PDP test and the status is mounted. Now I will drop the blogger and I will keep the data files.

I will execute this statement. Now I will make this query. I will not find the pluggable anymore because I drop it. Very nice. Now you have to check the compatibility. This is the code, I described it in detail and I told you that it’s not working in 18 C. Just ignore it, no problem. Then I will execute the following command create a Bloggerbill database PDP test one using the XML file that I generate now, but I will make now no copy temp file reuse in the previous lecture I make file name convert, right? But here I said no copy temp file reuse. Now let’s execute this command and see what will happen. I will execute this, I will wait a little and you can see vlogger bill database PDP test one created now I will go to a virtual machine, I will go to ORCL now, if you notice now that I don’t see any directory called PDB test one. If you go here, it’s supposed to create a directory called PDB test one. But actually it will not create any directory. Why? I will tell you why.

Now I will continue. I will make selection ID name open mode from video sign PTPs. I will execute this and you can see that I have a blogger build database called PDP test one. And it is mounted. Now let’s move to this debug gable database. I will make alter session set container equal PDP test one. I will execute this and you can see session altered. Now I will open this bloggable database. You can see now that the Blogger Bell database is opened. So bloggle database open. Now I will make select star from HR employees. I can see the data without any issue.

But now when I make SelectCon ID file number name from video sign data file, I will execute this. So I can see now that the data files exist in the old folder, which is PDP test. That’s it. It’s very simple and easy. So actually the database structure exists, but it reading the data files from where it reading the data files from the PDB test. That’s it. So this is the difference. Actually, I don’t recommend you to do this. I recommend you always to do file name convert and this is Pittal. So in this case, you will find directory for each Blogger bill. And each Blogger bill has a naming convention. Okay, very nice. So this is the most important thing that I want you to know. Thank you for listening and see you in the next video.