DBs shutdown but still active?

[MCubitt]

I am unsure if this is a problem or not. Worse still, I am unsure if it is os or db specific!

Running Oracle 9i on AIX 5.1 box, I have a script which shuts down databases, using "shutdown immediate" as user sys.

From the log it shows the DB shuts down.

However, the log which restarts the DB shows some apparent os calls to the DBs which are shut down. I wonder if this is normal. Would it prevent full backups ocurring (using Tivoli)?

The log shows the current process BEFORE restarting the DBs:

(DBs tle and IFSL)
UID PID PPID C STIME TTY TIME CMD
oracle 25574 1 0 04:41:49 - 0:00 ora_smon_IFSL
oracle 32260 1 2 05 May - 5:44 /oracle/app/oracle/product/9.2.0.1.0/bin/tnslsnr LISTENER -inherit
tle 44036 1 0 12 May - 0:00 /oracle/tle/tlrt/bin/tlxlpre -P TLXLPRE
oracle 52392 1 0 06:05:45 - 0:00 oracleIFSL (LOCAL=NO)
oracle 55824 1 0 04:41:49 - 0:00 ora_cjq0_IFSL
oracle 69694 1 0 04:41:48 - 0:00 ora_dbw0_IFSL
oracle 70642 1 1 06:06:22 - 0:00 oracleIFSL (LOCAL=NO)
oracle 85780 1 3 04:41:57 - 1:21 ora_j001_IFSL
oracle 92230 1 0 04:41:49 - 0:00 ora_reco_IFSL
oracle 93780 1 0 05:20:36 - 0:02 oracleIFSL (LOCAL=NO)
oracle 105006 1 0 04:41:48 - 0:00 ora_pmon_IFSL
oracle 108360 1 0 06:06:22 - 0:00 oracleIFSL (LOCAL=NO)
oracle 109120 1 1 04:41:57 - 1:27 ora_j002_IFSL
oracle 114238 1 0 04:41:48 - 0:01 ora_lgwr_IFSL
oracle 116252 1 0 04:41:48 - 0:00 ora_ckpt_IFSL
oracle 116790 1 0 06:05:46 - 0:00 oracleIFSL (LOCAL=NO)
oracle 117382 1 2 06:10:00 - 0:00 oracleIFSL (LOCAL=NO)
oracle 118138 1 0 05:20:00 - 0:02 oracleIFSL (LOCAL=NO)
oracle 121746 1 0 04:41:56 - 1:26 ora_j000_IFSL
tle 126098 1 0 08:33:41 - 0:04 /oracle/tle/tlrt/bin/tlxlpost -P TLXLPOST



Now, I know we do not stop the Oracle listner so I guess we should (stop before ending DBs and start before starting Dbs?)

There are also Windows servers running services (which I have now scripted to end too)


Any suggestions how I can ensure teh DBs are fully ended?

Thanks






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Your Oracle instance does not appear to be down. As long as your "pmon", "smon", "dbwr", "ckpt", "lgwr", et cetera background processes are still running, Oracle is still up.

What are the alert-log messages that lead you to believe the instance is down? What are the time stamps for the successful "shutdown", and are they before or after the "4:41:xx" timestamps of your Oracle background processes, above? (And, no, you do not need your listener to be down for successful cold backups.)

When you say, "Any suggestions how I can ensure the DBs are fully ended?" Yes, you can issue o/s "kill" commands on the processes (which behave like a shutdown abort) and, in turn, are not advisable for taking cold backups. And anyway, we need to identify why a "shutdown immediate" is leading you to believe that it is working properly while leaving your standard Oracle background processes up and running...That is bad.

Let us know more,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:13 (13May04) UTC (aka "GMT" and "Zulu"), 09:13 (13May04) Mountain Time)

 

[MCubitt]

Mufasa,

The script to shut down the DBs generates a log, the SQl messages indicated to me the DB instance was shut:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 IFSL
IFSSERVER
9.2.0.1.0 12-MAY-04 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL


Database closed.
Database dismounted.
ORACLE instance shut down.

This log was generated 13 May at 0407.


I have just read the alert log for this DB. It looks like the Db started up again at 4.41am for some reason? Very odd.

