【基础原理】教你如何修改 oracle 11g RAC控制文件路径方法

1.rac集群资源现状
[root@rac1 ~]# crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATADG1.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG2.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG3.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRADG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.SYSTEMDG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.racdb.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open
ora.scan1.vip
1 ONLINE ONLINE rac1

2.创建参数文件备份
SQL> create pfile=’/home/oracle/rac_pfile.ora’ from spfile;

File created.

3.备份控制文件
SQL> col name for a80
SQL> set pages 1000 lines 180
SQL> select inst_id,name from gv$controlfile;

INST_ID NAME
———- ——————————————————————————–
1 +DATADG1/racdb/controlfile/current.256.1090271279
1 +DATADG2/racdb/controlfile/current.256.1090271283
2 +DATADG1/racdb/controlfile/current.256.1090271279
2 +DATADG2/racdb/controlfile/current.256.1090271283

SQL> alter database backup controlfile to ‘/home/oracle/racdb.ctl’;

Database altered.

4.关闭数据库
[oracle@rac1 ~]$ srvctl stop database -d racdb
[root@rac1 ~]# crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATADG1.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG2.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG3.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRADG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.SYSTEMDG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.racdb.db
1 OFFLINE OFFLINE Instance Shutdown
2 OFFLINE OFFLINE Instance Shutdown
ora.scan1.vip
1 ONLINE ONLINE rac1

5.启动节点1到nomount状态
[oracle@rac1 ~]$ srvctl start instance -d racdb -i racdb1 -o nomount
[oracle@rac1 ~]$ srvctl status database -d racdb
实例 racdb1 正在节点 rac1 上运行
实例 racdb2 没有在 rac2 节点上运行

SQL> select status from gv$instance;

STATUS
————
STARTED

5.rman还原控制文件
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jul 14 23:37:13 2022

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

connected to target database: RACDB (not mounted)

RMAN> restore controlfile to ‘+DATADG3’ from ‘+DATADG1/racdb/controlfile/current.256.1090271279’;

Starting restore at 14-JUL-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=racdb1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 14-JUL-22

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jul 14 23:37:13 2022

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

connected to target database: RACDB (not mounted)

RMAN> restore controlfile to ‘+DATADG3’ from ‘+DATADG1/racdb/controlfile/current.256.1090271279′;

Starting restore at 14-JUL-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 instance=racdb1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 14-JUL-22

RMAN>
6.查看还原的控制文件
[grid@rac2 ~]$ asmcmd
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 5120 2882 0 2882 0 N DATADG1/
MOUNTED EXTERN N 512 4096 1048576 5120 4688 0 4688 0 N DATADG2/
MOUNTED EXTERN N 512 4096 1048576 5120 4892 0 4892 0 N DATADG3/
MOUNTED EXTERN N 512 4096 1048576 2048 1867 0 1867 0 N FRADG/
MOUNTED NORMAL N 512 4096 1048576 3072 2146 1024 561 0 Y SYSTEMDG/
ASMCMD> ls
DATADG1/
DATADG2/
DATADG3/
FRADG/
SYSTEMDG/
ASMCMD> cd +DATADG3
ASMCMD> ls
RACDB/
ASMCMD> cd RACDB
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
current.257.1110065497
current.258.1110065841
ASMCMD>
ASMCMD>
ASMCMD> pwd
+DATADG3/RACDB/CONTROLFILE

7.更新spfile控制文件参数
SQL> alter system set control_files=’+DATADG3/RACDB/CONTROLFILE/current.257.1110065497′,’+DATADG3/RACDB/CONTROLFILE/current.258.1110065841′ scope=spfile sid=’*’;

System altered.

8.重启数据库
关闭数据库：
[oracle@rac1 ~]$ srvctl stop instance -d racdb -i racdb1

[root@rac1 ~]# crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATADG1.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG2.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG3.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRADG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.SYSTEMDG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.racdb.db
1 OFFLINE OFFLINE Instance Shutdown
2 OFFLINE OFFLINE Instance Shutdown
ora.scan1.vip
1 ONLINE ONLINE rac1

启动数据库
[oracle@rac1 ~]$ srvctl start database -d racdb

[root@rac1 ~]# crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATADG1.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG2.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.DATADG3.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRADG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.SYSTEMDG.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.asm
ONLINE ONLINE rac1 Started
ONLINE ONLINE rac2 Started
ora.gsd
OFFLINE OFFLINE rac1
OFFLINE OFFLINE rac2
ora.net1.network
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.ons
ONLINE ONLINE rac1
ONLINE ONLINE rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 ONLINE ONLINE rac2
ora.racdb.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open
ora.scan1.vip
1 ONLINE ONLINE rac1

9.查看修改后控制文件
SQL> set pages 1000 lines 180
SQL> col name for a80
SQL>
SQL> select inst_id,name from gv$controlfile;

INST_ID NAME
———- ——————————————————————————–
2 +DATADG3/racdb/controlfile/current.257.1110065497
2 +DATADG3/racdb/controlfile/current.258.1110065841
1 +DATADG3/racdb/controlfile/current.257.1110065497
1 +DATADG3/racdb/controlfile/current.258.1110065841