1.归档模式
#查看归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Current log sequence 7
#归档相关参数
show parameter log_archive_start #从10G开始,废弃此参数,9i如果为False,归档模式也不自动归档。
show parameter log_archive_format
show parameter log_archive_dest
#修改归档模步骤:
#修改参数
shutdown immediate; # 以immediate方式关闭数据库
startup mount; # 启动到mount状态
alter database archivelog; # 更改运行模式 #停止归档: alter database noarchivelog;
alter database open; # 并启动数据库
archive log list
-----------------------------------------
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
2.闪回
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 10G #两个参数都是动态参数
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
# 闪回
1.可以放闪回区的文件有:控制文件、归档的日志文件、闪回日志、控制文件和SPFILE的自动备份、RMAN备份集、数据文件COPY。
2.闪回使用85%,97%时,alert文件会有告警信息;闪回恢复区空间不足,数据库会挂起,报错为ORA-19815。
处理方法:· 手工删除归档日志
· RMAN中 crosscheck archivelog all;
· RMAN中 delete expired archivelog all;
· select name, space_limit, space_used from v$recovery_file_dest; #检查
#修改闪回大小
alter system set db_recovery_file_dest_size=20G scope=both;
3.RMAN备份
1.RMAN备份信息默认存放于控制文件中,可以配置RMAN备份信息存放于目录数据库中(Catalog Database)。
2.备份策略有:基于冗余 - 每个文件保留n份
基于恢复窗口 - 定义备份保留时间(备份超过保留时间,备份被标记为废弃obsolete,但不自动删除,需要执行delete obsolete 才会删除,查看使用 report obsolete)
3.使用NOCATALOG进行RMAN备份,备份完成后,本次备份信息写入控制文件。最后自动备份控制文件和spfile。
4.控制文件记录了数据库的数据文件、日志文件、备份信息,以及数据库的检查点和SCN,对RMAN恢复至关重要。
#RMAN常用命令
RMAN> show all;
RMAN> configure retention policy to renundancy 2; #设置备份文件保留两份
RMAN> report obsolete; #显示废弃陈旧备份
RMAN> delete obsolete; #删除废弃陈旧备份
RMAN> delete nopromt obsolete; #删除废弃,无需确认立即删除。
RMAN> crosscheck backup; #手工删除的备份,删除信息不会体现至数据库,crosscheck后会标记为expired
RMAN> delete expired backup; #删除expired陈旧
RMAN> crosscheck archivelog all; #手工删除归档日志后,crosscheck验证,标记为expired
RMAN> delete noprompt expired archivelog all; #删除expired归档日志
RMAN> delete expired copy; # 删除 EXPIRED 副本
RMAN> delete backupset 19; # 删除特定备份集
RMAN> delete backuppiece 'd:\backup\DEMO_19.bak'; # 删除特定备份片
RMAN> delete backup; # 删除所有备份集
RMAN> delete datafilecopy 'd:\backup\DEMO_19.bak'; #删除特定映像副本
RMAN> delete copy; # 删除所有映像副本
RMAN> delete archivelog all delete input; # 在备份后删除输入对象
RMAN> list backup;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST COPY OF TABLESPACE 'SYSTEM';
RMAN> LIST BACKUP OF DATAFILE'/ORADATA/user01.dbf';
RMAN> list backup summary;
RMAN> report schema;
RMAN> backup as copy database;
RMAN> backup as copy current controlfile format '/backup/control01.ctl';
RMAN> backup datafile1;
RMAN> backup as copy datafile 1;
RMAN> copy datafile 1 to '/backup/datafile1.dbf'; #RMAN镜像拷贝
RMAN> list copy; #查看镜像copy列表
RMAN> restore spfile to '/backup/spfile.ora' from autobackup; #恢复spfile 未momount无法从auto备份恢复spfile
RMAN> restore controlfile to '/backup/control01.ctl' from autobackup; #恢复控制文件 未mount无法重从auto备份恢复控制文件
RMAN> restore controlfile to '/home/oracle/control01.ctl' from '/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2020_04_25/o1_mf_s_1038673028_hb7wj4o9_.bkp'
#RMAN 备份相关的动态性能表
V$ARCHIVED_LOG: 本视图包含了所有归档重做日志文件的创建情况,备份情况以及其他信息。
V$BACKUP_CORRUPTION: 这个视图显示了RMAN 在哪些备份集中发现了损坏的数据坏。在你使用BACKUP VALIDATE 命令对备份集进行检查时如果发现了损坏的数据块,RMAN 将在这个视图中写入记录。
V$COPY_CORRUPTIO: 本视图显示了哪些镜像复制备份文件已经被损坏。
V$BACKUP_DATAFILE: 本视图通常用来获取每个数据文件中非空白数据块的数量,从而帮助你创建出大小基本相等的备份集。另外,在视图中也包含了数据文件中损坏的数据块的信息。
V$BACKUP_REDOLOG: 本视图显示了在现有的备份集中已经有哪些归档重做日志文件。
V$BACKUP_SET: 本视图显示了已经创建的备份集的信息。
V$BACKUP_PIECE: 本视图显示了已经创建的备份片段的信息。
v$rman_configuration;
v$backup_spfile;
#RMAN设置
RMAN> configure device type disk backup type to compressed backupset; #设置默认压缩备份
#rman备份
RMAN> RUN {
crosscheck archivelog all ;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/bakup/%F";
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
BACKUP as compressed backupset database format "/bakup/full_%d_%T_%s.bak" plus archivelog;
release channel c1;
release channel c2;
}
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUPSET;
delete noprompt archivelog all completed before 'sysdate-10';
delete noprompt expired backup;
备注:backup full tag 'back200000302' as compressed backupset database include current controlfile format "/home/oracle/full_%d_%T_%s.bak" plus archivelog format "/home/oracle/arch_%d_%T_%s.bak" delete all input ;
#使用备份控制文件进行不完全恢复
RMAN> restore database
RMAN> recover database using backup controlfile until cancel
SQL> alter database open restlogs
#基于SCN和基于时间点的不完全恢复
RMAN> run {
set until scn 18888888; #基于scn #基于时间点 set until time '2000-01-02 11:00:00'
restore database;
recover database;
}
SQL> alter database open restlogs;
RMAN> restore database;
RMAN> recover database until scn 18888888;
RMAN> recover database until time '2000-01-02 11:00:00';
SQL> alter database open restlogs;
#通过setname指定恢复数据文件路径
RMAN> run {
set newname for datafile 1 to '/u03/oradata/use01.dbf'
restore datafile 1;
switch datafile all;
recover datafile 1;
}
SQL> alter database open;
#基于数据块的恢复
[oracle@server pdb1]$ dbv file=system01.dbf #dbv检查是否有坏块
------------------------------------------------------------------------------------
DBVERIFY: Release 19.0.0.0.0 - Production on Sat Apr 25 17:44:43 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/CDB1/pdb1/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 34560
Total Pages Processed (Data) : 17434
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8682
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2120
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 6324
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2240255 (0.2240255)
RMAN> backup validate datafile 1; #使用RMAN验证数据库文件,以发现坏块
-------------------------------------------------------------------------------
Starting backup at 25-APR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 16818 115210 2241042
File Name: /u01/app/oracle/oradata/CDB1/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 79634
Index 0 12706
Other 0 6042
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 1142
Finished backup at 25-APR-20
SQL> select * from v$database_block_corruption where file# = 1; #验证完成之后愉快的查一查
RMAN> blockrecover datafile 1 block 11 from backupset; #基于数据块级别的恢复
SQL> select * from v$database_block_corruption where file# = 1; #就看一下一下是否修复好
RMAN> backup validate datafile 1; #使用RMAN验证数据库文件,刷新一下 block corruption 信息
SQL> select * from v$database_block_corruption where file# = 1; #检查是否修复
#异机恢复
RMAN> catlog start with '/backup'
RMAN> report schema;
RMAN> run {
set newname for datafile 1 to '/u03/oradata/use01.dbf';
......
set newname for datafile n to '/u03/oradata/use0n.dbf';
restore database;
switch datafile all;
}
RMAN> recover database;
RMAN> alter database open resetlogs;
4.其他
# 运行脚本(又能细分为几种方式如下:
1. [oracle@oracle ~]$ rman TARGET / @backup_db.rman
2. RMAN> @backup_db.rman
3. RMAN> RUN { @backup_db.rman } #运行存储在恢复目录中的脚本(需要首先为rman 创建恢复目录)
4. RMAN> RUN { EXECUTE SCRIPT backup_whole_db };
5.操作系统脚本(linux,windows 环境下小有差异,但只是操作系统命令的不同)
[oracle@oracle ~]$rman cmdfile=backup_db.rman
#收藏的命令
# list
RMAN> list incarnation;
RMAN> list backup by file; #按备份类型列出备份 Datafile-->Archivelog-->Control File-->Spfile
RMAN> list backup; #列出详细备份
RMAN> list expired backup; #列出过期的备份
RMAN> list backup of tablespace tbs1;#列出表空间的备份
RMAN> list backup of datafile 1; #列出数据文件的备份
RMAN> list archivelog all;#列出归档日志信息
RMAN> list backup of archivelog all; #列出归档日志的备份信息
RMAN> list backup of controlfile; #列出控制文件的备份信息
RMAN> list backup of spfile;#列出spfile的备份信息
# report
RMAN> report need backup days=3; #报告最近没有被备份的数据文件
RMAN> report need backup redundancy=2; #报告冗余备份,即列出少于两个冗余备份的数据文件信息
RMAN> report need backup recovery window of 1 days; #列出1天内没有被备份的数据文件信息
RMAN> report unrecoverable; #报告数据文件的不可恢复操作,即列出没有备份的数据文件信息
RMAN> report schema; #报告目标数据库物理结果,显示出表空间(包括临时表空间)即对应数据文件信息
RMAN> report obsolete; #报告丢弃的备份,如果使用了保存策略,备份会被标记为丢弃状态
RMAN> delete obsolete; #删除丢弃状态的备份
# crosscheck
RMAN> crosscheck backup;
RMAN> crosscheck backup of datafile 1;
RMAN> crosscheck backup of tablespace users;
RMAN> crosscheck backup of controlfile;
RMAN> crosscheck backup tag='TAG20161120T121243';
RMAN> crosscheck backup completed after 'sysdate-2';
RMAN> crosscheck backup completed between 'sysdate-5' and 'sysdate-2';
RMAN> crosscheck archivelog all;
RMAN> crosscheck archivelog like 'o1_mf_1_6%';
RMAN> crosscheck archivelog from sequence 5;
RMAN> crosscheck archivelog until sequence 8;
# delete
RMAN> report obsolete;
RMAN> delete obsolete; #删除陈旧备份
RMAN> delete backupset 3; #删除指定编号的备份集,noprompt参数表示直接删除,不提示输入yes或no
RMAN> delete backuppiece '/home/oracle/backup/DEMO.bak'; #删除指定的备份片
RMAN> delete backup; #删除所有的备份
RMAN> delete copy; #删除映像副本
RMAN> delete datafilecopy '/home/oracle/backup/demo.bak'; #删除指定映像副本
RMAN> delete datafilecopy 1;
RMAN> delete backupset 1,2,3; #删除多个备份集,用逗号隔开
RMAN> delete archivelog all; #删除所有归档
RMAN> delete archivelog until time 'sysdate-1'; #删除当前之前的归档
收藏:
https://www.cnblogs.com/hllnj2008/p/4075751.html