An Oracle Zombie Database Mystery (and a chance to win $25!)
I’m launching a YouTube channel to post training content. I have plans for a series of videos on TFA (Trace File Analyzer) and AHF (Autonomous Health Framework) as well as my passion project, running Oracle on Docker. To celebrate and promote my channel I’ve created a puzzle and I’m offering a prize—a $25 Amazon gift card!
Contest Rules
Comment on the video that shows this behavior with your answer. The first person to correctly identify what’s happening wins!. (Full contest rules at the end of this post.)
The Puzzle—Cure the Zombie!
The puzzle is to identify the Oracle feature that causes the database to enter a zombie state!
I start with a running database and report some information from v$database and v$instance:
SQL> select name 2 , instance_name 3 , version 4 , open_mode 5 , database_role 6 , flashback_on 7 , status 8 from v$database 9 , v$instance; NAME INSTANCE_NAME VERSION OPEN_MODE DATABASE_ROLE FLASHBACK_ON STATUS --------- ---------------- ----------------- -------------------- ---------------- ------------------ ------------ ORCLCDB ORCLCDB 19.0.0.0.0 READ WRITE PRIMARY NO OPEN 1 row selected.
I stop the database and startup mount:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 570424424 bytes Fixed Size 9137256 bytes Variable Size 335544320 bytes Database Buffers 218103808 bytes Redo Buffers 7639040 bytes Database mounted.
So far, so good. Next, I stop the database but it fails!
SQL> shutdown immediate ORA-01109: database not open Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
The system shows database processes are still active. The database doesn’t eventually stop; it’s entered its zombie state!
[oracle@oci19c scripts]$ ps -ef | grep ORCLCDB oracle 8145 1 0 17:35 ? 00:00:00 ora_pmon_ORCLCDB oracle 8147 1 0 17:35 ? 00:00:00 ora_clmn_ORCLCDB oracle 8149 1 0 17:35 ? 00:00:00 ora_psp0_ORCLCDB oracle 8153 1 0 17:35 ? 00:00:00 ora_vktm_ORCLCDB oracle 8157 1 0 17:35 ? 00:00:00 ora_gen0_ORCLCDB oracle 8159 1 0 17:35 ? 00:00:00 ora_mman_ORCLCDB oracle 8163 1 0 17:35 ? 00:00:00 ora_gen1_ORCLCDB oracle 8166 1 0 17:35 ? 00:00:00 ora_diag_ORCLCDB oracle 8171 1 0 17:35 ? 00:00:00 ora_dbrm_ORCLCDB oracle 8173 1 0 17:35 ? 00:00:00 ora_vkrm_ORCLCDB oracle 8177 1 0 17:35 ? 00:00:00 ora_pman_ORCLCDB oracle 8179 1 0 17:35 ? 00:00:00 ora_dia0_ORCLCDB oracle 8181 1 0 17:35 ? 00:00:00 ora_dbw0_ORCLCDB oracle 8183 1 0 17:35 ? 00:00:00 ora_lgwr_ORCLCDB oracle 8185 1 0 17:35 ? 00:00:00 ora_ckpt_ORCLCDB oracle 8188 1 0 17:35 ? 00:00:00 ora_lg00_ORCLCDB oracle 8190 1 0 17:35 ? 00:00:00 ora_smon_ORCLCDB oracle 8192 1 0 17:35 ? 00:00:00 ora_lg01_ORCLCDB oracle 8196 1 0 17:35 ? 00:00:00 ora_reco_ORCLCDB oracle 8200 1 0 17:35 ? 00:00:00 ora_lreg_ORCLCDB oracle 8204 1 0 17:35 ? 00:00:00 ora_pxmn_ORCLCDB oracle 8216 1 0 17:35 ? 00:00:00 ora_tmon_ORCLCDB
The last entry in the alert log is this:
ALTER DATABASE CLOSE NORMAL Stopping Emon pool ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
After logging back in to SQL*Plus I’m unable to start or stop the database. It initially shows me as connected, then that the instance is idle. Nothing I do causes the alert log to record any additional messages. The only escape is running shutdown abort. After that, I’m able to start the database again and everything is back to normal:
[oracle@oci19c scripts]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 17 17:36:11 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected. SQL> startup ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 SQL> conn / as sysdba Connected to an idle instance. SQL> startup ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 SQL> conn / as sysdba Connected to an idle instance. SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 570424424 bytes Fixed Size 9137256 bytes Variable Size 335544320 bytes Database Buffers 218103808 bytes Redo Buffers 7639040 bytes Database mounted. Database opened.
I illustrate the entire behavior in my video if you want to see it in action.
I know of three separate ways to cause this behavior, which I’ve encountered several times in my career (first on Oracle 7 on HP back in 1995). I call this a behavior rather than a problem because it’s something that is intentionally part of Oracle. I can make a case that it’s a bug but there’s also an argument that things are working exactly as designed. A feature, not a bug! I didn’t locate anything about this in Metalink or search engines—and that’s based on me being able to search for the symptoms, cause and solution. I hope this will be an entertaining challenge!
To help you narrow your search (or deepen the mystery).
This doesn’t depend on:
Database version or edition. I first saw this on version 7 and recreated it on 11g, 12.1, 12.2, 18c and 19c.
Patch level.
Host infrastructure. It’s reproducible on databases running on physical systems, VM, Oracle Cloud, and Docker. (FYI: This is a good reason DBAs should be familiar with Docker!)
Any database parameter.
Whether the database uses pfile or spfile.
Whether the database is a single instance, RAC or engineered system.
If the database uses ASM or filesystem storage for any component.
Environment settings. Nothing needs to be set in the environment to cause this.
Changes to Oracle code, binaries, or operating system components. It doesn’t require changing anything installed as part of the database to see this behavior.
For Linux systems, any OS flavor and version that runs Oracle will do.
Operating system shouldn’t make a difference but I only tested it for this blog/contest in Linux. I have seen it on Windows, HP and Solaris in the past. Based on my understanding of the mechanisms behind this behavior it should behave the same, but I can’t confirm that 100% and I don’t have access to a Windows system to verify.
I shouldn’t need to say this but… don’t experiment with this in an environment people rely on! It involves starting and stopping the database and it places the database in a limbo where it’s neither up nor down.
In the video I run this script:
set feedback on
set pause off
set echo on
select name
, instance_name
, version
, open_mode
, database_role
, flashback_on
, status
, force_logging
from v$database
, v$instance;
pause Press ENTER to shutdown immediate...
shutdown immediate
pause Press ENTER to startup mount...
startup mount
pause Press ENTER to shutdown immediate...
shutdown immediate
pause Press ENTER to startup...
startup
set pause off
select name
, instance_name
, version
, open_mode
, database_role
, flashback_on
, status
, force_logging
from v$database
, v$instance;Now… Who’s going to claim the $25?
Be sure to subscribe to my channel for updates on new Oracle content, especially if you’re interested in Trace File Analyzer/Autonomous Health Framework or learning about running Oracle on Docker!
ELIGIBILITY: Open only to persons who are 18 years of age or older. All applicable federal, state and local laws and regulations apply. Void where prohibited or restricted by law. NO PURCHASE NECESSARY.
AGREEMENT TO RULES: By participating, the Contestant (“You”) agree to be fully unconditionally bound by these Rules, and You represent and warrant that You meet the eligibility requirements. In addition, You agree to accept the decisions of Sponsor as final and binding as it relates to the content of this Campaign.
CONTEST PERIOD: The Contest entry period begins at 8:00am ET on Monday, April 20, 2020 and ends when a correct answer is posted in the video comments section (the "Contest Period"). The winner is the first person to correctly identify the cause behind the behavior shown in the video (https://youtu.be/QrYy32q1S9o) and described in the accompanying post (https://oraclesean.com/blog/zombie-database-mystery).
HOW TO ENTER: During the Contest Period, subscribe to the channel https://www.youtube.com/channel/UCrEjDFoqHDk-558TP8xiQXw and post an answer in the comments of the video (https://youtu.be/QrYy32q1S9o).
WINNER SELECTION: Winners will be determined by the timestamp on their comment. The Winner will be notified via email to the email address or YouTube account associated with their comment and/or via e-mail and/or telephone within one (1) week of posting the correct answer. The Sponsor is the judge of the Contest and their decisions on all matters relating to the Contest shall be final. In the event that a potential winner is disqualified for any reason, Sponsor may, in its sole discretion, award the applicable prize to an alternate winner selected at random.
CONDITIONS: Sponsor reserves the right, at their sole discretion, to cancel, terminate, modify or suspend the Contest (or portion thereof). Sponsor also reserves the right at their sole discretion to disqualify the Entry of any individual. All federal, state and local taxes on prize and any expenses not specified herein are the sole responsibility of winner. Should a dispute arise regarding the identity of the claimant, Sponsor reserves the sole right to determine the eligible party. By entering, entrant waives all rights to bring (and covenants not to bring) any claim against Sponsor or any others associated with this Contest. Entrants further agree to release and hold harmless Sponsor from any and all liability arising from their participation in the Contest, or Sponsor’s use of any entry information. Any disputes that may arise hereunder shall be governed in all respects by the laws of the State of Idaho without regard to the conflicts of laws principles of any jurisdiction. Venue with respect to any such disputes shall be had in the state and federal courts of the State of Idaho.
PRIVACY POLICY: Information submitted with an entry will be used solely to identify the winning entry and deliver the prize.
PRIZE: A digital Amazon Gift Card in the amount of $25 USD.
PUBLICITY AND RELEASE: As a condition of entry into the Contest, except where prohibited by law, each entrant and each winner by accepting a prize grants to Sponsor, all right, title and interest in to publicize, broadcast, display and/or otherwise use the Contest entrant’s name, city, state, and biographical material (collectively, “Licensed Rights”) in any media for advertising and publicity purposes, without additional review, compensation, or approval of the winner. If Winner does not respond to notification, or is not present when prize delivery is attempted, such prize will be forfeited. By participating, entrants agree to be bound by the Official Rules and the decisions of the judges, which are final and binding in all respects.
LIMITATION OF LIABILITY: Sponsor assumes no responsibility or liability for (a) any incorrect or inaccurate entry information, or for any faulty or failed electronic data transmissions; (b) any unauthorized access to, or theft, destruction or alteration of entries at any point in the operation of this Contest; (c) any technical malfunction, failure, error, omission, interruption, deletion, defect, delay in operation or communications line failure, regardless of cause, with regard to any equipment, systems, networks, lines, satellites, servers, camera, computers or providers utilized in any aspect of the operation of the Contest; (d) inaccessibility or unavailability of any network or wireless service, the Internet or website or any combination thereof; (e) suspended or discontinued Internet, wireless or landline phone service; or (f) any injury or damage to participant's or to any other person’s computer or mobile device which may be related to or resulting from any attempt to participate in the Contest or download of any materials in the Contest.
If, for any reason, the Contest is not capable of running as planned for reasons which may include without limitation, infection by computer virus, tampering, unauthorized intervention, fraud, technical failures, or any other causes which may corrupt or affect the administration, security, fairness, integrity or proper conduct of this Contest, the Sponsor reserves the right at its sole discretion to cancel, terminate, modify or suspend the Contest in whole or in part. In such event, Sponsor shall immediately suspend all prize award. Sponsor shall not have any further liability to any participant in connection with the Contest.
The Contest is in no way sponsored, endorsed, administered by, or associated with YouTube, LinkedIn or Twitter. All entries must comply with YouTube’s Community Guidelines (http://www.youtube.com/t/community_guidelines) and YouTube’s Terms of Service (https://www.youtube.com/static?template=terms). Any entry that doesn’t comply to these terms will be disqualified.
