OraPerf : Oracle Free Performance Monitor

Some of the program I did during my free time. This is a the first program in Visual Basic Express using Oracle connection string.

Most Oracle DBA have to face question like

In general, how well is my database running? What defines efficiency?

What average response time are my users experiencing?

OraPerf is a little tool which captures Oracle database information such as session and memory usage. It is based on Oracle 10g Dynamic views V$. The information will be update every seconds.
http://download.cnet.com/OraPerf/3000-10254_4-10816959.html

Update :

• Include response time metrics

 

Oracle Wait Interface v$view

Oracle Wait Interface

Oracle Wait Interface has had the following four V$ views :

• V$EVENT_NAME
• V$SESSION_WAIT
• V$SESSION_EVENT
• V$SYSTEM_EVENT

For 10g

Oracle Database 10g Release 1 introduces the following new views to display wait information from several perspectives:

• V$SYSTEM_WAIT_CLASS
• V$SESSION_WAIT_CLASS
• V$SESSION_WAIT_HISTORY
• V$EVENT_HISTOGRAM
• V$ACTIVE_SESSION_HISTORY

V$EVENT_NAME

It is a reference view that contains all the wait events defined for your database instance

select event#, name, parameter1, parameter2, parameter3 from v$event_name order by name;

V$SESSION_WAIT

The V$SESSION_WAIT view provides detailed information about the event or resource that each session is waiting for. This view contains only one row of information per session, active or inactive, at any given time. Unlike the other views, this view displays session-level wait information in real time.

V$SYSTEM_EVENT

The V$SYSTEM_EVENT displays aggregated statistics of all wait events encountered by all Oracle sessions since the instance startup. It keeps track of the total number of waits, total timeouts, and time waited for any wait event ever encountered by any of the sessions.

select b.class, a.*, c.startup_time
from v$system_event a,
v$event_name b,
v$instance c
where a.event = b.name
order by b.class, a.time_waited;

V$SESSION_EVENT

The V$SESSION_EVENT view contains aggregated wait event statistics by session for all sessions that are currently connected to the instance. This view contains all the columns present in the V$SYSTEM_EVENT view and has the same meaning, but the context is session-level. It keeps track of the total waits, time waited, and maximum wait time of each wait event by session.

break on sid skip 1 dup
col sid format 999
col event format a39
col username format a6 trunc
select b.sid,
decode(b.username,null,
substr(b.program,18),b.username) username a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
a.time_waited_micro
from v$session_event a, v$session b
where b.sid = a.sid + 1
order by 1, 6;

Steps of recovering database using Rman from a barebone system. (ie you have lost everything). This is tested on Oracle 10g database.

For setting up Rman, you should set controlfile autobackup on.

Before you do this, you need to create the oradata and admin (bdump, cdump...) directories.


C:\>rman target /
connected to target database: orcl2 (not mounted)

RMAN> set dbid=632410335
RMAN> startup nomount;
RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';
RMAN> restore spfile from autobackup;
RMAN> restore controlfile from autobackup;
RMAN> startup force mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

Oracle Import Export GUI Tools

Check out this tool "OraExport" which allow you to export Oracle data using a GUI interface. It generate a oracle export script and run it. This tool is especially useful to those Oracle DBA who often transfer data objects between Oracle database.

Oracle newbie may also find it is very easy to Export/Import data objects with this tool even without typing an Export/Import parameter.
This is a free tool to share.
Requirements

• Microsoft .Net framework
• Oracle client

Related link on Oracle exp
http://wiki.oracle.com/page/Oracle+export+and+import+
      

 

Oracle High Availability Solutions

Oracle provides four popular high availability solutions:

• Oracle Advanced Replication
• Oracle Real Application Clusters (RAC)
• Oracle Data Guard (physical/logical standby database)
• Oracle Streams

Advanced Replication

Replication is the process of copying and maintaining database objects, such as tables, in multiple database that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations.

Replication supports a variety of applications that often have different requirements. Some applications allow for relatively autonomous individual materialized view sites. Other applications require data on multiple servers to be synchronized in a continuous, nearly instantaneous manner to ensure that the service provided is available and equivalent at all times.

Real Application Clusters (RAC)

Oracle Real Application Clusters (RAC) allows multiple instances accessing a single database. The typical installation involves a cluster of nodes with access to a set of shared disks.

Data Guard

Oracle Data Guard is the management, monitoring, and automation software that work with a production database and one or more standby databases to protect data against failures, errors, and corruption that might otherwise destroy your database.

Streams

Oracle Streams enables you to share data and events in a stream. The stream can propagate this information within a database or from one database to another. The stream routes specified information to specified destinations.

Using Oracle Streams, you control what information is put into a stream, how the stream flows or is routed from database to database, what happens to events in the stream as they flow into each database, and how the stream terminates.

Oracle Streams setup

Streams is basically a queuing technology.

Here is how I get started. You can use Oracle 10g Enterprise Manager to create a schema level stream but apparently it has some bugs and work only on the later version.

Login as stradmin

This is a simple example of streaming data (one way) from source schema to target schema

1. run @startup.sql
2. On the source database , turn on supplemental logging for table or database:
   ALTER TABLE scott.dept ADD SUPPLEMENTAL LOG GROUP dept_pk(deptno) ALWAYS;
   or
   alter database add supplemental log data (primary key, unique index) columns;
3. export schema from the source database.
   exp USERID=SYSTEM/manager@rep2 OWNER=SCOTT FILE=scott.dmp LOG=exportTables.log OBJECT_CONSISTENT=Y STATISTICS = NONE
4. import schema into target database.
   imp USERID=SYSTEM@pluto FULL=Y CONSTRAINTS=Y FILE=scott.dmp IGNORE=Y COMMIT=Y LOG=importTables.log STREAMS_INSTANTIATION=Y
5. run @startup.sql

How to move an Oracle Forms application from Windows to Linux

Log-in access as the "oracle" user

1. Create a staging directory where the application source files (FMB, MMB, PLL, OLB) can be stored permanently or temporarily. This directory will also be where the compiled executables (FMX, MMX, PLX) will be created. For example:
   mkdir /u02/oracle/ias904_mid/forms90/myApplication
   
2. Copy/transfer all of the Forms components which make up the application to the directory created in step 2. These will include FMB, MMB, PLL and OLB files.
   IMPORTANT: Unix is a case sensitive operating system. Be sure that any references to files within your application have been corrected to match files on the new file system. This will usually impact references to image (icon) files, other forms, menus, and libraries.
   Copy/transfer all other application files as needed. These files may include custom resource (.res), image or custom JARs files. These files will need to be placed in specific locations based on how you developed the application. IMPORTANT: If transferring using FTP, all files must be transferred in binary mode.
   
3. Open a shell session.
   
4. Set the ORACLE_HOME variable and point it to the Application Server installation. Be sure to use the appropriate syntax for the particular shell you are using. For example in csh the command would look something like this:
   export ORACLE_HOME=/u02/oracle/ias904_mid
   
5. Set the FORMS90_PATH variable to the directory which was created in step 2. For example:
   export FORMS90_PATH=/u02/oracle/ias904_mid/forms90/myApplication
   Additional variables may be necessary or desired based on your needs and system configuration. Here are a few examples:
   TNS_ADMIN
   NLS_LANG
   CLASSPATH
   TERM
   DISPLAY
   In most cases, setting these will not be necessary if you use the provided script (.sh file) noted in the next step.
   When compiling a Forms application it is important to understand the application. Most important is to understand the dependencies which may exist between components. In other words, for example you will not be able to compile an FMB if it has a dependant PLL which has not yet been compiled. In most cases the order in which compiling should occur is as follows:
   PLL
   MMB
   FMB
   There are exceptions, but this will work in most cases.
   
6. Using the compiler, generate "X" files for all of the application's binaries (PLL, MMB, FMB). The command will be something like the following:
   
   f90genm.sh module=myForm module_type=form compile_all=yes userid=scott/tiger@orcl
   
   Other possible module_type values:
   library
   menu
   form

How to add icons into Forms 10g using JAR

Icon image files can either be retrieved by Forms as individual files on the
filesystem or from a Java Archive (JAR file). If an application uses lots of
icon images it is recommended that they are stored in a JAR file to reduce the
number of HTTP round trips.

Steps to achieve this in a 9iDS/10gDS environments and are given below:

Oracle 9iDS/10gDS (Forms Builder / Runtime)

This example assumes an install of 9iDS/10gDS on a MS Windows.

1. Copy all the icons files (gif or jpg) to a folder e.g c:\icons folder
2. Open up a MS Dos/ Command prompt window
3. Change to the target icons folder
   cd c:\icons
   and jar the icon files
   jar -cvf icons.jar *
4. Copy icons.jar to \forms90\java
   or
   Copy icons.jar to \forms\java
   
5. Changes have to be made in formsweb.cfg
   a) archive_jini=xxall_jinit.jar,icons.jar
   b) imageBase=codeBase
   This signifies that the jar file is placed under the forms90/java or forms/java
   folder.
   

Generic code for Oracle forms calling reports

PROCEDURE CALL_REPORT(rptname varchar2,paramval varchar2 default null,paraform varchar2 default 'N') IS
plid paramlist;
a_paralist varchar2(4000);
report_id Report_Object;
report_job_id VARCHAR2(100);
report_status VARCHAR2(100);
vc_use_report_server char(1) := 'N';
vs_report_server varchar2(256) := 'GEOREPSRV';
vs_report_server_url varchar2(256) := 'dev60cgi/rwcgi60';
vs_report_format VARCHAR2(100) := 'pdf';
BEGIN
dbms_application_info.set_module('GEORPT',rptname);
if paramval is null then
a_paralist := 'PARAMFORM=NO';
else
a_paralist := 'PARAMFORM=NO' paramval;
end if;
-- vincent
-- replace spaces with %20 for web.show_document
a_paralist := replace(a_paralist,' ','%20');
--

