2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation

8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation

1/69


2/69

NÜRNBERG, 18.11.2015

Running SAP on Oracle12c – Experiences


3/69

EXPERIENCES

Running SAP on Oracle 12c

• Introduction

• Oracle 12c for SAP

• Running Oracle 12c @ FIT

• Summary


4/69

Speakers

INTRODUCTION

• Dr. Thimo Bastuck• active since 2002 in the area of database and SAP operating

• installations, migrations, release upgrades of SAP systems and databases

• focused on databases (esp. Oracle), high availability

• Freudenberg IT GmbH & Co. KG,Team Application Architecture

• Location Weinheim, Germany

• Claudia Hüffer

• active since 1993 in Oracle environment• database, high availability, disaster recovery, database security

• Principal Sales Consultant

• Server Technologies Competence Center Nord

• ORACLE Deutschland B.V. & Co. KG

• Location Hamburg, Germany

Freudenberg IT | DOAG Conference Nürnberg | 2015 4


5/69

Introducing the Freudenberg Group

5

INTRODUCTION

Freudenberg Group• 507 companies in 60 countries• Family-owned since 1849

Staff Numbers• Over 40,000 employees worldwide [2014]

Turnover• > 7 € billion [2014]• 16 Business Groups operating in various market and industry sectors

Freudenberg IT• Subsidiary of the Freudenberg Group• Spin-off of the Freudenberg Group internal IT department in 1995

Headquarters• Weinheim | Germany

Freudenberg IT | DOAG Conference Nürnberg | 2015


6/69

FIT – Global Provider of IT Solutions

INTRODUCTION

Freudenberg IT | DOAG Conference Nürnberg | 2015 6

History• Spin-off of the Freudenberg Group internal IT department in 1995

• Multiple Data Centers in Germany, the United States, Canada, and China

Staff Numbers• >730 employees worldwide [2014]

• including 240 in the United States and China

Status• Revenue:134,8 Mio. € [2014]

• positive EBIT at competitive level

Portfolio• Hosting and Consulting Services in SAP environment• Managed Services IT Provider

Success• > 85% of FIT service revenues from third-party markets

• 11,2 % annual growth [CAGR 2010 - 2014]


7/69

Oracle 12c for SAP


8/69

Oracle 12c – certification in four phases

ORACLE 12C FOR SAP - ROADMAP

• Phase 1: base certification with Oracle 12c in March 2015

• No specific 12c features involved

• Phase 2: Oracle 12.1.0.2 certification June 2015• Oracle Database In-Memory certification, advanced index compression, onlinedatafile move,...

• Phase 3: planned for Q4 2015

• Information Lifecycle Management (Heat Map, ADO), Database Vault 12c,Hybrid Columnar Compression (Exadata, SuperCluster)

• Phase 4: planned for CY 2016• Oracle Multitenant

• General Information:

• 1914631 - Central Technical Note for Oracle Database 12c Release 1 (12.1)

• Oracle Database 12c for SAP –Roadmap and Base Certification Features

8Freudenberg IT | DOAG Conference Nürnberg | 2015

http://service.sap.com/sap/support/notes/1914631http://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://www.oracle.com/us/solutions/sap/oradb12c-sap-certification-roadmap-2506113.pdfhttp://service.sap.com/sap/support/notes/1914631


9/69

Oracle Database In-Memory: Dual Format Database

ORACLE 12C FOR SAP

9

• BOTH row and columnformats for same table

• Simultaneously active andtransactionally consistent

• Analytics & reporting use newin-memory column format

• OLTP uses proven row format

• Tables can be loaded

completely or partially(partition wise, column wise)

• Generic DB Operationsremains unchanged (HA, B/R,DR, Loading, DBSec, APIs...)



10/69

Oracle Database In-Memory: Orders of MagnitudesFaster Analytic Data Scans

ORACLE 12C FOR SAP

10

• Each CPU core scans local in-memory columns

• Scans use super fast SIMDvector instructions

• Originally designed for graphics& science

• Billions of rows/sec scan rateper CPU core

• Row format is millions/sec



11/69

Oracle Database In-Memory: Implementation

ORACLE 12C FOR SAP

• Set init.ora parameter INMEMORY_AREA (restart of DB required)

• Define tables for In-Memory column store:• ALTER TABLE sales INMEMORY[MEMCOMPRESS FOR DML|QUERY HIGH/LOW|CAPACITY HIGH/LOW][PRIORITY CRITICAL|HIGH|MEDIUM|LOW|NONE] NO INMEMORY(prod_id);

• ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NOINMEMORY;

• Table accessible immediately after DB restart

• Looking for good candidates?

• Use Database In-Memory Advisor DBMS_INMEMORY_ADVISOR

• Can also be used with Oracle 11g – see MOS Note 1965343.1

• Diagnostic and Tuning Pack required11Freudenberg IT | DOAG Conference Nürnberg | 2015

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1965343.1https://support.oracle.com/epmos/faces/DocumentDisplay?id=1965343.1


12/69Freudenberg IT | DOAG Conference Nürnberg | 2015

Advanced Compression Option - enhancements

ORACLE 12C FOR SAP

• OLTP Table Compression renamed to Advanced Row Compression

