此次測試將資料庫遷移到ASM中，本例已將ASM instance和DB instance都建立好。
測試環境：vmware server1.05+rhel4 u4+oracle 10.2.0.1

參考

ASM instance的情況如下：

SQL> select group_number,state,type,total_mb,free_mb From v$asm_diskgroup;

GROUP_NUMBER STATE TYPE TOTAL_MB FREE_MB
------------ ----------- ------ ---------- ----------
1 MOUNTED NORMAL 3996 3890
2 MOUNTED EXTERN 1998 1946

SQL> select group_number,disk_number,mount_status,header_status,state,total_mb,free_mb,name,failgroup from v$asm_disk

order by PATH;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB NAME FAILGROUP
------------ ----------- ------- ------------ -------- ---------- ---------- ------- ------------------------------
1 0 CACHED MEMBER NORMAL 999 969 VOL1 FGROUP1
1 1 CACHED MEMBER NORMAL 999 976 VOL2 FGROUP1
1 2 CACHED MEMBER NORMAL 999 971 VOL3 FGROUP2
1 3 CACHED MEMBER NORMAL 999 974 VOL4 FGROUP2
2 0 CACHED MEMBER NORMAL 999 972 VOL5 VOL5
2 1 CACHED MEMBER NORMAL 999 974 VOL6 VOL6

6 rows selected.

SQL> select * from v$asm_alias;

no rows selected

1.確定DBID

可以透過rman來檢視dbid

[oracle@rhel131 ~]$ echo $ORACLE_SID
orcl

