Saturday, October 3, 2009

ORA-01031: insufficient privileges

ORA-01031: insufficient privileges
============================
ABOVE ERROR MAY OCCUR IN FOLLOWING SITUATIONS...

1. Absent/corrupted PASSWORD FILE (Remote connection)
===================================

The issue here is the ablity to connect to your instance remotely as
sysdba; this requires

1. a password file generated by orapwd and

2. an init.ora parameter, remote_login_passwordfile, set to shared or
exclusive .If this parameter is set to NONE then no password file is utilised and no possibility exists of a remote login with sys as sysdba.

To solve the problem:

Delete old password file manually(OS delete).
then create new one.

to create a password file :

C:\Documents and Settings\user> orapwd file=G:\oracle\product\10.2.0\db_1\database\PWDBEFTN.ora password=sys entries=5


2. Lack of privileges
====================
In most cases, the user receiving this error lacks a privilege to create an object (such as a table, view, procedure and the like).
Grant the required privilege .

grant create table to user;


3. Startup
===========
If someone receives this error while trying to startup the instance,
the logged on user must belong to the ora_dba group on Windows or dba group on Unix.
To add a user to the ora_dba group on Windows, net localgroup should help:
C:\> net localgroup ora_dba rene /add

4. In windows (Local machine)
=====================
Windows does this on occasion, usually when domain accounts are used.
Ensure you are using a local user account (even domain accounts allow local
logins when the network is absent)

What is XML Schema

What is XML Schema: A brief introduction-
----------------------------------------
An XML schema defines the structure of an XML document by setting constraints on the data types of elements and attributes in the document. XML documents can be validated against the XML schema to ensure they conform to the constraints defined in the schema. There are several schema languages that define the grammar for an XML document, for instance, Document-Type Definition (DTD) language, and XML Schema language. Of these languages, because of its extensive support for data types and namespaces, XML Schema is becoming the most widely used schema language;


XML documents are increasingly being used as a format of data exchange. But for an XML document to be acceptable to different developers/users, the XML document should conform to a standard structure. XML Schema is an XML-based representation of the structure of an XML document. Through its support for datatypes and namespaces, XML Schema has the potential to provide the standard structure for XML elements and attributes. However, to check if an XML document conforms to an XML Schema, the document must be validated against that XML Schema.

Three procedure for performing that validation using parsers for the

1.Simple API for XML (SAX)
2.Document Object Model (DOM)
3.An XML Schema Design (XSD) validator.

functional key in 10g like 6i

migrating the oracle forms from version 6i to 10g.

In forms 10g the functional key like 6i then you need to do in window platform

1) Stop OC4J
2) Rename FRMWEB.RES TO FRMWEB_ORIG.RES,
3) COPY FRMPCWEB.RES TO FRMWEB.RES
4) Start OC4J

like:
F10 = save
F9 = List of value
F8 = execute query

Friday, October 2, 2009

how to create virtual drive in windows

The drive letter you assign represents a virtual drive, because you can use the drive letter in commands as if it were a physical drive

Syntax
----------
SUBST /?

SUBST [drive1: [drive2:]path]

SUBST drive1: /D

Display names of the virtual drives in effect:

SUBST

example:
---------

This command creates a virtual drive Z for the path d:\halim:

SUBST Z: d:\halim

Now, instead of typing the full path, you can reach this directory by typing the letter of the virtual drive, followed by a colon, as in:

z:

This example works only if you have included the line LASTDRIVE=Z in your CONFIG.SYS file to define Z as the highest letter that the operating system recognizes as a disk drive. For more information, see the LASTDRIVE command.

how to Install report server in 10g

cmd> rwserver -install report_ser1

cmd> rwserver server=report_ser1 start

cmd> rwserver server=report_ser1 stop

cgicmd.dat Mapping file for Oracle Reports 10g

cgicmd.dat Mapping file for Oracle Reports
------------------------------------------------

execsql: report=execsql.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440 %P

execsql2: report=execsql.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440 P_REGION=%1 P_CITY=%2 P_TOP_VIDEOS=%3 P_SECOND_USER_DETAILS=%4 %*

; Runtime Customizations example
custom: report=video_custom.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS pagestream=YES tolerance=1440 %P

; Chart Hyperlinking example
chart1: report=repchart1.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440 pagestream=yes %*

;Drill down report from repchart1
chart2: report=repchart2.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS pagestream=yes tolerance=1440 %*

;XML Output example
xml: report=xml.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS pagestream=yes tolerance=1440

;XML Output from HTMLCSS hyperlink
xmloutput: report=xml.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=XML tolerance=1440

;Bookmarks Example
bookmarks: report=bookmark.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=pdf tolerance=1440

;High quaility invoice
invcheck: report=invcheck.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=PDF tolerance=1440

;News Letter
newslet: report=newslet.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440

;Letter with check for current date
checklet: report=checklet.rep server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440

;High quality account statement
rfund30: report=rfund.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440


; EXEC_SQL example
ejemplo1: report=ejemplo1.rdf server=Rep60_COMPAQ userid=demos/demos@ora8 destype=cache desformat=HTMLCSS tolerance=1440 %P

Monday, September 28, 2009

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.
---------------------------------------------

-------TO ABOID following message when export data.



cmd> set  NLS_LANG=.WE8MSWIN1252 SQL>

example:



C:\Users\Halimm>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 14 16:36:03 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> $exp icms_stage\icms_stage file=C:\halim_documents\icms2_dump\icms_stage_14022014.dmp owner=icm

Export: Release 10.2.0.5.0 - Production on Fri Feb 14 16:36:15 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ICMS_STAGE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ICMS_STAGE
About to export ICMS_STAGE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ICMS_STAGE's tables via Conventional Path ...
. . exporting table                        ADDRESS       2384 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table          AVAILABILITY_CALENDAR       2213 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table           BACKUP_SECURITY_USER       2372 rows exported
. . exporting table                        COMPANY       2384 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        CONTACT       2384 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    COUNTY_TYPE        161 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                  DIVISION_TYPE         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table           FUNCTIONAL_ROLE_TYPE         43 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table           FUNCTIONAL_USER_ROLE        451 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                   HOLIDAY_TYPE         64 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                 LEAVE_CALENDAR        231 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        MIG_LOG         26 rows exported
. . exporting table   MIG_ROLE_TYPE_IS_JUDGE_SECRE         78 rows exported
. . exporting table MIG_SECURITY_ROLE_TYPE_DETAILS         92 rows exported
. . exporting table           OFFICE_LOCATION_TYPE         10 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                  POSITION_TYPE         33 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                SECURITY_DETAIL       1900 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table              SECURITY_QUESTION          3 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table             SECURITY_ROLE_TYPE         39 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                  SECURITY_USER       2385 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                  SITE_CALENDAR       6831 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    SITE_COUNTY        527 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      SITE_SLOT      40105 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      SITE_TYPE         98 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                      SLOT_TYPE          8 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     STATE_TYPE         50 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                    USER_COUNTY       1058 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                USER_CREDENTIAL       2242 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     USER_GROUP        111 rows exported
. . exporting table               USER_STATUS_TYPE          6 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

SQL>
SQL>
SQL>
SQL>
SQL> exit

C:\Users\Halimm>set NLS_LANG=.WE8MSWIN1252
C:\Users\Halimm>
C:\Users\Halimm>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 14 16:36:03 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> $exp icms_stage\icms_stage file=C:\halim_documents\icms2_dump\icms_stage_14022014.dmp owner=icm

Export: Release 10.2.0.5.0 - Production on Fri Feb 14 16:36:15 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ICMS_STAGE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ICMS_STAGE
About to export ICMS_STAGE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ICMS_STAGE's tables via Conventional Path ...
. . exporting table                        ADDRESS       2384 rows exported
. . exporting table          AVAILABILITY_CALENDAR       2213 rows exported
. . exporting table           BACKUP_SECURITY_USER       2372 rows exported
. . exporting table                        COMPANY       2384 rows exported
. . exporting table                        CONTACT       2384 rows exported
. . exporting table                    COUNTY_TYPE        161 rows exported
. . exporting table                  DIVISION_TYPE         14 rows exported
. . exporting table           FUNCTIONAL_ROLE_TYPE         43 rows exported
. . exporting table           FUNCTIONAL_USER_ROLE        451 rows exported
. . exporting table                   HOLIDAY_TYPE         64 rows exported
. . exporting table                 LEAVE_CALENDAR        231 rows exported
. . exporting table                        MIG_LOG         26 rows exported
. . exporting table   MIG_ROLE_TYPE_IS_JUDGE_SECRE         78 rows exported
. . exporting table MIG_SECURITY_ROLE_TYPE_DETAILS         92 rows exported
. . exporting table           OFFICE_LOCATION_TYPE         10 rows exported
. . exporting table                  POSITION_TYPE         33 rows exported
. . exporting table                SECURITY_DETAIL       1900 rows exported
. . exporting table              SECURITY_QUESTION          3 rows exported
. . exporting table             SECURITY_ROLE_TYPE         39 rows exported
. . exporting table                  SECURITY_USER       2385 rows exported
. . exporting table                  SITE_CALENDAR       6831 rows exported
. . exporting table                    SITE_COUNTY        527 rows exported
. . exporting table                      SITE_SLOT      40105 rows exported
. . exporting table                      SITE_TYPE         98 rows exported
. . exporting table                      SLOT_TYPE          8 rows exported
. . exporting table                     STATE_TYPE         50 rows exported
. . exporting table                    USER_COUNTY       1058 rows exported
. . exporting table                USER_CREDENTIAL       2242 rows exported
. . exporting table                     USER_GROUP        111 rows exported
. . exporting table               USER_STATUS_TYPE          6 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

SQL>


Install Oracle Database 10g R2 on Solaris [SPARC]

Install Oracle Database 10g R2 on Solaris [SPARC]

Unzip the files:
==========================

root# unzip 10202_database_solx86.zip

(You should now have a single directory called "database" containing installation files.)

================================
Open the "/etc/hosts" file edit:
================================

root# vi /etc/hosts





=================================
Open the "/etc/system" file edit:
=================================

root# vi /etc/system

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax= 25769803776
set shmsys:shminfo_shmmni=100

================================
Create the new groups and users:
================================

root# groupadd oinstall
root# groupadd dba
root# useradd -g oinstall -G dba -d /export/home/oracle oracle
root# mkdir /export/home/oracle
root# chown -R oracle:oinstall /export/home/oracle
root# passwd -r files oracle
root# projadd oracle

=======================================================
Append the following line to the "/etc/user_attr" file:
=======================================================

root# vi /etc/user_attr

oracle::::project=oracle


====================================================================
Add the lines to the "/etc/project" file with the following command:
====================================================================

root# projmod -s -K "project.max-shm-memory=(priv,25769803776,deny)" oracle


=========================================================
To determine whether the required packages are installed:
=========================================================

root# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

root# pkgadd -d /cdrom/sol_10_106_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

===========================================================================
Create oracle home (added by halim)
==========================================================================
Create the directories in which the Oracle software will be installed:

oracle$ mkdir -p /d02/oracle/ora102

chown -R oracle:oinstall /d02


make all neccessary drive/folder for datafiles and give permission (user:- oracle group:- oinstall )
to all oracle datafile folder,oracle installation folder

==========================================================================

=====================
Edit ".profile" file:
=====================

Oracle$ vi .profile

ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_BASE=/d02; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/oracle/ora102; export ORACLE_HOME
PATH=/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/bin/X11R6:/usr/openwin/bin:$ORACLE_HOME/bin:$HOME/bin:.; export PATH
ORACLE_PATH=$ORACLE_HOME/bin:.; export ORACLE_PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE/lib:$ORACLE_HOME/jlib; export CLASSPATH
ORACLE_SID=stlbas; export ORACLE_SID
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
set TWO_TASK $ORACLE_HOME/bin
TMP=/tmp; export TMP
TEMP=/tmp; export TEMP
TMPDIR=$TMP; export TMPDIR

=================================
Install the oracle software
=================================
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=:0.0; export DISPLAY

==========================
oracle$ ./runInstaller


$ ./runInstaller -ignoreSysPrereqs


============================
post installation work
===========================
================================================================
In "/var/opt/oracle/oratab" file modify for auto start Database:
================================================================

root# vi /var/opt/oracle/oratab

stlbas:/d02/oracle/ora102:Y


=========================================================
Create a file "/etc/init.d/dbstart" with following lines:
=========================================================

root# vi /etc/init.d/dbstart


# Start the Oracle Database and listeners
echo
echo "################################################################################"
echo "# Begin of O R A C L E startup section #"
echo "################################################################################"

echo
echo " WELCOME TO BANK DATABASE"
echo


su - oracle<<<<

root# mv [from] [to]

root# cp [from] [to]

root# mkdir

root# rmdir

root# df -k [Display mount point]

root# mount [Display mounted device]

root# umount

root# cat

root# kill -9 PID

root# prtconfgrep -i mem [Display RAM size]

root# ipcs -pmb [Display allocated memory segment]

root# psrinfo -v [Display the no of CPU]

root# sar -u 2 5 [Display CPU report, 1st argument time interval in second, 2nd argument no. of sample]

root# sar -w 5 5 [Memory switching and swaping]

root# sar -b 1 6 [buffer activity]

root# vmstat 5 3 [Various server values, 1st argument time interval in second, 2nd argument no. of sample]

root# mpstat 10 2 [per processor report, 1st argument time interval in second, 2nd argument no. of sample]

root# w [Server load average]

root# iostat 3 [I/O against physical disk in time interval]

===============
NFS Mount (Sun)
===============

The following deamons must be running for the share to be seen by a PC:
-----------------------------------------------------------------------

root# /usr/lib/nfs/nfsd -a
root# /usr/lib/nfs/mountd
root# /opt/SUNWpcnfs/sbin/rpc.pcnfsd


To see a list of the nfs mounted drives already present type:
-------------------------------------------------------------

root# exportfs

First the mount point must be shared so it can be seen by remote machines:
--------------------------------------------------------------------------

root# share F nfs o ro /cdrom

Next the share can be mounted on a remote machine by root using:
----------------------------------------------------------------

root# mkdir /cdrom#1

root# mount o ro myhost:/cdrom /cdrom#1

============
Useful Files
============

/etc/passwd [User settings]
/etc/group [Group settings for users]
/etc/hosts [Hostname lookup information]
/etc/system [Kernel parameters]



