4 Managing the Oracle Instance Copyright 2007 Oracle

Objectives After completing this lesson, you should be able to: • Start and stop

Management Framework Oracle Database 11 g management framework components: • Database instance • Listener

Starting and Stopping Database Control $ emctl start dbconsole Oracle Enterprise Manager 11 g

Oracle Enterprise Manager 4 -5 Copyright © 2007, Oracle. All rights reserved.

Accessing Oracle Enterprise Manager 4 -6 Copyright © 2007, Oracle. All rights reserved.

Database Home Page Property pages 4 -7 Copyright © 2007, Oracle. All rights reserved.

Other Oracle Tools Components > SQL*Plus Init Params DB Startup DB Shutdown Alert Log

Using SQL*Plus is: • A command-line tool • Used interactively or in batch mode

Calling SQL*Plus from a Shell Script $. /batch_sqlplus. sh SQL*Plus: Release 11. 1. 0.

Calling a SQL Script from SQL*Plus script. sql select * from departments where location_id

Initialization Parameter Files spfileorcl. ora 4 - 12 Copyright © 2007, Oracle. All rights

Simplified Initialization Parameters Advanced Basic CONTROL_FILES DB_BLOCK_SIZE PROCESSES UNDO_MANAGEMENT … 4 - 14 DB_CACHE_SIZE

Initialization Parameters: Examples Parameter CONTROL_FILES Specifies DB_FILES Maximum number of database files PROCESSES Maximum

Initialization Parameters: Examples Parameter SGA_TARGET Specifies MEMORY_TARGET Oracle systemwide usable memory Total size of

Initialization Parameters: Examples Parameter PGA_AGGREGATE_TARGET Specifies SHARED_POOL_SIZE Size of shared pool (in bytes) UNDO_MANAGEMENT

Using SQL*Plus to View Parameters SQL> SELECT name , value FROM V$PARAMETER; NAME VALUE

Changing Initialization Parameter Values • Static parameters: – Can be changed only in the

Changing Parameter Values: Examples SQL> ALTER SESSION SET NLS_DATE_FORMAT ='mon dd yyyy'; Session altered.

Database Startup and Shutdown or 4 - 24 Copyright © 2007, Oracle. All rights

Starting Up an Oracle Database Instance 4 - 25 Copyright © 2007, Oracle. All

Starting Up an Oracle Database Instance: NOMOUNT OPEN STARTUP MOUNT NOMOUNT Instance started SHUTDOWN

Starting Up an Oracle Database Instance: MOUNT OPEN STARTUP MOUNT NOMOUNT Control file opened

Starting Up an Oracle Database Instance: OPEN STARTUP MOUNT NOMOUNT All files opened as

Startup Options: Examples 4 - 29 SQL> startup 1 SQL> startup nomount 2 SQL>

Shutting Down an Oracle Database Instance 4 - 30 Copyright © 2007, Oracle. All

Shutdown Modes A I T N Allows new connections No No Waits until current

Shutdown Options On the way down: • Uncommitted changes rolled back, for IMMEDIATE •

Shutdown Options: Examples SQL> shutdown transactional SQL> shutdown immediate SQL> shutdown abort 4 -

Shutdown Options On the way up: • Online redo SHUTDOWN ABORT log files used

Using SQL*Plus to Start Up and Shut Down [oracle@EDRSR 9 P 1 oracle]$ sqlplus

Blackout Administration 4 - 36 Copyright © 2007, Oracle. All rights reserved.

Viewing the Alert Log Database Home page > Related Links region > Alert Log

Viewing the Alert History 4 - 40 Copyright © 2007, Oracle. All rights reserved.

Using Trace Files • Each server and background process can write to an associated

Dynamic Performance Views Provide access to information about changing states of the instance memory

Dynamic Performance Views: Usage Examples a SQL> SELECT sql_text, executions FROM v$sql WHERE cpu_time

Dynamic Performance Views: Considerations • These views are owned by the SYS user. •

Data Dictionary: Overview Schema Constraints Indexes Views Sequences Temp Tables > Data Dict Tables

Data Dictionary Views Who Can Query Contents Subset of Notes DBA_ DBA Everything N/A

Data Dictionary: Usage Examples a SELECT table_name, tablespace_name FROM user_tables; b SELECT sequence_name, min_value,

Summary In this lesson, you should have learned how to: • Start and stop

Practice 4 Overview: Managing the Oracle Instance This practice covers the following topics: •

Slides: 43

Download presentation

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 4 -2 Copyright © 2007, Oracle. All rights reserved.

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

Starting and Stopping Database Control $ emctl start dbconsole Oracle Enterprise Manager 11 g Database Control Release 11. 1. 0 Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved. https: //edrsr 17 p 1. us. oracle. com: 1158/em/console/about. Application Starting Oracle Enterprise Manager 11 g Database Control. . . started. ---------------------------------Logs are generated in directory /u 01/app/oracle/product/11. 1. 0/db_1/ edrsr 17 p 1. us. oracle_orcl/sysman/log $ emctl stop dbconsole Oracle Enterprise Manager 11 g Database Control Release 11. 1. 0 Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved. https: //edrsr 17 p 1. us. oracle. com: 1158/em/console/about. Application Stopping Oracle Enterprise Manager 11 g Database Control. . . Stopped. 4 -4 Copyright © 2007, Oracle. All rights reserved.

Other Oracle Tools Components > SQL*Plus Init Params DB Startup DB Shutdown Alert Log Perf Views 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 4 -8 Copyright © 2007, Oracle. All rights reserved.

