故障现场：
[oraprod@ebsdb02 ~]$ ./stopdg.sh
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2022 10:42:34
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebsdb02.rhc.cloud)(PORT=1529)))
The command completed successfully
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 9 10:42:34 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> Connected.
SQL>
Database altered.
SQL> ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraprod@ebsdb02 ~]$ ps -ef| grep ora
root 8373 8326 0 10:26 pts/2 00:00:00 sudo su - oraprod
root 8392 8373 0 10:26 pts/2 00:00:00 su - oraprod
oraprod 8393 8392 0 10:26 pts/2 00:00:00 -bash
oraprod 8803 8393 0 10:29 pts/2 00:00:00 sqlplus as sysdba
root 10404 9402 0 10:39 pts/0 00:00:00 sudo su - oraprod
root 10410 10404 0 10:39 pts/0 00:00:00 su - oraprod
oraprod 10411 10410 0 10:39 pts/0 00:00:00 -bash
oraprod 10454 10411 0 10:39 pts/0 00:00:00 sqlplus as sysdba
root 10872 10811 0 10:41 pts/1 00:00:00 sudo su - oraprod
root 10877 10872 0 10:41 pts/1 00:00:00 su - oraprod
oraprod 10878 10877 0 10:41 pts/1 00:00:00 -bash
oraprod 11122 10878 0 10:42 pts/1 00:00:00 ps -ef
oraprod 11123 10878 0 10:42 pts/1 00:00:00 grep ora
[oraprod@ebsdb02 ~]$ ps -ef| grep pmon
oraprod 11147 10878 0 10:43 pts/1 00:00:00 grep pmon
[oraprod@ebsdb02 ~]$ ./startdg.sh
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-MAR-2022 10:43:23
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/prod/db/12.1.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/prod/db/12.1.0/network/admin/PROD_ebsdb02/listener.ora
Log messages written to /u01/prod/db/12.1.0/diag/tnslsnr/ebsdb02/proddg/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb02.rhc.cloud)(PORT=1529)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebsdb02.rhc.cloud)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias PRODDG
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 09-MAR-2022 10:43:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/prod/db/12.1.0/network/admin/PROD_ebsdb02/listener.ora
Listener Log File /u01/prod/db/12.1.0/diag/tnslsnr/ebsdb02/proddg/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb02.rhc.cloud)(PORT=1529)))
Services Summary...
Service "PROD" has 1 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 9 10:43:23 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3.2212E+10 bytes
Fixed Size 5302704 bytes
Variable Size 1275070032 bytes
Database Buffers 3.0870E+10 bytes
Redo Buffers 61804544 bytes
SQL>
Database altered.
SQL> select * from v$i
Database altered.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraprod@ebsdb02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 9 10:43:52 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> ^[[A " - rest of line ignored.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraprod@ebsdb02 ~]$
在dataguard环境中，经常发现standby库的pxxx进程的trace文件中有这样的信息，查了metalink，官方的解释：并不是bug或者错误，而是因为在primary database执行了不可恢复的操作导致的，解决的办法特简单，在primary database上执行以下操作: alter database force logging;
可以用以下的脚本查看那些数据文件有问题：
SELECT name, unrecoverable_change# , unrecoverable_time
FROM v$datafile
where UNRECOVERABLE_CHANGE# > 0 order by 1;
dataguard中，主库和从库已经配置好，主库已经执行过rman的duplicate操作，要把从库起到open状态时，报了一下错误：
ORA-10456: cannot open standby database; media recovery session may be in progress
解决方法：
先在从库停止standby
SQL>alter database recover managed standby database cancel;
这时再执行
SQL>alter database open;
备库就正常open啦
然后再启动日志应用
SQL>alter database recover managed standby database using current logfile disconnect;
参考文档：http://blog.itpub.net/26736162/viewspace-1780863/
主备库归档日志应用情况查询脚本：
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
FROM V$ARCHIVED_LOG A
WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
FROM V$ARCHIVED_LOG B
WHERE B.THREAD# = A.THREAD#
AND B.APPLIED = 'YES')
AND RESETLOGS_CHANGE# = (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
ORDER BY A.THREAD#, A.SEQUENCE#;
网友评论