You can use these steps to migrate a NON-ASM database to ASM database in Oracle 11gR2.
Prerequisite - ASM instance should be created and up and running. Please refer my previous article to create a ASM instance
1) Check the database version and status
SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;
INSTANCE_NAME VERSION DATABASE_STATUS
---------------- ----------------- -----------------
learndb 11.2.0.3.0 ACTIVE
2) We should be able to check the ASM disk available from the instance which we created earlier
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- ----------
DISK1 NORMAL 4777 ORCL:DISK1
Migrating the SPFILE from Non-asm to ASM
1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk
RMAN>BACKUP AS BACKUPSET SPFILE;
2) Now shutdown the database and start it in Mount stage
SQL> Shutdown immediate
SQL> STARTUP MOUNT;
3) Now restore the spfile from the backup to ASM disk as below
RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';
4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/11.2.0/dbh
ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance
SQL> shutdown immediate
6) Delete "spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora with the following line of content and start the instance,
vi initlearndb.ora
SPFILE='+DATA/spfilelearndb.ora'
SQL> Startup ( first it will search for spfile<sid>.ora which we deleted and next it will look for init<sid>.ora which we have moified with the above content )
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilelearndb.ora
Migrating Control files from Non-ASM to ASM
1) These are the current control files in non-asm
SQL>show parameter contol_files
control_files string /u01/data/learndb/control01.ctl,
/u01/data/learndb/control02.ctl
2) Start the instance in nomount state
SQL> startup nomount
3) You can move these control files to ASM using RMAN
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (not mounted)
RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';
4) You can check whether the control file are created
ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'
+DATA/LEARNDB/CONTROLFILE/current.256.787200593
5) Update the spfile with new control file location
SQL> alter system set control_files='+DATA/LEARNDB/CONTROLFILE/current.256.787200593' scope=spfile;
6) Now shutdown the instance and startup in mount stage and check for the control file location
SQL> shutdown immediate
SQL> startup mount
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/learndb/controlfile/curr
ent.256.787200593
Migrating the Datafiles from Non-ASM to ASM
1) Keep the database is in mount stage
2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (DBID=3704858930, not open)
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
3) Switch the database to the copy created using the following RMAN command
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"
4) Migrate the tempfile to ASM using RMAN
RMAN> run
2> {
3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
4> switch tempfile all;
5> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
renamed tempfile 1 to +DATA in control file
5) Now open the Database
RMAN> ALTER DATABASE OPEN;
database opened
6) You can now check the datafiles created in ASM
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/learndb/datafile/users.261.787201725
+DATA/learndb/datafile/undotbs1.259.787201713
+DATA/learndb/datafile/sysaux.257.787201553
+DATA/learndb/datafile/system.258.787201633
Migrating the Redo log files from Non-ASM to ASM
1) Identify the currently available redo log files using the following command
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER STATUS
---------- --------- -----------
3 /u01/data/learndb/redo03.log INACTIVE
2 /u01/data/learndb/redo02.log CURRENT
1 /u01/data/learndb/redo01.log INACTIVE
2) Add the new logfiles to ASM using following command
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;
3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';
4) You can check the logfiles created now in ASM
GROUP# MEMBER STATUS
---------- --------- -----------
1 +DATA/learndb/onlinelog/group_1.264.787205393 CURRENT
2 +DATA/learndb/onlinelog/group_2.265.787205405 INACTIVE
3 +DATA/learndb/onlinelog/group_3.266.787205417 INACTIVE
Now your database is migrated to ASM
Hi DUDE,
ReplyDeleteI need one help from you. I have a Lunix RH 6 Machine.
[root@oc4827630337 oracle]# rpm -qa|grep oracleasm
oracleasm-support-2.1.5-1.el6.x86_64
oracleasmlib-2.0.4-1.el6.x86_64
[root@oc4827630337 oracle]#
But
[root@oc4827630337 oracle]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@oc4827630337 oracle]#
ANd
[root@oc4827630337 oracle]# oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=oracle
ORACLEASM_GID=dba
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
[root@oc4827630337 oracle]#
When I checked in Google, there is no kernel dirver available for DownLoad for my kernel version
[root@oc4827630337 oracle]# oracleasm update-driver
Kernel: 2.6.32-279.19.1.el6.x86_64 x86_64
Driver name: oracleasm-2.6.32-279.19.1.el6.x86_64
Driver for kernel 2.6.32-279.19.1.el6.x86_64 does not exist
[root@oc482763f0337 oracle]#
Could you please suggest - how can I overcome this situation and do the ASM setup migrating from a NON ASM DB to a ASM db...
Thanks in advance for your help.
INDRANIL, IBM
Hi Indranil,
Deleteoracle does not release asmlib rpms for RHEL6.
If you have Oracle unbreakable linux network support, you can download it from there
Refer the document ID 1089399.1, for more information
==========
Software Update Policy for ASMLib running on future releases of Red Hat Enterprise Linux
Red Hat Enterprise Linux 6 (RHEL6)
For RHEL6 or Oracle Linux 6, Oracle will only provide ASMLib software and updates when configured Unbreakable Enterprise Kernel (UEK). Oracle will not provide ASMLib packages for kernels distributed by Red Hat as part of RHEL 6 or the Red Hat compatible kernel in Oracle Linux 6. ASMLib updates will be delivered via Unbreakable Linux Network(ULN) which is available to customers with Oracle Linux support. ULN works with both Oracle Linux or Red Hat Linux installations, but ASMlib usage will require replacing any Red Hat kernel with UEK
Hi Kumara
ReplyDeletePlease send me the step by step upgrade 10gR (10.2.0.3) RAC to 11.2.0.3 RAC .
We are not using ASM in 10g RAC database,
excellent senthil kumar very much understandable. so greatful to you. thank you so much
ReplyDeleteGreat Work
ReplyDeleteGreat Work
ReplyDeleteReally appreciable. Sir you described the process in very understanding way and I check it. Thank you Helping
ReplyDeletenice and helpful..thanks
ReplyDeletenice post..
ReplyDeletenice
ReplyDelete