Create Database   «Prev  Next»

Lesson 4 Starting an instance
Objective Start the new COIN instance.

Starting Oracle Instance

Remember that an instance refers to the
  1. software processes and
  2. associated memory structures
, and that the term database refers to the physical database files. It follows, then, that before you can create a new database, you must first start the software. You can do this from the command prompt. If you are using Windows, go ahead and open a command prompt window now. The first thing you need to do is indicate which instance you want to work with. Under Windows NT, this is done using the SET command to set the value of an environment variable named ORACLE_SID to the name of the instance. For example: 
SET ORACLE_SID = COIN

On a UNIX system, the procedure is much the same. You set an environment variable, and then export it. These commands should work on most UNIX systems: 
ORACLE_SID=COIN

export ORACLE_SID

Starting an Instance with the NOMOUNT option

The next step is to start SQL*Plus, connect to the instance that you want to start, and then start that instance. Use the SQL*Plus STARTUP command to do this. An important caveat is that you must start the instance using the NOMOUNT option. NOMOUNT tells SQL*Plus to start the instance, but not to mount or open the database. That is important because mounting the database implies opening the control file. Since you have not created the database yet, there are no files to open. The following example shows this entire process being executed under Windows OS: 
C:\Oracle\ADMIN\COIN\create>set oracle_sid=coin
C:\Oracle\ADMIN\COIN\create>SQL
Oracle SQL*Plus Release 3.1.5.0.0 - Production
(c) Copyright 1997, Oracle Corporation.
Oracle8i Enterprise Edition Release 8.1.5.0.0
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area              34451404 bytes
Fixed Size                               65484 bytes
Variable Size                         17469440 bytes
Database Buffers                      16384000 bytes
Redo Buffers                            532480 bytes

The process is the same on UNIX. The only difference is that with UNIX, you use the export command, not SET, to set the value of the ORACLE_SID environment variable. Give this a try now. Set ORACLE_SID=COIN, start SQL*Plus, and issue a STARTUP NOMOUNT command. If you have done everything right so far, the COIN instance should start. Once you've started it, issue the SHUTDOWN command to shut it back down again.
  • Some Common Problems when you first try to start a new Oracle instance from SQL*Plus
    1. Invalid Credentials:Ensure that you are using the correct username and password to connect to the database. Verify that the password is not case-sensitive and that you are specifying the correct connection string.
    2. Database Not Found:Confirm that the database you are trying to connect to actually exists. Check the name of the database and make sure it is available before attempting to start the instance.
    3. Instance Not Running:Verify that the instance you are trying to start is not already running. Attempting to start an already running instance can result in errors.
    4. Permissions Issue:Ensure that the user you are using has the necessary permissions to start the instance. The user should be granted the `START INSTANCE` privilege or be a member of the `SYSDBA` role.
    5. Invalid Environment Settings:Confirm that the required environment variables for Oracle are properly set. Specifically, check the `ORACLE_HOME` and `ORACLE_SID` variables to ensure they are correctly configured.
    6. Corrupted Database Files:If the database files have been corrupted, you might encounter issues when trying to start the instance. Check the integrity of the data files and ensure that they are not damaged or corrupted.
    7. Space Issue:Make sure that there is enough disk space available for the instance to start and operate properly. Insufficient disk space can lead to errors during instance startup.
    8. Network Issues:Verify that the network configuration is correct, especially if you are connecting to a remote database. Ensure that the host and port numbers are specified correctly, and that there is network connectivity between the client and the database server.
    • Case-sensitivity issues Unix:
    Case-sensitivity is a problem that UNIX users run into all the time. UNIX is a case-sensitive operating system. When it comes to starting an instance, the case of the SID matters. If your SID is COIN, then your init file should be initCOIN.ora, and you should set ORACLE_SID=COIN. The directory name, under the Oracle admin directory, should also be COIN. Remember that the environment variable is case sensitive as well. Use ORACLE_SID, not oracle_sid.

Forgetting NOMOUNT option

If you forget to include NOMOUNT on the STARTUP command line, Oracle8 will try to open the database control files. You will see an error like this: 
ORA-00205: error in identifying controlfile, 
check alert log for more info

When this happens, Oracle8 will have actually started the instance. You could probably ignore the error and go on to create your database. However, to be conservative, I would recommend running a SHUTDOWN command followed by the correct form of the STARTUP command: STARTUP NOMOUNT.

Forgetting to CONNECT INTERNAL command

Sometimes people forget to issue the CONNECT INTERNAL command before they try to start an instance.
I do this sometimes, especially when I am in a rush. This is the error that you are likely to see: 
SQL> startup nomount

ORA-01031: insufficient privileges

If this happens to you, just run the CONNECT INTERNAL command, and then try the STARTUP command again.

Forgetting ORADIM

This problem will only plague Windows OS users. If you forgot to run ORADIM, or if you did not remember to issue a 
SET ORACLE_SID=COIN command,

you may receive a TNS protocol adapter error when you start SQL*Plus. It will look like this: 
C:\Oracle\ADMIN\COIN\pfile>SQL
Oracle SQL*Plus Release 3.1.5.0.0 - Production
(c) Copyright 1997, Oracle Corporation.
ORA-12560: TNS:protocol adapter error
SQL>

You may also receive an unexpected password prompt. Either way, check to be sure that you have correctly set the ORACLE_SID environment variable, and then check the Services control panel to be sure that a service named OracleServiceCOIN exists. If it doesn't, you should back up one lesson and run the ORADIM command to create it.

Cant find initialization file

Oracle expects to find the initialization file in a specific directory. On Windows, Oracle looks in the DATABASE directory underneath Oracle Home. On UNIX, Oracle8 looks in the dbs directory under Oracle Home. If the software doesn't find the initialization file where it expects it to be, you will get an error message looking like this: 
SVRMGR> startup nomount
LRM-00109: could not open parameter file
'C:\Oracle\Ora81\DATABASE\INITcoin.ora'
ORA-01078: failure in processing system parameters

The useful part about this error message is that Oracle8 tells you exactly which file it is trying to find and where it is looking. One solution is to move your initialization file to that directory and name it accordingly. Another solution is to use the PFILE option with the STARTUP command. Then you can explicitly point to your initialization file. For example: 
STARTUP PFILE=c:\coin\initcoin.txt NOMOUNT

I try to avoid using the PFILE option because it's just one more thing that I have to remember. In an environment with multiple DBAs and multiple databases, it can quickly become a struggle for everyone to remember where the initialization files are for each database. It's easier to just place them in the normal locations, so that Oracle8 can find them on its own.
SEMrush Software