Oracle8 Core Tecnology Seminar ~ E: Oracle8 ~
1. 2. 3.
Node1 Node2 Users Oracle8 Server Users tnsnames.ora
PL/SQL V8 OCI(Oracle Call Interface) KILL Shadow Process
SELECT SESSION NONE BASIC PRECONNECT SELECT SESSION SELECT NONE BASIC PRECONNECT
Client Instance1 Instance2 1 2TakeOver PRECONNECT
Users LISTENER tnsnames.ora SMON 2 Failover Architecture 2(SMON ORA-3113ORA-3114
DBA SVRMGR>SHUTDOWN TRANSACTIONAL DBA SVRMGR>ALTER SYSTEM DISCONNECT SESSION sid,serial# POST_TRANSACTION; SHUTDOWN IMMEDIATE
Why? ALTER SESSION 1 2
(cont d) sample sb4 callback_fn(svchp,envhp,fo_ctx,fo_type,fo_event) dvoid *svchp; dvoid *envhp; dvoid *fo_ctx; ub4 fo_type; ub4 fo_event; { switch (fo_event) { case OCI_FO_BEGIN: { printf( Failing Over... Please stand by n ); break; } case OCI_FO_END: { printf( Failover ended... ); } fo_event OCI_FO_BEGIN OCI_FO_END OCI_FO_ABORT OCI_FO_REAUTH
(cont d) sample int register_callback(srvhp,errhp) dvoid *srvhp; dvoid *errhp; { OCIFocbkStruct failover; failover.callback_function = &callback_fn; if (OCIAttrSet(srvhp,(ub4) OCI_HTYPE_SRV, (dvoid*) &failover,(ub4) 0, (ub4) OCI_ATTR_FOCBK,errhp)!=OCI_SUCCESS) return(2); return(0); }
Oracle8 Server EE R8.0.3.0.0 Node1 Node2 Users (OCI AP) Oracle8 Server Node1: Host testspi SID tog8036 Node2: Host testspi7 SID tog8037 20,000 SELECT 5
dbname = tog803 : local_listener = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) event = 10258 trace name context forever, level 4 thread = 4 instance_number = 4 LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) SID 1. LOCAL_LISTENER 2. LISTENER.ORA SID event
Method=BASIC TNSNAMES.ORA conn_basic = (DESCRIPTION_LIST = (DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) (CONNECT_DATA = (SID=tog8036)(SERVER=DEDICATED) (FAILOVER_MODE=(TYPE=select)(METHOD=basic)))) (DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi7)(PORT=1521)) (CONNECT_DATA = (SID=tog8037)(SERVER=DEDICATED) (FAILOVER_MODE=(TYPE=select)(METHOD=basic)))) )
Method=BASIC (cont d) NODE1 NODE2 GV$SESSION User INST_ID SERIAL# USERNAME FO_TYPE FO_METHOD FO -------- -------- ------------ ------------ ------------ --- 4 10 O8NFEV SELECT BASIC NO INST_ID SERIAL# USERNAME FO_TYPE FO_METHOD FO -------- -------- ------------ ------------ ------------ --- 5 37 O8NFEV SELECT BASIC YES NODE1 NODE1 NODE2 ---------------------------------- --------------------------------------------------------------------- (*)INST_ID SERIAL# USERNAME Oracle (*)FAILOVER_TYPE (*)FAILOVER_METHOD (*)FAILED_OVER YES NO
Method=PRECONNECT TNSNAMES.ORA conn_preconn = (DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) (CONNECT_DATA = (SID=tog8036)(SERVER=DEDICATED) (FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(BACKUP=conn_bk)))) conn_bk = (DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi7)(PORT=1521)) (CONNECT_DATA = (SID=tog8037)(SERVER=DEDICATED)))
Method=PRECONNECT (cont d) NODE1 NODE2 Pre-Established GV$SESSION User INST_ID SERIAL# USERNAME FO_TYPE FO_METHOD FO -------- -------- ------------ ------------ ------------ --- 4 4 O8NFEV SELECT PRECONNECT NO Primary 5 51 O8NFEV SELECT NONE NO Backup INST_ID SERIAL# USERNAME FO_TYPE FO_METHOD FO -------- -------- ------------ ------------ ------------ --- 5 51 O8NFEV SELECT PRECONNECT YES NODE1 NODE2 NODE1 TakeOver)
CPU Type=SELECT,Method=BASIC CPU
dbname = tog803 : mts_dispatchers = tcp,1 mts_max_dispatchers = 5 mts_servers = 1 mts_max_servers = 10 mts_service=fail_over mts_multiple_listeners = true mts_listener_address = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) mts_listener_address = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=testspi7)(PORT=1521)) thread = 4 instance_number = 4 MTS MTS_SERVICE SID DB_NAME
(cont d) LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME=/home/tog803/app/oracle/product/8.0.3) (SID_NAME = tog8036) (SID_DESC = (ORACLE_HOME=/home/tog803/app/oracle/product/8.0.3) (SID_NAME = fail_over) )
Method=BASIC TNSNAMES.ORA conn_basic conn_basic = (DESCRIPTION = (ADDRESS_LIST = (( ADDRESS ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521)) (( ADDRESS ADDRESS = (PROTOCOL=tcp)(HOST=testspi7)(PORT=1521))) (CONNECT_DATA = (SID=fail_over)(SERVER=SHARED) (FAILOVER_MODE=(TYPE=select)(METHOD=basic))) ))
Method=PRECONNECT TNSNAMES.ORA conn_preconn = (DESCRIPTION = (ADDRESS_LIST = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi6)(PORT=1521))) (CONNECT_DATA = (SID=fail_over)(SERVER=SHARED) (FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(BACKUP=conn_bk)))) conn_bk = (DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp)(HOST=testspi7)(PORT=1521)) (CONNECT_DATA = (SID=tog8037)(SERVER=SHARED)))
Shutdown Transactional DBA SVRMGR>SHUTDOWN TRANSACTIONAL NODE1 NODE2 Update User1 Commit Shutdown User1 2 User1 Tips
SVRMGR>ALTER SYSTEM DISCONNECT SESSION sid,serial# POST_TRANSACTION; NODE1 NODE2 2 Alter System.. Post_transaction Oracle8 Server Tips: SID,SERIAL# OS SID,SERIAL# SQL> SELECT SID,SERIAL#,USERNAME FROM V$SESSION 2>WHERE PROCESS= 22216 ; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 12 105 SYSTEM
Type=SESSION NODE1 NODE2 User NODE1 NODE1 NODE2
NODE1 NODE2 NODE3 Coord Query Slave0 Slave1 Slave3 ORA-12805 PQ Oracle8 Server PQORA-12805
NODE1 NODE2 Select User1 Select 3600 3690 Update 3600 Table User1 User2 Select User1 1 1 2 3600 3690 1 1 2 3600
PL/SQL NODE1 NODE2 PL/SQL Block ORA- Oracle8 Server ORA-25402 PL/SQL ORA-25408
SELECT empno, ename From emp; Client SELECT empno, ename From emp; empno name empno name 7369 smith 7369 smith 7499 allen 7521 ward Instance1 Instance2 7499 allen 7521 ward 7566 jones 7566 jones 7654 martin 7654 martin 7698 scott 7698 scott 7782 clark 7782 clark
(cont d) NODE1 NODE2 NODE1 NODE2 5 rows. 19,990 rows. 0 31 Oracle8 Server Oracle8 Server
Method=BASIC
V8 OCI DBA Callback