• Explains the differences between user-managed backup and recovery and server-managed backup and recovery • Perform user-managed database Full recovery • Perform user-managed database incomplete recovery
- Types of use for backup and recovery
Types of database backup and recovery include: • User-managed: Move files using OS commands without rman– –DBA requires manual maintenance of backup activity records • Server managed: There are two ways to recover a database using Rman. You can use Rman and take advantage of its automatic recovery capabilities. It can restore the appropriate files and use very few commands to restore the database to its current state. You can also perform a manual restore. This is called "User-managed recovery." User-managed restores require the use of OS commands to move files and then issue recovery commands in Sql*plus. Both of these methods use the restore and restore process.
- Perform user-managed database backups
You can back up a database by using an OS command to create a copy of the data file. The operation process depends on whether the database is in Archivelog mode. If so, you can leave the database open and available by placing each tablespace in Backup mode before copying the data files for the tablespace. Otherwise, you must close the database before copying the data file. Archive mode, manual hot backup script: http://blog.csdn.net/rlhua/article/details/11850629 non-archive mode, manual cold backup script: http://blog.csdn.net/rlhua/ article/details/11850445
- Where backup mode is required
If you write a block during the execution of a data manipulation language (DML) statement, multiple parts of the block may be affected. All modifications to a block do not occur at the same time, so there may be inconsistencies in the specific blocks. Suppose T2 represents the moment between the write time of different parts of a block. If a block is copied at T2 time during the execution of an OS copy command, the block is considered broken. In addition, the OS Copy command does not necessarily copy the file header first, so the file header must be frozen during replication. RMAN can try to handle this issue. If the block read is broken, the block is read repeatedly until the block is consistent. However, if an OS command (such as the Linux CP command) is replicating a data file, the broken block is not treated as a broken block and the copy of the block is inconsistent. To remedy this situation, put the tablespace or even the entire database into backup mode. The effect of this is to generate more redo. The image of each block is written to the redo log before it is modified. Then, during the recovery of blocks in the data file, you can use the front image of the shatter block as the recovery base and apply more redo data to the block. To reduce the overhead of maintaining additional redo data, Oracle recommends placing one tablespace at a time in backup mode while replicating its data files.
- Determine the files to be backed up manually
[Email protected]> select name from v$datafile; NAME----------------------------------------------------------------------------------------------------/u01/ app/oracle/oradata/test0924/system01.dbf/u01/app/oracle/oradata/test0924/sysaux01.dbf/u01/app/oracle/oradata/ test0924/fla_tbs02.dbf/u01/app/oracle/oradata/test0924/users01.dbf/u01/app/oracle/oradata/test0924/ example01.dbf/u01/app/oracle/oradata/test0924/fla_tbs01.dbf/u01/app/oracle/oradata/test0924/undotbs02.dbf/u01/ App/oracle/oradata/test0924/undotbs01.dbf/u01/app/oracle/oradata/test0924/inventory01.dbf 9 rows selected. [email protected]> select name from v$controlfile; NAME----------------------------------------------------------------------------------------------------/u01/ app/oracle/oradata/test0924/control01.ctl/u01/app/oracle/fast_recovery_area/test0924/control02.ctl A user-managed backup requires an understanding of the data file name and location on disk to know which files need to be replicated. Determine which data file to back up by querying the V$datafile view. Determine the control file location by querying the V$controlfile view. Only one multiplexed control text needs to be backed upPieces as they are the same.
- Manually backing up the Noarchivelog database
• Close the DB instance:sql> shutdownimmediate Copy the data file to the backup location: $ cp $ORACLE _base/orcl/datafile/*.dbf \>/u02/backup/datafile Copy control files to backup location: CP $ORACLE _base/orcl/controlfile/*.ctl \>/u02/backup/controlfile Launch instance and open database:sql> startup By shutting down the database and copying all the data files and control files to a backup directory, you can make a consistent overall database backup of the Noarchivelog database. Because the file copy operation is performed using the OS command (in this case, the Linux CP command), you must first close the database. This puts the database in a consistent state. This is the only option if the database is running in Noarchivelog mode. Otherwise, in Archivelog mode, you can perform inconsistent backups so that you can keep the database running when you perform a backup.
- Manually backing up the Archivelog database
• Determine tablespace and its data files:[email protected]> select File_name,tablespace_name from Dba_data_files; file_name , &NB Sp tablespace_name--------------------------------------------------------------------------------/ U01/APP/ORACLE/ORADATA/TEST0924/USERS01.DBF Users/u01/app/oracle/oradata/test0924/fla_ TBS02.DBF FLA_TBS2/U01/APP/ORACLE/ORADATA/TEST0924/SYSAUX01.DBF sysaux/u01/app/ ORACLE/ORADATA/TEST0924/SYSTEM01.DBF &NBSP;SYSTEM/U01/APP/ORACLE/ORADATA/TEST0924/EXAMPLE01.DBF EXAMPLE/U01/APP/ORACLE/ORADATA/TEST0924/FLA_TBS01.DBF fla_tbs1/u01/app/oracle/oradata/ TEST0924/UNDOTBS02.DBF UNDOTBS2/U01/APP/ORACLE/ORADATA/TEST0924/UNDOTBS01.DBF UNDOTBS1/ U01/APP/ORACLE/ORADATA/TEST0924/INVENTORY01.DBF inventory 9 rows selected. For eachTable spaces do the following: • Place tablespace in Backup mode:[email protected]> alter TABLESPACE users begin backup; tablespace altered. Copy the tablespace's data file to the backup location: $ cp /u01/app/oracle/oradata/test0924/users01.dbf /u02/backup/datafile make the table space exit Backup mode:[email protected]> alter TABLESPACE users end Backup; tablespace altered. If the database is in Archivelog mode, you may not have to close the database before copying the file. The result is an inconsistent backup, but the app will revert to a consistent state after the data has been re-made. Start Backup mode: Before copying a data file, you must put each data file in Backup mode. Use the BEGIN BACKUP clause of the ALTER TABLESPACE and ALTER DATABASE commands to perform this operation. The following is the syntax for each command: ALTER tablespace <tablespace> BEGIN BACKUP; ALTER DATABASE BEGIN BACKUP; The ALTER tablespace command affects only those data files that are part of the tablespace. The ALTER database affects all data files in the databases. End Backup mode: It is important to leave the data file out of the backup mode. When you close a database, you cannot have any data files in Backup mode. If you try to close a database that is in that state, you receive an error. In addition, because the backup mode causes additional redo data to be generated, it brings additional load to the system. If you do not actively back up your data files, there is no reason to keep any data files in Backup mode. Note: In addition, you need to archive the current redo log files and secure them back up.
The ways to back up control files include: • Create a file for the image copy, write to the specified name: • Generate a script to recreate the control file, write to the trace file:sql> ALTER DATABASE BACKUP controlfile to '/u01/backup/ Controlfile.bak ';D atabase altered. sql> ALTER DATABASE BACKUP controlfile to Trace;database altered. You should back up the control files every time that you make structural changes to the database. Use one of the commands shown in the example to perform this operation. The first command creates a binary copy of the file. If the backup file already exists and you want to overwrite the file, you can choose to provide the Reuse keyword. The second command creates a text-only version of the control file, which is actually a script that creates a control file after it is run. The generated script is written to the diagnostic trace directory, such as: $ORACLE _base/diag/rdbms/orcl/orcl/trace You can also use the as ' filename ' clause to specify the name of the trace file.
- Perform a user-managed database Full recovery: overview
User-managed Database Full recovery: • Restore the database to the latest SCN can process the entire database at one time, or one data file or tablespace at a time • Requires current control files or backup control files • Backup of all files that need to be recovered • All archive logs required so far A full database recovery restores the database to its latest state. You can restore the entire database at once, or you can restore a tablespace or data file at a time. You must have a current or backup control file in order to perform a full recovery on the database. You must also have a backup of all the files required for media recovery or all archive redo log files that have been generated since the data file was added to the database. You must have all available archive logs from the point in time that the backup was performed to the present. If you do not have all archived logs, you can only revert to the last point in time that the redo is available. If no archive log is required, only the online redo log is applied. The following views can be queried: v$recover_file: See which files require media recovery V$recovery_log: See which archive logs are required to perform recovery
- Perform a full restore of a closed database: overview
In some cases, when files that are part of the system tablespace are damaged, the instance is automatically closed. When something is wrong with some data files, you can decide that keeping the database running is meaningless, even if the instance remains running, and there are too many affected database objects. In this case, shut down the database to perform the recovery. If the database is still open, you can query the V$recovery_file view to see which data files need to be recovered and query V$recovery_log after the data file is restored to see which archive logs are required. This indicates which files (if any) you need to restore from the backup. Then close the database. Investigate the media failure and determine the cause of the problem. Fix the problem so that you can restore the files from the backup. For example, you may need to replace the disk drive. You can now use the Recover command to perform the recovery. Limit the recovery scope to the desired range, such as a data file or table space. You can restore the entire database if needed. Then open the database.
- Identify recovery-related files
• Identify data files that need to be recovered:[email protected]> SELECT file#, error from v$recover_file; file# ERROR--------------------------------------------------------------------------- 4 File not found determines the archive log file required to complete the recovery:[email protected]> SELECT archive_name from v$recovery_log; no rows selected If the database is still open, follow the steps below to query the file. Otherwise, try to launch the instance and mount the database to issue the query. To determine which data files need to be recovered, query the V$recover_file view. The error column indicates why the file needs to be recovered. If this column has any value other than offline normal, recovery is required. To see an overview of all affected data files and tablespaces, include V$datafile and v$tablespace in this query. Here is an example: SELECT r.file#, D.name df_name, T.name tbsp_name,d.status, R.error, r.change#, R.timefrom v$recover_file R, V$DAT Afile D, v$tablespace twhere t.ts# = D.ts# and d.file# = r.file#; [email protected]> l 1 SEL ECT r.file#, D.name df_name, T.name tbsp_name,d.status, R.error, r.change#, r.time 2 from V$RECOVER_FILE R, V$ DataFile D, V$tablespace t , WHERE t.ts# = d.ts# and d.file# = R.FILE#[EMAIL&NBSP;PRotected]>/ file# df_name &NBSP ; tbsp_name status error &nbs P change# time--------------------------------------------------- --------------------------------------------------------------------------- &NBSP;4/U01 /APP/ORACLE/ORADATA/TEST0924/USERS01.DBF USERS online file not FOUND   ; &NBSP;0 This can indicate the extent of the damage and help you determine the object of the recover command. The V$recovery_log view displays the archive log files required to perform the recovery. If the list shows that some files have been moved out of the default archive log location, you must restore them to a location before performing the recovery. After the results of these queries are logged, close the database.
- Restore files related to recovery
After you have determined which data files and archived log files are required, restore them to the appropriate disk location. Restore the data file by copying a data file from the backup location, as shown in the following example: [email protected] ~]$ cp/u01/app/oracle/oradata/test0924/users01old.dbf/u01/app/ ORACLE/ORADATA/TEST0924/USERS01.DBF If any archive log is required for recovery, check that the logs are still in the default disk location of the archive log. These logs may not be in the default location, for example, they have been moved to a tape or other disk drive. If the logs have been moved, you need to restore them to the default archive log location or to a temporary location. If there is enough free space in the default location (as specified by the log_archive_dest_1 initialization parameter), restore the log at that location. Otherwise, you can place the log on some other disk location. When you restore, you can specify the alternate location to locate the archive log file. If you must move the data file, you must record the fact in the control file. This is done by executing the alterdatabase RENAME file command, as shown in the following example:sql> ALTER DATABASE RENAME file '/u01/app/oracle/oradata/test0924/ Users01.dbf ' to '/newdisk/orcl/datafile/users01.dbf '; note: Before you execute the ALTER DATABASE RENAME file command, you must launch the instance and mount the database. If you have not already done so, mount the database and bring all the data files online. You can check the status of each data file by querying the V$datafile view. You can use the following command to bring the data file online:sql> ALTER DATABASE datafile '/newdisk/orcl/datafile/users01.dbf ' online;
The data file is now restored to a point in time past. The archive log file has also been restored to its default location or to a different location (for this recovery only). You are ready to perform a real recovery step, which means that the redo has been applied and the data file has been restored to the latest SCN. Use the Sql*plus recover command to perform this operation. If you do not specify the automatic option, you are prompted to specify each redo log file that you want to apply. This improves the control of the recovery process. Typically, automatic is used for full recovery. If the archived log file has been restored to a disk location other than the default location of the database, you must specify the FROM clause. Provide the directory where these files are stored, and the recovery process will look for files in that directory. Finally, open the database. This is fully restored.
- Perform a full recovery on an open database
If a media failure occurs while the database is open, the database will continue to run. When you try to write data to a data file, the data file is automatically taken offline. Querying these data files does not cause them to be taken offline, but it returns an error to the user who issued the query. Similar to recovering a closed database, you first need to query the files and archive logs that need to be recovered. Then, make all the table spaces that contain the corrupted data file offline. You can use the following command to complete the operation:sql> ALTER tablespace survey OFFLINE temporary; using the temporary option causes Oracle to perform checkpoint operations on all online data files that are part of the tablespace. Data files that have been inspected for checkpoints do not need to be recovered after they are brought back online because they are up to date with the latest SCN for any transaction that might affect them. Although the data file must be available while this command is running, this option is more consistent with your needs. The problem may be temporary, and you can bring the tablespace online without generating an error. Check the media to determine the cause of the problem. You can use the Dbverify utility to perform this operation. If the file is permanently damaged, restore and restore as described earlier in restoring the shutdown database. After the restore and restore steps are complete, you should bring all tablespaces back online.
- Perform incomplete recovery of user management: overview
You can restore a database to a point in time in the following situations: • You want the database to be in a state before a user error or administrative error occurs. • After you try to recover the block media, the database contains corrupted blocks. • A full recovery of the database cannot be performed because some redo log files are missing. • You want to create a test database that puts the database in the state of a time in the past. • Lost data file and one or more archived redo log files. Incomplete recovery is a recovery that does not restore the database to the most recent SCN that has been transacted. For some reason, just restore the database to a point in time, not now. The process of performing an incomplete recovery differs from full recovery processing, where the difference is primarily in the amount of redo being applied.
- Select Incomplete Recovery method
You can indicate when to stop applying the redo data by: • Specify the time to stop • Specify the stop SCN when you issue a cancel command when performing a recovery schedule incomplete recovery, determine which method you want to use to specify when to stop the application of the redo data. You can stop the recovery process by specifying one of the following: • Time: The log time that the recovery should stop. This is done automatically so that the recovery process does not prompt you to enter each file name. SCN: Restore the system change number that should stop at that point. This is done automatically so that the recovery process does not prompt you to enter each file name. Cancel: When you are prompted for the next redo log file name during the recovery process, specify the CANCEL keyword. This procedure cannot be performed automatically because you must specify CANCEL to terminate the recovery operation.
- Perform incomplete recovery of user management
• Restore the database to a point:sql> RECOVER database UNTIL time ' 2005-12-14:12:10:03 '; • Restore the database until the cancel operation is performed:sql> RECOVER databaseuntil cancel; Perform an incomplete restore using the following command: RECOVER [AUTOMATIC] DATABASE option below is the meaning of the option: automatic: Automatically apply archive and redo log files option:until time ' YYYY-MM-DD: HH24:MI:SS ' UNTIL canceluntil change <integer>using BACKUP controlfile incomplete recovery based on cancellation is very similar to a full restore of the shutdown database execution. The difference is in the way the recover command is executed; Specify the until Cancel clause. This clause causes the recovery process to prompt you to confirm the suggested name of each redo log file that you want to apply. Therefore, during the recovery process, you are prompted to confirm the file name of the redo log that was archived or online, and you can either accept it or change it for each file name. When you reach the point in time for the recovery to stop, enter cancel instead of the file name. This will stop the recovery. After you do this, you must open the database by using the Resetlogs option. The database is currently performing another instantiation operation, so the redo log sequence number needs to be reset. After you open the database, check the message in the alert log. In this way, you can learn whether the recovery was successful. Incomplete recovery based on time and change a time-and-change incomplete recovery is similar to a cancellation-based recovery, except that different criteria are used to specify the time to stop recovery. Time-based recovery uses the time specified in the command line of the recover command to learn when to stop. Change-based recovery uses the SCN specified on the command line. As with all incomplete recoveries, you must then use the Resetlogs option to open the database. Note: To automatically apply the Redo log file during the recovery process, you can use the Sql*plus SET autorecovery on command, enter auto at the recovery prompt, or use the Recover automatic command.
- Perform incomplete recovery of user management: Steps
To perform an incomplete recovery of user management, follow these steps: 1. Close the database. 2. Restore the data file. 3. Load the database. 4. Restore the database. 5. Open the database using the Resetlogs option. 1. If the database is open, turn it off using the normal, immediate, or transactional option. 2. Restore all data files from backup. You must use a backup that was created before the time that you intend to revert to. You may also need to restore the archive log. If there is enough free space, revert to the log_archive_dest location or use the Alter SYSTEM ARCHIVE LOG START to <LOCATION> command or set Logsource <location > command to change the location. If the database structure differs from the current database structure when performing an incomplete recovery to a point in time, you will also need to restore the control file. 3. Load the database. 4. Use the Recover database command to recover the databases. 5. To synchronize the data files with the control files and redo logs, open the database using the Resetlogs option.
- User-managed time-based recovery: example
The situation is as follows: • A job is running in error and its results and effects must be canceled. • The operation occurred 15 minutes ago, and there are few database activities after this. • You decide to perform an incomplete recovery and restore the database to a state that was 15 minutes ago. sql> shutdownimmediate$ cp/backup/*.dbf/u01/db01/oradatasql> STARTUP mountsql> RECOVER DATABASE UNTIL & Nbsp; time ' 2005-11-28:11:44:00 '; sql> ALTER DATABASE OPEN resetlogs; Below is a typical case of using until time recovery. Assumption: • The current time is 12:00 noon November 28, 2005. • A job run error that affects many of the tables in multiple scenarios. • This operation occurs approximately 11:45. • Database activity is rare because most employees are now in a meeting. You must revert to the state of the database before the job is run. Because you know the approximate time of the error and the structure of the database has not changed since 11:44, you can use the Until method: 1. If the database is open, use the normal, immediate, or transactional options to turn it off. 2. Restore all data files from the backup (if possible, using the latest). You may also need to restore the archive log. If there is enough free space, revert to the log_archive_dest location or use alter system archive LOG START to <LOCATION> command or set Logsource < The location> command to change the location. 3. Load the database. 4. Recover database:sql> recover db until time ' 2005-11-28:11:44:00 ' ora-00279:change 148448 ... 11/27/05 17:04:20 needed for thread Media recovery complete.5. To synchronize the data file with the control file and redo log, open the database using the RESETLOGS option:sql> ALTER DATABASE open resetlogs; Sql> Archive Log List ... Oldest onlINE log sequence 0Next log sequence to archive 1Current log sequence 1 when the recovery is completed successfully, the user database is notified that all data entered after the recovery time (11:44) needs to be re-entered.
- User-managed cancellation-based recovery: an example
The situation is the same as the time-based example except for the following: • Redo logs are not multiplexed. • An online redo log is missing. • The lost redo log is not archived. • The redo log contains information since 11:34. • Lost 26 minutes of data. • Users can manually re-enter their data. After searching for the redo log file in the directory, it is found that the redo log Log2a.rdo is missing and the log has not been archived. Therefore, you cannot revert to this point. After querying V$archived_log, determine the missing archive log sequence (Log2a.rdo):sql> SELECT * from V$archived_log; RECID STAMP ... first_change# first_time--------------...-----------------------1 318531466 ... 88330 05-11-15:12:4347 319512880 ... 309067 05-11-28:11:26 Follow these steps to restore the database: • Close the database. • Restore all data files from the most recent backup. • Load the database. • Execute recover DATABASE UNTIL CANCEL. • Execute the ALTER DATABASE open resetlogs to open the databases. Cancellation-based recovery is the same as a time-based recovery step, except for the recover database. When you execute the Recover database UNTIL Cancel command, it recovers the databases until the log file is not found. When you are prompted to enter the name of the missing archive redo log file, enter Cancel, and the recovery will stop at that point in time.
http://blog.csdn.net/rlhua/article/details/12682217
