Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Thursday, May 29, 2014

HANA Memory Allocation Limit Change With hdbcons for HANA

The SAP HANA command line utility hdbcons can be used to administer the HANA system directly from the Linux operating system command line.

It's a very powerful utility and I'm sure as time goes by, it will provide invaluable information and functionality for HANA DB administrators to help fine-tune the database.

Log in as the <sid>adm Linux user and run the hdbcons command.
It will automatically connect the indexserver for that instance (if it's running).
You can then list the available server commands:

hana01:/usr/sap/H10/HDB10> hdbcons
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'H10', instance '10'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 4092)
> help

Available commands:
ae_checksize - Check and Recalculate size of columns within the Column Store
authentication - Authentication management.
bye - Exit console client
cd - ContainerDirectory management
checktopic - CheckTopic management
cnd - ContainerNameDirectory management
context - Execution context management (i.e., threads)
converter - Converter management
crash - Crash management
crypto - Cryptography management (SSL/SAML/X509).
deadlockdetector - Deadlock detector.
debug - Debug management
distribute - Handling distributed systems
dvol - DataVolume management
ELF - ELF symbol resolution management
encryption - Persistence encryption management
event - Event management
exit - Exit console client
flightrecorder - Flight Recorder
help - Display help for a command or command list
log - Show information about logger and manipulate logger
mm - Memory management
monitor - Monitor view command
mproxy - Malloc proxy management
mutex - Mutex management
output - Command for managing output from the hdbcons
pageaccess - PageAccess management
page - Page management
pcm - Performance Counter Monitor management
profiler - Profiler
quit - Exit console client
replication - Monitor data and log replication
resman - ResourceManager management
runtimedump - Generate a runtime dump.
savepoint - Savepoint management
snapshot - Snapshot management
statisticsservercontroller - StatisticsServer internals
statreg - Statistics registry command
stat - Statistics management
tablepreload - Manage and monitor table preload
tracetopic - TraceTopic management
trace - Trace management
version - Version management
vf - VirtualFile management

For each of the above commands, additional help can be obtained by re-issuing "help" plus the command name e.g.:

> help mm

Synopsis: mm <subcommand> [options]: Memory management Available subcommands:
   - list|l [-spf] [-l <level>] [<allocator_name>]: List allocators recursively
      -C: work on composite allocators
      -s: Print some allocator statistics (use stat command for full stats)
      -p: Print also peak usage statistics
      -f: Print allocator flags
      -S: Sort output descending by total size
      -l <level>: Print at most <level> levels
   - flag|f [-C] <allocator_name> [-rsdt] <flag_set>: Set allocator flags
      -C: work on composite allocators
      -r: Set flags recursively
      -s: Set given flag(s), default
      -d: Delete given flag(s)
      -t: Toggle given flag(s)
      -a: Also apply changes to associated composite allocators (not allowed in context with '-C')
   - info|i [-f] <address>: Show block information for a given address
      -f: Force block info, even if block not known
   - blocklist|bl [-rtsS] <allocator_name> [-l <count>]: List of allocated blocks in an allocator
      -C: work on composite allocators
      -r: Show blocks in sub-allocators recursively
      -s: Show also allocation stack traces, if known. Cannot be combined with optiont '-t'.
      -S: Show only blocks with known allocation stack traces. Cannot be combined with optiont '-t'.
      -t: Show top allocation locations sorted by used size (descending).
            Cannot be combined with options '-s' or '-S'.
            The default number of printed locations is 10, this can be changed by '-l' option.
      -l <count>: Limit to <count> locations. Valid only if combined with option '-t'. Unlimited in cas <count> = 0.
   - maplist|ml: List all mappings used by the allocator
   - mapcheck|mc [-sln]: Check all mappings used by the allocator
      -s: Also show all system mappings
      -l: Also show own mappings as in maplist
      -n: Suppress output of known alloc stack traces for unaccounted memory
   - mapexec|me [-u]: List all known executable module mappings
      -u: Update list of known executable modules
   - reserveallocator|reserveallocators: Print information about OOM Reserve Allocators
   - top [-C] [-sctr] [-l <count>] [-o <fn>] [<allocator_name>]: List top users of memory, <allocator_name> is needed except for option '-t'
      -C: work on composite allocators
      -s: Sort call stacks by used size (default)
      -c: Sort call stacks by call count
      -t: Throughput, sort nodes by sum of all allocations
      -r: work recursively also for all suballocators
      -l <count>: Only output top <count> stack traces (0=unlimited)
      -o <fn>: Specify output file name
   - callgraph|cg [-sctrk] [-l <count>] [-o <fn>] [<allocator_name>]: Generate call graph, <allocator_name> is needed except for option '-t'
      -C: work on composite allocators
      -s: Sort nodes by used size (default)
      -k: Ouput in kcachegrind format
      -c: Sort nodes by call count
      -t: Throughput, sort nodes by sum of all allocations
      -r: Work recursively also for all suballocators
      -d: Do full demangle (also parameters and template arguments)
      -l <count>: Only output top <count> functions (0=unlimited)
      -o <fn>: Specify output file name (for .dot graph)
   - resetusage|ru [-r] [<allocator_name>]: Reset call stack usage counters, <allocator_name> is needed except for option '-r' or '-C'
      -C: work on composite allocators
      -r: Work recursively also for all suballocators
   - limit [-s <size>[K|M|G|T]]: Get current allocation limit in bytes
      -s <size>[K|M|G|T]: Set current allocation limit in bytes, KB, MB or GB
   - globallimit [-s <size>[K|M|G|T]]: get current global (if IPMM is active) allocation limit in bytes
      -s <size>[K|M|G|T]: Set current global (if IPMM is active) allocation limit in bytes, KB, MB or GB
   - garbagecollector|garbagecollection|gc [-f]: Return free segments/blocks to
     operating system
      -f: Also return free fragments in big blocks
   - ipmm: Print Inter Process Memory Management information
      -d: Print detailed information.
   - compactioninfo, ci: Print information about last compaction
   - virtual: Print information about virtual but not resident memory (linux only)
   - requested: Print information about requested allocations (reporting no overhead at all), iterates over all instances of ReportRequestedAllocators
   - blockedmemory [-s <size>[K|M|G|T]]: Get current blocked memory.
      -s <size>[K|M|G|T]: Set current blocked memory in bytes, KB, MB or GB and try to reserve this memory. Common options and arguments:
   - <allocator_name>: Name of the allocator to address
   - <flag_set>: Comma-separated list of following flags: ffence (fence front, writes the pattern 0xaaccaacc in front of the allocated block),
     bfence (fence back, writes the pattern 0xaaccaacc behind the allocated block), astrace (stack trace at allocation),
     dstrace (stack trace at deallocation), areset (overwrite at allocate with pattern 0xd00ffeed),
     dreset (overwrite at deallocate with pattern 0xdeadbeef), all, none, default, !emptyok (allow
     non-empty destruction), preventcheck (prevent changing check flags)
     atrace (trace at allocation), dtrace (trace at deallocation),
     malf (malfunction) or their 2-letter shortcuts [OK]

As an example, the current global allocation limit can be displayed:

> mm globallimit
Current global allocation limit=15032385536B.

We can adjust the global allocation limit online by issuing the additional "-s" parameter:

> mm globallimit -s 16G
Current global (if IPMM active) allocation limit: 17179869184B

Now re-check:

mm globallimit
Current global allocation limit=17179869184B.

What's the current global allocation limit  in the global.ini you might ask?

hana01:/usr/sap/H10/HDB10> grep '^global'  /usr/sap/H10/SYS/global/hdb/custom/config/global.ini

global_allocation_limit = 14336

It hasn't changed.

So we have confirmed that we can affect the configuration of the HANA system in real-time using hdbcons, but we don't necessarily preserve the configuration.
You can also check in HANA Studio on the landscape page.
Since each service (process) takes it's memory allocation percentage from the global allocation, this will automatically change in real-time too.
This means that for analysing "what-if" style scenarios or for operational acceptance testing, you can effectively present a set of configuration values and work through them almost automatically.  Invaluable for those test automation guys in the field.

Thursday, May 22, 2014

HowTo: Using DBMS_STATS to Restore Stats

Scenario: You're about to implement some major changes to the Oracle database that will adjust the database statistics.
Oracle provide the DBMS_STATS package to help administer stats.  The package includes some procedures that can be used to export/import stats, plus restore them to a previous point in time.

When statistics are updated using DBMS_STATS.GATHER_*_STATS, it saves the previous version in the database (can be changed with DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure).  Also, see table DBA_TAB_STATS_HISTORY.

These versions are retained for a specific retention period, which you can check using the GET_STATS_HISTORY_RETENTION procedure:

SQL> set serveroutput on
v number;
DBMS_OUTPUT.PUT_LINE('Stats history retention: ' || v || ' days.');

Stats history retention x days.

PL/SQL procedure successfully completed.

You can also check the date of the oldest stats history:

SQL> set serveroutput on
v timestamp;
DBMS_OUTPUT.PUT_LINE('Oldest stats history: ' || v);

Oldest stats history: 15-DEC-13 PM

PL/SQL procedure successfully completed

To restore the statistics you can use one of the relevant procedures:


See here for parameters:
As an example, the RESTORE_SCHEMA_STATS procedure takes the following parameters:

ownname   Schema owner,
timestamp   Timestamp,
force   TRUE|FALSE   Restore even if stats are locked, default TRUE,
no_invalidate   TRUE|FALSE   Invalidate cursors, default get_param('NO_INVALIDATE').

If the stats are restored to a specific timestamp, it means that whatever statistics values were applicable to a specific table at a specific point in time, are applied to the tables.  If the table's statistics are not changed then there will be gaps in the history.
You can imagine this being like a roll-forward through the DBA_TAB_STATS_HISTORY table, until the timestamp specified.

WARNING: If the table's statistics are not changed then there will be gaps in the history.  In which case, you may not be able to restore previous statistics if the table stats have not changed within the last history window (default 31 days).

Some great examples are here:

You should also note, that under an SAP system, the Oracle stats gatherer is called by BR*Connect, and note that it calls the GATHER_TABLE_STATS procedure for each table that is mentioned in table DBSTATC for tables that have stats enabled in DBSTATC.
If the table is not enabled to collect stats, then it may have stats delivered by SAP (see SAP note 1020260), in which case, there may not be any history.

Also see my blog post on SAP statistics and DBSTATC.

Tuesday, May 20, 2014

SAP HANA - Migrate Statistics Server 1917938

Since SAP note "1917938 - Migration of statistics server with upgrade to SPS 7" seems to be going missing rather a lot, I've noted the content here for reference based on v10 05-05-2014.

If you do not monitor or administrate the upgraded SAP HANA database with the DBA Cockpit or Solution Manager, you can activate the new statistics server. If the DBA Cockpit or Solution Manager is active, you are only allowed to activate the new statistics server if you observe SAP Note 1925684.

A configuration change is required to activate the new statistics server:

nameserver.ini -> [statisticsserver]->active=true

The data held in the persistence of the statistics server is now transferred to the persistence of the master index server. At the end of the migration, the statistics server is automatically stopped and removed from the database configuration (topology). The functions of the statistics server are distributed to other services.

The migration of the statistics server is carried out without interrupting the backup history, which means that data and log backups created before the migration can still be used to restore the SAP HANA database.

The HANA instance must not be restarted during the migration.
The migration is completed when no statisticsserver process is running in the HANA instance.
It is not necessary to restart the HANA instance following the migration.

Thursday, May 15, 2014

RMAN 10.2 Block Corruption Checking - Physical, Logicial or Both

It's an old topic, so I won't dwell on the actual requirements or the process.

However, what I was not certain about, was whether RMAN in 10.2 (10gR2) would perform both physical *and* logical corruption checking if you use the command:


I kept finding various documents with wording like that found here:
"For example, you can validate that all database files and archived redo logs can be backed up by running a command as follows:

This form of the command would check for physical corruption. To check for logical corruption,

It took a while, but I found the original document from Oracle here:

Right at the bottom, it confirms that ordinarily "BACKUP VALIDATE DATABASE;" would check for physical corruption.
The additional keywords "CHECK LOGICAL" will check for logical corruption *in addition* to physical corruption.

So RMAN doesn't need running twice with each validate command combination.

Thursday, May 08, 2014

HowTo: Check SAP SUM Version Without Executing

Scenario: You have extracted, or previously used and subsequently found, a SUM directory on your SAP system.
This is production and you don't want to start SUM on this system.
You want to know what version of SUM it is.

You can check the file for the SUM version without needing to start SUM:


> more
Manifest-Version: 1.0
keyname: SUM
keylocation: SAP AG
os: NTAMD64
compilation mode: UNICODE
compiled for: 64 BIT
release: 1.0
support package: 7
patch number: 2

native branch: lmt_008
java branch: lmtj_008_REL
assembly time: 2013-05-13 05:10:16
pack version: 22
pack tool version: 1.042

You are interested in the lines:
"release", "support package" and "patch number".

The example above is therefore SUM 1.0 SP07 patch 2.

Thursday, May 01, 2014

SQL Server Shrink Transaction Log Script

Below is a script that shrinks SQL Server transaction logs tested on SQL Server 2008R2.
Before running the script, you should ensure that you take a transaction log backup of your databases (which obviously means you should have already taken a full backup).  The transaction log backup will free the virtual logs from within the transaction log file.

The script simply tries to shrink the transaction log file by 25% for any databases that are not called "MASTER", "MODEL", "MSDB" or "TEMPDB".

If you wish to shrink logs by more than 25%, either change the script, or run it multiple times until it can't shrink the logs any further.

NOTE: When executing the script in SQL Management Studio, you should set the results output to "Text", so that you can see the output of the script.

USE master

DECLARE @database_id    int,
        @database_name  nvarchar(128),
        @file_id        int,
        @file_name      nvarchar(128),
        @size_mb        int,
        @new_size       int;

DECLARE @dbcc_output    char(5);
  SELECT database_id,
         UPPER(DB_NAME(database_id)) database_name,
         name file_name,
         (size*8)/1024 size_mb
   FROM  master.sys.master_files
   WHERE type_desc = 'LOG'
     AND state_desc = 'ONLINE'
   ORDER BY size_mb DESC;

    OPEN Cur_LogFiles
    FETCH NEXT FROM Cur_LogFiles
       INTO @database_id, @database_name, @file_id, @file_name, @size_mb
      -- Determine 25% of our current logfile size.
      SET @new_size = @size_mb*0.75;
      -- Set context to the database that owns the file and shrink the file with DBCC.
      PRINT 'Database: ' + @database_name + ', file: ' + @file_name + ', size: ' + CONVERT(varchar,@size_mb,25) + 'mb';
      PRINT 'Attempting to shrink file: ' + @file_name + ' by 25% to: '+ CONVERT(varchar,@new_size,25) + 'mb';

      EXEC ('USE [' + @database_name + ']; DBCC SHRINKFILE (['+@file_name+'],'+@new_size+');');

      FETCH NEXT FROM Cur_LogFiles
         INTO @database_id, @database_name, @file_id, @file_name, @size_mb

    CLOSE Cur_LogFiles
    DEALLOCATE Cur_LogFiles