12c Migrate Database from non-ASM to ASM using online relocation of data files

Wednesday, February 18, 2015

There are many articles explaining how to migrate database from file system into ASM. You could use RMAN to create an image copy of the database into ASM and switch to the database copy, restore database from backup sets into ASM or create duplicate database.

All of these RMAN features are available on Oracle versions before 12c.

In this post I will use slightly different approach - using online relocation of data files into ASM.

Renaming and Relocating Online Data Files is available from 12c and enables us to rename or relocate data file while the database is open and users are accessing data files. This simplifies datafile management and avoids downtimes even when moving SYSTEM or UNDO tablespaces.


This is 12.1 pluggable single-tenant database I want to migrate from file system to ASM:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u01/app/oracle12/oradata/cdb12c/system01.dbf
2    260      PDB$SEED:SYSTEM      NO      /u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf
3    740      SYSAUX               NO      /u01/app/oracle12/oradata/cdb12c/sysaux01.dbf
4    585      PDB$SEED:SYSAUX      NO      /u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf
5    740      UNDOTBS1             YES     /u01/app/oracle12/oradata/cdb12c/undotbs01.dbf
6    5        USERS                NO      /u01/app/oracle12/oradata/cdb12c/users01.dbf
7    270      PDB:SYSTEM           NO      /u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf
8    605      PDB:SYSAUX           NO      /u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf
9    5        PDB:USERS            NO      /u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf
10   50       PDB:MARKO            NO      /u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 100         /u01/app/oracle12/oradata/cdb12c/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf
3    100      PDB:TEMP             100         /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf



Create script to move data files:

$ sqlplus -s / as sysdba
set lines 200
set pages 50
set feed off
set head off
spool /tmp/move_dbfiles.sql
select 'ALTER DATABASE MOVE DATAFILE '''||name||''' TO ''+DATA'';' from v$datafile
order by con_id;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA';
spool end;
exit

Now, if you just run this script you will get some errors because you can’t move PDB files if you’re not in PDB container. Also PDB must not be offline.


After I slightly edited script this is final version:

$ cat /tmp/move_dbfiles.sql

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/undotbs01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/sysaux01.dbf' TO '+DATA';

ALTER SESSION SET CONTAINER=pdb$seed;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/sysaux01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdbseed/system01.dbf' TO '+DATA';

ALTER SESSION SET CONTAINER=pdb;
ALTER DATABASE OPEN;

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/marko01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/pdb_users01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/system01.dbf' TO '+DATA';
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle12/oradata/cdb12c/pdb/sysaux01.dbf' TO '+DATA';


Let’s execute this script:

$ sqlplus / as sysdba

SQL> @/tmp/move_dbfiles.sql

Database altered.
Database altered.
Database altered.
Database altered.
Session altered.
Database altered.
Database altered.
Session altered.
Database altered.
Database altered.
Database altered.
Database altered.
Database altered.

Data files are now migrated to ASM without any downtime. Very simple.


Notice my temporary tablespaces are still on file system.

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 100         /u01/app/oracle12/oradata/cdb12c/temp01.dbf
2    62       PDB$SEED:TEMP        32767       /u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf
3    100      PDB:TEMP             100         /u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf


I cannot use command as above to move them so I will use slightly different approach.

SQL> alter tablespace TEMP add tempfile '+DATA';
Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/temp01.dbf';
Tablespace altered.


SQL> alter session set container=pdb;
Session altered.

SQL> alter tablespace TEMP add tempfile '+DATA';
Tablespace altered.

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdb/temp01.dbf';
Tablespace altered.


The tricky part is to move temp file from tablespace TEMP in PDB$SEED used for creation of pluggable databases. I have found this solution on Jože Senegačnik blog post.

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> alter pluggable database pdb$seed close;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.

SQL> alter session set container=pdb$seed;
Session altered.

SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.

SQL>  alter tablespace temp drop tempfile '/u01/app/oracle12/oradata/cdb12c/pdbseed/temp01.dbf';
Tablespace altered.

SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> alter pluggable database pdb$seed close;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;
Pluggable database altered.



All my data files and temporary files are now migrated into ASM.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     +DATA/CDB12C/DATAFILE/system.259.871936371
2    260      PDB$SEED:SYSTEM      NO      +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527
3    740      SYSAUX               NO      +DATA/CDB12C/DATAFILE/sysaux.263.871936455
4    585      PDB$SEED:SYSAUX      NO      +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497
5    740      UNDOTBS1             YES     +DATA/CDB12C/DATAFILE/undotbs1.257.871936413
6    5        USERS                NO      +DATA/CDB12C/DATAFILE/users.258.871936451
7    270      PDB:SYSTEM           NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549
8    605      PDB:SYSAUX           NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565
9    5        PDB:USERS            NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547
10   50       PDB:MARKO            NO      +DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      PDB$SEED:TEMP        32767       +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389
4    100      TEMP                 32767       +DATA/CDB12C/TEMPFILE/temp.268.871936871
5    100      PDB:TEMP             32767       +DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909
7    100      PDB$SEED:TEMP        32767       +DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843



Move redo log files into ASM:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle12/oradata/cdb12c/redo01.log
/u01/app/oracle12/oradata/cdb12c/redo02.log
/u01/app/oracle12/oradata/cdb12c/redo03.log


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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+DATA';

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+DATA';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+DATA';

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/CDB12C/ONLINELOG/group_1.261.871938327
+DATA/CDB12C/ONLINELOG/group_2.260.871938351
+DATA/CDB12C/ONLINELOG/group_3.272.871938381


All this operations were done online while database was up and running.

Now comes part when some downtime is needed - part when we move control files from file system into ASM.


$ srvctl stop database -d cdb12c


RMAN> startup nomount;

Oracle instance started

Total System Global Area     838860800 bytes

Fixed Size                     2929936 bytes
Variable Size                570428144 bytes
Database Buffers             260046848 bytes
Redo Buffers                   5455872 bytes

RMAN> restore controlfile to '+DATA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl';

Starting restore at 17-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 17-FEB-15

RMAN> restore controlfile to '+FRA' from '/u01/app/oracle12/oradata/cdb12c/control01.ctl';

Starting restore at 17-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 17-FEB-15




ASMCMD [+] > find --type CONTROLFILE +DATA *
+DATA/CDB12C/CONTROLFILE/current.273.871940441
ASMCMD [+] > find --type CONTROLFILE +FRA *
+FRA/CDB12C/CONTROLFILE/current.256.871940457



SQL> alter system set control_files='+DATA/CDB12C/CONTROLFILE/current.273.871940441','+FRA/CDB12C/CONTROLFILE/current.256.871940457' scope=spfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.



Put parameter file into ASM also and start database.

$ srvctl start database -d cdb12c -o mount


RMAN> run
2> {
3> BACKUP AS BACKUPSET SPFILE;
4> RESTORE SPFILE TO "+DATA/CDB12C/spfilecdb12c.ora";
5> }

Starting backup at 17-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-FEB-15
channel ORA_DISK_1: finished piece 1 at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/backupset/2015_02_17/o1_mf_nnsnf_TAG20150217T214744_bg7b7jkg_.bkp tag=TAG20150217T214744 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-15

Starting Control File and SPFILE Autobackup at 17-FEB-15
piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-FEB-15

Starting restore at 17-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/CDB12C/spfilecdb12c.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle12/fast_recovery_area/CDB12C/autobackup/2015_02_17/o1_mf_s_871939236_bg7b7m1p_.bkp tag=TAG20150217T214745
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-15



$ srvctl modify database -db cdb12c -spfile '+DATA/CDB12C/spfilecdb12c.ora';



$ srvctl stop database -db cdb12c
$ srvctl start database -db cdb12c



Final checks:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/CDB12C/spfilecdb12c.ora

SQL> set lines 200
SQL> set pages 999
SQL> select name from v$datafile union all
  2  select name from v$tempfile union all
  3  select member from v$logfile union all
  4  select name from v$controlfile;

NAME
-----------------------------------------------------------------------------
+DATA/CDB12C/DATAFILE/system.259.871936371
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/system.265.871936527
+DATA/CDB12C/DATAFILE/sysaux.263.871936455
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/DATAFILE/sysaux.256.871936497
+DATA/CDB12C/DATAFILE/undotbs1.257.871936413
+DATA/CDB12C/DATAFILE/users.258.871936451
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/system.269.871936549
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/sysaux.266.871936565
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/users.270.871936547
+DATA/CDB12C/0C38EB3894542836E055000000000001/DATAFILE/marko.271.871936543
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.264.871937389
+DATA/CDB12C/TEMPFILE/temp.268.871936871
+DATA/CDB12C/0C38EB3894542836E055000000000001/TEMPFILE/temp.267.871936909
+DATA/CDB12C/0C366F6BD5E01C14E055000000000001/TEMPFILE/temp.262.871937843
+DATA/CDB12C/ONLINELOG/group_1.261.871938327
+DATA/CDB12C/ONLINELOG/group_2.260.871938351
+DATA/CDB12C/ONLINELOG/group_3.272.871938381
+DATA/CDB12C/CONTROLFILE/current.273.871940441
+FRA/CDB12C/CONTROLFILE/current.256.871940457

19 rows selected.




REFERENCES
http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN012
http://joze-senegacnik.blogspot.com/2015/02/maintaining-tempfile-in-temp-tablespace.html



Share/Bookmark

5 comments:

  • Wow, brilliant! Nice article and very excellent explanations. Thanks for posting this

  • Paul Atemnkeng

    In this scenario, do the two databases reside on two separate servers?

  • Paul Atemnkeng

    In this scenario, do the two databases reside on two separate servers?

  • Worked as a charm. Thank you!!!

  • ashish jha

    Brilliant...it is easy to implement in 12c.....

  • Post a Comment