Creating a Second Oracle Instance

[Adrian Sobers2]

Hi,

How do I go about creating a second instance on the same server? I would like to have a test instance which ideally should be a mirror of the production instance on the same server. Possible? Recommended?

[Sanjay_6]

Hi,

It is not recommended to have the test & production on the same server. It is a no-no for everything, not just an oracle database.

Hope this helps.

regds

[Patti Johnson · Accepted solution]

If you don't have a dedicate test server, then a second instance on your production box is possible (I've got one environment setup that way). Just make sure you have sufficient resources (memory, disk, i/o) to support two instances.

There are several ways to create the second instance. Create a new instance from scratch by setting the ORACLE_SID to a new value and running the db create scripts. Or take your latest backup copy and create a clone of production. Depending on the size of your database cloning is usually faster than creating the db then importing the users/data. If you are using rman for backups then you can follow the rman procedure to duplicate the database. If not check out Metalink article 18070.1 that explains how to create a copy of the database on the same server.

Patti

[Yogeeraj_1]

hi,

have you considered creating another schema on the same database and use:
set current_schema
to simply change the default schema name.

this will work if your database is not HUGE!

To "copy" the objects from the "production" schema to the "test" schema, you would then just do an export/import...

regards
Yogeeraj

[twang]

few steps to do:
- cold backup you prod instance (suggested)
- on your prod:
alter database backup controlfile to trace;
(find the trace file in $ORACLE_BASE/admin/bdump
and rename it to cre_ctrl.sql
- restore the cold backup to new locations
- edit init.ora to reflect the new instance properties
- edit cre_ctrl.sql to reflect the new instance properties
- create a new env file for the new instance
- source to new env
- sqlplus /nolog
- connect / as sysdba
- startup nomount
- @cre_ctrl.sql
- alter database open


note:
1. some kernel parameters must be modified to allow 2 instances on same box, nproc, nfile, nflocks....
2. BACKUP of prod instance is must before any action on this.

[Jean-Luc Oudart]

Adrian,

It's possible.
to create a copy of existing database, cloning is fairly easy (cf. Twang post)
But as you will be on same server, the source instance has to be shut down while you clone the new one !

Is it recommended.
In most companies you (have to) separate production for test/dev/uat. Either you have different servers or partitions. Obviously, it goes back to your company policy.

Regards
Jean-Luc

[Fred Ruffet]

I am with Sanjay and is 0 points on this one !

It is possible but must not be done.

Testing can mean many things.
It can be going into consuming resources the production need. It will not reflect anything as long as production runs on the same machine so the results can be false.

If your production DB is well sized, reserving a second SGA in memory may get you into troubles, staring swapping, and in a worst case, can go into crashing your production.

Regards,

Fred

[Victor BERRIDGE]

Hi Adrian,
Here are some documentation you may read to help you in the task appointed...


Good luck
Victor

[Tor-Arne Nostdal]

1) Playing around with a test system on the same machine that runs production is definitely no good idea...

Arguments for two machines (get High availability ?):
We typically run Dev./Test on one machine and Prod on another.
The Test system is a copy of production which we refresh from time to time.
This give a good QA-system before putting into production. Since we use two servers we can (and do) setup failover.
If production server fail, the database will be started up on the D/T-machine.
Requirements: HW: 2 machines + SAN, SW: MCSG

2) Running two (or more instances) on one machine is possible.
We have a machine with 7 different small oracle db's

3) Consider if they should use same Oracle runtime or not ?
If they use separate copies of Oracle (not only DB) you will have the opportunity to upgrade each instance separately.

4) Resource allocation
Running 2 Oracle DB's on one machine:
Kernel parameters + initSID_1.ora + initSID_2.ora must match HW configuration and needs/wanted resource allocation.

/Tor-Arne

[Adrian Sobers2]

thanks all