oracle手工建庫（Build the Oracle Database Manually）

Build the Oracle Database Manually     As we all know that Oracle offers two ways for us to build database. One is using the tool named DBCA, and another is to build database manually.

The first way to build database is so normal that we do not talk it this time. The emphasis of this article is to build the oracle database manually.

At the very beginning, we should make sure how many steps we should do. To build database manually first we should prepare the environmental parameters for oracle database, second prepare the password file and initial parameter file, third we should prepare scrip to create database, and finally prepare the directory for oracle database.

Now here we go.

1. Prepare the environmental parameter for database

[[email protected] ~]$ pwd

/home/oracle

[[email protected] ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=orcl2

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/usr/X11R6/lib

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386/server

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/rdbms/lib

export CLASS_PATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib

export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/rdbms/jlib

export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/network/jlib

export TNS_ADMIN=$ORACLE_HOME/network/admin

export NLS_LANG=american_america.AL32UTF8

export ORACLE_TERM=xterm

export EDITOR=vi

export PATH=$ORACLE_HOME/bin:$PATH

export LANG=en_US

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

2. Prepare the password file and initial parameter file for database

[[email protected] ~]# su - oracle

[o[email protected] ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/

@@ create the password file for orcl2

[[email protected] dbs]$ orapwd file=orapworcl2 password=oracle

@@ create the initial parameter file for orcl2

[[email protected] dbs]$ vim initorcl2.ora

orcl.__db_cache_size=973078528

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__shared_pool_size=301989888

orcl.__db_cache_size=973078528

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__shared_pool_size=301989888

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl2/adump'

*.background_dump_dest='/u01/app/oracle/admin/orcl2/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/orcl2/controlfile/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl2/controlfile/control01.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl2/cdump'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain='example.com'

*.db_file_multiblock_read_count=16

*.db_name='orcl2'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=440401920

*.processes=200

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=225

3. Prepare the scrip to create database

[[email protected] ~]$ vim create_db.sql

CREATE DATABASE orcl2

USER SYS IDENTIFIED BY "oracle"

USER SYSTEM IDENTIFIED BY "oracle"

LOGFILE

GROUP 1 ('/u01/app/oracle/oradata/orcl2/redo01_1.dbf','/u01/app/oracle/oradata/orcl2/redo01_2.dbf') SIZE 50M,

GROUP 2 ('/u01/app/oracle/oradata/orcl2/redo02_1.dbf','/u01/app/oracle/oradata/orcl2/redo02_2.dbf') SIZE 50M,

GROUP 3 ('/u01/app/oracle/oradata/orcl2/redo03_1.dbf','/u01/app/oracle/oradata/orcl2/redo03_2.dbf') SIZE 50M

MAXLOGFILES 10

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/app/oracle/oradata/orcl2/datafile/system01.dbf' SIZE 512M REUSE EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl2/datafile/sysaux01.dbf' SIZE 512M REUSE

DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/orcl2/datafile/temp01.dbf' SIZE 20M REUSE

UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/orcl2/datafile/undotbs1.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

4. Prepare the directory for database

[[email protected] ~]$ mkdir /u01/app/oracle/admin/orcl2

[[email protected] ~]$ mkdir /u01/app/oracle/oradata/orcl2

[[email protected] ~]$ mkdir /u01/app/oracle/oradata/orcl2/{controlfile,datafile,onlinelog}

[[email protected] ~]$ mkdir /u01/app/oracle/admin/orcl2/{adump,bdump,cdump,udump}

[[email protected] ~]$ mkdir /u01/app/oracle/ flash_recovery_area /orcl2

[[email protected] ~]$ mkdir /u01/app/oracle/ flash_recovery_area /orcl2/controlfile

5. Create the database

[[email protected] ~]$ echo $ORACLE_SID

orcl2

[[email protected] orcl2]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> startup nomount

ORACLE instance started.

SQL> @/home/oracle/create_db.sql

Database created.

@@ now we check the database file

[[email protected] orcl2]$ ls -lh /u01/app/oracle/oradata/orcl2/

total 301M

drwxr-xr-x 2 oracle oinstall 4.0K Jul 30 07:22 controlfile

drwxr-xr-x 2 oracle oinstall 4.0K Jul 30 07:23 datafile

drwxr-xr-x 2 oracle oinstall 4.0K Jul 30 07:06 onlinelog

-rw-r----- 1 oracle oinstall 51M Jul 30 07:53 redo01_1.dbf

-rw-r----- 1 oracle oinstall 51M Jul 30 07:53 redo01_2.dbf

-rw-r----- 1 oracle oinstall 51M Jul 30 07:22 redo02_1.dbf

-rw-r----- 1 oracle oinstall 51M Jul 30 07:22 redo02_2.dbf

-rw-r----- 1 oracle oinstall 51M Jul 30 07:22 redo03_1.dbf

-rw-r----- 1 oracle oinstall 51M Jul 30 07:22 redo03_2.dbf

[[email protected] orcl2]$ ls -lh /u01/app/oracle/oradata/orcl2/controlfile/

total 6.6M

-rw-r----- 1 oracle oinstall 6.6M Jul 30 07:55 control01.ctl

[[email protected] orcl2]$ ls -lh /u01/app/oracle/oradata/orcl2/datafile/

total 1.2G

-rw-r----- 1 oracle oinstall 513M Jul 30 07:24 sysaux01.dbf

-rw-r----- 1 oracle oinstall 513M Jul 30 07:53 system01.dbf

-rw-r----- 1 oracle oinstall 21M Jul 30 07:23 temp01.dbf

-rw-r----- 1 oracle oinstall 201M Jul 30 07:46 undotbs1.dbf

@@ to create data directory view and the running environment for PL/SQL

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

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> create tablespace users datafile '/u01/app/oracle/oradata/orcl2/datafile/users01.dbf' size 200M reuse default storage (initial 5120K next 5120k pctincrease 20);

Tablespace created.

SQL> alter database default tablespace users;

Database altered.

6. Authentication

@@ now the database has been created, and we should test the database in the finally

[[email protected] orcl2]$ echo $ORACLE_SID

orcl2

[[email protected] orcl2]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 30 08:47:29 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL2 (DBID=770813640)

MAN> 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 ORCL2 770813640 CURRENT 1 30-JUL-12

RMAN> report schema;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 512 SYSTEM *** /u01/app/oracle/oradata/orcl2/datafile/system01.dbf

2 200 UNDOTBS1 *** /u01/app/oracle/oradata/orcl2/datafile/undotbs1.dbf

3 512 SYSAUX *** /u01/app/oracle/oradata/orcl2/datafile/sysaux01.dbf

4 200 USERS *** /u01/app/oracle/oradata/orcl2/datafile/users01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1 20 TEMP 20 /u01/app/oracle/oradata/orcl2/datafile/temp01.dbf