Shulou(Shulou.com)06/01 Report--

I. the method of manually generating awr report

1. After the corresponding permission user logs in (sysdba), in $ORACLE_HOME/rdbms/admin

2. Execute @? / rdbms/admin/awrrpt.sql in sqlplus and follow the prompts.

3. Generate AWR report description

Single instance: @ $ORACLE_HOME/rdbms/admin/awrrpt.sql

RAC:@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

Generate a specific instance of RAC: @ $ORACLE_HOME/rdbms/admin/awrrpti.sql

Generate reports for multiple instances of RAC: @ $ORACLE_HOME/rdbms/admin/awrgrpti.sql

Generate AWR report for SQL: @ $ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Generate an awr report of SQL for a specific instance: @ $ORACLE_HOME/rdbms/admin/awrsqrpi.sql

Generate a single instance time period comparison report: @ $ORACLE_HOME/rdbms/admin/awrddrpt.sql

Generate RAC period comparison report: @ $ORACLE_HOME/rdbms/admin/awrgdrpt.sql

Generate a time period comparison report for a specific instance: @ $ORACLE_HOME/rdbms/admin/awrddrpi.sql

Generate RAC specific multi-instance period comparison report: @ $ORACLE_HOME/rdbms/admin/awrgdrpi.sql

Note: the directory must have Oracle permission.

II. AWR report management

1. View the current AWR save policy, settings: snapshot interval, save time.

SQL > col SNAP_INTERVAL format A20

SQL > col RETENTION format A20

SQL > select * from dba_hist_wr_control

2. Adjust AWR configuration, modify snapshot interval and save time, etc. AWR configurations are all configured through the dbms_workload_repository package.

Adjust the frequency and retention policy of snapshot generated by AWR, such as changing the collection interval to 30 minutes. And keep it for 5 days (in minutes):

SQL > exec dbms_workload_repository.modify_snapshot_settings (interval= > 30, retention= > 524 / 60)

3. Turn off AWR report

Turn off AWR and set interval to 0 to turn off automatic snapshot capture

SQL > exec dbms_workload_repository.modify_snapshot_settings (interval= > 0)

4. Create a snapshot manually

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()

5. View snapshots

SQL > select * from sys.wrh$_active_session_history

6. Manually specify the scope for deleting snapshots

SQL > exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id = > 973, high_snap_id = > 999, dbid = > 262089084)

7. Create a baseline and save the data for future analysis and comparison

SQL > exec dbms_workload_repository.create_baseline (start_snap_id = > 1003, end_snap_id = > 1013, 'apply_interest_1')

8. Delete baseline

SQL > exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name = > 'apply_interest_1', cascade = > FALSE)

9. Export and migrate AWR data to other databases for later analysis

SQL > exec DBMS_SWRF_INTERNAL.AWR_EXTRACT (dmpfile = > 'awr_data.dmp', mpdir = >' DIR_BDUMP', bid = > 1003, eid = > 1013)

10. Migrate AWR data files to other databases

Exec DBMS_SWRF_INTERNAL.AWR_LOAD (SCHNAME = > 'AWR_TEST', dmpfile = >' awr_data.dmp', dmpdir = > 'DIR_BDUMP')

The AWR data is transferred to the SYS schema: SQL > exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME = > 'TEST')

11. Generate the first awr report

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level= > 'ALL')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.