Oracle is a powerful database tool. I was assigned to prepare the report on database creation using oracle. Coding or physical creation of database in oracle is relatively easier task as compared with its planning and logical creation.

Keeping this situation in view I’ve focused on planning and logical creation rather then it’s coding. I’ve phased my report in four sections in these sections I’ve discussed planning, creating and securing the database as well as the concept of data dictionary.

Although that was not an easy task, however I have tried my level best to comprehend the topic and make concept easy so that it could be help full to others.

Treat this report as an asset. Thank you.

Contents

Database Management system………………………………

Phase 1:

Planning and design

DBMS in ORACLE………………….

Phase 2:

Preparing

The database……………………..

Phase 3:

Creating and securing

The database………………………

Phase 4:

The data dictionary……………….

Aims and objectives:

To describe the need for design and planning when creating DATABASE in ORACLE.

When you are creating a database careful planning and designing are essential. Time spent at these early stage swill greatly reduce problems later on.

Who creates a database? A DBA (database administrator).you as a DBA should consider following points:

· What are the planned applications?

· What are the data categories?

· What are your backup strategies?

It is important that you understand the relationship between all contents of database. Storage architecture diagram shown on the next page illustrate the relationship between various database contents. Right side of diagram shoes relationship between logical components while left side shows physical components.

As you can see a database consist of one or more then one tablespace, each tablespace consists of one or more operating system file and has one or more extent.

An extent is set of continuous data blocks in a database. An oracle data block is smallest unit of I/O that the database uses. Extents are group together as segments. Segments are allocated to logical storage structures such as tables and indexes.

For database planning to be truly successful the DBA need to familiar with the characteristics of data to be stored. The logical division of data occurs through the use of tablespace.

By taking time to determine the appropriate table space structure for user database you’ll help to:

· Minimize fragmentation.

· Minimize disk contention.

· Separate segments.

An oracle database always includes the system tablespace, however, other tablespaces can also be defined. On some platforms the oracle installation program will automatically create a number of predefined tablespaces. A tablespace can brought online while database is running. With the exception of the system tablespace, tablespace can go-or be taken - offline ,leaving the database running. However if a tablespace contain active rollback segment, it cannot be taken offline.

Each object- table, index, or cluster- can be only in one tablespace.

There are number of steps required to setup the OS directory structure of machine on which oracle server will be used. First you need to name all drives that might contain oracle server date- applicable to

Hierarchical data structure only -. This ensures that a wildcard or similar mechanism can be used to refer to the collection of drives as unit. Then you need to create a directory at the same level on each of the drives for oracle server date.

You place al relative files into a newly created directory which can include a control file, redo log file, and data files. Once the operating system directory structure is setup the drives on system can hold any file from any database without risk of disorganization.

In addition of system tablespace you can create a number of tablespaces. The TEMP tablespace store the temporary segments used when sorting. The RBS tablespace stores addition rollback segments. You cannot name a segment “Rollback” because it is a reserved word. The tool table space contains tools needed by oracle. The APPLI_DATA tablespace stores production data. You can name the table what ever you want except reserved word. More then one table can be used for production of data. Indexes associated with data production are also stored in APPLI_INDEX table space. It is highly recommended that you separate data segments from index segments. You can classify the space allocated to a tablespace , cluster, and tableindex by creating a logical structure called segments, manipulating segments or groups of segments can help you minimize fragmentation and disk contention. A fragmented database is a database in which

free space is scattered about in number of small spaces that make space usage less efficient.

Certain segment types are more prone to fragmentation then others :

· Data dictionary segment have no tendency towards fragmentation and never fragment free space.

· Application data segments have a low fragmentation propensity, as tables have life span equal to that of their associated project.

· Application-interim and rollback segments have moderate fragmentation capacity.

· Temp segment has a high fragmentation capacity.

A temporary segment is a storage space within tablespace that’s used as temporary work area during processing. Each temp segment is created specifically

For user purpose and they disappear when process no longer requires it . You can minimize the fragmentation by separating groups of segments with different fragmentation characteristics among different table spaces.

As a rule you should not allow temporary or additional rollback segments in system tablespace also you should give system storage quota only to other DBA’s.

Disk contention is where multiple requests from processor to a single or small number of physical disks have to be queued. This occurs because data can only be accessed by the same set of read-write heads one time. By locating key data on different physical disks these sets can be read simultaneously.

You can minimize disk contention by separating groups of segments that can be used simultaneously.

You can separate:

· Dictionary, rollback, and temp segments from other segments and each other.

· Data segments from their corresponding index segments.

· Large segments from small segments.

It is important to protect your data base while at the same time maximize its storage and performance. There are a number of guidelines you should follow:

· You should keep at least two active copies of a database control file on at least two physical locations.

· Also you should separate REDO log file and put group members on separate files.

· You should separate tablespace whose data(if located on same disk) is a liable to

cause disk contention. This process is known as stripping.

Aims and objectives:

To describe the initial steps needed to create a DATABASE.

One of the first things you needed to do when creating an oracle database is to decide on an instance name. The name must be unique. However, Instance and database should normally have the same name. An instance is a mean of accessing the database, it contains background processes and memory needed to run oracle. (Oracle’s shared structure is collectively known as system global area-SGA).

The name of oracle background always includes the instance name to avoid having duplicate instance name. You check the names of other oracle instance on your system by listing all current processes.

To list all current process you search for the INIT.ORA with file manager. Once you have decided about an instance name you must set it at OS level to make sure that you are working in an appropriate environment.

The system identifier or SID is used by oracle server to determine which instance the user will connect to. By setting a system identifier a user id able to connect different databases before starting up instance you must SID to the correct value

For example the command.

SET ORA_SID = TEST will set the value of ORA_SID and verify it. Syntax for setting the environmental variables for UNIX would be

C SHELL:

Prompt>SETENV ORACLE_SID test

The value of SID can be up to eight characters, depending on OS used. The parameter file determines instance configuration such as the size of memory buffers, the location, and number of control files and number of background of process it is a small text file commonly known as INIT<SID>.ora file that is read during instance startup only

A default parameter file is supplied with oracle software. You can copy the file into new location and modify accordingly. Parameters value can be of different types like. Parameter value can be of three different types: integer, character, and Boolean. Most parameters have default settings. The parameters can have a significant effect on database performance, and some need to be modified for production system.

To display or see the parameter setting you select the ORACLE SQL WORKSHEET icon from enterprise manager.

First you connect the database as SYSDBA, then use SHOW PARAMETERS command. Among the parameter that should be specified are:

· The database identifier.

· The name of control file.

· The number of block cached in SGA.

· The location where background trace files are written.

· The location where user trace files are created.

· The size of share pool.

You should specify at least control file name in a parameter file, placing them on separate disk if possible.

If DBA name is specified it should match the database stored in control file.

You should specify parameter files setting for the size of database block as well as for the version of server with which the instance should be compatible.

The table on this page shows other commonly modified along with description of their functions. Once you modify the parameter file you should shutdown and restart the instance to make new values effective. Certain parameters like DB_BLOCK_SIZE cannot change after database creation in order to change this parameter the table should be recreated. You can also change some parameters using ALTER TABLE command. Alternatively you can use ORACLE INSTANCE MANAGER Icon from enterprise manager to display and change the parameters settings you select the “INITIALIZATION PARAMETER” icon in the list to display the initialization parameter property sheet for example

change the cache size threshold to 25 select it from the list to display the edit initialization parameter dialog box setup new value in STARTUP field and then click the APPLY button to save the changes. If you have made changes to parameters that would be dynamically updated changes take effect when you click the APPLY button, but for those which are not dynamically update you are required to restart the database to make the changes effective.

Oracle can represent data in any language through the use of NLS-national language support-. By using character set made up of longer pieces of information then normal oracle can accommodate languages such as Japanese and Chinese. You should set the ORA_NLS variable before you can create your database, as ORA_NLS is a path to language object file. If ORA_NLS file is not set characters other then default would not be recognized. This means character translation would not occur for user whose character set differ from default and 8 bit value may be lost or misprinted.

Other problems associated with not setting ORA_NLS are

· Message would have displayed in other then expected language.

· ORA_12705 errors from user connection.

· “SQL” loader-266 unable to locate character set error.

You can check which NLS settings are valid by connecting the database as DBA and issuing the following command:

SELECT * FROM sys.v$NLS_valid_values.

If ORA_NLS values are not set only default values will be shown.

Aims and objectives:

How to create and secure the new database.

Creating a Database

You must complete several steps before you can create an oracle database including:

· Deciding on an instance name.

