Saturday, April 18, 2009

Kiến trúc ORACLE Admin 9i

1. Oracle Architectual

Oracle Server

A. Oracle Database (Storage)

A.1 PHYSICAL

a. Datafile: luu du lieu nguoi dung & luu du lieu Oracle

b. Redologfile

c. Controlfile

d. init parameter file: text & binary

e. password file

f. archived log file

g. alert$ORACLE_SID.log

i. trace file

j. dump

k. backup file

A.2 LOGICAL

Database > Tablespace > Segments ( Table, Index) > Extents > Oracle Block > OS block

B. Oracle Instance

B.1 Memory

a. SGA

- Shared-pool

_ Library Cache: shared sql hoac PLSQL

_ Dictionary Cache: mo ta objects, privs, ...

- Buffer-Cache: cache block duoc doc / ghi

- Redolog-Buffer: ghi moi thay doi len data-block

- Java-Pool: java

- Large-Pool: big objects

b. PGA: khi server process sinh ra, chua thong tin noi bo server process

B.2 Process

a. background process

SMON

PMON

CKPT

DBWRn

LGWR

ARCHn : ARCHIVED LOG mode

b. server process

- dedicated server: 1 server process = 1user process

- shared server: 1 server process = 20 - 30 user process

- General (Hybrid)

L2: Getting Started With Oracle Server

1. SQL* PLUS: interactive & manipulate Oracle Server

2. Oracle Universall Install (OUI): install & remove oracle software, create database (dbca)

3. dbca: create, delete, configure & manage template oracle databae

4. Oracle Enterprise Manager (OEM)

- single database (standalone): quan tri tung database rieng le, khong can cau hinh

- Oracle Management Server (OMS): - quan tri nhieu database tren 1 giao dien chung thong nhat, chi can login 1 lan co the quan tri nhieu database,

- phai cau hinh, phai cai Agent tren cac may Oracle can quan ly va phai cai Oracle Repository)

L3. Managing Oracle Instance

1. pfile / spfile: tham so cau hinh Oracle Instance

- pfile: text, duoc doc khi STARTUP

- spfile: binary, ALTER SYSTEM set shared_pool_size=200M scope=spfile|memory|both

- spfile$ORACLE_SID.ora > spfile.ora > init$ORACLE_SID.ora > init.ora

- Vi tri file tham so:

$ORACLE_HOME/dbs/

2. STARTUP

- STARTUP NOMOUNT: ( STARTED) (select status from v$instance)

doc file: init parameter file

ghi file: alert.log

failed: khong tim thay init parameter file hoac loi cau tham so

- STARTUP MOUNT: (MOUNTED)

doc file: controlfile file

ghi file: alert.log

failed: khong tim thay Controlfile hoac CF bi loi

- ALTER DATABASE OPEN: (OPEN)

doc file: datafile & redologfile

ghi file: alert.log

failed: khong tim thay datafile & redologfile hoac datafile, redologfile bi loi

Vi du:

cannot access file : test01.dbf

==> file bi loi ==> OPEN ==> OFFLINE test01.dbf ==> OPEN

alter database datafile '/u01/oradata/ora9i/test01.dbf' offline;

3. SHUTDOWN

A: no wait, but not safe (mat dien may chu) (RECOVER AUTOMATIC DATABASE)

I: cho ghi du lieu da comit & dong bo file

T: co Immediate va khong cho Transaction moi

N: co T va khong cho phep Session moi

4. Xem log

background_dump_dest: vi tri alert.log

background_dump_dest: vi tri luu trace file do background process sinh ra

user_dump_dest: luu vi tri trace file do user process & server process sinh ra

Lession 4: Creating Database

1. Plan

- Purpose: dev, prod, test

- Type: Data warehouse (DSS), OLTP (shared-pool, block_size) , Hybrid

- hardware, growth ... chi tiet ( 80 GB)

- Security: features (XML db, JVM, ...) ==> default accounts ==> default password

2. Co 2 cach tao database

- dbca: GUI, them - xoa - sua - template

