Practice: Manual Database Creation

 

This Practice will focus on the manual creation of the Oracle Database.

 

Assumptions

 

·        SYSDBA privileges have been established by OS or Password File

·        Sqlplus script to create the database will work on different OS’s file paths or is changed by the Learner

 

Instructions

 

1.      In Sqlplus, connect with sysdba privileges and run the create database script.

 

connect SYS/change_on_install as SYSDBA

set echo off

spool c:\oracle9i\assistants\dbca\logs\CreateDB.log

startup nomount pfile="c:\oracle9i\admin\test\scripts\init.ora";

CREATE DATABASE test

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

DATAFILE

'c:\oracle9i\oradata\test\system01.dbf'

SIZE 325M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

UNDO TABLESPACE "UNDOTBS"

DATAFILE 'c:\oracle9i\oradata\test\undotbs01.dbf' SIZE 200M

REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

CHARACTER SET WE8MSWIN1252

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('c:\oracle9i\oradata\test\redo01.log') SIZE 100M,

GROUP 2 ('c:\oracle9i\oradata\test\redo02.log') SIZE 100M,

GROUP 3 ('c:\oracle9i\oradata\test\redo03.log') SIZE 100M;

spool off

 

 

2.      In Sqlplus, check the physical files created by the script.

 

SQL> select name from v$datafile

  2  /

 

NAME

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

C:\ORACLE9I\ORADATA\TEST\SYSTEM01.DBF

C:\ORACLE9I\ORADATA\TEST\UNDOTBS01.DBF

 

SQL> select name from v$controlfile;

 

NAME

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

C:\ORACLE9I\ORADATA\TEST\CONTROL01.CTL

C:\ORACLE9I\ORADATA\TEST\CONTROL02.CTL

C:\ORACLE9I\ORADATA\TEST\CONTROL03.CTL

 

SQL> select member from v$logfile;

 

MEMBER

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

C:\ORACLE9I\ORADATA\TEST\REDO01.LOG

C:\ORACLE9I\ORADATA\TEST\REDO02.LOG

C:\ORACLE9I\ORADATA\TEST\REDO03.LOG

 

SQL>

 

 

Vladimir Grigorian, Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 3: Managing an Oracle Instance

Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 3: Managing an Oracle Instance

Practice: Create and Manage Initialization Parameter Files

 

This practice shows how to create and use the SPFILE and maintain the PFILE.

 

Assumptions

 

 

 

Instructions

 

1.      Using SQLPLUS, select the non-default parameters from the v$parameter view.

 

SQL> l

  1  select name, value from v$parameter

  2* where isdefault ='FALSE'

SQL> /

 

NAME                      VALUE

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

processes                 150

timed_statistics          TRUE

shared_pool_size          29360128

sga_max_size              135032864

large_pool_size           1048576

java_pool_size            0

control_files            c:\oracle9i\oradata\OEMREP\CONTROL01.CTL

db_block_size             4096

db_cache_size             16777216

compatible                9.0.0

fast_start_mttr_target    300

undo_management           AUTO

undo_tablespace           UNDOTBS

remote_login_passwordfile EXCLUSIVE

db_domain

distributed_transactions  0

instance_name             OEMREP

background_dump_dest      c:\oracle9i\admin\OEMREP\bdump

user_dump_dest            c:\oracle9i\admin\OEMREP\udump

core_dump_dest            c:\oracle9i\admin\OEMREP\cdump

sort_area_size            524288

db_name                   OEMREP

open_cursors              300

 

24 rows selected.

 

 

2.      Using SQLPLUS, create an SPFILE from the existing PFILE in the default directory for these files.

 

SQL> create spfile from pfile;

 

File created.

 

 

3.      Using SQLPLUS, select the non-default parameters from v$SPPARAMETER view.

 

SQL> l

  1  select name, isspecified, value

  2  from v$spparameter

  3* where isspecified = 'TRUE'

SQL> /

 

NAME                      ISSPEC VALUE

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

processes                 TRUE   150

timed_statistics          TRUE   TRUE

shared_pool_size          TRUE   16777216

sga_max_size              TRUE   134217728

