Transcript
Oracle 10g Database Associate Student Guide (V2) Volume 2
This document contains proprietary information. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. This material or any portion of it may not be copied in any form or by any means without the express written permission of Jeremy Russell & Associates. Any other copying is a violation of copyright law and may result in civil and/or criminal proceedings. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Jeremy Russell & Associates Ltd., 1 Tucker Street, Watford, Herts. WD18 0AU, U.K. Whilst every precaution has been taken in the preparation of this information, Jeremy Russell & Associates Ltd. do not warrant that this document is error-free. No responsibility is assumed by Jeremy Russell & Associates Ltd. for any errors or omissions or for damages resulting from the use of the information contained herein. All products and company names are used for identification purposes only and may be trademarks or registered trademarks of their respective owners.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
10 0 PL/SQL for DBA's
ODBA10gDB-OCA-10-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 10 – PL/SQL FOR DBA'S Lesson 10 provides a DBA oriented overview of the use of PL/SQL for application developers and DBA tasks
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 271
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
Objectives
• After completing this lesson, you should have an understanding of the following: • PL/SQL architecture and block structure • Block types – anonymous blocks, procedures, functions, packages, triggers • PL/SQL at runtime • Oracle supplied packages
ODBA10gDB-OCA-10-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 10 provides a DBA oriented overview of the use of PL/SQL for application developers and DBA tasks. Topics include: PL/SQL architecture and basic block structures Block types, including anonymous blocks, procedures, functions, packages and triggers How PL/SQL operates at runtime Oracle supplied packages for database administration
V2: Page 272
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
PL/SQL Overview
• Proprietary extension to SQL • Used by developers – to automate regular end user procedures – to centralise frequently executed code
• Used by database administrators – to automate regular administration tasks – to execute Oracle provided scripts
ODBA10gDB-OCA-10-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Overview Procedural Language for SQL (PL/SQL) is a proprietary extension to the SQL language that provides logic, recursion, iteration and conditional processing. The language provides application developers and administrators with an efficient server-based mechanism for running frequently executed code close to where the data resides. Many DBA activities are carried out by executing Oracle supplied procedures. Further details are discussed later in this class. Full details regarding the PL/SQL language can be found in the Oracle PL/SQL User's Guide and Reference manual (part no B14261-01). Further information about the supplied packages are in the Oracle PL/SQL Packages and Types Reference manual (Part Number B14258-01).
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 273
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
Architecture Overview DEVELOPER CLIENT
INTERACTIVE CLIENT
Trigger Trigger Blocks Blocks
Anonymous Anonymous Blocks Blocks
SERVER PL/SQL PL/SQL ENGINE ENGINE PACKAGES PROCEDURES PROCEDURES
ODBA10gDB-OCA-10-4
FUNCTIONS FUNCTIONS
TRIGGERS TRIGGERS
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Architecture Overview A PL/SQL “engine” is embedded into the server software, which can be invoked either via client tool “anonymous blocks” or stored code (procedures, functions, packages or triggers). For client side use, an invocation mechanism is provided via the interactive SQL*Plus programs. Other Oracle Developer client tools (Forms and Reports) also include PL/SQL execution features. PL/SQL has limited support for embedded SQL commands; only DML (SELECT, INSERT, UPDATE and DELETE) and transaction (COMMIT, ROLLBACK, SAVEPOINT) commands are supported in native form. DCL and DDL statements are supported through an Oracle supplied procedure.
V2: Page 274
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
Anonymous Block
ODBA10gDB-OCA-10-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Anonymous Block An anonymous block is entered interactively using any of the SQL interface tools. This block is executed within the client interface, except for any embedded SQL statements which are extracted and passed to the server for execution. An example of an anonymous block is illustrated on the slide (“Ch10AnonymousBlock.sql”). Statements inside PL/SQL blocks are terminated with a semicolon. Keywords are case-insensitive, although most examples show keywords in uppercase and objects (tables and column names) are in lowercase. The language was originally based on ADA but exhibits some features of other block structured languages, including PASCAL and C. PL/SQL blocks can also be embedded inside Oracle 3GL interfaces, including PRO*C and PRO*COBOL.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 275
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
PL/SQL Block Structure
• DECLARE – Variable definitions – Database cursors – Exceptions
• EXECUTION – Business code
• EXCEPTION – Error handlers ODBA10gDB-OCA-10-6
DECLARE CURSOR q_tab IS SELECT * FROM target; e_failure EXCEPTION; c target.code%TYPE; n target.name%TYPE; BEGIN OPEN q_tab; LOOP FETCH q_tab INTO c, n; DBMS_OUTPUT.PUT_LINE(c||' '||n); EXIT WHEN q_tab%ROWCOUNT>3; END LOOP; CLOSE q_tab; EXCEPTION WHEN e_failure THEN NULL; END;
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Block Structure
• Declaration Section This section appears first in a PL/SQL block. It is used to declare variables, user-defined exceptions and database cursors. Variable types can be declared using any of the available Oracle column types, or linked to a specific ‘table.column’ type for enhanced independence.
• Execution Section The main business processing of a PL/SQL block appears in the execution section. Loops, logic and database access and update statements can be used.
• Exception Section This optional section is used to define non-procedural error handling routines. DECLARE CURSOR q_tab IS SELECT * FROM target; e_failure EXCEPTION; c target.code%TYPE; n target.name%TYPE; BEGIN OPEN q_tab; LOOP FETCH q_tab INTO c, n; DBMS_OUTPUT.PUT_LINE(c||' '||n); EXIT WHEN q_tab%ROWCOUNT>3; END LOOP; CLOSE q_tab; EXCEPTION WHEN e_failure THEN NULL; END;
V2: Page 276
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
PL/SQL Procedures
• Named routines to be called from any client or other procedure CREATE OR REPLACE PROCEDURE myproc AS • Stored once and shared by clients • Better runtime performance DECLARE w NUMBER; • Reduced network BEGIN ... traffic myproc; CURSOR q_tab IS SELECT * FROM target; e_failure EXCEPTION; c target.code%TYPE; n target.name%TYPE; BEGIN OPEN q_tab; LOOP FETCH q_tab INTO c, n; DBMS_OUTPUT.PUT_LINE(c||' '||n); EXIT WHEN q_tab%ROWCOUNT >= 3; END LOOP; CLOSE q_tab; EXCEPTION WHEN e_failure THEN ... NULL; END; END;
ODBA10gDB-OCA-10-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Procedures A PL/SQL procedure is a named code block that is stored in the database and can be referenced from any Oracle tool, application or other stored code. By storing code centrally, all users can be assured of running the same code. In addition, the DBA has a single place where code needs to be updated if application requirements change. All stored code is executed on the server. Since this is closer to where the data is stored, the use of stored code in an application provides better runtime performance overall. Network traffic is minimised since only the results of a procedure need to be shipped over the network. Procedures can be called by executing the name of the procedure. Although not illustrated in this example, procedures can receive and return parameters (“Ch10CreateProcedure.sql”).
CREATE OR REPLACE PROCEDURE myproc AS CURSOR q_tab IS SELECT * FROM target; e_failure EXCEPTION; c target.code%TYPE; n target.name%TYPE; BEGIN OPEN q_tab; LOOP FETCH q_tab INTO c, n; DBMS_OUTPUT.PUT_LINE(c||' '||n); EXIT WHEN q_tab%ROWCOUNT >= 3; END LOOP; CLOSE q_tab; EXCEPTION WHEN e_failure THEN NULL; END;
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 277
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
PL/SQL Functions
• Named functions to be called from any client or other procedure/function • Stored once and shared by clients • Can be used in anonymous or SELECT myfunc( stored code 'fred bloggs') CREATE OR REPLACE FUNCTION myfunc (p_name IN CHAR := NULL) RETURN VARCHAR2 AS w_name VARCHAR2(60); BEGIN IF p_name IS NULL THEN w_name := 'Unknown Name'; ELSE w_name := 'Name is ' || INITCAP(p_name); END IF; RETURN w_name; END; from dual;
MYFUNC('FREDBLOGGS') Name is Fred Bloggs
ODBA10gDB-OCA--10-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Functions A PL/SQL function is a stored code object that can utilise the full range of PL/SQL and SQL statements, except that stored functions are prohibited from modifying tables (or calling procedures that modify tables). Input parameters can also be specified for functions. A function can also return a single parameter (of a specified type) to the caller. Functions can be invoked as part of an assignment statement within another PL/SQL block or directly in a SQL statement. CREATE OR REPLACE FUNCTION myfunc (p_name IN CHAR := NULL) RETURN VARCHAR2 AS w_name VARCHAR2(60); BEGIN IF p_name IS NULL THEN w_name := 'Unknown Name'; ELSE w_name := 'Name is ' || INITCAP(p_name); END IF; RETURN w_name; END;
V2: Page 278
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
PL/SQL Packages
• Group of procedures and functions • Loaded into memory as a single unit • Header and body are separated to minimise impact of changes to code DECLARE w NUMBER; • Uses memory BEGIN ... more efficiently mypack.p1;
CREATE OR REPLACE PACKAGE mypack AS PROCEDURE p1; PROCEDURE p2; FUNCTION f1(input NUMBER) RETURN char; END;
CREATE OR REPLACE PACKAGE BODY mypack AS PROCEDURE p1 IS BEGIN NULL; END p1; PROCEDURE p2 IS BEGIN NULL; END p2; FUNCTION f1(input NUMBER) RETURN char IS BEGIN NULL; END f1; ... END mypack; END;
ODBA10gDB-OCA-10-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Packages Packages are created from groups of procedures and functions. A package has both a header (that defines the interface as a list of procedures and functions) and a body (that defines the executed code for each sub-object). By using packages, changes to the internal execution method that do not affect the interface (parameters or return types) will break the recompilation chain that would otherwise be necessary if code is altered as Oracle is running.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 279
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
PL/SQL at Runtime
• Resides in SYSTEM tablespace • Loaded into shared pool • Can be preloaded on db startup ODBA10gDB-OCA-10-10
SGA Shared Pool
Buffer Cache
Library Cache Dictionary Cache
Redo Log Buffer
DBWR
LGWR
1A Control file
Data file
1B
Redo Logs
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL at Runtime As a PL/SQL stored code routine is invoked, the code is loaded into the Shared Pool library cache and compiled into p-code for execution. Subject to available memory space, this code is retained in memory for the lifetime of the instance. Further calls to the same routines will therefore execute efficiently, since no further disk access is required. Packages can also be loaded into memory (using an Oracle supplied package ‘DBMS_SPOOL’) to ensure that sufficient memory is reserved for the package as Oracle is booted.
V2: Page 280
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
PL/SQL Triggers
• Code attached to event on table, view or database • Automatically runs when event occurs • Cascaded updates, auditing, complex INSERT INTO target VALUES (-1,'-1'); validation, data SELECT rowcount derivation FROM control; CREATE TABLE control ( tabname VARCHAR2(30), rowcount NUMBER);
CREATE OR REPLACE TRIGGER target_ins AFTER INSERT ON target BEGIN UPDATE control SET count = count + 1 WHERE control_key = 'TARGET'; END; / INSERT INTO control VALUES ('TARGET', 0);
ROWCOUNT 1 ODBA10gDB-OCA-10-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
PL/SQL Triggers Triggers are code routines that can be declared on table, view or database events. Trigger code is then automatically executed whenever the relevant event occurs. Triggers can be used for cascading updates, controlled denormalisation of the database contents, auditing of database operations, additional complex validation and data derivation for additional updates. Possible trigger events on tables include : BEFORE/AFTER INSERT/UPDATE/DELETE. Possible trigger events on views include INSTEAD OF. Possible
trigger
SHUTDOWN/LOGOFF
events on database instances include and AFTER SERVERERROR.
Copyright © 2007 Jeremy Russell & Associates Ltd.
AFTER
STARTUP/LOGON,
BEFORE
V2: Page 281
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
Supplied Packages
• Many PL/SQL packages are installed in the database to extend functionality • Supplied code can be used in any user applications • Described in PL/SQL Packages and Types Reference Manual
ODBA10gDB-OCA-10-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Supplied Packages Oracle is supplied with a number of packages for extending the database functionality. These packages are installed into new databases; some are always installed and others are installed subject to options selected during the database creation process. The Oracle Database PL/SQL Packages and Types Reference manual (part B14258-01 contains details of all supplied code. All stored code, whether Oracle or user-written, is available for viewing from the ‘DBA_SOURCE’ dictionary view.
SQL> DESC dba_source Name Null? ----------------------------------------- -------OWNER NAME TYPE LINE TEXT
V2: Page 282
Type ---------------------------VARCHAR2(30) VARCHAR2(30) VARCHAR2(12) NUMBER VARCHAR2(4000)
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
Supplied Packages Examples Package
Description
DBMS_ALERT
Async event notification
DBMS_DATAPUMP
Moves data and metadata between databases
DBMS_DDL
Allows DDL to run inside stored code
DBMS_FLASHBACK
Reset database to an earlier time
DBMS_JOB
Interface for job queue and scheduler
DBMS_LOCK
Interface for Oracle Lock Management Services
DBMS_LOGMNR
Interface to initialise and run log viewer tasks
DBMS_OUTPUT
Used for simple PL/SQL debugging
DBMS_RANDOM
Simple random number generator
DBMS_REPAIR
Data corruption repair procedures
DBMS_SPACE
Provides additional segment space information
ODBA10gDB-OCA-10-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Supplied Package Examples Package
Description
DBMS_ALERT
Async event notification
DBMS_DATAPUMP
Moves data and metadata between databases
DBMS_DDL
Allows DDL to run inside stored code
DBMS_FLASHBACK
Reset database to an earlier time
DBMS_JOB
Interface for job queue and scheduler
DBMS_LOCK
Interface for Oracle Lock Management Services
DBMS_LOGMNR
Interface to initialise and run log viewer tasks
DBMS_OUTPUT
Used for simple PL/SQL debugging
DBMS_RANDOM
Simple random number generator
DBMS_REPAIR
Data corruption repair procedures
DBMS_SPACE
Provides additional segment space information
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 283
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
EM Administration
• EM > Administration > Programs • Program type selected from list • Display, edit or recompile code
ODBA10gDB-OCA-10-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Administration Stored code objects can be administered using Enterprise Manager. Select Administration > Programs. The program type required can then be selected from either the list of links or the drop-down box displayed. The required object can be located in the owners schema. The source can be displayed, edited, recompiled or deleted, as well as other functions.
V2: Page 284
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 10 – PL/SQL for DBA’s
Oracle 10g Database Associate
Summary
• In this lesson, you have learned about: • PL/SQL architecture and block structure • Block types – anonymous blocks, procedures, functions, packages, triggers • PL/SQL at runtime • Oracle supplied packages
ODBA10gDB-OCA-10-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 285
Oracle 10g Database Associate
Lesson 10 – PL/SQL for DBA’s
Practice 10
• There are no practice exercises for this lesson • Use of relevant supplied packages will be discussed in later lessons in this course
ODBA10gDB-OCA--10-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 10 There are no practice exercises for this lesson. Use of relevant supplied packages will be discussed in later lessons in this course.
________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 286
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
11 0 Database Security
ODBA10gDB-OCA--11-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 11 – DATABASE SECURITY Lesson 11 introduces a variety of database wide security features.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 287
Oracle 10g Database Associate
Lesson 11 – Database Security
Objectives
• After completing this lesson, you will understand the following: – Principal of least privilege – Default user accounts – Standard password security features – Audit database activity – Register for security updates
ODBA10gDB-OCA--11-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 11 introducts miscellaneous database wide security items. Topics include: Principle of ‘least privilege’ Default user accounts created automatically by the DBCA An overview of standard password security features How to audit database activity Registering for security updates
V1: Page 288
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Defensive Database Security
• Most security breaches occur from within
"Know thyself and know thy enemy"
• It is never too early to think about security
"The security benefits of enforcing a
least privilege principle during application development will pay off " ODBA10gDB-OCA--11-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Defensive Database Security Sun Tzu was a Chinese philosopher and has been quoted widely in the context of security. References include such diverse sources as, sundry Internet Gaming forums1 and even a discourse on selling more pizza2. Perhaps the most apt quote is from a Computerworld article on company information security3. This articles lists four key principles for this purpose.
Know thy system Principle of least privilege Defense in depth Prevention is ideal, but detection is a must
An Oracle White Paper4 further emphasises the recommended approach to security from Oracle’s perspective, by saying “Security must be part of the planning process from day one of the application development process” and “The security benefits of enforcing a least privilege principle during application development will pay off”. Both Sun Tzu, Computerworld and Oracle recognise that most attacks on computer systems come from within, therefore guarding against internal intruders is a primary focus when thinking about database security.
1 2 3 4
http://www.gamearena.com.au/pc/messageboards/797-672/index.php?action=show&id=2379926 http://www.pmq.com/mag/2002spring/big_dave_ostrander.shtml http://www.computerworld.com/securitytopics/security/story/0,10801,82515,00.html?source=NLT_SEC2&nid=82515
http://www.oracle.com/technology/deploy/security/pdf/bwp_security_db_database_10gR2_0508.pdf
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 289
Oracle 10g Database Associate
Lesson 11 – Database Security
Principal of least privilege
• Ensure users can' t do more than necessary – Grant only required privileges – Revoke unnecessary privileges from PUBLIC – Grant roles only if all privileges are needed – Discussed in Lesson 12
• Restrict run-time permissions to JVM – Prevents unauthorised command execution – Requires in-depth Java programming skills
ODBA10gDB-OCA--11-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Principle Of Least Privilege Based on the Sun Tzu theory, the overriding security concern for a DBA should be to ensure that users are only able to perform the tasks which are necessary. Several controls are provided for the DBA in this context. A user account should only be granted relevant privileges. A ‘privilege’ entitles the user of the account to perform a specific system or object related tasks. System privileges are mostly statement based, whilst object privileges permit a explicit operation on a defined object (table, view etc).
V1: Page 290
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
PUBLIC A “PUBLIC” pseudo-account is created automatically in every database, for use as a holding area for common privileges. This account cannot be used directly to log in to an Oracle database. “PUBLIC” account permissions are inherited by all named user accounts automatically. Care should be taken to ensure that no unnecessary privileges are given to this potentially powerful account.
ROLES An Oracle “role” is a grouping of privileges, typically describing a job function. Privileges are assigned to the role and the role can be assigned to one or more user accounts. Whilst roles provide a useful facility for simplifying administrative tasks, careful planning of the use of roles is essential for security purposes. The DBA should have a clear understanding of the inherent privileges assigned to a role and only assign the role to a user account when all inherited privileges are relevant. Roles, privileges and user accounts are described in detail in Lesson 12.
Java Virtual Machine (JVM) From Oracle8i onwards, the Oracle database includes a runtime Java Virtual Machine (JVM) that can be used to execute stored code written in Java, alongside the proprietary PL/SQL facility. The Java language is far more extensive than PL/SQL and includes facilities for executing operating system commands with the OS privileges of the Oracle software owner. Whilst detailed knowledge of Java programming would be required to misuse this facility, there are many programmers with such knowledge working today. Access to the predefined Java roles should therefore be restricted to relevant staff. These roles include JAVADEBUGPRIV JAVAIDPRIV JAVASYSPRIV JAVAUSERPRIV JAVA_ADMIN JAVA_DEPLOY
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 291
Oracle 10g Database Associate
Lesson 11 – Database Security
Default user accounts
• Lock & expire default accounts • Change default user passwords – Administrative users : SYS, SYSTEM – Other users : SCOTT, DBSNMP, MDSYS …
• Enforce password management using profiles
ODBA10gDB-OCA--11-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Default User Accounts When creating a new database, many user accounts are added automatically. Depending on the options selected, these accounts may include any or all of the following (“Ch11UserStatus.sql”): USERNAME ANONYMOUS BI CTXSYS DBSNMP DIP DMSYS EXFSYS HR IX MDDATA MDSYS MGMT_VIEW OE OLAPSYS ORDPLUGINS
V1: Page 292
ACCOUNT_STATUS EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED OPEN EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED OPEN EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED
USERNAME ORDSYS OUTLN PM SCOTT SH SI_INFORMTN_SCHEMA SYS SYSMAN SYSTEM TSMSYS WMSYS XDB ORDSYS OUTLN
ACCOUNT_STATUS EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED OPEN EXPIRED & LOCKED EXPIRED & LOCKED OPEN OPEN OPEN EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED EXPIRED & LOCKED
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Although the majority of these accounts are locked by default, meaning that they cannot be used to login without DBA intervention, the ‘OPEN’ accounts may have permissions that would permit unauthorised activities and may have well known passwords. Default passwords should be changed always for the open accounts, in particular ‘SYS’, ‘SYSTEM’ and ‘SYSMAN’. Ongoing password management i.e. regular changes and validation for insecure passwords can be managed using database ‘profiles’, also discussed in Lesson 12.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 293
Oracle 10g Database Associate
Lesson 11 – Database Security
Standard password security features
• Enforce regular changes and verification using profiles • Restrict operating system access to administrative password file • Restrict operating system authentication
ODBA10gDB-OCA--11-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Standard Password Security Features Password security in the Oracle database should follow good standard practice as applied to other password functions in systems. Passwords should be changed on a regular basis and validated for easy to guess combinations. These features can be managed using Oracle profiles, discussed in a later lesson. The Oracle administrative password file should be guarded against unauthorised access and modification. This file, which resides in the ‘/database/’ directory and is called ‘PWD.ora’, contains usernames for accounts that are authorised to connect with ‘SYSDBA’ and ‘SYSOPER’ privileges. The current contents of this file can be examined by querying the dictionary table ‘V$PWFILE_USERS’. Operating system authentication allows authorised users to connect with ‘SYSDBA’ or ‘SYSOPER’ privileges without providing a further username and password combination. Whilst useful, this is a possible security risk if an account holder leaves an unattended workstation connected to the OS. If OS authentication is active, the following connect string can be used to connect to Oracle: CONNECT / AS SYSDBA
For Windows system, ensure that no OS users are members of the ORA_DBA group. Administrative Tools > Computer Management > Local Users and Groups to check.
Use
For UNIX/Linux systems, ensure that no OS users are members of the ORA_DBA group. Examine the ‘/etc/group’ file for more information.
V1: Page 294
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Audit database activity
• Use system triggers / autonomous txns • Fine-grained auditing – virtual private db • Be aware of system log files
ODBA10gDB-OCA--11-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Audit Database Activity In Oracle 10g, a wide range of auditing operations can be selected to track statements, activities against tables or columns and even privileged administrator activities. Audit information can be written to the database (‘dba_audit_trail’) or to an operating system The ‘AUDIT’ statement can be used to turn on required options, as illustrated opposite. A fine grained auditing facility can be used, via the ‘DBMS_FGA’ package, to create policies and run stored procedures that can audit database access and update to the row level. This is part of Oracle’s Virtual Private Database (VPD) mechanism. The system log files (‘alert.log’) may also contain relevant information that records significant events for auditing purposes.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 295
Oracle 10g Database Associate
Lesson 11 – Database Security
Auditing Example – Hacker Tracker SQL> SQL> ALTER ALTER SYSTEM SYSTEM SET SET AUDIT_TRAIL AUDIT_TRAIL == "DB" "DB" SCOPE SCOPE == SPFILE; SPFILE; SQL> SQL> STARTUP STARTUP FORCE; FORCE; SQL> SQL> AUDIT AUDIT CREATE CREATE SESSION; SESSION; SQL> SQL> CONNECT CONNECT scott/lion@v10g scott/lion@v10g ERROR: ERROR: ORA-01017: ORA-01017: invalid invalid username/password; username/password; logon logon denied denied SQL> SQL> CONNECT CONNECT system/oracle@v10g system/oracle@v10g SQL> SQL> SELECT SELECT count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY') count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY') 2> 2> FROM FROM dba_audit_trail dba_audit_trail 3> 3> 4> 4>
WHERE WHERE returncode<>0 returncode<>0 GROUP GROUP BY BY username,terminal,to_char(timestamp,'DD-MON-YYYY username,terminal,to_char(timestamp,'DD-MON-YYYY HH:MI'); HH:MI');
COUNT(*) COUNT(*) USERNAME USERNAME 11 SCOTT SCOTT
ODBA10gDB-OCA--11-8
TERMINAL TERMINAL TO_CHAR(TIMESTAMP TO_CHAR(TIMESTAMP JEREMYXP JEREMYXP 01-JUN-2007 01-JUN-2007 12:00 12:00
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Auditing Example This example sets the audit trail destination to the database audit log. When this parameter is changed, the database instance must be restarte d. The ‘AUDIT’ statement can then be used to select a variety of auditable actions. This example turns auditing on for connections (‘CREATE SESSION’ operations). Both successful and unsuccessful operations are audited. To select either of these sub-sets of operations individually, append the clause ‘WHENEVER [NOT] SUCCESSFUL’ to the audit statement. A login has then been attempted, but with an incorrect password. After reconnecting as a DBA account, the ‘dba_audit_trail’ view can then be queried to see the audited results. This query only shows unsuccessful login attempts (where the returncode is nonzero). (“Ch11AuditHacker.sql”)
V1: Page 296
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Network Security
• • • •
Implement sensible network security Use appropriate Oracle encryption Consider the use of hardware firewalls Restrict listener execution privileges & remove external procedure defaults • Always use listener passwords
ODBA10gDB-OCA--11-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Network Security In general, any computer connected to a network is inherently NOT secure. By implementing sensible network security, including the use of commercial firewall and virus checking software, many network risks are reduced. Oracle provides enhanced networking security features, including options for encrypting data during transmission. Further details are discussed in Lesson 13 – Networking. The Oracle Listener program, that permits the connection between a client process and the database instance, is a common point of attack against an Oracle network. Privileges for starting, stopping and administering/configuring the listener should be restricted. The default listener configuration file (‘listener.ora’) is installed with the facility to execute external code under the privileges of the Oracle user. This ‘external procedure’ facility should always be removed from the listener.ora file if the feature is not required for production use. The listener should be secured with a password to prevent unauthorised access.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 297
Oracle 10g Database Associate
Lesson 11 – Database Security
Register for security updates
• EM > Administration > Policies • Monitor policy violations for critical patch advisories and others • Register with Metalink
ODBA10gDB-OCA--11-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Register For Security Updates The Enterprise Manager suite is installed with a number of Oracle ‘policies’, good practice recommendations for efficient and secure database instance management. Included within the standard policies for every database are a series of ‘critical patch advisories’, allowing Oracle to warn the DBA when security holes are found and software has been issued to fix the problem. A partial listing of security policy violations is illustrated below. Further information on the problem, together with suggested actions for resolving detected issues, can be displayed by clicking the displayed ‘policy rule’.
Registering with the Oracle Metalink support system will ensure that all automatic alerts and advisories issued by Oracle are checked.
V1: Page 298
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Miscellaneous Password Facts
• Password encryption algorithm published: "Special Ops: Host and Network Security for Microsoft, Unix, and Oracle" (page 727) • Hashed passwords are stored in SYS.USER$, password files, export dumps • Cleartext passwords may be in logs, UNIX scripts, dump or trace files
ODBA10gDB-OCA--11-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Miscellaneous Password Facts
Password Cracking Oracle’s password encryption mechanism has been described and documented in a book, “Special Ops: Host and Network Security for Microsoft, UNIX, and Oracle” by Erik Pace Birkholz and Stuart McClure (ISBN 1931836698).
Passwords to Crack There may be encrypted passwords stored in the locations shown on the slide.
Passwords in Clear Some sites may have clear i.e. unencrypted passwords stored in log files, UNIX scripts (or Windows batch files) as well as some dump and trace files.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 299
Oracle 10g Database Associate
Lesson 11 – Database Security
Conclusions
• Security is mainly being aware of the potential risks … The The only only bullet-proof bullet-proof intrusion intrusion prevention prevention solution solution in in the the world world today today is is to to lock lock the the computer tt connect computer away, away, don' don' connect aa network network and tt give and don' don' give anyone anyone the the key. key. • ... so, in the real world, know where the holes might be and how to plug them! ODBA10gDB-OCA--11-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Conclusions This lesson outlined many of the security risks that can be avoided with sensible precautionary measures. Further details are provided in later lessons.
V1: Page 300
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 11 – Database Security
Oracle 10g Database Associate
Summary
• In this lesson, you should have learnt about: – Principal of least privilege – Default user accounts – Standard password security features – Auditing database activity – Registering for security updates – How to think proactively about security
ODBA10gDB-OCA--11-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 301
Oracle 10g Database Associate
Lesson 11 – Database Security
This page intentionally left blank
V1: Page 302
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
12 0 User Administration
ODBA10gDB-OCA-12-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 12 – USER ADMINISTRATION In Lesson 12, you will examine user account administration. Additional topics demonstrated include the use of profiles for resource management and an introduction to privileges and roles to manage user capabilities.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 303
Oracle 10g Database Associate
Lesson 12 – User Administration
Objectives
• After completing this lesson, you will understand the following: – The principles of user accounts – User management commands – Privileges and roles – The principles of resource management
ODBA10gDB-OCA-12-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives In Lesson 12, you will examine user account administration. Additional topics demonstrated include the use of profiles for resource management and an introduction to privileges and roles to manage user capabilities. Topics include: Principle of user accounts User management commands and EM operations Privileges and roles Resource management principles User accounts are defined to allow users to access the database instance. By logging in to the instance with the assigned account name / password, privileges and usable space restrictions are assigned to the logged in session. The DBA (or an assigned deputy) is responsible for creating, altering and dropping user accounts. Oracle allows system (statement) privileges and object privileges (on specific tables). The assigned privileges can be granted to an account individually or grouped into roles for easier management. An Oracle profile can be used to manage password and system resource limits. Password security is always enforced; system resource limits can be suspended by the DBA
V1: Page 304
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
User Accounts
• A user account includes: – Authentication mechanism – Tablespace settings • Default, Temporary, Quota
– Privileges • Direct, via a role
– Resource limits • Via a profile
– Schema ODBA10gDB-OCA-12-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
User Accounts Each user will have their own account and password, created and managed by the DBA. A user account includes: Authentication method
password, external (operating system)
Tablespace settings
a default and a temporary tablespace
Privileges
system and object level privileges on tables and columns
Resource limits
a means of enforcing password and system limitations
Schema
objects created by the logged in account
During database creation, Oracle creates several reserved account names: SYS
change_on_install
Owner of the base data dictionary tables
SYSTEM
manager
DBA account to be used for creating further user accounts
Depending on the Oracle version in use, other reserved accounts may be created – for a complete list of these accounts, see ‘Default User Accounts’ in Lesson 6.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 305
Oracle 10g Database Associate
Lesson 12 – User Administration
Profiles
• • • • •
Named set of resource limits DEFAULT profile is created automatically Assigned to individual users by the DBA Can inherit limits from DEFAULT profile Control – Password Resource Limits – Oracle Kernel Resource Limits
ODBA10gDB-OCA-12-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Profiles A profile is a named set of resource limits that are created independently and assigned to the user account name. Oracle is installed with a DEFAULT profile; this profile can be altered but cannot be dropped. The profile has all limit values set to UNLIMITED.
DEFAULT
Other profiles can ‘inherit’ values from the DEFAULT profile, simplifying the enforcement of selective global resource limits. If a limit is changed in the DEFAULT profile, the changed value ripples through to all inheriting profiles. The DBA assigns a named profile to one or more user accounts. If no explicit profile is assigned, the user account is automatically set to use the limits in the DEFAULT profile. Profiles control the password expiration settings, validation and reusability of passwords. Password control is always enforced by Oracle.. Profiles also optionally control kernel resources, including limiting the length of a SQL statement and/or the number of I/O operations carried, by session or by statement. Kernel resource profile processing is enabled by setting the dynamic parameter RESOURCE_LIMIT =
TRUE.
V1: Page 306
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
EM and Profiles
• EM > Administration > Users & Privileges > Profiles
ODBA10gDB-OCA-12-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM and Profiles To display and edit profile information using Enterprise Manager, select Administration > Users and Privileges > Profiles.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 307
Oracle 10g Database Associate
Lesson 12 – User Administration
Profile Password Limits
• • • • • • •
FAILED_LOGIN_ATTEMPTS PASSWORD_LOCK_TIME PASSWORD_LIFE_TIME PASSWORD_GRACE_TIME PASSWORD_REUSE_TIME PASSWORD_REUSE_MAX PASSWORD_VERIFY_FUNCTION
ODBA10gDB-OCA-12-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Profile Password Limits The following password resource limits are always enforced. Default values are UNLIMITED.
FAILED_LOGIN_ATTEMPTS
Number of times a user can attempt a login before the account is locked.
PASSWORD_LOCK_TIME
Number of days for which the account remains locked.
PASSWORD_LIFE_TIME
Number of days after which the password expires.
PASSWORD_GRACE_TIME
Number of days after which a warning message is issued on login. If the password is not changed in this period, it expires automatically.
PASSWORD_REUSE_TIME
Number of days before which a password cannot be reused.
PASSWORD_REUSE_MAX
Maximum number of times the same password can be reused.
PASSWORD_VERIFY_FUNCTION PL/SQL function (owned by the SYS schema) to validate usable passwords.
See the example script provided by Oracle in the file
$ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
V1: Page 308
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Profile Kernel Limits
• • • • • • • • •
CPU_PER_SESSION CPU_PER_CALL SESSIONS_PER_USER CONNECT_TIME IDLE_TIME LOGICAL_READS_PER_SESSION LOGICAL_READS_PER_CALL PRIVATE_SGA COMPOSITE_LIMIT
ODBA10gDB-OCA-12-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Profile Kernel Limits The following system resource limits are always enforced, if the dynamic parameter value for Default values are UNLIMITED.
RESOURCE_LIMIT = TRUE.
CPU_PER_SESSION
Maximum CPU time (hundredths of seconds) for the session.
CPU_PER_CALL
Maximum CPU time (hundredths of seconds) for a statement.
SESSIONS_PER_USER
Maximum simultaneous sessions allowed for this account.
CONNECT_TIME
Maximum connect time in minutes allowed for this account, after which the session is semi-disconnected.
IDLE_TIME
Maximum idle time in minutes, after which the session is semi-disconnected.
LOGICAL_READS_PER_SESSION Maximum number of data blocks (logical + physical) allowed to be read in the session.
LOGICAL_READS_PER_CALL
Maximum number of data blocks (logical + physical) allowed to be read by a single statement.
PRIVATE_SGA
For multi-threaded server connections, maximum SGA private space allocation.
COMPOSITE_LIMIT
Specify the total resource cost for a session, expressed in service units, calculated as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 309
Oracle 10g Database Associate
Lesson 12 – User Administration
Profile Management • CREATE PROFILE … ALTER PROFILE default LIMIT CONNECT_TIME 60; CREATE PROFILE novice LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME UNLIMITED IDLE_TIME 15 CONNECT_TIME DEFAULT LOGICAL_READS_PER_CALL 1000; • ALTER USER fred PROFILE novice; ODBA10gDB-OCA-12-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Profile Management Named profiles can be created with all or selected password or system resource limits. For each limit, the profile can be set with: A specific value (e.g. FAILED_LOGIN_ATTEMPTS above). An UNLIMITED value (e.g. PASSWORD_LIFE_TIME above). A reference to the DEFAULT profile (e.g. CONNECT_TIME above). Profiles are assigned to users with the CREATE USER or ALTER USER commands. A profile can be dropped – at any time – with the DROP PROFILE command. Any users who were assigned with that profile revert to the settings in the DEFAULT profile with immediate effect (“Ch12CreateProfile.sql”).
V1: Page 310
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Authentication Mechanisms
• Password File – SYSDBA – SYSOPER – V$PWFILE_USERS
• Operating System – /etc/group – User Manager for Windows
• Database – DBA_USERS ODBA10gDB-OCA-12-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Authentication Mechanisms User sessions are authenticated (logged) into the database using one of three methods. For privileged (DBA) users, password file or operating system authentication is used to start or stop the database. For password file authentication, a password file must exist and the parameter file entry ‘REMOTE_LOGIN_PASSWORDFILE’ must be set to EXCLUSIVE. Connection to the database for password file privileged users is made using the command:
SQL> CONNECT username/password AS SYSDBA
For operating system privileged authentication, the OS account name must be registered in ‘/etc/group’ or in User Manager for Windows, against the Oracle software installation group.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 311
Oracle 10g Database Associate
Lesson 12 – User Administration
Connection to the database for operating system privileged users is made using the command:
SQL> CONNECT / AS SYSDBA
This example requires an account name of OPS$username to be created. For example, to use OS authentication for the root account, create an Oracle account called OPS$ROOT. For regular users, connection is made using the command:
SQL> CONNECT username/password or SQL>CONNECT /
V1: Page 312
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Tablespace Settings
• Default – Assigned to user for object creation
• Temporary – Assigned to user for temporary use
• Quota – Space to be used for owned objects – No quota needed on temporary tablespaces
ODBA10gDB-OCA-12-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Tablespace Settings Each user account has a default tablespace assigned, where objects (tables, indexes etc) are built if a specific tablespace is not identified on the creation command. If an account is created without a default tablespace, the account defaults to SYSTEM. If the account has no quota on SYSTEM, no tables can be created. A temporary tablespace should also be assigned for each account, to be used for disk storage by selected statement that exceed the space available in RAM. A quota – an amount of disk storage – should also be assigned for the default and any other tablespaces used by the user. This space is used for objects owned by the user, irrespective of which account adds the information to the object. No quota is needed for temporary tablespaces.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 313
Oracle 10g Database Associate
Lesson 12 – User Administration
Privileges
• System Privileges – Statement execution privilege – e.g. GRANT CREATE [ ANY ] TABLE TO fred;
• Object Privileges – e.g.
ODBA10gDB-OCA-12-11
GRANT SELECT ON EMP TO fred; GRANT ALL ON dept TO public;
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Privileges System privileges Oracle provides over 100 system level privileges that can be assigned. System privileges refer to statement level privileges, that permit or ban the execution of the relevant statement. Many of the system privileges have two versions, with and without the ANY keyword. A privilege given with an ANY keyword allows the account to execute the statement for all other users as well as in their own account. Accounts are created with NO privileges and must be granted even the CREATE SESSION privilege to allow them to log in to Oracle.
SQL> GRANT CREATE TABLE, CREATE INDEX TO fred, bill; SQL> REVOKE CREATE INDEX FROM fred;
V1: Page 314
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Object privileges
An object privilege provides the ability to perform an action on a specified object (table or column).
Objects when created are private to the owner of the object, unless and until permission is given to other accounts or to the reserved role PUBLIC.
Privileges can also be granted on selected columns:
SQL> GRANT SELECT(code, name, salary), UPDATE(name) 2 ON emp 3 TO fred; SQL> REVOKE SELECT(salary) ON emp FROM fred;
Privilege Management Privileges are given to accounts with a GRANT statement and removed with a REVOKE statement.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 315
Oracle 10g Database Associate
Lesson 12 – User Administration
Roles
• CREATE ROLE accounts_user; • GRANT ALL ON customers TO accounts_user; • GRANT accounts_user TO fred; • ALTER ROLE accounts_user IDENTIFIED BY secretPassword; • DROP ROLE accounts_user;
ODBA10gDB-OCA-12-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Roles A role is a grouping of system and object privileges that can be granted, as a group, to a user or to another role. The use of roles is strongly recommended both for easier management and for performance reasons. Roles can be created, altered and dropped. Default roles can be assigned to an account at login. Additional roles can be requested by the account on demand. Passwords may be required to enable selected roles.
V1: Page 316
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
On database creation, several reserved roles are defined automatically:
DBA
Allows full access to all account tables and other objects for management.
RESOURCE
Allows creation of objects in specified account and limited management of other account objects (e.g. creating indexes for other accounts); includes CONNECT privilege.
CONNECT
Allows management of objects in the relevant account.
“CONNECT” and “RESOURCE” are provided for V6 and V7 backward compatibility and should not be used for new production accounts.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 317
Oracle 10g Database Associate
Lesson 12 – User Administration
Creating a new user
• CREATE USER auser IDENTIFIED BY apassword DEFAULT TABLESPACE users QUOTA 2M ON users QUOTA 2M ON example TEMPORARY TABLEPACE temp PROFILE novice PASSWORD EXPIRE;
ODBA10gDB-OCA-12-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Creating a new user The example creates a new user ‘auser’ with a password of ‘apassword’. The default tablespace is ‘users’ and objects can be created in ‘users’ and ‘example’. Any temporary space requirements will be created in ‘temp’. The ‘novice’ profile is assigned to limit any resource requirements for this account. Note that the password is set to ‘EXPIRE’ – this will force the account password to be changed on the next (first) login (“CreateUser.sql”)
V1: Page 318
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
EM and Users
• EM > Administration > Users & Privileges > Users
ODBA10gDB-OCA-12-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM and Users To display a list of current database users in EM, select Administration > Users and Privileges > Users. For each user account, a summary of the dictionary information is displayed. To display a full screen of information about the account, click the required user name. To edit a user account, select the radio button next to the required user name and click the Edit button. Several sub-screens can then be selected, including General
Passwords, profile and tablespace information
Roles
Assigned database roles
System Privileges
Assigned system / statement privileges
Object Privileges
Assigned object privileges
Quotas
Tablespace quota information
Consumer Groups Switching Privileges
Resource plans are used by Oracle to schedule use of Oracle CPU time and other features and are discussed in a later lesson.
Proxy Users
Property sheet describing user proxy accounts – not discussed further in this course
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 319
Oracle 10g Database Associate
Lesson 12 – User Administration
User Management
• ALTER USER auser QUOTA 0 ON users; • ALTER USER auser IDENTIFIED BY newpassword PASSWORD EXPIRE; • DROP USER auser [CASCADE]; ODBA10gDB-OCA-12-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
User Management Any of the account settings can be changed by the DBA. The user is limited to changing their own password only. If a quota is set to 0 (as above) on a specified tablespace, the space already assigned to that user’s objects is retained and rows may be deleted and re-inserted. However, no further space can be claimed by that account for objects currently owned by the account. If a user forgets their password, the DBA can reset it as above. The password is encrypted using a one way encryption algorithm in the data dictionary and cannot be converted back to clear text. A user can be dropped, if they do not own any objects. A user with objects can be dropped using the keyword ‘CASCADE’. Care should be taken in the use of this option.
V1: Page 320
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
EM and Roles
• EM > Administration > Users & Privileges > Roles
ODBA10gDB-OCA-12-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM and Roles To display the roles present in the database, select Administration > Users and Privileges > Roles. A paginated list of existing roles is displayed. To display full details regarding a role, click the role name. A variety of information is shown, including authentication information, roles assigned to this role, system and object privileges and consumer group switching information.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 321
Oracle 10g Database Associate
Lesson 12 – User Administration
Roles and Users
• CREATE ROLE accounts_user; • GRANT ALL ON customers, orders TO accounts_user WITH GRANT OPTION; • GRANT accounts_user TO fred; • ALTER ROLE accounts_user IDENTIFIED BY secretPassword; • DROP ROLE accounts_user; ODBA10gDB-OCA-12-17
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Roles and Users Roles and privileges can be granted to users and to other roles. The WITH ADMIN OPTION can be applied to system privileges to allow the grantee (recipient) to pass the privilege on to other accounts. The WITH GRANT OPTION can be applied to object privileges to allow the grantee to pass the privilege on to other accounts.
V1: Page 322
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Data Dictionary
• • • • • • •
DBA_PROFILES DBA_USERS DBA_TS_QUOTAS DBA_SYS_PRIVS DBA_TAB_PRIVS DBA_COL_PRIVS DBA_ROLES
ODBA10gDB-OCA-12-18
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Data Dictionary
DBA_PROFILES Name ----------------------------------------PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
Null? -------NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(32) VARCHAR2(8) VARCHAR2(40)
Null? -------NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) NUMBER VARCHAR2(30) VARCHAR2(32) DATE DATE VARCHAR2(30) VARCHAR2(30) DATE VARCHAR2(30) VARCHAR2(30) VARCHAR2(4000)
DBA_USERS Name ----------------------------------------USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME
Copyright © 2007 Jeremy Russell & Associates Ltd.
NOT NULL NOT NOT NOT NOT
NULL NULL NULL NULL
V2: Page 323
Oracle 10g Database Associate
Lesson 12 – User Administration
DBA_TS_QUOTAS Name ----------------------------------------TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
Null? -------NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(30) NUMBER NUMBER NUMBER NUMBER VARCHAR2(3)
Null? -------NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(40) VARCHAR2(3)
Null? -------NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(40) VARCHAR2(3) VARCHAR2(3)
Null? -------NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(40) VARCHAR2(3)
Name Null? ----------------------------------------- -------ROLE NOT NULL PASSWORD_REQUIRED
Type ---------------------------VARCHAR2(30) VARCHAR2(8)
DBA_SYS_PRIVS Name ----------------------------------------GRANTEE PRIVILEGE ADMIN_OPTION
DBA_TAB_PRIVS Name ----------------------------------------GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
DBA_COL_PRIVS Name ----------------------------------------GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRANTABLE
DBA_ROLES
V1: Page 324
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Summary
• In this lesson, you should have learnt about: – The principles of user accounts – User management commands – Privileges and roles – The principles of resource management
ODBA10gDB-OCA-12-19
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 325
Oracle 10g Database Associate
Lesson 12 – User Administration
Practice 12 • Using EM, create a new user account – use your own name and any password • For the new account, assign 2MB quotas on tablespaces USERS and EXAMPLE • Create a new profile with a maximum of 2 concurrent sessions; assign to your new account • With SQL*Plus, try to login three times to the new account • Configure your new account to allow access to the SCOTT.emp table ODBA10gDB-OCA--12-20
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 12 In this practice, you will create and test a new user account. Use EM to create a new user account with your own name and any password of your choice Assign the ‘CREATE SESSION’ privilege to the new user account. (“Ch12CreateUser.sql”).
________________________________________________________________________ ________________________________________________________________________ For this new account, (“Ch12CreateUser.sql”).
assign
2MB
quotas
on
tablespaces
USERS
and
EXAMPLE
________________________________________________________________________ ________________________________________________________________________ Create a new profile with a maximum of two concurrent sessions and assign this to your new account. Ensure that the limit will be applied. (“Ch12CreateProfile.sql”).
________________________________________________________________________ ________________________________________________________________________
V1: Page 326
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 12 – User Administration
Oracle 10g Database Associate
Use SQL*Plus to try and connect three times to your new account (“Ch12Connect.sql”).
________________________________________________________________________ ________________________________________________________________________ Configure your new account to be able to access the SCOTT.EMP table (“Ch12AddPrivileges.sql”).
________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 327
Oracle 10g Database Associate
Lesson 12 – User Administration
This page intentionally left blank
V1: Page 328
Copyright © 2005 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
13 0 Network Administration
ODBA10gDB-OCA-13-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 13 – NETWORK ADMINISTRATION Lesson 13 outlines the Oracle Net Services administration tools and utilities
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 329
Oracle 10g Database Associate
Lesson 13 – Network Administration
Objectives
• After completing this lesson, you will be able to do the following: – Understand the Oracle networking system – Configure Net Service aliases – Use Listener features – Configure client connections with Net Manager – Test connections with the TNSPING utility – Describe name resolution methods
ODBA10gDB-OCA-13-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 13 outlines the Oracle Net Services administration tools and utilities. Topics include: An overview of the Oracle networking system Configuring Net Services aliases Using and administering listener features Configuring client connections with Net Manager Testing connections with the TNSPING utility
V2: Page 330
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Client Server Architecture
• Client process calls listener with login and connect string • Listener generates server process • Client calls server with SQL statements • Server processes SQL and returns data
SGA Shared Pool
Buffer Pool
Java Pool Log Buffer
Large Pool
Processes DBWR
DBWR
DBWR
PMON
CKPT
ARCH
LISTENER
SERVER
Instance
System
Sysaux
Parameter
Users
Control
Undo
Example
Redo Logs
Database ODBA10gDB-OCA-13-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Client Server Architecture Most Oracle database systems will use some form of “client/server” architecture. A client process, often running on a PC or workstation, accepts operator commands and passes them (in the form of SQL) to a server (shadow) process. The server process always runs on the database host. The intervening communication methodology forms one part of the Net Services architecture. Oracle Net also uses other processes (listener, dispatchers and more) to facilitate the establishment of a connection.
Connection Alternatives Depending on the networking and database instance configuration, shadow processes may be prespawned to ensure that the client process achieves the fastest possible connection to the database. The overhead of starting up a new OS shadow process to service the client is minimised. The number of pre-spawned processes can also be managed by the DBA (Network Administrator), providing further tuning opportunities.
Connect String The client process connects to the database service required, by specifying a “connect string” component of the initial connection command. For example: sqlplus system/oracle@dbService
In this example, “system” is the user account name, “oracle” is the account password and “dbservice” – the connect string – is the name of the database service (or host) to receive the connection.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 331
Oracle 10g Database Associate
Lesson 13 – Network Administration
Client/Server Connection Options
• Oracle supports a range of connection methods
Database Host
1
User
2
User
3
Browser
Direct
SERVER
Network
Internet
SERVER
User
SERVER
Application Server ODBA10gDB-OCA-13-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Client/Server Connection Options 1. A direct connection, typical of a UNIX system with local “dumb” terminals No client side configuration is required and databases are located using the host network name. This configuration only supports a single database on the host and is intended for use in smaller environments.
2. Client / server connections, with PC’s for presentation and a high capacity, high performance database server to manage data retrieval and updating. Multiple database instances can be supported if required on the database server. Target connections are defined in configuration files resident on each client (“TNSNAMES.ORA”). Distributed databases (DBMS to DBMS communication) can also be configured using this method i.e. each database acts as a pseudo-client for the other databases in the network.
3. Internet / Intranet applications, using an intermediate application server as a concentrator and HTML pre-processor. This level of application may use several machines to act as application servers and communication concentrators to the network. Regionalised setups are possible, allowing many flexible opportunities for load management and configuration. Oracle Application Server is not covered in this course.
V2: Page 332
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Oracle Net Components
• Name resolution methods – Host naming / EZConnect – Local naming – Centralised names
• Multiple connection methods – Two task – Shared server – Connection Manager
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA-13-5
Oracle Net Components
Name Resolution Methods Host naming
Simplest level of database identification. The connection string is a network host name; the named host can only support a single networked database instance.
Local naming
Local naming requires a TNSNAMES file to reside on each client. If the network configuration changes, all the clients that require access to the new resource must be updated (or have access to a shared TNSNAMES file).
Centralised names
Centralised naming can use an LDAP server to store connection information (not discussed in this class).
Connection Methods Two-task
Common client server connection method. Server (shadow) runs on the database host (which can be the same processor as the client). See page 361 in this lesson.
Multi-threaded server
Shared, pre-spawned servers are generated when the instance starts. An additional dispatcher processor (or multiple dispatchers) also started by the instance will route requests to a free server and queue return results for the client.
Connection Manager
An intermediate network node that routes requests from clients to the relevant database server. Connection Manager is also capable of protocol conversion and simple network access controls.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 333
Oracle 10g Database Associate
Lesson 13 – Network Administration
Name Resolution : Host naming/EZCONNECT sqlplus usr/pwd@host
sqlplus usr/pwd@host/SID
O r a c l e
LISTENER
SERVER
N e t ODBA10gDB-OCA-13-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Name Resolution : Host Naming/EZCONNECT
Host Naming The host naming method is the simplest means of connecting remotely to a database. Operating in a TCP/IP environment only, host naming is configured using existing name resolution services, including. NIS
Network Information Services; Sun Microsystems Yellow Pages protocol
DNS
Domain Name System; uses a central DNS server
Hosts
A local ‘/etc/hosts’ file
The connect string is the network name of the database host. The host can accept connections for a single database instance. The host’s “LISTENER.ORA” file must be configured as illustrated on pages 348 to 350.
EZCONNECT The EZCONNECT naming method was introduced with Oracle 10g. This method can only be used with 10g databases, whereas other naming methods can be used with earlier database releases. An EZCONNECT connect string specified in full is: CONNECT username/password@host[:port][/service_name][/instance_name]
For ‘typical’ Oracle installs, the connect string below can be used with default port values: CONNECT username/password@host/ORCL
V2: Page 334
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Net Manager Host Configuration
ODBA10gDB-OCA-13-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Net Manager Host Configuration Host naming can be set for a client using the Net Manager utility. To start Net Manager: On UNIX, execute $ORACLE_HOME/bin/netmgr On Windows, use Start > Programs > Oracle Home > Configuration and Migration Tools > Net Manager From the initial Net Manager display: Select the "Profile" option from the left hand menu tree. Select the "Naming" option from the right hand drop down. From the "Available Methods:" list, highlight the HOSTNAME entry and click the right arrow key illustrated below, to move the highlighted entry to the "Selected Methods:" box on the right of the panel. Where multiple entries are moved to the "Selected Methods:" box, the sequence of the selections indicates the priority, with entries at the top of the list being examined first. This sequence can be altered by using the "Promote" and "Demote" buttons after highlighting the entry to move in the "Selected Methods:" box.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 335
Oracle 10g Database Associate
Lesson 13 – Network Administration
Name Resolution : Local Naming sqlplus usr/pwd@host
tnsnames.ora prod: db1, host1
ODBA10gDB-OCA-13-8
O r a c l e
LISTENER
SERVER
N e t
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Name Resolution : Local Naming The local naming convention uses a ‘TNSNAMES.ORA’ file located on each client in the network. The TNSNAMES.ORA configuration file resides in ‘/network/admin’. This location can be overridden using the TNS_ADMIN environment variable. For each database, an entry is created that identifies: the local name for the database the database server network name, protocol and protocol specific parameters the instance name on the host. More details on TNSNAMES.ORA can be found on page 347.
V2: Page 336
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Net Manager Local Configuration
ODBA10gDB-OCA-13-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Net Manager Local Configuration To configure ‘SQLNET.ORA’ for local naming, using Net Manager, ensure that the ‘TNSNAMES’ method is moved to the "Selected Methods:" list. Promote the ‘TNSNAMES’ method to become the first entry in the list (as illustrated above) to ensure that the local naming resolution method is considered first.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 337
Oracle 10g Database Associate
Lesson 13 – Network Administration
Net Manager Service Name Configuration
ODBA10gDB-OCA-13-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Net Manager Service Name Configuration The ‘TNSNAMES.ORA’ on the client must also be configured to enter the service name and contact information. Using Net Manager, select the "Service Naming" option in the left panel menu. To add a new service to the local "tnsnames.ora" file, click the green + symbol in the left hand toolbar to open the wizard. Each screen displayed by the configuration wizard is illustrated on pages 339 to 343.
V2: Page 338
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Adding a new service – Net service name
ODBA10gDB-OCA-13-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Adding a new service – Net service name Step 1 of the wizard requests the Net service name to be used as a connection string to reach the database. Click Next to continue to the next screen.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 339
Oracle 10g Database Associate
Lesson 13 – Network Administration
Adding a new service - protocol
ODBA10gDB-OCA-13-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Adding a new service – protocol Step 2 of the wizard lists all available protocols. Select the required protocol from the list. Click Next to continue to the next screen
V2: Page 340
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Adding a new service – protocol settings
ODBA10gDB-OCA-13-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Adding a new service – protocol settings Step 3 of the wizard requests protocol specific settings. For TCP/IP, enter the host name and listener port number. The default listener port number is 1521. For multiple listeners, additional port numbers (1522, 1523 etc) can be used. For Named Pipe connections, a computer name and pipe name is requested. The default pipe name is 'ORAPIPE' and this is normally left at this value. For IPC connections, an IPC Key Value is requested. The default key value of ORCL can be overtyped if required. Click Next to continue to the next screen.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 341
Oracle 10g Database Associate
Lesson 13 – Network Administration
Adding a new service – DB service name
ODBA10gDB-OCA-13-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Adding a new service – DB service name Step 4 of the wizard requests the service name or system identifier (SID) for the database service. The entered value will depend on the version of the database installed (e.g 8i or later, or version 8). The connection type to be used for this service is also specified, from the following choices Database default
Use a shared connection if available, else a shared connection.
Shared Server
Always use a shared connection
Dedicated Server
Always use a dedicated connection
The different methods are described shortly; leave the value as "Database Default" for most connections. Click Next to continue to the next screen.
V2: Page 342
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Adding a new service – connection test
ODBA10gDB-OCA-13-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Adding a new service – Connection Test Step 5 of the wizard allows the newly added service to be tested. The initial test is carried out using the Oracle demonstration account (user = ‘scott’, Password = ‘tiger’). Click the "Test" button to carry out a test connection – the message "The connection test was successful" should be displayed. If this account is not setup, click the "Change Login" button to display a dialog to enter an alternative user name and password to use for the test.
Click OK to close this dialog and use the "Test" button to repeat the test with the new account details.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 343
Oracle 10g Database Associate
Lesson 13 – Network Administration
Configuration Files : SQLNET.ORA
• • • •
Resides on client and server Contains network setup information Optional diagnostic parameters Optional Oracle*Names information
ODBA10gDB-OCA-13-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Configuration Files : SQLNET.ORA To use Oracle Net to connect clients (or servers) to database hosts, each system in the network should have a ‘SQLNET.ORA’ file. This file configures the network connection methods for the machine and controls logging and tracing of Oracle network operations. Diagnostic parameters can also be set in the SQLNET.ORA file. These include both logging and tracing options. Logging records error information to a log file. Tracing records regular operational steps to a trace file.
V2: Page 344
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
SQLNET.ORA Example ## ## ##
sqlnet.ora sqlnet.ora Network Network Configuration Configuration File: File: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Generated Generated by by Oracle Oracle configuration configuration tools. tools.
## ## ## ##
This This file file is is actually actually generated generated by by netca. netca. But But if if customers customers choose choose to to install install "Software "Software Only", Only", this this file file wont exist and without the native authentication, they wont exist and without the native authentication, they will will not not be be able able to to connect connect to to the the database database on on NT. NT.
SQLNET.AUTHENTICATION_SERVICES= SQLNET.AUTHENTICATION_SERVICES= (NTS) (NTS) NAMES.DIRECTORY_PATH= NAMES.DIRECTORY_PATH= (TNSNAMES, (TNSNAMES, EZCONNECT) EZCONNECT)
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA-13-17
SQLNET.ORA Example
# sqlnet.ora Network Configuration File: # C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora # Generated by Oracle configuration tools. # This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT. SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH
= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_CLIENT TRACE_UNIQUE_CLIENT TRACE_FILE_CLIENT TRACE_DIRECTORY_CLIENT
= = = =
LOG_FILE_CLIENT LOG_DIRECTORY_CLIENT
= sqlnet.log = /oracle/log
# Log file name (default) # Log file directory
NAMES.DIRECTORY_PATH
= (TNSNAMES)
# Naming method(s) : ONAMES, HOSTNAME
OFF ON nettrace /oracle/trace
Copyright © 2007 Jeremy Russell & Associates Ltd.
# # # #
Levels : USER < ADMIN < SUPPORT Generate unique trace file names Client side trace file prefix Client side trace file directory
V2: Page 345
Oracle 10g Database Associate
Lesson 13 – Network Administration
Configuration Files : TNSNAMES.ORA
• Resides on client systems • Identifies network services – Host name – Instance identifier
• Can also reside on distributed servers
ODBA10gDB-OCA-13-18
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Configuration Files : TNSNAMES.ORA The TNSNAMES file identifies the services that can be used from the client. For each service, the following information must be provided: Service name and domain name Description Address Protocol Host – protocol specific information for connection to the host Port for TCP/IP connections, to identify the listener port to be used (defaults to 1521). Service name/SID – the database identifier to receive connections See the sample file on page347. .
V2: Page 346
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
TNSNAMES.ORA Example V10G V10G == (DESCRIPTION (DESCRIPTION == (ADDRESS (ADDRESS == (PROTOCOL (PROTOCOL == TCP)(HOST TCP)(HOST == JEREMYXP)(PORT JEREMYXP)(PORT == 1521)) 1521)) (CONNECT_DATA (CONNECT_DATA == (SERVER (SERVER == DEDICATED) DEDICATED) (SERVICE_NAME (SERVICE_NAME == v10g) v10g) )) )) EXTPROC_CONNECTION_DATA EXTPROC_CONNECTION_DATA == (DESCRIPTION (DESCRIPTION == (ADDRESS_LIST (ADDRESS_LIST == (ADDRESS (ADDRESS == (PROTOCOL (PROTOCOL == IPC)(KEY IPC)(KEY == EXTPROC1)) EXTPROC1)) )) (CONNECT_DATA (CONNECT_DATA == (SID (SID == PLSExtProc) PLSExtProc) (PRESENTATION (PRESENTATION == RO) RO) )) ))
ODBA10gDB-OCA-13-19
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
TNSNAMES.ORA Example
# tnsnames.ora Network Configuration File: # C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools. V10G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JEREMYXP)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = v10g) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 347
Oracle 10g Database Associate
Lesson 13 – Network Administration
Listener
• • • • •
Process started separately on server Waits for incoming connection requests Spawns server (shadow) process Address of shadow returned to client Client communicates with shadow
ODBA10gDB-OCA-13-20
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Listener The listener process runs on the database host. The process is started and managed by the listener control utility (LSNRCTL). Several operational methods can be employed by the listener to establish a client/server connection.
Bequeath session The listener waits for incoming connection requests on the configured network protocol (and port). When a request is received, the listener will spawn a server (shadow) process. The session connection information is bequeathed to the newly spawned server process. Further communication between the client and the database instance does not require the listener to be active.
Redirect session (dedicated) As the listener starts, a number of shadow processes are spawned (as configured in the LISTENER.ORA configuration file). The listener waits for incoming connection requests on the configured network protocol (and port). When a request is received, the listener redirects the session to one of the existing server processes. Further communication between the client and the database instance does not require the listener to be active. Additional resources are required to create and maintain the pre-spawned servers but connection time improves by removing the overhead of starting a new process for each connection.
Redirect session (dispatcher) For a multi-threaded server environment, a dispatcher process (started by the database instance) receives the incoming request from the listener. The least loaded dispatcher is selected automatically. More details regarding the multi-threaded server environment are discussed later in this lesson.
V2: Page 348
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Configuration Files : LISTENER.ORA
• Located on database host • Controls listener configuration • Specifies databases to accept incoming connections
ODBA10gDB-OCA-13-21
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Configuration Files : LISTENER.ORA The ‘LISTENER.ORA’ file configures the listener on a database host. The following information must be specified: Listener name
Defaults to LISTENER.
Listener address
The list of addresses (protocols, host names and ports) that are using this listener. For IPC addresses, client processes run on the same host server. The KEY name is the database service or SID name. For TCP addresses, the host name and port number (default 1521) must match the client’s TNSNAMES.ORA settings.
Databases using the listener
The SID_LIST_LISTENER block defines the databases for which requests are accepted by this listener. For each database, the corresponding ORACLE_HOME directory and the System Identifier (SID) of the database must be specified.
Additional parameters
See page 370 for more details of logging and tracing for the server.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 349
Oracle 10g Database Associate
Lesson 13 – Network Administration
LISTENER.ORA Example SID_LIST_LISTENER SID_LIST_LISTENER == (SID_LIST (SID_LIST == (SID_DESC (SID_DESC == (SID_NAME (SID_NAME == PLSExtProc) PLSExtProc) (ORACLE_HOME (ORACLE_HOME == C:\oracle\product\10.2.0\db_1) C:\oracle\product\10.2.0\db_1) (PROGRAM (PROGRAM == extproc) extproc) )) )) LISTENER LISTENER == (DESCRIPTION_LIST (DESCRIPTION_LIST == (DESCRIPTION (DESCRIPTION == (ADDRESS (ADDRESS == (PROTOCOL (PROTOCOL == IPC)(KEY IPC)(KEY == EXTPROC1)) EXTPROC1)) (ADDRESS (ADDRESS == (PROTOCOL (PROTOCOL == TCP)(HOST TCP)(HOST == JEREMYXP)(PORT JEREMYXP)(PORT == 1521)) 1521)) )) ))
ODBA10gDB-OCA-13-22
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LISTENER.ORA Example
# listener.ora Network Configuration File: # C:\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 = C:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = JEREMYXP)(PORT = 1521)) ) ) TRACE_LEVEL_LISTENER = OFF TRACE DIRECTORY_LISTENER = /oracle/trace TRACEFILE_LISTENER = listener.trc
V2: Page 350
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Listener Control Utility
• • • •
LSNRCTL command line utility Control program for listener service Starts and stops the listener process Other commands – SET trc_level – SET trc_file
• Actions are logged in LISTENER.LOG
ODBA10gDB-OCA-13-23
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Listener Control Utility The listener program is started, managed and stopped using the listener control utility, LSNRCTL. The utility operates either interactively or uses command line parameters.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 351
Oracle 10g Database Associate
Lesson 13 – Network Administration
Starting the listener The ‘START’ command will start the named listener (LISTENER by default). This example shows the command line mode of lsnrctl. $ lsnrctl start LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-MAY-2007 11:50:32 Copyright (c) 1991, 2005, Oracle.
All rights reserved.
Starting tnslsnr: please wait... TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JEREMYXP)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER -----------------------Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 Production Start Date 21-MAY-2007 11:50:35 Uptime 0 days 0 hr. 0 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JEREMYXP)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully $ _
V2: Page 352
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Stopping the listener The ‘Stop’ command will start the named listener (LISTENER by default). This example shows the interactive mode of LSNRCTL. $ lsnrctl LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-MAY-2007 11:50:32 Copyright (c) 1991, 2005, Oracle.
All rights reserved.
Welcome to LSNRCTL, type "help" for information. LSNRCTL> stop listener Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))) The command completed successfully LSNRCTL> exit $ _
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 353
Oracle 10g Database Associate
Lesson 13 – Network Administration
EM Listener Status
ODBA10gDB-OCA-13-24
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Listener Status The listener status is displayed on the EM Home page. The listener name can be clicked to display further information regarding the listener status. Further information includes log locations, connection information (net address) and start time. The ‘Edit’ button allows sundry listener parameters to be edited as described on the following slides.
V2: Page 354
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
EM Edit Listener : General
ODBA10gDB-OCA-13-25
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Edit Listener The ‘Edit’ button on the listener status screen allows editing of several categories of listener parameters. Host credentials are required to be entered for this task. These can also be saved for future use by clicking the ‘Save as Preferred Credential’ checkbox. General listener parameters that can be edited include protocol specific information as described on page 349 in ‘LISTENER.ORA’.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 355
Oracle 10g Database Associate
Lesson 13 – Network Administration
EM Edit Listener : Authentication
ODBA10gDB-OCA-13-26
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Authentication The Authentication allows the specification of a password to control listener operations.
V2: Page 356
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
EM Edit Listener : Logging and Tracing
ODBA10gDB-OCA-13-27
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Logging and Tracing The EM Logging and Tracing page allows specification of logging status and destination (for gathering network usage statistics). Tracing locations and levels can also be entered. Tracing levels are discussed later in this lesson (see page 365).
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 357
Oracle 10g Database Associate
Lesson 13 – Network Administration
EM Edit Listener : Static Registration
ODBA10gDB-OCA-13-28
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Static Registration From Oracle8i onwards, a database instance has been able to use ‘dynamic registration’ to notify a listener of the database presence. Earlier versions mandated ‘static registration’, where the listener configuration needed to include a reference to the instance. This screen allows pre-8i database connection information to be entered, if the Oracle 10g listener needs to administer earlier database versions.
V2: Page 358
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
EM Edit Listener : Other Services
ODBA10gDB-OCA-13-29
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Other Services This screen allows the entry of connection information for external procedures, heterogenous services (gateways to non-Oracle products) and the Oracle Rdb database. No further details on these topics are discussed in this class.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 359
Oracle 10g Database Associate
Lesson 13 – Network Administration
Listener Management
• Automatic instance registration • Listener load balancing • Failover and troubleshooting
ODBA10gDB-OCA-13-30
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Listener Management
Automatic Instance Registration From Oracle8i, a database server can automatically register itself with a listener, passing service information to the listener on service startup. The service does not have to be registered in the listener.ora file on the host. Registered services can be listed using the command 'lsnrctl services'. A database will register with the default listener; this can be overridden by setting the init.ora parameter LOCAL_LISTENER to specify the listener name to be used. The INSTANCE_NAME and SERVICE_NAME parameters must also be set (in the instance parameter file) to register the database automatically.
Listener Load Balancing For Oracle Real Application Clusters (RAC), multiple listeners can be configured to balance connection loads between themselves. RAC is not discussed further in this class.
Failover and Troubleshooting Oracle Transparent Application Failover (TAF) allows Oracle Net to fail over a broken connection to a different listener. This permits the user to continue working using the new connection, as if the original connection had never failed. TAF involves manual configuration of a net service name that includes the FAILOVER_MODE parameter included in the CONNECT_DATA section of the connect descriptor. sales.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))
V2: Page 360
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Connections : Two-task Architecture
• Most common connection method (client / server) • Server (shadow) runs on database host • Bequeath or redirect session • TCP/IP, IPC or other protocols
ODBA10gDB-OCA-13-31
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Connections : Two-task Architecture The listener waits for incoming connection requests on the configured network protocol (and port). When a request is received, the listener will spawn a server (shadow) process. The session connection information is bequeathed to the newly spawned server process. Further communication between the client and the database instance does not require the listener to be active. The separation of user and server processes, even on the same system, provides a level of additional security. For each user process, there is a dedicated server process started. Even when the user is not making requests, the server process remains in memory and uses resources. Using the two-task architecture allows the user to have immediate access through their dedicated server process to the database host. Some operating system overhead is used in managing a large number of (potentially) idle processes.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 361
Oracle 10g Database Associate
Lesson 13 – Network Administration
Connections : Shared server ReqQ sqlplus usr/pwd@host
O r a c l e N e t
DISPATCHER 1
LISTENER
RespQ1 SERVER 1 SERVER 2
DISPATCHER 2
SERVER 3 SERVER 4
ODBA10gDB-OCA-13-32
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Connections : Shared Server The Shared Server (formerly Multithreaded Server [MTS]) feature overcomes some of the operating system load in managing server processes for each user, by sharing a smaller number of processes amongst multiple user processes. For OLTP applications, shared servers may be more efficient, since users are not communicating with the server continuously. More users may be able to supported (effectively) within a specified server hardware architecture than with the two-task architecture.
V2: Page 362
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Configuring Shared Server A database instance must be configured to use shared server, by setting the init.ora parameters below: DISPATCHERS
Number of dispatchers to start when the instance starts, for a specified protocol.
MAX_DISPATCHERS
Maximum number of dispatchers allowed on this instance.
SHARED_SERVERS
Number of servers to start when the instance starts.
MAX_SHARED_SERVERS
Maximum number of servers allowed on this instance.
... dispatchers = "(PROTOCOL=TCP)(DISPATCHERS=3)" max_dispatchers=10 shared_servers = 8 max_shared_servers = 20 ...
The MTS_DISPATCHERS and MTS_SERVERS parameters are dynamic and can be altered by the DBA for load balancing:
SQL> ALTER SYSTEM SET SHARED_SERVERS = 12
Making a connection and processing requests A connection is made using the listener in the standard way – the listener then routes the connection to a ‘DISPATCHER’ process for the instance. This selected dispatcher then controls the connection for the duration of the user session. The dispatcher adds subsequent requests to a single dispatcher request queue. The request will be dequeued by any available server for processing. The results are placed onto a response queue for the appropriate dispatcher (one queue is maintained for each dispatcher). When the dispatcher finishes it’s current processing, it will check it’s queue and process any pending responses by routing them back to the relevant client.
Tuning shared servers The DBA can monitor the length of request and response queues, by examining the relevant MTS tables (‘v$dispatcher’ and ‘v$shared_server’). If these table indicate, the DBA can either add or stop dispatcher and server processes to improve response times or reduce the OS load on the system.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 363
Oracle 10g Database Associate
Lesson 13 – Network Administration
Running TNSPING
• Command line tool to determine whether a service is reachable • Displays round trip time in msec • No username/password required
ODBA10gDB-OCA-13-33
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Running TNSPING The Oracle ‘tnsping’ utility allows the DBA to determine whether a net service is reachable. This command line utility can be invoked using the command : tnsping netServiceName [count]
Running tnsping confirms that an instance can be reached and returns the round-trip time in milliseconds. If the instance is not reachable, an error message is displayed. C:\>tnsping v10g TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on D/M/Y Used parameter files: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = JEREMYXP)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = v10g))) OK (20 msec) C:\>lsnrctl stop LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on D/M/Y Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) The command completed successfully C:\>tnsping v10g TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on D/M/Y Used parameter files: C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = JEREMYXP)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = v10g))) TNS-12541: TNS:no listener
V2: Page 364
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Trace Levels
• OFF – No trace information
• USER – Basic user level information
• ADMIN – Database Administrator level information
• SUPPORT – Oracle support/internal information ODBA10gDB-OCA-13-34
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Trace Levels When setting up logging and tracing for each Oracle tool, a setting can be specified to control the level of detail required in the appropriate file. The following levels are supported: OFF
Tracing is disabled
USER
Tracing information is applicable to users
ADMIN
Tracing information is suitable for database administrators
SUPPORT
Tracing information is relevant to Oracle customer support only
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 365
Oracle 10g Database Associate
Lesson 13 – Network Administration
Logging and Tracing Parameters
• listener.ora – [log|trace]_[file|directory]_listener
• sqlnet.ora – [log|trace]_[file|directory]_[client|server] – trace_level_client, trace_unique_client
ODBA10gDB-OCA-13-35
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Logging and Tracing Parameters Logging and tracing parameters can be set in a variety of different Oracle configuration files.
LISTENER.ORA LOG_FILE_LISTENER LOG_DIRECTORY_LISTENER TRACE_FILE_LISTENER TRACE_DIRECTORY_LISTENER
SQLNET.ORA LOG_FILE_LISTENER LOG_DIRECTORY_LISTENER TRACE_FILE_LISTENER TRACE_DIRECTORY_LISTENER TRACE_LEVEL_CLIENT TRACE_UNIQUE_CLIENT CMAN_PROFILE
V2: Page 366
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Client Logging/Tracing
• Profile > General > Logging / Tracing • Directories must already exist
ODBA10gDB-OCA-13-36
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Client Logging/Tracing Using Oracle Net Manager, select the Local > Profile option in the left hand menu. Using the drop down list in the main panel, choose the General option
Tracing option
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 367
Oracle 10g Database Associate
Lesson 13 – Network Administration
Logging Option
Setting Options For each logging and tracing option, both directory names and file names can be specified. Directory names must exist prior to being able to use the directory in the Net Manager tool. For client tracing only, the Unique Trace File Name checkbox can be set to generate a unique file name for each new trace file created. If this box is left unchecked, trace information for multiple sessions will be written to the same trace file on a client system. The server information in the tracing tab is used for tracing server networking operations only.
Tracing Precautions Tracing operations, especially at the higher (ADMIN) level, can generate a large amount of information very rapidly. Ensure that sufficient disk space is available before enabling tracing. Only enable the tracing option when a network issue is suspected.
V2: Page 368
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
SQLNET.ORA Logging/Tracing
# SQLNET.ORA Network Configuration File: C:\...sqlnet.ora # Generated by Oracle configuration tools.
TRACE_DIRECTORY_CLIENT = c:\trace TRACE_UNIQUE_CLIENT = on TRACE_DIRECTORY_SERVER = c:\server TRACE_FILE_CLIENT = orcl_trace TRACE_FILE_SERVER = server_trace TRACE_LEVEL_CLIENT = ADMIN SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)
ODBA10gDB-OCA-13-37
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
SQLNET.ORA Logging/Tracing An example of a SQLNET.ORA file is shown below. Relevant sections are highlighted.
# SQLNET.ORA Network Configuration File: C:\oracle\ora90\NETWORK\ADMIN\sqlnet.ora # Generated by Oracle configuration tools. TRACE_DIRECTORY_CLIENT = C:\Trace LOG_FILE_CLIENT = client.log TRACE_UNIQUE_CLIENT = on TRACE_DIRECTORY_SERVER = C:\Server SQLNET.AUTHENTICATION_SERVICES= (NTS) LOG_DIRECTORY_CLIENT = c:\oracle\ora90\network\log TRACE_FILE_CLIENT = orcl_trace TRACE_FILE_SERVER = server_trace TRACE_LEVEL_CLIENT = ADMIN TRACE_LEVEL_SERVER = USER NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 369
Oracle 10g Database Associate
Lesson 13 – Network Administration
Server Logging/Tracing
• Listener > General Parameters > Logging & Tracing
ODBA10gDB-OCA-13-38
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Server Logging/Tracing To configure logging and tracing for the LISTENER process, select Local > Listener and the required listener name, to display the listener control panel. From the drop down list, choose General Parameters, then the Logging and Tracing tab:
Select the appropriate logging or tracing radio button, to turn the facility on or off as needed. When enabling logging, specify the name of the required listener log file. The default directory is the Oracle home directory, network/log subdirectory. The default file name is "LISTENER.log". When enabling tracing, specify the required tracing level (see page Error! Bookmark not defined.). The default directory is the Oracle home directory, network/trace subdirectory. The default file name is "LISTENER.trc".
V2: Page 370
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
LISTENER.ORA Logging/Tracing
... (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oracle\ora90) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = C:\oracle\ora90) (SID_NAME = orcl) ) )
TRACE_LEVEL_LISTENER = USER
ODBA10gDB-OCA-13-39
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LISTENER.ORA Logging/Tracing An example of a LISTENER.ORA file is shown below. Relevant sections are highlighted. Note that when default parameters are specified in Net Assistant, the parameters are not included in the generated ORA file: # LISTENER.ORA Network Configuration File: C:\oracle\ora90\NETWORK\ADMIN\listener.ora # Generated by Oracle configuration tools. TRACE_FILE_LISTENER = MY_LISTENER.trc LOG_DIRECTORY_LISTENER = C:\oracle\ora90\network\log LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = JEREMYRUSSELL)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = C:\oracle\ora90) (SID_NAME = orcl) ) ) TRACE_LEVEL_LISTENER = ADMIN LOG_FILE_LISTENER = MY_LISTENER.log TRACE_DIRECTORY_LISTENER = C:\oracle\ora90\network\trace
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 371
Oracle 10g Database Associate
Lesson 13 – Network Administration
Listener Audit Trail
• Samples from listener.log • Successful connection request 01-JAN-2006 13:16:24*(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=v10g) (CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe) (HOST=JEREMYXP)(USER=jeremy))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.4)(PORT=2141)) * establish * v10g * 0
• Listener reload/service registration Trace level is currently 0 01-JAN-2004 12:00:00 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=) (USER=))(COMMAND=reload)(ARGUMENTS=64) (SERVICE=LISTENER)(VERSION=153092352)) * reload * 0 ... 01-JAN-2006 13:18:14 * service_register * v10g * 0 ODBA10gDB-OCA-13-40
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Listener Audit Trail The listener audit trail includes details of listener start ups, shut downs and service registrations/deregistrations.
Listener Startup TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-MAY-2007 D/M/Y Copyright (c) 1991, 2005, Oracle.
All rights reserved.
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log Trace information written to C:\oracle\product\10.2.0\db_1\network\trace\listener.trc Trace level is currently 0 Started with pid=2796 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JEREMYXP)(PORT=1521))) Listener completed notification to CRS on start TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE 21-MAY-2007 13:19:51 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=jeremy))(COMMAND=status)(ARGUMENTS=64) (SERVICE=LISTENER)(VERSION=169869568)) * status * 0
Service Registration 21-MAY-2007 13:20:42 * service_register * v10g * 0 21-MAY-2007 13:20:51 * service_update * v10g * 0
V2: Page 372
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Listener Shutdown No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JEREMYXP)(PORT=1521))) Listener completed notification to CRS on stop 21-MAY-2007 13:21:40 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=jeremy))(COMMAND=stop)(ARGUMENTS=64) (SERVICE=LISTENER)(VERSION=169869568)) * stop * 0
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 373
Oracle 10g Database Associate
Lesson 13 – Network Administration
Network Error Codes Code
Message
Cause
3113 EOF on communication channel
Database server failed
12154 Could not resolve service name
Incorrect connect info
12514 Listener cannot resolve service
Incorrect connect info
01034 Oracle not available
Database server down
01017 Invalid username/password
Correct details
12224 No listener
Listener is down
12203 Failed to connect to destination
Incorrect connect info
12224 Failed to detect listener on host
Check listener config
12500 Failed to start dedicated server
Check listener config
12545 Lookup failure of TNS
Check connect info
12560 Protocal adapter error
Use tracing
ODBA10gDB-OCA-13-41
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Network Error Codes When a network problem occurs, one of the following common network errors is likely to be displayed.
Code
Message
Cause
3113
EOF on communication channel
Database server failed
12154
Could not resolve service name
Incorrect connect info
12514
Listener cannot resolve service
Incorrect connect info
01034
Oracle not available
Database server down
01017
Invalid username/password
Correct details
12224
No listener
Listener is down
12203
Failed to connect to destination
Incorrect connect info
12224
Failed to detect listener on host
Check listener config
12500
Failed to start dedicated server
Check listener config
12545
Lookup failure of TNS
Check connect info
12560
Protocal adapter error
Use tracing
V2: Page 374
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Network Problem Diagnosis
• Examine log file (sqlnet.log), to review error message at end of file • Enable tracing and use Trace Assistant to format trace files • Trace files created in selected directory • Trace file names set in Network Manager / sqlnet.ora
ODBA10gDB-OCA-13-42
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Network Problem Diagnosis When a networking problem is suspected, the network administrator should examine the ‘SQLNET.LOG’ file (see page 377). Error information is written to the end of the log file. The problem may be resolvable from this information, which includes date and time information together with details of the error code and error message. For more information, if the SQLNET.LOG does not provide enough information for a resolution to be determined, the network administrator can enable selective tracing as already described. The failing operation can then be repeated.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 375
Oracle 10g Database Associate
Lesson 13 – Network Administration
Network Stack Codes In Logs Application Application
Application Application
NR NR OCI OCI
NS NSMain Main NS(2) NS(2)
Two TwoTask TaskCommon Common TNS TNS
NA NA
NT NTMain Main
OPA OPA
NT NTMain Main NT NTMain Main
Protocol Protocol
ODBA10gDB-OCA-13-43
NN NN
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Network Stack Codes in Logs In the networking logging and tracing files, codes are used to represent the OSI network layer that generated the message. The following network layer/stack codes are used:
Code
OSI Layer Name
NA
Network Authentication
NI
Network Interface
NN
Network Naming
NR
Network Routing
NS
Network Session
NT
Network Transport
V2: Page 376
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
SQLNET Error Log Extract
*********************************************************************** Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=orcl)(CID= (PROGRAM=C:\oracle\product\10.2.0\db_1\jdk\jre\bin\java.exe) (HOST=JEREMYXP)(USER=jeremy)))) VERSION INFORMATION: TNS for 32-bit Windows: Version 10.2.0.1.0 - Production Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production Time: 01-JAN-2006 09:02:05 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0
ODBA10gDB-OCA-13-44
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
SQLNet Error Log Extract
*********************************************************************** Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=orcl) (CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\jdk\jre\bin\java.exe) (HOST=JEREMYXP)(USER=jeremy)))) VERSION INFORMATION: TNS for 32-bit Windows: Version 10.2.0.1.0 - Production Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production Time: 21-MAY-2007 09:02:05 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 377
Oracle 10g Database Associate
Lesson 13 – Network Administration
Trace File Extract
[01-JAN-2007] [01-JAN-2007] [01-JAN-2007] [01-JAN-2007] [01-JAN-2007] [01-JAN-2007]
--- TRACE CONFIGURATION INFORMATION FOLLOWS --New trace stream is c:\servertrace_3088.trc New trace level is 16 --- TRACE CONFIGURATION INFORMATION ENDS ----- PARAMETER SOURCE INFORMATION FOLLOWS --Attempted load of system pfile source C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora [01-JAN-2007] Parameter source loaded successfully ... [01-JAN-2007] nlstdipi: entry [01-JAN-2007] nlstdipi: exit [01-JAN-2007] nagblini: entry [01-JAN-2007] nau_gin: entry [01-JAN-2007] nau_gparams: entry [01-JAN-2007] nam_gbp: Reading parameter "sqlnet.authentication_required" [01-JAN-2007] nam_gbp: Parameter not found [01-JAN-2007] nau_gparams: Using default value "FALSE" [01-JAN-2007] nau_gslf: entry [01-JAN-2007] nam_gic: entry ...
ODBA10gDB-OCA-13-45
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Trace File Extract [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007 [01-JAN-2007
V2: Page 378
13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364] 13:35:55:364]
nsdo: rank=64, nsctxrnk=0 nsdo: nsctx: state=8, flg=0x400c, mvd=0 nsdo: gtn=107, gtc=107, ptn=10, ptc=2011 nsdofls: entry nsdofls: DATA flags: 0x0 nsdofls: sending NSPTDA packet nspsend: entry nspsend: plen=22, type=6 nttwr: entry nttwr: socket 5496 had bytes written=22 nttwr: exit nspsend: packet dump nspsend: 00 16 00 00 06 00 00 00 |........| nspsend: 00 00 08 02 00 00 00 09 |........| nspsend: 05 00 00 00 12 00 |...... | nspsend: 22 bytes to transport nspsend: normal exit nsdofls: exit (0) nsdo: nsctxrnk=0 nsdo: normal exit nsdo: entry nsdo: rank=64, nsctxrnk=0 nsdo: nsctx: state=8, flg=0x400c, mvd=0 nsdo: gtn=107, gtc=107, ptn=10, ptc=2011 nsdo: switching to application buffer nsrdr: entry nsrdr: recving a packet nsprecv: entry nsprecv: reading from transport... nttrd: entry
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Trace Assistant
• Reformats trace files for readability Usage : trcasst [options] [options] default values are -odt -e0 -s always last argument -o[c|d][u|t][q] Net Services and TTC information [c] Summary of Net Services information [d] Detailed Net Services information [u] Summary of TTC information [t] Detailed TTC information [q] SQL commands (used together with u) -s Statistics -e[0|1|2] Error information, default is 0 [0] Translate NS error numbers [1] Error translation [2] Error numbers without translation -l[a|i ] Connection information [a] List all connections in a trace file [i ] Decode a specified connection
ODBA10gDB-OCA-13-46
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Trace Assistant The Trace Assistant utility reformats trace files for readability. diagnostics are illustrated on the next page:
Copyright © 2007 Jeremy Russell & Associates Ltd.
Some examples of trace file
V2: Page 379
Oracle 10g Database Associate
Lesson 13 – Network Administration
Trace Assistant Utility: Version 10.2.0.1.0 Production on D/M/Y Copyright (c) 2001, 2005, Oracle. All rights reserved. ************************************************************************* * Trace Assistant * ************************************************************************* Packet dump for thread #: 1956 ---> Send 269 bytes - Connect packet timestamp=21-MAY-2007 13:41:18:508 Current NS version number is: 313. Lowest NS version number can accommodate is: 300. Maximum SDU size: 2048 Maximum TDU size: 32767 NT protocol characteristics: Asynchronous mode Callback mode Test for more data Full duplex I/O Urgent data support Handoff connection to another Grant connection to another Line turnaround value: 0 Connect data length: 211 Connect data offset: 58 Connect data maximum size: 512 Native Services wanted Authentication is linked and specify NAU doing O3LOGON - DH key foldedin Native Services wanted Authentication is linked and specify NAU doing O3LOGON - DH key foldedin (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=JEREMYXP)(Port=1521))(CONNEC T_DATA=(SID=v10g)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\perl\5.8. 3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=JEREMYXP)(USER=SYSTEM))) ) Packet dump for thread #: 1956 <--- Received 8 bytes - Resend packet
timestamp=21-MAY-2007 13:41:18:518
Packet dump for thread #: 1956 ---> Send 269 bytes - Connect packet timestamp=21-MAY-2007 13:41:18:518 Current NS version number is: 313. Lowest NS version number can accommodate is: 300. Maximum SDU size: 2048 Maximum TDU size: 32767 NT protocol characteristics: Asynchronous mode Callback mode Test for more data Full duplex I/O Urgent data support Handoff connection to another Grant connection to another Line turnaround value: 0 Connect data length: 211 Connect data offset: 58 Connect data maximum size: 512 Native Services wanted Authentication is linked and specify NAU doing O3LOGON - DH key foldedin Native Services wanted Authentication is linked and specify NAU doing O3LOGON - DH key foldedin
V2: Page 380
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Trace Assistant - Summary
---------------------Trace File Statistics: ---------------------Start Timestamp : 01-JUL-2007 15:44:31:988 End Timestamp : 01-JUL-2007 15:45:49:710 Total number of Sessions: 2 DATABASE: Operation Count: 1 OPENS, Parse Counts: 1 PL/SQL, 3 SELECT, 0 LOCK, 1 TRANSACT, Execute counts with SQL data: 1 PL/SQL, 1 SELECT, 0 LOCK, 0 TRANSACT,
5 PARSES,
5 EXECUTES,
8 FETCHES
0 INSERT, 0 DEFINE,
0 UPDATE, 0 SECURE,
0 DELETE, 0 OTHER
0 INSERT, 0 DEFINE,
0 UPDATE, 0 SECURE,
0 DELETE, 0 OTHER
Operation Ratio: 5.0 PARSES per OPEN,
1.0 EXECUTES per PARSE
Packet Ratio: 7.6 packets sent per operation Currently opened Cursors: 0 Maximum opened Cursors : 1 ...
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA-13-47
Trace Assistant – Summary
---------------------Trace File Statistics: ---------------------Start Timestamp : 01-JUL-2007 13:41:18:508 End Timestamp : 01-JUL-2007 13:41:18:688 Total number of Sessions: 2 DATABASE: Operation Count: 0 OPENS, Parse Counts: 0 PL/SQL, 0 SELECT, 0 LOCK, 0 TRANSACT, Execute counts with SQL data: 0 PL/SQL, 0 SELECT, 0 LOCK, 0 TRANSACT,
3 PARSES,
1 EXECUTES,
1 FETCHES
0 INSERT, 0 DEFINE,
0 UPDATE, 0 SECURE,
0 DELETE, 3 OTHER
0 INSERT, 0 DEFINE,
0 UPDATE, 0 SECURE,
0 DELETE, 0 OTHER
Packet Ratio: 19.0 packets sent per operation Currently opened Cursors: 0 Maximum opened Cursors : 0 ORACLE NET SERVICES: Total Calls : Total Bytes : Average Bytes: Maximum Bytes:
19 sent, 17 received, 7 oci 4365 sent, 4452 received 229 sent per packet, 261 received per packet 1119 sent, 1011 received
Grand Total Packets:
19
sent,
17 received
************************************************************************* * Trace Assistant has completed * *************************************************************************
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 381
Oracle 10g Database Associate
Lesson 13 – Network Administration
Summary
• In this lesson, you should have learnt about: – The Oracle networking system – Configuring Net Service aliases – Using Listener features – Configuring client connections with Net Manager – Testing connections with the TNSPING utility – Oracle name resolution methods ODBA10gDB-OCA-13-48
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 382
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 13 – Network Administration
Oracle 10g Database Associate
Practice 13
• Use Net Manager to examine your local networking configuration • Use EM to check status of local listener • Use lsnrctl to check listener services • Working with another delegate, configure a client connection to their database • Connect to the configured database
ODBA10gDB-OCA-13-49
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 13 In this practice, you will perform a range of networking tasks. Use Net Manager to examine your local networking configuration. ‘/NETWORK/ADMIN’.
Also check the files at
________________________________________________________________________ ________________________________________________________________________ Use Enterprise Manager and the listener control utility to check the status of your listener.
________________________________________________________________________ ________________________________________________________________________ Working with another delegate, configure a connection to their Oracle instance. ________________________________________________________________________ ________________________________________________________________________ Using SQL*Plus, use the new connect string to access the database as ‘system’. Confirm that you are using the new machine with the command: SELECT host_name FROM v$instance;
________________________________________________________________________ ________________________________________________________________________ Confirm that the EMP2 table is still accessible and contains 14 rows. ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 383
Oracle 10g Database Associate
Lesson 13 – Network Administration
This page intentionally left blank
V2: Page 384
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
14 0 Backup and Recovery Concepts
ODBA10gDB-OCA-14-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 14 – BACKUP AND RECOVERY CONCEPTS This lesson discusses the Oracle backup and recovery principles.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 385
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Objectives
• After completing this lesson, you will have an understanding of how to : – Safeguard the different database files – Use redo logs and control files – Set up redo log archiving – Configure the Flash Recovery area – Work with the RMAN tool – Identify possible causes of failure
ODBA10gDB-OCA-14-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives This lesson discusses the Oracle backup and recovery principles. Topics include: How to safeguard the different database file types (data, control, redo log). Use of redo logs and control files by running instances Setup requirements for redo log archiving Flash Backup and Recovery options and use of the 10g Flash Recovery Area Recovery Manager (RMAN) options and usage Possible causes of failure necessitating recovery
V2: Page 386
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Recovery Structures SGA Shared Pool
Buffer
Java Pool
Pool
Log Buffer
Large Pool
Processes DBWR
SMON
PMON
LGWR
SERVER
System
Sysaux
Users
Undo
CKPT
ARCH
Example
Archive Logs Parameter
ODBA10gDB-OCA-14-3
Control
Redo Logs
Database
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Recovery Structures The buffer cache is the SGA memory area that stores recently used disk blocks. This buffer cache is filled by shadow processes in response to SQL queries. Changes to data blocks are also made by the shadow process in the buffer cache. At intervals, or when requested to clean memory, the DBWR process writes changed (dirty) blocks from the buffer cache back to disk. Regular writes are triggered by the checkpoint event. Data written by the DBWR may be committed or uncommitted data – writing is asynchronous from transaction processing (COMMIT’s). It is normal for data files to contain uncommitted data during normal operations. At a SHUTDOWN, incomplete transactions will reverse uncommitted data before the shutdown completes. Committed data still in memory is sync’ed back to the data file before shutdown completes. If a power failure or SHUTDOWN ABORT prevents clean checkpointing of the data files, the automatic instance recovery procedure that is triggered by the next startup will use the redo logs and rollback segments to remove uncommitted data and update committed data before the files can be used again. The details of this process may vary between Oracle versions. Production databases running (as recommended) in ARCHIVELOG mode will invoke the ARCH process to copy filled log areas to an offline directory, freeing the online logs for overwriting. By ensuring sufficient online logs for the maximum concurrent user base, the DBA helps to prevent all users waiting for log space to become free.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 387
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Recovery Features & Operations
• Recovery Features – – – – – –
Redo Log Buffer LGWR Process Redo Logs ARCH Process Control Files CKPT Process
• Recovery Operations – Restore – Recover
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA-14-4
Recovery Features Redo Log Buffer : temporary area for transaction details LGWR Process
: empties redo log buffer to redo log
Redo Logs
: multiple log groups used in a circular fashion
ARCH Process
: optional process for copying full online logs
Control Files
: names and status details for data and log files
CKPT Process
: process used to synchronise data file and control file headers
Recovery Operations Recovery operations generally require a two phased operation: Restoration
: of all datafiles from a previous backup
Recovery
: of the contents to a defined point in time
V2: Page 388
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Redo Log Buffer
• Memory area for transaction information • Written by SHADOW process – Part of normal processing
• Emptied by LGWR process – At commit, timeout, 1MB of data, 1/3 full – Before DBWR writes modified blocks
• Size controlled by LOG_BUFFER parameter – Smaller is better ODBA10gDB-OCA-14-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Redo Log Buffer The redo log buffer is a reusable memory area that stores information about updates (change vectors) made to database blocks. The change vector records sufficient information to recreate the changes made by DML and DDL operations. Information is written to the log buffer by a shadow process attached to each client process. The information is emptied to the online redo log files by the log writer ‘LGWR’ process at intervals. The log buffer size is controlled by the ‘LOG_BUFFER’ parameter. A small size is recommended and typical sizes will be discussed further in lesson 19 – Diagnostics and Tuning.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 389
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
LGWR Process
• Empties Redo Log Buffer • Writes to current log file (group) – When the redo log buffer is 1/3 full – On a three second time out. – Whenever a commit occurs – In sync with the DBWR' s processing – Provided there is sufficient free space in the current online log
ODBA10gDB-OCA-14-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LGWR Process The log writer (‘LGWR’) process, started with the database instance, empties the redo log buffer periodically to the current online redo log file (group). As part of this regular process, uncommitted changes may be recorded in the log file together with committed changes. If the log buffer becomes full and ‘LGWR’ has no space to perform the required disk I/O, the entire instance will halt pending release of space on disk.
V2: Page 390
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Redo Logs
• • • • • • •
Written by LGWR process Members of a group written at same time Minimum of two log groups required No preset maximum group or members Groups can be added, dropped or moved Should be multiplexed Dictionary tables: V$LOG, V$LOGFILE
ODBA10gDB-OCA-14-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Redo Logs
• Online Redo Logs Redo logs are written by the ‘LGWR’ process, from change vectors stored in the SGA’s redo log buffer. All members of a multiplexed group are written simultaneously. A database requires a minimum of two online redo logs to operate. Further groups can be added to a database and additional members can be added to a group. Sufficient groups should be created to accommodate peak period usage. New log groups can be added with the ‘ALTER DATABASE ADD LOGFILE’ command: SQL> ALTER DATABASE ADD LOGFILE 2 GROUP 4 ('$HOME/disk5/redoD1.log', '$HOME/disk6/redoD2.log') SIZE 10M, 3 GROUP 5 ('$HOME/disk5/redoE1.log', '$HOME/disk6/redoE2.log') size 10M;
New members can be added to existing groups with the ‘ADD LOGFILE MEMBER’ clause: SQL> ALTER DATABASE ADD LOGFILE MEMBER 2 '$HOME/disk5/redoD3.log'TO GROUP 4, 3 '$HOME/disk5/redoE3.log'TO GROUP 5;
An entire log group or a single member can be removed, provided that the log group is not the current log, the removed file is not the last member of the group and there are two other groups still active and online: SQL> ALTER DATABASE DROP LOGFILE GROUP 4; SQL> ALTER DATABASE DROP LOGFILE MEMBER '$HOME/disk5/redoE3.log';
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 391
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Redo Logs - EM
• EM > Administration > Storage > Redo Log Groups
ODBA10gDB-OCA-14-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Redo Logs – EM Redo log management can also be performed using EM. Select Administration > Storage > Redo log groups to display the current redo log setup. From the drop down menu, the following operations can be selected Clear Logfile
Clear a corrupted file without archiving
Create Like
Create a new log group like the selected group
Force Checkpoint
Manually write dirty blocks to disk
Generate DDL
Generate statements to create the selected group
Sizing Advice
If advisors are on, recommend optimal log sizes
Switch Logfile
Manually switch logfile to next group
V2: Page 392
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Dictionary Tables
• V$LOG – one entry for each log group SQL> desc v$log Name Null? Type ----------------------------------------- -------- ---------------------------GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2(3) VARCHAR2(16) NUMBER DATE
• V$LOGFILE – one entry for each log member SQL> desc v$logfile Name Null? Type ----------------------------------------- -------- ---------------------------GROUP# STATUS TYPE MEMBER
Copyright © 2007 Jeremy Russell & Associates Ltd.
NUMBER VARCHAR2(7) VARCHAR2(7) VARCHAR2(513)
V2: Page 393
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
ARCH Process
• Optional process to copy filled redo logs to separate areas • Recommended for production databases • Multiple copies can be taken • Log switch event (automatic or manual) triggers archiving • Online log area reused after archiving • No proper recovery without archiving ODBA10gDB-OCA-14-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ARCH Process The archiver (‘ARCH’) process is optional but strongly recommended for production databases. When started, this process copies filled logs to an offline area, preferably the Flash Recovery Area. Without these stored redo logs, the database cannot be recovered (corrected) in the event of a disk error.
• NOARCHIVELOG If the instance is running in ‘NOARCHIVELOG’ mode, the ARCH process is not started. When an online redo log is full, a log switch event occurs and further I/O by the LGWR process occurs to the next available log group. When all log groups have been filled, processing continues with the first group in the sequence, thus overwriting older information.
• ARCHIVELOG When the instance is running in ‘ARCHIVELOG’ mode, the ARCH process is triggered at a log switch and copies the contents of the filled log to the offline area(s) specified in the parameter file. Where there are multiple logs in a group, ARCH randomly chooses one log file to copy to the offline area. Once the complete log contents have been copied, the log file (group) is marked as ARCHIVED and may then be reused when all other log files are filled.
V2: Page 394
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Control Files
• Contain name and checkpoint for – Data files – Redo log files
• Database cannot open if files are out of synchronisation • Periodic manual backup is recommended
ODBA10gDB-OCA-14-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Control Files The control files named in the parameter file are updated regularly by various processes, to record the timestamp (SCN – System Change Number) for the last update to a data or log file. When a database instance is started, the SCN’s of each file are compared to the SCN held in the control file. If there is a mismatch – an ‘inconsistent’ database - the database cannot be started until the offending file is recovered. A damaged file or tablespace may be taken offline (ALTER TABLESPACE/DATAFILE … OFFLINE) to open the database without the damaged file, which must then be recovered manually. Since the control file contains the structure of the database, a backup should be taken whenever the structure changes. Such changes include adding or removing tablespaces, datafiles or log files.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 395
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
A control file is backed up using: SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'path_name'; SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The first command takes a snapshot of the control file in binary format to a specified location. This backup can be used in the event that an entire tablespace is inadvertently removed from the system.
The second version can be used in the (hopefully rare) situation when all copies of the current control file are lost or damaged. By using the text version, the control file can be recreated and then resynced with the current data and log files. Some information may be lost by this process. Oracle’s “Out-of-the-box” backup policy, discussed in Lesson 15, automatically backs up control files along with data files as scheduled.
V2: Page 396
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
CKPT Process
• Checkpoint writes all modified blocks back to data file (synchronise buffers) • Header of data file updated (timestamp) • CKPT process always enabled in 10g • Controlled by – LOG_CHECKPOINT_INTERVAL – LOG_CHECKPOINT_TIMEOUT
• More frequent checkpoints take time but reduce potential future recovery time ODBA10gDB-OCA-14-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
CKPT Process The checkpoint process (‘CKPT’) processes the checkpoint event, which that all modified data buffers are re-written to the data files. The data file header(s) are updated and the control file updated with the SCN. Checkpoint frequency is controlled by the parameters: LOG_CHECKPOINT_INTERVAL = number of redo blocks LOG_CHECKPOINT_TIME = seconds
Following a power failure, instance (fast) recovery occurs from the last checkpoint in the redo log file. All changes written to the log file after the checkpoint flag have not been guaranteed to have been written to the data files prior to the failure and therefore all such blocks are written as part of the next instance startup. This set of actions re-synchronises the data files with the control file to ensure that all committed transactions and only committed transactions are present in the data files. Redo log entries written before the last checkpoint do not need recovery. Frequent checkpoints slow down system processing but reduce the time to recover in the event of a system / power failure. Optimum checkpoint intervals are discussed in more detail in Lesson 19 – Diagnostics and Tuning.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 397
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Data Files
• Primary storage for user data • If database is not archiving – Database must be shut down for backup – All control, data, redo log files to be copied
• If database is archiving – Partial backup is permitted (one tablespace or datafile) – Control file should be copied at same time – Redo logs are backed up by archiver ODBA10gDB-OCA-14-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Data Files All data files are read by the server/shadow processes into the buffer cache. Updates to data files are made by the database writer ‘DBWR’ process at intervals. For instances running in ‘NOARCHIVELOG’ mode, the only valid backup method is a cold backup. This consists of a copy of all data, control and online log files taken when the instance has been shutdown. To recover from any media failures for ‘NOARCHIVELOG’ instances, the entire backup set must be restored. No further recovery is possible, since there is no (archived) transaction history. All updates and data applied since the last cold backup will be lost by the recovery For instances running in ‘ARCHIVELOG’ mode, the ‘ARCH’ process maintains a history of updates in the form of archived logs. Backups of datafiles or tablespaces may be taken individually, whilst the system is online. Such backups are known as ‘fuzzy backups’, since the contents are indeterminate at the time the backup is taken. Restore of the backup file must be followed by a recovery phase, where the relevant log contents are replayed against the restored tablespace or datafile. Assuming availability of all archived logs, no data will be lost in the event of a restore and recover becoming necessary.
V2: Page 398
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Archiving Setup - Manual • Database must be in mount mode • Check parameter file settings – LOG_ARCHIVE_DEST – LOG_ARCHIVE_FORMAT – LOG_ARCHIVE_START
• ALTER DATABASE ARCHIVELOG; • ALTER DATABASE OPEN; • SHUTDOWN NORMAL; • TAKE A FULL, COLD BACKUP!!! ODBA10gDB-OCA-14-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Archiving Setup - Manual To switch a database into ‘ARCHIVELOG’ mode, several steps must be performed. The instance must be closed before commencing. The parameter file entries for archiving must be set as below: LOG_ARCHIVE_DEST
the directory name to receive the archived logs. Sufficient space for archived logs must be available on the selected disk drive.
LOG_ARCHIVE_FORMAT
the file name to be used to create the archived logs. The file name can include the following options %s or %S include the log number in the file name %t or %T include the thread number in the file name (applies to parallel processing only)
LOG_ARCHIVE_START
TRUE FALSE
fully automatic archiving occurs (recommended) logs must be archived manually
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 399
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
The instance can then be started, mounted (NOT opened) and switched into ‘ARCHIVELOG’ mode. The database should then be opened to record the changes in the control file, shut down again and an immediate full backup taken. This backup becomes the ‘seed’ for any future recoveries.
SQL> STARTUP MOUNT [pfile=parameterFilePath] SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> SHUTDOWN NORMAL
V2: Page 400
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Archiving Setup - EM
• EM > Maintenance > Recovery Settings • Check ARCHIVELOG mode & Destination
ODBA10gDB-OCA-14-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Archiving Setup – EM To switch a database into ‘ARCHIVELOG’ using Enterprise Manager, select Maintenance > Recovery Settings. Check the ‘ARCHIVELOG mode’ checkbox and confirm that Archive Log Destination 10 is set to ‘USE_DB_RECOVERY_FILE_DEST’, indicating that the Oracle recommended strategy of using the Flash Recovery Area has been selected. To perform this change manually, use the following SQL commands: SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = nG; SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/pathName';
The file size must be set before setting the destination and should be large enough to hold at least one complete copy of the entire database, together with any incremental backups (Lesson 15) and archived log files retained online. In automatic mode, Oracle will delete files that are obsolete if there is insufficient space for further backups. In addition, warnings are written to the alert log when the flash recovery area reaches 85% full. A critical message is written to the alert log when the flash recovery area reaches 97% full.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 401
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Archiving Setup - Confirmation
• Apply changes and restart instance
ODBA10gDB-OCA-14-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Archiving Setup – Confirmation After making the changes recommended on the previous slide, clicking ‘Apply’ displays a confirmation screen as shown. To fully apply the required backup and recovery strategy, the database instance must be restarted as mentioned previously on page 399. Confirm this action by clicking the ‘Yes’ button on the confirmation screen. Once the database has been restarted, ‘ARCHIVELOG’ status can be confirmed by inspecting the output of the SQL*Plus command illustrated below:
SQL> ARCHIVE LOG LIST Database log mode Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence SQL> _
V2: Page 402
Archive Mode Enabled USE_DB_RECOVERY_FILE_DEST 11 13 13
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Flash Recovery
• Dedicated disk area for all backups • Defined as a single directory (file system or ASM disk group) • One flash recovery area can be shared between multiple databases Database 1 Flash Recovery Area Database 1 ODBA10gDB-OCA-14-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Flash Recovery The Oracle 10g Flash Recovery Area is a dedicated storage area where all recovery files and activities for one or more Oracle databases are stored. The Flash Recovery Area can be a single disk directory, a file system or an Automatic Storage Management (ASM) disk group (not discussed in this course). Recovery files that may be written to this area include: Control files Archived log files
using parameter ‘LOG_ARCHIVE_DEST_10’
Flashback logs
for point-in-time recovery without restoring files (see page 404)
Control file autobackups
as written during data file backups by RMAN
Data file copies
RMAN ‘BACKUP AS COPY’ commands
RMAN files
offline/archived logs read during RMAN recovery from tape
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 403
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Define Flash Recovery Area
• Requires ' SYSDBA'login • EM > Maintenance > Backup/Recovery Settings > Recovery Settings > Scroll
ODBA10gDB-OCA-14-17
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Define Flash Recovery Area A Flash Recovery Area is defined using EM > Maintenance > Backup/Recovery Settings > Recovery Settings. Scroll to the end of this screen to display the Flash Recovery Area detail. Information required includes: Flash Recovery Area Location
Full path of disk directory to be used
Flash Recovery Area Size
Defaults to 2 GB
The ‘Enable Flashback Database’ checkbox can also be checked, to permit flashback database operations that restore the entire database to a point in time without the need to restore old backups of data files. Checking this option enables the writing of ‘Flashback Logs’ to the Flash Recovery Area. The Flashback Retention Period can also be set, to define how long Flashback Logs are retained in the Flash Recovery Area.
V2: Page 404
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Recovery Manager Utility
• Command line backup/recovery tool • Used by EM to perform operations • Can use a catalog area for persistence
ODBA10gDB-OCA-14-18
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Recovery Manager Utility Recovery Manager (‘RMAN’) is a client side executable program that can be used to perform both backup and recovery tasks on a target database. The utility can be used in an interactive mode or via the EM GUI interface. The majority of EM backup and recovery operations invoke RMAN as a scheduled job to perform the requested actions. RMAN can optionally use a recovery catalog area, to store recovery data, it’s own configuration settings and the target database schema. The recovery catalog can reside in the target database control file. For added functionality, including storage of canned scripts for performing RMAN tasks, a recovery catalog stored in a separate database instance. This configuration is recommended where there are multiple database instances to be managed with RMAN. One recovery catalog instance can deal with more than one target database.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 405
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
• RMAN Advantages RMAN backups have the following advantages over more traditional “file copy” backups as described further in Lesson 15 - Backup Procedures. Skipping unused blocks
‘Never-used’ blocks are not backed up in ‘BACKUPSET’ mode.
Backup compression
Proprietary compression methods minimise media needed.
Open database backups
Simplified open backups when in ‘ARCHIVELOG’ mode
Incremental backups
Block level incremental backups can be performed
Block level recovery
Small numbers of damaged blocks can be fixed online
Multiple I/O channels
Multiple, parallel processes can be used for faster operations
Platform independence
RMAN commands are portable to all supported platforms
Tape manager integration
Third party media managers are supported
Cataloging
RMAN operations are stored for subsequent reporting
Scripting support
Scripts in a recovery catalog can be maintained with RMAN
V2: Page 406
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Points of Failure
• • • • •
SQL statement fails User / shadow process fails User error / mistake Instance / power failure Hard disk / media problem
ODBA10gDB-OCA-14-19
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Points of Failure The following potential points of failure can occur within a running Oracle instance. SQL statement fails
Oracle recovers from statement failures (logic errors, constraint checks, privilege limitations) by rolling back the offending statement No action is required by the DBA
User / shadow process fails
The user disconnects incorrectly, kills a process or an exception occurs. PMON will rollback any partial updates when the abnormal process is detected.
User error/mistake
A user may drop a table or an entire tablespace inadvertently. Recovery actions depend on the severity of the issue but may require DBA intervention or a complete restore and partial database recovery.
Instance/power failure
No recovery action is required by the DBA. The next instance startup will enter the ‘fast recovery’ mode and thus resynchronise the data and control files.
Media failure
A soft or hard head crash will require DBA action to restore the offending file to the same (or a different) place, then recover the contents using the archived logs. Precise recovery steps will depend on which file(s) are damaged and are discussed in Lesson 16.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 407
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
Summary
• In this lesson, you should have learned about: – Safeguarding the different database files – Use of redo logs and control files – Setting up redo log archiving – Flash Backup and Recovery options – RMAN tool and options – Possible causes of failure
ODBA10gDB-OCA-14-20
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 408
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 14 – Backup and Recovery Concepts
Oracle 10g Database Associate
Practice 14
• Switch your database into ARCHIVELOG mode, to use the Flash Recovery Area • Check that the instance has properly restarted in this mode
ODBA10gDB-OCA-14-21
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 14 In preparation for exercises in Lesson 15 and 16, switch your database into ‘ARCHIVELOG’ mode and ensure it is using the Flash Recovery Area. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ Check that the instance has properly restarted in the relevant mode, using the SQL*Plus ‘ARCHIVE LOG LIST’ command. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 409
Oracle 10g Database Associate
Lesson 14 – Backup and Recovery Concepts
This page intentionally left blank
V2: Page 410
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
15 0 Database Backups
ODBA10gDB-OCA-15-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 15 – DATABASE BACKUPS Lesson 15 describes and demonstrates a variety of database backup procedures.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 411
Oracle 10g Database Associate
Lesson 15 – Database Backups
Objectives
• After completing this lesson, you should be able to do the following: – Perform closed/consistent database backups – Perform open data file / incremental backups – Backup control files – Use EM and RMAN for backup operations, including image copies and backup sets – Automate database backups – Monitor the Flash Recovery Area ODBA10gDB-OCA-15-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 15 describes and demonstrates a variety of database backup procedures. Topics include: Performing closed or consistent database backups Perform open data file and incremental backups Backing up control files Using Enterprise Manager and RMAN for backup operations A comparison between image copies and backup sets How to automate database backups Monitoring the Flash Recovery Area
For the backup operations described in this lesson, you must connect to the database specifying an account with ‘SYSDBA’ or ‘SYSOPER’ privileges. Enterprise Manager invokes the ‘RMAN’ utility to perform manual and automated backup tasks.
V2: Page 412
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Closed Database Backup - Manual
• Closed backup includes – Control files, Online logs, Data files – Parameter file, Password file
• Instance must be closed before backup • Operating system copy of all files • Logs are not mandatory but simplify eventual restore
ODBA10gDB-OCA-15-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Closed Database Backup - Manual A closed backup, used when the database instance is running in NOARCHIVELOG mode, is simple to perform. The database must be shutdown (normal, immediate or transactional). All control files and data files should copied to removeable media to form the backup image of the database file. It is advisable to copy the online log files at the same time – whilst the contents are not required, a copy of the physical file(s) are useful to speed any future restore operation. The parameter file should also be copied. It is advisable to maintain a printed version of this file in the event that the backups are lost or damaged. The password file should also be copied. Lost or damaged password files can be recovered – the contents of the file can be viewed using the dictionary table V$PWFILE_USERS.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 413
Oracle 10g Database Associate
Lesson 15 – Database Backups
This closed, offline backup can be used to restore the database to the precise state it was in when the instance was shut down. This type of backup is often useful as a part of a disaster recovery plan, since it is both self contained and easier (faster) to restore than a more complex online backup. A list of the files to be copied can be generated using the SQL script below:
SQL> SET HEADING OFF FEEDBACK OFF SQL> 2 3 4 5
SELECT 'cp ' || name || ' backupLocation' FROM v$datafile UNION SELECT 'cp ' || name || ' backupLocation' FROM v$controlfile UNION SELECT 'cp ' || member || ' backupLocation' FROM v$logfile;
SQL> SET HEADING ON FEEDBACK ON
V2: Page 414
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Closed Database Backup – EM
• EM > Maintenance > Schedule Backup > Customized Backup > Whole Database – Backup Type : Full, Incremental, Refresh – Backup Mode: Offline Backup
• Specify destination disk directory • Schedule backup once and immediately • Instance shuts down, is backed up and then restarted automatically ODBA10gDB-OCA-15-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Closed Database Backup – EM To perform a closed backup using Enterprise Manager, on the Maintenance tab select Schedule Backup > Customized Backup > Whole Database radio button. Provide the host credentials with relevant OS privileges. Click ‘Schedule Customized Backup’.50% On the ‘Schedule Customized Backup: Options’ display, specify the following options: Backup Type
Full Backup. A full backup will only omit ‘never used’ blocks. Incremental backups will only copy blocks changed since the previous full backup. If it is intended to perform further incremental backups, check the ‘Use as base …’ checkbox. Checking ‘Refresh the latest datafile copy …’ will update older copies of backed up datafiles to reflect changes copied in an incremental backup, thus updating the older copies to the present time.
Backup Mode
Offline Backup. Automatically shuts down the instance before backup.
Advanced
Backup archived logs. Recommended for easier restoration if required.
On the ‘Schedule Customized Backup: Settings’ display, specify a location for writing the backup. Choose ‘View Default Settings’ to make permanent changes to this information or ‘Override Current Settings’ to make changes for this operation only. Disk settings that can be overridden include the degree of parallelism, the disk location and the backup type (backup set [ optionally compressed] or image copy). The settings can be tested before proceeding.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 415
Oracle 10g Database Associate
Lesson 15 – Database Backups
On the ‘Schedule Customized Backup: Schedule’ display, the backup can be started immediately or set for later unattended and/or repeated execution. A job name and description can also be specified for identification in the job scheduler system. On the ‘Schedule Customized Backup: Review’ display, the selected parameters will be displayed, together with the ‘RMAN’ script that will be used for the backup. An example script is shown below: run { allocate channel oem_backup_disk1 type disk format 'C:\Backup\%U'; backup as COPY tag '%TAG' database include current controlfile; backup as COPY archivelog all not backed up; release channel oem_backup_disk1; }
Once the job has been submitted, the database will be shut down and remounted to perform the backup operation. Note that this operation may take time to complete. Once the backup has been performed, the database is re-opened. The results of a backup operation are shown below. This listing includes an SPFILE, online log copies, control file and data files. Note the size of the backups – these are different from the sizes of the online data files, indicating that only used blocks have been copied.
V2: Page 416
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Open Backup - Manual
• Instance MUST be in ARCHIVELOG mode • Take copy of selected tablespaces or files • Database can be open during backup – ALTER TABLESPACE t BEGIN BACKUP; – Operating system file copy – ALTER TABLESPACE t END BACKUP; – ALTER SYSTEM SWITCH LOGFILE;
ODBA10gDB-OCA-15-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Manual Open Backup For instances running in ‘ARCHIVELOG’ mode, the preferred backup methodology is an open backup. Advantages over a closed backup include: The ability to perform the backup without disrupting database queries or updates. Improved backup speed since the backup can be selective by datafile or tablespace. To carry out an open database backup, individual tablespaces or datafiles are copied to backup media. The tablespace must be placed in backup mode. Operating system commands are then used to perform the backup operation. Once the copy is completed, the tablespace can be taken out of backup mode. Further tablespaces can then be backed up using the same mechanism. Once all required tablespaces have been copied, the logfile should be switched, in order to allow the log contents written during the backup to be archived. SQL> ALTER TABLESPACE tablespaceName BEGIN BACKUP; -- Perform operating system file copy SQL> ALTER TABLESPACE tablespaceName END BACKUP; SQL> ALTER TABLESPACE tablespaceName BEGIN BACKUP; -- Perform operating system file copy SQL> ALTER TABLESPACE tablespaceName END BACKUP; SQL> ALTER SYSTEM SWITCH LOGFILE;
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 417
Oracle 10g Database Associate
Lesson 15 – Database Backups
Open Backup – EM
• Selective or full backup • EM > Maintenance > Schedule Backup > Customized Backup > Backup Type • Select required option • Add required tablespaces, datafiles, logs • Schedule/execute backup as required
ODBA10gDB-OCA-15-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Open Database Backup Open database backups can also be performed using EM. Backups can be for the entire database or selective by tablespace or data file. The required tablespaces, datafiles or logs are added to the list of items to be copied. The backup operation can then be scheduled in the same way as the full, closed backup demonstrated previously.
V2: Page 418
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Oracle-Suggested Backup
• An Oracle-suggested backup strategy can be selected and scheduled automatically • Choose disk, tape, both as a destination • Full copy performed once, followed by a daily incremental backup • Start date/time entered (2:00am default) • Backup job added to scheduler for regular execution ODBA10gDB-OCA-15-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle-Suggested Backup An ‘out-of-the-box’ backup strategy is provided with Enterprise Manager that can be selected and scheduled automatically. The backup can be taken to disk, tape or both. The default location is the flash recovery area but this can be overridden by changing the defaults via Maintenance > Backup Settings. The strategy performs an initial full database copy, followed by a daily incremental backup to the selected area. The backup job is scheduled by default for 2:00am daily, but this time can be overridden for all (or an individual) execution. The scheduled job is resubmitted for automatic execution on a daily basis.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 419
Oracle 10g Database Associate
Lesson 15 – Database Backups
Oracle-Suggested Backup: Review
ODBA10gDB-OCA-15-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle-Suggested Backup: Review Before the job is submitted, a review screen is displayed for confirmation of the selected options. The default settings are:
The ‘RMAN’ script that will be used is: Daily Script: run { allocate channel oem_disk_backup device type disk; recover copy of database with tag 'ORA$OEM_LEVEL_0'; backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA$OEM_LEVEL_0' database; }
V2: Page 420
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Control File Backup – Manual
• Control file backups – Binary – recover from dropped files/spaces – ALTER DATABASE BACKUP CONTROLFILE TO 'path'
– Text – recover from loss of all controlfiles – ALTER DATABASE BACKUP CONTROLFILE TO TRACE
ODBA10gDB-OCA-15-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Manual Control File Backup Control files can be backed up using one of two methods, as outlined below.
• Binary controlfile backup SQL> ALTER DATABASE BACKUP CONTROLFILE TO 'path';
This command creates a binary copy of the controlfile in the specified location. The binary backup can be used as a recovery start point in the event that a datafile or tablespace is inadvertently dropped (DBA user error)
• Text controlfile backup SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command creates a text file in the USER_DUMP_DEST location. The script file contains in addition the necessary startup commands to be used if all copies of the current control file are lost or damaged.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 421
Oracle 10g Database Associate
Lesson 15 – Database Backups
Control File Text Backup
Dump file c:\oracle\admin\v10g\udump\v10g_ora_2552.trc Mon Jan 01 09:48:58 2007 ORACLE V10.2.0.1.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Windows XP Version V5.1 Service Pack 1 CPU : 1 - type 586 Process Affinity : 0x00000000 Memory (Avail/Total): Ph:274M/1022M, Ph+PgF:932M/1693M, VA:1554M/2047M Instance name: v10g Redo thread mounted by this instance: 1 Oracle process number: 18 Windows thread id: 2552, image: ORACLE.EXE (SHAD) *** SERVICE NAME:(SYS$USERS) 2007-01-01 09:48:58.609 *** SESSION ID:(155.3) 2007-01-01 09:48:58.609 *** 2007-01-01 09:48:58.609 -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. --- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' --- LOG_ARCHIVE_FORMAT=ARC%S_%R.%T --- DB_UNIQUE_NAME="v10g" --- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS -- FAL_CLIENT='' -- FAL_SERVER='' --- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_10=ENABLE --- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file.
V2: Page 422
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
--Set #1. NORESETLOGS case --- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "V10G" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 'C:\ORACLE\ORADATA\V10G\REDO01.LOG' SIZE 50M, GROUP 2 'C:\ORACLE\ORADATA\V10G\REDO02.LOG' SIZE 50M, GROUP 3 'C:\ORACLE\ORADATA\V10G\REDO03.LOG' SIZE 50M -- STANDBY LOGFILE DATAFILE 'C:\ORACLE\ORADATA\V10G\SYSTEM01.DBF', 'C:\ORACLE\ORADATA\V10G\UNDOTBS01.DBF', 'C:\ORACLE\ORADATA\V10G\SYSAUX01.DBF', 'C:\ORACLE\ORADATA\V10G\USERS01.DBF', 'C:\ORACLE\ORADATA\V10G\EXAMPLE01.DBF' CHARACTER SET WE8MSWIN1252 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\FLASH_RECOVERY_AREA\V10G\ARCHIVELOG\2005_09_29\O1_MF_1_1_%U_.ARC'; -- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\FLASH_RECOVERY_AREA\V10G\ARCHIVELOG\2005_09_29\O1_MF_1_1_%U_.ARC'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\V10G\TEMP01.DBF' SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 423
Oracle 10g Database Associate
Lesson 15 – Database Backups
--Set #2. RESETLOGS case --- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "V10G" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 'C:\ORACLE\ORADATA\V10G\REDO01.LOG' SIZE 50M, GROUP 2 'C:\ORACLE\ORADATA\V10G\REDO02.LOG' SIZE 50M, GROUP 3 'C:\ORACLE\ORADATA\V10G\REDO03.LOG' SIZE 50M -- STANDBY LOGFILE DATAFILE 'C:\ORACLE\ORADATA\V10G\SYSTEM01.DBF', 'C:\ORACLE\ORADATA\V10G\UNDOTBS01.DBF', 'C:\ORACLE\ORADATA\V10G\SYSAUX01.DBF', 'C:\ORACLE\ORADATA\V10G\USERS01.DBF', 'C:\ORACLE\ORADATA\V10G\EXAMPLE01.DBF' CHARACTER SET WE8MSWIN1252 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\FLASH_RECOVERY_AREA\V10G\ARCHIVELOG\2005_09_29\O1_MF_1_1_%U_.ARC'; -- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\FLASH_RECOVERY_AREA\V10G\ARCHIVELOG\2005_09_29\O1_MF_1_1_%U_.ARC'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\V10G\TEMP01.DBF' SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. --
V2: Page 424
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Image Copies
• Bit by bit copy of data, redo log, control files • Generated with – RMAN 'backup as copy'(recorded in catalog) or – operating system commands
• Created on disk only (not tape) • RMAN checks for corrupted blocks during copy operation ODBA10gDB-OCA-15-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Image Copies An ‘image copy’ is a bit-by-bit copy of a source file. Image copies can be created for data files, redo log files or control files. Image copies are created with the RMAN ‘backup as copy’ command or an operating system copy command. If the RMAN command is used, an entry is also made in the RMAN catalog, to allow the image copy to be used during a subsequent restore operation, If an operating system command is used to perform a manual image copy, the RMAN ‘catalog’ command can be used to add the image copy information to an RMAN catalog, to allow RMAN to use the manual backup if a recovery operation is required. Image copies can only be created on disk. The resulting files must be copied to removeable media manually if required – this operation is not controlled by Oracle. If the RMAN utility is used to create image copies, blocks are checked for corruption and flagged during the backup operation.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 425
Oracle 10g Database Associate
Lesson 15 – Database Backups
Backup Sets
• Create backup sets with RMAN command backup as backupset' ' • One or more datafiles, control, SPFILE & archived redo log files • Can be split into ' backup pieces'by using ' MAXPIECESIZE'option during backup • Can use ' unused block compression; • Can be compressed using RMAN command ' backup as compressed backupset' ODBA10gDB-OCA-15-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Backup Sets A backup set is one (or more) operating system files that can contain data from multiple Oracle files. A backupset can be created with the RMAN command ‘backup as backupset; for example RMAN> BACKUP AS BACKUPSET DATABASE;
The files in a backup set can include data, control and spfiles. A backup set can also contain multiple archivelogs. Archivelogs and data files cannot be resident in the same backupset. A backup set can be split into ‘backup pieces’, to circumvent operating system or media file sizes. The size of a piece is set by specifying the ‘MAXPIECESIZE’ option during the backup operation. Backupsets can be written to disk or tape. Two compression methods can be selected during creation of a backup set. ‘Unused block compression’ skips blocks that have never been used, reducing the size of the resulting backup set. The command ‘backup as compressed backupset’ uses a proprietary Oracle compression algorithm, tailored for the contents of datafiles and archived log files, to further reduce the size of the backup set on disk or tape.
V2: Page 426
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Monitoring Flash Recovery Area
• FRA used up to the space assigned • When FRA is 85%/97% full, messages are written to alert log • Use v$recovery_file_dest view to monitor FRA status SELECT SELECT name, name, space_limit/(1024*1024*1024) space_limit/(1024*1024*1024) quota, quota, space_used/(1024*1024*1024) space_used/(1024*1024*1024) used, used, space_reclaimable/(1024*1024*1024) space_reclaimable/(1024*1024*1024) reclaimable, reclaimable, number_of_files number_of_files files, files, (space_limit (space_limit -- space_used)/space_limit space_used)/space_limit percent percent FROM FROM v$recovery_file_dest v$recovery_file_dest
ODBA10gDB-OCA-15-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Monitoring Flash Recovery Area The FRA size must be set (parameter ‘db_recovery_file_dest_size’) before setting the location (parameter ‘db_recovery_file_dest’). The FRA should be large enough to hold at least one complete copy of the entire database, together with any incremental backups and archived log files retained online. In automatic mode, Oracle will delete files that are obsolete if there is insufficient space for further backups. In addition, warnings are written to the alert log when the flash recovery area reaches 85% full. A critical message is written to the alert log when the flash recovery area reaches 97% full. The FRA can be monitored manually, using the ‘V$RECOVERY_FILE_DEST’ dictionary table. SQL> SELECT name, space_limit/(1024*1024*1024) 2 space_used/(1024*1024*1024) 3 space_reclaimable/(1024*1024*1024) 4 number_of_files 5 (space_limit - space_used)/space_limit 6* FROM v$recovery_file_dest;
quota, used, reclaimable, files, percent
NAME QUOTA USED RECLAIMABLE FILES PERCENT ------------------------------ ----- ------ ----------- ------ ------C:\oracle/flash_recovery_area 2 0.02 0.0033 4 0.9901
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 427
Oracle 10g Database Associate
Lesson 15 – Database Backups
Summary
• In this lesson, you should have learnt to: – Perform closed/consistent database backups – Perform open data file / incremental backups – Backup control files – Use EM and RMAN for backup operations, including image copies and backup sets – Automate database backups – Monitor the Flash Recovery Area
ODBA10gDB-OCA-15-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 428
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 15 – Database Backups
Oracle 10g Database Associate
Practice 15
• Use EM to perform a full, closed backup to a newly created database and to the Flash Recovery Area • Perform an open, incremental backup on the ' USERS'and ' EXAMPLE'tablespaces to the FRA • Check the status of the FRA using EM and the appropriate data dictionary view ODBA10gDB-OCA-15-14
Copyright Jeremy Russell & Associates, 2005. All rights reserved.
Practice 15 In this practice, you will perform a range of backup operations. Use EM to perform a full, closed backup. You should follow the procedures outlined in this lesson. You should create a new OS directory to store the backup. Repeat this operation to the Flash Recovery Area and check the results of both backups.
________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ Perform an open, incremental backup on the ‘USERS’ and ‘EXAMPLE’ tablespaces. Write the backups to the FRA.
________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ Using Enterprise Manager, check the status of the FRA after completing the suggested operations. ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 429
Oracle 10g Database Associate
Lesson 15 – Database Backups
This page intentionally left blank
V2: Page 430
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
16 0 Database Recoveries
ODBA10gDB-OCA-16-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 16 – DATABASE RECOVERIES Lesson 16 describes and demonstrates a variety of database recovery procedures.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 431
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Objectives
• After completing this lesson, you should be able to do the following: – Restore and recover data files – Restore and recover critical data files – Restore damaged control files – Recover lost redo log files
ODBA10gDB-OCA-16-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 16 describes and demonstrates a variety of database recovery procedures. Topics include: Restoring data files from backup copies Recovering the contents of the restored file(s) from redo logs Procedured for restoring and recovering critical data files Restoring damaged control files Recovering lost redo log files For the resolution operations described in this lesson, you must connect to the database specifying an account with ‘SYSDBA’ or ‘SYSOPER’ privileges. Enterprise Manager invokes the ‘RMAN’ utility to perform manual and automated recovery tasks. Resolving a database problem generally requires two operation steps: Restore
copy the contents of damaged files from backups
Recover
use archived and online logs to roll the restored files back to a consistent state
This lesson only deals with complete recoveries. A brief overview of incomplete recoveries is discussed; further details are dealt with in a different class.
V2: Page 432
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
Recover from Cold Backup
• • • • •
Only choice for NOARCHIVELOG databases Restore all data, control and log files Restore password / parameter file No recovery of data is possible Simple procedure but – Data will be lost because – Database is restored as at last backup
ODBA10gDB-OCA-16-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Recover from Cold Backup For a database running in ‘NOARCHIVELOG’ mode, the only method for resetting the database to a usable state, in the event of a media problem, is a full, cold restore. Data will be lost during this operation – any updates made since the backup was taken will NOT be recovered. To perform a cold restore, shut down the instance, if it is not already shut down. The fastest method of shutting down – in this situation only – is to perform a ‘SHUTDOWN ABORT’. Using operating system commands, restore the backups to their original locations. If the original locations are not available, additional steps will be necessary (see page 436). A parameter file will need to be available or restored from a backup. If there is no backup available, a skeleton parameter file can be created manually using a text editor. A password file will need to be available or restored from a backup. If there is no password file available, the parameter file may need to be modified to set ‘REMOTE_LOGIN_PASSWORDFILE = none’ to allow the database to be started by an OS privileged user. After restoring all files, startup the instance and open the database.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 433
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Recover from Hot Backup
• • • • • • •
Database can be open for restore/recover DB must be mounted for ' critical files' Only damaged files need to be restored DO NOT restore control files Recovers all data to failure point Requires all archived logs since backup May take longer than cold restore
ODBA10gDB-OCA-16-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Recover from Hot Backup A hot restore of non-critical tablespaces or datafiles for an instance in ‘ARCHIVELOG’ database can be performed whilst the database remains open. The damaged tablespace must be taken offline for the recovery operation Critical files, either part of the ‘SYSTEM’ tablespace or a datafile containing active undo segments, cannot be recovered while the database is open..These critical tablespaces cannot be taken offline, therefore the database must be closed and remounted for the recovery.
• Open database restore/recover If the database remains open following the failure, the failing file is not a critical file and the instance is running in ‘ARCHIVELOG’ mode, the restore and recovery can be carried without shutting the database down.
V2: Page 434
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
Determine the file(s) involved from the available information (consult the dictionary tables ‘V$DATAFILE’ and ‘V$DATAFILE_HEADER’). If the damaged file is not already offline (V$DATAFILE_HEADER.STATUS), take it offline using the command:
SQL> ALTER DATABASE DATAFILE 'fullpathname' OFFLINE; Using the appropriate operating system commands, restore the last good backup of the failed file(s) over their original location. Issue the following command to recover the contents from the archived logs: SQL> RECOVER DATAFILE 'fullpathname' #or SQL> RECOVER TABLESPACE tablespaceName
• Closed database restore/recover If the database has closed as a result of the failure, and the failing file is not critical to operation, mount the database using the ‘STARTUP MOUNT’ command and take the file offline. Open the database and perform the same recovery as above. SQL> STARTUP MOUNT; SQL> ALTER DATABASE DATAFILE 'filename' OFFLINE; SQL> ALTER DATABASE OPEN; SQL> RECOVER TABLESPACE tablespace SQL> ALTER TABLESPACE tablespace ONLINE;
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 435
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Rename Data File
• CONNECT user/pwd AS SYSDBA • STARTUP MOUNT PFILE=pfile • OS copy file to newPath • ALTER DATABASE RENAME FILE 'currentPath' TO 'newPath'; • ALTER DATABASE OPEN
ODBA10gDB-OCA-16-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Rename Data File If the failure is more serious, for example the entire disk unit/controller is offline, a backup of a failed file can be restored to an alternate location before recovery. Startup and mount the database, then copy the last good backup of the damaged file to the required location. The copy MUST be in place before changing the database control file’s record of the file name. Use the ‘ALTER DATABASE RENAME FILE’ command to change the file name in the control file, then proceed as on the previous page to offline the file/tablespace, open the database if required, and recover the file/tablespace.
SQL> ALTER DATABASE RENAME FILE 2> '/d2/oracle/data01.dbf' TO '/d3/oracle/data01.dbf';
V2: Page 436
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
This page intentionally left blank
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 437
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Complete Recovery Procedures • ALTER DATABASE RECOVER DATABASE; • RECOVER [AUTOMATIC] DATABASE • ALTER DATABASE RECOVER DATAFILE 'path'; • RECOVER [AUTOMATIC] DATAFILE 'path' • Specify log: (=suggested | filename | AUTO | CANCEL)
ODBA10gDB-OCA-16-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Complete Recovery Procedures Depending on the damaged file or files, one of the following commands will be used to recover the damaged area after restoring a good copy of the file(s).
SQL> [ALTER DATABASE] [AUTOMATIC] RECOVER DATABASE [;] SQL> [ALTER DATABASE] [AUTOMATIC] RECOVER DATAFILE 'path' [;] SQL> [ALTER DATABASE] [AUTOMATIC] RECOVER TABLESPACE tablespaceName [;]
Prefixing a command with ‘ALTER DATABASE’, to make the command a SQL command is NOT recommended since it suppresses certain error messages displayed during recovery. The ‘AUTOMATIC’ keyword assumes that all required archive logs are in place and simply applies the logs unless one is missing or an error occurs. Use the statement ‘SET AUTORECOVERY ON’ before entering the RECOVER command to make the same assumption.
V2: Page 438
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
If automatic recovery is not performed, the following message(s) will be displayed by the recovery process.
ORA-00279: change 12345...15/01/03 12:00:00 needed for thread 1 ORA-00289: suggestion: /usr/disk2/archives/arch_345.rdo ORA-00280: change 12345 for thread #1 is in sequence #345 Specify log: (=suggested | filename | AUTO | CANCEL)
Hitting will accept the suggested filename (‘arch_345.rdo’) and proceed to the next log file, when the message will be repeated. Typing an alternate file name will direct Oracle to a different location. Typing ‘AUTO’ advises Oracle that all files are in place and the recovery can continue automatically. Typing ‘CANCEL’ indicates that there is a missing or damaged archive log file and that recovery cannot complete normally. When recovery has been successfully processed, the message ‘Media Recovery Complete’ is displayed.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 439
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
EM Tablespace Recovery
• EM > Maintenance > Perform Recovery • Object Level Recovery > Object Type Tablespaces > Perform Object Level Recovery • Select Tablespace(s) to be recovered • RMAN restores the selected backup and recovers the contents automatically
ODBA10gDB-OCA-16-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Tablespace Recovery On the EM Maintenance page, select Perform Recovery. In the Object Level Recovery section, select the object type (tablespace, datafile) to be repaired. Select the objects to be repaired and allow RMAN to recover the contents.
V2: Page 440
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
Recover Control File
• If single copy of multiplexed control file is damaged, database shuts down immediately • Copy a good file over the damaged file or remove the damaged file from the PFILE • If all control files are damaged, recover from trace file copy of control file SHUTDOWN SHUTDOWN NORMAL NORMAL || IMMEDIATE IMMEDIATE || TRANSACTIONAL TRANSACTIONAL @$BACKGROUND_DUMP_DEST/con.sql @$BACKGROUND_DUMP_DEST/con.sql
ODBA10gDB-OCA-16-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Recover Control File If any copy of a control file is damaged, the database will typically shut down immediately. To resolve the problem, use OS commands to copy a good control file over the damaged copy. Alternatively, remove the reference to the damaged control file from the parameter file (‘PFILE’). The database will then need to be restarted using the PFILE and a new SPFILE created. If all copies of the control file(s) become unusable (unlikely if sufficient mirrored versions are maintained), the text version of the control file can be used to recreate a good copy. Using the script created earlier, the DBA can STARTUP NOMOUNT the instance and execute the script. The control file is recreated from the datafile and tablespace information recorded in the script; the status of each object is taken from the object itself. The procedure ends with ‘ALTER DATABASE OPEN RESETLOGS’ and therefore the instance should be closed and a full backup made, as before.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 441
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Log File Recovery
• ALTER DATABASE CLEAR UNARCHIVED LOG; • ALTER DATABASE CLEAR UNARCHIVED LOG GROUP n UNRECOVERABLE DATAFILE; • SHUTDOWN AND TAKE COLD BACKUP
ODBA10gDB-OCA-16-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Log File Recovery If one member of a multiplexed log file is damaged, the log file can be DROPPED using ‘ALTER The other members of the group will be used and no effect will be noticed by database users. DATABASE DROP LOGFILE’.
If an entire group is damaged, the group can be dropped. The database should be halted and backed up for security before recreating the damaged log files. If the database cannot be stopped, the log file can be cleared using one of the following commands:
SQL> ALTER DATABASE CLEAR UNARCHIVED LOG; SQL> ALTER DATABASE CLEAR UNARCHIVED LOG GROUP n 2 UNRECOVERABLE DATAFILE;
At the earliest opportunity, you should close the database and take a full, cold backup. If the database suffers another problem before a backup is taken, the data may not be recoverable since an intact log stream will not be available.
V2: Page 442
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
Incomplete Recovery Overview
• Required when an archive log is missing or complete recovery can' t be performed • Incomplete recovery means data loss • All data files must be restored first • Recovery with instance mounted (closed) • Full backup suggested after recovery ends
ODBA10gDB-OCA-16-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Incomplete Recovery Overview During recovery, archived logs cannot be skipped. If there is a missing archive log file that cannot therefore be processed, recovery cannot complete because there may be changes in later logs that are dependent on the missing archive. An ‘incomplete recovery’ procedure must therefore be carried, to correctly synchronise ALL data files with the control file. To perform an incomplete recovery, ALL data files must be restored i.e. INCOMPLETE recovery means a FULL restore. This must be carried out with the database instance mounted and the database closed. Once all data files have been restored, the instance can be mounted (NOT opened) and a ‘RECOVER DATABASE UNTIL CANCEL’ command issued. Recovery will proceed as described earlier, with each logfile being prompted for in sequence. When the missing archive log is requested, respond ‘CANCEL’ to the prompt and recovery will halt.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 443
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
After recovering as much as possible, the database must be opened with the command:
SQL> ALTER DATABASE OPEN RESETLOGS;
This variant of the OPEN command will resync the control file with the current state of the data files and reset the current log number back to 1. The procedure creates a new incarnation of the database. All preceding archived logs are unusable, since they refer to the old incarnation. The instance should immediately be closed and a full backup (data files, log files, control files) taken. This new backup becomes the start point for any succeeding recoveries that may be necessary. If there are archived log copies, these should be destroyed to avoid confusion between archives from the old and new incarnations. The new logs will have the same naming sequence as the old logs and confusion when a further database problem arises makes the next procedure liable to more complex errors.
V2: Page 444
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 16 – Database Recoveries
Oracle 10g Database Associate
Summary
• In this lesson, you should have learnt to: – Restore and recover data files – Restore and recover critical data files – Restore damaged control files – Recover lost redo log files
ODBA10gDB-OCA-16-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 445
Oracle 10g Database Associate
Lesson 16 – Database Recoveries
Practice 16
• Create a new USERS2 tablespace • Perform a backup of the new tablespace • Connect as SYSTEM and copy the EMP table from SCOTT into USERS2 • Restore and recover the USERS2 tablespace • Confirm that the SYSTEM.EMP2 table is still in the database ODBA10gDB-OCA-16-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 16 In this practice, you will perform a range of recovery operations. Use EM to create a new USERS2 tablespace. Perform a backup of the new tablespace
________________________________________________________________________ ________________________________________________________________________ Run any SQL interface tool, connect as ‘SYSTEM’ and copy the ‘EMP’ table from the ‘SCOTT’ schema to the new tablespace CREATE TABLE emp2 TABLESPACE users2 AS SELECT * FROM scott.emp;
________________________________________________________________________ ________________________________________________________________________ Your instructor will advise you on how to ‘damage’ the USERS2 tablespace. Instructions will vary depending on the operating system being used for this class. ________________________________________________________________________ ________________________________________________________________________ Restore and recover the USERS2 tablespace, without shutting down the database. ________________________________________________________________________ ________________________________________________________________________ Confirm that the EMP2 table is still accessible and contains 14 rows. ________________________________________________________________________
V2: Page 446
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
17 0 Lock Management
ODBA10gDB-OCA-17-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 17 – LOCK MANAGEMENT Lesson 17 explores locks and latches that are used by Oracle to ensure that access to resources including tables, rows and memory structures are organised to prevent duplicated or lost updates.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 447
Oracle 10g Database Associate
Lesson 17 – Lock Management
Objectives
• After completing this lesson, you should be able to : – Detect and resolve lock conflicts – Manage deadlocks – Understand transaction and lock interaction – Comprehend Oracle 10g lock modes
ODBA10gDB-OCA-17-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives In Lesson 17, you will explore locks and latches that are used by Oracle to ensure that access to resources including tables, rows and memory structures are organised to prevent duplicated or lost updates. Topics include: Basic Oracle locking principles Management of deadlocks Transaction and locking interaction Oracle 10g lock modes
V2: Page 448
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
Locking Overview
• Locks prevent simultaneous access to a resource (table, row, shared memory) • Most locking requirements are automatic • Oracle never blocks a reading process • Latches • Deadlocks may occur between transactions
ODBA10gDB-OCA-17-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Locking Overview Oracle uses locks to serialise requests for schema objects and rows by SQL and PL/SQL statements. This locking is usually managed automatically. Multiple concurrent requests for access to a database object are queued and actioned in a series. Reading processes do not block, not are they blocked by, other processes. Writing processes that require exclusive access to a resource will wait until all current users have completed pending updates before the next process gains access to the object.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 449
Oracle 10g Database Associate
Lesson 17 – Lock Management
Lock Types
• DML Locks – prevent DML/DDL conflicts – Table-Level : prevents concurrent DDL operations – Row-Level : locks rollback segment; prevents concurrent DML operations
• DDL Locks – protects a schema object – Shared – used by CREATE PROCEDURE – Exclusive – used by CREATE/ALTER/DROP
ODBA10gDB-OCA-17-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Lock Types
DML Locks These locks provide guaranteed data integrity in a multi-user environment, by preventing conflicting operations from interfering with each other’s updates.
Table-Level Locks are created for DML operations that modify the content of an object. Explicit locks are prevented from being acquired by other processes when a table-level lock is held. Row-Level Locks are designed to prevent concurrent DML operations from the ‘missing update’ syndrome, due to the timing of two near-concurrent DML activities.
• DDL Locks A DDL (dictionary) lock is used to protect schema objects in the process of being changed.
Shared DDL Locks are used by statements that reference permenant objects, including, ‘CREATE [OR REPLACE] [PROCEDURE | FUNCTION | PACKAGE [BODY]]’ statements that require a consistent view of any referenced objects. Other statements include ‘AUDIT’, ‘COMMENT’ and ‘CREATE SYNONYM’. Exclusive Locks are acquired by DDL operations, including index statements, tablespace statements and ‘[ALTER | DROP] TABLE’ statements.
V2: Page 450
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
Locking Issues
• Incorrect locking levels – Developers should not code exclusive locks
• Incorrect transaction style – Long running transactions must be checked
• Incorrect user behaviour – Users must be educated to commit often
• Non-Oracle product interference – Generic software may be badly written ODBA10gDB-OCA-17-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Locking Issues Contention caused by locking typically arises from any of the following causes: Incorrect locking levels Programmers may have written explicit locking statements into the code, which are then holding schema objects longer than necessary. In particular, consideration should be given to use of the ‘SELECT … FOR UPDATE’ statement. Incorrect transaction style Transactions may not be used properly by your applications. statement may be holding locks on schema objects for too long.
Insufficient use of the ‘COMMIT’
Incorrect user behaviour Users may require education and training in order to appreciate the implications of not completing transactions in a timely fashion. Non-Oracle product interference A common problem with multi-RDBMS software that is deployed on an Oracle system is that the lowest common denominator mechanism may have been used by developers. Some customisation of this generic style of software may be necessary to optimise performance for the Oracle10g product.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 451
Oracle 10g Database Associate
Lesson 17 – Lock Management
Locking Diagnostics
• Locked objects – V$LOCK SELECT o.owner, o.object_id, o.object_name, o.object_type, l.type FROM dba_objects o, v$lock l WHERE o.object_id = l.id1;
• Waiting sessions - DBA_WAITERS SELECT waiting_session, holding_session, lock_type, mode_held, mode_requested, lock_id1, lock_id2 FROM dba_waiters
ODBA10gDB-OCA-17-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Locking Diagnostics
• List currently locked objects This SQL statement shows (‘Ch17LockedObjects.sql’): SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
currently
locked
objects
in
the
running
instance
SELECT o.owner, o.object_id, o.object_name, o.object_type, DECODE (l.type, 'RS', 'ROW SHARE', 'MR', 'MEDIA RECOV', 'TM', 'DML ENQUEUE', 'TS', 'TEMP SEG', l.type) type, DECODE (l.lmode, 1, 'NULL', 2, 'ROW-S', 3, 'ROW-X', 4, 'SHARE', 5, 'SROWX', 6, 'EXCL.', l.type) lmode FROM dba_objects o, v$lock l WHERE o.object_id = l.id1 AND owner != 'SYS';
OBJECT OWNER ID OBJECT_NAME ---------- ---------- -------------------SCOTT 51151 EMP
V2: Page 452
OBJECT TYPE ----------TABLE
LOCK TYPE ----------DML ENQUEUE
LOCK MODE ----ROW-X
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
• List Waiting Sessions When a process is (‘Ch17Waiters.sql’)::
locked,
the
‘DBA_WAITERS’
view
shows
the
blocking
process
SQL> SELECT 2 * 3 FROM 4 dba_waiters 5 ; WAITING HOLDING LOCK SESSION SESSION TYPE ---------- ---------- -----------140 142 DML
Copyright © 2007 Jeremy Russell & Associates Ltd.
MODE HELD ---------Row-X (SX)
MODE LOCK LOCK REQUESTED ID1 ID2 ---------- ---------- ---------Exclusive 51151 0
V2: Page 453
Oracle 10g Database Associate
Lesson 17 – Lock Management
EM Blocking Sessions
ODBA10gDB-OCA--17-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Blocking Sessions This Enterprise Manager page displays a tree structured view of blocked and blocking sessions. This hierarchical representation of offending sessions also provides an easy way to determine the user who is blocking and then kill the session. To display the blocking sessions page, select EM > Performance – scroll to the end of the page (to Additional Monitoring Links) and select ‘Blocking Sessions’. The blocking sessions page (see slide) is displayed. To kill (terminate) the blocking session, select the corresponding radio button, then click the ‘Kill Session’ button.
page 456).
V2: Page 454
This can also be performed using SQL statements (see
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
EM Hang Analysis
ODBA10gDB-OCA--17-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Hang Analysis From the EM Performance page, select the Additional Monitoring Links > Hang Analysis link. When EM detects a block, the page is refreshed – note that the information may not be displayed immediately. This page shows a hierarchical display of blocking and blocked sessions. Each session is colour coded to indicate the length of time that the session has been blocked. Sessions are displayed in green, yellow and red, with each successive colour indicating a longer blocked/blocking period. More information about any indicated session can be displayed by clicking on the coloured block. Click ‘View Session Details’ to display full details on the selected session.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 455
Oracle 10g Database Associate
Lesson 17 – Lock Management
Manual Lock Resolution
• Determine sessions holding locks SELECT sid, serial#, username, program FROM v$session WHERE sid in ( SELECT holding_session FROM dba_blockers);
• Abort the session holding locks ALTER SYSTEM KILL SESSION 'sid, serial';
ODBA10gDB-OCA--17-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Resolving Locking Issues
• Determine sessions holding locks The ‘DBA_BLOCKERS’ view lists processes that are waiting on another process to release a lock. SQL> SELECT 2 sid, serial#, username, program FROM v$session 3 WHERE 4 sid in ( 5 SELECT holding_session FROM dba_blockers); USER SID SERIAL# NAME PROGRAM ---------- ---------- --------------- -----------------------------142 8687 SCOTT sqlplus.exe
• Abort the session holding locks Once the blocking session has been identified (session 142 in the example above), this session can be terminated as below, using the ‘ALTER SYSTEM KILL SESSION’ command: SQL> ALTER SYSTEM KILL SESSION ’142, 8687’; System altered.
V2: Page 456
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
Deadlocks
• Deadlock occurs when two sessions are wait for each other to release resources • Oracle detects and resolves a deadlock automatically • The ‘victim’ statement is killed • Neither session is terminated completely • DBA investigation is required to resolve the programming error ODBA10gDB-OCA--17-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Deadlocks A deadlock occurs when two concurrent sessions attempt to lock resources (rows, tables or other objects) using an inappropriate sequence. For example: At time t1, Session 1 locks resource 1 – lock is acquired successfully At time t2, Session 2 locks resource 2 – lock is acquired successfully At time t3, Session 1 attempts to lock resource 2 – session 1 waits since the resource is already locked At time t4, Session 2 attempts to lock resource 1 – session 1 already has this locked and is waiting for Session 2 to release resource 2. If session 2 also waited, the resulting impasse would cause both sessions to wait, effectively forever. Oracle will automatically detect the potential deadlock and terminate session 1’s statement, although it was session 2 that finally caused the potential deadlock to occur. The session will receive an ORA00060 error (deadlock detected while waiting for resource). The session is not terminated entirely, therefore other locks are no released. The session should be manually rolled back and restarted. If the interrupted statement is immediately reissued, the other session will then detect a deadlock and receive the ORA-00060 error in turn. An investigation into the underlying cause of the problem is recommended.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 457
Oracle 10g Database Associate
Lesson 17 – Lock Management
Dictionary Information V$LOCK
Lists locks currently held and outstanding requests for a lock or latch
DBA_BLOCKERS
Lists sessions holding lock for which others are waiting
DBA_WAITERS
Displays waiting sessions
DBA_DDL_LOCKS
DDL Locks and pending requests
DBA_DML_LOCKS
DML locks and pending requests
DBA_LOCK
All locks and pending requests
DBA_LOCK_INTERNAL
All internal locks and pending requests
ODBA10gDB-OCA--17-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Dictionary Information
V$LOCK Name Null? ----------------------------------------- -------ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
Type ---------------------------RAW(4) RAW(4) NUMBER VARCHAR2(2) NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER
DBA_BLOCKERS Name Null? Type ----------------------------------------- -------- ---------------------------HOLDING_SESSION NUMBER
DBA_WAITERS Name Null? ----------------------------------------- -------WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
V2: Page 458
Type ---------------------------NUMBER NUMBER VARCHAR2(26) VARCHAR2(40) VARCHAR2(40) NUMBER NUMBER
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
DBA_DDL_LOCKS Name Null? ----------------------------------------- -------SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
Type ---------------------------NUMBER VARCHAR2(30) VARCHAR2(30) VARCHAR2(40) VARCHAR2(9) VARCHAR2(9)
DBA_DML_LOCKS Name ----------------------------------------SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTED LAST_CONVERT BLOCKING_OTHERS
Null? Type -------- ---------------------------NUMBER NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(30) VARCHAR2(13) VARCHAR2(13) NUMBER VARCHAR2(40)
DBA_LOCK Name Null? ----------------------------------------- -------SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
Type ---------------------------NUMBER VARCHAR2(26) VARCHAR2(40) VARCHAR2(40) VARCHAR2(40) VARCHAR2(40) NUMBER VARCHAR2(40)
DBA_LOCK_INTERNAL Name Null? ----------------------------------------- -------SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
Copyright © 2007 Jeremy Russell & Associates Ltd.
Type ---------------------------NUMBER VARCHAR2(56) VARCHAR2(40) VARCHAR2(40) VARCHAR2(1130) VARCHAR2(40)
V2: Page 459
Oracle 10g Database Associate
Lesson 17 – Lock Management
Summary
• In this lesson, you have learnt about : – Oracle' s automatic locking mechanisms – Detecting and resolving lock conflicts – Managing deadlocks – Transaction and lock interaction – Oracle 10g lock modes
ODBA10gDB-OCA--17-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 460
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 17 – Lock Management
Oracle 10g Database Associate
Practice 17
• Open 3 SQL*Plus sessions as SCOTT • Lock a row using the SQL command:
UPDATE emp SET sal = sal WHERE empno=7839
• Try the same command in all sessions • Using EM, explore the blocked processes • Open another SQL*Plus session and query the dictionary tables for locking • Use EM to kill the locking session • Experiment with deadlocks as described Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA--17-12
Practice 17 In this practice, you will deliberately create blocking and deadlock situations. Open three SQL*Plus sessions and connect as the user SCOTT.
________________________________________________________________________ In one session, lock a row using ‘UPDATE emp SET sal = sal (Ch17LockEmp7839.sql).
WHERE
empno=7839’
________________________________________________________________________ Reissue the same command in the other two sessions. Note that sessions 2 and 3 hang.
________________________________________________________________________ Use Enterprise Manager and the dictionary tables to explore the locking situation.
________________________________________________________________________ Kill the blocking sessions using Enterprise Manager.
________________________________________________________________________ Reconnect two sessions as SCOTT. Issue the following commands, in the order shown: Session Session Session Session
1 2 1 2
: : : :
update update update update
emp emp emp emp
set set set set
sal sal sal sal
= = = =
sal sal sal sal
where where where where
empno empno empno empno
= = = =
7900; 7902; 7902; 7900;
(Ch17LockEmpA.sql) (Ch17LockEmpB.sql) (Ch17LockEmpB.sql) (Ch17LockEmpA.sql)
What happens to both sessions when the deadlock situation is detected?
________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 461
Oracle 10g Database Associate
Lesson 17 – Lock Management
This page intentionally left blank
V2: Page 462
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
18 0 Automatic Maintenance
ODBA10gDB-OCA-18-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 18 – AUTOMATIC MAINTENANCE Lesson 18 describes the automatic maintenance features of Oracle 10g, designed to reduced the workload of the database administrator.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 463
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Objectives
• After completing this lesson, you should have an understanding of : – Automatic Maintenance Features – Workload Repository (AWR) – Database Diagnostic Monitor (ADDM) – Active Session History (ASH) – Alerts, Thresholds and Metrics – How Oracle Automates Maintenance
ODBA10gDB-OCA-18-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives In this lesson, you will experiment with the automatic features installed in the Oracle 10g database for maintaining the database contents. Topics discussed include: Automatic Workload Repository (AWR) Automatic Database Diagnostic Monitor (ADDM) Active Session History (ASH) Alerts, thresholds and metrics An introduction to the Job Scheduler and using the Scheduler to execute jobs at off-peak periods.
V2: Page 464
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Automatic Maintenance Features
• Automatic Workload Repository – Storage in SYSAUX for regular snapshots
• Automatic Database Diagnostic Monitor – Regular analysis of stored snapshots
• Thresholds and Metrics – Limits for wide range of statistics
• Automating maintenance tasks – Scheduled system and manual jobs ODBA10gDB-OCA-18-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Automatic Maintenance Features
• Automatic Workload Repository The Automatic Workload Repository (AWR) receives and stores snapshots of important statistics and workloads in the database. A snapshot is taken every sixty minutes by default. The snapshot information is stored for seven days. Both the sampling interval and the retention period can be altered.
• Automatic Database Diagnostic Monitor The Automatic Database Diagnostic Monitor (ADDM) is invoked after each snapshot, to analyse the performance of the instance over the snapshot period. Findings (notification of performance issues) are presented on the Enterprise Manager Database Control home page, together with recommendations for resolving any detected problems.
• Thresholds and Metrics Metrics are the statistics measured by Oracle during normal operation. Thresholds are the boundary limits that, when breached, indicate that the system is operating in an adverse way.
• Automating Maintenance Tasks Oracle 10g includes a Job Scheduler component, which executes jobs according to a variety of possible timetables. New Oracle10g databases include several automatic jobs to maintain the database and instance performance. To view the automatic jobs, connect to EM with “SYSDBA” privileges and select EM Home Page > Administration > Jobs.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 465
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Automatic Workload Repository
• • • •
Temporary storage assigned in SGA Stats/snapshot flushed by MMNL/MMON Snapshots stored for 7 days in AWR Collected information includes: – Time model – Object usage – Session statistics
– Network statistics – Disk I/O statistics – System statistics
• Active Session History for recent activity ODBA10gDB-OCA-18-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Automatic Workload Repository The Automatic Workload Repository (AWR) collects a series of automatic statistics describing instance performance. AWR consists of the following mechanisms: In-memory collection facility for a range of statistics, visible through V$ views. Collected statistics include – Object statistics, including access and usage information; SQL statement stats, including CPU, parse and elapsed times; Wait and timing statistics; Selected optimizer statistics for self tuning of the instance; Change metrics for selected base statistics; Active Session History (ASH) stats, representing recent session activity. V$ views include “V$SYSSTAT”, “V$SQL”, “V$OSSTAT”, “V$SEGMENT_STATISTICS”, “V$SYS_TIME_MODEL” and “V$ACTIVE_SESSION_HISTORY”.
AWR snapshots written at intervals to the SYSAUX tablespace. Tables used include “DBA_ADVISOR_*”, “DBA_HIST_*”, “DBA_FEATURE_*” and more. Primary statistics are used to derive secondary statistics (metrics) that track changes in the state of the instance. These derived metrics are used to monitor system health, detect problems and for self-tuning (e.g. reconfiguring memory usage automatically).
V2: Page 466
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
How AWR Works
• MMNL collects metrics in memory every second • MMON dumps to SYSAUX each sixty minutes • ADDM analyses snapshots and stores results in AWR • Statistics can be analysed manually as required with ADDM or SQL*Plus ODBA10gDB-OCA-18-5
SQL*Plus EM
SGA STATISTICS MMON
MMNL
ADDM
SYSAUX
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
How AWR Works Two processes, the Memory Monitor (“MMON”) and Memory Monitor Light (“MMNL”) manage the AWR. MMON collects statistics every minute for storage in the SGA in a 2MB reserved area. When this area is full, the MMNL process filters and flushes data in between full MMON flushes. MMON invokes the ADDM after each AWR run, to check for problems identified within the monitored period. MMON also monitors the “ALERT_QUE”, a queue owned by the “SYS” account, every minute, for any new information. Alerts detected by MMON are then displayed on the database home page in Enterprise Manager.
After analysing a snapshot, ADDM reports any detected issues on the Enterprise Manager home page.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 467
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Customising the AWR
• Alter Snapshot collection / retention period / Statistics collection level – EM > Administration > Automatic Workload Repository > Edit
ODBA10gDB-OCA-18-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Customising the AWR To alter the settings used by the AWR, select Enterprise Manager > Administration > Automatic Workload Repository > Edit. The following settings can be changed: Snapshot retention period
Time based retention – enter a retention period in days (default is 7 days) Retain forever – ensure that there is sufficient space in the AWR for storage
Snapshot collection interval
Set a collection period between 10 minutes and 2 hours; or turn snapshot collection off (not recommended)
Statistics collection level (init.ora ‘statistics_level’ parameter)
BASIC
: disables most statistics (not recommended)
TYPICAL
: default value – should be used
ALL
: collect additional timing and SQL statistics
The changes can also be applied using the “DBMS_WORKLOAD_REPOSITORY” package: DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(20160,30)
The first parameter is the retention period (in minutes); the second parameter is the collection interval (in minutes).
V2: Page 468
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Automatic Database Diagnostic Monitor
• • • •
Executes after each AWR snapshot Analyses recent or past snapshot delta Stores results in the AWR EM > Advisor Central > ADDM
ODBA10gDB-OCA-18-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Automatic Database Diagnostic Monitor The Automatic Database Diagnostic Monitor (ADDM) is a cost optional component that analyses AWR snapshots and recommends solutions to performance issues. Suggestions by ADDM use the Advanced Queuing (AQ) mechanism to push alerts to Enterprise Manager. ADDM analyses data collected in the Automatic Workload Repository (AWR) and identifies possible performance issues. For each of the identified areas, a basic cause is suggested and recommendations specified for correcting the problem. Setting the STATISTICS_LEVEL parameter to TYPICAL or ALL ensures that an ADDM analysis task is performed and findings and recommendations stored in the database every time an AWR snapshot is taken. ADDM executions are suspended if the parameter is set to BASIC. ADDM runs can also be executed manually. To invoke ADDM, select EM > Advisor Central > ADDM. Select the pair of snapshots from the “Run ADDM” display. A further snapshot is taken and the intervening workload is analysed.
Alternatively, a new snapshot can be written to the AWR using the Oracle supplied procedure “DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()”. When a performance spike is noted, in between regular snapshots, ADDM can be executed manually to analyse the performance since the previous snapshot. To perform a manual execution, click the “Run ADDM Now” button on the EM Performance tab. Information about findings from the most recent ADDM execution can be displayed by clicking “Findings” in the EM Home Page Diagnostic Summary.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 469
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
ADDM Goals
• Perform workload in less time • Focuses on resource bottlenecks • Uses wait model statistics to identify top problem areas • Problem areas identified include: – Memory sizing – I/O issues – Bad SQL ODBA10gDB-OCA-18-8
– Configuration issues – Excessive logons – Parsing problems
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ADDM Goals The principal goal of each “ADDM” execution is to reduce the DB time spent by the database instance. DB time is the total amount of time spent either waiting for events or executing user-level database calls for client processes. ADDM focuses on identifying resource bottlenecks that prevented the instance from executing the workload at optimum speed. By examining the CPU time taken to process SQL, PL/SQL and Java code, together with overheads including process creation and management, ADDM can determine where the system has spent unnecessary time waiting for work to complete. An ADDM run also drills down into the collected statistical information, to identify potential causes of the problems. As well as reporting symptoms, ADDM can therefore also suggest corrective actions that can alleviate the detected condition. A wide range of possible issues could be reported by ADDM, including: Memory sizing problems – incorrect apportionment between SGA areas Disk I/O issues – by comparison to expected performance values Bad SQL – identifying statements consuming an inordinately high proportion of resources Configuration issues – inappropriate parameter file settings Excessive logons – caused by poor application design Parsing problems – caused by badly written or almost duplicated SQL statements
V2: Page 470
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
ADDM Findings & Recommendations
ODBA10gDB-OCA-18-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ADDM Findings and Recommendations The ADDM analysis report includes both findings (problems) and recommendations (to resolve the reported problems):
CPU load I/O usage Resource intensive PL/SQL and Java Application issues Concurrency issues
Memory usage Resource intensive SQL RAC issues Database configuration issues Object contention
Recommendations provided may include: Hardware changes Schema changes Using other advisors
Database configuration changes Application changes
A sample performance analysis report, finding list is illustrated below:
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 471
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Active Session History
• • • • •
Immediate analysis of current sessions Samples taken per second Saved to disk every ten seconds Stored in V$_ACTIVE_SESSION_HISTORY EM Performance > Top Activity > Top Sessions
ODBA10gDB-OCA-18-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Active Session History The Active Session History (“ASH”) contains sampled information written every second, in contrast to the AWR information written hourly (by default). The information is recorded in a reserved SGA buffer – the space required is minimal and is determined in part by the number of CPU’s assigned to the instance. The minimum size is 1mb and cannot exceed 5% of the SGA size. The space used by the ASH buffers can be displayed with this query (“Ch19AshSize.sql”): SQL> SELECT * FROM v$sgastat 2 WHERE POOL = 'shared pool' 3 AND NAME LIKE 'ASH%'; POOL NAME BYTES ------------ -------------------------- ---------shared pool ASH buffers 2097152
By examining the instance constantly, the ASH mechanism provides more timely information, allowing the DBA to receive more immediate notification of problems as they occur.
V2: Page 472
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Information is also saved periodically to the AWR, to assist the ADDM. The information is saved every 10 seconds and can be displayed using the dictionary dynamic performance view V$ACTIVE_SESSION_HISTORY and the AWR view DBA_HIST_ACTIVE_SESS_HISTORY (“Ch19AshData.sql”): SQL> SELECT to_char(min(sample_time), 2 'dd-mm-yyyy hh24:mi:ss') "Oldest entry", 3 to_char(max(sample_time), 4 'dd-mm-yyyy hh24:mi:ss') "Most recent entry" 5 FROM v$active_session_history; Oldest entry Most recent entry ------------------- ------------------28-05-2007 11:22:23 29-05-2007 13:17:13 SQL> SELECT to_char(min(sample_time), 2 'dd-mm-yyyy hh24:mi:ss') "Oldest entry", 3 to_char(max(sample_time), 4 'dd-mm-yyyy hh24:mi:ss') "Most recent entry" 5 FROM dba_hist_active_sess_history; Oldest entry Most recent entry ------------------- ------------------22-05-2007 10:29:36 29-05-2007 12:43:17
The ASH contents can also be displayed graphically; select Enterprise Manager Home > Performance > Additional Monitoring Links > Top Activity > Top Sessions:
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 473
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Alerts and Thresholds
• Alert – announcement of instance problem – Default alerts include tablespace full, recovery area full, blocking sessions – Alerts displayed on EM Home Page
• Threshold – level at which alert is issued – Oracle defined measurement points – Instance / database limit can be set by DBA – Specific objects can have custom thresholds
ODBA10gDB-OCA-18-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Alerts and Thresholds
• Alerts An Oracle alert is a notification of an issue with the current instance. Alerts may be triggered by reaching a threshold level or because an event has occurred. When an alert condition occurs, Oracle create an alert on the Advanced Queuing queue “SYS.ALERT_QUE”. The alert information includes: Database entity triggering the alert
Problem Description
Suggested remedial action
Severity Level
Recommended sdvisor name to execute for more information (optional) The Enterprise Manager Database Control is automatically subscribed to “SYS.ALERT_QUE”, which displays alerts on the home page. A summary of alert types can be displayed from “V$ALERT_TYPES” (“Ch18AlertTypes.sql”): SQL> SELECT DISTINCT type, group_name, object_type 2 FROM v$alert_types 3 ORDER BY group_name; TYPE GROUP_NAME OBJECT_TYPE ---------- -------------------- -------------------Stateless Configuration FILE Stateless Configuration TABLESPACE Stateful High Availability DATABASE Stateful High Availability INSTANCE Stateless High Availability INSTANCE Stateful Performance FILE Stateful Performance SESSION Stateful Space QUOTA Stateful Space SYSTEM Stateful Space TABLESPACE
V2: Page 474
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
• Thresholds A threshold is a level at which an alert is issued. Thresholds can be set to user-defined values, for both warning and critical levels. Monitored metrics can be displayed by selecting EM Home Page > Related Links > All Metrics. Thresholds are the boundary limits that, when breached, indicate that the system is operating in an adverse way. Threshold levels for both warning and critical levels can be altered by selecting EM Home Page > Related Links > Manage Metrics.
• Clearing Alerts When an alert occurs, the Enterprise Manager Database Control displays the alert on the home page. Some alerts are cleared from this display when the condition causing the alert has been resolved. Other alerts are more persistent and will be retained for the duration of the snapshot retention period. These alerts can be cleared manually if required. From the Enterprise Manager home page, click the “Diagnostic Summary > Alert Log” link.
To remove selected alerts, click the alert’s checkbox and click the “Purge” button. To clear open alerts, select the alerts with the relevant checkbox and click the “Clear” button.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 475
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
EM Alert Display
ODBA10gDB-OCA-18-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Alert Display When either a warning or critical alert level is reached, a message is displayed on the Database Home page in the “Alerts” section. The screen dump below shows the 100% utilisation in the “FULLTABLESPACE” tablespace.
The Database Home page alert list shows the automatically triggered messages.
V2: Page 476
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Thresholds
• Monitoring levels set for selected values • EM > Related Links > Manage Metrics > Edit Thresholds
ODBA10gDB-OCA-18-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Thresholds Using EM, “threshold” levels can be set for a wide range of values that Oracle will then monitor to ensure that the threshold is not crossed, causing a potentially undesirable condition. For example, a DBCA created database is installed with a standard threshold that triggers an warning if a tablespace exceeds 85% of used space and a critical alert if the space used rises beyond 97%.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 477
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Editing Thresholds
• Set warning and critical values for required metrics • Response action is OS command or script to be executed automatically
ODBA10gDB-OCA-18-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Editing Thresholds From EM, select Related Links > Manage Metrics > Edit Thresholds to change required values. In the examples below, the “Generic Alert Log Error” metric requires character thresholds to be used to match against possible log errors. The “Blocking Session Count” metric is a numeric value which can be used to raise a warning/alert if the number of blocking sessions exceeds either of the specified levels. The optional “Response Action” is an OS script that will be run in addition to the server alert log messages in appropriate situations.
V2: Page 478
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Baseline Metrics
• Metrics derived from AWR snapshot pair • Used to set typical workload values • EM > Related Links > Metric Baselines
ODBA10gDB-OCA-18-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Baseline Metrics Baseline metrics are created from two AWR snapshots taken before and after periods of typical workload processing. Baselines are also referred to as “preserved snapshot sets”. Baselines can be created for previous periods, to be compared with new baselines against the same system. Creating a baseline changes the status of the pair of snapshots to be retained until the associated baseline is dropped. To create and manage a baseline, select EM > Administration > Automatic Workload Repository > Preserved Snapshot Sets. A baseline can also be created using SQL with the stored procedure “DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (“Ch19CreateBaseline.sql”): SQL> EXEC dbms_workload_repository.create_baseline(> start_snap_id => 85, end_snap_id => 90, > baseline_name => 'Wednesday Afternoon') ; PL/SQL procedure successfully completed.
Current baselines can be displayed from the “DBA_HIST_BASELINE” dictionary view. SQL> EXEC PRINT_TABLE('SELECT DBID BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME -----------------
* : : : : : : :
Copyright © 2007 Jeremy Russell & Associates Ltd.
FROM dba_hist_baseline') 2703147384 1 Wednesday Afternoon 85 30-MAY-07 12.00.26.285 90 30-MAY-07 17.00.22.568
V2: Page 479
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Automating Maintenance Tasks
• Managed with Oracle 10g Job Scheduler • EM (SYSDBA) > Administration > Jobs
• GATHER_STATS_JOB executed daily ODBA10gDB-OCA-18-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Automating Maintenance Tasks Oracle 10g includes a comprehensive scheduling mechanism, that allows jobs to be run at regular intervals. For databases created using the DBCA, a scheduled job is created for the ‘SYS’ user to gather various statistics automatically. To display the job schedule, connect to EM with ‘SYSDBA’ privileges and select Administration > Database Scheduler > Jobs
The ‘SYS.GATHER_STATS_JOB’ runs in a maintenance window that opens overnight and at weekends. More details regarding the job scheduler and the GATHER_STATS_JOB are discussed in the next lesson
V2: Page 480
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Summary
• In this lesson, you have learnt about : – Automatic Maintenance Features – Workload Repository (AWR) – Database Diagnostic Monitor (ADDM) – Active Session History (ASH) – Alerts, Thresholds and Metrics – Automating maintenance tasks
ODBA10gDB-OCA-18-17
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 481
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Practice 18
• In this exercise, you will invoke several situations that trigger Oracle alerts • By running the supplied scripts, a number of different conditions will be invoked • Enterprise Manager will detect these faults and suggest some corrective actions
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA-18-18
Practice 18 For this practice, you will trigger various alerts using the provided scripts, then examine the results. To prepare for this practice, modify the Enterprise Manager configuration files as below:
EM’s response time may be reduced by modifying the EM daemon configuration file “ORACLE_HOME/sysman/config/emd.properties”. To improve the response time, modify the “UploadInterval” parameter in this file from the default value of “5” minutes to a lower value. The Enterprise Manager timeout can also be extended, by modifying the configuration file “ORACLE_HOME/sysman/config/emoms.properties” – add the following entry to this file: oracle.sysman.eml.maxInactiveTimeout=480
Note that this value is in minutes – 480 allows for an eight hour timeout. The Enterprise Manager daemon will need to be stopped and restarted in order to allow EM to recognise the modified entries.
Filling Tablespaces Begin by executing the Enterprise Manager Database Control and connect using a “SYSDBA” account. Examine the home page, alert information. You should not see any relevant alerts.
V2: Page 482
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Open a new command window and start SQL*Plus using the “SYSTEM” account. Execute the supplied script “Ch18CreateAlert.sql”. This script will create a new tablespace (“FULLTABLESPACE”) and populate the tablespace with a large table (“TESTCUSTOMERS”). Watch the EM Home Page until an alert is displayed. Note that this may take up to 5 minutes to be recognised by EM.
Whilst waiting for the alert to be identified, examine the tablespace using EM > Administration > Tablespaces. Note that “FULLTABLESPACE” shows a usage over 90% - the warning threshold is set to 85%.
In the SQL*Plus window, execute the supplied script “Ch18FixAlert.sql”. This script adds a second 10MB datafile to the “FULLTABLESPACE” tablespace. By doubling the space assigned to the tablespace, the usage of “FULLTABLESPACE” drops below 50%. Watch the EM home page until the alert is cleared – for threshold metrics, alerts are removed automatically when the condition causing the alert is resolved.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 483
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
In the SQL*Plus window, execute the supplied script “Ch18CriticalAlert.sql”. This script resizes the second datafile in the “FULLTABLESPACE” tablespace to 1MB. This script then creates a table (“TESTSALES”) in the “FULLTABLESPACE” tablespace and copies rows into this table until all the available space is used. Observe the EM home page again, to see the notification of the critical alert.
Exit from the SQL*Plus session; this will release locks on the files in “FULLTABLESPACE”. To cleanup from this practice, execute the following command:
OS> sqlplus system/oracle @Ch18CleanupAlert
Blocking Sessions From the Enterprise Manager home page, select Related Links > Manage Metrics. Click the “Edit Thresholds” button to display the current set of metric thresholds. Scroll down to view the “Blocking Session count” metric – the value should be zero. Edit this value by selecting the associated radio button – set the “Warning threshold” to 2 and the “Critical threshold” to 4. Click the “OK” button to save the changes. Wait for the “Update succeeded” message to be displayed. Click the “Database Instance: XXX” link in the top left corner of the EM screen to return to the home page display. Scroll down to display the “Alerts” section – note that there are no current alerts. Open a SQL*Plus session and connect as the “SYSTEM” user. Execute the supplied script “Ch18LockRow.sql” – this script will lock a row in the “SH.CUSTOMERS” table, then pause.
V2: Page 484
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Open a second SQL*Plus session and connect as the “SYSTEM” user. Execute the same script in this window – this second session will be blocked by the first session. Open a third SQL*Plus session and connect as the “SYSTEM” user. Execute the same script in this window – this third session will also be blocked by the first session. Return to the EM home page and select the “Performance” tab. Examine the “Average Active Sessions” display – note the increase in “Application” wait time as in the graph below:
Select the “Top Activity” link to display information about the waiting sessions:
Click the “Database Instance: XXX” link in the top left corner of the EM screen to return to the Performance page display.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 485
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Open two further SQL*Plus session and connect as the “SYSTEM” user. Execute the “Ch18LockRow.sql” script in these window – these sessions will also be blocked by the first session. On the EM Performance page, scroll to the “Additional Monitoring Links” section and click the “Blocking Sessions” link:
Note that there are several sessions waiting on a single session – the blocking session is 138 in the example above. Click the “Database Instance: XXX” link in the top left corner of the EM screen to return to the Performance page display. Click the “Home” tab to return to the Database home page. Observe the Alerts section:
V2: Page 486
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 18 – Automatic Maintenance
Oracle 10g Database Associate
Open one more SQL*Plus session and execute the query shown below (“Ch18OSAlerts.sql”):
SQL> EXECUTE print_table ('SELECT * FROM dba_outstanding_alerts') SEQUENCE_ID REASON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE REASON TIME_SUGGESTED CREATION_TIME SUGGESTED_ACTION
: : : : : : : : : :
ADVISOR_NAME METRIC_VALUE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL HOSTING_CLIENT_ID MODULE_ID PROCESS_ID HOST_ID HOST_NW_ADDR INSTANCE_NAME INSTANCE_NUMBER USER_ID EXECUTION_CONTEXT_ID ERROR_INSTANCE_ID ----------------SEQUENCE_ID REASON_ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE REASON
: : : : : : : : : : : : : : :
TIME_SUGGESTED CREATION_TIME SUGGESTED_ACTION
: : :
ADVISOR_NAME METRIC_VALUE MESSAGE_TYPE MESSAGE_GROUP MESSAGE_LEVEL HOSTING_CLIENT_ID MODULE_ID PROCESS_ID HOST_ID HOST_NW_ADDR INSTANCE_NAME INSTANCE_NUMBER USER_ID EXECUTION_CONTEXT_ID ERROR_INSTANCE_ID -----------------
: : : : : : : : : : : : : : :
: : : : : : :
Copyright © 2007 Jeremy Russell & Associates Ltd.
3191 6 SID: 138 Serial#: 9089 SESSION Session 138 is blocking 6 other sessions 30-MAY-07 13.42.22.550000 +01:00 30-MAY-07 13.37.21.708000 +01:00 Run ADDM to get more performance analysis about your system. ADDM 6 Warning Performance 1 SERVER MANAGEABILITY:kelr.c "v10g"."v10g" JEREMYTRAVEL 10.10.10.10 v10g 1 661A1B25094D-49A1-89F6-DF437BB40746-0 3172 121 Application EVENT_CLASS Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Application" 30-MAY-07 13.11.19.311000 +01:00 30-MAY-07 13.11.19.311000 +01:00 Run ADDM to get more performance analysis about your system. ADDM 100 Warning Performance 5 SERVER MANAGEABILITY:kelr.c "v10g"."v10g" JEREMYTRAVEL 10.10.10.10 v10g 1 9543C54A26CE-475A-8181-E2A02EB04942-0
V2: Page 487
Oracle 10g Database Associate
Lesson 18 – Automatic Maintenance
Click the Performance tab, to display the EM Performance page. On the EM Performance Page, click the “Run ADDM now” button. Observe the “Performance Analysis” information displayed by the ADDM.
Drill down into the first finding (“SQL statements were found waiting for row lock waits”). Observe the recommendations from the ADDM:
Cleanup To clean up from this practice, close all open SQL*Plus sessions, by pressing in each session. Note that you will need to close the sessions in the same sequence that they were opened. Each session will rollback any updates, then exit from SQL*Plus.
V2: Page 488
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
19 0 Performance Tuning
ODBA10gDB-OCA--19-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 19 – PERFORMANCE TUNING Lesson 19 focuses on the Performance Tuning features of Oracle 10g and the Enterprise Manager Database Control.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 489
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Objectives
• After completing this lesson, you should understand: – Instance and Database Tuning Procedures – Enterprise Manager Performance Features – Dynamic Performance Views – Oracle Advisory Framework • Automatic Database Diagnostic Monitor and … • SQL Tuning Advisor • SQL Access Advisor • Memory Advisor • Other Advisors ODBA10gDB-OCA--19-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives Lesson 19 demonstrates the performance tuning features of Oracle 10g and the Enterprise Manager Database Control. Topics discussed and demonstrated include: Instance and Database Tuning Procedures Enterprise Manager Performance Views Dynamic Performance Views More on the Oracle Advisory Framework, including:
V2: Page 490
–
Automatic Database Diagnostic Monitor
–
SQL Tuning Advisor
–
SQL Access Advisor
–
Memory Advisor
–
Undo Advisor
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Oracle Tuning Principles
• Based on “Time and Wait” model • Exposed to DBA’s as “Wait Interface” • 870+ wait events, split into classes: – Events : Free buffer, Scattered – Classes: User I/O, Commit, Application…
• EM > Performance > Average Active Sessions ODBA10gDB-OCA--19-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle Tuning Principles In Oracle 10g, tuning is based on the Oracle “Time and Wait” model. The overriding goal of the tuning process is minimise “Database Time”. Database time is measured as total amount of time spent either waiting for active (non-idle) events – referred to as wait time – or executing user-level database calls for client processes (excluding system processes) – referred to as service time. The Oracle “Wait Interface” is the dictionary information exposed by the Oracle database kernel, to describe events for which a process is waiting at any point in time. In the Wait Interface, information on both wait counts and wait time is made available via data dictionary views and through Enterprise Manager. The 870+ wait events are classified into groups by Oracle. By analysing the wait conditions and the groups to which they belong, an Oracle performance specialist can determine the importance of sundry events without needing to be aware of the lower-level details. Oracle 10g Enterprise Manager exposes the Wait Interface through the Performance Tab, Average Active Sessions graph.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 491
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Tuning Considerations
• Entire system needs to be optimised, including – Applications : Database Design and SQL – Memory : SGA organisation – Disk : Optimising multiple devices – Processes : Ensuring correct design – Operating : Shared memory utilisation System
• Changing one aspect may affect others Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ODBA10gDB-OCA--19-4
Tuning Considerations Tuning an Oracle database instance requires a holistic approach to perceived performance problems. Consideration must be given to all of the following areas: Applications
Database design that is appropriate for the application usage should be considered. Options include the creation of appropriate and efficient indexing structures, consideration of the use of partitioned objects and the use of materialised views. SQL statements issued by applications must also be examined. Potential issues include inefficient join mechanisms,
Instance Memory
The structure of the SGA and how it is split between the key areas, principally the buffer cache and the shared pool, is a critical factor when determining an optimum use of resources.
Instance Disk
Effective use of the available disk structure means ensuring optimum distribution of I/O across assigned disk units (drives and controllers). Operating system statistics showing I/O rates by drive will assist in maximising the use of these scarce resources.
Instance Processes
For some Oracle processes e.g. “DBWR”, “LGWR”, “ARCH”, either multiple processes or slave processes can be used to parallelise operations. By using these features on supported operating systems (not Windows!)
Operating System
Refer to your supplied Oracle OS documentation and installation guides for pre-requisites and tuning strategies for your specific OS.
V2: Page 492
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
This page intentionally left blank
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 493
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
EM Performance Page
May 31, 2007
ODBA10gDB-OCA--19-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
EM Performance Page Enterprise Manager’s Performance page provides a full set of performance monitoring and tuning tools, that consider all aspect of Oracle database and instance tuning.. The Host graph shows potential problems outside the database. On Windows platforms, CPU utilization will be displayed. On other platforms, the graph shows the load average (a moving average of the run queue length), which indicates the level of contention for CPU time. (Note : the large examples opposite and on the slide are from a Windows XP installation). The Average Active Sessions graph shows the level of CPU activity inside the database, together with an ordered list of wait event categories. . In the screen opposite, there have been recent increases in CPU usage (lower green section) and waits for User I/O (middle dark blue section) and System I/O (upper light blue section). The Instance Disk I/O graph shows the rate at which the database instance is issuing read/write requests. Increasing values for “other reads and other writes” indicates a backup, archiving, or file transfer activity that is occurring in the database. Physical reads and writes correspond to the normal I/O operations between the database files and the SGA.. The Instance Throughput graph highlights changes in the rates of logons, transactions, physical disk I/O and redo log consumption (per second or per transaction). The graph scales are adjusted appropriately as required by changes in the current database load.. Any of the categories can be clicked to drill down for more information. On the subsequent screens, the data is broken down further; for example, into types of I/O - including log file read, control file write, and more.
V2: Page 494
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
May 31, 2007
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 495
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
ADDM Recommendations
ODBA10gDB-OCA--19-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
ADDM Recommendations As discussed in the previous lesson, the Automatic Database Diagnostic Monitor is invoked after each AWR snapshot, to analyse the most recent period of activity. A pair of snapshots can be selected for analysis of any time period where snapshots have been retained in the AWR. Sundry recommendations can be provided by ADDM: see the list below for some examples:
The recommendations are shown in descending order of likely impact on overall performance. By clicking each recommendation, further information is displayed to assist in analysing the underlying cause of the reported problem.
V2: Page 496
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Oracle Statistics
• Object statistics: – for tables, indexes, columns – automatically collected with scheduled ‘GATHER_STATS_JOB’
• System statistics: – I/O and CPU utilisation information – Manually collected with ‘GATHER_SYSTEM_STATS’ and with instance parameters – Capture levels : BASIC, TYPICAL, ALL ODBA10gDB-OCA--19-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle Statistics
• Object Statistics A major source of input into the findings and recommendations produced by the ADDM (and associated tools) are the statistics that can be gathered on both database objects and system performance. Object statistics are maintained in the data dictionary for tables, indexes and (optionally) columns. These statistics can be gathered for a single object, a full schema or the entire database. For production systems, statistics are gathered principally using an automatically scheduled “GATHER_STATS_JOB” and stored in related dictionary tables e.g. “DBA_TABLES”, “DBA_INDEXES” and others. By using these statistics, the Query Optimiser can make informed decisions about the relative sizes of referenced objects. By making appropriate decisions, an optimal execution plan can be determined for a SQL statement.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 497
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Several procedures in the Oracle supplied “DBMS_STATS” package can be used to gather statistics on a single object, an schema or the entire database (“Ch19ObjectStats.sql”).
SQL> EXECUTE dbms_stats.gather_schema_stats( > OWNNAME => 'SCOTT', > METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO', > CASCADE=>TRUE); PL/SQL procedure successfully completed. SQL> SELECT table_name, num_rows, blocks, avg_row_len 2 to_char(last_analyzed, 'DD-MON-RR HH24:MI') "LAST DATE" 3 FROM dba_tables 4 WHERE owner = 'SCOTT'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST DATE ---------- ---------- ---------- ----------- --------------DEPT 5 5 19 31-MAY-07 17:40 EMP 14 5 37 31-MAY-07 17:40 BONUS 0 0 0 31-MAY-07 17:40 SALGRADE 5 5 10 31-MAY-07 17:40 NEWEMP 0 0 0 31-MAY-07 17:40 SQL>
“GATHER_STATS_JOB” is scheduled to execute in a maintenance window every night. This job will ensure that the schema statistics are as up-to-date as necessary, by gathering statistics on objects where the statistics are empty or stale (more than 10% of the table rows have been modified). This job executes the internal procedure “GATHER_DATABASE_STATS_JOB_PROC” – this procedure prioritises objects to be analysed in order of the magnitude of the changes applied, to ensure that the most needed statistics are updated first (before the maintenance window closes).
V2: Page 498
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
• System Statistics System statistics describe performance of the host environment on which Oracle is running. The information collected includes CPU costs (for executing instructions) and I/O cost (for retrieving single and multiple blocks from disk). Information is collected manually during periods of typical workload. The query optimiser can then factor the information into its cost calculations. System statistics are gathered manually, using the “DBMS_STATS.GATHER_SYSTEM_STATS” (“Ch19SystemStats.sql”):
Oracle
supplied
procedure
SQL> EXECUTE dbms_stats.gather_system_stats (gathering_mode=>'stop') ... run a typical workload during this period ... SQL> EXECUTE dbms_stats.gather_system_stats (gathering_mode=>'stop')
The results used by the query optimiser can be views from the table “SYS.AUX_STATS$”:
SQL> SELECT * FROM sys.aux_stats$; SNAME -------------------SYSSTATS_INFO SYSSTATS_INFO SYSSTATS_INFO SYSSTATS_INFO SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN SYSSTATS_MAIN
PNAME PVAL1 PVAL2 --------------- ---------- -------------------STATUS COMPLETED DSTART 05-31-2007 16:46 DSTOP 05-31-2007 16:51 FLAGS 1.00 CPUSPEEDNW 484.97 IOSEEKTIM 10.00 IOTFRSPEED 4096.00 SREADTIM 29.63 MREADTIM 65.50 CPUSPEED 1215.00 MBRC 9.00 MAXTHR SLAVETHR
Collected information includes the monitored period (“DSTART” to “DSTOP”), CPU speed without workload (“CPUSPEEDNW”) and with a workload (“CPUSPEED”), in millions of cycles per second and single/multi-block readtimes (“SREADTIM” / “MREADTIM”). Other procedures in the “DBMS_STATS” package can be used to save and load various sets of statistics, to be used during different periods of workload intensity. For more information, refer to the Oracle “PL/SQL Packages and Types” manual.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 499
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Oracle Query Optimiser
• Technology to create efficient execution plans for SQL statements • Considers multiple paths and costs • Factors include table size, available index structures, column value distribution … • Influenced by collected schema and system statistics • Replaces previous RULE/COST options ODBA10gDB-OCA--19-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle Query Optimiser The query optimiser is a technology built into the Oracle engine that can search for optimum query execution plans automatically. Earlier releases of Oracle included two optimiser modes, “rule” and “cost”. The rule based optimiser (RBO) used a built-in, invariable algorithm to determine the execution sequence. For example, when the RBO was used, tables in a query were processed in the reverse order in which they appeared in a SQL statement. Rule based optimisation (RBO) was typically less efficient than cost based optimisation (CBO). The CBO considers relative table sizes, packing density of rows and other factors to determine the “best” execution plan. The CBO choices are triggered by statistics on tables, indexes and other objects, that allow the CBO to consider all possible paths. Although Oracle 10g still includes an RBO mechanism, the default is to use CBO and statistics on schema objects. This is triggered by the automatic statistical gathering methods implemented in Oracle 10g.
V2: Page 500
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
This page intentionally left blank
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 501
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Execution Plans
ODBA10gDB-OCA--19-9
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Execution Plans The query optimiser technology determines an optimum “execution plan”, a sequence for accessing and processing the objects referenced in a query. Many factors are examined when determining plans, including both object and system statistics. Plans are stored in the SGA as a result of a “PARSE” operation on a SQL statement. Using the SQL*Plus “AUTOTRACE” feature, plans can be displayed interactively for further examination by a developer or DBA (“Ch19QueryOptimiser.sql”).
V2: Page 502
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
SQL> SET TIMING ON AUTOTRACE TRACEONLY EXPLAIN SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19*
SELECT
FROM
WHERE AND AND AND AND GROUP
C.cust_state_province state ,P.prod_name prod ,P.prod_category ctgy ,T.calendar_year year ,SUM(S.amount_sold) sale ,COUNT(S.amount_sold) cnt sh.sales S ,sh.times T ,sh.customers C ,sh.products P S.time_id = T.time_id S.prod_id = P.prod_id S.cust_id = C.cust_id C.cust_state_province IN ('IL','IN','MI','MN','WI') P.prod_id BETWEEN 5 AND 35 BY C.cust_state_province ,P.prod_category ,P.prod_name ,T.calendar_year
Execution Plan ---------------------------------------------------------Plan hash value: 3478307342 ------------------------------------------------------------------------------|Id |Operation |Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 575 | 52900 | 833 (2)| | 1 | HASH GROUP BY | | 575 | 52900 | 833 (2)| |*2 | HASH JOIN | | 14319 | 1286K| 832 (2)| | 3 | TABLE ACCESS FULL |TIMES | 1826 | 21912 | 16 (0)| |*4 | HASH JOIN | | 14319 | 1118K| 815 (2)| |*5 | TABLE ACCESS FULL |CUSTOMERS | 1914 | 26796 | 368 (1)| |*6 | HASH JOIN | | 52815 | 3404K| 446 (2)| | 7 | TABLE ACCESS BY INDEX ROWID|PRODUCTS | 13 | 572 | 2 (0)| |*8 | INDEX RANGE SCAN |PRODUCTS_PK | 13 | | 1 (0)| | 9 | PARTITION RANGE ALL | | 298K| 6416K| 443 (2)| |*0 | TABLE ACCESS FULL |SALES | 298K| 6416K| 443 (2)| ------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("S"."TIME_ID"="T"."TIME_ID") 4 - access("S"."CUST_ID"="C"."CUST_ID") 5 - filter("C"."CUST_STATE_PROVINCE"='IL' OR "C"."CUST_STATE_PROVINCE"='IN' OR "C"."CUST_STATE_PROVINCE"='MI' OR "C"."CUST_STATE_PROVINCE"='MN' OR "C"."CUST_STATE_PROVINCE"='WI') 6 - access("S"."PROD_ID"="P"."PROD_ID") 8 - access("P"."PROD_ID">=5 AND "P"."PROD_ID"<=35) 10 - filter("S"."PROD_ID"<=35 AND "S"."PROD_ID">=5)
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 503
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Optimiser Dynamic Sampling
• Automatic feature to re-gather statistics as appropriate during query parsing • Set OPTIMIZER_DYNAMIC_SAMPLING to value between 0 and 10 • Higher values are more aggressive • Recommended value of 2 for Oracle 10g
ODBA10gDB-OCA--19-10
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Optimiser Dynamic Sampling Dynamic sampling can be turned on to control automatic sampling as a SQL statement is parsed and compiled. The parameter “OPTIMIZER_DYNAMIC_SAMPLING” accepts a numeric value from 0 to 10. Higher values request more aggressive sampling; acceptable values are: Level 0
Do not use dynamic sampling.
Level 1
Conditionally sample all tables that have not been analyzed
Level 2
Unconditionally sample to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3
All tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate.
Level 4
All tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns.
Levels 5 to 9
All tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the number of blocks.
Level 10
All tables that meet the Level 9 criteria using all blocks in the table.
V2: Page 504
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Advisor Central
• Entry point for all advisors
ODBA10gDB-OCA--19-11
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Advisor Central From the “Related Links” section on all the main Enterprise Manager tabs, the “Advisor Central” link displays the list of available “Advisors”. For full use of the Memory and MTTR Advisors, a “SYSDBA” login should be used. Each advisor can be selected and actioned by clicking the appropriate link. Provided advisors include: Memory Advisor
Tunes the use of Shared Memory, optimising the amount of memory assigned to the buffer cache, shared pool, Java pool and large pool
MTTR Advisor
Allows instance recovery time to be bounded by time, which causes Oracle to set various internal parameters to conform with the entered target time.
Segment Advisor
Scans tablespaces or selected segments to determine whether any performance gains can be made if the objects are shrunk to reclaim unused space
SQL Access Advisor
Analyses recent SQL statements to suggest alterations in structure that may improve performance of the workload
SQL Tuning Advisor
Analyses recent or selected SQL statements to suggest modifications to statements to improve the executed plans.
Undo Management (Advisor)
Suggests maximum undo retention for specified UNDO tablespace size
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 505
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Memory Advisor
ODBA10gDB-OCA--19-12
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Memory Advisor The Memory Advisor allows the DBA to plan for maximum memory usage for both the SGA and PGA areas. Using Oracle’s Automatic Memory Management, the distribution of memory between the components of this areas can be adjusted automatically. Maximum values can be set using the Memory Advisor. The impact of increasing or decreasing memory can also be explored with this tool.
V2: Page 506
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
MTTR Advisor
ODBA10gDB-OCA--19-13
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
MTTR Advisor The MTTR Advisor is enabled by setting the system parameter “fast_start_mttr_target” to a value in seconds for completing a power failure recovery. The graphical advisor assists in evaluating the effects of this setting on system performance. When the MTTR advisor is enabled, the “V$MTTR_TARGET_ADVICE” view can also be used to determine the effect of the different settings expressed as additional physical I/O operations SQL> desc v$mttr_target_advice Name Null? ----------------------------------------- -------MTTR_TARGET_FOR_ESTIMATE ADVICE_STATUS DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR ESTD_TOTAL_IOS ESTD_TOTAL_IO_FACTOR
Copyright © 2007 Jeremy Russell & Associates Ltd.
Type ---------------------------NUMBER VARCHAR2(5) NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER
V2: Page 507
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Segment Advisor
• Checks selected objects for wasted space • Can select by tablespace or schema • Schedules task for immediate or deferred execution • Recommends and implements segment or tablespace shrinkage as appropriate
ODBA10gDB-OCA--19-14
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Segment Advisor The Automated Segment Advisor can be invoked on demand to check selected objects for wasted space. Options for checking include by selected tablespace(s) or specified schema object(s). The Segment Advisor can be run immediately or on a regular schedule. Since this advisor can be resource-intensive, it is recommended to schedule regular runs during off-peak periods. The Segment Advisor will generate reports recommending that tables or other objects are reorganised to compress rows into the minimum possible number of blocks.
V2: Page 508
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Segment Advisor Example
ODBA10gDB-OCA--19-15
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Segment Advisor Example This example creates a new tablespace and builds a new table in the space. The table is then emptied by deleting all rows before running the Automated Segment Advisor. Where there is reclaimable space, the advisor can show more information as below – click the button in the “Recommendations” column:
The “Implement” button can then be used to schedule a job to perform the required optimisation.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 509
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
SQL Access Advisor
ODBA10gDB-OCA--19-16
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
SQL Access Advisor The SQL Access Advisor evaluates a range of SQL statements and suggests Selected workloads can be drawn from historical (actual) workloads, a previously captured and stored workload, a user-defined (typical) workload or a hypothetical workload based on selected schemas/tables. Recommendation types include suggestions for new indexes or materialized views (or both), or an evaluation of the use of existing structures only. The Advisor can run in either a “limited” mode, focusing on perceived high-cost statements only or a “comprehensive” mode, where all statements are fully analysed. A wide range of Advanced Options can also be selected. These include workload scope (targeted at OLTP or Data Warehousing systems), whether results should include “DROP object” recommendations, tuning targets (from CPU time, disk I/O and more) as well as other choices.
V2: Page 510
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
SQL Access Advisor Results WorkLoad I/O Cost The workload I/O cost display shows a graph of the likely I/O cost reductions after implementing the suggestion recommendations. Further details can be displayed by clicking the underlined recommendation count in the display.
A recommendation summary is illustrated below:
Further details for each recommendation can be displayed by clicking the recommendation id or the “Recommendation Details” button.
The recommendations can be implemented if required by clicking the “Apply” button.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 511
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
SQL Tuning Advisor
• EM > Advisor Central > SQL Tuning Advisor > Top Activity
ODBA10gDB-OCA--19-17
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
SQL Tuning Advisor The SQL Tuning Advisor tool uses the Automatic Tuning Optimizer (ATO) to perform a variety of tuning tasks, resulting in suggestions for resolving issues discovered and thus improving query performance. Statistics Analysis
Checks referenced objects for missing and out-of-date stats; if the STA’s recommendations are ignored, corrective action is scheduled automatically.
SQL Profiling
Additional information may be collected to refine initial estimates for enhanced query performance.
Access Path Analysis
Based on typical usage, generates recommendations for additional index creation steps that will decrease execution time for the query.
SQL Structure Analysis
ATO attempts to generate syntactic and semantic improvements to the queries being tuned.
Other routes to selecting SQL statements for analysis are via any of the links on the initial SQL Tuning advisor page.
V2: Page 512
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
SQL Tuning Advisor Recommendations
ODBA10gDB-OCA--19-18
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
SQL Tuning Advisor Recommendations For poorly performing SQL statements, the SQL Tuning Advisor can be invoked to make recommendations to improve the performance. From the EM Top Activity screen, one or more statements can be selected for analysis by the SQL Tuning Advisor. An analysis run can be scheduled immediately or for deferred execution. After analysis, a report is produced which may include advice to collect statistics or a revised execution plan for a specified statement, which uses hints to optimise the statement. Further options include the ability to display an original and recommended execution plan, as shown below. Click the icon to display the revised plan suggestion:
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 513
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
To adopt the new plan suggestion, select the appropriate radio button and click “Implement”. This stores the new plan as a “profile” for the statement. SQL profiles are stored in the “dba_sql_profiles” view:
Name ----------------------------------------NAME CATEGORY SIGNATURE SQL_TEXT CREATED LAST_MODIFIED DESCRIPTION TYPE STATUS FORCE_MATCHING
Null? -------NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL
Type ---------------------------VARCHAR2(30) VARCHAR2(30) NUMBER CLOB DATE DATE VARCHAR2(500) VARCHAR2(9) VARCHAR2(8) VARCHAR2(3)
After accepting the recommended advice, statements will then use the SQL profile to optimise future executions. This can be demonstrated by re-running the tuned statement in SQL*Plus with the ‘SET AUTOTRACE TRACEONLY’ option:
Execution Plan ---------------------------------------------------------Plan hash value: 4238351645 -------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 187 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 187 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("CUST_ID"=1234) Note ----- SQL profile "SYS_SQLPROF_0141ba51fab18000" used for this statement
V2: Page 514
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Undo Advisor
ODBA10gDB-OCA--19-19
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Undo Advisor The Undo Advisor considers the workload over a number of set time periods (or a custom time period) and computes the best possible undo retention period (in minutes) and the optimum undo tablespace size to support the maximum length query recorded. Using the EM Advisor, the DBA can enter a time period and display an analysis of the system activity. Any recommendations for resizing the undo tablespace or can then be implemented immediately through the advisor.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 515
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Memory Advisor
ODBA10gDB-OCA--19-20
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Memory Advisor The SGA can be set to “automatic” mode, where the system will automatically split the assigned memory used by the Oracle instance. This Automatic Shared Memory Management (“ASMM”) simplifies the management of systems with fluctuating workloads. For example, a representative system which runs OLTP jobs during a daytime period will require a larger shared pool and buffer cache. Overnight batch jobs will necessitate a bigger memory allocation for the large pool. ASMM will automatically assign an appropriate amount of memory to the requesting area without DBA intervention. To configure ASMM, the “SGA_TARGET” parameter should be set to the required value. To manually manage shared memory, disable the automatic management by clicking the “Disable” button. By selecting this option, the “SGA_TARGET” is set to a value of 0. The screen will change to the display shown on the right, which allows entry of manual sizing parameters for selected memory areas. In ASMM mode, the “Advice” button displays an advisor window which allows the DBA to assess the predicted effect on DBtime of altering the SGA size.
V2: Page 516
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
MTTR Advisor
ODBA10gDB-OCA--19-21
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
MTTR Advisor The MTTR Advisor assists in evaluating the effect of different related settings on system performance. When the MTTR advisor is enabled, the “V$MTTR_TARGET_ADVICE” view can be used to determine the effect of the different settings expressed as additional physical I/O operations SQL> desc v$mttr_target_advice Name Null? ----------------------------------------- -------MTTR_TARGET_FOR_ESTIMATE ADVICE_STATUS DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR ESTD_TOTAL_IOS ESTD_TOTAL_IO_FACTOR
Copyright © 2007 Jeremy Russell & Associates Ltd.
Type ---------------------------NUMBER VARCHAR2(5) NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER
V2: Page 517
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Invalid/Unusable Objects
• Schema objects dependent on changed objects must be recompiled or reparsed • Applies to views (tables, indexes), stored code (other code, tables, indexes) • Dynamic recompilation is inefficient • Caused by modifying stored code, moving tables, rebuilding indexes • Similar operations should run off-peak ODBA10gDB-OCA--19-22
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Invalid/Unusable Objects PL/SQL stored code (including procedures, functions, packages and triggers) is held in the shared pool library cache at run-time. Within the cache, both text and a parsed (compiled) version of the code is held. Where a stored code object refers to other stored code – and the referenced code is altered – any “dependent” code is invalidated and must be recompiled. Similar dependencies apply to database views (stored SQL statements) – a view, together with any stored code that references the view – is invalidated if base tables used by the view are changed. Other schema objects (tables, synonymns, sequences) are always valid. Invalid objects may invoke a chain of mandatory recompilation, leading to a limited period of reduced performance whilst dynamic recompilation is completed. Because of the potentially complex interdependencies between stored code objects, it can be difficult to assess the likely impact of a small change to stored code.
V2: Page 518
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
• Examining Object Dependencies Several dictionary views (e.g. “USER_DEPENDENCIES” show details of dependent objects. (‘Ch18Dependencies’)
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 519
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Invalid Objects Example
ODBA10gDB-OCA--19-23
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Invalid Objects Example In this example, two procedures are created (‘proc1’ and ‘proc2’). ‘proc2’ references ‘proc1’, therefore when ‘proc1’ is updated, the dependent procedure ‘proc2’ is invalidated. To test, execute the demonstration script ‘Ch19CrInvProcs.sql’. The supplied packaged procedure “dbms_utility.compile_schema” procedure can resolve invalid code when required.
V2: Page 520
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
SQL> CREATE OR REPLACE PROCEDURE proc1 as BEGIN NULL; END; 2 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE proc2 AS BEGIN proc1; END; 2 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE proc1 AS BEGIN NULL; END; 2 / Procedure created. SQL> SELECT OBJECT_NAME, STATUS FROM user_objects 2 WHERE object_type = 'PROCEDURE'; OBJECT_NAME --------------PROC2 PROC1
STATUS ------INVALID VALID
SQL> EXEC DBMS_UTILITY.COMPILE_SCHEMA( 'SCOTT' ); PL/SQL procedure successfully completed. SQL> SELECT OBJECT_NAME, STATUS FROM user_objects 2 WHERE object_type = 'PROCEDURE'; OBJECT_NAME --------------PROC2 PROC1
STATUS ------VALID VALID
The ‘dbms_utility.compile_schema’ procedure can resolve invalid code when required.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 521
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Summary
• In this lesson, you have learnt about : – Instance and Database Tuning Procedures – Enterprise Manager Performance Features – Dynamic Performance Views – Oracle Advisory Framework • Automatic Database Diagnostic Monitor and … • SQL Tuning Advisor • SQL Access Advisor • Memory Advisor • Other Advisors
ODBA10gDB-OCA--19-24
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 522
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Practice 19
• In this practice, you will execute a number of SQL scripts as below • Execute several inefficient SQL statement and use the EM SQL Tuning Advisor • Create a current workload, then run the EM SQL Access Advisor • Create a workload generating contention and undo information, then run an ADDM analysis report and observe the results ODBA10gDB-OCA--19-25
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Practice 19 In this practice, you will use several advisors to investigate database issues and performance. Open a SQL*Plus session as “SYSTEM” and execute the statements below to create a new test table (“Ch19AddmContendSetup.sql”):
SQL> CREATE TABLE contendtest AS 2 SELECT * FROM sh.customers;
In the same SQL*Plus session, execute the script “Ch19AddmFragmentSetup.sql” – this script creates two further test tables, called “testcustomers” and “fragcustomers”. Create a manual AWR snapshot, using the “dbms_workload_repository.create_snapshot()” procedure (“Ch19CreateSnapshot.sql”): SQL> EXECUTE dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. SQL>
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 523
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
Execute the following SQL statements several times. These statements are forcing a full scan of the “SH.CUSTOMERS” table instead of using an index. In addition, the use of literals instead of a runtime bind variable is also detrimental to performance (“Ch19AddmLiteralQuery.sql”):
SQL> SQL> SQL> SQL> SQL> SQL>
SELECT SELECT SELECT SELECT SELECT SELECT
/*+ /*+ /*+ /*+ /*+ /*+
full(c) full(c) full(c) full(c) full(c) full(c)
*/ */ */ */ */ */
* * * * * *
FROM FROM FROM FROM FROM FROM
sh.customers sh.customers sh.customers sh.customers sh.customers sh.customers
c c c c c c
WHERE WHERE WHERE WHERE WHERE WHERE
cust_id cust_id cust_id cust_id cust_id cust_id
= = = = = =
1234; 4567; 8765; 1234; 4567; 8765;
Open a SQL*Plus session as “SYSTEM” and execute the statements below several times – these statements and others are in the script “Ch19MultiQuery.sql”. The indexing structure of the “SH.CUSTOMERS” is not ideal for these queries. SQL> SELECT cust_id, cust_last_name from system.testcustomers 2 WHERE cust_city_id = 51040 3 ORDER BY cust_last_name; SQL> SELECT count(distinct cust_city_id) FROM system.testcustomers; SQL> SELECT cust_id, cust_last_name, cust_email FROM system.testcustomers 2 WHERE cust_city_id = 51040 3 ORDER BY cust_email;
In the same session, get ready to run the PL/SQL procedure below (“Ch19AddmContend1.sql”). Type the command “@Ch19AddmContend1.sql” but do not hit return. BEGIN FOR I IN 1..4 LOOP UPDATE contendtest SET cust_first_name = 'FRED' WHERE cust_last_name < 'K'; COMMIT; END LOOP; END; . /
V2: Page 524
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 19 – Performance Tuning
Oracle 10g Database Associate
Start a second SQL*Plus session as “SYSTEM” and execute the procedure below, in the script (“Ch19AddmContend2.sql”): BEGIN FOR I IN 1..4 LOOP UPDATE contendtest SET cust_first_name = 'Fred' WHERE cust_last_name >= 'K'; COMMIT; END LOOP; END; . /
These two scripts will execute repeatedly and contend with each other. Repeat both procedures several times. Create another ADDM snapshot, with the “dbms_workload_repository.create_snapshot()” procedure (“Ch19CreateSnapshot.sql”): Use the EM Advisor Central Page, confirm that the “Advisory Type” is set to “ADDM” and the “Advisor Runs” to “Last Run”. Click “Go” to display the results
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 525
Oracle 10g Database Associate
Lesson 19 – Performance Tuning
The Advisor results page should display findings similar to the output shown below:
What conclusions can you draw from an examination of this information and by drilling down further into the analysed results?
V2: Page 526
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 20 – Q & A
Oracle 10g Database Associate
20 0 Q&A
ODBA10gDB-OCA-20-1
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
LESSON 20 – Q & A Lesson 20 concludes this class with suggested further reading and study options, and an opportunity to ask further questions.
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 527
Oracle 10g Database Associate
Lesson 20 – Q & A
Objectives
• This session gives you the opportunity to explore other areas of interest • The following pages also include: – A further reading list – List of useful websites – Information on Oracle certification – Suggestions for further training
ODBA10gDB-OCA-20-2
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Objectives In this lesson, you will be provided with a number of additional references to sources of further information. Information provided includes: Further reading suggestions List of useful websites Information on Oracle certification Suggestions for further training
V2: Page 528
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 20 – Q & A
Oracle 10g Database Associate
Suggested Reading List
• Oracle 10g Beginners Guide – Abramson et al : Oracle Press : 0072230789
• Oracle Essentials – Greenwald et al : O' Reilly : 0596005857
• Oracle Database 10g DBA Handbook – Loney, Bryla : Oracle Press : 0072231459
• Oracle 10g OCP New Features – Bryla, Thomas : Sybex : 00782143555 ODBA10gDB-OCA-20-3
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Suggested Reading List Oracle 10g Beginners Guide –
Abramson et al : Oracle Press : 0072230789
Oracle Essentials –
Greenwald et al : O'Reilly : 0596005857
Oracle Database 10g DBA Handbook –
Loney, Bryla : Oracle Press : 0072231459
Oracle 10g OCP New Features –
Bryla, Thomas : Sybex : 00782143555
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 529
Oracle 10g Database Associate
Lesson 20 – Q & A
Useful Websites
• • • • • • •
http://www.lazydba.com/ http://www.dba-village.com/ http://www.gennick.com/ http://www.kevinloney.com/ http://asktom.oracle.com/ http://technet.oracle.com http://www.oracletrainer.net
ODBA10gDB-OCA-20-4
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Useful Websites
http://www.lazydba.com/ http://www.dba-village.com/ http://www.gennick.com/ http://www.kevinloney.com/ http://asktom.oracle.com/ http://technet.oracle.com http://www.oracletrainer.net
V2: Page 530
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 20 – Q & A
Oracle 10g Database Associate
Oracle Certification
• • • •
http://tinyurl.com/cw3ql (Oracle site) http://www.orafaq.com/faqocp.htm http://www.whizlabs.com/ocp.html http://www.dbasupport.com/forums/forum display.php?forumid=3 • http://securereg3.prometric.com/ • http://ilearning.oracle.com/
ODBA10gDB-OCA-20-5
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle Certification
http://tinyurl.com/cw3ql (Oracle site) http://www.orafaq.com/faqocp.htm http://www.whizlabs.com/ocp.html http://www.dbasupport.com/forums/forumdisplay.php?forumid=3 http://securereg3.prometric.com/ http://ilearning.oracle.com/
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 531
Oracle 10g Database Associate
Lesson 20 – Q & A
Further Training
• After completing this class, you should be able to take the Oracle Associate exam • Further training is available to enable you to proceed to the Oracle Professional exam • Please note the following Oracle T&C' s Candidates Candidateswho whowish wishto toobtain obtainthe theOracle OracleDatabase Database10g 10gDBA DBAOCP OCP credential must attend one instructor-led inClass or instructor-led credential must attend one instructor-led inClass or instructor-led online onlinecourse coursefrom fromthe theapproved approvedlist listof ofOracle OracleUniversity Universitycourses courses and andsubmit submitthe theOracle OracleOCP OCPHands-On Hands-OnCourse CourseRequirement RequirementForm Form ODBA10gDB-OCA-20-6
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Further training After completing this class, you should be able to take the Oracle Associate exam to attain stage 1 of the Oracle certification status. Additional training (or self-tuition) will allow you to proceed to the Oracle Professional exam, or even an Oracle Masters certification. Please note that you are required to take at least one authorised Oracle class in order to be issued with a confirmation of your certification status. Please consult your Oracle representative for more information.
V2: Page 532
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 20 – Q & A
Oracle 10g Database Associate
Oracle Certified Professional Training
• • • • • • •
Globalisation and internationalisation Advanced recovery tasks with RMAN Automatic database management Resource manager Using the Oracle scheduler Flashback database mechanism Setup and use of Automated Storage files
ODBA10gDB-OCA-20-7
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Oracle Certified Professional Training To attain the OCP status, you will need to attend a class or study for the following additional areas in Oracle 10g: Globalisation and internationalisation Advanced recovery tasks with RMAN Automatic database management Resource manager Using the Oracle scheduler Flashback database mechanism Setup and use of Automated Storage files
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 533
Oracle 10g Database Associate
Lesson 20 – Q & A
Summary
• This session gave you the opportunity to explore other areas of interest • Good luck with your Oracle DBA role and keep reading and practicing to reinforce the skills gained in this class
ODBA10gDB-OCA-20-8
Copyright Jeremy Russell & Associates, 2007. All rights reserved.
Summary ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________
V2: Page 534
Copyright © 2007 Jeremy Russell & Associates Ltd.
Lesson 20 – Q & A
Oracle 10g Database Associate
This page intentionally left blank
Copyright © 2007 Jeremy Russell & Associates Ltd.
V2: Page 535
Oracle 10g Database Associate
Lesson 20 – Q & A
This page intentionally left blank
V2: Page 536
Copyright © 2007 Jeremy Russell & Associates Ltd.
