数据库管理-第八十六期 19c OCM之路-第一堂（01）（20230628）

第八十六期 19c OCM之路-第一堂（01）

经过2天的努力，先把host01和host02的基础环境准备好，由于RAC我觉得没啥难度，所以host03和host04就放到后面去弄。先根据考纲对host01、host02上的的考试内容过一下。这里我争取是考点+模拟考题（从11g、12c考试经验和19c知识点出发）+难点or复杂内容官方文档位置来过每个知识点。

1 环境补充说明

• 操作系统及软件
  

• 主机信息
  

• 密码信息
  

2 第一堂-01

因为这里还没准备完善，因此今天只会过部分考点，所有考点信息还是查阅上一期的网址。
SkillSet 1: General database and network administration
数据库通用及网络管理：

考点1：Create a pluggable database 创建PDB

这里不止是创建一个PDB，而是多个，但是19c增加了Application Conteiner，所以有些曾经12c放在这里的考点会移到Application Container那部分去，但是这里还是按照12c的标准尽可能全覆盖一下：
在PRODCDB中创建PDB：
1 通过复制pdbprod1的方式创建PDB pdbprod3，并要求不适用触发器使PDB能随CDB启动：

create pluggable database pdbprod3
from pdbprod1
file_name_convert=('/pdbprod1/','/pdbprod3/');

alter pluggable database pdbprod3 open;
alter pluggable database pdbprod3 save state;

2 创建PDB pdbprod4并包含DB11G中HR,OE,SH,BI的数据。完成后保持DB11G可用：
这里需要先把tnsname写了：

$ORACLE_HOME/network/admin/tnsname.ora
PRODCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODCDB)
    )
  )

PDBPROD1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBPROD1)
    )
  )

PDBPROD2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBPROD2)
    )
  )

PDBPROD3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBPROD3)
    )
  )
PDBPROD4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBPROD4)
    )
  )

PDBPROD5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBPROD5)
    )
  )

PROD4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD4)
    )
  )

PROD5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD5)
    )
  )

DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G)
    )
  )

EMREP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EMREP)
    )
  )

解题操作如下：

db11g --详见上一期环境变量说明
sqlplus sys/oracle@db11g as sysdba
	create directory ext as '/home/oracle/scripts';
	grant all on directory ext to public;
expdp system/oracle SCHEMAS=hr,or,sh,bi dumpfile=db11g.dmp directory=ext

sqlplus sys/oracle@prodcdb as sysdba
	create pluggable database pdbprod4
	admin user admin identified by oracle
	file_name_convert=('/pdbseed/','/pdbprod4/');
	alter pluggable database pdbprod4 open;

	alter session set container=pdbprod4;
	create directory ext as '/home/oracle/scripts';
	grant all on directory ext to public;
	create tablespace users datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD4/users01.dbf' size 10m autoextend on;
	create tablespace example datafile '/u01/app/oracle/oradata/PRODCDB/PDBPROD4/example01.dbf' size 10m autoextend on;

impdp system/oracle@pdbprod4 dumpfile=db11g.dmp directory=ext schema=hr,oe,sh,bi

3 通过PROD4创建pdbprod5（使用plugin的方式）

sqlplus sys/oracle@prod4 as sysdba
shut immediate
startup mount
alter database open read only;
alter system enable restricted session;
exec dbms_pdb.describe('/home/oracle/scripts/prod4topdb.xml');

mkdir /u01/app/oracle/oradata/PRODCDB/pdbprod5
sqlplus sys/oracle@prodcdb as sysdba
	set serveroutput on

	declare
		rtn boolean;
	begin
		if DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/scripts/prod4topdb.xml') then
			dbms_output.put_line('ok');
		else
			dbms_output.put_line('fail');
		end if;
	end;
	/ --检查可忽略

	select name,message,action from pdb_plug_in_violations;
	alter system set pdb_file_name_convert='/u01/app/oracle/oradata/PROD4','/u01/app/oracle/oradata/PRODCDB/pdbprod5';
	create pluggable database pdbprod5 using '/home/oracle/scripts/prod4topdb.xml';
	start ?/rdbms/admin/noncdb_to_pdb.sql

考点2：Create tablespace 创建表空间

这个考点相对就简单了，主要是需要到指定的数据库或PDB执行相关操作，这里就可以用以下两种方式到指定的数据库或PDB：

sqlplus sys/oracle@pdbprod5 as sysdba

prodcdb
sqlplus / as sysdba
	alter session set container=pdbprod1;

db11g
sqlplus / as sysdba 

这里不指定大概题目，就带几个命令（不包含切库操作）：

create bigfile tablespace tbstest datafile '/u01/app/oracle/oradata/PRODCDB/pdbprod2/tbstest01.dbf' size 100m autoextend on maxsize 2T;

create tablespace example datafile '/u01/app/oracle/oradata/PRODCDB/pdbprod4/example01.dbf' size 10m autoextend on;

create bigfile temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/PRODCDB/pdbprod3/temp1.dbf' size 400m autoextend on maxsixe 4T uniform size 1m tablespace group temp_grp;
create bigfile temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PRODCDB/pdbprod3/temp2.dbf' size 400m autoextend on maxsixe 4T uniform size 1m tablespace group temp_grp;
alter database default temporary tablespace temp_grp

考点3：Managing undo 管理undo

由于从12.2开始，local undo的引入，PDB可以管理自己的undo表空间，说白了还是读题看调整哪的undo：

sqlplus sys/oracle@pdbprod1 as sysdba
	alter system set undo_retention=5400;
	alter database datafile x autoextend on;
	alter tablespace undotbs1 retention guarantee;

总结

这一篇试个水，看看反应，下一篇把第一堂内容更新完。
如有错漏请及时联系。
老规矩，知道写了些啥。