[oracle@rhel131 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 25 13:25:53 2009

connected to target database: ORCL (DBID=1207536709)

RMAN> exit

Recovery Manager complete.

2.確定資料檔案的位置

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl

3.建立non-ASM的RMAN指令碼

SQL> set serveroutput on;
SQL> declare
2 cursor df is select file#, name from v$datafile;
3 begin
4 dbms_output.put_line('run');
5 dbms_output.put_line('{');
6 for dfrec in df loop
7 dbms_output.put_line('set newname for datafile ' ||
8 dfrec.file# || ' to ''' || dfrec.name ||''' ;');
9 end loop;
10 dbms_output.put_line('restore database;');
11 dbms_output.put_line('switch all;');
12 dbms_output.put_line('}');
13 end;
14 /
run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf' ;
set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/sysaux01.dbf' ;
set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users01.dbf' ;
set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/example01.dbf' ;
restore database;
switch all;
}

PL/SQL procedure successfully completed.

4.關閉block tracking。
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled

5.關閉資料庫，修改pfile

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

修改pfile如下：
[oracle@rhel131 pfile]$ cat init.ora
orcl.__db_cache_size=188743680
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=88080384
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracl

e/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=286261248
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

*.control_files='+dgroup1'
*.db_create_file_dest='+dgroup1'
*.db_recovery_file_dest='+dgroup2'

6.用修改後的pfile啟動到nomout,恢復controlfile

[oracle@rhel131 pfile]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 25 13:46:03 2009

connected to target database (not started)

RMAN> startup nomount pfile='/u01/app/oracle/admin/orcl/pfile/init.ora';

Oracle instance started

Total System Global Area 289406976 bytes

Fixed Size 1219016 bytes
Variable Size 96470584 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes

RMAN> RESTORE CONTROLFILE FROM '/u01/app/oracle/oradata/orcl/control01.ctl';

Starting restore at 25-FEB-09
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=+DGROUP1/orcl/controlfile/backup.256.679758573
Finished restore at 25-FEB-09

7.備份資料檔案到ASM

RMAN> ALTER DATABASE MOUNT;

using target database control file instead of recovery catalog
database mounted

RMAN> backup as copy database format '+dgroup1';

Starting backup at 25-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output filename=+DGROUP1/orcl/datafile/system.257.679758645 tag=TAG20090225T1350 44 recid=2

stamp=679758704
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output filename=+DGROUP1/orcl/datafile/sysaux.258.679758711 tag=TAG20090225T135044 recid=3 stamp=679758730
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=+DGROUP1/orcl/datafile/example.259.679758737 tag=TAG20090225T135044 recid=4 stamp=679758745
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output filename=+DGROUP1/orcl/datafile/undotbs1.260.679758751 tag=TAG20090225T135044 recid=5 stamp=679758754
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/app/oracle/oradata/orcl/users01.dbf
output filename=+DGROUP1/orcl/datafile/users.261.679758755 tag=TAG20090225T135044 recid=6 stamp=679758755
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=+DGROUP1/orcl/controlfile/backup.262.679758755 tag=TAG20090225T135044 recid=7 stamp=679758757
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 25-FEB-09

8.切換資料檔案為ASM，並開啟資料庫

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DGROUP1/orcl/datafile/system.257.679758645"
datafile 2 switched to datafile copy "+DGROUP1/orcl/datafile/undotbs1.260.679758751"
datafile 3 switched to datafile copy "+DGROUP1/orcl/datafile/sysaux.258.679758711"
datafile 4 switched to datafile copy "+DGROUP1/orcl/datafile/users.261.679758755"
datafile 5 switched to datafile copy "+DGROUP1/orcl/datafile/example.259.679758737"

RMAN> ALTER DATABASE OPEN;

database opened

這時看看ASM instance自動建立好的alias.

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS where alias_directory='Y';

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
CONTROLFILE 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS where alias_directory='N';

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
backup.256.679758573 1 256 N Y
Backup.262.679758755 1 262 N Y
SYSTEM.257.679758645 1 257 N Y
SYSAUX.258.679758711 1 258 N Y
EXAMPLE.259.679758737 1 259 N Y
UNDOTBS1.260.679758751 1 260 N Y
USERS.261.679758755 1 261 N Y

7 rows selected.

9.建立online redolog file

[oracle@rhel131 pfile]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 25 13:57:01 2009

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

SQL> declare
2 cursor orlc is select lf.member, l.bytes
3 from v$log l, v$logfile lf
4 where l.group# = lf.group#
5 and lf.type = 'ONLINE'
6 order by l.thread#, l.sequence#;
7 type numTab_t is table of number index by binary_integer;
8 type charTab_t is table of varchar2(1024) index by binary_integer;
9 byteslist numTab_t;
10 namelist charTab_t;
11 procedure migrateorlfile(name IN varchar2, bytes IN number) is
12 retry number;
13 stmt varchar2(1024);
14 als varchar2(1024) := 'alter system switch logfile';
15 begin
16 select count(*) into retry from v$logfile;
17 stmt := 'alter database add logfile size ' || bytes;
18 execute immediate stmt;
19 stmt := 'alter database drop logfile ''' || name || '''';
20 for i in 1..retry loop
21 begin
22 execute immediate stmt;
23 exit;
24 exception
25 when others then
26 if i > retry then
27 raise;
28 end if;
29 execute immediate als;
30 end;
31 end loop;
32 end;
33 begin
34 open orlc;
35 fetch orlc bulk collect into namelist, byteslist;
36 close orlc;
37 for i in 1..namelist.count loop
38 migrateorlfile(namelist(i), byteslist(i));
39 end loop;
40 end;
41 /

PL/SQL procedure successfully completed.

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS where alias_directory='Y';

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
ORCL 1 4294967295 Y Y
CONTROLFILE 1 4294967295 Y Y
DATAFILE 1 4294967295 Y Y
ONLINELOG 1 4294967295 Y Y
ORCL 2 4294967295 Y Y
ONLINELOG 2 4294967295 Y Y

6 rows selected.

SQL> select name,group_number,file_number,alias_directory,system_created from V$ASM_ALIAS where alias_directory='N';

NAME GROUP_NUMBER FILE_NUMBER A S
------------------------------------------------ ------------ ----------- - -
backup.256.679758573 1 256 N Y
Backup.262.679758755 1 262 N Y
SYSTEM.257.679758645 1 257 N Y
SYSAUX.258.679758711 1 258 N Y
EXAMPLE.259.679758737 1 259 N Y
UNDOTBS1.260.679758751 1 260 N Y
USERS.261.679758755 1 261 N Y
group_4.263.679759147 1 263 N Y
group_2.264.679759159 1 264 N Y
group_3.265.679759173 1 265 N Y
group_4.256.679759155 2 256 N Y
group_2.257.679759167 2 257 N Y
group_3.258.679759181 2 258 N Y

10.最好可能還需要建立臨時表空間和開啟block tracking.

SQL> CREATE TEMPORARY TABLESPACE temp01 TEMPFILE
2 '+dgroup1' SIZE 100M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M;

Tablespace created.

SQL> drop tablespace temp INCLUDING CONTENTS ;

Tablespace dropped.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

11.重建SPFILE

修改一下pfile的control_files的值為恢復出來的controlfile後重建SPFILE

*.control_files='+dgroup1/ORCL/CONTROLFILE/backup.256.679758573'

SQL> create spfile from pfile='/u01/app/oracle/admin/orcl/pfile/init.ora';

File created.

12.最後最好將磁碟上的檔案刪除，騰出更多的空間。

由於rman裡已儲存了在磁碟上的datafile的地址，所以只需要簡單地在rman裡就可以將datafile刪除。

RMAN> delete copy of database;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
8 1 A 25-FEB-09 481284 25-FEB-09 /u01/app/oracle/oradata/orcl/system01.dbf
9 2 A 25-FEB-09 481284 25-FEB-09 /u01/app/oracle/oradata/orcl/undotbs01.dbf
10 3 A 25-FEB-09 481284 25-FEB-09 /u01/app/oracle/oradata/orcl/sysaux01.dbf
11 4 A 25-FEB-09 481284 25-FEB-09 /u01/app/oracle/oradata/orcl/users01.dbf
12 5 A 25-FEB-09 481284 25-FEB-09 /u01/app/oracle/oradata/orcl/example01.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/system01.dbf recid=8 stamp=679758796
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf recid=9 stamp=679758796
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf recid=10 stamp=679758796
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/users01.dbf recid=11 stamp=679758797
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf recid=12 stamp=679758797
Deleted 5 objects

RMAN> host 'rm /u01/app/oracle/oradata/orcl/control*';
host command complete

RMAN> host 'rm /u01/app/oracle/oradata/orcl/redo*';

host command complete

RMAN> host 'rm /u01/app/oracle/oradata/orcl/temp01.dbf';

host command complete

至此已完成將資料庫遷到到ASM中。

遷移已存在的資料庫到ASM中

相關文章