DBLOG
» WTF
Toggle navigation
DBLOG
主页
OCM
1、概览
2、数据导入导出
3、GaussDB(DWS)数据库管理
4、数据库调优与开发实践
5、湖仓一体
6、开发应用
7、集群管理
8、巡检和维运维
About Me
归档
标签
M2原题
无
2025-03-31 17:04:35
10
0
0
admin
**Part 1: Backup/Recovery** ``` 加密和分区表都记得store 闪回归档 develo guid flashback data archive 克隆数据库 设置remote参数，连接的都是cdb 文档backup 25.9.8 外部表 先建普通分区表 sqldr -help 外部表参数 大字段 db_s参数 文档 deve 搜comp 加密 文档 admin sqllan 搜admin key 文档 admin guide 搜encry 本地管理在附近 e default storage() 分区管理 文档 data ware interval-R 创建表空间 -R -P 闪回归档 develop guid 搜 flashback_te 再搜archive 创建用户表空间 创建恢复 授权给sh sh 修改表 文档 SECUSER 搜 'SECUSER policy' exaple相关的 授权 audit system，admin，any,viewer 要dump 和 设置only star 开启star_transformation_enabled 参数 加bitmap索引 加序列 ALTER TABLE EMPLOYEES2 ADD (empNO NUMBER generated always AS identity (START WITH 1 increment BY 1)); SEGMENT CREATION DEFERRED; alter index invisible ``` **Section 1: Configuring the Recovery Catalog** 1. Create an RMAN recovery catalog on the PDBRCAT1 pluggable database on the RCATCDB. Owner must be rc_admin and the password must be RC_ADMIN. ``` create catalog ``` 2. Register the PRODCDB and DEVCDB databases with the recovery catalog. ``` 先升后注 upgrade database; register catalog ``` **Section 2: Recovery and Duplication** 1. Users reported that the PROD5 database is unavailable. Identify the cause and take appropriate recovery actions so that you can connect with the net service name PROD5. ``` failure ``` 2. Create a PDB named PDBPROD2_DEV on the DEVCDB container database using the RMAN duplication feature. Use the PDBPROD2 pluggable database which is in active mode on the PRODCDB container database as the source. Do not create a separate auxiliary instance. Perform this task on the DEVCDB directly. ``` 设置remote参数 目录 Duplicating pdbs to ... active section size 400M ``` **Section 3: Backups** 1. Make sure the following requirements are fulfilled: - Configure RMAN, so that backup taken for the database PRODCDB should be retained for 14 days. Files having no changes since the last backup are not backed up. Backup sets written to disk using one backup command should use 4 channels. Archived logs should be eligible for deletion after backed up 2 times to disk. ``` recovery wonodow of 14 days; ``` 2. Create a backup as a backup set, with TAG PRODCDB_BKP, use these criteria: a. It should include a backup of the PDBPROD1 database. It must be compressed. b. It contains all files needed to perform complete recovery if any media failure occurs. c. It must form the baseline for an incremental backup strategy. ``` in le 1 ``` 3. The backup of EXAMPLE tablespace in the PDBPROD1 database takes a long time. To speed up the backup process, you decide to backup the tablespace in parallel. Set the maximum file size to 30M, and ensure that the backup information is written in the catalog. ``` section size tablespace pdbprod1:example ``` 4. Reduce the overheads of incremental backups to a minimum on the PRODCDB database. ``` alter database enable block change tracking using file '/home/oracle/backup/track.f' ``` 5. Multiplex the redo logs for all groups in the PRODCDB database to the location /u01/app/oracle/oradata/MREDO. ``` to group ``` 6. Configure the database to reuse a reusable record in the control file after 14 days in the PRODCDB database. ``` control ``` **Section 4: Configuring Flashback** 1. In the PRODCDB container database, enable Flashback Database. Make sure the following requirements are fulfilled: a. Enable Flashback Database on PRODCDB container database. b. Configure a fast recovery area of 20 GB. The fast recovery area should be in the /u01/app/oracle/flash directory on the Database Server. c. Configure Flashback Database for a timeframe of 48 hours. 2. Create the restore point DBRSP1 in the PRODCDB database. The restore point does not age out on the control file. ``` guarantee flashback database; ``` **Part 2: Data Management** **Section 1: Creating an Additional Buffer Cache** 1. In the PRODCDB database, create an additional buffer cache to use with 16 KB blocks. Ensure that this new buffer cache is always available. ``` 15M ``` **Section 2: Creating a Plug-in Tablespace and External Table** 1. Plug in the transportable tablespace, TRPDATA, into the PDBPROD1 pluggable database, and bring it online. The data TRPDATA6, which is being transported, was created on a big-endian platform, Solaris 10 OE 64-bit. The TRPDATA6 data file and the trans2_2.dmp dump file are located in /home/oracle/scripts. The exported user is SST. The plug-in tablespace objects should belong to the SST user. ``` 7804 ``` 2. In the scripts directory, you will find sales_2018.dat.gz, sales_2019.dat.gz, and sales_master.ctl. Using the information found in the files, create an Oracle Loader external table in the schema in the PDBPROD1 database that must run a preprocess to manipulate data. This external table must be partitioned and meet the following requirements: a. Partitioning key is the TIME_ID column. b. Use the following partition information: - year2018 VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY')) - year2019 VALUES LESS THAN (TO_DATE('31-12-2019', 'DD-MM-YYYY')) c. Partition "year2018" should correspond to sales_2018.dat.gz and partition "year2019" should correspond to sales_2019.dat.gz. ``` 先创建sqlldr入 log建表 修改时间格式 预处理脚本 location改在分区中 ``` **Section 3: Optimizing Space Usage for the LOB Data and Encrypted Tablespace** 1. In the PDBPROD1 database, create a table to optimize space usage for multiple rows that can have the same LOB data and compress the LOB data. ``` db_se =p 文档develop filese 建表 lob com 高压缩 compress hight 去重 dedu 创建dir 授权多一个执行 execute exec执行存过 ``` 2. In the PDBPROD2 database, create an encrypted tablespace using the following specifications: a. Tablespace name: ENCT_DATA b. File size: 50 MB c. Enable data file auto extend, set the minimum size of incremental to 10 MB and the maximum to 1 GB. d. Use 'welcome' as the password for the keystore. ``` 文档 admin sql lan 搜admin key 文档 admin guide 搜encry 本地管理在附近 default storage move到新表空间 ``` **Section 4: Administering Partitioning** 1. In PDBPROD1, create a partitioned table SH.NEW_ORDER_ITEMS. Use the SH.NEW_ORDERS table as the basis for partitioning the NEW_ORDER_ITEMS table. Use PART_TBS1 and PART_TBS2 tablespaces for the partitions. 2. In PDBPROD1, create a partitioned table SH.SALES_HISTORY_2014 with these columns. ``` 文档 data ware interval-R 创建表空间 -P ``` **Section 5: Configuring the Database to Retrieve Previous Versions of the Table Rows** 1. Configure the PDBPROD1 pluggable database so that all previous versions of rows for the past year in the SH.PROMOTIONS table are retrievable. Create and use the following tablespace and non-DBA user. ``` develop 搜 flashback_te 再搜archive 创建用户表空间 创建恢复 授权给sh sh 修改表 ``` **Section 6: Optimize Star Queries** 1. Configure the database instance so that the query in /home/oracle/scripts/2_6_1.sql persistently uses the star transformation optimizations in PDBPROD1. Ensure that the changes do not affect any other PDBs in PRODCDB. ``` star 开启参数 脚本的from表 where列都创建bitmap索引 ``` **Section 7: Materialized Views** 1. Configure the PDBPROD1 pluggable database such that query rewrites are always done against all materialized views in all cases where the materialized views are consistent with the underlying data, even if constraints on the tables are not verified. Ensure that the changes do not affect any other PDBs in PRODCDB. ``` 参数 write都改为true ``` **Section 8: Auditing** 1. Create users who can maintain the audit configuration and audit trail. This user is a non-DBA user and configures auditing only on the PDBPROD2 database. Username should be SECUSER. ``` 创建账号 授权 conncet,resource,audit any,audit table,audit system,audit_admin,audit_viewer to SECUSER ``` 2. Create an audit policy named PDBPROD2_POL. This audit policy audits for: a. CREATE TABLE b. Using Datapump import c. Selecting and deleting HR Users from the EMPLOYEES Table d. Do not collect indirect user activity (monitor direct user activity only). ``` 文档 SECUSER 搜 'SECUSER policy' exaple相关的 下面19 设置only ``` 3. Enable the PDBPROD2_POL policy to record only when these actions are successful. ``` 都在上面地下 启动审计 wheneve succ ``` **Section 9: Configuring Additional Options** 1. In PDBPROD1, create a table HR.EMPLOYEES2 that is a copy of the HR.EMPLOYEES table. Add a column EMPNO to HR.EMPLOYEES2 so that inserts of new rows cause a value to be automatically generated for that column. Explicit insertion of data into the column is not permitted. ``` create table HR.EMPLOYEES2 select * from HR.EMPLOYEES; ALTER TABLE EMPLOYEES2 ADD (empNO NUMBER generated always AS identity (START WITH 1 INCREMENT BY 1)); ``` 2. In PDBPROD1, reclaim unused space from the sparsely populated table HR.EMP without moving the entire segment. Any changes you need to make to the table properties should be permanent changes. ``` alter table emp enable row movement. 方案-数据库对象-方案=HR 收缩段 ``` 3. In PDBPROD1, create a table HR.T2 with the following columns. ``` CREATE TABLE hr.t2 (c1 NUMBER(4), c2 VARCHAR2(10)) SEGMENT CREATION DEFERRED; ``` 4. Ensure that the index INVENTORY_IX on the table OE.INVENTORIES in PDBPROD1 is maintained for any DML operation and is not used by the optimizer unless explicitly specified as a hint. ``` ALTER INDEX oe.INVENTORY_IX INVISIBLE; ```
上一篇:
M1原题
下一篇:
M3原题
0
赞
10 人读过
新浪微博
微信
腾讯微博
QQ空间
人人网
DBLOG
admin