Creating a Database

Creating a Database

Managing and Organizing a Database

• Creating a database is the first step in managing a database system

• A database may have been created automatically as part of Oracle9i Server installation, or you can create a new one later

• Oracle Data Migration Assistant is used to migrate from an earlier version of the database

Creation of Database:

Here we can create Database in two methods:

·         Manually

·         DBCA(Database Configuration Assistant

Creation of Database using Manually

Step 1:

First create all the necessary directories. Followings are my directories:

For eg : now I’m going to create new database i.e., TEST then we have to create new directories in

$cd /Oracle/product/10.2.0/db_1/admin/

Create TEST directory in that location

$mkdir TEST

Go to TEST directory

$cd TEST

And create remaining directories

$mkdir  adump bdump cdump udump arch

Similarly we have to create new directory in oradata

$cd /Oracle/product/10.2.0/oradata

Create TEST directory here

$mkdir TEST

Step 2:

Next prepare the database creation script. Following is my script "TEST.sql"

CREATE DATABASE TEST

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/Oracle/product/10.2.0/oradata/TEST/redo1.log' SIZE 10M,

GROUP 2 '/Oracle/product/10.2.0/oradata/TEST/redo2.log' SIZE 10M,

GROUP 3 '/Oracle/product/10.2.0/oradata/TEST/redo3.log' SIZE 10M

DATAFILE

'/Oracle/product/10.2.0/oradata/TEST/system.dbf' size 100m,

'/Oracle/product/10.2.0/oradata/TEST/usr04.dbf' size 10m

sysaux datafile '/Oracle/product/10.2.0/oradata/TEST/sysaux.dbf' size 100m

undo tablespace undotbs

datafile '/Oracle/product/10.2.0/oradata/TEST/undo.dbf' size 50m

CHARACTER SET US7ASCII;

Save this TEST.sql in any location your wish here I’m going to save this file in $ORACLE_HOME/dbs location

Step 3:

Prepare the init file. Like this one [initTEST.ora]

For eg I have old parameter file initPROD.ora, then I need to copy that file as

$cp initPROD.ora initTEST.ora

After copying we need to modify that file by replacing PROD as TEST

$vi initTEST.ora

*.audit_file_dest='/Oracle/product/10.2.0/db_1/admin/TEST/adump'

*.background_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/Oracle/product/10.2.0/oradata/TEST/control01.ctl', '/Oracle/product/10.2.0/oradata/TEST/control02.ctl','/Oracle/product/10.2.0/oradata/TEST/control03.ctl'

*.core_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='TEST'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=’/Oracle/product/10.2.0/db_1/admin/TEST/arch’

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=200278016

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=601882624

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS'

*.user_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/udump'

*.db_recovery_file_dest='/Oracle/product/10.2.0/db_1/flash_recovery_area’

*.db_recovery_file_dest_size=2147483648

Step 4:

Now perform the following steps:

Export env variables

$ export ORACLE_SID=TEST

$ export ORACLE_HOME=/Oracle/product/10.2.0/db_1

$ export PATH=/Oracle/product/10.2.0/db_1/bin:$PATH

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1263176 bytes

Variable Size 167774648 bytes

Database Buffers 427819008 bytes

Redo Buffers 7122944 bytes

SQL> @TEST.sql

Database created.

Step 5:

So your database is create. Now just run the catalog.sql, catproc.sql and pupbld.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

SQL> @/Oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

SQL> @/Oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

SQL>conn system/manager

Connected

SQL>@/Oracle/product/10.2.0/db_1/sqlplus/admin/pupbld.sql

SQL> select name from v$database;

NAME

---------

TEST

Okay, now your database is ready to use.

Creation of Database using DBCA

To create database using DBCA we need to execute this command using root user

$ xhost +

After that logon to our oracle os user

$ su – Oracle

Start the database creation process by running the command dbca. Before that, please ensure that the Oracle Home is set propely.

$ export ORACLE_HOME=/Oracle/product/10.2.0/db_1

$ export PATH=/Oracle/product/10.2.0/db_1/bin:$PATH

$ dbca

The DBCA appears on the screen as below.

 

Choose the option to create a database.

Opt for custom database creation where you can choose to create a custom database.

Give an appropriate name to the database.

If you have a grid control setup, let the dbca register the database to the grid.

The choice of file system or an ASM solely depends on the kind of infrastructure available.

This is the location where you can specify the name of all the files. The location of files can be further controlled by when we get the tablespace, redolog and controlfiles option.

The use of the Flash Recovery Area or Archive log destination depends on for what purpose you plan to use the database. If it is a system testing or a development environment, then the option might be skipped.

Depending on the need, you can configure the components for the database. If you need XA components, then JVM also needs to be made available. If you need a vanilla database, simply check out all the options.

Often, with DBCA, the components like XML, JVM get installed. These options are considered as the standard options. Click on the "Standard Database Components" tab as show above. This will take you to the options below.

The next section is the database sizing where memory parameter, storage parameters, the charachter sets may be taylored. If your system uses multiple connections and a OLTP kind of an environment, then you may go for a shared server connections. However, in most of the cases, the dedicated server connection will be the preferred one.

You can customize your database content with the type of charachterset you prefer.

This section results into the location/sizing of datafiles, controlfiles and the redolog files.

The final part would be to get a confirmation as to what you have done so far.

These are the simple steps to create the database with DBCA. This also happens to be one of the first chapters you need to study for your Oracle Certifications(Oracle Certified Associate(OCA)).

The choice of DBCA mainly depends on the type of environment you prefer.. It is mostly used for development and testing environments where the purpose of the database is to be a simple back end database with optimum performance. DBCA may be used a standard build tool to create a database once it is determined what what standards are acceptable.