plid := buildparamlist(a_paralist);

if instr(UPPER(a_paralist),'COPIES=') > 0 then
delete_parameter(plid,'COPIES');
end if;
if instr(UPPER(a_paralist),'DESFORMAT=') > 0 then
vs_report_format := substr(a_paralist,(10 + instr(UPPER(a_paralist),'DESFORMAT=')),(instr(UPPER(a_paralist),'',instr(UPPER(a_paralist),'DESFORMAT=')) - instr(UPPER(a_paralist),'DESFORMAT=') - 10));
delete_parameter(plid,'DESFORMAT');
end if;
if instr(UPPER(a_paralist),'BACKGROUND=') > 0 then
delete_parameter(plid,'BACKGROUND');
end if;
if instr(UPPER(a_paralist),'CURRENCY=') > 0 then
delete_parameter(plid,'CURRENCY');
end if;
if instr(UPPER(a_paralist),'DECIMAL=') > 0 then
delete_parameter(plid,'DECIMAL');
end if;
if instr(UPPER(a_paralist),'DESNAME=') > 0 then
delete_parameter(plid,'DESNAME');
end if;
if instr(UPPER(a_paralist),'DESTYPE=') > 0 then
delete_parameter(plid,'DESTYPE');
end if;
if instr(UPPER(a_paralist),'MODE=') > 0 then
delete_parameter(plid,'MODE');
end if;
if instr(UPPER(a_paralist),'ORIENTATION=') > 0 then
delete_parameter(plid,'ORIENTATION');
end if;
if instr(UPPER(a_paralist),'PRINTJOB=') > 0 then
delete_parameter(plid,'PRINTJOB');
end if;
if instr(UPPER(a_paralist),'THOUSANDS=') > 0 then
delete_parameter(plid,'THOUSANDS');
end if;

begin
select REF_STRING into vc_use_report_server from SYS_REF_TABLE where ref_code = 'USEREPSRV';
if vc_use_report_server not in ('Y','N') then
vc_use_report_server := 'N';
end if;
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('USEREPSRV', 'Use Report Server','N');
commit;
vc_use_report_server := 'N';
when others then
vc_use_report_server := 'N';
end;



begin
select REF_STRING into vs_report_server from SYS_REF_TABLE where ref_code = 'GEOREPSRV';
vs_report_server := nvl(vs_report_server,'GEOREPSRV');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRV', 'GeO Report Server','GEOREPSRV');
commit;
vs_report_server := 'GEOREPSRV';
when others then
vs_report_server := 'GEOREPSRV';
end;
begin
select REF_STRING into vs_report_server_url from SYS_REF_TABLE where ref_code = 'GEOREPSRVURL';
vs_report_server_url := nvl(vs_report_server_url,'GEOREPSRVURL');
exception
when no_data_found then
insert into SYS_REF_TABLE (REF_CODE,REF_TITLE,REF_STRING) values ('GEOREPSRVURL', 'GeO Report Server URL','dev60cgi/rwcgi60');
commit;
vs_report_server_url := 'dev60cgi/rwcgi60';
when others then
vs_report_server_url := 'dev60cgi/rwcgi60';
end;
report_id:= find_report_object('REPORT1');
if GET_APPLICATION_PROPERTY(USER_INTERFACE) <> 'WEB' then
vc_use_report_server := 'N';
END IF;
if vc_use_report_server = 'N' then
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,SCREEN);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
report_job_id:=run_report_object(report_id,plid);
else
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_EXECUTION_MODE,RUNTIME);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,CACHE);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,vs_report_format);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,rptname);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,vs_report_server);
report_job_id :=run_report_object(report_id,plid);
report_status := report_object_status(report_job_id);

if report_status = 'FINISHED' THEN
IF UPPER(vs_report_format) = 'RTF' THEN
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server) '&mimetype=application/msword', '_blank');
ELSE
Web.Show_Document(vs_report_server_url '/' url_encode2('getjobid' substr(report_job_id,length(vs_report_server) + 2)) '?' url_encode2('server') '=' url_encode2(vs_report_server), '_blank');
END IF;
else
msg('Report failed with message ' report_status chr(13) substr(a_paralist,instr(UPPER(a_paralist),'P_USERNAME'),30) );
end if;
end if;
dbms_application_info.set_module('','');
END;

How to setup Oracle Forms 10g IAS

How to setup Oracle Forms 10g IAS

Login to "Enterprise Manager"
URL : http://localhost:18100/
Default userid = ias_admin

Setting Form servers
Adding user defined icons.jar into d:\oracle\oas10g\forms\java

