Configuring Oracle 11g database

Steps to configure the database

1. We can start configuring the database using dbca command.

Open terminal and go to the following path

If the environment variables are not set then use the below command

cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

If the environment variables are set then use the below command

cd $ORACLE_HOME/bin

Since we have set the environment variables in the bash_profile whenever the user starts the variables will get initialized.

Now type ./dbca in the terminal .

2. Now click create database option . Since there are no databases configured already the other options like delete a database, configure database options are disabled .

3. Now choose the template

Ø General purpose or transaction processing (includes datafiles)

Ø Custom Database

Ø Data Warehouse(includes datafiles)

Since we are dealing with transaction processing and we require datafiles by default we are proceeding with General purpose or transaction processing.

4. Now provide Unique Name and SID to the database

For Global Database Name and SID we can provide different names, but it is recommended to provide same name for both to avoid confusion.

5. Provide password for the default users like SYS, SYSMAN etc.

We have two options

Ø Provide different passwords to the default users.

Ø Provide common passwords to the default users.

Note: If the users are provided with different passwords , remember them . If the users are provided with common passwords be careful that there is a security constraint.

6. While providing the password it is recommended to use Oracle standards ( strong password )

Ø Minimum of 8 characters

Ø At least one upper case character, one lower case character and one digit required.

Still want to continue, proceed by clicking yes .

7. Provide the storage type and locations for datafiles.

Choose the type of storage

Ø File System –for manually configuring the location of database files

Ø Automatic Storage Management – for automatically configuring database files which will be taken care by Oracle Server.

Ø We are going to see how to configure files manually. So choose File System from the drop down menu for Storage Type.

Ø If a template has been created already for file management, then choose the option Use Database File Locations from Template.

8. Now click Browse button to select the location for datafiles

Directly we can type the path for creating the datafiles. If the path is not found the configuration procedure will create one.

Ø If the storage should be taken care by Oracle Server then choose Automatic Storage Management.

Ø Choose Use Oracle- Managed Files if Automatic Storage Management is chosen.

Ø Click Browse for selecting the path where the Oracle Server has to store the database files or enter the path directly in the field (if it is not there configure will create it).

Ø Click Multiplex Redo logs and Control Files to enter the multiple paths where the replica of the database files will be placed. (It is recommended to provide at least two different locations so that it would help in the time of database crash or corrupted database files.)

9. Recovery Configuration

This step involves two options

Ø Flash Recovery area

Ø Archiving

Both helps in time of database failure or crash and helps in recovering the database.

Ø Select the option Enable Archiving ,for enabling archiving

Ø Click Edit Archive Mode Parameters and provide the archive log destinations.

Ø It is recommended to have more than one destination so that we can still have the data in the event of crash.

10. Database content

This step consists of two tabs

Ø Sample schemas

Ø Custom scripts

The schemas like HR,SCOTT etc. will be created if the sample schemas option is selected.

We can run custom scripts to create any schemas in the custom scripts tab.

Ø Click Run the following scripts if you have any scripts to run by adding the scripts using Add button.

Ø Click Next to continue .

11. Initialization parameters

a. Memory:

Ø The main part of installing a database is the memory distribution

Ø Typical : (For newbies)

Specify the memory size for both SGA and PGA and check on Use Automatic Memory Management so that Oracle server will take care of the memory requirements required by the individual components like PGA, SGA etc.

Ø Custom : (For experts)

Specify the size for the components SGA, PGA separately.

Ø Check or tick the Use Automatic Memory Management by which oracle server will manage the memory requirements with the memory size provided.

Ø Click Show Memory distribution to see the memory distributed among server components.

Ø If Automatic Memory Management is not needed, uncheck the option and click Show Memory Distribution.

Ø Select Custom and provide the memory for each components (For experts)

b. Sizing

Mention the number of user processes (User process and Oracle background process) that can simultaneously connected to this database

c. Character Set

Specify the character set which database has to display. Choose as required.

d. Connection Mode

Ø Dedicate Server Mode

Ø Shared Server Mode

12. Database Storage

a. Control files:

Please add the number of control files as required and place it in different locations as a part of backing up the server when there is a crash.

Note : If the control file is corrupted or crashed the oracle database server wont startup . So it is recommended to have at least two control files in different locations.

b. Data Files

These files will have the actual data that we are inserting into the database. Specify the path where they should be placed.

c. Redo logs

Ø These files are as important as control files , because they hold information about the changes that have done to the data.

Ø It is recommended to have atleast two redo groups and at least one redo log file in each group ,so that the data won’t get lost or corrupted.

Note: By default all the files mentioned above will be placed under the path which we have selected in step 8 in the section Steps to configure the database. If required change the path .

13. Creation options

Ø Create Database –To create the database with all the data options till now we have done.

Ø Save as a Database Template –To Save the options so that we can configure the database with the same options we have done already. It will save time a lot.

Ø Generate Database Creation Scripts – To create the database creation scripts so that it can be used to configure the same template from backend.

14. Click on Finish.

Ø Click save as an HTML file to save the summary of all the options that we have done till now. It will be handy when we need to check for some options that we have used to configure the database like the path of the control files, redo files etc.