- memory: shared-pool, buffer

- location & size

- tablespaces

- charactset: VN8VN3, UTF8

- archive-log mode

- dedicated | shared

- generates scripts ==> $ORACLE_BASE/admin/$ORACLE_SID/scripts/

ora9i.sh: moi cai file deu co ten database ==> doi ten db_name trong tat file

- dbcreate.sql

- dbpostcreation.sql

- create template

- CREATE DATABASE

a. log group, logfile, controlfile, datafile SYSTEM

b. catalog.sql: data dictionary views

catproc.sql: PLSQL packages

Lession 5: Data Dictionary

- Based-tables: ten cot rat kho hieu, thuoc ve SYS, chi duoc truy cap, thay doi boi chinh Oracle

sys.user$ ==> dba_users

- Data Dictionary View: thong tin mo ta ve Oracle, ten cot de hieu, thuong duoc truy cap DBAs

- catalog.sql ==> $ORACLE_HOME/rdbms/admin/

dbms*.sql: thu vien PL/SQL

utl*.sql: tien ich

cat*.sql: danh muc du lieu

prvt*.sql: thu vien lenh dang duoc ma hoa

- select tu based-tables

[dba | all | user]_tables

dba_: co tat ca object

all_: chi nhung object ma tai khoan truy cap

user_: chi co nhung objects ma do chinh tai khoan tao ra

SYS-SQL> SELECT COUNT(*) FROM ALL_tables;

HR-SQL> SELECT COUNT(*) FROM ALL_tables;

HR-SQL> SELECT COUNT(*) FROM user_tables;

- Dynamic Performance View:

- doc tin truc tu bo nho & controlfile

- V$

- phan anh thay doi cua Oracle mang tinh real-time

- v$sga

- v$versions

- v$sessions

- v$datafile

- v$tablespace

- Ky thuat sinh ma

vi du:

- SELECT 'DROP TABLE ' || table_name || ';'

FROM dba_tables

WHERE owner='HR';

- CONNECT hr/hr

SELECT 'DROP TABLE ' || table_name || ';'

FROM user_tables;

- exp hr/hr tables=(employees) file=employees.dmp

- SELECT 'exp hr/hr tables=(' || table_name || ') file=' || table_name || '.dmp'

FROM user_tables;

- SELECT 'exp system/oracle owner=(' || username || ') file=' || username || '.dmp data=y'

FROM dba_users;

Lession 6: Controlfile

- CF: luu thong tin cau truc vat ly database

- CREATE CONTROL FILE: so luong datafile, logfile , vi tri file ...

- ALTER DATABASE BACKUP CONTROLFILE TO TRACE; -- user_dump_dest

- Multiplexing controlfile: dat cac controlfile nam tren cac dia cung khac nhau, dat tren 2 Controller khac nhau

- v$controlfile

- show parameter control_

- v$parameter: SELECT value FROM v$parameter WHERE name='control_files';

- v$spparameter

- Neu co 3 control file khai bao trong control_files, khi STARTUP neu thieu 1 CF thi se khong STARTUP database

- CF duoc doc o mode Mounted

Lession 7: Redo log file

- Redolog file: chua moi thay doi cua Oracle ===> archived-log file ( 10 vi tri)

- Log Group: it nhat 2, moi log group phai co it nhat 1 log member ( file)

- Oracle tu van, 1 log group nen 3 logfile member

- Multiplexing redo log file

- ALTER SYSTEM add logfile MEMBER | GROUP

- v$log

- v$logfile

- ARCHIVED-LOG: lay dinh file archived-log default, $ORACLE_HOME/dbs/

1. SHUTDOWN IMMEDIATE

2. STARTUP MOUNT;

3. ALTER DATABASE ARCHIVELOG;

4. ALTER DATABASE OPEN;

5. ARCHIVE LOG LIST;

Lession 8: Tablespace & Datafile

1. Tong quan tablespace & datafile

- tablespace gom nhieu datafile

- datafile gom nhieu block

- SYSTEM tablespace: luu thong tin ve ban than Oracle

- NON-SYSTEM: DATA, TEMP, LOG

- Noi dung tablespace thi co 3 loai:

PERMANENT: du lieu

UNDO: luu anh truoc khi thay doi (500$)

salary:500$

update emp set salary=1000$ where name='DONG';

rollback;

TEMP:

- SELECT first_name, salary FROM employees ORDER BY first_name;

- SELECT DISTINCT isdn FROM mc_subscribers; -- 20.000.000

==> cat du lieu thanh manh

==> luu nhung manh chua sort xuong vung tam: tablespace TEMP

==> sau khi sort ==> tap hop ket qua sort & gui cho client

2. Phan loai phuong thuc quan ly Extent

- Database > Tablespace > Segment > Extents > Oracle Block > OS Block

- khi INSERT du lieu vao table ==> FULL ==> ALLOCATE EXTENT

1. Dictionary Managed Tablespace:

- hoi Data Ditionary View

- ALLOCATE extent: INSERT based-tables

- DE-ALLOCATE extent: DELETE | UPDATE vao based-tables

2. Locally Managed Tablespace

- Bitmap: 0 | 1

- CREATE TABLESPACE example datafile '/u01/oradata/ora9i/example01.dbf' size 100M;

3. UNDO TABLESPACE

- CREATE UNDO TABLESPACE

4. TEMPORARY TABLESPACE

- Create TEMPORARY TABLESPACE temp1 tempfile /u01

- Create TEMPORARY TABLESPACE temp2 tempfile /u02

create user u1 identified by u1 TEMPORARY TABLESPACE temp1;

create user u2 identified by u2;

- Co 2 cach chi dinh DEFAULT temporary tablespace;

- CREATE DATABASE

- ALTER DATABASE

5. CREATE | DROP | OFFLINE | READ-ONLY | RESIZE TABLESPACE

create tablespace data01 datafile '/u01/oradata/ora9i/data0101.dbf' size 5M

extent managent local;

Oracle9.2.0, SYSTEM locally ==> locally

6. ADD | RESIZE DATAFILE

- AUTOEXTEND ON

- cho insert khi file bi day

- maxextents=UNLIMITED

- quyen INSERT ==> INSERT den khi dia cung FULL thi thoi ==> D.O.S

- AUTOEXTEND ==> TEMPORARY TABLESPACE

- Sort tren Mem khong du thi se luu tam thoi du lieu sort tren TEMPFILE

- TEMPFILE dang la AUTOEXTEND

vi du:

- 2007, VMS-TT2, MOBICARD, LINUX

- MC_ACTION_AUDIT: rat lon, luu moi tac dong thay doi, xem tren du lieu thong ung dung FPT

- partition theo thang:

- 2005: SELECT ... MC_ACTION_AUDIT GROUP BY, ORDER BY ...

- TEMP: AUTOEXTEND

===> day toan bo mountpoint /u02 ==> tempfile02.dbf 25 GB

===> ket noi bi bao loi ==> khong ket noi thuc hien giao dich ==> 8h PM

===> DBA chay vao ==> telnet ==>

sqlplus "/ as sysdba" ==> trace file ==> Khong dang nhap duoc

SHUTDOWN ABORT

DROP TABLESPACE TEMP;

CREATE TEMP newtemp;

OPEN

rm /u02/tempfile02.dbf

su - root

updatedb

df -h

reboot

OS loi ==> 10h ==> lo mo cau hinh RAID ==> nang hon

==> bo tay ==> LAC VIET ==> recover block ==> 2 ngay

==> restore backup from tape ==> may chu ==> chuyen mobicard ==>

==> hop phong tinhoc voi lanh dao IT ==> tim nguyen ==> dua giai phap

==> thanh lap to SYSTEM & DATABASE

7. Oracle managed File (OMF)

- create tablespace data2009 datafile '/u01/oradata/ora9i/data01.dbf' size 100M;

- alter tablespace data2009 add datafile '/u01/oradata/ora9i/data02.dbf' size 100M;

- db_create_file_dest = '/u01/oradata/ora9i/';

create tablespace data2009 datafile size 100;

alter tablespace data20009 add datafile;

8. dba_tablespaces | v$tablespace | dba_free_space

dba_data_files | v$datafile

dba_temp_files | v$tempfile

Lesssion 9: Storage Structur & relationship

1. Types of Segment:

- Table

_ khong co Index thi thuong dung Full-Table-Scan

_ xem du lieu voi cot co Index: Scan b*tree Index ==> rowid ==> row data

- Index: b*tree index

- Partitioned Table : cac rows duoc phan nhom theo mot dac tinh nao do: thoi gian

- Partitioned Index: no se "dua" vao Table: drop table ==> drop index, truncate table ==> truncate index

- Cluster Table: dung de nhom hai hay nhieu bang co cung cot du lieu voi nhau ==> tang toc do cau truy JOIN giua cac bang do

- Index Organized Table (IOT): du lieu cua bang duoc luu tren b*tree index

- LOBs Segment: Large Object ==> CLOB (character: text file) & BLOB (Binary: movie, music)

- TEMPORARY: chi dung de luu tam thoi du lieu cho tac vu Sort khong du tren bo nho

- Bootstrap (Cache) segment: dung de luu Data Dictionary Cache (shared-pool)

2. Block size: kich thuoc chuan & phi chuan

- db_block_size: 8 K

- CREATE TABLESPACE data datafile '/u01/data01.dbf' blocksize 16K size 100M;

- db_multiple_block_read_count = 16 | 32

3. Co che quan ly segment

- AUTOMATIC SEGMENT MANAGEMENT

- create tablespace data2009 datafile '/u01/oradata/ora9i/data01.dbf' size 100M

extent management local

automatic segment management;

- MANUAL SEGMENT MANAGEMENT

- inittrans : so trans duoc phep truy cap vao block dong thoi

- maxtrans: so max trans duoc phep truy cap vao block dong thoi

- pctfree: qui dinh nguong cho thao tac INSERT & UPDATE

= 10% ==> de gianh 10% cho tac vu UPDATE

= 0% ==> UPDATE xay ra ==> migration row ==> row tren 2 block

- pctused: chi ra nguong cho tac vu DELETE ==> khi du lieu bi delete xuong duoi muc qui dinh boi pctused thi block se thoa dieu kien INSERT

==> nghia block duoc chuyen freelist

4. dba_segments: segment dang nam trong file

dba_extents:

Lession 10: UNDO tablespace

1. noi dung: anh truoc khi thay doi cua mot du lieu nao do

2. chuc nang:

a. rollback

b. shutdown abort ==> inconsistent ==> (instance) recovery ==> redolog file & undo tablespace

b. read consitency: chi du lieu commit moi duoc thay boi cac session #

3. co che quan ly UNDO

a. AUTO

b. MANUAL

4. Cau hinh

- UNDO_MANAGEMENT = AUTO | MANUAL

- UNDO_TABLESPACE =

5. tham so

- undo_retention: qui dinh so giay du lieu trong UNDO Tablespace duoc giu lai

6. dba_rollback_segs: xem thong rollback segment

v$rollname

v$undostat

Lession 11: Tables

1. segment types: table, partition table, cluster, iot

2. cau truc rows: header, data , column legth ==> migration rows

3. Table

a. CREATE TABLE a (x number)

b. Built-in: NUMBER, VARCHAR2, DATE

User-defined: TYPE nhan vien IS manv, tennv, phongban;

ROWID: vi tri vat ly 1 rows, 9i ==> truy cap theo ROWID la nhanh nhat

UROWID: phien ban 8i (restricted rowid)