Try testing the website thru config
URL : http://localhost:7777/forms/frmservlet?config=geo

Setting Report servers
Check the current environment
http://localhost:7777/reports/rwservlet/showenv

Check what options you have
http://localhost:7777/reports/rwservlet

At Application server
Change the conf file
D:\oracle\OAS10G\reports\conf.conf
Uncomment the sourcedir and add the geo directory where the reports is located

At GEO
10200 - System Reference Table
GEOREPSRV =
GEOREPSRVURL = /reports/rwservlet
USEREPSRV = Y

Export data on webForm using DAD

How to export data on webform using DAD?
Using DAD to export data on Oracle webform without using javabean . You need to setup DAD on the Application server first.

Code in the calling oracle forms
if GET_APPLICATION_PROPERTY(USER_INTERFACE) = 'WEB' then
select REF_STRING into a_url from SYS_REF_TABLE where ref_code = 'DAD';
Web.Show_Document(a_url '/stkmas_export?v_org_no=' :global.orgno '&v_stk_flg=S' , '_blank');

Code in the store procedure

PROCEDURE STKMAS_EXPORT AS
cursor c1 is select STK_FLG,STK_C,NAME from stk_mas where org_no = v_org_no and stk_flg = v_stk_flg order by org_no,stk_c;
BEGIN
owa_util.mime_header('text/plain');
for c1rec in c1 loop
htp.p('STK_FLG^STK_C^NAME'); for c1rec in c1 loop htp.p(c1rec.STK_FLG '^' c1rec.STK_C '^' translate(c1rec.name,chr(10),'') '^');
end loop;
END STKMAS_EXPORT;

How to set up Developer reports Server 6.0 on NT

This is a step by step guide that helps you in setting up the Reports Server
6.0 on the NT platform

Set-up Procedure

1. Add the Reports Server to the network configuration file.
   Add a line of the following form to
   
   ORACLE_HOME/net80/admin/tnsnames.ora: = ( ADDRESS= (PROTOCOL=TCP) (HOST=) (PORT=))
   
   where is the name of the Reports Multi-Tier Server instance. If you are using a sqlnet.ora file and default_domain is specified, remember to fully qualify the Reports Multi-Tier Server TNS name with the default domain. For example, if the default_domain is world, the repserver.world would be a valid TNS name. is the IP-adress or the hostname of the Application Server machine, and is the port number to which the Reports Multi-Tier Server is listening (such as, 1949).
   
2. Install the Reports Multi-Tier Server as a NT-service
   Run the following command to install the RMTS as a service (Start - Run):
   
   rwmts60 -install tcpip
   
3. Verify the settings of the service
   For the Reports Multi-tier server to run properly, it must have access to printers. Note that by default, the SYSTEM user does not have access to printers. Therefore, you must either set up a separate user to run the Reports Server or give the SYSTEM user access to printers. Since the latter can prove difficult, it is best to set up a separate user to run the Reports Server. In order to change the start-up account of the service, go to the Control Panel - Services.
   
   Look for the entry "Oracle Reports Server ." Click on Startup. From this startup dialog, select "This Account" on the Log On As section, and type in your operating system username and password. This specifies that the Reports Multi-tier Server is run as you. In addition, you can also set the service to be started automatically (when the system is booted up) or manually. If you install the service to run under a user other than SYSTEM, make sure that the user account:
   · Has the Password Never Expires option selected in the User Manager. · Has membership in the appropriate groups to run the Reports Multi-tier Server and access to the report files. · Has at least print permission to a default printer. · Can log on to a service. Go to Administration Tools User Manager, Policies, User Rights. Check Show Advanced User Rights; choose Log on a Service, and click the Add button.
   
4. Starting/Monitoring/Stopping the service
   To start the service, go to the Control Panel, Services, and select the service, optionally enter command line arguments in Startup Parameters, and click on the Start button. If the service fails to start, check the Reports Server logfile for more information. The file is ORACLE_HOME\report60\ server\.log.
   In order to see if your Reports Server is running, simply go to Task manager, Processes, and check to see if the process ‘rw60mts.exe’ is listed.
   To stop the service, go to the Control Panel, Services, and select the service. Click on the Stop button.
   
5. The Reports Server Configuration file
   A configuration file is created when the Reports Multi-tier Server is first started. The file is ORACLE_HOME\report60\server\.ora. Note that if you manually edit the file, the changes are not picked up by the Reports Multi-Tier Server until you shut it down and restart it. The four optional server command line arguments (minengine, maxengine, initengine, and maxidle) can be specified in the Startup Parameters in the Control Panel Services window to override the settings in the configuration file.
   
   So for the set-up, you can repeat steps 2 through 6 to set up and run multiple Reports Multi-tier Servers on one machine. Make sure that you specify different port numbers.
   
   NOTE: To uninstall the Reports Multi-tier Server service, go to the Control Panel, Services and stop the service. Then run the following command line:
   
   rwmts60 -uninstall
   
   NOTE: When running the Reports Server as a service, there is a known problem with Windows NT not sending report output to mail (DESTYPE=MAIL). Microsoft plans to fix this in Microsoft Exchange Server 5.0. In the meantime, you can get around this problem by running the Reports Server not a service.
   
   

