Oracle归档模式下恢复-recover命令及相关数据字典
3个recover命令：
1、 recover[automatic] database;数据库mount状态
2、recover[automatic] database "表空间号“|“表空间名"；
数据库open状态
3、recover[automatic] datafile"数据文件名"|“数据文件号”
相关数据字典 1、查询数据文件文件号、名称、表空间： sql> select file_id,file_name,tablespace_name from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------------------------------------- ------------------------------ 4 C:\ORACEL\BASE\ORADATA\ORCL\USERS01.DBF USERS 3 C:\ORACEL\BASE\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1 2 C:\ORACEL\BASE\ORADATA\ORCL\SYSAUX01.DBF SYSAUX 1 C:\ORACEL\BASE\ORADATA\ORCL\SYSTEM01.DBF SYSTEM 5 C:\ORACEL\BASE\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE 6 C:\ORCL1122\DISK5\LIANXI01.DBF LIANXI 7 C:\ORCL1122\DISK5\LIANXI02.DBF LIANXI 8 C:\ORCL1122\DISK8\PIONEER_DATA.DBF PIONEER_DATA 9 C:\ORCL1122\DISK5\LIANXI_INDEX.DBF LIANXI_INDEX 10 C:\ORCL1122\DISK5\LIANXI_INDEX02.DBF LIANXI_INDEX 11 C:\ORCL1122\DISK8\PIONEER_INDX.DBF PIONEER_INDX 11 rows selected 2、查询所有表空间的状态: sql> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE LIANXI ONLINE LIANXI_TEMP ONLINE LIANXI_INDEX ONLINE PIONEER_DATA ONLINE PIONEER_INDX ONLINE 11 rows selected 3、查询所有数据文件的状态： sql> select file#,status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE 9 ONLINE 10 ONLINE 11 ONLINE 11 rows selected 4、查询恢复的数据文件及恢复从何时开始 sql> select * from v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------ ---------- ----------- 5、查询恢复期间所需的归档日志文件： sql> select * from v$recovery_log; THREAD# SEQUENCE# TIME ARCHIVE_NAME ---------- ---------- ----------- -------------------------------------------------------------------------------- ============================================================= ============================================================= ===========================举例：============================ 1、将表空间pioneer_indx 脱机 sql> alter tablespace pioneer_indx offline; Tablespace altered 2、查看： sql> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE LIANXI ONLINE LIANXI_TEMP ONLINE LIANXI_INDEX ONLINE PIONEER_DATA ONLINE PIONEER_INDX OFFLINE 11 rows selected sql> select file#,status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE 9 ONLINE 10 ONLINE 11 OFFLINE 11 rows selected sql> select * from v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------ ---------- ----------- 11 OFFLINE OFFLINE OFFLINE NORMAL 0 sql> desc v$recovery_log; Name Type Nullable Default Comments ------------ ------------- -------- ------- -------- THREAD# NUMBER Y SEQUENCE# NUMBER Y TIME DATE Y ARCHIVE_NAME VARCHAR2(513) Y 3、联机：在查看 恢复正常 sql> alter tablespace pioneer_indx online; Tablespace altered 查看： sql> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE LIANXI ONLINE LIANXI_TEMP ONLINE LIANXI_INDEX ONLINE PIONEER_DATA ONLINE PIONEER_INDX ONLINE 11 rows selected sql> select file#,status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 ONLINE 8 ONLINE 9 ONLINE 10 ONLINE 11 ONLINE 11 rows selected sql> select * from v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------ ---------- ----------- ============================将文件脱机======================================== 4、将7号文件 脱机 sql> alter database datafile 7 offline; Database altered 5、查看： 文件变成recover状态 sql> select file#,status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 7 RECOVER 8 ONLINE 9 ONLINE 10 ONLINE 11 ONLINE 11 rows selected sql> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE LIANXI ONLINE LIANXI_TEMP ONLINE LIANXI_INDEX ONLINE PIONEER_DATA ONLINE PIONEER_INDX ONLINE 11 rows selected 但是表空间还是联机状态 sql> select * from v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- ------------------ ---------- ----------- 7 OFFLINE OFFLINE 2199594424 2017-9-27 8 sql> select * from v$recovery_log; THREAD# SEQUENCE# TIME ARCHIVE_NAME ---------- ---------- ----------- -------------------------------------------------------------------------------- 6、重新设置联机： 在恢复之前是不能联机的 sql> alter database datafile 7 online; alter database datafile 7 online ORA-01113: 文件 7 需要介质恢复 ORA-01110: 数据文件 7: 'C:\ORCL1122\DISK5\LIANXI02.DBF' 7、恢复： sql> recover datafile 7; 完成介质恢复。 8、联机： sql> alter database datafile 7 online; Database altered 原文链接：https://www.f2er.com/oracle/207547.html
