有没有做黑市网站,做网站还要写文章吗,阿里云域名 设置网站,一键生成app软件下载问题描述
部署完一套3节点的11g RAC后#xff0c;进行了内存的参数优化#xff0c;优化时忘记了先备份参数文件#xff0c;忘记了计算内存参数眼盲的复制粘贴执行内存优化sql导致优化后重启实例启动失败。艾#xff0c;由于粗心自己给自己挖了个坑。
切记更改参数步骤进行了内存的参数优化优化时忘记了先备份参数文件忘记了计算内存参数眼盲的复制粘贴执行内存优化sql导致优化后重启实例启动失败。艾由于粗心自己给自己挖了个坑。
切记更改参数步骤
备份参数文件执行更改参数sql根据更改参数属性是否重启数据库
注意不要被Linux-x86_64 Error: 28: No space left on device干扰磁盘剩余空间还很多主要关注PRCR-1079、CRS-5017、ORA-27102的提示。
--启动数据库 1个节点操作所有节点生效
[oracleracdb01:/]$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_targetos_memory_total * 7 * 8 / 100 / 1024
--pga_targetos_memory_total * 7 * 2 / 100 / 1024
alter system set sga_max_size20G sid* scopespfile;
alter system set sga_target20G sid* scopespfile;
alter system set pga_aggregate_target10G sid* scopespfile;
alter system set processes3000 scopespfile;
alter system set sessions4544 scopespfile;
alter system set open_cursors1000 scopespfile;
alter system set session_cached_cursors300 scopespfile;
alter system set db_files5000 scopespfile;
alter system set _undo_autotunefalse sid* scopespfile;
alter system set undo_retention10800 scopespfile;
解决办法
查看数据库告警日志中的启动参数
注意观察
sga_target 800M
pga_aggregate_target 200M
[oracleracdb01:/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.0log_archive_dest_1 LOCATIONDATAlog_archive_format %t_%s_%r.dbfdb_create_file_dest DATAdb_recovery_file_dest DATAdb_recovery_file_dest_size 4782Mundo_tablespace UNDOTBS1instance_number 1remote_login_passwordfile EXCLUSIVEdb_domain dispatchers (PROTOCOLTCP) (SERVICEracdbXDB)remote_listener racdbscan01:1521audit_file_dest /oracle/app/oracle/admin/racdb/adumpaudit_trail DBdb_name racdbopen_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位置方法1oracle用户$ORACLE_HOME/dbs下
oracleracdb01:/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[oracleracdb01:/oracle/app/oracle/product/11.2.0/db_1/dbs]$more initracdb1.ora
SPFILEDATA/racdb/spfileracdb.ora查看集群spfile位置方法2
--查看spfile位置方法2grid用户asmcmd命令行中查
[rootracdb01 ~]# su - grid
Last login: Thu May 23 13:50:27 CST 2024 on pts/2
[gridracdb01:/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服务器本地
[rootracdb01 logs]# su - grid
Last login: Wed May 22 16:00:59 CST 2024 on pts/0
[gridracdb01:/home/grid]$asmcmd
ASMCMD cp DATA/racdb/spfileracdb.ora /home/grid/spfileracdb.ora
copying DATA/racdb/spfileracdb.ora - /home/grid/spfileracdb.ora
查看spfile文件内容
[gridracdb01:/home/grid]$strings spfileracdb.ora
racdb1.__db_cache_size549453824
racdb3.__db_cache_size549453824
racdb2.__db_cache_size549453824
racdb1.__java_pool_size4194304
racdb3.__java_pool_size4194304
racdb2.__java_pool_size4194304
racdb1.__large_pool_size8388608
racdb3.__large_pool_size8388608
racdb2.__large_pool_size8388608
racdb1.__pga_aggregate_target209715200
racdb3.__pga_aggregate_target209715200
racdb2.__pga_aggregate_target209715200
racdb1.__sga_target838860800
racdb3.__sga_target838860800
racdb2.__sga_t
arget838860800
racdb1.__shared_io_pool_size0
racdb3.__shared_io_pool_size0
racdb2.__shared_io_pool_size0
racdb1.__shared_pool_size268435456
racdb3.__shared_pool_size268435456
racdb2.__shared_pool_size268435456
racdb1.__streams_pool_size0
racdb3.__streams_pool_size0
racdb2.__streams_pool_size0
*._b_tree_bitmap_plansFALSE
*._cleanup_rollback_entries2000
*._clusterwide_global_transactionsFALSE
*._datafile_write_errors_crash_instanceFALSE
*._gc_policy_time0
*._gc_undo_affi
nityFALSE
*._index_partition_large_extentsFALSE
*._memory_imm_mode_without_autosgaFALSE
*._optimizer_adaptive_cursor_sharingFALSE
*._optimizer_extended_cursor_sharingNONE
*._optimizer_extended_cursor_sharing_relNONE
*._optimizer_null_aware_antijoinFALSE
*._optimizer_use_feedbackFALSE
*._partition_large_extentsFALSE
*._PX_use_large_poolTRUE
*._undo_autotuneFALSE
*._use_adaptive_log_file_syncFALSE
*.audit_file_dest/oracle/app/oracle/admin/racdb/adump
*.audit_tr
ailNONE
*.cluster_databasetrue
*.compatible11.2.0.4.0
*.control_file_record_keep_time31
*.control_filesDATA/racdb/controlfile/current.274.1169660279,DATA/racdb/controlfile/current.273.1169660279
*.db_block_size8192
*.db_create_file_destDATA
*.db_domain
*.db_files5000
*.db_nameracdb
*.db_recovery_file_destDATA
*.db_recovery_file_dest_size5014290432
*.deferred_segment_creationFALSE
*.diagnostic_dest/oracle/app/oracle
*.dispatchers(PROTOCOLTCP) (SE
RVICEracdbXDB)
*.enable_ddl_loggingTRUE
*.event28401 trace name context forever,level 1,10949 trace name context forever,level 1
racdb1.instance_number1
racdb3.instance_number3
racdb2.instance_number2
*.log_archive_dest_1LOCATIONDATA
*.log_archive_format%t_%s_%r.dbf
*.open_cursors1000
*.parallel_force_localTRUE
*.parallel_max_servers64
*.pga_aggregate_target10737418240
*.processes3000
*.remote_listenerracdbscan01:1521
*.remote_login_passwordfileexclusive
*.resource_limitTRUE
*.resource_manager_planforce:
*.sec_case_sensitive_logonFALSE
*.session_cached_cursors300
*.sessions4544
*.sga_max_size21474836480
*.sga_target21474836480
racdb3.thread3
racdb2.thread2
racdb1.thread1
*.undo_retention10800
racdb1.undo_tablespaceUNDOTBS1
racdb2.undo_tablespaceUNDOTBS2
racdb3.undo_tablespaceUNDOTBS3
生成节点1的pfile文件
根据集群spfile生成节点1的pfile文件
[gridracdb01:/home/grid]$strings spfileracdb.ora initracdb.ora_bak_20240522
计算内存大小
--os_memory_total$(awk /MemTotal/{print $2} /proc/meminfo) 4026136 单位K
--$sga_targetos_memory_total * 7 * 8 / 100 * 1024 2308747428 单位字节
--pga_targetos_memory_total * 7 * 2 / 100 * 1024 577186857 单位字节
补充
首先先不用计算内存大小的参数进行如下步骤的操作。先用数据库告警日志中的启动参数sga_target 、
pga_aggregate_target 进行如下操作如果成功可忽略计算内存大小步骤。如果用数据库告警日志中的启动参数sga_target 、
pga_aggregate_target 进行如下操作启库提示如下报错
[oracleracdb01:/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
[gridracdb01:/home/grid]$vi initracdb.ora_bak_20240522
*._b_tree_bitmap_plansFALSE
*._cleanup_rollback_entries2000
*._clusterwide_global_transactionsFALSE
*._datafile_write_errors_crash_instanceFALSE
*._gc_policy_time0
*._gc_undo_affinityFALSE
*._index_partition_large_extentsFALSE
*._memory_imm_mode_without_autosgaFALSE
*._optimizer_adaptive_cursor_sharingFALSE
*._optimizer_extended_cursor_sharingNONE
*._optimizer_extended_cursor_sharing_relNONE
*._optimizer_null_aware_antijoinFALSE
*._optimizer_use_feedbackFALSE
*._partition_large_extentsFALSE
*._PX_use_large_poolTRUE
*._undo_autotuneFALSE
*._use_adaptive_log_file_syncFALSE
*.audit_file_dest/oracle/app/oracle/admin/racdb/adump
*.audit_trailNONE
*.cluster_databasetrue
*.compatible11.2.0.4.0
*.control_file_record_keep_time31
*.control_filesDATA/racdb/controlfile/current.274.1169660279,DATA/racdb/controlfile/current.273.1169660279
*.db_block_size8192
*.db_create_file_destDATA
*.db_domain
*.db_files5000
*.db_nameracdb
*.db_recovery_file_destDATA
*.db_recovery_file_dest_size5014290432
*.deferred_segment_creationFALSE
*.diagnostic_dest/oracle/app/oracle
*.dispatchers(PROTOCOLTCP) (SERVICEracdbXDB)
*.enable_ddl_loggingTRUE
*.event28401 trace name context forever,level 1,10949 trace name context forever,level 1
racdb1.instance_number1
racdb3.instance_number3
racdb2.instance_number2
*.log_archive_dest_1LOCATIONDATA
*.log_archive_format%t_%s_%r.dbf
*.open_cursors1000
*.parallel_force_localTRUE
*.parallel_max_servers64
*.pga_aggregate_target209715200
*.processes3000
*.remote_listenerracdbscan01:1521
*.remote_login_passwordfileexclusive
*.resource_limitTRUE
*.resource_manager_planforce:
*.sec_case_sensitive_logonFALSE
*.session_cached_cursors300
*.sessions4544
*.sga_max_size2013265920
*.sga_target2013265920
racdb3.thread3
racdb2.thread2
racdb1.thread1
*.undo_retention10800
racdb1.undo_tablespaceUNDOTBS1
racdb2.undo_tablespaceUNDOTBS2
racdb3.undo_tablespaceUNDOTBS3
注意以下参数不用在pfile中包含根据pfile生成spfile文件时会自动生成spfile文中会自动生成。
racdb1.__db_cache_size549453824
racdb3.__db_cache_size549453824
racdb2.__db_cache_size549453824
racdb1.__java_pool_size4194304
racdb3.__java_pool_size4194304
racdb2.__java_pool_size4194304
racdb1.__large_pool_size8388608
racdb3.__large_pool_size8388608
racdb2.__large_pool_size8388608
racdb1.__pga_aggregate_target209715200
racdb3.__pga_aggregate_target209715200
racdb2.__pga_aggregate_target209715200
racdb1.__sga_target838860800
racdb3.__sga_target838860800
racdb2.__sga_t
arget838860800
racdb1.__shared_io_pool_size0
racdb3.__shared_io_pool_size0
racdb2.__shared_io_pool_size0
racdb1.__shared_pool_size268435456
racdb3.__shared_pool_size268435456
racdb2.__shared_pool_size268435456
racdb1.__streams_pool_size0
racdb3.__streams_pool_size0
racdb2.__streams_pool_size0
创建集群的spfile文件
节点1使用pfile方式启库
[oracleracdb01:/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 spfileDATA/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
启动集群实例
--启动集群实例
[oracleracdb01:/home/oracle]$srvctl start database -d racdb--查看集群实例状态
[oracleracdb01:/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
查看集群状态
[gridracdb01:/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_size800M scopespfile;
alter system set sga_target800M scopespfile;
alter system set pga_aggregate_target200M scopespfile;--关闭数据库
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_size800M scopespfile;
alter system set sga_target800M scopespfile;
alter system set pga_aggregate_target200M scopespfile;--关闭数据库
[oracleracdb01:/home/oracle]$srvctl stop database -d racdb
[oracleracdb01:/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--启动集群实例
[oracleracdb01:/home/oracle]$srvctl start database -d racdb--查看集群实例状态
[oracleracdb01:/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)管理-腾讯云开发者社区-腾讯云
若有疑问可以关注公众号留言加微信进行答疑解惑。
