存储控制器故障导致RAC数据库
ORA-600[kclchkblk_4]错误故障修复

大家好,我是黄伟老师。前几天,一位网友的数据库因为双存储控制器故障,导致一套跑在该存储上的Oracle11gR2 RAC数据库异常,出现ORA-600的错误,数据库无法正常启动,影响业务系统的正常使用和生产,于是找我帮忙处理。处理完这个case之后,现在我把当时的情景尽可能的还原出来,录制成这个视频,希望各位观看学习之后,将来如果在你的工作中,也出现类似的数据库故障,可以起到一个参考指导的作用,提供一个分析的思路,帮各位快速定位和恢复启动数据库。当然,这里面会涉及到一些Oracle数据库的运行原理和一些专业术语,比如SCN、Checkpoint,不过我会尽可能尝试着把它们讲清楚。

一 首先,接到帮助请求

二 分析错误和定位问题

对于不熟悉Oracle错误的同学,可以参考metalink(Oracle官方技术支持网站)官方文档(What is an ORA-600 Internal Error? (Doc ID 146580.1))的解释:

什么是ORA-600?

*ORA-600 errors are raised from the kernel code of the Oracle RDBMS software when an internal inconsistency is detected or an unexpected condition is met. This situation is not necessarily a bug as it might be caused by problems with the Operating System, lack of resources, hardware failures, etc.

With the ORA-600 error comes a list of arguments in square brackets. The first of these arguments tells us from where in the code the error was caught and thus is the key information in identifying the problem. This argument is either a number or a character string. The remaining arguments are used to supply further information (e.g. values of internal variables etc).

Whenever an ORA-600 error is raised a trace file is generated and an entry written to the alert.log with details of the trace file location. Starting with Oracle Database 11g Release 1, the diagnosability infrastructure was introduced which places the trace and core files into a location controlled by the DIAGNOSTIC_DEST initialization parameter when an incident, such as an ORA-600 is created. For earlier versions, the trace file will be written to either USER_DUMP_DEST (if the error was caught in a user process) or BACKGROUND_DUMP_DEST (if the error was caught in a background process like PMON or SMON). The trace file contains vital information about what led to the error condition.*

所以,我们从错误信息参数中,可以猜测出,ORA-00600: internal error code, arguments: [kclchkblk_4]应该是跟数据块错误有关的可能性很大。再结合这个案例,网友提供的信息说存储控制器故障,猜测多半情况下,数据库在运行(数据库进程通过内存空间,频繁和存储设备读写数据)的过程中,存储控制器一旦故障,多数情况下会导致内存中的脏数据(被修改过的数据)无法写入数据库,或者数据库检查点信息无法同步到控制文件、日志文件、数据文件中。那么数据库必死无疑,无法再继续工作下去了,当我们下次手工启库、或者数据库自动重启的过程中,数据库在open阶段,如果数据库无法完成一致性校验或者Instance Recovery实例恢复,那么数据库就无法Open,不能对业务系统提供服务了。

那么,本案例中,这个ORA-00600: internal error code, arguments: [kclchkblk_4]究竟表示什么意思呢?后面的几个参数又表示什么呢?我想,如果我们能把这几个参数搞清楚的话,那么这个问题也许就有眉目了。

于是,从metalink上找到跟该错误有关的几篇文章:

1 ORA-600 [kclchkblk_4] (Doc ID 1522401.1)

ORA-600 [kclchkblk_4] (Doc ID 1522401.1)这篇文档中,获取的信息有限,只是说:目前该错误并没有完全公开发布出来,因为它通常伴随ORA-600错误出现,而且被认为是已知确认的bug。

2 Bug 14351566 – ORA-600 [kclchkblk_4] ORA-600 [2662] when doing flash back (Doc ID 14351566.8)

该文档描述的是数据库多次执行flashback之后,导致的bug。我们该故障场景中,数据库并没有执行flashback的操作,排除。

3 ORA-600 [2662] “Block SCN is ahead of Current SCN” (Doc ID 28929.1)

 

不过,从该指导文档中可以获取到对此案例有用的指导信息。

① 该错误比较常见,通常由于存储故障、或者服务器异常掉电,数据库abort关闭等,导致的数据块里记录的SCN与数据库当前SCN不一致导致的。解决问题的常规办法就是通过redo log恢复数据,如果依然不奏效的话,可能需要通过非常规手段来修改数据库的SCN,使之一致,进而避免该错误发生。

这里,我在一套测试环境上模拟了该错误,并且手工修复了该错误:

Sat Dec 19 08:55:28 2020
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8251.trc  (incident=28953):
ORA-00600: internal error code, arguments: [2662], [0], [112587826], [0], [112587881], [4194432], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_28953/orcl_ora_8251_i28953.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8251.trc:
ORA-00600: internal error code, arguments: [2662], [0], [112587826], [0], [112587881], [4194432], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8251.trc:
ORA-00600: internal error code, arguments: [2662], [0], [112587826], [0], [112587881], [4194432], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 8251): terminating the instance due to error 600
Instance terminated by USER, pid = 8251
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (8251) as a result of ORA-1092
Sat Dec 19 08:55:33 2020

当然,这是另外一个case,我们暂且不去聚焦这个ORA-600 26662的错误如果解决。如果大家感兴趣且有需求的话,我可以另外录制一个视频去讲解它。

②从该文档中,倒是给出了有意义的错误参数:

ARGUMENTS:
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.

可以看出,这些错误参数,跟前面提到的错误参数格式非常一致:

RROR:

Format: ORA-600 [kclchkblk_4] [a] [b] [c] [d] [e]

但是,Oracle官方并没有给出这里的几个参数表示的具体意义。

那么,进一步猜测,我们这个故障的错误是不是也可以借鉴于该文档中给出的suggestion来尝试恢复呢?

③ 官方给出的几个suggestion


SUGGESTIONS:        
     
  There are different situations where ORA-600 [2662] can be raised.

  It can be raised on startup or during database operation.

  If not using Parallel Server, check that 2 instances have not mounted
  the same database.

  Check for SMON traces and have the alert.log and trace files ready
  to send to support.

  Check the SCN difference [argument d]-[argument b].

  If the SCNs in the error are very close, then try to shutdown and startup
  the instance several times. 

  In some situations, the SCN increment during startup may permit the 
  database to open. Keep track of the number of times you attempted a 
  startup.

到这里,有了一个可以尝试的解决问题的思路了,如果ORA-600 [kclchkblk_4] [a] [b] [c] [d] [e]这里的几个参数也表示某种与SCN有关的信息的话,那么,是不是可以根据该建议尝试恢复呢?

三 解决问题

1 start instance,确认错误参数是否推进

Tue Dec 08 15:04:02 2020
Thread 1 advanced to log sequence 9 (thread open)
Thread 1 opened at log sequence 9
  Current log# 1 seq# 9 mem# 0: +DATA/jhzmpr/onlinelog/group_1.262.1045198071
  Current log# 1 seq# 9 mem# 1: +DATA/jhzmpr/onlinelog/group_1.263.1045198071
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Dec 08 15:04:02 2020
SMON: enabling cache recovery
SUCCESS: diskgroup ARCH was mounted
Archived Log entry 1831 added for thread 1 sequence 8 ID 0xddd7c8e4 dest 1:
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_17070.trc  (incident=800345):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171385871], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/incident/incdir_800345/jhzmpr1_ora_17070_i800345.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
NOTE: dependency between database jhzmpr and diskgroup resource ora.ARCH.dg is established
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_17070.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171385871], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_17070.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171385871], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 17070): terminating the instance due to error 704
Instance terminated by USER, pid = 17070
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (17070) as a result of ORA-1092
Tue Dec 08 15:04:05 2020
ORA-1092 : opitsk aborting process

果不其然,argument d比上次推进了:从之前的171365869推进到171385871。同时,argument b保持不变,其它错误信息也保持一致。

2 继续上述操作

[root@jhzmpr01 ~]# srvctl start db -d jhzmpr
PRCR-1079 : Failed to start resource ora.jhzmpr.db
CRS-5017: The resource action "ora.jhzmpr.db start" encountered the following error:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171405876], [], [], [], [], [], [], []
Process ID: 21207
Session ID: 3175 Serial number: 3
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/jhzmpr01/agent/crsd/oraagent_oracle//oraagent_oracle.log".

CRS-5017: The resource action "ora.jhzmpr.db start" encountered the following error:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171405882], [], [], [], [], [], [], []
Process ID: 43944
Session ID: 3175 Serial number: 3
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/jhzmpr02/agent/crsd/oraagent_oracle//oraagent_oracle.log".

CRS-2674: Start of 'ora.jhzmpr.db' on 'jhzmpr01' failed
CRS-2674: Start of 'ora.jhzmpr.db' on 'jhzmpr02' failed
CRS-2632: There are no more servers to try to place resource 'ora.jhzmpr.db' on that would satisfy its placement policy
[root@jhzmpr01 ~]# 

3 直至错误参数一致

Tue Dec 08 15:44:31 2020
SMON: enabling cache recovery
SUCCESS: diskgroup ARCH was mounted
Archived Log entry 1846 added for thread 1 sequence 22 ID 0xddd7c8e4 dest 1:
NOTE: dependency between database jhzmpr and diskgroup resource ora.ARCH.dg is established
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_54315.trc  (incident=1248369):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171679465], [0], [171665974], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/incident/incdir_1248369/jhzmpr1_ora_54315_i1248369.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_54315.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171679465], [0], [171665974], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_54315.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171679465], [0], [171665974], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 54315): terminating the instance due to error 600
Instance terminated by USER, pid = 54315
ORA-1092 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:11782:10822} */...
opiodr aborting process unknown ospid (54315) as a result of ORA-1092
Tue Dec 08 15:44:34 2020
ORA-1092 : opitsk aborting process

4 启动数据库

Tue Dec 08 15:46:51 2020
Starting ORACLE instance (normal)
...
...
tarted redo scan
SUCCESS: diskgroup FLASH was mounted
Completed redo scan
 read 11 KB redo, 5 data blocks need recovery
Started redo application at
 Thread 1: logseq 23, block 3
Recovery of Online Redo Log: Thread 1 Group 5 Seq 23 Reading mem 0
  Mem# 0: +DATA/jhzmpr/onlinelog/group_5.273.1046776933
  Mem# 1: +FLASH/jhzmpr/onlinelog/group_5.256.1046776933
Completed redo application of 0.01MB
Completed crash recovery at
 Thread 1: logseq 23, block 25, scn 171685974
 5 data blocks read, 5 data blocks written, 11 redo k-bytes read
ARCH: STARTING ARCH PROCESSES
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

        ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

        Alternatively, if these temporary tablespaces are no longer
        needed, then they can be dropped.
          Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_smon_57089.trc  (incident=1280273):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/incident/incdir_1280273/jhzmpr1_smon_57089_i1280273.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

5 至此,该错误已经消失了,数据库可以正常访问了。

但是,alert日志里抛出一个报警,让我们给数据库添加临时文件。为什么呢?这是因为,这位网友之前根据网上的一些帖子尝试对数据库删除临时文件,但是没能恢复数据库。这里,我们可以先不去理会临时文件的文件,可以让数据库使用SYSAUX那个表空间充当临时表空间,如果需要的话。

四 补充

1 SCN

①定义

System Change Number,数据库“内部时钟”,数据库中每时每刻都在发生改变,这些改变对应这一个具体的时间点儿,每个具体的时间点儿对应一个数字编号,这个编号就是SCN值。比如:2020年12月19日18时30分52秒,对应一个SCN号,35789713456。注意,不同时间点儿对应不同的SCN,数据库一旦启动运行,SCN就不停向前滚动。

② 作用

对于关系型数据库而言,用于事务的ACID控制和管理,以及数据库的备份和恢复的标准。

③位置

数据文件、控制文件、日志文件、数据文件头、数据块中都有记录。

④查看
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
 112573590

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  112573593

SQL> select file#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1          112563728 2020/12/19 15:38:19
         2          112563728 2020/12/19 15:38:19
         3          112563728 2020/12/19 15:38:19
         4          112563728 2020/12/19 15:38:19
         5          112563728 2020/12/19 15:38:19
         6          112563728 2020/12/19 15:38:19
         7          112563728 2020/12/19 15:38:19

7 rows selected.

SQL> select group#,FIRST_CHANGE#,FIRST_TIME from v$log;

    GROUP# FIRST_CHANGE# FIRST_TIME
---------- ------------- -------------------
         1     112563728 2020/12/19 15:38:19
         2     112544279 2020/12/19 11:35:21
         3     112551742 2020/12/19 12:37:46

SQL> 
⑤结构和计数原理

占6个字节,48个二进制位数。前2个字节构成SCN_WRAP,最大值为2的16次方,65536。后4个字节构成SCN_BASE,最大值为2的32次方,4294967296。SCN总大小为2的48次方=281474976710656,281万亿个计数。

假定初始情况下,SCN_WRAP=0,SCN_BASE从1开始计数,当达到最大值4294967296时,就向前进一位,SCN_WRAP从0变为1,同时SCN_BASE从0重新开始计数,下一次达到最大值时,再向前进一位,SCN_WRAP再加1,SCN_BASE再次从0重新开始计数,以此循环往复。直至SCN用完耗尽为止。

SCN = (SCN_WRAP * 4294967296) + SCN_BASE

举个生活中的例子,类似于我们的计时秒表,当秒针从0跳到59时,分针从0变为1,同时秒针重新从0开始,直到到达下一个59秒,分针从1跳到2….

Oracle为什么要这么设计呢?这很好理解,例如,我们日常生活中会说3分28秒,很少有人直接说208秒,对不对? 就图一方便计数呗。

从Oracle12.2开始,SCN改为8个字节了,所以,其总大小变为2的63次方了。一个更大的数了。

2 Checkpoint

A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when you make a change to a block the modifications of that block are made to a memory copy of the block. When you commit the block, it is not written to the datafile but it is written to the REDO LOG, so we can “replay” a transaction in the event of an instance failure. Eventually, the system will checkpoint your modified blocks to the datafiles on disk.

  • When all dirty buffers in the Oracle SGA are written to datafiles by DBWR.

  • When a redo log switch occurs.

  • Whenever the time set by the LOG_CHECKPOINT_TIMEOUT parameter is reached.

  • By issuing the command ‘alter system switch logfile’ or ‘alter system checkpoint’.

说一个简单的场景,假定我们要执行下述SQL,修改表里的一条记录:

update department set emp_name=’Huangwei’ where emp_name=’huangwei’;

 

update department set emp_name='Huangwei' where emp_name='huangwei';

数据库肯定不会直接到磁盘中去修改该数据,因为如果把数据读入到内存中,在内存中读写数据,比读写磁盘要快不止一个数量级。所以,

① 数据库先到内存中去找要修改的数据,如果内存中没有该数据的话,Oracle数据库的服务器进程把需要的数据从磁盘中读取到内存中;注意,Oracle不会只读这一条记录的数据,而是读取该记录所在的数据块,把整个数据块的内容全部读入内存。为了性能和效率,Oracle数据库肯定不会傻傻的一次只读一条记录的数据,最小IO单位是database block,其它关系型数据库也类似。

② 然后准备在内存中对该数据进行修改;真正修改之前,还要先做另外一件事儿:就是把这份数据放到一个undo回滚段中,用于确保事务的一致性读或者回滚等操作。接下来,给该SQL语句分配一个事务号,说白了就是一个SCN号。并开始执行真正的修改,同时,在redo log buffer里对该操作进行完整的记录(如:2020年12月19日19点40分,这个时间点儿其实就是这个个SCN,将‘huangwei’修改成了‘Huangwei’),为什么要记录这个呢?答案是便于恢复数据,这里就不再继续扩展。注意,该修改过的新数据(称为dirty buffer)并不一定马上写回到磁盘中;

③ 当我们执行commit时,这时,该修改过的新数据(称为dirty buffer)写回到磁盘中去了吗???

④ 也不一定,因为,此时只要保证redo log buffer里,记录的关于本条SQL修改的信息被LGWR后台进程写出到redo log file里,就算完成了。在不考虑数据库包含DATAGUARD备库的情况下,此时就可以立即返回commit成功,即告诉你,你的修改已经生效了。为什么呢?这就是Oracle的redo log file的作用,如果此时数据库出现instance crash的话,我的修改还在吗?在,Oracle可以利用写入到redo logfile里的内容,进行“redo”,或者叫“replay”。这也就是它为啥叫redo了。

⑤那问题来了?到底什么时候,我的这个修改被真正写回到磁盘中呢?答案,看情况。

比如:如果此时,数据库的database buffer cache不够用了,数据库会根据LRU算法把旧的脏数据写出到磁盘中;

或者:此时发生了redo logfile switch;

再:手工执行了 alter system checkpoint;等。

注意,把内存中的脏数据写出到数据文件是DBWR后台进程干的活儿;记录检查点信息到控制文件、同步到数据文件、日志文件中,则是CKPT后台进程的职责。

The checkpoint process updates the control file when a checkpoint is performed. Remember that a checkpoint operation writes all changed (dirty) blocks to disk. By recording this fact in the control file, Oracle knows what redo records need to be applied in the event of an instance failure. To recover from an instance failure, Oracle needs to apply all redo generated after the last checkpoint recorded in the control file.

  • Log switches cause checkpoints. Checkpoints do not cause log switches.

  • DBWR writes dirty blocks from the buffer cache to disk — that does not happen when you “commit” — LGWR is involved during a commit.

五 小结

1 解决该数据库故障并不难

只是需要不停的startup,shudown,再startup。难点在于去分析和定位该问题,找到一些相关的线索,并支撑和问题相关的错误信息。

2 如何规避该问题

如果这位网友的数据库,在故障发生之前,执行了合适的热备份+归档的话,遇到该故障的时候,可能就没有这么被动。

3 备份很重要

在遇到数据库故障或者错误的场景下,一定要尽可能的先对数据库进行备份,保留数据库故障现场,便于后续恢复不成功的情况下,可以多次重新尝试。另外,如果的确没有十足把握的话,不要轻易尝试对数据库执行各种恢复操作。总结该故障的过程中,发现系统报错时间:

Sun Dec 06 05:16:31 2020
Thread 1 advanced to log sequence 652 (LGWR switch)
  Current log# 2 seq# 652 mem# 0: +DATA/jhzmpr/onlinelog/group_2.264.1045198071
  Current log# 2 seq# 652 mem# 1: +DATA/jhzmpr/onlinelog/group_2.265.1045198073
Sun Dec 06 05:16:33 2020
Archived Log entry 1816 added for thread 1 sequence 651 ID 0xdd0af974 dest 1:
Sun Dec 06 05:45:39 2020
WARNING: Write Failed. group:2 disk:2 AU:54678 offset:49152 size:8192
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_dbwb_7076.trc:
ORA-15080: synchronous I/O operation to a disk failed
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192
WARNING: failed to write mirror side 1 of virtual extent 7778 logical extent 0 of file 315 in group 2 on disk 2 allocation unit 54678
KCF: read, write or open error, block=0xf3106 online=1
file=10 '+DATA/jhzmpr/datafile/aoi.315.1057790147'
error=15081 txt: ''
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_dbwb_7076.trc:
Sun Dec 06 05:45:39 2020
WARNING: Read Failed. group:2 disk:0 AU:340 offset:49152 size:16384
WARNING: failed to read mirror side 1 of virtual extent 4 logical extent 0 of file 261 in group [2.4004072768] from disk DATA_0000  allocation unit 340 reason error; if possible, will try another mirror side
Sun Dec 06 05:45:39 2020
...
...
Sun Dec 06 05:45:46 2020
License high water mark = 102
Termination issued to instance processes. Waiting for the processes to exit
Sun Dec 06 05:46:00 2020
Instance termination failed to kill one or more processes
Instance terminated by CKPT, pid = 7080
Sun Dec 06 05:46:00 2020
第一次手工尝试恢复故障:
Sun Dec 06 14:28:32 2020
Starting ORACLE instance (normal)
...
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_6706.trc:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 2 thread 1: '+DATA/jhzmpr/onlinelog/group_2.265.1045198073'
ORA-00312: online log 2 thread 1: '+DATA/jhzmpr/onlinelog/group_2.264.1045198071'
ORA-742 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:11782:2} */...
Sun Dec 06 14:28:57 2020
Shutting down instance (abort)

第二次
Sun Dec 06 23:37:03 2020
Starting ORACLE instance (normal)
...
Mon Dec 07 00:27:06 2020
alter database clear unarchived logfile group 2
ORA-1624 signalled during: alter database clear unarchived logfile group 2...
Mon Dec 07 00:28:08 2020
ALTER DATABASE RECOVER  database using backup controlfile until cancel  
Media Recovery Start
 started logmerger process
Mon Dec 07 00:28:08 2020

...
Mon Dec 07 00:29:27 2020
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_pr00_43688.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/jhzmpr/datafile/system.256.1045198003'
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
ALTER DATABASE RECOVER CANCEL 
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Mon Dec 07 00:29:41 2020
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...
Mon Dec 07 00:30:17 2020
ALTER DATABASE RECOVER  database using backup controlfile until cancel allow 1 corruption  
Media Recovery Start
Serial Media Recovery started
...
Mon Dec 07 22:28:46 2020
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/trace/jhzmpr1_ora_39995.trc  (incident=576369):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [171642483], [0], [171225822], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/jhzmpr/jhzmpr1/incident/incdir_576369/jhzmpr1_ora_39995_i576369.trc
Use ADRCI or Support Workbench to package the incident.

4 祸不单行,福无双降,顺手帮忙

这个库处理好之后,那哥们儿出于对我的信任,又请我帮忙处理同样跑在该存储设备上另外几个同样没能幸免于难的数据库故障。那些使用该存储设备作为数据库存储的好几个数据库都挂了,

5 参考链接

https://topic.alibabacloud.com/a/ora-00600-kclchkblk_4-error-recovery-method-introduction-12_8_8_20042453.html

http://blog.itpub.net/29863023/viewspace-2132815/

 

6 感谢

各位老铁,如果觉得我的视频内容有用,能帮助到你,请帮忙一键三连,顺手转发给更多的朋友。

访问我的个人站点,查看本视频中提到的所有文档和资料素材: http://oracleonlinux.cn/

感谢大家的收看,我们下一期视频节目再见。

最后,欢迎大家关注我的个人微信公众号。

 

 

监听日志过大导致数据库连接变慢

收到公司一项目组邮件,说某开发环境的数据库连接不稳定,时快时慢,但是一旦连上数据库,整体的操作并不慢,想让我帮忙看看。

环境:Windows 2K8 R2 64位操作系统+Oracle 11gR2 64 bit单实例数据库 。

现象:在数据库服务器上,tnsping本地机器上的Oracle网络服务名,果然时快时慢。快时延迟只有10毫秒或者是0毫秒,而慢时延迟能达到10000多毫秒。这个显然不能接受。

思路:

1 第1反映可能是Oracle网络配置问题;
2 数据库服务器或者监听进程过于繁忙导致的;

处理:

经过排查和分析,由于是开发环境,各种不知名的原因,导致该Windows服务器上安装了1套10g的数据库软件[未建库],1套文中提到的11gR2单实例数据库,10g和11g版本的环境分别创建了各自的监听程序,10g的在默认1521端口,11g的在1522端口,并且2个监听程序的名字都是listener。问题更为蹊跷的是,如果tnsping 10g软件下的网络服务名很正常,tnsping 11g软件下的网络服务名才会出现时快时慢的现象。

于是乎,停掉10g环境下的那个占用1521端口的监听,重新配置11g的监听使之驻留在1521端口上。此时问题依旧:

C:\Users\Administrator>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 24-6月 -2014 16:49:20

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

已使用的参数文件:
D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.1.87)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 毫秒)

C:\Users\Administrator>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 24-6月 -2014 16:49:22

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

已使用的参数文件:
D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.1.87)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (7360 毫秒)

C:\Users\Administrator>

说明问题应该不至于出现在网络配置上面。

同时,该库是一个开发用的数据库,更谈不上繁忙之说。

那么,问题究竟应该出现在那儿呢?既然,在服务器上tnsping本地机器上的网络服务名不稳定,且SQL*PLUS通过网络服务名来访问本地机器上的Oracle服务肯定也会慢,那么就发起一个本地的网络服务连接,然后看看监听的日志信息。果不其然,发现了一点儿端倪,监听程序的日志文件过大:

listener_log_4GB

listener_log_4GB

原来,造成问题的罪魁就是这个过大的日志文件[Windows下Oracle的监听日志文件超过4G大小,便会出现该问题]!解决的方法就很简单,停止监听程序,删除监听日志文件,重启监听,问题解决!

谨慎细致的德国客户同事

这次,在德国出差期间,不巧的是,干活儿的家伙什蓝屏罢工了。向德国客户求助,德国人原本想帮我重新安装个正版的Windows系统,但想想,不对劲,友好的说,到时候你还是要把这个正版的软件带回China。

所以,作罢。

然后,德国佬给我提供了一台式机,并帮我做了些基本配置。开机,进入桌面之后,瞬间就觉得人家德国人做事的贴心与细致了:

dbs

原来,这哥们儿深知我干的是数据库相关的活儿,所以就事先在桌面上帮我创建好了访问数据库服务器远程桌面的快捷方式。

瞬间,觉得温暖。

Windows 10gR2 RAC迁移OCR和Vote Disk以及ASM磁盘组上的数据

概要:本文详细记录如何把运行在Windows 2K8环境上的Oracle 10gR2 双节点RAC数据库的OCR、Vote Disk以及存放在ASM磁盘组里的数据,迁移至新存储的步骤。

迁移之前的存储信息:

wps_clip_image-4627

如上图所示,我们的最终目标是要把OCR、Vote Disk,DATA、FLASH磁盘组从Disk 2,Disk 3上分别迁移至Disk 4和Disk 5。

接下来给出详细的操作步骤和流程:

1  迁移OCR

A 创建磁盘分区

C:\Users\Administrator>diskpart

Microsoft DiskPart version 6.1.7601

Copyright (C) 1999-2008 Microsoft Corporation.

On computer: HAMNODE1

DISKPART> list disk

Disk ###  Status         Size     Free     Dyn  Gpt

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

Disk 0    Online           40 GB      0 B

Disk 1    Online           20 GB    19 GB

Disk 2    Online         6149 MB  2048 KB

Disk 3    Online         6149 MB  2048 KB

Disk 4    Online         6149 MB  6147 MB

Disk 5    Online           10 GB    10 GB

DISKPART> select disk 4

Disk 4 is now the selected disk.

DISKPART> list part

There are no partitions on this disk to show.

DISKPART> create part ext

DiskPart succeeded in creating the specified partition.

DISKPART> create part log size=320

DiskPart succeeded in creating the specified partition.

DISKPART> create part log

DiskPart succeeded in creating the specified partition.

DISKPART> list part

Partition ###  Type              Size     Offset

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

Partition 0    Extended          6147 MB  1024 KB

Partition 1    Logical            320 MB  2048 KB

* Partition 2    Logical           5825 MB   323 MB

DISKPART>

分区之后:

wps_clip_image-12024

注:您应该在其他节点上看到同样的磁盘分区信息。且,建议在其他节点上将Windows系统自动给新分区分配的盘符移除掉。

B 准备磁盘,创建裸设备链接

C:\oracle\product\10.2.0\crs\BIN\GUIOracleOBJManager.exe

wps_clip_image-12115

wps_clip_image-24325

wps_clip_image-17645

wps_clip_image-13996

同样,执行完上述操作,您应该在RAC的所有节点上通过

C:\oracle\product\10.2.0\crs\BIN\GUIOracleOBJManager.exe程序看到相同的信息。

C 手工备份OCR

ocrconfig -export c:\oracle…..\backup_filename

备份之前,可以查看下OCR的自动备份信息:

c:\oracle\product\10.2.0\crs\BIN>ocrconfig -showbackup

hamnode2     2014/05/08 06:01:07     C:\oracle\product\10.2.0\crs\cdata\crs

hamnode2     2014/05/07 23:44:04     C:\oracle\product\10.2.0\crs\cdata\crs

hamnode2     2014/05/07 19:44:04     C:\oracle\product\10.2.0\crs\cdata\crs

hamnode2     2014/05/06 16:34:35     C:\oracle\product\10.2.0\crs\cdata\crs

hamnode1     2014/04/30 07:09:51     C:\oracle\product\10.2.0\crs\cdata\crs

c:\oracle\product\10.2.0\crs\BIN>

执行备份:

c:\oracle\product\10.2.0\crs\BIN>ocrconfig -export C:\oracle\product\10.2.0\crs\cdata\crs\backup_ocr_140508.bak

c:\oracle\product\10.2.0\crs\BIN>cd ..\cdata\crs

c:\oracle\product\10.2.0\crs\cdata\crs>dir

Volume in drive C has no label.

Volume Serial Number is BA2B-5F59

Directory of c:\oracle\product\10.2.0\crs\cdata\crs

05/08/2014  07:50 AM    <DIR>          .

05/08/2014  07:50 AM    <DIR>          ..

05/06/2014  03:24 AM         4,063,232 backup00.ocr

05/05/2014  11:24 PM         4,063,232 backup01.ocr

05/05/2014  07:24 PM         4,063,232 backup02.ocr

05/08/2014  07:43 AM            94,029 backup_ocr_140508.bak

05/02/2014  06:23 AM         4,042,752 day.ocr

05/05/2014  03:24 PM         4,063,232 day_.ocr

05/02/2014  01:04 AM            93,359 hw_backup_ocr.bak

04/30/2014  07:09 AM         4,042,752 week.ocr

8 File(s)     24,525,820 bytes

2 Dir(s)  20,506,509,312 bytes free

c:\oracle\product\10.2.0\crs\cdata\crs>

D 添加OCR镜像

ocrconfig -replace ocrmirror \\.\ocrmirror

添加之前,查看OCR信息:

c:\oracle\product\10.2.0\crs\BIN>ocrcheck

Status of Oracle Cluster Registry is as follows :

Version                  :          2

Total space (kbytes)     :     327452

Used space (kbytes)      :       3836

Available space (kbytes) :     323616

ID                       :  478679697

Device/File Name         : \\.\ocrcfg

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

c:\oracle\product\10.2.0\crs\BIN>

添加OCRMIRROR并验证:

c:\oracle\product\10.2.0\crs\BIN>ocrconfig -replace ocrmirror \\.\ocrmirror

c:\oracle\product\10.2.0\crs\BIN>ocrcheck

Status of Oracle Cluster Registry is as follows :

Version                  :          2

Total space (kbytes)     :     327452

Used space (kbytes)      :       3836

Available space (kbytes) :     323616

ID                       :  478679697

Device/File Name         : \\.\ocrcfg

Device/File integrity check succeeded

Device/File Name         : \\.\ocrmirror

Device/File integrity check succeeded

Cluster registry integrity check succeeded

c:\oracle\product\10.2.0\crs\BIN>

E 删除OCR device并验证

c:\oracle\product\10.2.0\crs\BIN>ocrconfig -replace ocr

c:\oracle\product\10.2.0\crs\BIN>ocrcheck

Status of Oracle Cluster Registry is as follows :

Version                  :          2

Total space (kbytes)     :     327452

Used space (kbytes)      :       3836

Available space (kbytes) :     323616

ID                       :  478679697

Device/File Name         : \\.\ocrmirror

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

c:\oracle\product\10.2.0\crs\BIN>

F 重启CRS并验证

至此,我们通过给OCR添加1份镜像然后删除原主OCR的方式,完成对OCR的迁移。

注:通过这种方式,删除原OCR之后,新添加的OCR镜像自动成为主OCR。

2 迁移Vote Disk

A 创建磁盘分区

这里的创建磁盘分区如创建OCR磁盘分区时,方法一致:

C:\Users\Administrator>diskpart

Microsoft DiskPart version 6.1.7601

Copyright (C) 1999-2008 Microsoft Corporation.

On computer: HAMNODE1

DISKPART> list disk

Disk ###  Status         Size     Free     Dyn  Gpt

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

Disk 0    Online           40 GB      0 B

Disk 1    Online           20 GB    19 GB

Disk 2    Online         6149 MB  2048 KB

Disk 3    Online         6149 MB  2048 KB

Disk 4    Online         6149 MB  2048 KB

Disk 5    Online           10 GB    10 GB

DISKPART> select disk 5

Disk 5 is now the selected disk.

DISKPART> list part

There are no partitions on this disk to show.

DISKPART> create part ext

DiskPart succeeded in creating the specified partition.

DISKPART> create part log size=196

DiskPart succeeded in creating the specified partition.

DISKPART> create part log

DiskPart succeeded in creating the specified partition.

DISKPART> list part

Partition ###  Type              Size     Offset

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

Partition 0    Extended            10 GB  1024 KB

Partition 1    Logical            196 MB  2048 KB

* Partition 2    Logical              9 GB   199 MB

DISKPART>

分区之后的信息:

wps_clip_image-12445

注:您应该在其他节点上看到同样的磁盘分区信息。且,建议在其他节点上将Windows系统自动给新分区分配的盘符移除掉。

B 准备磁盘,创建裸设备链接

这里的创建磁盘分区如创建OCR磁盘分区时,方法一致:

C:\oracle\product\10.2.0\crs\BIN\GUIOracleOBJManager.exe

wps_clip_image-12507

wps_clip_image-3414

C 查看Vote Disk信息

c:\oracle\product\10.2.0\crs\BIN>crsctl query css votedisk

0.     0    \\.\votedsk1

located 1 votedisk(s).

c:\oracle\product\10.2.0\crs\BIN>

D 备份Vote Disk

c:\oracle\product\10.2.0\crs\BIN>ocopy \\.\votedsk1 c:\oracle\product\10.2.0\crs\cdata\crs\votedisk_140508.bak

C:\ORACLE\PRODUCT\10.2.0\CRS\CDATA\CRS\VOTEDISK_140508.BAK

c:\oracle\product\10.2.0\crs\BIN>cd ..\cdata\crs

c:\oracle\product\10.2.0\crs\cdata\crs>dir

Volume in drive C has no label.

Volume Serial Number is BA2B-5F59

Directory of c:\oracle\product\10.2.0\crs\cdata\crs

05/08/2014  08:37 AM    <DIR>          .

05/08/2014  08:37 AM    <DIR>          ..

05/06/2014  03:24 AM         4,063,232 backup00.ocr

05/05/2014  11:24 PM         4,063,232 backup01.ocr

05/05/2014  07:24 PM         4,063,232 backup02.ocr

05/08/2014  07:43 AM            94,029 backup_ocr_140508.bak

05/02/2014  06:23 AM         4,042,752 day.ocr

05/05/2014  03:24 PM         4,063,232 day_.ocr

05/02/2014  01:04 AM            93,359 hw_backup_ocr.bak

05/08/2014  08:37 AM       205,520,896 VOTEDISK_140508.BAK

04/30/2014  07:09 AM         4,042,752 week.ocr

9 File(s)    230,046,716 bytes

2 Dir(s)  20,299,304,960 bytes free

c:\oracle\product\10.2.0\crs\cdata\crs>

E 停止CRS

c:\oracle\product\10.2.0\crs\BIN>srvctl stop database -d hamrac

c:\oracle\product\10.2.0\crs\BIN>srvctl stop asm -n hamnode1

c:\oracle\product\10.2.0\crs\BIN>srvctl stop asm -n hamnode2

c:\oracle\product\10.2.0\crs\BIN>srvctl stop nodeapps -n hamnode2

c:\oracle\product\10.2.0\crs\BIN>srvctl stop nodeapps -n hamnode1

c:\oracle\product\10.2.0\crs\BIN>crs_stat -t

Name           Type           Target    State     Host

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

ora....SM1.asm application    OFFLINE   OFFLINE

ora....E1.lsnr application    OFFLINE   OFFLINE

ora....de1.gsd application    OFFLINE   OFFLINE

ora....de1.ons application    OFFLINE   OFFLINE

ora....de1.vip application    OFFLINE   OFFLINE

ora....SM2.asm application    OFFLINE   OFFLINE

ora....E2.lsnr application    OFFLINE   OFFLINE

ora....de2.gsd application    OFFLINE   OFFLINE

ora....de2.ons application    OFFLINE   OFFLINE

ora....de2.vip application    OFFLINE   OFFLINE

ora.hamrac.db  application    OFFLINE   OFFLINE

ora....c1.inst application    OFFLINE   OFFLINE

ora....c2.inst application    OFFLINE   OFFLINE

c:\oracle\product\10.2.0\crs\BIN>crsctl stop crs

Stopping resources. This could take several minutes.

Successfully stopped CRS resources.

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

c:\oracle\product\10.2.0\crs\BIN>

注:最后一步停止CRS时,您需要到RAC所有节点上都停止CRS!

F 添加Vote Disk

c:\oracle\product\10.2.0\crs\BIN>crsctl add css votedisk \\.\votedsk2 -force

Now formatting voting disk: \\.\votedsk2

successful addition of votedisk \\.\votedsk2.

c:\oracle\product\10.2.0\crs\BIN>crsctl query css votedisk

0.     0    \\.\votedsk1

1.     0    \\.\votedsk2

located 2 votedisk(s).

c:\oracle\product\10.2.0\crs\BIN>

G 删除原Vote Disk

c:\oracle\product\10.2.0\crs\BIN>crsctl delete css votedisk \\.\votedsk1 -force

successful deletion of votedisk \\.\votedsk1.

c:\oracle\product\10.2.0\crs\BIN>crsctl query css votedisk

0.     0    \\.\votedsk2

located 1 votedisk(s).

c:\oracle\product\10.2.0\crs\BIN>

H 启动CRS并验证

3 迁移ASM DATA磁盘组

A 查看ASM磁盘信息

C:\Users\Administrator>asmtool -list

NTFS                             \Device\Harddisk0\Partition1              100M

NTFS                             \Device\Harddisk0\Partition2            40858M

Oracle Raw Device file           \Device\Harddisk2\Partition1              320M

ORCLDISKDATA1                    \Device\Harddisk2\Partition2             5825M

Oracle Raw Device file           \Device\Harddisk3\Partition1              196M

ORCLDISKFRA0                     \Device\Harddisk3\Partition2             5949M

Oracle Raw Device file           \Device\Harddisk4\Partition1              320M

\Device\Harddisk4\Partition2             5825M

Oracle Raw Device file           \Device\Harddisk5\Partition1              196M

\Device\Harddisk5\Partition2            10044M

C:\Users\Administrator>set oracle_sid=+asm1

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 9 01:57:46 2014

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, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> col path for a30

SQL> set line 120

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

DATA_0001                      NORMAL         5825       4215 \\.\ORCLDISKDATA1

FRA_0000                       NORMAL         5949       5667 \\.\ORCLDISKFRA0

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           5825       4215

FRA                            MOUNTED           5949       5667

SQL>

结合上述,看到DATA磁盘组[目前只有1个ASM磁盘,DATA_0001]对应于\Device\Harddisk2\Partition2设备。

FRA磁盘组[目前也只有1个ASM磁盘,FRA_0000]对应于\Device\Harddisk3\Partition2。

B DATA磁盘组添加磁盘

wps_clip_image-12595

wps_clip_image-31477

wps_clip_image-22238

wps_clip_image-12359

wps_clip_image-21133

wps_clip_image-15974

wps_clip_image-10317

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED          11650      10038

FRA                            MOUNTED           5949       5667

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

DATA_0001                      NORMAL         5825       5019 \\.\ORCLDISKDATA1

FRA_0000                       NORMAL         5949       5667 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       5019 \\.\ORCLDISKDATA0

SQL>

C DATA磁盘组移除旧磁盘

SQL> alter diskgroup data drop disk data_0001;

Diskgroup altered.

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

DATA_0001                      DROPPING       5825       5186 \\.\ORCLDISKDATA1

FRA_0000                       NORMAL         5949       5667 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       4852 \\.\ORCLDISKDATA0

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED          11650      10038

FRA                            MOUNTED           5949       5667

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES

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

1 REBAL RUN           1          1        776        808       1451           0

SQL>

直到最后:

SQL> select * from v$asm_operation;

no rows selected

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           5825       4215

FRA                            MOUNTED           5949       5667

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

NORMAL         5825          0 \\.\ORCLDISKDATA1

FRA_0000                       NORMAL         5949       5667 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       4215 \\.\ORCLDISKDATA0

SQL>

4 迁移ASM FRA磁盘组

A FRA磁盘组添加新磁盘

wps_clip_image-12791

wps_clip_image-4041

wps_clip_image-21263

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

NORMAL         5825          0 \\.\ORCLDISKDATA1

FRA_0000                       NORMAL         5949       5842 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       4215 \\.\ORCLDISKDATA0

FRA_0001                       NORMAL        10044       9867 \\.\ORCLDISKFRA1

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           5825       4215

FRA                            MOUNTED          15993      15709

SQL>

B FRA磁盘组删除旧磁盘

SQL> alter diskgroup fra drop disk fra_0000;

Diskgroup altered.

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           5825       4215

FRA                            MOUNTED          15993      15709

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

NORMAL         5825          0 \\.\ORCLDISKDATA1

FRA_0000                       DROPPING       5949       5920 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       4215 \\.\ORCLDISKDATA0

FRA_0001                       NORMAL        10044       9789 \\.\ORCLDISKFRA1

SQL> select * from v$asm_operation;

no rows selected

SQL> select name,state,total_mb,free_mb,path from v$asm_disk;

NAME                           STATE      TOTAL_MB    FREE_MB PATH

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

NORMAL         5825          0 \\.\ORCLDISKDATA1

NORMAL         5949          0 \\.\ORCLDISKFRA0

DATA_0000                      NORMAL         5825       4215 \\.\ORCLDISKDATA0

FRA_0001                       NORMAL        10044       9762 \\.\ORCLDISKFRA1

SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

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

DATA                           MOUNTED           5825       4215

FRA                            MOUNTED          10044       9762

SQL>

其他具体操作步骤类似。

5 删除磁盘分区

删除卷、磁盘分区信息之后:

wps_clip_image-19324

最终,对于OCR、Vote Disk的数据,我们分别通过添加镜像的方式,然后删除现有的配置,进而间接的将OCR、Vote Disk分别从Disk 2、Disk 3迁移到Disk 4和Disk 5。

对于存放在ASM 磁盘的DATA和FRA中的数据,我们是通过先对现有DATA、FRA 磁盘组分别添加ASM磁盘,然后删除磁盘组中原有的旧ASM磁盘,进而完成迁移ASM 磁盘数据的目标!

6 注意事项

1 Note:

Note:

You can migrate the RAC OCR when CRS is running ,but you cannot migrate the Voting Disk when the CRS is running.That means we must stop the CRS before we can migrate the Voting Disk.

2 Note:

Also,you must backup the voting disk when the CRS is running,or else if you shutdown the CRS first,and then you cannot use the ocopy command to backup the voting disk.

3 Note:

PROT-22: Storage too small

wps_clip_image-21036

Cause:

The ocrmirror disk space is less than the ocr disk space.

wps_clip_image-2601

Solution:

Enlarge the ocrmirror disk space as the same as the ocr disk space.

4 Note:

PROT-21: Invalid parameter

wps_clip_image-27194

Cause:

PROT-21 error is encountered because the cluster is not yet fully aware of the newly created logical drive that is being specified for <filename> in the ocrconfig -replace command.

Solution:[Doc ID 1059663.1]

This code defect is fixed in patch bundle 31 (and higher) for Oracle on Windows.
Patch 31 is available for download from My Oracle Support under the following patch numbers:

Microsoft Windows (32-bit) – 9233830
Microsoft Windows Itanium (64-bit) – 9233831
Microsoft Windows x64 (64-bit) – 9233832

There is also a relatively simple workaround available.  Namely, stop and restart the Oracle Object Service after creating the symbolic link for the new logical drive and before running the ‘ocrconfig -replace’ command.

Windows下如何查看ASM磁盘对应的设备?

如题,在Windows系统下维护ASM磁盘提供存储设备的Oracle数据库时,该如何确定ASM磁盘对应的磁盘设备呢?

1 在操作系统层面看到下述磁盘信息,Disk 0是操作系统盘,Disk 1,Disk 2是2块ASM磁盘:

wps_clip_image-11545

2 从ASM实例里,看到磁盘组、磁盘的信息如下:

SQL> select name,total_mb,free_mb,state from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB STATE
------------------------------ ---------- ---------- -----------
ORADATA                            206849      32661 MOUNTED

SQL> select name,total_mb,free_mb,state from v$asm_disk;

NAME                             TOTAL_MB    FREE_MB STATE
------------------------------ ---------- ---------- --------
                                   206849          0 NORMAL
ORADATA_0001                       206849      32661 NORMAL

SQL> select name,total_mb,free_mb,state,path from v$asm_disk;

NAME                             TOTAL_MB    FREE_MB STATE    PATH
------------------------------ ---------- ---------- -------- --------------------
                                   206849          0 NORMAL   \\.\ORCLDISKORADATA0
ORADATA_0001                       206849      32661 NORMAL   \\.\ORCLDISKDATA0

SQL>

上述看到的信息已经是对ORADATA磁盘组执行过drop disk之后看到的信息。

那么接下来,如果想要OS级别移除ORADATA磁盘里path为\\.\ORCLDISKORADATA0的磁盘的话,该如何确定该磁盘对应OS级别的磁盘呢?究竟是Disk 1还是Disk2呢?

3 方法1:

C:\Users\Administrator>asmtool -list
NTFS                             \Device\Harddisk0\Partition1           278973M
ORCLDISKDATA0                    \Device\Harddisk1\Partition1           206849M
ORCLDISKORADATA0                 \Device\Harddisk2\Partition1           206849M

C:\Users\Administrator>

方法2:

通过DBCA进入ASM管理[Oracle 10g版本,11g应该使用ASMCA]之后,选择给磁盘stamp disk之后,也可以看到下述信息:

wps_clip_image-11662

进而,均可以可定path为\\.\ORCLDISKORADATA0的ASM磁盘对应于OS上的Disk2。

4 通过上述第3步骤移除磁盘的stamp操作之后,看到的信息如下:

wps_clip_image-11786

此时,已经从ASM实例里看不到ASM磁盘的信息了。

5 最终,从OS级别删除磁盘Disk 2的卷信息,分区信息之后,如下:

wps_clip_image-11848

附:在Linux上,可以直接通过/usr/sbin/oracleasm querydisk /dev/sd*的方法来查找ASM磁盘对应OS级别的设备信息。

一步一步在Linux上升级10gR2 RAC到11gR2 RAC(8-完)

升级后修改/etc/orataboracle用户环境变量

将双节点的/etc/oratab文件中的下列

devdb:/u01/app/oracle/product/10.2.0/db_1:N

改为新的11g配置信息。

节点1改为:

devdb1:/u01/app/oracle/product/10.2.0/db_1:N

节点改为:

devdb2:/u01/app/oracle/product/10.2.0/db_1:N

 

修改双节点oracle用户的/home/oracle/.bash_profile配置文件:

节点1修改后:

节点2修改后:

 

修改cluster_database=true

关闭数据库,修改cluster_database=true之后,重启。

SQL> select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>         

 

node1-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initdevdb1.ora 

SPFILE=’+DATA/devdb/spfiledevdb.ora’

node1-> 

 

节点1重启:

node1-> env|grep ORA

ORA_CRS_HOME=/u01/app/oracle/product/10.2.0/crs_1

ORACLE_SID=devdb1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 27 16:08:20 2013

 

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

 

Connected to an idle instance.

 

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1219260416 bytes

Fixed Size                  2252744 bytes

Variable Size             452984888 bytes

Database Buffers          754974720 bytes

Redo Buffers                9048064 bytes

Database mounted.

Database opened.

SQL> show parameter cluster_database

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

SQL> 

 

节点启动数据库:

 

将数据库、实例、服务重新配置到Grid Infrastructure下管理

添加数据库:

node1-> srvctl config database -d devdb

PRCD-1120 : The resource for database devdb could not be found.

PRCR-1001 : Resource ora.devdb.db does not exist

node1->

 

添加:

node1-> srvctl add database -d devdb -o /u01/app/oracle/product/11.2.0/db_1 -c RAC -m oracleonlinux.cn -p +DATA/devdb/spfiledevdb.ora -y automatic -a data,fra

node1-> 

 

添加之后:

node1-> srvctl config database -d devdb

Database unique name: devdb

Database name: 

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/devdb/spfiledevdb.ora

Domain: oracleonlinux.cn

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: devdb

Database instances: 

Disk Groups: DATA,FRA

Mount point paths: 

Services: 

Type: RAC

Database is administrator managed

node1-> 

 

添加实例:

node1-> srvctl add instance -h

 

Adds a database instance configuration to the Oracle Clusterware.

 

Usage: srvctl add instance -d <db_unique_name> -i <inst_name> -n <node_name> [-f]

    -d <db_unique_name>      Unique name for the database

    -i <inst>                Instance name

    -n <node_name>           Node name

    -f                       Force the add operation even though some resource(s) will be stopped

    -h                       Print usage

node1-> srvctl add instance -d devdb -i devdb1 -n node1

node1-> srvctl add instance -d devdb -i devdb2 -n node2

 

添加服务:

 

验证

重启双节点OS

 

启动服务:

node1-> srvctl start service -d devdb -s rac11g

node1-> crs_stat -t

 

 

看到此时SCAN IP浮动到节点2

 

 

Q:如何处理gsd服务为OFFLINE的情况?

A:可以参照我之前的一篇文章:

http://www.oracleonlinux.cn/2012/03/how_to_start_11gr2_rac_oc4j_gsd_service/

 

最后,可以考虑将双节点上的原10g clusterware以及数据库软件删除掉!

此处不再给予赘述展示!!!!

一步一步在Linux上升级10gR2 RAC到11gR2 RAC(7)

开始正式升级

Note

11g 软件来启库,且已UPGRADE方式,注意环境变量的设置!

执行$ORACLE_HOME/rdbms/admin/catupgrd.sql来升级!

 

node1-> export ORACLE_SID=devdb1                                  

node1-> export ORACLE_BASE=/u01/app/oracle                       

node1-> export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                      

node1-> which sqlplus

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 27 13:01:19 2013

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade   

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1219260416 bytes

Fixed Size                  2252744 bytes

Variable Size             385876024 bytes

Database Buffers          822083584 bytes

Redo Buffers                9048064 bytes

Database mounted.

Database opened.

SQL> spool /home/oracle/u_info4.log

SQL> @?/rdbms/admin/catupgrd

…..

……

……

.

Oracle Database 11.2 Post-Upgrade Status Tool           12-27-2013 14:12:22

.

Component                               Current      Version     Elapsed Time

Name                                    Status       Number      HH:MM:SS

.

Oracle Server

.                                         VALID      11.2.0.4.0  00:13:37

JServer JAVA Virtual Machine

.                                         VALID      11.2.0.4.0  00:10:44

Oracle Real Application Clusters

.                                         VALID      11.2.0.4.0  00:00:00

Oracle Workspace Manager

.                                         VALID      11.2.0.4.0  00:00:37

OLAP Analytic Workspace

.                                         VALID      11.2.0.4.0  00:00:17

OLAP Catalog

.                                         VALID      11.2.0.4.0  00:00:50

Oracle OLAP API

.                                         VALID      11.2.0.4.0  00:00:29

Oracle Enterprise Manager

.                                         VALID      11.2.0.4.0  00:07:15

Oracle XDK

.                                         VALID      11.2.0.4.0  00:03:15

Oracle Text

.                                         VALID      11.2.0.4.0  00:00:46

Oracle XML Database

.                                         VALID      11.2.0.4.0  00:05:15

Oracle Database Java Packages

.                                         VALID      11.2.0.4.0  00:00:19

Oracle Multimedia

.                                         VALID      11.2.0.4.0  00:03:41

Spatial

.                                         VALID      11.2.0.4.0  00:05:15

Oracle Expression Filter

.                                         VALID      11.2.0.4.0  00:00:12

Oracle Rules Manager

.                                         VALID      11.2.0.4.0  00:00:09

Final Actions

.                                                                00:00:43

Total Upgrade Time: 00:53:32

 

PL/SQL procedure successfully completed.

 

SQL> 

SQL> SET SERVEROUTPUT OFF

SQL> SET VERIFY ON

SQL> commit;

 

Commit complete.

 

SQL> 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

SQL> 

SQL> 

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The above sql script is the final step of the upgrade. Please

DOC>   review any errors in the spool log file. If there are any errors in

DOC>   the spool file, consult the Oracle Database Upgrade Guide for

DOC>   troubleshooting recommendations.

DOC>

DOC>   Next restart for normal operation, and then run utlrp.sql to

DOC>   recompile any invalid application objects.

DOC>

DOC>   If the source database had an older time zone version prior to

DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade

DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC>   with Oracle.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SQL> 

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL> 

SQL> REM END OF CATUPGRD.SQL

SQL> 

SQL> REM bug 12337546 – Exit current sqlplus session at end of catupgrd.sql.

SQL> REM                This forces user to start a new sqlplus session in order

SQL> REM                to connect to the upgraded db.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

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

Data Mining and Real Application Testing options

node1-> 

 

大概整个升级过程持续 00:53:32

提示检查升级日志,去查看/home/oracle/u_info4.log且需要处理掉错误!!!!!!!

检查日志过程中,之前在步骤尝试升级数据库时遇到的错误已经不存在,且亦无其它错误出现。

 

执行catuppst.sql执行升级后操作

执行 @$ORACLE_HOME/rdbms/admin/catuppst.sql

 

node1-> hostname

node1.oracleonlinux.cn

node1-> export ORACLE_SID=devdb1                                  

node1-> export ORACLE_BASE=/u01/app/oracle                       

node1-> export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                      

node1-> which sqlplus 

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 27 15:25:11 2013

 

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

 

Connected to an idle instance.

 

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1219260416 bytes

Fixed Size                  2252744 bytes

Variable Size             503316536 bytes

Database Buffers          704643072 bytes

Redo Buffers                9048064 bytes

Database mounted.

Database opened.

SQL> spool /home/oracle/u5.log

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

 

TIMESTAMP

——————————————————————————–

COMP_TIMESTAMP POSTUP_BGN 2013-12-27 15:26:38

 

 

PL/SQL procedure successfully completed.

 

 

This script will migrate the Baseline data on a pre-11g database

to the 11g database.

 

 

…..

…..

SQL> PROMPT Updating registry…

Updating registry…

SQL> INSERT INTO registry$history

  2    (action_time, action,

  3     namespace, version, id,

  4     bundle_series, comments)

  5  VALUES

  6    (SYSTIMESTAMP, ‘APPLY’,

  7     SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),

  8     ‘11.2.0.4’,

  9     0,

 10     ‘PSU’,

 11     ‘Patchset 11.2.0.2.0’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> SPOOL off

SQL> SET echo off

Check the following log file for errors:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DEVDB_APPLY_2013Dec27_15_26_50.log

SQL> 

 

根据提示检查/home/oracle/u5.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DEVDB_APPLY_2013Dec27_15_26_50.log均无错误!!!!

编译失效对象

执行 @$ORACLE_HOME/rdbms/admin/utlrp.sql来编译失效对象

 

SQL> spool /home/oracle/u6.log

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

TIMESTAMP

————————————————————————————————————–

COMP_TIMESTAMP UTLRP_BGN  2013-12-27 15:31:15

 

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;

DOC>#

 

PL/SQL procedure successfully completed.

 

 

TIMESTAMP

————————————————————————————————————–

COMP_TIMESTAMP UTLRP_END  2013-12-27 15:33:55

 

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

 

OBJECTS WITH ERRORS

——————-

                  0

 

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

 

ERRORS DURING RECOMPILATION

—————————

                          0

 

 

Function created.

 

 

PL/SQL procedure successfully completed.

 

 

Function dropped.

 

 

PL/SQL procedure successfully completed.

 

SQL>

升级后确认监听

确保listener是运行在11gR2 Grid路径下:

node1-> lsnrctl status

 

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 27-DEC-2013 15:39:46

 

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

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 – Production

Start Date                26-DEC-2013 22:12:55

Uptime                    0 days 17 hr. 26 min. 51 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/grid/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.33)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.35)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

  Instance “+ASM1”, status READY, has 1 handler(s) for this service…

Service “devdb.oracleonlinux.cn” has 1 instance(s).

  Instance “devdb1”, status READY, has 2 handler(s) for this service…

Service “devdbXDB.oracleonlinux.cn” has 1 instance(s).

  Instance “devdb1”, status READY, has 1 handler(s) for this service…

The command completed successfully

node1-> 

一步一步在Linux上升级10gR2 RAC到11gR2 RAC(6)

11 升级10gR2 RAC数据库至11gR2 RAC数据库

升级前的准备工作:

确保原10g RAC数据库在双节点上都已经正常关闭,如果没关闭,请确保在所有节点上都关闭。

B Copy 10gR2 RAC下的初始化参数文件、口令文件、网络配置文件至11gR2 软件下对应的目录

节点1

node1-> cp /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0/db_1/network/admin/

node1-> cp /u01/app/oracle/product/10.2.0/db_1/dbs/initdevdb1.ora /u01/app/oracle/product/11.2.0/db_1/dbs/  

node1-> cp /u01/app/oracle/product/10.2.0/db_1/dbs/orapwdevdb1 /u01/app/oracle/product/11.2.0/db_1/dbs/

 

节点2

node2-> cp /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0/db_1/network/admin/

node2-> cp /u01/app/oracle/product/10.2.0/db_1/dbs/initdevdb2.ora /u01/app/oracle/product/11.2.0/db_1/dbs/

node2-> cp /u01/app/oracle/product/10.2.0/db_1/dbs/orapwdevdb2 /u01/app/oracle/product/11.2.0/db_1/dbs/

