Experience – 第2页 – Oracle and Linux

遇到ORA-600 [kmgs_parameter_update_timeout_1] [1565]的错误续

7月 27,2012 留下评论 Experience, Oracle, RAC

刚发完上篇文章之后，又从老杨的一篇文章“http://blog.itpub.net/post/468/450451?SelectActiveLayout=a”中看到一些有所帮助的信息，而这个案例中在alert里看到，在这个600的错误抛出之前一条错误信息中看到：

Wed Jul 25 09:56:46  2012
Thread 1 advanced to log sequence 580 (LGWR switch)
  Current log# 1 seq# 580 mem# 0: +DATA/zhfr8db/onlinelog/group_1.271.783424599
  Current log# 1 seq# 580 mem# 1: +FLASH/zhfr8db/onlinelog/group_1.256.783424601
Wed Jul 25 10:49:39  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)

的错误信息。

从后续的alert日志里，尝试关闭数据库服务器的时候抛出的错误中也有类似错误：

Wed Jul 25 10:55:03  2012
Trace dumping is performing id=[cdmp_20120725105503]
Wed Jul 25 10:56:06  2012
Restarting dead background process MMON
MMON started with pid=33, OS id=6756
Wed Jul 25 12:46:20  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_5552.trc
Wed Jul 25 12:47:03  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_2008.trc
Wed Jul 25 12:50:01  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)

再从c:\oracle\product\10.2.0\admin\zhfr8db\udump\zhfr8db1_ora_5552.trc文件跟踪看到：

*** 2012-07-25 12:46:20.268
*** CLIENT ID:() 2012-07-25 12:46:20.268
      ----------------------------------------
      SO: 000000047111DEF0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
      (process) Oracle pid=31, calls cur/top: 000000047E15DC20/000000047E15DC20, flag: (6) SYSTEM
                int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 33
              last post received-location: ksrpublish
              last process to post me: 7e11e6f8 1 6
              last post sent: 849 0 4
              last post sent-location: kslpsr
              last process posted by me: 7312aef8 1 6
        (latch info) wait_event=0 bits=0
        Process Group: DEFAULT, pseudo proc: 00000004731384B8
        O/S info: user: SYSTEM, term: DATACENTER01, ospid: 4828 
        OSD pid info: Windows thread id: 4828, image: ORACLE.EXE (ASMB)
        Short stack dump: 
ksdxfstk+42<-ksdxcb+1630<-ssthreadsrgruncallback+589<-OracleOradebugThreadStart+975<-0000000077D6B71A
<-0000000077EF047A<-0000000077DA79F3<-0000000008653328<-000000000865190C<-0000000005F564A9
<-0000000005F0CF64<-0000000005EE5D88<-0000000005EE57F9<-0000000005EA5ECB<-ttcdrv+14881
<-0000000005EAAA6D<-xupirtrc+1335<-xupirtr+216<-upirtr+23<-kpurcs+45
<-OCIKDispatch+32<-kfnOpExecute+146<-kfnbRun+1062<-ksbrdp+988<-opirip+700
<-opidrv+856<-sou2o+52<-opimai_real+268<-opimai+96<-BackgroundThreadStart+637<-0000000077D6B71A
        ----------------------------------------
        SO: 000000047114E1A0, type: 4, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (session) sid: 189 trans: 0000000000000000, creator: 000000047111DEF0, flag: (51) USR/- BSY/-/-/-/-/-
                  DID: 0001-001F-00000003, short-term DID: 0000-0000-00000000
                  txn branch: 0000000000000000
                  oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 0/SYS
        service name: SYS$BACKGROUND
        waiting for 'ASM background timer' wait_time=0, seconds since wait started=420165
                    =0, =0, =0
                    blocking sess=0x0000000000000000 seq=31
        Dumping Session Wait History
         for 'ASM background timer' count=1 wait_time=4.999949 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999893 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000022 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999948 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999924 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000012 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999948 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999858 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=4.999991 sec
                    =0, =0, =0
         for 'ASM background timer' count=1 wait_time=5.000000 sec
                    =0, =0, =0
        Sampled Session History of session 189 serial 1
        ---------------------------------------------------
        The sampled session history is constructed by sampling
        the target session every 1 second. The sampling process
        captures at each sample if the session is in a non-idle wait,
        an idle wait, or not in a wait. If the session is in a
        non-idle wait then one interval is shown for all the samples
        the session was in the same non-idle wait. If the
        session is in an idle wait or not in a wait for
        consecutive samples then one interval is shown for all
        the consecutive samples. Though we display these consecutive
        samples  in a single interval the session may NOT be continuously
        idle or not in a wait (the sampling process does not know).
 
        The history is displayed in reverse chronological order.
 
        sample interval: 1 sec, max history 120 sec
        ---------------------------------------------------
          [121 samples,                                    12:44:20 - 12:46:20]
            idle wait at each sample
        temporary object counter: 0
          KTU Session Commit Cache Dump for IDLs: 
          KTU Session Commit Cache Dump for Non-IDLs: 
          ----------------------------------------
          UOL used : 0 locks(used=0, free=0)
          KGX Atomic Operation Log 000000047AECC840
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Cursor Pin uid 189 efd 3 whr 11 slp 0
          KGX Atomic Operation Log 000000047AECC888
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Library Cache uid 189 efd 0 whr 0 slp 0
          KGX Atomic Operation Log 000000047AECC8D0
           Mutex 0000000000000000(0, 0) idn 0 oper NONE
           Library Cache uid 189 efd 0 whr 0 slp 0
          ----------------------------------------
          SO: 000000045A233D80, type: 41, owner: 000000047114E1A0, flag: INIT/-/-/0x00
          (dummy) nxc=0, nlb=0   
        ----------------------------------------
        SO: 0000000472172A40, type: 11, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 000000047111DEF0,
                           event: 31, last message event: 31,
                           last message waited event: 31,                            next message: 0000000476225BC8(0), messages read: 0
                           channel: (00000004711640E0) system events broadcast channel
                                    scope: 2, event: 30690, last mesage event: 7413,
                                    publishers/subscribers: 1/45,
                                    messages published: 3
                                    oldest msg (?): 0000000476225BB8 id: 1 pub: 000000047E11F768
                                    heuristic msg queue length: 3
        ----------------------------------------
        SO: 0000000472270FA0, type: 19, owner: 000000047111DEF0, flag: INIT/-/-/0x00
         GES MSG BUFFERS: st=emp chunk=0x0000000000000000 hdr=0x0000000000000000 lnk=0x0000000000000000 flags=0x0 inc=0
          outq=0 sndq=0 opid=0 prmb=0x0 
          mbg[i]=(0 0) mbg[b]=(0 0) mbg[r]=(0 0)
          fmq[i]=(0 0) fmq[b]=(0 0) fmq[r]=(0 0)
          mop[s]=0 mop[q]=0 pendq=0 zmbq=0
          nonksxp_recvs=0
        ------------process 0x0000000472270FA0--------------------
        proc version      : 0
        Local node        : 0
        pid               : 4828
        lkp_node          : 0
        svr_mode          : 0
        proc state        : KJP_FROZEN
        Last drm hb acked : 0
        Total accesses    : 3
        Imm.  accesses    : 0
        Locks on ASTQ     : 0
        Locks Pending AST : 0
        Granted locks     : 0
        AST_Q: 
        PENDING_Q: 
        GRANTED_Q: 
        ----------------------------------------
        SO: 000000047E15DC20, type: 3, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (call) sess: cur 7114e1a0, rec 0, usr 7114e1a0; depth: 0
          ----------------------------------------
          SO: 000000045ED23770, type: 84, owner: 000000047E15DC20, flag: INIT/-/-/0x00
          (kfgso) flags: 00000000 clt: 3 err: 0 hint: 0
          (kfgpn) rpi: 1 itrn:0000000000000000 gst:0000000000000000 usrp:0000000000000000
          busy: 0 rep: 0 grp: 5d60b840 check: 0/0 glink: 5d60b888 5d60b888
            kfgrp:  number: 0/0 type: 0 compat: 0.0.0.0.0 dbcompat:0.0.0.0.0
            timestamp: 0 state: 0 flags: 2 gpnlist: 5ed237f0 5ed237f0
            KFGPN at 5ed23770 in dependent chain
        ----------------------------------------
        SO: 000000045AEEDD48, type: 16, owner: 000000047111DEF0, flag: INIT/-/-/0x00
        (osp req holder)
PSO child state object changes :
Dump of memory from 0x0000000474167DC0 to 0x0000000474167FC8
474167DC0 00000005 00000000 5AEEDD48 00000004  [........H..Z....]
474167DD0 00000010 000313F4 7E15DC20 00000004  [........ ..~....]
474167DE0 00000003 000313F4 72270FA0 00000004  [..........'r....]
474167DF0 00000013 000312CB 72172A40 00000004  [........@*.r....]
474167E00 0000000B 000313F4 7114E1A0 00000004  [...........q....]
474167E10 00000004 000312CB 00000000 00000000  [................]
474167E20 00000000 00000000 00000000 00000000  [................]
        Repeat 25 times
474167FC0 00000000 00000000                    [........]        
*** 2012-07-25 12:46:37.393
*** CLIENT ID:() 2012-07-25 12:46:37.393
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 320 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 288 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1 
*** 2012-07-25 12:47:20.314
*** CLIENT ID:() 2012-07-25 12:47:20.314
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1

难道问题是：

WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 320 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 288 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1 
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1

导致的，这些WARNING又说明什么呢？

遇到ORA-600 [kmgs_parameter_update_timeout_1] [1565]的错误

7月 27,2012 留下评论 Experience, Oracle, RAC

今天上午，在一套Windows 2003 64位的双节点10.2.0.5.0的64位RAC数据库上，遇到一则600的错误，ORA-00600: 内部错误代码, 参数: [kmgs_parameter_update_timeout_1], [1565], [], [], [], [], [], []。从(节点1的)alert日志里获取的具体错误信息如下：

Wed Jul 25 10:49:39  2012
Unexpected communication failure with ASM instance:
 error 21561 (ORA-21561: 生成 OID 失败
)
NOTE: ASMB process state dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc
System State dumped to trace file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc
Wed Jul 25 10:55:02  2012
Errors in file c:\oracle\product\10.2.0\admin\zhfr8db\bdump\zhfr8db1_mmon_4624.trc:
ORA-00600: 内部错误代码, 参数: [kmgs_parameter_update_timeout_1], [1565], [], [], [], [], [], []
ORA-01565: 标识文件 '+DATA/zhfr8db/spfilezhfr8db.ora' 时出错
ORA-17503: ksfdopn: 2 未能打开文件 +DATA/zhfr8db/spfilezhfr8db.ora
ORA-21561: 生成 OID 失败

Wed Jul 25 10:55:03  2012
Trace dumping is performing id=[cdmp_20120725105503]
Wed Jul 25 10:56:06  2012
Restarting dead background process MMON
MMON started with pid=33, OS id=6756

当时的表现情况是，客户端无法通过应用系统访问数据库，客户端通过tnsping service_name的返回结果也是，有时通，返回10毫秒，而有时挂死了。

登录到其中的一个节点服务器(节点1)上，执行lsnrctl status，查看监听状态也挂死，而到另外一个节点(节点2)上，执行lsnrctl status一切正常。并且，分别在两个节点上，通过SQL*PLUS连接数据库均正常，执行crs_stat -t返回的结果也都正常。

接下来，分别在两个节点上作了一个AWR报告，分析了AWR发现并没有发现数据库的异常现象。

从告警日志里看到跟SPFILE相关的错误，于是在SQL*PLUS里尝试create pfile from spfile,以及使用RMAN工具backup spfile时，都报错了，当时匆忙，具体错误号没有记录下来。

没辙了，就到节点1上，尝试通过srvctl stop database -d db_unique_name关闭数据库时，挂死了，丝毫没有反应；通过SQL*PLUS在节点1上，尝试shutdown immediate来手工关闭实例，也未果。

当时，从网络上找到老杨以及惜分飞的文章，不过，貌似都和我遇到的这个情况不太类似。

然后，在没有更好的办法的前提下，直接重启节点1的Windows服务器，毕竟当时应用已经不可用了，再者是RAC架构，最起码还留着一个实例。重启之后，数据库一切正常了。

最后，从MetaLink上找到这样的一篇文章：ORA-600 [kmgs_parameter_update_timeout_1], [1565] While Accessing Spfile Managed By ASM [ID 553915.1]该文档描述，该错误影响到10.2及以上版本的数据库，原因是：

This is due to unpublished bug 5399699 where ORA-600 [kmgs_parameter_update_timeout_1] or similar errors can occur in MMON when ASM is being used.

In 10g MMON manages all memory re-size activity by modifying related parameters. If MMON is not running DBW0 will handle this task. The parameter update activity is triggered by a timeout. Basically this error indicates that the MMON process is not able to write to the SPFILE to store some settings required for dynamic SGA parameter adjustments.

也就是说：这个错误是oracle还未发布的一个bug,bug号是5399699。这个错误是说在10g的数据库里，MMON进程动态的管理内存，MMON（Memory Monitor）是10g数据库的新进程。从10g开始，数据库支持自动调整SGA内存，当需要调整（动态增大或减小）的时候，MMON进程会自动完成，MMON会把这个改变的信息，写入到SPFILE里。

结合最开始的alert日志文件的内容，是由于当时MMON进程无法把这个信息写入到SPFILE里导致的，导致后来MMON后台进程僵死了，在Wed Jul 25 10:56:06 2012的时候，MMON进程又被重启了。然后数据库一直处于“假死”的状态。

Oracle Metalink上这篇文章给的解决方案：

Solution

1. Upgrade to the 10.2.0.4.4 PSU Patch:9352164 or higher where this bug is fixed.

2. Check if Patch:5399699 is available for your RDBMS release and platform.

3. Use one of the following workarounds:

Relocate the spfile either to some other diskgroup on which the archive logs are not being managed.
Move the spfile to the file system

显然，第1个与当前环境下的数据库版本不一致；而第2个方案中，又没有查到Windows 2003 X64平台下的补丁；第3个方案，我当前是RAC的库，如果把SPFILE迁移到文件系统上的话，又不太合适，除非给每个实例单独配置PFILE。

最后，这个问题，对于我这个Oracle菜鸟而言，依然头痛，没有更好的解决方案，如果大家有遇到过类似的案例的话，请不吝赐教！

解决一则row cache lock引起的性能故障

6月 5,2012 有2条评论 Experience, Oracle, Performance Tuning

收到紧急邮件：说某客户生产系统在今天早上9:30左右，生产环境整个系统操作响应很慢，查询一票单子可能要耗时半分钟，有时候登录界面也卡住很久才响应。

这是一套10.2.0.5.0的双节点RAC数据库，平时系统运行较为正常，现在突然变慢。好了，对于我这个优化菜鸟来讲，还是从AWR报告入手。下面给出分析步骤和解决办法：

1 从报告头中看到，在数据库出现性能问题的一个小时内，DB Time达到240分钟，(DB Time)/Elapsed=3.93，说明数据库应该是存在问题的。

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	10638	05-6月 -12 09:00:00	54	5.8
End Snap:	10639	05-6月 -12 10:01:03	102	5.8
Elapsed:		61.04 (mins)
DB Time:		240.11 (mins)

2 从报告的Load Profile节，看到用户的每秒调用高达680次，说明肯定存在问题了。

Load Profile

	Per Second	Per Transaction
Redo size:	12,939.46	6,115.60
Logical reads:	67,323.18	31,819.06
Block changes:	53.22	25.15
Physical reads:	1.02	0.48
Physical writes:	4.72	2.23
User calls:	679.70	321.25
Parses:	90.49	42.77
Hard parses:	0.35	0.16
Sorts:	1.94	0.92
Logons:	0.08	0.04
Executes:	316.75	149.71
Transactions:	2.12

3 继续分析报告，从Top 5中看到排在第一的等待事件是row cache lock,并且该等待事件的平均等待达到2128ms。

ROW CACHE LOCK等待事件是一个共享池相关的等待事件，是由于对于字典缓冲的访问造成的。通常直接的解决办可以通过调大共享池来解决，但是，并非在所有场景下都凑效。

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
row cache lock	2,736	5,822	2,128	40.4	Concurrency
CPU time		4,305		29.9
gc cr block busy	2,293	2,633	1,148	18.3	Cluster
gc buffer busy	1,569	1,096	698	7.6	Cluster
enq: TX – row lock contention	2,029	998	492	6.9	Application

4 继续分析，发现基于时间的统计信息中，加载序列sequence的耗时排在了第二位。

Time Model Statistics

Total time in database user-calls (DB Time): 14406.5s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name

Statistic Name	Time (s)	% of DB Time
sql execute elapsed time	14,188.01	98.48
sequence load elapsed time	6,900.83	47.90
DB CPU	4,304.59	29.88
PL/SQL execution elapsed time	20.64	0.14
parse time elapsed	10.25	0.07
hard parse elapsed time	6.00	0.04
PL/SQL compilation elapsed time	1.17	0.01
hard parse (sharing criteria) elapsed time	1.07	0.01
repeated bind elapsed time	0.80	0.01
hard parse (bind mismatch) elapsed time	0.64	0.00
connection management call elapsed time	0.31	0.00
failed parse elapsed time	0.00	0.00
DB time	14,406.50
background elapsed time	2,115.75
background cpu time	20.52

5 看到最耗时的竟然是一条再简单不过的SQL语句，SELECT SEQ_NEWID.NEXTVAL FROM DUAL，取序列的值，竟然会如此的耗时？

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Total DB Time (s): 14,406
Captured SQL account for 223.8% of Total

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
6,910	0	281	24.59	47.96	1gd7ancd2px8m	FC.EdiService.Import.exe	SELECT SEQ_NEWID.NEXTVAL FROM …

6 再看字典缓冲区的统计信息：取序列值一共287次，就失败了43.9%，看来的确是取序列值的地方出现问题，也就解释了为什么上一步骤中的分许出的那条SQL会如此耗时，因为差不多有一半的情况下都没有取到序列的值。

Dictionary Cache Stats

"Pct Misses" should be very low (< 2% in most cases)
"Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	65	1.54	1	1
dc_database_links	304	0.00	0	1
dc_global_oids	155	0.00	0	24
dc_histogram_data	74,704	0.25	0	5,612
dc_histogram_defs	71,400	0.26	0	4,945
dc_object_ids	29,398	0.01	0	1,136
dc_objects	3,912	0.23	0	860
dc_profiles	150	0.00	0	1
dc_rollback_segments	17,789	0.10	0	56
dc_segments	8,927	0.06	4	896
dc_sequences	287	43.90	279	3
dc_tablespace_quotas	2	50.00	0	2
dc_tablespaces	8,954	0.00	0	25
dc_usernames	1,082	0.00	0	8
dc_users	13,991	0.00	0	31
outstanding_alerts	326	77.91	23	54

7 到此，解决问题的基本思路已经出来了，通过将序列缓存到内存中，基本可以解决问题。通过查看生产系统上的该序列的信息，发现创建序列的语句如下：

-- Create sequence 
create sequence SEQ_NEWID
minvalue 1000
maxvalue 9999
start with 1000
increment by 1
nocache;

8 调整序列，使之cache到内存中，alter sequence SEQ_NEWID cache 3000;

9 附带，刚在生产系统上把序列cache到内存，另一同事就把RAC数据库给重启了，据他判断说是存储系统出了问题。可是，怎么会是存储出了问题呢？如果，能重现这次的性能问题该多好啊，只是没有如果！

在处理这个问题的时候，参照了老白的文章：白鳝说WAIT EVENT之ROW CACHE LOCK

如何获取Oracle数据库中某表及索引、约束、触发器、对象权限的创建语句？

4月 10,2012 留下评论 Experience, Oracle, Scripts

在上一篇文章中，我们知道了如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？那么对于数据库中特定的某张表而言，又该如何获取建表语句、及索引、约束、外键约束、触发器、对象权限的创建脚本呢？

在这里，给出一个实现上述功能的脚本：

OCM11g-> cat table_ddl.sql 
-- Be sure the variable 'S' means the Schema name,the 'T' means table name.
-- All these variables should be UPPERCASE.

def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
 
spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;
 
-- Uncomment to generate object level grants
 select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;
 
spool off;
OCM11g->

下述是对于HR用户下的EMPLOYEES表的输出结果：

SQL> conn hr/hR
Connected.
SQL> @table_ddl.sql
Enter value for 1: HR
Enter value for 2: EMPLOYEES

  CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "COMMISSION_PCT" NUMBER(2,2),
        "MANAGER_ID" NUMBER(6,0),
        "DEPARTMENT_ID" NUMBER(4,0),
         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE,
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE,
         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  TABLESPACE "EXAMPLE" ;



  CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;



  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE;



  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE;



  CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES"
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE;

  CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY"
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
/
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;



  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";


SQL>

Oracle 10g RAC 配置物理dataguard系列2：备库安装、升级oracle软件、配置ASM实例

2月 14,2012 有2条评论 Experience, Oracle

在上一篇文章Oracle 10g RAC 配置物理dataguard系列1：RAC主库信息概览、备库准备工作中，我们了解了双节点RAC主库的基本信息，并且成功创建了一台将来用作物理备库的机器。在本篇中，我们将完成以下任务：

在物理备库机器上安装oracle 10g 数据库软件；
在物理备库机器上升级oracle 10g 软件到10.2.0.5.0；
在物理备库机器上配置ASM存储，创建ASM实例；

一安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

1 创建oracle用户、用户组，参照以下shell脚本完成：

[root@ora10grac-dg Pre10g]# pwd
/root/Pre10g
[root@ora10grac-dg Pre10g]# ll
total 24
-rw-r--r-- 1 root root 2165 Feb 14 14:49 1preuser.sh
-rw-r--r-- 1 root root  510 Feb 14 14:47 2predir.sh
-rw-r--r-- 1 root root  801 Feb 14 14:47 3prelimits.sh
-rw-r--r-- 1 root root  541 Feb 14 14:47 4prelogin.sh
-rw-r--r-- 1 root root  652 Feb 14 14:47 5preprofile.sh
-rw-r--r-- 1 root root 2300 Feb 14 14:47 6presysctl.sh
[root@ora10grac-dg Pre10g]# chmod u+x *.sh
[root@ora10grac-dg Pre10g]# ll
total 24
-rwxr--r-- 1 root root 2165 Feb 14 14:49 1preuser.sh
-rwxr--r-- 1 root root  510 Feb 14 14:47 2predir.sh
-rwxr--r-- 1 root root  801 Feb 14 14:47 3prelimits.sh
-rwxr--r-- 1 root root  541 Feb 14 14:47 4prelogin.sh
-rwxr--r-- 1 root root  652 Feb 14 14:47 5preprofile.sh
-rwxr--r-- 1 root root 2300 Feb 14 14:47 6presysctl.sh
[root@ora10grac-dg Pre10g]# cat 1preuser.sh 
#!/bin/bash
#Purpose:Create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment
#variable for oracle user.
# 
#Usage:Log on as the superuser('root'),and then execute the command:#./1preuser.sh
#Author:Asher Huang

echo "Now create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment"
groupadd oinstall 
groupadd dba
useradd -g oinstall -G dba -c "Oracle software owner" -d  /home/oracle   oracle 
echo "oracle" | passwd --stdin oracle
echo "export ORACLE_BASE=/u01/app" >> /home/oracle/.bash_profile
echo 'export ORACLE_HOME=$ORACLE_BASE/oracle' >> /home/oracle/.bash_profile
echo "export ORACLE_SID=pridb"  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/usr/X11R6/lib'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386/server'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/rdbms/lib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/rdbms/jlib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/network/jlib'  >> /home/oracle/.bash_profile
echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/oracle/.bash_profile
echo "export NLS_LANG=american_america.AL32UTF8" >> /home/oracle/.bash_profile
echo "export ORACLE_TERM=xterm" >> /home/oracle/.bash_profile
echo "export EDITOR=vi" >> /home/oracle/.bash_profile
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> /home/oracle/.bash_profile
echo "export LANG=en_US" >> /home/oracle/.bash_profile
echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/oracle/.bash_profile

echo "The two groups named 'oinstall' and 'dba' plus the oracle user has been created."
echo "The Environment variable for oracle user also has been setted sussessfully."
[root@ora10grac-dg Pre10g]#

2 创建相关目录，其中/u01/app用做Oracle Base 目录，/u01/app/oracle用作Oracle Home目录，具体参照下述脚本：

[root@ora10grac-dg Pre10g]# cat 2predir.sh 
#!/bin/bash
#Purpose:Create the necessary directory for oracle user and change the authention to oracle user.
#Usage:Log on as the superuser('root'),and then execute the command:#./2predir.sh
#Author:Asher Huang

echo "Now create the necessary directory for oracle user and change the authention to oracle user..."
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01
chown oracle:oinstall /home/oracle
echo "The necessary directory for oracle user and change the authention to oracle user has been finished"[root@ora10grac-dg Pre10g]#

3 配置oracle用户的SHELL限制，我们通过下述脚本来修改/etc/security/limits.conf配置文件：

[root@ora10grac-dg Pre10g]# cat 3prelimits.sh 
#!/bin/bash
#Purpose:Change the /etc/security/limits.conf. 
#        "Configure the SHELL limits on oracle user."
#        配置oracle用户的SHELL限制
#  nofile ----可打开的文件描述符的最大数
#  nproc  ----单个用户可用的最大进程数量
#Usage:Log on as the superuser('root'),and then execute the command:#./3prelimits.sh
#Author:Asher Huang


echo "Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before"
cp /etc/security/limits.conf /etc/security/limits.conf.bak
echo "oracle soft nproc 2047" >>/etc/security/limits.conf
echo "oracle hard nproc 16384" >>/etc/security/limits.conf
echo "oracle soft nofile 1024" >>/etc/security/limits.conf
echo "oracle hard nofile 65536" >>/etc/security/limits.conf
echo "Modifing the /etc/security/limits.conf has been succeed."[root@ora10grac-dg Pre10g]#

4 配置SHELL对oracle用户的限制生效，需要修改/etc/pam.d/login文件，脚本如下：

[root@ora10grac-dg Pre10g]# cat 4prelogin.sh 
#!/bin/bash
#Purpose:Modify the /etc/pam.d/login.
# 为使第3步骤中配置SHELL对oracle用户的限制生效，需要修改/etc/pam.d/login文件.
#Usage:Log on as the superuser('root'),and then execute the command:#./4prelimits.sh
#Author:Asher Huang

echo "Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak"
cp /etc/pam.d/login /etc/pam.d/login.bak
echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login
echo "session required pam_limits.so" >>/etc/pam.d/login
echo "Modifing the /etc/pam.d/login has been succeed."

5 配置系统相关的环境变量，修改/etc/profile,脚本：

[root@ora10grac-dg Pre10g]# cat 5preprofile.sh 
#!/bin/bash
#Purpose:Modify the /etc/profile.配置系统相关的环境变量
#Usage:Log on as the superuser('root'),and then execute the command:#./5preprofile.sh
#Author:Asher Huang

echo "Now modify the  /etc/profile,but with a backup named  /etc/profile.bak"
cp /etc/profile /etc/profile.bak
echo 'if [ $USER = "oracle" ]; then' >>  /etc/profile
echo 'if [ $SHELL = "/bin/ksh" ]; then' >> /etc/profile
echo 'ulimit -p 16384' >> /etc/profile
echo 'ulimit -n 65536' >> /etc/profile
echo 'else' >> /etc/profile
echo 'ulimit -u 16384 -n 65536' >> /etc/profile
echo 'fi' >> /etc/profile
echo 'fi' >> /etc/profile
echo "Modifing the /etc/profile has been succeed."

6 修改系统内核参数，修改/etc/sysctl.conf配置文件，脚本：

[root@ora10grac-dg Pre10g]# cat 6presysctl.sh 
#!/bin/bash
#Purpose:Modify the /etc/sysctl.conf.
# 修改系统内核参数，若配置有误，在安装时有先决条件检查，即会报错!!!
#Usage:Log on as the superuser('root'),and then execute the command:#./6presysctl.sh
#Author:Asher Huang

echo "Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak"
cp /etc/sysctl.conf /etc/sysctl.conf.bak
echo "fs.file-max = 65536" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf

echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.shmmax = 2147483648" >> /etc/sysctl.conf

echo "net.core.rmem_default=1048576" >> /etc/sysctl.conf
echo "net.core.rmem_max=262144" >> /etc/sysctl.conf
echo "net.core.wmem_default=262144" >> /etc/sysctl.conf
echo "net.core.wmem_max=262144" >> /etc/sysctl.conf

echo "net.ipv4.ip_local_port_range = 1024 65000" >> /etc/sysctl.conf
echo "Modifing the /etc/sysctl.conf has been succeed."
echo "Now make the changes take effect....."

## fs.file-max = 65536 表示文件句柄的最大数量。文件句柄设置表示在linux系统中可以打开的文件数量 

## kernel.sem = 250 32000 100 128  sem 4个参数依次是
##SEMMSL:每个用户拥有信号量最大数;
##SEMMNS:系统信号量最大数;
##SEMOPM:每次semopm系统调用操作数;
##SEMMNI:系统信号量集数最大数。这4个参数为固定内容大小。

## kernel.shmall = 2097152 该参数表示系统一次可以使用的共享内存总量（以页为单位，）。缺省值就是2097152，通常不需要修改。
## 可用共享内存的总量，单位页，在32位系统上一页=4k，也就是4096字节。按照这个设置2097152*4k/1024/1024 = 8G， 就是说可用共享内存一共 8G
## kernel.shmmax = 2147483648 该参数定义了共享内存段的最大尺寸（以字节为单位）。缺省为32M，对于oracle来说，该缺省值太低了，通常将其设置为2G
## 单个共享内存段的最大数，2147483648/1024/1024=2048M=2G
## kernel.shmmni = 4096 这个内核参数用于设置系统范围内单个共享内存段的最小值。该参数的默认值是 4096 。通常不需要更改。


## 设置UDP send/receive buffers high enough ,通常在RAC环境下，私网最好是千兆网卡，协议为UDP。
## net.core.rmem_default=262144   //  默认的接收窗口大小 
## net.core.rmem_max=262144       //  接收窗口的最大大小 
## net.core.wmem_default=262144   //  默认的发送窗口大小 
## net.core.wmem_max=262144       //  发送窗口的最大大小


## net.ipv4.ip_local_port_range = 1024 65000 //表示端口范围


#使配置生效，执行下述命令：
sysctl -p

7 将上述脚本编写无误后，以root用户依次执行：

[root@ora10grac-dg Pre10g]# ./1preuser.sh 
Now create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment
Changing password for user oracle.
passwd: all authentication tokens updated successfully.
The two groups named 'oinstall' and 'dba' plus the oracle user has been created.
The Environment variable for oracle user also has been setted sussessfully.
[root@ora10grac-dg Pre10g]# ./2predir.sh 
Now create the necessary directory for oracle user and change the authention to oracle user...
The necessary directory for oracle user and change the authention to oracle user has been finished
[root@ora10grac-dg Pre10g]# ./3prelimits.sh 
Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before
Modifing the /etc/security/limits.conf has been succeed.
[root@ora10grac-dg Pre10g]# ./4prelogin.sh 
Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak
Modifing the /etc/pam.d/login has been succeed.
[root@ora10grac-dg Pre10g]# ./5preprofile.sh 
Now modify the  /etc/profile,but with a backup named  /etc/profile.bak
Modifing the /etc/profile has been succeed.
[root@ora10grac-dg Pre10g]# ./6presysctl.sh 
Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak
Modifing the /etc/sysctl.conf has been succeed.
Now make the changes take effect.....
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 65536
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default = 1048576
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.ip_local_port_range = 1024 65000
[root@ora10grac-dg Pre10g]#

8 以oracle用户登录系统，并确认环境变量生效：

[root@ora10grac-dg Pre10g]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)
[root@ora10grac-dg Pre10g]# su - oracle
[oracle@ora10grac-dg ~]$ pwd
/home/oracle
[oracle@ora10grac-dg ~]$ env | grep ORA
ORACLE_SID=pridb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@ora10grac-dg ~]$

