***************************** 备份与恢复 (用户管理 与 RMAN) ********************************
一、前置步骤
1.设置归档
a. shutdown immediate;
b. startup mount;
c. alter database archivelog; (禁用归档:alter database noarchivelog; )
d. alter database open;
校验:archive log list;
select log_mode from v$database;
手工归档:alter system archive log current; (非常规)
切换日志:alter system switch logfile;
设置归档进程数量:alter system set log_archive_max_process=6;
归档路径(31路)及状态参数:
(默认位置:$ORACLE_HOME/dbs | $ORACLE_HOME\database || FRA
log_archive_dest(_1-10)='location=/01/backup' mandatory(optional) reopen=600
log_archive_dest_state(_1-10)= enable or defer (对应归档路径可用与否)
log_archive_format='%t_%s_%r.arc' (scope=spfile 重启生效,先备份spfile
%t:线程号 %s:日志序列号 %r:Resetlogs ID)
log_archive_min_succeed_dest=1 (如大于1,则多路任一路无法写入,则挂起)
查询归档存储位置状态:select dest_name,destination,status,binding from v$archive_dest;
2.相关查询
a. 文件位置:
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$archived_log
union
select name from v$tempfile;
b. 查看热备份状态
select distinct a.FILE#, c.NAME, a.STATUS, a.CHANGE#, a.TIME
from v$backup a, v$datafile b, v$tablespace c
where a.FILE# = b.FILE#
and b.TS# = c.TS#
order by a.FILE#;
c. 查看闪回区使用情况及跟踪诊断
select * from v$flash_recovery_area_usage;
select * from v$spparameter where name like 'diagnostic_dest'
d. 查看快速闪回区位置和尺寸参数(设置值则启用)
db_recovery_file_dest / db_recovery_file_dest_size
e. 检查点查询 (前三记录控制文件中)
系统检查点:select checkpoint_change# from v$database;
数据文件检查点:select file#,checkpoint_change# from v$datafile;
结束检查点: select file#,last_change# from v$datafile;
数据文件头部检查点: select name,checkpoint_change# from v$datafile_header;
3.备份相关参数:
control_file_record_keep_time
******************************************************************************************************
******************************************************************************************************
二、用户管理方法
1. 冷备份和恢复
参考脚本:备份 coldbackup.txt
说明:如不用备份集中的重做日志,需要执行如下命令:
recover database until cancel;
alter database open resetlogs;
2. 热备份和恢复
参考脚本: 无
【备份步骤】:
a.归档:
SQL>archive log list;
b.备份文件和大小
SQL>select name from v$datafile;
SQL>select tablespace_name,file_name from dba_data_files order by 1,2; (12c不准确)
SQL>select sum(bytes)/1024/1204 from dba_data_files; (12c不准确)
c.日志序列号
SQL>select thread#,max(sequence#) from v$log group by thread# order by thread#;
d.开始备份模式
SQL>alter database begin backup; (database)
SQL>alter tablespace <tablespace_name> begin backup; (tablespace)
e.复制文件 (cp -rf)
f.退出备份模式
SQL>alter database end backup; (database)
SQL>alter tablespace <tablespace_name> end backup; (tablespace)
g.归档当前重做日志,再记日志序列号
SQL>alter system archive log current;
SQL>select thread#,max(sequence#) from v$log group by thread# order by thread#;
h.备份控制文件
SQL>alter database backup controlfile to '/bak/control.ctl.bak' reuse;
i.备份所有归档日志(select resetlogs_id from v$archived_log)(d-g的归档日志ID)
j.备份参数文件、密码文件
【恢复步骤】:
a.查询,select * from v$recover_file; (需恢复的文件)
select * from v$recovery_log; (需用到的日志)
select file#,checkpoint_change#,last_change# from v$datafile;(数据文件SCN)
b.设置自动搜索归档日志: set autorecovery on;
(1).完全恢复
情况1,数据库打开,用户表空间损坏:
1.将表空间设置为offline状态 |alter tablespace ecs_ts offline immediate;
2.还原备份(复制黏贴) |cp
3.查询需恢复的文件和需用到的日志 (参考) |v$recover_file; v$recovery_log;
4.恢复表空间 |recover automatic tablespace ecs_ts;
5.将表空间设置online |alter tablespace ecs_ts online immediate;
情况2,数据库关闭,系统表空间损坏
1.将数据库启动到mount | startup mount;
2.还原备份(系统表空间文件)| cp
3.恢复数据库 | recover automatic database;
(或只恢复文件 | recover automatic datafile 1)
4.打开数据库:alter database open;
情况3,数据库关闭,用户表空间损坏 (可以先打开数据库)
1.将数据库启动到mount状态 |startup mount。
2.将丢失数据文件设置成离线 |alter database datafile 4 offline.
3.打开数据库 |alter database open.
4.还原数据文件 |cp
5.恢复数据文件 |recover automatic datafile 4;
6.将数据文件设置成online状态 |alter database datafile 4 online;
情况4,恢复控制文件
1.关闭数据库 |shutdown abort
2.复制控制文件(多路复制) |cp
3.启动mount |startup mount
4.恢复 |recover database using backup controlfile;
5.应用联机重做日志 |recover database using backup controlfile; < redo file
6.resetlogs打开数据库 |alter database open resetlogs;
(2).不完全恢复
1.关闭数据库: |shutdown abort
2.从备份中还原所有数据文件 |cp
3.将数据库启动到mount状态 |startup mount
4.选择恢复:a.基于时间点: |recover database until time 'yyyy-mm-dd hh24:mi:ss';
b.基于撤销: |recover database until cancel;
c.基于SCN号: |recover database until cancel scn 33333;
5.打开数据库: |alter database open resetlogs;
6.全备
********************************************************************************************************
********************************************************************************************************
三、RMAN方法
最简RMAN备份与恢复:
$ rman target /
RMAN> backup database;
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
----RMAN> run { }
1. 配置RMAN备份策略(configure | show all)
(1)运行方式:
备份用户和sys用户:sysdba权限.
本地(推荐): RMAN target /
远程: RMAN target sys/passwd@remote_db
(2)设置归档位置和格式(默认dbs,FRA,参数指定), 参考【设置归档】
(3)备份集存储位置和格式:
默认位置dbs--FRA--(backup..format)--->如下:
RMAN>configure device type disk parallelism 2;
RMAN>configure channel 1 device type disk format '/u01/rman1_%U.bk';
RMAN>configure channel 2 device type disk format '/u02/rman2_%U.bk';
释放通道:RMAN>configure channel 3 device type disk clear;
(4)自动备份控制文件:
查看:RMAN>show controlfile autobackup;
启动:RMAN>configure controlfile autobackup on;
禁用:RMAN>configure controlfile autobackup off;
(5)控制文件备份位置:(默认位置dbs--FRA--->如下:
RMAN>configure controlfile autobackup format for device type disk to '/u01/rman/rman_ctl_%F.bk';
改为默认存储:RMAN>configure controlfile autobackup format for device type disk clear;
(6)备份归档: RMAN>backup database plus archivelog;
(7)控制文件快照设置
查询:RMAN>show snapshot contorlfile name;
修改:configure snapshot controlfile name to '/u01/rman/rman_ctl_%F.bk';
(8)使用恢复目录 catalog
(9)使用介质管理器
(10)控制文件元数据天数:
SQL>show parameter control_file_record_keep_time
(11)保留策略
a.恢复时间窗: RMAN>configure retention policy to recover window of 5 days;
b.冗余: RMAN>configure retention policy to redundancy 2;
(12)删除备份(依据保留策略)
RMAN>report obsolete;
RMAN>delete obsolete;
RMAN>delete noprompt obsolete;
(13)清理保留策略
RMAN>configure retention policy to none; (不标记废弃)
RMAN>configure retention policy clear; (重合默认值)
(14)归档删除策略
RMAN>configure archivelog deletion policy to backed 2 times to device type disk;
RMAN>delete archivelog all;
RMAN>show archivelog deletion policy;
RMAN>configure archivelog deletion policy clear;
(15)镜像还是备份集
as copy | as backupset
(16)压缩
查看: RMAN>show compression algorithm;
备份压缩: RMAN> backup as compressed backupset database;
配置压缩:RMAN> configure device type disk backup type to compressed backupset;
配置释放:RMAN> configure device type disk clear;
配置算法:RMAN>configure compressed algorithm 'HIGH' |'MEDIUM'| 'LOW' ;
算法释放:RMAN>configure compressed algorithm clear;
2. 相关参数设置
export NLS_DATE_FORMAT= 'YYY-MM-DD HH24:MI:SS';
3. 备份 :
参考脚本: rman_backup.txt
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69915315/viewspace-2640143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/69915315/viewspace-2640143/