oracle data guard - ghdrako/doc_snipets GitHub Wiki

obraz

The types of standby databases are as follows:

  • Physical standby database
  • Logical standby database
  • Snapshot Standby Database A

Protection modes available in Oracle Data Guard

  1. Maximum Protection
  2. Maximum Availability
  3. Maximum Performance

Show protection mode of primary database

SELECT PROTECTION_MODE FROM V$DATABASE;

Change protection mode

ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];

DB_FILE_NAME_CONVERT parameter - used when you are using different directory structure in standby database compare to primary database data files location.

Oracle Data Guard Services

  • Redo Transport Services
    • Transmit redo data from the primary system to the standby systems in the configuration
    • Manage the process of resolving any gaps in the archived redo log files due to a network failure
    • Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database.
  • Apply Services - automatically apply the redo data on the standby database to maintain consistency with the primary database.
    • For physical standby databases, applies redo data on the standby database using standard recovery techniques of an Oracle database.
    • For logical standby databases,first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.
  • Role Transitions - change the role of a database using either a switchover or a failover operation. An Oracle database operates in one of two roles: primary or stand by. A switchover is a role reversal between the primary database and one of its standby databases.

(DGMGRL) to:

  • Create and enable Oracle Data Guard configurations, including setting up redo transport services and apply services
  • Manage an entire Oracle Data Guard configuration from any system in the configuration
  • Manage and monitor Oracle Data Guard configurations that contain Oracle RAC primary or standby databases * * Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager Cloud Control or a single command in the DGMGRL command-line interface.
  • Enable Oracle Data Guard fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Oracle Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically,

dgmgrl (Data Guard Manager Command Line Interface) to narzędzie dostarczane przez Oracle, umożliwiające zarządzanie konfiguracjami Oracle Data Guard w sposób zcentralizowany i ustandaryzowany. Poniżej przedstawiamy szczegółowy opis funkcjonalności, zastosowań oraz wymagań tego narzędzia.

dgmgrl (Data Guard Manager Command Line Interface) to główne narzędzie używane do:

  • Konfiguracji i zarządzania Data Guard Brokerem: Umożliwia tworzenie, modyfikację i monitorowanie konfiguracji Data Guard.
  • Monitorowania stanu konfiguracji: Pozwala sprawdzać, czy wszystkie bazy danych w konfiguracji działają prawidłowo oraz są poprawnie synchronizowane.
  • Przeprowadzania operacji administracyjnych: Narzędzie umożliwia wykonywanie operacji przełączeń (switchover), awaryjnego przełączenia (failover) czy weryfikacji integralności konfiguracji.

dgmgrl upraszcza proces zarządzania rozproszonymi środowiskami, gdzie kilka baz danych musi współpracować w ramach jednej konfiguracji Data Guard.

Łączenie się z konfiguracją:

dgmgrl sys/hasło@nazwa_aliasu_bazy

DGMGRL> SHOW CONFIGURATION;   
DGMGRL> SHOW DATABASE 'nazwa_bazy';           # szczegoly konfiguracji danej bazy




DGMGRL> SWITCHOVER TO 'nazwa_bazy_zapasowej'; # przelaczenie na baze zapasowa
DGMGRL> VALIDATE DATABASE 'nazwa_bazy';       #  weryfikacji konfiguracji

Aby móc korzystać z dgmgrl, należy spełnić następujące wymagania:

  • Oracle Enterprise Edition: Data Guard jest funkcjonalnością dostępną głównie w wersji Enterprise.
  • Skonfigurowany Data Guard Broker: Przed użyciem dgmgrl, system musi mieć poprawnie skonfigurowaną infrastrukturę Data Guard Broker, w tym odpowiednią konfigurację bazy głównej i zapasowych baz danych.
  • Odpowiednie uprawnienia: Użytkownik łączący się do konfiguracji powinien posiadać odpowiednie uprawnienia (np. SYSDBA), aby móc wykonywać operacje administracyjne.

Oracle Data Guard Startup & Shutdown Steps

Data Guard Shutdown Sequence

  • Stop log apply service or MRP and shutdown the standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUT IMMEDIATE;
  • Stop log shipping from primary and shutdown primary database
SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER';
SQL> SHUT IMMEDIATE;

Data Guard Startup Sequence

  • Startup primary database and enable log shipping
SQL> STARTUP;
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE';
  • Startup standby and enable log apply service or MRP
SQL> startup nomount;
SQL> alter database mount standby database;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Dataguard Switch Over

  • Step 1:- Verify the database is ready for switch over or not?
SQL> alter database switchover to ORCLSB verify;
  • Step 2:- Check database role and database name
    • Primary DB: select name,open_mode,database_role from v$database;
    • Standby DB: select name,open_mode,database_role from v$database;

Precheck for Switchover:-

  • Step 3:- Verify the state of data guard on both the databases with following SQL queries:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

  • Step 4:- select switchover_status from v$database;

  • Step 5:- on Primary DB

SQL> alter database commit to switchover to standby;

