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:

Category:
choose
All categories 12c Exadata GO internals konferencje Oracle RAC RMAN RUST security tips&tricks tuning

Popular posts

14.06.2016
ORACLE RAC 12c: restore OCR and VOTEDISK

Let’s fuckup the cluster! Of course after this operation, the final state of the processes can look like this: The cssd service will not be able to start, because there are no voting disks: OK, so let’s try to stop the cluster services: Now we will have to start CRS in exclusive mode and start […]

Read more
10.02.2016
How to list files from directory using external table in 12c

I’ve wrote about privilege escalation with external tables in this post: https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/ This time we will try to list all files within directory object, to which we have no EXECUTE privs. This is possible due to great new feature of database 12c that allows to use metacharacters to match multiple files in external table. I […]

Read more
28.05.2017
How to recover a dropped package from archivelogs – based on research for VOODOO engine

Sorry for long time without post, but we are writing with Marcin Rydz a new heterogenous replication product based on archivelogs and it’s consuming a looooooot of our time 🙂 Yes I know it’s nothing new on the market. There are other cool products and I know they’ll be probably be better. But we want […]

Read more
03.05.2015
12c: Spatial Vector Acceleration

There is a new feature in Oracle Spatial 12c, called "Spatial Vector Acceleration". According to Oracle: One of the most notable improvements with Oracle Spatial and Graph 12c can be seen in performance increases in vector data operations. Enabling the Spatial Vector Acceleration feature (available with the Spatial option) dramatically improves the performance of commonly […]

Read more
01.02.2017
back to the basics: ALTER TABLE MOVE vs SHRINK

It’s time for the next article with ODBV visualisation 🙂 This time let’s examine the difference between move and shrink – this is very common question on a lot of trainings. You can find a lot of great articles in the Internet regarding this subject but I think that visualisation really helps to understand what […]

Read more
30.12.2016
back to the basics: truncate table reuse storage vs drop storage

From time to time I get questions on my trainings, what is the difference between TRUNCATE TABLE and TRUNCATE TABLE DROP STORAGE… well, there is no difference because DROP STORAGE is default 😉 DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the […]

Read more
28.10.2017
Visualize your STATSPACK reports with Python Plotly

If you work as a consultant, getting remote access to your customer is sometimes a nightmare! Even when you’ll get one, there a times when you just want to get your job done and finish this nightmare. For example, there are times when it is easier to dump a statspack repo and do offline analyzes. […]

Read more
08.02.2016
Sparc M7 vs Intel and P8

I had a pleasure to work with my colleagues from Oracle – Radosław Kut and Krzysztof Marciniak – at comparing the performance of In-Memory queries between Sparc M7, Intel® Xeon® X5670 and Intel® Xeon® E5-2699. You can find results of our findings in this presentation: https://www.oracle.com/webfolder/s/delivery_production/docs/FY16h1/doc17/DB12c-on-SPARC-M7.pdf Following their methodology I’d like to present appropriate results […]

Read more
29.10.2015
Oracle 12c: PRAGMA UDF – the truth

Let’s check, why PRAGMA UDF makes execution faster, then regular function. C function responsible for calling a PL/SQL code from SQL is called "plsql_run": Now let’s try to create a new HR session and create a simple function which will multiple values by 2: From other terminal I will connect to the HR session with […]

Read more
12.03.2017
ODBV improvements

Thanks to suggestions made by Frits Hoogland, I made some improvements to the ODBV. The new version can be found here: http://ora-600.pl/oinstall/odbv.x86_64 The changes are: Recognition of first, second and third level bitmap block Recognition of pagetable segment header Block number ranges on the left side The blocks will be coloured properly to belonging segment. […]

Read more
03.11.2016
Oracle Database Block Visualizer

Recently I wanted to demonstrate to some people on my training, how Oracle database maintains blocks in a datafile – what happens after truncate, truncate with drop storage clause, delete, regular insert, direct path insert and so on… I didn’t find any tool for that so I’ve written my own. It’s core code is based […]

Read more
27.01.2015
Slow TRUNCATE TABLE due to operations on MON_MODS$

One of my customers had a problem with a very slow TRUNCATE TABLE operation on a partitioned table having 22 000 partitions. The problem has raised after a test migration to database 12c. I’ve prepared a little demo to address this issue and show the walkaround for it. Oracle Database 12c (12.1.0.2): Oracle Database 11g […]

Read more

Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated