[RMAN] Oracle11G 联机备份与恢复
Oracle11G 联机备份与恢复-----------------------------------
1
设置产品数据库为归档模式
C:\>sqlplus /nologSQL> conn sys/root:192.168.1.222:1521:ORALCE11G as sysdbaConnected.SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 38Next log sequence to archive 40Current log sequence 40SQL> alter database open;
2
设置存储库, 不要将RMAN目标数据库用作存储库. 存储库至少需要125M空间来存放恢复目录条目.
注意:因为我是在个人PC上操作,所以没办法,只能在自己的数据库来创建存储库.
C:\>sqlplus sys/root:192.168.1.222:1521:ORALCE11G as sysdbaSQL> create tablespace RMAN datafile 'D:\APP\ORADATA\ORALCE11G\RMAN01.DBF' size 125M autoextend on next 50M maxsize 500M;SQL> CREATE USER rman IDENTIFIED BY rman;SQL> GRANT CONNECT, RESOURCE to rman;SQL> GRANT recovery_catalog_owner to rman;SQL> alter user rman default tablespace rman quota unlimited on rman;SQL> exit
3
创建恢复目录
C:\>rman catalog rman/rman@ORALCE11GRMAN> create catalog;
4
注册数据库
C:\>rman target sys/root:192.168.1.222:1521@ORALCE11G catalog rman/rman@ORALCE11GRecovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 20 00:31:29 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.connected to target database: ORALCE11 (DBID=3002332376)connected to recovery catalog databaseRMAN> register database;database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
5
配置备份策略
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 days;old RMAN configuration parameters:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;new RMAN configuration parameters:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;new RMAN configuration parameters are successfully storedstarting full resync of recovery catalogfull resync completeRMAN> CONFIGURE BACKUP OPTIMIZATION ON;old RMAN configuration parameters:CONFIGURE BACKUP OPTIMIZATION OFF;new RMAN configuration parameters:CONFIGURE BACKUP OPTIMIZATION ON;new RMAN configuration parameters are successfully storedstarting full resync of recovery catalogfull resync completeRMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;old RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP OFF;new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters are successfully storedstarting full resync of recovery catalogfull resync complete
6
备份数据库
6.1 完全备份
# 修改恢复目录C:\>sqlplus sys/root@ORALCE11G as sysdba...SQL> alter system set db_recovery_file_dest='d:\backup';System altered.SQL> alter system set db_recovery_file_dest_size=4G;System altered.C:\>rman target sys/root@ORALCE11G catalog rman/rman@ORALCE11GRecovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 20 01:33:17 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.connected to target database: ORALCE11 (DBID=3002332376)connected to recovery catalog databaseRMAN> backup as backupset database;Starting backup at 20-APR-12allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=16 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBFinput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBFinput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBFinput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBFinput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBFinput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBFchannel ORA_DISK_1: starting piece 1 at 20-APR-12channel ORA_DISK_1: finished piece 1 at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNNDF_TAG20120420T021651_7S0ONNQG_.BKP tag=TAG20120420T021651 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:45Finished backup at 20-APR-12Starting Control File and SPFILE Autobackup at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781064318_7S0OQZNJ_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 20-APR-12RMAN> sql 'alter system archive log current';sql statement: alter system archive log currentRMAN> list backup by backup;List of Backup Sets===================BS KeyType LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------370 Full 1.23G DISK 00:01:39 20-APR-12 BP Key: 371 Status: AVAILABLECompressed: NOTag: TAG20120420T021651 Piece Name: D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNNDF_TAG20120420T021651_7S0ONNQG_.BKPList of Datafiles in backup set 370File LV Type Ckp SCN Ckp TimeName---- -- ---- ---------- --------- ----1 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF2 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF3 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF4 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\USERS01.DBF5 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF7 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\RMAN01.DBFBS KeyType LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------382 Full 9.36M DISK 00:00:02 20-APR-12 BP Key: 389 Status: AVAILABLECompressed: NOTag: TAG20120420T021838 Piece Name: D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781064318_7S0OQZNJ_.BKPSPFILE Included: Modification time: 20-APR-12SPFILE db_unique_name: ORALCE11GControl File Included: Ckp SCN: 1964431 Ckp time: 20-APR-12RMAN># 可以看到备份集中包括数据文件和SPFILE,其实还包括控制文件.
6.2
镜像备份
RMAN> backup as copy database;Starting backup at 20-APR-12allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=138 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_SYSTEM_7S0PHG0H_.DBF tag=TAG20120420T023109 RECID=4 STAMP=781065117channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_SYSAUX_7S0PK5L2_.DBF tag=TAG20120420T023109 RECID=5 STAMP=781065157channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_RMAN_7S0PL8TC_.DBF tag=TAG20120420T023109 RECID=6 STAMP=781065168channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_UNDOTBS1_7S0PLR3F_.DBF tag=TAG20120420T023109 RECID=7 STAMP=781065183channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_USERS_7S0PM74W_.DBF tag=TAG20120420T023109 RECID=8 STAMP=781065197channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBFoutput file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_EXAMPLE_7S0PMGCQ_.DBF tag=TAG20120420T023109 RECID=9 STAMP=781065204channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07Finished backup at 20-APR-12Starting Control File and SPFILE Autobackup at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065206_7S0PMQLK_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 20-APR-12RMAN>
7
删除备份,包括有效的备份
list backup;delete backup;crosscheck backup;crosscheck archivelog all;delete expired backup;list backup;
8
手动备份控制文件和SPFILE
RMAN> backup current controlfile spfile;Starting backup at 20-APR-12using channel ORA_DISK_1channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 20-APR-12channel ORA_DISK_1: finished piece 1 at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NCNNF_TAG20120420T023551_7S0PR8SD_.BKP tag=TAG20120420T023551 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 20-APR-12channel ORA_DISK_1: finished piece 1 at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNSNF_TAG20120420T023551_7S0PRB0H_.BKP tag=TAG20120420T023551 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 20-APR-12Starting Control File and SPFILE Autobackup at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065356_7S0PRF9R_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 20-APR-12RMAN>
9
备份归档日志文件
backup archivelog all delete all input;
10
增量备份
0级备份:
backup incremental level 0 as compressed backupset database;RMAN> backup incremental level 0 as compressed backupset database;Starting backup at 20-APR-12using channel ORA_DISK_1channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBFinput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBFinput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBFinput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBFinput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBFinput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBFchannel ORA_DISK_1: starting piece 1 at 20-APR-12channel ORA_DISK_1: finished piece 1 at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S0Q0VTC_.BKP tag=TAG20120420T024026 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:35Finished backup at 20-APR-12Starting Control File and SPFILE Autobackup at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065723_7S0Q3WN0_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 20-APR-12
1级累积备份
backup as compressed backupset incremental level 1 cumulative database;MAN> backup as compressed backupset incremental level 1 cumulative database;tarting backup at 20-APR-12sing channel ORA_DISK_1hannel ORA_DISK_1: starting compressed incremental level 1 datafile backup sethannel ORA_DISK_1: specifying datafile(s) in backup setnput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBFnput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBFnput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBFnput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBFnput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBFnput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBFhannel ORA_DISK_1: starting piece 1 at 20-APR-12hannel ORA_DISK_1: finished piece 1 at 20-APR-12iece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S0Q4WFO_.BKP tag=TAG20120420T02423 comment=NONEhannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45inished backup at 20-APR-12tarting Control File and SPFILE Autobackup at 20-APR-12iece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065802_7S0Q6CHN_.BKP comment=NONEinished Control File and SPFILE Autobackup at 20-APR-12
1级差异备份
backup as compressed backupset incremental level 1 database;Starting backup at 20-APR-12using channel ORA_DISK_1channel ORA_DISK_1: starting compressed incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBFinput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBFinput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBFinput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBFinput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBFinput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBFchannel ORA_DISK_1: starting piece 1 at 20-APR-12channel ORA_DISK_1: finished piece 1 at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S0Q86B8_.BKP tag=TAG20120420T024421 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:46Finished backup at 20-APR-12Starting Control File and SPFILE Autobackup at 20-APR-12piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065908_7S0Q9O8S_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 20-APR-12
11
备份验证
backup validate database archivelog all;
12
恢复控制文件
shutdown immediate;startup nomount;restore controlfile from autobackup;recover database until cancel;alter database open resetlogs;
13
恢复表空间
RMAN> sql 'alter tablespace users offline immediate';sql statement: alter tablespace users offline immediateRMAN> restore tablespace users;Starting restore at 20-APR-12using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORADATA\ORALCE11G\USERS01.DBFchannel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S0Q0VTC_.BKPchannel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S0Q0VTC_.BKP tag=TAG20120420T024026channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:15Finished restore at 20-APR-12RMAN> recover tablespace users;Starting recover at 20-APR-12using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00004: D:\APP\ORADATA\ORALCE11G\USERS01.DBFchannel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S0Q4WFO_.BKPchannel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S0Q4WFO_.BKP tag=TAG20120420T024235channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00004: D:\APP\ORADATA\ORALCE11G\USERS01.DBFchannel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S0Q86B8_.BKPchannel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S0Q86B8_.BKP tag=TAG20120420T024421channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 20-APR-12RMAN> sql 'alter tablespace users online';sql statement: alter tablespace users onlinerestore tablespace users validate;RMAN>
14
恢复数据文件
restore datafile 7;recover datafile 7;
15
恢复数据库
restore database;recover database delete archivelog maxsize 2gb;alter database open;
16
编目备份
list backup;cp /USER* d:\backup\USER*catalog datafilecopy 'd:\backup\USERS*';report schema
17
Linux定时调度 crontab
17.1
以 oracle 用户登录, 创建调度计划
su -l oracle
crontab -l
crontab -e
输入并保存:
0 3 * * 6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 0-2,4-6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 3 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
30 6,12,18,23 * * * /home/oracle/oracle/scripts/hot_arc_log_bak.sh
注意: 任务是在 oracle 用户下执行,需要可执行权限.
17.2
使调度生效:
以 root 用户登录
su root
cd /etc/init.d
./crond restart
查看进程,保证crond 进程只有一个,如果有多个,则会执行多次.
ps -ef | grep crond
root 22612 10 09:49 ? 00:00:00 crond
root 22676 224270 09:50 pts/0 00:00:00 grep crond
18. 自动备份脚本
hot_arc_log_bak.sh
#!/bin/sh#################################################### ## hot_arc_log_bak.sh## ##################################################################################################### global define##################################################compatible_echo(){case $SHELL in*/bin/Bash)alias echo="echo -e";;esac}# export envsource $HOME/.bash_profile################################################## config#################################################ORACLE_HOME=/opt/oracle/product/11gexport ORACLE_HOMEPATH=$PATH:$HOME/bin:$ORACLE_HOME/binexport PATHRMAN=$ORACLE_HOME/bin/rmanexport RMAN################################################## backup archive log & control file & spfile#################################################$RMAN target / nocatalog log="$HOME/backup/rman_arc_`date '+%Y%m%d%H%M%S'`.log" <<EOFrun {CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE RETENTION POLICY to recovery window of 7 days;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE disk TO 2;allocate channel c1 type disk format '$HOME/backup/74/arc_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/arc_%d_DBID%I_%T_T%t_%U';sql 'alter system archive log current' ;backup archivelog all tag 'arch' delete all input;release channel c1;allocate channel c2 type disk format '$HOME/backup/74/ctl_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/ctl_%d_DBID%I_%T_T%t_%U';backup current controlfile tag 'ctl';release channel c2;allocate channel c3 type disk format '$HOME/backup/74/spfile_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/spfile_%d_DBID%I_%T_T%t_%U';backup spfile tag='spfile';release channel c3;}list backup;exit;EOF
hot_db_inc_bak.sh
#!/bin/sh#################################################### ## hot_db_inc_bak.sh## #################################################################################################### # global define##################################################compatible_echo(){case $SHELL in*/bin/Bash)alias echo="echo -e";;esac}source $HOME/.bash_profile################################################## # config##################################################ORACLE_HOME=/opt/oracle/product/11gexport ORACLE_HOMEPATH=$PATH:$HOME/bin:$ORACLE_HOME/binexport PATH# backup policyWEEK_DAILY=`date +%a`case"$WEEK_DAILY" in "Mon") BAK_LEVEL=2 ;; "Tue") BAK_LEVEL=2 ;; "Wed") BAK_LEVEL=1 ;; "Thu") BAK_LEVEL=2 ;; "Fri") BAK_LEVEL=2 ;; "Sat") BAK_LEVEL=2 ;; "Sun") BAK_LEVEL=0 ;; "*") BAK_LEVEL=error;;esacexport BAK_LEVEL=$BAK_LEVELRMAN=$ORACLE_HOME/bin/rmanexport RMAN################################################## # backup datafile##################################################$RMAN target / nocatalog log="$HOME/backup/rman_db_`date '+%Y%m%d%H%M%S'`.log" <<EOFrun {CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE RETENTION POLICY to recovery window of 7 days;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE disk TO 2;allocate channel c1 type disk format '$HOME/backup/74/db_inc_lv"$BAK_LEVEL"_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/db_inc_lv"$BAK_LEVEL"_%d_DBID%I_%T_T%t_%U';backup incremental level $BAK_LEVEL filesperset 2 tag 'db_inc_lv"$BAK_LEVEL"' database skip readonly;release channel c1;}list backup summary;report obsolete;delete noprompt obsolete;crosscheck backup;delete noprompt expired backup;list backup summary;exit;EOF
19. 配置NFS
############### 配置服务端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 配置NFS服务端
vi /etc/exports
/var/nfs/rman/backup/74 192.168.0.11(rw,root_squash,sync)
/var/nfs/rman/backup/75 192.168.0.11(rw,root_squash,sync)
# NFS服务配置生效
# SUSE
chkconfig -a nfsserver
/etc/init.d/nfsserver restart
# RedHat
/etc/init.d/nfs restart
# 检查NFS启动
rpcinfo -p localhost | grep nfs
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
############### 配置客户端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 挂载
cd /mnt
mkdir 74 75
chmod 777 74 75
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/74 /mnt/74
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/75 /mnt/75
unmount -l /mnt/74
unmount -l /mnt/75
# 开机启动时自动挂载
vi /etc/fstab
192.168.0.123:/var/nfs/rman/backup/75 /mnt/75 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
192.168.0.123:/var/nfs/rman/backup/74 /mnt/74 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
20.恢复
RMAN 恢复:
恢复控制文件:
shutdown immediate;
startup nomount;
restore controlfile; | restore controlfile from autobackup;
recover database until cancel;
alter database mount;
alter database open; | alter database open resetlogs;
恢复数据库:
shutdown immediate;
startup mount;
restore database;
recover database; | recover database using backup controlfile;
alter database open; | alter database open resetlogs; // 不完全恢复
恢复数据文件:
shutdown immediate;
startup mount;
restore datafile 7;
recover datafile 7;
alter database open;
SPFile恢复:
shutdown immediate;
startup nomount;
set dbid=<DBID>;
restore spfile from autobackup;
shutdown immediate;
startup;
口令文件恢复:
重建口令文件:
orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=admin
windows下oracle默认的位置是$ORACLE_HOME/database目录，文件名格式是pwdSID.ora。
linux下oracle默认的位置是$ORACLE_HOME/dbs目录，文件名格式是orapwSID。
创建完后，数据库需要重启动，新的口令文件才能生效。
完全恢复:
sqlplus /nolog
conn sys/<pwd>@SID as sysdba;
shutdown immediate;
startup nomount;
rman target ／ nocatalog; // 默认且必须是SYSDBA用户
RMAN 中执行SQL为： sql ‘sql’;
// 从备份控制文件恢复
restore controlfile from autobackup;
alter database mount;
restore database;
//由于使用的是备份控制文件，oracle要求使用backup controlfile 的选项来执行恢复
recover database; | recover database using backup controlfile;
// 由于使用的是备份的控制文件，所以oracle要求用resetlogs的方式打开数据库.
alter database open; | alter database open resetlogs;
完全恢复完成.
21
多元复用控制文件
selectname from v$controlfile;
SQL>alter system set control_files='d:\oracle\CONTROL01.CTL''c:\oracle\CONTROL01.CTL'scope=spfile;
SQL>shutdown immediate;
SQL>host copy d:\oracle\CONTROL01.CTLc:\oracle\CONTROL01.CTL;
SQL>startup
当控制文件介质失效时，用于恢复，多元复用控制文件应该保存在不同磁盘上.
22
多元复用重做日志文件
select group#,member from v$logfile;
ALTER DATABASE ADD LOGFILE MEMBER
'F:\ORABAK\REDO01.LOG' TO GROUP 1,
'F:\ORABAK\REDO02.LOG' TO GROUP 2,
'F:\ORABAK\REDO03.LOG' TO GROUP 3;
重做日志默认有三个组.
当日志组中有一个重做日志介质失效时，可以使用备用的重做日志.多元复用重做日志文件应该保存在不同磁盘上，提高可用性.
23
查询当前使用哪个日志组
select * from v$log; // STATUS: INACTIVE | CURRENT
24
重新日志丢失解决方案:
非当前重做日志，可直接清除：
alter database clear logfile group 1;
当前重做日志，恢复：
host copy F:\ORABAK\REDO03.LOG E:\bisoft\oracle\app\oradata\bisoft\REDO03.LOG
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
25
启动闪回配置
SQL> startup mount;ORACLE 例程已经启动。Total System Global Area744910848 bytesFixed Size 1374696 bytesVariable Size 318768664 bytesDatabase Buffers 419430400 bytesRedo Buffers 5337088 bytes数据库装载完毕。SQL> alter database flashback on;数据库已更改。SQL> alter database open;数据库已更改。SQL> select flashback_on from v$database;FLASHBACK_ON------------------------------------YESSQL> show parameter recyclebin;NAME TYPE------------------------------------ ----------------------VALUE------------------------------recyclebin stringonSQL>设置闪回区:SQL> show parameter db_recovery_file_dest;设置闪回日志保留时间:SQL> alter system set db_flashback_retention_target=4320; #3days
注意: Flashback不支持sys用户，system表空间下面的对象，也不能从回收站里拿到。故使用sys或是system用户登录时，show recyclebin为空。
授权:
grant select any table, flashback any table to x;
闪回数据库:
flashback database to scn 2323534;
验证:
alter database open ready only;
验证后：
shutdown immediate；
startup mount;
alter database open resetlogs;
resetlogs是将control file中的scn#与数据文件中的scn#同步，消除时间间隔。
闪回表:
show recyclebin;
flashback table t_name to before drop;
清除回收站:
purge recyclebin
闪回事务:
select xid from v$transaction;
Flash Version Query:
表test:
select versions_starttime, versions_endtime, versions_xid, versions_operation, id
from test versions
between timestamp minvalue and maxvalue
order by versions_starttime;
在上述查询中，列 versions_starttime、versions_endtime、versions_xid、versions_operation是伪列，还有一些伪列，如versions_startscn和versions_endscn显示了该时刻的系统更改号。列versions_xid显示了更改该行的事务标识符。
当然，除了分析以上所有的变更之外，可以根据需要指定时间段，如显示在2005-05-07时间在15:30到16:30之间test表的所有变更。
SQL>select id from test
versions between timestamp to_date(’2005-05-07 15:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(’2005-05-07 16:30:00’,’
yyyy-mm-dd hh24:mi:ss’)
26
查看当前的scn:
select current_scn from v$database;
最早可闪回的SCN:
select * from v$flashback_database_log;
27
rman target / nocatalog
sql 'alter system set NLS_DATE_LANGUAGE=AMERICAN scope=spfile';
sql 'alter system set NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS" scope=spfile';
shutdown immediate;
startup;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:/orabak/autobackup/ctl_%d_%T_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
show all;
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level0_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 0 database tag 'inc0' filesperset 10 database SKIP INACCESSIBLE;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level1_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 1 database tag 'inc1' filesperset 10 duration 2:00 minimize load SKIP INACCESSIBLE ;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level1_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
exit
EOF
# 重建控制文件
alter database backup controlfile to trace as 'e:/orabak/rc.trc';
# 归档日志
RUN{
sql 'alter system archive log current';
allocate channel ch1 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/ctl_level0_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch2;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
基于RMAN的备份优化
1.修改large_pool_size
alter system set large_pool_size=64M scope=spfile;
show parameter large_pool_size;
2.
CONFIGURE BACKUP OPTIMIZATION ON;
3.
NFS
rsize=32768,wsize=32768
4.
NFS
异步磁盘IO操作
async
5.
配置与磁盘个数相等的通道数
allocate channel
6.
配置与CPU个数一半的并行度
PARALLELISM 1;
7.
压缩备份集
backup as compressed backupset
8.
机器负载高时时可考虑主动延长备份时间来降低负载
duration 2:00 minimize load
9.
启用块跟踪
alter database enable block change tracking using file 'd:/app/product/11.2.0/dbhome_1/dbs/block_change_tracking.trk';
10.
合理的备份时间与备份策略
11.
页:
[1]