这篇文章将为大家详细讲解有关Oracle 12.2如何使用Database Link优化Standby Database Workload,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
从Oracle 12.2开始,可以通过在database_link_to参数来指定一个数据库链路来优化备库workload。由于安全原因,Oracle建议使用私有数据库链路。这个链路必须属于sys用户并且能让有权限的用户来访问。Oracle数据库包含一个缺省的权限用户名为SYS$UMF。
在主库中使用sys用户创建表t1与配置sys$umf用户,这个用户默认是被锁定的,所以需要先解锁这个用户并设置密码:
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.
创建一个备库(cs_dg)到主库的链路,由于ADG是只读的,所以创建数据库链路操作都需要在主库中执行:
SQL> create database link cs_dg2_to_cs2 connect to sys$umf identified by abcd1234 using 'CS';
Database link created.
在备库中验证数据库链路是否创建成功
col owner for a20
SQL> col object_name for a50
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
-------------------- --------------------------------------------------
SYS CS_DG2_TO_CS2
SYS SYS_HUB
SQL> select table_name from user_tables@cs_dg2_to_cs2;
no rows selected
SQL> select sysdate from dual@cs_dg2_to_cs2;
SYSDATE
------------
24-JAN-19
在备库中对表t1执行查询,database_link_to参数用来指定备库到主库的数据链路名cs_dg2_to_cs2
SQL> variable tname varchar2(30);
SQL> variable query varchar2(500);
SQL> exec :tname := 'jy_task';
PL/SQL procedure successfully completed.
SQL> exec :query := 'select 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
----------------------------------------------------
关于“Oracle 12.2如何使用Database Link优化Standby Database Workload”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。