Lesson 13

1. with Archiving and
2. without Archiving

This module introduced you to various methods of performing physical backups with and without archiving.
You learned how to do open and closed database backups. You also learned the importance of frequent and regular database and tablespace backups as essential strategies for any recovery scheme. Having completed this module, you should be able to:

1. Perform database backups using operating system commands
2. Describe the recovery implications of closed and open database backups
3. Perform closed and open database backups
4. Identify the different types of control file backups
5. Identify the backup implications of the logging and nologging options
6. Describe backup issues associated with read-only tablespaces

This module introduced you to the following terms:

1. Database Writer: The Database Writer is responsible for writing modified blocks from the database buffer cache back to the database files.
2. System change number: System change number is a clock value for the Oracle database that describes a committed version of the database. It functions as a timestamp that helps to ensure transaction consistency.
3. Media failure: Media failure refers to the error that occurs when you try to write or read a file that is required to operate the database. It is also called disk failure because there is a physical problem reading or writing physical files on the disk. Mirrored online redo logs: A mirrored online redo log is also called a multiplexed online redo log. It contains copies of online redo log files physically located on a separate disk. Any changes made to one member of the group are recorded in other members.
4. Operating system backup: An operating system (O/S) backup is made using an operating system command. Operating system backups can be written to disk or tape in any format that a specific operating system supports.
5. Control file: A control file is a binary file containing the name and creation time of the database, the names and locations of a database's datafiles and redo log files. Every time an instance of the database is started, its control file is used to identify the datafile and redo log file that must be open for the database to run properly.

In the next module, you will learn how to recover a database in NOARCHIVE mode in case of media failure. The following section discusses data dictionary views and their functions.

1. V$DATAFILE: Contains a list of the names and status for all datafiles
2. V$CONTROLFILE: Contains the names of all control files
3. V$LOGFILE: Contains the names of all redo log files
4. V$BACKUP: Contains information about which files are in backup mode
5. V$ARCHIVE_LOG: Contains archived log information from control files
6. V$ARCHIVE_DEST: Contains information about the current instance and archive log destinations
7. V$LOG_HISTORY: Contains log file information from the control file
8. V$DATABASE: Contains information about the current state of archiving
9. DBA_DATA_FILES: Contains a list of all datafiles and their respective tablespaces

You can view data file numbers and data file names in the V$DATAFILE, V$DATAFILE_COPY, or V$DATAFILE_HEADER view. For example, to view data file numbers and data file names in your database, issue this SQL command:

RMAN> select file#, name from v$datafile;

You can also issue the RMAN report schema command to display data file names and numbers. Once you know the name or number for each file you want to back up, you can use the backup datafile command to perform the actual backup operation.

Before you restore the data file from the RMAN backup, verify whether the missing data file physically exists at the OS level. Also confirm that the Oracle software owner has the appropriate read and write privileges on the missing data file and directory. We also recommend querying the data dictionary for more information. The V$DATAFILE_HEADER view derives its information from the data file headers and reports in the ERROR and RECOVER columns any potential problems.For example, a YES or null value in the RECOVER column indicates there is a problem:
An Oracle SQL query executed against the `v$datafile_header` view, which is used to check the status of data files in the database.
Here's the code and an explanation of its functionality:

SELECT file#, status, error, recover
FROM v$datafile_header;

Sample Output:

FILE#  STATUS   ERROR           REC
-----  -------  --------------  ---
1      ONLINE   NO              NO
2      ONLINE   NO              NO
3      ONLINE   NO              NO
4      ONLINE   FILE NOT FOUND  NO

Explanation:

1. Columns in the Query:
   • FILE#: Indicates the file number of the data file.
   • STATUS: Shows whether the data file is online or offline. In this case, all files are marked ONLINE, meaning they are accessible by the database.
   • ERROR: Displays any errors associated with the data file. For file 4, the error is FILE NOT FOUND, indicating that the database cannot locate the specified data file.
   • REC: Indicates if recovery is needed for the data file. A value of NO means no recovery is currently required.
2. Significance of the Query:
   • This query is used to monitor the health of data files in the database.
   • The error FILE NOT FOUND for file 4 suggests that this data file is missing or inaccessible, which may require manual intervention to resolve.
3. Actionable Insights:
   • Investigate the location and availability of file 4 to determine why it is not found.
   • Consider restoring the file from a backup or verifying its path in the database configuration.

1) Verify whether the missing data file physically exists at the OS level
Also the V$RECOVER_FILE view displays the status of files needing media recovery. The V$RECOVER_FILE reads from the control file and displays information about files needing media recovery:

Oracle SQL query targeting the `v$recover_file` view to check the status and errors of data files that might need recovery. Here's the code and an explanation:

SELECT file#, online_status, error
FROM v$recover_file;

Sample Output:

FILE#  ONLINE_STATUS   ERROR
-----  -------------   --------------
4      ONLINE          FILE NOT FOUND

Explanation:

1. Columns in the Query:
   • FILE#: Identifies the file number in the database.
   • ONLINE_STATUS: Shows the status of the file. ONLINE indicates that the file is currently online.
   • ERROR: Displays any errors related to the file. In this case, the error for file 4 is FILE NOT FOUND, indicating that the database expects the file to be present but cannot locate it.
2. Significance of the Query:
   • This query is particularly useful for diagnosing issues with database files that might need recovery.
   • The error FILE NOT FOUND suggests that a data file is missing or inaccessible, possibly due to a misconfiguration, deletion, or corruption.
3. Actionable Steps:
   • Verify the physical location of the missing file and ensure that it matches the path recorded in the database control file.
   • If the file is missing, attempt to restore it from a recent backup.
   • Use appropriate recovery commands, such as RECOVER DATAFILE, to synchronize the file with the database.

2) V$RECOVER_FILE view displays the status of files needing media recovery Note I f you restore a control file from a backup, the V$RECOVER_FILE view will not contain accurate information.

Question: I want to display the maxsize for a tablespace data file.
Maxsize is the total of maxsize of datafiles in that particular tablespace which is the total of Megabytes allocated to the datafiles.
For example,

alter database datafile 'file1' size 10G autoextend on maxsize 32G;
alter database datafile 'file2' size 15G autoextend on maxsize 32G;

Normal tablespace scripts including the one on this site is having total of size of datafiles (that is 10g + 15g = 25g).
I am looking for script which will use maxbytes of datafiles (that is 32g + 32g = 64g).

A backup is either consistent or inconsistent. To make a consistent backup, your database must have been shut down cleanly and remain closed for the duration of the backup. All committed changes are written to the data files during the shut down process, so the data files are in a transaction-consistent state. When you restore your data files from a consistent backup, you can open the database immediately. If the database is in ARCHIVELOG mode, then you can make inconsistent backups that are recoverable using archived redo log files. Open database backups are inconsistent because the online redo log files contain changes not yet applied to the data files. The online redo log files must be archived and then backed up with the data files to ensure recoverability. Despite the name, an inconsistent backup is as robust a form of backup as a consistent backup. The advantage of making inconsistent backups is that you can back up your database while the database is open for updates.

Click the Quiz link below to review your understanding of physical backups with and without archiving.
Archiving Physical Backups - Quiz