Oracle Architecture and Administration (1 of 3)

  1. Oracle Architecture Components
    1. Describe the Oracle Server Architecture and its main components.
    2. Oracle is basically formed from :

      1. An instance: Which is basically a set of memory structures and background processes that cache the most used data, and serve as an exchange point between the users(clients) and the datafiles that have the information on disks.
      2. Datafiles : These datafiles are located on hard disks, and they compose all the data that is stored in Oracle,control files,tablespaces, redo-log files,etc.
      3. Oracle-Software : This contains the Oracle-kernel which is much like a sub-operating system on top of the host system , because it coordinates the memory-structures and processes (Instance) and the file updates (Datafiles) on the hard drives.
    3. List the Structures involved in connecting a user to an Oracle Instance
    4. The Oracle server has a set of memory structures and operating process called "The Instance" which control the actions that occur between the end user and the database, its main components are:

      1. SGA
        1. Database Buffer Cache(depends on DB_BLOCK_SIZE & DB_BLOCK_BUFFERS parameters)
        2. Redo Log Buffer (depends on LOG_BUFFER , monitored V$SYSSTAT )
        3. Shared Pool (depends on SHARED_POOL_SIZE )
          1. Library Cache
            1. Shared SQL Areas (Contains the parse tree and execution plan for a single SQL statement)
            2. Private SQL Areas (Contains data such as bind information and runtime buffers,each session that issues a SQL statement has a private SQL area;many private SQL areas can be associated with the same shared SQL area, if a session is connected via a dedicated server private SQL areas are in the users PGA. If the sessionis connected via the multi-threaded server, the private area is kept in the SGA)
              1. Persistent (Contains bind information that persists accorss executions,code for datatype conversion and other state information, in contrast to Runtime,the persistent area remains waiting after the statement completes, the open cursors that will not be used shoudld be closed to free the persistant area and to minize the amount of memory required for the application)
              2. Runtime ( Contains information used while the SQL statement is being executed, it is the first step in an execute REQUEST, it releases this memory when the statement finishes)
          2. Dictonary Cache (Also known as the row cache, because it holds data as rows instead of buffers)
          3. Control Structures
        4. Large Pool (Optional,used for large memory allocations)
      2. PGA : A PGA (Process Global area ) is a memory region containing data and control information for a single process. One PGA is allocate for each server process.
        1. Stack Space: A PGA always contains stack space, which is memory allocate to hold a sessions variables,arrays, and other information.
        2. Session Information : If the instance is in single-serer mode, the PGA also contains information about the users session, such a private SQL areas. If the instane is in multi-threaded server, this session information is not in the PGA, but is instead allocated in the SGA. The PGA's initial size is fixed and OS specific, however the parameters OPEN_LINKS and DB_FILES affect the sizes of PGAs.
        3. Sort Areas : Use memory from the PGA of the Oracle server process that performs the sort on behalf of the user process. However, a part of the sort area ( up to SORT_AREA_RETAINED) exists in the runtime area of the process's private SQL area. The sort area can grow up to limit SORT_AREA_SIZE.
      3. Software Code Areas : Portions of memory used to store Oracle code
      4. Background Processes:
        1. PMON (Process Monitor, performs process recovery when a user processes fails, it is reponsible for cleaning up the database buffer cache and freein resources that the user process was using )
        2. SMON (System Monitor, Performs crash recovery , if necessary at instance startup, it is also responsible for coalescing contiguos free space within tablespaces, and cleaning up temporary segments that are no longer in use)
        3. DBWR (Writes the modified (dirty) buffers in the database buffer cache to disk)
        4. LGWR (Writes the redo log buffer contents to a redo log file on disk, it write of the buffer to disk when : A user process commits a transaction, every three seconds, when the redo-log buffer is one-third full, when a DBWn process writes modified buffers to disk)
        5. ARCH : Copies online redo log files to a designated storage device once they become ful or when the ALTER SYSTEM SWITCH LOGFILE command forces a log switch.
        6. CKPT (when a checkpoint ocurrs , Oracle must update the headers of all datafiles to record the details of the checkpoint.)
        7. RECO : Used with distributed database configurations to automatically resolve failed distributed transactions
        8. SNPn ( Job Queue/Snapshot Process): With the distributed database configuration, up to 36 job queues can automatically refresh table snapshots.
        9. LCKn : In Oracle Parallel Server, a lock process provides inter-instance locking
        10. QMN ( Queue Monitor) (Optional for Oracle Advanced Queuing which monitors the message queues)
        11. Snn (shared Server Processes serve multiple client requests in the multi-threaded server configuration )
        12. Dnn (Dispatcher Processes, suppor multi-threaded configuration by allowing user processes to share limited number of server processes.
        13. Pnnn (Parallel query processes)
    1. List the Stages in processing:queries,DML statements, COMMITS.
    2. Queries : The server session hashes de SQL statement passed to it and compares that hash number with the hash numbers of statements already saved in the Shared SQL area. If an exact duplicate of the statement is found in the shared pool, the parsed form of the statement and the execution plan that are already stored are used. If a match is not found in the shared pool, the server session parses de statement.

      Next, the server checks to see wheter the data blocks necessary to complete the transaction (query) are already stored in the database buffer cache. If the block are not in the buffer, the server reads the necessary blocks from the data files and copies them into the cache. NOW it returns the information.

      DML Statements : It performs the same steps up to when the data is copied into the buffer (data block buffer cache), before it returns the infromation (as is the case with a query),the blocks in memory (the cache) are modified accordingly. Once they are modified in memory these blocks are marked as dirty, and are placed on the dirty list. Redo log information is also generated on this transaction and placed in the redo-log cache. Up to this point any of the following can occur:

      1. The user commits, this signals the LGWR to flush the redo-log buffer to the online redo-log file.
      2. The redo information that was generated causes de redo-log buffer to become one-third full. This triggers a redo-log buffer flush by LGRW
      3. The number of dirty blocks reached a threshold length. This triggers DBWR to flush all the dirty blocks in the database buffer cache to the data files, which in turn also causes LGWR to flush the redo-log buffers to the online-redo log files.
      4. A database checkpoint occurs. This triggers the database buffer cache ( with DBWR) as well as the redo-log buffers ( with LGRW) to flush.
      5. The number of available free buffers in the buffer cache drops below the threshold value. This also causes the database buffer cache to flush
      6. An unrecoverable error ocurrs. This forces the transaction to be terminated and rolled back and an error reported back to the server session.

      NOTE: The transaction never records as successful until the redo-log buffer successfully writes to the online redo-log files. This demonstrates the importance of the LGRW and ARCH processes.

  1. Getting Started with the Oracle Server.
    1. Identify the Features of the Universal Installer
    2. Install and Deinstall Products. Upgrade Oracle version

    3. Setup operating system and password file authentication
    4. Via Operating System : When setting up a user to be authenticated via the operating system the user name defined in the operating system will differ from the user name in Oracle by a prefix defined in the init.ora file, name OS_AUTHENT_PREFIX , this prefix defaults to OPS$.Example:

      If in the UNIX system there is a user by the name of alfonso , if an Oracle account by the name of OPS$ALFONSO , then this user will be able to login automatically into SQL*Plus, once authenticated by the OS.

      The user can be defined in various way inside Oracle :

        1. Allowing access through another OS account: This method will allow that the account be accessed from another OS user account
        2.                create user OPS$alfonso identified by ALFONSO;  
        3. Authentication only possible through the OS user account:
          1. create user OPS$alfonso identified externally
          2. create user OPS$alfonso identified VALUES 'no way'
        4. NOTE: If a user is connecting via remote access and the connection is nonsecure (non-encrypted) then the parameter REMOTE_OS_AUTHENT must be set to TRUE in the init.ora (the default is false)

      Via Password File : When the user cannot be authenticated via the Operating System, then a password file is created. To generate a password file it is necessary to use the ORAPWD utility.

        1. Create the password file with the command :
          	 ORAPWD FILE=filename  PASSWORD=password  ENTRIES= max users  

          The password parameter specifies the password for that must be used in order to login as a DBA to the database. The ENTRIES parameter cannot be modified afterword,so it is recommended to be set at a high value.

        3. Set the parameter REMOTE_LOGIN_PASSWORDFILE in the init.ora file. This parameter has three values: NONE,SHARED and EXCLUSIVE .
        4. The NONE value causes Oracle to behave as if the password file did not exist (this is the default).

          The SHARED value enables the password file to be used by multiple databases. However the only users recognized by a SHARED password file are SYS and INTERNAL , thus you cannot add users to a SHARED password file.

          The EXCLUSIVE is of course used for a single database.

        5. Once this is done and the database has been restarted, you connect internal , then grant either the SYSOPER or SYSDBA to your users. SYSDBA gives the user DBA authority; SYSOPER lets the user perform database operations suppor activities. The views V$PWFILE_USERS holds information on which users have been granted these privileges.

      The use of a Password file does not-prevent OS-authenticated users from connecting if they meet the criteria for OS authenticating.

    5. List the main components of the Oracle Enterprise Manager and their uses
    6. Oracle Enterprise Manager provide a framework for an enterprise wide distributed system management solution. Enterprise Manager is open and extendible, Tcl is used to submit commands to remote operating systems and databases for execution. The implementation of Tcl used by Enterprise Manager,is known as OraTcl, and includes extensions that enable functions you need to fully manager an Orcle database environment:

      1. Start and Stop Oracle databases
      2. Execute SQL
      3. Access operating system resources and functions
      4. Access Simple Network Management Protocol (SNMP) Management Information Base (MIB) variables describing Oracle databases.
      5. Acces non-oracle SNMP-enabled services and devices.

      NOTE on SNMP: Originally SNMP was used to communicate with network devices, but it is now used to communicate with applications as well. Application developers now integrate applications within the console using Object Linking and Embedding (OLE) or calls to one of the published APIs specific to Oracle Enterprise Manager.

      When information moves over the network between the console and the intelligent agents, it can be secured with Oracle Server Network Services using the Net8 Advanced Networking Option (ANO). This level of security makes it possible to administer remote databases over the Internet.

      In addition to messages between the communications daemon and the intelligent agents, the application components such as the database administration tools communicate directly with the remote databases by using SQL over Net8. Tasks performed by the application components directly on remote databases in real time do not use the communication daemon. The communication daemon uses either native TCP/IP or Net8 TNS (Transparent Network Substrate) connections to communicate with remote intelligent agents to perform core console functions such as job scheduling and event management.

      The intelligent agent autonomously executes and manages remote activities. After the communication dameon instructs a remote agent to execute a particular script at a given time, the script executes independently of the console. If the console is unavailable when it is time to execute the remote job, the agent manages the execution and buffers up to 500 returnes messages. The agent passes this information back to the console, when the console becomes available.

      Getting Started : After installing the OEM, it is recommended that a repositary be generated as this provides an easier way to login to the console and store prefered credentials between sessions. This is even more beneficial when there are various systems administrators, each one should have their own repository so he or she can store his information.The Adminsitration Tools which are provided in OEM can can accessed in four ways.

      1. Directly from the Windows Taskbar:
      2. From th Enterprise Manager console application launch palette. Before using the console however a repositary must be created.This process is done automatically. The first time the console starts with a given UserID, Repositary Manager describes the sub-components that are necessary to start the console. When responding OK at the prompt, Repositary manager creates a repositary for itself, "Enterprise Manager", "Software Manager", and it starts the "Discover New Services Wizard". If there are nodes on the network that already have Oracle Intelligent Agent configured,this wizard communicates with the remote nodes to populate the navigation tree on the console.NOTE:Because a repository is built automatically during the first login, you must be careful to avoid creating a repository for a system user,as the repository will be generated in the SYSTEM tablespace.
      3. From within the console on the menu bar by selecting Tools,Application.Enterprise Manager is now ready to be used, but there are no databases or listeners known to the Enterprise Manager unless the "Discover New Services Wizard" took advantage of remote intelligent agents to automatically discover and define them.
      4. After defining the listeners and databases, Database Administration are accesible by the fourth method, By right-clicking on one of the newly added databases in the upper-left navigator window.

      The FOUR primary CONSOLE functions

      1. Navigator : Provides a tree structure representing all databases,servers, Net 8 listeners, Web Servers, and groups of these components. This is the most widely used part of the Enterprise Manager console. It behaves much like Internet Explorer. The menus enables the creation of database obejects or the deletion of them. Some object changes are possible directly from the Navigator using the "Quick Edit" wheras more complex changes might require accessing one of the Database Administration Tools that operate on the selected object by selecting Related Tools.
      2. Group : (formerly Map) provides a way to geographically track the status of systems and drill down into systems to exmine listeners and databases, in conjunction with event functionality it provides system status at a glance. Uses bitmap files (*.bmp) to presents organizational, geographical maps, used in conjunction with remote intelligent agents, Group can indicate the status of each object on the map with a small signal flag.The event management component enable the DBA to define conditions or thresholds neccessary to trigger changes in the status indicated by the signal flags in the maps.
      3. Job Schedules : Is also dependant on the availability of remote intelligent agents. In case a job runs to resolve or prevent a fault ( in event manager ) then Job Scheduler handles the task. In addition, the Job Scheduler can launch,monitor,and report completion of a series of activities on remote systems. It provides a way for DBAs to automate tasks at specific times or based on events in the OS environment. Scheduling is especially valuable in complex environements with many systems. Job can perform any tasks requiring OS commands or SQL. The remote agent handles actual extension independant of the Enterprise Manager console.
      4. Event Management : Is dependant on the availability of remote intelligent agents. It communicates with remote intelligent agents to track activities and conditions on remote systems.Using this capability, Enterprise Manager sends mail,pages people, or runs a job to correct a fault or capture diagnostic information for later analysis. The Event Management System is the Enterprise Manager component that manages the Health and welfare of remote databases and the services that make them available to applications. Unlike Job which triggers tasks on a Specific Schedule, EMS works with remote intelligent agents to constantly monitor for unusual conditions and then manage the events on specifications defined by the DBA. Like Job, EMS uses the communication deamon on the local client and remote intelligent agents on each system to monitor and respond to events. The intelligent agent actually monitor system resources, so the events are managed 24 hrs without the direct participation of the Enterprise Manager console. A variety of predefined events come ready to configure and register.

      The following Database Administration Tools are available :

        1. Instance Manager : All the configuration information stored in the init<SID>.ora file is accessible through the Instance Manager. The DBA can start-shutdown database instances, mount and open databases,and toggle archive logging on and off.
        2. Schema Manager : It administers clusters,constraints, database links, functions, indexes, packages, partitions, priviliges , procedures, queues, refresh groups, sequences , snapshot logs, synonyms, tables, triggers, views and the relationship thse object have to each other.

          Using Schema manager is useful for building objects such as temporary tables outside the scope of the production database design,however Schema Manager is no replacement for database design tools, such as Oracle Designer, Computer Systems Advisors Silverrrun, Logic Works Erwin, or Powersoft PowerDesigner, using Schema manager in one of these environments is just as counterproductive as using native DDL.
        3. SQL Worksheet : It is the Database administration tool to Server Manager and SQL*Plus. Targeted to DBAs it provides a way to submit SQL, PL/SQL, DBA commands , and SQL scripts.
        4. Security Manager : The DBA can easily create,alter and drop security objects and associate privileges, roles, quotas, and profiles to users and other roles.
        5. Storage Manager : The DBA can create,alter and drop storage. It adds rollback segments and data files to tablespaces, takes tablespaces online and offline, and takes them into and out of read-only mode.It can also shrink rollback-segments.
        6. Backup Manager : It provides two subsystems for managing recovery and backup for Oracle8i : Operating System Backup and Oracle8i Recovery Manager . The OS backup doesn't provide a comprehensive solution for 8i. (it is Oracle 7 orientated ). Oracle 8i Recovery Manager is a comprehensive recovery and backup solution included features such as point-in-time database recovery,scripting, and control file recovery.
        7. Data Manager : Assists DBAs to perform imports,exports, and table loads. This tool provides an easy to use interface that generates parameter files on the SQL*Loader and the import/export utilities.
        8. Sofware Manager : Provides a method for database administrators to distribute Oracle software and related configuration information across a network of computers by using an easy-to use central client interface working with remote intelligent agents .
        9. Intermedia Text Manager
        10. Replication Manager
      The Diagnostic Pack

      The Enterprise manager Diagnostic pack is a value added component of Enterprise Manager that provides tools for performance monitoring and tracking, session monitoring, trace management, and lock management. The diagnostic pack provides tools necessary to capture, store, and analyze performance data to meet this objective.

      Performance Manager is the centerpiece of the Diagnostic pack . This tool provides real-time performance monitoring and the capability to record performance characterstics for later analysis.It has built in charts and tables for collection and analysis of contention, global database statisitics, I/O, workload levels, memory, and parallel server information.

      Trace Manager is a comprhensive tool for collecting data for performance management, capacity planning, and workload managment. By using the provided API,trace manager can even collect information from third-party and custom applications. Job (the component of the Enterprise Manager) must be configured before using Trace Manager. The tool also uses its own repository and an Oracle Expert repository, which it builds the first time it is accessed by a userr who hasn't used Expert or Trace.

      Top Sessions Top sessions enables database administrators to monitor database sessions and kill them if necessary due to inactivity or over-utilization of resources. This tool monitors all sessions or only the most active based on consumption of specified resources such as memory or I/O.

      Capacity Planner The capacity planner of the diagnostic pack is used to collect and store database and operating system statistics in a repository that can be later analyzed.

      Lock Manager It is a utility for monitoring database locks and troubleshooting problem blocking and waiting conditions.

      The prerequiste for this utility are the existence of the views: V$LOCK, V$SESSION, DBA_OBJECTS

      The Tuning Pack

      This utility includes:

      Tablespace Manager : Provides detailed information on storage and utilization in tablespaces, rollback segments, and data files.

      Oracle Expert : Expert is the implementation of a performance management methedology. Wheras other diagnositc packs provide information for database administratos to analyze, Expert applies a set of rules to the data to develop conclusions and make recommenadtions to improve performance.

      SQL Analyze : SQL Analyze is a tool that will enable you to performance tune any troublsome SQL statement.

      1. Replication Manager : Is useful for environments that make heavy use of Oracle8i replication features.
      2. Oracle Fail Safe: Manages high availability environments implemented on Windows NT clusters.
      3. Oracle Biometrics: Works with specialized hardware to administer fingerprint identification used with Advanced Networking Option.
      4. Change Management Pack : This helps the DBA manage object definition changes across mulitple databases.
      5. Management Pack for Oracle Applications: The job for managing the multiple tier Oracle Application environment is made simpler by this pack.
      6. Standard Management Pack: This pack is a group of general utilities to help the DBA troubleshoot problems,track changes, and tune database indexes.

  2. Managing the Oracle Instance
    1. Create the Parameter File
    2. The default parameter file (init.ora) is located at $ORACLE_HOME/dbs directory, the default naming for this file is initSID.ora, where SID is the system identifier for the database. At the very least this file should contain the following three parameters: CONTROLFILE, DB_BLOCK_BUFFER and DB_NAME

    3. Startup the Instance and open the database
    4. The Instance is named based on the ORACLE_SID environment variable of the OS. The exact time the instance creation occurs is called nomount what this does is allocate the SGA and start the background processes.

             # svrmgrl 
             > connect internal as sysdba 
             > startup nomount pfile=/u01/app/admin/init(SID).ora 

      If the path to pfile is not specified, then the command will look in the directory $ORACLE_HOME/dbs/ for a file named init($SID).ora , where SID is the environment variable SID.

      The next stage in the process is mount stage, in this stage the control file(s) is read and accessible,and queries and modifiactions to the data stored in the control file can be made.

      alter database mount

      The final stage, the database is opened, in this stage the database files referenced by the control files are locked for exclusive use by the instance.

      alter database open

      NOTE: The alter database mount can be skipped if instead of the startup nomount the command startup mount is issued.

    5. Close the Database and Shutdown the Instance
    6. The keyword shutdown is issued from svrmgr in order to shutdown the Database and Instance.

      The command shutdown abort is used to closed down the Instance, Oracle will need to perform recovery prior to opening the database with command alter database open , the command shutdown is used when having deadlocks in the database application, because Oracle will not be able to shut down the database if the command shutdown immediate is issued.

    7. Get and set parameter values
    8. Changes made to parameter values in the init.ora file do not take effect until the database is shutdown and restarted. These values can queried from the view V$PARAMETER .There is an exception, these parameters can be changed with the command ALTER SYSTEM ,when the system (Instance) is up , however in case the instance is shutdown the changes will not take effect on the next startup.

    9. Manage Sessions
    10. You can set a limit on the number of concurrent sessions that can connect to a database. To set the maximum number of concurrent sessions for an instance, set the parameter LICENSE_MAX_SESSION, once this limit is reached only users who have the RESTRICTED SESSION system privilege can connect to the instance; this allows DBAs to kill unneeded sessions, allowing other sessions to connect.

      In addition to this parameter you can set a warning limit on the number of concurrent sessions, this is set with the parameter LICENSE_SESSIONS_WARNING. Once this limit is reached, additional users can continue to connect up to the maximum limit, but Oracle sends a warning for each connecting user.

      A session marked to be terminated is displayed with the V$SESSION view.To KILL a session ,after querying the V$SESSION columns SID and SERIAL# , you issue the following command: (Assuming SID=7 and SERIAL=15, and of course the STATUS(column) INACTIVE )


      After the command is issued the STATUS (column) is changed to KILLED and the SERVER(column) is changed to PSEUDO

      You can also see the current limits of all the license setting, the currnet number of sessions, and the maximum number of concurrent sessions for the instance by querying the V$LICENSE data dictionary view.

      V$LOCK shows which sessions hold locks on certain processes . Net 8 adds support for session multiplexing.

    11. Monitor the ALERT file and the trace file.
    12. The ALERT file , typically name SIDalrt.log contains : all internal errors (ORA-600), block corruption errors (ORA-1578) and deadlock errors, administrative operations, such as CREATE /ALTER /DROP /DATABASE /TABLESPACE /ROLLBACK SEGMENT, SQL statements and STARTUP,SHUTDOWNS, and ARCHIVE LOG, several messages and errors relating to the functions of shared server and dispatcher processes, errors occurring during the automatic refresh of a snapshot, the values of all the initialization parameters at the time the database and instace start. It is stored in the location specified by BACKGROUND_DUMP_TEST. Critical functions are always logged here, as well as database startup and shutdown messages.

      Background processes also create their own trace files where problems and failures are logged. Background proccesses are logged to the directory specified by the parameter BACKGROUND_DUMP_TEST, and the tracefiles for server processes are logged to USER_DUMP_TEST. Both of these files are named processname_number.trc

      In order to enbale SQL tracing, issue the command ALTER SESSION SET SQL_TRACE=TRUE, or set the parameter SQL_TRACE=true in init.ora