Infrastructure at your Service

Marc Wagner

Nutanix Era with oracle databases : Part 4 – Taking a snapshot and running Log Catch Up

By September 15, 2021 Oracle No Comments

Now that we have our ORATEST database provisioned (see Nutanix Era blog part 3), we will be looking how to run snapshot and Log Catch Up and how will this take effects on our oracle database.

For more information on Nutanix products, I would encourage you to visit Nutanix website.

Time Machines

Snapshot and Log Catch Up are done with the Time Machines menu :













Then we will select the TM (Time Machines) for our database. Here ORATEST :


Once we are in the Time Machines part of our concerned database, it is easy to see that our database belongs (as defined when provisioning the database) to the DEFAULT_OOB_GOLD_SLA SLA.

We can briefly see the retention of the snapshot policy that is used :






And we can see the schedule of the snapshot and the Log Catch Up. In our case :

  • Daily Snapshot are taken every day at 01:00 CEST.
  • We take one snapshot per day.
  • We take Log Catch Up every 30 minutes.
  • The weekly Snapshot are taken on Wednesday.
  • The monthly Snapshot are taken on the 11th of the month.







The SLA can be updated or created from the SLA Menu :












Snapshot

Let’s take a snapshot on our ORATEST oracle database. This is done by clicking on Actions and then snapshot :












We will provide a name for our snapshot :








And we will check the operations output :






If we go back to the TM Menu, we will notice the manual snapshot we have just taken :












In the Nutanix Era documentation, Nutanix gives the following advise and recommendation :
“The database is quiesced for the time the snapshot is being taken. Therefore, you would want to take only a minimum number of snapshots in a day.”

This make sense and we can see this in the alert log of the database :

[email protected]:/home/oracle/ [ORATEST] ORATEST
********* dbi services Ltd. *********
STATUS                 : OPEN
DB_UNIQUE_NAME         : ORATEST
OPEN_MODE              : READ WRITE
LOG_MODE               : ARCHIVELOG
DATABASE_ROLE          : PRIMARY
FLASHBACK_ON           : NO
FORCE_LOGGING          : NO
VERSION                : 19.12.0.0.0
CDB Enabled            : NO
*************************************

[email protected]:/home/oracle/ [ORATEST] taa
32720458,32725484,32728984,32738356,32739966,32740503,32754845,32758096,
32765738,32784393,32784403,32786309,32795712,32810668,32811069,32811131,
32816003,32817950,32818019,32858446,32874995,32881853,32889434,32895105,
32900208,32902635,32936961,32941509,32996071,33034103,33048277,33127032
===========================================================
2021-09-14T12:30:44.520240+00:00
db_recovery_file_dest_size of 10240 MB is 3.89% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2021-09-14T12:43:27.691263+00:00
ALTER SYSTEM ARCHIVE LOG
2021-09-14T12:43:27.710807+00:00
Thread 1 advanced to log sequence 34 (LGWR switch),  current SCN: 2708180
  Current log# 1 seq# 34 mem# 0: /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/onlinelog/o1_mf_1_jk7r195y_.log
  Current log# 1 seq# 34 mem# 1: /u02/app/oracle/oradata/fra_ORATEST/ORATEST/onlinelog/o1_mf_1_jk7r19br_.log
2021-09-14T12:43:27.782463+00:00
NET  (PID:17691): Archived Log entry 24 added for T-1.S-33 ID 0xd7835926 LAD:1
TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P266 (87) VALUES LESS THAN (TIMESTAMP' 2021-10-01 00:00:00')
alter database begin backup
Completed: alter database begin backup
2021-09-14T12:43:39.983879+00:00
alter database end backup
Completed: alter database end backup


We can see that taking a snapshot, Nutanix Era have run a backup of the archive log and have put the database in backup mode (alter database begin backup; and alter database end backup;). This steps is mandatory knowing the backup is done by a tool external to oracle, and this in order for Nutanix Era to have a consistent snapshot doing a backup on line (database is still running).
This will garantee not to have any :

  • header inconsistency
  • fractured blocks
  • backup inconsistency

What will happen during a begin Backup?

  • Backup online flag is set in the datafile file headers. This will guarantee the consistency in the backup.
  • A checkpoint is executed in order to have all the dirty blocks written in the datafiles.

What will happen during the Backup?

  • The datafile header are frozen in order to have the copy of the datafile timestamp with the SCN related to the beginning of the backup. So when this backup files will be used for a recovery, Oracle will know that the recovery (applying archive logs) needs to start with this appropriate Backup SCN. The next checkpoints done during the backup will not update the SCN in the datafile header, but will still update a backup SCN. This will solve the header inconsistency.
  • All first update on a block in the buffer cache will write the full block in the redo logs. Normal behavior is to write only a change vector in the redo log. This will solve the fractured blocks problem. Fractured blocks can exist in the backup file, but those blocks would be totaly replaced during the recovery.

What will happen during an end Backup?

  • A redo entry informing about end backup mode in the redo logs. So in case of recovery, Oracle will know that at least, the recovery should happen until this point. Backup consistency is guaranteed.
  • A reset of the backup online flag in the datafile header.
  • Datafile headers are updated with current SCN.

During begin/end backup, the database is fully available. But knowing the generated redo logs will be more important, it is recommended not to execute it during a high loaded time and not so frequently. Moreover we are putting the whole database in online backup mode and not only one tablespace after the other. Thus Nutanix recommendation in the documentation makes sense.

With the V$BACKUP view we can check which datafiles are set in backup mode.

Before executing a Nutanix snapshot, the view will show the following :

SQL> select FILE#, STATUS, TIME from v$backup;

     FILE# STATUS             TIME
---------- ------------------ -----------------------------
         1 NOT ACTIVE         15-SEP-2021 01:00:12
         3 NOT ACTIVE         15-SEP-2021 01:00:12
         5 NOT ACTIVE         15-SEP-2021 01:00:12
         7 NOT ACTIVE         15-SEP-2021 01:00:12


Executing a new snapshot will put the database in backup mode :

[email protected]:/home/oracle/ [ORATEST] taa
Thread 1 advanced to log sequence 55 (LGWR switch),  current SCN: 2809395
  Current log# 1 seq# 55 mem# 0: /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/onlinelog/o1_mf_1_jk7r195y_.log
  Current log# 1 seq# 55 mem# 1: /u02/app/oracle/oradata/fra_ORATEST/ORATEST/onlinelog/o1_mf_1_jk7r19br_.log
2021-09-15T08:47:46.781831+00:00
NET  (PID:413515): Archived Log entry 45 added for T-1.S-54 ID 0xd7835926 LAD:1
alter database begin backup
Completed: alter database begin backup
2021-09-15T08:47:58.718464+00:00
alter database end backup
Completed: alter database end backup


And during that time all datafiles will be shown as been in backup mode :

SQL> select FILE#, STATUS, TIME from v$backup;

     FILE# STATUS             TIME
---------- ------------------ -----------------------------
         1 ACTIVE             15-SEP-2021 08:47:46
         3 ACTIVE             15-SEP-2021 08:47:46
         5 ACTIVE             15-SEP-2021 08:47:46
         7 ACTIVE             15-SEP-2021 08:47:46

SQL> select file#, name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------------------------------------
         1 /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/datafile/o1_mf_system_jk7qywmo_.dbf
         3 /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/datafile/o1_mf_sysaux_jk7qzopd_.dbf
         5 /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/datafile/o1_mf_undotbsp_jk7rk221_.dbf
         7 /u02/app/oracle/oradata/datafiles_ORATEST/ORATEST/datafile/o1_mf_users_jk7r05t2_.dbf


Log Catch Up

From the same TM Menu, we can run a Log Catch Up :








The system will advise us that a next one is already planned (as part of the SLA), and will ask us if we would like to continue anyhow. We will confirm it :






This steps will save the database transaction logs. On oracle database these are the archive logs.

The first execution failed :






The error message is : Continuous recovery for this time machine is currently not active.

