昨天在客户那里做了一次rman异机的恢复，把生产库弄一份给测试库用，总库大概80G，总共花费了2个小时，当时客户的环境是windows 11.2.0.3，今天早晨在linux下重新测试了一下，记录下来供大家参考 环境: 所有操作都是按主机名区分 源库: 主机名 bre1 实例名为
昨天在客户那里做了一次rman异机的恢复，把生产库弄一份给测试库用，总库大概80G，总共花费了2个小时，当时客户的环境是windows 11.2.0.3，今天早晨在linux下重新测试了一下，记录下来供大家参考
环境:
所有操作都是按主机名区分
源库:
主机名 bre1 实例名为bre1 数据文件目录在/u01/app/oradata下
目标库:
主机名 bre2 数据文件准备放在/bre1/oradata下
废话不多说，下面是实际操作步骤
1.在源库创建备份集，并且拷贝到目标库
拷贝的目录可用和原来的备份的时候的目录一致，也可以不一致
备份system表空间rman会自动备份参数文件和控制文件
RMAN> backup database format '/home/oracle/%U.bak'; Starting backup at 21-AUG-2014 10:03:35 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/bre1/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/bre1/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/bre1/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/bre1/test.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/bre1/users01.dbf channel ORA_DISK_1: starting piece 1 at 21-AUG-2014 10:03:36 channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:51 piece handle=/home/oracle/03pggb7o_1_1.bak tag=TAG20140821T100336 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 21-AUG-2014 10:03:52 channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:53 piece handle=/home/oracle/04pggb87_1_1.bak tag=TAG20140821T100336 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 21-AUG-2014 10:03:53 [oracle@bre2 ~]$ scp bre1:/home/oracle/03pggb7o_1_1.bak . The authenticity of host 'bre1 (192.168.56.45)' can't be established. RSA key fingerprint is 73:56:4c:3a:01:3f:50:c8:d8:3a:5d:d5:21:00:6a:fe. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'bre1,192.168.56.45' (RSA) to the list of known hosts. oracle@bre1's password: 03pggb7o_1_1.bak 100% 336MB 37.3MB/s 00:09 [oracle@bre2 ~]$ scp bre1:/home/oracle/04pggb87_1_1.bak . oracle@bre1's password: 04pggb87_1_1.bak 100% 9600KB 9.4MB/s 00:002.还原spfile，使用nomount打开数据库
编辑一个最简单的pfile，只有db_name即可：
[oracle@bre2 dbs]$ vi initbre1.ora [oracle@bre2 dbs]$ cat initbre1.ora db_name=bre1然后就可以nomount打开数据库了：
SQL> startup nomount; ORACLE instance started. Total System Global Area 229683200 bytes Fixed Size 2251936 bytes Variable Size 171967328 bytes Database Buffers 50331648 bytes Redo Buffers 5132288 bytes SQL>再使用rman来恢复spfile：
RMAN> restore spfile from '/home/oracle/backup/04pggb87_1_1.bak'; Starting restore at 21-AUG-2014 9:39:31 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=171 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/04pggb87_1_1.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 21-AUG-2014 9:39:32 RMAN>使用spfile来打理数据库试试
[oracle@bre2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 10:08:01 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora' SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora' SQL> startup nomount; ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 2: No such file or directory Additional information: 9925有很多相关目录没有创建，可以使用strings spfilebre1.ora来查看一下spfile里面的内容，建好相关的目录，并且赋予权限。。这些都搞好后nomount打开数据库不再报错。
3.恢复控制文件
登陆到rman，控制文件还原的时候将还原到参数文件指定的位置，所以参数文件指定的控制文件目录必须存在，否则报错，例如下面一开始我没有创建就报错，后来创建完成就不报错了，如果你想还原到其他地方，可以修改参数文件指定到相应地址。
RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak'; Starting restore at 21-AUG-2014 10:26:14 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/21/2014 10:26:15 ORA-19870: error while restoring backup piece /home/oracle/04pggb87_1_1.bak ORA-19504: failed to create file "/u01/app/oracle/oradata/bre1/control01.ctl" ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak'; Starting restore at 21-AUG-2014 10:26:50 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/21/2014 10:26:51 ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/bre1/control02.ctl" ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 ORA-19600: input file is control file (/u01/app/oracle/oradata/bre1/control01.ctl) ORA-19601: output file is control file (/u01/app/oracle/fast_recovery_area/bre1/control02.ctl) RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak'; Starting restore at 21-AUG-2014 10:27:24 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/bre1/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/bre1/control02.ctl Finished restore at 21-AUG-2014 10:27:25
4.mount数据库,注册备份集
重启数据库，直接就可以启动到mount
SQL> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 503319632 bytes Database Buffers 322961408 bytes Redo Buffers 6565888 bytes Database mounted. SQL>如果此时备份集所在文件目录改变，需要手工catalog注册备份集，这里我们将拷过来的备份文件复制到/home/oracle/backup下，演示一下手工catalog备份集
[oracle@bre2 ~]$ ls 03pggb7o_1_1.bak 04pggb87_1_1.bak pfile.ora test [oracle@bre2 ~]$ mkdir backup [oracle@bre2 ~]$ mv *.bak backup [oracle@bre2 ~]$ ls backup 03pggb7o_1_1.bak 04pggb87_1_1.bak RMAN> catalog backuppiece '/home/oracle/backup/03pggb7o_1_1.bak'; Starting implicit crosscheck backup at 21-AUG-2014 10:32:10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK Crosschecked 1 objects Finished implicit crosscheck backup at 21-AUG-2014 10:32:10 Starting implicit crosscheck copy at 21-AUG-2014 10:32:10 using channel ORA_DISK_1 Finished implicit crosscheck copy at 21-AUG-2014 10:32:11 searching for all files in the recovery area cataloging files... no files cataloged cataloged backup piece backup piece handle=/home/oracle/backup/03pggb7o_1_1.bak RECID=2 STAMP=856175531 RMAN> catalog backuppiece '/home/oracle/backup/04pggb87_1_1.bak'; cataloged backup piece backup piece handle=/home/oracle/backup/04pggb87_1_1.bak RECID=3 STAMP=8561755505.使用set newname将数据文件还原到不同目录
原来的数据文件目录在/u01/admin/oradata下面,我们将数据文件恢复到/bre1/oradata下面，%b的意思是只获取文件名，没有目录信息
[root@bre2 ~]# mkdir -p /bre1/oradata
[root@bre2 ~]# chown -R oracle:oinstall /bre1
[oracle@bre2 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 21 10:36:10 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BRE1 (DBID=2522432392, not open)
run{
allocate channel ch1 device type disk;
set newname for database to '/bre1/oradata/%b';
restore database;
release channel ch1;
6> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=396 device type=DISK
executing command: SET NEWNAME
Starting restore at 21-AUG-2014 10:38:03
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /bre1/oradata/system01.dbf
channel ch1: restoring datafile 00002 to /bre1/oradata/sysaux01.dbf
channel ch1: restoring datafile 00003 to /bre1/oradata/undotbs01.dbf
channel ch1: restoring datafile 00004 to /bre1/oradata/users01.dbf
channel ch1: restoring datafile 00005 to /bre1/oradata/test.dbf
channel ch1: reading from backup piece /home/oracle/backup/03pggb7o_1_1.bak
channel ch1: piece handle=/home/oracle/backup/03pggb7o_1_1.bak tag=TAG20140821T100336
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:45
Finished restore at 21-AUG-2014 10:38:48
released channel: ch1
数据文件都在了：
[root@bre2 ~]# ls -l /bre1/oradata
total 1564468
-rw-r----- 1 oracle oinstall 629153792 Aug 21 10:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 734011392 Aug 21 10:38 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 21 10:38 test.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug 21 10:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall 6561792 Aug 21 10:38 users01.dbf
资料上说可以使用switch datafile all来直接修改控制文件中的文件目录，但是我试了一下貌似不行，还是需要在手工rename，下面是我手工修改控制文件中的数据文件目录，先看一下当前的数据文件目录
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/bre1/system01.dbf /u01/app/oracle/oradata/bre1/sysaux01.dbf /u01/app/oracle/oradata/bre1/undotbs01.dbf /u01/app/oracle/oradata/bre1/users01.dbf /u01/app/oracle/oradata/bre1/test.dbf SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/bre1/temp01.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/bre1/redo01.log /u01/app/oracle/oradata/bre1/redo02.log /u01/app/oracle/oradata/bre1/redo03.log
我自己写了一个拼接sql来将数据文件目录做转换：
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafile
union all
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfile
union all
select 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile
;
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafile union all select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfile union all select 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile 6 ; 'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/BRE1/ORADATA'||SUBSTR(NAME,LENGTH(NAME)-INSTR(REVERSE(NAME),'/')+1,INSTR(REVERSE(NAME),'/'))||''';' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '/bre1/oradata/system01.dbf' to '/bre1/oradata/system01.dbf'; alter database rename file '/bre1/oradata/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf'; alter database rename file '/bre1/oradata/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf'; alter database rename file '/bre1/oradata/users01.dbf' to '/bre1/oradata/users01.dbf'; alter database rename file '/bre1/oradata/test.dbf' to '/bre1/oradata/test.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/temp01.dbf' to '/bre1/oradata/temp01.dbf'; alter database rename file '/bre1/oradata/redo01.log' to '/bre1/oradata/redo01.log'; alter database rename file '/bre1/oradata/redo02.log' to '/bre1/oradata/redo02.log'; alter database rename file '/bre1/oradata/redo03.log' to '/bre1/oradata/redo03.log'; alter database rename file '/u01/app/oracle/oradata/bre1/system01.dbf' to '/bre1/oradata/system01.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/users01.dbf' to '/bre1/oradata/users01.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/test.dbf' to '/bre1/oradata/test.dbf'; alter database rename file '/u01/app/oracle/oradata/bre1/redo01.log' to '/bre1/oradata/redo01.log'; alter database rename file '/u01/app/oracle/oradata/bre1/redo02.log' to '/bre1/oradata/redo02.log'; Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/bre1/redo03.log' to '/bre1/oradata/redo03.log'; Database altered.rename之后控制文件中的数据文件目录都已经改到了/bre1/oradata下，那么就可以进行恢复了。
6.恢复数据库
我这里是测试就没有恢复什么归档日志，如果是正式生产库，还需要恢复归档日志，可能还需要使用到CATALOG ARCHIVELOG 来注册归档的备份集和在RMAN中使用
RUN
{
SET ARCHIVELOG DESTINATION TO '/home/oracle';
RESTORE ARCHIVELOG all;
}
来还原出归档日志。
或者还可以在controlfile中修改归档目录来恢复。恢复出归档日志之后，就可以进行数据库恢复。
[oracle@bre2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 11:16:06 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 830841 generated at 08/21/2014 10:03:36 needed for thread 1
ORA-00289: suggestion : /arch1_42_851018056.dbf
ORA-00280: change 830841 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
open 过程中可能会遇到ora-00392错误，解决办法：
使用SQL> alter database clear logfile group 2;
到此恢复成功！
总结：
主要的难点是需要对rman比较熟悉，需要使用语句修改控制文件中的文件位置，否则会报错找不到相关文件。
如果备份集的目录改变了，需要手工catalog注册备份集到控制文件，并且rename file。
MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AMMySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand
MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AMToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith
MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AMToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters
MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AMMySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.
MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AMOptimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.
Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AMMastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.
Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AMChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic
MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AMBest practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
SublimeText3 Chinese version
Chinese version, very easy to use
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
Dreamweaver Mac version
Visual web development tools
