Database Hang and Recover Procedure

In our example, we assume the database is hanging for unknown reason.

1. Try to identify process ID the top loading process

2. Try to check the oracle user session detail by using the above process ID

3. If there is no particular high load process or we cannot connect to oracle database to check user session detail (i.e. connect via sqlplus but seem hang). We assume the database is hang.

4. Dump System Statistics
   e.g. export ORACLE_SID=DB_SID
          sqlplus -prelim / as sysdba
          oradebug setmypid
          oradebug unlimit
          oradebug dump systemstate 10
          -> wait 90 seconds (i.e. This is not a command)
          oradebug dump systemstate 10
          -> wait 90 seconds (i.e. This is not a command)
          oradebug dump systemstate 10
          exit


5. Perform Hang Analysis
   e.g. export ORACLE_SID=DB_SID
          sqlplus -prelim / as sysdba
          oradebug setmypid
          oradebug unlimit
          oradebug hanganalyze 3
          -> wait 90 seconds (i.e. This is not a command)
          oradebug hanganalyze 3
          exit

6. Create SR in oracle and update the trace files generated  in Step 5 and Step 6 to Oracle SR

7. Shutdown Database abnormally by killing the Oracle SMON process and all related process will be killed

8. Check all process related to target instance still existed. If they are still existed, please kill them manually.

9. Start oracle instance in restricted mode
   e.g. export ORACLE_SID=DB_SID
          sqlplus / nolog
          connect / as sysdba
          startup restrict

10. If the database startup normally, please shutdown it to make sure database can be shutdown normally.

11. Start oracle instance in restricted mode and check the date integrity first.

12. If everything ok, please disable restricted mode and notify user that the system is back to normal

How To Retreive Date and Time Information In Oracle

How To Retreive Date and Time Information In Oracle

SQL> select to_char(sysdate,'DD/MM/YYYY HH24:MI') "Started" from dual;

Started
----------------
30/12/2009 16:54

SQL> select sysdate from dual;

SYSDATE
---------
30-DEC-09

SQL> select sysdate-1 from dual;

SYSDATE-1
---------
29-DEC-09

SQL> select trunc(sysdate-1) from dual;

TRUNC(SYS
---------
29-DEC-09

SQL> select '29-DEC-09' from dual;

'29-DEC-0
---------
29-DEC-09

Tips for PCTUSED and PCTFREE

If the application frequently performs UPDATES that alter sizes of rows
greatly, then PCTFREE can be set high and PCTUSED can be set low. This would
allow for large amount of space in data blocks for row size growth.

If there is more INSERT activity with less UPDATES, the PCTFREE can be set
low with average value for PCTUSED to avoid chaining of rows.

If the main concern is performance and more space is available, then
PCTFREE can be set very high and PCTUSED very low.

If the main concern in space and not performance, then PCTFREE can set very
low and PCTUSED very high.

The default values of PCTFREE is 10 and PCTUSED is 40.

Mount standby database readonly

connect / as sysdba
shutdown
startup nomount;
alter database mount standby database;
alter database open read only;

Check Oracle Database Size SQL

select sum(bytes)/1024/1024/1024 Size_GB from dba_data_files;

Oracle 10G Installation Procedure in SuSE 9.3 Professional or SUSE Linux Enterprise Server 9

1. Install the following package in SUSE Linux Enterprise Server 9 :

binutils-2.15.90.0.1.1-32.5
gcc-3.3.3-43.24
gcc-c++-3.3.3-43.24
glibc-2.3.3-98.28
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.24
libstdc++-devel-3.3.3-43.24
make-3.80-184.1
pdksh-5.2.14-780.1
sysstat-5.0.1-35.1
xscreensaver-4.16-2.6
openmotif-2.2.3
db1-1.85-89.1

compat-db-4.0.14(Will Prompt error But we cannot find in SuSE 9.3)

orarun-1.8-109.15.i586.rpm (For Oracle 9i Only)

2. Modify /etc/sysctl.conf

kernel.shmmax = 536870912
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.file-max = 65536

For SuSE Only : /sbin/chkconfig boot.sysctl on

3. Add the following to /etc/security/limits.conf

* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536

4. Add the following to /etc/pam.d/login

session required /lib/security/pam_limits.so
session required pam_limits.so

5. Add the following to /etc/profile

ulimit -u 16384 -n 65536

6. Create user ora10g and group dba

7. Add the following to /home/ora10g/.profile

ORACLE_BASE=/disk1/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
NLS_LANG=AMERICAN_AMERICA.ZHT16BIG5; export NLS_LANG
NLS_DATE_FORMAT=dd/mm/yyyy
DISPLAY=192.168.1.123:0.0; export DISPLAY

PATH=$PATH:$ORACLE_HOME/bin; export PATH

ORACLE_SID=test_db
export ORACLE_SID

# For 8.1.7.3-4 and 9i export bugs
ORA_OCI_NO_OPTIMIZED_FETCH=1; export ORA_OCI_NO_OPTIMIZED_FETCH

8. mkdir /disk1 and change permission to ora10g:dba

9. Install DB

10. Install Compansion Option 2

11. Install Compansion Option 3(Apache Only)

12. mv /disk1/oracle/product/10.2.0/db_1/opmn/conf/ons.config /disk1/oracle/product/10.2.0/db_1/opmn/conf/ons.config.orig

How to drop datafile from tablespace in Oracle 8i

Oracle 8i
There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents;
6. through OS command remove all datafiles belongs to droped tablespace.

Well, one way may be :
1. create a new tablespace
2. move all objects from your old tbs to the new
3. drop your old tablespace
4. recreate your tablespace with the size as well
5. moveback objects to this last tbs
6. drop your tbs created on step 1
7. rebuild index.