Oracle Database Manual Creation

Finally I create a database manually on my machine. This is no big thing but I consider it an achivement in itself. Below are the steps I followed to create my sample database.

1) Create the init.ora file: I copied the sample init.ora file provided by the Oracle installations which will be located at ORACLE_HOME\dbs folder. I modifed the parameters in the file and created the flash_recovery_area folder.
2) Connect to an idle Instance: I have a default database already installed on my machine when I initially installed Oracle. So I had to shut it down using the "shutdown immediate" command.(The shutdown command was somehow taking a very long time so I restarted the machine and shutdown the instance again.) Then I connected to the idle instance.
3)Start the instance: Start the instance using the below command
startup nomount pfile='c:\mynewdb\init.ora' <\code>
4) Create Database Command: Execute the create database command now as shown below.



CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY mynewdb
USER SYSTEM IDENTIFIED BY mynewdb
LOGFILE GROUP 1 ('c:\mynewdb\redo01.log') SIZE 100M,
GROUP 2 ('c:\mynewdb\redo02.log') SIZE 100M,
GROUP 3 ('c:\mynewdb\redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'c:\mynewdb\system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'c:\mynewdb\sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE 'c:\mynewdb\tbs_01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE 'c:\mynewdb\temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS
DATAFILE 'c:\mynewdb\UNDOTBS.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


I ran into the below issues which took quite some time for me to find and fix them.

a)For undo table space, in the init.ora I gave a different name for the undo tablespace and in the create database command I used a different one, because of which my create statement was failing.

b)After I fixed that issue I have still getting some error during the default tablespace creation and I found that in my create statement the default tablespace does not have a datafile associated with it. I added the highlighted line in my create database command and the command worked fine. I am not sure what the issue really is because I took that particular create statement from the Oracle Database Administrator's Guide. Well I am not too bothered about that cause my database is created now(Yay..)

5)TableSpace Creation: Now I created some more tablespaces using the below commands.


CREATE TABLESPACE users LOGGING
DATAFILE 'c:\mynewdb\users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE indx LOGGING
DATAFILE 'c:\mynewdb\indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;



6)Run Script to Create Data Dictionary Views: Finally I ran the catproc.sql and catalog.sql files located in the \rdbms\admin directories.
Well that completes my database creation manually. I am hoping that my original database installed previously also works fine.


I created a new user in the db and granted the create session and resource privileges. After that I try to connect to the DB but I get the below error

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

I connect as system and then run the pupbld.sql file from the sqlplus\admin directory. After that I am able to connect to the directory without any issues.


set ORACLE_SID = mynewdb