Re: Help! How do I create a second database on the same machine?

Jeremy Cope <jeremy_at_intrahealth.co.nz> wrote in article
> I have an Oracle 7.3 database running on NT and I would like to create
> two environments e.g test and prod
>
> What is the best way to do this?
>
> Do I need to install Oracle again and create another instance and
> database?

Jeremy, if your system is anything like mine, you need a test environment that is the same as the prod but seperate. You therefore have the options: two seperate instances, OR two 'databases'. A new installation of Oracle is not needed for either.

As the previous answers in the news group have explained, creating a new instance is essentially copying the scripts used to create the original instance. All the relevent files initSID.ora etc should have the OLDSID replaced with NEWSID. Now edit these to reflect new control files etc. Do the same kind of thing with the database creation scripts. This gives you 2 seperate instances that can be maintained (incl. shutdown etc) individually. Access to the seperate instances is controlled by the value of ORACLE_SID variable before connection.

You do, however, have the option of creating multiple 'databases' in the one instance. E.G. Test users simply connect using a new and different default tablespace (and temporary TS if you need it). This is assuming that your current prod 'database' consists of one TS. Multiple TS's would require some extra thinking - security, PL/SQL etc.

I strongly suggest the 2 instance approach. Its allows easier management...

Hope it helps.
ciao

-- 
Guy van der Werf - guy_at_londonoffice.com or guy_at_sydneyoffice.com
Director, consultant, designer, developer and tea-boy at
Libran Systems Ltd, UK