Database Operations

(1)

Database Operations

1 System Operations 5 System Management

2 Deliveries Tools

-Requirements 6 High Availability

3 Performance Management 7 Printing

(2)

Database Operations

Purpose

Verify the backup/recovery procedure Verify the disaster recovery procedure Verify database performance management Verify database monitoring procedure Verify growing management procedures

Key Issues

Backup/recovery concept Disaster recovery concept File distribution

Database performance Database monitoring Growing management

(3)

Key Performance Indicators

Indicator

Value

Target

Avg. DB Request Time in Dialog Task

Avg. DB Request Time in Update Task

Reads / User call

SQL Statements with inefficient search strategy

SQL Statements with high data transfer volume

DB Size

Last Month DB Growth

Top growing tables

(4)

Database Layer: Key Questions

Is backup strategy appropriate ?

Are emergency procedures defined ?

Are administrative tasks for regular operation defined ?

WHAT?

WHO?

Database administrator

Operating system administrator

System administrator

Technical team lead/Technical consultant

Network administrator

(5)

Backups: How often? When ?

Considerations for Setting Up a Backup Strategy

Volume of data backed up? Backup duration?

Backup cycle length? Costs?

Administrator skills

24-hour availability? Maximum downtime?

(6)

Emergency Procedures for Worst Case Scenario

Obtain new hardware

Apply database backup

Recover database

(7)

Free space

Database logs

Monitoring Tasks

Missing indexes

Database

performance

Last successful

backup

Expensive SQL statements

(8)

Maintaining Your Database

Apply database updates

and patches provided by your

database manufacturer

Manage the growth of your database

(9)

Subunit 1

Backup Strategy and Tape Management

Subunit 2

Scheduling, Performing, and Monitoring Backups

Subunit 3

Restore and Recovery

Subunit 4

Advanced Backup Techniques

Subunit 5

Storage Management

(10)

A good database backup strategy prevents data

loss and minimizes system downtime

External factors

(Such as fire or water damage)

Physical errors

(Such as hardware failure)

DROP MARA

Logical errors

(Such as a deleted table)

Data

loss

The Importance of Database Backups

Procedure and Escalation Plan

(11)

Physical

data check:

Verify backup

on tape

Logical

data check:

Verify database

consistency

Oracle data files

ORA-1578: Oracle data

block corrupted

Database backup

(12)

You must recover the complete database to a point in time before the error

DROP MARA

Logical errors

(Such as a deleted table)

Time when logical

error occurred

Time when database is

stopped for recovery

Lost information

Logical error recovery

Possible Causes of Data Loss (1)

12:50

4:00

(13)

If one offline redo log file is lost, none of the files

that follow it can be used

Sequence of offline redo log files:

Forward recovery

A database backup is restored and you want to

recover data from offline redo log files

Lost offline redo log file

Point in time of

the database error

Intact but unusable

offline redo log files

Lost information

Time

Possible Causes of Data Loss (2)

(14)

Disaster recovery

External factors

(Such as fire or water damage)

Physical errors

(Such as hardware failure)

Only data saved on

tape can be recovered

Only tapes stored in a safe

location can be recovered

Possible Causes of Data Loss (3)

(15)

Additional

Offline redo log

file backup (x2)

28 days

Offline redo log

file backup (x2)

Verify the database

Verify the backup

Online

Verify the backup

Offline

(16)

Media

cpio/dd

Control

file Datafiles redo log filesOnline redo log filesOffline

Oracle database

Media

BRRESTORE Detail logDetail log

Detail log

Detail log BRARCHIVE

cpio/dd parallel

BRBACKUP

(17)

Objects that need to be backed up

R/3 data

Computing center data

R/3 interfaces SAP executables Operating system Database executables

Database objects

Data files

Online redo log files

Control file Profiles Offline redo log files

Offline redo log file backup

Offline redo log file backup Database backup Database backup BRBACKUP BRARCHIVE BRRESTORE

Backup Objects

(18)

BRBACKUP

BRARCHIVE

?

Length of

backup cycle

Number of parallel

backup devices

+ 30% Reserve

Frequency

of backups

Database

size

Number and size of redo

log files in a backup cycle

Tape pools

(19)

Initialize new tapes, non-SAP tapes, or locked tapes:

Rename non-locked tapes:

brbackup -i force or brarchive -i force

.tape.hdr0

Write the label to the tape that also contains the tape name ...

