Tuesday, April 24, 2012

Resolved: PRCR-1079 ORA-00119 CRS-2674 CRS-2528 Oracle 11g Release 2 Cannot Start Database

Issue: Cannot start the database with SRVCTL start database -d testdb

srvctl start database -d testdb


PRCR-1079 : Failed to start resource ora.testdb.db
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.testdb.db' on 'linux-rac01' failed
CRS-2528: Unable to place an instance of 'ora.testdb.db' as all possible servers are occupied by the resource already
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.testdb.db' on 'linux-rac02' failed

To troubleshoot further if you try to open the database from SQLPLUS with any of the instances you will receive below error message.

SQL> startup


ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'linux-rac-scan:1521'



There is a remote.listener parameter in spfile/pfile. If you are using SPFILE then try to create a pfile using spfile with below command.

SQL> create pfile='/tmp/ptestdb.ora' from spfile='+Data/testdb/pfiletestdb.ora'

Open the pfile you will find a parameter *.remote.listener which will be something like this:

*.remote_listener='linux-rac-scan:1521'


The error here is because when I checked srvctl config scan I found that scan name is a fully qualified name linux-rac-scan.ingress.com where as here it is initial. So we need to modify this parameter and should change it to:

*.remote_listener='linux-rac-scan.ingress.com:1521'



Once the modification is done try to open the database on any of the instance using that particular pfile.

SQL>startup pfile='/tmp/ptestdb.ora'
Database will be opened without any errors. Once this modification is done try to re-create spfile using this pfile by below command.

SQL> create spfile='+Data/testdb/pfiletestdb.ora' from pfile='/tmp/ptestdb.ora'



Once the Spfile is created shutdown database and try to start the database using srvctl command.

srvctl start database -d testdb -o open

You will see that command will get successfully completed and your database will be up and running.

Hope this article will help you in your issue. If you have any trouble please feel free to drop a comments.

Resolved: PRCR-1079 ORA-00119 CRS-2674 CRS-2528 Oracle 11g Release 2 Cannot Start Database

Issue: Cannot start the database with SRVCTL start database -d testdb

srvctl start database -d testdb


PRCR-1079 : Failed to start resource ora.testdb.db
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.testdb.db' on 'linux-rac01' failed
CRS-2528: Unable to place an instance of 'ora.testdb.db' as all possible servers are occupied by the resource already
ORA-00119: invalid specification for system parameter %s
CRS-2674: Start of 'ora.testdb.db' on 'linux-rac02' failed

To troubleshoot further if you try to open the database from SQLPLUS with any of the instances you will receive below error message.

SQL> startup


ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'linux-rac-scan:1521'



There is a remote.listener parameter in spfile/pfile. If you are using SPFILE then try to create a pfile using spfile with below command.

SQL> create pfile='/tmp/ptestdb.ora' from spfile='+Data/testdb/pfiletestdb.ora'

Open the pfile you will find a parameter *.remote.listener which will be something like this:

*.remote_listener='linux-rac-scan:1521'


The error here is because when I checked srvctl config scan I found that scan name is a fully qualified name linux-rac-scan.bhavik.com where as here it is initial. So we need to modify this parameter and should change it to:

*.remote_listener='linux-rac-scan.bhavik.com:1521'



Once the modification is done try to open the database on any of the instance using that particular pfile.

SQL>startup pfile='/tmp/ptestdb.ora'
Database will be opened without any errors. Once this modification is done try to re-create spfile using this pfile by below command.

SQL> create spfile='+Data/testdb/pfiletestdb.ora' from pfile='/tmp/ptestdb.ora'



Once the Spfile is created shutdown database and try to start the database using srvctl command.

srvctl start database -d testdb -o open

You will see that command will get successfully completed and your database will be up and running.

Hope this article will help you in your issue. If you have any trouble please feel free to drop a comments.

Tuesday, April 17, 2012

Oracle 11g Release 2 Real Application Cluster ERROR ORA-12505 with Java JDBC

Recently I was facing an issue with JAVA when trying to connect my Oracle 11g Release 2 RAC Database that was using SCAN Listener.

Below is the test program that I have used to check the connection between JAVA and Oracle Database.

import java.sql.*;

