Move ASM database files from one diskgroup to another

In the past week, I have been involved in a migration of a standalone database to RAC with ASM through datab pump export and import.  After the migration was done, one of the ASM diskgroup is more than 98% percent used, which triggerred alerts from BMC Patrol, which is a standard monitoring tool in my working environment. Searching My Oracle Support website, I found the following note:

How to move ASM database files from one diskgroup to another ? (Doc ID 330103.1)


The note is pretty straight forword, however, there was a one suprise when I tried to offline a datafile :

ORA-01145: offline immediate disallowed unless media recovery enabled

That was because I put the db in noarchivelog mode in order to speed up the data pump import jobs. After I configured the archivelog mode, thing went fine.

Here are the steps I followed for future reference

1) Identify the data file to be moved

+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf            NON_TTQ_CLB_MSG
2)  Identify the diskgroup on to which the file has to be moved.

+DATA_09/roeprdsc/datafile

3) Take the file offline.

 ALTER DATABASE DATAFILE '+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' OFFLINE;

4. Copy the data file

I used DBMS_FILE_TRANSFER package. We can also use RMAN, detail see the note.
     
          
create or replace directory orcl1 as '+DATA_01/roeprdsc/datafile';
        
create or replace directory orcl2 as '+DATA_09/roeprdsc/datafile';
      
 Note, if need to create a directory in a diskgroup, syntax looks like

 ASM> Alter disgroup asmdsk2 add directory  '+asmdsk2/test';        
       
BEGIN
 DBMS_FILE_TRANSFER.COPY_FILE(
 source_directory_object => 'ORCL1',
 source_file_name => 'non_ttq_clb_msg_01.dbf',
 destination_directory_object => 'ORCL2',
 destination_file_name => 'non_ttq_clb_msg_01.dbf');
END;                          
/


5) Rename the file to point to new location.

ALTER DATABASE RENAME FILE '+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' TO
 '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' ;

6) Recover the file.

  RECOVER DATAFILE '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf'

7) Bring the file online.

 ALTER DATABASE DATAFILE '+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf' online;


8) Verify the new file location.
         
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where file_name like '%clb_msg%';

FILE_NAME
--------------------------------------------------------------------------------
...
+DATA_09/roeprdsc/datafile/non_ttq_clb_msg_01.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_02.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_03.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_04.dbf
+DATA_01/roeprdsc/datafile/non_ttq_clb_msg_05.dbf
..



9) Delete the file from its original location either per SQLPLUS or per ASMCMD:

Syntax examples:

SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE users.256.565313879;

or:   ASMCMD> rm -rf 

Note:
Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed. ( To not to have the alert come again, I have to do this)

Reference: Clean up SharePlex Queues

We use SharePlex replication as our DR solution for couple of applicatins. Last Saturday night, we did a DR test for one of the very important applications, but we could not make replication working from DR site to other target. As we ran out of time in the window, we just brought up application back to the production site without fixing issues. Today I involved vendor support to test DR configuration (with a dummy configuration) again to make sure that if real DR situation happens replication will work. I have learned that the key is that we should clean up orphan or corrupted queues before activating a configuration in DR. ( note: when we issue deactivate config , suppose all associated queues with this particular conifg will be gone, if not, those left queues need to be cleaned up).


Below are the steps to clean up SharePlex queues for future reference:


1. shutdown or shutdown force at source and target
2. qview -i
3. qview> qsetup
4. qview> qstatus
5. qview> deleteq p   -- for post
   qview> deleteq x   -- for export
6. On target:  truncate splex.shareplex_trans;