module5-create_database.htm; updated May 26, 2013; Some figures shown in these notes are from Oracle document D11321GC11, Production 1.1 D34286; Oracle® Database Administrator's Guide 11g Release 2 (11.2) .E25494-03

 

Module 5 – Create a Database

 

Objectives

 

v Learn the Optimal Flexible Architecture (OFA) for database planning. 

v Create an Oracle database manually. 

v Familiarize with the Oracle Database Configuration Assistant.

v Familiarize with creating an Oracle database using Oracle Managed Files.

 

Database Planning

 

Database planning is based on your understanding of the purpose and type of database needed.  Many organizations have multiple databases that serve different purposes such as online-transaction-processing (OLTP), data warehousing, decision support systems, and general purpose usage.

 

Database planning also requires you to outline the database architecture in terms of:

·        How many data files will be needed? 

o   Let's assume we will start with a basic, general purpose usage database.

o   We will need database files for the different tablespaces:

1.   SYSTEM: data dictionary.

2.   SYSAUX: tables and objects for various Oracle software products.

3.   UNDOTBS: undo tablespace for undo recovery.

4.   TEMP: storage for temporary objects, such as sorting.

5.   DATA: permanent organizational data objects, e.g., Orders, Customers, Products, etc.

6.   INDEXES: permanent indexes for tables stored in the DATA tablespace.

7.   USERS: storage for user generated objects that are not part of the organizational data.

8.   Any special usage tablespaces, such as dedicated tablespaces for large applications.

·        Control file location? 

o   To maximize recoverability, 2 or more copies of the control file are needed.

o   Any disk drive will do.

·        Redo Log file location and usage?

o   To maximize recoverability, 2 or more groups with multiple redo log files per group are needed.

o    Select a disk drive to minimize I/O contention.

·        What size system global area will the database need?

o   Start out with about 1 G. 

o   Let Oracle optimize the memory allocation for the various caches.

·        Is database archiving necessary?

o   Production systems - archiving is essential.

o   Archiving requires storage capacity for archive redo log files and backups of the database.

o   You must monitor the disk storage usage.

·        How many disk drives are available?

o   For SOBORA2, you have 3 disks drives available.

o   Additional disk drives cost $$$ - the purchase of more space would need to be justified.

·        How many instances and/or databases will run? This is situational dependent.  Your student database will be a single instance.

·        Will the database be distributed? Again, this is situational dependent.

 

 

Optimal Flexible Architecture (OFA)

 

Oracle’s method for the physical layout of an Oracle database’s file structure is termed the OFA. 

·        This approach requires the allocation Oracle database files across several different physical disk devices in order to provide good system performance for data retrieval/storage. 

·        The OFA is most applicable when the database server has more than one physical disk drive although the OFA naming convention is applicable to any database server.

 

The objective of the OFA approach is to make it easy to administer a growing database where you need to add data, add users, create new databases or tablespaces, add hardware, and distribute the input-output load across disk drives.

 

This gives the steps to implementing an OFA.

 

Step 1.  Establish an Operating System Directory Structure 

 

An orderly operating system directory structure is one in which any database file can be stored on any disk drive resource.  This is accomplished by creating the ORACLE_BASE environment variable. 

·        The ORACLE_BASE is the base subdirectory for Oracle.

·        On SOBORA2, ORACLE_BASE=/u01/app/oracle

·        Depending on the installation, you may or may not find that this variable has been created for any given database. 

·        Under $ORACLE_BASE are additional subdirectories where Oracle objects are stored following a standard approach.  These may include:

 

 

dbock/@sobora2.isg.siue.edu=>ls -al

total 24

drwxr-xr-x   6 oracle dba 4096 May 14 23:19 .

drwxr-xr-x   5 oracle dba 4096 Jun 16  2009 ..

drwxr-xr-x   2 oracle dba 4096 May 14 11:26 checkpoints

drwxrwxr-x  11 oracle dba 4096 May 14 10:44 diag

drwxrwxr-x   2 oracle dba 4096 May 14 23:19 fast_recovery_area

drwxr-xr-x   4 oracle dba 4096 May 14 10:41 product

 

·        These directories are created to store files associated with the Oracle RDBMS software – not with a specific database.

·        PRODUCT – This subdirectory stores the Oracle kernel and other files that make up the Oracle Relational DBMS product. 

o   Under the PRODUCT subdirectory you will find a subdirectory for each version of Oracle that is installed. 

o   Examine this to determine the version number for our current version of Oracle. 

o   This approach to subdirectory structuring allows the installation of newer versions of Oracle without taking older versions off-line until all testing and changeover requirements are completed for the newer version of Oracle.

 

Step 2.  Identify Available Disk Drives and Establish the Oradata Subdirectory

 

The DBA will identify the available disk drives for use in creating a database. 

·        Examine the SOBORA2 Server at SIUE to determine how many disk drives available.

·        There are three available. 

·        Space allocated for your work is on directories like this.  Replace dbockstd (the dbock student account) with your own SIUE EID value.

 