class ScanTest {
public static void main(String args[]) throws SQLException {
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
);
String serverName = "rac-scan.ingresstest.com";
int port = 1525;
String user = "scott";
String password = "tiger";
String SID = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
Connection conn = DriverManager.getConnection(URL, user, password);
String SQL = "SELECT tname, tabtype FROM tab";
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(SQL);
while (rs.next()) {
System.out.println(
rs.getString(1)
);
}
stat.close();
conn.close();
}
}

 It always ended up with an error given below.
 
Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:


ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
rac-scan.ingresstest.com:1525:ORCL

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:439)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at ScanTest.main(ScanTest.java:15)

Solution to the above problem is because when doing an Installation of Oracle Database 11g Release 2: RAC you might have used Normal Database Configuration which is not recommended by Oracle Corporation.

Oracle recommends to use Custom when you are creating a database with DBCA utility on RAC platform Ver 11g Release 2.

:: you are accessing the server with a database SID, whereas :/ will access a service.

The SCAN Listener only know of services, and not of database SIDs.


So either you can modify your connection URL to

String serverName = "rac-scan.ingresstest.com";

int port = 1525;
String user = "scott";
String password = "tiger";
String Service_Name = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + "/" + Service_Name;
Connection conn = DriverManager.getConnection(URL, user, password);

or you can use it with below method if you can add the custom URL for JDBC and using SCAN Listener.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.ingresstest.com)(PORT=1525))(CONNECT_DATA=(SERVICE_NAME=orcl)))

if you are not using SCAN Listener then create your JDBC URL in below method.

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac01) (PORT=1525))
(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac02) (PORT=1525))
(CONNECT_DATA=(SERVICE_NAME=orcl)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Oracle 11g Release 2 Real Application Cluster ERROR ORA-12505 with Java JDBC

Recently I was facing an issue with JAVA when trying to connect my Oracle 11g Release 2 RAC Database that was using SCAN Listener.

Below is the test program that I have used to check the connection between JAVA and Oracle Database.

import java.sql.*;

class ScanTest {
public static void main(String args[]) throws SQLException {
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
);
String serverName = "rac-scan.bhaviktest.com";
int port = 1525;
String user = "scott";
String password = "tiger";
String SID = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
Connection conn = DriverManager.getConnection(URL, user, password);
String SQL = "SELECT tname, tabtype FROM tab";
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(SQL);
while (rs.next()) {
System.out.println(
rs.getString(1)
);
}
stat.close();
conn.close();
}
}

 It always ended up with an error given below.
 
Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:


ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
rac-scan.bhaviktest.com:1525:ORCL

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:439)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at ScanTest.main(ScanTest.java:15)

Solution to the above problem is because when doing an Installation of Oracle Database 11g Release 2: RAC you might have used Normal Database Configuration which is not recommended by Oracle Corporation.

Oracle recommends to use Custom when you are creating a database with DBCA utility on RAC platform Ver 11g Release 2.

:: you are accessing the server with a database SID, whereas :/ will access a service.

The SCAN Listener only know of services, and not of database SIDs.


So either you can modify your connection URL to

String serverName = "rac-scan.bhaviktest.com";

int port = 1525;
String user = "scott";
String password = "tiger";
String Service_Name = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + "/" + Service_Name;
Connection conn = DriverManager.getConnection(URL, user, password);

or you can use it with below method if you can add the custom URL for JDBC and using SCAN Listener.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.bhaviktest.com)(PORT=1525))(CONNECT_DATA=(SERVICE_NAME=orcl)))

