Data Guard Physical Standby Setup in Oracle Database 11g Release 2
===================================================================
On primary database Issue these commands. {primary dbname=PROD, standby dbname=PROD_STBY}
================================= ============================================
oracore@r12prod1$ orapwd file=orapwPROD password=sys123 force=y entries=10 Ignorecase=y
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PROD_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PROD_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=PROD_STBY;
ALTER SYSTEM SET FAL_CLIENT=PROD;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/DBCORE/oradata/PROD_STBY','/u01/DBCORE/oradata/PROD' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/DBCORE/oradata/PROD_STBY','/u01/DBCORE/oradata/PROD' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> select member from v$logfile; note: check the location
ALTER DATABASE ADD LOGFILE ('/u01/DBCORE/oradata/PROD/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/DBCORE/oradata/PROD/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/DBCORE/oradata/PROD/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/DBCORE/oradata/PROD/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/DBCORE/oradata/PROD/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/DBCORE/oradata/PROD/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/DBCORE/oradata/PROD/standby_redo04.log') SIZE 50M;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracore/PROD_STBY.ctl';
create pfile from spfile;
create pfile='/home/oracore/initPROD_STBY.ora' from spfile;
Now send the dbfiles,controlfile,initfile,passwordfile to Standby location.
Note:shut down the db and send db files.
==============================
[oracore@r12prod1 PROD]$ cd /u01/DBCORE/oradata/PROD
[oracore@r12prod1 PROD]$ scp -rpv * oracore@r12prod2:/u01/DBCORE/oradata/PROD_STBY
[oracore@r12prod1 ~]$ scp -rpv PROD_STBY.ctl oracore@r12prod2:/u01/DBCORE/oradata/PROD_STBY
[oracore@r12prod1 dbs]$ scp -rpv initPROD.ora orapwPROD oracore@r12prod2:/u01/DBCORE/product/11.2.0/dbhome/dbs/
Open Init file and edit the parameter
===========================
[oracore@r12prod2 dbs]$ vi initPROD_STBY.ora
PROD_STBY.__db_cache_size=822083584
PROD_STBY.__java_pool_size=16777216
PROD_STBY.__large_pool_size=16777216
PROD_STBY.__oracle_base='/u01/DBCORE'#ORACLE_BASE set from environment
PROD_STBY.__pga_aggregate_target=805306368
PROD_STBY.__sga_target=1174405120
PROD_STBY.__shared_io_pool_size=0
PROD_STBY.__shared_pool_size=301989888
PROD_STBY.__streams_pool_size=0
*.audit_file_dest='/u01/DBCORE/admin/PROD_STBY/adump'
*.control_files='/u01/DBCORE/oradata/PROD_STBY/PROD_STBY.ctl'
*.db_file_name_convert='/u01/DBCORE/oradata/PROD','/u01/DBCORE/oradata/PROD_STBY'
*.fal_client='PROD_STBY'
*.fal_server='PROD'
*.db_unique_name='PROD_STBY'
*.log_file_name_convert='/u01/DBCORE/oradata/PROD','/u01/DBCORE/oradata/PROD_STBY'
*.log_archive_dest_2='SERVICE=PROD NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'
Update tns and listener entries on both primary and standby
============================================
STandby db
==========
oracore@r12prod2 $ orapwd file=orapwPROD_STBY password=sys123 force=y entries=10 Ignorecase=y
[oracore@r12prod2 admin]$ lsnrctl start LISTENER
SQL> startup nomount pfile='/u01/DBCORE/product/11.2.0/dbhome/dbs/initPROD_STBY.ora';
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> SHUT IMMEDIATE;
SQL> startup nomount;
SQL> alter database mount standby database; (note: check the archive log files)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
==========================================================================================
Database altered.
===================================================================================================
Test Log Transport
On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
=========================================================================================================
How To: Configure Data Guard Broker \\
=================================== 1.) Check parameter DG_BROKER_START on primary: Error:ORA-16525: the Data Guard broker is not yet available ( Referred from metalink SQL> show parameter DG_BROKER_START =============================================== NAME TYPE VALUE Cause: The Data Guard broker process has not yet been started, is initializing, or failed to start. ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE Action: If the broker has not been started set DRS_START to true and allow the broker to finish initializing before making the request 2.) Set DG_BROKER_START to true on primary: If the broker failed to start check the Data Guard log for possible errors. Otherwise, retry the operation. SQL> alter system set DG_BROKER_START=true scope=both; System altered. 3.)Check DG_BROKER_START on standby side: SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE 4.) Set DG_BROKER_START to true on standby: SQL> alter system set DG_BROKER_START=true scope=both; System altered. 5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME: Update the listener.ora file on primary and standby Primary Listener Entry ================= LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = r12prod1.oracle.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD) ) (SID_DESC = (GLOBAL_DBNAME = PROD_DGMGRL.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD) ) ) Note: Stop and start the listener after updating the entires ============================================= Standby Listener Entry ================== LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = r12prod2)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD_STBY.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD_STBY) ) (SID_DESC = (GLOBAL_DBNAME = PROD_STBY_DGMGRL.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD_STBY) ) ) Note: Stop and start the listener after updating the entires ============================================= 6.) Now to create the data guard broker configurations: [oracore@r12prod1 admin]$ dgmgrl DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/sys123 Connected. DGMGRL> create configuration 'PRODSTBY' > as primary database is 'PROD' > connect identifier is PROD; O/P: ==== Configuration "PRODSTBY" created with primary database "PROD". Check if its configured ================== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED Now add the Standby entry on Primary ====================== DGMGRL> add database 'PROD_STBY' as > connect identifier is PROD_STBY > maintained as physical; O/p === Database "PROD_STBY" added Check if its configured ================== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database PROD_STBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED Now Enable the configuration ======================= DGMGRL> enable configuration Enabled. To Check ======== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database PROD_STBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS To check the properties. =================== DGMGRL> show database verbose 'PROD' DGMGRL> show database verbose 'PROD_STBY' To Switchover database using dgmgrl ============================== DGMGRL> switchover to 'PROD_STBY';
Oracle Data Guard Interview Questions & Answers
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 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 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
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|
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 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.
Step for Physical Standby
These are the steps to follow:
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
col name format a20
col thread# format 999
col sequence# format 999
col first_change# format 999999
col next_change# format 999999
SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,
next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME”
FROM v$archived_log
V$ log_history
Tell me about parameter which is used for standby database?
Log_Archive_Dest_n
Log_Archive_Dest_State_n
Log_Archive_Config
Log_File_Name_Convert
Standby_File_Managment
DB_File_Name_Convert
DB_Unique_Name
Control_Files
Fat_Client
Fat_Server
The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.
Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
=================================== 1.) Check parameter DG_BROKER_START on primary: Error:ORA-16525: the Data Guard broker is not yet available ( Referred from metalink SQL> show parameter DG_BROKER_START =============================================== NAME TYPE VALUE Cause: The Data Guard broker process has not yet been started, is initializing, or failed to start. ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE Action: If the broker has not been started set DRS_START to true and allow the broker to finish initializing before making the request 2.) Set DG_BROKER_START to true on primary: If the broker failed to start check the Data Guard log for possible errors. Otherwise, retry the operation. SQL> alter system set DG_BROKER_START=true scope=both; System altered. 3.)Check DG_BROKER_START on standby side: SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE 4.) Set DG_BROKER_START to true on standby: SQL> alter system set DG_BROKER_START=true scope=both; System altered. 5.) Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME: Update the listener.ora file on primary and standby Primary Listener Entry ================= LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = r12prod1.oracle.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD) ) (SID_DESC = (GLOBAL_DBNAME = PROD_DGMGRL.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD) ) ) Note: Stop and start the listener after updating the entires ============================================= Standby Listener Entry ================== LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = r12prod2)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD_STBY.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD_STBY) ) (SID_DESC = (GLOBAL_DBNAME = PROD_STBY_DGMGRL.oracle.com) (ORACLE_HOME= /u01/dataguard/app/oracore/product/11.2.0/db_1) (SID_NAME = PROD_STBY) ) ) Note: Stop and start the listener after updating the entires ============================================= 6.) Now to create the data guard broker configurations: [oracore@r12prod1 admin]$ dgmgrl DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/sys123 Connected. DGMGRL> create configuration 'PRODSTBY' > as primary database is 'PROD' > connect identifier is PROD; O/P: ==== Configuration "PRODSTBY" created with primary database "PROD". Check if its configured ================== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED Now add the Standby entry on Primary ====================== DGMGRL> add database 'PROD_STBY' as > connect identifier is PROD_STBY > maintained as physical; O/p === Database "PROD_STBY" added Check if its configured ================== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database PROD_STBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED Now Enable the configuration ======================= DGMGRL> enable configuration Enabled. To Check ======== DGMGRL> show configuration Configuration - PRODSTBY Protection Mode: MaxPerformance Databases: PROD - Primary database PROD_STBY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS To check the properties. =================== DGMGRL> show database verbose 'PROD' DGMGRL> show database verbose 'PROD_STBY' To Switchover database using dgmgrl ============================== DGMGRL> switchover to 'PROD_STBY';
Oracle Data Guard Interview Questions & Answers
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 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 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
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|
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 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.
Step for Physical Standby
These are the steps to follow:
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
col name format a20
col thread# format 999
col sequence# format 999
col first_change# format 999999
col next_change# format 999999
SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,
next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME”
FROM v$archived_log
V$ log_history
Tell me about parameter which is used for standby database?
Log_Archive_Dest_n
Log_Archive_Dest_State_n
Log_Archive_Config
Log_File_Name_Convert
Standby_File_Managment
DB_File_Name_Convert
DB_Unique_Name
Control_Files
Fat_Client
Fat_Server
The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.
Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
No comments :
Post a Comment