Thu May 13 04:05:04 2004
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 194
Thu May 13 04:05:15 2004
ALTER DATABASE CLOSE NORMAL
Thu May 13 04:05:15 2004
SMON: disabling tx recovery
Thu May 13 04:05:31 2004
SMON: disabling cache recovery
Thu May 13 04:05:33 2004
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 2803
Successful close of redo thread 1.
Thu May 13 04:06:40 2004
Completed: ALTER DATABASE CLOSE NORMAL
Thu May 13 04:06:40 2004
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu May 13 04:41:47 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 900
resource_limit = TRUE
shared_pool_size = 603979776
large_pool_size = 16777216
java_pool_size = 33554432
control_files = /oracledata/IFSL/control01.ctl, /oracleindex/IFSL/control02.ctl, /oracledata/IFSL/control03.ctl
db_block_buffers = 20000
db_block_size = 8192
compatible = 9.2.0.1.0
log_buffer = 1572864
log_checkpoint_interval = 100000
log_checkpoint_timeout = 0
db_files = 1024
db_file_multiblock_read_count= 96
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_suppress_errors = FALSE
undo_retention = 900
max_enabled_roles = 100
remote_login_passwordfile= SHARED
global_names = TRUE
instance_name = IFSL
utl_file_dir = /oracle/app/oracle/admin/IFSL/output
job_queue_processes = 3
parallel_max_servers = 5
background_dump_dest = /oracle/app/oracle/admin/IFSL/bdump
user_dump_dest = /oracle/app/oracle/admin/IFSL/udump
max_dump_file_size = 10240
core_dump_dest = /oracle/app/oracle/admin/IFSL/cdump
oracle_trace_collection_name=
sort_area_size = 1572864
sort_area_retained_size = 1572864
db_name = IFSL
open_cursors = 500
os_authent_prefix =
optimizer_mode = choose
parallel_automatic_tuning= TRUE
optimizer_index_cost_adj = 10
pga_aggregate_target = 31457280
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
Thu May 13 04:41:50 2004
ALTER DATABASE MOUNT
Thu May 13 04:41:54 2004
Successful mount of redo thread 1, with mount id 3222643598.
Thu May 13 04:41:54 2004
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu May 13 04:41:55 2004
ALTER DATABASE OPEN
Thu May 13 04:41:55 2004
Thread 1 opened at log sequence 2803
Current log# 2 seq# 2803 mem# 0: /redolog1/IFSL/redo02.log
Current log# 2 seq# 2803 mem# 1: /redolog2/IFSL/redo02a.log
Successful open of redo thread 1.
Thu May 13 04:41:55 2004
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu May 13 04:41:55 2004
SMON: enabling cache recovery
Thu May 13 04:41:56 2004
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu May 13 04:41:56 2004
SMON: enabling tx recovery
Thu May 13 04:41:56 2004
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Thu May 13 05:19:59 2004
Thread 1 advanced to log sequence 2804
Current log# 3 seq# 2804 mem# 0: /redolog1/IFSL/redo03.log
Current log# 3 seq# 2804 mem# 1: /redolog2/IFSL/redo03a.log
Thu May 13 06:10:09 2004
Starting ORACLE instance (normal)
Thu May 13 08:33:48 2004
Thread 1 advanced to log sequence 2805
Current log# 4 seq# 2805 mem# 0: /redolog1/IFSL/redo04.log
Current log# 4 seq# 2805 mem# 1: /redolog2/IFSL/redo04a.log






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Sorry, to answer the final point, I assumed (big mistake with Oracle, it seems) that shutdown immediate would completey shut down the database.. immediately!

Do "other" os processes really have to end outside of this? is the shutdown command not effective enough?

Sorry for any misinterpretation, I am new to this (clearly!)




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Don't apologize...It's not your fault if Oracle chose rather silly labels for their "shutdowns". To clarify, there are three levels/types of Oracle shutdown:

1) "shutdown normal" which Oracle should call "shutdown wait", since Oracle waits for users to finish their work and disconnect on their own.

2) "shutdown immediate" which Oracle should call "shutdown interrupt", since in this case, Oracle interrupts connected sessions and rollsback any "in-flight" transactions.

3) "shutdown abort" which is a fairly accurate label, but might be more descriptive if Oracle called it "shutdown shutoff", since it is identical in behaviour to your shutting off the power...no time for any gracefulness or rollbacks; it just stops and requires any automatic recovery (from your on-line redo logs) upon next startup.

