Manual Database creation with command line in 11gr2

We can create the database manually via two method

1. DBCA  Or 2. Command line

First method is quite simple and just few clicks but second method is also quick with few commands.

Step:

1. Create directories for the ORADATA / DIAG folder (bdump/alert logfile/trace file etc)

2. Create init parameter file with the minimum below parameters

db_name=taj

control_files = 'D:\app\Administrator\oradata\taj\control01.ctl','D:\app\Administrator\oradata\taj\control02.ctl'

diagnostic_dest= d:\app\administrator

NOTE: replace db-name , controlfile file location and diag parameter location as per your environment.

3. create oracle service on windows via ORADIM utility

ORADIM -NEW -SID taj -STARTMODE manual

4. create password file via ORAPWD utility

ORAPWD file=%ORACLE_HOME%/dbs/PWDTAJ.ora password=oracle

5. startup database in nomount mode with pfile

sqlplus sys/oracle as sysdba

startup nomount pfile='c:\init_taj.ora';

6. execute below create database script

CREATE DATABASE taj

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

MAXLOGFILES 5

MAXDATAFILES 100

DATAFILE 'D:\app\Administrator\oradata\taj\system01.dbf' SIZE 325M REUSE

AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

UNDO TABLESPACE "UNDOTBS" DATAFILE 'D:\app\Administrator\oradata\taj\undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET WE8MSWIN1252

logfile 'D:\app\Administrator\oradata\taj\redo01.log' size 100M reuse,

        'D:\app\Administrator\oradata\taj\redo02.log' size 100M reuse,

        'D:\app\Administrator\oradata\taj\redo03.log' size 100M reuse

EXTENT MANAGEMENT LOCAL;

7. run the following script to create needful packages

@%ORACLE_HOME%/rdbms/admin/catalog.sql@%ORACLE_HOME%/rdbms/admin/catproc.sqlNOTE: connect with SYSDBA user and execute.

@%ORACLE_HOME%/sqlplus/admin/pupbld.sql

NOTE: connect with SYSTEM user and execute above pupbld.sql script.

8. enable archive log mode

shutdown immediate

startup mount

alter database archivelog;

alter database open;

9. take full database backup (via RMAN/USER MANAGED as per your choice)

SQL> host rman target sys

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 6 16:45:52 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

target database Password:

connected to target database: TAJ (DBID=3195814920)

RMAN> backup database plus archivelog;

Starting backup at 06-SEP-11

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=117 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=1 STAMP=761156598

input archived log thread=1 sequence=2 RECID=2 STAMP=761156813

input archived log thread=1 sequence=3 RECID=3 STAMP=761156903

input archived log thread=1 sequence=4 RECID=4 STAMP=761157108

input archived log thread=1 sequence=5 RECID=5 STAMP=761157311

input archived log thread=1 sequence=6 RECID=6 STAMP=761157508

input archived log thread=1 sequence=7 RECID=7 STAMP=761157965

channel ORA_DISK_1: starting piece 1 at 06-SEP-11

channel ORA_DISK_1: finished piece 1 at 06-SEP-11

piece handle=D:\APP\ADMINISTRATOR\11.2.0\DB\DATABASE\01MLSMAF_1_1 tag=TAG2011090

6T164606 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 06-SEP-11

Starting backup at 06-SEP-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\TAJ\SYSTEM01.

DBF

input datafile file number=00002 name=D:\APP\ADMINISTRATOR\ORADATA\TAJ\SYSAUX01.

DBF

input datafile file number=00003 name=D:\APP\ADMINISTRATOR\ORADATA\TAJ\UNDOTBS01

.DBF

channel ORA_DISK_1: starting piece 1 at 06-SEP-11

channel ORA_DISK_1: finished piece 1 at 06-SEP-11

piece handle=D:\APP\ADMINISTRATOR\11.2.0\DB\DATABASE\02MLSMC8_1_1 tag=TAG2011090

6T164703 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 06-SEP-11

channel ORA_DISK_1: finished piece 1 at 06-SEP-11

piece handle=D:\APP\ADMINISTRATOR\11.2.0\DB\DATABASE\03MLSMDB_1_1 tag=TAG2011090

6T164703 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 06-SEP-11

