Oracle9i DBA -- Database: Fundamentals I (20)

Other

##CONTINUE##

• Query the data dictionary view DBA_FREE_SPACE to show how much free space is available in the database.
• DBA_FREE_SPACE identifies the location and amount of free space by tablespace name, file ID, starting block ID, bytes, and blocks.
• Query the data dictionary view DBA_SEGMENTS to display how much space is already used. And the location of all the tables and indexes by one users.
• LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:

  1. When a transaction commits

  2. When the redo log buffer cache is one-third full

  3. When there is more than a megabyte of changes records in the redo log buffer cache

  4. Before DBWn writes modified blocks in the database buffer cache to the data files

  5. Every 3 seconds.

  Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.

  LGWR can also call on DBWn to write to the data files.

  Note: DBWn does not write to the online redo logs.

• There is a one-to-one correspondence between a user and server process, is called a dedicated server connection. When using a shared server configuration, it is possible for multiple user processes to share server processes.
• PCTUSED for a data segment represents the minimum percentage of used space that the Oracle server tries to maintain for each data block of the table. A block is put back on the free list when its used space falls below PCTUSED. The free list of a segment is a list of blocks that are candidates for accommodating future inserts. A segment, by default, is created with one free list. Segments can be created with a higher number of free lists by setting the FREELISTS parameter of the storage clause.

Oracle9i DBA -- Database: Fundamentals I (19)

Using Globalization Support

##CONTINUE##

• NLS_DATABASE_PARAMETERS shows the database character set and all the NLS parameter settings.
• NLS_CHARACTERSET and the NLS_NCHAR_ CHARACTERSET are set when creating the database and cannot be changed later.
• 

Auditing

• Using DBMS_FGA the DBA creates an audit policy on the target table. Monitoring of data access based on content is implemented through DBMS_FGA.
• Audit session, Audit session whenever successful, Audit session whenever not successful, Audit select on Account.person, Audit Delete on Account.person

Loading data into a database

• The three models of concurrency are Intersegment concurrency, Intrasegment concurrency and Parallel concurrency
  

Oracle9i DBA -- Database: Fundamentals I (18)

Managing Roles

##CONTINUE##

• Role Privileges include the following : Create Role, Alter Any Role, Drop Any Role, Grant Any Role
• The SET ROLE command will turn off any other roles granted to the user.
• You can’t assign a role to a user within a CREATE USER statement.
• Max_Enabled_roles allow the user to limit the maximum number of database roles that the user can enable.
• Default role is granted through alter user. Set role is use for enabling or disabling a role.
• SET ROLE NONE is to turn off all roles granted to the user including default role. Drop role is drop the role and not disable it. Revoke role is used to revoke the role from the user and not just disable it.
• The CREATE DATABASE , ALTER TABLESPACE privilege is SYSDBA privilege
• 
  

Oracle9i DBA -- Database: Fundamentals I (17)

Managing Privileges

##CONTINUE##

• System privilegea enable users to perform certain actions in the database while object privilege enables users to access and manipulate specific objects.
• ALTER SESSION is a system privilege the information can be queried from DBA_SYS_PRIVS and USER_SYS_PRIVS views.
• To ensure that system privileges allow access on all schemas except SYS the 07_DICTIONARY_ACCESSIBILITY parameter must be set to FALSE.
• Oraenv is to switch between databases. Imp and exp are import and export. Sqlldr is for SQL*Loader
• Execute privilege on a procedure is a object privilege the information can be gathered from ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
• The Object privilege has a cascading effect. The System does not have any cascading effect.

Oracle9i DBA -- Database: Fundamentals I (16)

Managing Users

##CONTINUE##

