Oracle 12c: Migrar Database de non-ASM a ASM usando online relocation data files

Hay muchos artículos que explican cómo migrar una base de datos en filesystem a ASM. Podemos utilizar RMAN para crear una copia de imagen de la base de datos en ASM y hacer un switch a la copia de la base de datos, restaurarla desde los backupset en ASM o hacer un duplicate.

Todas estas características de RMAN están disponibles en versiones Oracle previas a 12c.

En el siguiente post de Marko Sutic,  podemos ver un enfoque ligeramente diferente - utilizando la reubicación online de  datafiles en 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.

Continuar leyendo ...