DBA: migrate database to ASM


14.05.2015
by Kamil Stawiarski

Let’s try to migrate database to ASM diskgroup without big maintenance window.

I have database and ASM (11.2.0.4) – the database is prepared to migration:

SQL> sho parameters db_recovery_file_dest

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 +DATA
db_recovery_file_dest_size	     big integer 6G
SQL> show parameters db_create_file_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest		     string	 +DATA
SQL> sho parameters db_create_online_log_dest_1

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1	     string	 +DATA

We will use recoverable copy of database to migrate to ASM.

[oracle@krol-julian ~]$ cat asm_migrate.rman 
run {
backup for recover of copy with tag 'asm_migr' incremental level 1 database;
recover copy of database with tag 'asm_migr';
}

The first run of this script will create copy of datafile on ASM:

[oracle@krol-julian ~]$ rman target / @asm_migrate.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 14 13:42:42 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1407457992)

RMAN> run {
2> backup for recover of copy with tag 'asm_migr' incremental level 1 database;
3> recover copy of database with tag 'asm_migr';
4> }
5> 
Starting backup at 14-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 7 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undo1_bo8r7j5h_.dbf
output file name=+DATA/orcl/datafile/undo1.257.879687765 tag=ASM_MIGR RECID=5 STAMP=879687776
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bo8r7j5o_.dbf
output file name=+DATA/orcl/datafile/system.258.879687779 tag=ASM_MIGR RECID=6 STAMP=879687787
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bo8r7j5q_.dbf
output file name=+DATA/orcl/datafile/sysaux.259.879687795 tag=ASM_MIGR RECID=7 STAMP=879687798
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bo8r7j68_.dbf
output file name=+DATA/orcl/datafile/users.260.879687801 tag=ASM_MIGR RECID=8 STAMP=879687803
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_def_tbs_bo8r7j6b_.dbf
output file name=+DATA/orcl/datafile/def_tbs.261.879687805 tag=ASM_MIGR RECID=9 STAMP=879687806
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-MAY-15
channel ORA_DISK_1: finished piece 1 at 14-MAY-15
piece handle=+DATA/orcl/backupset/2015_05_14/ncsnn1_asm_migr_0.262.879687809 tag=ASM_MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-15

Starting recover at 14-MAY-15
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 5 found to recover
no copy of datafile 7 found to recover
Finished recover at 14-MAY-15

Recovery Manager complete.

Subsequent executions of this script will recover the copy of database with fresh incremental backups

[oracle@krol-julian ~]$ rman target / @asm_migrate.rman

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 14 13:48:28 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1407457992)

RMAN> run {
2> backup for recover of copy with tag 'asm_migr' incremental level 1 database;
3> recover copy of database with tag 'asm_migr';
4> }
5> 
Starting backup at 14-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undo1_bo8r7j5h_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bo8r7j5o_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bo8r7j5q_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bo8r7j68_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_def_tbs_bo8r7j6b_.dbf
channel ORA_DISK_1: starting piece 1 at 14-MAY-15
channel ORA_DISK_1: finished piece 1 at 14-MAY-15
piece handle=+DATA/orcl/backupset/2015_05_14/nnndn1_asm_migr_0.265.879688111 tag=ASM_MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-MAY-15
channel ORA_DISK_1: finished piece 1 at 14-MAY-15
piece handle=+DATA/orcl/backupset/2015_05_14/ncsnn1_asm_migr_0.266.879688111 tag=ASM_MIGR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-15

Starting recover at 14-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=+DATA/orcl/datafile/system.258.879687779
recovering datafile copy file number=00002 name=+DATA/orcl/datafile/sysaux.259.879687795
recovering datafile copy file number=00003 name=+DATA/orcl/datafile/undo1.257.879687765
recovering datafile copy file number=00005 name=+DATA/orcl/datafile/users.260.879687801
recovering datafile copy file number=00007 name=+DATA/orcl/datafile/def_tbs.261.879687805
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2015_05_14/nnndn1_asm_migr_0.265.879688111
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2015_05_14/nnndn1_asm_migr_0.265.879688111 tag=ASM_MIGR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 14-MAY-15

Recovery Manager complete.

Now it is time to start maintenance window. I will move the controlfile from filesystem to ASM, using RMAN. First let’s remove the control_files parameter from spfile:

[oracle@krol-julian dbs]$ cd $ORACLE_HOME/dbs
[oracle@krol-julian dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 14 13:53:37 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> Disconnected
[oracle@krol-julian dbs]$ vim initorcl.ora 
[oracle@krol-julian dbs]$ cat initorcl.ora | grep control_files
[oracle@krol-julian dbs]$ strings spfileorcl.ora | grep control_files
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_bo6hcyjf_.ctl'#Restore Controlfile
[oracle@krol-julian dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 14 13:55:24 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2254952 bytes
Variable Size		  570427288 bytes
Database Buffers	 1560281088 bytes
Redo Buffers		    4923392 bytes
SQL> 

Now I will use RMAN to "restore" the controlfile from it’s original location

[oracle@krol-julian dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 14 13:57:24 2015

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/controlfile/o1_mf_bo6hcyjf_.ctl';

Starting restore at 14-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.267.879688653
Finished restore at 14-MAY-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

I will run my RMAN script one last time to refresh the copy of the database

[oracle@krol-julian ~]$ rman target / @asm_migrate.rman 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 14 13:59:32 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1407457992, not open)

RMAN> run {
2> backup for recover of copy with tag 'asm_migr' incremental level 1 database;
3> recover copy of database with tag 'asm_migr';
4> }
5> 
Starting backup at 14-MAY-15
(...)

And switch to ASM copy. After that I can open my database.

[oracle@krol-julian ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 14 14:01:19 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1407457992, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.258.879687779"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.259.879687795"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undo1.257.879687765"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/users.260.879687801"
datafile 7 switched to datafile copy "+DATA/orcl/datafile/def_tbs.261.879687805"

RMAN> recover database;

Starting recover at 14-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-MAY-15

RMAN> alter database open;

database opened

Now we can delete copy of database from filesystem

RMAN> delete copy of database;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
25      1    A 14-MAY-15       1599181    14-MAY-15      
        Name: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bo8r7j5o_.dbf

26      2    A 14-MAY-15       1599181    14-MAY-15      
        Name: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bo8r7j5q_.dbf

27      3    A 14-MAY-15       1599181    14-MAY-15      
        Name: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undo1_bo8r7j5h_.dbf

28      5    A 14-MAY-15       1599181    14-MAY-15      
        Name: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bo8r7j68_.dbf

29      7    A 14-MAY-15       1599181    14-MAY-15      
        Name: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_def_tbs_bo8r7j6b_.dbf

Because I want to have redo logs also on ASM I have add new redo logs and remove the old ones

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_bo8r9cmn_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_bo8r9bmh_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_bo8r99nk_.log

SQL> alter database add logfile group 11 size 128m,  
  2  group 12 size 128m,
  3  group 13 size 128m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 INACTIVE
	 2 CURRENT
	 3 UNUSED
	11 UNUSED
	12 UNUSED
	13 UNUSED

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 INACTIVE
	 2 INACTIVE
	 3 INACTIVE
	11 CURRENT
	12 UNUSED
	13 UNUSED

6 rows selected.

SQL> alter database drop logfile group 1, group 2, group 3;

Database altered.

MAINTENANCE WINDOW ENDS.

Now is good time to register the database in grid infrastructure

[oracle@krol-julian ~]$ srvctl add database -d orcl -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileorcl.ora -a data

Search:


Kontakt

Database Whisperers sp. z o. o. sp. k.
Al. Jerozolimskie 200
Wejście B, III piętro/ pokój 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter zapisz się żeby być na bieżąco