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

16. Default scope in alter statement part 2

Hello everyone and welcome back. In the previous lecture we talked about the default scope in Altar statement. Now let’s do a very nice exercise. I open my SQL developer and my virtual machine is up and running. And also the listener is up and running. My service services is up and running. Now I will do a very nice exercise. First thing I have a parameter called open underscore cursors. And this parameter specifies the maximum number of open cursors a session can have at once. Remember when you do any select statement or SQL statement oracle open cursor in the memory. So actually you can use this parameter to prevent a session from opening an excessive number of Kercels. So this is a good for performance. You don’t want assistant to open a huge number of kercels. Now I connected as sysdba and I will make now shock on name. So I will find now that I am in the root database. Now I will do this statement select name comma value as session modifiable s system modifiable suable database modifiable and coned from visual sign parameter where name equal open cursor I will execute this statement and I can see that the value is 300.

So any session it’s allowed to open 300 kerso. And you can see now in the is system modifiable the value is immediate. This means that I can change the value in the memory level. Okay. And I can see now that the session is false. You cannot change it in the session, but you can change it also in the blogger bill database. We will talk about bloggle database parameters later on. Okay, very nice. Now I will do the same query, but I will make it from video science system parameter. I will execute this and you can see the value also 300. This means that any new session will inherit this information. So it’s allowed only to open 300 Kerson.

And you can see here the value for s default is false. This means that this value mentioned in the SP file. You can go to the SP file and you will find this is already in the SP file. Now I will do this. I will make sure parameter SP file I will execute this and I can see that I have a value. This means that the last time I started my database I started using SP file or RCL Torra. You can see here the value. So now when I make alter system sit open cursor, for example 30 one, this is equal to alter system sit open cursor 30 one scope equal both. So this is the first part in my presentations. If a server parameter file was used to start up the database, then both is the default. Now I will pack again here and I will execute this command. You can see system set altered. Now if I make this query from video science system parameter, you can see the new value is 30 one. So these changes happened in the memory and also in the SP file.

So if I go to my virtual machine I will go to home. Then other location computer one app Oracle product 18 DB underscore one I will go to DBS. Then I will open the SP fileorcl aura. And you can see now this is the value opencarcer 30 one. Okay I will close it and I will close it. So I prove the first part of my presentation. Let’s back again. Now go to virtual machine, open SQL plus shut down immediate then start up using a p file. Then try to make altar system set open cursor 30 one scope equal SP file. In this case it will give you error because it’s not allowed to make it on SP file. Now let’s understand it one by one. So now I will go to my virtual machine. I have a test aura you can see here test ora. Okay, very nice. So I will start up the database again using test or first thing I will open a terminal and I will log in SQL plus as Scuba.

Now I will make shut down immediate and I will wait a little until the shutdown is finished. Now I will open another terminal and I will make PWD to take this path. So I will take this path copy. Very nice. I will close. Then I will come to this session and I will wait until the database is down. So now the database is down. I will start the database using a p file test aura. So I will make startup then p file equal I will paste the path, then slash, then test or I will execute and I will wait until the database has been started. And remember that I started the database using this B file. I will wait a little. And now the database is up and running. I will make show parameter SP file. I can see the value is null. This means that I started my database using a p file.

If I go here and I try now to make the following comment. Altar system set open cursor equal 30 one scope equal SP file. I will take this command which is altar system set open cursor equal 30 one scope equal SP file. I will take a control c. I will go to my virtual machine and I will try to paste. Then I will execute. You will find the following error write to SP file requested but no SP file is in use so I will face error. Why? Because here you are trying to write an SP file. But the last time you started your database you started using a. P file. So that’s why it’s not allowed to make the scope equal SP file. The only option is memory. So I also approve this part. If a parameter file was used to start up the database then memory is the default as well, the only scope you can specify. So thank you for letting listening and see you in the next video.

17. CONTAINER clause in alter system & Inheritance 1

Hello everyone and welcome back this lecture, it is one of the most complicated and confusing lectures in this course. Actually a lot of database programmers and DBAs, they have some doubt about these concepts. But don’t worry, I will teach you everything in details. In this lecture we will talk about the container close in Alter statement. Actually you can do the following for example Alter system set parameter equal value, container equal and you have two options current or all and the current is the default. Now let’s talk about this concept one by one.

