创建Oracle数据库

4：通过实例管理命令在服务表里创建一个实例管理服务，设置启动方式为手动
        c:/>oradim -new -sid mydb -startmode manual
         -pfile "d:/oracle/product/10.1.0/admin/prod/pfile/initmydb.ora"
        ----》奇怪的是服务管理窗口中显示为启动，但关闭出错？ 
c:/>oradim -shutdown -sid mydb -usrpwd mydb
ora-01012: not logged on
        ----》启动出错    
c:/>oradim -startup -sid mydb -usrpwd mydb
ora-01078: failure in processing system parameters
lrm-00109: could not open parameter file
'd:/oracle/product/10.1.0/admin/mydb/pfile'
       -----》因为没创建spfile因此在此指定
c:/>oradim -startup -sid mydb -usrpwd mydb
-pfile "d:/oracle/product/10.1.0/admin/mydb/pfile/initmydb.ora"
ora-00205: error in identifying controlfile, check alert log for more info
d:/oracle/product/10.1.0/admin/mydb/bdump/alert_mydb.log中显示错误原因
大致意思是找不到控制文件，我想可能应该以nomount方式打开，但oradim没提供这个
选项，于是启动sqlplus。

c:/>sqlplus /nolog

sql*plus: release 10.1.0.2.0 - production on 星期三 4月 11 10:49:13 2007

copyright (c) 1982, 2004, oracle.       all rights reserved.

sql> connect /as sysdba
error:
ora-12560: tns:protocol adapter error

sql> connect sys/mydb as sysdba
error:
ora-12560: tns:protocol adapter error

sql> exit

c:/>set oracle_sid=mydb

c:/>sqlplus /nolog

sql*plus: release 10.1.0.2.0 - production on 星期三 4月 11 10:50:21 2007

copyright (c) 1982, 2004, oracle.       all rights reserved.

sql> conn sys/mydb as sysdba
connected.
 
sql> startup nomount
ora-01078: failure in processing system parameters
lrm-00109: could not open parameter file 'd:/oracle/product/10.1.0/admin/mydb/pfile'

sql> startup nomount pfile="d:/oracle/product/10.1.0/admin/mydb/pfile/initmydb.ora"
ora-01081: cannot start already-running oracle - shut it down first

sql> shutdown
ora-01507: database not mounted

oracle instance shut down.

sql> startup nomount pfile="d:/oracle/product/10.1.0/admin/mydb/pfile/initmydb.ora"
oracle instance started.

total system global area       171966464 bytes
fixed size                        787988 bytes
variable size                  145750508 bytes
database buffers                25165824 bytes
redo buffers                      262144 bytes

----》因为每次都要指定pfile不方便，可以创建一个
c:/>sqlplus /nolog

sql*plus: release 10.1.0.2.0 - production on 星期三 4月 11 11:02:57 2007

copyright (c) 1982, 2004, oracle.       all rights reserved.

sql> conn sys/mydb as sysdba
connected to an idle instance.
sql> create spfile from pfile;
create spfile from pfile
*
error at line 1:
ora-01078: failure in processing system parameters
lrm-00113: error when processing file
'd:/oracle/product/10.1.0/admin/mydb/pfile'
lrm-00109: could not open parameter file
'd:/oracle/product/10.1.0/admin/mydb/pfile'

----》上面的错误原因是第二步造成的，其中ifile="..../initmydb.ora"才行。
sql> create spfile from pfile;
file created.

----》再启动，不用指定pfile了
sql> startup nomount
oracle instance started.
......

sql> shutdown immediate
ora-01507: database not mounted
oracle instance shut down.

----》重新启动机器后，orcl数据库总是显示为已启动，mydb为未启动状态
这种情况下orcl其实是一个空闲进程，数据库nomount阶段，mydb则没启动进程，
所要连接到mydb会出现如下错误：
sql> conn sys/mydb as sysdba
error:
ora-12560: tns:protocol adapter error
可从服务管理器中启动oracleservicemydb则可以连接了。

----》下面两个操作有点奇怪，我创建的是实例，可使用inst启动不了，需要用srvc
c:/>oradim -startup -sid mydb -starttype inst -usrpwd mydb
ora-12560: tns:protocol adapter error

c:/>oradim -startup -sid mydb -starttype srvc -usrpwd mydb

5：创建数据库
sql> create database mydb
       2        logfile
       3        group 1
       4        ('d:/oracle/product/10.1.0/oradata/mydb/redo11.log',
       5         'd:/oracle/product/10.1.0/oradata/mydb/redo12.log') size 10m,
       6        group 2
       7        ('d:/oracle/product/10.1.0/oradata/mydb/redo21.log',
       8         'd:/oracle/product/10.1.0/oradata/mydb/redo22.log') size 10m
       9        datafile 'd:/oracle/product/10.1.0/oradata/mydb/system01.dbf' size 100m
