【测试】普通表空间的热备及模拟故障恢复

①创建目录：

[oracle@host02 ~]$ mkdir hot_bk

②先查看归档是否打开（热备时一定要打开）

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     93

Current log sequence           96

现在开启归档（mount状态下）：

SQL> alter database archivelog;

Database altered.


SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     93

Next log sequence to archive   96

Current log sequence           96

③查看数据库中表空间文件：

SQL> select file_name,tablespace_name from dba_data_files;

 

FILE_NAME                                          TABLESPACE

-------------------------------------------------- ----------

/u01/app/oracle/oradata/PROD/system01.dbf          SYSTEM

/u01/app/oracle/oradata/PROD/sysaux01.dbf          SYSAUX

/u01/app/oracle/oradata/PROD/undotbs01.dbf         UNDOTBS1

/u01/app/oracle/oradata/PROD/users01.dbf           USERS

/u01/app/oracle/oradata/PROD/test101.dbf           TEST1

/u01/app/oracle/oradata/PROD/test102.dbf           TOOL

/u01/app/oracle/oradata/PROD/user02.dbf            USERS

 

7 rows selected.

④开启users表空间热备：

SQL> alter tablespace USERS begin backup;

Tablespace altered.

开始备份：

SQL> !cp /u01/app/oracle/oradata/PROD/user02.dbf /home/oracle/hot_bk

查看备份文件和备份信息：

[oracle@host02 ~]$ cd /home/oracle/hot_bk/

[oracle@host02 hot_bk]$ ls

user02.dbf

SQL> select * from v$backup;

 

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 ACTIVE 373801 27-SEP-16

6 NOT ACTIVE 0

7 ACTIVE 373801 27-SEP-16

⑤关闭备份

SQL> alter tablespace users end backup;

 

Tablespace altered.

再次查看备份信息：

SQL> select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE             373801 27-SEP-16

         6 NOT ACTIVE                  0

         7 NOT ACTIVE             373801 27-SEP-16

 

6 rows selected.

⑥模拟故障，删除users表空间：

[oracle@host02 hot_bk]$ cd /u01/app/oracle/oradata/PROD

[oracle@host02 PROD]$ ls

initPROD.ora      redo03a.log   temp_01.dbf  test102.dbf

ora_control1.ctl  redo04a.log   temp01.dbf   undotbs01.dbf

redo01a.log       sysaux01.dbf  temp_02.dbf  user02.dbf

redo02a.log       system01.dbf  test101.dbf  users01.dbf

[oracle@host02 PROD]$ rm user02.dbf

⑦现在开始进行恢复：

首先将要恢复的表空间处于脱机状态：

SQL> alter database datafile '/u01/app/oracle/oradata/PROD/user02.dbf' offline;

 

Database altered.

开始恢复数据文件：

[oracle@host02 PROD]$ cd /home/oracle/hot_bk/

[oracle@host02 hot_bk]$ ls

user02.dbf

[oracle@host02 hot_bk]$ cp user02.dbf /u01/app/oracle/oradata/PROD

[oracle@host02 hot_bk]$ cd /u01/app/oracle/oradata/PROD

[oracle@host02 PROD]$ ls

initPROD.ora      redo03a.log   temp_01.dbf  test102.dbf

ora_control1.ctl  redo04a.log   temp01.dbf   undotbs01.dbf

redo01a.log       sysaux01.dbf  temp_02.dbf  user02.dbf

redo02a.log       system01.dbf  test101.dbf  users01.dbf

用recover命令进行介质恢复：

SQL> recover datafile 7;

Media recovery complete.

⑧将表空间改成联机状态：

SQL> alter database datafile '/u01/app/oracl2016-10-12e/oradata/PROD/users01.dbf'

  2  online;

 

Database altered.

恢复完毕。