Oracle10gR2手工创建实例

问题描述：

本机上已经安装了一个sid为orcl的实例，现在想用orcl这个实例的一些参数文件手工创建一个sid为wilson的实例。

1.确定新建数据库的sid

Last login: Wed Dec 26 15:27:36 2012 from 192.168.159.1
[oracle@test1 ~]$ pwd
/home/oracle
[oracle@test1 ~]$ export ORACLE_SID=wilson
[oracle@test1 ~]$ env | grep ORA
ORACLE_SID=wilson
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle

2.创建初始化参数文件

[oracle@CentOS5 ~]$ cd $ORACLE_HOME/dbs
[oracle@CentOS5 dbs]$ ls -la
total 84
drwxr-x---  2 oracle oinstall  4096 Dec 15 22:32 .
drwxr-xrwx 56 oracle oinstall  4096 Dec 15 19:46 ..
-rw-rw----  1 oracle oinstall  1544 Dec 15 22:32 hc_orcl.dat
-rw-r--r--  1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-rw----  1 oracle oinstall    24 Dec 14 22:53 lkORCL
-rw-r-----  1 oracle oinstall  1536 Dec 14 22:56 orapworcl
-rw-r-----  1 oracle oinstall  2560 Dec 15 20:46 spfileorcl.ora
[oracle@CentOS5 dbs]$ strings spfileorcl.ora > initwilson.ora

[oracle@CentOS5 dbs]$
[oracle@CentOS5 dbs]$ vi initwilson.ora

wilson.__db_cache_size=360710144
wilson.__java_pool_size=4194304
wilson.__large_pool_size=4194304
wilson.__shared_pool_size=113246208
wilson.__streams_pool_size=0
audit_file_dest='/u01/admin/wilson/adump'
background_dump_dest='/u01/admin/wilson/bdump'
compatible='10.2.0.3.0'
control_files='/u01/oradata/wilson/controlfile/controlfile01.ctl','/u01/oradata/wilson/controlfile/controlfile02.ctl'
core_dump_dest='/u01/admin/wilson/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='wilson'
db_recovery_file_dest='/u01/flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=wilsonXDB)'
job_queue_processes=10
nls_language='SIMPLIFIED CHINESE'
nls_territory='CHINA'
open_cursors=300
pga_aggregate_target=160432128
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=483393536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/u01/admin/wilson/udump'
~
~
~
"initwilson.ora" 29L, 993C written

上面主要是根据我自己建的一个orcl库的参数来生成参数文件，主要要更改目录为我要创建的wilson目录，这个目录已经目录的子目录都可以从之前创建的实例中拷贝过来，然后删掉属于原实例的文件。

3.创建口令文件

[oracle@CentOS5 dbs]$ pwd
/u01/oracle/dbs

[oracle@CentOS5 dbs]$ ls -la
total 88
drwxr-x---  2 oracle oinstall  4096 Dec 28 11:33 .
drwxr-xrwx 56 oracle oinstall  4096 Dec 15 19:46 ..
-rw-rw----  1 oracle oinstall  1544 Dec 15 22:32 hc_orcl.dat
-rw-r--r--  1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r--  1 oracle oinstall   995 Dec 28 11:33 initwilson.ora
-rw-rw----  1 oracle oinstall    24 Dec 14 22:53 lkORCL
-rw-r-----  1 oracle oinstall  1536 Dec 14 22:56 orapworcl
-rw-r-----  1 oracle oinstall  2560 Dec 15 20:46 spfileorcl.ora
[oracle@CentOS5 dbs]$ orapwd file=orapwwilson password=wilson entries=10
[oracle@CentOS5 dbs]$ ls -la
total 92
drwxr-x---  2 oracle oinstall  4096 Dec 28 11:57 .
drwxr-xrwx 56 oracle oinstall  4096 Dec 15 19:46 ..
-rw-rw----  1 oracle oinstall  1544 Dec 15 22:32 hc_orcl.dat
-rw-r--r--  1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r--  1 oracle oinstall   995 Dec 28 11:33 initwilson.ora
-rw-rw----  1 oracle oinstall    24 Dec 14 22:53 lkORCL
-rw-r-----  1 oracle oinstall  1536 Dec 14 22:56 orapworcl
-rw-r-----  1 oracle oinstall  2560 Dec 28 11:57 orapwwilson
-rw-r-----  1 oracle oinstall  2560 Dec 15 20:46 spfileorcl.ora

4.连接到实例，并创建spfile文件

[oracle@CentOS5 dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 28 11:59:07 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> !ls -la $ORACLE_HOME/dbs
total 96
drwxr-x---  2 oracle oinstall  4096 Dec 28 12:00 .
drwxr-xrwx 56 oracle oinstall  4096 Dec 15 19:46 ..
-rw-rw----  1 oracle oinstall  1544 Dec 15 22:32 hc_orcl.dat
-rw-r--r--  1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r--  1 oracle oinstall   995 Dec 28 11:33 initwilson.ora
-rw-rw----  1 oracle oinstall    24 Dec 14 22:53 lkORCL
-rw-r-----  1 oracle oinstall  1536 Dec 14 22:56 orapworcl
-rw-r-----  1 oracle oinstall  2560 Dec 28 11:57 orapwwilson
-rw-r-----  1 oracle oinstall  2560 Dec 15 20:46 spfileorcl.ora
-rw-r-----  1 oracle oinstall  3584 Dec 28 12:00 spfilewilson.ora

5.将实例启动到nomount状态

SQL> startup nomount
ORACLE instance started.

Total System Global Area  486539264 bytes
Fixed Size                  1268220 bytes
Variable Size             121636356 bytes
Database Buffers          360710144 bytes
Redo Buffers                2924544 bytes

6.运行创建数据库的脚本

SPOOL log.log;
CREATE DATABASE "wilson"
   USER SYS IDENTIFIED BY wilson
   USER SYSTEM IDENTIFIED BY wilson
   LOGFILE GROUP 1 ('/u01/oradata/wilson/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oradata/wilson/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/oradata/wilson/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/oradata/wilson/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/oradata/wilson/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oradata/wilson/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE UNDOTBS1
      DATAFILE '/u01/oradata/wilson/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
spool off;

6.Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build views, synonyms, and PL/SQL packages:
CONNECT /  AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql
EXIT

7.创建用户的默认表空间

SQL> create tablespace users datafile '/u01/oradata/wilson/users01.dbf' size 100m;

Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
USERS

5 rows selected.

SQL> alter database default tablespace users;

Database altered.

大功告成~~