课程第8次（2016-11-16星期三）

botang

上完1Z0-052第6章（网络 静态注册 动态注册 连接时故障转移 共享服务器 数据库链）
（052共19章，053共21章，063多租户共9章，49-3）

1. select  * from v_$session   s
   
2. where    s.TERMINAL  ='pts/2' ;
   
3.   
   
4.   select  * from v_$transaction t;
   
5.       
   
6.   select  * from v_$lock  l   where l.SID=71;
   
7.   
   
8.   ---------------------------
   
9.   select  * from v_$session   s
   
10.   where s.BLOCKING_SESSION is not null;
    
11.   
    
12.   select  * from v_$session   s
    
13.    where s.SID in (select  BLOCKING_SESSION  from v$session  ) ;

复制代码

网络配置文件：
tnsnames.ora

1. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/grid/network/admin/tnsnames.ora
   
2. # Generated by Oracle configuration tools.
   
3. 
   
4. RCAT =
   
5.   (DESCRIPTION =
   
6.     (ADDRESS_LIST =
   
7.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))
   
8.     )
   
9.     (CONNECT_DATA =
   
10.       (SERVICE_NAME = rcat.example.com)
    
11.     )
    
12.   )
    
13. 
    
14. ORCL =
    
15.   (DESCRIPTION =
    
16.     (ADDRESS_LIST =
    
17.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))
    
18.     )
    
19.     (CONNECT_DATA =
    
20.       (SERVICE_NAME = orcl.example.com)
    
21.     )
    
22.   )
    
23. 
    
24. 
    
25. fordb =
    
26.    (DESCRIPTION =
    
27.     (ADDRESS_LIST =
    
28.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))
    
29.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1522))
    
30.     )
    
31.   )
    
32. 
    
33. 
    
34. foronly =
    
35.    (DESCRIPTION =
    
36.     (ADDRESS_LIST =
    
37.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))
    
38.     )
    
39.   )
    

复制代码

数据库共享服务器配置：

1. [oracle@station90 admin]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 09:45:45 2016
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show parameter dispa
   
10. 
    
11. NAME                                     TYPE         VALUE
    
12. ------------------------------------ ----------- ------------------------------
    
13. dispatchers                             string         (PROTOCOL=TCP) (dispatchers=3)
    
14.                                                   (listener=foronly)
    
15. max_dispatchers                      integer
    
16. SQL> show parameter shar
    
17. 
    
18. NAME                                     TYPE         VALUE
    
19. ------------------------------------ ----------- ------------------------------
    
20. cursor_sharing                             string         EXACT
    
21. hi_shared_memory_address             integer         0
    
22. max_shared_servers                     integer
    
23. shared_memory_address                     integer         0
    
24. shared_pool_reserved_size             big integer 36909875
    
25. shared_pool_size                     big integer 0
    
26. shared_server_sessions                     integer
    
27. shared_servers                             integer         4
    
28. SQL>
    

复制代码

共享服务器与监听器：

1. [oracle@station90 admin]$ lsnrctl services
   
2. 
   
3. LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-NOV-2016 09:46:46
   
4. 
   
5. Copyright (c) 1991, 2009, Oracle.  All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
   
8. Services Summary...
   
9. Service "+ASM" has 1 instance(s).
   
10.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
11.     Handler(s):
    
12.       "DEDICATED" established:253 refused:0 state:ready
    
13.          LOCAL SERVER
    
14. Service "orcl.example.com" has 2 instance(s).
    
15.   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    
16.     Handler(s):
    
17.       "DEDICATED" established:0 refused:0
    
18.          LOCAL SERVER
    
19.   Instance "orcl", status READY, has 4 handler(s) for this service...
    
20.     Handler(s):
    
21.       "DEDICATED" established:12 refused:0 state:ready
    
22.          LOCAL SERVER
    
23.       "D002" established:94 refused:0 current:4 max:1022 state:ready
    
24.          DISPATCHER <machine: station90.example.com, pid: 8871>
    
25.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=27145))
    
26.       "D001" established:88 refused:0 current:4 max:1022 state:ready
    
27.          DISPATCHER <machine: station90.example.com, pid: 8867>
    
28.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=55687))
    
29.       "D000" established:97 refused:0 current:4 max:1022 state:ready
    
30.          DISPATCHER <machine: station90.example.com, pid: 3790>
    
31.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=26260))
    
32. Service "orclXDB.example.com" has 1 instance(s).
    
33.   Instance "orcl", status READY, has 0 handler(s) for this service...
    
34. Service "rcat.example.com" has 2 instance(s).
    
35.   Instance "rcat", status UNKNOWN, has 1 handler(s) for this service...
    
36.     Handler(s):
    
37.       "DEDICATED" established:0 refused:0
    
38.          LOCAL SERVER
    
39.   Instance "rcat", status READY, has 4 handler(s) for this service...
    
40.     Handler(s):
    
41.       "DEDICATED" established:6 refused:0 state:ready
    
42.          LOCAL SERVER
    
43.       "D002" established:0 refused:0 current:0 max:1022 state:ready
    
44.          DISPATCHER <machine: station90.example.com, pid: 9997>
    
45.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=11223))
    
46.       "D001" established:1 refused:0 current:0 max:1022 state:ready
    
47.          DISPATCHER <machine: station90.example.com, pid: 9995>
    
48.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=61816))
    
49.       "D000" established:1 refused:0 current:0 max:1022 state:ready
    
50.          DISPATCHER <machine: station90.example.com, pid: 2864>
    
51.          (ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=50083))
    
52. Service "rcatXDB.example.com" has 1 instance(s).
    
53.   Instance "rcat", status READY, has 0 handler(s) for this service...
    
54. The command completed successfully
    
55. [oracle@station90 admin]$
    

复制代码

第二个监听器：

1. [oracle@station90 admin]$ lsnrctl services l2
   
2. 
   
3. LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-NOV-2016 09:47:22
   
4. 
   
5. Copyright (c) 1991, 2009, Oracle.  All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.90)(PORT=1522)))
   
8. Services Summary...
   
9. Service "+ASM" has 1 instance(s).
   
10.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
11.     Handler(s):
    
12.       "DEDICATED" established:0 refused:0 state:ready
    
13.          LOCAL SERVER
    
14. Service "orcl.example.com" has 2 instance(s).
    
15.   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    
16.     Handler(s):
    
17.       "DEDICATED" established:0 refused:0
    
18.          LOCAL SERVER
    
19.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
20.     Handler(s):
    
21.       "DEDICATED" established:1 refused:0 state:ready
    
22.          LOCAL SERVER
    
23. Service "orclXDB.example.com" has 1 instance(s).
    
24.   Instance "orcl", status READY, has 0 handler(s) for this service...
    
25. Service "rcat.example.com" has 2 instance(s).
    
26.   Instance "rcat", status UNKNOWN, has 1 handler(s) for this service...
    
27.     Handler(s):
    
28.       "DEDICATED" established:0 refused:0
    
29.          LOCAL SERVER
    
30.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
31.     Handler(s):
    
32.       "DEDICATED" established:0 refused:0 state:ready
    
33.          LOCAL SERVER
    
34. Service "rcatXDB.example.com" has 1 instance(s).
    
35.   Instance "rcat", status READY, has 0 handler(s) for this service...
    
36. The command completed successfully
    

复制代码

共享服务器相关查询：

1. select  * from
   
2. v_$dispatcher  d  where  d.PADDR='00000001EC7542E0' ;
   
3. 
   
4. select  * from
   
5. v_$session s where s.USERname='SYSTEM';
   
6. 
   
7. select  * from
   
8.   v$circuit  c;

复制代码