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 (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 (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
Do following steps
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
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.
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.
Issue following command to change system to NOARCHIVELOG to ARCHIVELOG
7) check archive log list
a) Redbox Indicates mode of Archivelog
b) Greenbox indicates Mode of current archivelog
c) Yellowbox indicates Desticantion to save archivelog files
8) so we need to change Destination of archivelog Default location to another location
First make a directory called ARCHIVELOG. Under $ORACLE_HOME/dbs directory
Please check picture how to change location of archivelog
9) Check if Archivelog is set properly or not.
So now we have enabled archivelog mode and have some archivelog file also.