GOOD BLOGS

Check in below links
http://dbataj. blogspot. com/2008/ 01/oracle- streams-setup- between-two. html

http://dbataj. blogspot. com/2008/ 01/oracle- streams-adding- new-site- in.html

http://dbataj. blogspot. com/2008/ 01/knllgobjinfo- missing-streams- multi.html

http://dbataj. blogspot. com/2008/ 01/tips-improve- performance- with-oracle. html

Network DBA

Network DBA
Today I discuss some NETWORK related issues which we database administrator usually faced.First understand which network files is important for oracle network connectivity.1. LISTENER.ORA file It is server side files which created only on ORACLE SERVER not CLIENT side. It content information regarding listener.2. TNSNAMES.ORA fileIt is Client side files which created in both sites SERVER/CLIENT. 3. SQLNET.ORA fileIt is Client side files which created in both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.NOTE: Above all three files is located in $ORACLE_HOME/network/admin DIRECTORY.Always remembers LISTENER.ORA, TNSNAMES.ORA file syntax must be correct otherwise you will get a lot of error because of this mistake.Example of LISTENER.ORA fileSID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)(PROGRAM = extproc)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomainname) (PORT = 1521)))(ADDRESS_LIST =(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521)))))If you have any syntax error with listener.ora file then you always get error message.Now in windows and linus you have to set LISTENER services.ON Windows:cmd>set ORACLE_HOME=oracle-home-pathcmd>set PATH=%ORACLE_HOME%/bincmd>set TNS_ADMIN=%ORACLE_HOME%/network/admincmd>lsnrctl LSNRCTL>startON linus/solaris/unix:$]export ORACLE_HOME=oracle-home-path$]export PATH=$ORACLE_HOME/bin:$PATH$]export TNS_ADMIN=$ORACLE_HOME/network/admin$]./lsnrctl$]LSNRCTL>startExample of TNSNAMES.ORA fileTAJ = #it is “TNS_ENTRY”(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.13) (PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))NOTE: As you see there is two names “TAJ” it’s called “TNS_ENTRY” which we use with connect string like “username/pwd@TNS_ENTRY”” & “ORCL” is service_name (instance_name)1. ORA-12154 : TNS:could not resolve the connect identifier specifiedThis error we received when we use “wrong” TNS_ENTRY” or “TNS_ENTRY” is not exist in TNSNAMES.ORA fileJust take above tnsnames.ora file there “tns_entry” is “TAJ” and service_name is “ORCL” now see…SQL> conn scott/tiger@tajConnected.User is connected because TAJ is exist in tnsnames.ora file.SQL> conn scott/tiger@orclERROR:ORA-12154: TNS:could not resolve the connect identifier specifiedUser is not connected because ORCL is not a tns_entry.2. ORA-12170 : TNS:Connect timeout occurredThis error is very generic and difficult to figure out. But most common reason for above error is “Firewall is enable between CLIENT/SERVER” so first disable firewall setting and try.Also check “use_shared_socket=TRUE” in registry, this must be set to TRUE.3. ORA-12203 : TNS:unable to connect to destinationThis error is occurring when client machine is not able to connect with server machine. It is just because of NETWORK connectivity.So first check you can able to ping server from client.cmd>ping SERVER_IP_ADDRESS4. ORA-12500 : TNS:listener failed to start a dedicated server processThis error is occurring because of SGA memory is not sufficient to handle client request or “PROCESSES” parameter is set to low value. So increase SGA size or increase PROCESSES parameter (we need to restart ORACLE SERVER because PROCESSES is static parameter)http://dbataj.blogspot.com/2007/03/ora-12500.html
ORA-12500
ORA-12500: TNS:listener failed to start a dedicated server processFor Above error there is couple of reason check below point.
1.Oracle Services is start.Windows > c:\>net start OracleServiceUnix > $ps -ef grep ora_smon2.Listener Services is start.3.Take a look on LISTENER.LOG file {%ORACLE_HOME%/network/log}4.if you received WINEXEC FORMAT ERROR 8C:\>net helpmsg 8Not enough storage is available to process this command.Contact OS vendor for more information.5.Sometimes it is happen due to PGA RAM shortage on database.You have to set below parameters.pga_aggregate_target, sort_area_size and hash_area_size.6.TNS-00517: Lost contact32-bit Windows Error: 54: Unknown errorIf your received above errorC:\>net helpmsg 54The network is busy.7.TNS-00507: Connection closed32-bit Windows Error: 109: Unknown errorC:\>net helpmsg 109The pipe has been ended.8.Recreate OracleService service through ORADIM utilityORADIM -DELETE -SID ORADIM -NEW -SID
5. ORA-12502 : TNS:listener received no CONNECT_DATA from clientThis error is occurring because of wrong configuration of TNSNAMES.ORA file just RECREATE tnsnames.ora file through NETCA tools.6. ORA-12505 : TNS:listener could not resolve SID given in connect descriptorhttp://dbataj.blogspot.com/2007/02/ora-12505.html
ORA-12505
Reason When ORACLE_SID <> SERVICE_NAMESMy ORACLE_SID = db01My SERVICE_NAMES = tajSQL> select instance_name from v$instance;INSTANCE_NAME----------------db01SQL> show parameter service_namesNAME TYPE VALUE------------------------------------ ----------- ------------------------------service_names string TajSQL>Take a look on my tnsnames.ora fileSQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools.TAJ =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = taj.domainname.local)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)[B](SID = Taj)[/B]))SQL> conn system/oracle@tajERROR:ORA-12505: TNS:listener does not currently know of SID given in connectdescriptorWarning: You are no longer connected to ORACLE.SQL> --edit my tnsnames.ora file from SID to SERVICE_NAME.SQL> host type C:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora# Generated by Oracle configuration tools.TAJ =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = taj.domainname.local)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)[B](SERVICE_NAME = Taj)[/B]))SQL> conn system/oracle@tajConnected.
7. ORA-12508 : TNS:listener could not resolve the COMMAND givenThis error occurring when multiple oracle home install on same machine and multiple listeners are running must set all enviourment variable correctly to point recent version oracle home. And recreate listener.ora file through NETCA tools.8. ORA-12514 : TNS:listener does not currently know of service requested in connect descriptorThis error is very generic, but most common reason it when database is not OPEN stage.SQL>select status from v$instance;http://dbataj.blogspot.com/2007/02/ora-12514.html
ORA-12514
ORA-12514: TNS:listener does not currently know of service requested in connect descriptorOne reason is default listener registration is DYNAMIC you have to change DYNAMIC to STATIC.edit your listener.ora file like below SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)(PROGRAM = extproc))(SID_DESC = (SID_NAME = orcl)(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1) ))C:\>LSNRCTL stopC:\>REM WAIT FEW SECONDSC:\>LSNRCTL start--------------------------------------------------------------------------
9. ORA-12515 : TNS:listener could not find a handler for this presentation*Cause: None of the listener's known service handlers are registeredas supporting the presentation protocol required by the connecting client.*Action: Check that the destination service is configured to accept thepresentation protocol.10. ORA-12516 : TNS:listener could not find available handler with matching protocol stackThis error sometime happen due to user connection reach processes parameter limit so just increase processes parameter and try.11. ORA-12518 : TNS:listener could not hand off client connectionThis error occurring because of memory so just increase oracle sga memory and try.12. ORA-12519 : TNS:no appropriate service handler foundThis error occurring when listener not register database services so try to register database manually to listener.13. ORA-12520 : TNS:listener could not find available handler for requested type of serverCommon reason for this error in user connection reaches “PROCESSES” parameter value. So increase processes parameter and try.http://dbataj.blogspot.com/2007/01/ora-12520.html
ORA-12520
Oracle Version : 10.1.0.2.0OS : Windows XP sp2--------------------------------------------------SQL> show parameter processesNAME TYPE VALUE------------------------------------ ----------- ------------------------------processes integer 50SQL> conn scott/tiger@testERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserverClause : Process parameter value is LOW. we have to increase.on Listner.log file04-MAR-2007 10:23:38 * service_update * test * 004-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520TNS-12520: TNS:listener could not find available handler for requested type of serverLsnrctl servicesService "test" has 1 instance(s).Instance "test", status READY, has 1 handler(s) for this service...Handler(s):"DEDICATED" established:29 refused:0 state:blockedLOCAL SERVERSuggestion:1.You should wait for some minutes and try to connect. ( but it is temporary solution)2.You should increase PROCESSES parameter values.( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)Oracle Version : 10.1.0.2.0OS : Windows XP sp2------------------------------------Database configure in Shared Server Mode.--------------------------------------------SQL> show parameter dispatchersNAME TYPE VALUE------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)max_dispatchers integerSQL> show parameter shared_serversNAME TYPE VALUE------------------------------------ ----------- ------------------------------max_shared_servers integershared_servers integer 1------------------------------------------------------------------------------------------------after change shared server = 0 then i got error "ora-12520".SQL> alter system set shared_servers = 0;System altered.SQL> conn scott/tiger@shERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserverC:\>lsnrctl servicesService "sh" has 1 instance(s). Instance "sh", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:3 refused:0 current:2 max:1002 state:blocked DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))-------------------------------------------------------------------For above error you have to check configure two parameters.1.Shared_servers2.DispatchersSQL> alter system set shared_servers = 1;System altered.SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;System altered.Service "sh" has 1 instance(s). Instance "sh", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:4 refused:0 current:2 max:1002 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))The command completed successfully
14. ORA-12533 : TNS:illegal ADDRESS parametersThis error occurring because of TNSNAMES.ORA file content SYNTAX error so best solution is just recreate TNSNAMES.ORA file through NETCA tools.15. ORA-12535 : TNS:operation timed outhttp://www.dbmotive.com/oracle_error_codes.php?errcode=12535
ORA-12535: TNS:operation timed out
Probably there's something wrong with the host (server) name or ip address you are connecting to.First of all, open dos box or Unix shell script and do:tnsping yourdatabasenameto determine the host.This is the output that will be returned (Oracle 9.2 style)Used parameter files:c:oracleora92networkadminsqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA920.WORLD) (SERVER = DEDICATED)))
Timeout message
Now, try to ping the hostname or ip address that was returned by the tnsping command:ping hostnameIf the ping command doesn't succeed, there's something wrong with the networkPossibile causes are ip address conflicts, virtual ip addresses that can be resolved anymore, failed cluster groups or veritas cluster packages, DNS outages, ...The best way to solve this timeout is to look for any network issues.If the ping command succeeds, than it might be a firewall problem.Typically, when you connect to an Oracle listener (default port is 1521), a new process is spawned/thread created which listens on a different port.This port is send back to the client, and the client tries to make a TCP connection to the new oracle process/thread.So apart from adding the lsnrctl to the firewall rules, you should also add the oracle process to the firewall rule. The port given back to the client can range anywhere from 1024 to 65535. Windows users might have a look at the USE_SHARED_SOCKET = TRUE variable you can set in the registry ( HKEY_LOCAL_MACHINESOFTWAREORACLE)
16. ORA-12538 : TNS:no such protocol adapterThis error occurring because of TNSNAMES.ORA file content “(PROTOCAL= )” value is blank so just recreate TNSNAMES.ORA file and try.17. ORA-12541 : TNS:no listenerThis error occurring when client machine didn’t find “LISTENER” on server, possible reason 1. A listener service is not started so first start and tries.2. We have two oracle home of different version so TNS_ADMIN parameter didn’t point right network directory.Suppose: we have install oracle 9i or oracle 10g on same machine and TNS_ADMIN variable set to point oracle 9i home. And we trying to connect from client using oracle 10g client to oracle 10g server and we received “above” error means client not able to find 10g listener because TNS_ADMIN point to oracle 9i.So set TNS_ADMIN variable to point oracle 10g directory and try.18. ORA-12542 : TNS:address already in useCheck Metalink Note:1017513.10219. ORA-12545 : Connect failed because target host or object does not existCommon reason when we use “HOSTNAME” instead of “IP ADDRESS” in tnsnames.ora so just change HOSTNAME with IP ADDRESS.20. ORA-12546 : TNS:permission deniedThis error when user is not belongs to ORA_DBA group then just add OS user to ORA_DBA group and try.21. ORA-12547 : TNS:lost contactCheck Metalink Note:102893.122. ORA-12560 : TNS:protocol adapter errorCommon error when oracle database service is not started.cmd>set ORACLE_SID=sidnamecmd>net start OracleService and try23. ORA-12564 : TNS:connection refusedCause: The connect request was denied by the remote user (or TNS software). Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation.24. ORA-12571 : TNS:packet writer failureCause: An error occurred during a data send. Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation. If error persists, contact Oracle Customer Support.25. ORA-12638 : Credential retrieval failedhttp://dbataj.blogspot.com/2007/01/ora-12638-credential-retrieval-failed.html
1.Domain issueWhen Server DOMAIN is different FROM client DOMAIN.2.Password Change IssueWhen 0S password is different FROM APPLICATION password.3.NTS (Network Telephony Services)makes the Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:- The Oracle server is not configured to support Windows authentication- The credentials you use to login to your local machine are not sufficient to allow you to login to the server.In my case, our Network Administrator had changed the password OS user, while rebooting the system, the Oracle popped up with the Credential Failure.1.Replacing the line: NTS -----------> NONE (nts to none)SQLNET.AUTHENTICATION_SERVICES= (NTS)withSQLNET.AUTHENTICATION_SERVICES= (NONE)2. Install Oracle Client with LOCAL Administrator A/c.
26. ORA-28545 : error diagnosed by Net8 when connecting to an agentCause: An attempt to call an external procedure or to issue SQL to a non-Oracle system on a Heterogeneous Services database link failed at connection initialization. The error diagnosed by Net8 NCR software is reported separately. Action: Refer to the Net8 NCRO error message. If this isn't clear, check connection administrative setup in tnsnames.ora and listener.ora for the service associated with the Heterogeneous Services database link being used, or with 'extproc_connection_data' for an external procedure call.27. ORA-28546 : connection initialization failed, probable Net8 admin errorCause: A failure occurred during initialization of a network connection from the Oracle server to a second process: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 adminitrative setup for database links or external procedure calls. The most frequent specific causes are: -- Database link setup for an Oracle-to-Oracle connection instead connects to a Heterogeneous Services agent or an external procedure agent. -- Database link setup for a Heterogeneous Services connection instead connects directly to an Oracle server. -- The extproc_connection_data definition in tnsnames.ora connects to an Oracle instanceinstead of an external procedure agent. -- Connect data for a Heterogeneous Services database link, usually defined in tnsnames.ora, does not specify (HS=). -- Connect data for an Oracle-to-Oracle database link, usually defined in tnsnames.ora, specifies (HS=). Action: Check Net8 administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the connection from the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID connects to the correct program. -- Confirm in TNSNAMES.ORA or the equivalent service definition that sevice 'extproc_connection_data' does NOT contain (HS=), or that the service definition used by a Heterogeneous Services database link DOES contain (HS=).28. ORA-28547 : connection to server failed, probable Oracle Net admin errorCause: A failure occurred during initialization of a network connection from a client process to the Oracle server: The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server. -- The connection uses a connect string which includes an (HS=) specification. Action: Check Net8 administration in the following ways: -- When using TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent service definition that the connect string does NOT contain (HS=).29. ORA-29260 : network error: %shttp://www.dbmotive.com/oracle_error_codes.php?errcode=29260NOTE: Above are some more famous error related to NETWORK.TNS_ADMIN enviourment variableIt is very important parameter for network files.Also set this parameter to correct network files directory where LISTENER.ORA, TNSNAMES.ORA, SQLNET.ORA file exists.

