Using Google Chrome?

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

Recent Posts

Monday, February 27, 2012

Running Oracle Production Database on VMware

NOTE: For research links, see my later post here.

Are you considering running Oracle production databases on VMware?
Obviously you've considered the Oracle support policy on this.
How likely is it that you will get asked to "make it physical"?  or How will Oracle support deal with your call if you tell them you're on VMware?

Well, there are some harsh responses from Oracle towards VMware customers if you have access to Metalink (sorry, My Oracle Support).
First you should read this doc: 1071005.1 (HOTSPOT ERROR DURING 32-BIT 11GR2 CLIENT INSTALL ON 64-BIT (X86_64) SUSE (VMWARE)).
Then read this document: 1075717.1 (Installing 32-bit RDBMS Client software on x86_64 Linux.)
I don’t think the first problem’s resolution is justified.  Do you?
I have found Oracle notes that state the following:

"(1) Make sure you are logged into the Server Console directly, and that you are NOT trying to install the patch over a remote connection (such as Terminal Services, Remote Desktop, Timbuk2, PCAnywhere, VNC, VMWare, etc.) "

"As for installing over a remote connection, we do not support this, because Oracle cannot control the way the permissions are setup, over the remote connection."

So VMware console is a remote connection...

Then there is this document:
http://www.oracle.com/us/solutions/sap/wp-o10g-rac-config-win-303805.pdf

This doc is an Oracle whitepaper which details SAP Netweaver / Oracle Databse 10gR2 RAC on Windows 2003.
In the doc it shows an example hosts file which is clearly from a VMware hosted server.  Double standards I feel!

This is just not a nice place to be if you're trying to convince a company that VMware is a solid, supported tool to run production databases, and that Oracle even support RAC on it now.  Maybe some of the notes are old, before the acceptance by Oracle that VMware is becoming big in many companies.  Or maybe Oracle's RAC support is an illusion of good will, whilst they quietly (or not so) improve the Oracle VM product.
The best way to tell, would be the acceptance of Oracle that VMware's vCPU allocation is acceptable as a form of hard partitioning, so that you can bring the Oracle DB license cost down by runnining on VMware, in the same way you can on Oracle VM.

Sunday, February 26, 2012

_fix_control bug

It turns out that the issue with the Oracle init param: "_FIX_CONTROL" preventing the database from starting (I blogged about this before), is bug 7509689: "_FIX_CONTROL" IS CAUSING TROUBLE DURING STARTUP.

It's fixed in 10.2.0.5.

Friday, February 17, 2012

Use Oracle 10g Segment Advisor Usage

Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It's useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor
Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space
An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

-- Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,'DD-MM-YY HH24:MI:SS') start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner='SYS'
AND job_name = 'AUTO_SPACE_ADVISOR_JOB'
ORDER BY actual_start_date;


Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

-- Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

-- Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK('Segment Advisor', :TASK_ID, :TASK_NAME);

-- Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'TABLE', '<SCHEMA>', '<TABLE NAME>', NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'INDEX', '<SCHEMA>', '<INDEX NAME>', NULL, NULL, :OBJECT_ID);

-- Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

-- Check the results in two ways:
-- Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

-- or use DBMS_SPACE package (recommended).

SELECT
  RECOMMENDATIONS RECOMMENDATION,
  C1 ACTION1,
  C2 ACTION2,
  C3 ACTION3
FROM
  TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
  TASK_ID = :TASK_ID;


-- Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);

Tuesday, February 14, 2012

Disabling Change or Deactivation of SAP Audit Logging SM19 - SAL

