Manually Database Creation.

Manually Database Creation.

Based On Windows Enviourments
----------------------------------------

Follow below procedure.
-----------------------------

1. Create Script for database creation through "DBCA".( database configuration assistant)
start>run>dbca
or
start>program>oracle_home>configuration_and_migration>DATABASE CONFIGURATION ASSISTANT

2. In DBCA ( windows) select "custom" option in template. ( no datafiles option)
then next >>> next >>> and database creation option remove check mark on "create database" and select ( mark a check) on Generate Database Creation Scripts.

Note: Script generate in :"oracle-home/admin/scripts"

3. Create folder in below mention path.
1. Oracle-home/admin/1.udump...2.bdump...3.cdump
2. Oracle-home/oradata/2."folder name is some instance name" in my case instance name is "orcl1" then folder name is "orcl1".

mkdir D:\oracle\product\10.1.0\admin\orcl1\create
mkdir D:\oracle\product\10.1.0\admin\orcl1\pfile
mkdir D:\oracle\product\10.1.0\admin\orcl1\bdump
mkdir D:\oracle\product\10.1.0\admin\orcl1\udump
mkdir D:\oracle\product\10.1.0\admin\orcl1\cdump
mkdir D:\oracle\product\10.1.0\oradata\orcl1

4.Set oracle_sid enviourment variable or create password file.

C:\>set oracle_sid=orcl1
C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdorcl1.ora password=oracle entries=5 force=y

Note: Password file create only "database folder".

5.Create services & start for new database.

C:\>D:\oracle\product\10.1.0\db_2\bin\oradim.exe -new -sid ORCL1 -startmode manual -spfileInstance created.
C:\>D:\oracle\product\10.1.0\db_2\bin\oradim.exe -edit -sid ORCL1 -startmode auto -srvcstart system

or
C:\>oradim -new -sid ORCL1 -startmode manual -spfile
C:\>oradim -edit -sid ORCL1 -startmode auto -srvcstart
C:\>D:\oracle\product\10.1.0\db_2\bin\sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Sat Nov 11 17:51:44 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL>

6.Run Create database scripts

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDB.sql
Enter value for syspassword: oracleConnected to an idle instance.
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDB.log
SQL> startup nomount pfile="D:\oracle\product\10.1.0\admin\orcl1\scripts\init.ora";ORACLE instance started.
Total System Global Area
171966464 bytesFixed Size
787988 bytesVariable Size
145488364 bytesDatabase Buffers
25165824 bytesRedo Buffers
524288 bytes
SQL> CREATE DATABASE "orcl1"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\sysaux01.dbf' SIZE120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\oracle\product\10.1.0\oradata\orcl1\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
12 CHARACTER SET WE8MSWIN1252
13 NATIONAL CHARACTER SET AL16UTF16
14 LOGFILE GROUP 1 ('D:\oracle\product\10.1.0\oradata\orcl1\redo01.log') SIZE10240K,
15 GROUP 2 ('D:\oracle\product\10.1.0\oradata\orcl1\redo02.log') SIZE 10240K,
16 GROUP 3 ('D:\oracle\product\10.1.0\oradata\orcl1\redo03.log') SIZE 10240K
17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";Enter value for systempassword: oracleold
17: USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"new
17: USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"
Database created.
SQL> spool offSQL>

7. Run Datafile's scripts

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDBFiles.sql
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDBFiles.log
SQL> CREATE TABLESPACE "USERS" LOGGING DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";
Database altered.
SQL> spool offSQL>

8.Run Catalog Script of Database dictionary views.

SQL>@D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDBCatalog.sql
instead of you can only run below three scripts.
@D:\oracle\product\10.1.0\db_2\rdbms\admin\catalog.sql;
@D:\oracle\product\10.1.0\db_2\rdbms\admin\catproc.sql;
connect SYSTEM/&&systemPassword
@D:\oracle\product\10.1.0\db_2\sqlplus\admin\pupbld.sql;

above three scripts are parts of "CreateDBCatalog.sql" scripts.

9. Create last script for pfile creation

SQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\postDBCreation.sql
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.log
SQL> connect SYS/&&sysPassword as SYSDBAConnected.
SQL> set echo on
SQL> create spfile='D:\oracle\product\10.1.0\db_2\database\spfileorcl1.ora' FROM pfile='D:\oracle\product\10.1.0\admin\orcl1\scripts\init.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect SYS/&&sysPassword as SYSDBA
Connected to an idle instance.
SQL> startup ;
ORACLE instance started.
Total System Global Area
171966464 bytesFixed Size
787988 bytesVariable Size
145488364 bytesDatabase Buffers
25165824 bytesRedo Buffers
524288 bytes
Database mounted.
Database opened.
SQL> select 'utl_recomp_begin: ' to_char(sysdate, 'HH:MI:SS') from dual;
'UTL_RECOMP_BEGIN:'TO_CH--------------------------utl_recomp_begin: 06:22:46
1 row selected.
SQL> execute utl_recomp.recomp_serial();
PL/SQL procedure successfully completed.
SQL> select 'utl_recomp_end: ' to_char(sysdate, 'HH:MI:SS') from dual;
'UTL_RECOMP_END:'TO_CH------------------------utl_recomp_end: 06:22:48
1 row selected.
SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.log
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining options
C:\>



Hope this helps.