Oracle: Time bombs

When installing a new system, like a new Oracle database server, there are some standards set which can be sufficient - or not.

Datafile autoextension

When you create an Oracle database, some tablespaces are created by default, like

• SYSTEM
• SYSAUX
• UNDOTBS1
• USERS
• TEMP

SYSTEM and SYSAUX hold the data of the Oracle dictionary as well as other things that have to do directly with Oracle management. TEMP is the first tablespace for temporary data. UNDOTBS1 holds before images of changed data. And USERS is the first tablespace for user data.

Each of these tablespaces consists of 1 datafile, that can be expanded unlimited; on a standard system using 8k blocks, unlimited means 32 GB. So if you do not set a lower maximum size for each datafile, you system can grow up to 160 GB. 160 GB can be a lot, but each desktop system can have much larger disks than that.

But this is only the first part. By default, Oracle uses Smallfile tablespaces. each tablespace consists of 32 GB, and if you need more, you can just add another junk with a maximum of 32 GB. If you know you'll have a big database, you could choose Bigfile tablespaces. These can only consist of one datafile, which can have a maximum size of 128 TB. And if you're allowing the datafile to grow to that maximum, and you disk is smaller than that, your disk space will be fully used.

Archived Logs

Oracle uses Redo Logs to write changes before they are written to disk. If you're using Noachivelog Mode (which you shouldn't do unless it's ok for you to completely lose your data), it's not an issue. But if you use Archivelog Mode (which you should do as this is the only way to be able to get online backups), Archive Logs are created in the Recovery Area (or somewhere else if you changed the parameters).

Every Oracle DBA knows this error:

ORA-00257: archiver error. Connect internal only, until freed

It means that

• Archived Logs are not deleted after backup, or
• there's no backup, or
• you didn't size this area sufficiently

Keep in mind that internal Oracle jobs also produce changes in the database (i. e. the daily maintenance job gathers statistics which are written into tables), so if you're not using the database for a while, the database still creates Archived Logs.

Trace files

Trace files are great if you have to solve a problem. but they can also be pain as they are created not only when having a problem, but also in daily business

Audit logs

In addition to trace files, also audit logs are generated. At least for every SYS login. But audit logs can also be written into the database. but it doesn't matter if they are written to disk or into the database - they will need space on your disk, unless you do some housekeeping...

Versions and Patches

When I started my career, new systems were installed and never patched until their end of life. In these days, that wasn't such a big problem as it is today, because most systems weren't connected to the internet. But it was a problem then, and it is a bigger one now. So always keep your systems up to date - operating systems as well as every other kind of installed software, like Oracle Databases.

Standard passwords

Since I started to work in IT, I very often come in contact with standard passwords, like change_on_install, manager, changeme etc. When you install a new system, especially when many people and companies are involved, standard passwords can make sense. But at the end of the project, for example when the new database server is installed, it could really make sense to set new passwords for every login, or disable some logins which could be a problem in future.

Features

When creating a new database system, Oracle sets some defaults, like features. Each feature has to be patched, so if you use many patches, the patching process can take longer.

Additionally, especially when using Enterprise Edition, many features can be activated, which have to be licensed. So disable these features if you don't want to pay a large amount of money later.