Hi ,
Prior to creating the Logical Standby please ensure the following:
1) Determine if the primary database contains datatypes or tables that are not
supported by a logical standby database.
SQL> select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;
to view the column names and datatypes for one of the tables listed in previous query.
SQL> select column_name,data_type from dba_logstdby_unsupported where owner=’
2) Ensure that table rows in the primary database can be uniquely identified.
to find the tables which do not have unique key use the following query.
SQL> select owner,table_name,bad_column from dba_logstdby_not_unique where table_name not in ( select table_name from dba_logstdby_unsupported);
3) Ensure that the primary database is in ARCHIVELOG mode and that archiving is enabled.
4. Ensure supplemental logging is enabled on the primary database. To see if supplemental logging is enabled, start a SQL session and query the
V$DATABASE fixed view.
For example, enter:
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
— —
YES YES
If supplemental logging is not enabled, execute the following statements:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;
5. Ensure LOG_PARALLELISM init.ora parameter is set to 1 (default value).
Hi,
1. Verify that the primary database is setup to remotely archive by checking the parameter log_archive_dest_2=’SERVICE=tnsname alias lgwr’;
2. Verify that remote archiving to the logical standby is successful.
Use the following query to determine the error
SQL> SELECT DEST_ID “ID”,STATUS “DB_status”,DESTINATION “Archive_dest”,ERROR “Error” FROM V$ARCHIVE_DEST;
When there is no error output is as shown
ID DB_STATUS ARCHIVE_DEST ERROR
————————————————————————-
1 VALID LOCATION=c:\oracle\archive
2 VALID SERVICE=stby lgwr
3. Verify that archive logs are being created in the standby_archive_dest.
SQL> show parameter standby_archive_dest;
4. Verify that log apply services on the standby are currently running.
SQL> SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY;
When querying the V$LOGSTDBY view, pay special attention to the HIGH_SCN
column. This is an activity indicator. As long as it is changing each time
you query the V$LOGSTDBY view, progress is being made. The STATUS column
gives a text description of the current activity.
If the query against V$LOGSTDBY returns no rows then logical apply is not running. Start logical apply by issuing the following statement:
SQL> alter database start logical standby apply;
If the query against V$LOGSTDBY continues to return no rows then proceed to step 5.
5. To determine if there is an archive gap, query the DBA_LOGSTDBY_LOG view on the logical standby database.
SQL> SELECT SUBSTR(FILE_NAME,1,100) FILE_NAME, SUBSTR(SEQUENCE#,1,10) “SEQ#”,
FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, ‘HH:MI:SS’) TIMESTAMP,
DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) “THR#”FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
Copy the missing logs to the logical standby system and register them using
the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on your logical standby
database. For example:
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE ‘/u01/oradata/arch/1_57.arc’;
6. Determine is logical apply is receiving errors while performing apply operations.
SQL> SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE FROM DBA_LOGSTDBY_EVENTS WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
7. Query DBA_LOGSTDBY_PROGRESS to verify that log apply services is progressing.
The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply operations on the logical standby databases. For example:
SQL> SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, NEWEST_SCN, NEWEST_TIME FROM DBA_LOGSTDBY_PROGRESS;
8. Verify that the table that is not receiving rows is not listed in the DBA_LOGSTDBY_UNSUPPORTED.