Starting backup at 06-SEP-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=8 STAMP=761158064

channel ORA_DISK_1: starting piece 1 at 06-SEP-11

channel ORA_DISK_1: finished piece 1 at 06-SEP-11

piece handle=D:\APP\ADMINISTRATOR\11.2.0\DB\DATABASE\04MLSMDH_1_1 tag=TAG2011090

6T164744 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 06-SEP-11

hope this help. write me email in case of any queries at askdbataj

Manually DB Creation with 9i

C:\>rem Create Required Directories

C:\>mkdir c:\ora9ihome\orcl9i\ORADATA
C:\>mkdir c:\ora9ihome\orcl9i\ADMIN\BDUMP
C:\>mkdir c:\ora9ihome\orcl9i\ADMIN\UDUMP
C:\>mkdir c:\ora9ihome\orcl9i\ADMIN\CDUMP

C:\>rem SET ORACLE ENVIOURMENT VARIABLE

C:\>set ORACLE_HOME=c:\ora9ihome
C:\>set PATH=c:\ora9ihome\bin
C:\>set ORACLE_SID=orcl9i

C:\>rem Create PASSWORDFILE for database authentication

C:\>ORAPWD file=c:\ora9ihome\database\PWDORCL9I.ora password=oracle entries=5

C:\>rem Create INIT parameter file and set below parameter

#Cache and I/O

db_block_size=4096

db_cache_size=20971520

#Diagnostics

background_dump_dest=c:\ora9ihome\orcl9i\admin\bdump

core_dump_dest=c:\ora9ihome\orcl9i\admin\cdump

user_dump_dest=c:\ora9ihome\orcl9i\admin\udump

#Control file

control_files='c:\ora9ihome\orcl9i\oradata\control01.ctl'

#Miscellaneous

compatible=9.0.0

db_name=orcl9i

remote_login_passwordfile=exclusive

#pool

java_pool_size=5242880

large_pool_size=5242880

shared_pool_size=20971520

#Processes

processes=75

#sort,hash

sort_area_size=10485760

#undo,rollback

undo_management=auto

undo_tablespace=UNDOTBS

C:\>rem Create Database Services

C:\>ORADIM -new -sid ORCL9I -startmode A
C:\>set oracle_sid=orcl9i

C:\>rem Connect with instance as SYSDBA
C:\>SQLPLUS /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Wed May 7 10:23:23 2008
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> conn sys/oracle as sysdba

Connected to an idle instance.

SQL> --Create Spfile from init.ora file.

SQL> create spfile from pfile='c:\temp\init.ora';
File created.

SQL> --Startup the instance at NOMOUNT stage

SQL> startup nomount

ORACLE instance started.
Total System Global Area 67923680 bytes

Fixed Size 282336 bytes

Variable Size 46137344 bytes

Database Buffers 20971520 bytes

Redo Buffers 532480 bytes

SQL> --Issue the Create Database statement

SQL> create database ORCL9I

2 maxinstances 1

3 maxloghistory 1

4 maxlogfiles 5

5 maxlogmembers 5

6 DATAFILE 'c:\ora9ihome\orcl9i\oradata\system01.dbf' size 200m

7 UNDO tablespace undotbs DATAFILE 'c:\ora9ihome\orcl9i\oradata\undotbs01.dbf' size 100m

8 character set US7ASCII

9 national character set AL16UTF16

10 logfile GROUP 1 ('c:\ora9ihome\orcl9i\oradata\redo01.log') size 5m,

11 GROUP 2 ('c:\ora9ihome\orcl9i\oradata\redo02.log') size 5m;

Database created.

SQL> --Create TEMP or additional tablespace

SQL> Create temporary tablespace TEMP

2 TEMPFILE 'c:\ora9ihome\orcl9i\oradata\temp01.dbf' size 10m;

Tablespace created.

SQL> create tablespace USERS

2 DATAFILE 'c:\ora9ihome\orcl9i\oradata\users01.dbf' size 10m;

Tablespace created.

SQL> --Run Scripts to Build Data Dictionary views

SQL> @%ORACLE_HOME%\rdbms\admin\catalog.sql

SQL> @%ORACLE_HOME%\rdbms\admin\catproc.sql

SQL> @%ORACLE_HOME%\sqlplus\admin\pupbld.sql

Manually DB Creation with 11G

Manually Database Creation is one of the most important works for DBA.

And In 11g it is take few minutes to do :)

The procedure is same as previous which we are used for 9i, 10g.

In this database creation I used below features

1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles
2. FRA (Flash Recovery Area) for Archivelog or backup files
3. ASM (Automatic Storage Mgmt) for Volume manager

1. Create Required Directories

E:\>mkdir e:\oracle\ORA11G

NOTE: above directory is for TRACE FOLDER.

And for ASM I already created DGRP2 group for database.

2. Set Enviourment Variable (ORACLE_HOME,ORACLE_SID,PATH)

E:\>set ORACLE_HOME=c:\app\m.taj\product\11.1.0\db_1
E:\>set PATH=c:\app\m.taj\product\11.1.0\db_1\bin
E:\>set ORACLE_SID=ora11g


3. Choose a Database Administrator Authentication Method

Two authentication method we can use.
1. Password Authentication method
For Password Authentication method we need to create PASSWORD file through ORAPWD utility.

2. OS Authentication method
For OS Authentication method OS user must be member of OS DBA Group.
And SQLNET.AUTHENTICATION_SERVICES= (NTS) is set in SQLNET.ORA file

NOTE: Here I used OS authentication Method.


3. Creating INIT.ORA parameter file
Set minimum required parameter in INIT.ora file.

Open NOTEPAD file and set below parameters

db_name = 'ORA11G'
#Database name.

db_create_file_dest='+DGRP2'
#OMF configuration for Datafile,controlfile
db_create_online_log_dest_1='+DGRP2'
#OMF configuration for redolog file

db_recovery_file_dest='+DGRP2'
db_recovery_file_dest_size=10G
#FRA (FLASH RECOVERY AREA configuration)

diagnostic_dest='E:\ORACLE\ORA11G'
#It is new feature with 11g for trace files (bdump,udump,cdump or many others
#folder created in "DIAG" folder inside "e:\oracle\ora11g" folder.

Save it in Temporary folder.

4. Create an Instance
E:\>oradim -NEW -SID ora11g -STARTMODE auto
Instance created.

5. Connect to an Instance

E:\>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 1 18:04:30 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.

6. Create Server Parameter file (SPFILE)

SQL> create SPFILE from PFILE='c:\temp\init.TXT';
File created.

7. Start the Instance

SQL> startup nomount

ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

8. Create Database

SQL> create database ORA11G;
Database created.

9. Create Temporary and Additional Tablespace

SQL> create temporary tablespace TEMP TEMPFILE '+DGRP2' size 5m;
Tablespace created.
SQL> create tablespace USERDATA DATAFILE '+DGRP2' size 5m;
Tablespace created.
SQL> alter database default temporary tablespace TEMP;
Database altered.
SQL> alter database default tablespace USERDATA;
Database altered.

10. Run Scripts to build data dictionary views

@%ORACLE_HOME%/rdbms/admin/catalog.sql
@%ORACLE_HOME%/rdbms/admin/catproc.sql
@%ORACLE_HOME%/sqlplus/admin/pupbld.sql

11. Change Database Mode from NO-ARCHIVELOG to ARCHIVELOG.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
SQL> alter database ARCHIVELOG;
Database altered.
SQL> alter database OPEN;
Database altered.

NOTE: It is recommended to run database in ARCHIVELOG MODE

12. Check all database files how is look in ASM :)

SQL> select NAME from v$datafile
2 union all
3 select NAME from v$controlfile
4 union all
5 select MEMBER from v$logfile;
NAME
--------------------------------------------------
+DGRP2/ora11g/datafile/system.281.653595347
+DGRP2/ora11g/datafile/sysaux.280.653595371
+DGRP2/ora11g/datafile/sys_undots.279.653595393
+DGRP2/ora11g/datafile/userdata.273.653595537
+DGRP2/ora11g/controlfile/current.285.653595303
+DGRP2/ora11g/onlinelog/group_1.283.653595305
+DGRP2/ora11g/onlinelog/group_2.282.653595325
7 rows selected.

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.