Sr SQL DBA The Computer Merchant, Ltd US-VA-Reston Post A Job | Post A Resume

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

	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

	Step 1. Start Instance Manager
			Press the New Button

		  Input a SID (4 characters) ->abcd

	Step 2. Enter the DBA Authorization password and authenication

	Step 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 start

			snapshot_refresh_processes = 1

			Password file usage

			remote_login_passwordfile = shared
				
			Enable text searching

			text_enable = true


        Step 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 

	Step 1.  Create tablespaces with associated Datafiles

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

		Determine if the tablespace is permenant 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


	Step 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;

	Step 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;

	Step 4. Create table privilege roles 
		(build the schema - assign object privileges latter)

		CREATE ROLE APPL_1_ROLE;
		CREATE ROLE APPL_2_ROLE;

	Step 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};


	Step 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};

	Step 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

	Step 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

Back to the LSS "How To" Series Main Page