Oracle使用rman从单实例迁移导RAC

Oracle使用rman从单实例迁移到RAC

1、对源端数据库做全备

mkdir-p/u01/app/oracle/backup/

touch rman_back.sh
chmod +x rman_back.sh
chown oracle:oinstall rman_back.sh

#!/bin/bash

BACKUP_PATH=/home/oracle/rman_back/
mkdir -p $BACKUP_PATH

source /home/oracle/.bash_profile
export ORACLE_SID=IOTSRM

rman target / << EOF

run{
delete noprompt archivelog until time "sysdate-1/48";
sql 'alter system archive log current';
delete noprompt expired archivelog all;
crosscheck archivelog all;
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
backup as compressed backupset database format='$BACKUP_PATH/%d_%s.DB';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all FORMAT '$BACKUP_PATH/%d_%s.arch';
BACKUP CURRENT CONTROLFILE FORMAT '$BACKUP_PATH/%d_%s.ctl';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
exit
EOF

nohup sh rman_back.sh &
tail -50f nohup.out

2、创建参数文件pfile

sqlplus / as sysdba << EOF
create pfile from spfile
EOF

将备份文件和生成pfile传到目标端节点1
pfile路径:$ORACLE_HOME/dbs/

2.修改参数文件,启动到nomount，注意创建目录
修改：
*.cluster_database=false
恢复完成再设置为true

startup nomount
startup nomount pfile='/u01/app/oracle/11204/dbs/initorcl.ora'
利用pfile创建spfile文件
create spfile='+DATA/orcl/spfileorcl.ora'from pfile='/u01/app/oracle/11204/dbs/initorcl.ora';
shutdown abort;

将数据库所有节点上的spfile文件都指向共享文件上的spfile文件

1节点：
[oracle@rac1~]$echo "SPFILE='+DATA/orcl/spfileorcl.ora'">/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora
2节点：
[oracle@rac1~]$echo "SPFILE='+DATA/orcl/spfileorcl.ora'">/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora
3.恢复控制文件
startup nomount
restore controlfile from '/home/oracle/rman_back/ORCL_62.ctl';

4.将备份文件路径写入控制文件

sql 'alter database mount';
catalog start with '/u01/backup/QKS/';

查看共享磁盘状况：
SQL>colnamefora10
SQL>colstatefora10
SQL>select group_number,name,state,type,total_mb,free_mb from v$asm_diskgroup;

恢复数据库(二)

5.获取数据文件和redo路径，根据需要生成的新的数据文件的路径位置，进行文本替换，暂存记事本，后续用来编写脚本

sqlplus / as sysdaa
set pagesize 9999
set linesize 200
select 'set newname for datafile '||file#||' to '||'''new'||name||''';' "change_sql" from v$datafile;
select 'set newname for tempfile '||file#||' to '||'''new'||name||''';' "change_sql" from v$tempfile;

例：原始语句。因为迁移服务器的原因，生成的数据文件有需要更换路径的可能
set newname for datafile 1 to ‘new+DATA/iot_srm/datafile/system.295.1027542995’;
替换：确认新的生成路径，语句进行修改后暂时保存在记事本

set newname for datafile 1 to '/u01/datafile/IOTSRM/system.295.1027542995';

6.恢复数据文件，通过脚本执行恢复

rman target /

touch restore.sh
chmod +x restore.sh
chown oracle:oinstall restore.sh

#!/bin/bash

# BACKUP_PATH=/home/oracle/rman_back/
# mkdir $BACKUP_PATH

source /home/oracle/.bash_profile
export ORACLE_SID=IOTSRM

rman target / << EOF
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;

set newname for datafile 1 to '/u01/datafile/ODBIQKS/system01.dbf';
set newname for datafile 2 to '/u01/datafile/ODBIQKS/sysaux01.dbf';
set newname for datafile 3 to '/u01/datafile/ODBIQKS/undotbs01.dbf';
set newname for datafile 4 to '/u01/datafile/ODBIQKS/users01.dbf';
set newname for datafile 5 to '/u01/datafile/ODBIQKS/example01.dbf';
set newname for tempfile 1 to '/u01/datafile/ODBIQKS/temp01.tpf';

restore database;
switch datafile all;
switch tempfile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}

nohup sh restore.sh &

tail -50f nohup.out

查看归档恢复情况：

RMAN>list backup of archivelog all;

PieceName:/u01/app/oracle/backup/arc_ORCL_20170421_22_0ms28a64_1_1 ListofArchivedLogsinbackupset22
ThrdSeqLowSCNLowTimeNextSCNNextTime
-----------------------------------------------------------
1 71 6860702 2017-04-2113:10:07 6860727 2017-04-21 13:10:28

注：根据备份集可以先恢复到SCN为 6860727 ，如果直接 recover database 会因丢失部分日志报错，但可以忽略。

