running sap on oracle 12c – experiences · pdf file• 1914631 - central technical...
TRANSCRIPT
NÜRNBERG, 18.11.2015
Running SAP on Oracle
12c – Experiences
EXPERIENCES
Running SAP on Oracle 12c
• Introduction
• Oracle 12c for SAP
• Running Oracle 12c @ FIT
• Summary
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
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
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]
Oracle 12c for SAP
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, online
datafile 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
8 Freudenberg IT | DOAG Conference Nürnberg | 2015
Oracle Database In-Memory: Dual Format Database
ORACLE 12C FOR SAP
9
• BOTH row and column
formats for same table
• Simultaneously active and
transactionally consistent
• Analytics & reporting use new
in-memory column format
• OLTP uses proven row format
• Tables can be loaded
completely or partially
(partition wise, column wise)
• Generic DB Operations
remains unchanged (HA, B/R,
DR, Loading, DBSec, APIs...)
Freudenberg IT | DOAG Conference Nürnberg | 2015
Oracle Database In-Memory: Orders of Magnitudes
Faster Analytic Data Scans
ORACLE 12C FOR SAP
10
• Each CPU core scans local in-
memory columns
• Scans use super fast SIMD
vector instructions
• Originally designed for graphics
& science
• Billions of rows/sec scan rate
per CPU core
• Row format is millions/sec
Freudenberg IT | DOAG Conference Nürnberg | 2015
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 NO
INMEMORY;
• 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 required
11 Freudenberg IT | DOAG Conference Nürnberg | 2015
Freudenberg 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 INDEX
orders_idx ON orders
(order_mode,order_status);
online,0,AAAPvCAAFAAAAFaAAa
online,0,AAAPvCAAFAAAAFaAAg
online,0,AAAPvCAAFAAAAFaAAl
online,2,AAAPvCAAFAAAAFaAAm
online,3,AAAPvCAAFAAAAFaAAq
CREATE UNIQUE INDEX
orders_idx ON orders
(order_mode,order_status)
compress 1;
online <-
Prefix
0,AAAPvCAAFAAAAFaAAa <-
Suffix
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm
3,AAAPvCAAFAAAAFaAAq
CREATE UNIQUE INDEX
orders_idx ON orders
(order_mode,order_status)
compress;
online,0 <-
Prefix
AAAPvCAAFAAAAFaAAa <-
Suffix
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
online,2
AAAPvCAAFAAAAFaAAm
online,3
AAAPvCAAFAAAAFaAAq
Traditional Index Key compression / prefix compression:
Freudenberg 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 INDEX
orders_idx ON orders
(order_mode,order_status);
online,0,AAAPvCAAFAAAAFaAAa
online,0,AAAPvCAAFAAAAFaAAg
online,0,AAAPvCAAFAAAAFaAAl
online,2,AAAPvCAAFAAAAFaAAm
online,3,AAAPvCAAFAAAAFaAAq
CREATE UNIQUE INDEX
orders_idx ON orders
(order_mode,order_status)
compress 1;
online <-
Prefix
0,AAAPvCAAFAAAAFaAAa <-
Suffix
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm
3,AAAPvCAAFAAAAFaAAq
CREATE UNIQUE INDEX
orders_idx ON orders
(order_mode,order_status)
compress;
online,0 <-
Prefix
AAAPvCAAFAAAAFaAAa <-
Suffix
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
online,2
AAAPvCAAFAAAAFaAAm
online,3
AAAPvCAAFAAAAFaAAq
Traditional Index Key compression / prefix compression: Using advanced index compression:
CREATE UNIQUE INDEX orders_idx
ON orders ( order_mode, order_status )
compress advanced low;
Automatically compresses index leaf blocks when becoming full
Freudenberg 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 ADVANCED
SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCED
ROW AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE orders ILM ADD POLICY tier to low_cost_store;
Oracle Multitenant – a new architecture for
consolidating databases and simplifying operations
ORACLE 12C FOR SAP
Self-contained PDB for each
application
• Applications run unchanged
• Rapid provisioning (via clones)
• Portability (via pluggability)
Common operations performed at
CDB level
• Manage many as one
(upgrade, HA, backup)
• Granular control when appropriate
Shared memory and background
processes
• More applications per server
15 Freudenberg IT | DOAG Conference Nürnberg | 2015
Running Oracle 12c
@ FIT
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
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
18 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 switch
to new Oracle user concept
19 Freudenberg IT | DOAG Conference Nürnberg | 2015
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<sid>:dba to
oracle:oinstall
• leading to considerably higher effort and longer downtime
• significant changes in highly automated environments (using different user ora<sid>
oracle, environment not set by default but to be set using tools like oraenv etc.)
• user ora<sid> 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<sid>:dba
• currently, FIT still using “classic” user concept
• new SAP installations with “classic” user concept on 12c possible as of SWPM
SP09 (but introducing tablespace compression restrictions?!)
• SAP note 1915323 - OS User Concept for Oracle Database 12c Release 1
20 Freudenberg IT | DOAG Conference Nürnberg | 2015
Software installation (online)
UPGRADING TO ORACLE 12C
• install using SAP RUNINSTALLER as usual (updated to latest version from note
1915301)
• Prerequisite Checker based on
older OS patchlevel as
known from 11g,
leading to obsolete “errors”
• example on AIX 7.1:
“missing” patches which are
already included in TL3 SP3
• /tmp check does not take
into account given new
temp. directory
• in this example, -tmp /usr/sap/install_tmp
had been passed
21 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 Oracle
binaries 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)
22 Freudenberg IT | DOAG Conference Nürnberg | 2015
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/<SID>:
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
23 Freudenberg IT | DOAG Conference Nürnberg | 2015
Software installation (online)
UPGRADING TO ORACLE 12C
• new options on screen “Privileged Operating System groups” – already
pre-definded by SAP response file:
24 Freudenberg IT | DOAG Conference Nürnberg | 2015
new
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”
25 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 : 180
26 Freudenberg IT | DOAG Conference Nürnberg | 2015
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_OBJECTS
There are invalid objects in an SAP schema.
Check why these objects are invalid before upgrading
the 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_SCHEMA
Table(s) PLAN_TABLE exist(s) in SAP schema.
The table(s) will get dropped after the upgrade and
recreated with new structure.
SAPSR3.PLAN_TABLE
.
27 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 : SID
DATABASE CREATION TIME : 2011-JUL-08 13:46:58
DATABASE AGE : 4 years 107 days
DATAOBJ# (current max.) : 310918493
OBJECT ID (current max.) : 310918487
OBJECT 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.
28 Freudenberg IT | DOAG Conference Nürnberg | 2015
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
29 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 datafiles
to have plenty of free space (don’t rely on this check here!)
30 Freudenberg IT | DOAG Conference Nürnberg | 2015
Upgrade preparations (online) – additional checks
UPGRADING TO ORACLE 12C
• in order to avoid DBUA bug, check if datafiles with absurd autoextend settings
exist, i.e. AUTOEXTENSIBLE=YES, but current size (BYTES) exceeds defined
MAXBYTES and turn them off:
31 Freudenberg IT | DOAG Conference Nürnberg | 2015
DBUA calculates
negative space values!
Upgrade preparations (online) – additional checks
UPGRADING TO ORACLE 12C
• in order to avoid DBUA bug, check if datafiles with absurd autoextend settings
exist, i.e. AUTOEXTENSIBLE=YES, but current size (BYTES) exceeds defined
MAXBYTES and turn them off:
SQL>
select 'alter database datafile ''' || FILE_NAME || ''' autoextend off;'
from dba_data_files where AUTOEXTENSIBLE='YES' and MAXBYTES<BYTES
union
select 'alter database tempfile ''' || FILE_NAME || ''' autoextend off;'
from dba_temp_files where AUTOEXTENSIBLE='YES' and MAXBYTES<BYTES;
e.g.: SQL> alter database datafile '/oracle/SID/sapdata88/undo_1/undo.data1'
autoextend off;
Database altered.
32 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 “much
more” for large BW systems)
• recommendation: make preparations for using “flashback database” (with
guaranteed restore point before starting structure upgrade):
• create filesystem /oracle/<SID>/oraflash (space similar to oraarch needed)
• and activate configuration parameters:
db_recovery_file_dest='/oracle/<SID>/oraflash'
db_recovery_file_dest_size=<less than filesystem 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
33 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 your
database being upgraded – this would cause DBUA to crash during input phase (should be
fixed soon)
• Linux: be sure not to have set use_large_pages=auto – this would cause DBUA to
abort in upgrade phase (this value is no longer supported)
34 Freudenberg IT | DOAG Conference Nürnberg | 2015
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”
35 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 right
Oracle Home
36 Freudenberg IT | DOAG Conference Nürnberg | 2015
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• new: upgrade parallelism
• attention: correct “Diagnostic Destination” to SAP standard /oracle/<SID>/saptrace to avoid
errors (SAP upgrade guide incorrect at this point – will be corrected)
37 Freudenberg IT | DOAG Conference Nürnberg | 2015
new
change!
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• new: Listener will be upgraded automatically, too, when running with the SID being
upgraded:
38 Freudenberg IT | DOAG Conference Nürnberg | 2015
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• ORA-32006 due to “REMOTE_OS_AUTHENT”: to be ignored
39 Freudenberg IT | DOAG Conference Nürnberg | 2015
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued)
• new:
summary screen
with timings
• attention:
to continue (finish)
press “Upgrade
Results” – don’t
CANCEL
40 Freudenberg IT | DOAG Conference Nürnberg | 2015
click here to continue & finish
new
Database structure upgrade (offline) – DBUA
UPGRADING TO ORACLE 12C
• sample screenshots: (continued) • “Close” at the end of “Upgrade Results” screen will finish DBUA
41 Freudenberg IT | DOAG Conference Nürnberg | 2015
…
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 = 8
in 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
42 Freudenberg IT | DOAG Conference Nürnberg | 2015
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)
43 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 Status
XDB 12.1.0.2.0 VALID
CATALOG 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
44 Freudenberg IT | DOAG Conference Nürnberg | 2015
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• important 1: if guaranteed restore point had been created, drop it now and update
parameter 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
45 Freudenberg IT | DOAG Conference Nürnberg | 2015
Post upgrade steps (offline/online)
UPGRADING TO ORACLE 12C
• catsbp sample output (new):
orasid> $ORACLE_HOME/sapbundle/SBP_121024_201509/catsbp
catsbp - 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
46 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 be
fixed with next versions; so far, invalid objects really have to be “resolved”!
47 Freudenberg IT | DOAG Conference Nürnberg | 2015
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)
48 Freudenberg IT | DOAG Conference Nürnberg | 2015
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/<SID> 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
49 Freudenberg IT | DOAG Conference Nürnberg | 2015
Runtime behavior (without new features)
RUNTIME EXPERIENCE SAP ON ORACLE 12C
• Examples:
• experience from large BW systems (DB size 2 TB – 10 TB, > 500 concurrent
users), 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 features
same good performance as before (no degradation, although you could assume a higher
need 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 even
improvements without doing special tunings or using new features
50 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 other
service disruption
• fully integrated in brspace already, see help menu:
Options for 'dfmove' function:
[-c|-force]
force database shutdown for move
default: don't shut down if SAP user connected
[-d|-destination <destination>]
move destination
no default
[-f|-file all_df|<file>|<file_id>|<file_id1>-<file_id2>|<sapdata_dir>
|sapdata<N>|sapraw|<win_drive>:|+<asm_disk>|<file_list>]
file name
all_df - all eligible data files
no default
[-o|-online no|yes]
move data file online (as of Oracle 12c)
default: no
[-p|-parallel <processes>]
number of parallel copy processes
default: 1
[-t|-tablespace <tablespace>|<tablespace_list>]
tablespace name
no default
...
51 Freudenberg IT | DOAG Conference Nürnberg | 2015
First experiences with new features
NEW 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:48
BR0657I 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
52 Freudenberg IT | DOAG Conference Nürnberg | 2015
First experiences with new features
NEW 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:32
BR1016I 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.data1
Completed: /* BRSPACE */ alter database move datafile
'/oracle/ZZ3/sapdata4/datusr_1/datusr.data1' to '/oracle/ZZ3/sapdata2/datusr_1/datusr.data1'
53 Freudenberg IT | DOAG Conference Nürnberg | 2015
First experiences with new features
NEW 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 on
block 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 compression
shows slightly better overall compression with “advanced”, some indexes up to 15% better
• loading “new systems” already tested: works great. Runtime behavior also fine.
54 Freudenberg IT | DOAG Conference Nürnberg | 2015
First experiences with new features
NEW FEATURES ORACLE DATABASE 12C
• Advanced Index Compression: (continued)
• results from first compression ratio comparison in detail:
55 Freudenberg 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)
Future use of new features
NEW 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 package
SAP_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=<IM size>
56 Freudenberg IT | DOAG Conference Nürnberg | 2015
Future use of new features
NEW FEATURES ORACLE DATABASE 12C
• IM administration: (continued)
• Determine tables to be loaded into memory
• Load tables into IM store:
SQL> alter table "SAPSR3"."<table>" inmemory memcompress for query low
priority high;
Table altered.
• Monitoring status:
v$inmemory_area
v$im_segments
57 Freudenberg IT | DOAG Conference Nürnberg | 2015
Future use of new features
NEW 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
58 Freudenberg IT | DOAG Conference Nürnberg | 2015
Future use of new features
NEW 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
59 Freudenberg IT | DOAG Conference Nürnberg | 2015
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
60 Freudenberg IT | DOAG Conference Nürnberg | 2015
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 costs
must be considered
61 Freudenberg IT | DOAG Conference Nürnberg | 2015
Using OEM 12c in SAP environment
ORACLE 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
62 Freudenberg IT | DOAG Conference Nürnberg | 2015
Typical examples on next slides…
Using OEM 12c in SAP environment – examples
ORACLE ENTERPRISE MANAGER 12C
63 Freudenberg IT | DOAG Conference Nürnberg | 2015
Example 1 – Top Activity:
„who“ is responsible for peak load?
Answer: BW index creation!
Using OEM 12c in SAP environment – examples
ORACLE ENTERPRISE MANAGER 12C
64 Freudenberg 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%
Using OEM 12c in SAP environment – examples
ORACLE ENTERPRISE MANAGER 12C
65 Freudenberg IT | DOAG Conference Nürnberg | 2015
Example 3 – Monitor SQL Execution:
what does running SQL do?
Answer: watch it!
Summary
Summary
RUNNING 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 the
operation of SAP systems, also in the hosting environment of Freudenberg IT.
67 Freudenberg IT | DOAG Conference Nürnberg | 2015
How to reach us
CONTACT
Your contact
@
Your contact
@
68
Freudenberg IT
Höhnerweg 2-4
69465 Weinheim
Germany
Dr. Thimo Bastuck
Solution 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üffer
Principal Sales Consultant
+49 40 89091 135
www.oracle.com/de
Thank you for
your attention.