10             autoextend on next 10m maxsize unlimited extent management local
11        sysaux datafile 'd:/oracle/product/10.1.0/oradata/mydb/sysaux01.dbf' size 50m
12        undo tablespace undotbs01 datafile 'd:/oracle/product/10.1.0/oradata/mydb/undotbs1.dbf' size 20m
13        default temporary tablespace temp
14        tempfile 'd:/oracle/product/10.1.0/oradata/mydb/temp01.dbf' size 10m
15        default tablespace users
16        datafile 'd:/oracle/product/10.1.0/oradata/mydb/users01.dbf' size 10m
17*       character set zhs16gbk
create database mydb
*
error at line 1:
ora-01092: oracle instance terminated. disconnection forced

----》查看alert_mydb.log发现错误出现在创建undotbs1.dbf后，
wed apr 11 13:48:19 2007
create undo tablespace undotbs01 datafile       'd:/oracle/product/10.1.0/oradata/mydb/undotbs1.dbf' size 20m

ora-30012 signalled during: create undo tablespace undotbs01 datafile       'd:/ora...
wed apr 11 13:48:21 2007
errors in file d:/oracle/product/10.1.0/admin/mydb/udump/mydb_ora_828.trc:
ora-00604: error occurred at recursive sql level 1
ora-30012: undo tablespace 'undotbs1' does not exist or of wrong type

----》为什么说undotbs1不存在？查看参数文件，发现这么两行
undo_management=auto
undo_tablespace=undotbs1

----》于是删除刚创建没成功的一些文件，然后修改创建数据库语句重新来
sql> conn sys/mydb as sysdba
connected to an idle instance.
sql> startup nomount
oracle instance started.

----》这次原本是创建成功了，但我为了记录错误原因，将创建好的数据库文件全部删除又来一次，
然后像下面这样修改，又处错了。
total system global area       171966464 bytes
fixed size                        787988 bytes
variable size                  145750508 bytes
database buffers                25165824 bytes
redo buffers                      262144 bytes
sql> l12
12*       undo tablespace undotbs01 datafile 'd:/oracle/product/10.1.0/oradata/mydb/undotbs1.dbf' size 20m
sql> c /undotbs01/undotbs1/
12*       undo tablespace undotbs1 datafile 'd:/oracle/product/10.1.0/oradata/mydb/undotbs1.dbf' size 20m
sql> run
......
create database mydb
*
error at line 1:
ora-01092: oracle instance terminated. disconnection forced

----》还是这个错误，但错误原因不一样了
wed apr 11 13:55:39 2007
errors in file d:/oracle/product/10.1.0/admin/mydb/udump/mydb_ora_1344.trc:
ora-01501: create database failed
ora-01991: invalid password file 'd:/oracle/product/10.1.0/db_1/database/pwdmydb.ora'

----》显示口令文件不可用，估计与刚才的删除有关，重新创建口令文件吧
见第三步
----》执行创建数据库的语句创建成功。

----》数据库创建后，自动处于open状态，所有v$...都可以查询，但其他则不可，如：
sql> select * from dba_users;
select * from dba_users
                   *
error at line 1:
ora-00942: table or view does not exist

----》解决上面的问题的方法是加载常用的数据字典包
sql>@d:/oracle/product/10.1.0/db_1/rdbms/admin/catalog.sql

----》使用system用户连接出现的问题
sql> conn system/zhiqiao
error:
ora-01017: invalid username/password; logon denied
warning: you are no longer connected to oracle.

sql> conn sys/mydb as sysdba
connected.

sql> grant connect to system identified by mydb;
grant succeeded.

sql> conn system/mydb
error:
ora-06550: line 1, column 7:
pls-00201: identifier 'dbms_application_info.set_module' must be declared
ora-06550: line 1, column 7:
pl/sql: statement ignored
error accessing package dbms_application_info
connected.
----》出现错误，但连接上了，连接orcl，或者用向导创建时没有这个问题。
sql> show user;
user is "system"

----》除了数据字典包外，通常还要加载以下包
1：pl/sql程序包       catproc.sql
2：远程数据复制       catrep.sql
3：java程序包       initjvm.sql
4：系统环境文件，必须以system用户登陆       db_1/sqlplus/admin/pupbld.sql

加载pl/sql程序包时出现很多错误，表或视图不存在，不知道为什么。