9 挂载oracle软件安装介质，并以oracle用户登录图形界面，开始执行软件安装：

① 挂载安装介质，这里oracle 10g软件的安装包、升级包、集群件已经刻录好成一个ISO文件，所以只需挂载光盘：

[root@ora10grac-dg ~]# mount /dev/cdrom /mnt/
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@ora10grac-dg ~]# cd /mnt/
[root@ora10grac-dg mnt]# ll
total 6
dr-xr-xr-x 1 root root 2048 Jul 27  2010 10201_clusterware_linux_x86_64
dr-xr-xr-x 1 root root 2048 Jul 27  2010 10201_database_linux_x86_64
dr-xr-xr-x 1 root root 2048 Jul 27  2010 p8202632_10205_Linux-x86-64
[root@ora10grac-dg mnt]# cd 10201_database_linux_x86_64/ && ll
total 25
dr-xr-xr-x 1 root root  2048 Jul 27  2010 doc
dr-xr-xr-x 1 root root  2048 Jul 27  2010 install
dr-xr-xr-x 1 root root  2048 Jul 27  2010 response
-r-xr-xr-x 1 root root  1326 Oct 23  2005 runInstaller
dr-xr-xr-x 1 root root 12288 Jul 27  2010 stage
-r-xr-xr-x 1 root root  5416 Oct 20  2005 welcome.html
[root@ora10grac-dg 10201_database_linux_x86_64]#