2.2.2 Installing the Reports Server as a non-service

1. Be sure the PATH contains ORACLE_HOME/bin.
   Go to the Control Panel, System. and verify the value of the system variable PATH contains the ORACLE_HOME/bin directory.
   
2. Add the Reports Server to the network configuration file.
   Add a line of the following form to ORACLE_HOME/net80/admin/tnsnames.ora: = ( ADDRESS= (PROTOCOL=TCP) (HOST=) (PORT=))
   where is the name of the Reports Multi-Tier Server instance. If you are using a sqlnet.ora file and default_domain is specified, remember to fully qualify the Reports Multi-Tier Server TNS name with the default domain. For example, if the default_domain is world, the repserver.world would be a valid TNS name. is the IP-adress or the hostname of the application server machine, and is the port number to which the Reports Multi-Tier Server is listening (such as, 1949).
   
3. Start the Reports Multi-tier Server
   Run the following command to start the reports server manually.
   Rwmts60 -listen name= [minengine= maxengine= initengine= maxidle=]
   You can use Task manager, Processes to ensure that the reports server is running.
   4. Stopping the Reports Multi-tier Server
   If the listener is running, the Task Manager will display a process called RWMTS60.EXE. Select RWMTS60.EXE and click End Process.
   
   2.2.3 The Reports Server Configuration File
   The configuration file specifies runtime parameters for the Reports Multi-tier Server. When the Reports Multi-tier Server is started, it gets a TNS name from the installation or the command line. The server will listen to this TNS address for RPC calls. The server then looks for a file that is named after the TNS name with .ora as the file extension and that is located in the server subdirectory in ORACLE_HOME\REPORT60\SERVER. If the configuration file is not present, a default one is created. Decide for yourself if this file requires modification. Note that your changes only take effect if you restart the Reports Server. Note that because of its importance, you should restrict access to the configuration file.
   The format of the configuration file is:
   identifier=
   maxconnect=
   sourcedir=
   cachedir=
   tempdir=
   cachesize=
   minengine=
   maxengine=
   initengine=
   maxidle=
   security=
   englife=
   
   If two servers are running on the same Windows machine, they will have to share the same ORACLE_HOME, REPORTS60_PATH, and REPORTS60_TMP. However, each server listens to a different TNS name and therefore, has a unique configuration file, that can specify different sourcedir, cachedir, and tempdir settings, optionally on different drives. The minengine, maxengine, maxidle, and cachesize settings can be viewed and changed from the queue manager. The queue administrator userid and password can also be changed. The configuration file can be overwritten by the server process when these settings change or when it needs to. Any optional arguments on the command line override the settings in the configuration file.
   
   Some remarks on the syntax in the configuration file: strings must be quoted if they contain spaces, and do not put spaces around the equal sign. (Follow the rules of an Oracle Reports command file.)

User Defined Metrics

Oracle Enterprise Manager 10g

You can monitor a OS process using Oracle Enterprise Manager. Here are the steps which I use to monitor the memory usage on a Linux box.

Steps

1. write a script eg "getmem.sh"


2. export em_result = `freegrep Memawk {'print $3'}`
   echo em_result=$em_result


3. Login to Grid Control > Targets > > User-Defined Metrics


4. Create an User Defined Metric to run the shell script.

Note : you need to use "em_result=....." where em_result is the keyword.

Inserting Custom Messages in Oracle Alert/Trace files

DBMS_SYSTEM package that allows us to insert our own customized messages in Alert log and/or trace files. I tried this feature and found it to be quite powerful as regards its usage.

Routines in DBMS_SYSTEM package
The following routines can be used to write vital information to Alerts and Trace files.

• KSDIND:
  Does an indent in the form of inserting colons (:) before the next write is carried out. The indent levels are from 0 to 30. This option could be used to separate the custom messages from the existing oracle messages. For example, I could search the Alert Log for messages starting with 5 colons to identify Application generated messages. The default is 0. This process fails to work in some cases (mentioned below). As a safety, I also add a standard prefix to all messages to identify application related messages.
  
• KSDWRT:
  Writes a message to the alert file. The first parameter DEST takes input as 1 (write to trace file), 2 (write to alert log) or 3 (write to both trace and alert log files). The second parameter TST is the actual text message that should go into the file(s).
  

Example

SQL> exec dbms_system.ksdwrt(2, 'testing for alert log writing');

http://www.dbasupport.com/oracle/ora9i/custom_messages.shtml

Dynamic Views

