Scenario
OS: Microsoft Windows XP with Service Pack 3
Oracle Database: 10g (10.1.0.2.0)
Default installation folder: D:\oracle <= You may have your oracle installation in different path. Please change accordingly
Source: http://www.adp-gmbh.ch/ora/admin/creatingdbmanually.html
(I changed few things from the literature obtained through the above mentioned link and added my own observations with below provided solution(s))
This document may not be followed for production scenarios. The main audience for this document are beginners with Oracle database technology. The author WILL not take any responsibilities for loss of data or disasters if used in production environment. However the author confirms the entire exercises provided herein with were successfully tried in at least two individual computers.
The entire exercises below are executed from Windows command prompt (start->run->cmd)
Specifying the Instance’s SID
There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string. The SID can be set through the ORACLE_SID environment variable.
D:\oracle\product\10.1.0>set ORACLE_SID=ORA10 <=The new database you want to create
Creating an Oracle Service
On Windows, each instance requires a Windows service. This service must first be created with oradim:
D:\oracle\product\10.1.0\Db_1>oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode M
Instance created.
It can be verified that a Windows service was created by typing services.msc into the console. A service named OracleServiceORA10 (ORA10 = %ORACLE_SID%) will be found. Also, the startup type is manual as was requested by -startmode M. Oracle also created a password file under %ORACLE_HOME%\database:
D:\oracle\product\10.1.0\Db_1>dir database
Volume in drive D has no label.
Volume Serial Number is C4E9-469A
Directory of D:\oracle\product\10.1.0\Db_1\database
03/05/2005 03:54 PM <DIR> .
03/05/2005 03:54 PM <DIR> ..
03/05/2005 11:16 AM <DIR> archive
03/05/2005 11:13 AM 31,744 oradba.exe
03/05/2005 03:54 PM 2,560 PWDORA10.ORA
As can be seen, the SID is in the password file’s name.
Creating the initialization parameter file
When an Oracle instance starts up, it requires either an initialization paramter file (init.ora) or an SPFILE. SPFILES have binary content and must be created from init.ora files. Therefore, the init.ora file (which is an ordianary text file) is created first. Here’s a minimal init.ora (under $ORACLE_HOME/dbs if it is Unix, or %ORACLE_HOME%\database, if it is windows) just to demonstrate how the control files are found. Of course, you will add more init params into the init.ora file.
Browse to the folder D:\oracle\product\10.1.0\Db_1\database and create a new text file. Paste the following inside the newly created text file (text in green).
control_files = (d:\oracle\databases\ora10\control01.ora,
d:\oracle\databases\ora10\control02.ora,
d:\oracle\databases\ora10\control03.ora)
undo_management = auto
db_name = ora10
db_block_size = 8192
(This is just basic requirements for initialization parameter file)
Rename the newly created text file to “initORA10.ora” (make sure you have selected to show the common extensions from “folder options”)
The undo_management parameter is necessary if we want to use automatic undo management. Although the above seems to be the bare required minimum, you probably also want do define background_dump_dest, core_dump_dest and user_dump_dest.
Starting the instance
Now, that we have created an Oracle service and the init.ora file, we’re ready to start the instance:
D:\oracle\product\10.1.0\Db_1>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 – Production on Sat Mar 5 16:05:15 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> connect sys/MYSECRETPASSWORD as sysdba
Connected to an idle instance.
SQL*Plus tells us that we’re connected to an idle instance. That means that it is not yet started. So, let’s start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 787708 bytes
Variable Size 61864708 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
This created the SGA (System Global Area) and the background processes.
Creating the database
We’re now ready to finally create the database:
You MUST create ‘D:\oracle\databases\ora10\’ folder manually before issuing the “create database” command. Browse to D:\oracle folder and right click to create “databases” and under “databases” you need to create the folder “ORA10”. If you ignore or fail to manually create these folders and sub folders be sure the create database command will fail miserably.
SQL>create database ora10
logfile group 1 (’D:\oracle\databases\ora10\redo1.log’) size 10M,
group 2 (’D:\oracle\databases\ora10\redo2.log’) size 10M,
group 3 (’D:\oracle\databases\ora10\redo3.log’) size 10M
character set WE8ISO8859P1
national character set utf8
datafile ‘D:\oracle\databases\ora10\system.dbf’
size 50M
autoextend on
next 10M maxsize unlimited
extent management local
sysaux datafile ‘D:\oracle\databases\ora10\sysaux.dbf’
size 10M
autoextend on
next 10M
maxsize unlimited
undo tablespace undo
datafile ‘D:\oracle\databases\ora10\undo.dbf’
size 10M
autoextend on
next 10M
maxsize unlimited
default temporary tablespace temp
tempfile ‘D:\oracle\databases\ora10\temp.dbf’
size 10M
autoextend on
next 10M
maxsize unlimited;
If something goes wrong with the creation, Oracle will write an error into the alert.log. The alert log is normaly found in the directory that is specified with the background_dump_dest. If this parameter was not specified (as is the case in our minimal init.ora), the alert.log will be written into %ORACLE_HOME%/RDMBS/trace. If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows). If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file ‘/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora’ error is issued. The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file (which seems to default to sql.bsq) After the creation of the database, it can be mounted and opened for use.
Completing the DB creation
In order to complete the db creation, the following scripts must be run as sys:
%ORACLE_HOME%/rdbms/admin/catalog.sql
%ORACLE_HOME%/rdbms/admin/catproc.sql and
SQL*Plus provides a shortcut to refer to the ORACLE_HOME directory: the question mark (?). Therefore, these scripts can be called like so:
SQL> @?/rdbms/admin/catalog.sql <= If you have multiple oracle products installed, like developer suits, database in the same machine, “?” may not point to your database installation path. Hence you could call the script like following
SQL>@D:\oracle\product\10.1.0\Db_1\rdbms\admin\catalog.sql
SQL> @?/rdbms/admin/catproc.sql <= If you have multiple oracle products installed, like developer suits, database in the same machine, “?” may not point to your database installation path. Hence you could call the script like following
SQL>@D:\oracle\product\10.1.0\Db_1\rdbms\admin\catproc.sql
Explanations for the scripts catalog.sql and catproc.sql
catalog.sql creates the data dictionary. catproc.sql creates all structures required for PL/SQL. catalog.sql calls, for example, catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers.
While executing “catproc.sql” you may notice that many errors would be thrown on the screen and it must be basically because of the limited initORA10.ora parameter files (I am not a database guy, hence I cannot authenticate my claim)
However you will see the successfully completed messages by the end of the execution of “catproc.sql”. You need to worry only if the “catproc.sql” terminates with errors.
The user system might also want to run ?/sqlplus/admin/pupbld.sql. pupbld.sql creates a table that allows to block someone from using sql plus.
SQL> connect system/manager
SQL> @?/sqlplus/admin/pupbld
Of course, tablespaces, users, tables and so on must be created according to the use of the database.
Setting up database to using java
Also call @?/javavm/install/initjvm if you want to enable the JServer option(?).
Oracle managed files
Refer also to DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST for Oracle-managed files.
Errors while creating database
If there is an error while the database is created, such as a ORA-01092: ORACLE instance terminated. Disconnection forced, the alert log should be consulted. This file most probably contains a more desriptive error message. If the error occurs at a very early stage, there won’t be an alert.log. In this case, the error will most probably be found in a trace file in udump directory.
The original document from the link mentioned on the top of this document is limited to creating a database and there is hardly any explanations which will guide a beginner to start the newly created database or access it through different oracle products (developer suit, SQL*Plus etc)
There are multiple methods to start the new database. I will stick to the easiest, one time settings so that you can forget about “forgetting” the commands and the order of issuing them in order to start your databases at later stages.
Stop the OracleServiceORA10 before you change the registry values. (eg: start->cmd->net stop OracleServiceORA10 or go to services console and stop the service)
Start regedit and browse to this particular registry key “HKEY_LOCAL_MACHINE\ SOFTTWARE\ORACLE\ KEY_OraDb10g_home1”
On your right hand side pan, you will see fresh entries for your newly created database “ORA10”
Locate the following entry “ORA_ORA10_AUTOSTART” which must have the value “FALSE”. Change this value to “TRUE”
For additional information please refer to http://www.dba-oracle.com/oracle9iAS_tips_windows_service.htm
Exit the registry editor and go to command prompt and issue the command “net start OracleServiceORA10” or go to services control panel and start the Oracle service for ORA10.
Make sure the listener service is up and running.
How to check whether the listener is up and running
Go to command prompt and issue the following command
Lsnrctl status
If any warnings or errors are shown
Issue the following command
Lsnrctl start
Now confirm you can connect to your new database
From command prompt issue “sqlplus /nolog”
Once you are provided with “SQL>” prompt
Issue the command “conn sys/mypassword@ora10 as sysdba”
If the database and listener services are up already, you must get connected to the new database immediately.
You need to add your newly created database details with TNSNAMES.ORA file in order to connect from different products.
Hope this document is useful to beginners & “somewhat” professionals