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