large_pool_size           TRUE   1048576

java_pool_size            TRUE   0

control_files             TRUE   c:\oracle9i\oradata\OEMREP\CONTROL01.CTL

control_files             TRUE   c:\oracle9i\oradata\OEMREP\CONTROL02.CTL

control_files             TRUE   c:\oracle9i\oradata\OEMREP\CONTROL03.CTL

db_block_size             TRUE   4096

db_cache_size             TRUE   16777216

compatible                TRUE   9.0.0

fast_start_mttr_target    TRUE   300

undo_management           TRUE   AUTO

undo_tablespace           TRUE   UNDOTBS

remote_login_passwordfile TRUE   EXCLUSIVE

db_domain                 TRUE

distributed_transactions  TRUE   0

instance_name             TRUE   OEMREP

dispatchers               TRUE   (PROTOCOL=TCP)(SER=MODOSE)

dispatchers               TRUE   (PROTOCOL=TCP)(PRE=oracle.aurora.server.

                                 GiopServer)

 

dispatchers               TRUE   (PROTOCOL=TCP)(PRE=oracle.aurora.server.

                                 SGiopServer)

 

background_dump_dest      TRUE   c:\oracle9i\admin\OEMREP\bdump

user_dump_dest            TRUE   c:\oracle9i\admin\OEMREP\udump

core_dump_dest            TRUE   c:\oracle9i\admin\OEMREP\cdump

sort_area_size            TRUE   524288

db_name                   TRUE   OEMREP

open_cursors              TRUE   300

28 rows selected.

 

 

4.      Using SQLPLUS, create a pfile from the existing spfile.

 

SQL> create pfile from spfile;

 

File created.

 

Practice: Describe the Oracle architecture and its main components

 

In this practice you will find sizes of the parts of the SGA and you will also dynamically change the sizes of the shared pool and the buffer cache.

 

Assumptions

 

·        This practice is written for any OS. 

·        The database version for these practices is assumed to be Oracle 9.0.1.

·        These practices assume that you have a database accounts with either DBA, SYSDBA.

·        These practices assume that you have a dynamic spfile instead of a pfile.  

 

 

Instructions

 

1.  Using SQLPLUS, show how big the SGA (Shared Global Area) is.

 

SQL> show sga

 

Total System Global Area  135032864 bytes

Fixed Size                   282656 bytes

Variable Size             117440512 bytes

Database Buffers           16777216 bytes

Redo Buffers                 532480 bytes

SQL>

 

 

      2.  Using SQLPLUS, show memory structures of the SGA include the sizes in bytes.

 

SQL> l

  1  select name, value from v$parameter where name in

  2  ('shared_pool_size',

  3   'java_pool_size',

  4   'large_pool_size',

  5   'db_cache_size',

  6   'log_buffer')

  7* order by 1

SQL> /

 

NAME                      VALUE

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

db_cache_size             16777216

java_pool_size            0

large_pool_size           1048576

log_buffer                524288

shared_pool_size          16777216

 

SQL>

 

 

 

3.  Using SQLPLUS, dynamically alter the size of the shared pool to 32 Meg, show the new size and dynamically alter it back to 16 Meg.  Use the ALTER SYSTEM command for both operations.

 

SQL> show parameter shared_pool_size

 

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 16777216

SQL> alter system set shared_pool_size = 32m;

 

System altered.

 

SQL> show parameter shared_pool_size

 

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 33554432

SQL> alter system set shared_pool_size = 16m;

 

System altered.

 

SQL> show parameter shared_pool_size

 

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 16777216

SQL>

           

     

4.  Using SQLPLUS, Show the size of the db_buffer_cache and dynamically change it to 32 m and  then back to 16m.  Use the ALTER SYSTEM command for both operations.

 

SQL> show parameter db_cache_size

 

NAME                                 TYPE        VALUE

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

db_cache_size                        big integer 16777216

SQL> alter system set db_cache_size=32m;

 

System altered.

 

SQL> show parameter db_cache_size

 

NAME                                 TYPE        VALUE

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

db_cache_size                        big integer 33554432

