Oralce database 11g 单机迁移至RAC

左手的ㄟ右手 2022-05-30 09:43 209阅读 0赞

单机：  
备份目录：/backup

rac:  
备份目录：/recover 单机归档存放位置：/archive

--------------------

在单机数据库中查看dbid号：  
select dbid from v$database;

在单机数据库中查看scn号：  
SQL> select FIRST\_CHANGE\#,NEXT\_CHANGE\#,STATUS from v$log;

--------------------

单机全备  
run \{  
allocate channel c1 device type disk ;  
allocate channel c2 device type disk ;  
allocate channel c3 device type disk ;  
backup database format =’/backup/data\_%s.bak’ filesperset 3;  
backup current controlfile format =’/backup/conbak\_%s.bak’;  
backup spfile format =’/backup/spfile\_%s.bak’;  
sql ‘alter system archive log current’;  
crosscheck archivelog all;  
delete noprompt expired archivelog all ;  
backup archivelog all delete input format =’/backup/arch\_%s.bak’ ;  
release channel c1 ;  
release channel c2 ;  
release channel c3 ;  
report obsolete;  
delete noprompt obsolete;  
crosscheck backup;  
delete noprompt expired backup;  
\}  
复制到rac  
scp \*.bak 192.168.66.61:/recover  
原归档复制到rac  
scp \*.arc 192.168.66.61:/recover

————-RAC———————  
在rac创建单机使用的目录  
\[grid@rac1 ~\]$ asmcmd  
ASMCMD> cd datadg  
ASMCMD> mkdir yh  
ASMCMD> cd yh  
ASMCMD> mkdir CONTROLFILE  
ASMCMD> mkdir DATAFILE  
ASMCMD> mkdir ONLINELOG  
ASMCMD> mkdir PARAMETERFILE  
ASMCMD> mkdir TEMPFILE  
ASMCMD> mkdir archivelog

--------------------

创建pfile 修改

*.cluster_database = true
    *.cluster_database _instances = 2
    *.undo_management=auto
    <sid1>.undo_tablespace=undotbs (undo tablespace which already exists)
    <sid1>.instance_name=<sid1>
    <sid1>.instance_number=1
    <sid1>.thread=1
    yh1.local_listener=<listenername>_<hostname1>
    
    <sid2>.instance_name=<sid2>
    <sid2>.instance_number=2
    <sid2>.local_listener=<listenername>_<hostname2>
    <sid2>.thread=2
    <sid2>.undo_tablespace=undotbs2
    <sid2>.cluster_database = true
    <sid2>.cluster_database _instances = 2

--------------------

yh2.__db_cache_size=360710144
    yh1.__db_cache_size=352321536
    yh2.__java_pool_size=4194304
    yh1.__java_pool_size=4194304
    yh2.__large_pool_size=4194304
    yh1.__large_pool_size=4194304
    yh2.__pga_aggregate_target=411041792
    yh1.__pga_aggregate_target=411041792
    yh2.__sga_target=616562688
    yh1.__sga_target=616562688
    yh2.__shared_io_pool_size=0
    yh1.__shared_io_pool_size=0
    yh2.__shared_pool_size=234881024
    yh1.__shared_pool_size=243269632
    yh2.__streams_pool_size=0
    yh1.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/yh/adump'
    *.audit_trail='db'
    *.cluster_database=true
    *.compatible='11.2.0.0.0'
    *.control_files='+DATAVG/yh/controlfile/control01.ctl','+DATAVG/yh/controlfile/control02.ctl'   (控制文件不能与另一个数据库同名)
    *.db_block_size=8192
    *.db_create_file_dest='+DATAVG'
    *.db_domain=''
    *.db_name='yh'
    *.db_recovery_file_dest='+DATAVG'
    *.db_recovery_file_dest_size=4558159872
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=yhXDB)'
    yh1.instance_number=1
    yh2.instance_number=2
    *.log_archive_dest_1='LOCATION=+DATAVG'
    *.log_archive_format='%t_%s_%r.arc'
    *.memory_target=1027604480
    *.nls_language='SIMPLIFIED CHINESE'
    *.nls_territory='CHINA'
    *.open_cursors=300
    *.processes=150
    *.remote_listener='rac-scan:1521'
    *.remote_login_passwordfile='exclusive'
    yh2.thread=2
    yh1.thread=1
    yh1.undo_tablespace='UNDOTBS1'
    yh2.undo_tablespace='UNDOTBS2'
    *.instance_number=2
    yh1.instance_name=yh1
    yh2.instance_name=yh2
    *.cluster_database_instances = 2
    *.undo_management=auto

--------------------

用pfile起到nomount状态下

--------------------

rman target /  
恢复控制文件  
RMAN>run\{  
set dbid=1478303236;  
restore controlfile from ‘/recover/conbak\_6.bak’;  
\}  
RMAN> alter database mount;

--------------------

恢复数据文件  
RMAN> CATALOG START WITH ‘/recover’; \#指定恢复时备份片的位置  
run\{  
set newname for datafile 1 to ‘+DATAVG/yh/DATAFILE/system01.dbf’;  
set newname for datafile 2 to ‘+DATAVG/yh/DATAFILE/sysaux01.dbf’;  
set newname for datafile 3 to ‘+DATAVG/yh/DATAFILE/UNDOTBS1.dbf’;  
set newname for datafile 4 to ‘+DATAVG/yh/DATAFILE/users01.dbf’;  
restore database;  
switch datafile 1;  
switch datafile 2;  
switch datafile 3;  
switch datafile 4;  
\}

--------------------

恢复数据库  
RMAN> CATALOG START WITH ‘/archive’;  
RMAN> run\{  
recover database until scn 1155704;  
\}

--------------------

修改redo文件的名字：  
SQL> alter database add logfile thread 1 group 4 ‘+datavg/yh/ONLINELOG/redo04\_1.log’ size 52M;  
SQL> alter database add logfile thread 1 group 5 ‘+datavg/yh/ONLINELOG/redo05\_1.log’ size 52M;  
SQL> alter database add logfile thread 2 group 6 ‘+datavg/yh/ONLINELOG/redo06\_1.log’ size 52M;  
SQL> alter database add logfile thread 2 group 7 ‘+datavg/yh/ONLINELOG/redo07\_1.log’ size 52M;  
SQL> alter database drop logfile group 1;  
SQL> alter database drop logfile group 3;

--------------------

RMANT> alter database open resetlogs;  
找不到当前正在使用的redo 创建文件夹 resetlogs打开数据库  
把current状态的redo整理好：  
SQL> alter system checkpoint;  
SQL> alter database drop logfile group 2;

--------------------

整理临时文件：  
SQL> alter tablespace temp add tempfile ‘+datavg/yh/TEMPFILE/temp02.dbf’ size 50M;  
SQL> select \* from dba\_temp\_files;  
SQL> alter database tempfile ‘/u01/oradata/yh/temp01.dbf’ offline ;  
SQL> alter database tempfile ‘/u01/oradata/yh/temp01.dbf’ drop including datafiles;

--------------------

创建spfile  
create spfile=’+datavg/yh/spfileyh.ora’ from pfile=’/pfile/pfile.ora’;  
$ vi inityh.ora  
SPFILE=’+DATADG/yh/spfileordb.ora’

--------------------

创建undo表空间  
create undo tablespace undotbs2 datafile ‘+datavg/yh/datafile/undotbs2.dbf’ size 50m;

--------------------

注册服务

$ srvctl add database -d yh -o /u01/app/oracle/product/11.2.0/db_1/
    $ srvctl add instance -d yh -i zy1 -n z1
    $ srvctl add instance -d yh -i zy2 -n z2
    $ srvctl start database -d yh