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

This article will explain in detail how Oracle 12.2 uses Database Link to optimize Standby Database Workload. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Starting with Oracle 12.2, the standby workload can be optimized by specifying a database link in the database_link_to parameter. For security reasons, Oracle recommends using private database links. The link must belong to the sys user and be accessible to authorized users. The Oracle database contains a default permission user name of SYS$UMF.

Use the sys user in the main library to create table T1 and configure the sys$umf user. This user is locked by default, so you need to unlock the user and set the password first:

SQL > alter user sys$umf account unlock identified by xxzx7817600;User altered.SQL > create table T1 as select * from user_tables;Table created.SQL > create index idx_t1_tablename on T1 (table_name); Index created.SQL > exec dbms_stats.gather_table_stats ('sys','t1',degree = > 4 cascade = > true); PL/SQL procedure successfully completed.

Create a cs_dg link to the primary library. Since ADG is read-only, all database link creation operations need to be performed in the primary library:

SQL > create database link cs_dg2_to_cs2 connect to sys$umf identified by abcd1234 using 'CS';Database link created.

Verify that the database link is created successfully in the repository

Col owner for a20SQL > col object_name for a50SQL > select owner,object_name from dba_objects where object_type='DATABASE LINK' OWNER OBJECT_NAME--SYS CS_DG2_TO_CS2SYS SYS_HUBSQL > select table_name from user_tables@cs_dg2_to_cs2 No rows selectedSQL > select sysdate from dual@cs_dg2_to_cs2;SYSDATE-24-JAN-19

Query table T1 in the slave database, and the database_link_to parameter is used to specify the data link name cs_dg2_to_cs2 from the standby database to the main database.

SQL > variable tname varchar2 (30); SQL > variable query varchar2 (500); SQL > exec: tname: = 'jy_task';PL/SQL procedure successfully completed.SQL > exec: query: =' select / * + full (t) * / col1 from T1 t where table_name=''T1''' PL/SQL procedure successfully completed.SQL > begin 2: tname: = dbms_sqltune.create_tuning_task 3 (4 sql_text = >: query, 5 task_name = >: tname, 6 database_link_to = > 'CS_DG2_TO_CS2', 7 scope= >' COMPREHENSIVE', 8 time_limit= > 60, 9 description= > 'Task to tune a query_on adg'); 10 end; 11 / PL/SQL procedure successfully completed.SQL > exec dbms_sqltune.execute_tuning_task (: tname) PL/SQL procedure successfully completed.SQL > select dbms_sqltune.report_tuning_task (: tname) from dual DBMS_SQLTUNE.REPORT_TUNING_TASK (: TNAME)-GENERAL INFORMATION SECTION -- this is the end of the article on "how Oracle 12.2 optimizes Standby Database Workload with Database Link" Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it 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.