Description:
- In this article we are going to see Migrating Oracle Database from Non-ASM to ASM Storage.
- There are several methods is there to move database from filesystem to Asm diskgroup like RMAN,DBMS_FILE_TRANSFER,ASMCMD and 12c online Move datafile
- Here we are going to convert database from local filesystem to ASM storage using Rman Method in 11g Database version.
Case Scenario:
- Database Version is 11.2.0.3 and having datafiles at file system.
- ASM installed and ready to be used.
Pre-Steps
Check the Asm instance is Running
select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;
select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
Login to ascmd check using lsdg command
We are going to migrate the below database “ORCL” which is 11g version and Disk Group Name is “+DATA”
SQL> select name from v$database; NAME ——— ORCL SQL> select name from v$datafile; NAME ——————————————– /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/orcl/example01.dbf SQL> select name from v$controlfile; NAME —————————————– /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl SQL> select member from v$logfile; MEMBER ————————————— /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log
Migration steps:
Step1:-Login in to ORCL Database change the below parameters
SQL> ALTER SYSTEM SET control_files=’+DATA’ scope=spfile; System altered. SQL> ALTER SYSTEM SET db_create_file_dest=’+DATA’ scope=spfile; System altered. SQL> ALTER SYSTEM SET db_recovery_file_dest=’+DATA’ scope=spfile; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
Step2:-Startup the ORCL database in NOMOUNT mode
SQL> startup nomount ORACLE instance started. Total System Global Area 1607008256 bytes Fixed Size 1345240 bytes Variable Size 486541608 bytes Database Buffers 1107296256 bytes Redo Buffers 11825152 bytes
Step3:-Connect to RMAN Session to copy the controlfile from local filesystem to ASM Diskgroup “+DATA”
[oracle@oracleasm ~]$ export ORACLE_SID=orcl [oracle@oracleasm ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:27:31 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> RESTORE CONTROLFILE FROM ‘/u01/app/oracle/oradata/orcl/control01.ctl’; Starting restore at 11-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATA/orcl/controlfile/current.280.978517795 Finished restore at 11-JUN-18
Step4:-Connect to SQL*Plus and mount the database
[oracle@oracleasm ~]$ sqlplus ‘/as sysdba’ SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 10:31:51 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter database mount; Database altered. SQL> select name from v$controlfile; NAME ———————————————– +DATA/orcl/controlfile/current.280.978517795
Step5:-Again connect to RMAN session to copy the database files from the local filesystem to ASM Diskgroup “+DATA”
[oracle@oracleasm ~]$ export ORACLE_SID=orcl [oracle@oracleasm ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:35:58 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1506003460, not open) RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA’; Starting backup at 11-JUN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf output file name=+DATA/orcl/datafile/system.281.978518177 tag=TAG20180611T103616 RECID=2 STAMP=978518232 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf output file name=+DATA/orcl/datafile/sysaux.282.978518235 tag=TAG20180611T103616 RECID=3 STAMP=978518275 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf output file name=+DATA/orcl/datafile/example.283.978518283 tag=TAG20180611T103616 RECID=4 STAMP=978518299 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf output file name=+DATA/orcl/datafile/undotbs1.284.978518307 tag=TAG20180611T103616 RECID=5 STAMP=978518310 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:05 channel ORA_DISK_1: starting datafile copy copying current control file output file name=+DATA/orcl/controlfile/backup.285.978518313 tag=TAG20180611T103616 RECID=6 STAMP=978518313 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=+DATA/orcl/datafile/users.286.978518315 tag=TAG20180611T103616 RECID=7 STAMP=978518314 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-JUN-18 channel ORA_DISK_1: finished piece 1 at 11-JUN-18 piece handle=+DATA/orcl/backupset/2018_06_11/nnsnf0_tag20180611t103616_0.287.978518315 tag=TAG20180611T103616 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 11-JUN-18
Step6:-Update the control file and data dictionary for the database files point to the ASM Diskgroup “+DATA”
RMAN> SWITCH DATABASE TO COPY; datafile 1 switched to datafile copy “+DATA/orcl/datafile/system.281.978518177” datafile 2 switched to datafile copy “+DATA/orcl/datafile/sysaux.282.978518235” datafile 3 switched to datafile copy “+DATA/orcl/datafile/undotbs1.284.978518307” datafile 4 switched to datafile copy “+DATA/orcl/datafile/users.286.978518315” datafile 5 switched to datafile copy “+DATA/orcl/datafile/example.283.978518283” SQL> select name from v$datafile; (Check database level) NAME ——————————————— +DATA/orcl/datafile/system.281.978518177 +DATA/orcl/datafile/sysaux.282.978518235 +DATA/orcl/datafile/undotbs1.284.978518307 +DATA/orcl/datafile/users.286.978518315 +DATA/orcl/datafile/example.283.978518283
Step7:-Tempfile relocating to ASM diskgroup
SQL> select name from v$tempfile;
NAME
—————————————–
/u01/app/oracle/oradata/orcl/temp01.dbf
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@oracleasm ~]$ export ORACLE_SID=orcl
[oracle@oracleasm ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 11 10:47:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1506003460, not open)
RMAN> run
{
set newname for tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ to ‘+DATA’;
switch tempfile all;
}2> 3> 4> 5>
executing command: SET NEWNAME
using target database control file instead of recovery catalog
renamed tempfile 1 to +DATA in control file
SQL> select name from v$tempfile;(Check database level)
NAME
—————————————-
+DATA/orcl/tempfile/temp.288.978519169
Step8:-Connect to SQL*Plus and open the database
[oracle@oracleasm ~]$ sqlplus ‘/as sysdba’ SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 10:52:41 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter database open; Database altered.
Step9:-One by one drop and re-create the online redo logfiles to ASM Diskgroup “+DATA”
SQL> select group#, status from v$log; GROUP# STATUS ———- —————- 1 INACTIVE 2 CURRENT 3 INACTIVE (Note:-inactive and unused group only we need to drop) SQL> select member from V$logfile; MEMBER —————————————– /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log SQL> alter database add logfile group 1 size 50m; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 50m; Database altered. SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 50m; Database altered. SQL> select member from V$logfile; MEMBER ——————————————————————————– +DATA/orcl/onlinelog/group_3.291.978519961 +DATA/orcl/onlinelog/group_2.293.978519999 +DATA/orcl/onlinelog/group_1.289.978519919 +DATA/orcl/onlinelog/group_1.290.978519921 +DATA/orcl/onlinelog/group_3.292.978519961 +DATA/orcl/onlinelog/group_2.294.978520001
Successfully completed the Migrating a Oracle Database From Non-ASM to ASM
Example for 12c:
It is very simple to migrate 12c Database from non-ASM to ASM. Simply use online datafile move command as follow. Assuming ASM is already installed and ready to use.
Move to ASM:
ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/orcl/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;
Ref: www.docs.oracle.com