目录
ORA-01012: 和 SP2-1545
代号错误详情：
ORA-01012: not logged on
SP2-1545: This feature requires Database availability.
前提：使用export ORACLE_PDB_SID=pdb名登录数据库，后出现报错
临时解决方法
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">shutdown</span> adort;
startup;</span></span><span style="background-color:#f8f8f8"><span style="color:#333333">2.永久解决办法（❗还没尝试）
不再使用shutdown 不跟参数的方式关闭数据库</span></span>参考：ORA-01012 ：not logged on的原因及其解决办法 - 一只竹节虫 - 博客园 (cnblogs.com)
ORA-01034
代号错误详情：ORA-01034 ORACLE not avaiable
前提：开启数据库，连接到一个idle insstance(空闲实例)
解决：
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#aa5500">#先启动监听</span>
<span style="color:#999977">[</span>oracle<span style="color:#0055aa">@localhost</span> ~<span style="color:#999977">]</span>$ lsnrctl <span style="color:#770088">start</span>
<span style="color:#aa5500">#再开始数据库</span>
<span style="color:#770088">SQL</span><span style="color:#981a1a">></span> startup;</span></span>登录后还是空实例，出现以下则说明没有打开数据库
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">SQL</span><span style="color:#981a1a">></span> startup
ORA<span style="color:#981a1a">-</span><span style="color:#116644">00838</span>: Specified <span style="color:#770088">value</span> of MEMORY_TARGET <span style="color:#770088">is</span> too small, needs <span style="color:#770088">to</span> be <span style="color:#770088">at</span> least <span style="color:#116644">1888</span>M
ORA<span style="color:#981a1a">-</span><span style="color:#116644">01078</span>: failure <span style="color:#770088">in</span> processing <span style="color:#3300aa">system</span> parameters</span></span>
再次解决:
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#999977">[</span>oracle<span style="color:#0055aa">@localhost</span> dbs<span style="color:#999977">]</span>cd $ORACLE_HOME/dbs
<span style="color:#aa5500">#查看是否有spfilexx.ora的文件</span>
<span style="color:#aa5500">#进入sql模式,创建一个新的文件</span>
<span style="color:#770088">SQL</span><span style="color:#981a1a">></span> <span style="color:#770088">create</span> pfile<span style="color:#981a1a">=</span><span style="color:#aa1111">'pfilexx.ora'</span> <span style="color:#770088">from</span> spfile<span style="color:#981a1a">=</span><span style="color:#aa1111">'spfilexx.ora'</span>;
<span style="color:#aa5500">#文件创建成功</span>
File created.
<span style="color:#aa5500">#退出sql模式</span>
<span style="color:#999977">[</span>oracle<span style="color:#0055aa">@localhost</span> dbs<span style="color:#999977">]</span>vi sprifliexx<span style="color:#0055aa">.ora</span>
<span style="color:#aa5500">#修改memory_target，单位B</span>
<span style="color:#aa5500">#上面图片显示至少需要1888M，即1888*1024*1024=1979 711 488</span>
<span style="color:#aa5500">#将*.memory_target=1979711488</span>
<span style="color:#aa5500">#保存退出</span>
<span style="color:#aa5500">#进入sql模式，进新建的pfilexx.ora文件覆盖旧的文件spfilexx.ora</span>
<span style="color:#aa5500">#再次退出sql模式，再次进入sql模式</span>
<span style="color:#770088">SQL</span><span style="color:#981a1a">></span>startup
<span style="color:#aa5500">#我们在去查看内存参数，show parameter memory，就会发现内存参数已经改回原来的值了。</span>
<span style="color:#770088">SQL</span><span style="color:#981a1a">></span><span style="color:#770088">show</span> parameter memory_target;</span></span>
😄成功解决！
ORA-01078与LRM-00109
代号错误详情：
前提：新建的一个kfc数据库执行startup命令后出现以下错误
ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/19.3.0/db_1/dbs/initkfc.ora'
oracle11g ORA-01078与LRM-00109 解决方法（详细）_win2019 oracle11g ora01078 lrm00109-CSDN博客
ORA-01157 ORA01109
代号错误详情：
ORA-01157: cannot identify/lock data file 20 - see DBWR trace file
ORA-01110: data file 20: /u01/app/oracle/oradata/0RCL/LZJ25/ zj25.dbf!
前提：
解决：
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">shutdown</span> immediate;
startup mount;
<span style="color:#770088">select</span> file<span style="color:#aa5500">#,name, status from v$datafile;</span>
<span style="color:#aa5500">#查看相对应的文件，将其下线</span>
<span style="color:#770088">alter</span> <span style="color:#770088">database</span> datafile ‘/u01/app/oracle/oradata/ORCL/lzj25<span style="color:#0055aa">.dbf</span>’ <span style="color:#770088">offline</span> <span style="color:#770088">drop</span>;
<span style="color:#770088">Database</span> altered.
<span style="color:#aa5500">#将文件状态由ONLINE变为RECOVER；</span>
<span style="color:#770088">alter</span> <span style="color:#770088">database</span> <span style="color:#770088">open</span>;</span></span>ORA-01219
代号错误详情：ORA-01219:数据库或插接式数据库未打开
前提：
使用select * from all_users查看系统下的所有用户
报错：
第 1 行出现错误: ORA-01219: 数据库或插接式数据库未打开: 仅允许在固定表或视图中查询
解决：
通过cmd窗口连接数据库
<span style="background-color:#f8f8f8"><span style="color:#333333">salplus / <span style="color:#770088">as</span> sysdba</span></span>连接数据库后，使用以下命令打开数据库
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">SQL</span><span style="color:#981a1a">></span> <span style="color:#770088">alter</span> <span style="color:#770088">database</span> <span style="color:#770088">open</span>;
<span style="color:#770088">alter</span> <span style="color:#770088">database</span> <span style="color:#770088">open</span>
<span style="color:#981a1a">*</span>
第 <span style="color:#116644">1</span> 行出现错误:
ORA<span style="color:#981a1a">-</span><span style="color:#116644">01157</span>: 无法标识/锁定数据文件 <span style="color:#116644">5</span> <span style="color:#981a1a">-</span> 请参阅 DBWR 跟踪文件
ORA<span style="color:#981a1a">-</span><span style="color:#116644">01110</span>: 数据文件 <span style="color:#116644">5</span>: <span style="color:#aa1111">'A:\ORACLE\DATABASE\ITXG.DBF'</span></span></span>❗：说明错误出现在此处。
<span style="background-color:#f8f8f8"><span style="color:#333333"> <span style="color:#770088">alter</span> <span style="color:#770088">database</span> datafile <span style="color:#aa1111">'A:\ORACLE\DATABASE\ITXG.DBF'</span> <span style="color:#770088">offline</span> <span style="color:#770088">drop</span>;
数据库已更改。</span></span>重新打开
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">alter</span> <span style="color:#770088">database</span> <span style="color:#770088">open</span>;
数据库已更改。</span></span>查询
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">SQL</span><span style="color:#981a1a">></span> <span style="color:#770088">select</span> <span style="color:#981a1a">*</span><span style="color:#770088">from</span> all_users;
USERNAME
<span style="color:#981a1a">--------------------------------------------------------------------------------</span>
USER_ID CREATED COMMON <span style="color:#770088">OR</span> INHERI
<span style="color:#981a1a">----------</span> <span style="color:#981a1a">--------------</span> <span style="color:#981a1a">------</span> <span style="color:#aa5500">-- ------</span>
DEFAULT_COLLATION
<span style="color:#981a1a">--------------------------------------------------------------------------------</span>
IMPLIC ALL_SH
<span style="color:#981a1a">------</span> <span style="color:#981a1a">------</span>
SYS
<span style="color:#116644">0</span> <span style="color:#116644">30</span><span style="color:#981a1a">-</span><span style="color:#116644">5</span>月 <span style="color:#981a1a">-</span><span style="color:#116644">19</span> YES Y YES
USING_NLS_COMP
<span style="color:#770088">NO</span> <span style="color:#770088">NO</span>
USERNAME
<span style="color:#981a1a">--------------------------------------------------------------------------------</span>
USER_ID CREATED COMMON <span style="color:#770088">OR</span> INHERI
<span style="color:#981a1a">----------</span> <span style="color:#981a1a">--------------</span> <span style="color:#981a1a">------</span> <span style="color:#aa5500">-- ------</span>
DEFAULT_COLLATION
<span style="color:#981a1a">--------------------------------------------------------------------------------</span>
IMPLIC ALL_SH
<span style="color:#981a1a">------</span> <span style="color:#981a1a">------</span>
AUDSYS
<span style="color:#116644">8</span> <span style="color:#116644">30</span><span style="color:#981a1a">-</span><span style="color:#116644">5</span>月 <span style="color:#981a1a">-</span><span style="color:#116644">19</span> YES Y YES
USING_NLS_COMP
<span style="color:#770088">NO</span> <span style="color:#770088">NO</span>
...</span></span>查找成功。😄
ORA-02065
解决：
alter system set memory_target=1456M SCOPE-SPFILE;参考链接：
Oracle初始化参数之memory_target - 小y - 博客园 (cnblogs.com)
ORA-28002
代号错误详情：oracle密码过期处理
问题：密码未做修改，提示无法登陆，过期！
ERROR:ORA-28002: the password will expire within 7 days
解决方案：
查询密码策略:DEFAULT
在管理员用户中查询dba_users数据字典
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">desc</span> dba_users;</span></span><span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">select</span> username,<span style="color:#770088">profile</span> <span style="color:#770088">from</span> dba_users;</span></span>从查询结果可知，大多数用户策略都为DEFAULT
<span style="background-color:#f8f8f8"><span style="color:#333333">USERNAME <span style="color:#770088">PROFILE</span>
<span style="color:#981a1a">--------------------</span> <span style="color:#981a1a">--------------------</span>
SYS <span style="color:#770088">DEFAULT</span>
<span style="color:#3300aa">SYSTEM</span> <span style="color:#770088">DEFAULT</span>
XS$NULL <span style="color:#770088">DEFAULT</span>
OJVMSYS <span style="color:#770088">DEFAULT</span>
LBACSYS <span style="color:#770088">DEFAULT</span>
OUTLN <span style="color:#770088">DEFAULT</span>
SYS$UMF <span style="color:#770088">DEFAULT</span>
DBSNMP <span style="color:#770088">DEFAULT</span>
APPQOSSYS <span style="color:#770088">DEFAULT</span>
DBSFWUSER <span style="color:#770088">DEFAULT</span>
GGSYS <span style="color:#770088">DEFAULT</span>
...</span></span>以上的defualt策略是如何管理，需要我们查询数据字典dba_profiles;
<span style="background-color:#f8f8f8"><span style="color:#333333"> <span style="color:#770088">select</span> <span style="color:#981a1a">*</span> <span style="color:#770088">from</span> dba_profiles;</span></span>查询结果中可以找到PASSWORD_LIFE_TIME这一行，可知密码的存活时间为180天
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">PROFILE</span> RESOURCE_NAME RESOURCE_TYPE <span style="color:#770088">LIMIT</span> COMMON INHERI IMPLIC
<span style="color:#981a1a">-------</span> <span style="color:#981a1a">-----------------</span> <span style="color:#981a1a">---------------</span> <span style="color:#981a1a">-----</span> <span style="color:#981a1a">------</span> <span style="color:#981a1a">------</span> <span style="color:#981a1a">-----</span>
<span style="color:#770088">DEFAULT</span> PASSWORD_LIFE_TIME <span style="color:#770088">PASSWORD</span> <span style="color:#116644">180</span> <span style="color:#770088">NO</span> <span style="color:#770088">NO</span> <span style="color:#770088">NO</span></span></span>❗所以修改PASSWORD_LIFE_TIME的时间为无限制（解决指令）
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">alter</span> <span style="color:#770088">profile</span> <span style="color:#770088">default</span> <span style="color:#770088">limit</span> PASSWORD_LIFE_TIME unlimited;</span></span>❗修改好后要给用户修改以下密码，否则前面的操作无效
<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">alter</span> <span style="color:#770088">user</span> <span style="color:#3300aa">system</span> identified <span style="color:#770088">by</span> Sa123456;</span></span>问题解决😄
未完待续❗❗
扫一扫
4086
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
