Friday, December 19, 2008
backup as compressed backupset format '/[mount_point]/[backup_name]__%Y-%M-%D-%t_s%s_p%p' database plus archivelog not backed up delete all input;
Fast incremental startegy by having the last backup full recovered every day (10g and up)
backup incremental level 1 cumulative for recover of copy with tag 'FULL_DB'device type disk filesperset = 10 tag 'INC_DB' database;
recover copy of database with tag 'FULL_DB';
backup filesperset = 25 tag 'ARCH_BACKUP' archivelog all not backed up delete all input;
Wednesday, December 17, 2008
When you use execute immediate for dml statements then you must explicity commit.
But when you use execute immediate for ddl statements then all previous dnml statements in a transaction are explicity commited.
Friday, December 12, 2008
select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual;
Just follow the links:
for examples -> http://asktom.oracle.com/tkyte/hexdec/index.html
for the code -> http://asktom.oracle.com/tkyte/hexdec/hexdec.sqlUsing the above functions you can do right shifting
select lpad( to_bin( 3123 ), 15, '0' ) bin1,
lpad( to_bin( trunc(3123/power(2,(rownum-1))) ), 15, '0' ) bin2,
where rownum <= 10;
Thursday, December 4, 2008
AIX 5.2 VMM Parameters Tunning
vmtune -c8 -s1 -r32 -R64 -p5 -P20 -t20 -f128 -F144 -W32
setting maxfree to 144
Setting minfree to 128
Setting minperm% to 5
Setting maxperm% to 20
Setting maxclient% to 20
Setting minpgahead to 32
Setting maxpgahead to 64
Setting maxrandwrt to 32
Setting numclust to 8
Setting sync_release_ilock to 1
async io servers : maxservers = 10 * CPU, minservers=maxservers/2
The above parameters are a good starting point. You can tune a little bit by monitoring performance.
If your database is an OLTP system , using lots of db_sequential_reads (index scans) you can put minpgahead to 512 and maxpgahead to 1024 on JFS and JFS2 volume groups.
For async io you can use the disks rule of thumb: maxservers=10*[total_disks_for_oracle_files],minservers=maxservers/2
On AIX 5.3 there is a slight diference in the way the VMM works so you can use this settings:
vmo -r -o lgpg_size=16777216 lgpg_regions=128
vmo -p -o minperm%=5
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o minfree=960
vmo -p -o maxfree=1088
For async io as for AIX 5.2.
Oracle Parameters for 9iR2(18.104.22.168) and up :
filesystem_options=SETALL Enables both ASYNC and DIRECT I/O or CONCURRENT I/O(JFS2)
It is advisable not to use direct I/O because degrades performance. If you have only JFS filesystems then use filesystem_options=ASYNC which is the default setting.
There is no "built-in" support for CIO or DIO in Oracle Database 9iR2 (9.2.0) or lower, though you can force the use of CIO (JFS2) or DIO (JFS) with filesystem mount options.