Section 0 :创建数据库(即手动建库)
1. Create a database the sid name is PROD
2. Don't run the Script catalog.sql and catproc.sql
参考联机文档：
Reference ==> Basic Initialization Parameters
Administrator's Guide ==> Step 7: Issue the CREATE DATABASE Statement
检查环境
创建密码文件
创建对应目录
创建pfile参数文件
创建spfile参数文件
创建数据库脚本
执行创建数据库脚本
- 1.检查ORACLE_SID=PROD
[root@ocm1 ~]# su - oracle
[oracle@ocm1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=testdb
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH
[oracle@ocm1 ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=PROD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH
~
~
~
~
~
".bash_profile" 18L, 376C written
[oracle@ocm1 ~]$ source .bash_profile
[oracle@ocm1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=PROD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH
[oracle@ocm1 ~]$
检查ORACLE_SID是否已经修改好
[oracle@ocm1 ~]$ env |grep ORA
ORACLE_SID=PROD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
或者
[oracle@ocm1 ~]$ env |grep -i sid
ORACLE_SID=PROD
- 2.创建密码文件
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ ll
total 32
-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30
[oracle@ocm1 dbs]$ ll
total 40
-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-r----- 1 oracle oinstall 5120 Mar 18 09:54 orapwPROD
- 3.创建所需要的目录
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/adump
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/bdump
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/cdump
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/udump
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk1
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk2/arch
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk3
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk4
[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk5
- 4.创建参数文件
Reference--Basic Initialization Parameters
创建参数文件
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@ocm1 dbs]$ vi initPROD.ora
CONTROL_FILES=('/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk1/control02.ctl','/u01/app/oracle/oradata/PROD/Disk1/control03.ctl')
DB_BLOCK_SIZE=8192
DB_CREATE_FILE_DEST=/u01/app/oracle/oradata/PROD/Disk1
DB_CREATE_ONLINE_LOG_DEST_1=/u01/app/oracle/oradata/PROD/Disk1
DB_NAME=PROD
JOB_QUEUE_PROCESSES=10
LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PROD/Disk2/arch'
PROCESSES=200
SGA_TARGET=500M
BACKGROUND_DUMP_DEST=/u01/app/oracle/admin/PROD/bdump
CORE_DUMP_DEST=/u01/app/oracle/admin/PROD/cdump
USER_DUMP_DEST=/u01/app/oracle/admin/PROD/udump
UNDO_MANAGEMENT=auto
UNDO_TABLESPACE=undotbs1
UNDO_RETENTION=5400
~
~
"initPROD.ora" [New] 16L, 705C written
- 5.使用pfile启动到nomount状态。创建spfile，使用spfile来强制启动，startup force nomount.
[oracle@ocm1 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 18 10:09:25 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 146800888 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile;
File created.
此时是以pfile启动的
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
或者查询ISSPECIFIED为false就是pfile启动的。
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE
强制重新启动
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 146800888 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes
查看是以spfile文件启动的
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0/db_1/dbs/spfilePROD.ora
或者查看ISSPECIFIED，有true即为spfile启动的数据库
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE
TRUE
SQL>
- 6.创建数据库脚本
SQL> CREATE DATABASE PROD
2 USER SYS IDENTIFIED BY oracle
3 USER SYSTEM IDENTIFIED BY oracle
4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,
5 GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,
6 GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 MAXINSTANCES 1
12 CHARACTER SET AL32UTF8
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE
17 DEFAULT TEMPORARY TABLESPACE tempts1
18 TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
19 SIZE 20M REUSE
20 UNDO TABLESPACE undotbs1
21 DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
22 SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
在数据库创建时可以查看下告警日志
[root@ocm1 ~]# su - oracle
[oracle@ocm1 ~]$ cd /u01/app/oracle/admin/PROD/bdump
[oracle@ocm1 bdump]$ ll
total 4
-rw-r--r-- 1 oracle oinstall 3488 Mar 18 10:11 alert_PROD.log
[oracle@ocm1 bdump]$ tail -f alert_PROD.log
Tue Mar 18 10:18:51 2014
CREATE DATABASE PROD
USER SYS IDENTIFIED BY ****USER SYSTEM IDENTIFIED BY ****LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
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 tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Tue Mar 18 10:18:53 2014
Database mounted in Exclusive Mode
Tue Mar 18 10:19:16 2014
Successful mount of redo thread 1, with mount id 254364939
Assigning activation ID 254364939 (0xf294d0b)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo01.log
Successful open of redo thread 1
Tue Mar 18 10:19:16 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Mar 18 10:19:16 2014
SMON: enabling cache recovery
Tue Mar 18 10:19:16 2014
create tablespace SYSTEM datafile '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL online
Tue Mar 18 10:19:38 2014
Completed: create tablespace SYSTEM datafile '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL online
Tue Mar 18 10:19:38 2014
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Tue Mar 18 10:19:55 2014
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Tue Mar 18 10:20:04 2014
Successfully onlined Undo Tablespace 1.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Tue Mar 18 10:20:04 2014
create tablespace SYSAUX datafile '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Tue Mar 18 10:20:24 2014
Completed: create tablespace SYSAUX datafile '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Tue Mar 18 10:20:25 2014
CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
SIZE 20M REUSE
Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
SIZE 20M REUSE
Tue Mar 18 10:20:25 2014
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Tue Mar 18 10:20:26 2014
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Tue Mar 18 10:20:33 2014
SMON: enabling tx recovery
Tue Mar 18 10:20:37 2014
Threshold validation cannot be done before catproc is loaded.
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=14, OS id=3903
Tue Mar 18 10:20:39 2014
Completed: CREATE DATABASE PROD
USER SYS IDENTIFIED BY ****USER SYSTEM IDENTIFIED BY ****LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
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 tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
TIMES: 45 minutes
1. Database Setup and Undo Management
1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.
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 minues on average.
1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.
1.2.3 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15
- 7.跑脚本，跑catalog和catporc这两个脚本
- 1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
PL/SQL procedure successfully completed.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
1 row selected.
- 1.2.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 90 minues on average.
SQL> alter system set undo_retention=5400;
System altered.
- 1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.
SQL> alter system set processes=135 scope=spfile;
System altered.
- 1.2.3 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15
SQL> alter system set job_queue_processes=15;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 150995192 bytes
Database Buffers 369098752 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>