Oracle 11g Data Guard Physical Standby Quick Configuration Guide (top)

Origin
Recent 10g and 11g physical standby configuration experiments found that Data Guard is actually easy, but lacks good documentation. I was experimenting with the official documentation and thought it wasn't very clear.

Google out of two PDF documents, read it feel much stronger than official documents. It may be useful for some friends to translate. I am also better acquainted with these two documents while translating. I have not translated English for a long time, can practice practiced hand by the way.

Original document (outside the wall):

Configure Dataguard 11gR2 Physical Standby Part 1
Configure Dataguard 11gR2 Physical Standby Part 2
The first part
Brief introduction
Data Guard is a feature of the Oracle database that provides redundancy to the database. Redundancy is achieved by creating a standby (physical replication) database, preferably in a different geographic location or on a different disk. The repository maintains data synchronization by applying changes on the main library. Repositories can use redo log applications (physical standby) or SQL application synchronization (logical standby).

The purpose of this article is to demonstrate that the Data Guard configuration is not complex and requires no special skills or training to learn to build. It will quickly show the reader the process of setting up a physical standby library. My goal is that, even if you first touch Data Guard, just think about using it or worry about whether it will be difficult to configure, this article will help you quickly build up a properly functioning physical standby library.

Why use Data Guard
Each of the Oracle high availability tools has its purpose. The reasons for using Data Guard are:

Redundancy of the entire database
Fast recovery in case of failure
Client can automatically re-connect after failure
Running backups in a standby library
Better fault mean time to repair
Not complicated.
System environment
After writing this article, I used DBCA to create a new database JED, and then rerun the configuration steps in the article to confirm that it applies to a basic Oracle 11g database. The main library is called JED and runs on a server called DEV-DB1. The repository is called JED2 and runs on a server called DEV-DB2.

No need to mention the basic premise
There are some basic settings that any production library should have. One of them is archive mode. For production libraries, this should be an obvious must-have configuration. If your production library doesn't have an archive model, you either need to start reading a book right away or you have to have a very, very good reason. I'm not sure who can really find a reason, but there are exceptions to any rule.

How to modify your database to archive mode:

sql> shutdown Immediate
Sql> Startup Mount
sql> ALTER DATABASE Archivelog;
sql> ALTER DATABASE open;
sql> archive log list;

Master Library Preparation
First, the repository is going to be the exact same copy of the main library, and it must receive the redo logs from the main library. In an Oracle database, a user can specify that an action does not produce a log (for example, using a nologging statement). This is a problem for the standby library. You must confirm that the user cannot instruct the database not to produce a redo log, which requires the Enable database's force logging feature. The Enable method is as follows:

sql> ALTER DATABASE force logging;
Sql> select Name, force_logging from V$database;

You should see force_logging listed as YES.

Second, you need to confirm that when the main library adds or deletes data files, the files are also added or deleted in the repository. Here's how to enable this feature:

Sql> alter system set standby_file_management = ' AUTO ';

Again, we want to confirm that the library has a backup log file (Standby log files). The standby log file is used by the repository to hold the redo logs received from the main library. There are two reasons why there is an alternate log file on the main library, one is that the main library may be converted to a standby repository, a backup log is required, and the repository will be built automatically if a backup log is built in the main library. The backup log should be as large as the online log, and the number of groups should be at least as much as the online log, or more. I like to give the backup log a number that is different from the online log, such as the online log group is 1 to 6, and the backup log is 11 to 16. Here's how to create an alternate log:

sql> ALTER DATABASE Add standby logfile Group One ('/oradata/jed/g11m01.sdo ', '/oradata/jed/g11m02.sdo ') size 50M;

If you do not use SSL for redo log transfer verification (generally not), then you need to use a password file for authentication. You must create a password file and set the parameter remote_login_passwordfile to EXCLUSIVE or SHARED. A generic database has a password file by default, and this parameter defaults to execusive. Check these two items first, if not the default, set the method as follows:

Sql> alter system set remote_login_passwordfile=exclusive Scope=spfile;
os> orapwd password=<sys User Password >

Finally, check that the Db_unique_name parameter of the database is set. If not, use alter system to set it up:

