DATA GUARD
What is
data guard?
Data Guard is a
comprehensive Oracle technology that provides disaster recovery, data
protection, and high availability for Oracle databases. It creates standby
databases to protect data against data loss and system failures.
Data Guard is
basically a ship redo and then apply redo, as you know redo is the information
needed to recover a database transaction. A production database referred to as
a primary database transmits redo to one or more independent replicas referred
to as standby databases.
select inst_id, process, status, thread#,
sequence#, block#, blocks from gv$managed_Standby where process in
('RFS','LNS','MRP0')
What is
standby database?
A standby
database is a database, replica created from a backup of a primary production
database. By applying archived redo logs from the primary database to the
standby database, we can keep the two databases synchronized.
What
are the benefits using data guard?
a. High
Availability.
B. Data
Protection.
d. Automatic Gap
detection and Resolution in standby database.
e. Automatic Role
Transition using Data Guard Broker
What
are data guard parameters to be set during configuration?
Below are the
standby database parameters.
Log_Archive_Dest_n
Log_Archive_Dest_State_n
Log_Archive_Config
Log_File_Name_Convert
DB_File_Name_Convert
Standby_File_Managment
DB_Unique_Name
Control_Files
Fal_Client
Fal_Server
What
are the different protection modes in Data Guard?
Maximum Protection: Guarantees zero data loss but can impact primary
database performance.
Maximum Availability: Provides high level of data protection with minimal
impact on primary database.
Maximum Performance: Default mode, provides basic data protection with
minimal overhead.
Explain
the components of Data Guard?
Primary Database: The production database
where data is modified.
Standby Database: A copy of the primary
database that can be used for disaster recovery or reporting.
Redo Transport Services: Transmits redo data
from primary to standby.
Data Transport Services: Transfers archived
redo logs to the standby.
Standby Apply Process: Applies redo data to
the standby database.
LNS – Log Writer Network service - Primary
RFS – Remote File server - Standby
Difference
between physical standby database or a logical standby database?
Physical standby
database
Provides
a physically identical copy of the primary database, with on-disk database
structures that are identical to the primary database on a block-for-block basis.
The database schemas, including indexes, are the same. A physical standby
database is kept synchronized with the primary database by recovering the redo
data received from the primary database.
Logical standby
database
Contains
the same logical information as the production database, although the physical
organization and structure of the data can be different. It is kept
synchronized with the primary database by transforming the data in the redo
logs received from the primary database into SQL statements and then executing
the SQL statements on the standby database. A logical standby database can be
used for other business purposes in addition to disaster recovery requirements.
This allows users to access a logical standby database for queries and
reporting purposes at any time. Thus, a logical standby database can be used
concurrently for data protection and reporting.
Figure 1-1 Typical Data Guard Configuration
What is
Fast Start Failover?
Fast
Start Failover is a technology that allows for rapid failover of the primary
database to a standby database in case of a failure.
Explain
the role of the Standby Apply Process (MRP).
MRP
is responsible for applying redo data to the standby database. It manages the
process of reading archived redo logs, applying them to the database, and
managing errors.
What is
the difference between Synchronous and Asynchronous Standby?
Synchronous
Standby: Redo
data is applied to the standby before committing on the primary, the primary
database will wait for the commit acknowledgement from stand by database,
providing stronger data protection but impacting performance.
Asynchronous
Standby: Redo
data is applied to the standby after committing on the primary, The primary
database do not wait for the acknowledgment from the standby database it is
continuously process data as it comes, providing better performance but reduced
data protection
Oracle
19C Active Data Guard DML Redirection Feature?
ACTIVE DATA GUARD
DML REDIRECT feature enables DML operation on the standby database
to be redirected to and run on the primary database instead of failing, where
the data changes will be applied. The updated data will then be streamed back
to the standby database to maintain redundancy.
SQL - ALTER DATABASE ADD FEATURE DML_REDIRECT ON; - Run on Primary and Stand BY
Set
Standby Role: Set the standby database to a role
that supports DML Redirection. This can be either PRIMARY or READ_WRITE. Use
the following SQL:
SQL - ALTER DATABASE SET STANDBY ROLE READ_WRITE;
Below
steps to achieve Applying DML on standby database:
· User
executes DML against open standby database.
·
DML redirects to the primary database.
·
Then DML is applied to the primary
database.
·
Then the data change is streamed back to
the standby database.
· Data
is visible to the client.
List
few of the data guard commands?
ADD - Adds a standby database to the
broker configuration.
CONNECT - Connects to an Oracle database
instance.
CONVERT - Converts a database from one type to
another (from Oracle 11g)
CREATE - Creates a broker configuration.
DISABLE - Disables a configuration, a database,
or fast-start failover (FSFO)
EDIT - Edits a configuration, database, or
instance -
Example >
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
ENABLE - Enables a configuration, a database,
or fast-start failover (FSFO).
FAILOVER - Changes a standby database to be the
primary database.
Example:-
DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
REINSTATE - Changes a database marked for
reinstatement into a viable standby.
REMOVE - Removes a configuration, Oracle
database, or instance.
SHOW - Displays information about a
configuration, database, instance or FSFO.
Example:
DGMGRL> SHOW CONFIGURATION [VERBOSE];
SHUTDOWN - Shuts down a currently running Oracle
instance.
SQL - Executes a SQL statement
Example:
DGMGRL> SQL "sql_statement";
STARTUP - Starts an Oracle database instance.
STOP - Stops the fast-start failover
(FSFO) observer.
SWITCHOVER - Switches roles between a primary and
standby database.
VALIDATE - command to checks whether the database
is ready for a role transition or not.
Example: -
DGMGRL> VALIDATE DATABASE ...; --
From Oracle Database 12c
Example
of REINSTATE of a DATA GUARD set up.
What is
the fal_client and fal_server parameter used for in data guard?
The fal_server
and fal_client are used for the gap resolution in Dataguard. When MRP process
finds that an archive log is missing during media recovery; it sends the
fal_client Information to the server identified by fal_server and requests
fal_server to resend the file again.
*.fal_server='Primary
database Name'
*.Fal_Client='Standby
database Name'
Password
file in the standby lost what will happen?
Nothing to worry
about, if primary and stand by database is already connected and we have lost
the pwd file. Still the redo transport will happen as it is. Because these Two
primary & standby were already in SYNC. Issue may occur if we
disable/enable the log shipping.
So just simply
copy the pwd file from primary to standby location and disable and enable the
log shipping.
What
are the data guard protection modes & what is the default protection mode?
Data guard uses
Sync & Asynchronous methods to transfer redo from primary to standby.
Using this we
have three protection modes in data guard.
NOTE - Maximum protection - shut down the primary database, rather
than allowing it to continue processing transactions that are unprotected. If
stand by is not responding.
1. Maximum
Protection - SYNC -
2. Maximum
Availability - SYNC
3. Maximum
Performance – ASYNC
Maximum
performance = the default protection mode.
What is
timeout, delay parameter in data guard and where can we specify them?
v The parameter is optional. By
default there is no DELAY
v If you have real-time apply
enabled, any delay that you set will be ignored.
v The above is suggested when we
configured more than 1 STNDBY database.
1. A standby
database automatically applies archived redo logs as it arrives from the
primary database. But in some cases, we want to create a time lag between the
archiving of a redo log at the primary site, and the application of the log at
the standby site.
2. Modify the
Log_Archive_Dest_n initialization parameter on the primary database to set a
delay for the standby database.
Example: For
60min Delay:
3. ALTER SYSTEM
SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′;
The following
example shows how to specify the DELAY attribute for this configuration:
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/dbs/'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2='SERVICE=stbyB LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_3='SERVICE=stbyC DELAY=120'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
4. The DELAY
attribute is expressed in minutes.
5. The archived
redo logs are still automatically copied from the primary site to the standby
site, but the logs are not immediately applied to the standby database. The
logs are applied when the specified time interval expires.
6. We can cancel
a specified delay interval as follows:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
How to
improve the data guard log apply?
If the real-time
apply feature is enabled, log apply services reads data directly from the
standby redo log file and apply redo data to the standby database as it is
received, without waiting for the current standby redo log file to be archived.
To enable real time apply we can use the below query and we need to create standby redo log files if not available
already on the standby side.
STANDBY SIDE
------------
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM
SESSION;
How to
check MRP process on data guard?
We can use
v$managed_standby view to check the status of the MRP process.
SQL> select
process, status from v$managed_standby;
PROCESS STATUS
---------
------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
Where
to check real times apply PROCESS is enable or not in standby database?
To determine if
real-time apply is enabled, we need to query the RECOVERY_MODE column of the
V$ARCHIVE_DEST_STATUS view.
SQL>
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
RECOVERY_MODE
-----------------------
MANAGED
REAL TIME APPLY
What is
the use of standby redo logs in data guard environment?
LNS
- Log Network service reads
the redo data and sends information through the oracle net services. In standby
the RFS - Remote File Server receives the
redo data and stores in the standby redo files. From there apply process will
apply the changes to standby database. It’s better create standby redo log
files in standby as well as in the primary database. Standby Redo Logs are not
mandatory for Primary Database but its good practice and useful in role
conversion from Primary to Standby Database)
How
redo entries transfer from primary to standby? Which background process will
perform this activity?
LNS process reads
the redo data as it arrives and send the information through oracle net
services. The RFS process in the standby reads same data and stores in the SRL
files.
From there apply
process will read the redo data and updates the standby database. ARCH process
generates archivelog.
Archive
logs missing from Primary to Standby Databases how do you resolve?
First we need to look
at alert log files in both primary and standby & figure out what caused for
lag. If that’s a pwd file issue for syncing just copy pwd file form primary to
standby and disable & enable the apply process and see the log are moving.
If it’s due to
some other issue the archives not transferred to STNDBY like network issue OR
due to any maintenance in Primary database server(ex: Patching or Data base
Upgrade). Just start the MRP process and validate the sync.
Archivelog
GAP is there from primary to standby how do you resolve?
First try to
disable/enable the Managed recovery process using broker OR manually. And try
to see are there are any errors in the alert logs for both primary and standby
and act accordingly OR follow the below steps.
Select
THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# from V$ARCHIVE_GAP;
A
sample output from find_gap.sql is:
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
-------- ------------- --------------
1 606 609
If the
LOW_SEQUENCE# is less than the HIGH_SEQUENCE# in the output, the database is
having a gap sequence.
Try to find out
the missing logs from Primary to standby archive log location. The naming
convention should match for archive log file. Rename them if required.
File can be
renamed using the operating system utility. Since these logs were not
transferred by the log transfer service, the managed recovery process will not
have any information about these logs. These logs will need to be manually
registered with the managed recovery process before they will be applied by the
log apply service. To register the logs with the MRP, use the following
statement:
For example:
Register all the files like below.
Stop the Managed
recovery process
ALTER DATABASE
REGISTER LOGFILE ‘/oracle/appsdb/arch/stdby_1_607.dbf
SQL>
alter database recover managed standby database disconnect from session;
How to
change protection modes in data guard?
In order to
change the protection mode to either MAXIMUM AVAILABILITY/MAXIMUM PROTECTION
and to Enable real time apply process, we need to have the standby redo logs
configured on the standby database. Also, the redo shipment parameter (log_archive_dest_2)
on the primary database should be configured to use SYNCHRONOUS.
Stand by redo
logs should be same size of online redo logs. Now create standby redo log files
on primary and standby database, if not exists already.
NOTE: We need to
check if MRP (Managed Recovery Process) is running on the standby database and
if running, then it needs to be cancelled.
EX: SQL>alter
database add standby logfile group 1 size 100M;
Once the SRLs are
added, start the MRP on the standby database.
SQL>
alter database recover managed standby database disconnect from session;
Configure the
redo shipment parameter (log_archive_dest_2) on the primary database to use
SYNCHRONOUS mode.
SQL> show
parameter log_archive_dest_2
NAME TYPE VALUE
------------------
------ -----------------------------------
log_archive_dest_2
string service=srpstb valid_for=(online_logfiles,primary_role)
db_unique_name=srpstb
SQL> alter
system set log_archive_dest_2='service=srpstb LGWR AFFIRM SYNC valid_for=
(online_logfiles,primary_role) db_unique_name=srpstb';
Now shutdown the
primary database & start in mount and change the protection mode like
below.
SQL> alter
database set standby database to maximum availability;
SQL> alter
database open;
SQL> select
status, instance_name, database_role, protection_mode from
v$database,v$instance;
Check primary and
standby are in Sync now.
What is
the role of Data Guard Broker?
Data Guard Broker
simplifies the management of multiple standby databases. It provides a
centralized view of the Data Guard environment and automates tasks like
configuration and monitoring.
Dataguard
importance and data guard broker importance for DBA?
Using DG broker
we can create configuration for primary and stanby databases. This is more
useful while setting the stanby database.
Role changes are
easier with the DG broker
The broker
automatically communicates between the databases in a Data Guard configuration
using Oracle Net Services.
If the primary
database fails, the broker automates the process for any one of the standby
databases to replace the primary database.
Fast-Start
Failover requires the Data Guard Broker.
The Broker is
quite sensitive and spots problems with the Data Guard Configuration fast.
How do
you monitor Data Guard performance?
Data Guard provides various performance metrics that can
be monitored using Oracle Enterprise Manager or SQL queries. Key metrics
include redo apply lag, archive log generation rate, and network latency.
How do
you troubleshoot common Data Guard issues?
Common issues include standby database errors, redo apply
lag, and network connectivity problems. Troubleshooting involves checking log
files, alert logs, and database status.
How do
you perform a switchover and failover in Data Guard?
Switchover: Planned transfer of primary database role to
a standby database.
Failover: Automatic or manual transfer of primary
database role to a standby database after a failure.
How to
switch over using Broker?
Connect to DGMGRL
and issue the below.
DGMGRL>
switchover to physt;
Performing
switchover NOW, please wait...
New primary
database "physt" is opening...
Operation
requires shutdown of instance "prima" on database "prima"
Shutting down
instance "prima"...
ORACLE instance
shut down.
Operation
requires startup of instance "prima" on database "prima"
Starting instance
"prima"...
ORACLE instance
started.
Database mounted.
Switchover
succeeded, new primary is "physt"
DGMGRL> show
configuration;
Configuration -
myconf
Protection Mode: MaxPerformance
Databases:
Physt - Primary database
Prima - Physical standby database
Fast-Start
Failover: DISABLED
Configuration
Status:
SUCCESS
Some
data guard broker commands?
DGMGRL> edit
database rs1_stb set state=APPLY-OFF;
DGMGRL> edit
configuration set protection mode as MaxProtection;
DGMGRL>
switchover to physt;
DGMGRL> show
configuration;
DGMGRL> ADD
DATABASE physt AS CONNECT IDENTIFIER IS physt MAINTAINED AS PHYSICAL;
DGMGRL> CREATE
CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS prima;
DGMGRL> show
database physt;
DGMGRL>
failover to physt;
DGMGRL>
convert database physt to snapshot standby;
How to create
standby database from Active database?
Using RMAN and DG
broker we can configure data guard easily.
1. We need to add
the required entries in listener.ora & tnsnames.ora – Primary & StandBy
2. Then connect
the primary database & create a fresh pfile for standby database and
transfer it to standby oracle_home/dbs location. -
Primary
3. Edit the pfile
and add standby parameters. - Standby
4. Make sure all
required structure created in the standby server. - Standby
5. Now connect to
the sqlplus and create spfile from pfile on the standby server - Standby
6. Startup standby
database in no mount stage - Standby
7. Now create the
SRL on the primary if we are restoring data from active database instead of
backup (OR) we can create them later as well.
8. Using RMAN
connect the both databases active & standby like below.
9. Rman target
sys/pwd@db auxiliary sys/pwd@db > And issue the duplicate command on Standby
10. RMAN>
duplicate target database for standby from active database; - StandBy
11. Now build the
configuration using DG broker.
12. Need to
execute 'SQL> alter system set dg_broker_start=true;' on the both
server(PMRY & STBY) before using broker.
13. Connect using
DGMGRL on the primary server: dgmgrl sys/oracle@prima > and create
configuration for primary. - Primary
DGMGRL>
CREATE CONFIGURATION myconf AS PRIMARY DATABASE IS prima CONNECT IDENTIFIER IS
prima; - Primary
14. Now add the
other database to the configuration as STNDBY. – Primary
DGMGRL>
ADD DATABASE physt AS CONNECT IDENTIFIER IS physt MAINTAINED AS PHYSICAL;
DGMGRL> enable
configuration;
15. Then check
alert log to check shipping is in process.
16. Completed.
How to
find archive gaps in data guard?
Using
V$ARCHIVE_GAP view we can find the archive log gaps in data guard.
SQL> Select
THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# from V$ARCHIVE_GAP;
Archive
gaps are more than how to restore?
Using
standby roll forward that uses RMAN incremental backup to apply the changes on
the standby.
1. Look for the
current_scn in both and primary & standby.
2. Stop the
Manage recovery process.
3. Connect to
RMAN and initiate backup from scn (Primary) – Standby SCN number.
4. And create a
fresh control file as well. SCP these backup files to standby server.
5. Now shutdown
the standby database and start in nomount stage.
6. Replace the
control file with new control file copied from PMRY.
Ø Shutdown standby &
startup in nomount
Ø
Rman no catalog target /
Ø Restore standby controlfile
from '/tmp/o1_mf_TAG20070220T151030_.bkp';
7. Mount the
standby database SQL> alter database mount standby database;
8. [Standby] RMAN
does not know about these files yet; so you must let it know – by a process
called cataloging. Catalog these files.
9. Connect and issue RMAN> catalog start with
'/u01/oraback'; - Backup location which we transferred from PRMY
10. RMAN will ask
> Do you really want to catalog the above files (enter YES or NO)? yes -
Just say 'YES'
11. Now RMAN>
recover database noredo;
12. After
recovery completed enable the managded recovery process.
13. SQL> alter
database recover managed standby database disconnect from session;
Dataguard
Broker configured, what happens to the Dataguard configuration if we do the
manual switchover, failover using sql commands?
Never tested, but
as of my knowledge nothing will happen. I think we need re-configure the broker
configuration after switched OR failover occurred manually.
Dataguard
broker configured how to set the database parameters using dgmgrl. What errors
you see when we set parameters using sql?
First to set the
‘dg_broker_start’ parameter value to TRUE if not done. Do it in Primary and
standby
SQL>alter
system set dg_broker_start=true scope=both ;
Edit the
listener.ora file which includes the db_unique_name_DGMGRL, db_domain values
for the GLOBAL_DBNAME in both primary and standby database. To set the value,
let’s check the db_domain value.
SQL>
show parameter db_domain;
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
Since the value
of db_domain is null so the the value of GLOBAL_DBNAME = NOIDA_DGMGRL for
primary database and for standby GLOBAL_DBNAME = DELHI_DGMGRL.
DGMGRL>
connect sys/xxxx@noida
DGMGRL> create
configuration 'dgnoida'as primary database is 'noida’ connect identifier is
noida;
DGMGRL> Add
database 'delhi' as connect identifier is delhi maintained as physical;
DGMGRL> enable
configuration
View the Primary
and Standby database properties
DGMGRL> show
database verbose noida
DGMGRL> show
database verbose delhi
The
archive log files are deleted from the primary before they are shipped and
applied to the standby??
We can keep deletion policy for Primary database RMAN so
that archive log cannot be removed before applying.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON
STANDBY; - Primary side
If real time
process is enabled the redo data routed to standby database as it is arrived in
the primary database. It won’t wait for till archive log file generate in the
primary database.
Restore archive
log files from backup, register them and use managed recovery to recover them;
or use manual recovery without registering them using RMAN.
New
datafiles are added to the primary, but they are not added to the standby
automatically?
If STANDBY_FILE_MANAGMENT=AUTO.
The data file will also automatically create in the stdby site. This parameter
uses DB_FILE_NAME_CONVERT parameter to change datafile location in the standby.
If both location are diff in primary & stanby.
If that’s not set
=> add the new datafiles to the standby database manually.
How to
verify whether the archive log file is corrupted on either standby or primary
site?
SQL>
alter system dump logfile '<name>' validate;
If you get the
SQL prompt back without error, then the archive log file is not corrupted.
Data
guard architecture?
The Data Guard
architecture consists of several key components:
Primary
Database: This is the
production database where data is modified and transactions are processed. It's
the source of data for the standby databases.
Standby
Database: A replica of the
primary database that can be used for disaster recovery, reporting, or other
purposes. It can be physical or logical.
Redo
Transport Services: Responsible
for transferring redo data (information about database changes) from the
primary database to the standby database.(Log
writer network services)
Data
Transport Services: Transfers
archived redo logs from the primary to the standby database.
Standby
Apply Process (MRP):
Applies the redo data to the standby database to keep it synchronized with the
primary.
How
Data Flow in Oracle Data Guard?
The data flow in
a Data Guard environment is as follows:
§ The primary database generates redo data
as transactions are committed.
§
Redo
Transport Services capture this redo data and transmit it to the standby
database.
§
The
standby database receives the redo data and applies it using the MRP process.
§
Archived
redo logs are transferred to the standby database using Data Transport Services
for recovery purposes.
Why We
Create Standby Redo Logs
Standby redo logs (SRLs) are created on a standby
database to improve performance and reduce data loss in a Data Guard
environment.
Real-Time
Apply: Standby redo logs facilitate real-time application of
redo data to the standby database, maintaining data consistency between the
primary and standby.
Improved
Performance:
Faster Redo Application: In maximum protection and
maximum availability modes, SRLs allow the standby database to directly receive
redo data from the primary database without going through the archive log
process. This significantly speeds up the application of changes to the
standby.
Reduced
Network Traffic: By using SRLs, the amount of data transferred
between the primary and standby databases is reduced, improving network
efficiency.
What
are the different types of standby databases and their use cases?
Types of Standby
Databases and Their Use Cases
- Physical Standby Database - An exact block-for-block
copy of the primary database.
- Logical Standby Database - Redo data is transformed
into DML statements and applied to the standby database.
- Snapshot Standby Database - A read-write copy of a
physical standby database and Changes made to the snapshot are discarded when
converted back to a physical standby.
- Active Standby Database - An active standby database
is a physical standby database that is open for read-only access while simultaneously
applying changes from the primary database.
Explain
the concept of Far Sync and its implications.
Far Sync is a feature introduced in Oracle 12c to address
the challenges of maintaining zero data loss in a Data Guard environment when
the primary and standby databases are geographically distant. It provides a way
to ensure data protection without significantly impacting primary database
performance.
A Far Sync instance is a lightweight database instance
that doesn't contain user data files. It primarily acts as a relay point for
redo data between the primary database and remote standby databases.
Redo
Reception: The Far Sync instance receives redo data from the primary
database.
Redo
Processing: It processes the redo data and creates standby redo logs
(SRLs).
Redo
Transmission: The Far Sync instance then ships the redo
data (in the form of SRLs) to the remote standby database.
How
does Data Guard handle database upgrades?
Data Guard
simplifies the upgrade process by allowing standby databases to be upgraded
implicitly through the application of redo data from the primary database. This
means you don't need to perform a separate upgrade on the standby.
What is
the role of redo transport services and data transport services?
Redo
Transport Services (RTS)
Purpose: Transfers redo data (changes made to the
database) from the primary database to the standby database in real-time.
Captures redo data generated by the primary database.
Transmits the redo data to the standby database.
Data
Transport Services (DTS)
Purpose: Transfers archived redo logs from the primary
database to the standby database.
Copies archived redo logs to a designated location on the
standby database.
Provides a backup of redo data for recovery purposes.
Make
sure last redo is applied after switchover?
When the switchover
command is executed the redo generation is stopped, all users are either
prevented from executing transactions or terminated and the current redo log is
archived for each tread. A special switchover marker called the EOR (end of
redo) is then placed in the header of the next sequence for each thread, and
the online redo files are archived a second time, sending the final sequences
to the standby databases. At this point the physical standby database is closed
and the final log switch is done without allowing the primary database to
advance the sequence numbers for each thread
After the EOR
redo is sent to the standby databases, the original primary database is
finalized as a standby and its control file backed up to the trace file and
converted to the correct type of standby control file
Start
the switchover (primary): SQL>alter database commit to switchover to
standby;
In the case of a
physical standby switchover the managed recovery process (MRP) is automatically
started on the original primary to apply the final archive logs that contain
the EOR so that all the redo ever generated is processed. The primary is then
dismounted and must then be restarted as a standby database in at least the
mount state.
The standby database must receive
this EOR redo otherwise the switchover cannot occur, once
this redo has been received and applied to complete the switchover you run the
following command, this will be automatic if you are using the Grid Control or
the Broker
Complete
the switchover (new primary):SQL> alter database commit to switchover to
primary;
The physical
standby switchover will wait for the MRP process to exit after processing the
EOR redo and then convert the standby control file into a normal production
control file. The final thing to do is to open the database for general
production use
Complete
the switchover (new primary): SQL> Alter database open;
What is
Active Standby Database and Use of it?
Ability to open
the standby database in read-only mode while the database is still in managed
recovery mode. This means 11g active data guard provides disaster recovery and
at the same time can be used for reporting purpose
How to
enable stanby database is in read-only mode (Active standby)??
If trying open
the standby database in read only mode will get the below error.
SQL> alter
database open;
alter database
open
*
ERROR at line 1:
ORA-10456: cannot
open standby database; media recovery session may be in progress.
1. First we need
turf off the log apply process on the standby database. Using the below.
DGMGRL>
edit database <stdby_dbname> set state=apply-off;
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>
ALTER DATABASE OPEN READ ONLY;
SQL>Select
name, open_mode, switchover_status, database_role from v$database;
Start apply
process using Broker (OR) Manually in standby database.
DGMGRL>
edit database <stdby_dbname> set state=apply-on;
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT;
To
disable the active data guard, the steps are the same:
1. DGMGRL>
edit database stdby set state=apply-off; (OR)
SQL> ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE CANCEL;
2. Shutdown the
stdby database;
3. Startup mount
the stdby database
4. DGMGRL>
edit database stdby set state=apply-on; (OR)
SQL> ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Explain
Snap Shot StandBy database and the use of it?
Able to open
standby database in read-write. This is very useful to turn our standby
database into application testing / development purposes. This is achieved
through snapshot standby database.
In order to
create snapshot standby database:
1. Standby
database must be a physical standby database
2. Flashback logging
must be enabled on both production and standby database
3. After enabling
flashback mode, connect to dgmgrl utility in the primary database:
4.
DGMGRL> CONVERT DATABASE STDBY TO SNAPSHOT STANDBY;
NOTE: Snap Shot StandBy – Is
recommended if we have more than one standby database to the primary database.
From now on, we
can do any testing (like creating new schemas, tables and so on) on our standby
database. Please note that at this point of time; all the redo generated in our
production database will be still shipped to standby database.
But it is just that
not going to be applied until the database is converted into physical standby
mode.
5. Once the
testing done, you can convert the snapshot standby database to physical standby
with just 1 command:
DGMGRL>c
CONVERT DATABASE STDBY TO PHYSICAL
STANDBY;
6. Note that when
the above command executed
All the changed
made to the snapshot standby database (such as creating new schemas, tables)
has been terminated
Previous physical
standby database state is initiated
Physical standby
database is mounted and MRP process is initiated. MRP will apply all the logs
which was shipped yet applied during the snapshot standby database state.
7. Duration for
this process is depends on few factors:
The amount of
changes made to the database during the snapshot standby database state. More
changes will lead to more time to rewind the changes via flashback database
option.
The amount of
archived logs generated during the snapshot standby database state. More
archive logs will cause more time to apply it when the database is converted to
physical standby database.
Deprecated
Parameters in 11G release for DG?
STANDBY_ARCHIVE_DEST - Deprecated
FAL_CLIENT
parameter is deprecated in 11g and not needed.
Oracle can get
the value of these 2 parameters from log_archive_dest_2 automatically.
How
many STNDBY databases we can configure?
A Data Guard
configuration can now consist of a primary database and up to 30 standby
databases.
Check
the Log Transport:
alter system
switch logfile;
SQL> select
max (sequence#) from v$archived_log;
NOTE: The new ALTER SYSTEM FLUSH REDO SQL
statement can be used at failover time to flush unsent redo from a mounted
primary database to a standby database, thereby allowing a zero data loss
failover to be performed even if the primary database is not running in a zero
data loss data protection mode.
NOTE: You can use the new ALTER SESSION SYNC
WITH PRIMARY SQL statement to ensure that a suitably configured physical
standby database is synchronized with the primary database as of the time the
statement is issued.
The
V$DATAGUARD_STATS view has been enhanced to a greater degree of accuracy in
many of its columns, including apply lag and transport lag.
You can view a
histogram of apply lag values on the physical standby. To do so, query the new V$STANDBY_EVENT_HISTOGRAM view.
NOTE: A corrupted data block in a primary
database can be automatically replaced with an uncorrupted copy of that block
from a physical standby database that is operating in real-time query mode. A
corrupted block in a physical standby database can also be automatically
replaced with an uncorrupted copy of the block from the primary database.
Enhanced
Oracle RAC switchover support for logical standby databases
When switching
over to a logical standby database where either the primary database or the
standby database is using Oracle RAC, the SWITCHOVER command can be used
without having to shut down any instance, either at the primary or at the
logical standby database.
How to
do DR?
Database
upgrades using Transient logical standby approach?
Comments
Post a Comment