Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to generate AWR reports in the 12.2 version of ADG repository in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Starting with Oracle Database 12.2, you can generate AWR reports for the Active Data Guard (ADG) repository, which no longer requires statspack to analyze performance.

The main library and standby library are located on the 2-node RAC

Main library (db_unique_name = pridb) node prihost1,prihost2

Standby library (db_unique_name = stbdb) node stbhost1,stbhost2

1) confirm the open mode and database role on the slave database:

SQL > select inst_id, open_mode, database_role from gv$database order by 1

INST_ID OPEN_MODE DATABASE_ROLE

1 READ ONLY WITH APPLY PHYSICAL STANDBY

2 READ ONLY WITH APPLY PHYSICAL STANDBY

2) on the main library

The SYS$UMF user is the database user who by default has all permissions to access system-level remote Management Framework (RMF) views and tables. All AWR-related operations in RMF can only be performed by SYS$UMF users.

SYS$UMF users are locked by default and must be unlocked before deploying the RMF topology:

SQL > alter user sys$umf identified by sysumf account unlock

3) create a database link between the main database and the slave database:

On the main library

Create database link dbl_pridb_to_stbdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'stbdb'

Create database link dbl_stbdb_to_pridb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'pridb'

4) We need to configure the database node to add to the topology. Each database node in the topology must be assigned a unique name (default is DB_UNIQUE_NAME):

In this example, we choose the name "prim" for the main library and "stby" for the standby library.

On the main library

Exec dbms_umf.configure_node ('prim')

5) remotely register the standby library with the main library. We can register it through the corresponding database link.

On the repository.

Exec dbms_umf.configure_node ('stby','dbl_stbdb_to_pridb')

6) create a RMF topology:

On the main library

Exec DBMS_UMF.create_topology ('Topology_1')

7) verify the steps completed so far:

Set line 132

Col topology_name format a15

Col node_name format a15

Select * from dba_umf_topology

Select * from dba_umf_registration

For example

SQL > select * from dba_umf_topology

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY

--

Topology_1 2014871576 1 ACTIVE

SQL > select * from dba_umf_registration

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE

Topology_1 prim 2014871576 0 FALSE FALSE OK

8) register the library to the topology.

On the main library:

Exec DBMS_UMF.register_node ('Topology_1',' stby', 'dbl_pridb_to_stbdb',' dbl_stbdb_to_pridb', 'FALSE',' FALSE')

PL/SQL procedure successfully completed.

Enable the AWR service on the node:

Exec DBMS_WORKLOAD_REPOSITORY.register_remote_database (node_name= > 'stby')

PL/SQL procedure successfully completed.

If you encounter "ORA-15766: already registered in an RMF topology", log out of the following nodes, and then rerun the "DBMS_UMF.register_node" registration:

Exec DBMS_UMF.unregister_node ('Topology_1',' stby')

PL/SQL procedure successfully completed.

"ORA-13519: Database id (1730117407) exists in the workload repository"

Log out of the remote database as shown below, and then rerun DBMS_WORKLOAD_REPOSITORY.register_remote_database:

Exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database ('stby','Topology_1',TRUE)

PL/SQL procedure successfully completed.

Verification

Set line 132

Col topology_name format a15

Col node_name format a15

SQL > select * from dba_umf_topology

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY

--

Topology_1 2014871576 6 ACTIVE

SQL > select * from dba_umf_registration

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE

Topology_1 prim 2014871576 0 FALSE FALSE OK

Topology_1 stby 1730117407 0 FALSE FALSE OK

SQL > select * from dba_umf_service

TOPOLOGY_NAME NODE_ID SERVICE

Topology_1 1730117407 AWR

9) RMF creates a remote snapshot

On the main library

Exec dbms_workload_repository.create_remote_snapshot ('stby')

We need to run at least twice to get begin_snap and end_snap.

If you encounter "ORA-13516: AWR Operation failed: Remote source not registered for AWR", manually switch several (2-3) log files on the main library:

Alter system switch logfile

10) create an AWR report

@? / rdbms/admin/awrrpti.sql

Just find the backup database according to dbid and host.

Thank you for reading! This is the end of this article on "how to generate an AWR report in the 12.2 version of ADG in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.