Oracle Database Administration

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]