Dbvisit – Standby Database Technology Blog

Dbvisit version Dbvisit 5.2.28 has been released. This version fixes a compatibility issue with Oracle 8i and Oracle 9i which was introduced in a previous version.

This version allows Dbvisit to be installed alongside Data Guard to test a comparison between Dbvisit and Data Guard. Dbvisit now ignores the specific Data Guard settings which means Data Guard does not need to be removed and no init.ora parameters need to be changed.

This version ignores dynamic memory location parameters and parameters related to other instances in spfile/pfile when creating the standby database.

New features:

1. Allow for Data Guard configurations to co-exists with Dbvisit.
   - Allow for VALID_FOR parameter.
   - Ignore standby redo log files in controlfile creation.

Fixes:

1. Remove a prompt for pfile name if PFILE is not found on the server during creation of standby database.
2. Set SOURCE and DESTINATION variables to short server names.
3. Change variables _SEQUENCE_LANG_NAME and _ASM_EXCLUDE to quoted strings instead of lists.
4. Change the query to obtain archive log names to ignore log archive destinations pointing to standby locations (used by Data Guard).
5. Oracle 8i may contain incorrect location in v$archive_dest – Fixed.
6. Does not use the correct SQL statement to obtain the standby database sequence for Oracle 8 and 9 – Fixed.
7. Ignore dynamic memory location parameters and parameters related to other instances in spfile/pfile when creating standby database.
8. When LOGDIR_DR is not set correctly there was an error message about incorrect mail settings – Fixed.

Please download from http://www.dbvisit.com/dbvisit_download.php

Compressing and uncompressing files with Dbvisit

Dbvisit uses its own internal compression and uncompression mechanism to compress and uncompress files during transportation.

These compression routines can be accessed manually through the dbv_functions commands.

To compress a file:

dbv_functions -g filename

To uncompress a file:

dbv_functions -u filename.gz

We have purchased additional hardware for our test and development environment. We have been using VMware server for our other dev/test hardware but wanted to try VMware ESXi.

The software you will need is:

• VMware ESXi 4.0 (to run on the new hardware)
• VMware vSphere Client with vSphere Host Update Utility (to run on the client which will manage the ESXi)
• VMware vSphere Command-Line Interface – Windows Installer (to run on the client which will manage the ESXi)
  Command-line tools to manage your vSphere infrastructure.
• VMware vCenter Converter Standalone (to convert VMware server guests to ESXi)

We wanted to install VMware ESXi on a USB stick. This gives the advantage of easily creating copies of the USB stick (using Linux dd command) in case ESXi did not want to boot. It also allows us to create a clone of the whole ESXi server.

Moving from VMware server to ESXi requires quite a different approach as you no longer are running a full OS on the host. All the things you take for granted by having a full OS are no longer there and this takes a bit of getting used to. You are also no longer as flexible because simple OS command backups do not work or are limited in their features.

The setup of ESXi is very straightforward and easy. However we quickly found one of the short comings to ESXi. Our USB had decided not to work anymore and as we were still in setup phase we had not created a backup yet.

To fix it, we rebooted the server with the ESXi CD and this allowed us the repair the USB stick and bring back ESXi. However all the meta data had gone. The VMware guests where still on the disk, but the vSphere Client did not pickup any of the existing VMware guests or resource pools that we had created. There is no quick refresh button.

This would be a major blow if this was a production server.

However we did find a way to re-register an existing VMware guest using the vSphere CLI tool. This tool is a collection of Perl scripts that are installed on your local machine (can be Windows or Linux) and connects to ESXi to run certain commands.

To re-register a VMware guest, run the following command:

perl vmware-cmd -U <myuser> -P <mypassword> -H HostABC
    -s register /vmfs/volumes/datastore1/MyVM/MyVM.vmx

It helps to know the path of the vmx. This can be found out by logging onto the console of the ESXi server. Typing ALT-F1 and then typing “unsupported”. This brings you to the console of the ESXi server and you can use use find and ls commands to find the location of the vmx file.

This brings back the VMware guests but does not bring back the resource pools. However in the vSphere Client you can re-create the same resource pools and move the VMware guests under the resource pools.

Before we deploy our server we want to make a backup of its configuration so that if something similar happened we can restore the configuration. To do this run the following command from CLI client:

C:\Program Files\VMware\VMware vSphere CLI\bin>perl vicfg-cfgbackup.pl --username root
--password xxxx --server xxxx -s C:\backup\server_esxi_config.dat
Saving firmware configuration to c:\backup\server_esxi_config.dat ...

Other handy commands to run under VMware ESXi console:
View disks with:

fdisk -l

View current disk paths with:

esxcfg-mpath -l

Other handy commands to run under vSphere CLI:
To identify the working directory of the virtual machine from ESXi and the vSphere CLI, run the command:

vmware-cmd.pl -H <host> -U <username> -P <password> -l

To create a folder for the copy of the virtual machine from ESXi and the vSphere CLI, run the command:

   vifs.pl -H <host> -U <username> -P <password> --mkdir '[datastore] dir'

where datastore is the name of the datastore, and dir is the name of the new directory

Dbvisit log gap report

Dbvisit can report on the difference between the primary and the standby database. This is done with the Dbvisit log gap report. To run the Dbvisit log gap report, run Dbvisit as follows on the primary server:

dbvisit11[/usr/local/dbvisit]> dbvisit -i w120n
=============================================================
Dbvisit Standby Database Technology
dbvisit started on dbvisit11: Mon May 24 10:08:35 2010
=============================================================

Contacting Standby Database w120n on dbvisit12...
Last standby sequence obtained (1159) for thread 1.

Dbvisit log gap report for w120n at 201005241008 (thread 1):
-------------------------------------------------------------
Standby database on dbvisit12 is at sequence: 1159.
Primary database on dbvisit11 is at log sequence: 1160.
Primary database on dbvisit11 is at archived log sequence: 1159.
Dbvisit last transfer log sequence: 1159.
Dbvisit last transfer at: 201005240949.

Archive log gap for w120n:  0.
Transfer log gap for w120n: 0.

=============================================================
dbvisit ended on dbvisit11
=============================================================

The report displays the following 2 key values:

• Archive log gap which is the difference between the last archived sequence on the primary and the last applied sequence on the standby database. The archive log gap should be near 0 (except when APPLY_DELAY_LAG_MINUTES is used).
• Transfer log gap which is the difference between the last archived sequence on the primary and the last sequence transferred to the standby server. The transfer log gap should be near 0.

The most important value is the Transfer log gap. This value represents your DR exposure. Having this value near 0 means all the available log files have been transferred to the standby server. They may not have been applied to the standby database, but they are queued up and ready to be applied.

The Dbvisit log gap report also:

• Emails the report to the user specified by the ADMINS email.
• Inserts the information into the Dbvisit repository to be used for trend analysis reporting purposes.

It is recommended to schedule the Dbvisit gap report every hour. This reporting is non-intrusive. No checkpoints are performed and no logs are transferred.

Adding or dropping redo logs to the standby database

If adding redo logs groups or redo logs members to the primary database, what should be done on the standby database?

To add normal redo logs (not standby redo logs) to a primary database, the new redo logs definitions must also be added to the standby database.

A standby database does not have redo logs so they cannot be added with the same SQL (ALTER DATABASE ADD STANDBY LOGFILE) commands as on the primary database. A standby database will only have redo logs when the standby database is activated to become a primary database. The activation process will create the redo logs.

The redo log information is held in the controlfile information on the primary database, so we can replace the standby controlfile with a new controlfile to automatically have the new redo log information available on the standby database.

This can be done in Dbvisit with the following command:

dbv_functions -Q database

Where database is your database name. This command is only run on the primary server.

This command will:

• Shutdown the standby database (It will not shutdown the primary database)
• Recreate the standby controlfile on standby server from the controlfile on the primary server.
• Restart the standby database.

Notes:

1. Ensure that the standby database is up to date before running this command.
2. The standby database must have the same structure as the primary database. If this is not the case, then the controlfile information has to be updated on the standby database once the controlfile has been replaced with the command:

   SQL> alter database rename file 'x' to 'y';

   Each datafile or redo file that is in a different location to the primary has to be renamed.

Dbvisit by default will send an email notification everytime that it is run on the primary and standby server. This is useful during the testing phase so that you get a sense of how Dbvisit is working.

However after the testing phase you only want to be notified by Dbvisit if there is an error or a threshold has been exceeded.
This can be easily done by setting the SUCCESSMAIL=No in the Dbvisit Database Configuration (DDC) file.

For example say your database is called w120n. Your DDC file will be called dbv_w120n.env.
Edit this file with any text editor on the primary server only:

[40 Mail Settings]
MAILCFG_MAIL_CLIENT = dbvisit
SUCCESSMAIL = No
SUCCESSMAIL_DR = No
...

Set both SUCCESSMAIL=No and SUCCESSMAIL_DR=No.
This will ensure Dbvisit will only email when there is an error or an alert on both the primary and standby servers.

Dbvisit will continue to send a heartbeat every day to ensure the emailing functionality is working.

The DDC file contains all the Dbvisit configurational settings. For each setting there is also an explanation into the function of the setting. The DDC should only be updated on the primary server. The next time Dbvisit is run on the primary server, it will automatically copy over the DDC file to the standby server if it detects an update.

We have released Dbvisit 5.2.26.

New features:

1. SOURCE and DESTINATION host name variables are now not case sensitive for Windows.
2. Display a message “please wait” to the user to indicate that Dbvisit may take some time to delete old archive files when running AMM (Archive Management Module) on the primary server.
3. Improve the ability to obtain the standby database sequence when the standby database sequence is 1.
4. Add message to SUCCESSMAIL emails on how to turn off success mails and only email on alerts.
5. Add option to use existing pfile/spfile on standby database during standby database creation.

Fixes:

1. Capture more information when another instance of Dbvisit is running on Windows to ensure it can be started or not.
2. During creation of standby database on Windows, Dbvisit may error when COMPRESS variable is set to ssh in a DDC file – Fixed
3. Dbvisit may error with the primary and the standby databases having redo logs with the same names under different logfile group numbers during Graceful Switchover – Fixed
4. In rare cases Dbvisit may error when trying to clean up trace files if a file has already been deleted by another dbvisit process running at the same time – Fixed.
5. When the user renames oracle init parameters while creating standby database, Dbvisit edits both standby pfile and spfile. If the standby database uses spfile, only spfile should be edited not pfile – Fixed.
6. Dbvisit does not capture ORA-12638 error message – Fixed.
7. Make the global variable PFILE non case sensitive for Windows while creating a standby database
8. While checking oracle database parameters pointing to ASM locations on primary when creating a standby database, dbvisit considers all parameters values that contain symbol + to be ASM locations which is not always the case – Fixed
9. In some cases fuser incorrectly reports file in use on HP-UX.
10. Remove REMOTE_LISTENER parameter from init.ora when creating standby database.

Dbvisit 5.2.26 can be downloaded from our website. Please see the README.txt file included in the download for upgrade instructions.

To test the email functionality of Dbvisit use the dbv_functions -m command.
This will send an email to the email address(es) specified by the ADMINS parameter in the Dbvisit Database Configuration (DDC) file.

For example for the w112g primary database run the command as follows:

$ dbv_functions -m w112g
=============================================================
Dbvisit Standby Database Technology (5.2.25.4075) (pid 3958)
dbv_functions started on dbvisit11: Fri Apr 30 15:38:57 2010
=============================================================

Sending schedular heartbeat message to dba_guru@companyxx.com.

This command can be used to test the email functionality is working. By default Dbvisit will send a test email every day to ensure the email functionality is still working. To specify the time that Dbvisit sends this test or heartbeat email is set by variable SEND_HEARTBEAT_TIME24. By default this is set to 7:00am.

It is also possible to send an attachment in the email. Specify the same command with a valid filename. This filename will then be emailed to the user specified by ADMINS as an attachment:

$ dbv_functions -m w112g dbv_w112g.env
=============================================================
Dbvisit Standby Database Technology (5.2.25.4075) (pid 3958)
dbv_functions started on dbvisit11: Fri Apr 30 15:38:57 2010
=============================================================

Attachment filesize is 33478 bytes

Sending schedular heartbeat message to dba_guru@companyxx.com.
with attachment dbv_w112g.env

In this example the DDC file (dbv_w112g.env) was sent to email dba_guru@companyxx.com as an attachment. You can use this command to send trace files or other relevant Dbvisit files to yourself.

The command can be run both on the primary and the standby database.

Welcome to the Dbvisit tip of the week.

To find transfer information about the last log that Dbvisit has transferred use the dbv_functions -N command run on the primary server. For example for the w112g primary database run the command as follows:

$ dbv_functions -N w112g
Dbvisit Database configuration (DDC) file dbv_w112g.env.
<<<>>>
oracle_sid             => w112g
sequence#              => 4
thread_num             => 1
log_id                 => 716466692
archive_name           => /oracle/flash_recovery_area/W112G/archivelog/2010_04_18/o1_mf_1_4_5wo
process_id             => 3748
process                => TRANSFER
timestamp              => 201004190307
datestamp              => 2010/04/19:03:07
start_time_date        => 2010/04/19:03:08
end_time_date          => 2010/04/19:03:08
source_host            => dbvisit51
destination_host       => dbvisit52
checksum               => cb3eacfd0be802c5999e9fd682c6c46b50a0ccba
size_in_bytes          => 9660283
size_in_mb             => 9.21
process_completed      => Y

To display information about a specific log file add the sequence number:

$ dbv_functions -N 3 w112g
Dbvisit Database configuration (DDC) file dbv_w112g.env.
<<<>>>
oracle_sid             => w112g
sequence#              => 3
thread_num             => 1
log_id                 => 716466692
archive_name           => /oracle/flash_recovery_area/W112G/archivelog/2010_04_17/o1_mf_1_3_5wl
process_id             => 3748
process                => TRANSFER
timestamp              => 201004190307
datestamp              => 2010/04/19:03:07
start_time_date        => 2010/04/19:03:07
end_time_date          => 2010/04/19:03:08
source_host            => dbvisit51
destination_host       => dbvisit52
checksum               => e6ab447f4c4df5bc64868a6b1baece595a8defbd
size_in_bytes          => 9433837
size_in_mb             => 9
process_completed      => Y

The above displays information about the transfer process. It is also possible to display information about the COMPRESS step:

$ dbv_functions -N 3 w112g COMPRESS
Dbvisit Database configuration (DDC) file dbv_w112g.env.
<<<>>>
oracle_sid             => w112g
sequence#              => 3
thread_num             => 1
log_id                 => 716466692
archive_name           => /oracle/flash_recovery_area/W112G/archivelog/2010_04_17/o1_mf_1_3_5wl
process_id             => 3748
process                => COMPRESS
timestamp              => 201004190307
datestamp              => 2010/04/19:03:07
start_time_date        => 2010/04/19:03:07
end_time_date          => 2010/04/19:03:07
source_host            => dbvisit51
destination_host       => dbvisit52
checksum               => 22e4ece6822f1ef951078992600f00a719de1a43
size_in_bytes          => 42861568
size_in_mb             => 40.88
process_completed      => Y

This shows that for sequence 3, the log file was 40Mb in size, and after compression and during transfer it was only 9Mb in size.

We have been working hard on a web based version of Dbvisit and although it is not yet finished we want to give a sneak preview of what the Dbvisit web based version will look like. Even though there will be a new GUI interface, the command line functionality will still be the same as with the current version if you prefer to use command line style.

The Dbvisit web based version will feature its own build in web server and will not rely on any external software, and is not build in APEX. The Dbvisit web based version will be platform independent and will work on Windows, Linux and Unix.

The Dbvisit web version main menu looks like this:

The menu items are easy to understand and are kept simple.

Click on “Setup” and the Dbvisit setup and configuration is an easy to use wizard which has most of the options already filled in and lets you know at which stage of the process you are:

Although Dbvisit is a background batch process, it can be run interactively through the web based version for testing purposes:

The “Standby Server” tab can be selected to run Dbvisit commands on the standby server.

The Dbvisit log files can also be easily displayed through the web based version. It will even show the Oracle Alert log:

As part of the web based version, Dbvisit will show details of the last transfer:

The sub menu provides additional functionality, which includes creating the standby database:

There will also be a reporting function which will report KPI’s like the log gap reports, time taken for the transfer process, size of the log files etc.

We are very excited about this release. There is still more development and testing to do before it can be released. There is no release date set, but as soon as we have a date, we will publish it here.  This version will be released as Dbvisit version 6.0.