Build a logical standby Database

1. Build a physical standby Database

2. Stop redo applications on the physical standby database. If the standby database is RAC, all instances other than the following statements must be stopped.

SQL> alter database recover managed standby database cancel;

 

Database altered.

3. Prepare the master database for role conversion

This step is only useful when performing switchover.

Modify initialization parameters:

Alter system set log_archive_dest_1 = 'location =/oradata/ARCH/valid_for = (online_logfiles, all_roles) db_unique_name = primary 'scope = spfile;

Alter system set log_archive_dest_3 = 'location =/oradata/logiclarch/valid_for = (standby_logfiles, standby_role) db_unique_name = primary' scope = spfile;

Alter system set log_archive_dest_state_3 = Enable;

 

Parameter Introduction

	The primary library runs in the master role	The primary database runs in the logical standby role.
Log_archive_dest_1	Path for archiving online log files of the master database	Path for archiving online logs of logical standby Databases
Log_archive_dest_3	Does not work	Archive the redo received from the master database to the local path

4. Create a logminer dictionary in the redo data

SQL> execute dbms_logstdby.build;

 

PL/SQL procedure successfully completed.

 

Note: After 11g R2, logs will be automatically enabled in the physical standby database. If the database version is earlier than 11.2, or the previous version is upgraded to 11.2, run the following statement to enable the Supplemental log

SQL> alter database add Supplemental log data (primary key, unique index) columns;

 

SQL> select supplemental_log_data_pk, supplemental_log_data_ui from V $ database;

 

Sup

------

Yes

 

If the parameter is not enabled, the DG becomes invalid after switchover or failover occurs. You need to reconfigure the logical standby database.

5. Convert the physical standby database to the logical standby Database

1). Single Instance

SQL> alter database recover to logical standby test;

 

Database altered.

Db_name specifies a name different from the master database to identify the new logical standby database.

2). RAC

Stop any instance except one instance, modify cluster_database to false, start the slave database to mount in exclusive mode, and then run the statement to switch to the logical slave database.

SQL> alter system set cluster_database = false scope = spfile;

SQL> shutdown abort;

SQL> startup Mount exclusive;

 

Note: During rolling upgrade, you need to switch to the logical standby database in keep identity mode. database version requirements: After 11.1

SQL> alter database recover to logical standby keep identity;

 

When the switch logical standby statement is executed, the statement waits for the application to redo the data until the logminer dictionary is found in the log file,

If logminer is not successfully executed in the master database, the statement waits until another session is started. Run the following SQL command to cancel switching the logical standby statement.

SQL> alter database recover mananged standby database cancel

 

Because db_name has changed, you need to re-create the password file before opening logical standby.

$ CD $ ORACLE_HOME/dbs

$ Orapwd file = orapwtest Password = Oracle

6. Adjust the initialization parameters of the logical standby Database

RAC environment: Set cluster_database to true

SQL> alter system set cluster_database = true scope = spfile;

 

Close the logical standby database, start to the Mount stage, and modify parameters

Alter system set log_archive_dest_1 = 'location =/oradata/ARCH/valid_for = (online_logfiles, all_roles) db_unique_name = standby 'scope = spfile;

Alter system set log_archive_dest_2 = 'service = primary async valid_for = (online_logfiles, primary_role) db_unique_name = primary' scope = spfile;

Alter system set log_archive_dest_3 = 'location =/oradata/logicalarch/valid_for = (standby_logfiles, standby_role) db_unique_name = standby' scope = spfile;

Alter system set log_archive_dest_state_1 = Enable;

Alter system set log_archive_dest_state_2 = Enable;

Alter system set log_archive_dest_state_3 = Enable;

 

Add standby logfile to the slave Database

SQL> alter database add standby logfile group 8'/oradata/orcl/standby08.log 'size 50 m, group 9'/oradata/orcl/standby09.log' size 50 m, group 10'/oradata/orcl/standby10.log 'size 50 m;

 

SQL> select group #, thread #, Bytes/1024/1024 m from V $ standby_log;

 

Group # thread # m

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

8 1 50

9 1 50

10 1 50

 

Restart the slave database to make the parameters take effect

 

Parameter Introduction

	When the standby database runs in the master role	When the standby database runs in the logical standby role
Log_archive_dest_1	Archive online logs of the master database to the local path	Archive online logs of the logical standby database to the local path
Log_archive_dest_2	Transmit redo to the remote logical standby database primary	Does not work
Log_archive_dest_3	Does not work	Archive the redo received from the master database to the local path

 

7. Open the logical standby Database

SQL> alter database open resetlogs;

 

Database altered.

 

8. Apply SQL statements to the logical standby Database

SQL> alter database start logical standby apply immediate;

 

Database altered.

 

NOTE: For the same host, run the following statement:

SQL> execute dbms_logstdby.skip ('alter tablespace ');

9. Check whether the logical standby database is available

Query on the slave database. The current applied_scn and newest_scn are consistent, and the current master database does not run transactions.

SQL> select applied_scn, newest_scn from dba_logstdby_progress;

 

Applied_scn newest_scn

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

379385 379385

Switch the log file of the master database

SQL> alter system switch logfile;

 

System altered.

View applied_scn and newest_scn in the slave Database

Log application:

SQL> select applied_scn, newest_scn from dba_logstdby_progress;

 

Applied_scn newest_scn

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

382578 382581

 

If a new transaction persists in the master database, applied_scn will catch up with newest_scn. When no transaction exists in the master database, applied_scn will evaluate newest_scn, And the slave database will finish applying the redo data of the master database.

 

 

View Master/Slave database status

As you can see, the dbid of the logical standby database is different from that of the primary database. The logical standby database can be opened to the read/write mode.

Master database:

SQL> select name, dbid, database_role, open_mode from V $ database;

 

Name dbid database_role open_mode

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

Orcl 1387027022 primary read write

Slave database:

SQL> select name, dbid, database_role, open_mode from V $ database;

 

Name dbid database_role open_mode

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

Test 2158270330 logical standby read write

 

Start and Stop the SQL application of the logical standby Database

Start:

SQL> alter database start logical standby apply immediate;

 

Database altered.

Stop:

SQL> alter database stop logical standby apply immediate;

 

Database altered.

 

Build a logical standby Database