Tuesday, September 29, 2009

How to set db_writer_processes

alter system set dbwr_io_slaves = 0 scope = spfile ;
alter system set db_writer_processes = 4 scope = spfile ;

Oracle có 2 cách để tăng tốc ghi dirty block từ Data Buffer Cache xuống Datafiles:
-Tăng số thread: DBWR_IO_SLAVES
-Tăng số proccess: DB_WRITER_PROCESSES

Tại 1 thời điểm, chúng ta chỉ có thể dùng 1 trong 2 cách mà thôi.
Trong trường hợp máy bạn có 4 CPU, thì tốt nhất là tăng số Writer, lưu ý là phải set lại DBWR_IO_SLAVES=0, thông thường số writer sẽ <= số CPU.

Tuning Database

Disabling Dispatchers

Use the following to disable dispatchers:

  1. Comment the dispatchers line in the init file:

    #dispatchers="(PROTOCOL=TCP)"

  2. Create spfile from pfile.

Setting Parameters

Set the following parameters:

  1. Set the processes parameter to 750:

    alter system set processes=750 scope=spfile;

  2. Set db_cache_size to 1G:

    alter system set db_cache_size=1G scope=spfile;

  3. Set undo_retention to 1800:

    alter system set undo_retention=1800 scope=spfile;

  4. Set db_writer_processes to 4:

    alter system set db_writer_processes=4 scope=spfile;

  5. Increase the size of redo log files to 300M:

    startup open restrict
    alter database add logfile group 5 '//oradata/data03/redo05.dbf' size 300M;
    alter database add logfile group 6 '//oradata/data03/redo06.dbf' size 300M;
    alter database add logfile group 7 '//oradata/data03/redo07.dbf' size 300M;
    alter database add logfile group 8 '//oradata/data03/redo08.dbf' size 300M;
    select group#, bytes, status from v$log /* to find the status of redo logfile groups */;
    alter system switch logfile
    /* perform this step till group 1, 2, 3 and 4 become inactive */;
    alter database drop logfile group 1;
    alter database drop logfile group 2;
    alter database drop logfile group 3;
    alter database drop logfile group 4;

Monday, September 28, 2009

View Processor Speed and RAM Size --- Solaris

Today I had a request for processor speeds, clock, RAM size, and the type of framebuffer for a Sun Blade 2000 workstation. I used the prtdiag command to get this information.

Here is the path.

# /usr/platform/sun4u/sbin/prtdiag -v

If you want the CPU speed(s) only:
# psrinfo -v

If you want RAM only:
# prtconf | grep Memory

Saturday, September 26, 2009

Installing iTunes on Windows Server 2003

iTunes windows_server_2003

Instructions Part 1

  • Download and save iTunesSetup.exe from here to a folder
  • Download and install WinRAR from here. This is required to dissect the .exe file into the component .msi files
  • After installing WinRAR, locate the iTunesSetup.exe file, right-click on it, hover over WinRAR, and click Extract to iTunesSetup\
  • You should then see a folder called iTunesSetup
  • Inside this you should see a number of .msi files perhaps including: AppleMobileDeviceSupport.msi, AppleSoftwareUpdate.msi, Bonjour.msi, iTunes.msi, MobileMe.msi, QuickTime.msi, SetupAdmin.exe

You have now downloaded the iTunes software. The problem we have now is that if we now try and install any one of these packages (.msi’s), we get operating system incompatibility warnings. This is because Apple have coded into their installer programs that their software is only to be installed on certain operating systems to prevent possible incompatibilities.

Therefore we need to remove these operating system options from the .msi files using a Microsoft SDK application.

Instructions Part 2

  • Download the Windows SDK for Windows Server 2008 and .NET Framework 3.5 from here
  • In the installation do not download and install every option (1.5GB +). Only select the W32 Components (15MB ish).
  • Once this has downloaded and installed successfully, we then need to browse into the SDK’s bin folder in a directory similar to this: C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin
  • In there you will see a file called Orca.msi. Install this.

Instructions Final Part

  • Run Orca from the Start Menu
  • Now open each one of the Apple .msi files from Orca in turn
  • In the left hand section, scroll down to the LaunchCondition table.
  • On the right hand side, you will notice information relating to the operating system. E.g. for the AppleMobileDeviceSupport.msi file I see (VersionNT=501 And ServicePackLevel>=2) Or VersionNT>=600 and its description: [ProductName] requires that your computer is running Windows XP SP2 or newer.
    For iTunes.msi I see: ((VersionNT=501 AND ServicePackLevel>=2) OR VersionNT>501
    Basically delete any row under the LaunchCondition table that contains “VersionNT”, “ServicePackLevel” etc…
  • This is the information that needs removing, so click on the row, and press delete on your keyboard.
  • Once deleted, click save from the top menu.
  • Now, open each .msi file, browser to the LaunchCondition table on the left, and delete any information relating to the operating system, then click save, untill all .msi files have been modified.

Once you have modified each .msi file, you can then begin the installation of which every program you want installing. For instance, if this was iTunes, then double-click and install iTunes.msi.

Hopefully this has worked for you. If not, or you want further help, please contact me using the comments form below. Good luck!

------------------------------------

Fix error

  1. Before installing iTunes 9, and whilst you are still using iTunes 8, shut down iTunes, and all iTunes related services. The easiest way of doing this is to open the Windows Task Manager and ending the processes of every iTunes program/ service. iTunes.exe, iTunesHelper.exe, iPodService.exe.
  2. Navigate to C:\Program Files\iPod\. In here COPY all files to a temporary directory called v8. You will need to copy iPodService.exe file and iPodService.Resources folder.
  3. Following my previous Installing iTunes on Windows Server 2003 post, modify the .msi files to allow the installer to run on a Windows Server.
  4. Run the installer for iTunes 9.
  5. When the installer fails, relating to the iPodService problem, navigate to C:\Program Files\iPod\, CUT the iPodService.exe and iPodService.Resources items and paste them in a folder called v9.
  6. Now open the v8 folder, COPY all the files, iPodService.exe and iPodService.Resources into the root of the C:\Program Files\iPod folder. (Replace the v9 files if you didn’t CUT them.)
  7. Now click retry in the installer.
  8. Installer finishes.
  9. iTunes 9 is installed!

iPod_iTunes_9_Windows_server_2003

Known potential problems

  1. As far as I am aware this fix does not allow iPod/ iPhone connectivity to this computer. This is because the v8 version is incompatible with the v9. I could be wrong though?

More Help

If you encounter any sort of problems with this fix, please use the comment system below, and I will try to reply and help you. I cannot make any promises. Please give a valid email address so that I can reply to you.

Thursday, September 17, 2009

ORA-14402: updating partition key column would cause a partition change

I am updating the table based on the leading partition key and i got the error message.

ORA-14402: updating partition key column would cause a partition change

Again i changed the table as below.

alter table inventory ENABLE ROW MOVEMENT;

Now it works fine. I have two questions.

1. When we update the partition key, does oracle really move the data to different partition or it deletes the record in current partition and insert into another partition?.

2. Is it overhead when we update parition key? Any extra cost?

Wednesday, September 16, 2009

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-01555:

snapshot too old: rollback segment number string with name "string" too small
Cause: rollback records needed by a reader for consistent read are overwritten by other writers
Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

If you're using manual UNDO Mode or use an Oracle version <9i,>

select rb.segment_name, rs.optsize, rs.hwmsize
from v$rollstat rs, dba_rollback_segs rb
where rs.usn = rb.segment_id

To change the optimal size you can issue following select:

alter rollback segment rbs0 storage (optimal 100m);
Pay attention that the size you specify for optimal will be constantly allocated inside the tablepace.
If you are using automatic UNDO mode, you can try to increase the value of undo_retention.

alter system set undo_retention=10800;

The value of undo_retention is the time in seconds the database tries to keep the undo blocks (not mark as being free) inside the undo tablespace.

Friday, September 11, 2009

Remove the obsolete parameter in SPFILE!

Message:
Code:
ORA-32004: obsolete and/or deprecated parameter(s) specified
Khi chúng ta set lại một số các parameter của DB trong SPFILE, giả sử switch Database sang trạng thái Archive..
Code:
SQL>Shutdown immediate
....
Instance shutdown.
SQL> Startup mount
....
Oracle Instance mounted.
SQL> Alter database archivelog;
Database archived.
SQL> Alter system set log_archive_dest='D:\Archive' scope=spfile;
System altered.
SQL> Alter system set log_archive_start=TRUE scope=spfile;
System altered.
SQL> Alter database open;
Database altered.
....

SQL>Shutdown immediate
....
Instance shutdown.
SQL> Startup mount
....
[b]ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.[/b]
Total System Global Area 524288000 bytes
Fixed Size 1249920 bytes
Variable Size 146804096 bytes
Database Buffers 369098752 bytes
Redo Buffers 7135232 bytes
Database mounted.
Hãy giải quyết dòng message đó. Rất đơn giản.

Code:

SQL> alter system reset log_archive_start scope=spfile sid='*';


System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1249920 bytes
Variable Size 155192704 bytes
Database Buffers 360710144 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

Monday, September 7, 2009

ORA-01502 Oracle Index in Unusable State

ORA-01502: index ’string.string’ or partition of such index is in unusable state

The error indicates an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations. This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace, all rows will still be loaded and imported, but the indices are left with STATUS = ‘INVALID’.

Invalid indexes can be checked with a

SELECT * from USER_INDEXES WHERE STATUS = ‘INVALID’;

SQL statement.

Solution to this error is simple. You can:

  1. Drop the specified index and/or recreate the index
  2. Rebuild the specified index
  3. Rebuild the unusable index partition

Generally, the following SQL manipulation language will be able to rebuild the unusable index:

ALTER INDEX index_name REBUILD

Thursday, September 3, 2009

Please wait 15 minutes before trying again

You can't do that without hacking. You could just turn off this option:

Admin CP -> vBulletin Options -> General Settings -> Use Login "Strikes" System -> No