c. ALTER TABLE : thay doi thong so luu tru cua Table (Storage Clause: initial extent, next

d. ALTER TABLE ... ADD COLUMN

e. ALTER TABLE DROP COLUMN ==> delete column-length ==> chiem thoi gian & IO

f. ALTER TABLE SET UNUSED ==> DROP COLUMN vao thoi diem it tai

g. TRUNCATE != DELETE

h. CREATE TABLE new_emp AS SELECT * FROM employees; (CTAS)

giong:

- cau truc cot & kieu du lieu cua cot

- no co the lay duoc du lieu cua bang goc

khong giong

- khong co Index, khong constraint, trigger ...

==> CREATE TABLE new_emp AS SELECT * FROM employees WHERE 1=2;

- DISCTINCT, MAX, MIN, COUNT, SUM, GROUP BY, ORDER BY

==> FPT

- hang dem xu ly chiem tai ==> TEMP ===>

==> MVIEW

CREATE MATERIALIZE VIEW v_dept_emp AS

select dept_name, count(first_name)

from big_department a, big_employees b

where a.dept_id = b.dept_it

ORDER BY dept_name;

Lession 12: Managing Index

1. Y nghia Index: muc luc mot cuon sach hang tram trang

2. Loai Index

1. B*Tree Index

- So luong rows khong qua nhieu: 20.000.000 rows

- gia tri cua cot tao Index khong xac dinh duoc, nhieu gia phan biet

vi du: isdn

imsi

cust_id

2. Bitmap Index

- so luong rows nhieu: 200.000.000 rows

- gia tri cua cot tao Index khong xac dinh duoc

vi du:

status = 0, 1, 2, 3, 4, 5 ... 10

color = 1, 2, ,3 , ..., 16

sex = male, female, gay

- cau lenh SQL ma dung nhieu dieu kien AND hoac OR

3. Function-based Index

- SELECT * FROM employees WHERE id = 100;

==> SELECT * FROM employees WHERE to_number(id) = 100;

3. Cau lenh lien quan Index

a. CREATE INDEX emp_id_idx ON emloyees(emp_id) PARALLEL 7 NOLOGGING;

b. DROP INDEX ...

c. ALTER INDEX ...: thay doi thuoc tinh luu tru cua Index

d. REBUILD INDEX: reoranized index ==> tang toc do duyet cay B*tree Index

e. ANALYZE INDEX ten_index VALIDATE STRUCTURE; ==> xac dinh Index co bi loi

Lession 13:

create table test (x varchar2(5), y number check > 0);

insert into test values('a',0);

insert into test ('a',1);

Lesssion 14: Managing User Profile

A. Quan tri mat khau

- dang nhap sai 3 lan: LOCK acccount ?

- qua 10 ngay khong doi mat khau: EXPIRED ?

CREATE PROFILE admin_users LIMIT

PASSWORD_FAILED_LOGINS 3

PASSWORD_LIFE_TIME 10;

- Neu user chua tao san:

create user dong identified by dong

default tablespace users

temporary tablespace temp

profile admin_users;

- Neu user da co san

alter user dong profile admin_users;

B. gioi han tai nguyen nguoi dung

- moi nguoi dung chi duoc ket noi 3 connection 1 luc

- nguoi dung nao ket noi ma khong lam gi trong vong 15' se LOGOUT

- Nguoi dung chi duoc phep ket noi trong vong 60'

CREATE PROFILE admin_users LIMIT

SESSIONS_PER_USER 3

IDLE_TIME 15

CPU_PER_USER 10

CONNECT_TIME 60

PASSWORD_FAILED_LOGINS 3

PASSWORD_LIFE_TIME 10;

- LIMIT_RESOURCE = TRUE

Lession 15: Managing User

1. CREATE USER

- quan tri theo user thuc: nghia la user do duoc tao boi Oracle

CREATE USER DONG IDENTIFIED BY oracle:

CREATE USER TRUNG IDENDIFIED BY oracle;

CREATE USER KHANG IDENDIFIED BY oracle;

CREATE USER HUNG IDENDIFIED BY oracle;

- quan tri theo user ung dung: nghia la user do duoc khai bao trong 1 bang ==> BIGAPPUSER

==> PAYMENT ==> PAYMENT_OWNER

==> Connection Pool: PAYMENT_OWNER, 50

admin_user

username

password

c. CREATE USER aaa IDENTIDED BY bbb

- khong chi dinh default TEMPORARY TABLESPACE

==> CREATE USER aaa IDENTIFIED BY bbb

TEMPORARY TABLESPACE temp;

- khong chi dinh default [PERMANENT] TABLESPACE

CREATE USER aaa IDENTIFIED BY bbb

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE data;

- cap QUOTA tren tablespace cho USER: alter user a quota 5m on example;

- UNLIMITED TABLESPACE: unlimited quota

2. ALTER USER a IDENTIFIED BY b; ==> khong an toan

SQL> password a ==> an toan hon

password ghi vao redolog o dang ma hoa ===> pha ma

3. ALTER USER a ACCOUNT LOCK | UNLOCK;

Les 4: Creating Database

Type of Database

- Data warehouse (DSS = Decission Support System): DOI SOAT,

- du lieu rat lon ==> 1 ti rows # 1 trieu rows

alter table small_tables add (status varchar2(1) default '1');

alter table big_tables add (status varchar2(1) default '1');

subscribers ( 5 M rows)

mc_subscribers (12M rows)

mc_subscribers.status = 0, 1 , 2 , 3 , 4, 5

update mc_subcribers set status='3';

==> redo-entry

- nhieu tac vu tong hop so lieu lon

- thuong co bao cao lon

============================

- 30/04 - 01/05/2009 khach hang se co khuynh huong mua san pham nao ?

- 30/04 khach nam 20 - 30 tuoi se mua qua - banh ?

===> thay boi ==> co thong tin co so ve ==> cang nhieu thong tin ==> cang tin

- Can co lich su du lieu ==> 10 nam mua hang cua khach hang ==> suy ra thoi quen mua hang

==> 3 nam nam gioi thich chocolate mau den

- Online Transaction Processing (OLTP) - IN (nap tien, kich hoat ...)

- nhieu tac vu - giao dich nho, nhanh

- thay doi du lieu nhieu

- it co nhung bao cao lon, tong hop

- General (Hybrid) - CSKH, MOBIFONE, MOBICARD

- DSS + OLTP

Less 5: Data Dictionary

- duoc luu trong tai khoan SYS

1. Based - table

sys.user$

2. Data Dictionary View

dba_users

dba_data_files

2.1 Dynamic Performance View: load len bo nho, luu tren do

v$session: select count(*) from v$session; ==> 15

2 session connected ==> 17

v$instance

v$database

v$datafile

3. DBA_TABLES | INDEXES | VIEWS | USERS | OBJECTS

ALL_TABLES | INDEXES | VIEWS | USERS | OBJECTS

USER_TABLES | INDEXES | VIEWS | USERS | OBJECTS

spfile$ORACLE_SID.ora

spfile.ora

init$ORACLE_SID.ora

init.ora

CN - 12 PM: backup database offline (no archived log)

Chieu thu 2: 3 PM, bi hu mat cai data01.dbf

===> phuc hoi lai du lieu cua ban backup luc 12 PM, CN

===> mat het toan bo du lieu tu luc 12 PM ==> thoi chieu thu 2

hu 1 block, datafile ==> phuc hoi toan bo bo database

TEST ==> DEVELOPMENT, PRODUCTION

=================

- tang I/O

- chiem space disk

- doi soat VMS cong ty (TT2): 1 ngay sinh 100 GB archived-log

BILLING:

toan bo thay doi doi duoc luu ra archived-log file

muon truy vet xem ai da thay doi du lieu trong bang customers vao thu 6 tuan vua

roi ???

LogMiner ==> xem noi dung archived-log file

select * from employees where id = 100;

select d.dept_name, e.first_name, e.salary

from employees e, department d

where e.dept_id = d.dept_id;

- cau lenh phai JOIN giua 2 hay nhieu bang

- cau lenh thuc thi nhieu lan

==> ton chi phi

================

cluster table

create temporary table t1 (x number);

delete from emloyees;

commit;

==> LogMiner

==> Flashback

No comments: