Migrate Oracle database files to ASM

Migrate Oracle database files to ASM

Read navigation

• Migrate data files to ASM
• Migrate log files to ASM
• Migrate control files to ASM

Migrate data files to ASM

Database migration in the case of Database Consistency: Start the database to the mount state, generate the rman copy statement, and then execute in rman:

SQL> startup mount
SQL> select Q' (copy datafile ')' | name | Q' ('to' + oradata ';)' from v $ datafile;
Q' (COPYDATAFILE ')' | NAME | Q' ('to' + oradata ';)'
-------------------------------------------------------------------------------
Copy datafile 'd: \ NSOADP \ SYSTEM01.DBF 'to' + oradata ';
Copy datafile 'd: \ NSOADP \ SYSAUX01.DBF 'to' + oradata ';
Copy datafile 'f: \ NSOADP \ undotbs01.dbf' to '+ oradata ';
Copy datafile 'd: \ NSOADP \ USERS01.DBF 'to' + oradata ';
SQL> exit
D: \> rman target/
RMAN> run
2> {
3> copy datafile 'd: \ NSOADP \ SYSTEM01.DBF 'to' + oradata ';
4> copy datafile 'd: \ NSOADP \ SYSAUX01.DBF 'to' + oradata ';
5> copy datafile 'f: \ NSOADP \ undotbs01.dbf' to '+ oradata ';
6> copy datafile 'd: \ NSOADP \ USERS01.DBF 'to' + oradata ';
7>}
Start backup on 10:02:44
Use channel ORA_DISK_1
Channel ORA_DISK_1: starts data file copies
Input data file: File No. = 00001 name = D: \ NSOADP \ SYSTEM01.DBF
Output file name = + ORADATA/nsoa/datafile/system.260.877082571 mark = tag201501_t100250 RECID = 265 STAMP = 877082603
Channel ORA_DISK_1: Data File copied. elapsed time: 00:00:35
Backup completed on 10:03:25
......

Generate a statement to change the data name of the control file, and then execute the change:

SQL> SELECT -- T. NAME, T. FILE #, C. FILE #, C. NAME
2 Q' (alter database rename file ')' | c. name | Q' ('to') '| t. name | Q '(';)'
3 from v $ DATAFILE_COPY t left join v $ DATAFILE C
4 on t. FILE # = C. FILE #
5 where t. name is not null order by COMPLETION_TIME desc;

SQL> alter database rename file 'd: \ NSOADP \ SYSTEM01.DBF 'to' + ORADATA/nsoa/datafile/system.260.877082571 ';

Migration in case of Database Inconsistency (archive mode ):
Use the alter database datafile' offline command to offline the data file, and then process the data file in the same way as above.

SQL> alter database datafile '/u01/oradata/sydb/user01.dbf' offline;

RMAN> copy datafile '/u01/oradata/sydb/user01.dbf' to' + oradata ';

Starting backup at 23-APR-15
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 180 device type = DISK
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00004 name =/u01/oradata/sydb/user01.dbf
Output file name = + ORADATA/sydb/datafile/users.257.877803115 tag = TAG20150423T181155 RECID = 1 STAMP = 877803123
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-APR-15

SQL> alter database rename file '/u01/oradata/sydb/user01.dbf' to '+ ORADATA/sydb/datafile/users.257.877803115 ';
SQL> alter database datafile '+ ORADATA/sydb/datafile/users.257.877803115' online;
Alter database datafile '+ ORADATA/sydb/datafile/users.257.877803115' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+ ORADATA/sydb/datafile/users.257.877803115'
SQL> recover datafile '+ ORADATA/sydb/datafile/users.257.877803115 ';
Media recovery complete.
SQL> alter database datafile '+ ORADATA/sydb/datafile/users.257.877803115' online;

Migrate log files to ASM

You can migrate non-current or active log files to asm in the mount or open state. The existing Log Files must have a member for each log group) or delete a log group (the database must have two log groups) and add a new log file or log group;

Alter database drop logfile member 'e: \ NSOADP \ REDO07.LOG '; # delete a log file
Alter database drop logfile group 2; # delete a log group
Alter database add logfile member '+ oradata' to group 7; # add a log file
Alter database add logfile ('+ oradata', 'oradata') size 50 m; # add a log group with two members
Alter database add logfile '+ oradata', 'oradata' size 50 m; # add two log groups at the same time

Migrate control files to ASM

First, determine the location and name of the control file.

SQL> show parameter control_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
Control_file_record_keep_time integer 7
Control_files string F: \ NSOADP \ CONTROL01.CTL

Modify the value of the control_files initialization parameter to asm, and then restart the database to the nomount status:

SQL> alter system set control_files = '+ oradata' scope = spfile;
SQL> shutdown immediate;
SQL> startup nomount

Use rman to copy the current control file to asm

Rman target/
RMAN> restore controlfile from 'f: \ NSOADP \ CONTROL01.CTL ';
Start restore on 09:40:30
Use the target database control file to replace the recovery directory
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 70 device type = DISK

Channel ORA_DISK_1: control file copy copied
Output file name = + ORADATA/nsoa/controlfile/current.314.877686033
Restore completed on 09:40:39

Note: If the backup control file is used for restoration, and the database has previously been used for RESETLOGS operations or the statement created when the control file is created with the RESETLOGS specified, you must use the RESETLOGS method to open the database.

SQL> alter database mount;
SQL> show parameter control_file

NAME TYPE VALUE
----------------------------------------------------------------------------------------
Control_file_record_keep_time integer 7
Control_files string + ORADATA/nsoa/controlfile/curr
Ent.314.877686033
SQL> alter database open;

-- Then end

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