On-line migration of Oracle 18c filesystem datafiles to Oracle ASM

Background

Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, since Oracle 12c this is has no longer been the case.

I was recently challenged with moving a good size filesystem 18c Oracle database from an existing storage platform to a new storage array.

Online_Data_Move

Prepare ASM Disk Groups

Use sqlplus to create the ASM disk group directories e.g.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysasm

Disk Group +DATA

SQL> ALTER DISKGROUP DATA ADD DIRECTORY ‘+DATA/PSTG’;
SQL> ALTER DISKGROUP DATA ADD DIRECTORY ‘+DATA/PSTG/DATAFILE’;
SQL> ALTER DISKGROUP DATA ADD DIRECTORY ‘+DATA/PSTG/CHANGETRACKING’;
SQL> ALTER DISKGROUP DATA ADD DIRECTORY ‘+DATA/PSTG/TEMPFILE’;

Disk Group +CONTROL_REDO

SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY ‘+CONTROL_REDO/PSTG’;
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY ‘+CONTROL_REDO/PSTG/CONTROLFILE’;
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY ‘+CONTROL_REDO/PSTG/ONLINELOG’;
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY ‘+CONTROL_REDO/PSTG/PARAMETERFILE’;

Disk Group +FRA

SQL> ALTER DISKGROUP FRA ADD DIRECTORY ‘+FRA/PSTG’;
SQL> ALTER DISKGROUP FRA ADD DIRECTORY ‘+FRA/PSTG/ARCHIVELOG’;

Identify Datafiles

column dname heading ‘Data File Name’ format a45
column filename heading ‘BCT File Name’ format a45
column tablespace_name heading ‘Tablespace Name’ format a25
column data_mb heading ‘Data MB’ format a10

select file_name as dname, tablespace_name, to_char(bytes/1024/1024,’999,999′) as data_mb from dba_data_files

Data File Name                             Tablespace Name Data MB
———————————————-  ————————- ——
/u02/oradata/PSTG/system01.dbf             SYSTEM  880
/u02/oradata/PSTG/sysaux01.dbf             SYSAUX  990
/u02/oradata/PSTG/sh.dbf                                   SH  512,000
/u02/oradata/PSTG/users01.dbf                  USERS  5
/u02/oradata/PSTG/soe.dbf                              SOE  512,000
/u02/oradata/PSTG/undotbs01.dbf    UNDOTBS1  400

On-line Datafile move

I have a previously blogged on using the Oracle 12c ALTER DATABASE MOVE DATAFILE command and you can see the full syntax  on the link above.

SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/oradata/PSTG/system01.dbf’ TO ‘+DATA/PSTG/DATAFILE/system01.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/oradata/PSTG/sysaux01.dbf’ TO ‘+DATA/PSTG/DATAFILE/sysaux01.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/oradata/PSTG/sh.dbf’ TO ‘+DATA/PSTG/DATAFILE/sh.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/oradata/PSTG/users01.dbf’ TO ‘+DATA/PSTG/DATAFILE/users01.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/u02/oradata/PSTG/undotbs01.dbf’ TO ‘+DATA/PSTG/DATAFILE/undotbs01.dbf’;

Monitor Move Progress

Below is a query which uses the V$SESSION_LONOPS view to report progress of the Online data file move operations.

column st heading ‘Start Time’ format a25
column time_remaining heading ‘Time|Remaining’
column elapsed_seconds heading ‘Elaspsed|Seconds’
column sofar heading ‘Sofar’ format a10
column total heading ‘Total’ format a10
column progress heading ‘Progress’ format a10
column message heading ‘Message’ format a100

select to_char(start_time,’DD-MON-YY hh24:mi:ss’) as st, time_remaining, elapsed_seconds, to_char(SOFAR/1024/1024,’999,999′) || ‘MB’ as sofar, to_char(TOTALWORK/1024/1024,’999,999′) || ‘MB’ as total, to_char((SOFAR/TOTALWORK)*100,’999′) || ‘%’ as progress, message from V$SESSION_LONGOPS;

Example Output

09-AUG-18 14:58:33 0 7 880MB 880MB 100%
Online data file move: data file 1: 922746880 out of 922746880 bytes done

09-AUG-18 15:01:07 0 7 990MB 990MB 100%
Online data file move: data file 3: 1038090240 out of 1038090240 bytes done

09-AUG-18 15:06:34 0 2767 512,000MB 512,000MB 100%
Online data file move: data file 5: 536870912000 out of 536870912000 bytes done

09-AUG-18 15:57:07 2757 38 6,962MB 512,000MB 1%
Online data file move: data file 2: 7300186112 out of 536870912000 bytes done

Updated Data file locations

We can check the file haven been relocated using the same query we ran earlier to identify the datafiles.

select file_name as dname, tablespace_name, to_char(bytes/1024/1024,’999,999′) as data_mb from dba_data_files;

Data File Name Tablespace Name Data MB
——————————————— ————————- ——–
+DATA/PSTG/DATAFILE/system01.dbf SYSTEM 880
+DATA/PSTG/DATAFILE/sysaux01.dbf SYSAUX 1,040
+DATA/PSTG/DATAFILE/sh.dbf SH 512,000
+DATA/PSTG/DATAFILE/users01.dbf USERS 5
+DATA/PSTG/DATAFILE/soe.dbf SOE 512,000
+DATA/PSTG/DATAFILE/undotbs01.dbf UNDOTBS1 400

Advertisements

Oracle 12c Online Database Move Datafile

After watching an Oracle R12c new features presentation at a recent conference somebody asked me what was my favourite Oracle R12c database feature, for me it was easy has to be ‘ALTER DATABASE MOVE DATAFILE’

Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, with Oracle 12c this is no longer the case.

Storage Maintenance

You can use the ALTER DATABASE MOVE DATAFILE to move a data file to different location, disk or storage system. This provides a simple Online method of migrating to a new storage platform.

Online_Data_Move

The Oracle ALTER DATABASE MOVE DATAFILE does all the hard work for you including renaming or relocating the data file at operating system level and changing pointers in the Control Files.

Oracle also provide a way of monitor progress by using the V$SESSION_LONGOPS view, the reported bytes done will increment until the move is completed.

SQL> select file_name, tablespace_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST                /u01/app/oracle/oradata/PSTG/datafile/test.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;

SQL> select to_char(start_time,’DD-MON-YY hh24:mi:ss’),  timestamp, time_remaining, elapsed_seconds, message from V$SESSION_LONGOPS;

02-JUN-17 10:51:44   0 158
Online data file move: data file 4: 10737418240 out of 10737418240 bytes done

SQL>  select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST               /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

Please note before you begin make sure you have enough space on the source and target storage platforms as Oracle will allocate the required storage space on the target system and will not free any space up on the source until the move is complete.

If you specify the ‘KEEP’ parameter Oracle will copy the data file to the new location and retain the old datafile in the original location, it will be no longer reference in the control files and can be removed when no longer required.

SQL>  select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Database altered.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/test.dbf

[oracle@ol72-oraclepub datafile]$ ls -lh

-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 test.dbf
-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 tester.dbf

Please consult the Oracle R12.2 Docs for further details.

Code examples:

Rename:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;

Relocate:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u02/app/oracle/oradata/PSTG/datafile/test.dbf’;

Copy:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Move into ASM:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;

REUSE: overwrite data file with the same name
KEEP: copies the data file to a new location and retains original data file in old location

A Quick look at Delphix Cross Platform Provisioning

Why Cross Platform Provisioning (XPP)

As a consultant I am fortunate to get the opportunity to work and speak with many customers across the whole of EMEA, a topic which is often discussed is the use of the Linux or the ‘Cloud’ for Development and Test teams. I will cover how you can migrate data to the Cloud in a future Blog post, but today I want to get you ready for the Cloud.

The migration of databases to Linux on commodity hardware on-premise or in the Cloud continues to grown year-on-year as IT departments look to reduce their infrastructure and operational costs and also provide greater Agility to the Business. However Unix is far from dead and not everyone is looking to migrate their Production systems to Linux quite yet. This a great example of where Delphix XPP can be used to provide Virtual Development and Test databases on Linux whilst retaining Production on Unix.

Validate Source

If you have a Unix dSource or VDB you may have noticed the ‘Transform to Linux’ option on your dashboard, this Service-lead feature provides an automated approach to the traditional labour intensive challenge of performing Endianness conversion, e.g. Unix (Big Endian) to Linux (Little Endian).

During the validation Delphix will spin-up a temporary VDB, therefore before you start you need to ensure that your Unix staging server has had the ‘Use as Staging’ enabled from within the Server Environment.

Validate dSource

To get started Open up the dSource or VDB card, and click on the ‘Linux’ tab, from here click on the Green Tick to validate the ‘Linux Transformation’. Delphix will then perform a series of checks against known Oracle restrictions including:

  • No encryption – Columns / Tablespaces
  • Locally managed tablespaces (not dictionary managed)
  • Tablespaces are self-contained
  • Restrictions surrounding XML Types, Spatial Indexes and Advanced Queries

Transform to Linux

 

If the Validation process fails, use the download the ‘Transformation’ text file to assist in identification and correction of the errors reports. Perform required updates and recommended changes to the Transformation script and Upload and repeat Validation step until successful validation.

Successful Validation

Now that you have Validated dSource / VDB the previously greyed out ‘Transform to Linux’ will be available allowing the Provision of Linux VDB’s

Validated dSource

Click on ‘Transform to Linux’ and follow the Delphix Provisioning wizard providing Database name and Mount Point as normal.

Provision Linux VDB

Taking Oracle EBS R12 up into the Clouds with Delphix

Getting ‘Cloud Ready’

In my previous Oracle E-Business Suite (EBS) Virtualisation postings I have shown how Delphix can be used to Virtualise your EBS environment, and then how it can be used to to provision new virtual EBS environments.

Part 1: Virtualising Oracle EBS R12.1 using Delphix

Part 2: Creating a Virtual Oracle EBS Environment using Delphix

Now that we are ‘Cloud Ready’ I will show how we can use Delphix Replication to migrate your Oracle EBS environments into the Cloud.

Delphix supports the Amazon Elastic Cloud Compute (EC2) platform, and has also recently announced support for VMware vCloud Air.

i2Family
Amazon i2 Family

Note: When using choosing your Amazon Cloud instance for Delphix select a storage optimised instance type from the I2 family. The larger instances provide greater CPU and memory, the more memory you can give your Delphix Engine the better as this will be used as a cache improving read performance.

 

The I2 This family includes the High Storage Instances that provide very fast SSD-backed instance storage optimized for very high random I/O performance, and provide high IOPS at a low cost. 

Delphix requires that the all the storage attached be Amazon Elastic Block Store (EBS), Delphix does not support the use of instance store volumes

Using the Cloud

EBS Replication
Oracle EBS Replication with Delphix

In the example above the On-Premise Oracle R12 environment (Database, dbTech Stack & Apps Tier) are all replicated to a Delphix E2C instance.

This provides an extremely Agile deployment model where Production, Prod Support, Development, Test, Integration, Acceptance Testing and Training can all be hosted where makes best sense for the business.

It is becoming increasling common to see Production On-Prem and non-Prod in the Cloud, this could be as part of a longer strategic journey to the Cloud, with quick wins being delivered by using ‘Compute On-Demand’ or the final architecture.

When customers look at ‘SaaS’ based solutions a requirement which is frequently overlooked and can be challenging for non-Virtualised solutions is business reporting and integration. Very few organisations rely on a single business solution and often struggle to get their data out of the hosted solution. This is example where a Cloud Delphix Engine could be used to replicate back to On-Prem Delphix Engine, providing the business access to the required data.

Configuring Delphix for the Cloud

To get started logon to your Source Delphix Engine as a Delphix Administrator

EBS_Source

 

Navigate to System -> Replication

 

 

 

 

 

Provide Replication Target IP Address, Username and Password, configure Schedule and Network Options.

Source_Rep1

 

Hit OK

Source_Rep2

 

Click on ‘Run Now’ to start the Replication process

 

Target_Rep1

You can now Logon to the empty Target Server and from the pulldown select the Source Server and check the ‘Active Jobs’ to see the Replication process.

Target_Post

 

Now that you have Virtualised your Oracle EBS environment, configured Delphix Replication and Replicated the dSource and VDB’s to your Target server you are free to ‘Cloud Burst’, expanding and shrinking the number of EBS environments in-line with business demand.

For further information on how Delphix can be used to support your Data Migration projects visit the Delphix web site