Custom Search . . .

Saturday, April 21, 2007

Manually Create DataBase

DataBase creation there are two type's

1. Using Database configuration assistant. Start -> run -> type " Dbca"

2. manually crated database using Command based method.

Here i have used manually cteated.

1. Create folder in %ORACLE_BASE%/admin/

%ORACLE_BASE%\admin>mkdir iso
%ORACLE_BASE%\admin>cd iso
%ORACLE_BASE%\admin\iso>mkdir adump
%ORACLE_BASE%\admin\iso>mkdir bdump
%ORACLE_BASE%\admin\iso>mkdir cdump
%ORACLE_BASE%\admin\iso>mkdir dpdump
%ORACLE_BASE%\admin\iso>mkdir pfile
%ORACLE_BASE%\admin\iso>mkdir scripts
%ORACLE_BASE%\admin\iso>mkdir udump

Here "ISO" is a my database name.

2. set oracle enviorment variable


F:\>set oracle_sid=iso

3. create password file for iso database

F:\>orapwd file=F:\oracle\product\10.2.0\db_1\database\PWDiso.ora entries=5 password=oracle force=y

4. Create Oracle instance and start your new service

F:\>oradim -new -sid iso -startmode manual
Instance created.


F:\>oradim -edit -sid iso -startmode auto


F:\>sqlplus /nolog


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 21 16:44:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba

Enter password:
Connected to an idle instance.


5. create initialization parameter file


in %ORACLE_HOME%\database\initISO.ora


SQL> startup nomount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes


6. Here run your Database script


SQL> CREATE DATABASE "ISO"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 50
7 DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSTEM01.DBF' SIZE 300M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
8 SYSAUX DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSAUX01.DBF'
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE
UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'F:\oracle\product\10.2.0\oradata\iso\TEMP01.DBF' SIZE 50M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE
'F:\oracle\product\10.2.0\oradata\iso\UNDOTBS01.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET WE8MSWIN1252
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE GROUP 1 ('F:\oracle\product\10.2.0\oradata\iso\REDO01.LOG') SIZE
10240K,
14 GROUP 2 ('F:\oracle\product\10.2.0\oradata\iso\REDO02.LOG') SIZE
10240K,
15 GROUP 3 ('F:\oracle\product\10.2.0\oradata\iso\REDO03.LOG') SIZE
10240K
16 USER SYS IDENTIFIED BY "&SYSPWD"
17 USER SYSTEM IDENTIFIED BY "&SYSTEMPWD";
Enter value for syspwd: babu
old 16: USER SYS IDENTIFIED BY "&SYSPWD"
new 16: USER SYS IDENTIFIED BY "babu"
Enter value for systempwd: babu
old 17: USER SYSTEM IDENTIFIED BY "&SYSTEMPWD"
new 17: USER SYSTEM IDENTIFIED BY "babu"
Database created.


7. Create user tablespace


SQL> CREATE TABLESPACE "USER" LOGGING DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\USERS01.DBF' SIZE 10m REUSE AUTOEXTEND ON NEXT 1280k MAXSIZE
2* UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
3 /
Tablespace created.


SQL> ALTER DATABASE DEFAULT TABLESPACE "USER";
Database altered.

8. Execute the following script


@%ORACLE_HOME%\RDBMS\ADMIN\catalog.SQL
@%ORACLE_HOME%\RDBMS\ADMIN\catproc.sql
@%ORACLE_HOME%\SQLPLUS\ADMIN\pupbld.sql


9. Finally create Spfile using your Pfile


SQL> CREATE SPFILE FROM PFILE='F:\oracle\product\10.2.0\db_1\database\initISO.ora';
File created.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

2 comments:

Md said...

Thanks it really helpful.

Babu said...

your welcome