Jerry Held

1
??????? ??????? ??????????? ?? ???? ???? Oracle
(Oracle9i) ???????? ?????? ??????????
????????????????? Oracle
2
Oracle ??????? ???????????
??? ?????????????? ???????????????? ???????

• ??????????
• ??????????? ? ??????????????
• ??????? ?????????????? ??? ??????? ????????
• ?????????????????
• ??????????? ????????????? ??????? ??? ??????????
  ???????? ?????????????

3
Oracle9i - ??????? ???????????
?????????????
Real Application Clusters Fast Restart
?????? ?????? ? ??????????
Recovery Manager Data Guard
?????????-???????? ????
??????????????????
Flashback Query Log Miner, Data Guard
????????????????????
Dynamic Reconfiguration Data Guard, RAC
????????????????
???????????????
Online Redefinition Partitioning, Parallel SQL
4
Oracle ??????? ???????????

• ?????? ?? ????? ?????? ? ????????? ?????
• ?????? ?? ???????????? ?????? ? ?????????
• ??????????? ??????? ??????????? ??? ????????? ??
  ????????????? ????

5
Oracle ??????? ???????????

• ?????? ?? ????? ?????? ? ????????? ?????
• Standart Fault Recovery
• Fast-start Checkpointing
• Fast-start parallel rollback
• Cold Failover
• Warm Failover - Real Application Cluster
• Hot Failover
• ?????????? ????? - Transparent Application
  Failover

6
???????? ?????????????? ??

• ??? ?????????????? ????? ???? ?? ??????
• ?????????, ??? ??? ?????????, ????????? ?? ????,
  ????????? (roll forward)
• ????? ?????? ?????????????? ???????? ?????????
  ?????? ? ??????????? ????? ???????????? ???????
  (checkpoints)
• ???????? ??? ????????????? ?????????? (roll back)
  
• ????? ?????? ?????????????? ???????? ?????
  ??????? ??????????, ??????? ?????? ????? ?? ?????
  ???? ????????????????? ???????????????

?? ?? Recovery
Step 1 Roll Forward
Step 2 Roll Back
Uncommitted Transaction
Committed Transaction
Recovery Time Roll Forward Roll Back
7
??????? Checkpointing

• ??????? Recovery ????????? ??? ?????????,
  ????????? ?? ??????? ?????????? Checkpoint
• ??????? ???????? ???????????? ?????????????? ???
  ???????????????? ????? ? ?????? ? ????? ?? ??
  ????
• ??? ?????? ??? ?????? ?????????, ????? checkpoint
  ?????????? ?? ??????? ?????? ???????? ??????
• ?????? ???????????? ???????????? ????????
  ??????????? ? ??????? ???????????? ?????? ???????
  ????? ???????? ?????? ? ??????? ??????

8
Oracle9i ????? ???????? ??????????????
Recovery Time Roll Forward Roll Back

• ??????????? ???????? ?????????????? ????? ????
• ???????????? ????? ?????? ? ?????? ???
  ?????????????
• ????????? ????????? ??????? ??????? ??????

9
Fast-Start Checkpoints

• ??? ????? ????? ?????????????? ????? ??????,
  ??????????? ????? ??????, ??????? ?????
  ??????????????? ? ?????? ????
• Oracle ????????????? ????????? ??????????? ?????
  checkpoints
• ????? ?????? ???????????????? ???? ???????????
  ?????? ??????, ??? ????? ????? ????????? ?????
  roll forward

10
???????? ??????????
Server 1
Server 3
Server 2
Server N

11
?????????? ???? ??????

• Shared Cache/Disk
• Oracle ? IBM Mainframes
• ??? ?????? ?????, ??? ????????
• ?? ??????? ??????????????? ??????

• Shared Nothing
• Microsoft ? IBM Unix/NT
• ??? ?????? ?????, ??? ??????????
• Static Data Partitioning

12
Microsoft Clustered Database - ??????????????
??
CLUSTER
NETWORK
database partition
database partition
database partition
database partition
database
13
???????????????? - ??? ??????????? ?????????
?????? ? ????
?????????????? ?? ...

• ???????? ????...
• ????????? ??????
• ?????????????? ???????, ???????, ?????????????,
  ????????
• ??????????? ??????

14
???????????????? - ??? ??????????? ?????????
?????? ? ????

• ?????????? ?? ...
• ???????? ????...

15
??????? ?????? ???????????? ??????? ???????????

• ? ?????????????? ?? ??? ??????? ???? ...
• ????????? ?????? ????? ???? ??????????
  ????????????
• ????????????? ?????????? ????? ??????????? ??????
  ?????? ????? (2-phase commit)
• ?????? ???????????????

database partition
16
??????? ?????? ???????????? ??????? ???????????

• ? ?????????? ?? ??? ??????? ???? ...
• ??? ?????? ???????? ??????????
• ?????? ?????? ????? ?? ??????????? (?????????????
  ?????????? ???????????? ??????? ??????)
• ?????????????? ??????????????

17
?????????????? ? Cold Failover

• ??????? Oracle ?? ???????? ???? ????????
• ?????? ???? ??????? ????? ???? ?????? ? ?????
  ?????? ???????
• ??????? ???????????? ????????? ???????
• ????? ???????? ?????????? ???? ???????? ??
  ??????? ??????????????

18
?????????????? ? Warm Failover

• ????????????????? ?? ???????? ???? ???????? ?
  Oracle Parallel Server
• ??? ?????????????? ??????????? ??????????? ??????

19
?????????????? ? Hot Failover

• ??????????????? ?????????? ? ???????? ?????
  ????????
• ? ?????? ???? ???????????? ??? ??????????
• ? ?????? ????? ????????????? ??? ???????????
  ???????? ?????

backup
primary
Shared Database
20
Failover - Oracle Fail Safe for MS NT
Oracle RDBMS Oracle Fail Safe
????????? ????
???????? ????
MS Clust. Mgr.
Windows NT
21
Parallel Cache Management
????? ???????

• ?? ??????????
• ????? ????

• ??????? ????????
• Application Partitioning
• Tx Monitors

. . .
. . .
. . .
Block A
Shared Disk Database
22
Cache Fusion

• Oracle9i Cache Fusion ???????? ??????????????????
  ? ????????????????
• ?????? ???????????? ???????? ????? ????????????
• ??????????? ????? ???????? ?????/??????

23
Oracle9i Real Application Cluster
--???????????????? ? ??????? ???????????

• ??????????? Shared Cache ?????????? Cache Fusion

• ??????????????? ???? ?????????? ??? ?????????

• ???????? ?????????????????, ????????? ????
  ????????

24
Transparent Application Failover (TAF)
?????? ???? - ??? ????, ??????? ?????? ?????????
!

• Transparent Application Failover ???????? ?? ????
  ???????
• ?????????? ? ???????????? ????????????? ?
  ????????? ?????????????????? ? ??????? ????
• ?????????? ? ??????? ?? ???????????
• ?????????????? ???????? Login?

??? ???? ???? ? ???????????? ????????? ?? ?
25
TAF Select Failover

• ?????????? ??????? ??????? ?????????? ???????????
• ???????? ?????-??????????? ?????? ???????????
  ??????????? ???????????

Client
SELECT FROM emp
SELECT FROM emp
Instance 2
Instance 1
Rows Partially Returned When Failover Occurred
Continues Returning Remaining Rows
26
?????????????? TAF

• TAF ???????? ??? ?????????
• Client/server ??????????
• ????????? ??????
• ???????? ??????? (SELECT statements) ???????
  ?????? ?????????? ??????????
• ?? ?????????
• ???????? ?????????? UPDATE
• ?????????? PL/SQL-??????? ?? ??????? ???????

• ??? ?????????? ??????????, ??????? ??????????
  Oracle Call Interface version 8 (OCI8) ??? ????,
  ???? ??? ???, ???????? ? TAF
• SQLPlus, ODBC, JDBC/OCI, Oracle precompilers

27
Dyna?onnect - ????????? ???????? ?????
?????????????
28
Real Application Cluster?????????????
DynaConnect
CPU Load

• ?? ??? ?????? ?????????????? ?? ????? listener??
• ???? ???????? ?? ????????????? ??? listener??
• Listener ???????? ???????? ???????????? ????

29
Oracle Parallel Query
Query
Server 1
Server 3
Server 2
Server N
Application

ClusterWare
ClusterWare
ClusterWare
ClusterWare
OS
OS
OS
OS
30
??????? Oracle ??? ?????????
????????????????
Oracle9i RAC OPQ
Oracle9i RAC
???????????
Oracle9i
Oracle9i Oracle Fail Safe
31
??????? Oracle ??? ?????????
Real Application Cluster
Hardware Failover

• ?????
• ??????? ??????????? ? ????????????????
• ??? ????? ????
• ????? ????????, ????????????? ?????, ????????
• ???????????? ????? ???????? ? ????????
  ??????????????
• ????? ???? ?????? ????????? ? ???? ???????????
  ????
• ???????????? ????????
• ???? ???????????????? ? ?????? ?????
• ?????????? ?????????? ??? ????????? ????????

• ?????
• ??????? ???????????
• ??????? ????????????????? ? ??????????
• ????????
• ????????? ?????????????? ???????? ?? ???????
• ??????????? ???????? ?????? heartbeat

32
??????? Oracle ??? ?????????
Real Application Cluster
Hardware Failover

• ??????
• ????? ??????? ????????? ? ?????????????????
• ????
• ???????????? ??????? ??????????? ?????? DLM

• ??????
• ???????????? ???? ????? ??????????????
• ???????? ???? ????? ???? ???????????? ? ???????
  ??????
• ??? ?????????? ????? ????????
• ????????? ?????????? ??? ?????? ????????

33
Oracle ??????? ???????????

• ?????? ?? ????? ?????? ? ????????? ?????
• ?????? ?? ???????????? ?????? ? ?????????
• ??????????? ??????? ??????????? ??? ????????? ??
  ????????????? ????

34
Oracle ??????? ???????????

• ?????? ?? ?????????
• Oracle Data Guard
• Advanced Replication
• Geo-mirroring

35
Oracle Data Guard
????????? ?????????? ????? ???? ??????
36
Failover ? Switchover
Primary Server
Standby Server
Log Write
Production Database
Log Apply

37
Logical Standby
38
?????????????? ????????? ???????? ?????????

• ????????? ?????????????? ????-?-???? ???????????
  ???????? ????????
• ????????????
• ?????????????? ????? ?????????
• ????????? ?????????????
• ???????? read-only ????? ??
• EMC Symmetrix SRDF(Symmetric Remote Data
  Facility) TimeFinder ??????????????? Oracle

39
Advanced Replication

• ?????????? - ??????? ??????????? ? ???????????
  ???????????? ???????? ?? ? ?????????? ????? ??????

40
????????? ???????
41
Oracle ??????? ???????????

• ?????? ?? ????? ?????? ? ????????? ?????
• ?????? ?? ???????????? ?????? ? ?????????
• ??????????? ??????? ??????????? ??? ????????? ??
  ????????????? ????

42
Database Resource Manager

• ???????, ??????????????? ?????????????,
  ?????????? ?? ?????, ???????????? ???????????????
• ????????? ?????????? ??????????

OLTP User
Batch Processing
43
Database Resource Manager????????? ????????

• ??????????? ???????
• ????????????? ???
• ??? ??????? ????????????
• ??????? ???????????? ?????????? ?????????????,
  ??????????? ?? ?????????? ???????? ? ???????
  ???????? ???????
• ???????? ?????? ????? ??? ?????? ???? ?????? ???
  ??????? ?????

44
LogMiner

• ????? redo log ???????? ?????????? ?? ??????????
  ??? ????? ??????
• Oracle9i LogMiner ????????????? ???????????
  ?????????, ??????????? ????????????? ??????????
  ???? ??????
• LogMiner ????? ????????????
• ??????????? ????????????? ?????????, ??????????
  ?? ???????????, ????????????, ???????, ???????,
  etc.
• ??????????, ??? ????????? ?????
• ?????????????? ?????????? ?? ?????? ??????????,
  ? ?? ?? ?????? ??
• ????????? ??????

45
?????? LogMiner
Joe Smith ?????? ???? ????????.
SQLgt select sql_redo, sql_undo from
vlogmnr_contents where 2 username joe
smith and seg_name EMPLOYEE
14 rows selected SQLgt exit
46
??????????????? ?????? ? ????????????????
???????????, ?????????????

• ??????????????? ???????? ?????????? VLDB
• ??????????????? ??????????? ????????
  ??????????????????
• ??????? ????????? ??? ????????????? ? ??????????
• ?????? ??????????? ? ??????????? ?? ??????????

????????????????
01-APR to 30-JUN
01-JAN to 31-MAR
01-OCT to 31-DEC
01-JUL to 30-SEP
47
??????????????? ?????? ? ???????????????????????
?? hash-????????

• ????????? ?????? ? ??????, ??????????? ??
  ??????????? ????????
• ??????? ??? ???????????? ????????????? ?????? ?
  ????????????? DML

?????? ??????????? ????????? hash-????????
48
??????????????? ?????? ? ???????????????
???????????????

• ?????? ?? ????? ????? ???????????????
• ???????????? ??????
• ????????????? ? ???????????

ORDER table

??????? ?? ?????, ????? ??hash-????????
Q1
Q2
Q3
Q4
49
List Partitioning
?????? ???????????? - ?????????? ???? ??????
??????????? ???????? ??????-???????????
??????? online
Europe Region
Americas Region
Asia Region
1200 GMT
?????????????
50
??????????????? ????????

• ?????? GLOBAL ????????? ?? ?????? ? ????? ??????
• ?????? ????? ???? ????????????? ??? ???
• ????????? ?????? ?????? ?? ???? ??????
• ?????? ?????? ??? OLTP
• LOCAL - ????????????? ??? ? ???????
• ???? ????????? ????? ?????????? ?? ????? ???
  ???????
• ???? ????????? ????? ???????????? ????????
• ?????????? ??? DSS
• ?????????? ??? OPS

Global Non-Partitioned Index
Global Partitioned Index
Table Partition
Table Partition
Table Partition
Table Partition
Local Partitioned Index Prefixed or Not
51
???????????? ?????????? ????????
user-defined function
select sum(revenue), store from line_items where
profit (price,units) gt 0.2 group by store order
by store cost
query servers
SQL
data
Coordinator
ship SQL functions, assemble results
dynamic data distribution
sorting grouping
scanning
scan line_items, executing profit function in
parallel
sort by store, sum revenues
52
???????????? INSERT, UPDATE ? DELETE??????
????????????? ?????????????? ??????????

• ? ?????? ??, ?????????? ???????? ?????? ????
  ??????????? ??? ????????? ????????? ???????
  ???????
• ???????? ? ?????????? ??????, ???????????
  ???????????, ??????????? ????????
  ?????????????????? ??? ??????????
• ??????????
• ?????????? UPDATE
• ?????????? DELETE
• ?????????? INSERT

53
????????????? ???????? ?????? ?? ????

• ????????? ?????? ????? ???????? ?? ???? ???
  ????????????? ????????? ?????????????
• ???????? ?? ??????? ??????????

54
?????????????? ??????

• ?????? ?? ???????????? ?????? ??????? (flashback)
• ????????? ????????? ?????? ?? ????? ?????????
  ????? ? ???????
• ????? SQL ?????? ????????

55
Oracle Enterprise Manager

• ????????????, ?????????? ??????????? ? ?????????
• ?????????? ???????
• Web ?????????
• HTML ??????
• ????????????
• ??? ?????

56
Oracle9i - ??????? ???????????
57
??? ??? ?????...

• ??????? ? ?????? 258-41-80
• www.oracle.com/ru