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.
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.
No comments:
Post a Comment