Basic Linux commands and links

These are some of the common linux OS commands for checking various system parameters.

1. To check for OS version
   # uname -a
2. To check 32 bit or 64 bit
   # uname -m
3. To check memory
   # cat /proc/meminfo
4. to check CPU info
   # cat /proc/cpuinfo
5. to check linux version
   # cat /etc/redhat-release
6. to check hard disk space
   # df -h or df -k
7. to check kernel parameters
   # cat /etc/sysctl.conf
8. Finding files
   # whereis filename
9. Getting to GUI screen. The default boot runlevel is set in the file /etc/inittab with the initdefault variable
   # startx or # init 5
10. Starting and stopping services (eg samba services)
    # service smb start
    # service smb stop
    # service smb status
    the chkconfig command can be used to adjust which applications start at each runlevel
    # chkconfig --list
11. CRONTAB Basic
    # crontab -e
    # crontab -l
    A crontab file is comprised of six fields:
    fields values
    ------ ------
    Minute 0-59
    Hour 0-23
    Day of month 1-31
    Month 1-12
    Day of Week 0 - 6, with 0 = Sunday
    

Some common linux commands for checking Oracle

• List all Oracle error messages from the alert.log file
  "grep"
  -v : show lines that do not contain the string
  -h : basic usage description
  # grep ORA- alert.log
• Find out file system usage for Oracle destination
  # df -k grep oradata

Some useful Linux links and tutorial

• www.linuxdevcenter.com/linux/cmd - basic linux command
• http://linuxcourse.rutgers.edu/rute/index.html
• http://www.rci.rutgers.edu/~usseries/UNIXcmds.html - basic unix command
• http://www.linuxhomenetworking.com/
• http://www.puschitz.com/ - installing Oracle in linux

Linux and Oracle links

• http://www.oracle-base.com/articles/10g/Articles10g.php - Oracle Articles & Installation guides
• http://www.dbazine.com/oracle/or-articles/liu2 - Top 10 DBA shell scripts for monitoring database
  
  

Oracle Rman - Server side Backup & Recovery

There are basically 2 types of backup, user-managed backup or server-managed backup. Rman is a server-managed recovery process. It keep tracks of what you have backup, find what files it is needed to restore.


Basic RMAN command

Rman> show all – displays everything
Rman> list backup – displays a list of
backups

Some useful Rman configuration

Rman > CONFIGURE CONTROLFILE AUTOBACKUP on;
Rman > CONFIGURE CHANNEL DEVICE TYPE DISK
FORMAT ‘C:\oracle\backup\ora_df%t_s%s_s%p';

Backing up using Rman. Some basic commands and options

Rman > backup database;
Rman > backup incremental level 0 database;
Rman > backup tablspace 'users';
Rman > backup current controlfile;

Here is an example of full recovery on bare system (meaning you lost everything! Or you are re-creating on another machine). Usually, if the spfile and controlfile is not missing, you can simply do a "restore database", "recover database" process

Set ORACLE_SID=mydb
%ORACLE_HOME%\bin\rman target mydb/password
Rman> startup nomount;
Rman> set DBID = ;
Rman> restore spfile from autobackup;
Rman> restore controlfile from autobackup;
Rman> alter database mount;
Rman> restore database;
Rman> recover database;
Rman> alter database open resetlogs;

Note : I have some problem with the control files . I need to copy the files from %oracle_home%\database to %oracle_home%\oradata and copy the pwd password file over to to make it work. Use "startup force mount" if you are installing on a fresh machine. I wonder if anyone have similar problems?

Rman> report obsolete;
Rman> delete obsolete noprompt;

Eg : backup all database and archive log. Delete all obsolete backup after backup is done

Rman> backup device type disk database;
Rman> backup device type disk archivelog all not
backed up delete all input;
Rman> delete noprompt obsolete device type disk;

Unable to find controlfile autobackup (Rman-06172)
If you use a different directory/format for controlfile autobackup you can set that format in the recovery script

set DBID=8267167
set controlfile autobackup format for device type disk to
'e:\dev\back\%F';
restore controlfile from
autobackup;

Otherwise, move them to Oracle_Home\database

Samples scripts

Backup incremental 1,2,..x

