資料庫遷移到ASM

lsq_008發表於2012-08-31
資料庫ASM
本文主要描述將資料庫從檔案系統遷移到ASM的過程。

1. 將資料庫以copy的方式備份到asm磁碟組DG1上：

[oracle@rhel ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Aug 30 07:37:36 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2090806509)

RMAN>  BACKUP AS COPY INCREMENTAL LEVEL 0  DATABASE  FORMAT '+DG1'  TAG 'ORA_ASM_MIGRATION';

Starting backup at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/db/oracle/oradata/test/system01.dbf
output filename=+DG1/test/datafile/system.258.792661073 tag=ORA_ASM_MIGRATION recid=2 stamp=792661098
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/home/db/oracle/oradata/test/sysaux01.dbf
output filename=+DG1/test/datafile/sysaux.257.792661105 tag=ORA_ASM_MIGRATION recid=3 stamp=792661142
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/home/db/oracle/oradata/test/undotbs01.dbf
output filename=+DG1/test/datafile/undotbs1.256.792661151 tag=ORA_ASM_MIGRATION recid=4 stamp=792661167
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DG1/test/controlfile/backup.264.792661175 tag=ORA_ASM_MIGRATION recid=5 stamp=792661177
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 30-AUG-12

RMAN> list copy ;

specification does not match any archive log in the recovery catalog

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
2       1    A 30-AUG-12       151785     30-AUG-12       +DG1/test/datafile/system.258.792661073
4       2    A 30-AUG-12       151813     30-AUG-12       +DG1/test/datafile/undotbs1.256.792661151
3       3    A 30-AUG-12       151798     30-AUG-12       +DG1/test/datafile/sysaux.257.792661105

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
5       A 30-AUG-12       151822     30-AUG-12       +DG1/test/controlfile/backup.264.792661175
1       A 30-AUG-12       151424     30-AUG-12       +DG1/test/controlfile/backup.262.792660997



[oracle@rhel ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 30 07:43:39 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create spfile='+DG1/spfile' from pfile='/home/db/oracle/product/10.2.0/dbs/inittest.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

2. 啟動資料庫到mount狀態，建立controlfile的備份：

[oracle@rhel ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Aug 30 07:53:10 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     167772160 bytes

Fixed Size                     1272600 bytes
Variable Size                109053160 bytes
Database Buffers              54525952 bytes
Redo Buffers                   2920448 bytes

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/home/oracle/control.bak'; 

Starting backup at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=/home/oracle/control.bak tag=TAG20120830T075403 recid=6 stamp=792662044
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-AUG-12

3. 修改控制檔案位置到asm磁碟組DG1上

SQL> alter system set control_files='+DG1/control01.ctl','+DG1/CONTROL02.CTL' scope=spfile sid='*';

System altered.

4. 恢復控制檔案到ASM磁碟組DG1上

[oracle@rhel ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Aug 30 08:02:15 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: test (not mounted)

RMAN> restore controlfile from '/home/oracle/control.bak';

Starting restore at 30-AUG-12
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=+DG1/control01.ctl
output filename=+DG1/control02.ctl
Finished restore at 30-AUG-12

5. 切換資料檔案位置到asm磁碟組DG1上，並做recover：

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DG1/test/datafile/system.258.792661073"
datafile 2 switched to datafile copy "+DG1/test/datafile/undotbs1.256.792661151"
datafile 3 switched to datafile copy "+DG1/test/datafile/sysaux.257.792661105"

RMAN> recover database;

Starting recover at 30-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK

starting media recovery

archive log thread 1 sequence 4 is already on disk as file /home/db/oracle/oradata/test/redo01.log
archive log filename=/home/db/oracle/oradata/test/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:03
Finished recover at 30-AUG-12

6. 切換臨時檔案位置到asm磁碟組DG1上：

RMAN> run {
2>             set newname for tempfile 1 to '+DG1';
3>             switch tempfile all;
4>  }

executing command: SET NEWNAME

renamed temporary file 1 to +DG1 in control file

7. 以resetlog的方式開啟資料庫，確認資料檔案已經遷移到asm上：

oracle@rhel ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Aug 30 08:12:02 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter database open RESETLOGS;

Database altered.

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                             FILE_ID BYTES/1024/1024
------------------------------ -------------------------------------------------- ---------- ---------------
SYSTEM                         +DG1/test/datafile/system.258.792661073                     1             325
UNDOTBS1                       +DG1/test/datafile/undotbs1.256.792661151                   2             200
SYSAUX                         +DG1/test/datafile/sysaux.257.792661105                     3             325

SQL> select tablespace_name,file_name,file_id,bytes/1024/1024 from dba_temp_files;

TABLESPACE_NAME                FILE_NAME                                             FILE_ID BYTES/1024/1024
------------------------------ -------------------------------------------------- ---------- ---------------
TEMPTS1                        +DG1/test/tempfile/tempts1.259.792663177                    1              20

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-742345/，如需轉載，請註明出處，否則將追究法律責任。

相關文章