以下基于一个安装过的Oracle 11gR2库,部署到其他机器的过程描述。
[root@localhost u01]# ls Oracle11gR2/
client.tar.gz db.tar.gz[root@localhost u01]# cd ..
[root@localhost /]# ls
bin cgroup etc lib lost+found misc net proc root selinux sys u01 usr var
boot dev home lib64 media mnt opt RHEL sbin srv tmp uloc utxt
[root@localhost /]# su - oracle
[oracle@localhost ~]$ ls -al
total 32
drwx------. 4 oracle oinstall 4096 Mar 14 03:35 .
drwxr-xr-x. 4 root root 4096 Mar 16 16:52 ..
-rw-------. 1 oracle oinstall 134 Mar 14 03:35 .bash_history
-rw-r--r--. 1 oracle oinstall 18 Jul 9 2013 .bash_logout
-rw-r--r--. 1 oracle oinstall 176 Jul 9 2013 .bash_profile
-rw-r--r--. 1 oracle oinstall 124 Jul 9 2013 .bashrc
drwxr-xr-x. 2 oracle oinstall 4096 Jul 14 2010 .gnome2
drwxr-xr-x. 4 oracle oinstall 4096 Mar 10 03:40 .mozilla
1. 设置环境变量
--------------------------------
[oracle@localhost admin]$ 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/11.2.0/db_1
export ORACLE_SID=cats
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export PATH
--------------------------------------
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /uloc/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@localhost admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /uloc/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
2. 建初始化参数文件
oracle@localhost dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@localhost dbs]$ ls -al
total 28
drwxr-xr-x. 2 oracle oinstall 4096 Mar 16 17:35 .
drwxr-xr-x. 77 oracle oinstall 4096 Jul 24 2014 ..
-rw-rw----. 1 oracle oinstall 1544 Aug 25 2014 hc_ssap.dat
-rw-r--r--. 1 oracle oinstall 2935 Mar 16 17:35 initcats.ora
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Jul 24 2014 lkSSAP
-rw-r-----. 1 oracle oinstall 1536 Jul 24 2014 orapwssap
[oracle@localhost dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@localhost dbs]$ cat initcats.ora
#
# $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
#
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
# init.ora
# FUNCTION
# NOTES
# MODIFIED
# ysarig 05/14/09 - Updating compatible to 11.2
# ysarig 08/13/07 - Fixing the sample for 11g
# atsukerm 08/06/98 - fix for 8.1.
# hpiao 06/05/97 - fix for 803
# glavash 05/12/97 - add oracle_trace_enable comment
# hpiao 04/22/97 - remove ifile=, events=, etc.
# alingelb 09/19/94 - remove vms-specific stuff
# dpawson 07/07/93 - add more comments regarded archive start
# maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
# jloaiza 03/07/92 - change ALPHA to BETA
# danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
# ghallmar 02/03/92 - db_directory -> db_domain
# maporter 01/12/92 - merge changes from branch 1.8.308.1
# maporter 12/21/91 - bug 76493: Add control_files parameter
# wbridge 12/03/91 - use of %c in archive format is discouraged
# ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
# thayes 11/27/91 - Change default for cache_clone
# jloaiza 08/13/91 - merge changes from branch 1.7.100.1
# jloaiza 07/31/91 - add debug stuff
# rlim 04/29/91 - removal of char_is_varchar2
# Bridge 03/12/91 - log_allocation no longer exists
# Wijaya 02/05/91 - remove obsolete parameters
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
db_name='cats'
memory_target=8000m
processes = 500
audit_file_dest='/u01/app/oracle/admin/cats/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=800
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/oradata/cats/control01.ctl,/u01/oradata/cats/control02.ctl,/u01/oradata/cats/control03.ctl)
compatible ='11.2.0'
[oracle@localhost dbs]$
[oracle@localhost dbs]$
3.建立需要的目录
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/flash_recovery_area
mkdir -p /u01/oradata/cats
4.创建口令文件
oracle@localhost admin]$ orapwd -help
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>
where
file - name of password file (required),
password - password for SYS will be prompted if not specified at command line,
entries - maximum number of distinct DBA (optional),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
There must be no spaces around the equal-to (=) character.
oracle@localhost admin]$ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=czty_cats force=y
[oracle@localhost dbs]$ ls -al
total 20
drwxr-xr-x. 2 oracle oinstall 4096 Mar 16 17:50 .
drwxr-xr-x. 77 oracle oinstall 4096 Jul 24 2014 ..
-rw-r--r--. 1 oracle oinstall 2935 Mar 16 17:35 initcats.ora
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 1536 Mar 16 17:50 orapwcats
5. 创建数据库
$ sqlplus '/as sysdba'
SQL> create spfile from pfile; (会自动读取pfile:$ORACLE_HOME/dbs/init$ORACLE_SID.ora)
SQL> startup nomount;
CREATE DATABASE cats
USER SYS IDENTIFIED BY czty_cats
USER SYSTEM IDENTIFIED BY czty_cats
LOGFILE GROUP 1 ('/u01/oradata/cats/redo01.log') SIZE 1024M,
GROUP 2 ('/u01/oradata/cats/redo02.log') SIZE 1024M,
GROUP 3 ('/u01/oradata/cats/redo03.log') SIZE 1024M
MAXLOGFILES 12
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 1000
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oradata/cats/system01.dbf' SIZE 2048M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oradata/cats/sysaux01.dbf' SIZE 2048M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oradata/cats/temp01.dbf'
SIZE 4096M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/oradata/cats/undotbs01.dbf'
SIZE 4096M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
指定日志输出地方
spool /home/oracle/scripts.log
创建系统默认的数据字典
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
创建系统 组件
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/dbmspool.sql
创建SYSAUX 组件
conn system/czty_cats
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
卸载扩展安全功能
@?/rdbms/admin/catnoqm.sql
---检查以上安装的组件是否正确安装:正常情况下,应该返回No Row
SQL> conn / as sysdba
Connected.
SQL>SELECT CID, dbms_registry_sys.patch_script(CID) AS source_file
FROM sys.registry$
where cid not in ('CATALOG', 'CATPROC', 'OWB');
no rows selected
6. 建立业务表空间
索引空间: cats_index
数据空间: cats_data
CREATE TABLESPACE cats_data LOGGING
DATAFILE '/u01/oradata/cats/cats_data01.dbf' SIZE 10240M REUSE AUTOEXTEND OFF,
'/u01/oradata/cats/cats_data02.dbf' SIZE 10240M REUSE AUTOEXTEND OFF,
'/u01/oradata/cats/cats_data03.dbf' SIZE 10240M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE cats_index LOGGING
DATAFILE '/u01/oradata/cats/cats_index01.dbf' SIZE 10240M REUSE AUTOEXTEND OFF,
'/u01/oradata/cats/cats_index02.dbf' SIZE 10240M REUSE AUTOEXTEND OFF,
'/u01/oradata/cats/cats_index03.dbf' SIZE 10240M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL;
=================================================
建立业务的具体表
1. 将发布版本中的create_user.sql
[oracle@localhost oracledb]$ cat create_user.sql
SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET SCAN ON
SET ECHO ON
UNDEFINE user_name
UNDEFINE user_password
UNDEFINE user_tablespace
REM
REM create user
REM
create user &&user_name identified by &&user_password
default tablespace &&user_tablespace
temporary tablespace tempts1
quota unlimited on &&user_tablespace;
CREATE ROLE ROLE_APPLICATION;
grant create session,alter session to ROLE_APPLICATION;
grant CREATE ASSEMBLY to ROLE_APPLICATION;
grant CREATE CLUSTER to ROLE_APPLICATION;
grant CREATE DATABASE LINK to ROLE_APPLICATION;
grant CREATE DIMENSION to ROLE_APPLICATION;
grant CREATE INDEXTYPE to ROLE_APPLICATION;
grant CREATE JOB to ROLE_APPLICATION;
grant CREATE MATERIALIZED VIEW to ROLE_APPLICATION;
grant CREATE OPERATOR to ROLE_APPLICATION;
grant CREATE PROCEDURE to ROLE_APPLICATION;
grant CREATE SEQUENCE to ROLE_APPLICATION;
grant CREATE SESSION to ROLE_APPLICATION;
grant CREATE SYNONYM to ROLE_APPLICATION;
grant CREATE TABLE to ROLE_APPLICATION;
grant CREATE TRIGGER to ROLE_APPLICATION;
grant CREATE TYPE to ROLE_APPLICATION;
grant CREATE VIEW to ROLE_APPLICATION;
grant EXECUTE ASSEMBLY to ROLE_APPLICATION;
grant connect,ROLE_APPLICATION to &&user_name;
grant execute on dbms_lock to &&user_name;
grant debug connect session to &&user_name;
PAUSE
REM
REM grant tablespace to users
REM
ALTER USER &&user_name QUOTA UNLIMITED ON cats_data;
ALTER USER &&user_name QUOTA UNLIMITED ON cats_index;
REM
REM create database objects
REM
conn &&user_name/&&user_password
@./create_mtas.sql
REM
REM recomplie invalid objects
REM
conn / as sysdba
execute utl_recomp.recomp_serial(upper('&&user_name'));
PAUSE
conn &&user_name/&&user_password
REM
REM init spnumber cfg
REM
UNDEFINE v_spnumber
declare
v_pro_channel number default 100;
begin
PRO_ADD_TAB_PARTITIONS('PRO_ADD_TAB_PARTITIONS', v_pro_channel);
end;
/
REM
REM init data
REM
set ECHO OFF
@./init_web_data.sql
[oracle@localhost oracledb]$ ls -al
total 180
drwxr-xr-x. 2 sms oinstall 4096 Mar 16 19:12 .
drwxr-xr-x. 9 sms oinstall 4096 Mar 16 19:04 ..
-rw-r--r--. 1 sms oinstall 786 Mar 13 16:56 change_log.txt
-rw-r--r--. 1 sms oinstall 162088 Mar 13 16:56 create_mtas.sql
-rw-r--r--. 1 sms oinstall 1906 Mar 16 19:12 create_user.sql
-rw-r--r--. 1 sms oinstall 2403 Mar 13 16:56 init_web_data.sql
[oracle@localhost oracledb]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 16 19:13:12 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, Data Mining and Oracle Database Vault options
(可选)删除用户cats
----- SQL> drop user cats cascade;
执行创建用户脚本(针对用户cats):
SQL> @create_user.sql