Chapter 25 – Diagnosing Oracle Database Issues

Intro

• There are two distinct sources for alert info in Oracle
  • Background processes – these create trace files and add data to alert logs. This cannot be configured.
  • Database alert system – runs from within the database and is completely configurable

The Alert Log

• As soon as an instance starts, critical messages are written to the alert log (and trace files) in the BACKGROUND_DUMP_DEST for which the OS directory must exist and the oracle user must have write permissions.
  • The format of the file is alert_<SID>.log
  • The directory can’t be the root directory
• Messages included in alert log
  • Startup and shutdown – moves from nomount to mount to open, etc.
  • All changes to physical structure of db – creating, dropping, renaming datafiles, logfiles, resizing & taking datafiles offline or online
  • Tablespace operations like DROP and CREATE; putting tablespaces into and out of hot backup mode for user backups.
  • Log switches and archives
  • Nondefault instance parameters used to start instance; alter system commands
  • There are no DML or DDL commands in the alert log.
• Common messages:
  • Checkpoint incomplete – when a checkpoint occurs and a previous checkpoint has not yet completed.
  • Unable to open file – when a problem exists with a controlfile or controlfile copy (-> MOUNT) or a problem exists with a redo log or datafile (-> OPEN)
  • Corrupt block – address of a corrupt datafile block
  • Problems with archiving – archivelog destination full
  • Deadlocks – sessions are blocking each other

Background Trace Files

• Background processes will write out data to the BACKGROUND_DUMP_DEST when they encounter problems.
• The size of the trace file is limited by MAX_DUMP_FILE_SIZE.
• All trace files write out their own file name, the version of Oracle running on the server and operating system information to the beginning of the file before writing out the trace info.

Server Generated Alerts

• Completely configurable alert system that monitors db, instance, user sessions and issues warnings when limits are reached and events occur.
• MMON (Manageablility Monitor) and MMNL (Manageablility Monitor Light) send out alerts based on configured metrics.
• Different metrics are calculated with different frequency.
  • Disk space is calculated every 10 mins
  • CPU usage is calculated every second

Alert Types

• threshold / stateful – alerts that build up over time and persist until cleared (disk space)
• nonthreshold / stateless – real-time alerts that don’t persist (snapshot too old)

The Alert System Architecture

• The instance accumulates statistics used by the alert system and writes them to disk (AWR) every hour.
• MMON issues alerts to the ALERT_QUE which is visible by many sessions via subscription.
  • The main subscriber to the ALERT_QUE is the DB EM Daemon which displays the alerts and can send out emails, if configured.
• The alert log file is completely separate from the Alert System

Viewing Alert Information

• Alert information can be viewed by querying:
  • DBA_OUTSTANDING_ALERTS
  • DBA_ALERT_HISTORY

Setting Up Notification Rules

• Can be configured through DB Control

Editing Thresholds

• Can be done via DB Control in the Manage Metrics link from the Related Links section or the dbms_server_alert package.
• Examples:
  • dbms_server_alert.tablespace_pct_full
  • dbms_server_alert.operator_ge – greater or equal
  • dbms_server_alert.object_type_tablespace

User Trace Files

• Background trace files are created automatically when a problem occurs while user trace files are created on demand.
• User tracing can be used to detect what SQL a user is running for situations where his session may be running slow.
• Shared servers and web applications may cause tracing problems because sessions are the same for many users. However, it is still possible to trace a session in Shared Server Mode by tracing the SID and SERIAL# that uniquely identify the session.

Instance Level SQL Tracing

• Can be enabled by setting the parameter SQL_TRACE to true and will be output in the USER_DUMP_DEST.
• The files will be named: <SID>_ora_<process_id>.trc
• To find the process id / username of the session that produced a trace file, you can join the V$SESSION & V$PROCESS views.
• SQL> alter system set sql_trace=true;

Session Level SQL Tracing

• Output in USER_DUMP_DEST.
• SQL> execute dbms_monitor.session_trace_enable(session_id=>162,serial_num=>14);
  • To get session_id:
    • SQL> select sid, serial# from v$session where username=’JOHN’;
• SQL> alter session set sql_trace=true; // only works if user is in SQL*Plus

Tracing with DB Control

• From the db control home page, Performance tab -> Top Consumers. You will see activity by session, module, service name, web client ID, or PL/SQL procedure.

Parameters

• BACKGROUND_DUMP_DEST [dyn] – location of the alert log
• USER_DUMP_DEST [dyn] – location of user trace files
• MAX_DUMP_FILE_SIZE [dyn] – defaults to UNLIMITED and is the max size for a dump file (does not apply to alert log)
• SQL_TRACE [dyn] – defaults to FALSE

Tables & Views

• DBA_OUTSTANDING_ALERTS – alerts yet to be cleared
• DBA_ALERT_HISTORY – alerts that have been cleared or were stateless
• DBA_EXTENTS – helps in determining block corruption
• V$ALERT_TYPES – configurable alerts
• V$SESSION – session info that can be used for determining who created user trace files
• V$PROCESS – session info that can be used for determining who created user trace files

Chapter 24 – Using RMAN to Back Up Databases

Intro

• There are four interfaces for using RMAN:
  • DB Control / Enterprise Manager
  • Batch mode / cron
  • Pipe interface
  • RMAN executable

RMAN in Batch Mode

• run {
• allocate channel t1 type sbt_tape;
• …
• backup copy of database delete input;}

The RMAN Pipe Interface

• Uses the PL/SQL package DBMS_PIPE which allows one database session to communicate with another. It does this by creating a data structure called a pipe in the SGA. One session will queue messages in the Pipe while the other will pick up these messages for execution. Output will be redirected to the controlling session.
• To create PIPEs:
  • rman PIPE rmpipe target sys/pass@ocp10g
  • Two pipes will be created:
    • ORA$RMAN_RMPIPE_IN – user input
    • ORA$RMAN_RMPIPE_OUT – user output
  • dbms_pipe.pack & dbms_pipe.send_message will be used by the controlling process.
  • dbms_pipe.unpack & dbms_pipe.receive_message will be used to retrieve output.

Backing Up with the RMAN Executable

Stand-Alone and Job/Command Block Commands

• run {} braces are used for commands that only make sense when run with others
  • run {
  • allocate channel d1 type disk;
  • backup database;
  • }
• Stand alone commands:
  • Connect

  Creating Backup Sets

  • RMAN can back up objects in two ways:
    • Image copy – identical binary copy of file
    • Backupset – format that can only be manipulated by Oracle
      • Controlfile
      • Spfile
      • Datafiles
      • Archive logs
      • Image copies
      • Other backupsets
  • It is quicker to restore from image copies but image copies are bigger and can only be written to disk (not tape).
  • Backup sets can make incremental backups and are the only option when backing up to tape.
  • Backup database creates a backupset with the datafiles, controlfile and spfile.
  • BACKUP DATABASE PLUS ARCHIVELOG causes the following actions to occur:
    • ALTER SYSTEM ARCHIVE LOG CURRENT
    • BACKUP ARCHIVELOG ALL
    • BACKUP WHOLE DB
    • ALTER SYSTEM ARCHIVE LOG CURRENT
    • Backup archive logs generated in steps 3 and 4.
  • Script to backup all archive logs older than 7 days
    • run {
    • allocate channel t1 type sbt;
    • backup as backupset archivelog until time ’sysdate -7′;}
  • Offline backups must be conducted in MOUNT mode. They also will fail if the database crashed or was shutdown aborted.
  • If the archive logs are multiplexed then the following commands will behave differently:
    • RMAN> backup as backupset archivelog all DELETE INPUT;
      • This will delete only archivelog copies in primary destination.
    • RMAN> backup as backupset archivelog all DELETE ALL INPUT;
      • This will delete all multiplexed archivelog copies.

  Making Image Copies

  • Image copies are exact binary copies, equivalent to an OS copy and paste. They cannot be backed up to tape.
  • Image copies can be made of:
    • Datafiles
    • Controlfile
    • Archive logs
    • NOT the spfile
  • Restores from image copies are faster then backupsets because they do not have to be extracted / decompressed.
  • The entire db can be backed up as an image copy:
    • RMAN> backup as copy database;

  Using Tags for Backup Sets and Image Copies

  • A tag is an assigned name to a backupset or a set of image copies.
  • RMAN> backup [as backupset] database tag=weekly;
  • RMAN> delete backupset tag weekly;

  Incremental Backups

  • Incremental backups are very dependent on SCN. Whenever a backup is taken, it is marked in the repository as having been taken at a particular SCN.
  • To determine what needs to be backed up for incrementals, rman only has to compare the scn of the last backup with the current scn of the db to see if data has been added.
  • Incremental backups only apply to datafiles because they rely on SCN.
  • Reasons to use incrementals:
    • Time – incrementals run quicker than a full backup every night with block change tracking enabled
      • If block change tracking is not enabled, then incrementals are not faster during the read but are writing less data to disk which is often the slowest portion (so in that way, it’s faster).
        
    • Space – disk space and tape backup space can be a problem if a full backup occurs every night
    • Impact on end users – the I/O needed for a full backup and disk contention can impact end users
  • Incremental backup levels:
    • Level 0 – full backup that can be used for subsequent incrementals
      • RMAN> backup incremental level 0 database;
    • Differential Level 1 – only the blocks that have changed since the last backup (whether it is level 0 or level 1)
      • RMAN> backup incremental level 1 differential database;
    • Cumulative Level 1 – all changes since the last level 0 incremental backup
      • RMAN> backup incremental level 1 cumulative database;
  • A full backup cannot be used for a cumulative level 1 backup. A cumulative level 1 backup must be done on top of an incremental level 0 backup.

  Combining Incremental Backups and Image Copies

  • It is possible to apply incremental backups to image copies (but not backupsets).
  • One technique would be an image copy once a week and incrementals, nightly.
  • Image copy:
    • run { backup as copy incremental level 0 database tag db_img_copy;}
  • Make incremental, apply it to copy of database, delete incremental:
    • run {
    • backup incremental level 1 for recover of copy with tag db_img_copy database tag db_incr_copy;
    • recover copy of database with tag db_img_copy;
    • delete backupset tag db_incr_copy;}

  Block Change Tracking

  • Incrementals will scan the entire database being backed up in order to detect changes, unless block change tracking is enabled.
  • Block change tracking enables an additional background process called CTWR (Change Tracking WRiter). This process records the address of each block that has been changed so that incrementals will no longer have to scan entire datafiles to find changed blocks.
    • By default, the change tracking file will go to the DB_CREATE_FILE_DEST. It is initially 10M and grows by increments of 10M.
    • SQL> alter database enable block change tracking using file ‘/home/oracle/ctwr.out’;

  Managing and Monitoring RMAN Backups

  LIST

  • RMAN> list backup; // backup sets
  • RMAN> list copy; // image copies
  • RMAN> list backup of database; // whole database backup sets, full or incremental
  • RMAN> list backup of datafile 1;
  • RMAN> list backup of tablespace users;
  • RMAN> list backup of archivelog all;
  • RMAN> list copy of archivelog from time=’sysdate – 2′;
  • RMAN> list backup of archivelog from sequence 1000 until sequence 1050;

  REPORT

  • RMAN> report schema; // “schema” is confusing. this just shows datafiles and has nothing to do with user objects.
  • RMAN> report need backup;
  • RMAN> report need backup days 3;
  • RMAN> report need backup redundancy 3;
  • RMAN> report obsolete;
  • RMAN> report obsolete redundancy 2; // shows backups that are older than two others

  DELETE

  • RMAN> delete obsolete;
  • RMAN> delete obsolete redundancy 2; // delete backups that are older than two others

  Views & Tables

  • These views and data are contained in the control file only (not recovery catalog).
  • V$BLOCK_CHANGE_TRACKING – to monitor block change tracking
  • V$BACKUP_FILES
  • V$BACKUP_SET
  • V$BACKUP_PIECE
  • V$BACKUP_REDOLOG – each archived log that has been backed up
  • V$BACKUP_SPFILE – each spfile that has been backed up
  • V$BACKUP_DEVICE – names of sbt devices
  • V$RMAN_CONFIGURATION

  Parameters

  • DB_CREATE_FILE_DEST – default location for the change tracking file

How to Create a Recovery Catalog in 10g

Create a database with dbca called rcat

C:\> sqlplus SYS/pass@rcat AS SYSDBA

SQL> CREATE SMALLFILE TABLESPACE catalog_tbs DATAFILE ‘F:\ORACLE\..\cat_tbs.dbf’ SIZE 20M AUTOEXTEND ON NEXT 5M UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> CREATE USER rcat IDENTIFIED BY rcat DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;

SQL> GRANT recovery_catalog_owner TO rcat;

SQL> Grant connect, resource to rcat;

SQL> exit

C:\> rcat catalog rcat/rcat@rcat

RMAN> create catalog;

RMAN> exit

C:\>rman target orcl catalog rcat/rcat@rcat

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Sep 12 21:21:27 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: RCAT (DBID=373135761)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;

Chapter 23 – Getting Started with Oracle Recovery Manager

Recovery Manager Features

• Backup of database plus archive logs made since the backup will be able to survive any data loss as long as redo log groups, exist.
• RMAN can backup:
  • Whole databases
  • Tablespaces
  • Datafiles
  • Controlfile
  • Spfile (static pfile cannot be backed up)
  • Archive logs
• Online redo logs are only protected by multiplexing and cannot be backed up.
• RMAN can create incremental backups, image copies or backupsets. Image copies cannot be written to tape.
• RMAN validates the contents of blocks as it scans them which will detect block corruption if it occurs.
• RMAN can be integrated with a tape backup library.

Recovery Manager Components

The RMAN Executable

• Can be found in $ORACLE_HOME/bin and is named rman
• RMAN connects to a db via a listener and server process, prompts for user commands and sends the commands to the instance to be executed.
• RMAN can be run remotely so long as the versions of the RMAN executable and target database, match.
• RMAN is implemented with the PL/SQL package DBMS_BACKUP_RESTORE. Since this package is kernelized, or part of the instance (stored in the SGA), you don’t need an open database to use RMAN. Other PL/SQL procedures are stored in the Data Dictionary, which means that you need an open database to use them.

The Target Database

• The target database is the db that you intend to backup, or restore and recover and RMAN can only connect to one target at a time.
• The controlfile of the target db stores information needed by RMAN:
  • Location of the datafiles
  • Backups that have been made in the past
  • RMAN’s persistent config settings
• RMAN also writes to the controlfile of the target database to tell where the backups are written.
• Still, if the target db is totally destroyed, including all copies of the controlfile, it will still be possible for RMAN to restore it, provided that RMAN has been configured correctly.

Server Processes and Channels

• Whenever a backup or restore operation is initiated, then at least one “channel” is launched. Multiple channels will allow for parallelism.
  A channel is a server process that does the work of copying files.
  • A channel can be launched manually via ALLOCATE CHANNEL or RMAN can launch channels automatically by using configured settings.
• Two types of channel processes exist:
  • Disk channels – backup or restore to disk
  • SBT channels – backup or restore to tape
• Another server process is the polling process. This process monitors the progress of the backup or restore operation.

The RMAN Repository

• The repository is always stored in the controlfile of the target database. There is also the option to store it in the Recovery Catalog database, as well.
• The repository in the controlfile / Recovery Catalog tells:
  • Location of the datafiles
  • Backups that have been made in the past
  • RMAN’s persistent config settings
• If the repository is lost, then it may be possible to rebuild it if the proper precautions have been taken.

The Recovery Catalog

• The RMAN repository is always written to the target db’s controlfile, but it can also be written out to a separate Oracle database, called the Recovery Catalog.
• Benefits of a Recovery Catalog
  • Enhances functionality
  • You are no longer dependent on the controlfile of the target db. What if controlfile is damaged / lost?
  • The Recovery Catalog can store RMAN scripts.
  • One Recovery Catalog can be used to store metadata for multiple databases.
  • There is no time limit to how long a Recovery Catalog can store metadata. The controlfile relies on the parameter: CONTROL_FILE_RECORD_KEEP_TIME.

Media Management Library

• To use a tape library, the instance must be linked to a device driver supplied by the tape library vendor.
• Once the driver is linked, it will be possible to launch channels of type SBT_TAPE.
• The Oracle Backup Solutions Program (BSP) certifies tape products as being compatible with RMAN.

The Auxiliary Database

• An auxiliary database is a new database created by RMAN from a backup of the target database.
• The auxiliary db can be a complete restore of the target (identical copy) or a subset of the target (TSPITR – tablespace point in time recovery – where one tablespace is pushed back while the remainder of the db is the same).
• An auxiliary db can also be used to create a standby db. A standby db is an exact replica, stored remotely which is continuously updated from the redo generation of the production db. In the event of a disaster, you can switch over to the standby db.

The Flash Recovery Area

• The flash recovery area is used for all recovery and fault tolerance related files. RMAN uses is as a default location for backups. It is also used for redo logfiles, controlfiles, archivelogs and flashback.

Environment Variables and Instance Parameters

• Environment variables like nls_date_format and nls_lang can effect RMAN decisions.
  • If the db is in mount mode then RMAN will determine settings from the controlfile.
  • If the db is in nomount or closed then it will take the values from the environment variables.
• Three parameters within the instance may be of significance:
  • CONTROL_FILE_RECORD_KEEP_TIME – not very important if using the recovery catalog. The data will be transferred from controlfile to recovery catalog whenever RMAN connects to the target. It uses the RESYNC CATALOG command.
  • DB_RECOVERY_FILE_DEST – location of the flash recovery area. RMAN can apply retention policies to files in this area.
  • DB_RECOVERY_FILE_DEST_SIZE – if it fills up, then the db will hang.

Connecting to Databases

• The rman executable can connect to local dbs or remote dbs
  • C:\> rman target sys/oracle@ocp10g catalog rman/rman@catdb
  • … is the same as …
  • C:\> rman
  • RMAN> connect target sys/oracle@ocp10g
  • connected to target database OCP10G (DBID=54237432)
  • RMAN> connect catalog rman/rman@catdb
  • connected to Recovery Catalog database

Configuring Persistent Settings

• To see the current RMAN persistent settings type: SHOW ALL.

Channel Allocation

• At least one channel must be allocated before a backup or restore can be executed.
• Channels can be allocated for each backup/recovery operation or they can be configured with default settings for all operations.
• Control Options:
  • Connect – the instance to which RMAN connects
  • Duration – controls the amount of time for the job, specified in hours and minutes
    • Minimize time – tells the job to run at full speed
    • Minimize load – tells the job to slow down if it is going to finish within the duration
    • Partial – allows RMAN to terminate a job that hasn’t finished within the duration without signalling an error
  • Format – path and filename with variables like %u or %d.
  • Maxopenfiles – limits number of input files that RMAN can open at once. default is 8.
  • Maxpiecesize – specified in bytes (default), kilobytes (k), megabytes (m) or gigabytes (g). restricts the size of pieces that the backup set is divided into
  • Parms – used to set variables for SBT_TAPE channel

What RMAN Will Back Up

• RMAN can exclude certain dbs by using exclude:
  • RMAN> configure exclude for tablespace example;
• RMAN will also exclude dbs that have not changed since the last backup if optimization is turned on.
  • RMAN> configure backup optimization on;
• If RMAN is not configured to backup the controlfile and all copies are lost, then the target db will never get past the NOMOUNT state and recovery will be impossible.

Configuring Retention of Backups

• The default configuration is redundancy 1 which means that RMAN will always attempt to have one backup of every datafile, archive log and controlfile. Delete obsolete will delete all datafile & controlfile copies but not archive logs.
• It is also possible to set the retention policy to none which will disable automatic deletion aka obsolete files.

Overriding Configured Defaults

• Defaults can be overridden inside of run braces { … }

Parameters

• CONTROL_FILE_RECORD_KEEP_TIME – how long the controlfile will keep metadata. Defaults to 7 days.

Views & Tables

• g

Chapter 21 – Managing Globalization in Oracle Databases

• NLS stands for National Language Support but it entails much more than just language.

Globalization Capabilities

Character Sets

• Some applications (MS Word) use the character sets provided with the OS, while Oracle provides its own character sets which means that it is the same on all platforms.
• Single-byte character sets use only one byte (8 bits or less) per character.
• Multi-byte character sets use one, two or three bytes per character.
• Fixed width character sets use the same number of bytes for each character.
• Varying width character sets use a different number of bytes per character.
• Single-byte seven-bit – uses a single byte per character but only 7 of the 8 bits per character
  • 2^7 total possible characters (128)
  • Usually inadequate
  • Oracle default (US7ASCII)
• Single-byte eight-bit – uses a single byte per character and 8 bits per character
  • 2^8 total possible characters (256)
  • Adequate for Western European languages but perhaps not Eastern Europe and definitely not Asian languages (ex. WE8ISO8859P15)
• Fixed-width multibyte
  • Unicode (AL16UTF16)
• Varying-width single-byte
  • Characters may be from one to four bytes
  • Unicode (UTF8)
• Varying-width multibyte
  • May use shift-out code to indicate that the following bytes are double-byte characters and shift-in code to indicate single-byte characters.

Language Support

• The number of languages depends on platform, release & patch level.
• The view V$NLS_VALID_VALUES shows all possible languages (American, German, French, Canadian French, etc.)
• The parameters NLS_LANGUAGE, NLS_DATE_LANGUAGE & NLS_SORT will determine error message language, date language & sort order, respectively.
  • SQL> select * from v$nls_valid_values where parameter=’SORT’;
• BINARY is the default value for NLS_SORT and is poor for any language besides English. It simply sorts by the ASCII numeric value of the character set.

Territory Support

• The territory selected sets a number of parameter defaults for day and week numbering, credit and debit symbols, date formats (nls_date_format), decimal and group numeric separators (nls_numeric_characters), and currency symbols (nls_currency).

Globalization Support Features

• Can be specified at any and all of five levels:
  • Database
  • Instance
  • Client environment
  • Session
  • Statement

Choosing a Character Set

• The character set is used to store data in columns of type: VARCHAR2, CLOB, CHAR and LONG. Changing the character set can destroy data in these columns.
• The National Character Set is a secondary character set specified at database creation time. It is used for columns of datatype: NVARCHAR2, NCLOB and NCHAR.
  • Must be either AL16UTF16 or UTF8. Other unicode sets are not supported.
• The only limitation on the database character set is that it must have either US7ASCII or EBCDIC as a subset since SQL and PL/SQL are stored in these char sets.
• The default character set is US7ASCII and the default national character set is AL16UTF16 (must be AL16UTF16 or UTF8).

Changing Character Sets

• Two tools exist:
  • Database Character Set Scanner (csscan)
    • csscan system/passwd full=y tochar=utf8
    • checks database to see if problems may arise
    • one possible problem is that the new char set may require more bytes and therefore the new data won’t fit in the column
    • the output will show every row that will have problems with the new char set
  • Language and Character Set File Scanner (lcsscan)
    • checks text files to see what language the data is in
    • must be a straight text file
• To change Character Sets of the DB
  • SQL> ALTER DATABASE CHARACTER SET …
  • SQL> ALTER DATABASE NATIONAL CHARACTER SET …
  • The new Character Set must be a superset of the old set but this does not guarantee no corruptions

Globalization Within the Database

• SQL> select * from nls_database_parameters;

Globalization at the Instance Level

• Instance parameter settings will override the database settings.
• NLS_INSTANCE_PARAMETERS show the settings just like NLS_DATABASE_PARAMETERS

Client-Side Environment Settings

• C:\> set nls_lang=Hungarian
• It may be dangerous for clients to set their own NLS settings.
  • Conversion may not be possible: Asian -> US7ASCII
• The conversion between client-side and server-side is done by Oracle Net.

Session-Level Globalization Settings

• A user can set their NLS settings in two ways:
  • SQL> alter session set nls_date_format=’dd.mm.yyyy’;
  • SQL> execute dbms_session.set_nls(‘nls_date_format’,”‘dd.mm.yyyy’”);

Statement Globalization Settings