node2-> 

Note 1

请将cluster_database=false,如果不修改该参数的话,在下一步中,用11gR2版本的数据库软件来拉起10gRAC库的时候,您会遭遇下述错误:

即,在启动数据库的过程中,发现Hang

检查alert日志:

node1-> tail -f /u01/app/oracle/diag/rdbms/devdb/devdb1/trace/alert_devdb1.log 

minact-scn: Inst 1 is now the master inc#:2 mmon proc-id:24159 status:0x7

minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb1/trace/devdb1_ora_24227.trc:

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb1/trace/devdb1_ora_24227.trc:

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE

Error 39701 happened during db open, shutting down database

USER (ospid: 24227): terminating the instance due to error 39701

Instance terminated by USER, pid = 24227

ORA-1092 signalled during: ALTER DATABASE OPEN MIGRATE…

 

node1-> oerr ora 39701

39701, 00000, “database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE”

// *Cause:  The database was mounted for SHARED cluster access.

// *Action: Set the CLUSTER_DATABASE initialization parameter to FALSE and

//          restart the server with the UPGRADE or DOWNGRADE option.

node1-> 

 

Note2

如果不将原10g RAC下的/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora文件拷贝至11g RAC的下u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora,在下一步中,您将遭遇下述错误:

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 26 10:57:22 2013

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdevdb1.ora’; 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-00119: invalid specification for system parameter REMOTE_LISTENER

ORA-00132: syntax error or unresolved network name ‘LISTENERS_DEVDB’

SQL> 

尝试升级数据库

这里通过执行$ORACL_HOME/rdbms/admin/catupgrd.sql来尝试升级数据库。

其实,在这步骤里应该是过不去,因为源库需要执行升级前预检查的工作!

预检查脚本:

Note

要用新装的11g 11204版本的数据库软件来拉10gRAC的库,注意环境变量的设置,且要以UPGRADE的方式来拉库

 

node1-> hostname

node1.oracleonlinux.cn

node1-> export ORACLE_SID=devdb1                                  

node1-> export ORACLE_BASE=/u01/app/oracle                       

node1-> export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                      

node1-> which sqlplus 

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 27 10:27:20 2013

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1219260416 bytes

Fixed Size                  2252744 bytes

Variable Size             385876024 bytes

Database Buffers          822083584 bytes

Redo Buffers                9048064 bytes

Database mounted.

Database opened.

SQL> 

然后执行$ORACL_HOME/rdbms/admin/catupgrd.sql脚本来升级数据库:

SQL> spool /home/oracle/upgrade_info.log

SQL> @?/rdbms/admin/catupgrd

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The first time this script is run, there should be no error messages

DOC>   generated; all normal upgrade error messages are suppressed.

DOC>

DOC>   If this script is being re-run after correcting some problem, then

DOC>   expect the following error which is not automatically suppressed:

DOC>

DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>              possibly in conjunction with

DOC>   ORA-06512: at “<procedure/function name>”, line NN

DOC>

DOC>   These errors will automatically be suppressed by the Database Upgrade

DOC>   Assistant (DBUA) when it re-runs an upgrade.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an “ORA-01722: invalid number”

DOC>    error if the user running this script is not SYS.  Disconnect

DOC>    and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>######################################################################

DOC>#

 

no rows selected

 

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an “ORA-01722: invalid number”

DOC>    error if the database server version is not correct for this script.

DOC>    Perform “ALTER SYSTEM CHECKPOINT” prior to “SHUTDOWN ABORT”, and use

DOC>    a different script or a different server.

DOC>######################################################################

DOC>######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an “ORA-01722: invalid number”

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform “ALTER SYSTEM CHECKPOINT” prior to “SHUTDOWN ABORT”,  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>     The following statement will cause an “ORA-01722: invalid number”

DOC>     error if the Oracle Database Vault option is TRUE.  Upgrades cannot

DOC>     be run with the Oracle Database Vault option set to TRUE since

DOC>     AS SYSDBA connections are restricted.

DOC>

DOC>     Perform “ALTER SYSTEM CHECKPOINT” prior to “SHUTDOWN ABORT”, relink

DOC>     the server without the Database Vault option, and restart the server

DOC>     using UPGRADE mode.

DOC>

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an “ORA-01722: invalid number”

DOC>   error if Database Vault is installed in the database but the Oracle

DOC>   Label Security option is FALSE.  To successfully upgrade Oracle

DOC>   Database Vault, the Oracle Label Security option must be TRUE.

DOC>

DOC>   Perform “ALTER SYSTEM CHECKPOINT” prior to “SHUTDOWN ABORT”,

DOC>   relink the server with the OLS option (but without the Oracle Database

DOC>   Vault option) and restart the server using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an “ORA-01722: invalid number”

DOC>   error if bootstrap migration is in progress and logminer clients

DOC>   require utlmmig.sql to be run next to support this redo stream.

DOC>

DOC>   Run utlmmig.sql

DOC>   then (if needed)

DOC>   restart the database using UPGRADE and

DOC>   rerun the upgrade script.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

no rows selected

 

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following error is generated if the pre-upgrade tool has not been

DOC>   run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:

DOC>

DOC>   SELECT TO_NUMBER(‘MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE’)

DOC>                       *

DOC>      ERROR at line 1:

DOC>      ORA-01722: invalid number

DOC>

DOC>     o Action:

DOC>       Shutdown database (“alter system checkpoint” and then “shutdown abort”).

DOC>       Revert to the original oracle home and start the database.

DOC>       Run pre-upgrade tool against the database.

DOC>       Review and take appropriate actions based on the pre-upgrade

DOC>       output before opening the datatabase in the new software version.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

 

Session altered.

 

 

Table created.

 

 

Table altered.

 

SELECT TO_NUMBER(‘MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE’)

                 *

ERROR at line 1:

ORA-01722: invalid number

 

 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

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

Data Mining and Real Application Testing options

node1->

果不其然,发现报错!!!

执行预检查脚本

根据上一步的错误提示:

我们要以原来10gRAC环境的数据库软件来启动数据库,去执行1个升级前的预检查脚本:

Note

该预检查脚本是要把低版本的数据库升级到11204版本的数据库需要执行预检查的脚本,可以参考MetaLink官方文档:Doc ID 837570.1Note 884522.1

这里将utlu112i_7.sql脚本下载至/home/oracle路径下。

这里要以原来10gRAC环境的数据库软件来启动数据库、并且以UPGRADE方式来启库、且需要注意环境变量需要设置成对应原10g RAC数据库软件的配置。

同样,记得需要修改10g参数文件里的cluster_database=false

 

 

Note

11gR2版本下,oracle会自动创建一个隐含参数__oracle_base,而在10g版本下不支持该参数。当然,如果不去掉该参数的话,那么您可能会遇到下述错误:

node1-> export ORACLE_SID=devdb1                                

node1-> export ORACLE_BASE=/u01/app/oracle                      

node1-> export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1  

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                     

node1-> which sqlplus                                           

/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Dec 27 10:46:12 2013

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade

ORA-01078: failure in processing system parameters

LRM-00101: unknown parameter name ‘__oracle_base’

SQL>

 

解决:

通过11g创建1pfile,然后将pfile里的初始化参数去掉:

node1-> export ORACLE_SID=devdb1                                  

node1-> export ORACLE_BASE=/u01/app/oracle                       

node1-> export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                      

node1-> which sqlplus                                              

/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 27 10:47:09 2013

 

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

 

Connected to an idle instance.

 

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area 1219260416 bytes

Fixed Size                  2252744 bytes

Variable Size             385876024 bytes

Database Buffers          822083584 bytes

Redo Buffers                9048064 bytes

SQL> create pfile=’/home/oracle/pfile.txt’ from spfile;

 

修改注释掉/home/oracle/pfile.txt里的下述参数:

devdb1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

以及修改

*.cluster_database=false

再根据提示,重新执行,参数文件指向新生成的且已修改的/home/oracle/pfile.txt

node1-> export ORACLE_SID=devdb1                               

node1-> export ORACLE_BASE=/u01/app/oracle                     

node1-> export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 

node1-> export PATH=$ORACLE_HOME/bin:$PATH.                    

node1-> which sqlplus                                          

/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus

node1-> sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Dec 27 10:49:18 2013

 

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

 

Connected to an idle instance.

 

SQL> startup upgrade pfile=’/home/oracle/pfile.txt’;      

ORACLE instance started.

 

Total System Global Area 1224736768 bytes

Fixed Size                  2095896 bytes

Variable Size             318768360 bytes

Database Buffers          889192448 bytes

Redo Buffers               14680064 bytes

Database mounted.

Database opened.

SQL> spool /home/oracle/u2.log   

SQL> @/home/oracle/utlu112i_7

Oracle Database 11.2 Pre-Upgrade Information Tool 12-27-2013 10:50:45

Script Version: 11.2.0.4.0 Build: 007

.

**********************************************************************

Database:

**********************************************************************

–> name:          DEVDB

–> version:       10.2.0.5.0

–> compatible:    10.2.0.5.0

–> blocksize:     8192

–> platform:      Linux x86 64-bit

–> timezone file: V4

.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

–> SYSTEM tablespace is adequate for the upgrade.

…. minimum required size: 942 MB

–> UNDOTBS1 tablespace is adequate for the upgrade.

…. minimum required size: 400 MB

–> SYSAUX tablespace is adequate for the upgrade.

…. minimum required size: 731 MB

–> TEMP tablespace is adequate for the upgrade.

…. minimum required size: 60 MB

.

**********************************************************************

Flashback: OFF

**********************************************************************

**********************************************************************

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note: Pre-upgrade tool was run on a lower version 64-bit database.

**********************************************************************

–> If Target Oracle is 32-Bit, refer here for Update Parameters:

— No update parameter changes are required.

.

 

–> If Target Oracle is 64-Bit, refer here for Update Parameters:

— No update parameter changes are required.

.

**********************************************************************

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

— No renamed parameters found. No changes are required.

.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

**********************************************************************

–> background_dump_dest         11.1       DEPRECATED   replaced by  “diagnostic_dest”

–> user_dump_dest               11.1       DEPRECATED   replaced by  “diagnostic_dest”

.

 

**********************************************************************

Components: [The following database components will be upgraded or installed]

**********************************************************************

–> Oracle Catalog Views         [upgrade]  VALID

–> Oracle Packages and Types    [upgrade]  VALID

–> JServer JAVA Virtual Machine [upgrade]  VALID

–> Oracle XDK for Java          [upgrade]  VALID

–> Real Application Clusters    [upgrade]  VALID

–> Oracle Workspace Manager     [upgrade]  VALID

–> OLAP Analytic Workspace      [upgrade]  VALID

–> OLAP Catalog                 [upgrade]  VALID

–> EM Repository                [upgrade]  VALID

–> Oracle Text                  [upgrade]  VALID

–> Oracle XML Database          [upgrade]  VALID

–> Oracle Java Packages         [upgrade]  VALID

–> Oracle interMedia            [upgrade]  VALID

–> Spatial                      [upgrade]  VALID

–> Data Mining                  [upgrade]  VALID

–> Expression Filter            [upgrade]  VALID

–> Rule Manager                 [upgrade]  VALID

–> Oracle OLAP API              [upgrade]  VALID

.

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: –> Database is using a timezone file older than version 14.

…. After the release migration, it is recommended that DBMS_DST package

…. be used to upgrade the 10.2.0.5.0 database timezone version

…. to the latest version which comes with the new release.

WARNING: –> Database contains INVALID objects prior to upgrade.

…. The list of invalid SYS/SYSTEM objects was written to

…. registry$sys_inv_objs.

…. The list of non-SYS/SYSTEM objects was written to

…. registry$nonsys_inv_objs.

…. Use utluiobj.sql after the upgrade to identify any new invalid

…. objects due to the upgrade.

…. USER MDSYS has 18 INVALID objects.

…. USER DMSYS has 2 INVALID objects.

…. USER PUBLIC has 352 INVALID objects.

…. USER CTXSYS has 1 INVALID objects.

…. USER ORACLE_OCM has 2 INVALID objects.

…. USER EXFSYS has 8 INVALID objects.

…. USER DBSNMP has 4 INVALID objects.

…. USER SYSMAN has 3 INVALID objects.

…. USER XDB has 3 INVALID objects.

…. USER SYS has 429 INVALID objects.

…. USER WMSYS has 25 INVALID objects.

WARNING: –> EM Database Control Repository exists in the database.

…. Direct downgrade of EM Database Control is not supported. Refer to the

…. Upgrade Guide for instructions to save the EM data prior to upgrade.

WARNING: –> Your recycle bin is turned on and currently contains no objects.

…. Because it is REQUIRED that the recycle bin be empty prior to upgrading

…. and your recycle bin is turned on, you may need to execute the command:

        PURGE DBA_RECYCLEBIN

…. prior to executing your upgrade to confirm the recycle bin is empty.

.

**********************************************************************

Recommendations

**********************************************************************

Oracle recommends gathering dictionary statistics prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

 

    EXECUTE dbms_stats.gather_dictionary_stats;

 

**********************************************************************

Oracle recommends removing all hidden parameters prior to upgrading.

 

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

 

    SELECT name,description from SYS.V$PARAMETER WHERE name

        LIKE ‘\_%’ ESCAPE ‘\’

 

Changes will need to be made in the init.ora or spfile.

 

**********************************************************************

SQL> 

 

看到有几个警告,分别是时区、用户的无效对象、以及回收站、数据字典的统计信息,我们可以忽略之!!!!

在这里我们把数据库正常关闭、准备正式升级数据库!!!

一步一步在Linux上升级10gR2 RAC到11gR2 RAC(5)

10 安装11gR2 oracle软件

解压oracle 11g 11.2.0.4.0版本的数据库软件:

解压前2个压缩文件,解压后copy database文件至/home/oracle目录:

[root@node1 ~]# unzip p13390677_112040_Linux-x86-64_1of7.zip 

[root@node1 ~]# unzip p13390677_112040_Linux-x86-64_2of7.zip 

Oracle用户登录图形界面,执行安装:

 

进入安装界面:

 

Next

 

Next

 

Next

 

解决安装RAC数据库软件时,OUI找不到节点:

node1-> pwd

/u01/app/oracle/oraInventory/ContentsXML

node1-> ll

total 12

-rwxrwx— 1 oracle oinstall 329 Dec 25 21:11 comps.xml

-rwxrwx— 1 oracle oinstall 924 Dec 25 21:11 inventory.xml

-rwxrwx— 1 oracle oinstall 292 Dec 25 21:11 libs.xml

node1-> cat inventory.xml 

<?xml version=”1.0″ standalone=”yes” ?>

<!– Copyright (c) 1999, 2013, Oracle and/or its affiliates.

All rights reserved. –>

<!– Do not modify the contents of this file by hand. –>

<INVENTORY>

<VERSION_INFO>

   <SAVED_WITH>11.2.0.4.0</SAVED_WITH>

   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<HOME NAME=”crs” LOC=”/u01/app/oracle/product/10.2.0/crs_1″ TYPE=”O” IDX=”1″ CRS=”true”>

   <NODE_LIST>

      <NODE NAME=”node1″/>

      <NODE NAME=”node2″/>

   </NODE_LIST>

</HOME>

<HOME NAME=”db” LOC=”/u01/app/oracle/product/10.2.0/db_1″ TYPE=”O” IDX=”2″>

   <NODE_LIST>

      <NODE NAME=”node1″/>

      <NODE NAME=”node2″/>

   </NODE_LIST>

</HOME>

<HOME NAME=”Ora11g_gridinfrahome1″ LOC=”/u01/app/11.2.0/grid” TYPE=”O” IDX=”3″ CRS=”true”>

   <NODE_LIST>

      <NODE NAME=”node1″/>

      <NODE NAME=”node2″/>

   </NODE_LIST>

</HOME>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

node1-> 

在双节上去掉该文件的下述部分:

<HOME NAME=”crs” LOC=”/u01/app/oracle/product/10.2.0/crs_1″ TYPE=”O” IDX=”1″ CRS=”true”>

   <NODE_LIST>

      <NODE NAME=”node1″/>

      <NODE NAME=”node2″/>

   </NODE_LIST>

</HOME>

重新执行安装:

 

Next


Next

 

Next:输入第4步骤中创建的ORACLE_HOME目录:/u01/app/oracle/product/11.2.0/db_1

 

Next

 

Next

 

Next

 

Next

 

Next

 

节点1

[root@node1 ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh 

Performing root user operation for Oracle 11g 

 

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/db_1

 

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]: 

The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

 

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 script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@node1 ~]# 

节点2

[root@node2 ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh 

Performing root user operation for Oracle 11g 

 

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/db_1

 

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]: 

The file “oraenv” already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

The file “coraenv” already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

 

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 script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

[root@node2 ~]# 

一步一步在Linux上升级10gR2 RAC到11gR2 RAC(4)

迁移10g RAC 磁盘组至11gR2 grid

Oracle用户调用/u01/app/11.2.0/grid/bin/asmca 进入ASMCA图形界面来将之前10gR2 RAC下的2ASM磁盘添加到11gR2 grid软件下来进行管理。

:为什么可以把存放10g数据库的磁盘放在11g ASM实例来管理?

:可以参考本人之前文章如何在一台服务器上共用一个ASM实例来创建10.2.0.5和11gR2的物理备库

 

node1-> /u01/app/11.2.0/grid/bin/asmca 

 

Mountdatafra磁盘组:

 

 

节点1通过SQL*PLUS来查看:

 

其中,v$ASM_DISK中看到的name问空的前2条记录是10g RAC下的存放OCRVoting Disk的磁盘,不予理会。

同时,通过11g grid infrastructure下的crs_stat命令也可以查看datafra磁盘组的信息:

node2-> /u01/app/11.2.0/grid/bin/crs_stat -t

Name           Type           Target    State     Host        

————————————————————

ora.DATA.dg    ora….up.type ONLINE    ONLINE    node1       

ora.FRA.dg     ora….up.type ONLINE    ONLINE    node1       

ora.GRIDDG.dg  ora….up.type ONLINE    ONLINE    node1       

ora….ER.lsnr ora….er.type ONLINE    ONLINE    node1       

ora….N1.lsnr ora….er.type ONLINE    ONLINE    node1       

ora.asm        ora.asm.type   ONLINE    ONLINE    node1       

ora.cvu        ora.cvu.type   ONLINE    ONLINE    node1       

ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               

ora….network ora….rk.type ONLINE    ONLINE    node1       

ora….SM1.asm application    ONLINE    ONLINE    node1       

ora….E1.lsnr application    ONLINE    ONLINE    node1       

ora.node1.gsd  application    OFFLINE   OFFLINE               

ora.node1.ons  application    ONLINE    ONLINE    node1       

ora.node1.vip  ora….t1.type ONLINE    ONLINE    node1       

ora….SM2.asm application    ONLINE    ONLINE    node2       

ora….E2.lsnr application    ONLINE    ONLINE    node2       

ora.node2.gsd  application    OFFLINE   OFFLINE               

ora.node2.ons  application    ONLINE    ONLINE    node2       

ora.node2.vip  ora….t1.type ONLINE    ONLINE    node2       

ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    node1       

ora.ons        ora.ons.type   ONLINE    ONLINE    node1       

ora….ry.acfs ora….fs.type ONLINE    ONLINE    node1       

ora.scan1.vip  ora….ip.type ONLINE    ONLINE    node1       

node2->