Monday, August 17, 2009

Sunday, August 16, 2009

Oracle Data Warehouse Tuning - 25 Tips

Things to know about Tuning Oracle Data Warehouse Databases -
-----------------------------------------------------------

1.Dimensions and Fact Tables
2.De-Normalization
3.RAID Levels (Design) - specific to DW applications
4.Big Tablespaces - Where single datafile can grow upto 128TB
5.Block size - 16K or 32K - Depends on the Operating system
6.Partitioning Options - which needs License from Oracle
Range,Hash,List and Composite partitions
7.Partition Operations - DDL specific
8.Bitmap Indexes - Helps a lot
9.Functional base indexes
10.Data Compresssion
11.Direct Data load operations
12.Oracle Joins - Nested , Merge and Hash joins
13.SQL Tuning - Need Good understanding of SQL Tuning
14.Parallel Operations
15.Materilaized Views
16.Dimensions
17.Query Rewrite option
18.AWR reports
19.ADDM
20.SQL Tuning Advisor
21.Reoranization
22.CBO Statistics
23.SQL Hints - This helps a lot in real time
24.Ofcourse some init.ora parameters like CBO related , MTS etc
25.IOT

RAC Features for Data Warehouse Databases
---------------------------------------

1.Automatic Workload Management
2.Parallel Query Options
3.Parallel Instance Groups
4.DOP
5.Be aware of the Inter connect traffic
6.Services
7.Partitions specific to RAC applications
8.SQL tuning
9.Dedicated Temp tablespaces
10.TAF

Oracle Portal 10.1.2.0.2 HA Implementation - HA

I already covered Oracle IAS 10.1.2.0.2 Disaster Recovery solution for Oracle AS Components (For IM and Portal) - Please see my previous thread for this.

I will cover Oracle Portal HA setup now.


For 11G Database Support
------------------------
Ofcourse Oracle Portal 10.1.4.1/10.1.4.2 is now ceritified with 11G database.For Portal 10.1.4.1, follow the recommended patches Doc ID 460362.1

Apply 10.1.2.3 patch set (5983622 Patch) to upgrade to 10.1.4.2 and upgrade the database to 11.1.0.6


Architecture Overview -
----------------------
2 Node Portal (Active/Active) cluster running on Linux ES AS 5. This Portal will be running on Oracle RAC 10G.

2 Nodes for IM.Virtaul host name IM
2 Nodes for OID.Virtaul host name OID
2 Nodes for RAC.
2 Nodes for Oracle Portal.Virtaul host name Portal
1 Node for LBR
1 Node for Linux DNS

All these nodes configured in the DNS for HN resolution.

Will post soon.....

Monday, August 10, 2009

Oracle 11G Active DataGuard Implementation using Active database (network) duplicate (RMAN) command - HA






Posting a very intresting topic on Oracle 11G. Initially I had some issues like ORA-01034, ORA-12528, RMAN-06217, ORA-00845 errors.Unfortunately I was not able to get much detailed information on how to implement this from google. It look me 2 hours to implement - the setup is the same as any Normal Dataguard (Oracle 10G) implementation. No major difference.

This feature is called Real-time Query database - that there is no suspension of Redo Apply. It uses incremental backup feature based on a change-tracking file. I mean like keeping track of all changed blocks through bitmaps. So much better performance. I would say that this should benefit a lot during the real time processing.





Oracle 11G performance improvement (Dataguard specific)
-------------------------------------------------------
1. Faster Failover - Failover in seconds with Fast-Start Failover.
2. Faster Redo Transport.
Optimized async transport for Maximum Performance Mode
Redo Transport Compression for gap fetching:new compression attribute for
log_archive_dest_n.
3. Faster Redo Apply
Parallel media recovery optimization.
4. Faster SQL Apply
Internal optimizations.
5. Fast incremental backup on physical standby database
Support for block change tracking.



(This Picture is about FAST FAILOVER - THIS SETUP NEEDs A THIRD NODE CALLED, SUBSCRIBER (MONITOR) AND DG BROKER SETUP. I will cover this topic soon). Oracle uses the same technology as what Microsoft does with SQL Server 2005, called Mirroring feature here ...!


Most important Prerequisites are
-------------------------------
1. Both the target and destination databases must be on an identical operating system platform.
2. Oracle Net must be aware of both the target and duplicate instances.
3. Both the target and destination databases must have the same sysdba password.
4. The target database must be open or in mount state.
5. If the target database is open, it must be in archivelog mode


Prepare your environments - both primary and secondary databases.This is the basic configuration like any standby setup. So I am skipping this.
Once everything is setup,

starup the primary database (in this case DG database). The primary can be either in mounted or open stage.

Start the standby instance (not mounted).

In pre 11G we used to create the standby control file and copy over it to the standby site. We also copy the datafiles to the standby site.

But in 11G this steps are not needed. Everything is automatic and so this process is called "Database Active Network duplicate" process.

Demo
----

DG - Primary Database
DG1 - Standby Database

From primary database - DG
--------------------------
SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

Active database (network) duplicate (RMAN) command
--------------------------------------------------

RMAN> duplicate target database for standby
2> db_file_name_convert '/dg/','/dg1/'
3> DORECOVER FROM ACTIVE DATABASE
4> spfile
5> parameter_value_convert '/dg/','/dg1/'
6> set log_file_name_convert '/dg/','/dg1/'
7> set fal_client='dgstdby'
8> set fal_server='dg'
9> set log_archive_dest_1='LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
10> set log_archive_dest_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
11> set standby_archive_dest='/u01/oracle/oradata/dg1'
12> set db_unique_name='dg1';
13>
Starting Duplicate Db at 12-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

contents of Memory Script:
{
backup as copy reuse
file '/u01/app/oracle/oracle11g/dbs/orapwdg' auxiliary format
'/u01/app/oracle/oracle11g/dbs/orapwdg1' file
'/u01/app/oracle/oracle11g/dbs/spfiledg.ora' auxiliary format
'/u01/app/oracle/oracle11g/dbs/spfiledg1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/oracle11g/dbs/spfiledg1.ora''";
}
executing Memory Script

Starting backup at 12-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
Finished backup at 12-AUG-09

sql statement: alter system set spfile= ''/u01/app/oracle/oracle11g/dbs/spfiledg1.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/dg1/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/oracle/oradata/dg1/control01.ctl'', ''/u01/oracle/oradata/dg1/control02.ctl'', ''/u01/oracle/oradata/dg1/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/dg/'', ''/dg1/'' comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''dgstdby'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''dg'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'' comment=
'''' scope=spfile";
sql clone "alter system set standby_archive_dest =
''/u01/oracle/oradata/dg1'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''dg1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/dg1/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u01/oracle/oradata/dg1/control01.ctl'', ''/u01/oracle/oradata/dg1/control02.ctl'', ''/u01/oracle/oradata/dg1/control03.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/dg/'', ''/dg1/'' comment= '''' scope=spfile