SQL> alter system set db_cache_size=16m;

 

System altered.

 

SQL> show parameter db_cache_size

 

NAME                                 TYPE        VALUE

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

db_cache_size                        big integer 16777216

SQL>

 

           

            5.  Using SQLPLUS, select the current size and the previous size of the default buffer cache.

 

SQL> l

  1  select name, current_size, buffers, prev_size, prev_buffers

  2* from v$buffer_pool

SQL> /

 

NAME                      CURRENT_SIZE    BUFFERS  PREV_SIZE PREV_BUFFERS

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

DEFAULT                             16       3932         32         7864

 

     

Practice: Configure OMF

 

This practice will help you configure Oracle Managed Files (OMF).

 

Assumptions

 

·        This practice is for the Windows platform, UNIX users will need to changed directories accordingly.

·        This practice uses SQLPLUS to explain OMF’s

 

Instructions

 

1.      Using SQLPLUS, select the parameters that control Oracle Managed Files from the V$SPPARAMETER view.

 

SQL> l

  1  select name, value

  2  from v$spparameter

  3* where name like 'db_create%'

SQL> /

 

NAME                                               VALUE

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

db_create_file_dest

db_create_online_log_dest_1

db_create_online_log_dest_2

db_create_online_log_dest_3

db_create_online_log_dest_4

db_create_online_log_dest_5

 

6 rows selected.

 

 

2.      Using SQLPLUS, alter the system to set the destinations for Oracle Managed Files, both datafiles and control and redo log files.

 

alter system set db_create_file_dest = 'C:\ORACLE9I\ORADATA\OEMREP\'

 

System altered.

 

SQL>  alter system set db_create_online_log_dest_1 = 'C:\ORACLE9I\ORADATA\OEMREP\' scope=memory;

 

System altered.

 

 

3.      Using  SQLPLUS, select the destinations of Oracle Managed Files from V$SPPARAMETER.

 

SQL> l

  1* select name, value from v$spparameter where name in ('db_create_file_dest','db_create_online_log_

SQL> /

 

NAME                           VALUE

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

db_create_file_dest            C:\ORACLE9I\ORADATA\OEMREP\

db_create_online_log_dest_1    C:\ORACLE9I\ORADATA\OEMREP\

 

 

Practice: Start Up and Shut Down an Instance

 

This practice will show how to startup and shutdown the Oracle database.

 

Assumptions

 

·        This practice is for any OS.

·        This practice will use SQLPLUS to startup the database

·        User needs the SYSDBA privileges, either through password file or OS authentication.

 

Instructions

 

1.      After connecting to the database with SYSDBA privileges in SQLPLUS, startup the databse in NOMOUNT state.

 

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  135032864 bytes

Fixed Size                   282656 bytes

Variable Size             117440512 bytes

Database Buffers           16777216 bytes

Redo Buffers                 532480 bytes

 

 

2.      Using SQLPLUS, alter the database to MOUNT state.

 

SQL> alter database oemrep mount;

 

Database altered.

 

 

3.      Using SQLPLUS, select the status of the instance from the V$INSTANCE view.

 

  1* select instance_name, status from v$instance

SQL> /

 

INSTANCE_NAME    STATUS

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

oemrep           MOUNTED

 

 

4.      Using SQLPLUS, alter the database to OPEN state.  Then select the status of the database from V$INSTANCE.

 

SQL> alter database open;

 

Database altered.

 

SQL> select instance_name, status from v$instance;

 

INSTANCE_NAME    STATUS

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

oemrep           OPEN

 

 

Practice: Shut Down an Instance

 

This practice you will shutdown the database using shutdown immediate.

 

Assumptions

 

 

 

Instructions

 

1.      Using SQLPLUS, select the status of the database from v$instance.

 

SQL> select instance_name, status from v$instance;

 

INSTANCE_NAME    STATUS

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

oemrep           OPEN

 

 

2.      Using SQLPLUS, connect to the database with SYSDBA privileges.

 

SQL> connect /@oemrep as sysdba

Connected.

 

 

3.      Using SQLPLUS, shutdown the database with a checkpoint.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

 

1