ORACLE11GOCM考试考点练习.doc

ORACLE11G OCM考试考点练习1 奇数机考试操作内容1.1 该机规划虚拟机名称：edgzrip1_Oracle Enterprise Linux虚拟机位置：D:ocmedgzrip1主机名称：edgzrip1ip地址：192.168.3.1011.2 考试要求手工创建数据库，ORACLE_SID= prod字符集相同，prod和omr字符集必须相同，都用AL32UTF81.3 【Section0】Create the database45分钟，在40分钟内完成。1.3.1 Create the database手工创建数据库1.3.1.1 到考场准备内容1.3.1.1.1 检查机器是否正常1、键盘；2、鼠标；3、复制是否正常；1.3.1.1.2 检查环境变量1、 ORACLE_HOME2、 ORACLE_SID3、 cat /etc/hosts4、 ORACLE目录1.3.1.1.3 把doc文档打开1、 在firefox打开doc文档，定位到在【Administrator's Guide】-【2 Creating and Configuring an Oracle Database】-【Step 9: Issue the CREATE DATABASE Statement】2、 pdf在新的窗口打开【Administrator's Guide】pdf文档，定位关键字【create database statement】2.1.复制建库的语句2.2.复制参数文件语句3、 pdf在新的窗口打开【Net Services Administrator's Guide】pdf文档，定位关键字【tnsnames.ora sample】1.3.1.1.4 打开1个窗口复制内容1、 把参数文件内容复制2、 把建库文件内容复制3、 复制tnsnames.ora语句1.3.1.2 Create the database打开1个窗口执行下面操作：1.3.1.2.1 查看和设置环境变量1、oracleedgzrip1 $ vi .bash_profileexport ORACLE_SID=prodexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_12、使参数生效oracleedgzrip1 /$ su - oracle1.3.1.2.2 生成口令文件oracleedgzrip1 dbs$ cd $ORACLE_HOME/dbsoracleedgzrip1 dbs$ orapwd file=orapwprod password=oracle entries=51.3.1.2.3 生成参数文件oracleedgzrip1 dbs$ vi initprod.oradb_name=prodmemory_target=1Gprocesses = 150audit_file_dest='/u01/app/oracle/admin/prod/adump'audit_trail ='db'db_block_size=4096db_domain=''db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'db_recovery_file_dest_size=2Gdiagnostic_dest='/u01/app/oracle'open_cursors=300remote_login_passwordfile='EXCLUSIVE'undo_tablespace='UNDOTBS1'undo_management=AUTOcontrol_files = ('/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl')compatible ='11.2.0'【注意从复制修改：】1、增加：undo_management=AUTO2、去掉：dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'3、修改控制文件位置1.3.1.2.4 创建目录oracleedgzrip1 dbs$ mkdir -p /u01/app/oracle/admin/prod/adumporacleedgzrip1 dbs$ mkdir -p /u01/app/oracle/flash_recovery_areaoracleedgzrip1 dbs$ mkdir -p /u01/app/oracle/oradata/prodoracleedgzrip1 dbs$ mkdir -p /u01/app/oracle/oradata/prod/disk1oracleedgzrip1 dbs$ mkdir -p /u01/app/oracle/oradata/prod/disk2oracleedgzrip1 dbs$ mkdir -p /u01/app/oracle/oradata/prod/disk31.3.1.2.5 运行建库脚本1、sqlplus登陆oracleedgzrip1 dbs$ sqlplus / as sysdba2、进入nomount状态SQL> startup nomount;3、执行建库脚本CREATE DATABASE prod USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prod/disk1/redo01a.log','/u01/app/oracle/oradata/prod/disk2/redo01b.log','/u01/app/oracle/oradata/prod/disk3/redo01c.log') SIZE 10M, GROUP 2 ('/u01/app/oracle/oradata/prod/disk1/redo02a.log','/u01/app/oracle/oradata/prod/disk2/redo02b.log','/u01/app/oracle/oradata/prod/disk3/redo02c.log') SIZE 10M, GROUP 3 ('/u01/app/oracle/oradata/prod/disk1/redo03a.log','/u01/app/oracle/oradata/prod/disk2/redo03b.log','/u01/app/oracle/oradata/prod/disk3/redo03c.log') SIZE 10M MAXLOGFILES 16 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/prod/system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 102040K MAXSIZE 2048M SYSAUX DATAFILE '/u01/app/oracle/oradata/prod/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/prod/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temp1 TEMPFILE '/u01/app/oracle/oradata/prod/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/prod/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 2048M;1.3.1.2.6 执行脚本【根据考试要求，确定是否执行下列脚本】?/rdbms/admin/catalog.sql【执行时间：3分钟】?/rdbms/admin/catproc.sql【执行时间：8分钟】【！若不执行，RMAN不好用】?/sqlplus/admin/pupbld.sql1.3.1.2.7 生成spfileSQL> create spfile from pfile;1.3.1.2.8 重启数据库【！看时间情况】SQL> shutdown immediate;SQL> startup;看是否使用spfile启动：SQL> show parameter spfile;1.3.1.2.9 配置tnsnames.ora1、启动listeneroracleedgzrip1 dbs$ cd $ORACLE_HOME/network/adminoracleedgzrip1 admin$ lsnrctlLSNRCTL> startLSNRCTL> exit2、修改tnsnames.oraoracleedgzrip1 admin$ vi tnsnames.oraprod = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = edgzrip1 ) (PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = prod ) ) )3、测试能连通oracleedgzrip1 admin$ sqlplus sys/oracleprod as sysdbaoracleedgzrip1 admin$ sqlplus system/oracleprod1.3.1.3 Determine and set sizing parameters for database structures1、 注意表空间数据文件的大小、要求，如：size、next、autoextend、uniform size2、 control file文件的位置3、 logfile的位置4、 字符集统一用AL32UTF81.3.1.4 创建1个表create table ksxx(c1 varchar2(100),n1 number,d1 date) tablespace users;1.3.1.5 最后交卷注意事项提前6分钟。1.3.1.5.1 查看库是否能连上1、oracledma $ sqlplus sys/oracleorcl as sysdba2、oracledma $ sqlplus system/oracleorcl1.3.1.5.2 备份口令文件1.3.1.5.3 备份参数文件1.3.1.5.4 备份数据库1.4 【Section1】Server Configuration考试时间：120分钟1.4.1 准备内容1.4.1.1 把doc文档打开1、【SQL Language Reference】定位关键【create tablespace】2、【Net Services Reference】定位关键字【listener.ora file】3、【Net Services Reference】定位关键字【tnsnames.ora】4、【Net Services Administrator's Guide】-【11 Configuring Dispatchers】1.4.1.2 打开1个窗口复制内容1.4.2 Create and manage temporary, permanent, and undo tablespaces1.4.2.1 undo相关的设置场景：如查询时间很长2个小时，为保证不出错Ora-1555提示SQL> show parameter undoSQL> alter system set undo_retention=7200 scope=both;1.4.2.2 带部分参数条件的表空间1、 建unform size=1m,手工管理的表空间SQL> CREATE TABLESPACE users01 DATAFILE '/u01/app/oracle/oradata/prod/user01.dbf' SIZE 10M autoextend on maxsize 2g EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K segment space management manual;2、 设置user01为缺省的表空间SQL> alter database default tablespace users01;3、 建索引表空间，段自动管理，SQL> create tablespace indx datafile '/u01/app/oracle/oradata/prod/indx01.dbf' SIZE 10M autoextend on maxsize 2g extent management local autoallocate segment space management manual;1.4.2.3 create temporary tablespace and group1、建temp表空间和tmp组SQL> create temporary tablespace temp tempfile '/u01/app/oracle/oradata/prod/temp1.dbf' SIZE 20M autoextend off tablespace group tempgroup;SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/prod/temp2.dbf' SIZE 10Mautoextend off tablespace group tempgroup1;2、指定数据库缺省的temporary表空间：SQL> alter database default temporary tablespace tempgroup1;4、 把temp表空间归到组中SQL> alter tablespace temp1 tablespace group tempgroup;1.4.2.4 对把空间和和临时表空间的应用SQL> create user hr identified by hrdefault tablespace users01 quota unlimited on users01temporary tablespace tempgroup1;SQL> alter user hr temporary tablespace tempgroup;1.4.3 Stripe data files across multiple physical devices and locations1.4.3.1 增加日志组，分布在不同磁盘【关键字】alter databseSQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/prod/disk1/redo4a.log' ,'/u01/app/oracle/oradata/prod/disk2/redo4b.log','/u01/app/oracle/oradata/prod/disk3/redo4c.log') SIZE 10m;1.4.3.2 删除日志组成员SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prod/disk1/redo4a.log'1.4.3.3 增加日志组成员SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/prod/disk1/redo4a.log' TO GROUP 4;1.4.3.4 control file在在数据库创建时，把controlfile分布在不同的磁盘1、 把最新的配置文件生成到pfile中SQL> create pfile='prod2012.ora' from spfile;2、 关闭数据库SQL> shutdown immediate;3、 备份原来的参数文件oracleedgzrip1 dbs$ cp initprod.ora initprod.ora.bak04、 把新生成的参数文件覆盖initprod.oraoracleedgzrip1 dbs$ cp prod2012.ora initprod.ora5、 移动spfileoracleedgzrip1 dbs$ mv spfileprod.ora spfileprod.ora.bak06、 修改参数文件initprod.ora*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/disk1/control02.ctl','/u01/app/oracle/oradata/prod/disk2/control02.ctl'7、 把controfile分别拷贝到disk1/ disk2oracleedgzrip1 prod$ cp control02.ctl disk1oracleedgzrip1 prod$ cp control02.ctl disk2oracleedgzrip1 prod$ cp control02.ctl disk38、 重启数据库SQL> startupSQL> create spfile from pfile;9、 关闭重启数据库SQL> shutdown immediate;SQL> startup1.4.4 Configure the database environment to support optimal data access performance1、 根据给定的脚本修改数据库的参数。2、 修改utl_file_dir参数SQL> alter system set utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts' scope=spfile;3、 重启数据库SQL> startup force;1.4.5 rman非归档模式的全备份数据库1、 新开一窗口2、 关闭数据库RMAN> shutdown immediate;3、 启动到mount状态RMAN> startup mount;4、 设置controfile自动备份RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;5、 备份数据库RMAN> backup database;6、 启动数据库RMAN> startup;1.4.6 Create and manage database configuration files建库脚本。1.4.7 Create and manage bigfile tablespacesSQL> create bigfile tablespace example datafile '/u01/app/oracle/oradata/prod/example01.dbf' SIZE 10M autoextend on maxsize 5t extent management local uniform size 512ksegment space management auto;扩展表空间大小，只能通过扩展此数据文件的大小：SQL> alter database datafile '/u01/app/oracle/oradata/tb05.dbf' resize 16m;1.4.8 Create and Manage a tablespace that uses NFS mounted file system file1.4.8.1 远端数据库配置oranfstab参考文档：【Grid Infrastructure Installation Guide for Linux】-【3.2.8 Enabling Direct NFS Client Oracle Disk Manager Control of NFS】1、 配置位置oracleedgzrip1 /$ cd $ORACLE_HOME/dbs2、 配置文件oranfstaboracleedgzrip1 dbs$ vi oranfstabserver:dnfspath:192.168.3.102export:/u01/app/oracle/dnfsdatamount:/u01/app/oracle/oradata/prod/dnfsdata3、【其它参考】oracleedgzrip1 ora2$ cd $ORACLE_HOME/dbsoracleedgzrip1 dbs$ vi oranfstabserver:192.168.37.129path:192.168.37.129export:/tmp/sharemount:/u01/nfs1.4.8.2 远端数据库修改dnfs使用的类库1、 lib目录oracleedgzrip1 dbs$ cd $ORACLE_HOME/lib2、 建立连接oracleedgzrip1 lib$ ls *odm*oracleedgzrip1 lib$ mv libodm11.so libodm11.so.bakoracleedgzrip1 lib$ ln -s libnfsodm11.so libodm11.so3、 【其它参考】oracleedgzrip1 dbs$ cd $ORACLE_HOME/liboracleedgzrip1 lib$ ls *odm*oracleedgzrip1 lib$ mv libodm11.so libodm11.so.bakoracleedgzrip1 lib$ ln -s libnfsodm11.so libodm11.so1.4.8.3 远端数据库创建表空间1、 重启数据库SQL> startup force;2、 创建表空间SQL> create tablespace tbnfs datafile '/u01/app/oracle/oradata/prod/dnfsdata/tbnfs1.dbf' size 5m autoextend on next 512k maxsize 20m extent management local segment space management auto;3、 查看dnfs状态4、 【其它参考】1、 关闭数据库重启SQL> shutdown immediate;SQL> startup;2、 创建表空间SQL> create tablespace nfs_remote datafile '/u01/nfs/ora2/nfs01.dbf' size 5m autoextend off;3、 检测是否正常SQL> select * from v$dnfs_servers;SQL> select * from v$dnfs_files;SQL> select * from V$DNFS_CHANNELS;4、 删除表空间和文件SQL> drop tablespace NFS_REMOTE including contents and datafiles;1.4.9 Create and manage multiple network configuration filesoracleedgzrip1 admin$ vi tnsnames.oraprod= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=edgzrip1)(PORT=1521) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=prod) ) )OMR= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=edgzrip2)(PORT=1521) ) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=OMR) ) )1.4.10 Create and configure a listener1.4.10.1 新建静态注册增加listener.ora：1、oracleedgzrip1 admin$ vi listener.oraLISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=edgzrip1)(PORT=1521) )SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=prod) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=prod) ) )2、启动和查看状态oracleedgzrip1 admin$ lsnrctlLSNRCTL> reloadLSNRCTL> status1.4.10.2 新建动态注册使用listener11、新建listener1oracleedgzrip1 admin$ vi listener.oraLISTENER1= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=edgzrip1)(PORT=1621) ) )SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=prod) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=prod) ) )2、启动oracleedgzrip1 admin$ lsnrctl start listener13、修改参数SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edgzrip1)(PORT=1621)'【！注意：在做dataguard时需要去掉，否则不成功】4、看状态oracleedgzrip1 admin$ lsnrctl status listener1修改service_names参数可以动态的注册到listener1中：SQL> alter system set service_names=''1.4.11 Configure the database instance to support shared server connections参考位置：【Net Services Administrator's Guide】-【11 Configuring Dispatchers】1、 配置dispatchers调度进程SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=5)' SCOPE=BOTH;2、 配置最大dispatchersSQL> alter system set max_dispatchers=5 scope=both;3、 配置共享服务器进程SQL> alter system set shared_servers=5 scope=both;4、 配置最大共享服务器进程SQL> alter system set max_shared_servers=5 scope=both;5、 配置tnsnames.oraorcl_dis= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dma)(PORT=1521) (CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER=SHARED) )6、 查看oracledma admin$ sqlplus test/testorcl_disLSNRCTL> service以下参考：1.4.11.1 综合场景配置要求：dispatchers=3,最大6个；servers5个，最大servers20;最多有300个session,其中preserve为120个保留给dedicated,如何配置：1、 SQL> alter system set DISPATCHERS='(PROTOCOL=tcp)(DISPATCHERS=3)'2、 SQL> alter system set max_dispatchers=6;3、 SQL> alter system set shared_servers=5;4、 SQL> alter system set max_shared_servers=20;5、 SQL> alter system set sessions=300 scope=spfile;【静态参数】6、 SQL> alter system set shared_server_sessions=180;7、 客户端连接配置oracleedgzrip1 admin$ vi tnsnames.orashareprod= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=edgzrip1)(PORT=1621) (CONNECT_DATA= (SERVICE_NAME=) (SERVER=shared) ) )8、 测试连接oracleedgzrip1 admin$ sqlplus system/oracleshareprod9、 查看状态oracleedgzrip1 admin$ lsnrctl service listener11.4.12 Set up network tracing参考文档：【Net Services Administrator's Guide】-【16 Troubleshooting Oracle Net Services】-【Tracing Error Information for Oracle Net Services】1.4.12.1 listener.ora级别ADR_BASE_LISTENER1 = /u01/app/oracle/product/11.2.0/grid/network/traceTRACE_LEVEL_LISTENER1 = USERTRACE_TIMESTAMP_LISTENER1 = ON1.4.12.2 sqlnet.ora级别TRACE_DIRECTORY_SERVER= /u01/app/oracle/product/11.2.0/grid/network/traceTRACE_LEVEL_SERVER= USER1.4.13 Manage Oracle network processesLSNRCTL> start/stop/services/ set1.4.14 Configure the network environment to allow connections to multiple databases配置：tnsname.ora1.4.15 Use configurationless connections参考文档：【8 Configuring Naming Methods】-【Using the Easy Connect