Rman异地容灾恢复演练实践
Rman异地容灾恢复演练实践
演练案例情形：主库全库备份，并且备份控制文件参数文件。通过备份文件，在异地重建数据库，进行数据恢复。
步骤：
1、备份数据库 backup database
2、ftp备份到目的服务器
3、为目标数据库创建项目目录
4、为目标数据库创建pfile
5、还原控制文件
6、还原数据文件
7、OPEN 数据库，如果在open情况下备份的，恢复后必须要resetlog..，经过实验验证，在关闭情况下备份，仍然要resetlog。
1、备份数据库 backup database，要控制文件，参数也要备份进去
配置备份目录：默认目录在/home/app/oracle/fast_recovery_area/LJYDB/backupset
configure channel device type disk format '/home/oracle/rmanbak/%d_%F'; ---%F保留给控制文件命名，所以不能作为数据文件名
configure channel device type disk format '/home/oracle/rmanbak/%U_%d';
查看备份配置
Show all;
设置自动备份控制文件，才会备份控制文件和参数文件？
CONFIGURE CONTROLFILE AUTOBACKUP ON;
2、ftp备份到目的服务器
1个数据文件，1个控制文件参数文件。注意目录位置。
[oracle@db-yzcvm archivelog]$ scp /home/app/oracle/fast_recovery_area/LJYDB/autobackup/2017_11_09/o1_mf_s_959594616_f07fvr9q_.bkp 132.108.200.133:/home/oracle/ljydb
oracle@132.108.200.133's password:
o1_mf_s_959594616_f07fvr9q_.bkp 100% 10MB 9.9MB/s 00:00
[oracle@db-yzcvm archivelog]$ scp /home/oracle/rmanbak/2asj4g39_1_1_LJYDB 132.108.200.133:/home/oracle/ljydb
oracle@132.108.200.133's password:
2asj4g39_1_1_LJYDB 100% 1461MB 91.3MB/s 00:16
[oracle@db-yzcvm archivelog]$ pwd
3、为目标数据库创建项目目录
/oradata/ljydb fast_recovery_area/ljydb
4、为目标数据库创建参数文件(使用RMAN还原或复制原pfile到目的服务器)
1、Export ORACLE_SID=ljydb
2、Rman登录
3、启动实例到nomount 状态. startup force nomount;-----rman用虚拟的参数文件启动实例
4、恢复参数文件。
5、修改参数文件，各个文件目录。（如果目录不一致，要导出pfile，修改pfile里面的目录，再导入spfile）
[oracle@cvm-dbsrv02 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 9 10:16:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> show all;
3.启动实例到nomount 状态，当spfile不可用时，rman用虚拟的参数文件启动实例
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome1/dbs/initljydb.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 822085528 bytes
Database Buffers 1258291200 bytes
Redo Buffers 55255040 bytes
RMAN>
恢复spfile文件
如果目录不一致，要导出pfile，修改pfile里面的目录，再导入spfile
-------查看pfile
create pfile='/home/oracle/ljydb/pfile2from.ora' from spfile
create spfile from pfile='/home/oracle/ljydb/pfile2from.ora';
RMAN> restore spfile from '/home/oracle/ljydb/o1_mf_s_959594616_f07fvr9q_.bkp';
Starting restore at 09-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/ljydb/o1_mf_s_959594616_f07fvr9q_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-NOV-17
5、恢复控制文件并切换到mount状态
RMAN> restore controlfile from '/home/oracle/ljydb/o1_mf_s_959594616_f07fvr9q_.bkp';
Starting restore at 09-NOV-17
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=/home/oracle/app/oracle/product/11.2.0/dbhome1/dbs/cntrlljydb.dbf
Finished restore at 09-NOV-17
RMAN> alter database mount;
6、还原数据库
指定备份文件所在目录
RMAN> catalog start with '/home/oracle/ljydb/'; #此命令用于扫描整个目录的备份片或者归档日志文件等
RMAN> restore database; ----如果目录不一致，直接使用不行，要先set newname for,如下。
run{
set newname for datafile 00001 to '/home/oracle/app/oracle/oradata/ljydb/system01.dbf';
set newname for datafile 00002 to '/home/oracle/app/oracle/oradata/ljydb/sysaux01.dbf';
set newname for datafile 00003 to '/home/oracle/app/oracle/oradata/ljydb/undotbs01.dbf';
set newname for datafile 00004 to '/home/oracle/app/oracle/oradata/ljydb/users01.dbf';
set newname for datafile 00005 to '/home/oracle/app/oracle/oradata/ljydb/data01.dbf';
restore database;
switch datafile all;
}
7、恢复数据库
RMAN> recover database; ----如果存在缺少日志报错，使用until语句试试不完全恢复。
RMAN>run{
set until sequence 90;
recover database;
}
------优先使用util scn。如果使用until sequence 还源源不断出现scn不够的问题。
run{
set until scn 15548186373605;
recover database;
}
RMAN> alter database open resetlogs;
Resetlogs 打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL>
搞定！!
问题及解决办法：
1、ORA-00202:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/06/2017 16:56:06
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: '/home/oracle/app/oracle/product/11.2.0/dbhome1/dbs/cntrlljydb.dbf'
查看spfile oracle版本
create pfile='/home/oracle/ljydb/pfileljydb2' from spfile;
没有版本信息
手工修改一下版本号试试。
alter system set compatible='11.2.0.4.0' scope=spfile;
SQL> alter database mount
2 ;
Database altered.
成功Mount。
再看看pfile有没有版本号
create pfile='/home/oracle/ljydb/pfileljydb3' from spfile;
---------
ljydb.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/app/oracle/product/11.2.0/dbhome1/dbs/cntrlljydb.dbf'#Restore Controlfile
*.db_name='ljydb'
---
RMAN-06023:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/08/2017 09:52:44
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> restore database; ----如果目录不一致，直接使用不行，要先set newname for,如下。
run{
set newname for datafile 00001 to '/home/oracle/app/oracle/oradata/ljydb/system01.dbf';
set newname for datafile 00002 to '/home/oracle/app/oracle/oradata/ljydb/sysaux01.dbf';
set newname for datafile 00003 to '/home/oracle/app/oracle/oradata/ljydb/undotbs01.dbf';
set newname for datafile 00004 to '/home/oracle/app/oracle/oradata/ljydb/users01.dbf';
set newname for datafile 00005 to '/home/oracle/app/oracle/oradata/ljydb/data01.dbf';
restore database;
switch datafile all;
}
RMAN-06054 缺少归档日志
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/09/2017 11:01:12
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 90 and starting SCN of 15548186354352
在原库发现这个scn还没有归档。
手工产生一次归档，再把归档日志复制过来试试。
alter system switch logfile;
select sequence#, first_change#, next_change#, first_time, next_time, backup_count
2 from v$archived_log where sequence#=76 and thread#=1;
/home/app/oracle/fast_recovery_area/LJYDB/archivelog/2017_11_09/o1_mf_1_90_f07kpclo_.arc
scp /home/app/oracle/fast_recovery_area/LJYDB/archivelog/2017_11_09/o1_mf_1_90_f07kpclo_.arc 132.108.200.133:/home/oracle/ljydb
oracle@132.108.200.133's password:
15548186354352
把归档日志重新应用
日志注册
catalog archivelog '/home/oracle/ljydb/o1_mf_1_90_f07kpclo_.arc';
RMAN> catalog archivelog '/home/oracle/ljydb/o1_mf_1_90_f07kpclo_.arc';
cataloged archived log
archived log file name=/home/oracle/ljydb/o1_mf_1_90_f07kpclo_.arc RECID=493 STAMP=959598845
重新恢复
RMAN> recover database;
Starting recover at 09-NOV-17
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 90 is already on disk as file /home/oracle/ljydb/o1_mf_1_90_f07kpclo_.arc
archived log file name=/home/oracle/ljydb/o1_mf_1_90_f07kpclo_.arc thread=1 sequence=90
unable to find archived log
archived log thread=1 sequence=91
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/09/2017 11:17:53
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 91 and starting SCN of 15548186358803
RMAN>
=========结论：
无论怎么把日志复制过来，重新应用，都会继续产生缺少新的scn日志的问题。
所以，只能采取resetlog的方式恢复。
run{ set until sequence 91;
recover database;
}
alter database open resetlogs;
o1_mf_1_90_f07kpclo_.arc
--Recover过程中会收到如下错误提示
--我们使用until子句来再次recover
RMAN>run{
set until sequence 90;
recover database;
}
RMAN> alter database open resetlogs;
ORA-27040 ORA-00344 在线日志文件错误
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/09/2017 11:20:40
ORA-00344: unable to re-create online log '/home/app/oracle/oradata/ljydb/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
修改在线日志文件目录
alter database rename file '/home/app/oracle/oradata/ljydb/redo01.log' to '/home/oracle/app/oracle/oradata/ljydb/redo01.log';
alter database rename file '/home/app/oracle/oradata/ljydb/redo02.log' to '/home/oracle/app/oracle/oradata/ljydb/redo02.log';
alter database rename file '/home/app/oracle/oradata/ljydb/redo03.log' to '/home/oracle/app/oracle/oradata/ljydb/redo03.log';
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 allowed
ORA-00312: online log 3 thread 1:
'/home/oracle/app/oracle/oradata/ljydb/redo03.log'
ORA-00312 修改日志状态表
来源：http://www.linuxidc.com/Linux/2014-11/108886.htm
alter database clear logfile group 1;
重做日志文件的集中状态：*（摘）*
1.CURRENT - 当前正在被LGWR使用的group(同时是ACTIVE状态)，其中记载数据库中正在进行的更改，删除将导致恢复时数据丢失。
2.ACTIVE - 活动的日志文件状态，日志完成切换或等待检查点事件时都可能出于这种状态，不是CURRENT的日志但是实例恢复时是需要的，此时的日志有可能已经完成了归档，即将变成INACTIVE状态，或者日志文件在等待Checkpoint事件没有完成归档。
3.INACTIVE - 非活动的日志状态，此时的日志实例恢复时不再需要，可能已经归档。
4.UNUSED - 尚未记录change的空白日志组，一般会出现在add logfile， resetlogs打开数据库或者使用了clear logfile命令后。
5.CLEARING - 表示该组重做日志文件正在被ALTER DATABASE CLEAR LOGFILE语句重建中（重建后状态会变成unused）。
6.CLEARING_CURRENT - 表示该组重做日志文件重建时出现错误，出现对日志文件的I/O故障就会出现这种状态。
解决办法：
select group#,bytes/1024/1024||'M',status from v$log;
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL>
搞定！!
检查无效的备份文件
crosscheck backup of database;
删除无效的备份记录
delete expired backup;
查看spfile文件备份
list backup of spfile;
crosscheck backup of spfile;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/home/app/oracle/fast_recovery_area/LJYDB/archivelog/2017_11_06/o1_mf_1_77_f00tqbll_.arc thread=1 sequence=77
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/home/app/oracle/fast_recovery_area/LJYDB/archivelog/2017_11_06/o1_mf_1_78_f00vpjsv_.arc thread=1 sequence=78
RMAN-08137
----解决办法：
此为11g的bug，需要在delete后加上force关键字，metalink上有记载http://blog.itpub.net/29863023/viewspace-1543386/
delete noprompt force archivelog all completed before 'sysdate-10/1440';
DELETE force ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
