Manual Database Creation in Oracle10g in Windows

Manual Database Creation steps:=>

1. Create Database folder in Oradata location:=>

   

    D:\oracle\product\10.2.0\oradata\axis\

   Name of database :=>axis

   Subfolder :=> datafile, controlfile, redolog, bdump, udump

 

Create pfile (initAXIS.ora) manually and save in location:=> ORACLE_HOME\database\

(D:\oracle\product\10.2.0\db_1\database) in windows

Add/Modify following parameter to initAXIS.ora file :=>

instance_name=axis

db_name =axis

db_unique_name = axis

control_files=("D:\oracle\product\10.2.0\oradata\axis\controlfile\control01.ctl","D:\oracle\product\10.2.0\oradata\axis\controlfile\control02.ctl","D:\oracle\product\10.2.0\oradata\axis\controlfile\control03.ctl")

sga_max_size =512M

sga_target =512M

shared_pool_size =120M

db_cache_size = 130M

pga_aggregate_target =130M

Compatible=10.2.0.1.0

undo_management=auto

undo_tablespace=undotbs

background_dump_dest='D:\oracle\product\10.2.0\oradata\axis\bdump'

user_dump_dest='D:\oracle\product\10.2.0\oradata\axis\udump'

2. Create Database instance :=>

D:\>oradim -NEW -SID AXIS -SYSPWD admin

Instance created.

D:\>set oracle_Sid=axis

D:\>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 27 09:57:44 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size                 1250044 bytes

Variable Size             134221060 bytes

Database Buffers       394264576 bytes

Redo Buffers              7135232 bytes

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------

Db_name                              string      axis

3.Create spfile using pfile

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------

spfile                               string

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size                  1250044 bytes

Variable Size             134221060 bytes

Database Buffers          394264576 bytes

Redo Buffers                7135232 bytes

3. a. Show Parameter pfile to confirm database is started using spfile

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------

spfile                               string      D:\ORACLE\PRODUCT\10.2.0

                                                 DATABASE\SPFILEAXIS.ORA

SQL> ALTER SYSTEM SET sessions=200 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET processes=250 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET job_queue_processes=10 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size =10G scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area' SCOPE=SPFILE;

System altered.

SQL>CREATE PFILE=’D:\oracle\product\10.2.0\db_1\database\pfile_bkp\initAXIS.ora’ FROM SPFILE;

SQL>shutdown immediate

d:>orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDAXIS.ora entries=5 password=admin

4.Run this script in nomount stage to create database

create database AXIS

user sys identified by admin

user system identified by admin

MAXINSTANCES 8

MAXLOGMEMBERS 4

MAXLOGFILES 32

MAXLOGHISTORY 100

character set WE8ISO8859P1

national character set AL16UTF16

logfile

group 1 ('D:\oracle\product\10.2.0\oradata\axis\redolog\redo01a.log','D:\oracle\product\10.2.0\oradata\axis\redolog\redo01b.log') size 50m reuse,

group 2 ('D:\oracle\product\10.2.0\oradata\axis\redolog\redo02a.log','D:\oracle\product\10.2.0\oradata\axis\redolog\redo02b.log') size 50m reuse,

group 3 ('D:\oracle\product\10.2.0\oradata\axis\redolog\redo03a.log','D:\oracle\product\10.2.0\oradata\axis\redolog\redo03b.log') size 50m reuse

datafile 'D:\oracle\product\10.2.0\oradata\axis\datafile\system01.dbf' size 500M reuse autoextend on next 1m maxsize unlimited extent management local

sysaux datafile 'D:\oracle\product\10.2.0\oradata\axis\datafile\sysaux01.dbf' size 500M reuse autoextend on next 1m maxsize 1024M

default tablespace users datafile 'D:\oracle\product\10.2.0\oradata\axis\datafile\users01.dbf' size 256m reuse autoextend on next 1m maxsize 1024M

default temporary tablespace temp tempfile 'D:\oracle\product\10.2.0\oradata\axis\datafile\temp01.dbf' size 256M reuse autoextend on next 1m maxsize 1024M

undo tablespace undotbs datafile 'D:\oracle\product\10.2.0\oradata\axis\datafile\undotbs01.dbf' size 256M reuse autoextend on next 1m maxsize 1024M

/

5.After database is created execute following scripts as sys

C:>ORACLE_HOME/rdbms/admin/catalog.sql

C:>ORACLE_HOME/rdbms/admin/catproc.sql

C:>ORACLE_HOME/rdbms/admin/dbmspool.sql

 To Install dbcontrol manually

D:> emca -config dbcontrol db -repos create

D:> emctl start dbconsole

Create user Scott for testing in later exercises

SQL> GRANT CREATE SESSION TO scott IDENTIFIED BY tiger;

Make database in ARCHIVELOG mode and turn FLASHBACK database on;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE FLASHBACK ON;