I installed Oracle 8.1.7 on a Unix box and client on Win2K box. I created a test database db1 using the dbassist command Wizard. During this process I set my database to be in ARCHIVELOG mode.
Now after eight hours, we started loading data from third-party software into Oracle. And after some time, it stopped loading the data. I tried to check it, and the RBS tablespace was FULL. I tried to bring the database down, and bring it back up, but it didn't come up.
What do I need to do in these situations? Can I run the database in ARCHIVELOG mode for a long time, say 2-3 days?
The fact that your RBS tablespace was full does not have anything to do with running in ARCHIVELOG mode. Rollback segments are online redo logs do work hand in hand to manage transactions consistency. Running in ARCHIVELOG mode is just a way of storing the data in the online redo logs for future usage, by archiving the data to another location.
It sounds more like your archive log destination (specified by the LOG_ARCHIVE_DEST parameter) is full. When this disk device becomes full, the database comes to a halt until space is made available. After all, if you are archiving transactions then how can you allow more transactions if you can't store them anywhere. So check your archive log destination. Make sure that you have enough disk space. If necessary, copy the old archive logs to another disk volume, or to tape.
Your RBS tablespace becoming full is a sign that you do not have enough space dedicated to holding rollback information for your load process you were doing. You'll need to increase the size of this tablespace.
You can stay up infinitely in ARCHIVELOG mode, provided that you have enough space in the archivelog destination device. Since no device has infinite space, this means that you'll occasionally have to remove old, obsolete archived redo logs from that device. When do these logs become obsolete? Any logs older than your backup are no longer needed. But many DBAs keep multiple generations of backups around. Let's say that you perform a weekly backup. And you want to keep three generations of backups on hand. Then you'll need to keep three weeks worth of archived redo logs. But you can move them around if needed to clear up room.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.