===================================================================


====user_attr.sh
#
# Copyright 2007 Sun Microsystems, Inc. All rights reserved.
# Use is subject to license terms.
#
# /etc/user_attr
#
# execution attributes for profiles. see user_attr(4)
#
#ident "@(#)user_attr 1.1 07/01/31 SMI"
#
#
adm::::profiles=Log Management
lp::::profiles=Printer Management
postgres::::type=role;profiles=Postgres Administration,All
oracle::::projct=oracle
root::::auths=solaris.*,solaris.grant;profiles=Web Console Management,All;lock_after_retries=no


======project.sh

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
oracle:100::::project.max-sem-ids=(priv,256,deny);project.max-shm-memory=(priv,25769803776,deny)

====hosts.sh
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
10.11.1.80 sundb loghost

=====system.sh need add following line

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax=25769803776
set shmsys:shminfo_shmmni=100

=====dbstart.sh

# Start the Oracle Database and listeners
echo
echo "################################################################################"
echo "# Begin of O R A C L E startup section #"
echo "################################################################################"

echo
echo " WELCOME TO DATABASE"
echo


su - oracle<
lsnrctl start

sqlplus /nolog<connect / as sysdba
startup
EOS

echo
echo " database started..."
echo

#emctl start dbconsole
#isqlplusctl start

EOO

echo
echo "################################################################################"
echo "# End of O R A C L E section #"
echo "################################################################################"
echo


=========================dbstop.sh

# Stop the Oracle Database and linteners
echo
echo "################################################################################"
echo "# Begin of O R A C L E shutdown section #"
echo "################################################################################"


echo
echo " HAVE A NICE DAY"
echo


su - oracle<
lsnrctl stop

sqlplus /nolog<connect / as sysdba
shutdown immediate

EOS

echo
echo " database stoped..."
echo

#emctl stop dbconsole
#isqlplusctl stop

EOO

echo "################################################################################"
echo "# End of O R A C L E section #"
echo "################################################################################"
echo

Auto start listener

#
# Set ORACLE_HOME_LISTNER
# Set this to enable auto startup for Oracle Net Listener.
# The script uses this find the binary to start Net Listener.
#
# Also, if the listener name is not the default name LISTENER, you must
# specify the listener name in the stop and start commands:
# $ORACLE_HOME/bin/lsnrctl {startstop} listener_name
#
#####################################

# Set this to bring up Oracle Net Listener
ORA_HOME="/d02/oracle/ora102"
ORA_OWNER="oracle"

if [ ! $ORA_HOME ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
else
LOG=/var/oracle/ora102/network/log/listener.log

# Start Oracle Net Listener
if [ -f $ORA_HOME/bin/tnslsnr ] ; then
echo "Starting Oracle Net Listener"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
else
echo "Oracle Net Listener already started"
fi
fi

report call from Forms

PROCEDURE Fpr_Print_Advice(s_fbflag varchar2,s_advnum varchar2) IS
s_parlst paramlist;
s_repnam varchar2(100);
Begin

If s_fbflag = 'FO' Then
:global.program_name := 'strfoadv.rdf';
ElsIf s_fbflag = 'BO' Then
:global.program_name := 'strboadv.rdf';
Else
Base_Message_Box('Not a valid Front Office / Back Office Flag');
Raise Form_Trigger_Failure;
End If;

s_parlst := Get_Parameter_List('s_pardat');

IF Id_Null(s_parlst) THEN
s_parlst := Create_Parameter_List('s_pardat');
Else
Destroy_Parameter_List('s_pardat');
s_parlst := Create_Parameter_List('s_pardat');
End if;

Add_Parameter(s_parlst,'P0',TEXT_PARAMETER,:crq1.brancd);
Add_Parameter(s_parlst,'P1',TEXT_PARAMETER,s_advnum);

--s_repnam := 'c:\syed\':global.program_name;
s_repnam := :global.sypthdir:global.program_name;

Add_parameter(s_parlst,'PARAMFORM',TEXT_PARAMETER,'NO');
Add_Parameter(s_parlst,'DESTYPE',TEXT_PARAMETER,'SCREEN');
Run_Product(Reports,s_repnam,Synchronous,Runtime,Filesystem,s_parlst,NULL);
End;

REgexp use in search

--1 -> Search users who play either football OR baseball AND cricket
--2 -> Search users who have interest in music
--3 -> Print the location information and their hobbies, for all the users


-- Declare a cursor that fetches users with hobbies related either to football or baseball and cricket.
-- Here (basefoot)([[:space:]]){0,}ball([^.]*)cricket expression returns true if the 'hobbies' column contains
-- values like football,baseball, base ball, foot ball etc. ([^.]*) means occurence of any character multiple
-- times. The 'i' indicates case-insensitive search


SELECT username,hobbies
FROM userinfo
WHERE REGEXP_LIKE(hobbies,'(basefoot)([[:space:]]){0,}ball([^.]*)cricket','i');


-- Declare a cursor to fetch the user data if their hobbies are related to music.
-- The 'musicviolinguitarsing(ering)' pattern returns true if the given
-- data matches any of music,violin, guitar, singer, singing character patterns.
-- sing(ering) represents either singer or singing character pattern.


SELECT username,hobbies
FROM userinfo
WHERE REGEXP_LIKE(hobbies,'musicviolinguitarsing(ering)','i');


-- This retrieves the location of all the users in the database.
-- REGEXP_SUBSTR(WEBSITE, ',[^,]+',1,2) fetches the second substring bounded by commas


SELECT username, hobbies, REGEXP_SUBSTR(address, ',[^,]+',1,2)
FROM userinfo ;

Cursor in query and xml

create table table_1 (countryid varchar2(10),country varchar2(20));

create table table_2 (cityid varchar2(10),city varchar2(20),countryid varchar2(10));


select cityid,cursor(city,countryid from table_2)
from table_2


select DBMS_XMLquery.GetXML('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2',2) dd from dual;


SELECT countryid, country,
(SELECT cityid
FROM table_2 t1
AS cityname
FROM table_1 t2


select cityid,cursor(city,countryid
from table_2) as c
from table_2


exec Get_Xml_Schema('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2')


select DBMS_XMLQuery.GetXML('select * from t1',2) from dual;

xml read xpath

create or replace procedure tamjid_xml_read_2 is
x XMLTYPE := XMLTYPE('



12
String

2
2
2
2


2
2
2
2

1967-08-13
14:20:00.0Z
String
1967-08-13
14:20:00.0Z
14:20:00.0Z
2
String
String
String



1967-08-13
1967-08-13
14:20:00.0Z
String
String
String
String
String
String



2

2
2
2
2


2
2
2
2

1967-08-13
1967-08-13
2
2

2
2
2
2

String


1967-08-13
14:20:00.0Z
14:20:00.0Z
String
String

2
2
2
2

2
2
2
2
String
String
2
2
String
String
1
1
String


1

2
2
2
2

**** VALID TEXT REQUIRED ****
123456789123456
aaaaaaaaaaaaaaaaaa

2
2
2
2

aaaaaaaaaaaaaaaaaa
Y
2
0
1


12

2
2
2
2

**** VALID TEXT REQUIRED ****
123456789123456
Y
2
0
12
aaaaaaaaaaaaaaa


1

2
2
2
2

**** VALID TEXT REQUIRED****
0
0
0
0
0
0
0
1
0
0
true
aaaaaaaa



2
2
2
2

**** VALID TEXT REQUIRED****
aa
123456789123456
aaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaa

0
1234
1234
1234
1234

1234
127
1234
a
1
127


0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
aaaaaaaaaaaaaaaaaaaa


2
2
2
2
String



2
2
2
2
2
String



2
2
2
2
String
String


');
begin
FOR i IN ( ( select EXTRACTVALUE(VALUE(t),'/EHR/StandardLevel') b_1,
EXTRACTVALUE(VALUE(t),'/EHR/TestFileInd') b_2,
EXTRACTVALUE(VALUE(t),'/EHR/ECreateDate') b_3,
EXTRACTVALUE(VALUE(t),'/EHR/ECreateTime') b_4,
EXTRACTVALUE(VALUE(t),'/EHR/ResendInd') b_5,
EXTRACTVALUE(VALUE(t),'/EHR/ESettlementDate') b_6,
EXTRACTVALUE(VALUE(t),'/EHR/ESessionTime') b_7,
EXTRACTVALUE(VALUE(t),'/EHR/ESettlementTime') b_8,
EXTRACTVALUE(VALUE(t),'/EHR/EType') b_9,
EXTRACTVALUE(VALUE(t),'/EHR/FileIDMod') b_10,
EXTRACTVALUE(VALUE(t),'/EHR/CountryCode') b_11,
EXTRACTVALUE(VALUE(t),'/EHR/EUsrFld') b_12,
EXTRACTVALUE(VALUE(t),'/EHR/DestRT/BankCode') b_13 ----type
from TABLE(XMLSEQUENCE(EXTRACT(x,'/BACPSInterface/OCE/EHR'))) t)
)

LOOP
dbms_output.put_line('B_1 = ' i.b_1 ' bankCode ' i.b_13);
END LOOP;

END;

RMAN Backup policy

RMAN Backup policy
---------------------

requirement is as follows.

1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode.

2)I will use flash recovery area to take backup for better management of backup data.

3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative.

4)As a repository I will use recovery catalog in order to store backup information.

5)I need to setup rman configuration like,
-autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) ,

