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_HOME\DATABASE\initu16.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:\DATA\U16\GROUP1\log1a.rdo' SIZE 200K,
             'E:\DATA\U16\GROUP1\log1b.rdo' SIZE 200K
          GROUP 2
             'E:\DATA\U16\GROUP2\log2a.rdo' SIZE 200K,
             'E:\DATA\U16\GROUP2\log2b.rdo' SIZE 200K
          GROUP 3
             'E:\DATA\U16\GROUP3\log3a.rdo' SIZE 200K,
             'E:\DATA\U16\GROUP3\log3b.rdo' SIZE 200K
          DATAFILE
             'E:\DATA\U16\DATAFILES\system01.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:\data\log3a.rdo',		
        'e:\data\log3b.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:\orant\DATABASE\ctl1{SID}orcl.ora, 
                      D:\orant\DATABASE\ctl2{SID}orcl.ora,
                      D:\orant\DATABASE\ctl3{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