Oracle base vs Oracle home

Oracle home is the location where the Oracle application is installed. We can have multiple Oracle home location in one server.

Oracle base is used for storing the binary files of the database. We can have multiple Oracle base location in one server. But the standard practice of oracle is to keep one oracle

ORACLE BASE:

One oracle base location can have multiple Oracle home location.

echo "$ORACLE_HOME"; echo "$ORACLE_SID";

If we have pfile not spfile.

Do a mv spfileprod.ora spdileprod.ora_2026-01-24

After that if we write show spfile in SQL prompt we can see that as the database has already started with spfile.

We can't create it as the database has already started with spfile.

Now of shutdown the database and start it again the we will see null as the spfile. It means my database has started with pfile not spfile.

The database would need at least either spfile or pfile to run.

Create spfile from pfile;

If both spfile and pfile got deleted then we can create them from the memory as they exist in the database memory when it got started.

Create spfile from memory;

Process of starting up database:-

1. Startup no mount:- In this state server reads the parameter file (by default it reads spfile, if it's not present then it will read pfile) and create the blank structure of System global area in memory. Parameter file has all the sizes of different parts of system global area. From there it creates them in memory. It starts all the background processes.

2. Startup mount: alter database mount: reads control file. Parameter has their path. From there it will read the control files. If the multiple control files has different contents then the database will also not start. From there the server learns that what data files are present in database as the control files has the metadata of the data files.

3. Open state:- In open state server reads the data files. If any data file is missing physically which are written in control file then it will also not start.

Shutdown immediate PS -ef | grep prod

No output. Only the process of the grep command.

Startup nomount

PS -ef | grep prod

Many output. Many processes of prod database.

All the background processes like log writer, checkpoint and database writer. DBWn there can be multiple the number states which number they are.

Alter database mount;

Mount state server read control file..

Alter database open;

Open state

Startup command has these 3 process run together.

Show parameter control_files;

2 types of view;

Dynamic view :- Taking data from control file. So the database even in mount state can show the data. Any view starting with v$ is a dynamic view.

Static view: Taking data from data file. So can only show the data when the database is in open state. Any view starting with dba_ is a static view.

desc v$datafile

desc dba_data_file

Select name, open_mode from v$database;

Can't see any data because in nomount state the database hasn't read the control files yet. And dynamic views read data from control files. So in nomount state nothing is shown here.

alter database mount;

Can't see any data from static view as they data from data files and in mount state only the control files are read to know the metadata of data files from control files t know which data files are available. But the data files itself are not read yet. So no data is displayed for static views.

Alter database open;

We can see the data files name and their location from the static view dba_data_files

Select table_name from dict;

Select table_name from dict where lower(table_name) like 'v$%';

Can see all the dynamic views through this command.

Log files:

Select member from v$logfile;

User present in my database:

Select username from dba_users:

Desc v$parameter; name, type, value (display these columns and see)

Select name, type , value from v$parameter;

Select name, type , value from v$parameter where lower(name) like 'process%'; show the process.

The value of processes is 300. It means 300 users can be connected at one time to the database.

Show parameter spfile;

Alter system set processes=400 scope=spfile;

All the value written in dynamic view are taken from control files. And all the parameter values are written in parameter file. This command will change that value from 300 to 400. The scope spfile means that the change will be done in parameter spfile itself.

Alter system set processes=400 scope=memory;

We can also do that. This will not change the parameter value in the spfile itself but in the database memory. This means that the change will be in effect until the database keeps running. If the database restarts then in the time of restart the database will read the old value of the parameter written in the control file. So the change will be temporary.

Alter system set processes=400 scope=both;

We can also change them both at the same time.

We always change the parameter at the demand of client. Some parameter change will not affect immediately. Some change required database restart.

But in this case the scope memory will not work. This is why:

Select name, type, value, ISSYS_MODIFIABLE from v$parameter where lower(name) like 'process%';

There are 3 values for ISSYS_MODIFIABLE:

False means the process needs restart of database to get the effect of changes.

Immediate means the changes will be done immediately.

Deferred means it will not require a database re-start just the session restart (log out and log in) to get in effect.

Important: If someone comes to me and asks for a process change. Then first check the process to find whether it needs a database restart to go in effect. If it needs a restart go to management and asks for their permission first for a downtime. (the server will be shutdown then). If the permission is granted then only restart the database.

Alter system set processes=400 scope=spfile;

This change will only get affected after database restart.

Because to change the value of parameter processes the database needs a restart anyway. So even if the change scope is in memory it will not take effect without restart. And a change in memory scope is temporary which is gone the moment the database restarts. So in this case the scope memory is useless. Scope memory will only work with those parameters whose issys_modifiable value is immediate