2010-04-30

Clone oracle 10g database software on Solaris

Environment
Oracle 10.2.0.4, Solaris 10

Cloning
1. Install oracle software, recommended patches and CPU on source host.
2. Oracle recommends to shutdown any databases, listeners, agents etc before copying files.
3. prepare exception list
export CLONE=/oracle/stage/clone10.2.0.4
cd $ORACLE_HOME
find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt
# network/admin can be useful for next installation
#find ./network/admin >>$CLONE/ExcludeList.txt
find ./oc4j/j2ee/OC4J_DBConsole_* >>$CLONE/ExcludeList.txt
find `hostname |cut -d "." -f 1`*_* >>$CLONE/ExcludeList.txt
# DBCA templates, If u have them
find ./assistants/dbca/templates >>$CLONE/ExcludeList.txt
cat $CLONE/ExcludeList.txt

4. archive source Oracle home
cd $ORACLE_HOME
tar -cvfX - $CLONE/ExcludeList.txt . | gzip -c >$CLONE/oracle_home.tar.gz

5. copy tar.gz file to target host
6. extract oracle home as root to preserve permissions
# cd $ORACLE_HOME
cd /oracle/rdbms/10.2.0/ora10P1
gunzip < /oracle/stage/clone10.2.0.4/oracle_home.tar.gz | tar xvf -
# if you need to change owner of installation. Owner group is the same.
find . -user ora10T4 -exec chown ora10P1 {} \;

7. on target host as oracle user
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup

8. Solaris specific. To change group (dba) that owns the installation modify
vi $ORACLE_HOME/rdbms/lib/config.s
otherwise go to next step

9. Solaris specific
IGNORE make -f ins_rdbms.mk config.o It returns: make: Fatal error: Command failed for target `config.o'
make -f ins_rdbms.mk ioracle

10. run Installer
cd $ORACLE_HOME/oui/bin
detach oracle home if it exists
./runInstaller -detachHome ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1"
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1" ORACLE_HOME_NAME="db_home_10g2_1"
and run as root
$ORACLE_HOME/root.sh

11. (optional) Let other users use Oracle Home.
cd $ORACLE_HOME/install
./changePerm.sh

Useful links
  1. Metalink DocID 300062.1 How To Clone An Existing RDBMS Installation Using OUI
  2. Metalink DocID 558478.1 Cloning A Database Home And Changing The User/Group That Owns It
  3. Oracle Software Cloning Using Oracle Universal Installer

2010-04-28

Speed up index creation

To speed up CREATE INDEX for large table or to adjust PGA per session.
Environment
Solaris 10, Oracle database 10.2.0.4

Solution
The most obvious way is to use NOLOGGING and PARALLEL, e.g.
CREATE INDEX index_name ON table_name
(column1, column2)
TABLESPACE ts_name
PARALLEL (DEGREE 6) NOLOGGING;

If it is not fast enough, you need to increase sort area size. Using WORKAREA_SIZE_POLICY = MANUAL and increasing SORT_AREA_SIZE didn't give a good result in my case. PGA was still quite small.

Another option is to play with hidden parameters
_pga_max_size Maximum size of the PGA memory for one process
_smm_max_size maximum work area size in auto mode (serial)
_smm_px_max_size maximum work area size in auto mode (global)

To see your current values log in as SYS and run following query:
(_smm* parameters are already in KiB)

select substr(ksppinm,1,20) param, ksppstvl/1024 "size_KiB", substr(ksppdesc,1,70) description
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_pga_max_size', 'pga_aggregate_target')
union all
select substr(ksppinm,1,20), to_number(ksppstvl), substr(ksppdesc,1,70)
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_smm_max_size', '_smm_px_max_size');

for 10.2.0.4 it looks like Oracle automatically sets values as
_pga_max_size = 0.2 * pga_aggregate_target
_smm_max_size = 0.5 * _pga_max_size (it automatically changes when you change _pga_max_size)
_smm_px_max_size = 0.5 * pga_aggregate_target

So by changing pga_aggregate_target you can change PGA size limit for session. You also can change these hidden parameters dynamically and monitor PGA usage, e.g.
alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2G;

These changes affect all sessions.

2010-04-07

Solaris cheat sheet for DBA

There are some handy Unix commands for DBA. It was done on Solaris 10.

System Configuration
prtconf
Find physical memory
prtconf | grep Memory

Information about CPU
Physical processors
psrinfo -p
psrinfo -pv
Virtual processors
psrinfo
psrinfo -v

Information about swap
List all swap areas
swap -l
Show swap usage
swap -s
Swapping activity (3 seconds 5 samples)
vmstat -S 3 5

Performance Monitoring
top
prstat
top processes with totals per user
prstat -a
sorted by memory and totals per user (shared memory shown).
prstat -a -s rss

Archiving
zip (-r) recursively and (-y) with symbolic links
zip -vry file.zip target_dir
tar and compress (tar stores symbolic links)
tar -cvf - target_dir | gzip -c > destination_dir/file.tar.gz
Use RELATIVE path for target directory, unless you want to restore files to the same location.
Unpacking tar.gz
cd destination_dir
gunzip < file.tar.gz | tar xvf -

Checksum and digest
digest -va md5 file_name
digest -va sha1 file_name
cksum file_name

32 or 64?
Is my Solaris 64-bit or 32-bit?
/usr/bin/isainfo -kv
Is my Oracle 64-bit or 32-bit?
cd $ORACLE_HOME/bin
file oracle

Oracle files
cat /var/opt/oracle/oratab
cat /var/opt/oracle/oraInst.loc

Solaris files
/etc/vfstab File system mount table
/var/adm/loginlog Log of login attempts
/var/adm/messages Syslog location