• Advanced Row Compression now supports more than 255 columns

• Advanced Index Compression introduced• Optimal compression for each index block, adaptive index compression

12

CREATE UNIQUE INDEXorders_idx ON orders(order_mode,order_status);

online,0,AAAPvCAAFAAAAFaAAa

online,0,AAAPvCAAFAAAAFaAAg

online,0,AAAPvCAAFAAAAFaAAl

online,2,AAAPvCAAFAAAAFaAAm

online,3,AAAPvCAAFAAAAFaAAq

CREATE UNIQUE INDEXorders_idx ON orders(order_mode,order_status)compress 1;

online



Advanced Compression Option - enhancements

ORACLE 12C FOR SAP

• OLTP Table Compression renamed to Advanced Row Compression

• Advanced Row Compression now supports more than 255 columns

• Advanced Index Compression introduced• Optimal compression for each index block, adaptive index compression

13

CREATE UNIQUE INDEXorders_idx ON orders(order_mode,order_status);

online,0,AAAPvCAAFAAAAFaAAa

online,0,AAAPvCAAFAAAAFaAAg

online,0,AAAPvCAAFAAAAFaAAl

online,2,AAAPvCAAFAAAAFaAAm

online,3,AAAPvCAAFAAAAFaAAq

CREATE UNIQUE INDEXorders_idx ON orders(order_mode,order_status)compress 1;

online



Advanced Compression Option - ILM

ORACLE 12C FOR SAP

• Information Lifecycle Management (ILM) – storage tiering, cost optimization

• Heat Map (row level, segment level)

• DB records data access statistics (write, full scan, lookup)• ALTER SYSTEM SET HEAT_MAP = ON SCOPE=BOTH;

14

• Automatic Data Optimization (ADO)

• Based on Heat Map

• Evaluated during maintenance window

• Examples: ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCEDSEGMENT AFTER 30 DAYS OF NO MODIFICATION;

ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCEDROW AFTER 30 DAYS OF NO MODIFICATION;

ALTER TABLE orders ILM ADD POLICY tier to low_cost_store;


15/69

Oracle Multitenant – a new architecture forconsolidating databases and simplifying operations

ORACLE 12C FOR SAP

Self-contained PDB for eachapplication

• Applications run unchanged

• Rapid provisioning (via clones)

• Portability (via pluggability)

Common operations performed atCDB level

• Manage many as one

(upgrade, HA, backup)• Granular control when appropriate

Shared memory and backgroundprocesses

• More applications per server



16/69

Running Oracle 12c

@ FIT


17/69

Overview

17

• Upgrading

• Prerequisites

• Software installation (online)

• Preparing the upgrade• Database structure upgrade (offline)

• Post steps (offline/online)

• Differences to operating SAP on Oracle 11g

• Runtime behavior

• New features

• Online Datafile Move

• Advanced Index Compression

• In Memory

• Multitenant

• Automatic Data Optimization

• OEM 12c

RUNNING ORACLE 12C @ FIT



18/69

Upgrade procedure – requirements

UPGRADING TO ORACLE 12C

• Oracle @ FIT used with: AIX 7.1, Linux SLES11

• Oracle/SAP requirements for upgrade: (see SAP note 1914631 + upgrade guide)

• Oracle 10.2.0.5, or 11.2.0.2 and higher (i.e. 11.2.0.1 not allowed )

• AIX 6.1 TL9 SP3 or AIX 7.1 TL3 SP3

• SLES 11 SP2 3.0.13-0.27

• Instant Client 11.2.0.4 or 12.1.0.2

• SAP kernel 7.21_EXT

• BR*Tools 7.40 PL 14

• J2EE engine: SAP JDBC Porting Layer supports 12c since end of 2012 (see note 1777021)

• ABAP correction to be applied for certain SAP_BASIS levels (see note 2157904)• ABAP SSFS (see note 1639578)

• needed because remote_os_authent=TRUE no longer supported

• already standard as of kernel 7.40


http://service.sap.com/sap/support/notes/1914631http://service.sap.com/sap/support/notes/1777021http://service.sap.com/sap/support/notes/2157904http://service.sap.com/sap/support/notes/1639578http://service.sap.com/sap/support/notes/1639578http://service.sap.com/sap/support/notes/2157904http://service.sap.com/sap/support/notes/1777021http://service.sap.com/sap/support/notes/1914631


19/69

Upgrade procedure – general


• general procedure very similar to upgrading to 11g, i.e.:

• upgrade phases as usual:

• software installation (online)

• upgrade preparations (online)

• database structure upgrade (offline)

• post steps (offline/online)

• still many automated checks and corrections

• but consider additional downtime tasks if needed for

• upgrading SAP kernel (incl. online SGEN after upgrade)

• activating ABAP SSFS

• and consider remarkable additional efforts and downtime when deciding to switchto new Oracle user concept



20/69

Upgrade procedure – new Oracle user concept


• when first releasing Oracle 12c for SAP applications, SAP only supported the“new” user concept using oracle:oinstall as known from ASM/RAC

• i.e. during upgrade, software owner had to be migrated from ora:dba tooracle:oinstall

• leading to considerably higher effort and longer downtime• significant changes in highly automated environments (using different user ora oracle, environment not set by default but to be set using tools like oraenv etc.)

