在原有資料庫的基礎上再建立一個資料庫

（先決條件：噹噹前虛擬記憶體不夠用時，將現有資料庫關閉；如果不關庫，則需要增加虛擬記憶體）

建庫開始：
1.生成pfile檔案：

[oracle@book oracle]$ cd $ORACLE_HOME

[oracle@book dbhome_1]$ cd dbs

oracle@book dbs]$ ls

hc_PROD.dat init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

[oracle@book dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > initEN.ora

[oracle@book dbs]$

[oracle@book dbs]$ ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

2.編輯pfile檔案：
[oracle@book dbs]$ vi initEN.ora

db_name='EN'

memory_target=900M

processes = 150

audit_file_dest='/u01/app/oracle/admin/EN/adump'

#audit_trail ='db'

db_block_size=8192

db_domain=''

#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'

#db_recovery_file_dest_size=2G

#diagnostic_dest='<ORACLE_BASE>'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = (/u01/app/oracle/oradata/EN/control01.ctl, /u01/app/oracle/oradata/EN/control02.ctl)

compatible ='11.2.0'

3.根據pfile建立目錄adump目錄和控制檔案目錄：

[oracle@book oracle]$ mkdir -p /u01/app/oracle/admin/EN/adump

[oracle@book oradata]$ cd /u01/app/oracle/admin

[oracle@book admin]$ ls

EN PROD

[oracle@book dbs]$ mkdir -p /u01/app/oracle/oradata/EN/

[oracle@book oradata]$ ls

EN PROD

4.透過pfile建立spfile：

--檢視已存在的pfile檔案iniEN.ora

[oracle@book dbs]$ ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

--驗證當前環境變數

[oracle@prod dbs]$ echo $ORACLE_SID

PROD

[oracle@book dbs]$ export ORACLE_SID=EN

[oracle@book dbs]$ echo $ORACLE_SID

EN

--透過pfile建立spfile

[oracle@book dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 17:59:42 2016

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> !ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfileEN.ora spfilePROD.ora

——驗證，資料庫啟動到nomount（預設情況下，oracle會使用spfile啟動資料庫）:

[oracle@book dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 18:01:50 2016

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 939495424 bytes

Fixed Size 2258840 bytes

Variable Size 595593320 bytes

Database Buffers 335544320 bytes

Redo Buffers 6098944 bytes

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileEN.ora

SQL> select status from v$instance ;

STATUS

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

STARTED

5.編寫建立資料庫指令碼:

Oracle11g官方文件：

Administrator’s Guide->2 Creating and Configuring an Oracle Database->Step 9: Issue the CREATE DATABASE Statement

1)修改資料庫名稱及相關口令

2)由於是測試環境，所以，可以將日誌檔案改為1個成員，把成員大小改為10M

3)修改users表空間大小及undo表空間大小

4)修改undo表空間的名稱，要與pfile檔案中的名稱相同

[oracle@book admin]$ cd $ORACLE_HOME

[oracle@book dbhome_1]$ vi create.sql

create database EN

user sys identified by oracle

user system identified by oracle

logfile group 1 ('/u01/app/oracle/oradata/EN/redo01.log') size 10m blocksize 512,

group 2 ('/u01/app/oracle/oradata/EN/redo02.log') size 10m blocksize 512,

group 3 ('/u01/app/oracle/oradata/EN/redo03.log') size 10m blocksize 512

maxlogfiles 5

maxlogmembers 5

maxloghistory 1

maxdatafiles 100

character set us7ascii

national character set al16utf16

extent management local

datafile '/u01/app/oracle/oradata/EN/system01.dbf' size 325m reuse

sysaux datafile '/u01/app/oracle/oradata/EN/sysaux01.dbf' size 325m reuse

default tablespace users

datafile '/u01/app/oracle/oradata/EN/users01.dbf'

size 50m reuse autoextend on maxsize unlimited

default temporary tablespace tempts1

tempfile '/u01/app/oracle/oradata/EN/temp01.dbf'

size 20m reuse

undo tablespace undotbs1

datafile '/u01/app/oracle/oradata/EN/undotbs01.dbf'

size 50m reuse autoextend on maxsize unlimited;

~

"create.sql" [New] 25L, 2140C written

[oracle@book dbhome_1]$

6.建立資料庫（資料庫此時的狀態應該是nomount，直接在sqlplus下執行建立資料庫指令碼即可）:

SQL> @?/create.sql或者@?/create（預設找 .sql）,@執行指令碼，？指$ORACLE_HOME.

或者直接在sql下執行如下：

SQL>create database EN

user sys identified by oracle

user system identified by oracle

logfile group 1 ('/u01/app/oracle/oradata/EN/redo01.log') size 10m blocksize 512,

group 2 ('/u01/app/oracle/oradata/EN/redo02.log') size 10m blocksize 512,

group 3 ('/u01/app/oracle/oradata/EN/redo03.log') size 10m blocksize 512

maxlogfiles 5

maxlogmembers 5

maxloghistory 1

maxdatafiles 100

character set us7ascii

national character set al16utf16

extent management local

datafile '/u01/app/oracle/oradata/EN/system01.dbf' size 325m reuse

sysaux datafile '/u01/app/oracle/oradata/EN/sysaux01.dbf' size 325m reuse

default tablespace users

datafile '/u01/app/oracle/oradata/EN/users01.dbf'

size 50m reuse autoextend on maxsize unlimited

default temporary tablespace tempts1

tempfile '/u01/app/oracle/oradata/EN/temp01.dbf'

size 20m reuse

undo tablespace undotbs1

datafile '/u01/app/oracle/oradata/EN/undotbs01.dbf'

size 50m reuse autoextend on maxsize unlimited;

Database created.

--驗證檢視相關引數

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u01/app/oracle/oradata/EN/con

trol01.ctl, /u01/app/oracle/or

adata/EN/control02.ctl

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileEN.ora

7.建立字典表及工具包：

--必執行指令碼

SQL> conn / as sysdba

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> conn system/oracle

SQL> @?/sqlplus/admin/pupbld.sql

--可選指令碼

SQL> conn / as sysdba

SQL> @?/rdbms/admin/catblock.sql

SQL> @?/rdbms/admin/catoctk.sql

SQL> @?/rdbms/admin/owminst.plb

8.驗證資料庫可用性：

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

9.建立密碼檔案：

[oracle@book dbs]$ ls

hc_EN.dat hc_PROD.dat initEN.ora init.ora initPROD.ora lkEN lkPROD orapwPROD spfileEN.ora spfilePROD.ora

——檢視顯示沒有建密碼檔案，建立密碼檔案（作用：允許遠端用密碼方式以sysdba身份登陸資料庫，密碼檔案可有可無）

[oracle@book dbs]$ orapwd file=orapwEN password=oracle

[oracle@book dbs]$ ls

hc_EN.dat hc_PROD.dat initEN.ora init.ora initPROD.ora lkEN lkPROD orapwEN orapwPROD spfileEN.ora spfilePROD.ora

資料庫手工建庫完成！！！！！！！！！！

——驗證檢視：

[oracle@book dbs]$ cd /u01/app/oracle/oradata/EN/

[oracle@book EN]$ ls

control01.ctl control02.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

[oracle@book EN]$

[oracle@book ~]$ echo $ORACLE_SID

EN

[oracle@book ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 18:56:45 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> show parameter name

NAME TYPE VALUE

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

cell_offloadgroup_name string

db_file_name_convert string

db_name string EN

db_unique_name string EN

global_names boolean FALSE

instance_name string EN

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string EN

結束！！！！！！！！！

【手工建庫】（二）在原有資料庫的基礎上再建立一個資料庫

相關文章