分类: Oracle
2010-12-01 15:12:30
数据库移植到ASM实践
ORACLE_SID=TEST
实施步骤:
1,将控制文件restore到ASM(1个)
2,利用RMAN将数据文件移植到ASM,以及控制文件(未移植临时表空间的数据文件)
3,移植redo文件
4,移植临时表空间(数据文件)
5,创建更多个控制文件
6,重启库,完毕。
1>将控制文件路径更改到ASM磁盘组
SQL> alter system set control_files='+DATA1/test/control01.ctl' scope=spfile;
关闭数据库
SQL>shutdown immediate;
2>用rman来进行移植
//连接指定目标数据库
[oracle@node2 oracle]$ export ORACLE_SID=TEST
[oracle@node2 oracle]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 30 19:14:08 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
//启动目标数据库
RMAN> startup nomount
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
//使用RMAN将控制文件copy到ASM磁盘上,RMAN会将控制放置在目标数据库的控制文件参数指定的路径中。
RMAN> restore controlfile from '/u01/app/oracle/oradata/TEST/control01.ctl' ;
Starting restore at 30-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output filename=+DATA1/ank/control01.ctl
Finished restore at 30-NOV-10
//利用rman copy将数据文件到ASM中
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
//开始copy
RMAN> backup as copy database format '+DATA1'; //路径为磁盘组 ,
Starting backup at 30-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
output filename=+DATA1/test/datafile/system.257.736456963 tag=TAG20101130T192242 recid=2 stamp=736456990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
output filename=+DATA1/test/datafile/sysaux.258.736456999 tag=TAG20101130T192242 recid=3 stamp=736457010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/TEST/example01.dbf
output filename=+DATA1/test/datafile/example.259.736457013 tag=TAG20101130T192242 recid=4 stamp=736457017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
output filename=+DATA1/test/datafile/undotbs1.260.736457021 tag=TAG20101130T192242 recid=5 stamp=736457021
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/TEST/users01.dbf
output filename=+DATA1/test/datafile/users.261.736457021 tag=TAG20101130T192242 recid=6 stamp=736457021
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA1/test/controlfile/backup.262.736457023 tag=TAG20101130T192242 recid=7 stamp=736457024
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 30-NOV-10
channel ORA_DISK_1: finished piece 1 at 30-NOV-10
piece handle=+DATA1/test/backupset/2010_11_30/nnsnf0_tag20101130t192242_0.263.736457027 tag=TAG20101130T192242 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-NOV-10
//利用RMAN的switch database命令切换数据文件的位置到ASM。
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA1/test/datafile/system.257.736456963"
datafile 2 switched to datafile copy "+DATA1/test/datafile/undotbs1.260.736457021"
datafile 3 switched to datafile copy "+DATA1/test/datafile/sysaux.258.736456999"
datafile 4 switched to datafile copy "+DATA1/test/datafile/users.261.736457021"
datafile 5 switched to datafile copy "+DATA1/test/datafile/example.259.736457013"
//控制文件中
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/TEST/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/TEST/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/TEST/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE //以下内容会得到更改,不包括,TEMP表空间的数据文件,因此要重建。
'+DATA1/test/datafile/system.257.736456963',
'+DATA1/test/datafile/undotbs1.260.736457021',
'+DATA1/test/datafile/sysaux.258.736456999',
'+DATA1/test/datafile/users.261.736457021',
'+DATA1/test/datafile/example.259.736457013'
//end
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2010_11_30/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2010_11_30/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/TEST/temp01.dbf'
SIZE 22020096 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
3>移植redo文件
思路:在数据库中新建redo group4,5,6 ,指定路径为ASM
切换使当前redo为新的重组日志组
删除1,2,3
SQL>alter database open
//新建redo组
SQL> alter database add logfile group 4 '+DATA1/test/redo4.log' size 5m;
Database altered.
SQL> alter database add logfile group 5 '+DATA1/test/redo5.log' size 5m;
Database altered.
SQL> alter database add logfile group 6 '+DATA1/test/redo6.log' size 5m;
Database altered.
//切换,将组1,2,3归档,使用新建的redo组
SQL> alter system checkpoint;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 INACTIVE
6 INACTIVE
6 rows selected.
//删除组1,2,3
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
4>移植临时表空间
思路:在数据库中增加一个临时表空间文件。指定路径ASM
删除原来的表空间的数据文件
SQL> select file_name ,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
/u01/app/oracle/oradata/TEST/temp01.dbf TEMP
SQL> alter tablespace temp add tempfile '+DATA1/test/temp01.dbf' size 100m
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/TEST/temp01.dbf';
Tablespace altered.
SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
+DATA1/test/temp01.dbf TEMP
5>创建多个控制文件
方案一:可能有点问题,三个控制文件scn不一致导致数据库无法open
SQL>shutdown immediate;
SQL>startup mount //备份控制文件,因此要到mount状态
SQL> alter database backup controlfile to '+data1/test/controlfile/control01.ctl';
SQL> alter database backup controlfile to '+data1/test/controlfile/control02.ctl';
SQL> alter database backup controlfile to '+data1/test/controlfile/control03.ctl';
SQL> alter system set control_files=
'+DATA1/TEST/CONTROLFILE/control01.ctl',
'+DATA1/TEST/CONTROLFILE/control02.ctl',
'+DATA1/TEST/CONTROLFILE/control03.ctl' scope=spfile;
//之前备份的时候,控制文件路径设置错误,特此纠正
方案二:利用RMAN OK。推荐
SQL>create pfile='/u01/a.ora' from spfile
SQL>vi /u01/a.ora
修改*.control_files='+data1/test/control01.ctl','+data1/test/control02.ctl','+data1/test/control03.ctl'
SQL>shutdown immediate
[oracle@node2 u01]$export ORACLE_SID=TEST
RMAN>startup nomount pfile='/u01/a.ora';
RMAN> restore controlfile from '+data1/test/control01.ctl' ;
RMAN> restore controlfile from '+data1/test/control01.ctl'
Starting restore at 30-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA1/test/control01.ctl
output filename=+DATA1/test/control02.ctl
output filename=+DATA1/test/control03.ctl
Finished restore at 30-NOV-10
RMAN>shutdown immediate;
[oracle@node2 dbs]$ export ORACLE_SID=TEST
[oracle@node2 dbs]$ sqlplus / as sysdba
SQL> startup pfile='/u01/aa.ora'
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/u01/aa.ora' ;
File created.
6>重启库,完毕
SQL>shutdown immediate;
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
---END
出现过的问题:
启动asmcmd
[oracle@node2 bin]$ asmcmd
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (DBD ERROR: OCISessionBegin)
解决该问题是未设置ORACLE_SID=+ASM
[oracle@node2 bin]$ export ORACLE_SID=+ASM
[oracle@node2 bin]$ asmcmd
ASMCMD>
chinaunix网友2011-06-05 02:03:42
大连法律咨询在线 http://www.fabowang.com 大连律师在线咨询 http://www.fabowang.com 大连法律顾问网 http://www.fabowang.com 大连律师咨询 http://www.fabowang.com