Oracle Data Guard 11gR2 Administration Beginner's Guide

上QQ阅读APP看书，第一时间看更新

Time for action – creating objects on the logical standby database

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 SYS user 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 table privilege for the HR user. 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 SYS user 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 STANDBY or NONE. 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 enabled

The Database Guard mode is STANDBY and 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 Database privilege 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 EMP and DEPT tables of the user SCOTT was 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_ROLE attribute, 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_ROLE attribute of PRIMARY). 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 their DATABASE_ROLE attribute. 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_STANDBY and set the DATABASE_ROLE attribute to STANDBY.

What just happened?

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.

Have a go hero – skip, disable guard, insert, instantiate, and disable skip

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.SALGRADE by creating a skip rule with DBMS_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_TABLE procedure.
Remove the skip rule with the DBMS_LOGSTDBY.UNSKIP procedure.
Insert into the table SCOTT.SALGRADE on primary and check if the insert was replicated to standby.

Automatic deletion of archived logs

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.

Deletion of the foreign archived logs

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.

Files inside the fast recovery area

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.

Files outside the fast recovery area

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.

We can now manually delete these files from the filesystem.

Deletion of the local archived logs

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.