Configure Oracle 11g Data guard (logical standby) based on the same host)

The Oracle Data guard logical standby database uses a backup of the master database to first create a physical standby database and then convert it to a logical standby database. After that, the master database transmits the logs to the slave database. The slave database uses logminer to parse the SQL statements executed by the master database from the logs of the master database and re-execute them on the slave database, this ensures that the data in the master database is logically consistent with that in the master database. In contrast to the physical standby database, the physical standby database uses redo apply and the logical standby database uses SQL apply. Therefore, the logical standby database only ensures that the data is logically consistent with the master database, so that the logical standby database can be in the open state and perform corresponding DML operations. This article describes the precautions for creating a logical standby database and how to create a logical standby database.

References:
Important configuration parameters of Oracle Data guard
Configure Oracle 11g Data guard based on the same host

 

1. Restrictions on logical standby Databases
There are many restrictions on the logical standby database. For example, some special data types such as object, nested table, rowid, object type, and custom data type are not supported, and
It supports segment compression and does not support a lot of things such as some specific DDL statements. For more information, see Oracle Data guard concepts and administration. However
The backup database still has many features that are not available in the physical backup database. The following lists the important information of the logical standby database.

A. Determine the Unsupported schema
-- Related schemas of Oracle databases are skipped. Therefore, do not create objects or test Objects Based On These schemas. You can use the following query to view them.
SQL> select owner from dba_logstdby_skip where statement_opt = 'internal schema ';

B. Determine unsupported data objects and Data Types
-- Use the following query to find unsupported tables
SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner, table_name;

-- The preceding query results can be further refined to find out why the table is not supported. The following query fails because the table cannot be logically standby due to the use of object, nested table, and other types.
SQL> select column_name, data_type from dba_logstdby_unsupported where owner = 'pm 'and table_name = 'print _ media ';

Column_name data_type
-------------------------------------------------------------------------
Ad_textdocs_ntab nested table
Ad_graphic bfile
Ad_header object

C. identify objects with uniqueness issues
Because logical standby is the same as the original database, the rowid on logical standby is not the same as the rowid on the master database. For more information about rowid, see Oracle rowid.
For the update and delete operations on the master database, Oracle uses the primary key and unique index/supplement logs to ensure that the objects operated by the master database and the slave database are the same records on the same object.
How can I identify the updated row in each update statement when the primary key and unique index are enabled and logs are supplemented? In the following scenario, when writing redo statements, the unique column value information will be appended.
If the table has a primary key, the primary key value is used as part of the update statement along with the updated column.
If a table has no primary key and has a unique index/constraint that is not empty, the minimum non-null unique index/constraint will be updated along with the column as part of the update statement.
The table has no primary key and no unique index/constraint. All columns with a fixed length (except long, lob, long raw, object type, and collection type columns) and the columns to be updated are part of the update statement.
Note: A table with a unique function index can be implemented with SQL apply. As long as the modified row can be uniquely identified, the index function cannot be used as a unique function to identify the updated row.
For scenarios where the application can ensure that the Row Records on the table are unique and do not want to create a primary key, you can create a rely constraint to avoid additional overhead caused by maintaining the primary key.
-- You can add a rely constraint to the table in the following way:
SQL> ALTER TABLE tb_name add primary key (ID, name) rely disable;

-- Data Dictionary dba_logstdby_not_unique records tables that do not have primary keys and unique indexes, or tables that do not have enough information can ensure that the master database and logical standby lock the same object.
SQL> select owner, table_name from dba_logstdby_not_unique
2 Where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) and bad_column = 'y ';

-- Check whether the Supplemental log is enabled for the master database. It is enabled immediately after dbms_logstdby.build is executed in the master database.
SQL> select supplemental_log_data_pk, supplemental_log_data_ui from V $ database;

 

2. Several important processes of the logical standby Database
The logical standby database requires a series of processes to capture logs and apply logs. It consists of two components: the mining engine and the application engine. That is, a log or backup day
Extract the SQL statement set, and apply the SQL statement set to the logical standby database. The related processes of these two engines can be queried in the V $ logstdby_process view to obtain related information.
Mining Engine process:
Reader: redo record)
Preparer: the process is responsible for converting the redo records parsed by the reader process to LCR (logical change record)
There can be multiple preparer processes. The parsed LCR is stored in an area named LCR cache in the shared pool.
Builder: The process packs the LCR into a transaction, combines multiple LCR into a single LCR, and manages the LCR cache. Such as memory paging and log mining checkpoints.

Application Engine process:
Analyzer: This process is responsible for checking the transaction segments contained in a group of LCR, filtering out transactions that do not need to be applied, and checking the dependencies of different transactions.
Coordinator: The process assigns a transaction to the applier process to monitor the transaction dependency and coordinate the submission order.
Applier: there can be multiple processes, which are responsible for applying LCR to the standby database.

 

3. Create a logical standby Database

A. There are many ways to create a physical standby database first. For Oracle 11g, you can create a physical standby database directly from the active database, it can also be created using the duplicate Method Based on 10 Gb RMAN. The creation of the physical standby database is not demonstrated here. You can refer to: Configure Oracle 11g Data guard role based on the same host to verify the master database and physical standby database-master database: cnbo, slave Database: hkbo-Information on the master database cnbo> select name, database_role, switchover_status from V $ database; Name database_role switchover_status certificate -------------- revoke cnbo primary to standby -- Information on the slave database hkbo> select name, open_mode, database_role, protection_mode from V $ da Tabase; Name open_mode database_role protection_mode --------- certificate ---------------- when hkbo mounted physical standby maximum performance -- SRL is applied hkbo> select sequence #, first_time, next_time, applied from V $ archived_log where rownum <3 order by first_time DESC; Sequence # first_time next_time applied -------------------------------------------------------- ------------------- 7 2013/08/16 10:38:03 2013/08/16 10:46:11 Yes 6 2013/08/16 10:38:00 2013/08/16 10:38:03 Yes C. to switch the physical standby database to the logical standby database, we need to build a logminer dictionary in the primary database and enable supplemental logs. Therefore, we should stop the MRP process of the logical standby database first to avoid generating additional redo apply. If we are using the broker to manage the existing physical standby database, disable the target database in the broker first. Hkbo> alter database recover managed standby database cancel; D. Modify the log_archive_dest_n parameter of the master database. In the future, you need to convert the logical standby database to the master database, but the master database to the logical standby database, we recommend that you first modify the log_archive_dest_n parameter. 1. Change the valid_for attribute in the log_archive_dest_1 parameter of the master database to valid only online redo logs, excluding the backup redo log, the second is to add a new archive path for the backup redo log. That is to say, the online log is separated from the backup log. Modify the following example, in this example, we have not modified log_archive_dest_1 = -- master database: cnbo slave Database: hkbo 'location = canonical -- When cnbo is the master database, it is used to store arch valid_for = (online_logfiles, all_rol Es) -- when the cnbo is switched to the standby database role, it is used to store the archive db_unique_name = cnbo 'generated by itself as the standby database; log_archive_dest_3 = -- this archive path is used for backup, only when the master database cnbo is converted to the slave database is valid 'location = use_db_recovery_file_dest -- when the cnbo is the slave database role, it is used to store standby_logfiles valid_for = (standby_logfiles, standby_role) received from) db_unique_name = cnbo '; log_archive_dest_state_3 = Enable D; Create logminer data dictionary cnbo> exec dbms_logstdby.build on the master database; e. Convert the physical standby database to the logical standby database hkbo> show parameter db_name name type value ----- Revoke privileges ------------------------------ db_name string cnbo hkbo> alter database recover to logical standby hkbo; -- if you are using a pfile file, the ORA-16254 will be prompted that db_name hkbo> shutdown abort; hkbo> startup Mount; --> restart the slave database because the logical slave database name, including dbid and incarnation, has been reinitialized. hkbo> select name, database_role from V $ database; name database_role ------------------------ --- ---------------------------------------------- Hkbo logical standby -- Author: Robinson Cheng -- Blog: recreate the backup Database Password File in Oracle 11g is no longer necessary. It is required in Oracle 10 Gb, And the password must be the same as that in the master database during reconstruction. The parameter log_archive_dest_n in the slave database is different from that in the physical standby database, after the logical standby database is opened, it will generate its own redo log. Therefore, we need to configure the parameter log_archive_dest_n. For the logical standby database, there are three types of log files: Online redo log, archived redo log, and standby redolog. Hkbo> select name, value from V $ parameter where name in ('Log _ archive_dest_1 ', 'Log _ archive_dest_2 '); NAME value specified parameter location = mongodb_unique_name = hkbo valid _ for = (all_logfiles, all_roles) using service = cnbo async db_unique_name = cnbo valid_for = (online_log files, primary_roles) hkbo> Ho ls/u02/database/hkbo/fr_area/hkbo --> This is the folder archivelog onlinelog hkbo> alter system set log_archive_dest_1 = 'location = Limit 2 valid_for = (online_logfiles, all_roles) db_unique_name = hkbo '; hkbo> alter system set log_archive_dest_3 = 'location = 1_2 valid_for = (standby_logfiles, standby_role) db_unique_name = hkbo'; hkbo> select name, value from V $ parameter where name in ('Log _ archive_dest_1 ', 2 'Log _ archive_dest_2', 'Log _ archive_dest_3 '); NAME value description ------------------------- ------------------------------------------- log_archive_dest_1 location = use_db_recovery_file_dest -- the arch valid_for = (online_logfiles, all_roles) generated by the slave database when hkbo is used as the master database, arch db_unique_name = hkbo log_archive_dest_2 service = cnbo async db_unique_name = cnbo -- When hkbo is a slave database, valid_for = (online_logfiles, primary_roles) is ignored) -- When hkbo is the master database, send redo data to the slave database cnbo log_archive_dest_3 location = standby -- When hkbo is the slave database, archive the standby log valid_for = (standby_logfiles received from the master database, standby_role) -- When hkbo is converted to the master database, this parameter is ignored db_unique_name = hkbo -- it is recommended to add the archive process hkbo> alter system set log_archive_max_processes = 9 for the master and slave databases; cnbo> alter system set log_archive_max_processes = 9; H. Open the logical standby database -- use resetlog to open the database hkbo> alter database open resetlogs; -- for the logical standby database on the same host, before enabling SQL apply for the first time, you need to perform the following operations so that SQL skips any alter tablespace DDL statements-whether this step is required, from Oracle Data guard concepts and administration, we understand that we want to execute hkbo> exec dbms_logstdby.skip ('alter tablespace'); I. enable SQL apply hkbo> alter database start logical standby apply immediate; hkbo> Ho ls/u02/database/hkbo/fr_area/hkbo -- an extra foreign_archivelog folder is added to store the logs from the master database archivelog foreign_archivelog onlinelogj. Check Result cnbo> create user Robin identified 2 default tablespace users; cnbo> grant DBA to Robin; cnbo> conn Robin/xxx; cnbo> Create Table T (what varchar (20), DT varchar (20 )); cnbo> insert into T select 'logicalstdby', to_char (sysdate, 'yyyymmdd hh24: MI: ss') from dual; cnbo> commit; cnbo> alter system switch logfile; hkbo> select * from Robin. t; -- verify what DT ---------------------- ------------------------------------- logicalstdby 20130820 17:33:19

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)