OCP课程37：管理Ⅰ之备份

stonebox · 发表于 2016-2-17 11:04:15

课程目标：

创建一致数据库备份
在不关闭数据库的时候备份数据库
创建增量备份
自动数据库备份
管理备份和查看备份报告
监控快速恢复区

1、备份策略：概览

登录/注册后可看大图

可以使用以下三种方式进行备份：

RMAN：Oracle推荐的方式，最常用的方式
Oracle Secure Backup：安全备份，备份到磁带以及备份操作系统文件
User-managed backups：用户管理的备份，使用DBA写的脚本进行备份，逐步淘汰了

2、安全备份

登录/注册后可看大图

Oracle安全备份用于：

完整的备份解决方案：可以备份整个Oracle环境下的数据库数据以及非数据库数据。
介质管理：提供介质管理层用于RMAN备份数据库到磁带，不需要购买第三方的介质管理产品。
备份网络任何位置的数据：可以备份网络上的数据到第三方存储。

RMAN和安全备份一起提供了一个端到端的备份解决方案。

3、用户管理的备份

登录/注册后可看大图

用户管理的备份一般通过撰写脚本来实现。通过脚本：

查询V$DATATBASE确定需要备份的数据文件和状态
查询V$LOGFILE识别联机重做日志文件
查询V$CONTROLFILE确定需要备份的控制文件
将表空间置于联机备份模式
查询V$BACKUP查看处于联机备份模式的表空间的数据文件
使用操作系统复制命令拷贝数据文件到备份位置
再将表空间置于正常模式

非归档模式下将表空间置于联机备份模式会报错

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 133

Current log sequence 135

SQL> alter tablespace ts01 begin backup;

alter tablespace ts01 begin backup

*

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery not enabled

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 133

Next log sequence to archive 135

Current log sequence 135

SQL> alter tablespace ts01 begin backup;

Tablespace altered.

ASMCMD> cp +DATA/stone/datafile/ts01.dbf_reorg0 /home/grid/ts01.dbf

copying +DATA/stone/datafile/ts01.dbf_reorg0 -> /home/grid/ts01.dbf

SQL> alter tablespace ts01 end backup;

Tablespace altered.

4、术语

登录/注册后可看大图

备份策略包括：

Whole database backup：整个数据库备份，包括数据文件和至少一个控制文件
Partial database backup：部分数据库备份，可以包含一个或者多个表空间以及一个或者多个数据文件，可以包含也可以不包含控制文件

备份类型包括：

Full backup：备份文件中包含数据的所有块
Incremental backup：只备份上次备份后修改过的块。支持两种级别的增量备份（0级和1级），1级增量备份有2种：
- cumulative：备份自0级以来所有的改变
- differential：备份自上次增量备份后的所有的改变（可以是0级也可以是1级）

备份模式：

Offline backup：离线备份也称为冷备份或者一致备份，在数据库没有打开时进行的备份，在备份的时候，数据文件头部的SCN等于控制文件的SCN
Online backup：在线备份也成为热备份或者不一致备份，在数据库打开的时候进行的备份，不保证数据文件和控制文件同步

5、术语

登录/注册后可看大图

备份可以存储为：

Image copies：镜像拷贝，就是数据文件或者归档日志文件的拷贝，只能备份到磁盘，会备份文件中的所有块，不管有没有数据，且会检查是否存在坏块，优势是恢复快，可以直接指定镜像拷贝的位置
Backup sets：备份集，一个或者多个二进制文件的集合，包括一个或者多个数据文件，控制文件，服务器参数文件或者归档日志文件。使用备份集不会备份没有数据的数据块，占用更少的磁盘或者磁带空间，而且还可以进行压缩

6、RMAN

登录/注册后可看大图

RMAN是Oracle提供的一个备份和恢复工具，可以用于备份数据文件，控制文件，归档文件，服务器参数文件，可以进行一致备份，不一致备份，增量备份，全备，整个数据库备份或者部分数据库备份，可以备份到磁盘或者磁带，可以通过EM调用，其他备份软件通过API调用。

7、配置备份设置

登录/注册后可看大图

使用EM进行全局备份设置

登录/注册后可看大图

在设备页面可以设置：

并行度：如果磁盘做了条带化，可以设置为磁盘的数量
备份位置：默认是快速恢复区
备份类型：备份集，压缩备份集或者镜像拷贝

登录/注册后可看大图

在备份集页面可以设置：

备份片最大大小
压缩算法

登录/注册后可看大图

8、配置备份设置

登录/注册后可看大图

在策略页面可以设置：

在每次备份的时候自动备份控制文件和服务器参数文件，一般都要选上
通过跳过未更改的文件 (如已经备份的只读和脱机数据文件) 优化整个数据库备份
启用块更改跟踪, 以提高增量备份的速度
排除在整个数据库备份之外的表空间
指定保留策略，默认保留最近的一次备份，可以指定保留的份数或者保留的天数

登录/注册后可看大图

9、调度备份：策略

登录/注册后可看大图

可以选择“Oracle建议的备份”策略，做一次整个数据库的增量0级备份，后续定期进行增量1级备份。也可以自定义，选择备份对象。

登录/注册后可看大图

10、调度备份：选项

登录/注册后可看大图

选择“调度定制备份”，进入“调度定制备份：选项”页面。

登录/注册后可看大图

可以选择“完全备份“以及“作为增量备份策略的基础”，做一个0级备份。如果已经有0级备份了，则可以选择“增量备份”以及“使用增量备份将磁盘上最新的数据文件副本刷新到当前时间”，可以直接进行联机备份，也可以将数据库切换到mount状态进行脱机备份。可以“同时备份磁盘上的所有归档日志”并“成功备份磁盘上的所有归档日志后将其从磁盘中删除”，并删除过时的备份，以节约磁盘空间。

登录/注册后可看大图

11、调度备份：设置

登录/注册后可看大图

选择是备份到磁盘还是磁带。同时可以查看前面设置的全局备份设置，也可以点击“覆盖默认设置”针对这一次备份单独进行备份设置。

12、调度备份：调度

选择重复调度，到时自动备份。

13、调度备份：复查

14、备份控制文件到跟踪文件

控制文件还有一个额外的备份选项：备份到跟踪文件，包含了重建控制文件所需要的SQL语句。

Oracle建议管理员在数据库物理结构发生变化后（比如增加表空间，数据文件，联机重做日志组）将控制文件备份到跟踪文件。

备份控制文件到跟踪文件可以使用EM也可以使用SQL：

SQL> alter database backup controlfile to trace;

Database altered.

然后在跟踪文件目录下查看最新的跟踪文件，就可以看到重建控制文件的内容了。或者也可以备份到指定的文件：

SQL> alter database backup controlfile to trace as '/home/oracle/stone.ctl';

Database altered.

登录/注册后可看大图

15、管理备份

登录/注册后可看大图

在管理备份页面可以查看备份完成时间，位置以及是否有效。

同时还可以：

Catalog Additional Files：将其他备份文件列入目录，用于恢复
Crosscheck All：全部交叉检查，在手动删除了操作系统上面的备份文件后，执行交叉检查确认目录中的备份记录对应的操作系统文件已经不在了
Delete All Obsolete：根据备份策略，删除所有过时备份
Delete All Expired：根据交叉检查结果，删除所有失效备份

登录/注册后可看大图

16、查看备份报告

登录/注册后可看大图

17、监控快速恢复区

登录/注册后可看大图

要关注快速恢复区的可用空间，如果由于空间不足而不能创建归档日志，数据库就会hang住。

在EM的“可用性”页面的“恢复设置”下：

查看已经使用了多少空间
指定快速恢复区位置
指定快速恢复区大小
配置闪回数据库及闪回保留时间

登录/注册后可看大图

18、使用RMAN命令进行备份

登录/注册后可看大图

启动RMAN，连接到数据库：

[oracle@oracletest1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 29 11:27:20 2015

connected to target database: STONE (DBID=3003597140)

查看并进行备份配置

RMAN> show all;

RMAN configuration parameters for database with db_unique_name STONE are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_stone.f'; # default

RMAN> configure default device type to disk;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

RMAN> delete backup;

RMAN> backup database plus archivelog delete input;

RMAN> delete backup;

RMAN> backup as copy datafile 7;

RMAN> backup copy of datafile 7;

19、相关习题：

（1）You executed the following command to perform a backup of the USERS tablespace:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
ALTER TABLESPACE users BEGIN BACKUP
*
ERROR at line 1:
ORA-01123: cannot start online backup; media recovery not enabled
What could be the reason for this error?
A.  The MTTR Advisor is disabled.
B.  The database is in NOARCHIVELOG mode.
C.  The tablespace is already in backup mode.
D.  The Flash Recovery Area is not configured.

答案：B

（2）You  perform  differential incremental level  1  backups  of your  database  on  each  working  day and level 0 backup on Sunday to tape:
Which two statements are true about differential incremental backups? (Choose two.)
A.  The backup performed on Sundays contains all the blocks that have ever been used in the database
B.  The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup
C.  The backup performed on each working day contains all the blocks that have changed since the last level 0 backup
D.  The backup performed on Monday contains all the blocks that have changed since the level 0 backup and every other working day contains all the blocks that have changed since the level 1 backup

答案：AD

（3）Note the following points describing various utilities in Oracle Database 11g:
1. It enables the high-speed transfer of data from one database to another
2. It provides a complete solution for the backup, restoration and recovery needs of the entire database
3. It enables the loading of data from an external file into an Oracle Database
4. It provides a tape backup management for the Oracle ecosystem
Which point describes Oracle Secure Backup?
A.  1
B.  2
C.  3
D.  4
E.  1,2 and 4
F.  1,2,3, and 4

答案：D

（4）Which two files in the database can be configured for automatic backups by using the autobackup feature in Recovery Manager (RMAN)? (Choose two.)
A.  Data Files
B.  Control Files
C.  Parameter Files
D.  Online Redo Log Files
E.  Server Parameter File

答案：BE

（5）You are managing an Oracle Database 11g database with the following backup strategy:
1. On Sunday, an incremental level 0 tape backup is performed
2. Monday through Saturday, a cumulative incremental level 1 tape backup is performed
Which two statements are true regarding the backups performed? (Choose two.)
A.  The backup performed in step 1 backs up all blocks that have ever been in use in the database
B.  The backup performed in step 2 copies all the blocks changed since the most recent level 0 backup
C.  The backup performed in step 1 backs up all the blocks changed since the most recent level 1 backup
D.  The  backup  performed  in  step  2  backs up  all  blocks  that  have  changed  since  the  most  recent  incremental backup at level 1

答案：AB

（6）View the Exhibit to examine the output for the CROSSCHECK BACKUP command.
Which statement is true about the output of the command?

登录/注册后可看大图

A.  The backup piece is expired as the new backup is available
B.  The backup piece is expired because the operating-system file was not found
C.  The backup piece is expired because the retention period for the backup has expired
D.  The backup piece is expired because the backup set to which it belongs is not complete

答案：B

（7）You want to configure and schedule offline database backups to run automatically.
Which tool or utility would you use to achieve this?
A.  The XML script
B.  The PL/SQL script
C.  The DBMS_SCHEDULER package
D.  Enterprise Manager to schedule the backup

答案：D

（8）You are using Enterprise Manager to schedule backups for your database.
Which type of script would be generated by the backup scheduler?
A.  XML script
B.  PL/SQL script
C.  Operating system script
D.  Recovery Manager (RMAN) script

答案：D

（9）Which  statement  is  true  about  a  whole  consistent  database  backup  on  a  database running in ARCHIVELOG mode?
A.  The backup will consist of used data blocks only.
B.  The database must be shut down to accomplish the backup.
C.  The backup can be accomplished without shutting down the database.
D.  The backup will contain all database files that have never been backed up.

答案：B

（10）You are managing an Oracle Database 11g database. The database is open, and you plan to perform Recovery Manager (RMAN) backups.
Which three statements are true about these backups? (Choose three.)
A.  The backups would be consistent.
B.  The backups would be inconsistent.
C.  The backups would be possible only if the database is running in ARCHIVELOG mode.
D.  The backups would be possible only if the database is running in NOARCHIVELOG mode.
E.  The backups need to be restored and the database has to be recovered in case of a media failure.

答案：BCE

（11）You are working on a database that must be functioning 24 hours a day, 7 days a week. The database is configured in ARCHIVELOG mode.
Which two options do you have for performing user-managed backups? (Choose two.)
A.  You can perform consistent backups only.
B.  You can perform a complete database backup without shutting down the database instance.
C.  You can back up data files only when all data files have the same SCN recorded in the control file.
D.  You can back up only those data files whose headers are frozen by using ALTER TABLESPACE BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP commands.

答案：BD

（12）You are managing an Oracle Database 11g database. You configured the database to run in ARCHIVELOG mode.
Which two statements are true in this scenario? (Choose two.)
A.  You must shut down the database instance to perform the backups.
B.  You must configure the Flash Recovery Area (FRA) for the database.
C.  You can perform complete database backups without closing the database.
D.  All  the  previous  closed  database  backups  including  control  files  become  invalid  after you  configure  the database to ARCHIVELOG mode.

答案：CD

帐号		自动登录	找回密码
密码			注册

[认证考试] OCP课程37：管理Ⅰ之备份