oracle 新建实例名,oracle手动创建另外一个实例

一.准备数据库物理文件存放的相关路径

例如：计划将控制文件，数据文件，日志文件等存放到/u02目录下

# mkdir /u02

# chown oracle:oinstall /u02

# chmod 755  /u02

# su - oracle

$ export ORACLE_BASE=/u02

$ export ORACLE_SID=test

$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/

二、 连接到另外一个实例，得到参数文件

$sqlplus /  as sysdba

SQL> CREATE PFILE='/u02/inittest.ora' from spfile;

SQL> exit

$ vim /u02/inittest.ora

三、修改参数文件：将参数文件inittest.ora中的与实例名有关的参数改为test，相应的路径也需改掉

如：

test.__db_cache_size=83886080

test.__java_pool_size=4194304

test.__large_pool_size=4194304

test.__shared_pool_size=67108864

test.__streams_pool_size=0

*.audit_file_dest='/u02/admin/test/adump'

*.background_dump_dest='/u02/admin/test/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u02/oradata/test/control01.ctl','/u02/oradata/test/control02.c

tl','/u02/oradata/test/control03.ctl'

*.core_dump_dest='/u02/admin/test/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.db_recovery_file_dest='/u02/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

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

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/archive1'

*.O7_DICTIONARY_ACCESSIBILITY=FALSE

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u02/admin/test/udump'

四、以dba的身份连入，将实例启动到nomount状态

[oracle@test1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 09:34:28 2011

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

Connected to an idle instance.

SQL> startup nomount pfile='/u02/inittest.ora'

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218292 bytes

Variable Size              75499788 bytes

Database Buffers           83886080 bytes

Redo Buffers                7168000 bytes

SQL> show parameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      test

db_unique_name                       string      test

global_names                         boolean     FALSE

instance_name                        string      test

lock_name_space                      string

log_file_name_convert                string

service_names                        string      test

五、创建数据库

CREATE DATABASE TEST

maxinstances 8

maxloghistory 1

maxlogfiles 16

maxlogmembers 3

maxdatafiles 100

DATAFILE

'$ORACLE_BASE/oradata/$ORACLE_SID/system01.dbf' size 300m autoextend on next 10m

extent management local

sysaux datafile

'$ORACLE_BASE/oradata/$ORACLE_SID/sysaux01.dbf' size 120m autoextend on next 10m

default temporary tablespace temp tempfile

'$ORACLE_BASE/oradata/$ORACLE_SID/temp01.dbf' size 20m

undo tablespace undotbs1 datafile

'$ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf' size 50m

CHARACTER set AL32UTF8

NATIONAL character set AL16UTF16

SET TIME_ZONE='+08:00'

LOGFILE

group 1 '$ORACLE_BASE/oradata/$ORACLE_SID/redo01.log' size 50m,

group 2 '$ORACLE_BASE/oradata/$ORACLE_SID/redo02.log' size 50m,

group 3 '$ORACLE_BASE/oradata/$ORACLE_SID/redo03.log' size 50m

/

六、查看数据库状态

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

说明数据库已经启动到了open状态

七、创建spfile

SQL> create spfile from pfile='/u02/inittest.ora';

File created.

八、创建数据字典

SQL>

SQL> @?/rdbms/admin/catproc

九、创建users表空间 建立scott用户

SQL> create tablespace users datafile '$ORACLE_BASE/oradata/$ORACLE_SID/user01.dbf' size 10m autoextend on;

Tablespace created.

SQL> @?/rdbms/admin/utlsampl

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dba dbs]$sql /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 7 06:06:50 2011

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

SQL> conn / as sysdba

Connected.

SQL> alter user scott account unlock identified by tiger;

User altered.

十、连接到scott用户有错误提示 根据提示执行PUPBLD脚本

SQL> conn scott/tiger

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL>

SQL> conn system/manager

Connected.

SQL>

SQL> show user

USER is "SYSTEM"

创建安全审核概要表:

SQL> @?/sqlplus/admin/pupbld

SQL> conn scott/tiger

Connected.