当前位置： 首页 > news >正文

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

news 2025/8/4 9:30:24

问题描述

部署完一套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)管理-腾讯云开发者社区-腾讯云

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

http://www.lryc.cn/news/350851.html

相关文章：

  • UE4打包Win64项目命令行
  • c语言bug汇总中篇5
  • 【linux】进程（一）
  • 手把手教你用Python轻松玩转SQL注入
  • redis的几种部署模式及注意事项
  • 使用Python生成一束玫瑰花
  • 紫光同创PGL22G开发板|盘古22K开发板，国产FPGA开发板，接口丰富
  • 大模型的实践应用24-LLaMA-Factory微调通义千问qwen1.5-1.8B模型的实例
  • 力扣爆刷第142天之二叉树五连刷（构造树、搜索树）
  • 0407放大电路的频率响应
  • 数据分析必备：一步步教你如何用Pandas做数据分析（6）
  • Spring Cloud系列—Spring Cloud Gateway服务网关的部署与使用指南
  • 创建一个python的Django项目文件
  • NB49 牛群的秘密通信
  • Git系列：git mv 高效的文件重命名与移动操作
  • 美区TikTok小店又出潜力爆品！“痘痘贴”一周销售八万单！
  • C++两种内置栈的使用
  • 如何用电脑批量操作多部手机
  • Delphi 程序例子（DPI变化自动感知及显示器相关功能演示）
  • mysql主从复制的步骤和使用到的操作命令有哪些？
  • [AIGC] Java CompletableFuture：简介及示例
  • 五步定位性能瓶颈
  • currentTarget指向监听者Target：指向触发者
  • OpenAI宫斗剧番外篇: “Ilya与Altman联手对抗微软大帝，扫除黑恶势力”，“余华”和“莫言”犀利点评
  • 网关路由SpringCloudGateway、nacos配置管理(热更新、动态路由)
  • 关于linux的防护，以及群集你要知道的有哪些11-搭建Zabbix监控系统
  • 腾讯云环境安装单机版minio
  • 蓝桥杯2023（十四届）省赛——统计日期（八重神子）
  • 【Redis基础知识一】
  • 如何在go项目中实现发送邮箱验证码、邮箱+验证码登录