Migrate Oracle Database from File System to ASM

Configure parameter file / spfile
alter system set control_files=’+DATA’ scope=spfile;
alter system set db_create_file_dest=’+DATA’ scope=spfile;
alter system set db_recovery_file_dest=’+DATA’ scope=spfile;
alter system set db_recovery_file_dest_size=3G scope=spfile;

RMAN> startup nomount;
restore controlfile from ‘/u01/oracle/VIS/db/apps_st/data/cntrl01.dbf’;

RMAN> ALTER DATABASE MOUNT;

RMAN> configure device type disk parallelism 4;

:: Backup original database (still in file system) ::
RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA’;

RMAN> SWITCH DATABASE TO COPY;

RMAN> alter database open;

RMAN> exit

sqlplus “/as sysdba”

col name for a45
select name, bytes/1024/1024 “SizeGB” from v$tempfile;

create temporary tablespace temp01 tempfile SIZE 1000M extent management local uniform size 1M;

alter database default temporary tablespace temp01;

alter database tempfile ‘/u01/oracle/VIS/db/apps_st/data/temp2_01.dbf’ drop including datafiles;

Drop and recreate the redo groups into ASM to migrate them to ASM Diskgroup.
SQL> select member from v$logfile;

col member for a50
select a.group#, a.member, b.bytes/1024/1024 “SizeMB”
from v$logfile a, v$log b where a.group# = b.group#;

Force a log switch until the last redo log is marked “CURRENT” by issuing the following command:
SQL> select group#, status from v$log;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#, status from v$log;

SQL> select group#, status from v$log;
GROUP# STATUS
—— —————-
1 INACTIVE
2 INACTIVE
3 CURRENT

After making the last online redo log file the CURRENT one, drop the first online
redo log:
SQL> alter database drop logfile group 3;

Re-create the dropped redo log group in ASM (and a different size if desired):
SQL> alter database add logfile group 3 size 300M;

After re-creating the online redo log group, force a log switch. The online redo
log group just created should become the CURRENT one:

SQL> select group#, status from v$log;
GROUP# STATUS
—— ——–
1 UNUSED
2 INACTIVE
3 CURRENT

SQL> alter system switch logfile;
SQL> select group#, status from v$log;
GROUP# STATUS
—— —————-
1 CURRENT
2 INACTIVE
3 ACTIVE

col member for a50
select a.group#, a.member, b.bytes/1024/1024 “SizeMB”
from v$logfile a, v$log b where a.group# = b.group#;

:: Delete old datafiles using RMAN ::
RMAN> connect target

RMAN> run {
DELETE COPY OF DATABASE;
}

:: Remove the old online redo logs manually ::
rm /home/oracle/oradata/db10g/redo*.log

Advertisements

How to Create ASM Instance and Disk Groups Manually

Creating ASM Instance and Diskgroup manually without dbca:
Configure the Disk Device(s) that will be used in ASM diskgroup (stamping devises as an ASM disks)

:: Format the disks as root user ::
/dev/sdb, /dev/sdc, /dev/sdd, /dev/sde
:: Create ASM disks ::
[root@host]# oracleasm createdisk DATA01 /dev/sdb1
Marking disk “/dev/sdb1” as an ASM disk: [ OK ]

[root@host]# oracleasm createdisk DATA02 /dev/sdc1
Marking disk “/dev/sdc1” as an ASM disk: [ OK ]

[root@host]# oracleasm createdisk DATA03 /dev/sdd1
Marking disk “/dev/sdd1” as an ASM disk: [ OK ]

[root@host]# oracleasm createdisk DATA04 /dev/sde1
Marking disk “/dev/sde1” as an ASM disk: [ OK ]

[root@host]# /etc/init.d/oracleasm listdisks
DATA01
DATA02
DATA03
DATA04

:: Create the password file ::
[oracle@host ~]$ orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=changeIt entries=3

:: Create required directories ::
[oracle@host ~]$ mkdir -p $ORACLE_BASE/admin/+ASM
[oracle@host ~]$ cd $ORACLE_BASE/admin/+ASM
[oracle@host +ASM]$ mkdir bdump
[oracle@host +ASM]$ mkdir udump
[oracle@host +ASM]$ mkdir cdump
[oracle@host +ASM]$ mkdir pfile

:: Create the init+ASM.ora file ::
Using vi editor or any other editor you like, create the init+ASM.ora file under the $ORACLE_HOME/dbs
directory and add the below lines into this file.
asm_diskgroups=’DATA’
asm_diskstring=’ORCL:*’
background_dump_dest=’/u01/app/admin/+ASM/bdump’
core_dump_dest=’/u01/app/admin/+ASM/cdump’
instance_type=’asm’
large_pool_size=12M
remote_login_passwordfile=’SHARED’
user_dump_dest=’/u01/app/admin/+ASM/udump’
:: Create Cluster Syncronization Service (CSS) as root ::

[root@host]# /u01/oracle/VIS/db/tech_st/11.1.0/bin/localconfig add

[oracle@shree ~]$
Create spfile+ASM.ora and start the instance using that file:

[oracle@shree ~]$ export ORACLE_SID=+ASM
[oracle@shree ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 4 21:17:35 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate

SQL> startup mount
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DATA”

SQL> show parameter disk
NAME TYPE VALUE
——————– ——– ————-
asm_diskgroups string DATA
asm_diskstring string ORCL:*
disk_asynch_io boolean TRUE

CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK
‘ORCL:DATA01’ name DATA_001,
‘ORCL:DATA02’ name DATA_002,
‘ORCL:DATA03’ name DATA_003,
‘ORCL:DATA04’ name DATA_004,
‘ORCL:DATA05’ name DATA_005
;

Diskgroup created.

Open the /etc/oratab file and add the following line at the end:
+ASM:/u01/app/oracle/product/11.1.0/db_1:Y

Move LOB segments with or without the table

Move LOB segments with or without the table

– Get the table_name in which the LOB resides
SELECT table_name, column_name, segment_name, tablespace_name from all_lobs where owner = ‘OWNER’;

– Move only LOB segment to a new tablespace
ALTER TABLE table_name MOVE LOB (lob_column_name) STORE AS (TABLESPACE new_tablespace_name);

– Move table with LOB segment
ALTER TABLE table_name MOVE TABLESPACE new_tablespace LOB (lob_column_name) STORE AS (TABLESPACE new_tablespace);