1、在老库上创建PFILE文件
SQL> create pfile='/home/oracle/initdzgddb.ora' from spfile;
2、在老库上备份控制文件
RMAN>backup current controlfile format='/home/oracle/cntrl_%s_%p_%t';
3、上传pfile和控制文件至新库的/jharch目录
dzgddb02:/#cd /home/oracle
dzgddb02:/home/oracle#rcp initdzgddb.ora root@134.225.80.26:/jharch
dzgddb02:/home/oracle#rcp cntrl_34_1_804423650 root@134.225.80.26:/jharch
3、新库2个节点分别创建trace目录
mkdir -p /oracle/app/oracle/admin/dzgddb/adump
mkdir -p /oracle/app/oracle/admin/dzgddb/bdump
mkdir -p /oracle/app/oracle/admin/dzgddb/cdump
mkdir -p /oracle/app/oracle/admin/dzgddb/dpdump
mkdir -p /oracle/app/oracle/admin/dzgddb/hdump
mkdir -p /oracle/app/oracle/admin/dzgddb/pfile
mkdir -p /oracle/app/oracle/admin/dzgddb/udump
4、修改initdzgddb.ora参数
*.control_files='/jhora01/rcontrolfile1.ctl','/jhora02/rcontrolfile2.ctl','/jhora03/rcontrolfile3.ctl'
*.log_archive_dest_1='LOCATION=/jharch/backup'
*.audit_file_dest='/oracle/app/oracle/admin/dzgddb/adump'
*.background_dump_dest='/oracle/app/oracle/admin/dzgddb/bdump'
*.core_dump_dest='/oracle/app/oracle/admin/dzgddb/cdump'
*.user_dump_dest='/oracle/app/oracle/admin/dzgddb/udump'
5、将数据库启动到nomount
startup nomount pfile='/jharch/initdzgddb.ora';
6、将老库备份生成的'cntrl_34_1_804423650' 控制文件上传到对应位置:
/jharch
还原控制文件
RMAN>restore controlfile from '/jharch/cntrl_34_1_804423650';
7、将数据库启动到mount
alter database mount;
8、由RMAN进行全库恢复
cd /jhora01
ls
由于恢复时间长后台执行脚本如下:
nohup rman nocatalog target / cmdfile restore.sh log restoredatabase.log &
恢复脚本restore.sh如下
run {
allocate channel t1 type 'sbt_tape' parms="ENV=(NB_ORA_SERV=nbubackup,NB_ORA_CLIENT=dzgddb02)";
set newname for datafile 1 to '/jhora01/rlvtssa_system.dbf';
set newname for datafile 2 to '/jhora01/rlvtssa_4_data54.dbf';
set newname for datafile 3 to '/jhora01/rlvtssa_sysaux.dbf';
set newname for datafile 4 to '/jhora01/rlvtssa_users.dbf';
set newname for datafile 5 to '/jhora01/rlvtssa_4_data58.dbf';
set newname for datafile 6 to '/jhora01/rlvtssa_8_data01.dbf';
set newname for datafile 7 to '/jhora01/rlvtssa_8_data02.dbf';
set newname for datafile 8 to '/jhora01/rlvtssa_8_data03.dbf';
set newname for datafile 9 to '/jhora01/rlvtssa_8_data04.dbf';
set newname for datafile 10 to '/jhora01/rlvtssa_8_data05.dbf';
set newname for datafile 11 to '/jhora01/rlvtssa_8_data06.dbf';
set newname for datafile 12 to '/jhora01/rlvtssa_8_data07.dbf';
set newname for datafile 13 to '/jhora01/rlvtssa_8_data08.dbf';
set newname for datafile 14 to '/jhora01/rlvtssa_8_data09.dbf';
set newname for datafile 15 to '/jhora01/rlvtssa_8_data10.dbf';
set newname for datafile 16 to '/jhora01/rlvtssa_8_data11.dbf';
set newname for datafile 17 to '/jhora01/rlvtssa_8_data12.dbf';
set newname for datafile 18 to '/jhora01/rlvtssa_8_data13.dbf';
set newname for datafile 19 to '/jhora01/rlvtssa_8_data14.dbf';
set newname for datafile 20 to '/jhora01/rlvtssa_8_data15.dbf';
set newname for datafile 21 to '/jhora01/rlvtssa_8_data16.dbf';
set newname for datafile 22 to '/jhora01/rlvtssa_8_data17.dbf';
set newname for datafile 23 to '/jhora01/rlvtssa_8_data18.dbf';
set newname for datafile 24 to '/jhora01/rlvtssa_8_data19.dbf';
set newname for datafile 25 to '/jhora01/rlvtssa_8_data20.dbf';
set newname for datafile 26 to '/jhora01/rlvtssa_8_data21.dbf';
set newname for datafile 27 to '/jhora01/rlvtssa_8_data22.dbf';
set newname for datafile 28 to '/jhora01/rlvtssa_8_data23.dbf';
set newname for datafile 29 to '/jhora01/rlvtssa_8_data24.dbf';
set newname for datafile 30 to '/jhora01/rlvtssa_8_data25.dbf';
set newname for datafile 31 to '/jhora01/rlvtssa_8_data26.dbf';
set newname for datafile 32 to '/jhora01/rlvtssa_8_data27.dbf';
set newname for datafile 33 to '/jhora01/rlvtssa_8_data28.dbf';
set newname for datafile 34 to '/jhora01/rlvtssa_8_data29.dbf';
set newname for datafile 35 to '/jhora01/rlvtssa_8_data30.dbf';
set newname for datafile 36 to '/jhora01/rlvtssa_4_data01.dbf';
set newname for datafile 37 to '/jhora01/rlvtssa_4_data02.dbf';
set newname for datafile 38 to '/jhora01/rlvtssa_4_data03.dbf';
set newname for datafile 39 to '/jhora01/rlvtssa_4_data04.dbf';
set newname for datafile 40 to '/jhora01/rlvtssa_4_data05.dbf';
set newname for datafile 41 to '/jhora01/rlvtssa_4_data06.dbf';
set newname for datafile 42 to '/jhora01/rlvtssa_4_data07.dbf';
set newname for datafile 43 to '/jhora01/rlvtssa_4_data08.dbf';
set newname for datafile 44 to '/jhora01/rlvtssa_4_data09.dbf';
set newname for datafile 45 to '/jhora01/rlvtssa_4_data10.dbf';
set newname for datafile 46 to '/jhora01/rlvtssa_4_data11.dbf';
set newname for datafile 47 to '/jhora01/rlvtssa_4_data12.dbf';
set newname for datafile 48 to '/jhora01/rlvtssa_4_data13.dbf';
set newname for datafile 49 to '/jhora01/rlvtssa_4_data14.dbf';
set newname for datafile 50 to '/jhora01/rlvtssa_4_data15.dbf';
set newname for datafile 51 to '/jhora01/rlvtssa_4_data16.dbf';
set newname for datafile 52 to '/jhora01/rlvtssa_4_data17.dbf';
set newname for datafile 53 to '/jhora01/rlvtssa_4_data18.dbf';
set newname for datafile 54 to '/jhora01/rlvtssa_4_data19.dbf';
set newname for datafile 55 to '/jhora01/rlvtssa_4_data20.dbf';
set newname for datafile 56 to '/jhora01/rlvtssa_4_data50.dbf';
set newname for datafile 57 to '/jhora01/rlvtssa_4_data44.dbf';
set newname for datafile 58 to '/jhora01/rlvtssa_4_data57.dbf';
set newname for datafile 59 to '/jhora01/rlvtssa_4_data51.dbf';
set newname for datafile 60 to '/jhora01/rlvtssa_4_data55.dbf';
set newname for datafile 61 to '/jhora01/rlvtssa_4_data56.dbf';
set newname for datafile 62 to '/jhora01/rlvtssa_4_data21.dbf';
set newname for datafile 63 to '/jhora01/rlvtssa_4_data22.dbf';
set newname for datafile 64 to '/jhora01/rlvtssa_4_data23.dbf';
set newname for datafile 65 to '/jhora01/rlvtssa_4_data24.dbf';
set newname for datafile 66 to '/jhora01/rlvtssa_8_data37.dbf';
set newname for datafile 67 to '/jhora01/rlvtssa_8_data38.dbf';
set newname for datafile 68 to '/jhora01/rlvtssa_4_data31.dbf';
set newname for datafile 69 to '/jhora01/rlvtssa_4_data32.dbf';
set newname for datafile 70 to '/jhora01/rlvtssa_4_data33.dbf';
set newname for datafile 71 to '/jhora01/rlvtssa_4_data34.dbf';
set newname for datafile 72 to '/jhora01/rlvtssa_4_data35.dbf';
set newname for datafile 73 to '/jhora01/rlvtssa_4_data36.dbf';
set newname for datafile 74 to '/jhora01/rlvtssa_8_data39.dbf';
set newname for datafile 75 to '/jhora01/rlvtssa_8_data40.dbf';
set newname for datafile 76 to '/jhora01/rlvtssa_4_data37.dbf';
set newname for datafile 77 to '/jhora01/rlvtssa_4_data25.dbf';
set newname for datafile 78 to '/jhora01/rlvtssa_4_data26.dbf';
set newname for datafile 79 to '/jhora01/rlvtssa_4_data27.dbf';
set newname for datafile 80 to '/jhora01/rlvtssa_4_data43.dbf';
set newname for datafile 81 to '/jhora02/rlvtssa_2_data07.dbf';
set newname for datafile 82 to '/jhora02/rlvtssa_8_data32.dbf';
set newname for datafile 83 to '/jhora02/rlvtssa_8_data33.dbf';
set newname for datafile 84 to '/jhora02/rlvtssa_8_data34.dbf';
set newname for datafile 85 to '/jhora02/rlvtssa_4_data30.dbf';
set newname for datafile 86 to '/jhora02/rlvtssa_4_data39.dbf';
set newname for datafile 87 to '/jhora02/rlvtssa_8_data41.dbf';
set newname for datafile 88 to '/jhora02/rlvtssa_8_data42.dbf';
set newname for datafile 89 to '/jhora02/rlvtssa_8_data43.dbf';
set newname for datafile 90 to '/jhora02/rlvtssa_4_data40.dbf';
set newname for datafile 91 to '/jhora02/rlvtssa_4_data41.dbf';
set newname for datafile 92 to '/jhora02/rlvtssa_4_data42.dbf';
set newname for datafile 93 to '/jhora02/rlvtssa_2_data02.dbf';
set newname for datafile 94 to '/jhora02/rlvtssa_2_data03.dbf';
set newname for datafile 95 to '/jhora02/rlvtssa_2_data05.dbf';
set newname for datafile 96 to '/jhora02/rlvtssa_8_data44.dbf';
set newname for datafile 97 to '/jhora02/rlvtssa_8_data45.dbf';
set newname for datafile 98 to '/jhora02/rlvtssa_8_data35.dbf';
set newname for datafile 99 to '/jhora02/rlvtssa_8_data31.dbf';
set newname for datafile 100 to '/jhora02/rlvtssa_8_data46.dbf';
set newname for datafile 101 to '/jhora02/rlvtssa_8_data47.dbf';
set newname for datafile 102 to '/jhora02/rlvtssa_8_data48.dbf';
set newname for datafile 103 to '/jhora02/rlvtssa_8_data49.dbf';
set newname for datafile 104 to '/jhora02/rlvtssa_8_data36.dbf';
set newname for datafile 105 to '/jhora02/rlvtssa_8_data50.dbf';
set newname for datafile 106 to '/jhora02/rlvtssa_4_data45.dbf';
set newname for datafile 107 to '/jhora02/rlvtssa_8_data51.dbf';
set newname for datafile 108 to '/jhora02/rlvtssa_8_data52.dbf';
set newname for datafile 109 to '/jhora02/rlvtssa_8_data53.dbf';
set newname for datafile 110 to '/jhora02/rlvtssa_4_data46.dbf';
set newname for datafile 111 to '/jhora02/rlvtssa_4_data47.dbf';
set newname for datafile 112 to '/jhora02/rlvtssa_4_data48.dbf';
set newname for datafile 113 to '/jhora02/rlvtssa_4_data49.dbf';
set newname for datafile 114 to '/jhora02/rlvtssa_8_data54.dbf';
set newname for datafile 115 to '/jhora02/rlvtssa_8_data55.dbf';
set newname for datafile 116 to '/jhora02/rlvtssa_8_data56.dbf';
set newname for datafile 117 to '/jhora02/rlvtssa_4_data38.dbf';
set newname for datafile 118 to '/jhora02/rlvtssa_4_data28.dbf';
set newname for datafile 119 to '/jhora02/rlvtssa_4_data29.dbf';
set newname for datafile 120 to '/jhora02/rlvtssa_2_data08.dbf';
set newname for datafile 121 to '/jhora02/rlvtssa_4_data52.dbf';
set newname for datafile 122 to '/jhora02/rlvtssa_4_data53.dbf';
set newname for datafile 123 to '/jhora02/rlvtssa_8_data57.dbf';
set newname for datafile 124 to '/jhora02/rlvtssa_8_data58.dbf';
set newname for datafile 125 to '/jhora02/rlvtssa_8_data62.dbf';
set newname for datafile 126 to '/jhora02/rlvtssa_8_data63.dbf';
set newname for datafile 127 to '/jhora02/rlvtssa_8_data60.dbf';
set newname for datafile 128 to '/jhora02/rlvtssa_8_data64.dbf';
set newname for datafile 129 to '/jhora02/rlvtssa_8_data65.dbf';
set newname for datafile 130 to '/jhora02/rlvtssa_8_data66.dbf';
set newname for datafile 131 to '/jhora02/rlvtssa_8_data67.dbf';
set newname for datafile 132 to '/jhora02/rlvtssa_8_data68.dbf';
set newname for datafile 133 to '/jhora02/rlvtssa_8_data69.dbf';
set newname for datafile 134 to '/jhora02/rlvtssa_8_data70.dbf';
set newname for datafile 135 to '/jhora02/rlvtssa_8_data71.dbf';
set newname for datafile 136 to '/jhora02/rlvtssa_8_data72.dbf';
set newname for datafile 137 to '/jhora02/rlvtssa_8_data73.dbf';
set newname for datafile 138 to '/jhora02/rlvtssa_8_data74.dbf';
set newname for datafile 139 to '/jhora02/rlvtssa_8_data61.dbf';
set newname for datafile 140 to '/jhora02/rlvtssa_8_data59.dbf';
set newname for datafile 141 to '/jhora02/rlvtssa_8_data75.dbf';
set newname for datafile 142 to '/jhora02/rlvtssa_8_data76.dbf';
set newname for datafile 143 to '/jhora02/rlvtssa_8_data77.dbf';
set newname for datafile 144 to '/jhora02/rlvtssa_8_data78.dbf';
set newname for datafile 145 to '/jhora02/rlvtssa_4_data59.dbf';
set newname for datafile 146 to '/jhora02/rlvtssa_8_data79.dbf';
set newname for datafile 147 to '/jhora02/rlvtssa_8_data80.dbf';
set newname for datafile 148 to '/jhora02/rlvtssa_8_data84.dbf';
set newname for datafile 149 to '/jhora02/rlvtssa_8_data85.dbf';
set newname for datafile 150 to '/jhora02/rlvtssa_8_data86.dbf';
set newname for datafile 151 to '/jhora02/rlvtssa_8_data87.dbf';
set newname for datafile 152 to '/jhora02/rlvtssa_8_data88.dbf';
set newname for datafile 153 to '/jhora02/rlvtssa_8_data89.dbf';
set newname for datafile 154 to '/jhora02/rlvtssa_8_data90.dbf';
set newname for datafile 155 to '/jhora02/rlvtssa_8_data91.dbf';
set newname for datafile 156 to '/jhora03/rlvtssa_8_103.dbf';
set newname for datafile 157 to '/jhora03/rlvtssa_8_104.dbf';
set newname for datafile 158 to '/jhora03/rlvtssa_8_107.dbf';
set newname for datafile 159 to '/jhora03/rlvtssa_8_data96.dbf';
set newname for datafile 160 to '/jhora03/rlvtssa_8_data97.dbf';
set newname for datafile 161 to '/jhora03/rlvtssa_8_data98.dbf';
set newname for datafile 162 to '/jhora03/rlvtssa_8_data99.dbf';
set newname for datafile 163 to '/jhora03/rlvtssa_8_100.dbf';
set newname for datafile 164 to '/jhora03/rlvtssa_8_101.dbf';
set newname for datafile 165 to '/jhora03/rlvtssa_8_102.dbf';
set newname for datafile 166 to '/jhora03/rlvtssa_8_115.dbf';
set newname for datafile 167 to '/jhora03/rlvtssa_8_116.dbf';
set newname for datafile 168 to '/jhora03/rlvtssa_8_118.dbf';
set newname for datafile 169 to '/jhora03/rlvtssa_8_120.dbf';
set newname for datafile 170 to '/jhora03/rlvtssa_8_108.dbf';
set newname for datafile 171 to '/jhora03/rlvtssa_8_109.dbf';
set newname for datafile 172 to '/jhora03/rlvtssa_8_105.dbf';
set newname for datafile 173 to '/jhora03/rlvtssa_8_106.dbf';
set newname for datafile 174 to '/jhora03/rlvtssa_8_112.dbf';
set newname for datafile 175 to '/jhora03/rlvtssa_8_113.dbf';
set newname for datafile 176 to '/jhora03/rlvtssa_8_121.dbf';
set newname for datafile 177 to '/jhora03/rlvtssa_8_110.dbf';
set newname for datafile 178 to '/jhora03/rlvtssa_8_111.dbf';
set newname for datafile 179 to '/jhora03/rlvtssa_8_122.dbf';
set newname for datafile 180 to '/jhora03/rlvtssa_8_125.dbf';
set newname for datafile 181 to '/jhora03/rlvtssa_8_114.dbf';
set newname for datafile 182 to '/jhora03/rlvtssa_8_data92.dbf';
set newname for datafile 183 to '/jhora03/rlvtssa_8_data93.dbf';
set newname for datafile 184 to '/jhora03/rlvtssa_8_117.dbf';
set newname for datafile 185 to '/jhora03/rlvtssa_8_data94.dbf';
set newname for datafile 186 to '/jhora03/rlvtssa_8_119.dbf';
set newname for datafile 187 to '/jhora03/rlvtssa_8_data95.dbf';
set newname for datafile 188 to '/jhora03/rlvtssa_8_126.dbf';
set newname for datafile 189 to '/jhora03/rlvtssa_8_133.dbf';
set newname for datafile 190 to '/jhora03/rlvtssa_8_164.dbf';
set newname for datafile 191 to '/jhora03/rlvtssa_8_123.dbf';
set newname for datafile 192 to '/jhora03/rlvtssa_8_124.dbf';
set newname for datafile 193 to '/jhora03/rlvtssa_8_138.dbf';
set newname for datafile 194 to '/jhora03/rlvtssa_8_127.dbf';
set newname for datafile 195 to '/jhora03/rlvtssa_8_128.dbf';
set newname for datafile 196 to '/jhora03/rlvtssa_8_129.dbf';
set newname for datafile 197 to '/jhora03/rlvtssa_8_130.dbf';
set newname for datafile 198 to '/jhora03/rlvtssa_8_131.dbf';
set newname for datafile 199 to '/jhora03/rlvtssa_8_139.dbf';
set newname for datafile 200 to '/jhora03/rlvtssa_8_132.dbf';
set newname for datafile 201 to '/jhora03/rlvtssa_8_134.dbf';
set newname for datafile 202 to '/jhora03/rlvtssa_8_135.dbf';
set newname for datafile 203 to '/jhora03/rlvtssa_8_136.dbf';
set newname for datafile 204 to '/jhora03/rlvtssa_8_137.dbf';
set newname for datafile 205 to '/jhora03/rlvtssa_8_140.dbf';
set newname for datafile 206 to '/jhora03/rlvtssa_8_155.dbf';
set newname for datafile 207 to '/jhora03/rlvtssa_8_157.dbf';
set newname for datafile 208 to '/jhora03/rlvtssa_8_141.dbf';
set newname for datafile 209 to '/jhora03/rlvtssa_8_142.dbf';
set newname for datafile 210 to '/jhora03/rlvtssa_8_143.dbf';
set newname for datafile 211 to '/jhora03/rlvtssa_8_144.dbf';
set newname for datafile 212 to '/jhora03/rlvtssa_8_145.dbf';
set newname for datafile 213 to '/jhora03/rlvtssa_8_146.dbf';
set newname for datafile 214 to '/jhora03/rlvtssa_8_147.dbf';
set newname for datafile 215 to '/jhora03/rlvtssa_8_148.dbf';
set newname for datafile 216 to '/jhora03/rlvtssa_8_149.dbf';
set newname for datafile 217 to '/jhora03/rlvtssa_8_150.dbf';
set newname for datafile 218 to '/jhora03/rlvtssa_8_152.dbf';
set newname for datafile 219 to '/jhora03/rlvtssa_8_153.dbf';
set newname for datafile 220 to '/jhora03/rlvtssa_8_169.dbf';
set newname for datafile 221 to '/jhora03/rlvtssa_8_154.dbf';
set newname for datafile 222 to '/jhora03/rlvtssa_8_171.dbf';
set newname for datafile 223 to '/jhora03/rlvtssa_8_156.dbf';
set newname for datafile 224 to '/jhora03/rlvtssa_8_172.dbf';
set newname for datafile 226 to '/jhora03/rlvtssa_8_160.dbf';
set newname for datafile 227 to '/jhora03/rlvtssa_8_159.dbf';
set newname for datafile 229 to '/jhora03/rlvtssa_8_163.dbf';
set newname for datafile 231 to '/jhora03/rlvtssa_8_165.dbf';
set newname for datafile 232 to '/jhora03/rlvtssa_8_166.dbf';
set newname for datafile 233 to '/jhora03/rlvtssa_8_167.dbf';
set newname for datafile 234 to '/jhora03/rlvtssa_8_168.dbf';
set newname for datafile 236 to '/jhora03/rlvtssa_8_170.dbf';
restore database;
switch datafile all;
}
9、将老库生产的所有归档日志复制到新库对应的目录下/jharch/backup
SQL>recover database using backup controlfile until cancel;
10、Rename the redo logs to new shared device,Add second instance redo logs
alter database rename file '/dev/rlvtssa_redo1_1' to '/jhora01/redo_1_1.dbf';
alter database rename file '/dev/rlvtssa_redo2_1' to '/jhora02/redo_1_2.dbf';
alter database rename file '/dev/rlvtssa_redo3_1' to '/jhora03/redo_1_3.dbf';
alter database rename file '/dev/rlvtssa_redo4_1' to '/jhora01/redo_1_4.dbf';
alter database rename file '/dev/rlvtssa_redo5_1' to '/jhora02/redo_1_5.dbf';
alter database add logfile thread 1 group 6 '/jhora03/redo_1_6.dbf' size 1024M;
alter database add logfile thread 2 group 7 '/jhora01/redo_2_1.dbf' size 1024M;
alter database add logfile thread 2 group 8 '/jhora02/redo_2_2.dbf' size 1024M;
alter database add logfile thread 2 group 9 '/jhora03/redo_2_3.dbf' size 1024M;
alter database add logfile thread 2 group 10 '/jhora01/redo_2_4.dbf' size 1024M;
alter database add logfile thread 2 group 11 '/jhora02/redo_2_5.dbf' size 1024M;
alter database add logfile thread 2 group 12 '/jhora03/redo_2_6.dbf' size 1024M;
11、检查老数据库对象数据及状态核对,然后停掉数据库
SQL>select owner,object_type,status ,count(*) from dba_objects group by owner,object_type,status order by 4;
dzgddb02:[/]#su - oracle
dzgddb02$[/home/oracle]lsnrctl stop LISTENER
dzgddb02$[/home/oracle]ps -ef |grep LOCAL=NO |awk '{print $2}'|xargs kill -9
dzgddb02$[/home/oracle]sqlplus / as sysdba
SQL> alter system archive log current;
SQL> alter sysytem switch logfile;(5次)
SQL>shutdown immediate;
12、将新生产的归档日志复制至新环境下的/jharch/backup目录下,追加最新的archlog,打开数据库
SQL>recover database using backup controlfile until cancel;
------在打开数据库之前检查两边的SCN值是否一样------
select a.name,
a.checkpoint_change# start_SCN,
b.checkpoint_change# last_SCN
from v$datafile_header a, v$datafile b
where a.file# = b.file#;
------如无问题,打开数据库------
SQL>alter database open resetlogs;
13、激活thread 2
------(要在OPEN模式下才能激活)------
alter database enable public thread 2;
14、create the second (or more) instance undo tablespace from existing instance
------(需要在OPEN模式下才能创建)------
create undo tablespace UNDOTBS1 datafile '/jhora01/rundotbs1_10g_01.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS1 add datafile '/jhora02/rundotbs1_10g_02.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS1 add datafile '/jhora03/rundotbs1_10g_03.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS1 add datafile '/jhora01/rundotbs1_10g_04.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS1 add datafile '/jhora02/rundotbs1_10g_05.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS1 add datafile '/jhora03/rundotbs1_10g_06.dbf' size 10240M autoextend off;
create undo tablespace UNDOTBS2 datafile '/jhora01/rundotbs2_10g_01.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS2 add datafile '/jhora02/rundotbs2_10g_02.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS2 add datafile '/jhora03/rundotbs2_10g_03.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS2 add datafile '/jhora01/rundotbs2_10g_04.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS2 add datafile '/jhora02/rundotbs2_10g_05.dbf' size 10240M autoextend off;
alter tablespace UNDOTBS2 add datafile '/jhora03/rundotbs2_10g_06.dbf' size 10240M autoextend off;
15、重建TEMP表空间
------(需要在OPEN模式下才能创建)------
CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/jhora01/rtemp01_10g_01.dbf' SIZE 10240M autoextend off;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/jhora01/rtemp_10g_01.dbf' SIZE 10240M,
'/jhora02/rtemp_10g_02.dbf' SIZE 10240M,
'/jhora03/rtemp_10g_03.dbf' SIZE 10240M,
'/jhora01/rtemp_10g_04.dbf' size 10240M,
'/jhora02/rtemp_10g_05.dbf' size 10240M,
'/jhora03/rtemp_10g_06.dbf' size 10240M autoextend off;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP01 INCLUDING CONTENTS AND DATAFILES;
16、停新库,修改/jharch/initdzgddb.ora参数值
SQL>shutdown immediate
------因原有库*.undo_tablespace='UNDOTBS01',现改为*.undo_tablespace='UNDOTBS1'
------增加如下参数------
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
zhjhdb1.undo_tablespace=UNDOTBS1
zhjhdb1.instance_name=zhjhdb1
zhjhdb1.instance_number=1
zhjhdb1.thread=1
zhjhdb1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=134.225.80.28)(PORT=1521))))'
zhjhdb2.instance_name=zhjhdb2
zhjhdb2.instance_number=2
zhjhdb2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=134.225.80.29)(PORT=1521))))'
zhjhdb2.thread=2
zhjhdb2.undo_tablespace=UNDOTBS2
zhjhdb2.cluster_database = TRUE
zhjhdb2.cluster_database_instances = 2
17、用修改的pfile 来创建spfile,注意放在共享设备上
SQL>startup nomount pfile='/jharch/initdzgddb.ora';
SQL>create spfile='/jhora01/spfiledzgddb.ora' from pfile='/jharch/initdzgddb.ora';
18、在所有节点上,修改pfile内容,将其指向共享设备上的spfile:
------一节点------
$ echo "spfile='/jhora01/spfiledzgddb.ora'" > /oracle/app/oracle/product/10g/db/dbs/initzhjhdb1.ora
$ cat initzhjhdb1.ora
spfile='/jhora01/spfiledzgddb.ora'
------二节点------
$ echo "spfile='/jhora01/spfiledzgddb.ora'" > /oracle/app/oracle/product/10g/db/dbs/initzhjhdb2.ora
$ cat initzhjhdb2.ora
spfile='/jhora01/spfiledzgddb.ora'
19、create new password file for instance
orapwd file=/oracle/app/oracle/product/10g/db/dbs/orapwzhjhdb1 password=oracle
orapwd file=/oracle/app/oracle/product/10g/db/dbs/orapwzhjhdb2 password=oracle
20、两个节点分别启动实例
shutdown二节点,启动实例startup
在二节点上执行catclust.sql脚本来创建相关视图
SQL>@?/rdbms/admin/catclust.sql
------一节点------
SQL>startup
21、使用srvctl 创建RAC服务(on one of the nodes)
-----将其他信息注册到CRS里------
$srvctl add database -d dzgddb -o $ORACLE_HOME -p /jhora01/spfileDZGDDB.ora (此出spfileDZGDDB.ora应改为spfiledzgddb.ora,我创建的spfile是小写,导致crs认为无此文件,srvctl拉不起实例和DB,小小错误,谨记!!!)
$srvctl add instance -d dzgddb -i zhjhdb1 -n zhjhdb11
$srvctl add instance -d dzgddb -i zhjhdb2 -n zhjhdb12
通过crs_stat -t可以看到
ora.dzgddb.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
ora....11.lsnr application ONLINE ONLINE zhjhdb11
ora....b11.gsd application ONLINE ONLINE zhjhdb11
ora....b11.ons application ONLINE ONLINE zhjhdb11
ora....b11.vip application ONLINE ONLINE zhjhdb11
ora....12.lsnr application ONLINE ONLINE zhjhdb12
ora....b12.gsd application ONLINE ONLINE zhjhdb12
ora....b12.ons application ONLINE ONLINE zhjhdb12
ora....b12.vip application ONLINE ONLINE zhjhdb12
此时并没有启动,因为我们的服务是刚添加上来的,还没有同步。 我们启动一下就ok了
$srvctl start database -d dzgddb
22、检查crs状态及后台日志
zhjhdb11:/# crsctl start crs
zhjhdb12:/# crsctl start crs
zhjhdb11:/# su - oracle
-----------------check oracle cluster status--------------------
$crs_stat -t
如果所有服务均是online,以下部分不用操作。
$crs_start -all
$crs_stat -t
--------------check database start logs-------
$cd $ORACLE_BASE/admin/*/bdump
$tail -200f al*.log|more
23、检查老数据库对象数据及状态核对
SQL>select owner,object_type,status ,count(*) from dba_objects group by owner,object_type,status order by 4;
-----遇到问题-------
1.修改了spfilexx.ora文件后,起不来
报错
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/jhora01/spfileDZGDDB.ora'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Additional information: 1969
Additional information: 512
解决方式:重建spfilexx.ora
原因:
修改了Oracle 10g里面的SPFILE***.ORA文件,数据库就起不来了,抱错ORA-27046: file size is not a multiple of logical block size 。原因是动态服务器参数文件(SPFILE)是一个二进制文件,是不可以手工修改的,如果手工改了,该文件就成为了无效文件。
此时可以通过以下方法解决:
1。将SPFILE中的参数拷贝到init***.ora文件中
2。删除DATABASE目录下的SPFILE***.ORA文件。
3。启动数据库 (现在应该可以成功启动)
4。SQL> create spfile from pfile 生成SPFIEL
使用startup命令启动数据库,,Oralce将会按照以下顺序在缺省目录中(9i,10i在oracle_home/database目录下)搜索参数文件:(1)spfile***.ora文件 (2)如果没有spfile***.ora文件就用spfile.ora文件(3) 如果没有spfile.ora文件就用init***.ora(4)如果没有init***.ora文件就用 pfile.ora
所以上面我们采取的解决方法是将破坏的SPFILE中的参数复制到init***.ora中,然后删除SPFILE文件,这样启动时就会用init***.ora文件启动,然后用create spfile from pfile 命令生成SPFIEL
以后如果要修改SPFILE中的参数绝对不能在手工修改SPFILE文件了,用下面命令:alter system set parameter=Value scope=spfile|both|memory 例如:alter system set db_cache_size=24M scope=both;来修改