Manually Creating a Database in Oracle

Manually Database Creation is one of the most important works for DBA.

STEP 1: Create Required Directories
C:\>mkdir c:\oracle\oradata\mydb
C:\>mkdir c:\oracle\admin\mydb\bdump
C:\>mkdir c:\oracle\admin\mydb\udump
C:\>mkdir c:\oracle\admin\mydb\cdump

STEP 2: Create PASSWORDFILE for database authentication
C:\>ORAPWD file=c:\oracle\ora92\database\PWDmydb.ora password=oracle entries=5

STEP 3: Create INIT parameter file and set below parameter
open notepad or wordpad and type the following parameters and save it as initMYDB.ora in the ORACLE_HOME/database folder
eg:
c:\oracle\ora92\database\initMYDB.ora
init parameters
background_dump_dest=c:\oracle\admin\mydb\bdump
core_dump_dest=c:\oracle\admin\mydb\cdump
user_dump_dest=c:\oracle\admin\mydb\udump
control_files='c:\oracle\oradata\mydb\control01.ctl'
compatible=9.0.0
db_name=mydb
instance_name=mydb
remote_login_passwordfile=exclusive

STEP 4: Create Database Services
C:\>ORADIM -new -sid mydb -startmode auto
Connect with instance as SYSDBA
C:\>set oracle_sid=mydb
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 1 13:01:47 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

STEP 5: Startup the instance at NOMOUNT stage
SQL> startup nomount pfile=C:\oracle\ora92\database\initMYDB.ora
ORACLE instance started.
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

STEP 6: Issue the Create Database statement
SQL> @"C:\Documents and Settings\Administrator\Desktop\dbcreate.sql"
Database created.
dbcreate.sql script
create database mydb
logfile GROUP 1 ('c:\oracle\oradata\mydb\redo01.log') size 5m, GROUP 2 ('c:\oracle\oradata\mydb\redo02.log') size 5m
datafile
'c:\oracle\oradata\mydb\system01.dbf' size 200m
/
open the notepad and save this file as dbcreate.sql

STEP 7: Run Scripts to Build Data Dictionary views
SQL> @C:\oracle\ora92\rdbms\admin\catalog.sql
SQL> @C:\oracle\ora92\rdbms\admin\catproc.sql

STEP 8: Create Spfile from init.ora file.
SQL> create spfile from pfile='C:\oracle\ora92\database\initMYDB.ora';
File created.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
creating undo tablespace
SQL> create undo tablespace undotbs
2 datafile 'c:\oracle\oradata\mydb\undo01.dbf' size 100m;
Tablespace created.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> alter system set undo_tablespace=undotbs scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
creating temporary tablespace
SQL> create temporary tablespace temptbs 2 tempfile 'c:\oracle\oradata\mydb\temp01.dbf' size 100m;
Tablespace created.
SQL> alter database default temporary tablespace temptbs;
Database altered.
creating tablespace for users
SQL> create tablespace users
2 datafile 'c:\oracle\oradata\mydb\user01.dbf' size 100m;
Tablespace created.
changing database from noarchivelog mode to archivelog
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 61
Current log sequence 62
step 1:
SQL> shu immediate;
step 2:
SQL> startup mount
step 3:
SQL> alter database archivelog;
Database altered.
step 4:
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 61
Next log sequence to archive 62
Current log sequence 62
step 5:
SQL> alter system set log_archive_start=true scope=spfile;
System altered.
SQL> startup force

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 62
Next log sequence to archive 63
Current log sequence 63
I HOPE THIS DOCUMENTATION WILL HELPS YOU DO CREATE A DATABASE.
THANKS FOR READING THIS DOCUMENTATION.