Tuesday 11 September 2012

ERROR : Connection was refused with error ORA-12519

Please check MOS id:552765.1


One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

In this case please check v$resource_limt view to find maximum utilization and available utilization.

For this,


Select resource_name,current_utilization,max_utilization ,limit_value from v$resource_limit;

If current utilization reaches the limit then increase the appropriate value, then if necessary bounce back the database.

In my case it processes limit has been exceeded,  so i increase both nodes and restarted the RAC DB.

Check lsnrctl status,

It may show status is blocked.

In my case;

On both nodes ---
SQL> alter system set sessions=800 scope=spfile;

Sistema modificado.

SQL> alter system set processes=1000 scope=spfile;


shutdown immediate
startup ------------  using svctl

processes integer 1000
sessions integer 1500
Because sessions are derived from processes.. Keep in mind...

ASM insufficient Privileges and while starting syntax error or unresolved network name 'LISTENER_+ASM'

For the above error,

as well as [oracle@racdr ~]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 8 13:00:14 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount pfile ='/u01/app/oracle/diag/asm/+asm/+ASM/trace/init+ASM.ora';
ORA-01031: insufficient privileges
SQL> conn sys as sysasm
Enter password:
ERROR:
ORA-01031: insufficient privileges

We  need to export all the home paths corectly

For ex.. while starting ASM.(11g)

ORACLE HOME should be grid home and also Grid home should be grid home..
export ORACLE_SID as +ASM


If you have any parameter like sqlnet.authentication_services inside your sqlnet.ora file comment that one. And also comment the LOCAL_LISTENER 'LISTENER_+ASM' (if exist ) in the pfile.

Better U create a pfile using ASM alertlog File

Following Steps has to be done.

 $export ORACLE_SID=+ASM
$export ORACLE_HOME=<Your Grid Oracle Home>
$export PATH=$PATH:$ORACLE_HOME/bin

Create the pfile with below values under $ORACLE_HOME/dbs

$cd $ORACLE_HOME/dbs
$vi init+ASM.ora

large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_power_limit = 1
diagnostic_dest = "/u01/app/oracle"
asm_diskstring='/dev/oracleasm/disks/*'

$sqlplus "/as sysasm"

sql>startup pfile='?/dbs/init+ASM.ora';

sql>alter diskgroup data mount; (Mention ur diskgroup name)


To catch details of ur ASM

You can use following commands for Linux.
# rpm -qa |grep oracleasm
# /etc/init.d/oracleasm status
# lsmod |grep -i asm
# cat /proc/filesystems |grep -i asm
# ls -l /usr/sbin/oracleasm-discover
# /etc/init.d/oracleasm listdisks
$ /etc/init.d/oracleasm listdisks | xargs /etc/init.d/oracleasm querydisk -p
# ls -l /dev/oracleasm/disks/*
# cat /proc/partitions
# cat /etc/sysconfig/oracleasm
# ls -l /etc/sysconfig/oracleasm*
Ur comments are welcome


 

Monday 10 September 2012

Migration of 10.2.0.3(32-Bit) database to 11.2.0.3(64-Bit)

Test Setup

Initial Platform : Redhat Linux 5 update 6 (32 – bit)

Since Oracle DB 10.2.0.3 is not a complete patch set, we need to install the base version that is 10.2.0.1 and create a test database ( here RMS), later upgrade it into 10.2.0.3 for test setup.

Before installing 10g, do the prerequisites, and also install all the required rpms which you can find inside RHEL media Server directory.

After the Successful installation of Oracle 10g & creation of test database,

Download the patchset for 10.2.0.3 and unzip the same.

Before installing the patchset.

1.Stutdown the database.
2.Stop the listener >> lsnrctl stop
3.stop dbconsole >> emctl stop dbconsole
4.stop isqlplusctl >> isqlplusctl stop
[oracle@localhost ~]$ . .bash_profile

[oracle@localhost ~]$ emctl status dbconsole

TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Oracle Enterprise Manager 10g is running.


Logs are generated in directory /home/oracle/oracle/product/10.2.0/db_1/localhost.localdomain_rms/sysman/log

[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.

[oracle@localhost ~]$ emctl status dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is not running.

Unzip the Patch set for 10.2.0.3 u can get Disk1 directory.

Install the patch set in the existing path, where 10.2.0.1 binaries installed. It will automatically update the binaries. Or else you can use Opatch utility.

[oracle@localhost ~]$ cd Disk1/

[oracle@localhost Disk1]$ ./runInstaller

Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed
All installer requirements met
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-08-20_05-33-38AM. Please wait ...[oracle@localhost Disk1]$ Oracle Universal Installer, Version 10.2.0.3.0 Production
Copyright (C) 1999, 2006, Oracle. All rights reserved.
Asia/Calcutta
[oracle@localhost Disk1]$
For Ex.. Updation of binaries process..

Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList.class"

Updating jar file "/home/oracle/oracle/product/10.2.0/db_1/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_jobs/_jobsList$__jsp_StaticText.class"

After the successful upgradation of binaries, You can start the database with upgrade mode.

Or you can upgrade using dbua utility.

sql> startup upgrade;

Run Catupgrd.sql

@?/rdbms/admin/catupgrd.sql

Oracle Database Server VALID 10.2.0.3.0 00:08:09
JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:05
Oracle XDK VALID 10.2.0.3.0 00:00:41
Oracle Database Java Packages VALID 10.2.0.3.0 00:00:48
Oracle Text VALID 10.2.0.3.0 00:00:26
Oracle XML Database VALID 10.2.0.3.0 00:01:38
Oracle Data Mining VALID 10.2.0.3.0 00:00:22
Oracle Rule Manager VALID 10.2.0.3.0 00:00:12

.

Total Upgrade Time: 00:25:08

DOC>#######################################################################
DOC>#######################################################################

Bounceback the database with normal mode then run utlrp.sql script to validate invalid objects.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup;

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

COUNT(*)

506

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
 
SQL> select count(*) from dba_objects where status = 'INVALID';

COUNT(*)

0

Check the status and version of all components

SQL> select comp_name , status ,version from dba_registry;

COMP_NAME STATUS VERSION

Oracle Database Catalog Views VALID 10.2.0.3.0
Oracle Database Packages and Types VALID 10.2.0.3.0
Oracle Workspace Manager VALID 10.2.0.1.0
Server JAVA Virtual Machine VALID 10.2.0.3.0
Oracle XDK VALID 10.2.0.3.0
Oracle Database Java Packages VALID 10.2.0.3.0
Oracle Expression Filter VALID 10.2.0.3.0
Oracle Data Mining VALID 10.2.0.3.0
Oracle Text VALID 10.2.0.3.0
Oracle XML Database VALID 10.2.0.3.0
Oracle Rule Manager VALID 10.2.0.3.0
Oracle interMedia VALID 10.2.0.3.0
OLAP Analytic Workspace VALID 10.2.0.3.0
Oracle OLAP API VALID 10.2.0.3.0
OLAP Catalog VALID 10.2.0.3.0
Spatial VALID 10.2.0.3.0
Oracle Enterprise Manager VALID 10.2.0.3.0

Everything is fine now..

Download Oracle DB 11.2.0.3 and install it in a different path
After Installing 11.2.0.3.. Check the prerequisites to upgrade to 11g for the existing 10g database.

Inside 11g ORACLE_HOME/rdbms/admin path you can find utlu112i.sql script copy it into temporary location and run it on 10g database to check the prerequisites.
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 21 05:43:27 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-21-2012 05:44:01
Script Version: 11.2.0.3.0 Build: 001
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Components: [The following database components will be upgraded or installed]
**********************************************************************

--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] INVALID
 
Changes will need to be made in the init.ora or spfile. Check the timezone

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

632

SQL> select * from v$timezone_file;

timezlrg.dat 3

Before Upgrade Purge the recycle bin and also gather the statistics for all schemas.

SQL> purge dba_recyclebin;

Upgradation using DBUA

Export the Oracle 11g Home path in the bash Profile.
In 11g home invoke dbua...

1.select the database which needed to upgrade..


I:\Screenshot-4.png

I:\Screenshot-4.png



(For our test DB RMS has been selected)

2.Select the timezone too.
4.Select the degree of parallelism


I:\Screenshot-3.png

I:\Screenshot-3.png


It will take long time to complete.


I:\Screenshot-6.png

I:\Screenshot-6.png


Everything will be upgraded automaticallically, when you use dbua including timezone.


I:\Screenshot-7.png

I:\Screenshot-7.png



Export 11g Home and check the status of all components.

SQL> col comp_name for a40;

SQL> /

COMP_NAME STATUS VERSION
 
Oracle Enterprise Manager VALID 11.2.0.3.0
OLAP Catalog VALID 11.2.0.3.0
Spatial VALID 11.2.0.3.0
Oracle Multimedia VALID 11.2.0.3.0
Oracle XML Database VALID 11.2.0.3.0
Oracle Text VALID 11.2.0.3.0
Oracle Data Mining VALID 11.2.0.3.0
Oracle Expression Filter VALID 11.2.0.3.0
Oracle Rule Manager VALID 11.2.0.3.0
Oracle Workspace Manager VALID 11.2.0.3.0
Oracle Database Catalog Views VALID 11.2.0.3.0
Oracle Database Packages and Types VALID 11.2.0.3.0
JServer JAVA Virtual Machine VALID 11.2.0.3.0
Oracle XDK VALID 11.2.0.3.0
Oracle Database Java Packages VALID 11.2.0.3.0
OLAP Analytic Workspace VALID 11.2.0.3.0
Oracle OLAP API VALID 11.2.0.3.0
17 rows selected.

SQL> select * from v$timezone_file;

FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14

We have upgraded the database from 10.2.0.3(32-Bit) to 11.2.0.3(32-Bit).

Now, We have to migrate this database to 64-bit

 
1. Install 64-bit RHEL 5.6 and get the server ready.

2. Do the prerequisites before install 11g like kernel settings

3. Install all the required rpms.( u can find inside server directory of RHEL Media)

4. Create oracle user under dba group

5. Give the appropriate privileges to Oracle user, in which software has to be installed.

6. Install Oracle 11.2.0.3 64-Bit Software.

 
Clone the database from 32-bit server to 64 – Bit Server by creating appropriate directory.

(Using SCP copy all the data, control , redo and spfiles to 64- bit Server)

(Comment audit_trail_file parameter in pfile and create spfile by the same)

In the New Server After the Cloning,(Export Bash profile)
Startup Upgrade;
Run
@?/rdbms/admin/utlirp.sql -------

PL/SQL procedure successfully completed.

DOC>#######################################################################

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

Normal Shutdown

Shutdown;

startup;

SQL> @?/rdbms/admin/utlrp.sql
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 23 01:58:50 2012

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

COUNT(*)
 
0

SQL> select platform_name from v$database;

PLATFORM_NAME
--------------------------------------------------------------------------------
Linux x86 64-bit

SQL> select comp_name ,version,status from dba_registry;

COMP_NAME VERSION STATUS

------------------------------ -----------
Oracle Enterprise Manager 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Data Mining 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rule Manager 11.2.0.3.0 VALID
Oracle Workspace Manager11.2.0.3.0 VALID
Oracle Database Catalog Views11.2.0.3.0 VALID
Oracle Database Packages and Types11.2.0.3.0 VALID
JServer JAVA Virtual Machine11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages11.2.0.3.0 VALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID
17 rows selected.
SQL> select ( case length(address) when 16 then '64 - bit oracle'
2 when 8 then '32-bit'
3 when 32 then '128-bit' end) wordsize
4 from v$sql
5 where rownum < 2;

WORDSIZE

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

64 - bit oracle

Unix Server Performance related to RAM - 32 Bit server

As every one knows top is the best command to calculate the Performance in Unix.

Here I have an issue, Where in I have to prove that the added RAM increased the performance and also th server is using that RAM.

As understood from experts points, Normally 32 bit server only address upto 4GB RAM.
But in my case it is utilizing the whole 8GB. But I am unable to increase my kernel.shmmax parameter value. If so not able to start ORACLE Database.

But This RAM Upgradation is helpfull in some ways,

For Example it is not going fo SWAP Space.

[oracle9i@dbserver oracle9i]$ free -g
             total       used       free     shared    buffers     cached
Mem:             7          7          0          0          0          7
-/+ buffers/cache:          0          7
Swap:            5          0          5


[oracle9i@dbserver oracle9i]$ uname -i
i386

sar -r gives,



07:20:00 AM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
07:30:00 AM     25816   8221028     99.69         0    258648   7485636   6143992         0      0.00
07:40:00 AM     25656   8221188     99.69         0    258944   7485816   6143992         0      0.00
07:50:00 AM     22484   8224360     99.73         0    258868   7486748   6143992         0      0.00
08:00:00 AM     17924   8228920     99.78         0    243440   7483468   6143992         0      0.00
08:10:00 AM     18116   8228728     99.78         0    211104   7463288   6143992         0      0.00
08:20:00 AM     24196   8222648     99.71         0    122844   7497572   6143992         0      0.00
08:30:00 AM     19576   8227268     99.76         0    103964   7512028   6143992         0      0.00
08:40:00 AM     19284   8227560     99.77         0     95216   7503028   6143992         0      0.00
08:50:00 AM     23900   8222944     99.71         0     82900   7475676   6143992         0      0.00
09:00:00 AM     19792   8227052     99.76         0     85444   7564408   6143992         0      0.00
09:10:00 AM     20416   8226428     99.75         0     89540   7554876   6143992         0      0.00
09:20:00 AM     18792   8228052     99.77         0     85996   7547380   6143992         0      0.00
09:30:00 AM     18192   8228652     99.78         0     82276   7535540   6143992         0      0.00
09:40:00 AM     20844   8226000     99.75         0      6712   7600744   6143992         0      0.00
09:50:00 AM     25244   8221600     99.69         0     12392   7597332   6143992         0      0.00
10:00:00 AM     19828   8227016     99.76         0     17836   7593152   6143992         0      0.00
10:10:00 AM     18460   8228384     99.78         0     22264   7581268   6143992         0      0.00
10:20:00 AM     19432   8227412     99.76         0     27408   7564876   6143992         0      0.00
10:30:00 AM     21004   8225840     99.75         0     32144   7546428   6143992         0      0.00
10:40:00 AM     27180   8219664     99.67         0     36924   7525764   6143992         0      0.00
10:50:00 AM     23308   8223536     99.72         0     41744   7540536   6143992         0      0.00
11:00:00 AM     19848   8226996     99.76         0     40200   7527148   6143992         0      0.00
Average:    kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
Average:        23443   8223401     99.72         0    157658   7632032   6143992         0      0.00

So I hope it is utilizing whole 8GB.

SAR will record always 1 week data. If not we need to configure.

For Example to get 3rd of this Month ,exactly 1 week back..(Only ram if u use -r)

[oracle9i@dbserver oracle9i]$ sar -f /var/log/sa/sa03 -r

10:00:00 PM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
10:10:00 PM   5957580   2289264     27.76         0    305448   1546828   6143992         0      0.00
10:20:00 PM   5960008   2286836     27.73         0    305492   1546832   6143992         0      0.00
10:30:00 PM   5959456   2287388     27.74         0    305548   1546836   6143992         0      0.00
10:40:00 PM   5959124   2287720     27.74         0    305604   1546836   6143992         0      0.00
10:50:00 PM   5959976   2286868     27.73         0    305664   1546840   6143992         0      0.00
11:00:00 PM   5959284   2287560     27.74         0    305732   1546840   6143992         0      0.00
11:10:00 PM   5958644   2288200     27.75         0    305792   1546844   6143992         0      0.00
11:20:00 PM   5957224   2289620     27.76         0    305844   1546844   6143992         0      0.00
11:30:00 PM   5957964   2288880     27.75         0    305912   1546848   6143992         0      0.00
11:40:00 PM   5958492   2288352     27.75         0    305948   1546848   6143992         0      0.00
11:50:00 PM   5958352   2288492     27.75         0    305984   1546852   6143992         0      0.00
Average:      5963314   2283530     27.69         0    301578   1546674   6143992         0      0.00


Previously,

11:00:00 AM kbmemfree kbmemused  %memused kbmemshrd kbbuffers  kbcached kbswpfree kbswpused  %swpused
11:10:00 AM     23880   4090356     99.42         0     69548   3563356   6071340     72636      1.18
11:20:00 AM     26304   4087932     99.36         0     69492   3559892   6071340     72636      1.18
Average:        25111   4089125     99.39         0     57561   3567780   6072109     71867      1.17


So I feel 32 bit server addresses 8gb RAM and also it is utilizing the whole space.

Need inputs from experts.. Thanks.