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

11. creating non default name for pfile and startup

Hello everyone and welcome back. In this lecture we will do a very nice exercise. We will create a p file. But we will create a p file with non-default name. For example, we will create a fleetest aura. Then we will start up the database using this p file. Now we will see how we can do this. First thing I will make a right click and I will open a terminal. I will make SQL plus as sysdba. So this is the first step. So my database is up and running. Now I will create the following command. Create Pfile equal test ora from SP file and I will press Enter. You can see file created. Now I will go to home other location computer u zero one app Oracle product 18 DB one. Then I will go to DBS and you will find test ora is. Now I will open this file and you can see this is the parameter you have.

Okay, very nice. Now I will do something nice. I will go to the SQL plus again. And I will make the following command. Show parameter max idle time. So the parameter max idle time is integer and the value currently is zero. What is the meaning of zero? This mean that I don’t have any idle time. This means that you can open any session and no need to do anything. Just go drink coffee for ten minutes, 1 hour, 2 hours, 3 hours, whatever. Your session will not be terminated because the value here is zero. So zero. This means that I don’t have any idle time. I can open any session and leave it without any transaction or without anything for 1 hour, 2 hours, one day whatever, your session will still active. So now what I will do, I will go to that tissue aura, I will open it and I will make for example copy for this one control C. Then I will make control and V.

Now what I will do, I will pack space until the dot here. I will make max idle time equal ten. So now this means that if you open a session and you didn’t do anything on this session, no transactions, no mouse moving, nothing for ten minutes. After ten minutes Oracle will terminate your session. Okay, very nice. So now what I will do, I will click save and I will close it. Now I will open the SQL Plus. Now I will make shut down for the database. Then I want to restart the database. But I will use this aura. So now I will make shut down immediate and I will wait a little. And after that I will make startup for the database. Okay so now the database is down. Now I will start up the database using test ora which is the p file that I have created now. So I will make startup Pfile equal test ora and I will press Enter. Now you can see for example, the following error couldn’t open parameter file test aura.

Why? Because I am using non default name. So I have to specify the location. So what I will do, I will open another terminal and I will make PWD. So I will print the current directory. So I will print your one app oracle product 18. DB underscore DBS, I will press here and I will take this path copy. Now I will make startup Pfile equal. I will put this one then test aura and I will press Enter. So now the database will start up without any issue. According to this test aura which is a p file. Okay, very nice. I will wait a little. Now the database is up and running. I will make show parameter SP file and I will press Enter. I can see the value for the SP file is null. This means that last time I start up the database, I started the database using a Pfile, not SP file. Remember that the Oracle instance can only read a p file. Now let’s see the effects for the value max idle time.

So I will make sure parameter max idle time I can see now the value is ten. Wow. So now any session will inherit this information. So if you open any new sessions and try to show parameter max idle time, you will find it ten minutes. Okay, very nice. Now what I will do, I will shut down the database. Shut down immediate. Then I will start up the database again. But I will only make startup without any options. So I will wait a little until the database is down. So now the database is down I will make startup. So now when I make a startup, oracle instance will search for SP file first and it will find SP file ORCL Aura. And in this file I don’t have any value for the max idle time.

So this means that it will take the default value again. So what I will do, I will go to the SQL plus and I will make a startup. After that I will query the parameter max idle time. Let’s see what will happen. I will wait a little until the database is open. So now the database is open. I will make now show parameter SP file and I can see now that the value is not null. This means the last time I start the database I started using SP file. Now I will make show per meter max idle time. And you can see now the value is zero. This means that this is a default value from the instance. Because I start up the database again using SP file or so in this lecture we learn how to create a B file with no default name. We learn how to edit this p file, we learn how to startup the database using this p file. And also we learn a lot of important things. Thank you for listening and see you in the next video.

12. alter system example ( memory )

Hello everyone and welcome back. I hope that you are enjoying in my course. In the previous lecture we learned how to create a p file and start up the database using this p file. In this lecture we will do some practice regarding the alter system statement. So now I will make a right click, open a terminal and I will make SQL plus as Scuba. Okay, so I am connecting to SQLPlus. I will make the following command show parameter max idle time. So currently the value is zero. Now I will make the following comment alter system sit max idle time equal for example 30 scope equal memo rate. So now what will happen? I will change the value for this parameter to p 30 and I will change it only in a memory level for the innocence. So this means that this changes will not be written to the SP file ORCL ora okay very nice. I will execute and I can see that I have a small error.

Okay, I know what is the issue, it should be L then E. So I will make alter system set max idle time equal 30 scope equal memory and I will press enter. Now I will find system altered. So this changes only for the innocence. If I go to the SP file or RCL aura and open it, I will not see max idle time. So if I open the SP file ORCL aura I will not see any value for max idle time. So I will close this and I will close this. I will open the SQL plus now if I make now show parameter max idle time, I will make copy and paste. I will press enter. So you can see now the value is 30.

So this means the change is only a memory level. So this mean if I start up the database again using the SP file, this value will not exist anymore and it will take the default value. But before that, I just want to prove that for you I will open a right click, open a terminal, I will make SQL plus as sigma. Now if I make show parameter max idle time you will still see that 30. So any new session will read this value which is max idle time equal 30. I will give you another proof. If I make select value from v dollar sign para meter where name equal max idle time you will see the value is 30. I will make edit and I will press escape. Then I will press the letter A for inserting new text. Then I will go here and I will make V dollars sign system underscore parameter I will press escape, then shift and colon, then W and GQ I will execute. So also the value is 30 for the view vulgar science system parameter.

So this means any new session will inherit this information. Good, very nice. So now what I will do, I will make now shut down immediate. Then I will start up the database again using the SP file or RCL aura which is the SP file. I will wait a little until the database is down. Then I will make only startup without any option. Okay, the database is down. Now I will make startup. So this means that the Oracle instance will start up the database and it will search for the SP file and it will find the SP file ORCL or here I don’t see any value for the max idle time. This means that the value for the max idle time will be resettled again to zero which is the instance default parameters.

I will make startup. I will wait a little until the database is up. Then I will make show parameter max idle time. So the database is up and running. Now I can take this query for example select value from v dollar sign system parameter where name equal max idle time. I will take it copy. Then I will paste it here I will press enter and you can see the value now is zero.

So because the instance read the parameters again from the SP file ORCL or. So in this lecture we understand that when we change the parameter in memory level it will stay until we start up the database again using SP file. So this lecture is very important. In the next lecture we will do the same exercise but we will make the scope equal SP file and we will see what will happen. So thank you for listening and see you in the next video.

13. alter system example ( spfile )

Hello everyone and welcome back. I hope that you are doing fine. In the previous lecture we understand the altar system statement and the scope was memory. In this lecture we will see the altar statement and the scope equal SP file. Okay, very nice. So currently I am in this location which is u zero one app, Oracle product, 18 DB. Underscore one DBS. If I open the SP file ORCL aura, I will not see any value for the max idle time. So now let’s see if you can see here in the file I don’t have any value for the max idle time. So I will close this and I will close this.

Now I will go to the SQL plus and I will make now alter system set max idle time equal 50, scope equal SP file. So this means that the changes will be written on the SP file, but the changes will not take effects. Now I have to restart up the database again to take the effects, because I didn’t mention here the memory. So I am making only the changes on the SP fileorcl. org. Now I will press enter, you will find system altered. Now if you take this query which is select value from video science system parameter where name equal max idle time. I will take it as copy.

Then I will paste it here, I will put semicolon and I will execute. You can see the value is still zero because the changes only happen in the SP file ORCL now if I open this file spfileorcl aura, you can see that max idle time equal 50. Okay, so now what I will do, I will close this one, close this one. I will make now shut down immediate. After I shut down the immediate, I will start up the database again. When I start up the database again, it will read the SP file and it will apply the new changes. Okay, very nice. So now let’s wait a little. So the database will be down after little minutes. Okay, now I will make startup. So Oracle will read the SP file or RCL ORAC and it will fit the parameters according to this SAP file. I will execute, I will wait a little. So the database is up.

Now I will make paste for the SQL query, select value from video science system underscore parameter where name equal max idle time. And I will put Silicone. I will press enter. You can see now the FX is 50. So now any new sessions will inherit this information. Okay, very nice. So in this lecture we understand what is the meaning of the scope equal SP file. So when you make alter statement and the scope is only SP file, the changes will be written on the SP file. But you have to start up the database again in order the effects take the changes. Okay, very nice, thank you for listening and see you in the next video.

14. alter system example ( both )

Hello everyone. So until now we learn the alter system statement on the scope of memory and also the SB file. Today we will do a nice exercise regarding alter system. The scope equal both. Now I make this really select value from v dollar sign system and the scope parameter where name equal max idle time. The value currently is 50. Now a very important note. If you try now to alter system and try to increase this value, it will give you error. Okay? So you have to make a value less than 50. I will teach you that. No problem. Anyway, let’s do this practice.

I will make now alter system set max idle time equal 20, scope equal both. So this means that these changes will happen in the memory and also in the SP file. I will press enter, you will find system altered. If you try to open now the SP file or RCL dot aura, you will find now that the max idle time equal 20. Very nice. So now the changes happen in the memory and in the SP file. So, if you start up the database again, you will find 20. Now what I will do, I will make alter system sit max idle time. I will take copy and I will paste here. I will put for example 30. And the 30 is more than 20, right? It will give you a row scope equal both. I will press enter.

You will find failure in update. SP file parameter cannot be modified because the specified value is invalid. Now, to solve this issue, we have to reset the parameter. So we have to remove it from the SP file. How we can do this? It’s very simple. We will make alter system reset max idle time scope equal both. So this means that I will reset the value for the max idle time in the SP file and also in the memory. I will press enter, you will find system altered.

Now, if you go to the SP file or Credit aura and open it for example in text editor, you will not find any value for the max idle time. So I will close this one. Now, if I make now alter system sit max idle time, I will take it copy paste now I will make for example 100 scope equal both. It will work without any issue. So, if you open now this file and you will find max idle time equal 100. Very nice. Now the last important note. Not all the parameters, you can change it by scope equal both. Some parameters, it only can be changed in SP file and need to restart the database again. We will talk about this later. So anyway, we learn what is the meaning of algebra system in the scope of memory and also the SP file and both. And also we learn how to reset the parameter, which is mean removing it from the SP file. Thank you for listening and see you in the next video.

15. Default scope in alter statement part 1

Hello everyone and welcome back. In this lecture we will understand a very important topic which is the default scope in Altar statement. Now, as you know that we can make for example Altar system sit parameter equal value and we put a scope. Now, if we didn’t put the close scope, what will be the default? Actually, we have two cases. The first case if a server parameter file was used to start up the database, then both is the default. So if the last time you started your database using SP file, then the scope is both. So this means alter system set parameter equal value it will be the same. Exactly. Alter system set parameter equal value, scope equal path.

So this is the first thing you have to know. Now, if a parameter file was used to start up the database and I mean here a Pfile, then memory is the default as well. The only scope you can specify. So this means if you started your database using Pfile, the only option you have is memory. So in this case, alter system set parameter equal value it will be alter system, set parameter equal value, scope equal memory. So this is the most important thing you should know in this lecture. In the second lecture we will do a nice exercise to tell you out these options. Thank you for listening and see you in the next video.