Oracle AppsDBAs World: Creating database with create database command

Creating database with create database command
--------------------------------------------------------

Here's the steps to create database
using create database command on NT.

1. Setting the instance sid.
In the same machine there may be more than
one instance can run . So to identify the perticular instance
we are setting this sid. Using the below command,
go to command prompt and set

c:\oracle\product\10.1.0>set ORACLE_SID=PROD

2.Creating service.
On windows each instance require a window service.
This service we can create using keyword called oradim,
use the below command to create the service, go to command prompt

and execute,
c:\>oradim -new -sid PROD -intpwd MYSECRETPASSWORD -startmode M

It will create the services and password file also.
The file will be located under %ORACLE_HOME%\database
and that is used to connect / as sysdba.

3.Creating pfile.
Whenever an instance is starting up, its requires pfile(init.ora)

or spfile to start the database.
pfile is a text file. Changes in the pfile will take effect on
next startup, spfile is a binary file changes will take effect

across startup and shutdown.Here's minimal init.ora

c:\oracle\product\10.1.0\Db_1\database\initPROD.ora

(Default location:%ORACLE_HOME/dbs in unix or

%ORACLE_HOME%\database, if it is windows)

control_files = (c:\oracle\databases\ora10\control01.ora,
c:\oracle\databases\ora10\control02.ora,
c:\oracle\databases\ora10\control03.ora)
undo_management = auto
db_name = ora10
db_block_size = 8192
background_dump_dest=c:\oracle\databases\ora10\bdump
user_dump_dest=c:\oracle\databases\ora10\udump

4.Starting the instance.
we have created an Oracle service and the init.ora file.
So we can start the instance. Use the below command,

c:\oracle\product\10.1.0\Db_1>sqlplus /nolog

connect as sysdba to create database,
SQL> connect sys/MYSECRETPASSWORD as sysdba
Connected to an idle instance.

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

5.Creating the database

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
default temporary tablespace temp
tempfile 'D:\oracle\databases\ora10\temp.dbf'
size 10M;

If you got any error while creating database check alert log for more details. The alert log file location is specified in background_dump_dest. After creating the database it'll mounted and opened for user access.

After creating the database run the following scripts. Example, this script will call catexp.sql which is a requirement for exp, or dbmsstdx.sql which is a requirement to create triggers.

Location of these scripts:
# %ORACLE_HOME%/rdbms/admin/catalog.sql
# %ORACLE_HOME%/rdbms/admin/catproc.sql

So run these scripts finally,
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

posted by Senthil Kumaran @ 8:58 PM

0 Comments: