Backup Recovery   «Prev  Next»

Lesson 6 Automated standby database
Objective Set up a standby database in Oracle

Automated Standby Database

How do I set up a "standby database" in Oracle 19c when using the process of Fast-Start Fault Recovery?
Setting up a standby database in Oracle 19c while using Fast-Start Fault Recovery involves several steps, including configuring the Data Guard environment and enabling parameters to optimize recovery using Fast-Start Fault Recovery. Here’s how you can achieve it:
  1. Primary Database Setup
    1. Enable Archiving Ensure that the primary database is in ARCHIVELOG mode and archiving is enabled: 
      SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
    2. Configure Initialization Parameters Add the following parameters to the init.ora or spfile: 
      # Data Guard Configuration
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/arch'
log_archive_dest_2 = 'SERVICE=standby_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
log_archive_format = '%t_%s_%r.arc'

# Set Fast-Start Fault Recovery Parameters
fast_start_mttr_target = 300     # Mean Time to Recover (seconds)
log_checkpoint_timeout = 1800     # Optional: Adjust checkpoint interval
db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size = 10G  # Adjust as needed
    3. Backup Primary Database Create a physical backup of the primary database using RMAN: 
      rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG FORMAT '/u01/backup/primary_%U';
  2. Standby Database Setup
    1. Restore the Primary Backup on the Standby Server Transfer the backup files to the standby server and restore the database: 
      rman target /
RMAN> SET DBID <Primary_DBID>;  -- Get from the primary database
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM '/path/to/primary_controlfile.ctl';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
    2. Configure Initialization Parameters On the standby database, configure init.ora or spfile with the following: 
      db_name = pets
db_unique_name = standby
control_files = ('/u01/app/oracle/oradata/standby/control01.ctl')
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/arch'
log_archive_dest_2 = 'SERVICE=primary_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primary'
db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size = 10G
standby_file_management = AUTO
fal_server = primary
fal_client = standby
  3. Enable Data Guard
    1. Start Redo Apply on the Standby Start the redo apply process to keep the standby in sync: 
      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
    2. Verify Data Guard Configuration On the primary database, verify the Data Guard setup: 
      SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;
  4. Fast-Start Fault Recovery Configuration Fast-Start Fault Recovery helps reduce downtime by minimizing the Mean Time to Recovery (MTTR).
    1. Set FAST_START_MTTR_TARGET On both the primary and standby databases, set the following: 
      SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300;
      - The value is in seconds and represents the maximum allowable time for crash recovery.
    2. Enable Flashback Database (Optional but Recommended) Flashback Database can be used to recover from accidental changes quickly: 
      SQL> ALTER DATABASE FLASHBACK ON;
    3. Monitor MTTR Settings Monitor recovery performance using the following query: 
      SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR FROM V$INSTANCE_RECOVERY;
  5. Testing the Configuration
    1. Switch Log Files on the Primary Force log file switches to test redo log shipping: 
      SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
    2. Verify Log Shipping and Apply On the standby database: 
      SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
    3. Simulate Failover If you want to test failover:
      1. Stop the primary database.
      2. Convert the standby database into the primary: 
        SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
      3. Point clients to the new primary database.
Summary of Key Parameters for Fast-Start Fault Recovery
Parameter Description
FAST_START_MTTR_TARGET Sets the maximum crash recovery time (in sec).
DB_RECOVERY_FILE_DEST Location for Flashback logs and recovery files.
DB_RECOVERY_FILE_DEST_SIZE Maximum size of recovery area.
FAL_SERVER Specifies the primary database for redo fetch.
FAL_CLIENT Specifies the standby database name.
STANDBY_FILE_MANAGEMENT Automatically manages standby datafiles.
With this configuration, your Oracle 19c database will efficiently manage a standby database using Fast-Start Fault Recovery. Let me know if you need further clarification or assistance!

Oracle Backup Recovery

Circumstances in which "Automated Standby Database Feature" is initiated

In the event of a
  1. power failure,
  2. hardware failure, or
  3. a physical disaster,
such as fire, flood, or earthquake, that causes an instance to crash, the automated standby database feature within Oracle can be a data lifesaver. While Oracle has provided the standby database feature for a number of years, the technology is significantly extended within Oracle8i. Copying or cloning the production database initially creates a standby database. As archived redo logs are generated on the production database, they are concurrently applied to the standby database. This allows the standby database to remain synchronized with the production database.
Legacy Reference: Oracle8i transfers logs automatically
Prior to Oracle8i, the archived redo logs had to be manually transported or copied to the standby database and manually applied. With the automated standby database within Oracle8i, the archived redo logs are automatically transferred and applied. This eliminates the need for manual procedures to copy and transmit the redo logs and the need for the operator at the backup site to manually specify which logs to apply. Automating this process eliminates a potential source of human error and increases database and application availability. Up to four standby databases can be maintained in a constant state of media recovery through the automated application of archived redo log files from the primary site. The automated standby database resides at any location, taking over the processing from the primary production database, and providing nearly continuous database availability. In the event of a failure of the primary database, one of the standby systems can be activated, providing immediate system availability. Oracle provides the commands and internal verifications for the creation and maintenance of the standby databases. A standby database uses the archived redo log information from the primary database, so it is ready to perform recovery and go online at any time. When the primary database archives its redo logs, the logs must be transferred to the remote site and applied to the standby database. The standby database is therefore always one or two logs behind the primary database in time and transaction history.

Creating a standby database

The following are the steps involved in creating a standby database:
  1. Make a copy of the primary init.ora file to create a standby init.ora file.
  2. Obtain a list of datafiles within the production database by querying the V$DATAFILE system view.
  3. Shut down your primary database cleanly.
  4. Make a consistent backup of the datafiles of your primary database using an O/S utility.
  5. Open the primary database and create the control file for your standby database.
  6. Archive the current online redo logs of the primary database.
  7. Transfer the standby database control file, archived log files, and backed up datafiles to the standby site/host using operating system commands.
Archiving the current online redo logs ensures consistency among the datafiles, the control file, and the redo log files.
  • Manual Recovery
    The following steps are involved in placing the standby database in manual recovery mode:
    1. Configure the initialization parameters for the standby site
    2. Start the standby instance and mount it in standby mode
    3. Transfer the archived redo logs to the desired location on the standby host
    4. Place the standby database in managed recovery mode

    The standby database can be on the same host as the production database or can be on a different host. It is recommended to have the same file structure on the standby database host as in the primary database host. Moving archived files to either a local or remote host, where the standby database resides, is automated with Oracle8i. Oracle keeps the standby database synchronized with the primary database by waiting for archived logs from the primary and then automatically applying them to the standby. This feature eliminates the need for manually providing the recovery process along with the filenames of the archived logs. For details on operating system changes for creating a standby database, please refer to Oracle’s Operating system-specific manuals. For details on creating a standby database on a different host machine or with a different directory structure, refer to Oracle Backup and Recovery Documentation. The next lesson explains more about read-only databases.

SEMrush Software Target 5SEMrush Software Banner 5