Search This Blog

Tuesday, September 6, 2011

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




1 comment:

Anonymous said...

nice one taj...

rohit