oracle redo log file sizing?

- whats the ideal size.
- what if log switch is occurring too often?
- getting message checkpoint not complete.
- getting message thread 1 cannot allocate new log , sequence ###

Oracle 11g 11.1 install on ubuntu 9.10 (both 64 bit)

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install gcc make binutils libaio1 gawk ksh libc6-dev rpm libmotif3 alien lsb-rpm libtool

sudo addgroup oinstall
sudo addgroup dba
sudo addgroup nobody
sudo usermod -g nobody nobody
sudo mkdir /home/oracle
sudo useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
sudo passwd -l oracle
sudo chown -R oracle:oinstall /home/oracle
sudo ln -s /usr/bin/awk /bin/awk
sudo ln -s /usr/bin/rpm /bin/rpm
sudo ln -s /usr/bin/basename /bin/basename
sudo mkdir /etc/rc.d
for i in 0 1 2 3 4 5 6 S ; do sudo ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
sudo mkdir -p /u01/app/oracle
sudo chown -R oracle:oinstall /u01

The orginal kernel parameters can be found by running the following.

sudo  /sbin/sysctl -a | grep kernel.sem
sudo  /sbin/sysctl -a | grep kernel.shm
sudo  /sbin/sysctl -a | grep file-max
sudo  /sbin/sysctl -a | grep ip_local_port_range
sudo  /sbin/sysctl -a | grep rmem_default
sudo  /sbin/sysctl -a | grep rmem_max
sudo  /sbin/sysctl -a | grep wmem_default
sudo  /sbin/sysctl -a | grep wmem_max

The output of the above command returns

kernel.sem = 250    32000    32    128
kernel.shmmax = 33554432
kernel.shmall = 2097152
kernel.shmmni = 4096
fs.file-max = 794920
net.ipv4.ip_local_port_range = 32768    61000
net.core.rmem_default = 124928
net.core.rmem_max = 131071
net.core.wmem_default = 124928
net.core.wmem_max = 131071



change the kernel parameters if the setting found above is lower than the recommendation given in the following table.

shmmni	4096
shmall	2097152
shmmax	2147483648
semmsl	250
semmns	32000
semopm	100
semmni	128
file-max	65536
ip_local_port_range	1024 - 65000
rmem_default	1048576
rmem_max	1048576
wmem_default	262144
wmem_max	262144

sudo cat >> /etc/sysctl.conf << EOF
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
EOF

sudo cat >> /etc/security/limits.conf << EOF

oracle soft nproc  2047

oracle hard nproc  16384

oracle soft nofile 1024

oracle hard nofile 65536EOF

sudo cat >> /etc/pam.d/login << EOF
session required /lib/security/pam_limits.so
session required pam_limits.so
EOF

sudo sysctl -p

sudo cat >> /etc/profile << EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$PATH:/u01/app/oracle/product/11.1.0/db_1/bin
export ORACLE_SID=cert11
EOF

sudo cat >> /etc/init.d/oracledb << EOF
#!/bin/bash
#
# /etc/init.d/oracledb
#
# Run-level Startup script for the Oracle Listener and Instances
# It relies on the information on /etc/oratab

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
echo "Oracle startup: cannot start"
exit 1
fi

case "$1" in
start)
# Oracle listener and instance startup
echo -n "Starting Oracle: "
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart"
touch /var/lock/oracle
echo "OK"
;;
stop)
# Oracle listener and instance shutdown
echo -n "Shutdown Oracle: "
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
su $ORACLE_OWNR -c $ORACLE_HOME/bin/dbshut
rm -f /var/lock/oracle
echo "OK"
;;
reload|restart)
$0 stop
$0 start
;;
*)
echo "Usage: `basename $0` start|stop|restart|reload"
exit 1
esac

exit 0
EOF


sudo chmod a+x /etc/init.d/oracledb

cd /etc/init.d

sudo update-rc.d oracledb defaults 99


Edit .bash_profile and add the following
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

install oracle software only

./runInstaller -ignoreSysPrereqs


then run dbca and name the database (cert11) in my case.

edit /etc/oratab and set the last field to 'Y' to start oracle automatically at boot.

Making flash player work in Ubuntu 9.10 64 bit

sudo apt-get remove --purge flashplugin-nonfree gnash gnash-common mozilla-plugin-gnash
sudo apt-get remove --purge swfdec-mozilla libflashsupport nspluginwrapper iceape-flashplugin
sudo apt-get remove --purge iceweasel-flashplugin mozilla-flashplugin firefox-flashplugin
sudo apt-get remove --purge xulrunner-flashplugin midbrowser-flashplugin xulrunner-addons-flashplugin
sudo rm -f /usr/lib/mozilla/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/iceweasel/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/firefox/plugins/npwrapper.libflashplayer.so
sudo rm -f /var/lib/flashplugin-nonfree/npwrapper.libflashplayer.so
sudo rm -f ~/.mozilla/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/firefox-addons/plugins/npwrapper.libflashplayer.so
sudo rm -f ~/.mozilla/plugins/*flash*
sudo rm -f /usr/lib/firefox/plugins/*flash*
sudo rm -f /usr/lib/firefox-addons/plugins/*flash*
sudo rm -f /usr/lib/mozilla/plugins/*flash*





sudo wget http://download.macromedia.com/pub/labs/flashplayer10/libflashplayer-10.0.45.2.linux-x86_64.so.tar.gz


sudo tar xzf libflashplayer-10.0.45.2.linux-x86_64.so.tar.gz
sudo mv libflashplayer.so /usr/lib/mozilla/plugins


restart firefox and test flash on youtube.

source : http://ubuntuforums.org/showthread.php?t=1081964

Disk cloning / partition image howto

http://wiki.archlinux.org/index.php/Disk_Cloning

use clonezilla or fsarchiver (for ext4 partitions)

fsarchiver is found on system rescuecd

clonezilla is a live cd.

PL/SQL questions

- When do you omit a semicolon with dynamic sql?
- expdp. how to export metadata only?
-

Oracle PL/SQL test score.

Assessment Scores
Test: Oracle PL/SQL
This report is confidential and its contents are intended to assist in the prediction of an applicant's work behavior. If you would like more information about this interpretive report or other products that PreVisor offers, please contact your account representative.

Score: 3.67
Proficiency Level: Advanced (3.51 - 4.50)
The candidate has mastered the basic concepts of Oracle PL/SQL (Architecture, Exception Handling, Built-in Packages) and intermediate Oracle PL/SQL concepts (SQL Operations, Language Elements, Tuning). The candidate is likely proficient with more advanced Oracle PL/SQL concepts such as:
# Subprograms
# Debugging
# Utilities
At the Advanced level, the candidate will be capable of working on projects involving Oracle PL/SQL and will be capable of mentoring others on most projects in this area.

Percentile Comparisons
The percentile score indicates how well the candidate scored relative to other candidates in the comparison population indicated by the score.


Percentile Comparisons


Percentile
Low Medium High
30 70 100
Global Population
86
Company Population
74


Time Summary:
Time Taken (mm:ss): 38:29
Started on: 3/4/10 3:08 PM
Completed on: 3/4/10 3:47 PM

Rownum trick

SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM<=1000000;

or if you need random numbers, just simply use

SELECT DBMS_RANDOM.VALUE
FROM DUAL
CONNECT BY ROWNUM<=1000000;


source : http://www.oracledba.co.uk/tips/cute_rownum.htm

To Practice

- Pipelined functions
- collections : with limit,without limit , exceptions
- bulk collect , binding
- ref cursor
- indexes
- explain plan with partitions
- parallel queries
- materialized views
- advanced query
- replication
- dbms_jobs
- dbms_lock.sleep(.01)
- partitioning , partition exchange
- sdlc
- normalization
- tuning
- dbms_stats
- stats_pack

Dimensional Modeling Case Study : Retail Sales

Step 1: Select the business Process : we select POS transactions
Step 2: Declare the grain

Capture the grain in the facts at the most detail level. Such most detailed data is called ATOMIC DATA.
Atomic data provides maximum analytic flexibility because it can be constrained and rolled up in every possible way. Detailed data in a dimensional model is poised and ready for the ad hoc attack by business users.

Step 3: Choose the dimensions
Step 4: Identify the facts.

Question: Should a calculated fact be stored in the database?
A) Yes as application logic at different places using the facts can introduce errors.
Better to have one source of calculation , therefore no chance of user error.
Also why should the reporting tool do calculations if can be avoided.

Yes Views can be an alternate solution but in that case users should not be given access to underlying tables in any circumstance.


Dimensions :
----------
Date
Product
POS Transaction Fact
Store
Promotion


Dimensions should have descriptive column values like
Holiday/NonHoliday instead of Y/N
Weekend/Weekday instead of Y/N flags
selling season column can have values like "Valentine's day/Christmas/Thanksgiving" etc

In a report dimension attributes become report headers.
eg total sales by Month by Weekday

Descriptive values help in slicing/dicing data rather than put another decode logic in
reports say weekend or non weekend based on Y/N flag.

Q) Do we need an explicit date dimension. Cant we do with actual date in the fact table instead
of a surrogate date key from date dimension
A) YES
reasons
1) relation databases can easily handle joins, optimizer will do it efficiently based on PK index defined.
2) Most databases will not index date calculations, so queries constraining on date calculations will be slower as will not take advantage of index.
3) Business users are not versed with SQL date semantics like how to determine weekend/weekday
given a date.
4) calendar logic belongs in the dimension table and not in the application code
5) SQL based date key (actual date) is typically 8 bytes so wasting 4 bytes (integer) for every date key if date is used in the facts.
in each row of the fact table.

Better to have a separate time dimension and not combine with date dimension.

four (4) step dimensional design process

- Select the business process to model
eg raw materials purchasing, orders, shipments, invoicing, inventory, general ledger etc
- Declare the grain of the business process ie specifying exactly what the individual fact
table row represents
- Choose the dimensions that apply to each fact table
- Identify the numeric facts that will populate each fact table.

PL/SQL performance enhancing features

Runtime optimization of fetch loops (10g onwards)
only works for FOR loops involving cursors
eg FOR cust_rec in (select * from customer)
LOOP
END LOOP

and FOR cust_rec in cust_cur
LOOP
END LOOP

Oracle in the above cases post ver 10g will fetch upto 100 rows in each fetch rather than 1
record each time.

This won't work with
open cursor
loop
exit when cursor%NOTFOUND
fetch cursorname into
end loop
close cursorname

source : Page 798 (Oracle PL/SQL by Steven Feuerstein)

Data Caching techniques
- Package Based Caching
- caching variables/constants
- caching table
- caching most commonly accessed rows of a table (just-in-time caching of table data)

Deterministic function caching
Function result caching