Datafiles and Tablespaces

Datafiles

Every datafile has two associated file numbers:

An absolute file number uniquely identifies a datafile in the database, Arelative file number uniquely identifies a datafile within a tablespace

At least one datafile is required for the SYSTEM tablespace of a database. You can add datafiles to tablespaces, subject to the datafile limits:

Operating system limit, Oracle system limit, Control file upper bound, Instance or SGA upper bound.

The first datafile must be at least 7M to contain the initial data dictionary and rollback segment. Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Datafiles should not be stored on the same disk drive that stores the database's redo log files. When creating a tablespace, you should estimate the potential size of the database objects and add sufficient files or devices accordingly. To add datafiles to a tablespace, you use the ALTER TABLESPACE...ADD DATAFILE statement. You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed. This will:

Reduce the need for immediate intervention when a tablespace runs out of space. Ensure applications will not halt because of running out of space.

You may find out if a datafile is auto-extensible by querying DBA_DATA_FILES. You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles. You can enable or disable automatic file extension for existing datafiles using the SQL statement ALTER DATABASE. You can manually resize a datafile using the SQL statement ALTER DATABASE. Offline datafiles cannot be accessed. Files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE statement. If you want to bring a datafile online or take it offline, you must have the ALTER DATABASE system privilege.

To bring a datafile online or take it offline, the database must be opened in exclusive mode.

Tablespaces

You may use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE to create a new tablespace You may use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace With Oracle8i, you can create locally managed tablespaces Locally managed tablespaces use bitmaps but NOT SQL dictionary tables to track used and free space. temporary tablespaces:

• improve the concurrence of multiple sort operations,
• reduce multiple sort operations overhead,
• avoid Oracle space management operations.

All tablespaces are initially created as read-write you may use the READ ONLY keywords in the ALTER TABLESPACE statement to change a tablespace to read-only you may use the READ WRITE keywords in the ALTER TABLESPACE SQL statement to change a tablespace to allow write operations SYSTEM tablespace can never be made read-only. Read-only tablespace:

prevents updates on all tables in the tablespace regardless of a user's update privilege level, eliminate the need to perform backup and recovery of large, static portions of a database, provide a means of completely protecting historical data.

Any tablespace except the SYSTEM tablespace can be dropped. Once a tablespace has been dropped, the tablespace's data is not recoverable. Back up the database completely immediately before and after dropping a tablespace When dropping a tablespace, only the file pointers in the control files are dropped. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate operating system commands Partitioned Tables cannot be moved via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces.

Storage Parameters

PCTFREE

set the percentage of a block to be reserved for possible updates to rows that already are contained in that block, after the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED, can specify any integer between 0 and 99 (inclusive) for PCTUSED, the sum of PCTUSED and PCTFREE should not exceed 100.

smaller PCTFREE:

Reserves less room for updates, Allows inserts to fill the block more completely, suitable for a segment that is rarely changed.

larger PCTFREE:

Reserves more room for future updates, May require more blocks for the same amount of inserted data, May improve update performance, suitable for segments that are frequently updated.

PCTUSED

smaller PCTUSED: Reduces processing costs incurred during UPDATE and DELETE, Increases unused space.

larger PCTUSED: Improves space efficiency, Increases processing cost during INSERTs and UPDATEs.

Other parameters:

INITIAL - size, in bytes, of the first extent allocated when a segment is created

NEXT- size, in bytes, of the next incremental extent to be allocated for a segment

PCTINCREASE – percentage by which each incremental extent grows over the last incremental extent allocated for a segment 0 means all incremental extents are the same size it cannot be negative.

MINEXTENTS - total number of extents to be allocated when the segment is created

MAXEXTENTS - total number of extents, including the first, that can ever be allocated for the segment FREELIST GROUPS - number of groups of free lists for the database object you are creating FREELISTS - number of free lists for each of the free list groups for the schema object

OPTIMAL – for use with rollback segments

