--查看sid SQL> show parameter instance_name ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string tos --创建pfile SQL> create pfile='/tmp/tospfile' from spfile; File created. --创建spfile in asm SQL> create spfile='+data' from pfile='/tmp/tospfile'; File created. --查看spfile name in asm ASMCMD> pwd +data/tos/parameterfile ASMCMD> ls spfile.282.754913039 --编辑pfile内容(如果有该文件,先删除/重命名) [oracle@localhost ~]$ vi $ORACLE_HOME/dbs/inittos.ora #内容为 spfile='+data/tos/parameterfile/spfile.282.754913039' --重命名spfile文件 [oracle@localhost dbs]$ mv spfiletos.ora spfiletos.ora_bak --重启数据库 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1260672 bytes Variable Size 75498368 bytes Database Buffers 83886080 bytes Redo Buffers 7127040 bytes Database mounted. Database opened. --查看spfile SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/tos/parameterfile/spfile .282.754913039
Daily Archives: 2011 年 08 月 01 日
普通库迁移至ASM存储
--查看当前库文件位置 SQL> select name from v$datafile 2 union 3 select member from v$logfile 4 union 5 select name from v$controlfile 6 union 7 select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/oradata/tos/control01.ctl /u01/oradata/tos/control02.ctl /u01/oradata/tos/control03.ctl /u01/oradata/tos/example01.dbf /u01/oradata/tos/redo01.log /u01/oradata/tos/redo02.log /u01/oradata/tos/redo03.log /u01/oradata/tos/sysaux01.dbf /u01/oradata/tos/system01.dbf /u01/oradata/tos/temp01.dbf /u01/oradata/tos/undotbs01.dbf /u01/oradata/tos/users01.dbf /u01/oradata/tos/xff01.dbf /u01/oradata/tos/xff02.dbf 14 rows selected. --备份控制文件 SQL> alter database backup controlfile to '/u01/control.ctl'; Database altered. --修改spfile中的控制文件至asm SQL> alter system set control_files='+DATA' scope=spfile; System altered. --修改db_recovery_file_dest至asm中 SQL> alter system set db_recovery_file_dest='+FLASHBACK'; System altered. --修改归档日志至asm中 SQL> alter system set log_archive_dest_1='location=+DATA'; System altered. --关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --登录rman [oracle@localhost ~]$ rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database (not started) --启动数据库至nomount状态 RMAN> startup nomount; Oracle instance started Total System Global Area 167772160 bytes Fixed Size 1260672 bytes Variable Size 67109760 bytes Database Buffers 92274688 bytes Redo Buffers 7127040 bytes --恢复控制文件 RMAN> restore controlfile from '/u01/control.ctl'; Starting restore at 27-JUN-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK channel ORA_DISK_1: copied control file copy output filename=+DATA/tos/controlfile/current.268.754905785 Finished restore at 27-JUN-11 --打开数据库至mount状态 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 --copy数据文件至asm RMAN> backup as copy database format '+DATA'; Starting backup at 27-JUN-11 Starting implicit crosscheck backup at 27-JUN-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK Finished implicit crosscheck backup at 27-JUN-11 Starting implicit crosscheck copy at 27-JUN-11 using channel ORA_DISK_1 Finished implicit crosscheck copy at 27-JUN-11 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile fno=00001 name=/u01/oradata/tos/system01.dbf output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35 channel ORA_DISK_1: starting datafile copy input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile copy input datafile fno=00005 name=/u01/oradata/tos/example01.dbf output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26 channel ORA_DISK_1: starting datafile copy input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile fno=00004 name=/u01/oradata/tos/users01.dbf output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04 Finished backup at 27-JUN-11 RMAN-06497: WARNING: control file is not current, control file autobackup skipped --恢复数据库 RMAN> recover database; Starting recover at 27-JUN-11 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3 media recovery complete, elapsed time: 00:00:03 Finished recover at 27-JUN-11 --修改数据库中数据文件路径 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833" datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021" datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929" datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035" datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995" datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027" datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031" --打开数据库 RMAN> alter database open resetlogs; database opened --添加日志文件 SQL> alter database add logfile group 4 '+DATA' size 10m; Database altered. SQL> alter database add logfile group 5 '+DATA' size 10m; Database altered. SQL> alter database add logfile group 6 '+DATA' size 10m; Database altered. --添加临时文件 SQL> alter tablespace temp add tempfile '+DATA' size 30m; Tablespace altered. --删除原临时文件 SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf'; Tablespace altered. --切换日志 SQL> alter system switch logfile; --多次 System altered. --数据文件修改写入磁盘 SQL> alter system checkpoint; System altered. --检查联机日志状态 SQL> select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 INACTIVE 4 INACTIVE 5 INACTIVE 6 CURRENT 6 rows selected. --删除原联机日志 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. --查看修改后状态 SQL> select name from v$datafile 2 union 3 select member from v$logfile 4 union 5 select name from v$controlfile 6 union 7 select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/tos/controlfile/current.268.754905785 +DATA/tos/datafile/example.272.754905995 +DATA/tos/datafile/sysaux.271.754905929 +DATA/tos/datafile/system.270.754905833 +DATA/tos/datafile/undotbs1.273.754906021 +DATA/tos/datafile/users.276.754906035 +DATA/tos/datafile/xff.274.754906027 +DATA/tos/datafile/xff.275.754906031 +DATA/tos/onlinelog/group_4.277.754906309 +DATA/tos/onlinelog/group_5.278.754906319 +DATA/tos/onlinelog/group_6.279.754906321 +DATA/tos/tempfile/temp.280.754906369
asm数据文件迁移(asm–>os)
--查看当前情况 SQL> select count(*) from hr.a; COUNT(*) ---------- 1580 SQL> select name from v$DATAFILE; NAME ----------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf +DG2/tasm/datafile/xff.256.754902279 6 rows selected. --创建目录 SQL> create directory asmsrc as '+DG2/TASM/datafile'; Directory created. SQL> create directory osdesc as '/u01/oradata'; Directory created. --表空间离线 SQL> alter tablespace xff offline; Tablespace altered. --数据文件迁移 SQL> begin 2 dbms_file_transfer.copy_file('ASMSRC', 3 'xff.256.754902279', 4 'OSDESC', 5 'xff_new_2.dbf'); 6 END; 7 / PL/SQL procedure successfully completed. --修改数据库中datafile路径 SQL> alter database rename file 2 '+DG2/tasm/datafile/xff.256.754902279' 3 to '/u01/oradata/xff_new_2.dbf'; Database altered. --表空间在线 SQL> alter tablespace xff online; Tablespace altered. --测试迁移结果 SQL> select name from v$DATAFILE; NAME ------------------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf /u01/oradata/xff_new_2.dbf 6 rows selected. SQL> select count(*) from hr.a; COUNT(*) ---------- 1580 --删除asm中文件 ASMCMD> rm XFF.256.754902279 ORA-15032: not all alterations performed ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) --不能直接删除,关闭数据库后可以删除(应该是bug)
asm数据文件迁移(asm–>asm)
rman迁移操作
[oracle@localhost oradata]$ rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 05:50:03 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TASM (DBID=3032096031) RMAN> sql ‘alter tablespace xff offline’; using target database control file instead of recovery catalog sql statement: alter tablespace xff offline RMAN> backup device type disk as copy datafile 6 format '+DG2'; Starting backup at 27-JUN-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=+DATA/tasm/xff01.dbf output filename=+DG2/tasm/datafile/xff.256.754899605 tag=TAG20110627T062003 recid=4 stamp=754899608 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 27-JUN-11 RMAN> switch tablepspace xff to copy; datafile 6 switched to datafile copy "+DG2/tasm/datafile/xff.256.754899605" RMAN> sql 'alter tablespace xff online'; sql statement: alter tablespace xff online
sql验证操作
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf +DG2/tasm/datafile/xff.256.754899605 6 rows selected. SQL> select count(*) from hr.a; COUNT(*) ---------- 1580
asm数据文件迁移(os–>asm)
--添加测试表空间 SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m; Tablespace created. --查看数据文件位置 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf /u01/oradata/xifenfei.dbf 6 rows selected. --创建测试表 SQL> create table hr.a tablespace xff 2 as 3 select * from dba_tables; Table created. SQL> select count(*) from hr.a; COUNT(*) ---------- 1580 --转移数据文件位置 [oracle@localhost oradata]$ rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: TASM (DBID=3032096031) RMAN> sql 'alter tablespace xff offline'; using target database control file instead of recovery catalog sql statement: alter tablespace xff offline RMAN> backup as copy tablespace xff format '+DATA'; Starting backup at 27-JUN-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=132 devtype=DISK channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=/u01/oradata/xifenfei.dbf output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 27-JUN-11 RMAN> switch tablespace xff to copy; datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121" RMAN> sql 'alter tablespace xff online'; sql statement: alter tablespace xff online --查看转移后的数据文件位置 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf +DATA/tasm/datafile/xff.269.754893121 6 rows selected. --测试其中数据是否存在 SQL> select count(*) from hr.a; COUNT(*) ---------- 1580 --创建asm中文件别名 ASMCMD> mkalias +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf --文件重命名 SQL> alter tablespace xff offline; Tablespace altered. SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf'; Database altered. SQL> alter tablespace xff online; Tablespace altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/tasm/system01.dbf +DATA/tasm/undotbs01.dbf +DATA/tasm/sysaux01.dbf +DATA/tasm/users01.dbf +DATA/tasm/example01.dbf +DATA/tasm/xff01.dbf 6 rows selected. --手工删除原来数据 [oracle@localhost oradata]$ rm xifenfei.dbf
说明:可以在rman移植数据文件位置的时候,同时处理好别名,然后对表空间重命名,实现只需要表空间离线一次