Oracle Archives » Musings of an IT Implementor

SAP Secondary Oracle DB Connection–EasyConnect

When you run an SAP system on a non-Oracle database platform, you may sometimes need to connect to a secondary Oracle database (for example, in a SAP BW environment you could need a connection to multiple source database systems).

The process that is usually followed, is to create the TNSNAMES.ora in the appropriate location on *every* SAP application server of the SAP system. Then put the TNS service name and username/password into the DBCO transaction within SAP.

There are a couple of downsides to this approach:

1, You generally have to put the TNSNAMES.ora file in /sapmnt/<SID> as this is already shared across the SAP system’s application servers.

2, You have to keep the TNSNAMES.ora file updated. Any changes require a complete restart of the SAP system in order for the file to be re-read.

This is where the Easy Connect string can be used.
Instead of entering the TNS service name into the DBCO transaction, you simply enter all the service details, removing the need for the TNSNAMES.ora file.

An example of the Easy Connect string is:

“servervname.com:1521/tns-service-name”

We are supposing that:

– “tns-service-name” is the TNS service name for your target database (listened for on the target Oracle listener)
– Port 1521 (default port) is used by the listener.
– The server on which the listener is located is servername.com.

You must include the double quotes in the DBCO entry.

Based on the above entry, you can then dynamically change the value as and when needed.
No need for a restart of the SAP application server.

Reference: SAP note: 808505 – Secondary connections to Oracle database

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
SQL> DECLARE
v number;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE(‘Stats history retention: ‘ || v || ‘ days.’);
END;
/ 
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
SQL> DECLARE
v timestamp;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
DBMS_OUTPUT.PUT_LINE(‘Oldest stats history: ‘ || v);
END;
/
Oldest stats history: 15-DEC-13 11.29.32.143688 PM
PL/SQL procedure successfully completed

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

DBMS_STATS.RESTORE_DICTIONARY_STATS
DBMS_STATS.RESTORE_FIXED_OBJECT_STATS
DBMS_STATS.RESTORE_SCHEMA_STATS
DBMS_STATS.RESTORE_SYSTEM_STATS
DBMS_STATS.RESTORE_TABLE_STATS

See here for parameters:
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#insertedID2
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: https://www.morganslibrary.org/reference/pkgs/dbms_stats.html

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.

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:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
I kept finding various documents with wording like that found here: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmbackp.htm#i1006353

“For example, you can validate that all database files and archived redo logs can be backed up by running a command as follows:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
This form of the command would check for physical corruption. To check for logical corruption,
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;"

It took a while, but I found the original document from Oracle here: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmconc1.htm#i1008614

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.

SAP DBCO Connection Without TNSNAMES

In order to create an external database connection to another database, so that an ABAP program can access it, you normally create the connection details in transaction DBCO.

However, if you use the ST04 transaction, it provides additional fields where you can enter the connection details such as Oracle listener port number, which will allow you to construct a connection string which will not require an entry in the server’s TNSNAMES.ora file.

HowTo: Read ST03 IO Redo Log Per Hour, Log Switches in SAP

Within the SAP St03 transaction, the analysis view “Wait Event Analysis -> IO Redo Log Per Hour” or “Redo Log Switches” (from SAPKB70029 onwards), is able to show you the Oracle redo log switch measure.
SAP Oracle IO Redo Log Per Hour

You will need to adjust the “Minimum Time Between Switches[sec]” and “Maximum Time Between Switches [sec]” values, then click the refresh button.
The results tell you in what hour period, the number of times a redo log switch was performed where the time between the switches was within your defined range.
As an example, the screen shot above shows that on 29-11-2013 between 04:00 and 05:00, a redo log switch occurred on only 1 occasion where the time between one log switch and another was within 120 seconds (2 minutes).
It’s difficult to say if too many log switches is an actual problem for your specific database, but I would tend to investigate any database where the logs are switching on more than a couple of occasions a day, within 60 seconds.