oracle手工建库窍门,oracle手工建库

1、建立口令文件，用于sys用户远程登录的认证(remote_login_passwordfile=exclusive)，位置

$ORACLE_HOME/dbs/orapwSID.创建命令： orapwd

[oracle@work dbs]$ orapwd file=orapwprod password=oracle entries=5

force=y

remote_login_passwordfile

1)none 拒绝sys用户从远程连接

2)exclusive sys用户可以从远程连接

3)share 多个库可以共享口令文件

SQL>ALTER SYSTEM SET remote_login_passwordfile=NONE SCOPE=SPFILE

【拒绝远程登录】

2、创建init parameter 文件

[oracle@oracle dbs]$more initdw.ora |grep -v '^#'|grep -v '^$'

>initlx02.ora

建立目录

[oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/bdump

[oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/cdump

[oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/udump

[oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/oradata/lx02【存放控制文件的位置】

修改初始化文件

[oracle@oracle dbs]$ vi initlx02.ora

db_name = lx02

sga_target = 300M

db_block_size = 8192

pga_aggregate_target = 30M

db_cache_size = 80M

shared_pool_size = 60M

parallel_threads_per_cpu = 4

optimizer_mode = choose

star_transformation_enabled = true

db_file_multiblock_read_count = 16

query_rewrite_enabled = true

query_rewrite_integrity = trusted

background_dump_dest = $ORACLE_BASE/admin/lx02/bdump

user_dump_dest = $ORACLE_BASE/admin/lx02/udump

core_dump_dest = $ORACLE_BASE/admin/lx02/cdump

control_files = $ORACLE_BASE/oradata/lx02/control01.ctl

undo_management = auto

undo_tablespace = rtbs

3、建立建库脚本

1、库名

2、表空间及数据文件的位置和大小

3、redo 日志文件的位置和大小

4、字符集

(1)建库脚本：vi  cr_db.sql

CREATE DATABASE anny

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/anny/redo01.log') SIZE 100M,

GROUP 2 ('/u01/app/oracle/oradata/anny/redo02.log') SIZE 100M,

GROUP 3 ('/u01/app/oracle/oradata/anny/redo03.log') SIZE 100M

MAXLOGFILES 10

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET zhs16gbk

NATIONAL CHARACTER SET AL16UTF16

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

EXTENT MANAGEMENT LOCAL(启动本地管理空闲区)

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

REUSE

DEFAULT TEMPORARY TABLESPACE temp

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

SIZE 20M REUSE

UNDO TABLESPACE rtbs【这个名字要和初始化文件的一致】

DATAFILE '/u01/app/oracle/oradata/anny/rtbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

【根据自己的实际需求更改】

告警日志信息：

create tablespace SYSTEM datafile  '$ORACLE_BASE/oradata/test/system01.dbf'

size 400m

default storage (initial 10K next 10K) online

Sat Aug 20 00:26:34 2011

Completed: create tablespace SYSTEM datafile

'$ORACLE_BASE/oradata/test/system01.dbf' size 400m

default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY

online

Sat Aug 20 00:26:34 2011

create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

Sat Aug 20 00:26:49 2011

Thread 1 advanced to log sequence 2

Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/test/redo02a.log

Sat Aug 20 00:26:50 2011

CREATE UNDO TABLESPACE RTBS DATAFILE  '$ORACLE_BASE/oradata/test/rtbs01.dbf'

size 100m

Sat Aug 20 00:26:51 2011

Successfully onlined Undo Tablespace 1.

Completed: CREATE UNDO TABLESPACE RTBS DATAFILE

'$ORACLE_BASE/oradata/test/rtbs01.dbf' size 100m

Sat Aug 20 00:26:51 2011

create tablespace SYSAUX datafile  '$ORACLE_BASE/oradata/test/sysaux01.dbf'

size 100m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

Completed: create tablespace SYSAUX datafile

'$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

Sat Aug 20 00:26:54 2011

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

'$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

'$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

Sat Aug 20 00:26:55 2011

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Sat Aug 20 00:26:55 2011

ALTER DATABASE DEFAULT TABLESPACE SYSTEM

Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM

Sat Aug 20 00:27:01 2011

SMON: enabling tx recovery

Sat Aug 20 00:27:02 2011

Threshold validation cannot be done before catproc is loaded.

replication_dependency_tracking turned off (no async multimaster replication

found)

Starting background process QMNC

QMNC started with pid=13, OS id=6485

Sat Aug 20 00:27:03 2011

Completed: create database test

user sys identified by **user system identified by *datafile

'$ORACLE_BASE/oradata/test/system01.dbf' size 400m

sysaux datafile '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

undo tablespace rtbs datafile '$ORACLE_BASE/oradata/test/rtbs01.dbf' size

100m

default temporary tablespace temp tempfile

'$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

logfile

group 1 '$ORACLE_BASE/oradata/test/redo01a.log' size 10m,

group 2 '$ORACLE_BASE/oradata/test/redo02a.log' size 10m,

group 3 '$ORACLE_BASE/oradata/test/redo03a.log' size 10m

character set zhs16gbk

(2)export  ORACLE_SID=lx02

(3)启动数据库到nomount状态，startup nomount

(4)启动告警日志  tail -f /$ORACLE_BASE/admin/lx02/bdump

(5)运行建库脚本：@/export/home/oracle/cr_db.sql

4、建立数据字典

创建数据字典脚本：vi  cr_dict.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/oracle

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

执行数据字典脚本：@/export/home/oracle/cr_dict.sql

5、创建users 表空间，作为普通用户的默认表空间

SQL> select tablespace_name from dba_tablespaces;【如果执行不成功，说明上一步有问题】

TABLESPACE_NAME

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

SYSTEM

RTBS

SYSAUX

TEMP

4 rows selected.

08:08:27 SQL> col file_name for a50

08:08:37 SQL> select file_id,file_name,tablespace_name from

dba_data_files;

FILE_ID FILE_NAME

TABLESPACE_NAME

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

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

1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM

2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS

3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX

SQL> create tablespace users

2   datafile '/u01/app/oracle/oradata/lx02/user01.dbf' size 100m;【创建表空间】

Tablespace created.

select * from database_properties；【查看数据库的属性】

SQL> alter database default tablespace users;【修改users的默认表空间】

Database altered.

08:10:45 SQL>  select file_id,file_name,tablespace_name from

dba_data_files;【查看数据文件】

FILE_ID FILE_NAME

TABLESPACE_NAME

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

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

1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM

2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS

3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX

4 /u01/app/oracle/oradata/lx02/user01.dbf            USERS

SQL> select file_name,file_id,tablespace_name from dba_temp_files;【查看临时表空间，临时表不和数据文件放在一起】

FILE_NAME                                             FILE_ID

TABLESPACE_NAME

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

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

/u01/app/oracle/oradata/anny/temp01.dbf                     1 TEMP

select username,default_tablespace,temporary_tablespace from

dba_users;【查看用户的默认表空间】

USERNAME        DEFAULT_TABLESP TEMPORARY_TABLESPACE

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

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

OUTLN           SYSTEM          TEMP

SYS             SYSTEM          TEMP

SYSTEM          SYSTEM          TEMP

SCOTT           USERS           TEMP

TOM             USERS           TEMP

DBSNMP          SYSAUX          TEMP

TSMSYS          USERS           TEMP

DIP             USERS           TEMP

4 rows selected.

6、添加scott 案例

SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql【运行该脚本就可以使用系统模板】

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

Production

With the Partitioning, OLAP and Data Mining options

到此，完成手工建库~~