DBLOG
» WTF
Toggle navigation
DBLOG
主页
OCM
1、概览
2、数据导入导出
3、GaussDB(DWS)数据库管理
4、数据库调优与开发实践
5、湖仓一体
6、开发应用
7、集群管理
8、巡检和维运维
About Me
归档
标签
M1原题
无
2025-03-13 11:34:03
8
0
0
admin
``` lock 在最底下 cdb创建、*pdb*设置（语句sta） 克隆 新建用户 要授权（搜下） file_name_convert 热转移 convert ``` ### Module 1: Manage Container Database #### PART 1: Manage Container Database #### Section 1: Creating Pluggable Databases: 1. **Create a pluggable database PDBPROD3 in PRODCDB. Use the following specifications.** a. The PDBPROD3 must be a copy of the PDBPROD1 database and must be opened for updates. ``` ``` 2. **Create pluggable databases PDBPROD4 and PDBPROD5 in PRODCDB. Use the following specifications:** a. Import application schemas HR, OE and BI from the DB12C non-CDB database into the PDBPROD4 pluggable database in the PRODCDB container database. ``` ``` b. Clone the Oracle 19c non-CDB database PROD4 into the PRODCDB container database as the pluggable database PDBPROD5. ``` ``` 3. **Create the following users:** a. USER1, with the following specifications: i. USER1 must be defined in PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5. ii. USER1 must also be defined in any future PDB in PRODCDB. iii. Adjust the name of the user, USER1, if required. ``` ``` b. USER2, which is defined only in the PDBPROD1 database. ``` ``` 4. **Create the following roles:** a. ROLE1, with the following specifications: i. It is defined in PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5. ii. ROLE1 must also be defined in any future PDB in PRODCDB. iii. Adjust the name of the role, ROLE1, if required. iv. Make ROLE1 the default role of USER1. ``` ``` b. ROLE2, which is defined only in database PDBPROD1. Users must be authorized by password before this role is enabled. ``` ``` 5. **Grant privileges and roles to users and roles. Use the following specifications:** a. Allow USER1 the privilege to log in to PDBPROD1, PDBPROD2, PDBPROD3, PDBPROD4, and PDBPROD5, and any other pluggable databases that may be created in the future. And USER1 will have create any table privilege limited to PDBPROD2. ``` ``` b. Allow USER2 the privilege to login to PDBPROD1. ``` ``` c. Modify ROLE1 role such that it has the privilege to create a stored procedure in all the current as well as future pluggable databases of the PRODCDB container database granted via the CR_PRO_ROLE role. Adjust the name of the role CR_PRO_ROLE, if required. ``` ``` d. Allow USER2 to create a trigger via role CR_TRG_ROLE in PDBPROD1 and allow USER2 to grant CR_TRG_ROLE to other users. ``` ``` #### Section 2: Creating and configuring Tablespaces 1. In the PDBPROD1 database, create a temporary tablespace group named TEMP_GRP to support batch processing, the creation of large indexes, and analysis of tables. The group should contain the temporary tablespaces TEMP1 and TEMP2. Make TEMP_GRP the default temporary tablespace for all new users and all existing users assigned to the current default temporary tablespace. ``` admin 第一个文档 搜 group ``` 2. In the PDBPROD2 database, create a permanent tablespace to store sample test data. Use the following specifications: a. Tablespace name: OLD_DATA_TBS b. Tablespace will only have one datafile for storing sample test data. c. Initial data file size: 500 MB d. The file can be expected to grow up to 1 TB. ``` bigfile ``` 3. In the PDBPROD1 database, create a permanent tablespace to store customer data. Use the following specifications: a. Tablespace name: CUST_DATA b. File size: 40 MB c. The tablespace will use uniform extent size with 5 MB. ``` extent management local uniform size ``` #### Section 3: UNDO Set Up and Management 1. Configure automatic undo management in the PRODCDB database to support the following requirements. Make sure that queries up to 90 minutes long can run and get read consistent versions of rows without any errors. ``` ``` 2. Change the configuration of the DEVCDB database so that there is an UNDO tablespace in each PDB. ``` startup upgrade alter database local undo on ``` #### Section 4: Creating and Managing Application Containers 1. Create an application container on the DEVCDB container database with the following specifications: a. The application root is named DEVAPP_ROOT. b. Create the application PDBs that belong to the application root as DEVAPP_PDB1. c. Open the application container that you created with read/write mode. ``` 文档 muliti application root 创建applicate后在里面创建pluggable DEVAPP_PDB1 ``` 2. Install the application in the application root that you created with the following specifications: a. Application Name: DEV_APP b. Application Version: 1.0 c. The application you are installing should run "dev_app4-2.sql." 3. Synchronize the application installed in the application root to the application PDB. ``` 创建应用都在上面文档下面 ``` 4. The DEVCDB container database has the application root container named HR_ROOT. The following applications are installed: a. Common user of the application container: the hr_admin (password is oracle_4U). b. Common objects of the application container: the EMP table and the DEPT table, which are METADATA-LINK objects, in the hr_admin schema. c. Application name: hr_app (version 1.0). **Configure the application container to meet the following requirements:** a. Open all the application PDBs (SALES, ACCOUNTING, RESEARCH) associated with HR_ROOT application root container. They should be in read/write mode. b. Connect as the hr_admin user to the HR_ROOT application root container and run the "dev_app4-3-2.sql" script to store data in the EMP and DEPT tables. c. The EMP and DEPT tables store data separately for each application PDB. Create a container map so that it is properly routed to the relevant application PDB when a query is performed. Use the map table name MAPTABLE and the DEPTNO column for the partitioning key. d. Configure to enable the container map you created to work together with the queried table. Also, configure the HR_ROOT application root container so that the table can be queried without the CONTAINERS clause. ``` 创建用户是c## 切换到hr_admin用户 hr_root库 执行脚本 注意 创建表需要sys 上面文档 搜partition 带有range 创建分区表 表名在题目里 下面的map是路由 升级应用 内容是enable 和default 对应两个表 共4个 enable 两个表 ``` #### Section 5: Server-Side and Client-Side Network Configuration 1. Perform the following client-side network configuration tasks on the 19c database server. Provide connect descriptors for your databases by using the local naming method. Use fully qualified hostnames. a. The PDBPROD3 alias should connect to PDBPROD3 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection. b. The PDBPROD4 alias should connect to PDBPROD4 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection. c. The PDBPROD5 alias should connect to PDBPROD5 by using the default listener LISTENER with TCP/IP port 1521, and should always use a dedicated server connection. ``` tns添加 ``` 2. On the 19c Database Server, create an additional listener LSNR2 by using port 1525. Create PROD_S alias for shared server connections to the database PDBPROD1 using listener LSNR2. ``` listner添加 tns添加 数据库注册 ``` #### Section 6: Configuring the Container Database for Shared Server 1. Configure the PRODCDB database to support up to 500 sessions reserving 100 for dedicated connections. 2. Configure the PRODCDB database to support a minimum of 3 TCP dispatchers and a maximum of 10. ``` dis DIS=3 ``` 3. Configure the PRODCDB database to support a minimum of 10 shared server processes and a maximum of 50 shared server processes. #### Section 7: Configuring the Control File 1. Create a third copy of the control file, control03.ctl, for the PRODCDB container database in $ORACLE_HOME/dbs/. The original control files must not be renamed or moved. ``` cp /u01/app/oracle/oradata/PRODCDB/control01.ctl $ORACLE_HOME/dbs/control03.ctl alter system set control_files='/u01/app/oracle/oradata/PRODCDB/control01.ctl','/u01/app/oracle/fast_recovery_area/PRODCDB/control02.ctl','$ORACLE_HOME/dbs/control03.ctl' scope=spfile; reboot ``` #### Section 8: Managing Pluggable Databases 1. Change the following initialization parameter settings on the PRODCDB container database. a. Set the maximum number of cursors that can be opened simultaneously in a session to 500. b. The maximum number of cursors must be set to PDBPROD1 only. c. Set the settings to persist across database restarts. ``` show parameter cursors current ``` 2. Enable the lockdown profile to limit operations at the PDB level. Create a lockdown profile with the following rules: a. The lockdown profile name is PROD_PROF1. b. Do not allow ALTER SYSTEM statements. ALLOW ALTER SYSTEM SET statements. c. PROD_PROF1 should affect only PDBPROD2. ``` lockfile sys创建 pdb中实现 ``` 3. Create a clone of PDBPROD1 which is in PRODCDB. The clone which you create should be on DEVCDB. Use the remote cloning feature with hot mode. Perform with the following specifications: a. The name of the clone which you create is PROD1_HOTCLONE. Enable its data to be refreshed every 5 minutes from the source pluggable database. b. The state of the PDB which you created should be in an instance mode that is able to refresh. ``` 关键字switch ....refreshe ``` 4. Move a PDB in the PRODCDB to the DEVCDB using near-zero downtime PDB relocation. Confirm the following to relocate the PDB: a. Run the relocate8-4.sh script under /home/oracle/scripts. This script starts RELOCATE_PDB1 on PRODCDB and creates a table named BIGTAB on RELOCATE_PDB1. After that, this script inserts data to the BIGTAB table. b. Move RELOCATE_PDB1 to the DEVCDB database using the near-zero downtime PDB relocation feature. Use AVAILABILITY NORMAL mode, and use the same name for the relocated PDB. c. Open the RELOCATE_PDB1 on the DEVCDB with read/write mode. ``` 关键字relocat dblink指定prodcdb ```
上一篇:
9、审计日志
下一篇:
M2原题
0
赞
8 人读过
新浪微博
微信
腾讯微博
QQ空间
人人网
DBLOG
admin