Click to learn more about author Gazanfurali Mohammed.
Most cloud databases offer zero downtime for maintenance activities – such as software upgrades – by allowing rolling upgrades. In rolling upgrades, a few nodes are upgraded at a time while the rest of the nodes serve the production operations.
In the case of Oracle databases, one of the options to upgrade with minimal downtime is to use Logical Standby. Here, zero downtime could be anything between a few secs to a few minutes depending on database transactions happening at the time of switchover.
Logical standby setup consists of a primary database which we intend to upgrade to a higher version and a Logical standby database which is a copy of the primary database. In this setup, the Logical standby database eventually at a higher version than the primary database and still be in synchronization with primary using SQL apply. The only downtime required is to promote a logical standby database to the Primary.
First, set up a Physical Standby for your Primary, and then you convert physical Standby into logical Standby. Secondly, open Logical Standby in read-write mode and upgrade it to higher version while primary is serving the production operations. Next, restart SQL apply to keep Logical Standby in synchronization with Primary. Finally, need to switchover from primary to logical Standby, which is your new Primary.
What is a Logical Standby Database?
A Logical Standby is a copy of a Primary Database and have identical logical structure as primary but can have an identical or different physical structure. Uses SQL Apply to keep in synchronization with Primary.
Physical Standby Vs. Logical Standby Database
Although the process of transmitting redo data is the same, the main difference is that physical standby uses redo applies, and logical Standby uses SQL apply to keep synchronized with Primary. Physical Standby is a replica of Primary database, and hence log apply service does block per block media recovery. Logical Standby uses log miner technique to mine the redo logs or archived logs, converts it into DDL or DML statements and executes it in standby database.
The physical Standby can be opened in read-only mode using active data guard feature to offload read-only applications. Whereas, Logical Standby can be opened in read-write mode and unload applications that read data mostly and writes seldom. Logical Standby also allows to create additional indexes, materialized views to optimize read operations.
Setup Logical Standby
If you already have a physical standby database, then you need to convert it to logical Standby by making a few configuration changes. If you do not have a physical standby database, then you should setup physical Standby first. To create a Physical Standby, you need hardware identical to Primary database in terms of its resources such as CPU, RAM, and Network bandwidth, because standby database can become your primary database in the event of unplanned or planned outages and it should be able to support production operations without any performance degradation.
Some Data Type Limitations on Logical Standby
Oracle uses SQL apply to keep logical Standby synchronized with its primary database. SQL apply converts every user data change on primary into SQL statements and execute it on Logical Standby. However, some database data type changes such as BFILE, ROWID, UROWID, Collections, objects with nested tables, Identity columns do not get translated into SQL statements, and hence those changes do not get applied on logical standby database. Identify the list of tables that contain unsupported data types and find out their sizes.
If there are only a few small tables, check if you can do export and import of those tables into logical Standby.
When database-wide Primary- key and unique-constraint/index supplemental logging is enabled, each update statement also writes the column values necessary in the redo log to identify the modified row in the logical standby database uniquely. If there is no primary key and no nonnull unique constraint/index, then all columns with a declared maximum length of 4000 bytes are logged as part of the update statement to help identify the modified row, which can slow down the SQL Apply rate and lead to data sync delays between logical Standby and Primary. Consider creating primary keys on such tables.
Optimize SQL Apply Performance
Keeping pace with primary workload is essential to minimize failover time, and for queries and reports running on the logical standby database to return results that are up-to-date with primary database transactions. There is always 1 Reader process, 1 Builder process, and 1 Analyzer process in addition to the 1 Coordinator process. It is possible to have multiple Preparer processes, and this defaults to 1 preparer process per 20 applier processes.
Following are some of the parameters which can be tuned to improve SQL apply performance.
MAX_SERVERS: Number of processes that SQL Apply uses to read and apply redo
MAX_SGA: Number of megabytes from shared pool in System Global Area (SGA) that SQL Apply uses
PREPARER_SERVERS: Number of preparer processes used to prepare changes
APPLY_SERVERS: Explicitly sets the number of Apply servers
Following are the sample commands to set these parameters.
exec DBMS_LOGSTDBY.APPLY_SET (‘MAX_SERVERS’, 12);
exec DBMS_LOGSTDBY.APPLY_SET (‘MAX_SGA’, 100);
exec DBMS_LOGSTDBY.APPLY_SET (‘TRANSACTION_CONSISTENCY’, ‘READ_ONLY’);
exec DBMS_LOGSTDBY.APPLY_UNSET(‘MAX_SGA’);
You can speed up the process of committing transactions to the logical standby database by setting PRESERVE_COMMIT_ORDER to false if the order of transaction commits on the primary database is not essential to you.
How to Recover from a Failed Statement in Logical Standby
Although we check for unsupported data types before setting up logical Standby, there could be a situation where transactions could fail on logical Standby due to various reasons. For example
1. Having the SQL Apply engine skip transactions without properly issuing a compensating transaction.
2. Providing users improper access to objects on Logical Standby
3. Performing unsupported operations on the Primary.
4. Modifying user data as the SYS user.
If a single table on logical Standby goes out of synchronization with Primary, dbms_logstdby. instantiate_table can be used to bring it back into sync. If multiple tables go out of sync, you can take an export dump from the primary and import it into logical Standby,
On the Logical: SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
expdp system/system tables=adp.test1 dumpfile=expadp.dmp flashback_scn=937485759
Use the restart_scn for the flashback_scn.
Database Upgrade and Switch Over to Logical Standby Database
Once Primary and Logical Standby is in synchronization and you do not see any unsupported events in DBA_LOGSTDBY_EVENTS, Upgrade the logical standby database following Oracle upgrade documents. Restart SQL apply to synchronize the data changes that might have happened on the primary database while the logical database was being upgraded. Plan and prepare for minimal downtime on Primary to switchover to a logical standby database that is your new Primary.