V$SGA	The V$SGA view is useful in determining how much total memory is allocated to the various componentsof the SGA. The following simple query gives you a summary of the SGA memory usage bythe current instance:	SQL> SELECT * FROM V$SGA;
V$SGASTAT	The V$SGASTAT view gives you a detailed breakdown of the SGA memory. It shows you currentmemory allocations broken down into the following main areas:	SQL> SELECT bytes from v$sgastat2 WHERE pool='shared pool' and3 V$SGA name='free memory'
V$SESSION	The V$SESSION view gives you a wealth of information about the users, including their operatingsystem username, terminal name, whether they’re actively executing a transaction or just connectedto the database, and how long their connection has been in place. In Oracle Database 10g,the V$SESSION view also contains several wait-related columns such as WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, and SECONDS_IN_WAIT.
V$SESSION_LONGOPS	The V$SESSION_LONGOPS view shows the status of all operations that run for a long time (morethan six seconds in absolute time). The columns SOFAR and TIME_REMAINING indicate how much ofthe work is done and how long the operation has to go before completing. The following is a samplequery using the view:	SQL> SELECT sid, opname, sofar,totalwork,2 start_time, time_remaining3* FROM V$SESSION_LONGOPS;
V$LOGFILE	The V$LOGFILE view provides information about each redo log file, including its name andwhether the file is valid or not. The STATUS column has the following values:	SQL> SELECT * FROM V$LOGFILE;
V$ARCHIVED_LOG	The V$ARCHIVED_LOG view is essential when you’re looking at information regarding whicharchive logs you have access to. The view contains one entry for every log that your databasearchives. When you restore an archive log, the operation inserts one row	SQL> SELECT name, thread#, sequence#,2 archived, applied, deleted, completion_time3* FROM V$ARCHIVED_LOG;
V$ARCHIVE_DEST	As its name indicates, the V$ARCHIVE_DEST view shows you each archive log destination and itsstatus. This view has a large number of columns, and you need to pay special attention to the followingcolumns:	SQL> SELECT dest_name2 FROM V$ARCHIVE_DEST;
V$SYSSTAT	The V$SYSSTAT view provides you with all the major system statistics: parse statistics, executionrates, full table scans, and other performance indices. The V$SYSSTAT view provides you with thebuffer-cache hit ratios and a number of other hit ratios. Listing 23-31 shows a summary of the mainclasses of statistics contained in the V$SYSSTAT view.	SQL> SELECT * FROM V$SYSSTAT;
V$OSSTAT	The new V$OSSTAT view comes in handy when you wish to check system usage statistics.

Setting Up Standby Database

When you create your STANDBY database, you'll need to create directories for database administration files, database files, and archive logs. You'll also need to prepare the standby instance by copying and configuring a parameter file, creating a password file, and creating Windows services on Windows


Standby init.ora file

db_name = PRACTICE
instance_name = STANDBY
service_names = STANDBY
control_files = ("/oradata/STANDBY/standby.ctl)
log_archive_dest_l = 'location=/oracata/STANDBY/archive'
LOG_ARCHIVE_DEST_2 = "MANDATORY service=STANDBY reopen=30"
standby_archive_dest = "/oradata/STANDBY/archive"
background_dump_dest = /app/oracle/admin/STANDBY/bdump
user_dump_dest = /app/oracle/admin/STANDBY/udump
db_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY" log_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY"
lock_name_space = STANDBY

Mount standby database

LINUX> export ORACLE_SID=STANDBY;
LINUX> sqlplus /nolog
SQL> CONNECT sys/standby AS SYSDBA;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE

Recover standby database
SQL> RECOVER MANAGED STANDBY DATABASE;

If your physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for a log switch boundary and for the standby redo log to be archived. This new functionality is called real-time apply.

SQL> Recover managed standby database using current logfile;

Activate the Standby Database

1. Cancel standby database
   SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
   
2. Activate Standby databse
   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
   SQL> SHUTDOWN;
   SQL> STARTUP;

Steps : For Failover

1. Perform terminal recovery on the standby by issuing managed recovery with the FINISH keyword. The following command is to be used if you have
   
   alter database recover managed standby database finish;
2. If you do not have standby redo logs, or they are not active, you must enter the following command:
   
   alter database recover managed standby database finish skip standby logfile;
3. Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:
   
   alter database commit to switchover to primary;
4. Step 5. Restart the new primary database.

Using Rman to create standby database


RMAN > connect target /
RMAN > run {
backup database
include current controlfile for standby;
sql "alter system current log file";
}


RMAN > connect auxiliary /
RMAN > Duplicate target database for standby dorecover;

Checking archive log statusPrimary
SQL > select status, error from v$archive_dest;

Standby
SQL > select * from v$standby_log;

Confirm Managed Recovery
How can you know if the managed archive propagation process is running properly? You can look in three places:.

1. Archive files Look at the STANDBY database archive destinationfor archive logs being transmitted from the primary. New archivelog files on the PRACTICE database will be reproduced in the/oradata/STANDBY/archive directory.
   
   
2. Standby alert log Check the STANDBY alert.log for archive logs applicationentries. If you haven't seen any activity yet, perform a few log switches on thePRACTICE database. Wait a few minutes and look for evidence that the newarchive logs were transported and applied.
   
   
3. Media Recovery Log /oradata/STANDBY/archive/71.arc Media Recovery Waiting for thread 1 seqtt 72
   Log history The third and final test is to select from v$log_history onboth the primary and standby databases. The following query should return the same number, bearing in mind that the standby might be afew seconds behind:.
   
   SQL> SELECT MAX(sequence^) FROM v$log_history;

Net8 Connect-Time Failover

You can use connect-time Net8 failover to cause clients to connect to a backup instance in cases where the primary instance cannot be reached. This makes the most sense in an OPS (Oracle Parallel Server) environment where multiple instances are all accessing the same database. However, it can be done in a non-OPS environment as well. If you are using Oracle's standby database feature, you can configure a net service name so that clients connect to the standby database whenever the primary database is unreachable. Similarly, you could connect to a backup database maintained using Oracle's replication features.

Dynamically registering global database names with your listeners
One important issue to be aware of is that connect-time failover only works if you are dynamically registering global database names with your listeners. If you are statically configuring global database names, then connect-time failover will not work in a consistent manner:

If you want to use Net8's connect-time failover feature, you need to delete the GLOBAL_DBNAME parameter and allow the database to register itself with the listener automatically. You can list the database in your SID_LIST; you just can't include the GLOBAL_DBNAME parameter.

Listener.ora on ARK1
LSNR817 =

(DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521)) )

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = ARK1)) ) )
SID_LIST_LSNR817 = (SID_LIST = (SID_DESC = (ORACLE_HOME = D:\Oracle\Product\8.1.7) (SID_NAME = ARK1) )

)

