Topic: Oracle Database Question

[PaulF]

I appreciate this is a bit of a specialised one, and I've done a fair bit of googling, but can't seem to find an answer.

We've got an application that we're doing some development on and the redo log filled up. I managed to find out how to extend the redo log to keep the database running for a bit.  It seems that the right thing to do is archive the log periodically to prevent this happening again in the future. I've run RMAN backup database plus archivelog; but no free space seems to have been added.  I know in SQL server world this isn't a problem and we'll just start overwriting when the log wraps around again. But I'd prefer to know that I've done things right before it falls over again !

[The Gulleysucker]

I'm not particularly familiar with Oracle DB but I assume you've had a read of this.. https://docs.oracle.com/cd/B19306_01/server.102/b14231/onlineredo.htm

At a glance, there's a bit that might be pertinent...

Forcing Log Switches

A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.

You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large redo log files that take a long time to fill.

To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause.

The following statement forces a log switch:

ALTER SYSTEM SWITCH LOGFILE;


The section on Drop'ing immediately preceding that could be of use as well....

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file.

To drop a member of an active group, you must first force a log switch.

It  looks like the REDO is Oracles idea of a transaction failover/recovery system and the system by default writes to a log group of these files (their size can specified though they are restricted to OS size). I get the impression from the article (someone may be able to correct me on this) that these should be archived using the RMAN etc, but they will remain but I'm guessing from then on will be overwritten until they 'fill up' again , hence the size stays the same.

Any good reason you are using Oracle or have you no choice? I only ask as many of the people I've been dealing with in the high end Enterprise scalable and failover world have or are making the transition over to using MongoDB over the kast year or so.

[PaulF]

I'm not particularly familiar with Oracle DB but I assume you've had a read of this.. https://docs.oracle.com/cd/B19306_01/server.102/b14231/onlineredo.htm

At a glance, there's a bit that might be pertinent...

Forcing Log Switches

A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.

You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large redo log files that take a long time to fill.

To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause.

The following statement forces a log switch:

ALTER SYSTEM SWITCH LOGFILE;


The section on Drop'ing immediately preceding that could be of use as well....

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file.

To drop a member of an active group, you must first force a log switch.

It  looks like the REDO is Oracles idea of a transaction failover/recovery system and the system by default writes to a log group of these files (their size can specified though they are restricted to OS size). I get the impression from the article (someone may be able to correct me on this) that these should be archived using the RMAN etc, but they will remain but I'm guessing from then on will be overwritten until they 'fill up' again , hence the size stays the same.

Any good reason you are using Oracle or have you no choice? I only ask as many of the people I've been dealing with in the high end Enterprise scalable and failover world have or are making the transition over to using MongoDB over the kast year or so.

Thanks GalleySucker, I had seen that amongst the docs, but skipped past it. Focussing on it does help a bit.
Client likes Oracle. Way out of my comfort zone, in a fun kind of way.