Introduction
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can 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.
Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a physical standby database or a logical standby database:
Figure 1-1 shows a typical Data Guard configuration in which archived redo log files are being applied to a physical standby database.
Data Guard Services
The following sections explain how Data Guard manages the transmission of redo data, the application of redo data, and changes to the database roles:
For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database, as shown in Figure 1-2.
Figure 1-2 Automatic Updating of a Physical Standby Database
For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database, as shown in Figure 1-3.
Figure 1-3 Automatic Updating of a Logical Standby Database
Data Guard Interfaces
Oracle provides three ways to manage a Data Guard environment:
1. SQL*Plus and SQL Statements
Configuring Oracle DataGuard using SQL commands - Creating a physical standby database
Step 1) Getting the primary database ready (on Primary host)
We are assuming that you are using SPFILE for your current(Primary) instance. You can check if your instance is using SPFILE or not using spfile parameter.
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
You can create spfile as shown below. (on primary host)
SQL> create spfile from pfile;
File created.
The primary database must meet two conditions before a standby database can be created from it:
SQL> select force_logging from v$database;
FOR
—
NO
Also the database is not in archive log mode as shown below.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /dy/oracle/product/db10g/dbs/arch
Oldest online log sequence 10
Current log sequence 12
Now we will start the database in archive log and force logging mode.
Starting database in archive log mode:-
We need to set following 2 parameters to set the database in archive log mode.
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
log_archive_format = “ARCH_%r_%t_%s.ARC”
If a database is in force logging mode, all changes, except those in temporary tablespaces, will be logged, independently from any nologging specification.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
So now our primary database is in archive log mode and in force logging mode.
SQL> select log_mode, force_logging from v$database;
LOG_MODE FOR
———— —
ARCHIVELOG YES
init.ora file for primary
control_files = /u00/oracle/product/10.2.0/oradata_orcl/orclcontrol.ctl
db_name = orcl
db_domain = us.oracle.com
db_block_size = 8192
pga_aggregate_target = 250M
processes = 300
sessions = 300
open_cursors = 1024
undo_management = AUTO
undo_tablespace = undotbs
compatible = 10.2.0
sga_target = 600M
nls_language = AMERICAN
nls_territory = AMERICA
background_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/bdump
user_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/udump
core_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/cdump
db_unique_name=’PRIMARY’
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
Log_archive_dest_state_1=ENABLE
Step 2) Creating the standby database
Since we are creating a physical stand by database we have to copy all the datafiles
of primary database to standby location. For that, you need to shutdown main database, copy the files of main database to new location and start the main database again.
Step 3) Creating a standby database control file
A control file needs to be created for the standby system. Execute the following on the primary system:
SQL> alter database create standby controlfile as ‘/dy/oracle/product/db10g/dbf/standby.ctl’;
Database altered.
Step 4) Creating an init file
SQL> show parameters spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /dy/oracle/product/db10g/dbs/s
pfiletest.ora
Step 5) Changing init.ora file for standby database
A pfile is created from the spfile. This pfile needs to be modified and then be used on the standby system to create an spfile from it. So create a pfile from spfile on primary database.
create pfile=’/some/path/to/a/file’ from spfile
SQL> create pfile=’/dy/oracle/product/db10g/dbs/standby.ora’ from spfile;
File created.
The following parameters must be modified or added:
control_files = /u00/oracle/product/10.2.0/oradata/orcl/standby_orcl.ctl
db_name = orcl
db_domain = us.oracle.com
db_block_size = 8192
pga_aggregate_target = 250M
processes = 300
sessions = 300
open_cursors = 1024
undo_management = AUTO
undo_tablespace = undotbs
compatible = 10.2.0
sga_target = 600M
nls_language = AMERICAN
nls_territory = AMERICA
background_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/bdump
user_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/udump
core_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/cdump
db_unique_name=’STANDBY’
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
Log_archive_dest_state_1=ENABLE
standby_archive_dest=/u00/oracle/product/10.2.0/prim_archive
db_file_name_convert=’/u00/oracle/product/10.2.0/oradata’,\
‘/u00/oracle/product/10.2.0/oradata/orcl’
log_file_name_convert=’/u00/oracle/product/10.2.0/oradata’,\
‘/u00/oracle/product/10.2.0/oradata/orcl’
standby_file_management=auto
FAL_Client=’to_standby’
Step 7) Creating the spfile on the standby database
set ORACLE_SID=orcl
sqlplus “/ as sysdba”
create spfile from pfile=’/…/../modified-pfile’;
Step 8- On standby database
SQL> startup nomount pfile=standby.ora
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 754981192 bytes
Database Buffers 314572800 bytes
Redo Buffers 2203648 bytes
SQL> alter database mount standby database;
Database altered.
Add following parameters to standby side
FAL_Client=’to_standby’
FAL_Server=’to_primary’
Log_archive_dest_2=’Service=to_primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
Log_archive_dest_state_2=ENABLE
remote_login_passwordfile=’SHARED’
Add following parameters to primary side
Log_archive_dest_2=’Service=to_standby lgwr’
Log_archive_dest_state_2=ENABLE
Standby_File_Management=’AUTO’
REMOTE_LOGIN_PASSWORDFILE=’SHARED’
Create password file on both sides
orapwd file=/u00/oracle/product/10.2.0/db_1/dbs/orapworcl.ora password=oracle entries=5 force=y
FTP the password file to standby location
Step 9) Configuring the listener
Creating net service names
Net service names must be created on both the primary and standby database that will be used by log transport services. That is: something like to following lines must be added in the tnsnames.ora.
Setup listener configuration
On Primary:
ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
)
)
SID_LIST_ORCL=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
On Standby:
SID_LIST_orcl=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
orcl =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215)(PORT = 1538))
)
)
TNSNAMES settings
On Primary:
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
TO_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215.us.oracle.com)(PORT = 1538))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
On standby:
TO_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215.us.oracle.com)(PORT = 1538))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
On Standby side following below commands
SQL> startup nomount pfile=standby_orcl.ora
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1980744 bytes
Variable Size 171968184 bytes
Database Buffers 452984832 bytes
Redo Buffers 2211840 bytes
SQL> alter database mount standby database;
Database altered.
Try to connect to stand by database from primary database
Following connections should work now
From Primary host:
sqlplus sys/oracle@orcl as sysdba –> This will connect to primary database
sqlplus sys/oracle@to_standby as sysdba –> This will connect to standby database from primary host
From Standby host
sqlplus sys/oracle@orcl as sysdba –> This will connect to standby database
sqlplus sys/oracle@to_primary as sysdba –> This will connect to primary database from standby host
LOG SHIPPING
On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.
SQL> Alter system set Log_archive_dest_state_2=ENABLE scope=both;
System Altered.
Check the sequence # and the archiving mode by executing following command.
SQL> Archive Log List
Then switch the logfile on primary side
SQL> Alter system switch logfile;
System Altered.
Start physical apply log service on standby side.
SQL> Alter Database Recover Managed Standby Database Disconnect;
Database Altered.
Now the session will be available to you and MRP will work as a background process and apply the redo logs.
You can check whether the log is applied or not by querying V$ARCHIVED_LOG.
SQL> Select Name, Applied, Archived from v$Archived_log;
This query will return the name of archived files and their status of being archived and applied.
Once you complete above step, you are done with physical standby. We can verify the log files which got applied using following commands.
On Standby side
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
9 rows selected.
On Primary side
SQL> Select Status, Error from v$Archive_dest where dest_id=2;
STATUS ERROR
——— —————————————————————–
VALID
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
On Standby side
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
10 12-JUN-07 12-JUN-07
10 rows selected.
If you can see, after switching archive log on primary side, an archive log file got applied to standby database.
Again on primary
SQL> alter system switch logfile;
System altered.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
10 12-JUN-07 12-JUN-07
11 12-JUN-07 12-JUN-07
11 rows selected.
After switching log file on primary, that log file got applied to standby database. This is called high protection mode. I will explain the switching to different modes in my next post. keep watching !!
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can 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.
Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a 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 schema, 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. The logical standby database is kept synchronized with the primary database by transforming the data in the redo 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. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.Figure 1-1 shows a typical Data Guard configuration that contains a primary database instance that transmits redo data to a physical standby database. The physical standby database is remotely located from the primary database instance for disaster recovery and backup operations. You can configure the standby database at the same location as the primary database. However, for disaster recovery purposes, Oracle recommends you configure standby databases at remote locations.
Figure 1-1 shows a typical Data Guard configuration in which archived redo log files are being applied to a physical standby database.
Data Guard Services
The following sections explain how Data Guard manages the transmission of redo data, the application of redo data, and changes to the database roles:
Log Transport Services
Control the automated transfer of redo data from the production database to one or more archival destinations.
Log Apply Services
Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database.
Role Management Services
Change the role of a database from a standby database to a primary database, or from a primary database to a standby database using either a switchover or a failover operation.A database can operate in one of the two mutually exclusive roles: primary or standby database.
- Failover
During a failover, one of the standby databases takes the primary database role.
- Switchover
Primary and standby database can continue to alternate roles. The primary database can switch the role to a standby database; and one of the standby databases can switch roles to become the primary.The main difference between physical and logical standby databases is the manner in which log apply services apply the archived redo data:
For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database, as shown in Figure 1-2.
Figure 1-2 Automatic Updating of a Physical Standby Database
For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database, as shown in Figure 1-3.
Figure 1-3 Automatic Updating of a Logical Standby Database
Data Guard Interfaces
Oracle provides three ways to manage a Data Guard environment:
1. SQL*Plus and SQL Statements
Using SQL*Plus and SQL commands to manage Data Guard environment.The following SQL statement initiates a switchover operation:
SQL> alter database commit to switchover to physical standby;2. Data Guard Broker GUI Interface (Data Guard Manager)
Data Guard Manger is a GUI version of Data Guard broker interface that allows you to automate many of the tasks involved in configuring and monitoring a Data Guard environment.3. Data Guard Broker Command-Line Interface (CLI)
It is an alternative interface to using the Data Guard Manger. It is useful if you want to use the broker from batch programs or scripts. You can perform most of the activities required to manage and monitor the Data Guard environment using the CLI.The Oracle Data Guard broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. The following are some of the operations that the broker automates and simplifies:
- Automated creation of Data Guard configurations incorporating a primary database, a new or existing (physical or logical) standby database, log transport services, and log apply services, where any of the databases could be Real Application Clusters (RAC) databases.
- Adding up to 8 additional new or existing (physical or logical, RAC, or non-RAC) standby databases to each existing Data Guard configuration, for a total of one primary database, and from 1 to 9 standby databases in the same configuration.
- Managing an entire Data Guard configuration, including all databases, log transport services, and log apply services, through a client connection to any database in the configuration.
- Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.
- Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the log apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.
Configuring Oracle DataGuard using SQL commands - Creating a physical standby database
Step 1) Getting the primary database ready (on Primary host)
We are assuming that you are using SPFILE for your current(Primary) instance. You can check if your instance is using SPFILE or not using spfile parameter.
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
You can create spfile as shown below. (on primary host)
SQL> create spfile from pfile;
File created.
The primary database must meet two conditions before a standby database can be created from it:
- It must be in force logging mode and
- It must be in archive log mode (also automatic archiving must be enabled and a local archiving destination must be defined.
SQL> select force_logging from v$database;
FOR
—
NO
Also the database is not in archive log mode as shown below.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /dy/oracle/product/db10g/dbs/arch
Oldest online log sequence 10
Current log sequence 12
Now we will start the database in archive log and force logging mode.
Starting database in archive log mode:-
We need to set following 2 parameters to set the database in archive log mode.
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
log_archive_format = “ARCH_%r_%t_%s.ARC”
If a database is in force logging mode, all changes, except those in temporary tablespaces, will be logged, independently from any nologging specification.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
So now our primary database is in archive log mode and in force logging mode.
SQL> select log_mode, force_logging from v$database;
LOG_MODE FOR
———— —
ARCHIVELOG YES
init.ora file for primary
control_files = /u00/oracle/product/10.2.0/oradata_orcl/orclcontrol.ctl
db_name = orcl
db_domain = us.oracle.com
db_block_size = 8192
pga_aggregate_target = 250M
processes = 300
sessions = 300
open_cursors = 1024
undo_management = AUTO
undo_tablespace = undotbs
compatible = 10.2.0
sga_target = 600M
nls_language = AMERICAN
nls_territory = AMERICA
background_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/bdump
user_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/udump
core_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/cdump
db_unique_name=’PRIMARY’
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
Log_archive_dest_state_1=ENABLE
Step 2) Creating the standby database
Since we are creating a physical stand by database we have to copy all the datafiles
of primary database to standby location. For that, you need to shutdown main database, copy the files of main database to new location and start the main database again.
Step 3) Creating a standby database control file
A control file needs to be created for the standby system. Execute the following on the primary system:
SQL> alter database create standby controlfile as ‘/dy/oracle/product/db10g/dbf/standby.ctl’;
Database altered.
Step 4) Creating an init file
SQL> show parameters spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /dy/oracle/product/db10g/dbs/s
pfiletest.ora
Step 5) Changing init.ora file for standby database
A pfile is created from the spfile. This pfile needs to be modified and then be used on the standby system to create an spfile from it. So create a pfile from spfile on primary database.
create pfile=’/some/path/to/a/file’ from spfile
SQL> create pfile=’/dy/oracle/product/db10g/dbs/standby.ora’ from spfile;
File created.
The following parameters must be modified or added:
- control_files
- standby_archive_dest
- db_file_name_convert (only if directory structure is different on primary and standby server)
- log_file_name_convert (only if directory structure is different on primary and standby server)
- log_archive_format
- log_archive_dest_1 — This value is used if a standby becomes primary during a switchover or a failover.
- standby_file_management — Set to auto
control_files = /u00/oracle/product/10.2.0/oradata/orcl/standby_orcl.ctl
db_name = orcl
db_domain = us.oracle.com
db_block_size = 8192
pga_aggregate_target = 250M
processes = 300
sessions = 300
open_cursors = 1024
undo_management = AUTO
undo_tablespace = undotbs
compatible = 10.2.0
sga_target = 600M
nls_language = AMERICAN
nls_territory = AMERICA
background_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/bdump
user_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/udump
core_dump_dest=/u00/oracle/product/10.2.0/db_1/admin/orcl/cdump
db_unique_name=’STANDBY’
Log_archive_dest_1=’Location=/u00/oracle/product/10.2.0/archive/orcl’
Log_archive_dest_state_1=ENABLE
standby_archive_dest=/u00/oracle/product/10.2.0/prim_archive
db_file_name_convert=’/u00/oracle/product/10.2.0/oradata’,\
‘/u00/oracle/product/10.2.0/oradata/orcl’
log_file_name_convert=’/u00/oracle/product/10.2.0/oradata’,\
‘/u00/oracle/product/10.2.0/oradata/orcl’
standby_file_management=auto
FAL_Client=’to_standby’
Step 7) Creating the spfile on the standby database
set ORACLE_SID=orcl
sqlplus “/ as sysdba”
create spfile from pfile=’/…/../modified-pfile’;
Step 8- On standby database
SQL> startup nomount pfile=standby.ora
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 754981192 bytes
Database Buffers 314572800 bytes
Redo Buffers 2203648 bytes
SQL> alter database mount standby database;
Database altered.
Add following parameters to standby side
FAL_Client=’to_standby’
FAL_Server=’to_primary’
Log_archive_dest_2=’Service=to_primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
Log_archive_dest_state_2=ENABLE
remote_login_passwordfile=’SHARED’
Add following parameters to primary side
Log_archive_dest_2=’Service=to_standby lgwr’
Log_archive_dest_state_2=ENABLE
Standby_File_Management=’AUTO’
REMOTE_LOGIN_PASSWORDFILE=’SHARED’
Create password file on both sides
orapwd file=/u00/oracle/product/10.2.0/db_1/dbs/orapworcl.ora password=oracle entries=5 force=y
FTP the password file to standby location
Step 9) Configuring the listener
Creating net service names
Net service names must be created on both the primary and standby database that will be used by log transport services. That is: something like to following lines must be added in the tnsnames.ora.
Setup listener configuration
On Primary:
ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
)
)
SID_LIST_ORCL=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
On Standby:
SID_LIST_orcl=
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
orcl =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215)(PORT = 1538))
)
)
TNSNAMES settings
On Primary:
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
TO_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215.us.oracle.com)(PORT = 1538))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
On standby:
TO_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun216.us.oracle.com)(PORT = 1530))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocsun215.us.oracle.com)(PORT = 1538))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
On Standby side following below commands
SQL> startup nomount pfile=standby_orcl.ora
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1980744 bytes
Variable Size 171968184 bytes
Database Buffers 452984832 bytes
Redo Buffers 2211840 bytes
SQL> alter database mount standby database;
Database altered.
Try to connect to stand by database from primary database
Following connections should work now
From Primary host:
sqlplus sys/oracle@orcl as sysdba –> This will connect to primary database
sqlplus sys/oracle@to_standby as sysdba –> This will connect to standby database from primary host
From Standby host
sqlplus sys/oracle@orcl as sysdba –> This will connect to standby database
sqlplus sys/oracle@to_primary as sysdba –> This will connect to primary database from standby host
LOG SHIPPING
On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.
SQL> Alter system set Log_archive_dest_state_2=ENABLE scope=both;
System Altered.
Check the sequence # and the archiving mode by executing following command.
SQL> Archive Log List
Then switch the logfile on primary side
SQL> Alter system switch logfile;
System Altered.
Start physical apply log service on standby side.
SQL> Alter Database Recover Managed Standby Database Disconnect;
Database Altered.
Now the session will be available to you and MRP will work as a background process and apply the redo logs.
You can check whether the log is applied or not by querying V$ARCHIVED_LOG.
SQL> Select Name, Applied, Archived from v$Archived_log;
This query will return the name of archived files and their status of being archived and applied.
Once you complete above step, you are done with physical standby. We can verify the log files which got applied using following commands.
On Standby side
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
9 rows selected.
On Primary side
SQL> Select Status, Error from v$Archive_dest where dest_id=2;
STATUS ERROR
——— —————————————————————–
VALID
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
On Standby side
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
10 12-JUN-07 12-JUN-07
10 rows selected.
If you can see, after switching archive log on primary side, an archive log file got applied to standby database.
Again on primary
SQL> alter system switch logfile;
System altered.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
1 11-JUN-07 12-JUN-07
2 12-JUN-07 12-JUN-07
3 12-JUN-07 12-JUN-07
4 12-JUN-07 12-JUN-07
5 12-JUN-07 12-JUN-07
6 12-JUN-07 12-JUN-07
7 12-JUN-07 12-JUN-07
8 12-JUN-07 12-JUN-07
9 12-JUN-07 12-JUN-07
10 12-JUN-07 12-JUN-07
11 12-JUN-07 12-JUN-07
11 rows selected.
After switching log file on primary, that log file got applied to standby database. This is called high protection mode. I will explain the switching to different modes in my next post. keep watching !!
No comments:
Post a Comment