volume_backup = (<SID>B01,<SID>B02... volume_archive = (<SID>A01,<SID>A02... ...

Profile init<SID>.sap contains the tape names:

(20)

Tape label

Tape checks:

Tape name

Lock period

Use count Tape name

Database name

Timestamp of last backup

Number of backups performed

Show tape label contents:

brbackup -i show or brarchive -i show

...

expir_period = 28

tape_use_count = 100 ...

Profile init<SID>.sap:

Tape Label Contents and Tape Checks

Error

Warning

(21)

BRBACKUP

Tables SDBAH and SDBAD

Logical tape locking

List of tapes used in expir_period ... C11B05, C11B06, ... Profile: init<SID>.sap ... volume_backup = (C11... volume_archive = (C11... ... Days 1 2 28 29 30 31 32 33 C11B01 C11B02 expir_period = 30 expir_period = 30

Physical tape locking

.tape.hdr0

BRARCHIVE

Tape Locking

28 28 days days

(22)

28 days

Offline

Additional

Online

Archives (2x)

Tape management active,

BRBACKUP finds tape names

+

Tape label check active

=

Only accepts tapes with requested

names and with expired lock period

C11B01 C11A01

BRBACKUP

BRARCHIVE

Tables SDBAH and SDBAD

(23)

28 days

Offline

Additional

Online

Archives (2x)

C11B01 C11A01

BRBACKUP

BRARCHIVE

Scenario 2: Manual Tape Selection

Tape lock expiration will be checked +

Tape management is not active =

To select any tape manually

brbackup -v SCRATCH or

brarchive -v SCRATCH

Tape lock expiration will be checked +

Tape name is changed to the currently required tape name

=

You can use this option to replace

(24)

28 days

Offline

Additional

Online

Archives (2x)

C11B01 C11A01

BRBACKUP

BRARCHIVE

Scenario 3: Tape Selection by an External Tool

Search

mechanism

Tape management is

deactivated

but tape name is checked

+

Tape label check active

=

Only accepts tapes

- With the specified names

- With expired lock period

brbackup -v C11M01,C11M02

(25)

Offline redo log n Detail log Summary log BRBACKUP: BRARCHIVE: .tape.hdr0 init<sid>.ora

init<sid>.dba init<sid>.sap DB file 1

DB file n Detail log Summary log reorg.log struct.log reorg.log struct.log .tape.hdr0 init<sid>.ora init<sid>.dba init<sid>.sap Offline redo log 1 Control file

Tape Layout

(26)

BRBACKUP Data files BRBACKUP Data files Log files Log files .... .... .... ............ ............_.... BRARCHIVE Offline redo log files BRARCHIVE Offline redo log files .... .... .... ....

init<SID>.sap sapr3.SDBAH_sapr3.SDBAD

Log files Log files .... .... .... ............ ............_.... CCMS Planning Calendar

Planning Goto Listing Help System

MON

MON TUETUE WEDWED THUTHU FRIFRI SATSAT SUNSUN

MON

BRBACKUP -t ... or

BRARCHIVE ...

Command prompt

- x

(27)

??? ? ? ? .... .... .... .... init<SID>.sap

compress

= hardware

compress_cmd

=

“

compress

-

b 12

-

c $ > $

”

compress_dir

= /oracle/<SID>/

sapreorg

tape_copy_cmd

=

dd

exec_parallel

= 0

tape_address

= /dev/rmt/0mn

tape_address_rew

= /dev/rmt/0m

tape_address_arch

= /dev/rmt/1mn

tape_address_rew_arch

=

/dev/rmt/1m

backup_ mode

backup_ mode = all= all

backup_ type

backup_ type = online [offline]= online [offline]

volume_backup

volume_backup == (<SID>B01, <SID>B02, ...)(<SID>B01, <SID>B02, ...)

tape_ size tape_ size = 32G= 32G tape_use_count tape_use_count = 100= 100 expir_period expir_period = 28= 28 backup_dev_type

backup_dev_type = tape= tape

archive_function

archive_function = copy_delete_save

volume_archive

volume_archive = (<SID>A01, <SID>A02, ...)

tape_size_arch

tape_size_arch = 6000M

(28)

BRBACKUP/BRARCHIVE cpio/dd

Data

...

dd: Error cpio: Error or cpio continuation volume

Data

cpio/dd cpio/dd

...

BRBACKUP/BRARCHIVE cpio/dd

Data

...

Data

cpio/dd cpio/dd

...

BRBACKUP: SAP follow-up tape

Correct

Parameter tape_size

Physical tape size

Parameter tape_size

Data

Physical tape size

Profile init<SID>.sap Parameter tape_size

(29)

400 MB 400 MB 400 MB 400 MB BRBACKUP 400 MB 400 MB 400 MB 400 MB compress compress = no= no tape_size tape_size = 1800M= 1800M init<SID>.sap 400 MB 400 MB

Tape station

without

hardware compression

200 MB 200 MB 200 MB 200 MB 200 MB 400 MB BRBACKUP 200 MB 200 MB 200 MB 200 MB 400 MB 400 MB 400 MB 400 MB 400 MB 400 MB 400 MB 400 MB compress

compress = hardware= hardware

compress_cmd

compress_cmd==““compresscompress--b 12b 12--c $ > $c $ > $””

exec_parallel exec_parallel = 0= 0 tape_size tape_size = 1600M= 1600M .... .... .... .... init<SID>.sap

Tape station

with

hardware compression

Once per cycle: Determine compression rate 400 MB 400 MB 400 MB 400 MB

Hardware Compression

(30)

CCMS Planning Calendar

MON

BRBACKUP -t online -d tape -m all -c -u system

Command prompt

- x

Scheduling and Performing a Normal Database Backup

tape_copy_cmd

tape_copy_cmd== dddd oror

tape_copy_cmd

tape_copy_cmd==cpiocpio

.... .... .... .... init<SID>.sap 1 _Calendar

Schedule an Action for Tue 05

18:00:00

Start time Period (weeks):

Full database offline + redo log backup Full database offline backup

Full database online + redo log backup Full database online backup

Redo log backup

Partial database offline backup Partial database online backup Check optimizer statistics Adapt next extents

Check database

h - Backup database

SAPDBA

- x

c - Backup device type tape d - Objects for backup all e - Backup type online g - Query only no S - Start BRBACKUP

SAPDBA

(31)

Phases of a Whole Database Backup

Save control file to disk Save control file to disk

Back up saved control file Back up saved control file For all tablespaces to be backed up: Begin tablespace backup mode Back up tablespace data files End tablespace backup mode

For all tablespaces to be backed up: Begin tablespace backup mode Back up tablespace data files End tablespace backup mode

Log file switch Log file switch

Back up control file Back up control file

Start database Start database Shut down database Shut down database

Back up data files Back up data files

*Back up online redo log files *Back up online redo log files

Offline

*Start database

*Shut down database *Shut down database Retrieve file names of data and online redo log files from database

and retrieve names of control files from init<SID>.ora

Retrieve file names of data and online redo log files from database and retrieve names of control files from init<SID>.ora

Back up tape header, init<SID>.sap, init<SID>.dba, and init<SID>.ora Back up tape header, init<SID>.sap, init<SID>.dba, and init<SID>.ora

Back up reorg.log, struct.log, detail log, and summary log Back up reorg.log, struct.log, detail log, and summary log

Online

tape_copy_cmd

tape_copy_cmd== dddd oror tape_copy_cmd

tape_copy_cmd==cpiocpio ....

.... .... ....

(32)

Data files compress_dir BRBACKUP -c -w use_dbv

A

B

N

.

A

?

Corruption

Log files Log files .... .... .... ............ ............_....

Oracle

8-KB

Block

=

?

File

length

Logical Verification of a Database Backup

Once per week. Minimum: once per cycle

(33)

Data files

compress_dir

BRBACKUP -t offline -c -w

A

B

N

A

.

...

Check

database

If possible:

_{Once each cycle}

=

?

Offline

(binary)

(34)

sapr3.SDBAH sapr3.SDBAD Log files Log files .... .... .... ............ ............_.... CCMS Planning Calendar

MON

cd /oracle/<SID>/sapbackup cat back<SID>.log | more cat b<timestamp>.and | more

Command prompt

- x

L - Show/Cleanup

SAPDBA

- x

a - Show log files / profiles

SAPDBA

- x

e - BRBACKUP log files

SAPDBA

- x

CCMS

Monitoring a Database Backup

Database Operations Monitor (DB24)

CCMS Calendar (DB13)

(35)

BRARCHIVE option -cds (copy, delete, save)

Status of an offline redo log file

4 2 4 2 4 2 ../saparch <SID>A01 <SID>A01 <SID>A02 <SID>A02

ARCHIVED

DELETED

SAVED

COPIED

../saparch <SID>A01 <SID>A01 <SID>A02 <SID>A02 43 42 44 4 6 4 5 <SID>A03 <SID>A03 42 44 43 42

BRARCHIVE -cds

Mon Tue Wed

4 2 4 2 42 43 44 42 43 44 45 46

(36)

MON

brarchive -cds -d tape -c -u system

Command prompt

- x

1 _Calendar

18:00:00

Start time Period (weeks):

Redo log backup

Partial database offline backup Partial database online backup Check optimizer statistics Adapt next extents

Check database

i - Back up offline redo log files

SAPDBA

- x

a - Archive function Copy, delete, and save archive logs c - Archive device type tape

s - Start BRARCHIVE

SAPDBA

- x

Performing Offline Redo Log File Backups

(37)

sapr3.SDBAH sapr3.SDBAD Log files Log files .... .... .... ............ ............_.... CCMS Planning Calendar

MON

cd /oracle/<SID>/sapbackup cat arch<SID>.log | more cat a<timestamp>.cds | more

Command prompt

- x

L - Show / Cleanup

SAPDBA

- x

a - Show log files / profiles

SAPDBA

- x

f - BRARCHIVE log files

SAPDBA

- x

Monitoring Offline Redo Log File Backups

Database Operations Monitor (DB24)

CCMS Calendar (DB13)

(38)

sapr3.SDBAH sapr3.SDBAD expir_period_SAPDBA_normal expir_period_SAPDBA_normal = 11= 11 expir_period_daily_check expir_period_daily_check = 5= 5 expir_period_BRBACKUP expir_period_BRBACKUP = 30= 30 expir_period_BRARCHIVE expir_period_BRARCHIVE = 30= 30 expir_period_oracle_trace expir_period_oracle_trace = 1= 1 .... .... .... .... init<SID>.dba delete b<timestamp>.and Command prompt - x Log files Log files .... .... .... ............ ............_.... L - Show/Cleanup SAPDBA - x

b - Cleanup log files / directories

SAPDBA

- x

a - SAPDBA log files and dump directories b - SAPDBA daily check log files

c - BRBACKUP log files d - BRARCHIVE log files f - ORACLE traces and audits

SAPDBA

- x

(39)

Detecting an archiver stuck Monitoring the directory saparch

f - Archive mode

SAPDBA

- x

c - Show all archive information

SAPDBA - x FREE SPACE : ... SAPDBA - x cd saparch df-k. Command prompt - x ../saparch alert_<SID>.log:

Thread <n> cannot allocate new log, All online logs needed archiving

__________ __________ __________ __________ SAPGUI - _.… _.… _.... x

Resolving an archiver stuck

Remove

dummy files

and

Run BRARCHIVE

<SID>A01 <SID>A01

Dummy

Freespace Problems in Directory saparch

(40)

Error types:

Statement

Process

Instance

User

Media

Database Errors

“Most importantly,

be prepared for disasters.

Don’t think you will never

see a failure. Every DBA will

experience a database failure.

It’s just a matter of when...

Good luck.”

(41)

How to Handle Problems

Do not make any rash decisions

Analyze the problem in detail

Create a problem-solving strategy

Before restoring any files, check:

What is causing the problem

Whether there is enough disk space to save and restore

files

Whether a hardware extension is necessary

The file system and mount points

The availability of backups

(42)

Control files 10 10 10 38 38 3 8

Offline and online redo log files

11 12 37 3 8 . . . Data files 10 10 1 0 38 38 Full backup 10 9 36 38 Legend:

Scenario

(43)

38 38 38 38 38 38 38 38 38 10 38 10 Partial restore 10 11 37 3 8 . . . Complete recovery 39 . . . mount open

Partial Restore and Complete Recovery

Control files

(44)

Full restore 10 10 10 10 10 10 9 10 11 . . . 37 38 10 11 . . . open

Database Reset

Control files

(45)

10 10 10 10 10 10 10 11 . . . 25 1 2 . . . 26 . . . 37

mount Open resetlogs

24 24 25 25 25 25 25 25 1 1 1 1 1 1 Incomplete recovery

Point in Time Recovery

Control files

Data files

(46)

Detail logs

back<SID>.log arch<SID>.log Recovery script

saparch Find offline

redo log files Restore

data files

Recover database Restore offline

redo log files Find

backups Check

Database

Database

(47)

Detail logs

back<SID>.log arch<SID>.log Recovery script

Check Database

Find offline redo log files Restore

backup files

Recover database Restore offline

redo log files Find

backups

saparch

Database

(48)

Partial Restore and Complete Recovery Limitations

Logs

No data or offline redo log file backups available

No BRBACKUP/BRARCHIVE logs available

Control files damaged

Online redo log files damaged init<SID>.* No init<SID>.*

files available

Problem

Solutions

Perform a database reset Perform a point in time recovery

Use the SAPDBA function

Restore individual files

Restore these files from tape using command

brrestore -n init_ora

Copy one of its mirrors

(49)

Database

sapreorg Save current online redo log files

and control file

Overwrite all data files, control files, and online redo log files Find full offline backups Detail logs back<SID>.log Database mount Database open

(50)

Recovery script saparch

Database

sapreorg Save online redo log files and control file Find Online_Cons backups Detail logs back<SID>.log Recover database until cancel Database open resetlogs

Database Reset Using a Consistent Online Backup

Overwrite all Data files and

control files

Offline redo log files

(51)

Find offline redo log files Find full offline/

online backups Detail logs back<SID>.log arch<SID>.log Recover until? Status: allowed? reorg<SID>.log

Database

Input: time

Not allowed if (for example): - No backup specified

- No offline redo log files found - Recovery over tablespace reorg - Backup before open resetlogs

Full Restore and Recovery (1)

(52)

Recovery script

saparch sapreorg

Save online redo log files and control file

Recover database (until time)

Database

Database open (resetlogs) Overwrite all

Data files and control files (if necessary)

Offline redo log files

Full Restore and Recovery (2)

(53)

Duration of a backup

Time window for a recovery

High availability

Training

Administrative workload

Acquisition costs

(54)

... ... Logs init

files Tape

header

Data files Offline redo log files

in saparch

Database backup

Offline redo log file backup

brbackup -m all -c -a -cds -c

(55)

10 10 10 Data files 25 25 25 25 25 25 10 10 10 11 . . . 24 9 10 Control files

Offline redo log files in saparch 25 10 10 init files ... . . . . . . 25 10 ... 25 Logs

...begin backup ...end backup

brbackup -t online_cons

(56)

DDS DDS DLT DLT DLT BRBACKUP init<SID>.sap exec_parallel = 0

tape_address = (dev1, dev2, dev3)

archive_function = double_save_delete

tape_address_arch = (dev4, dev5)

BRARCHIVE

Control files

Offline redo log files in saparch

(57)

Data files . . .

Mon Tues Sun

brbackup -m PSAPBTABD

brbackup -m all -f 7

BRARCHIVE BRARCHIVE BRARCHIVE

brbackup -m PSAPSTABD

(58)

System, roll, and temp tablespace brbackup -m all_data Data tablespaces Pure index tablespaces

(59)

Data files

init<SID>.sap exec_parallel = 0

tape_address = (dev1, dev2, dev3)

backup_root_dir = (lvol1, lvol2)

dir1 dir2 Fast 1st step brbackup -d disk -e 4 “Slow” 2nd step brbackup -b last -d tape

(60)

$ORACLE_HOME . . . mirrlogA sapbackup sapcheck saptrace origlogA dbs sapdata<n> sapreorg mirrlogB saparch origlogB sapdata1 btabd_1 NEW_DB_HOME . . . mirrlogA sapbackup origlogA dbs sapdata<n> mirrlogB origlogB sapdata1 btabd_1 init<SID>.sap new_db_home = /oracle/NEW brbackup -d disk_copy

(61)

1. Split mirror 2. Mount 3. Backup 4. Unmount 5. Resync brbackup -t online/offline_split -d tape

Production server Backup server

Mirror

(62)

Production server

saparch Database open

sapr3.SDBAH

sapr3.SDBAD brarchive_{-d disk -f -w}-sd

SAP Tools and the Oracle Standby Database

Standby server

Database mounted standby or offline for backup

Recovery brarchive -ssd -f -m 60 _{-t offline_standby}brbackup saparch

ftp

NFS

OR

(63)

BACKINT External backup server init<SID>.sapbrbackup $ORACLE_HOME . . . saparch sapdata<n> sapdata1 btabd_1 SAPDBA brarchive brrestore

Backup Inquire Restore

brbackup

init<SID>.utl

init<SID>.sap

backup_dev_type = util_file_online

util_par_file = init<SID>.utl

(64)

Disks

File system

Datafile1 Datafile2

. . .

Space Management: Review

8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K 8K Extent 48K Extent 144K

Segment

192K

1 2 Initial Extent Next Extent Segment (table/index) Data block Table ABC Index XYZ Table DEF Segment 192K Segment 640K

. . .

. . . Segment 192K Segment 256K Segment 80K Segment 224K Datafile2 Datafile2 Datafile1 Datafile1 Tablespace

(65)

. . .

0

0 11 22 33 44 55

Objects (tables/indexes) _Tablespace

<tablespace>.data1 2 3 1 0 2 3 0 0 1 0 4 1 2 4 5 Gaps

Oracle block Internal

fragmentation Used Free 3 5 1 4 External fragmentation 2

Space Management: Fragmentation Types

Critical object

(66)

<Timestamp.chk> in

/oracle/<SID>/sapcheck CCMS Planning Calendar

R/3 _x

MON MON CheckDB CheckDB TUE TUE CheckDB CheckDB WED WED CheckDB CheckDB THU THU CheckDB CheckDB FRI

FRI SATSAT

CheckDB CheckDB SUN SUN sapdba sapdba --nextnext MON MON CheckDB CheckDB TUE TUE CheckDB CheckDB WED WED CheckDB CheckDB THU THU CheckDB CheckDB FRI

FRI SATSAT

CheckDB CheckDB SUN SUN sapdba sapdba --nextnext MON MON CheckDB CheckDB TUE TUE CheckDB CheckDB WED WED CheckDB CheckDB THU THU CheckDB CheckDB FRI

FRI SATSAT

CheckDB CheckDB SUN SUN MON MON CheckDB CheckDB TUE TUE CheckDB CheckDB WED WED CheckDB CheckDB THU THU CheckDB CheckDB FRI

FRI SATSAT

CheckDB CheckDB SUN SUN sapdba sapdba --nextnext

Daily Monitoring: sapdba -check

Database table DBMSGORA

1 _Calendar

18:00:00

Start time

Period (weeks):

Redo log backup

Partial database offline backup Partial database online backup Check optimizer statistics Update optimizer statistics Adapt next extents

Check database Start immediately Start check Warnings Errors Total

Configure check Database operations monitor History Database tableStandard

DBMSGORA

Refresh every Delete after View the last

Check results Settings

10 seconds inaktiv 100 days aktiv 10 days

History: All messages

6 5 11

Result Date Time Days Error type Error name Text

W E

08/21/1999 22:00:00 10 PROF LOG_SMALL_EN... LOG_SMALL_ENTRY_MAX_SIZE

08/21/1999 22:00:00 10 DBO OPT DB Operation opt never started or finished successful

Database Check: Overview of Results

sapdba -check

(67)

Configuration table

DBCHECKORA

Configuring sapdba -check

Type Parameter Object Actv. Condition Description

Change Database Check Parameter

Database analysis tool (SAPDBA) ARCHIVE_STUCK

Yes

Error

ARCHIVE STUCK - FS SPACE #1 MORE THAN #2 FULL greater than (old 80 Percentage

Repeat period Corrective measure Changed by Duration Type Time Unit Operation

Program SAPDBA: BACKUP ARCHIVE LOGS if

DB17

Number of SAPDBA check parameters

ARCHIVE_STUCK

DBA E > 80 P P SAPDBA:BACKUP ARCH... ARCHIVE STUCK - FS SPAC...

Total Status Profile param. Oracle alerts Operations 67 19 28 13 7 66 1 Active Inaktiv DBA DBA DBA DBA DBA DBA DBA DBA CONTROL_FILE_MISSING CONTROL_MIRROR CRITICAL_SEGS DF_OFFLINE FILE_MISMATCH FILE_MISSING FILE_TYPE_UNKNOWN FS_FULL E E E E E E W W > > P P 1 95 INIT<SID>.ORA

SAPDBA: TABLESPACE A... DO A “CREATE CONTROL... SAPDBA: RESTORE/RECO... EXTEND FILESYSTEM OR... 01 / 22 / 1999

P E

CA...

CONTROL FILES ARE MISSI... CONTROL FILE(S) ARE NOT... SEGMENT(S) #1 WOULD CAU... DATAFILE(S) #1 OFFLINE FILE TYPE DOES NOT MATC... DATA FILES ARE MISSING FILE TYPE ( DATAFILE , RA... # 1 FILE SYSTEM(S) # 2 ARE... Typ Parameter O... Actv. S.. Op... Val. U... Per... Unit Date U... C CorrMeasure Description

(68)

ADD A DATA FILE:

File size depends on the estimated increase of the tablespace objects. Check for the number of data files in the database.

<tablespace>.data1 Critical object Extents

New file

<tablespace>.data2 Back up extended

tablespace and control files

Tablespace Extension

RESIZE THE DATA FILE:

Extend the size of the data file depending on the space available on the file system and size of critical object.

<tablespace>.data1

Critical object

Original size _{After Resize}

(69)

Table TGORA (storage parameters for R/3 tables) Category INIT NEXT MINEXTENT MAXEXTENT

0 16 40 1 300 1 16 160 1 300 2 16 640 1 300 3 16 2560 1 300 4 16 10240 1 300 5 16 20480 1 300 6 16 40960 1 300 7 16 81920 1 300 8 16 163840 1 300 9 16 327680 1 300 10 16 655360 1 150 11 16 1310720 1 150 12 16 2621440 1 150 13 16 5242880 1 150 14 16 10485760 1 150

Table IGORA (storage parameters for R/3 indexes) Category INIT NEXT MINEXTENT MAXEXTENT

0 16 40 1 300 1 16 80 1 300 2 16 160 1 300 3 16 640 1 300 4 16 2560 1 300 5 16 5120 1 300 6 16 10240 1 300 7 16 20480 1 300 8 1640960 1 300 9 16 81920 1 300 10 16 163840 1 150 11 16 3276801 150 12 16 6553601 150 13 16 1310720 1 150 14 16 2621440 1 150

R/3 ABAP Dictionary: Display technical settings x

Logical storage parameters

Name ZPROGRAM Transparent Table Short text Test Table for technical settings

Last changed TND 24/08/1999 Status Active Saved

Data class APPL1 Transaction data. transparent tables

Size category 4 Data records expected: 39.000 to 3.100.000

(70)

R/3 x

MON

sapdba

--nextnext

MON

sapdba

--nextnext

MON

sapdba

--nextnext

MON

sapdba

--nextnext

Example sapdba -next:

Table size: 900 MB 10 % value: 90000KB Current NEXT: 20480KB NEXT candidate: 90000KB Next larger TGORA value: 163840 NEXT candidate: 90000 Next smaller TGORA value: 81920 Current NEXT: 20480 Technical settings: Category 3: 2560 10485760 5242880 2621440 1310720 655360 327680 163840 81920 40960 20480 10240 2560 640 160 40 TGORA values (KB)

New value for NEXT: 163840

Using sapdba -next

Database table DBMSGORA

1 _Calendar

18:00:00

Start time

Period (weeks):

Redo log backup

Partial database offline backup Partial database online backup Check optimizer statistics Update optimizer statistics Adapt next extents

Check database

(71)

Daily Monitoring: Tables and Indexes

Database ORACLE Date/time of this analysis 08/24/1999 07:01:30

NameTCC

Database System

Refresh

Refresh Checks_Checks Space statistics_{Space statistics}

Total number 27

Total size/kb 12.123.016

Total free/kb 3.050.320 25%

Minimum free/kb 4.024

Max. autoextensible/kb AutoExtend off

Tablespaces Current sizes Current sizes Freespace statistics Freespace statistics Space statistics Space statistics Tables Indexes Total number 13.064 15.305 Total size/kb 5.915.664 2.979.432

More than 1 extent 1.082 1.591

Missing in database 0 1

Missing in R/3 DDIC 0 0

Space-critical objects 0 0

Tables and indexes

Detailed analysis Detailed analysis

Space critical objects Space critical objects

Missing indexes Missing indexes Space statistics Space statistics Day Time M T W T F S S 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 ---RSDBPREV 1 C X:X:X:X:X:X:X X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X:X RSORATDB 1 C

(72)

Tables and Indexes: Important Reports

09/05/1999 10:40:26 DB_SERVER Critical table/index growth

Intervals: 08/06/1999 - 09/05/1999 Measurements: 29

Table Name Type Size(KB) NextExtSize Extents MaxExtents FirstExtent Tablespace Total Growth (Kbyte) Total Growth defined %used (Kbyte)

ATAB~0 INDEX 72.720 10.880 160 168 68 300 56 45.936 PSAPPOOLI SMENINTT~ INDEX 2.416 2.400 80 31 30 300 10 16 PSAPSTABI SKAT~001 INDEX 9.960 2.080 80 89 26 300 30 2.904 PSAPSTABI

Critical growth of tables/indexes in the last 4 weeks

Table Space History

09/05/1999 10:40:26 DB_SERVER

Intervals: 08/06/1999 - 09/05/1999 Measurements: 30 Scale: Day

Scale: Day Size (KB) Free(KB) Used (KB) %-Used Tables/Indexes Extents Tablespace Total Chg/day Total Total Chg/day Total Chg Total Chg/day Total Chg/day PSAPPOOLD 747.512 0 38.944 708.568 2.415 94 0 6.619 0 8.692 22 PSAPPOOLI 563.200 0 97.888 465.312 1.793 82 0 6.751 0 9.296 21 PSAPSTABI 921.600 0 70.288 851.312 1.909 92 0 4.404 0 6.156 10 PSAPSTABD 1.013.744 0 31.024 982.720 3.384 96 0 3.325 0 4.307 8 PSAPROLL 307.200 0 182.392 124.808 3.084 40 1 15 0 120 3 PSAPBTABD 735.216 0 231.336 503.880 1.249 68 0 2.344 0 2.471 1 PSAPBTABI 409.600 0 125.768 283.832 157 69 0 3.222 0 3.542 1 PSAPSOURCED 102.400 0 69.296 33.104 6 32 0 47 0 49 0 PSAPSOURCEI 102.400 0 47.520 54.880 3 53 0 54 0 60 0 PSAPTEMP 307.200 0 307.192 8 36 0 0 0 0 0 0 PSAPUSER1D 8.192 0 4.024 4.168 36 50 0 4 0 4 0 PSAPPROTI 33.792 0 16.592 17.200 52 50 0 114 0 131 0

(73)

Detail log: 9908221023.aly

*********************************************************************** (12690 tables analyzed - sorted by empty space in descending order) TABLESPACE_NAME TABLE_NAME EMPTY(kb) NEVER_BEEN_USED(kb) USED(kb) PSAPSTABD E071K 42032 42032 5128 PSAPEL40AD D010L 31112 31112 3168 PSAPPOOLD ATAB 17112 17112 58368 PSAPES40AD DOKCLU 13136 13136 323448 PSAPES40AD D010S 10088 10088 774848 *********************************************************************** CHARTS OF 20 EMPTY INDEXES - USING: VALIDATE STRUCTURE

(6751 indexes analyzed. sorted by empty space within BTREE = USED_BY_BTREE - USED)

TABLESPACE_NAME INDEX_NAME TOTAL(kb) USED_BY_BTREE(kb) USED(kb) PSAPPOOLI ATAB~0 72720 72648 50563 PSAPPOOLI T512T~0 7280 7200 4641 PSAPPOOLI RTXTL~0 11920 10072 7745 PSAPPOOLI T800Y~0 8800 8744 6726

SAPDBA Detail Log

Analyzing Internal Fragmentation

sapdba -analyze

DB02

>> Detail Analysis

Data from DBA_SEGMENTS/DBA_INDEXES Space Allocated space..Kbyte 72.720 blocks... 9.090 extents... 168 Block structure Blocksize...byte 8.192 Pct_free... 10 Transactions initial.. 2 maximum.. 255 Header minimum....byte 159 Data maximum...byte 7.230 Process freelists... 1 Freelist groups... 1

(74)

Example for a table reorganization 1) Export table 2) Drop table 3) Import table Recreate index sapreorg Data_1 0 0 1 2 2 1

Example for an index recreation 1) Drop index 2) Recreate index PSAPTEMP Additional storage space required Additional storage space required Data_1 0 0 1 2 2 1 Data_1 0 0 2 1 Data_1 0 1 0 2

Reorganization: Basics

(75)

Disk_4 1 0 2 3 0 1 0 4 2 5 3 1 0 2 3 0 1 0 4 2 5 3 Disk_3 1 0 2 3 0 1 0 4 2 5 3 1 0 2 3 0 1 0 4 2 5 3 Disk_2 1 0 2 3 0 1 0 4 2 5 3 1 0 2 3 0 1 0 4 2 5 3 Disk_1 1 0 2 3 0 1 0 4 2 5 3 1 0 2 3 0 1 0 4 2 5 3 Disk "hot spots" Disk accesses [%] Disk

Reorganization: Reasons

Detail log: 9909050715.aly

CHARTS OF 20 EMPTY INDEXES - USING: VALIDATE STRUCTURE (6751 indexes analyzed. sorted by empty space within BTREE = USED_BY_BTREE - USED)

TABLESPACE_NAME INDEX_NAME TOTAL(kb) USED_BY_BTREE(kb) USED(kb) PSAPPOOLI ATAB~0 72720 72648 20563 PSAPPOOLI T512T~0 7280 7200 4641 PSAPPOOLI RTXTL~0 11920 10072 7745 PSAPPOOLI T800Y~0 8800 8744 6726 PSAPPOOLI T52C5~0 5480 5424 3760

SAPDBA Detail Log

Fragmented Indexes . . . 0 0 11 22 33 44 55 Oracle block Internal fragmentation Free Used

(76)

Moving / Renaming data files Disk x 1 0 2 3 0 1 4 2 5 3 1 4 0 Disk y 1 0 2 3 0 1 4 2 5 3 1 4 0 Reorganization of a tablespace Data file_1 1 0 2 3 0 1 0 4 2 5 3 Data file_2 7 6 8 9 1 5 4 10 6 11 7 Data file_2 0 Data file_1 0 0 0

Reorganization of a tablespace with data files

Data file_1 Data file_2 1 0 2 3 0 1 0 4 2 5 7 6 8 9 5 10 11 Data file_new 0 0 0 0 0 Reorganization of a single object or a list of objects

Phases Directories

Create script and restart file sapreorg/<timestamp>/<timestamp>.<extension>. Check the free space sapreorg, PSAPTEMP.

Perform a reorganization PSAPROLL, objects tablespace

Phases Directories

Create script and restart file sapreorg/<timestamp>/<timestamp>.<extension>.

Check the free space sapreorg, PSAPTEMP.

Perform a reorganization PSAPROLL, objects tablespace

Data file_1 0 0 1 2 2 1 Data file_1 0 0 2 1 Disk "hot spots" Fragmented indexes Internal fragmentation

Internal and external fragmentation Fragmented indexes

(77)

Legend:

++ = very good, + = good, o = average, NA = Not applicable

I = Parallel on index, T = Parallel on table. (Oracle PARALLEL clause) Legend:

++ = very good, + = good, o = average, NA = Not applicable

I = Parallel on index, T = Parallel on table. (Oracle PARALLEL clause)

Reorganization: Methods

Reorganization of tables

Method Runtime Security Additional space R3Chop Parallel Compress Restrictions Create table ++ ++ Objects tablespace NA T NA No tables with

as select PSAPROLL LONG/RAW fields

SAPDBA unload. + + sapreorg NO P NO export < max_file_size

SQL*loader PSAPTEMP (usually 2 Gbyte)

Oracle o + sapreorg YES P YES

export/import PSAPTEMP Reorganization of tables

Method Runtime Security Additional space R3Chop Parallel Compress Restrictions Create table ++ ++ Objects tablespace NA T NA No tables with

as select PSAPROLL LONG/RAW fields

SAPDBA unload. + + sapreorg NO P NO export < max_file_size

SQL*loader PSAPTEMP (usually 2 Gbyte)

Oracle o + sapreorg YES P YES

export/import PSAPTEMP

Reorganization of indexes

Method Runtime Security Additional space Parallel

Alter index ++ ++ Objects tablespace I

rebuild PSAPTEMP

Index + + PSAPTEMP I

recreate

Reorganization of indexes

Method Runtime Security Additional space Parallel

Alter index ++ ++ Objects tablespace I

rebuild PSAPTEMP

Index + + PSAPTEMP I

(78)

Compress extents: Yes No 0 0 1 1 2 0 0 2 3 1 4 0 1 2 3 4

Reduce object size:

Yes No 0 0 1 1 2 0 0 2 3 1 4 0 Freespace

Chop (export dump):

Yes No sapreorg 2 GB 2 GB sapreorg 4 GB

Compress (export dump):

Yes No sapreorg 1 GB 2 GB sapreorg 2 GB 2 GB

Parallel export / import

(Example: 2 processes in parallel)

exp_imp_degree = 2 sapreorg1 dump1 sapreorg2 dump2 Database Process 1 _{Process 2}

Both conditions required: exp_imp_degree = 2 and 2 dump destinations Possible? Possible?

Reorganization: Options

(79)

Subunit 1

Oracle Transactions

Subunit 2

I/O Tuning and Disk Configuration

Subunit 3

Database File Properties

Subunit 4

I/O Tuning: Buffer Cache

Subunit 5

Tuning the Oracle Database Writer

