Oracle异机恢复

# 恢复环境说明
1. 强制采用相同数据库名和实例名
2. 但是数据路径是不一致的
- 源数据库： +HNCJCW/HNCJCW/...
- 目的数据库： +ORADATA001/HNCJCW/...

# 基本信息确认

- 确认是oranode27和oranode28上的两个实例做的RAC
mysql> select id,agent_id,agent_name,database_name,instance_name,sys_passwd from t_oracle_instance where instance_name like "%hncjcw%";
+-----+--------------------------------------+------------+---------------+---------------+------------+
| id | agent_id | agent_name | database_name | instance_name | sys_passwd |
+-----+--------------------------------------+------------+---------------+---------------+------------+
| 246 | 1f4fc60b-3093-48be-8b32-d5712c8ca7ca | dbn01 | hncjcw | hncjcw1 | hncjcwsys |
| 248 | A9AE5B15-240B-4F00-9E48-01928ED5451B | oranode27 | hncjcw | hncjcw1 | hncjcwsys |
| 249 | 94152990-414f-49c2-a6e3-d08ff3af0ba0 | oranode28 | hncjcw | hncjcw2 | hncjcwsys |
+-----+--------------------------------------+------------+---------------+---------------+------------+

 

我们数据库中

select id,agent_id,agent_name,database_name,instance_name,sys_passwd from t_oracle_instance;
+----+--------------------------------------+------------+---------------+---------------+------------+
| id | agent_id | agent_name | database_name | instance_name | sys_passwd |
+----+--------------------------------------+------------+---------------+---------------+------------+
| 1 | 4f1cd913-2940-42b7-ab80-6a896d039ab2 | zzxtdb2 | HISU | HISU2 | oracle |
| 2 | d5829814-be31-47ad-97b7-18d1b3ef3986 | zzxtdb1 | HISU | HISU1 | oracle |
+----+--------------------------------------+------------+---------------+---------------+------------+


实际
数据库名：HISU
实例名：HISU1

 


- 数据路径如下：是GFS的路径 所以可能在一台storage上看不到数据，从客户端或者直接mount相应的GFS的volume能够看到全部数据
select * from t_oracle_backups where instance_id=248 order by create_time\G;

/nfs/168afa6b/c-1565502021-20200414-01

 

- 客户端的登录信息
172.21.21.87/88

ssh oracle@172.21.21.87 oracle/Oraclepw@CpqR root/Rootpw@CpqR

ssh root@10.20.17.34


# 恢复测试机准备

- 地址信息
172.21.30.51 root/Cgsl@123

- 创建数据目录 将备份数据拷贝到该目录下 用以恢复 -> 如果数据量比较大的话 只能通过mount方式了
mkdir /newstart
chown -R oracle:oinstall /newstart

- 强制采用相同数据库名和实例名

- libobk.so准备
cp libobk.so /u01/app/oracle/product/12.1.0/db_1/lib/
chown -R oracle:oinstall /u01/app/oracle/product/12.1.0/db_1/lib/libobk.so

- 查看可用实例
ps -ef | grep pmon

- 我们就选择hncjcw1用来恢复吧 -> 同名实例操作会方便很多 既然是异机恢复 为啥实例名要搞得不一致 这不为难自己么
export ORACLE_SID=hncjcw1

先开启归档模式
shutdown immediate;
startup nomount;
alter database mount;
alter database archivelog;
alter database open;


# 恢复的时候要保证RAC的两个实例都处于关闭状态
- 新建好的实例 需要先关闭下
export ORACLE_SID=hncjcw1
rman target /
SHUTDOWN IMMEDIATE

- 重新进入
export ORACLE_SID=hncjcw1
export BACKUP_DIR=/nfs/168afa6b/


export ORACLE_SID=orcl
export BACKUP_DIR=/nfs/513c2be3/
rman target /

# pfile的准备

说明：
1. 如果源数据库和目的数据库的场景差不多的话 则直接复制源数据库的pfile即可，如果是RAC，spfile在ASM上面的话，可以通过：
- create pfile='/tmp/pfile.ora' from spfile; 来导出当前的pfile
2. 如果源数据库不存在了，或者就想使用备份时候的那个spile的话，则可以通过恢复的方式获取：
- 因为是sbt方式备份的 所以要用sbt的方式来恢复

startup nomount;
run {
ALLOCATE CHANNEL 'ch1' TYPE sbt;
restore spfile to pfile '/u01/app/oracle/product/12.1.0/db_1/dbs/old.ora' from '/newstart/c-3588461853-20190604-00';
release channel ch1;
}


run {
ALLOCATE CHANNEL 'ch1' TYPE sbt;
restore spfile to pfile '/home/oracle/backups.ora' from '/nfs/513c2be3/c-1398055727-20200415-01';
release channel ch1;
}

 

- 除非源数据库和目的数据库的配置一模一样，否则pfile还是需要根据实际情况进行修改的

vim /u01/app/oracle/product/12.1.0/db_1/dbs/old.ora

几个重要的参数
audit_file_dest -> 这个路径一般在本地
control_files -> 这个路径一般在ASM上 所以需要根据实际情况修改
db_create_file_dest -> 这个路径一般在ASM上 所以需要根据实际情况修改
db_recovery_file_dest -> 这个路径一般在ASM上 所以需要根据实际情况修改
db_recovery_file_dest_size
db_name -> 如果数据库名不一致的话，就不要进行异机恢复了吧 这特么不是自己嫖自己么
db_unique_name


# 使用修改整理好的pfile将数据库启动到nomount状态
rman target /
SHUTDOWN IMMEDIATE;
startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/old.ora';


startup nomount pfile='/home/oracle/backups.ora';

- DBID根据c-3588461853-20190604-00这个文件的文件名获取
set DBID=3588461853;

set DBID=1398055727;


set DBID=2254762787;
常见报错1: pfile -> /u01/app/oracle/product/12.1.0/db_1/dbs/old.ora 中指定的数据不存在 要么先创建之 要么修改pfile

channel ch1: restoring control file
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/05/2019 16:25:09
ORA-19870: error while restoring backup piece /newstart/c-3588461853-20190604-00
ORA-19504: failed to create file "+HNCJCW"
ORA-17502: ksfdcre:4 Failed to create file +HNCJCW
ORA-15001: diskgroup "HNCJCW" does not exist or is not mounted
ORA-15001: diskgroup "HNCJCW" does not exist or is not mounted

实际当中我们是把+HNCJCW改成了+ORADATA001


# 恢复控制文件

run {
ALLOCATE CHANNEL 'ch1' TYPE sbt;
restore controlfile from '/backup-oracle/16548633/c-2254762787-20190830-01';
release channel ch1;
}


run {
ALLOCATE CHANNEL 'ch1' TYPE sbt;
restore controlfile from '/nfs/513c2be3/c-1398055727-20200415-01';
release channel ch1;
}


# 恢复数据文件，即执行restore操作 -> 备份的时候datafile的路径是'+HNCJCW/HNCJCW/DATAFILE/system.258.994249735', 所以这里要set newname一下
run {
ALLOCATE CHANNEL c1 TYPE sbt;
set newname for datafile 00001 to "+ORADATA001/HNCJCW/DATAFILE/system.258.994249735";
set newname for datafile 00002 to "+ORADATA001/HNCJCW/DATAFILE/hnsycs.286.995131679";
set newname for datafile 00003 to "+ORADATA001/HNCJCW/DATAFILE/sysaux.257.994249699";
set newname for datafile 00004 to "+ORADATA001/HNCJCW/DATAFILE/undotbs1.260.994249771";
set newname for datafile 00005 to "+ORADATA001/HNCJCW/DATAFILE/undotbs2.268.994249953";
set newname for datafile 00006 to "+ORADATA001/HNCJCW/DATAFILE/users.259.994249771";
set newname for datafile 00007 to "+ORADATA001/HNCJCW/DATAFILE/ufgovu8.298.995195849";
set newname for datafile 00008 to "+ORADATA001/HNCJCW/DATAFILE/users.1613.999172301";
set newname for datafile 00009 to "+ORADATA001/HNCJCW/DATAFILE/users.1604.999172303";
set newname for datafile 00010 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts1.1598.1001155371";
set newname for datafile 00011 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts2.1496.1001155373";
set newname for datafile 00012 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts3.1587.1001155373";
set newname for datafile 00013 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts4.1494.1001155373";
set newname for datafile 00014 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts5.1593.1001155373";
set newname for datafile 00015 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts6.1591.1001155373";
set newname for datafile 00016 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwts7.1489.1001155373";
set newname for datafile 00017 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts1.767.1002188409";
set newname for datafile 00018 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts2.763.1002188913";
set newname for datafile 00019 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts3.774.1002188999";
set newname for datafile 00020 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts4.773.1002189057";
set newname for datafile 00021 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts5.1076.1002189159";
set newname for datafile 00022 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts6.3348.1002189207";
set newname for datafile 00023 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts7.3350.1002189265";
set newname for datafile 00024 to "+ORADATA001/HNCJCW/DATAFILE/hncjcsts8.3352.1002189311";
set newname for datafile 00025 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwcs.550.1002714209";
set newname for datafile 00026 to "+ORADATA001/HNCJCW/DATAFILE/hncjcwcx.1479.1009537035";
restore database;
switch datafile all;
release channel c1;
}


catalog start with '/ceshi/4fb780d2';


rman pid 67913

alter database mount;

run {
ALLOCATE CHANNEL c1 TYPE sbt;
restore database;
release channel c1;
}


# 执行recover操作
run {
ALLOCATE CHANNEL c1 TYPE sbt;
set until time "to_date('2019-06-05 12:00:00','yyyy-mm-dd HH24:MI:SS')";
recover database;
release channel c1;
}

 

run {
ALLOCATE CHANNEL c1 TYPE sbt;
set until time "to_date('2020-04-16 18:24:05','yyyy-mm-dd HH24:MI:SS')";
recover database;
release channel c1;
}

create pfile='/home/init.ora' from spfile;

 

 

- 常见错误1：RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 33973 and starting SCN of 598280399
分析：这个问题没有深入去查原因了，很可惜，后面只是通过强制丢弃的方式来解决

解决方式：指定到不出错的那个sequence 但有可能会丢失数据哦
run {
ALLOCATE CHANNEL c1 TYPE sbt;
set until sequence 33973 thread 1;
recover database;
release channel c1;
}

 

run {
ALLOCATE CHANNEL c1 TYPE sbt;
set until sequence 1065929 thread 1;
recover database;
release channel c1;
}

recover database until scn =1065929;


- 常见错误2：ORA-19809: limit exceeded for recovery files
分析：归档空间太小了，需要扩大点 -> 就是前面的pfile里面指定的

解决方式：把归档日志的目录调大
SQL> show parameter db_recovery;
SQL> alter system set db_recovery_file_dest_size=200G SID='*';


- 常见错误3：日志文件目录不对

-- 如果只是重做日志的话 可以丢弃掉
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;

- 查询下哪些online log路径需要改的
SELECT member FROM v$logfile;

ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_21.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_21.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_22.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_22.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_11.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_11.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_12.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_12.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_31.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_31.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_32.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_32.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_41.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_41.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_42.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_42.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_51.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_51.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_52.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_52.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_61.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_61.log';
ALTER DATABASE RENAME FILE '+HNCJCW/HNCJCW/ONLINELOG/group_62.log' TO '+ORADATA001/HNCJCW/ONLINELOG/group_62.log';

- 貌似后面的这些可以不操作
+HNCJCW/HNCJCW/ONLINELOG/group_7.1476.1009745187
+HNCJCW/HNCJCW/ONLINELOG/group_7.3433.1009745187
+HNCJCW/HNCJCW/ONLINELOG/group_8.766.1009745235
+HNCJCW/HNCJCW/ONLINELOG/group_8.3120.1009745235
+HNCJCW/HNCJCW/ONLINELOG/group_9.2665.1009745235
+HNCJCW/HNCJCW/ONLINELOG/group_9.302.1009745235
+HNCJCW/HNCJCW/ONLINELOG/group_10.1109.1009745235
+HNCJCW/HNCJCW/ONLINELOG/group_10.2669.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_11.3197.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_11.3194.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_12.470.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_12.3205.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_13.326.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_13.3345.1009745237
+HNCJCW/HNCJCW/ONLINELOG/group_14.3408.1009745239
+HNCJCW/HNCJCW/ONLINELOG/group_14.1140.1009745239


- 通过阶段日志得方式开启数据库
alter database open resetlogs;

 

select name from v$database;
select instance_name from v$instance;
Select * FROM DBA_DATA_FILES;


看ASM上存储的信息

su - grid
asmcmd
ASMCMD> ls
DATAC1/
DBFS_DG/
RECOC1/
ASMCMD>

 

posted @ 2020-04-17 16:24  士官长  阅读(312)  评论(0)  编辑  收藏  举报