ORA-01565: Unable to open Spfile 附加 windows上定时清理归档日志脚本

背景

客户的oracle数据库突然挂了，启动不起来，查看报错日志如下：

ORA-01565: Unable to open Spfile D:\APP\ADMINISTRATOR\PRODUCT\12.2.0\DBHOME_1\DATABASE\SPFILEONEPAY.ORA.
ORA-01565: Unable to open Spfile D:\APP\ADMINISTRATOR\PRODUCT\12.2.0\DBHOME_1\DATABASE\SPFILEONEPAY.ORA.
2025-02-03T13:04:43.322775+08:00

分析

spfile文件找不到，有测试环境就从测试环境拷贝过来，没有的话，就自己生成一个。
可以尝试从内存中恢复。
CREATE SPFILE='/path/to/spfile.ora' FROM MEMORY;
也可以尝试从rmane中恢复：
RMAN> list backup of spfile;
RMAN> restore spfile to '/path/to/spfile.ora' from 'backup_piece_name';
总之就是想办法把这个文件恢复，实在不行，自己创建一个，写入基础的参数。

处理

直接从测试环境拷贝过来，启动成功，有一些报错先无视
trace日志：

alert日志：

优化

1.发现内存非自动管理，服务器有32G，初始配置太低，优化一下

SHOW PARAMETER sga_target;
SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER memory_target;
SHOW PARAMETER memory_max_target;

修改配置并重启，如果有部分参数无法修改，就
ALTER SYSTEM SET SGA_TARGET = 0 SCOPE = BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = BOTH;
ALTER SYSTEM SET MEMORY_TARGET = 20G SCOPE = BOTH;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 25G SCOPE = SPFILE;

windows定时删除archivelog日志

delete_archivelog.bat

@echo off
setlocal



:: 定义Oracle环境变量
set ORACLE_HOME=D:\app\Administrator\product\12.2.0\dbhome_1
set ORACLE_SID=onepay
set PATH=%ORACLE_HOME%\bin;%PATH%

:: 打印开始信息
echo Starting RMAN archivelog cleanup...

:: 创建临时RMAN脚本文件
set RMAN_SCRIPT=%TEMP%\rman_cleanup.rman
echo DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; > "%RMAN_SCRIPT%"
echo CROSSCHECK ARCHIVELOG ALL; >> "%RMAN_SCRIPT%"
echo DELETE EXPIRED ARCHIVELOG ALL; >> "%RMAN_SCRIPT%"
echo EXIT; >> "%RMAN_SCRIPT%"

:: 使用RMAN命令行执行脚本
rman target / LOG=D:\app\archivelog_cleanup.log CMDFILE="%RMAN_SCRIPT%"

:: 删除临时RMAN脚本文件
del "%RMAN_SCRIPT%"

:: 检查上一条命令的退出状态
IF %ERRORLEVEL% EQU 0 (
    echo RMAN archivelog cleanup completed successfully.
) ELSE (
    echo There was an error during the RMAN archivelog cleanup. Check the log file for details.
)

endlocal