Why would one need multiple instances of same Oracle database

oracle

I came across following statement while reading this short article –

An instance is the software (and memory) that Oracle uses to
manipulate the data in the database. In order for the instance to be
able to manipulate that data, the instance must open the database. A
database can be opened (or mounted) by more than one instance,
however, an instance can open at most one database.

I am wondering, why would one create multiple instances of same database?

Best Answer

For availability (one instance goes down, another instance can continue to work), handling load between nodes (adding more nodes on as needed basis) and possibility to dynamically allocate resources based on needs. That is achieved by using Oracle RAC (Real Application Cluster) where instances are running on separate machines.

In Oracle for version up to and including 11.2, one database can be used by one instance (basic setup) or more instances (Oracle RAC).

It's expected to see paradigm shift for instance vs database with soon-to-be-released Oracle 12c (having more databases inside one instance).

Related Solutions

Oracle Databases – need to export nightly for backup/DR purposes

if your database is a serious production database, it should be running in archivelog mode, no question about that. This is assuming that users are constantly doing transactions and that you don't want to lose them. If your database is only bulk loaded once a day, an export dump might be a valid solution as backup, if you know how to use it to re-create a new database, once you lost the prod database.

For now, logon as a database administrator on your server and switch to archivelog mode

sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

database is open for production and you can make online backups. This does not mean your database is out of the danger zone, there are still a few things that need to be setup to keep it running without problems.

First thing that will happen is that the filesystem where your archivelog files are written will get filled up, causing a full halt of the database.

best advice: hire a dba to do the job for you, it is easier to make a disaster than to prevent, or even worse, to recover from one. Without the proper config recovery can be close to impossible. There are lots of consultancy firms that offer adhoc services via some pre-paid system. Maybe giving the application to a service provider might be smarter. This al depends on what you do and what kind of protection against disaster[s] you want.

second best: start reading the docs Oracle® Database 2 Day DBA

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created

Your kernel parameters need modifying.

Edit /etc/sysctl.conf and ensure the following lines are present:

kernel.shmall = 18350080
kernel.shmmax = 75161927680
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

Then reboot the machine.

Best Answer

Related Solutions

Oracle Databases – need to export nightly for backup/DR purposes

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created

Related Question