Oracle 10 Gb single-instance database data guard logical standby configuration details (according to the official documents)

----------- Step-by-step instructions for creating a logical standby database -----------

1. Create a physical standby database (exsiting now)

2. Stop redo apply on the physical standby Database

Executing the following statement in the sqlplus on the physical standby database:
Alter database recover managed standby database cancel;

3. Prepare the primary database to support a logical standby Database

3.1 prepare the primary database for role transitions

Adding the following parameters in the standby role initiallization settings for the primary database.

Log_archive_dest_3 = 'location =/u02/APP/Oracle/oradata/Beijing/arch2/valid_for = (standby_logfiles, standby_role) db_unique_name = Beijing'
Log_archive_dest_state_3 = Enable

Note: When the Chicago database is running in the primary role, the log_archive_dest_3 is ignored, and becoming valid only when cuug is running in the standby role. when the Chicago database is running in the logical standby role, archives redo data
Stored ed from the primary database to the local archived redo log files in/u02/APP/Oracle/oradata/cuug/ARCH /.

3.2 build a dictionary in the redo data
To build dictionary in the redo data, you must enable the logminer at first.
Issuing the following statement when the primary database is in the open state:

04:59:50 SQL> alter database add Supplemental log data;
If you do not run the command above to enable the logminer, the alter database recover to logical standby cuuo will never end up even if you run;
Execute dbms_logstdby.build; below successfully.

A logminer dictionary must be built into the redo data so that the logminer component of SQL apply can properly interpret changes it sees in the redo. To build the logminer dictionary, issue the following statement:
SQL> execute dbms_logstdby.build;
The dbms_logstdby.build procedure waits for all existing transactions to complete. long-running transactions executed on the primary database will affect the timeliness of this command.

The specified procedure uses flashback query to obtain a consistent snapshot of the data dictionary that is then logged in the redo stream. Oracle recommends setting the undo_retention initialization parameter to 3600 on both the primary and
Logical standby databases.

4. Transition to a logical standby Database
4.1 convert to a logical standby Database
Show parameter db_name ----- physical standby Database
Alter database recover to logical standby cuug;
Log_archive_dest_1 = 'location =/u02/APP/Oracle/oradata/cuuo/arch valid_for = (online_logfiles, all_roles) db_unique_name = cuuo'
Log_archive_dest_2 = 'service = cuug lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = cuug'
Log_archive_dest_3 = 'location =/u02/APP/Oracle/oradata/Wuhan/arch2 valid_for = (standby_logfiles, standby_role) db_unique_name = Wuhan'
Log_archive_dest_state_1 = Enable
Log_archive_dest_state_2 = Enable
Log_archive_dest_state_3 = Enable

5. Open the logical standby Database
The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.

To open the new logical standby database, you must open it with the resetlogs option by issuing the following statement:

SQL> alter database open resetlogs;

------------ Applying the redo data to the logical standby database -------------
Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new db_name initialization parameter.

Issue the following statement to begin applying redo data to the logical standby database. For example:

SQL> alter database start logical standby apply immediate;

6. Testing the configuration for the logical standby database.
6.1 switch to the primary database, and then issue the following:
23:38:02 sys @ cuug> select * from Scott. test;

Deptno dname Loc
-------------------------------------
10 Accounting New York
20 research Dallas
30 sales Chicago
40 operations Boston
50 development leader
50 test leader
60 adivisor leader

7 rows selected.

Elapsed: 00:00:00. 00
23:45:40 sys @ cuug> insert into Scott. Test values (70, 'marketing', 'Leader ');

1 row created.

Elapsed: 00:00:00. 01
23:46:40 sys @ cuug> com MIT;

Commit complete.

Elapsed: 00:00:00. 05

6.2 you have inserted a row in the Scott's table test, and now you must want to know whether the update of this table will be reflected in the same table on the standby database.
To do this, firstly you can check the standby client's Alert Log via tail command, and then run the following command for several times:

23:46:43 sys @ cuug> alter system switch logfile;

System altered.

Elapsed: 00:00:03. 26

If the alert log of the logical standby database prints the information as following, it means that the standby database is applying the redo data now, you can wait a moment, and then execute a select * from Scott. test in the sqlplus and check whether there
Is a new row, and congratuations to you if so.

Primary database is in maximum performance mode
RFS [2]: successfully opened standby log 4: '/u02/APP/Oracle/oradata/Wuhan/redo04.log'
Tue Jul 3 23:58:18 2012
Logminer: End mining logfile:/u02/APP/Oracle/oradata/Wuhan/redo05.log
Tue Jul 3 23:58:18 2012
Logminer: begin mining logfile:/u02/APP/Oracle/oradata/Wuhan/redo04.log
Tue Jul 3 23:58:31 2012
RFS [1]: Archived log: '/u01/APP/Oracle/product/10.2.0/db_1/dbs/arch1_94_787678850.log'
Tue Jul 3 23:58:31 2012

RFS logminer: Registered logfile [/u01/APP/Oracle/product/10.2.0/db_1/dbs/arch1_94_787678850.log] To logminer session ID [1]

For reprint, please indicate the source and original article links:

Http://blog.csdn.net/xiangsir/article/details/8572899