Wednesday, August 29, 2012

ORCL - Restart Database and EM

Back from a week vacation and found that the database is not working as well the ORCLE EM. What a pity for me as a newbie DBA without any mentor or help inside office. The good thing is I have Intenet to look for help....

OK, after half day working around, here I record the steps after server is accidently shutdown:

1. my bad is not setup swap space persistent, so I have to swap -a to make enough space, will do the persistent setup for swap space.
swap -a /export/home/oracle/orcl_swap then swap -l to check

2. Start the listener: lsnrctl start

3. Set up the ORACLE_SID, could add the line in .profile: export ORACLE_SID=orcl

4. Set up the ORACLE_HOSTNAME and UNQNAME for ORALE EM
export ORACLE_HOSTNAME=solaris
export ORACLE+UNQNAME=orcl
OM will look inside folder solaris_orcl

5. Startup database
sqlplus / as sysdba then startup

6. Start Oracle EM
emctl start dbconsole

Thanks to the link ORACLE-BASE.com:
http://www.oracle-base.com/articles/misc/basic-enterprise-manager-troubleshooting.php

Tuesday, July 24, 2012

ORCL - Data Pump

We need move 10g production data into 11g test so we could test advance system upgrade, there are three upgrade methods offered to upgrade database from 10g to 11g.
  1. Database Upgrade Assistant (DBUA)
  2. Manual Upgrade (Oracle provided scripts)
  3. Export/Import (exp/imp, or expdp/impdp)
Using data pump looks promising, I prefer using Oracle EM, rather than manually following the steps:
1 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp
2 copy dump file to new server
3 create database in new server with same tablespaces as source database -- IS IT NECESSARY?! (if different structure)
4 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp

I used EM and finally got this error:
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOOK_FOR_OBJECT 
looks some problem with temp tablespace:
assign the temp tablespace to the schema owner as Oracle is probably using whatever default TEMP tablespace is specified for the database.
SQL> alter user advance temporary tablespace TEMP;
 
At leaset I was able to login through PL/SQL developer.





helpful links:

Monday, July 23, 2012

ORCL - Installation

I decide to blog this after some frustration on installing Oracle 11g on Solaris 10. (For Solaris 11 you need some support account to get the Oracle 11g).

Intenet has a lot of information which sometimes could mislead you if you dont read carefully :(. I will recommend to read the official manual.

1st intall Oracle-Solaris 10, pretty straight forawrd, login as root.

Preinstallation Requirements:
PACKAGE: check all needed packages: pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibms SUNWsprot \ SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt get missing package from installaton DVD if missing any:
pkgadd -d /cdrom/sol*/Solaris*/Product SUNWi1cs SUNWi15cs
GROUP AND USER:
# groupadd oinstall
# groupadd dba
# useradd -d /export/home/oracle -m -s /usr/bin/bash -g oinstall -G dba oracle
# passwd -r files oracle
KERNEL PARAMETERS (IMPORTANT!)
Set Resource Control to Oralce Recommended Value
# projadd -U oracle -K "project.max-shm-memory=(priv,6g,deny)" group.dba
# projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.dba
# projmod -sK "project.max-sem-ids=(privileged,100,deny)" group.dba
# projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba
This will make them persist, confirm by cat /etc/project
NOTE: When you use the prctl command (Resource Control) to change system parameters, you do not have to restart the system for these parameter changes to take effect. However, the changed parameters do not persist after a system restart.
Make sure oracle user is associated with the project (group.dba). This is missing in documents, and will possible produce "out of memory" error.
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=3(default)
# usermod -K project=group.dba oracle
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=100(group.dba)  
SWAP (IMPORTANT!)
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
# mkfile 4096m /export/home/oracle/orcl_swap
# swap -a /export/home/oracle/orcl_swap
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
/export/home/oracle/orcl_swap - 8 8388600 8388600
Now wh have enough swap space.
if you need persistent swap, add line inside /etc/vfstab.
INSTALL ORACLE Downlaod Oracle 11g and create necessary folders.
# mkdir -p /export/home/oracle/product/11.2.0/db_1
# mkdir /export/home/oracle/tmp
# chown -R oracle:oinstall /export/home/oracle
Configure profile for oracle user with enviroment variables:
vi/gedit /export/home/oracle/.profile
export ORACLE_BASE=/export/home/oracle
export ORACLE_HOME=$ORACLE_HOME/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export TMP=/export/home/oracle/tmp
export TMPDIR=/export/home/oracle/tmp
export DISPLAY=0:0
Unzip and install
# unzip solaris.x64_11gR2_database_1of2.zip
# unzip solaris.x64_11gR2_database_2of2.zip
# cd databse
# ./runInstaller

After 5 time installation I am feeling OK with Oracle installation. Next step I woild like to copy 10g production data into 11g new server.