Logical standby creation steps

To establish logical standby, you must first establish physical standby. The process of establishing physical standby is omitted. The main process of the logical DB at www.2cto.com is reader (read redo entries) ---> prepare (generate LCR) ---> builder (LCR packaging for the same transaction) ---> analyzer (analyzing dependencies between transactions) ---> coordinator Coordination ---> apply (applying SQL) 1: check whether the primary data contains unsupported objects. You can query the data dictionary table DBA_LOGSTDBY_UNSUPPORTED: SQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER, TABLE_NAME; www.2cto.com II: create an alternate tablespace in the primary database. use DBMS_LOGMNR_D.SET_TABLESPACE to put some tables into this table empty. Middle. some tables in the logical standby database belong to SYS and SYSTEM users, and these are stored in the tablespace. these tables may record a sharp increase after a period of time. to prevent the SYSTEM tablespace from being used up and cause the database to go DOWN, create the alternate tablespace to store these tables. alter system set undo_retention = 1800 scope = both; create tablespace logmnrts DATAFILE '/oradata/ORCL/logmnrts/logmnrts01.dbf' SIZE 25 m autoextend on maxsize 8000 M; SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('logmninsts'); (EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('logmninsts') in the preceding step; you can perform corresponding maintenance in future.) 3: In order Stdandby redo must be created for real-time application on the standby end. When adding additional logs to the master database, a large redo should be generated. Therefore, the redo log should be larger on the standby end, add standby redo logs. When adding additional logs to the master database, a large redo log should be generated. Therefore, it is recommended that the redo log be larger than shutdown immediate; startup nomount; alter database mount standby database; alter database add standby logfile group 4'/opt/oradata/ora1/standbyredo04.log 'size 100 m; alter database add standby logfile group 5'/opt/oradata/ora1/standbyredo05.log' size 100 m; al Ter database add standby logfile group 6'/opt/oradata/ora1/standbyredo06.log 'size 100 m; alter database add standby logfile group 7'/opt/oradata/ora1/standbyredo07.log 'size 100 m; primary also needs to change redo log to 100 m. Here, it is omitted www.2cto.com 4: verify whether the table has no primary key select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) and bad_column = 'Y'; Verify the additional LOG: SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG, SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG from v $ DATABASE; PK _ UI _ --- no alter database add supplemental log data (primary key, unique index) COLUMNS; after the supplemental logging attribute is activated, perform a log switch to ensure that the current log does not contain non-append log data ). In addition, if you have created a physical standby database before activating the log supplement process on the primary database, you must perform the same operation on all the physical standby databases. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; then verify SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG, SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG FROM V $ DATABASE; PK_LOG UI_LOG ------ YES 5: add some parameters for primary database *. db_name = 'orcl '*. db_unique_name = 'Primary '*. log_archive_config = 'dg _ CONFIG = (primary, phystandby, logstandby )'*. log_archive_dest_1 = 'location =/oradata/arch valid_for = (all_logfiles, all_roles) db_unique_name = primary '*. log_archive_dest_2 = 'service = phystandby lgwr sync affrem valid_for = (online_logfiles, primary_role) db_unique_name = phystandby '*. log_archive_dest_3 = 'service = logstandby lgwr sync affrem valid_for = (online_logfiles, primary_role) db_unique_name = logstandby '*. log_archive_dest_state_1 = 'enable '*. log_archive_dest_state_2 = 'enable '*. log_archive_dest_state_3 = 'enable '*. log_archive_format = '% t _ % s _ % r _ % sarch. dbf '*. undo_retention = 3600 Note: db_unique_name is the db_unique_name parameter file to add some parameters for standby database *. db_name = 'orcl '*. db_unique_name = 'logstandby '*. fal_client = 'logstandby '*. fal_server = 'Primary '*. log_archive_config = 'dg _ CONFIG = (primary, phystandby, logstandby )'*. log_archive_dest_state_1 = 'enable '*. log_archive_dest_state_2 = 'enable '*. LOG_ARCHIVE_DEST_1 = 'location =/oradata/ORCL/arch VALID_FOR = (ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = logstandby '*. LOG_ARCHIVE_DEST_2 = 'location =/oradata/ORCL/standbyarch VALID_FOR = (STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME = logstandby '*. log_archive_format = '% t _ % s _ % r _ % sarch. dbf '*. undo_retention = 3600 after spfile is generated, use spfile to start standby. When it is converted to logical standby, change www.2cto.com 6 of db_name in spfile: alter database recover managed standby database cancel on the standby end; 7. Create a logminer dictionary exec dbms_logstdby.build on the primary database. archive the current log of the master database to ensure that the archive containing the data dictionary information is passed to the backup database alter system archive log current; 8: on the standby end, alter database recover to logical standby newdb_name; If spfile is used, db_name in spfile is automatically changed. Remember to generate a new password file to back up alert_SID.log 9 of the slave database: shutdown immediate; startup mount; alter database open resetlogs on the standby end; 10: Apply redo on the standby end. If the standby redo log file is not used before, here, an error occurs: alter database start logical standby apply immediate; 11: optimize the logical standby parameter alter database stop logical standby apply; execute dbms_logstdby.apply_set ('Prepare _ servers', 2 ); execute slave ('apply _ servers', 8); alter database start logical standby APPLY immediate; alter database stop logical standby apply; EXECUTE dbms_logstdby.apply_set ('max _ SGA ', 1000 ); alter database start logical standby apply immediate;