Crosscheck oracle что это
// Резервное копирование и восстановление данных (Команды RMAN)
Лучше посмотреть материал, который находится здесь .
Там постепенно обновляется информация согласно появления новых знаний.
FRA - Fast Recovery Area - область на диске для бекапов и архивных журналов
RMAN - Recovery Manager (утилита для резервного копирования и восстановление данных)
В большинстве случаев, рекомендуется настроить работу базы данных в режиме работы ARCHIVELOG.
ARCHIVELOGи и BACKUPы хранятся в FRA, необходимо, чтобы база данных имела возможность записи данных в эту область, иначе возникнет ошибка и база перестанет принимать команды пользователей. Поэтому, по мере заполенения FRA, необходимо ее очищать (исключительно средствами RMAN).
Посмотреть данные FRA:
Restore - выполняется копирование файлов из резервной копии. Поврежденные файлы заменяются на рабочие из бекапа. (Чаще всего это несколько устаревшие данные)
Recover - процесс применения журналов к базе данных для наката ее вперед до определенного времени, точки восстановления и д.р.
EXPIRED (Истекшие) - это файлы, статус который в репозитории RMAN - EXPIRED. RMAN маркирует бекапы и копии данных как expired в случае, если при запуске CROSSCHECK будут найдены ссылки на отсутсвующие или недоступные файлы.
OBSOLETE (Устаревшие) - бекапы и копии данных в репоризотории RMAN, помеченные как obsolete. Они больше ненужны, их можно удалить средствами RMAN.
Если по-простому, чтобы иметь возможность восстановить работу базы данных в случае сбоя нужно знать следующие команды.
Создание резервной копии базы данных
// Восстановление базы данных
Все остальное - частные случаи .
Разумеется для баз данных, более критичных для организации, необходимо дополнительное планирование систем резервного копирования и восстановления после сбоев.
// Режим работы БД
// Основные представления:
select * from v$database; --
select * from v$recovery_file_dest; -- месторасположение FRA.
select * from v$flash_recovery_area_usage; -- использованный объем
select * from v$rman_backup_job_details; -- информация по бекапам
select * from v$rman_backup_subjob_details;
select * from v$rman_configuration; --
select * from v$rman_status;
select * from v$rman_backup_type;
select * from v$rman_configuration;
select * from v$archived_log;
select * from v$backup_corruption;
select * from v$copy_corruption;
select * from v$backup_files;
select * from v$backup_device;
select * from v$backup_set;
select * from v$backup_piece;
select * from v$backup_redolog;
select * from v$backup_spfile;
// Изменить расположение flash_recovery_area
SQL> alter system set db_recovery_file_dest="D:\_backups\_Oracle\DB_RECO VERY_FILE_DEST";
// Установить предельный размер flash_recovery_area
SQL> alter system set db_recovery_file_dest_size = 60G;
// Влючить flashback (если выключен)
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database flashback on;
SQL> alter database open;
// Влючить archivelog (если выключен)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> shutdown immediate;
SQL> startup;
RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;
// Работа с точками восстановления
// Создание точки восстановления
SQL> create restore point 'pointName';
// Создание точки восстановления с гарантией отката (при включенном flashback)
SQL> create restore point 'pointName' guarantee flashback database;
// Показать точки восстановления
SQL> select * from v$restore_point;
// Удалить точку восстановления
SQL> drop restore point 'pointName'
// Откатиться на точку восстановления (при включенном flashback)
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> flashback database to restore point 'pointName';
SQL> alter database open;
// Подключение к БД средствами RMAN
// На локальном компьютере с правами администратора операционной системы.
CMD> rman target /
// Настройка параметров резервного копирования средствами RMAN
// Показать все параметры
RMAN> show all;
// Команды для создания резервных копий
// Резервное копирование БД согласно параметров, которые можно посмотреть командой RMAN> show all;
RMAN> backup database;
// Создать backup базы данных и архивных журналов
RMAN> backup database plus archivelog
// Полный backup со сжатием и указанием файла
RMAN> bakup as compressed backupset database plus archivelog format 'c:\backupDB.bkp' ;
// backup as copy device
backup as copy device type disk database;
// backup as backupset
backup as backupset device type disk database;
backup as backupset format '\arc.bkp'' tablespace users, system, sysaus, undo;
// Резервное копирование ControlFile и SPFile
RMAN> backup current controlfile spfile;
SQL> alter database backup controlfile to trace as 'C:\trace.trc';
// Резервное копирование табличного пространства
RMAN> backup as copy format 'C:\oracle\product\10.2.0\oradata\orcl2\backup.bkp ' tablespace users ;
// Резервное копирование табличного пространства в FRA
RMAN> backup as copy tablespace users ;
// Резервное копирование datafile
RMAN> backup datafile 'C:\oracle\product\10.2.0\oradata\orcl2\undo01.dbf ';
// RMAN не делает бекап базы данных, но читает все блоки и проверяет их на поврежденность. Если находятся поврежденные блоки, то информация о них попадает в V$DATABASE_BLOCK_CORRUPTION
RMAN> BACKUP VALIDATE DATABASE;
RMAN> backup validate database archivelog all;
// Делаем инкрементальный бекап с уровнем 0
RMAN> backup incremental level 0 database;
// Делаем инкрементальный бекап с уровнем 1
RMAN> backup incremental level 1 database;
// Делаем кумулятивный (включает в себя измениния отраженные в инкрементальных бекапах ) бекап с уровнем 1
RMAN> backup incremental level 1 cumulative database;
Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.
Oracle Database Backup and Recovery User's Guide to learn how to manage database records in the recovery catalog
RMAN must be connected to a target database instance, which must be started.
A maintenance channel is not required for a disk cross-check. If you use a media manager and have not configured automatic channels for it, then you must use run ALLOCATE CHANNEL FOR MAINTENANCE before CROSSCHECK . For example, if you created a backup on an SBT channel, but have not configured automatic SBT channels for this device, then you must manually allocate an SBT channel before CROSSCHECK can check the backup. Furthermore, if you performed backups with different media manager options (pools, servers, libraries, and so on), then you must allocate maintenance channels for each combination.
CROSSCHECK validates all specified backups and copies, even if they were created in previous database incarnations.
RMAN always maintains metadata about backups in the control file of every target database on which it performs operations. If you use RMAN with a recovery catalog, then RMAN also maintains the metadata from every registered database in the recovery catalog.
If a backup is on disk, then CROSSCHECK determines whether the header of the file is valid. If a backup is on tape, then RMAN queries the RMAN repository for the names and locations of the backup pieces to be checked. RMAN sends this metadata to the target database server, which queries the media management software about the backups. The media management software then checks its media catalog and reports back to the server with the status of the backups.
EXPIRED and AVAILABLE Status
You can view the status of backup sets and copies recorded in the RMAN repository through LIST , V$ views, or recovery catalog views (if you use RMAN with a catalog). Table 2-4 describes the meaning of each status.
The CROSSCHECK command only processes files created on the same device type as the channels used for the cross-check. The CROSSCHECK command checks only objects marked AVAILABLE or EXPIRED in the repository by examining the files on disk for DISK channels or by querying the media manager for sbt channels.
Table 2-4 Meaning of CROSSCHECK Status
Object is not found either in file system (for DISK ) or in the media manager (for sbt ). A backup set is EXPIRED if any backup piece in the set is EXPIRED .
The CROSSCHECK command does not delete the repository records of files that it does not find, but updates their repository records to EXPIRED . You can run DELETE EXPIRED to remove the repository records for expired files and any existing physical files whose status is EXPIRED .
If backups are EXPIRED , then you can reexecute the cross-check later and determine whether expired backups are available. This precaution is especially useful when you use RMAN with a media manager. For example, if some backup pieces or copies were erroneously marked as EXPIRED because the PARMS channel settings were incorrect, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.
Object is available for use by RMAN. For a backup set to be AVAILABLE , all backup pieces in the set must have the status AVAILABLE .
Cross-Checks in a Data Guard Environment
"RMAN Backups in a Data Guard Environment" explains the difference between the association and accessibility of a backup. In a Data Guard environment, the database that creates a backup or copy is associated with the file. You can use maintenance commands such as CHANGE , DELETE , and CROSSCHECK for backups when connected to any database in the Data Guard environment if the backups are accessible. In general, RMAN considers tape backups created on any database as accessible to all databases in the environment, whereas disk backups are accessible only to the database that created them.
RMAN can only update the status of a backup from AVAILABLE to EXPIRED or DELETED when connected as TARGET to the database associated with the backup. If RMAN cannot delete a backup because it is not associated with the target database, then RMAN prompts you to perform the same CROSSCHECK operation for the file at the database with which it is associated. In this way RMAN protects against unwanted status changes that result from incorrect SBT configurations.
For example, assume that you connect RMAN as TARGET to standby database standby1 and back it up to tape. If the backup is manually removed from the tape, and if you perform a cross-check of the backup on standby2 , then RMAN prompts you to run the cross-check on standby1 . A cross-check on standby1 updates the status of the tape backup to EXPIRED when the media manager reports that the backup has been deleted.
Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.
Oracle Database Backup and Recovery User's Guide to learn how to manage database records in the recovery catalog
RMAN must be connected to a target database instance, which must be started.
A maintenance channel is not required for a disk cross-check. If you use a media manager and have not configured automatic channels for it, then you must use run ALLOCATE CHANNEL FOR MAINTENANCE before CROSSCHECK . For example, if you created a backup on an SBT channel, but have not configured automatic SBT channels for this device, then you must manually allocate an SBT channel before CROSSCHECK can check the backup. Furthermore, if you performed backups with different media manager options (pools, servers, libraries, and so on), then you must allocate maintenance channels for each combination.
CROSSCHECK validates all specified backups and copies, even if they were created in previous database incarnations.
For preplugin backups, the CDB must be open in read-write mode and you must connect as a common user with the SYSDBA or SYSBACKUP privilege.
RMAN always maintains metadata about backups in the control file of every target database on which it performs operations. If you use RMAN with a recovery catalog, then RMAN also maintains the metadata from every registered database in the recovery catalog.
If a backup is on disk, then CROSSCHECK determines whether the header of the file is valid. If a backup is on tape, then RMAN queries the RMAN repository for the names and locations of the backup pieces to be checked. RMAN sends this metadata to the target database server, which queries the media management software about the backups. The media management software then checks its media catalog and reports back to the server with the status of the backups.
EXPIRED and AVAILABLE Status
You can view the status of backup sets and copies recorded in the RMAN repository through LIST , V$ views, or recovery catalog views (if you use RMAN with a catalog). Table 2-4 describes the meaning of each status.
The CROSSCHECK command only processes files created on the same device type as the channels used for the cross-check. The CROSSCHECK command checks only objects marked AVAILABLE or EXPIRED in the repository by examining the files on disk for DISK channels or by querying the media manager for sbt channels.
Table 2-4 Meaning of CROSSCHECK Status
Object is not found either in file system (for DISK ) or in the media manager (for sbt ). A backup set is EXPIRED if any backup piece in the set is EXPIRED .
The CROSSCHECK command does not delete the repository records of files that it does not find, but updates their repository records to EXPIRED . You can run DELETE EXPIRED to remove the repository records for expired files and any existing physical files whose status is EXPIRED .
If backups are EXPIRED , then you can reexecute the cross-check later and determine whether expired backups are available. This precaution is especially useful when you use RMAN with a media manager. For example, if some backup pieces or copies were erroneously marked as EXPIRED because the PARMS channel settings were incorrect, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.
Object is available for use by RMAN. For a backup set to be AVAILABLE , all backup pieces in the set must have the status AVAILABLE .
Cross-Checks in a Data Guard Environment
"RMAN Backups in a Data Guard Environment" explains the difference between the association and accessibility of a backup. In a Data Guard environment, the database that creates a backup or copy is associated with the file. You can use maintenance commands such as CHANGE , DELETE , and CROSSCHECK for backups when connected to any database in the Data Guard environment if the backups are accessible. In general, RMAN considers tape backups created on any database as accessible to all databases in the environment, whereas disk backups are accessible only to the database that created them.
RMAN can only update the status of a backup from AVAILABLE to EXPIRED or DELETED when connected as TARGET to the database associated with the backup. If RMAN cannot delete a backup because it is not associated with the target database, then RMAN prompts you to perform the same CROSSCHECK operation for the file at the database with which it is associated. In this way RMAN protects against unwanted status changes that result from incorrect SBT configurations.
For example, assume that you connect RMAN as TARGET to standby database standby1 and back it up to tape. If the backup is manually removed from the tape, and if you perform a cross-check of the backup on standby2 , then RMAN prompts you to run the cross-check on standby1 . A cross-check on standby1 updates the status of the tape backup to EXPIRED when the media manager reports that the backup has been deleted.
Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.
Oracle Database Backup and Recovery User's Guide to learn how to manage database records in the recovery catalog
RMAN must be connected to a target database instance, which must be started.
A maintenance channel is not required for a disk cross-check. If you use a media manager and have not configured automatic channels for it, then you must use run ALLOCATE CHANNEL FOR MAINTENANCE before CROSSCHECK . For example, if you created a backup on an SBT channel, but have not configured automatic SBT channels for this device, then you must manually allocate an SBT channel before CROSSCHECK can check the backup. Furthermore, if you performed backups with different media manager options (pools, servers, libraries, and so on), then you must allocate maintenance channels for each combination.
CROSSCHECK validates all specified backups and copies, even if they were created in previous database incarnations.
RMAN always maintains metadata about backups in the control file of every target database on which it performs operations. If you use RMAN with a recovery catalog, then RMAN also maintains the metadata from every registered database in the recovery catalog.
If a backup is on disk, then CROSSCHECK determines whether the header of the file is valid. If a backup is on tape, then RMAN queries the RMAN repository for the names and locations of the backup pieces to be checked. RMAN sends this metadata to the target database server, which queries the media management software about the backups. The media management software then checks its media catalog and reports back to the server with the status of the backups.
EXPIRED and AVAILABLE Status
You can view the status of backup sets and copies recorded in the RMAN repository through LIST , V$ views, or recovery catalog views (if you use RMAN with a catalog). Table 2-4 describes the meaning of each status.
The CROSSCHECK command only processes files created on the same device type as the channels used for the cross-check. The CROSSCHECK command checks only objects marked AVAILABLE or EXPIRED in the repository by examining the files on disk for DISK channels or by querying the media manager for sbt channels.
Table 2-4 Meaning of CROSSCHECK Status
Object is not found either in file system (for DISK ) or in the media manager (for sbt ). A backup set is EXPIRED if any backup piece in the set is EXPIRED .
The CROSSCHECK command does not delete the repository records of files that it does not find, but updates their repository records to EXPIRED . You can run DELETE EXPIRED to remove the repository records for expired files and any existing physical files whose status is EXPIRED .
If backups are EXPIRED , then you can reexecute the cross-check later and determine whether expired backups are available. This precaution is especially useful when you use RMAN with a media manager. For example, if some backup pieces or copies were erroneously marked as EXPIRED because the PARMS channel settings were incorrect, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.
Object is available for use by RMAN. For a backup set to be AVAILABLE , all backup pieces in the set must have the status AVAILABLE .
Cross-Checks in a Data Guard Environment
"RMAN Backups in a Data Guard Environment" explains the difference between the association and accessibility of a backup. In a Data Guard environment, the database that creates a backup or copy is associated with the file. You can use maintenance commands such as CHANGE , DELETE , and CROSSCHECK for backups when connected to any database in the Data Guard environment if the backups are accessible. In general, RMAN considers tape backups created on any database as accessible to all databases in the environment, whereas disk backups are accessible only to the database that created them.
RMAN can only update the status of a backup from AVAILABLE to EXPIRED or DELETED when connected as TARGET to the database associated with the backup. If RMAN cannot delete a backup because it is not associated with the target database, then RMAN prompts you to perform the same CROSSCHECK operation for the file at the database with which it is associated. In this way RMAN protects against unwanted status changes that result from incorrect SBT configurations.
For example, assume that you connect RMAN as TARGET to standby database standby1 and back it up to tape. If the backup is manually removed from the tape, and if you perform a cross-check of the backup on standby2 , then RMAN prompts you to run the cross-check on standby1 . A cross-check on standby1 updates the status of the tape backup to EXPIRED when the media manager reports that the backup has been deleted.
Links to Download Oracle 12c / 11g for Linux, Windows etc.
Learn Oracle 11g / 12c Database Admin step by step
Most asked Oracle DBA Interview Questions.
Tutorial for Oracle SQL
Learn MySQL step by step
Whatever backups you take using RMAN, it store all the backup information in the database Control File and also in the Recovery Catalog if you are using RMAN with Catalog.
To maintain the information in up-to-date state we might need the following commands
Crosscheck Command in RMAN
Crosscheck is a very useful command in RMAN which automatically checks all the backups whose information is store in the RMAN repository and whether they are available on the disk or not.
If a corresponding backup is not available on the disk then Crosscheck command marks the backup as Expired otherwise it marks it as Available
To check status of all backupsets we can give the following command
RMAN> crosscheck backup;
To check status of all image copies we can give the following command
RMAN> crosscheck copy;
To know the status of Expired and Available backupsets give the following command
RMAN> list expired backup;
To know the status unavailable image copy copy give the following command
To register manual backups
Suppose we have taken a backup of a particular datafile outside of RMAN through O/s commands. Whatever backup you take outside of RMAN, RMAN will not know about it and it will not consider this backup exist until we register the backup in RMAN catalog.
For example suppose we have taken a backup of users01.dbf datafile on /u02/oracle/d2/users01.dbf' directory and now we want to register this backup in RMAN repository
To register give the following command
RMAN> catalog datafilecopy '/u02/oracle/d2/users01.dbf';
Once you issue the above command then RMAN will enter the information about this backup in the catalog and will recover from it in future recovery operation if needed.
60 Technical Questions
42 Backup & Recovery Questions
Читайте также: