oracle11g 建库,Oracle11g手工建库

最新推荐文章于 2023-12-31 21:14:19 发布

那天我捡了只猫

最新推荐文章于 2023-12-31 21:14:19 发布

阅读量163

点赞数

文章标签： oracle11g 建库

测试环境：

操作系统：64位 OEL5.6

数据库：Oracle11.2.0.4

1：配置环境变量(以下是本实验环境的配置)

Default

[oracle@prod ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

export ORACLE_SID=PROD

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[oracle@prod ~]$

--使环境变量生效，当然，如果重新登陆或者做过切换等操作，此步骤可以省略

[oracle@prod ~]$ . .bash_profile

[oracle@prod ~]$

[oracle@prod~]$cat.bash_profile

# .bash_profile

# Get the aliases and functions

if[-f~/.bashrc];then

.~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

exportPATH

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

exportORACLE_SID=PROD

exportPATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[oracle@prod~]$

--使环境变量生效，当然，如果重新登陆或者做过切换等操作，此步骤可以省略

[oracle@prod~]$..bash_profile

[oracle@prod~]$

2：创建密码文件(作用：允许远程用密码方式以sysdba身份登陆数据库，密码文件可有可无)

Default

--切换到存放密码文件的目录

[oracle@prod ~]$ cd $ORACLE_HOME/dbs

[oracle@prod dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@prod dbs]$ ls

init.ora

[oracle@prod dbs]$

--生成密码文件的命令，帮助信息

[oracle@prod dbs]$ orapwd

Usage: orapwd file= entries= force= ignorecase= nosysdba=

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@prod dbs]$

--生成密码文件，密码文件的格式：orapw+SID

[oracle@prod dbs]$ orapwd file=orapwPROD password=oracle

[oracle@prod dbs]$ ls

init.ora orapwPROD

[oracle@prod dbs]$

--切换到存放密码文件的目录

[oracle@prod~]$cd$ORACLE_HOME/dbs

[oracle@proddbs]$pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@proddbs]$ls

init.ora

[oracle@proddbs]$

--生成密码文件的命令，帮助信息

[oracle@proddbs]$orapwd

Usage:orapwdfile=entries=force=ignorecase=nosysdba=

where

file-nameofpasswordfile(required),

password-passwordforSYSwillbepromptedifnotspecifiedatcommandline,

entries-maximumnumberofdistinctDBA(optional),

force-whethertooverwriteexistingfile(optional),

ignorecase-passwordsarecase-insensitive(optional),

nosysdba-whethertoshutouttheSYSDBAlogon(optionalDatabaseVaultonly).

Theremustbenospacesaroundtheequal-to(=)character.

[oracle@proddbs]$

--生成密码文件，密码文件的格式：orapw+SID

[oracle@proddbs]$orapwdfile=orapwPRODpassword=oracle

[oracle@proddbs]$ls

init.oraorapwPROD

[oracle@proddbs]$

3：生成pfile文件

Default

[oracle@prod dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@prod dbs]$ ls

init.ora orapwPROD

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

[oracle@prod dbs]$ ls

init.ora initPROD.ora orapwPROD

[oracle@prod dbs]$

[oracle@prod dbs]$ vi initPROD.ora

db_name='PROD'

memory_target=1G

processes = 150

--注意要校验此路径，在下一步创建目录时，要相同

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

#audit_trail ='db'

--把审计的参数注释掉，去掉这个功能，DBCA建库，默认情况下是启用的。

db_block_size=8192

db_domain=''

--关闭快速恢复区

#db_recovery_file_dest='/flash_recovery_area'

--关闭快速恢复区，如果需要开启的话，随时可以通过修改参数来完成。

#db_recovery_file_dest_size=2G

--注释掉诊断的参数，数据库创建好后，这个参数会自动启用

#diagnostic_dest=''

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

--undo表空间名称，在创建数据库时名称要匹配上

undo_tablespace='UNDOTBS1'

--修改控制文件的名字及位置

control_files = (/u01/app/oracle/oradata/PROD/ora_control1.ctl,/u01/app/oracle/oradata/PROD/ora_control2.ctl)

compatible ='11.2.0'

[oracle@proddbs]$pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@proddbs]$ls

init.oraorapwPROD

[oracle@proddbs]$catinit.ora|grep-v^#|grep -v ^$ > initPROD.ora

[oracle@proddbs]$ls

init.orainitPROD.oraorapwPROD

[oracle@proddbs]$

[oracle@proddbs]$viinitPROD.ora

db_name='PROD'

memory_target=1G

processes=150

--注意要校验此路径，在下一步创建目录时，要相同

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

#audit_trail ='db'

--把审计的参数注释掉，去掉这个功能，DBCA建库，默认情况下是启用的。

db_block_size=8192

db_domain=''

--关闭快速恢复区

#db_recovery_file_dest='/flash_recovery_area'

--关闭快速恢复区，如果需要开启的话，随时可以通过修改参数来完成。

#db_recovery_file_dest_size=2G

--注释掉诊断的参数，数据库创建好后，这个参数会自动启用

#diagnostic_dest=''

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

--undo表空间名称，在创建数据库时名称要匹配上

undo_tablespace='UNDOTBS1'

--修改控制文件的名字及位置

control_files=(/u01/app/oracle/oradata/PROD/ora_control1.ctl,/u01/app/oracle/oradata/PROD/ora_control2.ctl)

compatible='11.2.0'

4：根据pfile创建目录adump目录和控制文件目录

Default

[oracle@prod ~]$ mkdir -p $ORACLE_BASE/admin/prod/adump

[oracle@prod ~]$ mkdir -p $ORACLE_BASE/oradata/PROD/

[oracle@prod ~]$

[oracle@prod~]$mkdir-p$ORACLE_BASE/admin/prod/adump

[oracle@prod~]$mkdir-p$ORACLE_BASE/oradata/PROD/

[oracle@prod~]$

5：通过pfile创建spfile

Default

--查看已存在的pfile文件initPROD.ora

[oracle@prod ~]$ cd $ORACLE_HOME/dbs

[oracle@prod dbs]$ ls

init.ora initPROD.ora orapwPROD

[oracle@prod dbs]$

--验证当前环境变量

[oracle@prod dbs]$ echo $ORACLE_SID

PROD

[oracle@prod dbs]$

--通过pfile创建spfile

[oracle@prod dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 16:34:35 2014

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> !ls

init.ora initPROD.ora orapwPROD spfilePROD.ora

SQL>

--查看已存在的pfile文件initPROD.ora

[oracle@prod~]$cd$ORACLE_HOME/dbs

[oracle@proddbs]$ls

init.orainitPROD.oraorapwPROD

[oracle@proddbs]$

--验证当前环境变量

[oracle@proddbs]$echo$ORACLE_SID

PROD

[oracle@proddbs]$

--通过pfile创建spfile

[oracle@proddbs]$sqlplus/assysdba

SQL*Plus:Release11.2.0.4.0ProductiononFriFeb2816:34:352014

Connectedtoanidleinstance.

SQL>createspfilefrompfile;

Filecreated.

SQL>!ls

init.orainitPROD.oraorapwPRODspfilePROD.ora

SQL>

6：数据库启动到nomount(默认情况下，oracle会使用spfile启动数据库)

Default

[oracle@prod dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 17:20:28 2014

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 671089544 bytes

Database Buffers 390070272 bytes

Redo Buffers 5517312 bytes

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

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

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

SQL>

SQL> select status from v$instance;

STATUS

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

STARTED

SQL>

[oracle@proddbs]$sqlplus/assysdba

SQL*Plus:Release11.2.0.4.0ProductiononFriFeb2817:20:282014

Connectedtoanidleinstance.

SQL>startupnomount;

ORACLEinstancestarted.

TotalSystemGlobalArea1068937216bytes

FixedSize2260088bytes

VariableSize671089544bytes

DatabaseBuffers390070272bytes

RedoBuffers5517312bytes

SQL>

SQL>showparameterspfile

NAMETYPEVALUE

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

spfilestring/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora

SQL>

SQL>selectstatusfromv$instance;

STATUS

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

STARTED

SQL>

7：编写创建数据库脚本

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文件中的名称相同

Default

CREATE DATABASE PROD

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512,

GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512,

GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.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/PROD/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf'

SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf'

SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATEDATABASEPROD

USERSYSIDENTIFIEDBYoracle

USERSYSTEMIDENTIFIEDBYoracle

LOGFILEGROUP1('/u01/app/oracle/oradata/PROD/redo01a.log')SIZE10MBLOCKSIZE512,

GROUP2('/u01/app/oracle/oradata/PROD/redo02a.log')SIZE10MBLOCKSIZE512,

GROUP3('/u01/app/oracle/oradata/PROD/redo03a.log')SIZE10MBLOCKSIZE512

MAXLOGFILES5

MAXLOGMEMBERS5

MAXLOGHISTORY1

MAXDATAFILES100

CHARACTERSETUS7ASCII

NATIONALCHARACTERSETAL16UTF16

EXTENTMANAGEMENTLOCAL

DATAFILE'/u01/app/oracle/oradata/PROD/system01.dbf'SIZE325MREUSE

SYSAUXDATAFILE'/u01/app/oracle/oradata/PROD/sysaux01.dbf'SIZE325MREUSE

DEFAULTTABLESPACEusers

DATAFILE'/u01/app/oracle/oradata/PROD/users01.dbf'

SIZE50MREUSEAUTOEXTENDONMAXSIZEUNLIMITED

DEFAULTTEMPORARYTABLESPACEtempts1

TEMPFILE'/u01/app/oracle/oradata/PROD/temp01.dbf'

SIZE20MREUSE

UNDOTABLESPACEUNDOTBS1

DATAFILE'/u01/app/oracle/oradata/PROD/undotbs01.dbf'

SIZE50MREUSEAUTOEXTENDONMAXSIZEUNLIMITED;

8：创建数据库(数据库此时的状态应该是nomount，直接在sqlplus下执行创建数据库脚本即可)

Default

SQL> CREATE DATABASE PROD

2 USER SYS IDENTIFIED BY oracle

3 USER SYSTEM IDENTIFIED BY oracle

4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512,

5 GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512,

6 GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log') SIZE 10M BLOCKSIZE 512

7 MAXLOGFILES 5

8 MAXLOGMEMBERS 5

9 MAXLOGHISTORY 1

10 MAXDATAFILES 100

11 CHARACTER SET US7ASCII

12 NATIONAL CHARACTER SET AL16UTF16

13 EXTENT MANAGEMENT LOCAL

14 DATAFILE '/u01/app/oracle/oradata/PROD/system01.dbf' SIZE 325M REUSE

15 SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE

16 DEFAULT TABLESPACE users

17 DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf'

18 SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

19 DEFAULT TEMPORARY TABLESPACE tempts1

20 TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf'

21 SIZE 20M REUSE

22 UNDO TABLESPACE UNDOTBS1

23 DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf'

24 SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

SQL> select status from v$instance;

STATUS

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

OPEN

SQL>

--查看相关参数

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u01/app/oracle/oradata/PROD/o

ra_control1.ctl, /u01/app/orac

le/oradata/PROD/ora_control2.c

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> show parameter spfile

NAME TYPE VALUE

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

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

/dbhome_1/dbs/spfilePROD.ora

SQL>

SQL>CREATEDATABASEPROD

2USERSYSIDENTIFIEDBYoracle

3USERSYSTEMIDENTIFIEDBYoracle

4LOGFILEGROUP1('/u01/app/oracle/oradata/PROD/redo01a.log')SIZE10MBLOCKSIZE512,

5GROUP2('/u01/app/oracle/oradata/PROD/redo02a.log')SIZE10MBLOCKSIZE512,

6GROUP3('/u01/app/oracle/oradata/PROD/redo03a.log')SIZE10MBLOCKSIZE512

7MAXLOGFILES5

8MAXLOGMEMBERS5

9MAXLOGHISTORY1

10MAXDATAFILES100

11CHARACTERSETUS7ASCII

12NATIONALCHARACTERSETAL16UTF16

13EXTENTMANAGEMENTLOCAL

14DATAFILE'/u01/app/oracle/oradata/PROD/system01.dbf'SIZE325MREUSE

15SYSAUXDATAFILE'/u01/app/oracle/oradata/PROD/sysaux01.dbf'SIZE325MREUSE

16DEFAULTTABLESPACEusers

17DATAFILE'/u01/app/oracle/oradata/PROD/users01.dbf'

18SIZE50MREUSEAUTOEXTENDONMAXSIZEUNLIMITED

19DEFAULTTEMPORARYTABLESPACEtempts1

20TEMPFILE'/u01/app/oracle/oradata/PROD/temp01.dbf'

21SIZE20MREUSE

22UNDOTABLESPACEUNDOTBS1

23DATAFILE'/u01/app/oracle/oradata/PROD/undotbs01.dbf'

24SIZE50MREUSEAUTOEXTENDONMAXSIZEUNLIMITED;

Databasecreated.

SQL>selectstatusfromv$instance;

STATUS

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

OPEN

SQL>

--查看相关参数

SQL>showparametercontrol_files

NAMETYPEVALUE

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

control_filesstring/u01/app/oracle/oradata/PROD/o

ra_control1.ctl,/u01/app/orac

le/oradata/PROD/ora_control2.c

SQL>showparameterundo

NAMETYPEVALUE

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

undo_managementstringAUTO

undo_retentioninteger900

undo_tablespacestringUNDOTBS1

SQL>showparameterspfile

NAMETYPEVALUE

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

spfilestring/u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfilePROD.ora

SQL>

9：创建字典表及工具包

Default

--必执行脚本

SQL> conn / as sysdba

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

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

SQL> conn system/passwd

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

--可选脚本

SQL> conn / as sysdba

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

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

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

--必执行脚本

SQL>conn/assysdba

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

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

SQL>connsystem/passwd

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

--可选脚本

SQL>conn/assysdba

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

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

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

那天我捡了只猫

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle11g 建库,Oracle11g手工建库

测试环境：操作系统：64位OEL5.6数据库：Oracle11.2.0.41：配置环境变量(以下是本实验环境的配置)Default[oracle@prod ~]$ cat .bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific ...
复制链接

扫一扫