1 / 57

Comprehensive Guide to Oracle Database Administration: Key Considerations and Best Practices

This guide explores essential strategies for effective Oracle Database Administration (DBA). Learn how proactive DBAs minimize after-hours work through routine checks and health assessments of the database environment. Key areas include monitoring growth patterns, alert log analysis for errors, and ensuring performance through regular health checks across development, testing, and production systems. Understand how to utilize various DBA views to manage database performance and troubleshoot issues effectively, incorporating a holistic management approach that prioritizes user experience and operational efficiency.

meena
Download Presentation

Comprehensive Guide to Oracle Database Administration: Key Considerations and Best Practices

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

E N D

Presentation Transcript

  1. Oracle Database Administration Session 9 Managing the Environment

  2. DBA Goals • A proactive DBA reduces the number of weekends and off-hours worked • Setup some routines • Review the database to see what is ‘normal’ • What are the growth patterns • Number of users logged in • The CPU and memory usage

  3. DBA Goals • The disks ‘hit’ the most • Check the alert.log for ora errors • Check for trace files • When you do these things regularly, you will notice the abnormal more easily

  4. Health Checks • Database • Application • System

  5. Release Management • Development • Test/Integration • Production • Patch • Interfaces • Conversion • Security

  6. Release Management • Systems • O/S • Backend • Middle Tier • Number of Users • Batch schedule • Performance Test

  7. Management Framework • Monitoring Framework • Automation • Batch Control • Scheduling • Production Control

  8. Environment Management • Wholistic Approach • Database • Middle Tier • Data Loading • Linked databases • Backups

  9. Environment Management • Application User perspective • Total number of Users • Max concurrent Users • Length of data loads • Uptime requirement • Must be available at…… • Manage to their requirements

  10. DBA Tasks • Is it running…… • “ps –eaf | grep dbw” tests to see if the database is up and running • “ps –eaf | grep lsnrctl” tests if the listener is running • sqlplus user@<ORACLE_SID> or some user Password ******* Tests if you can connect to the database

  11. Alert Log • Alert.log • Located in /$ORACLE_BASE/admin/<SID>/diag/bdump • It shows when the database was started • When a log switch occurred • Shows the non-default parameters from the init.ora

  12. Alert Log • Any ddl commands to change the structure of the database • Space allocation errors • When trace files were created and where they are located • When the database was shutdown or started

  13. Alert Log • This file is not adjusted by Oracle, it is always appended to, until you change it. • You can rename it, edit it, or reduce it’s size • When it is renamed, Oracle will create a new file automatically

  14. Trace Files • When there are internal errors in a user session or with a background process, Oracle creates a trace file • They are to help in debugging a problem • Trace files, created by a background process, will have the name of the background process as part of the file name

  15. Trace Files • The trace file of a user process will have the process ID, in the file name • The following init.ora parameters relate to trace files • max_dump_file_size unlimited size • background_dump_dest background processes • user_dump_dest user sessions traces

  16. Type of Views • Dba_ • User_ • All_ • V$…. • X$..

  17. Views • dba_segments • dba_rollback_segs • dba_objects • v$rollstat • v$rollname

  18. Views • dba_extents • dba_tables • dba_indexes • v$sqltext

  19. Instance Level • v$instance • v$database • v$sga • v$sgastat • v$parameter • v$option • v$version • v$license

  20. Session Level • v$session • v$transaction • v$process • v$access

  21. Database Structure • v$tablespace • v$datafile • v$dbfile • v$dblink • v$datafile_header

  22. Performance • v$waitstat • v$filestat • v$sysstat

  23. All V$ Views • select name from v$fixed_table where name like ‘v%' order by name

  24. Example of a View CREATE OR REPLACE VIEW DBA_SEGMENTS ( OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, RELATIVE_FNO, BUFFER_POOL ) AS select owner, segment_name, partition_name, segment_type, tablespace_name, header_file, header_block, bytes, blocks, extents, initial_extent, next_extent, min_extents, max_extents, pct_increase, freelists,freelist_groups, relative_fno, buffer_pool from sys_dba_segs

  25. dba_segments Name Null? Type ----------------------------------------- -------- ------------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(7)

  26. Examples • select substr(segment_name,1,19) segment_name, extents from dba_segments where tablespace_name = ‘TS‘ • select substr(table_name,1,19) table_name, freelists from dba_tables where tablespace_name = ‘TS'

  27. DBA Views set pagesize 9999 col owner format a10 col object_name format a40 select owner,object_name,object_type from dba_objects where (object_name like 'DBA_%' or object_name like 'V$%' ) and owner in ('SYS', 'SYSTEM', 'PUBLIC') order by owner,object_name

  28. Space Management • A database stores data • We must have enough free space available, to ensure that will continue to occur • Tablespace available • Segment space available inside the tablespace • Can we extend each segment

  29. Storage Information • select segment_name, segment_type,extents from dba_segments where tablespace_name = ‘DATA‘ and segment_type = ‘TABLE' • This will tell you what number of extents each table has, in a specific tablespace

  30. Storage Information • Change table to index and get the values for indexes

  31. ALTER TABLESPACE Use this command to alter an existing tablespace in one of the following ways: * to add datafile(s) * to rename datafiles * to change default storage parameters * to take the tablespace online or offline * to begin or end a backup * to allow or disallow writing to a tablespace * to change the default logging attribute of the tablespace * to change the minimum tablespace extent length

  32. Tablespace management ALTER TABLESPACE tablespace {ADD DATAFILE 'filespec‘ [AUTOEXTEND {OFF | ON [NEXT integer [K | M] ] [MAXSIZE { UNLIMITED | integer [K | M] } ] } ] [, 'filespec' [AUTOEXTEND {OFF | ON [NEXT integer [K | M] ] [MAXSIZE {UNLIMITED | integer [K | M] } ] } ] ] ...

  33. Tablespace management | RENAME DATAFILE 'filename' [, 'filename'] ... TO 'filename' ['filename'] ... | COALESCE | DEFAULT STORAGE storage_clause | MINIMUM EXTENT integer [K | M] | ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE] | {BEGIN | END} BACKUP | READ {ONLY | WRITE} | PERMANENT | TEMPORARY}

  34. Tablespace management example • CREATE TABLESPACE TOOLS DATAFILE '/u51/oradata/dev/tools01.dbf' SIZE 50M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE LOGGING ONLINE

  35. Tablespace management example • ALTER TABLESPACE TOOLS ADD DATAFILE '/u51/oradata/dev/tools02.dbf' SIZE 200M / • ALTER DATABASE DATAFILE '/u51/oradata/dev/tools01.dbf' RESIZE 100M /

  36. Tablespace management example • ALTER DATABASE DATAFILE '/u08/oradata/test/datafile01.dbf' RESIZE 200M; • ALTER DATABASE DATAFILE '/u08/oradata/test/datafile01.dbf' AUTOEXTEND ON • ALTER DATABASE DATAFILE '/u08/oradata/test/tools01.dbf' AUTOEXTEND ON NEXT 200M MAXSIZE 1000M

  37. Sql creating sql • select ' alter database datafile ''' || file_name || ''' resize ' || to_char ( bytes/1024/1024 + &Size ) || 'M ; ' from dba_data_files where tablespace_name = upper ( '&tablespace_name' ) and rownum < 2

  38. Create tablespace set heading off set verify off select distinct ( substr ( name, 1, 4 ) ) from v$datafile / select '&Press any key to continue ... ' from dual / Prompt Enter Tablespace_name : Accept Tablespace_name select ' create tablespace &&tablespace_name datafile ''/u&filename/oradata/'|| value || '/&&tablespace_name' || '01.dbf'' size &Size M; ' from dual, v$parameter where name = 'instance_name' /

  39. Object Status Examples • select count(*) from dba_objects where status = 'INVALID‘ • select object_name, object_type from dba_objects where status = 'INVALID'

  40. Object Modifications • select owner,object_name, object_type, status, timestamp from dba_objects where substr(timestamp,1,10) = to_char(sysdate-1, 'YYYY-MM-DD') order by owner,object_name • This will tell what objects changed since the day before

  41. User Sessions • desc v$session • SQL> select status, count(*) from v$session group by status; STATUS COUNT(*) -------- ---------- ACTIVE 18 INACTIVE 16

  42. User Sessions • A value of inactive means that no SQL statement is running at the moment • The total number of users connected is the addition of both count values • What is each user doing, etc

  43. Users • CREATE USER You can assign the following properties to the user: * default tablespace * temporary tablespace * quotas for allocating space in tablespaces

  44. Create User CREATE USER user IDENTIFIED {BY password | EXTERNALLY } [ DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE tablespace | QUOTA { integer [K | M] | UNLIMITED } ON tablespace [ QUOTA { integer [K | M] | UNLIMITED } ON tablespace ]

  45. Alter User ALTER USER user { IDENTIFIED {BY password | EXTERNALLY} | DEFAULT TABLESPACE tablespace | TEMPORARY TABLESPACE tablespace | QUOTA { integer [K | M] | UNLIMITED} ON tablespace [ QUOTA { integer [K | M] | UNLIMITED} ON tablespace] ... | DEFAULT ROLE { role [, role] ... | ALL [EXCEPT role [, role] ...] | NONE} } ...

  46. Roles • Connect role • Resource role • DBA role • Object privileges • System Privileges

  47. Connect • select * from dba_sys_privs where grantee = ‘connect’ • CONNECT CREATE VIEW NO • CONNECT CREATE TABLE NO • CONNECT ALTER SESSION NO • CONNECT CREATE CLUSTER NO • CONNECT CREATE SESSION NO • CONNECT CREATE SYNONYM NO • CONNECT CREATE SEQUENCE NO • CONNECT CREATE DATABASE LINK NO

  48. RESOURCE Role • select * from dba_sys_privs where grantee = ‘RESOURCE’ • RESOURCE CREATE TYPE NO • RESOURCE CREATE TABLE NO • RESOURCE CREATE CLUSTER NO • RESOURCE CREATE TRIGGER NO • RESOURCE CREATE OPERATOR NO • RESOURCE CREATE SEQUENCE NO • RESOURCE CREATE INDEXTYPE NO • RESOURCE CREATE PROCEDURE NO

  49. DBA Role • select * from dba_sys_privs where grantee = ‘DBA’

  50. Roles GRANT (Object Privileges) GRANT { object_priv | ALL [PRIVILEGES] } [ ( column [, column] ...) ] [, { object_priv | ALL [PRIVILEGES] } [ ( column [, column] ...) ] ] ... ON [ schema.| DIRECTORY] object TO { user | role | PUBLIC} ... [ WITH GRANT OPTION]

More Related