Sql> show Paramter db_unique_name;
Sql> alter system set DB_UNIQUE_NAME=SOME_NAME Scope=spfile;

Flash back to the database
I strongly recommend that you turn on the database flashback feature. Flashback allows you to restore a database to a previous point in time. This is useful when a failover occurs, which allows you to flash the old Main library back to the fault before converting it to a standby. If the Flashback feature is not enabled, you will have to rebuild the repository, which means copying the data file again. In addition to this benefit, flash pensive can in some cases allow you to avoid recovering data from a backup.

To enable the Flashback feature, you must first configure the quick Recovery Zone (flash/fast Recovery area). Here's how:

Sql> alter system set db_recovery_file_dest= ' & Fast Recovery area directory or ASM disk group name ';
Sql> alter system set db_recovery_file_dest_size=400g;

Once you have configured the Quick recovery area, you can enable the Flashback logging feature:

Sql> ALTER DATABASE flashback on;
Sql> select flashback_on from V$database;

Flashback_on The value of this column should be yes. If you encounter ORA-01153 error, it must be in the standby library to do this. You need to cancel the Redo log app first, enable the flashback log, and then re-enable the log app.

In the main library, the flashback log is enabled and the standby is not synchronized. You must manually enable the flashback log on both the master and the standby libraries. If you do not enable the flashback log, when a failover occurs, you will need to completely restart the creation of a standby library.

Sql*net Configuration
Before you create a standby, make sure that you can communicate between the databases of two servers, if we want to create a repository with RMAN's duplicate from active database command. We need to configure the listener and TNS names. You can configure it manually, or you can use the Network Configuration tool (NETCA). I prefer manual configuration because I'm older, and these profiles are not complicated

First, you need to configure the monitoring of the master standby repository. Although the database is automatically registered for monitoring, the repository must be in the Nomount state first if you want to create a standby using RMAN's duplicate command. In the Nomount state, the DB instance does not automatically register for monitoring, and you must configure static listening. It is also important to note that the database in the Nomount state must be connected using the private mode (dedicated server).

The TNS name files on both servers must be configured so that the master repository can find each other using the service Names in the Log_archive_dest_n and Fal_server parameters (which are described later). The specific configuration should resemble the following example.

Monitoring configuration for the main library (DEV-DB1):

Sid_list_listener=
(Sid_list =
(Sid_desc =
(Global_dbname = JED)
(Oracle_home =/oracle/product/11.2.0)
(Sid_name = JED)
)
)

Standby (DEV-DB2) for the monitoring configuration:

Sid_list_listener=
(Sid_list =
(Sid_desc =
(Global_dbname = JED2)
(Oracle_home =/oracle/product/11.2.0)
(Sid_name = JED2)
)
)

The TNS name file configuration for the main library:

JED2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dev-db2) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = JED2)
)
)

The TNS name file configuration for the standby library:

JED =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dev-db1) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = JED)
)
)

Redo Log Transport Configuration
Now the main repository can still communicate with each other, the next step is to configure the archive location and redo log transfer. We will first configure it on the main library, and then after the repository is created, modify the configuration of the standby.

To configure the archive location:

Sql> alter system set log_archive_dest_1 = ' Location=use_db_recovery_file_dest valid_for= (all_logfiles, All_roles) Db_unique_name=jed ';

This command specifies the fast recovery area as the archive location, which is used to archive all log files under all database roles. The official documentation says using valid_for= (Online_logfiles, All_roles), which will cause the repository to be unable to archive alternate log files because they are not online logs. However, if you use the All_logfiles option, the Master repository will be able to archive both online and standby logs. You must use All_logfiles if you want to back up the repository and back up the archive log at the same time.

Then configure the Redo log transfer to the standby library:

Sql> alter system set log_archive_dest_2 = ' service=jed2 async valid_for= (online_logfile,primary_role) db_unique_ Name=jed2 ';

This statement says that if this is the main library, the online log is transferred using the service name JED2, and the target library is named JED2.

Be aware that the Standby_archive_dest parameter is not required and has been deprecated by the official. When debugging, many people kindly advised me to set this parameter, but starting the database after setting this parameter, will only report Ora-32004:obsolete or deprecated parameter (s) specified for RDBMS instance error.

