Sunday, July 10, 2011

Just test connection with FAILOVER with default service name

I posted "Just test connection with FAILOVER on RAC". I remember some question. When I created database, my database name = "db". The default (database) service name used by oracle database is DB .
Can I use this service name with FAILOVER? NO, It's not WORK!!! I have to create New Database Service and use it.
but Many DBAs believes, it can use... Let me test ***Assume database name = db***
SQL> select name from v$database;

NAME
---------
DB
The default (database)service name is "DB". In tnsnames.ora file
db_by_scan =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-scan)(PORT = 1521))
(FAILOVER=on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 10)
(DELAY = 1)
)
)
)
Connect with db_by_scan
$ sqlplus system@db_by_scan
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 10 22:05:54 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

SQL> show user;
USER is "SYSTEM"

SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------ --------------- --------------- -------------
1 SYSTEM sqlplus@client (TNS V1-V3) client db SELECT
I only saw one session.
*** If I did not use default (database service name = database name = db), I mean If I use service name = service1 (new database service)***
SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
1 SYSTEM oracle@db01 (PZ99) client service1 NONE
1 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT
2 SYSTEM oracle@db02 (PZ99) client service1 NONE
that showed 3 sessions!!!
Back to my test... with database name = db (using database service = db)
SQL> show user;
USER is "SYSTEM"

SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------ --------------- --------------- -------------
1 SYSTEM sqlplus@client (TNS V1-V3) client db SELECT
test!!! Instance on db01 crashed ***don't do it on production***
$ ps -aef | grep smon | grep db
oracle 23092 1 0 20:13 ? 00:00:01 ora_smon_db1

$ kill -9 23092
Back to "system" session.
SQL> /
SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28552
Session ID: 196 Serial number: 9
OK... lost connection.
*** If You are using FAILOVER on RAC... You should create NEW Database Service.***

No comments: