【ERROR】OPatch failed with error code 73

一、问题描述
今天要给Oracle数据库软件打补丁,操作过程中报错 “OPatch failed with error code 73” ,通过具体报错信息后得知是libclntsh.so.11.1文件被占用了,通过一路追查后得知某程序占用后,停掉相应的程序后打补丁通过。

二、实验
1.打补丁(未成功)

[oracle@suzzy 27734982]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.19
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/product/10.2.0/db
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/10.2.0/db/oraInst.loc
OPatch version    : 11.2.0.3.19
OUI version       : 11.2.0.4.0
Log file location : /oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2018-09-05_14-28-26PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/oracle/product/10.2.0/db/lib/libclntsh.so.11.1
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/oracle/product/10.2.0/db/bin/tnslsnr
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2018-09-05_14-28-26PM_1.log

OPatch failed with error code 73

2.查看什么进程占用 libclntsh.so.11.1

[oracle@suzzy 27734982]$ fuser  /oracle/product/10.2.0/db/lib/libclntsh.so.11.1
/oracle/product/10.2.0/db/lib/libclntsh.so.11.1:  2925m

3.查看2925进程是什么程序在用(监听程序)

[oracle@suzzy 27734982]$ ps -ef|grep 2925
oracle     2925      1  0 12:30 ?        00:00:00 /oracle/product/10.2.0/db/bin/tnslsnr LISTENER -inherit
oracle    12224  11247  0 14:32 pts/0    00:00:00 grep 2925

4.停止监听程序

[oracle@suzzy 27734982]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-SEP-2018 14:33:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

5.检查2925进程是否存在

[oracle@suzzy 27734982]$ ps -ef|grep 2925
oracle    12238  11247  0 14:33 pts/0    00:00:00 grep 2925

6.再次打补丁(成功)

[oracle@suzzy 27734982]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.19
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/product/10.2.0/db
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/10.2.0/db/oraInst.loc
OPatch version    : 11.2.0.3.19
OUI version       : 11.2.0.4.0
Log file location : /oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2018-09-05_14-35-09PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075  25869727  26609445  26392168  26925576  27338049  27734982  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/product/10.2.0/db')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.sdo, 11.2.0.4.0...

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.sdo.locator, 11.2.0.4.0...

Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...
Applying sub-patch '18031668' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.ldap.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.crs, 11.2.0.4.0...

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...

Patching component oracle.rdbms.deconfig, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...
Applying sub-patch '18522509' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.deconfig, 11.2.0.4.0...
Applying sub-patch '19121551' to OH '/oracle/product/10.2.0/db'

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.sysman.console.db, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.ordim.client, 11.2.0.4.0...

