Home » Posts tagged 'Oracle 9i'

Tag Archives: Oracle 9i

Rencana upgrade Oracle 9i (9.2.0.7) ke Oracle 11g (11.2.0.3)


Berbekal manual dokumentasi dari SAP (untuk upgrade Oracle 9i ke Oracle 10g dan upgrade Oracle 10g ke Oracle 11g) dan pengalaman melakukan upgrade Oracle 9i ke Oracle 11g di server testing AIX 5.3, akhirnya diputuskan bahwa gw disuruh melakukan upgrade di mesin Dev, QA, dan Production.

Rencana upgrade ini mesti didesain sungguh-sungguh dan penuh perhitungan karena menyangkut server production yang menjadi jantung sistem ERP SAP R/3 4.7 di company gw. Artinya gw mesti mempertaruhkan dan mengerahkan semua kemampuan dan knowledge gw pada project ini.

Perkiraan waktu untuk upgrade dilakukan pada minggu ke-2 atau ke-3 bulan Februari tahun 2012 ini.

Karena project ini sangat krusial, maka gw udah buru-buru membereskan pekerjaan instalasi server DEV dan QA SAP ERP 6.0 with EHP5 milik company yg lain (yg menjadi tanggung jawab gw juga). Gw mau full konsentrasi mempersiapkan tahapan upgrade ini.

Well, semoga project ini berjalan lancar.

Patching AIX 5.3 TL 3 to 5.3 TL 12


Berangkat dari kebutuhan untuk upgrade Oracle 9i (9.2.0.7) yang dipakai sekarang ke Oracle 11g (11.2.0.2), ternyata ada prerequisite di level OS yang perlu diperhatikan. Oracle 11g ini memerlukan AIX 5.3 minimal di level TL 9 SP 1 (AIX 5.3.9.1). Coba ubek-ubek ke fix central di IBM dan ketemu the latest fix pack (TL 12).

Hari Jumat lalu gw udah coba melakukan patch di server testing. So far lancar dan AIX telah naik ke level 5.3.12.1 (AIX 5.3 TL 12 SP 1). Server Dev & QA juga sudah dipatch. Demikian juga Server Apps 1 dan Apps 2.

Tinggal 2 server yang belum dipatch yaitu DB dan Apps 3 (pair hacmp).

Setelah ini tinggal menyiapkan tempat untuk ORACLE_HOME, kebutuhan stage (untuk installer), dll. Semoga pekerjaan bulan depan dapat berjalan lancar.

Add Supplemental Log Data pada database Oracle


Implementasi sistem BI (Business Intelligence) menuntut adanya akses ke dalam database Oracle SAP R/3 (sebagai ERP dan salah satu source informasi). Perubahan ini diperlukan oleh sistem ETL (Extract Transform Load) yang akan melakukan insialisasi data dan melakukan capture perubahan data.

ETL menggunakan aplikasi dari Informatica Data Replicator (dahulu disebut Wisdomforce DBSync). IDR menuntut settingan bahwa setiap perubahan dalam database akan di-log ke dalam file offline redo log. Perubahan itu adalah penambahan log data pada level database. Sebenarnya supplemental log data dapat dilakukan pada level tabel maupun database.

Berikut command sql yang :

SQL>alter database add supplemental log data;

Dengan command diatas diaktifkan maka setiap perubahan pada level database akan di-log kedalam file archive log (offline redo log). Konsekuensinya adalah penambahan file archive log akan lebih cepat dan lebih banyak dari sebelumnya (dari biasanya).

Layanan Upgrade Database Server Oracle


Inaroka Consulting memberikan layanan bagi instansi/kantor/perusahaan/pabrik yang menggunakan database Oracle (mulai Oracle 9i) untuk bisa melakukan upgrade.

Jenis Upgrade database Oracle yang dilayani :

– Upgrade dari Oracle 9i ke Oracle 10g

– Upgrade dari Oracle 9i ke Oracle 11g

– Upgarde dari Oracle 9i ke Oracle 11g

– Training Upgrade database Oracle

– Identifikasi dan Implementasi OLTP Compression (Table Compression dan Index Compression) pada Oracle 11g

Untuk harga layanan dan informasi lebih lanjut, silakan hubungi kami.

Inaroka Consulting
Perum Sukolilo Park Regency J-20
Keputih, Sukolilo, Surabaya
HP : 081235634212 (Bpk. Ardhian)
Email : devratt@yahoo.com

Oracle : Tuning PGA_AGGREGATE_TARGET in Oracle 9i


Tuning PGA_AGGREGATE_TARGET in Oracle 9i
Brian Peasland, Oracle Pipeline SYSOP
Abstract ‘
Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to help better manage session working areas in a session’s Program Global Area (PGA). This paper discusses available methods to help tune this new Oracle 9i parameter.
Introduction
Each Oracle session needs memory set aside for it to perform certain work operations. For instance, if the application requests a sorting operation by using certain SQL statements, like GROUP BY or ORDER BY, the application’s session can perform this sort in memory, provided enough memory has been reserved for that sort operation. If there is not enough reserved memory, then the sort operation is done in pieces using a temporary holding area on disk in the TEMP tablespace.
Before Oracle 9i, the DBA configured a session’s working areas by configuring a number of parameters such as BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, and SORT_AREA_SIZE. Collectively, these parameters are referred to as the *_AREA_SIZE parameters. The problem with this approach is that one often reserved memory that was never used. If I set SORT_AREA_SIZE=1M and HASH_AREA_SIZE=5M, then I have allocated at least 6MB of working area. What if I needed 2MB for sorting and I am not going to perform any hash joins? I have 5MB of allocated working area that is sitting there idle that I would like to use to assist my sort operation. The pre-9i Oracle DBA had to carefully determine how to allocate the best working area sizes so that optimal performance could be achieved, without using up too much of the server’s physical memory.
To address this type of situation, Oracle created a way to let the instance automatically manage the working areas of the database sessions. Oracle 9i now has the ability to reserve a large chunk of working area space in memory and to let the instance dynamically change the working area allocations depending on the session’s operations. One session that needs 1MB of sort space and 4MB of hash area space would fit well into an allocation as defined above. A second session that needs 4MB of sort space and 1MB of hash area space would also fit well into a similar allocation since Oracle 9i now has the capability to dynamically change these working area allocations depending on the usage. Oracle 9i uses the PGA_AGGREGATE_TARGET initialization parameter to define the total amount of PGA reserved memory.
Initial Setup
To begin using this new Oracle 9i feature, you need to set the PGA_AGGREGATE_TARGET initialization parameter. This parameter can be set without restarting the Oracle instance. As a guideline, Oracle recommends initially setting this parameter to 16% of your server’s physical memory for OLTP systems and 40% of your server’s physical memory for DSS systems. Like any other memory configuration guidelines from Oracle Corp, this is just a starting place. You will most likely want to tune this setting depending on the usage of your system’s resources. My server has 1GB of physical memory. Using the above guidelines, I will initially set my PGA_AGGREGATE_TARGET initialization parameter. You can see an example of this in Figure 1.
Figure 1. – Setting PGA_AGGREGATE_TARGET
ORA9I SQL> alter system set pga_aggregate_target=160M;
System altered.
ORA9I SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
———————————— ———– —————————
pga_aggregate_target                 big integer 167772160
With this parameter set, Oracle will now automatically perform dynamic working area memory management.
Please note that the amount of memory set aside for the PGA_AGGREGATE_TARGET is for all server processes, not for each server process. This parameter can be set to zero to turn off dynamic working area memory management. The acceptable range of values is between 10MB and 4096GB – 1.
Tuning PGA_AGGREGATE_TARGET
To illustrate how to tune this parameter, I will set my PGA_AGGREGATE_TARGET to the minimum value allowed, 10MB and run a load simulation on my instance. We want to determine when the PGA_AGGREGATE_TARGET is too low to give optimal performance and too high so as to not waste allocated memory.
Oracle 9i gives us many different views to query to see how well our dynamic working area memory management is performing. Oracle 9i has added a few statistics to V$SYSTAT and V$SESSTAT.
Figure 2. – V$SYSTAT with 10MB PGA Target
ORA9I SQL> select name,value from v$sysstat
2>  where name like ‘workarea executions%’;
NAME                                          VALUE
—————————————- ———-
workarea executions – optimal                   510
workarea executions – onepass                     1
workarea executions – multipass                   4
The query in Figure 2 shows us how many operations, or executions, were performed in the work areas. These executions fall into three categories. The optimal executions are those operations that were performed entirely in memory. As the name suggests, this is the most favorable type of execution. If the operation was too big to be performed in memory, then part of the operation spills onto disk. If only one pass was needed on disk, then this execution is noted in the onepass statistic. If more than one pass was needed on disk, then this execution is noted in the multipass statistic. Ideally, all executions should be in the optimal statistic and the statistics for onepass and multipass should be zero. From the query in Figure 2, I can see that my value for PGA_AGGREGATE_TARGET is too small.
Oracle 9i includes a new view called V$PGASTAT. This view can give you additional statistics on how well the dynamic working area memory management is performing.
Figure 3. V$PGASTAT with 10MB PGA Target
ORA9I SQL> select * from v$pgastat;
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter             10485760 bytes
aggregate PGA auto target                   4248576 bytes
global memory bound                          524288 bytes
total PGA inuse                             5760000 bytes
total PGA allocated                        10342400 bytes
maximum PGA allocated                      42925056 bytes
total freeable PGA memory                     65536 bytes
PGA memory freed back to OS                 9306112 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas          631808 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas        529408 bytes
over allocation count                          9201
bytes processed                            55100416 bytes
extra bytes read/written                  159971328 bytes
cache hit percentage                          25.61 percent
16 rows selected.
The first line of output in Figure 3 shows that my PGA_AGGREGATE_TARGET is currently set to 10MB. Some parts of the PGA are used for non-tunable information such as session context information and other overhead. The rest of the PGA memory footprint is dynamically tunable and is indicated by the aggregate PGA auto target statistic. The value in the second line of output of Figure 3 should not be significantly smaller than the value in the first line, as it is above. If this value is too small, then Oracle does not have enough memory to dynamically adjust.
Oracle 9.2 includes two additional rows of output to V$PGASTAT. They are over allocation count and cache hit percentage. If Oracle determines that it cannot honor the setting for PGA_AGGREGATE_TARGET, then it needs to allocate additional memory. The number of times Oracle detects this condition since instance startup is noted by the over allocation count statistic. Ideally, this value should be zero. The cache hit percentage statistic shows a hit ratio on the number of bytes where optimal executions were performed compared the total number of bytes for all executions, optimal, one-pass, and multi-pass. If all executions where optimal, then this statistic should be 100%.
It should be obvious from the queries in Figures 2 and 3 that the PGA_AGGREGATE_TARGET is under allocated. The question that remains is how much to increase this parameter to obtain optimal performance without wasting allocated memory? Oracle 9i includes a new V$PGA_TARGET_ADVICE view to help us answer this question. In order to use this view, one needs to ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. Let’s look at Figure 4 to see what advice Oracle can give us if we change our PGA_AGGREGATE_TARGET to a different value.
Figure 4. – V$PGA_TARGET_ADVICE with 10MB PGA Target
ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
2>              bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
3>              estd_pga_cache_hit_percentage as est_hit_pct,
4>              estd_overalloc_count as est_overalloc
5>        from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
————– ————— —————— ———– ————-
10        51766272           80197632          39             4
12        51766272           79773696          39             3
14        51766272           79773696          39             3
16        51766272           79773696          39             2
18        51766272           79773696          39             2
20        51766272           79773696          39             2
30        51766272           19943424          72             0
40        51766272           19943424          72             0
60        51766272           19943424          72             0
80        51766272           19943424          72             0
10 rows selected.
The output in Figure 4 shows us statistics that Oracle estimates would happen under our current workload if the PGA were changed to a different target size. Using this information, we should first attempt to reduce the estimated over allocations to zero. The first target size listed where this value is eliminated is 30MB. Notice that as we increase the target size, the estimated cache hit percentage increases, and the estimated number of bytes read and written in one-pass or multi-pass executions falls as well. Knowing that I need to increase this value, and looking at the target advice, I’m going to set PGA_AGGREGATE_TARGET to 80MB and restart the instance to clear all statistics. I’ll then rerun the same simulated load on the database.
I expect that I will still have some executions that will require reads and writes to disk since the read/write estimate show in Figure4 is non-zero for a 80MB PGA target. After restarting the instance and running the simulated load, I can see that this is true from the query in Figure 5.
Figure 5. – V$SYSTAT with 80MB PGA Target
ORA9I SQL> select name,value from v$sysstat
2> where name like ‘workarea executions%’;
NAME                                          VALUE
—————————————- ———-
workarea executions – optimal                   511
workarea executions – onepass                     4
workarea executions – multipass                   0
I have eliminated the multi-pass executions, but a few one-pass executions remain. Let’s now check the results in V$PGASTAT show in Figure 6.
Figure 6. – V$PGASTAT with 80MB PGA Target
ORA9I SQL> select * from v$pgastat;
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter             83886080 bytes
aggregate PGA auto target                  70290432 bytes
global memory bound                         4194304 bytes
total PGA inuse                             5790720 bytes
total PGA allocated                        10792960 bytes
maximum PGA allocated                      15750144 bytes
total freeable PGA memory                    196608 bytes
PGA memory freed back to OS                11403264 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas         4319232 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                             0
bytes processed                            47950848 bytes
extra bytes read/written                   39481344 bytes
cache hit percentage                          54.84 percent
We can see in Figure 6 that we have eliminated the over allocation count statistic. The auto target statistic is very close to the target parameter. So we are getting closer. But the cache hit percentage is still far away from 100% and there are a large number of extra bytes read and written. We will look for advice for our next setting in Figure 7.
Figure 7. V$PGA_TARGET_ADVICE with 80MB PGA Target
ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
2  bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
3  estd_pga_cache_hit_percentage as est_hit_pct,
4  estd_overalloc_count as est_overalloc
5  from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
————– ————— —————— ———– ————-
10        45456384           84205568          35             2
20        45456384           78962688          37             0
40        45456384           19740672          70             0
60        45456384           19740672          70             0
80        45456384           19740672          70             0
96        45456384           19740672          70             0
112        45456384           19740672          70             0
128        45456384           13095936          78             0
144        45456384           13095936          78             0
160        45456384           13095936          78             0
240        45456384           13095936          78             0
320        45456384                  0         100             0
480        45456384                  0         100             0
640        45456384                  0         100             0
The query output in Figure 7 shows that we can eliminate the extra read and write bytes if we allocate 320MB to the PGA_AGGREGATE_TARGET. We can also hit our 100% cache hit ratio. We can further see that allocating 640MB or even 480MB of memory would not help us achieve any better performance. These settings would waste memory. Our next step would be to change the PGA_AGGREGATE_TARGET parameter and then perform the same queries at regular intervals to ensure that we are achieving optimal performance.
Conclusion
The benefit of letting Oracle 9i dynamically manage your working area memory is a great tool for the DBA to employ. The DBA does not have to worry about setting each of the *_AREA_SIZE parameters correctly. New statistics to V$SYSTAT and the new V$PGASTAT and V$PGA_TARGET_ADVICE views assist the DBA in determining if this parameter is not set correctly and the best setting for the PGA_AGGREGATE_TARGET parameter.
References

Oracle 9i Database Reference
Metalink Note: 148346.1 Oracle 9i Monitoring Automated SQL Execution Memory Management
Metalink Note: 223730.1 Automatic PGA Memory Management in 9i

Pengen mengambil ujian sertifikasi OCA and OCP


Sebenarnya sudah lama berkutat dengan Oracle. Mulai dari kenal Oracle 8i dari jaman kuliah (sekitar tahun 1999) sampe mulai kerja dan berhadapan langsung dengan Oracle 9i (database versi enterprise in the real world dan digunakan sebagai database server ERP SAP R/3 4.7), kemudian bersentuhan dengan database Oracle 10g (juga digunakan sebagai database server SAP ERP 6.0 SR3) dan yang terakhir adalah Oracle 11g (tepatnya Oracle 11.2.0.2) pada waktu testing upgrade database SAP. Pengalaman selama menangani database server ini sedikit banyak ikut membentuk experience gw untuk lebih berkutat bagaimana menangani database Oracle, melakukan tuning performance, troubleshooting, setting dan konfigurasi Oracle Data Guard, dan masih banyak lagi experience yang gw dapet.

Lama-lama pengen juga untuk ikut ujian sertifikasi. Sebelumnya gw menganggap bahwa sertifikasi kurang penting (he..he..karena gw gak punya duit untuk ikut training dan ujiannya sich…) namun setelah sempat baca-baca dan kenal beberapa orang di forum-forum database dan SAP, pandangan itu sedikit berubah. Sepertinya sertifikasi ini penting bagi gw untuk lebih meningkatkan kemampuan dan nilai jual gw di perusahaan maupun di value gw sebagai seorang freelancer. Beberapa kali gw ikut dalam project-project freelance dan berhadapan dengan database Oracle.

Dari beberapa situs dan pencarian di Google, gw dapat link berikut mengenai sertifikasi dan ujian OCA (Oracle Certified Associate) dan OCP (Oracle Certified Professional) –> http://rohmad.net/2008/07/21/strategi-mengambil-training-database-oracle/ dan http://rohmad.net/2008/04/15/ujian-oca-dan-ocp-database-oracle-10g/.

SAP R/3 4.7 using Oracle 10.2.0.4


Dalam 2 hari ini, akhirnya berhasil untuk upgrade database Oracle dari versi 9.2.0.7 menjadi 10.2.0.4 alias Oracle 10g.

Proses upgrade lumayan memakan perhatian karena banyak step-step yang rumit. Proses upgrade berjalan dengan mulus dan tidak menyentuh ke datafile. Jadi bisa dikatakan hampir tidak ada hubungannya dengan size database. Satu-satunya hal yang berhubungan dengan size database adalah proses untuk update statistics. Namun, hal ini bisa dilakukan secara online dan sebenarnya bukan bagian integral dari proses upgrade itu sendiri karena secara database sudah naik versi ke 10.2.0.4.

Beberapa problem dan hal yang perlu diperhatikan adalah :

  • profile-profile user perlu disesuaikan setelah versi Oracle naik (parameter ORACLE_HOME)
  • File listener.ora, sqlnet.ora, dan tnsnames.ora tinggal disalin dari disesuaikan. Catatan : port listener (entah kenapa) tidak bisa memakai 1527 (seperti standar SAP), akhirnya pake port 1521 (standar Oracle). parameter SDU juga dihilangkan
  • Pastikan update profile-profile SAP
  • Pastikan SAP bisa terkoneksi ke database Oracle menggunakan command R3trans -d. Baca log R3trans (file /home/[sid]adm/trans.log) jika terjadi error.

Saat ini gw sedang mencoba untuk melakukan proses index compression berbekal Oracle 10g dan BRTOOLS versi 7.20 SP 13.

Semoga berhasil.

Moral Story : Proses upgrade Oracle 9i ke 10g tidak rumit, mostly mulus.

Next Project : Upgrade dari Oracle 10g ke Oracle 11g

Preparing for busy week…


Dalam beberapa minggu ke depan kayaknya bakalan banyak pekerjaan yang mesti diselesaikan. Ada pekerjaan pindah fisik server SAP di Gresik, tugas kantor ke Jakarta (tentative) untuk training aplikasi SCM, yaitu JDA Manugistics, dan beberapa pekerjaan-pekerjaan lain.

Problem minggu ini sudah dimulai. Server yang dipakai untuk JDA Manugistics problem di bagian fisik hardware. Ada hardware yang failure.

Pekerjaan yang akan dilakukan minggu ini adalah mencoba upgrade database Oracle dari 9i ke Oracle 10g.

Pengen mencicipi database IBM DB2


Kebetulan di kantor sedang ada wacana untuk melakukan migrate database ERP dari Oracle 9i ke IBM DB2. Rencananya sih dipakai IBM DB2 versi terbaru (the latest one) 9.5. Versi ERP yang digunakan di kantor juga support untuk penggunaan versi IBM DB2 terbaru, tidak seperti Oracle yang digunakan saat ini. Versi ERP yang digunakan hanya support untuk Oracle 9i, tidak bisa move ke Oracle 10g.

Nah, sebelum pindah ke IBM DB2 di ERP system, gw mau mencicipi nya sendiri, he..he..kayaknya layak dicoba deh. Kebetulan pas presentasi orang IBM kemaren, keliatan banget fitur-fitur IBM DB2 yang keren. Bisa HA (High Availability) dan DR (Disaster Recovery). Malah udah menyertakan license buat backup Tivoli juga, free. Keren kayaknya.

Rencana IBM DB2 akan gw install di virtual machine dulu, buat liat fiturnya aja. Atau gw sekalian deh install versi IDESnya, kali aja menarik.

Perubahan konfigurasi untuk ODG


Agar bisa dijalankan Oracle Data Guard ada beberapa perubahan yang mesti dilakukan baik di Primary Database maupun Standby Database. Perubahan terutama dilakukan pada beberapa file berikut :

  • listener.ora
  • tnsnames.ora
  • sqlnet.ora
  • init.ora atau pfile atau spfile (tergantung database pake apa)

Setelah perubahan dilakukan, dilakukan transfer datafile dari primary ke standby agar posisi datafile konsisten. Apabila database standby pernah di-open maka ini menjadi wajib dilakukan. Karena kalau tidak dilakukan maka standby database akan dianggap tidak konsisten. Control file di standby database juga perlu di-create dengan posisi standby dari control file milik primary database.

Untuk sisi SAP di primary database tidak ada perubahan. Perubahan dilakukan di environment SAP di sisi standby database. Perubahan yang perlu adalah mengubah dbs_ora_tnsname menjadi service name standby (lihat di tnsnames.ora dan atau listener.ora yang baru). Baru SAP instance di standby server bisa dijalankan. Jangan lupa soal saplicense yah…