• A user can be authenticated by the data dictionary, operating system and network.
• ORACNV is a script which can be executed to create a centralized repository of user accounts leading to ease of administration.
• The unlimited tablespace privilege takes priority over quota settings.
• To encrypt a user’s password the ORA_ENCRYPT_LOGIN parameter must be set to true.
• OS_AUTHENT_PREFIX = “” is used to set user name format to a NULL prefix. OS_AUTHENT_PREFIX = OPS$ will allow the user to be authenticated by the OS or the Oracle server. Create User OPS$xxx IDENTIFIED EXTERNALLY will allow the user to be authenticated by the OS. REMOTE_OS_AUTHENT=TRUE specifies that a usr can be authenticated by a remote os.
• 

Oracle9i DBA -- Database: Fundamentals I (15)

Managing Password Security and Resources
##CONTINUE##

• Resource Limits can be set at session level and call level
• Profiles are assigned to users by the CREATE USER or ALTER USER command
• Enforce the resource limits

  1. by enabling the parameter with the ALTER SYSTEM command

  ALTER SYSTEM SET RESOURCE_LIMIT=TRUE

  2. Set the RESOURCE_LIMIT parameter to TRUE

• Profiles cannot be created by the CREATE user command.
• 

Oracle9i DBA -- Database: Fundamentals I (14)

Maintaining Data Integrity
##CONTINUE##

• The utlexcptl.sql script can create the EXCEPTIONS table

• CHECK constraint can be defined at both column and table level. Environment functions such as SYSDATE cannot be used to evaluate check condition.
• Use the SET CONSTRAINTS statement to make constraints either DEFERRED or IMMEDIATE. The ALTER SESSION statement also has cluses to SET CONSTRAINTS to DEFERRED or IMMEDIATE.
• DBA_CONSTRAINTS view is required to return the name of the foreign key constraint(CONSTRAINTS_NAME) and the referenced primary key(R_CONSTRAINTS_NAME).
• Both unique and primary key constraints require index.

Oracle9i DBA -- Database: Fundamentals I (13)

Managing Indexes
##CONTINUE##

• When you encounter index fragmentation, you can rebuild or coalesce the index. coalesce on an index is a block rebuild that is done online.
• DBA_INDEXES : index name , index type, table owner, index owner.
• Index rebuilds have the following characteristics

  O A new index is built using an existing index as the data source

  O Sorts are not needed when an index is built using an existing index, resulting in better performance.

  O The old index is deleted after the new index is build. During the rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.

  O The resulting index does not contain any deleted entries. This index uses space more efficiently

  O Queries can continue to use the existing index while the new index is being built.

• DBA_IND_EXPRESSIONS : Provides information on columns or expressions used to create index
• V$OBJECT_USAGE : Provide information on usage of index
• Cluster maintains a “Cluster Index” that is used to locate rows and other information
• B-tree indexes work well in single indexes rather than multiple indexes. B-tree support row-level both unique and non-unique indexes. B-tree index are suitable for retrival of a small number of rows.
• The individual partitions are stored in separate segments whether it is a table partition or index partition. Partitions are beneficial in large objects that are read or written to simultaneously.
• 
  

Oracle9i DBA -- Database: Fundamentals I (12)

Managing Tables

##CONTINUE##

• The TZ environment variable can be used to decide the how the date will be displayed.
• Each row in the table consists of a Row header (store the number of columns in a row) and Row Data (stores the column length and value).
• A ROWID is composed of

  O Datafile number : Is assigned to each data object, such as table or index when it is created, and it is unique within the database.

  O Relative file number : is unique to each file within a tablespace

  O Block number : Represents the position of the block, containing the row, within the file.

  O Row number : Identifies the position of the row directory slot in the block header.

• The portioning methods available in tables are Range, Hash, List and Composite. Range portioning is useful for tables with a range of value. Hash portioning is done using Hash algorithm. When all the values that are to be stored in a column are known, list partitioning can be used. Composite partitioning is a combination of hash and range partitioning.
• The value of the parameter COMPATIBLE must be set to Oracle 8.1 or highter to use ROWID.
• PCTFREE, NEXT, MINEXENTS can be modified after table creation.
• Row Migration occurs due to low PCTFREE value. Row chaining the row is divided into smaller chunks. Row migration the entire row is moved to a new block.

Oracle9i DBA -- Database: Fundamentals I (11)

Managing Undo Data

##CONTINUE##

• For sizing a UNDO tablespace the UNDO_RETENTION in seconds, number of undo data blocks generated per second and the overhead varies based on extent and file size (db_block_size) is required.
• Use the ALTER SYSTEM command for dynamic switching between UNDO tablespaces

  ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

• An undo segment with the status PENDING OFFLINE still contains active transactions. When no rows return from the query, then all transactions are complete, and the tablespace can be dropped.
• Oracle server automatically creates an UNDO tablespace called SYS_UNDOTBS when no undo tablespace name is specified.
• Deferred undo segments are dropped automatically
• Before any DML operation the undo information will always be stored in the undo segment.
• ‘Snapshot too old’ error may occur if there are long running queries. This generally takes place if the database has larger rollback extents. If the rollback extents are smaller, it will lead to undo extents getting deallocated.
• V$ROLLSTAT is used to view the undo statistic and the segment number. V$ROLLNAME is show the segment number and name. V$UNDOSTAT contains snapshots of the performance of the undo tablespace. V$TRANSACTION and V$SESSION can be used to view the active transactions.
• The ‘Set TRANSACTION ISOLATION LEVEL SERIALIZABLE’ will ensure read consistency of the transaction, though it is derimental to its performance.
• The different type of undo segments are SYSTEM, NONSYSTEM and Deferred. The deferred segment is created when a tablespace is brought offline. They are used for recovery when the transaction is brought online.
• USER_SEGMENT data dictionary contains all types of segments and their storage parameters for the user.
  

Oracle9i DBA -- Database: Fundamentals I (10)

Storage Structure and Relationships

##CONTINUE##

• The default size for PCTFREE is 10 and of PCTUSED is 40 ,and the PCTINCREASE 50
• Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
• MAXTRANS specifies the number of concurrent transactions per block.
• INITRANS, MAXTRANS,PCTFREE,PCTUSED are associated with the block.
• Increasing the PCTFREE parameter can control Row migration in blocks
• Optimal Flexible Architecture (OFA) : Oracle's recommended standard database architecture layout

  Three major rules:

  O Establish a directory structure where any database file can be stored on any disk resource

  O Separate objects with different behavior into different tablespaces

  O Maximize database reliability and performance by separating database components across different disk resources

• The Oracle blocks are the finest level of data storage identity that contains multiple operating system blocks.
• The Dictionary Managed Tablespace automatically assume the size of NEXT clause as 5 times the DB_BLOCK_SIZE and similarly INITIAL has to be twice of DB_BLOCK_SIZE
• Extents are deallocated whenever the segments are altered, dropped or truncated.
• Extents are allocated to segments when the segment are altered, created or extended.
• Bootstrap segment (Cache segment), Table , Index and IOTs have their separate segments. Nested Tables stored in their own segment.
• The effects of using TRUNCATE command are as follows:

  1. All rows in the table are deleted.

  2. No undo data is generated and the command commits implicitly because TRUNCATE TABLE is a DDL command.

  3. Corresponding indexes are also truncated.

  4. A table that is being referenced by a foreign key cannot be truncated.

  5. The delete triggers do not fire when this command is used.

  6. It reduces the number of extents allocated to the table to the original setting for MINEXTENTS.

Oracle9i DBA -- Database: Fundamentals I (9)

Managing Tablespace and Datafiles

##CONTINUE##

• The free extents are managed in the tablespaces via the bitmaps. Bit values are used to indicate free or used space.
• Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces.

  CREATE TABLESPACE data02

  DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

  SEGMENT SPACE MANAGEMENT AUTO;

  Ps. Free space is managed in the undo tablespace for a tablespace created with automatic segment-space management.

• Using the CREATE TABLESPACE statement and specifying the blocksize clause can create tablespaces of non-standard block size. DB_CACHE_SIZE and DB_nK_CACHE_SIZE
• The sum of the size of all data files specified in the CREATE TABLESPACE statement determines the initial size of a tablespace.
• Datafiles can belong to only one tablespace and database
• Moving Data Files:

  O ALTER DATABASE :

  ALTER DATABASE RENAME

  FILE '/u01/oradata/system01.dbf'

  TO '/u03/oradata/system01.dbf';

  1. Shut down the database.

  2. Use an operating system command to move the files.

  3. Mount the database.

  4. Execute the ALTER DATABASE RENAME FILE command.

  5. Open the database.

  SYSTEM tablespace cannot be taken offline, you must use this method to move

  data files in the SYSTEM tablespace.

  O Moving Data Files: ALTER TABLESPACE

  ALTER TABLESPACE userdata

  RENAME DATAFILE '/u01/oradata/userdata01.dbf'

  TO '/u01/oradata/userdata01.dbf';

  1.Take the tablespace offline.

  2. Use an operating system command to move or copy the files.

  3. Execute the ALTER TABLESPACE RENAME DATAFILE command.

  4. Bring the tablespace online.

  5. Use an operating system command to delete the file if necessary.

• When a datafile is made read-only it can reside only on read-only media.
• The Read-only tablespace can be deleted in the same manner the READ WRITE tablespace is.
• The Default temporary tablespace is a locally managed tablespace.It cannlt be altered to a permanent tablespace.

• When a tablespace is taken offline with the immediate clause, it need to do media recovery when bringing the tablespace online.

• Locally managed tablespaces :
  

  

O Free extents recorded in bitmap
O Each bit corresponds to a block or group of blocks
O Bit value indicates free or used

O Reduced contention on data dictionary tables

O No undo generated when space allocation or deallocation occurs

O No coalescing required

• Dictionary-managed tablespaces :

• O Default method

  O Free extents recorded in data dictionary tables

  O Extents are managed in the data dictionary

  O Each segment stored in the tablespace can have a different storage clause

  O Coalescing required

  • Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:

  1. Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table.

  2. Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.

  3. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

  4. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace

  5. Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

  • FOR RECOVERY clause freezes the checkpoint information wherever it is, by not updating it. Because a point-in-time recovery is desired, if a backup had to be taken, he would have updated the checkpoint information in datafile headers and control files by using either NORMAL(default) or TEMPORARY option
  • Dictionary Managed Tablespace cause recursive space management that slows down the systens, however it provides individual STORAGE clause to satisfy user custom needs. It has to be coalesced to fee space.
  • Segments in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient.
  • Add more space in an existing TABLESPACE e

  ALTER DATABASE DATAFILE ‘/u1/d.dbf’ RESIZE 10M

  ALTER TABLESPACE tbs ADD DATAFILE ‘/u1/d2.dbf’ size 10M

  • Tablespace can store multiple datafiles on different disks, but don’t provide multiplexing (store same contents in multiple files)
  • Resizing a Tablespace

  A Tablespace can be resized by :

  O Changing the size of a data file

  - Automatically using AUTOEXTEND

  - Manually using ALTER DATABASE

  O Adding a data file using ALTER TABLESPACE

  • Enabling Automatic Extension of Data Files:

  When a data file is created, the following SQL commands can be used to enable automatic extension of the data file:

  - CREATE DATABASE

  - CREATE TABLESPACE ... DATAFILE

  - ALTER TABLESPACE ... ADD DATAFILE

  Query the DBA_DATA_FILES views to determine whether AUTOEXTEND is enabled.

Oracle9i DBA -- Database: Fundamentals I (7)

Maintaining Redo Log Files

##CONTINUE##

• The minimum size of a online redo log file is 50 KB. The maximum size is specific to the OS.
• Redo log files are used only for recovery. Rollback segments provide the database with a read consistency method.
• Archiving can be accomplished automatically by ARCn and manually through SQL statements. They can be multiplexed.
• FAST_START_MTTR_TARGET parameter 700 : Recovery from an instance failure will not take more than 700 seconds. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified.
• Archived Redo Log files can be accomplished automatically by ARCn and the maximum number of ARCn processes in an instance is 10
• If the ARCn cannot write to the archive destination, oracle will write a message to the alert file and all database operations are stopped. Operations are resumed after a successful writing operation on the archived log file.
• V$LOGFILE the value of the STATUS column could be one of the following:

  O INVALID indicates that the file is inaccessible.

  O STALE indicates that contents of the file are incomplete.

  O DELETED indicates that the file is no longer used.

  O Blank indicates that the file is in use.

• Automatic archiving can be set by the parameter LOG_ARCHIVE_START = TRUE which is available in V$PARAMTER c is used to automate ARCn process only when the database is already in the ARCHIVELOG mode.
• The ALTER DATABASE ARCHIVELOG is used when database is in mount stage.
• %S specifies the log sequence number with leading zero filled. Eg: arch_%s -> arch_0000001
• The CKPT writes checkpoint information in control files and data file headers.
• The ARCHIVELOG mode does not start the ARChiver process, rather it states that the database may use archive logs. Hence it ensures that the complete recovery is possible even if the redo logs are overwritten through a log switch.
• LOG_CHECKPOINT_TO_TRACE = true means that the information about the log switches will be stored in alert_sid.ora file.

• When the oracle server starts, it assigns the same sequence number to a group, that may contain multiple members and all of them have the same sequence number.

Oracle9i DBA -- Database: Fundamentals I (6)

Maintaining the Control File

##CONTINUE##

• Multiplexing the control file using spfile step

1. Alter the spfile

2. Showdown normal

3. Create additional control files

4. Start the database

• Multiplexing the Control File Using init.ora(pfile)

1. Shutdown normal

2. create additional control files

3. Add the new control file name to init.ora

4. Start the database

• V$PARAMETER lists the status and location of all parameters (eg. Control files names)

V$CONTROLFILE_RECORD_SECTION provides information about control file record sections.

V$CONTROLFILE provides information about the name and status of all control files.

SHOW PARAMETER CONTROLFILES

• The database has to be in the NOMOUNT state to create the control file.
• The recommended way is to have 3 control file copies on separate disks to provide fault tolerance. The maximum level of multiplexing control files is 8 , minimum is 2. But in OMF only up to 5
• 
• The Redo Log files and Control files creation share the common parameter DB_CREATE_ONLINE_LOG_DEST_n parameter through OMF, while datafiles use DB_CREATE_FILE_DEST.
• DB_CREATE_FILE_DEST sets the default location for datafile, control file, and online log creation.
• When available control file is failed, Recover the database using SQL>STARTUP RECOVER or RMAN>RECOVERY DATABASE

Oracle9i DBA -- Database: Fundamentals I (5)

Data Dictionary Content and Usage

##CONTINUE##

• The DICTIONARY view can also be queried by using its synonym DICT. And also static view.
• Data Dictionary Views
  1. DBA_ : show what is in all schemas.
     
  2. USER_ : show what is in the user’s schema
  3. ALL_ : show what the user can access
• User SYS is the owner of the dynamic performance tables. User SYSTEM is the owner of additional tables and views.
• To get an overview of the columns in the data dictionary and dynamic performance views, the DICT_COLUMNS view can be queried. To get an overview of the data dictionary and dynamic performance views, the DICTIONARY view or its synonym DICT can be queried.
• Information about tables can be obtained by querying the data dictionary. 1. DBA_TABLES 2.DBA_OBJECTS
• Scripts starting with :
  1. cat*.sql are used to create data dictionary views.
  2. Dbms*.sql are used for database package specification
  3. Utl*.sql are used to create additional views and tables.
  4. Prvt*plb are used to creating database package code.
• Base table are created using sql.bsq script and is automatically run at the time of database creation. The data dictionary views are created using the catalog.sql script.
• The control files contains such information as location of datafiles, redo log files, dynamic performance view, etc.
• Dynamic performance views obtain availability of an object, information about locks, active state of a session
• The V$PWFILE_USERS view can be used to gather information on all users with SYSDBA or SYSOPER privileges.
• V$LOG status field shows CLEARING when an ALTER DATABASE CLEAR LOGFILE command is issued. CLEARING_CURRENT means the current logfile is being cleared of a closed thread.
• LOG_CHECKPOINT_INTERVAL specifies the number of redo blocks after which the checkpoint will be caused
• LOG_CHECKPOINT_TIMEOUT indicates the seconds to resist before causing a checkpoint.
• FAST_START_IO_TARGET indicates the target IO manipulation.
• The AUD$ is a base table. Base tables end up with a $ mark and are created at the time of db create.
• The USER_SOURCE data dictionary view contains the actual program text used to write a stored procedure.
• 
• The data dictionary provides information about: Logical and physical database structure, Definitions and space allocations of objects, Integrity constraints, Users, Roles, Privileges, Auditing.

Oracle9i DBA -- Database: Fundamentals I (4)

Creating a Database

##CONTINUE##

• Redo Log Buffers, Tablespaces, Datafiles can be viewed details of the database defined by a template.
• REMOTE_LOGIN_PASSWORD

  1. None : remote login is not enabled

  2. Exclusive : remote login accessibility is exclusive

  3. Shared : remote login accessibility is shared

• The maximum log file groups is specified in the MAXLOGFILES
• The ORAPWD utility is used to create the password file.
• Oracle Database Configuration Assistant is used for creation of the database. Oracle Enterprise Manager provides various administering tools. Oracle Database Migration Assistant is used to migrate from an earlier version of the database.
• The various ways of creating a database are by using ORACLE Universal Installer, ODCA and the create database command.
• These environment variable is needed to be set while creating a database. ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH, PATH, ORACLE_BASE, ORA_NLS33
• In OMF %g -> Log Group names for Redo logs

  %t -> the tablespace name in data file name

  %u -> eight-character string that guardantees uniqueness

• Create a database the instance must be started in the nomount mode.
• Consider the characteristics of the data to be stored before determining the structure appropriate for your database, in order to:
  1. Minimize fragmentation
  2. Minimize disk contention
  3. Separate objects

• 
  

Oracle9i DBA -- Database: Fundamentals I (3)

Managing an Oracle Instance

##CONTINUE##

• IFILE can additional files be added to the PFILE.
• STARTUP and SHUTDOWN of database are the option available to SYSDBA and SYSOPER.
• IFILE is a handy way to modularize and version the parameter files, but there is a limit of specifying only three nesting levels. If exceeded Oracle will generate an error.
• DB_NAME is a database identifier of eight characters or less.
• V$SESSION obtained the information about SID and the Serial#
• The alert_SID.log location is defined by the BACKGROUND_DUMP_DEST initialization parameter. The default location on Unix is $ORACLE_HOME/rdbms/log.
• In the read-only mode DB recovery can take place.
• PROCESS initialization parameter: It determines the maximum no of OS processes that can connect to the instance.It determines the value for the number of background processes.
• For maximum compatibility the UNDO_MANAGEMENT INITIALIZATION parameter of Oracle 9i Database should to be set to manual.
• LICENCE_MAX_SESSIONS is used to define the number of concurrent sessions that are allowed to connect to the 9i DBA.
• USER_DUMP_DEST directory has the trace files generated by user sessions, specifies location for user process errors.User trace file size is determined by MAX_DUMP_FILE_SIZE, default is 10MB. CORE_DUMP_DEST is used on unix platforms to save the core dump files. BACKGROUND_DUMP_DEST is used to set the directory path where the alert log file is written. Log background process errors.
• Instance failure is automatically fixed by SMON process, when the instance is restarted by using STARTUP OPEN command on the SQL or the Server Manager.
• SPFILE doesn’t contain statements that are only comment lines, while PFILE may have complete comments.
• MOUNT is the level where Control file is read, Data files are read at OPEN level.
• The initialization parameter is in ORACLE_HOME\dbs
• STARTUP FORCE will always abort a running instance before performing a normal startup. Can be used to reconnect to an already connected and running instance, and requires instance recovery.

Oracle9i DBA -- Database: Fundamentals I (2)

Getting Started with Oracle Server
##CONTINUE##

• According to Oracle’s recommended approach, the first task that the DBA has to perform is evaluation of the server hardware.
• The default initialization file is created in the ORACLE_BASE/admin/pfile
• Using Oracle in a non-interactive mode is runInstaller –responsefile -silent
• The SYSTEM user is used to create the internal views used by various Oracle tools

• Before starting the DBCA the user accounts should either be locked or if that is not done then the accounts should be assigned new passwords.
  

Oracle9i DBA -- Database: Fundamentals I (1)

Oracle Architectural Components
##CONTINUE##

• OSDBA groups allows its members to connect to 9i using the SYSDBA privileg0065
• Tablespace -> Segment -> Extent -> DB blocks
• The value of ORACLE_HOME are represented using ‘?’ symbol.
• The SGA_MAX_SIZE is used to specify the max size of the SGA.
• The share pool consists of the most recently executed SQL statements and the most recently used data definitions. The Database Buffer Cache contains copies of the database files, the redo log buffer records all changes made to the database blocks and the Program Global Area contains the data and control information for single server process or single background process.
• The data dictionary cache is also referred to as the dictionary cache or row cache.
• The database buffer cache stores copies of data blocks that have been retrieved from the data files. Consists of independent sub-caches:

  1. DB_CACHE_SIZE : Sizes the default buffer cache size only, it always exists and cannot be set to zero. Size of the buffer cache in Oracle SGA.

  2. DB_KEEP_CACHE_SIZE : Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused.

  3. DB_RECYCLE_CACHE_SIZE : Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused.

• DB_BLOCK_BUFFERS : Number of blocks cached in the SGA
• The size of each buffer in the buffer cache is equal to the size of an Oracle block, and it is specified by the DB_BLOCK_SIZE parameter. The database buffer cache consists of independent sub-caches for buffer pools and for multiple block sizes. The parameter DB_BLOCK_SIZE determines the primary block size, which is used for the SYSTEM tablespace.
• Diagnostic files contain information about significant events encountered while the instance is operational.

  1. alertSID.log file

  2. Background trace files : Vital information when background processes, such as SMON, PMON, DBWn, and others fail.

  3. User trace files : Vital information for fatal user errors or user forced traced files.

• The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).
  

Oracle9i DBA -- Database: Fundamentals I (1Z0-031)

Oracle 9i DBA 認證考試 -- Database: Fundamentals I (1Z0-031)

要成為 Oracle 9i OCA 必須通過此門考試, 但此門考試是 DBA 認證考試中最簡單的一個考試, 當初去考試的時候約30分鐘就做完了~~ 隨後幾篇是當初準備考試的筆記, 大概念完就可以去考了~^Q^

以下是此門考試的內容:
##CONTINUE##

• Oracle Architectural Components
• Getting Started with Oracle Server
• Managing an Oracle Instance
• Creating a Database
• Data Dictionary Content and Usage
• Maintaining the Control File
• Maintaining Redo Log Files
• Managing Tablespace and Datafiles
• Storage Structure and Relationships
• Managing Undo Data
• Managing Tables
• Managing Indexes
• Maintaining Data Integrity
• Managing Password Security and Resources
• Managing Users
• Managing Privileges
• Managing Roles
• Using Globalization Support
• Auditing
• Loading data into a database
• Other