Section 0: 创建一个数据库
修改环境变量：
[oracle@rac1 ~]$ cd ~
[oracle@rac1 ~]$ vi .bash_profile
export ORACLE_SID=PROD export EDITOR=vi
[oracle@rac1 ~]$ source .bash_profile - [oracle@rac1 ~]$ env|grep -i sid ORACLE_SID=PROD
建立相关目录：
[oracle@rac1 ~]$ cd $ORACLE_BASE
[oracle@rac1 oracle]$ mkdir -p admin/PROD/{a,b,c,u}dump [oracle@rac1 oracle]$ mkdir -p oradata/PROD/Disk{1,2,3,4,5}
修改/etc/oratab文件
[oracle@rac1 oracle]$ vi /etc/oratab
PROD:/u01/app/oracle/OracleHomes/db10g:N
建立初始化参数文件initPROD.ora
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$cat init.ora|grep -v ^#|grep -v ^$>initPROD.ora [oracle@rac1 dbs]$ vi initPROD.ora
参考联机文档：Administrator's Guide —> 第二章Creating an Oracle Database —> Understanding Initialization Parameters —> Sample Initialization Parameter File 修改如下：
修改里面的db_name和controlfile
删除其他内存参数:db_block_buffers、shared_pool_size,LOG_BUFFER
增加sga_max_size和sga_target均为300M
control_files = (/u01/app/oracle/oradata/PROD/Disk1/control01.ctl, /u01/app/oracle/oradata/PROD/Disk2/control02.ctl, /u01/app/oracle/oradata/PROD/Disk3/control03.ctl) db_name = PROD
log_archive_dest_1 = \TION=/home/oracle/arch\log_archive_dest_state_1 = enable db_block_size = 8192 undo_management = AUTO undo_tablespace = undotbs compatible = 10.2.0 sga_target = 300M sga_max_size = 300M
建立密码文件orapwPROD
[oracle@rac1 dbs]$ orapwd file=orapwPROD password=oracle entries=20
启动SQLPLUS
[oracle@rac1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 16:08:29 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance.
创建SPFILE
SQL> create spfile from pfile; File created.
启动到nomount
SQL> startup nomount ORACLE instance started.
Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 104859024 bytes Database Buffers 205520896 bytes Redo Buffers 2973696 bytes SQL>
在Gedit中编辑创建数据库语句，参考联机文档Administrator's Guide —> 第二章Creating an Oracle Database —> Step 7: Issue the CREATE DATABASE Statement [oracle@rac1 scripts]$ touch createPROD.sql [oracle@rac1 scripts]$ gedit createPROD.sql CREATE DATABASE PROD
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo101.log','/u01/app/oracle/oradata/PROD/Disk2/redo102.log') SIZE 100M, GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo201.log','/u01/app/oracle/oradata/PROD/Disk2/redo202.log') SIZE 100M, GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo301.log','/u01/app/oracle/oradata/PROD/Disk2/redo302.log') SIZE 100M
MAXLOGFILES 200 MAXLOGMEMBERS 5 MAXLOGHISTORY 200 MAXDATAFILES 100 MAXINSTANCES 2
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf' SIZE 100M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE 2G; 到此，手动建库已完成。
Section 1: 数据库和网络配置
1. Database Setup and Undo Management
1.1 Run the minimum required scripts to complete the basic configuration of PROD database .
@?/rdbms/admin/catalog.sql和@?/rdbms/admin/catproc.sql
1.2 Set up automatic undo management in the PROD database to support the following requirements :
1.2.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 90 minutes on
average .
1.2.2 The number of concurrent OLTP users will be approximately 120 during normal business hours .
1.2.3 The number of concurrent batch processes that will run in the evenings and weekings will be approximately 12 to 15 . 更改参数设置
Alter system set undo_retention=5400(1.2.1); (show parameter unto) 使以上参数生效：
Alter tablespace undotbs retention guarantee(1.2.1);
(select tablespace_name,retention from dba_tablespaces;)
2. Server-side Network Configuration
2.1. Create a listener using the default listener name .
2.1.1 The TCP/IP protocol will be used for all connections.Use the machine name (not the IP address) for host. 2.1.2 This listener will listener on the default port.
2.1.3 Database: PROD and EMREP (created later) will be serviced by this listener. SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(SID_NAME =emrep )
(ORACLE_HOME = /oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
2.2 Add a second listener,named LSNR2,which will listen on port 1526,Configure this listener to support only automatic instance registrations.
2.2.1 Set up the PROD instance to automatically register with the LSNR2. #SID_LIST_LSNR2 = # (SID_LIST = # (SID_DESC =
# (SID_NAME = PROD )
# (ORACLE_HOME = /oracle/product/10.2.0/db_1) # ) # )
LSNR2 =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1526)) ) )
2.3 Start both listeners. LSNR_2 =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1526)) )
(CONNECT_DATA =
(SERVICE_NAME = LSNR_2) ) )
需要在tnsnames.ora里配置： prod_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) –将dispatcher也注册到1521的监听上
)
alter system set local_listener=?prod_2?(2.2.1) alter system regitster(2.2.1) lsnrctl start lsnrctl start lsnr2
3. Shared Server Configuartion
3.1 Configure the PROD database to support up to 300 sessions,reserving 100 for dedicated connection.
SQL> alter system set sessions=300 scope=spfile ;
SQL> alter system set shared_server_sessions=200 scope=both; 3.2 Configure the PROD database to support. 3.2.1 Default of 3 TCP dispatchers
SQL>alter system set dispatchers=\TCHERS=3)\scope=both;
alter system set dispatchers=\alter system set dispatchers=\
* LISTENER参数和local_listener参数都可以设定pmon进程将把dispatcher注册到哪个listener上去，但是dispatchers参数的子参数LISTENER优先于local_listener的配