冷备-物理备份-在线热备

2019-11-23 BoobooWei

[toc]

物理备份-在线热备

上一节课学习了物理备份-离线冷备，在数据库shutdown的情况下，手动复制物理文件到备份目录：

• 口令文件
• 参数文件
• 控制文件
• 数据文件
• 日志文件

今天学习的是在数据库正常提供服务的情况线下，进行的备份。

物理备份-在线热备份：online backup在数据库open状态下进行的备份，需要数据库运行在归档模式下。

操作实践

实践01-检查数据库归档模式

Managing Archived Redo Log Files

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       /u01/app/oracle/product/11.2.0.4/dbs/arch
Oldest online log sequence     7
Current log sequence	       9

思考：初始化配置数据库归档模式的方式是什么？即如何设置初始数据库归档模式？

• 可以在CREATE DATABASE语句中将初始归档模式设置为数据库创建的一部分。

• 建议创建数据库时，使用NOARCHIVELOG也是默认的模式，在创建数据库后，再决定是否更改初始归档模式。

• 如果创建数据库时，指定ARCHIVELOG模式，则必须设置初始化参数，这些参数指定已归档的重做日志文件的目标位置（请参见“ 为归档目标设置初始化参数 ”）。

实践02-开启数据库归档模式

语法：更改数据库的归档模式，请使用ALTER DATABASE带有ARCHIVELOGor NOARCHIVELOG子句的语句。

权限：必须以管理员权限（AS SYSDBA）连接到数据库。

步骤：

1. 关闭数据库实例，备份数据库
2. 启动实例到nomount模式，更改初始化参数文件
3. 启动实例到mount模式，更改数据库的归档模式
4. 启动实例到open模式
5. 关闭数据库实例，备份数据库

更改数据库归档模式将更新控制文件。更改数据库归档模式后，必须备份所有数据库文件和控制文件。以前的任何备份均已在NOARCHIVELOG模式下使用，因此不再可用。

1. 关闭数据库实例，备份数据库

[oracle@oratest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 23 20:06:25 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       /u01/app/oracle/product/11.2.0.4/dbs/arch
Oldest online log sequence     7
Current log sequence	       9
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

备份数据库
--此处省略备份操作，详情见上一节实践内容

2. 启动实例到nomount模式，更改初始化参数文件

修改初始化参数文件 log_archive_dest_1

[oracle@oratest ~]$ mkdir /home/oracle/arc_booboo_dest1

SQL> startup nomount
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size		    2251936 bytes
Variable Size		  171967328 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5132288 bytes

SQL> column name format a30
SQL> column value format a20
SQL> column issys_modifiable format a10
SQL> select name,value,issys_modifiable from v$parameter where name like 'log_archive%';

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_1				    IMMEDIATE
log_archive_dest_2				    IMMEDIATE
log_archive_dest_3				    IMMEDIATE
log_archive_dest_4				    IMMEDIATE
log_archive_dest_5				    IMMEDIATE
log_archive_dest_6				    IMMEDIATE
log_archive_dest_7				    IMMEDIATE
log_archive_dest_8				    IMMEDIATE
log_archive_dest_9				    IMMEDIATE
log_archive_dest_10				    IMMEDIATE
log_archive_dest_11				    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_12				    IMMEDIATE
log_archive_dest_13				    IMMEDIATE
log_archive_dest_14				    IMMEDIATE
log_archive_dest_15				    IMMEDIATE
log_archive_dest_16				    IMMEDIATE
log_archive_dest_17				    IMMEDIATE
log_archive_dest_18				    IMMEDIATE
log_archive_dest_19				    IMMEDIATE
log_archive_dest_20				    IMMEDIATE
log_archive_dest_21				    IMMEDIATE
log_archive_dest_22				    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_23				    IMMEDIATE
log_archive_dest_24				    IMMEDIATE
log_archive_dest_25				    IMMEDIATE
log_archive_dest_26				    IMMEDIATE
log_archive_dest_27				    IMMEDIATE
log_archive_dest_28				    IMMEDIATE
log_archive_dest_29				    IMMEDIATE
log_archive_dest_30				    IMMEDIATE
log_archive_dest_31				    IMMEDIATE
log_archive_dest_state_1       enable		    IMMEDIATE
log_archive_dest_state_2       enable		    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_state_3       enable		    IMMEDIATE
log_archive_dest_state_4       enable		    IMMEDIATE
log_archive_dest_state_5       enable		    IMMEDIATE
log_archive_dest_state_6       enable		    IMMEDIATE
log_archive_dest_state_7       enable		    IMMEDIATE
log_archive_dest_state_8       enable		    IMMEDIATE
log_archive_dest_state_9       enable		    IMMEDIATE
log_archive_dest_state_10      enable		    IMMEDIATE
log_archive_dest_state_11      enable		    IMMEDIATE
log_archive_dest_state_12      enable		    IMMEDIATE
log_archive_dest_state_13      enable		    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_state_14      enable		    IMMEDIATE
log_archive_dest_state_15      enable		    IMMEDIATE
log_archive_dest_state_16      enable		    IMMEDIATE
log_archive_dest_state_17      enable		    IMMEDIATE
log_archive_dest_state_18      enable		    IMMEDIATE
log_archive_dest_state_19      enable		    IMMEDIATE
log_archive_dest_state_20      enable		    IMMEDIATE
log_archive_dest_state_21      enable		    IMMEDIATE
log_archive_dest_state_22      enable		    IMMEDIATE
log_archive_dest_state_23      enable		    IMMEDIATE
log_archive_dest_state_24      enable		    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_dest_state_25      enable		    IMMEDIATE
log_archive_dest_state_26      enable		    IMMEDIATE
log_archive_dest_state_27      enable		    IMMEDIATE
log_archive_dest_state_28      enable		    IMMEDIATE
log_archive_dest_state_29      enable		    IMMEDIATE
log_archive_dest_state_30      enable		    IMMEDIATE
log_archive_dest_state_31      enable		    IMMEDIATE
log_archive_start	       FALSE		    FALSE
log_archive_dest				    IMMEDIATE
log_archive_duplex_dest 			    IMMEDIATE
log_archive_min_succeed_dest   1		    IMMEDIATE

NAME			       VALUE		    ISSYS_MODI
------------------------------ -------------------- ----------
log_archive_trace	       0		    IMMEDIATE
log_archive_config				    IMMEDIATE
log_archive_local_first        TRUE		    IMMEDIATE
log_archive_format	       %t_%s_%r.dbf	    FALSE
log_archive_max_processes      4		    IMMEDIATE

71 rows selected.

SQL> alter system set log_archive_dest_1='location=/home/oracle/arc_booboo_dest1';

System altered.

3. 启动实例到mount模式，更改数据库的归档模式

SQL> alter database  mount
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size		    2251936 bytes
Variable Size		  171967328 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5132288 bytes
Database mounted.

SQL> select name,LOG_MODE from v$database;

NAME	  LOG_MODE
--------- ------------
BOOBOO	  NOARCHIVELOG

SQL> alter database archivelog;

Database altered.

SQL> select name,LOG_MODE from v$database;

NAME	  LOG_MODE
--------- ------------
BOOBOO	  ARCHIVELOG

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /home/oracle/arc_booboo_dest1
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence	       9

4. 启动实例到open模式

SQL> alter database open;
Database altered.
SQL> create pfile from spfile;
File created.
SQL> create pfile from spfile;
File created.

5. 关闭数据库实例，备份数据库

更改数据库归档模式将更新控制文件。更改数据库归档模式后，必须备份所有数据库文件和控制文件。以前的任何备份均已在NOARCHIVELOG模式下使用，因此不再可用。

所以变更归档模式后一定要备份 。

具体备份操作查看上一节课。

实践03-在线热备用户表空间

老师笔记

热备份：(online backup)在数据库open状态下进行的备份，需要数据库运行在归档模式下！！！

archive log list
shut immediate
startup mount
alter database archivelog;
alter database open;

修改存档位置：
mkdir /home/oracle/arc_db01_dest1
alter system set log_archive_dest_1='location=/home/oracle/arc_db01_dest1/';

如何热备份用户表空间
alter tablespace users begin backup;
!cp -v /u01/app/oracle/oradata/db01/users01.dbf /home/oracle/hotbk/
alter tablespace users end backup;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

模拟数据改变：
insert into scott.e01 select * from scott.e01;
commit;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

模拟数据文件丢失：
!rm -f /u01/app/oracle/oradata/db01/users01.dbf

模拟数据改变：
insert into scott.e01 select * from scott.e01;
commit;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

重新启动数据库
startup force

将普通文件offline，打开数据库(offline状态的数据文件open时不会被校验)
SQL> alter database datafile 4 offline;
SQL> alter database open;
查看哪些文件需要恢复
SQL> select * from v$recover_file;

还原数据文件：将备份的文件拷贝回数据库中
cp -v /home/oracle/hotbk/users01.dbf /u01/app/oracle/oradata/db01/users01.dbf

恢复数据文件
SQL> recover datafile 4;

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

恢复成功后将数据文件online，然后校验数据：
SQL> alter database datafile 4 online;

Database altered.

Elapsed: 00:00:00.15
SQL> select count(*) from scott.e01;

  COUNT(*)
----------
     57344
#################################################################################
无备份的还原和恢复: 
1.数据库要归档模式
2.数据文件创建于归档之后

创建实验用的表空间
create tablespace tbs1 datafile '/home/oracle/tbs01.dbf' size 10m;

在新的表空间中创建表，将表空间写满数据
create table scott.t01 tablespace tbs1 as select * from scott.emp;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

模拟数据增长
insert into scott.t01 select * from scott.t01;
commit;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

模拟数据文件丢失
!rm -f /home/oracle/tbs01.dbf

重新启动数据库,发现文件丢失
startup force

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/tbs01.dbf'

将5号文件offline打开数据库
SQL> alter database datafile 5 offline;
SQL> alter database open;

根据控制创建崭新的数据文件
alter database create datafile 5 as '/u01/app/oracle/oradata/db01/tbs01.dbf';

使用日志恢复数据文件
SQL> recover datafile 5;

将数据文件online
SQL> alter database datafile 5 online;

校验数据：
select count(*) from scott.t01;
#################################################################################
热备份全库：
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||
       '!cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)||
       'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files;

切换日志：
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
#################################################################################
不完全恢复：

1.要有全备
2.要确定灾难产生时间点(log miner)
生成挖掘队列：告诉oracle要对哪些日志进行挖掘
exec dbms_logmnr.add_logfile('/home/oracle/arc_db01_dest1/1_39_924710753.dbf',dbms_logmnr.new);
开始挖掘
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
查看挖掘结果
select scn,sql_redo from v$logmnr_contents where seg_name='E01' and seg_owner='SCOTT';

       SCN
----------
SQL_REDO
---------------------------
--------------------
   1032938
drop table scott.e01 purge;

3.在备用节点还原数据文件

4.创建控制文件
create controlfile reuse database db01 resetlogs archivelog
datafile
'/home/oracle/test/system01.dbf',
'/home/oracle/test/sysaux01.dbf',
'/home/oracle/test/tbs01.dbf',
'/home/oracle/test/undotbs01.dbf',
'/home/oracle/test/users01.dbf'
logfile
group 1 '/home/oracle/test/redo01.log' size 50m,
group 2 '/home/oracle/test/redo02.log' size 50m; 

5.恢复数据库到灾难产生时间点
recover database using backup controlfile until change 1032938;

6.拷贝需要的归档日志
scp /home/oracle/arc_db01_dest1/1_{27..35}_924710753.dbf oracle@172.25.5.11:/home/oracle/arc_db01_dest1/

7.使用resetlogs方式打开数据库
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.e01;

  COUNT(*)
----------
     50000