Automate to avoid database cloning disasters.

“Accidentally destroyed production database on first day of a job”

Wow, that headline grabbed my attention.

Earlier this week you may have seen an article reported by The Register about a post in  reddit from a junior software developer going by the name of  “cscareerthrowaway567”, who on his/her first day destroyed a Production database and lost their job.

I was basically given a document detailing how to setup my local development environment. Which involves run a small script to create my own personal DB instance from some test data. After running the command i was supposed to copy the database url/password/username outputted by the command and configure my dev environment to point to that database. Unfortunately instead of copying the values outputted by the tool, i instead for whatever reason used the values the document had.

Unfortunately apparently those values were actually for the production database (why they are documented in the dev setup guide i have no idea). Then from my understanding that the tests add fake data, and clear existing data between test runs which basically cleared all the data from the production database. Honestly i had no idea what i did and it wasn’t about 30 or so minutes after did someone actually figure out/realize what i did.

Now, we can not be sure if the story by “cscareerthrowaway567” is true or not but it does provide an great example of the potential dangers of manual database cloning.

The story raises many issues around process, security, change management, training and not least why someone thought it was a good idea to include Production account details in a database cloning document ?

However, this could have easily been avoided through the use of Automation or a CDM (Copy Data Management) tools e.g. Catalogic, Actifio, Delphix etc..

Many modern All-Flash Arrays (AFA) provide powerful REST API’s which can be called from various programming and scripting languages e.g. Python, Java, Perl, PowerShell etc… to create custom solutions.

AFA Storage API’s can also be consumed by DevOPS Automation and Provisioning software e.g. Ansible, Chef, Puppet etc.. to provide full-stack or end-to-end Automation of Database Cloning, improving security and also removing the chance of human error.

Check out the Pure Storage Developer Community area for code examples.

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

How to resize an XFS filesystem

A question which I frequently get asked is, how do I a resize my Oracle XFS file system ?

As I needed to resize an Oracle FRA area today, I thought this would make a great topic for a Blog post.

Ok, lets start be checking the current size and geometry using the Linux df -h and xfs_growfs -n commands.

[root@z-oracle ~]# df -h
Filesystem                Size  Used Avail Use% Mounted on
..
/dev/mapper/psta-orafra   1.0T   33M  1.0T   1% /u04/app/oracle/fast_recovery_area
..

[root@z-oracle ~]# xfs_growfs /dev/mapper/psta-orafra -n
meta-data=/dev/mapper/psta-orafra isize=256 agcount=4, agsize=67108864 blks
= sectsz=512 attr=2, projid32bit=1
= crc=0 finobt=0 spinodes=0
data = bsize=4096 blocks=268435456, imaxpct=5
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=0
log =internal bsize=4096 blocks=131072, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

Now run the multipath command to check the current size of the LUN,  look for the friendly device name within the list of devices.

[root@z-oracle ~]# multipath -ll

psta-orafra (3624a937050c939582b0f46c0000a8f84) dm-17 PURE ,FlashArray
size=1.0T features=’0′ hwhandler=’0′ wp=rw
`-+- policy=’queue-length 0′ prio=1 status=active
|- 10:0:3:20 sdgl 132:16 active ready running
|- 10:0:4:20 sdiq 135:160 active ready running
|- 10:0:5:20 sdkq 66:480 active ready running
|- 10:0:6:20 sdmo 70:256 active ready running
|- 10:0:7:20 sdom 129:288 active ready running
|- 1:0:0:20 sdai 66:32 active ready running
|- 1:0:1:20 sdcr 69:240 active ready running
|- 1:0:2:20 sder 129:48 active ready running
|- 1:0:3:20 sdgr 132:112 active ready running
|- 1:0:4:20 sdil 135:80 active ready running
|- 1:0:5:20 sdkk 66:384 active ready running
|- 1:0:6:20 sdmk 69:448 active ready running
|- 1:0:7:20 sdop 129:336 active ready running
|- 10:0:0:20 sdau 66:224 active ready running
|- 10:0:1:20 sdck 69:128 active ready running
`- 10:0:2:20 sdel 128:208 active ready running

Now resize the volume using the Pure FlashArray UI, command line or REST API.

Screen Shot 2017-05-11 at 10.44.03

We now need to perform a rescan of the SCSI devices on our Linux server to identify any LUNS which have been resized.

[root@z-oracle ~]# rescan-scsi-bus.sh -s
Scanning SCSI subsystem for new devices
Searching for resized LUNs

We can now resize the multipath device using the following command:

[root@z-oracle mapper]# multipathd -k’resize map /dev/dm-17
ok

Great, ok now use the Linux command xfs_growfs to extend the file system, note if you do not specify -D xfs_growfs will grow to use all available space.

[root@z-oracle ~]# xfs_growfs /dev/mapper/psta-orafra
meta-data=/dev/mapper/psta-orafra isize=256 agcount=4, agsize=67108864 blks
= sectsz=512 attr=2, projid32bit=1
= crc=0 finobt=0 spinodes=0
data = bsize=4096 blocks=268435456, imaxpct=5
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=0
log =internal bsize=4096 blocks=131072, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
data blocks changed from 268435456 to 536870912

Ok, lets check the results

[root@z-oracle ~]# df -h
Filesystem Size Used Avail Use% Mounted on

/dev/mapper/psta-orafra 2.0T 33M 2.0T 1% /u04/app/oracle/fast_recovery_area
..

As you see from above, within a few minutes I have been able to increase my Oracle fast_recovery_area from 1TB to 2TB.

Oracle 12c on Docker

Getting the Image

Great news, during DockerCon 2017 Oracle announced support for Oracle 12.1 Database Docker images. Press release: Oracle Brings Oracle’s Flagship Databases and Developer Tools to the Docker Store

Previously to run an Oracle Database in a Docker container you needed to create your own Docker image, I actually Blogged on this earlier in the year, if you interested you can read about it here.

However, you can now obtain an official Oracle 12.1 Database Docker images from the Docker Store

Before you can ‘pull’ the new Docker image you will need to login to the Docker Store and click on the ‘Get Content’ button and then complete the form, providing your name, company, phone number and then accepting the Oracle license agreement.

Docker_Store

The ‘Setup’ button provides details on how ‘pull’ the image and also how to set-up the your Oracle database within Docker.

docker pull store/oracle/database-enterprise:12.1.0.2

pull
The Docker image includes all the required Oracle files so is pretty large coming in at ~4.87GB, so it may take a while to download.

Running the Container

The environment file allows for customisation of the Database name, password, domain and feature bundle.

The command for creating a database container is:

$ docker run -d –env-file -p :1521 -p :5500 -it –name –shm-size=”4g”

Parameters:
<path_to_env_file> is the path to the environment file you created using above example.
<listener_port> is the port on host machine to map the container’s 1521 port (listener port).
<http_port> is the port on host machine to map the container’s 5500 port (http service port).
<container_name> is the container name you want to create.
<shared_memory_size> is the memory size for the container to run. The minimum requirement is 4GB (–shm-size=”4g”).
<image_name> is the image that you use to create a container.

In this example our environment file is called db_env.dat and our container is going to be called dockerDB

docker run -d –env-file ./db_env.dat -p 1527:1521 -p 5507:5500 -it –name dockerDB –shm-size=”8g” store/oracle/database-enterprise:12.1.0.2

We should now be able to see our new Oracle 12.1 image and running container using:

docker image ls
docker container ls

images

From my MacBook Pro we check the progress of the build using the Docker logs option e.g.

docker logs -f dockerDB

ready

We can also connect to the running container using the Docker exec command e.g.

docker exec -it dockerDB /bin/bash

oracle.png
From here we can see the Oracle Docker is using Oracle Linux 7.3 and the database is called ‘OraDoc’ as per our environment file.

As you can see from the above we are connected as root, if you want to use the oracle account then type ‘su – oracle’ and from there try out sqlplus inside your container.

database

Oracle 12c on an MBP Done!