×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Foundation

3. Oracle 10g Create Database (UNIX) by Michael42
Posted: 17 Oct 06 (Edited 19 Oct 06)

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:
  1. Using the DBCA create the core database components (control files, common tablespaces, redo log groups, archive logs etc.)
  2. Using Enterprise Manager (or via script) add any custom tablespaces and settings.
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
  1. Decide on a database SID. For this illustration DB1 is used.
  2. 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.
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

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close