Sync time

Windows 2000/XP's W32Time Service

The following example will make ntp1.example.local and ntp2.example.local our two synchronization sources (the quotes are only required when you have more than one server in your list).

net time /setsntp:"ntp1.example.local ntp2.example.local"

Now we need to start the "Windows Time" (W32Time) service. Open a command prompt (Click Start, then Run, type "cmd" and click ok.) and issue the following command:

net start "windows time"

net time [\\ComputerName] [/querysntp] [/setsntp[:NTPServerList]]
Parameters
file://ComputerName/ : Specifies the name of a server you want to check or with which you want to synchronize.
/set : Synchronizes the computer's clock with the time on the specified computer or domain.
/setsntp[:NTPServerList] : Specifies a list of NTP time servers to be used by the local computer. The list can contain IP addresses or DNS names, separated by spaces. If you use multiple time servers, you must enclose the list in quotation marks.

Check the list of time servers here
http://ntp.isc.org/bin/view/Servers/NTPPoolServers




Sync Time on Linux
# rdate -s time.nist.gov

Data Guard Broker thru CLI

The Data Guard broker is a centralized framework that creates, automates, and manages all aspects of a Data Guard configuration. This broker can be accessed locally or remotely by using either of the two clients: the command-line interface (CLI) or the Data Guard page of the new OEM. Once you are connected, you have the ability to change any attribute of the configuration as well as monitor progress and perform health checks.

The CLI Interface
We need to satisfy a few requirements. First, the physical or logical standby must have been created, as the CLI does not have the ability to create a standby (only the Data Guard GUI does). In addition, both the primary and standby databases must have been started with the DG_BROKER_START parameter equal to True (this spawns the DMON process) and be using an spfile.

init need to add
*.DG_BROKER_START=TRUE
*.FAL_CLIENT='ORCL1'
*.FAL_SERVER='ORCL2'
*.STANDBY_FILE_MANAGEMENT='AUTO'

c:> dgmgrl
DGMGRL> connect sys/password

DGMGRL> create
configuration 'MyDR' as
>primary database is 'orcl1'
>connect identifier is orcl1;

DGMGRL> add database 'orcl2' as
>connect identifier is orcl2
>maintained as physical;

DGMGRL> enable configuration;

DGMGRL> show configuration;

how to change the standby of the physical standby to the read-only mode for reporting purposes

DGMGRL> edit database 'orcl2' set state='READ-ONLY';

To return the physical standby to the recovery mode, we change the state once again.

DGMGRL> EDIT DATABASE 'orcl2' SET STATE='ONLINE';

DGMGRL> SWITCHOVER TO "ORCL2";

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:

DGMGRL> SHOW DATABASE VERBOSE 'db01';

Performing a Failover Operation

You invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable.

Connect to the target standby database.
To perform the failover operation, you must connect to the standby database to which you want to fail over using the SYSDBA username and password of that database. For example:

DGMGRL> CONNECT sys/oracle@db02

Issue the failover command.
Now you can issue the failover command to make the target standby database the new primary database for the configuration. Note that after the failover completes, the original primary database cannot be used as a viable standby database of the new primary database unless it is re-created as described in Section 4.2. T

DGMGRL> FAILOVER TO "DB02"

Some Data Guard links
http://www.dbasupport.com/oracle/ora10g/logical_standby_db.shtml
http://www.pafumi.net/Standby_Concepts_Configuration.html
http://www.oracle-base.com/articles/9i/DataGuard.php

Some Rman links
http://www.radford.edu/~wkantsio/oracle/rman-clone-win.htm

Manual Recovery - control files, redo log,etc

Recovering from Loss of a Control File

Losing one of the multiplexed control files immediately aborts the instance.
If you have not lost every control file, recovering from this failure is fairly straightforward.

SQL> startup nomount
SQL> select name, value from v$spparameter2 where name = 'control_files';

In the next step, you change the value of CONTROL_FILES in the SPFILE and restart theinstance, as you can see here:
SQL> alter system
set control_files ='/u02/oradata/ord/control01.ctl',
'/u06/oradata/ord/control02.ctl'4 scope = spfile;
SQL> shutdown immediate
SQL> startup

Recovering from Loss of a Redo Log File

A database instance stays up as long as at least one member of a redo log group is available. The alert log records the loss of a redo log group member.

1. Verify which redo log file group member is missing.
2. Archive the log file group’s contents; if you clear this log file group before archiving it, youmust back up the full database to ensure maximum recoverability of the database in the caseof the loss of a datafile. Use the command ALTER SYSTEM ARCHIVE LOG GROUP groupnum; toforce the archive operation.
3. Clear the log group to re-create the missing redo log file members using the command ALTER DATABASE CLEAR LOGFILE GROUP groupnum; you can also replace the missing member by copying one of the good group members to the location of the missing member

SQL> select * from v$logfile order by group#;
SQL> alter system archive log group 1;
SQL> alter database clear logfile group 1;
SQL> select * from v$logfile order by group#;

Linux Networking links

Some good Linux networking links

NIS Configuration

• http://linuxhelp.blogspot.com/2005/06/nis-client-and-server-configuration.html
• www.angelfire.com/linux/linuxclusters/nis.htmwww.linuxhomenetworking.com
  

Samba Configuration

• http://www.reallylinux.com/docs/sambaserver.shtml
• http://www.reallylinux.com/docs/basicnetworking.html

NFS Configuration

• www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/
• www.linuxhomenetworking.com

DBMS_APPLICATION_INFO package

I often see requests to investigate why a particular job is taking longer than expected, or to kill a session running a particular job. The problem is identifying the session, and they trying to identify what the session is doing, or what part of the batch process is running.

If developers used the DBMS_APPLICATION_INFO package to instrument their code it would make mine that their life much easy. The package allows you to specify a Module and Action for the current position in the code. This can be monitored externally using V$SESSION and also appears in V$SQLAREA to allow you to match SQL to a module.

You can also use the package to put your own progress information in V$SESSION_LONGOPS. If you haven't come across this view before Oracle itself populates it when doing "long operations", so you can monitor the progress of an index rebuild, or how far a FTS has got. With the DBMS_APPLICATION_INFO package you can show the progress of you own batch processing, eg. You have processes 300 contracts out of 2000 etc.

- at start
DBMS_APPLICATION_INFO.SET_MODULE( 'TEST MODULE','AT START' );
- when complete
DBMS_APPLICATION_INFO.SET_MODULE( NULL,NULL );

The first monitors what the progress in v$SESSION ...
SELECT sid , module ,action
FROM v$session
WHERE module IS NOT NULL

The second monitors V$SESSION_LONGSOPS through the long ops section

SELECT sid ,opname,sofar,totalwork,units,elapsed_seconds ,time_remaining FROM v$session_longops WHERE sofar != totalwork;

VMware for Oracle

You want to run Oracle on multiple platforms? You want to be able to install once and then go ahead and play with the database after having set it up once and not need to reinstall the software . You are very curious to learn these newer technologies, you want to watch and learn how RAC works but unfortunately you just have one computer at home. Even at work it's not just that easy to get a couple of machines , if not servers, to hook them up, build clusters and have them all shared Cooked or RAW via a SCSI let alone JBOD, SAN or NAS.

Good VMware with links with Oracle
http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml
http://www.oracle.com/technology/tech/linux/vmware/cookbook/index.html
http://oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

Performance monitoring on Windows

Oracle Performance Monitoring on Windows

The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install them when you install Oracle, select the custom install option. You can also install this option later via the Oracle installer. Once Oracle Counters for Windows Performance Monitor has been installed, you must perform one more piece of setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry. In order to do this, from a command prompt run orafcfg.exe with a username, password and Oracle net service name as follows:

operfcfg –U system –P password –D sid

This will update the registry. You should now be able to monitor Oracle via perfmon. Some of the things that you can monitor are:

• The Oracle Buffer Cache. Here you can see the cache miss ratio.
• Shared Pool Stats. This collection includes the data dictionary cache, and the library
  cache.
• Log Buffer. Provides information on log space requests.
• Database Data Files. This object provides physical read and write per second counters.
• DBWR stats. Provides information on the DB Writer processes.
  

Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.
By taking advantage of Oracle Counters for Windows Performance Monitor you can easily and efficiently monitor Oracle along with monitoring the OS. Some of the most important and first counters that I look at when performance monitoring a system are:

• Processor: %Processor Time. This gives me a quick look at how busy the system is.
• Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides me with an overview of how well the I/O subsystem is doing.

When first looking at a system I am actually more interested in disk latencies than throughput. The Avg. Disk sec/Read and Avg. Disk sec/Write should be in the range of 5-15 ms (0.005 – 0.015). Anything higher than this indicates a problem.

Performance monitoring on Linux

There are some built in command in Linux to check the performance of linux eg

• top : Provide information (frequently refreshed) about the most CPU-intensive processes currently running. you can sort by CPU% or MEM% by typing 'F'
• ps -aux : all the processes in the system. use "grep "to filter which processes
• free : Display statistics about memory usage: total free, used, physical, swap, shared, and buffers used by the kernel.Easy monitoring with "SAR"

The SAR suite of utilities is bundled with your system (in fact, it is installed on most flavors of UNIX®), but probably not enabled. To enable SAR, you must run some utilities at periodic intervals through the cron facility. Use the crontab -e command while running as the root user

• mpstat : average CPU statistic
• iostat : flow of data to and from disk drive
• vmstat : memory, like "free"

Useful link

http://www-128.ibm.com/developerworks/aix/library/au-unix-perfmonsar.html

Oracle Memory Configuration on Windows Server

Configuring Large Memory for Oracle on 32-bit and 64-bit Windows

There is an inherent maximum of 4 GB of addressable memory for the 32 bit architecture. This is a maximum per process. one 4 GB memory segment is shared by all user threads. By default, if you run multiple Oracle instances on the same server, or run other applications on the same server, they will share the same 4 GB of memory.

By default, on Windows 2000 and Server 2003, 2 GB of the available 4 GB of memory is reserved for the 32-bit OS and 2 GB is shared for User Threads (i.e. the Oracle SGA). It is possible to allow Oracle to use more memory. To expand the total memory used by Oracle above 2 GB, the /3GB flag may be set in the boot.ini file. With the /3GB flag set, only 1 GB is used for the OS, and 3 GB is available for all user threads, including the Oracle SGA.

Workarounds are available for using memory above the 4 GB limit. Intel 32-bit processors such as the Xeon processor support Paging Address Extensions for large memory support. PAE allocates additional memory in a separate memory segment that is also assigned to the process. MS Windows 2000 and 2003 support PAE through Address Windowing Extensions (AWE). PAE/AWE may be enabled by setting the /PAE flag in the boot.ini file. The “USE_INDIRECT_BUFFERS=TRUE” parameter must also be set in the Oracle initialization file. In addition, the DB_BLOCK_BUFFERS parameter must be used instead of the DB_CACHE parameter in the Oracle initialization file.

With this method, Windows 2000 Server and Windows Server 2003 versions can support up to 8 GB of total memory.Windows Advanced Server and Data Center versions support up to 64 GB of addressable memory with PAE/AWE.

Recommanded changes for Windows x86 system Boot.ini SWITCH

• < 4GB None (or /NOPAE)
• = 4GB /3GB (or /3GB /NOPAE)
• > 4GB to <= 16GB /3GB /PAE
• >16GB /PAE only
  
  Support for /3GB and /PAE – 32-bit
  
  
• Windows 2000 Advanced Server
• Windows 2000 Datacenter Server
• Windows 2003 Enterprise Edition
• Windows 2003 Datacenter Edition

32-bit Max Memory Support on x86 based computers

• Windows 2003 Enterprise = 32GB max
• Windows 2003 Datacenter = 64GB max

Some good links for oracle on windows
http://www.siebelonmicrosoft.com/technical_content/oracle/workshops.aspx

How to setup Oracle IAS (Application Server) to run Oracle forms/report?

Make sure you have a list of files (xxxx.conf, jinit.exe, icons.jar, icons.cab, xxxx.sig, xxxx.env html files) before you start.

Step 1. Installation of CD (3 in totals)
Install Oracle 10g IAS Core (9.0.4) on Oracle_home1 eg D:\OAS10g
Install Forms Server and Report Server eg D:\Orant
Install forms patchset 13

Step 2. The Configuration files in 10g IAS
Copy 6iserver.conf to %oracle_home%\Apache\Apache\conf;
Modify the parameters to point to forms6i home
Change the oracle_apache.conf to include 6iserver.conf

For Servlet mode only
Modify the files at %oracle_home\Apache\Jserv\Servlets\zone.properities
Modify the httpd.conf, uncomment #
# Include the configuration for Apache JServ 1.1
# include "D:\OAS10g\Apache\Jserv\conf\jserv.conf"

