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