BECOME AN ORACLE DBA: Managing the Oracle Instance? (Spfile's, Pfile's, Archivelog)

This topic will tell you how to manage multiple databases on Oracle.. We can create 'N' no.of databases on one server. You are already know how to create database manually and create using DBCA (database configuring Assistant). If you have any difficulty in creating or installing database and Oracle software, You can check my previous posts.

You already know that I have created Two databases named VIDYA and SAGAR. Further I Have changed one of my Database ID and name VIDYA to VEERA. If you have any doubts, check my previous posts.

Make a Note I have two databases in single server named VEERA and VIDYA

PFILE and SPFILE:

In order learn about PFILE and SPFILE, you need to learn what is database? Database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to resolving the problem of information management. For every Organization database is very important. As a DBA you need to check whether the database is functioning correctly or not and you need to check performance of database and security of database.

You all know There are three ways to create database

At the stage of Installation Oracle software into server
Create Using DBCA (DATABASE CONFIGURATION ASSISTANT) and
Manual database creation.

You can check my previous posts How create database using above steps.

At the time of database creation (1&2 types), PFILE and SPFILE are automatically created.

PFILE:

Pfile (parameter file) is text file which contains configuration of database. Means this parameter file tells to Oracle, where files are need to store and some memory information etc.. It is text file we can edit using Vi editor. we can configure structure of database.

SPFILE:

SPFILE (server parameter file) is binary file. we can't open this file using Vi editor. when we are starting oracle instance. Oracle will read this file first, if everything is perfect oracle instance will start and we are able to access oracle database

we can create SPFILE from pfile and we can create pfile from spfile

The default location of spfile is $ORACLE_HOME/dbs

The default location of pfile is $ORACLE_BASE/admin/(oracle_sid)/pfile

SPFILE to PFILE conversion and starting Oracle instance with pfile as well as with spfile:

3) First Let me tell you, how to start multiple oracle instance in single server

We have two databases in our server, those are VEERA and VIDYA

we need to set correct environments to start corresponding database. Now let me start VEERA

database;

Do following steps

$set ORACLE_SID=VEERA

$export ORACLE_SID

$. oraenv

ORACLE_SID= [VEERA] ? VEERA

$sqlplus / as sysdba

Start database using startup command and check which database that you are connected. Check below picture for better Idea

Issue following command

SQL> select name from v$database

You connected right database

Let's connect another database

get out of SQL and set environment to start second database as shown in figure

Now you know, how to start multiple databases in one server.

4) Next we will see, how to convert spfile to pfile and vise versa and start databases with pfile as well as

spfile

By default Oracle reads Spfile to start database. If Spfile is not there it will check for pfile and start the database. If both are not there we need to create pfile as we discussed before on manual database creation concept.

If Oracle find pfile, that should be fine, if not we need to give patch of pfile to start instance and database. or if you have pfile convert it to spfile and put into under $ORACLE_HOME/dbs directory.

Convert SPFILE to PFILE:

You no need to start database creating pfile and spfile. Check the below picture, how to convert

You have created pfiles for both databases. Check those files are created or not.

Remove spfile and try to start database.

5) Set Oracle environment and connect to database as sysdba. As I said before, by default Oracle database first, if spfile is not there, it goes to pfile automatically. Both are not exists. Your database is not operational

In previous step I created pfile from spfile and removed Spfile

If you see above picture I start oracle database without errors. If any error occur while starting database, start database using below command

SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/initSAGAR.ora';

database will start without errors

Once database is stated check to which database you are connect and issue the command as shown in figure. The marked box contain no value. So you have to confirm that the database is started with pfile.

If it contain path of spfile, you have to confirm that the database is start with spfile

6) Now Create spfile from pfile. Please check picture.

You Created spfile from pfile successfully then remove pfile and try to start the database.

If you check above picture. You started oracle database without any errors. If issue the command for check whether your database is stated with spfile or pfile.

In red box there is spfile path in value column. If you start database is spfile it shows path of spfile if you start with pfile if show NULL

You can practice same procedure for another database name VIDYA.

Archivelog:

An Oracle database can run in one of two modes. By default, the database is created in NOARCHIVELOG mode. When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Every time you have to shutdown database and make a backup. You are unable to use RMAN if you running your database in NOARCHIVELOG

If you want to work your production system efficiently, you have to have your database running in ARCHIVELOG mode.

Command for checking log mode of Oracle database.