Listener.ora on DIA3
LSNRDIA3 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.138.21)(PORT = 1523)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = DIA3)) ) )
SID_LIST_LSNRDIA3 = (SID_LIST = (SID_DESC = (ORACLE_HOME = /opt/oracle/product/8.1.7) (SID_NAME = DIA3) ) )

Failover Configuration in Tnsnames.ora on Net8 Client
PROD.WORLD =

(DESCRIPTION_LIST =

(FAILOVER = TRUE)

(LOAD_BALANCE = FALSE)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521))

(CONNECT_DATA = (SERVICE_NAME = ARK1)

(SERVER = DEDICATED)

)

)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1523))

(CONNECT_DATA = (SERVICE_NAME = DIA3)

(SERVER = DEDICATED)

)

)

)

When you connect using the service name PROD, Net8 will first try to connect to the database service ARK1 on the host arkum. If that connection fails, Net8 will move on to the database service DIA3 on the host named diamond.

Notice that the description list contains both (FAILOVER = true) and (LOAD_BALANCE = false). (FAILOVER = true) still represents the default behavior. It's included here to make it clear that failover is being used. (LOAD_BALANCE = false), however, does not represent the default behavior in this case. It's included to disable client load balancing, which is enabled by default whenever multiple descriptions are being used. With client load balancing enabled, Net8 would randomly choose descriptions from the description list. By disabling client load balance iou ensure that Net8 tries each DESCRIPTION in the order in which it appears in the list.

Using Materialized Views

The use of materialized views, or snapshots as they were previously known, is the simplist way to achive replication of data between sites. The materialized view is a table whose contents are periodically refreshed using a query against a remote table

Query Rewriting
The QUERY_REWRITE_ENABLED initialization parameter determines whether Oracle will rewrite a queryor not. The default value for this parameter is FALSE,

Refresh Mode
You can choose between the ON COMMIT and ON DEMAND modes of data refresh.

• ON COMMIT: In this mode, whenever a data change in one of the master tables is committed,the materialized view is refreshed automatically to reflect the change.
• ON DEMAND: In this mode, you must execute a procedure like DBMS_MVIEW.REFRESH to updatethe materialized view.The default refresh mode is ON DEMAND.

Refresh Type

You can choose from the following four refresh types:

• COMPLETE: This refresh option will completely recalculate the query underlying the materializedview. Thus, if the materialized view originally took you 12 hours to build, it’ll take aboutthe same time to rebuild it. Obviously, you wouldn’t want to use this option each time a fewrows are modified, dropped, or inserted into your master tables.
• FAST: Under the fast refresh mechanism, Oracle will use a materialized view log to log allchanges to the master tables. It’ll then use the materialized view log to update the mastertables, thus avoiding a complete refresh of the view. You can use other techniques toperforma fast refresh, but the materialized view log is the most frequently used devicefor this purpose.

Creating Materialized Views

