Why and when should I backup my database?
Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:
* Rate of data change/ transaction rate
* Database availability/ Can you shutdown for cold backups?
* Criticality of the data/ Value of the data to the company
* Read-only tablespace needs backing up just once right after you make it read-only
* If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
* If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
* Etc.
Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunication.
[edit] What strategies are available for backing-up an Oracle database?
The following methods are valid for backing-up an Oracle database:
* Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details.
* Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
* Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
* RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry.
Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files.
[edit] What is the difference between online and offline backups?
A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
[edit] What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
RMAN> run {
set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
restore database;
recover database;
}
[edit] My database is down and I cannot restore. What now?
This is probably not the appropriate time to be sarcastic, but, recovery without backups are not supported. You know that you should have tested your recovery strategy, and that you should always backup a corrupted database before attempting to restore/recover it.
Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.
Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!
DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database. More info about DUDE is available at http://www.ora600.nl/.
[edit] How does one backup a database using the export utility?
Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports. For more information about the Oracle export and import utilities, see the Import/ Export FAQ.
[edit] How does one put a database into ARCHIVELOG mode?
The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
Issue the following commands to put a database into ARCHIVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Alternatively, add the above commands into your database's startup command script, and bounce the database.
The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc
NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE 2:' ARCHIVELOG mode was introduced with Oracle 6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE 5: When running Oracle Real Application Clusters (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode. See the RAC FAQ for more details.
[edit] I've lost an archived/online REDO LOG file, can I get my DB back?
The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.
NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.
_allow_resetlogs_corruption = true
This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).
Steps:
* Do a "SHUTDOWN NORMAL" of the database
* Set the above parameter
* Do a "STARTUP MOUNT" and "ALTER DATABASE OPEN RESETLOGS;"
* If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the "ALTER DATABASE OPEN RESETLOGS;" command.
* Wait a couple of minutes for Oracle to sort itself out
* Do a "SHUTDOWN NORMAL"
* Remove the above parameter!
* Do a database "STARTUP" and check your ALERT.LOG file for errors.
* Extract the data and rebuild the entire database
[edit] User managed backup and recovery
This section deals with user managed, or non-RMAN backups.
[edit] How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
[edit] How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyzFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
[edit] My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the Scripts section of this site.
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
ALTER DATABASE END BACKUP;
This command must be issued when the database is mounted, but not yet opened.
[edit] Does Oracle write to data files in begin/hot backup mode?
When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files.
When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
To solve this problem, simply switch to RMAN backups.
[edit] RMAN backup and recovery
This section deals with RMAN backups:
[edit] What is RMAN and how does one use it?
Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recovering Oracle Databases. RMAN ships with the database server and doesn't require a separate installation. The RMAN executable is located in your ORACLE_HOME/bin directory.
In fact RMAN, is just a Pro*C application that translates commands to a PL/SQL interface. The PL/SQL calls are statically linked into the Oracle kernel, and does not require the database to be opened (mapped from the ?/rdbms/admin/recover.bsq file).
RMAN can do off-line and on-line database backups. It cannot, however, write directly to tape, but various 3rd-party tools (like Veritas, Omiback, etc) can integrate with RMAN to handle tape library management.
RMAN can be operated from Oracle Enterprise Manager, or from command line. Here are the command line arguments:
Argument Value Description
-----------------------------------------------------------------------------
target quoted-string connect-string for target database
catalog quoted-string connect-string for recovery catalog
nocatalog none if specified, then no recovery catalog
cmdfile quoted-string name of input command file
log quoted-string name of output message log file
trace quoted-string name of output debugging message log file
append none if specified, log is opened in append mode
debug optional-args activate debugging
msgno none show RMAN-nnnn prefix for all messages
send quoted-string send a command to the media manager
pipe string building block for pipe names
timeout integer number of seconds to wait for pipe input
-----------------------------------------------------------------------------
Here is an example:
[oracle@localhost oracle]$ rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target;
connected to target database: ORCL (DBID=1058957020)
RMAN> backup database;
...
[edit] How does one backup and restore a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database. RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/backup/%d_t%t_s%s_p%p'
(database);
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
[edit] How does one backup and restore archived log files?
One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup scripts:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/archback/log_%t_%sp%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
The "delete input" clause will delete the archived logs as they are backed-up.
List all archivelog backups for the past 24 hours:
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
Here is a restore example:
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }
[edit] How does one create a RMAN recovery catalog?
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
[edit] How does one integrate RMAN with third-party Media Managers?
The following Media Management Software Vendors have integrated their media management software with RMAN (Oracle Recovery Manager):
* Veritas NetBackup - http://www.veritas.com/
* EMC Data Manager (EDM) - http://www.emc.com/
* HP OMNIBack/ DataProtector - http://www.hp.com/
* IBM's Tivoli Storage Manager (formerly ADSM) - http://www.tivoli.com/storage/
* EMC Networker - http://www.emc.com/
* BrightStor ARCserve Backup - http://www.ca.com/us/data-loss-prevention.aspx
* Sterling Software's SAMS:Alexandria (formerly from Spectralogic) - http://www.sterling.com/sams/
* SUN's Solstice Backup - http://www.sun.com/software/whitepapers/backup-n-storage/
* CommVault Galaxy - http://www.commvault.com/
* etc...
The above Media Management Vendors will provide first line technical support (and installation guides) for their respective products.
A complete list of supported Media Management Vendors can be found at: http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htm
When allocating channels one can specify Media Management spesific parameters. Here are some examples:
Netbackup on Solaris:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';
Netbackup on Windows:
allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";
Omniback/ DataProtector on HP-UX:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';
or:
allocate channel 'dev_1' type 'sbt_tape' parms 'ENV=OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';
[edit] How does one clone/duplicate a database with RMAN?
The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.
Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.
Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":
connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /
run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';
allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;
duplicate target database to dupdb
logfile
GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}
The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.
Notes: the "set newname" commands are only required if your datafile names will different from the target database.
The newly cloned DB will have its own unique DBID.
[edit] Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?
Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database. In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken. Let's look at an example:
Let's take a backup (partial in our case for ilustrative purposes):
$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog
RMAN> backup datafile 1;
Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04[/code]
Now, let's destroy one of the control files:
SQL> show parameters CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/orcl/control01.ctl,
/oradata/orcl/control02.ctl,
/oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl</pre>
Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:
SQL> startup NOMOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1301536 bytes
Variable Size 262677472 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes</pre>
Now, from SQL*Plus, run the following PL/SQL block to restore the file:
DECLARE
v_devtype VARCHAR2(100);
v_done BOOLEAN;
v_maxPieces NUMBER;
TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
v_pieceName t_pieceName;
BEGIN
-- Define the backup pieces... (names from the RMAN Log file)
v_pieceName(1) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
v_pieceName(2) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
v_maxPieces := 2;
-- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
-- Restore the first Control File...
DBMS_BACKUP_RESTORE.restoreSetDataFile;
-- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
dbms_output.put_line('Start restoring '||v_maxPieces||' pieces.');
FOR i IN 1..v_maxPieces LOOP
dbms_output.put_line('Restoring from piece '||v_pieceName(i));
DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
exit when v_done;
END LOOP;
-- Deallocate the channel...
DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
WHEN OTHERS THEN
DBMS_BACKUP_RESTORE.deviceDeAllocate;
RAISE;
END;
/
Let's see if the controlfile was restored:
SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r----- 1 oracle dba 3096576 Aug 20 16:45 /oradata/orcl/control01.ctl[/code]
We should now be able to MOUNT the database and continue recovery...
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl
SQL> alter database mount;
SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
Database altered.
SQL> alter database open resetlogs;
Database altered.
How to perform and recover from a cold backup
By Nicolas de Fontenay 18 Comments
Categories: Maintenance
Back to basic.
The cold backup is the easiest backup that could be performed.
It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.
It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.
Perform a cold backup
A cold back requires a clean shutdown of the database.
shutdown immediate or normal are the only shutdown allowed.
A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.
The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).
When the clean shutdown is done, copy the oradata folders to a new location.
After the files are copied, the database can be started up.
Recover from a cold backup
Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.
1) If the service doesn’t exist yet, issue the oradim command:
oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword
If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.
2) Copy the oradata folders to the exact same location from primary to the server to recover.
If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.
3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.
In the case a data file has been moved to new location, the database must be started in mount mode:
C:\set oracle_sid= mySID
C:\sqlplus / as sysdba
SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)
and a rename command must be issued for each files relocated:
SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;
SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;
SQL>alter database open;
User Managed Backup --- Oracle
User Managed Backup
1. Physical Backup
1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)
2. Logical Backup
1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)
Now we see the physical backup.
Cold Backup: (Consistent Backup)
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.
Why you say cold backup is consistent backup?
After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.
Cold Backup Steps:
1. Get the file path information using below query
Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.
Hot Backup (Inconsistent backup)
A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
Why you say Hot backup is inconsistent backup?
While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.
Hot Backup Steps:
1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;
2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup
Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format
Below one is readable format. (Used for cloning)
Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;
6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)
Example:
steps:
2.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)
3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;
Hot Backup internal operations:
Note: While I am trying to write “during hot backup what are the internal operations going on?” I have referred several notes, but below one is very clear and nice one.
Reference: http://knol.google.com/k/franck-pachot/oracle-begin-backupend-backup/17uabcrki6uux/3#
During backup mode, for each datafile in the tablespace, here is what happens:
1- When BEGIN BACKUP is issued:
•The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for a recovery.
•A checkpoint is done for the tablespace, so that in case of recovery, no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.
2- During backup mode:
•The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup. Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs. This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
•Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behavior that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.
That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)
3- When END BACKUP is issued:
•A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
•The hot backup flag in the datafile headers is unset.
•The header SCN is written with the current one.
Remarks:
1.The fractured block is not frequent as it happens only if the i/o for the copy is done at the same time on the same block as the i/o for the update. But the only mean to avoid the problem is to do that full logging of block for each block, just in case.
2.If the OS I/O size is multiple of the Oracle block size (e.g backup done with dd bs=1M), that supplemental logging is not useful as fractured blocks cannot happen.
3.The begin backup checkpoint is mandatory to manage the fractured block issue: as Oracle writes the whole before image of the block, it needs to ensure that it does not overwrite a change done previously. With the checkpoint at the beginning, it is sure that no change vector preceding the begin backup has to be applied be applied.
4.The supplemental logging occurs when accessing the block for the first time in the buffer cache. If the same block is reloaded again in the buffer cache, supplemental logging will occur again. I haven't seen that point documented, but a test case doing a 'flush buffer_cache' proves that.
I Hope this article helped you to understand the user managed physical backup.Suggestions are welcome.
Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:
* Rate of data change/ transaction rate
* Database availability/ Can you shutdown for cold backups?
* Criticality of the data/ Value of the data to the company
* Read-only tablespace needs backing up just once right after you make it read-only
* If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
* If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
* Etc.
Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunication.
[edit] What strategies are available for backing-up an Oracle database?
The following methods are valid for backing-up an Oracle database:
* Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details.
* Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
* Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
* RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry.
Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files.
[edit] What is the difference between online and offline backups?
A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
[edit] What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
RMAN> run {
set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
restore database;
recover database;
}
[edit] My database is down and I cannot restore. What now?
This is probably not the appropriate time to be sarcastic, but, recovery without backups are not supported. You know that you should have tested your recovery strategy, and that you should always backup a corrupted database before attempting to restore/recover it.
Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.
Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!
DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database. More info about DUDE is available at http://www.ora600.nl/.
[edit] How does one backup a database using the export utility?
Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports. For more information about the Oracle export and import utilities, see the Import/ Export FAQ.
[edit] How does one put a database into ARCHIVELOG mode?
The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
Issue the following commands to put a database into ARCHIVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Alternatively, add the above commands into your database's startup command script, and bounce the database.
The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc
NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE 2:' ARCHIVELOG mode was introduced with Oracle 6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE 5: When running Oracle Real Application Clusters (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode. See the RAC FAQ for more details.
[edit] I've lost an archived/online REDO LOG file, can I get my DB back?
The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.
NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.
_allow_resetlogs_corruption = true
This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).
Steps:
* Do a "SHUTDOWN NORMAL" of the database
* Set the above parameter
* Do a "STARTUP MOUNT" and "ALTER DATABASE OPEN RESETLOGS;"
* If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the "ALTER DATABASE OPEN RESETLOGS;" command.
* Wait a couple of minutes for Oracle to sort itself out
* Do a "SHUTDOWN NORMAL"
* Remove the above parameter!
* Do a database "STARTUP" and check your ALERT.LOG file for errors.
* Extract the data and rebuild the entire database
[edit] User managed backup and recovery
This section deals with user managed, or non-RMAN backups.
[edit] How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
[edit] How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyzFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
[edit] My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the Scripts section of this site.
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
ALTER DATABASE END BACKUP;
This command must be issued when the database is mounted, but not yet opened.
[edit] Does Oracle write to data files in begin/hot backup mode?
When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files.
When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
To solve this problem, simply switch to RMAN backups.
[edit] RMAN backup and recovery
This section deals with RMAN backups:
[edit] What is RMAN and how does one use it?
Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recovering Oracle Databases. RMAN ships with the database server and doesn't require a separate installation. The RMAN executable is located in your ORACLE_HOME/bin directory.
In fact RMAN, is just a Pro*C application that translates commands to a PL/SQL interface. The PL/SQL calls are statically linked into the Oracle kernel, and does not require the database to be opened (mapped from the ?/rdbms/admin/recover.bsq file).
RMAN can do off-line and on-line database backups. It cannot, however, write directly to tape, but various 3rd-party tools (like Veritas, Omiback, etc) can integrate with RMAN to handle tape library management.
RMAN can be operated from Oracle Enterprise Manager, or from command line. Here are the command line arguments:
Argument Value Description
-----------------------------------------------------------------------------
target quoted-string connect-string for target database
catalog quoted-string connect-string for recovery catalog
nocatalog none if specified, then no recovery catalog
cmdfile quoted-string name of input command file
log quoted-string name of output message log file
trace quoted-string name of output debugging message log file
append none if specified, log is opened in append mode
debug optional-args activate debugging
msgno none show RMAN-nnnn prefix for all messages
send quoted-string send a command to the media manager
pipe string building block for pipe names
timeout integer number of seconds to wait for pipe input
-----------------------------------------------------------------------------
Here is an example:
[oracle@localhost oracle]$ rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target;
connected to target database: ORCL (DBID=1058957020)
RMAN> backup database;
...
[edit] How does one backup and restore a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database. RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/backup/%d_t%t_s%s_p%p'
(database);
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
[edit] How does one backup and restore archived log files?
One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup scripts:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/archback/log_%t_%sp%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
The "delete input" clause will delete the archived logs as they are backed-up.
List all archivelog backups for the past 24 hours:
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
Here is a restore example:
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }
[edit] How does one create a RMAN recovery catalog?
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
[edit] How does one integrate RMAN with third-party Media Managers?
The following Media Management Software Vendors have integrated their media management software with RMAN (Oracle Recovery Manager):
* Veritas NetBackup - http://www.veritas.com/
* EMC Data Manager (EDM) - http://www.emc.com/
* HP OMNIBack/ DataProtector - http://www.hp.com/
* IBM's Tivoli Storage Manager (formerly ADSM) - http://www.tivoli.com/storage/
* EMC Networker - http://www.emc.com/
* BrightStor ARCserve Backup - http://www.ca.com/us/data-loss-prevention.aspx
* Sterling Software's SAMS:Alexandria (formerly from Spectralogic) - http://www.sterling.com/sams/
* SUN's Solstice Backup - http://www.sun.com/software/whitepapers/backup-n-storage/
* CommVault Galaxy - http://www.commvault.com/
* etc...
The above Media Management Vendors will provide first line technical support (and installation guides) for their respective products.
A complete list of supported Media Management Vendors can be found at: http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htm
When allocating channels one can specify Media Management spesific parameters. Here are some examples:
Netbackup on Solaris:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';
Netbackup on Windows:
allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";
Omniback/ DataProtector on HP-UX:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';
or:
allocate channel 'dev_1' type 'sbt_tape' parms 'ENV=OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';
[edit] How does one clone/duplicate a database with RMAN?
The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.
Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.
Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":
connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /
run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';
allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;
duplicate target database to dupdb
logfile
GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}
The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.
Notes: the "set newname" commands are only required if your datafile names will different from the target database.
The newly cloned DB will have its own unique DBID.
[edit] Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?
Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database. In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken. Let's look at an example:
Let's take a backup (partial in our case for ilustrative purposes):
$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog
RMAN> backup datafile 1;
Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04[/code]
Now, let's destroy one of the control files:
SQL> show parameters CONTROL_FILES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata/orcl/control01.ctl,
/oradata/orcl/control02.ctl,
/oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl</pre>
Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:
SQL> startup NOMOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1301536 bytes
Variable Size 262677472 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes</pre>
Now, from SQL*Plus, run the following PL/SQL block to restore the file:
DECLARE
v_devtype VARCHAR2(100);
v_done BOOLEAN;
v_maxPieces NUMBER;
TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
v_pieceName t_pieceName;
BEGIN
-- Define the backup pieces... (names from the RMAN Log file)
v_pieceName(1) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
v_pieceName(2) :=
'/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
v_maxPieces := 2;
-- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
-- Restore the first Control File...
DBMS_BACKUP_RESTORE.restoreSetDataFile;
-- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
dbms_output.put_line('Start restoring '||v_maxPieces||' pieces.');
FOR i IN 1..v_maxPieces LOOP
dbms_output.put_line('Restoring from piece '||v_pieceName(i));
DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
exit when v_done;
END LOOP;
-- Deallocate the channel...
DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
WHEN OTHERS THEN
DBMS_BACKUP_RESTORE.deviceDeAllocate;
RAISE;
END;
/
Let's see if the controlfile was restored:
SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r----- 1 oracle dba 3096576 Aug 20 16:45 /oradata/orcl/control01.ctl[/code]
We should now be able to MOUNT the database and continue recovery...
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl
SQL> alter database mount;
SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
Database altered.
SQL> alter database open resetlogs;
Database altered.
How to perform and recover from a cold backup
By Nicolas de Fontenay 18 Comments
Categories: Maintenance
Back to basic.
The cold backup is the easiest backup that could be performed.
It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.
It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.
Perform a cold backup
A cold back requires a clean shutdown of the database.
shutdown immediate or normal are the only shutdown allowed.
A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.
The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).
When the clean shutdown is done, copy the oradata folders to a new location.
After the files are copied, the database can be started up.
Recover from a cold backup
Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.
1) If the service doesn’t exist yet, issue the oradim command:
oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword
If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.
2) Copy the oradata folders to the exact same location from primary to the server to recover.
If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.
3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.
In the case a data file has been moved to new location, the database must be started in mount mode:
C:\set oracle_sid= mySID
C:\sqlplus / as sysdba
SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)
and a rename command must be issued for each files relocated:
SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;
SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;
SQL>alter database open;
User Managed Backup --- Oracle
User Managed Backup
1. Physical Backup
1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)
2. Logical Backup
1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)
Now we see the physical backup.
Cold Backup: (Consistent Backup)
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.
Why you say cold backup is consistent backup?
After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.
Cold Backup Steps:
1. Get the file path information using below query
Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.
Hot Backup (Inconsistent backup)
A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
Why you say Hot backup is inconsistent backup?
While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.
Hot Backup Steps:
1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;
2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup
Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format
Below one is readable format. (Used for cloning)
Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;
6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)
Example:
steps:
2.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)
3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;
Hot Backup internal operations:
Note: While I am trying to write “during hot backup what are the internal operations going on?” I have referred several notes, but below one is very clear and nice one.
Reference: http://knol.google.com/k/franck-pachot/oracle-begin-backupend-backup/17uabcrki6uux/3#
During backup mode, for each datafile in the tablespace, here is what happens:
1- When BEGIN BACKUP is issued:
•The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for a recovery.
•A checkpoint is done for the tablespace, so that in case of recovery, no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.
2- During backup mode:
•The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup. Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs. This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
•Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behavior that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.
That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)
3- When END BACKUP is issued:
•A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
•The hot backup flag in the datafile headers is unset.
•The header SCN is written with the current one.
Remarks:
1.The fractured block is not frequent as it happens only if the i/o for the copy is done at the same time on the same block as the i/o for the update. But the only mean to avoid the problem is to do that full logging of block for each block, just in case.
2.If the OS I/O size is multiple of the Oracle block size (e.g backup done with dd bs=1M), that supplemental logging is not useful as fractured blocks cannot happen.
3.The begin backup checkpoint is mandatory to manage the fractured block issue: as Oracle writes the whole before image of the block, it needs to ensure that it does not overwrite a change done previously. With the checkpoint at the beginning, it is sure that no change vector preceding the begin backup has to be applied be applied.
4.The supplemental logging occurs when accessing the block for the first time in the buffer cache. If the same block is reloaded again in the buffer cache, supplemental logging will occur again. I haven't seen that point documented, but a test case doing a 'flush buffer_cache' proves that.
I Hope this article helped you to understand the user managed physical backup.Suggestions are welcome.
No comments:
Post a Comment