Notifications
Clear all
Topic starter
We have a EMR test environment on a separate set of servers from live. Every few months we make a complete copy of our live database and copy it to our test environment. We generally do not perform DB Backups of the test environment because if we lost it we would just recreate it.
I know the Oracle archive logs get removed once a backup is complete, but since that never happens, they never get removed in test. The part I don't understand is why, even if no one has used the test environment, we end up with about 50gigs of archive logs per day. We have to keep removing them to keep the db from filling up. Any ideas why we would end up with this much in the logs?
Posted : June 21, 2017 1:56 am
If you don't care about recovery of your test database then I would suggest putting your Oracle TEST database is noarchivelog mode. That way it would not create any archive logs at all. Have a talk with your DBA and suggest it.
If you cannot do that then write a script that monitors the archive log directory for space. when it gets to a certain percentage prune off the x amount of oldest archive logs.
Posted : June 21, 2017 2:55 am
We were also running into this issue. The archive logs in test would fill up and bring down the test environment. After careful consideration we decided that the best course of action would be to turn the logging feature off in test (no need for archive logs).
Here are the instructions that GE provided to disable logging:
Below you’ll find the script – Parts in orange will need to be changed accordingly to match your DB. Copy and paste this in to startit.sql and run this script to turn off archive mode.
set termout on
set echo on
spool <driveLetter>:\oracle\admin\<dbName>\log\start.log
rem ==========================================================
rem Start up the database
rem ==========================================================
connect sys/oracle@<dbName> as SYSDBA
startup mount
-- No Archive Log Mode
alter database noarchivelog;
alter database open;
-- Clean up the workstation records.
connect ml/ml@<dbName>
-- Set all the DTS states from running (4) to inactive (0).
update l3dts set state = 0 where state = 4 and isdts = 1;
Commit;
set serverout on;
execute clean_work;
spool off
exit;
Posted : June 21, 2017 2:58 am
hmmm good question.... i just checked our test database and it does a archive log switch at 11 PM every night and we get about a 185 MB archive log file on average (some times much much smaller) As far as i know we have any sort of automated application jobs disabled in our test database... Might be oracle schedule jobs that create the redo log data. makes me want to read the log file and see what is in.
i've let my dba skills get very very rusty unfortunately.
Posted : June 21, 2017 5:15 am
Sorry, closer inspection it looks every night we get 3 archive logs (3 limes that switch log command is called) at 11 PM. 1 is usually around 200 MB, the other 2 are usually a couple MB or less.
We are not using any type oracle integrated backup to cause the log switches and certainly no manually activity is created 200 MB of redo log.
Our online logs are 600 MB
Posted : June 21, 2017 5:22 am
I don't see this behavior in a production, but it is still 9.8 SP13, while test is 9.10
I just noticed the upgrade to 9.10 re-enabled our outbound subscriptions, which might cause some activity.
I disabled them and will see if this makes a difference and why we are seeing archive data in test.
still researching why in test I get 3 archive logs every night at 11 PM.
Posted : June 21, 2017 5:52 am
sorry for hijacking your thread, but i checked my alert log and i found that in my test I have some sort of backup running every night at 11 the starts with an archive log command on our 3 logs, so that explains the 3 archive logs I am seeing every night. I didn't schedule this backup so need to figure out where it came from!!!
I will let you know if I figure out where the 200 MB of redo is coming from that is getting archived.
Posted : June 21, 2017 6:13 am
Figure out that 9.10 installs the oraclevsswriter service. So my regular windows backup is actually triggering and oracle archive log, and backup commands.
Still doesn't help why we are getting 200 MB of redo in test everyday. but I did learn something new about 9.10
Posted : June 21, 2017 7:13 am
If you are copying Live over you might be getting CQR or eRX stuff happening overnight creating those archive log files.
Posted : June 21, 2017 1:28 pm
yeah, I am not the OP, but i did notice 9.10 upgrade re-enabled CQR subscriptions are turned them off but won't know the results until the day after tomorrow. As for eRX, no appointments, so no formularies or eligibility checks should be being downloaded, anything else that is going on there?
Thanks.
Posted : June 21, 2017 1:39 pm
I don't know that the jobs Oracle runs (jobs.exe) would generate much in the way of archive logs. I wouldn't put it past eRx to be doing something overnight even with no appointments. Though you could have appointments if this is a copy of production, depending on how long ago the copy was made. I don't know if there are any other 3rd party apps you might have that might have hooks and transfer data. Patient portal, etc.
Posted : June 22, 2017 2:53 am
