[20141218]关于dual.txt

[20141218]关于dual.txt

--昨天在群里有人问,数据库在mount状态下,打入什么命令,可以导致在后续执行alter database open的时候,数据库crash。
--实际上最简单的方法就是desc dual就可以出现这种现象,就是ora-4043错误。

--做一个测试:

SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYS@test> startup mount
ORACLE instance started.
Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.
SYS@test> desc dual
ERROR:
ORA-04043: object dual does not exist

SYS@test> host oerr ora 4043
04043, 00000, "object %s does not exist"
// *Cause:  An object name was specified that was not recognized by the system.
//          There are several possible causes:
//          - An invalid name for a table, view, sequence, procedure, function,
//          package, or package body was entered. Since the system could not
//          recognize the invalid name, it responded with the message that the
//          named object does not exist.
//          - An attempt was made to rename an index or a cluster, or some
//          other object that cannot be renamed.
// *Action: Check the spelling of the named object and rerun the code. (Valid
//          names of tables, views, functions, etc. can be listed by querying
//          the data dictionary.)

SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SYS@test> select open_mode from v$database ;
ERROR:
ORA-03114: not connected to ORACLE

--这个是因为在mount状态下,仅仅少量的视图可以访问,当访问到不存在的表与视图时,这部分信息已经加载到shared pool。
--开机以后,访问到这些对象被认为是不正常的,而dual表在open状态要使用,其结果直接导致实例crash。

--换成这样,就可以正常启动。

SYS@test> startup mount
ORACLE instance started.

Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.
SYS@test> desc dual
ERROR:
ORA-04043: object dual does not exist

SYS@test> alter system flush shared_pool;
System altered.

SYS@test> alter database open ;
Database altered.

--可以测试别的视图看看,比如dba_tables看看。

SYS@test> startup mount
ORACLE instance started.
Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.

SYS@test> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SYS@test> alter database open ;

Database altered.

SYS@test> select count(*) from dba_tables;
    COUNT(*)
------------
        1595

--这样没有问题。如果执行desc dba_tables看看。

SYS@test> startup mount
ORACLE instance started.
Total System Global Area    473956352 bytes
Fixed Size                    2084776 bytes
Variable Size               176160856 bytes
Database Buffers            285212672 bytes
Redo Buffers                 10498048 bytes
Database mounted.
SYS@test> desc dba_tables
ERROR:
ORA-04043: object dba_tables does not exist

SYS@test> alter database open ;

Database altered.

SYS@test> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SYS@test> alter system flush shared_pool;
System altered.

SYS@test> select count(*) from dba_tables;
    COUNT(*)
------------
        1595

--总之,如果在mount后出现,ora-04043错误,最简单的方法就是在open前,执行一次alter system flush shared_pool;。

时间: 2024-10-15 21:53:03

[20141218]关于dual.txt的相关文章

[20171127]dual.txt

[20171127]dual.txt --//我曾经提到如果在nomount,mount阶段,desc dual,启动到open阶段时,数据库会直接崩溃. --//链接:http://blog.itpub.net/267265/viewspace-1246984/ SYS@test> desc dual ERROR: ORA-04043: object dual does not exist SYS@test> alter database open; alter database open

Hive内置运算函数,自定义函数(UDF)和Transform

4.Hive函数 4.1内置运算符 内容较多,见<Hive官方文档>   4.2内置函数 内容较多,见<Hive官方文档> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF   测试各种内置函数的快捷方法: 1.创建一个dual表 create table dual(id string); 2.load一个文件(一行,一个空格)到dual表 hive> load data local inp

[20141218]误操作删除dual表的恢复.txt

[20141218]误操作删除dual表的恢复.txt --没事,做一个误操作删除dual表的恢复,没想到不能按照网上介绍的方法恢复,做一个记录. 1.建立测试数据库: mkdir -p /mnt/ramdisk mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk $ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName test -sid test -s

【基于WPF+OneNote+Oracle的中文图片识别系统阶段总结】之篇三:批量处理后的txt文件入库处理

篇三:批量处理后的txt文件入库处理 [开篇概述]:本文继上述文章再做深度剖析,本篇主要介绍txt入库(oracle)和wpf分页,在此篇涉及的页面设计前篇已经细述不再概说.里面涉及到oracle 64位系统安装处理问题,以前oracle的表,序列,触发器,自增种子等基本操作.外加文件操作和利用用户控件分页等技术.大致效果图如下,其次粘贴完整代码,其后就一一概说本文核心要点和难点突破,最后梳理技术知识点,使其一则保持完整性,二则梳理整理知识点便于随时取用.(本篇暂做效果图如下,至于最终效果图和审

JAVA已经去除TXT之中的2个值,如何插入JDBC?

问题描述 /****/packagecom.text.txt;importjava.io.BufferedReader;importjava.io.File;importjava.io.FileNotFoundException;importjava.io.FileReader;importjava.io.IOException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.ut

如何在postgresql中模拟oracle的dual表,来测试数据库最基本的连接功能?

还好,网上弄到的,,没有dual的数据库,可以试图用select函数不带from数据表的方式来实现返回值. 一段测试代码:   try: conn = psycopg2.connect(database=db.service_name, user=db.username, password=password, host=db.ip, port=db.port) cursor = conn.cursor() except Exception, e: context_dict = {'msg': e

[20161101]rman备份与数据文件变化7.txt

[20161101]rman备份与数据文件变化7.txt --//想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.... --//而且当时的测试很乱,自己主要一边做一边想.... --//链接: http://blog.itpub.net/267265/viewspace-2127386/ http://blog.itpub

[20171113]修改表结构删除列相关问题.txt

[20171113]修改表结构删除列相关问题.txt --//维护表结构删除字段一般都是先 ALTER TABLE <table_name> SET UNUSED (<column_name>); --//然后等空闲时候删除列. ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>; --//参考文档: https://docs.oracle.com/cd/E11882_01/server.1

[20171031]markhot.txt

[20171031]markhot.txt --//昨天看了https://jonathanlewis.wordpress.com/2017/10/02/markhot/,测试看看这样时候可以减少争用. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------