Oracle 9i DBA Fundamentals, Exam 1Z0-031

The Oracle 9i DBA Fundamentals Exam is the second of two required tests to achieve Oracle Certified Associate status.

These notes are from Exam Cram Oracle 9i Fundamentals 1 by April Wells.

Tablespace contains segments which contain extents which contain data blocks which contain OS blocks. Extents are specific to a data file. Segments can span data files.

Oracle Universal Installer can be run non-interactively using a template so that installs can be completely scripted. ./runInstaller -responsefile filename -silent

The password file allows access to administrative roles sysdba / sysoper to an OS user without divulging the adminsitrative passwords.
Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.

Oracle Enterprise Manager can be used to control a fleet of servers using Intelligent Agents and the Oracle Management Server schema. Each managed node requires one Intelligent Agent which communicates with OMS.

Oracle Flexible Architecture would have:
ORACLE_BASE the topmost directory,
ORACLE_HOME set to ORACLE_BASE/product/release/
Add ORACLE_HOME/bin to the PATH.
Set ORA_NLS33 to ORACLE_HOME/nls/data if using character set other than US7ASCII.
Add ORACLE_HOME/lib to LD_LIBRARY_PATH.

Startup: nomount mount open restrict recover force
If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.
STARTUP NOMOUNT can be used to create a new controlfile.

Oracle Managed File parameters: DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n
When using OMF, alter database add logfile; will create logfile with size of 100MB.

Startup nomount then create database.
CREATE DATABASE runs sql.bsq to create data dictionary base tables.
Install data dictionary views with ORACLE_HOME/rdbms/admin/catalog.sql.
Install PLSQL with catproc.sql.

To convert ORACLE_HOME/dbs/initSID.ora to spfile: create spfile from pfile;

alter system enable restricted session; Leaves sessions connected but new sessions can only be established by DBA and those with RESTRICTED SESSION privilege.

If not specified with startup pfile=filename, then starts with ORACLE_HOME/dbs/spfileSID.ora or spfile.ora or initSID.ora.

alter session set sql_trace=true
dbms_system.set_sql_trace_in_session

Show data dictionary tables: SELECT * FROM DICTIONARY [DICT CATALOG CAT]
Show data dictionary columns: SELECT * FROM DICT_COLUMS

V$ are all dynamic performance views.

DBA_SEGMENTS and DBA_EXTENTS both have column BLOCKS.

Block header contains: block address, row directory, table directory, transaction slots.
The table directory is the portion of the data block contains information about the table having rows in this block.
Blocks have a header that consist of:
* Block type
1=undo segment header, 2=undo segment block, 5=data segment header, 7=temporary table, 11=data file header, 14=Unlimited rollback segment header, 15=Unlimited deferred rollback segment header, 16=Unlimited data segment header, 17=Unlimited data segment header with FL groups, 18=Extent map block
* Block format
Is it an oracle 7 or oracle 8 or 9 or 10 formatted block?
* Relative database address
* SCN
* SCN sequence number
* Check value
Is used for integrity checking at the block level if db_block_checksum is set.
* Tail
The tail is not at the beginning of a block but at its end: it is used as a block consistency check. That is, the value must be equal to SCN + block type + SCN sequence number.

AL32UTF8 varying width multi byte character set.
AL16UTF16 fixed width multi byte character set.
UTF-8 unicode standard.
UTF8 Oracle implementation of unicode.
Database character set may not be fixed width multi byte.
National character set may be fixed width multi byte.
When developing a packaged application, UTF-16 and nvarchar data types will provide most portability to any customer site.
NLS_LANGUAGE determines: NLS_DATE_LANGUAGE NLS_SORT.
Set ORA_NLS33 to $ORACLE_HOME/ocommon/nls/admin/data
Default sorting method is binary.
V$NLS_PARAMETERS

System Change Number is stored in all data files and control files and redo log files.
alter database open resetlogs; This creates a new incarnation of the database and affects recovery.
create controlfile...; Create control file from scratch with information you provide. You need to know names of all data files.
alter database backup controlfile to '/path/name';
alter database backup controlfile to trace;
V$CONTROLFILE

FAST_START_MTTR_TARGET sets upper bound on recovery time by causing more frequent check points.
alter system switch logfile;
alter database clear [unarchived] logfile group ;
V$LOG
V$LOGFILES
V$LOGHISTORY Archived logs.
V$LOGHIST Online logs.
alter system archive log all; Does what the archiver process does.

CREATE TABLESPACE BLOCKSIZE needs a configured subcache to use - DB_[2 4 8 16 32]K_CACHE_SIZE. Temporary tablespace can not use nonstandard block size.
Tablespaces that can never be offline: system, default temporary, undo with active segments.
ALTER TABLESPACE OFFLINE NORMAL;
TEMPORARY; might need recovery
IMMEDIATE; will need recovery
FOR RECOVER; allows tablespace point in time recovery
ONLINE;
READ ONLY; still will allow objects to be dropped
READ WRITE; undoes READ ONLY
Temporary tablespace tuning should set UNIFORM EXTENT SIZE to multiple of SORT_AREA_SIZE.
BACKUP CONTROLFILE does not record temporary tablespace temp files.
CREATE CONTROLFILE does not allow specifying temporary tablespace.
Set a default temporary tablespace: alter database default temporary tablespace ;
To drop a tablespace for sure: drop tablespace including contents and datafiles cascade constraints;

Read consistency is implemented through undo and system change numbers.

SET TRANSACTION READ ONLY; provides transaction level repeatable read.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; provides transaction level repeatable read during read/write.

Types of undo: system, non-system, deferred. Deferred occurs when tablespace is offline for recovery.

Init.ora settings for system managed undo: undo_management [auto, manual] undo_tablespace=.

How to change the undo tablespace: alter system set undo_tablespace=;
When one instance of RAC is looking for a previous SCN in undo, does it look in it's undo then ask all other instances if it is available in their undo?

alter system set undo retention=;
Monitor via v$undostat.

Index organized table overflow holds columns that don't fit or that are excluded (PCTTHRESHOLD INCLUDING).

CHAR(n) 1-2000 bytes, blank padded.
VARCHAR2(n) 0-4000 bytes.
DATE 7 bytes.
TIMESTAMP Fraction of second, up to 9 digits, default is 6.
BLOB When greater than 4000 bytes, a locator points to out of line storage.
VARRAY Stores a list, manimum size is set.
External table:
create or replace directory as '/dir';
grant read on directory to user1;
create table ... organization external;

dbms_redefinition.can_redef_table to verify if it can be online redefined.
Create interim table.
dbms_redefinition.start_redef_table
dbms_redefinition.finish_redef_table

alter table move tablespace ;

Bitmap index may never be unique.
alter index rebuild online; online allows continued DML on the table.
alter index coalesce; Merges leaf blocks.
analyze index validate structure; Information is stored in index_stats.
alter index monitoring usage; Information is stored in V$OBJECT_USAGE.

Primary key constraint may not contain nulls.
Unique constraint may contain nulls.

drop table cascade constraints;
drop tablespace including contents cascade constraints;

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2427.htm#1315815
$ORACLE_HOME/rdbms/admin/utlexcpt.sql creates EXCEPTIONS table.
alter table enable valid constraint exceptions into EXCEPTIONS;

PASSWORD_LOCK_TIME number of days account will be locked after FAILED_LOGIN_ATTEMPTS
.

PASSWORD_REUSE_TIME days before password may be reused. PASSWORD_REUSE_MAX number of new passwords before reuse of previous password. These are mutually exclusive - use one or the other.

PASSWORD_LIFE_TIME days to expire password.

Oracle-supplied password verify_function is enabled by running utlpwdmg.sql. It changes the default profile.

ALTER RESOURCE COST changes the weighted values.

Init.ora RESOURCE_LIMIT

DBMS_RESOURCE_MANAGER package use requires privilege
ADMINISTER_RESOURCE_MANAGER, which is in DBA role.
Resource plans are created in the pending area. The resource plan guides resources to consumer groups.
execute dbms_resource_manager.create_pending_area;
execute dbms_resource_manager.clear_pending_area;
Create consumer group. There may be more than one consumer group per plan. Oracle provides consumer groups default_consumer_group and other_groups.
execute dbms_resource_manager.create_consumer_group;
execute dbms_resource_manager.create_plan(plan_name);
Plan directive specifies how resource are allocated to consumer groups.
execute dbms_resource_manager.create_plan_directive; This must always include the OTHER_GROUPS catch-all.
Individual users needs privileges to set initial group and to switch groups. It is convenient to grant the privileges to PUBLIC.
execute dbms_resource_manager.set_initial_consumer_group;
execute dbms_resource_manager.grant_switch_consumer_group;
Activate the resource manager: alter system set resource_manager_plan=<>;
DBA_RSRC_PLANS lists all resource plans.
V$RSRC_PLAN lists currently active plans.
DBA_RSRC_PLAN_DIRECTIVES lists all resource plan directives.

To move the audit trail out of the system tablespace:
alter table aud$ move tablespace <>'
create index <> on aud$(sessionid, ses$tid) tablespace <>;

Users with role SYSOPER can
STARTUP RESTRICT.

Extended ROWID is ten bytes encoded in base 64. Restricted ROWID is six bytes encoded in base 16.