Oracle手工建库指南

一：设置环境变量 [Oracle@orcl ~]$ cat .bash_profile # .bash_profile # Get the aliases and functionsif [ -f ~/.bashrc ];

一：设置环境变量

[Oracle@orcl ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
EDITOR=vim
NLS_LANG=american_america.AL32UTF8
NLS_DATE_FORMAT=YYYY-MM-DD:HH24:MI:SS
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$PATH
export PATH ORACLE_SID ORACLE_BASE ORACLE_HOME EDITOR NLS_LANG NLS_DATE_FORMAT
alias sqlplus='/usr/local/rlwrap/bin/rlwrap sqlplus'
alias rman='/usr/local/rlwrap/bin/rlwrap rman'

二：准备密码文件和初始化参数文件以及创建数据库脚本

[root@orcl ~]# su - oracle
[oracle@orcl ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl dbs]$ orapwd file=orapworcl password=123456 entries=5

*.db_name=orcl
*.db_block_size=8192
*.sga_target=1677721600
*.undo_management='AUTO'
*.control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control012ctl','/u01/app/oracle/oradata/orcl/control03.ctl')
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

[oracle@orcl ~]$ cat $ORACLE_HOME/dbs/initorcl.ora
*.db_name=orcl
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_unique_name='orcl'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=1500
*.control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control012ctl','/u01/app/oracle/oradata/orcl/control03.ctl')
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

[oracle@orcl ~]$ cat create_db.sql
CREATE DATABASE orcl
USER SYS IDENTIFIED BY "123456"
USER SYSTEM IDENTIFIED BY "123456"
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01_1.dbf','/u01/app/oracle/oradata/orcl/redo01_2.dbf') SIZE 150M,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02_1.dbf','/u01/app/oracle/oradata/orcl/redo02_2.dbf') SIZE 150M,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03_1.dbf','/u01/app/oracle/oradata/orcl/redo03_2.dbf') SIZE 150M
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 2000M REUSE EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 800M REUSE
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs1.dbf' SIZE 2000M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

三：准备创建数据库需要的相关目录

[oracle@orcl ~]$ mkdir -p /u01/app/oracle/admin/orcl
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/admin/orcl/{adump,bdump,cdump,udump}
[oracle@orcl ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

四：开始建库

[oracle@orcl ~]$ env |grep SID
ORACLE_SID=orcl
[oracle@orcl ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 29 11:55:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 121637108 bytes
Database Buffers 41943040 bytes
Redo Buffers 2973696 bytes

SQL> @/home/oracle/create_db.sql;
Database created

[oracle@orcl ~]$ ls -lh /u01/app/oracle/oradata/orcl/
total 5.6G
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control012ctl
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control01.ctl
-rw-r----- 1 oracle oinstall 6.6M Sep 29 14:30 control03.ctl
-rw-r----- 1 oracle oinstall 151M Sep 29 14:25 redo01_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:25 redo01_2.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo02_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo02_2.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo03_1.dbf
-rw-r----- 1 oracle oinstall 151M Sep 29 14:22 redo03_2.dbf
-rw-r----- 1 oracle oinstall 801M Sep 29 14:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 2.0G Sep 29 14:25 system01.dbf
-rw-r----- 1 oracle oinstall 21M Sep 29 14:25 temp01.dbf
-rw-r----- 1 oracle oinstall 2.0G Sep 29 14:25 undotbs1.dbf

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;
PL/SQL procedure successfully completed.
SQL> shutdown immediate
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 138414324 bytes
Database Buffers 25165824 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

五：验证

[oracle@orcl ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 29 14:52:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1290917246)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1290917246 CURRENT 1 2011-09-29:14:21:50

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2000 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf
2 2000 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs1.dbf
3 800 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 20 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> create tablespace users datafile '/u01/app/oracle/oradata/orcl/users01.dbf' size 500M
2 autoextend on maxsize 5G;
Tablespace created

SQL> alter database default tablespace users;
Database altered

六：配置EM

建立和配置EM资料库
SQL> @?/sysman/admin/emdrep/sql/emreposcre /u01/app/oracle/product/10.2.0/db_1 SYSMAN 123456 TEMP ON;
SQL> alter user SYSMAN identified by "123456" account unlock;
SQL> alter user DBSNMP identified by "123456" account unlock;
SQL> exit
[oracle@orcl admin]$ emca -config dbcontrol db -silent -DB_UNIQUE_NAME orcl -PORT 1521 -EM_HOME /u01/app/oracle/product/10.2.0/db_1 -LISTENER LISTENER -SERVICE_NAME orcl.herostart.com -SYS_PWD 123456 -SID orcl -ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1 -DBSNMP_PWD 123456 -HOST orcl.herostart.com -LISTENER_OH /u01/app/oracle/product/10.2.0/db_1 -LOG_FILE /u01/app/oracle/product/10.2.0/db_1/log/emConfig.log -SYSMAN_PWD 123456
[oracle@orcl admin]$ emctl status dbconsole
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/orcl.herostart.com_orcl/sysman/log