主页 / dba / 问题 / 318523
Accepted
Olga Pshenichnikova
Asked: 2022-10-22 06:20:17 +0800 CST

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

  • 772

我们一直在Oracle XE 10.2.0.1.0运行CentOS 6.9。

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

我带来alert_XE.log 这里

所以这里是事件和时间：

  1. 2022 年 10 月 21 日星期五 12:11:46 - 重启后第一个 DB 启动，DB 进入 MOUNT 模式
  2. 2022 年 10 月 21 日星期五 12:26:50 - 第一service oracle-xe restart期
  3. Fri Oct 21 16:51:12 2022 - 第二service oracle-xe restart期，DB 进入 OPEN 模式

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

更新

LD_LIBRARY_PATH我通过直接编辑修复了错误/etc/init.d/oracle-xe，并在环境变量定义块之后附加了以下行：

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

现在，如果我按照Miracle173 的建议尝试OPEN数据库，则会出现以下错误：

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的 ed 实例，我尝试了：DBoracleALTER 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 <none>
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。

但是问题依然存在...

logs

1 个回答

  1. Best Answer

    感谢miracle173的回答！并感谢DBA电报组的帮助。

    所以这个问题的解决方案非常复杂，并且包含很多不同的设置。即使十五次DB不启动。但这对我们来说没问题。

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

    1. 我把新 LD_LIBRARY_PATH配置放在.bash_profile
    2. 我也发现了旧 LD_LIBRARY_PATH配置/etc/profile

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

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

    然后我LD_LIBRARY_PATH在环境变量定义块之后将配置放在初始化脚本的头部：

    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.
    • 0

相关问题