RAC local data file migration to ASM method-non-archive Mode

RAC local data file migration to ASM method-non-archive Mode

Reference: How to migrate RAC local data files to ASM

System Environment: rhel6.2 _ x64 + Oracle RAC11g
 
Procedure:
1. Non-archive Mode
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination/u01/oracle/app/oracle/11.2.0/db/dbs/arch
Oldest online log sequence 303086
Current log sequence 303087
SQL>

2. Non-system tablespace (currently in the production environment, node RAC1 can recognize local files)
SQL & gt; set line 180
SQL> col file_name for a60
SQL> col tablespace_name for a15
SQL> select file_name, file_id, online_status, tablespace_name from dba_data_files;
FILE_NAME FILE_ID ONLINE _ TABLESPACE_NAME
--------------------------------------------------------------------------------------------
+ DATA/sdgdorcl/datafile/system.259.848099691 1 SYSTEM
+ DATA/sdgdorcl/datafile/sysaux.260.848099695 2 ONLINE SYSAUX
+ DATA/sdgdorcl/datafile/undotbs1.261.848099697 3 ONLINE UNDOTBS1
+ DATA/sdgdorcl/datafile/undotbs2.263.848099707 4 ONLINE UNDOTBS2
+ DATA/sdgdorcl/datafile/users.264.848099707 5 ONLINE USERS
+ DATA/sdgdorcl/datafile/data01.268.848183595 6 ONLINE data01
+ DATA/sdgdorcl/datafile/nnc_index01.269.848183657 7 ONLINE INDEX01
+ DATA/sdgdorcl/datafile/data0101.268.8481835951.ora 8 ONLINE data01
+ DATA/sdgdorcl/datafile/data01.271.854940577 9 ONLINE data01
+ DATA/sdgdorcl/datafile/data0102.268.8481835951.ora 10 ONLINE data01
+ DATA/sdgdorcl/datafile/nnc_index01.20160308.ora 11 ONLINE INDEX01

FILE_NAME FILE_ID ONLINE _ TABLESPACE_NAME
--------------------------------------------------------------------------------------------
/U01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora 12 ONLINE data01
/U01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora 13 ONLINE data01
+ DATA/sdgdorcl/datafile/data01.274.942512215 14 ONLINE data01
+ DATA/sdgdorcl/datafile/data01.275.942512497 15 ONLINE data01
+ DATA/sdgdorcl/datafile/nnc_index01.276.942512519 16 ONLINE INDEX01
+ DATA/sdgdorcl/datafile/nnc_index01.277.942512529 17 ONLINE INDEX01
SQL>

3. Clean and close RAC2 and RAC1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. Start mount status of RAC1
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.7103E + 10 bytes
Fixed Size 2245480 bytes
Variable Size 7381978264 bytes
Database Buffers 9663676416 bytes
Redo Buffers 55263232 bytes
Database mounted.
SQL>

5. Run the rman cp command to copy data files.
[Oracle @ sdgddb1 ora_data] $ rman target/
RMAN> copy datafile '/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora' to '+ data ';
Starting backup at 13-MAY-16
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 433 instance = sdgdorcl1 device type = DISK
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00013 name =/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora
Output file name = + DATA/sdgdorcl/datafile/data01.278.911738531 tag = TAG20160513T045849 RECID = 1 STAMP = 911710866
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25
Finished backup at 13-MAY-16

RMAN> copy datafile '/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora' to '+ data ';
Starting backup at 13-MAY-16
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00012 name =/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora
Output file name = + DATA/sdgdorcl/datafile/data01.279.911742569 tag = TAG20160513T050248 RECID = 2 STAMP = 911711045
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 13-MAY-16
RMAN>

6. In sqlplus, start the database to the mount state, rename the data file, and view the information.
SQL> alter database rename file '/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora' to '+ DATA/sdgdorcl/datafile/data01.278.911738531 ';
SQL> alter database rename file '/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora' to '+ DATA/sdgdorcl/datafile/data01.279.911742569 ';

7. Start rac1 and rac2
# RAC1
SQL> alter database open;
Database altered.
SQL>

# RAC2
SQL> startup
ORACLE instance started.
Total System Global Area 1.7103E + 10 bytes
Fixed Size 2245480 bytes
Variable Size 7381978264 bytes
Database Buffers 9663676416 bytes
Redo Buffers 55263232 bytes
Database mounted.
Database opened.
SQL>

Appendix: Procedures for migrating SYSTEM data files (process description ):
1. Stop DB.
2. Move the datafile using asmcmd.
3. Mount the DB.
4. Rename the datafile.
5. Open the DB.
6. On other RAC nodes you still need to bounce the database because it is SYSTEM tablespace, otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.

How to copy data files in ASM to the Operating System

Restoration After all Oracle 11g rac asm disks are lost

Oracle 11g from entry to proficient in PDF + CD source code

Installing Oracle 11g R2 using RHEL6 ASM

Oracle 10g manual creation of the ASM Database

Oracle 10g R2 create ASM instance Step By Step