Oracle8i Administration

1. Oracle8i Administration 포스데이타 광양운영팀 권 태 삼

2. Lesson 1Oracle Architectural Components

3. ■ Overview Instance User Process SGA Shared Pool Library Cache Data buffer Cache Redo Log Buffer Server Process Data Dict Cache PGA SMON DBW0 PMON CKPT LGWR Others Database Parameter file Data file Control file Redo log file Password file Archived Log file

4. ■ Overview • Oracle Server • Oracle Instance and Oracle Database • Oracle Instance • Memory structure: SGA(Shared Global Area) • 데이터 베이스 프로세스에 의해 공유되는 데이터베이스 정보를 저장 • Background Process • Database file • 데이터베이스 정보를 저장하기 위해 제공되는 OS File System • Server Process • User Process로 부터 보내어진 SQL 문을 실행하는 프로세스

5. ■ Oracle Database Files Database Parameter file Data file Control file Redo log file Password file • Data File • 데이터베이스 내에 실제 데이터 • 데이터는 사용자 정의 테이블에 저장 • 데이터 파일은 데이터 사전 포함 • Redo Log File • 데이터를 복구하기 위해 데이터베이스 변경 기록을 보관 • 데이터베이스는 최소 두개의 Redo log file이 필요 • Control File • 데이터베이스의 무결성을 유지하기 위해 필요한 정보를 포함 • 데이터베이스 구조정보 • 동기화정보 • 데이터베이스는 최소한 하나의 control file을 포함 Archived Log file

6. ■ Oracle Instance Instance SGA Shared Pool Memory structures Library Cache • Oracle database에 접근하기 위한 수단 • 항상 하나의 데이터베이스만을 Open • Oracle Instance • SGA Memory Structure • Background Process Data buffer Cache Redo Log Buffer Data Dict Cache Background Processes SMON DBW0 PMON CKPT LGWR Others

7. ■ Oracle Instance • SGA • 데이터베이스 프로세스에 의해 공유되는 데이터베이스 정보를 저장하기 위해 사용되는 메모리 영역 • Shared Pool • Library Cache: 최근에 실행된 SQL 문장을 보관 • Data Dictionary Cache: 데이터 사전으로부터 가장 최근에 사용된 데이터 보관 • Data Buffer Cache: 가장 최근에 사용된 데이터를 보관 • Redo log Buffer: 데이터베이스의 변경된 정보를 보관 • Background Process • 동시 접속 사용자의 서비스 요청에 필요한 기능을 수행 • Database Writer(DBW0): database buffer cache의 변경된 내용을 데이터 파일에 저장 • Log Writer(LGWR): redo log buffer에 등록된 변경사항을 redo log file에 보관 • System Monitor(SMON): 데이터베이스 일관성 체크 • Process Monitor • Checkpoint Process(CKPT): control file에 데이터베이스 상태 정보를 갱신, buffer cache내에 변경사항을 data file에 갱신

8. ■ Processing a Query • Parse • Search for identical statement • Check syntax, object names, and privileges • Lock objects used during parse • Create and store execution plan • Execute • Identify rows selected • Fetch • Return rows to user process

9. ■ The Shared Pool Shared Pool • Library Cache(Shared SQL Area) • Text of the SQL statement • Parse tree • Execution plan • Reduce parse time and memory requirement • Data Dictionary Cache(Dictionary Cache, Row cache) • 최근에 사용된 테이블에 대한 정보 보관 • Tables, indexes, columns, users, privileges, other database objects • SHARED_POOL_SIZE 파라미터로 size를 정할 수 있음. Library Cache Data Dict Cache

10. ■ Database Buffer Cache • 최근에 사용된 block을 보관 • DB_BLOCK_SIZE 파라미터를 이용해 block size 변경 • DB_BLOCK_BUFFES 파라미터를 이용해 buffer 수 변경 ■Program Global Area(PGA) • Server Process에 의해서만 쓰여짐 • Sort area, session information, cursor state, stack space Server Process PGA

11. ■ Processing a DML Statement User Process SGA Shared Pool Data buffer Cache Redo Log Buffer • DML execute phase • 필요로 하는 block이 buffer cache에 있는지 없는지 check • 수정하기 위해 row를 locking • Data 변경사항을 redo log buffer에 기록 • Server process가 rollback block에 before image를 기록, data block을 update UPDATAE emp … 3 4 1 2 Server Process Database Data file Control file Redo log file 1

12. ■ Redo Log Buffer • Size defined by LOG_BUFFER • Records changes made through the instance • Used sequentially • Is a circular buffer ■Rollback Segment • Server process saves the old data value into a rollback segment • Undo the changes if the transaction is rolled back • Provide read consistency • Instance Recovery

