脱机异地还原（安装一个原环境相同的linux，并安装数据库，注意不要配置安装实例）
1、检查/home/oracle下的.bashrc .bash_profile内容是否与原环境一致（具体看情况而定），示例如下： # User specific aliases and functions export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_SID=orcl export ORACLE_OWNER=oracle export ORACLE_TERM=vt100 export THREADS_FLAG=native export LANG=en_US 2、要记录原数据库的BDID(此次测试的DBID=1495584919）
3、oracle用户下： [oracle@g ~]$ rman target / nocatalog RMAN> RMAN>startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 285213576 bytes Database Buffers 775946240 bytes Redo Buffers 5517312 bytes RMAN>set dbid=1495584919; RMAN>restore spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora' from '/oback/CONTROLFILE.ORCL.c-1495584919-20180217-06'; Starting restore at 17-FEB-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=396 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oback/CONTROLFILE.ORCL.c-1495584919-20180217-06 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 17-FEB-18 RMAN> startup force nomount; ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× RMAN> startup force nomount; 报错: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 02/17/2018 17:01:47 RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory cat /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora 查看 db_recovery_file_dest的参数,手工建立了fast_recovery_area目录,并修改权限为oracle.oinstall RMAN> startup force nomount; 报错: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 02/17/2018 17:05:14 RMAN-04014: startup failed: ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated ORA-07286: sksagdi: cannot obtain device information. Linux-x86_64 Error: 2: No such file or directory cat /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora 查看 LOG_ARCHIVE_DEST_1的参数,手工建立了/backup/archive/ORCL目录并修改权限为oracle.oinstall RMAN> startup force nomount; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 02/17/2018 17:10:23 RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file Linux-x86_64 Error: 2: No such file or directory Additional information: 9925 手工建立了audit_file_dest='/u01/app/oracle/admin/orcl/adump'目录并修改权限为oracle.oinstall ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× 再次执行：成功了 RMAN> startup force nomount; Oracle instance started Total System Global Area 5010685952 bytes Fixed Size 2261848 bytes Variable Size 1006636200 bytes Database Buffers 3992977408 bytes Redo Buffers 8810496 bytes RMAN>restore controlfile from '/oback/CONTROLFILE.ORCL.c-1495584919-20180217-06'; ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× 报错： Starting restore at 17-FEB-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 02/17/2018 17:18:33 ORA-19870: error while restoring backup piece /oback/CONTROLFILE.ORCL.c-1495584919-20180217-06 ORA-19504: failed to create file "/u01/app/oracle/oradata/orcl/control01.ctl" ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 手工建立了"/u01/app/oracle/oradata/orcl/"目录并修改权限为oracle.oinstall 再次执行命令 报错： channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 02/17/2018 17:21:22 ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/orcl/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/orcl/control01.ctl) ORA-19601: output file is control file (/u01/app/oracle/fast_recovery_area/orcl/control02.ctl) 手工建立了"/u01/app/oracle/fast_recovery_area/orcl/"目录并修改权限为oracle.oinstall 再次执行命令（成功） Starting restore at 17-FEB-18 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/orcl/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl Finished restore at 17-FEB-18 ××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
4、启动数据库到加载状态 RMAN>alter database mount; database mounted released channel: ORA_DISK_1 在新控制文件中注册数据文件备份和归档备份 RMAN>catalog start with '/oback/'; searching for all files that match the pattern /oback/ List of Files Unknown to the Database ===================================== File Name: /oback/backup_2bsrfda0_1_1 File Name: /oback/backup_2csrfdag_1_1 File Name: /oback/CONTROLFILE.ORCL.c-1495584919-20180217-06 File Name: /oback/backup_2dsrfdah_1_1 File Name: /oback/backup_2esrfdai_1_1 File Name: /oback/backup_29srfd9o_1_1 File Name: /oback/backup_2asrfd9p_1_1 Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /oback/backup_2bsrfda0_1_1 File Name: /oback/backup_2csrfdag_1_1 File Name: /oback/CONTROLFILE.ORCL.c-1495584919-20180217-06 File Name: /oback/backup_2dsrfdah_1_1 File Name: /oback/backup_2esrfdai_1_1 File Name: /oback/backup_29srfd9o_1_1 File Name: /oback/backup_2asrfd9p_1_1
5、还原数据库 RMAN> restore database; Starting restore at 17-FEB-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: reading from backup piece /oback/backup_2asrfd9p_1_1 channel ORA_DISK_1: piece handle=/oback/backup_2asrfd9p_1_1 tag=TAG20180217T153313 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: reading from backup piece /oback/backup_2bsrfda0_1_1 channel ORA_DISK_1: piece handle=/oback/backup_2bsrfda0_1_1 tag=TAG20180217T153313 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /oback/backup_2csrfdag_1_1 channel ORA_DISK_1: piece handle=/oback/backup_2csrfdag_1_1 tag=TAG20180217T153313 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: reading from backup piece /oback/backup_2dsrfdah_1_1 channel ORA_DISK_1: piece handle=/oback/backup_2dsrfdah_1_1 tag=TAG20180217T153313 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 17-FEB-18
6、恢复数据 RMAN> recover database; ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× 报错： starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=55 channel ORA_DISK_1: reading from backup piece /oback/backup_2esrfdai_1_1 channel ORA_DISK_1: piece handle=/oback/backup_2esrfdai_1_1 tag=TAG20180217T153338 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/USE_DB_RECOVER_FILE_DEST1_55_967804185.dbf thread=1 sequence=55 unable to find archived log archived log thread=1 sequence=56 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/17/2018 17:35:32 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 56 and starting SCN of 1681835 可见，出先此错误的原因是恢复需要的日志记录在控制文件或恢复目录中找不到。解决方法分两种情况： 1.如果相关的日志存在且可用的话，就将此日志记录添加到控制文件或恢复目录中。 2.如果相关的日志已经被删除了或不可用了，那么就按照错误的提示scn将数据库恢复到此scn，本案例是1681835。 也就是说此时数据库只能进行不完全恢复了，在打开数据库时得使用resetlogs打开。 执行： RMAN> recover database until scn 1681835; ×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××× 重置重做日志，即将重做日志的sequence置零 RMAN> alter database open resetlogs;
7、增加监听 复制原来的文件过来 提示用户密码错误无法登陆，查看密码文件，提示缺少 SQL> show parameter remote_login; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE SQL> ho ls $ORACLE_HOME/dbs/orapw*; ls: cannot access /u01/app/oracle/product/11.2.0/db_1/dbs/orapw*: No such file or directory 使用orapwd重建当前数据库密码文件 SQL> ho orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10 重置sys密码： SQL> password sys; Changing password for sys Old password: New password: Retype new password: Password changed 用户解锁：改密码等 create user test identified by "111111"; grant connect,resource,sysdba to test alter user SCOTT account unlock identified by 111111;
-----end-------