oracle und db2 luw - doag deutsche oracle … und db2 luw udo brede solution ... – recovery...
TRANSCRIPT
![Page 1: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/1.jpg)
© 2010 Quest Software, Inc. ALL RIGHTS RESERVED
Oracle und DB2 LUW
Udo Brede
Solution Architect DB2
![Page 2: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/2.jpg)
2
Agenda
• Grundlagen und Terminologie
• Architektonische Unterscheidungen
• Storage und Space Management
• Unterscheidungen in der Administration
• Oracle Compatibility Features in DB2 LUW Version 9.7
![Page 3: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/3.jpg)
3
DB2 = Database 2
• 07.06.1983
• DB2 z/OS (MVS, OS/390)
• DB2 VSE/VM
• DB2 auf z/Linux
• DB2/2
• DB2/400 (AS400, I-Series)
• DB2 LUW (Linux, Unix, Windows)
![Page 4: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/4.jpg)
4
Komponenten der DBMS
Oracle
• Instance
• File
• Database
• Tablespace
• Schema
• Table
• Index
• View
• Trigger
• Rollback Segment
• Stored Procedures
• SQL Plus
• Packages
DB2 LUW
• Instance
• Container
• Database
• Tablespace
• Schema
• Table
• Index
• View
• Trigger
• NA
• Stored Procedures
• DB2 CLP
• Modules
![Page 5: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/5.jpg)
5
Produkt Optionen
• Express Edition– 1 CPU/1GB RAM
– 4 GB max DB size
– Nur für Windows/Linux
• Standard Edition– 4 Sockets
– Unlimited scalability
• Enterprise Edition– Unlimited scalability
– Table Partitioning
– RAC
• Express-C– 2 CPU/2GB RAM
– Windows/Linux/Solaris/Mac
• Express Edition– 4 CPU/ 4GB RAM max
• Workgroup Edition– 4 CPUs/ 16 GB RAM
• Enterprise Server Edition– Unlimited scalability
– Data partitioning (DPF)
– Purescale
Oracle DB2
![Page 6: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/6.jpg)
6
System/Data Dictionary - Catalog
• Base tables– SYS.xxxxx$
• System views– SYS.GV_$ or GV$
– SYS.V_$ or V$
– ALL_
– DBA_
– USER_
• Base Tables
– SYSIBM.xxxx
• System Views
– SYSCAT• Read-only views defined for catalog
base tables
– SYSSTAT• Updateable set of views
• Primarily used for access path
manipulation
– Administrative Views
Oracle DB2
![Page 7: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/7.jpg)
7
Zugriff/Datenbankmangement
• OEM - GUI– DBA Mgmt Pack(free)
– Change Mgmt Pack($)
– Diagnostics Pack($)
– Tuning Pack($)
– Recovery Manager($)
• Sqlplus
• Control Center GUI– Command center
– Command line processor
– Command window
– Script center
– Visual Explain
• Data Studio/Optim– Nachfolger vom Control Center
• CLP
Oracle DB2
![Page 8: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/8.jpg)
8
Gleicher Name – verschiedene Erklärung/Nutzung
Oracle
• Stored Procedure
– PL/SQL
• Package
– grouping of PL/SQL blocks
– Nothing to do with optimization
• Segment
– Set of Oracle Extents
DB2
• Stored Procedure
– External - C,Java,Cobol
– Internal, SQL/PL
• Package
– Pre-compiled static SQL
• Segment
– Block of pages in TS
![Page 9: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/9.jpg)
9
Verschiedene Namen – gleiche Nutzung
Oracle
• Package
• Data Block
• Dictionary
• Alert Log
• Redo Log
• Archive log
• Segments
• Statement Cache
• System Global Area
(SGA)
DB2
• Module (9.7)
• Data Page
• Catalog
• Diag log
• Log Files
• Archival Logging
• Space Consuming Objects
• Package Cache
• Instance shared memory/DB
shared memory
![Page 10: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/10.jpg)
10
Instance und Datenbank
Oracle
• Instance– A collection of Processes
– One data dictionary
– one active database
– Init.ora parameter file
DB2
• Instance– Logical database server
environment
– Also referred to as a Node
– 1 to many databases
– Database Manager
Configuration File
![Page 11: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/11.jpg)
11
Instance
DB2
D
B
M
C
O
N
F
I
G
D
B
M
C
O
N
F
I
G
Instance_1
PRODDB1
CatalogDBCONFIG
Log
Catalog
Log
DBCONFIGPRODDB2
BP’s
BP’s
Instance_2
TESTDB1
CatalogDBCONFIG
Log
Catalog
Log
DBCONFIGTESTDB2
BP’s
BP’s
SIDOracle Database
Control
files
Data
files
Redo
log
files
Data
Dictionary
![Page 12: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/12.jpg)
12
Bufferpool
Oracle
• Typically use single BP
• Defined in init.ora
• Dynamic create, alter, and delete
• Objects assigned via DDL
DB2
• IBMDEFAULTBP automatically
created with database
– Additional pools created with DDL
• Hidden Bufferpools
– 4k,8k,16k, & 32K
• Share only within individual
databases
![Page 13: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/13.jpg)
13
Datenbanken
Oracle
• Physical Structure
– Data files
– Redo Log files
– Control files
• Only one DB mounted/opened
• SYSTEM Tablespace
– Data dictionary
DB2
• Logical grouping of DB2 objects
• 1 to many databases/instance
• System tablespaces– SYSCATSPACE
– TEMPSPACE
– USERSPACE
• Bufferpools defined in database
• Database configuration file
![Page 14: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/14.jpg)
14
Storage und Space Management
• Data Files– Cooked
– Raw
– Auto extend
• Container
– Device
– Directory
– File
DB2Oracle
![Page 15: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/15.jpg)
15
Tablespaces
Oracle
– System • Automatically created
– SYSAUX• Auxillary system TS
– UNDO
– Non-System• Permanent
• Temporary
• BIGFILE/SMALLFILE
DB2• Regular
– USERSPACE1
• Temporary
• Large
![Page 16: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/16.jpg)
16
Tablespaces - 2
Oracle
• Dictionary Managed
– No space calculations
• Locally Managed
• Automatic Storage
Management (ASM)
– 10g
DB2
• System Managed (SMS)
– O/S’s file manager allocates
space as needed
– Good for small tables
• Database Managed (DMS)
– Space is pre-allocated
– Better suited for large tables
– Faster I/O
• Automatic
– Combines features of both
SMS and DMS
![Page 17: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/17.jpg)
17
DB2 Tablespaces
• System Managed– No finite storage specified
• O/S’s file manager
allocates space as needed
• Good for small tables
• Database Managed– Space is pre-allocated
– Better suited for large tables
– Faster I/O
Add Containers to TS X
Separate Indexes from Data X
Space allocated as Needed X
High performance in heavy OLTP X
High performance in decision support X X
Ease of administration for small tables X
Flexibility of Administration X
File or Device containers X
SMS DMS
![Page 18: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/18.jpg)
18
Tablespace Komponenten
Segments
Extents
Data Blocks
Data Files
Oracle DB2
Storage Object
Extents
Page
Containers
![Page 19: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/19.jpg)
19
Datenspeicherung
Oracle• Datafile
– Datafiles associated to an individual tablespace
– ‘/DISK6/app04.dbf’
DB2• Containers
– A container is assigned to an individual tablespace
• Directory name
– SMS Only
– D:\MYTS
• Raw Device
– DMS Only
– E:
• File name
– DMS Only
– D:\SODADB\SODA.UTILITY.DMS
![Page 20: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/20.jpg)
20
Partitioning
Oracle
• Table Definition
– 1-64k partitions
– Partitioning on range, hash,
both or list
– Partitions assigned to TS
• Enhances Parallel Query
• Easier Data Management
DB2• Table partitioning
– Partitioning part of table DDL
– Partitions can be in one or multiple tablespaces
– Partitions can be rolled in/out
– Partitions can be administered independently
• Database Partitioning– Database Partitioning
Feature (DPF)• ESE Edition only
– Database is partitioned across multiple servers
– Multiple tables
![Page 21: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/21.jpg)
21
Tabelle
Oracle
– One to many tables
defined in a tablespace
• Tables and Indexes are
independent of each
other
– Index Organized Tables
DB2
– One to many tables
defined in a tablespace
• Indexes directly tied to
table definition
![Page 22: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/22.jpg)
22
Index
Oracle– Unique
– Non-unique
– Partitioning
– Function based
– Bit Map
DB2• Unique
• Non-unique
• Clustering
• Partitioned
![Page 23: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/23.jpg)
23
Index - 2
DB2• Indexes are dependent on tables. Index space must be
specified when table created.
– All indexes for table use 1 tablespace
– Index space is predefined before IX’s are created
– Indexes can be defined in same tablespace as table
![Page 24: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/24.jpg)
24
Administration
![Page 25: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/25.jpg)
25
Optimizer
Oracle
• Cost based
• Rules Based
– Backward compatibility
DB2
• Cost based more
sophisticated than Oracle
– Seven levels of optimization
– Adjusted based on query
complexity
• No Rules
![Page 26: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/26.jpg)
26
Optimizer Class
• DB2 Optimizer Class– Values are between 0 and 9, default is 5
• Determines the intensity used by the DB2 SQL Compiler when rewriting SQL
• Dynamic SQL can’t spend time optimizing, use lower class
• Static SQL optimizes once, use a higher class
• “dft_queryopt” database setting
• SET CURRENT QUEREY OPTIMIZATION n
Level Recommendation
0 Minimal amount of optimization. Only recommended for very simple SQL accessing well indexed tables. Only
nested loop joins and IX scans enabled.
1 Similar to 0 except Merge Scan and TS scan enabled.
2 Recommended for very complex queries which are infrequently executed in a decision support or OLAP
environment.
3 Closest to z/OS optimizer. Recommended for queries with 4 or more joins.
5 DEFAULT – Most cost effective method for mix of simple and complex queries. Optimization will be automatically
reduced for complex dynamic SQL if optimizer determines that the resources are not necessary.
7 Same as 5 except optimization not reduced for complex dynamic SQL
9 Used to determine whether more comprehensive optimization can generate better access plan for very complex long
running queries using large tables
![Page 27: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/27.jpg)
27
EXPLAIN
EXPLAIN_INSTANCE
EXPLAIN_STATEMENT
EXPLAIN_OPERATOR
EXPLAIN_STREAM
EXPLAIN_OBJECT
EXPLAIN_ARGUMENTEXPLAIN_PREDICATE
Oracle
DB2
PLAN_TABLE
![Page 28: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/28.jpg)
28
Clustering/Scalability: Oracle RAC
Application
server 1
Application
server 2
Application
server 3
Node 1
Instance 1
Node 1 Node 1
Instance 2 Instance 3
RAC
Database
![Page 29: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/29.jpg)
29
Clustering/Scalability: DB2 Purescale
GBP
Group Lock
Manager
Member 1
PRODDB1
CatalogDBCONFIG
Log
Catalog
Log
DBCONFIGPRODDB2
BPs
BPs
Member 2
PRODDB3
CatalogDBCONFIG
Log
Catalog
Log
DBCONFIGPRODDB4
BPs
BPs
DATA
![Page 30: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/30.jpg)
30
Monitoring
• Performance Snapshots - V$– Instance/Database
– Memory
– Disk
– User/Session
– Contention
• AWR (Statspack)– Collects Stats over time
– Similar to DB2 Snapshot
• TKPROF– Formats SQL trace data from applications
• Snapshot Monitor– Show status of database counters at
instant in time
– Buff, Lock, Sort, Stmt, Tbl, UOW
• Event Monitor– Status after the event
• Databases
• Tablespaces
• Connections
• Tables
• Statements
• Transactions
• Deadlocks
• Administrative Views and Table Procedures
DB2Oracle
![Page 31: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/31.jpg)
31
Utilities
• RMAN
• IMPORT
• EXPORT
• SQL*LOADER
• ANALYZE
• DB_VERIFY
• BACKUP
• IMPORT
• EXPORT
• LOAD
• RUNSTATS
• RESTORE
• REORG
• REORGCHK
Oracle DB2
![Page 32: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/32.jpg)
32
Backup/Recovery
![Page 33: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/33.jpg)
33
Backups
Oracle• Database
– Cold
– Hot
• Tablespace
• Components
– Database Backup
– REDO Log
– ARCHIVED REDO logs
– UNDO Records
– Control File
DB2
• Database
– Online
– Offline
• Tablespace
• Components
– Backup Image
– Incremental Copy
– Backup History File
– Active Logs
– Archive Logs
• HADR
– High Availability Disaster Recovery
• Primary/Standby Databases
![Page 34: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/34.jpg)
34
Recovery Info
• Control File
• Database name
• Data file location
• Redo log file location
• Tablespace names
• Current log sequence number
• Checkpoint information
• Log history
• Backup information
• RMAN
– - Recovery Catalog
Recovery History File• Updated:
– Backup of DB/TS
– Restore/Roll Forward of DB/TS
– Drop/Load/Reorg/Stats of a table
– Quiesce/Alter TS
• Contains– Part of DB which was copied
– When DB was copied
– Location of the copy
– Time of last restore
– DDL of tables
Oracle DB2
![Page 35: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/35.jpg)
35
• Redo Logs apply to Instance– NoArchive Mode
• Redo logs overwritten
– Archive Mode
• Recover DB, TS, DF, block
• 3 Redo log states
– Active
– Inactive
– Archived
• Rollback Segments– Exist until data committed
• Defined at database– Circular
• No roll-forward recovery
– Archival• Fully recoverable
• Similar to OS/390
• 3 log files
– Active
– Online Archived
– Offline Archived
• On Demand Archiving
– Close and archive an active log at any time
• Dual Logging
ORACLE DB2
Logging
![Page 36: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/36.jpg)
36
Circular Logging
• Non-recoverable databases
• Log files are reused
• Uses active logs only– Secondary used for overflow
• Roll-forward recovery not
possible
• Default method for new DB’s
S
E
C
O
N
D
A
R
Y
1Primary
1
2“n”
3
“n”
![Page 37: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/37.jpg)
37
Archival Logging
• Log files not reused
• Roll Forward Recovery
Online Archival -
Contains information
for committed and
externalized transactions.
Stored in the active log
subdirectory
12
13
14
15
16
Active –
Contains information
For non-committed or
Non-externalized
Transactions.
Offline Archival
Files moved from active
Log subdirectory.
Usually offline media
![Page 38: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/38.jpg)
38
Recovery Möglichkeiten
• Instance Recovery– RAC only
– Uses Redo logs to recover from
System/Instance failures
• Crash
– Uses online REDO logs
– Recovers data files
• Disaster
• Media Recovery
• Crash– Uses logs to recover from power
interrupts or application ABENDS
• Roll-Forward– Image copy plus log apply
– LOCAL TIME
• Version– Image copy
• HADR
Oracle DB2
![Page 39: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/39.jpg)
39
Datenreorganisation
• OEM Reorg Wizard
– Tables
– Tablespace
– Indexes
– Repair migrated rows
• Online Redefinition Package – Enterprise Edition only
– Online reorg
• REORG– Table
– Index
• REORGCHK– Determines when Reorg is
required
• Online
Oracle DB2
![Page 40: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/40.jpg)
40
Daten laden
• SQL*Loader Utility
– Insert/Append/Replace/Truncate
– Direct=True|False
• By-pass SGA build data block
• No Triggers
• No RI
– Parallel=True|False
• Multiple load files
• Drop all Indexes
– Recoverable/Unrecoverable
• Load– Locks single table in TS
– Insert/Replace
– RUNSTATS
– Good for large amounts of data
– READ ACCESS for rows not being loaded Load directly from a SQL query
• Import– Can dynamically create table
– Insert process
• Update
• Replace
– Good for small amounts of data
Oracle DB2
![Page 41: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/41.jpg)
41
ORACLE COMPATIBILITY FEATURES
![Page 42: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/42.jpg)
42
Einstellung für Oracle Compatibility Features
• DB2 Compatibility Vector– New registry value
• Enables Oracle compatibility
– Controls which compatibility features to turn on
– db2set DB2_COMPATIBILITY_VECTOR=ORA
• Activates all features.
![Page 43: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/43.jpg)
43
Compatibility Features
• Oracle data dictionary compatible views
– ALL_*
– DBA_*
– USER_*
• CLPPlus
– SQL*Plus compatible command line processor
![Page 44: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/44.jpg)
44
Modules
• New DB2 Object– Groups object definitions together for an application
• Functions
• Procedures
• TYPE
• VARIABLE
• Same as Oracle PL/SQL Packages
![Page 45: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/45.jpg)
45
Application Enablement
• Native PL/SQL support– No SQL translation required
– PL/SQL compiler
– PL/SQL debugger
– PL/SQL Object support
• After each row triggers
• Anonymous Blocks
• Before each row triggers
• PL/SQL packages
• Procedures
![Page 46: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/46.jpg)
46
Application Enablement
PackageDBMS_ALERT Allows sessions to semaphore
DBMS_JOB DB2 task scheduler API
DBMS_LOB Oracle LOB API
DBMS_OUTPUTBasic reporting
DBMS_PIPE Allows sessions to send data back and forth
DBMS_SQL Dynamic SQL API
DBMS_UTILITY Misc. application procedures
UTL_DIR Maintains directory aliases used w/UTL_FILE
UTL_FILE Allows working with files on DB2 server
UTL_MAIL Send Email from SQL
Common Packages Provided by DB2
![Page 47: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/47.jpg)
47
Neue DB2 Datentypen
Oracle• BFILE
• BLOB
• CHAR(n)
• CLOB
• DATE
• LONG
• LONG RAW
• NCHAR(n)
• NCLOB
• NUMBER(p,s)
• NVARCHAR2(n)
• RAW(n)
• ROWID
• VARCHAR2(n)
DB2 • BOOLEAN
• INDEX BY
• NUMBER
• Oracle DATE
• Ref Cursor type
• ROW TYPE
• TIMESTAMP(n)
• VARCHAR2
• VARRAY
![Page 48: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/48.jpg)
48
Zusammenfassung
Um erfolgreich ein Cross-Pattform DBA zu sein:
• Wissen was das Prinzip einer relationalen Datenbank ist
• Unterschiede und Besonderheiten anderer RDBMS kennen
• Gleiche Tools können hier unterstützen
• Mit DB2 LUW Version 9.7 ist es einfach von Oracle nach DB2 zu wechseln
![Page 49: Oracle und DB2 LUW - DOAG Deutsche ORACLE … und DB2 LUW Udo Brede Solution ... – Recovery Manager($) • Sqlplus ... 2 Recommended for very complex queries which are infrequently](https://reader034.vdocuments.us/reader034/viewer/2022051508/5a9eb1667f8b9a84178bb001/html5/thumbnails/49.jpg)
© 2010 Quest Software, Inc. ALL RIGHTS RESERVED
Danke für Ihre Aufmerksamkeit