Step 3: Create the initialisation parameter file
Have a look at the sample initialisation parameter file and edit it accordingly.
Store the initialisation parameter file in the Oracle’s default location.
Table 1.1 Default parameter file locations
Platform Default Name Default Location
Unix Init$ORACLE_SID.ora $ORACLE_HOME/dbs
NT Init$ORACLE_SID.ora $ORACLE_HOME/database
The initialisation parameter file initmynewdb.ora is stored in the following location:
/home/oracle/product/9.0.1/dbs/initmynewdb.ora
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# Miscellaneous COMPATIBLE=9.0.0 DB_NAME=mynewdb
# Distributed, Replication and Snapshot DB_DOMAIN=til.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
# Network Registration
INSTANCE_NAME=mynewdb
# Pools
JAVA_POOL_SIZE=31457280 LARGE_POOL_SIZE=1048576 SHARED_POOL_SIZE=52428800
# Processes and Sessions PROCESSES=150
# Redo Log and Recovery
FAST_START_MTTR_TARGET=300
# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN
# Sort, Hash Joins, Bitmap Indexes SORT_AREA_SIZE=524288
# System Managed Undo and Rollback Segments UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undotbs Step4: Connect to the instance
Start SQL*Plus and connect to your Oracle instance AS SYSDBA.
$ SQLPLUS /nolog
CONNECT SYS /MANAGER AS SYSDBA
[oracle9i@esdlnx dbs]$ sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Tue Oct 16 16:13:26 2001 (c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys/manage as sysdba Connected.
SQL>
Step5: Start the Instance
Start the instance without mounting the database. This is done only during database creation or while performing maintenance operations.
Use the Startup command without NOMOUNT option
STARTUP NOMOUNT
At this point, only instance is created and there is no database. Only SGA is created and background processes are started in preparation for the creation of new database.
Step6: Issue the Create database statement
To create new database use CREATE DATABASE statement. This statement does the following:
• Creates the data files for the database
• Creates the control files for the database
• Creates the redo log files for the database and establishes the ARCHIVELOG mode.
• Creates the SYSTEM table space and the SYSTEM rollback segment
• Creates the data dictionary
• Sets the character set that stores data in the database
• Sets the database time zone
• Mounts and opens the database for use
The following statement creates database mynewdb:
CREATE DATABASE mynewdb SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/home1/oracle9i/mynewdb/temps1.dbf'
SIZE 50M REUSE AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITED CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/home1/oracle9i/mynewdb/redo01.log') SIZE 100M, GROUP 2 ('/home1/oracle9i/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/home1/oracle9i/mynewdb/redo03.log') SIZE 100M;
A database is created with the following characteristics:
• The database is named mynewdb
• Three control files are created as specified by CONTROL_FILES initialisation parameter
• MAXINSTANCES specified that only one instance can have this database mounted and open.
• MAXDATAFILES specifies the maximum number of data files that can be open in the database.
• The SYSTEM table space, consisting of the operating system file /home1/oracle9i/mynewdb/system01.dbf is created as specified by the DATAFILE clause. If the file already exists, it is overwritten.
• The UNDO TABLE SPACE clause creates and names an undo table space to be used for undo records for this database.
• The DEFAULT_TEMPORARY_TABLESPACE clause creates a temporary table space.
• The US7ASCII character set is used to store data in this database.
• The AL16UTF16 character set is specified as the NATIONAL CHARACRTER SET used to store data in columns of specifically defined as NCHAR, NCLOB, or NVARCHAR2.
• The new database has three online redo log files as specified in the LOGFILE clause.
MAXLOGHISTORY, MAXLOGFILES, and MAXLOGMEMBERS define limits for the redo log.
• As ARCHIVELOG clause is not specified in this CREATE DATABASE statement, redo log files will not initially be archived.
• The default database time zone is the same as the operating system’s time zone.
• You set the database’s default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted (as it is in this case), the default database time zone is the operating system time zone.
Step7: Create Additional table spaces
To make the database functional, you need to create additional files and table spaces for users. The following sample script creates some additional table spaces:
CONNECT SYS/ password AS SYSDBA
-- create a user table space to be assigned as the default table space for users CREATE TABLESPACE users LOGGING
DATAFILE '/home1/oracle9i/mynewdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
-- Create a table space for indexes, separate from user table space CREATE TABLESPACE indx LOGGING
DATAFILE '/home1/oracle9i/mynewdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
Step8: Run scripts to create Data Dictionary Views:
Run the scripts necessary to build views, synonyms, and PL/SQL packages:
CONNECT SYS/ password AS SYSDBA
@/home/oracle/product/9.0.1/rdbms/admin/catalog.sql;
@/home/oracle/product/9.0.1/rdbms/admin/catproc.sql;
EXIT
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic Performance views, and public synonyms for many of the views.
Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
Step9: Create Server parameter file:
CONNECT SYS/ password AS SYSDBA -- Create the server parameter file
CREATE SPFILE=' /home/oracle/product/9.0.1/spfilemynewdb.ora' FROM PFILE='/home/oracle/product/9.0.1/initmynewdb.ora';
SHUTDOWN
This time you will start up using the server parameter file CONNECT SYS/ password AS SYSDBA
STARTUP
Step10: Back up the database Take the back up of the database.
1.12 Create a database using Oracle Database Configuration Assistant
The Oracle Database Configuration Assistant (DBCA) is a graphical user interface (GUI) tool that interacts with the Oracle Universal Installer, or can be used
Standalone, to simplify the creation of a database.
Using Oracle Database Configuration Assistant you can add or delete a database. The Oracle Database Configuration Assistant can be used to create single instance databases, or it can be used to create or add instances in an Oracle Real Application Clusters environment.
1.12.1 Advantages of using Oracle Database Configuration Assistant:
• Its wizards guide you through a selection of options providing an easy means of creating and tailoring your database.
• It builds efficient and effective databases that take advantage of Oracle’s new features.
• It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files, including initialisation files, follow standard naming and placement practices.
1.12.2 Creating a database
A database can be created from predefined templates provided by Oracle or from templates that you or others have created. When you select a template, you can choose either to include data files or not. If you select a template with data files, you
will be able to save the database creation information as a template or script. You can run the script later to create a new database.
Oracle provides templates for the following options:
DSS: Users perform numerous, complex queries that process large volumes of data.
Response time, accuracy, and availability are key issues.
OLTP: Many concurrent users performing numerous transactions requiring rapid access to data. Availability, speed, concurrence, and recoverability are key issues.
New Database: This template allows you maximum flexibility in defining a database.
1.12.3 Global Database name and Parameters
You are guided thorough a series of pages that allow you to further define your database or to accept default parameter values and file locations as recommended by Oracle. You provide a global database name, specify database options to include, determine mode (dedicated server of shared server), and ultimately you can specify initialisation parameter.
Typical: This creates a database with minimal user input. You do not specify specific initialisation parameter values; instead, you specify the maximum number of concurrent users, the percentage of physical memory reserved for Oracle, and a database type (OLTP, Multipurpose or Data Warehousing).
Custom: Custom allows you to specify initialisation parameter values that affect the size of the System Global Area (SGA). Very experienced database administrators who have specific tuning needs can use it
1.12.4 Completing Database Creation
After you have completed the specification of the parameters that define your database you can:
• Create the database
• Save the description as database template
• Generate database creation scripts.
1.12.5 Deleting a database
The Oracle Database Configuration Assistant enables you to delete a database.
When you do so, you delete the database instance and its control file(s), redo log files, and data files. The initialisation parameter file is not deleted.
1.13 Monitor the use of Diagnostic Files
Trace Files: Background processes always write to a trace file when appropriate. In the case of the ARCn background process, it is possible, through an initialisation parameter, to control the amount and type of trace information that is produced.
Trace files are written on behalf of server processes whenever internal errors occur.
Additionally, setting the initialisation parameter SQL_TRACE = TRUE causes the SQL trace facility to generate performance statistics for the processing of all SQL statements for an instance and write them to the USER_DUMP_DEST directory.
Optionally, trace files can be generated for server processes at user request. Regardless of the current value of the SQL_TRACE initialisation parameter, each session can enable or
disable trace logging on behalf of the associated server process by using the SQL statement ALTER SESSION SET SQL_TRACE. This example enables the SQL trace facility for a specific session: