Moving Oracle Datafiles to a New ASM Group in Azure Storage

by | May 26, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

 


Create three scripts scripts:


 


SELECT ‘ALTER TABLESPACE ‘||tablespace_name||’ OFFLINE NORMAL;’ from DBA_TABLESPACES;

SELECT ‘ALTER DATABASE RENAME FILE ”’||NAME|| ”’ TO ‘||NAME||’;’ from V$DATAFILES;

SELECT ‘ALTER TABLESPACE ‘||tablespace_name||’ ONLINE;’ from DBA_TABLESPACES;

 


 


Now I could update this with the location path for where I want my new files and then proceed to shutdown the database, copy the files after I run the first script for each tablespace, then update the metadata for the datafile location and then bring it back online.


So the steps would be-



  • Take the tablespace offline.

  • Copy the file to the new location.

  • Update the metadata to point to the new location

  • Put the tablespace online.


 


AS this database isn’t active, I can do this…but with ASM…I have two choices that are the common path for copying datafiles to a new diskgroup:



1. RMAN copy
2. DBMS_FILE_TRANSFER


 


Due to a design challenge in the path naming, etc., I wasn’t able to use DBMS_FILE_TRANSFER and had to use RMAN, but it also meant I had to put the database in archive log mode to choose this second option.


 


Example of a file copy using DBMS_FILE_TRANSFER:


BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘+DATA_DG1/oradata/DB1’,
source_file_name => ,’edata_01.dbf’
destination_directory_object => ‘SDATA’,
destination_file_name => ‘edata_01.dbf’);
END;

 


There’s a lot more to do with either when ASM is involved. With the logical design of the physical datafiles, all changes have to be done via Multiple tools:



  • Present the storage to ASM

  • Create the disk

  • Create the diskgroup


 


Take the inventory as we would above, then I need to put the database in archivelog mode to use RMAN:


 


RMAN> report schema;


List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 1920 SYSTEM YES +DATA_DG1/oradata/DB1/system01.dbf
2 2850 SYSAUX NO +DATA_DG1/oradata/DB1/sysaux01.dbf
3 373760 UNDOTBS1 YES +DATA_DG1/oradata/DB1/undotbs01.dbf
4 250 USERS NO +DATA_DG1/oradata/DB1/users01.dbf
5 6213231 SDATA NO +DATA_DG1/oradata/DB1/sdata_01.dbf
6 68817 WDATA NO +DATA_DG1/oradata/DB1/wdata_01.dbf
7 5120 IDATA NO +DATA_DG1/oradata/DB1/idata_01.dbf
8 1024 EDATA NO +DATA_DG1/oradata/DB1/edata_01.dbf
9 2048 XDB NO +DATA_DG1/oradata/DB1/xdb.dbf


 


List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 373760 TEMP 67108863 +DATA_DG1/oradata/DB1/temp01.dbf


 


What’s required for RMAN with ASM datafile copies for a new diskgroup?



  • Backup the datafile as a copy, format it with the new diskgroup.

  • Offline the datafile

  • Switch the datafile to the copy, which is pointed to the new diskgroup

  • Recover the datafile copy

  • Online the datafile

  • Delete the previous datafile, (now viewed as the copy)


Unless you format your datafile backups with additional configurations, there’s very little dynamic SQL that can assist in getting this automated for you, as your new files will have the dynamic generated file extension for ASM. In our example below, we’ll use the Users tablespace datafile, which is datafile #4:


BACKUP AS COPY 
DATAFILE 4
FORMAT “+SDATA”;

SQL “ALTER DATABASE DATAFILE ”+DATA_DG1/oradata/DB1/users01.dbf” OFFLINE”;
SWITCH DATAFILE “+DATA_DG1/oradata/DB1/users01.dbf” to COPY;
RECOVER DATAFILE “+DATA_DG1/oradata/DB1/users01.259.1073503311”;
SQL “ALTER DATABASE DATAFILE ” +DATA_DG1/oradata/DB1/users01.259.1073503311” ONLINE”;
DELETE DATAFILECOPY “+DATA_DG1/oradata/DB1/users01.dbf”;


Notice that some of the syntax involved quotes and others involve double, single ticks. You need to make sure you use the correct ones for the push of a SQL statement via RMAN vs. the commands to identify the ASM datafile path.


Unlike a Linux/Unix MV command, RMAN ends up making three copies of the file instead of two, which means you need a little bit more space, (also depends on you settings for ASM redundancy, too):
1. Original
2. The copy in the new Diskgroup
3. A third used for the substantiated file to bring online before it drops the older copy.


With the time that it takes to back up and move files, for any tablespaces that didn’t have anything in them and for temp and undo, it was simpler to just create new ones that run through the steps to move a datafile that didn’t have anything in it.


All this reminded me was why I’m a performance DBA and not a backup and recovery DBA…. :)


 


CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 
‘+SDATA’ SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
 
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 
‘+SDATA’ SIZE 100M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE;

ALTER SYSTEM SET UNDO TABLESPACE=UNDOTBS2;

ALTER DATABASE SET DEFAULT TEMPORARY TABLESPACE=TEMP2;

 


In the end I ended up with the following RMAN schema report:


List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 1920 SYSTEM YES +SDATA/DB1/DATAFILE/system01.263.1073698411
2850 SYSAUX NO +SDATA/DB1/DATAFILE/sysaux01.265.1073712889
4 250 USERS NO +SDATA/DB1/DATAFILE/users01.264.1073711908
5 6213231 SDATA NO+SDATA/DB1DATAFILE/sdata_01.262.1073711893
6 68817 WDATA NO +SDATA/DB1/DATAFILE/wdata.259.1073503311
7 5120 IDATA NO +SDATA/DB1/DATAFILE/idata.258.1073502989
8 1024 EDATA NO +SDATA/DB1/DATAFILE/edata.257.1073502623
9 2048 XDB NO +SDATA/DB1/DATAFILE/xdb.256.1073501461
10 1024 UNDOTBS2 YES +SDATA/DB1/DATAFILE/undotbs2.260.1073514289


List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 373760 TEMP 67108863 +DATA_DG1/oradata/DB1/temp01.dbf
2 1024 TEMP2 65535 +SDATA/DB1/TEMPFILE/temp2.262.1073514495


 


Only the one tempfile exists in the old diskgroup and it’s no longer used by anything or anyone.  All temp usage has been switched over to the TEMP2 tablespace that has the new tempfile residing in +SDATA diskgroup.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment