Migrating 11gR2 Database to and from ASM

In this post i am migrating Oracle 11gR2 database from ASM to NON-ASM and NON-ASM to ASM storage.

NON-ASM to ASM
==============
Steps to migrate Non-ASM database and fast recovery area to ASM database using RMAN.
1) Backup database,spfile and disable flashback database
1.1) Lets assume we have two disk groups +ORADATA for database and +ORAFRA for fast recovery area.
1.2) Copy the spfile cp spfiletest.ora spfiletest.ora.org
1.3) start RMAN session in new window to the database to be migrated
rman target / nocatalog
1.4) Backup data files to Oracle ASM diskgroup (+ORADATA)
rman> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+ORADATA'
TAG 'ASM_MIGRATION';
}
1.5) Check if block change tracking is enabled.
sql> select filename, status, bytes from v$block_change_tracking
if it is enabled then disable it.
rman> sql "alter database disable block change tracking";
1.6) archive the online logs if the database is in archivelog mode
rman> sql "ALTER SYSTEM ARCHIVE LOG CURRENT";
1.7) Backup spfile
rman> backup as backupset spfile;
1.8) check if flash back database is enabled
sql> select flashback_on from v$database;
if it is enabled then disabled it
rman> sql "alter database flashback off";
rman> shutdown immediate
2) Restore files to oracle ASM and recover database and migrate fast recovery area.
2.1) restore spfile to ASM disk group
rman> startup mount;
rman> restore spfile to '+ORADATA/spfiletest.ora';
rman> shutdown immediate
2.2) set oracle managed files to Oracle ASM locations
sql> startup nomount;
sql> alter system set db_create_file_dest='+ORADATA' sid='*';
sql> alter system set db_recovery_file_dest_size=10g sid='*';
sql> alter system set db_recovery_file_dest='+ORAFRA' sid='*';
sql> shutdown immediate
2.3) set the control_files location to ASM location
sql> startup nomount;
sql> alter system set control_files='+ORADATA','+ORAFRA' scope=spfile sid='*';
sql> Shutdown immediate;
rman> startup nomount
rman> restore contolfile from '/apps/oracle/oradata/test/control01.ctl';
rman> alter database mount;
2.4) Migrate data files to Oracle ASM
rman> switch database to copy;
rman>
run
{
allocate channel dev1 device type disk;
allocate channel dev2 device type disk;
allocate channel dev3 device type disk;
allocate channel dev4 device type disk;
recover database;
}
2.5) Enable block changes or flashback database if it was enabled.
sql> alter database enable block change tracking using file '+ORADATA';
sql> alter database flashback on;
2.6) open database
sql> alter database open;
2.5) Move Redologs to ASM.use below procedure
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/

ASM to Non-ASM
==============
To be Continued ...... ut quick hint in the above procedure reverse the order.

Oracle ASMLIB configuration - 11gR2

There are 3 storage options to setup ASM on Linux
1) Raw devices
2) ASMlib
3) Block devices
In this post i have focused on ASMlib which is oracle's recomended storage options. I am on Redhat Linux AS 4 ( 2.6.9-78.ELsmp)

The very first thing we need to do is identify the partition which can be used for asm storage, i have used local drive and created partition using
fdisk /dev/sda
Note: do not create any file system on the partition

once the partition's are created you can download the asmlib rpm's from oracle.
check the Linux release and download asmlib drivers
uname -rm
2.6.9-78.ELsmp x86_64
and download drivers from oracle site, match the above release http://www.oracle.com/technology/software/tech/linux/asmlib/rhel4.html

Example:
oracleasm-2.6.9-78.ELsmp-2.0.5-1.el4
We have to download three rpm's and apply in same order
1)oracleasm-support-2.1.3-1.el4.x86_64.rpm
2)oracleasm-2.6.9-78.ELsmp-2.0.5-1.el4.x86_64.rpm
3)oracleasmlib-2.0.4-1.el4.x86_64.rpm

Once rpm's are installed verfiy
rpm -qa | grep oracleasm -- should return above three

Once ASMLIB software is installed make asm driver available by below commands, this will create /dev/oracleasm mount point
#/etc/init.d/oracleasm configure
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 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 [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

Check status
#/etc/init.d/oracleasm

Next, use below create disk to place the disk in asm management
#/etc/init.d/oracleasm createdisk ORADATA /dev/sda6
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

#df -ha must show
oracleasmfs 0 0 0 - /dev/oracleasm

check if the disks created above can be discovered,
#/usr/sbin/oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:ORADATA [230468427 blocks (117999834624 bytes), maxio 256]

Install ASM from grid infrastructure home.Download grid control from below link for 11gR2 and install ASM.

http://www.oracle.com/technology/software/products/database/oracle11g/112010_linx8664soft.html

Re-Org Tablespace with Lob Columns

It is often seen that the tables with LOB columns consume space much more then actual data,there are ways to reclaim wasted space

1) import/export
2) datapump which is faster then imp/exp and more flxible
3) moving LOB objects to different tablespace.

In this article i will re-org using temporary tablespace and moving it back to original tabespace.

First we need to identify the lob columns and space consumed

select dl.table_name,dl.column_name,dl.segment_name,ds.segment_type,
ds.bytes,ds.blocks
from dba_lobs dl, dba_segments ds
where ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
order by bytes desc

Generate script, here i have created lob_ts_temp tablespace with nologging and autoextended on , spool it to a file and execute

select 'alter table ' || dl.owner ||'.'|| dl.table_name || ' move lob (' || column_name || ') store as (tablespace lobs_ts_temp);'
from
dba_lobs dl,
dba_segments ds
where 1=1
and ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
and dl.tablespace_name='LOBS_TS';

execute the script generated by the above sql, once the script is finished check the space again and you will see how much space was wasted.

select dl.table_name,dl.column_name,dl.segment_name,ds.segment_type,
ds.bytes,ds.blocks
from dba_lobs dl, dba_segments ds
where ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
order by bytes desc;

Before dropping make sure that the tablespace is empty,
select * from dba_tablespace_usage_metrics where tablespace_name='LOBS_TS';

You can drop original tablespace using below command
drop tablespace lob_ts including contents and datafile;

Now, if you want you can re-create original tablespace and move the objects back or you can keep the same tablespace, but you have to plan earlier whether you want to re-create original tablespace or you want to move and keep the objects in new tablespace, create new or temporary tablespace as per requirement.

Moving tablespace makes indexes unuseable so identify and rebuild indexes, use below query to generate the script

select 'alter index ' || owner ||'.'||index_name || ' rebuild online parallel 2;' from dba_indexes
where status='INVALID'

I hope this article will be useful, please comment.

Oracle DB Installation Help

http://www.puschitz.com/InstallingOracle10g.shtml