Re: creating database instance

In article <8gmm9p$n90$1_at_nnrp1.deja.com>,   christopher.lewis_at_ac.com wrote:
> Hi there, I'm fairly new to this so thanks for bearing with me.
 Anyway,
> I'm trying to create a new additional database instance (msd2) using
> Oracle 8.1.5 on a Sun SPARC server. My script begins as follows:
>
> connect internal
> startup nomount pfile=.......
>
> CREATE DATABASE msd2
> CONTROLFILE REUSE
> LOGFILE .......
>
> However, I get the following error message:
>
> Connected.
> ORA-01081: cannot start already-running ORACLE - shut it down first
> CREATE DATABASE msd2
> *
> Error at line 1:
> ORA-01501: CREATE DATABASE failed
> ORA-01100: database already mounted
>
> Is this just a matter of switching the ORACLE_SID? And if so, how can
 I
> set it if the database hasn't been created yet?
>
> Thanks for the help.
>

The ORACLE_SID is used to specify a particular instance to connect to. An instance is a set of processes (pmon, smon, dbw0, etc.) and memory structures (SGA). An instance is not the same thing as a database, which consists of control files, redo log files, and data files. In order to create a new database, a new instance must first be started. If you already have an instance running and a database created (e.g. msd1), then to create a msd2 database, you will need to change ORACLE_SID to something other than msd1.

The differentiation between an instance (ORACLE_SID in env) and database (db_name in init.ora) is subtle in a non-Parallel Server environment because, since it's a one-to-one relationship, they are usually named the same thing. In OPS, however, there is one database, but multiple instances (processes, memory structures, etc) accessing that single set of control files, redo log files, and data files that comprise the database. With OPS, you may have a db_name=PROD and say, 4 instances (typically on 4 separate machines) PROD1, PROD2, PROD3, PROD4.

Just to show you ORACLE_SID do not necessarily relate to the db_name, set your ORACLE_SID to msd1 and startup an instance and open the msd1 database. Then shutdown the database and open the msd2 database using the same instance.

$ ORACLE_SID=msd1; export ORACLE_SID
$ sqlplus '/ as sysdba'

SQL> startup pfile=initmsd1.ora
SQL> select name from v$database;
SQL> shutdown
SQL> startup pfile=initmsd2.ora
SQL> select name from v$database;
SQL> shutdown

Even though the instance name stayed the same, two different databases were mounted with it (one at a time).

Hope this helps.

Doug.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat May 27 2000 - 00:00:00 CDT