/u01/student/dbockstd

/u02/student/dbockstd

/u03/student/dbockstd

 

Typically, a DBA will create a subdirectory named oradata (as noted above) on each disk drive – this subdirectory on each drive will store the files that comprise the database.   

 

Spreading the database files across multiple disk drives reduces the contention that will exist between the datafiles for simultaneous use of input-output paths.

 

Step 3.  Create Different Groups of File Objects

 

This step involves the creation of separate Tablespaces to store objects.

·        Initially you will create a database with four tablespaces: SYSTEM, SYSAUX, UNDO, and TEMP. 

·        You will add additional tablespaces as needed. This also allows the DBA to separate and more easily manage object groups as Tablespaces.  

·        Examples of additional database tablespaces include USERS, DATA, INDEXES, BIGFILE, and others.

 

Some rules that are recommended include:

·        Separate groups of objects with different fragmentation characteristics in different tablespaces; for example, avoid storing data and undo segments together because their fragmentation characteristics are different

·        Separate groups of segments that will contend for disk resources in different tablespaces; for example, avoid storing data tables and their associated indexes together because Oracle would like to write both of these simultaneously as data rows are inserted, deleted, or updated, so are the associated indexes.

·        Separate groups of segments that have different behavioral characteristics in different tablespaces; for example, avoid storing database objects that need daily backup in tablespaces with objects that only need yearly backup.

·        Create a separate tablespace for each large application – smaller applications can usually share a tablespace such as DATA without many contention problems.

·        Store static segments separately from high use dynamic segments, such as those used for Data Manipulation Language (DML).

·        Store staging tables for a data warehouse in their own tablespace.

·        Store materialized views in a separate tablespace from the tablespace used to store the base tables on which the materialized views are built.

·        If tables/indexes are partitioned, store each partition in its own tablespace.

 

Step 4.  Maximize Database Reliability

 

In order for a database to startup, a database must have a control file that is valid.

·        The control file is not corrupted and is up to date. 

·        Because this file is critical, it is common to keep at least three active copies of the database control file on three different physical disk drives.  Thus, if a disk drive crashes, a good copy of the control file is available.

·        You will start out with two copies of the control file for your student database. Later you will add more.

 

A database should have at least three database redo log groups.

·        Although only two log files are required at a minimum, this is generally not adequate.

·        Database reliability is maximized by isolating the groups to the extent possible on disk drives serving few or no files that will be active while the database is in use.

·        You will start out with two groups with one redo log file per group. Later you will add more.

 

You should try to store tablespaces whose data participate in disk resource contention on separate disk drives.

 

A Minimum OFA Recommended Disk Configuration

 

This is the minimum configuration recommended by Oracle requires five disk drives.

 

·        DISK1: Oracle Executables, SYSTEM Tablespace datafiles, SYSAUX Tablespace datafiles, USERS Tablespace datafiles, and 1st copy of the control file.

·        DISK2: DATA Tablespace datafiles, 2nd copy of the control file, Redo Log Files.

·        DISK3: INDEXES Tablespace datafiles, 3rd copy of the control file, Redo Log Files.

·        DISK4: UNDO Tablespace datafiles, TEMP Tablespace datafile, and any export files.

·        DISK5: Archive Redo Log files, Redo Log Files.

    

Ø This configuration is NOT the most desirable configuration because having the Redo Logs and Archive Redo Log files on the same disk drive will cause some contention problems. 

Ø It is also not desirable to have SYSTEM and USERS tablespaces on the same disk drive.

 

Other Acceptable Configurations

 

Obviously everything is not always optimal.  A fairly acceptable configuration is the 3-disk drive configuration because you can still separate the location of the control files.

 

·        DISK1: Oracle Executables, SYSTEM Tablespace datafiles, SYSAUX Tablespace datafiles, Redo Log Files, UNDO Tablespace datafiles, any export files, 1st copy of the control file.

·        DISK2: DATA Tablespace datafiles, USERS Tablespace datafiles, TEMP Tablespace datafiles, 2nd copy of the control file, Redo Log Files.

·        DISK3: Archive Redo Log Files, Redo Log Files, INDEXES Tablespace datafiles, 3rd copy of the control file.

 

 

OFA File Naming Standard

 

You need to have a standard file naming convention – this will make it easier to do backups and to find files. 

 

Mount point—This is the top point in the physical disk file structure.  The recommended naming format is:

 

<string constant><numeric value>

 

Ø where <string constant> can be one or more letters and <numeric value> is a two or three digit value. 

Ø Typical UNIX and LINUX examples for naming the disk drives:  /u01 /u02 /u03   or   /a01  /a02  /a03. 

 

Software executables—each version of Oracle that is installed (e.g., 11g, 10g, and incremental versions) should reside in separate directories with a naming format of:

 

<string constant><numeric value>/<directory type>/<product owner>

 

Ø where <directory type> implies the type of file in the directory and <product owner> is the name of the user that owns/installs the directory files.

 

Ø Example:  /u01/app/oracle would contain application-related files (app) installed by the user named oracle.

 

Ø This lists the different versions of the Oracle RDBMS on SOBORA2.

 

dbock/@sobora2.isg.siue.edu=>ls -al

drwxr-xr-x  3 oracle dba 4096 May 15  2009 10.2.0

drwxr-xr-x  3 oracle dba 4096 May 14 10:41 11.2.0

 

Database files—each set of database files belonging to a single database should reside in separate directories with the naming format of:

 

<string constant><numeric value>/<oradata>/<database name>

 

Ø Where <oradata> is a fixed directory name and <database name> is the value of the DB_Name initialization parameter.

Ø Example:  /u02/oradata/DBORCL

 

Tablespace file names—Internal Oracle tablespace names can be up to 30 characters in length. 

·        In a UNIX/LINUX environment it is advisable to limit the names of the files that store tablespaces to eight characters or less because portable UNIX/LINUX filenames are limited to 14 characters to facilitate copying. 

·        Use a suffix of <n>.dbf where the value of <n> is two digits – thus the suffix requires six characters leaving eight characters to name the tablespace.

·        Only datafiles, control files, and redo log files should be stored in the <database name> directory.

·        This is the organization of the DBORCL database:

 

ORGANIZATION OF THE DBORCL DATABASE

 

Tablespaces: 

Tablespace                      Size (MB)  Free (MB)     % Free     % Used

------------------------------ ---------- ---------- ---------- ----------

UNDOTBS1                              435   423.6875         97          3

USERS                                22.5     19.375         86         14

SYSAUX                                340    10.1875          3         97

SYSTEM                                480      6.625          1         99

TEMP                                   28          0          0        100

 

select file_name, tablespace_name from dba_data_files;

 

FILE_NAME                                TABLESPACE

---------------------------------------- ----------

/u02/oradata/DBORCL/DBORCLusers01.dbf    USERS

/u01/oradata/DBORCL/DBORCLsysaux01.dbf   SYSAUX

/u03/oradata/DBORCL/DBORCLundotbs01.dbf  UNDOTBS1

/u01/oradata/DBORCL/DBORCLsystem01.dbf   SYSTEM

 

select file_name, tablespace_name from dba_temp_files;

 

FILE_NAME                                TABLESPACE

---------------------------------------- ----------

/u03/oradata/DBORCL/DBORCLtemp01.dbf     TEMP

 

Data Files/Redo Log Files/Control Files

/u01

DBORCLsystem01.dbf; DBORCLsysaux01.dbf

DBORCLredo01a.rdo; DBORCLredo02a.rdo; DBORCLredo03a.rdo

DBORCLcontrol01.ctl

/u02

DBORCLusers01.dbf

DBORCLredo01b.rdo; DBORCLredo02b.rdo; DBORCLredo03b.rdo

DBORCLcontrol02.ctl

/u03

DBORCLtemp01.dbf; DBORCLundotbs01.dbf

DBORCLcontrol03.ctl

 

 

 

Database Creation

 

Prerequisites

 

In order to create a new database, you must have a privileged account that is authenticated by either the operating system or the use of a special Oracle database password file. 

·        We accomplish this at SIUE by assigning your account to the LINUX group named dba. 

·        This means that you have SYSDBA privileges. 

 

You must ensure that the memory available for the SGA, Oracle executables, and background processes is sufficient. 

 

You must calculate the necessary disk space for the database. 

·        This is a fairly complex set of calculations if you follow Oracle's method for calculating disk space. 

·        Alternatively, you can create a simple database and estimate disk space for the production database from the simple database.

 

Authentication Methods for DBAs

 

If the database to be administered is local – this means that you will sit at the terminal where the database resides – then you can use local database administration authentication by the operating system.  You can also do this if you have a secure connection.  Alternatively, as is shown in the figure below, you can use a password file.

 

 

A password file is created by using the password utility named orapwd.  When you connect using SYSDBA privilege, you connect to the SYS schema.  The steps to using password file authentication are given here.

 

NOTE: AS A STUDENT YOU DO NOT NEED A PASSWORD FILE—DO NOT CREATE ONE.

 

1. Create the password file using the password utility orapwd.  Example:

 

orapwd file=filename password=password entries=max_users

 

where:

 

·        filename: Name of the password file (mandatory parameter).  You select a filename, typically orapwSID.

·        password: The password for SYSOPER and SYSDBA (mandatory parameter)

·        entries: The maximum number of distinct users allowed to connect as SYSDBA or SYSOPER.  You must create a new password file if the number of DBAs exceeds the number of entries set when the password file was created – delete the file and create a new one, then reauthenticate users.  Note: There are no spaces around the equal-to (=) character.

 

Example:

 

orapwd file=$ORACLE_HOME/dbs/orapwUSER350 password=admin entries=25

 

2. Set the REMOTE_LOGIN_PASSWORDFILE parameter in the PFILE to a value of EXCLUSIVE.  

·        EXCLUSIVE means only one instance can use the password file and that the password file contains names other than SYS. 

·        Using an EXCLUSIVE password file you can grant SYSDBA or SYSOPER privileges to individual users.

 

3. Connect to the database using the password file created above.

 

CONNECT sys/admin AS SYSDBA

 

4.  Assign privileges to each user that will function as a DBA.

 

GRANT SYSDBA TO USER350;

 

Password files should be stored on UNIX/LINUX and Windows server computers at the following directory locations: 

·        UNIX and LINUX: $ORACLE_HOME/dbs

·        Windows: %ORACLE_HOME%/database

 

 

Creating a Database

 

There are three ways to create an Oracle database.

1.   Oracle Universal Installer – During installation of the Oracle software, this product provides you options for creating several different types of databases, and these can be modified later to meet OFA-compliance guidelines.

2.   Oracle Database Configuration Assistant (DBCA) – This product provides several options for creating different database types, and it also allows you to upgrade an existing database.

3.   Manual Database Creation – This approach used the CREATE DATABASE command  and is the approach we will take because it teaches you much more about the database creation process.  Usually you do this by creating an SQL script and then executing the script.

 

You must select a database name.  At SIUE, you will name your database to match your Oracle user account name assigned in the course, for example, USER350.

 

Manual Database Creation

 

Steps to Create a Database

 

This outlined the steps to creating a useable database.

1.   Set the operating system environment variables ORACLE_HOME, ORACLE_SID, PATH, and LD_LIBRARY_PATH.

2.   Create directories to store your database files.

3.   Create an initSID.ora parameter file (PFILE) and store it to your $HOME directory.

4.   Create the Database. 

·        Use SQLPlus and connect as SYS AS SYSDBA. 

·        Create a SPFILE from the PFILE (NOTE: the step that creates the SPFILE is optional—you will use just a PFILE for your databases -- you will not create a SPFILE).

·        Connect as SYS AS SYSDBA and execute the CREATE DATABASE command in SQLPlus.

5.   Run the required catalog.sql and catproc.sql scripts to create the data dictionary.  Run the pupbld.sql script to create the product user profile.

6.   Create additional tablespaces such as a USERS tablespace for user data and any other tablespaces that may be required to meet the needs of the database.

 

1.  Setting the Environment Variables

 

Before a database is created, the operating system environment must be configured and the Oracle RDBMS server software must have already been installed.  At a minimum, five environment variables must be set:

 

·        ORACLE_HOME

·        ORACLE_BASE

·        ORACLE_SID,

·        PATH

·        LD_LIBRARY_PATH. 

 

Some of these variables are already be set for your student accounts on the SOBORA2 server. 

·        Use the operating system command:  env  -- to check the environment variable values. 

·        If they are not set, modify your .profile file so that they are always set when you connect. 

·        Later in this document you will learn how to modify the .profile file. 

 

ORACLE_HOME is the full path to the top directory in which the Oracle software is installed.  The directory for ORACLE_HOME should be supplied by the person who installed the server, usually the system administrator or the DBA.  At SIUE, the value for ORACLE_HOME for Oracle version 11g:

 

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

 

ORACLE_BASE -- you have already learned this is the base directory for installing Oracle products.

 

ORACLE_BASE=/u01/app/oracle

 

ORACLE_SID is the name assigned to an instance of a database. 

·        The ORACLE_SID (system identifier) is used by the operating system to distinguish different database instances running on the machine. 

·        When you create your database at SIUE, you will name the database as specified by your instructor, for example your database may be named USER350.

 

ORACLE_SID=USER350

 

IMPORTANT NOTE:  UNIX and LINUX are case-sensitive for directory and file names.  Since Oracle interfaces with the operating system when naming files, you must ensure that in naming the database you are consistent in your use of either lower case or upper case – use of upper case when naming the database is the generally accepted industry standard, e.g., USER350.

 

PATH defines the directories the operating system searches to find executables, such as SQLPlus. 

·        The Oracle RDBMS executables are located in $ORACLE_HOME/bin and this must be added to the PATH variable. 

·        The PATH for student accounts on the DBORCL server should already include $ORACLE_HOME/bin. 

·        You can examine the current PATH for your account with the operating system env command.

 

PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:/bin:/usr/bin:/usr/local/bin:.

 

LD_LIBRARY_PATH defines the directories in which required library files are stored.  The value for the SOBORA2 server for this variable is: 

 

LD_LIBRARY_PATH= /u01/app/oracle/product/11.2.0/dbhome_1/lib

 

If any of the environment variables are not set for your Oracle Server account, you can edit the .profile file by using either the vi editor. 

·        Before you edit any file, you should always create a backup copy of the file so you can recover if necessary.  Here the user dbockstd is copying the .profile file to a backup file named .profile.bkp.

 

dbockstd/@sobora2.isg.siue.edu=>cp .profile .profile.bkp

 

·        Another option is to use FTP software to transfer the .profile to your client computer, edit the file with a plain text editor such as Microsoft Notepad, then FTP the file back to your $HOME directory on the Oracle Server