It seems that Nutanix needs to have a first automatic snapshot successfully run before been able to run Log Catch Up. A manual snapshot is not sufficient. My lab is not running in the night, so no automatic snapshot part of the SLA has been executed so far. I could change the execution time of the daily snapshot, but I had the opportunity to let the lab up and running for the night. On the next morning I could see that a manual execution of the Log Catch Up is now successful :






And also, going back to the Time Machines Menu for the ORATEST database, we can see that starting the automatic daily snapshot, all automatic and manual Log Catch Up are now successful.

Here we can see the 2 manual snapshots I have executed the day before :






And here we can see the situation after the automatic daily snapshot has been executed in the night :







We can see that now all next Log Catchup schedules are displayed as well.

Era documentation would say following according to Log Catch Up :
“For Oracle databases, Era switches the current online redo logs and then copies all the archived logs generated since the last log catch-up to permanent log storage location (also known as log drive) of Era. Note that Era does not remove any logs from their original location after copying. The management and retention of those logs are according to the policies set by the database administrator.”

Let’s have a look on ERA VM and let’s check the log storage location.

[[email protected]_ERA ~]$ df -h
Filesystem                                                                                                          Size  Used Avail Use% Mounted on
devtmpfs                                                                                                            7.8G     0  7.8G   0% /dev
tmpfs                                                                                                               7.8G   16K  7.8G   1% /dev/shm
tmpfs                                                                                                               7.8G   41M  7.8G   1% /run
tmpfs                                                                                                               7.8G     0  7.8G   0% /sys/fs/cgroup
/dev/mapper/era_server_vg_818f945082d133207c13b485872a454b27e1404e-root                                              48G  8.2G   40G  18% /
/dev/mapper/ntnx_era_dg_log_vg_47aab96bac4343b2a4a73453130a22fd-ntnx_era_agent_lv_47aab96bac4343b2a4a73453130a22fd  197G  365M  187G   1% /home/era/era_base/log_drive/ORATEST_47aab96bac4343b2a4a73453130a22fd
/dev/sda1                                                                                                          1014M  239M  776M  24% /boot
tmpfs                                                                                                               1.6G     0  1.6G   0% /run/user/0
tmpfs                                                                                                               1.6G     0  1.6G   0% /run/user/1001


So, every database has there own file system storage for the database archive logs. In our case it will be /home/era/era_base/log_drive/ORATEST_47aab96bac4343b2a4a73453130a22fd.

Let’s generate a new archive log file :

[email protected]:/u02/app/oracle/oradata/fra_ORATEST/ORATEST/archivelog/2021_09_15/ [ORATEST] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 15 13:46:42 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select max(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    67

SQL> alter system archive log current;

System altered.

SQL> select max(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    68


The maximum database sequence which was archived was 67 and we generate a new archive log with database sequence 68. We can check the archive log file name.

SQL> select name, sequence# from v$archived_log where sequence#=68;

NAME											    SEQUENCE#
------------------------------------------------------------------------------------------ ----------
/u02/app/oracle/oradata/fra_ORATEST/ORATEST/archivelog/2021_09_15/o1_mf_1_68_jn3yc3hr_.arc	   68


We can find the newly created archive log in the FRA :

[email protected]:/u02/app/oracle/oradata/fra_ORATEST/ORATEST/archivelog/2021_09_15/ [ORATEST] ls -ltrh
total 52M
...
...
...
-rw-rw----. 1 oracle oinstall  36K Sep 15 13:41 o1_mf_1_67_jn3xzzts_.arc
-rw-r-----. 1 oracle oinstall  20K Sep 15 13:47 o1_mf_1_68_jn3yc3hr_.arc


We will run a new Log Catch Up on Nutanix Era :






Note that the ERA and Database server are not in the same Time Zone as my laptop. The VMs are in the UTC time zone and my browser in the UTC+2 time zone.

Now we can see that for the time the Log Catch Up was created (2021-09-15 15:48:51 UTC+2, see previous screenshot) we have a new directory that was created in the log_drive of the Era VMs (2021-09-15 13:48:54 UTC) : /home/era/era_base/log_drive/ORATEST_47aab96bac4343b2a4a73453130a22fd/logs_0/20210915134119_20210915134854.

And in this last one we will see our last newly created archive log file with sequence 68 :

[[email protected]_ERA ~]$ ls -ltrh /home/era/era_base/log_drive/ORATEST_47aab96bac4343b2a4a73453130a22fd/logs_0/20210915134119_20210915134854
total 11M
-rwxr-xr-x. 1 root root 20K Sep 15 13:50 o1_mf_1_68_jn3yc3hr_.arc
-rwxr-xr-x. 1 root root 11M Sep 15 13:50 control01_1083332975.ctl
-rwxr-xr-x. 1 root root 22K Sep 15 13:50 o1_mf_1_69_jn3yg697_.arc
[[email protected]_ERA ~]$


As explained in the documentation, running a Log Catch Up will first switch the current online redo logs and then copies all the archived logs generated since the last log catch-up to the permanent log storage location. This is why we have now a max sequence for the archive log file equal to 69.

[email protected]:/u02/app/oracle/oradata/fra_ORATEST/ORATEST/archivelog/2021_09_15/ [ORATEST] ls -ltrh
total 53M
...
...
...
-rw-rw----. 1 oracle oinstall  36K Sep 15 13:41 o1_mf_1_67_jn3xzzts_.arc
-rw-r-----. 1 oracle oinstall  20K Sep 15 13:47 o1_mf_1_68_jn3yc3hr_.arc
-rw-rw----. 1 oracle oinstall  22K Sep 15 13:48 o1_mf_1_69_jn3yg697_.arc

[email protected]:/u02/app/oracle/oradata/fra_ORATEST/ORATEST/archivelog/2021_09_15/ [ORATEST] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 15 14:04:22 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select max(SEQUENCE#) from v$archived_log;

MAX(SEQUENCE#)
--------------
	    69

SQL>


The previous Log Catch Up has effectively backup the archive log file with database sequence 67 :

[[email protected]_ERA ~]$ ls -ltrh /home/era/era_base/log_drive/ORATEST_47aab96bac4343b2a4a73453130a22fd/logs_0/20210915133110_20210915134119
total 11M
-rwxr-xr-x. 1 root root 36K Sep 15 13:42 o1_mf_1_67_jn3xzzts_.arc
-rwxr-xr-x. 1 root root 20K Sep 15 13:42 o1_mf_1_66_jn3xn994_.arc
-rwxr-xr-x. 1 root root 11M Sep 15 13:42 control01_1083332521.ctl
[[email protected]_ERA ~]$


Archive log deletion

As we can see checking backup database parameters, the archive log deletion policy is set to none :

[email protected]:/home/oracle/ [ORATEST] rmanh

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 15 14:13:30 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORATEST (DBID=3615690790)

RMAN> show ARCHIVELOG DELETION POLICY;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORATEST are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN>


We can see that none of our archive log files are setted as reclaimable :

SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
ARCHIVED LOG                          6.16                         0              61          0

SQL> select count(*) from v$archived_log;

  COUNT(*)
----------
	61


And as we can see, for oracle, none of the archive log file has been backed up :

SQL> select count(*), BACKUP_COUNT from v$archived_log group by BACKUP_COUNT;

  COUNT(*) BACKUP_COUNT
---------- ------------
        61            0


And none has neither been deleted nor set as reclaimable :

SQL>
select applied,deleted,decode(rectype,11,'YES','NO') reclaimable,count(*),min(sequence#),max(sequence#)
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES' and name is not null
  4  group by applied,deleted,decode(rectype,11,'YES','NO') order by 5;

APPLIED   DEL REC   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- --- --- ---------- -------------- --------------
NO        NO  NO          61             10             70

SQL>


Conclusion

As we could see, Nutanix Era is well designed to have a proper and consistent database backup and archive logs backup. We just need not to forget to set accordingly the archive log deletion policy in order to managed correctly the archive logs. Nutanix Era is not going to do any DBA tasks for you.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Marc Wagner
Marc Wagner

Consultant