课程第42/43/44次（2018-05-13星期日上下午，2018-05-14星期一）

botang

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  lsinventory  
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-30-35上午.log
    
14. 
    
15. Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-30-35上午.txt
    
16. 
    
17. --------------------------------------------------------------------------------
    
18. 已安装的顶级产品 (1):
    
19. 
    
20. Oracle Database 11g                                                  11.2.0.3.0
    
21. 此 Oracle 主目录中已安装 1 个产品。
    
22. 
    
23. 
    
24. 此 Oracle 主目录中未安装任何中间补丁程序。
    
25. 
    
26. 
    
27. --------------------------------------------------------------------------------
    
28. 
    
29. OPatch succeeded.
    

复制代码

如果是热补丁：

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  query -is_online_patch
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-32-39上午.log
    
14. 
    
15. --------------------------------------------------------------------------------
    
16. Patch is an online patch: true
    
17. 
    
18. 
    
19. OPatch succeeded.
    

复制代码

如果是冷补丁：

1. [oracle@station90 13906496]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  query -is_online_patch
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-34-29上午.log
    
14. 
    
15. --------------------------------------------------------------------------------
    
16. Patch is an online patch: false
    
17. 
    
18. 
    
19. OPatch succeeded.
    

复制代码

热补丁的选项：
-connectString
              This option can be used to specify the list of database
              instances on which the patch needs to be applied. The
              value for this option is specified as per the following
              syntax "SID:Userasswd:Node". The SID is a must, others
              can be ignored, OPatch takes default values for it.
              Ex: oracle:dba:dba:mymachine,oracle1:::
              NOTE: If the system is not part of RAC setup, then to
              patch just the local node, provide the node name as
              empty string.

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  apply  -connectString  orcl:::   -connectString  rcat:::
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-40-37上午.log
    
14. 
    
15. 
    
16. 补丁程序只应在 '-all_nodes' 模式下应用/回退。
    
17. 将 RAC 模式转换为 '-all_nodes' 模式。
    
18. Applying interim patch '13906496' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
    
19. Verifying environment and performing prerequisite checks...
    
20. 
    
21. 是否继续? [y|n]
    
22. y
    
23. User Responded with: Y
    
24. All checks passed.
    
25. Backing up files...
    
26. 
    
27. 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
    
28. The patch will be installed on active database instances.
    
29. 正在数据库 'rcat' 上安装和启用联机补丁程序 'bug13906496.pch'。
    
30. 
    
31. Patch 13906496 successfully applied
    
32. Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-40-37上午.log
    
33. 
    
34. OPatch succeeded.
    

复制代码

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  lsinventory
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-41-46上午.log
    
14. 
    
15. Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-41-46上午.txt
    
16. 
    
17. --------------------------------------------------------------------------------
    
18. 已安装的顶级产品 (1):
    
19. 
    
20. Oracle Database 11g                                                  11.2.0.3.0
    
21. 此 Oracle 主目录中已安装 1 个产品。
    
22. 
    
23. 
    
24. 中间补丁程序 (1) :
    
25. 
    
26. Patch (online) 13906496: applied on Sun May 13 09:40:48 CST 2018
    
27. Unique Patch ID:  17324986
    
28.    Created on 22 Jul 2014, 12:09:07 hrs PST8PDT
    
29.    Bugs fixed:
    
30.      13906496
    
31. 
    
32. 
    
33. 
    
34. --------------------------------------------------------------------------------
    
35. 
    
36. OPatch succeeded.
    

复制代码

回退热补丁：

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  rollback -id  13906496  -connectString orcl:::  -connectString  rcat:::
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-02上午.log
    
14. 
    
15. RollbackSession 从 OH '/u01/app/oracle/product/11.2.0/dbhome_1' 回退中间补丁程序 '13906496'
    
16. 
    
17. 补丁程序只应在 '-all_nodes' 模式下应用/回退。
    
18. 将 RAC 模式转换为 '-all_nodes' 模式。
    
19. 
    
20. 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
    
21. The patch will be removed from active database instances.
    
22. 正在数据库 'rcat' 上禁用和删除联机补丁程序 'bug13906496.pch'
    
23. 
    
24. RollbackSession 从产品清单中删除中间补丁程序 '13906496'
    
25. Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-02上午.log
    
26. 
    
27. OPatch succeeded.
    

复制代码

1. [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch  lsinventory
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-57上午.log
    
14. 
    
15. Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-44-57上午.txt
    
16. 
    
17. --------------------------------------------------------------------------------
    
18. 已安装的顶级产品 (1):
    
19. 
    
20. Oracle Database 11g                                                  11.2.0.3.0
    
21. 此 Oracle 主目录中已安装 1 个产品。
    
22. 
    
23. 
    
24. 此 Oracle 主目录中未安装任何中间补丁程序。
    
25. 
    
26. 
    
27. --------------------------------------------------------------------------------
    
28. 
    
29. OPatch succeeded.
    

复制代码

打冷补丁：

1. [oracle@station90 13906496]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply
   
2. Invoking OPatch 11.2.0.1.7
   
3. 
   
4. Oracle 中间补丁程序安装程序版本 11.2.0.1.7
   
5. 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
   
6. 
   
7. 
   
8. Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
   
9. Central Inventory : /u01/app/oraInventory
   
10.    from           : /etc/oraInst.loc
    
11. OPatch version    : 11.2.0.1.7
    
12. OUI version       : 11.2.0.3.0
    
13. Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-47-09上午.log
    
14. 
    
15. Applying interim patch '13906496' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
    
16. Verifying environment and performing prerequisite checks...
    
17. 
    
18. 是否继续? [y|n]
    
19. y
    
20. User Responded with: Y
    
21. All checks passed.
    
22. 
    
23. 请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
    
24. (Oracle 主目录 = '/u01/app/oracle/product/11.2.0/dbhome_1')
    
25. 
    
26. 
    
27. 本地系统是否已准备打补丁? [y|n]
    
28. y
    
29. User Responded with: Y
    
30. Backing up files...
    
31. 
    
32. 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
    
33. Patch 13906496 successfully applied
    
34. Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-47-09上午.log
    
35. 
    
36. OPatch succeeded.
    

复制代码

从11.2.0.3grid升到12.1.0.2grid的日志：



精确控制通道和输入（一般是输入文件多于通道数考虑）：

1. RMAN> run {
   
2. 2> allocate channel c1 device type sbt ;
   
3. 3> allocate channel c2 device type sbt ;
   
4. 4> backup ( datafile 1,5 channel c1) ( datafile 2,3,4 channel c2 ) ;
   
5. 5> release channel c1;
   
6. 6> release channel c2;
   
7. 7> }
   
8. 
   
9. using target database control file instead of recovery catalog
   
10. allocated channel: c1
    
11. channel c1: SID=247 device type=SBT_TAPE
    
12. channel c1: Oracle Secure Backup
    
13. 
    
14. allocated channel: c2
    
15. channel c2: SID=9 device type=SBT_TAPE
    
16. channel c2: Oracle Secure Backup
    
17. 
    
18. Starting backup at 13-MAY-18
    
19. channel c1: starting full datafile backup set
    
20. channel c1: specifying datafile(s) in backup set
    
21. input datafile file number=00001 name=+DATADG/orcl/datafile/system.256.973363899
    
22. input datafile file number=00005 name=+DATADG/orcl/datafile/example.266.973363999
    
23. channel c1: starting piece 1 at 13-MAY-18
    
24. channel c2: starting full datafile backup set
    
25. channel c2: specifying datafile(s) in backup set
    
26. input datafile file number=00002 name=+DATADG/orcl/datafile/sysaux.257.973363901
    
27. input datafile file number=00003 name=+DATADG/orcl/datafile/undotbs1.258.973363901
    
28. input datafile file number=00004 name=+DATADG/orcl/datafile/users.259.973363901
    
29. channel c2: starting piece 1 at 13-MAY-18
    
30. channel c2: finished piece 1 at 13-MAY-18
    
31. piece handle=14t2pkp1_1_1 tag=TAG20180513T113600 comment=API Version 2.0,MMS Version 10.4.0.4
    
32. channel c2: backup set complete, elapsed time: 00:01:05
    
33. channel c1: finished piece 1 at 13-MAY-18
    
34. piece handle=13t2pkp1_1_1 tag=TAG20180513T113600 comment=API Version 2.0,MMS Version 10.4.0.4
    
35. channel c1: backup set complete, elapsed time: 00:01:15
    
36. Finished backup at 13-MAY-18
    
37. 
    
38. Starting Control File and SPFILE Autobackup at 13-MAY-18
    

复制代码

rman 会话定位：

1. SELECT s.sid, p.spid, s.client_info ,s.terminal
   
2.    FROM v$process p, v$session s
   
3.     WHERE p.addr = s.paddr
   
4.    AND  s.terminal in ( SELECT s.terminal
   
5.    FROM v$process p, v$session s
   
6.     WHERE p.addr = s.paddr
   
7.    AND CLIENT_INFO LIKE '%botang%');
   
8. 
   
9. 
   
10. SELECT  s.sid, OPNAME, CONTEXT, SOFAR, TOTALWORK,
    
11.     ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
    
12.     FROM V_$SESSION_LONGOPS s
    
13.     WHERE OPNAME LIKE 'RMAN%'
    
14.     AND OPNAME NOT LIKE '%aggregate%'
    
15.    AND TOTALWORK != 0
    
16.    AND SOFAR <> TOTALWORK
    
17.    and s.sid in ( SELECT s.sid
    
18.    FROM v$process p, v$session s
    
19.     WHERE p.addr = s.paddr
    
20.    AND  s.terminal in ( SELECT s.terminal
    
21.    FROM v$process p, v$session s
    
22.     WHERE p.addr = s.paddr
    
23.    AND CLIENT_INFO LIKE '%botang%'));
    
24. 
    
25. 
    

复制代码

-----------------------------------
同步和异步IO（磁带机）

1. 
   
2.    
   
3.    
   
4. select  a.IO_COUNT ,a.LONG_WAITS  ,a.SHORT_WAIT_TIME_TOTAL
   
5. ,a.LONG_WAIT_TIME_TOTAL  from v_$backup_async_io  a
   
6.   where  a.sid  in  ( SELECT s.sid
   
7.    FROM v$session s
   
8.     WHERE   s.terminal in ( SELECT s.terminal
   
9.    FROM v$session s
   
10.     WHERE  CLIENT_INFO LIKE '%botang%'));
    
11.       
    
12. select  * from v_$backup_sync_io  a
    
13. where  a.sid  in  ( SELECT s.sid
    
14.    FROM v$session s
    
15.     WHERE   s.terminal in ( SELECT s.terminal
    
16.    FROM v$session s
    
17.     WHERE  CLIENT_INFO LIKE '%botang%'));

复制代码

maxopenfiles和filesperset语法：

1. RMAN> run {
   
2. 2> allocate channel c1 device type sbt maxopenfiles 1;
   
3. 3> allocate channel c2 device type sbt maxopenfiles 1;
   
4. 4> backup database filesperset 1;
   
5. 5> }
   
6. 
   
7. allocated channel: c1
   
8. channel c1: SID=711 device type=SBT_TAPE
   
9. channel c1: Oracle Secure Backup
   
10. 
    
11. allocated channel: c2
    
12. channel c2: SID=483 device type=SBT_TAPE
    
13. channel c2: Oracle Secure Backup
    
14. 
    
15. Starting backup at 14-MAY-18
    
16. channel c1: starting full datafile backup set
    
17. channel c1: specifying datafile(s) in backup set
    
18. input datafile file number=00001 name=+DATA/ORCL2/DATAFILE/system.266.976031403
    
19. channel c1: starting piece 1 at 14-MAY-18
    
20. channel c2: starting full datafile backup set
    
21. channel c2: specifying datafile(s) in backup set
    
22. input datafile file number=00002 name=+DATA/ORCL2/DATAFILE/sysaux.269.976031405
    
23. channel c2: starting piece 1 at 14-MAY-18
    
24. channel c1: finished piece 1 at 14-MAY-18
    
25. piece handle=10t2t69h_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
    
26. channel c1: backup set complete, elapsed time: 00:00:26
    
27. channel c1: starting full datafile backup set
    
28. channel c1: specifying datafile(s) in backup set
    
29. input datafile file number=00003 name=+DATA/ORCL2/DATAFILE/undotbs1.270.976031403
    
30. channel c1: starting piece 1 at 14-MAY-18
    
31. channel c2: finished piece 1 at 14-MAY-18
    
32. piece handle=11t2t69h_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
    
33. channel c2: backup set complete, elapsed time: 00:00:26
    
34. channel c2: starting full datafile backup set
    
35. channel c2: specifying datafile(s) in backup set
    
36. input datafile file number=00005 name=+DATA/ORCL2/DATAFILE/example.267.976031415
    
37. channel c2: starting piece 1 at 14-MAY-18
    
38. channel c1: finished piece 1 at 14-MAY-18
    
39. piece handle=12t2t6ab_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
    
40. channel c1: backup set complete, elapsed time: 00:00:25
    
41. channel c1: starting full datafile backup set
    
42. channel c1: specifying datafile(s) in backup set
    
43. input datafile file number=00004 name=+DATA/ORCL2/DATAFILE/users.268.976031411
    
44. channel c1: starting piece 1 at 14-MAY-18
    
45. channel c2: finished piece 1 at 14-MAY-18
    
46. piece handle=13t2t6ab_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
    
47. channel c2: backup set complete, elapsed time: 00:00:25
    
48. 
    

复制代码

backup duration：

1. backup duration   00:05 minimize load tablespace users ;

复制代码

-----------------------------多租户体系结构：
1. 从V$视图开始熟悉（不要从数据字典视图开始），相同的v$视图要在根容器和插件数据库之一上分别查一遍。注意从（v$database，v$instance）之后查询结果就会不一样。要特别注意con_id这一列（0或1）。

2. 两个V$视图的连接，一定要加上a.con_id=b.con_id。

1. select   d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
   
2. from v$database d;
   
3. 
   
4. select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
   
5.   from v$instance i;
   
6.   
   
7.   select s.CON_ID,s.NAME,s.PDB
   
8.    from v$services s;
   
9.    
   
10.     select  p.CON_ID,p.NAME,p.OPEN_MODE
    
11.   from v$pdbs p;
    
12.   
    
13.   alter pluggable database pdb1_1 close;
    
14.   
    
15.       select  p.CON_ID,p.NAME,p.OPEN_MODE
    
16.   from v$pdbs p;
    
17.   
    
18.   alter pluggable database pdb1_1 open ;
    
19.   
    
20.     alter pluggable database pdb1_1 close immediate;
    
21.    
    
22.     select  c.CON_ID,c.DBID,c.PDB_ID, c.GUID  , c.PDB_NAME,c.STATUS
    
23.    from cdb_pdbs c;
    
24.    
    
25.     select  * from v$logfile;
    
26.    
    
27.      select * from v$controlfile;
    
28.      
    
29.       select  c.CON_ID,c.FILE_NAME,c.STATUS from
    
30.   cdb_data_files c order by c.CON_ID;
    
31.   
    
32.              select c.FILE_NAME,c.STATUS from
    
33.   dba_data_files c;
    
34.      
    
35.     select  d.CON_ID,d.NAME,d.STATUS
    
36.   from v$datafile d order by 1;
    
37.   
    
38.   -----------------
    
39.     select t.NAME,t.STATUS,t.CON_ID
    
40.    from v$tempfile t;
    
41.   
    
42.   --------------------
    
43.    select t.CON_ID,t.NAME,d.NAME,d.STATUS
    
44.   from v$tablespace t, v$datafile d
    
45.   where t.TS#=d.TS#  and t.CON_ID=d.CON_ID
    
46.   order by 1,2;
    
47.   
    
48.   

复制代码