SEC3
2017-02-07 23:46 ᵬyŧểᵬyŧȅ 阅读(674) 评论(0) 编辑 收藏 举报301 - Maintain Recovery Catalogs
1. 文档
l Backup and Recovery User's Guide - > 13 Managing a Recovery Catalog
2. 数据库归档设置
Check the DB PROD1 configuration in host ODD.
-- Check the DB is in ARCHIVELOG mode
archive log list;
-- Check the DB is in archivelog mode and enable FLASHBACK
select flashback_on from v$database;
show parameter recovery_file
alter database flashback on;
-- Enable BLOCK CHANGE TRACKING for Incremental Backups
-- By default, create it in the path specified by parameter DB_CREATE_FILE_DEST
alter database enable block change tracking
using file '/u01/app/oracle/oradata/PROD1/block_change_tracking.f' reuse;
-- Check the result
col filename format a60
select * from v$block_change_tracking;
Add the service entry in file TNSNAMES.ORA of host ODD.
vi $ORACLE_HOME/network/admin/tnsnames.ora
-- Add the entry of DB EMREP
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = EMREP.oracle.com)
)
)
第 1 页 共 4 页
ORACLE 11g OCM
3. 建立 RMAN Catalog
Backup without Catalog Backup with Catalog
Create the Recovery Catalog on the DB EMREP of host EVEN.
-- We create the TABLESPACE that is required to host the RECOVERY CATALOG
create tablespace rcat datafile '/u01/app/oracle/oradata/EMREP/rcat.dbf' size 100m
autoextend on next 1m maxsize 1g extent management local uniform size 1m;
-- Create the Recovery Catalog owner user with the necessary permissions
create user rman identified by rman default tablespace rcat temporary tablespace temp;
alter user rman quota unlimited on rcat;
grant recovery_catalog_owner to rman;
-- Connect to RMAN catalog to create a catalog on host EVEN
rman catalog rman/rman@emrep
RMAN> create catalog;
4. 注册数据库
Register PROD1 DB in the Recovery Catalog on EMREP DB.
-- Edit the file TNSNAMES.ORA
vi $ORACLE_HOME/network/admin/tnsnames.ora
-- Add the following entries to the tnsnames.ora file of EMREP DB
PROD1 =
(DESCRIPTION =
第 2 页 共 4 页
ORACLE 11g OCM
(ADDRESS = (PROTOCOL = TCP)(HOST = odd.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1.oracle.com)
)
)
-- Register the PROD1 DB to the catalog, the PROD1 DB must be mounted or opened first.
-- 注意：target 登入只能用 sys 用户
rman target sys/oracle@prod1 catalog rman/rman@emrep
-- 如果是再次重新注册 PROD1 DB，要先清除原注册的 DB 数据后才能注册
-- unregister database;
RMAN> register database;
5. 维护 RMAN Catalog
Maintaining a Recovery Catalog
-- Perform a full resynchronization：Create a snapshot control file and copy
-- any updated info from that snapshot control file to the recovery catalog.
RMAN> resync catalog;
-- Upgrading the recovery catalog
RMAN> upgrade catalog;
-- Import a recovery catalog.
-- For example: import the catalog owned by 102cat on database srcdb
RMAN> import catalog 102cat@srcdb;
-- Drop a recovery catalog
RMAN> drop catalog;
6. Catalog 备份文件
CATALOG 命令: 把备份文件（文件拷贝或用户手工管理的备份）纳入到 RMAN Catalog 中。
-- Create the backup directory as user oracle on ODD host
mkdir /tmp/backup
-- Perform rman on host EVEN or host ODD
rman target sys@prod1 catalog rman/rman@emrep
RMAN> backup datafile '/u01/app/oracle/oradata/PROD1/users01.dbf' format '/tmp/backup/test.bkp';
RMAN> catalog backuppiece '/tmp/backup/test.bkp';
第 3 页 共 4 页
ORACLE 11g OCM
-- 注意：上述命令中 target 是 PROD1，备份文件夹'/tmp/backup/test.bkp' 在 host ODD 上，不是在 host EVEN
Catalog all files in a disk location on host ODD with CATALOG START WITH.
RMAN scans all paths for all files on disk that begin with the specified prefix. The prefix is
not just a directory name. Using the wrong prefix can cause the cataloging of the wrong files.
RMAN> catalog start with '/tmp/backup/'; # catalog all files "/tmp/backup/*"
RMAN> catalog start with '/tmp/backup'; # catalog all files "/tmp/backup*" ,"/tmp/backup/*"
# 注意上述2者的区别。
Catalog all objects in the Fast Recovery Area on host ODD.
RMAN> catalog recovery area;
第 4 页 共 4 页
ORACLE 11g OCM
302 - Configure Recovery Manager
1. 文档
l Backup and Recovery User's Guide - > 5 Configuring the RMAN Environment
2. RMAN 默认设置
-- List the default values of the rman parameters
rman target sys@prod1 catalog rman/rman@emrep
RMAN> SHOW ALL;
RMAN configuration parameters for database with db_unique_name PROD1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f'; # default
RMAN 备份策略参考：
1) 数据库启用归档模式
2) 设置 RMAN 的 REDUNDANCY > 1.
3) 备份数据库到 2 个以上的不同介质（例如：磁盘、磁带）
4) 定时进行备份：BACKUP DATABASE PLUS ARCHIVELOG
5) 不要重复使用多个 RMAN Catalog 管理备份数据
6) 设置 control file autobackup feature = ON
第 1 页 共 3 页
ORACLE 11g OCM
3. 设置 AUTOBACKUP
In this mode, RMAN will backup the CONTROLFILE and the SPFILE whenever you perform a backup.
-- Enable AUTOBACKUP
configure controlfile autobackup on;
l CONTROLFILE 和 SPFILE 的 autobackup 文件的默认存储位置在：
<fast_recovery_area>/PROD1/autobackup/<date>/*
l 可以用以下的方法把 autobackup 文件存放到另外的位置：
-- Change the default path of AUTOBACKUP
-- %F: only for controlfile. Combines the DBID, day, month, year, and sequence into a unique
-- file name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ.
configure controlfile autobackup format for device type disk to '/tmp/%F';
-- Check the configure result
show all;
backup as compressed backupset tablespace uesrs;
-- Reset the AUTOBACKUP path back to the FRA
configure controlfile autobackup format for device type disk clear;
show all; -- Check the configure result
4. 设置 RETENTION POLICY.
By default, it is set to redundancy of 1 copy.
-- Increase to 2 copies of redundancy
configure retention policy to redundancy 2;
-- Specify no backup is marked as obsolete.
configure retention policy to none;
-- Do not confuse with CLEAR, which will set the default value of REDUNDANCY 1
configure retention policy clear;
-- Change the retention policy setting backups 7 days
-- 注意：This parameter must less than CONTROL_FILE_RECORD_KEEP_TIME
configure retention policy to recovery window of 7 days;
第 2 页 共 3 页
ORACLE 11g OCM
5. 启用 Backup OPTIMIZATION
对在存储设备上已有备份、且未有更新的数据不做备份
(包括：offline-normal, read-only 或 closed normally)
configure backup optimization on;
show all; -- Check the configure result
-- 强制备份全部数据
BACKUP DATABASE FORCE;
BACKUP ARCHIVELOG ALL FORCE;
6. 配置 Parallel Channels
-- Specify two channels default for disk backups
configure device type disk parallelism 2 backup type to backupset;
-- configure device type disk clear; -- 恢复默认设置
show all; -- Check the configure result
-- Make a backup to check the parallel 2 channels
backup datafile 4,5;
-- Delete the backups
delete backup of datafile 4,5;
第 3 页 共 3 页
ORACLE 11g OCM
303 - Use Recovery Manager to Perform
Database Backups
1. 文档
l Backup and Recovery User's Guide - > 9 Backing Up the Database
2. 备份数据库
-- Using the prod1 recovery catalog
rman target sys@prod1 catalog rman/rman@emrep
-- Backup the DB + archivelogs
backup database plus archivelog;
默认 backup 数据存储在 FRA.
-- Check the FRA.
select * from v$recovery_area_usage;
手工删除备份
-- Deletes all Backupsets
delete backup;
-- Delete the COPY backups
delete copy of database;
3. 压缩备份
（占用 CPU 资源，更少的存储空间）
-- Compressed Backup DB + archivelogs
backup as compressed backupset database plus archivelog;
4. 备份各类数据
Backup Database, Tablespace, Datafile, ...
-- Full backup of the database AS COPY
-- Default BACKUP is AS BACKUPSET, unless we change the configuration of RMAN
第 1 页 共 5 页
ORACLE 11g OCM
-- The advantage of this type of backups is that the RESTORE operation is immediate
backup as copy database plus archivelog;
-- Backup one or more DATAFILES
backup as copy datafile 1,2;
-- Backup datafile specifying a location
backup as copy datafile 1 format '/u01/app/oracle/fast_recovery_area/PROD1/system01.dbf';
-- Using custom formats for backups
backup archivelog from sequence=1
format '/u01/app/oracle/fast_recovery_area/PROD1/ar_%t_%s_%p' delete input;
-- %t: Specifies the backup set time stamp, which is a 4-byte value
-- %s: Specifies the backup set number.
-- %p: Specifies the piece number within the backup set.
-- Backup one or more TABLESPACES
backup tablespace users;
5. 增量备份 (Incremental Backup)
首次全备份 (level 0)，后续增量备份 (level 1)：
l DIFFERENTIAL Incremental Backup (差异增量备份)
仅备份上次备份（包括 level 0 或 level 1）后的更新数据。这是增量备份的默认模式。
第 2 页 共 5 页
ORACLE 11g OCM
l CUMULATIVE Incremental Backup (累积增量备份)
备份上次 level 0 备份后所有累积的更新数据，即：上次 level 0备份后的所有 level 1 备份的内
容也将重复备份。
-- Level 0 Backup
BACKUP INCREMENTAL LEVEL 0 DATABASE;
-- Differential incremental backup （默认增量备份模式）
BACKUP INCREMENTAL LEVEL 1 DATABASE;
-- Cumulative incremental backup
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
-- 例1: DIFFERENTIAL backup 节省备份数量和备份时间
vi /home/oracle/backup_inc.rmn
-- Add the following lines
RUN {
RECOVER COPY OF DATABASE WITH TAG 'DAILY_BKP_POLICY';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'DAILY_BKP_POLICY'
DATABASE PLUS ARCHIVELOG;
}
RMAN> @ /home/oracle/backup_inc.rmn
-- 例2: CUMULATIVE backup 实现最快的恢复时间
vi /home/oracle/backup_cum.rmn
-- Add the following lines
RUN {
RECOVER COPY OF DATABASE WITH TAG 'DAILY_BKP_POLICY';
第 3 页 共 5 页
ORACLE 11g OCM
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'DAILY_BKP_POLICY' CUMULATIVE
DATABASE PLUS ARCHIVELOG;
}
RMAN> @ /home/oracle/backup_cum.rmn
6. 常用的 RMAN 命令
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
BACKUP VALIDATE DATABASE; -- Physical corruptions (查：V$DATABASE_BLOCK_CORRUPTION)
BACKUP CHECK LOGICAL DATABASE; -- Logical Corruptions (查：Alert and session Trace File)
REPORT OBSOLETE;
REPORT UNRECOVERABLE;
REPORT NEED BACKUP;
REPORT NEED BACKUP INCREMENTAL 2; -- Report Datafiles that need more than 2 INC backups
REPORT NEED BACKUP REDUNDANCY 2;
REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;
REPORT SCHEMA;
REPORT SCHEMA AT TIME 'SYSDATE-7';
VALIDATE DATABASE PLUS ARCHIVELOG;
VALIDATE ARCHIVELOG ALL;
VALIDATE CHECK LOGICAL TABLESPACE USERS;
VALIDATE CHECK LOGICAL DATAFILE 4;
VALIDATE CHECK LOGICAL DATAFILE 4 BLOCK 1 TO 10;
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;
VALIDATE CHECK LOGICAL BACKUPSET 6446;
RESTORE DATABASE VALIDATE;
RESTORE DATABASE PREVIEW;
RECOVER DATABASE PREVIEW;
LIST BACKUP OF DATABASE;
LIST BACKUP OF ARCHIVELOG FROM SCN 2953216;
LIST BACKUPPIECE '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2015_09_07/<...>.bkp';
LIST BACKUPPIECE 8222;
LIST BACKUPSET 8220;
LIST COPY OF TABLESPACE SYSTEM;
LIST COPY OF DATABASE ARCHIVELOG FROM TIME='SYSDATE-7';
LIST INCARNATION;
第 4 页 共 5 页
ORACLE 11g OCM
LIST EXPIRED BACKUP;
LIST BACKUP SUMMARY; -- TY = B/P (Backupset/Proxy copy)
-- LV = [01]/F/A (Incremental level X/Datafiles/Archivelogs)
-- S = A/U/X (Available/Unavailable/Expired)
DELETE OBSOLETE; -- 删除过期备份数据
7. 备份相关的视图 Views
· V$ARCHIVED_LOG
· V$COPY_CORRUPTION
· V$DATABASE_BLOCK_CORRUPTION
· V$RMAN_CONFIGURATION
· V$BACKUP_<CORRUPTION|SET|SPFILE|DATAFILE|DEVICE|FILES|PIECE|REDOLOG|SYNC_IO|ASYNC_IO>
· V$PROXY_<ARCHIVEDLOG|DATAFILE>
第 5 页 共 5 页
ORACLE 11g OCM
304 - Use Recover Manager to Perform
Complete Database Restore and Recovery
Operations
1. 文档
l Backup and Recovery User's Guide -> 17 Performing Complete Database Recovery
l Backup and Recovery User's Guide -> 30 Performing User-Managed Recovery: Advanced Scenarios
2. 数据库错误场景
1) Loss of system DATAFILE
2) Loss of non-system DATAFILE
3) Loss of a DATAFILE without Backup
4) Loss of a CONTROLFILE
5) Loss of all the CONTROLFILE
6) Loss of REDOLOGs
7) Loss of CONTROLFILE, SPFILE, DATAFILEs and REDOLOGs
8) Loss of Tempfile
9) Block corruption of datafiles
3. 丢失系统表空间数据文件
Loss of the DATAFILE 1 of SYSTEM tablespace.
-- First of all, check the backup is ok.
RMAN> show all;
RMAN> list backup summary;
RMAN> report need backup;
模拟出错及检查：
-- Delete the DATAFILE 1
rm /u01/app/oracle/oradata/PROD1/system01.dbf
-- 检查数据库当前状态，应该提示 datafile 1 出错
rman target sys@prod1 catalog rman/rman@emrep
RMAN> validate database;
第 1 页 共 10 页
ORACLE 11g OCM
-- Note: The online redo log files and temp files are not validated by RMAN.
数据恢复：
-- Shutdown the DB instance
RMAN> SHUTDOWN ABORT
RMAN> STARTUP MOUNT
-- Restore and recover the DATAFILE
RMAN> RESTORE DATAFILE 1;
RMAN> RECOVER DATAFILE 1;
RMAN> ALTER DATABASE OPEN;
RMAN> validate database; -- Check the DB ok
4. 丢失非系统数据文件
Loss of a non-system DATAFILE
-- Delete the TABLESPACE USERS DATAFILE
rm /u01/app/oracle/oradata/PROD1/users01.dbf
-- Force the failure by trying to write to the TBS
SQL> create table test (c1 number) tablespace users;
-- 检查数据库当前状态，应该提示 datafile 5 出错
rman target sys@prod1 catalog rman/rman@emrep
RMAN> validate database;
数据恢复：
-- List the problem in DB
RMAN> LIST FAILURE;
-- Analyze the error and generate script to repair it
RMAN> ADVISE FAILURE;
-- Preview the contents of repairing script
RMAN> REPAIR FAILURE PREVIEW;
-- Run the script
RMAN> REPAIR FAILURE;
RMAN> validate database; -- Check the DB ok
5. 丢失无备份的数据文件
To recover from this case, we must have all the ARCHIVELOG since DATAFILE was created, and
also the name of the DATAFILE must be included in the CONTROLFILE.
-- Create a TABLESPACE test with a sample table
create tablespace test datafile '/u01/app/oracle/oradata/PROD1/test01.dbf' size 50m;
第 2 页 共 10 页
ORACLE 11g OCM
create table test_table tablespace test as select * from hr.employees;
select count(*) from test_table;
-- Delete the DATAFILE
rm /u01/app/oracle/oradata/PROD1/test01.dbf
数据恢复：
-- Check the missing datafile number
select file#,name from v$datafile;
-- Offline the DATAFILE
ALTER DATABASE DATAFILE 6 OFFLINE;
-- Create a DATAFILE with the same characteristics as the lost DATAFILE
ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/oradata/PROD1/test01.dbf';
-- Recover the DATAFILE
RECOVER DATAFILE 6;
ALTER DATABASE DATAFILE 6 ONLINE;
-- Check the result
SELECT COUNT(*) FROM TEST_TABLE;
-- Clean the test environment
DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;
6. 丢失部分控制文件
-- Delete a CONTROLFILE
rm /u01/app/oracle/oradata/PROD1/control01.ctl
-- 检查数据库当前状态，rman 登入即会报 control file 出错，需要在 shutdown 后才能登入。
rman target sys@prod1 catalog rman/rman@emrep
数据恢复：
-- Close the database (if it is not crashed)
SQL> SHUTDOWN ABORT
-- Copy the normal CONTROLFILE to the lost location
cp -p /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/oradata/PROD1/control01.ctl
-- Restart the DB
SQL> STARTUP
第 3 页 共 10 页
ORACLE 11g OCM
7. 丢失全部控制文件
-- 注意：练习前一定要先做好备份：
SQL> alter database flashback on;
SQL> select log_mode,flashback_on from v$database;
SQL> alter database enable block change tracking using file
'/u01/app/oracle/oradata/PROD1/block_change_tracking.f' reuse;
SQL> select * from v$block_change_tracking;
RMAN> register database;
RMAN> configure controlfile autobackup on;
RMAN> configure backup optimization on;
RMAN> backup as compressed backupset database plus archivelog delete all input;
RMAN> show all;
RMAN> report need backup;
RMAN> list backup summary;
-- Delete all the 2 CONTROLFILEs in the DB PROD1
rm /u01/app/oracle/oradata/PROD1/control01.ctl
rm /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
-- 检查数据库当前状态，rman 登入即报 control file 出错，需要在 shutdown 后才能登入。
rman target sys@prod1 catalog rman/rman@emrep
-- Close the database (if it is not crashed)
SHUTDOWN ABORT
数据恢复：
-- Run the following script from RMAN
sqlplus rman/rman@emrep
SQL> select * from rc_database; --获取该 DB 的 dbid（例如：1583199105）
rman target / catalog rman/rman@emrep
RMAN> set dbid = 1583199105;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> recover database;
RMAN> sql 'alter database open resetlogs';
new incarnation of database registered in recovery catalog
RMAN> list incarnation;
-- 注意：以下检查并不提示需要重新备份 DB，所以必须立即备份(VERY IMPORTANT)
RMAN> report need backup;
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
第 4 页 共 10 页
ORACLE 11g OCM
8. 丢失全部日志文件
注意：删除全部 redo logfiles 不会即时报错，只有切换到最后一个未归档的 logfile 时才会出现无
响应死机。查看 alert_xxx.log 有错误记录。
-- Check the location of redo logfiles
select member from v$logfile;
-- delete all the redo logfiles with OS cmd.
alter system checkpoint;
alter system switch logfile;
......
alter system switch logfile;
-- Ctrl+C 中断执行
shutdown immediate;
startup
-- 此时启动将出错: ORA_03113: end-of-file on communication channel ...
数据恢复：
-- 如果数据库是正常关闭，日志文件中没有未决的事务需要实例恢复。
-- 丢失 redo logfiles 后，可用以下方法重建日志文件。
sqlplus / as sysdba
startup mount;
-- Clear all the redo logfiles
alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 2;
alter database clear unarchived logfile gorup 3;
alter database open;
-- Make a BACKUP at once (VERY IMPORTANT)
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
-- 如果丢失的日志文件组中有活动的事务，上述恢复方法将可能失效。
-- 解决方法1. 使用备份数据恢复数据库，可参考下述的不完全恢复方法。
-- 解决方法2. 通过使用隐含参数设置，强制性恢复开启数据库，但是有可能导致数据库不一致。
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
9. 丢失全部数据库文件
Loss of all CONTROLFILE, SPFILE, DATAFILES and REDOLOGS.
-- Check the location of data files
select file_name from dba_data_files;
select file_name from dba_temp_files;
第 5 页 共 10 页
ORACLE 11g OCM
select member from v$logfile;
show parameter control_files
-- spfile/password file: $ORACLE_HOME/dbs
-- Delete files
rm /u01/app/oracle/oradata/PROD1/system01.dbf
rm /u01/app/oracle/oradata/PROD1/sysaux01.dbf
rm /u01/app/oracle/oradata/PROD1/undotbs01.dbf
rm /u02/app/oracle/oradata/PROD1/users01.dbf
rm /u02/app/oracle/oradata/PROD1/system02.dbf
rm /u01/app/oracle/oradata/PROD1/temp01.dbf
rm /u01/app/oracle/oradata/PROD1/control01.ctl
rm /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD1.ora
rm /u01/app/oracle/oradata/PROD1/redo101.log
rm /u01/app/oracle/oradata/PROD1/redo201.log
rm /u01/app/oracle/oradata/PROD1/redo301.log
rm /u02/app/oracle/oradata/PROD1/redo102.log
rm /u02/app/oracle/oradata/PROD1/redo202.log
rm /u02/app/oracle/oradata/PROD1/redo302.log
......
-- Abort shutdown the DB
SQL> SHUTDOWN ABORT
-- 获取该 target DB 的最近一个 DBID 值
SQL> conn rman/rman@emrep
SQL> select * from rc_database;
-- Connect with RMAN
rman target / catalog rman/rman@emrep --因为 PROD1 DB 未能启动，无法用 sys@prod1登入
RMAN> SET DBID 2196200734
-- In SQL*Plus or RMAN, start the instance mode NOMOUNT
STARTUP NOMOUNT
Configure the FRA so that RMAN can find the AUTOBACKUP without problems.
-- In SQL*Plus, configure the parameters of the FRA
-- 注意：须先设定 FRA 大小，再设定 FRA 位置
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';
-- In RMAN, recover the SPFILE, restarting the instance and restore the database
restore spfile from autobackup;
-- 注：在 no catalog 恢复时，可能出错：
-- RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
-- 可以直接指定最新的 autobackup 文件位置进行恢复
-- restore spfile from '/u01/app/oracle/fast_recovery_area/PROD1/autobackup/xxx.bkp';
第 6 页 共 10 页
ORACLE 11g OCM
shutdown immediate;
startup nomount;
restore controlfile from autobackup;
alter database mount;
restore database
-- In SQL*Plus, get the last ARCHIVELOG sequence# and thread#
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select thread#, resetlogs_change#, archived, sequence#, completion_time
from v$archived_log
where archived='YES'
AND COMPLETION_TIME = (SELECT MAX(COMPLETION_TIME)
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES');
-- In RMAN, recovered until the last ARCHIVE LOG (n+1) and open with RESETLOGS
-- If the last created archived redo log has sequence n,
-- then specify UNTIL SEQUENCE n+1 so that RMAN applies n and then stops.
recover database until sequence 3 thread 1;
alter database open resetlogs;
new incarnation of database registered in recovery catalog
list incarnation;
-- Perform a new backup after recover open with resetlogs (VERY IMPORTANT)
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
10. 丢失临时表空间文件
-- Note: In UNIX/Linux environment the deletion of file is NOT reflected immediately.
-- Rerun the same query may use the DB Cache and the query may still continue successfully.
SQL> alter session set sort_area_size=50;
SQL> set autotrace off;
SQL> create table t1 as select * from all_objects;
SQL> insert into t1 select * from all_objects;
SQL> /
-- … REPEAT the insert for about 10 times …
SQL> /
68938 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
620442
第 7 页 共 10 页
ORACLE 11g OCM
SQL> set autotrace traceonly statistics;
SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc;
Statistics
----------------------------------------------------------
77 recursive calls
4 db block gets
9179 consistent gets
12609 physical reads
0 redo size
11896316 bytes sent via SQL*Net to client
455402 bytes received via SQL*Net from client
41364 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
620442 rows processed
-- Now delete the temporary tablespace DATAFILE
rm /u01/app/oracle/oradata/PROD1/temp01.dbf
-- Check the result
SQL> insert into t1 select * from all_objects;
SQL> /
-- … REPEAT the insert for about 5 times …
SQL> /
SQL> commit;
SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc;
-- or
SQL> select * from t1 order by 2 desc, 1, 5 desc, 4, 6 desc,7,8;
ERROR at line 1:
ORA-01565: error in identifying file '.../temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
-- In Oracle 11g the instance startup will re-create the Tempfiles.
-- Just restart the instance to re-create Tempfiles
-- But in production DB, You can be recreate the tempfiles manually:
-- Create a new temp TBS as default, then drop the old temp TBS.
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD1/temp2.dbf'
size 100m autoextend on;
alter database default temporary tablespace temp2;
drop tablespace temp;
第 8 页 共 10 页
ORACLE 11g OCM
11. 修复数据块坏区 (Block Corruption)
sqlplus / as sysdba
-- Create a test TBS
CREATE TABLESPACE BC DATAFILE '/u01/app/oracle/oradata/PROD1/bc01.dbf' SIZE 20M;
-- Create a sample table in the TBS
CREATE TABLE TEST_BC TABLESPACE BC AS SELECT * FROM DBA_OBJECTS;
RMAN> backup tablespace bc;
RMAN> backup archivelog all;
-- 定位数据文件(FILE_NO)和表(block_no)的位置
-- 例如:
-- FILE_NO BLOCK_NO
-- ---------- ----------
-- 7 131
SELECT DISTINCT
MIN(DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) as FILE_NO,
MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)) as BLOCK_NO
FROM TEST_BC;
-- Flush the cache to show the corruption quickly
ALTER SYSTEM FLUSH BUFFER_CACHE;
-- 模拟出错
dd of=/u01/app/oracle/oradata/PROD1/bc01.dbf bs=8192 conv=notrunc seek=131 << EOF
OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm
EOF
dd of=/u01/app/oracle/oradata/PROD1/bc01.dbf bs=8192 conv=notrunc seek=132 << EOF
OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm OCM ocm
EOF
-- Block Corruption should appear ORA_01578, ORA-01110 or ORA-19566 errors
SELECT * FROM TEST_BC;
...
ORA-01578: ORACLE data block corrupted (file #7, block #129)
ORA-01110: data file 7: '/u01/app/oracle/oradata/PROD1/bc01.dbf'
RMAN> backup tablespace bc;
...
ORA-19566: exceeded limit of 0 corrupt blocks for file
-- Use DRA (Data Recovery Advisor) to repair the corrupted blocks.
rman target / catalog rman/rman@emrep
第 9 页 共 10 页
ORACLE 11g OCM
RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;
-- Check the result
RMAN> list failure all;
第 10 页 共 10 页
ORACLE 11g OCM
305 - Configure RMAN
1. 文档
l Backup and Recovery Reference
2. 参照考点
l 【301】Mantain Recovery Catalogs
l 【302】Configure Recovery Manager
3. 日期格式
-- By default, date format is "DD-MON-YY"
-- 修改环境变量 NLS_DATE_FORMAT
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
rman target sys@prod1 catalog rman/rman@emrep
-- Check the date format with backup list
LIST BACKUP SUMMARY;
4. 内部脚本（Stored Scripts）
-- Creating Stored scripts
CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
-- Executing Stored Scripts
RUN
{
EXECUTE GLOBAL SCRIPT global_full_backup;
}
-- Deleting Stored Scripts
DELETE GLOBAL SCRIPT 'global_full_backup';
第 1 页 共 4 页
ORACLE 11g OCM
5. 外部脚本
-- Create a backup script
vi /tmp/test_rman.rcv
-- Add the following line
BACKUP DATAFILE 4;
-- APPEND: appending logs to the output file, instead of overwriting it
rman target sys@prod1 catalog rman/rman@emrep cmdfile=/tmp/test_rman.rcv
log=/tmp/test_rman.log append
-- Review the output file
cat /tmp/test_rman.log
6. 跟踪调试
rman target sys@prod1 catalog rman/rman@emrep cmdfile=/tmp/test_rman.rcv
log=/tmp/test_rman.log append debug trace=/tmp/test_rman.trc
7. 备份设备（Backup Device）
-- Configuring the Default Device for Backups: Disk/SBT
-- sbt 配置要参照设备文档或第三方 media manager 备份文档
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
-- 直接备份
BACKUP DEVICE TYPE sbt DATABASE;
BACKUP DEVICE TYPE DISK DATABASE;
8. 备份类型
-- Configuring the Default Type for Backups: BackupSet/Copy
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; -- image copies
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; -- uncompressed
-- 压缩备份集
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
第 2 页 共 4 页
ORACLE 11g OCM
9. 其他设置参数
-- Backup Set：备份集，可由多个备份文件（Backup Piece）组成。
-- Backup Piece：备份片（备份文件）
-- MAXSETSIZE: 一个备份集的最大容量. Default MAXSETSIZE is unlimited.
-- MAXSETSIZE should be at least as large as the largest datafile.
configure maxsetsize to 1024M;
-- configure maxsetsize clear;
-- MAXPIECESIZE：一个备份文件(Backup piece)的最大容量. Default is unlimited.
-- 当设定单个备份文件的限定大小后，备份集(Backup Set)将按设定大小生成多个备份文件。
configure channel device type disk maxpiecesize 50m;
-- configure channel device type disk clear;
第 3 页 共 4 页
ORACLE 11g OCM
-- MAXOPENFILES：每个 channel 可同时读取的文件数. The MAXOPENFILES default is 8.
configure channel device type disk maxopenfiles 2 rate 50m;
-- configure channel device type disk clear;
-- FILESPERSET：每个备份集可包含的数据文件(data files)数量. Default is 64.
backup database filesperset 2;
-- SECTION SIZE：在并行备份时，各 channel 按 section size 分拆单个大数据文件进行备份。
-- 每个 section 写入一个 Backup Piece 中。一个数据文件最大可分拆为256个 section。
-- 注意：SECTION SIZE 和 MAXPIECESIZE 不能同时使用。
backup tablespace sysaux section size 50m;
RMAN-06808: SECTION SIZE cannot be used when piece limit is in effect
第 4 页 共 4 页
ORACLE 11g OCM
306 - Create Different Types of RMAN
Backups to Cater for Different Performance
and Retention Requirements
1. 文档
l Backup and Recovery User's Guide -> 9 Backing Up the Database
2. 备份保留（Backup Retention）
-- Default policy of backup retention
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
-- Configure RMAN to keep the last 3 backups, the 4th is marked as OBSOLETE
configure retention policy to redundancy 3;
show all;
-- Configure to keep the 2 weeks' backups
-- The First is to modify the parameter CONTROL_FILE_RECORD_KEEP_TIME
SQL> alter system set control_file_record_keep_time=15 scope=both;
RMAN> configure retention policy to recovery window of 15 days;
RMAN> show all;
长期保留备份（Long-Term Storage）
-- KEEP option: 设定的保留时间不受 retention policy 限制，
-- keep forever 必须在 recovery catalog 中使用。
rman target / catalog rman/rman@emrep
-- Keep a backup of the database never expired
backup database keep forever format '/tmp/%U';
-- Keep a backup to an expire-time from the day of backup (not including the specified time)
backup tablespace users tag 'mybak' keep until time 'sysdate+1' format '/tmp/%U';
list backup summary;
-- Nokeep a backup and set it obsoleted, exempt from the retention policy
change backupset 6103 nokeep;
第 1 页 共 2 页
ORACLE 11g OCM
3. 备份速度（Channel Rate）
-- Limit the transfer rate to a maximum of 20M per channel
run {
allocate channel d1 device type disk rate 20m;
allocate channel d2 device type disk rate 20m;
backup as backupset database;
release channel d1;
release channel d2;
}
-- Set the default limit with CONFIGURE CHANNEL
CONFIGURE CHANNEL DEVICE TYPE DISK RATE 20M;
show all;
-- Remove the rate configuration
configure channel device type disk clear;
show all;
4. 其他设置
以下相关的 RMAN 备份设置，请参照其它考点内容：
· Configure the FRA (Fast Recovery Area)
· Configure multiple channels
· Enable BLOCK CHANGE TRACKING (for incremental backups)
· Enable AUTOBACKUP
· Enable OPTIMIZATION
· Use INCREMENTAL BACKUP
第 2 页 共 2 页
ORACLE 11g OCM
307 - Set Flashback Database Parameters
1. 文档
l Backup and Recovery User's Guide -> 7 Using Flashback Database and Restore Points
2. 启用闪回（Enable Flashback）
-- 1. 闪回模式：Check FLASHBACK mode
select flashback_on from v$database;
select ts#,name,flashback_on from v$tablespace;
-- Enable FRA settings and FLASHBACK mode. (Enable archive log mode first)
SHOW PARAMETER RECOVERY_FILE
shutdown immediate
startup mount
ALTER DATABASE FLASHBACK ON;
alter database open;
-- 2. 闪回数据保留时间：
-- Configure flashback retention to 2 days (only for Flashback Database)
-- 2 days * 24 hours * 60 minutes = 2880 min
alter system set db_flashback_retention_target=2880 scope=both;
show parameter retention;
-- 3. 回收站（recyclebin）：Confirm the recycle bin enabled 'on'
show parameter recyclebin
3. 表空间闪回设置
By default, the FLASHBACK mode applies to all the TABLESPACES
-- Disable FLASHBACK to a specific TABLESPACE
ALTER TABLESPACE USERS FLASHBACK OFF;
select ts#,name,flashback_on from v$tablespace;
-- Re-enable tablespace FLASHBACK again, the instance must be mount.
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER TABLESPACE USERS FLASHBACK ON;
ALTER DATABASE OPEN;
第 1 页 共 1 页
ORACLE 11g OCM
308 - Configure a Fast Recovery Area
1. 文档
l Backup and Recovery User's Guide -> 5 Configuring the RMAN Environment -> Configuring the
Fast Recovery Area
2. Fast Recovery Area（FRA）
Fast Recovery Area（快速恢复区）是 Oracle 数据库的专用管理空间，用于保存 RMAN 的磁盘备份、
控制文件的自动备份、以及归档日志文件，其中的文件统一以 OMF(Oracle Managed File)格式管理命名。
如果 FRA 空间不足，系统将根据设定的数据保留策略，尝试删除其中的过期文件、冗余文件或已经备
份的文件，若还是没有剩余空间，则 FRA 中的以下文件将无法写入更新，数据库将出错停止运行。
l 控制文件：Multiplexed copies of the current control file
l 在线日志文件：Online redo log files
l 归档日志文件：Archived redo log files
l 闪回日志文件：Flashback logs（如果设置了 guaranteed restore points）
-- 检查设置 FRA 的 2 个参数 (dest & size)
show parameter db_recovery_file_dest
-- Enable FRA
-- 1. Set the recovery area size
-- 2. Set the recovery area location
-- 3. Restart the DB
alter system set DB_RECOVERY_FILE_DEST_SIZE=5G;
alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';
-- Disable FRA
-- 1. Disable flashback database
-- 2. Change the location for archive logs
-- 3. Disable the FRA destination
ALTER DATABASE FLASHBACK OFF;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog/';
alter system set DB_RECOVERY_FILE_DEST='';
第 1 页 共 3 页
ORACLE 11g OCM
3. FRA 外的设置
1) 多重归档日志
Archive logs out of FRA
-- Set LOG_ARCHIVE_DEST_n parameter to non-FRA locations for archive logs.
-- If LOG_ARCHIVE_DEST_n (n = 0..31) is not specified, then LOG_ARCHIVE_DEST_10 => FRA (默认)
show parameter log_archive_dest
-- Change the archive logs to 2 destination out of FRA
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog/';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/oracle/archivelog2/';
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
-- Check the new archive logs
ls /home/oracle/archivelog/
ls /home/oracle/archivelog2/
ls /u01/app/oracle/fast_recovery_area/PROD1/archivelog/<date>/
-- Reset the archive logs to FRA
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';
alter system set DB_RECOVERY_FILE_DEST_SIZE=5G;
alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';
shutdown immediate
startup
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
-- Check the new archive logs
ls /home/oracle/archivelog/
ls /home/oracle/archivelog2/
ls /u01/app/oracle/fast_recovery_area/PROD1/archivelog/<date>/
-- Check the location of ARCHIVELOG
select name,blocks,block_size,archived
from v$archived_log where archived='YES' and name is not null order by completion_time;
第 2 页 共 3 页
ORACLE 11g OCM
2) 多重数据备份
-- Duplex backups out of FRA (在 FRA 内不支持保存重复备份)
-- Set the backup copies to 2
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup1/%U','/home/oracle/backup2/%U';
backup tablespace users;
-- or
BACKUP DEVICE TYPE DISK tablespace users
FORMAT '/home/oracle/backup1/%U','/home/oracle/backup2/%U';
-- Check the backupsets
ls /home/oracle/backup1
ls /home/oracle/backup2
-- Delete the backups
delete backup of tablespace users;
-- Reset the RMAN config
configure datafile backup copies for device type disk clear;
configure channel device type disk clear;
4. FRA 空间计算
Oracle 建议的 FRA 空间预算公式（用于增量备份）
n = 每次（增量）备份的间隔天数
y = 用于更新逻辑备库的外部归档日志的延迟天数
FRA size = Size of copy of database +
Size of an incremental backup +
Size of (n + 1) days of archived redo logs +
Size of (y + 1) days of foreign archived redo logs (for logical standby) +
Size of control file +
Size of an online redo log member * number of log groups +
Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value)
-- 查看 FRA 的使用统计（Check the usage of FRA）
set lines 170
select * from v$recovery_area_usage;
第 3 页 共 3 页
ORACLE 11g OCM
309 - Perform Various Recovery Operations
Using Flashback Technology
1. 文档
l Backup and Recovery User's Guide -> 7 Using Flashback Database and Restore Points
l Backup and Recovery User's Guide -> 18 Performing Flashback and Database Point-in-Time
Recovery
l Advanced Application Developer's Guide -> 12 Using Oracle Flashback Technology
2. 各项 Flashback 技术特点
l 闪回数据库（Flashback Database）
使用通过 flashback database 语句，使数据库迅速地回滚到以前的某个时间点或者某个 SCN（系统更
改号）上，而不需要进行时间点的恢复操作。
第 1 页 共 9 页
ORACLE 11g OCM
l 闪回删除（Flashback Drop）
类似于操作系统的垃圾回收站功能，可以从回收站中恢复被 Drop 的表或者索引。
l 闪回表（Flashback Table）
可以使数据库表能够被恢复到之前的某一个时间点上。
l 闪回版本查询（Flashback Version Query）
通过该功能，可以看到特定的表在某个时间段内所进行的任何修改操作及变化。
l 闪回数据归档（Flashback Data Archive）
在预设的保留时间范围内，可以查询指定数据表的任何时间点的数据，不受 Undo 保留时间限制。如
果该数据表变化很频繁，对空间的要求可能很高。
l 闪回事务查询（Flashback Transaction Query）
可以在事务级别上检查数据库的任何改变，可用于对数据库的性能优化、事务审计及错误诊断等操作。
【注意】
l 修改表结构 的 DDL 语句 （包括：drop/modify column, move table, drop partition, truncate
table/partition, add constraint 等) 会使数据表的 Undo 数据失效，无法使用闪回技术恢复 DDL 语
句执行之前的内容（引致 ORA-01466错误）。其它只修改表的存储属性的 DDL 语句（如：PCTFREE,
INITRANS, MAXTRANS 等)则不会令 Undo 数据失效。
l Oracle 强烈建议设置使用 Undo 自动管理模式（UNDO_MANAGEMENT = AUTO），并且设置 UNDO_RETENTION
参数，确保在需要使用闪回处理的时间内，有足够的 Undo 保留数据以供使用。
3. Flashback Database
1) Restore the DB to a restore point.
-- The GUARANTEE clause keep the Flashback Logs beyond the retention
create restore point before_exercises guarantee flashback database;
-- Check the DB restore points
select * from v$restore_point;
-- Delete the HR schema
DROP USER HR CASCADE;
-- Flashback the DB to restore point. The DB instance must be in MOUNT state.
shutdown immediate
startup mount
第 2 页 共 9 页
ORACLE 11g OCM
flashback database to restore point before_exercises;
-- 如果出错 ORA-38780: Restore point 'xxx' does not exist. 可加双引号重试。
-- flashback database to restore point "before_exercises";
-- Open the DB at the restore point
-- ALTER DATABASE OPEN RESETLOGS;
-- 或者 RECOVER to the present time and open the DB normally
RECOVER DATABASE;
ALTER DATABASE OPEN;
-- Delete restore points.
DROP RESTORE POINT BEFORE_EXERCISES;
2) Performing User-Managed Database Flashback and Recovery with SQL*Plus
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
-- Flashack Database to a prior time (e.g. 10 minutes ago).
STARTUP FORCE MOUNT
FLASHBACK DATABASE TO SCN 46963;
FLASHBACK DATABASE TO TIMESTAMP '2015-09-08 19:00:00';
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2015/09/08 19:00:00','YYYY/MM/DD HH24:MI:SS');
-- Open the DB at the restore point
-- ALTER DATABASE OPEN RESETLOGS;
-- 或者 RECOVER to the present time and open the DB normally
RECOVER DATABASE;
ALTER DATABASE OPEN;
4. Flashback Drop
Enable the Recycle Bin (RECYCLEBIN) to recover tables deleted (DROP TABLE)
1) Undrop a deleted table
-- Check and turn on the Recycle Bin
show parameter recyclebin;
-- If the recyclebin is OFF, turn it on and restart the DB
-- alter system set recyclebin=on scope=spfile;
-- Create a table for testing
CREATE TABLE TEST_EMPLOYEES TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;
-- Delete the table
DROP TABLE TEST_EMPLOYEES;
第 3 页 共 9 页
ORACLE 11g OCM
-- Check the recycle bin for the new deleted object
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;
SELECT * FROM DBA_RECYCLEBIN;
-- Undrop the table
FLASHBACK TABLE TEST_EMPLOYEES TO BEFORE DROP;
SELECT * FROM RECYCLEBIN;
-- Check the table
SELECT COUNT(*) FROM TEST_EMPLOYEES;
2) Undrop and rename a deleted table
-- Delete the table
DROP TABLE TEST_EMPLOYEES;
-- Undrop and rename the table
FLASHBACK TABLE TEST_EMPLOYEES TO BEFORE DROP RENAME TO TEST_EMPLOYEES_OLD;
-- The rename with original name
alter table TEST_EMPLOYEES_OLD RENAME TO TEST_EMPLOYEES;
3) Undrop one of the deleted tables with the same name.
-- Delete the 1st table
DROP TABLE TEST_EMPLOYEES;
-- Create a new 2nd table with the same name but no records
CREATE TABLE TEST_EMPLOYEES TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES WHERE 1=2;
-- where 1=2 令 where 条件永远为假，返回空值数据。
-- where 1=1 令 where 条件永远为真，返回全部数据。
-- Delete the 2nd table
DROP TABLE TEST_EMPLOYEES;
-- Check the RECYCLEBIN that there are two tables with the same name "TEST_EMPLOYEES"
SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME='TEST_EMPLOYEES';
-- Query the tables in the RECYCLEBIN
SELECT COUNT(*) FROM "BIN$3qdKDJCYSGngQ2QBqMBRgw==$0";
SELECT COUNT(*) FROM "BIN$3qdKDJCdSGngQ2QBqMBRgw==$0";
-- Undrop the 1st table with data in the RECYCLEBIN
FLASHBACK TABLE "BIN$3qdKDJCYSGngQ2QBqMBRgw==$0" TO BEFORE DROP;
4) Permanent drop data
-- Delete the table permanently
DROP TABLE TEST_EMPLOYEES PURGE;
第 4 页 共 9 页
ORACLE 11g OCM
-- Purge a tablespace objects in the recycle bin
PURGE TABLESPACE USERS;
-- Purge a tablespace objects belonging to a user in the recycle bin
PURGE TABLESPACE USERS USER HR;
-- Purge the current user's recycle bin
PURGE RECYCLEBIN;
-- Purge all Recycle Bins
PURGE DBA_RECYCLEBIN;
5) Undrop dependent objects: Dependent objects do not retake the original name after undrop.
-- Create a test table
CREATE TABLE TEST_EMPLOYEES TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;
-- Create an index
CREATE INDEX IDX_EMPLOYEES ON TEST_EMPLOYEES(EMPLOYEE_ID) TABLESPACE USERS;
-- Delete the table
DROP TABLE TEST_EMPLOYEES;
-- Check the names of deleted objects
SELECT * FROM RECYCLEBIN;
-- Undrop the table
FLASHBACK TABLE TEST_EMPLOYEES TO BEFORE DROP;
-- Check the name of the index
select index_name from dba_indexes where table_name='TEST_EMPLOYEES';
-- Rename the index
ALTER INDEX "BIN$3qdKDJCiSGngQ2QBqMBRgw==$0" RENAME TO "IDX_EMPLOYEES";
-- Clean environment
DROP TABLE TEST_EMPLOYEES PURGE;
5. Flashback Table
Restoring a Table to an Earlier State. Flashback table Not supported for user SYS.
-- Create a test table
conn hr/hr
CREATE TABLE employees_test AS SELECT * FROM employees;
-- Check the data
SELECT salary FROM employees_test WHERE salary < 2500;
-- 注意：Enable row movement for the table to flashback
conn hr/hr
ALTER TABLE employees_test ENABLE ROW MOVEMENT;
第 5 页 共 9 页
ORACLE 11g OCM
-- Mark the current time or SCN
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual; -- Mark this time as T1
conn / as sysdba
select current_scn from v$database; -- Mark this SCN as S1
-- 修改数据
UPDATE employees_test SET salary = salary * 1.1 WHERE salary < 2500;
COMMIT;
SELECT salary FROM employees_test WHERE salary < 2500;
-- Flashback table to the time T1
FLASHBACK TABLE employees_test
TO TIMESTAMP TO_TIMESTAMP('2016-03-08 01:46:55', 'YYYY-MM-DD HH24:MI:SS');
-- or to the SCN S1
FLASHBACK TABLE employees_test TO SCN 971712;
-- 检查结果
SELECT salary FROM employees_test WHERE salary < 2500;
drop table employees_test;
6. Flashback Query
Flashback Query: Retrieve data for an earlier time that you specify with the AS OF clause of
the SELECT statement. The query explicitly references a past time through a time stamp or System
Change Number (SCN)
---- 例子1 ------
-- Create a test table
CREATE TABLE TEST_EMPLOYEES TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;
-- Mark the current time and SCN
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual; -- Mark this time as T1
select current_scn from v$database; -- Mark this SCN as S1
-- Delete a row in the table
DELETE FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID=100;
COMMIT;
SELECT * FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID=100;
-- Query the Lost Row with Flashback Query at time T1
SELECT * FROM TEST_EMPLOYEES
第 6 页 共 9 页
ORACLE 11g OCM
AS OF TIMESTAMP TO_TIMESTAMP('2015-11-04 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE EMPLOYEE_ID=100;
-- or at SCN S1
SELECT * FROM TEST_EMPLOYEES
AS OF SCN 981614
WHERE EMPLOYEE_ID=100;
-- Restoring a Lost Row at time T1
INSERT INTO TEST_EMPLOYEES (
SELECT * FROM TEST_EMPLOYEES
AS OF TIMESTAMP TO_TIMESTAMP('2015-11-04 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE EMPLOYEE_ID=100
);
SELECT * FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID=100;
---- 例子2 ------
-- Delete some rows in the table
DELETE FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID>200;
COMMIT;
SELECT * FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID>200;
-- Create a View for the last 1 minutes' data (注意：在此段时间内要有数据记录)
CREATE VIEW vw_last AS
SELECT * FROM TEST_EMPLOYEES
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
select * from vw_last;
drop view vw_last;
-- Restore the data row lost in the last 1 minutes
INSERT INTO TEST_EMPLOYEES
(SELECT * FROM TEST_EMPLOYEES AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)
MINUS
SELECT * FROM TEST_EMPLOYEES
);
commit;
-- Check the result
SELECT * FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID>200;
7. Flashback Version Query
Flashback Version Query: Retrieve metadata and historical data for a specific time interval.
use the VERSIONS BETWEEN clause of the SELECT statement. Metadata for each row version includes
start and end time, type of change operation, and identity of the transaction that created the
第 7 页 共 9 页
ORACLE 11g OCM
row version.
l VERSIONS_STARTSCN and VERSIONS_STARTTIME: Starting System Change Number (SCN) or TIMESTAMP
when the row version was created. If this pseudocolumn is NULL, then the row version was
created before start.
l VERSIONS_ENDSCN and VERSIONS_ENDTIME: SCN or TIMESTAMP when the row version expired. If the
pseudocolumn is NULL, then either the row version was current at the time of the query or
the row corresponds to a DELETE operation.
l VERSIONS_XID: Identifier (a RAW number) of the transaction that created the row version.
l VERSIONS_OPERATION: Operation performed by the transaction: I for insertion, D for deletion,
or U for update. The version is that of the row after an INSERT operation, the row before
a DELETE operation, or the row affected by an UPDATE operation.
-- 检查 flashback query 的有效时间范围
-- 注意：
show parameter undo_rentention;
-- or
set lines 170
select XID,start_scn,start_timestamp,commit_scn,commit_timestamp
from flashback_transaction_query
where table_name='TEST_EMPLOYEES';
-- Query the data version (注意：时间戳要在 undo_rentention 有效范围内)
col versions_starttime for a25
col versions_endtime for a25
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime,
versions_xid, versions_operation, LAST_NAME, EMPLOYEE_ID
FROM TEST_EMPLOYEES
VERSIONS BETWEEN TIMESTAMP
SYSTIMESTAMP - INTERVAL '5' MINUTE
AND SYSTIMESTAMP
WHERE EMPLOYEE_ID > 200;
-- or
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime,
versions_xid, versions_operation, LAST_NAME, EMPLOYEE_ID
FROM TEST_EMPLOYEES
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE EMPLOYEE_ID > 200;
8. Flashback Transaction
-- Create a test table
CREATE TABLE TEST_EMPLOYEES TABLESPACE USERS AS SELECT * FROM HR.EMPLOYEES;
ALTER TABLE TEST_EMPLOYEES ADD CONSTRAINT test_pk PRIMARY KEY(employee_id);
第 8 页 共 9 页
ORACLE 11g OCM
-- First to enable supplemental logging, or you cannot use Flashback Transaction Query
select log_mode, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK from v$database;
-- If the SUPPLEMENTAL_LOG_DATA_xxx = 'NO', then enable the supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- If the table has primary key, then enable the primary key log
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- Delete 2 records from the table TEST_EMPLOYEES
DELETE FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID=100;
COMMIT;
DELETE FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID=101;
COMMIT;
-- Check the transactions from the FLASHBACK_TRANSACTION_QUERY view
select XID,start_scn,start_timestamp,commit_scn,commit_timestamp,undo_sql
from flashback_transaction_query
where table_name='TEST_EMPLOYEES';
-- Using Flashback Version Query to check the changes of a DELETE (VERSIONS OPERATION = 'D')
set lines 170
select versions_xid, versions_startscn, versions_endscn,
versions_operation, employee_id, first_name
from test_employees versions between scn minvalue and maxvalue
where versions_operation='D'
order by 2;
-- Archive current redo log
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT;
-- Enable table row movement
-- alter table test_employees enable row movement;
-- Flashback the DELETEs by the transaction ID (XID)
exec dbms_flashback.transaction_backout
(2, xid_array('01000C004A030000','09001F0020030000'),dbms_flashback.nocascade);
-- Check the result
SELECT EMPLOYEE_ID, FIRST_NAME FROM TEST_EMPLOYEES WHERE EMPLOYEE_ID IN (100,101);
-- Clean environment
DROP TABLE TEST_EMPLOYEES PURGE;
-- Drop the supplemental logs
alter database drop supplemental log data (primary key) columns;
alter database drop supplemental log data;
第 9 页 共 9 页
