Oracle 11gR2手工复制

以下基于一个安装过的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