SQL> GRANT CREATE DATABASE LINK TO scott;
SQL> GRANT CREATE MATERIALIZED VIEW TO scott;
SQL> GRANT QUERY REWRITE TO scott;

Creating the Materialized View Log

Let’s use the FAST refresh mechanism for our materialized view. This will require the creation of twomaterialized logs, of course, to capture the changes to the two master tables that are going to be thebasis for our materialized view. Here’s how you create the materialized view logs:Here’s how you create the materialized view log:

SQL> CREATE MATERIALIZED VIEW LOG ON products;
SQL> CREATE MATERIALIZED VIEW LOG ON sales;

SQL> CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE REFRESH FORCE
ON DEMAND AS SELECT * FROM emp@tsh1.world;

Initialization Parameters

Automatic Undo Management

Prior to Oracle 9i, a DBA had to manage rollback tablespaces and rollback segments manually. Failure to allocate enough segments, or to allocate enough space for those segments, would invariably leads to "ORA-01555: snapshot too old" error during long transactions. Since the advent of 9i, that worry can, and should, largely be eliminated.
3 new initialization parameters were added: UNDO_MANAGEMENT, UNDO_RETENTION, and UNDO_TABLESPACE.

To activate automatic undo management at least one undo tablespace exists.
set the UNDO_MANAGEMENT = AUTO
set the UNDO_RETENTION = 0 (zero),

Oracle will automatically tune for maximum retention of undo information based on the space available in the target undo tablespace, with the caveat that this automatic tuning mechanism will never tune for less than 15 minutes of retention.

Automatic Memory Tuning

Two new parameters, WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET

WORKAREA_SIZE_POLICY = TRUE
PGA_AGGREGATE_TARGET > 0 (zero)

In previous releases, or when not using the new automatic PGA tuning ability, a DBA had to carefully tune the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE parameters to achieve optimal sort and join performance.

With automatic PGA tuning enabled, a process's needs shrink and grow, so does its PGA.
The recommended starting point for PGA_AGGREGATE_TARGET on an online transaction processing (OLTP) system is 16% of physical memory, and for DSS systems, it is 40% of physical memory.

Metalink Note 223730.1 suggests querying the V$SQL_WORKAREA_ACTIVE view to determine if any PGA work areas are undersized, resulting in writes to temporary segments.

SELECT
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM
V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

The goal is to have a cache hit ratio as close to 100% as possible, and to have zero processes overallocating their PGA.

Optimizer (CBO)

Some of the parameter values affect the decision of CBO. They should be change if necessary.

Default values during installation

• optimizer_index_caching=0
• optimizer_index_cost_adj=100

optimizer_index_caching=0 means “you don’t normally have any index blocks cached in RAM”(percent-value) . It should be around : 80-90

optimizer_index_cost_adj=100 means “index-access is just as expensive as full table scans” It should be about: 20-30 (i.e. cost is 1/5 or so)

Use GATHER_SCHEMA_STATS instead of Analyze Table

GATHER_SCHEMA_STATS( ownname=>’GEO’, cascade=>TRUE, method_opt=>’FOR ALL INDEXED COLUMNS SIZE AUTO’);

• cascade : analyzes indexes,
• method_opt : controls histogram generation,

Cloning a Database

I routinely refresh development and test databases, and they will sometimes need to clone databases to test backup and recovery strategies.

• By using the RMAN DUPLICATE command
• By using the OEM Database Control
• By manually performing the copy with SQL

Using RMAN to Clone a Database
RMAN provides the DUPLICATE command, which uses the backups of a database to create a newdatabase. The files are restored to the target database, after which an incomplete recovery is performedand the new database is opened with the OPEN RESETLOGS command.

1. Create a new init.ora file for the auxiliary database. The init.ora file should have the following parameters, with the data files and log file parameters changed to ensure that theoriginal database files arent used for the new database:
   
   - DB_FILE_NAME_CONVERT
   - LOG_FILE_NAME_CONVERT
2. Start the target database instance.
   
   SQL > startup nomount
3. Connect the recovery catalog to the target database and the auxiliary database
   
   RMAN > CONNECT target / catalog rman/rman1@catalog_db auxiliary sys/password@auxiliary_db
4. Issue the RMAN DUPLICATE command, as follows:
   
   RMAN> DUPLICATE TARGET DATABASE TO auxiliary_db
   pfile =/u01/app/oracle/10.2.0/db_1/dbs/init_auxiliary_db;
5. Opens the duplicated database with the RESETLOGS
   
   SQL> alter database open resetlogs

Manually Cloning a Database
To clone a database manually, you need to first use the operating system to copy all of the source database files to the target location.

1. Copy the prod database files to the target location.
2. Prepare a text file for the creation of a control file for the new database as follows:
   
   SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
   
3. On the target location, create all the directories for the various files.
   
4. change SID, path, of the backup control trace file controlfile_open.sql
5. Run the following command
   
   SQL> startup nomount;
   SQL> @controlfile_open.sql