· Copying and editing parameter file.

· Setting the system identifier.

· You also need to set ORA_NLS if you don’t want to use default settings.

Once you have done this initial work you are ready to connect to oracle default database. You can connect to database using the “CONNECT sysDBA” command.

Once connected to the oracle installation database you are ready to start the instance. An instance is a combination of memory buffers and background processes and is the environment in which database exists and function.

You can start the instance using “STARTUP NOMOUNT” command. This is the generic way to start an instance regardless what OS you use.

You are now ready to create your database. An oracle database consists of:

Data files that store tables and indexes.

Redo log files that are part of the oracle server recovery structure.

Control files that contain information necessary starts and maintain the database.

To create database you use “create database” command.

For example assume that database is the name of “database” and file space is a data file or log file space. Bear in mind that:

· Control file reuse is not normally specified for new database.

· No_archievelog and Exclusive are default when database is created.

· Archive log mode should not be specified unless the redo log files are large enough to hold all information relating data dictionary creation.

This example shows how a database called “TEST” is created.

The database has 10 MB data file called “SYSTEM.DBF”.

It also has two 500KB log files called “LOG1a.RDO” and “LOG2a.RDO”.

The “create database” statement may take some time to complete due to amount of processing taking place in background.

Another example:

The final piece of code specifies that character set WE8 8 59PI be used for database dba1.

After you select the “Execute” button the “Create Database” takes some time to complete due to amount of processing taking place in background.

Securing the new Database

Once you have created a database you can ensure its stability by:

· Multiplex control files.

· Multiplex redo log files.

· Creating a password file.

A control file contains information necessary to start and maintain database. It is read when database is started. If he control file is not found the MOUNT and OPEN states will fail when you startup data. Oracle allows multiplex, identical control files to be open concurrently and written off for same database.

For safety reason a database should have two control files, each on different disk. This placement of file is known as multiplexing. All control files contain same information and are updated when any changes are made to database; if one file is lost other will remain usable. Before adding a control file you need to shut down the database, make a copy of existing control file to a new location, next you alter the parameter file to include name of new copy. When parameters files are modified you can start database. Both files will then written simultaneously.

“This procedure assumes that only one control file exists. These steps are not necessary when database has created with multiplex control file”

You should remember to confirm that the oracle server has read write privileges to newly created control file.

Multiplex Redo log files

Another way to ensure the security of newly created database is multiplex redo log files, with multiplex redo log files oracle concurrently writes the same redo log information to multiple, identical online redo log file are called groups and each redo log file is called a member. Every oracle database must have at least two redo log file groups and each group should contain at least one member. The redo log file records all changes made to data in database buffer cache.

If an instance failure occurs the redo log files are used recover the modified data that was in memory. Redo log files are only used for recovery. To avoid a redo log file becoming a single point of failure for an oracle data base you must ensure that each group has more then one member. You can add new member to a redo log file group with “ALTER DATABASE” SQL command.

ALTER DATABASE test

ADD LOGFILE MEMBER ’c:\oracle\dat01\log1b.rdo’

TO group 1.

Alternatively you can use ORACLE BACKUP RECOVERY MANAGER to add a redo log file.

Password file

You can also protect the security of new database creating password file. A password is used to authenticate the users performing database administration. A password file is created by using ORAPWD utility.

Aims and objectives:

To describe the functions of data dictionary.

One of most important elements of oracle data base is data dictionary. The data dictionary is a comprehensive set of base tables and views that provides a read only information reference for associated oracle data base. Contents of data dictionary include:

· The user name privileges ,and role of oracle server users.

· The name and definition of schema objects.

· Integrity constraints.

· Stored procedures and database triggers.

Data dictionary also includes information on :

· Space allocation for database objects.

· General database structure.

· Auditing information.

Data dictionary is referenced by oracle server, the database administrator, and users.

Data base administrator query the data dictionary to obtain information about:

· Space usage.

· Fragmentation.

· User access privileges .

The oracle server use the data dictionary to load the definition into SGA, and it uses the data dictionary to obtain space usage information so that table size can be expand when needed. Tables and views in data dictionary are categorized into:

User_XXX, ALL_XXX, and DBA_XXX. All data dictionary tables and views are owned by SYS.

Coding in database application is not the major portion that should be emphasized while creating a database, major emphasize should place on is careful planning and design & backup and recovery procedures.