数据库迁移-从文件系统到ASM
1.2.2. 数据库迁移-从文件系统到ASM
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/10g/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
--查看参数文件和控制文件位置
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
spfile string /u01/oracle/10g/product/10.2.0
/db_1/dbs/spfilegt10g.ora
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
control_files string /u01/oracle/10g/oradata/gt10g/
control1.ctl
--参数文件和控制文件备份
SQL> create pfile='/tmp/pfile1' from spfile;
File created.
SQL> alter database backup controlfile to'/tmp/controfile1';
Database altered.
--查看当前数据文件位置
SQL> select name from v$datafile
2 union all
3 select name from v$controlfile
4 union all
5 select name from v$tempfile
6 union all
7 select member from v$logfile;
NAME
--------------------------------------------------
/u01/oracle/10g/oradata/gt10g/system01.dbf
/u01/oracle/10g/oradata/gt10g/undotbs1.dbf
/u01/oracle/10g/oradata/gt10g/sysaux01.dbf
/u01/oracle/10g/oradata/gt10g/users01.dbf
/u01/oracle/10g/oradata/gt10g/gtlions01.dbf
/u01/oracle/10g/oradata/gt10g/gtlions02.dbf
/u01/oracle/10g/oradata/gt10g/control1.ctl
/u01/oracle/10g/oradata/gt10g/temp01.dbf
/u01/oracle/10g/oradata/gt10g/gtlionstemp01.dbf
/u01/oracle/10g/oradata/gt10g/redo1.log
/u01/oracle/10g/oradata/gt10g/redo2.log
NAME
--------------------------------------------------
/u01/oracle/10g/oradata/gt10g/redo3.log
12 rows selected.
--查看OMF相关参数
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_create_file_dest string
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_recovery_file_dest_size big integer 0
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
log_archive_dest_1 string location=/u01/oracle/10g/arch
log_archive_dest_10 string
--修改控制文件位置到ASM
SQL> alter system set control_files='+data01'scope=spfile;
System altered.
--修改OMF相关参数到ASM
SQL> alter system setdb_create_file_dest='+data01' scope=both;
System altered.
SQL> alter system setdb_recovery_file_dest_size=1024m scope=both;
System altered.
SQL> alter system setdb_recovery_file_dest='+fra' scope=both;
System altered.
SQL> alter system setlog_archive_dest_1='location=+fra' scope=both;
System altered.
SQL> exit
Disconnected from Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
--切换到RMAN操作
[oracle@gtser1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Productionon Mon Feb 25 15:35:29 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: GT10G(DBID=2268277830)
--关闭数据库
RMAN> shutdown immediate;
using target database control file instead ofrecovery catalog
database closed
database dismounted
Oracle instance shut down
--启动到nomount状态
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 520093696 bytes
Fixed Size 2097496 bytes
Variable Size 138415784 bytes
Database Buffers 373293056 bytes
Redo Buffers 6287360 bytes
--还原控制文件
RMAN> alter database mount;
RMAN> restore controlfile from '/tmp/controfile1';
Starting restore at 25-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
outputfilename=+DATA01/gt10g/controlfile/current.270.808328193
Finished restore at 25-FEB-13
--备份数据文件副本到ASM磁盘组DATA01
RMAN> backup as copy database format'+data01';
Starting backup at 25-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001name=/u01/oracle/10g/oradata/gt10g/system01.dbf
output filename=+DATA01/gt10g/datafile/system.271.808328265tag=TAG20130225T153744 recid=125 stamp=808328266
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003name=/u01/oracle/10g/oradata/gt10g/sysaux01.dbf
outputfilename=+DATA01/gt10g/datafile/sysaux.272.808328281 tag=TAG20130225T153744recid=126 stamp=808328316
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002name=/u01/oracle/10g/oradata/gt10g/undotbs1.dbf
outputfilename=+DATA01/gt10g/datafile/undotbs1.273.808328327 tag=TAG20130225T153744recid=127 stamp=808328326
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005name=/u01/oracle/10g/oradata/gt10g/gtlions01.dbf
outputfilename=+DATA01/gt10g/datafile/gtlions.281.808328327 tag=TAG20130225T153744recid=128 stamp=808328329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006name=/u01/oracle/10g/oradata/gt10g/gtlions02.dbf
outputfilename=+DATA01/gt10g/datafile/gtlions.256.808328331 tag=TAG20130225T153744recid=129 stamp=808328330
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004name=/u01/oracle/10g/oradata/gt10g/users01.dbf
outputfilename=+DATA01/gt10g/datafile/users.269.808328331 tag=TAG20130225T153744 recid=130stamp=808328333
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:04
Finished backup at 25-FEB-13
RMAN-06497: WARNING: control file is not current,control file autobackup skipped
--切换数据文件到ASM
RMAN> switch database to copy;
datafile 1 switched to datafile copy"+DATA01/gt10g/datafile/system.271.808328265"
datafile 2 switched to datafile copy"+DATA01/gt10g/datafile/undotbs1.273.808328327"
datafile 3 switched to datafile copy"+DATA01/gt10g/datafile/sysaux.272.808328281"
datafile 4 switched to datafile copy"+DATA01/gt10g/datafile/users.269.808328331"
datafile 5 switched to datafile copy"+DATA01/gt10g/datafile/gtlions.281.808328327"
datafile 6 switched to datafile copy"+DATA01/gt10g/datafile/gtlions.256.808328331"
--恢复数据库
RMAN> recover database;
Starting recover at 25-FEB-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already ondisk as file /u01/oracle/10g/oradata/gt10g/redo1.log
archive log filename=/u01/oracle/10g/oradata/gt10g/redo1.logthread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-FEB-13
--打开数据库
RMAN> alter database open resetlogs;
database opened
--切换SQL*PLUS操作
[oracle@gtser1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on MonFeb 25 15:41:10 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
--迁移REDO到ASM
SQL> alter database add logfile group 4'+data01' size 30m;
Database altered.
SQL> alter database add logfile group 5'+data01' size 30m;
Database altered.
SQL> alter database add logfile group 6'+data01' size 30m;
Database altered.
--删除旧REDO
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instancegt10g (thread 1) - cannot drop
ORA-00312: online log 1 thread 1:'/u01/oracle/10g/oradata/gt10g/redo1.log'
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
--迁移TEMP到ASM，删除旧TEMP文件
SQL> alter tablespace temp add tempfile'+DATA01' size 100m;
Tablespace altered.
SQL> alter tablespace temp drop tempfile'/u01/oracle/10g/oradata/gt10g/temp01.dbf';
Tablespace altered.
SQL> alter tablespace gtlionstemp add tempfile'+DATA01' size 100m;
Tablespace altered.
SQL> alter tablespace gtlionstemp droptempfile '/u01/oracle/10g/oradata/gt10g/gtlionstemp01.dbf';
Tablespace altered.
--迁移spfile到ASM
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
spfile string /u01/oracle/10g/product/10.2.0
/db_1/dbs/spfilegt10g.ora
SQL> create pfile='/tmp/pfile2' from spfile;
File created.
SQL> create spfile='+data01' frompfile='/tmp/pfile2';
File created.
SQL> exit
Disconnected from Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
[oracle@gtser1 ~]$ vi/u01/oracle/10g/product/10.2.0/db_1/dbs/initgt10g.ora
[oracle@gtser1 ~]$ less /u01/oracle/10g/product/10.2.0/db_1/dbs/initgt10g.ora
spfile='+data01/gt10g/parameterfile/spfile.257.808328691'
[oracle@gtser1 ~]$ mv/u01/oracle/10g/product/10.2.0/db_1/dbs/spfilegt10g.ora/u01/oracle/10g/product/10.2.0/db_1/dbs/spfilegt10g.ora.bak
[oracle@gtser1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on MonFeb 25 15:47:39 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and RealApplication Testing options
SQL> startup force;
ORACLE instance started.
Total System Global Area 520093696 bytes
Fixed Size 2097496 bytes
Variable Size 138415784 bytes
Database Buffers 373293056 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
spfile string +DATA01/gt10g/parameterfile/sp
file.257.808328691
--检查迁移结果
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ -----------------------------------------
control_files string +DATA01/gt10g/controlfile/curr
ent.270.808328193
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 8
Next log sequence to archive 11
Current log sequence 11
SQL> Select Name
2 From V$datafile
3 Union All
4 Select Name
5 From V$controlfile
6 Union All
7 Select Name
8 From V$tempfile
9 Union All
10 Select Member From V$logfile;
NAME
--------------------------------------------
+DATA01/gt10g/datafile/system.271.808328265
+DATA01/gt10g/datafile/undotbs1.273.808328327
+DATA01/gt10g/datafile/sysaux.272.808328281
+DATA01/gt10g/datafile/users.269.808328331
+DATA01/gt10g/datafile/gtlions.281.808328327
+DATA01/gt10g/datafile/gtlions.256.808328331
+DATA01/gt10g/controlfile/current.270.808328193
+DATA01/gt10g/tempfile/temp.262.808328581
+DATA01/gt10g/tempfile/gtlionstemp.263.808328591
+DATA01/gt10g/onlinelog/group_4.259.808328481
+DATA01/gt10g/onlinelog/group_5.260.808328487
NAME
--------------------------------------------
+DATA01/gt10g/onlinelog/group_6.261.808328491
12 rows selected.
--检查迁移结果
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------------------------------
1 440 SYSTEM *** +DATA01/gt10g/datafile/system.271.808328265
2 25 UNDOTBS1 *** +DATA01/gt10g/datafile/undotbs1.273.808328327
3 250 SYSAUX *** +DATA01/gt10g/datafile/sysaux.272.808328281
4 5 USERS *** +DATA01/gt10g/datafile/users.269.808328331
5 10 GTLIONS *** +DATA01/gt10g/datafile/gtlions.281.808328327
6 10 GTLIONS *** +DATA01/gt10g/datafile/gtlions.256.808328331
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- -------------------------------
1 100 GTLIONSTEMP 100 +DATA01/gt10g/tempfile/gtlionstemp.263.808328591
2 100 TEMP 100 +DATA01/gt10g/tempfile/temp.262.808328581
-The End-