oracle提供的recover命令,Oracle归档模式下恢复-recover命令及相关数据字典

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