数据库管理 2023-06-28
第八十六期 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;
总结
这一篇试个水,看看反应,下一篇把第一堂内容更新完。
如有错漏请及时联系。
老规矩,知道写了些啥。