Monday, June 15, 2015

Using on ZFSSA:ZFSSAADM - Copy On Write (COW) on ZFS Storage Appliance (ZFSSA)

Database cloning with Copy On Write (COW) on ZFS Storage Appliance (ZFSSA). The method that I will demonstrate is the method of using the API ZFSSAADM for database cloning for data on ZFSSA. ZFSSAADM is an API which is part of the 12c Oracle software.

What we have is the following setup

  • An Oracle database 12c server 
    • With NFS mount point to the ZFS Storage Appliance Operational 
  • Direct NFS for databases Oracle ZFS Storage Appliance Simulator [Server IP 192.168.56.102]

    Validate my environment and settings

    Validate if it is a CDB database


    SQL> select database_name, CDB from v$database
    
    DATABASE_NAME         CDB
    ------------------------------ ---
    CDB01          NO
    
    SQL> > archive log list
    
    Database log mode        Archive Mode
    Automatic archival        Enabled
    Archive destination        USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     14
    Next log sequence to archive   16
    Current log sequence        16
    


    /etc/fstab
    #################################################################
    # entry for ZFSSA shares                                        #
    # ############################################################# #
    192.168.56.102:/export/dnfsdb01   /zfssa/db01    nfs  rw,bg,hard,nointr,noac,rsize=32768,wsize=32768,tcp,vers=3,timeo=600 0 0
    
    /etc/oranfstab
    #################################################################
    # entry for ZFSSA shares                                        #
    # ############################################################# #
     server: ZFSSA
       local: 192.168.56.101 path: 192.168.56.102
       export: /export/dnfsdb01 mount: /zfssa/db01 
    

    NFS share
    df -h 
    
    [192.168.56.102:/export/dnfsdb01       35G  2.3G   32G   7% /zfssa/db01]
    

    Direct NFS validation
    adrci exec="set home $ORACLE_SID; show alert -term;"|grep ODM
    
    Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0 
    

    Cloning the database using COW for the database on ZFSSA


    • place database in backup mode or bring the database down
    • clone the database with api zfssaadm 
    • mount the new zfssa mount point [ On the same server or on an other server] 
    • create the new database based on the datafiles of the cloned mountpoint

    Execute as Oracle user
    locate zfssaadm
    /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/utl/zfssa/bin/zfssaadm
    
    /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/utl/zfssa/bin/zfssaadm
    Usage: zfssaadm version
           zfssaadm [-v] [-l login] [-P password] [-p port] clone [-n newmountpoint] server:mountpoint
           zfssaadm [-v] [-l login] [-P password] [-p port] destroy [-F] server:mountpoint
    
    

    As root user add the following to the sudo file
    visudo
    
    Cmnd_Alias ORA =  /bin/mount                                          ,\
                      /bin/umount                                         ,\
                      /bin/mkdir                                          ,\
                      /bin/rmdir                                          ,\
                      /bin/rm                                             ,\
                      sudoedit /etc/fstab                                 ,\
                      sudoedit /etc/oranfstab                             ,\
                      /usr/bin/install                                    ,\
                      /usr/bin/find
    oracle ALL=(root, oracle) NOPASSWD:ORA
    

    Linux Function oracle session
    function ora_edit
    {
    ED="/bin/ed"
    CONTENT_TO_APPEND="$1"
    
    ##### Set editor #####
    OLD_EDITOR=$EDITOR
    export EDITOR=$ED;
    
    ##### Append using ed #####
    echo "a
    $CONTENT_TO_APPEND
    .
    w
    q" | sudoedit $2
    
    ##### Clean up #####
    export EDITOR=$OLD_EDITOR
    }
    

    Preparation joords01 DB
    mkdir -p $ORACLE_BASE/admin/joords01/{adump,dpdump,pfile,work}
    
    echo "joords01:/u01/app/oracle/product/12.1.0/dbhome_1:N" >> /etc/oratab
    
    cat /etc/mtab|grep db01
    192.168.56.102:/export/dnfsdb01 /zfssa/db01 nfs rw,bg,hard,nointr,noac,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,addr=192.168.56.102 0 0
    

    Shutdown Database CDB01
    sqlplus / as sysdba << EOF
    shutdown abort;
    startup restrict;
    shutdown immediate;
    EOF
    

    Create ZFSSA clone using ZFSSAADM

    /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/utl/zfssa/bin/zfssaadm -v -l root -P welcome1 clone -n /export/joords01  192.168.56.102:/export/dnfsdb01
    
    SCRIPT lookup {"mountpoint":"/export/dnfsdb01"}
    SCRIPT snapshot [{"pool":"demo","project":"default","share":"dnfsdb01"}] "joords01"
    SCRIPT clone [{"pool":"demo","project":"default","share":"dnfsdb01"}] "joords01" "joords01"
    /export/joords01
    

    StartDatabase CDB01
    sqlplus / as sysdba << EOF
    startup;
    EOF
    

    Set NFS share to new NFS clone JOORDS01
    sudo mkdir -p /zfssa/joords01
    
    ora_edit  "192.168.56.102:/export/joords01    /zfssa/joords01 nfs  rw,bg,hard,nointr,noac,rsize=32768,wsize=32768,tcp,vers=3,timeo=600 0 0" /etc/fstab
    
    cat /etc/fstab|grep joords
    192.168.56.102:/export/joords01    /zfssa/joords01 nfs  rw,bg,hard,nointr,noac,rsize=32768,wsize=32768,tcp,vers=3,timeo=600 0 0
    
    ora_edit "export: /export/joords01  mount: /zfssa/joords01" /etc/oranfstab
    
    cat /etc/oranfstab|grep joordsexport: 
    /export/joords01  mount: /zfssa/joords01
    
    sudo mount /zfssa/joords01
    
    df -h
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda2              12G  7.4G  3.7G  67% /
    tmpfs                 5.4G  272K  5.4G   1% /dev/shm
    /dev/sda1             243M  119M  111M  52% /boot
    /dev/sdb1             9.9G  239M  9.2G   3% /opt
    /dev/sdc1              40G   33G  5.2G  87% /u01
    /dev/sdd1             119G   25G   88G  23% /u02
    192.168.56.102:/export/dnfsdb01
                           35G  2.4G   32G   7% /zfssa/db01
    shares                932G   82G  851G   9% /media/sf_shares
    192.168.56.102:/export/joords01
                           35G  2.4G   32G   7% /zfssa/joords01
    

    Create script for create controlfile for the clone database joords01 on /zfssa/joords01
    cd $ORACLE_BASE/admin/joords01/work
    
    ls -d /zfssa/joords01/CDB01/datafile/*.dbf
    
    /zfssa/joords01/CDB01/datafile/o1_mf_sysaux_borfr773_.dbf
    /zfssa/joords01/CDB01/datafile/o1_mf_system_borfwrw5_.dbf
    /zfssa/joords01/CDB01/datafile/o1_mf_undotbs1_borg2zh4_.dbf
    /zfssa/joords01/CDB01/datafile/o1_mf_users_borg2w97_.dbf
    

    Create controlfile script Use the output of the command above as input of the files in the create controlfile script
    vi cr_cntrl_joords.sql
    
    SET ECHO ON
    SET FEEDBACK 1
    SET NUMWIDTH 10
    SET LINESIZE 80
    SET TRIMSPOOL ON
    SET TAB OFF
    SET PAGESIZE 100
    
    STARTUP NOMOUNT PFILE=/u01/app/oracle/admin/joords01/pfile/new_joordsinit.ora
    CREATE CONTROLFILE REUSE SET DATABASE joords01 RESETLOGS
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXINSTANCES 1
        MAXLOGHISTORY 908
    LOGFILE
      GROUP 1 '/zfssa/joords01/CDB01/datafile/joords01_log1.log' SIZE 100M BLOCKSIZE 512,
      GROUP 2 '/zfssa/joords01/CDB01/datafile/joords01_log2.log' SIZE 100M BLOCKSIZE 512
    DATAFILE
    '/zfssa/joords01/CDB01/datafile/o1_mf_sysaux_borfr773_.dbf',
    '/zfssa/joords01/CDB01/datafile/o1_mf_system_borfwrw5_.dbf',
    '/zfssa/joords01/CDB01/datafile/o1_mf_undotbs1_borg2zh4_.dbf',
    '/zfssa/joords01/CDB01/datafile/o1_mf_users_borg2w97_.dbf'
    CHARACTER SET AL32UTF8; 
    

    create initfile
    vi /u01/app/oracle/admin/joords01/pfile/new_joordsinit.ora
    
    *.audit_file_dest='/u01/app/oracle/admin/joords01/adump'
    *.audit_trail='db'
    *.compatible='12.1.0.2.0'
    *.control_files='/zfssa/joords01/CDB01/controlfile/control_joords01.ctl'
    *.db_block_size=8192
    *.db_create_file_dest='/zfssa/joords01/'
    *.db_domain=''
    *.db_name='joords01'
    *.db_recovery_file_dest='/zfssa/joords01'
    *.db_recovery_file_dest_size=4560m
    *.local_listener='LISTENER_CDB01'
    *.log_archive_format='%t_%s_%r.dbf'
    *.open_cursors=300
    *.pga_aggregate_target=256m
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=768m
    *.undo_tablespace='UNDOTBS1'
    

    Set Oracle environment export ORACLE_SID=joords01
    Create database
    sqlplus / as sysdba
    @/u01/app/oracle/admin/joords01/work/cr_cntrl_joords.sql
    
    alter database open resetlogs;
    
    ALTER TABLESPACE TEMP ADD TEMPFILE '/zfssa/joords01/CDB01/datafile/o1_mf_temp_borg6q4f_.tmp'
         SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    

    ZFSSA GUI


    Cleanup joords01 DB
    • Stop database joords01 
    • Umount /zfssa/joords01
    • destroy ZFSSA clone 
    • rmdir /zfssa/joords01

    /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/utl/zfssa/bin/zfssaadm -v -l root -P welcome1 destroy  192.168.56.102:/export/joords01
    Destroy share 192.168.56.102:/export/joords01? yes
    SCRIPT lookup {"mountpoint":"/export/joords01"}
    SCRIPT dependents [{"pool":"demo","project":"default","share":"joords01"}]
    SCRIPT destroyShare [{"pool":"demo","project":"default","share":"joords01"}]
    SCRIPT destroySnapshot [{"pool":"demo","project":"default","share":"dnfsdb01"}] "joords01"
    

    ZFSSA GUI