Now, back to your specific situation. The subsequent "mysterious" startup that occurred is precisely why I needed you to confirm whether the timestamps of shutdown preceded the timestamps for the background processes. DBAs often build automatic restarts into their databases, specifically, if a roving/recurring "cron" job detects that a database is down, it automatically kicks off a restart sequence. Is something like that happenning in your installation? If so, then you will probably want to manually disable the restart if you need to have the db down during some sort of cold backup/maintenance.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:59 (13May04) UTC (aka "GMT" and "Zulu"), 09:59 (13May04) Mountain Time)

 

[MCubitt]

Santa,

Thnak you for your English translation of Oracle-speak! I quite agree with you on your comments.

I had no idea that there is anything which attempts to restart a database if it is shut down.

The oracle cron job starts the DBs at 6am (only it can't because they are already active, we now know!).

I see no oracle or root cron job which could be restarting the DB.

is there a way I can discover what it is that is attempting to help me but giving me grief?!

Thanks again,





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

To be clear, there is nothing built-into Oracle that restarts the database if it is down. That is a behaviour that you or someone else would have to explicitly create.

Now, first question: Are you THE DBA for this database? Who else's o/s login is also a member of your designated DBA group? (specifically, who else can issue the Oracle "startup" command without receiving the error "insufficient privileges"?) If there is someone else, confirm whether or not they were connected at the time of the mysterious re-start. Also, just ask them if they restarted the db. (Although a 4:41 a.m. re-start would indicate a pretty anal-retentive colleague, but why wouldn't s/he notify you?)

Second, if you have no one else that would have explicitly started the database at that hour, then you must look deeper for automatic-restart possibilities. Oracle does not, however, have auto-restart features without explicit setup on your part.

Let us know your findings,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:21 (13May04) UTC (aka "GMT" and "Zulu"), 11:21 (13May04) Mountain Time)

 

[MCubitt]

Mufasa,

Am I the DBA? Not yet... Our supplier of teh ERp system came and installed Oracle and the DB. I have taken over and am learning "on the job". Yes, this is not an ideal situation and yes, I am due training. All I have had is an introduction to Oracle 9i course!

Unless the supplier set anything up then it would be down to us.

No one in my dept or the supplier would be accessing the system at that time, as far as I can imagine.

I thought the best bet was to look at the logs from last night. It the time is teh same, it's unlikely to be anything other than an automated process... maybe!

Well, well, well.. the DBs shut down at the correct time and restarted at the correct time. perhaps someone was dialing in... we have salesmen who use a portal to access the DB, perhaps an early start?


Thanks for your help. I think I need to continue to monitor things...






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[stefanhei]

Hi MCubitt.
Do you use RMAN for your backups? If so it could also be the RMAN-script which starts the DB after the backup (and as it's a cold backup tries to shut down first too).
If you think someone logged into your AIX-box using oracle account you can check with the [/b]last[/b] command like

last <oracleOSAccount> |pg

Stefan

 

[MCubitt]

Stef,

No, we dont use RMAN. We attempt to shut down everything on teh UNIX server (Oracle bases, anyway) and take a snapshot of the system using Tivoli.

Thanks for the "last" tip but as you see, it didn't assist in this case:
oracle pts/2 10.56.57.14 14 May 09:11 still logged in.
oracle pts/3 128.25.1.101 13 May 23:34 - 23:36 (00:02)
oracle pts/3 10.56.57.14 13 May 15:04 - 17:22 (02:17)
oracle pts/2 10.56.57.14 11 May 16:52 - 17:31 (00:39)
oracle ftp 10.56.57.14 11 May 14:24 - 14:41 (00:17)
oracle ftp 10.56.57.14 11 May 10:10 - 10:17 (00:07)
oracle ftp 10.56.57.14 11 May 10:08 - 10:10 (00:01)
oracle pts/2 10.56.57.14 11 May 09:20 - 10:19 (00:58)
oracle pts/1 10.56.57.27 11 May 08:09 - 21:15 (13:05)
oracle pts/1 128.25.1.101 10 May 23:54 - 23:55 (00:00)





There's no need for sarcastic replies, we've not all been this sad for that long!