Search This Blog

Thursday, May 1, 2008

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.

6 comments:

Nilesh said...

hEY bOSS... tHIS NICE ONE .. I M REALLY LOOKING FOR THE SAME ONE ..EARLIER I USED 9I BUT RECENTLY I M TRYING TO WORK ON 11g BUT STILL UNABLE TO GET THROUGH .HOPE UR TOPIC MAY HELP ME OUT...
THANKS

rajesh said...

nice documentation taj
i am working as an system admin in wipro chennai. i have done ocp certification in oracle 9i. is there is any opening in dubai as oracle dba for freshers. pls reply me.

Anonymous said...

can you do the same thing on linux 4 platform i will be very grateful to you.

How to create database manually in oracle 11g on linux platform?

Thanks in advance

Maria said...

wonderful taj....

akhthar said...

How to create database manually in oracle 8i on winxp?
can we follow the same procedure!!!

Thanks in advance

akki

Anonymous said...

You should really stop using Windows for Oracle databases. I spent months migrating a company to Red Hat because some sys admin who long ago left the company only knew Windows and installed everything on Windows. When your company goes to hire more DBAs they will have trouble finding one who is both *knowledgable* and *wants* to work with Windows. Microsoft's OS is old news, and honestly terrible for most server tasks. IMO it's for corporate IT guys who like running Exchange, SQLServer, and cheap 3rd part software solution from companies who only know .NET because they came from colleges with Microsoft partnerships that only taught .NET.