2023年7月30日发(作者：)
数据库异地备份⽅案数据库异地备份⽅案备份⽬的保证在⽣产数据库发⽣异常，短时间⽆法恢复时，确保数据的可恢复性，或者在误删数据后的紧急恢复。环境分析确定业务系统，确定⽣产环境，确认备份环境。1、 现有数据量2、 每⽇增量数据3、 备份位置4、 ⽹络带宽5、 业务可中断时间6、 可容忍数据丢失情况风险分析1、 ⽣产环境和备份环境在同⼀主机不能避免主机单点故障，如主机cpu，内存等硬件损坏，⽣产和备份都不可⽤，不建议。2、 ⽣产环境和备份环境在同⼀机柜不同主机不能避免机柜单点故障，如机柜机器同时损坏，局部⽕灾等，虽然可能性较⼩，有条件还是建议不放在⼀起。3、 ⽣产环境和备份环境在同⼀机房不同机柜不能避免机房出现极端情况，机房断电，挖断光缆等，机房多路电源，快速抢修都能很好的解决此类情况，但遇见⽕灾等还是有可能导致⽣产和备份同时丢失数据。4、 ⽣产环境和备份环境在同城不同机房⼏乎可避免已知所有威胁，但对⽹络要求⾼，投⼊成本⾼。⽅案⽐对1、 商业软件VERITAS NBU，DSG等优点：稳定可靠，有原⼚⽀撑，⼀定程度可以保证数据安全，恢复时间需要根据数量⼤⼩决定。缺点：使⽤商业软件需要授权，后期维护成本⾼，必须要⼚商来实施。恢复⽅式：⼀般使⽤rman异机恢复。2、 ⼿动rman脚本备份优点：⽆商业授权，成本较低，备份灵活，可以对⽣产环境做修复。缺点：调试过程⽐较慢，对脚本书写等要求较⾼。恢复⽅式：使⽤rman恢复。3、 使⽤数据泵备份优点：简单，更灵活，可以对重要的表进⾏备份，指定⽤户备份。缺点：不能恢复到指定时间，只能恢复到备份时间点。恢复⽅式：如果使⽤数据泵备份，恢复也必须使⽤数据泵。4、 搭建dg备份环境优点：在可以快速恢复，适⽤于⼤型实时性要求⾼数据量⼤的系统。缺点：对⽹络要求⽐较⾼，实施相对复杂，需要对现有⽣产环境做修改，并且重启⽣产环境。恢复⽅式：⼿动切换到备份环境⽅案选择通过如上对⽐针对数据量不超过2T，每⽇增量不超过200G，建议使⽤⼿动备份的⽅式。⼿动脚本实施过程本地新建备份⽬录 根据现有数据库数据量⼤⼩，创建相应的⽬录mkdir –p /backup/logmkdir –p /backup/rmandata创建远程备份⽬录如果不是使⽤nfs挂载，需要使⽤scp的时候远程先创建⽬录。Mkdir –p /dest_bak配置RMAN策略rman target /show all;configure controlfile autobackup on;configure retention policy to recovery window of 7 days;configure controlfile autobackup format for device type disk to '/backup/rmandata/%F';
编写本地备份脚本脚本中⽬录如果使⽤nfs挂载，则不⽤传输。如果没有挂载则涉及远程传输scp命令，需要配置互信。全备脚本vi /rman_bak/database_ce /home/oracle/.bash_profilerman target / log /backup/log/database`date +%Y%m%d%H%M%S`.log append < #删除本机⼀天前的备份#find /backup/rmandata -ctime +1 -exec rm -rf {} ;#删除本机⼗分钟前的备份find /backup/rmandata -cmin +10 -exec rm -rf {} ;echo "backup complete!"归档脚本Vi /rman_bak/archivelog_ce /home/oracle/.bash_profilerman target / log /backup/log/archivelog`date +%Y%m%d%H%M%S`.log < RMAN> 2> 3> 4> 5> 6>RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "identifier": expecting one of: ";"RMAN-01008: the bad identifier was: ch1RMAN-01007: at line 6 column 17 file: standard input RMAN>RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "identifier": expecting one of: ";"RMAN-01008: the bad identifier was: ch2RMAN-01007: at line 1 column 17 file: standard input RMAN>RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-00558: error encountered while parsing input commandsRMAN-01009: syntax error: found "}": expecting one of: "advise, allocate, alter, backup, @, catalog, change, configure,connect, convert, copy, create, crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list, mount, open,print, quit, recover, register, release, repair, replace, report, reset, restore, resync, revoke, run, send, set, show, shutdown,spool, sql, startup, switch, transport, unregister, upgrade, validate, {, "RMAN-01007: at line 1 column 1 file: standard input⽀撑和维护定期查看备份⽇志，备份数据等情况，是否需要修改频率，备份保留时间，归档保留周期，⽬录空间⼤⼩以及备份时间。恢复测试搭建oracle环境，定期指定时间点做恢复测试，验证备份的有效性。对备份策略和结果有问题的情况及时调整备份。创建参数⽂件[oracle@host01 reco]$cd /rman_full/reco[oracle@host01 reco]$ vi _name=orclsga_target=500Mcontrol_files='/rman_full/reco/'db_create_file_dest='/rman_full/reco'指定ORACLE_SID[oracle@host01 rman_full]$ export ORACLE_SID=orcl启动到nomount[oracle@host01 reco]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 29 22:05:27 2020Copyright (c) 1982, 2013, Oracle. All rights ted to an idle > startup nomount pfile='/rman_full/reco/';ORACLE instance System Global Area 521936896 bytesFixed Size 2254824 bytesVariable Size 159385624 bytesDatabase Buffers 356515840 bytesRedo Buffers 3780608 bytesSQL> exit连接rman恢复控制⽂件[oracle@host01 reco]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 29 22:06:10 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights ted to target database: ORCL (not mounted)RMAN> restore controlfile from "/rman_full/c-1546745-17";Starting restore at 29-DEC-20using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/rman_full/reco/ished restore at 29-DEC-20启动数据库到mount[oracle@host01 reco]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 29 22:10:12 2020Copyright (c) 1982, 2013, Oracle. All rights ted to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database mount;Database altered.将备份集添加到rman[oracle@host01 reco]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Tue Dec 29 22:11:12 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights ted to target database: ORCL (DBID=1546745075, not open)RMAN> catalog start with "/rman_full/";using target database control file instead of recovery catalogsearching for all files that match the pattern /rman_full/List of Files Unknown to the Database=====================================File Name: /rman_full/arch_ORCL_20210112_77_1File Name: /rman_full/arch_ORCL_20210112_46_1File Name: /rman_full/arch_ORCL_20210112_72_1File Name: /rman_full/full_ORCL_20210112_2nvketer_1_1File Name: /rman_full/ctl_ORCL_20210112_99_1File Name: /rman_full/arch_ORCL_20210112_54_1File Name: /rman_full/c-1546745-12File Name: /rman_full/arch_ORCL_20210112_73_1File Name: /rman_full/ctl_ORCL_20210112_62_1File Name: /rman_full/full_ORCL_20210112_2hvkesr5_1_1File Name: /rman_full/arch_ORCL_20210112_102_1File Name: /rman_full/ctl_ORCL_20210112_85_1File Name: /rman_full/c-1546745-16File Name: /rman_full/c-1546745-13File Name: /rman_full/c-1546745-0fFile Name: /rman_full/c-1546745-05File Name: /rman_full/c-1546745-0cFile Name: /rman_full/c-1546745-0bFile Name: /rman_full/arch_ORCL_20210112_90_1File Name: /rman_full/c-1546745-04File Name: /rman_full/full_ORCL_20210112_2uvketpi_1_1File Name: /rman_full/c-1546745-0aFile Name: /rman_full/ctl_ORCL_20210112_55_1File Name: /rman_full/arch_ORCL_20210112_60_1File Name: /rman_full/full_ORCL_20210112_1ivkemjp_1_1File Name: /rman_full/arch_ORCL_20210112_68_1File Name: /rman_full/c-1546745-15File Name: /rman_full/full_ORCL_20210112_0tvkeld9_1_1File Name: /rman_full/arch_ORCL_20210112_65_1File Name: /rman_full/c-1546745-17File Name: /rman_full/c-1546745-06File Name: /rman_full/arch_ORCL_20210112_98_1File Name: /rman_full/full_ORCL_20210112_0vvkelhf_1_1File Name: /rman_full/full_ORCL_20210112_10vkelhf_1_1File Name: /rman_full/c-1546745-0dFile Name: /rman_full/arch_ORCL_20210112_101_1File Name: /rman_full/arch_ORCL_20210112_83_1File Name: /rman_full/arch_ORCL_20210112_47_1File Name: /rman_full/c-1546745-09File Name: /rman_full/c-1546745-0eFile Name: /rman_full/arch_ORCL_20210112_76_1File Name: /rman_full/arch_ORCL_20210112_64_1File Name: /rman_full/full_ORCL_20210112_0pvkel6s_1_1File Name: /rman_full/arch_ORCL_20210112_53_1File Name: /rman_full/arch_ORCL_20210112_69_1File Name: /rman_full/full_ORCL_20210112_0svkeld9_1_1File Name: /rman_full/c-1546745-07File Name: /rman_full/full_ORCL_20210112_15vkembg_1_1File Name: /rman_full/arch_ORCL_20210112_61_1File Name: /rman_full/full_ORCL_20210112_1cvkemd0_1_1File Name: /rman_full/arch_ORCL_20210112_84_1File Name: /rman_full/arch_ORCL_20210112_97_1File Name: /rman_full/arch_ORCL_20210112_41_1File Name: /rman_full/full_ORCL_20210112_1jvkemjp_1_1File Name: /rman_full/ctl_ORCL_20210112_48_1File Name: /rman_full/c-1546745-02File Name: /rman_full/c-1546745-14File Name: /rman_full/ctl_ORCL_20210112_74_1File Name: /rman_full/arch_ORCL_20210112_34_1File Name: /rman_full/c-1546745-10File Name: /rman_full/ctl_ORCL_20210112_92_1File Name: /rman_full/full_ORCL_20210112_0nvkel2r_1_1File Name: /rman_full/ctl_ORCL_20210112_70_1File Name: /rman_full/arch_ORCL_20210112_91_1File Name: /rman_full/full_ORCL_20210112_2vvketpi_1_1File Name: /rman_full/full_ORCL_20210112_1bvkemd0_1_1File Name: /rman_full/full_ORCL_20210112_2gvkesr5_1_1File Name: /rman_full/ctl_ORCL_20210112_78_1File Name: /rman_full/arch_ORCL_20210112_40_1File Name: /rman_full/c-1546745-11File Name: /rman_full/reco/e Name: /rman_full/full_ORCL_20210112_0mvkel2r_1_1File Name: /rman_full/full_ORCL_20210112_0qvkel6s_1_1File Name: /rman_full/c-1546745-03File Name: /rman_full/c-1546745-08File Name: /rman_full/ctl_ORCL_20210112_66_1File Name: /rman_full/arch_ORCL_20210112_35_1File Name: /rman_full/full_ORCL_20210112_1pvkeof8_1_1File Name: /rman_full/full_ORCL_20210112_16vkembg_1_1File Name: /rman_full/ctl_ORCL_20210112_103_1File Name: /rman_full/full_ORCL_20210112_2ovketer_1_1File Name: /rman_full/full_ORCL_20210112_1qvkeof8_1_1 Do you really want to catalog the above files (enter YES or NO)? ataloging doneList of Cataloged Files=======================File Name: /rman_full/arch_ORCL_20210112_77_1File Name: /rman_full/arch_ORCL_20210112_46_1File Name: /rman_full/arch_ORCL_20210112_72_1File Name: /rman_full/full_ORCL_20210112_2nvketer_1_1File Name: /rman_full/ctl_ORCL_20210112_99_1File Name: /rman_full/arch_ORCL_20210112_54_1File Name: /rman_full/c-1546745-12File Name: /rman_full/arch_ORCL_20210112_73_1File Name: /rman_full/ctl_ORCL_20210112_62_1File Name: /rman_full/full_ORCL_20210112_2hvkesr5_1_1File Name: /rman_full/arch_ORCL_20210112_102_1File Name: /rman_full/ctl_ORCL_20210112_85_1File Name: /rman_full/c-1546745-16File Name: /rman_full/c-1546745-13File Name: /rman_full/c-1546745-0fFile Name: /rman_full/c-1546745-05File Name: /rman_full/c-1546745-0cFile Name: /rman_full/c-1546745-0bFile Name: /rman_full/arch_ORCL_20210112_90_1File Name: /rman_full/c-1546745-04File Name: /rman_full/full_ORCL_20210112_2uvketpi_1_1File Name: /rman_full/c-1546745-0aFile Name: /rman_full/ctl_ORCL_20210112_55_1File Name: /rman_full/arch_ORCL_20210112_60_1File Name: /rman_full/full_ORCL_20210112_1ivkemjp_1_1File Name: /rman_full/arch_ORCL_20210112_68_1File Name: /rman_full/c-1546745-15File Name: /rman_full/full_ORCL_20210112_0tvkeld9_1_1File Name: /rman_full/arch_ORCL_20210112_65_1File Name: /rman_full/c-1546745-17File Name: /rman_full/c-1546745-06File Name: /rman_full/arch_ORCL_20210112_98_1File Name: /rman_full/full_ORCL_20210112_0vvkelhf_1_1File Name: /rman_full/full_ORCL_20210112_10vkelhf_1_1File Name: /rman_full/c-1546745-0dFile Name: /rman_full/arch_ORCL_20210112_101_1File Name: /rman_full/arch_ORCL_20210112_83_1File Name: /rman_full/arch_ORCL_20210112_47_1File Name: /rman_full/c-1546745-09File Name: /rman_full/c-1546745-0eFile Name: /rman_full/arch_ORCL_20210112_76_1File Name: /rman_full/arch_ORCL_20210112_64_1File Name: /rman_full/full_ORCL_20210112_0pvkel6s_1_1File Name: /rman_full/arch_ORCL_20210112_53_1File Name: /rman_full/arch_ORCL_20210112_69_1File Name: /rman_full/full_ORCL_20210112_0svkeld9_1_1File Name: /rman_full/c-1546745-07File Name: /rman_full/full_ORCL_20210112_15vkembg_1_1File Name: /rman_full/arch_ORCL_20210112_61_1File Name: /rman_full/full_ORCL_20210112_1cvkemd0_1_1File Name: /rman_full/arch_ORCL_20210112_84_1File Name: /rman_full/arch_ORCL_20210112_97_1File Name: /rman_full/arch_ORCL_20210112_41_1File Name: /rman_full/full_ORCL_20210112_1jvkemjp_1_1File Name: /rman_full/ctl_ORCL_20210112_48_1File Name: /rman_full/c-1546745-02File Name: /rman_full/c-1546745-14File Name: /rman_full/ctl_ORCL_20210112_74_1File Name: /rman_full/arch_ORCL_20210112_34_1File Name: /rman_full/c-1546745-10File Name: /rman_full/ctl_ORCL_20210112_92_1File Name: /rman_full/full_ORCL_20210112_0nvkel2r_1_1File Name: /rman_full/ctl_ORCL_20210112_70_1File Name: /rman_full/arch_ORCL_20210112_91_1File Name: /rman_full/full_ORCL_20210112_2vvketpi_1_1File Name: /rman_full/full_ORCL_20210112_1bvkemd0_1_1File Name: /rman_full/full_ORCL_20210112_2gvkesr5_1_1File Name: /rman_full/ctl_ORCL_20210112_78_1File Name: /rman_full/arch_ORCL_20210112_40_1File Name: /rman_full/c-1546745-11File Name: /rman_full/full_ORCL_20210112_0mvkel2r_1_1File Name: /rman_full/full_ORCL_20210112_0qvkel6s_1_1File Name: /rman_full/c-1546745-03File Name: /rman_full/c-1546745-08File Name: /rman_full/ctl_ORCL_20210112_66_1File Name: /rman_full/arch_ORCL_20210112_35_1File Name: /rman_full/full_ORCL_20210112_1pvkeof8_1_1File Name: /rman_full/full_ORCL_20210112_16vkembg_1_1File Name: /rman_full/ctl_ORCL_20210112_103_1File Name: /rman_full/full_ORCL_20210112_2ovketer_1_1File Name: /rman_full/full_ORCL_20210112_1qvkeof8_1_1List of Files Which Where Not Cataloged=======================================File Name: /rman_full/reco/ RMAN-07517: Reason: The file header is corrupted使⽤控制⽂件⽣成的sql⽂件[oracle@host01 reco]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 29 22:12:45 2020Copyright (c) 1982, 2013, Oracle. All rights ted to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database backup controlfile to trace as '/rman_full/reco/';Database altered.查看sql⽂件，并根据sql⽂件写脚本[oracle@host01 reco]$ more -- The following are current System-scope REDO Log Archival related-- parameters and can be included in the database initialization file.---- LOG_ARCHIVE_DEST=''-- LOG_ARCHIVE_DUPLEX_DEST=''---- LOG_ARCHIVE_FORMAT=%t_%s_%---- DB_UNIQUE_NAME="orcl"---- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'-- LOG_ARCHIVE_MAX_PROCESSES=4-- STANDBY_FILE_MANAGEMENT=MANUAL-- STANDBY_ARCHIVE_DEST=?/dbs/arch-- FAL_CLIENT=''-- FAL_SERVER=''---- LOG_ARCHIVE_DEST_1='LOCATION=/app/u01/oracle/product/11.2.0/dbs/arch'-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'-- LOG_ARCHIVE_DEST_STATE_1=ENABLE ---- Below are two sets of SQL statements, each of which creates a new-- control file and uses it to open the database. The first set opens-- the database with the NORESETLOGS option and should be used only if-- the current versions of all online logs are available. The second-- set opens the database with the RESETLOGS option and should be used-- if online logs are unavailable.-- The appropriate set of statements can be copied from the trace into-- a script file, edited as necessary, and executed when there is a-- need to re-create the control file.---- Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- WARNING! The current control file needs to be checked against-- the datafiles to insure it contains the correct files. The-- commands printed here may be missing log and/or data files.-- Another report should be made after the database has been-- successfully opened. -- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ( '+DATA/orcl/onlinelog/group_1.261.1018964343', '+FRA/orcl/onlinelog/group_1.257.1018964343' ) SIZE 50M BLOCKSIZE 512, GROUP 2 ( '+DATA/orcl/onlinelog/group_2.262.1018964345', '+FRA/orcl/onlinelog/group_2.258.1018964345' ) SIZE 50M BLOCKSIZE 512, GROUP 3 ( '+DATA/orcl/onlinelog/group_3.263.1018964345', '+FRA/orcl/onlinelog/group_3.259.1018964347' ) SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE '+DATA/orcl/datafile/system.256.1018964259', '+DATA/orcl/datafile/sysaux.257.1018964259', '+DATA/orcl/datafile/undotbs1.258.1018964259', '+DATA/orcl/datafile/users.259.1018964259', '+DATA/orcl/datafile/example.265.1018964353', '+DATA/orcl/datafile/fs_tbs1.267.1036268673'CHARACTER SET AL32UTF8; -- Configure RMAN configuration record 1VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('CONTROLFILE AUTOBACKUP','ON');-- Configure RMAN configuration record 2VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7DAYS');-- Configure RMAN configuration record 3VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICETYPE','DISK TO ''/backup/rmandata/%F''');-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/app/u01/oracle/product/11.2.0/dbs/arch1_1_';-- ALTER DATABASE REGISTER LOGFILE '/app/u01/oracle/product/11.2.0/dbs/arch1_1_';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or R DATABASE-- Block change tracking was enabled, so re-enable it DATABASE ENABLE BLOCK CHANGE TRACKINGUSING FILE '/u01/app/oracle/' REUSE;-- All logs need archiving and a log switch is SYSTEM ARCHIVE LOG ALL;-- Database can now be opened DATABASE OPEN;-- Files in read-only tablespaces are now DATABASE RENAME FILE 'MISSING00007' TO '+DATA/orcl/datafile/his4.268.1060958305';-- Online the files in read-only TABLESPACE "HIS4" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require TABLESPACE TEMP ADD TEMPFILE '+DATA/orcl/tempfile/temp.264.1018964349' REUSE;-- End of tempfile additions.---- Set #2. RESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- WARNING! The current control file needs to be checked against-- the datafiles to insure it contains the correct files. The-- commands printed here may be missing log and/or data files.-- Another report should be made after the database has been-- successfully opened. -- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ( '+DATA/orcl/onlinelog/group_1.261.1018964343', '+FRA/orcl/onlinelog/group_1.257.1018964343' ) SIZE 50M BLOCKSIZE 512, GROUP 2 ( '+DATA/orcl/onlinelog/group_2.262.1018964345', '+FRA/orcl/onlinelog/group_2.258.1018964345' ) SIZE 50M BLOCKSIZE 512, GROUP 3 ( '+DATA/orcl/onlinelog/group_3.263.1018964345', '+FRA/orcl/onlinelog/group_3.259.1018964347' ) SIZE 50M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE '+DATA/orcl/datafile/system.256.1018964259', '+DATA/orcl/datafile/sysaux.257.1018964259', '+DATA/orcl/datafile/undotbs1.258.1018964259', '+DATA/orcl/datafile/users.259.1018964259', '+DATA/orcl/datafile/example.265.1018964353', '+DATA/orcl/datafile/fs_tbs1.267.1036268673'CHARACTER SET AL32UTF8; -- Configure RMAN configuration record 1VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('CONTROLFILE AUTOBACKUP','ON');-- Configure RMAN configuration record 2VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7DAYS');-- Configure RMAN configuration record 3VARIABLE RECNO NUMBER;EXECUTE :RECNO := _BACKUP_FIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICETYPE','DISK TO ''/backup/rmandata/%F''');-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/app/u01/oracle/product/11.2.0/dbs/arch1_1_';-- ALTER DATABASE REGISTER LOGFILE '/app/u01/oracle/product/11.2.0/dbs/arch1_1_';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or R DATABASE USING BACKUP CONTROLFILE-- Block change tracking was enabled, so re-enable it DATABASE ENABLE BLOCK CHANGE TRACKINGUSING FILE '/u01/app/oracle/' REUSE;-- Database can now be opened zeroing the online DATABASE OPEN RESETLOGS;-- Files in read-only tablespaces are now DATABASE RENAME FILE 'MISSING00007' TO '+DATA/orcl/datafile/his4.268.1060958305';-- Online the files in read-only TABLESPACE "HIS4" ONLINE;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require TABLESPACE TEMP ADD TEMPFILE '+DATA/orcl/tempfile/temp.264.1018964349' REUSE;-- End of tempfile additions.执⾏还原由于新环境和原来的环境的⽂件位置不同，这⾥根据新环境设置新名称。 RMAN> run {2> allocate channel ch1 device type disk;3> allocate channel ch2 device type disk;4> set newname for datafile '+DATA/orcl/datafile/system.256.1018964259' to '/rman_full/reco/datafile/';5> set newname for datafile '+DATA/orcl/datafile/sysaux.257.1018964259' to '/rman_full/reco/datafile/';6> set newname for datafile '+DATA/orcl/datafile/undotbs1.258.1018964259' to '/rman_full/reco/datafile/';7> set newname for datafile '+DATA/orcl/datafile/users.259.1018964259' to '/rman_full/reco/datafile/';8> set newname for datafile '+DATA/orcl/datafile/example.265.1018964353' to '/rman_full/reco/datafile/';9> set newname for datafile '+DATA/orcl/datafile/fs_tbs1.267.1036268673' to '/rman_full/reco/datafile/fs_tbs1dbf';10> SET UNTIL TIME "to_date('2021-01-12 14:00:00','yyyy-mm-dd hh24:mi:ss')";11> restore database;12> switch datafile all;13> release channel c1;14> release channel c2;15> } allocated channel: ch1channel ch1: SID=19 device type=DISKallocated channel: ch2channel ch2: SID=20 device type=DISKexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET until clauseStarting restore at 29-DEC-20channel ch1: starting datafile backup set restorechannel ch1: specifying datafile(s) to restore from backup setchannel ch1: restoring datafile 00001 to /rman_full/reco/datafile/nnel ch1: restoring datafile 00006 to /rman_full/reco/datafile/fs_tbs1dbfchannel ch1: reading from backup piece /rman_full/full_ORCL_20210112_1qvkeof8_1_1channel ch2: starting datafile backup set restorechannel ch2: specifying datafile(s) to restore from backup setchannel ch2: restoring datafile 00002 to /rman_full/reco/datafile/nnel ch2: restoring datafile 00003 to /rman_full/reco/datafile/nnel ch2: restoring datafile 00004 to /rman_full/reco/datafile/nnel ch2: restoring datafile 00005 to /rman_full/reco/datafile/nnel ch2: reading from backup piece /rman_full/full_ORCL_20210112_1pvkeof8_1_1channel ch2: piece handle=/rman_full/full_ORCL_20210112_1pvkeof8_1_1 tag=TAG20210112T124527channel ch2: restored backup piece 1channel ch2: restore complete, elapsed time: 00:00:15channel ch2: starting datafile backup set restorechannel ch2: specifying datafile(s) to restore from backup setchannel ch2: restoring datafile 00007 to +DATA/orcl/datafile/his4.268.1060958305channel ch2: reading from backup piece /rman_full/full_ORCL_20210112_2vvketpi_1_1channel ch1: piece handle=/rman_full/full_ORCL_20210112_1qvkeof8_1_1 tag=TAG20210112T124527channel ch1: restored backup piece 1channel ch1: restore complete, elapsed time: 00:00:18channel ch2: piece handle=/rman_full/full_ORCL_20210112_2vvketpi_1_1 tag=TAG20210112T141618channel ch2: restored backup piece 1channel ch2: restore complete, elapsed time: 00:00:03Finished restore at 29-DEC-20 datafile 1 switched to datafile copyinput datafile copy RECID=17 STAMP=1060467802 file name=/rman_full/reco/datafile/afile 2 switched to datafile copyinput datafile copy RECID=18 STAMP=1060467802 file name=/rman_full/reco/datafile/afile 3 switched to datafile copyinput datafile copy RECID=19 STAMP=1060467802 file name=/rman_full/reco/datafile/afile 4 switched to datafile copyinput datafile copy RECID=20 STAMP=1060467802 file name=/rman_full/reco/datafile/afile 5 switched to datafile copyinput datafile copy RECID=21 STAMP=1060467802 file name=/rman_full/reco/datafile/afile 6 switched to datafile copyinput datafile copy RECID=22 STAMP=1060467802 file name=/rman_full/reco/datafile/fs_tbs1dbf released channel: c1released channel: c2执⾏恢复指定时间应⽤归档RMAN> run {2> allocate channel c1 device type disk;3> allocate channel c2 device type disk;4> SET UNTIL TIME "to_date('2021-01-12 14:00:00','yyyy-mm-dd hh24:mi:ss')";5> recover database;6> release channel c1;7> release channel c2;8> }allocated channel: c1channel c1: SID=19 device type=DISKallocated channel: c2channel c2: SID=20 device type=DISKexecuting command: SET until clauseStarting recover at 29-DEC-20datafile 7 not processed because file is read-onlystarting media recoverychannel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=42channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_60_1channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=43channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_61_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_60_1 tag=TAG20210112T124554channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:01archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_42_ thread=1 sequence=42channel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=44channel c1: restoring archived logarchived log thread=1 sequence=45channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_64_1channel c2: piece handle=/rman_full/arch_ORCL_20210112_61_1 tag=TAG20210112T124554channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:01archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_43_ thread=1 sequence=43channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=46channel c2: restoring archived logarchived log thread=1 sequence=47channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_65_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_64_1 tag=TAG20210112T130357channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_44_ thread=1 sequence=44archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_45_ thread=1 sequence=45channel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=48channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_68_1channel c2: piece handle=/rman_full/arch_ORCL_20210112_65_1 tag=TAG20210112T130357channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:01archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_46_ thread=1 sequence=46archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_47_ thread=1 sequence=47channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=49channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_69_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_68_1 tag=TAG20210112T130500channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_48_ thread=1 sequence=48channel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=50channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_72_1channel c2: piece handle=/rman_full/arch_ORCL_20210112_69_1 tag=TAG20210112T130500channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_49_ thread=1 sequence=49channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=51channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_73_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_72_1 tag=TAG20210112T130545channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_50_ thread=1 sequence=50channel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=52channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_76_1channel c2: piece handle=/rman_full/arch_ORCL_20210112_73_1 tag=TAG20210112T130545channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_51_ thread=1 sequence=51channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=53channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_77_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_76_1 tag=TAG20210112T130620channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_52_ thread=1 sequence=52channel c1: starting archived log restore to default destinationchannel c1: restoring archived logarchived log thread=1 sequence=54channel c1: reading from backup piece /rman_full/arch_ORCL_20210112_83_1channel c2: piece handle=/rman_full/arch_ORCL_20210112_77_1 tag=TAG20210112T130620channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:00archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_53_ thread=1 sequence=53channel c2: starting archived log restore to default destinationchannel c2: restoring archived logarchived log thread=1 sequence=55channel c2: reading from backup piece /rman_full/arch_ORCL_20210112_84_1channel c1: piece handle=/rman_full/arch_ORCL_20210112_83_1 tag=TAG20210112T140031channel c1: restored backup piece 1channel c1: restore complete, elapsed time: 00:00:01archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_54_ thread=1 sequence=54channel c2: piece handle=/rman_full/arch_ORCL_20210112_84_1 tag=TAG20210112T140031channel c2: restored backup piece 1channel c2: restore complete, elapsed time: 00:00:01archived log file name=/app/u01/oracle/product/11.2.0/dbs/arch1_55_ thread=1 sequence=55media recovery complete, elapsed time: 00:00:00Finished recover at 29-DEC-20released channel: c1released channel: c2RMAN>RMAN>RMAN>修改重做⽇志⽂件 alter database rename file '+DATA/orcl/onlinelog/group_1.261.1018964343' to'/rman_full/reco/onlinelog/group_1_'; alter database rename file '+FRA/orcl/onlinelog/group_1.257.1018964343' to'/rman_full/reco/onlinelog/group_1_'; alter database rename file '+DATA/orcl/onlinelog/group_2.262.1018964345' to'/rman_full/reco/onlinelog/group_2_'; alter database rename file '+FRA/orcl/onlinelog/group_2.258.1018964345' to'/rman_full/reco/onlinelog/group_2_'; alter database rename file '+DATA/orcl/onlinelog/group_3.263.1018964345' to'/rman_full/reco/onlinelog/group_3_'; alter database rename file '+FRA/orcl/onlinelog/group_3.259.1018964347' to'/rman_full/reco/onlinelog/group_3_'; 使⽤resetlog⽅式打开数据库执⾏过程中有报错，以下是排错过程SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-00344: unable to re-create online log'/rman_full/reco/onlinelog/group_1_'ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 1提⽰没有⽬录，创建⽬录[oracle@host01 reco]$ mkdir onlinelog[oracle@host01 reco]$ datafile onlinelog ORCL 再次打开SQL> alter database open;alter database open*ERROR at line 1:ORA-19751: could not create the change tracking fileORA-19750: change tracking file: '/u01/app/oracle/'ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 1ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3（这⾥敲错了，应该是resetlog，没有关系，下⾯提⽰没有块更改的track⽬录，创建⽬录）[root@host01 ~]# mkdir -p /u01/app/oracle/[root@host01 ~]# chown oracle:oinstall /u01/app/oracle 再次打开，SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-00392: log 3 of thread 1 is being cleared, operation not allowedORA-00312: online log 3 thread 1: '/rman_full/reco/onlinelog/group_3_'ORA-00312: online log 3 thread 1: '/rman_full/reco/onlinelog/group_3_' 这⾥是因为指定了时间，在做to_time recovery ,当前正在actve 的⼀组⽇志如logfile group 3 可能需要切断，才会报错。处理⽅法：这个错误需要⼿动重置redo⽂件才可以解决，⽆法直接resetlogs的⽅式重置。SQL> alter database clear logfile group 3; Database altered.再次打开SQL> alter database open resetlogs; alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-39700: database must be opened with UPGRADE optionProcess ID: 3335Session ID: 21 Serial number: 55提⽰需要使⽤upgrade打开，想起来原来的测试环境是11203的，恢复环境是11204，所以需要升级。然后关闭数据库，创建spfile后，再次打开 SQL> startup nomount pfile='/rman_full/reco/';ORACLE instance started. Total System Global Area 521936896 bytesFixed Size 2254824 bytesVariable Size 159385624 bytesDatabase Buffers 356515840 bytesRedo Buffers 3780608 bytes创建spfileSQL> create spfile from pfile='/rman_full/reco/';File > startup upgrade ;ORACLE instance started. Total System Global Area 521936896 bytesFixed Size 2254824 bytesVariable Size 159385624 bytesDatabase Buffers 356515840 bytesRedo Buffers 3780608 bytesDatabase se opened.升级脚本SQL> @$ORACLE_;再次打开，查看数据库状态[oracle@host01 rman_full]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 29 23:17:22 2020Copyright (c) 1982, 2013, Oracle. All rights ted to an idle > startup ;ORACLE instance System Global Area 521936896 bytesFixed Size 2254824 bytesVariable Size 318769176 bytesDatabase Buffers 197132288 bytesRedo Buffers 3780608 bytesDatabase se >SQL>SQL> select instance_name,status from v$instance;INSTANCE_NAME STATUS---------------- ------------orcl OPEN 源端和⽬标数据对⽐SQL> select count(*) from 1; COUNT(*)---------- 1203248 SQL> select count(*) from his1; COUNT(*)---------- 1203248 脚本部分特别是定时删除，和向远程发送的部分，感觉写的不好，可能还会有错误。如果有问题，烦请给予指正。
发布者：admin，转转请注明出处：http://www.yc00.com/news/1690650724a386426.html
评论列表（0条）