1z0-082 Oracle Database Administration – Managing Database Instance

1. Initialization Parameters & Parameters Files

Hello everyone and welcome back. Welcome to this new chapter. In this chapter we will talk about managing database innocence. This chapter is very important and we will learn many important things. We will start by initialization parameters and parameters files. And this is one of the most important topics in Oracle database course. So now let’s start. First thing when you start up the instance, it reads instance configuration parameters. We call these parameters initialization parameters and the innocence read it from a file called initialization files. So when you start the instance, the instance read configuration file. Inside these files you will find parameters. So Oracle database will not function correctly if we don’t have these files.

Okay, very nice. So it’s like a catalog, it’s like the engine for the database. The database cannot be up without these files because it contains a very important information. Okay, parameters file locations in most platform is in the Oracle home directorygbs. So you can see it by your eyes, but you cannot edit it by assemble way. We will understand how to edit it in details in this chapter, don’t worry. Okay, actually you can use one of the following types of parameter files to start the instance. We have two types of parameter files. In order to start the instance, we should choose one type. Okay, so we should choose one type at a time. The first type called server parameter file SP file. So SP stands for server parameter and this is the preferred choice for all the database administrator. Actually, it is a binary file, you cannot edit it manually. Database can read it and write on it. It is the preferred choice for most of database administrator.

Actually, you can use alter system command for changing parameters. It is automatically created by DBCA which is database configuration assistant. When you create the container database, the default name for the SP file is the ISP file concatenate with the Sid which is mean the NSS name aura. For example SP file ORCL aura. So this is the default name for the SP file. So it will be SP file concatenate with the sid aura. The second type of the parameter files, we call it text initialization parameter and we call it b file. Actually it is a text file containing parameter value, a NAMEVALUE pairs. So you will find name and you will find a value and we will understand in details how we can create a p file. The database can only read this file.

You should edit it manually and restore the instance to refresh the parameter values and the sample p file init aura. So you will find a sample file called initial aura and this is a b file. So the conclusion of this lecture that the parameter is very important and these parameters exist in two type of files server parameter file, SP file and we have Pfile. So the purpose of this lecture is only to understand this information. Don’t worry. We will understand everything in details. So now I will log in in the Oracle user. I will go to home other location. Computer u zero one app.

Oracle product 18 DB. One DBS. And you will find that I have Spfile ORCL aura. And also I have ini aura. This is SB file and this is B file. So Aura Kilde created automatically. When we create the dead database, we will understand how to edit all this information. Please don’t try to delete or try to open these files. Now I just want you to know the location for these files. Thank you for listening and see you in the next video.

2. Search order for a parameter file

Hello everyone and welcome. In the previous lecture we understand a basic information about the parameter and the parameter files. We said that we have two type of parameter files. We have SP file and it is a binary file. And the oracle database can and read and write from this file. We have another file called b file. And the oracle database server can only read this file. So anyway, it’s very simple. When the oracle instance starts, the instance search for SP file or b file. But now the question what is the order for this search? So in this picture we will understand this topic.

So search order for a parameter file. Suppose that you start up your database and you assure the following comment. You make startup. You didn’t mention any option, you just do a startup. So how the innocence search for this file? Actually, step number one the innocence will try to find SP file concatenate with Sid aura. So this is priority number one. So the innocence will try to search for the file which is SP file concatenate with Sid ora for example SP file ORCL aura and ORCL is the innocence name. If the innocence doesn’t find this file, it will search for another file called SP file aura. If the instance didn’t find this file, then it will look for the b file which is init concatenate with Sid ora for example initorcl aura.

So this is the sequence. Okay, very nice. Now a very important note. You can create p file from SP file, but you should name it in it is. ID ora in order the instance can use it automatically in case the SP file not available. So this means that I can create b file from this one. But if you want to create a b file from this one, which is the SP file, you have to name it in it Sid ORAC. This is only if you need the oracle automatically to find or search for this file. But you can name it another name. But in this case you have to specify it during startup. For example, you will make startup b file equal file name.

That’s it. So the lecture is very simple. When the oracle instance starts, it will search for SP file, then it will search for b file. And you can start up your instance directly using a b file. But you have to mention that in the command startup b file equals b file name for example. And we will understand all these in details in the practices. So the purpose of this lecture is only to understand that concept. Thank you for listening and see you in the next video. You.

3. Uses of Initialization Parameters

Hello everyone and welcome back. I hope that you like my course and I hope that you are enjoying in my course. In the previous lectures, we understand what is the initialization parameters, what is the parameter files, and how the Oracle instance saves for the parameter files. But now we have to understand why the parameter is very important. So inside the parameter files we have parameters. So these parameters will have specific values. So now in this lecture we will understand the uses of initialization parameter. The first thing you have to know that the initialization parameters optimize performance by adjusting memory structures. So this is one of the most important thing to optimize performance. So the database administrator could adjust some parameters to enhance the performance. Okay, also sit database y defaults such the amount of space initially allocated for a context area when it’s created. So what is the meaning of context area?

 Actually the context area, it is area for processing an SQL statement. So this parameter will contain some values. So the database administrator can adjust these values in order to make better performance also right?

Okay, very nice. Also set database limits such as the maximum number of database users. Another important thing, specify name of files or directories required by the database. And this is very important. For example, within the Oracle innocence start, you need to know where is the control files and the control files contain metadata about the database files and also the reader log files. So the Oracle instance cannot start without these parameters.

So this is the most important thing and this is the most important tasks for the initialization parameter. So performance database limits, specify names, files and directories. Now a very important note, all initialization parameters are optional. Oracle has a default value for each parameter. So the database administrator only change these parameters in case they need something. Okay, very nice. Remember that we have more than 400 parameters. So the DBA only need a few parameters to understand it and change it. Most of these parameters we will never touch it. Okay, remember this information. So thank you for listening and see you in the next video.

4. Types of Initialization Parameters

Hi and welcome back. I hope that you are doing fine. This lecture, it is one of the most important lectures in this course because this lecture will help you forever. Remember my words, it will help you forever. Now, in this lecture we will understand the types of of initialization parameters. Actually we have two types of initialization parameters. The first type we call it basic parameters. Actually most databases should only need to have the database basic initialization parameters set to run properly and efficiently. This means that we have hundreds of parameters, but actually we have a basic parameters. These basic parameters, you should understand it and you should care about it. That’s it. Okay, basic parameters, around 30 parameters, so it is not a huge number and even most of DBAs not touching even these basic parameters.

Okay, very nice examples for basic parameters like database block size, SGA, target control files I will go to my virtual machine, I will log in as Oracle user, I will make right click and I will open a terminal and I will make now SQL plus as Scuba I will press enter. Now you will find that connected to an ideal instance, I will make startup. Now remember, when I make a startup without any option, oracle or the innocence will search for the SP file first. So it will search for SP fila why ORCL? Because my innocence name is ORCL. Okay, so now I will start up my database. Okay, very nice. I will make sure gone name. So currently I am in the rule database show user. So the user is sys. Now I want for example to see the block size I can make simply show parameter block and I will make enter. Now I only make sure parameter block.

This means that it will show me any parameters that contain this word which is a block. Now you can see that I have a parameter called DB and the score block and the score size and the type is integer. The value now is 8192. This means that the block size now is 8 KB. So we can use shock parameter in order to see a specific parameter. Don’t worry, we will understand a lot of things and we will understand a lot of the views. In order to see all these parameters, I’ll just give you the information one by one. I will start with the presentation. So the basic parameters is the first type of initialization parameter. Actually we have advanced parameters and when I say advanced parameters, make sure that even the expert database administrator don’t know a lot of this information and these parameters. So take it easy, no need to understand everything. This is the basic concept in database administrations. You have to understand the concept, but not everything. Now for example, sometimes we need to change these parameters to enhance performance.

So we have to read about it to understand this parameter. What we will do then we can change it so don’t take it hard okay? It’s very simple now some of these parameters need expert DBAs so not anyone can change this parameter because it needs a lot of experience and a lot of study and even Oracle recommend you not to touch the advanced parameters so that’s it, it’s very simple okay? Example for example database cache size okay, very nice now we have another parameters we call it derived parameters actually the derived parameters could be a basic or advanced okay, what is the meaning of derived parameters? This means that their values are calculated from the value of other parameters. For example, I have a parameter called session and the session derived from the processes. For example, in order to set this number the session the session should be 1. 5 multiply Pi processes plus 22. So this is we call it derived parameters. So this means the parameter depending on another parameter.

Now, maybe you will ask me how you know that this is the correct number. Because we have a reference and we have a manual from Oracle to understand this don’t worry, I will teach you in this lecture the secret to understand the parameters also we have operating system depending parameters and these parameters could be also a basic or advanced, for example, database block size, depending on the operating system. You have to understand that the operating system is a basic rule and a pacing judge in order to set some parameters. Very nice. Now, the best way to know more is database reference manual for Oracle. Twelve C or 18 C or whatever. So what is the solution? The solution is very simple. You will go to for example open a browser like Chrome. Then you will make for example search for Oracle database reference twelve C. You can do 18 C. You can do whatever. For example, I will make now search for Oracle database reference twelve C. I’ll press.

 You will find now many results. But you have to see for example the website that docs dot Oracle. For example, I have here Oracle database reference twelve C. Release one so 12. 1. You will click on this link and you will find a huge manual. You can download this manual. Very simple, right? So if you can go here, you can download it. But anyway, I will give you some nice information. So first thing you have to see that this is the manual. You can come here, for example, to initialization parameter, click here and you will find a very huge information about the initialization parameter. For example, uses of initialization parameter, basic initialization parameter, parameter files and a lot of information. For example, if I make right click on basic initialization parameters and I opened another tab, you will find that this is all the basic parameters. It’s around 30 parameters. Okay, very nice.

For example, I can click on DB underscore name and it will give me a lot of information. For example, this is a parameter tab is a string the syntax is DB underscore name equal value, default value there is no default value. Is it modifiable? No is it modifiable in the bloggable database? No, it is a basic or advanced. It will tell you yes it is a basic parameters that’s it and you can read here a lot of information about this. For example, database name specifies a database Identifier up to add character this parameter must be specified and must correspond to the name specified in the create database statement that’s it. So you have to read a lot of this information, okay?

And beside that you can see here all the parameters that you need, okay? Remember that even the expert database administrator they don’t know all these parameters so according to the requirements they can go and search and try to understand for example what is the meaning of OS underscore rule for me, I don’t know so you have to check all this information. So now if you go for example to sessions I will search for sessions now you will find for session this is the sessions press here you will find that the session is a parameter tab is integer and the default value is derived it will be 1. 5 multiply pi processes plus 22 is it modifiable? Yes you can make alter system to change it. Is it modifiable in the boggle database? Yes range of values and basic? Yes, it is a basic parameters so this is the secret of understanding these parameters. In the next lectures we will understand more and more and we will learn how to alter and do everything you need. Thank you for listening and see you in the next video.

5. About Modifying Initialization Parameters

Hello everyone and welcome. So we understand what is the meaning of initialization parameters, we understand the parameter files, we understand what is the benefits of parameters in this lecture we will understand the concept of modifying initialization parameters. So always said we need sometimes to modify parameters. So let’s start modify parameters to set capacity limits or improve performance. So this is the main reason to change the parameters. Okay, we use Em Express which is mean enterprise manager express and it is a nice tool and we will understand this tool in details later or we can use SQL plus command we have alter session and alter system alter session, it is only for a specific session altar system for the whole system.

Now, how we can make alter? First thing we have a view this view called V dollar sign parameter. In this view we have three important columns is session modifiable, is system modifiable and is PDP modifiable. Now for example, if the value for the session is true, this means that we can change the parameter based on the session limit. If we find it true in the column as PDB modifiable, this means that we can change the parameter according to the blogger database. So we can understand that we have many bloggle and each blogger bill has different parameters. So we have some parameters that can be different from bloggabel to bloggle.

Okay, first thing we have to query the visual or sign parameter. We should understand these three columns then we decide how to make the changes. Okay, now a very important note in the Alter system command we have a close call scope and this is a very important now for example, we have many options for the scope. For example, you can make alter system and change the parameter on memory scope. What this means when you say memory indicates that the changes is made in memory only take effects immediately and persist until the database is shut down. So for example when you change a parameter and the scope is memory, these changes happen in the memory only.

If you switch off your database and open it again, these changes will be no longer available. Also we have another scope, we call it SP file. This mean indicates that the change is made on several parameter file. The new setting takes effects when the database is next shut down and start up again. Actually we have some parameters, we cannot change it except in the SP file. So you change it on the SP file, then you have to pounce the database again and also we have both and this is the default.

So if you make alter system and you didn’t specify memory or SP file, the default will be both and this indicates that the change is made in memory and in the server parameter file and this is the default. We also have deferred and this mean effective for future sessions. So this means that you can make alter and you use the option deferred. But only the future session will take these changes. So this is the concept for changing and modifying the initialization parameters. We will do a lot of examples in order to understand this concept. Thank you for listening and see you in the next video.