Home » Posts tagged 'Oracle'

Tag Archives: Oracle

Un-officially SAP OSDB Migration Project done !!


Another project have been completed by me this year.

Since the sister company (formerly I maintain) moved to hyperconverged Nutanix and used Acropolis as its hypervisor, there is a SAP R / 3 4.7 ext 200 system running on HP rx4640 machine with HPUX 11.23 OS and Oracle 9.2 database server that must be migrated to a Nutanix machine . HP rx4640 machine is not extended maintenance support. While the system, SAP R / 3 4.7 ext 200 is also not supported by SAP both SAP instance and database server.

Target using Linux OS SLES 11 SP4 and DB2 database server 9.7 FP 5.

Broadly speaking the migration process is :

    – Export existing data in SAP R / 3 4.7 ext 200 on HPUX source machine
Install DB2 9.7 database server on target Linux
Install Central Instance on Linux target
Install Database instance in Linux target by selecting System Copy and export result (process no.1) as imported data.

The export process runs smoothly. Problems begin to emerge during process no.4 of them:

The process of database load had stopped because the file system is full sapdata. Solution: extend the sapdata partition and the file system
The database load process was stopped again because the file system is a full archive log. Solution: extend the partition and add the number of LOGARCHIVE second files.

After the import process is complete, there are still more problems that arise, the SAP kernel 6.40 used does not recognize the linux 3.0 kernel (used SLES 11 SP 4) and DB2 9.7 database. The solution is download the latest version of SAP kernel 6.40 EX2 patch (version 414).

Project un-official SAP OSDB migration done by un-certified Base like me

Advertisements

video installation SAP NetWeaver 7.4 using Oracle 12c on SuSE Linux Enterprise Server 11 SP 4


This video show you on step by step installation of SAP NetWeaver 7.4 using  Oracle 12c as database server on SuSE Linux Enterprise Server 11 SP 4.

Preparation :

Installation part 1 :

Installation part 2 :

Installation part 3 :

Installation part 4 :

Enjoy !!

Oracle 11g Extended Statistics for SAP Tables


Extended Statistics is an attempt to fix one of the flaws in CBO–values of different columns are not correlated.

Let us take an example of two columns in a table. One of the column contain department code and the other contains employee name. Let us assume that there are 10 departments and 3000 employees in our example. In a real life scenario, all the employees do not belong to all the departments, but CBO assumes that is the case and hence it assumes that there are 3000*10 = 30000 combinations of employee name and department code that exist. In reality, it can be between 3000 and 30000 (assuming employee belongs to at least one department and can clock for multiple departments).
(more…)

How can we calculate archivelog size each day?


How can we calculate archivelog size each day/hour?
This is not a difficult idea for someone who work as DBA, I just wish more idea for discussion and sharing . Some Idea using V$LOG.BYTES and V$ARCHIVED_LOG.*
but I believe V$ARCHIVED_LOG view be able to help:
Archivelog size each day:

SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

TIME SIZE_MB
———- ———-
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
2011-02-16 73959.86
2011-02-17 69969.71
2011-02-18 74677.10
2011-02-19 75474.95
2011-02-20 77967.07
2011-02-21 67802.70

Archivelog size each hour:

SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24’;

Session altered. (more…)

BRTOOLS 720 (for Oracle 10g and 11g)


You can download

Brtools 7.20 is available on the servicemarket place at the following link

http://service.sap.com/swdc

Download
Support Packages and Patches – Entry by Application Group
Additional Components
SAP Kernel
SAP Kernel 64bit
Choose your OS
Choose Oracle

DBATL720O10_1-20005243.SAR DBATOOLS Package for Oracle 10g and 11g

The oss note 12741 is also useful for download paths for all released Brtools.

How to find indexes which are candidate for rebuild?


I know it’s a very arguable question, but let me put my words and then you can comment.

I follow different approaches to find out indexes for rebuild

– Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3

– Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First “Analyze the index with validate structure option” and then,

SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

But (a big but), the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.

See this example:

SQL> analyze index TEST_INDX validate structure; — First analyze the suspect index

Index analyzed.

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
———— ———- ———- ———– ————-
TEST_INDX 8 938752 29575 73342

You can see height of the index is 8 and also high number of DEL_LF_ROWS

SQL> set autotrace on
SQL> set timing on
SQL>
SQL> select count(*) from TEST_TABLE where TEST_COL like ‘http://www.hots%’;

COUNT(*)
———-
39700
Elapsed: 00:00:27.25

Execution Plan
———————————————————-
Plan hash value: 870163320

Id Operation Name Rows Bytes Cost (%CPU) Time

0 SELECT STATEMENT 1 117 10 (0) 00:00:01

1 SORT AGGREGATE 1 117

*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01

Statistics
———————————————————-
1 recursive calls
0 db block gets
764 consistent gets
757 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now you rebuild the indexes

SQL> alter index TEST_INDX rebuild;

Index altered.

SQL> select count(*) from TEST_TABLE where TEST_COL like ‘http://www.hots%’;

COUNT(*)
———-
39700

Elapsed: 00:00:06.18

Execution Plan
———————————————————-
Plan hash value: 870163320 – See here although it is using the same plan but still it is faster

Id Operation Name Rows Bytes Cost (%CPU) Time

———————————————————————————-

0 SELECT STATEMENT 1 117 6 (0) 00:00:01

1 SORT AGGREGATE 1 117

* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01

Statistics
———————————————————-
15 recursive calls
0 db block gets
592 consistent gets
588 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
—————————— ———- ———- ———- ———–
TEST_INDX 4 865410 15434 0

This clearly indicates rebuild helped my query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0

Now coming to second of part of Harvinder’s comment.

Possible ways of Rebuilding.

– Online/Offline.

ONLINE Rebuild (8i onwards)

SQL> Alter index rebuild online;

This allows parallel DML to go on while Index is getting rebuild. Remember, online index requires more space, as it creates a new index before dropping the old one.

Index Rebuild is primarily a 3 step process

Prepare phase: Oracle locks the table for a fraction of second (actually not felt) to build index structure and populate data-dictionary.

Build phase: Most of the work is done in this phase. Oracle engine populates the index using the table and allows parallel DML’s, parallel DML’s uses a temporary journal table (b tree index like structure) to host the entries while the new index is getting populated.

Merge phase: Now the final stage, Oracle merges the new index with the journal table and drops the old index. Even during the merge phase, any changes to the table are recorded in the journal table and they get merged towards end of this phase.

9i onwards, online index rebuild feature includes Reverse Key, Function Based and Key Compressed indexes.

Offline Index rebuild.

SQL> Alter index rebuild;

This is conventional rebuild which was used(still available) before 8i. In this rebuild process, oracle drops the old index and creates a new one. In this process, no extra space is required, but parallel dml’s are not supported.