What is ARCHIVELOG mode?
Oracle saves the filled redo logs which allows you to recover the database to any point in time.
What is NOARCHIVELOG mode?
The filled redo logs are overwritten and not saved. In this mode, you can only restore the backed up data but no changes that were made AFTER the backup.
‘What’s a consistent backup?
A consistent backup is when you can restore the database without performing a recovery. This is because the datafile(s) and the control file(s) contain data taken from the same point in time and therefore, have the same SCN.
Note: This usually only occurs when a database is shutdown gracefully (i.e. SHUTDOWN NORMAL -or- SHUTDOWN TRANSACTIONAL).
What’s an inconsistent backup?
An inconsistent backup contains datafiles from different points in time (i.e. different SCN’s) because changes are being committed while the database is still online. Therefore, you must apply archived and online redo logs to the backup in order to recover the database to the current point in time.
What’s a hot backup?
A backup that is made while the database is online (i.e. most common backup)
What’s a cold backup?
A backup made while the database is offline (i.e. usually during a scheduled maintenance window).
What are the 3 types of backups you can perform?
- Whole Database - Backup all files including the control file.
- Tablespace - Backup all the datafiles that belong to a tablespace
- Datafile - Backup a single datafile
What are the most important Backup Guidelines?
- Multiplex & Mirror the Control Files (on separate disks managed by different disk controllers)
- Multiplex the Archive Redo Logs (LOG_ARCHIVE_MIN_SUCCEED_DEST = 2)
- Backup the SPFILE (or init.ora)
- Backup sqlnet.ora
- Backup tnsnames.ora
- Backup listener.ora
- Backup password file
- Perform frequent backups
What is Multiplexing?
Multiple copies of a file that are being written to at the same time by the Oracle software.
What is Mirroring?
Mirroring involves multiple disks in a RAID array that are exact copies of each other.
Ex: If you have two copies of a control file in a mirrored RAID array you will actually have four copies in total.
What is the Flash Recovery Area?
The default location on disk for storing every file related to backup and recovery operations:
Note: The FRA is efficient because it allows for fast recovery because all the necessary files are located on disk instead of tape. Also, Oracle manages the files in your FRA using Automatic Disk-Based Backup and Recovery which makes managing the backup and recovery files easier.
What backup-related files can the FRA store?
- Datafile Copies (image copies of datafiles using the BACKUP AS COPY command)
- RMAN Backup Pieces (OS file containing the backup of a datafile, a control file, and archived redo logs)
- Incremental Backups
- Control File Auto-Backups
- Archived Redo Logs
- Online Redo Logs
- Current Control Files
- Flashback Logs
What is Automatic Disk-based Backup and Recovery?
Oracle manages the backup, including datafiles, archive redo log files, control files, online redo log files and any other files you include in your FRA (i.e. SPFILE, tnsnames.ora, listener.ora).
How do you enable Automatic Disk-based Backup and Recovery?
You must:
- Configure an FRA
- Allocate Disk Space to the FRA
- Allocate MAX disk size to the FRA (i.e. 100GB)
- Specify how long to keep backup-related files
How do you configure an FRA?
You must set five initialization parameters either at the creation of the database or dynamically:
- DB_RECOVERY_FILE_DEST_SIZE=___ (Sets the max size of the FRA)
- DB_RECOVERY_FILE_DEST=___ (Sets the location of the FRA on disk)
- DB_CREATE_FILE_DEST=___ (Sets the location of the datafiles, control files, and online redo logs)
- LOG_ARCHIVE_DEST_1 = ‘LOCATION=USE_DB_CREATE_FILE_DEST’
- LOG_ARCHIVE_DEST_2 = ‘LOCATION=USE_DB_RECOVERY_FILE_DEST’
Note: You MUST set the location of the FRA (Parameters 1 & 2) on a disk SEPARATE from where the active database files are
Note: You MUST set DB_RECOVERY_FILE_DEST_SIZE first!
Note: LOG_ARCHIVE_DEST_n sets the location of the archived redo log files. We have the archive redo logs going to the location with the datafiles AND the FRA.
How do you dynamically configure the FRA?
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST_SIZE=100GB SCOPE=BOTH;
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST =‘/u01/app/oracle…’ SCOPE=BOTH;
Note: SCOPE=BOTH ensures the changes are writtenly permanently to the SPFILE.
How do you backup the FRA?
RMAN> BACKUP RECOVERY AREA (Backs up every file in the FRA to tape)
RMAN> BACKUP RECOVERY FILES (Backs up every file in the FRA as well as recovery-related files on the OS to tape).
What view can I use to see FRA-related information?
V$RECOVERY_FILE_DEST
Ex: SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
Note: This view can be used to check the current location, disk quota, space in use, total files, and space reclaimable by deleting files.
What view can I use to check the space being used by different types of files in the FRA?
V$FLASH_RECOVERY_AREA_USAGE
Ex: SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE
How do you delete obsolete backup files?
RMAN> DELETE OBSOLETE;
What are the ways you can connect to RMAN?
- Connect using Database Authentication:
- $ rman
- RMAN> CONNECT TARGET /
- Connect using OS Authentication:
- $ rman target /
- Connect to the Recovery Catalog (Recommended):
- $ rman target d0dv catalog rman/rman@d0dv
Note: In the example above the rman user is connecting to the target database “d0dv” through the recovery catalog @ d0dv.
Explain RMAN Scripting?
RMAN comes with a scripting language that allows you to automate backup tasks easily. You can store scripts in the recovery catalog (i.e. stored scripts) or as text files.
Note: Stored scripts are advantageous as they allow anyone logging into RMAN to use them.
What are Command Files and how do you use them?
Command Files (.cmd) are OS files that contain RMAN commands that need to be executed. They can be executed from the OS or from RMAN.
Ex: Command File “testfile.cmd” contains the command:
BACKUP DATABASE PLUS ARCHIVELOG;
OS Execution: $ rman target / @testfile.cmd
OS Execution using Recovery Catalog:
$ rman target d0dv catalog rman/rman@d0dv CMDFILE testfile.cmd LOG outfile.txt
RMAN Execution: RMAN> @testfile.cmd
How do you create Stored Scripts?
Stored scripts in RMAN are created using the CREATE SCRIPT command followed by the contents which are enclosed in { }.
Ex:
RMAN> CREATE SCRIPT nightly_backup {
ALLOCATE CHANNEL c1 TYPE DISK;
BACKUP DATABASE FORMAT ‘/u01/app/oracle/%u’
SQL ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’;
}
