Less04_Database_Instance.ppt

Copyright © 2009, Oracle. All rights reserved. 4 - 2 Objectives After completing this lesson, you should be able to: • Start and stop the Oracle database and components • Use Oracle Enterprise Manager • Access a database with SQL*Plus • Modify database initialization parameters • Describe the stages of database startup • Describe database shutdown options • View the alert log • Access dynamic performance views

Copyright © 2009, Oracle. All rights reserved. 4 - 3 Management Framework Oracle Database 11g Release 2 management framework components: • Database instance • Listener • Management interface: – Database Control – Management agent (when using Grid Control) Listener Database Control Management agent Management interface or

Copyright © 2009, Oracle. All rights reserved. 4 - 4 Starting and Stopping Database Control $ . oraenv ORACLE_SID = [orcl] ? orcl The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home1 is /u01/app/oracle $ emctl start dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved. http://host01.example.com:1158/em/console/aboutApplication Starting Oracle Enterprise Manager 11g Database Control ........started. ------------------------------------------------------------------ Logs are generated in directory /u01/app/oracle/product/11.2.0/db_home1/host01.example.com_orcl/sysman/ log $ emctl stop dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved. https://host01.example.com:1158/em/console/aboutApplication Stopping Oracle Enterprise Manager 11g Database Control ... ... Stopped.

Copyright © 2009, Oracle. All rights reserved. 4 - 8 Other Oracle Tools • SQL*Plus provides an additional interface to your database so that you can: – Perform database management operations – Execute SQL commands to query, insert, update, and delete data in your database • SQL Developer: – Is a graphical user interface for accessing your instance of Oracle Database – Supports development in both SQL and PL/SQL – Is available in the default installation of Oracle Database Components > SQL*Plus Init Params DB Startup DB Shutdown Alert Log Perf Views

Copyright © 2009, Oracle. All rights reserved. 4 - 9 Using SQL*Plus SQL*Plus is: • A command-line tool • Used interactively or in batch mode $ sqlplus hr SQL*Plus: Release 11.2.0.1.0 - Production on Thu Jun 18 05:04:49 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter Password: ******* Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select last_name from employees; LAST_NAME ------------------------- Abel Ande …

Copyright © 2009, Oracle. All rights reserved. 4 - 10 Calling SQL*Plus from a Shell Script $ ./batch_sqlplus.sh SQL*Plus: Release 11.2.0.1.0 - Production on Thu Jun 18 05:10:19 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> COUNT(*) ---------- 107 SQL> 107 rows updated. SQL> Commit complete. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options $ # Name of this file: batch_sqlplus.sh # Count employees and give raise. sqlplus hr/hr <<EOF select count(*) from employees; update employees set salary = salary*1.10; commit; quit EOF Output

Copyright © 2009, Oracle. All rights reserved. 4 - 11 Calling a SQL Script from SQL*Plus $ sqlplus hr/hr @script.sql SQL*Plus: Release 11.2.0.1.0 - Production on Thu Jun 18 05:13:42 2009 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 60 IT 103 1400 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options select * from departments where location_id = 1400; quit script.sql Output

Copyright © 2009, Oracle. All rights reserved. 4 - 14 Simplified Initialization Parameters DB_CACHE_SIZE DB_FILE_MULTIBLOCK _READ_COUNT SHARED_POOL_SIZE … Advanced CONTROL_FILES DB_BLOCK_SIZE PROCESSES UNDO_TABLESPACE … Basic

Copyright © 2009, Oracle. All rights reserved. 4 - 15 Initialization Parameters: Examples Parameter Specifies CONTROL_FILES One or more control file names DB_FILES Maximum number of database files PROCESSES Maximum number of OS user processes that can simultaneously connect DB_BLOCK_SIZE Standard database block size used by all tablespaces DB_CACHE_SIZE Size of the standard block buffer cache

Copyright © 2009, Oracle. All rights reserved. 4 - 16 Initialization Parameters: Examples System Global Area (SGA) Shared pool Database buffer cache Redo log buffer Streams pool Large pool Java pool KEEP buffer pool RECYCLE buffer pool nK buffer cache PGA Stack Space User Global Area SGA_TARGET (Total size of all SGA components) MEMORY_TARGET (Total size of system-wide usable memory) Stack Space User Global Area PGA

Copyright © 2009, Oracle. All rights reserved. 4 - 18 Initialization Parameters: Examples Parameter Specifies PGA_AGGREGATE_TARGET Amount of PGA memory allocated to all server processes SHARED_POOL_SIZE Size of shared pool (in bytes) UNDO_MANAGEMENT Undo space management mode to be used

Copyright © 2009, Oracle. All rights reserved. 4 - 19 Using SQL*Plus to View Parameters SQL> SELECT name , value FROM V$PARAMETER; NAME VALUE ------------ ---------- lock_name_space 2 processes 150 sessions 247 timed_statistics TRUE timed_os_statistics 0 … SQL>SHOW PARAMETER SHARED_POOL_SIZE NAME TYPE VALUE ------------------------------------ ----------- --------------------- shared_pool_size big integer 0 SQL> show parameter para NAME TYPE VALUE ------------------------------------ ----------- --------------------- fast_start_parallel_rollback string LOW parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_execution_message_size integer 16384 parallel_instance_group string …

Copyright © 2009, Oracle. All rights reserved. 4 - 21 Changing Initialization Parameter Values • Static parameters: – Can be changed only in the parameter file – Require restarting the instance before taking effect – Account for about 110 parameters • Dynamic parameters: – Can be changed while database is online – Can be altered at: — Session level — System level – Are valid for duration of session or based on SCOPE setting – Are changed by using ALTER SESSION and ALTER SYSTEM commands – Account for about 234 parameters

Copyright © 2009, Oracle. All rights reserved. 4 - 23 Changing Parameter Values: Examples SQL> ALTER SESSION SET NLS_DATE_FORMAT ='mon dd yyyy'; Session altered. SQL> SELECT SYSDATE FROM dual; SYSDATE ----------- jun 18 2009 SQL> ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=2 COMMENT='Reduce from 10 for tighter security.' SCOPE=SPFILE; System altered.

Copyright © 2009, Oracle. All rights reserved. 4 - 30 Starting Up an Oracle Database Instance: OPEN OPEN MOUNT NOMOUNT SHUTDOWN All files opened as described by the control file for this instance Control file opened for this instance Instance started STARTUP

Copyright © 2009, Oracle. All rights reserved. 4 - 31 Startup Options: Examples SQL> startup SQL> alter database mount; SQL> alter database open; SQL> startup nomount 1 2 3 4 $ srvctl start database –d orcl –o mount • Using the sqlplus utility: • Using the srvctl utility with Oracle Restart

Copyright © 2009, Oracle. All rights reserved. 4 - 33 Shutdown Modes Shutdown modes: • A = ABORT • I = IMMEDIATE • T = TRANSACTIONAL • N = NORMAL Shutdown Modes A I T N Allows new connections No No No No Waits until current sessions end No No No Yes Waits until current transactions end No No Yes Yes Forces a checkpoint and closes files No Yes Yes Yes

Copyright © 2009, Oracle. All rights reserved. 4 - 34 Shutdown Options During: SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL or SHUTDOWN IMMEDIATE Consistent database On the way down: • Uncommitted changes rolled back, for IMMEDIATE • Database buffer cache written to data files • Resources released On the way up: • No instance recovery

Copyright © 2009, Oracle. All rights reserved. 4 - 36 Shutdown Options During: SHUTDOWN ABORT or Instance failure or STARTUP FORCE Inconsistent database On the way down: • Modified buffers not written to data files • Uncommitted changes not rolled back On the way up: • Online redo log files used to reapply changes • Undo segments used to roll back uncommitted changes • Resources released

Copyright © 2009, Oracle. All rights reserved. 4 - 37 Shutdown Options: Examples SQL> shutdown SQL> shutdown immediate SQL> shutdown abort SQL> shutdown transactional • Using SQL*Plus: • Using the SRVCTL utility with Oracle Restart $ srvctl stop database –d orcl –o abort 1 2 3 4

Copyright © 2009, Oracle. All rights reserved. 4 - 38 Viewing the Alert Log Database Home page > Related Links region > Alert Log Content Components SQL*Plus Init Params DB Startup DB Shutdown > Alert Log Perf Views

Copyright © 2009, Oracle. All rights reserved. 4 - 40 Using Trace Files • Each server and background process can write to an associated trace file. • Error information is written to the corresponding trace file. • Automatic diagnostic repository (ADR) – Is a systemwide central tracing and logging repository – Stores database diagnostic data such as: — Traces — Alert log — Health monitor reports

Copyright © 2009, Oracle. All rights reserved. 4 - 42 Shared pool Database buffer cache Redo log buffer Streams pool Large pool Java pool KEEP buffer pool RECYCLE buffer pool nK buffer cache Dynamic Performance Views Provide access to information about changing states of the instance memory structures Session data Wait events Memory allocations Running SQL UNDO usage Open cursors Redo log usage …and so on System Global Area Components SQL*Plus Init Params DB Startup DB Shutdown Alert Log > Perf Views

Copyright © 2009, Oracle. All rights reserved. 4 - 43 Dynamic Performance Views: Usage Examples SQL> SELECT sql_text, executions FROM v$sql WHERE cpu_time > 200000; SQL> SELECT * FROM v$session WHERE machine = 'EDRSR9P1' and logon_time > SYSDATE - 1; SQL> SELECT sid, ctime FROM v$lock WHERE block > 0; 1 2 3

Copyright © 2009, Oracle. All rights reserved. 4 - 44 Dynamic Performance Views: Considerations • These views are owned by the SYS user. • Different views are available at different times: – The instance has been started. – The database is mounted. – The database is open. • You can query V$FIXED_TABLE to see all the view names. • These views are often referred to as “v-dollar views.” • Read consistency is not guaranteed on these views because the data is dynamic.

Copyright © 2009, Oracle. All rights reserved. 4 - 45 Data Dictionary: Overview SELECT * FROM dictionary; Tables Indexes Views Users Schemas Procedures …and so on Schema Constraints Indexes Views Sequences Temp Tables > Data Dict System Tablespace Metadata

Copyright © 2009, Oracle. All rights reserved. 4 - 46 Data Dictionary Views Who Can Query Contents Subset of Notes DBA_ DBA Everything N/A May have additional columns meant for DBA use only ALL_ Everyone Everything that the user has privileges to see DBA_ views Includes user’s own objects and other objects the user has been granted privileges to see USER_ Everyone Everything that the user owns ALL_ views Is usually the same as ALL_ except for the missing OWNER column (Some views have abbreviated names as PUBLIC synonyms.)

Copyright © 2009, Oracle. All rights reserved. 4 - 48 Data Dictionary: Usage Examples SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS = 'OPEN'; SELECT table_name, tablespace_name FROM user_tables; SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS','XDB'); DESCRIBE dba_indexes 1 2 3 4

Copyright © 2009, Oracle. All rights reserved. 4 - 49 Quiz When using Oracle Restart, the server control utility (srvctl) must be used instead of SQL*Plus to start and stop a database instance. 1. True 2. False

Copyright © 2009, Oracle. All rights reserved. 4 - 50 Quiz Which data dictionary view can be used to find the names of all tables in the database? 1. USER_TABLES 2. ALL_TABLES 3. DBA_TABLES 4. ANY_TABLES

Copyright © 2009, Oracle. All rights reserved. 4 - 51 Summary In this lesson, you should have learned how to: • Start and stop the Oracle database and components • Use Oracle Enterprise Manager • Access a database with SQL*Plus • Modify database initialization parameters • Describe the stages of database startup • Describe database shutdown options • View the alert log • Access dynamic performance views

Copyright © 2009, Oracle. All rights reserved. 4 - 52 Practice 4 Overview: Managing the Oracle Instance This practice covers the following topics: • Navigating in Enterprise Manager • Viewing and modifying initialization parameters • Stopping and starting the database instance • Viewing the alert log • Connecting to the database by using SQL*Plus

Less04_Database_Instance.ppt

More Related Content

Similar to Less04_Database_Instance.ppt

Recently uploaded

Less04_Database_Instance.ppt