Tuesday, July 31, 2007

Oracle Table Spaces

Typical default tablespaces for an Oracle database
  • System tablespace

  • Sysaux tablespace

  • Undo tablespace

  • Temporary tablespace

  • Default permanent tablespace


Physical Database Structures

The Oracle database consists of the following three main types of files:

  • Data files: These files store the table and index data. Can belong to only one database

  • Control files: These files record changes to all database structures. Names and locations of the data files, redo log files, current log sequence numbers, backup set details, and the all-important system change number (SCN), which indicates the most recent version of committed changes in the database. Every database has one control file.

  • Redo log files: These online files contain the changes made to table data. Redo log files contain the following information about database changes made by transactions:

    • Indicators specifying when the transaction started

    • The name of the transaction

    • The name of the data object that was being updated (e.g., an application table)

    • The "before image" of the transaction (the data as it was before the changes were made)

    • The "after image" of the transaction (the data as it was after the transaction made the changes)

    • Commit indicators that indicate whether and when the transaction completed

Oracle config files:

The SPFILE (init.ora is no longer recommended)

When you create a new database, you specify the initialization parameters for the Oracle instance in a special configuration file called the server parameter file (SPFILE). You can also use an older version of the configuration file called the init.ora file but the SPFILE is recommended. Note the SPFILE is a binary and not directly editable. Location is ORACLE_HOME/dbs.

The Password File

The password file is an optional file in which you can specify the names of database users who have been granted the special SYSDBA or SYSOPER administrative privileges, which enable them to perform privileged operations, such as starting, stopping, backing up, and recovering databases.

The Alert Log File

Every Oracle database has an alert log named alertdb_name.log (where db_name is the name of the database). The alert log captures major changes and events that occur during the running of the Oracle instance, including log switches, any Oracle-related errors, warnings, and other messages. Location specified in the BACKGROUND_DUMP_DEST initialization parameter, or defaults to $ORACLE_HOME/rdbms/log on HP.
To locate:

SQL> SHOW PARAMETER background dump
NAME TYPE VALUE
-----------------------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/product/10.2.0/db_1/orcl/bdump

Trace Files

Oracle requires that you specify three different trace file directories in your initialization file: the background dump directory, the core dump directory, and the user dump directory.

BACKGROUND_DUMP_DEST parameter.
CORE_DUMP_DEST parameter.
USER_DUMP_DEST initialization parameter.

Oracle Managed Files

The OMF feature aims at relieving DBAs of their traditional file-management tasks. When you use the OMF feature, you don't have to worry about the names and locations of the physical files. Instead, you can focus on the objects you're creating. Oracle will automatically create and delete files on the operating system as needed.


Oracle Processes

Sunday, July 29, 2007

Oracle Notes

  • Security
    - User Management

  • System management
    - Capacity Planning
    - Backup and Restore
    - Performance Tuning
    - Trouble Shooting
    - Patches

  • Database design
    - SQL review, keys, indexes
    - Logical Design, tables spaces,
    - partitioning schemes and strategies

Friday, July 27, 2007

Informix Support Info

During Informix Support Center business hours (8 a.m. to 8 p.m. Eastern Time):
  • Priority 1: Call 800-274-8184, select option 9 for Down System
  • Priority 2 thru 4: Call 800-274-8184
  • 24x7 (8 p.m. to 8 a.m. ET and Weekends): Call 888-876-9797 for P1 issues only.