Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 21 August 2013

How to Create Oracle database Manually

There are basically three ways to create database:
Using the database configure Assistance (DBCA)
DBCA can be used to create the new database at the time of oracle installation as well as later at any time as a standalone tool, which provide a graphical interface (GUI) that guide you through the creation of database.
With the SQL create database statement.
You can use the CREATE DATABASE script to create the database manuallly from command prompt. For that you must have created previously your environment as a part of oracle installation (Install oracle software only).
Through upgrading an existing database.
If you are already using a previous release of oracle, You can upgrade your existing database and use it with new release of oracle software
This article basically focusing on the second option (only). It can be completed on the command line that is without any GUI.
Database creation prepares several operating system files to work together as an Oracle database. You need only create a database once, Thus you must carefully plan your database structure before creating a database such as:
1. Plan the database tables and indexes and estimate the amount of space they will require.
2. Plan the layout of underlying operating system. Proper distribution of I/O will improve your database performance. For example: Place redolog files and datafiles on seperate disks. Placing datafiles on seperate disk will reduce contention problem.
3. Consider using OMF feature to create and manage the operating system file that comprise your database storage.
4. Select the global database name, which is the name (DBNAME) and location (DOMAIN_NAME) of database within the network structure.
5. Develop good understanding of Pfile or spfile parameters.
6. You must select the database character set. All characters including data in data dictionary, is stored in database character set
7. Consider what time zones your database must support.
8. Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created. The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four
non-standard block sizes when creating tablespaces.
9. Use an UNDO tablespace to manage your undo records, rather than rollback segments.
10. Develop a backup and recovery strategy to protect the database failure.
                                                                **Step to Create Database Manually**
Step1: Create all the necessary directories.
Step2: Prepare the database Script.
Step3: Prepare the init.ora file.
Step4: Startup created database with init.ora file.
Step5: Finally run the catalog.sql and catproc.sql scripts.

Step1: First create all the required directory on the destination server such as: Admin, adump, bdump, cdump, udump, Archive etc.
Step2: Next Prepare the database creation script such as:
Create Database Script on Windows Environment
----------------------------------------------------------------------------------------------------
Create database MY_DB
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
MAXINSTANCES 1 
MAXLOGHISTORY 292
logfile group 1 ('D:\oracle\MY_DB\redo1.log') size 10M, 
group 2 ('D:\oracle\MY_DB\redo2.log') size 10M,
group 3 ('D:\oracle\MY_DB\redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile 'D:\oracle\MY_DB\system_01.dbf'
size 50M autoextend on
next 20M maxsize unlimited
extent management local
sysaux datafile 'D:\oracle\MY_DB\sysaux_01.dbf'
size 10M autoextend on
next 10M maxsize unlimited
undo tablespace undotbs1
datafile 'D:\oracle\MY_DB\undotbs1_01.dbf' size 10M 
default temporary tablespace temp
tempfile 'D:\oracle\MY_DB\temp_01.dbf' size 10M; 
Note: On windows environment you need to create services using oradim such as:
CMD> ORADIM -NEW -SID MY_DB -PFILE='D:\oracle\admin\SADHAN\pfile\initSADHAN.ora';
Create Database Script on Linux Environment
----------------------------------------------------------------------------------------------------
Create database MY_DB
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
MAXINSTANCES 1 
MAXLOGHISTORY 292
logfile group 1 ('/u01/../redo1.log') size 10M, group 2 ('/u01/../redo2.log') size 10M,
group 3 ('/u01/../redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile '/u01/../system_01.dbf' size 50M autoextend on next 20M maxsize unlimited
extent management local
sysaux datafile '/u01/../sysaux_01.dbf' size 10M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1
datafile '/u01/../undotbs1_01.dbf' size 10M 
default temporary tablespace temp
tempfile '/u01/../temp_01.dbf' size 10M;
Step3: Prepare the init.ora file such as:
audit_file_dest='/u01/../MY_DB/admin/adump'
background_dump_dest='/u01/../MY_DB/admin/bdump' 
compatible='10.2.0.3.0' 
control_files='/u01/../MY_DB/control01.ctl', '/u01/../MY_DB/control02.ctl','/u01/../MY_DB/control03.ctl' 
core_dump_dest='/u01/../MY_DB/admin/cdump' 
db_block_size=8192 
db_domain='' 
db_file_multiblock_read_count=16 
db_name='MY_DB' 
dispatchers='(PROTOCOL=TCP) (SERVICE=my_dbXDB)' 
job_queue_processes=10 
log_archive_dest_1='LOCATION=/u01/../MY_DB/archive' 
log_archive_format='%t_%s_%r.dbf' 
open_cursors=300 
pga_aggregate_target=220200960 
processes=150 
remote_login_passwordfile='EXCLUSIVE' 
sga_target=629145600 
undo_management='AUTO' 
undo_tablespace='UNDOTBS' 
user_dump_dest='/u01/../MY_DB/admin/udump' 
db_recovery_file_dest='/u02/../MY_DB/backup' 
db_recovery_file_dest_size=230686720 
Step4: Now start the newly created database in nomount phase with the help of init.ora file.
$ export ORACLE_SID=my_db 
$ sqlplus / as sysdba 
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 21 10:26:54 2012 
Copyright (c) 1982, 2006, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> Startup Pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initmy_db.ora nomount; 
ORACLE instance started. 
Total System Global Area 629145600 bytes 
Fixed Size 1443789 bytes 
Variable Size 168878648 bytes 
Database Buffers 447849588 bytes 
Redo Buffers 7340032 bytes 
SQL> @My_db.sql 
Database created.
Step5: Finally run the catalog.sql and catproc.sql scripts.
Thus the database is created now. you just need to run the catalog.sql and catproc.sql scripts. You will find these script on the location: $ORACLE_HOME/rdbms/admin
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql 
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql 
SQL> select name from v$database; 
NAME 
--------- 
MY_DB 
Finally now your database is ready to use. 

3 comments:

  1. Fantastic article ! You havemade some very astute statements and I appreciate the the effort you have put into your

    writing. Its clear that you know what you are writing about. I am excited to read more of your sites content.

    Hadoop training

    ReplyDelete

  2. Dear friend. I truly just like your posting and your current web page all in all! That write-up is really plainly composed and without difficulty understandable. Your Wordpress style is awesome as well! Would be awesome to discover exactly where My partner and i are able obtain this. Please hold up the very good work. We all need much more this kind of website owners just like you on the web and much less spammers. Wonderful man!
    Microsoft dynamics training

    ReplyDelete
  3. BlueHost is ultimately the best web-hosting provider for any hosting plans you might require.

    ReplyDelete