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, 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 11.2.0.4.0 - Production
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PL/SQL Release 11.2.0.4.0 - Production
SQL> connect / as sysdba
Connected.
SQL> startup nomount
ORACLE instance started.

Total System Global Area              245762048 bytes
Fixed Size                               2213928 bytes
Variable Size                         163577960 bytes
Database Buffers                       67108864 bytes
Redo Buffers                             716800 bytes

Adjustments Made:
  1. Version Numbers:
    • Updated SQL*Plus and PL/SQL versions to 11.2.0.4.0, reflecting the final patch set of Oracle 11g R2.
    • Updated the database release to 11.2.0.4.0.
  2. Dates:
    • Adjusted the copyright date to reflect the timeline of Oracle 11g R2 (up to 2013).
  3. Memory Values:
    • Updated memory allocation values to reflect more realistic numbers for Oracle 11g R2 environments.
  4. Command Updates:
    • Replaced connect internal with connect / as sysdba, as connect internal was deprecated in Oracle 9i and beyond.

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
    The listed statements about common problems when starting a new Oracle instance from SQL*Plus generally hold true for Oracle 12c, as well as for other versions of Oracle databases. Here's a detailed review:
    Statements and Oracle 12c Context
    1. Invalid Credentials: Correct. Oracle 12c requires valid credentials to connect, and by default, passwords are case-sensitive unless configured otherwise. The correct username/password and connection string are necessary.
    2. Database Not Found: Correct. You need to ensure that the database you're trying to connect to exists and is registered in the Oracle listener (for remote connections). Use tools like lsnrctl status to confirm database availability.
    3. Instance Not Running: Correct. Attempting to start an already running instance will result in an error such as "ORA-01081: cannot start already-running ORACLE - shut it down first." Check the instance's state with ps -ef | grep pmon or SQL*Plus SELECT status FROM v$instance;.
    4. Permissions Issue: Correct. In Oracle 12c, only users with the SYSDBA or SYSOPER roles can start or stop an instance. Attempting to start an instance without the required privileges results in an error.
    5. Invalid Environment Settings: Correct. Oracle 12c relies on properly configured environment variables (ORACLE_HOME, ORACLE_SID, and optionally PATH). Misconfiguration leads to errors when starting an instance.
    6. Corrupted Database Files: Correct. Corrupted files (e.g., control files, datafiles, or redo logs) prevent the instance from starting properly. Oracle 12c also includes tools like RMAN and DBV (Database Verification Utility) for integrity checks.
    7. Space Issue: Correct. Oracle 12c needs sufficient disk space, especially in the FRA (Flash Recovery Area) and for temporary segments. Lack of space can prevent instance startup or lead to further operational problems.
    8. Network Issues: Partially applicable for local instances but completely valid for remote connections. For remote connections, you must ensure the correct network configuration (tnsnames.ora or Easy Connect string) and that the listener is running (lsnrctl start).

    Specific to Oracle 12c
    Oracle 12c introduced the concept of Multitenant Architecture[1] with CDBs (Container Databases) and PDBs (Pluggable Databases). When working in Oracle 12c:
    • Starting a CDB is different from starting a PDB. A CDB must be in the open state for PDBs to be accessed.
    • Use SQL*Plus to start the container (STARTUP) and then open PDBs (ALTER PLUGGABLE DATABASE <PDB_NAME> OPEN;).
    These additional considerations may apply depending on the architecture you are using.
    Summary: The listed points are accurate for troubleshooting Oracle 12c instance startup issues. However, you should also account for Multitenant Architecture specifics if using CDB/PDB configurations.

    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, Oracle 11g 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 that looks like this: 
     SQL> startup nomount
LRM-00109: could not open parameter file
'C:\Oracle\product\11.2.0\dbhome_1\DATABASE\initcoin.ora'
ORA-01078: failure in processing system parameters
    The useful part about this error message is that Oracle 11g 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.ora 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 default locations, so that Oracle 11g can find them on its own.
    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.


    [1]Multitenant Architecture:In Oracle 12c, Multitenant Architecture introduces the concept of a Container Database (CDB) that can hold multiple Pluggable Databases (PDBs). This allows for efficient resource utilization and simplified management by consolidating many databases into one while maintaining isolation and security.
    SEMrush Software