User Managed Online Backups (Hot Backup)
While Recovery Manager (RMAN) is highly recommended for database backup and recovery operations your organization may require you to perform backups using a custom process or you might have an interest in knowing what is required to perform a user managed backup.
Read more »
Remove a Database from a RMAN Recovery Catalog
There will come a time that you will need to remove a database from the recovery catalog. Removing a database from the recovery catalog removes all metadata for the database from the catalog.
Read more »
Registering a Database with a Recovery Catalog
Registering a database with a recovery catalog records the existence of the database with the recovery catalog. Once the database is registered metadata about the database and its backups will be stored in the recovery catalog.
Read more »
Creating an RMAN Recovery Catalog
The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.
Read more »
Archival Backups RMAN Backups for Long-Term Retention
RMAN has the ability to make an archival backup that is exempt from the configured retention policy. An archival backup is an all inclusive self-contained backup of the database at specific point in time. Archival backups are commonly used to satisfy regulatory requirements such as end year or quarter backups.
Read more »
Dropping a Database via RMAN
In Oracle Database 10gR1 Oracle introduced the RMAN command DROP DATABSE. This one simple statement has the ability to completely remove a database including all RMAN backups with the optional INCLUDING BACKUPS clause.
Read more »
Backup Duplexing with RMAN
RMAN has the ability to write duplexed backup sets creating up to four identical copies of each backup piece in a backup set to different destinations. Duplexing provides an extra layer of protection against disaster, media damage and human error that is easy to configure.
Read more »
Creating Image File Copy Backups with RMAN
When using RMAN to back up to disk you have two options in how to write the backups. The default is a backup set which uses unused block compression and is in an RMAN-specific format. The other method is an image copy that is a bit for bit copy of the data file, archive redo log file or control file.
Read more »
User Managed Consistent Database Backup (Cold Backup)
While Recovery Manager (RMAN) is highly recommended for database backup and recovery operations your organization may require you to perform backups using a custom process or you might have an interest in knowing what is required to perform a user managed backup.
Read more »
Backup Does Not Run When Scheduled in CRON
You have created a script to perform the backup and have tested it to verify that it works as expected. However when the script is executed in CRON it either appears to not run or it runs with errors.
Read more »
Quick RMAN configuration for disk based backups
This document will detail a quick set up of RMAN for disk based backups using Oracle Database 11gR2 base. Read more »
Restore database to another host using RMAN
This document will detail the steps to restore a database on a new host using RMAN. For this demonstration we will be using Oracle Database 11gR2 on Linux and disk based backup.
Recover from a corrupt or missing control file
This document will detail recovery scenarios dealing with missing or corrupted control files using RMAN disk based backups. Read more »
Recover from a missing data file
This document will detail two recovery scenarios dealing with a missing datafile using a RMAN disk based backup. Read more »
Block Media Recovery with RMAN
This document will detail a block media recovery scenario using a RMAN disk based backup. If you need a document for configuring RMAN see the post Setting up RMAN for backup and recovery using Database Control
Multiplexing the Redo Log Files
To protect against failure of the redo logs, Oracle allows redo logs to be multiplexed. With multiplexed redo logs two or more identical copies can be placed in separate locations. The log writer process (LGWR) writes the same redo information to each multiplexed log.
Configure Flashback Database
As your database grows larger the length of time required to recover to a point in time grows. In some cases time required to restore all files to perform a point in time recovery becomes prohibitive.
Flashback database provides a solution to point in time recovery issues. Flashback Database using a new type of log file called the Flashback Database log. The Flashback Database log periodically receives before images of database blocks to use for backing out changes to the datafiles in a flashback operation.
The Flashback Database logs are stored in the Flash Recovery Area and are automatically created and managed when Flashback Database is enabled.
This document will detail the configuration of Flashback Database and perform a Flashback.
Setting up RMAN for backup and recovery using Database Control
Using Database Control you can configure RMAN to back up you database to disk or tape with relative ease. This document will detail how to configure RMAN for disk based backups and perform a backup.
Read more »
Enable/Disable Archive Log Mode 10g/11g using Database Control
Database Control makes a lot normal administrative tasks easier. You can enable and disable Archive Log Mode with a few mouse clicks as this document will detail. If you would like to do this through the command line take at the post Enable/Disable Archive Log Mode 10g/11g.
Enable/Disable Archive Log Mode 10g/11g
Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.
If you would like to perform these tasks using Database Console see the post Enable/Disable Archive Log Mode 10g/11g using Database Console.
Enable Archive Log Mode
The following are the steps required to enable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:02:52 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 25 Current log sequence 27 SQL>
The log mode is No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can determine the path by looking at the parameter RECOVERY_FILE_DEST.
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3852M
SQL>
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.
SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both; System altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u02/app/oracle/oradata/orcl/arch Oldest online log sequence 25 Current log sequence 27 SQL>
Now we shutdown the database and bring it backup in mount mode.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 511708752 bytes Database Buffers 331350016 bytes Redo Buffers 5132288 bytes Database mounted. SQL>
Lastly all that is needed it set archive log mode and open the database.
SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/app/oracle/oradata/orcl/arch Oldest online log sequence 25 Next log sequence to archive 27 Current log sequence 27 SQL>
We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well. In Oracle 9i an earlier another parameter needed to be set in order to enable automatic archiving. This in no longer the case in 10g and 11g as automatic archiving is enabled when the database is placed in archive log mode.
You can switch to the log file to see that an archive is written to archive log location.
SQL> alter system switch logfile; System altered. SQL> host [oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/arch 1_27_711369564.dbf [oracle@ora1 ~]$ exit exit SQL>
Disable Archive Log Mode
Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.
The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:54:05 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/app/oracle/oradata/orcl/arch Oldest online log sequence 26 Next log sequence to archive 28 Current log sequence 28 SQL>
The Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 511708752 bytes Database Buffers 331350016 bytes Redo Buffers 5132288 bytes Database mounted. SQL>
All that is left is to disable archive log mode and open the database.
SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u02/app/oracle/oradata/orcl/arch Oldest online log sequence 26 Current log sequence 28 SQL>
As you can see, ARCHIVELOG mode has been disabled.