The other parameter to set is Fal_server. This parameter specifies where to find the missing archive log when there is a problem with the log transfer. It is used when there is a gap between the redo logs received by the repository. This can happen when a log transfer is interrupted, such as when you need to perform maintenance operations on the standby. There was no log transmission during the standby maintenance period, and the gap appeared. With this parameter set, the repository will proactively look for the missing logs and require the main library to be transferred.

Sql> alter system set Fal_server = ' JED2 ';

Note that the fal_client parameter has been deprecated in 11g.

Then we have to let the main library know the name of another library in the Data Guard configuration:

Sql> alter system set Log_archive_config = ' dg_config= (jed,jed2) ';

Once this is done, we can prepare the repository environment and start creating the repository.

Prepare for storage environment
Start preparing the repository environment now. There are a number of ways to perform these steps. What I'm writing here is what I think is the best way for me. You should experiment with many ways to see which one is right for you.

First, we want to create a password file and a parameter file (SPFile) for the standby library. The password file can be copied directly to the past, only need to change the name on the line. For example, the password file on the main library is $ORACLE _home/dbs/orapwjed. We copy it to the same location on the standby server, replacing the main repository with the SID of the repository and modifying its name to OrapwJED2.

To create a standby spfile, first create a startup parameter file (pfile):

Sql> create Pfile from SPFile;

I'd like to introduce a new feature that looks pretty good. Use RMAN to create a standby library SPFILE. The reason I don't use this feature is:

Anyway, I also need to copy the password file to the standby server, so it doesn't save me the time to copy the file.
To use this feature, you still need to do a lot of replacement work with the Parameter_value_convert parameter, and use the SPFILE statement and multiple SET statements to make sure everything is correct.
I find it easier to copy pfile in the past (you can even paste the copy directly), just change the name and change the parameters inside. This is easy, you can also learn a lot in the process of manual modification and debugging. I found that manual modification is faster than creating features with RMAN's spfile.

After you have created the pfile of the main library, copy it to the same location as the standby server and use the SID of the repository to modify its name. You need to make the following changes to Pfile:

Depending on the configuration and file location of your repository, you may need to modify the Audit_file_dest,control_files and DISPATCHERS parameters (and perhaps other parameters that need to be modified).
The db_unique_name in the log_archive_dest_1 parameter is modified to the corresponding unique name of the standby (this is JED2).
Log_archive_dest_2 parameter, modify the service name and database unique name for the master library (this is JED).
The Fal_server parameter modifies the service name that points to the main library.
Add the following parameters:
Db_unique_name=jed2
Db_file_name_convert and Log_file_name_convert. If the data files and log file locations of the master repository are different, these two parameters need to be set.
Then create the desired directory structure and modify the relevant files on the standby server. At a minimum, you need to modify the following to create directories and files:

$ORACLE _base/admin/$ORACLE _sid
$ORACLE _base/admin/$ORACLE _sid/adump (audit_file_dest configuration directory)
Data Files Directory
Control file Directory
Log file directory
Quick Recovery Area Directory
Add the repository information to the/etc/oratab file
Now you are ready to launch the repository instance to create the database. Create a spfile during the startup process.

sql> Startup Nomount Pfile=initjed2.ora
Sql> create SPFile from Pfile;
sql> shutdown
Sql> Startup Nomount
Sql> Show Parameter SPFile
Sql> exit

Show parameter SPFile displays the location of the SPFile, when the standby is in the Nomount state.

Prepare library creation
As in previous steps, there are several ways to create a database. In 11g, I'm going to use the replication feature of RMAN because it's easy. In the previous step, we copied the password file and the parameter file to the repository server, modified the parameter file, and created the SPFile. This makes it easier to use the Rman replication feature, but you can also skip the manual copying of passwords and parameter files, allowing RMAN to use commands such as Spfile,parameter_value_convert and set to help you do this automatically.

The command to create a repository using RMAN is straightforward. It instructs RMAN to replicate the currently active database (main library) directly to the secondary database (repository). This way you do not need to copy the backup of the main library to the standby server, and then restore the database. In today's storage technology, we have a faster and simpler way to replicate databases, but in order to showcase this new feature of 11g, and this feature is simple, I like to use it as much as possible.

rman> Connect Target [email protected]
Rman> Connect Catalog <catalogowner>@<catalogdb>
Rman> Connect Auxiliary [email protected]
Rman> duplicate target database for standby from active database;

After the 11.2.0.2.0 version, you can connect to the secondary database directly using connect target, but if you do not specify a user name and password, you will be reported invalid Username/password error when copying to the standby.

When the copy command is executed, I like to tail the alarm log file of the repository, observe which step the copy is taken and see if there is an error. Note that the ora-27037:unable to obtain file status error is normal for online and standby log files.

You can also replicate in parallel to improve performance. After you have allocated the main and standby multiple channels, execute the copy command:

Run
{
Allocate channel chan1 type disk;
Allocate channel chan2 type disk;
Allocate channel CHAN3 type disk;
Allocate channel CHAN4 type disk;
Allocate auxiliary channel AUX1 type disk;
Allocate auxiliary channel AUX2 type disk;
Allocate auxiliary channel AUX3 type disk;
Allocate auxiliary channel AUX4 type disk;
Duplicate target database for standby from active database;
}

If everything is OK, you will see an RMAN report similar to the following information:

Finished Duplicate Db at 07-may-10

When the repository copy is complete, I like to enable the Flashback log in the repository:

Sql> ALTER DATABASE flashback on;

Start Redo Log App
It is very easy to start or stop the redo log application. To start the Log app:

sql> ALTER database RECOVER MANAGED STANDBY database USING current LOGFILE

DISCONNECT from SESSION;

This command instructs the repository to start using an alternate log file for recovery. It also tells the repository command to go back to the command line interface when it is finished. If you want to stop recovery:

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

Verify that the log is applied correctly
You want to make sure that the redo log is being applied to the standby repository. First, we want to make sure that the archive destination configuration in the Master repository is valid:

Sql> Select dest_id, STATUS, DESTINATION, ERROR from V$archive_dest where dest_id<=2;

The destination status should be displayed as VALID.

Then verify that the redo log is actually applied and executed in the main library:

Sql> Select sequence#, First_time, Next_time, applied, archived from v$archived_log where name = ' JED2 ' ORDER by First_ time;

If the archive and log applications are normal, both the applied and archived columns should be yes. Many tutorials let this query sort by sequence# columns, but I don't recommend them. If you sort by sequence# column, when you do a failover, the serial number will start again from 1, when you use this query, you will not be able to see the latest records at the end of the results. I used to wonder why I couldn't find a new record because the new record didn't appear at the end, and I didn't see it. So, this query is sorted in first_time column.

If you find that the log is not being applied, it is possible that the redo log has a gap, in which case the repository cannot be applied to the log. But if your fal_server parameter is set correctly, this should not be a problem. You can check for a redo log gap on the main library:

Sql> Select STATUS, gap_status from v$archive_dest_status where dest_id = 2;

If everything is OK, you should return to VALID and NO GAP. If you want to test how the Fal_server this parameter works. You can turn off the repository first, then switch several logs in the main library, wait a minute, start the standby library, and then switch the logs again. Such a gap will soon appear. If the fal_server is set up correctly, the missing redo logs are transferred and applied.

The V$dataguard_status view is useful for finding errors and understanding what has happened. You can view the database status by executing the following query on the Master repository:

Sql> SELECT * from V$dataguard_status order by TIMESTAMP;

Sometimes you manually want to make sure that the data is really synced. A more convincing approach is to directly query the repository to see if the new data exists. You can open the repository as read-only state, cancel the log application first, and then execute the following command:

sql> ALTER DATABASE OPEN READ only;

You can then query whether the changed data is synchronized. 11G has supported the active repository, allowing the database to be opened in a read-only state while starting the log application.

Summarize
Now that you have a well-configured data Guard, you have a redundant database. I don't want to leave the main preparation conversion, failover, rebuilding the library and so on, these topics will be placed in the second part of this article.

I hope this article will help you to create your Data Guard environment more easily and faster.

Oracle 11g Data Guard Physical Standby Quick Configuration Guide (top)