DBLOG
» WTF
Toggle navigation
DBLOG
主页
OCM
1、概览
2、数据导入导出
3、GaussDB(DWS)数据库管理
4、数据库调优与开发实践
5、湖仓一体
6、开发应用
7、集群管理
8、巡检和维运维
About Me
归档
标签
M3原题
无
2025-03-31 17:18:02
11
0
0
admin
### Part 1: Performance Management ``` 统计信息（） admin sql page table_stats exec 后面加上 size auto sql基线 设置sql plan 对应target和max 两个memory都设置为0 sga都是1088 inmeory_size 112 不均匀 收集表空间 for all indexed 添加cascade 文档（page）下有 少dml bitmap 少空间创建索引 设置表 parallel 1 解决共享池 cursor_sharing 参数 force 从不失效 文档（set_table_p 后面换成 no inv ，ture） 统计表（table_s） also使用范围扫描 create ind...gloable partition by hash() partitionS 4 DOP alter system set parallel_degree_policy =limited 时长 old alter system set parallel_min_time_threshold=10; alter system set parallel_min_servers = 30; alter system set parallel_MAX_servers = 200; alter table sales parallel 6; 最小10 per alter system set parallel_min_percent=10 scope=spfile; sga_tatget=600m sga_min_size=300m pga_aggregate_limit=700m; addm alter system set awr_pdb_autoflush_enabled=true; （同一个文档）exec dbms_workload_repository_.modify_snapshot_settings(interval => 60); show paramete cpu alter system set cpu_count=1; 开归档 设置 db_d 生成pfile 文档中配置删除dbname controlfile 替换名字 目录只有location 12 添加监听 tns 监听需要有mgrl 都对应proddg 生成spfile 传输还有密码文件 替换pfile内容 生成spfile 控制文件恢复 auxi 连接 dupli nofile结尾 启动同步 切换快照备库 在切回物理备库 开启tracking \temp_undo参数 rman 设置最下面archive *** to applied on all standby; 开启闪回 dgmgrl 配置 都开启block_start flash 参数 备库dest滞空 关闭同步重启 create add enable edit配置 sync app...old 50 max faststartfailovertarget start obsever enable fast_start failover; switchover 'proddg02' 测试 再切回来 ``` #### Section 1: Creating a Synonym 1. **Create a public synonym called SALES1 for SH.SALES in the PDBPROD2 pluggable database on host01.** ``` create sy ``` #### Section 2: Configuring the Oracle Database Resource Manager 1. **Assign the SYSTEM user as the resource administrator.** ``` em 方案 用户中添加admin resource ``` 2. **Create a plan called DAYTIMEP for the PRODCDB container database with the following specifications:** - a. **PDBPROD1 gets five shares of CDB resources.** - b. **PDBPROD2 gets two shares of CDB resources.** - c. **PDBPROD1 on PRODCDB will not exceed 85% of CPU utilization.** - d. **PDBPROD2 on PRODCDB will not exceed 60% of CPU utilization.** - e. **No PDB within PRODCDB will exceed 50% of PARALLEL_SERVERS_TARGET.** ``` em 管理 资源管理 资源计划 创建 添加数据库 改参数 ``` 3. **Create two Resource Manager consumer groups, ONLINE_GRP and REPORTING_GRP, in the PDBPROD1 pluggable database.** ``` develop ``` 4. **Create a plan for the PDBPROD1 database with the following specifications:** - a. **Create a plan named 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.** - c. **The CPU ratio for ONLINE_GRP, REPORTING_GRP, and OTHER_GROUPs should be set as 60, 30, and 10, respectively.** - d. **The REPORTING_GRP group should have a parallel degree limit of 30. The maximum time a session can be idle is 10 minutes.** - 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.** ``` develop share 6 两个excute 都是10 cancel_sql para 30 kill_session idle 10 ``` 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.** ``` ``` #### Section 3: Tuning SQL Statements 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.** ``` 创建索引 收集统计信息 ``` 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.** ``` sql基线 设置sql plan 创建索引 设置表 parallel 1 ``` 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.** ``` 设置result_cache 参数 设置表 alter table -- (mode force) 执行343 ``` 4. **Enable faster reporting and ad hoc queries on the SH.CUSTOMERS table in PDBPROD1 without impacting the existing workload.** - a. **No additional indexes should be used for this performance improvement.** - 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.** - c. **Ensure that the highest priority is given to the queries on the CUSTOMERS table in comparison to all other tables in PDBPROD1.** - d. **In the CUSTOMERS table, data in the CUST_ID and CUST_FIRST_NAME columns should occupy minimum space in memory.** ``` 设置参数 em修改 ``` #### Section 4: Optimizing Schema Objects 1. **In the PDBPROD1 database, an application needs to access the SH.CUSTOMERS.CUST_LAST_NAME column.** - a. **The table data is in mixed case and the application filters using upper case.** - 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.** ``` 创建索引 索引大写 ``` 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.** ``` 收集统计信息 ``` 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.** ``` 创建bitmap索引 ``` 4. **In the PDBPROD1 database, create an index on the SH.CUSTOMERS.COUNTRY_ID and SH.CUSTOMERS.CUST_CITY columns.** - a. **The index must be named COUNTRY_CUST_IDX.** - b. **The application requires that the leading column of this index must be the COUNTRY_ID column.** - 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.** ``` create index COUNTRY_CUST_IDX on CUSTOMERS(COUNTRY_ID,CUST_CITY) compress 1; 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.** ``` show parameter cursor share =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.** 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.** ``` gloable partition by (PROD_ID) partitions 4 ``` #### Section 5: Configuring Parallel Execution 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:** - 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.** - b. **In PDBPROD1, any SQL statement that takes 10 seconds or more to execute should be considered for automatic DOP.** - c. **Allow up to 200 parallel execution servers to be started for the PRODCDB instance. The instance should create 30 servers at startup.** - d. **In PDBPROD1, set the SH.SALES table to have a default DOP of six.** - 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.** ``` parallss ``` #### Section 6: Instance Tuning 1. **Set the total memory size of the SGA available to the PDBPROD1 pluggable database in the PRODCDB database to 600 MB.** ``` ``` 2. **Ensure that the SGA memory size used by the PDBPROD1 pluggable database in the PRODCDB database is at least 300 MB.** ``` ``` 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.** ``` ``` 4. **Retrieve ADDM reports in a specific PDBPROD1 pluggable database for performance investigations. Please set up the following:** - a. **Enable the setting to allow AWR snapshot capture on PDBPROD1.** - b. **Set the AWR snapshot take interval in PDBPROD1 to 60 minutes.** ``` ``` 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.** ``` ``` --- ### Part 2: Data Guard #### Section 1: Creating a Physical Standby Database 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:** - 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.** - b. **The directory structure should be identical for both databases.** - c. **Configure the Data Guard Broker for use.** ``` ``` #### Section 2: Configuring the Physical Standby Database for Testing 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.** ``` ``` #### Section 3: Configuring the Physical Standby Database for Reporting and Redo Apply 1. **Configure the physical standby database to be used as a reporting server and ensure that it is open for reporting.** - a. **Configure host01 as the primary database and host02 as the standby database.** - b. **The standby database must be open, including pluggable databases, for reporting purposes.** - 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.** ``` ``` 2. **Reduce the overhead on incremental backups to a minimum on the primary database regardless of which database is acting as the primary.** ``` ``` 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.** ``` ``` #### Section 4: Automating Management 1. **The physical standby database should acknowledge receipt of the redo only when it can guarantee that it is able to apply that redo.** ``` ``` 2. **Automate failovers and use host02 for any components that provide the automation. Make sure that the configuration is synchronized and fully operational.** ``` ``` 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.** ``` ``` #### Section 5: Performing a Switchover 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.** ``` ``` 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:** - a. **Primary database must open in read-write mode.** - b. **The physical standby database must have managed recovery enabled and open as read-only. The pluggable database must also be readable.** ``` ```
上一篇:
M2原题
下一篇:
M4原题
0
赞
11 人读过
新浪微博
微信
腾讯微博
QQ空间
人人网
DBLOG
admin