Removing Jobs from Job Queue

Great Tips for Removing Jobs from Job Queue Automatically! !

Author: Burleson

Today we will discuss some great tips that will help you automatically remove jobs from the job queue. You can either use dbms_job in Oracle 9i or dbms_scheduler in Oracle 10g above to manage the scheduled jobs. The dbms_job is outdated by dbms_scheduler that offers lots more features.

Automatically Removing Scheduled Jobs from Job Queue:
The DBMS_JOB subprograms can be used to schedule and manage jobs in the job queue. The REMOVE procedure of DBMS_JOB subprograms can be used to remove an existing job from the job queue. However it does not stop a running job. Use dbms_job.remove( job => number) syntax to remove scheduled job from the job queue.
exec dbms_job.remove( 1213);

Automatically Removing Running Jobs from Job Queue:
In order to automatically removing running jobs from Job Queue you must first ensure that you want to abort a running task as for this purpose the rollback will be invoked. A simple shell script can be used to remove all running scheduled jobs.

Capture the SID and PID:
The v$session view represents the sessions currently connected to the instance whereas v$sysstat shows the summary of resource usage First of all we need to capture the SID and PID for all running jobs (v$session, v$sysstat).

Break Jobs:
Then we will break all jobs by using below syntax

EXEC DBMS_JOB.BROKEN( job#,TRUE) ;

Kill Session:
Finally we will kill the session for each (SID, PID) of the running jobs. You need to issue below command for this purpose

ALTER SYSTEM KILL SESSION 'sid,serial# ';

Automatically Removing Queued Jobs from Job Queue:
Below steps discuss how queued jobs can be removed from the job queue.

Stop all job execution:
First of all we will stop all job execution by running below command.

ALTER SYSTEM SET job_queue_processes = 0;

Find Jobs in Queue:
DBA_JOBS describes all jobs in the database. Now we will use DBA_JOBS to find the queued jobs.

Break Jobs:
You can use DBMS_JOB package to schedule a job to run at a specified time. Now we will break the jobs by using DBMS_JOBS.

Remove Jobs:
Finally we will remove the jobs by using the REMOVE procedure of DBMS_JOBS package.

exec dbms_job.remove( 1213);

Materialized Views in Oracle

Materialized Views in Oracle
Materialized Views in OracleA materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term). For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views. In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views. Primary Key Materialized ViewsThe following statement creates the primary-key materialized view on the table emp located on a remote database.SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48WITH PRIMARY KEY AS SELECT * FROM emp@remote_db;Materialized view created.Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.Rowid Materialized ViewsThe following statement creates the rowid materialized view on table emp located on a remote database:SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db; Materialized view log created.Subquery Materialized ViewsThe following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:SQL> CREATE MATERIALIZED VIEW mv_empdeptAS SELECT * FROM emp@remote_db eWHERE EXISTS(SELECT * FROM dept@remote_db dWHERE e.dept_no = d.dept_no)

duplicate database

Duplicating a Database with RMAN
Operating Environment Windows XP Prof. SP-2
Oracle Release / Version 10.1.0.2.0 Enterprise Edition
ORACLE_BASE d:\oracle
ORACLE_HOME d:\oracle\product\10.1.0
Target Database SID ORA101
Duplicate Database SID TESTDB
RMAN Catalog Database No recovery catalog. Using control file.
Archive Log Mode Enabled
-----------------------------------------------------------------------------------------------
1.Create password File for duplicate database
C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdtestdb.ora
password=oracle entries=5 force=y
----------------------------------------------------------------
2.Create Init.ora file for duplicate database. copy init.ora file from target database
(ora101) and edit.
SQL> create pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ora' from
spfile;
File created.
#Minimum Changes in init.ora file
db_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product
\10.1.0\oradata\TESTDB')
log_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product
\10.1.0\oradata\TESTDB')
background_dump_dest='D:\oracle\product\10.1.0\admin\testdb\bdump'
control_files='D:\oracle\product\10.1.0\oradata\testdb\control01.ctl','D:\oracle\product
\10.1.0\oradata\testdb\control02.ctl','D:\oracle\product\10.1.0\oradata\testdb\control03.ctl'
core_dump_dest='D:\oracle\product\10.1.0\admin\testdb\cdump'
db_name='testdb'
dispatchers='(PROTOCOL=TCP)(SERVICE=testdbXDB)'
user_dump_dest='D:\oracle\product\10.1.0\admin\testdb\udump'
---------------------------------------------------------------------------------------------
3.Create / Start the Auxiliary Instance (Duplicate Database)
Create all required directory.
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\bdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\cdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\udump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\pfile
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\scripts
C:\>mkdir d:\oracle\product\10.1.0\oradata\TESTDB
C:\>ORADIM -NEW -SID TESTDB
Instance created.
C:\>set ORACLE_SID=TESTDB
C:\>SQLPLUS "/ AS SYSDBA"
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 12:15:14 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='d:\oracle\product\10.1.0\db_2\database
\initTESTDB.ORA';
File created.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
SQL>
----------------------------------------------------------------
4.Mount or Open Target Database.(ora101)
C:\>sqlplus sys/oracle@ora101 as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 16:33:33 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
------------
OPEN
-------------------------------------------------
5.Make sure you have valid Target Database backup and Archive redo logs.
C:\>rman target sys@ora101
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:connected to target database: ORA101 (DBID=
5128390)
RMAN> configure controlfile autobackup on;
using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }
Starting backup at 25-DEC-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\ORA101\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\ORA101\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\ORA101\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\ORA101\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\ORA101\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA
\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042
_2RZKPF0Z_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-DEC-06
Starting backup at 25-DEC-06
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=610113930
input archive log thread=1 sequence=8 recid=2 stamp=610117104
input archive log thread=1 sequence=9 recid=3 stamp=610120818
input archive log thread=1 sequence=10 recid=4 stamp=610124804
input archive log thread=1 sequence=11 recid=5 stamp=610129547
input archive log thread=1 sequence=12 recid=6 stamp=610130542
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA
\ORA101\BACKUPSET\2006_12_25\O1_MF_ANNNN_TAG20061225T164224
_2RZKSLN0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
Finished backup at 25-DEC-06
Starting Control File and SPFILE Autobackup at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA
\ORA101\AUTOBACKUP\2006_12_25\O1_MF_S_610130562_
2RZKT33X_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-06
RMAN>
--------------------------------------------------------------------------
6.Configure TNSNAMES.ORA file for duplicate database.
#TNSNAMES.ORA (ORACLEHOME/NETWORK/ADMIN
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
#LISTENER.ORA ( ORACLEHOME/NETWORK/ADMIN
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=d:\oracle\product\10.1.0\db_2)
(SID_NAME=testdb)
)
cmd>lsnrctl reload
-------------------------------------------------------------------------
7.Connect to RMAN. (Duplicate Database with Different directory structure.)
C:\>rman TARGET=sys/oracle@ora101 AUXILIARY=sys/oracle@testdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORA101 (DBID=5128390)
connected to auxiliary database: testdb (not mounted)
RMAN>
run
2> {
3> allocate auxiliary channel c1 device type DISK;
4> allocate auxiliary channel c2 device type DISK;
5> allocate auxiliary channel c3 device type DISK;
6> DUPLICATE target database to TESTDB;
7>
}
allocated channel: c1channel c1: sid=160 devtype=DISK
allocated channel: c2channel c2: sid=159 devtype=DISK
allocated channel: c3channel c3: sid=158 devtype=DISK
Starting Duplicate Db at 25-DEC-06
contents of Memory Script:
{
set until scn 380106;
set newname for datafile 1 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
set newname for datafile 5 to "D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\EXAMPLE01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-DEC-06
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\EXAMPLE01.DBF
channel c1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA
\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042
_2RZKPF0Z_.BKP tag=TAG20061225T164042channel c1: restore complete
Finished restore at 25-DEC-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
switch clone datafile all
;
}executing Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=610136063 filename=D:\ORACLE\PRODUCT
\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=610136063 filename=D:\ORACLE\PRODUCT
\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=610136063 filename=D:\ORACLE\PRODUCT
\10.1.0\ORADATA\TESTDB\USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=610136064 filename=D:\ORACLE\PRODUCT
\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
set until scn 380106;
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-DEC-06
starting media recovery
archive log thread 1 sequence 12 is already on disk as file D:\ORACLE
\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG
\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARCarchive log thread 1 sequence 13
is already on disk as file D:\ORACLE\PRODUCT\10.1.0
\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1
_13_2RZPF26C_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0
\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1
_12_2RZKSFPG_.ARC thread=1 sequence=12archive log filename=D:\ORACLE
\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG
\2006_12_25\O1_MF_1_13_2RZPF26C_.ARC thread=1 sequence=13
media recovery complete
Finished recover at 25-DEC-06
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\EXAMPLE01.DBF";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\UNDOTBS01.DBF recid=1 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0
\ORADATA\TESTDB\SYSAUX01.DBF recid=2 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0
\ORADATA\TESTDB\USERS01.DBF recid=3 stamp=610136101
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0
\ORADATA\TESTDB\EXAMPLE01.DBF recid=4 stamp=610136102
datafile 2 switched to datafile copyinput datafilecopy recid=1 stamp=610136100
filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB
\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=2
stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA
\TESTDB\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy
recid=3 stamp=610136101 filename=D:\ORACLE\PRODUCT\10.1.0
\ORADATA\TESTDB\USERS01.DBFdatafile 5 switched to datafile copyinput
datafilecopy recid=4 stamp=610136102 filename=D:\ORACLE\PRODUCT
\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-DEC-06
RMAN>
----------------------------------------------------------
8.Create Tempfile for Temporary Tbs.
C:\>SQLPLUS SYS/ORACLE@TESTDB AS SYSDBA
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 18:25:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
SQL> alter tablespace temp add tempfile
2 'd:\oracle\product\10.1.0\oradata\TESTDB\temp01.dbf' size 10m;
Tablespace altered.

Adding Custom Messages to Oracle Alert Log!!

Almost every database administrator requires custom messages to be written into Oracle alert log. Writing custom messages to Oracle alert logs supplements default Oracle alerts with custom messages hence making it more clear and understandable .
Writing custom messages to your Oracle alert log file is not a big deal. You can do it in the same way as you write to some flat file. Today we will discuss some possible options that will make it easy and efficient to write messages to your Oracle alert log.
Using Oracle DBMS_SYSTEM Package:
Oracle DBMS_SYSTEM package supports an undocumented procedure KSDWRT. You can use KSDWRT procedure to write custom messages to your Oracle alert log.
Oracle KSDWRT procedure takes two parameters. The first parameter is a number that indicates the location. If you set it to 1 then it will write to trace file, if you set it to 2 then it will write to the alert file and if you set it to 3 then it will write your custom message to both alert log and the trace file. The second parameter is your custom message that you want to write to the alert log. For example if you want to add timestamp to your alert log then you will need to execute below command.
EXECUTE SYS.DBMS_SYSTEM.KSDWRT(2,TO_CHAR(SYSDATE) ' -- ');
Accessing Alert Log as External Table:
Another option to write custom messages to Oracle alert log is to map external files to Oracle table. Once your alert log is defined as an external table then you use SQL statements to access the alert log messages.
First of all we will create directory for alert log location.
CREATE DIRECTORY BDUMP AS '/U01/APP/ORACLE/ADMIN/MYSID/BDUMP';
Now we will create our alert log table.
CREATE TABLE MY_ALERTLOG ( MYCUSTOMESSAGE VARCHAR2(80) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY BDUMP ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE ) LOCATION('ALRT_MYSID.LOG' ) ) REJECT LIMIT 1000;
Now all you have to do is to query it with standard SQL as follows.
SELECT MYCUSTOMESSAGE FROM MY_ALERTLOG WHERE MYCUSTOMESSAGE LIKE '%ORA-00600%';
Oracle UTL_FILE Package:
Oracle UTL_FILE package provides an easy mechanism to write into Oracle alert file by allowing Oracle SQL and PL/SQL to read and write directly from flat files on the server. You just have to follow below simple steps.
In the first step we will get the location of Oracle alert log by running below command.
SELECT NAME INTO :ALERT_LOC FROM V$PARAMETER WHERE NAME =‘BACKGROUND_DUMP_DESTINATION';
Now we will set the UTL_FILE_DIR parameter.
ALTER SYSTEM SET UTL_FILE_DIR = ‘:MY_ALERTLOG');
Now we will open the file for write access.
UTL_FILE.FOPEN(':ALERT_LOC','ALERTPROD.LOG','W');
Finally we will write our custom message to the alert log and then close the file.
DBMS_OUTPUT.PUT_LINE('INVALID_APPLICATION_ERROR'); UTL_FILE.FCLOSE(':ALERT_LOC');


You can also write a stored procedure that will perform above steps so as to write custom messages to your Oracle alert log.
Operating System Utilities:
A number of UNIX/Linux utilities are available that can be used to write operating system messages to your Oracle alert log.
Procedural Languages:
Almost all procedural languages support functions for writing into file. Therefore you can use any procedural language to write messages to the Oracle alert log. Read Again!!

Handling Recordsets in Oracle Procedures!!

Handling Recordsets in Oracle Procedures!!

It is not a big deal to develop a stored procedure for performing select on a table. However it is really tricky to return a record set from a stored procedure as we have to use reference cursor in order to accomplish this task. In this article we will discuss how a resultset can be returned in an Oracle procedure and how we can use it in our calling program.
Create Table:
First of all we will create a table.
CREATE TABLE MYTABLE ( MYPK NUMBER(9) NOT NULL, MYDATE DATE NOT NULL, MYCLIENT NUMBER(9) NOT NULL, MYAMOUNT NUMBER(9,2) DEFAULT 0 NOT NULL, PAYMENT_STATUS NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid CONSTRAINT PK_MYTABLE PRIMARY KEY(MYPK) ) /

Create Sequence:
Now we will create a sequence for it.
CREATE SEQUENCE MYSEQ START WITH 1 CACHE 200 /
Insert Data:
Now we will insert some data in our table.
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,201,1200.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,202,1300.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,203,1400.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,204,1500.00);
Now if we want to pass resultset between PL/SQL sub programs and client application then we must define a reference cursor. Declaring same ref cursor in every program will add overhead and therefore we will be creating a single global type in a package specification. Once the package is defined then we just have to declare the cursor of that type in our procedures.
Create Package:
Now we will create a package that defines a cursor variable of type MYCURSOR. Remember MYCURSOR will be acting as an output variable.

CREATE OR REPLACE PACKAGE MYCURSOR_TYPE AS TYPE MYCURSOR IS REF CURSOR; END; /
Create Procedure:
Now we will create a procedure.


CREATE OR REPLACE PROCEDURE RETURN_MY_RECORDSET ( MYTABLEDATE IN DATE, MYRESULTSET OUT MYCURSOR_TYPE.MYCURSOR ) AS BEGIN OPEN MYRESULTSET FOR SELECT MYPK, MYDATE, MYCLIENT, MYAMOUNT FROM MYTABLE WHERE MYDATE <= MYTABLEDATE ORDER BY MYPK;END RETURN_MY_RECORDSET;

Return Resultset:
Now we will call the above procedure in order to return the resultset from our Oracle procedure.
VARIABLE MyVarResultSet REFCURSOR EXEC RETURN_MY_RECORDSET(sysdate, :MyVarResultSet); PRINT :MyVarResultSet
The output will be as follows.
MYPK MYDATE MYCLIENT MYAMOUNT ——— —————— ————— —————– 1 17-FEB-08 201 1200 2 17-FEB-08 202 1300 3 17-FEB-08 203 1400 4 17-FEB-08 204 1500
Create Temporary Table:
Now we will create a temporary table that will be holding the data returned from our procedure.
CREATE GLOBAL TEMPORARY TABLE MYTEMPTABLE ( MYPK NUMBER(9), MYDATE DATE, MYAMOUNT NUMBER(9,2), MYCLIENT NUMBER(9) ) ON COMMIT PRESERVE ROWS /
Store data in Temporary Table:
Now we will store data into our temporary table.
DECLARE c MYCURSOR_TYPE.MYCURSOR; MYTEMPTABLE_PK NUMBER(9); MYTEMPTABLE_DATE DATE; MYTEMPTABLE_AMOUNT NUMBER(9,2); MYTEMPTABLE_CLIENT NUMBER(9);

BEGIN RETURN_MY_RECORDSET(sysdate,c); LOOP FETCH C INTO MYTEMPTABLE_PK,MYTEMPTABLE_DATE,MYTEMPTABLE_AMOUNT,MYTEMPTABLE_CLIENT; exit when c%notfound; INSERT INTO MYTEMPTABLE VALUES(MYTEMPTABLE_PK,MYTEMPTABLE_DATE,MYTEMPTABLE_AMOUNT,MYTEMPTABLE_CLIENT); END LOOP; CLOSE c; COMMIT; END; /
Retrieve Data:


Now just we have to do is to query our temporary table to get the output from the temporary table. Hence you are able to easily use the records returned by the Oracle procedure in your calling program. You can process individual records or put it in the temporary table in your calling program. One thing you should keep in mind is that your calling program should close the cursor otherwise it will result in the problem of cursor leaks.
SQL> SELECT * FROM MYTEMPTABLE;
MYPK MYDATE MYCLIENT MYAMOUNT ——— —————— ————— —————– 1 17-FEB-08 201 1200 2 17-FEB-08 202 1300 3 17-FEB-08 203 1400 4 17-FEB-08 204 1500 Read Again!!

How to Import data without Dumpfile-Network Mode Import

Please visit my blog http://arjudba. blogspot. com add my blog links to your favourites.
http://arjudba. blogspot. comThank you for visiting my blog.
How to Import data without Dumpfile-Network Mode Import
It is possible to import data in a database without the dump file. From network the data will be retrieved from one database and then import that data back to the target database. There are no dump files involved.How to do the Network Mode Import:------------ --------- --------- -------1)Create a database link (with the source database) in the database where you will perform the import operation. SQL> create database link THERAP.REGRESS. RDBMS.DEV. US.ORACLE. COM connect to arju identified by a using 'NEPTUNE';Database link created.I have to used this long name because the database global name settings.2)Check the database link:SQL> select table_name from user_tables@ THERAP.REGRESS. RDBMS.DEV. US.ORACLE. COM;TABLE_NAME------------ --------- ---------FIRST_TABLETESTNAMESQL> select table_name from user_tables;no rows selected3)Perform the network mode import operation.Specify database link parameter with NETWORK_LINKSQL> !impdp arju/arju directory=dnet FLASHBACK_SCN= 118740640 NETWORK_LINK= THERAP.REGRESS. RDBMS.DEV. US.ORACLE. COMNote that no dumpfile parameter here. I created dnet directory into which log file will be written.Restrictions of Network Mode Import:------------ --------- --------- --------- ----1.If the source database is read-only, then the user on the source database must have a locally-managed tablespace assigned as a default temporary tablespace. Otherwise, the job will fail.2.If the USERID that is executing the import job has the IMP_FULL_DATABASE role on the target database, then that user must also have the EXP_FULL_DATABASE role on the source database.3.The only types of database links supported by Data Pump Import are: public, fixed-user, and connected-user. 4.When the NETWORK_LINK parameter is used in conjunction with the TABLES parameter, only whole tables can be imported (not partitions of tables).5.Network imports do not support the use of evolved types.
Please visit my blog http://arjudba. blogspot. com

Great Tips for Protecting Oracle Listener!!

In this article we will discuss some great tips that will help you protect your listener by setting password on it. This will help you prevent unintentional stopping of your listener and prevent illicit access to your listener.
Adding Oracle Cleartext Password:
If you set the password manually in listener.ora using the PASSWORDS_ parameter then your password will be stored in cleartext. You can add below entry in your existing listener.ora file.
PASSWORDS_MYLISTENER = (myPswrdVar1)
After that you will stop the listener by running below command and then restart it.
LSNRCTL> SET PASSWORD PASSWORD: LSNRCTL> STOP LSNRCTL> START

Adding Oracle Encrypted Password:
In order to add Oracle encrypted password you should set the password using LSNRCTL. LSNRCTL will encrypt the password stored in listener.ora.
LSNRCTL> SET CURRENT_LISTENER LSNRCTL> SET SAVE_CONFIG_ON_STOP ON LSNRCTL> CHANGE_PASSWORD
Old password: New password: Reenter new password: <>
LSNRCTL> SET PASSWORD PASSWORD: LSNRCTL> SAVE_CONFIG
The passwords you entered will not be echoed. Now if you check your listener.ora file then you will see a new parameter as PASSWORDS_ .
Now you will stop the listener by running below command.
LSNRCTL> SET PASSWORD PASSWORD: LSNRCTL> STOP LSNRCTL> START
Now if you check your listener.ora file then you will see new entries as follows
SAVE_CONFIG_ON_STOP_MYLISTENER = ON PASSWORDS_LISTENER = 2D6C48144CF753AC
If cleartext password is set for your listener then first you will be required to comment out the line PASSWORD_ in your listener.ora file, then restart listener and finally run above LSNRCTL commands.
You can use the PASSWORDS_listener_name parameter to store an encrypted password for a listener. This will make sure that the privileges operations like SAVE_CONFIG and STOP are secure.
PASSWORDS_MYLISTENER = (myPswrdVar1)
Password protecting Listener with forgot password:
If you have forgotten your listener password then first you will need to log in to operating system and kill the listener password process from your operating system. Then you will have to modify the PASSWORDS_LISTENER parameter in the listener.ora file and restart your listener. Finally you can use any of the above methods to set up password protection of your listener. Read Again!!
Note: It is recommended not to run any code portion directly on your production system.

DBA Interview questions

http://dbataj.blogspot.com/2008/04/preparation-for-dba-interview.html

What is REMOTE_LOGIN_ PASSWORDFILE?

What is REMOTE_LOGIN_ PASSWORDFILE?

•The remote REMOTE_LOGIN_ PASSWORDFILE instance parameter controls whether it is possible to connect to the instance as a user with the SYSDBA privilege over the network.•This parameter is string type and can have two values either NONE or SHARED. The default value is shared. In fact the value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED.•When this parameter is set to NONE the only way to get a SYSDBA connection is to log on to the operating system of the server machine as a member of the operating system group that owns the Oracle software.•Setting this parameter to either EXCLUSIVE or SHARED gives users another way in. They can log on to the server as SYSDBA even if they are not a member of the Oracle software owning group, or even if they are coming in across the network, they can still connect as SYSDBA.•The passwords are embedded, in encrypted form, in an operating system file in the location $ORACLE_HOME/ dbs/orapw on Unix, or %ORACLE_HOME% \pwd .ora database on Windows.•The V$PWFILE_USERS view shows you which users have their passwords entered in the password file, and whether they have the SYSOPER privilege, the SYSDBA privilege, or both.

Database Administrator Authentication
We sometimes need such special operation on database like, startup or shutdown the database or change the archival mode of the task. To do this, two special system privileges, SYSDBA and SYSOPER are required. You must have one of these privileges granted to you, depending upon the level of authorization you require.One thing we need to remember when you connect with SYSDBA or SYSOPER privileges, you connect with a default schema.For SYSDBA this schema is SYS and for SYSOPER the schema is PUBLIC.There are two methods are available for authenticating database administrators.1)Operating System(OS) Authentication. 2)Password File Authentication.Priority of Database Administrator Authentication Methods:------------------------------------------------------------Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If yes then use OS Authentication.Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If No then use Password file Authentication.Remote Database Administration ->If have Secure Connection?->If No then use Password file Authentication.Local Database Administration ->Want to use OS authentication?->If No then use Password file Authentication.Local Database Administration ->Want to use OS authentication?->If yes then use OS Authentication.Using Operating System Authentication-------------------------------------------Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER.On unix is is usually referred as dba/oper and on windows as ORA_DBA/ORA_OPER.If you are a member of the OSDBA/ORAOPER group and you specify AS SYSDBA/ AS SYSOPER when you connect to the database, then you connect to the database with the SYSDBA/SYSOPER system privilege. If you are not a member of OSDBA/ORAOPER group then OS authentication fails.How to Use Operating System AuthenticationTo enable operating system authentication of an administrative user:1.Create an operating system account for the user.2.Add the account to the OSDBA or OSOPER operating system defined groups.On unix the system is # id -a oracleuid=100(oracle) gid=100(oinstall) groups=101(dba)# useradd -g oinstall -G dba test# exitexitbash-3.00$ su testPassword:sh-3.00$ sqlplus / as sysdbaOn windows, click on my computer>Manage>Local Users and Groups>Add user and group.Using Password File Authentication--------------------------------------------1.If you don't have password file create one,2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).3.Connect to the database as user SYS (or as another user with the administrative privileges).4.If the user does not already exist in the database, create the user.5.Grant the SYSDBA or SYSOPER system privilege to the user:Here is the steps,1.orapwd FILE=newpwdfile.pwd PASSWORD=test ENTRIES=302.alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile3.SQL> conn / as sysdba4.SQL> create user test identified by t;5.SQL> !lsnrctl status and see the service name. Here my one is arju.therapbd.com6.grant sysdba to testAnd here is the test after step 5,SQL> grant create session to test;Grant succeeded.SQL> conn test/t as sysdbaConnected.SQL> conn test/t@neptune/arju.therapbd.com as sysdbaERROR:ORA-01031: insufficient privilegesWarning: You are no longer connected to ORACLE.SQL> conn / as sysdbaConnected.SQL> grant sysdba to test;Grant succeeded.SQL> conn test/t@neptune/arju.therapbd.com as sysdbaConnected.You can check who of you have password file authentication using,select username, SYSDBA,SYSOPER from v$pwfile_users;

Switchover to Physical Standby Database

Great Tips for Switchover to Physical Standby Database!!
Real Application Clusters Database:
Oracle Real Application Clusters (Oracle RAC) enables you to deploy single database
across a cluster of servers. No application changes are necessary and you can experience
fault tolerance, performance and scalability.
Switchover in a Real Application Clusters Database:
A single primary instance and only one standby instance can be active during a
switchover in a real application clusters database and hence you have to shutdown all but
one primary and one standby instance before a switchover.
Once a switchover is completed, you are required to restart those primary and standby
instances that were shut down during the switchover process.
Performing switchover when Other Instances are running:
If you try to perform a switchover when other instances are running then you will get
ORA-01105 as follows
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances
Switchover on Primary Database:
In order to perform a switchover, run below command on the primary database.
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
SESSION SHUTDOWN;
The above statement first terminates all active sessions by closing the primary database.
Then any non-archived redo log files are transmitted and applied to standby database.
Apart from that an end-of-redo marker is added to the header of the last log file that was
archived.
A backup of current control file is created and the current control file is converted into a
standby control file.
Bringing Standby Database as Primary Database:
You can bring up the standby database as the primary database on the standby database
server in case of primary site destroyed by a disaster.
The ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
command is used to cancel the Redo Apply or real-time apply.
The standby database is activated by using the ALTER DATABASE ACTIVATE
STANDBY DATABASE command. This command converts the standby database to a
primary database, creates a new reset logs branch, and then opens the database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Transition Standby Database to Primary Role:
The ACTIVATE statement used in above command is used to immediately transition the
standby database to the primary role. No attempt is made to apply any additional redo
data that may be exist at the standby location.
The ACTIVATE statement should be carefully used as the cost of data loss at standby
location should be balanced against the extended downtime period that is required to
repair the primary database.
Changed Database Role:
You can issue below command to check the changed database role.
select database_role, db_unique_name, name from v$database;
DATABASE_ROLE DB_UNIQUE_NAME NAME
————————- —————————— ————
PRIMARY stdb PRAC
Physical Standby Database with Time Lag:
You can also create a physical standby database with a time lag. You can set a delay for
standby database by modifying the LOG_ARCHIVE_DEST_2 initialization parameter on
the primary database . You can run below command to add a six hour delay.
The following is an example of how to add a 4-hour delay:
alter system set log_archive_dest_2='SERVICE=stdb LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=stdb' DELAY=360 '
scope=both;

Configuring Oracle Net Services

Configuring Oracle Net Services
This chapter decribes how to configure Oracle Net Services on AIX, HP, Linux, Solaris, and Tru64 systems. It contains
the following sections:

Location of Oracle Net Services Configuration Files

Adapters Utility

Oracle Protocol Support

Setting Up the Listener for TCP/IP or TCP/IP with SSL

Oracle Enterprise Manager

Configuring Oracle Intelligent Agent for Oracle SNMP

Oracle Advanced Security

Calling 32-Bit External Procedures from PL/SQL (AIX, HP, and Solaris 64-Bit Only)
Location of Oracle Net Services Configuration Files
Oracle Net Services configuration files are typically, but not always, located in the $ORACLE_HOME/network/admin
directory. Depending on the file, Oracle Net uses a different search order to locate the file.
The search order for the sqlnet.ora and ldap.ora files is as follows:
1. The directory specified by the TNS_ADMIN environment variable, if set
2. The $ORACLE_HOME/network/admin directory
The search order for the cman.ora, listener.ora, and tnsnames.ora files is as follows:
1. The directory specified by the TNS_ADMIN environment variable, if set
Oracle9i Administrator's Reference
Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX,
HP 9000 Series HP-UX, Linux Intel, and Sun Solaris
Part No. A97297-01
Contents
Index
See Also:
Oracle9i Net Services Administrator's Guide for more information on Oracle networking.
2. One of the following directories:
3.

For Solaris systems, the/var/opt/oracle directory

For AIX, HP, Linux, and Tru64, the /etc directory
4. The $ORACLE_HOME/network/admin directory
For some system-level configuration files, users may have a corresponding user-level configuration file (stored in the
user's home directory). The settings in the user-level file override the settings in the system-level file. The following
table lists the system-level configuration files and the corresponding user-level configuration files:
Sample Configuration Files
The $ORACLE_HOME/network/admin/samples directory contains samples of the cman.ora, listener.ora,
names.ora, sqlnet.ora, and tnsnames.ora configuration files.
Adapters Utility
Use the adapters utility to display the transport protocols, naming methods, and Oracle Advanced Security options
that Oracle9i supports on your system. To use the adapters utility, enter the following commands:
$ cd $ORACLE_HOME/bin
$ adapters ./oracle
The adapters utility displays output similar to the following:
Oracle Net transport protocols linked with ./oracle are
IPC
BEQ
TCP/IP
SSL
RAW
Oracle Net naming methods linked with ./oracle are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Oracle Names Server Naming
NIS Naming
Oracle Advanced Security options linked with ./oracle are:
RC4 40-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
System-Level Configuration File User-Level Configuration File
sqlnet.ora $HOME/.sqlnet.ora
tnsnames.ora $HOME/.tnsnames.ora
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
SHA crypto-checksumming (for FIPS)
SHA-1 crypto-checksumming
Kerberos v5 authentication
CyberSAFE authentication
RADIUS authentication
ENTRUST authentication
On the client, run the adapters utility to display the configured Oracle transport protocols, naming methods, and
security options on the system. To run the adapters utility on the client:
$ cd $ORACLE_HOME/bin
$ adapters
The adapters utility displays output similar to the following:
Installed Oracle Net transport protocols are:
IPC
BEQ
TCP/IP
SSL
RAW
Installed Oracle Net naming methods are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Oracle Names Server Naming
NIS Naming
Installed Oracle Advanced Security options are:
RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
CyberSAFE authentication RADIUS authentication
See Also:
Oracle9i Net Services Administrator's Guide for more information on the adapters utility.
Oracle Protocol Support
Oracle protocol support is a component of Oracle Net. It includes the following:

IPC protocol support

TCP/IP protocol support

TCP/IP with SSL protocol support
The IPC, TCP/IP, and TCP/IP with SSL protocol supports each have an address specification that is used in Oracle Net
Services configuration files and in the DISPATCHER initialization parameter in the initsid.ora file. The following
sections describe the address specifications for each of the protocol supports.
IPC Protocol Support
The IPC protocol support can be used only when the client program and the Oracle9i server are installed on the same
system. This protocol support requires a listener for its operation. It is installed and linked to all client tools and to
Oracle9i.
The IPC protocol support requires an address specification in the following format:
(ADDRESS = (PROTOCOL=IPC) (KEY=key))
The following table describes the parameters used in this address specification:
The following shows a sample IPC protocol address:
(ADDRESS= (PROTOCOL=IPC) (KEY=PROD))
TCP/IP Protocol Support
TCP/IP is the standard communication protocol used for client/server communication over a network. The TCP/IP
protocol support enables communication between client programs and the Oracle9i server, whether they are installed on
the same or different systems. If the TCP/IP protocol is installed on your system, the TCP/IP protocol support is
installed and linked to all client tools and to Oracle9i.
The TCP/IP protocol support requires an address specification in the following format:
(ADDRESS = (PROTOCOL=TCP) (HOST=hostname) (PORT=port))
See Also:
Oracle9i Net Services Administrator's Guide for more information on Oracle protocol support.
Parameter Description
PROTOCOL The protocol to be used. The value is IPC. It is not case sensitive.
KEY Any name unique from any other name used for an IPC KEY on the same system.
The following table describes the parameters used in this address specification:
The following shows a sample TCP/IP protocol address:
(ADDRESS= (PROTOCOL=TCP)
(HOST=MADRID)
(PORT=1521))
TCP/IP with SSL Protocol Support
The TCP/IP with SSL protocol support enables an Oracle application on a client to communicate with remote Oracle
databases through TCP/IP and SSL. To use TCP/IP with SSL, Oracle Advanced Security must be installed.
The TCP/IP with SSL protocol support requires an address specification in the following format:
(ADDRESS = (PROTOCOL=TCPS) (HOST=hostname) (PORT=port))
The following table describes the parameters used in this address specification:
The following shows a sample TCP/IP with SSL protocol address:
(ADDRESS= (PROTOCOL=TCPS)
(HOST=MADRID)
(PORT=2484))
Setting Up the Listener for TCP/IP or TCP/IP with SSL
Oracle Corporation recommends that you reserve a port for the listener in the /etc/services file of each Oracle Net
Services node on the network. The default port is 1521. The entry lists the listener name and the port number, for
example:
oraclelistener 1521/tcp
In this example oraclelistener is the name of the listener, as defined in the listener.ora file. Reserve more than
one port if you intend to start more than one listener.
If you use intend to use SSL, you should define a port for TCP/IP with SSL in the /etc/services file. Oracle
Corporation recommends a value of 2484. For example:
Parameter Description
PROTOCOL The protocol support to be used. The value is TCP. It is not case sensitive.
HOST The host name or the host IP address.
PORT The TCP/IP port. Specify the port as either a number or the alias name mapped to the port in
the /etc/services file. Oracle Corporation recommends a value of 1521.
Parameter Description
PROTOCOL The protocol to be used. The value is TCPS. It is not case sensitive.
HOST The host name or the host IP address.
PORT The TCP/IP with SSL port. Specify the port as either a number or the alias name mapped to the port in
the /etc/services file. Oracle Corporation recommends a value of 2484.
oraclelistenerssl 2484/tcps
In this example oraclelistenerssl is the name of the listener, as defined in the listener.ora file. Reserve more
than one port if you intend to start more than one listener.
Oracle Enterprise Manager
Use the oratclsh executable to debug your Tcl scripts. Before executing oratclsh, set the TCL_LIBRARY
environment variable to specify the $ORACLE_HOME/network/agent/tcl directory.
Configuring Oracle Intelligent Agent for Oracle SNMP
Although Oracle Intelligent Agent does not require Simple Network Management Protocol (SNMP) to work, you can
configure Oracle SNMP support before starting the Intelligent Agent. Note that all of the configuration files for the
following steps are located in the $ORACLE_HOME/network/snmp/peer directory.
Configure the Master Agent
In the CONFIG.master file, make the following change:
1. Search for the line beginning with MANAGER.
2. Change the value of the MANAGER parameter to the IP address or hostname of the system where you want
SNMP trap messages sent.
You can also make other changes to the CONFIG.master file as documented within the file.
3. On AIX systems only, add the following line to the /etc/snmpd.conf file, where ip_address is the IP address
of the Oracle subagent:
smux 0.0 " " ip_address
Configure the Encapsulator
To configure the encapsulator, perform the following steps:
1. Add the following line to the snmpd.conf file, where hostname_or_IP_address represents the local system IP
address or host name:
trap hostname_or_IP_address
2. On AIX systems only, modify the port numbers specified for the NEW_SNMPD_PORT and
NEW_TRAPD_PORT variables in the start_peer script, if necessary.
The port number specified for the NEW_SNMPD_PORT variable must be different to the port number specified
for the NEW_TRAPD_PORT variable.
See Also:
Intelligent Agent User's Guide for more information on debugging Tcl scripts.
3. On all platforms except AIX, complete the following steps:
a. If necessary, modify the port number specified in the CONFIG.encap file. The default port number is 1161.
b. If you modified the port number in step a, change the value specified for the NEW_SNMPD_PORT
variable in the start_peer script to this port number.
c. If necessary, modify the value specified for the NEW_TRAPD_PORT variable.
This variable specifies the PEER encapsulator port to which the snmpd agent sends traps. This port number
must be different to the port number specified for the NEW_SNMPD_PORT variable.
Verify the Location of the SNMP Daemon in the start_peer Script
The start_peer script contains a line similar to the following, where snmpd_executable_path is the path of the
snmpd executable:
SNMPD=snmpd_executable_path
Make sure that snmpd_executable_path is the location of the snmpd executable on your system.
Start the SNMP Components
To start the SNMP components, perform the following steps:
1. Enter the following commands to verify that the SNMP components (master_peer, encap_peer, and snmpd) are
not running:
$ ps -aef grep peer
$ ps -aef grep snmp
If any of the components are running, log in as the root user and use the kill command to terminate the
processes before proceeding.
2. On AIX systems only, enter the following command as the root user to start the native AIX SNMP agent:
# startsrc -s snmpd "-f /tmp/snmpd.log"
This command starts the SNMP daemon and logs information in the /tmp/snmpd.log file.
3. On all platforms except AIX, complete the following steps:
a. As the root user, run the start_peer script to start the PEER master agent, PEER encapsulator, and
native UNIX SNMP agent:
# cd $ORACLE_HOME/network/snmp/peer
# ./start_peer -a
Note:
b. Enter the following commands to verify that the SNMP components are running:
# ps -aef grep peer
# ps -aef grep snmp
Configure and Start the Database Subagent
If you do not have the native UNIX SNMP agent on your system, yo must not use
the PEER encapsulator. To start the master agent only, enter start_peer -m.
For information on configuring and starting the database subagent (the Oracle Intelligent Agent), see the Oracle
Enterprise Manager Configuration Guide.
Oracle Advanced Security
When you install Oracle Advanced Security, three .bak files are created: naeet.o.bak, naect.o.bak, and
naedhs.o.bak. These files are located in the $ORACLE_HOME/lib directory. They are required for relinking during
deinstallation of Oracle Advanced Security. Do not delete them.
Calling 32-Bit External Procedures from PL/SQL (AIX, HP, and
Solaris 64-Bit Only)
The 64-bit Extproc executable (extproc) and the 32-bit Extproc executable (extproc32) are installed in the
$ORACLE_HOME/bin directory. By default, the extproc executable is enabled to run 64-bit external procedures. To
enable 32-bit external procedures:
1. Set the value of the PROGRAM parameter in the listener.ora file:
(PROGRAM=extproc32)
2. Include the $ORACLE_HOME/lib32 directory in one of the following environment variables, depending on your
platform:
3. Shut down and restart the listener.
Platform Environment Variable
AIX LIBPATH
HP SHLIB_PATH
Solaris 64-bit LD_LIBRARY_PATH
Note:
You can configure the listener to run either 32-bit or 64-bit external procedures, but not
both at the same time.

Troubleshooting Oracle Performance Problems

Troubleshooting Oracle Performance Problems!!
It feels very frustrating when problems peep into your database and make it run slow. Well no need to worry any more as today we will discuss some efficient tips to determine the problems that makes your database run slow.
Find Session Waiting:
Oracle V$SESSION view lists the session information for all current sessions. First of all we will query V$SESSION view in order to find out the session waiting without Idle event.
SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME FROM V$ SESSION WHERE STATE = 'WAITING' AND WAIT_CLASS != 'IDLE';
We get the output as follows that shows that our user HR is waiting on ENQ: TX - ROW LOCK CONTENTION event that is blocked by Session 11.
SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME ------ ------------------ ---------------------------------------------------- ------------------------------- --------------------------- ----------------- 27 HR ENQ: TX - ROW LOCK CONTENTION 11 111 0
Find SQL Statement:
Now we will query the V$SESSION and V$SQL views so as to find out the SQL statements.
SELECT SID, SQL_TEXT FROM V$SESSION S, V$SQL Q WHERE SID IN (27,11) AND (Q.SQL_ID = S.SQL_ID OR Q.SQL_ID = S.PREV_SQL_ID);
The output will be as follows. Here we can see that our Session 11 has blocked Session 27. Therefore if we COMMIT or ROLL BACK Session 11 then session 27 will continue waiting for the lock.
SID SQL_TEXT ---------- -------------------------------------------------------------11 SELECT * FROM BIG_TABLE FOR UPDATE 27 SELECT DECODE(‘VAR','VAR','3','4') FROM DUAL
27 SELECT DECODE(‘VAR','VAR','3','4') FROM DUAL 27 DELETE FROM BIG_TABLE
Kill Session:
Now we will kill the session 11 and apply commit or roll back on session 11. SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME FROM V$ SESSION WHERE STATE = 'WAITING' AND WAIT_CLASS != 'IDLE'; no rows selected
Find Waiting Session:
In other case we find that now no session is blocking another session but this time our session 227 is waiting for DB FILE SEQUENTIAL READ event.
SELECT SID, USERNAME, EVENT, BLOCKING_SESSION,SECONDS_IN_WAIT, WAIT_TIME FROM V$ SESSION WHERE STATE = 'WAITING' AND WAIT_CLASS != 'IDLE';
We find the output as follows
SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME ------ ------------------ ---------------------------------------------------- ------------------------------- --------------------------- ----------------- 227 MANAGER DB FILE SEQUENTIAL READ 0 0
Find SQL Statement:
Now we will query the V$SESSION and V$SQL views so as to find out the SQL statements.
SELECT SID, SQL_TEXT FROM V$SESSION S, V$SQL Q WHERE SID IN (227) AND (Q.SQL_ID = S.SQL_ID OR Q.SQL_ID = S.PREV_SQL_ID);
The output will be as follows.
SID SQL_TEXT ---------- ---------------------------------------------------------------------------------------------------------------------------------- 227 SELECT "VAR1"."CUSID","VAR1"."LOGIN","VAR1"."DOMAIN","VAR1"."UNILOGIN","VAR1"."EMAIL","VAR1"."STATUS","VAR1"."MYPSWRDT","VAR1"."MYPSWRD","VAR1"."VIEWT","VAR1"."MAXCON","VAR1"."CHARGERATE","VAR1"."TIMEREMAIN","VAR1"."CASHREMAIN","VAR1"."EXPIREDATE","VAR1"."LDAP_SERVER","VAR1"."MAIL_MB","VAR1"."CONTROL","VAR1"."TIMEZONE","VAR1"."SPEED","VAR1"."CTRL_SERVICE","VAR1"."PKGID","VAR1"."PPID","VAR1"."GID","VAR1"."MASTERACCT","VAR1"."FROM_WALLET","A1"."LASTACCESS","VAR1"."CREATEDATE","VAR1"."LASTUPDATE","VAR1"."OPR_BY","VAR1"."MYPSWRD2","VAR1"."MYPSWRDT2","VAR1"."VAS","VAR1"."CONTROL2","VAR1". "FIRSTACCESS" FROM "ACM" "VAR1" WHERE "VAR1"."GID"=:B1
227 SELECT "VAR1"."GID","VAR1"."ASSETNUM","VAR1"."BCYCLE","VAR1"."BAN","VAR1"."PKGID","VAR1"."STATUS","VAR1"."CREATEDATE" FROM "GROUPINFO" "VAR1" WHERE "VAR1"."ASSETNUM" IS NOT NULL AND "VAR1"."ASSETNUM"<>'020000000' AND "VAR1"."BCYCLE"=:B1
Oracle V$SESSION_WAIT_CLASS View:
Oracle view can be used to identify whether the session is bound mostly by user I/O and the like. It displays the time spent in various wait event operations on a per-session basis.
SELECT WAIT_CLASS_ID, WAIT_CLASS, TOTAL_WAITS, TIME_WAITED FROM V$SESSION_WAIT_CLASS WHERE SID = 25371; WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED ------------- ---------------------------------------------------------------- ----------- ----------- 1893977003 OTHER 1224 39 3835070507 CONCURRENCY 711 120 27233371728 IDLE 2118450 121891 20071174339 NETWORK 2118520 273 17407277717 USER I/O 1484880 37155715 38713711733 CLUSTER 972791 2102717 71 ROWS SELECTED.
Oracle V$SYSTEM_EVENT view:
You can use V$SYSTEM_EVENT view to get an insight into the system events that are currently happening within your system. For example we can focus on the USER I/O and find timed wait on V$SYSTEM_EVENT view.
SELECT EVENT, TOTAL_WAITS, TIME_WAITED FROM V$SYSTEM_EVENT E, V$EVENT_NAME N WHERE N.EVENT_ID = E.EVENT_ID AND E.WAIT_CLASS_ID = 17407277717 ; EVENT TOTAL_WAITS TIME_WAITED ----------------------------------------- ----------------------- --------------------- DATA FILE INIT WRITE 43 9 LOCAL WRITE WAIT 352 47 READ BY OTHER SESSION 2371087 714739 DB FILE SEQUENTIAL READ 41918354 271039340 DB FILE SCATTERED READ 19110727 7177108712 DB FILE SINGLE WRITE 93971 2739 DB FILE PARALLEL READ 30337183 227114971 DIRECT PATH READ 248340 8235 DIRECT PATH READ TEMP 20547719 122773 DIRECT PATH WRITE 109045 857 DIRECT PATH WRITE TEMP 1137183 22748 11 ROWS SELECTED.
Here we can see that the DB FILE SEQUENTIAL READ event has maximum total wait and maximum time waited. Now what you have to do is to enable the trace file on session 227 and then find out where the database has been waiting.

Library Cache Performance Tuning

V$LIBRARYCACHE
Collecting Library Cache Statistics
Interpretation
Tuning Suggestions
Allocating More Memory
Write Identical SQL Statements
Speeding Access to Shared SQL Areas on Execution Calls
Caching Session Cursors
The statistics found in the v$librarycache table reflect all library cache activity since the most recent instance
startup. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE
system privilege, such as SYSTEM.
Each row in this table contains statistics for a specific item kept in the library cache. The item described by each
row is identified by the value of the NAMESPACE column. Rows of the table with the following NAMESPACE
values reflect library cache activity for SQL statements and PL/SQL blocks:

'SQL AREA'

'TABLE/PROCEDURE'

'BODY'

'TRIGGER'
Rows with other NAMESPACE values reflect library cache activity for object definitions that Oracle uses for
dependency maintenance. The following columns of the V$LIBRARYCACHE table reflect library cache misses
on execution calls:

PINS This column shows the number of times an item in the library cache was executed.
RELOADS This column shows the number of library cache misses on execution steps.

Note: If you get a chance take a look at the SNMP . Its pretty cool.
Collecting Library Cache Statistics
To collect statistics query the V$LIBRARYCACHE table over a period of time with this query:
SELECT SUM(pins) "Executions",
SUM(reloads) "Cache Misses while Executing"
FROM v$librarycache;
Executions Cache Misses while Executing
---------- ----------------------------
320871 549
Interpreting the V$LIBRARYCACHE Table
Examining the data returned by the sample query leads to these observations:

The sum of the PINS column indicates that SQL statements, PL/SQL blocks, and object definitions were
accessed for execution a total of 320,871 times.

The sum of the RELOADS column indicates that 549 of those executions resulted in library cache misses
causing Oracle to implicitly reparse a statement or block or reload an object definition because it had aged
out of the library cache.

The ratio of the total RELOADS to total PINS is about 0.17%. This value means that only 0.17% of
executions resulted in reparsing.
Total RELOADS should be near 0. If the ratio of RELOADS to PINS is more than 1%, then you should reduce
these library cache misses through the means discussed in the next section.
Reducing Library Cache Misses
You can reduce library cache misses by

Allocating Additional Memory for the Library Cache
You may be able to reduce library cache misses on execution calls by allocating additional memory for the
library cache. To ensure that shared areas remain in the cache once their SQL statements are parsed,
increase the amount of memory available to the library cache until the V$LIBRARYCACHE.RELOADS
value is near 0. To increase the amount of memory available to the library cache, increase the value of the
initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter depends on your
operating system. This measure will reduce implicit reparsing of SQL statements and PL/SQL blocks on
execution. To take advantage of additional memory available for shared SQL areas, you may also need to
increase the number of cursors permitted for a session. You can increase this limit by increasing the value
of the initialization parameter OPEN_CURSORS. Be careful not to induce and swapping by
allocating too much memory for the library cache. The benefits of a library cache large enough to avoid
cache misses can be partially offset by reading shared SQL areas into memory from disk whenever you
need to access them.

Writing Identical SQL Statements
You may be able to reduce library cache misses on parse calls by ensuring that SQL statements and
PL/SQL blocks share a shared SQL area whenever possible. For two different occurrences of a SQL
statement or PL/SQL block to share a shared SQL area, they must be identical according to these criteria:
The text of the SQL statements or PL/SQL blocks must be identical, character for character,

including spaces and case. For example, these statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM emp;
These statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM Emp;

References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in
the same schema. For example, if the schemas of the users BOB and ED both contain an EMP table and
both users issue the following statement, their statements cannot use the same shared SQL area:
SELECT * FROM emp;
SELECT * FROM emp;
If both statements query the same table and qualify the table with the schema, as in the following
statement, then they can use the same shared SQL area: SELECT * FROM bob.emp;

Bind variables in the SQL statements must match in name and datatype. For example, these statements
cannot use the same shared SQL area: SELECT * FROM emp WHERE deptno = :department_no;
SELECT * FROM emp WHERE deptno = :d_no;

The SQL statements must be optimized using the same optimization approach and, in the case of the costbased
approach, the same optimization goal. For information on optimization approach and goal, see
Chapter 9, "Tuning SQL Statements". Shared SQL areas are most useful for reducing library cache misses
for multiple users running the same application. Discuss these criteria with the developers of such
applications and agree on strategies to ensure that the SQL statements and PL/SQL blocks of an application
can use the same shared SQL areas:

Use bind variables rather than explicitly specified constants in your statements whenever possible. For
example, the following two statements cannot use the same shared area because they do not match
character for character: SELECT ename, empno FROM emp WHERE deptno = 10; SELECT ename,
empno FROM emp WHERE deptno = 20; You can accomplish the goals of these statements by using the
following statement that contains a bind variable, binding 10 for one occurrence of the statement and 20 for
the other: SELECT ename, empno FROM emp WHERE deptno = :department_no; The two occurrences of
the statement can use the same shared SQL area.

Be sure that users of the application do not change the optimization approach and goal for their
individual sessions. You can also increase the likelihood that SQL statements issued by different
applications can share SQL areas by establishing these policies among the developers of these applications:

Standardize naming conventions for bind variables and spacing conventions for SQL statements and
PL/SQL blocks.

Use stored procedures whenever possible. Multiple users issuing the same stored procedure automatically
use the same shared PL/SQL area. Since stored procedures are stored in a parsed form, they eliminate
runtime parsing altogether.
Speeding Access to Shared SQL Areas on Execution Calls
If you have no library cache misses, you may still be able to speed execution calls by setting the value of
the initialization parameter CURSOR_SPACE_FOR_TIME. This parameter specifies when a shared SQL
area can be deallocated from the library cache to make room for a new SQL statement. The default value of
this parameter is FALSE, meaning that a shared SQL area can be deallocated from the library cache
regardless of whether application cursors associated with its SQL statement are open. The value of TRUE
means that a shared SQL area can only be deallocated when all application cursors associated with its
statement are closed. Depending on the value of CURSOR_SPACE_FOR_TIME, Oracle behaves
differently when an application makes an execution call. If the value is FALSE, Oracle must take time to
check that a shared SQL area containing the SQL statement is in the library cache. If the value is TRUE,
Oracle need not make this check because the shared SQL area can never be deallocated while an
application cursor associated with it is open. Setting the value of the parameter to TRUE saves Oracle a
small amount of time and may slightly improve the performance of execution calls. This value also
prevents the deallocation of private SQL areas until associated application cursors are closed.
Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if there are library cache misses on
execution calls. Such library cache misses indicate that the shared pool is not large enough to hold the
shared SQL areas of all concurrently open cursors. If the value is TRUE and there is no space in the shared
pool for a new SQL statement, the statement cannot be parsed and Oracle returns an error saying that there
is no more shared memory. If the value is FALSE and there is no space for a new statement, Oracle
deallocates an existing shared SQL area. Although deallocating a shared SQL area results in a library cache
miss later, it is preferable to an error halting your application because a SQL statement cannot be parsed.
Do not set the value of CURSOR_SPACE_FOR_TIME to TRUE if the amount of memory available to
each user for private SQL areas is scarce. This value also prevents the deallocation of private SQL areas
associated with open cursors. If the private SQL areas for all concurrently open cursors fills the user's
available memory so that there is no space to allocate a private SQL area for a new SQL statement, the
statement cannot be parsed and Oracle returns an error indicating that there is not enough memory.
Caching Session Cursors
If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the
session cursors can affect system performance. Session cursors can be stored in a session cursor cache.
This feature can be particularly useful for applications designed using Oracle Forms because switching
between forms closes all session cursors associated with a form.
Oracle uses the shared SQL area to determine if more than three parse requests have been issued on a given
statement. If so, Oracle assumes the session cursor associated with the statement should be cached and
moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the
same session will then find the cursor in the session cursor cache.
To enable caching of session cursors, you must set the initialization parameter
SESSION_CACHED_CURSORS. This parameter is a positive integer that specifies the maximum number
of session cursors kept in the cache. A least recently used (LRU) algorithm ages out entries in the session
cursor cache to make room for new entries when needed.
You can also enable the session cursor cache dynamically with the ALTER SESSION SET
SESSION_CACHED_CURSORS command.
To determine whether the session cursor cache is sufficiently large for your instance, you can examine the
session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of
times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of
the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a
larger value.

Oracle Multitable Inserts

Oracle Multitable Inserts, Important Concerns!!
All database administrators and developers encounter single table INSERT statements
every now or then. Oracle also supports a multiple-table INSERT statement that enables
you to make a single pass through the source data and load the data into more than one
table.
Oracle multitable Insert feature has been available since Oracle since 9i. There are two
types of multi-table insert: simple multitable insert and conditional multitable insert.
Simple multitable insert will insert values into all of the tables whereas conditional
multitable insert insert rows into some of the tables instead of all of them.
Oracle Multitable Insert Syntax:
The syntax of Oracle multitable Inserts is as follows
INSERT [ ALL FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]
The `ALL` keyword makes database check each WHEN condition whereas the `FIRST`
keyword makes database to stop checking the WHEN conditions once first TRUE
condition is met. If WHEN condition is TRUE then the corresponding INTO clause will
be executed. However, if no WHEN condition is TRUE then ELSE clause will be
executed. The INSERT ALL statement allows insertion of several different values clauses
in a single statement.
Simple Multitable Insert:
create table MYTABLE1 (COL1 NUMBER(30), COL2 NUMBER(30) );
Table created.
create table MYTABLE2 (COL3 NUMBER(30), COL4 DATE);
Table created.
CREATE SEQUENCE MYSEQ INCREMENT BY 1;
Sequence created.
INSERT ALL
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+1 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+2 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID+3 )
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID +4)
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID +5)
select MYID, created from all_objects
/
Conditional Multitable Insert:
create table MYTABLE1 (COL1 NUMBER(30), COL2 NUMBER(30) );
Table created.
create table MYTABLE2 (COL3 NUMBER(30), COL4 DATE);
Table created.
CREATE SEQUENCE MYSEQ INCREMENT BY 1;
Sequence created.
insert
when mod( MYID, 2 ) = 1 then
into MYTABLE1 ( COL1, COL2 ) values ( MYSEQ.nextval, MYID )
when mod( MYID, 2 ) = 0 then
into MYTABLE2 ( COL3, COL4 ) values ( MYSEQ.nextval, created )
select MYID, created from all_objects
/
Multitable INSERT statement increases performance as the code will do a single pass
through the all_objects table instead of two. Hence your data will be loaded faster.
If you had used single table insert then you would be subject to cost of SELECT
statement twice; once for each insert into…select statement. The SELECT statement will
be run only once in a multi-table insert and therefore you will be subject to the cost of
SELECT statement only.
Limitations of Oracle Multitable Inserts:
Apart from its beauty there are some limitations of Oracle multitable inserts. Oracle
multitable inserts can be performed only on tables. You can not attempt multitable inserts
on views, materialized views or on a remote table. All of insert_into_clause s cannot
combine to specify more than 999 target columns in Oracle multitable insert. Oracle does
not guarantee the order of inserts in spite of the explicit ordering in the select clause.
While performing a multitable insert, a table collection expression can not be specified.
Also a subquery of the multitable insert statement cannot use a sequence.
Multitable inserts are not parallelized in RAC environment. Similarly if your target table
is index organized or has a bitmap index defined on it, then also multiple inserts are not
parallelized. Oracle multitable insert statements does not support plan stability.