RAC11G参数修改错误导致启库失败处理

问题描述

部署完一套3节点的11g RAC后，进行了内存的参数优化，优化时忘记了先备份参数文件，忘记了计算内存参数眼盲的复制粘贴执行内存优化sql导致优化后重启实例启动失败。艾，由于粗心自己给自己挖了个坑。

切记更改参数步骤：

• 备份参数文件
• 执行更改参数sql
• 根据更改参数属性是否重启数据库

注意不要被Linux-x86_64 Error: 28: No space left on device干扰，磁盘剩余空间还很多，主要关注PRCR-1079、CRS-5017、ORA-27102的提示。

--启动数据库  1个节点操作所有节点生效
[oracle@racdb01:/]$srvctl start database -d racdb
PRCR-1079 : Failed to start resource ora.racdb.db
CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: -67108864
Additional information: 1
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb01/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 2013265920
Additional information: 1
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb03/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 2013265920
Additional information: 1
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb02/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-2674: Start of 'ora.racdb.db' on 'racdb01' failed
CRS-2674: Start of 'ora.racdb.db' on 'racdb03' failed
CRS-2674: Start of 'ora.racdb.db' on 'racdb02' failed
CRS-2632: There are no more servers to try to place resource 'ora.racdb.db' on that would satisfy its placement policy

问题原因

部署完3节点的RAC后进行了如下参数优化，3个节点的内存为4G而忘记进行公式计算直接设置了20G，然后就启库失败了。

--os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo)

--$sga_target=os_memory_total * 7 * 8 / 100 / 1024

--pga_target=os_memory_total * 7 * 2 / 100 / 1024

alter system set sga_max_size=20G sid='*' scope=spfile;
alter system set sga_target=20G sid='*' scope=spfile;
alter system set pga_aggregate_target=10G sid='*' scope=spfile;
alter system set processes=3000 scope=spfile;
alter system set sessions=4544 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set session_cached_cursors=300 scope=spfile;
alter system set db_files=5000 scope=spfile;
alter system set "_undo_autotune"=false sid='*' scope=spfile;
alter system set undo_retention=10800 scope=spfile;

解决办法

查看数据库告警日志中的启动参数

注意观察：

sga_target = 800M

pga_aggregate_target = 200M

[oracle@racdb01:/oracle/app/oracle/diag/rdbms/racdb/racdb1/trace]$vi alert_racdb1.log
检索条件：Starting up
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      racdb01
Release:        3.10.0-1160.el7.x86_64
Version:        #1 SMP Mon Oct 19 16:18:59 UTC 2020
Machine:        x86_64
VM name:        VMWare Version: 6
Using parameter settings in client-side pfile /oracle/app/oracle/admin/racdb/pfile/init.ora on machine racdb01
System parameters with non-default values:processes                = 1000sessions                 = 1522sga_target               = 800Mdb_block_size            = 8192compatible               = "11.2.0.4.0"log_archive_dest_1       = "LOCATION=+DATA"log_archive_format       = "%t_%s_%r.dbf"db_create_file_dest      = "+DATA"db_recovery_file_dest    = "+DATA"db_recovery_file_dest_size= 4782Mundo_tablespace          = "UNDOTBS1"instance_number          = 1remote_login_passwordfile= "EXCLUSIVE"db_domain                = ""dispatchers              = "(PROTOCOL=TCP) (SERVICE=racdbXDB)"remote_listener          = "racdbscan01:1521"audit_file_dest          = "/oracle/app/oracle/admin/racdb/adump"audit_trail              = "DB"db_name                  = "racdb"open_cursors             = 300pga_aggregate_target     = 200Mdiagnostic_dest          = "/oracle/app/oracle"

查看集群spfile位置

注意单点和集群的参数文件位置的差异。

单点实例的参数文件位置在$ORACLE_HOME/dbs目录下，参数文件有init.ora、initsid.ora spfilesid.ora