·        Again, ensure you create a backup of the file prior to modifying it. 

·        Here are example commands for setting environment variables using the LINUX/UNIX operating system shell on the Oracle server.  If you add these to the .profile file, do NOT type the operating system prompt (the dollar sign symbol $).

 

Example -- Bourne or Korn shell:

 

$ ORACLE_SID= USER350; export ORACLE_SID

$ LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib

$ export LD_LIBRARY_PATH

 

2.  Create Directories to Store Your Database Files

 

You need to create directories where your database will be stored. 

·        Create directories named oradata inside your HOME directory on each of your allocated disk drives (/u01, /u02, and /u03).

·        Set the permissions to 775 to allow Oracle to read/write/execute for the directory in order to avoid any permission problems during database creation.  The setting of “5” is necessary for other binaries to read/execute for the directory. 

·        Example - here the user dbockstd is on their HOME directory on drive /u01.  The mkdir command creates the directory.  The chmod command sets the permissions for the directory.

 

/u01/student/dbockstd

dbockstd/@sobora2.isg.siue.edu=>mkdir oradata

 

/u01/student/dbockstd

dbockstd/@sobora2.isg.siue.edu=>chmod 775 oradata

 

 

3.  Create the initSID.ora Initialization Parameter File (PFILE)

 

The initSID.ora file (PFILE) stores the parameters used to initialize a database instance during startup. 

·        The parameters in this file also affect characteristics of the database when it is created.

·        Name the file according to your SIUE assigned USER account, e.g., initUSER350.ora.

 

You must create an initSID.ora file prior to attempting to create the database with the CREATE DATABASE command.

·        During installation of the Oracle server software, a sample init.ora file is copied to the $ORACLE_HOME/dbs directory. 

·        You should not attempt to modify this file; rather, you need to create a copy of the file to your own $HOME directory. 

·        When you copy the file to your $HOME directory, you need to name it initSID.ora where SID=your account name on the SOBORA2 server, for example, initUSER350.ora. 

·        Set the permissions on the file to 775 (read-write-execute for yourself and the group, and read-execute for the world) in order to avoid any permission errors during database creation.

 

You can set the permissions on the file when you FTP it to your $HOME directory or when the file is on the directory, use the operating system command to change the permissions.  Example:

 

dbockstd/@sobora2.isg.siue.edu=>chmod 775 initUSER350.ora

 

This is the list of parameter settings from the example init.ora file

·        Note that the database is being created at directory location $HOME/oradata for this example.

·        The directory $HOME/oradata has permission settings of 775 on the directory itself.

·        You must alter the settings to match your SIUE EID, e.g., replace dbockstd with your EID.

·        Change all of the directories listed to match your allocated HOME on the server.

 

# Change USER001 to your assigned user account.

# Change YOURHOME to your assigned SIUE EID for a HOME directory

 

db_name='USER001'

audit_file_dest='/u01/student/YOURHOME/oradata/adump'

audit_trail ='db'

compatible ='11.2.0'

control_files = ("/u01/student/YOURHOME/oradata/USER001control01.ctl",

    "/u02/student/YOURHOME/oradata/USER001control02.ctl")

db_block_size=8192

db_domain='siue.edu'

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=1G

diagnostic_dest='/u01/student/YOURHOME'

dispatchers='(PROTOCOL=TCP) (SERVICE=USER001XDB)'

 

# Uncomment the next two lines when turning on archivelog mode

# specify a location for the log archive destination

# LOG_ARCHIVE_DEST_1 = 'LOCATION = /u01/student/YOURHOME/oradata/archive'

# log_archive_format='USER001_%t_%s_%r.arc'

 

memory_target=1G

open_cursors=300

processes = 150

remote_login_passwordfile='EXCLUSIVE'

#UNDO_Management is Auto by default

undo_tablespace='UNDOTBS1'

# End of file

 

 

Create the Directories

 

Every directory referenced in the init.ora file must be created and have the permission settings established before attempting to execute a CREATE DATABASE command.  In the above init.ora file, these include:

 

audit_file_dest='/u01/student/YOURHOME/oradata/adump'

diagnostic_dest='/u01/student/YOURHOME'

# LOG_ARCHIVE_DEST_1 = 'LOCATION = /u01/student/YOURHOME/oradata/archive'

 

·        Note that the db_recovery_file_dest directory already exists. 

·        The directors on /u01, /u02, and /u03 named oradata must also exist.

 

 

The UNDO_MANAGEMENT initialization parameter determines whether the Oracle server automatically or the DBA manually handles undo data. 

·        You do not need to set UNDO_MANAGEMENT to AUTO -- it is AUTO by default. 

·        The name of the UNDO tablespace is established by the UNDO_TABLESPACE a parameter. 

·        This tablespace name must match the tablespace name in your CREATE DATABASE command (shown in the next section of these notes).  Example:

 

undo_tablespace='UNDOTBS1'

 

 

4. Create the Database

 

You are now ready to execute the CREATE DATABASE command. 

·        This assumes that you have planned for all files that will comprise your initial database.

