Module 3

### Part 1: Performance Management  
第 1 部分：性能管理

**Section 1: Creating a Synonym**  
第 1 节：创建同义词

1. **Create a public synonym called SALES1 for SH.SALES in the PDBPROD2 pluggable database on host01.**  
   1. 在 host01 上的 PDBPROD2 可插拔数据库中，为 SH.SALES 创建一个名为 SALES1 的公共同义词

```
-- 授予表的查询权限
GRANT SELECT ON sh.sales TO PUBLIC;
-- 创建公有同义词
CREATE PUBLIC SYNONYM salesl FOR sh.sales;
```

**Section 2: Configuring the Oracle Database Resource Manager**  
第 2 节：配置 Oracle 数据库资源管理器
**Set up and configure the Resource Manager on the PRODCDB container database. The specifications are as follows:**  
在 PRODCDB 容器数据库上设置和配置资源管理器。具体要求如下：
1. **Assign the SYSTEM user as the resource administrator.**  
   1. 将 SYSTEM 用户指定为资源管理员。
```
em
数据库-方案-用户-sys登陆-系统权限-编辑列表
将admin resource manager移动过去
应用
```
2. **Create a plan called DAYTIMEP for the PRODCDB container database with the following specifications:**
   2. 为 PRODCDB 容器数据库创建一个名为 DAYTIMEP 的计划，具有以下规格：
   a. **PDBPROD1 gets five shares of CDB resources.**  
      a. PDBPROD1 获得五个 CDB 资源份额。

b. **PDBPROD2 gets two shares of CDB resources.**  
      b. PDBPROD2 获得两个 CDB 资源份额。

c. **PDBPROD1 on PRODCDB will not exceed 85% of CPU utilization.**  
      c. PRODCDB 上的 PDBPROD1 CPU 利用率不得超过 85%。

d. **PDBPROD2 on PRODCDB will not exceed 60% of CPU utilization.**  
      d. PRODCDB 上的 PDBPROD2 CPU 利用率不得超过 60%。

e. **No PDB within PRODCDB will exceed 50% of PARALLEL_SERVERS_TARGET.**  
      e. PRODCDB 中的任何 PDB 都不得超过 PARALLEL_SERVERS_TARGET 的 50%。

```
em
数据库-管理-资源管理器-CDB资源计划-创建
计划：DAYTIMEP
资源分配-添加删除
加入pdbprod1\2
按上面修改资源限制
```

3. **Create two Resource Manager consumer groups, ONLINE_GRP and REPORTING_GRP, in the PDBPROD1 pluggable database.**  
   3. 在 PDBPROD1 可插拔数据库中创建两个资源管理器消费者组：ONLINE_GRP 和 REPORTING_GRP

```
sqldev
resource manager
consumer groups

两个都是
Please Enter Value  ：ONLINE_GRP、REPORTING_GRP
ONLINE_GRP ：ONLINE_GRP、REPORTING_GRP

```
4. **Create a plan for the PDBPROD1 database with the following specifications:**  
   4. 为 PDBPROD1 数据库创建一个计划，具有以下规格：
   a. **Create a plan named DAYTIME_PDB1.**  
      a. 创建一个名为 DAYTIME_PDB1 的计划。
   b. **The CPU time for a query for a session in the ONLINE_GRP group should be limited to 10 seconds. If the query is estimated to take longer than 10 seconds, then the query should be canceled.**  
      b. ONLINE_GRP 组中会话的查询 CPU 时间应限制为 10 秒。如果查询预计耗时超过 10 秒，则应取消该查询。
   c. **The CPU ratio for ONLINE_GRP, REPORTING_GRP, and OTHER_GROUPs should be set as 60, 30, and 10, respectively.**  
      c. ONLINE_GRP、REPORTING_GRP 和 OTHER_GROUP 的 CPU 比率应分别设置为 60、30 和 10。
   d. **The REPORTING_GRP group should have a parallel degree limit of 30. The maximum time a session can be idle is 10 minutes.**  
      d. REPORTING_GRP 组应具有 30 的并行度限制。会话的最大闲置时间为 10 分钟。
   e. **If a session in the REPORTING_GRP consumer group exceeds 10,000 I/O requests or exceeds 3,000 megabytes of data transferred, the session should be terminated.**  
      e. 如果 REPORTING_GRP 消费者组中的会话超过 10,000 次 I/O 请求或超过 3,000 兆字节的数据传输，则应终止该会话。

```
sqldev
plan
创建DAYTIME_PDB1
再DAYTIME_PDB1上添加消费组
分别对应
switch_group cancel_sql  kill_session
cpu为shares

```
5. **The DEFAULT_CDB_PLAN resource plan should be active in the PRODCDB container database, and the DEFAULT_PLAN resource plan should be active in the PDBPROD1 pluggable database.**  
   5. 在 PRODCDB 容器数据库中应激活 DEFAULT_CDB_PLAN 资源计划，而在 PDBPROD1 可插拔数据库中应激活 DEFAULT_PLAN 资源计划。

```
看好哪个计划  open
```
### Section 3: Tuning SQL Statements  
第 3 节：调优 SQL 语句

1. **The PDBPROD1 pluggable database has the SH user (password: sh) and the CUSTOMERS table. SQL statements that query this CUSTOMERS table use the CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID columns as predicates in sets, and these columns have high correlation. Tune the optimizer for more accurate selectivity.**  
   1. PDBPROD1 可插拔数据库具有 SH 用户（密码：sh）和 CUSTOMERS 表。查询此 CUSTOMERS 表的 SQL 语句使用 CUST_CITY、CUST_STATE_PROVINCE 和 COUNTRY_ID 列作为集合中的谓词，这些列具有高度相关性。调整优化器以获得更准确的选择性。

```
-- 创建索引
CREATE INDEX cust_con_indx 
ON customers(cust_city, cust_state_province, country_id);
-- 收集表统计信息
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
      ownname          => 'SH',
      tabname          => 'CUSTOMERS',
      method_opt       => 'FOR COLUMNS (cust_city, cust_state_province, country_id) SIZE AUTO'
   );
END;
/
```
2. **Log in to the PDBPROD1 database as the SH user, with password sh, and run the 3_4_2.sql script that is located in /home/oracle/scripts. Evolve the SQL plan baselines for the SQL statement in the script.**  
   2. 以用户 SH 登录 PDBPROD1 数据库，密码为 sh，并运行位于 /home/oracle/scripts 中的 3_4_2.sql 脚本。演化脚本中 SQL 语句的 SQL 计划基线。

```
sqlplus  sh/sh@pdbprod1

@3_4_2.sql

emcc
数据库-性能-sql-sql计划控制-sql计划基线-加载-从游标高速缓存加载-搜索
上面填%LOAD_CC%
下面选中

```
3. **In the PDBPROD1 pluggable database, enable queries on the HR.EMPLOYEES table to use the result cache. Do not allow changes to any SQL to enable result cache. Set the maximum size of the result cache to 15 MB.**  
   3. 在 PDBPROD1 可插拔数据库中，启用 HR.EMPLOYEES 表的查询使用结果缓存。不得更改任何 SQL 以启用结果缓存。将结果缓存的最大大小设置为 15 MB。

```
show parameter result_cache
alter system set result_cache_max_size=15m;

alter table hr.EMPLOYEES result_cache(mode force);

@3_4_3.sql
```
4. **Enable faster reporting and ad hoc queries on the SH.CUSTOMERS table in PDBPROD1 without impacting the existing workload.**  
   4. 在 PDBPROD1 中启用对 SH.CUSTOMERS 表的更快报告和临时查询，而不影响现有工作负载。
   a. **No additional indexes should be used for this performance improvement.**  
      a. 此性能提升不应使用额外的索引。
   b. **The total memory available for the SGA should be 1088 MB, and a maximum of 112 MB of the SGA can be used for this configuration.**  
      b. 可用的 SGA 总内存应为 1088 MB，且此配置最多可以使用 SGA 的 112 MB。
   c. **Ensure that the highest priority is given to the queries on the CUSTOMERS table in comparison to all other tables in PDBPROD1.**  
      c. 确保与 PDBPROD1 中所有其他表相比，CUSTOMERS 表的查询优先级最高。
   d. **In the CUSTOMERS table, data in the CUST_ID and CUST_FIRST_NAME columns should occupy minimum space in memory.**  
      d. 在 CUSTOMERS 表中，CUST_ID 和 CUST_FIRST_NAME 列的数据应占用最小内存空间。

```
alter system set memory_max_target = 0 scpoe=spfile;
alter system set memory_target= 0 scope=spfile;
alter system set sga_max_size=1088M scope =spfile;
alter system set  sga_target=1088M scope =spfile;
alter system set inmemory_size=112m scope = spfile;

重启数据库

emcc
方案-数据库对象-表
根据用户名、表名搜索
编辑
内存中列存储
启用内存存储
基于查询存储
高
下拉
 CUST_ID and CUST_FIRST_NAME
变更查询高

```
### Section 4: Optimizing Schema Objects  
第 4 节：优化模式对象

1. **In the PDBPROD1 database, an application needs to access the SH.CUSTOMERS.CUST_LAST_NAME column.**  
   1. 在 PDBPROD1 数据库中，一个应用程序需要访问 SH.CUSTOMERS.CUST_LAST_NAME 列。

a. **The table data is in mixed case and the application filters using upper case.**  
      a. 表数据为混合大小写，应用程序使用大写进行过滤。
   b. **Analysis reveals that a normal index on that column is not used. Create an index named CUST_LAST_NAME_IDX on the CUST_LAST_NAME column that will be used by the application.**  
      b. 分析显示该列上的普通索引未被使用。创建一个名为 CUST_LAST_NAME_IDX 的索引在 CUST_LAST_NAME 列上，以便应用程序使用。

```
sqlplus sh/sh@pdbprod1

set autotrace trace exp;

select * from CUSTOMERS where upper(CUST_LAST_NAME)='TEXT';

create index CUST_LAST_NAME_IDX on CUSTOMERS(upper(CUST_LAST_NAME));

```
2. **In the PDBPROD1 database, the column HR.EMPLOYEES.DEPARTMENT_ID contains unevenly distributed data. You need to supply more information to the optimizer to allow for more efficient use of indexes on this table. Generate statistics on the EMPLOYEES table to solve this problem.**  
   2. 在 PDBPROD1 数据库中，HR.EMPLOYEES.DEPARTMENT_ID 列包含分布不均的数据。您需要为优化器提供更多信息，以便更有效地使用该表上的索引。生成 EMPLOYEES 表的统计信息以解决此问题。

```
exec dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=>'for all indexed columns size auto',cascade=>true);

```
3. **In the PDBPROD1 database, the column SH.CUSTOMERS.COUNTRY_ID is of low cardinality. There is little DML activity on the table. Create an index that can take advantage of these column attributes.**  
   3. 在 PDBPROD1 数据库中，SH.CUSTOMERS.COUNTRY_ID 列的基数低。该表上的 DML 活动很少。创建一个可以利用这些列属性的索引。

```
create bitmap index CUST_idx2 on CUSTOMERS(COUNTRY_ID);

```
4. **In the PDBPROD1 database, create an index on the SH.CUSTOMERS.COUNTRY_ID and SH.CUSTOMERS.CUST_CITY columns.**  
   4. 在 PDBPROD1 数据库中，在 SH.CUSTOMERS.COUNTRY_ID 和 SH.CUSTOMERS.CUST_CITY 列上创建索引。

a. **The index must be named COUNTRY_CUST_IDX.**  
      a. 索引必须命名为 COUNTRY_CUST_IDX。
   b. **The application requires that the leading column of this index must be the COUNTRY_ID column.**  
      b. 应用程序要求该索引的前导列必须是 COUNTRY_ID 列。
   c. **Take advantage of the Oracle feature of indexes that allows the creation of the index to use less space when the leading column is often duplicated.**  
      c. 利用 Oracle 索引的特性，在前导列经常重复时创建索引以使用更少的空间。

```
create index COUNTRY_CUST_IDX on CUSTOMERS(COUNTRY_ID,CUST_CITY) compress 1;
```
5. **Analysis of the PDBPROD1 database reveals that a third-party application is not using bind variables and has skewed data. In addition, you find shared pool latch contention. Find the best solution to reduce shared pool usage.**  
   5. 对 PDBPROD1 数据库的分析显示，第三方应用程序未使用绑定变量，且数据分布不均。此外，您发现共享池锁争用。寻找减少共享池使用的最佳解决方案。

```
alter system set cursor_sharing =force;
```
6. **In the PDBPROD1 database, ensure that the cursors pertaining to the SH.SALES table in the cache are never invalidated by optimizer statistics gathering.**  
   6. 在 PDBPROD1 数据库中，确保与 SH.SALES 表相关的缓存光标在优化器收集统计信息时从不失效。

```
exec dbms_stats_.set_table_prefs('SH','SALES','NO_INVALIDATE','TRUE');

exec dbms_stats.gather_table_stats('SH','SALES');
```
7. **In the PDBPROD1 database, in the table SH.PROD_INFO, there are many buffer busy waits during inserts. A sequence is used to generate values for the PROD_ID column in this table. Range scans are also frequently performed on the PROD_ID column. Create an index, PROD_IDX, on the PROD_ID column that does not suffer as many buffer busy waits on inserts, but which may also be used for index range scans.**  
   7. 在 PDBPROD1 数据库的 SH.PROD_INFO 表中，插入期间存在许多缓冲区繁忙等待。该表使用序列生成 PROD_ID 列的值。同时，PROD_ID 列上也经常执行范围扫描。创建一个名为 PROD_IDX 的索引在 PROD_ID 列上，该索引在插入时不会遭遇过多的缓冲区繁忙等待，但也可用于索引范围扫描。

```
create index PROD_IDX on PROD_INFO(PROD_ID) global partition by  hash(PROD_ID) partitions 4;
```
### Section 5: Configuring Parallel Execution  
第 5 节：配置并行执行

1. **Configuring parallel execution in the PRODCDB database and PDBPROD1 database. Use a plan table that persists across logins. As the SYS user, implement the following:**  
   1. 配置 PRODCDB 数据库和 PDBPROD1 数据库中的并行执行。使用一个在登录间持久存在的计划表。作为 SYS 用户，实施以下内容：

a. **In PDBPROD1, enable automatic degree of parallelism without allowing for in-memory parallel execution. In the root container, automatic DOP should be enabled for in-memory parallel execution, but disable performance feedback.**  
      a. 在 PDBPROD1 中启用自动并行度（DOP），但不允许内存中的并行执行。在根容器中，应为内存中的并行执行启用自动 DOP，但禁用性能反馈。

```
show parameter parallel
alter  system set  parallel_degree_policy =limited;
```
   b. **In PDBPROD1, any SQL statement that takes 10 seconds or more to execute should be considered for automatic DOP.**  
      b. 在 PDBPROD1 中，任何执行时间超过 10 秒的 SQL 语句都应考虑用于自动 DOP。

```
alter system set parallel_min_time_threshold=10;
```
   c. **Allow up to 200 parallel execution servers to be started for the PRODCDB instance. The instance should create 30 servers at startup.**  
      c. 允许为 PRODCDB 实例启动最多 200 个并行执行服务器。该实例在启动时应创建 30 个服务器。

```
alter  system set  parallel_min_servers = 30;
alter  system set  parallel_min_servers = 200;
```
   d. **In PDBPROD1, set the SH.SALES table to have a default DOP of six.**  
      d. 在 PDBPROD1 中，将 SH.SALES 表的默认 DOP 设置为六。

```
alter table sales  parallel 6;
```
   e. **In PDBPROD1, a parallelized SQL statement should not execute unless at least 10% of the requested number of parallel execution processes are available to start its execution.**  
      e. 在 PDBPROD1 中，只有在至少 10% 的请求数量的并行执行进程可用以启动执行时，才应执行并行 SQL 语句。

```
alter system set parallel_min_percent=10 scope=spfile;
```

### Section 6: Instance Tuning  
第 6 节：实例调优

1. **Set the total memory size of the SGA available to the PDBPROD1 pluggable database in the PRODCDB database to 600 MB.**  
   1. 将 PRODCDB 数据库中可用的 PDBPROD1 可插拔数据库的 SGA 总内存大小设置为 600 MB。

```
alter system set sga_tatget=600m scope=spfile;
```
2. **Ensure that the SGA memory size used by the PDBPROD1 pluggable database in the PRODCDB database is at least 300 MB.**  
   2. 确保 PRODCDB 数据库中 PDBPROD1 可插拔数据库使用的 SGA 内存大小至少为 300 MB。

```
altrt system set sga_min_size=300m scope=spfile;
```
3. **Set the memory size limit of the PGA that the PDBPROD1 pluggable database in the PRODCDB database can use to 700 MB. Do not exceed the limit.**  
   3. 将 PRODCDB 数据库中 PDBPROD1 可插拔数据库可以使用的 PGA 内存大小限制设置为 700 MB。不得超过该限制。

```
alter system set pga_aggregate_limit=700m;
```
4. **Retrieve ADDM reports in a specific PDBPROD1 pluggable database for performance investigations. Please set up the following:**  
   4. 在特定的 PDBPROD1 可插拔数据库中获取 ADDM 报告以进行性能调查。请设置以下内容：

a. **Enable the setting to allow AWR snapshot capture on PDBPROD1.**  
      a. 启用设置以允许在 PDBPROD1 上捕获 AWR 快照。
   b. **Set the AWR snapshot take interval in PDBPROD1 to 60 minutes.**  
      b. 将 PDBPROD1 中 AWR 快照的采集间隔设置为 60 分钟。

```
alter system set awr_pdb_autoflush_enabled=true;

exec dbms_workload_repository_.modify_snapshot_settings(interval => 60);

```
5. **The PRODCDB database is running on host01 with 2 CPUs. Ensure that PRODCDB instances use no more than one CPU on host01 at the same time.**  
   5. PRODCDB 数据库在 host01 上运行，具有 2 个 CPU。确保 PRODCDB 实例在 host01 上同一时间不使用超过一个 CPU。

```
show paramete cpu

alter system set cpu_count=1;
```
### Part 2: Data Guard  
第 2 部分：数据保护

#### Section 1: Creating a Physical Standby Database  
第 1 节：创建物理备用数据库

1. **PRODDG database is located on host01 and has a DB_UNIQUE_NAME of PRODDG01. Use the following specifications to create a physical standby database for PRODDG:**  
   1. PRODDG 数据库位于 host01 上，具有 DB_UNIQUE_NAME 为 PRODDG01。使用以下规格为 PRODDG 创建物理备用数据库：

a. **PRODDG will be the physical standby database on host02 with DB_UNIQUE_NAME set to PRODDG02. The instance name for both databases should be PRODDG.**  
      a. PRODDG 将成为在 host02 上的物理备用数据库，DB_UNIQUE_NAME 设置为 PRODDG02。两个数据库的实例名称应为 PRODDG。
   b. **The directory structure should be identical for both databases.**  
      b. 两个数据库的目录结构应相同。
   c. **Configure the Data Guard Broker for use.**  
      c. 配置数据保护代理以供使用。

```
export ORACLE_SID=PRODDG

--确认数据文件
select name from v$datafile;

--开启归档

create pfile from spfile;

cd $ORALCE_HOME/dbs

cp initPRODDG.ora orapwPRODDG /u01/app/oracle/oradata/PRODDG
cd /u01/app/oracle/oradata

--启动到nomount状态传输文件
tar -czvf PRODDG.tar.gz  PRODDG

scp PRODDG.tar.gz oracle@host02:/u01/pp/oracle/oradata

-修改pfile
添加（从官方文档里粘出   删除lis）
DB_NAME=PRODDG
DB_UNIQUE_NAME=PRODDG01
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDG01,PRODDG02)'
LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDG01'
LOG_ARCHIVE_DEST_2='SERVICE=PRODDG02 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDG02'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
fal_client=PRODDG01
FAL_SERVER=PRODDG02
DB_FILE_NAME_CONVERT='/PRODDG02/','/PRODDG01/'
LOG_FILE_NAME_CONVERT='/PRODDG02/','/PRODDG01/'
STANDBY_FILE_MANAGEMENT=AUTO

create spfile from pfile;

startup mount;

alter database filashback on;

alter database force loggingl;

alter database open;

--添加日志组
desc v$logfile;

select member,group# from v$logfile;

select bytes/1024/1024 from v$log;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/slog1.rdo') SIZE 200M;
 
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/slog2.rdo') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/slog3.rdo') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/slog4.rdo') SIZE 200M;

--创建standby 控制文件
alter database create standby controlfile as '/u01/app/oracle/proddg_control.ctl';
scp /u01/app/oracle/proddg_control.ctl host02：/u01/app/oracle/

添加监听
        (SID_DESC=
         (GLOBAL_DBNAME=PRODDG01.example.com)
         (SID_NAME=PRODDG)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
        )
        (SID_DESC=
         (GLOBAL_DBNAME=PRODDG01_DGMGRL.example.com)
         (SID_NAME=PRODDG)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)

添加tnsname
PRODDG01=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG01.example.com)
    )
  )

PRODDG02=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG02.example.com)
    )
  )

--host02
cd /u01/app/oracle/oradata/PRODDG

--修改initPRODDG.ora 与host01相反
DB_NAME=PRODDG
DB_UNIQUE_NAME=PRODDG02
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDG01,PRODDG02)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDG02'
LOG_ARCHIVE_DEST_2='SERVICE=PRODDG01 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDG01'

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=PRODDG01

DB_FILE_NAME_CONVERT='/PRODDG01/','/PRODDG02/'
LOG_FILE_NAME_CONVERT='/PRODDG01/','/PRODDG02/'
STANDBY_FILE_MANAGEMENT=AUTO

--创建pfile中的目录
mkdir -p /u01/app/oracle/admin/PRODDG/adump
mkdir -p /u01/app/oracle/fast_recovery_area/PRODDG

添加lis 与tns
        (SID_DESC=
         (GLOBAL_DBNAME=PRODDG02.example.com)
         (SID_NAME=PRODDG)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
        )
        (SID_DESC=
         (GLOBAL_DBNAME=PRODDG02_DGMGRL.example.com)
         (SID_NAME=PRODDG)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)

cp initPRODDG.ora  orapwPRODDG  $ORACLE_HOME/dbs
 
 
添加tnsname
PRODDG01=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG01.example.com)
    )
  )

lsnrctl reload

export ORACLE_SID=PRODDG
sqlplus / as sysdba
create spfile from pfile;
startup nomount

rman target /

restore controlfile from '/u01/app/oracle/proddg_control.ctl';

startup mount ;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ALTER SYSTEM SET DG_BROKER_START=TRUE;
ALTER SYSTEM RESET LOG_ARCHIVE_DEST_1;
ALTER SYSTEM RESET LOG_ARCHIVE_DEST_1;

--开启闪回
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH;

--配置DGMGRL
CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'PRODDG01' CONNECT IDENTIFIER IS PRODDG02;
ADD DATABASE 'PRODDG02' AS CONNECT IDENTIFIER IS PRODDG02;
enable configuration;

--文档8.3
设置
LogXptMode
ApplyLagThreshold
TransportLagThreshold

help edit
edit DATABASE 'PRODDG01' SET PROPERTY LogXptMode='SYNC';
edit DATABASE 'PRODDG01' SET PROPERTY  ApplyLagThreshold ='45';
edit DATABASE 'PRODDG01' SET PROPERTY  TransportLagThreshold ='30';

edit DATABASE 'PRODDG02' SET PROPERTY LogXptMode='SYNC';
edit DATABASE 'PRODDG02' SET PROPERTY  ApplyLagThreshold ='45';
edit DATABASE 'PRODDG02' SET PROPERTY  TransportLagThreshold ='30';

show parameter temp
alter system set temp_undo_enabled = true;

```
2. **Minimize redo generation for both databases when performing DML on global temporary tables regardless of the role of the database.**  
   2. 在对全局临时表执行 DML 时，减少两个数据库的重做生成，无论数据库的角色如何。

```

```
#### Section 2: Configuring the Physical Standby Database for Testing  
第 2 节：配置物理备用数据库以进行测试

1. **Configure the physical standby database PRODDG to use it for testing, including DML and transactional workloads. Note: The tasks of this section must be performed before Section 3, Section 4, and Section 5 are performed.**  
   1. 配置物理备用数据库 PRODDG 以用于测试，包括 DML 和事务负载。注意：本节的任务必须在进行第 3 节、第 4 节和第 5 节之前完成。

```
--2节点执行
create sequence seq1 start with 1 increment by 1 cache 1000;
select  seq1.nextval from dual;
create sequence seq2 session;
select seq2.nextval fro dual;

--1节点
alter database enable block change tracking using file '/u01/app/oracle/oradata/PRODDG/proddg_track.f';
select  seq1.nextval from dual;
select seq2.nextval fro dual;
```

#### Section 3: Configuring the Physical Standby Database for Reporting and Redo Apply  
第 3 节：配置物理备用数据库以进行报告和重做应用

1. **Configure the physical standby database to be used as a reporting server and ensure that it is open for reporting.**  
   1. 配置物理备用数据库以用作报告服务器，并确保它对报告开放。
   a. **Configure host01 as the primary database and host02 as the standby database.**  
      a. 将 host01 配置为主数据库，将 host02 配置为备用数据库。
   b. **The standby database must be open, including pluggable databases, for reporting purposes.**  
      b. 备用数据库必须开放，包括可插拔数据库，以便用于报告。
   c. **You should configure the standby database to apply redo as soon as it arrives. Ensure that the standby database applies arriving redo as soon as it arrives regardless of which database is acting as the primary.**  
      c. 应配置备用数据库尽快应用到达的重做。确保备用数据库在到达后立即应用重做，无论哪个数据库充当主数据库。

```
switchover to 'proddg01';
edit DATABASE 'PRODDG01' SET PROPERTY LogXptMode='SYNC';
edit DATABASE 'PRODDG02' SET PROPERTY LogXptMode='SYNC';
```
2. **Reduce the overhead on incremental backups to a minimum on the primary database regardless of which database is acting as the primary.**  
   2. 无论哪个数据库充当主数据库，都要将主数据库上增量备份的开销降到最低。

```
alter database enable block change tracking using file '/u01/app/oracle/oradata/PRODDG/proddg_track.f';
```

3. **Regardless of which database is the primary, configure the database buffer cache to retain its state when you change the role of the database.**  
   3. 无论哪个数据库是主数据库，都应配置数据库缓冲区缓存以在更改数据库角色时保留其状态。

```
rman target /

--都执行
show all
configure archivelog deletion policy  to applied on all standby;
```
### Section 4: Automating Management  
第 4 节：自动化管理

1. **The physical standby database should acknowledge receipt of the redo only when it can guarantee that it is able to apply that redo.**  
   1. 物理备用数据库仅在能够确保应用该重做时，才应确认收到重做。

```
enable database 'PRODDG02';
```
2. **Automate failovers and use host02 for any components that provide the automation. Make sure that the configuration is synchronized and fully operational.**  
   2. 自动化故障转移，并在 host02 上使用任何提供自动化的组件。确保配置已同步并完全正常运行。

```
--02上
enable fast_start failover;
switchover to 'PRODDG02';

```
3. **An alert should be raised when redo has not been applied by the physical standby database within 50 seconds, regardless of which database is the standby.**  
   3. 当物理备用数据库在 50 秒内未应用重做时，应发出警报，无论哪个数据库是备用。

```
dgmgrl sys/oracle@proddg02 "start observer" &
```
### Section 5: Performing a Switchover  
第 5 节：执行切换

1. **Switch the database roles so that the database running on host02 has the primary database role, and the database on host01 has a physical standby database role.**  
   1. 切换数据库角色，使运行在 host02 上的数据库具有主数据库角色，而运行在 host01 上的数据库具有物理备用数据库角色。

```
switchover to 'PRODDG01'
```
2. **Switch the role of the database again to bring the database running on host02 to the standby database role and the database running on host01 to the primary database role. After the database role is switched, ensure that the following requirements are met:**  
   2. 再次切换数据库的角色，将运行在 host02 上的数据库切换到备用数据库角色，将运行在 host01 上的数据库切换到主数据库角色。切换数据库角色后，确保满足以下要求：
   a. **Primary database must open in read-write mode.**  
      a. 主数据库必须以读写模式打开。
   b. **The physical standby database must have managed recovery enabled and open as read-only. The pluggable database must also be readable.** 
      b. 物理备用数据库必须启用管理恢复，并以只读模式打开。可插拔数据库也必须可读。

```
SELECT OPEN_MODE FROM V$DATRABASE;
```

DBLOG

导航1234

最近发表

友情链接