recover database until scn 6860727;

7、源端备份后的变化数据

7.1 准备源数据全备后变化的数据
强制切换日志

alter system switch logfile;
shutdown immediate；

7.2 在线重做日志和新归档日志拷贝到目标库rac1

scp -r./*oracle@192.168.91.140:/u01/app/oracle/archivelog
scp -r./redo*oracle@192.168.91.140:/u01/app/oracle/oradata/orcl

8.2 把归档日志注册到rman

RMAN>catalog archivelog '/u01/app/oracle/archivelog/arch_1_72_929977336.arc','/u01/app/oracle/archivelog /arch_1_73_929977336.arc';

RMAN>recover database;

9、重建redo，修改日志文件路径放置共享磁盘

oracle@rac1~]$sqlplus / as sysdba 
SQL>set line 150
SQL>col member for a50
SQL>select * from v$logfile;
GROUP# STATUS TYPEMEMBER IS_

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '+DATA';

9.启动数据库

alter database open resetlogs;
select * from v$logfile;
select open_mode from v$database;

四、配置数据库参数

1、查看数据情况
修改参数：

SQL>col value for a10
SQL>select * from v$option where parameter='Real Application Clusters';
PARAMETERVALUE
---------------------------------------------------------
Real Application Clusters TRUE


SQL>show parameter cluster
NAMETYPEVALUE
----------------------------------------------------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL>show parameter thread
NAMETYPEVALUE
-----------------------------------------------------------------------------
parallel_threads_per_cpu integer 2
thread integer 1

SQL>show parameter instance_number
NAMETYPEVALUE
-----------------------------------------------------------------------------
instance_number integer 1

SQL>alter system set cluster_database=true scope=spfile sid='*';
SQL>alter system set cluster_database_instances=2 scope=spfile sid='*';
SQL>alter system set instance_number=1 scope=spfile sid='orcl1';
SQL>alter system set instance_number=2 scope=spfile sid='orcl2';
SQL>alter system set thread=1 scope=spfile sid='orcl1';
SQL>alter system set thread=2 scope=spfile sid='orcl2';
添加日志组文件
SQL>show parameter undo_tablespace
NAMETYPEVALUE
-----------------------------------------------------------------------------
undo_tablespace string UNDOTBS1

SQL>create undo tablespace UNDOTBS2 datafile '+DATA/orcl/datafile/undotbs02.dbf' size 100M;
alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='orcl2';
alter database add logfile thread 2 group 4 ('+DATA','+FRA') size 50M;
alter database add logfile thread 2 group 5 ('+DATA','+FRA') size 50M;
alter database add logfile thread 2 group 6 ('+DATA','+FRA') size 50M;

alter database add logfile member '+FRA' to group 4;
SQL>alter database enable thread 2;
SQL>shutdown immediate

2、将其他的信息注册到CRS里

[oracle@rac1~]$srvctl add database -d orcl -o $ORACLE_HOME -p +DATA/orcl/spfileorcl.ora
[oracle@rac1~]$srvctl add instance -d orcl -i orcl1 -n rac1
[oracle@rac1~]$srvctl add instance -d orcl -i orcl2 -n rac2

3、启动rac1和rac2实例

[oracle@rac1~]$srvctl start database -d orcl -o open

4、确认实例情况

select instance_number,instance_name,host_name from v$instance;

5、执行catclust.sql脚本创建相关视图

SQL>@$ORACLE_HOME/rdbms/admin/catclust.sql 

6、重建 temp表空间
6.1 手工重建temp表空间
因为在 recover 的时候不会对temp 表空间进行recover。所以recover 后，我们要手工重建temp表空间。

[grid@rac1~]$asmcmd lsof |grep temp
orclorcl1+data/orcl/tempfile/temp.266.941900557
[oracle@rac1~]$sqlplus / as sysdba
添加临时表空间文件
SQL>alter tablespace temp add tempfile '+DATA' size 100M;
Tablespacealtered.
SQL>select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.266.941900557
+DATA/orcl/tempfile/temp.267.941901771

6.2 重启数据库再删除原temp表空间

[oracle@rac1oracle]$srvctl stop database -d orcl -o immediate
[oracle@rac1oracle]$srvctl start database -d orcl -o open
[oracle@rac1oracle]$sqlplus / as sysdba
SQL>alter database tempfile '+DATA/orcl/tempfile/temp.266.941900557' drop including datafiles;
Database altered.

7、配置集群监听
7.1 配置tnsnames.ora (所有节点执行)

[oracle@rac1~]$su - oracle
[oracle@rac1~]$cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin 
[oracle@rac1admin]$vi tnsnames.ora
ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.91.154)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=orcl)
)
)

7.2 启动监听

[oracle@rac1admin]$srvctl stop listener
[oracle@rac1admin]$srvctl start listener
[oracle@rac1admin]$srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s):rac2,rac1