Oracle DB links

Here are the steps to create Db links from one database to another database.
I am creating a database link in SRITSTDB(source db) for schema/user "TEST" to SRISTGDB(target db).

Step 1: Adding a TNS entry for target db in source db host machine.

a) Open tnsnames.ora file in SRITSTDB database.           It will be located in $ORACLE_HOME/network/admin      b) add the tns entry like below.           "SRISTGDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hello)(PORT=1521))           (CONNECT_DATA=(SID=SRISTGDB)))"

Step 2: Login as user in which you want to create this db link.

Here I mentioned for TEST user. So i am logging as "TEST" user as below.      UNIX PROMPT>sqlplus test/test)))"

Step 3: Creating a database link.
Use the below sql to create the syntax.

Syntax: Create database link linkname connect to user identified by pwd using 'CONEECT_STRING'; Usage 1:Create database link SRISTGDBLINK CONNECT to test identified by test                using 'SRISTGDB'; Note: If there is any problem in TNS setup on that host use the follwing method to create the db link. I am using the complete connect string descriptor instead of just connect string name. Some times If you are trying to use this db link from sql navigator or from sql*plus it will give "ORA-12541: TNS: no listener". For that use the follwing syntax to create the dblink. Usage 2:Create database link SRISTGDBLINK CONNECT to test identified by test using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hello)(PORT=1521))(CONNECT_DATA=(SID=SRISTGDB)))';

Step 4: Checking.

a) Connect as TEST user to the SRITSTDB.      b) select name from v$database@SRISTGDBLINK;

Applying Oracle Patches on 10.2.0.2 home

The follwoing patch numbers are required for a new Oracle 10.2.0.2 home. I have applied these patches on Solaris 10 operating system. Check your operating system before applying.

The main thing in this article is, applying a group of patches at a time using some scripts.

Step 1: We have to apply following 6 Patches to 10.2.0.2 home after upgrading it from 10.2.0.1. The following are the required Patches.

1) 5117016     2) 5458753     3) 5075470     4) 4689959     5) 4712729     6) 4966417

Step 2: Create a directory /tmp/Patches/

Step 3: Create a file "apply_all.sh" file with the following code.
cd /tmp/Patches
vi apply_all.sh

#!/bin/sh set PATH=${PATH}:${ORACLE_HOME}/OPatch; export PATH #This is the Oracle home where the patches will apply. set OBJECT_MODE=32_64; export OBJECT_MOD for patchid in `cat patches`; do current_dir=`pwd` cd $patchid $ORACLE_HOME/OPatch/opatch apply #This is OPatch software directory which we are using to apply patches. cd $current_dir done exit

Step 4: Create a file "apply_all.sh" file with the following code.
The above script uses the "patches" file which contains all the patch numbers which we have to apply. So just create a file "patches" with the following content.

vi patches     5458753     5075470     4689959     4712729     4966417 Note: This patchs file may contain any number of patches. It may be one or any number. Here we are applying 6 patches, so this file contains all the 6 Patch numbers which we are applying to this home.

Step 5: Make sure the all the databases and listeners are down, if any of the databases are using this oracle 10.2.0.2 home.
NOTE:If it is a new host/new home then there is no need to stop any databases because it may not used by any databases.

Step 6:Execution of "apply_all" script.

a) set the environment to current oracle home i.e. 10.2.0.2 - setenv ORACLESID b) Double check the $ORACLE_HOME; env | grep ORA ; -> It should set to 10.2.0.2 home. b) cd /tmp/Patches; c) chmod 775 apply_all.sh d) ./apply_all

Oracle Database manual upgrade from 9.2.0.7 to 10.2.0.3

This upgrade is also similar to upgrade from 10.2.0.2 to 10.2.0.3 except in 9i there is no SYSAUX tablespace. We have to manually create the SYSAUX tablespace before we do upgrade.

Just follow all the steps described in my previous post "Oracle Database manual upgrade from 10.2.0.2 to 10.2.0.3" In Step 7 modify the steps as below.

Step 7: Start the upgrade
UNIX PROMPT> mkdir /tmp/upgrade/ -> create this directory to store the log files.
UNIX PROMPT> sqlplus "/as sysdba"
SQL> startup upgrade

After giving “statup upgrade” create the sysaux tablespace as follows.
SQL> CREATE TABLESPACE sysaux DATAFILE '/opt/mis/oracle/data/data/sysaux1a.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
Now the upgrade process will starts. It will take atleast 45 mins to 1:15 mins.
At last it will show the components list and their status.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:28:27
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:04:42
Oracle XDK VALID 10.2.0.3.0 00:01:02
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:36
Oracle Expression Filter INVALID 10.2.0.3.0 00:00:22
Oracle Enterprise Manager VALID 10.2.0.3.0 00:03:51

SQL>spool off;

Oracle Database manual upgrade from 10.2.0.2 to 10.2.0.3

I am providing step by step Oracle database manual upgrade process from 10.2.0.2 to 10.2.0.3

NOTE: I hope you have already created oracle 10.2.0.3 home in your machine.
On my machine I have oracle 10.2.0.2 binaries in "/opt/mis/oracle/product/10.2.0.2" path and oracle 10.2.0.3 binaries in "/opt/mis/oracle/product/10.2.0.3" path.

This is for solaris/linux servers.

Step 1: Pre checks
a) Check SYSTEM table space has atleast 150 MB free.
b) Check shared_pool_size parameter is set to 400MB.
Show parameter shared_pool_size;
If it is not set do "ALTER SYSTEM SET SHARED_POOL_SIZE='400M' SCOPE=both;"
c) Check java_pool_size parameter is set to 150MB.
Show parameter java_pool_size
If it is not set, do ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;"

Step 2: Disable all cron jobs related to the ORACLE_SID
This step is to ensure that no cron(unix) jobs are running for this database. It is not needed if you do not have any cron jobs running for this ORACLE_SID.
To check cron jobs for a particular ORACLE_SID do " crontab -l grep ORACLE_SID "

Step 3: Create spfile if it doesn't exist in 10.2.0.2_HOME/dbs directory.
Check whether your current database is mounted using spfile or not. For this use the follwing query.
SQL> SELECT NVL(value,'NO')FROM v$parameter WHERE NAME='spfile';
If it is not mounted using spfile, create an spfile using...
UNIX PROMPT> cd $ORACLE_HOME/dbs
UNIX PROMPT> sqlplus "/as sysdba"
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;

Step 4: Change the $ORACLE_HOME enviroment variable
Now we have to set the $ORACLE_HOME environment variable to 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
ORACLE_HOME=/opt/mis/oracle/product/10.2.0.2 (This is before change.)

You have to change this in the enviroment file. In my solaris system these environment variables are stored in .cshrc file in the home directory.
Any way the normal environment variable file entries will look as follows.
Change the ORACLE_HOME variable. See below.
unsetenv LD_LIBRARY_PATH_64
unsetenv LD_LIBRARY_PATH
setenv ORACLE_SID BMC4INT
setenv ORACLE_BASE /opt/mis/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/10.2.0.3
setenv LD_LIBRARY_PATH_64 $ORACLE_HOME/lib:$ORACLE_HOME/javavm/lib
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/javavm/lib
unsetenv TWO_TASK
unsetenv ORACLE_SERVER
setenv DBA $ORACLE_BASE/admin # OFA
setenv NLS_LANG AMERICAN_AMERICA.UTF8
setenv ORA_NLS10 $ORACLE_HOME/nls/data # new in 10.2

Step 5: Copy the spfile.ora from 10.2.0.2_oracleHome/dbs to 10.2.0.3_oraclehome/dbs directory.
UNIX PROMPT> cd /opt/mis/oracle/product/10.2.0.2/dbs/
UNIX PROMPT> cp spfileORALCE_SID.ora /opt/mis/oracle/product/10.2.0.3/dbs/
NOTE:Do not change the COMPATIBLE parameter. It should set
to 10.2.0.2 only.

Step 6: Double check env variables for 10.2.0.3
UNIX PROMPT> env | grep ORACLE_HOME
Now it should show 10.2.0.3

Step 7: Start the upgrade
UNIX PROMPT> mkdir /tmp/upgrade/ -> create this directory to store the log files.
UNIX PROMPT> sqlplus "/as sysdba"
SQL> startup upgrade
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
Now the upgrade process will starts. It will take atleast 45 mins to 1:15 mins.
At last it will show the components list and their status.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.3.0 00:28:27
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:04:42
Oracle XDK VALID 10.2.0.3.0 00:01:02
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:36
Oracle Expression Filter INVALID 10.2.0.3.0 00:00:22
Oracle Enterprise Manager VALID 10.2.0.3.0 00:03:51

SQL>spool off;

Step 8: Set the COMPATIBLE parameter to 10.2.0.3.
SQL> alter system set compatible='10.2.0.3' scope=both;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter compatible; -> It should show 10.2.0.3

Step 9: Check the status of all the components of DBA_REGISTRY:
SQL> select comp_name,comp_id,version,status from dba_registry;
SQL> exit;

Step 10: Create a script called "recomp.sql" in "/tmp/upgrade" directory.
This script is used to execute utlrp.sql file along with some other manual compile statemetns.
UNIX PROMPT> cd /tmp/upgrade/
UNIX PROMPT> vi recomp.sql
Add the follwing entries to the file.

col object_type for a20
col object_name for a32
col owner for a12
spool recomp.log
select owner,object_type,count(1) from dba_objects where status<>'VALID'
group by owner,object_type;
@?/rdbms/admin/utlrp
set echo on feed on
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
alter session set nls_length_semantics=byte;
alter view KU$_XMLSCHEMA_VIEW compile;
select owner,object_type,object_name from dba_objects where status<>'VALID' and
owner in ('SYS','SYSTEM');
spool off;
exit;

UNIX PROMPT> nohup sqlplus "/ as sysdba" @recomp.sql &
It will execute the file in nohup mode.
Step 11: Checking
Select comp_name,comp_id,version,status from dba_registry;
Now all the objects should show "VALID" state.

Step 12: Change the Listener
Change the listener.ora file in $ORACLE_HOME/network/admin/ folder and make home
point to new 10.2.0.3 home.

Step 13: Restore the cron jobs for this ORACLE SID

OPEN_CURSORS parameter

This parameter is used to specify the number of open cursors each session can have at once. It is used to prevent session from opening more cursors.

Value for this parameter: If a session does not open the number of cursors specified by OPEN_CURSORS, there will not be any overhead to setting this value higher than actually needed.

OPEN_CURSORS simply allocates a fixed number of slots but does not allocate memory for these slots for a client (eg: it sets an array up to have 1,000 cursors for example but does not allocate 1,000 cursors).

Good Article regarding Automatic Shared Memory Management.

I found this article very useful regarding DB_Cache_Size, Large_pool, Java_pool and Shared_pool.

The url is as here.
http://www.oracle.com/technology/pub/articles/10gdba/week17_10gdba.html

kgl_large_heap_warning_threshold parameter

The following message will appear in alert.log file.
Heap size 2294K exceeds notification threshold (2048K).

This error occurs due to a process spending lot of time in finiding free memory extents during an allocate. Warning messages are written in alert.log file when heap size exceeds this threshold value.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
I guess it needs a quick bounce;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter _kgl_large_heap_warning_threshold
SQL> SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';

Please see oracle metalink Note:330239.1 for more information.

Note: "_kgl_large_heap_warning_threshold" was a new parameter in 10.2.0.1 and Oracle internal threshold was very low. Hence it was recommended to be set at 8388608 in order to avoid too many warnings in the alert log about heap size. In 10.2.0.2 it was fixed and set at 50MB which is default and is no longer necessary to be set.

Oracle Life

Life is an Oracle... Make your life as deep as a Database. Be always as unique as a Primary Key. Be productive as an Index. Take others' good habits as a Foreign Key. Share your joys with others as a Cluster. Always keep note of your doings as good as a Data Dictionary. Grant some privileges to others as a Role. Secure the things as a DBA. Do the things in order as a Rowid. Follow your resolution as strict as a Sequence. Light other's life as a Trigger. Be prepared for the worst as an Exception. Always be fruitful as a Function. Forgive others as a Rollback Transaction. Be thankful to God as a Commit Transaction. Always help others like Checkpoint. Choose the best path like an Optimizer. Project yourself the best like a View.