② Oracle用户登录图形界面，执行安装：

③ 发现报错，原因是Oracle 10g 软件不支持Red Hat 5版本的操作系统，Oracle 10g在2004年就发布了，而Red Hat 5则在2007年发布。修改/etc/redhat-release文件，使之骗过Oracle：

[root@ora10grac-dg ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
[root@ora10grac-dg ~]# cp /etc/redhat-release /etc/redhat-release.bak
[root@ora10grac-dg ~]# vi /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
[root@ora10grac-dg ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
[root@ora10grac-dg ~]#

④ 修改之后，重新执行OUI图形界面安装，发现这次报错如下：

[oracle@ora10grac-dg ~]$ /mnt/10201_database_linux_x86_64/runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-14_03-25-08PM. Please wait ...[oracle@ora10grac-dg ~]$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2012-02-14_03-25-08PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.(Unknown Source)
        at java.awt.Component.(Unknown Source)
[oracle@ora10grac-dg ~]$

⑤ 原因是缺少libXp-1.0.0-8.1.el5.i386.rpm及libXp-1.0.0-8.1.el5.x86_64.rpm这两个RPM软件包，接下来，卸载oracle安装介质的光盘，挂载操作系统安装光盘，到操作系统安装光盘里找到这两个RPM包，并执行安装：

[root@ora10grac-dg ~]# umount /mnt/
[root@ora10grac-dg ~]# mount /dev/cdrom /mnt/
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@ora10grac-dg ~]# cd /mnt/Server/
[root@ora10grac-dg Server]# ll libXp*
-rw-r--r-- 2 oracle root 22852 Jan 22  2009 libXp-1.0.0-8.1.el5.i386.rpm
-rw-r--r-- 2 oracle root 23561 Jan 22  2009 libXp-1.0.0-8.1.el5.x86_64.rpm
-rw-r--r-- 2 oracle root 15103 Jan 22  2009 libXp-devel-1.0.0-8.1.el5.i386.rpm
-rw-r--r-- 2 oracle root 15072 Jan 22  2009 libXp-devel-1.0.0-8.1.el5.x86_64.rpm
-rw-r--r-- 2 oracle root 46410 Jan 22  2009 libXpm-3.5.5-3.i386.rpm
-rw-r--r-- 2 oracle root 45336 Jan 22  2009 libXpm-3.5.5-3.x86_64.rpm
-rw-r--r-- 2 oracle root 31307 Jan 22  2009 libXpm-devel-3.5.5-3.i386.rpm
-rw-r--r-- 2 oracle root 32469 Jan 22  2009 libXpm-devel-3.5.5-3.x86_64.rpm
[root@ora10grac-dg Server]# rpm -ivh libXp-1.0.0-8.1.el5.i386.rpm 
warning: libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@ora10grac-dg Server]# rpm -ivh libXp-1.0.0-8.1.el5.x86_64.rpm 
warning: libXp-1.0.0-8.1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@ora10grac-dg Server]# cd 
[root@ora10grac-dg ~]# umount /mnt/
[root@ora10grac-dg ~]#

⑥ 接下来，重新挂载Oracle安装介质光盘，重新执行OUI安装，进入熟悉的OUI安装界面，选择高级安装，单击Next，一路走下去：

⑦ 单击Next，进入到安装前的与检查页面，提示交换分区不够大的警告，还有一个网络配置的信息，说是建议采用DHCP，这里直接忽略：

⑧ Next 进入下一步，注意选择只安装软件，不要建库，因为我们是要配置物理dataguard。

⑨ 最后，以root用户执行下述两个脚本，完成oracle软件的安装：

[root@ora10grac-dg ~]# /u01/app/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete  
[root@ora10grac-dg ~]# /u01/app/oracle/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@ora10grac-dg ~]#

⑩ 以Oracle用户执行SQL*PLUS命令行工具，做简单验证：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 14 16:53:01 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL>

至此，Oracle 10g数据库软件已经安装完成！！！

二 oracle软件安装完成之后，接下来，开始升级Oracle软件到10.2.0.5.0版本：

① 同样，10.2.0.5.0的升级包也在安装介质的光盘上，如果没有的话，需要自行到oracle的官方网站下载。依然以oracle用户在图形界面下执行下述命令，进入OUI的图形界面进行升级：

[oracle@ora10grac-dg ~]$ /mnt/p8202632_10205_Linux-x86-64/Disk1/runInstaller 
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-14_04-59-35PM. Please wait ...[oracle@ora10grac-dg ~]$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

② Next 进入下一步：

③ 继续Next：

④ Next，进入下一步：

⑤ 发现，此时提示内核的fs.file-max = 65536参数太小，需要调整为101365。以及net.ipv4.ip_local_port_range = 1024 65000需要调整为net.ipv4.ip_local_port_range = 9000 65500。依据错误提示信息，对/etc/sysctl.conf配置文件重新进行修改。修改后，单击Retry，此时不再提示该警告，转而提示swap过小，可直接忽略：

[root@ora10grac-dg ~]# tail -10 /etc/sysctl.conf
fs.file-max = 101365
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default=1048576
net.core.rmem_max= 1048576
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.ip_local_port_range = 9000 65500
[root@ora10grac-dg ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 101365
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.ip_local_port_range = 9000 65500
[root@ora10grac-dg ~]#

⑥ 单击，Install，进入安装升级包：

⑦ 最后，执行提示脚本/u01/app/oracle/root.sh，完成oracle软件的升级！

[root@ora10grac-dg ~]# /u01/app/oracle/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@ora10grac-dg ~]#

⑧ 当然，再次以oracle用户在命令行执行SQL*PLUS命令，确认数据库软件已经升级到10.2.0.5.0版本：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 17:31:12 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL>

⑨ 最后，别忘记了配置监听程序。可以使用Oracle提供的Net Configuration Assisstant、Net Manager图形化界面工具进行配置，也可以直接到$ORACLE_HOME/network/admin/目录下直接编辑一个listener.ora的配置文件。这里以netca配置：

配置完成后的信息如下：

[oracle@ora10grac-dg ~]$ cat /u01/app/oracle/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora10grac-dg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@ora10grac-dg ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 15-FEB-2012 09:17:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10grac-dg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                15-FEB-2012 09:16:03
Uptime                    0 days 0 hr. 1 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10grac-dg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora10grac-dg ~]$

三接下去的工作便是配置ASM存储，创建ASM实例：

① 关闭物理备库机器，添加新的硬盘，该硬盘将来用作ASM磁盘，用于数据库的存储。我这里添加两块硬盘/dev/sdb将来用作ASM存储，/dev/sdc将来用作备份用。添加硬盘后的信息如下：

[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  4.8G  2.6G  66% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
[root@ora10grac-dg ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1044     8385898+  83  Linux
/dev/sda2            1045        1305     2096482+  82  Linux swap / Solaris

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table
[root@ora10grac-dg ~]#

接下来，分别格式化/dev/sdb、/dev/sdc:

[root@ora10grac-dg ~]# fdisk /dev/sdb 
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): 
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ora10grac-dg ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1044.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1044, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1044, default 1044): 
Using default value 1044

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ora10grac-dg ~]# partprobe 
Warning: Unable to open /dev/hdc read-write (Read-only file system).  /dev/hdc has been opened read-only.
[root@ora10grac-dg ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1044     8385898+  83  Linux
/dev/sda2            1045        1305     2096482+  82  Linux swap / Solaris

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1305    10482381   83  Linux

Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1044     8385898+  83  Linux
[root@ora10grac-dg ~]#

在这里，顺便给/dev/sdc1做ext3的文件系统，然后挂载到/oracle_backup下，并更改权限，步骤如下：

[root@ora10grac-dg ~]# mkdir /oracle_backup  
[root@ora10grac-dg ~]# mkfs.ext3 /dev/sdc1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1048576 inodes, 2096474 blocks
104823 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2147483648
64 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 25 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@ora10grac-dg ~]# mount -t ext3 /dev/sdc1 /oracle_backup/
[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  4.8G  2.6G  66% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
/dev/sdc1     ext3    7.9G  147M  7.4G   2% /oracle_backup
[root@ora10grac-dg ~]# chown -R oracle:oinstall /oracle_backup/
[root@ora10grac-dg ~]# cd / && ll
total 184
drwxr-xr-x   2 root   root      4096 Feb 14 12:26 bin
drwxr-xr-x   3 root   root      4096 Feb 14 11:07 boot
drwxr-xr-x  10 root   root      3800 Feb 15 09:57 dev
drwxr-xr-x  89 root   root     12288 Feb 15 10:03 etc
drwxr-xr-x   3 root   root      4096 Feb 14 14:59 home
drwxr-xr-x  11 root   root      4096 Feb 14 12:26 lib
drwxr-xr-x   7 root   root     12288 Feb 14 12:26 lib64
drwx------   2 root   root     16384 Feb 14 11:00 lost+found
drwxr-xr-x   2 root   root      4096 Feb 15 09:38 media
drwxr-xr-x   2 root   root         0 Feb 15 09:38 misc
drwxr-xr-x   2 root   root      4096 Feb 17  2010 mnt
drwxr-xr-x   2 root   root         0 Feb 15 09:38 net
drwxr-xr-x   3 root   root      4096 Feb 14 16:43 opt
drwxr-xr-x   3 oracle oinstall  4096 Feb 15 10:03 oracle_backup
dr-xr-xr-x 120 root   root         0 Feb 15 09:37 proc
drwxr-x---  16 root   root      4096 Feb 14 17:46 root
drwxr-xr-x   2 root   root     12288 Feb 14 12:26 sbin
drwxr-xr-x   2 root   root      4096 Feb 14 11:01 selinux
drwxr-xr-x   2 root   root      4096 Feb 17  2010 srv
drwxr-xr-x  11 root   root         0 Feb 15 09:37 sys
drwxr-xr-x   3 root   root      4096 Feb 14 11:06 tftpboot
drwxrwxrwt  17 root   root      4096 Feb 15 09:51 tmp
drwxr-xr-x   3 oracle oinstall  4096 Feb 14 14:59 u01
drwxr-xr-x  15 root   root      4096 Feb 14 11:05 usr
drwxr-xr-x  22 root   root      4096 Feb 14 11:10 var
[root@ora10grac-dg /]#

② 配置ASM，在这里用裸设备做，不采用oracle 的asmlib进行配置(虽然我个人以前一直使用)，所以在这里也就不需要安装asmlib软件包。

首先，获取/dev/sdb设备的UUID：

[root@ora10grac-dg rules.d]# scsi_id -g -v -s /block/sdb
36000c29e0aa117a4e812fb78d5fb3517
[root@ora10grac-dg rules.d]#

然后，在/etc/udev/rules.d/60-raw.rules文件里添加配置规则如下：

[root@ora10grac-dg rules.d]# pwd
/etc/udev/rules.d
[root@ora10grac-dg rules.d]# ll
total 156
-rw-r--r-- 1 root root   515 Jul  3  2009 05-udev-early.rules
-rw-r--r-- 1 root root   900 Mar 31  2010 40-multipath.rules
-rw-r--r-- 1 root root 15647 Jul  3  2009 50-udev.rules
-rw-r--r-- 1 root root   471 Jul  3  2009 51-hotplug.rules
-rw-r--r-- 1 root root   143 Jul  4  2009 60-net.rules
-rw-r--r-- 1 root root  1088 Jun  6  2007 60-pcmcia.rules
-rw-r--r-- 1 root root   475 Feb 15 15:30 60-raw.rules
-rw-r--r-- 1 root root  9014 Apr  1  2010 60-wacom.rules
-rw-r--r-- 1 root root   129 Apr  1  2010 61-uinput-stddev.rules
-rw-r--r-- 1 root root   214 Apr  1  2010 61-uinput-wacom.rules
-rw-r--r-- 1 root root    61 Feb 15 15:32 65-raw-permissions.rules
-rw-r--r-- 1 root root  1823 Nov  5  2008 85-pcscd_ccid.rules
-rw-r--r-- 1 root root   114 Nov  5  2008 90-alsa.rules
-rw-r--r-- 1 root root    61 Jul  3  2009 90-dm.rules
-rw-r--r-- 1 root root    82 Apr  1  2010 90-hal.rules
-rw-r--r-- 1 root root   107 Jul  3  2009 95-pam-console.rules
-rw-r--r-- 1 root root   292 Apr  1  2010 98-kexec.rules
-rw-r--r-- 1 root root  2319 Jul 15  2008 bluetooth.rules
[root@ora10grac-dg rules.d]# cat 60-raw.rules 
# Enter raw device bindings here.
#
# An example would be:
#   ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
#   ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="36000c29e0aa117a4e812fb78d5fb3517",RUN+="/bin/raw /dev/raw/raw1 %N" 
[root@ora10grac-dg rules.d]#

其次，在/etc/udev/rules.d/路径下添加一个权限控制文件，这里命名为65-raw-permissions.rules，其作用是确保操作系统重启后，oracle用户仍然有对裸设备操作的权限。否则，重启系统后，ASM 实例将不可用。

[root@ora10grac-dg rules.d]# cat 65-raw-permissions.rules 
KERNEL=="raw1", OWNER="oracle", GROUP="oinstall", MODE="660"
[root@ora10grac-dg rules.d]#

最后，重新加载规则，并启动udev：

[root@ora10grac-dg rules.d]# /sbin/udevcontrol reload_rules
[root@ora10grac-dg rules.d]# /sbin/start_udev 
Starting udev: [  OK  ]
[root@ora10grac-dg rules.d]# ll /dev/raw/raw1 
crw-rw---- 1 oracle oinstall 162, 1 Feb 15 15:34 /dev/raw/raw1
[root@ora10grac-dg rules.d]#

至此，raw设备配置完毕！

③ 创建ASM实例：

首先，确认asm相应的软件包安装成功：

[root@ora10grac-dg ~]# rpm -qa | grep asm
oracleasm-support-2.1.3-1.el5
oracleasm-2.6.18-194.el5-2.0.5-1.el5
[root@ora10grac-dg ~]#

从上，可以看到系统并没有安装asmlib包，因为我们是用裸设备作asm磁盘。

然后，配置ASM服务：

[root@ora10grac-dg ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@ora10grac-dg ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [  OK  ]
Scanning the system for Oracle ASMLib disks: [  OK  ]
[root@ora10grac-dg ~]# /etc/init.d/oracleasm listdisks
[root@ora10grac-dg ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [  OK  ]
[root@ora10grac-dg ~]# /etc/init.d/oracleasm listdisks
[root@ora10grac-dg ~]# /etc/init.d/oracleasm querydisk /dev/sd*
Device "/dev/sda" is not marked as an ASM disk
Device "/dev/sda1" is not marked as an ASM disk
Device "/dev/sda2" is not marked as an ASM disk
Device "/dev/sdb" is not marked as an ASM disk
Device "/dev/sdb1" is not marked as an ASM disk
Device "/dev/sdc" is not marked as an ASM disk
Device "/dev/sdc1" is not marked as an ASM disk
[root@ora10grac-dg ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@ora10grac-dg ~]#

从上面的输出中，可以看到asm服务会随着操作系统启动而加载，并且在没有用asmlib包配置asm磁盘的情况下，执行/etc/init.d/oracleasm listdisks命令是看不到磁盘的。

其次，oracle用户登录图形界面，执行dbca，进入图形界面进行安装：

单击Next,进入下一步：

选择最后一项，配置ASM，单击Next：

提示需要以root用户执行”/u01/app/oracle/bin/localconfig add”命令来启动CSS服务：

[root@ora10grac-dg ~]# /u01/app/oracle/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab 
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        ora10grac-dg
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@ora10grac-dg ~]# ps -ef | grep css
root     25911     1  0 16:06 ?        00:00:00 /bin/sh /etc/init.d/init.cssd run
oracle   26151 25911  4 16:06 ?        00:00:01 /u01/app/oracle/bin/ocssd.bin
root     26171  3352  0 16:07 pts/1    00:00:00 grep css
[root@ora10grac-dg ~]#

执行完上述命令之后，回到图形界面，单击OK，进入下一步：

输入ASM实例的SYS用户的口令，单击Next：

提示，DBCA将创建ASM实例，单击OK，下一步：

这时，可以在操作系统上看到ASM实例的后台进程：

[root@ora10grac-dg ~]# ps -ef | grep asm_
oracle   26211     1  0 16:12 ?        00:00:00 asm_pmon_+ASM
oracle   26213     1  0 16:12 ?        00:00:00 asm_psp0_+ASM
oracle   26215     1  0 16:12 ?        00:00:00 asm_mman_+ASM
oracle   26217     1  0 16:12 ?        00:00:00 asm_dbw0_+ASM
oracle   26219     1  0 16:12 ?        00:00:00 asm_lgwr_+ASM
oracle   26221     1  0 16:12 ?        00:00:00 asm_ckpt_+ASM
oracle   26223     1  0 16:12 ?        00:00:00 asm_smon_+ASM
oracle   26225     1  0 16:12 ?        00:00:00 asm_rbal_+ASM
oracle   26227     1  0 16:12 ?        00:00:00 asm_gmon_+ASM
root     26233  3352  0 16:12 pts/1    00:00:00 grep asm_
[root@ora10grac-dg ~]#

单击Create New,创建磁盘组，输入磁盘组的名称，oradata,冗余级别选择外部，选中磁盘/dev/raw/raw1，单击Ok：

最后，单击Finish完成ASM实例的创建。

我们，可以在SQL*PLUS的字符命令行下访问asm实例：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ export ORACLE_SID=+ASM
[oracle@ora10grac-dg ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 15 16:20:45 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter spfile;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
spfile                               string                            /u01/app/oracle/dbs/spfile+ASM
                                                                       .ora
SQL> show parameter asm

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
asm_diskgroups                       string                            ORADATA
asm_diskstring                       string
asm_power_limit                      integer                           1
SQL> col name for a30
SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE                               TOTAL_MB    FREE_MB
------------------------------ --------------------------------- ---------- ----------
ORADATA                        MOUNTED                                10236      10186

SQL>

至此，我们的物理备库已经准备就绪，但是我们的物理备库还没有搭建出来，未完待续！下一篇，介绍物理备库搭建的详细步骤，敬请期待！！！

Oracle 10g RAC 配置物理dataguard系列1：RAC主库信息概览、备库准备工作

2月 14,2012 有1条评论 Dataguard, Experience, Oracle, RAC

最近在学习Oracle dataguard相关知识。本系列就Oracle Enterprise Linux 5.5 X86_64位环境下Oracle 10g 10.2.0.5.0双节点RAC数据库配置一套物理dataguard做一记录，一来给自己的学习做一简单记录，二来，同样希望可以给广大网友及Oracle数据库技术爱好者提供思路。

在实施之前，简单介绍下背景、最终目标及配置步骤：

背景介绍：主库是一套运行在Oracle Enterprise Linux 5.5 X86_64位环境下Oracle 10g 10.2.0.5.0双节点RAC数据库，主库用的是ASM存储，ASM磁盘利用raw来实现；
最终目标：需要给该主库搭建一套同样是运行在Oracle Enterprise Linux 5.5 X86_64位环境下的物理备库，并且能够switchover，确保高可用
配置步骤： 1 了解现有主库的配置信息；

2 搭建一台干净的新机器，用于创建物理备库使用；

3 在物理备机上安装oracle数据库软件，并且升级到同主库软件版本一致，即10.2.0.5.0；

4 在物理备机上创建ASM实例，ASM磁盘同样选择裸设备来实现；

5 配置物理备库；

6 确认switchover成功，双节点RAC+physical standby既可提供实例级别容灾(RAC功能)又可以提供存储级别容灾(Dataguard功能)，做到真正的高可用！

本篇是整个实施过程的系列1：本篇的内容主要是查看主库基本配置信息，及创建一台干净的Linux服务器，用作备库。

一首先了解下现有双节点RAC数据库配置信息：

1 节点1系统基本信息：

[root@oracle-rac1 ~]# hostname 
oracle-rac1.gillion.com.cn
[root@oracle-rac1 ~]# uname -rm
2.6.18-194.0.0.0.3.el5 x86_64
[root@oracle-rac1 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
              ext3     20G   12G  6.8G  64% /
/dev/sda1     ext3     99M   19M   75M  21% /boot
tmpfs        tmpfs    3.9G     0  3.9G   0% /dev/shm
[root@oracle-rac1 ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:31  
          inet addr:172.16.0.33  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fe3a:b831/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:836563 errors:0 dropped:0 overruns:0 frame:0
          TX packets:10459 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:73068451 (69.6 MiB)  TX bytes:2856621 (2.7 MiB)
eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:31  
          inet addr:172.16.0.35  Bcast:172.16.15.255  Mask:255.255.240.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
eth1      Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:3B  
          inet addr:192.168.139.13  Bcast:192.168.139.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe3a:b83b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1484344 errors:0 dropped:0 overruns:0 frame:0
          TX packets:707841 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:327352385 (312.1 MiB)  TX bytes:353601291 (337.2 MiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:265726 errors:0 dropped:0 overruns:0 frame:0
          TX packets:265726 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:61035429 (58.2 MiB)  TX bytes:61035429 (58.2 MiB)
[root@oracle-rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost
172.16.0.33             oracle-rac1.gillion.com.cn oracle-rac1
172.16.0.35             oracle-rac1-vip oracle-rac1-vip.gillion.com.cn
192.168.139.13          oracle-rac1-priv
172.16.0.34             oracle-rac2.gillion.com.cn oracle-rac2
172.16.0.36             oracle-rac2-vip oracle-rac2-vip.gillion.com.cn
192.168.139.14          oracle-rac2-priv
[root@oracle-rac1 ~]# su - oracle
[oracle@oracle-rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? glndb
[oracle@oracle-rac1 ~]$ export ORACLE_SID=glndb1
[oracle@oracle-rac1 ~]$ env | grep ORA
ORACLE_SID=glndb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac1 ~]$

2 节点1数据库基本信息：

[oracle@oracle-rac1 ~]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 09:11:00 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select dbid,name,created,log_mode,db_unique_name from gv$database;
      DBID NAME      CREATED             LOG_MODE     DB_UNIQUE_NAME
---------- --------- ------------------- ------------ ---------
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
SQL> show parameter db_name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_name                              string      glndb
SQL> show parameter db_unique;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_unique_name                       string      glndb
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch1
Oldest online log sequence     7699
Next log sequence to archive   7700
Current log sequence           7700
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL>

3 节点2基本信息：

[root@oracle-rac2 ~]# hostname 
oracle-rac2.gillion.com.cn
[root@oracle-rac2 ~]# uname -rm
2.6.18-194.0.0.0.3.el5 x86_64
[root@oracle-rac2 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
              ext3     20G   12G  6.9G  63% /
/dev/sda1     ext3     99M   19M   75M  21% /boot
tmpfs        tmpfs    3.9G     0  3.9G   0% /dev/shm
[root@oracle-rac2 ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:4C  
          inet addr:172.16.0.34  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fed1:b54c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:833250 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8442 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:72725394 (69.3 MiB)  TX bytes:2440410 (2.3 MiB)
eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:4C  
          inet addr:172.16.0.36  Bcast:172.16.15.255  Mask:255.255.240.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
eth1      Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:56  
          inet addr:192.168.139.14  Bcast:192.168.139.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fed1:b556/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1534148 errors:0 dropped:0 overruns:0 frame:0
          TX packets:655976 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:424024468 (404.3 MiB)  TX bytes:256611983 (244.7 MiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:274642 errors:0 dropped:0 overruns:0 frame:0
          TX packets:274642 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:61783289 (58.9 MiB)  TX bytes:61783289 (58.9 MiB)
[root@oracle-rac2 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost
172.16.0.33             oracle-rac1.gillion.com.cn oracle-rac1
172.16.0.35             oracle-rac1-vip oracle-rac1-vip.gillion.com.cn
192.168.139.13          oracle-rac1-priv
172.16.0.34             oracle-rac2.gillion.com.cn oracle-rac2
172.16.0.36             oracle-rac2-vip oracle-rac2-vip.gillion.com.cn
192.168.139.14          oracle-rac2-priv
[root@oracle-rac2 ~]# su - oracle
[oracle@oracle-rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? glndb
[oracle@oracle-rac2 ~]$ export ORACLE_SID=glndb2
[oracle@oracle-rac2 ~]$ env |grep ORA
ORACLE_SID=glndb2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac2 ~]$

4 点2数据库基本信息：

[oracle@oracle-rac2 ~]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 09:25:47 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> set line 160
SQL> select dbid,name,created,log_mode,db_unique_name from gv$database;
      DBID NAME      CREATED             LOG_MODE     DB_UNIQUE_NAME
---------- --------- ------------------- ------------ ---------
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_name                              string      glndb
SQL> show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_unique_name                       string      glndb
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch2
Oldest online log sequence     3270
Next log sequence to archive   3271
Current log sequence           3271
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL>

5 数据文件、控制文件基本信息：

SQL> select name,bytes/1024/1024 M from v$datafile
  2  union
  3  select name,0 from v$controlfile
  4  order by 2
  5  ;
NAME                                                        M
-------------------------------------------------- ----------
+ORADATA/glndb/controlfile/current.256.726057849            0
+ORADATA/glndb/datafile/example.435.756233417               2
+ORADATA/glndb/datafile/users.265.726057871                 2
+ORADATA/glndb/datafile/undotbs3.268.774870843            200
+ORADATA/glndb/datafile/undotbs2.264.726057869            261
+ORADATA/glndb/datafile/sysaux.262.726057863             1290
+ORADATA/glndb/datafile/system.260.726057851             6039
7 rows selected.
SQL>

6 联机日志文件信息：

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1       7700   52428800          1 NO  CURRENT              445626703 2012/02/13 23:06:00
         2          1       7699   52428800          1 YES INACTIVE             445622543 2012/02/13 23:05:29
         4          2       3271   52428800          1 NO  CURRENT              445627780 2012/02/13 23:05:58
         5          2       3270   52428800          1 YES INACTIVE             445332258 2012/02/13 09:05:39
SQL>

7 资源状态信息：

[oracle@oracle-rac2 ~]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 ~]$

二在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用

本文采用的是在一套虚拟机上搭建另外的备库机器，该虚拟机软件是VMware ESX 4.0，该软件其实可以认为是一个操作系统，是直接装在物理机器上的，有别于Vmware公司的VMware Server、VMware Workstation软件，后两者是先需要有操作系统，然后在操作系统上安装该虚拟机软件。而且，VMware ESX 4.0虚拟机软件相对来说也要比后两者稳定。

用Vmware vSphere Client工具登录虚拟机控制台：

登录控制台后，虚拟机信息概览：

2 选择File–New–Virtual Machine，新建虚拟机命名为10gRAC-Dataguard：

3 接下来一步一步走，同在VMware Server、VMware Workstation里创建新虚拟机一样，在此不再赘述。配置后的虚拟机概览如下：

4 给10gRAC-Dataguard加电，开始安装操作系统，虚拟机磁盘分区如下：

5 虚拟机网络配置信息如下：

IPv4:172.16.0.202

Netmask:255.255.240.0

Gateway:172.16.15.254

DNS:Null

Hostname:ora10grac-dg

6 软件包安装时，选择客户化，其中，下述是选择要安装的软件包：

Desktop Environments：GNOME Desktop Environment

Applications:

Editors

Graphics

Development:

Development Libraries

Development Tools

GNOME Software Development

Legacy Software Development

X software Development

Base System:

Administration Tools

Base

Legacy Software Support

System Tools （其中，选择oracleasm软件包。注：默认情况下并没有oracleasmlib-2.0.4-1.el5软件包，如果采用asmlib来管理和创建asm磁盘的话，仍需要后续手工安装该RPM包，否则将来配置ASM存储时会找不到ASM磁盘！！！）

暂时，先安装这些软件包，稍后在安装oracle软件时，如有缺失软件包的话，再续安装，至于其它的Server，虚拟化，集群软件包一概不装！

7 接下来，进行格式化文件系统，进入系统安装。稍后，还有一些后续的配置工作，记得不要开启SELinux并且关闭防火墙！最后，一台新的虚拟机配置成功，登录界面如下：

8 新虚拟机基本信息如下：

[root@ora10grac-dg ~]# hostname 
ora10grac-dg
[root@ora10grac-dg ~]# uptime 
 11:17:18 up 2 min,  2 users,  load average: 1.98, 1.23, 0.47
[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  2.8G  4.7G  37% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
[root@ora10grac-dg ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:2E:60:24  
          inet addr:172.16.0.202  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fe2e:6024/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3356 errors:0 dropped:0 overruns:0 frame:0
          TX packets:196 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:333071 (325.2 KiB)  TX bytes:21084 (20.5 KiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:1056 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1056 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:1753796 (1.6 MiB)  TX bytes:1753796 (1.6 MiB)
[root@ora10grac-dg ~]# uname -rm
2.6.18-194.el5 x86_64
[root@ora10grac-dg ~]#

这样，一台新的物理备库机器配置成功！接下来需要在该机器上安装oracle软件，配置ASM，升级oracle软件到10.2.0.5.0版本，下篇继续！

umount误操作引发数据库宕机

11月 24,2011 有8条评论 Experience, Oracle

在开发数据库上，执行完其它测试工作后，随手执行命令卸载光盘，图省事，执行了下述命令：

[root@OEL511gR2 ~]# umount -all

然后，然后，就导致了一则不大不小的数据库宕机！！！
因为，我的开发库文件系统信息如下：

[root@OEL511gR2 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    9.7G  7.9G  1.3G  87% /
tmpfs        tmpfs    502M     0  502M   0% /dev/shm
/dev/sdb1     ext4     67G   57G  6.5G  90% /u02/rimis_data
172.16.1.100:/backup
               nfs    466G  105G  361G  23% /backup
[root@OEL511gR2 ~]#

而数据库的数据文件信息如下：

[oracle@OEL511gR2 ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 24 17:09:59 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/RIMISDB/system01.dbf
/u01/app/oradata/RIMISDB/undotbs01.dbf
/u01/app/oradata/RIMISDB/sysaux01.dbf
/u01/app/oradata/RIMISDB/users01.dbf
/u02/rimis_data/css_ott.dbf
/u02/rimis_data/css_ltt_bk.dbf
/u02/rimis_data/css_lti_bl.dbf
/u02/rimis_data/css_oti.dbf
/u02/rimis_data/css_ltt_rp.dbf
/u02/rimis_data/css_cdi.dbf
/u02/rimis_data/css_lti_ob.dbf
NAME
--------------------------------------------------------------------------------
/u02/rimis_data/css_ltt_eh.dbf
/u02/rimis_data/css_blt.dbf
/u02/rimis_data/css_ltt_bl.dbf
/u02/rimis_data/css_eci.dbf
/u02/rimis_data/css_ect.dbf
/u02/rimis_data/css_lti_ec.dbf
/u02/rimis_data/css_lti.dbf
/u02/rimis_data/css_ltt_gw.dbf
/u02/rimis_data/css_ltt.dbf
/u02/rimis_data/css_ltt_tr.dbf
/u02/rimis_data/css_lti_rp.dbf
NAME
--------------------------------------------------------------------------------
/u02/rimis_data/css_lti_bk.dbf
/u02/rimis_data/css_gwi.dbf
/u02/rimis_data/css_bli.dbf
/u02/rimis_data/css_lti_tr.dbf
/u02/rimis_data/css_ltt_ec.dbf
/u02/rimis_data/css_gwt.dbf
/u02/rimis_data/css_lti_gw.dbf
/u02/rimis_data/css_cdt.dbf
/u02/rimis_data/css_ltt_ob.dbf
/u02/rimis_data/css_lti_eh.dbf
32 rows selected.
SQL>

接着，邮箱就瞬间收到了来自开发项目组发出的数据库不可用的邮件！
最后，重新挂载磁盘，重启数据库。
一个小插曲，启完数据库后，忘记了启监听，就电话通知开发部门数据库可用。
又接着还是一通反应数据库不可用，罪过啊，手工启监听，手工注册服务！
写在这里，给自己提个醒，在服务器上操作时，记得千万千万要谨慎！！！好在，本次故障中，硬盘没损坏！不然就KO了！

如何从逻辑备份的dumpfile文件里获取DDL脚本?

11月 21,2011 有1条评论 Experience, Oracle

在数据库的管理工作中，难免会遇到使用Export/Import或者是EXPort Data Pump/IMPort Data Pump工具来执行逻辑备份、恢复的场景。在有些时候，我们可能会对产生的dumpfile的内容感兴趣。本文就这两种工具产生的逻辑备份文件分别描述如何获取数据定义语句(Data Definition Language)的脚本？

1 先来看，如何从传统的Export工具产生的dumpfile里获取DDL语句？

首先，导出HR用户的所有对象：

rac1-> id

uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)

rac1-> pwd

/home/oracle

rac1-> ls -l

total 24

drwxrwxr-x 17 root   root      4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall 15626 Aug 31 14:28 db.rsp

drwxr-x---  3 oracle oinstall  4096 Aug 31 15:03 oradiag_oracle

rac1-> exp hr/hr file=exp_hr.dmp log=exp_hr.log owner=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 16:50:22 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user HR

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user HR

About to export HR's objects ...

. exporting database links

. exporting sequence numbers

.....

.....

. exporting statistics

Export terminated successfully without warnings.

rac1-> ls -l

total 23656

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

rac1->

我们已经顺利导出了一份exp_hr.dmp的dumpfile，那么该如何从该文件里获取DDL语句呢？

我们可以利用传统的导入工具Import附带SHOW=y选项来获取：

rac1-> imp hr/hr file=exp_hr.dmp log=show_ddl.log  show=y

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:03:26 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

...

...

Import terminated successfully without warnings.

rac1->

rac1-> ls -l

total 23688

drwxrwxr-x 17 root   root         4096 May 26 21:20 12419321

-rw-r--r--  1 oracle oinstall    15626 Aug 31 14:28 db.rsp

-rw-r--r--  1 oracle oinstall 24166400 Nov 21 16:51 exp_hr.dmp

-rw-r--r--  1 oracle oinstall     2493 Nov 21 16:51 exp_hr.log

drwxr-x---  3 oracle oinstall     4096 Aug 31 15:03 oradiag_oracle

-rw-r--r--  1 oracle oinstall    28766 Nov 21 17:03 show_ddl.log

rac1-> head -30 show_ddl.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing HR's objects into HR

"BEGIN  "

"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"

"CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41155490');"

"COMMIT; END;"

"CREATE TYPE "MY_TEST_TYPE" TIMESTAMP '2011-10-12:09:59:12' OID 'AF115DDE620"

"59E4AE04010ACBF000672'   as object(id number,name varchar2(10));"

"CREATE SEQUENCE "LOCATIONS_SEQ" MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 S"

"TART WITH 3300 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "DEPARTMENTS_SEQ" MINVALUE 1 MAXVALUE 9990 INCREMENT BY 10 "

"START WITH 280 NOCACHE NOORDER NOCYCLE"

"CREATE SEQUENCE "EMPLOYEES_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999"

"99999 INCREMENT BY 1 START WITH 207 NOCACHE NOORDER NOCYCLE"

"CREATE CLUSTER "DEPT_EMP_CLUSTER" ("DEPARTMENT_ID" NUMBER(4, 0))  PCTFREE 1"

"0 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 262144 NEXT 1048576 MI"

"NEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U"

"SERS""

"CREATE INDEX "IDX_DEPT_EMP_CLUSTER" ON CLUSTER "DEPT_EMP_CLUSTER" PCTFREE 1"

"0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 F"

"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS""

"CREATE TABLE "BIGFILE_TAB" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 P"

"CTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXT"

"ENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BIG_T"

rac1->

从上，我们可以看到，我们在IMPORT命令行上带上SHOW=Y的选项时，IMPORT工具并没有真正的执行数据导入，而是生成了一份含有DDL语句的日志文件，我们可以直接编辑该日志文件，以获取我们所需的脚本。

2 那么，对于EXPDP产生的dumpfile，又该如何获取DDL语句呢？

先以EXPDP导出dumpfile：

rac1-> pwd

/u01/app/oracle/mig_dir

rac1-> ls -l

total 0

rac1-> expdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=expdp_hr.log schemas=hr

Export: Release 11.2.0.1.0 - Production on Mon Nov 21 17:16:45 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

...

Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:19:48

rac1-> ls -l

total 24248

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

rac1->

该如何从expdp_hr.dmp文件里获取DDL语句呢？IMPDP工具给我们提供了SQLFILE的命令行选项，同样是只获取DDL语句，并未真正的执行数据导入。

rac1-> impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=hr_ddl.sql

Import: Release 11.2.0.1.0 - Production on Mon Nov 21 17:23:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "HR"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

...

...

Job "HR"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 17:23:47

rac1-> ls -l

total 24360

-rw-r----- 1 oracle asmadmin 24797184 Nov 21 17:19 expdp_hr.dmp

-rw-r--r-- 1 oracle asmadmin     3299 Nov 21 17:19 expdp_hr.log

-rw-r--r-- 1 oracle asmadmin   103892 Nov 21 17:23 hr_ddl.sql

-rw-r--r-- 1 oracle asmadmin     1672 Nov 21 17:23 impdp_hr.log

rac1-> head -30 hr_ddl.sql

-- CONNECT HR

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DEVDB', inst_scn=>'41161309');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC

CREATE TYPE "HR"."MY_TEST_TYPE"

OID 'AF115DDE62059E4AE04010ACBF000672' as object(id number,name varchar2(10));

/

ALTER TYPE "HR"."MY_TEST_TYPE"

COMPILE SPECIFICATION

PLSQL_OPTIMIZE_LEVEL=  2

PLSQL_CODE_TYPE=  INTERPRETED

PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

/

-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE

CREATE SEQUENCE  "HR"."LOCATIONS_SEQ"  MINVALUE 1 MAXVALUE 9900 INCREMENT BY 100 START WITH 3300 NOCACHE  NOORDER  NOCYCLE ;

rac1->

最后，简单一句话总结：

对于使用传统工具EXPORT导出的dumpfile，我们可以使用IMPORT加上SHOW=Y的选项获取DDL；

对于使用数据泵EXPDP导出的dumpfile，我们可以使用IMPDP加上SQLFILE的选项获取DDL。

ORA-39142 IMPDP跨版本导入数据报错解决

11月 18,2011 有2条评论 Experience, IMPDP, Oracle

前几天，公司某项目组搭建一套演示应用系统，需要将后台的Oracle数据从一台服务器迁移到另外一套系统。在执行IMPDP导入的过程遭遇下述错误，后经查找Metalink给出解决方法。

-bash-3.2$ impdp directory=mig_dir dumpfile=dms_expdp.dmp logfile=impdp.log schemas=dms
Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 10 November, 2011 3:12:56
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Username: sys/oracle as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/mig_dir/dms_expdp.dmp"
[oracle@OEL511gR2 mig_dir]$ oerr ora 39142
39142, 00000, "incompatible version number %s in dump file \"%s\""
// *Cause:  A dump file was specified for an import operation whose version
//          number is incompatible with the dump file version of the
//          Data Pump product currently running on the system. Usually this
//          message indicates that the dump file was produced by a newer
//          version of the Data Pump export utility.
// *Action: Import this dump file using the Data Pump import utility with
//          the same version as the export which created the file.
[oracle@OEL511gR2 mig_dir]$

ORA-39142的错误是指要导入的dump file中记录的数据库版本与要导入的数据库版本不一致。果不其然，这份导出的dump file是其他同事从一套11.1.0.6.0数据库上EXPDP 导出的，而这套要导入演示系统的数据库版本是10.2.0.5.0。将11g数据库中导出的dump file导入到10g数据库的过程中，就报这个错误了，Metalink ID 553337.1给出了详细的解决方法。

我这里的解决方法是在源库，即11g的数据库上重新以EXPDP加上version=10.2.0.5.0参数重新执行导出，然后在目标库上重新执行上述导入命令，OK！

需要注意的是，经过测试：直接在目标库(10.2.0.5.0)上以IMPDP附带version=11.1.0.6.0参数导入第一份dump file文件是行不通的，依旧会报上述ORA-39142的错！
那么，dump file里包含的数据库版本信息及其它详细信息应该怎么获取呢？我们将在下一篇介绍。

Oracle 数据逻辑恢复及权限控制一例

11月 16,2011 有1条评论 Experience, Experiments, Oracle

近日，应公司内部东南亚Regional Integrated Management Information System项目组需求，完成下述测试环境的搭建：

1 在现有开发库中，创建一个新的名为RIMISADMIN的schema，将现有RIMIS这个schema下的所有数据逻辑备份出来并导入到新建的RIMISADMIN这个schema下；

2 另外创建一个新的测试schema 名为RIMISTEST，该测试schema与RIMISADMIN公用一份数据，并限制其权限仅可以执行DML语句，对数据表有增删改查功能，不允许执行DDL语句，同时不允许对如下表执行DML语句操作：
cd_menu;cd_functionpoint;cd_menu_function;cd_office_fmenu;cd_role_fmenu;cd_office_biz_config;
cd_serial_no;cd_codedict;cd_codetype;edi_code_table;edi_code_type;pr_report_config;
上述内容是公司同事发给我的一封邮件，当然是我与需求同事沟通确认后，整理出来的需求信息。

我的解决思路：
1 在现有RAC数据库中将RIMIS schema用户下的所有数据用EXPDP按照schema模式全部逻辑导出；

2 准备使用IMPDP工具附带remap_schema=rimis:rimisadmin参数全部导入，在这一步执行之前确认RAC数据库的存储信息时，发现ASM磁盘组的总空间为236G，剩余空间只有区区的30G左右。而原有RIMIS schema下的所有数据在数据库中占用将近55G的空间，看来不能直接在这台RAC数据库上搭建这个测试环境，除非先给ASM磁盘组添加磁盘，而开发库又需要给各个项目组使用，不好直接停服务加磁盘，怕影响其他项目组进度。只好选择一种迂回的方案，先将测试环境搭建到另外一套数据库中，等有机会的话，再停RAC数据库，然后添加存储之后另作处理。

3 在新的测试环境上执行逻辑导入，导入之前，在新环境下创建必要的表空间，表空间名需要与原环境下RIMIS用户对象所在表空间名相同，否则，在导入时就得指定REMAP_TABLESPACE选项了，不然，就不可能顺利导入。具体的导入的命令如下：

impdp directory=mig_dir dumpfile=expdp_rimis_20111114.dmp logfile=impdp_rimis.log  remap_schema=rimis:rimisadmin exclude=table_statistics

4 导入之后，发现有些对象编译失效，图省事，执行下述命令直接编译RIMISADMIN schema下的所有对象：

SQL> exec dbms_utility.compile_schema('RIMISADMIN');

5 以SYS用户登录数据库，修改RIMISADMIN schema的口令，并确认RIMISADMIN 可以正常访问数据库；

[oracle@OEL511gR2 mig_dir]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 16 16:10:42 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn / as sysdba
Connected.
SQL> alter user rimisadmin identified by fjxm;
User altered.
SQL> conn rimisadmin/fjxm;
Connected.
SQL>

6 新环境下创建RIMIS schema,原来需求是创建RIMISTEST，又经更改需求要命名为RIMIS，并只给RIMIS用户赋予最小CREATE SESSION的权限及CREATE SYNONYM(见第8步，具体用处)的权限：

SQL> conn / as sysdba
Connected.
SQL> create user rimis identified by rimis;
User created.
SQL> grant create session to rimis;
Grant succeeded.
SQL> conn rimis/rimis
Connected.
SQL>

7 给RIMIS用户赋予可以对RIMISADMIN对象的增删改查的权限，见下述SQL：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/privs.sql
SQL> select 'grant select,insert,update,delete on '||table_name||' to rimis;' from user_tables;
SQL> spool off;

简单修改/home/oracle/privs.sql后，由RIMISADMIN用户执行/home/oracle/privs.sql赋权脚本。

8 RIMIS用户创建一系列的同义词：
RIMISADMIN先生成创建同义词的脚本：

SQL> conn rimisadmin/fjxm
Connected.
SQL> spool /home/oracle/create_synonym.sql
SQL> select 'create synonym '||table_name||' for rimisadmin.'||table_name||';' from user_tables;
SQL> spool off;

然后交由RIMIS用户执行上述创建同义词的脚本；
至此，RIMIS用户就可以正常访问RIMISADMIN用户下的对象。只是，还有一个特殊的上述那些表的权限控制，不能执行DML操作。

9 移除对特定表的增删改的权限：

SQL> conn rimisadmin/fjxm
Connected.
SQL> select 'revoke insert,update,delete on '||table_name||' from rimis;'
   2 from user_tables
   3 where lower(table_name) in ('cd_menu','cd_functionpoint','cd_menu_function',
   4 'cd_office_fmenu','cd_role_fmenu','cd_office_biz_config','cd_serial_no',
   5 'cd_codedict','cd_codetype','edi_code_table','edi_code_type','pr_report_config');

至此，完成RIMIS项目组的数据逻辑恢复、及相关权限的控制。以前，在数据库日常管理中，很少有遇到权限比较细化的场景，所以简单做一记录。
在发布本博文前，收到同事的邮件，说权限已经控制OK！

Tag Experience

Solution

一 安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

二 oracle软件安装完成之后，接下来，开始升级Oracle软件到10.2.0.5.0版本：

三 接下去的工作便是配置ASM存储，创建ASM实例：

一 首先了解下现有双节点RAC数据库配置信息：

二 在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用

一安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

三接下去的工作便是配置ASM存储，创建ASM实例：

一首先了解下现有双节点RAC数据库配置信息：

二在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用