Oracle DG Physical Standby environment setup

Oracle Data Guard, logical standby and physical standby. Here are the steps to build a physical standby environment.

First, enable Force Logging

Set the primary database to force logging mode. Use the following statement:

View Status:

Sql> SELECT database_role,force_logging from V$database;

Database_role force_logging

----------------  ---------------

PRIMARY NO

Modify Mode

sql> ALTER DATABASE force logging;

Database altered.

Cancel Force logging mode:

Sql> ALTER DATABASE no force logging;

Database altered.

Explanation: Why change to force Logging

There are DDL statements that can avoid writing redo by specifying the nologging clause (the goal is to increase speed, sometimes it works). When you specify that the database is force logging mode, the database records all operations except temporary tablespaces or temporary rollback segments, ignoring specified parameters such as nologging. If a statement such as Nologging is executed while executing force logging, then force logging waits until all such statements are executed.

Force logging is saved as a fixed parameter in the control file, so it is not affected by operations such as restart (only once), and if you want to cancel, you can turn off the enforcement record through the alter DATABASE no FORCE logging statement.

Second, create the key file (if it does not exist)

All databases in the same data guard configuration must have separate key files and must ensure that the SYS user of all database servers has the same password in the same data guard configuration to ensure the smooth transmission of the redo data. Because the Redo transport service transmits redo data through authenticated network sessions, the session is authenticated using the SYS user password contained in the key file.

If you use DBCA to build the library, Oracle automatically creates the key file, which is in the%oracle_home%/database directory, and it doesn't matter if the key file is not found in the directory, Oracle provides a command to create a key file: Orapwd , in the%oracle_home%/bin directory, there are two ways to invoke this command: with a parameter call and without a parameter call.

When called without a parameter, returns the invocation and Parameter form of the command, for example:

[Oracle@localhost ~]$ Orapwd

Usage:orapwd file=<fname> password=<password> entries=<users> force=<y/n>

where

File-name of password file (mand),

Password-password for SYS (Mand),

Entries-maximum number of distinct DBA and force-whether to overwrite existing file (opt),

Opers (opt),

There are no spaces around the equal-to (=) character.

which

File: Specifies the key file name and path.

Password:sys user password.

Entries: Specifies the maximum number of users that the database can have SYSDBA permissions.

Force: If file exists overwrite.

The ORAPWD command is very simple to use, and file and password are required parameters.

Note that the naming rules for Windows platforms and Linux/unix platform key files are not the same:

Windows platform naming rules: Pwd[sid].ora

Linux/unix Platform Command rule: orapw[sid]--NOTE: No file name, (case sensitive)

Examples are as follows:

[Oracle@localhost dbs]$ orapwd FILE=/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/DBS/ORAPWORCL password=admin entries=30

Third, the configuration standby redologs

For maximum protection and maximum availability patterns, it is recommended that you configure standby redologs for the standby database (not configured or available, Oracle will automatically create the archive on the standby database side and be virtualized as a group of standby redologs), and transmits redo data using LGWR sync mode.

1. About Standby Redologs

Oracle recommends that DBAs consider standby redologs configuration issues when creating standby databases. Standby Redologs is very similar to online redologs, it should be said that the two are only different service objects, other parameter properties, and even the operation of the command format is almost the same, the DBA in the design of Standby Redologs can use to create online Redologs ideas, such as creating several filegroups, redundancy of multiple files per group, and so on. In addition to this, Oracle provides some standard recommendations, as follows:

(1) Ensure that the file size of the standby redologs is the same size as the primary database online Redologs file. This is very good understanding, is to receive and apply conveniently.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

(2) Create an appropriate number of log groups. Generally, the Standby redologs log group is at least one more than the online redologs log file group of the primary database. Recommended standby the number of Redologs log groups is based on the number of threads in the primary database (the number of threads here can be understood as the number of nodes in the RAC environment).

There is a recommended formula for reference: (number of log groups per thread +1) x maximum number of threads.

For example, the primary database has two threads, each of which allocates two sets of logs, and the number of standby log groups is recommended to 6 groups, which can be used to reduce the likelihood that the primary database instance LGWR process is locked.

Tip: A logical standby database might need to increase the number of standby redologs groups (or increase the archive process) depending on the workload, because the logical standby database might need to write online redologs files at the same time.

2. Management Standby Redologs

Standby Redologs is almost exactly the same as online redologs, except that you need to specify one more Standby keyword when creating or deleting it.

View Redo log:

Sql> SELECT group#,type,member from V$logfile;

group# TYPE Member

---------- ------- --------------------------------------------------

3 Online/u01/app/oracle/oradata/orcl/redo03.log

2 Online/u01/app/oracle/oradata/orcl/redo02.log

1 Online/u01/app/oracle/oradata/orcl/redo01.log

Add a new standby Redologs group (note that the group number does not repeat with the currently existing online redologs group) and assign a member to the group:

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log ') size 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log ') size 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log ') size 50M;

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log ') size 50M;

Deleting the standby Redologs group is just as easy:

sql> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

You can view the standby redologs created in the current database through dynamic performance view V$logfile, for example:

Sql> SELECT group#,type,member from V$logfile;

group# TYPE Member

---------- ------- --------------------------------------------------

3 Online/u01/app/oracle/oradata/orcl/redo03.log

2 Online/u01/app/oracle/oradata/orcl/redo02.log

1 Online/u01/app/oracle/oradata/orcl/redo01.log

4 Standby/u01/app/oracle/oradata/orcl/redo04.log

5 Standby/u01/app/oracle/oradata/orcl/redo05.log

6 Standby/u01/app/oracle/oradata/orcl/redo06.log

7 Standby/u01/app/oracle/oradata/orcl/redo07.log

Tip: Distinguish the record from the Type column in this view is online redologs or standby redologs.