终于决定重装oracle 10g了,跟着ocp教程认真学习一下。
1 安装oracle软件
2 用DBCA的模板创建一个database。这次总算是记得选中“生成数据库创建脚本”了。。。
3 用Net Manager创建listener和配置tnsnames
4 在Toad中用scott/tiger连接成功,ok
学习eygle为告警日志创建外部表:
SQL> create or replace
directory bdump
2 as
‘D:\oracle\admin\ora10g\bdump’;
目录已创建。
SQL> create table
alert_log ( text varchar2(400) )
2 organization external (
3 type oracle_loader
4 default directory BDUMP
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile 10 ) 11 location('alert_ocp10g.log') 12 ) 13 reject limit unlimited 14 /
表已创建。
SQL> select * from
alert_log where text like 'ORA-%';
TEXT
--------------------------------------------------------------------------------
ORA-1109 signalled
during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: ??????? 1 (???? 1) ???
ORA-00312: ???? 1 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO01B.LOG'
ORA-27041: ??????
ORA-00312: ???? 1 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO01A.LOG'
ORA-27041: ??????
ORA-00313: ??????? 2 (???? 1) ???
ORA-00312: ???? 2 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO02B.LOG'
ORA-27041: ??????
ORA-00312: ???? 2 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO02A.LOG'
ORA-27041: ??????
TEXT
--------------------------------------------------------------------------------
ORA-00313: ??????? 3
(???? 1) ???
ORA-00312: ???? 3 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO03B.LOG'
ORA-27041: ??????
ORA-00312: ???? 3 ?? 1:
'D:\ORACLE\ORADATA\ORA10G\REDO03A.LOG'
ORA-27041: ??????
ORA-1109 signalled during: ALTER DATABASE CLOSE
NORMAL...
已选择17行。
刚创建居然就有这么多异常,囧。。。 去网上一个个的慢慢查吧。。。
前面几个异常ORA-00313、ORA-00312和ORA-27041:在相应的trace文件中可以看到还伴随有一个操作系统错误:
OSD-04002:
无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
因此我猜测这些异常应该是数据库还在创建过程中产生的吧,当时重做日志文件还没生成,因为之后用sqlplus就能正常的startup了,也没有再报错了。可能需要去进一步证实。。
关于最后一个异常ORA-1109:网上搜到这么一段解释
From time to time you see messages on public forums or Metalink that
complain about problems with alter database
close (or close normal) as if
it were a legitimate SQL command. My test on Oracle 8.1.7.0 and
9.0.1.1.1 indicate that this command completes "correctly" with
feedback "Database altered" and apparently leaves the database in
mounted stage (v$database.open_mode = 'MOUNTED'). But after that
you can't alter database open, which
would return ORA-1531 ("a database already open by the instance").
So you have to shutdown which returns
ORA-1109 ("database not open"). After that, startup works fine and everything is back to
normal.
The moral of the story is that, just because you see
ALTER DATABASE CLOSE NORMAL
in alert.log doesn't mean it's a SQL command you can use. In fact
there's never been this command in Oracle documentation SQL
Reference (checked in SQL Reference for Oracle7 and later
versions). The only place in documentation as well as the message
file oraus.msg you can find ALTER
DATABASE CLOSE is ORA-1093 and ORA-1094. I think having
these two errors is misleading. Other things Oracle can do
internally but we cannot are ALTER
DATABASE DISMOUNT, SELECT
PIECE FROM SYS.IDL_SB4$ (seen in v$sql or SQL
trace).
原来我在创建完数据库之后,到mount模式下设置过archivelog和flashback_on,然后没有open就直接shutdown了,从而产生了ORA-1109异常。
为啥日志里的中文会变问号呢?这个问题很困扰啊。。。。
1 首先,select * from
nls_database_parameters;发现NLS_CHARACTERSET确实是ZHS16GBK
2 注册表中的NLS_LANG是SIMPLIFIED CHINESE_CHINA.ZHS16GBK,也没问题
随便建一个表,插入一个中文值,用sqlplus的select能正常显示中文
SQL> select * from
scott.test;
A
------------------------------
中文中文中文
此时,为了模拟控制文件受损,在同一个sqlplus窗口中先shutdown immediate;
SQL> shutdown
immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
然后手动去删掉一个controlfile,再在sqlplus里startup;
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 373293056 bytes
Fixed
Size 1249080 bytes
Variable
Size 142606536 bytes
Database
Buffers 226492416 bytes
Redo
Buffers 2945024 bytes
ORA-00205: ?????????, ??????,
???????
乱码出现了!!!!去查看alert文件,也有乱码
ORA-00202:
????:
''D:\ORACLE\ORADATA\ORA10G\CONTROL03.CTL''
ORA-27041: ??????
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
此时,手动恢复控制文件之后,再在sqlplus里mount并open数据库,select就变乱码了。。。
SQL> alter database
mount;
数据库已更改。
SQL> alter database
open;
数据库已更改。
SQL> select * from
scott.test;
A
------------------------------
??????
太奇怪了,如果只是简单的shutdown和startup,sqlplus中还是可以正常的显示中文,仅仅是中间加了一次异常错误,字符集就乱了。。。但是如果重开一个sqlplus窗口或者在当前窗口退出再重进sqlplus,select出来就没问题了,中文正常显示。
这是为啥啊???难道是负责报异常和写alert.log的后台进程在捣鬼??向数据库大牛dbsnake咨询了一下,觉得问题应该还是跟NLS_LANG有关。希望能尽快找到答案。。。
P.S. 刚才又跟dbsnake请教了下,应该是当前session的NLS_LANG在异常之后被改变了。。
异常之前:
SQL> SELECT * FROM
NLS_SESSION_PARAMETERS;
PARAMETER VALUE
---------------------------- ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
异常之后:
SQL> SELECT * FROM
NLS_SESSION_PARAMETERS;
PARAMETER VALUE
----------------------------- ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ?
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
可以看到,NLS_CURRENCY已经由¥变为?了,说明NLS_LANG肯定变化了。
此外,还观察到一个现象:并不是所有的异常发生之后都会改变当前session的NLS_LANG,比如ORA-1109异常就不会改变NLS_LANG,但是ORA-00205就肯定会改变。
Anyway,先尽可能详细地记录下来吧,以后再慢慢研究。。。真的是很感谢dbsnake的热心帮忙,从中我也学到了不少Oracle的知识,嘿嘿~