Now let's try to create some objects on the logical standby database. First we will create a test table with the HR user. The Database Guard mode is ALL, which is the default.
- Connect the logical standby database with
SYSuser and execute the following query:SQL> SELECT GUARD_STATUS FROM V$DATABASE; GUARD_S ------- ALL SQL> CONN SCOTT/TIGER Connected. SQL> CREATE TABLE TEST (A NUMBER); create table test (a number) * ERROR at line 1: ORA-01031: insufficient privileges
The error message specifies a privilege problem but this is not due to the lack of
create tableprivilege for theHRuser. We receive this error because the Database Guard mode does not allow for creation of the table. Let's change it and try again. - Connect with the
SYSuser using the following query:SQL> ALTER DATABASE GUARD STANDBY; Database altered. SQL> CONN SCOTT/TIGER Connected. SQL> CREATE TABLE TEST (A NUMBER); Table created. SQL> INSERT INTO TEST VALUES (1); 1 row created. SQL> COMMIT; Commit complete.
We're able to create a table when the Database Guard mode is
STANDBYorNONE. What about an index? There is no doubt that we can create an index for the test table, which is a standalone standby object not maintained by SQL Apply. - Let's try to create an index on a table that is being replicated.
SQL> CONN SCOTT/TIGER Connected. SQL> CREATE INDEX TESTIDX ON DEPT (LOC); create index testidx on dept (loc) * ERROR at line 1: ORA-16224: Database Guard is enabledThe Database Guard mode is
STANDBYand we are not able to create an index on a standby table handled by SQL Apply. - We should disable the Database Guard in session and try again. In order to disable Database Guard, the user needs the
Alter Databaseprivilege as shown in the following query:SQL> GRANT ALTER DATABASE TO SCOTT; Grant succeeded. SQL> CONN SCOTT/TIGER Connected. SQL> ALTER SESSION DISABLE GUARD; Session altered. SQL> CREATE INDEX TESTIDX ON DEPT (LOC); Index created.
If an index is being created on a table that is handled by SQL Apply, we need to disable Database Guard for that session.
- Let's try if the same applies to the materialized views. Suppose a materialized view for a query on the
EMPandDEPTtables of the userSCOTTwas created on the primary database. As MV DDLs are not replicated with SQL Apply and we need the MV on the standby, we need to create it in the physical standby database. Let's create the MV using the following query:SQL> CONN SCOTT/TIGER Connected. SQL> CREATE MATERIALIZED VIEW SCOTT.EMPDEPT REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT E.ENAME, D.DNAME FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO; Materialized view created.
We are able to create a materialized view without disabling Database Guard for that session.
- Now we will create a scheduler job to refresh the MV periodically on the logical standby databse:
SQL> GRANT CREATE JOB TO SCOTT; GRANT SUCCEEDED. SQL> CONN SCOTT/TIGER CONNECTED. SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB ( 3 JOB_NAME => 'REFRESH_EMPDEPT_MV' , 4 JOB_TYPE => 'PLSQL_BLOCK', 5 JOB_ACTION => 'BEGIN DBMS_MVIEW.REFRESH (LIST =>''SCOTT.EMPDEPT'', METHOD => ''C''), END; ', 6 START_DATE => SYSDATE, 7 REPEAT_INTERVAL => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=0', 8 END_DATE => NULL, 9 ENABLED => TRUE, 10 END; 11 / PL/SQL procedure successfully completed.
We didn't specify a value for the
DATABASE_ROLEattribute, so it will have the default, which is the current role of the database,STANDBY. This job will run as long as this database role is logical standby.We assume that MV exists on primary and a scheduler job is also running for the refresh of the MV on the primary database (with the
DATABASE_ROLEattribute ofPRIMARY). We also created the MV and a job for its refresh on the logical standby now. But what happens if we perform a switchover? Both scheduler jobs on the primary and standby will not run because of theirDATABASE_ROLEattribute. So let's create one more scheduler job on standby and primary to be ready for switchover and failover. - On the standby database, enter the following set of statements:
SQL> CONN SCOTT/TIGER CONNECTED. SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB ( 3 JOB_NAME => 'REFRESH_EMPDEPT_MV_PRIMARY', 4 JOB_TYPE => 'PLSQL_BLOCK', 5 JOB_ACTION => 'BEGIN DBMS_MVIEW.REFRESH (LIST =>''SCOTT.EMPDEPT'', METHOD => ''C''), END; ', 6 START_DATE => SYSDATE, 7 REPEAT_INTERVAL => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=0', 8 END_DATE => NULL, 9 ENABLED => TRUE); 10 END; 11 / PL/SQL procedure successfully completed. SQL> BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE (NAME => 'REFRESH_EMPDEPT_MV_PRIMARY', ATTRIBUTE => 'DATABASE_ROLE', VALUE => 'PRIMARY'), END; / PL/SQL procedure successfully completed.
- Now do the same for the primary database. Create a job with the name
REFRESH_EMPDEPT_MV_STANDBYand set theDATABASE_ROLEattribute toSTANDBY.
The most important feature of the logical standby database is its ability to access the standby and run reports with the flexibility of creating index, temporary tables, and materialized views on it. By creating these objects, you can achieve more performance on the reports. Also some reporting tools that require creating temporary objects can run on logical standby databases. In this section we have studied the methods, limitations, and considerations of creating database objects on the logical standby and tried to implement some of them. This information will help you customize the logical standby for your own needs.
In order to revise what we saw in this chapter, execute the following exercise:
You will do some application tests and you'll do so on the logical standby database. The table SCOTT.SALGRADE will be modified in this test and when the test finishes, you want to revert all the changes to the table and configure the replication once again.
- Disable replication for the table
SCOTT.SALGRADEby creating a skip rule withDBMS_LOGSTDBY.SKIP. - To simulate the test, insert rows into this table on the logical standby after disabling Database Guard.
- Reverse changes made to the table by restoring it from primary. Use the
DBMS_LOGSTDBY.INSTANTIATE_TABLEprocedure. - Remove the skip rule with the
DBMS_LOGSTDBY.UNSKIPprocedure. - Insert into the table
SCOTT.SALGRADEon primary and check if the insert was replicated to standby.
The two types of archived redo logfiles on the logical standby database need to be deleted as they become unnecessary depending on our data retention specifications. The archived logs containing redo that were sent from the primary database are called foreign archived logs and the archived log produced by the logical standby itself, containing the changes on the standby database are called local archived logs. Oracle handles this deletion process automatically while offering some customization.
It's possible to keep foreign archived logs on the fast recovery area defined by DB_RECOVERY_FILE_DEST or on another directory or ASM disk group outside the fast recovery area. The Archivelog deletion policy differs depending on whether the foreign archived logs are in FRA or not.
If we specified the log archive destination for the standby logfiles as LOCATION=USE_DB_RECOVERY_FILE_DEST, the foreign archive logs will be kept in FRA. A foreign archived log in FRA is automatically deleted by the logical standby database if all the redo it contains were applied and then the retention time period specified by DB_FLASHBACK_RETENTION_TARGET passes. The default value for this parameter is 1440 minutes, which is one day. This value is also valid if we did not specify any value for this parameter.
By default, even if we keep the foreign archived log outside the FRA, logical standby handles the automatic deletion of these files. The retention time value for the applied foreign archived logs can be defined with the following syntax:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET ('LOG_AUTO_DEL_RETENTION_TARGET','4320'),The default value for LOG_AUTO_DEL_RETENTION_TARGET is the DB_FLASHBACK_RETENTION_TARGET initialization parameter value in the logical standby database.
If we don't want the logical standby database to automatically delete the foreign archived logs, we can use the following procedure:
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE'), When we disable automatic deletion of foreign archived logs, the DBA_LOGMNR_PURGED_LOG view will help us identify the logs, which are ready to be deleted depending on the retention policy. In order to refresh this view use the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.PURGE_SESSION; PL/SQL procedure successfully completed. SQL> SELECT * FROM DBA_LOGMNR_PURGED_LOG; FILE_NAME -------------------------------------------------- /u01/app/oracle2/archive_std/1_455_791552282.arc /u01/app/oracle2/archive_std/1_456_791552282.arc /u01/app/oracle2/archive_std/1_457_791552282.arc /u01/app/oracle2/archive_std/1_458_791552282.arc /u01/app/oracle2/archive_std/1_459_791552282.arc 5 rows selected.
Local archived logs that were generated from online redo logs of the standby database are created in the same way within the primary databases. Unlike foreign archived logs, logical standby databases do not delete these archived logs automatically unless they're kept in the fast recovery area.
You can use RMAN to handle the deletion of the local archived logs. If a backup strategy is used to backup the logical standby database, we should consider the deletion of the local archived logs in this strategy as we do on the primary databases.