工程日志数据库迁移到asm.doc

（项目名称）-工程日志（工程部）工程日志填报日期：10327客户名称产品名称参与人员到达现场实施目的数据库迁移到ASM项目环境源数据库目标数据库操作系统AIXAIX主机名Aix207Aix207数据库版本Oracle 10.2.0.1.0Oracle 10.2.0.1.0实例名ProdProd监听LISTENER/1521LISTENER/1521项目实施1 查询磁盘信息rootaix207 /#lsdev -c diskhdisk0 Available 1S-08-00-8,0 16 Bit LVD SCSI Disk Drivehdisk1 Available 1n-08-02 Other FC SCSI Disk Drivehdisk2 Available 1n-08-02 Other FC SCSI Disk Drivehdisk3 Available 1n-08-02 Other FC SCSI Disk Drivehdisk4 Available 1n-08-02 Other FC SCSI Disk Drivehdisk5 Available 1n-08-02 Other FC SCSI Disk Drivehdisk6 Defined 1n-08-02 Other FC SCSI Disk Driverootaix207 /#lsattr -El hdisk1clr_q no Device CLEARS its Queue on error Truelocation Location Label Truelun_id 0x1000000000000 Logical Unit Number ID Falsemax_transfer 0x40000 Maximum TRANSFER Size Truenode_name 0x50020f200000c39a FC Node Name Falsepvid 0056f03ea9b901f60000000000000000 Physical volume identifier Falseq_err yes Use QERR bit Trueq_type simple Queuing TYPE Truequeue_depth 1 Queue DEPTH Truereassign_to 120 REASSIGN time out value Truerw_timeout 30 READ/WRITE time out value Truescsi_id 0x10800 SCSI ID Falsestart_timeout 60 START unit time out value Trueww_name 0x50020f230000c39a FC World Wide Name False2 创建asm磁盘设备rootaix207 /#smit vgrootaix207 /#lsvg asmvgVOLUME GROUP: asmvg VG IDENTIFIER: 0056f03e00004c000000014501a5686fVG STATE: active PP SIZE: 64 megabyte(s)VG PERMISSION: read/write TOTAL PPs: 480 (30720 megabytes)MAX LVs: 256 FREE PPs: 480 (30720 megabytes)LVs: 0 USED PPs: 0 (0 megabytes)OPEN LVs: 0 QUORUM: 2 (Enabled)TOTAL PVs: 1 VG DESCRIPTORS: 2STALE PVs: 0 STALE PPs: 0ACTIVE PVs: 1 AUTO ON: yesMAX PPs per VG: 32512 MAX PPs per PV: 1016 MAX PVs: 32LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: noHOT SPARE: no BB POLICY: relocatablerootaix207 /#mklv -y lv_asm1 -t raw asmvg 160lv_asm1rootaix207 /#mklv -y lv_asm2 -t raw asmvg 160lv_asm2rootaix207 /#mklv -y lv_asm3 -t raw asmvg 160lv_asm3rootaix207 /#lsvg -l asmvgasmvg:LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINTlv_asm1 raw 160 160 1 closed/syncd N/Alv_asm2 raw 160 160 1 closed/syncd N/Alv_asm3 raw 160 160 1 closed/syncd N/Arootaix207 /#chown -R oracle:oinstall /dev/rlv_asm1 rootaix207 /#chown -R oracle:oinstall /dev/rlv_asm2rootaix207 /#chown -R oracle:oinstall /dev/rlv_asm3rootaix207 /#ls -l /dev | grep rlv_asmcrw-rw- 1 oracle oinstall 51, 1 Mar 27 12:49 rlv_asm1crw-rw- 1 oracle oinstall 51, 2 Mar 27 12:50 rlv_asm2crw-rw- 1 oracle oinstall 51, 3 Mar 27 12:50 rlv_asm33 asm实例配置3.1 建立相关目录12:09:11oracleaix207 $cd /u01/app/oracle/admin/12:53:43oracleaix207 admin$mkdir -p +ASM/bdump12:54:11oracleaix207 admin$mkdir -p +ASM/cdump12:54:20oracleaix207 admin$mkdir -p +ASM/udump12:54:57oracleaix207 admin$ls -ltotal 0drwxr-xr-x 5 oracle oinstall 256 Mar 27 12:54 +ASMdrwxr-x- 8 oracle oinstall 256 Mar 17 19:27 proddrwxr-x- 8 oracle oinstall 256 Mar 18 20:16 test3.2创建ASM实例初始化参数文件12:55:13oracleaix207 admin$cd /u01/app/oracle/product/10.2.0/db_1/dbs/12:57:37oracleaix207 dbs$vi init+ASM.orainit+ASM.ora New file *.background_dump_dest=/u01/app/oracle/admin/+ASM/bdump*.core_dump_dest=/u01/app/oracle/admin/+ASM/cdump*.instance_type=asm*.large_pool_size=12M*.remote_login_passwordfile=SHARED*.user_dump_dest=/u01/app/oracle/admin/+ASM/udump*.asm_diskstring=/dev/rlv_asm*3.3 ASM实例启动13:29:56oracleaix207 dbs$su rootroots Password:tuh W$bash13:33:27rootaix207 dbs#cd $ORACLE_HOME/bin13:33:37rootaix207 bin#./localconfig delete/etc/oracle does not exist. Creating it now.No such file or directory/etc/init.cssd519: /etc/oracle/scls_scr/aix207/root/cssrun: cannot createtouch: /etc/oracle/scls_scr/aix207/root/nooprocd cannot createtouch: /etc/oracle/scls_scr/aix207/root/noclsmon cannot createFailure 33 in main OCR context initialization: PROC-33: Oracle Cluster Registry is not configured Operating System error No such file or directory 2Shutdown has begun. The daemons should exit soon.13:33:48rootaix207 bin#./localconfig addSuccessfully accumulated necessary OCR keys.Creating OCR keys for user root, privgrp system.Operation successful.Configuration for local CSS has been initializedAdding to inittabStartup will be queued to init within 30 seconds.Checking the status of new Oracle init process.Expecting the CRS daemons to be up within 600 seconds.CSS is active on these nodes. aix207CSS is active on all nodes.Oracle CSS service is installed and running under init(1M)13:36:30oracleaix207 $export ORACLE_SID=+ASM13:36:33oracleaix207 $sqlplus /as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 13:36:49 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.13:36:52 SYS +ASMstartupASM instance startedTotal System Global Area 130023424 bytesFixed Size 2019136 bytesVariable Size 102838464 bytesASM Cache 25165824 bytesORA-15110: no diskgroups mounted3.4 ASM磁盘组管理3.4.1 创建磁盘组13:37:27 SYS +ASMcreate diskgroup DG1 normal redundancy13:39:57 2 failgroup FG1 disk /dev/rlv_asm113:40:25 3 failgroup FG2 disk /dev/rlv_asm213:40:27 4 failgroup FG3 disk /dev/rlv_asm3;Diskgroup created.13:47:19 SYS +ASMselect instance_name,status from v$instance;INSTANCE_NAME STATUS- -+ASM STARTED如果阵列已经有RAID保护，就用external redundancy（外部冗余,磁盘数最好为偶数），就可不需再做normal redundancy（双向冗余），以免增加IO。3.4.2 查看磁盘组13:41:02SYS+ASMselectGROUP_NUMBER,DISK_NUMBER,NAME,FAILGROUP,CREATE_DATE,PATH from v$asm_disk;GROUP_NUMBER DISK_NUMBER NAME FAILGROUP CREATE_DA PATH- - - - 1 0 DG1_0000 FG1 27-MAR-14 /dev/rlv_asm1 1 1 DG1_0001 FG2 27-MAR-14 /dev/rlv_asm2 1 2 DG1_0002 FG3 27-MAR-14 /dev/rlv_asm34 数据库迁移4.1将数据文件备份到ASM磁盘组：+DG112:09:27oracleaix207 $export ORACLE_SID=prod13:53:19oracleaix207 $sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 13:53:41 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.13:53:41 SYS prodstartup ORACLE instance started.Total System Global Area 612368384 bytesFixed Size 2022800 bytesVariable Size 134218352 bytesDatabase Buffers 469762048 bytesRedo Buffers 6365184 bytesDatabase mounted.Database opened.13:57:29oracleaix207 $rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 27 13:57:38 2014Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: PROD (DBID=254308570, not open)RMAN run2 shutdown immediate;3 startup mount;4 allocate channel c1 type disk;5 allocate channel c2 type disk;6 allocate channel c3 type disk;7 allocate channel c4 type disk;8 backup as copy incremental level 0 database format +DG1 TAG ORA_ASM_MIGRATION;9 using target database control file instead of recovery catalogdatabase dismountedOracle instance shut downconnected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area 612368384 bytesFixed Size 2022800 bytesVariable Size 134218352 bytesDatabase Buffers 469762048 bytesRedo Buffers 6365184 bytesallocated channel: c1channel c1: sid=157 devtype=DISKallocated channel: c2channel c2: sid=154 devtype=DISKallocated channel: c3channel c3: sid=153 devtype=DISKallocated channel: c4channel c4: sid=152 devtype=DISKStarting backup at 27-MAR-14channel c1: starting datafile copyinput datafile fno=00004 name=/disk1/oradata/prod/users01.dbfchannel c2: starting datafile copyinput datafile fno=00002 name=/u01/app/oracle/oradata/prod/undotbs01.dbfchannel c3: starting datafile copyinput datafile fno=00001 name=/u01/app/oracle/oradata/prod/system01.dbfchannel c4: starting datafile copyinput datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbfoutput filename=+DG1/prod/datafile/sysaux.258.843314417 tag=ORA_ASM_MIGRATION recid=24 stamp=843314663channel c4: datafile copy complete, elapsed time: 00:04:25channel c4: starting datafile copyinput datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbfoutput filename=+DG1/prod/datafile/example.260.843314673 tag=ORA_ASM_MIGRATION recid=25 stamp=843314867channel c4: datafile copy complete, elapsed time: 00:03:25output filename=+DG1/prod/datafile/undotbs1.256.843314415 tag=ORA_ASM_MIGRATION recid=26 stamp=843314905channel c2: datafile copy complete, elapsed time: 00:08:28output filename=+DG1/prod/datafile/system.259.843314417 tag=ORA_ASM_MIGRATION recid=27 stamp=843314908channel c3: datafile copy complete, elapsed time: 00:08:27output filename=+DG1/prod/datafile/users.257.843314417 tag=ORA_ASM_MIGRATION recid=28 stamp=843315048channel c1: datafile copy complete, elapsed time: 00:10:54Finished backup at 27-MAR-14Starting Control File and SPFILE Autobackup at 27-MAR-14piece handle=/local_backup/backup/control/c-254308570-20140327-00 comment=NONEFinished Control File and SPFILE Autobackup at 27-MAR-14released channel: c1released channel: c2released channel: c3released channel: c44.2将初始化参数文件转储到ASM磁盘组RMAN backup as backupset spfile;Starting backup at 27-MAR-14allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=157 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=154 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 27-MAR-14channel ORA_DISK_1: finished piece 1 at 27-MAR-14piece handle=/local_backup/backup/channel/9sp47u0c_1_1 tag=TAG20140327T141330 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 27-MAR-14Starting Control File and SPFILE Autobackup at 27-MAR-14piece handle=/local_backup/backup/control/c-254308570-20140327-01 comment=NONEFinished Control File and SPFILE Autobackup at 27-MAR-14RMAN restore spfile to +DG1/spfileprod.ora;Starting restore at 27-MAR-14using channel ORA_DISK_1using channel ORA_DISK_2channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: restoring SPFILEoutput filename=+DG1/spfileprod.orachannel ORA_DISK_1: reading from backup piece /local_backup/backup/control/c-254308570-20140327-01channel ORA_DISK_1: restored backup piece 1piece handle=/local_backup/backup/control/c-254308570-20140327-01 tag=TAG20140327T141333channel ORA_DISK_1: restore complete, elapsed time: 00:00:27Finished restore at 27-MAR-144.3修改初始化参数文件initprod.ora，使其指向ASM磁盘组的spfileprod.ora14:15:39oracleaix207$cd $ORACLE_HOME/dbs14:15:39oracleaix207 dbs$mv intprod.ora initprod.ora.bk 14:23:16oracleaix207dbs$mv spfileprod.ora spfileprod.ora.old 14:15:55oracleaix207 dbs$vi initprod.ora SPFILE=+DG1/spfileprod.ora 添加内容14:28:10 SYS prodstartup mountORACLE instance started.Total System Global Area 612368384 bytesFixed Size 2022800 bytesVariable Size 138412656 bytesDatabase Buffers 465567744 bytesRedo Buffers 6365184 bytesDatabase mounted.14:28:52 SYS prodshow parameter spfileNAME TYPE VALUE- - -spfile string +DG1/spfileprod.ora4.4 修改控制文件的位置14:38:19 SYS prodalter system set db_create_file_dest=+DG1 SID=prod;System altered.14:39:42 SYS prodalter system set control_files=+DG1,+DG1,+DG1 scope=spfile sid=prod;System altered.14:39:50 SYS prodstartup nomount;ORACLE instance started.Total System Global Area 612368384 bytesFixed Size 2022800 bytesVariable Size 138412656 bytesDatabase Buffers 465567744 bytesRedo Buffers 6365184 bytesRMAN restore controlfile from /local_backup/backup/control/c-254308570-20140327-00;Starting restore at 27-MAR-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:35output filename=+DG1/prod/controlfile/current.262.843316871output filename=+DG1/prod/controlfile/current.263.843316881output filename=+DG1/prod/controlfile/current.264.843316885Finished restore at 27-MAR-1414:40:08 SYS prodalter database mount;Database altered.14:42:47 SYS prodselect name from v$controlfile;NAME-+DG1/prod/controlfile/current.262.843316871+DG1/prod/controlfile/current.263.843316881+DG1/prod/controlfile/current.264.8433168854.5 修改控制文件中数据文件的位置RMAN switch database to copy; Switch Database可将数据文件存放目录迁移到初始化参数中指定的位置released channel: ORA_DISK_1Starting implicit crosscheck backup at 27-MAR-14allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=151 devtype=DISKCrosschecked 11 objectsFinished implicit crosscheck backup at 27-MAR-14Starting implicit crosscheck copy at 27-MAR-14using channel ORA_DISK_1using channel ORA_DISK_2Crosschecked 5 objectsFinished implicit crosscheck copy at 27-MAR-14searching for all files in the recovery areacataloging files.no files catalogeddatafile 1 switched to datafile copy +DG1/prod/datafile/system.259.843314417datafile 2 switched to datafile copy +DG1/prod/datafile/undotbs1.256.843314415datafile 3 switched to datafile copy +DG1/prod/datafile/sysaux.258.843314417datafile 4 switched to datafile copy +DG1/prod/datafile/users.257.843314417datafile 5 switched to datafile copy +DG1/prod/datafile/example.260.84331467314:43:03 SYS prodselect name from v$datafile;查询数据文件的位置NAME-+DG1/prod/datafile/system.259.843314417+DG1/prod/datafile/undotbs1.256.843314415+DG1/prod/datafile/sysaux.258.843314417+DG1/prod/datafile/users.257.843314417+DG1/prod/datafile/example.260.8433146734.6 恢复数据库RMAN run2 allocate channel c1 device type disk;3 allocate channel c2 device type disk;4 allocate channel c3 device type disk;5 allocate channel c4 device type disk;6 recover database;7 released channel: ORA_DISK_1released channel: ORA_DISK_2allocated channel: c1channel c1: sid=155 devtype=DISKallocated channel: c2channel c2: sid=151 devtype=DISKallocated channel: c3channel c3: sid=149 devtype=DISKallocated channel: c4channel c4: sid=148 devtype=DISKStarting recover at 27-MAR-14starting media recoveryarchive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/oradata/prod/redo01.logarchive log filename=/u01/app/oracle/oradata/prod/redo01.log thread=1 sequence=26media recovery complete, elapsed time: 00:00:07Finished recover at 27-MAR-14released channel: c1released channel: c2released channel: c3released channel: c44.7 resetlogs开库，检查各文件位置14:46:55 SYS prodalter database open resetlogs;Database altered.如果在转储控制文件时用原控制文件来做转储，开库时不用open resetlogs;如：RMAN restore controlfile from /u01/app/oracle/oradata/prod/control01.ctl;SQLalter database open;14:56:07 SYS prodselect FILE#,STATUS,NAME from v$datafile;NAME-+DG1/prod/datafile/system.259.843314417+DG1/prod/datafile/undotbs1.