-backup optimization on (in order to skip logs that it has already backed up to the specified device). and,

-recovery window to 7 days. (to ensure that you can recover the database to any point within the last week)

6)recovery catalog database name is test, listener port 1522 and serice name test1

Below is the step by step solution.


01: Connect to source database as sysdba.
=============================================
oracle@neptune ~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:22:04 2008


Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

02: See the log mode of database.
====================================

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

03: Shutdown the source database.
================================
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

04: Start the database in mount state.
======================================
SQL> startup mount
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 109055528 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.

05: Enable archive log.
=========================
SQL> alter database archivelog;
Database altered.

06: Open the database.
=========================

SQL> alter database open;
Database altered.

07: Check the archived destination and flash recovery area size and location.
=============================================
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------
db_recovery_file_dest string /oracle/app/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 50G
If you see after giving archive log list Archive destination is not shown as USE_DB_RECOVERY_FILE_DEST then set it by,
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

08: Connect to rman of source database.
=========================================
SQL> host rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:24:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)

09: Change some configuration settings of source database.
======================================================
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

10: Connect to the recovery catalog database.
====================================================
oracle@neptune ~$ sqlplus sys/sistem@test:1522/test1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:35:21 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

11: Create recovery catalog user and tablespace and assign permission.
====================================================
SQL> create user rman identified by rman;

User created.

SQL> create tablespace catalog_spc datafile '/oradata1/catalog.dbf' size 10M autoextend on maxsize unlimited;

Tablespace created.

SQL> alter user rman default tablespace catalog_spc temporary tablespace temp quota unlimited on catalog_spc;

User altered.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

12: Connect to recovery catalog database.
=================================================

bash-3.00$ rman catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:39:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

13: Create recovery catalog schema objects.
============================================

RMAN> create catalog tablespace catalog_spc;

recovery catalog created

14: Be sure whether recovery catalog objects successfully created.
===================================================

SQL> select table_name from tabs;

15: Connect to rman on source and recovery catalog database.
==========================================================

bash-3.00$ rman target / catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:41:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)
connected to recovery catalog database

16: Register database with recovery catalog.
============================================

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


17: Check whether registration was successful.
==============================================

RMAN> report schema;

If this return database structure then registration is successful.

18: Create scripts for incremental database backups.
=========================================================

This script is for full database backup level 0. This one will be run on weekly basis.
--------------------------

CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
This script is for incremental cumulative database backup level 0. This one will be run on daily basis.
CREATE GLOBAL SCRIPT global_incr_backup
{
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}


This one is for RMAN incrementally updated backups. I just made this and will not use it. For huge database we may consider it.
--------------------------------

CREATE GLOBAL SCRIPT global_incr_updated_backup
{
RECOVER COPY OF DATABASE WITH TAG 'inc_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'inc_update'
DATABASE;
}

A sample output after creating global_full_backup,
RMAN> CREATE GLOBAL SCRIPT global_full_backup;
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

starting full resync of recovery catalog
full resync complete
created global script global_full_backup

19: Run these scripts and take backups.
======================================

Weekly,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_full_backup;
}

Daily,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_incr_backup;
}


You may consider also inside script like,

RMAN> backup database plus archivelog delete all input;

in order to delete archived log from all locations that already taken backup.

Or, you may issue,

RMAN> delete archivelog all backed up 1 times to disk;


in order to delete archive log that have at least 1 times backed up to disk.

Enterprise Manager Recreate

1).Delete DB Control Configuration Files using EMCA scripts

In 10.2g run, $emca -deconfig dbcontrol db Then enter sid as prompt
and then y.

2).Delete DB Control Configuration Files Manually:

Remove the following directories from your filesystem:
$ORACLE_HOME/hostname_sid
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

3).Delete DB Control Repository using RepManager:
Invoke following command, RepManager hostname listener_port sid -
action drop
But don't delete by RepManager. It puts the database in quiescence
mode.

it gave me
-bash: RepManager: command not found

4).Delete DB Control Repository Objects Manually
drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;


5).Delete DB Control Configuration Files and Repository Objects using
EMCA

For 10.2,

>emca -deconfig dbcontrol db -repos drop

REP-1950 Invalid Source specified.

solution:

source destination need to be correct and valid.

example:
-------------------------------------------------

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
rem ---------------------------
rem begin command line argument
rem ---------------------------
for %%f IN (*.RDF) do rwconverter userid=islbas/islbas@beftn batch=yes source=%%f stype=rdffile DTYPE=repFILE compile_all=yes OVERWRITE=yes logfile=log.txt dest='D:\ISLBAS\Report\des\%%f'
rem -------------------------
rem end command line argument
rem -------------------------
ECHO FINISHED COMPILING

-----------------------------------
Here source need to place in single line

Be Careful: ALL command Line Argument
need to write in a single line.

compile oracle Forms and Reports batch wise

how to compile a oracle report in unix or linux or sun solaries
=========================================

