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

6. Alter session example

Hello everyone and welcome. I hope that you are doing fine in this lecture we will understand the altercation statement. We will do a very simple and nice example. First thing, I am connected on CIS user and my virtual machine is up and running. I will make this query select name comma value from video sign parameter order by name. So this view, it will show you all the parameters or all the database parameters in the current session I would execute this and you can see now that this is the result. I have the name of the parameter and I have the value. If you make count rows you can find that I have 433 parameters. So remember that I have Oracle 18. C installed in my machine as I told you before that even the expert database administrator doesn’t know what is the meaning of all this information. Okay, but some values are very simple and you can learn it easily.

Now for example, you can see now that the database block size is 8192, which is mean 8 KB. Okay? So this is the first step. Now I will do another query. I will make select name comma value comma ssession modifiable comma s system modifiable comma s PDB modifiable from feedular sign parameter where name equal NLS date format so I have parameter called NLSD format. I will execute this query and I can see this is the result. So this is the name and this is the value and this is the session modifiable system and PDB. I can see now the value for the session is true.

This means that I can change the value for this parameter based on decision but I cannot change it on the system level. I can change it on PDB or the blogger database level. A very important note null doesn’t mean that this is a null value or the default is null. It could mean that this is a derived parameter. So if you go for example to the manual for the reference you will find that the NLSD format the default value is derived from another parameter which is the NLS underscore territory. So this means that this is a derived parameter. So this is not our subject.

Now, I just want to understand that is that format we can change it only on the session and also the bloggle but it’s not allowed to change it on the system. Okay, now let’s understand more. I will make now select Sys data from dual. I will execute this statement and you can see now the Sys date is 4519 because the default date format is Ddmonrr and all the SQL programmer know this information. Now suppose that I want to change the behave to another date format. For example, I can make alter session set NLS underscore date and the score format equal Ddmy. This means that if I make any query and the tables that contains a date it will appear in this format. Okay, it’s very simple. So now I will make this altered session. You will find session altered.

Now I can make sure that the change takes effects for my session. I will make the query again, I will execute this and you can see now that the NLS debt format currently Ddmy. Remember that this effects only for this session. So other sessions will still see the old values. Okay, remember this. So now when I make now select this date from a dual. You will see now that for 2019. So I change it. Okay, very nice. For example to prove that for you I will make for example month. Now I will alter session altered. Now I will execute this query again you will find DD month YY I will make select sys data from dual you will find for February 2019. So if you try to query any date in any column, the result will show you in this format and this only for your session. So if I exit the session and pack again, you will find that these changes will not be longer available. A very important note actually SQL developer do a lot of these changes on decision level. So if you go for example to tool preferences and if you go for example to database and if you go here to NLS for example, you can change the date format to anything you like. So these changes will automatically applied whenever you open your SQL developer. Okay, so this is environment variable paste on the SQL developer.

Anyway, this is not our subject. I just want you to understand in this lecture that first thing we query the visual sign parameter. We saw that the session is modifiable for this parameter. And simply we can make alter session set format. And here you should put a valid date format. Now a question, suppose that someone asks you, can you query all the parameters that we can change it on session level? It’s very simple. I can make select name comma value is session modifiable s system modifiable is blogger database underscore modifiable from video assigned parameter where is session underscore modifiable equal to.

So this is a query, it will show you all the parameters that you can change it on the session. I will execute this query and this is the result. If you make count rows, I have 181 parameters. You can change it on the alter session. That’s it. So this is a good start for understanding the parameters. In the next lectures we will understand more and more. Thank you for listening and see you in the next video.

7. v$parameter VS v$parameter2

Hello everyone and welcome back. This lecture is very important and in this lecture we will understand some important views related to the database parameters. I am connected as CIS user as siesta my virtual machine is up and running and my database is up and running. I will start by this command, I will make sure connate I will execute. So currently I am in the root database now I will make select name comma open mode from video sign PDBs I will execute. So I have two bloggle database the seed and the orclpdb. The first one read only, the second one read and write in case if you have any bloggable database closed just open it using this command which is alter bloggabel database all open. Now let’s talk little about the view vdoer signed parameter.

Actually this view show information about the parameters that are currently in effects for the session and this is very important. So currently in effects for the session. So now I will do this query select name comma value comma is session modifiable s system modifiable sbdb modifiable container ID from visual assigned parameter order by name I will execute this statement and you can see now this is the result. I have all these parameters, I just want you to see that I have a column called container ID. You can see now the value is one and only one. Why? Because I am in the root database currently and this view is only show you the parameters that affects in the current session. So that’s why you don’t find other parameters for the blogger build databases.

Okay, so this is the first thing you have to know now I will do the same query but I will add a where condition where name equal control files. I just want to see information about the control files. I will execute this statement so you can see that I have a parameter called control file and the value is the following I have two control file, control one and control two. Okay now let’s talk about these three columns which is the sassing modifiable is system modifiable and is PDB modifiable. When you see a false in the ascission modifiable, this means you cannot change this value or this parameter on the session level regarding the bloggers ‘database if you find false this means you cannot change this parameter on the blogger database level. But regarding now in the is system modifiable when

you see a false that doesn’t mean that you cannot change it on the system level. So what is the meaning of this? Let’s see these notes note when you see s systemifiable equal false this means it is a static parameter.

This means it could be changed but in SP file only and need to restart the database using SP file. So this means you cannot change this value in memory level you have to change it in the SP file then you have to restart the database to take the effects and we will take exercise about this. I just want you to understand the concept. Now when you see S system underscore modifiable equal immediate this means the changes can take effects immediate. So you can change it in the memory level which is mean change it online. When you see the is systemifiable equal deferred changes can take effects for future sessions we will have a lot of excesses about this.

I just want you to understand that the column in the is system modifiable has a different meaning. Okay very nice. Okay now we have another view called video sign parameter two. Actually this view exactly same video sign parameter. So it show information about parameters that currently affects for decision but it is more readable in case the value has many lines. For example you can see here in the value I have two control file control one and control two. So maybe this is not readable for some people, right? So oracle introduced this view called visual assigned parameter to give more readable result. So I will execute the semic query but I will make video assigned parameter two.

I will execute this and you can find now the result appear in two lines control zero one in one line and control zero two in second line. So that’s it. So this is the difference between visualar sign parameter and video sign parameter two. So in this lecture we understand the following. We understand what is the difference between video sign parameter and video sign parameter two. And also we understand what is the meaning of the column s system modifiable. So when we see false this mean it can be changed but it can change only in the SP file not in the memory. We will see example about this letter. Okay very nice. In the next lecture we will end up understand the view called visual sign system parameter. Thank you for listening and see you in the next video.

8. v$system_parameter VS v$system_parameter2

Hello everyone and welcome back. In the previous lecture we understand what is the difference between video sign parameter and video sign parameter two. In this lecture we will understand a view called video sign system parameter. Actually, this view shows those innocence level parameters and these are what all new sessions inherit. So this means whatever you open a new session, the parameters coming from this view, which is the sign system underscore parameter. Now let’s try to understand it one by one. I will make select name comma value comma session modifiable s system modifiable as bloggable database modifiable s default comma coned from video science system parameter order by name. I will execute this query and this is the result.

I will make count rows. You can see that I have 457 rows and most of the values. You will find it in the container ID zero, which is the basket and this is the instance level. Okay, now to understand this more, I will go to my virtual machine and I will log in as Oracle. Now I will go to this path which is use one app, oracle product 18 DB, one DBS. I can see init aura and this is a p file and I can see SP file ORCL aura. This file, you cannot edit it or change it manually, but you can open it. Now I will open this file and you can see, this is the result. You can see some red lines like this.

This indicates that this file is protected and you cannot change it manually. Anyway, I just want you to focus on one thing. You can see here, this is the parameters. You can see in this file a few number of parameters, it’s around maybe 2025 parameters. But in the view I saw around more than 400 parameters. So, what the meaning of this? Now, let’s back to the exercise and I will describe that for you. So you can see a column, and this column called is default.

When you see the value for this column true. This means that this value is a default value coming from the instance level. When you see the value is false like this, this means that the value of this parameter is coming from SP file or p file. So, remember this information. So for example, you can see here audit file destination. This is the value. And you can see that s default equal false. When you see a false here, this means that this is not a default and it’s coming from SP file or p file. If you go to the virtual machine,

you can see here audit file destination. And this is the value, exactly the same value. You see it in the view, right? Okay, very nice. So the first thing we have to know that anything is default equal to that. This is a default value from the innocence. If you find false, this value exists in SP file or b file. Okay, very nice. So any new session will inherit this information. Okay, now let’s continue. I will do this query. I will do the same equity but I will make where s default equal false and con it equals zero order by name. I will execute this statement and I will make count rows and you will find 19 rows and all these parameters exist in SP file or b file. Okay, now we have to understand what is the difference between visualar sign system parameter and visualar sign parameter.

Now I will do this query where name equal NLS debt format. I will execute this and you can see this is the value for the NLS debt format. The value is null because this is a derived parameter and you can see that I can change it on the session level. Okay, very nice. Now what happens if I do alter session set NLS debt format equal ddmy so this means that I want to change the NLS debt format to my session only to PDD many. So this means that this effect will not affect this review which is video science system parameter.

Now let’s see, I will execute this alter statement. Now if I make this query from video science system parameter where name equal NLS jet format I will not see any effects for this view you can see the NLS jet format. The value is null, which is a derived parameter. So any new session is trying to connect. Now will read this value. It will not read ddmy because these changes only happened to my session. But now if I make the same query from visual assigned parameter where name equal NLS jet format you can see the changes happen on the session and the value is GD mm YY.

So any new session, it will not read this value because it inherited the information from this review. Okay, very nice. Now let’s go to understand v dollar sign system parameter two it is exactly Vidal sign system parameter but it is more readable in case the value has many lines. So if you make this query where name equal control files you will find the result appear in two lines. You can see here control zero one and control zero two. So that’s it. So thank you for listening and see you in the next video.

9. example: Search order for a parameter file and creating pfile

Hello everyone and welcome back. I hope that you are doing fine. In this lecture we will do a nice exercise in order to understand the search order for parameter file. So in this lecture we will understand a very nice topic. I told you before when you start up the Oracle database the instance will try to search for SP file or p file. The order will be like the following the instance will search for a file called SP file concatenate with Sid ora and the Sid is the instance name for example Spfile ORCL aura. If not find it will search for Spfile aura. If not find it will search for a p file and the pfiler name should be init ora example ignitors ora okay very nice. Now I will go to innocence and I will go to this location. Use one app oracle product 18 DB underscore one DBS. So I have the SP file spile ORCL or now I will make a right click and I will make rename. I will put at the beginning for example X-Y-Z and I will rename.

Now I will open a terminal and I will write SQL plus as sysdba and you can see that connected to an idle innocence. So currently my innocence is down. If you find your innocence is up just make shut down immediately. Okay, very nice. So now I want to start up the database. So Oracle will search for the SP file ORCL ora if not find it will search for Spfile ora if not find it will search for init ORCL aura. Actually I don’t have any of these files.

So if I make now startup and I will press enter you will find error failure in processing system parameters because it didn’t find off these files. Okay, very nice. So now I will go here and I will make rename. And now I will make remove for the XYZ and I will make rename again. Now if I make a startup it will work without any issue. So startup and I will execute and the Oracle database will start up without any issue. And this is very important exercise. So I will wait little and the database is up and running. Now I will do another nice exercise. I will create a p file and I will create a p file called ignitors ora from this SP file. And remember that the p file can be edited manually. Okay, very nice. I will execute this command and you can see file created. So if you go here and try to open it, it will open without any issue and you will not see the red lines like in the SP file. And you can edit these values and you can add more values and we will understand that in detail, don’t worry.

So I just want you understand that the b file is very simple to deal with. You can edit it here manually. The database can only read from this but cannot write on it. Okay so I will close this one. Now what I will do, I will go to the SP file and I will rename it and I will put X-Y-Z and I will rename. So now I will open the SQL plus now I will make shut down immediate. So I will stop my database. Then I will try to start up the database again. So when I start up the database again oracle instance will search for the SP file first it will not find it, then it will go for the B file and I already have a B file with the name ignitors aura.

Now let’s see so I will wait a little until the database is down. Now I will make star top. So what happens oracle first will try to search for SP file or RCL or it will not find it, then it will search for SP file aura it will not find it, then it will look for b file and it will find the Pfile that ignitors. Ora I will make a startup now and the aura kill will start up the database based on the P file without any issue. I will wait a little and you can see now that the Oracle database started without any issue. So this is only a smaller practice and we will do a lot of new practices later on. So in this lecture I just want you to understand the sequence for the same file parameter. Thank you for listening and see you in the next video.

10. Understanding the parameter spfile

Hello everyone and welcome back. I hope that you are doing fine. In the previous lecture we created a p file called Ignitors aura from SP file and we start up the database based on this file. Now the question how can I know if the database started by a. P file or SP file? So how can I know? Now I will make exit and I will make exit again and I will make open a terminal. So suppose now the database administrator login as SQL plus as SDBA so the database is up and running but I just want to know the last time I start up the database did I started by SP file or p file? How can I know this information? It’s very simple.

You can make show parameter SP file and I will press enter now when you see the value is null for the parameter SP file this means that you have a startup your database using a. P file I will approve that for you. Now I will go to this file and I will rename it and I will remove the XYZ and I will make rename very nice. I will pack again here and I will make shut down immediate and I will wait a little until the database is down. So now the database is down.

Now I will make startup. Now Oracle will start up the database again but it will find SP file or Rcl. org. This means that the database will be startup according to SP file. I will execute this command and I will wait little until the database is up. So the database now is up. Now I just want to see did I start up the database by b file or SB file? It’s very simple. I will make show parameter SP file and I will press Enter. Now you can see the value for the SP file is the following path. This means that I use this SP file aura in order to start up the database. So when the value is null, this means p file. If the value is not null, this mean SP file. So thank you for listening and see you in the next video.