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

Blocking Locks

Blocking Locks

A blocking lock occurs when a lock placed onfrom accessing the same object or objects. The information—it tells you which sessions are currentlyobject is presently waiting. You can combine in the V$SESSION tables, to find out who is holding

SQL> SELECT a.username, a.program, a.sid,
FROM v$session a, dba_blockers b
WHERE a.sid = b.holding_session;

DBA_BLOCKERS and DBA_WAITERS

SQL> SELECT waiting_session, blocking_session, lock_type
FROM DBA_BLOCKERS;

For 10g there is additional columns to check blocking session

select lpad(' ',3*(level-1)) SID SID, USERNAME, TERMINAL, CLIENT_INFO, EVENT
from V$SESSION
START WITH BLOCKING_SESSION_STATUS='VALID'
connect by prior BLOCKING_SESSION = SID

Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo entries');

If the ratio (redo log space requests / redo entries) is greater than 1:5000, then increase the size of the redo log buffer until the space request ratio stops falling.
Alternately, if memory is not a constraint then try to reduce the number of times that a process had to wait for the log cache to be flushed:

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.
If you want to know how long processes had to wait as well as the number of times then try the following script instead:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning.

Link of Oracle tuning
http://www.cryer.co.uk/brian/oracle/tuning.htm

Automatic Startup Scripts on Linux

Automatic Startup Scripts on Linux

Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:
chmod 750 /etc/init.d/dbora

Link the file into the appropriate run-level script directories:
ln -s /etc/init.d/myservice /etc/rc0.d/K10dbora
ln -s /etc/init.d/myservice /etc/rc3.d/S99dbora

Associate the myservice service with the appropriate run levels:
chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.

Method 2

Create a script at /usr/local/bin with the following information (eg dbora)

/home/oracle/Orahome1/bin/lsnrctl start
sqlplus /nolog << EOF
conn / as sysdba;
startup;
exit;
EOF

Add the following lines into /etc/rc.d/rc.local
su - oracle -c "/usr/local/bin/startdb"

Good Solaris link

http://www.softpanorama.org/Net/Solaris/network_configuration.shtml

Statspack

All about Statspack

Installing Statspack

Run the ‘spcreate.sql’ script using SQL*Plus as user SYS. User PERFSTAT is created by this script, owning all objects needed by the statspack package.

SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

Removing Statspack
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spdrop

Taking Snapshot
Interactive way to take a snapshot

SQL> connect perfstat/perfstat
SQL> execute statspack.snap;

Automatically gather StatsPack snapshots
To use an Oracle-automated method for collecting statistics, you can use dbms_job. A sample script on how to do this is supplied in spauto.sql, which schedules a snapshot every hour, on the hour.

• change snapshot interval
  execute dbms_job.interval(,'SYSDATE+(1/48)');
  
• remove the autocollect job,
  execute dbms_job.remove();

To gather a STATSPACK report
@%oracle_home%/rdbms/admin/spreport

Some free statspack analysis report

http://www.statspackanalyzer.com/analyze.asp

Export / Import in Oracle

Export to multiple files

Tip : As of Oracle 8i ,Export file Greater than 2GB is not a problem

Syntax
exp tables= file= filesize=

Eg
C:\>exp sam/dba_pass tables=EMPTEST file=(exp1.dmp,exp2.dmp,exp3.dmp) filesize=1000M

IMPORT from more then one dump file


Syntax
imp tables= file=(file1.dmp ,file2.dmp...) filesize=

Caution : FILESIZE value in imp should match with FILESIZE value of Export eg in our case 1000M

for eg

C:\>imp sam/dba_pass tables=EMPTEST file=(exp1.dmp,exp2.dmp,exp3.dmp) filesize=1000M ignore=Y