New Oracle Instance

I wanted to have a separate area for storing résumé data so I needed to create a new database instance. I created a 2GB partition on one of my disks and planned to use it as a "raw" (no filesystem) data partition for the new instance (you'll see 2000M in one of the commands; this is where it comes from). Oracle doesn't make it particularly easy but a bit of document mining provides the information you require. Just in case anyone else needs to do something similar, here are the steps I took.

NOTES: My Oracle installation on Linux resides in /u/OraHome1 which is also mapped to the ORACLE_HOME environment variable. The new instance is called SUDS so replace all instances with whatever you want your new instance to be named. I'll show the prompts in bold and all typed commands will be displayed in a fixed-width font. When the prompt is the number sign (#) it means that you have to perform that step as the superuser. When the prompt is the dollar sign ($) then you should be the oracle user.

  1. Run the raw command on your raw partition to bind it to a device node in the /dev/raw directory. Just as a reminder to myself I first created a hard link from the first raw device to something called rawora.
    # ln /dev/raw/raw1 /dev/raw/rawora
    # raw /dev/raw/rawora /dev/hdc4
    
    Don't forget to add the second command above to the /etc/rc.d/rc.local file!
  2. Make the oracle user the owner of the device node.
    # chown oracle /dev/raw/rawora
    
  3. Create a new directory for containing the instance within the existing file structure.
    $ mkdir $ORACLE_HOME/admin/SUDS
    $ cd $ORACLE_HOME/admin/SUDS
    $ mkdir create exp pfile bdump cdump udump
    
  4. Copy the template file init.ora to the pfile subdirectory.
    $ cd pfile
    $ cp $ORACLE_HOME/dbs/init.ora initSUDS.ora
    
  5. On the line which starts db_name= replace the DEFAULT string with the instance name, SUDS in my case. I also changed the processes line to only fire up 10. Finally I modified the control_files line to read like this:
    control_files = (/u/OraHome1/oradata/SUDS/control01.ctl, /u/OraHome1/oradata/SUDS/control02.ctl)
    
    I tried to maintain the same structure as the instance created at Oracle installation time.
  6. Create the directory which is going to contain the database files, specified already as the path to the control files in the previous step.
    $ mkdir $ORACLE_HOME/oradata/SUDS
    
  7. Set the ORACLE_SID environment variable to the new instance name. (This is for the Bourne and Korn shells.)
    $ ORACLE_SID=SUDS
    $ export ORACLE_SID
    
  8. Fire up svrmgrl and perform database initialized by following these steps:
    $ svrmgrl
    
    Oracle Server Manager Release 3.1.7.0.0 - Production
    
    Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.
    
    Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.1 - Production
    
    SVRMGR> connect internal/oracle
    Connected.
    SVRMGR> CREATE DATABASE SUDS
         2> DATAFILE '/u/OraHome1/oradata/SUDS/system01.dbf' SIZE 50 M
         3> LOGFILE GROUP 1 (/'u/OraHome1/oradata/SUDS/redo01.dbf')  SIZE 1M,
         4> GROUP 2 ('/u/OraHome1/oradata/SUDS/redo02.dbf')  SIZE 1M;
    < lots of output not shown, same as the following commands >
    SVRMGR> @/u/OraHome1/rdbms/admin/catalog;
    SVRMGR> @/u/OraHome1/rdbms/admin/catproc;
    SVRMGR> @/u/OraHome1/rdbms/admin/catexp;
    SVRMGR> @/u/OraHome1/rdbms/admin/utlxplan;
    SVRMGR> @/u/OraHome1/rdbms/admin/catblock;
    SVRMGR> connect system/manager
    SVRMGR> @/u/OraHome1/sqlplus/admin/pupbld;
    SVRMGR> connect internal/oracle
    SVRMGR> CREATE TABLESPACE DATA DATAFILE '/dev/raw/rawora' SIZE 2000M;
    SVRMGR> CREATE ROLLBACK SEGMENT rbs01;
    SVRMGR> ALTER ROLLBACK SEGMENT rbs01 ONLINE;
    SVRMGR> CREATE ROLLBACK SEGMENT rbs02;
    SVRMGR> ALTER ROLLBACK SEGMENT rbs02 ONLINE;
    SVRMGR> @/u/OraHome1/rdbms/admin/catalog;
    SVRMGR> exit
    $ 
    
  9. Create a symbolic link from your new parameter file back into the base directory.
    $ cd $ORACLE_HOME/dbs
    $ ln -s $ORACLE_HOME/admin/SUDS/pfile/initSUDS.ora initSUDS.ora
    
  10. Add the private rollback segments to the paramter file, i.e. in the $ORACLE_HOME/dbs/initSUDS.ora file you need to uncomment the rollback_segments line and add the segment names. Based on the previous steps it should look like this:
    rollback_segments = (rbs01, rbs02)
    
  11. Create the password file for the instance.
    $ orapwd $ORACLE_HOME/dbs/orapwSUDS
    
  12. As root, modify the /etc/oratab file to include the new instance. In my case, the line looks like this:
    SUDS:/u/OraHome1:Y
  13. I was using the TNS listener to provide access to the existing instance so I just needed to add another service description to the end of the $ORACLE_HOME/network/admin/listener.ora file. It looks just like the ones above it, to wit:
        (SID_DESC =
          (GLOBAL_DBNAME = SUDS)
          (ORACLE_HOME = /u/OraHome1)
          (SID_NAME = SUDS)
        )
    
  14. In order to make sure that everything is working properly (and it's not as though I have to worry about inconveniencing other users) I just shut Oracle down and bring it back up. The easiest way to do this is to run the startup scripts like this:
    # cd /etc/rc.d/rc3.d
    # ./S99oracle stop
    # ./S99oracle start
    
    I usually use the script command at times like these. That way, if you've made any mistakes in configuration, you will have a log which should point you in the right direction.
  15. If everything has been set up correctly then it's time to reset the passwords for the system and sys users and add a new user. The system account always starts out with the password manager so you'll want to change it immediately.
    $ sqlplus
    
    SQL*Plus: Release 8.1.7.0.0 - Production on Tue Dec 3 14:53:49 2002
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    Enter user-name: system
    Enter password: password
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.1 - Production
    
    SQL> alter user system identified by password;
    
    User altered.
    SQL>
    alter user sys identified by password; User altered.
    SQL>
    create user pselby identified by password; User created.
    SQL >
    The password strings would be replaced by the appropriate values.
  16. I need to have this new user use the DATA tablespace as the default and also grant him a couple of privileges so he can do his job.
    SQL> alter user pselby default tablespace DATA;
    
    User altered.
    SQL>
    alter user pselby quota unlimited on DATA; User altered.
    SQL>
    grant create session to pselby; Grant succeeded.
    SQL>
    grant create table to pselby; Grant succeeded.
    SQL>
    exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production With the Partitioning option JServer Release 8.1.7.0.1 - Production $
  17. The only thing left is to transfer the tables from the original instance into the new one. I want to copy everything under my username and the import/export utilities make it easy. On the original instance (set the ORACLE_SID environment variable appropriately) I simply need to enter the following:
    $ exp -OWNER=PSELBY -FILE=/tmp/tables
    
    I get prompted for the username and password and everything gets exported to a file named /tmp/table.dmp (note the extension, in case you want to backup the file or save it somewhere). Change the ORACLE_SID to the new instance and run the following:
    $ imp -FILE=/tmp/tables
    
    A walk in the park!

Some of you might be wondering why I'm willing to share so many details of my installation. When it comes to certain things I can be quite circumspect. In this case, however, my firewall has been configured to deny access to port 1521 (the Oracle TNS listener). So even having my username isn't going to do anyone any good. Other protocols such as telnet, ssh and the r-commands (rlogin, rcp, etc.) are similarly restricted. My interface to the 'net is locked down tightly and I regularly run third-party port scans to ensure that I haven't left any doors open.

Finally, I think the more details you provide, the better the chance of success of those wanting to follow in my footsteps. Obviously these instructions are for Oracle 8.1.7 running under Linux; they won't be directly applicable to an Oracle 9i installation on Solaris. Then again, the basic concepts should be transferrable to a variety of platforms. I can claim that these were the steps I took and the operation was entirely successful. I wish you similar good fortune in your own endeavours.

Copyright © 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015 by Phil Selby. All rights reserved.