if you are not using SCAN Listener then create your JDBC URL in below method.

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac01) (PORT=1525))
(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac02) (PORT=1525))
(CONNECT_DATA=(SERVICE_NAME=orcl)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Add SCAN LISTENER in 11g Release 2

I have built database server with single SCAN LISTENER for some testing purpose. Now I need to add other 2 SCAN LISTENER so was trying to do it with the SRVCTL Utility so here are the steps on how to add scan listener after doing entire built of database server with single SCAN LISTENER.

[grid@linux-rac01 grid]$ srvctl config scan

SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.ingresstest.com/192.168.50.102
[grid@linux-rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525

[grid@linux-rac01 grid]$ ping rac-scan.ingresstest.com
PING rac-scan.ingresstest.com (192.168.50.103) 56(84) bytes of data.
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=2 Destination Host Unreachable
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=3 Destination Host Unreachable
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=4 Destination Host Unreachable
— rac-scan.ingresstest.com ping statistics —
6 packets transmitted, 0 received, +3 errors, 100% packet loss, time 5000ms, pipe 3


[grid@linux-rac01 grid]$ ping rac-scan
PING rac-scan.ingresstest.com (192.168.50.104) 56(84) bytes of data.
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=2 Destination Host Unreachable
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=3 Destination Host Unreachable
From linux-rac01.ingresstest.com (192.168.20.50) icmp_seq=4 Destination Host Unreachable
 — rac-scan.ingresstest.com ping statistics —
11 packets transmitted, 0 received, +6 errors, 100% packet loss, time 10000ms, pipe 3


[grid@linux-rac01 grid]$ ping rac-scan
PING rac-scan.ingresstest.com (192.168.50.102) 56(84) bytes of data.
64 bytes from rac-scan.ingresstest.com (192.168.50.102): icmp_seq=1 ttl=64 time=0.039 ms
64 bytes from rac-scan.ingresstest.com (192.168.50.102): icmp_seq=2 ttl=64 time=0.054 ms
64 bytes from rac-scan.ingresstest.com (192.168.50.102): icmp_seq=3 ttl=64 time=0.049 ms
64 bytes from rac-scan.ingresstest.com (192.168.50.102): icmp_seq=4 ttl=64 time=0.063 ms


— rac-scan.ingresstest.com ping statistics —
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.039/0.051/0.063/0.010 ms


[grid@linux-rac01 grid]$ host rac-scan
rac-scan.ingresstest.com has address 192.168.50.104
rac-scan.ingresstest.com has address 192.168.50.103
rac-scan.ingresstest.com has address 192.168.50.102


[grid@linux-rac01 grid]$ nslookup rac-scan
Server: 192.168.21.3
Address: 192.168.21.3#53
Name: rac-scan.ingresstest.com
Address: 192.168.50.104
Name: rac-scan.ingresstest.com
Address: 192.168.50.102
Name: rac-scan.ingresstest.com
Address: 192.168.50.103


[grid@linux-rac01 grid]$
[root@linux-rac01 grid]# host rac-scan
rac-scan.ingresstest.com has address 192.168.50.104
rac-scan.ingresstest.com has address 192.168.50.103
rac-scan.ingresstest.com has address 192.168.50.102



 
Now if you see rac-scan is configured in DNS Server and helps in resolving 3 IP addresses given below with rac-scan.ingresstest.com.
 

192.168.50.104
192.168.50.103
192.168.50.102


Below is the snapshot of the tnsnames.ora file :



ORCL =
DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.ingresstest.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.ingresstest.com)
)
)


Above log represents that we have currently 1 scan listener and helps users request to connect database but only if the scan name is resolved to the working IP address.
 
rac-scan resolves either to 192.168.50.103 or 192.168.50.104

SQL> conn scott/tiger@orcl
Connection failed.
ERROR: ORA-12543: TNS:destination host unreachable


Connection established.rac-scan resolves to 192.168.50.102.

SQL>conn scott/tiger@orcl
connected
SQL>




So we are now going to add another 2 scan lister which will omit the above problem when it resolves with ip 103 and 104.

[grid@linux-rac01 grid]$ srvctl stop scan_listener

