What is a Data guard?
Oracle Data guard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.
Oracle Data guard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.
What
are the types of Oracle Data Guard?
Oracle
Data Guard classified in to two types based on way of creation and method used
for Redo Apply. They are as follows.
- Physical standby (Redo Apply technology)
- Logical standby (SQL Apply Technology)
What additional standby database
mode does Oracle 11g offer?
Oracle 11g has introduced the Oracle
Snapshot Standby Database. In Snapshot Standby Database a physical
standby database can easily open in read-write mode and again you can convert
it back to the physical standby database. This is suitable for test and
development environments and also maintains protection by continuing to receive
data from the production database and archiving it for later use.
What
are the advantages in using Oracle Data Guard?
Following
are the different benefits in using Oracle Data Guard feature in your
environment.
- High Availability.
- Data Protection.
- Off loading Backup operation to standby database.
- Automatic Gap detection and Resolution in standby database.
- Automatic Role Transition using Data Guard Broker.
What
are the different services available in Oracle Data Guard?
Following are the different
Services available in Oracle Data Guard of Oracle database.
- Redo Transport Services.
- Log Apply Services.
- Role Transitions.
What is Redo Transport Services?
It control the automated transfer of redo data from the
production database to one or more archival destinations.
Redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.
Redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.
What
are the different Protection modes available in Oracle Data Guard?
Following
are the different protection modes available in Data Guard of Oracle database
you can use any one based on your application requirement.
- Maximum Protection
- Maximum Availability
- Maximum Performance
Maximum availability
:
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.
Maximum performance :
This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.
Maximum protection :
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.
Maximum performance :
This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.
Maximum protection :
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.
How
to check what protection mode of primary database in your Oracle Data Guard?
By
using following query you can check protection mode of primary database in your
Oracle Data Guard setup.
SELECT
PROTECTION_MODE FROM V$DATABASE;
For
Example:
SQL>
select protection_mode from v$database;
PROTECTION_MODE
——————————–
——————————–
MAXIMUM
PERFORMANCE
How
to change protection mode in Oracle Data Guard setup?
By
using following query your can change the protection mode in your primary
database after setting up required value in corresponding LOG_ARCHIVE_DEST_n
parameter in primary database for corresponding standby database.
ALTER
DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];
Example:
alter
database set standby database to MAXIMUM PROTECTION;
What
are the advantages of using Physical standby database in Oracle Data Guard?
Advantages
of using Physical standby database in Oracle Data Guard are as follows.
- High Availability.
- Load balancing (Backup and Reporting).
- Data Protection.
- Disaster Recovery.
What
is physical standby database in Oracle Data Guard?
Oracle
Standby database are divided into physical standby database or logical standby
database based on standby database creation and redo log apply method. Physical
standby database are created as exact copy i.e block by block copy of primary
database. In physical standby database transactions happen in primary database
are synchronized in standby database by using Redo Apply method by continuously
applying redo data on standby database received from primary database. Physical
standby database can offload the backup activity and reporting activity from
Primary database. Physical standby database can be opened for read-only
transactions but redo apply won’t happen during that time. But from 11g onwards
using Active Data Guard option (extra purchase) you can simultaneously open the
physical standby database for read-only access and apply redo logs received
from primary database.
What
is Logical standby database in Oracle Data Guard?
Oracle
Standby database are divided into physical standby database or logical standby
database based on standby database creation and redo log apply method. Logical
standby database can be created similar to Physical standby database and later
you can alter the structure of logical standby database. Logical standby
database uses SQL Apply method to synchronize logical standby database with
primary database. This SQL apply technology converts the received redo logs to
SQL statements and continuously apply those SQL statements on logical standby
database to make standby database consistent with primary database. Main
advantage of Logical standby database compare to physical standby database is
you can use Logical standby database for reporting purpose during SQL apply i.e
Logical standby database must be open during SQL apply. Even though Logical
standby database are opened for read/write mode, tables which are in
synchronize with primary database are available for read-only operations like
reporting, select queries and adding index on those tables and creating
materialized views on those tables. Though Logical standby database has
advantage on Physical standby database it has some restriction on data-types,
types of DDL, types of DML and types of tables.
What
are the advantages of Logical standby database in Oracle Data Guard?
- Better usage of resource
- Data Protection
- High Availability
- Disaster Recovery
What is Data Guard Broker?
Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:
a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.
Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:
a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.
What
is the usage of DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
DB_FILE_NAME_CONVERT
parameter is used in Oracle Data Guard setup that to in standby databases.
DB_FILE_NAME_CONVERT parameter are used to update the location of data files in
standby database. These parameter are used when you are using different
directory structure in standby database compare to primary database data files
location.
What
is the usage of LOG_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
LOG_FILE_NAME_CONVERT
parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT
parameter are used to update the location of redo log files in standby
database. These parameter are used when you are using different directory
structure in standby database compare to primary database redo log file
location.
1) Used for duplicating database for recovery purpose.
It converts the filename of a new datafile on the primary database to a filename on the standby database.
It converts the filename of a new datafile on the primary database to a filename on the standby database.
2) Standby
#############################################################
Step for Physical Standby
These are the steps to follow:
- Enable Archive log mode
- Enable forced logging
- Create a password file
- Configure a standby redo log
- Enable archiving
- Set up the primary database initialization parameters
- Configure the listener and tnsnames to support the
database on both nodes
Some key points before proceeding with the physical standby setup.
- Primary database should be in archivelog mode.
- Forced Logging is on in Primary database (sql>alter database force logging;)
- Initialization parameter “db_name” should be same on both primary and standby database.
- Initialization parameter “db_unique_name” should be different on primary and standby databases.
Primary Database : PRIM
Standby Database : STND
1. Check if the primary database is using the password file or not. If not, then create one as below.
orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y
2. Add the following parameters in the initialization parameter file of the primary database.
initprim.ora
prim.__db_cache_size=46137344
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base=’/u01/home/oracle’#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest=’/u01/home/oracle/admin/prim/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.diagnostic_dest=’/u01/home/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.db_unique_name=’prim’
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prim’
*.log_archive_dest_2=’service=stnd
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base=’/u01/home/oracle’#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest=’/u01/home/oracle/admin/prim/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.diagnostic_dest=’/u01/home/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.db_unique_name=’prim’
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prim’
*.log_archive_dest_2=’service=stnd
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
3. Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.
Primary:
listener.ora file
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
)
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
tnsnames.ora file
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
# Generated by Oracle configuration tools.
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
Standby:
listener.ora file
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
)
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = stnd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = stnd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
tnsnames.ora file
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
# Generated by Oracle configuration tools.
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
4. Add the following parameters in the initialization parameter file of the standby database
initstnd.ora
stnd.__db_cache_size=230686720
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stnd/adump’
# *.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/app/oracle/oradata/stnd/control01.ctl’,’/u02/app/oracle/oradata/stnd/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=stnd’
*.log_archive_dest_2=’service=prim
valid_for=(online_logfiles,primary_role)
db_unique_name=prim’
*.db_unique_name=’stnd’
*.db_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.log_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.standby_file_management=AUTO
*.FAL_SERVER=’prim’
*.FAL_CLIENT=’stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stnd/adump’
# *.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/app/oracle/oradata/stnd/control01.ctl’,’/u02/app/oracle/oradata/stnd/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=stnd’
*.log_archive_dest_2=’service=prim
valid_for=(online_logfiles,primary_role)
db_unique_name=prim’
*.db_unique_name=’stnd’
*.db_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.log_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.standby_file_management=AUTO
*.FAL_SERVER=’prim’
*.FAL_CLIENT=’stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
5. Now copy the password file of the primary database “orapwprim” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwstnd”.
Note that the format of the password file in Windows is as pwd<SID>.ora (Example:pwdPRIM.ora)
6. Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).
rman target sys/<password>@prim auxiliary sys/<password>@stnd
Now perform the duplicate operation to create the standby database.
rman>duplicate target database for standby from active database nofilenamecheck;
7. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.
sqlplus sys/<password>@stnd as sysdba
Check the status of the standby database by making sure it is in mount stage.
sql>select status,instance_name,database_role from v$instance,v$database;
8. Now start the managed recovery process on the standby database.
sql>alter database recover managed standby database disconnect from session;
9. Now check if the managed recovery process (MRP) has been started on the standby database or not.
sql>select process,status,sequence# from v$managed_standby;
Example:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 39
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 39
Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the MRP0 under the process column.
10. On the primary database, perform a few log switches and check if the logs are applied to the standby database.
sqlplus sys/<password>@prim as sysdba
sql>alter system switch logfile;
sql>select max(sequence#) from v$archived_log;
Example:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
38
————–
38
sqlplus sys/<password>@stnd as sysdba
sql>select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
Example:
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 38
———- ————–
1 38
Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.
If you didn't have access to the
standby database and you wanted to find out what error has occurred in a data
guard configuration, what view would you check in the primary database to check
the error message?
You can check the v$dataguard_status
view. Select message from v$dataguard_status;
Why would I use Active Data Guard
and not simply use SQL Apply (logical standby) that is included with Data Guard
11g?
If read-only access satisfies the
requirement - Active Data Guard is a closer fit for the requirement, and
therefore is much easier to implement than any other approach. Active
Data Guard supports all datatypes and is very simple to implement. An Active Data
Guard replica can also easily support additional uses - offloading backups from
the primary database, serve as an open read-write test system during off-peak
hours (Snapshot Standby), and provide an exact copy of the production database
for disaster recovery - fully utilizing standby servers, storage and software
while in standby role.
If you wanted to upgrade your
current 10g physical standby data guard configuration to 11g, can you
upgrade the standby to 11g first then upgrade the primary ?
Yes, in Oracle 11g, you can
temporarily convert the physical standby database to a logical standby database
to perform a rolling upgrade. When you issue the convert command you need
to keep the identity:
alter database recover logical
standby keep identity;
Can Oracle Data Guard be used with
Standard Edition of Oracle ?
Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.
Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.
How do
you identify the archive logs received and applied in DR standby?
v$archived_log
eg: SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
eg: SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
what is
standby_file_management is Auto?
Ans: Enables automatic datafile creation in standby when we create in Primary.
Ans: Enables automatic datafile creation in standby when we create in Primary.
What is
the command used for duplicating the database?
rman>
duplicate target database for standby;
rman>
duplicate target database to primary;
What is
the limit for standby servers in 9i?
Ans : 9
Ans : 9
What is
RFS and MRP0 Process?
RFS - remote file server - Receives remote primary archive files to standby
MRP0 - Managed recovery process Will Apply archives in standby
How do you verify the physical standby database is performing properly?
1) Switch log file in primary
2) verify v$archived_log , the log applied.
How do you create standby control file?
In Primary..
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
What are the steps used to shutdown standby ?
To stop Redo Apply before shutting down the database, use the following steps:
Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.
If the MRP0 or MRP process exists, then the standby database is applying redo.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
If Redo Apply is running, cancel it as shown in the following example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;
What are the steps involved in starting up primary standby?
Start and mount the physical standby database:
SQL> STARTUP MOUNT;
Start Redo Apply or real-time apply:
To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;
To start real-time apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> USING CURRENT LOGFILE;
On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view, which displays
the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.
What is the view used for finding out archives not received by standby?
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
3> WHERE LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);
How do you find protection mode, the protection level, the role of the database, and switchover status ?
Ans: v$database
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, -
PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS -
FROM V$DATABASE;
SQL> SELECT FS_FAILOVER_STATUS FSFO_STATUS, FS_FAILOVER_CURRENT_TARGET -
TARGET_STANDBY, FS_FAILOVER_THRESHOLD THRESHOLD, -
FS_FAILOVER_OBSERVER_PRESENT OBS_PRES -
FROM V$DATABASE
How to monitor Redo Apply and redo transport services ?
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2> FROM V$MANAGED_STANDBY;
RFS - remote file server - Receives remote primary archive files to standby
MRP0 - Managed recovery process Will Apply archives in standby
How do you verify the physical standby database is performing properly?
1) Switch log file in primary
2) verify v$archived_log , the log applied.
How do you create standby control file?
In Primary..
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
What are the steps used to shutdown standby ?
To stop Redo Apply before shutting down the database, use the following steps:
Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.
If the MRP0 or MRP process exists, then the standby database is applying redo.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
If Redo Apply is running, cancel it as shown in the following example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;
What are the steps involved in starting up primary standby?
Start and mount the physical standby database:
SQL> STARTUP MOUNT;
Start Redo Apply or real-time apply:
To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;
To start real-time apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> USING CURRENT LOGFILE;
On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view, which displays
the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.
What is the view used for finding out archives not received by standby?
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM
2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
3> WHERE LOCAL.SEQUENCE# NOT IN
5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND
6> THREAD# = LOCAL.THREAD#);
How do you find protection mode, the protection level, the role of the database, and switchover status ?
Ans: v$database
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, -
PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS -
FROM V$DATABASE;
SQL> SELECT FS_FAILOVER_STATUS FSFO_STATUS, FS_FAILOVER_CURRENT_TARGET -
TARGET_STANDBY, FS_FAILOVER_THRESHOLD THRESHOLD, -
FS_FAILOVER_OBSERVER_PRESENT OBS_PRES -
FROM V$DATABASE
How to monitor Redo Apply and redo transport services ?
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2> FROM V$MANAGED_STANDBY;
Checklist
Steps
|
Description
|
Site
|
1
|
Enforce Logging of All operations
to ensure data consistence.
Make Sure Primary Database is in
ARCHIVE Log mode and is set to Auto-Archive
SQL> alter database force
logging;
|
Primary
|
2
|
Create a Backup of Primary
Database datafiles
Offline (consistent) or an online (inconsistent)
|
Primary
|
3
|
Create a Standby Control file
SQL> alter database create
standby controlfile as '/..../...';
|
Primary
|
4
|
Create a Copy of init.ora file for
Standby database.
|
Primary
|
5
|
Ship backup data files, standby
control file and standby init.ora file to standby site
|
Primary
|
6
|
Setup Net*8 on Primary site to
access Standby site
|
Primary
|
7
|
Setup Net*8 on Standby site to
access Primary site
|
Standby
|
8
|
Make Primary and Standby init.ora
changes (parameters)
Db_file_name_convert
Standby_archive_dest ......
|
Standby
|
9
|
Startup Standby Database instance
in recovery mode
SQL> startup mount
pfile='.....'
|
Standby
|
10
|
Create Standby redo log files, if
applicable
SQL> alter database add standby
logfile (/..','/....');
|
Standby
|
11
|
Setup instance parameters to ship
redo log/archive log to standby site
SQL> alter system archive log
current;
|
Primary
|
12
|
Verify shipping and application of
archive log
|
Primary/Standby
|
Switchover
a. identified what role the db plays
SQL> select database_role from v$database;
b. Initiate the switchover on the primary database
SQL> alter database commit to switchover to physical standby
with session shutdown wait;
c. On the primary database shutdown and restart the instance.
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
d. Start the MRP process (Managed Recovery Process)
SQL> alter database recover managed standby database
disconnect;
e. Verify the switchover status in the v$database view. it
should be SWITCHOVER PENDING status.
SQL> select database_role from v$database;
f. Switch physical standby database role to the primary role.
SQL> alter database commit to switchover to primary with
session shutdown wait;
g. Shutdown and restart the new primary database
SQL> shutdown
SQL> startup
h. Begin archiving logs to the physical standby database.
SQL> alter system archive log start;
SQL> alter system switch logfile;
FAILOVER
a. Initiate the failover operation on the standby database
SQL> alter database recover managed standby database finish;
b. Convert the physical standby database to the primary role
SQL> alter database commit to switchover to primary;
c. Register the missing redo logs.
SQL> alter database register logfile '/standby/arch_dest/arch_1_101.arc';
Dataguard Troubleshooting useful queries
Standby Database:
selectNAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,
CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database;
select inst_id,process, status, client_process, thread#, sequence#, block#, blocks from gv$managed_standby where process = 'MRP0';
select inst_id,process, status, client_process, thread#, sequence#, block#, blocks from gv$managed_standby where process = 'MRP0';
Starting MRP0 :
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 67;
For RAC use
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT FROM SESSION PARALLEL 132 USING CURRENT LOGFILE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 67;
For RAC use
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT FROM SESSION PARALLEL 132 USING CURRENT LOGFILE;
On Standby
select * from gv$active_instances;
ps -ef|grep -i mrp
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
Defer Log Shipping:
alter system set log_archive_dest_state_2=defer scope=both;
alter system set dg_broker_start=false;
Enable Log Shipping:
alter system set log_archive_dest_state_2 = 'enable';
alter system set dg_broker_start=true;
alter system set log_archive_dest_state_2=defer scope=both;
alter system set dg_broker_start=false;
Enable Log Shipping:
alter system set log_archive_dest_state_2 = 'enable';
alter system set dg_broker_start=true;
Starting the STANDBY
DATABASE:
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
Checking For Dataguard Errorr:
select to_char(timestamp,'DD/MM/YY HH24:MI:SS') timestamp,severity, message_num, message from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
select * from v$ARCHIVE_GAP;
Missing Logs on Standby:
select local.thread# , local.sequence# from (select thread# , sequence# from v$archived_log where dest_id=1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#) ;
Starting MRP0:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Stoping MRP0:
RECOVER MANAGED STANDBY DATABASE CANCEL;
MRP0 STATUS - RAC:
select inst_id,process, status, client_process, thread#, sequence#, block#, blocks from gv$managed_standby where process = 'MRPO';
select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status;
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
Checking For Dataguard Errorr:
select to_char(timestamp,'DD/MM/YY HH24:MI:SS') timestamp,severity, message_num, message from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
select * from v$ARCHIVE_GAP;
Missing Logs on Standby:
select local.thread# , local.sequence# from (select thread# , sequence# from v$archived_log where dest_id=1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#) ;
Starting MRP0:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Stoping MRP0:
RECOVER MANAGED STANDBY DATABASE CANCEL;
MRP0 STATUS - RAC:
select inst_id,process, status, client_process, thread#, sequence#, block#, blocks from gv$managed_standby where process = 'MRPO';
select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status;
Registring Logfile
alter database register logfile ' 26457 ';
How To Check Oracle Physical Standby is in Sync with the Primary or Not?
On Primary
set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest where DESTINATION is NOT NULL
/
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1
/
select max(sequence#),thread# from gv$log group by thread#;
set numwidth 15
select max(sequence#) current_seq from v$log;
/
On Standby
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;
/
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
/
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
/
select max(sequence#),thread# from gv$archived_log where applied='YES' group by thread#;
/
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;
/
Check which logs are missing
Run this on the standby...
select local.thread#, local.sequence# from (select thread# , sequence# from v$archived_log where dest_id=1) local
where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
/
Display info about all log destinations
To be run on the primary
set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
select ds.dest_id id, ad.status, ds.database_mode db_mode, ad.archiver type, ds.recovery_mode, ds.protection_mode, ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active, ds.archived_seq# from v$archive_dest_status ds, v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id
/
Display log destinations options
To be run on the primary
set numwidth 8 lines 100
column id format 99
select dest_id id , archiver, transmit_mode, affirm , async_blocks async, net_timeout net_time, delay_mins delay, reopen_secs reopen
, register,binding from v$archive_dest order by dest_id;
/
MRP Speed
Set Linesize 400
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.Mm.Yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.Mm.Yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);
Run this on the standby...
select local.thread#, local.sequence# from (select thread# , sequence# from v$archived_log where dest_id=1) local
where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
/
Display info about all log destinations
To be run on the primary
set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
select ds.dest_id id, ad.status, ds.database_mode db_mode, ad.archiver type, ds.recovery_mode, ds.protection_mode, ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active, ds.archived_seq# from v$archive_dest_status ds, v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id
/
Display log destinations options
To be run on the primary
set numwidth 8 lines 100
column id format 99
select dest_id id , archiver, transmit_mode, affirm , async_blocks async, net_timeout net_time, delay_mins delay, reopen_secs reopen
, register,binding from v$archive_dest order by dest_id;
/
MRP Speed
Set Linesize 400
Col Values For A65
Col Recover_start For A21
Select To_char(START_TIME,'Dd.Mm.Yyyy Hh24:Mi:ss') "Recover_start",To_char(Item)||' = '||To_char(Sofar)||' '||To_char(Units)||' '|| To_char(TIMESTAMP,'Dd.Mm.Yyyy Hh24:Mi') "Values" From V$Recovery_progress Where Start_time=(Select Max(Start_time) From V$Recovery_progress);
/
TIME IT TOOK TO APPLY A LOG
select TIMESTAMP,completion_time "ArchTime",SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",
round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc;
/
Problem: Recovery service has stopped for a while and there has been a gap between primary and standby side. After recovery process was started again, standby side is not able to catch primary side because of low log applying performance. Disk I/O and memory utilization on standby server are nearly 100%.
Solution:
1 – Rebooting the standby server reduced memory utilization a little.
2 – ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL8 DISCONNECT FROM SESSION;
In general, using the parallel recovery option is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. The performance improvement from the parallel recovery option is also dependent upon whether the operating system supports asynchronous I/O. If asynchronous I/O is not supported, the parallel recovery option can dramatically reduce recovery time. If asynchronous I/O is supported, the recovery time may be only slightly reduced by using parallel recovery.
3 -alter system Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096 scope = spfile;
Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096
When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.
4 – Kernel parameters that changed in order to reduce file system cache size.
dbc_max_pct 10 10 Immed
dbc_min_pct 3 3 Immed
select TIMESTAMP,completion_time "ArchTime",SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",
round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc;
/
Problem: Recovery service has stopped for a while and there has been a gap between primary and standby side. After recovery process was started again, standby side is not able to catch primary side because of low log applying performance. Disk I/O and memory utilization on standby server are nearly 100%.
Solution:
1 – Rebooting the standby server reduced memory utilization a little.
2 – ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL8 DISCONNECT FROM SESSION;
In general, using the parallel recovery option is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. The performance improvement from the parallel recovery option is also dependent upon whether the operating system supports asynchronous I/O. If asynchronous I/O is not supported, the parallel recovery option can dramatically reduce recovery time. If asynchronous I/O is supported, the recovery time may be only slightly reduced by using parallel recovery.
3 -alter system Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096 scope = spfile;
Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096
When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.
4 – Kernel parameters that changed in order to reduce file system cache size.
dbc_max_pct 10 10 Immed
dbc_min_pct 3 3 Immed
No comments:
Post a Comment
Thank for showing interest in giving comments/feedback/suggestions
Note: Only a member of this blog may post a comment.