rman+duplicate迁移_RAC-单机

wanedepidan

已于 2023-03-21 11:20:16 修改

阅读量417

点赞数

分类专栏： oracle迁移文章标签： oracle 数据库 Powered by 金山文档

于 2023-03-15 10:48:16 首次发布

本文链接：https://blog.csdn.net/wanedepidan/article/details/129547989

版权

oracle迁移专栏收录该内容

2 篇文章 0 订阅

订阅专栏

RAC环境： db_name= orcl sid= orcl1/orcl2

单实例： db_name= orcl sid= orcl

Rac 网卡设置：

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.10.223 rac1

192.168.10.224 rac2

192.168.10.225 rac1-vip

192.168.10.226 rac2-vip

10.10.10.3 rac1-priv

10.10.10.4 rac2-priv

192.168.10.227 rac-scan

192.168.10.222 rman_backup

注：红色为rac 相关IP , 蓝色为单实例IP

创建监听，通过netmgr 或者在 $ORALCE_HOME/NETWORK/ADMIN/下配置 listener.ora和tnsname.ora文件：同时rac 和单实例下都需要配置，tnsping通就行。

[oracle@rac1 admin]$ cat tnsnames.ora

ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
    )
  )
 
ORCL_SING =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rman_backup)(PORT= 1521))
    )
    (CONNECT_DATA =
     (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_SING为需要添加的部分

[oracle@rman_backup admin]$ cat listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = orcl)
     (ORACLE_HOME = /u01/app/oracle/product/11.2/dbhome_1)
     (SID_NAME = orcl)
    )
  )
 
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = rman_backup)(PORT = 1521))
    )
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

通过RAC 的spfile 生成pfile文件修改后 scp 至单机

RAC 生成pfile文件

SQL> create pfile='/u01/app/oracle/initorcl.ora' from spfile;

[oracle@rac1 ~]$ cat /u01/app/oracle/initorcl.ora
orcl1.__db_cache_size=234881024
orcl2.__db_cache_size=268435456
orcl1.__java_pool_size=16777216
orcl2.__java_pool_size=16777216
orcl1.__large_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__pga_aggregate_target=402653184
orcl2.__pga_aggregate_target=402653184
orcl1.__sga_target=754974720
orcl2.__sga_target=754974720
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=436207616
orcl2.__shared_pool_size=436207616
orcl1.__streams_pool_size=33554432
orcl2.__streams_pool_size=0                           --这行及以上要删除
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true                               --删除       
*.compatible='11.2.0.0.0'
*.control_files='+DATAFILE/orcl/controlfile/current.260.825093345','+ARCHIVELOG/orcl/controlfile/current.256.825093345'             --修改
*.db_block_size=8192
*.db_create_file_dest='+DATAFILE'                     --修改
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+ARCHIVELOG'                 --修改
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
orcl2.instance_number=2                               --删除
orcl1.instance_number=1                               --删除                      
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_listener='scan.com:1521'                     --删除          
*.remote_login_passwordfile='exclusive'
*.sessions=170
orcl2.thread=2
orcl1.thread=1
orcl2.undo_tablespace='UNDOTBS2'                     --删除
orcl1.undo_tablespace='UNDOTBS1'                     --修改为orcl.undo_tablespace='UNDOTBS1'

单机pfile 文件来源于修改后的RAC 文件:

[oracle@rman_backup ~]$ cat /u01/app/oracle/initorcl.ora

orcl.__db_cache_size=452984832
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=687865856
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=184549376
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/orcl/controlfile/current.260.825093345','/u01/app/oracle/oradata/controlfile/current.256.825093345'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/orcl'
*.db_domain=''
*.db_file_name_convert=('+DATAFILE/orcl/datafile','/u01/app/oracle/oradata/orcl')
*.db_file_name_convert=('+DATAFILE/orcl/tempfile','/u01/app/oracle/oradata/orcl/tempfile')
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/archive_log'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert=（'+DATAFILE/orcl/onlinelog','/u01/app/oracle/oradata/logfile'）
*.log_file_name_convert=（'+ARCHIVELOG/orcl/onlinelog','/u01/app/oracle/onlinelog/logfile'）
*.memory_target=1153433600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sessions=170
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

