DBMS_WORKLOAD_REPOSITORY

MODIFY_SNAPSHOT_SETTINGS Procedure

This procedure controls two aspects of snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload Repository.

Syntax:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS((
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);

Parameters:

Parameter Description

retention:
The new retention time (in minutes). The specified value must be in the range of 1 day to 100 years. If ZERO is specified, the maximum value of 100 years will be used. If NULL is specified, the old value for retention is preserved.

interval:
The new interval setting between each snapshot, in units of minutes. The specified value must be in the range between 10 minutes to 1 year. If ZERO is specified, the maximum value of 1 year will be used. If NULL is specified, the current value is preserved.

dbid:
The database id (default to local DBID.


Examples:

This example changes the interval setting to one hour and the retention setting to two weeks for the local database:

execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 20160);


If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

(or)

 select SNAP_INTERVAL,RETENTION from DBA_HIST_WR_CONTROL;
(from Oracle 11g)

Ex:
Interval > 60 min
Keep time > 30 days.

exec dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 43200);

Default user in Oracle

SYS
SYSTEM
OUTLN
SCOTT
ADAMS
JONES
CLARK
BLAKE
WOOD
STEEL
CLOTH
PAPER
HR
OE
SH
OE
SH
DEMO
ANONYMOUS
AURORA$ORB$UNAUTHENTICATED
AWR_STAGE
CSMIG
CTXSYS
DBSNMP
DIP
DMSYS
DSSYS
EXFSYS
LBACSYS
MDSYS
ORACLE_OCM
ORDPLUGINS
ORDSYS
PERFSTAT
TRACESVR
TSMSYS
XDB


('SYS','SYSTEM','OUTLN','SCOTT','ADAMS','JONES','CLARK',
'BLAKE','WOOD','STEEL','CLOTH','PAPER','HR','OE','SH','OE','SH',
'DEMO','ANONYMOUS','AURORA$ORB$UNAUTHENTICATED','AWR_STAGE',
'CSMIG','CTXSYS','DBSNMP','DIP','DMSYS','DSSYS','EXFSYS','LBACSYS',
'MDSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','PERFSTAT',
'TRACESVR','TSMSYS','XDB')

Oracle IMP Notes

[ID 136697.1] >> hcheck.sql (Health Check)

Startup Upgrade/Migrate/Downgrade

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

How to setup DGMGRL, broker with example

Before we start, remember that DG broker does not have the ability to create standby and is used for managing the dataguard configuration. Before proceeding with step-by-step instructions on how to set up DG broker, I would recommend you to get comfortable with the concepts first.
The following configuration was tested on RHEL4U2 64-bit with Oracle 10.1.0.5 database.
Pri db_unique_name = 'TESTPRI'DG db_unique_name = 'TESTDG'Configuration(any name) = 'DG_TEST'sys password = 'sys'Pri conn stg = 'TESTPRI'
1. Set up init parameters on primary to enable broker
Note: For RAC, ensure dg_broker_config_files are on shared storage and accessible to all the instances.
Note: Broker config files are named as dr1<>.dat and dr2<>.dat
SQL> alter system set dg_broker_start=false sid='*';System altered.SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';System altered.SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';System altered.SQL> alter system set dg_broker_start=true sid='*';System altered.
2. Verify if DMON process has started on all the instances of primary. Example:
$ ps -efgrep dmongrep -v greporacle 16190 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR1$ ps -efgrep dmongrep -v greporacle 29723 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR2
3. Set up init parameters on standby
SQL> alter system set dg_broker_start=false sid='*';System altered.SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';System altered.SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';System altered.SQL> alter system set dg_broker_start=true sid='*';System altered.
4. GLOBAL_DBNAME should be set to <>_DGMGRL.<> in listener.ora on all instances of both primary and standby.
This is important otherwise you'll have TNS-12154 error during switchover operation.
Example:
SID_LIST_LISTENER_TESTPRI = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /apps/oracle/product/10g/db) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = TESTPRIR1) (GLOBAL_DBNAME = TESTPRI_DGMGRL) (ORACLE_HOME = /apps/oracle/product/10g/db) ) )
5. DGMGRL Configuration5.1ConnectDGMGRL> CONNECT sys/sysConnected.5.2Create ConfigurationDGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;Configuration "DG_TEST" created with primary database "TESTPRI".5.3Verify configurationDGMGRL> SHOW CONFIGURATION;Configuration Name: DG_TEST Enabled: NO Protection Mode: MaxPerformance Databases: TESTPRI - Primary databaseCurrent status for "DG_TEST":DISABLED5.4Verify database; if RAC verify if all instances are validatedDGMGRL> show database 'TESTPRI';Database Name: TESTPRI Role: PRIMARY Enabled: NO Intended State: ONLINE Instance(s): TESTPRIR1 TESTPRIR2Current status for "TESTPRI":DISABLED5.5Add standby database to the configurationDGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;Database "TESTDG" added.5.6Enable the brokerDGMGRL> ENABLE CONFIGURATION;Enabled.5.7Verfying againDGMGRL> SHOW CONFIGURATION;Configuration Name: DG_TEST Enabled: YES Protection Mode: MaxPerformance Databases: TESTPRI - Primary database TESTDG - Physical standby databaseCurrent status for "DG_TEST":SUCCESS6. TroubleshootingLet us see some sample issues and their fixIssueDGMGRL> CONNECT sys/sysORA-16525: the Data Guard broker is not yet availableFixSet dg_broker_start=trueIssueAfter enabling the configuration, on issuing SHOW CONFIGURATION, this error comes Warning: ORA-16608: one or more sites have warningsFixTo know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.Few Monitorable properties to troubleshootDGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';IssueDGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT TESTPRIR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting. TESTPRIR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.IssueDGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE TESTPRIR2 ArchiveLagTarget 0 0 TESTPRIR2 LogArchiveMaxProcesses 4 2 4 ExampleDGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses'; LogArchiveMaxProcesses = '4'FixDGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;orSQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';System altered.DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses'; LogArchiveMaxProcesses = '4'More commandsDGMGRL> SHOW DATABASE VERBOSE 'dbname';This will show all property values in detailDGMGRL> HELP;List of all broker commands with usage help
Equivalent Broker Commands to 'ALTER SYSTEM'SQL> alter database recover managed standby database cancel;DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';SQL> alter database recover managed standby database disconnect;DGMGRL> edit database 'stby_dbname' set state='ONLINE';SQL> alter system set log_archive_max_processes=4;DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;SQL> alter system set log_archive_dest_state_2='enable' scope=both;DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';SQL> alter system set log_archive_dest_state_2='defer' scope=both;DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';This will defer all standby databases
That's it my friends. You are all set to use broker now for managing your dataguard configuration. In my next blog, I'll talk about switchover using DG broker