run
{ allocate
channel d1 type disk;
backup incremental
level=0 cumulative database format 'c:\ORACLE\BACKUP\ch12\db_%d_%s_%p_%t'
tag = 'whole_inc0';}

run
{ allocate channel d1 type disk; backup
incremental level = 1 cumulative database
format
'c:\oracle\BACKUP\ch12\db_%d_%s_%p_%t'
tag = 'whole_inc1';}

Restore database

shutdown abort;
startup mount;
run
{ allocate channel d1 type disk;
restore database;
recover database;}

Duplicate database

connect target sys/practice@practice
connect catalog rman/rman@rcat
connect auxiliary sys/cline@cline
run
{ set until logseq 13 thread 1;
allocate auxiliary channel d1 type disk;
duplicate target database to CLINE;}
alter database open;

Standby database

run { allocate channel d1 type disk;
backup incremental level=0 database format
'c:\ORACLE\BACKUP\db_%d_%s_%p_%t'
tag = 'stby_inc0'
include current controlfile for standby;
sql "alter system archive log current";
backup archivelog from time 'sysdate-1/24'
format 'c:\ORACLE\BACKUP\ar_%d_%s_%p_%t';}

Incomplete recovery

run
{ set until time "to_date('11/28/2005 16:00:00','MM/DD/YYYY
HH24:MI:SS')";
shutdown immediate;
startup mount;
allocate channel d1 type disk;
restore database;
recover database;
alter database open resetlogs;}

Some Rman links
http://www.pafumi.net/rman.htm

Archive or No archive log

The Oracle database can run in one of two modes: ARCHIVELOG mode and NOARCHIVELOG mode. Unless you keep this file, you cannot recover data from a backup to the current point in time.

To check if the database is in archive log mode. Connect as sysdba
SQL > select dbid, name, log_mode from v$database; --or
SQL > archive log list ;

To start archive mode log.
SQL> startup mount;
SQL> alter database archivelog;
To start archive log automatically in init.ora file, add this line
LOG_ARCHIVE_START=TRUE

To stop archive mode log.
SQL> alter database noarchivelog;

To change the archive log file destination
SQL> ALTER SYSTEM SET log_archive_dest_l = "location=/oradata/PRACTICE/archive";

Deadlocks in database

How to find the blocking/blocked sessions?
There are a number of reasons why a database will hang or appear to hang. One cause can be due to 2 or more sessions blocking each other when attempting to access the same object in incompatible modes. How to identify who is waiting and who is blocking?

You can use some useful scripts here.
Method 1 - SQLPLUS (1)
column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8

select distinct substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID", s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS' and
a.sid in (select l.sid from v$lock l
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=l.id1 and
b.id2=l.id2 and b.request>0));

Another script (2)
set linesize 132
pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID','S.SERIAL# Kill, U1.NAME'.'substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request
from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5

Another method - 3 (using oracle script)
SQL>@$ORACLE_HOME/rdbms/admin/utllockt.sql

if you encounter missing table error, run this scripts
SQL>@$ORACLE_HOME/rdbms/admin/catblock.sql

Method 2 - OEM
Oracle Enterprise Manager provides an easy way to detect blocking sessions. For 10g, go to Performance -> Blocking session. Simply select the first node and click the "kill" button

How to Recreate the DB Control Repository

This is a step by step information of how to recreate DB control repository. DB control are sensitive to operating system changes such as hostname changes.

How to remove and recreate the DB Control Repository in a 10g Database.

On Unix Systems:

1. Run 10g DB $OH/sysman/admin/emdrep/bin/RepManager
2. NOTE: Type RepManager by itself and you will get a list of options available for the tool
3. After you delete the Repository, you will need to remove the configuration files. To do this, run emca -x
4. Finally, to created both the schema objects and configuration files run emca and answer the prompts

On Windows Systems:
NOTE: The RepManager script in 10.1.0.2.0 does not work correctly on Windows. For this reason, you will have to manually drop the sysman schema and management objects. The RepManager script should be fully functional in the 10.1.0.3.0 release.

1. Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and mangement objects:
   SHUTDOWN IMMEDIATE;
   STARTUP RESTRICT;
   EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
   EXEC sysman.setEMUserContext('',5);
   REVOKE dba FROM sysman;
   DECLARE
   CURSOR c1 IS
   SELECT owner, synonym_name name
   FROM dba_synonyms
   WHERE table_owner = 'SYSMAN'
   ;
   BEGIN
   FOR r1 IN c1 LOOP
   IF r1.owner = 'PUBLIC' THEN
   EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
   ELSE
   EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
   END IF;
   END LOOP;
   END;
   DROP USER mgmt_view CASCADE;
   DROP ROLE mgmt_user;
   DROP USER sysman CASCADE;
   ALTER SYSTEM DISABLE RESTRICTED SESSION;
   
2. At a command Prompt, run emca -x sid to remove the existing configuration files and then recreate the schema and configuration files with just emca.
   For example: emca -x
   emca

For Oracle 10g rel 2

1. to remove dbconsole, type in the command line
   emca -deconfig dbcontrol db -repos drop
2. to add dbconsole
   emca -config dbcontrol db -repos create
3. to use silent installation
   ORACLE_HOME = /opt/oracle/product/10.2.0
   PORT = 1521SID = cctest
   HOST = cc-oracle.co.clatsop.or.us
   DBSNMP_PWD = oracle12
   SYSMAN_PWD = oracle12
   SYS_PWD = oracle12
   DBCONTROL_HTTP_PORT = 1168
   
   Then run the emca with the following options:
   emca -config dbcontrol db -silent -respFile

Scheduling Jobs in Oracle

Scheduling jobs in Oracle can be done either thru SQLPLUS or OEM.
A example of scheduling a job is

SQL> VARIABLE jobno number;
SQL> BEGIN - Run the job every 10 minutes
DBMS_JOB. SUBMIT (: jobno, ' create_date_log_row; ' , SYSDATE, ' (SYSDATE + l/(24*6) ) ');
commit ,
END;
/
SQL> print jobno

There is a job_queue_processes which you must set the value greater than 0 or else the job will not run. In Oracle 8i, by default this value is 0, starting Oracle 9i this value is 10.

To start the job scheduler once the database is open, use the ALTER SYSTEM command:
SQL> ALTER SYSTEM SET job_queue_processes = 2;

How do you remove the jobs you don't need?
SQL> EXEC DBMS_Job.Remove(X);
COMMIT;<-- remember to commit

To temporary disable jobs from running
SQL> EXEC DBMS_JOBS.BROKEN(23, TRUE);
To enable
SQL> EXEC DBMS_JOB.BROKEN(23, FALSE, SYSDATE);

• If you want the job to run every six hours, use the interval SYSDATE + 1/4.
• If you want the job to run every hour, use the interval SYSDATE + 1/24.
• If you want the job to run every half hour, use the interval SYSDATE + 1/48.
• If you want the job to run every 15 minutes, use the interval SYSDATE + 1/96.
• If you want the job to run every minute, use the interval SYSDATE + 1/1440.
  
  Of coz, you can also schedule jobs using Operating System. For Windows = Task scheduler, for Linux = Crontab
  

Killing Oracle sessions

One of the most common problems in Oracle is someone taking huge resources causing the system to appear "hang". Killing an Oracle session can be done either by using Oracle Enterprise Manager(OEM) or SQLPLUS. Killing from OEM is straightforward. However there are times there you couldn't even get into the OEM due to CPU is 100%.

You can either kill it from the Oracle level or Operation system level.

Using the Oracle level method
Identify the session and kill the session using
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Using the Operating system level method
In SQLPLUS (Windows)
SELECT s.sid,
p.spid,
s.osuser,
s.program
FROM v$process p,
v$session sWHERE p.addr = s.paddr;

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:
C:> orakill ORACLE_SID spid

In SQLPLUS (Unix, Linux)
To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:
% kill -9 spid

You can also disconnect the session using these command
alter system disconnect session 'sid,serial#' immediate;

A New Beginning..

This is my first blog.

This blog is mainly about running Oracle based by my past experiences. It will covers issues on Oracle databases, IAS, Oracle forms and reports, etc.

I will also includes operating system issue (Windows, Linux) in relation with Oracle products.

Some useful Oracle website to start with
http://www.ixora.com.au/ - free tips and scripts on Oracle and Unix
http://asktom.oracle.com - Tom Kyte website.
http://metalink.oracle.com - Oracle helpdesk