centos7 mysql还原dmp_Linux平台下RMAN异机恢复总结

最新推荐文章于 2021-12-28 10:59:18 发布

Fantix King

最新推荐文章于 2021-12-28 10:59:18 发布

阅读量173

点赞数

文章标签： centos7 mysql还原dmp

本文链接：https://blog.csdn.net/weixin_29266647/article/details/113652952

版权

下面总结、整理一下RMAN异机恢复这方面的知识点，这篇笔记在个人笔记里面躺了几年了，直到最近偶然被翻看到，遂整理、总结一下。如下所示，个人将整个RMAN异机恢复分为准备工作和操作步骤两大部分。当然，准备工作里面，有些步骤不是必须的，可以跳过或忽略的。这个取决于你的实际环境和你对RMAN异机恢复的熟悉程度。

准备工作

1：了解一下目标服务器与源服务器的操作系统版本信息

需要对比一下目标服务器与源服务器的操作系统版本是否一致，具体来说，操作系统版本信息、内核信息(例如Oracle Linux是否使用Unbreakable Enterprise Kernel内核等)，以及操作系统是32bit还是64bit等。如果RMAN异机恢复只是准备Dev、Test、UAT环境，那么这个完全可以忽略，如果是正式环境的迁移，那么最好关注一下，避免一些问题。例如，有些版本的操作系统对不是官方认证的，如果在迁移前不关注这些，那么迁移后，有可能出现一些意想不到的问题。

# uname -a# uname -m# more /etc/redhat-release

注意：这些工作是前期准备工作，不能到RMAN还原恢复的时候才做。

2：检查目标服务器与源服务器的数据库版本信息

如果源数据库和目标数据库版本一致，那么完全可以跳过这一步。但是，有时候可能是从32位还原升级到64位；有些是从ORACLE 10g 迁移升级到ORACLE 11g，那么在后面的RMAN还原后，还需做一些额外的Upgrade工作。个人刚做DBA的第一年，在一次迁移过程，事先安装过程中不小心选错了版本(标准版弄成了企业版，安装过程中由于检查某个选项，点击后退过程中系统默认选择了企业版，当时没有发现)，后续没有仔细检查，迁移完成后，验证时才发现版本是企业版。结果将整个迁移进度全部打乱了！

32bit

64bit

3：检查实例安装路径以及数据文件等路径

这里指数据库实例的安装路径，以及数据文件、控制文件、参数文件是否一致，如果全部一致的话，那么可以避免很多问题，但是很多时候，

我们需要重新规划存储路径或者其它一些原因，这些数据文件、参数文件等，很有可能跟源服务器不一致，那么事先了解这些，在迁移过程中

就必须注意到这些情况。作出相应的处理。否则RMAN还原过程中就会遇到一些问题。

4：将备份文件拷贝的相关目录

可以将备份拷贝到恢复目录解压或指定目录解压。如果源服务器与目标服务器的RMAN备份路径一致，那么可以省去很多不必要的麻烦。

5：创建相关的目录

例如，目标服务器安装ORACLE实例时，选择了“只安装实例”选项，那么在RMAN还原之前，我们必须创建下面一些目录(这些不是必须的，有些环境甚至完全不必要。具体根据实际情况判断)：

1：创建$ORACLE_BASE/admin/$ORACLE_SID/下的六个目录；

2： $ORACLE_BASE/oradata下创建$ORACLE_SID目录；

3： RMAN备份路径目录(这个地方最好与源数据库一致，创建好后，把源数据库备份的数据文件复制到这个目录里)；--非必须。

4：归档日志目录(同样，创建好后，把需要的归档日志文件复制到此目录) --非必须。

[root@getlnx14dev ~]#mkdir -p admin/SCM2/{adump,bdump,cdump,dpdump,pfile,udump}[root@getlnx14dev ~]#mkdir -p oradata/$ORACLE_SID[root@getlnx14dev ~]# chown -R oracle:oinstall /data[root@getlnx14dev ~]# su - oracleLast login: Fri May 26 13:38:38 CST 2017 on pts/2[oracle@getlnx14dev ~]$ cd /data/[oracle@getlnx14dev data]$ mkdir scm2[oracle@getlnx14dev data]$

操作步骤

下面测试，是将数据库通过RMAN备份恢复到一台测试服务器，出于测试目的，故意将实例的安装路径，以及恢复路径全部故意弄成不一致。具体测试环境如下：

源服务器：

操作系统： Red Hat Enterprise Linux Server release 5.1

数据库版本： Oracle Database 10g Release 10.2.0.4.0 32bit 标准版

目标服务器：

操作系统： Red Hat Enterprise Linux Server release 7.2 (Maipo) #注意，这个版本不是官方认证版本，仅做测试而已。

数据库版本： Oracle Database 10g Release 10.2.0.4.0 64bit 标准版

1：查询DBID信息

DBID是DataBase IDentifier的缩写，意思就是数据库的唯一标识符。这个DBID在数据文件头和控制文件都是存在的，可以用于标示数据文件的归属。

对于不同数据库来说，DBID应当不同，而db_name则可能是相同的。一般在nocatalog模式并且控制文件丢失时才需要这个。

SQL> select name,dbid from v$database;NAME DBID--------- ----------SCM2 3990839260SQL>

如果源服务器已经宕机，那么如何查询DBID相关信息呢？关于这个，其实也有很多方法获取，例如，如果你曾经做过AWR报告，可以从AWR中找到对应的DBID，也可以从恢复的控制文件获取等。

2：启动数据库实例到nomount状态

使用RMAN还原恢复时，DB必须启动到nomout状态。

[oracle@getlnx14dev SCM2]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Sun May 28 21:14:36 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database (not started)RMAN> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/init.ora';Oracle instance startedTotal System Global Area 159383552 bytesFixed Size 2082400 bytesVariable Size 150997408 bytesDatabase Buffers 4194304 bytesRedo Buffers 2109440 bytes

3：还原参数文件spfile

RMAN>set DBID=3990839260;RMAN>restore spfile to pfile '/home/oracle/oracle/product/10.2.0/db_1/dbs/spfileSCM2.ora' from '/app/backup/backup/backupsets/ora_cfc-3990839260-20170518-00';

参数文件恢复后，需要修改相关参数，这里由于测试缘故，我们故意在目标服务器安装ORACLE实例时，随意选择了一个路径，故意与源服务器ORACLE实例的安装路径不一致，当然，还有其它一些情况，也有可能导致你必须修改一些参数。

db_name=SCM2

db_block_size=8192

db_file_multiblock_read_count=16

sga_target=1200M

pga_aggregate_target=760M

audit_file_dest=/u01/app/oracle/admin/SCM2/adump

background_dump_dest=/u01/app/oracle/admin/SCM2/bdump

user_dump_dest=/u01/app/oracle/admin/SCM2/udump

core_dump_dest=/u01/app/oracle/admin/SCM2/cdump

control_files=('/u01/app/oracle/oradata/SCM2/control01.ctl','/u01/app/oracle/oradata/SCM2/control02.ctl','/u01/app/oracle/oradata/SCM2/control03.ctl')

compatible=10.2.0.1.0

remote_login_passwordfile=exclusive

open_cursors=300

sessions=450

processes=300

undo_management=auto

undo_tablespace=UNDOTBS1

修改后的pfile参数文件。

db_name=SCM2

db_block_size=8192

db_file_multiblock_read_count=16

sga_target=1200M

pga_aggregate_target=760M

audit_file_dest=/home/oracle/oracle/admin/SCM2/adump

background_dump_dest=/home/oracle/oracle/admin/SCM2/bdump

user_dump_dest=/home/oracle/oracle/admin/SCM2/udump

core_dump_dest=/home/oracle/oracle/admin/SCM2/cdump

control_files=('/home/oracle/oracle/oradata/SCM2/control01.ctl','/home/oracle/oracle/oradata/SCM2/control02.ctl','/home/oracle/oracle/orada

ta/SCM2/control03.ctl')

compatible=10.2.0.1.0

remote_login_passwordfile=exclusive

open_cursors=300

sessions=450

processes=300

undo_management=auto

undo_tablespace=UNDOTBS1

如果前面准备步骤，没有创建对应的udmp等目录，就会遇到下面错误

RMAN> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initSCM2.ora';RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of startup command at 05/26/2017 15:27:45RMAN-04014: startup failed: ORA-00444: background process "PMON" failed while startingORA-07446: sdnfy: bad value '' for parameter .

Problem

The path to bdump,adump or udump does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initialize parameter.

Solution

If you use pfile to start your database then edit the pfile with any editor (for example vi on unix) and either change the location of user_dump_dest or remove the parameter user_dump_dest from pfile. And then perform. startup

4:还原控制文件(control file)。

如果你实在不知道控制文件在那个备份集的那个文件，那么可以在源服务器使用list命令查看。如下所示。当然如果经验丰富或者你对备份与还原了如指掌的话，完全不用这一步骤。

RMAN> list backup of controlfile;using target database control file instead of recovery catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4 Full 7.58M DISK 00:00:00 18-MAY-17BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20170518T224247Piece Name: /u03/backup/backupsets/ora_cfc-3990839260-20170518-00Control File Included: Ckp SCN: 22876629756 Ckp time: 18-MAY-17

后续具体操作操作如下所示

RMAN> shutdown immediate;using target database control file instead of recovery catalogOracle instance shut downRMAN> exitRecovery Manager complete.[oracle@getlnx14dev ~]$ export ORACLE_SID=SCM2;[oracle@getlnx14dev ~]$ rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 29 08:41:17 2017Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: SCM2 (DBID=3990839260)RMAN>RMAN> startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initSCM2.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 1258291200 bytesFixed Size 2083624 bytesVariable Size 318768344 bytesDatabase Buffers 922746880 bytesRedo Buffers 14692352 bytesRMAN>RMAN>restore controlfile from '/app/backup/backup/backupsets/ora_cfc-3990839260-20170518-00';Starting restore at 26-MAY-17allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=437 devtype=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:03output filename=/home/oracle/oracle/oradata/SCM2/control01.ctloutput filename=/home/oracle/oracle/oradata/SCM2/control02.ctloutput filename=/home/oracle/oracle/oradata/SCM2/control03.ctlFinished restore at 26-MAY-17RMAN>

5：启动数据库到mount状态

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1RMAN>

6：检查数据库备份有效性

RMAN> crosscheck backup;allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=437 devtype=DISKcrosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944345823_s1_s1 recid=1 stamp=944345824crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944345828_s2_s1 recid=2 stamp=944345829crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944347364_s3_s1 recid=3 stamp=944347365Crosschecked 3 objects

通过下面这个命令可以将备份集注册到控制文件。catalog start with命令将以前的备份集信息重新导入到当前控制文件中, 一般应用于RMAN恢复, 控制文件又是旧的或者是手工创建的(这样的控制文件当然没有最新的备份集的信息),或者恢复目录不一致的情况, 通过catalog start with 可以将最新的备份集以及归档日志文件列表导入到控制文中, 然后就可以进行rman的恢复了.

RMAN> catalog start with '/app/backup/backup';searching for all files that match the pattern /app/backup/backupList of Files Unknown to the Database=====================================File Name: /app/backup/backup/backuplogs/rman_backup_db_EELSCM2_2017-05-18.logFile Name: /app/backup/backup/backupsets/ora_df944345828_s2_s1File Name: /app/backup/backup/backupsets/ora_df944347364_s3_s1File Name: /app/backup/backup/backupsets/ora_cfc-3990839260-20170518-00File Name: /app/backup/backup/backupsets/controlfile.copyFile Name: /app/backup/backup/backupsets/ora_df944345823_s1_s1Do you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /app/backup/backup/backupsets/ora_df944345828_s2_s1File Name: /app/backup/backup/backupsets/ora_df944347364_s3_s1File Name: /app/backup/backup/backupsets/ora_cfc-3990839260-20170518-00File Name: /app/backup/backup/backupsets/controlfile.copyFile Name: /app/backup/backup/backupsets/ora_df944345823_s1_s1List of Files Which Where Not Cataloged=======================================File Name: /app/backup/backup/backuplogs/rman_backup_db_EELSCM2_2017-05-18.logRMAN-07517: Reason: The file header is corruptedRMAN> crosscheck backup;using channel ORA_DISK_1crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944345823_s1_s1 recid=1 stamp=944345824crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/app/backup/backup/backupsets/ora_df944345823_s1_s1 recid=7 stamp=945013360crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944345828_s2_s1 recid=2 stamp=944345829crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/app/backup/backup/backupsets/ora_df944345828_s2_s1 recid=4 stamp=945013360crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/u03/backup/backupsets/ora_df944347364_s3_s1 recid=3 stamp=944347365crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/app/backup/backup/backupsets/ora_df944347364_s3_s1 recid=5 stamp=945013360crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/app/backup/backup/backupsets/ora_cfc-3990839260-20170518-00 recid=6 stamp=945013360Crosschecked 7 objectsRMAN> restore database preview summary;Starting restore at 26-MAY-17using channel ORA_DISK_1List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---2 B F A DISK 18-MAY-17 1 1 YES TAG20170518T221708List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- --------------- ------- ------- ---------- ---3 B A A DISK 18-MAY-17 1 1 YES TAG20170518T224244Media recovery start SCN is 22876629141Recovery must be done beyond SCN 22876629141 to clear data files fuzzinessFinished restore at 26-MAY-17RMAN>

7： Restore database

如果要将数据文件还原到不同的地方(恢复路径不同)，那么就要用set命令指定新位置。并且使用switch datafile all将信息更新的到控制文件。否则就可以直接使用restore database命令。

run{set newname for datafile 1 to "/data/scm2/system01.dbf";set newname for datafile 2 to "/data/scm2/undotbs01.dbf";set newname for datafile 3 to "/data/scm2/sysaux01.dbf";set newname for datafile 4 to "/data/scm2/users01.dbf";set newname for datafile 5 to "/data/scm2/bookt_d01.dbf";set newname for datafile 6 to "/data/scm2/data_consol_d01.dbf";set newname for datafile 7 to "/data/scm2/data_consol_x01.dbf";set newname for datafile 8 to "/data/scm2/escmowner_d01.dbf";set newname for datafile 9 to "/data/scm2/escmowner_x01.dbf";set newname for datafile 10 to "/data/scm2/escmowner_x02.dbf";set newname for datafile 11 to "/data/scm2/escmowner_x03.dbf";set newname for datafile 12 to "/data/scm2/escmupdate_d01.dbf";set newname for datafile 13 to "/data/scm2/escmupdate_x01.dbf";set newname for datafile 14 to "/data/scm2/gent_d03.dbf";set newname for datafile 15 to "/data/scm2/gent_d01.dbf";set newname for datafile 16 to "/data/scm2/gent_d02.dbf";set newname for datafile 17 to "/data/scm2/gent_x01.dbf";set newname for datafile 18 to "/data/scm2/gsot_d01.dbf";set newname for datafile 19 to "/data/scm2/inventory_d01.dbf";set newname for datafile 20 to "/data/scm2/inventory_d02.dbf";set newname for datafile 21 to "/data/scm2/inventory_x01.dbf";set newname for datafile 22 to "/data/scm2/mit_d01.dbf";set newname for datafile 23 to "/data/scm2/ppot_d01.dbf";set newname for datafile 24 to "/data/scm2/sct_d01.dbf";set newname for datafile 25 to "/data/scm2/smt_d01.dbf";set newname for datafile 26 to "/data/scm2/smt_x01.dbf";set newname for datafile 27 to "/data/scm2/statspack_d01.dbf";set newname for datafile 28 to "/data/scm2/stat_d01.dbf";set newname for datafile 29 to "/data/scm2/stat_x01.dbf";set newname for datafile 30 to "/data/scm2/tna_d01.dbf";set newname for datafile 31 to "/data/scm2/tna_x01.dbf";set newname for datafile 32 to "/data/scm2/tracking_d01.dbf";set newname for datafile 33 to "/data/scm2/gent_d04.dbf";set newname for datafile 34 to "/data/scm2/ANTIDUMP_D02.dbf";set newname for datafile 35 to "/data/scm2/ANTIDUMP_x01.dbf";set newname for datafile 36 to "/data/scm2/escmupdate_d02.dbf";set newname for datafile 37 to "/data/scm2/users02.dbf";set newname for datafile 38 to "/data/scm2/escmupdate_d03.dbf";set newname for datafile 39 to "/data/scm2/TDC_SHIPING_DATA01.dbf";set newname for datafile 40 to "/data/scm2/TDC_SHIPING_IDX01.dbf";set newname for datafile 41 to "/data/scm2/escmowner_d02.dbf";restore database ;switch datafile all;}

8 Recover database;

RMAN> recover database;Starting recover at 28-MAY-17using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=7242channel ORA_DISK_1: reading from backup piece /app/backup/backup/backupsets/ora_df944347364_s3_s1channel ORA_DISK_1: restored backup piece 1piece handle=/app/backup/backup/backupsets/ora_df944347364_s3_s1 tag=TAG20170518T224244channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archive log filename=/home/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7242_720647799.dbf thread=1 sequence=7242unable to find archive logarchive log thread=1 sequence=7243RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/28/2017 22:13:21RMAN-06054: media recovery requesting unknown log: thread 1 seq 7243 lowscn 22876629745RMAN>

这里是提醒恢复到一个未知的scn号。在alter database mount之后，通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。

9：处理redo log和temp表空间

如前面所说，如果实例安装路径不同，或者redo log和临时表空间对应的文件在目标服务器上找不到。那么就必须操作下面

SQL> col status for a7SQL> col type for a7;SQL> col member for a64;SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- ---------------------------------------------------------------- ---4 ONLINE /u01/app/oracle/oradata/SCM2/redo04.log NO3 ONLINE /u01/app/oracle/oradata/SCM2/redo03.log NO2 ONLINE /u01/app/oracle/oradata/SCM2/redo02.log NO1 ONLINE /u01/app/oracle/oradata/SCM2/redo01.log NOSQL>SQL> alter database rename file '/u01/app/oracle/oradata/SCM2/redo01.log'2 to '/home/oracle/oracle/oradata/SCM2/redo01.log';Database altered.SQL> alter database rename file '/u01/app/oracle/oradata/SCM2/redo02.log'2 to '/home/oracle/oracle/oradata/SCM2/redo02.log';Database altered.SQL> alter database rename file '/u01/app/oracle/oradata/SCM2/redo03.log'2 to '/home/oracle/oracle/oradata/SCM2/redo03.log';Database altered.SQL> alter database rename file '/u01/app/oracle/oradata/SCM2/redo04.log'2 to '/home/oracle/oracle/oradata/SCM2/redo04.log';Database altered.SQL>

如果不处理，否则就会出现这样的错误：

RMAN> alter database open resetlogs;RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of alter db command at 05/26/2017 16:28:37ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/SCM2/redo01.log'ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directory

SQL> col name for a80;SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/SCM2/temp01.dbfSQL> alter database tempfile '/u01/app/oracle/oradata/SCM2/temp01.dbf' drop;SQL> alter tablespace temp2 add tempfile '/home/oracle/oracle/oradata/temp01.dbf'3 size 200M4 autoextend on5 next 128M6 maxsize 4G;Tablespace altered.

10：用open resetlogs 打开数据库

正常情况下应该是

SQL> alter database open resetlogs;Database altered.

但是，这个是从32位数据库还原到64bit数据库，那么就可能出现下面情况。

RMAN> alter database open resetlogs;database openedRMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT rowRMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rowsORACLE error from target database:ORA-06553: PLS-801: internal error [56319]

具体的告警日志如下。

Completed: alter database open resetlogsSun May 28 22:34:47 2017Errors in file /home/oracle/oracle/admin/SCM2/bdump/scm2_mmon_20272.trc:ORA-06553: PLS-801: internal error [56319]Sun May 28 22:34:47 2017Errors in file /home/oracle/oracle/admin/SCM2/bdump/scm2_mmon_20272.trc:ORA-06553: PLS-801: internal error [56319]Sun May 28 22:34:47 2017Starting background process CJQ0CJQ0 started with pid=19, OS id=21562Sun May 28 22:34:50 2017Errors in file /home/oracle/oracle/admin/SCM2/bdump/scm2_j000_21565.trc:ORA-12012: error on auto execute of job 42567ORA-06553: PLS-ORA-06553: PLS-801: internal error [56319]:Sun May 28 22:34:50 2017Errors in file /home/oracle/oracle/admin/SCM2/bdump/scm2_j001_21567.trc:ORA-12012: error on auto execute of job 42568ORA-06553: PLS-ORA-06553: PLS-801: internal error [56319]:Sun May 28 22:34:50 2017Errors in file /home/oracle/oracle/admin/SCM2/bdump/scm2_j002_21569.trc:ORA-12012: error on auto execute of job 5329ORA-06544: PL/SQL: internal error, arguments: [ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []ORA-06553: PLS-801: internal error [56319]], [], [], [], [], [], [], []