Manual Db Creation

C:\>rem Create Required Directories
C:\>mkdir c:\ora9ihome\orcl9i\ORADATAC:\>mkdir c:\ora9ihome\orcl9i\ADMIN\BDUMPC:\>mkdir c:\ora9ihome\orcl9i\ADMIN\UDUMPC:\>mkdir c:\ora9ihome\orcl9i\ADMIN\CDUMP
C:\>rem SET ORACLE ENVIOURMENT VARIABLE
C:\>set ORACLE_HOME=c:\ora9ihomeC:\>set PATH=c:\ora9ihome\binC:\>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 AC:\>set oracle_sid=orcl9i
C:\>rem Connect with instance as SYSDBAC:\>SQLPLUS /nologSQL*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
Email this • Share on Facebook • Digg This! • Add to del.icio.us • Save to del.icio.us • Subscribe to this feed • Technorati Links • Sphere: Related Content • outside.in: geotag this story • Discuss on Newsvine • Add to Mixx! • Stumble It!
Posted by Mohammed Taj at 11:24:00 AM 0 comments
Labels: Database Creation
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 features1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles2. FRA (Flash Recovery Area) for Archivelog or backup files3. ASM (Automatic Storage Mgmt) for Volume manager1. Create Required DirectoriesE:\>mkdir e:\oracle\ORA11GNOTE: 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_1E:\>set PATH=c:\app\m.taj\product\11.1.0\db_1\binE:\>set ORACLE_SID=ora11g3. Choose a Database Administrator Authentication MethodTwo authentication method we can use.1. Password Authentication methodFor Password Authentication method we need to create PASSWORD file through ORAPWD utility.2. OS Authentication methodFor OS Authentication method OS user must be member of OS DBA Group.And SQLNET.AUTHENTICATION_SERVICES= (NTS) is set in SQLNET.ORA fileNOTE: Here I used OS authentication Method.3. Creating INIT.ORA parameter fileSet minimum required parameter in INIT.ora file.Open NOTEPAD file and set below parametersdb_name = 'ORA11G'#Database name.db_create_file_dest='+DGRP2'#OMF configuration for Datafile,controlfiledb_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 InstanceE:\>oradim -NEW -SID ora11g -STARTMODE autoInstance created.5. Connect to an InstanceE:\>sqlplus /nologSQL*Plus: Release 11.1.0.6.0 - Production on Thu May 1 18:04:30 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.SQL> conn / as sysdbaConnected to an idle instance.6. Create Server Parameter file (SPFILE)SQL> create SPFILE from PFILE='c:\temp\init.TXT';File created.7. Start the InstanceSQL> startup nomountORACLE instance started.Total System Global Area 150667264 bytesFixed Size 1331740 bytesVariable Size 92278244 bytesDatabase Buffers 50331648 bytesRedo Buffers 6725632 bytes8. Create Database SQL> create database ORA11G;Database created.9. Create Temporary and Additional TablespaceSQL> 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.sql11. Change Database Mode from NO-ARCHIVELOG to ARCHIVELOG.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 150667264 bytesFixed Size 1331740 bytesVariable Size 92278244 bytesDatabase Buffers 50331648 bytesRedo Buffers 6725632 bytesDatabase mounted.SQL> alter database ARCHIVELOG;Database altered.SQL> alter database OPEN;Database altered.NOTE: It is recommended to run database in ARCHIVELOG MODE12. 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.6535953257 rows selected.
Email this • Share on Facebook • Digg This! • Add to del.icio.us • Save to del.icio.us • Subscribe to this feed • Technorati Links • Sphere: Related Content • outside.in: geotag this story • Discuss on Newsvine • Add to Mixx! • Stumble It!
Posted by Mohammed Taj at 5:37:00 PM 0 comments
Labels: Database Creation
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>dbcaorstart>program>oracle_home>configuration_and_migration>DATABASE CONFIGURATION ASSISTANT2. 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.cdump2. 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\createmkdir D:\oracle\product\10.1.0\admin\orcl1\pfilemkdir D:\oracle\product\10.1.0\admin\orcl1\bdumpmkdir D:\oracle\product\10.1.0\admin\orcl1\udumpmkdir D:\oracle\product\10.1.0\admin\orcl1\cdumpmkdir D:\oracle\product\10.1.0\oradata\orcl14.Set oracle_sid enviourment variable or create password file.C:\>set oracle_sid=orcl1C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdorcl1.ora password=oracle entries=5 force=yNote: 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 systemor C:\>oradim -new -sid ORCL1 -startmode manual -spfileC:\>oradim -edit -sid ORCL1 -startmode auto -srvcstartC:\>D:\oracle\product\10.1.0\db_2\bin\sqlplus /nologSQL*Plus: Release 10.1.0.2.0 - Production on Sat Nov 11 17:51:44 2006Copyright (c) 1982, 2004, Oracle. All rights reserved.SQL>6.Run Create database scriptsSQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDB.sqlEnter value for syspassword: oracleConnected to an idle instance.SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDB.logSQL> startup nomount pfile="D:\oracle\product\10.1.0\admin\orcl1\scripts\init.ora";ORACLE instance started.Total System Global Area171966464 bytesFixed Size787988 bytesVariable Size145488364 bytesDatabase Buffers25165824 bytesRedo Buffers524288 bytesSQL> CREATE DATABASE "orcl1"2 MAXINSTANCES 83 MAXLOGHISTORY 14 MAXLOGFILES 165 MAXLOGMEMBERS 36 MAXDATAFILES 1007 DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED8 EXTENT MANAGEMENT LOCAL9 SYSAUX DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\sysaux01.dbf' SIZE120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\oracle\product\10.1.0\oradata\orcl1\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED11 UNDO TABLESPACE "UNDOTBS1" DATAFILE 'D:\oracle\product\10.1.0\oradata\orcl1\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED12 CHARACTER SET WE8MSWIN125213 NATIONAL CHARACTER SET AL16UTF1614 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 10240K17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";Enter value for systempassword: oracleold17: USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"new17: USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle"Database created.SQL> spool offSQL>7. Run Datafile's scriptsSQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\CreateDBFiles.sqlSQL> connect SYS/&&sysPassword as SYSDBAConnected.SQL> set echo onSQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\CreateDBFiles.logSQL> 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.sqlinstead 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 creationSQL> @D:\oracle\product\10.1.0\admin\orcl1\scripts\postDBCreation.sqlSQL> connect SYS/&&sysPassword as SYSDBAConnected.SQL> set echo onSQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.logSQL> connect SYS/&&sysPassword as SYSDBAConnected.SQL> set echo onSQL> 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 SYSDBAConnected 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 bytesDatabase 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:461 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:481 row selected.SQL> spool D:\oracle\product\10.1.0\db_2\assistants\dbca\logs\postDBCreation.logSQL> exit;