集群的参数文件位置在$ORACLE_HOME/dbs目录下，参数文件有init.ora、initsid.ora ，其中initsid.ora中记录的是spfilesid.ora的全路径。

查看集群spfile位置方法1

--查看spfile位置方法1：oracle用户$ORACLE_HOME/dbs下
oracle@racdb01:/oracle/app/oracle/product/11.2.0/db_1/dbs]$ls -l
total 16
-rw-rw---- 1 oracle asmadmin 1544 May 22 18:52 hc_racdb1.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   37 May 22 17:41 initracdb1.ora
-rw-r----- 1 oracle oinstall 1536 May 22 17:39 orapwracdb1[oracle@racdb01:/oracle/app/oracle/product/11.2.0/db_1/dbs]$more initracdb1.ora
SPFILE='+DATA/racdb/spfileracdb.ora'

查看集群spfile位置方法2

--查看spfile位置方法2：grid用户asmcmd命令行中查
[root@racdb01 ~]# su - grid
Last login: Thu May 23 13:50:27 CST 2024 on pts/2
[grid@racdb01:/home/grid]$asmcmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  NORMAL  N      OCR/
ASMCMD> cd DATA
ASMCMD> ls
RACDB/
ASMCMD> cd RACDB
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileracdb.ora
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls
spfile.291.1169733707
ASMCMD> pwd
+DATA/RACDB/PARAMETERFILE

复制spfile文件到节点1服务器本地

[root@racdb01 logs]# su - grid
Last login: Wed May 22 16:00:59 CST 2024 on pts/0
[grid@racdb01:/home/grid]$asmcmd
ASMCMD> cp +DATA/racdb/spfileracdb.ora /home/grid/spfileracdb.ora
copying +DATA/racdb/spfileracdb.ora -> /home/grid/spfileracdb.ora

查看spfile文件内容

[grid@racdb01:/home/grid]$strings spfileracdb.ora
racdb1.__db_cache_size=549453824
racdb3.__db_cache_size=549453824
racdb2.__db_cache_size=549453824
racdb1.__java_pool_size=4194304
racdb3.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=8388608
racdb3.__large_pool_size=8388608
racdb2.__large_pool_size=8388608
racdb1.__pga_aggregate_target=209715200
racdb3.__pga_aggregate_target=209715200
racdb2.__pga_aggregate_target=209715200
racdb1.__sga_target=838860800
racdb3.__sga_target=838860800
racdb2.__sga_t
arget=838860800
racdb1.__shared_io_pool_size=0
racdb3.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_pool_size=268435456
racdb3.__shared_pool_size=268435456
racdb2.__shared_pool_size=268435456
racdb1.__streams_pool_size=0
racdb3.__streams_pool_size=0
racdb2.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._clusterwide_global_transactions=FALSE
*._datafile_write_errors_crash_instance=FALSE
*._gc_policy_time=0
*._gc_undo_affi
nity=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_tr
ail='NONE'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='+DATA/racdb/controlfile/current.274.1169660279','+DATA/racdb/controlfile/current.273.1169660279'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=5000
*.db_name='racdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=5014290432
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SE
RVICE=racdbXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
racdb1.instance_number=1
racdb3.instance_number=3
racdb2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=64
*.pga_aggregate_target=10737418240
*.processes=3000
*.remote_listener='racdbscan01:1521'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sessions=4544
*.sga_max_size=21474836480
*.sga_target=21474836480
racdb3.thread=3
racdb2.thread=2
racdb1.thread=1
*.undo_retention=10800
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
racdb3.undo_tablespace='UNDOTBS3'

生成节点1的pfile文件

根据集群spfile生成节点1的pfile文件

[grid@racdb01:/home/grid]$strings spfileracdb.ora  > initracdb.ora_bak_20240522

计算内存大小

--os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo) 4026136 单位K

--$sga_target=os_memory_total * 7 * 8 / 100 * 1024 2308747428 单位字节

