Oracle Architecture and Administration (2 of 3)

  1. Creating the Database
    1. Prepare the Operating System
    2. On a UNIX system the majority of the required environment variables are stored in a file named oraenv or coraenv , on a Windows machine these are stored in registry. The main enviornment variables to be aware of are :

      DISPLAY: The machine name or IP Address-X server-and-screen being used by your workstation to connect to the system where the software will be installed, if not sure what X server and screen setting should be, use 0 (zero) for both, DISPLAY=workstation_name:0.0

      ORACLE_HOME: Set according to OFA (Oracle Flexible Architecture)

      ORACLE_SID: System ID for the Database ( Important for specifying which database to mount)

      ORACLE_BASE: Set according to OFA

      ORA_NLS33: Required to be set if installing a database with a storage character set other then US7ASCII.To be set $ORACLE_HOME/ocommon/nls/admin/data

      NLS_LANG: Required if installing or creating a database that uses a character set other than US7ASCII ( Recommend Mexican Spanish Character Set : WE8DEC , NLS_VALUE= esm )

      LD_LIBRARY_PATH : To be updated with the location of the Oracle Libraries $ORACLE_HOME/lib

      CLASSPATH : Location for Java Classes to load.

      A Unix a group called dba is generated for the DBAs. An account orainstall is used to install the Oracle Software, this account must be a member of the dba group.

    3. Prepare the parameter file :
    4. The minimum parameters required to create the database are controlfile, db_block_size and db_name, however other recommended parameters include USER_DUMP_TEST,BACKGROUND_DUMP_TEST,NLS_DATE_FORMAT,etc..

    5. Create the Database :
    6. The create database command creates:

      The system datafiles and tablespace, controlfiles, initial redo log groups, the system rollback segments and the internal database tables necessary for the operation of the Oracle Database. ( This command is issued after the database is in the no mount stage).

      It is very important to correctly size the datafile specified with the create database command, as this datafile will be assigned to the SYSTEM Tablespace. Allocate more space to the database if the database will make heavy usage of stored packages, procedures, functions and triggers. Set the MAX parameters high as well. Example :

      CREATE DATABASE "ODBC"
         maxdatafiles 254
         maxinstances 8
         maxlogfiles 32
         character set US7ASCII
         national character set US7ASCII
      DATAFILE '/u01/app/oracle/oradata/ODBC/system01.dbf' SIZE 175M
      logfile '/u01/app/oracle/oradata/ODBC/redo01.log' SIZE 500K, 
          '/u01/app/oracle/oradata/ODBC/redo02.log' SIZE 500K; 
      

      After the command is run the database opens automatically. At this point it is a working, although very bare database.

  2. Create Data Dictonary Views and the Standard Packages
    1. Construct the Data Dictonary views
    2. The data dictonary views are created by the catalog.sql script located at $ORACLE_HOME/rdbms/admin/

    3. Query the Data Dictionary
    4. The data dictonary is composed of:

      1. Internal RDBMS(X$) Tables: The tables are used by the Oracle RDBMS to keep track of internal database information. Most of them are not designed to be used directly by DBAs or users.
      2. Data Dictonary Tables : Hold information for tables,indexes,constraints and all other database constructs. They are owned by SYS, and easily identifiable by a trailing $ in their names tab$,seg$,cons$,etc. They are generated by the script SQL.BSQ
      3. Dynamic Performance Views : These are the mainstay of the DBA, V$ views are actully public synonyms to the V$ views owned by SYS.
      4. Data Dictonary Views : The data dictonary views are views created on the X$ and data dictonary tables. They are divided into three categories DBA_, ALL_ , USER_ .
    5. Prepare the PL/SQL environment using the administrative scripts.
    6. The scripts catproc.sql is used to install the procedural Option (or PL/SQL objects) and its supporting database structures.

    7. Administer stored procedures and packages
    8. When a subprogram is created via the REPLACE OR CREATE command, it is stored in the database. The subprogram is stored in compiled form, which is known as p-code. The p-code has all the references in the subprogram evaluated , and the source code is translated into the form that is easily readable by the PL/SQL engine. When the subprogram is called, the p-code is read from disk, if necessary and exectued. P-code is analogouse to the object code generated by other 3GL compilers. Since the p-code has the object references in the subprogram already evaluated (this is the early binding property mentioned in PL/SQL characteristics ), executing the p-code is a comparetively inexpensive operation.

      Information about the subprogram is accessible through varios dictonary views:

      1. USER_OBJECTS: This view contains all the information on the subprograms (including all other objects ), which includes when the object was created and last modified, the type of the object (function,procedure,table,sequence,etc) and the validity of the object.
      2. USER_SOURCE : Contains the original source_code for the object.
      3. USER_ERRORS : Contains information about compile errors.

      A package is esentially a named declarative section. Anything that can go into the declarative part of a block can go in a package. This includes procedures, functions, cursors, types, and variables. A package is composed of two parts a header (or specfication) and a body . They are both separate dictonary objects in the database.

      The header contains all the elements that will conform the package: procedure specifications , function specifications , variable declarations, type definitions , exception declarations and cursor declarations. However it does not contain any code for any part.

      The package body cannot be compiled unless the package header is compiled successfully. It contains the code for the forward subprogram declarations in the package header. Objects in the header that are not forward decalarations (like an exception) can be referenced in the package body without being redeclared. The package body is optional in case that the header does not contain any procedures or functions ( only variable declarations, cursors,types,etc)

      The package body and header are also stored in the USER_OBJECTS where object_type is PACKAGE and PACKAGE BODY.

      NOTE : To find DEPENDENCIES among OBJECTS the views: DBA_DEPENDENCIES,USER_DEPENDENCIES and ALL_DEPENDENCIES are provided.

    9. List the types of database event triggers
    10. A trigger is executed implicitly whenever the triggering event happens, and the trigger doesnt accept arguments. Triggers are used to

      1. Maintain complex integrity constraints not possible through declarative constraints enabled on the table.
      2. Auditing information in a table, by recording the changes made and who made them
      3. Automatically signalling other programs that action needs to take place, when changes are made to a table.

      The general syntax is:

       
      CREATE [OR REPLACE] TRIGGER trigger_name 
      [BEFORE | AFTER] triggering_event ON table_reference
      [FOR EACH ROW [WHEN trigger condition]]
      trigger_body;
      

      Where trigger_event specifies when the trigger fires, table_reference is the table for which the trigger is defined, and trigger body is the main code for the trigger.

      The required components of a trigger are the trigger_name, triggering_event, and the body . The WHEN clause is optional. The Namespace for trigger names is different from that of other subprograms. Subprograms (procedures,functions,tables,packages,etc) share the same namespace (Schema) which means that a certain procedure cannot have the same name as another existing object in the Schema (be it table,package,etc.). Triggers however, can have the same name as table or procedure, since they are in a separate Namespace.

      TYPES OF TRIGGERS

      The triggering event determines the type of the trigger. Triggers can be defined for INSERT, UPDATE, or DELETE operations. They can be fired before or after the operation, and they can also fire on row or statement operations. The values for the statement, timing and level determine the type of the trigger. There are a total of 12 possible types: 3 statements,2 timing and 2 levels.

      Category Values Comments
      Statement INSERT, UPDATE, DELETE Defines which kind of DML statements causes the trigger to fire
      Timing BEFORE or AFTER Defines wether the trigger fires before the statement is executed or after the statement is executed
      Level Row or Statement If the trigger is a row-level trigger, it fires once for each row affected by the triggering statement. If the trigger is a statement-level trigger it fires once , either before or after the statement. A row-level trigger is identified by the FOR EACH ROW clause in the trigger definition

      You can drop or enable/disable the trigger with the following commands:

      drop trigger <trigger_name>
      alter trigger <trigger_name> [ENABLE|DISABLE]
      

  3. Managing Rollback Segments
    1. Create rollback segments using appropriate storage settings
    2. The database assings rollback segments on a round-robin basis.They support the use of rollback command to restore the previous image of modified records. They also rollback transactions that are aborted prior to completion.

      During queries, rollback segments are used to construct a consisten "before" image of the data that has changed-but not commited- prior to the execution of the query. The round-robin fashion in which rollback segments are assigned is designed to distribute the transaction load being carried by the rollback segments. Even though a single rollback segment can be created it is not recommended due to contention in the rollback segment,conversely, you may choose to create many small rollback segments, so that each transaction will have it own rollback segment, this also run into performance problems, because if the rollback segments are to small they will likely be dynamically extended in order to service the transaction.

      The design process involves determining the transaction volume and estimating the number and type of transactions. There are two types of entries to be considered:

      • Active Entries : Those who still have not been commited or rolled back
      • Inactive,in use (IIU) entries, which have been commited or rolled back, but whose data is in use by processes (long-running queries).

      The key to managing rollback segments is to minimize the amount of inactiv in use (IIU) entre data. Unfortunatly their existence only becomes evident when the error "ORA-1555 snapshot to old" is reported.

      To solve this problem, the large queries should be isolated to run at times when there is very little transaction occuring. In order to determine the amount of writes made to a rollback segment the V$ROLLSTAT view is used, this view used in conjunction with the V$ROLLNAME which is used to determine the amount of activity in each rollback segment.

      In order to determine the activity for a unique transaction, you need to direct the transaction to a specific rollback segment set transaction use rollback segment SEGMENT_NAME.

      NOTE: The previous views V$ROLLNAME are reset once the database is shutdown.

    3. Maintain Rollback Segments
    4. The parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT does not limit the number of transactions that can use a rollback segment.Rather, it determines the number of rollback segments an instance attempts to acquire when opening a database.

      The parameter ROLLBACK_SEGMENTS specifies the rollback segments per NAME to be loaded at instance startup.

    5. Plan the number and size of rollback segments
    6. Number of Current TransactionsNumber of Rollback Segments
      n <164
      16 <= n <324
      32 <= n8
    7. Obtain Rollback segment information from the data dictonary
    8. A rollback segment can be in any of the following states:

      • OFFLINE : Has not been acquired (brought online) by any instance.
      • ONLINE : Has been acquired (brought online) by an instance; may contain data from active transactions.
      • NEEDS RECOVERY : Contains data from uncommited transactions that cannot be rolled back (because the data files involved are inaccesible) , or is corrupted.
      • PARTLY AVAILABLE : Contains data from an in-doubt transaction (that is, an unresolved distributed transaction).
      • INVALID : Has been dropped ( The space once alloated to this rollback segment will later be used when a new rollback segment is created).

      The data dictonarytable DBA_ROLLBACK_SEGS lists the state of each rollaback segment allong wight other rollback information.

    9. Troublshoot common Rollback segment problems.
    10. A PARTLY AVAILABLE rollback segment is being used by an in-doubt distributed transaction that cannot be resolved because of a network failure.

      A NEEDS RECOVERY rollback segment is being used by a transaction (local or distributed ) that cannot be resolved because of local media failure, such as a missing or corrupted datafile , or is itslef corrupted.

      Oracle or a DBA can bring a PARTLY AVAILABLE rollback segment online. In contrast you must take a NEEDS RECOVERY rollback segment OFFLINE before it can be brought online.(If you recover the database and there by resolve the transaction , Oracle automatically changes the state of the NEEDS RECOVERY rollback segment to OFFLINE.

      A DBA can drop a NEEDS RECOVERY rollback segment ( This allows the DBA to drop corrupted segments) A PARTLY AVAILABLE segment cannot be dropped; you must first resolve the in-doubt transaction, either automatically by the RECO process or manually.

      If you bring a PARTIALLY AVAILABLE rollback segment online ( by a command or during instance startup ), Oracle can use it for new transactions. However, the in-doubt transaction still holds some of its transaction table entries, so the number of new transactions that can use the rollback segment is limited.

      Also , until you resolve the in-doubt transaction, the transaction continues to hold the extents it acquired in the rollback segment, preventing other transactions from using them. Thus, the rollback segment might need to acquire new extents for the active transactions, and therefore grow. To prevent the rollback segment from growing, a DBA might prefer to create a new rollback segment for transactions to use until the in-doubt transaction is resolved, rather than bring the PARTLY AVAILABLE segment online.

      When a tablspace goes offline so that transactions cannot be rolled back immediately , Oracle writes a deferred rollback segment . The deferred rollback segment contains the rollback entries that could not be applied to the tablespace, so that they can be applied when the tablspeace comes back online. These segments disappear as soon as the tablespace is brought back online and recovered. Oracle automatically creates deferred rollback segments in the SYSTEM tablespace.

  4. Managing Tables
    1. Create tables using appropriate storage settings
    2. The table should ideally fit in one sgement, see other information in this section for sizing.

    3. Control the space used by tables
    4. These are managed by the storage parameters specified at creation MAXEXTENTS, MINEXTENTS, PCTINCREASE, FIRST, NEXT, PCTUSED, PCINCREASE, the value for these parameters can be queried at USER_OBJECTS or USER_TABLES.

    5. Analyze tables to check integrity and migration
    6. Row migration ocurrs when an update to a row makes the length of the row larger than the space available to it in the block. When this happens Oracle tries to find another block with enough free space to hold the entire row, if such a block is available ,Oracle moves the entire row to the new block. If the row is too large to fit into any available block, the row is split into multiple pieces and stores each piece in a separate block.

      The problems that occur are that:

      Oracle has to perform at least one additional I/O read every time it has to fetch a migrated row. It must read each block that contains either a portion of the rows data or a pointer to another block that contains row data. Oracle also has to store additional pointers along with row data to accomodate the data migration mechanism. While usually not substantial, this wastes some disk space.

      Eliminating Chained and Migrated Rows

      Chained and migrated row impose a serious problem to overall database performance. To check for chained or migrated rows you issue the command:

      ANALYZE TABLE <table_name> LIST CHAINED ROWS <into table_to_insert_results>
      

      The table <into table_to_insert_results> should be the same as the chained_rows table defined in the utlchain.sql, if no table_to_insert_results is specified the command looks for the chained_rows table. If after querying a table it shows that rows are migrated then you can follow these steps:

      • Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
            CREATE TABLE cadena_intermedia
              AS SELECT *
                FROM tabla_migrada
                  WHERE ROWID IN
                      ( SELECT head_rowid
                          FROM chained_rows
                          WHERE table_name= 'tabla_migrada');
          
      • Delete the migrated and chained rows fromthe existing table:
            DELETE FROM tabla_migrada
              WHERE ROWID IN
                 (SELECT head_rowid
                   FROM chained_rows
                   WHERE table_name = 'tabla_migrada');
          
      • Insert the rows from the intermediate table into an existing table
            INSERT INTO tabla_existente
               SELECT *
                 FROM cadena_intermedia
          
      • Delete the row :
            DELETE FROM chained_rows
            WHERE tabel_name=tabla_migrada
          
      • Re-ANALYZE the table to see if the row have een moved.
    7. Retrieve Information about tables
    8. The dynamic views USER_TABLES, USER_OBJECTS , AND USER_CONSTRAINTS hold information regarding the tables among others.

    9. Convert between different formats of ROWID.
    10. The ROWID is used to uniquely identify a row in a regular Oracle Index (B-Tree) and in used throught the Oracle8 kernel. The row-id uses a base-64 encoding , and is 16 characters wide. This means that there are 64 to the 18 possible rows in any given Oracle 8 database (more than enough!). The components of the ROWID are:

      Data ObjectRelative File#Block #Slot
      OOOOOOFFFBBBBBBSSS

      The first six characters represent the data object number, the next thre represent the relative file number within the tablespace, and the next six represent the block number within the file, and the last three represent the (row) slot number within the block.

      ROWID version Comparison
      VersionDescriptionBytesDisplayFile Nos.
      7.xrestricted62 dotsabsolute
      8.xextended10no dotsrelative

      The tablspace-realtive id addressing is the foundation of Oracle support for VLDB (Very Large Databases). Oracle8 uses 10 bytes to store the ROWID as opposed to 6 bytes for Oracle7.x .

      When relative ROWID is sufficient, the restricted ROWID is used. When absolute ROWID is required, as with Global indexes on partitioned tables,the extendad (full) ROWID is used. Restricted ROWIDs are sufficient for nonpartitioned indexes on nonpartitioned tables, equipartitiones indexes on partitioned tables, and chaining and migration pointers accros blocks. Extended ROWID are necessary for Global Indexes on partitioned tables, kernel use, and stored forms of ROWID.

      Application poratability arises only if the applcation either partially stores or retrieves ROWID rows. On the other hand, those application using ROWIDs on the whole should be unaffected.

      When Oracle 7 tables are either exported or migrated to Oracle 8, stored ROWID column widths are automatically widened to accomodate new, wider Oracle 8 ROWIDs.

      The make the conversion the package DBMS_ROWID is used, which is created by the dbmsutil.sql script called by the catproc.sql script.

        update los_carros set
          rowid=dbms_rowid.rowid_to_restricted(rowid,'OCP','LOS_CARROS',0);
        update los_carros set
          rowid=dbms_rowid.rowid_to_extended(rowid,'OCP','LOS_CARROS',0);
      

  5. Managing Indexes
    1. List the different types of indexes and their uses.
      • B*Tree: This index is generated when a specific column in a table contains a primary key or unique constraint. This type of index holds the value of the columns plus its ROWID. When Oracle queries for information on a specific row in a table, it looks up the ROWID in the index and then pulls the data directly from the table.
      • Index Organized Tables : Index Organized tables store their data as if the entire table was stored as an index. A B-tree normally stores only the indexed columns in the index, an Index Organized table stores all the columns in the Index. Because all the table columns are stored as an index, the rows of the table do not have ROWIDs, the only constraint in order to declare a table an Index Organized Table is that is has to have a primary key, this type of index is recommended ONLY for very static data.
      • Bitmap Index : These types of index are used for tables that have very low cardinality (different values in the same column), for example a table that has a column with 10000 rows, and the possible values are only YES and NO.
      • The other type of index is the CLUSTER INDEX , this index is created on the column shared by the tables in the clusters.
    2. Create a B-Tree and bitmap index
    3. B-Tree Index:

        create index CODIGO_POSTAL
        on ESTADOS(Codigo_Postal,Descripcion)
      

      This index is created on two columns in the ESTADOS table.

      Index Organized Table:

        create table ESTADOS(
          Codigo_postal CHAR(15) primary key,
          Descripcion VARCHAR2(25)
        )
        organization index;
      

      Bitmap Index:

        create bitmap index EMPLEADO_CON_SEGURO
        on empleados(seguro_medico);
      
    4. Reorganize Indexes
    5. The command alter index ... rebuild can be used to recreate or change the location and initial storage sized of the index.

      • Change Storage Values and Location
              alter index EMPLEADOS_CON_SEGURO rebuild
              tablespace INDEXES
              storage (initial 2M next 2M pctincrease 0);
            
      • Allocate the Indexes in the appropriate Tablespace before it is created by the primary key column in the Table.
              create table comida_rapida
              ( Tipo_de_comida varchar2(25),
                Costo number,
                Lugar_de_restaurant varchar2(40),
                constraint UBICACION primary key (Lugar_de_Restaurant)
                using index tablespace INDEXES
                storage (initial 2M next 2M pctincrease 0));
            
    6. Rename an Index
    7. alter index CAMBIO rename to NOMBRE_NUEVO;
      alter index CHANGE rename to NEW_NAME;
      
    8. Drop Indexes
    9. DROP INDEX <index name>
      
    10. Get index information from the data dictonary
    11. Information on the indexes can be queried in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES