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
