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