How do you migrate oracle database from Non ASM to ASM
In this tutorial i will explained step by step Migration from non asm oracle database to asm oracle database
Simple Steps of database migration From Non ASM to ASM::
- SET THE control_files and db_create_file_dest
- backup the database and archivelog with controlfile using rman utility
- SET THE control_files and db_create_file_dest to relevent asm disk
- shutdown the database
- startup the nomount mode and Restore the control file
- mount the database
- Backup Database Into Asm Disk Group
- Switch All Data Files To The New Asm Diskgroup Location
- recover the database
- open the database using resetlogs
STEP 1. SET THE control_files and db_create_file_dest PARAMETER BEFORE BACKUP IF NOT SET IN NON ASM DATABASE AND ALSO ENABLE ARCHIVELOG MODE
SQL>
alter system set control_files='/home/oracle/kfc/oradata/control/control01.ctl' scope=spfile;
alter system set db_create_file_dest='/home/oracle/kfc/oradata/data/' scope=spfile;
--The ALTER DATABASE ARCHIVELOG command can only be performed if the database in mounted mode.
alter database close;
alter database archivelog;
-- BACKUP THE UPDATED pfile
create spfile from pfile;
create pfile from spfile;
startup force;
--CHECK THE control_files and db_create_file_dest PARAMETER IF ENABLED OR NOT
SQL> sho parameter db_create_file_dest
SQL> sho parameter control_files
STEP 2. BACKUP THE DATABASE WITH ARCHIVELOG USING RAMAN BEFORE TAKING BACKUP ENABLE THE CONTROLFILE AUTOBACKUP ON
RMAN>
rman target /
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman_control/%F';
backup database plus archivelog format '/home/oracle/rman_data/data_%U';
STEP 3.SET THE DB_control_files and db_create_file_dest PARAMETERS TO THE RELEVENT ASM DISK GROUPS.
alter system set control_files='+DG1' scope=spfile;
alter system set db_create_file_dest='+DG1' scope=spfile;
STEP 4.SHUT DOWN THE TARGET DATABASE AND STARTUP THE DATABASE UNTIL NOMOUNT STAGE THEN CONNECT TO RMAN UTILITY
shut immediate;
startup nomount;
create pfile from spfile;
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /home/oracle/kfc/oradata/contr
ol/control01.ctl
[oracle@teng dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 28 09:14:08 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: kfc (not mounted)
STEP 5. RESTORE THE CONTROLFILE FROM RMAN CONTROL FILE BACKUP LOCATION('/home/oracle/rman_control/%F';)
RMAN> restore controlfile from '/home/oracle/rman_control/c-1039361779-20160803-00';
Starting restore at 03-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+DG1/kfc/controlfile/current.283.918908975
Finished restore at 03-AUG-16
STEP 6. MOUNT THE DATABASE USING RESTORED CONTROLFILE(DATABASE WOULD NOT BE MOUNT IF NO CONTROLFILE)
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
STEP 7.BACKUP DATABASE INTO ASM DISK GROUP ('+DG1')
RMAN>backup as copy database format '+DG1';
Starting backup at 03-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/home/oracle/dummy/0rrbqn03.dbf
output filename=+DG1/kfc/datafile/system.284.918909681 tag=TAG20160803T124120 recid=10 stamp=918909765
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:27
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/home/oracle/dummy/0srbqn2k.dbf
output filename=+DG1/kfc/datafile/sysaux.285.918909767 tag=TAG20160803T124120 recid=11 stamp=918909838
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:20
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/home/oracle/dummy/0trbqn4a.dbf
output filename=+DG1/kfc/datafile/tbs.286.918909847 tag=TAG20160803T124120 recid=12 stamp=918909879
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:37
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/dummy/0urbqn4t.dbf
output filename=+DG1/kfc/datafile/tbs1.287.918909885 tag=TAG20160803T124120 recid=13 stamp=918909908
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 03-AUG-16
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
STEP 8.SWITCH ALL DATA FILES TO THE NEW ASM DISKGROUP LOCATION ('+DG1').
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/kfc/datafile/system.284.918909681"
datafile 2 switched to datafile copy "+DG1/kfc/datafile/sysaux.285.918909767"
datafile 3 switched to datafile copy "+DG1/kfc/datafile/tbs.286.918909847"
datafile 4 switched to datafile copy "+DG1/kfc/datafile/tbs1.287.918909885"
STEP 9.RECOVER THE ORACLE DATABASE USING "recover database" COMMAND
RMAN> recover database; Starting recover at 03-AUG-16 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 4 is already on disk as file +DG1/kfc/onlinelog/group_1.277.918365371 archive log filename=+DG1/kfc/onlinelog/group_1.277.918365371 thread=1 sequence=4 media recovery complete, elapsed time: 00:00:01 Finished recover at 03-AUG-16
STEP 10.OPEN DATABASE USING RESETLOGS
RMAN> alter database open resetlogs;
database opened
STEP 11.CHECK THE DATABASE DATAFILE AND CONTROLFILE LOCATION WHETHER IS SUCCEFULLY RESTORED OR NOT IN ASM DISK.
SQL> show parameter control; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DG1/kfc/controlfile/current.2 83.918908975 SQL> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DG1
Comments
Post a Comment