Patching component oracle.ordim.jai, 11.2.0.4.0...
Applying sub-patch '19769489' to OH '/oracle/product/10.2.0/db'
ApplySession: Optional component(s) [ oracle.sysman.agent, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.ovm, 11.2.0.4.0...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.rdbms.util, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.xdk.parser.java, 11.2.0.4.0...

Patching component oracle.oraolap, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms.deconfig, 11.2.0.4.0...
Applying sub-patch '20299013' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms.dv, 11.2.0.4.0...

Patching component oracle.rdbms.oci, 11.2.0.4.0...

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.sysman.common, 10.2.0.4.5...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.xdk.parser.java, 11.2.0.4.0...

Patching component oracle.sysman.console.db, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.sysman.common.core, 10.2.0.4.5...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms.deconfig, 11.2.0.4.0...
Applying sub-patch '20760982' to OH '/oracle/product/10.2.0/db'

Patching component oracle.sysman.console.db, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Applying sub-patch '21352635' to OH '/oracle/product/10.2.0/db'

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...
Applying sub-patch '21948347' to OH '/oracle/product/10.2.0/db'
ApplySession: Optional component(s) [ oracle.tfa, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.ovm, 11.2.0.4.0...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...

Patching component oracle.xdk.parser.java, 11.2.0.4.0...

Patching component oracle.sysman.console.db, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.5...
Applying sub-patch '22502456' to OH '/oracle/product/10.2.0/db'
ApplySession: Optional component(s) [ oracle.tfa, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.oraolap.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.olap, 11.2.0.4.0...

Patching component oracle.oraolap, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Applying sub-patch '23054359' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms.dv, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Applying sub-patch '24006111' to OH '/oracle/product/10.2.0/db'

Patching component oracle.sqlplus.ic, 11.2.0.4.0...

Patching component oracle.sqlplus, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Applying sub-patch '24732075' to OH '/oracle/product/10.2.0/db'

Patching component oracle.precomp.common, 11.2.0.4.0...

Patching component oracle.sysman.plugin.db.main.agent, 11.2.0.4.0...

Patching component oracle.sqlplus.ic, 11.2.0.4.0...

Patching component oracle.sqlplus, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.util, 11.2.0.4.0...

Patching component oracle.ordim.client, 11.2.0.4.0...

Patching component oracle.ordim.jai, 11.2.0.4.0...

Patching component oracle.ordim.server, 11.2.0.4.0...
Applying sub-patch '25869727' to OH '/oracle/product/10.2.0/db'
ApplySession: Optional component(s) [ oracle.oid.client, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.rsf, 11.2.0.4.0...

Patching component oracle.oracore.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...
Applying sub-patch '26609445' to OH '/oracle/product/10.2.0/db'

Patching component oracle.oracore.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...
Applying sub-patch '26392168' to OH '/oracle/product/10.2.0/db'
ApplySession: Optional component(s) [ oracle.oid.client, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.rsf, 11.2.0.4.0...

Patching component oracle.ldap.client, 11.2.0.4.0...

Patching component oracle.sysman.agent, 10.2.0.4.5...

Patching component oracle.xdk, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.network.listener, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...

Patching component oracle.xdk.parser.java, 11.2.0.4.0...

Patching component oracle.xdk.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...
Applying sub-patch '26925576' to OH '/oracle/product/10.2.0/db'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Applying sub-patch '27338049' to OH '/oracle/product/10.2.0/db'

Patching component oracle.assistants.server, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Applying sub-patch '27734982' to OH '/oracle/product/10.2.0/db'

Patching component oracle.ctx, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...

Patching component oracle.ctx.rsf, 11.2.0.4.0...

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rman, 11.2.0.4.0...

OPatch found the word "error" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
chmod: changing permissions of `/oracle/product/10.2.0/db/bin/extjobO': Operation not permitted
make: [iextjob] Error 1 (ignored)


Composite patch 27734982 successfully applied.
OPatch Session completed with warnings.
Log file location: /oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2018-09-05_14-35-09PM_1.log

OPatch completed with warnings.

7.验证补丁是否成功修补完成

[oracle@suzzy 27734982]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.19
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/product/10.2.0/db
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/10.2.0/db/oraInst.loc
OPatch version    : 11.2.0.3.19
OUI version       : 11.2.0.4.0
Log file location : /oracle/product/10.2.0/db/cfgtoollogs/opatch/opatch2018-09-05_14-39-39PM_1.log

Lsinventory Output file location : /oracle/product/10.2.0/db/cfgtoollogs/opatch/lsinv/lsinventory2018-09-05_14-39-39PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: suzzy
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  27734982     : applied on Wed Sep 05 14:37:46 CST 2018
Unique Patch ID:  22229176
Patch description:  "Database Patch Set Update : 11.2.0.4.180717 (27734982)"
   Created on 31 May 2018, 01:37:49 hrs PST8PDT
Sub-patch  27338049; "Database Patch Set Update : 11.2.0.4.180417 (27338049)"
Sub-patch  26925576; "Database Patch Set Update : 11.2.0.4.180116 (26925576)"
Sub-patch  26392168; "Database Patch Set Update : 11.2.0.4.171017 (26392168)"
Sub-patch  26609445; "Database Patch Set Update : 11.2.0.4.170814 (26609445)"
Sub-patch  25869727; "Database Patch Set Update : 11.2.0.4.170718 (25869727)"
Sub-patch  24732075; "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Sub-patch  24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"
Sub-patch  23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"
Sub-patch  22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Sub-patch  21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     21174504, 17184721, 21538558, 16091637, 18092127, 17381384, 15979965
     20671094, 16731148, 16314254, 13837378, 18441944, 17835048, 13558557
     17008068, 17201159, 25427662, 17853498, 20717359, 17246576, 18356166
     18681862, 18440047, 20569094, 20031873, 16875449, 20387265, 19788842
     17296856, 21330264, 14010183, 17648596, 17551063, 17025461, 24719736
     17267114, 22507210, 17912217, 17889583, 18202441, 17040764, 17478145
     16524926, 25655390, 19358317, 22148226, 18747196, 26544823, 18641419
     17036973, 18948177, 17811789, 16542886, 14285317, 18009564, 16618694
     8322815, 16832076, 18247991, 16692232, 22507234, 17570240, 13871092
     24624166, 24348685, 17848897, 17441661, 14034426, 17465741, 16596890
     17437634, 21343897, 20506706, 21453153, 18339044, 17951233, 22321741
     21795111, 18430495, 21787056, 22380919, 19469538, 20506715, 17811429
     19721304, 17903598, 18230522, 19554106, 19458377, 21281607, 17612828
     6599380, 22092979, 22321756, 17040527, 17811438, 18641461, 14657740
     13364795, 21387964, 19490948, 17346671, 17588480, 22351572, 18235390
     26474853, 18849970, 17889549, 19309466, 16472716, 20596234, 18331850
     18641451, 17344412, 21179898, 19461270, 17546761, 24842886, 14521849
     18203835, 18203838, 18964939, 18203837, 17313525, 22195457, 18139690
     16837842, 22296366, 14106803, 17842825, 21352646, 22657942, 16360112
     20657441, 22195441, 17389192, 26198926, 14565184, 17205719, 18440095
     14764829, 22195448, 14354737, 13944971, 16571443, 21868720, 17186905
     17080436, 18673342, 22905130, 17027426, 27374796, 19972569, 19972568
     20144308, 19972566, 17282229, 19972564, 16870214, 21629064, 19615136
     21354456, 17390431, 18762750, 23007241, 16613964, 17957017, 18098207
     18471685, 19730508, 21538485, 18264060, 17323222, 17754782, 17600719
     18317531, 17852463, 17596908, 17655634, 16228604, 27053456, 20074391
     19972570, 18090142, 18996843, 19854503, 16042673, 17835627, 20334344
     17393683, 20861693, 18000422, 17551709, 26575788, 23315889, 20506699
     19006849, 18277454, 18456514, 19174430, 17258090, 17174582, 25654936
     17242746, 16399083, 17824637, 21132297, 22465352, 17762296, 22168163
     17397545, 16450169, 12364061, 20067212, 18856999, 19211724, 19463893
     19463897, 21343775, 17853456, 18673304, 20004021, 26030218, 21668627
     16194160, 17477958, 16538760, 12982566, 24570598, 20828947, 18259031
     20296213, 18293054, 17610798, 19699191, 23065323, 17311728, 18135678
     18774543, 23294548, 16785708, 10136473, 24560906, 22551446, 19777862
     17786518, 18315328, 18334586, 12747740, 18096714, 19032867, 21641760
     17390160, 18899974, 17232014, 20598042, 18673325, 16422541, 18155762
     14015842, 19827973, 22683225, 17726838, 18554871, 23177648, 18051556
     20803583, 21972320, 15990359, 17922254, 18282562, 16855292, 16668584
     21343838, 20299015, 17446237, 18093615, 18043064, 23713236, 17694209
     17288409, 18308268, 20475845, 17274537, 13955826, 16934803, 17634921
     17501491, 16315398, 23725036, 22683212, 17006183, 13829543, 18191164
     17655240, 26746894, 22809871, 18384391, 19393542, 21538567, 16198143
     21847223, 25823754, 17892268, 20142975, 19584068, 17165204, 25165496
     18604493, 21756699, 18508861, 16901385, 18554763, 21532755, 18189036
     17443671, 17385178, 14829250, 17936109, 20925795, 20509482, 17478514
     27441326, 16850630, 13951456, 16595641, 14054676, 15861775, 21142837
     16912439, 17299889, 17297939, 23003979, 18619917, 16833527, 17798953
     17816865, 18607546, 17571306, 21286665, 17341326, 26910644, 17851160
     20558005, 17586955, 19049453, 21051840, 17587063, 16956380, 18328509
     25423453, 14133975, 18061914, 18522509, 21051833, 20294666, 18765602
     20860659, 20324049, 18199537, 17332800, 13609098, 22502493, 18384537
     14338435, 17945983, 16392068, 21067387, 17752995, 21051862, 16863422
     25505382, 17237521, 18244962, 19544839, 24433711, 24717859, 17156148
     18973907, 23026585, 17877323, 17449815, 18180390, 17088068, 17037130
     20004087, 21422580, 19466309, 11733603, 25505371, 21051858, 18084625
     18674024, 21051852, 18091059, 25369547, 16306373, 18306996, 17787259
     18193833, 19915271, 20513399, 20631274, 25879656, 16344544, 14692762
     18614015, 17346091, 18228645, 17721717, 18436307, 21756677, 19888853
     11883252, 17891943, 19475971, 22353199, 16384983, 19121551, 27825893
     12816846, 17982555, 17761775, 22243719, 17265217, 25505394, 17071721
     16721594, 18262334, 21756661, 17891946, 15913355, 17672719, 17602269
     17239687, 17042658, 17238511, 17811456, 17284817, 17752121, 20879889
     21380789, 17394950, 17011832, 16579084, 22195465, 14602788, 18325460
     24476265, 26569225, 24476274, 12611721, 16903536, 17006570, 19689979
     16043574, 18783224, 24662775, 16494615, 21526048, 17392698, 19197175
     16069901, 17811447, 27870645, 17308789, 22195477, 24835538, 17865671
     17343514, 19013183, 17325413, 18316692, 16180763, 17348614, 14368995
     21983325, 17393915, 16285691, 19211433, 20331945, 17883081, 17705023
     24316947, 17614227, 19578350, 22195485, 14084247, 13645875, 16777840
     19727057, 14852021, 18744139, 18674047, 17716305, 19285025, 18482502
     27534509, 17622427, 19289642, 22195492, 25947799, 14458214, 20869721
     21172913, 17767676, 18723434, 25505407, 17786278, 19258504, 17082983
     21351877, 17365043, 13498382, 18331812, 16065166, 25489607, 16685417
     18031668, 22893153, 16943711, 19272701, 21517440, 25897615, 17649265
     13866822, 18094246, 24528741, 17783588, 14245531, 17082359, 18280813
     20448824, 23330119, 16268425, 19487147, 25600421, 18018515, 17302277
     17215560, 24411921, 19271443, 25764020, 17016369, 20777150, 23330124
     16756406, 20441797, 19769489, 28100487, 17545847, 25093656, 18260550
     13853126, 17227277, 23536835, 25957038, 24652769, 19207117, 9756271
     18868646, 17614134, 26667023, 17546973, 18704244, 19680952, 26667015
     17050888, 18828868, 18273830, 17360606, 16992075, 24563422, 17375354
     12905058, 18362222, 21429602, 27086138, 17571039, 17468141, 18436647
     17235750, 21168487, 16220077, 16929165

--------------------------------------------------------------------------------

OPatch succeeded.

三、总结
打补丁看似非常容易的事,可就这么个小事情都给我绊住啦。还记得某人与我讲的话,数据库没小事。看来还真是这样,还要多多学习,多多写东西才可以。给自己放的假有些长了,现在我回来了。要继续开始学习啦,正好今天早晨与位好朋友微信聊了几句,才发现每个人都这么努力的学习,而我怎么还会给自己找借口呢。不要忘记自己的初心,加油吧,继续向自己的梦想前进。Where there is a will there is a way.

 

发表在 oracle, Upgrade | 标签为 , , | 留下评论

Oracle 静默安装-单机

一、前期准备
1.硬件检查

cat /etc/issue
uname -r (版本)
grep MemTotal /proc/meminfo (内存大小)
grep SwapTotal /proc/meminfo (交换区大小)
grep "model name" /proc/cpuinfo (CPU信息)
free (可用内存)

2.检查依赖包

[root@dgc ~]# rpm -q binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc-2.5 glibc-common glibc-devel glibc-headers ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel
binutils-2.20.51.0.2-5.42.el6.x86_64
package compat-libstdc++-33 is not installed
elfutils-libelf-0.158-3.2.el6.x86_64
package elfutils-libelf-devel is not installed
gcc-4.4.7-11.el6.x86_64
package gcc-c++ is not installed
package glibc-2.5 is not installed
glibc-common-2.12-1.149.el6.x86_64
glibc-devel-2.12-1.149.el6.x86_64
glibc-headers-2.12-1.149.el6.x86_64
package ksh is not installed
libaio-0.3.107-10.el6.x86_64
package libaio-devel is not installed
libgcc-4.4.7-11.el6.x86_64
libstdc++-4.4.7-11.el6.x86_64
package libstdc++-devel is not installed
make-3.81-20.el6.x86_64
sysstat-9.0.4-27.el6.x86_64
unixODBC-2.2.14-14.el6.x86_64
package unixODBC-devel is not installed

3.创建所需的操作系统组和用户

groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle

4.创建安装目录及权限

mkdir -p /app/oracle
chown -R oracle.oinstall /app/oracle

5.修改内核参数

vi /etc/sysctl.conf 
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
sysctl -p

6.修改用户限制

vi /etc/security/limits.conf
#for oracle
oracle           soft    nproc             2047
oracle           hard    nproc           16384
oracle           soft    nofile             1024
oracle           hard    nofile            65536

vi /etc/pam.d/login
session required /lib/security/pam_limits.so

7.创建/etc/oraInst.loc文件,内容如下

nventory_loc=/app/oracle/oraInventory
inst_group=oinstall

更改文件的权限

chown oracle:oinstall /etc/oraInst.loc
chmod 664 /etc/oraInst.loc

8.设置oracle环境变量

vi /home/oracle/.bash_profile
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11204/db
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022

9.使设置生效

source /home/oracle/.bash_profile

10.配置hosts文件

vi /etc/hosts
10.102.98.231   adgc

注:
同时,在使用静默安装的时候会检查DISPLAY的设置,如果经常说检查DISPLAY不行的话,到root用户下执行 “xhost + 你的ip:0.0”
二、实施部署
1.解压oracle安装文件

unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

2.复制响应文件模板,设置响应文件权限(注意调整个人环境路径 )

mkdir $ORACLE_BASE/etc
cp /backup/software/database/response/* /app/oracle/etc/
su - root
chmod 700 /app/oracle/etc/*.rsp(注意所有者为 oinstall)

3.静默安装Oracle软件
(1)修改安装Oracle软件的响应文件/app/oracle/etc/db_install.rsp (可根据里面的配置提示来操作,下面是一些常用配置,作为参考)

oracle.install.option=INSTALL_DB_SWONLY // 安装类型
ORACLE_HOSTNAME=dgc // 主机名称(hostname查询)
UNIX_GROUP_NAME=oinstall // 安装组
INVENTORY_LOCATION=/app/oracle/oraInventory //INVENTORY目录(不填就是默认值)
SELECTED_LANGUAGES=en,zh_CN,zh_TW // 选择语言
ORACLE_HOME=/app/oracle/product/11204/db_1 // oracle_home
ORACLE_BASE=/app/oracle // oracle_base
oracle.install.db.InstallEdition=EE // oracle版本
oracle.install.db.isCustomInstall=false //自定义安装,否,使用默认组件
oracle.install.db.DBA_GROUP=dba // dba用户组
oracle.install.db.OPER_GROUP=oinstall // oper用户组
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE //数据库类型
oracle.install.db.config.starterdb.globalDBName=orcl //globalDBName
oracle.install.db.config.starterdb.SID=orcl //SID
oracle.install.db.config.starterdb.memoryLimit=32768 //自动管理内存的内存(M)
oracle.install.db.config.starterdb.password.ALL=oracle //设定所有数据库用户使用同一个密码

注:
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false(手动写了false)
DECLINE_SECURITY_UPDATES=true  //设置安全更新(貌似是有bug,这个一定要选true,否则会无限提醒邮件地址有问题,终止安装。PS:不管地址对不对)

(2)开始静默安装

[root@dgc database]# su - oracle
[root@dgc oracle]# cd /backup/software/database/
[oracle@dgc database]$ ./runInstaller -silent -responsefile /app/oracle/etc/db_install.rsp 
正在启动 Oracle Universal Installer...

检查临时空间: 必须大于 120 MB。   实际为 725253 MB    通过
检查交换空间: 必须大于 150 MB。   实际为 32279 MB    通过
准备从以下地址启动 Oracle Universal Installer /tmp/OraInstall2018-03-12_06-24-28PM. 请稍候...[oracle@dgc database]$ [WARNING] [INS-13014] 目标环境不满足一些可选要求。
   原因: 不满足一些可选的先决条件。有关详细信息, 请查看日志。/app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log
   操作: 从日志 /app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log 中确定失败的先决条件检查列表。然后, 从日志文件或安装手册中查找满足这些先决条件的适当配置, 并手动进行修复。
可以在以下位置找到本次安装会话的日志:
 /app/oracle/oraInventory/logs/installActions2018-03-12_06-24-28PM.log
Oracle Database 11g 的 安装 已成功。
请查看 '/app/oracle/oraInventory/logs/silentInstall2018-03-12_06-24-28PM.log' 以获取详细资料。

以 root 用户的身份执行以下脚本:
        1. /app/oracle/product/11204/db_1/root.sh

Successfully Setup Software.

(3)以 root 用户的身份执行脚本/app/oracle/product/11204/db_1/root.sh
(4)另一种方式,直接使用命令加参数方式安装

$ ./runInstaller -silent -debug -force \
FROM_LOCATION=/backup/software/database/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/app/oracle/oraInventory \
ORACLE_HOME=/app/oracle/product/11204/db_1 \
ORACLE_HOME_NAME="OraDb11g_Home" \
ORACLE_BASE=/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
DECLINE_SECURITY_UPDATES=true

… … 
结束时会出现
Oracle Database 11g 的 安装 已成功。
请查看 '/app/oracle/oraInventory/logs/silentInstall2018-03-12_05-28-14PM.log' 以获取详细资料。

(5)以 root 用户的身份执行脚本/app/oracle/product/11204/db_1/root.sh

[root@dgc tmp]#  /app/oracle/product/11204/db_1/root.sh
Check /app/oracle/product/11204/db_1/install/root_dgc_2018-03-12_17-31-43.log for the output of root script

4.静默安装DB
(1)编辑配置文件dbca.rsp (可根据里面的配置提示来操作,下面是一些常用配置,作为参考)

[GENERAL]
RESPONSEFILE_VERSION = "11.2.0"
OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]
GDBNAME = "orcl"
SID = "orcl"
TEMPLATENAME = "General_Purpose.dbc"  /*根据下面的模板来设定*/
CHARACTERSET = "AL32UTF8"  /* Default value US7ASCII */

(2)安装DB

[oracle@dgc ~]$ dbca -silent -responsefile /app/oracle/etc/dbca.rsp 

报错: 模板 new_orcl.dbc 不存在。请为数据库创建操作指定现有模板。

安装模版所在位置:

[oracle@dgc ~]$ cd /app/oracle/product/11204/db_1/assistants/dbca/templates
[oracle@dgc templates]$ ls
Data_Warehouse.dbc  example.dmp          New_Database.dbt   Seed_Database.dfb
example01.dfb       General_Purpose.dbc  Seed_Database.ctl

[oracle@dgc etc]$ dbca -silent -responsefile /app/oracle/etc/dbca.rsp 
输入 SYS 用户口令: 
 
输入 SYSTEM 用户口令: 
  
复制数据库文件
1% 已完成
3% 已完成
11% 已完成
18% 已完成
26% 已完成
37% 已完成
正在创建并启动 Oracle 实例
40% 已完成
45% 已完成
50% 已完成
55% 已完成
56% 已完成
60% 已完成
62% 已完成
正在进行数据库创建
66% 已完成
70% 已完成
73% 已完成
85% 已完成
96% 已完成
100% 已完成
有关详细信息, 请参阅日志文件 "/app/oracle/cfgtoollogs/dbca/orcl/orcl.log"。

5.安装配置网络监听
(1)安装监听

[oracle@dgc etc]$ netca -silent -responsefile /app/oracle/etc/netca.rsp 

正在对命令行参数进行语法分析:
参数"silent" = true
参数"responsefile" = /app/oracle/etc/netca.rsp
完成对命令行参数进行语法分析。
Oracle Net Services 配置:
完成概要文件配置。
Oracle Net 监听程序启动:
    正在运行监听程序控制: 
      /app/oracle/product/11204/db_1/bin/lsnrctl start LISTENER
    监听程序控制完成。
    监听程序已成功启动。
监听程序配置完成。
成功完成 Oracle Net Services 配置。退出代码是0

(2)检查监听状态

[oracle@dgc etc]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-MAR-2018 19:50:19

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                12-MAR-2018 19:49:58
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11204/db_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dgc/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgc)(PORT=1521)))
The listener supports no services
The command completed successfully

(3)增加tnsnames.ora文件

[oracle@dgc admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dgc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

(4)测试连接

[oracle@dgc admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 12 23:00:54 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dgc admin]$ 

(5)检查并修改数据库参数(根据情况而定)
例如: memory_max_target, sga_max_size, processes, pga_aggregate_target 等等

SYS@orcl > alter system set processes=1000 scope=spfile;
System altered.

SYS@orcl >  alter system set sga_max_size=20g scope=spfile;
System altered.

SYS@orcl > alter system set sga_target=20g scope=spfile;
System altered.

SYS@orcl > alter system set pga_aggregate_target=6g scope=spfile;
System altered.

SYS@orcl > alter system set nls_time_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
System altered.

三、总结
安装结束了,早就想做这个实验了,就是一直犯懒,2018年不能再这样懒下去了。静默安装分为3步,分别是安装软件,建立数据库,配置监听。个人感觉分开弄比较好,也是前辈提倡的步骤,因为这样可以在出现问题时,很容易定位,并且做好一步是一步,比如软件安装好了,建立数据库时出错时,软件就不用重新安装了,只是找到原因,重新建立数据库即可。还有就是可以选择使用rsp响应文件或者直接命令方式,后面直接写上想着配置参数。最后一点就是要注意在安装后记得检查修改参数,因为一但数据库投入运行再发现参数不正确,修改就显得被动了。最后还是给自己打打气,2017未实现的,希望能在2018实现,加油!Where there is a will there is a way.

 

发表在 Basic, oracle | 标签为 , | 留下评论

【Partition table】分区表删除分区数据时,导致索引失效

一、描述
今天有个小任务就是要删除些数据,哈哈,先自己小开心一下。因为要删除的数据表是我之前转换成的分区表。这个分区表是按照里面有个创建时间字段来分区的,1个季度为1个分区。所以我现在要将2017年7月1日之前的数据删除(数据量约1000万),可以直接删除表分区数据就好。如果要是用delete去删除这么多的数据,我还要写存储过程,分批提交的这样做。就是这样的一简单的truncate partition 引发了后继的业务故障。最终查询到该表的索引失效,重建立后恢复。真是汗!

二、实验
1.创建环境

SQL> create table TEST_PARTAS (id number(11), ACCOUNT_ID number(11) ,CTIME date) 
  2  partition by range (CTIME)
  3  interval( NUMTOYMINTERVAL(3,'month'))
  4  (partition P0 values less than (TO_DATE('2016-01-01','yyyy-mm-dd')),
  5   partition p1 values less than (to_date('2017-01-01','yyyy-mm-dd')));

Table created.

SQL>  insert into TEST_PARTAS select t.id,t.account_id,t.create_time from act_test t;
3483178 rows created.

SQL> commit;
Commit complete.

SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS('SAM','TEST_PARTAS');
PL/SQL procedure successfully completed.

2.检查分区表及数据

SQL> select count(*) from TEST_PARTAS;

  COUNT(*)
----------
   3483178

SQL> set lines 120 pages 200;
SQL> set long 9999999
SQL> col table_name for a15
SQL> col PARTITION_NAME for a10

SQL> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;  

TABLE_NAME      PARTITION_   NUM_ROWS     BLOCKS INT HIGH_VALUE
--------------- ---------- ---------- ---------- --- --------------------------------------------------
TEST_PARTAS     P0            2182116       6046 NO  TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     P1             616290      36506 NO  TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1611       44829       4030 YES TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1612       21706       3022 YES TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1613      172525       3022 YES TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1614      442435       2014 YES TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

TEST_PARTAS     SYS_P1615        3277        238 YES TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                     MI:SS', 'NLS_CALENDAR=GREGORIAN')


7 rows selected.

3.创建主键和索引

SQL> alter table TEST_PARTAS add constraint pk_id primary key(ID);
Table altered.

SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);
Index created.

4.检查索引状态,当前状态可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

5.用truncate 删除p0分区数据,不加update index参数

SQL> alter table test_partas truncate partition p0;

Table truncated.

6.检查索引状态,状态不可用

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    UNUSABLE
IND_ACCOUNT_ID                 TEST_PARTAS                    UNUSABLE

7.重建立索引,要加online ,尽量减小对业务的冲

SQL> alter index PK_ID rebuild online;

Index altered.

SQL> alter index IND_ACCOUNT_ID rebuild online;

Index altered.

8.检查索引状态,此时索引恢复正常可用状态

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

9.用truncate 删除p1分区数据,增加update index参数

SQL> alter table test_partas truncate partition p1 update indexes;

Table truncated.

10.检查索引状态,此时索引正常可用状态

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    VALID
IND_ACCOUNT_ID                 TEST_PARTAS                    VALID

三、扩展
通过这个问题,我们再扩展一下,如果drop分区会不会同样影响索引,答案是肯定的,删除分区,索引仍然失效。

SQL> alter table test_partas drop partition SYS_P1611;

Table altered.

SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_ID                          TEST_PARTAS                    UNUSABLE
IND_ACCOUNT_ID                 TEST_PARTAS                    UNUSABLE

四、总结
一个小小的失误,带来了大大的问题,还好这次操作,影响的不是核心业务表。通过失误,也让我看到了自己对知识点掌握上的不足。以后的路还很远,振作起来,努力学习吧。让自己在后面的DB生涯中,少范错误,多多提高效率。没有什么比学习与进步更快乐,In the end, Happy spring festival 2018 to everyone.

 

发表在 Basic, oracle | 标签为 , | 留下评论

【ERROR】rm删除时,参数列表过长,及处理方法

一、问题描述
由于日志过多,将磁盘空间占满,处理时碰到参数列表过报错。主要原因是要删除的文件数量过大导致。需要采用 find […] | xargs rm -rf […] 方式来处理。

二、故障处理
1.删除过程中报错

[oracle@zw-oradb02 adump]$ rm -rf *.aud
-bash: /bin/rm: 参数列表过长

2.修改命令行

[oracle@zw-oradb02 adump]$ find . -name "*.aud" |xargs rm -rf "*.aud”

三、总结
如果天天都出这样的小问题,每个都能掌握起来,不久的将来也可以成长为IT精英小人物。事上无难事,只怕有心人,让小事情来得再猛烈些,哈哈。Where there is a will there is a way.

 

发表在 ERROR, system | 标签为 , | 留下评论

【Mysql】连接数过多,应急处理方法

一、问题描述
今天突然接到个问题,网页报错:503 Service Temporarily Unavailable。经过查询发现是某个用户的连接超级多,已经将数据库连接占满。处理方案,即时杀掉堵塞的进程,之后可以扩大max_connections参数。

ebd429bc-1a22-49d6-8264-236056c9770c

二、处理方法
1.查询连接情况

root@localhost > show processlist;
…...
1001 rows in set (0.00 sec)
root@localhost > show variables like '%proces%';
Empty set (0.00 sec)

2.检查参数

root@localhost > show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1001  |
+----------------------+-------+
1 row in set (0.00 sec)

3.通过命令生成杀进程脚本

root@localhost > select concat('KILL ',id,';') from information_schema.processlist where user=’sam' into outfile '/tmp/a.txt';
Query OK, 991 rows affected (0.02 sec)

脚本内容如下:

+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 31964612;         |
| KILL 31964609;         |
| KILL 31964611;         |
…...
| KILL 31966619;         |
| KILL 31966620;         |
+------------------------+
991 rows in set (0.02 sec)
root@localhost > 

4.执行上面生成的KILL脚本

root@localhost > source /tmp/a.txt
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
……

5.检查连接状况,恢复正常

root@localhost > show processlist;

6.修改Max_used_connections参数(注:记得要修改my.cnf文件,下次重启动后仍然有效)

mysql> set GLOBAL max_connections=2000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

三、总结
Mysql的参数学习之max_connections,一个控制连接数的参数。此问题背后肯定存在着某些问题,不要只是一味地调大参数。后来经过对语句的分析,最终此问题定位为安全部门在做安全测试,导致问题产生。2017年只剩下最后1周了,提前祝大家元旦快乐。Happy every day.

 

发表在 mysql | 标签为 | 留下评论

Oracle Function — wmsys.wm_concat 合并行记录函数

一、描述
小需求又来了,现在需要将某表上的记录查询出来并且要求连接使用。通过查找后发现Oracle有个内部函数 wm_concat,这个函数在wmsys用户下。使用时那是当然的容易。一般人我都不告诉他(开个小玩笑)。

二、实验
1.创建实验表

SAM@orcl > create table t1 (id int, name varchar2(10));
Table created.

SAM@orcl > insert into t1 values (1,'sam1');
1 row created.

SAM@orcl > insert into t1 values (2,'sam2');
1 row created.

SAM@orcl > insert into t1 values (3,'sam3');
1 row created.

SAM@orcl > insert into t1 values (4,'sam4');
1 row created.

SAM@orcl > insert into t1 values (5,'sam5');
1 row created.

SAM@orcl > commit;
Commit complete.

SAM@orcl > select * from t1;

        ID NAME
---------- ----------
         1 sam1
         2 sam2
         3 sam3
         4 sam4
         5 sam5

2.使用wm_concat函数查询,并得到想要的结果

SAM@orcl > select wm_concat(id),wm_concat(name) from t1;

WM_CONCAT(ID)
--------------------------------------------------------------------------------
WM_CONCAT(NAME)
--------------------------------------------------------------------------------
1,2,3,4,5
sam1,sam2,sam3,sam4,sam5

3.到此处问题已被解决,但更加深入了解一下,我打算建立一个t2表,将查询出来的数据插入到t2表中,得到的结果却报错,数据类型不匹配。

SAM@orcl > create table t2 (sum_id int,sum_name varchar2(100));
Table created.

SAM@orcl > insert into t2  select wm_concat(id),wm_concat(name) from t1;
insert into t2  select wm_concat(id),wm_concat(name) from t1
                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

4.再次验证,看数据类型到底变为什么?将查出结果直接放在新建表t3中,成功。

SAM@orcl > create table t3 as  select wm_concat(id) as sum_id,wm_concat(name) as sum_name from t1;
Table created.

5.查看新表t3结构,数据类型发生变化,由源来的int, varchar2变为了clob

SAM@orcl > desc t3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SUM_ID                                             CLOB
 SUM_NAME                                           CLOB

6.查询t3表内容

SAM@orcl > select * from t3;

SUM_ID
--------------------------------------------------------------------------------
SUM_NAME
--------------------------------------------------------------------------------
1,2,3,4,5
sam1,sam2,sam3,sam4,sam5

四、扩展知识
1.此时开发人员认为这个函数果然厉害,就说能不能看一下这个函数如何写的,与此同时也想到我的心里去了,看看Oracle开发人员的思路。说不定能大开眼界。采用dbms_matadata.get_ddl来取function脚本。结果发现该Function已被加密,无法读取。加密方式为wrap加密。

SAM@orcl > set long 99999 pages 1000
SAM@orcl > SELECT SYS.DBMS_METADATA.get_ddl ('FUNCTION','WM_CONCAT','WMSYS') FROM DUAL;

SYS.DBMS_METADATA.GET_DDL('FUNCTION','WM_CONCAT','WMSYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
53 96
antgYqrbNGLSC7Re+71hueZFyT4wg0SvLZ6pyi+mUCJD1KOso
xPiallQXtwu7BTsCmx9/hIg
+ln6MEC75cHHT8YFQPvfjqPM1MuiY1Z0kXN0TQ0W8KE1SkAqjh/+tB/q
+oI45dREmV5OHaYy
H/E=

五、总结
有时候通过小小的知识点就可以帮助我们DBA和开发人员解决很大的问题,所以此时我想到的就是知识是无限的海洋,等待着我们不停的探索与学习。再有就是我认为古人云:三人行,必有我师。那是相当的corrent and nice. 最后还是希望我能够不断的成长与进步。fighting! Where there is a will, there is a way.

 

发表在 Basic, oracle | 标签为 , | 留下评论

Oracle通过Sqlplus结合Shell脚本方式生成Excel文件

一、问题描述
今天接到一个任务,是要求每天通过SQL脚本生成excel文件,并且自动发送到相关人员邮箱。这个需求我还真是没有做过,之前只做过通过SQL脚本生成为HTML网页文件。于是乎,我又开始的学习过程,如今的Internet时代,解决问题就是easy,很快就找到了eygle的博客(使用SQL*PLUS,构建完美excel或html输出http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html)。下面是学习过程中的实验部分。

二、实验
1.建立测试表

SAM@dzwj >  create table test1(id int,name varchar2(10),loc varchar2(30),hire_date date,email varchar2(20),department varchar2(20));
Table created.

SAM@dzwj > insert into test1 values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
1 row created.

SAM@dzwj > SAM@dzwj > commit;
Commit complete.

SAM@dzwj > select * from test1;

        ID NAME       LOC                            HIRE_DATE          EMAIL                DEPARTMENT
---------- ---------- ------------------------------ ------------------ -------------------- --------------------
         1 sam1       beijing                        28-NOV-17          sam1@oracle.com      it
         2 sam2       beijing                        28-NOV-17          sam2@oracle.com      it
         3 sam3       beijing                        28-NOV-17          sam3@oracle.com      it
         4 sam4       beijing                        28-NOV-17          sam4@oracle.com      it
         5 sam5       beijing                        28-NOV-17          sam5@oracle.com      it

2.编辑 main.sql

[oracle@testdb ~]$ cat main.sql
set linesize 200 pagesize 10000
set term off verify off feedback off 
set markup html on entmap on spool on preformat off
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
spool /home/oracle/test1.xls
@/home/oracle/get_tables.sql
spool off
exit

3.编辑 get_tables.sql

[oracle@testdb ~]$ cat get_tables.sql 
select * from test1;

4.编辑执行文件 collect.sh

[oracle@testdb ~]$ cat collect.sh 
#!/bin/bash
. /home/oracle/.bash_profile
DATE=`date +%Y%m%d`
sqlplus sam/oracle@dzwj @/home/oracle/main
mv /home/oracle/test1.xls /home/oracle/test1_${DATE}.xls

5.给collect.sh 执行权限

[oracle@testdb ~]$ chmod u+x collect.sh 

6.执行

[oracle@testdb ~]$ ./collect.sh 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

7.验证
将文件传回到本地机器打开,得到想要的excel文件

94012998-ae37-4e2f-be0b-fca4c74b962b

三、总结
生活在Internet时代真是件幸福的事,此次任务算是告一段落,但是当中还是碰到一些小problems,比如一开始没有加时间NLS_DATE_FORMAT变量的修改,导出的时间类型数据时没有时间,只有年月日。总而言之,多学习,多实践,没错的。向eygle大神致谢。  Where there is a will, there is a way.

发表在 Basic, oracle | 标签为 , | 留下评论

crontab 问题检查与处理

一、描述
       今天给一台linux机器配置了crontab ,希望每天0点与14点分别跑两个导入数据脚本,可是配置好后。检查时发现该脚本未成功运行,于是需要检查原因并处理。
二、故障排查
1.检查crond 服务
[root@testdb ~]# service crond status
crond (pid  31955) is running…
2.检查系统日志
查询/var/spool/mail/oracle日志发现报错信息 /home/oracle/impdp_xf.sh: line 2: impdp: command not found
这下找到未何失败的原因了,命令没找到,看来是缺少环境变量导致。
3.问题处理
在执行/home/oracle/impdp.sh脚本中增加 source /home/oracle/.bash_profile 后正常。
三、总结
       到处都会有小坑,DBA们要注意喽。Where there is a will, there is a way.
发表在 ERROR, system | 留下评论

利用修改sql_mode=NO_TABLE_OPTIONS收集建表语句,去掉engine和charset关键字

一、描述
平时我们有需求是要根据已有的表来生成建立表语句,直接查看生成过程中会有ENGINE和CHARSET两个关键字,其实我们大多数时候是不需要该参数,如果导出后手工修改脚本,实在是在麻烦了。在这里我们可以使用sql_mode参数,修改为NO_TABLE_OPTIONS来进行收集。当然我们注意点是要修改当前session的变量,而不是全局变量。下面是个小小的实验,演示一下这个过程。
二、实验
1.查看sql_mode变量当然值

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

2.查看t1表的建立语句,带有ENGINE与CHARSET两个参数

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.修改sql_mode为NO_TABLE_OPTIONS

mysql> set session sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| sql_mode      | NO_TABLE_OPTIONS |
+---------------+------------------+
1 row in set (0.00 sec)

4.再次查看建t1表SQL语句,没有ENGINE和CHARSET两个参数

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------+
| Table | Create Table                                                                      |
+-------+-----------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) |
+-------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.退出会话,使sql_mode参数恢复默认值
三、总结
一个小小sql_mode的参数调整,可以省去我们迁移过程中,大量修改脚本的操作。当然在MYSQL中sql_mode参数还有很对应的值,需要我们继续学习与研究。MYSQL真是有意思,继续学习中……

发表在 mysql | 标签为 , | 留下评论

Linux系统互信ssh的配置方法

Linux系统互信ssh的配置方法
一、ssh互信的介绍
ssh互信是两台机器(terminal-1和terminal-2)经过预先设置好认证的key文件,双方互相访问时,进行自动认证,无需再次输入密码,从而实现互信。
实现原理:
1.在要配置互信的机器(terminal-1和terminal-2)上生成各自经过认证的key文件。
2.将所有的key文件汇总到一个总的认证文件夹中。
3.将打包的key发给想要进行互信的机器(terminal-1和terminal-2)
4.互信验证

二、实验
1. 两台机器检查(sam 172.16.211.129 suzzy 172.16.211.130)
terminal-1:

[root@sam ~]# hostname
sam
terminal-2:
[root@suzzy ~]# hostname
suzzy

2. sam机器ssh到suzzy机器(需要输入正确密码才可以登录)
密码正确:

[root@sam ~]# ssh suzzy
The authenticity of host 'suzzy (172.16.211.130)' can't be established.
RSA key fingerprint is e0:4b:15:f3:fe:6c:2d:11:f7:ad:7e:a6:d6:65:0e:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'suzzy,172.16.211.130' (RSA) to the list of known hosts.
root@suzzy's password: 
Last login: Fri Oct 30 15:27:15 2015 from 172.16.211.1
[root@suzzy ~]# 

密码错误:

[root@sam ~]# ssh suzzy
root@suzzy's password: 
Permission denied, please try again.
root@suzzy's password: 
Permission denied, please try again.
root@suzzy's password: 
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
[root@sam ~]# 

3. 创建互信所用到的目录并修改权限(如果没有的话),如果你用过ssh登录过对方机器,该目录会自动创建,即便登录不成功。

[root@sam ~]# rm -rf .ssh
[root@sam ~]# mkdir .ssh
[root@sam ~]# chmod 755 .ssh
[root@sam ~]# ls -la
total 376
dr-xr-x---. 31 root root      4096 Oct 30 16:05 .
dr-xr-xr-x. 28 root root      4096 Aug 31 15:28 ..
drwxr-xr-x.  2 root root      4096 Nov 27  2014 .abrt
… ...
drwxr-xr-x   2 root root      4096 Oct 30 16:05 .ssh

4. 创建密钥(默认回车)
sam机器:

[root@sam ~]#  /usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
70:d2:c8:c6:01:6d:1c:2b:2e:8c:89:c0:ae:fc:14:2d root@sam
The key's randomart image is:
+--[ RSA 2048]----+
|    .+o.         |
|.    o+=         |
|..  ..O o        |
|++ ..o +         |
|+.oE..  S        |
|o  .o            |
|.. .             |
|  o              |
|   .             |
+-----------------+
[root@sam .ssh]# ll
total 8
-rw------- 1 root root 1675 Oct 30 17:42 id_rsa
-rw-r--r-- 1 root root  390 Oct 30 17:42 id_rsa.pub

[root@sam .ssh]#  /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
9e:12:19:4e:6a:d5:46:64:47:3d:f9:2a:11:e0:49:ad root@sam
The key's randomart image is:
+--[ DSA 1024]----+
|       .*+o. .   |
|       * oo +    |
|      + =. . o   |
|     = +E .   .  |
|    o + S  . .   |
|   .   o .. .    |
|      . o  .     |
|       .         |
|                 |
+-----------------+
[root@sam .ssh]# ll
total 16
-rw------- 1 root root  672 Oct 30 17:49 id_dsa
-rw-r--r-- 1 root root  598 Oct 30 17:49 id_dsa.pub
-rw------- 1 root root 1675 Oct 30 17:42 id_rsa
-rw-r--r-- 1 root root  390 Oct 30 17:42 id_rsa.pub

注:suzzy机器同上

[root@suzzy ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
d9:d1:27:75:5b:85:a2:af:77:75:83:74:d1:2a:02:35 root@suzzy
The key's randomart image is:
+--[ RSA 2048]----+
|         .E   ..*|
|        .  o...o+|
|         ...o..o.|
|         oo..+.. |
|        S .o..o  |
|            .. .o|
|           .   .o|
|          . . .  |
|           . .   |
+-----------------+
[root@suzzy ~]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
c4:94:b1:87:9a:34:1d:35:cb:51:03:12:f1:86:b7:fe root@suzzy
The key's randomart image is:
+--[ DSA 1024]----+
|        B*=oo    |
|       +.O + .   |
|      o B B      |
|     . = + .     |
|      o S .      |
|         .       |
|          .      |
|           .     |
|            E    |
+-----------------+

5. 将每个主机上的公共密钥文件id_rsa.pub和id_dsa.pub的内容复制到~/.ssh/authorized_keys文件中。并把这个文件分别放到所有机器中。注意,当您第一次使用ssh访问远程主机时,其RSA密钥是未知的,所以提示确认一下,确认完毕后SSH将记录远程主机的RSA密钥,以后连接该主机就不用密码了。

[root@sam .ssh]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[root@sam .ssh]# cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
[root@sam .ssh]# ssh root@suzzy cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
The authenticity of host 'suzzy (172.16.211.130)' can't be established.
RSA key fingerprint is e0:4b:15:f3:fe:6c:2d:11:f7:ad:7e:a6:d6:65:0e:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'suzzy,172.16.211.130' (RSA) to the list of known hosts.
root@suzzy's password: 
[root@sam .ssh]# ssh root@suzzy cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
root@suzzy's password: 
[root@sam .ssh]# 

6. 检查总密钥文件

[root@sam .ssh]# ls -l authorized_keys 
-rw-r--r-- 1 root root 1980 Oct 30 18:19 authorized_keys
[root@sam .ssh]# cat authorized_keys 
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAzxsnq3tyb50Z+uRGp0tFpMOSTqZpvOvcyrB0S2vbL0YdUl4oJg2xnwo6duteS5EHzoVKzRjSdfrDM5owKRVsWJnufAA/o6z0kiiYje6Cvfd5hlw/jgJtU1TVuzZsj+bwnCzWuSKfkkM/uhBvWk9UQe0GuTClUn4bxuXuFNGwWuDi020pwwNLdUbEtH93rhWFGskUrj9s2RLd3eDquT18TQzNGwwG0PrbedxyT57aVdbqKyLnxMDx/eOHKW4dWZQMIaUe/n73rjuGG43F6oRFk3R52bMSdOqYqljUSI5FmtBAAO1AyTALldg09rdg6PqTlYyQvLt1T9JVok6BLm9nHQ== root@sam
ssh-dss AAAAB3NzaC1kc3MAAACBAPR47OE9s4nnzVEpoH6/rPgJexROcNSwMPJeF9QSXpuIcbUNmLocrab8xQ33rfFkjUOAaf5PG9AJa3T0FReobaEA8uRui4NjJJ74/Bjs+Rm6Lg9FAeboIwBAuEyIG4VcoFXCf7bpplg6+hiRd4oNkuDA+GJdVMyBqWvborAIhuPJAAAAFQCnj9Ft5BQs5tzP6uoV0pBvx7lEAQAAAIEA15+u2GTGGiEMeeCCOwu4kQNjKUGHrZsMBepbOmaBImBeYJP3Q76dpSxjJpPjQ/bk61oVHnKjceuXwpmiND08BvjrGAtw15q2wodKdr0EnRjFQ/8MIWsfSgkBx1ngWDvAD+YqZI1V9imnUUEU+Xlq84PDxDKn45tZ2sauSkYWrhwAAACBAK3I2fkbaLwd/3gWcXkMoA7jm2euYB48DXm2NMYe0WddI/lN+LocRKEf9ZEwts48tw7G/lwcD+eP6jWPYNnSBr8xavjt3ZtVlGnuUlU4yuzUFvtBC8AnG7nyihEFM7lM+V2N0Tx/83K6gRXFGDoOpT4/xfEFxlVFsLdfuuodHhh3 root@sam
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA1H5kArvHN1jagQEUIwTHBEQXI0CHNgMJMZrnIlgNY2ssSFKfJdCdA8bfBtoIesfBSLfyQHFFqwh5CZqfXTlhL6JLlVL0anUnpEHX9v5B1vrNIfsQTXhfjXpyJOJNd1pWFweOQLq/fSvuoWvxQQESBloN8rUFs+eXvxMYi4y5rfQ+9MkJ6y+6HA1JB2KlHadzoA0vbZ4JxS/gcifhAzCv0goEw6ulNwHxdgx4Sp3EG+i8QxlSjV3BJ16FknaMRV8eMy8+pRibY6dWB+FW7sV5rQoT9/2PaqgUf0rMvCPzDE4aNpPYPXiU53dX+691iarmQo1Km26YHu7gDPsGbxa+lw== root@suzzy
ssh-dss AAAAB3NzaC1kc3MAAACBAI4e0Ul5sHev0cAc0uwTgIU9x7oNTQq0YYSdBySfJ1iqpeKM5B1nf4y8C6o+m8IOh5C6BH7Jx3oLyW0oBetlfGroU3WHVSC2D/lAY9CywvRqmLB96OFICJG7NMU48vfFTyYj7m4ARo/gFnhF6svY65tUkrZgf7vxX8F+PNH66YDdAAAAFQCVKW/d0cQ6HBwIZheTbZ+mwkDEYwAAAIAEG0tEw2CKvysYNglifESkHmNw8DqgFvZ0azXTkr8OVxfNKB/h9lzV7U+IyIMMcsSfZaukGVntTtg0RVFPTMq/5rhUrupUWfRNgm0vgTGS2v5JPc5xYdoqZXQS8EIFvndkDyqGU233aievALTITY6bCyt4Nks95obUrSDl4T5ZnAAAAIAv5IVdJ8l2XKNdWMCSJXPhzepDtuzXbx5hKMRoNtoi+Qz8s/uAn3wEJC4qB7zjTnZQcfOdoV0R0JegvI46GO1D3sQhtUy76I2DlwXr0HjrOd/+UXQzfXf3rY3/B4rCTuGjwbbuAZeJVS+joV+MkeaiFrXoXisXjFDOoUiAIX1amw== root@suzzy

7. 将总密钥文件传到其他机器对应目录

[root@sam .ssh]# scp authorized_keys root@suzzy:~/.ssh/
root@suzzy's password: 
authorized_keys                                            100% 1980     1.9KB/s   00:00 

8. 测试连接(首次还是需要YES下,第二次便可以不需要)

[root@sam ~]# ssh suzzy
Last login: Fri Oct 30 18:25:38 2015 from sam
[root@suzzy ~]# ssh sam
Last login: Fri Oct 30 18:26:34 2015 from suzzy
[root@sam ~]# 

9. 将authorized_keys文件权限变更为600,以便安全,每台都需要更改

[root@sam ~]# cd .ssh
[root@sam .ssh]# ls -l authorized_keys 
-rw-r--r-- 1 root root 1980 Oct 30 18:19 authorized_keys
[root@sam .ssh]# chmod 600 authorized_keys 
[root@sam .ssh]# ls -l authorized_keys 
-rw------- 1 root root 1980 Oct 30 18:19 authorized_keys

三、总结
这个互信操作在Oracle 10g配置RAC(real application cluster)前是需要手工来操作的,从11G安装开始,可以在图形界面按钮式配置,相当容易,但我们还是应该掌握该技巧,在需要免密登录时还是要通过手工配置。看家的本领可不能丢。

 

发表在 oracle, RAC, system | 标签为 , | 留下评论