Oracle备份与恢复 #

备份类型
├── 物理备份
│   ├── 冷备份（一致性备份）
│   └── 热备份（非一致性备份）
├── 逻辑备份
│   └── Data Pump导出
└── 备份策略
    ├── 全量备份
    ├── 增量备份
    └── 归档日志备份

数据库模式
├── 归档模式（ARCHIVELOG）
│   └── 支持时间点恢复
└── 非归档模式（NOARCHIVELOG）
    └── 只能恢复到备份点

-- 连接RMAN
rman target /

-- 查看配置
RMAN> SHOW ALL;

-- 配置备份参数
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

-- 全库备份
RMAN> BACKUP DATABASE;

-- 全库备份到指定位置
RMAN> BACKUP DATABASE FORMAT '/backup/full_%d_%T_%s.bak';

-- 包含归档日志的备份
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- 备份表空间
RMAN> BACKUP TABLESPACE users;

-- 备份数据文件
RMAN> BACKUP DATAFILE 1;

-- 0级增量备份（相当于全量）
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- 1级增量备份
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- 累积增量备份
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

-- 增量备份策略
-- 周日：0级备份
-- 周一至周六：1级备份

-- 备份所有归档日志
RMAN> BACKUP ARCHIVELOG ALL;

-- 备份并删除归档日志
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

-- 备份指定时间范围的归档日志
RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

-- 恢复整个数据库
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

-- 时间点恢复
RMAN> RUN {
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
}

-- 恢复表空间
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

-- 恢复数据文件
RMAN> SQL 'ALTER DATABASE DATAFILE 5 OFFLINE';
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
RMAN> SQL 'ALTER DATABASE DATAFILE 5 ONLINE';

-- 导出整个数据库
expdp system/password FULL=Y DIRECTORY=backup_dir DUMPFILE=full.dmp LOGFILE=full.log;

-- 导出Schema
expdp system/password SCHEMAS=hr DIRECTORY=backup_dir DUMPFILE=hr.dmp LOGFILE=hr.log;

-- 导出表
expdp system/password TABLES=employees,departments DIRECTORY=backup_dir DUMPFILE=tables.dmp;

-- 导出表空间
expdp system/password TABLESPACES=users DIRECTORY=backup_dir DUMPFILE=users.dmp;

-- 并行导出
expdp system/password FULL=Y DIRECTORY=backup_dir DUMPFILE=full_%U.dmp PARALLEL=4;

-- 查询导出
expdp system/password TABLES=employees DIRECTORY=backup_dir DUMPFILE=emp.dmp QUERY=employees:"WHERE department_id = 10";

-- 导入整个数据库
impdp system/password FULL=Y DIRECTORY=backup_dir DUMPFILE=full.dmp LOGFILE=import.log;

-- 导入Schema
impdp system/password SCHEMAS=hr DIRECTORY=backup_dir DUMPFILE=hr.dmp;

-- 导入表
impdp system/password TABLES=employees DIRECTORY=backup_dir DUMPFILE=tables.dmp;

-- 重映射Schema
impdp system/password REMAP_SCHEMA=hr:hr_new DIRECTORY=backup_dir DUMPFILE=hr.dmp;

-- 重映射表空间
impdp system/password REMAP_TABLESPACE=users:new_users DIRECTORY=backup_dir DUMPFILE=users.dmp;

-- 导入时排除对象
impdp system/password FULL=Y DIRECTORY=backup_dir DUMPFILE=full.dmp EXCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')";

-- 创建目录
CREATE DIRECTORY backup_dir AS '/backup';
GRANT READ, WRITE ON DIRECTORY backup_dir TO system;

-- 查看目录
SELECT * FROM dba_directories;

-- 闪回查询
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- 闪回查询到指定时间
SELECT * FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- 闪回版本查询
SELECT 
    versions_xid,
    versions_starttime,
    versions_endtime,
    first_name,
    salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
    (SYSTIMESTAMP - INTERVAL '1' HOUR) AND SYSTIMESTAMP
WHERE employee_id = 100;

-- 启用行移动
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- 闪回表到指定时间
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- 闪回表到SCN
FLASHBACK TABLE employees TO SCN 1234567;

-- 查看回收站
SELECT * FROM user_recyclebin;

-- 闪回删除的表
FLASHBACK TABLE employees TO BEFORE DROP;

-- 闪回并重命名
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

-- 清空回收站
PURGE RECYCLEBIN;

-- 彻底删除（不进回收站）
DROP TABLE employees PURGE;

-- 检查闪回数据库是否启用
SELECT flashback_on FROM v$database;

-- 启用闪回数据库
ALTER DATABASE FLASHBACK ON;

-- 闪回数据库
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
ALTER DATABASE OPEN RESETLOGS;

-- 闪回数据库到SCN
FLASHBACK DATABASE TO SCN 1234567;

备份策略示例
├── 每日备份
│   ├── 增量备份（1级）
│   └── 归档日志备份
├── 每周备份
│   └── 全量备份（0级）
├── 每月备份
│   └── Data Pump逻辑备份
└── 保留策略
    ├── 备份保留7天
    └── 归档日志保留30天

-- RMAN备份脚本
RUN {
    ALLOCATE CHANNEL ch1 TYPE DISK;
    ALLOCATE CHANNEL ch2 TYPE DISK;
    
    BACKUP INCREMENTAL LEVEL 0 DATABASE
        FORMAT '/backup/inc0_%d_%T_%s.bak'
        TAG 'INC0_BACKUP';
    
    BACKUP ARCHIVELOG ALL
        FORMAT '/backup/arch_%d_%T_%s.bak'
        DELETE INPUT;
    
    BACKUP CURRENT CONTROLFILE
        FORMAT '/backup/cf_%d_%T_%s.bak';
    
    RELEASE CHANNEL ch1;
    RELEASE CHANNEL ch2;
}

-- 删除过期备份
RMAN> DELETE OBSOLETE;
RMAN> DELETE EXPIRED BACKUP;