oracle 12c asm - files.meetup.com caldara - oracle databas… · 7 04.09.2015 oracle acfs and...

BASLE BERN BRUGG DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. GENEVA

HAMBURG COPENHAGEN LAUSANNE MUNICH STUTTGART VIENNA ZURICH

Oracle ACFS and Oracle Database: a Perfect Marriage?

Ludovico Caldara Senior Consultant @ Trivadis Oracle ACE

About Ludovico Caldara

■ 16 Years DBA (Not Only Oracle)

▪ I do it everywhere (also on Windows)

■ RAC ATTACK Ninja & co-writer

■ RAC SIG, SOUG, ITOUG Board member

■ OCP (11g, 12c, MySQL) & OCE

■ Italian living in Switzerland

■ http://www.ludovicocaldara.net

■ @ludodba

■ ludovicocaldara

Oracle ACFS and Oracle Database: a Perfect Marriage? 2 04.09.2015

Our company.

© Trivadis – The Company 3 07/09/2015

Trivadis is a market leader in IT consulting, system integration, solution engineering

and the provision of IT services focusing on and technologies

in Switzerland, Germany, Austria and Denmark. We offer our services in the following

strategic business fields:

Trivadis Services takes over the interactive operation of your IT systems.

O P E R A T I O N

COPENHAGEN

MUNICH

LAUSANNE

BERN

ZURICH

BRUGG

GENEVA

HAMBURG

DÜSSELDORF

FRANKFURT

STUTTGART

FREIBURG

BASLE

VIENNA

With over 600 specialists and IT experts in your region.

© Trivadis – The Company 4 07/09/2015

14 Trivadis branches and more than

600 employees

200 Service Level Agreements

Over 4,000 training participants

Research and development budget:

CHF 5.0 / EUR 4 million

Financially self-supporting and

sustainably profitable

Experience from more than 1,900

projects per year at over 800

customers


ASM, ADVM, ACFS?

ASM, ADVM, ACFS?


ASM

Disk Group 1 Disk Group 2

ADVM Vol2

ACFS /app

.exe .dbf .txt

DB03

Disk Group 3

.dbf

.ctl .dbf .dbf

.dbf

.ctl .dbf .dbf

DB04 APP01

ADVM Vol4 ADVM Vol3

Ext4 /abc

.exe .dbf .txt

APP02 APP02

RAW

Cluster File System – ACFS (1)

Needs Grid Infrastructure!

ASM Cluster File System (ACFS) is a general purpose cluster filesystem implemented as part of ASM

– Dynamic loadable OS drivers delivered by Oracle

– Based on ASM Dynamic Volume Manager (ADVM)

Supports both traditional UNIX-style file access control classes and file access control lists (ACLs) for

Windows platforms

Uses standard file related system calls – no need to use special tools

Cannot be used for Grid Infrastructure home, voting files, OCR, ASM diag dest.

04.09.2015 Oracle ACFS and Oracle Database: a Perfect Marriage? 7


ASM Cluster File System (ACFS) leverages ASM

– High performance (Variable extents-based allocation)

– Data spread evenly across ASM disks

– Same data protection ot the Disk Group (External / Normal / High redundancy)

Supports Database Homes and Database diag dests

Multi-node, shared filesystem



Main features: Snapshots, Encryption, Security, Replication, Auditing

Up to 11.2.0.3, it was part of the Cloud File System option ($$)

Starting with 11.2.0.4, ACFS and ADVM are no more an extra-cost option

Starting with 12cR1, Oracle ACFS supports Oracle Datafiles if DB release >= 11.2.0.4

– https://docs.oracle.com/database/121/OSTMG/asmfilesystem.htm#OSTMG95961

Since ODA sw version 12.1.2, databases are created on ACFS


https://docs.oracle.com/database/121/OSTMG/asmfilesystem.htm#OSTMG95961

https://docs.oracle.com/database/121/OSTMG/asmfilesystem.htm#OSTMG95961


Datafiles on ACFS

ASM, ADVM, ACFS?


ASM


ADVM Vol2

ACFS /app

.exe .dbf .txt

DB03

Disk Group 3

.dbf

.ctl .dbf .dbf

.dbf

.ctl .dbf .dbf

DB04 APP01

ADVM Vol4 ADVM Vol3

Ext4 /abc

.exe .dbf .txt

APP02 APP02

RAW

ASM, ADVM, ACFS?


ASM


ADVM Vol1 ADVM Vol2

ACFS /u02 ACFS /app

.ctl .dbf .dbf

.exe .dbf .txt

DB01 DB03

Disk Group 3

.dbf

.ctl .dbf .dbf

.dbf

.ctl .dbf .dbf

DB04 APP01

ADVM Vol4 ADVM Vol3

Ext4 /abc

.exe .dbf .txt

APP02 APP02

RAW

ASM, ADVM, ACFS?


ASM


ADVM Vol1 ADVM Vol2

ACFS /u02 ACFS /app

.ctl .dbf .dbf

.exe .dbf .txt

DB01 DB03

Disk Group 3

.dbf

.ctl .dbf .dbf

.dbf

.ctl .dbf .dbf

DB04 APP01

ADVM Vol4 ADVM Vol3

Ext4 /abc

.exe .dbf .txt

APP02 APP02

RAW

NEW: 12c

Requirements


No Oracle Restart, only Grid Infrastructure (even single-node, but cluster installation)

advm.compatible and asm.compatible must be >= 12.1

Volume stripe columns must be set to 1 in 12.1.0.1.

– From 12.1.0.2 onwards the default is ok (columns 8, width 1024kb)

The DB must be running with filesystemio_options=setall

If Grid Infrastructure 12.1.0.1, only Unix/Linux

– Starting with 12.1.0.2, also Windows

Implementation steps (1)


## create the Disk Group with compatible 12.1

cat dg_acfs.xml

<dg name="ACFS" redundancy="external">

<dsk string="ORCL:DISK5" /><dsk string="ORCL:DISK6" /><dsk string="ORCL:DISK7" /><dsk string="ORCL:DISK8" />

<a name="compatible.asm" value="12.1.0.0.0"/>

<a name="compatible.rdbms" value="12.1.0.0.0"/>

<a name="compatible.advm" value="12.1.0.0.0"/>

</dg>




cat dg_acfs.xml






</dg>

asmcmd mkdg dg_acfs.xml




cat dg_acfs.xml






</dg>


## create the Volume (stripe columns must be set to 1 in 12.1.0.1. From 12.1.0.2 onwards the default is ok)

asmcmd volcreate -G acfs -s 19G volacfs --column 1




cat dg_acfs.xml






</dg>


## create the Volume (stripe columns must be set to 1 in 12.1.0.1. From 12.1.0.2 onwards the default is ok)

asmcmd volcreate -G acfs -s 19G volacfs --column 1

asmcmd volinfo --all

Diskgroup Name: ACFS

Volume Name: VOLACFS

Volume Device: /dev/asm/volacfs-167

State: ENABLED

Size (MB): 19456

Resize Unit (MB): 8

Redundancy: UNPROT

Stripe Columns: 1

...



grid@racvagn1:~/ $ /sbin/mkfs -t acfs /dev/asm/volacfs-167

mkfs.acfs: version = 12.1.0.2.0

mkfs.acfs: on-disk version = 39.0

mkfs.acfs: volume = /dev/asm/volacfs-167

mkfs.acfs: volume size = 20401094656 ( 19.00 GB )

mkfs.acfs: Format complete.









grid@racvagn1:~/ $ sudo su -

# srvctl add filesystem -d /dev/asm/volacfs-167 -m /u02 -u oracle -fstype ACFS -autostart ALWAYS











# srvctl start filesystem -d /dev/asm/volacfs-167

# df -k /u02

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/asm/volacfs-167 19922944 117616 19805328 1% /u02












# df -k /u02


/dev/asm/volacfs-167 19922944 117616 19805328 1% /u02

# chown oracle:oinstall /u02

# chmod 775 /u02

# su - oracle

oracle@racvagn1:~/ [+ASM1] mkdir -p /u02/app/oracle/oradata












# df -k /u02


/dev/asm/volacfs-167 19922944 117616 19805328 1% /u02


# chmod 775 /u02

# su - oracle


oracle@racvagn1:~/ [+ASM1] crsctl stat res ora.acfs.volacfs.acfs -t

Name Target State Server State details

Local Resources

ora.acfs.volacfs.acfs

ONLINE ONLINE racvagn1 mounted on /u02,STABLE













# df -k /u02


/dev/asm/volacfs-167 19922944 117616 19805328 1% /u02


# chmod 775 /u02

# su - oracle


oracle@racvagn1:~/ [+ASM1] crsctl stat res ora.acfs.volacfs.acfs -t

Name Target State Server State details

Local Resources

ora.acfs.volacfs.acfs



... Then create the database there... With filesystemio_options=setall!!



ASM is the default, but the

ACFS filesystem is discovered

And proposed by default

when Storage Type “File System” is selected

DBCA


Performance

SLOBing ASM vs ACFS

Environment

– ACFS filesystem, ASM DiskGroup

– Same topology, one SSD disk, same environment

– Same database and tablespace, datafile moved online from ASM to ACFS between runs SQL> alter database move datafile 2 to

'/u02/ACFSDB/ACFSDB/datafile/slob01.dbf';

Database altered.

OWNER TABLE_NAME NUM_ROWS BLOCKS

---------- ---------- ---------------- ----------------

USER2 CF1 97,464 100,493

USER1 CF1 100,000 100,883


SLOBing ASM vs ACFS

5 SLOB runs on ACFS, 5 SLOB runs on ASM

– Instance caging (1CPU)

– 20% update, no Hot Blocks

– consider only 1-bk reads/second

on SLOB TBSP

– skip worst, average others


SLOBing ASM vs ACFS

5 SLOB runs on ACFS, 5 SLOB runs on ASM

– Instance caging (1CPU)

– 20% update, no Hot Blocks

– consider only 1-bk reads/second

on SLOB TBSP

– skip worst, average others

Best ACFS run worse than worst ASM run

– Average loss: 3,8%


0

500

1000

1500

2000

2500

3000

3500

4000

1 2 3 4

ASM vs ACFS

ASM ACFS

SLOBing ASM vs ACFS

I did different tests (different update pcts, run durations, etc.), almost same results

Not a big performance loss, but... not so good either!

These are non-exhaustive tests, maybe ACFS has better (or worse!) performances under different loads.

Anyway, due to additional layer, ACFS cannot be faster than flat ASM in any case



Magic features

(or options?)

ACFS and Datafiles - Features

For Database files:

– ACFS Snapshots require Enterprise Edition



For Database files:




For Database files:


– ACFS Encryption, ACFS Security, ACFS Replication, ACFS Auditing are not available.

Oracle Database options should be used instead. Respectively: Oracle Advanced

Security, Oracle Database Vault, Oracle Data Guard, Oracle Audit Vault)

– https://docs.oracle.com/database/121/DBLIC/editions.htm#CIHDDJCJ


https://docs.oracle.com/database/121/DBLIC/editions.htm#CIHDDJCJ



For Database files:


– ACFS Encryption, ACFS Security, ACFS Replication, ACFS Auditing are not available.

Oracle Database options should be used instead. Respectively: Oracle Advanced

Security, Oracle Database Vault, Oracle Data Guard, Oracle Audit Vault)

– https://docs.oracle.com/database/121/DBLIC/editions.htm#CIHDDJCJ





So "Datafiles on ACFS"

is about snapshots

(remember, EE only)


0

500

1000

1500

2000

2500

3000

3500

4000

1 2 3 4

0

2

4

8

16

Write performance w/ snapshots (on base mountpoint)

snapshots

SLOB Run

IOs/second


0

500

1000

1500

2000

2500

3000

3500

4000

1 2 3 4

0

2

4

8

16

Write performance w/ snapshots (on base mountpoint)

snapshots

SLOB Run

IOs/second Big regression starting with the first snapshot.

No further regressions when more inactive snapshots are created.

Copy on Write?


Copy on Write?


Not really, it’s a Redirect on Write.

Copy on Write?



0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file

Copy on Write?



0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file Modify a block in first extent of /mp/file?

Copy on Write?



0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode


No snapshots point to the same extent: OK

Writes on base FS with snapshots


What if I have snapshots?

0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

Modify a block in first extent of /mp/file?




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file


Snapshots point to the same extent: Redirect

0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file



0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000 Next write on the same block: OK




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file



0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000 Modify a block in first extent of snapshot 1?

Next write on the same block: OK




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file



0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000

0x5000000

0x5000000

Modify a block in first extent of snapshot 1?


Snapshot 2 points to the same extent: Redirect




0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file



0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000

0x5000000

0x5000000








0x1000000 0x2000000 0x3000000

0x1000000

0x2000000

0x3000000

inode

/mp/file



0x4000000

0x1000000

0x2000000

0x3000000

inode

/mp/../sp1

/file

0x1000000

0x2000000

0x3000000

inode

/mp/../sp2

/file

0x4000000

0x5000000

0x5000000





Modify a block in first extent of snapshot 2 is

also ok because no snaps point to the same extent


Evidence?

Evidence of Redirect on Write Snapshot


# acfsutil snap create -w SNAP1 /u02

acfsutil snap create: Snapshot operation is complete.

# acfsutil snap create -w -p SNAP1 SNAP2 /u02

acfsutil snap create: Snapshot operation is complete.

Then, two nested snapshots: /u01 <- SNAP1 <- SNAP2

# dd if=/dev/urandom of=/u02/test/file count=16 bs=1048576

16+0 records in

16+0 records out

16777216 bytes (17 MB) copied, 2.83643 s, 5.9 MB/s

First, let’s create a file 16MB big



# acfsutil info file /u02/test/file | grep \|

--offset ----length | -dev --------offset

0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096

# acfsutil info file /u02/.ACFS/snaps/SNAP1/test/file | grep \|


0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096

All three files (base and snapshots) use the same extents



# dd if=/dev/urandom of=/u02/test/file count=1 bs=8192 conv=notrunc

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.068746 s, 119 kB/s



0 6524928 | 1 310378496 (was 30975586304)

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096

If I modify 8k in the first extent of /u02/test/file, the whole extent of the base file is redirected

Redirecting the whole extent. This is why the write is slower. Not because of a double write

typical of Copy on Write



# dd if=/dev/urandom of=/u02/test/file count=1 bs=8192 conv=notrunc seek=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.00299343 s, 2.7 MB/s



0 6524928 | 1 310378496

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096

Modifying another 8k block on the same extent does not lead to relocation. Write is faster.



# dd if=/dev/urandom of=/u02/.ACFS/snaps/SNAP2/test/file count=1 bs=8192 conv=notrunc seek=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.0348603 s, 235 kB/s



0 6524928 | 1 310378496

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 30975586304

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096



0 6524928 | 1 316903424 (was 30975586304)

6524928 5156864 | 1 30982111232

11681792 7925760 | 1 30987268096

The same would have appened mofiying snapshot 1.

Another try: modifying 8k in the first extent of second snapshot file leads to redirect for snapshot 2

Snapshot size and Write Latency Avg are correlated


Snapshot Size

IO Latency

Snap size=

DB Size

Same

Latency w/o

Snapshot

Curves depend on the repartition of hot spots / cold spots into the extents

New Snapshots == New latency spikes

Snap

Create

Other considerations about performances


ACFS is variable-extent based

– It may incur in fragmentation, especially with snapshots (due to extents redirects)

– I do not have elements for saying if it is a major problem or not

– [acfsdefrag0] system process exists, let me guess what it is for

– One extent may reside on different ASM Allocation Units (see acfsutil info file –d)

– Size allocated and size used may differ

## 1 MB file extent lean on two AUs on disks ASMDISK3 and ASMDISK6

----current offset | -cur vol offset -mir --extent --device name --------doffset --length -ASMdev

0 | 30397808640 0 3622 ORCL:ASMDISK3 5074698240 409600 5

409600 | 30398218240 0 3623 ORCL:ASMDISK6 5090836480 638976 1

--------------------------------------------------------------------------------------------------


Database on ACFS:

snapshot usecases

Snapshots are useful for...


Backup LOL, just kidding

Getting a consistent point before a modification

– Alternative to RESTORE POINTs?

Datafile copy for fast media recovery

Database cloning for Q&A, reporting, other

Multitenant: create pdb from pdb snapshot copy


Snapshots for consistent copy



On most snapshot technologies

– You take a snapshot

– You modify the data

– In case of problems, you revert (restore) the snapshot

With ACFS, there is still no way to revert a snapshot natively (no acfsutil command or other)

Nowadays, it does not make sense!



Workaround 1: Manual datafile copy from the snapshot

– shutdown

– delete the content from the base FS

– move the files from the snapshot

– drop/recreate the snapshot (to free up used snapshot space)

– startup



Workaround 2: Do some magic with RMAN and a temporary snapshot

– create a writable snapshot from snapshot

– shutdown

– delete the content from the base FS

– RMAN catalog start with... switch database to copy

– startup

– move back the datafiles with online move, then get the rid of the temporary snapshot



Workaround 3: Use guaranteed restore points instead of snapshots.

– :-P


File copy for fast media recovery



Follow the same procedure explained before...

– new writable snapshot form snapshot

– offline datafile

– catalog file, switch datafile to copy

– recover datafile, online datafile

– move datafile to old location (thank you 12c!)

– delete temporary snapshot



Having snapshots catalogued in RMAN as datafile copies allows also other kinds of

recovery

– E.g. block recovery


Database cloning for Q&A,

reporting, other

ACFS for Database cloning


Space efficient, fast-created copies

Source should not be performance-critical

ASM

Disk Group 1

ADVM Vol2

ACFS

/u02

.dbf

DB

.dbf .dbf

SNAP1

/u02/…/

SNAP2

/u02/…/

SNAP3

/u02/…/

SNAP4

/u02/…/

.dbf .dbf .dbf .dbf .dbf .dbf


DB01 DB02 DB03 DB04

ACFS for Database cloning – better with DG


Do not use with SYNC redo transport. If you need protection, setup a second standby.

ASM

Disk Group 1

ADVM Vol2

ACFS

/u02

.dbf

STDBY

.dbf .dbf

SNAP1

/u02/…/

SNAP2

/u02/…/

SNAP3

/u02/…/

SNAP4

/u02/…/



DB01 DB02 DB03 DB04

ASM

Disk Group 1

.dbf .dbf .dbf

DB

Best approach for cloning a standby on the same host


DGMGRL> edit database ACFSDB set state="APPLY-OFF";

$ acfsutil snap create -w CLONE /u02

DGMGRL> edit database ACFSDB set state="APPLY-ON";

SOURCE SQL> Backup controlfile to trace as '...' reuse resetlogs;

SOURCE SQL> create pfile='...' from spfile;

... sed-modify controlfile and pfile

CLONE SQL> create spfile from pfile ='...';

CLONE SQL> create controlfile …

CLONE SQL> alter database open resetlogs;

CLONE SQL> alter tablespace TEMP add tempfile ...;

CLONE SQL> shutdown immediate;

CLONE SQL> startup mount exclusive;

# nid TARGET=SYS DBNAME=CLONE

$ srvctl add database –db CLONE –oh $ORACLE_HOME –spfile=/u02/.ACFS/snaps/CLONE/.../ ...

Advantages of DG + ACFS


No need to recover datafiles, no fuzzy datafiles

– Stopping the redo apply on a standby database leaves the datafiles recovered to a

point in time

– Subsequent «alter database open resetlogs» just works

Decoupled disks, no impact on source database (primary)

DEMO?

gDBClone.pl


gDBClone.pl

http://www.oracle.com/technetwork/indexes/samplecode/gdbclone-download-2295388.html

Not production ready

Give a look into the source code. It does not look as something written by a DBA…

– Shuts down the source (but requires it in archivelog mode)

– Get the lists of datafiles for CREATE CONTROLFILE by doing a ls -l of /…/datafiles/







Create Pluggable Database

Snapshot Copy

Create Pluggable Database Snapshot Copy


If you use Multitenant, you can thin copy a PDB from another one, inside the same CDB:

SQL> create pluggable database CLONE1 from SOURCE snapshot copy;

Pluggable database created.

Create Pluggable Database Snapshot Copy


Internally:

– it creates the snapshot on ACFS

– It copies the definition of the PDB using the same datafiles

– It replaces the datafiles in file$ directly with symlinks pointing to the snapshot

Cloning inside multitenant means consolidating per business line, not per environment.

Ludovico Caldara

Senior Consultant

Tel. +41 79 909 72 75

[email protected]


oracle 12c asm - files.meetup.com caldara - oracle databas… · 7 04.09.2015 oracle acfs and...

Documents