Oracle 部分报错处理

Oracle报错处理

处理Oracle数据库启动报错 ORA-27104

报错内容：system-defined limits for shared memory was misconfigured

查看报错日志（我的日志位置，根据自身环境确定）

cat /u01/app/oracle/diag/rdbms/oradb/oradb/alert/log.xml

错误内容如下：

<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='oracle'
 host_addr='fd4a:8abb:ad4::cfb' pid='3810'>
 <txt>System cannot support SGA size of 2352 MB.
 </txt>
</msg>
<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='oracle'
 host_addr='fd4a:8abb:ad4::cfb' pid='3810'>
 <txt>Total system memory configured is 1838 MB.
 </txt>
</msg>
<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='oracle'
 host_addr='fd4a:8abb:ad4::cfb' pid='3810'>
 <txt>Instance maximum shared memory size should be less than 1654 MB.
 </txt>

我的环境中，根据报错可以看出配置的SGA相关配置的内存太大了，要求小于1654MB

解决办法：数据库不能启动，需要调整编辑参数文件来修改相关的SGA值

我的spfile文件位置是：/u01/app/oracle/product/19c/dbs

[oracle@oracle dbs]$ pwd
/u01/app/oracle/product/19c/dbs
[oracle@oracle dbs]$ ls
hc_oradb.dat  init20210220.ora  init.ora  lkORADB  orapworadb  spfileoradb.ora
[oracle@oracle dbs]$

先生成一个临时spfile文件来修改配置

SQL> create pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora' from spfile;

File created.

SQL> !vi /u01/app/oracle/product/19c/dbs/init20210220.ora

打开后配置如下：

oradb.__data_transfer_cache_size=0
oradb.__db_cache_size=1778384896
oradb.__inmemory_ext_roarea=0
oradb.__inmemory_ext_rwarea=0
oradb.__java_pool_size=0
oradb.__large_pool_size=16777216
oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oradb.__pga_aggregate_target=822083584
oradb.__sga_target=2466250752
oradb.__shared_io_pool_size=134217728
oradb.__shared_pool_size=520093696
oradb.__streams_pool_size=0
oradb.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oradb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORADB/control01.ctl','/u01/app/oracle/oradata/ORADB/control02.ctl'
*.db_block_size=8192
*.db_name='oradb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)'
*.local_listener='LISTENER_ORADB'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=782m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2346m
*.undo_tablespace='UNDOTBS1'

将 oradb.__sga_target= 和 .sga_target= 这两个参数修改到推荐值范围，然后保存。保存之后使用心得spfile启动数据库

SQL> startup pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora';
ORACLE instance started.

Total System Global Area 1426059296 bytes
Fixed Size          8896544 bytes
Variable Size         352321536 bytes
Database Buffers     1056964608 bytes
Redo Buffers            7876608 bytes
Database mounted.
Database opened.
SQL>

现在数据库能启动成功了，还需要将修改后的pfile文件覆盖原spfile文件

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1426059296 bytes
Fixed Size          8896544 bytes
Variable Size         352321536 bytes
Database Buffers     1056964608 bytes
Redo Buffers            7876608 bytes
Database mounted.
Database opened.
SQL>

完成

修改archivelog报错 ORA-00265

执行过强制关闭数据库的操作就会出现的错误

解决步骤：将数据库起到open状态，正常关闭shutdown immediate，再起到mount状态

SQL> alter database open ;------启动数据库到open状态
Database altered.
SQL> shutdown immediate------正常关闭immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;------启动到mount状态
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             536874104 bytes
Database Buffers          289406976 bytes
Redo Buffers                2392064 bytes
Database mounted.
SQL> alter database archivelog;------执行更改archivelog
Database altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7