Tutorial 3: Creating a Database

This tutorial will cover how to create an Oracle 11g database on Fedora 12. You will need the operating system setup correctly and the Oracle 11g binaries installed before continuing. The previous two tutorials cover how to do that if you haven't already.


Creating a Database

Before proceeding if you are running this from a remote computer instead of on the physical server box then the make sure you have an X client setup as the installer uses graphical windows that need to be displayed on your remote display.

1. First log in as the oracle user and start the database configuration assistant:

	cd /ora/fs2000/s00/oracle/product/11.2.0.1/bin/

	./dbca

This will start a pop up window that says "Welcome Page". Click next. The next page will say "Step 1 of 12". On this page select "Create Database" and click next.

2, The next page will say "Step 2 of 12". On this page select "Custom Database" and click next.

3. The next page will say "Step 3 of 12". Here is where you will enter your database name. In my example I enter CRAIG as my database, maybe yours is DEV01 or something like that. Just make sure it matches the name you have used for the mount points and so forth. After you enter the database name click next.

4. The next page will say "Step 4 of 12". Unclick "Configure Enterprise Manager" and then click next. This is a very useful tool if you are managing many databases but you can just use the single dbconsole that will install with your database which does the same thing for single instances.

5. The next page will say "Step 5 of 12". Here you will enter the passwords for the two privileges users. Note that in 11g the passwords are now case sensitive and require semi-complicated passwords. Once you have created the passwords click next.

6. The next page will say "Step 6 of 12". Just click next on this page.

7. The next page will say "Step 7 of 12". First unclick "Specify Flash Recovery Area". After that click on "Enable Archiving" and then click "Edit Archive Mode Parameters". This will now allow you to setup your archive log format and destination. For the format I like to use the database name concatenated with the unique string options such as follows:

	CRAIG_%t_%s_%r.arc

For the location we will use the archive mount point created earlier. For example:

	/ora/fs2000/archive/CRAIG/

Once these are set click on OK and then click next.

8. The next page will say "Step 8 of 12". On this page unclick all four optional software packages and then click next.

9. The next page will say "Step 9 of 12". Notice this page has four tabs starting you on the memory page. For memory you can set it to start with 512MB. This should be more than enough and you can change all settings later on. On the "Sizing" tab set the processes to 500. And finally on the "Character Sets" tab set the language to Unicode 4.0 (AL32UTF8). Once this is done click next.

10. The next page will say "Step 10 of 12". This is where we setup all the data files the database will use and their locations. First start by clicking on control file and set the database to use three control files such as follows:

	/ora/fs2000/u01/CRAIG/control01.ctl
	/ora/fs2000/u02/CRAIG/control02.ctl
	/ora/fs2000/u03/CRAIG/control03.ctl

Now click on tablespaces. This will list 5 different tablespaces. On each one double click below "File Directory" on the {ORACLE_BASE} and change the location of each to the mount point locations such as:

	/ora/fs2000/u01/CRAIG/system01.dbf
	/ora/fs2000/u02/CRAIG/sysaux01.dbf
	/ora/fs2000/u03/CRAIG/users01.dbf
	/ora/fs2000/u04/CRAIG/temp01.dbf
	/ora/fs2000/u05/CRAIG/undotbs01.dbf

And change the file size for each to:

	system01.dbf - 750MB
	sysaux01.dbf - 750MB
	users01.dbf - 100MB
	temp01.dbf - 1000MB
	undotbs01.dbf - 1000MB

Remember that these can be increased/decreased later on.

Finally for each one click on the storage tab and unclick "Automatically Extend". Once you have change the locations, size, and turned off auto extend for all five files then you can continue to the redo logs.

Open the Redo Log Groups. Here we will want to create three groups of redo logs each with two files such as:

	Group 1 - /ora/fs2000/r01/CRAIG/red01a.log
	Group 1 - /ora/fs2000/r02/CRAIG/red01b.log

	Group 2 - /ora/fs2000/r02/CRAIG/red02a.log
	Group 2 - /ora/fs2000/r03/CRAIG/red02b.log

	Group 3 - /ora/fs2000/r03/CRAIG/red03a.log
	Group 3 - /ora/fs2000/r01/CRAIG/red03b.log

Change the file size of each log to 100MB also. The multiplexing and crossing of mount points will give us maximum protection against loss of a log or a mount point. Once this is done click next.

11. Click on "Finish" and you will now be presented with a summary. Click OK on the summary and it will start creating the database. Depending on your hardware this can take 2 minutes or 2 hours. When it finishes it will automatically start the database for you. There are a couple steps to allow you to connect locally and remotely however.


Final Networking Configuration

Once the installer is completely finished installing the database you can now setup the network configuration for the database.

At the Unix prompt as the oracle user you will need to set the database name that you are working with. To set it as the CRAIG database for example you run the following command and type in your database name:

	. oraenv

	CRAIG

Now you will need to create two files called listener.ora and tnsnames.ora in the following directory:

	/ora/fs2000/s00/oracle/product/11.2.0.1/network/admin/

The tnsnames.ora is a list of all your databases and what servers they are on. For now we have one database so for my setup it looks like the following (modify it for yours by changing the HOST and SID):

	CRAIG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = craigsbox)(PORT = 42000)))
		(CONNECT_DATA = (SID = CRAIG)(SERVER = DEDICATED)))

I set the port to be 4 concatenated with the file system number which is 2000 creating 42000. Important: This port needs to be opened on your firewall also!

The listener.ora file is for setting up the program that listens for connections to your database. Mine looks like the following (modify yours for your database name, host, port, etc.):


	SID_LIST_LISTENER_FS2000 =
	  (SID_LIST =
	    (SID_DESC =
	      (GLOBAL_DBNAME = CRAIG)
	      (ORACLE_HOME = /ora/fs2000/s00/oracle/product/11.2.0.1)
	      (SID_NAME = CRAIG)
	    )
	  )

	LISTENER_FS2000 =
	  (DESCRIPTION_LIST =
	    (DESCRIPTION =
	      (ADDRESS_LIST =
	        (ADDRESS = (PROTOCOL = TCP)(HOST = craigsbox)(PORT = 42000))
	      )
	      (ADDRESS_LIST =
	        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
	      )
	    )
	  )

Now that these two files are created you can start the listener program. Type in the following at the Unix prompt as the oracle user:

	lsncrtl

	start LISTENER_FS2000

	exit

	tnsping CRAIG

If the listener started properly the tnsping command will return some information about your database. If not then it will say it can't resolve that database name.

If you have the oracle client installed on a remote computer you should be able to copy the tnsnames.ora file to the ORACLE_HOME/network/admin directory and run the tnsping command and get database information back.

On the server as the sys user connect to the database and check the instance information to ensure it is online and functioning:

	sqlplus /nolog

	connect / as sysdba

	select * from V$INSTANCE;

If this worked your database is in good condition and ready for use. Proceed to the next tutorial on how to create a schema.

Back to Tutorial Index