ORACLE10G手工创建数据库非ASM_oracle创建表不指定datafile就创建不了-CSDN博客

本文详细介绍了如何在没有备份的情况下重建数据库，包括创建SYS口令认证文件、设置环境变量、创建目录、创建PFILE、进入数据库进行配置、创建数据库、创建用户表空间、运行数据字典脚本以及更改系统默认表空间等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

公司的一个测试库,有人把数据文件,控制文件全删了,没有备份,想用数据库,就去手工创建了个库,以前光看别人手工去建,这次自己动手做了一次,碰到的唯一的一个问题就是PFILE里面必须有UNDO表空间的管理模式,否则就会在CREATE DATABASE(第6步)的时候就会报ORA-30014: operation only supported in Automatic Undo Management mode,以下是基于AIX5.2(AIX p2704u 3 5 000DCC0D4C00)安装的:

1.创建SYS口令认证文件
orapwd file=$ORACLE_HOME/dbs/orapwdb10 password=oracle entries=5

2.设置环境变量
ORACLE_SID=db10
ORACLE_BASE=/oracle10
ORACLE_HOME=$ORACLE_BASE/product/10.2

3.创建目录：
[p2704u]:[/oracle10]$ mkdir -p admin/db10/bdump 
[p2704u]:[/oracle10]$ mkdir -p admin/db10/udump
[p2704u]:[/oracle10]$ mkdir -p admin/db10/cdump
[p2704u]:[/oracle10]$ mkdir -p admin/db10/adump
[p2704u]:[/oracle10]$ mkdir oradata

4.进入$ORACLE_HOME/dbs下,创建PFILE(即initdb10.ora)

db_name=db10
db_cache_size=80M
db_files = 80
shared_pool_size = 500M   
processes = 150
log_buffer = 32768
max_dump_file_size = 10240
global_names = TRUE
undo_management=AUTO
control_files ='/oracle10/oradata/control01.ctl','/oracle10/oradata/control02.ctl'
audit_file_dest='/oracle10/admin/db10/adump'
background_dump_dest='/oracle10/admin/db10/bdump'
core_dump_dest='/oracle10/admin/db10/cdump'

5.进入数据库,打开到nomount状态:

[p2704u]:[/oracle10]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 27 22:02:33 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/oracle10/product/10.2/dbs/initdb10.ora';
ORACLE instance started.
Total System Global Area  637534208 bytes
Fixed Size                  2023024 bytes
Variable Size             549454224 bytes
Database Buffers           83886080 bytes
Redo Buffers                2170880 bytes
SQL> 

6.创建数据库DB10:

SQL> CREATE DATABASE  DB10
  2  USER SYS IDENTIFIED BY oracle
  3  USER SYSTEM IDENTIFIED by oracle
  4  LOGFILE
  5  GROUP 1 ('/oracle10/oradata/redo01.log') SIZE 50M,
  6  GROUP 2 ('/oracle10/oradata/redo02.log') SIZE 50M,
  7  GROUP 3 ('/oracle10/oradata/redo03.log') SIZE 50M
  8  MAXLOGFILES 5
  9  MAXLOGMEMBERS 3
 10  MAXLOGHISTORY 1
 11  MAXDATAFILES 100
 12  MAXINSTANCES 1
 13  CHARACTER SET ZHS16GBK
 14  NATIONAL CHARACTER SET AL16UTF16
 15  SYSAUX DATAFILE '/oracle10/oradata/sysaux01.dbf' SIZE 150M
 16  DATAFILE '/oracle10/oradata/system01.dbf' SIZE 400M 
 17  EXTENT MANAGEMENT LOCAL
 18  DEFAULT TEMPORARY TABLESPACE temp
 19  TEMPFILE '/oracle10/oradata/temp01.dbf' SIZE 40M
 20  UNDO TABLESPACE UNDOTBS 
 21  DATAFILE '/oracle10/oradata/undotdbs01.dbf' SIZE 200M;

Database created.

SQL> 

7.创建USER表空间:

SQL> CREATE TABLESPACE USERS LOGGING 
  2  DATAFILE '/oracle10/oradata//users01.dbf' 
  3  SIZE 1024M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE 2G
  4  EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> 

8.运行数据字典脚本:
@/oracle10/product/10.2/rdbms/admin/catalog.sql
@/oracle10/product/10.2/rdbms/admin/catproc.sql


9.更改系统默认表空间是USERS

ALTER DATABASE DEFAULT TABLESPACE USERS;