• user ora could be kept for compatibility reasons

• @ FIT, this new concept delayed the Oracle 12c rollout

• finally, in August 2015 SAP decided to further support the “classic” user concept

for non-ASM/RAC installations using ora:dba • currently, FIT still using “classic” user concept

• new SAP installations with “classic” user concept on 12c possible as of SWPMSP09 (but introducing tablespace compression restrictions?!)

• SAP note 1915323 - OS User Concept for Oracle Database 12c Release 1


http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323http://service.sap.com/sap/support/notes/1915323


21/69

Software installation (online)


• install using SAP RUNINSTALLER as usual (updated to latest version from note1915301)

• Prerequisite Checker based on

older OS patchlevel asknown from 11g,leading to obsolete “errors”

• example on AIX 7.1:“missing” patches which are

already included in TL3 SP3

• /tmp check does not takeinto account given newtemp. directory

• in this example,-tmp /usr/sap/install_tmphad been passed


http://service.sap.com/sap/support/notes/1915301http://service.sap.com/sap/support/notes/1915301


22/69



• Prerequisite Checker does not check important AIX IOCP setting:

• for Oracle 12c, IOCP must be available on the server, otherwise executing Oraclebinaries will crash:

orasid> sqlplus

exec(): 0509-036 Cannot load program sqlplus because of the following errors:

rtld: 0712-001 Symbol CreateIoCompletionPort was referenced

from module /oracle/SID/121/lib/libttsh12.so(), but a runtime definition

of the symbol was not found.

rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced

from module /oracle/SID/121/lib/libttsh12.so(), but a runtime definition

of the symbol was not found.

rtld: 0712-002 fatal error: exiting.

• so be sure to check the setting in advance:root> lsdev | grep iocp

iocp0 Available I/O Completion Ports

• if necessary, make it available using smitty (see MOS Doc ID 1949184.1)


https://support.oracle.com/epmos/faces/DocumentDisplay?id=1949184.1https://support.oracle.com/epmos/faces/DocumentDisplay?id=1949184.1


23/69



• software installation using Oracle Universal Installer (OUI) as usual

• SAP response file, being loaded when running „RUNINSTALLER “

• new: call OUI with specifying the new ORACLE_BASE directory /oracle/:

orasid>./RUNINSTALLER -tmp /usr/sap/install_tmp -oracle_base /oracle/SID

• as known from Oracle 11g:

• patchset installation cumulative, so only install latest patchset

• don’t forget to install the latest „SAP Bundle Patch “ (SBP) with SAP tool „ MOPatch“afterwards



24/69



• new options on screen “Privileged Operating System groups” – alreadypre-definded by SAP response file:


new


25/69

Upgrade preparations (online)


• procedure quite similar to upgrading to 11g:

• 1) running tool “pre_upgrade_tasks.sql” for executing preparative tasks: • gathering Oracle dictionary statistics

• recompiling invalid objects

• purging database recyclebin• 2) running tool “pre_upgrade_checks.sql” which will execute

• Oracle Pre-Upgrade Information Tool, generating logfile and fixup SQLs (new), see below

• Oracle Database Pre-Upgrade Information Utility for SAP

1. Review results of the pre-upgrade checks:

/oracle/cfgtoollogs/SID/preupgrade/ preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/oracle/cfgtoollogs/SID/preupgrade/ preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/oracle/cfgtoollogs/SID/preupgrade/ postupgrade_fixups.sql

• 3) new: run generated “preupgrade_fixups.sql”



26/69



• information from pre_upgrade_checks.sql and logfile:

• source database status (platform, version, configuration, timezone file, security etc.)

• some examples:

--> Database Character Set : UTF8

--> National Character Set : UTF8--> Database Log Mode : ARCHIVELOG--> Database Flashback Mode : NO--> AWR Retention : 42

--> COMPATIBLE : 11.2.0--> REMOTE_OS_AUTHENT : TRUE

--> Oracle RAC System : FALSE--> Oracle DV System : FALSE--> SAP BW/DW/BI System : TRUE--> SAP Unicode System : TRUE--> SAP ABAP Stack/Java Stack : TRUE/FALSE

--> Database Security Configuration--> User Profile SAPUPROF : TRUE--> DEFAULT.FAILED_LOGIN_ATTEMPTS: 10

--> DEFAULT.PASSWORD_LIFE_TIME : 18026Freudenberg IT | DOAG Conference Nürnberg | 2015


27/69



• information from pre_upgrade_checks.sql and logfile: (continued)

• existing restore points, “_” and event parameters, list of invalid objects, and other warnings

• some examples:

**********************************************************************

Generic Pre-Upgrade Warnings**********************************************************************

WARNING: ----> INVALID_SAP_OBJECTSThere are invalid objects in an SAP schema.Check why these objects are invalid before upgradingthe database..

.**********************************************************************SAP-Specific Pre-Upgrade Warnings**********************************************************************

WARNING: ----> NO_SPFILE No Server Parameter File (SPFILE) created.Recommendation: Configure a Server Parameter File.See SAP Note 601157..

WARNING: ----> PLAN_TABLE_EXISTS_IN_SAP_SCHEMATable(s) PLAN_TABLE exist(s) in SAP schema.The table(s) will get dropped after the upgrade andrecreated with new structure.SAPSR3.PLAN_TABLE

.27Freudenberg IT | DOAG Conference Nürnberg | 2015


28/69




• new: Object ID Checker:

************************************************************

*** Oracle Database Object ID Checker for SAP ***************************************************************DATABASE NAME : SIDDATABASE CREATION TIME : 2011-JUL-08 13:46:58DATABASE AGE : 4 years 107 days

DATAOBJ# (current max.) : 310918493OBJECT ID (current max.) : 310918487OBJECT ID (system limit) : 4254950911

OBJECT ID (check limit ) : 3221225472

Checking whether object id is above critical value of 3221225472

*************************************************************** OK ***************************************************************Object ID numbers are not critical.



29/69




• e.g. obsolete/deprecated parameters, component list: (preupgrade.log)

**********************************************************************[Obsolete/Deprecated Parameters]--> remote_os_authent 11.1 DESUPPORTED

[Changes required in Oracle Database init.ora or spfile]

**********************************************************************[Component List]

**********************************************************************

--> Oracle Catalog Views [upgrade] VALID--> Oracle Packages and Types [upgrade] VALID**********************************************************************

• desupported remote_os_authent is reason for activating ABAP SSFS



30/69




• free space check: (preupgrade.log)

**********************************************************************[Tablespaces]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade.

minimum required size: 4708 MB--> SYSAUX tablespace is adequate for the upgrade.

minimum required size: 23137 MB--> PSAPUNDO tablespace is adequate for the upgrade.

minimum required size: 400 MB

--> PSAPTEMP tablespace is adequate for the upgrade. minimum required size: 60 MB

[No adjustments recommended]

• be careful: always double check the SYSTEM free space: enable “autoextend” on datafilesto have plenty of free space (don’t rely on this check here!)



31/69

Upgrade preparations (online) – additional checks


• in order to avoid DBUA bug, check if datafiles with absurd autoextend settingsexist, i.e. AUTOEXTENSIBLE=YES, but current size (BYTES) exceeds definedMAXBYTES and turn them off:


DBUA calculatesnegative space values!


32/69

Upgrade preparations (online) – additional checks


• in order to avoid DBUA bug, check if datafiles with absurd autoextend settingsexist, i.e. AUTOEXTENSIBLE=YES, but current size (BYTES) exceeds definedMAXBYTES and turn them off:

SQL>

select 'alter database datafile ''' || FILE_NAME || ''' autoextend off;'from dba_data_files where AUTOEXTENSIBLE='YES' and MAXBYTES


33/69

Upgrade preparations (online) – additional prep.


• new: DBUA will run with archiving on, no more option to turn off archiving

• so check free space of “oraarch” filesystem (typically a few GB needed, but may be “muchmore” for large BW systems)

• recommendation: make preparations for using “flashback database” (withguaranteed restore point before starting structure upgrade):• create filesystem /oracle//oraflash (space similar to oraarch needed)

• and activate configuration parameters:

db_recovery_file_dest='/oracle//oraflash'

db_recovery_file_dest_size=

• backup Listener/SQL*Net configuration

• and ensure that DB software owner has write permission to it

• if using non-standard path $TNS_ADMIN, copy current cfg into source$ORACLE_HOME/network/admin



34/69

Database structure upgrade (offline) – first steps


• first downtime steps before “dbua” call:

• run additional full backup if desired

• (with “archiving on”, all standard backups + redologs can also be used in case of failure)

• update SAP kernel (including BR*Tools) + Oracle instant client if needed• recommendation: create guaranteed restore point for flashback database

SQL> CREATE RESTORE POINT before_oracle_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

• be careful:

• if running one Listener for multiple databases on your host, be sure not to run it on yourdatabase being upgraded – this would cause DBUA to crash during input phase (should befixed soon)

• Linux: be sure not to have set use_large_pages=auto – this would cause DBUA toabort in upgrade phase (this value is no longer supported)



35/69

Database structure upgrade (offline) – first steps


• when ignoring, this might happen…:

• Listener issue: “unexpected error”

• use_large_pages=auto:

“no longer supported”



36/69

Database structure upgrade (offline) – DBUA


• the main upgrade is being started using the SAP wrapper script “dbua.sap.sh“

as already known from 11g

• also including “customer scripts” to automatically set parameters (e.g. “_” and

events) after upgrade etc.

• most DBUA input screens are pre-filled by wrapper script• sample screenshots:

• choose the rightOracle Home



37/69



• sample screenshots: (continued)

• new: upgrade parallelism

• attention: correct “Diagnostic Destination” to SAP standard /oracle//saptrace to avoid

errors (SAP upgrade guide incorrect at this point – will be corrected)


new

change!


38/69




• new: Listener will be upgraded automatically, too, when running with the SID beingupgraded:



39/69



• sample screenshots: (continued)• ORA-32006 due to “REMOTE_OS_AUTHENT”: to be ignored



40/69




• new:summary screenwith timings

• attention:to continue (finish)press “Upgrade Results” – don’t CANCEL


click here to continue & finish

new


41/69



• sample screenshots: (continued)• “Close” at the end of “Upgrade Results” screen will finish DBUA


…


42/69

Post upgrade steps (offline/online)


• dbua.sap.sh stops instance after upgrade

• check migrated SQL*Net config, esp. when using non-standard $TNS_ADMIN

• in case of Java stack or custom jdbc applications, add into sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8in order to avoid

ORA-28040: No matching authentication protocol

• continue with standard post steps according to upgrade guide:

• user environments, oratab, (re)move old Oracle home, startup listener

• run post upgrade scripts:

• @?/rdbms/admin/utlu121s.sql – generate upgrade status information• @post_upgrade_checks.sql – SAP post upgrade checks (next slides)

• catsbp (next slides)

• final post steps:

• check DB parameter (note 1888485), update statistics, initial backup,and run SGEN after starting SAP in case of SAP kernel upgrade




43/69



• run post upgrade scripts: (continued)• @post_upgrade_checks.sql – SAP post upgrade checks; output samples:

--> Database Log Mode : ARCHIVELOG--> Database Flashback Mode : RESTORE POINT ONLY--> AWR Retention : 42

--> Database Parameter--> COMPATIBLE : 11.2.0--> REMOTE_OS_AUTHENT : FALSE--> Fast Recovery Area (FRA) Configuration

--> FRA Destination : /oracle/SID/oraflash--> FRA Size : 30000 M--> FRA Space Limit : 30000 M--> FRA Space Used : 9000 M / 30 %

• compatible cannot be changed to 12.1.0.2.0 with flashback database active( manual post step)



44/69



• run post upgrade scripts: (continued)• @post_upgrade_checks.sql – SAP post upgrade checks; output samples: (cont’d)

**********************************************************************Database Components**********************************************************************

The following database components are installed:

Component ID Version StatusXDB 12.1.0.2.0 VALIDCATALOG 12.1.0.2.0 VALID

CATPROC 12.1.0.2.0 VALID.

• new: XDB mandatory component as of 12c, installed as part of the upgrade



45/69



• important 1: if guaranteed restore point had been created, drop it now and updateparameter compatible (+ restart database):

SQL> DROP RESTORE POINT before_oracle_upgrade;

Restore point dropped.

SQL> alter system set compatible='12.1.0.2.0' scope=spfile;

System altered.

• important 2: run catsbp as part of SBP post step

(not mentioned explicitly in upgrade guide)• new: catsbp is now shell script, only short output



46/69



• catsbp sample output (new):

orasid> $ORACLE_HOME/sapbundle/SBP_121024_201509/catsbpcatsbp - Post-process Installation of SBP 12.1.0.2.4 201509V2.Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.

Log file: $ORACLE_BASE/cfgtoollogs/sqlpatch/SAP201509_APPLY_SID_2015_10_24-19-23-15.log

Connecting to database...Connecting to database...done.

Executing Datapatch...Executing Datapatch...done.

Loading style sheets...Loading style sheets...skipped.

Reason: Style sheets already loaded

Recompiling objects with utlrp...Recompiling objects with utlrp...done.

Log file: $ORACLE_BASE/cfgtoollogs/sqlpatch/SAP201509_APPLY_SID_2015_10_24-19-23-15.log

catsbp completed successfully.

Overall Status: COMPLETE



47/69



• catsbp sample output (new): (continued)

• “known” errors: (no SAP note yet)

Executing Datapatch...Executing Datapatch...failed.

Reason: Unknown error (exit value 255)

solution: re-execute catsbp

Recompiling objects with utlrp...Recompiling objects with utlrp...failed .Reason: Cannot recompile some objects...Overall Status: INCOMPLETE

solution: Overall Status should not be “INCOMPLETE” due to invalid objects – should befixed with next versions; so far, invalid objects really have to be “resolved”!



48/69

Upgrade procedure – summary


• easy workarounds for minor errors if occurring (might vanish with next updates)

• further improvements compared to 11g, e.g.

• parallelization of main upgrade scripts shorter downtime

• further automating manual steps (fixup scripts etc.)• improved user interface (see timings etc.)

• as reliable as known from earlier releases

• typical “pure” DB upgrade downtime < 1 hour

• typical overall SAP downtime 1-2 hours (depending on related activities)



49/69

Administrative differences (without new features)

DIFFERENCES TO OPERATING SAP ON ORACLE 11G

• no differences from “inside SAP” (esp. DBA-Cockpit) or using BR*Tools(except new features)

• Oracle user concept: change possible at your choice (see separate slide)

• changed ORACLE_BASE:• now /oracle/ instead of /oracle

• clear separation when running multiple databases on one host

• e.g. directory “cfgtoollogs” containing upgrade logs etc. now separated

• changed ORACLE_HOME:

• name does not contain “bit” value any longer

• so no “121_64”, but simply “121” • own scripts and automation might need to be adjusted (manageable efforts @ FIT)

• as always: some “new” processes on OS level …

• no more “ORA-32004” (or similar) at DB instance startup because“REMOTE_OS_AUTHENT = TRUE” can’t be used any longer



50/69

Runtime behavior (without new features)

RUNTIME EXPERIENCE SAP ON ORACLE 12C

• Examples:

• experience from large BW systems (DB size 2 TB – 10 TB, > 500 concurrentusers), after upgrading without explicitly activating new features:

• some process completion times dropped significantly: some formerly unexplained “strange”behavior seems to be solved, reducing runtimes from > 1 hour down to “minutes”

• other customer states “no change in runtimes”, i.e. in spite of many built-in new featuressame good performance as before (no degradation, although you could assume a higherneed of system resources)

• experience from FIT owned ERP system:

• DB response times dropped by 10% better performance after upgrading

• Resume:

• all positive, no performance degradation, at least same performance or evenimprovements without doing special tunings or using new features



51/69

First experiences with new features

NEW FEATURES ORACLE DATABASE 12C

• “small” but powerful new feature: “Online Datafile Move”

• datafiles can be “renamed” or moved to another location without downtime or any otherservice disruption

• fully integrated in brspace already, see help menu:

Options for 'dfmove' function:

[-c|-force]force database shutdown for movedefault: don't shut down if SAP user connected

[-d|-destination ] move destinationno default

[-f|-file all_df|||-||sapdata|sapraw|:|+|]

file nameall_df - all eligible data files

no default[-o|-online no|yes] move data file online (as of Oracle 12c)default: no

[-p|-parallel ]number of parallel copy processesdefault: 1

[-t|-tablespace |]tablespace nameno default

...



52/69

First experiences with new featuresNEW FEATURES ORACLE DATABASE 12C

• “Online Datafile Move” example:

• move datafile “datusr.data1” from sapdata4 to sapdata2 online (SAP system running):

orazz3> ls -l /oracle/ZZ3/sapdata*/datusr_1/datusr.data1-rw-r----- 1 orazz3 dba 20979712 Nov 4 16:05 /oracle/ZZ3/sapdata4/datusr_1/datusr.data1

orazz3> brspace -f dfmove -f /oracle/ZZ3/sapdata4/datusr_1/datusr.data1 -d sapdata2 -o yes

...BR0280I BRSPACE time stamp: 2015-11-04 22:31:48BR0657I Input menu 319 - please enter/check input values-------------------------------------------------------------------------------Options for moving data file /oracle/ZZ3/sapdata4/datusr_1/datusr.data1

1 * Current file size in MB (currsize) . [20]2 * Current link target (currlink) ..... []3 - Move destination (destination) ..... [/oracle/ZZ3/sapdata2]

4 ~ Raw disk / link target (rawlink) ... []5 - Move data file online (online) ..... [yes]6 # Parallel copy processes (parallel) . [1]7 # Force instance shutdown (force) .... [no]8 - SQL command (command) .............. [alter database move datafile

'/oracle/ZZ3/sapdata4/datusr_1/datusr.data1' to '/oracle/ZZ3/sapdata2/datusr_1/datusr.data1']

Standard keys: c - cont, b - back, s - stop, r - refr-------------------------------------------------------------------------------BR0662I Enter your choice:c



53/69


• “Online Datafile Move” example: (continued)...BR0370I Directory /oracle/ZZ3/sapdata2/datusr_1 created

BR0280I BRSPACE time stamp: 2015-11-04 22:32:32BR1016I SQL statement 'alter database move datafile '/oracle/ZZ3/sapdata4/datusr_1/datusr.data1'to '/oracle/ZZ3/sapdata2/datusr_1/datusr.data1'' executed successfully

BR1082I Data file /oracle/ZZ3/sapdata4/datusr_1/datusr.data1 moved successfully to/oracle/ZZ3/sapdata2/datusr_1/datusr.data1

BR0480I Directory /oracle/ZZ3/sapdata4/datusr_1 deleted...

• result:orazz3> ls -l /oracle/ZZ3/sapdata*/datusr_1/datusr.data1-rw-r----- 1 orazz3 dba 20979712 Nov 4 22:37 /oracle/ZZ3/sapdata2/datusr_1/datusr.data1

• alert.log: Wed Nov 04 22:32:27 2015/* BRSPACE */ alter database move datafile '/oracle/ZZ3/sapdata4/datusr_1/datusr.data1' to'/oracle/ZZ3/sapdata2/datusr_1/datusr.data1'

Wed Nov 04 22:32:29 2015 Moving datafile /oracle/ZZ3/sapdata4/datusr_1/datusr.data1 (6) to/oracle/ZZ3/sapdata2/datusr_1/datusr.data1

Move operation committed for file /oracle/ZZ3/sapdata2/datusr_1/datusr.data1Completed: /* BRSPACE */ alter database move datafile

'/oracle/ZZ3/sapdata4/datusr_1/datusr.data1' to '/oracle/ZZ3/sapdata2/datusr_1/datusr.data1'53Freudenberg IT | DOAG Conference Nürnberg | 2015


54/69


• Advanced Index Compression:

• reduces complexity for index compression: no need to define suitable set of columns(“prefix”) any more, as best method of index compression will be chosen automatically onblock level

• expecting higher compression ratio for indexes with formerly view “prefix columns”

• if index should always be compressed: set parameter

_advanced_index_compression_options=16

for compressing all new created indexes (SAP note 2138262)

this is even possible when loading a new system (SWPM/R3load)

makes compressing “new” (i.e. also reorganized) systems much easier!

• Status @ FIT:

• first comparison between compression ratio of standard and advanced index compressionshows slightly better overall compression with “advanced”, some indexes up to 15% better

• loading “new systems” already tested: works great. Runtime behavior also fine.




55/69


• Advanced Index Compression: (continued)

• results from first compression ratio comparison in detail:


Sizes compressed [MB] Col.* Sizes [MB] Compression ratio [%]

Index name Standard Advanced Delta [%] (Std.) unocmpressed Standard Advanced

/BIC/4EYSTOCKHI2Z0 6.543 5.658 -13,5 8 9.111 71,8 62,1

/BIC/AEI2PDEBT00~0 2.842 2.805 -1,3 4 5.628 50,5 49,8

/BIC/AEK4LYR1000~0 2.761 2.313 -16,2 7 4.759 58,0 48,6/BIC/AYBLISDEF00Y2 2.536 2.500 -1,4 2 4.809 52,7 52,0

/BIC/AYBLISDEF00YI 2.620 2.602 -0,7 3 5.567 47,1 46,7

/BIC/AYBLISDEF00YK 2.232 2.286 2,4 1 3.476 64,2 65,8

/BIC/AYBLISDEF00~0 3.236 3.270 1,1 1 4.461 72,5 73,3

/BIC/AYFIGLCT100~0 10.506 9.969 -5,1 5 23.325 45,0 42,7

/BIC/AYPCAC0100~0 3.267 2.767 -15,3 15 5.583 58,5 49,6

/BIC/AYTMCUORD00~0 2.720 2.749 1,1 1 3.711 73,3 74,1

/BIC/AYTMDEBT80001 921 917 -0,4 4 2.636 34,9 34,8

/BIC/AYTMDEBT800~0 2.652 2.670 0,7 1 3.271 81,1 81,6

/BIC/AYTMDEBTA00~0 1.815 1.824 0,5 1 2.255 80,5 80,9

/BIC/AYTM_CON100~0 3.355 2.974 -11,4 3 4.622 72,6 64,3

/BIC/AYTM_DEBT0001 2.607 2.636 1,1 4 7.452 35,0 35,4

/BIC/AYTM_DEBT00Y1 3.518 3.490 -0,8 5 9.387 37,5 37,2

/BIC/AYTM_DEBT00Y2 3.737 3.706 -0,8 3 8.214 45,5 45,1

/BIC/AYTM_DEBT00Y4 3.726 3.701 -0,7 3 8.222 45,3 45,0

/BIC/AYTM_DEBT00~0 7.458 7.500 0,6 1 9.280 80,4 80,8

/BIC/AZMARCFAN00KE 5.156 5.114 -0,8 2 6.536 78,9 78,2

sum 74.208 71.451 -3,7 132.305 56,1 54,0

* no. of columns to compress (standard index compression)


56/69

Future use of new featuresNEW FEATURES ORACLE DATABASE 12C

• Database In-Memory: (DBIM)

• big advantage compared to “other” IM solutions: certified for almost ALL SAP products,

not restricted to BW or minimum SAP patch levels (except Oracle 12c compatibility)

• very promising for reducing DB response times (typical for IM solutions)

• good Oracle whitepaper “Using SAP NetWeaver with Oracle Database In-Memory”available on SCN DOC-65092

• FIT status: currently working together with a customer who has licensed DBIM

• current state: determining table candidates for IM using IM advisor and packageSAP_IM_ADV for “known” tables, first testings

• no runtime comparisons yet – to be shown on next conferences…

• IM administration is really easy:

• Activation: (see whitepaper)inmemory_max_populate_servers=4

inmemory_clause_default='PRIORITY HIGH'

inmemory_size=


http://scn.sap.com/docs/DOC-65092http://scn.sap.com/docs/DOC-65092http://scn.sap.com/docs/DOC-65092http://scn.sap.com/docs/DOC-65092


57/69


• IM administration: (continued)

• Determine tables to be loaded into memory

• Load tables into IM store:

SQL> alter table "SAPSR3"."" inmemory memcompress for query low priority high;

Table altered.

• Monitoring status:

v$inmemory_area

v$im_segments



58/69



• IM status during loading tables:

SQL> select * from v$inmemory_area;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID

-------------------------- ----------- ---------- -------------------------- ----------

1MB POOL 2096103424 256901120 POPULATING 0

64KB POOL 520093696 22872064 POPULATING 0

SQL> select SEGMENT_NAME, SEGMENT_TYPE, count(*), sum(INMEMORY_SIZE), sum(BYTES),sum(BYTES_NOT_POPULATED), POPULATE_STATUS from v$im_segments group by SEGMENT_NAME, SEGMENT_TYPE,POPULATE_STATUS;

SEGMENT_NAME SEGMENT_TYPE COUNT(*) SUM(INMEMORY_SIZE) SUM(BYTES) SUM(BYTES_NOT_POPULATED) POPULATE_

-------------------- --------------- ---------- ------------------ ---------- ------------------------ ---------

/BIC/FYCUSTM3 TABLE PARTITION 83 100007936 72548352 0 COMPLETED

/BIC/FYCUSTM5 TABLE PARTITION 26 30670848 11534336 0 COMPLETED/BIC/FYCUSTM1 TABLE PARTITION 3 3538944 4653056 0 COMPLETED

/BIC/FYCUSTM2 TABLE PARTITION 3 393216 100663296 98893824 STARTED



/BIC/FYCUSTM3 TABLE PARTITION 1 131072 6291456 6127616 STARTED


/BIC/DYCUSTM32 TABLE 1 4325376 5242880 0 COMPLETED

---------- ------------------ ---------- ------------------------

sum 215 328138752 619773952 105021440



59/69



• IM status after loading tables:

SQL> select * from v$inmemory_area;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID

-------------------------- ----------- ---------- -------------------------- ----------

1MB POOL 2096103424 727711744 DONE 0

64KB POOL 520093696 75366400 DONE 0

SQL> select SEGMENT_NAME, SEGMENT_TYPE, count(*), sum(INMEMORY_SIZE), sum(BYTES),sum(BYTES_NOT_POPULATED), POPULATE_STATUS from v$im_segments group by SEGMENT_NAME, SEGMENT_TYPE,POPULATE_STATUS;

SEGMENT_NAME SEGMENT_TYPE COUNT(*) SUM(INMEMORY_SIZE) SUM(BYTES) SUM(BYTES_NOT_POPULATED) POPULATE_

-------------------- --------------- ---------- ------------------ ---------- ------------------------ ---------


/BIC/FYCUSTM5 TABLE PARTITION 152 181403648 109314048 0 COMPLETED/BIC/FYCUSTM1 TABLE PARTITION 3 3538944 4653056 0 COMPLETED






---------- ------------------ ---------- ------------------------

sum 574 803078144 944308224 0



60/69

More new features… NEW FEATURES ORACLE DATABASE 12C

• Automatic Data Optimization: (ADO)

• not yet released by SAP, but already integrated into the current SWPM SP09:

• may not yet be used according to SAP note 1914631:

“In SWPM 1.0 SP09 do not use option 'Tablespace Parameters -> Policy

Tablespaces' on the 'Advanced Oracle Configuration' dialog until ILM/ADO

functionality has been released for SAP. See SAP notes 2172935 and 2173485.”

• FIT view: very promising feature, so waiting to officially be released by SAP


http://service.sap.com/sap/support/notes/1914631http://service.sap.com/sap/support/notes/2172935http://service.sap.com/sap/support/notes/2173485http://service.sap.com/sap/support/notes/2173485http://service.sap.com/sap/support/notes/2172935http://service.sap.com/sap/support/notes/1914631


61/69

More new features… NEW FEATURES ORACLE DATABASE 12C

• Oracle Multitenant:

• not yet released by SAP

• @ FIT: for FIT customer, this feature seams to be less relevant in SAP environment so far• more relevant outside SAP: currently, some customers are running multiple databases for

small Non-SAP application could be consolidated using “Multitenant”, but license costsmust be considered



62/69

Using OEM 12c in SAP environmentORACLE ENTERPRISE MANAGER 12C

• @ FIT: OEM 12c being used especially for analyzing performance issues:

• comparing periods of good / bad performance

• analyzing peak loads

• identifying problematic SQLs• SQL tuning

• watch running SQLs in realtime

• for those use cases, Diagnostic and Tuning Pack must be licensed(otherwise OEM won’t show any data in the interesting areas)

• according to SAP recommendation, AWR retention time always set to

42 days

• most used starting points:

• SQL Monitor – Last Hour (from DB Home)

• Performance | Top Activity

• Performance | ASH Analytics


Typical examples on next slides…


63/69

Using OEM 12c in SAP environment – examplesORACLE ENTERPRISE MANAGER 12C


Example 1 – Top Activity:„who“ is responsible for peak load?

Answer: BW index creation!


64/69



Example 2 – SQL Tuning:how to reduce load from sample SQL?

Answer: creating index on table VAPMA,columns MANDT,… reduces load by 88%


65/69



Example 3 – Monitor SQL Execution:what does running SQL do?

Answer: watch it!


66/69

Summary


67/69

SummaryRUNNING SAP ON ORACLE 12C

• Oracle 12c being used with SAP since summer 2015 @ FIT

• first x.1 Release certified with SAP, in the past only x.2 Release certified

• nevertheless, upgrades ran basically good and fast,

process as stable and reliable as known from 11g• further significant operating improvements

• furtherly reduced complexity

• enhanced flexibility

• reduced need for downtimes

• improved performance

• great tool for problem analysis: OEM 12c

Thus, as its predecessors, Oracle 12c continues to be a very solid platform for theoperation of SAP systems, also in the hosting environment of Freudenberg IT.



68/69

How to reach usCONTACT

Your contact

@

Your contact

@

68

Freudenberg IT

Höhnerweg 2-4

69465 Weinheim

Germany

Dr. Thimo BastuckSolution Architect

+49 6201 80 8000

[email protected]

www.freudenberg-it.com


ORACLE Deutschland B.V. & Co. KG

Kühnehöfe 5

22761 Hamburg

Germany

Claudia HüfferPrincipal Sales Consultant

+49 40 89091 135

[email protected]

www.oracle.com/de


69/69

Thank you for

2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation

Documents

Transcript of 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation