User-Managed Backups
手动备份 (冷备, 热备 都支持, 跟 RMAN 没关系)
后边两个是 一致性备份 和 不一致性备份
全备:
可以在数据库打开或关闭时. (备份 control file 和 data file) 如果是关闭时候的备份, 就是一致性备份, In such a backup, all the database file headers are consistent whith the control file, and when restored completely, the database can be opened without any recovery. When the database is open and operational, the datafile headers are not consistent with the control file unless the database is open in read-only mode. When the database is shutdown with the ABORT option this inconsistency persists. Backups of the database in such a state are termed as an inconsistent backup. Inconsistent backups need recovery to bring the database into a consistent state. When databases need to be avaliable 7 days a week, 24 hours a day, you have no option but to use an inconsistent backup, and this can be performed only on databases running in Archivelog mode.
Tablespace Backup:
A tablespace backup is a backup of the datafiles that make up a tablespace. 只能备份archivelog模式, 或者 read-only 或者是 offline-normal 的表空间.
Datafile Backup:
You can make backups of a single datafile if your database is in Archivelog mode. 只能备份archivelog模式, 或者 read-only 或者是 offline-normal 的datafile.
Control file backup:
You can configure RMAN for automatic backups of the control file after a BACKUP or COPY command is issued.
User-managed backup 只要用系统的赋值, 粘贴就可以了, 来进行 restore, 然后保存好 archivelog 文件, 来进行 recovery, (目前我们系统使用这种)
Oracle recommends using RMAN for all backup and recovery opeartions, but supports user-managed backup and recovery methods.
冷备 ( 数据库关闭的情况下, 备份 ) 这跟全备不是一个含义.
一致性全备份: A consistent whole database backup, also known as a closed database backup, it can also include the online redo log files, parameter file, and the password file.
Ensure that the complete pathnames of the files are noted and used appropriately in the backup.
Note: It is not necessary to include the online redo log files as part of a whole database backup, if the database has been shutdown cleanly, by using a normal, transactional, or immediate option.
一致性全备的步骤: (手动)
- shutdown the database
- copy all required files to the backup location
- open the database
最好将 password file 和 parameter file 一起复制了.
目前我使用的每个月 ( 大备份 ) 就是这种. 只有 datafiles control files 是最重要的
1. 确认要备份的东西
2. 关闭数据库(干净的关闭)
3. copy 想要备份的内容
4. startup open
以下是 完成 冷备 脚本 ( SQL 脚本 ) 其实这种也不用备份 online redolog file
-------------------------------------------------------------------
set feedback off heading off verify off trimspool off
set pagesize 0 linesize 200
define dir=’/tmp/wb’
define ws=’tmp/ws.sql’
spool &ws
select ‘! cp ‘ || name || ‘&dir’ from v$datafile orader by 1;
select ‘! cp ‘ || member|| ‘&dir’ from v$logfile orader by 1;
select ‘! cp ‘ || name || ‘&dir’ from v$controlfile orader by 1;
select ‘! cp ‘ || name || ‘&dir’ from v$tempfile orader by 1;
spool off
shutdown immediate
@&ws
startup
-------------------------------------------------------------------
一致性全备的缺点:
- 一致性全备不支持 7*24小时服务, 因为必须要关闭数据库
- copy 数据需要很长时间, 在这个时间里, 数据库是不能被启动的.
- recovery 只能到最后的全备时刻, and lost transactions may have to be entered manually following a recovery operation.
热备 ( 数据库在打开的状态下备份 )
- Perform backups of all the tablespaces or individual datafiles while they are online or offline.
- Backup the control file to a binary file or create a script to re-create the control file.
The online redo log files do not need to be backed up.
这种就是非一致性的备份, 比如你备份一个table, 你刚刚备份完, 此时你备份别的 table, 而有用户此时对刚刚你备份的table进行操作, 这样就不一致了.这种的话, 就不需要备份 online redo log file, 因为有 archive history .
说白了, 就是支持数据库一直在open的状态.
热备: 尽量在数据库活动处于低谷时, 进行.
必须是在 ARCHIVELOG mode 模式, 理由上边已经说了, 热备是 非一致性的备份. 如果不是 archivelog mode的话, 那么到时候会有数据丢失.
常见的是, 以表空间为单位, 备份.
Regardless of the option you choose, the database remains avaliable for normal(transaction) use during the backup process.
When a datafile is placed in backup mode, more redo log entries may be generated because the log writer writes block images of changed blocks of the datafile in backup mode to the redo log instead of just the row information.
This could have a significant impact on the size of redo logs and the performance of the log writer.
备份 tablespace (online) 步骤
1. set the datafile or tablespace in backup mode by issuing ALTER TABLESPACE tablespace_name BEGIN BACKUP
This prevents the sequence number in the datafile header from changing, so that logs are applied in recovery
from backup start time. Even if the datafile is in backup mode, it is available for normal transaction.
2. Use an operating system backup utility to copy all datafiles in the tablespace to backup storage.
cp /oradata/u03/users01.dbf /backup/users01.dbf
3. alter tablespace users end backup;
4. alter system archive log current; ( 归档操作 )
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived.
Repeat these steps for all tablespaces, including SYSTEM and undo segment tablespaces.
The time between the ALTER TABLESPACE BEGIN BACKUP and ALTER TABLESPACE END BACKUP commands should be minimized, because more redo information is generated as a result of modified blocks being written to the redo log files. It is therefore recommended that you perform online backup of one tablespace at a time.
这里的 144,145 就是类似文件头的东西, 就是 SCN
如果不执行 alter tablespace begin backup 而直接将该文件拷贝走, 可以拷贝, 但是将来不能被恢复. 即 表空间进入 backup 模式.
那么 backup 模式究竟做了什么事情呢?
非常牛逼的, 从操作系统下, 将数据库的内容读出来
dd if=example01.dbf ibs=8192 skip=1011 count=2 | strings
解释如下 : dd linux 命令
if: 参数, 表示读那个文件
ibs: 表示以多少为单位, 因为此数据库的db_block 是 8192, 所以此处也是 8192
count=2 表示连续读2块
strings : 2进制读取
其中比较关键的步骤是, 首先你要找到数据在那个块, 另外要知道数据库的一个 data block大小
通过此SQL语句, 可以知道具体数据在数据文件中的哪个 data block
SELECT dbms_rowid.rowid_block_number(rowid) blk, name from fruit;
其中 fruit 只是刚刚创建的新表, 关键还是前面的 dbms 所带的包
begin backup 和 end backup 之间的时间间隔, 应该尽量小
v$backup 这个视图可以看到 究竟有哪些 文件在 backup 状态下. ACTIVE 状态
在 backup 状态下, 比如 断点了, 再重新打开数据库时, 你备份的 tablespace 是有问题的, 无法打开的, 此时, 要进入 V$backup, 就会看到该 tablespace 正处于 backup 的 active 状态, 此时只要把它变成 NOACTIVE 状态就可以了。 alter database datafile 4 end backup ( 或者干脆就 alter database end backup )
如果你修改了 tablespace 为 read only, 那么备份完 tablespace 还要把 constrol file 备份一下, 因为该 constrol file 已经变化了。
Nologging 一般是批量处理的时候, 数量很大.
Nologging 速度快.
备份 control file
- control 是多路复用的, 在参数文件中有文件的位置
- The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command creates a script to re-create the control file, The file is located in the directory specified in the initializaiton parameter USER_DUMP_DEST. This script does not contain RMAN metadata.
冷备
直接 copy 到别的地方.
什么时候备份控制文件 ? 只要控制文件发生变化, 我们就要备份它, 以下命令会改变控制文件.
备份参数文件
直接 copy 备份 init param 就可以了. ( 参数文件 )
DBVERIFY 好像基本不用
检查数据文件块的 ( Using DBVERIFY )
dbv file=system01.dbf feedback=100
