1.PURPOSE AND SCOPE
In some cases we accidentally forget and create datafiles on local disk while using.In this article i am going to explain how to move datafile local disk to ASM.
2.PRACTICE
2.1. I use 2 node RAC system for this practice. And create 2 tablespace on node 1 local disk.
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'AHBS%'; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts AHBS_DATA_TS /u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf AHBS_INDEX_TS 2 rows selected. |
2.2. We need to change the status of these tablespace to offline.
|
1 2 3 4 |
SQL> alter tablespace AHBS_INDEX_TS offline; Tablespace altered. SQL> alter tablespace AHBS_DATA_TS offline; Tablespace altered. |
2.3. For copying tablespaces to ASM i used rman copy command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[oracle@sba5db07 ~]$ rman Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 30 18:10:00 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target; connected to target database: MERGE (DBID=3048845705) RMAN> copy datafile '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf'; Starting backup at 30-JUN-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=201 instance=MERGE1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00017 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts output file name=+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf tag=TAG20120630T182127 RECID=4 STAMP=787342893 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 30-JUN-12 RMAN> copy datafile '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf' to '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ; Starting backup at 30-JUN-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00018 name=/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf output file name=+DATA_SBA5/merge/datafile/ahbs_index_ts01.dbf tag=TAG20120630T182136 RECID=5 STAMP=787342896 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 30-JUN-12 |
2.4. Okey now we created datafiles on ASM but database doesnt aware of these tablespaces.So we need to rename datafiles local to ASM.
|
1 2 3 4 5 6 7 |
SQL> alter database rename file '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/ahbs_data_ts' to '+DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf' ; Database altered. SQL> alter database rename file '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/AHBS_INDEX_TS01.dbf' to '+DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf' ; Database altered. |
2.5. Lets change tablespaces status to online.
|
1 2 3 4 5 6 7 |
SQL> alter tablespace AHBS_INDEX_TS online; Tablespace altered. SQL> alter tablespace AHBS_DATA_TS online; Tablespace altered. |
2.6. Lets check path of tablespaces.
|
1 2 3 4 5 6 7 8 |
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name like 'AHBS%'; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- +DATA_SBA5/merge/datafile/ahbs_data_ts_01.dbf AHBS_DATA_TS +DATA_SBA5/merge/datafile/AHBS_INDEX_TS01.dbf AHBS_INDEX_TS |
Buğra hocam eline sağlık bu arlaar ASM üzerinde testler yapıyordum senin paylaşmış olduğun bu senaryoyu denemiş oldum.
teşekkürler
Rica ederim işine yaradığına sevindim.