分类目录归档：Oracle

oracle 12.1 RAC的ocr磁盘组异常恢复

发表于 2013 年 11 月 13 日由惜分飞

在11.2或者12.1的RAC中,ocr和votedisk可以放到asm中,而很多人安装系统把ocr和votedisk放到一个单独的asm 磁盘组里面,但是如果这个磁盘组坏了,而数据所在的磁盘组是好的,这个时候该怎么恢复呢？这里的恢复分两种情况，一种是有ocr备份的恢复,另外一种是无ocr备份的恢复。但是在一般情况下ocr是每4个小时自动备份一份，因此大部分的系统中都会有ocr的备份。本blog主要对于oracle 12c rac在有ocr备份，存储ocr,votedisk的asm磁盘组异常恢复
确定ocr,votedisk,asm spfile存在一个独立asm diskgroup中

[grid@xifenfei ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1360
         Available space (kbytes) :     408208
         ID                       : 2132096904
         Device/File Name         :    +DG_SYS
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user


SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DG_SYS/xff-cluster/ASMPARAMET
                                                 ERFILE/registry.253.825628977

[grid@xifenfei ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   3e20d13ae98a4fcfbffa489ab4df68a3 (/dev/sdc2) [DG_SYS]
Located 1 voting disk(s).

ASMCMD>  lsdsk -t -G dg_sys
Create_Date  Mount_Date  Repair_Timer  Path
08-SEP-13    08-SEP-13   0             /dev/sdc2

查看当前rac状态

[grid@xifenfei ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.DATA.dg
               ONLINE  ONLINE       xifenfei                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.net1.network
               ONLINE  ONLINE       xifenfei                 STABLE
ora.ons
               ONLINE  ONLINE       xifenfei                 STABLE
ora.proxy_advm
               ONLINE  OFFLINE      xifenfei                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       xifenfei                 169.254.196.108 10.1
                                                             0.30.22,STABLE
ora.asm
      1        ONLINE  ONLINE       xifenfei                 Started,STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cdb.db
      1        ONLINE  ONLINE       xifenfei                 Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.oc4j
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.xifenfei.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
--------------------------------------------------------------------------------

kfed查看磁盘头

[grid@xifenfei ~]$ kfed read /dev/sdc2
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  2879801080 ; 0x00c: 0xaba646f8
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                202375168 ; 0x020: 0x0c100000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:             DG_SYS_0000 ; 0x028: length=11
kfdhdb.grpname:                  DG_SYS ; 0x048: length=6
kfdhdb.fgname:              DG_SYS_0000 ; 0x068: length=11
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32990483 ; 0x0a8: HOUR=0x13 DAYS=0x8 MNTH=0x9 YEAR=0x7dd
kfdhdb.crestmp.lo:            303455232 ; 0x0ac: USEC=0x0 MSEC=0x197 SECS=0x21 MINS=0x4
kfdhdb.mntstmp.hi:             32990485 ; 0x0b0: HOUR=0x15 DAYS=0x8 MNTH=0x9 YEAR=0x7dd
kfdhdb.mntstmp.lo:           1776845824 ; 0x0b4: USEC=0x0 MSEC=0x221 SECS=0x1e MINS=0x1a
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    5451 ; 0x0c4: 0x0000154b
kfdhdb.pmcnt:                         3 ; 0x0c8: 0x00000003
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                     10 ; 0x0d4: 0x0000000a
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32990483 ; 0x0e4: HOUR=0x13 DAYS=0x8 MNTH=0x9 YEAR=0x7dd
kfdhdb.grpstmp.lo:            301063168 ; 0x0e8: USEC=0x0 MSEC=0x77 SECS=0x1f MINS=0x4
kfdhdb.vfstart:                     224 ; 0x0ec: 0x000000e0
kfdhdb.vfend:                       256 ; 0x0f0: 0x00000100
kfdhdb.spfile:                      219 ; 0x0f4: 0x000000db   ----asm spfile的起点
kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001
kfdhdb.flags:                         1 ; 0x0fc: 0x00000001

备份ocr

[root@xifenfei xff-cluster]# ocrconfig  -manualbackup

xifenfei     2013/09/08 23:48:57     /u01/app/12.1/grid/product/cdata/xff-cluster/backup_20130908_234857.ocr

[root@xifenfei xff-cluster]# ocrconfig -showbackup

xifenfei     2013/08/08 21:11:00     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/backup00.ocr

xifenfei     2013/08/08 17:10:56     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/backup01.ocr

xifenfei     2013/07/08 20:23:18     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/backup02.ocr

xifenfei     2013/08/08 17:10:56     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/day.ocr

xifenfei     2013/08/08 17:10:56     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/week.ocr

xifenfei     2013/09/08 23:48:57     /u01/app/12.1/grid/product/cdata/xff-cluster/backup_20130908_234857.ocr

xifenfei     2013/06/28 22:55:02     /u01/app/12.1/grid/product/cdata/xifenfe-cluster/backup_20130628_225502.ocr

破坏asm disk

[grid@xifenfei ~]$ dd if=/dev/zero of=/dev/sdc2 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 6.6061e-05 seconds, 62.0 MB/s

关闭crs

[root@xifenfei xff-cluster]# crsctl stop crs

启动crs

[root@xifenfei xff-cluster]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[grid@xifenfei admin]$ crsctl status res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.cluster_interconnect.haip
      1        ONLINE  OFFLINE                               STABLE
ora.crf
      1        ONLINE  OFFLINE                               STABLE
ora.crsd
      1        ONLINE  OFFLINE                               STABLE
ora.cssd
      1        ONLINE  OFFLINE      xifenfei                 STARTING
ora.cssdmonitor
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.ctssd
      1        ONLINE  OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.evmd
      1        ONLINE  INTERMEDIATE xifenfei                 STABLE
ora.gipcd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.gpnpd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.mdnsd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.storage
      1        ONLINE  OFFLINE                               STABLE
--------------------------------------------------------------------------------

GI相关日志

--alert日志
2013-09-08 23:53:37.662:
[gpnpd(1507)]CRS-2328:GPNPD started on node xifenfei.
2013-09-08 23:54:10.244:
[cssd(1584)]CRS-1713:CSSD daemon is started in hub mode
2013-09-08 23:54:10.915:
[cssd(1584)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/12.1/grid/product/log/xifenfei/cssd/ocssd.log
2013-09-08 23:54:11.183:
[ohasd(1367)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE
2013-09-08 23:54:11.183:
[ohasd(1367)]CRS-2769:Unable to failover resource 'ora.diskmon'.
2013-09-08 23:54:26.044:
[cssd(1584)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/12.1/grid/product/log/xifenfei/cssd/ocssd.log
2013-09-08 23:54:41.146:
[cssd(1584)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/12.1/grid/product/log/xifenfei/cssd/ocssd.log
2013-09-08 23:54:56.195:
[cssd(1584)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/12.1/grid/product/log/xifenfei/cssd/ocssd.log

--ocssd日志
2013-09-08 23:54:25.976: [    GPNP][1090226496]clsgpnp_profileCallUrlInt: [at clsgpnp.c:2360] Result: (0) CLSGPNP_OK. Successful get-profile CALL to remote "ipc://GPNPD_xifenfei" disco ""
2013-09-08 23:54:25.976: [    CSSD][1090226496]clssnmReadDiscoveryProfile: voting file discovery string(/dev/sd*)
2013-09-08 23:54:25.976: [    CSSD][1090226496]clssnmvDDiscThread: using discovery string /dev/sd* for initial discovery
2013-09-08 23:54:25.976: [   SKGFD][1090226496]Discovery with str:/dev/sd*:

2013-09-08 23:54:25.976: [   SKGFD][1090226496]UFS discovery with :/dev/sd*:

2013-09-08 23:54:26.032: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdc:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdc1:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdb:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdc2:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdd:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sdd1:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sda:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sda1:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]Fetching UFS disk :/dev/sda2:

2013-09-08 23:54:26.037: [   SKGFD][1090226496]OSS discovery with :/dev/sd*:

2013-09-08 23:54:26.042: [   SKGFD][1090226496]Handle 0x1d65c10 from lib :UFS:: for disk :/dev/sdb:

2013-09-08 23:54:26.043: [   SKGFD][1090226496]Handle 0x20c95a0 from lib :UFS:: for disk :/dev/sdc2:

2013-09-08 23:54:26.043: [   SKGFD][1090226496]Handle 0x20c9dd0 from lib :UFS:: for disk :/dev/sdd:

2013-09-08 23:54:26.044: [   SKGFD][1090226496]Lib :UFS:: closing handle 0x1d65c10 for disk :/dev/sdb:

2013-09-08 23:54:26.044: [   SKGFD][1090226496]Lib :UFS:: closing handle 0x20c95a0 for disk :/dev/sdc2:

2013-09-08 23:54:26.044: [   SKGFD][1090226496]Lib :UFS:: closing handle 0x20c9dd0 for disk :/dev/sdd:

2013-09-08 23:54:26.044: [    CSSD][1090226496]clssnmvDiskVerify: Successful discovery of 0 disks
2013-09-08 23:54:26.044: [    CSSD][1090226496]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2013-09-08 23:54:26.044: [    CSSD][1090226496]clssnmvFindInitialConfigs: No voting files found
2013-09-08 23:54:26.044: [    CSSD][1090226496](:CSSNM00070:)clssnmCompleteInitVFDiscovery: Voting file not found. Retrying discovery in 15 seconds

在我们破坏了ocr所在的asm disk的磁盘后,启动crs明显提示无法找到votedisk信息

强制关闭crs

[root@xifenfei xff-cluster]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'xifenfei'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'xifenfei'
CRS-2677: Stop of 'ora.drivers.acfs' on 'xifenfei' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'xifenfei' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'xifenfei'
CRS-2673: Attempting to stop 'ora.gipcd' on 'xifenfei'
CRS-2673: Attempting to stop 'ora.evmd' on 'xifenfei'
CRS-2677: Stop of 'ora.gpnpd' on 'xifenfei' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'xifenfei' succeeded
CRS-2677: Stop of 'ora.evmd' on 'xifenfei' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei' has completed
CRS-4133: Oracle High Availability Services has been stopped.

exclusive模式启动crs

[root@xifenfei xff-cluster]# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'xifenfei'
CRS-2677: Stop of 'ora.drivers.acfs' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'xifenfei'
CRS-2672: Attempting to start 'ora.mdnsd' on 'xifenfei'
CRS-2676: Start of 'ora.evmd' on 'xifenfei' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'xifenfei'
CRS-2676: Start of 'ora.gpnpd' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'xifenfei'
CRS-2672: Attempting to start 'ora.gipcd' on 'xifenfei'
CRS-2676: Start of 'ora.cssdmonitor' on 'xifenfei' succeeded
CRS-2676: Start of 'ora.gipcd' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'xifenfei'
CRS-2672: Attempting to start 'ora.diskmon' on 'xifenfei'
CRS-2676: Start of 'ora.diskmon' on 'xifenfei' succeeded
CRS-2676: Start of 'ora.cssd' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'xifenfei'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'xifenfei'
CRS-2672: Attempting to start 'ora.ctssd' on 'xifenfei'
CRS-2676: Start of 'ora.ctssd' on 'xifenfei' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'xifenfei' succeeded
CRS-2676: Start of 'ora.drivers.acfs' on 'xifenfei' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'xifenfei'
CRS-2676: Start of 'ora.asm' on 'xifenfei' succeeded


[grid@xifenfei xifenfei]$ crsctl stat res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  INTERMEDIATE xifenfei                 OCR not started,STAB
                                                             LE
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.crf
      1        OFFLINE OFFLINE                               STABLE
ora.crsd
      1        OFFLINE OFFLINE                               STABLE
ora.cssd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.cssdmonitor
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.ctssd
      1        ONLINE  ONLINE       xifenfei                 ACTIVE:0,STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.evmd
      1        ONLINE  INTERMEDIATE xifenfei                 STABLE
ora.gipcd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.gpnpd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.mdnsd
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.storage
      1        OFFLINE OFFLINE                               STABLE

创建磁盘组

[grid@xifenfei xifenfei]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 00:23:40 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create diskgroup DG_OCR external redundancy disk '/dev/sdc2' attribute 'COMPATIBLE.ASM' = '12.1.0';

Diskgroup created.

[root@xifenfei xff-cluster]# ls -l
total 1472
-rw-r--r-- 1 root root 1503232 Sep  8 23:48 backup_20130908_234857.ocr
[root@xifenfei xff-cluster]# ocrconfig -restore backup_20130908_234857.ocr
PROT-35: The configured OCR locations are not accessible

SQL> conn / as sysasm
Connected.
SQL> drop diskgroup DG_OCR force including contents;

Diskgroup dropped.

SQL>  create diskgroup DG_SYS  external redundancy disk '/dev/sdc2' attribute 'COMPATIBLE.ASM' = '12.1.0';

Diskgroup created.

为了操作方便,建议创建磁盘组和以前ocr所在异常的磁盘组一致

还原ocr

[root@xifenfei xff-cluster]# ocrconfig -restore backup_20130908_234857.ocr

--ALERT 日志
2013-09-09 00:26:50.584:
[client(3015)]CRS-1002:The OCR was restored from file backup_20130908_234857.ocr.

处理votedisk

[root@xifenfei xff-cluster]# crsctl replace votedisk +DG_SYS
Successful addition of voting disk 60a037da30714f6bbfe5d90206ff27a7.
Successfully replaced voting disk group with +DG_SYS.
CRS-4266: Voting file(s) successfully replaced

创建asm spfile

[grid@xifenfei dbs]$ vi /tmp/asm.txt 
instance_type='asm'
large_pool_size=12M
remote_login_passwordfile= "EXCLUSIVE"
asm_diskstring           = "/dev/sd*"
asm_power_limit          = 1

[grid@xifenfei dbs]$ sqlplus '/ as sysasm'

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 00:34:18 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>  create spfile='+DG_SYS' FROM pfile='/tmp/asm.txt';

File created.

重启crs

[root@xifenfei xff-cluster]# crsctl stop crs -f

[root@xifenfei xff-cluster]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[grid@xifenfei dbs]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.DATA.dg
               ONLINE  ONLINE       xifenfei                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.net1.network
               ONLINE  ONLINE       xifenfei                 STABLE
ora.ons
               ONLINE  ONLINE       xifenfei                 STABLE
ora.proxy_advm
               ONLINE  OFFLINE      xifenfei                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       xifenfei                 169.254.196.108 10.1
                                                             0.30.22,STABLE
ora.asm
      1        ONLINE  ONLINE       xifenfei                 Started,STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cdb.db
      1        ONLINE  ONLINE       xifenfei                 Open,STABLE
ora.cvu
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.oc4j
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
ora.xifenfei.vip
      1        ONLINE  ONLINE       xifenfei                 STABLE
--------------------------------------------------------------------------------

这里crs已经恢复正常，进一步检查ocr,votedisk,asm spfile情况

[grid@xifenfei ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1380
         Available space (kbytes) :     408188
         ID                       : 2132096904
         Device/File Name         :    +DG_SYS
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

[grid@xifenfei ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 16:12:21 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile; 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DG_SYS/xff-cluster/ASMPARAMET
                                                 ERFILE/registry.253.825640465
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[grid@xifenfei ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   60a037da30714f6bbfe5d90206ff27a7 (/dev/sdc2) [DG_SYS]
Located 1 voting disk(s).

至此在ocr 磁盘组异常,而有ocr备份的情况下故障恢复完毕，对于没有ocr备份的故障，只能通过重建ocr来完成，大概步骤为

--deconfigure(root)
remote node
# <$GRID_HOME>/crs/install/rootcrs.pl -deconfig -force -verbose

lastnode
# <$GRID_HOME>/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode

--配置信息重建ocr等(grid)
# $GRID_HOME/crs/config/config.sh

发表在 ORACLE 12C, Oracle RAC | 标签为 ocr异常恢复 | 评论关闭

证明递归session存在并解释为什么不在v$session中显示

发表于 2013 年 11 月 8 日由惜分飞

我们在数据库的使用过程中,有时候会遇到类似情况,我会话是登录的,但是我进行某种操作,缺报session不足.这种情况证明该sql后台还产生了其他会话,这里通过试验分析证明了递归session的存在
会话创建表报session超

CDB_PDB@CHF>  create table t_xifenfei(id number) ;
create table t_xifenfei(id number)
                 
ERROR at line 1:
ORA-00018: maximum number of sessions exceeded

这里有个问题:当前会话已经登录成功了，证明当前session是足够的，但是为什么在执行创建表操作之时依然会报ORA-00018呢？通过10046继续分析

CDB_PDB@CHF> alter session set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

会话已更改。

CDB_PDB@CHF> create table t_xifenfei as select * from dual;

表已创建。

CDB_PDB@CHF> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_ora_6596.trc

分析trace文件

CDB_PDB@CHF> host tkprof  E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_ora_6596.trc d:/1.txt

--查看trace文件，发现里面有很多基表操作，拿其中的一个tab$表分析，创建表过程有如下insert操作
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,
  audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
  avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,
  dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)
values
(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,
  :9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
  decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)

尝试人工插入

CDB_PDB@CHF> insert into sys.tab$ select * from sys.tab$ where rownum=1;
insert into sys.tab$ select * from sys.tab$ where rownum=1
                *
第 1 行出现错误:
ORA-01031: 权限不足

证明当前执行创建表的session无权限直接操作tab$表，证明应该有其他表操作它

v$session视图基表
通过查询V$FIXED_VIEW_DEFINITION视图获得相关sql语句,不同版本可能有出入,但是大体一致

/* Formatted on 2013/11/8 23:09:30 (QP5 v5.227.12220.39754) */
SELECT inst_id,
       addr,
       indx,
       ksuseser,
       ksuudses,
       ksusepro,
       ksuudlui,
       ksuudlna,
       ksuudoct,
       ksusesow,
       DECODE (ksusetrn, HEXTORAW ('00'), NULL, ksusetrn),
       DECODE (ksqpswat, HEXTORAW ('00'), NULL, ksqpswat),
       DECODE (BITAND (ksuseidl, 11),
               1, 'ACTIVE',
               0, DECODE (BITAND (ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
               2, 'SNIPED',
               3, 'SNIPED',
               'KILLED'),
       DECODE (ksspatyp,
               1, 'DEDICATED',
               2, 'SHARED',
               3, 'PSEUDO',
               'NONE'),
       ksuudsid,
       ksuudsna,
       ksuseunm,
       ksusepid,
       ksusemnm,
       ksusetid,
       ksusepnm,
       DECODE (BITAND (ksuseflg, 19),
               17, 'BACKGROUND',
               1, 'USER',
               2, 'RECURSIVE',
               '?'),
       ksusesql,
       ksusesqh,
       ksusepsq,
       ksusepha,
       ksuseapp,
       ksuseaph,
       ksuseact,
       ksuseach,
       ksusecli,
       ksusefix,
       ksuseobj,
       ksusefil,
       ksuseblk,
       ksuseslt,
       ksuseltm,
       ksusectm,
       DECODE (BITAND (ksusepfl, 16), 0, 'NO', 'YES'),
       DECODE (ksuseft,
               2, 'SESSION',
               4, 'SELECT',
               8, 'TRANSACTIONAL',
               'NONE'),
       DECODE (ksusefm,
               1, 'BASIC',
               2, 'PRECONNECT',
               4, 'PREPARSE',
               'NONE'),
       DECODE (ksusefs, 1, 'YES', 'NO'),
       ksusegrp,
       DECODE (BITAND (ksusepfl, 16),
               16, 'ENABLED',
               DECODE (BITAND (ksusepfl, 32), 32, 'FORCED', 'DISABLED')),
       DECODE (BITAND (ksusepfl, 64),
               64, 'FORCED',
               DECODE (BITAND (ksusepfl, 128), 128, 'DISABLED', 'ENABLED')),
       DECODE (BITAND (ksusepfl, 512),
               512, 'FORCED',
               DECODE (BITAND (ksusepfl, 256), 256, 'DISABLED', 'ENABLED')),
       ksusecqd,
       ksuseclid
  FROM x$ksuse
 WHERE BITAND (ksspaflg, 1) != 0 AND BITAND (ksuseflg, 1) != 0

注意:v$session查询的肯定是BITAND (ksuseflg, 1)!=0的记录

通过锁住表测试
CDB_PDB@SYS表示sys用户,CDB_PDB@CHF表示chf用户,使用两个session,不同用户测试

CDB_PDB@SYS> show user;
USER 为 "SYS"

--SYS用户锁住表
CDB_PDB@SYS> lock table tab$ IN exclusive MODE;

表已锁定。

CDB_PDB@CHF> show user;
USER 为 "CHF"
CDB_PDB@CHF>  select sid from v$mystat where rownum=1;

       SID
----------
        57

CDB_PDB@CHF> select paddr from v$session where sid=57;

PADDR
----------------
000007FF1E10F228

--CHF用户创建表
CDB_PDB@CHF>  create table t_xifenfei_new as select * from dual;

--SYS用户查询
CDB_PDB@SYS> SELECT s.addr,
  2         s.indx sid,
  3         s.ksuseser SERIAL#,
  4         ksuudsna username,
  5         DECODE (BITAND (ksuseflg, 19),
  6                 17, 'BACKGROUND',
  7                 1, 'USER',
  8                 2, 'RECURSIVE',
  9                 '?')
 10            TYPE
 11    FROM x$ksuse s
 12   WHERE ksusepro = '000007FF1E10F228';

ADDR                    SID    SERIAL# USERNAME                       TYPE
---------------- ---------- ---------- ------------------------------ ----------
000007FF1E1EBEA0         57         23 CHF                            USER
000007FF1E1D7F90         67        183 SYS                            RECURSIVE

CDB_PDB@SYS> SELECT ksuudsna username,
  2         ksuseflg
  3    FROM x$ksuse s
  4   WHERE ksusepro = '000007FF1E10F228';

USERNAME                         KSUSEFLG
------------------------------ ----------
CHF                             135266369
SYS                                     2

--这里我们发现递归sys调用的sql,在v$session视图中被排除了,因此递归sql的session不能在v$session显示
CDB_PDB@SYS> select bitand(2,1) from dual;

BITAND(2,1)
-----------
          0

至此，我们可以验证，我们当前的会话，在创建表的过程中有一个sys的递归session执行了关于基表的操作,但是由于v$session视图对于x$ksuse表中的部分记录进行了过滤因此我们不能在v$session查看到这些递归session

继续分析bitand函数
通过观察v$session的创建语句，我们可以发现如下规律,如果某个session是递归session,那么BITAND (ksuseflg, 19)=2,那当这个值为2的时候，是不是BITAND (ksuseflg, 1)一定为0呢？bitand函数实际上就是把里面的两个参数转换为二进制然后进行and运算,也就是两个对应位都为1的情况才会结果得带1(bitand(3,1)=1,bitand(2,1)=0),这里可以发现19转换为二进制为10011,要使得BITAND (ksuseflg, 19)=2成立,那就是说ksuseflg转换为二进制后,最后一位必须是0;而BITAND (ksuseflg, 1)在这样的情况下,一定为0,因此递归session的一定不会在v$session视图显示.

发表在 Oracle | 评论关闭

因RAC的undo_management参数不一致导致数据库mount报ORA-01105 ORA-01606

发表于 2013 年 10 月 27 日由惜分飞

环境Linux 5.8 10.2.0.5 RAC,两个节点只能一个节点mount,如果尝试mount另外节点就报ORA-01105和ORA-01606错误
数据库版本

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

crs资源情况

[oracle@node1 dbs]$ $ORA_CRS_HOME/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....D1.inst application    OFFLINE   OFFLINE               
ora....D2.inst application    ONLINE    ONLINE    node2       
ora.PROD.db    application    ONLINE    ONLINE    node2       
ora....SM1.asm application    ONLINE    ONLINE    node1       
ora....E1.lsnr application    ONLINE    ONLINE    node1       
ora.node1.gsd  application    ONLINE    ONLINE    node1       
ora.node1.ons  application    ONLINE    ONLINE    node1       
ora.node1.vip  application    ONLINE    ONLINE    node1       
ora....SM2.asm application    ONLINE    ONLINE    node2       
ora....E2.lsnr application    ONLINE    ONLINE    node2       
ora.node2.gsd  application    ONLINE    ONLINE    node2       
ora.node2.ons  application    ONLINE    ONLINE    node2       
ora.node2.vip  application    ONLINE    ONLINE    node2

节点1 mount报错

SQL> startup
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: gc_files_to_locks not identical to that of another mounted instance

Error:    ORA 1105 
Text:     mount is incompatible with mounts by other instances 
-------------------------------------------------------------------------------
Cause:  An attempt was made to mount the database, but another instance has already mounted 
        a database by the same name, and the mounts are not compatible.
        dditional messages will accompany this message to report why the mounts are incompatible.
Action:  See the accompanying messages for the appropriate action to take.

Error:  ORA 1606 
Text:   GC_FILES_TO_LOCKS not identical to that of another mounted instance 
-------------------------------------------------------------------------------
Cause:  The initialization parameter GC_FILES_TO_LOCKS is not the same as 
        another instance mounted in parallel mode.
        This parameter must be the same as that for all shared instances.
Action: Modify the parameter to be compatible with the other instances, then 
        shut down and restart the instance.

根据这个错误提示，查询两个节点的gc_files_to_locks参数，均为空值(默认值),也就是值相同

SQL> show parameter gc_files_to_locks;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
gc_files_to_locks                    string

检查两个节点的gc相关隐含参数,发现所有值也均一致

NAME                           DESCRIPTION                                                           VALUE  
------------------------------ --------------------------------------------------------------------- -------
_gc_affinity_limit             dynamic affinity limit                                                50     
_gc_affinity_minimum           dynamic affinity minimum activity per minute                          6000   
_gc_affinity_time              if non zero, enable dynamic object affinity                           10     
_gc_async_memcpy               if TRUE, use async memcpy                                             FALSE  
_gc_check_bscn                 if TRUE, check for stale blocks                                       TRUE   
_gc_coalesce_recovery_reads    if TRUE, coalesce recovery reads                                      TRUE   
_gc_defer_time                 how long to defer down converts for hot buffers                       3      
_gc_dissolve_undo_affinity     if TRUE, dissolve undo affinity after an offline                      FALSE  
_gc_dynamic_affinity_locks     if TRUE, get dynamic affinity locks                                   TRUE   
_gc_element_percent            global cache element percent                                          103    
_gc_global_lru                 turn global lru off, make it automatic, or turn it on                 AUTO   
_gc_initiate_undo_affinity     if TRUE, initiate undo affinity after an online                       TRUE   
_gc_integrity_checks           set the integrity check level                                         1      
_gc_keep_recovery_buffers      if TRUE, make recovery buffers current                                TRUE   
_gc_latches                    number of latches per LMS process                                     8      
_gc_maximum_bids               maximum number of bids which can be prepared                          0      
_gcs_fast_reconfig             if TRUE, enable fast reconfiguration for gcs locks                    TRUE   
_gcs_latches                   number of gcs resource hash latches to be allocated per LMS process   64     
_gcs_pkey_history              number of pkey remastering history                                    4000   
_gcs_process_in_recovery       if TRUE, process gcs requests during instance recovery                TRUE   
_gcs_resources                 number of gcs resources to be allocated                                      
_gcs_shadow_locks              number of pcm shadow locks to be allocated                                   
_gc_statistics                 if TRUE, kcl statistics are maintained                                TRUE   
_gcs_testing                   GCS testing parameter                                                 0      
_gc_tsn_undo_affinity          if TRUE, use TSN undo affinity                                        TRUE   
_gc_undo_affinity              if TRUE, enable dynamic undo affinity                                 TRUE   
_gc_undo_affinity_locks        if TRUE, get affinity locks for undo                                  TRUE   
_gc_use_cr                     if TRUE, allow CR pins on PI and WRITING buffers                      TRUE   
_gc_vector_read                if TRUE, vector read current buffers                                  TRUE

仔细对比数据库参数，发现undo异常

--节点1
SQL>  show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      SYSTEM


--节点2
SQL>  show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

这里已经明确，因为两个节点的undo_*相关参数配置不正确，导致数据库只能一个节点mount。进一步定位问题发现，原来是因为dba粗心在编辑节点1的参数文件的时候把undo_*相关的参数给弄丢了,从而数据库使用了默认值undo_management=manual,undo_tablespace=system

发表在 ORA-xxxxx | 标签为 ORA-01105, ORA-01606 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle

oracle 12.1 RAC的ocr磁盘组异常恢复

证明递归session存在并解释为什么不在v$session中显示

因RAC的undo_management参数不一致导致数据库mount报ORA-01105 ORA-01606

文章分类

最近发表

分类目录归档：Oracle

oracle 12.1 RAC的ocr磁盘组异常恢复

证明递归session存在并解释为什么不在v$session中显示

因RAC的undo_management参数不一致导致数据库mount报ORA-01105 ORA-01606

标签云

文章分类

最近发表