You can specify the container close when you set a parameter value in the container database. So actually we use this close, which is a container usually in the container database. Okay, a CDB uses an inheritance module for initialization parameter in which the blogger bit database inherits initializations parameter values from the root. So actually this mean that when you change the parameter and you change it in the root level, this means that these parameters will affect also the blogger bill databases and we call this inheritance. Okay, this is very important.

Now a blogger bill database can override the root settings for some parameters and this only when the value for the column is BDB underscore modifiable as a true. So when you find this column as a true, this means that the blogger able database can override the setting from the root. For example, we could see the open cursor in the route 300, but in the bloggabel 500. So this means the blogger bill override the setting for the route and we will see that in the exercises in details in the next lecture. Note when the Blogger Bell database override the route settings, a new record will be added to a view called v-dollar sign system underscore parameter.

Now, a very important note if you specify container equal all, then the parameter settings applies to all containers in the CDB, including the roots and all of the lockable databases. The current container must be the root. So this means if you are in the root database and you make Alter statement for a parameter and you mention container equal all, so this means you are doing this change in the root and all the bloggers database inside this route.

Now, if you specify container equal current, then the parameter settings apply only to the current container. But when the current container is the root, the parameter settings apply to the root and to any bloggable database with the inheritance property of a true for the parameter. But this is only if no override done before pi the blogger bill.

So this means even if you are in the container database and you make container equal current, this will affect also the Blogger Bell database, but in one condition that the Blogger Bell database didn’t override this value before. I know that this is confusing, but when we do one example I will show you all all these things in details. So I just want you to repeat this lecture. In the next lecture, I will prove for you all these points. Thank you. And see you in the next lecture.

18. CONTAINER clause in alter system & Inheritance 2

Hello everyone and welcome. In the previous lecture we understand the container clause in the altar system statement. We will do a very nice exercise. First thing I open my virtual machine. Now I will make right click and I will open a terminal. The first thing that I have to do that I want to login in SQLPlus as Csdba. Now I want to shut down the database and I want to start up my database according to the SP file. Because the last time I started my database I started using a p file. I don’t want to use a p file in this lecture, I want to use the SP file. So I will make shut down immediate. I will wait a little until the database is down, then I will start up the database again.

So now the database is down, I will make now the command startup. So now the startup will be paste on the SP file ORCL or because this is the default SP file. Okay, very nice. So now I will wait a little okay, now I will register the listener alter system register. So this means that I will not wait 60 seconds until the listener register the services. So now I will exit from here and I will try to make now listener status. So I can see now all the services is up and running. So now I will go to desktop, this is my original machine and I will open SQL developer and I will make connections to the SAS user. So I will go to the CIS user, this one, because this connection, I already did it before.

I will click and the session is connected. Just to remind you, I will go here, I will make right click properties and you can see this is the connection details cess and the role is Sysdba. The hostname is CISCOM and the port 1521 and the service name is Orcl. com. Very nice. Now what I will do, I will start executing the following commands. I just want you to focus on this very well. Remember that you have the attached file for this lecture. The first command I will make showdown underscore name I will execute. So you see now that I am currently in the root database. Now I will execute the following SQL statement or select statement from video sign system parameter where name equal open kercels and we talked about opencastle before. I will execute the selected statement and you can see now the current value for opencursors is 30 one. And you can see now that I have only one record. So I have one record and the value of Suable database modifiable equal true. You can see here equal true. This means that the bloggable databases can override this value.

Okay, this means the blogger bell database inherit initialization parameter value from the root. And let’s see so now currently the bloggable database will read 30 one kercer. Because I have only here one record. This means that the blogger database doesn’t change this value yet. Now let’s approve that for you. First thing I will make alter system sits open cursor equal 400, container equal current. Actually if you do current or all it will be the same in this case.

Why? Because if we back to the presentation and you can see here, if you specify container equal current then the parameter settings apply only to the current container. But when the current container is the root, the parameter settings apply to the root and to any blogger bell database. Why? Because the blogger Bell database still didn’t override this value.

