【物理热备】（上）备份恢复普通表空间手工备份恢复

最新推荐文章于 2024-10-20 17:13:34 发布

congmeijiao4803

最新推荐文章于 2024-10-20 17:13:34 发布

阅读量86

点赞数

文章标签：数据库操作系统

物理热备：数据库库打开时备份，要求开启归档模式，物理热备可以备份单个数据文件、表空间及数据文件。
物理热备，分普通表空间（非关键数据文件）备份恢复和系统表空间（关键数据文件）备份恢复。
系统表空间：指的是system、sysaux、undo表空间。

一：普通表空间的热备及模拟故障恢复（方法一之关库恢复）
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

确认已设置快速恢复区
SYS@PROD1>show parameter recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0

2.查看数据库的表空间及对应文件
SYS@PROD1>col tablespace_name for a20;
SYS@PROD1>col file_name for a50;
SYS@PROD1>select TABLESPACE_NAME,FILE_NAME from dba_data_files;

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

6 rows selected.

建立备份文件存放目录
[oracle@host01 files]$ mkdir hot_backup

3.开始表空间备份
SYS@PROD1>alter tablespace EXAMPLE begin backup;

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

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

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

6 rows selected.

5.结束表空间备份
SYS@PROD1>alter tablespace EXAMPLE end backup;

Tablespace altered.

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

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

6 rows selected.

6.删除表空间下的数据文件
[oracle@host01 hot_backup]$ cd /u01/app/oracle/oradata/PROD1/
[oracle@host01 PROD1]$ ls
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
control03.ctl redo01.log redo03.log system01.dbf ts_01.dbf users01.dbf
[oracle@host01 PROD1]$ rm example01.dbf

7.一致性关闭数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

8.启动时提示找不到数据文件
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 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD1/example01.dbf'

9.进行恢复
拷贝文件到oradata下
[oracle@host01 ~]$ cd /home/oracle/files/hot_backup/
[oracle@host01 hot_backup]$ cp example01.dbf /u01/app/oracle/oradata/PROD1/

恢复表空间
SYS@PROD1>recover tablespace example;
Media recovery complete.

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

Database altered.

二：普通表空间的热备及模拟故障恢复（方法二之在线恢复）

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>set linesize 200;
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.备份users表空间
SYS@PROD1>alter tablespace users begin backup;

Tablespace altered.

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

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

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

6 rows selected.

5.结束表空间备份
SYS@PROD1>alter tablespace users end backup;

Tablespace altered.

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

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
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.

6.删除数据文件
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/users01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/users01.dbf
ls: /u01/app/oracle/oradata/PROD1/users01.dbf: No such file or directory

7.恢复数据文件
[oracle@host01 ~]$ cd /home/oracle/files/hot_backup/
[oracle@host01 hot_backup]$ ll
total 360060
-rw-r----- 1 oracle oinstall 363077632 Dec 15 14:59 example01.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 15 15:35 users01.dbf

将数据文件offline
SYS@PROD1>alter database datafile '/u01/app/oracle/oradata/PROD1/users01.dbf' offline;

Database altered.

拷贝文件到oradata下
[oracle@host01 hot_backup]$ cp users01.dbf /u01/app/oracle/oradata/PROD1/

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/users01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf

使用recover命令进行介质恢复
SYS@PROD1>recover datafile 4;
Media recovery complete.

将表空间改为online
SYS@PROD1>alter database datafile '/u01/app/oracle/oradata/PROD1/users01.dbf' online;

Database altered.

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

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