Step 6:- on Primary DB

shutdown immediate;
startup nomount;
alter database mount standby database;
  • Step 7:- Primary DB
select name,open_mode,database_role from v$database;
alter database recover managed standby database disconnect from session;
  • Step 8:- Standby DB:
alter database commit to switchover to primary;
  • Step 9:- Standby DB:
select name,open_mode,database_role from v$database;

DB_UNIQUE_NAME

In a Data Guard environment, the primary and standby databases share the same DBID and database name. To be eligible for registration in the recovery catalog, each database in the Data Guard environment must have different DB_UNIQUE_NAME values.

The DB_UNIQUE_NAME parameter for a database is set in its initialization parameter file.

Troubleshooting

  • SHOW CONFIGURATION LAG - show actual configuration (who is primary, who is standby) and the lag between the databases
. oraenv <<< DBTEST
dgmgrl / "show configuration lag;"

If the lag is bigger than a couple of seconds or if you have errors in your configuration, you will need to do some troubleshooting.

  • Fast Recovery Area on your standby database
select  sum(PERCENT_SPACE_USED-PERCENT_SPACE_RECLAIMABLE) "Real FRA usage %" from v$flash_recovery_area_usage;

If the FRA is almost full, you can remove older archivelogs, for example those older than 2 days if your standby has a 1-day lag:

rman target /
delete force noprompt archivelog all completed before 'sysdate-2';
exit;
  • standby_file_management - it must be set to AUTOMATIC: it means that any file created on the primary will be created on the standby.
show parameter standby_file_management
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 AUTO

If standby_file_management is set to MANUAL, your standby will not be in sync anymore as soon as a new datafile is created on the primary. You will need to manually create the file on the standby to continue the sync.

The MANUAL mode exists because some older configurations had different filesystems on primary and on standby and didn’t use OMF, meaning that standby database cannot guess where to put the new file.

Both primary and standby databases must have this parameter set to AUTO nowadays.

  • Cross test connexions

From your primary:

sqlplus sys@DBTEST_CR as sysdba
sqlplus sys@DBTEST_IN as sysdba

From your standby:

sqlplus sys@DBTEST_CR as sysdba
sqlplus sys@DBTEST_IN as sysdba
  • Recover standby database from service

If you still struggle to get your standby back in sync, because too many archivelogs are missing or because the archivelogs are not on the primary site anymore, you can use this nice RMAN command on your standby:

sqlplus / as sysdba
alter system set dg_broker_start=false;
exit;

srvctl stop database -db DBTEST_CR
sleep 10
srvctl start database -db DBTEST_CR -o mount

rman target /
recover database from service 'DBTEST_IN';
exit;

sqlplus / as sysdba
alter system set dg_broker_start=true;
exit;

This RECOVER DATABASE FROM SERVICE will do an incremental backup on the primary to recover the standby without needing the missing archivelogs. It’s convenient and much faster than rebuilding the standby from scratch.

  • Check SCN - on primary and standby
select current_scn from v$database;
  • Check alert_DBTEST.log and drcDBTEST.log on both servers

Never miss an error reported in the alert_DBTEST.log on both sides. I would recommend disabling the Data Guard configuration, doing a tail -f on both alert_DBTEST.log files, and enabling back the configuration:

. oraenv <<< DBTEST
dgmgrl / "disable configuration;"
sleep 60
dgmgrl / "enable configuration;"

There are also dedicated trace files for Data Guard, at the same place as alert_DBTEST.log: drcDBTEST.log. You may find additional information for troubleshooting your configuration in these files.

Primary Database Changes That Require Manual Intervention at a Physical Standby

Primary Database Change Action Required on Physical Standby
Database Adding a Data File or Creating a Tablespace No action is required if the STANDBY_FILE_MANAGEMENT database initialization parameter is set to AUTO. If this parameter is set to MANUAL, the new data file must be copied to the physical standby database.
Dropping Tablespaces and Deleting Data Files Delete data file from primary and physical standby database after the redo data containing the DROP or DELETE command is applied to the physical standby.
Using Transportable Tablespaces with a Physical Standby Database Move tablespace between the primary and the physical standby database
Renaming a Data File in the Primary Database Rename the data file on the physical standby database.
Add or Drop a Redo Log File Group Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary.
NOLOGGING or Unrecoverable Operation Use the RMAN command RECOVER ... NONLOGGED BLOCK to replace the invalid blocks on the standby with the changed blocks from the primary.

Renaming a Data File in the Primary Database

When you rename one or more data files in the primary database, the change is not propagated to the standby database. It must be done manually even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

--on primary db
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf 3.
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE -> '/disk1/oracle/oradata/payroll/tbs_4.dbf' -> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;

-- on standby db
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN;
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
SQL> STARTUP MOUNT;
SQL> Alter System set STANDBY_FILE_MANAGEMENT = MANUAL;
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' -> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
SQL> Alter System set STANDBY_FILE_MANAGEMENT = AUTO;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -> DISCONNECT FROM SESSION;