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

Creating new Oracle 18c ASM Disk Groups

Creating New Volumes

Create the new ASM volume(s) using your storage platform, for this blog I have created 3 volumes on a Pure FlashArray, simply providing names and sizes.

FA_createVol
Create new ASM Volume(s)

Note the Volume serial numbers,  these will be used in the multipath configuration wwid (World Wide Identifier)

FA_vols
New ASM volumes

SCSI storage device serial numbers are universally unique. Each one starts with a 36bit (9 hexadecimal digit) vendor ID. Because the vendor ID is always the same for Pure Storage FlashArray volumes (3624a9370), it is omitted from the CLI and GUI displays.

Linux Configuration

Create multipath entries in  ‘/etc/multipath.conf’ for the 3 new volumes, in the examples below the wwid (World Wide Identifier) is set to Vendor ID + Serial number. e.g. ‘3624a9370’ + ‘ff551cc46aee54bf9000126ab’.
Note, the ‘wwid’ needs to be in lowercase and the ‘alias’ name for ASM disks needs to be less than 30 characters, alphanumeric and only use the ‘_ ‘ special character. 

multipath

Update multipath Configuration

Flush unused multipath devices

# multipath -F

Reload multipath configuration:

# service multipathd reload

Load and display multipath configuration, device mapper and other components

# multipath -v2
# multipath -ll

Oracle ASM Disk Discovery

Before we update our UDEV rules, lets check our Oracle ASM discovery disk string ‘ASM_DISKSTRING’.

Using asmcmd

ASMCMD> dsget
parameter:/u01/shared_config/ocr_configuration,  /dev/pureasm
profile:/u01/shared_config/ocr_configuration, /dev/pureasm

Using sqlplus

SQL> show parameter ASM_DISKSTRING;

asm_diskstring string /u01/shared_config/ocr_configuration, /dev/pureasm

We can now edit or create a UDEV rules file to reflect the ASM discovery string destination, the SYMLINK prefixes .

udev
/etc/udev/rules.d/99-oracle-asmdevices.rules

Reload UDEV rules as the root user using ‘udevadm trigger’ to set ownership and create links to the ASM disk discovery directory.

asmdisks
ls -la /dev/pureasm

We can now ready to create the ASM Disk Groups using the SQLPlus or the Oracle ASM Configuration Assistant ‘asmca’ UI.

I repeated this 3 times, creating +DATA. +FRA and +CONTROL_REDO which I will use in my next Blog Post, where I plan to share how to migrate from a file systems to Oracle ASM.

asmca
asmca

Oracle 18c Grid Infrastructure installation

Preparation

[root@z-oracle1 ~]# cd /u01/app/18.0.0/grid/cv/rpm
[root@z-oracle1 rpm]# ls
cvuqdisk-1.0.10-1.rpm

[root@z-oracle1 rpm]# rpm -ivh cvuqdisk-1.0.10-1.rpm
Preparing… ################################# [100%]
Updating / installing…
1:cvuqdisk-1.0.10-1 ################################# [100%]

18c Grid Infrastructure installation

Confirm ‘grid’ owner groups.

[grid@z-oracle1 grid]$ id grid

uid=54331(grid) gid=1001(dba) groups=1001(dba),54321(oinstall),54333(asmdba),54334(asmoper),54335(asmadmin),54336(racdba)

Create 18c Grid Infrastructure Oracle Base, Home and admin directories e.g.

[grid@z-oracle1 ~]$mkdir -p /u01/app/18.0.0/grid
[grid@z-oracle1 ~]$mkdir /u01/app/grid

Launch installer from terminal capable of starting X session.

[grid@z-oracle1 grid]$ cd $GRID_HOME
[grid@z-oracle1 grid]$ ./gridSetup.sh

Select Clustered, non-clustered or software only installation

1
Step 1

Update the ASM disk discovery path (if required), and select disk path for your +DATA disk group.

Before I started the 18c installer I created 3 Pure Storage volumes and configured UDEV rules to help identification. I can now select my 1TB +DATA volume and set the Redundancy to ‘External’ as the storage platform will provide the required data protection.

2
Step 2

Provide ASM passwords and click ‘Next’

3
Step 3

Register with Oracle Enterprise Manager, I will skip this for now, ‘Next’

4
Step 4

Confirm Oracle ASM ‘grid’ account groups, click ‘Next’

5
Step 5

Confirm pre-created Oracle Base location.

6
Step 6

Select ‘Next’ and run root.sh in a separate terminal window when prompted

7
Step 7

Review 18c Grid Infrastructure summary and click ‘Install’ to confirm installation

9
Step 9

Run root.sh from root window.

root
root.sh

Wait for the script to start the 18c Grid Infrastructure services, once complete click ‘OK’.

11.png
Step 11

If all has gone well, you should be presented with a ‘successful’ installation screen.

Now, lets look at our first look at an 18c ASM instance, if you observant you will see I have created 2 additional ASM DiskGroups +FRA & + CONTROL_REDO.

Will check my previous Blogs and if missing will share how you can create ASM Diskgroups using ASMCLI or AMSCA (UI).

1800.png

Come back in a few days to see my first 18c database Blog post.

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

Resizing Oracle ASM disks

Today I though I would share with how easy it is to resize Oracle ASM volumes with Pure Storage.

Ok, lets first check the Oracle ASM disk sizes using ‘asmcmd -p’

asmcli_pre.png

As you can see from the above, I have 3 volumes each of 100GB, for this test let’s increase them all to 1TB using the purevol command from with the CLI.

Pure_resize.png

Great, my volumes have now al been resized, I could have achieved the same results with the Pure UI or Web Services, but that’s something for another day.

Linux device rescan

Ok, we now need to let Linux and Oracle know about our resized volumes.

As root rescan the SCSI devices to identify which volumes which have been resized using:

rescan-scsi-bus.sh -s

Use ‘multipathd -k ‘resize map ‘ to resize the multipath devices e.g.

multipathd -k'resize map slob-data'

Before you moving onto resizing the Oracle ASM disk groups check your updated multi path configuration with ‘multipath -ll’, look for your device name and size e.g.

slob-data (3624a937050c939582b0f46c000059779) dm-5 PURE,FlashArray      size=1.0T features='0' hwhandler='0' wp=rw
`-+- policy='queue-length 0' prio=1 status=active
...

Oracle ASM resize

As your ‘grid’ user connect as sysasm from sqlplus e.g. sqlplus / as sysasm and perform ‘alter disk group <dg_name> resize all’

sqlplus_resize

Great, job done, but before we more on let’s check out work first using sqlplus as sysasm

SQL>  select name, total_mb/(1024) "Total GiB" from v$asm_diskgroup;
NAME                           Total GiB
------------------------------ ----------
CONTROL_REDO                   1024
FRA                            1024
DATA                           1024

And, now with the ASM command line utility ‘asmcmd’

asmcli_post.png

Or if you prefer the ASM UI ‘asmca’.

asmca.png

Using udev rules with OL / RH7 on VMware Fusion

Preparing to use udev rules on VMware Fusion

Before you can use udev rules with your new VMware Hard Drives you need to update the Virtual Machines .vmx file and set the following.

disk.EnableUUID = “TRUE”

Without this setting the ‘/usr/lib/udev/scsci_id’ command will no values.

As with all configuration files it’s best practice to create a backup before editing, however this is especially true for the VMware Fusion .vmx file as any mistake can result in a Virtual Machine which will no longer boot.

Shutdown your Virtual Machine before you attempt any edits of the .vmx file.

Creating the udev rules

Once your edit is complete you can reboot your VM and confirm is working ok with ‘usr/lib/udev/scsci_id -g -u -d /dev/’ e.g.

[root@ol72 ~]# /usr/lib/udev/scsi_id –verbose -g -u -d /dev/sdc
36000c298d0c4d6c7ace0c7b990069f48
[root@ol72 ~]# /usr/lib/udev/scsi_id –verbose -g -u -d /dev/sdd
36000c290045c8d9e8660057e191ec523
[root@ol72 ~]# /usr/lib/udev/scsi_id –verbose -g -u -d /dev/sde
36000c29b6dc77c32663c5b8354fffd37
[root@ol72 ~]# /usr/lib/udev/scsi_id -g -u -d /dev/sde
36000c29b6dc77c32663c5b8354fffd37

Now you know the Disks unique ID’s you can add them to a new udev rule. e.g.

/etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”36000c298d0c4d6c7ace0c7b990069f48″, SYMLINK+=”dg_prod_oradata1″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”36000c290045c8d9e8660057e191ec523″, SYMLINK+=”dg_prod_oraredo1″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”36000c29b6dc77c32663c5b8354fffd37″, SYMLINK+=”dg_prod_orafra1″, OWNER=”grid”, GROUP=”asmadmin”, MODE=”0660″

Unlike RedHat 5 & 6 there is no need to restart the udev service on RedHat 7

Testing the udev rules

You can now test the udev rules using udevadm test e.g.

udevadm test /block/sdb/sdb1

And check the results with ls -l /dev/

[root@ol72 dev]# ls -l /dev/sd*
brw-rw—- 1 root disk 8, 32 Oct 18 11:29 sdc
brw-rw—- 1 grid asmadmin 8, 33 Oct 18 11:49 sdc1
brw-rw—- 1 root disk 8, 48 Oct 18 11:29 sdd
brw-rw—- 1 grid asmadmin 8, 49 Oct 18 11:55 sdd1
brw-rw—- 1 root disk 8, 64 Oct 18 11:29 sde
brw-rw—- 1 grid asmadmin 8, 65 Oct 18 11:55 sde1

[root@ol72 dev]# ls -l /dev/dg*
lrwxrwxrwx 1 root root 4 Oct 18 11:49 /dev/dg_prod_oradata1 -> sdc1
lrwxrwxrwx 1 root root 4 Oct 18 11:55 /dev/dg_prod_orafra1 -> sde1
lrwxrwxrwx 1 root root 4 Oct 18 11:55 /dev/dg_prod_oraredo1 -> sdd1