Oracle手工建库指南

一：设置环境变量

[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[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. http://orcl.herostart.com: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