Listen Software’s How To: Creating a Database

Creating a Database

Plan Database File Locations

Three control files

Three groups of redo log files, with each group having two members

Create a file structure for the data files

Creating a Database Manually

  1. Decide on a unique instance name and database character set

  2. Set the operating system variables

    (UNIX)
       ORACLE_HOME
       ORACLE_SID
       ORACLE_BASE
       ORA_NLS33
       PATH
    
    (NT)
       ORADIM80 -NEW -SID u16
       -INTPWD password -STARTMODE auto
       -PFILE ORACLE_HOMEDATABASEinitu16.ora
    
       You must decide the SID, Password, and
       Create the parameter file (initu16.ora)
    
       SET ORACLE_SID=u16
       SET LOCAL=2:u16
    
       Statement 1 : Make u16 the current SID
       Statement 2 : Override the LOCAL environment variable
  3. Prepare the parameter file

    use init.ora as a parameter file template

    db_name = Eight characters or fewer that identify the database

    control_files = the location of three control files

    DB_BLOCK_SIZE = Determines the database block size (can not change after the database has been created)

  4. Create a password file

  5. Start the instance

    STARTUP NOMOUNT pfile=initu16.ora
  6. Create the database

    MANUALLY FROM Server Manager

    CREATE DATABASE "U16"
           MAXLOGFILES 6
           MAXLOGMEMBERS 6
           MAXDATAFILES 30
           MAXLOGHISTORY 100
           ARCHIVELOG
    
    LOGFILE
           GROUP 1
              'E:DATAU16GROUP1log1a.rdo' SIZE 200K,
              'E:DATAU16GROUP1log1b.rdo' SIZE 200K
           GROUP 2
              'E:DATAU16GROUP2log2a.rdo' SIZE 200K,
              'E:DATAU16GROUP2log2b.rdo' SIZE 200K
           GROUP 3
              'E:DATAU16GROUP3log3a.rdo' SIZE 200K,
              'E:DATAU16GROUP3log3b.rdo' SIZE 200K
           DATAFILE
              'E:DATAU16DATAFILESsystem01.dbf'
           size 30M
    
           CHARACTER SET WE8ISO8859P1;				
    			
    Database
           select name,created,log_mode from v$database;
    
    Thread
           select status, instance from v$thread;
    
    Datafiles
           select name from v$datafile;
    		
    Logfiles
           select member from v$logfile;
    
    Controlfiles
           select name from v$controlfile;
    
    Verify System Creation
           select file_name from dba_data_files
           where tablespace_name='SYSTEM';
    
    Look at the database users
           select username, created from dba_users;
  7. Run scripts to generate the data dictionary and accomplish postcreation steps.

    Create the data dictionary
       CATALOG.SQL
    
    prostcreation objects
       CATPROC.SQL
  8. Maintaining Redo Log Files

    (Determine Archiving Status)

    select log_mode from v$database;

    (Determine is Automatic Archiving is enabled)

    select archiver from v$instance;

    (if you need to add a new group of redo logs)

    ALTER DATABASE ADD LOGFILE
       (		
         'c:datalog3a.rdo',		
         'e:datalog3b.rdo' 
       ) size 200k;
    
    select * from v$log_file;

Creating a Database using Instance Manager

  1. Start Instance Manager
    Press the New Button
    Input a SID (4 characters) ->abcd

  2. Enter the DBA Authorization password and authenication

  3. Press the Advanced Button

    • The Database Name must be the same as the Database Name in the initabcd.ora file

    • Enter the max logfile value and the max group member value

    • Design the location of your datafiles, logfiles, archive files, and control files

    • Change the logfile location and name to meet your design

    • Set the location of the Parameter file

      Defining Parameters in the Parameter File

      db_name = {myDBName} #database name using to identify the database
      
      db_files = 30          #maximum allowable number of database files
      			
      #control file list (Created by the Instance Manager)
      
      control_files = (D:orantDATABASEctl1{SID}orcl.ora, 
                       D:orantDATABASEctl2{SID}orcl.ora,
                       D:orantDATABASEctl3{SID}orcl.ora)
      
      #database will be compatible with software of this version
      			
      compatible = 7.3.0.0.0
      
      # db_file_multiblock_read_count= 
      # number of database blocks to read with each I/O.
      
      # db_file_multiblock_read_count =  8 # INITIAL
      # db_file_multiblock_read_count = 8 # SMALL
      db_file_multiblock_read_count = 16 # MEDIUM
      # db_file_multiblock_read_count = 32 # LARGE
      
      # db_block_buffers = number of database blocks cached in memory
      
      db_block_buffers tells the oracle kernel the 
      size of the area that stores database read from the disk
      
      # db_block_buffers =  200  # INITIAL
      # db_block_buffers = 200  # SMALL
      db_block_buffers = 550   # MEDIUM
      # db_block_buffers = 3200  # LARGE
      				
      Size in bytes of the shared pool
      
      # shared_pool_size =  6500000  # INITIAL
      # shared_pool_size = 3500000  # SMALL
      shared_pool_size = 6000000   # MEDIUM
      # shared_pool_size = 9000000  # LARGE
      
      Number of redo blocks for checkpoint threshold
      
      log_checkpoint_interval = 10000
      
      Maximum number of user processes
      
      # processes =  50  # INITIAL
      # processes = 50  # SMALL
      processes = 100  # MEDIUM
      # processes = 200  # LARGE
      
      DML locks - one for each table modified in a transaction
      
      # dml_locks =  100  # INITIAL
      # dml_locks = 100  # SMALL
      dml_locks = 200   # MEDIUM
      # dml_locks = 500  # LARGE
      
      Redo circular buffer size
      
      # log_buffer =  8192      # INITIAL
      # log_buffer = 8192      # SMALL
      log_buffer = 32768     # MEDIUM
      # log_buffer = 163840    # LARGE
      	
      Number of sequence cache entries
      	
      # sequence_cache_entries =  10   # INITIAL
      # sequence_cache_entries = 10   # SMALL
      sequence_cache_entries = 30    # MEDIUM
      # sequence_cache_entries = 100  # LARGE
      	
      # sequence_cache_hash_buckets =  10     # INITIAL
      # sequence_cache_hash_buckets = 10     # SMALL
      sequence_cache_hash_buckets = 23      # MEDIUM
      # sequence_cache_hash_buckets = 89     # LARGE
      
      # audit_trail = true            # if you want auditing
      # timed_statistics = true       # if you want timed statistics
      max_dump_file_size = 10240      # limit trace file size to 5 Meg each
      
      Start the Archiver Process
      	
      log_archive_start = true      # if you want automatic archiving
      
      LOG_ARCHIVE_DEST = E:{db_name}ARCHIVE 
      #location of the archive directory
      
      # define directories to store trace and alert files
      
      background_dump_dest=%RDBMS73%trace
      user_dump_dest=%RDBMS73%trace
      			
      Size of database block in bytes. Db_block_size can not 
      be changed after database creation
      
      db_block_size must be a multiple of 512K
      
      db_block_size = 8192
      
      Number of job queue processes to startPassword file usage
      
      remote_login_passwordfile = shared
      				
      Enable text searching
      
      text_enable = true
  4. Create your database

    Once the instance has been started, it is an idle instance.

    Steps to starting the database

    From a DOS Prompt

    set ORACLE_SID=abcd
    set LOCAL =2:abcd
    
    svrmgr23 (NT)
    
    connect internal/password
    
    startup pfile=initabcd.ora

