ORACLE11G_OCM考试考点练习.doc

oracle11g ocm考试考点练习1 奇数机考试操作内容1.1 该机规划虚拟机名称：edgzrip1_oracle enterprise linux虚拟机位置：d:ocmedgzrip1主机名称：edgzrip1ip地址：011.2 考试要求手工创建数据库，oracle_sid= prod字符集相同，prod和omr字符集必须相同，都用al32utf81.3 【section0】create the database45分钟，在40分钟内完成。1.3.1 create the database手工创建数据库 到考场准备内容.1 检查机器是否正常1、键盘；2、鼠标；3、复制是否正常；.2 检查环境变量1、 oracle_home2、 oracle_sid3、 cat /etc/hosts4、 oracle目录.3 把doc文档打开1、 在firefox打开doc文档，定位到在【administrators guide】-【2 creating and configuring an oracle database】-【step 9: issue the create database statement】2、 pdf在新的窗口打开【administrators guide】pdf文档，定位关键字【create database statement】2.1.复制建库的语句2.2.复制参数文件语句3、 pdf在新的窗口打开【net services administrators guide】pdf文档，定位关键字【tnsnames.ora sample】.4 打开1个窗口复制内容1、 把参数文件内容复制2、 把建库文件内容复制3、 复制tnsnames.ora语句 create the database打开1个窗口执行下面操作：.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 - oracle.2 生成口令文件oracleedgzrip1 dbs$ cd $oracle_home/dbsoracleedgzrip1 dbs$ orapwd file=orapwprod password=oracle entries=.3 生成参数文件oracleedgzrip1 dbs$ vi initprod.oradb_name=prodmemory_target=1gprocesses = 150audit_file_dest=/u01/app/oracle/admin/prod/adumpaudit_trail =dbdb_block_size=4096db_domain=db_recovery_file_dest=/u01/app/oracle/flash_recovery_areadb_recovery_file_dest_size=2gdiagnostic_dest=/u01/app/oracleopen_cursors=300remote_login_passwordfile=exclusiveundo_tablespace=undotbs1undo_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、修改控制文件位置.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/disk.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;.6 执行脚本【根据考试要求，确定是否执行下列脚本】?/rdbms/admin/catalog.sql【执行时间：3分钟】?/rdbms/admin/catproc.sql【执行时间：8分钟】【！若不执行，rman不好用】?/sqlplus/admin/pupbld.sql.7 生成spfilesql create spfile from pfile;.8 重启数据库【！看时间情况】sql shutdown immediate;sql startup;看是否使用spfile启动：sql show parameter spfile;.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/oracleprod determine and set sizing parameters for database structures1、 注意表空间数据文件的大小、要求，如：size、next、autoextend、uniform size2、 control file文件的位置3、 logfile的位置4、 字符集统一用al32utf 创建1个表create table ksxx(c1 varchar2(100),n1 number,d1 date) tablespace users; 最后交卷注意事项提前6分钟。.1 查看库是否能连上1、oracledma $ sqlplus sys/oracleorcl as sysdba2、oracledma $ sqlplus system/oracleorcl.2 备份口令文件.3 备份参数文件.4 备份数据库1.4 【section1】server configuration考试时间：120分钟1.4.1 准备内容 把doc文档打开1、【sql language reference】定位关键【create tablespace】2、【net services reference】定位关键字【listener.ora file】3、【net services reference】定位关键字【tnsnames.ora】4、【net services administrators guide】-【11 configuring dispatchers】 打开1个窗口复制内容1.4.2 create and manage temporary, permanent, and undo tablespaces undo相关的设置场景：如查询时间很长2个小时，为保证不出错ora-1555提示sql show parameter undosql alter system set undo_retention=7200 scope=both; 带部分参数条件的表空间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; 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; 对把空间和和临时表空间的应用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 locations 增加日志组，分布在不同磁盘【关键字】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; 删除日志组成员sql alter database drop logfile member /u01/app/oracle/oradata/prod/disk1/redo4a.log; 增加日志组成员sql alter database add logfile member /u01/app/oracle/oradata/prod/disk1/redo4a.log to group 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.ctl7、 把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 file 远端数据库配置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:02export:/u01/app/oracle/dnfsdatamount:/u01/app/oracle/oradata/prod/dnfsdata3、【其它参考】oracleedgzrip1 ora2$ cd $oracle_home/dbsoracleedgzrip1 dbs$ vi oranfstabserver:29path:29export:/tmp/sharemount:/u01/nfs 远端数据库修改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.so 远端数据库创建表空间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 listener 新建静态注册增加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 status 新建动态注册使用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 administrators 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以下参考： 综合场景配置要求：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 administrators guide】-【16 troubleshooting oracle net services】-【tracing error information for oracle net services】 listener.ora级别adr_base_listener1 = /u01/app/oracle/product/11.2.0/grid/network/tracetrace_level_listener1 = usertrace_timestamp_listener1 = on 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 naming method】指easy connect:connect username/passwordhost:port/service_name:server/instance_name如：oracledma $ sqlplus test/testdma:1521/orcl:dedicated/orcl【server为dedicated,shared,pooled】oracleedgzrip1 admin$ sqlplus system/oracleedgzrip1:1621/:shared/prod1.4.16 use grid infrastructure to manage oracle databases and other resources参照rac部分。1.5 【section2】enterprise manager grid control1.5.1 deploy enterprise manager grid control agents prod数据库安装agent.1 下载的agent1、 火狐浏览器http:/edgzrip2:4889/agent_download/.0/2、 进入linux目录，下载agentdownload.linux 另存为：/home/oracle下3、 修改agentdownload.linux的执行权限oracleedgzrip1 $ chmod -r 777 agentdownload.linux.2 把prod库加到oratab中oracleedgzrip1 disk1$ vi /etc/oratabprod:/u01/app/oracle/product/11.2.0/dbhome_1:n【否则】gc不会自动发现prod数据库,也可以手工添加。【数据库性能优化咨询项目、整体梳理、】.3 安装agentoracleedgzrip1 $ ./agentdownload.linux -b /u01/app/oracle/product/11.2.0 -m edgzrip2 -r 4889【9:50开始】.4 执行脚本rootedgzrip1 # /u01/app/oracle/product/11.2.0/agent10g/root.sh【！注意agent安装程序仍在继续执行，执行完成后会自动退出】.5 查看状态oracleedgzrip1 bin$ cd /u01/app/oracle/product/11.2.0/agent10g/binoracleedgzrip1 bin$ emctl status agent.6 agent.0升级到.01、 把.0的安装介质拷贝到/home/oracle下oracleedgzrip1 bin$ scp -r edgzrip2:/tmp/soft/gcmedia/3731593 /home/oracle2、 把agent停掉oracleedgzrip1 bin$ cd /u01/app/oracle/product/11.2.0/agent10g/binoracleedgzrip1 bin$ emctl stop agent3、 执行升级安装oracleedgzrip1 disk1$ ./runinstallerrootedgzrip1 # /u01/app/oracle/product/11.2.0/agent10g/root.sh4、 查看状态oracleedgzrip1 bin$ cd /u01/app/oracle/product/11.2.0/agent10g/binoracleedgzrip1 bin$ emctl status agent1.5.2 若gc安装失败应对策略在奇数机安装database controloracleedgzrip1 bin$ emca -deconfig dbcontrol db -repos droporacleedgzrip1 bin$ emca -config dbcontrol db -repos create1.6 【section3】managing database availability【！先备份！】注意设置：rman configure controlfile autobackup format for device type disk clear;否则自动恢复控制文件会找不到，让其备到flash_recovery_areaautobackup下面。rman backup database plus archivelog; 1.6.1 mantain recovery catalogs. 把prod改为归档模式在prod库操作：sql shutdown immediate;sql startup mount;sql alter database archivelog;sql alter database open;查看状态：sql archive log list; 配置catalog在omr库操作：1、建catalog用的表空间sql create tablespace rmancatalog datafile /u01/app/oracle/oradata/omr/rmancatalog01.dbf size 50m;2、创建catalog的用户sql create user rcata identified by oracle default tablespace rmancatalog quota unlimited on rmancatalog;3、给catalog用户赋权限sql grant dba,recovery_catalog_owner to rcata;4、创建catalog资料信息oracleedgzrip2 $ rman catalog rcata/oracleomrrman create catalog;5、注册目标库oracleedgzrip2 $ rman target sys/oracleprod catalog rcata/oracleomrrman register database;【会报错，因前面配置了dispatcher共享连接，需要去掉】【!在prod库】sql alter system