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> @?/rdbms/admin/catalog
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.