• The tightest level of control over Globalization is to manage it from within each statement.
  • SQL> select to_char(hiredate,’Day dd, Month YYYY’,'nls_date_language=dutch’), to_char(hiredate, ‘Day dd, Month YYYY’,nls_date_language=german) from scott.emp;
• Statement conversion choices:
  • to_date – nls_date_language, nls_calendar
  • to_number – nls_numeric_characters, nls_currency, nls_dual_currency, nls_iso_currency, nls_calendar
  • to_char, to_nchar – nls_date_language, nls_numeric_characters, nls_currency, nls_dual_currency, nls_iso_currency, nls_calendar

Linguistic Sorting and Selection

• Oracle’s default sort order is binary, which converts characters to their ASCII codes and sorts them accordingly. This is suitable for American english but not for other languages.
• Linguistic sorting means that Oracle will replace each character with a numeric value that represents its sorting value for the chosen language.
• All nls_sort options can be suffixed with _AI or _CI for accent insensitive or case insensitive
  • SQL> alter session set nls_sort=FRENCH_CI;

The Locale Builder

• A graphical tool to create customized Globalization environments
  • $ORACLE_HOME/nls/lbuilder/lbuilder

Using Time Zones

• The Oracle environment can be made time zone aware by using datatypes:
  • TIMESTAMP WITH TIME ZONE – stores the timestamp and time zone it refers to
  • TIMESTAMP WITH LOCAL TIME ZONE – timestamp is converted to db and converted to user’s local time zone on retrieval
• DATE and TIMESTAMP are always normalized to the database time zone and displayed unchanged when retrieved

Parameters

Language Support

• NLS_LANGUAGE – defaults to AMERICAN
• NLS_DATE_LANGUAGE – defaults to AMERICAN
• NLS_SORT – defaults to BINARY which is a poor choice for many languages

Territory Support

• NLS_TERRITORY – defaults to AMERICA
• NLS_CURRENCY – defaults to $
• NLS_DUAL_CURRENCY – defaults to $ (secondary currency for territory)
• NLS_ISO_CURRENCY – defaults to AMERICA
• NLS_DATE_FORMAT – defaults to DD-MM-RR
• NLS_NUMERIC_CHARACTERS – defaults to ., (decimal and comma)
• NLS_TIMESTAMP_FORMAT – defaults to DD-MM-RRHH.MI.SSXFF AM (for columns of type timestamp)
• NLS_TIMESTAMP_TZ_FORMAT – defaults to DD-MM-RRHH.MI.SSXFF AM TZR (for columns of type TIMESTAMP WITH LOCAL TIMEZONE)

Other NLS Settings

• NLS_CALENDAR – defaults to Gregorian
• NLS_COMP – defaults to BINARY (comparison of letters by binary value)
• NLS_LENGTH_SEMANTICS – defaults to BYTE (column length specified in bytes – not chars)
• NLS_NCHAR_CONV_EXCP – defaults to false (won’t show error messages when conversions between different types fail – VARCHAR2 -> NVARCHAR)

Views & Tables

• V$NLS_VALID_VALUES
• V$NLS_PARAMETERS – Globalization settings currently in effect for your session
• V$TIMEZONE_NAMES -list of supported time zones
• NLS_DATABASE_PARAMETERS – shows current values of all NLS settings at db level
• NLS_INSTANCE_PARAMETERS – shows current values of all NLS settings at instance level
• NLS_SESSION_PARAMETERS – shows current values of all NLS settings at session level

Chapter 36 – Automating Administrative Tasks

The Scheduler Architecture

• The DBA_SCHEDULER_JOBS view in the data dictionary holds info for all scheduled jobs.
• The background process CJQ0 monitors the table and launches other processes – Jnnn – to run the jobs.
• The Jnnn process relies on the parameter JOB_QUEUE_PROCESSES, which defaults to 0. If it is set to 0, then jobs won’t run.

Scheduler Objects

Jobs

• A job specifies what to do and when to do it.
• The what can be:
  • Single SQL statement
  • PL/SQL Block
  • PL/SQL Stored Procedure
  • Java stored procedure
  • An external procedure
  • An executable or script on the OS
• The when can be:
  • A pre-defined schedule
  • A pre-defined window
  • An event
  • A time
• Jobs can be created via the dbms_scheduler.create_job procedure
  • All forms of the create_job procedure require job_name
  • Other options:
    • job_type
    • plsql_block
    • stored_procedure
    • executable
    • job_priority (defaults to 3 but can be 1 – 5, with 1 being highest priority)
    • job_action
    • auto_drop (says to execute the job only once – it’s the default if no scheduling info exists) …

Programs

• Programs are like pulling the what out of a job
• Created with the dbms_scheduler.create_program procedure
• Arguments
  • program_name
  • program_type
  • program_action
  • number_of_arguments
  • enabled
  • comments

Schedules

• Schedules are like pulling the when out of a job
• Created with the dbms_scheduler.create_schedule procedure
• Arguments
  • schedule_name
  • start_date
  • repeat_interval
  • end_date
  • comments
• Repeat Interval examples:
  • repeat_interval => ‘freq=hourly; interval=12′
  • repeat_interval => ‘freq=yearly; bymonth=jan,apr,jul,oct; bymonthday=2′
  • repeat_interval => ‘freq=weekly; interval=2; byday=mon; byhour=3; byminute=12′

Job Classes

• A job class is used to associate jobs with consumer groups. This will allow jobs to be run with specified resources, etc. It also controls logging.
• Arguments
  • job_class_name
  • resource_consumer_group
  • service (RAC only – service name of instance to run on)
  • logging_level
  • log_history
  • comments
• Created with dbms_scheduler.create_job_class

Windows

• A window adds functionality to the schedule by giving Oracle a window of time in which to run a job. Jobs scheduled within a window can be run at any time which is chosen by Oracle’s discretion.
• It is possible to nominate a RESOURCE_PLAN to start when the window begins as well as a WINDOW_PRIORITY (HIGH | LOW) to manage overlapping windows.
• It is possible to combine windows into window groups for ease of administration.
• Defaults are WEEKEND_WINDOW & WEEKNIGHT WINDOW.
• Windows cannot have the same name as schedules because they share the same namespace.
• Arguments
  • window_name
  • resource_plan
  • start_date
  • repeat_interval
  • end_date
  • duration
  • window_priority (either high or low)
    • if two windows have the same priority, then the one opened first will have priority
  • comments
• The duration is of the form:
  • ‘days hours:minutes:seconds’ // ‘0 3:30:00′

Privileges

• Example
  • GRANT CREATE ANY JOB TO HR;
• List of privs:
  • CREATE JOB (a user can create his own jobs)
  • CREATE ANY JOB (a user can create jobs in other schemas)
  • EXECUTE ANY PROGRAM
  • EXECUTE ANY CLASS
  • MANAGE SCHEDULER
  • EXECUTE ON <job, program or class>
  • ALTER ON <job, program or schedule>
  • ALL ON <job, program, schedule or class>

Using Programs and Schedules

• After creating a program or job via create_program or create_job, you will also need to enable it (since it is disabled by default)
  • SQL> exec dbms_scheduler.enable(‘prog1′);
• Programs cannot have the same names as jobs because they share the same namespace.
• A job can be executed without a schedule manually:
  • SQL> exec dbms_scheduler.run_job(’savedate’);

Using Classes, Windows and the Resource Manager with Jobs

• Jobs can be configured with the following components:
  • Job classes – jobs are assigned a class (logging)
  • Consumer groups – classes are assigned to consumer groups (CPU, active session pool)
  • Resource plans – consumer groups are part of a resource plan (only one resource plan active at a time)
  • Windows – resource plans and jobs will be active during windows
  • Window groups – windows can be combined into groups for administration purposes

Preconfigured Jobs

• There are two jobs configured by default:
  • PURGE_LOG
  • GATHER_STATS_JOB

Packages

• DBMS_SCHEDULE.
  • CREATE_JOB
  • CREATE_PROGRAM
  • CREATE_SCHEDULE
  • CREATE_JOB_CLASS
  • CREATE_WINDOW

Processes

• CJQ0 – launches Jnnn processes when they are scheduled to run
• Jnnn – each process runs a job (ex. J0001, J0002, …)

Tables & Views

• DBA_SCHEDULER_JOBS – shows scheduled jobs info

Parameters

• JOB_QUEUE_PROCESSES – defaults to 0 which means that jobs won’t run.
• RESOURCE_MANAGER_PLAN – current plan for the instance

Chapter 35 – Managing Oracle Database Resources

The Resource Manager Architecture

• Users are placed in Consumer Groups and Resource Plans to control and manage the allocation of resources across the groups.
• It is configured by default.

Consumer Groups

• Set of users with similar resource requirements
• A user can be a member of multiple groups
• At any given moment, a user session can only have one active group
• Requires a pending area before it can be created.
• There are 5 default groups:
  • SYS_GROUP – only SYSTEM user by default
  • DEFAULT_CONSUMER_GROUP – all users not assigned to group
  • OTHER_GROUPS – all users are member of this group
  • LOW_GROUP – Intended for low-priority sessions
  • AUTO_TASK_CONSUMER_GROUP – intended for running system maintenance jobs

Resource Manager Plans

• Default plans:
  • INTERNAL_PLAN – gives the OTHER_GROUPS 0% of CPU at all levels (equal access to all users). This is the default plan
  • SYSTEM_PLAN
    • 100% to SYS_GROUP at level 1
    • 100% to OTHER_GROUPS at level 2
    • 100% to LOW_GROUP at level 3
  • INTERNAL_QUIESCE – freezes all sessions out (by setting max number of sessions to 0) except for SYS_GROUP

Resource Manager Configuration Tools

• Two packages make up the Resource Manager API:
  • DBMS_RESOURCE_MANAGER_PRIVS – give resource manager privs to users or switch user to another group (GRANT[/REVOKE]_SWITCH_CONSUMER_GROUP or GRANT[/REVOKE]_SYSTEM_PRIVILEGE)
    • dbms_resource_manager_privs.grant_system_privilege(‘Mike’,'ADMINISTER_RESOURCE_MANAGER’,false)
  • DBMS_RESOURCE_MANAGER – create consumer groups, plans and directives (many functions available)
    • dbms_resource_manager.create_consumer_group(‘grp1′,’comment’)
    • a pending area (in the SGA) must be created before the group is added to make sure it is possible (ex. pct’s add up to 100). If the plan is valid, then it is saved to the Data Dictionary.
    • a pending area is not needed to put users into groups.
  • There is no need to add a user to group (only switch) since all users are members of OTHER_GROUPS on creation

Resource Manager Plans

• All plans must include a directive for OTHER_GROUPS or it will not be validated
• Methods to control consumer groups:
  • CPU
    • CPU uses emphasis method b/c it will vary depending on use
  • Number of active sessions
  • Degree of parallelism
  • Operation execution time
  • Idle time
  • Volume of undo data
    • All besides CPU use absolute limits which are not variables
  • Auto consumer group switching (execution time combined with CPU)
• Steps to create a plan:
  • Create pending area
    • SQL> exec dbms_resource_manager.create_pending_area;
  • Create plan
    • SQL> exec dbms_resource_manager.create_plan(plan=>’daytime’,comment=>’reg plan’);
  • Create directives
    • SQL> exec dbms_resource_manager.create_plan_directive(plan=>’daytime’, group_or_subplan=>’sys_group’, cpu_p1=>100);
    • … more directives …
  • Validate pending area:
    • SQL> exec dbms_resource_manager.validate_pending_area;
  • Submit pending area:
    • SQL> exec dbms_resource_manager.submit_pending_area;
  • Activate plan
    • SQL> alter system set resource_manager_plan=daytime;

CPU Method

• There are 8 possible priority levels
• It is impossible to allocate more than 100% of the CPU because the plan will not be saved. It is possible to allocate less than 100%, but there is little point in doing so.
• Another option is to make the group the plan and have other groups as subplans in a heirarchical fashion.

The Active Session Pool Method

• Limits the number of active sessions (not connections!!!). An active session is a session that is running a query or has an uncommitted transaction.
• If parallel processing is enabled, one operation still counts as only one
• By default, a session will be queued indefinitely but a timeout can be set

Limiting the Degree of Parallelism

• Can be used on single- or multi-processor machines.
• To enable parallel processing:
  • PARALLEL_MAX_SERVERS – must be set to create a pool of parallel execution servers
  • Enable parallelism for each table with ALTER TABLE <table_name> PARALLEL
  • Enable parallel DML for your session with ALTER SESSION ENABLE PARALLEL DML
  • Either set PARALLEL_AUTOMATIC_TUNING=TRUE or specify parallelism in each statement
• You can limit the number of parallel processors that each group has access to.

Controlling Jobs by Execution Time

• Jobs are estimated at how long they will run. If optimizer statistics are incorrect, then a job can execute when it wasn’t supposed to (too long).
• Jobs will not timeout. They will either run or be cancelled.

Terminating Sessions by Idle Time

• You can disconnect sessions by idle time or idle blocker time.
• Idle blocker time is how long there have been locks on data.

Restricting Generation of Undo Data

• The UNDO tablespace runs the risk of filling up if programmers poorly design queries.
• You can restrict groups on how much UNDO they are allowed to generate.
• When a group reaches its UNDO limit, the sessions will hang until a transaction is committed, freeing up UNDO space.

Automatic Consumer Group Switching

• You can force a group to switch privileges if their query runs too long.

Additional Features

Quiescing the Database

• Quiescing the database forces all groups besides the SYS_GROUP, to hang. It does so by activating the INTERNAL_QUIESCE plan
  • SQL> alter system quiesce restricted
  • SQL> alter system unquiesce
• The quiesce will only work if a resource plan has been active since startup. The plan must be specified in the s/pfile in order for this to be true

Consumer Group Switching for One Call

• Automatic consumer group switching can only be done via SQL execution time.
• Automatic consumer group switching via Resource Manager is permanent for the session, unless switched back manually.
  • Users can switch their sessions back via procedure in dbms_session or sys can use dbms_resource_manager.switch_consumer_group_for_sess
• Using the dbms_resource_manager API, you can switch consumer groups only for the duration of the call.
  • switch_time_in_call => — automatically switch back to the original consumer group after n seconds

Use of the Ratio CPU Method

• Instead of specifying the percent of CPU allowed to each group, you can simply use ratios between the groups.
• This is helpful when adding new groups because you won’t have to redistribute the the percentages.
• SQL> DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => ’service_level_plan’, CPU_MTH -> ‘RATIO’, COMMENT => ’service level plan’);
  • CPU_MTH can also be ‘EMPHASIS’ which is default

Create a Simple Resource Plan

• exec dbms_resource_manager.create_simple_plan(
  simple_plan=>’plan1′,
  consumer_group1=>’oltp’, group1_cpu=>50…)
• No need for pending_area.
• Level 1 is preconfigured at 100% for SYS_GROUP so all configs start at level 2.
• Bottom level is configured for OTHER_GROUPS at 100%.

Adaptive Consumer Group Mapping

• The two methods EXPLICIT & ORACLE_USER are available by default for determining the user’s consumer group
• Other methods available
  • EXPLICIT – switch groups via command to switch either code or Resource Plan
  • SERVICE_MODULE_ACTION – switch groups depending on service name used to connect, module and action of the code that is being executed. The programmers must embed these names within the code.
  • SERVICE_MODULE – switch groups depending on service name used to connect and program module
  • MODULE_NAME_ACTION – switch groups depending on program module and action being executed
  • MODULE_NAME – switch groups depending on program module
    • For all module or action methods, programmers must embed calls to DBMS_APPLICATION_INFO
  • SERVICE_NAME – switch groups depending on service name used to connect
  • ORACLE_USER – activate group at login depending on Oracle User ID
  • CLIENT_PROGRAM – switch groups depending on user process used to connect
  • CLIENT_OS_USER – switch groups depending on OS username
  • CLIENT_MACHINE – switch groups depending on name of client machine

Views & Tables

• V$SESSION – the column CURRENT_QUEUE_DURATION will show the duration that each queued session has been waiting
• V$RSRC_CONSUMER_GROUP – shows current session info for all consumer groups

Parameters

• RESOURCE_MANAGER_PLAN – used to enable a plan. By default it is not set, which means that INTERNAL_PLAN is the plan in effect.
• PARALLEL_MAX_SERVERS – used to enable parallelism on queries and DML

Chapter 34 – Monitoring and Managing Memory

The System Global Area

• SGA components are sized in granules. A granule is an area of contiguous memory.
  • The exception to this is the log buffer, which is not sized in granules.
  • Regardless of what size you specify for a particular SGA component, it will be rounded up to the next whole granule.
    • The exclusion is again the log buffer
• Made up of the following required components:
  • Shared Pool
  • Log Buffer
  • Db Buffer Cache Default Pool

• Following are optional requirements:
  • Large Pool
  • Streams Pool
  • Java Pool
  • DB Buffer Cache Keep Pool
  • DB Buffer Cache Recycle Pool
  • DB Buffer Cache nK Block Size Pools
• LOG_BUFFER and SGA_MAX_SIZE are two of the only static SGA parameters.

The Shared Pool

• Sized according to the SHARED_POOL_SIZE parameter. If set too low, the instance will not start.
• Has 36 components which are sized automatically.
  • SQL area & Library Cache – recently executed SQL statements & execution plan
  • Data Dictionary Cache (aka row cache) – data dictionary info being used to parse SQL statements
  • Active Session History – recent activity by users which is flushed to the AWR
• There is a Shared Pool Advisor to determine how large it should be.

The Database Buffer Cache

The LRU List and the Checkpoint Queue

• Every buffer in the db buffer cache will be in one of three states: pinned, dirty or free.
  • A pinned buffer is a buffer that is in use.
  • A dirty buffer is one that has not yet been flushed to disk.
  • A free or clean buffer is when the block has been copied into the cache and not yet changed or it has been changed and already flushed to disk.
• There should be many free buffers in the cache at any time. The decision on which buffer to use will be determined by the LRU List. The oldest accessed block will be overwritten.
• Another list is the Checkpoint Queue. This is a list of dirty buffers waiting to be written to disk by DBWn.
• The process that searches for free buffers in the LRU List also notifies the Checkpoint Queue of dirty buffers and adds them to the list. The LRU blocks will be written to disk first.
• When data gets written to disk:
  • A server process takes too long to find a free buffer.
  • The checkpoint queue is too long.
  • Every 3 seconds, DBWn writes dirty buffers to disk.
• The only time that all dirty buffers get written to disk is when a checkpoint occurs (ex. SHUTDOWN, or alter system checkpoint).
• Tablespace checkpoints occur:
  • tablespace is dropped
  • tablespace made read-only
  • tablespace put into hot backup mode
  • tablespace is taken offline
• Log switches do not trigger a checkpoint.

The Default, Keep and Recycle Pools

• Each pool within the DB buffer cache has its own LRU list and checkpoint queue.
• The default pool is the only required pool and has its size determined by DB_CACHE_SIZE.
• To ensure that blocks of segments that are constantly used are always in memory, create a db buffer cache “keep” pool by setting the DB_KEEP_CACHE_SIZE parameter.
  • SQL> alter index emp_name_idx(storage buffer_pool keep);
• To ensure that blocks of segments that are unlikely to be reaccessed are pushed out of memory as quickly as possible, create a db buffer cache “recycle” pool by setting the DB_RECYCLE_CACHE_SIZE parameter.
  • SQL> alter table emp(storage buffer_pool recycle);

The Nonstandard Block Size Pools

• The DB_BLOCK_SIZE parameter is used for formatting the SYSTEM and SYSAUX and temporary tablespace datafiles. It can NEVER be changed.
• In order to create tablespaces with different block sizes you must set the appropriate db buffer cache pools:
  • DB_2K_CACHE_SIZE
  • DB_4K_CACHE_SIZE
  • DB_8K_CACHE_SIZE
  • DB_16K_CACHE_SIZE
  • DB_32K_CACHE_SIZE
  • You cannot set the above parameter if it is already your default. It will throw an error.
• By creating additional buffer pools, you will be able to copy tablespaces between databases with different block sizes.
  • SQL> alter system set db_16k_cache_size=4m;
  • SQL> create tablespace ts_16k datafile ‘ts_16k.dbf’ size 400m blocksize 16k;
• You cannot create a tablespace of nonstandard block size that is of type temporary.
• The keep and recycle buffers can only be of the default block size.

Sizing the Database Buffer Cache

• The cache should be large enough to cache blocks that are repeatedly accessed, but not so large that it caches blocks that are used only once.
• The Database Buffer Cache Advisor is available via V$DB_CACHE_ADVICE

The Log Buffer

• Short term staging area for changes before they are written to the redo logs.
• LGWR writes to disk:
  • When a user commits a transaction
  • When the log buffer is 1/3 full (even if no commit has been issued)
  • Right before DBWn, writes (redo logs must have data before datafiles)
  • Every three seconds
• The LOG_BUFFER parameter determines the size

Other SGA Areas

The Large Pool

• The purpose of the large pool is to reduce strain on the shared pool
• Will be used instead of the shared pool for the following:
  • Shared Server configuration – UGAs will be stored in the large pool instead of shared
  • Parallel execution servers
  • I/O slave processes
  • RMAN
• Size is determined by the dynamic LARGE_POOL_SIZE parameter.
• To monitor usage, view V$SGASTAT

The Java Pool

• Its purpose is to provide room for the runtime memory structures used by a Java application
• It’s technically “optional” but from a practical standpoint, necessary.
• Controlled by three instance parameters:
  • JAVA_POOL_SIZE – dynamic; creates the Java pool and is limited by SGA_MAX_SIZE. Default is 24MB.
  • JAVA_MAX_SESSIONSPACE_SIZE – static; max space for any one session
  • JAVA_SOFT_SESSIONSPACE_LIMIT – static; if a session’s Java pool goes above this limit then a message is written to a trace file

The Streams Pool

• Stores changes that exist in the redo logs in order to propagate changes to other databases at remote sites.
• The changes from the redo logs are physical change records and can only be directly applied to datafiles, not applied logically to tables.

Automatic Shared Memory Management (ASMM)

• ASMM manages and auto-tunes the following components if they are set to zero or not set (if SGA_TARGET has a value):
  • DB buffer cache default pool, DB_CACHE_SIZE
  • Shared pool, SHARED_POOL_SIZE
  • Large pool, LARGE_POOL_SIZE
  • Java pool, JAVA_POOL_SIZE
• If the parameters have been set then Oracle will not lower the values below the setting
• These structures must be set manually:
  • DB buffer cache keep pool, DB_KEEP_CACHE_SIZE
  • DB buffer cache recycle pool, DB_RECYCLE_POOL_SIZE
  • DB buffer cache nonstandard block size pools, DB_nK_CACHE_SIZE
  • Streams pool, STREAMS_POOL_SIZE
  • Log buffer, LOG_BUFFER
• To enable ASMM set the parameter SGA_TARGET
• ASMM will not work if the STATISTICS_LEVEL is set to BASIC. It needs to gather stats in order to monitor and adjust the SGA.
• Use of an spfile allows Oracle to remember the values of the various components for the next startup.
• You cannot change the SGA_TARGET to a value greater than SGA_MAX_SIZE while the instance is running (you will get an error). However, if set in the spfile, then the SGA_MAX_SIZE will be adjusted to SGA_TARGET on startup.
• ASMM starts an additional background process called MMAN. It controls the tuning to the various SGA components.

The Program Global Area (PGA)

• The PGA stores information private to a particular session. It is created at the start of each session and a separate PGA (each of variable sizes) exists for each session.
• Information contained in the PGA is:
  • Sort space for sorting, joining, and aggregating rows (perhaps most important)
  • Bind information
  • Session variables
  • Stack space
  • Cursors
• The UGA is a part of the PGA.
• Two important parameters exist for the PGA:
  • WORKAREA_SIZE_POLICY – [AUTO | MANUAL] Should be set to AUTO
  • PGA_AGGREGATE_TARGET – Total amt of memory used for all PGAs (defaults to 20% of the SGA size)
  • • If all memory in PGA_AGGREGATE_TARGET is used up, then Oracle will take memory from one PGA and distribute it to another
  • • In the rare case that there is no memory that can be pulled away, the session will either use temporary space or break the PGA_AGGREGATE_TARGET setting.
• V$PGASTAT & V$PGA_TARGET_ADVICE are views used to monitor and size the PGA.
• If the WORKAREA_SIZE_POLICY is set to MANUAL, the PGA will rely on the following parameters (if set to AUTO the following will be ignored):
  • SORT_AREA_SIZE
  • HASH_AREA_SIZE
  • BITMAPMERGE_AREA_SIZE
  • CREATE_BITMAP_AREA_SIZE
  • The problem with MANUAL policy is that PGA will grow but never shrink. Oracle will be unable to reassign memory between PGAs.

Parameters

• SGA_MAX_SIZE – SGA can’t exceed this limit
• SHARED_POOL_SIZE
• STATISTICS_LEVEL – must be set to TYPICAL in order to use V$SHARED_POOL_ADVICE
• DB_CACHE_SIZE – size of db buffer cache default pool
• DB_KEEP_CACHE_SIZE – size of db buffer cache keep pool
• DB_RECYCLE_CACHE_SIZE – size of db buffer cache recycle pool
• LOG_BUFFER – size of the log buffer in the SGA
• CPU_COUNT – (depending on the value) determines the size of the LOG_BUFFER
• LARGE_POOL_SIZE – size of the large pool
• WORKAREA_SIZE_POLICY – PGA policy which should be set to AUTO
• PGA_AGGREGATE_TARGET – Total amt of memory used for PGAs (defaults to 20% of the SGA size)

Views & Tables

• V$SGASTAT – sizes of various SGA components
• V$SGAINFO – see granule size
• V$SHARED_POOL_ADVICE – help size your shared pool
• V$DB_CACHE_ADVICE
• V$PGASTAT – see current allocations on the PGA
• V$PGA_TARGET_ADVICE – similar to other advice views; has factor field and predictions about effects

Chapter 33 – Managing Storage with Automatic Storage Management

Intro

• ASM stands for Automatic Storage Management.
• ASM is an Oracle-aware LVM (logical volume manager) that can stripe and mirror database files across a number of physical devices.

The Purpose of a Logical Volume Manager

• An LVM allows putting many physical volumes into arrays that can be treated as one huge disk area and then partitioned into as many logical volumes as you want.

RAID (Redundant Array of Independent Disks) Levels

• Level 0
  • Optimal for performance but suboptimal for fault tolerance
  • Logical volume cut across two or more physical drives (aka striping data)
  • Allows for data to be written to multiple physical drives concurrently
• Level 1
  • Optimal for fault tolerance
  • Logical volume mirrored against multiple physical drives
• Level 5
  • Combination of Level 0 and Level 1
  • Logical volume is cut across multiple physical drives
  • Checksumming algorithm writes out information to recover from loss of one physical volume
  • Checksumming algorithm requires overhead which effects performance
• Level 0+1
  • Optimal for both performance and fault tolerance
  • Mirroring of striped disks

Volume Sizes

• Physical volumes have definite sizes. With an LVM you can add, remove and resize logical volumes while the system is running.

Choice of RAID Level

• Different RAID levels can be applied to different files:
  • SYSTEM & UNDO tablespaces -> RAID 1 (redundancy)
  • Redo logs -> RAID 0 (multiplexing means no need to mirror)
  • Control file -> RAID 0+1 (any damage to any copy crashes db)
  • Other tablespaces -> RAID 5 or RAID 0+1 (depends on importance)

ASM Compared with other LVMs

• Different file types will perform better or worse depending on stripe size. ASM knows how to size the stripes.
• ASM provides RAID functionality at the file level. Other LVMs only work at the volume level.
• ASM is an Oracle standard and works the same on all platforms.
• ASM is always available on Oracle while other OSes require you to purchase a third-party application.

The ASM Architecture

• An ASM instance has the parameter INSTANCE_TYPE set to ASM instead of RDBMS.
• An ASM instance controls access to ASM files on ASM diskgroups, on behalf of the RDBMS instance.

The ASM Disks and Disk Groups

• An ASM disk group is a pool of ASM disks managed as one logical volume. The disks must be raw devices.
• The usable size of the ASM disk group will depend on the level of fault tolerance. The default is single mirror.

The ASM Instance

• The RDBMS instance performs its own I/O (reading, writing to datafiles & redo logs), not the ASM.
• Since an ASM instance cannot mount or open a database, it cannot read a data dictionary; therefore, you can only connect to it with a password file or OS authentication (as SYSOPER or SYSDBA).
• It makes the most sense to have one ASM instance that manages all RDBMS instances on the server. Although, it is possible to have many ASM instances (just pointless).
• An ASM instance will have two background processes:
  • RBAL & ARBn are used to rebalance data, I/O, striping, etc.

The RDBMS Intances

• An RDBMS instance running with ASM will have two background processes:
  • RBAL & ASMB. ASMB is used to create a session against the ASM process.
  • The information passed from the RDBMS to the ASM will be requests for physical changes, such as file creation, deletion, or resizing, and also various statistics and status messages.
• The RDBMS knows the name of the ASM diskgroups because they are embedded in the controlfile.
• When an ASM instance starts, it registers its name with the Cluster Synchronization service. The ASMB process of the RDBMS locates the ASM instance via the Cluster Sync service.
• A common config for RDBMS with ASM is two diskgroups. One contains the flash_recovery_area and the other has the database files.

The ASM Files

• Files in the ASM disk groups are managed by ASM on behalf of the RDBMS instance
  • Controlfile
  • Initialization parameter file
  • Online / Archived Redo Logs
  • Datafiles
  • Tempfiles
  • RMAN backup sets / image copies
  • Flashback logs
  • Controlfile autobackups
  • Data Pump dump files
• Excluded files
  • Alert log
  • Trace files
  • Password file
• All ASM files (not AUs) are striped across all disks in the group.
  • Coarse striping – the AU (allocation unit) is set to a large amount (1,000k) to deal with big I/O
  • Fine striping – the AU is set to a small amount (128k) to deal with small I/O
• The syntax for managing files within ASM is the same as RDBMS. The only difference is when specifying a location, you simply point to the ASM and it will be organized by the instance.
• There is a one-to-one relationship between a database file and an ASM file.
• Moving files from the OS to the ASM instance must be done via RMAN backup and restore operations. This is because the OS can’t see the ASM disks.
• It is possible to have some files using ASM and others located within the OS. However, there is no point in doing this.

Creating, Starting and Stopping an ASM Instance

• An ASM instance uses a parameter file just like an RDBMS instance.
  • instance_type = ‘asm’ // REQUIRED
  • instance_name = ‘+asm’ // must be prefixed with +
  • asm_diskstring = ‘/dev/hda4/’, ‘/dev/hda6′ or ‘\\.\*:’
  • asm_diskgroups = dg1, dg2
  • backgroung_dump_dest = ‘/opt/app/oracle/bdump’
• To start the instance you must connect as SYSDBA and issue a STARTUP command. The ORACLE_SID must be set to the instance name (ex. +ASM).
• There is no OPEN state for an ASM instance – only NOMOUNT & MOUNT.
• The RDBMS cannot OPEN until the ASM instance is MOUNTED.
• If an ASM is issued a SHUTDOWN, it will first shutdown all RDBMS instances and then shut itself down.
• A SHUTDOWN ABORT will kill the ASM and then all RDBMS instances will crash, as well.

Creating ASM Disk Groups

• Syntax:
  • create diskgroup dg1 disks ‘/dev/sdc’, ‘/dev/sdd’ [ NORMAL | HIGH | EXTERNAL ] REDUNDANCY;
• If you choose a disk that is already part of a disk group, then the command will fail.
• The default level of redundancy is “normal”, which means that each AU (allocation unit) is mirrored once.
• The default RAID level is RAID 0+1 for every file unless specified, otherwise.
• All files are striped across all disks with ASM choosing coarse or fine depending on the file type (datafile, redo log, controlfile).
• Redundancy types:
  • NORMAL – mirror
  • HIGH – three copies
  • EXTERNAL – no redundancy – assumes external LVM
• When ASM mirrors extents, it will never mirror an extent to another disk in the same failure group.
• By default, each disk (/dev/hda, /dev/hdb, etc.) is considered its own failure group.
• Failure groups are like specifying submirrors. The below will group failgrp1 & failgrp2 as single logical units.

  • FAILGROUP failgrp1 DISK
    '/dev/d1',
    '/dev/d2',
    FAILGROUP failgrp2 DISK
    '/dev/d3',
    '/dev/d4';

Creating and Using ASM Files

• ASM disk groups are created from within the ASM instance while ASM files are created from within the RDBMS instance.
  • SQL> create tablespace tbs1 datafile ‘+dg1′ size 100m;
  • SQL> alter tablespace system add datafile ‘+system_dg’ size 100m;
  • SQL> alter database add logfile group 4 ‘+dg_log1′,’+dg_log2′ size 100m;
  • SQL> alter system set log_archive_dest_1=’+dg_arc1′;
  • SQL> alter system set db_recovery_file_dest=’+dg_arc1′;

ASM and RMAN

• The only way to migrate or backup ASM files is via RMAN.
  • SQL> alter system set controlfiles=’+dg1′,’+dg2′ scope=spfile;
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • RMAN> restore controlfile from ‘/u01/../control01.ctl’;
• This script will migrate all datafiles:
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • SQL> backup as copy database format ‘+dg1′;
  • SQL> switch database to copy;
  • SQL> alter database open;

ASM and Linux

• Requires the ASMLib library which is specific to the OS (RedHat, Suse, etc)

Views and Tables

• V$ASM_DISK – shows list of disks discovered by the ASM instance
• V$ASM_DISKGROUP – shows diskgroups
• V$DATAFILE – you can see the ASM generated file name
• V$LOGFILE – you can see the ASM generated file name
• V$ARCHIVED_LOG – you can see the ASM generated file name

Chapter 32 – Monitoring and Managing Storage

Online and Archive Redo Log File Storage

Disk I/O and the online Redo Log Files

• When a user issues a commit statement, the session will hang until LGWR flushes the data from the log buffer to the redo log files.
• When LGWR flushes the log buffer to disk, it issues a parallel write request to the OS. If the multiplexed files are on separate devices then the OS can do a parallel write to the different devices. Otherwise, if multiple files are on the same device, then the OS must write sequentially.

Log Switches and Performance

• LGWR writes out data in realtime but DBWn writes as little as possible, as rarely as possible. The gap between the LGWR flush and DBWn is the amount of redo that will have to be processed to back out corruptions in case of a crash. The FAST_START_MTTR_TARGET controls the gap. The faster the recovery time, the smaller the gap and therefore the more frequent DBWn writes to disk.
• There will always be enough redo data in the online data files to bring the db up to date in case of a crash. To ensure this, LGWR can’t overwrite a logfile group until DBWn has written the data to disk (datafiles). For each log switch, DBWn must write data to disk (from the db buffer cache) which means frequent log switches (due to small redo log files) can impact performance.
• In the Redo Logs section of DB Control, there is a log advisor which tells you if DBWn is being forced to write data that it wouldn’t otherwise write when log switches occur.

Archive Log Files and Performance

• It is possible, at times of peak activity that the redo logs are generating data faster than it can be copied to the archive logs. The instance will hang until the archiver catches up.
  • To minimize this effect:
    • Never locate archive logs on the same device as the redo logs. This guarantees contention between LGWR and ARCn.
    • Always launch as many ARCn processes (default 2; max 10) as archiver destinations (also 10 max). This is controlled by LOG_ARCHIVE_MAX_PROCESSES.
    • If problems still occur, add more online redo log groups. It will ensure more time before log files get overwritten.

Space Errors and Resumable Statements

Common Space-Related Errors

• The AUTOEXTEND attribute of a datafile allows Oracle to increase the size of the datafile as long as the max size hasn’t been reached or the disk isn’t full.
• If the UNDO tablespace is full, then everyone will get errors on DML.
• If the user’s temporary tablespace is full and a SELECT needs to use temporary space to sort, then the user will get errors.

Autoextension of Datafiles

• Can be specified at datafile creation time or subsequently with the ALTER DATABASE DATAFILE <name> AUTOEXTEND ON NEXT 10m MAXSIZE 200m;

Resumable Statements

• A session or the whole instance can be configured to suspend statements that hit space problems rather than rolling back and giving an error. The DBA can then fix the problem and the statements will resume where it left off. Default timeout is 2 hours which will then give error.
• All row locks and undo or temporary space will be held.
• SQL> alter session enable resumable timeout 60 name ‘AR archive’; (60 seconds)
• The table DBA_RESUMABLE will show suspended sessions.
• After a session is suspended, the event “after suspend” can be used to run PL/SQL functions for things like emailing the DBA, etc.

Use of Alerts to Monitor Tablespace Usage

• To check the free space available in tablespaces use the DBA_FREE_SPACE view.
• Alerts will be raised when tablespaces hit 85% and 97% of capacity and will be checked every 10 minutes. By default, alerts will be shown in database control and the DBA_OUTSTANDING_ALERTS / DBA_ALERT_HISTORY views.
• For autoextensible tablespaces, alert compares current usage with max file size.

Monitoring and Managing Segment Sizes

• As rows are inserted, the tablespace is allocated extents as necessary. On delete however, the extents are not deallocated.
• It is best practice to allocate enough space instead of having Oracle allocate dynamically.
• Since row deletions don’t deallocate space, it is possible to have more space than necessary for the data in the rows. This is a waste of space and also a performance hit since table scans must unnecessarily scan deleted data.
  • Indexes have similar problems.
• Database Control Advisors can scan objects and give recommendations.

Estimating Segment Sizes

• If you give an estimated number of table rows, an advisor will estimate the amount of space needed.
• A similar option is available for indexes.

Shrinking Table Segments

• The HWM or High Water Mark is a marker in a segment of the last block that has been used. As extents fill up, new extents are added to the table. As rows are deleted, space within the segment is freed up but the HWM doesn’t move and the segments retain their space. New insertions can use the freed up space but in some cases segments can be sparsely populated below the HWM. This is a waste of storage and inefficient since full table scans have to go through the deleted data.
• SQL> ALTER TABLE users SHRINK SPACE;
  • Will reorganize the table into as few rows as possible with all valid rows at the front of the table. Will push down HWM and free all unneeded extents back to the tablespace. The result is a small and compact table with all rows packed as tightly together as possible. Optimization and space utilization are achieved.
  • Rows are moved via copy, paste and delete. If a user tries to access a row while it is being moved, they may experience a momentary delay until the lock is released.
  • The final stage is moving the HWM and freeing up the empty blocks.
• Insert and delete triggers don’t fire while shrinking takes place. Row movement must be enabled. Segment shrink operations only work with heap organized tables, in segments using ASSM (Auto Segment Space Mgt). Segment Management can be viewed in the DBA_TABLESPACES table.
• Tables that cannot be shrunk:
  • Clustered tables
  • Tables with columns of type LONG
  • LOB segments (although the table can be shrunk)
  • Tables with on-commit materialized views (b/c triggers are disabled)
  • IOT mapping tables and IOT overflow segments
  • Tables with function based indexes
• Other options:
  • SQL> ALTER TABLE users SHRINK SPACE COMPACT;
    • Stops the shrink before moving the HWM because the HWM move requires a (very brief) table lock. It can be run with COMPACT and then subsequently without it. The second run will be very fast since it’s already compacted.
  • SQL> ALTER TABLE users SHRINK SPACE CASCADE;
    • Shrinks tables and indexes and moves the HWM.

Shrinking Index Segments

• Indexes also become fragmented from deletes due to their B*Tree structure. However, new insertions do not reuse the space from deleted rows.
• To see the index’s space utilization:
  • SQL> analyze index I1 validate structure;
  • SQL> select LF_ROWS_LEN, DEL_LF_ROWS_LEN FROM INDEX_STATS WHERE NAME=’I1′;
  • DEL_LF_ROWS_LEN tells how many rows from the index are used on deleted rows.
  • LF_ROWS_LEN tells how many rows are in total.
• To free up the space used by deleted rows for reuse, do:
  • SQL> ALTER INDEX I1 SHRINK SPACE;
  • This command will not compact the index, it will only free up the deleted blocks. In order to compact the blocks, you must REBUILD the index.
  • If you check the DBA_SEGMENTS view, the segment size will still be the same.

Rebuilding Index Segments

• Command:
  • SQL> ALTER INDEX I1 REBUILD ONLINE [tablespace indexes];
  • Without the ONLINE keyword, the table will be locked for the duration of the rebuild.
• A rebuild will create an entirely new index and therefore requires up to twice the amount of space during the operation.

Monitoring Index Usage

• Indexes can improve queries but can be bad for DML operations. Whenever the value of an indexed column is changed, the block of the table row must be read into memory and changed, plus the blocks of the index must be changed. Every unused index slows down the database and serves no purpose.
• To see if an index is being used, you can monitor it:
  • SQL> alter index idx1 monitoring usage;
  • … wait for minutes, hours or days …
  • SQL> select index_name,table_name,used from v$object_usage;
  • SQL> alter index idx1 nomonitoring usage; (to stop monitoring)
  • The used column (yes/no) will tell whether the index has been used since monitoring began.

Alternative Table Storage Structures

• Heap organized tables are the default table type and are made up of variable length rows in random order.
• Other options are IOTs, index clusters, hash clusters and sorted hash clusters.
• Programmers don’t need to know the structure of the table, their code will remain unchanged.

Index Organized Tables (IOTs)

• An IOT looks and acts like a table but the segment used to store it is of type index.
• The key and entire row is stored in the leaf blocks of the B*Tree. (A regular index holds the key and a pointer to the row, in the leaf.)
• IOTs are faster than a heap table with an index because there is no need to reference the rowid pointer.
• IOTs can be created with an “overflow” segment. This works on the assumption that programmers will only need a few columns on a regular basis and the rest will be stored in the overflow segment.
  • SQL> create table emp_iot (emp_no number, emp_ name varchar2(20), emp_dept varchar2(20), emp_dept number, constraint emp_pk primary key(emp_no)) organization index including emp_name overflow tablespace hr_over;
  • The “including emp_name” part means all columns before and including emp_name will be stored in the IOT while the remainder will be stored in the overflow.
  • An alternative to “including emp_name” would be “PCTTHRESHOLD n”. This states that n percent of the row will be stored in the IOT with the remainder going to the overflow.
• Mapping table required for secondary index:
  • SQL> alter table emp_iot move mapping table;
    • IOT will be reorganized and generate a mapping table (heap) which holds rowids for IOT. Secondary bitmap indexes can be built on the mapping table.
    • SQL> create bitmap index emp_indx on emp_iot(emp_dept);
• Limitations:
  • Table must be created with a primary key, nondeferrable, constraint.
    • nondeferrable – checks constraints as soon as statement executes.
    • deferrable – waits until commit to check constraints.
  • An IOT Cannot be a clustered table
  • Cannot have columns of type LONG
  • Cannot have partitioning (which is only offered as an add-on anyway)

Index Clustered Tables

• An index cluster is a group of tables stored in one physical segment.
• All tables in the index cluster must have a common cluster key or column (sno in the example below).
  • SQL> create cluster staff_clust (sno number) index;
  • SQL> create index staff_idx on cluster staff_clust;
  • SQL> create table staff (sno number, sname varchar2(20), constraint staff_pk primary key (sno)) cluster staff_clust (sno);
  • SQL> create table address_lines(sno number constraint addr_fk references staff, lno number, ltext varchar2(100)) cluster staff_clust (sno);

Hash Clustered Tables

• Hash clusters also have a cluster key like index clusters. However, the key is used to construct a hashing algorithm.
• Whenever a row is inserted, Oracle assigns a physical location based on the hash value. This method, hash key access is the fastest method of retrieval.
• Searching an index will retrieve a location from the hash algorithm. This is CPU intensive instead of I/O.
• One drawback is that it is only efficient when searching for specific values, not ranges or get-next-record type of statements.
• Another drawback is the size consideration. The estimated number of rows is used to construct the hashing algorithm so if it’s very inaccurate, the performance will degrade.

Sorted Hash Clustered Tables

• Same as hash clustered tables except you specify which columns are to be sorted. The rows are sorted as part of the hash algorithm.

Parameters

• FAST_START_MTTR_TARGET (the smaller the value, the more often DBWn writes to disk )
• LOG_ARCHIVE_MAX_PROCESSES (number of archiver processes writing to arc destination. should be equal to number of destinations.)
• RESUMABLE_TIMEOUT (to set ENABLE RESUMABLE for entire instance – in seconds)

Tables & Views

• DBA_DATA_FILES (see which datafiles have autoextend)
• DBA_RESUMABLE (shows all suspended sessions / SQL text / error, etc.)
• V$SESSION (shows whether a session is suspended or not)
• DBA_FREE_SPACE (shows the free space available in the tablespaces)
• DBA_OUTSTANDING_ALERTS (shows alerts on tablespace size, etc.)
• DBA_ALERT_HISTORY (when alerts are no longer outstanding, they are moved here)
• DBA_SEGMENTS (check size of segment after shrink space on tables or rebuild on indexes)
• DBA_TABLES or USER_TABLES (will tell which cluster the table belongs to)
• DBA_SEGMENTS or USER_SEGMENTS (has field SEGMENT_TYPE of cluster or index, etc.)