13. ■ COMMIT Processing Instance 1 SGA Shared Pool Server Process Data buffer Cache Redo Log Buffer 4 • Step in Processing COMMIT • Server process가 SCN을 가진 commit record를 redo log buffer에 기록 • LGWR이 redo log buffer의 내용을 redo log file에 기록 • User에게 COMMIT 완료를 통보 • Resource lock을 release 3 LGWR User Process 2 Database Data file Control file Redo log file

14. ■ Log Writer(LGWR) Instance SGA Shared Pool Data buffer Cache Redo Log Buffer • LGWR writes when • When a transaction commits • When the redo log buffer is one-third full • When there is more than a megabyte of changes recorded in the redo log buffer • Before DBW0 writes modified blocks to the data files LGWR Database Data file Control file Redo log file

15. ■ Database Writer(DBW0) Instance SGA Shared Pool Data buffer Cache Redo Log Buffer • DBW0 writes when • There are many dirty buffers • There are few free buffers • Timeout occurs(every three seconds) • Checkpoint occurs DBW0 Database Data file Control file Redo log file

16. ■ SMON(System Monitor) • Automatically recovers the instance • Roll forward changes in the redo logs • Opens the database for user process • Rolls back uncommitted transactions • Coalesces free space • Deallocates temporary segments ■ PMON(Process Monitor) • Cleans up after failed processes by • Rolling back the transaction • Releasing locks • Releasing other resources

17. Lesson 2Getting Started with the Oracle Server

18. ■ Users SYS and SYSTEM • SYS • Password: change_on_install • Owner of the database data dictionary • SYSTEM • Password: manager • Owner of additional internal tables and views used by Oracle tools ■ Authentication • OS 인증 • Password 인증 • Remote로 접속 시 password file을 인증 받아야 접속 가능 • OS의 DBA 그룹이 아니면서 sysdba, sysoper role을 가지고 있는 user • DB 인증

19. ■ Operating System Authentication • Set up the user to be authenticated by the operating system • Set REMOTE_LOGIN_PASSWORDFILE to NONE • Use the following commands to connect to a database CONNECT / AS SYSDBA CONNECT / AS SYSOPER sqlplus internal

20. ■ Using Password File Authentication • Create the password file using the password utility • Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED • Use the following command to connect to a database $orapwd file=$ORACLE_HOME/dbs/orapwSID password=admin entries=10 CONNECT INTERNAL/ORACLE

21. Lesson 3Managing an Oracle Instance

22. ■ Overview of Start Up and Shut Down an Oracle Server • Start up • Start an instance • Mount the database • Open the database • Shut down • Close the database • Dismount the database • Shut down the instance

23. ■ The Initialization Parameter File Instance SGA Shared Pool Data buffer Cache Redo Log Buffer • Size the System Global Area(SGA) • Set database and instance defaults • Set user or process limits • Set limits on database resources • Define various physical attributes of the database(database block size) • Specify control files, archived log files, the ALERT file, and trace file locations initU15.ora SMON DBW0 PMON CKPT LGWR ARCH SQL> CONNECT / AS SYSDBA SQL> STARTUP PFILE=/DISK1/initU15.ora

24. ■ Stages in Startup and Shutdown OPEN Data file, Redo log file 검증 All files opened as described by the control file for this instance STRATUP MOUNT control file 읽음 • Starting Up in Stages • Starting the Instance • parameter file 읽음 • SGA와 background process를 띄움 • ALERT file, trace file을 open • Database와 control file을 생성 • Mounting the Database • Renaming data file • Redo log archived option 설정 • full database recovery 수행 • Opening the Database • Open the data file • Open the redo log file • database 일관성 check • 필요하다면 SMON이 instance recovery 수행 Control file opened for this instance NOMOUNT parameter file 읽음 Instance started SHUTDOWN SHUTDOWN

25. ■ Stages in Startup and Shutdown • Shutting Down in Stages • Closing the Database • buffer cache의 변경사항을 data file에 보관 • redo log file 내의 entries를 redo log file에 보관 • data file, redo log file close • Dismounting the Database • Database dismount • Control file close • Shutting Down the Instance • Instance Shutting down • ALERT file, trace file close • SGA deallocate • background process terminate • ALERT file • DB start, shutdown 시간 기록 • startup, shutdown 시 error 기록

26. ■ Starting Up the Instance STARTUP PFILE=$ORACLEHOME/DISK1/initU15.ora • STARTUP Command • ALTER DATABASE Command • Opening a Database in Read-Only Mode • Execute queries • Execute disk sorts using locally managed tablespaces • Take data files offline and online • Perform recovery of offline data files and tablespaces ALTER DATABASE database MOUNT; ALTER DATABASE database OPEN READ ONLY;

27. ■ Shutting Down • Shutdown Option • Shutdown mode A Abort I Immediate T Transactional N Normal

28. ■ Getting and Setting Parameter Values • Dynamic Performance View • Oracle Server에 의해 관리되고, 계속적으로 update • 디스크와 메모리 구조에 대한 데이터를 포함 • performance tuning을 위해 사용되는 데이터를 포함 • Have public synonyms with the prefix V$ V$PARAMETER V$SGA V$OPTION V$PROCESS V$VERSION V$INSTANCE SGA V$THREAD V$CONTROLFILE V$DATABASE V$DATAFILE V$DATAFILE_HEADER V$LOGFILE Control file

29. ■ Getting and Setting Parameter Values SHOW PARAMETER control • Displaying Current Parameter Values • Command • Dynamic performance view V$PARAMETER를 이용한 query • Dynamic Initialization Parameters • instance 실행동안에 initialization parameter를 수정 SELECT name FROM V$PARAMETER ALTER SYSTEM SET TIMED_STATISTICS=true; ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED;

30. ■ Managing Sessions STARTUP RESTRICT • Enable and Disable Restricted Session • Command • Mode 변경 • Terminating Sessions • Session 확인 • Session Terminate ALTER SYSTEM DISABLE RESTRICTED SESSION; SELECT id,serial# FROM V$SESSION WHERE username=‘SCOTT’; ALTER SYSTEM KILL SESSION ‘7,15’;

31. ■ Managing Sessions • ALERT File and Trace File • Trace file은 서버와 background process에 의해 작성 • Oracle Server는 trace file 내에 error 정보를 보관 • ALERT file은 메시지와 에러로 구성 • Server process tracing을 변경 ALTER SESSION SET sql_trace=TRUE;

32. Lesson 4Creating a Database

33. ■ Overview • Database System을 관리하고, 조직하는데 첫번째 단계 • Database 생성은 많은 data file(OS file)이 필요함 • Database 생성 후 변경할 수 없는 database block size와 database character set과 같은 database 설정을 결정한 후 database를 생성 • 다음과 같은 방법으로 인증이 되어야 database를 생성할 수 있음 • operating system • Using a password file • Instance allocate를 위한 Memory가 충분해야 • Database 공간을 위한 충분한 디스크 공간이 있어야

34. ■ Creating a Database Manually • 유일한 instance, 데이터베이스 이름, 데이터베이스 character set을 결정 • OS 변수 설정 • parameter file 준비 • password file 생성 • Instance 실행 • 데이터베이스 생성 • Data Dictionary 생성을 위한 scripts 실행과 차후 단계 실행

35. ■ Creating a Database Manually • OS 변수 설정 • Parameter file 준비 • 새로운 initSID.ora 생성 • vi 에디터를 이용해 initSID.ora 파일 수정 • DB_NAME, CONTROL_FILES, DB_BLOCK_SIZE는 반드시 수정 $cp init.ora $ORACLE_HOME/dba/initU16.ora

36. ■ Creating a Database Manually SQL>STARTUP NOMOUNT PFILE=initU16.ora • Instance 실행 • SYSDBA로 접속 • NOMOUNT 단계에서 Instance 실행 • 데이터베이스 생성 CREATE DATABASE U16 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXLOGHISTORY 100 LOGFILE GROUP 1 (‘/DISK3/log1a.rdo’, /DISK4/log1b.rdo’) SIZE 1 M, GROUP 2 (‘/DISK3/log2a.rdo’, /DISK4/log2b.rdo’) SIZE 1 M DATAFILE ‘/DISK1/system01.dbf’ size 50M autoextend on CHARACTER SET WE8ISO8859P1;

37. Lesson 5Creating Data Dictionary Views and Standard Packages

38. ■ Overview • Data Dictionary • 데이터베이스 내에 객체들에 대한 설명을 포함 • Base tables • Data Dictionary views • Dynamic Performance table • 데이터베이스와 instance를 모니터하고 tuning 하기 위해 DBA에 의해 사용되는 정보를 포함 • Built-in PL/SQL packages

39. ■ Data Dictionary Overview • Oracle Database 중에 가장 중요한 부분 • 데이터베이스와 데이터베이스의 object들에 대한 정보 제공 • read-only table과 view를 포함 • SQL Command에 의해 update • DDL(Data Definition Language) • Some DML(Data Manipulation Language) • SYS user가 소유 • SYSTEM tablespace 내에 저장 • SELECT 문만을 사용

40. ■ Data Dictionary Contents • Logical and physical database structure • Definition and space allocations of objects • Integrity constraints • Users의 이름 • 각 user에게 할당된 Roles • 각 user에게 할당된 Privileges • Auditing • Other Information

41. ■ How the Data Dictionary is Used • Oracle server uses it to find information about • Users • Schema Objects(tables, views, indexes, clusters, synonyms, procedures, and so on) • Storage structures • Oracle Server는 DDL 문이 실행될 때 Data Dictionary를 수정 • User와 DBA는 데이터베이스에 대한 정보를 얻고자 할 때 사용 • Data Dictionary Example • General Overview • DICTIONARY • DICT_COLUMNS • Schema objects • DBA_TABLES • DBA_TAB_COLUMNS • DBA_OBJECTS • DBA_CONSTRAINTS • Space allocation • DBA_SEGMENTS • DBA_EXTENTS • DBA_FREE_SPACE • Database Structure • DBA_DATA_FILES • DBA_ROLLBACK_SEGS • DBA_TABLESPACES

42. ■ Constructing the Data Dictionary • Creating Data Dictionary Views • Administrative Scripts

43. Lesson 6Maintaining the Control File

44. ■ The Use of the Control File • 데이터베이스를 성공적으로 동작시키기 위해 필요한 작은 binary file • 각 control file은 하나의 database와 link • 데이터베이스가 사용되는 동안 Oracle Server에 의해 계속적으로 update • control file 내의 정보는 Oracle Server에 의해서만 수정 • Should be multiplexed • Contents • Database name and identifier • Database creation date • Data file and redo log file locations • Tablespace names • Log history is recored during log switches • Location and status of archived logs • Backup information • Current log sequence number • Checkpoint information

45. ■ How to Add a Control File • Shut down the database • Copy the current control file using an operating system command • Add the new control file names to the CONTROL_FILES parameter • Start up the database ■ Guidelines for Control Files • Multiplex the control file • Include the full pathname in CONTROL_FILES • Back up the control file after the database structure changes

46. ■ Obtaining Information About the Control File SELECT name FROM v$controlfile; • V$CONTROLFILE • V$CONTROLFILE_RECORD_SECTION • Control File로 부터 많은 Performance View에 대한 정보를 얻음 • V$BACKUP • V$DATAFILE • V$TEMPFILE • V$TABLESPACE • V$ARCHIVE • V$LOG • V$LOGFILE • V$LOGHIST • V$ARCHIVED_LOG • V$DATABASE • Others

47. Lesson 7Maintaining Redo Log Files

48. ■ Overview • Oracle Server는 데이터베이스의 데이터 손실을 최소화하기 위해 online redo log file을 관리 • database buffer cache 내에 모든 데이터 변경 사항을 기록 • instance failure와 같은 상황에서 data file에는 쓰여지지 않고, commit 된 데이터를 복구하기 위해 사용 • recovery를 위해서만 사용 • Redo Log Groups and Members • Redo Log Groups • online redo log file들의 집합을 online redo log group • LGWR Process는 그룹 내 모든 online redo log files에 같은 정보를 동시에 작성 • Oracle Server에는 최소 두개의 online redo log file groups가 있어야 • Redo Log Members • 그룹 내 각 online redo log file을 member라 함 • 그룹 내 각 member는 동일한 log sequence number를 가지고 있고, 같은 size • 현재 log sequence number는 control file과 모든 data files의 헤더에 저장

49. ■ LGWR, Log Switches, and Checkpoints Instance SGA Shared Pool Data buffer Cache Redo Log Buffer DBWR CKPT LGWR ARCH Database Parameter file Data file Control file Redo log file Archived log file Password file

50. ■ LGWR, Log Switches, and Checkpoints • LGWR • transaction commit • redo log buffer가 1/3이상 full • redo log buffer에 기록된 변경사항이 1M 이상일때 • timeout(every three second) • DBWn이 동작하기 전에 • Log Switches • LGWR이 하나의 online redo log group에 작성하는 것을 멈추고, 다른 online redo log group에 작성하는 event • Log switch가 발생하면 checkpoint가 시작 • Checkpoint • CKPT는 모든 데이터파일의 header와 control file의 header를 update • Checkpoint는 다음과 같은 상황에서 발생 • 모든 log switch • instance가 normal, transactional, immediate 옵션을 가지고 shut down되었을 때 • LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, FAST_START_IO_TARGET 파라미터 설정에 따라 • DBA의 요청에 의해 • checkpoint에 대한 정보는 LOG_CHECKPOINTS_TO_ALERT 파리미터가 TRUE로 설정 되어있으면 ALERT 파일에 기록된다.