Using SQL*Plus is: • A command-line tool • Used interactively or in batch mode $ sqlplus hr/hr SQL*Plus: Release 11. 1. 0. 3. 0 - Beta on Wed May 30 21: 41: 24 2007 Copyright (c) 1982, 2006, Oracle. All rights reserved. Connected to: Oracle Database 11 g Enterprise Edition Release 11. 1. 0. 3. 0 - Beta With the Partitioning, OLAP and Data Mining options SQL> select last_name from employees; LAST_NAME ------------Abel Ande Atkinson … 4 -9 Copyright © 2007, Oracle. All rights reserved.

Calling SQL*Plus from a Shell Script $. /batch_sqlplus. sh SQL*Plus: Release 11. 1. 0. 3. 0 - Beta on Wed May 30 21: 41: 24 2007 Copyright (c) 1982, 2006, Oracle. All rights reserved. Connected to: Oracle Database 11 g Enterprise Edition Release 11. 1. 0. 3. 0 - Beta With the Partitioning, OLAP and Data Mining options # Name of this file: batch_sqlplus. sh SQL> # Count employees and give raise. COUNT(*) sqlplus hr/hr <<EOF -----select count(*) from employees; 107 update employees set salary = SQL> salary*1. 10; 107 rows updated. commit; SQL> quit Commit complete. EOF SQL> Disconnected from Oracle Database exit 11 g Enterprise Edition Release 11. 1. 0. 3. 0 - Beta With the Partitioning, OLAP and Data Mining options [oracle@EDRSR 9 P 1 oracle]$ 4 - 10 Copyright © 2007, Oracle. All rights reserved. Output

Calling a SQL Script from SQL*Plus script. sql select * from departments where location_id = 1400; quit Output $ sqlplus hr/hr @script. sql SQL*Plus: Release 11. 1. 0. 3. 0 - Beta on Wed May 30 21: 41: 24 2007 Copyright (c) 1982, 2006, Oracle. All rights reserved. Connected to: Oracle Database 11 g Enterprise Edition Release 11. 1. 0. 3. 0 - Beta With the Partitioning, OLAP and Data Mining options DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ----------------------60 IT 103 1400 Disconnected from Oracle Database 11 g Enterprise Edition Release 11. 1. 0. 3. 0 - Beta With the Partitioning, OLAP and Data Mining options 4 - 11 Copyright © 2007, Oracle. All rights reserved.

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

Initialization Parameters: Examples Parameter CONTROL_FILES Specifies 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 4 - 15 One or more control file names Copyright © 2007, Oracle. All rights reserved.

Initialization Parameters: Examples Parameter SGA_TARGET Specifies MEMORY_TARGET Oracle systemwide usable memory Total size of all SGA components Shared SQL area Library cache Database buffer cache 4 - 16 Other Shared pool Redo log buffer Java pool Data dictionary cache Streams pool I/O buffer Free memory Response queue Request queue Large pool Copyright © 2007, Oracle. All rights reserved. SGA

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

Using SQL*Plus to View Parameters SQL> SELECT name , value FROM V$PARAMETER; NAME VALUE ----------lock_name_space 2 processes 150 sessions 170 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 ------------------fast_start_parallel_rollback parallel_adaptive_multi_user parallel_automatic_tuning parallel_execution_message_size parallel_instance_group … 4 - 19 TYPE -----string boolean integer string VALUE ----------LOW TRUE FALSE 2148 Copyright © 2007, Oracle. All rights reserved.

Changing Initialization Parameter Values • Static parameters: – Can be changed only in the parameter file – Require restarting the instance before taking effect • 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 4 - 21 Copyright © 2007, Oracle. All rights reserved.

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

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

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

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

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

Using SQL*Plus to Start Up and Shut Down [oracle@EDRSR 9 P 1 oracle]$ sqlplus dba 1/oracle as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened. SQL> 4 - 35 285212672 1218472 250177624 33554432 262144 bytes bytes Copyright © 2007, Oracle. All rights reserved.

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

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: — — — 4 - 41 Traces Alert log Health monitor reports Copyright © 2007, Oracle. All rights reserved.

Dynamic Performance Views Provide access to information about changing states of the instance memory structures Components SQL*Plus Init Params DB Startup DB Shutdown Alert Log > Perf Views Session data Wait events Memory allocations Running SQL UNDO usage Open cursors Redo log usage …and so on Oracle instance 4 - 43 Copyright © 2007, Oracle. All rights reserved.

Dynamic Performance Views: Usage Examples a SQL> SELECT sql_text, executions FROM v$sql WHERE cpu_time > 200000; b SQL> SELECT * FROM v$session WHERE machine = 'EDRSR 9 P 1' and logon_time > SYSDATE - 1; c SQL> SELECT sid, ctime FROM v$lock WHERE block > 0; 4 - 44 Copyright © 2007, Oracle. All rights reserved.

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. 4 - 45 Copyright © 2007, Oracle. All rights reserved.

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

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 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. ) 4 - 47 Copyright © 2007, Oracle. All rights reserved.

Data Dictionary: Usage Examples a SELECT table_name, tablespace_name FROM user_tables; b SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS', 'XDB'); c SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS = 'OPEN'; d DESCRIBE dba_indexes; 4 - 49 Copyright © 2007, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Start and stop the Oracle database and components • Use Enterprise Manager and describe its high-level functionality • 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 4 - 50 Copyright © 2007, Oracle. All rights reserved.

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 4 - 51 Copyright © 2007, Oracle. All rights reserved.