1z0-082 Oracle Database Administration – Helpful queries to explore Oracle DB architecture Part 4

11. cdb_tablespaces / V$TABLESPACE

Hello everyone and welcome back. I hope that you are doing fine in this lecture. We will understand how to query the table space. And we have two main tables CDB underscore table spaces and visualar sign table space. And I assume that you know from now what is the difference between these two. Anyway, maybe you will ask yourself. It is a very huge subject. We have thousands of tables, we have thousands of views. How we can remember all these things? The answer no need to remember because you have only to understand the concept and to remember the main topics. For example, maybe I forget what is the table name for table space. But I remember that I have a subject called table space. I have a subject called control file. I have a subject called temp file.

But I don’t remember these tables. What to do? Actually, it’s very simple. By practice you will notice or remember most of these tables. Another way you can go by Google and search for example how to query the Tim file in Oracle database and you will find millions of results. So don’t take it hard, just take it easy. But for the purpose of the exam you have to study hard man. Okay, so let’s start. I will do now show con name. You will find that the container is CDB root. Now I just want to make sure if the Blogger bill is open. I will make alter blogger bill database or Rclpdb open. I will execute this and you will find the bluggable is already open so no need to do anything. Now I have a table called CDB underscore table spaces.

I will queerly the following columns table space, name, block size, status, contents, conid from CDB underscore table spaces I will execute and this is the result. Actually I have these table spaces in container ID one and I have these table spaces in container ID three. So this is the table space name and this is the block size. It is 8192 so it is eight kilo pet. And the status is online and the content is a permanent. So this means the system is a permanent content. But for example, the undo is undo content. But for example the temp table space is a temporary content. That’s it. Okay, so I have here eight rows. But now if I make select star from video sign table space, I will find more, right? Because I told you that the video sign it will show you a lot of information. Even if the database is hidden or seed or closed. I will execute this and you will find that I also have the table spaces for the container ID too, which is the seed database.

Now I will make alter sessions set container equal or Rclpdb. I will move to the pluggable. Now if I execute this statement CDB underscore tablespace or DB underscore tablespace it will give you the same result because it will show you only the data in the blogger bill. I will execute this, it will show you five rows. I will execute this, it will show you error. Why? Because I don’t have con ID in DBA underscore tablespace. So I will remove this column.

That’s it. I will execute again. I will execute again. And you can see five rows. And also now if you make select star from visual assigned tablespace, it will show you also five rows. Why? Because I am currently on the Blogger build database. I will execute this and you can see that I have five rows. So in this lecture we understand how to query the table space in each blogger builds and also in the container. And we understand what is the difference between CDB underscore table space and video sign tablespace. Thank you for listening and see you in the next video.

12. v$logfile / V$CONTROLFILE

Hello everyone and welcome. In this lecture we will learn how to query the log files and also the control file. This lecture is very important because we will learn a very important concept. So I open my virtual machine and my database is up and running and also the listener up and running. And this is the services I will open my SQL developer and I connected as sys. As sysdba. First thing we will make surecon name I will execute. So you will find that I am on the database route. Okay, now I will make this command in order to open all the pluggable databases. Alter bloggable database all open. I will execute and you will find pluggable database all altered. I will make this query select conid name open mode from video sign containers. I will execute this statement and you can see that I have a root database and I have the C database and I have one blogger database. Okay, very nice.

Now we will learn how to query the log files. Actually we have Vdlarsign log file. But before that we have to review some information and I give you this information in the chapter one. So first thing read log files stores all the changes made to the database as they occur. So this is the purpose of reader log files. The database maintains online reader log files to protect against data loose, especially after an instance failure. The online reader log files enable Oracle database to recover committed data that it has not yet written to the data files. So the purpose of redo log files for backup and recovery and the reader doc files stores the changes that happen to the database. Actually we need minimum two reader log files. So any database it will consist minimum two reader log files. Why? One is always available for writing while the other is being archived. Okay, very nice.

Now a very important note. Readlock files exist in the whole instance, not particular container. Okay, so it’s not for particular container. So when I make now select star from video sign look file, I will see the following result. I will execute and you will find that I have now three read log files and you will find the container ID zero. So this redo log files belong to the instance, not to a specific container. So this is a very important note. So I have three reader log files and this is the locations for these files. You will find I have also group one, group two, group three, we will understand what is this letter. So I just want you to understand that we use video sign log file in order to query the log files.

Okay, now we will move to another topic, which is the control file. Actually I also give you this information before the control files stores metadata about the data files and online readlock files like names, locations and statuses. And this info is required by database instance to open the database. So in order the instance to be started, he need to know where is the locations for data files, where is the locations for the read look files. So all this information exists in files called control files. Also a very important note. Control files exist in the whole instance, not for particular container. So it is the same of read log files. So I have a view called video sign control file.

I will make select star from video sign control file and you will find that I have two control file, control one and control two. And the container ID is zero. Because this file doesn’t belong to any specific database, it belong to the instance. So this is the purpose of this lecture. And don’t worry, we will understand more and more in the next chapters. So the purpose only is how to query these files. Thank you for listening and see you in the next video.

13. Pluggable Database Save state

Hello everyone and welcome back. In this lecture I will teach you something very nice and very important. Maybe you ask yourself why every time I shut down my database, then I start up my database. I find the Blogger Bell database mounted. Is there any solution to me make it open without open it manually? From Oracle twelve C release two Oracle introduce a nice solution to solve this issue which is alter bloggable database save state. Now to do this, let’s try to do some examples. I will make right click and I will open a terminal. I will make now SQL plus as SGBA. Now I will make this query select name open underscore mode from v dollars sign PDPs.

I will execute sorry. I will make select name open mode from the dollar sign PDPs and you can see that I have the name and open mode. I have the seed is read only. And I have a bloggable database called Orclpdb read write now why I hate SQL Plus? Because it’s not flexible.

You have to do a lot of formatting to see a nice result or a nice format. So for example, why these columns appear like this? I want it to appear beside each others. The reason is very simple. If you make describe the dollar sign PDPs, you will find now that the column name is varchar 2128. So he needs 128 character to show you the data, right? So that’s why it will be on multiple lines. So how to solve this issue, you have to do some format. For example, you will make column name format a 30. So this means that I need the column name to appear as only 30 character. I will enter now I will execute the query again.

I will take it copy and I will paste. And you can see now the result is much better. So that’s why the programmers and SQL developers, they didn’t use SQL plus they use the SQL developer and it is a pissed choice for writing SQL commands. Now let’s back to our subject. So you will find now that the Orclpdb is read and write. Now I will shut down the database and open it again. Shut down immediate. Now I will start up. Now I will make format again copy. I will paste. Then I will write my query again copy and paste. So you will find that the Orclpdb wanted. So what is the solution? The solution to make it permanent. Open to do the following commands. First thing, you have to open it manually. Alter bloggable database ORCL PDP. Open. Then you will do the following command alter bloggable database ORCL PDB save State and now the Blogger Bell database. It will be in a safe state. So now if you shut down your database and open it again, so you will not face this issue, it will be always opened. I will prove that for you. So shut down immediate. Now I will make a startup now I will make format for the column name. Then I will write my query again. And you can see now that the Orclpdb is retried, so it will be always opened. So that’s it. I hope that this solution will help you.