Creating your development environment

  1. Create tablespaces with associated Datafiles

    Consider the following tables and design the location for storage of the datafile

    Determine if the tablespace is permanent or temporary

    • [User] Data tablespace stores data: tables, snapshots, views,
      functions, procedures, and triggers

    • [Index] Data tablespace stores index, primary key constraints

    • [Temporary] Data tablespace stores sort data and is a temporary tablespace type

    • [Rollback] data tablespace stores rollback segments

    • [Work] data tablespace allows the user to create personal tables (helpful for reports)

    See DBA Administrative Tasks

  2. Create Application role

    CREATE ROLE APPLICATION_ROLE NOT IDENTIFIED;
    GRANT ANALYZE ANY, 
          CREATE CLUSTER, 
          CREATE DATABASE LINK,
          CREATE PROCEDURE,
          CREATE ROLE,
          CREATE SESSION,
          ALTER SESSION,
          CREATE SEQUENCE,
          CREATE SNAPSHOT,
          CREATE SYNONYM,
          CREATE ANY SYNONYM,
          DROP ANY SYNONYM,
          CREATE TABLE,
          FORCE TRANSACTION,
          CREATE TRIGGER,
          CREATE VIEW
    TO APPLICATION_ROLE;
  3. Create User role

    CREATE ROLE USER_ROLE NOT IDENTIFIED;
    GRANT CREATE DATABASE LINK,
          CREATE SESSION,
          ALTER SESSION,
          CREATE SNAPSHOT,
          CREATE SYNONYM,
          CREATE TABLE,
          CREATE VIEW
    TO USER_ROLE;
  4. Create table privilege roles (build the schema–assign object privileges latter)

    CREATE ROLE APPL_1_ROLE;
    CREATE ROLE APPL_2_ROLE;
  5. Create application/programmer users

    CREATE USER {application_user}
    IDENTIFIED BY PASSWORD
    DEFAULT TABLESPACE {data tablespace name}
    TEMPORARY TABLESPACE {temporary tablespace name}
    QUOTA UNLIMITED ON {data tablespace name}
    QUOTA UNLIMITED ON {index tablespace name}
    QUOTA UNLIMITED ON {temporary tablespace name};
    		
    GRANT APPLICATION_ROLE TO {application_user};
    GRANT {APPL_1_ROLE} TO {application_user};
  6. Create online users

    CREATE USER {online_user}
    IDENTIFIED BY PASSWORD
    DEFAULT TABLESPACE {data tablespace name}
    TEMPORARY TABLESPACE {temporary tablespace name}
    QUOTA 5M ON {data tablespace name}
    QUOTA UNLIMITED ON {index tablespace name}
    QUOTA 10M ON {temporary tablespace name};
    		
    GRANT {APPL_1_ROLE} TO {online_user};
  7. Create data schema (tables,indexes, primary and foreign contraints, snapshots, views, function, packages, procedures, and synonyms)

    1. Create your tables
    2. Create primary and foreign key constraints
    3. Create indexes
    4. Create Snapshot, Views
    5. Create Functions, Packages, and Stored Procedures
    6. Create Database Links
    7. Create Private and Public Synonyms
  8. Assign object priviledges to the table privilege roles

    GRANT SELECT,UPDATE,DELETE,INSERT ON myTable TO {APPL_1_ROLE};
    GRANT EXECUTE ON myFunction TO {APPL_1_ROLE};

David Nishimoto
For Listen Software

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles