|
- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-30-35上午.log
- Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-30-35上午.txt
- --------------------------------------------------------------------------------
- 已安装的顶级产品 (1):
- Oracle Database 11g 11.2.0.3.0
- 此 Oracle 主目录中已安装 1 个产品。
- 此 Oracle 主目录中未安装任何中间补丁程序。
- --------------------------------------------------------------------------------
- OPatch succeeded.
复制代码
如果是热补丁:
- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query -is_online_patch
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-32-39上午.log
- --------------------------------------------------------------------------------
- Patch is an online patch: true
- OPatch succeeded.
复制代码 如果是冷补丁:
- [oracle@station90 13906496]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query -is_online_patch
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-34-29上午.log
- --------------------------------------------------------------------------------
- Patch is an online patch: false
- 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.
- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply -connectString orcl::: -connectString rcat:::
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-40-37上午.log
- 补丁程序只应在 '-all_nodes' 模式下应用/回退。
- 将 RAC 模式转换为 '-all_nodes' 模式。
- Applying interim patch '13906496' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
- Verifying environment and performing prerequisite checks...
- 是否继续? [y|n]
- y
- User Responded with: Y
- All checks passed.
- Backing up files...
- 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
- The patch will be installed on active database instances.
- 正在数据库 'rcat' 上安装和启用联机补丁程序 'bug13906496.pch'。
- Patch 13906496 successfully applied
- Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-40-37上午.log
- OPatch succeeded.
复制代码- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-41-46上午.log
- Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-41-46上午.txt
- --------------------------------------------------------------------------------
- 已安装的顶级产品 (1):
- Oracle Database 11g 11.2.0.3.0
- 此 Oracle 主目录中已安装 1 个产品。
- 中间补丁程序 (1) :
- Patch (online) 13906496: applied on Sun May 13 09:40:48 CST 2018
- Unique Patch ID: 17324986
- Created on 22 Jul 2014, 12:09:07 hrs PST8PDT
- Bugs fixed:
- 13906496
- --------------------------------------------------------------------------------
- OPatch succeeded.
复制代码 回退热补丁:
- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch rollback -id 13906496 -connectString orcl::: -connectString rcat:::
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-02上午.log
- RollbackSession 从 OH '/u01/app/oracle/product/11.2.0/dbhome_1' 回退中间补丁程序 '13906496'
- 补丁程序只应在 '-all_nodes' 模式下应用/回退。
- 将 RAC 模式转换为 '-all_nodes' 模式。
- 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
- The patch will be removed from active database instances.
- 正在数据库 'rcat' 上禁用和删除联机补丁程序 'bug13906496.pch'
- RollbackSession 从产品清单中删除中间补丁程序 '13906496'
- Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-02上午.log
- OPatch succeeded.
复制代码- [oracle@station90 online]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-44-57上午.log
- Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-05-13_09-44-57上午.txt
- --------------------------------------------------------------------------------
- 已安装的顶级产品 (1):
- Oracle Database 11g 11.2.0.3.0
- 此 Oracle 主目录中已安装 1 个产品。
- 此 Oracle 主目录中未安装任何中间补丁程序。
- --------------------------------------------------------------------------------
- OPatch succeeded.
复制代码 打冷补丁:
- [oracle@station90 13906496]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply
- Invoking OPatch 11.2.0.1.7
- Oracle 中间补丁程序安装程序版本 11.2.0.1.7
- 版权所有 (c) 2011, Oracle Corporation。保留所有权利。
- Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
- Central Inventory : /u01/app/oraInventory
- from : /etc/oraInst.loc
- OPatch version : 11.2.0.1.7
- OUI version : 11.2.0.3.0
- Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-47-09上午.log
- Applying interim patch '13906496' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
- Verifying environment and performing prerequisite checks...
- 是否继续? [y|n]
- y
- User Responded with: Y
- All checks passed.
- 请关闭本地系统上在此 ORACLE_HOME 之外运行的 Oracle 实例。
- (Oracle 主目录 = '/u01/app/oracle/product/11.2.0/dbhome_1')
- 本地系统是否已准备打补丁? [y|n]
- y
- User Responded with: Y
- Backing up files...
- 正在为组件 oracle.rdbms, 11.2.0.3.0 打补丁...
- Patch 13906496 successfully applied
- Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-05-13_09-47-09上午.log
- OPatch succeeded.
复制代码
从11.2.0.3grid升到12.1.0.2grid的日志:
精确控制通道和输入(一般是输入文件多于通道数考虑):
- RMAN> run {
- 2> allocate channel c1 device type sbt ;
- 3> allocate channel c2 device type sbt ;
- 4> backup ( datafile 1,5 channel c1) ( datafile 2,3,4 channel c2 ) ;
- 5> release channel c1;
- 6> release channel c2;
- 7> }
- using target database control file instead of recovery catalog
- allocated channel: c1
- channel c1: SID=247 device type=SBT_TAPE
- channel c1: Oracle Secure Backup
- allocated channel: c2
- channel c2: SID=9 device type=SBT_TAPE
- channel c2: Oracle Secure Backup
- Starting backup at 13-MAY-18
- channel c1: starting full datafile backup set
- channel c1: specifying datafile(s) in backup set
- input datafile file number=00001 name=+DATADG/orcl/datafile/system.256.973363899
- input datafile file number=00005 name=+DATADG/orcl/datafile/example.266.973363999
- channel c1: starting piece 1 at 13-MAY-18
- channel c2: starting full datafile backup set
- channel c2: specifying datafile(s) in backup set
- input datafile file number=00002 name=+DATADG/orcl/datafile/sysaux.257.973363901
- input datafile file number=00003 name=+DATADG/orcl/datafile/undotbs1.258.973363901
- input datafile file number=00004 name=+DATADG/orcl/datafile/users.259.973363901
- channel c2: starting piece 1 at 13-MAY-18
- channel c2: finished piece 1 at 13-MAY-18
- piece handle=14t2pkp1_1_1 tag=TAG20180513T113600 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c2: backup set complete, elapsed time: 00:01:05
- channel c1: finished piece 1 at 13-MAY-18
- piece handle=13t2pkp1_1_1 tag=TAG20180513T113600 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c1: backup set complete, elapsed time: 00:01:15
- Finished backup at 13-MAY-18
- Starting Control File and SPFILE Autobackup at 13-MAY-18
复制代码
rman 会话定位:
- SELECT s.sid, p.spid, s.client_info ,s.terminal
- FROM v$process p, v$session s
- WHERE p.addr = s.paddr
- AND s.terminal in ( SELECT s.terminal
- FROM v$process p, v$session s
- WHERE p.addr = s.paddr
- AND CLIENT_INFO LIKE '%botang%');
- SELECT s.sid, OPNAME, CONTEXT, SOFAR, TOTALWORK,
- ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
- FROM V_$SESSION_LONGOPS s
- WHERE OPNAME LIKE 'RMAN%'
- AND OPNAME NOT LIKE '%aggregate%'
- AND TOTALWORK != 0
- AND SOFAR <> TOTALWORK
- and s.sid in ( SELECT s.sid
- FROM v$process p, v$session s
- WHERE p.addr = s.paddr
- AND s.terminal in ( SELECT s.terminal
- FROM v$process p, v$session s
- WHERE p.addr = s.paddr
- AND CLIENT_INFO LIKE '%botang%'));
复制代码
-----------------------------------
同步和异步IO(磁带机)
-
-
- select a.IO_COUNT ,a.LONG_WAITS ,a.SHORT_WAIT_TIME_TOTAL
- ,a.LONG_WAIT_TIME_TOTAL from v_$backup_async_io a
- where a.sid in ( SELECT s.sid
- FROM v$session s
- WHERE s.terminal in ( SELECT s.terminal
- FROM v$session s
- WHERE CLIENT_INFO LIKE '%botang%'));
-
- select * from v_$backup_sync_io a
- where a.sid in ( SELECT s.sid
- FROM v$session s
- WHERE s.terminal in ( SELECT s.terminal
- FROM v$session s
- WHERE CLIENT_INFO LIKE '%botang%'));
复制代码
maxopenfiles和filesperset语法:
- RMAN> run {
- 2> allocate channel c1 device type sbt maxopenfiles 1;
- 3> allocate channel c2 device type sbt maxopenfiles 1;
- 4> backup database filesperset 1;
- 5> }
- allocated channel: c1
- channel c1: SID=711 device type=SBT_TAPE
- channel c1: Oracle Secure Backup
- allocated channel: c2
- channel c2: SID=483 device type=SBT_TAPE
- channel c2: Oracle Secure Backup
- Starting backup at 14-MAY-18
- channel c1: starting full datafile backup set
- channel c1: specifying datafile(s) in backup set
- input datafile file number=00001 name=+DATA/ORCL2/DATAFILE/system.266.976031403
- channel c1: starting piece 1 at 14-MAY-18
- channel c2: starting full datafile backup set
- channel c2: specifying datafile(s) in backup set
- input datafile file number=00002 name=+DATA/ORCL2/DATAFILE/sysaux.269.976031405
- channel c2: starting piece 1 at 14-MAY-18
- channel c1: finished piece 1 at 14-MAY-18
- piece handle=10t2t69h_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c1: backup set complete, elapsed time: 00:00:26
- channel c1: starting full datafile backup set
- channel c1: specifying datafile(s) in backup set
- input datafile file number=00003 name=+DATA/ORCL2/DATAFILE/undotbs1.270.976031403
- channel c1: starting piece 1 at 14-MAY-18
- channel c2: finished piece 1 at 14-MAY-18
- piece handle=11t2t69h_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c2: backup set complete, elapsed time: 00:00:26
- channel c2: starting full datafile backup set
- channel c2: specifying datafile(s) in backup set
- input datafile file number=00005 name=+DATA/ORCL2/DATAFILE/example.267.976031415
- channel c2: starting piece 1 at 14-MAY-18
- channel c1: finished piece 1 at 14-MAY-18
- piece handle=12t2t6ab_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c1: backup set complete, elapsed time: 00:00:25
- channel c1: starting full datafile backup set
- channel c1: specifying datafile(s) in backup set
- input datafile file number=00004 name=+DATA/ORCL2/DATAFILE/users.268.976031411
- channel c1: starting piece 1 at 14-MAY-18
- channel c2: finished piece 1 at 14-MAY-18
- piece handle=13t2t6ab_1_1 tag=TAG20180514T195321 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c2: backup set complete, elapsed time: 00:00:25
复制代码
backup duration:
- 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。
- select d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
- from v$database d;
-
- select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
- from v$instance i;
-
- select s.CON_ID,s.NAME,s.PDB
- from v$services s;
-
- select p.CON_ID,p.NAME,p.OPEN_MODE
- from v$pdbs p;
-
- alter pluggable database pdb1_1 close;
-
- select p.CON_ID,p.NAME,p.OPEN_MODE
- from v$pdbs p;
-
- alter pluggable database pdb1_1 open ;
-
- alter pluggable database pdb1_1 close immediate;
-
- select c.CON_ID,c.DBID,c.PDB_ID, c.GUID , c.PDB_NAME,c.STATUS
- from cdb_pdbs c;
-
- select * from v$logfile;
-
- select * from v$controlfile;
-
- select c.CON_ID,c.FILE_NAME,c.STATUS from
- cdb_data_files c order by c.CON_ID;
-
- select c.FILE_NAME,c.STATUS from
- dba_data_files c;
-
- select d.CON_ID,d.NAME,d.STATUS
- from v$datafile d order by 1;
-
- -----------------
- select t.NAME,t.STATUS,t.CON_ID
- from v$tempfile t;
-
- --------------------
- select t.CON_ID,t.NAME,d.NAME,d.STATUS
- from v$tablespace t, v$datafile d
- where t.TS#=d.TS# and t.CON_ID=d.CON_ID
- order by 1,2;
-
-
复制代码
|
|