Oracle DBA Place

Previously, you could only update Global Temp Tables on Active Data Guard standby database. From Oracle  19c , you can also update regular tables. Updates on Active Data Guard: Update will be redirected to the primary Primary makes update, generates & sends redo for that update to all standbys Active Data Guard session sees the update in redo apply and resumes Preserves ACID properties for ADG session Redirected update only visible to session before commit; visible to all sessions after commit For “Mostly Read, Occasional Updates ” applications – for example, recording user logins for auditing purposes Enabled by ADG_REDIRECT_DML at system or session level The following setup is based on Active Data guard configuration is MaxAvailability, but the behavior is the same when configuring MaxPerformance A step by step guide to demonstrate this feature: Current configuration Basic setup  The setup is primary database sends its redo logfiles through a f...

Hi, In an earlier version of Data Guard, when the broker had problems, one of the automatic answers I got from support was to recreate the broker configuration. In other words: Drop the configuration.  Create the configuration. If your broker is simple, this is not a huge request. Actually, you could follow Step By Step How to Recreate Data Guard Broker Configuration (Doc ID 808783.1) . But in my case, using Active Data Guard with Far Sync, with many fine-tuned configurations, it was not so convenient. I had a number of ways I tried to be efficient: I could write down all broker commands - to be able to run them again next time. For versions 11.2.0.4 and 12.1.0.2, I found a way to query the broker and re-create a current configuration creation script ( Extract Data Guard Commands ). I also had some issues trying to Drop/remove Far-Sync Configurations from broker 12.1 . On version 12.2.0.1, I found out that the broker metadata was changed, so I had to update my previo...

Problem - Automatic Workload Repository (AWR) snapshots cannot be taken in a read-only standby environment. - Performance monitoring and analysis is limited to basic STATSPACK functionality . Solution In Oracle Database 12.2 , the AWR framework is enhanced to support capture of remote snapshots from any generic database, including Active Data Guard (ADG) databases. This framework is called the Remote Management Framework (RMF). • A target catalog database collects snapshots from the remote databases (sources). • Snapshots can be collected automatically or manually. • AWR tables on the catalog database accumulate snapshot data from all sources via database links. • Source databases must be registered on the catalog via new DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE API These are the basic steps for setting up the RMF topology for generating AWR on the physical standby: 1. Configure database nodes to add to the topology. 2. Create the topology. 3. Register database...

