Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 5: Data Dictionary Content and Usage

Practice: Setting up password file authentication

 

This practice will help you set up password file authentication for the Oracle 9i database.

 

Assumptions

 

·        The ORAPWD utility can be used on NT, Unix and lynux operating systems.

·        ORAPWD is a command line utility to create the password file

·        This file should be created in the dbs directory in Unix or the database directory in NT.

 

Instructions

 

  1. Create the password file with the ORAPWD utility in the correct directory.

 

C:\>orapwd

Usage: orapwd file=<fname> password=<password> entries=<users>

 

  where

    file - name of password file (mand),

    password - password for SYS and INTERNAL (mand),

    entries - maximum number of distinct DBA and OPERs (opt),

  There are no spaces around the equal-to (=) character.

 

C:\>orapwd file=c:\oracle9i\database\PWDtest2.ora password=oracle entries=5

 

C:\>

 

 

2.   Using SQLPLUS, make sure that the Oracle database is using the password file to authenticate DBA’s

 

SQL> show parameter remote_login_passwordfile

 

NAME                                 TYPE        VALUE

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

remote_login_passwordfile            string      EXCLUSIVE

SQL>

 

 

3.   Using SQLPLUS, add the user SCOTT to the password file for SYSDBA authentication.

 

SQL> grant sysdba to scott;

 

Grant succeeded.

 

 

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>

 

Practice: Data Dictionary Creation

 

This practice will focus on the creation of the Data Dictionary and PLSQL functionality.

 

Assumptions

·        All scripts run as user sys from SQLPLUS

·        Scripts are run on both UNIX and NT  

·        Scripts are found in ORACLE_HOME/rdbms/admin/

 

Instructions

 

1.      In Sqlplus, connect to the database as sys and run the CATALOG.sql and CATPROC.sql scripts.

 

connect SYS/change_on_install as SYSDBA

set echo on

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

@c:\oracle9i\rdbms\admin\catalog.sql;

@c:\oracle9i\rdbms\admin\catexp7.sql;

@c:\oracle9i\rdbms\admin\catblock.sql;

@c:\oracle9i\rdbms\admin\catproc.sql;

@c:\oracle9i\rdbms\admin\catoctk.sql;

@c:\oracle9i\rdbms\admin\catobtk.sql;

@c:\oracle9i\rdbms\admin\caths.sql;

@c:\oracle9i\rdbms\admin\owminst.plb;

connect SYSTEM/manager

@c:\oracle9i\sqlplus\admin\pupbld.sql;

connect SYSTEM/manager

set echo on

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

@c:\oracle9i\sqlplus\admin\help\hlpbld.sql helpus.sql;

spool off

spool off

exit;

 

 

1