1z0-082 Oracle Database Administration – Managing Database Instance Part 5

21. The alert log Part 2

Hello everyone and welcome back. I hope that you are doing fine. In the previous lecture we took an overview about the alert look, today we will navigate and we will see the alert logs. So I open my virtual machine. I will go to to home. Then I will go to other location computer one app Oracle. Then I will go to Diag. Then I will go to the product RDBMS. Then the oracle and oracle again. So this is the directory. Actually you can see the alert log in two places. In a folder called Alert and also in a folder called Trace. Now let’s go for the first folder which is alert. I will open this folder and you will see one file and this file log XML. And this is the XML version for the alert log. Now I will make a right click and I will open it with G Edit. I will wait a little until the file loaded.

And you can see this is the data. You can see from here to here. This is entry. So for example the first time I started Oracle instance it was in this date and the time was like this. You will find some information for example the host ID and also the IB address. So you will find all the alerts for your database in this file. So you can search simply by for example taking this like this, control C then you will make control. And if so you can see here in the rift. You can see for example starting Oracle instance there is 56 times I started my Oracle database. You can search by like this one. So this is the next one. This is the next, this is the next and you can see and investigate about all the starting Oracle instance. A very important note. Searching here is case N sensitive. So you can put small letters or upper cases, it doesn’t matter. Now for example, let’s try to search for Alter data pace and wait a little. So you will find now that there is more than 330 Alter database. For example alter database mount. I did this transaction in this date and this is the time.

Okay, very nice. So anyway we will close this file and I will pack to Oracle. Now I will go to address file. I will open this file. Now the first file you will find a file called alert underscore ORCL actually this is the text version for the alerts I will do the same things. I will make right click and I will go to open with other application. And I will go to view all applications. And I will search for G edit. So I will go for G edit. So I will wait a little. So this is the text version for the alert look, you will find the same information but this is in a text version. Okay, very nice.

So for example, I want to search for shutdown I will press control and F and I will put shut down. So you will find here all the shutdown I make around 144 shutdown. You can search down like this and you can see all the things that you need. Okay, very nice. Now I will close the search. Now I will go until the end of the file. I will go up little until I reach for example to this point system parameter with nondefold value. So this means this is the last time I make startup for my database. I started my database with the following nondiffault parameter. For example number of processes was 300, the open cursor was 410. So this is very nice. In case you lose the SP file, you can know that this is the parameter you used in the last time you start up your database. So this is the concept for the alert lock. So I repeat again the concept of this lecture that you have to understand that I have two places for the alert log. The first one is in a folder called Alert and it contains the XML version. And I have another alert log in a trace folder and contain the text version. Remember that you can open it with any text editor and you can search for the things that you need. So this is the concept in the next lecture we will understand how to view the alert log using the adrci. Thank you for listening and see you in the next video.

22. View alert log using ADRCI

Hello everyone and welcome back. In the previous lecture we understand how to view the alert log using the text editor. In this lecture we will understand how to view the alert log using the Oracle utility adrci. But before that I have to give you a pacing information about the command VI and Linux because this is the main thing you have to know in this lecture. So now what I will do I will make a right click and I will open a terminal. Now I will move to the desktop. So I will make CD desktop. I will press enter. Now I will create a text file in that desktop. So I will write the command touch then for example kha text I will press Enter. Very nice. So this is the file. Okay now I will open this file and I will write High then High then High. Now I will write High capital and High capital then high small and high small and I will write for example here had it okay very simple.

Now I will click save and I will close it. Now I will make vikhay text. So I will open the file using the VI command from Linux I’ll press Enter. So I loaded the data into VI command. Okay, very nice. Now suppose that I want to move to the end of the file. I will press Shift and G. So this is Shift and G. So I move to the end of the file. Now suppose now that I want to search for the last time High. What I will do? I will make question mark then I will make High. So when you make a question mark then high this means that you want to search for the text High from down to up. Now I’ll press Enter. So the cursor move to the last time there is High in the file.

Now to search also from bottom to up. Now press N. So I’ll press N. You can see like this I’ll press N. It go here and it go here. So we can notice now that it is a case sensitive. Because I search for high small letter. Okay, very nice. Now I will go down like this. Now I will make another question mark. Then I will make high capital. I’ll press Enter so it will show you the last time there is High in the text file but it is a capital letter. So this is the last time there is a high capital letter in the file. Piss in it will search also up. Very nice. Now I will go to the end of the file again. Now suppose now that you want to search from up to down. For example I want to search for the first high in the file. You will press, slash, then high. I’ll press enter.

