Oralce database 11g 单机迁移至RAC
单机：
备份目录：/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创建单机使用的目录
[[email protected] ~]$ 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 = 2yh2.__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