--pga_target=os_memory_total * 7 * 2 / 100 * 1024 577186857 单位字节

补充

首先先不用计算内存大小的参数进行如下步骤的操作。先用数据库告警日志中的启动参数sga_target 、

pga_aggregate_target 进行如下操作，如果成功可忽略计算内存大小步骤。如果用数据库告警日志中的启动参数sga_target 、

pga_aggregate_target 进行如下操作启库提示如下报错：

[oracle@racdb01:/home/oracle]$srvctl start database -d racdb
PRCR-1079 : Failed to start resource ora.racdb.db
CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00821: Specified value of sga_target 800M is too small, needs to be at least 1860M
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb01/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00821: Specified value of sga_target 800M is too small, needs to be at least 1860M
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb02/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-5017: The resource action "ora.racdb.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-00821: Specified value of sga_target 800M is too small, needs to be at least 1860M
. For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/racdb03/agent/crsd/oraagent_oracle/oraagent_oracle.log".CRS-2674: Start of 'ora.racdb.db' on 'racdb01' failed
CRS-2674: Start of 'ora.racdb.db' on 'racdb02' failed
CRS-2674: Start of 'ora.racdb.db' on 'racdb03' failed
CRS-2632: There are no more servers to try to place resource 'ora.racdb.db' on that would satisfy its placement policy

然后再进行计算内存大小的值进行如下操作，尽可能使用先前正常启库的参数。该步骤灵活变通。

更改后的节点1的pfile文件后的内容

:%s#21474836480#2308747428#g

:%s#10737418240#577186857#g

[grid@racdb01:/home/grid]$vi initracdb.ora_bak_20240522
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._clusterwide_global_transactions=FALSE
*._datafile_write_errors_crash_instance=FALSE
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='+DATA/racdb/controlfile/current.274.1169660279','+DATA/racdb/controlfile/current.273.1169660279'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_files=5000
*.db_name='racdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=5014290432
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
racdb1.instance_number=1
racdb3.instance_number=3
racdb2.instance_number=2
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_force_local=TRUE
*.parallel_max_servers=64
*.pga_aggregate_target=209715200
*.processes=3000
*.remote_listener='racdbscan01:1521'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sessions=4544
*.sga_max_size=2013265920
*.sga_target=2013265920
racdb3.thread=3
racdb2.thread=2
racdb1.thread=1
*.undo_retention=10800
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
racdb3.undo_tablespace='UNDOTBS3'

注意：以下参数不用在pfile中包含，根据pfile生成spfile文件时会自动生成，spfile文中会自动生成。

racdb1.__db_cache_size=549453824
racdb3.__db_cache_size=549453824
racdb2.__db_cache_size=549453824
racdb1.__java_pool_size=4194304
racdb3.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=8388608
racdb3.__large_pool_size=8388608
racdb2.__large_pool_size=8388608
racdb1.__pga_aggregate_target=209715200
racdb3.__pga_aggregate_target=209715200
racdb2.__pga_aggregate_target=209715200
racdb1.__sga_target=838860800
racdb3.__sga_target=838860800
racdb2.__sga_t
arget=838860800
racdb1.__shared_io_pool_size=0
racdb3.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_pool_size=268435456
racdb3.__shared_pool_size=268435456
racdb2.__shared_pool_size=268435456
racdb1.__streams_pool_size=0
racdb3.__streams_pool_size=0
racdb2.__streams_pool_size=0

创建集群的spfile文件

节点1使用pfile方式启库

[oracle@racdb01:/home/oracle]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu May 23 14:00:56 2024Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup pfile='/home/grid/initracdb.ora_bak_20240522';
ORACLE instance started.Total System Global Area 2004267008 bytes
Fixed Size		    2254624 bytes
Variable Size		 1845496032 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    5521408 bytes
Database mounted.
Database opened.

创建集群的spfile文件

SQL> create spfile='+DATA/racdb/spfileracdb.ora' from pfile='/home/grid/initracdb.ora_bak_20240522';File created.

关闭节点1实例

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

启动集群实例

--启动集群实例
[oracle@racdb01:/home/oracle]$srvctl start database -d racdb--查看集群实例状态
[oracle@racdb01:/home/oracle]$srvctl status database -d racdb
Instance racdb1 is running on node racdb01
Instance racdb2 is running on node racdb02
Instance racdb3 is running on node racdb03

查看集群状态

[grid@racdb01:/home/grid]$crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dgONLINE  ONLINE       racdb01ONLINE  ONLINE       racdb02ONLINE  ONLINE       racdb03
ora.LISTENER.lsnrONLINE  ONLINE       racdb01ONLINE  ONLINE       racdb02ONLINE  ONLINE       racdb03
ora.OCR.dgONLINE  ONLINE       racdb01ONLINE  ONLINE       racdb02ONLINE  ONLINE       racdb03
ora.asmONLINE  ONLINE       racdb01                  StartedONLINE  ONLINE       racdb02                  StartedONLINE  ONLINE       racdb03                  Started
ora.gsdOFFLINE OFFLINE      racdb01OFFLINE OFFLINE      racdb02OFFLINE OFFLINE      racdb03
ora.net1.networkONLINE  ONLINE       racdb01ONLINE  ONLINE       racdb02ONLINE  ONLINE       racdb03
ora.onsONLINE  ONLINE       racdb01ONLINE  ONLINE       racdb02ONLINE  ONLINE       racdb03
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr1        ONLINE  ONLINE       racdb01
ora.cvu1        ONLINE  ONLINE       racdb01
ora.oc4j1        ONLINE  ONLINE       racdb01
ora.racdb.db1        ONLINE  ONLINE       racdb01                  Open2        ONLINE  ONLINE       racdb02                  Open3        ONLINE  ONLINE       racdb03                  Open
ora.racdb01.vip1        ONLINE  ONLINE       racdb01
ora.racdb02.vip1        ONLINE  ONLINE       racdb02
ora.racdb03.vip1        ONLINE  ONLINE       racdb03
ora.scan1.vip1        ONLINE  ONLINE       racdb01

补充

单实例和集群修改实例级别的参数区别

单实例

--备份spfile文件
SQL> create pfile='/home/oracle/pfiledhh.ora_bak_20240523' from spfile;--更改参数
alter system set sga_max_size=800M  scope=spfile;
alter system set sga_target=800M  scope=spfile;
alter system set pga_aggregate_target=200M  scope=spfile;--关闭数据库
shutdown immediate--启库
startup --查看生效后的参数
show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;

集群

--备份spfile文件
SQL> create pfile='/home/grid/pfileracdb.ora_bak_20240523' from spfile;--更改参数
alter system set sga_max_size=800M  scope=spfile;
alter system set sga_target=800M  scope=spfile;
alter system set pga_aggregate_target=200M  scope=spfile;--关闭数据库
[oracle@racdb01:/home/oracle]$srvctl stop database -d racdb
[oracle@racdb01:/home/oracle]$srvctl status database -d racdb
Instance racdb1 is not running on node racdb01
Instance racdb2 is not running on node racdb02
Instance racdb3 is not running on node racdb03--启动集群实例
[oracle@racdb01:/home/oracle]$srvctl start database -d racdb--查看集群实例状态
[oracle@racdb01:/home/oracle]$srvctl status database -d racdb
Instance racdb1 is running on node racdb01
Instance racdb2 is running on node racdb02
Instance racdb3 is running on node racdb03--查看生效后的参数
show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;

Oracle数据库的配置文件丢失或损失，重新执行pfile启动_oracle pfile启动-CSDN博客

RAC 环境下参数文件(spfile)管理-腾讯云开发者社区-腾讯云

若有疑问可以关注公众号留言加微信进行答疑解惑。