------------------------------------------------
------------------sample-------------------------
-------------------------------------------------
Be Careful: ALL command Line Argument
need to write in a single line.
-------------------------------------------------
--------------------------------------------------

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
rem ---------------------------
rem begin command line argument
rem ---------------------------
for %%f IN (*.RDF) do rwconverter userid=islbas/islbas@beftn batch=yes source=%%f stype=rdffile DTYPE=repFILE compile_all=yes OVERWRITE=yes logfile=log.txt dest='D:\ISLBAS\Report\des\%%f'
rem -------------------------
rem end command line argument
rem -------------------------
ECHO FINISHED COMPILING


another parameter of DTYPE=rdffile
==============================================================================

For Windows
====================

----------------------
Forms/Reports 6i
---------------------

REM WINDOWS COMPILE FORMS
::compile_forms.bat
cls
Echo compiling Forms....
for %%f IN (*.fmb) do ifcmp60 userid=scott/tiger@v817 module=%%f batch=yes
module_type=form compile_all=yes window_state=minimize
ECHO FINISHED COMPILING

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
for %%f IN (*.rdf) do RWCON60 userid=scott/tiger@v817 batch=yes source=%%f
stype=rdffile DTYPE=REPFILE OVERWRITE=yes logfile=log.txt
ECHO FINISHED COMPILING

--------------------
Forms/Reports 9.0.X
--------------------

REM WINDOWS COMPILE FORMS
::compile_forms.bat
cls
Echo compiling Forms....
for %%f IN (*.fmb) do ifcmp90 userid=scott/tiger@v817 module=%%f batch=yes
module_type=form compile_all=yes window_state=minimize
ECHO FINISHED COMPILING


--------------------
Forms/Reports 10.X
--------------------

::compile_forms.bat
cls
Echo compiling Forms....
for %%f IN (*.fmb) do frmcmp userid=scott/tiger@v817 module=%%f batch=yes
module_type=form compile_all=yes window_state=minimize
ECHO FINISHED COMPILING

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
for %%f IN (*.rdf) do rwconverter userid=scott/tiger@v817 batch=yes
source=%%f stype=rdffile DTYPE=REPFILE compile_all=yes OVERWRITE=yes logfile=log.txt
ECHO FINISHED COMPILING

For UNIX
===================
-------------------
Forms/Reports 6i
-------------------

#UNIX Forms Compile
#compile_forms.sh
for i in `ls *.fmb`
do
echo Compiling Form $i ....
f60genm userid=scott/tiger@bs817 batch=yes module=$i module_type=form
compile_all=yes window_state=minimize
done

#UNIX COMPILE REPORTS
#compile_rep.sh
for i in `ls *.rdf`
do
echo Compiling Report $i ...
rwcon60 userid=scott/tiger@bs817 batch=yes source=$i stype=rdffile
dtype=repfile overwrite=yes compile_all=yes
done

------------------------
Forms/Reports 9.0.X, 10.x
------------------------

#UNIX Forms Compile
#compile_forms.sh
for i in `ls *.fmb`
do
echo Compiling Form $i ....
f90genm userid=scott/tiger@bs817 batch=yes module=$i module_type=form
compile_all=yes window_state=minimize
done

( For 10gR1 = forms 9.0.4.x, you can use f90gen also)
( For 10gR2 = forms 10.x, you can use frmcmp.sh or frmcmp_batch.sh)

#UNIX COMPILE REPORTS
#compile_rep.sh
for i in `ls *.rdf`
do
echo Compiling Report $i ...
rwconverter.sh userid=scott/tiger@bs817 batch=yes source=$i
stype=rdffile dtype=repfile overwrite=yes compile_all=yes
done


ADDITIONAL:
You can use the information above to compile libraries or menues or
convert files from binary to text and text to binary too. eg:

for %%f in (*.pll) do ifcmp60 userid=user/pwd@db module=%%f module_type=LIBRARY batch=yes window_state=minimize compile_all=yes
for %%f in (*.mmb) do ifcmp60 userid=user/pwd@db module=%%f module_type=MENU batch=yes window_state=minimize compile_all=yes
for %%f in (*.mmb) do ifcmp60 userid=user/pwd@db module=%%f module_type=FORM batch=yes window_state=minimize parse=YES
for %%f in (*.mmb) do ifcmp60 userid=user/pwd@db module=%%f module_type=FORM batch=yes window_state=minimize script=YES
(and similar loops for Unix).

REP-1950: Invalid Source specified.

Oracle Application Server Command-Line Tools

Oracle Application Server Command-Line ToolsTable

summarizes the command-line tools available in Oracle Application Server, with descriptions and pointers to more information.

Table B-1 Oracle Application Server Command-Line Tools

Command Path from Oracle Home Description
bulkdelete
UNIX: ldap/bin/bulkdelete.sh

Windows: ldap\bin\bulkdelete.bat
Delete a subtree efficiently in Oracle Internet Directory.

See: Oracle Identity Management User Reference

bulkload
UNIX: ldap/bin/bulkload.sh

Windows: ldap\bin\bulkload.bat
Create Oracle Internet Directory entries from data residing in or created by other applications.

See: Oracle Identity Management User Reference

bulkmodify
UNIX: bin/bulkmodify

Windows: bin\bulkmodify
Modify a large number of existing Oracle Internet Directory entries in an efficient way.

See: Oracle Identity Management User Reference

catalog
UNIX: ldap/bin/catalog.sh

Windows: ldap\bin\catalog.bat
Add and delete catalog entries in Oracle Internet Directory.

See: Oracle Identity Management User Reference

dcmctl
UNIX: dcm/bin/dcmctl

Windows: dcm\bin\dcmctl.bat
Manage application server instances and OracleAS Clusters, deploy applications, manage the DCM repository.

See: Distributed Configuration Management Administrator's Guide

dipassistant
UNIX: bin/dipassistant

Windows: bin\dipassistant.bat
Directory Integration and Provisioning Assistant—assists in performing all operations in the Oracle Directory Integration and Provisioning platform.

See: Oracle Identity Management User Reference

dmstool
UNIX: bin/dmstool

Windows: bin\dmstool.bat
View performance metrics and set reporting intervals.

See: Oracle Application Server Performance Guide

emctl
UNIX: bin/emctl

Windows: bin\emctl.bat
Start, stop, and manage security for Oracle Enterprise Manager 10g.

See: Chapter 2, "Introduction to Administration Tools"

eulbuilder.jar
UNIX: bin/eulbuilder.jar

Windows: bin\eulbuilder.jar
Discoverer EUL Java command-line interface. Create and manipulate Discoverer EULs without installing Oracle Discoverer Administrator.

See: Oracle Business Intelligence Discoverer EUL Command Line for Java User's Guide

fplsqlconv90
UNIX: bin/fplsqlconv90

Windows: bin\fplsqlconv90
Update obsolete usage in your PL/SQL code in order to migrate your Forms6i applications to Oracle Application Server Forms Services.

See: Oracle Application Server Forms Services Online Help

hiqpurge
UNIX: ldap/bin/hiqpurge.sh

Windows: ldap\bin\hiqpurge.bat
Move the changes from the human intervention queue to the purge queue.

See: Oracle Identity Management User Reference

hiqretry
UNIX: ldap/bin/hiqretry.sh

Windows: ldap\bin\hiqretry.bat
Move the changes from the human intervention queue to the retry queue.

See: Oracle Identity Management User Reference

iasua
UNIX: upgrade/iasua.sh

Windows: upgrade\iasua.bat
Oracle Application Server Upgrade Assistant.

See: Oracle Application Server Upgrade and Compatibility Guide

ifbld90
UNIX: bin/ifbld90

Windows: bin\ifbld90
Start Forms Developer with specific options for a Forms session.

See: Oracle Application Server Forms Services Online Help

ifcmp90
UNIX: bin/ifcmp90

Windows: bin\ifcmp90
Start Form Compiler to generate a form.

See: Oracle Application Server Forms Services Online Help

iff2xml90
UNIX: bin/iff2xml90

Windows: bin\iff2xml90
Traverse a module object hierarchy and produce an XML representation of it.

See: Oracle Application Server Forms Services Online Help

ifweb90
UNIX: bin/ifweb90

Windows: bin\ifweb90
Preview a form in a Web browser.

See: Oracle Application Server Forms Services Online Help

ifxml2f90
UNIX: bin/ifxml2f90

Windows: bin\ifxml2f90
Take well-defined XML format and convert it back into a module.

See: Oracle Application Server Forms Services Online Help

ifxmlv90
UNIX: bin/ifxmlv90

Windows: bin\ifxmlv90
XML Validator that can be used on the command line or called from Java to validate .xml files or XMLDocument Java objects respectively against the Forms XML Schema.

See: Oracle Application Server Forms Services Online Help

jazn.jar
UNIX: j2ee/home/jazn.jar

Windows: j2ee\home\jazn.jar
Manage both XML-based and LDAP-based JAAS data.

See: Oracle Application Server Containers for J2EE Security Guide

ldapadd
UNIX: bin/ldapadd

Windows: bin\ldapadd
Add entries, their object classes, attributes, and values to Oracle Internet Directory.

See: Oracle Identity Management User Reference

ldapaddmt
UNIX: bin/ldapaddmt

Windows: bin\ldapaddmt
Add entries, their object classes, attributes, and values to Oracle Internet Directory. Like ldapadd, except supports multiple threads for adding entries concurrently.

See: Oracle Identity Management User Reference

ldapbind
UNIX: bin/ldapbind

Windows: bin\ldapbind
Determine if you can authenticate a client to a server.

See: Oracle Identity Management User Reference

ldapcompare
UNIX: bin/ldapcompare

Windows: bin\ldapcompare
Match attribute values you specify in the command-line with the attribute values in the Oracle Internet Directory entry.

See: Oracle Identity Management User Reference

ldapdelete
UNIX: bin/ldapdelete

Windows: bin\ldapdelete
Remove entire entries from Oracle Internet Directory.

See: Oracle Identity Management User Reference

ldapmoddn
UNIX: bin/ldapmoddn

Windows: bin\ldapmoddn
Modify the DN or RDN of an Oracle Internet Directory entry.

See: Oracle Identity Management User Reference

ldapmodify
UNIX: bin/ldapmodify

Windows: bin\ldapmodify
Perform actions on attributes in Oracle Internet Directory.

See: Oracle Identity Management User Reference

ldapmodifymt
UNIX: bin/ldapmodifymt

Windows: bin\ldapmodifymt
Modify several Oracle Internet Directory entries concurrently.

See: Oracle Identity Management User Reference

ldapsearch
UNIX: bin/ldapsearch

Windows: bin\ldapsearch
Search and retrieve specific entries in Oracle Internet Directory.

See: Oracle Identity Management User Reference

ldifmigrator
UNIX: bin/ldifmigrator

Windows: bin\ldifmigrator.bat
Migrate data from application-specific repositories into Oracle Internet Directory.

See: Oracle Identity Management User Reference

ldifwrite
UNIX: bin/ldifwrite

Windows: bin\ldifwrite
Convert to LDIF all or part of the information residing in an Oracle Internet Directory.

See: Oracle Identity Management User Reference

ocactl
UNIX: oca/bin/ocactl

Windows: oca\bin\ocactl.bat
OracleAS Certificate Authority administration tool.

See: Oracle Application Server Certificate Authority Administrator's Guide

oidctl
UNIX: bin/oidctl

Windows: bin\oidctl
Start and stop Oracle Internet Directory.

See: Oracle Identity Management User Reference

oidmon
UNIX: bin/oidmon

Windows: bin\oidmon
Initiate, monitor, and terminate Oracle Internet Directory processes.

See: Oracle Identity Management User Reference

oidpasswd
UNIX: bin/oidpasswd

Windows: bin\oidpasswd
Change the Oracle Internet Directory database password.

See: Oracle Identity Management User Reference

oidprovtool
UNIX: bin/oidprovtool

Windows: bin\oidprovtool.bat
Administer provisioning profile entries in Oracle Internet Directory.

See: Oracle Identity Management User Reference

oidreconcile
UNIX: bin/oidreconcile

Windows: bin\oidreconcile
Synchronize Oracle Internet Directory entries.

See: Oracle Identity Management User Reference

oidstats
UNIX: ldap/admin/oidstats.sh

Windows: ldap\admin\oidstats.bat
Analyze the various database ods schema objects to estimate statistics.

See: Oracle Identity Management User Reference

ojspc
UNIX: bin/ojspc

Windows: bin\ojspc.bat
JSP back precompiler.

See: Oracle Application Server Containers for J2EE Support for JavaServer Pages Developer's Guide

opmnctl
UNIX: opmn/bin/opmnctl.exe

Windows: opmn\bin\opmnctl.exe
Start, stop, and get status on OPMN-managed processes.

See: Oracle Process Manager and Notification Server Administrator's Guide

ossoca.jar
UNIX: sso/lib/ossoca.jar

Windows: sso\lib\ossoca.jar
Configure OracleAS Single Sign-On, including additional languages.

See: Oracle Application Server Single Sign-On Administrator's GuideandOracle Application Server Globalization Guide

ossoreg.jar
UNIX: sso/lib/ossoreg.jar

Windows: sso\lib\ossoreg.jar
Register mod_osso.

See: Oracle Application Server Single Sign-On Administrator's Guide

portalRegistrar
UNIX: wireless/bin/internal/portalRegistrar.sh

Windows: wireless\bin\internal\portalRegistrar.bat
Reregister the mobile gateway parameter with OracleAS Portal.

See: Oracle Application Server Portal Configuration Guide and Oracle Application Server Wireless Administrator's Guide

printlogs
UNIX: diagnostics/bin/printlogs

Windows: diagnostics\bin\printlogs.bat
Print the contents of diagnostic log files to standard output.

See: Appendix F, "printlogs Tool Syntax and Usage"

remtool
UNIX: ldap/bin/remtool

Windows: ldap\bin\remtool
Search for problems and seek to rectify them in the event of an Oracle Internet Directory replication failure.

See: Oracle Identity Management User Reference

reRegisterSSO
UNIX: wireless/bin/reRegisterSSO.sh

Windows: wireless\bin\reRegisterSSO.bat
Reregister the Wireless Single Sign-On partner application with the Single Sign-On server.

See: Oracle Application Server Wireless Administrator's Guide

resetiASpasswd
UNIX: bin/resetiASpasswd.sh

Windows: bin\resetiASpasswd.bat
Reset the internal password that instances use to authenticate themselves with Oracle Internet Directory. Resets it to a randomly generated password.

See: Oracle Application Server Security Guide

runstartupconsole
UNIX: bin/runstartupconsole.sh

Windows: bin/runstartupconsole.bat
Start and stop Application Server instances.

See: Section 3.2, "Starting and Stopping Application Server Instances"

rwbuilder
UNIX: bin/rwbuilder

Windows: bin\rwbuilder
Invoke the Reports Builder.

See: Oracle Application Server Reports Services Publishing Reports to the Web

rwcgi
UNIX: bin/rwcgi

Windows: bin\rwcgi
Like rwservlet, translate and deliver information between HTTP and the Reports Server. The rwservlet command is the recommended choice; rwcgi is maintained only for backward compatibility.

See: Oracle Application Server Reports Services Publishing Reports to the Web

rwclient
UNIX: bin/rwclient

Windows: bin\rwclient
Parse and transfer a command line to the specified (or default) Reports Server.

See: Oracle Application Server Reports Services Publishing Reports to the Web

rwconverter
UNIX: bin/rwconverter

Windows: bin\rwconverter
Convert one or more report definitions or PL/SQL libraries from one storage format to another.

See: Oracle Application Server Reports Services Publishing Reports to the Web

rwrun
UNIX: bin/rwrun

Windows: bin\rwrun
Run a report using the OracleAS Reports Services in-process server.

See: Oracle Application Server Reports Services Publishing Reports to the Web

rwserver
UNIX: bin/rwserver

Windows: bin\rwserver.bat
Invoke the Reports Server.

See: Oracle Application Server Reports Services Publishing Reports to the Web

schemasync
UNIX: bin/schemasync

Windows: bin\schemasync.bat
Synchronize schema elements—namely attributes and object classes—between an Oracle directory server and third-party LDAP directories.

See: Oracle Identity Management User Reference

ssocfg
UNIX: sso/bin/ssocfg.sh

Windows: sso\bin\ssocfg.bat
Update host, port, and protocol of OracleAS Single Sign-On URL.

See: Oracle Application Server Single Sign-On Administrator's Guide

ssooconf.sql
UNIX: portal/admin/plsql/sso/ssooconf.sql

Windows: portal\admin\plsql\sso\ssooconf.sql
Script to point OracleAS Single Sign-On server to a different Oracle Internet Directory.

See: Oracle Application Server Single Sign-On Administrator's Guide

stopodiserver
UNIX: ldap/odi/admin/stopodiserver.sh

Windows: ldap\odi\admin\stopodiserver.bat
In a client-only installation where the Oracle Internet Directory Monitor and Oracle Internet Directory Control Utility are not available, you can start the directory integration and provisioning server without the oidctl tool. To stop the server, use the stopodiserver tool.

See: Oracle Identity Management User Reference

uddiadmin.jar
UNIX: uddi/lib/uddiadmin.jar

Windows: uddi\lib\uddiadmin.jar
Manage the UDDI registry, which is part of OracleAS Web Services.

See: Oracle Application Server Web Services Developer's Guide

webcachectl
UNIX: bin/webcachectl

Windows: bin\webcachectl.exe
In a standalone environment, administer OracleAS Web Cache processes, including the administration server process, cache server process, and auto-restart process.

See: Oracle Application Server Web Cache Administrator's Guide

Emergency Performance improbment Method

The Emergency Performance Method is as follows:

1. Survey the performance problem and collect the symptoms of the performance
problem. This process should include the following:

■ User feedback on how the system is underperforming. Is the problem
throughput or response time?
■ Ask the question, "What has changed since we last had good performance?"
This answer can give clues to the problem. However, getting unbiased
answers in an escalated situation can be difficult. Try to locate some
reference points, such as collected statistics or log files, that were
taken before and after the problem.
■ Use automatic tuning features to diagnose and monitor the problem. In
addition, you can use Oracle Enterprise Manager performance features to
identify top SQL and sessions.

2. Sanity-check the hardware utilization of all components of the application system.
Check where the highest CPU utilization is, and check the disk, memory usage,
and network performance on all the system components. This quick process
identifies which tier is causing the problem. If the problem is in the application,
then shift analysis to application debugging. Otherwise, move on to database
server analysis.

3. Determine if the database server is constrained on CPU or if it is spending time
waiting on wait events. If the database server is CPU-constrained, then investigate
the following:

■ Sessions that are consuming large amounts of CPU at the operating system
level and database; check V$SESS_TIME_MODEL for database CPU usage
■ Sessions or statements that perform many buffer gets at the database level;
check V$SESSTAT and V$SQLSTATS
■ Execution plan changes causing sub-optimal SQL execution; these can be
difficult to locate
■ Incorrect setting of initialization parameters
■ Algorithmic issues as a result of code changes or upgrades of all components
If the database sessions are waiting on events, then follow the wait events listed in
V$SESSION_WAIT to determine what is causing serialization.
The V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity which can be used to perform diagnosis even after an incident has ended and the system has returned to normal operation. In cases of massive contention for the library cache,it might not be possible to logon or submit SQL to the database. In this case, use historical data to determine why there is suddenly contention on this latch. If most waits are for I/O, then examine V$ACTIVE_SESSION_HISTORY to determine the SQL being run by the sessions that are performing all of the inputs and outputs.

4. Apply emergency action to stabilize the system. This could involve actions that
take parts of the application off-line or restrict the workload that can be applied to the system. It could also involve a system restart or the termination of job in
process. These naturally have service level implications.

5. Validate that the system is stable. Having made changes and restrictions to the
system, validate that the system is now stable, and collect a reference set of
statistics for the database. Now follow the rigorous performance method described
earlier in this book to bring back all functionality and users to the system. This
process may require significant application re-engineering before it is complete.

Is all the CPU being utilized ?

Is all the CPU being utilized?

If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.

you have serialization and unscalable behavior within the server. Get
the WAIT_EVENTS statistics from the server, and determine the biggest serialization point.

Performance Checklist for new Application

Performance Checklist for new Application
=========================================

For a new Application, build a list of tasks that—if performed correctly—will
increase the chance of optimal performance in production and—if there is a
problem—enable rapid debugging of the application:

1. When you create the control file for the production database, allow for growth by
setting MAXINSTANCES, MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, and
MAXLOGHISTORY to values higher than what you anticipate for the rollout. This
results in more disk space usage and bigger control files, but saves time later
should these need extension in an emergency.

2. Set block size to the value used to develop the application. Export the schema
statistics from the development/test environment to the production database if the
testing was done on representative data volumes and the current SQL execution
plans are correct.

3. Set the minimal number of initialization parameters. Ideally, most other
parameters should be left at default. If there is more tuning to perform, this shows
up when the system is under load.

4. Be prepared to manage block contention by setting storage options of database
objects. Tables and indexes that experience high INSERT/UPDATE/DELETE rates
should be created with automatic segment space management. To avoid
contention of rollback segments, automatic undo management should be used.

5. All SQL statements should be verified to be optimal and their resource usage
understood.

6. Validate that middleware and programs that connect to the database are efficient
in their connection management and do not logon/logoff repeatedly.

7. Validate that the SQL statements use cursors efficiently. Each SQL statement
should be parsed once and then executed multiple times. The most common
reason this does not happen is because bind variables are not used properly and
WHERE clause predicates are sent as string literals. If the precompilers are used to
develop the application, then make sure that the parameters MAXOPENCURSORS,
HOLD_CURSOR, and RELEASE_CURSOR have been reset from the default values
prior to precompiling the application.

8. Validate that all schema objects have been correctly migrated from the
development environment to the production database. This includes tables,
indexes, sequences, triggers, packages, procedures, functions, Java objects,
synonyms, grants, and views. Ensure that any modifications made in testing are
made to the production system.

9. As soon as the system is rolled out, establish a baseline set of statistics from the database and operating system. This first set of statistics validates or corrects any assumptions made in the design and rollout process.

10. Start anticipating the first bottleneck (there will always be one) and follow the
Oracle performance method to make performance improvement.

SQL Parsing in Oracle

Parsing is the process of interpreting a SQL statement and
creating an execution plan for it. This process has many phases, including syntax
checking, security checking, execution plan generation, and loading shared
structures into the shared pool.

There are two types of parse operations:

■ Hard Parsing: A SQL statement is submitted for the first time, and no match is
found in the shared pool. Hard parses are the most resource-intensive and
unscalable, because they perform all the operations involved in a parse.

■ Soft Parsing: A SQL statement is submitted for the first time, and a match is
found in the shared pool. The match can be the result of previous execution by
another user. The SQL statement is shared, which is good for performance.
However, soft parses are not ideal, because they still require syntax and
security checking, which consume system resources.


To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.

For example:

Statement with string literals:
--------------------------------
SELECT * FROM employees
WHERE last_name LIKE 'KING';

Statement with bind variables:
-------------------------------

SELECT * FROM employees
WHERE last_name LIKE :1;

Different Index Type

There are several index types available, and each index has benefits for certain
situations.

The following list gives performance ideas associated with each index type.

1. B-Tree Indexes
-------------
These indexes are the standard index type, and they are excellent for
primary key and highly-selective indexes. Used as concatenated indexes, B-tree
indexes can be used to retrieve data sorted by the index columns.

2. Bitmap Indexes
--------------
These indexes are suitable for low cardinality data. Through
compression techniques, they can generate a large number of rowids with minimal
I/O. Combining bitmap indexes on non-selective columns allows efficient AND and OR
operations with a great number of rowids with minimal I/O. Bitmap indexes are
particularly efficient in queries with COUNT(), because the query can be satisfied within the index.

3. Function-based Indexes
--------------------
These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.
Function-based indexes are particularly useful when querying on composite columns
to produce a derived result or to overcome limitations in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.

4. Partitioned Indexes
-----------------
Partitioning a global index allows partition pruning to take place
within an index access, which results in reduced I/Os. By definition of good range or
list partitioning, fast index scans of the correct index partitions can result in very fast query times.

5. Reverse Key Indexes
-------------------
These indexes are designed to eliminate index hot spots on insert
applications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.

6. Index-Organized Tables or Appending Columns to an Index
--------------------------------------------------------
One of the easiest ways to speed up a query is to reduce the number of logical I/Os by eliminating a table access from the execution plan. This can be done by appending to the index all columns referenced by the query. These columns are the select list
columns, and any required join or sort columns. This technique is particularly useful
in speeding up online applications response times when time-consuming I/Os are
reduced. This is best applied when testing the application with properly sized data for the first time.
The most aggressive form of this technique is to build an index-organized table (IOT). However, you must be careful that the increased leaf size of an IOT does not
undermine the efforts to reduce I/O.

Need to know for Oracle Database architecture

The following questions should stimulate thought on Oracle Database architecture.

These questions demonstrate how business requirements
can influence the architecture, ease of implementation, and overall
performance and availability of a system.

For example:

■ How many users will the system support?
■ What will be the user interaction method?
■ Where are the users located?
■ What is the network speed?
■ How much data will the user access, and how much of that data is largely read
only?
■ What is the user response time requirement?
■ Do users expect 24 hour service?
■ Must all changes be made in real time?
■ How big will the database be?
■ What is the required throughput of business transactions?
■ What are the availability requirements?
■ Do skills exist to build and administer this application?
■ What compromises will be forced by budget constraints?

Hardware Components for oracle Database

The main hardware components include:

■ CPU
■ Memory
■ I/O Subsystem
■ Network

CPU :
There can be one or more CPUs, and they can vary in processing power from
simple CPUs found in hand-held devices to high-powered server CPUs. Sizing of
other hardware components is usually a multiple of the CPUs on the system.

Memory
Database and application servers require considerable amounts of memory to
cache data and avoid time-consuming disk access.

I/O Subsystem
The I/O subsystem can vary between the hard disk on a client PC and
high performance disk arrays. Disk arrays can perform thousands of I/Os each second
and provide availability through redundancy in terms of multiple I/O paths and hot
pluggable mirrored disks.

Note: These factors are based on Oracle Server Performance
group’s experience of tuning unscalable systems.

Network
All computers in a system are connected to a network, from a modem line to a
high speed internal LAN. The primary concerns with network specifications are
bandwidth (volume) and latency (speed).

Parameters Affecting Memory Usage

The memory required by any one Oracle session depends on many factors.
Typically the major contributing factors are:

■ Number of open cursors
■ Memory used by PL/SQL, such as PL/SQL tables
■ SORT_AREA_SIZE initialization parameter

In Oracle, the PGA_AGGREGATE_TARGET initialization parameter gives greater control
over a session's memory usage.

Oracle Performance Tuning Features

Automatic Oracle Performance Tuning Features
--------------------------------------------

■ Automatic Workload Repository (AWR) collects, processes, and maintains
performance statistics for problem detection and self-tuning purposes.

■ Automatic Database Diagnostic Monitor (ADDM) analyzes the information
collected by the AWR for possible performance problems with the Oracle
database.

■ SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL
statements without modifying any statements.

■ SQLAccess Advisor provides advice on materialized views, indexes, and
materialized view logs.

■ End to End Application tracing identifies excessive workloads on the system by
specific user, service, or application component.

■ Server-generated alerts automatically provide notifications when impending
problems are detected. See Oracle Database Administrator's Guide for information
about monitoring the operation of the database with server-generated alerts.

■ Additional advisors that can be launched from Oracle Enterprise Manager, such as
memory advisors to optimize memory for an instance. The memory advisors are
commonly used when automatic memory management is not set up for the
database. Other advisors are used to optimize mean time to recovery (MTTR),
shrinking of segments, and undo tablespace settings. For information about using
advisors that are available with Oracle Enterprise Manager, see Oracle Database 2
Day + Performance Tuning Guide.

■ The Database Performance page in Oracle Enterprise Manager displays host,
instance service time, and throughput information for real time monitoring and
diagnosis. The page can be set to refresh automatically in selected intervals or
manually. For information about the Database Performance page, see Oracle
Database 2 Day + Performance Tuning Guide.

(Manually) Oracle Performance Tuning Features
------------------------------------------

■ V$ Performance Views

The V$ views are the performance information sources used by all Oracle performance
tuning tools. The V$ views are based on memory structures initialized at instance
startup. The memory structures, and the views that represent them, are automatically
maintained by Oracle throughout the life of the instance. See Oracle Database Reference for information about dynamic performance views