2015-k-sap-thimo bastuck-betrieb von sap mit oracle 12c erfahrungsbericht-praesentation
TRANSCRIPT
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
1/69
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
2/69
NÜRNBERG, 18.11.2015
Running SAP on Oracle12c – Experiences
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
3/69
EXPERIENCES
Running SAP on Oracle 12c
• Introduction
• Oracle 12c for SAP
• Running Oracle 12c @ FIT
• Summary
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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]
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
7/69
Oracle 12c for SAP
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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...)
Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
13/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
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
14/69Freudenberg IT | DOAG Conference Nürnberg | 2015
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;
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
15Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
16/69
Running Oracle 12c
@ FIT
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
18Freudenberg IT | DOAG Conference Nürnberg | 2015
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
19/69
Upgrade procedure – general
UPGRADING TO ORACLE 12C
• 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
19Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
20/69
Upgrade procedure – new Oracle user concept
UPGRADING TO ORACLE 12C
• 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
20Freudenberg IT | DOAG Conference Nürnberg | 2015
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
21/69
Software installation (online)
UPGRADING TO ORACLE 12C
• 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
21Freudenberg IT | DOAG Conference Nürnberg | 2015
http://service.sap.com/sap/support/notes/1915301http://service.sap.com/sap/support/notes/1915301
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
22/69
Software installation (online)
UPGRADING TO ORACLE 12C
• 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)
22Freudenberg IT | DOAG Conference Nürnberg | 2015
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1949184.1https://support.oracle.com/epmos/faces/DocumentDisplay?id=1949184.1
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
23/69
Software installation (online)
UPGRADING TO ORACLE 12C
• 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
23Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
24/69
Software installation (online)
UPGRADING TO ORACLE 12C
• new options on screen “Privileged Operating System groups” – alreadypre-definded by SAP response file:
24Freudenberg IT | DOAG Conference Nürnberg | 2015
new
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
25/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• 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”
25Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
26/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• 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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
27/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• 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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
28/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• information from pre_upgrade_checks.sql and logfile: (continued)
• 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.
28Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
29/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• information from pre_upgrade_checks.sql and logfile: (continued)
• 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
29Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
30/69
Upgrade preparations (online)
UPGRADING TO ORACLE 12C
• information from pre_upgrade_checks.sql and logfile: (continued)
• 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!)
30Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
31/69
Upgrade preparations (online) – additional checks
UPGRADING TO ORACLE 12C
• 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:
31Freudenberg IT | DOAG Conference Nürnberg | 2015
DBUA calculatesnegative space values!
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
32/69
Upgrade preparations (online) – additional checks
UPGRADING TO ORACLE 12C
• 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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
33/69
Upgrade preparations (online) – additional prep.
UPGRADING TO ORACLE 12C
• 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
33Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
34/69
Database structure upgrade (offline) – first steps
UPGRADING TO ORACLE 12C
• 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)
34Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
35/69
Database structure upgrade (offline) – first steps
UPGRADING TO ORACLE 12C
• when ignoring, this might happen…:
• Listener issue: “unexpected error”
• use_large_pages=auto:
“no longer supported”
35Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
36/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• 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
36Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
37/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• 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)
37Freudenberg IT | DOAG Conference Nürnberg | 2015
new
change!
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
38/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• new: Listener will be upgraded automatically, too, when running with the SID beingupgraded:
38Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
39/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)• ORA-32006 due to “REMOTE_OS_AUTHENT”: to be ignored
39Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
40/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• new:summary screenwith timings
• attention:to continue (finish)press “Upgrade Results” – don’t CANCEL
40Freudenberg IT | DOAG Conference Nürnberg | 2015
click here to continue & finish
new
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
41/69
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)• “Close” at the end of “Upgrade Results” screen will finish DBUA
41Freudenberg IT | DOAG Conference Nürnberg | 2015
…
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
42/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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
42Freudenberg IT | DOAG Conference Nürnberg | 2015
http://service.sap.com/sap/support/notes/1888485http://service.sap.com/sap/support/notes/1888485
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
43/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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)
43Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
44/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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
44Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
45/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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
45Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
46/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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
46Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
47/69
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• 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”!
47Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
48/69
Upgrade procedure – summary
UPGRADING TO ORACLE 12C
• 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)
48Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
49Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
50Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
...
51Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
52Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
53/69
First experiences with new featuresNEW FEATURES ORACLE DATABASE 12C
• “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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
54/69
First experiences with new featuresNEW FEATURES ORACLE DATABASE 12C
• 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.
54Freudenberg IT | DOAG Conference Nürnberg | 2015
http://service.sap.com/sap/support/notes/2138262http://service.sap.com/sap/support/notes/2138262
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
55/69
First experiences with new featuresNEW FEATURES ORACLE DATABASE 12C
• Advanced Index Compression: (continued)
• results from first compression ratio comparison in detail:
55Freudenberg IT | DOAG Conference Nürnberg | 2015
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)
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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=
56Freudenberg IT | DOAG Conference Nürnberg | 2015
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
57/69
Future use of new featuresNEW FEATURES ORACLE DATABASE 12C
• 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
57Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
58/69
Future use of new featuresNEW FEATURES ORACLE DATABASE 12C
• IM administration: (continued)
• 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/FYCUSTM2 TABLE PARTITION 46 127729664 396886016 0 COMPLETED
/BIC/FYCUSTM4 TABLE PARTITION 25 29491200 12713984 0 COMPLETED
/BIC/FYCUSTM3 TABLE PARTITION 1 131072 6291456 6127616 STARTED
/BIC/FYCUSTM6 TABLE PARTITION 27 31850496 9240576 0 COMPLETED
/BIC/DYCUSTM32 TABLE 1 4325376 5242880 0 COMPLETED
---------- ------------------ ---------- ------------------------
sum 215 328138752 619773952 105021440
58Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
59/69
Future use of new featuresNEW FEATURES ORACLE DATABASE 12C
• IM administration: (continued)
• 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/FYCUSTM3 TABLE PARTITION 110 135004160 114360320 0 COMPLETED
/BIC/FYCUSTM5 TABLE PARTITION 152 181403648 109314048 0 COMPLETED/BIC/FYCUSTM1 TABLE PARTITION 3 3538944 4653056 0 COMPLETED
/BIC/FYCUSTM2 TABLE PARTITION 53 155910144 507183104 0 COMPLETED
/BIC/FYCUSTM4 TABLE PARTITION 86 116195328 127598592 0 COMPLETED
/BIC/FYCUSTM6 TABLE PARTITION 168 199229440 65470464 0 COMPLETED
/BIC/DYCUSTM32 TABLE 1 4325376 5242880 0 COMPLETED
/BIC/DYCUSTM42 TABLE 1 7471104 10485760 0 COMPLETED
---------- ------------------ ---------- ------------------------
sum 574 803078144 944308224 0
59Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
60Freudenberg IT | DOAG Conference Nürnberg | 2015
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
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
61Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
62Freudenberg IT | DOAG Conference Nürnberg | 2015
Typical examples on next slides…
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
63/69
Using OEM 12c in SAP environment – examplesORACLE ENTERPRISE MANAGER 12C
63Freudenberg IT | DOAG Conference Nürnberg | 2015
Example 1 – Top Activity:„who“ is responsible for peak load?
Answer: BW index creation!
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
64/69
Using OEM 12c in SAP environment – examplesORACLE ENTERPRISE MANAGER 12C
64Freudenberg IT | DOAG Conference Nürnberg | 2015
Example 2 – SQL Tuning:how to reduce load from sample SQL?
Answer: creating index on table VAPMA,columns MANDT,… reduces load by 88%
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
65/69
Using OEM 12c in SAP environment – examplesORACLE ENTERPRISE MANAGER 12C
65Freudenberg IT | DOAG Conference Nürnberg | 2015
Example 3 – Monitor SQL Execution:what does running SQL do?
Answer: watch it!
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
66/69
Summary
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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.
67Freudenberg IT | DOAG Conference Nürnberg | 2015
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
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
www.freudenberg-it.com
Freudenberg IT | DOAG Conference Nürnberg | 2015
ORACLE Deutschland B.V. & Co. KG
Kühnehöfe 5
22761 Hamburg
Germany
Claudia HüfferPrincipal Sales Consultant
+49 40 89091 135
www.oracle.com/de
-
8/16/2019 2015-K-sap-thimo Bastuck-betrieb Von Sap Mit Oracle 12c Erfahrungsbericht-praesentation
69/69
Thank you for