[grid@linux-rac01 grid]$ srvctl stop scan
[grid@linux-rac01 grid]$ srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.ingresstest.com/192.168.50.102
[grid@linux-rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525
[grid@linux-rac01 grid]$ which srvctl
/u01/home/11.2.0/grid/bin/srvctl

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl modify scan -n rac-scan

NOTE: If the above step doesn't and resolves all the IP address try using full name. Make sure that if you are using full qualified name then the same needs to be modified in TNSNAMES.ORA FILE as well on APPLICATION SERVER.


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.ingresstest.com/192.168.50.103
SCAN VIP name: scan2, IP: /rac-scan.ingresstest.com/192.168.50.104
SCAN VIP name: scan3, IP: /rac-scan.ingresstest.com/192.168.50.102


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl modify scan_listener -u


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1525
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1525

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl start scan_listener

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.ingresstest.com/192.168.50.103
SCAN VIP name: scan2, IP: /rac-scan.ingresstest.com/192.168.50.104
SCAN VIP name: scan3, IP: /rac-scan.ingresstest.com/192.168.50.102

Once it is done you can check the status with SRVCTL command.


Check the Status of SCAN and SCAN_LISTENER:
 
[grid@linux-rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node linux-rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node linux-rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node linux-rac02


[grid@linux-rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node linux-rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node linux-rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node linux-rac02


Once the SCAN LISTENER is configured you can use tsnping and see if there is any database listening to that port.
 
Please let us know your feedback and comments.

Add SCAN LISTENER in 11g Release 2

I have built database server with single SCAN LISTENER for some testing purpose. Now I need to add other 2 SCAN LISTENER so was trying to do it with the SRVCTL Utility so here are the steps on how to add scan listener after doing entire built of database server with single SCAN LISTENER.

[grid@linux-rac01 grid]$ srvctl config scan

SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.bhaviktest.com/192.168.50.102
[grid@linux-rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525

[grid@linux-rac01 grid]$ ping rac-scan.bhaviktest.com
PING rac-scan.bhaviktest.com (192.168.50.103) 56(84) bytes of data.
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=2 Destination Host Unreachable
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=3 Destination Host Unreachable
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=4 Destination Host Unreachable
— rac-scan.bhaviktest.com ping statistics —
6 packets transmitted, 0 received, +3 errors, 100% packet loss, time 5000ms, pipe 3


[grid@linux-rac01 grid]$ ping rac-scan
PING rac-scan.bhaviktest.com (192.168.50.104) 56(84) bytes of data.
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=2 Destination Host Unreachable
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=3 Destination Host Unreachable
From linux-rac01.bhaviktest.com (192.168.20.50) icmp_seq=4 Destination Host Unreachable
 — rac-scan.bhaviktest.com ping statistics —
11 packets transmitted, 0 received, +6 errors, 100% packet loss, time 10000ms, pipe 3


[grid@linux-rac01 grid]$ ping rac-scan
PING rac-scan.bhaviktest.com (192.168.50.102) 56(84) bytes of data.
64 bytes from rac-scan.bhaviktest.com (192.168.50.102): icmp_seq=1 ttl=64 time=0.039 ms
64 bytes from rac-scan.bhaviktest.com (192.168.50.102): icmp_seq=2 ttl=64 time=0.054 ms
64 bytes from rac-scan.bhaviktest.com (192.168.50.102): icmp_seq=3 ttl=64 time=0.049 ms
64 bytes from rac-scan.bhaviktest.com (192.168.50.102): icmp_seq=4 ttl=64 time=0.063 ms


— rac-scan.bhaviktest.com ping statistics —
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.039/0.051/0.063/0.010 ms


[grid@linux-rac01 grid]$ host rac-scan
rac-scan.bhaviktest.com has address 192.168.50.104
rac-scan.bhaviktest.com has address 192.168.50.103
rac-scan.bhaviktest.com has address 192.168.50.102


[grid@linux-rac01 grid]$ nslookup rac-scan
Server: 192.168.21.3
Address: 192.168.21.3#53
Name: rac-scan.bhaviktest.com
Address: 192.168.50.104
Name: rac-scan.bhaviktest.com
Address: 192.168.50.102
Name: rac-scan.bhaviktest.com
Address: 192.168.50.103


[grid@linux-rac01 grid]$
[root@linux-rac01 grid]# host rac-scan
rac-scan.bhaviktest.com has address 192.168.50.104
rac-scan.bhaviktest.com has address 192.168.50.103
rac-scan.bhaviktest.com has address 192.168.50.102



 
Now if you see rac-scan is configured in DNS Server and helps in resolving 3 IP addresses given below with rac-scan.bhaviktest.com.
 

192.168.50.104
192.168.50.103
192.168.50.102


Below is the snapshot of the tnsnames.ora file :



ORCL =
DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.bhaviktest.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.bhaviktest.com)
)
)


Above log represents that we have currently 1 scan listener and helps users request to connect database but only if the scan name is resolved to the working IP address.
 
rac-scan resolves either to 192.168.50.103 or 192.168.50.104

SQL> conn scott/tiger@orcl
Connection failed.
ERROR: ORA-12543: TNS:destination host unreachable


Connection established.rac-scan resolves to 192.168.50.102.

SQL>conn scott/tiger@orcl
connected
SQL>




So we are now going to add another 2 scan lister which will omit the above problem when it resolves with ip 103 and 104.

[grid@linux-rac01 grid]$ srvctl stop scan_listener

[grid@linux-rac01 grid]$ srvctl stop scan
[grid@linux-rac01 grid]$ srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.bhaviktest.com/192.168.50.102
[grid@linux-rac01 grid]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525
[grid@linux-rac01 grid]$ which srvctl
/u01/home/11.2.0/grid/bin/srvctl

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl modify scan -n rac-scan

NOTE: If the above step doesn't and resolves all the IP address try using full name. Make sure that if you are using full qualified name then the same needs to be modified in TNSNAMES.ORA FILE as well on APPLICATION SERVER.


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.bhaviktest.com/192.168.50.103
SCAN VIP name: scan2, IP: /rac-scan.bhaviktest.com/192.168.50.104
SCAN VIP name: scan3, IP: /rac-scan.bhaviktest.com/192.168.50.102


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl modify scan_listener -u


[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1525
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1525
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1525

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl start scan_listener

[root@linux-rac01 root]# /u01/home/11.2.0/grid/bin/srvctl config scan
SCAN name: rac-scan, Network: 1/192.168.20.0/255.255.255.0/eth2
SCAN VIP name: scan1, IP: /rac-scan.bhaviktest.com/192.168.50.103
SCAN VIP name: scan2, IP: /rac-scan.bhaviktest.com/192.168.50.104
SCAN VIP name: scan3, IP: /rac-scan.bhaviktest.com/192.168.50.102

Once it is done you can check the status with SRVCTL command.


Check the Status of SCAN and SCAN_LISTENER:
 
[grid@linux-rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node linux-rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node linux-rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node linux-rac02


[grid@linux-rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node linux-rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node linux-rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node linux-rac02


Once the SCAN LISTENER is configured you can use tsnping and see if there is any database listening to that port.
 
Please let us know your feedback and comments.

Thursday, April 5, 2012

Resolving Error ORA-01115, ORA-01110 and ORA-15081 connecting Database

If you are getting error ORA-01115, ORA-01110 and ORA-15081 after you connected to a database or while connecting to the database accessing header of SQL as mentioned below.

SQL*Plus: Release 11.2.0.2.0 Production on Mon April 5 17:01:10 2012Copyright (c) 1982, 2010, Oracle. All rights reserved.Connected to:



Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production


With the Partitioning, Real Application Clusters and Automatic Storage Management

SQL> select * from v$instance;


select * from v$instance
*


ERROR at line 1:


ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA/test/datafile/system.dbf'ORA-15081: failed to submit an I/O operation to a disk

The real cause of this above error is nothing else but just because you might be trying to connect with the user which doesn't belong to dba, oper, asmadmin, etc operating system group.

To correct this error just you can assign the o/s user to an appropriate group and it will get resolved.

Hope this will help you and please give your feedback/comments about our blog which helps us in improving it.

Tuesday, April 3, 2012

Oracle 11g Release 2: SRVCTL commands PART 7 [SCAN_LISTENER]

SRVCTL command for SCAN Listner:

To add SCAN Listener
  • srvctl add scan_listener [-l lsnr_name_prefix] [-s] [-p "[TCP:]port_list[/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]"]
  • srvctl add scan_listener -l scanlistener01
To Remove Scan Listener
  • srvctl remove scan_listener [-f]
  • srvctl remove scan_listener -f
Note: Below command is using ordinal_number parameter so An ordinal number is that identifies which SCAN VIP you want to start. The range of values you can specify for this option is 1 to 3.

To Start Scan Listener
  • srvctl start scan_listener [-n node_name] [-i ordinal_number]
  • srvctl start scan_listener -n linuxrac01 -i 1
To Stop Scan Listener
  • srvctl stop scan_listener [-i ordinal_number] [-f]
  • srvctl stop scan_listener -i 3
To check the status of Scan Listner
  • srvctl status scan_listener [-i ordinal_number]
  • srvctl status scan_listener -i 1
To Enable Scan Listener
  • srvctl enable scan_listener [-i ordinal_number]
  • srvctl enable scan_listener -i 2
To Disable Scan Listener
  • srvctl disable scan_listener [-i ordinal_number]
  • srvctl disable scan_listener -i 1
To Configure Scan Listener
  • srvctl config scan_listener [-i ordinal_number]
  • srvctl config scan_listener -i 3
To Modify Scan Listener
  • srvctl modify scan_listener {-p [TCP:]port[/IPC:key][/NMP:pipe_name] [/TCPS:s_port][/SDP:port] -u }
  • srvctl modify scan_listener -u -p TCP:1521
To relocate Scan Listener
  • srvctl relocate scan_listener -i ordinal_number [-n node_name]
  • srvctl relocate scan_listener -i 1 -n linuxrac02


Please feel free to leave your comments.

Oracle 11g Release 2: SRVCTL commands PART 6 [SCAN]

SRVCTL command for SCAN(Single Client Access Name):

To Add a SCAN
  • srvctl add scan -n nodename
  • srvctl add scan -n linuxrac01
To Remove a SCAN
  • srvctl remove scan [-f]
Note: Below command is using ordinal_number parameter so An ordinal number is that identifies which SCAN VIP you want to start. The range of values you can specify for this option is 1 to 3.

To Start a SCAN
  • srvctl start scan [-i ordinal_number] [-n node_name]
  • srvctl start scan -i 1 -n linuxrac01
To Stop a SCAN
  • srvctl stop scan [-i ordinal_number] [-f]
  • srvctl stop scan -i 1
To Check status of SCAN
  • srvctl status scan [-i ordinal_number]
  • srvctl status scan -i 1
To Enable a SCAN
  • srvctl enable scan [-i ordinal_number]
  • srvctl enable scan -i 1
To Disable a SCAN
  • srvctl disable scan [-i ordinal_number]
  • srvctl disable scan -i 3
To Configure a SCAN
  • srvctl config scan [-i ordinal_number]
  • srvctl config scan -i 2
To Modify SCAN
  • srvctl modify scan -n scan_name
  • srvctl modify scan -n SCAN1
To Relocate a SCAN
  • srvctl relocate scan -i ordinal_number [-n node_name]
  • srvctl relocate scan -i 2 -n linuxrac02
Keep watching.. Still other parts of SRVCTL commands are yet to come

Monday, April 2, 2012

Oracle 11g EM DBconsole: WARNING: Database instance unavailable

Hello,


If you are receiving above error when accessing the DBConsole it is possible that there is some problem with EM repository.


It is always better to drop the em repository and deconfig all your database running in cluster or non-cluster mode.


Below are the steps that you can use.


  • emca -deconfig all db


Note: Above command will be used for all the ORACLE_SID running on the node and use below command for the last one.


  • emca -deconfig all db -repos drop
Now before creating a repository make sure that you have set below parameters:
  1. ORACLE_SID
  2. ORACLE_UNQNAME
  3. ORACLE_HOSTNAME
  4. ORACLE_HOME
  5. GRID_HOME
  • emca -config dbcontrol db -repos create -cluster -EM_NODE_LIST dbnode1,dbnode2
Now, without using the -repos create execute the command mentioned below to add the rest of the databases.
  • emca -config dbcontrol db -cluster -EM_NODE_LIST dbnode1,dbnode2
Please feel free to contact me if you are facing me an issue.

Oracle 11g Enterprise Manager Switching Accessibility Mode Disabled to Enabled

If you receive a message accessing Oracle Enterprise Manager : 11g saying Accessibility Mode Disabled


Follow the below steps

1. vi $ORACLE_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/uix-config.xml

2. Change the value of the accessibility-mode property from inaccessible to accessible.

4. Save and close the file.

5. Restart the Oracle Management Service or restart the Database Control (10g only)


Hope this will help you out and please feel free to leave your comments.

Cheers!!
ingress

Oracle 11g Enterprise Manager Switching Accessibility Mode Disabled to Enabled

If you receive a message accessing Oracle Enterprise Manager : 11g saying Accessibility Mode Disabled


Follow the below steps

1. vi $ORACLE_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/uix-config.xml

2. Change the value of the accessibility-mode property from inaccessible to accessible.

4. Save and close the file.

5. Restart the Oracle Management Service or restart the Database Control (10g only)


Hope this will help you out and please feel free to leave your comments.

Cheers!!
Bhavik