topic Re: Creating a Second Oracle Instance in Operating System - HP-UX

Creating a Second Oracle Instance

Adrian Sobers2 — Mon, 22 Nov 2004 14:54:44 GMT

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?

Re: Creating a Second Oracle Instance

Sanjay_6 — Mon, 22 Nov 2004 14:56:46 GMT

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

Re: Creating a Second Oracle Instance

Patti Johnson — Mon, 22 Nov 2004 15:15:53 GMT

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

Re: Creating a Second Oracle Instance

Yogeeraj_1 — Tue, 23 Nov 2004 02:24:18 GMT

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

Re: Creating a Second Oracle Instance

twang — Tue, 23 Nov 2004 02:33:34 GMT

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.

Re: Creating a Second Oracle Instance

Jean-Luc Oudart — Tue, 23 Nov 2004 05:18:50 GMT

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

Re: Creating a Second Oracle Instance

Fred Ruffet — Tue, 23 Nov 2004 05:35:05 GMT

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

Re: Creating a Second Oracle Instance

Victor BERRIDGE — Tue, 23 Nov 2004 05:36:54 GMT

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

Good luck
Victor

Re: Creating a Second Oracle Instance

Tor-Arne Nostdal — Tue, 23 Nov 2004 05:37:07 GMT

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

Re: Creating a Second Oracle Instance

Adrian Sobers2 — Tue, 23 Nov 2004 10:05:33 GMT

thanks all