分类目录归档：Oracle

替换bootstarp$表

发表于 2013 年 12 月 15 日由惜分飞

对数据库有一定了解的人都知道，数据库启动是通过bootstarp$表来实现启动引导的。那这里有两个疑问：
1.引导表bootstarp$名字是否是唯一？
2.引导表的位置是不是在数据文件固定？比如11g/12c在file 1 block 520，10g在file 1 block 377？

跟踪数据库启动

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 15 14:16:58 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 Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

SQL> startup mount;
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> alter session set db_file_multiblocK_read_count=1;
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Session altered.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_31364.trc
SQL> alter database Open;

Database altered.
SQL> oradebug EVENT 10046 trace name context off
Statement processed.

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='BOOTSTRAP$';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1          520

SQL> select object_id from dba_objects where object_name='BOOTSTRAP$';

 OBJECT_ID
----------
        59

分析trace文件

WAIT #140077386411120: nam='db file sequential read' ela= 56 file#=1 block#=520 blocks=1 obj#=-1 tim=1719385755334
=====================
PARSING IN CURSOR #140077386402760 len=188 dep=1 uid=0 oct=1 lid=0 tim=1719385757322 hv=4006182593 ad='6645d370' sqlid='32r4f1brckzq1'
create table bootstrap$ (
END OF STMT
PARSE #140077386402760:c=2000,e=1711,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1719385757319
EXEC #140077386402760:c=0,e=430,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1719385757909
CLOSE #140077386402760:c=0,e=9,dep=1,type=0,tim=1719385758105
=====================
PARSING IN CURSOR #140077386402760 len=55 dep=1 uid=0 oct=3 lid=0 tim=1719385759507 hv=2111436465 ad='6645bc80' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #140077386402760:c=1000,e=1365,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1719385759505
BINDS #140077386402760:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f664edb8780  bln=22  avl=02  flg=05
  value=59
EXEC #140077386402760:c=3000,e=8859,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1719385768574
WAIT #140077386402760: nam='db file sequential read' ela= 27 file#=1 block#=520 blocks=1 obj#=59 tim=1719385768753
WAIT #140077386402760: nam='db file sequential read' ela= 23 file#=1 block#=521 blocks=1 obj#=59 tim=1719385769575

这里我们可以发现，数据库启动的时候是读file 1 block 520，object_id为59，为bootstarp$对象

bbed查看root rdba地址

ub4 kcvfhrdb                             @96       0x00400208

SQL> select to_number('208','xxx') from dual;

TO_NUMBER('208','XXX')
----------------------
                   520

这里可以看出来，数据库启动的file header rdba地址和trace文件中的一致，也是指定到bootstarp$;

替换bootstrap$表为xifenfei

SQL> create table xifenfei as select * from bootstrap$;

Table created.

SQL> desc xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LINE#                                     NOT NULL NUMBER
 OBJ#                                      NOT NULL NUMBER
 SQL_TEXT                                  NOT NULL VARCHAR2(4000)

SQL> select sql_text from xifenfei where line#=59;

SQL_TEXT
--------------------------------------------------------------------------------
CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT
" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
 (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJN
O 59 EXTENTS (FILE 1 BLOCK 520))

--清除bootstarp$记录
SQL> delete from xifenfei where line#=59;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select objECT_ID from dba_objects where object_name='XIFENFEI';

 OBJECT_ID
----------
     20314

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='XIFENFEI';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1        45712


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
Database opened.

--删除bootstarp$表
SQL>  drop table bootstrap$;

Table dropped.

SQL>  exec DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP('XIFENFEI');

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Dec 15 14:29:54 2013

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

Connected to an idle instance.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2288872 bytes
Variable Size             310379288 bytes
Database Buffers           79691776 bytes
Redo Buffers                8486912 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> alter session set db_file_multiblocK_read_count=1;

Session altered.

SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_ora_32727.trc
SQL> alter database Open;

Database altered.

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='BOOTSTRAP$';

no rows selected

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='XIFENFEI'; 

 OBJECT_ID
----------
     20314

分析trace文件

WAIT #139841534602352: nam='db file sequential read' ela= 23 file#=1 block#=45712 blocks=1 obj#=-1 tim=1720082484775
=====================
PARSING IN CURSOR #139841534593992 len=193 dep=1 uid=0 oct=1 lid=0 tim=1720082488552 hv=2096904950 ad='6645d650' sqlid='bs6v55xygsfrq'
create table bootstrap$ (
END OF STMT
PARSE #139841534593992:c=2000,e=2925,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1720082488550
EXEC #139841534593992:c=0,e=466,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1720082489124
CLOSE #139841534593992:c=0,e=8,dep=1,type=0,tim=1720082489266
=====================
PARSING IN CURSOR #139841534593992 len=55 dep=1 uid=0 oct=3 lid=0 tim=1720082490510 hv=2111436465 ad='6645c050' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #139841534593992:c=1999,e=1211,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1720082490509
BINDS #139841534593992:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f2f64fe8780  bln=22  avl=04  flg=05
  value=20314
EXEC #139841534593992:c=1000,e=1789,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1720082492533
WAIT #139841534593992: nam='db file sequential read' ela= 20 file#=1 block#=45712 blocks=1 obj#=20314 tim=1720082492685
WAIT #139841534593992: nam='db file sequential read' ela= 25 file#=1 block#=45713 blocks=1 obj#=20314 tim=1720082492986

这里可以看出来，数据库在启动的时候是读取file 1 block 45712，object_id为20314

bbed查看root rdba地址

ub4 kcvfhrdb                             @96       0x0040b290

SQL> select to_number('b290','xxxxxxx') from dual;

TO_NUMBER('B290','XXXXXXX')
---------------------------
                      45712

证明现在的文件头里面的file header rdba 已经修改为file 1 block 45712和trace里面看到的一致,都是XIFENFEI这个表

测试结论
1. 通过使用SWAP_BOOTSTRAP可以置换掉数据库启动开始表bootstrap$变为另外一个表,而且该过程直接修改文件头的kcvfhrdb值
2. 通过试验证明,oracle启动的时候不是程序里面写死的去读file 1的某个block,而是通过读取kcvfhrdb然后启动数据库

发表在 Oracle | 2 条评论

一次数据库优化全过程分析

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

最近对客户的一个数据库进行了优化，在本次优化过程中，主要涉及以下方面：
1. 确保系统有足够的内存,处理方法配置Hugepage,减小SGA
2. 优化因为主键表频繁插入引起的user$,con$,cdef$递归查询sql

SQL> select c.name, u.name from con$ c, cdef$ cd, user$ u where 
   2 c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user#;


Execution Plan
----------------------------------------------------------
Plan hash value: 2409458995

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |  3820 |   164K|    38   (6)| 00:00:01 |
|*  1 |  HASH JOIN          |       |  3820 |   164K|    38   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | USER$ |    64 |   896 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |       |  3820 |   111K|    34   (3)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| CDEF$ |  3820 | 34380 |    25   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| CON$  |  6368 |   130K|     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------

具体见:一次数据库优化全过程分析

发表在 Oracle性能优化 | 标签为数据库优化 | 6 条评论

ORACLE 12C RAC修改ocr/votedisk/asm spfile所在磁盘组名称

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

今天看着我这个单节点的12C rac,突然觉得ocr所在的磁盘组叫做+DG_SYS有点不舒服，想改成+SYS_DG。处理方法是先把ocr/votedisk/asm spfile迁移到已经存在的asm中,然后修改磁盘组名称,最后迁移到新名称磁盘组中(本次处理流程+DG_SYS—>+DATA—>+SYS_DG)
当前运行情况

[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
--------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DG_SYS                         MOUNTED
DATA                           MOUNTED

[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).

[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

SQL> show parameter spfile;

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

修改ocr路径
ocrconfig -add和ocrconfig -delete完成ocr更换磁盘组,该过程可以在线处理

[root@xifenfei ~]# ocrconfig -add +data
--alert 日志
2013-09-09 22:32:40.799: 
[crsd(5064)]CRS-1007:The OCR/OCR mirror location was replaced by +data.

[root@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 Name         :      +data
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

[root@xifenfei ~]# ocrconfig -delete +DG_SYS

--alert 日志
2013-09-09 22:35:53.585: 
[crsd(5064)]CRS-1010:The OCR mirror location +DG_SYS was removed.

[root@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         :      +data
                                    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 succeeded

修改votedisk路径
通过crsctl replace votedisk命令修改

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

-alert日志
2013-09-09 22:38:15.259: 
[cssd(4685)]CRS-1605:CSSD voting file is online: /dev/sdb; details in /u01/app/12.1/grid/product/log/xifenfei/cssd/ocssd.log.
2013-09-09 22:38:15.259: 
[cssd(4685)]CRS-1626:A Configuration change request completed successfully
2013-09-09 22:38:15.285: 
[cssd(4685)]CRS-1601:CSSD Reconfiguration complete. Active nodes are xifenfei .

[root@xifenfei ~]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   161ddea0a5fe4f28bfb67536e6105122 (/dev/sdb) [DATA]
Located 1 voting disk(s).

修改asm spfile位置

[grid@xifenfei ~]$  gpnptool get -o-

Success.
…………
<orcl:ASM-Profile id="asm" DiscoveryString="/dev/sd*" SPFile="+DG_SYS/xff-cluster/ASMPARAMETERFILE/registry.253.825640465" Mode="legacy"/>
…………

[grid@xifenfei ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 22:42:05 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 pfile='/tmp/pfile.asm' from spfile;

File created.

SQL> create spfile='+DATA' FROM PFILE='/tmp/pfile.asm';

File created.

[grid@xifenfei ~]$  gpnptool get -o-

Success.
…………
<orcl:ASM-Profile id="asm" DiscoveryString="/dev/sd*" SPFile="+DATA/xff-cluster/ASMPARAMETERFILE/registry.253.825720159" Mode="legacy"/>
…………

这里证明create asm spfile会自动修改spfile在gpnptool对应的profile里面的配置,无需人工干预

重启crs
为了使得asm使用新的磁盘组中的spfile文件

[root@xifenfei ~]# crsctl stop crs
[root@xifenfei ~]# crsctl start crs

验证+DG_SYS磁盘组未被使用

[grid@xifenfei ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 22:59:49 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      +DATA/xff-cluster/ASMPARAMETER
                                                 FILE/registry.253.825720159

ASMCMD> lsof
DB_Name  Instance_Name  Path                                                                      
+ASM     +ASM1          +DATA.255.819326577                                                       
cdb      cdb1           +DATA/CDB/CONTROLFILE/current.274.819356503                               
cdb      cdb1           +DATA/CDB/DATAFILE/sysaux.278.819355829                                   
cdb      cdb1           +DATA/CDB/DATAFILE/system.269.819356101                                   
cdb      cdb1           +DATA/CDB/DATAFILE/undotbs1.276.819356317                                 
cdb      cdb1           +DATA/CDB/DATAFILE/users.279.819356309                                    
cdb      cdb1           +DATA/CDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/pdbseed_temp01.dbf    
cdb      cdb1           +DATA/CDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.272.819356709  
cdb      cdb1           +DATA/CDB/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.271.819356709  
cdb      cdb1           +DATA/CDB/ONLINELOG/group_1.277.822736453                                 
cdb      cdb1           +DATA/CDB/ONLINELOG/group_2.280.822736461                                 
cdb      cdb1           +DATA/CDB/ONLINELOG/group_3.275.822736397                                 
cdb      cdb1           +DATA/CDB/TEMPFILE/temp.273.819356649

dismount +DG_SYS磁盘组

                                   
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     20480     3369                0            3369              0             Y  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      5451     5231                0            5231              0             N  DG_SYS/
ASMCMD> umount dg_sys
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     20480     3369                0            3369              0             Y  DATA/

修改asm dg名称
修改磁盘组+DG_SYS为+SYS_DG

[grid@xifenfei ~]$ renamedg phase=both dgname=DG_SYS newdgname=SYS_DG verbose=true

Parsing parameters..

Parameters in effect:

         Old DG name       : DG_SYS 
         New DG name          : SYS_DG 
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null) 
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=DG_SYS newdgname=SYS_DG verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk UFS:/dev/sdc2 with disk number:0 and timestamp (32990496 1727895552)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk UFS:/dev/sdc2 with disk number:0 and timestamp (32990496 1727895552)
Checking if the diskgroup is mounted or used by CSS 
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/sdc2
Modifying the header
Completed phase 2
Terminating kgfd context 0x7fceeb02a0a0

mount +SYS_DG

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED
SYS_DG                         DISMOUNTED

SQL> alter diskgroup sys_dg mount;

Diskgroup altered.

SQL>  select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED
SYS_DG                         MOUNTED

asm spfile/ocr/votedisk迁移从+DATA到+SYS_DG

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

File created.

[root@xifenfei ~]# ocrconfig -add +SYS_DG
[root@xifenfei ~]# ocrconfig -DELETE +DATA
[root@xifenfei ~]# crsctl replace votedisk +SYS_DG
Successful addition of voting disk 9694a31053ea4ff4bfb57891461a1296.
Successful deletion of voting disk 161ddea0a5fe4f28bfb67536e6105122.
Successfully replaced voting disk group with +SYS_DG.
CRS-4266: Voting file(s) successfully replaced
[root@xifenfei ~]# crsctl stop crs
[root@xifenfei ~]# crsctl start crs

删除ocr里面老磁盘组(+DG_SYS)信息

[root@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.DG_SYS.dg
               ONLINE  OFFLINE      xifenfei                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei                 STABLE
ora.SYS_DG.dg
               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
--------------------------------------------------------------------------------

[root@xifenfei ~]# srvctl remove diskgroup -diskgroup dg_sys
[root@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.SYS_DG.dg
               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
--------------------------------------------------------------------------------

至此ocr/votedisk/asm spfile所在磁盘组修改名称完成,因为该库是一个单节点的rac,如果是两个或者更多节点的rac可以实现不停机的情况下进行(分步重启节点).该处理过程和11.2 rac完全相同,未有任何的改变

发表在 ORACLE 12C, Oracle RAC | 标签为 ORACLE 12C, oracle rac | 一条评论

17813235971

QQ 咨询

分类目录归档：Oracle

替换bootstarp$表

一次数据库优化全过程分析

ORACLE 12C RAC修改ocr/votedisk/asm spfile所在磁盘组名称

文章分类

最近发表

分类目录归档：Oracle

替换bootstarp$表

一次数据库优化全过程分析

ORACLE 12C RAC修改ocr/votedisk/asm spfile所在磁盘组名称

标签云

文章分类

最近发表