Logical Standby configuration:
The following data types are not supported:
NClob
Long
Long Raw
Bfile
RowID
UroWID
Check the table not supported:
SQL> Select Distinct Owner, Table_Name from DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER, TABLE_NAME
The following operations are not supported:
Alter Database
Alter session
ALTER SNAPSHOT
Alter Snapshot log
Alter System Switch log
Create Control File
Create Database
Create Database Link
Create Pfile from SPFILE
Create Schema Authorization
Create snapshot
Create Snapshot log
Create SPFile from Pfile
Create Table As SELECT FROM A Cluster Table
DROP DATABASE LINK
Drop snapshot
Drop snapshot log
EXPLAIN
Lock Table
Rename
SET Constraints
Set role
SET Transaction
Advanced queue management and materialization view refresh is not supported (DBMS_AQADM, DBMS_MVIEW_REFRESH)
Logical Standby requires each table to have Primary Key or UNIQUE INDEX, and if there is a form that must be labeled, the Supplemental Logging property of the primary library can be activated, but this will record each record in the table in the Redo log. All fields are marked, greatly increase the amount of records of Redo log.
Check that there is no uniqueness indicator:
SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
If you can confirm that there is a uniquely labeled field in the table, you don't want to create a primary key or an index to increase the update overhead, then create a DISABLED RELY type of constraint, such as:
SQL> ALTER TABLE MyTab Add Primary Key (ID, Name) Rely Disable;
Check if the supplemental logging has been activated.
SQL> SELECT Supplemental_Log_Data_PK, Supplemental_Log_Data_UI from V $ Database;
Use the following method to activate the Supplemental Logging property of the Primary library:
SQL> ALTER DATABASE ADD Supplemental Log Data (Primary Key, UNIQUE INDEX) Column
After activating the Supplemental Logging property, make a log switching to ensure that the current log does not contain non-additional log data (Nonsupplemental Log).
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Create a new table space for logminer, otherwise the object that Logical Standby needs to create the default in the System tablespace.
SQL> CREATE TABLESPACE LOGMNRTS DATAFILE '/DISK1/Oracle/dbs/logmnrts.dbf' Size25M
AutoExtend on maxsize unlimited
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('Logmnrts');
Close primary, back up all data files, pass to Standby
Start Primary, create a data dictionary for Logical Standby Database
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
Check that you need to upload archive log information on Standby
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT NAME FROM V $ Archived_log where (Sequence Max (Sequence Max (Sequence #) from V $ archived_log where dictionary_begin = 'yes' and standby_dest = 'no'));
Name
-------------------------------------------------- ------------------------------
/opt/oracle/oradata/orcl/archive/1_29.dbf
Modify TNSNames.ora and Listener.ora and Listener.ora on the primary and standby, the configuration method is the same as Physical Standby.
Modify the fal_server and fal_client values in the initialization parameters on Standby.
Set the Standby database
SQL> Startup Mount;
SQL> ALTER DATABASE Guard ALL;
SQL> ALTABASE register logical logfile '/opt/oracle/roadata/orcl/archive/1_29.dbf';
SQL> ALTABASE Start Logical Standby Apply Initial;
The initial starting Standby requires Apply Initial, and then run the following SQL to start and turn off the Standby log application.
SQL> ALTER DATABASE STOP Logical Standby Apply;
SQL> ALTER DATABASE Start Logical Standby Apply;
Test if the entire environment is successful in the primary log archive.
Check the log application of Standby:
SQL> SELECT SEQUENCE #, First_Time, Next_Time, Dict_Begin, DICT_END from DBA_LOGSTDBY_LOG ORDER BY Sequence #;
