Oracle rac修改spfile

Oracle rac修改spfile

𝓓𝓸𝓷 Lv6
  2025-08-02 19:19:55 2025-08-02 19:19:55 Created   2025-08-06 16:17 2025-08-06 16:17 Updated    

一、查看当前spfile位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> show parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/yfdb/spfileyfdb.ora

[oracle@yfrac01 ~]$ cd $ORACLE_HOME/dbs
[oracle@yfrac01 dbs]$ ll
总用量 16
-rw-rw---- 1 oracle asmadmin 1544 8月   5 11:40 hc_yfdb1.dat
-rw-r--r-- 1 oracle oinstall 2851 5月  15 2009 init.ora
-rw-r----- 1 oracle oinstall   35 7月   7 2017 inityfdb1.ora
-rw-r----- 1 oracle oinstall 1536 6月  22 2022 orapwyfdb1

[oracle@yfrac01 dbs]$ more inityfdb1.ora 
SPFILE='+DATA/yfdb/spfileyfdb.ora'

二、备份spfile位置

1
[oracle@yfrac01 dbs]$ cp inityfdb1.ora inityfdb1.ora.bak

三、查看spfile内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

RAC环境慎用create pfile from spfile，因为这样创建的pfile会直接覆盖$ORACLE_HOME/dbs/inityfdb1.ora内容，导致重启DB后使用了本地pfile:


[oracle@yfrac01 dbs]$ more /tmp/pfile.ora 
yfdb1.__db_cache_size=65229815808
yfdb2.__db_cache_size=64961380352
yfdb2.__java_pool_size=1879048192
yfdb1.__java_pool_size=1879048192
yfdb2.__large_pool_size=2147483648
yfdb1.__large_pool_size=2147483648
yfdb1.__oracle_base='/home/app/oracle'#ORACLE_BASE set from environment
yfdb2.__oracle_base='/home/app/oracle'#ORACLE_BASE set from environment
yfdb2.__pga_aggregate_target=27111981056
yfdb1.__pga_aggregate_target=27111981056
yfdb2.__sga_target=81335943168
yfdb1.__sga_target=81335943168
yfdb2.__shared_io_pool_size=0
yfdb1.__shared_io_pool_size=0
yfdb1.__shared_pool_size=11542724608
yfdb2.__shared_pool_size=11811160064
yfdb2.__streams_pool_size=0
yfdb1.__streams_pool_size=0
*.audit_file_dest='/home/app/oracle/admin/yfdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='+DATA/yfdb/controlfile/current.260.948713911','+FRA/yfdb/controlfile/current.256.948713911'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=1000
*.db_name='yfdb'
*.db_recovery_file_dest_size=2147366207488
*.db_recovery_file_dest='+DATA'
*.diagnostic_dest='/home/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=yfdbXDB)'
yfdb1.instance_number=1
yfdb2.instance_number=2
yfdb1.log_archive_dest_1='location=+FRA/arch'
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=27076329472
*.processes=5000
*.remote_listener='yfrac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=11005
*.sga_target=81228988416
yfdb2.thread=2
yfdb1.thread=1
*.undo_retention=36000
yfdb2.undo_tablespace='UNDOTBS2'
yfdb1.undo_tablespace='UNDOTBS1'

四、修改归档日志路径

RAC修改参数有两种方法:

  • 手工编辑参数文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
(1) 备份spfile
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

(2) 修改pfile
[oracle@yfrac01 ~]$ grep log_archive_dest_1 /tmp/pfile.ora
*.log_archive_dest_1='LOCATION=+DATA'

[oracle@yfrac01 ~]$ sed -i 's/LOCATION=+DATA/LOCATION=+FRA/g' /tmp/pfile.ora
[oracle@yfrac01 ~]$ grep log_archive_dest_1 /tmp/pfile.ora
*.log_archive_dest_1='LOCATION=+FRA'
 
(3) 关闭另一节点

(4) 生成spfile
SQL> create spfile='+DATA' from pfile='/tmp/pfile.ora';

File created.

(5) 重启DB

(5) 检查验证
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA
Oldest online log sequence     39
Next log sequence to archive   40
Current log sequence           40

(5) 查看spfile
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/YFDB/PARAMETERFILE/spfile.279.1208438851
                                                 
此时spfile的位置会发生变化

(6) 创建别名
ASMCMD> cd data/yfdb
ASMCMD> mkalias +DATA/YFDB/PARAMETERFILE/spfile.279.1208438851 spfileorcl.ora
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    ARCHIVELOG/
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/
PARAMETERFILE  MIRROR  COARSE   AUG 06 13:00:00  N    spfileyfdb.ora => +DATA/YFDB/PARAMETERFILE/spfile.279.1208438851

直接这样创建别名，重启DB, show parameter spfile仍然看到的路径是+DATA/YFDB/PARAMETERFILE/spfile.279.1208438851而不是+DATA/yfdb/spfileyfdb.ora

(7) 修改asm配置
查看ocr记录的spfile位置:
$ srvctl config database -d yfdb -a
Database unique name: yfdb
Database name: yfdb
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/YFDB/PARAMETERFILE/spfile.279.1208438851

修改ocr记录的spfile位置(oracle用户执行而不是grid用户，任意一个节点执行):
$ srvctl modify database -d yfdb -p '+DATA/yfdb/spfileyfdb.ora'
[oracle@server01 ~]$ srvctl config database -d yfdb |grep -i spfile
Spfile: +DATA/orcl/spfileyfdb.ora

重启DB：

重新验证:
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileyfdb.ora

温馨提示: 
如果不想这么麻烦，则在第(4) 步，使用绝对路径创建:
SQL> create spfile='+DATA/yfdb/spfileyfdb.ora' from pfile='/tmp/pfile.ora';

File created.

spfileyfdb.ora只是一个别名最终指向实际参数为: +DATA/YFDB/PARAMETERFILE/spfile.278.1208438123
  • 使用alter system set命令修改参数
1
2
3
4
5
6
7
8
9
10
11
12
alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='yfdb1';
alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+DATA' scope=spfile;

注意:
这里的*是指所有实例
SQL> alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='yfdb1';

System altered.

alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='yfdb1';等于修改参数文件中的yfdb1.log_archive_dest_1='location=+FRA/arch'
alter system set log_archive_dest_1='location=+DATA' scope=spfile sid='*';等于修改参数文件中的*.log_archive_dest_1='LOCATION=+DATA'

五、修改控制文件路径

1.备份控制文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
col name for a80
set pages 1000 lines 180
select inst_id,name from gv$controlfile;

   INST_ID NAME
---------- --------------------------------------------------------------------------------
	 1 +DATA/yfdb/controlfile/current.260.948713911
	 1 +FRA/yfdb/controlfile/current.256.948713911
	 2 +DATA/yfdb/controlfile/current.260.948713911
	 2 +FRA/yfdb/controlfile/current.256.948713911
 
 SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 31
control_files			     string	 +DATA/yfdb/controlfile/current
						 .260.948713911, +FRA/yfdb/cont
						 rolfile/current.256.948713911
control_management_pack_access	     string	 DIAGNOSTIC+TUNING 

 SQL>  alter database backup controlfile to '/tmp/yfdb1.ctl';

Database altered.

SQL> alter database backup controlfile to trace as '/tmp/yfdb.ctl';
 
Database altered.
2.关闭数据库
1
[grid@yfrac01 ~]$ srvctl stop database -d yfdb
3.启动节点1实例至nomount
1
2
3
4
5
[grid@yfrac01 ~]$ srvctl start instance -d yfdb -i yfdb1 -o nomount

[grid@yfrac01 ~]$ srvctl status database -d yfdb
实例 yfdb1 正在节点 yfrac01 上运行
实例 yfdb2 没有在 yfrac02 节点上运行
4.移动控制文件位置

移动控制文件可以使用mv和rman恢复命令恢复控制至目标位置

  • 修改cp命令移动控制文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
(1) 将asm文件从磁盘组复制到磁盘组:
ASMCMD> cp +FRA/yfdb/controlfile/Current.256.948713911  +DATA/Current

(2) 将asm文件从磁盘组复制到文件系统:
ASMCMD> cp +FRA/yfdb/controlfile/Current.256.948713911  /tmp
copying +FRA/yfdb/controlfile/Current.256.948713911 -> /tmp/Current.256.948713911

(3) 将操作系统中的文件复制到磁盘组:
ASMCMD> rm -rf Current.256.948713911
ASMCMD> ls
Backup.6949.1196070339
snapcf_yfdb.f
ASMCMD> cp /tmp/Current.256.948713911 +FRA/yfdb/controlfile/Current.256.948713911
copying /tmp/Current.256.948713911 -> +FRA/yfdb/controlfile/Current.256.948713911
ASMCMD-8016: copy source '/tmp/Current.256.948713911' and target '+FRA/yfdb/controlfile/Current.256.948713911' failed
ORA-15056: additional error message
ORA-15046: ASM file name '+FRA/yfdb/controlfile/Current.256.948713911' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

在拷贝到ASM文件时不可以指定文件后面的数值，Oracle在文档Doc ID:Note:452158.1中进行详细的描述：

ASMCMD> cp /tmp/Current.256.948713911 +FRA/yfdb/controlfile/Current
 
ASM只是在目标位置下保留了一个alias，真正的文件放在了ASM目录:
ASMCMD> cp /tmp/Current.256.948713911 +FRA/yfdb/controlfile/Current
copying /tmp/Current.256.948713911 -> +FRA/yfdb/controlfile/Current
ASMCMD> ls -l
Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     AUG 05 03:00:00  Y    Backup.6949.1196070339
                                               N    Current => +FRA/ASM/CONTROLFILE/Current.256.1208354291
                                               N    snapcf_yfdb.f => +FRA/YFDB/CONTROLFILE/Backup.6949.1196070339
ASMCMD> pwd
+fra/yfdb/controlfile
 
 (3) 将操作系统中的文件复制到磁盘组:
ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';


使用cp命令可以执行以下三种复制操作:
(1) 从磁盘组中复制文件到操作系统中
(2) 从磁盘组中复制文件到磁盘组中
(3) 从操作系统中复制文件到磁盘组中

注意：
有些文件是不能执行复制的，比如OCR和SPFILE文件。为了备份，复制或移动Oracle ASM SPFILE文件，可以使用spbackup,spcopy或spmove命令。为了复制OCR备份文件，源地址必须是磁盘组
cp 拷贝ASM文件或者创建spfile 时不能直接指定OMF form的文件名称。即去掉数字部分，只保留名称部分即可
  • rman恢复命令恢复控制至目标位置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[root@yfrac01 ~]# su - oracle
[oracle@yfrac01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 5 13:13:29 2025

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

connected to target database: YFDB (not mounted)

RMAN> restore controlfile to '+DATA' from '+FRA/yfdb/controlfile/current.256.948713911';

Starting restore at 05-AUG-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6487 instance=yfdb1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 05-AUG-25

RMAN> restore controlfile to '+DATA' from '+FRA/yfdb/controlfile/current.256.948713911';

Starting restore at 05-AUG-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6349 instance=yfdb1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 05-AUG-25

这里恢复两次，生成两个控制文件副本

查看控制文件:
[grid@yfrac01 ~]$ asmcmd 
ASMCMD> cd data/yfdb/controlfile
ASMCMD> ls
Current.260.948713911
current.556.1208351711
current.557.1208352043

ASMCMD> pwd
+data/yfdb/controlfile

5.修改spfile中控制文件路径
1
2
3
SQL> alter system set control_files='+DATA/yfdb/controlfile/current.556.1208351711','+DATA/yfdb/controlfile/current.557.1208352043' scope=spfile sid='*';

System altered
6.重新启动数据库
1
2
3
[grid@yfrac01 ~]$ srvctl stop instance -d yfdb -i yfdb1

[grid@yfrac01 ~]$ srvctl start database -d yfdb
7.验证修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
[grid@yfrac01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
ora.FRA.dg
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
ora.OCR.dg
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
ora.asm
               ONLINE  ONLINE       yfrac01                  Started             
               ONLINE  ONLINE       yfrac02                  Started             
ora.gsd
               OFFLINE OFFLINE      yfrac01                                      
               OFFLINE OFFLINE      yfrac02                                      
ora.net1.network
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
ora.ons
               ONLINE  ONLINE       yfrac01                                      
               ONLINE  ONLINE       yfrac02                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       yfrac01                                      
ora.cvu
      1        ONLINE  ONLINE       yfrac01                                      
ora.oc4j
      1        ONLINE  ONLINE       yfrac01                                      
ora.scan1.vip
      1        ONLINE  ONLINE       yfrac01                                      
ora.yfdb.db
      1        ONLINE  ONLINE       yfrac01                  Open                
      2        ONLINE  ONLINE       yfrac02                  Open                
ora.yfrac01.vip
      1        ONLINE  ONLINE       yfrac01                                      
ora.yfrac02.vip
      1        ONLINE  ONLINE       yfrac02                                      
[grid@yfrac01 ~]$ 

SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 31
control_files			     string	 +DATA/yfdb/controlfile/current
						 .556.1208351711, +DATA/yfdb/co
						 ntrolfile/current.557.12083520
						 43
control_management_pack_access	     string	 DIAGNOSTIC+TUNING

8.删除不需要的控制文件
1
2
3
4
5
6
7
8
9
10
ASMCMD> cd data/yfdb/controlfile
ASMCMD> ls
Current.260.948713911
current.556.1208351711
current.557.1208352043
ASMCMD> rm -rf Current.260.948713911

ASMCMD> ls
current.556.1208351711
current.557.1208352043

六、Oracle RAC修改spfile位置

1.备份spfile
1
2
3
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.
2.创建spfile
1
2
3
4
5
SQL> create spfile='+DATA/yfdb/spfileyfdb.ora' from pfile='/tmp/pfile.ora';

File created.

DATA/yfdb/spfileyfdb.ora使用绝对路径
3.重启DB
1
2
SQL> shutdown imediate
SQL> startup
4.验证
1
2
3
4
5
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/yfdb/spfileyfdb.ora
  • Title: Oracle rac修改spfile
  • Author: 𝓓𝓸𝓷
  • Created at : 2025-08-02 19:19:55
  • Updated at : 2025-08-06 16:17:00
  • Link: https://www.zhangdong.me/oracle-rac-modify-spfile.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
Oracle RAC添加删除日志文件 Oracle RAC添加删除日志文件 Oracle11g RAC添加删除asmdisk磁盘组 Oracle11g RAC添加删除asmdisk磁盘组 Oracle11gR2 RAC迁移到单实例 Oracle11gR2 RAC迁移到单实例
推荐文章
Oracle RAC添加删除日志文件 Oracle RAC添加删除日志文件 Oracle11g RAC添加删除asmdisk磁盘组 Oracle11g RAC添加删除asmdisk磁盘组
评论
  1. 一、查看当前spfile位置
  2. 二、备份spfile位置
  3. 三、查看spfile内容
  4. 四、修改归档日志路径
  5. 五、修改控制文件路径
    1. 1.备份控制文件
    2. 2.关闭数据库
    3. 3.启动节点1实例至nomount
    4. 4.移动控制文件位置
    5. 5.修改spfile中控制文件路径
    6. 6.重新启动数据库
    7. 7.验证修改
    8. 8.删除不需要的控制文件
  6. 六、Oracle RAC修改spfile位置
    1. 1.备份spfile
    2. 2.创建spfile
    3. 3.重启DB
    4. 4.验证