oracle12c备份恢复策略

一、RMAN全备份

#删除不可用的备份：

crosscheck backup;

delete noprompt expired backup;

 

#删除并过期备份

report obsolete; ##报告备份是否过期，备份是否过期，要根据rman指定的备份策略来衡量，如果未指定备份策略，则此条件不成立

delete noprompt obsolete;

 

#在数据量比较小、或者数据库服务器性能很强大的情况下，可以每天进行一次全备份。

#全被策略如下

1、crontab定时任务，避开业务繁忙时段

39 11 * * * su - oracle -c "/home/oracle/scripts/rman/rman_full.sh" 2>&1

 

2、RMAN备份脚本

mkdir -p /home/oracle/scripts/rman/log

mkdir -p /home/oracle/backup/rman/full

 

vi /home/oracle/scripts/rman/rman_full.sh

 

source /home/oracle/.bash_profile

DAY_TAG=`date +"%Y-%m-%d"`

backdir1=/home/oracle/backup/rman/full

rm -rf $backdir1/*

rman target / nocatalog msglog /home/oracle/scripts/rman/log/fullback_$DAY_TAG.log<<EOF

run{

crosscheck backup;

delete noprompt expired backup;

allocate channel d1 type disk maxpiecesize = 3500M; ----由于备份后，要把备份文件放到阿里云的OSS存储上面，但是OSS存储有4GB大小限制，因此，我们也要

allocate channel d2 type disk maxpiecesize = 3500M; ----限制RMAN备份片的大小。

#crosscheck archivelog all;

#delete noprompt expired archivelog all;

#crosscheck backup;

#delete noprompt expired backup;

backup as compressed backupset database format '$backdir1/full_%d_%T_%t_%s_%p';

sql 'alter system archive log current';

backup as compressed backupset filesperset 10 format '$backdir1/arch_%d_%T_%t_%s_%p' archivelog all;

backup current controlfile format '$backdir1/ctl_%d_%T_%t_%s_%p';

release channel d1;

release channel d2;

#report obsolete; ##报告备份是否过期，备份是否过期，要根据rman指定的备份策略来衡量，如果未指定备份策略，则此条件不成立

#delete noprompt obsolete;

}

EOF

 

二、零级全备份和一级增量备份

1、crontab定时任务，避开业务繁忙时段

30 11 * * 0 su - oracle -c "/home/oracle/scripts/rman/rman_0_level_full.sh" 2>&1

30 11 * * 1-6 su - oracle -c "/home/oracle/scripts/rman/rman_1_level_incremental.sh" 2>&1

 

2、备份脚本

1）周末0级全备

vi /home/oracle/scripts/rman/rman_0_level_full.sh

 

source /home/oracle/.bash_profile

DAY_TAG=`date +"%Y-%m-%d"`

week=`date +%w`

backdir1=/home/oracle/backup/rman/"$week"

rm -rf $backdir1/*

rman target / nocatalog msglog /home/oracle/scripts/rman/log/fullback_$DAY_TAG.log<<EOF

run{

crosscheck backup;

delete noprompt expired backup;

allocate channel d1 type disk maxpiecesize = 3500M;

allocate channel d2 type disk maxpiecesize = 3500M;

#crosscheck archivelog all;

#delete noprompt expired archivelog all;

#crosscheck backup;

#delete noprompt expired backup;

backup incremental level 0 as compressed backupset database format '$backdir1/full_%d_%T_%t_%s_%p';

sql 'alter system archive log current';

backup as compressed backupset filesperset 10 format '$backdir1/arch_%d_%T_%t_%s_%p' archivelog all;

backup current controlfile format '$backdir1/ctl_%d_%T_%t_%s_%p';

release channel d1;

release channel d2;

#report obsolete;

#delete noprompt obsolete;

}

EOF

 

2）周一到周六，针对周末的0级全备，每天进行1级增量备份

vi /home/oracle/scripts/rman/rman_1_level_incremental.sh

 

source /home/oracle/.bash_profile

DAY_TAG=`date +"%Y-%m-%d"`

week=`date +%w`

backdir1=/home/oracle/backup/rman/"$week"

rm -rf $backdir1/*

rman target / nocatalog msglog /home/oracle/scripts/rman/log/fullback_$DAY_TAG.log<<EOF

run{

crosscheck backup;

delete noprompt expired backup;

allocate channel d1 type disk maxpiecesize = 3500M;

allocate channel d2 type disk maxpiecesize = 3500M;

#crosscheck archivelog all;

#delete noprompt expired archivelog all;

#crosscheck backup;

#delete noprompt expired backup;

backup incremental level 1 as compressed backupset database format '$backdir1/full_%d_%T_%t_%s_%p';

sql 'alter system archive log current';

backup as compressed backupset filesperset 10 format '$backdir1/arch_%d_%T_%t_%s_%p' archivelog all;

backup current controlfile format '$backdir1/ctl_%d_%T_%t_%s_%p';

release channel d1;

release channel d2;

#report obsolete;

#delete noprompt obsolete;

}

EOF

--------------------------------------------------------------------------------------------

三、expdp逻辑导出

--创建目录diretory

mkdir -p /oracle/backup/exp

chown -R oracle.oinstall /oracle/backup/exp

su - oracle

sqlplus sys/oracle@pdboracle as sysdba

create or replace directory dumpdir as '/oracle/backup/exp';

grant read,write on directory dumpdir to public;

#调用

chmod +x expdp_schema.sh

sh expdp_schema.sh

 

vi expdp_schema.sh

 

export LANG=en_US.UTF-8

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12/db_1

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

SERVICE_NAME=PDBORACLE

SCHEMA_NAME=ZYJ

DIRECTORY_NAME=dumpdir

FILE_DATE=`date '+%Y%m%d'`

SAVED_DAY=2

 

expdp system/oracle@$SERVICE_NAME dumpfile=$SCHEMA_NAME\_$FILE_DATE.dmp directory=$DIRECTORY_NAME schemas=$SCHEMA_NAME logfile=$SCHEMA_NAME.log

 

fcount=`ls /oracle/backup/exp/$SCHEMA_NAME\_*.dmp | wc -l`

while [ $fcount -gt $SAVED_DAY ]

do

delfile=`ls -lt /oracle/backup/exp/$SCHEMA_NAME\_*.dmp | awk '{line = $9} END {print line}'`

rm -f $delfile

fcount=`expr $fcount - 1`

done

--------------------------------------------------------------------------------

--四、rman恢复12c到指定时间点

--restore database后可以加restore archivelog all;

vi /home/oracle/testrman.rman

 

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

set until time='2020-12-07 12:01:04';

restore database;

recover database;

}

 

rman target /

--rman中执行shutdown和startup mount

shutdown immediate;

--若需要恢复controlfile，则先startup nomount;

startup mount;

@/home/oracle/testrman.rman

 

--日志输出如下：恢复脚本中不能加sql 'alter database open restlogs'，去除即可

介质恢复完成, 用时: 00:00:01

在 2020-12-07 14:49:54 完成了 recover

sql 语句: alter database open resetlog

释放的通道: c1

释放的通道: c2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: 位于 12/07/2020 14:49:54 的 default 通道上的 sql 命令失败

RMAN-11003: 在分析/执行 SQL 语句期间失败: alter database open resetlog

ORA-02288: 无效的 OPEN 模式

RMAN> **文件结尾**

 

--rman中执行报错：RMAN-11003: failure during parse/execution of SQL statement: alter database open restlogs

--sql 'alter database open restlogs';

alter database open resetlogs;

quit

 

--验证

sqlplus sys/oracle@pdboracle as sysdba;

select * from zyj.test;

 ---------------------------------------------------------------------

--五、全库恢复

--若选用PROD1为catalog目录库

sqlplus sys/oracle@prod1 as sysdba

create user catalog identified by oracle;

grant connect,recovery_catalog_owner to catalog;

alter user catalog unlimited tablespace onusers;

rman catalog catalog/oracle@prod1

create catalog;

 

--a、先恢复spfile

vi initPROD2.ora

db_name=PROD2

 

sqlplus sys/oracle@prod2 as sysdba

startup nomount;

 

rman target sys/oracle@prod2 catalog catalog/oracle@prod1

 

RMAN> list backup of spfile;

RMAN>restore spfile from '/u01/app/oracle/FRA/PROD2/backupset/2016_11_19/o1_mf_nnsnf_TAG20161119T220145_d30ptbhz_.bkp';

RMAN> list backup of controlfile;

RMAN>restore controlfile from '/u01/app/oracle/FRA/PROD2/backupset/2016_11_19/o1_mf_ncnnf_TAG20161119T220114_d30psdfq_.bkp';

 

--可以在rman中执行 by zhuyj

alter database mount;

 

--恢复数据库,须先恢复controlfile后到mount状态

---或者用(list failure须controlfile存在，然后执行alter database open或alter database open然后执行list failure;)

--list failure;

--advise failure;

--然后执行生成的恢复脚本

restore database;

SQL>recover database using backup controlfile until cancel;

--先输入auto，执行完毕以后。

--再次执行：recover database using backup controlfile until cancel;

--当问你要联机日志文件的日志序列号的时候，输入 cancel

 

alter database open resetlogs;