sql statement: alter system set fal_client = ''dgstdby'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''dg'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/oracle/dg1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'' comment= '''' scope=spfile

sql statement: alter system set standby_archive_dest = ''/u01/oracle/oradata/dg1'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''dg1'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1255473152 bytes

Fixed Size 1299624 bytes
Variable Size 721423192 bytes
Database Buffers 520093696 bytes
Redo Buffers 12656640 bytes

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/oracle/oradata/dg1/control01.ctl';
restore clone controlfile to '/u01/oracle/oradata/dg1/control02.ctl' from
'/u01/oracle/oradata/dg1/control01.ctl';
restore clone controlfile to '/u01/oracle/oradata/dg1/control03.ctl' from
'/u01/oracle/oradata/dg1/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/oracle11g/dbs/snapcf_dg.f tag=TAG20090812T030007 RECID=1 STAMP=694666809
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-09

Starting restore at 12-AUG-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=154 device type=DISK

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 12-AUG-09

Starting restore at 12-AUG-09
using channel ORA_AUX_DISK_1

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 12-AUG-09

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oracle/oradata/dg1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/oracle/oradata/dg1/system01.dbf";
set newname for datafile 2 to
"/u01/oracle/oradata/dg1/sysaux01.dbf";
set newname for datafile 3 to
"/u01/oracle/oradata/dg1/undotbs01.dbf";
set newname for datafile 4 to
"/u01/oracle/oradata/dg1/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/oracle/oradata/dg1/system01.dbf" datafile
2 auxiliary format
"/u01/oracle/oradata/dg1/sysaux01.dbf" datafile
3 auxiliary format
"/u01/oracle/oradata/dg1/undotbs01.dbf" datafile
4 auxiliary format
"/u01/oracle/oradata/dg1/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/oradata/dg1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oracle/oradata/dg/system01.dbf
output file name=/u01/oracle/oradata/dg1/system01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oracle/oradata/dg/sysaux01.dbf
output file name=/u01/oracle/oradata/dg1/sysaux01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oracle/oradata/dg/undotbs01.dbf
output file name=/u01/oracle/oradata/dg1/undotbs01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/oradata/dg/users01.dbf
output file name=/u01/oracle/oradata/dg1/users01.dbf tag=TAG20090812T030032 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-AUG-09

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/oracle/arch/1_4_694653014.dbf" auxiliary format
"/u01/oracle/dg1/1_4_694653014.dbf" ;
catalog clone archivelog "/u01/oracle/dg1/1_4_694653014.dbf";
switch clone datafile all;
}
executing Memory Script

Starting backup at 12-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=4 RECID=3 STAMP=694666920
output file name=/u01/oracle/dg1/1_4_694653014.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 12-AUG-09

cataloged archived log
archived log file name=/u01/oracle/dg1/1_4_694653014.dbf RECID=1 STAMP=694666922

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=694666922 file name=/u01/oracle/oradata/dg1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=694666922 file name=/u01/oracle/oradata/dg1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=694666922 file name=/u01/oracle/oradata/dg1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=694666922 file name=/u01/oracle/oradata/dg1/users01.dbf

contents of Memory Script:
{
set until scn 585861;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 12-AUG-09
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/oracle/dg1/1_4_694653014.dbf
archived log file name=/u01/oracle/dg1/1_4_694653014.dbf thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-AUG-09
Finished Duplicate Db at 12-AUG-09

Recovery Manager complete.

[oracle@Grid1 oradata]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 12 03:03:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect sys/sys2@dg1 as sysdba
Connected.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@Grid1 oradata]$ ps -ef|grep pmon
oracle 12811 1 0 02:57 ? 00:00:00 ora_pmon_dg
oracle 13649 1 0 03:00 ? 00:00:00 ora_pmon_dg1
oracle 15014 7640 0 03:03 pts/4 00:00:00 grep pmon
[oracle@Grid1 oradata]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 12 03:03:44 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> connect sys/sys2@dg1 as sysdba
Connected.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

Active DataGuard Process
------------------------

SQL> alter database open;

Database altered.

SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0



SQL> alter database close;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now


SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 5 0 0

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15


SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 13 1 2
ARCH CLOSING 1 14 1 2
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 12 1 267
MRP0 WAIT_FOR_LOG 1 15 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 15 3 2

9 rows selected.

SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE

SQL> select status from v$instance;

STATUS
------------
OPEN

From Node 1 -DG
---------------

SQL> select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- ---------- --------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE

SQL> select name from v$database;

NAME
---------
DG

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SQL> create table test2_dg as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.


From Node 2 - DG1
-----------------
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 13 1 2
ARCH CLOSING 1 14 1 2
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 12 1 267
MRP0 WAIT_FOR_LOG 1 15 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 15 3 2

9 rows selected.

SQL> desc test2_dg
ERROR:
ORA-04043: object test2_dg does not exist


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/dg1/
Oldest online log sequence 14
Next log sequence to archive 0
Current log sequence 16
SQL> desc test2_dg
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

Try to create some objects from DG1 - standby database
-----------------------------------------------------

SQL> create table tet as select * from dba_objects;
create table tet as select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

SQL> select DATABASE_ROLE, open_mode from v$database;

DATABASE_ROLE OPEN_MODE
---------------- ----------
PHYSICAL STANDBY READ ONLY

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- -------------- ----------- ------------
LNS ASYNC dest activation 1 1 100 99.95
LNS ASYNC end of log 5126 4961 500456 97.63
LNS wait on ATTACH 2 0 60 30.11
LNS wait on SENDREQ 179 0 240 1.34
LNS wait on DETACH 4 0 0 0
LNS wait on LGWR 2 0 0 0
LGWR-LNS wait on channel 539 539 602 1.12
LGWR wait for redo copy 104 0 2 .02

8 rows selected.


Hope this helps...

I will cover the Switchover and failover later.

Sunday, August 9, 2009

SAP ECC 6 and MSCS configuration (Active/Standby)- HA






Sample Picture - Single SAP system in the MSCS Cluster







6 Node cluster configuration - Windows 2003 Enterprise edition.
--------------------------------------------------------------

This is one of the SAP's recommended HA configuration.

Node1 - Oracle 10G installed - 6GB RAM,Dual Xeon 16 cores - Oracle Binaries in F Local Drive and Datafiles/logfiles/controlfiles in O Shared drive for Oracle Datafiles - Called O Drives 300GB
Node2 - Oracle 10G installed - 6GB RAM,Dual Xeon 16 cores - Oracle Binaries in F Local Drive and Datafiles/logfiles/controlfiles in O Shared drive for Oracle Datafiles - Called O Drives 300GB
Node1 - First MSCS node - Running SCS - 4GB RAM - Shared Drive for SAP mount point - Called S Drive - 100GB
Node2 - Second MSCS node - Standby SCS - 4GB RAM - Shared Drive for SAP mount point -
Called S Drive
Node3 - MS Active Directory - Primary server - 2GB RAM - Local Drive
Node4 - SAP First DI instance - Local Drive F
Node5 - SAP Second DI instance - Local Drive F

You can further enhance this HA architecture more protection.

Enqueue replication server for HA.
LBR to Load balance between available DI instances.
Oracle RAC for HA and scalling purpose.

SAP Services running on the First Node (SUBSCRIBE) and MSCS running on Second Node (ECC6)



Sample Picture - Multiple SAP systems in the MSCS Cluster

Minimum Requirments

1.Shared Drives for Oracle and SAP mount point
2.MSCS installed and fully functional.
3.MS AD installed and fully functional.
4.DNS configured for all hosts
5.Linkd installed and configured if Multiple SAP to be suppored in the MS Cluster.
SAP NetWeaver 2004s SR2 and higher ABAP+Java (kernel 7.00)used.
6.Oracle Fail safe - Optional


SAP services moving to the second Node



SAP services completely moved to the second Node




Steps

All nodes are already configured with Linkd shared mount points.
1.Install Oracle binaries on node1
2.Install SAP SCS on the First node- node2 - instance # 00.S Drive
3.Install ASCS on the First node - node2 - instance # 01. S Drive
3.SAP configuration prepare on the first SCS node - node2
4.Configure the second node for the MSCS - node3
5.Install Database instance - O Drive

Test the Fail over by moving MSCS groups from node2 to node 3. All the services SCS,ASCS and Oracle will be move to the second stand by node2.

6.Install the first DI instance on node5
7.Install the second DI instance on node6
8.Check the SAP connectivity using the two DI with SAP system on Primary (active) cluster.
9.Switch over the SAP system from node2 to node3.repeat the step 8.Since the Network name (say sapcluster) and IP also switches to node3 , there should not be any problem in connecting.
10.Configure the SAP logon group for the two DI instances
11.For more HA you can configure enqueue replication server.
12.Complete the TMS and other initial configurations
13.Backup the SAP instance using BRTOOLS tools

This completes the basic overview about the SAP Active/Passive cluster implementation.

Wednesday, August 5, 2009

SAP Performance Tuning

One needs to understand OS, Networking, Hardware, RDBMS and clustering to perform performance tunning of any ERP product. SAP provides very good transaction codes for monitoring and troubleshooting all these components. Familiarization of these components would help understand the transaction code description which in turn will help to manage SAP system more efficiently.

During the process of analyzing the root cause of performance issue, one has to make sure the CPU is not close to 100% usage, if

- CPU is not close to 100%, then any monitoring tools like Toad, foglight, and Grid control will help in resolving the performance issue. If CPU is 100%, the use of above tools will not help because

1. It is a very time consuming process or
2. CPU may block these tools from connecting

In that case, use of native tools like SQL plus, NIping, Netstat, sar, vmstat, mpstat, top, ps, iostat, msprot, ensmon, dbmon, msmon etc will help.

Even though Oracle provides some features like ADDM and SQL Tuning Advisor for Performance tuning, it would be better to understand the functionality of Optimizer technology.In real time ADDM and SQL Tuning Advisor helped me to tune optimize the performance of Database Batch jobs (which reduced the time from 14 hours to 3 hours).But further manual tuning (using MV and SQL hints) reduced the time to 30 min.

Certain Database (like in Tele industry) is very sensitive and need immediate attention in case of any critical performance issues.This situation can be related to table buffer/lock isse (SAP) or latch/lock/Bad queries (Oracle) or Disk IO queue issue (Operating system > 30%) or message server is not coming up in MSCS environment after the MSCS resource switchover (message server port not released from the old Active host , netstat -a) or MSCS cluster freezing at shared disk level (cluster level - quorom disks).

In general use the given sequence below to start with any troubleshooting issue.

Level 1 --> OS
Level 2 --> Network
Level 3 --> Oracle
Level 4 --> SAP

So root cause analysis involve from level 1 to leve 4 (may be more in case of more layers are used - example Cluster).

From my experience while at one client (Telecommunication) , there was a call drop during peak hours.I started troubleshooting from level 1 and found disk IOQ was > 25 %.At level 2 (Oracle) i found one user was rebulding a huge index and that caused the disk IO to peak. So i had to kill that session (dropping calls would drop the revenue) to bring the IO normal , < 10%. This fixed the call drop issue.Disk IOQ > 20-25% would be expensive for any sensitive databases.It can be ok (> 40%) for any other non sensitive databases.So never allow expensive operations during peak hours and monitor those operations always.

Few useful Transaction codes that helps to troubleshoot the Root cause performance issue
---------------------------------------------------

Level 1 (OS)
------------

AL11 - SAP Directories
OS02 - OS configuration
OS06/ST06 - OS monitor
OSS7 - SAPOSCOL Targets
SM50 - Process Overview

Level 2 (Network)
-----------------

OS01 - LAN Check via ping

Level 3 (Oracle)
---------------

DBA CockPit - Configuration and Maintenance (Wonderful Tcode)
DB01 - Oracle Lock monitor
DB02 - Database Performance
DB03 - Parameter changes at the database level
DB12 - Backup logs
DB17 - Database check condition
DBCO - Database connections
ST04N - Database Performance Monitor
DB20 - Table statistics
DB16 - Databse checks
DB14 - DBA log display

Level 4 (SAP)
------------

AL12 - Buffer monitoring
ST01 - System Trace
ST02 - SAP Memory Monitor
ST03N - System Load monitor
ST05 - Performance Analysis
ST07 - Application Monitor
ST10 - Table Access Statistics
SM50 - Process Overview
SM12 - Lock entries
SM59 - RFC Connections
SMICM - ICM Monitor
SMMS - Message server monitor
Finally CCMS

Hope this helps.

MS SQL Server 2008 Replication - HA

Very intresting topic.Will post soon...

MS SQL Server 2008 Mirroring - HA

Very intresting topic.Will post soon...

MS SQL Server 2008 - Log Shipping - HA

Very intresting topic.Will post soon...

Oracle 11G RMAN New Features

will post soon...

SAP EP 6 installation

Will post soon...

SAP Solution Manager - MSCS HA Configuration

This setup needs better understanding of SAP components. I was not able to publish this in detail step by step.But given a brief introduction and will provide more details if anyone needs.

Requirements -

1.Shared Disks - for SAP mount points (//sapcluster/sapmnt), Oracle Datafiles(including archive files,log files and control files) and Quorom Disks for MSCS cluster services.
2.MSCS Active Directory
3.MSCS DNS configuration
4.MSCS Cluster services
5.Oracle Fail Safe (Optional)
6.Oracle RAC (For better scalling and High availability for the Oracle Database)
Windows 2003 server



6 node configuration for HA setup - NO SPOF for the most of the SAP components

Configuration and Setup in Brief
--------------------------------
1.Node1 - Called Publish.Install and configure MSCS active Directory and DNS.
Domain called sapdomain.com.
2.Node2 - Called Subscribe.Will be part of sapdomain.com domain.This will be first node for the HA for SAP Components.Will be running SCS and ASCS.
3.Node3 - Called Witness.Will be the second node for SCS and ASCS.
4.Node4 - Called repeng.Will be running Replication Enqueue server.Shared Disk S will be mounted as NFS.
5.Node6 - Called Diag1.Will be running Diag SAP services.Installed on Local drive.
6.Node7 - Called Diag2.Will be running Diag SAP Services.Installed on Local Drive.


For more HA , you may separate the Oracle Database from the SAP node and use with RAC feature.Diag instances can be configured with SAP logon groups and may also used with LBR for Load balancing with HA.

Not complete...

Oracle Fail Safe - HA implementation

Will post soon....