【物理热备】（下）备份恢复系统表空间 手工备份恢复

系统表空间的热备及模拟故障恢复
恢复系统表空间只能关闭数据库进行，系统表空间不能offline

1.检查是否开启归档模式
SYS@PROD1>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

2.查看表空间及对应的数据文件
SYS@PROD1>select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1;

  FILE_ID TABLESPACE_NAME      FILE_NAME
---------- -------------------- --------------------------------------------------
        1 SYSTEM               /u01/app/oracle/oradata/PROD1/system01.dbf
        2 SYSAUX               /u01/app/oracle/oradata/PROD1/sysaux01.dbf
        3 UNDOTBS1             /u01/app/oracle/oradata/PROD1/undotbs01.dbf
        4 USERS                /u01/app/oracle/oradata/PROD1/users01.dbf
        5 EXAMPLE              /u01/app/oracle/oradata/PROD1/example01.dbf
        6 TS_TEST              /u01/app/oracle/oradata/PROD1/ts_01.dbf

6 rows selected.

3.备份系统表空间
SYS@PROD1>alter tablespace system begin backup;

Tablespace altered.

4.拷贝数据文件到备份位置
SYS@PROD1>! cp /u01/app/oracle/oradata/PROD1/system01.dbf /home/oracle/files/hot_backup

查看备份状态
SYS@PROD1>select * from v$backup;

    FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
        1 ACTIVE                1115764 15-DEC-16
        2 NOT ACTIVE                  0
        3 NOT ACTIVE                  0
        4 NOT ACTIVE            1110120 15-DEC-16
        5 NOT ACTIVE            1107830 15-DEC-16
        6 NOT ACTIVE                  0

6 rows selected.

5.结束备份
SYS@PROD1>alter tablespace system end backup;

Tablespace altered.

查看状态，已完成
SYS@PROD1>select * from v$backup;

    FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
        1 NOT ACTIVE            1115764 15-DEC-16
        2 NOT ACTIVE                  0
        3 NOT ACTIVE                  0
        4 NOT ACTIVE            1110120 15-DEC-16
        5 NOT ACTIVE            1107830 15-DEC-16
        6 NOT ACTIVE                  0

6 rows selected.

删除系统表空间数据文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/system01.dbf

系统表空间不能offline
SYS@PROD1>alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline;
alter database datafile '/u01/app/oracle/oradata/PROD1/system01.dbf' offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary


关闭数据库，模拟故障
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1>

启动数据库，提示找不到数据文件
SYS@PROD1>startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             520096848 bytes
Database Buffers          310378496 bytes
Redo Buffers                2371584 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD1/system01.dbf'

SYS@PROD1>! cp /home/oracle/files/hot_backup/system01.dbf /u01/app/oracle/oradata/PROD1/system01.dbf

恢复system表空间
SYS@PROD1>recover datafile 1;
Media recovery complete.

打开数据库
SYS@PROD1>alter database open;

Database altered.

YS@PROD1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME      STATUS
-------------------- ---------
SYSTEM               ONLINE
SYSAUX               ONLINE
UNDOTBS1             ONLINE
TEMP                 ONLINE
USERS                ONLINE
EXAMPLE              ONLINE
TS_TEST              ONLINE

7 rows selected.

SYS@PROD1>select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME      STATUS
-------------------------------------------------- -------------------- ---------
/u01/app/oracle/oradata/PROD1/users01.dbf          USERS                AVAILABLE
/u01/app/oracle/oradata/PROD1/undotbs01.dbf        UNDOTBS1             AVAILABLE
/u01/app/oracle/oradata/PROD1/sysaux01.dbf         SYSAUX               AVAILABLE
/u01/app/oracle/oradata/PROD1/system01.dbf         SYSTEM               AVAILABLE
/u01/app/oracle/oradata/PROD1/example01.dbf        EXAMPLE              AVAILABLE
/u01/app/oracle/oradata/PROD1/ts_01.dbf            TS_TEST              AVAILABLE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2130718/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2130718/