Step by Step Migrating a Oracle Database From Non-ASM to ASM

Step by Step Migrating a Oracle Database From Non-ASM to ASMDescription:-

  • In this article we are going to see How to migrate the database from normal Filesystem 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

Example for 12c:-

12c Migrate Database from non-ASM to ASM using online datafile move

Move to ASM:
ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/orcl/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;

  • ASM Instance is already Created and Ready to Use see the below link.

Step by Step configuration ASM for a Standalone Database Here 

Let’s Start the Demo:-
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”  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

Step6:-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

Step7:-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.

Step8:-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

Reference:-

https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG12000

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Step by Step configuration ASM for a Standalone Database

Step by Step configuration ASM for a Standalone Database

Description:-

  • In this article we are going to see Step by Step configuration Asm for a Standalone Database.

Four stage we are going to see the demo:-

  • Adding Diskgroup in Vmware
  • Grid Software installation
  • Oracle Software installation
  • Asm Standalone Database creation using DBCA Tool

Let’s Start the Demo:-

  • Already installed OEL 5.5  in Vmware

Note:-Do not forget the below steps while installing OS OEL5.5

select the package “oracle validated package” and ASM Rpms.

Prepare for oracle binaries installation 

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Login in to oracle set the bash profile for grid home and oracle home
su – oracle
vi .bash_profile
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=oracleasm; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/11.2.0/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/11.2.0/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=RAC1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
alias grid=’. /home/oracle/grid_env’
alias db=’. /home/oracle/db_env’

vi grid_env
ORACLE_SID=+ASM1; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

vi  db_env
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

DISPLAY=oracleasm:0.0; export DISPLAY

[root@oracleasm ~]# init 0

Adding Disk in Vmware

Double click the Hard DiskClick Add Button

Click Independent Button and Next
Click Next

Click Allocate all disk space now (Because Database creation will fast)Browse the folder for ASM DiskDiskgroup creation in Progress

I added 2 Disk groups 10GB and 15GB (same steps follow for the 2nd disk)

Power on the Vmware and check fdisk -l

partition the disks

[root@oracleasm ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.

After partition the disks (/dev/sdb1 & /dev/sdc1)

Configure ASMLib using the following command.

[root@oracleasm ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets (‘[]’). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
[root@oracleasm ~]# /usr/sbin/oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module “oracleasm”: oracleasm
Mounting ASMlib driver filesystem: /dev/oracleasm

[root@oracleasm ~]# /usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oracleasm ~]# /usr/sbin/oracleasm createdisk DISK2 /dev/sdc1
Writing disk header: done
Instantiating disk: done

[oracle@oracleasm disks]$ cd /dev/oracleasm/disks
[oracle@oracleasm disks]$ ls -lrt
total 0
brw-rw—- 1 oracle oinstall 8, 17 Jun 7 22:39 DISK1
brw-rw—- 1 oracle oinstall 8, 33 Jun 7 22:39 DISK2

We have completed the Diskgroup creation at OS LEVEL
Now, We are going to install grid software 11.2.0.3

[root@oracleasm ~]# su – oracle
[oracle@oracleasm ~]$ cd /u01
[oracle@oracleasm u01]$ ls -lrt
total 3363640
drwxrwxr-x 2 oracle oinstall 16384 Jun 7 07:08 lost+found
drwxrwxr-x 3 oracle oinstall 4096 Jun 7 19:18 app
-rwxrwxr-x 1 oracle oinstall 1337967916 Jun 7 21:04 p10404530_112030_LINUX_1of7.zip
-rwxrwxr-x 1 oracle oinstall 1142289834 Jun 7 21:05 p10404530_112030_LINUX_2of7.zip
-rwxrwxr-x 1 oracle oinstall 960703760 Jun 7 21:06 p10404530_112030_LINUX_3of7.zip

Unzip start installing Grid Software

[oracle@oracleasm ~]$ . .bash_profile
[oracle@oracleasm ~]$ grid
[oracle@oracleasm ~]$ echo $ORACLE_HOME
/u01/app/11.2.0/grid
[oracle@oracleasm ~]$ cd /u01/grid
[oracle@oracleasm grid]$ ./runInstaller

Click configure oracle grid infrastructure for standalone server 

Click next

Browse the location and see the disks(/dev/oracleasm/disks)

Allocate the password

set the Asm group

Set the grid home

Click next

Click Finish

Grid software installation in progress

Login in to Root user and run the below script

[oracle@oracleasm grid]$ su – root
Password:
[root@oracleasm ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@oracleasm ~]# /u01/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …
Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node oracleasm successfully pinned.
Adding Clusterware entries to inittab
oracleasm 2018/06/07 22:56:55 /u01/app/11.2.0/grid/cdata/oracleasm/backup_20180607_225655.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

11.2.0.3 Grid software successfully completed.

Next we are going to install oracle binaries

[oracle@oracleasm u01]$ ls -lrt
total 3363648
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 database
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 2011 grid
drwxrwxr-x 2 oracle oinstall 16384 Jun 7 07:08 lost+found
-rwxrwxr-x 1 oracle oinstall 1337967916 Jun 7 21:04 p10404530_112030_LINUX_1of7.zip
-rwxrwxr-x 1 oracle oinstall 1142289834 Jun 7 21:05 p10404530_112030_LINUX_2of7.zip
-rwxrwxr-x 1 oracle oinstall 960703760 Jun 7 21:06 p10404530_112030_LINUX_3of7.zip
drwxrwxr-x 5 oracle oinstall 4096 Jun 7 22:52 app

[oracle@oracleasm ~]$ . .bash_profile
[oracle@oracleasm ~]$ db
[oracle@oracleasm ~]$ cd /u01/database
[oracle@oracleasm database]$ ./runInstaller

Uncheck  the box

Click skip software update

Click create and configure database or Here click install database software only after using DBCA we will create new DB

Click Desktop Class

Database name and password

[root@oracleasm ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

ASM database creation using DBCA Tool.

[oracle@oracleasm ~]$ export ORACLE_SID=chennai
[oracle@oracleasm ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 8 01:15:32 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> select name from v$database;
NAME
———
CHENNAI
SQL> select name from v$datafile;
NAME
————————————————-
+DATA/chennai/datafile/system.256.978224651
+DATA/chennai/datafile/sysaux.257.978224651
+DATA/chennai/datafile/undotbs1.258.978224651
+DATA/chennai/datafile/users.259.978224651

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba