Search This Blog

Saturday, November 11, 2006

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.

2 comments:

Anonymous said...

Hello TAJ,

Thanks for opening this blog,its really good for the starters.

The step for creating the database are there only on Windows environment,Can you please out the steps of creating and installing database and database software.

Regards

kaunain

Satish Jain Puglia said...

Dear Taj,
You have the great collection in your blog.This artical has helped to me twice.

Thanks