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
-
Decide on a unique instance name and database character set
-
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
-
Prepare the parameter file
use
init.ora
as a parameter file templatedb_name
= Eight characters or fewer that identify the databasecontrol_files
= the location of three control filesDB_BLOCK_SIZE
= Determines the database block size (can not change after the database has been created) -
Create a password file
-
Start the instance
STARTUP NOMOUNT pfile=initu16.ora
-
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;
-
Run scripts to generate the data dictionary and accomplish postcreation steps.
Create the data dictionary CATALOG.SQL prostcreation objects CATPROC.SQL
-
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
-
Start Instance Manager
Press the New Button
Input a SID (4 characters) ->abcd
-
Enter the DBA Authorization password and authenication
-
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
-
-
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
-
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)
-
-
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;
-
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;
-
Create table privilege roles (build the schema–assign object privileges latter)
CREATE ROLE APPL_1_ROLE; CREATE ROLE APPL_2_ROLE;
-
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};
-
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};
-
Create data schema (tables,indexes, primary and foreign contraints, snapshots, views, function, packages, procedures, and synonyms)
- Create your tables
- Create primary and foreign key constraints
- Create indexes
- Create Snapshot, Views
- Create Functions, Packages, and Stored Procedures
- Create Database Links
- Create Private and Public Synonyms
-
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};