·        The SQLPlus software is used when creating the database.

·        The Oracle executable for SQLPlus is sqlplus.

 

During database creation, the Oracle software is only aware of the SYS user and the SYSDBA role. 

·        To create a database you must connect to SQLPlus as the user SYS and the role SYSDBA. 

·        This can be accomplished by using one of several methods -- we will use PuTTY for a secure telnet session.

·        The steps are outlined below.

 

1.  Connect to the SOBORA2 Server through a PuTTY SSH session or by using SQL worksheet. 

·        Enter your account name and password at the operating system prompts. 

·        During login respond to the script asking for the ORACLE_SID with the name of the database you are about to create (if you mess this up, the easiest corrective action is to log off and login again).

 

login as: my_siue_eid

my_siue_eid@sobora2.siue.edu's password:

ORACLE_SID = [USER350] ?

ORACLE_HOME = [/home/oracle] ?

 

System responds back:

ORACLE_SID  = USER350

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

 

2.  Connect to SQLPlus.  In situations where the DBA's operating system account is assigned to the dba administrator's group, you can connect to sqlplus by using one of the two command sequences shown here.   Example:

 

dbockstd/@sobora2.isg.siue.edu=>sqlplus '/ as sysdba'

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 24 23:52:25 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL>

 

 

or

 

dbockstd/@sobora2.isg.siue.edu=>sqlplus /nolog

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 24 23:51:40 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

SQL> connect / as sysdba

Enter password: <you can just press the Enter key a password is not needed>

Connected to an idle instance.

 

SQL>

 

When you connect, you can verify that you are connected as SYS within SQL*Plus by using the SHOW USER command:

 

SQL> show user

USER is "SYS"

 

 

3.   Start up the database in NOMOUNT mode. 

·        This assumes the PFILE is stored to your $HOME directory and you are currently located at your $HOME directory.

 

SQL> startup nomount pfile=initUSER350.ora

 

·        This command assumes you are NOT necessarily located at your $HOME directory and can be issues while you are examining other directories.

 

SQL> startup nomount pfile=$HOME/initUSER350.ora

 

·        Oracle’s response to the STARTUP command is shown here.  The number of bytes allocated to the various memory structures may differ from those shown here.

 

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size                  2235208 bytes

Variable Size             616563896 bytes

Database Buffers          444596224 bytes

Redo Buffers                5541888 bytes

 

4.  Create a SQL script file that contains the CREATE DATABASE command.   This is easily done using Windows Notepad and you can FTP the script to your $HOME account on the SOBORA2 Server.  Using a script is better than typing the command in at the SQLPlus prompt because you are very likely to make a typographical error when typing the command. 

 

Example for the USER350 database: (pay attention to the placement of commas as shown in blue and the TEMPFILE specification for the Temporary Tablespace): 

 

CREATE DATABASE USER350

 USER SYS IDENTIFIED BY password1

 USER SYSTEM IDENTIFIED BY password2

  LOGFILE

  GROUP 1 ('/u03/student/dbockstd/oradata/USER350redo01a.log') SIZE 64M,

  GROUP 2 ('/u03/student/dbockstd/oradata/USER350redo02a.log') SIZE 64M

 MAXLOGFILES 20

 MAXLOGMEMBERS 5

 MAXLOGHISTORY 1

 MAXDATAFILES 25

 MAXINSTANCES 1

 CHARACTER SET US7ASCII

 NATIONAL CHARACTER SET AL16UTF16

 DATAFILE '/u01/student/dbockstd/oradata/USER350system01.dbf'

   SIZE 325M REUSE EXTENT MANAGEMENT LOCAL

 SYSAUX DATAFILE '/u01/student/dbockstd/oradata/USER350sysaux01.dbf'

   SIZE 325M REUSE

 DEFAULT TEMPORARY TABLESPACE temp

   TEMPFILE '/u02/student/dbockstd/oradata/USER350temp01.dbf'

   SIZE 50M REUSE

 UNDO TABLESPACE undotbs1

   DATAFILE '/u02/student/dbockstd/oradata/USER350undo01.dbf'

   SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

 

5.  Execute the SQL script to create the database.  Assuming the script is named create_database.sql, the SQL start command is used to run the script. 

 

SQL>  start create_database.sql

 

or

 

SQL> @create_database.sql

 

The script will run for a few minutes (depends on the size of the tablespaces you are creating and the number of other students working on the server) and eventually the SQL> prompt will display with a message telling you that the database was created.

 

Troubleshooting

 

If the CREATE DATABASE command fails, you must troubleshot the problem.  Potential errors include:

 

·        Syntax errors in the CREATE DATABASE script.  Determine the errors and correct them.  You will also have to delete any control files, redo log files, etc, that were created up to the point of failure in the CREATE DATABASE script.

·        One or more database/control file(s) already exists – delete the file(s) and any other @files that were created,  and run the CREATE DATABASE script again.

·        Insufficient directory permission.  You did not provide permission for the DBA group to which you belong to write to the directory where you are creating a file that is part of the database.  The permission setting needs to be a minimum of 775 (although in an operational setting we would probably use 660 or 770, here we are trying to avoid permission setting problems).  You can use the chmod command to set permissions.  Example setting the permission level for the oradata: 

 

$ chmod 775 oradata

 

·        The CREATE DATABASE command creates several files then abends because of an inability to update security files – you haven’t created a Password file for the database, but your init.ora file references one with the remote_login_passwordfile=EXCLUSIVE statement.  Solution – comment this statement out in your init.ora file because you will not be using a password file initially; delete all database files created, shutdown the instance (Shutdown Immediate), log off SQL*Plus, logon again and issue the CREATE DATABASE command again.

·        There is insufficient disk space available.  You will need to revise your database plan and select alternative disk drive resources for one or more files.

 

 

5.  Run Scripts to Create the Data Dictionary and Product User Profile

 

1.  Now you are ready to create the data dictionary.  Run the catalog.sql and catproc.sql scripts located in $ORACLE_HOME/rdbms/admin after the database is created. 

 

o   CATALOG.SQL – this script creates views against the data dictionary tables, dynamic performance views, and public synonyms for most of the views.   The group PUBLIC is granted readonly access to the views.

o   CATPROC.SQL – this script sets up PL/SQL for use by creating numerous tables, views, synonyms, comments, package bodies, and other database objects.

 

Both scripts must be run as SYS.  You should still be connected as SYS, but if you took a break and need to reconnect, the UNIX and SQL*Plus commands if you are not connected are:

 

$ sqlplus /nolog

SQL> connect sys as sysdba

SQL> Password:  <press the enter key here>

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

 

As the script executes, numerous messages will flash across the screen.  This may take several minutes to finish.

 

At the end of the script you will see:

 

Commit complete.

 

PL/SQL procedure successfully completed.

 

SQL>

 

2.  Now run the catproc.sql script. 

 

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

 

During execution of the script, you will see various messages.  Some of them are error messages, but these are NOT actual errors – usually they are caused by the script trying to drop or checking for the existence of a non-existent object before creating the object.

 

During execution of the script you will also have to respond to a few questions from the script – pressing the keyboard Enter key is a sufficient response.

 

On a system that is not busy, the total time for both scripts to complete is about 10 to 15 minutes.  Since our SOBORA2 server will be quite busy, we'll probably lecture on some topics while these scripts are running.

 

At the end of the script you will see:

 

 

SQL> SELECT dbms_registry_sys.time_stamp('CATPROC') AS timestamp FROM DUAL;

 

TIMESTAMP

-----------------------------------------------------

COMP_TIMESTAMP CATPROC    2013-05-26 16:14:34

 

1 row selected.

 

SQL>

SQL> SET SERVEROUTPUT OFF

 

3.  After these scripts have run, verify that the objects are valid.  The following query returns any invalid objects.

 

SELECT owner,object_name,object_type

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner,object_type,object_name;

 

You should receive the message "no rows selected".

 

If any objects are listed as invalid, shutdown your Oracle Instance, then delete all of the files you created that comprise your database and try again to create the database.

 

4.  Run the pupbld.sql script. 

 

Run the pupbld.sql script located in directory $ORACLE_HOME/sqlplus/admin to create the Product User Profile table and related procedures.  Running this script, among other purposes, prevents a warning message each time a user connects to SQL*Plus. 

 

IMPORTANT:  The script must be run while you are connected as the user SYSTEM.  The script runs very quickly – just a few seconds.

 

If you exited, SQL*Plus reconnect as the user SYSTEM.  Remember the default password for this user is manager– however, you may have set the password as part of your CREATE DATABASE command or you may have changed it already.  Earlier, the CREATE PASSWORD example command for USER350 set the SYSTEM user's password to the value password2.

 

SQL> connect system/<password>

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

 

At the end of the script, you will see this message.

 

SQL> -- End of pupbld.sql

 

6.  Creating Tablespaces

 

After creating the database, it is appropriate to create additional tablespaces.  The following tablespaces are recommended as a minimum starting point.  In a production environment, additional tablespaces would be created to support various applications and/or special requirements. 

 

At this point you should realize that the TEMP and UNDO01 tablespaces were created as part of the CREATE DATABASE command.  You should proceed to create the USERS tablespace.  We will leave the creation of additional tablespaces, specifically DATA and INDEXES to later computer laboratory assignments.

·        USERS – stores user data.

·        TEMP – stores temporary data created by ORDER BY sorting and table joins.  You may have created this as a TEMPORARY TABLESPACE as part of the CREATE DATABASE command.

·        UNDO – used for undo segments that support recovery operations.  You may have also created this as part of the CREATE DATABASE command.

·        DATA – used to store permanent organizational tables and clusters.

·        INDEXES – used to store permanent organizational indexes to tables and clusters.

 

Connect as SYS AS SYSDBA.

 

SQL> connect sys as sysdba

Enter password:

Connected.

 

CREATE TABLESPACE users

DATAFILE '/u02/student/dbockstd/oradata/USER350users01.dbf'

   SIZE 5M REUSE EXTENT MANAGEMENT LOCAL;

 

Tablespace created.

 

 

 

After the CREATE DATABASE Command

 

If the CREATE DATABASE command executes properly, you will now have an Oracle Instance of your database running and in OPEN stage.  It is available for use.

 

The CREATE DATABASE command always creates two users, SYS and SYSTEM regardless of whether or not you specify this as part of the command.  If you did not create them with unique passwords as part of the command, then they need to have their passwords changed immediately.  You can change them with the following SQL command:

 

SQL> ALTER USER sys IDENTIFIED BY new_password;

SQL> ALTER USER system IDENTIFIED BY new_password; 

 

This is the end of the manual creation of the database.  In future modules, you will continue to learn how to populate the database with additional tablespaces, tables, indexes, views, user accounts, roles, profiles, and other database objects.

 

Examine directory that stores your alert.log file.  For the USER350 database, this is located at:

 

/u01/student/dbockstd/diag/rdbms/user350/USER350/trace

 

The base directory for the Automatic Diagnostic Repository (ADR) files is set with the DIAGNOSTIC_DEST parameter in your init.ora file. 

 

·        Within the ADR home directory are subdirectories:
alert - The XML formatted alertlog -- it is named alert_USER350.log.
trace - files and text alert.log file
cdump - core files

·        The XML formatted alert.log is named as log.xml.

 

Confirm the existence of the alert.log file.  Another module will cover the alert file in more detail, but you should understand that the alert file stores messages about the startup, shutdown, and operation of your database.  You should examine is regularly for error messages.

 

 

Database Creation Using OMF

 

While we will not practice creating a database using the Oracle Managed Files (OMF) approach, you need to be familiar with this approach as a new DBA. 

 

OMF simplifies file administration because it eliminates the need to directly manage files within an Oracle database.  This is a good approach to use for a smaller database that runs in a Windows operating system environment.

 

Under OMF, files are named as follows:

·        Control Files:  ora_%u.ctl

·        Redo Log Files:  ora_%g_%u.log

·        Datafiles:  ora_%t_%u.dbf

·        Temporary Datafiles:  ora_%t_%u.tmp

 

%u is a unique 8-character string that is system generated.

%t is the tablespace name.

%g is the Redo Log File group number.

 

The parameter DB_CREATE_FILE_DEST can be set to specify the default location for datafiles.  The parameter DB_CREATE_ONLINE_LOG_DEST_N can be set to specify the default locations for Redo Log Files and Control Files where the value 'N' can be a maximum of 5 locations. 

 

Example initialization parameter file entries for OMF:

 

DB_CREATE_FILE_DEST=/$HOME/oradata/u01

DB_CREATE_ONLINE_DEST_1=/$HOME/oradata/u02

DB_CREATE_ONLINE_DEST_2=/$HOME/oradata/u03

 

Since these parameters specify file locations, the CREATE DATABASE command with OMF is simplified to simply require a specification of the database name.

 

CREATE DATABASE USER350;

 

The result of the CREATE DATABASE command with these specifications would be:

·        SYSTEM Tablespace of 300MB to 600MB in size that is autoextensible with unlimited size located at $HOME/oradata/u01.

·        Two online Redo Log groups with two members each of size 100MB.  The locations would be $HOME/oradata/u02 and $HOME/oradata/u03.

·        If the initialization parameter file specifies automatic undo management, an undo tablespace datafile will be created at location $HOME/oradata/u01 (same location as the SYSTEM tablespace) that is 10MB in size and which is autoextensible up to an unlimited size.  It will be named SYS_UNDOTBS.

·        If the initialization parameter file does NOT specify any control files, the CREATE DATABASE command will automatically cause two control files to be created, one in each location where the Redo Log Files are located.

 

 

Dropping a Database

 

Dropping a database removes its datafiles, redo log files, control files, and initialization parameter files. 

·        Generally you would use this as a student to drop a database in order to start over again if you make major mistakes.

·        Dropping a production database should only be done after creating a valid cold backup, because all data is deleted.

 

Drop Database Command

 

To drop a database, execute this statement.

 

DROP DATABASE;

 

·        The DROP DATABASE statement first deletes all control files and all other database files listed in the control file.

·        It then shuts down the database instance.

 

To use the DROP DATABASE statement successfully, the database must be mounted in exclusive and restricted mode.  Connect as SYS as SYSDBA.

 

SHUTDOWN;

 

<the database will be closed, dismounted, and the Oracle instance shut down>

 

STARTUP FORCE MOUNT;

 

ALTER SYSTEM ENABLE RESTRICTED SESSION;

 

DROP DATABASE;

 

The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database.  It is best to use RMAN to delete such files.  If the database is on raw disks, the actual raw disk special files are not deleted.

 

If you used the Database Configuration Assistant to create your database, you can use that tool to delete (drop) your database and remove the files.

 

 

 

END OF NOTES