数据库在重新启动后更改挂载模式

Question

我们让Oracle XE 10.2.0.1.0在CentOS 6.9上运行。

数据库始终稳定工作，但系统重新启动除外。如果在操作系统终端中发出reboot命令，则数据库将进入MOUNT模式。然后，我们发出service oracle-xe restart 两次，以使DB进入OPEN模式。第一次命令很快发出，最后，第二次重新启动需要一些时间。正如我在日志中看到的，由于某些资源阻塞，第一次无法引发实例。它发出一个终止信号，然后在发出第二个service oracle-xe restart命令之后启动。

我把alert_XE.log 这里带来了

因此，这里是事件和时代：

1. 星期五10月21日12:11:46 2022 -第一次DB重新启动后，DB进入安装模式。
2. 10月21日星期五12:26:50 2022年-第一期service oracle-xe restart发行
3. 星期五10月21日16:51:12 2022秒service oracle-xe restart发行DB进入开放模式

如果没有足够的信息为我提供解决方案的路径，您能给我写信告诉我我需要增加的日志级别在哪里以及哪些日志级别，或者我需要监视哪些日志丢失信息。

<#>更新

我用LD_LIBRARY_PATH修正了错误，直接编辑了/etc/init.d/oracle-xe，并添加了一个接一个的环境变量防御行：

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LD_LIBRARY_PATH

现在，如果我按照OPEN的建议尝试使用miracle173数据库，就会得到以下错误：

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

<#>更新

现在我们在alter.log中有了新的错误，下面是ALTER DATABASE MOUNT之后的内容：

Mon Oct 24 17:02:49 2022
Beginning crash recovery of 1 threads
Mon Oct 24 17:02:49 2022
Started redo scan
Mon Oct 24 17:02:49 2022
Completed redo scan
 147 redo blocks read, 78 data blocks need recovery
Mon Oct 24 17:02:49 2022
Started redo application at
 Thread 1: logseq 1791, block 3
Mon Oct 24 17:02:49 2022
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1791 Reading mem 0
  Mem# 0 errs 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_dgsl2gd0_.log
Mon Oct 24 17:02:49 2022
Completed redo application
Mon Oct 24 17:02:49 2022
Completed crash recovery at
 Thread 1: logseq 1791, block 150, scn 284886106315
 78 data blocks read, 78 data blocks written, 147 redo blocks read
Mon Oct 24 17:02:49 2022
Thread 1 advanced to log sequence 1792
Thread 1 opened at log sequence 1792
  Current log# 1 seq# 1792 mem# 0: /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_dgsl2dvq_.log
Successful open of redo thread 1
Mon Oct 24 17:02:49 2022
SMON: enabling cache recovery
Mon Oct 24 17:02:50 2022
Successfully onlined Undo Tablespace 1.
Mon Oct 24 17:02:50 2022
SMON: enabling tx recovery
Mon Oct 24 17:02:50 2022
Database Characterset is CL8MSWIN1251
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Process QMNC died, see its trace file
Mon Oct 24 17:02:51 2022
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_2423.trc:
ORA-00443: background process "QMNC" did not start
Mon Oct 24 17:02:51 2022
Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_2423.trc:
ORA-00450: background process 'QMNC' did not start
ORA-00443: background process "QMNC" did not start
Error 450 happened during db open, shutting down database
USER: terminating instance due to error 450
Instance terminated by USER, pid = 2423
ORA-1092 signalled during: ALTER DATABASE OPEN...

这是/usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_2423.trc的尾巴：

*** 2022-10-24 17:02:49.712
KCRA: start recovery claims for 78 data blocks
*** 2022-10-24 17:02:49.721
KCRA: blocks processed = 78/78, claimed = 78, eliminated = 0
*** 2022-10-24 17:02:49.721
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1791 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 78/78 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 377/377 = 1.0
----------------------------------------------
*** 2022-10-24 17:02:51.204
Process QMNC is dead (pid=2545, state=3):
*** 2022-10-24 17:02:51.204
KSTDUMP: In-memory trace dump
TIME:SEQ#        ORAPID   SID EVENT  OP DATA
========================================================================
8DBD1DEB:0000066E    18    38 10254  34 KSBS1P: process USER trying to start background QMNC
8DBD1DEF:0000066F    18    38 10254  37 KSBS1P: process USER obtained PR enqueue to start background QMNC
8DCC0868:0000067C    18    38 10254  51 KSBS1P: out of loop: process did not start
KSTDUMP: End of in-memory trace dump
*** 2022-10-24 17:02:51.204
ORA-00443: background process "QMNC" did not start
ORA-00450: background process 'QMNC' did not start
ORA-00443: background process "QMNC" did not start

<#>更新

好的，我成功地使用MOUNT帐户会话连接到DB的oracle ed实例，我尝试了ALTER DATABASE OPEN：

[root@MOBILE_TEST bdump]# su - oracle
-bash-4.1$ pwd
/usr/lib/oracle/xe
-bash-4.1$ groups
dba
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 24 17:45:10 2022

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

Connected to an idle instance.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL>

<#>更新

我将日志输入到oracle-xe中：

start() {
        MAXI_LOG="/root/temp/ora.log"
        echo $(date) >> $MAXI_LOG

        if [ "$CONFIGURE_RUN" != "true" ]
        then
        echo "Oracle Database 10g Express Edition is not configured.  You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database."
                exit 0
        fi

        status=`ps -ef | grep tns | grep oracle`
        if [ "$status" == "" ]
        then
                echo "TNSLSTENER" >> $MAXI_LOG
                if [ -f $ORACLE_HOME/bin/tnslsnr ]
                then
                        echo "Starting Oracle Net Listener."
                        $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR  start" > /dev/null 2>&1
                fi
        fi

        echo "Starting Oracle Database 10g Express Edition Instance."
        echo "START" >> $MAXI_LOG
        $SU -s /bin/bash  $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" >> $MAXI_LOG 2>&1
        RETVAL=$?

        if [ $RETVAL -eq 0 ]
        then
                echo "GOOD" >> $MAXI_LOG
                echo
        else
                echo Failed to start Oracle Net Listener using $ORACLE_HOME/bin/tnslsnr\
                        and Oracle Express Database using $ORACLE_HOME/bin/sqlplus.
                echo "BAD" >> $MAXI_LOG
                RETVAL=1
        fi

        return $RETVAL
}

我重新启动了系统几次，下面是我们在$MAXI_LOG中的内容：

25.10.2022 17:31:32 MSK 2022
TNSLSTENER
START
ORACLE instance started.

Total System Global Area  532676608 bytes
Fixed Size                  1259880 bytes
Variable Size             150996632 bytes
Database Buffers          377487360 bytes
Redo Buffers                2932736 bytes
Database mounted.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
GOOD
25.10.2022 17:33:25 MSK 2022
TNSLSTENER
START
ORACLE instance started.

Total System Global Area  532676608 bytes
Fixed Size                  1259880 bytes
Variable Size             150996632 bytes
Database Buffers          377487360 bytes
Redo Buffers                2932736 bytes
Database mounted.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
GOOD

我使用ls -t --full-time . | head -10查看最后的跟踪，这就是我所拥有的：

[root@MOBILE_TEST ~]# cd /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/
[root@MOBILE_TEST bdump]# ls -t --full-time . | head -10
итого 16780
-rw-r--r--. 1 oracle dba 1906729 2022-10-25 17:33:34.694999956 +0300 alert_XE.log
-rw-r-----  1 oracle dba   39525 2022-10-25 17:02:32.848999960 +0300 xe_lgwr_2361.trc
[root@MOBILE_TEST udump]# ls -t --full-time . | head -10
итого 8976
-rw-r-----  1 oracle dba    3385 2022-10-25 17:33:30.663999958 +0300 xe_ora_2376.trc
-rw-r-----  1 oracle dba    8643 2022-10-25 17:33:27.485999961 +0300 xe_ora_2343.trc
-rw-r-----  1 oracle dba    5126 2022-10-25 17:31:37.601999962 +0300 xe_ora_2385.trc

这是xe_ora_2385.trc的尾巴：

