oracle考试试题 - 图文

Database setup and undo management

1、Run the minimum require scripts to complete the basic configuration of database. Connect:sys/ORACLE as system [startup] SQL>@?/rdbms/admin/catalog.sql #创建数据库字典文件 SQL>@?/rdbms/admin/catproc.sql #创建数据库基本存储过程及包 SQL>@?/rdbms/admin/catblock.sql SQL>@?/rdbms/admin/catoctk.sql SQL>@?/rdbms/admin/owminst.plb Connect:system/oracle SQL>@?/sqlplus/admin/pupbld.sql 启动错误信息: ORA-00704: 引导程序进程失败 ORA-39700: 必须用 UPGRADE 选项打开数据库 解决方案: SQL>startup upgrade #启动数据库 SQL>show parameter sql_trace; #查看是否打开日志记录 SQL>alter system set sql_trace=true scope=spfile; SQL>shutdown immediate #关闭数据库 再次启动数据库,报错,看日志信息 SQL>startup ORA-01092:ORACLE实例终止,强行断开连接 查看xxx.trc日志文件： PARSING IN CURSOR #5 len=63 dep=1 uid=0 oct=3 lid=0 tim=1226366925317451 hv=3748897726 ad='3fb20db8' SELECT NULL FROM REGISTRY$ WHERE CID='CATPROC' AND VERSION = :1 我们发现这是因为没有执行cataproc.sql脚本文件. SQL>startup upgrade SQL>@?/rdbms/admin/cataproc.sql SQL>shutdown immediate 2、Set up automatic undo management in the PORD database to support the following requirements:

avoid ora-01555 shapshot too old errors for queries running up to 90 mins on average.

the number of concurrent OLTP user will be approximate 120 during

normal business hour.(正常时间，有120的会话)

the number of concurrent batch processes that will run in the evenings and weekend will up to 15.(正常情况,有15个批处理)

SQL>alter system set undo_retention=5400; SQL>alter tablespace undotbs1 retention guarantee; SQL>alter system set processes=150 scope=spfile; SQL>alter system set transactions_per_rollback_segment=12 scope=spfile;

二、Section1

Server-side network configuration

1、Create a listener using the default listener name

the TCP/IP protocol will be used for all connections. Use the machine name(do not IP address)

this listener will listen on the default port

database :PROD and EMREP(created later)will serviced by this listener SID_LIST_LISTENER= (SID_LIST= (SID_DESC=

(GLOBAL_DBNAME=PROD)

(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) (SID_NAME=PROD) )

(SID_DESC=

(GLOBAL_DBNAME=EMREP)

(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1) (SID_NAME=EMREP) ) )

LISTENER=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=db.oracle.com)(PORT=1521)) )

2、Add second listener, named LSNR2,which will listen on port 1526.configure this listener to support instance registrations.

set up the PORD instance to automatical register with the LSNR2 start both listeners 1、配置监听器LSNR2 LSNR2= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=db.oracle.com)(PORT=1526)) ) 2、PROD实例默认是动态注册到1521端口，不会注册到LSNR2监听器中，因此需要修改参数LOCAL_LISTENER 3、查看监听器是否成功注册

Share server configuration

1.Configure the PROD database to support up to 300 sessions，reserving 100 for dedicated connection. SQL>alter system set processes=200 scope=spfile; SQL>alter system set sessions=300 scope=spfile; #实例中同时存在最大会话数 SQL>alter system set shared_server_sessions=200;