Oracle(11g)单实例----RAC(11g)迁移模拟测试
Oracle11.2.0.4单实例----Oracle19C-RAC迁移模拟测试
总体思想：通过RMAN物理备份在备库恢复主库数据，后续通过在主库停业务后，将从备份后的所有日志都同步到新库应用，重新配置数据库为集群数据库。此迁移涉及停机时间主要在停机后日志生成量决定，这些日志需要同步到备库同时应用这些日志，日志量越少恢复时间越快，本文仅做实施可行性测试，解决技术问题，发现潜在故障，后续需要在生产库做准迁移测试再确定具体迁移导致的停机时间。
1 新库搭建好RAC，存储空间足够，使用ASM存储数据库文件。
2 源库做物理热备
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter system archive log current';
5> backup database format '/archivelog/backup/full_%d_%T_%s_%U' include current controlfile plus archivelog delete all input;
6> release channel c1;
7> release channel c2;
8> crosscheck backup;
9> delete noprompt expired backup;
10> report obsolete;
11> delete noprompt obsolete;
12> delete noprompt archivelog all;
13> }
3修改实例参数.如下修改，注意文件目录要存在
*.audit_file_dest='/oracle/db/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+ASMVG1/prod/controlfile/control01.ctl','+ASMVG1/prod/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/oracle/db/diag'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_dest_1='location=+ARCH/prod/ARCHIVELOG '
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
cluster_database=false
prod1.undo_tablespace='UNDOTBS1'
prod2.undo_tablespace='UNDOTBS2'
prod1.thread=1
prod2.thread=2
prod1.instance_number=1
prod2.instance_number=2
prod1.instance_name='prod1'
prod2.instance_name='prod2'
启动实例
RMAN> startup nomount pfile='/oracle/db/backup/pfile.ora';
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
创建spfile
SYS@prod1>create spfile='+asmvg1/prod/parameterfile/spfileprod.ora' from pfile='/oracle/db/backup/pfile.ora';
File created.
Elapsed: 00:00:00.23
创建密码文件
[oracle@rac1 dbs]$ orapwd file=/oracle/db/product/11.2/dbs/orapwprod1 password=oracle1234
[oracle@rac2 dbs]$ orapwd file=/oracle/db/product/11.2/dbs/orapwprod2 password=oracle1234
恢复控制文件
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 13 15:44:02 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@prod2>startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
由于报错如下
RMAN> restore controlfile from '/oracle/db/backup/full_PROD_20220513_5_050tde3h_1_1';
Starting restore at 13-MAY-22
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 05/13/2022 15:51:56
ORA-19697: standby control file not found in backup set
手工备份一个如下，以后单独执行一个备份
RMAN> backup current controlfile;
Starting backup at 13-MAY-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
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
channel ORA_DISK_1: starting piece 1 at 13-MAY-22
channel ORA_DISK_1: finished piece 1 at 13-MAY-22
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2022_05_13/o1_mf_ncnnf_TAG20220513T155411_k7w3o513_.bkp tag=TAG20220513T155411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAY-22
Starting Control File and SPFILE Autobackup at 13-MAY-22
piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2022_05_13/o1_mf_s_1104594854_k7w3o655_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-MAY-22
下面在新库恢复控制文件
RMAN> restore controlfile from '/oracle/db/backup/o1_mf_ncnnf_TAG20220513T155411_k7w3o513_.bkp';
Starting restore at 13-MAY-22
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=+ASMVG1/prod/controlfile/control01.ctl
output file name=+ASMVG1/prod/controlfile/control02.ctl
Finished restore at 13-MAY-22
mount数据库
SYS@prod1>alter database mount;
Database altered.
Elapsed: 00:00:04.25
并注册备份，注意需要把归档也拷贝到这个目录，需要看备份的日志，看归档备份到了哪里，好指定目录。
RMAN> catalog start with '/oracle/db/backup';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/db/backup
List of Files Unknown to the Database
=====================================
File Name: /oracle/db/backup/full_PROD_20220513_6_060tde3h_1_1
File Name: /oracle/db/backup/o1_mf_ncnnf_TAG20220513T155411_k7w3o513_.bkp
File Name: /oracle/db/backup/pfile.ora
File Name: /oracle/db/backup/full_PROD_20220513_5_050tde3h_1_1
File Name: /oracle/db/backup/pfile.ora.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/db/backup/full_PROD_20220513_6_060tde3h_1_1
File Name: /oracle/db/backup/o1_mf_ncnnf_TAG20220513T155411_k7w3o513_.bkp
File Name: /oracle/db/backup/full_PROD_20220513_5_050tde3h_1_1
List of Files Which Where Not Cataloged
=======================================
File Name: /oracle/db/backup/pfile.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /oracle/db/backup/pfile.ora.bak
RMAN-07517: Reason: The file header is corrupted
主库查询方便新库恢复数据库
SYS@prod> set line 1000
SYS@prod> set pagesize 1000
SYS@prod> col file_name for a60
SYS@prod> select 'set newname for datafile '||file_id||' to '''||'+asmvg1/prod/datafile'||''';' cmd from dba_data_files order by file_id;
CMD
---------------------------------------------------------------------------------------------
set newname for datafile 1 to '+asmvg1/prod/datafile';
set newname for datafile 2 to '+asmvg1/prod/datafile';
set newname for datafile 3 to '+asmvg1/prod/datafile';
set newname for datafile 4 to '+asmvg1/prod/datafile';
set newname for datafile 5 to '+asmvg1/prod/datafile';
执行数据文件恢复
RMAN> run
2> {
3> set newname for datafile 1 to '+asmvg1';
4> set newname for datafile 2 to '+asmvg1';
5> set newname for datafile 3 to '+asmvg1';
6> set newname for datafile 4 to '+asmvg1';
7> set newname for datafile 5 to '+asmvg1';
8> restore database;
9> switch datafile all;
10> switch tempfile all;
11> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-MAY-22
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 00002 to +asmvg1
channel ORA_DISK_1: restoring datafile 00003 to +asmvg1
channel ORA_DISK_1: restoring datafile 00005 to +asmvg1
channel ORA_DISK_1: reading from backup piece /archivelog/backup/full_PROD_20220513_6_060tde3h_1_1
channel ORA_DISK_1: errors found reading piece handle=/archivelog/backup/full_PROD_20220513_6_060tde3h_1_1
channel ORA_DISK_1: failover to piece handle=/oracle/db/backup/full_PROD_20220513_6_060tde3h_1_1 tag=TAG20220513T144921
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 +asmvg1
channel ORA_DISK_1: restoring datafile 00004 to +asmvg1
channel ORA_DISK_1: reading from backup piece /archivelog/backup/full_PROD_20220513_5_050tde3h_1_1
channel ORA_DISK_1: errors found reading piece handle=/archivelog/backup/full_PROD_20220513_5_050tde3h_1_1
channel ORA_DISK_1: failover to piece handle=/oracle/db/backup/full_PROD_20220513_5_050tde3h_1_1 tag=TAG20220513T144921
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 13-MAY-22
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1104595664 file name=+ASMVG1/prod/datafile/system.257.1104595661
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1104595664 file name=+ASMVG1/prod/datafile/sysaux.266.1104595659
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1104595664 file name=+ASMVG1/prod/datafile/undotbs1.265.1104595659
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1104595664 file name=+ASMVG1/prod/datafile/users.264.1104595661
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=1104595664 file name=+ASMVG1/prod/datafile/lin.258.1104595659
找出目前的新日志重点
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size
------- ----------
8 3.00K
List of Archived Logs in backup set 8
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 10 1031223 13-MAY-22 1031243 13-MAY-22
Backup Set Copy #1 of backup set 8
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 13-MAY-22 NO TAG20220513T144931
List of Backup Pieces for backup set 8 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
8 1 AVAILABLE /u01/app/oracle/fast_recovery_area/PROD/backupset/2022_05_13/o1_mf_annnn_TAG20220513T144931_k7vzvv6d_.bkp
Backup Set Copy #2 of backup set 8
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:00 13-MAY-22 NO TAG20220513T144931
List of Backup Pieces for backup set 8 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
14 1 AVAILABLE /oracle/db/backup/o1_mf_annnn_TAG20220513T144931_k7vzvv6d_.bkp
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 5.03M DISK 00:00:00 13-MAY-22
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20220513T161347
Piece Name: /oracle/db/backup/o1_mf_annnn_TAG20220513T161347_k7w4sw0o_.bkp
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 11 1031243 13-MAY-22 1036613 13-MAY-22
next-scn:1036613 <<<<<恢复到和这个SCN，此时不会报错
RMAN> recover database until scn 1036613;
Starting recover at 13-MAY-22
using channel ORA_DISK_1
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=10
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2022_05_13/o1_mf_annnn_TAG20220513T144931_k7vzvv6d_.bkp
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2022_05_13/o1_mf_annnn_TAG20220513T144931_k7vzvv6d_.bkp
channel ORA_DISK_1: failover to piece handle=/oracle/db/backup/o1_mf_annnn_TAG20220513T144931_k7vzvv6d_.bkp tag=TAG20220513T144931
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCH/prod/archivelog/1_10_948379625.dbf thread=1 sequence=10
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: reading from backup piece /oracle/db/backup/o1_mf_annnn_TAG20220513T161347_k7w4sw0o_.bkp
channel ORA_DISK_1: piece handle=/oracle/db/backup/o1_mf_annnn_TAG20220513T161347_k7w4sw0o_.bkp tag=TAG20220513T161347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCH/prod/archivelog/1_11_948379625.dbf thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-MAY-22
旧库查询
SYS@prod> select thread#,sequence#,first_change#,next_change#,status,archived from v$log;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# STATUS ARC
---------- ---------- ------------- ------------ ---------------- ---
1 10 1031223 1031243 INACTIVE YES
1 11 1031243 1036613 INACTIVE YES
1 12 1036613 2.8147E+14 CURRENT NO
分析，我们知道目前已经将11号（包含）之前的日志都应用到新库，下面我们创新表切换日志
关闭数据库，迁移归档，然后 在新库应用归档，验证数据是否新。
创建新表
SYS@prod> l
1* select segment_name,segment_type,tablespace_name from dba_segments where owner='LIN'
SYS@prod> /
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
LAST_T TABLE LIN
LIN_T TABLE LIN
切换日志将其归档，然后拷贝到新库
SYS@prod> select thread#,sequence#,first_change#,next_change#,status,archived from v$log;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# STATUS ARC
---------- ---------- ------------- ------------ ---------------- ---
1 13 1038169 2.8147E+14 CURRENT NO
1 11 1031243 1036613 INACTIVE YES
1 12 1036613 1038169 INACTIVE YES
12号日志已经归档,看归档位置和状态
SYS@prod> select thread#,sequence#,name,status from v$archived_log;
THREAD# SEQUENCE# NAME S
---------- ---------- ------------------------------ -
1 6 D
1 7 D
1 8 D
1 9 D
1 10 D
1 11 /archivelog/1_11_948379625.dbf A
1 12 /archivelog/1_12_948379625.dbf A
7 rows selected.
将其拷贝到新库
[oracle@dg1 archivelog]$ scp *.dbf 192.168.56.101:/oracle/db/backup
oracle@192.168.56.101's password:
1_11_948379625.dbf 5146KB 5.0MB/s 00:01
1_12_948379625.dbf
注册到新库
RMAN> catalog start with '/oracle/db/backup';
searching for all files that match the pattern /oracle/db/backup
List of Files Unknown to the Database
=====================================
File Name: /oracle/db/backup/1_11_948379625.dbf
File Name: /oracle/db/backup/1_12_948379625.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/db/backup/1_11_948379625.dbf
File Name: /oracle/db/backup/1_12_948379625.dbf
关闭数据库后把当前日志也拷贝到新库相应目录，因为控制文件记的还是这个在线日志位置
[oracle@dg1 archivelog]$ scp /u01/app/oracle/oradata/prod/*.log 192.168.56.101:/u01/app/oracle/oradata/prod
oracle@192.168.56.101's password:
Permission denied, please try again.
oracle@192.168.56.101's password:
redo01.log 50MB 50.0MB/s 00:01
redo02.log 50MB 25.0MB/s 00:02
redo03.log 50MB 50.0MB/s 00:01
下面恢复数据库
RMAN> recover database;
Starting recover at 13-MAY-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/prod/redo01.log
archived log file name=/u01/app/oracle/oradata/prod/redo01.log thread=1 sequence=13
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '+ASMVG1/prod/datafile/system.257.1104595661'
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-MAY-22
下面主备打开数据库，但是需要修改日志文件的位置
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 13 16:53:52 2022
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 Real Application Clusters and Automatic Storage Management options
SYS@prod1>alter database rename file '/u01/app/oracle/oradata/prod/redo01.log' to '+ASMVG1';
Database altered.
Elapsed: 00:00:00.03
SYS@prod1>alter database rename file '/u01/app/oracle/oradata/prod/redo02.log' to '+ASMVG1';
Database altered.
Elapsed: 00:00:00.02
SYS@prod1>alter database rename file '/u01/app/oracle/oradata/prod/redo03.log' to '+ASMVG1';
Database altered.
Elapsed: 00:00:00.01
验证如下
SYS@prod1> select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE +ASMVG1 NO
2 ONLINE +ASMVG1 NO
1 ONLINE +ASMVG1 NO
Elapsed: 00:00:00.00
尝试打开数据库
SYS@prod1>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+ASMVG1'
报错处理，对日志做clear操作
SYS@prod1>alter database clear logfile group 2;
Database altered.
Elapsed: 00:00:00.43
SYS@prod1>alter database clear logfile group 1;
Database altered.
Elapsed: 00:00:00.31
SYS@prod1>alter database clear logfile group 3;
Database altered.
Elapsed: 00:00:00.27
SYS@prod1>alter database open resetlog;
alter database open resetlog
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
Elapsed: 00:00:00.00
SYS@prod1>alter database open resetlogs;
Database altered.
Elapsed: 00:00:03.39
##########################################################################
此时数据库已经打开，下面我们配置RAC组件，使得当前数据库成为集群管理的数据库
##########################################################################
1 修改数据库配置参数
SYS@prod1>alter system set cluster_database=true scope=spfile sid='*';
System altered.
Elapsed: 00:00:00.07
SYS@prod1>alter system set cluster_database_instances=2 scope=spfile sid='*';
System altered.
Elapsed: 00:00:00.01
SYS@prod1>alter system set instance_number=1 scope=spfile sid='prod1';
System altered.
Elapsed: 00:00:00.00
SYS@prod1>alter system set instance_number=2 scope=spfile sid='prod2';
System altered.
Elapsed: 00:00:00.02
SYS@prod1>alter system set thread=1 scope=pfile sid='prod1';
alter system set thread=1 scope=pfile sid='prod1'
*
ERROR at line 1:
ORA-00922: missing or invalid option
Elapsed: 00:00:00.00
SYS@prod1>alter system set thread=1 scope=spfile sid='prod1';
System altered.
Elapsed: 00:00:00.01
SYS@prod1>alter system set thread=2 scope=spfile sid='prod2';
System altered.
Elapsed: 00:00:00.01
SYS@prod1>show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@prod1>select file_name,tablespace_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
+ASMVG1/prod/datafile/users.264.1104595661
USERS
+ASMVG1/prod/datafile/undotbs1.265.1104595659
UNDOTBS1
+ASMVG1/prod/datafile/sysaux.266.1104595659
SYSAUX
+ASMVG1/prod/datafile/system.257.1104595661
SYSTEM
+ASMVG1/prod/datafile/lin.258.1104595659
LIN
Elapsed: 00:00:00.03
SYS@prod1>create undo tablespace undotbs2 datafile '+ASMVG1/prod/datafile/undotbs02.dbf' size 100m;
Tablespace created.
Elapsed: 00:00:00.88
SYS@prod1>alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='prod2';
System altered.
Elapsed: 00:00:00.02
增加第2个实例的REDO日志
SYS@prod1>alter database add logfile thread 2 group 4 ('+ASMVG1') size 50m;
Database altered.
Elapsed: 00:00:00.30
SYS@prod1>alter database add logfile thread 2 group 5 ('+ASMVG1') size 50m;
Database altered.
Elapsed: 00:00:00.34
SYS@prod1>alter database add logfile thread 2 group 6 ('+ASMVG1') size 50m;
Database altered.
Elapsed: 00:00:00.25
启动日志线程
SYS@prod1>alter database enable thread 2;
Database altered.
Elapsed: 00:00:00.06
SYS@prod1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@prod1>
注册组件到集群
[oracle@rac1 ~]$ srvctl add database -d prod -o $ORACLE_HOME -p +ASMVG1/prod/parameterfile/spfileprod.ora
[oracle@rac1 ~]$ srvctl add instance -d prod -i prod1 rac1
PRKO-2002 : Invalid command line option: rac1
[oracle@rac1 ~]$ srvctl add instance -d prod -i prod1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d prod -i prod2 -n rac2
启动集群数据库
相关文章