BUFFER_POOL – defines a default buffer pool (cache) for a schema object Not valid for tablespaces Not valid for rollback segments.Rollback Segments

Private rollback segment: acquired explicitly by an instance when the instance opens the database if it is named in the ROLLBACK_SEGMENTS parameter in the initialization parameter file, form a pool of rollback segments for use by that any instance requiring a rollback segment, database with the Parallel Server option can have only public segments, public and private rollback segments are identical on database with no Parallel Server option, When an instance starts, it acquires TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments by default, To ensure that the instance acquires particular rollback segments that have particular sizes or particular tablespaces, specify the rollback segments by name via the ROLLBACK_SEGMENTS parameter. Recommended:
create one tablespace specifically to hold all rollback segments, separated with the data.

V$ Table Reference

DATABASE BACKUPS, ARCHIVE FILES, AND RECOVERY

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$BACKUP
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$DELETED_OBJECT
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_STATUS
V$RECOVER_FILE

CACHE MANAGEMENT

V$CACHE
V$DB_OBJECT_CACHE
V$LIBRARYCACHE
V$ROWCACHE
V$SUBCACHE

CONTROL FILES

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

CURSORS AND SQL STATEMENTS

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_SHARED_MEMORY

DATABASES AND INSTANCES

V$ACTIVE_INSTANCES
V$BGPROCESS
V$BH
V$COMPATIBILITY
V$COMPATSEG
V$COPY_CORRUPTION
V$DATABASE
V$DATAFILE
V$DATAFILE_COPY
V$DATAFILE_HEADER
V$DBFILE
V$DBLINK
V$DB_PIPES
V$INSTANCE
V$LICENSE
V$OFFLINE_RANGE
V$OPTION
V$SGA
V$SGASTAT
V$TABLESPACE
V$VERSION

SQL*LOADER DIRECT LOAD OPTION

V$LOADCSTAT
V$LOADPSTAT
V$LOADTSTAT

FIXED VIEWS

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

I/O

V$FILESTAT
V$WAITSTAT

LATCHES AND LOCKS

V$BUFFER_POOL
V$CACHE_LOCK
V$CLASS_PING
V$DLM_CONVERT_LOCAL
V$DLM_CONVERT_REMOTE
V$DLM_LATCH
V$DLM_MISC
V$ENQUEUE_LOCK
V$EVENT_NAME
V$FALSE_PING
V$FILE_PING
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$PING
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1

MISCELLANEOUS

V$TIMER
V$TYPE_SIZE
V$_SEQUENCES

MULTI-THREADED AND PARALLEL SERVERS

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$MTS
V$QUEUE
V$REQDIST
V$SHARED_SERVER
V$THREAD

OVERALL SYSTEM PERFORMANCE

V$GLOBAL_TRANSACTION
V$OBJECT_DEPENDENCY
V$SHARED_POOL_RESERVED
V$SORT_SEGMENT
V$SORT_USAGE
V$STATNAME
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TRANSACTION

PARALLEL QUERY OPTION

V$EXECUTION
V$EXECUTION_LOCATION
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT

ORACLE PARAMETERS

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$PARAMETER
V$SYSTEM_PARAMETER

REDO LOGS

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY

ROLLBACK SEGMENTS

V$ROLLNAME
V$ROLLSTAT

SECURITY AND PRIVILEGES

V$ENABLEDPRIVS
V$PWFILE_USERS

SESSIONS

V$ACCESS
V$MYSTAT
V$PROCESS
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSTAT
V$SESS_IO

9i Architecture

The machine running your Oracle database has an Oracle database and an Oracle instance. The Oracle Database is the setof files containing the data. The Oracle Instance is the background processes and memory structures that act on the database.

The instance is identified through an environment variable ORACLE_SID in the operating system. The instance can therefore only reference one database at any time.

Memory Structures

The Oracle Instance Memory Structure can be broken down into three main components.

• The System Global Area (SGA)
• The Large Pool
• The Program Global Area (PGA)

SGA

The System Global Area, in turn, is comprised of a group of shared memory structures.

• The Shared Pool
• Database Buffer Cache
• Redo Log Buffer
• Java Pool

The SGA is also known as the Shared Global Area. The Shared Pool is further divided into two sections:

• Library Cache
• Row Cache

The Library Cache contains all the recently executed SQL statements, the execution plans, parse trees etc.

The Row Cache holds the most recently accessed database object definitions, files, indexes, tables, users, privileges, views, etc... A statement being parsed will be processed here to resolve object names and privileges.

Database Buffer Cache

Redo Log Buffer

Java Pool

The Large Pool

PGA

Background Processes

PMON

SMON

LGWR

DBWR

CKPT

ARCH

RECO

LCKn

Pnnn

SNPn

S000 - S999

D000 - D999

Authentication

The following methods for authenticating replace the CONNECT INTERNAL syntax

• operating system authentication
• Password files

CONNECT INTERNAL continues to be supported for backward compatibility. The OSOPER and OSDBA roles can be granted to a user through the operating system, NOT through a GRANT statement. The OSOPER and OSDBA roles cannot be revoked or dropped.

OSOPER permits user to perform: STARTUP and SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, RECOVER

OSDBA: contains all system privileges with ADMIN OPTION, contains the OSOPER role, permits CREATE DATABASE and time-based recovery.

You can create a password file using the password file creation utility

ORAPWD. You may set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to: NONE - causes Oracle to behave as if the password file does not exist. It is the default value. EXCLUSIVE - the password file can be used with only one database. Only such file can contain the names of users other than SYSOPER and SYSDBA. SHARED - the password file can be used by multiple databases. Only SYSDBA and SYSOPER are recognized by a SHARED password file.

Control Files every Oracle database has one created at the same time as the database, you should create two or more copies for redundancy, records the physical structure of the database, contains the following:

• Database name
  
• Names and locations of associated databases and online redo log files
  
• Timestamp of the database creation
  
• Current log sequence number
  
• Checkpoint information.

You can create a new control file for a database using the CREATE CONTROLFILE statement.

You MUST create a new control file in order to change one of the permanent database settings, such as:

• database's name
  
• MAXLOGFILES
  
• MAXLOGMEMBERS
  
• MAXLOGHISTORY
  
• MAXDATAFILES
  
• MAXINSTANCES

To drop control files from the database (steps):

1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database's initialization parameter file to delete the old control file's name.
3. Restart the database.

Initialization Parameters

DB_NAME and DB_DOMAIN - for creating a database's global database name

example: DB_NAME = MYTEST DB_DOMAIN = US.ABC.COM.

CONTROL_FILES holds a list of control filenames to use for the new database,

if you want Oracle to create new operating system files, make sure that the filenames listed do not match any filenames that currently exist on your system, and vice versa when there is no filenames listed, Oracle uses a default filename. You should list at least two filenames here.

DB_BLOCK_SIZE default data block size for every Oracle server is operating system-specific, determines the size of the database buffers in the buffer cache of the System Global Area (SGA). Larger data block size provides greater efficiency in disk and memory I/O, block size is set during database creation, block size cannot be changed after database creation, except by re-creating the database.

DB_BLOCK_BUFFERS determines the number of buffers in the buffer cache in the System Global Area (SGA), larger cache sizes reduce the number of disk writes of modified data, large cache consumes memory and induce memory paging, you should beforehand estimate the number of data blocks that your application accesses most frequently

PROCESSES determines the maximum number of operating system processes that can be connected to Oracle concurrently, you need 5 for the background processes and 1 for each user process.

ROLLBACK_SEGMENTS a list of the rollback segments an Oracle instance acquires at database startup.

Database Administration

Oracle Database Configuration Assistant (DBCA) launched by the Oracle Universal Installer, can automatically create a starter database for you, provides the simplest means of creating a database.

Create the database manually from a script edit the sample database creation script and the sample initialization parameter file to suit your needs.

Two user accounts are automatically created with the database. SYS - initial password: CHANGE_ON_INSTALL and SYSTEM - initial password: MANAGER - they are granted the DBA role.

CATALOG.SQL creates the views of data dictionary tables, creates the dynamic performance views.

CATPROC.SQL establishes the usage of PL/SQL functionality, creates many of the PL/SQL Oracle supplied packages.

Licensing facilities: related initialization parameters in your initialization parameter file:

• LICENSE_MAX_SESSIONS
• LICENSE_SESSIONS_WARNING
  
• LICENSE_MAX_USERS.

Starting an instance without mounting a database: does not allow access to the database, usually done only for database creation or the recreation of control files.

Starting the instance and mount the database: leave the database closed, does not allow general access to the database.

Starting the instance, and mount and open the database: unrestricted mode: allows access to all users, restricted mode: allows access for database administrators only, all users with the CREATE SESSION system privilege can connect to an open database in unrestricted mode, in restricted mode, database access is allowed only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege, to place a running instance in restricted mode, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause.

Startup Force do so only if you cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands , OR when you experience problems when starting an instance.

Starting an instance and mount the database in either exclusive or shared mode: possible only if your operating system supports the Oracle Parallel Server (OPS).

Normal database shutdown: No new connections are allowed, Oracle waits for all currently connected users to disconnect from the database before actually shutting down, at next startup there will be no instance recovery procedures, use the SHUTDOWN command with the NORMAL option.

Immediate database shutdown: should be used in the following situations: A power shutdown will occur, The database is functioning irregularly, Any uncommitted transactions are rolled back, Oracle does not wait for users currently connected to the database to disconnect, at next startup there will be no instance recovery procedures, use the SHUTDOWN command with the IMMEDIATE option.

Planned shutdown of an instance allow active transactions to complete first, no client can start a new transaction on this instance, does not require all users to log off. use the SHUTDOWN command with the TRANSACTIONAL option.

Shut down a database instantaneously via aborting, do so if the database is functioning irregularly and neither of the other types of shutdown works, or if you experience problems when starting the database instance, current client SQL statements being processed are immediately terminated. uncommitted transactions are not rolled back. implicitly disconnects all connected users – no waiting at next startup there will be instance recovery procedures

Oracle Enterprise Manager a separate Oracle product, combines a graphical console, agents, common services, and tools, can be used for administering your database, can be used for starting up and shutting down instances.

Initialization parameter file text file contains a list of instance configuration parameters, often named INIT.ORA or INITsid.ORA, where sid is operating system specific, each Oracle database has at least one initialization parameter file that corresponds only to that database, client for accessing the database must be able to read a database's initialization parameter file to start a database's instance.

Oracle Processes

Database writer (DBWn) writes modified blocks from the database buffer cache to the datafiles one is sufficient for most systems you can configure additional processes to improve write performance valid range: DBW1 through DBW9 use the initialization parameter DB_WRITER_PROCESSES.

Log Writer (LGWR) writes redo log entries to disk sequentially.

Checkpoint (CKPT) happen at specific times all modified database buffers in the system global area are written to the datafiles responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database.

System Monitor (SMON) performs crash recovery when a failed instance starts up again cleans up temporary segments that are no longer in use recovers dead transactions skipped during crash and instance recovery coalesces free extents within the database's dictionary-managed tablespaces.

Process Monitor (PMON) performs process recovery when a user process fails responsible for cleaning up the cache responsible for freeing resources previously used checks on dispatcher and server processes and restarts them if they have failed.

Archiver (ARCn) copys the online redo log files to archival storage performs copying when redo log files are full or a log switch occurs.

Recoverer (RECO) resolve distributed transactions that are pending.

Dispatcher (Dnnn) present only when the multi-threaded server (MTS) configuration is used.

Lock (LCK0) works in an Oracle Parallel Server provides inter-instance locking.