Jeyaseelan Blog: How to clone a database using ZFS snapshot

STEP by STEP Guide to create an Oracle database using ZFS snapshots.

Generally for 100TB DB running on an X5 Exadata machine with 64 channels via two ZFS controllers,

Traditional RMAN L0 backup runs around 6 hours. Restoration of the whole database takes around 7 to 9 hours

Image backup with L1 merge on daily basis, then if we can simply switch the database within around 15 mins to the image backup (apply required archive-logs).

We write the image backup on ZFS we can create snapshots ( only delta changes will recorded) in between daily incremental and merge backup . This will help DBAs to do point-in time recovery (or) create multiple testing environment faster

I explained the below steps to do a point time clone using ZFS snapshots. This can be used for testing purpose or extract some point time data if production data has corrupted.

ZFS configuration ( ZS3-2 80TB)

This test case has been tested on the below ZFS configuration

ZFS appliance with two heads with 80TB storage capacity.

If you have ZFS cluster configuration with ( active-active ), you can avail both controllers to get better performance .

If you have ZFS cluster configuration with ( active-passive ), you can avail only one controllers

Heada : akzfsa101a.des.sg.com

POOLS : ( Active – Active )

akzfsa101a : (Double Parity/Online

akzfsa101b : Exported

Cluster: Active/Active

Projects:

exp_backup_heada ( on akzfsa101a.des.sg.com )

Shares :

exp_backup_heada/image_backup => mount point ( /export/exp_backupa/image_backup)

exp_backup_heada/rman_archivelog => mount point ( /export/exp_backupa/rman_archivelog)

Headb : akzfsa101b.des.sg.com

POOLS : ( Active – Active )

akzfsa101b : Exported

akzfsa101a : (Double Parity/Online

Cluster: Active/Active

Projects:

exp_backup_headb ( on akzfsa101b.des.sg.com )

Shares :

exp_backup_headb/image_backup => mount point ( /export/exp_backup_headb/image_backup)

exp_backup_headb/rman_archivelog => mount point ( /export/exp_backup_headb/rman_archivelog)

Shares Compression Option

Take Base image backup with GZIP-2 FAST on backup share
Then revert compression option to LZJB ( Fastest) for level 1 backups.
Archive log can be compressed at rman level and write on LZJB ( fastest) share.

Share Mount options ((Oracle best practice setting)

Linux

rw,bg,hard,nointr,rsize=1048576,wsize=1048576,tcp,vers=3,time0=600,noac,actimeo=0

Solaris

rw,bg,hard,nointr,rsize=1048576,wsize=1048576,tcp,vers=3,forcedirectio,noac,actimeo=0

For example:

mount -t nfs -o rw,bg,hard,nointr,rsize=1048576,wsize=1048576,tcp,vers=3,time0=600,noac,actimeo=0 akzfsa101a.des.sg.com: /export/exp_backup_heada/image_backup /export/exp_backup_heada/image_backup

mount -t nfs -o rw,bg,hard,nointr,rsize=1048576,wsize=1048576,tcp,vers=3,time0=600,noac,actimeo=0 akzfsa101b.des.sg.com: /export/exp_backup_heada/image_backup /export/exp_backup_heada/image_backup

Create a database using ZFS snapshots.

I.1 High Level steps

I. 1.1) Pre-requisite

Create pools/projects/shares in the ZFS appliance
Mount the shares with best practice attributes on the compute node or DB server
Take an image backup
Create snapshot
Take L1 backup and merge with base image backup

(Repeat steps 3 to 5 at regular interval)

I. 1.2) when we would like to create a clone copy on ZFS Snapshot

Create a clone against the select snapshot ( based on the day ) in the ZFS
Set quota and create a mount point against the snapshot created in the ZFS
Mount the snapshot mount point in compute node
start the db in mount mode
catalog the snapshot mounts
switch the database to copy
open the database

II.1 Detail Steps

II. 1.1 Create base image backup ( take backup via both controllers – heads )

run {

sql 'alter system set "_backup_disk_bufcnt"=64';

sql 'alter system set "_backup_disk_bufsz"=1048576';

allocate channel ch001 device type disk format '/export/exp_backup_heada/image_backup/%U';

allocate channel ch002 device type disk format '/export/exp_backup_headb/image_backup/%U';

RECOVER COPY OF DATABASE WITH TAG 'image_copy_ZFS' ;

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 cumulative FOR RECOVER OF COPY WITH TAG 'image_copy_ZFS' DATABASE;

}

II.1.2 Create archivelog backups in a separate Share

run {

sql 'alter system set "_backup_disk_bufcnt"=64';

sql 'alter system set "_backup_disk_bufsz"=1048576';

allocate channel ch001 device type disk format '/export/exp_backup_heada/rman_archivelog/%U';

allocate channel ch002 device type disk format '/export/exp_backup_headb/rman_archivelog/%U';

backup archivelog all not backed up 1 times delete input TAG 'logs_ab';

backup current controlfile TAG 'ctl_bk' FORMAT '/export/exp_backup_heada/image_backupa/%d_%s_%p_%c_%t.ctl.rman';

backup spfile TAG 'spfile_bk' FORMAT '/export/exp_backup_heada/image_backupa/%d_%s_%p_%c_%t.spfile.rman';

release channel ch001;

release channel ch002;

}

II.1.3 Create a snapshot on both heads.

II. 1.3. 1 High level steps

ssh from compute node to zfs netman ( this need ssh key to be updated in the ZFS. Ref Apendix)
type ‘shares’ ( in zfs prompt )
type ‘ls’ ( to list projects )
type ‘select project’ ( select image_backup_heada)
type ‘snapshot’ ( to go to snapshot area )
type ‘snapshot <snap name>’ ( snapshot day1 ) ( to create snapshot)
type ‘ls’ ( to list snapshots )
type exit

II. 1.3. 2 Detailed steps

On Head 1:

ssh from compute node ( this can be done via GUI as well )

ssh -l oracle akzfsa101b-netman ( ensure oracle keys are copied to the zfs appliance)

akzfsa101a-netman:> shares

akzfsa101a-netman:> ls

Properties:

pool = akzfsa101a

usage_data = 29.6T

usage_snapshots = 1.09T

usage_total = 30.69T

Projects:

exp_backup_heada

Children:

encryption => Manage encryption keys

replication => Manage remote replication

schema => Define custom property schema

akzfsa101a-netman:shares> select exp_backup_heada

akzfsa101a-netman:shares exp_backup_heada > ls

…

Filesystems:

NAME SIZE ENCRYPTED MOUNTPOINT

image_backup 29.5T off /export/exp_backup_heada/image_backup

akzfsa101a-netman:shares exp_backup_heada > select image_backup

akzfsa101a-netman:shares exp_backup_heada/image_backup> snapshots

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots> snapshot day1

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots> ls

day1

Children:

automatic => Configure automatic snapshots

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots> exit

Repeat the above step in head b as well

On Head b:

ssh from compute node ( this can be done via GUI as well )

ssh -l oracle akzfsa101b-netman ( ensure oracle keys are copied to the zfs appliance)

akzfsa101b-netman:> shares

akzfsa101b-netman:> ls

Properties:

pool = akzfsa101b

usage_data = 28.6T

usage_snapshots = 1.09T

usage_total = 29.69T

Projects:

exp_backup_headb

Children:

encryption => Manage encryption keys

replication => Manage remote replication

schema => Define custom property schema

akzfsa101b-netman:shares> select exp_backup_headb

akzfsa101b-netman:shares exp_backup_headb > ls

…

Filesystems:

NAME SIZE ENCRYPTED MOUNTPOINT

image_backup 29.5T off /export/exp_backup_headb/image_backup

akzfsa101b-netman:shares exp_backup_headb > select image_backup

akzfsa101b-netman:shares exp_backup_headb/image_backup> snapshots

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots> snapshot day1

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots> ls

day1

Children:

automatic => Configure automatic snapshots

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots> exit

II 1.4 run image backup script ( step 2.1)

--- run the image backups

II 1.5 Create Clone based a day1 snapshot on both Nodes

II.1.5.1 High level steps

Select snapshot ( select day1)
Clone <clone name> ( clone DB12C_PITR )
Set quota=50g
Set mountpoint=(clone path)

set mountpoint=/export/image_backup_heada/DB12C_PITR1
Commit.
List clones

Do the above step on both heads.

II.1.5.2 Detail steps

Node akzfsa101a

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots> select day1

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots@day1> clone DB12C_PITR1

akzfsa101a-netman:shares exp_backup_heada/image_backup/DB12C_PITR1 (uncommitted clone)> set quota=50G

quota = 50G (uncommitted)

akzfsa101a-netman:shares exp_backup_heada/image_backup /DB12C_PITR1 (uncommitted clone)> set mountpoint=/export/image_backup_heada/DB12C_PITR1

mountpoint = /export/image_backup_heada/DB12C_PITR1 (uncommitted)

akzfsa101a-netman:shares exp_backup_heada/image_backup/DB12C_PITR1 (uncommitted clone)> commit

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots@day1> list clones

Clones: 1 total

PROJECT SHARE

Image_backup_heada DB12C_PITR1

akzfsa101a-netman:shares exp_backup_heada/image_backup snapshots@day1

Node akzfsa101b

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots> select day1

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots@day1> clone DB12C_PITR1

akzfsa101b-netman:shares exp_backup_headb/image_backup/DB12C_PITR1 (uncommitted clone)> set quota=50G

quota = 50G (uncommitted)

akzfsa101b-netman:shares exp_backup_headb/image_backup /DB12C_PITR1 (uncommitted clone)> set mountpoint=/export/image_backup_headb/DB12C_PITR1

mountpoint = /export/image_backup_headb/DB12C_PITR1 (uncommitted)

akzfsa101b-netman:shares exp_backup_headb/image_backup/DB12C_PITR1 (uncommitted clone)> commit

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots@day1> list clones

Clones: 1 total

PROJECT SHARE

Image_backup_headb DB12C_PITR1

akzfsa101b-netman:shares exp_backup_headb/image_backup snapshots@day1

II.1.6 Catalog the files into the database from the clone

Start the db in mount mode ( using the control file taken on the day)

DB12C_PITR> $ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 11 08:58:55 2017

connected to target database: DB12C_PITR (DBID=1161943131, not open)

RMAN> list copy of datafile 1;

using target database control file instead of recovery catalog

specification does not match any datafile copy in the repository

RMAN> catalog start with "/export/image_backup_heada/DB12C_PITR1 ";

searching for all files that match the pattern /export/image_backup_heada/DB12C_PITR1

List of Files Unknown to the Database

=====================================

File Name: /export/image_backup_heada/DB12C_PITR1/c-1161943131-20171211-03

File Name: /export/rman_backup_heada/DB12C_PITR1/data_D-DB12C_PITE_I-1161943131_TS-SYSAUX_FNO-2_0hslr01a

File Name:

RMAN> catalog start with "/export/image_backup_headb/DB12C_PITR1 ";

RMAN> list copy of datafile 1;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

10 1 A 11-DEC-17 1282837 11-DEC-17

Name: /export/image_backup_headb/DB12C_PITR1/data_D-DB12C_PITR-1161943131_TS-SYSTEM_FNO-1_0gslr01a

Tag: IMAGECOPY

II.1.7 Switch the database to clone copy

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/export/image_backup_heada/DB12C_PITR/data_D-DB12C_PITR_I-1161943131_TS-SYSTEM_FNO-1_0gslr01a"

datafile 1 switched to datafile copy "/export/image_backup_heada/DB12C_PITR/data_D-DB12C_PITR_I-1161943131_TS-SYSAUX_FNO-1_0gslr01a"

….

datafile 4 switched to datafile copy

…..

II. 1.8 Recover and open the database to PITR

RMAN> run {

SET UNTIL TIME "to_date('2017-12-11:05:59:00', 'yyyy-mm-dd:hh24:mi:ss')";

recover database;

}2> 3> 4>

executing command: SET until clause

Starting recover at 11-DEC-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=118 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=131 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=134 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=137 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=139 device type=DISK

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file +DATA01/db12c/archivelog/2017_12_11/thread_1_seq_6.8711.962431237

archived log for thread 2 with sequence 3 is already on disk as file

…..

archived log file name=+DATA01/db12c/archivelog/2017_12_11/thread_8_seq_3.8712.962431237 thread=8 sequence=3

media recovery complete, elapsed time: 00:00:02

Finished recover at 11-DEC-17

RMAN> alter database open resetlogs;

database opened

RMAN> exit

II.1.9 Verify the datafiles

Recovery Manager complete.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 11 09:04:53 2017

SQL> conn /as sysdba

Connected.

SQL> select name from v$datafile;

NAME

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

/export/image_backup_heada/DB12C_PITR1/data_D-DB12C

U_I-1161943131_TS-SYSTEM_FNO-1_0gslr01a

….

13 rows selected.

….

SQL>

Appendix

How to setup SSH key for oracle account in ZFS

Generate DSA key from Compute Node.
Login into netman ( gui)
https:// akzfsa101a-netman.des..com:215
go to configuration=>preferences => SSH Publick Keys (+) => paste the compute node DSA key and give a relevant key comment

Jeyaseelan Blog

Wednesday, 23 May 2018

How to clone a database using ZFS snapshot

No comments:

Post a Comment

ZFS

Public Cloud tools comparison

About Me