Wednesday, August 6, 2008

How to use MERGE in Oracle

MERGE is used to select rows from one or more sources, and update or insert into a table or view. You cannot update the same row of the target table multiple times in the same MERGE statement.

Syntax:

MERGE INTO
<table/view>
USING <table/view/subquery> ON (condition)

--update

WHEN MATCHED THEN
UPDATE SET column = (value/expr),..
WHERE (condition) | DELETE WHERE (condition)

--insert

WHEN NOT MATCHED THEN
INSERT (column,..) VALUES (value/expr,..)
WHERE (condition)

--error log

LOG ERRORS INTO
<table>

If the ON clause condition is true

then UPDATE section gets executed.

else

INSERT section gets executed.

Restrictions on the merge update:

1. You cannot update a column that is referenced in the ON condition clause.

2. Cannot specify DEFAULT when updating a view.

See the example code below.

Create a target table called TEST1 and inset some test data

CREATE TABLE test1 (
co1 VARCHAR2(3),
co2 VARCHAR2(3),
co3 VARCHAR2(3),
co4 NUMBER );

ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY(co1);

INSERT INTO test1 VALUES('1', 'val', 'sd1', 100);
INSERT INTO test1 VALUES('2', 'va2', 'sd2', 100);
INSERT INTO test1 VALUES('3', 'va3', 'sd3', 100);
INSERT INTO test1 VALUES('4', 'va4', 'sd4', NULL);
INSERT INTO test1 VALUES('5', 'va5', 'sd5', 100);

commit;


Create a target table called TEST2 and inset some test data

CREATE TABLE test2 (
a1 VARCHAR2(3),
a2 NUMBER );

ALTER TABLE test2 ADD CONSTRAINT test2_pk PRIMARY KEY(a1);

INSERT INTO test2 VALUES('1', 10);
INSERT INTO test2 VALUES('3', 20);
INSERT INTO test2 VALUES('6', 30);
INSERT INTO test2 VALUES('10', 40);
INSERT INTO test2 VALUES('15', 50);
INSERT INTO test2 VALUES('2', -10);

commit;

Now by using the MERGE statement we are going to update/delete or either insert records to the target table(test1) by using the source (test2).

1. MERGE INTO test1 a
2. USING (select a1, a2
3. from test2 ) b
4. ON (a.co1 = b.a1)
5. WHEN MATCHED THEN
6. update set a.co4 = a.co4 * b.a2,
7. a.co2 = b.a2
8. where co3 = \'sd3\'
9. delete where co3 = \'sd3\'
10. WHEN NOT MATCHED THEN
11. insert (a.co1, a.co2, a.co3, a.co4) values
12. (b.a1, null, null, b.a2);


line 2. uses a subquery to select the source records.
line 4. ON clause specify the condition. This condition controls the MERGE to perform update or insert. If true then update else insert.
line 6., 7. updates the columns by using the source column data.
line 9. deletes the target row after updations when the delete condition satisfied. This delete clause evaluates the updated value not the original values.
This statement executes only when ON clause condition (line 4) returns true.
line 10.When ON clause is false then this insert statement is executed.


How tnsnames.ora and sqlnet.ora file work together?

I would like to bring an article which explains how useful sqlnet and tnsnames files are and how they work together.

The default domain in the Sqlnet file is used for a SCENARIO purpose only.

SCENARIO 1:

in tnsnames.ora

dba.domain = (....)

in sqlnet.ora file

names.default_domain = domain


So, in sqlplus log screen, the following can be typed in: hr/hr@dba

Note the fully qualified dbadomain would not be typed in, because it will be automatically appended to the end of the connect string specified.

SCENARIO 2:

in tnsnames.ora

dba.domain = (....)

SCENARIO.NW.NOS.ORALCE.COM = (....)

in sqlnet.ora file

names.default_domain = domain

What would happen in the SCENARIO where multiple domain have been defined in the tnsnames.ora but with only one sqlnet.ora? Just remember, the key is what is defined in the TNSNAMES.ORA file, sqlnet.ora is for convenience.

The entire connect string can be typed in.

In sqlplus log screen, just type in :

hr/hr@test
<--- should again work

hr/hr@test.domain
<--- should again work

hr/hr@SCENARIO
<--- should *NOT* work, because in fact it will be SCENARIO.domain

hr/hr@SCENARIO.NW.NOS.ORALCE.COM
<--- should work

What
is being said is that if the domain is added after the name when loginning as defined in the tnsnames.ora file, then don\'t use the default domain that is defined in the sqlnet.ora file.

SCENARIO
3:

In
fact a sqlnet.ora file is not really needed for the purposes of login

E.g.,
in tnsnames.ora

test
= (....) <--- note here, no domain

SCENARIO.NW.NOS.BOEING.COM
= (....)

no sqlnet.ora file exists anywhere.

In
sqlplus log screen, just type in :

hr/hr@test
<--- should again work

hr/hr@SCENARIO.NW.NOS.BOEING.COM
<--- should work

hr/hr@SCENARIO
<--- should *NOT* work

hr/hr@test.domain
<--- should *NOT* work

RDA





Source file exported to clipboard (as text)






Remote Diagnostic Agent (RDA)



On Monday morning I received a mail from my client regarding installation of oracle 10g on RHEL ES 4 platform. I started the installation and ended up with many errors. While struggling to get required RPMS and going through metalink documents I found usage of RDA.

I used to hear about RDA even when working with Oracle Corporation but never thought how useful RDA is.

Remote Diagnostic Agent (RDA) is a set of command line diagnostic scripts. RDA is used to gather detailed information about an Oracle environment.

Oracle Support encourages the use of RDA because it provides a comprehensive picture of the customer's environment.


Some times in the platform like RHEL to find required RPMs and compatibility RDA looks very great. Eben you might run custom xml scrips with this RDA.


All installation instructions are given in metalink note 314422.1.

The Tools can be run in SHELL or PEARL environment.



Example

Ater installation of RDA /home/oracle/rda



To start data collection

[oracle@rda]$ ./rda.sh

-------------------------------------------------------------------------------

RDA Data Collection Started 06-Feb-2008 11:50:16 AM

-------------------------------------------------------------------------------

Processing Initialization module ...

Processing CFG module ...

Processing Sampling module ...

Processing OCM module ...

Processing OS module ...

Processing PROF module ...

Processing PERF module ...

Processing RDSP module ...

Processing LOAD module ...





This will generate many files including a zip file usually required by Oracle Support.







RDA includes health Check Validation Engine



To check HCVE



Run



Processing HCVE tests ...

Available Pre-Installation Rule Sets:

1. Oracle Database 10g R1 (10.1.0) PreInstall (Linux-x86)

2. Oracle Database 10g R1 (10.1.0) PreInstall (Linux AMD64)

3. Oracle Database 10g R1 (10.1.0) PreInstall (IA-64 Linux)

4. Oracle Database 10g R2 (10.2.0) PreInstall (Linux AMD64)

5. Oracle Database 10g R2 (10.2.0) PreInstall (IA-64 Linux)

6. Oracle Database 10g R2 (10.2.0) PreInstall (Linux-x86)

7. Oracle Application Server 10g (9.0.4) PreInstall (Linux)

8. Oracle Application Server 10g R2 (10.1.2) PreInstall (Linux)

9. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux AMD64)

10. Oracle Application Server 10g R3 (10.1.3) PreInstall (IA-64 Linux)

11. Oracle Application Server 10g R3 (10.1.3) PreInstall (Linux-x86)

12. Oracle Portal PreInstall (Generic)

Available Post-Installation Rule Sets:

13. Oracle Portal PostInstall (generic)

14. RAC 10G DB and OS Best Practices (Linux)

15. Data Guard PostInstall (Generic)

Enter the HCVE rule set number

Hit 'Return' to accept the default (1)

>



Select your choice and press enter



Test Results

~~~~~~~~~~~~



ID NAME RESULT VALUE

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

10 OS Certified? PASSED Adequate

20 User in /etc/passwd? PASSED userOK

30 Group in /etc/group? PASSED GroupOK

40 Input ORACLE_HOME RECORD $ORACLE_HOME

50 ORACLE_HOME Valid? PASSED OHexists

60 O_H Permissions OK? PASSED CorrectPerms

70 Umask Set to 022? PASSED UmaskOK

80 LDLIBRARYPATH Unset? FAILED IsSet

100 Other O_Hs in PATH? PASSED NoneFound

110 oraInventory Permiss PASSED oraInventoryOK

120 /tmp Adequate? PASSED TempSpaceOK

130 Swap (in MB) RECORD 4605

140 RAM (in MB) PASSED 2007

150 Swap OK? PASSED SwapOK

160 Disk Space OK? PASSED DiskSpaceOK

170 Kernel Parameters OK FAILED SHMMAXTooSmall SEMOPMTooSmall

180 Got ld,nm,ar,make? PASSED ld_nm_ar_make_found

190 ulimits OK? FAILED StackTooSmall MaxLockMemTooSmall

200 EL4 RPMs OK? PASSED NotEnterprise

203 RHEL21 RPMs OK? PASSED NotRHEL21

204 RHEL3 RPMs OK? PASSED NotRHEL3

205 RHEL4 RPMs OK? PASSED RHEL4rpmsOK

208 SUSE SLES8 RPMs OK? PASSED NotSuSE

209 SUSE SLES9 RPMs OK? PASSED NotSuSE

212 Patch 3006854 Instal PASSED NotRequired

213 LD_ASSUME_KERNEL Set PASSED NotRHEL3

214 ip_local_port_range FAILED LowTooHigh HighTooLow

220 Tainted Kernel? PASSED NotVerifiable

230 Other OUI Up? PASSED NoOtherOUI



This will give you a fair idea of environment before installation.



And always RDA will have a result file, some time Oracle Support may ask this file. Usually this will be generated in output folder.



So try to learn RDA as quickly as possible. This tool helps in many ways.



Purpose of this document was to just highlight what RDA is and how best to use it. Refer Metalink for downloadable, getting started and installation instructions.




upgrade





Source file exported to clipboard (as text)






Upgrade database from 9i to 10g

First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.


Example.

1.you create sample database in oracle 9i.
2.create os user for oracle10g

su - root
groupadd dba # group of users to be granted SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c oracle10g -g oinstall -G dba oracle
passwd oracle

3.Download oracle 10g software from oracle web site then install oracle 10 in that server.
4.Then set environment variables for oracle10g.

For example 9i database name test

the database to be upgraded is called test.

then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory

Login to 9i database,

$sqlplus "/as sysdba"
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

sql>spool /tmp/upgrade.txt
sql>@/tmp/utlu102i
Oracle Database 10.2 Upgrade Information Utility 06-20-2006 16:39:48
.
**********************************************************************
Database:
**********************************************************************
--> name: test
--> version: 9.2.0.6.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 439 MB
.... AUTOEXTEND additional space required: 109 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 18 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 5 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 49 MB
.... AUTOEXTEND additional space required: 4 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 177861837
WARNING: --> "java_pool_size" needs to be increased to at least 67108864
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora
or spfile]
**********************************************************************
--> "hash_join_enabled"
.
**********************************************************************
Components: [The following database components will be upgraded
or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... CTXSYS
.... XDB
.... WMSYS
.... ORDSYS
.... MDSYS
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

SQL> spool off
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Under most circumstances, at this point, the database would be backed up. This demonstration will skip over that part of the upgrade

Next, we create a PFILE from our SPFILE that we can make changes to:

SQL> create pfile from spfile;

File created.

SQL> exit
$ vi /u01/app/oracle/product/9.2.0/dbs/inittest.ora


Edit the pfile using this the diagnostic output as a guide. Here is my file:

*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u02/oradata/test/control01.ctl',
'/u02/oradata/test/control02.ctl',
'/u02/oradata/test/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_cache_size=117440512
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fast_start_mttr_target=300
#*.hash_join_enabled=TRUE
*.instance_name='test'
#*.java_pool_size=33554432
*.java_pool_size=67108864
*.large_pool_size=28311552
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_max_open_files=20
#*.shared_pool_size=38797312
*.shared_pool_size=177861837
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'



set pagesize 50000
tti "Database growth per month for last year"
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
;





Monday, August 4, 2008

Upgrade database from 9i to 10g

Upgrade database from 9i to 10g


First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.


Example.
1.you create sample database in oracle 9i.2.create os user for oracle10g
su - root

groupadd dba # group of users to be granted SYSDBA system privilege

groupadd oinstall # group owner of Oracle files

useradd -c oracle10g -g oinstall -G dba oraclepasswd oracle


3.Download oracle 10g software from oracle web site then install oracle 10 in that server.

4.Then set environment variables for oracle10g.


For example 9i database name test.The database to be upgraded is called test.then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory




Login to 9i database,


$sqlplus "/as sysdba"

Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production



Run utlu102i.sql

This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql

Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

Shut the database down with either normal or immediate
shutdown immediate

Copy the spfile (or pfile) and the password file from the existing home to the 10g one.

cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* /dbs/

Upgrade the database

sqlplus "/ as sysdba"

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 398458880 bytes
Fixed Size 1261068 bytes
Variable Size 276824564 bytes
Database Buffers 117440512 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
sql>

CREATE TABLESPACE sysaux
DATAFILE '/u01/ora9R2/oradata/sysaux01.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

This next bit is the upgrade itself. It takes roughly half an hour to complete.
Spool the output to a file so that you can review it afterward.

@?/rdbms/admin/catupgrd.sql

Create a sysaux tablespace

Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.

create tablespace sysaux
datafile '' size 512M
extent management local
segment space management auto
/

Recompile any invalid objects

SQL> select count(*) from dba_objects where status='INVALID';

@?/rdbms/admin/utlrp.sql

Then check the status of the upgrade

@?/rdbms/admin/utlu102s.sql

SGA

SGA:
The SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database.

sql>SHOW SGA



Total System Global Area 168783792 bytes
Fixed Size 737200 bytes
Variable Size 134217728 bytes
Database Buffers 33554432 bytes
Redo Buffers 274432 bytes

Total System Global Area : - Total in bytes of all the sub-divisions that makes up the SGA.

Fixed Size: Fixed size contains general information about the state of the database and the instance, which the background processes need to access. This does not store user data. Usually this area is less than 100k in size.

Variable Size: This part is influenced by the following init.ora parameters. shared_pool_size large_pool_size java_pool_size Database Buffers:

This holds data blocks copies that are read from datafiles and can be calculated by using following formula. size = db_block_buffers * block size Redo Buffers: This is another buffer in the SGA that holds information about changes made to the database.

Approximating size calculation of the SGA

Oracle 9i: To approximate size of the SGA (Shared Global Area), use following formula: db_cache_size + db_keep_cache_size + db_recycle_cache_size + db_nk_cache_size + shared_pool_size + large_pool_size + java_pool_size + log_buffers + 1mb

How Deadlock occurs?

Dealdlock

Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock.

Example:
create table sample(id number,Country varchar2(10) );

insert into sample values ( 1, 'China' );
insert into sample values ( 2, 'France' );
commit;

SQL> Select * from sample ;
id country
1 China
2 France


Issue the following command in session1:
SQL> update sample set country='Italy' where id=1;

Issue the following command in Session 2:
SQL> update sample set country='Canada' where id=2;
SQL> update sample set country='Canada' where id=1;

Session2 is now waiting for the lock held by Session1

Issue the following command in session1:
SQL> update sample set country='Italy' where id=2;

This update would cause Session1 to wait on the lock held by Session2, but Session2 is already waiting on this session. This causes a deadlock