So you can see now the cursor moved to the first high. Now you can press N to go like this. That’s it. It’s very simple. So the concept using the question mark to search from put on using Stash to search from up. That’s it. So now to exit, I will make Shift and Colon, then Q, then this sign and I will press Enter. That’s it. It’s very simple and easy. Now let’s move to the Oracle utility which is Adrci. So I will make adrci and I will press enter. So now I am in the treaty Adrci. And you can see here is the ADR page is Uz one app Oracle. Now I will write the following command show Alert and I’ll press Enter. Now you can see some choices. I just want you to choose the first choice which is Diag RDBMS ORCL maybe in your case it will be choice number two or three. So I just want you to go for Diag RDBMS. Then the database ID, then the Innocence ID. So currently I have to press one. Now when I press one, Oracle will load all the content for the alert log, which we saw in the last lecture in the terminal, I will press Enter. And this is exactly the log alert, but it’s loaded using the VI command.

So now if I want to move to the last line in the file, I will press Shift and G. So now I move to the last line in the file. Now suppose now I want to see what is the last time I started the Oracle database. I will make a question mark. Then remember that it is a case sensitive. So I will make starting. So the S is capital, then small. Then put Oracle Capital Letter and press Enter. So you can see the castle move to here. So this is the last time I started my Oracle Innocence. It was in 18 February 2019. That’s it. If you want to go up, press N. Like this. Like this. Like this. Now suppose now that you want to see what is the first time you make Alter for the database.

So I will press slash. Then I will search for Alter database. I’ll press Enter and you can see this is the first time you make Alter for the database. Alter database mount. Okay, very nice. So this is the concept. Actually I prefer to use the text editor than using this method. Now to exit, I will make Shift and Column, then a queue, then this sign and I’ll press Enter. Now to exit from the trinity, press the Q and to exit to the terminal, press also Exit and that’s it. So this is that concept of viewing the alert log using the Adrci utility. Thank you for listening and see you in the next video.

23. Trace files and Purging Part 1

Hello everyone. In the previous lecture we understand how to view the alert log using the Oracle utility adrci. In this lecture we will talk about the trace files. I just want you to focus only on the information that I will give you. You related to the exam and related to your knowledge. So let’s start. Actually, the trace files contain error information and a very important note contact Oracle support services if internal error occurs. So normally the trace files contain error information. Besides that, it contains information that can provide guidance for tuning applications or an instance.

 And we will see that later in a chapter called SQL Tuning. So this is the most important things you have to know about trace files. Now, each server and packaging process can write to an associated trace file. So this means the server can write and also the packaging process can write to a trace file. How we can distinguish between the server and the package process? It’s very simple. For the packaging process, you will find the trace file like the following. It will start by ORCL the instance ID then the process name. For example DBW zero which is database writer.

Then you will find a number TRC. So when you see this file, this means that this is generated by a packet grand process like Database Writer. Regarding the server process, the name will be like the following ORCL then or then a number TRC. So this is how to distinguish between a trace file related to a pack grander process and address file related to a server process. Okay, very nice. So trace file names for packaging grand processes are named after their processes exception trace files generated by job queue processes. So this means that you can even generate a trace file using a job queue process. Okay, very nice. Now a very important note.

Oracle database includes an advanced fault diagnosis of infrastructures for preventing, detecting, diagnosing and resolving problems. So this means that most of these things are automatic from Oracle database. Okay, a very important note when a critical error occurs, an incidence number is assigned to the error. Diagnostic data for the error such as a trace file is immediately captured and tagged with incidents number and the data is stored in ADR. Now, ADR files can be automatically purged with a retention policy parameter. And this is very important because by days these files will have a huge size. So it should be automatic way to purge these files. Now, let’s talk a little about Purging mechanism. What is the meaning of Purging mechanism? The Purging mechanism allows you to specify retention policy stating how old ADR contents should be before they are automatically deleted. Now, I told you that the server process and the packaging process and a lot of things will generate address files and DDL files and alert files. So by days these will have a big size. So we need something from Oracle to delete these files.

So we have something how old ADR contents should be before they are automatically deleted. Now there are two things you have to know. We have something called the loan retention. The loan retention period is used for the relatively higher value diagnostic data such as incidents and alert lock. The default retention is three, six, five days. So 365 days. And this information is very important. So that’s why oracle keep it for one year. So this information related to a higher value diagnostic data such the incidents and the other clock. Now regarding the short retention period is used for traces and core dump. So it’s not that important. So oracle keep it for 30 days. So we have something called long retention and we have something called short retention. The most important thing you should know that older items are deleted first. And also the perishing mechanism enable you to specify the size based retentions to specify a target size for an ADR home. So for example, you can specify that I need only 1GB for the ADR home. So I don’t need more than 1GB. And you can give a size or target. We will talk about this in the next lecture. So this is the pacing information that I need you to know in this lecture. Thank you and see you in the next video.

24. Trace files and Purging Part 2

Hello everyone and welcome back. I hope that you are enjoying in my course in this lecture. We will take a very nice exercise. And this exercise will help you a lot. We will enable the DDL logging. I told you before that we have have alert log. And the alert log contains some DDL operation. Like for example create database alter database. But it doesn’t contains all the DDL operations. Like for example create table or create a view or drop table. So how we can enable this? Actually we can enable it by database triggers. But we have something more simple using a parameter. Now first thing I will go to home. Then I will go to other location. I will go to computer. I will go to use one. And I will go to app. Oracle, diagnostic. Then I will go to the product RDBMS.

Then ORCL which is the oracle? ID. And this is the instance ID. I can see that I have a folder called look. I will open this folder and you can see that I have a DDL. If you open a DDL you will find it empty currently. Because until now we didn’t enable the DDL. Okay, very nice. So now what we will do, we will make a right click and we will open a terminal. Now I will log in for the SQL Plus. So SQL plus as SDBA I will make surecon name. So currently I am in the root database. Now I want to move to the bloggable database. Orclpdb I will make alter session set container equal orclpdp and I will press Enter. So session altered. I will make now show con name. So currently I am in the bloggabel database orclpdb. Now there is a parameter called Enable DDL Logging. I will make the following command show parameter DDL and I will press Enter. You will find that I have a parameter called enable underscore DDL logging. And currently the value is false. So now what I will do. I will take this one as copy.

Then I will make it true. But only for the session. So I will make alter session fit. Then I will make paste equal true. So this means that I change the value for this parameter to be true only for my current session. So now if I make show parameter DDL, you will find the enable DDL logging. Now is true. Now I will create a table create table test. For example n number. So a table created. If you can see that I have a file created called DDL underscore ORCL log.

Now I will create another table create table test one n number. I will drop the first table and the second table drop table test. Then drop table test one. Very nice. Now if we go and open the DDL underscore ORCL log. You will find all the DDL operation that I did. So you will find the create tabletist and the create tabletist one. And the drop table test. Drop table test one with the dates and time. Wow. Very nice. Also you will find inside the folder DDL, a new log file created and this is the XML versions. So if you open this one, you will find the XML versions for all the things that you do as DDL statement. So you will find this is the create table and it will give you more information that you create a table called Test and you created in the container or Rclpdb.

The container ID is a three and this is the host ID and this is the ID address and also the date and time. Wow. So in this lecture we understand how to enable the DDL. Remember that we enable it only for the session. You can search for this parameter and you can change it on the Blogger bill database or in the Row database. So this is only a practice to enable it for the session only. So I think that this information is very nice and it’s very important for some database administrator in case they need to log all the DDL statement for the developers, for example. Thank you for listening and see you in the next video. You.

25. Enable DDL logging

Hello everyone. Welcome back. In the previous lecture we took an overview about the trace files so this is the presentation I told you that the trace files contains error information and also information that can provide guidance for joining applications and I told you that each server and packagrando process can write to an associated trace file now, if you go to the virtual machine you will find this is the folder for trace if I open it, you will find the following you will find the first one is alert ORCL log and we mentioned this information before now we have a file TRC now, for example, if I ask you is this a pack grant process or a server process? Actually from the name I can see that it is a pack ground process because it starts by ORCL underscore then the process name then a number if it ORCL underscore Aura then number it will be a server trace. File.

Now, a very important note. You will find another extension called TRM. Never mind about this. I just want you to focus about TRC. Now, even the expert database administrator, they don’t understand everything about the acres, files. Even if you try to open it, most of times you will not understand anything. So never mind about this topic. Okay? So the most important thing you have to know that in case if we have something dangerous, the trace. Files will move to incidents. So if you go to the incidents and try to open it, you will find the folder is empty.

So this means that I don’t have any serious issues. So most of the trace files only additional information for the packaging processes and the server processes. So don’t go deep. More and more related to this subject. Okay, very nice. Now what we will do now we will open a terminal and we will write the following comment adrci so now I move to the treaty. Adrci now let’s do some exercises. And these exercises only for the exam information and for your knowledge. And this is all you need to know. Don’t try to go deep more and more now you will find it attachment this text file now first thing you have to know every time you move to adrci and you want to investigate or query the policies you have to set the path first. So this is the command for setting the home path. You should make sithomepath. You have to make sure that you have this directory. So if you name your innocence and your database different, you should use your original name. So I will take this command, I will take it copy, and I will put here paste, and I will press enter. So this is the first thing you have to do.

You have to set the home path for the Adri. So it is diag RDBMS orclorcl the next step. You can do this. Sequery select size B underscore policy from ADR Control aux. I will take this sequence copy and I will paste it semicolon and I will press Enter. It is a very huge number, right? So actually, I don’t need this amount of size. I have to change it. Okay? So in order to query the reserved size for the ADR, you can write this query, which is select size P and the score policy from ADR Control, Aux. So this is the size in pipe and it is a very big size. Now let’s move to the slide. I have another query, and this query will show you the short policy and the long policy.

So I will make select short P underscore policy, long P underscore policy from ADR Control. I will take it copy, then I will paste it and I will press Enter. So the short P policy is 720 and the long P policy is 8760. But wait a minute, man. You told us in the previous lecture that the long retention is three, six, five days. And regarding the short retention is 30 days. But I can see here it’s different. Why? Because this is in hours. Okay, very nice. So if you divide 720 by 24, it will be 30 days. Now, I will make calculator. I will make now 700 to 20 divided by 24. So it will be 30 days. That’s it. It’s very simple, right? So this means that I have 30 days for the short policy and I have one year for the long policy.

And remember that the long policy for a higher diagnostic information like the incidents and the alert log regarding the short policy, it is related to the trace files and core dumps. Okay, now let’s take this command, estimate size underscore policy and I give it this amount in pipe. I will take it copy. I will go to the terminal and I will paste this. What is the meaning of this? This means I am telling the Oracle or the utility, which is the adrci, if I choose the size to pay, for example, 200 megabytes. So you can see, this one is 200 megabyte, 2060 after 200. So this means I am telling the utility if I need only this size for the ADR, what I have to make the short policy and the long policy. I will execute this command and Oracle tell you if you need to put the size for the ADR is 200 megabyte, you should make the long policy in hours this amount. And you should make the short policy in hours this amount. So actually, this help you to specify the values for the ADR sizes.

Okay, very nice. Now let’s go to the exercise again and let’s do the following comment. I will take it copy and I will paste here. So here you are telling the Oracle or the utility, if I want to put the short policy is one to 9 hours. So 192 hours. And I want to make the long policy also 192 hours. How much I should put the size or the total size now? 192. Let’s go to calculation. And we will make 192 hours divided by 24. So it will be eight days. So here you are doing the utility. If I want to keep the files for only eight days what is the estimated file size I need? I will press enter. So all I can tell you if you want to keep the files for the short and the long around eight days you have to put this size in pipes. That’s it. It’s very simple and easy. I will take it copy. For example, I will go to this text and I will put this value here. Okay. So now I want to update the policy. I will make the short policy is 192 hours. The long policy 192. And the size is like this. I will take this one copy. I will execute the first one. Then I will go to the second one copy I will paste. Then I will go to the third one. Then I will go to the third one copy and I will paste. So I make the short policy for eight days. The long policy for eight days. And the total size for the ADR is this amount in pipes. Now if you go and execute this query again CTRL C or copy then paste here you will find now that I change the policy to P ed days. So this means that after eight days the old files will be deleted. Okay. Very nice. So this is the most information you need to know. Now the last thing you have to know is burging manually.

Now what I will do, I will make exit. And you can see now currently I am on this path which is use one app oracle diag RWMS or RCL or RCL if I make BWD you can see now I am currently on this path. Now suppose that I want to see the size currently. Now I will make the following command. DUHS. Actually this command will show you the estimated size currently in this directory I will press Enter. You can see now the estimated size is 32 megabyte. Okay, very nice. Now suppose now that you want to reduce this size. Maybe you have some requirements that you need some space in the server. You need to reduce this file. It’s not good to practice and go to delete these files manually. You have to use the verge. Now I will make adrci and I will make help purge. And this it will give you information about this command. You have to read this information. You can make pairs.

You can make pairs I and give it incidence range. And you can make pairs and give it age. You can make bears then size, then you give it amount. So actually it is a huge topic. But I just want you to give you only the information that you need. Now suppose that you need to reduce the amount for these files to be only. For example five megabytes. Okay, what you will do, you will make purge. Then dash. Then you will make size. Then five. Then you will make six, zeros, 123123. Then you will press enter. Now Oracle will start delete files and it will start from the old.

And this is according to the retention period. So he will try to reduce the amount of this directory. I will wait a little. It will take time. So now the command has been finished. I will make exit. Now I will make the command again which is DUHS. I will press enter. So you can see that Oracle reduce it to eleven megabyte. He couldn’t reduce it more for some reasons. Maybe there is some files. He cannot remove it because it contains a huge data. Okay. So that’s why he reduced it to eleven megabyte. If you go to the original it was 32 megabyte. He can only reduce it to eleven megabyte. So this is the way how to reduce the ADR size manually. I don’t prefer to use any manual method. Thank you for listening and see you in the next video.

26. V$SPPARAMETER

Hello everyone and welcome back. Actually we talked before about video sign parameter and video sign parameter two and video science system underscore parameter and video science system underscore parameter two. But today we will talk about something called vdolar sign SP parameter. To understand this topic we have to do some exercises. Now first thing I open the location use one app oracle product 18 DB underscore one DBS. And this is the SP file ORCL aura. This is the server parameter file. I will open it and you can see now that this file it is not updatable. Now if you can see now the open cursors is 410. Okay, now I will do this exercise. I will make a right click and I will open a terminal. I will log in SQL plus as SDBA. Now the first thing I have to do, I need to shut down the database. So I will make shut down immediately. I will wait a little. Then I will start up my database again. So it will be started according to this SAP file. After that I will show you what is the difference between video sign system parameter and video sign s parameter.

And this topic is very important. Okay. Now I will wait a little until the database is down. So now the Oracle instance shut down and finish. Now I will make startup. I will wait a little until the database started. Very nice. I will make alter system register system altered. So I did this command because I don’t want to wait 60 seconds until the listener registered the services. So if I make now exit and I make listener status, you will find all the services is up and running. Now I will minimize my virtual machine. And I will go to the SQL developer. I will open it in my machine, my original machine. I will connect remotely to my virtual machine. Okay, very nice. So now what I will do, I will go to usersis connection, I will open this connection. So actually this is sysdba and it is in the root database. Very nice. Now I will execute the following commands. Now I will make showcon name.

You can see now that I am in the root database. Now if I make this query from visualar science system and the scope parameter where name equal open cursors, I will execute this command. And you can see now the value is 410. So it is exactly the same SP file. And you can see that system modifiable false system modifiable immediate and blogger database through its default false. When you see here false. This means that this value coming from a server parameter file or p parameter file. Very nice. OK, now I will make this command alter system set open cursor equal 310 container equal all scope equal memory. So now I will make the open cursor for the root database and all the lockable database 310.

But this scope will be memory so this means that these changes will not be written in the SP file, right? Very nice so now I will execute this command so now I will execute this command and you can find that system set altered now if I do the semic query from visual assign system parameter I will find these changes right? I will execute you can see now 310. So now all the sessions inherit this parameter. Okay, very nice. But these changes, it is not written to the SP file. Because if you go to the SP file and you open it, you will still see 410. But Oracle introduced something very nice called the dollar sign SP parameter. And this view displays information about the content of the server parameter file.

If a server parameter file was not used to start up the instance then each row of the view will contain false in the as specified column. Okay, very nice. So now I will make select star from the dollar sign SP parameter where name equal open cursors I will execute this, it will show you now 410. So actually this is a very nice information suppose that I don’t want to go and to see the original value for the open cursors in the SP file I can run this query to show you the current information in the current SP file. So this is the purpose of this review. Very nice. So this is the concept of this lecture. So thank you for listening and see you in the next video.