Oracle(11g)单实例----RAC(11g)迁移模拟测试

2022-05-13 21:54:39

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

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

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

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