Prior to that, we need to create the Physical Standby.
We need to perform following steps before creating Logical Standby Database :
1. Primary Database must be in Archive log mode.
Check your database is in archive log mode or not. If not enable it for archive log.
SQL> SELECT NAME,OPEN_MODE,LOG_MODE,DATABASE_ROLE FROM V$DATABASE; NAME OPEN_MODE LOG_MODE DATABASE_ROLE --------- -------------------- ------------ ---------------- MGR READ WRITE ARCHIVELOG PRIMARY
2. Unsupported Objects :
We should be aware of non-supported objects in the Logical Standby database.
Following are the list of Unsupported objects in the Logical Standby database.
- Tables and sequences in SYS schema.
- Tables used to support materialized view.
- Global temporary tables.
- Tables with unsupported data types.
If your primary database contains any of the above objects then log apply service automatically excludes these objects while applying redo to the logical standby database.
Now, See few examples of unsupported data types :
- BFILE, ROWID, and UROWID
- User-defined types
- Multimedia data types (Spatial, Image and Oracle Text)
We can view unsupported tables from dba_logstdby_unsupported_tables.
SQL> select * from dba_logstdby_unsupported_table; OWNER TABLE_NAME ------------------------------ ------------------------------ SCOTT ON1XY
A result of above query contains the list of tables which are not supported by log apply process.
To view the list of unsupported data types, we can query dba_logstdby_unsupported table.
SQL> select table_name,column_name,attributes,data_type from dba_logstdby_unsupported; TABLE_NAME COLUMN_NAME ATTRIBUTES DATA_TYPE ------------------------------ ------------------------------ --------------------------------------- -------------------------------- ON1XY PATH BFILE
It contains the list of data types with object name which is unsupported in a logical standby environment.
3. Unsupported DDL commands :
We need to be aware of DDL command that does not execute on a logical standby database.
Below is the list of few examples of DDL commands that do not execute on Logical Standby Database with log apply process :
• ALTER DATABASE • ALTER SESSION • ALTER MATERIALIZED VIEW • ALTER MATERIALIZED VIEW LOG • CREATE DATABASE • CREATE DATABASE LINK • DROP DATABASE LINK • DROP MATERIALIZED VIEW • DROP MATERIALIZED VIEW LOG • EXPLAIN • LOCK TABLE • SET CONSTRAINTS
4. Unsupported PLSQL Supplied Packages :
PLSQL supplied packages which modifies metadata are not supported by SQL apply process. Oracle PLSQL supplied packages which do not modify system metadata but may modify user data are supported by SQL apply process if they do not contain any unsupported data types listed in the 3rd section.For eg: DBMS_LOB, DBMS_SQL
Below is List of few examples of Unsupported PL/SQL supplied packages include :
• DBMS_JAVA • DBMS_REGISTRY • DBMS_ALERT
4: Ensuring Unique Row Identifiers :
Primary database and standby database rowid might not be the same so we need to apply the different mechanism to ensure efficient SQL apply process for data updates to the logical standby database.
And Primary key or Unique key works efficiently with this.
We can query a dba_logstdby_not_unique table to check tables that do not contain any primary or unique key.
SQL> SELECT * FROM dba_logstdby_not_unique 2 ; OWNER TABLE_NAME B ------------------------------ ------------------------------ - SCOTT BONUS N SCOTT SALGRADE N HR DEMO N
Now we are all set to create Logical Standby Database. In next article, we will see a creation of Logical Standby Database.
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