*** SERVICE NAME:() 2022-10-25 16:34:08.669
*** SESSION ID:(38.1) 2022-10-25 16:34:08.669
kccsga_update_ckpt: num_1 = 8, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0
/usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_2376.trc
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
System name:    Linux
Node name:      MOBILE_TEST.domain.central
Release:        2.6.32-696.1.1.el6.i686
Version:        #1 SMP Tue Apr 11 16:37:48 UTC 2017
Machine:        i686
Instance name: XE
Redo thread mounted by this instance: 0 
Oracle process number: 18
Unix process pid: 2376, image: oracle@MOBILE_TEST.domain.central (TNS V1-V3)

*** SERVICE NAME:() 2022-10-25 17:33:30.664
*** SESSION ID:(38.1) 2022-10-25 17:33:30.664
kccsga_update_ckpt: num_1 = 8, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0

<#>更新

我设置了/etc/security/limits.conf：

*          hard    nproc     32768
*          hard    nofile    32768

在ulimit脚本中记录init：

$SU -s /bin/bash $ORACLE_OWNER -c "ulimit -a | grep '\-[nu]'" >> $ORA_LOG 2>&1

你看，限制是没有问题的：

Wed Oct 26 17:04:28 MSK 2022
open files                      (-n) 32768
max user processes              (-u) 32768

我还把Y放在了/etc/oratab中。

但肉质仍然存在..。

Answer 1

谢谢miracle173的回答！感谢一位DBA电报公司的人组提供的帮助。

因此，这个问题的解决方案是非常复杂的，由许多不同的设置组成。而且，即使在15次启动时，DB也不会启动。但对我们来说没问题。

首先，我开始使用LD_LIBRARY_PATH解决错误。我在网上搜索解决方案，并找到了这个链接。

1. 我在LD_LIBRARY_PATH中添加了新的配置
2. 我发现旧的 LD_LIBRARY_PATH配置也在/etc/profile中

尽管旧的和新的配置，系统没有反应。

我将登录到/etc/init.d/oracle-xe和/usr/lib/oracle/xe/.bash_profile中。比我所看到的，在~/.bash_profile服务的System V Init启动期间，系统根本不处理这些文件(D20和/etc/profile)。

然后，在环境变量定义块之后，将LD_LIBRARY_PATH配置放在init脚本的头上：

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LD_LIBRARY_PATH

我在启动脚本中添加了日志，这对我有很大帮助：

ORA_LOG=/var/log/oracle.log
echo "-------------------------------------------------" >> $ORA_LOG
date >> $ORA_LOG
$SU -s /bin/bash $ORACLE_OWNER -c "ulimit -a | grep '\-[nu]'" >> $ORA_LOG 2>&1

此外，我还将start中的日志记录作为stop函数：

$SU -s /bin/bash  $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" >> $ORA_LOG 2>&1
...
$SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql" >> $ORA_LOG 2>&1

我没有记录侦听器，因此没有问题。

init脚本的最终版本是这里

此外，我还处理了系统限制的问题。如您所见，我还将ulimit -a记录在init脚本的头上。我在/etc/security/limits.conf中配置了系统限制：

*          hard    nproc     32768
*          hard    nofile    32768

现在我们可以在/var/log/oracle.log中看到更多的消息：

-------------------------------------------------
Wed Oct 26 17:20:58 MSK 2022
open files                      (-n) 32768
max user processes              (-u) 32768

我做的最后一个修正是“一步一步初始化”。我将$ORACLE_HOME/config/scripts/startdb.sql文件更改为：

connect / as sysdba
startup
exit

至：

connect / as sysdba
startup nomount
alter database mount;
alter database open;
exit

这是我们新日志中的最后记录：

-------------------------------------------------
Thu Oct 27 13:26:01 MSK 2022
open files                      (-n) 32768
max user processes              (-u) 32768
START
ORACLE instance started.
Total System Global Area  532676608 bytes
Fixed Size                  1259880 bytes
Variable Size             150996632 bytes
Database Buffers          377487360 bytes
Redo Buffers                2932736 bytes
Database altered.
Database altered.