创建orapwdSID 密码文件

orapwd file=orapworcl password=oracle entires=10
scp orapworcl oracle@192.168.10.222:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

备份rac 所有数据，一个节点上备份，另一个节点的archivelog 日志备份后，也得拷贝过去

rac1:

run {
allocate channel c1 device type diskconnect 'sys/oracle@orcl1';
allocate channel c2 device type diskconnect 'sys/oracle@orcl2';
backup database format'/u01/app/oracle/db_back/%U_%T.DB';
sql 'alter system archive log current';
backup format '/u01/app/oracle/db_back/%U_%T.ARCHIVE' archiveloog all delete input;
backup current controlfile FORMAT'/u01/app/oracle/db_back/%T.CTL';
backup spfile tag='spfile'format='/u01/app/oracle/db_back/%T.PFILE';
release channel c2;
release channel c1;
}

scp /u01/app/oracle/db_back/* oracle@192.168.10.222:/u01/app/oracle/db_back/

rac 2:

 backup archivelog all format'/u01/app/oracle/db_back/%U_%T.archivelog'

scp /u01/app/oracle/db_back/* oracle@192.168.10.222:/u01/app/oracle/db_back/

node 单实例通过pfile 加载到 nomount 状态后，通过pfile 创建spfile 文件。

startup nomount pfile='/u01/app/oracle/initorcl.ora';
create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from pfile='/u01/app/oracle/initorcl.ora';

通过 rman target sys/oracle@ORCL auxiliary / 在单机登陆。

通过 duplicate target database to orcl 恢复数据。 ----这两步有可能会报错，根据报错修改信息。

[oracle@rman_backup db_back]$ rman target sys/oracle@ORCL auxiliary /

RMAN> duplicate target database to orcl;

Starting Duplicate Db at 09-SEP-13

using target database control file insteadof recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 devicetype=DISK

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Modifiedby RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''ORCL'' comment=

''Modified by RMAN duplicate''scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ORCL'' comment= ''Modified by RMANduplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1152450560 bytes

Fixed Size 2227704 bytes

Variable Size 687866376 bytes

Database Buffers 452984832 bytes

Redo Buffers 9371648 bytes

Starting restore at 09-SEP-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=133 devicetype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring controlfile

channel ORA_AUX_DISK_1: reading from backuppiece /u01/app/oracle/db_back/20130909.CTL

channel ORA_AUX_DISK_1: piecehandle=/u01/app/oracle/db_back/20130909.CTL tag=TAG20130909T152516

channel ORA_AUX_DISK_1: restored backuppiece 1

channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:03

output filename=/u01/app/oracle/orcl/controlfile/current.260.825093345

output filename=/u01/app/oracle/oradata/controlfile/current.256.825093345

Finished restore at 09-SEP-13

database mounted

Using previous duplicated file/u01/app/oracle/oradata/orcl/system.256.825091903 for datafile 1 withcheckpoint SCN of 2318677

Using previous duplicated file /u01/app/oracle/oradata/orcl/sysaux.257.825091907for datafile 2 with checkpoint SCN of 2318677

Using previous duplicated file/u01/app/oracle/oradata/orcl/undotbs1.258.825091909 for datafile 3 withcheckpoint SCN of 2318657

Using previous duplicated file/u01/app/oracle/oradata/orcl/users.259.825091909 for datafile 4 with checkpointSCN of 2318657

Using previous duplicated file/u01/app/oracle/oradata/orcl/example.264.825093429 for datafile 5 withcheckpoint SCN of 2318677

Using previous duplicated file/u01/app/oracle/oradata/orcl/undotbs2.265.825093865 for datafile 6 withcheckpoint SCN of 2318657

Using previous duplicated file/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105 for datafile 7 withcheckpoint SCN of 2318657

contents of Memory Script:

{

set until scn 2996999;

set newname for datafile 1 to

"/u01/app/oracle/oradata/orcl/system.256.825091903";

set newname for datafile 2 to

"/u01/app/oracle/oradata/orcl/sysaux.257.825091907";

set newname for datafile 3 to

"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909";

set newname for datafile 4 to

"/u01/app/oracle/oradata/orcl/users.259.825091909";

set newname for datafile 5 to

"/u01/app/oracle/oradata/orcl/example.264.825093429";

set newname for datafile 6 to

"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865";

set newname for datafile 7 to

"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

contents of Memory Script:

{

catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/system.256.825091903",

"/u01/app/oracle/oradata/orcl/sysaux.257.825091907",

"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909",

"/u01/app/oracle/oradata/orcl/users.259.825091909",

"/u01/app/oracle/oradata/orcl/example.264.825093429",

"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865",

"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";

switch clone datafile 1 todatafilecopy

"/u01/app/oracle/oradata/orcl/system.256.825091903";

switch clone datafile 2 todatafilecopy

"/u01/app/oracle/oradata/orcl/sysaux.257.825091907";

switch clone datafile 3 todatafilecopy

"/u01/app/oracle/oradata/orcl/undotbs1.258.825091909";

switch clone datafile 4 todatafilecopy

"/u01/app/oracle/oradata/orcl/users.259.825091909";

switch clone datafile 5 todatafilecopy

"/u01/app/oracle/oradata/orcl/example.264.825093429";

switch clone datafile 6 todatafilecopy

"/u01/app/oracle/oradata/orcl/undotbs2.265.825093865";

switch clone datafile 7 todatafilecopy

"/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105";

}

executing Memory Script

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/system.256.825091903 RECID=10 STAMP=825722444

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/sysaux.257.825091907RECID=11 STAMP=825722444

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs1.258.825091909 RECID=12STAMP=825722444

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/users.259.825091909RECID=13 STAMP=825722444

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/example.264.825093429 RECID=14STAMP=825722444

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/orcl/undotbs2.265.825093865RECID=15 STAMP=825722444

cataloged datafile copy

datafile copy filename=/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105 RECID=16STAMP=825722444

datafile 1 switched to datafile copy

input datafile copy RECID=10STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/system.256.825091903

datafile 2 switched to datafile copy

input datafile copy RECID=11STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/sysaux.257.825091907

datafile 3 switched to datafile copy

input datafile copy RECID=12STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/undotbs1.258.825091909

datafile 4 switched to datafile copy

input datafile copy RECID=13STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/users.259.825091909

datafile 5 switched to datafile copy

input datafile copy RECID=14STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/example.264.825093429

datafile 6 switched to datafile copy

input datafile copy RECID=15STAMP=825722444 file name=/u01/app/oracle/oradata/orcl/undotbs2.265.825093865

datafile 7 switched to datafile copy

input datafile copy RECID=16STAMP=825722444 filename=/u01/app/oracle/oradata/orcl/gta_dcsys_data.269.825163105

contents of Memory Script:

{

set until scn 2996999;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 09-SEP-13

using channel ORA_AUX_DISK_1

starting media recovery

Oracle instance started

Total System Global Area 1152450560 bytes

Fixed Size 2227704 bytes

Variable Size 687866376 bytes

Database Buffers 452984832 bytes

Redo Buffers 9371648 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Reset to original value by RMAN''scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ==============

RMAN-00571:=====================================================

RMAN-03002: failure of Duplicate Db commandat 09/09/2013 23:20:57

RMAN-05501: aborting duplication of targetdatabase

RMAN-03015: error occurred in stored scriptMemory Script

RMAN-06053: unable to perform mediarecovery because of missing log

RMAN-06025: no backup of archived log forthread 2 with sequence 46 and starting SCN of 2990968 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 45 and starting SCN of 2990878 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 44 and starting SCN of 2904738 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 43 and starting SCN of 2811911 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 42 and starting SCN of 2768543 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 41 and starting SCN of 2698110 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 40 and starting SCN of 2665715 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 39 and starting SCN of 2561227 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 38 and starting SCN of 2468986 found to restore

RMAN-06025: no backup of archived log for thread2 with sequence 37 and starting SCN of 2380959 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 36 and starting SCN of 2318917 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 35 and starting SCN of 2318877 found to restore

RMAN-06025: no backup of archived log forthread 2 with sequence 34 and starting SCN of 2318176 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 72 and starting SCN of 2975716 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 71 and starting SCN of 2897150 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 70 and starting SCN of 2831168 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 69 and starting SCN of 2723427 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 68 and starting SCN of 2612996 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 67 and starting SCN of 2525003 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 66 and starting SCN of 2418165 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 65 and starting SCN of 2318921 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 64 and starting SCN of 2318880 found to restore

RMAN-06025: no backup of archived log forthread 1 with sequence 63 and starting SCN of 2318172 found to restore

提示缺少归档日志

开始不完全恢复

startup mount;
restore database until sequence 62 thread 1；
recover database until sequence 62 thread 1；
alter database open resetlogs;

至此迁移基本完成

单实例后续扫尾工作。

8.1 ：清除多余的undo文件

查看UNDO 信息：

SQL> select name from v$tablespace wherename like 'UNDO%';

NAME
------------------------------
UNDOTBS1
UNDOTBS2

因为我们使用的是UNDOTBS1，在pfile里设置的，所以把UNDOTBS2删除掉。

SQL> show parameter undo_tablespace;
 
NAME                                 TYPE        VALUE
----------------------------------------------- ------------------------------
undo_tablespace                      string     UNDOTBS1

SQL> drop tablespace undotbs2 includingcontents and datafiles;

Tablespace dropped.

SQL> select name from v$tablespace wherename like 'UNDO%';

NAME
------------------------------
UNDOTBS1

8.2 ：清除未使用线程的redo日志组

默认情况下，RAC 环境下，每个实例都有2个redo。在单实例下，就没有必要了,删除点线程2的redo 信息。

SQL> select thread#,status,enabled fromv$thread;

  THREAD# STATUS ENABLED
---------- ------ --------
        1 OPEN   PUBLIC
        2 CLOSED PRIVATE

SQL> select group#,thread#,archived,status from v$log; 

   GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
        1          1 YES UNUSED
        2          1 NO  CURRENT
        3          2 NO  CURRENT
        4          2 YES UNUSED

SQL> alter database disable thread 2;

Database altered.
 
SQL> alter database drop logfile group3;

Database altered.
 
SQL> alter database drop logfile group4;

Database altered.

SQL> selectgroup#,thread#,archived,status from v$log;
 
   GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
        1          1 YES INACTIVE
        2          1 NO  CURRENT

现在就剩2个了。一般的单实例是3个online redo，再添加一组。

SQL> alter database add logfile group 3('/u01/app/oracle/oradata/orcl/redo03.log') size 10m;
 
Database altered.
 
SQL> selectgroup#,thread#,archived,status from v$log;

   GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------------
        1          1 YES INACTIVE
        2          1 NO  CURRENT
        3          1 YES UNUSED

8.3：重建临时表空间，并删除原来的数据文件

SQL> create temporary tablespace temp1  tempfile '/U01/app/oracle/oradata/orcl/temp01.dbf' size 100M autoextend on next 100M maxsize unlimited;

SQL> alter database default temporary tablespace temp1;

SQL> drop tablespace temp including contents and datafiles;

SQL> select file#,name from v$tempfile;

wanedepidan

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
rman+duplicate迁移_RAC-单机

2、创建监听，通过netmgr 或者在 $ORALCE_HOME/NETWORK/ADMIN/下配置 listener.ora和tnsname.ora文件：同时rac 和单实例下都需要配置，tnsping通就行。---这个报错，是应为在orcl2 目录下，而orcl1不能访问orcl2 目录下的文件（不是aSM)。----这两部，有可能会报错，根据报错，修改信息。orcl2.undo_tablespace='UNDOTBS2' --这要删除，保留一个。
复制链接

扫一扫