Chapter 3: CREATING A DATABASE (ORACLE 9i)

Chapter 3: CREATING A DATABASE

CHAPTER NO # 3

CREATING A DATABASE

PLANNING AND ORGANIZING A DATABASE:

Planning for your database is the first step in managing a database system.

-          Define the purpose of the database

-          Define the type of the database

-          Outline the database architectural design

-          Choose the database name.

Create your database

OPTICAL FLEXIBLE ARCHITECTURE  (OFA) :

Oracle recommended standard database architecture layout

OFA includes 3 major rules :

-          Establish a directory structure where any database file can be stored on any disk resource

-          Separate objects with different behavior into different tablespaces. 

-          Maximize database reliability and performance by separating database components across different disk resources.

OFA organize database files by types and usage.

Binary files, control files, online redo log files, and administrative files can be spread across multiple disks.

A consistent naming convention provides the following benefits:

1- Database files can be easily differentiated from other files.

2- It is easy to identify the control files, online redo log files, and data files.

3- Better performance is achieved by decreasing disk contention among data files, binary files and administrative files which can reside on separate directories and disks.

CREATION PREREQUISITES:

To create a new database, you must have the following:

-          A privileged account authenticated by one of the following:

1-  Operating system

2-  Password file

      -     Sufficient memory to start the instance.

      -     Sufficient disk space for the planned database.

USING PASSWORD FILE AUTHENTICATION :

CREATING A DATABASE

Database can be created by one of the three ways:

1. Can be created as part of the oracle 9i installation using oracle universal installer.
2. By using Oracle Database Configuration Assistant  (DBCA)
3. By creating SQL script using create database command.

CREATING A DATABASE  BY  (DBCA)
ORACLE DATABASE CONFRIGATION ASSISTANT

The DBCA is graphical user interface that interacts with the oracle universal installer, or can be used stand alone, to simplify the creation of database. DBCA is JAVA based and can be launched from any platform with a JAVA engine.

By DBCA you can :

1. Create a Database
2. Configure Database Options
3. Delete a Database
4. Manage Template
• Create new templates using predefined template settings
• Create new template from an existing database
• Delete database template

PRACTICAL #

STEP # 1:

Delete the exsisting password file using operating system commands,

Location :   D:\oracle\ora92\database\PWDORCL

STEP # 2:

Create a new password file by using password utility in CMD (DOS)

Write this syntax:

ORAPWD FILE=D:\ORACLE\ORA92\DATABASE\PWDORCL.ORA

PASSWORD=ORACLE ENTRIES=5

Note :  If error generate :    RESTART ORACLE SERVICES

STEP # 3:

RUN DBCA

Location:  D:\oracle\ora92\bin

Welcome Slide   :   Next

Slide 1                :   Choose Option Create database              : Next

Slide 2                :   Choose type of database                         : Next

Slide 3                :   Write Database Name & SID                  : Next

Slide 4                :   Choose Dedicated Server Mode              : Next

Slide 5                :   Leave all option as default                       : Next

Slide 6                :   Simple                                                      : Next

Slide 7                :   Choose Create a Database                       : Next

Slide 8                :                                                                    : Finish

Summary Window Open Click OK

Now Process Start.

CREATING A DATABASE MANUALLY

PRACTICAL #

STEP # 1:

Create a New Folder _MYDB_ (give name) in

D:\oracle\oradata\_MYDB_

STEP # 2:

Create 5 new folder in _MYDB_

And give the following name

1. create
2. udump
3. bdump
4. cdump
5. pfile

STEP # 3:

Copy old init.ora file

Location   D:\oracle\admin\orcl\pfile\init.ora

And paste new pfile folder you create above

STEP # 4:

Open file in notepad change the following parameters

            CHANGE – 01:

DB_NAME=  _MYDB_

            CHANGE – 02:

DIAGNOSTIC AND STATISTICS:

Backgroud_dump_dest = D:\oracle\oradata\_MYDB_\bdump

Core_dump_dest = D:\oracle\oradata\_MYDB_\cdump

User_dump_dest = D:\oracle\oradata\_MYDB_\udump

            CHANGE – 03:

FILE CONFRIGATION:

Control_file=(“ D:\oracle\oradata\_MYDB_\create\control01.dbf” ,

                       “ D:\oracle\oradata\_MYDB_\create\control02.dbf” ,

                       “ D:\oracle\oradata\_MYDB_\create\control03.dbf” )

            CHANGE – 04:

INSTANCE IDENTIFICATION:

Instance_name = _MYDB_

            CHANGE – 05:

SECURITY AND AUDITING:

Remote_login_password_file = SHARED

            CHANGE – 06:

SYSTEM MANAGED UNDO AND ROLLBACK SEGMENTS

undo_management=AUTO

undo_tablespace=UNDOTBS1

Now file save as :    “init.ora”

Destination :             All types

STEP # 5:

Open SQL Prompt

Connect   sys/oracle as sys dba

Shutdown Instance

Than     

Startup Nomount PFILE=’D:\oracle\oradata\_MYDB_\pfile\init.ora’ ;

Massage :  Oracle Instance Started

STEP # 6:

Write the following coating on SQL prompt

CREATE DATABASE  MYDB

DATAFILE 'D:\ORACLE\ORADATA\MYDB\CREATE\SYSTEM01.DBF' SIZE 200 M

LOGFILE

GROUP 1 'D:\ORACLE\ORADATA\MYDB\CREATE\G1M1.LOG' SIZE 10 M,

GROUP 2 'D:\ORACLE\ORADATA\MYDB\CREATE\G2M1.LOG' SIZE 10 M

UNDO TABLESPACE UNDOTBS1

DATAFILE 'D:\ORACLE\ORADATA\MYDB\CREATE\UNDO01.DBF' SIZE 20 M

DEFAULT TEMPORARY TABLESPACE TEMP

TEMPFILE 'D:\ORACLE\ORADATA\MYDB\CREATE\TEMP01.DBF' SIZE 50 M;

(OPTIONAL COMMAND WHILE CREATING DATABASE

MAXLOGFILE    5

MAXLOGMEMBERS   5

MAXLOGHISTORY   1

MAXDATAFILES   100

MAXINSTANCE   1)

STEP # 6:

To copy default tables we have to run these scripts

@D:\oracle\ora92\RDBMS\admin\catalog.sql

@D:\oracle\ora92\RDBMS\admin\catproc.sql

STEP # 7:

Now we create additional table, tablespaces as needed .

END

CREATING A DATABASE USING OMF

ORACLE MANAGE FILES

PRACTICAL #

STEP # 1:

Create a New Folder _ORCL3_ (give name) in

D:\oracle\oradata\_ORCL3_

STEP # 2:

Create 5 new folder in _ORCL3_

And give the following name

6. create
7. udump
8. bdump
9. cdump
10. pfile

STEP # 3:

Copy old init.ora file

Location   D:\oracle\admin\orcl\pfile\init.ora

And paste new pfile folder you create above

STEP # 4:

Open file in notepad change the following parameters

            CHANGE – 01:

DB_NAME=  _ORCL3_

            CHANGE – 02:

DIAGNOSTIC AND STATISTICS:

Backgroud_dump_dest = D:\oracle\oradata\_ORCL3_\bdump

Core_dump_dest = D:\oracle\oradata\_ORCL3_\cdump

User_dump_dest = D:\oracle\oradata\_ORCL3_\udump

            CHANGE – 03:

Mark     ( # )    on control files

            CHANGE – 04:

INSTANCE IDENTIFICATION:

Instance_name = _ORCL3_

            CHANGE – 05:

Mark  ( # )  on     undo_management=AUTO

Mark  ( # )  on     undo_tablespace=UNDOTBS1

            CHANGE – 6:

Define OMF parameters

DB_CREATE_FILE_DEST=D:\ORACLE\ORADATA\ORCL3\CREATE

DB_CREATE_ONLINE_LOG_DEST_1=D:\ORACLE\ORADATA\ORCL3\CREATE

DB_CREATE_ONLINE_LOG_DEST_2=D:\ORACLE\ORADATA\ORCL3\CREATE

Now file save as :    “init.ora”

Destination :             All types

STEP # 5:

Open SQL Prompt

Connect   sys/oracle as sys dba

Shutdown Instance

Than     

Startup Nomount PFILE=’D:\oracle\oradata\_ORCL3_\pfile\init.ora’ ;

Massage :  Oracle Instance Started

STEP # 6:

Write the create database command on SQL prompt

SQL>   create database orcl3;

END 

CREATE DATABASE COMMAND

1. DATABASE:

Is the name of the database

2. CONTROLFILE REUSE:

Means existing control file identified in the initialization parameter file should be reuse.

3. LOGFILE GROUP:

Specifies the name of the log files to be used and the group to which they belong. The database requires at least to online redo log file group.

4. MAXLOGFILES:

Specifies the maximum number of online redo log file group that can ever be created for the database.

5. MAXLOGHISTORY:

Specifies the maximum number of archived redo log files for automatic media recovery.

6. MAXDATAFILES:

Maximum datafiles can be created in a database.

7. MAXINSTANCE:

Specifies the maximum number of instance that can mount and open the database.

8. ARCHIVELOG | NOARCHIVELOG

Specify archive log if you want the content of online redo log file group to be archived

before reuse.

Specify noarchive log if you want the content of online redo log file group to be not archived before reuse.

9. FORCE LOGGING

Specifies the logging of all changes in the database except for changes in temporary tyablespaces and temporary segments.

10. CHARACTER SET:

Specifies the character set the database use to store data.

11. DEFAULT TEMPORARY TABLESPACE

Creates a default temporary table space for the database.

Oracle will assign to this tablespace any users for whom you do not specify a different temporary tablespace

12. UNDO TABLESPACE

Creates undo tablespace and creates the specified data files as part of the undo tablespace.

AFTER DATABASE CREATION

The Database contains:

• Data files, control files, online redo log files
• User sys with the password  ( change_on_install )
• User system with the password manager
• Internat tables (but no data dictionary views)

--------------------------------  END OF CHAPTER 03  ------------------------