Adding Aditional Database 1

[dtrahokie]

I have an existing Database and want to create a second database. Is it as simple as loging in as the sys user and issuing a create database statement (obviously with all of the associated other commands).

Do I need to issue a command "user sys identified by password" to create a new sys and system user?

THanks in advance for all of your help.

 

[SantaMufasa]

Hokie,

Creating a second database involves these steps:

1) Identify the second instance to the operating system. On *nix, you add your new instance information to the file "/var/opt/oracle/oratab" (On Solaris) or "/etc/oratab" (on AIX and HP-UX), for example. On Windows, you run "oradim" to add a new service for the new instance.

2) Create an init<SID>.ora parameter file for the new instance in $ORACLE_HOME/database (for Windows) or $ORACLE_HOME/dbs (for *nix).

3) Populate ORACLE_HOME and ORACLE_SID system variables with the values you want for this session. On *nix, run ".oraenv", which reads your oratab file and allows you to specify which Oracle instance you wish to run. On Windows, run a script that sets your ORACLE_HOME and ORACLE_SID values.

4) Connect to Oracle. Run "sqlplus /nolog"..."connect / as sysdba". If you receive an "insufficient privileges" error, you are not logged into the operating system as a "privileged" user. To determine who is a privileged user, on Windows, navigate to your o/s-user management program to see the names of users that are members of the ORA_DBA group. On *nix, "cat $ORACLE_HOME/rdbms/lib/config.s"; the name that appears on the lines with ".ascii" is your Oracle DBA group. Your login must be a member of the Oracle DBA group to "connect / as sysdba" without a password.

5) Create the database. Once you are successfully connected as a fully authorised Oracle DBA (i.e., "connect / as sysdba"), you do the following:

**************************************************************************************
a) startup nomount pfile=<enter your init<SID>.ora here if not in a default path>
b) create database <db_name>
	controlfile reuse -- control file names automatically come from init<SID>.ora file
	logfile	group 1 ('<name of log group 1 member 1>',
			,'<name of log group 1 member 2>',) size <n>M reuse,
		group 2 ('<name of log group 2 member 1>',
			,'<name of log group 2 member 2>') size <n>M reuse,
		group 3 ('<name of log group 3 member 1>',
			,'<name of log group 3 member 2>',) size <n>M reuse
	datafile '<name of system tablespace file' size <n>M reuse
	character set <charset id>;

The above command creates and opens your database.

6. Create your UNDO/RBS tablespace. Create your tablespace for undo/rollback activity depending upon your preferences for rollback behaviour.

7. Cause your tablespaces to AUTOEXTEND. This is my preference rather than a requirement. If you create your SYSTEM and/or UNDO/RBS tablespaces, above, with a limited sizes, you will want to ensure growth ability:
"alter database datafile '<filename>' autoextend on next 10m maxsize 2000m;"

At this point, your database is open and fully functional. It is ready for you to add application tablespaces and users. (If you want ARCHIVE logging, you need to explicitly invoke it.)

Let us know if this answers your question about creating a second database on a system.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:11 (14Jul04) UTC (aka "GMT" and "Zulu"), 08:11 (14Jul04) Mountain Time)

 

[Turkbear]

Hi,
Just to be sure - you want to create an entirely new Oracle instance, not just add new objects to your existing one?
Many folks confuse Oracle's database ( the instance) with SqlServer's use of the same name for an entirely different object ( in Oracle the equivalent would be a schema which is a collection of objects owned by a user or the system).

 

[SantaMufasa]

Thanks, Turkbear, for your clarification. It is a VERY important issue to ensure that Hokie wants an entirely new and distinct Oracle database/instance, not just a new Oracle user/schema, which other database vendors refer to as a database.

So, Hokie, can you confirm what level of Oracle creation did you really want?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:47 (14Jul04) UTC (aka "GMT" and "Zulu"), 10:47 (14Jul04) Mountain Time)

 

[dtrahokie]

I'm trying to create a comletely new and distinct Oracle instance / database.

Thanks to boh of you for your help. When I followed Dave's instructions I issued the startup nomount command and got the error that the database was already started. I'm guessing that this occurred because there is already a running database on the system. Can I do this without taking that database offline?

Thanks.

 

[SantaMufasa]

Hokie,

You do not need to offline the other database. Your problem arose most likely by missing Step 3: Populate ORACLE_HOME and ORACLE_SID system variables, above. If you do not explicitly set your ORACLE_SID to the name of the new SID, sqlplus will attempt to connect you to the OLD instance.

Try that correction and let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:27 (14Jul04) UTC (aka "GMT" and "Zulu"), 11:27 (14Jul04) Mountain Time)