Oracle 手动创建数据库

Oracle手动创建数据库流程

1.Oracle环境配置

2.pfile配置文件编写

3.创建数据库

4.创建数据库数据字典

.bash_profile

[oracle@DG01 ~]$ cat /home/oracle/.bash_profile

### oracle base

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;export ORACLE_HOME

ORACLE_SID=pcqasdg0;export ORACLE_SID

ORACLE_UNQNAME=pcqasdg01;export ORACLE_UNQNAME

PATH=/usr/sbin:/$PATH;export PATH

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

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64;export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;export CLASSPATH

TNS_ADMIN=$ORACLE_HOME/network/admin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG

alias taillog='tail -f /u01/app/oracle/diag/rdbms/pcqasdg01/pcqasdg0/trace/alert_pcqasdg0.log'

alias dba='sqlplus "/as sysdba"'

alias dbs='cd $ORACLE_HOME/dbs'

alias arch='cd /u01/archive/pcqasdg0'

umask 022

if [ $USER = "oracle" ]; then

        if [ $SHELL = "/bin/ksh" ]; then

        ulimit -p 16384

        ulimit -n 65536

        else

        ulimit -u 16384 -n 65536

        fi

fi

pfile.ora

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

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

[oracle@DG01 dbs]$ vim initpcqasdg0.ora

db_name='pcqasdg0'

processes = 600

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

audit_trail ='none'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=4G

diagnostic_dest='/u01/app/oracle'

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

sec_case_sensitive_logon=FALSE

undo_tablespace='UNDOTBS1'

control_files = ('/u01/app/oracle/oradata/control01.ctl','/u01/app/oracle/oradata/control02.ctl','/u01/app/oracle/fast_recovery_area/control03.ctl')

compatible ='11.2.0.0.0'

sessions=1800

processes=900

sga_max_size=32G

sga_target=32G

db_files=1024

db_unique_name='pcqasdg01'

create.sql

[oracle@DG01 dbs]$ mkdir /u01/app/oracle/redo -p

[oracle@DG01 dbs]$ vim /u01/new_database_create.sql

create database pcqasdg0

user sys identified by "SYS"

user system identified by "SYSTEM"

maxlogfiles 16

maxlogmembers 5

maxdatafiles 1024

maxinstances 8

maxloghistory 291

--logfiles

logfile group 1('/u01/app/oracle/redo/redo01.1.log','/u01/app/oracle/redo/redo01_2.log') size 100M,

group 2('/u01/app/oracle/redo/redo02_1.log','/u01/app/oracle/redo/redo02_2.log') size 100M,

group 3('/u01/app/oracle/redo/redo03_1.log','/u01/app/oracle/redo/redo03_2.log') size 100M

--datafiles

datafile '/u01/app/oracle/oradata/pcqasdg0/system01.dbf' size 512M reuse extent management local

SYSAUX datafile '/u01/app/oracle/oradata/pcqasdg0/sysaux01.dbf' size 512M reuse

default temporary tablespace TEMP tempfile '/u01/app/oracle/oradata/pcqasdg0/temp01.dbf' size 128M reuse

undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/pcqasdg0/undotbs01.dbf' size 64M reuse autoextend on maxsize unlimited

character set al32utf8

;

建库开始

mkdir -p /u01/app/oracle/admin/pcqasdg0/adump

mkdir -p /u01/app/oracle/fast_recovery_area

[oracle@DG01 oracle]$ mkdir /u01/app/oracle/oradata/pcqasdg0 -p

[oracle@DG01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 26 14:19:52 2015

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

Connected to an idle instance.

SQL>

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpcqasdg0.ora';

LRM-00101: unknown parameter name 'db_file'

ORA-01078: failure in processing system parameters

SQL> !vim $ORACLE_HOME/dbs/initpcqasdg0.ora

db_file-->db_files

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpcqasdg0.ora';

ORA-00371: not enough shared pool memory, should be atleast 778462822 bytes

SQL> !vim $ORACLE_HOME/dbs/initpcqasdg0.ora

Add 

sga_target=96G

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpcqasdg0.ora';

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

SQL> !mkdir -p /u01/app/oracle/admin/pcqasdg0/adump

SQL> !mkdir -p /u01/app/oracle/fast_recovery_area

SQL> !mkdir -p /u01/app/oracle/oradata/pcqasdg0

SQL>  startup nomount pfile='$ORACLE_HOME/dbs/initpcqasdg0.ora';

ORACLE instance started.

Total System Global Area 1.0262E+11 bytes

Fixed Size                  2238472 bytes

Variable Size            6979323896 bytes

Database Buffers         9.5563E+10 bytes

Redo Buffers               74444800 bytes

SQL> !rm /u01/app/oracle/oradata/control*

SQL> !rm /u01/app/oracle/fast_recovery_area/control*

SQL> @/u01/new_database_create.sql

Database created.

查看数据库状态

SQL> select status from v$instance;

STATUS

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

OPEN

设定datafiles为自动扩展

SQL> CREATE TABLESPACE USERS DATAFILE

  '/u01/app/oracle/oradata/pcqasdg0/user01.dbf' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;  2    3    4    5    6    7    8    9

Tablespace created.

SQL> col name for a60

SQL> select file#,name from v$datafile union select file#,name from v$tempfile;

     FILE# NAME

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

         1 /u01/app/oracle/oradata/pcqasdg0/system01.dbf

         1 /u01/app/oracle/oradata/pcqasdg0/temp01.dbf

         2 /u01/app/oracle/oradata/pcqasdg0/sysaux01.dbf

         3 /u01/app/oracle/oradata/pcqasdg0/undotbs01.dbf

         4 /u01/app/oracle/oradata/pcqasdg0/user01.dbf

SQL> alter database datafile 1 autoextend on;

SQL>alter database datafile 2 autoextend on;

SQL>alter database datafile 3 autoextend on;

SQL>alter database datafile 4 autoextend on;

更新数据库数据字典

[oracle@DG01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 26 15:48:19 2015

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

Connected to:

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

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

SQL> spool /u01/cate.log

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> select owner,object_type, object_name from dba_objects where status='INVALID' order by object_name;

no rows selected

查看datafiles扩展

SQL>  col FILE_NAME for a60

SQL>  select FILE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME                                                                            AUT

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

/u01/app/oracle/oradata/pcqasdg0/system01.dbf                YES

/u01/app/oracle/oradata/pcqasdg0/sysaux01.dbf                YES

/u01/app/oracle/oradata/pcqasdg0/undotbs01.dbf               YES

/u01/app/oracle/oradata/pcqasdg0/user01.dbf                     YES

创建密码文件

如未创建密码文件客户端使用sysdba权限时会报ORA-01031: insufficient privileges

[oracle@DG01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=password entries=5 force=y

注意密码文件格式：【orapw+oracle_sid】

SQL> select * from v$pwfile_users;

其他參考
http://blog.itpub.net/519536/viewspace-667563