SQL> SELECT log_mode FROM v$database;LOG_MODE
————
ARCHIVELOG

— 1. 使用RMAN执行表空间时间点恢复
— 1.1 连接到RMAN
RMAN> CONNECT TARGET /– 1.2 执行表空间时间点恢复
RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’;– 2. 验证表空间状态
— 2.1 检查表空间是否在线
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 2.2 如果表空间处于离线状态，将其设置为在线
ALTER TABLESPACE EXAMPLE ONLINE;– 3. 使用闪回技术执行表空间时间点恢复
— 3.1 检查闪回数据库是否启用
SELECT flashback_on FROM v$database;– 3.2 执行表空间闪回
ALTER TABLESPACE EXAMPLE FLASHBACK OFF;ALTER TABLESPACE EXAMPLE OFFLINE;FLASHBACK TABLESPACE EXAMPLE TO TIMESTAMP TO_TIMESTAMP(‘2023-06-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);ALTER TABLESPACE EXAMPLE ONLINE;– 4. 使用备份集执行表空间时间点恢复
— 4.1 还原表空间
RMAN> RESTORE TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’;– 4.2 恢复表空间
RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’;– 4.3 将表空间设置为在线
ALTER TABLESPACE EXAMPLE ONLINE;

RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’;Starting recover at 01-JUN-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK

Creating automatic instance, with SID=’aux1′

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=aux1_ORCL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1G
processes=200
db_create_file_dest=/u01/app/oracle/auxiliary
log_archive_dest_1=’location=/u01/app/oracle/auxiliary’
enable_pluggable_database=true

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 9135448 bytes
Variable Size 301989888 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Automatic instance created

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 device type=DISK

contents of Memory Script:
{
# set requested point in time
set until time “to_date(‘2023-06-01 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;# restore the tablespaces in the recovery set and the auxiliary set
restore tablespace EXAMPLE;switch clone datafile all;}
executing Memory Script

executing command: SET until clause

Starting restore at 01-JUN-23
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/fgedudb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_31/o1_mf_nnndf_TAG20230531T230000_jk123456_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_05_31/o1_mf_nnndf_TAG20230531T230000_jk123456_.bkp tag=TAG20230531T230000
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:30
Finished restore at 01-JUN-23

datafile 5 switched to datafile copy
input datafile copy RECID=1 STAMP=1001234567 file name=/u01/app/oracle/oradata/fgedudb/example01.dbf

contents of Memory Script:
{
# recover the tablespaces in the recovery set
recover tablespace EXAMPLE;# switch back to original datafiles
switch datafile all;}
executing Memory Script

Starting recover at 01-JUN-23
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 123 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_01/o1_mf_1_123_jk123457_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_06_01/o1_mf_1_123_jk123457_.arc thread=1 sequence=123
media recovery complete, elapsed time: 00:00:15
Finished recover at 01-JUN-23

datafile 5 switched to datafile copy
input datafile copy RECID=2 STAMP=1001234582 file name=/u01/app/oracle/oradata/fgedudb/example01.dbf

Automatic instance removed
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/datafile/o1_mf_system_jk123458_.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/datafile/o1_mf_sysaux_jk123459_.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/datafile/o1_mf_undotbs1_jk123460_.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/onlinelog/o1_mf_1_jk123461_.log deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/onlinelog/o1_mf_2_jk123462_.log deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/pdbseed/system01.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/pdbseed/sysaux01.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/pdbseed/undotbs01.dbf deleted
auxiliary instance file /u01/app/oracle/auxiliary/ORCL/pdbseed/temp01.dbf deleted
Finished recover at 01-JUN-23

— 1. 验证表空间状态
— 1.1 检查表空间是否在线
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 1.2 检查数据文件状态
SELECT file_name, status FROM dba_data_files WHERE tablespace_name = ‘EXAMPLE’;– 2. 验证表空间中的数据
— 2.1 检查表空间中的对象数量
SELECT owner, object_type, count(*) FROM dba_objects WHERE tablespace_name = ‘EXAMPLE’ GROUP BY owner, object_type;– 2.2 验证表中的数据
SELECT COUNT(*) FROM hr.employees;– 2.3 验证特定时间点的数据
— 例如：检查恢复到2023-06-01 10:00:00的数据
SELECT * FROM hr.employees WHERE hire_date <= TO_DATE('2023-06-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');-- 3. 验证表空间性能 -- 3.1 执行查询测试 SELECT * FROM hr.employees WHERE department_id = 10;-- 3.2 执行插入测试 INSERT INTO hr.employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (9999, 'Test', 'User', 'test.user@fgedu.net.cn', SYSDATE, 'IT_PROG', 5000);COMMIT;-- 3.3 执行更新测试 UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 9999;COMMIT;-- 4. 验证表空间依赖关系 -- 4.1 检查是否有引用该表空间的对象 SELECT owner, object_name, object_type FROM dba_objects WHERE referenced_name = 'EXAMPLE' OR referenced_type = 'TABLESPACE';-- 5. 验证表空间的可读写性 -- 5.1 尝试在表空间中创建表 CREATE TABLE hr.test_table (id NUMBER, name VARCHAR2(100)) TABLESPACE EXAMPLE;-- 5.2 尝试插入数据 INSERT INTO hr.test_table VALUES (1, 'Test');COMMIT;-- 5.3 尝试查询数据 SELECT * FROM hr.test_table;-- 5.4 清理测试数据 DROP TABLE hr.test_table;DELETE FROM hr.employees WHERE employee_id = 9999;COMMIT;

SQL> SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;TABLESPACE_NAME STATUS
—————————— ———
EXAMPLE ONLINE

— 案例：将EXAMPLE表空间恢复到2023-06-01 10:00:00

— 1. 准备工作
— 1.1 检查数据库状态
SELECT log_mode FROM v$database;– 1.2 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 1.3 检查表空间中的数据文件
SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘EXAMPLE’;– 1.4 确定恢复时间点
— 恢复到2023-06-01 10:00:00

— 2. 执行表空间时间点恢复
— 2.1 使用RMAN执行恢复
RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’;– 3. 验证恢复结果
— 3.1 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 3.2 检查数据文件状态
SELECT file_name, status FROM dba_data_files WHERE tablespace_name = ‘EXAMPLE’;– 3.3 验证表中的数据
SELECT COUNT(*) FROM hr.employees;– 3.4 验证特定时间点的数据
SELECT * FROM hr.employees WHERE hire_date <= TO_DATE('2023-06-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');-- 4. 测试表空间功能 -- 4.1 执行查询测试 SELECT * FROM hr.employees WHERE department_id = 10;-- 4.2 执行插入测试 INSERT INTO hr.employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (9999, 'Test', 'User', 'test.user@fgedu.net.cn', SYSDATE, 'IT_PROG', 5000);COMMIT;-- 4.3 执行更新测试 UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 9999;COMMIT;-- 5. 清理测试数据 DELETE FROM hr.employees WHERE employee_id = 9999;COMMIT;

— 1. 准备测试环境
— 1.1 创建测试表空间
CREATE TABLESPACE test_ts DATAFILE ‘/u01/app/oracle/oradata/fgedudb/test_ts01.dbf’ SIZE 10G AUTOEXTEND ON NEXT 1G;– 1.2 创建测试用户
CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE test_ts;GRANT CONNECT, RESOURCE TO test_user;– 1.3 创建测试表
CREATE TABLE test_user.test_table (
id NUMBER,
name VARCHAR2(100),
value NUMBER,
create_date DATE
) TABLESPACE test_ts;– 1.4 插入测试数据
INSERT INTO test_user.test_table
SELECT
rownum,
‘Test ‘ || rownum,
rownum * 100,
SYSDATE – rownum/1000
FROM dual
CONNECT BY rownum <= 10000000;COMMIT;-- 1.5 创建表空间备份 RMAN> BACKUP TABLESPACE test_ts;– 1.6 等待一段时间，确保有归档日志
— 例如：等待10分钟

— 1.7 再次插入数据，模拟数据变更
INSERT INTO test_user.test_table
SELECT
rownum + 10000000,
‘Test ‘ || (rownum + 10000000),
(rownum + 10000000) * 100,
SYSDATE – rownum/1000
FROM dual
CONNECT BY rownum <= 1000000;COMMIT;-- 2. 测试表空间时间点恢复性能 -- 2.1 记录开始时间 SET TIMING ON -- 2.2 执行表空间时间点恢复 RMAN> RECOVER TABLESPACE test_ts UNTIL TIME ‘SYSDATE – 10/1440’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’;– 2.3 记录恢复时间
SET TIMING OFF

— 3. 验证恢复结果
— 3.1 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘test_ts’;– 3.2 验证表中的数据
SELECT COUNT(*) FROM test_user.test_table;– 3.3 执行查询测试
SET TIMING ON
SELECT COUNT(*) FROM test_user.test_table WHERE value > 500000000;SET TIMING OFF

— 3.4 执行插入测试
SET TIMING ON
INSERT INTO test_user.test_table VALUES (21000001, ‘Test 21000001’, 2100000100, SYSDATE);COMMIT;SET TIMING OFF

— 4. 分析测试结果
— 4.1 记录恢复时间
— 4.2 分析恢复前后的查询性能
— 4.3 分析恢复前后的DML性能

— 5. 清理测试数据
— 5.1 删除测试表
DROP TABLE test_user.test_table;– 5.2 删除测试用户
DROP USER test_user CASCADE;– 5.3 删除测试表空间
DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;

— 1. 恢复失败
— 问题：表空间时间点恢复失败
— 解决方案：
— 1.1 检查备份和归档日志是否可用
RMAN> LIST BACKUP OF TABLESPACE EXAMPLE;RMAN> LIST ARCHIVELOG ALL;– 1.2 检查目标时间点是否有可用的备份
RMAN> LIST BACKUP OF TABLESPACE EXAMPLE COMPLETED BEFORE ‘2023-06-01 10:00:00’;– 1.3 检查辅助目录空间
— df -h /u01/app/oracle/auxiliary/

— 1.4 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 2. 表空间无法在线
— 问题：恢复后表空间无法在线
— 解决方案：
— 2.1 检查表空间状态
SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘EXAMPLE’;– 2.2 检查表空间损坏情况
SELECT * FROM v$recover_file WHERE tablespace_name = ‘EXAMPLE’;– 2.3 尝试修复表空间
ALTER TABLESPACE EXAMPLE ONLINE;– 2.4 如果修复失败，执行介质恢复
RMAN> RECOVER TABLESPACE EXAMPLE;– 3. 数据不一致
— 问题：恢复后数据不一致
— 解决方案：
— 3.1 验证数据行数
SELECT COUNT(*) FROM hr.employees;– 3.2 验证数据内容
SELECT * FROM hr.employees WHERE rownum <= 10;-- 3.3 检查索引状态 SELECT index_name, status FROM dba_indexes WHERE tablespace_name = 'EXAMPLE';-- 3.4 重建损坏的索引 ALTER INDEX hr.emp_empid_pk REBUILD;-- 4. 恢复时间过长 -- 问题：表空间时间点恢复时间过长 -- 解决方案： -- 4.1 使用并行恢复 RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’ PARALLELISM 4;– 4.2 使用增量备份
— 定期创建增量备份，减少恢复时间

— 4.3 优化存储性能
— 使用高速存储设备，提高恢复速度

— 4.4 合理安排恢复时间
— 在低峰期执行恢复操作

— 5. 辅助实例创建失败
— 问题：RMAN无法创建辅助实例
— 解决方案：
— 5.1 检查辅助目录权限
— ls -la /u01/app/oracle/auxiliary/

— 5.2 检查辅助目录空间
— df -h /u01/app/oracle/auxiliary/

— 5.3 检查RMAN配置
RMAN> SHOW ALL;– 5.4 手动指定辅助实例参数
RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-06-01 10:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’ USING ‘DB_NAME=aux1’;– 6. 归档日志缺失
— 问题：恢复过程中缺少归档日志
— 解决方案：
— 6.1 检查归档日志是否存在
SELECT * FROM v$archived_log WHERE sequence# BETWEEN start_seq AND end_seq;– 6.2 从备份中恢复归档日志
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE start_seq UNTIL SEQUENCE end_seq;– 6.3 如果归档日志无法恢复，考虑使用更早的备份
RMAN> RECOVER TABLESPACE EXAMPLE UNTIL TIME ‘2023-05-31 23:00:00’ AUXILIARY DESTINATION ‘/u01/app/oracle/auxiliary’;