Basic Threats to Your Oracle 12c Database
Before you can create guidelines to lower the risk of data loss and corruption in your Oracle 12c database or take the steps to recover your information quickly, you have to understand what you’re up against. A good backup and recovery plan can help guard against the following threats.
Instance failure in Oracle 12c
Instance failure occurs when Oracle, as a running program, crashes. The good news here is Oracle has provided a recovery mechanism and can automatically recover from instance failure. All you have to do is restart the database instance. However, you might want to investigate what caused the instance failure so you can prevent it from happening again.
Lost files in Oracle 12c
Losing files within the Oracle code tree can result in your database crashing. The code tree has the files that you install when you put Oracle on your system, including the database files, the patch sets and patches you apply, and any other tools required to make your application run.
Make sure the code tree is part of the OS backup; it’s an often-overlooked portion of a solid backup and recovery strategy. Sometimes the loss of a file in the Oracle code tree can be restored quickly out of the OS backup instead of doing an entire reinstallation.
Keep all the Oracle installation software handy in case you need to reinstall it. Keeping it handy means having a hard copy of it ready to go rather than storing it on the network. In the event of a disaster, you may not have access to those network resources.
Don’t rely on being able to download it from the Oracle website. That could take hours depending on how busy the site is.
Dropped objects in Oracle 12c
Objects in the database are dropped by humans. Problems occur when someone drops an object that she either didn’t mean to drop or that she later decides she wants back.
Unless DBAs are involved with application design, many times they’re simply told what and when to remove something from the database. Always take a Data Pump export of that object before you drop it.
Media failure in Oracle 12c
Media failure occurs when a file or log required for the database to operate is lost, including
Data files, which store the bulk of your data. Data files typically have an extension of .dbf.
Control files, which store information about how your database is configured. For example, are you in ARCHIVELOG mode? How many data files are you allowed to have? You can find control files by checking the initialization parameter control_files. Typically, these files have an extension of .ctl or .con.
Parameter files, such as PFILE or SPFILE. If your database uses an SPFILE, check the initialization parameter for spfile. Otherwise, by default, the file is in $ORACLE_HOME/dbs for Linux/UNIX or $ORACLE_HOME/database for Windows.
Archive logs, which are created when you turn on archiving in the database. You can find the logs by checking the initialization parameter log_archive_dest_n (with n being a number from 1–10). Else, look in at the parameter db_recovery_file_dest to find them in the Fast Recovery Area. Typically, archive logs have an extension of .arc.
Corruption in Oracle 12c
Corruption is one of the most elusive and difficult types of failure to deal with. Figuring out why it occurred is often a considerable challenge. When things are corrupted, rows become unreadable in the database and the rows can report errors.
Corruption can be:
Physical: Actual sectors on the disk are physically damaged.
Logical: Not physical damage, but data somehow went missing or is unreadable.
Your data: The data itself is just wrong. For example, all your last_names were updated to be the same as the first names.
In the data dictionary: This is corruption in the metadata that Oracle uses to manage the database.
You can use Recovery Manager, Block Media Recovery, and Data Pump to help recover from corruption problems. Fortunately, this type of failure is rare.
If you’re having corruption troubles in your database, consider contacting Oracle Support for help. This is the type of issue where Oracle earns those hefty support contracts.
User error in Oracle 12c
User error isn’t actually a type of failure, but it’s probably the most common cause for recoveries. This is mentioned it because you can protect against user error (even your own in case you’re fallible).
Proper training, including training your users, can help reduce the chances of user error.