ORACLE DATA GAURD
Architecture of Datagaurd :
=========================
When we have just primary database , it fills log buffer , whenever any changes are made to the database and LGWR[log writer] process does it works and writes to online redo log files. When log switch occurs ARCn process awakes and content of online redo log files will be flushed to Archive redo log files.
Now , how it works with Standby database configured ?
LNS process of primary database captures redo from redo log buffer.
Send it to RFS process of standby database through oracle net.
RFS process then writes that redo information to standby redo log files.
If LNS process is not fast enough to capture redo information before it goes to online redo log files or if redo data are going online redo log files very quickly then LNS process will read from Online redo log files and send redo to RFS process through Oracle net.
If some network outage occur and online redo log gets log switch and data goes to archived redo log files , before its been written to standby redo log files then RFS process will directly communicate to ARCn process and works for Archive log gap resolution.
Once with any possible way redo are written to standby redo log files then , MRP [in case of physical dataguard] or LSP [in case of logical dataguard] process apply that redo or sql to standby database.
And as redo data is being applied on standby database , ARCn process of standby database also generates archive logs.
You can use your standby database for resource intensive task like backup and also it is usable for reporting tasks.
Primary Database Processes for Dataguard Environment :
1. LGWR : LGWR collects transaction redo information and updates online redo log files.
2.LNS process [Log writer network server]: LNS process works in two way.
SYNC mode : When you have configured your dataguard environment in sync Redo transport Service , LGWR passes redo to LNS process , which transfers data directly to RFS process on the standby database. LGWR waits for confirmation from the LNS process and LNS process waits confirmation from RFS process that redo data are applied to standby database before acknowledging commit.
ASYNC : When you have configured async redo transport service, it is independent of LNS process , whether LNS process have read from redo log buffer or from online redo log files . Dataguard just starts asynchronous LNS process , other than that LGWR has no interaction with any asynchronous standby destinations. In simple terms , data guard will not wait for any acknowledgement from standby database that redo are applied or not and keeps on doing its work. So it is way faster than sync mode .
3. ARCn Process : As we know ARCn process creates a copy of the online redo log files . ARCn is also responsible for shipping redo data to an RFS process at a standby database and for pro-actively detecting and resolving gaps on all standby database.
Standby Database Processes :
1. RFS [Remote File Server] : As we have seen above RFS process can get redo data either from LNS process or from ARCn process of primary and RFS process can writes redo information to standby redo logs files.
Each LNS and ARCn process that communicates with Standby database has its own RFS process.
2. ARCn [Archiver] : The ARCn process archives standby redo logs.
3. MRP [Managed Recovery Process] : In case of physical dataguard MRP process comes into play.MRP process applies archived redo log information to the physical standby database.You can start managed recovery using “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” this foreground session performs recovery. And if you want to perform recovery on background then you can optionally use DISCONNECT FROM SESSION. clause where MRP background process will start .
If you use DG BROKER to manage your dataguard , it always starts MRP background process.
4. LSP [Logical Standby ]: It comes into play for logical dataguard only. It controls the application of archived redo log information to the logical standby database. LSP process will transform redo data into sql statements and then these sql statements will be applied to logical standby database.
=======================
MODES OF DATA GAURD
=========================
1)Maximum Protection Mode (Sync/AFFIRM):
---------------------------------------
Purpose: This mode is designed for zero data loss. It ensures that every transaction committed on the primary database is written to at least one standby database before it is acknowledged as complete.
Operation: In Maximum Protection mode:
Redo data from the primary database is synchronously transmitted and applied to the standby database(s) in real-time.
Both the primary and standby databases must be in sync (no data loss is allowed). The primary database will wait for the standby to acknowledge the redo receipt and apply it before continuing with the transaction.
Failure Behavior: If the primary database detects that there is no standby database available (e.g., if the standby is down or unreachable), it will not allow new transactions to be committed. This prevents any potential data loss because the system ensures data is fully protected before continuing.
Use Case: This mode is suitable when the priority is to prevent data loss at all costs, which is critical in high-availability environments where business operations cannot tolerate even a small amount of data loss.
Key Characteristics:
Zero Data Loss (synchronous redo transport).
Requires both primary and standby databases to be online and able to communicate synchronously.
Can impose performance overhead due to the synchronous nature.
EX - ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=<standby_tns>
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=<standby_db_unique_name>
SYNC AFFIRM';
SYNC Mode:
In SYNC (synchronous) mode, the primary database waits for acknowledgment from the standby database before it commits a transaction.
This ensures no data loss, as the redo data must be safely written to the standby redo logs before the transaction is committed on the primary.
AFFIRM Option:
AFFIRM means the standby database confirms that the redo data is written to the disk (standby redo logs) before sending the acknowledgment back to the primary.
This ensures maximum data safety because the redo is guaranteed to be persistently stored.
Maximum Protection:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
2)Maximum Availability Mode (sync/AFFIRM):
---------------------------
Purpose: This mode strikes a balance between data protection and availability. It provides protection against data loss in the event of failure of the primary database but also allows for continued database operation when the standby is not available.
Operation: In Maximum Availability mode:
Redo data is transmitted synchronously to the standby database, similar to Maximum Protection mode.
If for any reason, the standby database becomes unavailable, the primary database will continue accepting transactions and will not stop.
The primary database acknowledges a commit as soon as the redo is written to the primary database's online redo log and the data is transmitted to at least one standby. If the standby is unavailable, the system can continue, but it ensures that no data is lost once the standby comes back online.
Failure Behavior: In this mode, the primary database will continue functioning even if it can't reach the standby for a period of time. When the standby becomes available again, it will catch up with any missed changes (via redo log apply).
Use Case: This mode is appropriate for applications that require a high level of availability but can tolerate brief periods without synchronous protection (i.e., the short interval when the primary can't contact the standby).
Key Characteristics:
Data Loss Prevention (synchronous redo transport when available).
Allows the primary to continue operating if the standby is unreachable, but it still prioritizes availability and data protection.
Ensures no data loss when communication is restored.
EX - ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=<standby_tns>
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=<standby_db_unique_name>
SYNC AFFIRM';
Maximum Availability:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
3. Maximum Performance Mode (async):
------------------------------
Purpose: This mode is designed for optimal performance. It prioritizes the performance of the primary database over synchronous data protection.
Operation: In Maximum Performance mode:
Redo data is transmitted asynchronously to the standby database. This means the primary database does not wait for an acknowledgment from the standby before committing a transaction.
The primary database commits the transaction immediately after the redo is written to its online redo log (without waiting for the standby to apply it).
If the standby database is unavailable, the primary continues operating without any disruption.
Failure Behavior: If the primary database fails, data in the primary database that was not yet shipped or applied to the standby might be lost. This mode allows for potential data loss in the event of a failure, but it ensures minimal impact on the performance of the primary database.
Use Case: This mode is best when performance is critical and occasional data loss (such as in situations where the redo shipping fails for a period) is acceptable, or when high throughput for the primary database is a priority over absolute protection.
Key Characteristics:
Asynchronous Redo Transport.
Ensures minimal performance impact on the primary database.
Possible Data Loss in the event of failure before the redo is shipped/applied to the standby
ex - ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=<standby_tns>
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=<standby_db_unique_name>
ASYNC';
Maximum Performance:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Summary of the Three Data Guard Modes:
modes Data Loss Redo Transport Failover Behavior Use Case
Maximum Protection : Zero - Synchronous Primary stops if standby is unreachable - Critical systems where data loss is unacceptable
Maximum Availability : Zero (mostly)- Synchronous (when available) - Primary continues if standby is down briefly -High availability with a balance between protection and uptime
Maximum Performance :Possible - Asynchronous - Primary continues with no impact on performance Performance-sensitive environments where some data loss is acceptable
=============================================================================
Here are the high-level steps to create a physical standby database in Oracle:
=============================================================================
Primary Database Configuration
1)Check Compatibility :
Ensure the primary and standby databases use the same Oracle version and compatible operating systems.
2)Enable Force Logging :
Force logging ensures that all operations are logged for replication :
ALTER DATABASE FORCE LOGGING;
3)Configure Archive Log Mode:
Ensure the primary database is in ARCHIVELOG mode:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enable Flashback (Optional but Recommended):
4)Enable flashback for point-in-time recovery:
ALTER DATABASE FLASHBACK ON;
5)Configure Standby Redo Logs and tns/listner file for both databases:
Add standby redo logs to the primary database, with the same size and number as redo logs:
ALTER DATABASE ADD STANDBY LOGFILE '/path_to_file/standby_redo01.log' SIZE 50M;
Modify Initialization Parameters:
6)Update the primary database’s init.ora or spfile:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';
ALTER SYSTEM SET FAL_SERVER=STANDBY;
ALTER SYSTEM SET FAL_CLIENT=PRIMARY;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/standby_path/','/primary_path/';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/standby_redo/','/primary_redo/';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Standby Database Preparation
7)Prepare the Standby Server:
Install Oracle software on the standby server.
Configure network connectivity between the primary and standby servers.
8)Create Required Directories:
Replicate the primary database’s directory structure on the standby server.
9)Transfer Files:
Transfer parameter files, password file, and initialization files from the primary to the standby server.
10)Duplicate the Database:
Use RMAN to duplicate the primary database to the standby server:
rman TARGET sys/password@primary AUXILIARY sys/password@standby
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='STANDBY'
SET FAL_SERVER='PRIMARY'
SET FAL_CLIENT='STANDBY'
SET STANDBY_FILE_MANAGEMENT='AUTO'
NOFILENAMECHECK;
Configuration on Both Databases
Start the Listener on Both Servers:
Configure and start the listener on the primary and standby servers.
11)Start the Standby Database:
Mount the standby database:
STARTUP MOUNT;
Enable MRP:
alter database recover managed standby database cancel; (stop MRP)
alter database recover managed standby database disconnect from session;
select process ,status ,sequence# from v$managed_standby;
sequence# checking
select max(sequence#) from v$archived_log;
select max(sequence#) from v$archived_log where applied='YES';
12)Verify Data Guard Configuration:
Check the status using DGMGRL or SQL queries:
dgmgrl
CONNECT sys/password;
SHOW CONFIGURATION;
Enable Data Guard Broker (Optional):
13)Simplify management by enabling the broker:
ALTER SYSTEM SET DG_BROKER_START=TRUE;
Testing and Validation
14)Validate Log Shipping:
Generate redo logs on the primary and check if they are applied on the standby:
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
Switchover/Failover Testing:
Test a switchover or failover using the Data Guard broker or manual steps.
--------------------
When the primary and standby databases have their datafiles and redo log files in different locations, you need to use the SET NEWNAME command in RMAN to map the file paths correctly during a restore or duplicate operation. Here's how to handle this situation:
RMAN> RUN {
SET NEWNAME FOR DATABASE TO '/u02/oradata/standby/%U';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
Handling Redo Log Location Differences
For redo logs, you need to update the standby control file or configure the parameters correctly. Use the LOG_FILE_NAME_CONVERT initialization parameter to remap redo log file locations.
Add or Modify Parameter in init.ora or spfile:
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT = '/u01/oradata/primary/', '/u02/oradata/standby/' SCOPE=SPFILE;
LOG_FILE_NAME_CONVERT = ('/u01/oradata/primary/', '/u02/oradata/standby/')
=========================
1. Physical Standby
Description: A physical standby is a replica of the primary database that is maintained through redo data application, keeping the data identical to the primary database.
Key Characteristics:
Uses Redo Apply to synchronize with the primary database.
Data is stored in the same physical format as the primary database.
Can be opened in read-only mode for reporting purposes.
Supports fast failover and switchover.
Use Cases:
Disaster recovery.
Offloading read-only queries (when opened in read-only mode).
High availability with minimal divergence from the primary database.
Limitations:
No changes to the schema or data are allowed while it is in standby mode.
Limited flexibility for custom transformations or changes.
2. Logical Standby
==================
Description: A logical standby database contains the same logical information as the primary database but may use a different physical structure. It applies SQL statements rather than raw redo logs to maintain synchronization.
Key Characteristics:
Uses SQL Apply to transform and execute SQL statements from redo logs.
Can remain open for read-write operations and schema changes while still applying updates from the primary.
Allows the addition of indexes, materialized views, and other objects to optimize reporting workloads.
Use Cases:
Reporting and decision support.
Data transformations and customizations on standby.
Disaster recovery with additional flexibility.
Limitations:
Slight lag in synchronization due to the overhead of SQL translation and execution.
More complex to manage compared to physical standby.
3. Snapshot Standby
==================
Description: A snapshot standby is a fully updatable standby database created from a physical standby. It allows testing or temporary data changes while retaining the ability to revert to standby mode.
Key Characteristics:
Operates as a fully updatable database.
Does not apply redo logs while in snapshot mode but queues them for later application.
Can be converted back to a physical standby, discarding any changes made in snapshot mode.
Use Cases:
Application testing or patch testing without affecting the primary database.
Temporary read-write access for specific tasks while preserving disaster recovery capabilities.
Limitations:
While in snapshot mode, redo data from the primary database is queued but not applied.
Not suitable for real-time reporting or disaster recovery until reverted to a physical standby.
Summary Table
Feature Physical Standby Logical Standby Snapshot Standby
Data Synchronization Redo Apply SQL Apply Redo Apply (queued)
Updatable? No Yes (custom objects) Yes
Reporting Use Yes (read-only) Yes (read-write) Yes (temporary, read-write)
Flexibility Low High Medium
Failover/Switchover Supported Supported Supported (after revert)
Typical Lag Minimal Potential SQL lag Redo backlog applies on revert
===============================================
STEPS FOR STANDBY CREATION USING RMAN DUPLICATE
-------------------------------------------
### CREATE STANDBY SERVER
1. Create a virtual machine for the standby server with Oracle Linux installed.
2. Install Oracle binaries. Do not create any database.
3. Set up networking between the production and standby servers.
- Oracle networking does not depend on physical DB presence.
4. **Configure Redo Transport:**
```
SQL> alter system set log_archive_dest_1 =
'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=proddb';
SQL> alter system set log_archive_dest_2 =
'service=proddb_stb async valid_for=(online_logfile, primary_role) db_unique_name=proddb_stb';
SQL> alter system set fal_server = 'proddb_stb';
SQL> alter system set log_archive_config = 'dg_config=(proddb, proddb_stb)';
```
---
### CONFIGURE STANDBY SERVER
5. Create a pfile from the spfile, and copy it to the standby server. Also, copy the password file from the production server to the standby server.
6. In `LOG_ARCHIVE_DEST_1`, change the `db_unique_name` to the standby SID (in this case, `proddb_stb`).
7. In `LOG_ARCHIVE_DEST_2`, change the service and `db_unique_name` to the primary SID (in this case, `proddb`).
8. Change `FAL_SERVER` to the primary SID (`proddb`).
9. Set `db_unique_name=proddb_stb`.
10. Create respective file location directories.
11. Review all the parameters and networking between the primary and standby servers.
### CREATE STANDBY DATABASE
12. On standby create spfile
---------------------------
startup nomount pfile=initprod.ora;
create spfile from pfile;
shut immediate;
startup nomount;
show parameter spfile;
exit
13. Duplicate the prod server via RMAN
rman target sys/sys@prod
RMAN> connect catalog rman_rc/rman_rc/rcat (if configured)
RMAN> connect auxiliary sys/sys@prod_stb
RMAN> duplicate target database for standby from active database nofilenamecheck;
14. Check the database status and db_roles :
select name,open_mode ,database_role from v$database;
=======================================
Oracle Redo Log File Status Summary
=======================================
State Description Action/Condition
CURRENT The active redo log file currently being written to. Only one redo log is in the CURRENT state at a time.
ACTIVE A redo log file that has been written to but not yet archived. Becomes ACTIVE after a log switch from CURRENT.(more than 1 active log)
INACTIVE A redo log file that has been archived or is no longer needed for transactions. Can be reused after being archived.
CLEARING A log file that is being cleared or reset. Typically happens when log files are being reused.
LOGGING Indicates Oracle is writing logs to the redo log file. Happens during normal redo logging activity.
ARCHIVED A redo log file that has been archived after a log switch. Stored in the archive log destination.
RESETLOGS Indicates a new incarnation of the database after a recovery. New redo log files are created post-recovery.
=====================================
Problem:
The standby database cannot apply redo logs in real-time, causing data discrepancies.
Cause:
• Insufficient memory or CPU resources on the standby server.
• Disk I/O bottlenecks during redo application.
Solution:
1. Parallel Recovery:
o Increase the number of recovery processes to speed up application:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL;
2. Increase Memory:
o Allocate more memory to the DB_CACHE_SIZE and LOG_BUFFER parameters on the
standby.
3. Optimize Disk I/O:
o Place redo logs on high-performance storage, such as SSDs.
4. Tune Redo Apply:
o Use the RECOVERY_MODE parameter for optimizing redo apply:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Purpose of the Command:
Enables real-time apply mode.Redo data is applied directly from the standby redo logs (SRLs) as it is received, without waiting for the log switch on the primary database.This ensures the standby is as current as possible, minimizing data lag between the primary and standby databases.
Automation: The DISCONNECT FROM SESSION clause ensures the recovery process does not require manual intervention or the session to remain active.
===========================================
When you encounter lag between the primary and standby database in an Oracle Data Guard configuration, applying a backup from the primary to the standby can help resolve discrepancies caused by gaps in redo log application or other issues. Here's an example of resolving lag using SCN-based backup and recovery.
---
### **Scenario**
- **Primary Database**: The source database where transactions are being generated.
- **Standby Database**: The target database that should remain synchronized with the primary.
- **Problem**: The standby is significantly lagging or unable to apply redo logs due to corruption or missing redo.
- **Solution**: Use an SCN-based backup from the primary to synchronize the standby.
---
### **Steps**
#### **1. Identify the Current SCN on the Standby**
Run the following command on the standby database to check the last applied SCN:
```sql
SELECT CURRENT_SCN FROM V$DATABASE;
```
#### **2. Create an Incremental Backup from the Primary**
Log in to the primary database and create an incremental backup starting from the SCN identified in step 1.
```bash
rman target /
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
BACKUP INCREMENTAL FROM SCN <standby_scn> DATABASE FORMAT '/backup_path/incr_%U';
RELEASE CHANNEL ch1;
}
```
Replace `<standby_scn>` with the SCN obtained from the standby database.
#### **3. Transfer the Backup to the Standby**
Copy the incremental backup files from the primary to the standby server using `scp` or another file transfer method:
```bash
scp /backup_path/incr_* standby_server:/standby_backup_path/
```
#### **4. Stop Managed Recovery on the Standby**
Stop the managed recovery process on the standby to prepare for applying the backup:
```sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
```
#### **5. Catalog the Backup on the Standby**
Log in to the standby and catalog the transferred backup files:
```bash
rman target /
CATALOG START WITH '/standby_backup_path/';
```
#### **6. Apply the Incremental Backup**
Apply the incremental backup to the standby database:
```bash
rman target /
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
RECOVER DATABASE NOREDO;
RELEASE CHANNEL ch1;
}
```
#### **7. Restart Managed Recovery**
Restart the managed recovery process on the standby:
```sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
```
#### **8. Verify Synchronization**
Check the synchronization status by querying the SCN on both primary and standby databases:
On Primary:
```sql
SELECT CURRENT_SCN FROM V$DATABASE;
```
On Standby:
```sql
SELECT CURRENT_SCN FROM V$DATABASE;
```
If the SCNs match or are close, the standby is synchronized.
---
### **Additional Notes**
1. **Consider Network Lag**: Ensure that the network between the primary and standby is not introducing latency or transfer issues.
2. **Redo Transport**: After synchronization, verify that redo transport services are configured correctly.
3. **Alert Monitoring**: Use `v$dataguard_stats` and `v$managed_standby` to monitor transport and apply lag in real time.
4. **Health Check**: Regularly verify the health of the Data Guard setup using Oracle's `DGMGRL`.
This approach ensures the standby database catches up to the primary without requiring a full reinitialization.
============================================
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY_DB,STANDBY_DB)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_DB';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY_DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_DB';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET FAL_SERVER=STANDBY_DB;
ALTER SYSTEM SET FAL_CLIENT=PRIMARY_DB;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/primary/data','/standby/data';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/primary/data','/standby/data';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
----------------------------------------------------
Enable ARCHIVELOG Mode:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDR)';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,orcl)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19/db_1/dbs/arch) DB_UNIQUE_NAME=PROD';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/proddruct/19/db_1/dbs/arch) DB_UNIQUE_NAME=proddr';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl DB_UNIQUE_NAME=orcl'scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET FAL_SERVER=orcl;
ALTER SYSTEM SET FAL_CLIENT=PROD;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PRODDR/';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PRODDR/';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=devdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=devdr';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
SQL> startup NOMOUNT PFILE='/u01/app/oracle/product/19/db_1/dbs/initproddr.ora';
sqlplus /nolog
connect / as sysdba
select instance_name, status, database_status from v$instance;
------------------------------------
select name, open_mode, db_unique_name, database_role from v$database;
START MRP :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
STOP MRP :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter system set log_archive_dest_state_2='defer';
alter system set log_archive_dest_state_2='enable';
alter system switch logfile;
select error,status from v$archive_dest where dest_id=2;
select error,status from v$archive_dest where dest_id=1;
select process ,status ,sequence# from v$managed_standby;
select dest_id,status,destination,error from v$archive_dest where dest_id=2;
##checking order of arhive logs
select sequence#,first_time,next_time,applied,archived from v$archived_log
where name='orcl' order by first_time;
#GAP checking
select status,gap_status from v$archive_dest_status where dest_id=2;
select process ,status ,sequence# from v$managed_standby;
alter database recover managed standby database disconnect from session;
process checking -
select client_process,process,thread#,sequence#,status from v$managed_standby
where client_process='LGWR' or process='MRP0';
checking both prm and stan are in sync or not
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
sequence# checking
select max(sequence#) from v$archived_log;
select max(sequence#) from v$archived_log where applied='YES';
-------------------------------
PROD PFILE :
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_m8t4rxpk_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_m8t4rxr5_.ctl'
*.db_block_size=8192
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=21474836480
*.db_unique_name='orcl'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_server='prod'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.93.134)(PORT=1521))'
*.log_archive_config='DG_CONFIG=(prod,orcl)'
*.log_archive_dest_1='location=/u01/app/oracle/product/19.3/db_home/dbs/arch'
*.log_archive_dest_2='SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.log_archive_dest_state_2='enable'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=363m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='orcl1'
*.sga_target=1300m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/ORCL/'
DB_FILE_NAME_CONVERT :
Purpose: Converts datafile paths from the source database to the target database.
Usage: Useful in scenarios like creating a standby database or cloning where the datafile locations differ between the source and the target.
*.log_file_name_convert='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/ORCL/'
LOG_FILE_NAME_CONVERT :
Purpose: Converts redo log file paths from the source database to the target database.
Usage: Useful when setting up a standby database or cloning, where redo log file locations differ.
Standby PFILE :
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_m8t4rxpk_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_m8t4rxr5_.ctl'
*.db_block_size=8192
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=21474836480
*.db_unique_name='orcl'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_server='prod'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.93.134)(PORT=1521))'
*.log_archive_config='DG_CONFIG=(prod,orcl)'
*.log_archive_dest_1='location=/u01/app/oracle/product/19.3/db_home/dbs/arch'
*.log_archive_dest_2='SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.log_archive_dest_state_2='enable'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=363m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='orcl1'
*.sga_target=1300m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/ORCL/'
*.log_file_name_convert='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/ORCL/'
--------------------------------------------------------------
rman target sys@PRODDR
-- On primary
SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET='proddr';
checking the pwd file same or not on bith db -
md5sum orapwprod
systemctl disable firewalld
:%s/ prod/prodr
SQL> show parameter remote_login_passwordfile;
select group#,thread#,sequence#,archived,status from v$standby_log;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo11.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo12.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo13.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo14.log' SIZE 50M;
orapwd file=$ORACLE_HOME/dbs/orapworcl entries=10 force=y
orapwd file=orapwdevdb password=sadashiv#123 entries=10 force=y
orapwd file=orapwprimary password=sonali#123 entries=10 force=y
orapwd file=orapwprod password=sadashiv#123 entries=10 force=y
sqlplus sys/sys@prod as sysdba
rman target sys/sadashiv#123@prod auxiliary sys/sadashiv#123@orcl1;
rman target sys/sys@prod auxiliary sys/sys@orcl1;
rman target sys/sonali#123@primary1 auxiliary sys/sonali#123@standby1;
alter sysetm register;
sqlplus sys/sys@ORCL1
mkdir -p /u01/app/oracle/admin/devdb/adump
mkdir -p /u01/data/db_files/devdb/
mkdir -p /u01/FRA/devdb
sadashiv#123
scp orapwporcl oracle@192.168.93.134:/u01/app/oracle/product/19.3/db_home/dbs
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';
SHOW PARAMETER LOCAL_LISTENER;
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.93.132)(PORT=1521))' SCOPE=BOTH;
./runInstaller -executePrereqs -silent -responseFile /u01/app/oracle/product/19/db_1/install/response/db_install.rsp
./runInstaller -silent -responseFile /u01/app/oracle/product/19/db_1/install/response/db_install.rsp
./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile /u01/app/oracle/product/19.3/db_home/install/response/db_install.rsp
CREATE CONFIGURATION 'my_dg_config' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS 'prod';
ADD DATABASE 'orcl' AS CONNECT IDENTIFIER IS 'orcl' MAINTAINED AS PHYSICAL;
create spfile='/tmp/initorcl.ora' from pfile;
SQL> alter system set service_names='orcl$' scope=spfile;
RMAN> connect auxiliary sys@ORCL
rman target sys/sadashiv#123@ORCL1 auxiliary sys/sadashiv#123@PROD;
duplicate target database for standby from active database nofilenamecheck;
ALTER DATABASE FLASHBACK OFF;
alert location-
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
control file location
/u01/app/oracle/oradata/PROD
alter database create standby controlfile as '/u01/app/oracle/oradata/PROD/orcl.ctl';
alter database create standby controlfile as /u01/app/prod.ctl';
----------------------------------------------
mkdir /u01/bkp
rman target /
backup database format '/u01/bkp/fulldev_bkp_%U';
backup archivelog all format '/u01/bkp/archdev_%U';
backup current controlfile format '/u01/bkp/devcontrol_ctl_%U';
backup incremental from scn 3209301 database format '/u01/bkp/incr_bkp_%U';
backup incremental level 0 database plus archivelog format '/u01/bkp/incr_bkp_%U';
scp /u01/bkp *
standby
mkdir /u01/bkp
db in - no mount
rman target /
restore controlfile from '/u01/bkp/control_ctl_112vg1oa_1_1
alter database mount;
catalog start with '/u01/bkp/';
restore database ;
recover database ;
mrp start
check rfs status
alter database open resetlogs;
RMAN> restore standby controlfile from service 'ORCL1'; (db in no mount )
recover database from service 'ORCL1';
restore database from service 'ORCL1'
RMAN> restore standby controlfile from service 'ORCL1';
-------------------------------------
START REAL TIME REDO APPLY IN DG :
Directly apply the redologs without waiting to fill the standby redolog file MRP will apply it realtime no lag and no gap fast sync.
alter database recover managed standby database using current logfile;
--------------------------------
Incremental backup and restore in DG -
Determine the SCN on the Standby Database:-
SELECT CURRENT_SCN FROM V$DATABASE;
RMAN> CONNECT TARGET sys/sys@primary_db;
backup current controlfile for standby format '/u01/bkp/control_ctl_%U';
RMAN> RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
BACKUP INCREMENTAL FROM SCN 3209819 DATABASE FORMAT '/u01/bkp/incr_backup_%U';
RELEASE CHANNEL ch1;
}
RMAN> CONNECT TARGET sys/sys@stadby_db;
restore controlfile from '/u01/bkp/control_ctl_112vg1oa_1_1
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
CATALOG START WITH '/u01/bkp/';
RECOVER DATABASE NOREDO;
RELEASE CHANNEL ch1;
}
----------------------------------
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%U';
exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'prod1_rw', network_name => 'prod1_rw', failover_method => 'BASIC', failover_type =>
'SELECT', failover_retries => 30, failover_delay => 10);
----------------------------------------------
creating clinet connection -
BEGIN
DBMS_SERVICE.CREATE_SERVICE (
service_name => 'prod1_rw',
network_name => 'prod1_rw',
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 30,
failover_delay => 10
);
END;
/
define db primary -
create or replace procedure prod1_rw_service
is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('prod1_rw');
else
DBMS_SERVICE.STOP_SERVICE('prod1_rw');
end if;
end;
/
TRIGGER TO START SERVICE ON DB STARTUP:
=======================================
create or replace TRIGGER db_startup
after startup on database
begin
prod1_rw_service;
end;
/
TRIGGER TO START SERVICE ON DB ROLECHANGE:
==========================================
create or replace TRIGGER db_role_change
after db_role_change on database
begin
prod1_rw_service;
end;
/
SQL> exec prod1_rw_service;
tnsnames.ora file config on client -
prod =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.93.132)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.93.134)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = prod1_rw)
(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=10))
)
)
-------------------------------------------------------------------
Oracle Data Guard Manual Switchover and Failover on Physical Standby :
-------------------------------------------------------------------
select name, open_mode, db_unique_name, database_role from v$database;
On primary:
===========
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
On standby:
===========
select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;
On primary:
===========
select SWITCHOVER_STATUS from V$DATABASE;
alter database commit to switchover to physical standby with session shutdown;
startup mount;
Convert standby to primary:
select SWITCHOVER_STATUS from V$DATABASE;
alter database commit to switchover to primary with session shutdown;
alter database open;
On new standby on MRP -
alter database recover managed standby database disconnect from session ;
------------------------------
Failover primary database :
----------------------------
select name, open_mode, db_unique_name, database_role from v$database;
On standby:
===========
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> select SWITCHOVER_STATUS from V$DATABASE;
SELECT DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;
You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;
Rebuild Primary After Failover :
Post failover, there are two methods of rebuilding your failed primary
Method 1:Rebuild from scratch à RMAN duplicate
Method 2: Flashback database à only if Flashback was enabled
Get the SCN at which standby became primary:
select to_char(standby_became_primary_scn) from v$database;
Flashback crashed primary(proddb):
startup mount;
flashback database to scn <standby_became_primary_scn>;
alter database convert to physical standby;
alter database recover managed standby database disconnect;
--------------------------------------------------
Convert Physical Standby into Snapshot Standby:
-------------------------------------------------
On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> select name, open_mode from v$database; >> make sure its mounted
SQL> alter database convert to snapshot standby;
SQL> alter database open; >> open the DB
SQL> select name, open_mode, database_role from v$database;
Verifying snapshot standby:
On standby:
===========
SQL> select name, guarantee_flashback_database from v$restore_point;
SQL> create table student(sno number(2), s_name varchar2(10));
SQL> insert into student values(1,'RAM');
SQL> insert into student values (2,'Max');
SQL> commit;
SQL> select * from student;
Revert back snapshot standby to physical standby:
On standby:
===========
SQL> select name, open_mode, database_role from v$database;
SQL> shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
SQL> select * from student;
-----------------------------------------
Configure Data Guard Broker :
----------------------------------------
on -stb
Edit listeners
(SID_DESC=
(GLOBAL_DBNAME=devdb_DGMGRL)
(SID_NAME=devdb)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
)
Stop MRP on standby :
On standby:
===========
SQL> alter database recover managed standby database cancel;
SQL> alter system set LOG_ARCHIVE_DEST_2='' sid='*';
On primary:
===========
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';
Enable broker :
On primary:
===========
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;
On standby:
===========
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker_start;
Register primary with broker:
On primary:
===========
dgmgrl sys/sadashiv#123@prod1
DGMGRL> create configuration my_dg_config as primary database is prod connect identifier is prod;
DGMGRL> show configuration;
Register standby with broker;
DGMGRL> add database devdb as connect identifier is devdb;
DGMGRL> show configuration;
Enable Data Guard broker:
Once primary and standby are registered, we must enable broker
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE prod;
DGMGRL> SHOW DATABASE devdb;
Turn on/off the apply process in DG :(MRP)
edit database orcl set state=APPLY-OFF;
edit database orcl set state=APPLY-ON;
Turn on/off the TRANSPORT from the source db :
edit database prod set state=TRANSPORT-OFF
edit database prod set state=TRANSPORT-ON
Monitering commands:
Check the Standby Synchronization Status :
SHOW DATABASE VERBOSE 'devdb';
Check Real-Time Query Synchronization :
SHOW DATABASE 'devdb';
Verify Lag in the Configuration :
VALIDATE DATABASE VERBOSE 'devdb';
Monitor Standby in Real Time
SHOW CONFIGURATION;
------------------------------------------
Oracle Data Guard Broker Switchover and Failover:
Switchover Using DGMGRL :
---------------------------
On primary:
===========
dgmgrl sys/sys@prod
DGMGRL> show configuration;
DGMGRL> SWITCHOVER TO orcl;
DGMGRL> show configuration;
Revert Back Using DGMGRL:
On current primary(orcl):
==============================
dgmgrl sys/sys@orcl
DGMGRL> show configuration;
DGMGRL> switchover to prod;
DGMGRL> show configuration;
Data Guard Broker Failover :-
On primary:
===========
ps -ef|grep pmon
grid 2636 1 0 Apr15 ? 00:00:12 asm_pmon_+ASM
oracle 16914 1 0 14:03 ? 00:00:00 ora_pmon_prod
oracle 17722 15893 0 15:08 pts/0 00:00:00 grep pmon
kill -9 16914
Failover to Standby
On standby:
===========
dgmgrl sys/sys@orcl
DGMGRL> show configuration;
DGMGRL> FAILOVER TO orcl;
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl"
DGMGRL> show configuration;
Rebuild Primary After Failover :-
On current primary (orcl):
===============================
dgmgrl sys/sys@orcl
(note stb (prod - should be in mount )
DGMGRL> show configuration;
DGMGRL> reinstate database prod;
Reinstating database "proddb", please wait...
Verify proddb post reinstate:
On failed primary (prod):
===========================
select name,open_mode from v$database;
select process, status, sequence# from v$managed_standby;
Switchover to get original configuration:
On current primary (proddb_st):
===============================
dgmgrl sys/sys@orcl
DGMGRL> switchover to prod;
----------------------------------------
Configure Fast Start Failover :
------------------------------------
On primary(proddb):
===================
dgmgrl sys/sys@proddb
DGMGRL> show database prod StaticConnectIdentifier;
DGMGRL> show database orcl StaticConnectIdentifier;
Define FastStartFailoverTarget:
On primary (proddb):
====================
dgmgrl sys/sys@prod
DGMGRL> SHOW FAST_START FAILOVER
DGMGRL> EDIT DATABASE prod SET PROPERTY FastStartFailoverTarget = 'orcl';
DGMGRL> EDIT DATABASE orcl SET PROPERTY FastStartFailoverTarget = 'prod';
DGMGRL> show database verbose prod;
DGMGRL> show database verbose orcl;
Define FastStartFailoverThreshold :
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;
DGMGRL> show fast_start failover;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30;
Enable FSFO : Never start observer on production database
On Standby Server:
==================
dgmgrl sys/sys@orcl
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> show configuration;
DGMGRL> start observer;
Test FSFO Configuration:
On primary (prod):
===================
sqlplus / as sysdb
SQL> shut abort;
On new primary (orcl):
===========================
sqlplus / as sysdba
SQL> select name, open_mode, db_unique_name from v$database;
Reinstate Failed Primary:
On failed primary (prod):
===========================
sqlplus / as sysdba
SQL> startup mount;
dgmgrl sys/sys@prod
DGMGRL> show configuration;
On current primary (orcl):
===============================
dgmgrl sys/sys@orcl
DGMGRL> show configuration;
DGMGRL> switchover to prod;
Disable FSFO:
dgmgrl sys/sys@prod
DGMGRL> DISABLE FAST_START FAILOVER;
Stop observer:
==============
dgmgrl sys/sys@orcl
DGMGRL> stop observer;
-----------------------------------------
Convert Physical Standby Into Logical Standby :
---------------------------------------------
Primary Database Changes
DGMGRL> show configuration
Configuration - my_dg
Protection Mode: MaxPerformance
Members:
prod - Primary database
orcl - (*) Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 59 seconds ago)
* In the primary database, retrieve the list of objects that are not supported
set pagesize 25;
column owner format a5;
select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;
Check the reason behind those unsupported objects
select column_name,data_type from dba_logstdby_unsupported where owner='OE' and table_name= 'CUSTOMERS';
* Identify the unsupported schemas on primary
Col owner format a20;
select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA';
* Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key
SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;
Redo Apply needs to be stopped on the Physical Standby now
DGMGRL> edit database ip7_st set state = apply-off;
Build Log Miner directory on primary
SQL> exec dbms_logstdby.build
Open Physical Standby
On Standby Server
=================
SQL> alter database recover to logical standby devdb;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs;
SQL> select name, open_mode, db_unique_name, database_role, guard_status from v$database;
Data Guard Broker Changes :
DGMGRL> remove database orcl;
Start Logical Apply Service on standby server
On Standby
==========
SQL> alter database start logical standby apply immediate;
Now add the new logical standby to the broker configuration
DGMGRL> add database orcl_stb as connect identifier is orcl_stb;
DGMGRL> enable database orcl_stb
DGMGRL> show configuration;
Configuration - my_dg
Protection Mode: MaxPerformance
Members:
prod - Primary database
orcl_stb - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 5 seconds ago)
Verify logical standby:
On standby:
===========
ps -ef|grep lsp
SQL> SELECT name, value FROM v$logstdby_stats;
Shutting Down Logical Standby :
Switch log on primary
Alter system switch logfile;
On standby, stop LSP:
Alter database stop logical standby apply;
Shutdown immediate;
To start the LSP process on standby
Startup;
Alter database start logical standby apply immediate;
-----------------------------------------------------------------------
EDIT DATABASE 'orcl' SET PROPERTY 'Log_Archive_Dest_State_2'='DEFER';
CREATE CONFIGURATION 'my_dg_config' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS 'prod';
ADD DATABASE 'prod' AS CONNECT IDENTIFIER IS 'prod';
ENABLE DATABASE 'orcl';
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
RMAN> run
{
duplicate target database for standby from active database
NOFILENAMECHECK
spfile
parameter_value_convert 'PROD','orcl'
set db_name='PROD'
set db_unique_name='orcl'
set db_file_name_convert '/u01/prod/oradata','/u01/prod/oradata'
set log_file_name_convert '/u01/prod/oradata/redolog',
'/u01/prod/oradata/redolog'
;
}
ALTER SYSTEM SET db_recovery_file_dest_size = 5G SCOPE=BOTH;
-
backup database plus archivelog;
alter database create standby controlfile as '/u01/bkp/orcl.ctl';
BACKUP DATAFILE 1 FORMAT '/u01/bkp/datafile1_%U.bkp';
log_archive_dest_2 string SERVICE=prod1 ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=prod
=====================================================
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/controlfile_trace.sql' RESETLOGS;
/u01/app/controlfile_trace.sql
@/u01/app/controlfile_trace.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
DATAFILE
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_mc8t4cp0_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_mc8t9k3d_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_mc8t9k44_.dbf',
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_mc8t4ctg_.dbf'
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_mc8thz20_.tmp
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_mc8thqfn_.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_mc8thqgr_.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_mc8thqjs_.log' SIZE 50M
-- Add additional redo log files here
CHARACTER SET AL32UTF8;
TEMPFILE
'/u01/app/oracle/oradata/ORCL/tempfile/temp01.dbf' SIZE 20M REUSE;
============================
Database drop with commands:
shut immediate;
startup mount ;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
DROP DATABASE;
rm -rf /path_to_your_database_files/*
================================
Key Differences
Feature Physical Standby Logical Standby Snapshot Standby
========= ============== ================ ===================
Replication Method Redo Apply (binary redo logs) SQL Apply (SQL transactions) Redo logs are shipped, but not applied until reverted
Structure Exact physical-copy Same logical, different physical Temporary updatable physical copy
Use Case Disaster recovery, real-time query Reporting, disaster recovery, schema modifications Testing, updatable for modifications
Failover/Switchover Fast failover or switchover Slower switchover, more flexible Must convert to physical standby first
Read-Only Mode Yes, with Active Data Guard Yes, always open for queries No, because it is in read-write mode
In summary:
Physical Standby: Exact copy, fast failover, supports read-only queries (Active Data Guard).
Logical Standby: Different physical structure, supports queries and changes, but SQL Apply can be slower.
Snapshot Standby: Temporary read-write mode for testing, can be reverted back to a physical standby.
=====================
/ora/u01/app/oracle/product/19.3/dr_db
====================
tns :
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb)
)
)
)
----
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.db.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
rman target sys/sysDEVDB
[oracle@pr ~]$ rman target sys/sys@prod auxiliary sys/sysDEVDB
Recovery Manager complete.
[oracle@db2 dbs]$ rman target sys/sys@prod auxiliary sys/sysDEVDB
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 4 10:55:44 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=598574758)
connected to auxiliary database: DEVDB (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
========================================
PROD DB PRM File
[oracle@db1 dbs]$ cat initprod.ora
prod.__data_transfer_cache_size=0
prod.__db_cache_size=1073741824
prod.__inmemory_ext_roarea=0
prod.__inmemory_ext_rwarea=0
prod.__java_pool_size=0
prod.__large_pool_size=16777216
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=536870912
prod.__sga_target=1577058304
prod.__shared_io_pool_size=83886080
prod.__shared_pool_size=385875968
prod.__streams_pool_size=0
prod.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u02/oradata/PROD/control01.ctl','/u02/oradata/PROD/control02.ctl'
*.db_block_size=8192
*.db_name='prod'
*.db_unique_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.enable_pluggable_database=true
*.fal_client='PROD'
*.fal_server='DEVDB'
*.log_archive_config='DG_CONFIG=(prod,devdb)'
*.log_archive_dest_1='LOCATION=/u02/oradata VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=devdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=devdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=3
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=500m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1500m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
listener.ora
[oracle@db1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.db.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@db1 admin]$
tns.ora
[oracle@db1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb)
)
)
=======================================
DEVDB PRM FILE :
prod.__data_transfer_cache_size=0
prod.__db_cache_size=1073741824
prod.__inmemory_ext_roarea=0
prod.__inmemory_ext_rwarea=0
prod.__java_pool_size=0
prod.__large_pool_size=16777216
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=536870912
prod.__sga_target=1577058304
prod.__shared_io_pool_size=83886080
prod.__shared_pool_size=385875968
prod.__streams_pool_size=0
prod.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/devdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u02/oradata/DEVDB/control01.ctl','/u02/oradata/DEVDB/control02.ctl'
*.db_block_size=8192
*.db_name='prod'
*.db_unique_name='devdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.enable_pluggable_database=true
*.fal_client='PROD'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,devdb)'
*.log_archive_dest_1='LOCATION=/u02/oradata/DEVDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=devdb'
*.log_archive_dest_2='SERVICE=prod LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.log_file_name_convert='/u02/oradata/DEVDB','/u02/oradata/PROD'
*.db_file_name_convert='/u02/oradata/DEVDB','/u02/oradata/PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=3
*.service_names='devdb'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=500m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1500m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@db2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.db.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@db2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2.db.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = devdb)
)
)
=====================================
Comments
Post a Comment