3. Oracle 10g Create Database (UNIX)

Overview
I highly recommend using the Oracle Database Configuration Assistant (DBCA). This will help you to ensure that your database is created correctly the first time.

The DBCA is reliable and does not take an extreme Oracle skill set to implement. Things that are simple can be duplicated more easily than things that are complex, and Oracle has done a great job of making the creation of databases simple with the DBCA.

Continuing on this theme, create your databases using the most concise set of options possible, unless you have a business requirement to do otherwise. Do not configure your database to support Advanced Security, Spatial, Ultra Text Searching, XML and the like, unless they are actually going to be used. It is easy enough to install these later.

It is a good practice to create your database in two main phases:
[ol]
[li]Using the DBCA create the core database components (control files, common tablespaces, redo log groups, archive logs etc.)[/li]
[li]Using Enterprise Manager (or via script) add any custom tablespaces and settings.[/li]
[/ol]

The database build time will be faster, and any issues with your settings will be more quickly flushed out. You will have a solid database foundation that can be built upon reliably.

Prerequisites
[ol]
[li]Decide on a database SID. For this illustration DB1 is used.[/li]

[li]Identify the directory(s) where you want to store your data files and set the proper UNIX permissions. Concerning the Oracle data directories, the OFA has changed in 10g. In it's base form, all recovery related files go to one area (/recovery_area) making the implementation of various Oracle tools from Flashback database to Data Guard easier.

10g OFA for database files:
/oradata/<SID>/dbf Tablespace datafiles.
/oradata/<SID>/recovery1 Archive, Control, Flashback and Redo files.
/oradata/<SID>/recovery2 Multiplexed: Archive, Control and Redo files.
/oradata/<SID>/rman RMAN backup files.
/oradata/<SID>/exports Export\DataPump files.
[/li]
[/ol]

Procedure

1. Login as the oracle user account.
2. Set your DISPLAY environment variable.
Example:
setenv DISPLAY myhostname:0
3. Run $ORACLE_HOME/bin/dbca &
Enter the displayed values as indicated changing as required in your environment.
4. Operations
(x) Create a Database
5. Database Templates
(x) Custom Database
6. Database Identification
Global Database name: DB1
SID: DB1
7. Management Options
[x] Configure Database with Enterprise Manager
(x) Use Database Control for Database Management
[ ] Enable Daily Backup <=== Do not enable this here because the defaults are not practical.
8. Database Credentials
Use the same password for all accounts.
9. Storage Options
(x) File System
10. Database File Locations
(x) Use Database File Locations from Template.
11. Recovery Configuration
Specify Flash Recovery Area
/oradata/{DB_NAME}/recovery1
Size: 2048
[x] Enable Archiving

Automatic Archiving
Archive Log File Format: {DB_NAME}_%T_%S_%r.arc
Archive Log Destination 1 /oradata/{DB_NAME}/recovery1
Archive Log Destination 2 /oradata/{DB_NAME}/recovery2

12. Database Content
Disable all except:
Enterprise Manager Repository [SYSAUX]
Standard Database Components
-> Just enable [x] Oracle JVM
13. Initialization Parameters
(x) Custom
14. Memory (small | medium-large)
Shared Memory Management: Automatic
SGA: Size: 300 | 300-512 mb
PGA Size: 92 | 128 mb
15. Sizing
Blocking Size: 8192 Bytes (Match this to your OS block size.)
Processes: 150
16. Character Sets
Use the default (ex: WE8ISO8859P1) or ALT32UTF8
National Character Set: AL16UTF16 (default)
17. Connection Server Mode
(x) Dedicated Server Mode
18. Database Storage
For Control, Redo and Archive files create as shown.
If not on SAN/RAID multiplex over JBOD (Just a Bunch of Disks).

Controlfile
Location
/oradata/{DB_NAME}/dbf
/oradata/{DB_NAME}/recovery1
/oradata/{DB_NAME}/recovery2

Tablespaces (Always use Locally Managed Tablespaces.)
Location Size (small | medium-large)
/oradata/{DB_NAME}/dbf/sysaux01.dbf 300 mb
/oradata/{DB_NAME}/dbf/system01.dbf 600 mb
/oradata/{DB_NAME}/dbf/temp01.dbf 250 mb | 500 mb
/oradata/{DB_NAME}/dbf/undotbs01.dbf 250 mb | 500 mb
/oradata/{DB_NAME}/dbf/users01.dbf 25 mb

¦ Import operations and routine Oracle patches may require more capacity for TEMP and UNDO tablespaces than the standard data load. Size these accordingly.
¦ Disk space permitting, enable auto-extend for the TEMP and user tablespaces.
¦ Enable auto-extend on the SYSAUX tablespace and set the maximum size to 600 mb. This is sufficient for small databases and will adequately scale for most databases. For large databases or databases where you determine you need to retain statistics for more than the default (7 days) this could reach 3gb. Adjust accordingly.

Redo Log Groups (4 groups with two members each)
File Name Location Size
redo1a.log /oradata/{DB_NAME}/recovery1/ 100 mb
redo2a.log /oradata/{DB_NAME}/recovery1/ 100 mb
redo3a.log /oradata/{DB_NAME}/recovery1/ 100 mb
redo4a.log /oradata/{DB_NAME}/recovery1/ 100 mb

redo1b.log /oradata/{DB_NAME}/recovery2/ 100 mb
redo2b.log /oradata/{DB_NAME}/recovery2/ 100 mb
redo3b.log /oradata/{DB_NAME}/recovery2/ 100 mb
redo4b.log /oradata/{DB_NAME}/recovery2/ 100 mb

The goal here is to have a log switch about every 20-30 minutes. Use the above values if you are not sure then resize them after analyzing the production load.

19. Creation Options
Create Database
Save as a Database Template (if this is the first database on system.)
Name: Standard