假定备份源是RAC+ASM环境,执行恢复操作的对象服务器是文件系统环境。假定备份源端具有一个称为 /home/grid/mnt的挂载点作为NFS客户端,在还原/恢复目标端,作为 NFS服务器的对应目录是 /home/oracle/formnt。
我的备份脚本如下:
Level 0 增量备份:
run{ backup incremental level 0 format ‘+DATA‘ database; sql ‘alter system archive log current‘; backup as backupset format ‘+DATA‘ archivelog from time ‘sysdate-1‘; backup format ‘+DATA‘ current controlfile; }
Level 1 增量备份:
run{ backup incremental level 1 format ‘+DATA‘ database; sql ‘alter system archive log current‘; backup as backupset format ‘+DATA‘ archivelog from time ‘sysdate-1‘; backup format ‘+DATA‘ current controlfile; }
仅使用0级和1级备份可能无法还原到指定的时间点。因此,上面的脚本中,切换 archive log 并对其进行备份。在执行备份之前,记录一下数据文件和 Online REDO 文件路径。
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/ORA12102/DATAFILE/system.261.943312827 +DATA/ORA12102/DATAFILE/sysaux.273.838525691 +DATA/ORA12102/DATAFILE/undotbs1.276.998325422 +DATA/ORA12102/DATAFILE/users.262.987321245 SQL> select member from gv$logfile; MEMBER -------------------------------------------------------------------------------- +DATA/ORA12102/ONLINELOG/group_3.264.954612469 +DATA/ORA12102/ONLINELOG/group_3.263.954612505 +DATA/ORA12102/ONLINELOG/group_2.266.954612377 +DATA/ORA12102/ONLINELOG/group_2.265.954612423 +DATA/ORA12102/ONLINELOG/group_1.268.954612297 +DATA/ORA12102/ONLINELOG/group_1.267.954612333 SQL>
Source 端的备份经过:
Level 0 增量备份 RMAN> run{ backup incremental level 0 format ‘+DATA‘ database; sql ‘alter system archive log current‘; backup as backupset format ‘+DATA‘ archivelog from time ‘sysdate-1‘; backup format ‘+DATA‘ current controlfile; }
模拟数据库的运行:变更一些数据
SQL> create table tab001 as select * from dba_objects; SQL> alter system archive log current;
Level 1 增量备份
RMAN> run{ backup incremental level 1 format ‘+DATA‘ database; sql ‘alter system archive log current‘; backup as backupset format ‘+DATA‘ archivelog from time ‘sysdate-1‘; backup format ‘+DATA‘ current controlfile; }
检查备份的结果:
$ export NLS_DATE_FORMAT=‘YYYYMMDD_HH24:MI:SS‘ $ rman target / RMAN> list backup; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ----------------- 5 Incr 0 1.20G DISK00:04:50 20210407_12:07:23 BP KEY: 5 STATUS: AVAILABLE Compressed : NO TAG: TAG20210407T120233 PIECE NAME: +DATA/ORA12102/BACKUPSET/2017_09_14/sssdr0_tag20210407t120233_0.260.954676953 ….. File LV Type Ckp SCNCkp Time Name ---- -- ---- ---------- ----------------- ---- 10 Incr 184119020210407_12:02:33 +DATA/ORA12102/DATAFILE/system.261.943312827 30 Incr 184119020210407_12:02:33 +DATA/ORA12102/DATAFILE/sysaux.273.838525691 40 Incr 184119020210407_12:02:33 +DATA/ORA12102/DATAFILE/undotbs1.276.998325422 60 Incr 184119020210407_12:02:33 +DATA/ORA12102/DATAFILE/users.262.987321245 …… BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 6 Full10.19M DISK00:00:01 20210407_12:07:23 BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG: TAG20210407T120233 Piece Name: /refresh/home/app/12.1.0.2/oracle/product/12.2.0.1/dbhome3/dbs/ncsnn1_tag20210407t125241_0.280.954680049 SPFILE Included: Modification time: 20210407_12:07:23 SPFILE db_unique_name: ORA12102 Control File Included: Ckp SCN: 1950495 Ckp time: 20210407_12:07:23 <<<< 此处 "SPFILE Include" 是随着 level 0 增分备份执行自动生成的包含 controlfile 和 spfile 信息的备份 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ----------------- 11 31.47M DISK00:00:13 20210407_12:54:35 BP KEY:11 STATUS: AVAILABLE Compressed: NO TAG: TAG20210407T125422 PIECE NAME: +DATA/ORA12102/BACKUPSET/2017_09_14/annnf0_tag20210407t125422_0.283.954680063 BS Key Type LV Size Device Type Elapsed Time Completion Time Thrd Seq Low SCNLow TimeNext SCN Next Time ---- ------- ---------- ----------------- ---------- --------- 114 183759620210407_11:22:53 184050920210407_11:48:07 115 184050920210407_11:48:07 184133520210407_12:07:41 116 184133520210407_12:07:41 184134320210407_12:07:43 117 184134320210407_12:07:43 184266320210407_12:51:19 118 184266320210407_12:51:19 184276220210407_12:54:19 119 184276220210407_12:54:19 184277020210407_12:54:20 <===请记住最新 archive log 的 Next SCN,因为我们将在还原时使用这个值。 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ----------------- 12 Full9.61M DISK00:00:07 20210407_12:54:44 BP KEY: 12 STATUS: AVAILABLE COMPRESSION: NO TAG: TAG20210407T125437 PIECE NAME : +DATA/ORA12102/BACKUPSET/2017_09_14/ncnnf0_tag20210407t125437_0.284.954680081 Controlfile included : Ckp SCN: 1842784 Ckp time: 20210407_12:54:37 RMAN>
使用asmcmd通过NFS将source 端ASM上的备份复制到dest 端。假设此NFS挂载点是 grid 用户主目录下的mnt目录。执行以下操作:
[root@ol66 ~]# su - grid [grid@ol66 ~]$ export ORACLE_SID=+ASM ASMCMD>cp +DATA/ORA12102/BACKUPSET/2017_09_14/sssdr0_tag20210407t120233_0.260.954676953 /home/grid/mnt ......
将上面 <list backup> 中列出的 备份文件,都拷贝到 dest 端(过程略)。
Dest 端执行恢复操作:
# su - oracle $ . oraenv ORACLE_SID = [ora12102] ? <<<<<<<<<<<<<<<<< 指定 Source 端的 ORACLE_SID ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1 <<<<<<<<<<<<<<<<<<< 设定 ORACLE_HOME(根据当初安装 Oracle binary 时的设定来指定) The Oracle base remains unchanged with value /u01/app/oracle $
利用前面的SPFILE 的备份(ncsnn1_tag20210407t125241_0.280.954680049),来在 dest 端恢复 SPFILE。
$ rman target / RMAN> startup nomount force; Failed to start: ORA-01078: failure in processing system parameters LRM-00109: ??????????????????????????????‘/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initora12102.ora‘????????????????????????????????? ... RMAN>
不必在意上面的错误,继续执行:
RMAN> restore spfile from ‘/home/oracle/formnt/ncsnn1_tag20210407t125241_0.280.954680049‘;
也作成 PFILE(是为了读取spfile 中的内容,并做出相应更改)
RMAN> restore spfile to pfile ‘/home/oracle/initora12102.ora‘ from ‘/home/oracle/formnt/ncsnn1_tag20210407t125241_0.280.954680049‘; RMAN> exit
由于source 端是RAC环境,dest 端是 single 环境,因此将 cluster_database设置为false或删除PFILE中的该行。然后检查有关PFILE中每个目录的信息。
$ cat initora12102.ora
ora12102.__data_transfer_cache_size=0 ora12102.__db_cache_size=1040187392 ora12102.__java_pool_size=16777216 ora12102.__large_pool_size=33554432 ora12102.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment ora12102.__pga_aggregate_target=503316480 ora12102.__sga_target=1493172224 ora12102.__shared_io_pool_size=67108864 ora12102.__shared_pool_size=318767104 ora12102.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/ora12102/adump‘ *.audit_trail=‘db‘ *.compatible=‘12.1.0.2.0‘ *.control_files=‘+DATA/ORA12102/CONTROLFILE/current.269.954612287‘,‘+DATA/ORA12102/CONTROLFILE/current.272.954612291‘ *.db_block_size=8192 *.db_create_file_dest=‘+DATA‘ *.db_domain=‘‘ *.db_name=‘ora12102‘ *.db_recovery_file_dest=‘+DATA‘ *.db_recovery_file_dest_size=4560m *.diagnostic_dest=‘/u01/app/oracle‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=ora12102XDB)‘ *.open_cursors=300 *.pga_aggregate_target=470m *.processes=300 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sga_target=1410m *.undo_tablespace=‘UNDOTBS1‘ $
参照上面的内容,在 dest 端,建立好目录。或者修改 pfile 里关于给中目录的信息,建立好对应的目录。
mkdir -p /u01/app/oracle/admin/ora12102/adump
因为 dest 端是文件系统,所以没有磁盘组,把涉及到磁盘组的内容进行修改,并建立相应目录(如:控制文件所在目录)
mkdir -p /u01/app/oracle/fast_recovery_area/CONTROLFILE mkdir -p /u01/app/oracle/oradata/CONTROLFILE
为在文件系统存储数据文件,建立相应的目录。
mkdir -p /u01/app/oracle/oradata/datafile
为了在文件系统上存储 online redo,也建立好相应的目录。
mkdir -p /u01/app/oracle/oradata/logfile
由于不能使用 ASM磁盘组,所以调整哪些用到磁盘组的路径,变成使用文件系统路径(如 control_files的值)。
ora12102.__data_transfer_cache_size=0 ora12102.__db_cache_size=1040187392 ora12102.__java_pool_size=16777216 ora12102.__large_pool_size=33554432 ora12102.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment ora12102.__pga_aggregate_target=503316480 ora12102.__sga_target=1493172224 ora12102.__shared_io_pool_size=67108864 ora12102.__shared_pool_size=318767104 ora12102.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/ora12102/adump‘ *.audit_trail=‘db‘ *.compatible=‘12.1.0.2.0‘ *.control_files=‘/u01/app/oracle/fast_recovery_area/CONTROLFILE/control01.ctl‘,‘/u01/app/oracle/oradata/CONTROLFILE/control02.ctl‘ *.db_block_size=8192 *.db_create_file_dest=‘/u01/app/oracle/oradata‘ *.db_domain=‘‘ *.db_name=‘ora12102‘ *.db_recovery_file_dest=‘/u01/app/oracle/fast_recovery_area‘ *.db_recovery_file_dest_size=4560m *.diagnostic_dest=‘/u01/app/oracle‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=ora12102XDB)‘ *.open_cursors=300 *.pga_aggregate_target=470m *.processes=300 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sga_target=1410m *.undo_tablespace=‘UNDOTBS1‘
现在 PFILE 修改好了,可以再次启动了:
$ sqlplus / as sysdba SQL> shu immediate SQL> startup nomount pfile=‘/home/oracle/initora12102.ora‘ SQL> exit
用 RMAM 恢复 控制文件:
$ rman target / RMAN> restore controlfile from ‘/home/oracle/formnt/ncnnf0_tag20210407t125437_0.284.954680081‘; RMAN> exit
Source 端,使用的是 ASM 磁盘组,dest 端使用文件系统,所以,设置 db_file_name_convert 和 log_file_name_convert。
(用到我上面建立好的 目录 /u01/app/oracle/oradata/datafile/ 和 /u01/app/oracle/oradata/logfile/ )
$ sqlplus / as sysdba SQL>create spfile from pfile=‘/home/oracle/initora12102.ora‘; SQL>shutdown immediate SQL>startup mount SQL> alter system set db_file_name_convert=‘+DATA/ORA12102/DATAFILE/‘,‘/u01/app/oracle/oradata/datafile/‘ scope=spfile; SQL> alter system set log_file_name_convert=‘+DATA/ORA12102/ONLINELOG/‘,‘/u01/app/oracle/oradata/logfile/‘ scope=spfile; SQL> shutdown immediate SQL> startup mount SQL> exit
由于在 source 端时,备份建立到了 磁盘组上,控制文件中也是如此记忆的。所以需要在 恢复控制文件之后,重新登记这些 备份的位置。
$ rman target / RMAN> RMAN> crosscheck backup; RMAN> delete expired backup; RMAN> catalog start with ‘/home/oracle/formnt/‘; <<<<<<<< 上述拷贝好的备份的位置。
执行数据库的 restore / recover :
RMAN> run{ set until scn 1842770; <<<<<<< 这个 SCN 的值,就是我们在 上面 list backup 时看到的最新的 archive log 的 next scn 值 restore database; recover database; }
打开数据库。
RMAN> alter database open resetlogs; RMAN>exit
确认一下新数据库总的 v$datafile 和 v$logfile 中 数据文件和 online redo 内容。
SQL> select name from v$datafile; SQL> select member from v$logfile; SQL> exit
至此,向新环境的恢复操作结束。