Subunit 6

Tuning the Oracle Log Writer

Subunit 7

Tuning Archiving

Subunit 8

Tuning the Shared Pool

Subunit 9

Tuning Rollback Segments

Subunit 10

Tuning the Temporary Tablespace

(80)

Role of the Database in the R/3 Architecture

Shadow Shadow process process A B ABAP:

EXEC SQL Insert …. R/3 work

process

DB interface

---SQL statement: Insert into ….

R/3 application layer Oracle database

(81)

Creating an SQL Statement

C Shadow Shadow process process B A R/3 work process DB interface ---ABAP: EXEC SQL Insert …. SQL statement: Insert into …. Shared Pool

Insert into mara (.. Update mara set ... Select * from ...

SQL net

Execution of this statement: Beginning of the Oracle transaction

(82)

Step 1: Read block from datafile

Data files

2

Database Buffer Pool

Table Data Block

1

(83)

Step 2: Read block from rollback

Data files

Rollback Data Block

2

Rollback Segments

1

1. Read data from the data file

(84)

Step 3: Write REDO for Rollback

Data files

Rollback segments

REDO Log Buffer

Change vector for RB-Block Rollback Data Block

3 1

2

2. Read a block from the rollback segment

3. Write REDO information to change the rollback block

(85)

Step 4: Copy old data block to rollback

Data files

REDO Log Buffer

4

1

3 2

2. Read a block from the rollback segment 3. Write REDO information to change the

rollback block

4. Copy the old data block value to the rollback data block

(86)

Step 5: Write REDO for data block

Data files

Rollback Segments

REDO Log Buffer

1

2

4

Change vector for Data-Block

3

5

2. Read a block from the rollback segment 3. Write REDO information to change the

rollback block

5. Write REDO information to change the data block

(87)

Step 6: Change the data block

Changed Data Block

REDO Log Buffer

4

3

5 5 6

1. Read the data from the data file 2. Read a block from the rollback

segment

3. Write REDO information to change the rollback block

5. Write REDO information to change the data block

6. Change the data block

Data files

1

2 3

(88)

The Shadow Process

Data files

Changed Data Block

REDO Log Buffer

1

2

4

3 5 5 C B A …. 6

(89)

The Shadow Process

Shadow

process

CKPTCKPT DBWR DBWR ARCH ARCH LGWR LGWR

All read operations are performed by

Oracle shadow processes!

(90)

The Shadow Process

So far, no data has been written to disks

(91)

COMMIT Change

1 2 4 3 5 5 C B A …. 6

C O M M I T

(92)

Log Writer LGWR

Online REDO log files LGWR

LGWR

REDO Log Buffer

Change vector for RB-Block

LGWR

OrigLog MirrorLog

(93)

Acknowledge Transaction

C B

A

….

Online REDO log files LGWR

LGWR

REDO log buffer

Change vector for RB-Block Change vector for Data-Block

LGWR

Data were successfully written to the REDO log