As we mentioned before, the default Database Guard mode for a logical standby database is set to ALL. Let's try to insert data into the HR.REGIONS table, which is out of the replication scope because of the skip rule we created.
- Connect the logical standby database with
SYSuser. Check the Database Guard mode andskiprules with the following query:SQL> SELECT GUARD_STATUS FROM V$DATABASE; GUARD_S ------- ALL SQL> SELECT OWNER, NAME,STATEMENT_OPT, PROC FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT <> 'INTERNAL SCHEMA'; OWNER NAME STATEMENT_OPT PROC -------- ------------ --------------- ------------------ DIRECTORY HR % DML TABLESPACE SYS.CHANGE_TS_DDLDatabase Guard mode is
ALLand allHRtables are skipped by SQL Apply. - Now connect with the
HRuser and insert a row to theREGIONStable:SQL> CONN HR/HR Connected. SQL> INSERT INTO HR.REGIONS VALUES (10,'TEST'), insert into hr.regions values (10,'test') * ERROR at line 1: ORA-16224: Database Guard is enabledIt's not possible to insert into a table, which is not part of the replication because the database guard mode is
ALL. - Let's change the mode to
STANDBYand try to insert in the table again using the following query:SQL> ALTER DATABASE GUARD STANDBY; Database altered. SQL> CONN HR/HR Connected. SQL> INSERT INTO HR.REGIONS VALUES (10,'TEST'), 1 row created.
We're now able to modify the tables with skip rules.
- Let's try to modify a table that is not skipped by SQL Apply:
SQL> CONN SCOTT/TIGER Connected. SQL> INSERT INTO DEPT VALUES (50,'TEST','TEST'), insert into dept values (50,'test','test') * ERROR at line 1: ORA-16224: Database Guard is enabled
We're now ready to change the logical standby database settings in order to let users modify non-replicated standby tables, all standby tables, or make the standby completely protected to user modification.
If only specific users need to modify standby tables, session-based disabling of database guard is more sensible.
If specific users on the logical standby need to modify tables and you do not want other users to have this opportunity, users can disable the Database Guard in their current sessions only and you can keep the logical standby on ALL or STANDBY mode. Execute the following statement to disable Database Guard for the current session:
SQL> ALTER SESSION DISABLE GUARD; Session altered.
The user must be granted the alter database privilege in order to disable Database Guard in its session.
Now set your Database Guard mode to NONE and try to insert into the table SCOTT.DEPT with the user Scott again. You should be able to modify all tables, which are also being modified with SQL Apply. Also, think about using the Database Guard mode as NONE. How could you control the accuracy of the data for the replicated tables when the users are free to modify them?
With a proper configuration, users are free to create database objects on the logical standby databases. However, we need to know some characteristics of the standby objects that are not handled by SQL Apply.
In order to create a standalone table on the logical standby database, the Database Guard mode must be STANDBY or NONE. One other way is to disable Database Guard for the current session and creating the table, which works even when the Database Guard is ALL.
On the other hand, if we somehow lose a table on the logical standby, which is inside the scope of replication and we want to create it again with the up-to-date data, it's possible to use the built-in DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure.
The logical standby database supports the replication of the jobs created with the DBMS_JOB package. These jobs will be created but will not run on the standby database. However, in case of failover or switchover, jobs will automatically start running on the new primary database.
The scheduler jobs created with DBMS_SCHEDULER are not replicated to the logical standby. However, in 11g there is a new attribute called database_role for this package, which makes scheduler jobs possible to be run on logical standby. By default, this attribute equals to the database_role value of the v$database view. You can create a job on the logical standby database and if you don't specify the value for the database_role attribute, the job will be run as long as the database role is logical standby.
Again if you don't specify the value for the database_role attribute, the scheduler jobs created on the primary database will be run on the database as long as the role is primary and will not be replicated to logical standby. If you want to keep the job running after a switchover or failover on the new primary, you must create the same scheduler job on the logical standby with the database_role attribute as Primary.
If you plan to create a scheduler job on the logical standby database with database_role Standby, you should also create one in the primary database with database_role Standby. So that, when a switchover is performed, the job will still be running on the new standby.
When we create a logical standby database, all materialized views and materialized view logs on the primary database also exist on the logical standby. However, SQL Apply skips DDL statements related to materialized views and logs. So the newly created, altered or dropped materialized views and logs on the primary database will not be handled on the logical standby.
If we need to have any materialized view (existing on the primary or not) on the logical standby we are able to create it. The MVs created on the standby can be refreshed using a fast, complete, or forced refresh. Refreshes may be on-commit, which will be triggered by SQL Apply or on-demand with scheduling or manual execution.