OCM核心内参(OCM考试题库).pdf

【实例简介】
OCM核心内参(OCM考试题库).pdf
、考试题库及讲解 (一) Section0:创建数据库(即于动建斥)【在db机操作】 1、手动建库 1)确定实例名。 export ORACLE SID=test 2)建立密码文件。 orapwd file-orapwtest password=oracle 3)建立 filed cat init. ora grep -v# grep -v$ >inittestora 在 unittest.ora里只包含如下初始化参数: db name=test processes=150 controlfiles-(XXXXXX, XXXxxx) db block size=8192 log buffer=50000000 sga target=300m undo management=auto undo tablespace=undotbs background dump dest core dump dest user dump dest 4)建立 spfile并启动实例 sq1>sqlplus/ as sysdba sql>create spfile from pfile sql>startup nomount 5)于工创建数据库(使用 create database命令) CREATE DATABASE mynewdb LSER SYS TDENTTFTED BY pz6ra8 LSER SYSTEM IDENTIFIED BY yItz5p LOGFILE GROUP 1 (/u01/oracle/oradata/mynewdb/redoO1 1og)SIZE 100M GROUP 2(/u01/oracle/oradata/mynewdb/ redo02 1og,)SIZE 100M GROLP 3(/uOl/oracle/oradata/mynewdb/redo03 log) SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET USTASCII NATIONAL CHARACTER SET ALI6UTF16 DATAFILE/u01/oracle/oradata/ mynewdb/systemO1 dbf SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE/u01/oracle/oradata/ mynewdb/ sysaux0l dbf SIZE 325M REUSE DEFAULT TABLESPACE tbs_1(在考试时去掉这条语句) DEFAULT TEMPORARY TABLESPACE tempts TEMPFILE/u01/ oracle/oradata/ mynewdb/temp01. dbf SIZE 20M REUSE LNDO TABLESPACE undotbs DATAFILE/u01/oracle/oradata/mynewdb/undotbs01 dbf SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNL IMITED 6)运行相关脚本 CONNECT SYS/password AS SYSDBA @/u01/oracle/rdbms/admin/catalog. sql @/u01/oracle/rdbms/admin/catproc sql @ ?/rdbms/ admin/catblock sql @?/rdbms/admin/ catoctk sql /rdbms/admin/owminst plb connect system oracle @?/sqlplus/admin/pupbld sql 2、 section0阶段考试需要注意的具体事项 1)按题目要求设置“字符集” 2)有5块磁盘,建库建在指定位置,一般都是建在文件系统上。 3)考试的结果是美国那边直接用脚本连到你的服务器上,通过数据字典抓取结果,然后判 断你哪些错了,所以,一定要保证在最后的时侯,数据库是OPEN的,监考老师说还有5分 钟结東考试的时候,不要再做什么其它操作了。如果之前你创建完数据库,想要 shutdown 以后冷备下的话,请估算好时间。我听到的案例是冷备刚完成,数据库还木 startup,结 果美国那边廾始抓取考试结果,因为无法连接数据库,因此第一人上午的 section得了零分。 4)为了减省时间,如果题目里没有明确要求备份,可以先不备份。 5)如果有时间,用root用户修改/etc/ hosts文件,使该文件里包括2个节点的主机名和 相应的ip地址。 3、参考的联机文档 1, administrator's guide->2 creating a oracle database-> Manually Creating an Oracle database (二) Section 1:数据库和网络的配置【在db机操作】 1、数据库配置(管理表空间) 有如下考题需要注意: Create a temporary tablespace group that contains two temporary tablespaces to support batch process about creation of large indexes, and analyzing tables. Use the following specification 1, Temporary tablespace group named TEMP GRP containing temporary tablespaces TEmP1 and TEMP2 2, Make TEMP GrP the default temporary tablespace for all new users 答案: create temporary tablespace templ tempfile/u01/app/oradata,templ. dbf size 50m tablespace group TEMP grP create temporary tablespace temp2 tempfile/uol/app oradata/temp2. dbf' size 50m tablespace group TEMP GRP alter database default temporary tablespace TEMP GRP select from database properties E Create a permanent tablespace to store sample test data. Use the following specification 1 Tablespace name of EXAMPle 2 Initial datafile size of 400MB with the file expected to grow to 4GB 3 Initial extent size of imb 4 Next extent size of lmb 答案: create tablespace example datafile xxxxxxx size 400m autoextend on maxsize 4, extent management local uniform size Im Create a permanent tablespace to store indexes. Use the following specification 1, Tablespace name of INDX 2、 File size of40MB 答案 create tablespace indx datafile size 1or yy, Create a permanent tablespace to store data collected from the various Oracle tools Use the following specification 1, Tablespace name of tools 2、 File size of48MB 3 Initial extent size of 4MB 4 Next extent size of 4MB 答案 create tablespace tools datafile '.,. size 48m extent management local unifor SIze 4m; A, Create a permanent tablespace for storing segments associated with onl ine transaction processing with high insert rates due to the potential high volume of concurrent inserts, every effort should be taken to reduce contention such of the tables that will be stored in this tablespace. Use the following specification I, Tablespace name of OLTP 2、 File size of48MB 3 Initial extent size of 2MB Next extent size of 2MB 答案 create tablespace oltp datafile '.. size 48m extent managemnet local uniform size 2m segment space management auto A Due to the expected high volumn of transaction, the database should have the following configuration: A minimum of 5 redo log groups 2, Each redo log group should not be a single point of failure 3、 File size of100MB 4, Specify the Location such that it minimizes contention and reduces the risk of a single point of failure case of disk drive failure 答案: Sql>ALTEr DATABASE ADD LOGFILe GROUP 1(/u01/app oradata/test/loga log /u01/app/ oradata/test/log1b log) SIZE 100m, group (‘/u01/app/ oradata/test/log5a.log’,’/u0l/apo/ oradata/test/log5b.1og’)size 100m: sql>alter database add logfile member/u01/app/oradatashuai/redolb log to group 注意:日志组的2个 member应该分布在2个不同的磁盘上。 t Triplex the controlfile to minimize recovery in case of disk drive failure 答案: 将数据库关闭,然后拷贝控制文件到目标路径,然后改pie中的 control参数,重新 生成 spfile,最后启动数据 A\ As user Sys, run the script /home/oracle/scripts/ create bishhr sql. Ignore any errors concerning OE. But do not ignore any other errors Investigate the parameter file for reasonable size for each parameter listed. Add additional parameters as you deem necessary to support an optimal database environment. In addition, modify or add the fol lowing listed parameters UTL FILE DIR=(/home/oracle, /home/oracle/temp, /home/oracle/scripts) Note: Applications that use Oracle10g features will be running therefore, ensure the database and instance are appropriately configured 答案 主要是根据题目要求调整数据库的初始化参数。 fu Backup the database to prepare for complete recovery under all circumstances 答案 给数据库做一个冷备,可以使用操作系统命令直接进行拷贝,最后记着 Open the database. 十、管理临时表空间组及更改默认的临时表空间 些相关的命令: ALTER TABLESPACE Imt emp TABLESPACE GROUP 指 mtemp3不属于任何衣空间组 alter database default tablespace tbsl select *k from database properties 2、网终配置 ■例题 如何配置一个非默认端口的监听,并启动它。 答案: 第一,配置 listener.ora文件 LISTENER (DESCRIPTION- (ADDRESS LIST (ADDRESS=(PROTOCOL =tcp(HOSTsale-server)(PORT=1421)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) SID LIST LISTENERE (SID LIST= (SID DESC= (GLOBAL DBNAME=Sales. us. acme. com (ORACLE HOME/ oracle10g) (SID NAME=Sales)) (SID DESC (SID NAME-pIsextproc) (ORACLE HOME=/oracle10g) (PROGRAM-extproc)) 第二,配置 tnsnames.ora文件 listener (DESCRIPTION (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT1421)) (CONNECT DATA=(SERVICE NAME=Sales. us. acme. com)) 第三,配置初始化参数 LOCAL L⊥ STENER alter system set LOCaL Listener=listener scope=both 第四,启动监听。 lsnrctl start 如何配置共享服务器模式。 答案 第一,设置一些初始化参数。 alter system set dispatchers=(PROTOCOL=TCP)(DISPATCHERS=2) scope=both alter system set shared servers=5 scope=both alter system set max shared servers=10 scope=both alter system set max dispatchers=5 scope=both 第二,在 tnsnames.ora文件加入如下内容: shual (DESCRIPTION (ADDRESS=(PROTOCOL=tcp)(HOst=sales-svr)(PORT=1421) (CONNECT DATA=(SERV ICE NAME=Shuai)(server=shared)) 第三,仗用 system用户连接数据库,方式如下 sqlplus system/oracle(shuai 第四,查看 system用户的连入方式。 ⊥ snrctl service 3、 section l阶段考试需要注意的具体事项 无 4、参考的联机文档 1administrator's gui de->8 Managing Tablespaces-> Creating Tablespaces 2)administrator's guide>6 Managing the Redo Log >Creating Redo Log Groups and Members 3)Net Services Reference->7 Listener Parameters (listener.ora 4)Net Services Reference->6 Local Naming Parameters (tnsnames. ora) 5)administrator's guide-> 4 Managing Oracle Database Processes-> Configuring Oracle database for Shared Server 6) SQL Reference->11 SQL Statements: CREATE CLUSTER to CREATE JAVA-> CREATE DIRECTORY 7)Net Services Administrator s Gui de->10 Configuring and Administering the Listener->Registering information with a Nonde fault Listener (三) Section2: Gridcontrol安装配置【在 gridcontrol 机上安装 gridcontro软件,在db机创建 gridcontrol要使用的资 料库】 1、 girdcontrol的安装 安装步骤如下 【实例截图】
【核心代码】