Step 3. The Configuration files in Forms6i
Copy the XXXX.env files to %oracle_home2%\forms60\server
Copy icons.jar, icons.cab, *.jar, *.sig to %oracle_home2%\java
Change the %oracle_home2%\Net80\admin\tnsnames.ora to point to database server
Replace the file jinit.exe at %oracle_home2%\jinit
Modify the file at “%oracle_home%\forms60\server\formsweb.cfg. Change the jinit_classid = clsid….. if the jinit is different

Step 4. The Registry setup
Add the fmx source to the registry key FORMS60_PATH ….;D:\V420
Add the rep source to the registry key REPORTS60_PATH ….;D:\V420

These are just basic steps for setting up IAS. Visit metalink if you need a detail information of how to setup.

These are my sample conf files
6iserver.conf

Alias /forms60java/ "D:\ORANT\forms60\java/"
Alias /jinitiator/ "D:\ORANT\jinit/"
Alias /dev60temp/ "D:\ORANT\tools\web60\temp/"
Alias /dev60html/ "D:\ORANT\tools\web60\html/"
Alias /jinitiator/ "D:\ORANT\jinit/"
Alias /asp/ "D:\OAS10g\Apache\Apache\hotdocs/"
ScriptAlias /dev60cgi/ "D:\ORANT\bin/"SetEnvIf Request_URL "ifcgi60" ORACLE_HOME=D:\ORANT
SetEnv FORMS60_WEB_CONFIG_FILE D:\ORANT\FORMS60\SERVER\formsweb.cfg
AddType video/avi avi
AddType application/x-orarrp rrpa rrpp rrpt rrpr

xxxx.env

PATH=D:\ORANT\bin
ORACLE_HOME=D:\ORANT
FORMS60_PATH=D:\ORANT\forms60;D:\v420
REPORTS60_PATH=D:\ORANT\forms60;D:\v420
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 # FORMS60_REPFORMAT=pdf

zone.properities

#New version v420 Release GeO env settingservlet.v420.code=oracle.forms.servlet.ListenerServlet servlet.v420.initArgs=EnvFile=D:\ORANT\forms60\server\v420.env servlet.v420.initArgs=configFileName=D:\ORANT\forms60\server\formsweb.cfg

Some issues in Oracle report printing

Oracle reports 6i seem to have more problems with windows XP than windows 2000. Here are some of the tips you can try

1. Check out what report patch you have. I have use patchset 18 and it manage to resolve most of the problem, including print to email issues. You can check which version by clicking help -> about report runtime
   
2. Check report background engine for any errors messages
   
3. Check software firewall. Some workstation comes bundle with firewall and sometimes they don't pop up a window, telling you they have block the report background engine from running.
4. Back to basic. Can you print on other applications software beside oracle report?

Automating Installation

During my day-to-day work, there are many times I need to do re-installation again and again to simulate and to practice. I have found there are actually a couples of ways to automate installation easily. In Oracle there is a response file which you can edit the default parameters, similar Linux kickstart process.

In Oracle, this is how I start

1. Start record the default parameters by typing
   setup.exe -record -destinationFile C:\OraInst\rec.rsp.
   The installation will run normally and a summary page will be presented. You can opt to cancel or continue.
2. To use a response file during an installation on Windows,
   setup.exe [-silent] -responseFile C:\OraInst\custom.rsp.
   Use -silent if you want full unattended installation. If you want the system to fill in default values, omit the -silent.

By default, the sys password will not be recorded, you will have to manually entered the values in the text file.

Automating Windows 2000/XP installation

Windows has a setupmgr which allows you to do unattended installation.

1. Insert the Windows CD
2. Browse /Support/Tools/Deploy.cab, extract the files
3. run the setupmgr.exe to answer a list of questions
4. After you answer all the required parameters, save the files as winnt.sif. you can view this text file.
5. Save these files on a floppy disk. (It is also possible t integrate it with Windows CD)
6. During installation, insert the floppy disk and the Windows CD. The windows will look for answer in the floppy disk for the parameters.
7. Ext

Unattended Installation links
http://support.microsoft.com/?kbid=308662
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=258

Creating USB thumb drive boot disk
http://www.weethet.nl/english/hardware_bootfromusbstick.php
http://www.bootdisk.com

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