This is a collection of 15 FAQs for Oracle DBA on creating Oracle database instances manually using CREATE DATABASE statement. Items in this FAQ collection are organized together to form a complete tutorial guide on creating a new database instance manually. Topics included in this FAQ are:
1. How To Create an Oracle Database?
2. How To Create an Oracle Database Manually?
3. How To Select an Oracle System ID (SID)?
4. How To Establish Administrator Authentication to the Server?
5. How To Create an Initialization Parameter File?
6. How To Connect the Oracle Server as SYSDBA?
7. How To Create a Server Parameter File?
8. How To Start an Oracle Instance?
9. How To Start a Specific Oracle Instance?
10. How To Start Instance with a Minimal Initialization Parameter File?
11. How To Run CREATE DATABASE Statement?
12. How To Do Clean Up If CREATE DATABASE Failed?
13. How To Run CREATE DATABASE Statement Again?
14. How To Create Additional Tablespaces for an New Database?
15. How To Build Data Dictionary View an New Database?
Sample scripts presented in this FAQ are based Oracle 10g Express Edition (XE) for Windows. The script in Step 7 failed due some unknown reason. But it might work on Oracle 10g Standard Edition.
How To Create an Oracle Database?
There are two ways to create a new database:
• Use the Database Configuration Assistant (DBCA) to create a database interactively.
• Use the CREATE DATABASE statement to create a database manually.
How To Create an Oracle Database Manually?
Based on Oracle's Administrator Guide, there are 11 steps to create a database with the CREATE DATABASE statement:
• Step 1: Decide on Your Instance Identifier (SID)
• Step 2: Establish the Database Administrator Authentication Method
• Step 3: Create the Initialization Parameter File
• Step 4: Connect to the Instance
• Step 5: Create a Server Parameter File (Recommended)
• Step 6: Start the Instance
• Step 7: Issue the CREATE DATABASE Statement
• Step 8: Create Additional Tablespaces
• Step 9: Run Scripts to Build Data Dictionary Views
• Step 10: Run Scripts to Install Additional Options (Optional)
• Step 11: Back Up the Database.
Other items in this FAQ collection will follow those steps to help you creating a new database manually from beginning to end.
How To Select an Oracle System ID (SID)?
This is Step 1. If you are planning to create a new database, you need to select an Oracle System ID (SID). This ID will be used to identify the new Oracle database and its Oracle instance. SID must be unique if you want to run multiple databases on a single server.
Let's set SID for the new database to be: FYI.
How To Establish Administrator Authentication to the Server?
This is Step 2. There are two ways to establish administrator authentication to a new database.
• Use a password file.
• Use operating system (OS) authentication.
Using OS authentication is easier on Windows system. If you used your own Windows user account to install Oracle server, it will put your Windows user account into a special Window's user group called SYSDBA. This Window's user group will be fully trusted by Oracle server with SYSDBA privilege.
To continue with other steps, make sure you logged into the Windows system with a user account in the SYSDBA group.
How To Create an Initialization Parameter File?
This is Step 3. To run an Oracle database as an Oracle instance, you need to create an initialization parameter file, which contains a set of initialization parameters.
The easiest way to create an initialization parameter file to copy from the sample file provided by Oracle. You can do this in a command window as shown below:
>cd $ORACLE_HOME
>copy .\config\scripts\init.ora .\database\initFYI_ini.ora
>edit .\database\initFYI_ini.ora
(replace XE by FYI)
In this example, only the SID is changed from XE to FYI. All other parameters are maintained as is.
How To Connect the Oracle Server as SYSDBA?
This is Step 4. The best way to connect to the Oracle server as an administrator is to use SQL*Plus. You need to run SQL*Plus with /nolog option and use the CONNECT with blank user name, blank password and AS SYSDBA option. Here is a sample session:
>cd $ORACLE_HOME
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
How To Create a Server Parameter File?
This is Step 5. The initialization parameter file is good to get an Oracle database instance started. But it is not ideal run an instance as production. You need to convert the initialization parameter file into a Server Parameter File (SPFile) using the CREATE SPFILE statement. The script below shows you how do this:
SQL> CREATE SPFILE=$ORACLE_HOME/dbs/SPFILEFYI.ora
2 FROM PFILE=$ORACLE_HOME/database/initFYI_ini.ora;
File created.
Note that $ORACLE_HOME should be replaced by the real path name where your Oracle server is intalled.
The SPFile should be located in the expected directory and named as SPFILE($SID).ora.
How To Start an Oracle Instance?
This is Step 6. Now you are ready to start the new Oracle Instance without any database. This instance will be used to create a database. Starting an instance without database can be done by using STARTUP NOMOUNT statement as shown below:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M
The SHUTDOWN command is need to bring the default instance XE down.
The STARTUP NOMOUNT command failed because it tried to start the default instance XE, and there is a bad parameter in the XE instance SPFile.
See the next FAQ question to find another way to start the new instance FYI.
How To Start a Specific Oracle Instance?
A simple way to start a specific Oracle instance is to start the instance with the PFILE option as shown in the following example:
>.\bin\sqlplus /nolog
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini.ora
ORA-02778: Name given for the log directory is invalid
The PFILE option allows you to specify the initialization parameter file of a specific Oracle instance. But the initialization parameter file created in Step 3 has some problem with the log directory.
How To Start Instance with a Minimal Initialization Parameter File?
The sample initialization parameter file provided by Oracle seems to be not working. But we can try to start the new instance with a minimal initialization parameter file (PFile). First you can create another PFile, $ORACLE_HOME/database/initFYI_ini_min.ora, as shown below:
db_name=FYI
control_files=("\oraclexe\oradata\FYI\control.dbf")
undo_management=AUTO
Then start the FYI instance again:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> STARTUP NOMOUNT
PFILE=$ORACLE_HOME/database/initFYI_ini_min.ora
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 1286028 bytes
Variable Size 58720372 bytes
Database Buffers 50331648 bytes
Redo Buffers 2908160 bytes
How To Run CREATE DATABASE Statement?
This is Step 7. Oracle Administrator Guide provided a sample CREATE DATABASE statement. But it is a long statement. You can modify and same it in a file, $ORACLE_HOME/configscripts/create_database_fyi.sql, and run the file within SQL*Plus. Here is a copy of the modified CREATE DATABASE statement:
CREATE DATABASE FYI
USER SYS IDENTIFIED BY fyicenter
USER SYSTEM IDENTIFIED BY fyicenter
LOGFILE GROUP 1 ('/oraclexe/oradata/FYI/redo01.log') SIZE 10M,
GROUP 2 ('/oraclexe/oradata/FYI/redo02.log') SIZE 10M,
GROUP 3 ('/oraclexe/oradata/FYI/redo03.log') SIZE 10M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oraclexe/oradata/FYI/system01.dbf' SIZE 32M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oraclexe/oradata/FYI/sysaux01.dbf' SIZE 32M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/oraclexe/oradata/FYI/temp01.dbf'SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oraclexe/oradata/FYI/undotbs01.dbf'
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Here is how to run this long CREATE DATABASE statement:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: 'C:\ORACLEXE\ORADATA\FYI\CONTROL.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Don't worry about the error messages. They are caused by the missing directory for data files in file system.
How To Do Clean Up If CREATE DATABASE Failed?
To better organize data files, you should create a dedicated directory for each Oracle database. This can be done by using Windows file explorer to create the \oraclexe\oradata\fyi\ directory. Try the CREATE DATABASE statement again, when you have the directory ready.
If your CREATE DATABASE statement failed half way again, you may have to clean up the partial result of the CREATE DATABASE statement. Here is a list of suggestions for you:
• Run SHUTDOWN command to stop the partial started database instance.
• Remove all files in FYI directory: \oraclexe\oradata\fyi\
• Run STARTUP NOMOUNT PFILE command to start the empty instance again to be ready for CREATE DATABASE statement.
How To Run CREATE DATABASE Statement Again?
After cleaning up the results of a previously failed CREATE DATABASE statement, you can run the CREATE DATABASE statement again as shown below:
SQL> @$ORACLE_HOME\config\scripts\create_database_fyi.sql;
CREATE DATABASE FYI
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Something went wrong again. This time it might related the limitation that Oracle 10g XE only supports one database instance. With the default instance called "XE" already defined, creating another database instance might be not allowed.
How To Create Additional Tablespaces for an New Database?
This is Step 8. Creating additional tablespaces can be done by using the CREATE TABLESPACE statement as shown in the following sample script:
SQL> CREATE TABLESPACE users
2 DATAFILE '/oraclexe/oradata/FYI/users01.dbf' SIZE 10M;
SQL> CREATE TABLESPACE indx
2 DATAFILE '/oraclexe/oradata/FYI/indx01.dbf' SIZE 10M;
How To Build Data Dictionary View an New Database?
This is Step 9. The Oracle Administrator Guide suggests to run two SQL scripts provided by Oracle as shown bellow:
SQL> @/u01/oracle/rdbms/admin/catalog.sql
SQL> @/u01/oracle/rdbms/admin/catproc.sql
