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;The default (database)service name is "DB". In tnsnames.ora file
NAME
---------
DB
db_by_scan =Connect with 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)
)
)
)
$ sqlplus system@db_by_scanI only saw one session.
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
*** 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';that showed 3 sessions!!!
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
Back to my test... with database name = db (using database service = db)
SQL> show user;test!!! Instance on db01 crashed ***don't do it on production***
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
$ ps -aef | grep smon | grep dbBack to "system" session.
oracle 23092 1 0 20:13 ? 00:00:01 ora_smon_db1
$ kill -9 23092
SQL> /OK... lost connection.
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
*** If You are using FAILOVER on RAC... You should create NEW Database Service.***
No comments:
Post a Comment