Hi, These days I am starting to work on Oracle 12.2, leaving behind the old versions. Since my main domain is Oracle Data Guard,  I posted a blog about Oracle 12c Release 2 New Features for Active Data Guard , one of a big new feature I missed is that the internal Data Guard broker configuration was changed dramatically on 12.2. Last week, I tried to " Extract Data Guard Commands ", and realized it is not working as before :( So I sat down and adjusted it to work on 12.2, this time I enhanced the output with some more important information. Run the following script as sysdba displayconfig122.sql: PROMPT disable FAST_START FAILOVER; PROMPT disable configuration; PROMPT remove configuration; DECLARE    rid                      INTEGER;    indoc                    VARCHAR2 (4000);    outdoc             ...

Prior to Oracle 12.2 we had to find some homemade tricks to run the Observer at the background this way: $ nohup dgmgrl -silent sys/P@$$w0rd@prmy_db "start observer" & Finally, From Oracle 12.2 the Observer can run in the background using wallet authentication to connect to primary & standby databases and the far sync instance. For configuring the wallet use the steps described in my post: " Creating a wallet - secure external password store " Starting the observer at the background using the wallet credentials: DGMGRL> START OBSERVER myobserver IN BACKGROUND FILE IS observer.dat LOGFILE IS observer.log CONNECT IDENTIFIER IS prmy_db Submitted command "START OBSERVER" using connect identifier "prmy_db"  You can follow the progress of the observer looking at the logfile: $ tail observer.log Connected to "prmy_db" Connected as SYSDBA. [W000 11/10 21:14:08.47] FSFO target standby is stndby_db [W000 11/10 21:...

Setting up an Observer Observer is an automatic data guard broker, it can be installed with either the Oracle Client Administrator software or the full Oracle Database software. It is preferable that the observer would run on a different host than the primary and standby databases, using the same network as any end-user client or application. The host should be located In a third, independent location or at least isolated as much as possible from the standby database. Make the observer highly available by configuring auto restart on the same server or on an alternate host. These are the steps for setting up the observer: Enable flashback on Primary & Standby: Primary: sqlplus> alter database flashback on ; Standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; alter database flashback on ; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Update db_lost_write_protect on Primary & Standby: SQL> alter system set db_lost_write_protect=...

I have just came across a meta parameter called DB_ULTRA_SAFE that sets the values for all of mentioned parameters in Far Sync Best Practice Recommendations (for Oracle 12.1) . When DB_ULTRA_SAFE=DATA_ONLY   • DB_BLOCK_CHECKING => MEDIUM.   • DB_LOST_WRITE_PROTECT => TYPICAL.   • DB_BLOCK_CHECKSUM => FULL. When DB_ULTRA_SAFE=DATA_AND_INDEX   • DB_BLOCK_CHECKING => FULL.   • DB_LOST_WRITE_PROTECT => TYPICAL.   • DB_BLOCK_CHECKSUM => FULL. When DB_ULTRA_SAFE=OFF   • When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.

Hi, At Oracle Open World 2017 I collected the main improvements and changes  are going to be implemented in Oracle Database 18c: RMAN - Refresh a standby from primary/backup using single RECOVER command Data Guard - In Memory Column store works with multi-instance redo apply - Multi-instance redo apply support with Block Change Tracking (BCT) - Global Temporary Table support from Standby. - DML operations on Standby – Redirection to Primary without ACID compromise. - Preserve buffer cache during role-change. - No logging enhancement - Performance mode and Availability mode. Yossi Nixon

These are the steps of creating Far Sync instance in the command line: Create 12.1 primary db Create 12.1 physical standby Create regular standby DG broker configuration modify network file (on primary and standby) and add entries for the far_sync instance, add to tnsnames.ora on Primary hosts and standby hosts: FarSyncInst =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = FarSyncHost)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = FarSyncInst)       (UR=A)     )   )     Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance. Copy tnsnames.ora to new farsync server – ensure all hosts mentioned in tnsnames are written also in /etc/hosts Add to listener.ora in far sync server LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =     ...

Hi, This post is a continuation of my post, Using Standby as an Alternate for Far Sync (12c): Limitations and Considerations . It is for database administrators who want to set up Far Sync for the first time. Creating a Far Sync instance requires a number of manual steps and there are many parameters to consider or set. I recently ran numerous scenarios using Oracle™ Active Data Guard with a Far Sync instance. Our goals were to: • Check Far Sync functionality and reliability • Validate that Far Sync has a low footprint under heavy load After running a large number of benchmark scenarios, where we changed one parameter at a time, here are my recommendations for using Active Data Guard with Far Sync: The primary database, the standby database, and the Far Sync instance should all be using the same software version and patch level; if not, upgrade the database(s) to the latest version with the latest patch sets. Consider changing the following database initialization parameters...

When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database parameters (local_listener, remote_listener) Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts. In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration Show the environment DGMGRL> show configuration Configuration - dr   Protection Mode: MaxAvailability   Members:   pdb7 - Primary database     fdb7 - Far sync instance       sdb7 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status upd...

Based on Oracle 12.1 The Recommended Data Guard configuration is in Maximum Availability mode, when using Far Sync which is located near the Primary: Primary Database  à Far Sync Instance - Network input/output (I/O) is synchronous (Sync). Far Sync Instance  à  Standby Database – Network I/O is asynchronous (Async). Primary Database  à  Standby Database  – As an alternate (when Far Sync is not reachable), network I/O is asynchronous (Async). Using SQL If you do not use Data Guard Broker (dgmgrl), use the parameters specified below. ·         Primary On the Primary Database Mandatory Parameters Parameter Value Remarks LOG_ARCHIVE_DEST_2 point to Far Sync service Alternate log_archive_dest_3 Switches to the standby destination, after the number of failures specified in the next parameter is reached. max_failure 1 This val...