Migrate Oracle Database To ASM Using ASMCMD.
Hai, in this article I
will demonstrate how to migrate Oracle Database from normal file System to ASM
Method 1: using ASMCMD.
Method 2: Using RMAN.
List of Topics (Linux, Database, RAC, EBS)
Method 1: using ASMCMD.
Method 2: Using RMAN.
List of Topics (Linux, Database, RAC, EBS)
Linux installed on
VM-Ware Linux version 5.7_x64 Bit
Oracle Database Version : 11.2.0.1_x64
Oracle Database Version : 11.2.0.1_x64
Assumptions:
Machine
|
IP Address
|
DB Name
|
SID
|
Listener
|
RAC1
|
192.168.1.11
|
DELL
|
DELL
|
DELL
|
[root@rac1 ~]#
[root@rac1 ~]# lsb_release
-a
LSB Version:
:core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
Distributor ID:
EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server
release 5.7 (Carthage)
Release: 5.7
Codename: Carthage
[root@rac1 ~]#
[root@rac1 ~]# arch
x86_64
[root@rac1 ~]#
[root@rac1 ~]#
[root@rac1 ~]# hostname
rac1.dell.com
[root@rac1 ~]#
[root@rac1 ~]# hostname -i
192.168.1.11
[root@rac1 ~]#
[root@rac1 ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:F3:1A:CD
inet addr:192.168.1.11 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500
Metric:1
RX packets:629747 errors:0 dropped:0
overruns:0 frame:0
TX packets:1296573 errors:0 dropped:0
overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:326863274 (311.7 MiB) TX bytes:1670770949 (1.5 GiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436
Metric:1
RX packets:5012 errors:0 dropped:0
overruns:0 frame:0
TX packets:5012 errors:0 dropped:0
overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:2169467 (2.0 MiB) TX bytes:2169467 (2.0 MiB)
virbr0 Link encap:Ethernet HWaddr 8A:45:ED:D4:24:30
inet addr:192.168.122.1 Bcast:192.168.122.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500
Metric:1
RX packets:0 errors:0 dropped:0
overruns:0 frame:0
TX packets:18 errors:0 dropped:0
overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:3357 (3.2 KiB)
[root@rac1 ~]#
[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.1.11 rac1.dell.com rac1
192.168.1.12 rac2.dell.com rac2
192.168.1.40 san.dell.com san
[root@rac1 ~]#
Create new groups and user
[root@rac1 ~]#
[root@rac1 ~]# userdel
oracle
[root@rac1 ~]# groupdel
oinstall
[root@rac1 ~]# groupdel dba
[root@rac1 ~]#
[root@rac1 ~]# rm -rf
/var/mail/oracle
[root@rac1 ~]# rm -rf
/home/oracle/
[root@rac1 ~]#
[root@rac1 ~]# groupadd -g
1000 oinstall
[root@rac1 ~]# groupadd -g
1001 dba
[root@rac1 ~]# groupadd -g
1002 asmdba
[root@rac1 ~]# groupadd -g
1003 asmadmin
[root@rac1 ~]# groupadd -g
1004 asmoper
[root@rac1 ~]#
[root@rac1 ~]# useradd -u
1100 -g oinstall -G dba,asmdba,asmadmin oracle
[root@rac1 ~]# useradd -u
1101 -g oinstall -G dba,asmdba,asmadmin,asmoper grid
[root@rac1 ~]#
[root@rac1 ~]# passwd
oracle
Changing password for
user oracle.
New UNIX password:
BAD PASSWORD: it is
based on a dictionary word
Retype new UNIX
password:
passwd: all
authentication tokens updated successfully.
[root@rac1 ~]#
[root@rac1 ~]# passwd grid
Changing password for
user grid.
New UNIX password:
BAD PASSWORD: it is
too short
Retype new UNIX
password:
passwd: all
authentication tokens updated successfully.
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ id
uid=1100(oracle)
gid=1000(oinstall) groups=1000(oinstall),1001(dba),1002(asmdba),1003(asmadmin)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ exit
logout
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ id
uid=1101(grid)
gid=1000(oinstall)
groups=1000(oinstall),1001(dba),1002(asmdba),1003(asmadmin),1004(asmoper) [grid@rac1
~]$
[grid@rac1 ~]$
Oracle Database (DELL) details
[root@rac1 ~]# ps -ef |
grep pmon
oracle 10403
1 0 08:36 ? 00:00:00 ora_pmon_DELL
root 10862
4501 0 08:41 pts/1 00:00:00 grep pmon
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/DELL/arch
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/DELL/arch
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cat dell.env
export ORACLE_SID=DELL
export
ORACLE_BASE=/u01/app/oracle
export
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib;
export LD_LIBRARY_PATH
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 06:34:28 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select
name,controlfile_type,open_mode from v$database;
NAME CONTROL OPEN_MODE
--------- -------
--------------------
DELL CURRENT READ WRITE
C-R-D files Location
SQL>
SQL> select name from v$controlfile;
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/control01.ctl
/u01/app/oracle/DELL/control02.ctl
SQL>
SQL>
SQL> select file_name
from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/users01.dbf
/u01/app/oracle/DELL/undotbs01.dbf
/u01/app/oracle/DELL/sysaux01.dbf
/u01/app/oracle/DELL/system01.dbf
SQL>
SQL>
SQL> select
member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/redo03.log
/u01/app/oracle/DELL/redo02.log
/u01/app/oracle/DELL/redo01.log
SQL>
SQL>
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/temp01.dbf
SQL>
SQL>
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL>
SQL> archive log list;
Database log
mode
No Archive Mode
Automatic
archival
Disabled
Archive
destination
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log
sequence 1
Current log
sequence 2
SQL>
Creation of ASM Disk
Here in this example I am using SAN Storage for ASM using Openfiler
[root@rac1 ~]#
[root@rac1 ~]# ping san
PING san.dell.com
(192.168.1.40) 56(84) bytes of data.
64 bytes from
san.dell.com (192.168.1.40): icmp_seq=1 ttl=64 time=85.5 ms
64 bytes from
san.dell.com (192.168.1.40): icmp_seq=2 ttl=64 time=0.437 ms
^C
--- san.dell.com ping
statistics ---
2 packets transmitted,
2 received, 0% packet loss, time 1077ms
rtt min/avg/max/mdev =
0.437/43.014/85.592/42.578 ms
[root@rac1 ~]#
[root@rac1 ~]# fdisk -l
Disk /dev/sda: 1073.7
GB, 1073741824000 bytes
255 heads, 63
sectors/track, 130541 cylinders
Units = cylinders of
16065 * 512 = 8225280 bytes
Device Boot Start End Blocks
Id System
/dev/sda1 *
1 25496 204796588+
83 Linux
/dev/sda2 25497 29320
30716280 82 Linux swap / Solaris
/dev/sda3 29321 33144
30716280 83 Linux
/dev/sda4 33145 130541
782341402+ 5 Extended
/dev/sda5 33145 130541
782341371 83 Linux
[root@rac1 ~]#
[root@rac1 ~]# chmod -R 775 /u01
[root@rac1 ~]#
[root@rac1 ~]# chmod -R 775 /u01
[root@rac1 ~]#
[root@rac1 ~]# iscsiadm -m
discovery -t st -p 192.168.1.40
192.168.1.40:3260,1
iqn.2006-01.com.openfiler:tsn.5cbf23534a21
[root@rac1 ~]#
[root@rac1 ~]# service
iscsi restart
iscsiadm: No matching
sessions found
Stopping iSCSI daemon:
iscsid is stopped
[ OK ]
Starting iSCSI daemon:
FATAL: Error inserting ib_iser
(/lib/modules/2.6.32-200.13.1.el5uek/kernel/drivers/infiniband/ulp/iser/ib_iser.ko):
Unknown symbol in module, or unknown parameter (see dmesg)
[ OK ]
[ OK ]
Setting up iSCSI
targets: Logging in to [iface: default, target:
iqn.2006-01.com.openfiler:tsn.5cbf23534a21, portal: 192.168.1.40,3260]
Login to [iface:
default, target: iqn.2006-01.com.openfiler:tsn.5cbf23534a21, portal:
192.168.1.40,3260] successful.
[ OK ]
[root@rac1 ~]#
[root@rac1 ~]# fdisk -l
Disk /dev/sda: 1073.7
GB, 1073741824000 bytes
255 heads, 63
sectors/track, 130541 cylinders
Units = cylinders of
16065 * 512 = 8225280 bytes
Device Boot Start End Blocks
Id System
/dev/sda1 *
1 25496 204796588+
83 Linux
/dev/sda2 25497 29320
30716280 82 Linux swap / Solaris
/dev/sda3 29321 33144
30716280 83 Linux
/dev/sda4 33145 130541
782341402+ 5 Extended
/dev/sda5 33145 130541
782341371 83 Linux
Disk /dev/sdb:
102.3 GB, 102374572032 bytes
255 heads, 63
sectors/track, 12446 cylinders
Units = cylinders
of 16065 * 512 = 8225280 bytes
Disk /dev/sdb
doesn't contain a valid partition table
[root@rac1 ~]#
Format ASM Disk
[root@rac1 ~]#
[root@rac1 ~]# 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 12446.
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-12446, default 1):
Using default value 1
Last cylinder or +size
or +sizeM or +sizeK (1-12446, default 12446):
Using default value
12446
Command (m for help): w
The partition table
has been altered!
Calling ioctl() to
re-read partition table.
WARNING: Re-reading
the partition table failed with error 16: Device or resource busy.
The kernel still uses
the old table.
The new table will be
used at the next reboot.
Syncing disks.
[root@rac1 ~]#
[root@rac1 ~]# fdisk -l
Disk /dev/sda: 1073.7
GB, 1073741824000 bytes
255 heads, 63
sectors/track, 130541 cylinders
Units = cylinders of
16065 * 512 = 8225280 bytes
Device Boot Start End Blocks
Id System
/dev/sda1 *
1 25496 204796588+
83 Linux
/dev/sda2 25497 29320
30716280 82 Linux swap / Solaris
/dev/sda3 29321 33144
30716280 83 Linux
/dev/sda4 33145 130541
782341402+ 5 Extended
/dev/sda5 33145 130541
782341371 83 Linux
Disk /dev/sdb: 102.3 GB, 102374572032 bytes
255 heads, 63 sectors/track, 12446 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks
Id System
/dev/sdb1 1 12446
99972463+ 83 Linux
[root@rac1 ~]#
Configure ORACLEASM Parameters.
[root@rac1 ~]#
[root@rac1 ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@rac1 ~]#
[root@rac1 ~]# 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 []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@rac1 ~]#
[root@rac1 ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@rac1 ~]#
[root@rac1 ~]# oracleasm init
[root@rac1 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@rac1 ~]#
[root@rac1 ~]# oracleasm status
[root@rac1 ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@rac1 ~]#
[root@rac1 ~]# oracleasm createdisk DELLASM /dev/sdb1
[root@rac1 ~]# oracleasm createdisk DELLASM /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@rac1 ~]#
[root@rac1 ~]# oracleasm scandisks
[root@rac1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@rac1 ~]#
[root@rac1 ~]# oracleasm listdisks
[root@rac1 ~]# oracleasm listdisks
DELLASM
[root@rac1 ~]#
[root@rac1 ~]# cd /dev/oracleasm/disks/
[root@rac1 disks]# ll
total 0
brw-rw---- 1 grid asmadmin 8, 17 Dec 16 11:56 DELLASM
[root@rac1 disks]#
[root@rac1 ~]# partx -a /dev/sdb
BLKPG: Device or resource busy
error adding partition 1
BLKPG: Device or resource busy
error adding partition 5
BLKPG: Device or resource busy
[root@rac1 ~]#
[root@rac1 ~]# partprobe /dev/sdb1
[root@rac1 ~]#
Install and Configure
Grid Infrastructure for a Standalone Server
[root@rac1 ~]#
[root@rac1 ~]# xhost+
access control disabled, clients can connect from any host
[root@rac1 ~]#
[root@rac1 ~]# xhost
access control disabled, clients can connect from any host
[root@rac1 ~]#
[root@rac1 ~]# chown -R
grid:oinstall /u01/sftwr/grid/
[root@rac1 ~]#
[root@rac1 ~]# chmod –R 775
/u01
[root@rac1 ~]#
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ cd
/u01/sftwr/grid/
[grid@rac1 grid]$ ll
total 40
drwxrwxr-x 9 grid oinstall 4096 Aug 16 2009 doc
drwxrwxr-x 4 grid oinstall 4096 Aug 15 2009 install
drwxrwxr-x 2 grid oinstall 4096 Aug 15 2009 response
drwxrwxr-x 2 grid oinstall 4096 Aug 15 2009 rpm
-rwxrwxr-x 1 grid oinstall 3795 Jan 29 2009 runcluvfy.sh
-rwxrwxr-x 1 grid oinstall 3227 Aug 15 2009 runInstaller
drwxrwxr-x 2 grid oinstall 4096 Aug 15 2009 sshsetup
drwxrwxr-x 14 grid
oinstall 4096 Aug 15 2009 stage
-rwxrwxr-x 1 grid oinstall 4228 Aug 17 2009 welcome.html
[grid@rac1 grid]$
[grid@rac1 grid]$ ./runInstaller
Starting Oracle
Universal Installer...
Checking Temp space:
must be greater than 120 MB. Actual
27372 MB Passed
Checking swap space:
must be greater than 150 MB. Actual
29991 MB Passed
Checking monitor: must
be configured to display at least 256 colors.
Actual 16777216 Passed
Preparing to launch
Oracle Universal Installer from /tmp/OraInstall2017-12-16_12-09-19PM. Please
wait ...[grid@rac1 grid]$
Run fixup script
[root@rac1 ~]# /tmp/CVU_11.2.0.1.0_grid/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.1.0_grid/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.1.0_grid/fixup.enable
Log file location: /tmp/CVU_11.2.0.1.0_grid/orarun.log
uid=1101(grid) gid=1000(oinstall) groups=1000(oinstall),1001(dba),1002(asmdba),1003(asmadmin),1004(asmoper)
[root@rac1 ~]#Enable file being used is :/tmp/CVU_11.2.0.1.0_grid/fixup.enable
Log file location: /tmp/CVU_11.2.0.1.0_grid/orarun.log
uid=1101(grid) gid=1000(oinstall) groups=1000(oinstall),1001(dba),1002(asmdba),1003(asmadmin),1004(asmoper)
Run root.sh for grid Infrastructure
[root@rac1 ~]#
[root@rac1 ~]# /u01/app/oracle/product/11.2.0/grid/root.sh
[root@rac1 ~]# /u01/app/oracle/product/11.2.0/grid/root.sh
Running Oracle 11g
root.sh script...
The following
environment variables are set as:
ORACLE_OWNER= oracle
Enter the full
pathname of the local bin directory: [/usr/local/bin]:
The file
"dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file
"oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file
"coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
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.sh script.
Now product-specific
root actions will be performed.
2017-12-16 08:45:20:
Checking for super user privileges
2017-12-16 08:45:20:
User has super user privileges
2017-12-16 08:45:20:
Parsing the host name
Using configuration
parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace
directory
LOCAL ADD MODE
Creating OCR keys for
user 'oracle', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node rac1
successfully pinned.
Adding daemon to
inittab
CRS-4123: Oracle High
Availability Services has been started.
ohasd is starting
acfsroot: ACFS-9301:
ADVM/ACFS installation can not proceed:
acfsroot: ACFS-9302:
No installation files found at
/u01/app/oracle/product/11.2.0/grid/install/usm/EL5/x86_64/2.6.18-8/2.6.18-8.el5uek-x86_64/bin.
rac1 2017/12/16 08:45:34
/u01/app/oracle/product/11.2.0/grid/cdata/rac1/backup_20171216_084534.olr
Successfully
configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory
properties for clusterware
Starting Oracle
Universal Installer...
Checking swap space:
must be greater than 500 MB. Actual
29991 MB Passed
The inventory pointer
is located at /etc/oraInst.loc
The inventory is
located at /u01/app/oraInventory
'UpdateNodeList' was
successful.
[root@rac1 ~]#
[root@rac1 ~]# ps -ef |
grep pmon
oracle 10403
1 0 08:36 ? 00:00:00 ora_pmon_DELL
root 13904
4501 0 08:45 pts/1 00:00:00 grep pmon
[root@rac1 ~]#
Check ASM Instance started or not
[root@rac1 ~]#
[root@rac1 ~]# ps -ef |
grep pmon
oracle 10403
1 0 08:36 ? 00:00:00 ora_pmon_DELL
grid 14612
1 0 08:46 ? 00:00:00 asm_pmon_+ASM
root 14733
4501 0 08:48 pts/1 00:00:00 grep pmon
[root@rac1 ~]#
[root@rac1 ~]# su - grid
[grid@rac1 ~]$
[grid@rac1 ~]$ cat asm.env
export ORACLE_SID=+ASM
export ORACLE_BASE=/u01/app/grid
export
ORACLE_HOME=/u01/app/grid/product/11.2.0/grid
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib;
export LD_LIBRARY_PATH
[grid@rac1 ~]$ . asm.env
[grid@rac1 ~]$
[grid@rac1 ~]$ sqlplus / as
sysasm
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 08:50:44 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic
Storage Management option
SQL>
SQL> select name,
state from v$asm_diskgroup;
NAME STATE
------------------------------
-----------
DATA MOUNTED
SQL> col NAME for
a15;
SQL>
SQL> col PATH for
a30;
SQL>
SQL> select NAME,
PATH, HEADER_STATUS from v$asm_disk;
NAME PATH HEADER_STATU
---------------
------------------------------ ------------
DATA_0000 /dev/oracleasm/disks/DELLASM MEMBER
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic
Storage Management option
[grid@rac1 ~]$
Check ASM Disk Status from Database
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$. dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 16 08:56:59 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select NAME,MOUNT_STATUS,TOTAL_MB from v$asm_disk;
NAME MOUNT_S TOTAL_MB
------------------------------ ------- ----------
DATA_0000 CACHED 97629
SQL>
Create Directory for DELL Database files in ASM
[root@rac1 ~]#
[root@rac1 ~]# su - grid
[grid@rac1 ~]$
[grid@rac1 ~]$. asm.env
[grid@rac1 ~]$
[grid@rac1 ~]$ asmcmd
ASMCMD>
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD>
ASMCMD> ls
ASM/
ASMCMD> mkdir DELL
ASMCMD>
ASMCMD> ls
ASM/
DELL/
ASMCMD>
Method 1 : Migration
to ASM by ASMCMD
Change Control file path to ASM DISK
[root@rac1 ~]#
[root@rac1 ~]# ps -ef | grep pmon
oracle 14690 1 0 17:50 ? 00:00:00 ora_pmon_DELL
grid 16230 1 0 17:57 ? 00:00:00 asm_pmon_+ASM
root 16351 15353 0 17:58 pts/4 00:00:00 grep pmon
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$. dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 08:56:59 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name
from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/control01.ctl
/u01/app/oracle/DELL/control02.ctl
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL> alter system
set control_files='+DATA/DELL/control01.ctl','+DATA/DELL/control02.ctl' scope =
spfile;
System altered.
SQL>
SQL> shut
immediate
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$
Copy C-R-D files to ASM
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ . asm.env
[grid@rac1 ~]$ asmcmd
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/control01.ctl
+DATA/DELL/control01.ctl
copying
/u01/app/oracle/DELL/control01.ctl -> +DATA/DELL/control01.ctl
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/control02.ctl +DATA/DELL/control02.ctl
copying
/u01/app/oracle/DELL/control02.ctl -> +DATA/DELL/control02.ctl
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/users01.dbf
+DATA/DELL/users01.dbf
copying /u01/app/oracle/DELL/users01.dbf
-> +DATA/DELL/users01.dbf
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/undotbs01.dbf
+DATA/DELL/undotbs01.dbf
copying
/u01/app/oracle/DELL/undotbs01.dbf -> +DATA/DELL/undotbs01.dbf
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/system01.dbf +DATA/DELL/system01.dbf
copying
/u01/app/oracle/DELL/system01.dbf -> +DATA/DELL/system01.dbf
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/sysaux01.dbf
+DATA/DELL/sysaux01.dbf
copying
/u01/app/oracle/DELL/sysaux01.dbf -> +DATA/DELL/sysaux01.dbf
ASMCMD>
ASMCMD> cp /u01/app/oracle/DELL/redo01.log +DATA/DELL/redo01.log
copying
/u01/app/oracle/DELL/redo01.log -> +DATA/DELL/redo01.log
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/redo02.log
+DATA/DELL/redo02.log
copying
/u01/app/oracle/DELL/redo02.log -> +DATA/DELL/redo02.log
ASMCMD>
ASMCMD> cp
/u01/app/oracle/DELL/redo03.log
+DATA/DELL/redo03.log
copying
/u01/app/oracle/DELL/redo03.log -> +DATA/DELL/redo03.log
ASMCMD>
ASMCMD>
ASMCMD> ls
ASM/
DELL/
ASMCMD>
ASMCMD> cd DELL
ASMCMD>
ASMCMD> ls
control01.ctl
control02.ctl
redo01.log
redo02.log
redo03.log
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf
ASMCMD>
Now Change the dbf files in SQL DATABASE
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 09:12:53 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup
mount
ORACLE instance
started.
Total System Global
Area 1027182592 bytes
Fixed Size 2219792 bytes
Variable Size 595591408 bytes
Database Buffers 423624704 bytes
Redo Buffers 5746688 bytes
Database mounted.
SQL>
SQL> select name
from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/dell/control01.ctl
+DATA/dell/control02.ctl
SQL>
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/users01.dbf' to
'+DATA/DELL/users01.dbf';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/undotbs01.dbf' to
'+DATA/DELL/undotbs01.dbf';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/system01.dbf' to
'+DATA/DELL/system01.dbf';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/sysaux01.dbf' to
'+DATA/DELL/sysaux01.dbf';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/redo03.log' to
'+DATA/DELL/redo03.log';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/redo02.log' to
'+DATA/DELL/redo02.log';
Database altered.
SQL>
SQL> alter
database RENAME file '/u01/app/oracle/DELL/redo01.log' to
'+DATA/DELL/redo01.log';
Database altered.
SQL>
SQL>
SQL> select name
from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/dell/system01.dbf
+DATA/dell/sysaux01.dbf
+DATA/dell/undotbs01.dbf
+DATA/dell/users01.dbf
SQL>
SQL> select
member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/dell/redo03.log
+DATA/dell/redo02.log
+DATA/dell/redo01.log
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/temp01.dbf
SQL>
SQL> alter
database open;
Database altered.
SQL>
Change the temp tablespace to ASM
SQL>
SQL> alter
tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/temp01.dbf
+DATA/dell/tempfile/temp.265.962875107
SQL>
SQL> alter
tablespace temp drop tempfile '/u01/app/oracle/DELL/temp01.dbf';
Tablespace altered.
SQL>
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/dell/tempfile/temp.265.962875107
SQL>
Now let’s Migrate SPfile to ASM.
SQL>
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL> create pfile
from spfile;
File created.
SQL>
SQL> create
spfile = '+DATA' from pfile;
File created.
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
[oracle@rac1 dbs]$
Check SPfile name in ASM along with numbers
[root@rac1 ~]#
[root@rac1 ~]# su - grid
[grid@rac1 ~]$
[grid@rac1 ~]$. asm.env
[grid@rac1 ~]$
[grid@rac1 ~]$ asmcmd
ASMCMD>
ASMCMD> cd
DATA/DELL/PARAMETERFILE/
ASMCMD>
ASMCMD> ls
spfile.266.962875261
ASMCMD>
Now let’s change the Pfile with an entry pointing to the new location of
SPfile in ASM
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$. dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd
$ORACLE_HOME/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ mv
spfileDELL.ora spfileDELL.ora_bkp
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cat
initDELL.ora
spfile='+DATA/DELL/PARAMETERFILE/spfile.266.962875261'
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 09:31:59 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
SQL> startup
force;
ORACLE instance
started.
Total System Global
Area 1027182592 bytes
Fixed Size 2219792 bytes
Variable Size 595591408 bytes
Database Buffers 423624704 bytes
Redo Buffers 5746688 bytes
Database mounted.
Database opened.
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string +DATA/dell/parameterfile/spfil
e.266.962875261
SQL>
Check ASM Disk Status
SQL>
SQL>
SQL> select NAME,MOUNT_STATUS,TOTAL_MB from v$asm_disk;
NAME MOUNT_S TOTAL_MB
------------------------------ ------- ----------
DATA_0000 CACHED 97629
SQL>
Method 2 : Migration
to ASM by RMAN
Enable archive log with SPfile.
[root@rac1 ~]#
[root@rac1 ~]# ps -ef |
grep pmon
oracle 14690
1 0 17:50 ? 00:00:00 ora_pmon_DELL
grid 16230
1 0 17:57 ? 00:00:00 asm_pmon_+ASM
root 16351 15353 0 17:58 pts/4 00:00:00 grep pmon
[root@rac1 ~]#
[root@rac1 ~]# chmod -R 775 /u01
[root@rac1 ~]#
[root@rac1 ~]# chmod -R 775 /u01
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/DELL/arch
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/DELL/arch
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 11:58:35 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select name,
open_mode from v$database;
NAME OPEN_MODE
---------
--------------------
DELL READ WRITE
SQL>
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL> alter system
set log_archive_dest='/u01/app/oracle/DELL/arch' scope=spfile;
System altered.
SQL>
SQL> shut
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL>
SQL> startup
mount
ORACLE instance
started.
Total System Global
Area 993775616 bytes
Fixed Size 2219472 bytes
Variable Size 578814512 bytes
Database Buffers 406847488 bytes
Redo Buffers 5894144 bytes
Database mounted.
SQL>
SQL> alter
database archivelog;
Database altered.
SQL>
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 1
Next log sequence to
archive 2
Current log
sequence 2
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL>
Shift the Controlfile parameter to ASM
Shift the Controlfile parameter to ASM
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/control01.ctl
/u01/app/oracle/DELL/control02.ctl
SQL>
SQL>
SQL> alter system
set control_files='+DATA/DELL/control01.ctl','+DATA/DELL/control02.ctl' scope =
spfile;
System altered.
SQL> alter system
set db_create_file_dest='+DATA' scope=spfile;
System altered.
SQL> shut
immediate
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL>
SQL> startup
nomount
ORACLE instance
started.
Total System Global
Area 993775616 bytes
Fixed Size 2219472 bytes
Variable Size 578814512 bytes
Database Buffers 406847488 bytes
Redo Buffers 5894144 bytes
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$
[oracle@rac1 ~]$ rman target
/
Recovery Manager:
Release 11.2.0.1.0 - Production on Sun Dec 17 09:43:20 2017
Copyright (c) 1982,
2009, Oracle and/or its affiliates. All
rights reserved.
connected to target
database: DELL (not mounted)
RMAN>
RMAN> restore
controlfile from '/u01/app/oracle/DELL/control01.ctl';
Starting restore at
17-DEC-17
using target database
control file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
SID=23 device type=DISK
channel ORA_DISK_1:
copied control file copy
output file
name=+DATA/dell/control01.ctl
output file
name=+DATA/dell/control02.ctl
Finished restore at
17-DEC-17
RMAN>
RMAN> alter
database mount;
database mounted
released channel:
ORA_DISK_1
RMAN>
RMAN> backup as
copy database format '+DATA';
Starting backup at
17-DEC-17
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
SID=23 device type=DISK
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00001 name=/u01/app/oracle/DELL/system01.dbf
output file
name=+DATA/dell/datafile/system.258.962963133 tag=TAG20171217T094533 RECID=1
STAMP=962963149
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00002 name=/u01/app/oracle/DELL/sysaux01.dbf
output file
name=+DATA/dell/datafile/sysaux.259.962963159 tag=TAG20171217T094533 RECID=2
STAMP=962963168
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00003 name=/u01/app/oracle/DELL/undotbs01.dbf
output file
name=+DATA/dell/datafile/undotbs1.260.962963173 tag=TAG20171217T094533 RECID=3
STAMP=962963174
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1:
starting datafile copy
copying current
control file
output file
name=+DATA/dell/controlfile/backup.261.962963175 tag=TAG20171217T094533 RECID=4
STAMP=962963175
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/DELL/users01.dbf
output file
name=+DATA/dell/datafile/users.262.962963177 tag=TAG20171217T094533 RECID=5
STAMP=962963176
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 17-DEC-17
channel ORA_DISK_1:
finished piece 1 at 17-DEC-17
piece
handle=+DATA/dell/backupset/2017_12_17/nnsnf0_tag20171217t094533_0.263.962963177
tag=TAG20171217T094533 comment=NONE
channel ORA_DISK_1:
backup set complete, elapsed time: 00:00:01
Finished backup at
17-DEC-17
RMAN>
RMAN> switch
database to copy;
datafile 1 switched to
datafile copy "+DATA/dell/datafile/system.258.962963133"
datafile 2 switched to
datafile copy "+DATA/dell/datafile/sysaux.259.962963159"
datafile 3 switched to
datafile copy "+DATA/dell/datafile/undotbs1.260.962963173"
datafile 4 switched to
datafile copy "+DATA/dell/datafile/users.262.962963177"
RMAN>
RMAN> alter
database open;
database opened
RMAN>
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- --
---------- ----------- ------------ ---------------
1 Full
80.00K DISK 00:00:00 17-DEC-17
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20171217T081855
Piece Name:
+DATA/dell/backupset/2017_12_17/nnsnf0_tag20171217t081855_0.263.963044371
SPFILE Included: Modification time: 17-DEC-17
SPFILE db_unique_name: DELL
RMAN>
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 17-DEC-17 1 1 NO TAG20171217T081855
RMAN>
RMAN> exit
Recovery Manager
complete.
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sun Dec 17 09:47:45 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
SQL>
SQL>
SQL> select NAME,
CONTROLFILE_TYPE, open_mode from v$database;
NAME CONTROL OPEN_MODE
--------- -------
--------------------
DELL CURRENT READ WRITE
SQL>
SQL> select name
from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/dell/control01.ctl
+DATA/dell/control02.ctl
SQL>
SQL> select
file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/dell/datafile/users.262.962963177
+DATA/dell/datafile/undotbs1.260.962963173
+DATA/dell/datafile/sysaux.259.962963159
+DATA/dell/datafile/system.258.962963133
SQL>
SQL> select
member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/redo03.log
/u01/app/oracle/DELL/redo02.log
/u01/app/oracle/DELL/redo01.log
SQL>
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/temp01.dbf
Now add temp datafile to ASM and then drop the old one
SQL>
SQL> alter
tablespace temp add tempfile size 500M;
Tablespace altered.
SQL>
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/DELL/temp01.dbf
+DATA/dell/tempfile/temp.264.962963569
SQL>
SQL> alter database tempfile '/u01/app/oracle/DELL/temp01.dbf' drop
including datafiles;
Database altered.
SQL>
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/dell/tempfile/temp.264.962963569
Now add online redo logs to ASM and delete the old(INACTIVE)
SQL>
SQL> col MEMBER
for a45
SQL> SELECT *
FROM V$LOGFILE;
GROUP# STATUS TYPE
MEMBER
IS_
---------- -------
------- ---------------------------------------- ---
3 ONLINE
/u01/app/oracle/DELL/redo03.log
NO
2 ONLINE
/u01/app/oracle/DELL/redo02.log
NO
1 ONLINE
/u01/app/oracle/DELL/redo01.log
NO
SQL>
SQL> select
GROUP#, MEMBERS, BYTES, STATUS from v$log;
GROUP#
MEMBERS BYTES STATUS
---------- ----------
---------- ----------------
1 1
52428800 INACTIVE
2 1
52428800 INACTIVE
3 1
52428800 CURRENT
SQL>
SQL> alter
database add logfile '+DATA' size 50M;
Database altered.
SQL>
SQL> alter
database add logfile '+DATA' size 50M;
Database altered.
SQL>
SQL> alter
database add logfile '+DATA' size 50M;
Database altered.
SQL>
SQL> SELECT *
FROM V$LOGFILE;
GROUP# STATUS TYPE
MEMBER IS_
---------- -------
------- --------------------------------------------- ---
3 ONLINE
/u01/app/oracle/DELL/redo03.log NO
2 ONLINE
/u01/app/oracle/DELL/redo02.log NO
1 ONLINE
/u01/app/oracle/DELL/redo01.log NO
4 ONLINE
+DATA/dell/onlinelog/group_4.265.963047411 NO
5 ONLINE
+DATA/dell/onlinelog/group_5.266.963047413 NO
6 ONLINE +DATA/dell/onlinelog/group_6.267.963047415 NO
6 rows selected.
SQL>
SQL> select
GROUP#, MEMBERS, BYTES, STATUS from v$log;
GROUP#
MEMBERS BYTES STATUS
---------- ----------
---------- ----------------
1 1
52428800 INACTIVE
2 1
52428800 INACTIVE
3 1
52428800 CURRENT
4 1
52428800 UNUSED
5 1
52428800 UNUSED
6 1
52428800 UNUSED
6 rows selected.
SQL>
SQL> alter database
drop logfile group 1;
Database altered.
SQL>
SQL> alter
database drop logfile group 2;
Database altered.
SQL>
SQL> alter system
switch logfile;
System altered.
SQL>
SQL> select GROUP#,
MEMBERS, BYTES, STATUS from v$log;
GROUP#
MEMBERS BYTES STATUS
---------- ----------
---------- ----------------
3 1
52428800 ACTIVE
4 1
52428800 CURRENT
5 1
52428800 UNUSED
6 1
52428800 UNUSED
SQL>
SQL> alter
database drop logfile group 3;
alter database drop
logfile group 3
*
ERROR at line 1:
ORA-01624: log 3
needed for crash recovery of instance DELL (thread 1)
ORA-00312: online log
3 thread 1: '/u01/app/oracle/DELL/redo03.log'
SQL>
SQL> select GROUP#,
MEMBERS, BYTES, STATUS from v$log;
GROUP#
MEMBERS BYTES STATUS
---------- ----------
---------- ----------------
3 1
52428800 INACTIVE
4 1
52428800 CURRENT
5 1
52428800 UNUSED
6 1
52428800 UNUSED
SQL>
SQL> alter
database drop logfile group 3;
Database altered.
SQL>
SQL> SELECT *
FROM V$LOGFILE;
GROUP# STATUS TYPE
MEMBER IS_
---------- -------
------- --------------------------------------------- ---
4 ONLINE
+DATA/dell/onlinelog/group_4.265.963047411 NO
5 ONLINE
+DATA/dell/onlinelog/group_5.266.963047413 NO
6 ONLINE
+DATA/dell/onlinelog/group_6.267.963047415 NO
SQL>
Now let’s Migrate SPfile to ASM.
SQL>
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL> create pfile
from spfile;
File created.
SQL>
SQL> create
spfile = '+DATA' from pfile;
File created.
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
[oracle@rac1 dbs]$
Check SPfile name in ASM along with numbers
[root@rac1 ~]#
[root@rac1 ~]# su - grid
[grid@rac1 ~]$
[grid@rac1 ~]$. asm.env
[grid@rac1 ~]$
[grid@rac1 ~]$ asmcmd
ASMCMD>
ASMCMD> cd
DATA/DELL/PARAMETERFILE/
ASMCMD>
ASMCMD> ls
spfile.232.962875232
ASMCMD>
Now let’s change the Pfile with an entry pointing to the new location of
SPfile in ASM
[root@rac1 ~]#
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$. dell.env
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd
$ORACLE_HOME/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ mv
spfileDELL.ora spfileDELL.ora_bkp
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cat
initDELL.ora
spfile='+DATA/DELL/PARAMETERFILE/spfile.232.962875232'
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ sqlplus / as
sysdba
SQL*Plus: Release
11.2.0.1.0 Production on Sat Dec 16 09:31:59 2017
Copyright (c) 1982,
2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning,
Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
SQL> startup
force;
ORACLE instance
started.
Total System Global
Area 1027182592 bytes
Fixed Size 2219792 bytes
Variable Size 595591408 bytes
Database Buffers 423624704 bytes
Redo Buffers 5746688 bytes
Database mounted.
Database opened.
SQL> show
parameter spfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string +DATA/dell/parameterfile/spfil
e.232.962875232
SQL>
Check ASM Disk Status
SQL>
SQL> select
NAME,MOUNT_STATUS,TOTAL_MB from v$asm_disk;
NAME MOUNT_S TOTAL_MB
------------------------------
------- ----------
DATA_0000 CACHED 97629
SQL>
Thus we have
successfully migrated all the control files, datafiles, online redo log
files, tempfiles, and SPfile for the
database “DELL” from local file system to ASM using ASMCMD & RMAN.
This Article explains how to Migrate ORACLE DATABASE
from file System to ASM in Linux Environment.
Thanks for Reading
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux
|
DATABASE
|
RMAN
|
RAC
|
EBS
|
EBS DataGuard
| ||||
Asakwrb areef brother,
ReplyDeleteSmall correction:
Now add temp datafile to ASM and then drop the old one
SQL>
SQL> alter tablespace temp add tempfile size 500M;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now add temp datafile to ASM and then drop the old one
SQL>
SQL> alter tablespace temp add tempfile '+DATA' size 500m;