Oracle归档模式下恢复-recover命令及相关数据字典
发布时间：2020-12-12 14:25:25 所属栏目：百科 来源：网络整理
导读：Oracle归档模式下恢复-recover命令及相关数据字典 3个recover命令： 1、 recover[automatic] database; 数据库mount状态 2、recover[automatic] database "表空间号“|“表空间名"； 数据库open状态 3、recover[automatic] datafile"数据文件名"|“数据文件
|
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:ORACELBASEORADATAORCLUSERS01.DBF USERS 3 C:ORACELBASEORADATAORCLUNDOTBS01.DBF UNDOTBS1 2 C:ORACELBASEORADATAORCLSYSAUX01.DBF SYSAUX 1 C:ORACELBASEORADATAORCLSYSTEM01.DBF SYSTEM 5 C:ORACELBASEORADATAORCLEXAMPLE01.DBF EXAMPLE 6 C:ORCL1122DISK5LIANXI01.DBF LIANXI 7 C:ORCL1122DISK5LIANXI02.DBF LIANXI 8 C:ORCL1122DISK8PIONEER_DATA.DBF PIONEER_DATA 9 C:ORCL1122DISK5LIANXI_INDEX.DBF LIANXI_INDEX 10 C:ORCL1122DISK5LIANXI_INDEX02.DBF LIANXI_INDEX 11 C:ORCL1122DISK8PIONEER_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:ORCL1122DISK5LIANXI02.DBF' 7、恢复： SQL> recover datafile 7; 完成介质恢复。 8、联机： SQL> alter database datafile 7 online; Database altered （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |