ODC Appreciation Day: Online Database Move Datafile

ODC Appreciation Day – 10th October 2017

Last year Tim aka ‘ORACLE-BASE’ launched the the first OTN (Oracle Technology Network) Appreciation Day, after the success of last year this is being repeated again this year under the new moniker of ODC.

As today is Tuesday 10th October 2017 expect some great content from fellow Oracle ACE’s and Oracle community folk coming out throughout the day as the different timezones come online.

It’s my pleasure to contribute to this year’s ODC Appreciation Day (Oracle Developer Community) and my favourite reason feature is the Oracle 12c Online database move datafile feature.

This feature is great for fixing naming or directory structure errors, re-organising your database layout, adopting new standards or migrating between storage systems. It supports Oracle ASM and filesystems with the only restriction being you need to be on Oracle 12c, so upgrade it your still on Oracle 11gR2 and embrace online database move datafile.

You can read more about it at my Blog  Online Database Move Datafile

Advertisements

One-Click Oracle 12c Database Clone

Background

Earlier in the year at the excellent OUgf Finnish Oracle User Group Harmony 17 conference, I performed a Live demo where I refreshed an Oracle 12c database using an Ansible Playbook in just over a minute, 1min 16sec to be more precise.

Timings

Once I had completed my presentation Frits Hoogland mentioned that he had recently used and Blogged on Ansible-Sempahore , an Open Source alternative to Ansible Tower which provides a Web UI and API’s for launching Ansible Tasks.

On my return to the UK I read Frits’ Blog – How to install semaphore UI for running Ansible and the installation instructions on the GitHub site, and was soon up and running.

Why this Blog

In this Blog post I am going to share how I used Ansible-Semaphore to launch my Ansible playbook to perform an Oracle 12c database refresh with a single click.

Once I had Semaphore installed, I configure the Inventory, Environment, Key Store and Playbook Repositories and added my Task Templates all through the Semaphore UI, all pretty straight forward, so no need to screenshot it.

semaphore Task Templates
semaphore Task Templates

A powerful feature of Semaphore is it’s integration with GitHub, this ensures every time a Playbook is run the last version is used as the code is pulled back from the repository.

Running my ‘Oracle Database Clone’ Ansible playbook is as now as simple as clicking ‘run!’ (no Playbook or Environment Overrides are required for this playbook)

Create_Task

At the end of each run semaphore provides a Task Log which is retained and accessible from the dashboard for historic review.

Task_Log

There we have it a One-Click Oracle 12c Database refresh in just under 1 minute 15 seconds.

If you want to see more, check out my YouTube channel to watch a demo of the above.

Docker, Oracle 12c and Persistent Storage

Introduction

If you have read my previous posts on Docker you may have already installed Docker and have an Oracle 12c database running in a Docker container, if not you may want to read my first post on Docker – Running Oracle 12.2.01 on Docker

However, since I wrote the above post Oracle have published Docker images for many of their products, these can be found at the Docker Store. The Oracle images on the Docker Store are not just a great resource and help accelerate adoption, but are also certified and supported by Oracle.

Before continuing with this post you need to check-out my posts on using the new Oracle 12c Docker images and the Pure Storage Docker plugin as this post can be considered the ‘Return of the Jedi’  in the series.

Oracle 12c with Persistent Storage

In this post I will create a Docker container running an Oracle 12c database called PSTG using 4 mount points /u01, /u02, /u03 & /u04 mounted from a Pure Storage FlashArray.

Database environment Layout

/u01 – OraInventory and Oracle 12c product directories
/u02 – Oracle Datafile
/u03 – Fast Recovery Area
/u04 – RedoLog files.

The 4 mount points will all be using persistent All Flash Storage providing low latency performance, space efficient crash consistent snapshots and requiring no local storage.

First, confirm that you have installed the Oracle 12c image using the the following:

$ docker images list

docker_imagesYou should be able see the store/oracle/database-enterprise 12.1.0.2 Docker Image, if missing you need to read my previous post.

Ok, copy the db_env.dat file created in the previous post to a new environment file $ORACLE_SID_env.dat and update database name and password e.g.

####################################################################
## Copyright(c) Oracle Corporation 1998,2016. All rights reserved.##
##                                                                ##
##                   Docker OL7 db12c dat file                    ##
##                                                                ##
####################################################################
##------------------------------------------------------------------
## Specify the basic DB parameters
##------------------------------------------------------------------
## db sid (name)
## default : ORCL
## cannot be longer than 8 characters
DB_SID=PSTG
## db passwd
## default : Oracle
DB_PASSWD=Oracle
## db domain
## default : localdomain
DB_DOMAIN=localdomain
## db bundle
## default : basic
## valid : basic / high / extreme 
## (high and extreme are only available for enterprise edition)
DB_BUNDLE=basic
## end

We will now create the 4 volumes using the docker volume create command and Pure Storage Docker plug-in providing name, size and label.

 docker volume create --driver=pure --opt size=20GB --name=PSTG-u01 --label=product
 docker volume create --driver=pure --opt size=100GB --name=PSTG-u02 --label=oradata
 docker volume create --driver=pure --opt size=200GB --name=PSTG-u03 --label=fast_recovery_area
 docker volume create --driver=pure --opt size=10GB --name=PSTG-u04 --label=redo

We can use the docker volume ls command with a filter to list the 4 database volumes we are interested in using the following syntax

$ docker volume ls -f name=environment

docker_vols

The Pure Storage Docker Plugin also supports docker volume inspect, from here we can see the labels which we defined earlier, great way to see where and what the mount is being used for.

$ docker volume inspect volume name

docker_inspect.png

Now that we have installed the Oracle 12c Docker image and created the 4 volumes we are ready to create our Oracle 12c container using the docker run command with -v to present our newly volumes to our container.

docker run -d –env-file ./PSTG_env.dat -p 1527:1521 -p 5507:5500 -it –name PSTG -v PSTG-u01:/u01 -v PSTG-u02:/u02 -v PSTG-u03:/u03 -v PSTG-u04:/u04 –shm-size=”8g” store/oracle/database-enterprise:12.1.0.2

Progress can be monitored using the docker logs command.

docker logs -f PSTG

Once the container has started we can now connect to it and check out our Oracle 12c environment using the docker exec command and container name

docker exec -it PSTG /bin/bash

From within our Oracle 12c ‘PSTG’ container we can see our Pure Storage volumes (/u01, /u02, /u03 & /u04) and the sizes we specified.

df.png

Now we are in our Docker container we can use SQLPlus to see data file locations, instance name, docker assigned hostname etc…

sqlplus

instance

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