您现在的位置是：首页 >技术杂谈 >Oracle ASM中的spfile恢复网站首页技术杂谈

做Database In-Memory的实验，经常会出现内存参数设置不正确，导致数据库无法启动的情况，例如inmemory_size设的太大，或者设置时没有带单位。此时就需要恢复spfile。本文讨论spfile位于ASM时的恢复过程。

数据库启动时错误为：

[oracle@bigt01 dbim]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 02:18:19 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-00600: internal error code, arguments: [ktsima_get_poolsize-1], [13421770], [16777216], [], [], [], [], [], [], [], [], []
SQL>

从srvctl中得到当前spfile的位置：

# 获取数据库名
$ srvctl config
DB0410_nrt1pv

# 获取数据库配置，其中有spfile的位置
$ srvctl config database -d DB0410_nrt1pv
Database unique name: DB0410_nrt1pv
Database name: DB0410
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DB0410_NRT1PV/PARAMETERFILE/spfile.262.1133756061
Password file:
Domain: sub07290808380.training.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths: /opt/oracle/dcs/commonstore
Services: DB0410_orclpdb1.paas.oracle.com
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: DB0410
Configured nodes: bigt02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

当然以grid用户运行asmcmd也可以获取其位置：

$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304   1048576   706888                0          706888              0             Y  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304    524288   520956                0          520956              0             N  RECO/

ASMCMD> cd DATA/
ASMCMD> cd DB0410_NRT1PV/
ASMCMD> cd PARAMETERFILE/
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   APR 26 02:00:00  Y    spfile.262.1133756061

知道其位置后，就可以依据他创建pfile了：

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 11:45:43 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile from spfile='&1';
Enter value for 1: +DATA/DB0410_NRT1PV/PARAMETERFILE/spfile.262.1133756061
old   1: create pfile from spfile='&1'
new   1: create pfile from spfile='+DATA/DB0410_NRT1PV/PARAMETERFILE/spfile.262.1133756061'

File created.

pfile的默认位置在$ORACLE_HOME/dbs中，显然本例即initDB0410.ora：

$ date
Wed Apr 26 11:47:44 UTC 2023
[oracle@bigt02 bigtable]$ ll $ORACLE_HOME/dbs
total 16
-rw-rw---- 1 oracle asmadmin 1544 Apr 25 23:59 hc_DB0410.dat
-rw-r--r-- 1 oracle asmadmin 2408 Apr 26 11:46 initDB0410.ora
-rw-r--r-- 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r----- 1 oracle oinstall 2048 Apr 10 04:07 orapwDB0410

我们来看下这个文件：

$ cat $ORACLE_HOME/dbs/initDB0410.ora
DB0410.__data_transfer_cache_size=0
DB0410.__db_cache_size=13153337344
DB0410.__inmemory_ext_roarea=0
DB0410.__inmemory_ext_rwarea=0
DB0410.__java_pool_size=0
DB0410.__large_pool_size=167772160
DB0410.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB0410.__pga_aggregate_target=12482248704
DB0410.__sga_target=49928994816
DB0410.__shared_io_pool_size=134217728
DB0410.__shared_pool_size=1946157056
DB0410.__streams_pool_size=0
DB0410.__unified_pga_pool_size=0
*._datafile_write_errors_crash_instance=false
*._db_writer_coalesce_area_size=16777216
*._disable_interface_checking=TRUE
*._enable_numa_support=FALSE
*._file_size_increase_increment=2143289344
*._fix_control='18960760:on'
*._gc_policy_time=20
*._gc_undo_affinity=TRUE
*.audit_file_dest='/u01/app/oracle/admin/DB0410_nrt1pv/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.0.0.0'
*.control_files='+RECO/DB0410_NRT1PV/CONTROLFILE/current.256.1133755633'
*.control_management_pack_access='DIAGNOSTIC+TUNING'
*.cpu_count=0
*.cursor_sharing='EXACT'
*.db_block_checking='OFF'
*.db_block_checksum='TYPICAL'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_domain='sub07290808380.training.oraclevcn.com'
*.db_files=1024
*.db_lost_write_protect='TYPICAL'
*.db_name='DB0410'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=511g
*.db_unique_name='DB0410_nrt1pv'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB0410XDB)'
*.enable_ddl_logging=TRUE
*.enable_pluggable_database=true
*.encrypt_new_tablespaces='ALWAYS'
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=TRUE
*.inmemory_size=51539607552
*.local_listener='LISTENER_DB0410'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=134217728
*.memory_target=0
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.os_authent_prefix='ops$'
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=2
*.pga_aggregate_limit=24964497408
*.pga_aggregate_target=12482248704
*.processes=1200
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=100
*.sga_target=49928994816
*.spatial_vector_acceleration=TRUE
*.sql92_security=TRUE
*.tde_configuration='keystore_configuration=FILE'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='only'
*.wallet_root='/opt/oracle/dcs/commonstore/wallets/DB0410_nrt1pv'

罪魁祸首在这里，之前设的50G太大了：

*.inmemory_size=51539607552

将其值调小，本例设为42G：

*.inmemory_size=42G

pfile修改对了，现在可以以pfile启动数据库了：

startup pfile='initDB0410.ora';
create spfile='+DATA' from memory;
shutdown immediate;

我们可以看到spfile的位置变了：

$ srvctl config database -d DB0410_nrt1pv
Database unique name: DB0410_nrt1pv
Database name: DB0410
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DB0410_NRT1PV/PARAMETERFILE/spfile.290.1135166043
Password file:
Domain: sub07290808380.training.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths: /opt/oracle/dcs/commonstore
Services: DB0410_orclpdb1.paas.oracle.com
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: DB0410
Configured nodes: bigt02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

一切正常，现在数据库可以启动了：

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 11:56:31 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4.9929E+10 bytes
Fixed Size                  9304376 bytes
Variable Size             922746880 bytes
Database Buffers         3758096384 bytes
Redo Buffers              141688832 bytes
In-Memory Area           4.5097E+10 bytes
Database mounted.
Database opened.

参考：

• How Srvctl Modify Database SPFILE