Some simple pointers on how to keep an Oracle database "up and running".
Usable for Oracle 10g/Oracle 11g/Oracle 12c.
Date : 27 februari, 2017
By: Albert van der Sel
Status: Just Starting.
Remark: Please refresh the page to see any updates.
It's a bit of a silly document, since the note is just a small set of pointers,
without proper explanations.
The goal is to provide some clues on how to recover from various causes that have led to a database in "trouble".
Ofcourse it's evident that no note can be complete on such subject.
Furher, I think that this note is not for experienced DBA's. However, for starting DBA's, up to a medior level..., it might be !
It would be great if you would have:
- General overview of the architecture of an Oracle database (processes, redo, undo, system etc..).
- General overview of Storage with respect to Oracle (filesystems, raw, ASM etc..).
- An idea of the logs and diagnostics (alertlog, traces etc..).
- An idea of how to work with RMAN, and expdp/impdp.
- An idea of full backups, and archive log backups, and the meaning of "restore" and "recover" of a database.
- An idea of Oracle's network stack and configuration (listener, tnsnames etc..).
- An idea of the various database objects like tables, indexes, procedures etc..
- An idea of how to work with the webbased EM (grid), and certainly the prompt utility "sqlplus".
- An idea of the init.ora/spfile.ora startup configuration file.
- How to stop and start a database/Instance.
- The significance of the "controlfile".
- The most important difference between 10g/11g, and 12c (most notably the CDB, and pdb containers).
IMPORTANT RULES (a bit trivial I guess: sorry!):
Maybe this note is unneccessary and a complete waste of my efforts, if you strictly adhere to the following rules:
◼ The most important rule is, that you take care that you always have recent backups of your production database(s).
Thus: implement a watertight backup/recovery procedure. If needed, dive "deep" into RMAN, and optionally the "flashback" options.
Test your procedures regularly.
Believe it or not, but this rule really forms the core of everything.
You must recent backups ofcourse, but a certain amount of historical backups too. As to the question on how long to keep older backups,
some business policy is probably in place (or should be in place).
For example, it's quite common to put apart (and safely store) weekly, montly backups, and yearly backups.
Having Oracle database backups is one thing, but what about the "machine" itself?
You probably have your oracle Instances on Virtual Machines.
If such a VM burns down, is it easy to get it back? Is there a replica (or so)?
To rebuild a machine from scratch (installing the OS, Oracle RDBMS, patches, add-ons etc..) can be a lot of work.
Although this is more for storage admins/system engineers: You better check this one too for production VM's.
◼ If an outage of a database is very costly for the business, then implement a HA cluster with a DR solution,
or, at least a DR implementation with, e.g., Data Guard.
◼ Keep in full control of the production database(s), and implement the principle of "least required privileges",
so that end users can never (accidentally) drop or alter objects (DDL).
Thus take care of an ironclad security implementation for production databases.
Also, database access should go via the proper application. Keep away dilettants who use other tools, with ODBC etc..
◼ Although the above three rules are the most important ones, it probably is also true that you must:
-keep your databases at a reasonble version, and patch level.
-you might implement a certain degree of auditing for some of your most important databases.
-try to seperate transactional database from reporting- and or BI solutions. For BI, create other databases.
Now, let me try to formulate "my pointers".....