1 / 50

Managing database instance

Managing database instance. Management Framework. Oracle Database 11 g Release 2 management framework has 3 components : Database instance Listener Management interface.

radha
Download Presentation

Managing database instance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing database instance

  2. Management Framework • Oracle Database 11g Release 2 management framework has 3 components: • Database instance • Listener • Management interface Each of these components must be started before you can use the services of the component and must be shut down cleanly when shutting down the server hosting the Oracle database. When using grid Management Agent or Listener Database Control Management interface

  3. Database Startup Incremental Startup • Nomount • Server parameter file is read • Mount • database is located. • Control file is required • redo and data files are opened • Open • data and redo log files • start recovery if needed/ open • Listener is open and ready to accepts requests. • Consistency check by SMON (then periodic) • PMON monitor users

  4. Starting Up an Oracle Database Instance: NOMOUNT OPEN STARTUP MOUNT NOMOUNT Instance started • initialization file is read • Memory structures are created • Oracle processes are started SHUTDOWN

  5. Starting Up an Oracle Database Instance:MOUNT OPEN STARTUP MOUNT Control file opened for this instance NOMOUNT Instance started SHUTDOWN

  6. Starting Up an Oracle Database Instance: OPEN OPEN STARTUP All files opened as described by the control file for this instance MOUNT Control file opened for this instance NOMOUNT Instance started SHUTDOWN

  7. Quick Descriptions • Instance/database • Initialization file contains operating parameters for instance/database • Spfile (dynamic) • pfile • Control file binary file containing info on active database • Redo logs • Check point • datafiles • Controlfile and redo log files protect data file

  8. Shutdown • Can be done incrementally • Must know how shutdown effects existing connections. • Normal : wait until all transactions disconnect • Immediate: smon rolls forward committed transactions • Transactional: Uncommitted transactions continue • Abort : like power cut!!!

  9. Shutdown Modes • Shutdown modes: • A = ABORT • I = IMMEDIATE • T = TRANSACTIONAL • N = NORMAL

  10. Shutdown Options During: SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL or SHUTDOWN IMMEDIATE • On the way down: • Uncommitted changes rolledback, forIMMEDIATE • Database buffer cache written todata files • Resources released • On the way up: • No instance recovery Consistent database

  11. Shutdown Options During: SHUTDOWN ABORT or Instance failure or STARTUP FORCE • On the way down: • Modified buffersnot written to data files • Uncommitted changes not rolled back • On the way up: • Online redo log files used to reapply changes • Undo segments used to roll back uncommitted changes • Resources released Inconsistent database

  12. Overview of Primary Components Instance Userprocess SGA Shared pool Library cache Redo logbuffer cache Databasebuffer cache Serverprocess Data Dict.cache PGA PMON SMON DBWR LGWR CKPT Others Data files Control files Redo log files Parameter file Archived log files Password file Database

  13. Oracle Instance • Is a means to access an Oracle database • Always opens one and only one database • Consists of memory and process structures Instance SGA Shared pool Memory structures Library cache Database buffer cache Redo logbuffer cache Data Dictionarycache Background structures/processes PMON SMON DBWR LGWR CKPT Others

  14. Header Data files (includes data dictionary) Control files Online redo log files Oracle database The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information. • Control files • Data files • Redo log files

  15. Oracle Database Data files Control files Redo log files Archived log files Parameter file Password file Oracle Database

  16. Data File • Tempfile • Special type of data file • Holds temporary information • Space freed when done/logout • Data files • Binary • Data stored as oracle blocks • OS blocks • Largest files type • Actual data • Exist all the time • Extension is NOT important but usually .DBF • Data File Header • First blocks of the data file (by default 64K) • Manage datafiles internal workings • Backup/recovery info • Free space infromation • File status detail

  17. Parameter Files • Entries are specific to the instance being accessed • There are two kinds of parameters: • Explicit: Having an entry in the file • Implicit: No entry within the file, but assuming the Oracle default values • Multiple files can be used for a single database to optimize performance in different situations.

  18. SPFILE spfileSID.ora • Binary file with the ability to make changes persistent across shutdown and startup • Maintained by the Oracle server • Records parameter value changes made with the ALTER SYSTEM command • Can specify whether the change being made is temporary or persistent • Values can be deleted or reset to allow an instance to revert to the default value • Can be dumped to text file for manual editing

  19. PFILEinitSID.ora • Default location is $ORACLE_HOME/dbs. • The PFILE is a text file that can be modified with an operating system editor. • Modifications to the file are made manually. • Changes to the file take effect on the next startup. Not dynamic

  20. PFILE Example # Initialization Parameter File: initdb01.ora db_name = db01 instance_name = db01 control_files = ( /u03/oradata/db01/control01db01.ctl, /u03/oradata/db01/control02db01.ctl) db_block_size = 4096 db_block_buffers = 500 shared_pool_size = 31457280 # 30M Shared Pool db_files = 1024 background_dump_dest = /u05/oracle11g/admin/db01/bdump user_dump_dest = /u05/oracle11g/admin/db01/udump core_dump_dest = /u05/oracle11g/admin/db01/cdump undo_management = auto undo_tablespace = undtbs . . .

  21. Initialization Parameter Files (continued) • Types of Values for Initialization Parameters • The Oracle database server has the following types of values for initialization parameters: • Boolean • String • Integer • Parameter file • Reserved • Big Integer

  22. Initialization Parameter Files (continued) Derived Parameter Values • values are calculated from the values of other parameters. • Do not alter values for derived parameters. If you do, the value that you specify overrides the calculated value. Example • The default value of the SESSIONS parameter is derived from the value of the PROCESSESparameter. • If the value of PROCESSES changes, the default value of SESSIONS changes as well unless you override it with a specified value.

  23. Initialization Parameter Files (continued) Operating System–Dependent Parameter Values • values or value ranges of some initialization parameters depend on the host operating system. Example • DB_FILE_MULTIBLOCK_READ_COUNTparameterspecifies the maximum number of blocks that are read in one I/O operation during a sequential scan; this parameter is platform dependent. • The size of those blocks, which is set by DB_BLOCK_SIZE, has a default value that depends on the operating system.

  24. Initialization Parameter Files (continued) Setting Parameter Values • Initialization parameters offer the most potential for improving system performance. • Some parameters set capacity limits but do not affect performance. Example • If OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. • Increasing the values of parameters may improve your system’s performance • increasing most parameters also increases the system global area (SGA) size. • A larger SGA can improve database performance up to a point then swapping of SGA will degrade the perfromance

  25. Control File The control file is a binaryfile that defines the currentstateof the physical database.. • Loss of the control file requires recovery • Is read at MOUNTstage • Is required to operate • Is linked to a single database • Should be multiplexed • Maintain a number of copies such that even if you lose a disk, you do NOT lose the control files • Maintains integrity of database • Extension is usually .ctl or .con • Any extension is ok • Sized initially by CREATE DATABASE Database Control files

  26. Simplified Initialization Parameters • A basic parameter is defined as one that you are likely to set to keep your database running with good performance. • All other parameters are considered to be advanced. • In the majority of cases, it is necessary to set and tune only the 30 basic parameters to get reasonable performance from the database. • In rare situations, modification of the advanced parameters may be needed to achieve optimal performance. • There are about 314 advanced parameters • Basic • Advanced CONTROL_FILES DB_BLOCK_SIZE PROCESSES UNDO_TABLESPACE … DB_CACHE_SIZE DB_FILE_MULTIBLOCK_READ_COUNT SHARED_POOL_SIZE …

  27. Initialization Parameters: Examples

  28. Databasebuffercache Redo logbuffer KEEP buffer pool Shared pool User Global Area User Global Area Stack Space Stack Space RECYCLE buffer pool Streams pool nK buffer cache Java pool Large pool Initialization Parameters: Examples PGA PGA System Global Area (SGA) SGA_TARGET(Total size of all SGA components) MMEMORY_TARGETT(Total size of system-wide usable memory)

  29. Initialization Parameters: Examples (continued) • SGA_TARGETspecifies the total size of all SGA components. If SGA_TARGET is specified, the following are automatically sized: • Buffer cache (DB_CACHE_SIZE) • Shared pool (SHARED_POOL_SIZE) • Large pool (LARGE_POOL_SIZE) • Java pool (JAVA_POOL_SIZE) • Streams pool (STREAMS_POOL_SIZE) • If these automatically tuned memory pools are set to nonzero values, the values are used as minimum levels by Automatic Shared Memory Management (ASMM). • The following pools are manually sized components and are not affected by ASMM: • Log buffer • Other buffer caches (such as KEEP and RECYCLE) and other block sizes • Fixed SGA and other internal allocations • The memory allocated to these pools is deducted from the total available memory for SGA_TARGET when ASMM is enabled.

  30. Initialization Parameters: Examples

  31. Using SQL*Plus to View Parameters SQL> SELECT name , value FROM V$PARAMETER; NAME VALUE ------------ ---------- lock_name_space 2 processes 150 sessions 247 timed_statistics TRUE timed_os_statistics 0 … SQL>SHOW PARAMETER SHARED_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- --------------------- shared_pool_size big integer 0 SQL> show parameter para NAME TYPE VALUE ------------------------------------ ----------- --------------------- fast_start_parallel_rollback string LOW parallel_adaptive_multi_userboolean TRUE parallel_automatic_tuningboolean FALSE parallel_execution_message_size integer 16384 parallel_instance_group string …

  32. Using SQL*Plus to View Parameters (continued) • Description of the view: SQL> descV$parameter Name Null? Type ---------------------------------------- ------- ------------- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBER VALUE VARCHAR2(4000) DISPLAY_VALUE VARCHAR2(4000) ISDEFAULT VARCHAR2(9) ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISINSTANCE_MODIFIABLE VARCHAR2(5) ISMODIFIED VARCHAR2(10) ISADJUSTED VARCHAR2(5) ISDEPRECATED VARCHAR2(5) ISBASIC VARCHAR2(5) DESCRIPTION VARCHAR2(255) UPDATE_COMMENT VARCHAR2(255) HASH NUMBER

  33. Using SQL*Plus to View Parameters (continued) • V$SPPARAMETER: Displays information about the contents of the server parameter file. • If a server parameter file was not used to start the instance, each row of the view will contain FALSE in the ISSPECIFIED column. • V$PARAMETER2: Displays information about the initialization parameters that are currently in effect for the session, with each parameter value appearing as a row in the view. • A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view. • V$SYSTEM_PARAMETER: Displays information about the initialization parameters that are currently in effect for the instance.

  34. Changing Initialization Parameter Values • Static parameters: • Can be changed only in the parameter file • Require restarting the instance before taking effect • Account for about 110 parameters • Dynamic parameters: • Can be changed while database is online • Can be altered at: • Session level • System level • Are valid for duration of session or based on SCOPE setting • Are changed by using ALTER SESSION and ALTER SYSTEM commands • Account for about 234 parameters

  35. Changing Initialization Parameter Values (contd) Use “ALTER SYSTEM SET” statement to set or change initialization parameter values. • SCOPE=SPFILE: The change is applied in the server parameter file only. No change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup and is persistent. This is the only SCOPE specification allowed for static parameters. • SCOPE=MEMORY: The change is applied in memory only. The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is immediate but not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed. • SCOPE=BOTH: The change is applied in both the server parameter file and memory. The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is persistent because the server parameter file is updated. For static parameters, this specification is not allowed.

  36. Changing Parameter Values: Examples SQL> ALTER SESSION SET NLS_DATE_FORMAT ='monddyyyy'; Session altered. SQL> SELECT SYSDATE FROM dual; SYSDATE ----------- jun 18 2009 SQL> ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=2 COMMENT='Reduce from 10 for tighter security.' SCOPE=SPFILE; System altered.

  37. Contents of a control file • Names and locations of data files and redo log files • Recovery Information • Backup Information • Checkpoint Information • Archiving Information • Database Name • Log History • Current logging information

  38. Using Redo Log Files Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure. • Redo log files are organized into groups. • An Oracle database requires at least two groups. • Each redo log within a group is called a member. Database Redo log files

  39. Redo Log Files • Binary file • Store info from log buffer • LGWR writes into redo logs • Extensions .LOG or .RDO usually • Contains all information required to required to recover lost data • Every SQL statement that change data can be reconstructed • Multiplex (save mirrored copies) members • on different disks

  40. Structure of Redo Log Files Group 1 Group 2 Group 3 Disk 1 Member Member Member Disk 2 Member Member Member • Redo logs are used in a cyclic fashion. • When a redo log file is full, LGWR will move to the next log group. • This is called a log switch • Checkpoint operation also occurs • Information is written to the control file Do not switch more frequently than 15-30 minutes

  41. Forcing Log Switches and Checkpoints • Log switches can be forced using the ALTER SYSTEM SWITCH LOGFILE command. • Checkpoints can be forced using: • Setting FAST_START_MTTR_TARGET parameter • ALTER SYSTEM CHECKPOINT command ALTER SYSTEM CHECKPOINT;

  42. Archived Redo Log Files Filled online redo log files can be archived. • Two advantages exist to archiving redo logs: • Recovery: A database backup, together with online and archived redo log files can guarantee recovery of all committed transactions. • Backup: Can be performed while the database is open. • By default a database is created in NOARCHIVELOGmode.

  43. Archived Redo Log Files • Archiving redo log files is accomplished by ARCn(Archiver) or manually through SQL statements. • Whenever a redo log is successfully archived, an entry is added to in the control file recording • the archive log name, • log sequence number, and • high and low SCN number • A filled redo log file cannot be reused until • a checkpoint has taken place and • the redo log file has been backed up the ARCn process. • Archived redo log files can be multiplexed. • Archived redo log files must be maintained by the DBA.

  44. Phases of database startup and shutdown

  45. Management Framework • Oracle Database 11g Release 2 management framework has 3 components: • Database instance • Listener • Management interface When using grid Management Agent or Listener Database Control Management interface

  46. Listener.ora • is a SQL*Net configuration file used to configure Oracle Database Listeners • required to accept remote connection requests • Normally resides in the ORACLE HOME\NETWORK\ADMIN directory or in the directory define by the $TNS_ADMIN variable. • consists of the following elements: • Name of the listener • Protocol addresses that the listener is accepting connection requests on • Database services • Dynamic service registration, eliminates the need for static configuration of supported services. However, static service configuration is required if you plan to use Oracle Enterprise Manager. • Control parameters • Queue size • Receive buffer size, send buffer size

  47. Sample Listener.ora # listener.ora Network Configuration File: /app/oracle/product/10.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. # SID_LIST_LISTENER = # (SID_LIST = # (SID_DESC = # (SID_NAME = PLSExtProc) # (ORACLE_HOME = /app/oracle/product/11.1.0/db_1) # (PROGRAM = extproc) # ) # ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) )

  48. How to modify listener • Edit listener.ora • Lsnrctl Lsnrctl start listener Lsnrctl stop listener Lsnrctl status listener • Net configuration assistant • Net manager

  49. Starting and Stopping Database Control (Enterprise Manager) • Database Control • stand-alone management console for databases that are not connected to the Grid Control framework • Each database that is managed with Database Control has a separate Database Control installation • From any one Database Control, you can manage only one database. • Navigate to the $ORACLE_HOME/bin directory if this directory is not in your operating system (OS) path. • there are two $ORACLE_HOME locations if Grid Infrastructure is installed • both contain the emctl utility. • emctl utility should always be invoked using the Oracle database $ORACLE_HOME and not the Grid Infrastructure $ORACLE_HOME.

  50. Using Database Control • Database Control uses a server-side agent process. This agent process automatically starts and stops when the dbconsole process is started or stopped. • Before using Database Control, ensure that a dbconsole process is started. • Command to start the dbconsole process: • emctl start dbconsole • Command to stop the dbconsole process: • emctl stop dbconsole • Command to view the status of the dbconsole process: • emctl status dbconsole

More Related