I will pack again here and I will execute this. So currently if you make container equal current or all, it will be the same. I will execute this system set altered. Now if I repeat the selective statement from Vidalia science system underscore parameter where name equal open cursor, I will execute this. I will find one record and the value is 400. So now any new sessions in the route or the blogger bells will read this parameter. So this means you are allowed only to open 400 kerso. I will approve that for you. Now I will make alter session setcointender equal orclpdb. So this means that I will move to the blogger able database orclpdb I will execute this command session altered. I will make sure current name I will execute. You can see that I am in the orclpdb now I will do the same query from video science system parameter. I will execute this and you can see now the value is 400 and the container ID is zero. This means that I am still inherited this information.

Now I will do the following. I will make alter system set open kerso e 500 container equal current. So I am doing this change inside the Vlogabelle database and here it’s not allowed to mention all, you can only mention current. So this means that this changes only will be for the blogger bill database or Rclpdb. I will execute this. I will execute this. You can see system sit altered. Now I will repeat this query so I can see now that I have the container ID three the value for the open cursor is 500. So this means that this changes only for this debug gable. Now what will happen if I move to the root? I will make alter session setcontender equals CDB dollar sign root. I will pack here. I will execute session altered. Now if I execute the semi query in the root database, I will have now two records. So I can see the value 400 for the root and 500 for the container ID three.

Very nice. So this means that the blogger database overwrite the setting for the root. Okay, so currently now I am in the root database. Now I will do the following I will make alter system sit opencaster equal 410 container equal current. Now only the route will be affected. Why? Because when the container or the route try to change this value, he will see that there is a customized value for the bloggable database so he will not touch it why? Because I make container equal current so in this case the only change will be for the root not for the pluggable he will not touch it because he finds a customized value in the bloggable. I will execute this statement. Now I will make the same query from visualarian system parameter. So I can see now that 410 and 500 I didn’t touch it now if I do this now alter system set opencaster equal 410 container equal all this means that I will remove the setup for the bluggar bill. So this will reset the values for all the blogger bills container and also the root so when I do this so I will execute this statement now if I make this query, I will find only one record. So this means that I reset the blogger build database again from the beginning and the value is 410 for all the root and all the blogger build databases. So if you practice the presentations, if you specify container equal all then the parameter settings apply to all containers in the CDB including the root and all the blogger build database. But you should do this command when you are in the root database I prove all these scenario for you. Thank you for listening and see you in the next video.

19. About Automatic Diagnostic Repository

Hello everyone and welcome back. In this lecture we will talk about automatic diagnostic repository ADR. Actually before I start in this topic, I just want to give you a very good information. First thing, no need to go deep inside the this lecture or inside this topic. I just want you to focus only on the things that I will give you. Related for the exam and related for your information. So don’t try to go deep more and more related to this topic. Because in the Oracle University track they didn’t focus on this subject, they just give a basic information about this subject. So now let’s start first thing, what is the automake diagnostic repository?

Actually it is a file paste repository outside the database. Actually it is a collection of files and folders. It is a systemwide central tracing and logging repository. Actually it store database diagnostic data such as traces, added log, health miniature reports. So actually this information will help the database administrator to trace files and to investigate about some problems and also internal errors. So the purpose of the ADR is to provide trace files, alert log and health monitor reports. Okay, actually the typical installation will have the ADR pays. It is exactly like the Oracle pays. So if you go for example to the virtual machine and you try to make echo dollar sign Oracle underscore pace, you will find the following path. For example u zero one app Oracle.

So actually this path, it is the same path of ADR. How can I make sure from this? There is a parameter called diagnostic and scored destination and also the AGR home path is like the following AGR pace which is the u zero one app Oracle. Then you will find a filter called diag. Then product type, the product type will be RDBMS. Then I have the database ID for example ORCL and the innocence ID for example ORCL.

Now let’s go to the virtual machine and do some exercise. And I will make a right click open a terminal. First thing I will make the following command echo dollar sign Oracle underscore pace. You have to put Oracle pace in a capital letter. I will press enter. So this is my Oracle paste and normally this is the same of the ADR directory. How can I make sure from this? It’s very simple. First thing I will go to make SQL plus as sysdba I will execute and I will find that I am connected to an ideal instance. I will make now startup. I will wait a little until the database is up.

So now the database is up, I will make the following show parameter dia only it’s enough. And I will press enter. So you will find that the diagnostic underscore destination it is use one app Oracle. So it is the same of the Oracle Pays. Now let’s go to see that in the files. Now I will go to home. Then I will go to other location computer then I will go to use one app Oracle. So actually this is the Oracle pace and also the ADR directory. Now if you go to the presentation you will find that the ADR home is ADR pace. Then I have a folder called diag then the product type, then database ID then innocence ID. So if I go here if I go to Diag you will find a folder called RDBMS then you will find the database ID or RCL then the innocence ID or RCL. So actually this is the collection of files and folders related to ADR. Now there is alert very important and I have incident, I have health miniature reports and I have a trace. So actually these folders contain files and this file is very nice and important for the database administrator to check information about errors and tracing and alert log. So I just want you to know this information now and we will continue in the next lecture.

20. The alert log Part 1

Hello everyone and welcome back. In the previous lecture we took an overview about the ADR and we said that it is a collection of files and folders for tracing alert log and Health Minister reports. Now we will talk about the alert log and this is very important. Now let’s start. What is the meaning of Alert log? The Alert log is a chronological log of messages and errors and includes the following items. First thing, it contains information about any non default initialization parameter used at startup. So you can see here all the parameters that he used during a startup and it is the no default parameters. So this is a great information. And also it contains all internal errors aura 0060 also block corruption errors aura 01578 and did block errors aura 00060 that occurs.

Also it contains administrative operations such as some Create, Alter drop statement and startup shutdown and archive lock statements. So also you can see for example create databases, alter databases, not all the DDL operations but some of this operation, but you can enable DDL for other operations and we will see that in the exercises. So using the Alert plug you can see what is the last time you make startup? What is the last time you make shutdown for the database? What is the last time you make create for a new database or bloggable database? So this is a nice information for the database administrator.

Also it includes messages and errors relating to the functions of shared server and dispatcher processes also include errors occurring during the automatic refresh of meteorological view. And the meteorological view is a very very big subject for the database programmers. You have to understand this topic because it’s very important. So if you are database programmer, just go and search about meteorology view, it’s a very nice topic. Now the locations of the various diagnostic directories can be displayed using a view called Vidar sand diag underscore Info. So if you make select name comma value from this view you will find all the directories that you need to investigate about these directories like the

alert plug and the incidents and the trace files. Now you can view the added plug by a text editor or using a utility from Oracle called Adrci. Actually I don’t prefer to view the files using this utility, it’s little complicated. Using the VI command from Linux you can easily search for anything you like using a text file and I advise you to use this method. So now let’s go to the virtual machine and I will log in as Oracle and I will open a terminal, I will make now SQL plus as Csdba. Now I will make some format before I make the query. So I will make column name format a 25 and I will make also column value format a 50. Now I will create the secretary select name comma value from video sign diag underscore Info and I will press Enter so actually this is the directory that you need. For example the AGR pace is u zero one app oracle the ADR home is u zero one app oracle diag RDBMS orclorcl the first one for the database ID, the second one for the instance name. For example the trace file exists in this directory and there is algebra for the alert, for the incidence, for the c dump and hail smelter and also this is the default trace file. So actually when the database administrator run this query, he will know where he can find the trace file, the alert, the incidents, the health meter reports. So this is the first step you have to see and you can see that active a problem count zero. Active incidents count zero. So until now I don’t have any serious issue in order to contact the Oracle support. If you find here for example the account not zero, you should contact the Oracle support to investigate about these issues. Okay so now I can see now that this is for the incidents, this is for the health report, this is for the alert, this is for a trace. Each folder contain specific informations and believe me this information is little complicated and no need to go deep about it. I just want you to focus only on the things that I will give you. Okay? That’s it. So thanks you for listening and see you in the next lecture.