Move database from local file system to shared ASM diskgroups

We have a single instance database ‘rac10g’ created on local file system and plan to migrate it to a RAC system using ASM.

At this point, We’ve installed clusterware 10.2.0.5 on all cluster nodes and a shared ASM system with diskgroups DATA and FRA.

The article demostrates steps to migrate database from local file system to clustered ASM storage.

Step 1) Create a pfile and locate control file to ASM diskgroups

SQL> create pfile from spfile;

vi initrac10g.ora

*.control_files=’+DATA’,’+DATA’,’+FRA’   # 2copies in DATA and 1 copy in FRA

Step 2) startup nomount

SQL> startup nomount pfile=’initrac10g.ora’

Step 3) connect target db from RMAN

RMAN> connect target /

Step 4) Restore control file to shared ASM storage

RMAN> restore controlfile from ‘/app/oracle/oradata/rac10g/contorl01.ctl’;

channel ORA_DISK_1: copied control file copy
output filename=+DATA/rac10g/controlfile/current.256.760821943
output filename=+DATA/rac10g/controlfile/current.257.760821947
output filename=+FRA/rac10g/controlfile/current.256.760821953
.

Note if you meet errors ORA-15077 as I did, refer to troubleshoot section in the end of this article for solution.
ORA-15001: diskgroup “DATA” does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup

Step 5) Mount the database

RMAN> sql ‘alter database mount’;

Step 6) Copy datafiles to ASM diskgroup ‘DATA’

RMAN> backup as copy database format ‘+DATA’;

Step 7)  Update controlfile with datafile copies

RMAN> switch database to copy;

Step 8 )  Update controlfile with tempfile copy

RMAN> run {
set newname for tempfile 1 to ‘+DATA’;
set newname for tempfile … to ‘+DATA’;
switch tempfile all;
}

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file

Step 9) open the database

RMAN> sql ‘alter database open’;

Step 10) Move redo log files to ASM diskgroups

sys@rac10g> select member, l.status, l.group# from v$log l, v$logfile lf where lf.group# = l.group#;

MEMBER                                   STATUS               GROUP#
—————————————- —————- ———-
/app/oracle/oradata/rac10g/redo03.log    CURRENT                   3
/app/oracle/oradata/rac10g/redo02.log    INACTIVE                  2
/app/oracle/oradata/rac10g/redo01.log    INACTIVE                  1

— add logfile members to ‘DATA’ and “FRA’

SQL> alter database add logfile member ‘+DATA’, ‘+FRA’ to group 1;

sys@rac10g> select member, l.status, l.group#, l.archived from v$log l, v$logfile lf where lf.group# = l.group#;

MEMBER                                             STATUS               GROUP# ARC
————————————————– —————- ———- —
/app/oracle/oradata/rac10g/redo03.log              CURRENT                   3 NO
/app/oracle/oradata/rac10g/redo02.log              INACTIVE                  2 YES
/app/oracle/oradata/rac10g/redo01.log              INACTIVE                  1 YES
+DATA/rac10g/onlinelog/group_1.264.760836295       INACTIVE                  1 YES
+FRA/rac10g/onlinelog/group_1.257.760836301        INACTIVE                  1 YES

— drop old logfile member from local filesystem

sys@rac10g> alter database drop logfile member ‘/app/oracle/oradata/rac10g/redo01.log’;
alter database drop logfile member ‘/app/oracle/oradata/rac10g/redo01.log’
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 1
ORA-01517: log member: ‘/app/oracle/oradata/rac10g/redo01.log’

sys@rac10g> alter database clear logfile group 1;

Database altered.

sys@rac10g> alter database drop logfile member ‘/app/oracle/oradata/rac10g/redo01.log’;

Database altered.

Repeat Step 10) for all the other logfile groups, if necessary force log switches and checkpoints;

SQL> alter system switch logfile;
or
SQL> alter system checkpoint;

Finally we have all datafiles, tempfiles, controlfiles and redo log files on ASM


sys@rac10g> select file_name from dba_data_files
2    union all
3* select file_name from dba_temp_files

FILE_NAME
------------------------------------------------------------
+DATA/rac10g/datafile/users.261.760835151
+DATA/rac10g/datafile/sysaux.260.760835115
+DATA/rac10g/datafile/undotbs1.259.760835071
+DATA/rac10g/datafile/system.256.760835015
+DATA/rac10g/tempfile/temp.263.760835669

sys@rac10g> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA/rac10g/controlfile/curre
nt.258.760834963, +DATA/rac10g
/controlfile/current.257.76083
4965, +FRA/rac10g/controlfile/
current.256.760834967

sys@rac10g> select member, l.status, l.group#, l.archived from v$log l, v$logfile lf where lf.group# = l.group# order by group#;

MEMBER                                             STATUS               GROUP# ARC
-------------------------------------------------- ---------------- ---------- ---
+DATA/rac10g/onlinelog/group_1.264.760836295       CURRENT                   1 NO
+FRA/rac10g/onlinelog/group_1.257.760836301        CURRENT                   1 NO
+FRA/rac10g/onlinelog/group_2.258.760837077        ACTIVE                    2 YES
+DATA/rac10g/onlinelog/group_2.265.760837071       ACTIVE                    2 YES
+FRA/rac10g/onlinelog/group_3.259.760837185        ACTIVE                    3 YES
+DATA/rac10g/onlinelog/group_3.266.760837179       ACTIVE                    3 YES

Step 11) replace control_files settings with real controlfiles and create spfile

vi initrac10g.ora

control_files=’+DATA/rac10g/controlfile/current.258.760834963′, ‘+DATA/rac10g/controlfile/current.257.760834965’, ‘+FRA/rac10g/controlfile/current.256.760834967’

otherwise you can create alias for the controlfiles

SQL> create spfile=’+DATA/RAC10G/PARAMETERFILE/spfilerac11g.ora’ from pfile;

vi initrac10g.ora

spfile=’+DATA/RAC10G/PARAMETERFILE/spfilerac11g.ora’

SQL>shutdown immediate;
SQL> startup

Done!

Troubleshooting

Problem ORA-15077 and ORA-15001
================================

at step 4) when restoring contorlfile to ASM I got this error

RMAN> restore controlfile from ‘/app/oracle/oradata/rac10g/control01.ctl’;

Starting restore at 02-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/02/2011 18:05:34
ORA-19504: failed to create file “+DATA”
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15001: diskgroup “DATA” does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-19600: input file is control file  (/app/oracle/oradata/rac10g/control01.ctl)
ORA-19601: output file is control file  (+DATA)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Analysis
=========

1) check if CRS is running on the node

[oracle@guang ]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

2) check if ASM instance is running

sys@+asm> select status from v$instance;

STATUS
————
STARTED

ASM instance is always in “STARED” mode, that is different from normal instance in “OPEN” “MOUNT” and “STARTED” modes.

sys@+asm> select name, state from v$asm_diskgroup;

NAME                           STATE
—————————— ———–
DATA                           DISMOUNTED
FRA                            DISMOUNTED

Problem is here !!!

sys@+asm> show parameter asm_diskgroups;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
asm_diskgroups                       string

Cause
======

The diskgroups are dismounted when trying to restore control file to them. Since the asm_diskgroups string is empty
so that diskgroups are not mounted automatically at instance startup.

Solution
=========

1) manually mount the diskgroups

alter diskgroup data mount;
alter diskgroup fra mount;

2) set asm_diskgroups string to “DATA, FRA” so that ASM instance will mount them automatically at next startp

alter system set asm_diskgroups=”DATA, FRA” sid=”*”;
shutdown immediate; — need to shutdown connected instance if any
startup
select name, state from v$asm_diskgroup;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: