Smart Database Technology
Standby Database and Oracle Replication blog
2
After a switch-over or fail-over to an Oracle Standby database using Dbvisit Standby, the Oracle Database Administrator (DBA) faces the challenge of switching clients to the new activated standby database which is now running as the new primary database. Previously these clients were connected to the former primary database, and so they must be reconnected to the new primary database.
Dbvisit Standby as well as Oracle Data Guard does not provide a feature to support this.
Generally there are two options for the DBA to achieve this:
- Change of the database host using either the
- connect string or
- DNS.
- Use of the NET8 listener fail-over.
1 – Change of database host
To begin lets look at option 1. Our examples use the following host and service names:
host name primary server: dbprod host name standby server: dbstandby service name: orcl net service name: orcl_ha
In the case of example 1-a, we are going to change the host name within the TNS connect string after switch-over/fail-over or change the IP from primary server’s to standby server’s.
The tnsnames.ora entry:
orcl_ha= (description= (address = (host = dbprod)(protocol = tcp)(port = 1521)) (connect_data = (service_name = orcl)) )
will be changed after the switch-over/fail-over into:
orcl_ha= (description= (address = (host = dbstandby)(protocol = tcp)(port = 1521)) (connect_data = (service_name = orcl)) )
Or alternatively by using a JDBC-connect:
jdbc:oracle:thin:@dbprod:1521/orcl
will be changed after the switch-over/fail-over into
jdbc:oracle:thin:@dbstandby:1521/orcl
This solution seems to be the easiest way if the changes to be made are limited, or on a single point. This type of configuration is mostly found with clients that have a:
- centralised tnsnames.ora, or
- net service resolution via Oracle Internet Directory, Active Directory or
- any connect string at the Application Server.
This method is only efficient in a centralised names configuration.
The DBA could switch to option 1-b if there is a DNS alias used for the name services. In addition to the host names defined above we define an extra DNS CNAME as dbha, which initially points at the host dbprod.
The tnsnames.ora as well as the JDBC connect string stay as they are after switch-over/fail-over:
orcl_ha= (description= (address = (host = dbha)(protocol = tcp)(port = 1521)) (connect_data = (service_name = orcl)) )
or:
jdbc:oracle:thin:@dbha:1521/orcl
After failover the DNS CNAME dbha is redirected to the host name dbstandby.
The advantage of this method is that it works well for centralised as well as for the de-centralised names configuration services. The disadvantage with this method is that the connect fail-over can have serious time delays, because the new DNS configuration first has to be replicated to the DNS servers. The name service caches of clients also may need to be flushed. So even though the database might be online and available, some of the clients may still not be able to connect because of the DNS update being delayed.
2 – Listener fail-over
The most recommended and most flexible solution is using a dynamic connect listener fail-over. To achieve this the DBA defines dbprod as well as dbstandby into a common connect string and to establish a fail-over between them both.
The related tnsnames.ora entry would be as follows:
orcl_ha= (description= (address = (host = dbprod)(protocol = tcp)(port = 1521)) (address = (host = dbstandby)(protocol = tcp)(port = 1521)) (failover = yes) (connect_data = (service_name = orcl)) )
Also for JDBC the DBA would have to use the complete connect string:
jdbc:oracle:thin:@(description=(address=(host=dbprod)(protocol=tcp)(port=1521))(address=(host=dbstandby)(protocol=tcp)(port=1521))(failover=yes)(connect_data=(service_name=orcl)))
In this configuration it’s essentially to ensure that only the listener at the present primary server is running. Otherwise it’s possible that you run into the problem that the client connections are directed to the standby database, which produces the following error message output:
ORA-01033: ORACLE initialisation or shut down in progress
If the listener is controlled by either Oracle Clusterware, Oracle Grid Infrastructure or Oracle Restart it would be possible to fully automate the start and shut downs of the listeners. This is done by integrating a control resource at the primary and standby server.
For example, create an action script, which is saved as /usr/bin/dbv_lsnr_ctrl.sh and that can be run by the Oracle user:
#!/bin/sh
# Listener status control script for use with Dbvisit standby
# - prevents listener from starting if the corresponding instance has standby role
#
unset DEFAULT_DBV_HOME PRIMARY_ROLE_EXPR INSTANCE_NAME
STANDBY_ROLE_EXPR="^Standby Database"
DEFAULT_DBV_HOME=/u01/app/dbvisit
INSTANCE_NAME=${_USR_ORA_SRV}
# set default Dbvisit Standby home path, if not already set in environment
if [ -z "$DBV_HOME" ]; then
DBV_HOME=${DEFAULT_DBV_HOME}
fi
case $1 in
# handle start request and check equally
# (start/report ok, except when on standby side and instance is running)
start|check)
! ${DBV_HOME}/dbv_oraStartStop status ${INSTANCE_NAME}|grep "${STANDBY_ROLE_EXPR}" >/dev/null 2>&1
exit $?
;;
# stop request always can be fulfilled
stop)
exit 0
;;
esac
# paranoia exit
exit 1
Within the script (or alternatively at the environment variable DEFAULT_DBV_HOME) the DBA should define the Dbvisit Standby installation path. In addition the following control resource should also be created on the primary:
crs_profile \ -create dbv_lsnr_ctrl \ -t application \ -a /usr/bin/dbv_lsnr_ctrl.sh \ -h dbprod \ -p restricted \ -o as=restore,ci=60,ft=0
On the standby we use dbstandby:
crs_profile \ -create dbv_lsnr_ctrl \ -t application \ -a /usr/bin/dbv_lsnr_ctrl.sh \ -h dbstandby \ -p restricted \ -o as=restore,ci=60,ft=0
In directory $CRS_HOME/crs/public the following .cap file will be created: dbv_lsnr_ctrl.cap.
The database name needs to be entered at line USR_ORA_SRV= exactly the same way that Dbvisit Standby commands are executed:
USR_ORA_SRV=orcl (e.g. at "dbvisit orcl")
The action script (/usr/bin/dbv_lsnr_ctrl.sh) is called with a parameter which is used to call the Dbvisit Standby command to obtain the database status:
dbv_oraStartStop status
To add the control resource to Oracle Clusterware:
crs_register dbv_lsnr_ctrl
Finally the dependency of listener and control resource needs to be defined:
crs_profile -update ora.LISTENER.lsnr -r dbv_lsnr_ctrl
After restarting the listener the dependency on the control resource is created. This determines the status of the database as reported by dbv_oraStartStop, whether it is started or not. If dbv_oraStartStop detects a database in standby role, the control resource goes down and prevents the listener from starting. Otherwise it starts and the listener is allowed to run.
Thanks to author: Thilo Solbrig (Oracle Certified Master) at Aspicon – Dbvisit Partner
Oracle provides the Database Upgrade Assistant (DBUA) to automate the upgrade process and makes appropriate recommendations for configuration options such as tablespaces and redo logs. The DBUA also provides support for Real Application Clusters (RAC) and Automatic Storage Management (ASM).
One of the options of DBUA is to temporary switch to NOARCHIVELOG mode during the upgrade to speed up the upgrade. However if the database has an associated standby database, then switching on NOARCHIVELOG will invalidate the standby database with Oracle SE and SE1. In Oracle EE, the redo logs can be used by Data Guard to keep the standby database up to date and this may not be an issue.
The reason that it invalidates the standby database is that there will be a gap in the archivelogs during NOARCHIVELOG and the standby database requires all archives logs in sequence to keep the standby database up to date.
Example:
During an upgrade with DBUA the database was switched to NOARCHIVELOG mode as can be seen from the following query:
SQL> select sequence#,name from v$archived_log where sequence#>4380; SEQUENCE# NAME 4381 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4381_7B5PN1LT_.ARC 4382 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4382_7B5T4KQK_.ARC 4383 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4383_7B5XO1LJ_.ARC 4384 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4384_7B615KV4_.ARC 4385 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4385_7B64P1KQ_.ARC 4386 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4386_7B686KYQ_.ARC 4387 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4387_7B6CQ1D2_.ARC 4388 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4388_7B6H7O6B_.ARC 4389 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4389_7B6KST7V_.ARC 4402 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004402_0749585225.0001 4403 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004403_0749585225.0001 4404 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004404_0749585225.0001 4405 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004405_0749585225.0001 4406 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004406_0749585225.0001 4407 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004407_0749585225.0001 4408 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004408_0749585225.0001 4409 E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004409_0749585225.0001
The logs for sequence 4390-4401 have been not been generated. This means that the standby database cannot be kept up to date. Since the archive logs are not created by Oracle, and you cannot skip archive logs with a standby database, it means the standby database has to be rebuild.
Re-creating the standby database can be easily done with Dbvisit Standby.
Recommendation
Do not switch on NOARCHIVELOG mode during a database upgrade, as this may invalidate your standby database.
When upgrading an Oracle database that has an associated standby database, we recommend the following approach is followed: Upgrading or patching Oracle with a standby database.
We definitely promote using RMAN for backups of your Oracle database. With RMAN you are able to recover up to the last moment before failure (if you have archive log mode enabled). This is as opposed to a facility like export dumps where you are only able to recover to the point when the export was made (or at some point in time before).
With RMAN you can also restore individual files and tablespaces. With export dumps you can restore individual tables but the transactional integrity is not guaranteed if you restore individual objects.
Recovery catalogue
RMAN can be used with or without a recovery catalogue. A recovery catalogue is a repository in a separate database. Usually this recovery catalogue holds the RMAN catalogue of several databases. If you use RMAN without a recovery catalogue, then the recovery information is held in the control files of the database. Even with the recovery catalogue, backup information is held in the control files of the database (incase the recovery catalogue is not available).
RMAN and standby database
If you are not replicating the RMAN catalogue database, then you can use RMAN without a recovery catalogue for initial backups when you failover to the standby database. When a standby database is failed over and becomes the primary database, a resetlog operation occurs and this invalidates all previous backups. After the initial RMAN backups on the new primary server have taken place and things have settled down, you can recreate a RMAN catalogue database to continue RMAN backups with a catalogue.
This may be better explained with the following steps:
ServerA -> primary server
ServerB -> standby server
1) Normal RMAN backups of primary database on ServerA using catalogue.
2) Failover occurs to standby database on ServerB. This now becomes primary database.
3) RMAN initial backups of new primary database on ServerB, using no catalogue.
4) Create new RMAN catalogue database.
5) RMAN backups of primary database on ServerB using catalogue.
The other alternative is to use RMAN without a catalogue for normal backups. This will simplify the fail over process as the re-creation of the catalogue is not needed.
There are no precautions for using RMAN in conjunction with Dbvisit Standby on the primary server. However, there are the following to points consider:
1) If you are using Dbvisit Standby to manage the archive log files on the primary server, you have to let RMAN know that Dbvisit Standby has deleted archive log files. For more information please see:http://www.dbvisit.com/forums/showthread.php?t=13
2) If you set LEAVE_COMPRESS_SOURCE=No (in the Dbvisit DDC file) then Dbvisit Standby will leave the archive files in an uncompressed state and RMAN will be able to find them and backup them up.
3) If you use RMAN with nocatalog, then the RMAN backup information is stored in the primary database controlfile. If you use graceful switchover (or role reversal), then the backup history information in the control file will be lost as a new control file is built during the role reversal process. The only way around that is to use a catalog.
Considerations for Standby Databases using Automatic Storage Management (ASM)
Every file created in ASM gets a system-generated filename, otherwise known as a fully qualified filename (FQFN). The fully qualified filename represents a complete path name in the ASM file system. An example of a fully qualified filename is:
+dgroup2/sample/controlfile/Current.256.541956473
You can use the fully qualified filename to reference (read or retrieve) an ASM file. ASM generates a fully qualified filename upon any request to create a file. A creation request cannot specify a fully qualified filename. Instead, it uses a simpler syntax to specify a file, such as an alias or just a disk group name. ASM then creates the file, placing it in the correct ASM “path” according to file type, and then assigns an appropriate fully qualified filename. If you specify an alias in the creation request, ASM also creates the alias so that it references the fully qualified filename.
FQFN are generally long and awkward, therefore, to make file-naming convention easier to remember the ASM Alias name format was introduced. ASM Aliases are essentially in hierarchical directory format, similar to the filesystem hierarchy. Alias names specify a disk group name, but instead of a file and incarnation number, a user-friendly string name is used.
Alias ASM filenames, otherwise known as aliases, can be used both for referencing existing ASM files and for creating new ASM files. Alias names start with the disk group name preceded by a plus sign, after which you specify a name string of your choosing. Alias filenames are implemented using a hierarchical directory structure, with the slash (/) or backslash (\) character separating name components. You can create an alias in any system-generated or user-created ASM directory. You cannot create an alias at the root level (+), however.
When you create an ASM file with an alias filename, the file is created with a fully qualified name, and the alias filename is additionally created. You can then access the file with either name.
Alias ASM filenames are distinguished from fully qualified or numeric names because they do not end in a dotted pair of numbers. An example of ASM alias for the fully qualified filename above is:
+ dgroup2/sample/controlfile/control01.dbf
Dbvisit currently creates an ASM standby database using aliases for standby database files. If the primary database uses ASM files that do not have aliases defined for them, just FQFN, Dbvisit will prompt you to provide aliases for the standby ASM datafiles. All ASM standby database files will be created with system-generated FQFN and have aliases defined for them. Aliases for standby datafiles, tempfiles, controlfiles and redo logs will be used in the database dictionary.
To use Dbvisit to create an ASM standby database or a standby database for an ASM primary database, make sure that the operating system user that runs Dbvisit has access to Oracle executables in the ORACLE HOME for the ASM instance. Dbvisit needs execute privilege on the ASMCMD utility, and if your installation implements Oracle 11g grid infrastructure under its own home and user, you may need to add the Dbvisit user to the operating group “oinstall” to make sure it can run the ASMCMD utility.
Replacing the standby controlfiles on the standby server
If for any reason the standby controlfiles have become corrupted or have been accidentally removed on the standby server, Dbvisit can re-create the standby controlfiles from the primary server without any outage on the primary database.
To re-create the standby controlfiles use the dbv_functions -Q option:
dbv_functions -Q w112g Dbvisit Database configuration (DDC) file dbv_w112g.env. =>Replace current standby controfiles on dbvisit52 with new standby controlfile?[No]: yes Starting... Completed. Transferring X.dbvisit.10010921.w112g.standbycontrolfile.ctl to host dbvisit52:X.dbvisit.10010921.w112g.standbycontrolfile.ctl Initiating replacing standby controlfiles on standby server... Dbvisit Database configuration (DDC) file dbv_w112g.env. Completed. Database w112g on dbvisit52 is already down. No action is taken. Completed. Standby controfiles on dbvisit52 replaced.
This command has created a new standby controlfile from the primary database, copied this to the standby server and replaced the existing standby controlfiles with the new copy. It will shutdown and restart the standby database during this process.
Running Dbvisit on the standby server will be back to normal with Dbvisit applying the archive logs files as per usual:
dbvisit w112g ============================================================= Dbvisit Standby Database Technology (pid 26054) dbvisit started on dbvisit52 ============================================================= Log file(s) for w112g from dbvisit51 will be applied to dbvisit52... 201006122346 - Log seq 21 thread 1 applied to standby database w112g. Starting Dbvisit Archive Management Module (AMM)... Dbvisit Archive Management Module (AMM) (Number to keep: 0) (Days to keep: 7) (Diskspace full threshold: 80%) Archive log dir: /oracle/app/oracle/oraarch/w112g Total number of archive files : 9 Number of files deleted : 0 Current Disk percent full : 64% ============================================================= dbvisit ended on dbvisit52 =============================================================
The location of the controlfiles for the standby database must be same as the location of the controlfiles for the primary database for the dbv_functions -Q command to work.
For other Dbvisit tips and tricks please see: http://blog.dbvisit.com/category/dbvisit-tips/
Inserting data to generating archive logs
For testing purposes, Dbvisit has the facility to create test data. This simulates the loading of the databases with data to generate new archive logs. Dbvisit can then be tested with “full” archive logs to determine if the network and hardware is sufficient to be able to handle the capacity.
To generate test data use the dbv_functions -T option:
dbv_functions -T w120n >>> Creating test table and inserting test data into w120n. Test table dbvisit.dbv_test_data created. Test table dbvisit.dbv_test_data dropped.
The data is inserted into a new table created in the Dbvisit database schema and then the table is dropped after completion of the insert to ensure that the database is left in the same state as when the command was started.
To add more data, a cycle number can be given:
dbv_functions -T w120n 2 >>> Creating test table and inserting test data into w120n. Test table dbvisit.dbv_test_data created. Insert cycle 1 completed. Rows inserted: 49974 Insert cycle 2 completed. Rows inserted: 99948 Test table dbvisit.dbv_test_data dropped.
This has added 99,948 rows and then dropped the table.
The higher the cycle number, the more data is added:
dbv_functions -T w120n 5 >>> Creating test table and inserting test data into w120n. Test table dbvisit.dbv_test_data created. Insert cycle 1 completed. Rows inserted: 49974 Insert cycle 2 completed. Rows inserted: 99948 Insert cycle 3 completed. Rows inserted: 199896 Insert cycle 4 completed. Rows inserted: 399792 Insert cycle 5 completed. Rows inserted: 799584 Test table dbvisit.dbv_test_data dropped.
This has added 799,584 records to the database, and then dropped the table.
After running the command, Dbvisit can be run as normal to transfer the archive log files to the standby server.
To see the size of the archive logs and statistics of the transfer see http://blog.dbvisit.com/dbvisit-tip-of-the-week-1/
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
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:
- Ensure that the standby database is up to date before running this command.
- 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.
Back to top