To disable changing or deactivation of the SAP Audit Logging (SAL) settings via transaction SM19, you can remove the authorisation object S_ADMI_FCD activity AUDA.
(see http://wiki.sdn.sap.com/wiki/display/SMAUTH/S_ADMI_FCD).

This would prevent “normal” BASIS administrators from being capable of changing or disabling audit logging, but permit you to provide an emergency user with this capability (you will need to change SAL settings in SM19 at some point!).

Sunday, February 12, 2012

ORA-01114 IO Error But What Is The Oracle File

Whilst creating a new 10GB index on a large 40GB table I was experiencing the
"ORA-01114: IO error writing block to file <file#> (block # <block#>)" error.

The main vital piece of information is the file number (<file#>). This can be found in the DBA_DATA_FILES or DBA_TEMP_FILES views as the FILE_ID column.

So, you construct a query to go find the file name:

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id = <the number>
UNION ALL
SELECT file_id, file_name
FROM dba_temp_files
WHERE file_id = <the number>;

NOTE: I query both just in case. I’ve worked on systems where the TEMP tablespace has not been created as “TEMPORARY”!

After running the query, you get “0 rows returned”.
What!

Well, in this case, the TEMP tablespace was a proper TEMPORARY tablespace as the file_id was returned from the DBA_TEMP_FILES view.

I knew that the file numbers were somehow linked to the number of permitted files in the database.  This is controlled by the DB_FILES database parameter.
Looking at the Oracle documentation (for 10g) here: http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm#i1206149

You will see that Oracle states that:
...the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.

In short this means that the DB_FILES parameter only applies to datafiles and not tempfiles.
Therefore, all other files will be outside this parameter range.

My parameter DB_FILES was set to 1024.
My reported file# in the ORA-01114 error was greater than 1024.
If you look at the file_id values for the DBA_TEMP_FILES view, you'll notice that the file_id numbers conflict with the file_id values in DBA_DATA_FILES.
Since the tempfiles exist outside the range of DB_FILES, I subtracted the value of parameter “DB_FILES” from the reported file#, to achieve the file_id for tempfiles belonging to temporary tablespaces.
Voila!

Sure enough, when I subtracted 1024 (my DB_FILES value) from my reported file id in the ORA-01114, I got 2, which equated to the second tempfile in the TEMP tablespace.
The partition that the tempfile was sitting in was 95% full, but the tempfile couldn't expand by the requested next extent size as it would be more than the partition free space.  Increasing the partition size fixed my problem.

Is there another way of viewing the online file# vs the DBA_x_FILES file_id field?  I don't know, but if I find one, I'll post it here.

Wednesday, February 08, 2012

SAP Short Dump SYSTEM_NO_TASK_STORAGE

A background job has produced a short dump with SYSTEM_NO_TASK_STORAGE.
The “Heap Memory” section in ST02 showed a maximum use of 1.5GB, but we had allocated nearly 1.9GB according to the "abap/heap_area_nondia" and "abap/heap_area_dia" instance profile parameters.

The ST02 short dump analysis shows the source code line where the problem occurred. The line of code doesn't look specifically interesting, it is, however, requiring a slight increase in memory allocation.
If the program is running in DIALOG then it will be allocated Extended Memory first, followed by HEAP (local process) memory.
If the program is running in BACKGROUND then it will be allocated HEAP memory first, followed by Extended Memory.

Extended Memory is pre-allocated at system startup according to the EM initial setting in the SAP instance profile.  It is then increased up to the maximum specified in the instance profile per user and per application server.
HEAP memory is only allocated as it is needed within each of the SAP dw.* OS processes, up to the maximum specified (in instance profile) per user or per application server.  It is then released back to the OS when the process finishes processing (SAP restarts the work process) and it has used over a specific amount of memory as set in parameter abap/heaplimit.

Since this was a background job, we can assume that we exhausted HEAP memory and should have automatically switched to Extended Memory.
However, the Kernel section of the short dump showed that we may have experienced an issue obtaining more virtual memory from the OS:

*** Error in libunwind: Out of memory. Try with a higher value >
 > for UNWIND_RESERVE_MEM (current value = 16).
 (0) 0x40000000017ae480 [dw.sapXXX_DVEBMGS01]
 (1) 0x40000000017ae2b0 [dw.sapXXX_DVEBMGS01]
 (2) 0x40000000021de880 [dw.sapXXX_DVEBMGS01]
 (3) 0x40000000021e45d0 [dw.sapXXX_DVEBMGS01]
 (4) 0x400000000115e860 [dw.sapXXX_DVEBMGS01]
 (5) 0x400000000120b3a0 [dw.sapXXX_DVEBMGS01]
 (6) 0x40000000010542f0 [dw.sapXXX_DVEBMGS01]
 (7) 0x4000000001111940 [dw.sapXXX_DVEBMGS01]
 (8) 0x40000000011ae790 [dw.sapXXX_DVEBMGS01]
 (9) 0x40000000012fb090 [dw.sapXXX_DVEBMGS01]
 (10) 0x40000000012fd7d0 [dw.sapXXX_DVEBMGS01]
 (11) 0x400000000188cae0 [dw.sapXXX_DVEBMGS01]
 (12) 0x4000000001896e70 [dw.sapXXX_DVEBMGS01]
 (13) 0x4000000001891670 [dw.sapXXX_DVEBMGS01]
 (14) 0x40000000018949a0 [dw.sapXXX_DVEBMGS01]
 (15) 0x400000000187f1e0 [dw.sapXXX_DVEBMGS01]
 (16) 0x40000000014d1ce0 [dw.sapXXX_DVEBMGS01]
 (17) 0x400000000149e8d0 [dw.sapXXX_DVEBMGS01]
 (18) 0x4000000001496fc0 [dw.sapXXX_DVEBMGS01]
 (19) 0x4000000001364c30 [dw.sapXXX_DVEBMGS01]
 (20) 0x4000000000ed4af0 [dw.sapXXX_DVEBMGS01]
 (21) 0x4000000000ed4a90 [dw.sapXXX_DVEBMGS01]
 (22) 0xc000000000045880 main_opd_entry + 0x50 [/usr/lib/hpux64/dld.so]


Is this case, you could either add more OS memory (essentially you have over allocated memory somewhere), or you could resize the abap/heap_area_nondia parameter to a smaller amount, so that it will switch to EM sooner.

Wednesday, February 01, 2012

Documenting an SAP ABAP System Technical Configuration

The following are my thoughts for documenting the technical configuration of an existing SAP ABAP stack:

                ENVIRONMENTS                             
                ARCHITECTURE OVERVIEW                         
                OPERATING SYSTEM DETAILS                    
                                PATCHES             
                                PARAMETERS   
                                USERS  
                DATABASE SYSTEM DETAILS                       
                                PATCHES             
                                PARAMETERS   
                                SAP SCHEMAS  
                                DB LINK INTERFACES     
                SAP KERNEL DETAILS                     
                                PARAMETERS   
                SAP COMPONENT DETAILS                         
                ABAP STACK DETAILS                    
                                LICENSES            
                                OPERATION MODES      
                                CLIENTS               
                                TMS      
                                STANDARD SAP JOBS & SCHEDULES        
                                NON-STANDARD JOBS & SCHEDULES     
                                SPOOL SERVERS               
                                OUTPUT DEVICES            
                                SYSTEM USERS 
                                DATABASE CHECKS AND JOBS   
                                CCMS MONITORING     
                CUSTOM DEVELOPMENTS                          
                                TRANSACTIONS               
                                PROGRAMS AND REPORTS         
                                FUNCTION MODULES   
                                BAPIS   
                                INTERFACES      
                                                System Landscape Directory
                                                Central User Administration
                                                Solution Manager Data Collection
                                                Integration Server
                                                RFCs
                                                Web Services
                                                JDBC Connectors
                SNOTES AND REPAIRS