Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

75

积分

1

好友

8

主题
1#
发表于 2012-12-22 16:45:04 | 查看: 28795| 回复: 11
环境描述:
OS level  :AIX 6100-06
GI:11.2.0.3
DB:11.2.0.3
ASM

数据库alert日志每隔几分钟出现同样错误信息:
  1. ……
  2. Sat Dec 22 16:36:17 2012
  3. Errors in file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc:
  4. ORA-00604: error occurred at recursive SQL level 1
  5. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  6. Errors in file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc:
  7. ORA-00604: error occurred at recursive SQL level 1
  8. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  9. Sat Dec 22 16:36:27 2012
  10. Errors in file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc:
  11. ORA-00604: error occurred at recursive SQL level 1
  12. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  13. Errors in file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc:
  14. ORA-00604: error occurred at recursive SQL level 1
  15. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  16. Sat Dec 22 16:36:37 2012
  17. Errors in file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc:
  18. ORA-00604: error occurred at recursive SQL level 1
  19. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  20. ……
复制代码
trace文件显示内容如下:
  1. Trace file /home/oracle/diag/rdbms/jhcxjm/jhcxjm1/trace/jhcxjm1_cjq0_6881530.trc
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  3. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
  4. Data Mining and Real Application Testing options
  5. ORACLE_HOME = /oracle/11.2
  6. System name:    AIX
  7. Node name:      jhcx01
  8. Release:        1
  9. Version:        6
  10. Machine:        0005A84BD600
  11. Instance name: jhcxjm1
  12. Redo thread mounted by this instance: 1
  13. Oracle process number: 42
  14. Unix process pid: 6881530, image: oracle@jhcx01 (CJQ0)


  15. *** 2012-12-22 06:00:00.036
  16. *** SESSION ID:(586.713) 2012-12-22 06:00:00.036
  17. *** CLIENT ID:() 2012-12-22 06:00:00.036
  18. *** SERVICE NAME:(SYS$BACKGROUND) 2012-12-22 06:00:00.036
  19. *** MODULE NAME:() 2012-12-22 06:00:00.036
  20. *** ACTION NAME:() 2012-12-22 06:00:00.036

  21. Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
  22. Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

  23. *** 2012-12-22 14:44:41.701

  24. *** 2012-12-22 14:44:42.455
  25. ORA-00604: error occurred at recursive SQL level 1
  26. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors

  27. *** 2012-12-22 14:44:47.509
  28. ORA-00604: error occurred at recursive SQL level 1
  29. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors

  30. *** 2012-12-22 14:44:52.512
  31. ORA-00604: error occurred at recursive SQL level 1
  32. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors

  33. *** 2012-12-22 14:44:57.515
  34. ORA-00604: error occurred at recursive SQL level 1
  35. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors

  36. *** 2012-12-22 14:45:02.518
  37. ORA-00604: error occurred at recursive SQL level 1
  38. ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
  39. ……
复制代码
初步诊断是SYS.DBMS_LOGSTDBY包体失效,但是通过查看dba_objects视图,发现有大量失效对象:
  1. SQL> select object_name from dba_objects where status like '%INVALID%' and owner='SYS' and object_name like '%DBMS%';

  2. OBJECT_NAME
  3. --------------------------------------------------
  4. DBMS_XSTREAM_UTL_IVK
  5. DBMS_XSTREAM_ADM_UTL
  6. DBMS_STREAMS_SM
  7. DBMS_STREAMS_MC
  8. DBMS_STREAMS_AUTO_INT
  9. DBMS_STREAMS_ADM
  10. DBMS_RCVMAN
  11. DBMS_LOGSTDBY
  12. DBMS_LOGREP_UTIL
  13. DBMS_LOGMNR_SESSION
  14. DBMS_LOGMNR_LOGREP_DICT
  15. DBMS_LOGMNR_INTERNAL
  16. DBMS_LOGMNR_FFVTOLOGMNRT
  17. DBMS_LOGMNR
  18. DBMS_INTERNAL_LOGSTDBY
  19. DBMS_CAPTURE_SWITCH_INTERNAL
  20. DBMS_CAPTURE_SWITCH_ADM
  21. DBMS_CAPTURE_PROCESS
  22. DBMS_CAPTURE_ADM_INTERNAL
  23. DBMS_CAPTURE_ADM
  24. DBMS_APPLY_HANDLER_ADM
  25. DBMS_APPLY_ADM_INTERNAL
  26. DBMS_APPLY_ADM

  27. 23 rows selected.
复制代码
再次查看alert日志,发现在14:37分左右,有人执行过drop tablespace操作:
  1. /* OracleOEM */ CREATE SMALLFILE TABLESPACE "STREAM_TBS" LOGGING DATAFILE 'stream01.ora' SIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT
  2. SPACE MANAGEMENT  AUTO
  3. Sat Dec 22 14:41:05 2012
  4. Completed: /* OracleOEM */ CREATE SMALLFILE TABLESPACE "STREAM_TBS" LOGGING DATAFILE 'stream01.ora' SIZE 2048M EXTENT MANAGEMENT LOC
  5. AL SEGMENT SPACE MANAGEMENT  AUTO
  6. Sat Dec 22 14:44:31 2012
  7. /* OracleOEM */ DROP TABLESPACE "STREAM_TBS" INCLUDING CONTENTS  CASCADE CONSTRAINTS
复制代码
后确认是开发人员误操作所致。

我的问题:
是否可以重建这些包体???
2#
发表于 2012-12-22 20:06:06
FYI
  1. [oracle@vrh8 admin]$ grep -i "DBMS_LOGSTDBY" *
  2. a0902000.sql:execute dbms_logstdby.set_tablespace('SYSAUX');
  3. a1001000.sql:Rem    ajadams     06/22/04 - conditionally call dbms_logstdby.set_tablespace
  4. a1001000.sql:    dbms_logstdby.set_tablespace('SYSAUX');
  5. c1001000.sql:Rem    ajadams     05/09/05 - dbms_logstdby_public package depricated
  6. c1001000.sql:drop package DBMS_LOGSTDBY_PUBLIC;
  7. dbmslsby.sql:Rem      dbms_logstdby package definition.
  8. dbmslsby.sql:Rem    ajadams     05/02/05 - dbms_logstdby_public now deprecated
  9. dbmslsby.sql:Rem    sslim       06/06/04 - fast failover: dbms_logstdby.rebuild
  10. dbmslsby.sql:Rem    raguzman    06/20/03 - dbms_logstdby should be invokers rights
  11. dbmslsby.sql:Rem    jnesheiw    09/03/02 - create DBMS_LOGSTDBY_PUBLIC package
  12. dbmslsby.sql:CREATE OR REPLACE PACKAGE sys.dbms_logstdby AUTHID CURRENT_USER IS
  13. dbmslsby.sql:--      this proc is here not dbms_logstdby_internal because
  14. dbmslsby.sql:END dbms_logstdby;
  15. dbmslsby.sql:CREATE OR REPLACE PUBLIC SYNONYM dbms_logstdby FOR sys.dbms_logstdby;
  16. dbmslsby.sql:-- Revoke execute on DBMS_LOGSTDBY from public.  If it has already
  17. dbmslsby.sql:   execute immediate 'REVOKE EXECUTE ON dbms_logstdby FROM public';
  18. dbmslsby.sql:GRANT EXECUTE ON dbms_logstdby TO dba;
  19. dbmslsby.sql:GRANT EXECUTE ON dbms_logstdby TO logstdby_administrator;
  20. prvtlsby.plb:CREATE OR REPLACE LIBRARY sys.dbms_logstdby_lib wrapped
  21. prvtlsby.plb:CREATE OR REPLACE PACKAGE BODY sys.dbms_logstdby wrapped
  22. [oracle@vrh8 admin]$
  23. [oracle@vrh8 admin]$
  24. [oracle@vrh8 admin]$ pwd
  25. /s01/oracle/product/10.2.0.5/db_1/rdbms/admin




  26. @dbmslsby.sql
  27. @prvtlsby.plb
复制代码

回复 只看该作者 道具 举报

3#
发表于 2012-12-22 20:24:47
谢谢刘大恢复,之前我已尝试过:
  1. @?/rdbms/admin/dbmslm.sql
  2. @?/rdbms/admin/dbmslmd.sql
  3. @?/rdbms/admin/dbmslms.sql
  4. @?/rdbms/admin/prvtlm.plb
  5. @?/rdbms/admin/prvtlmd.plb
  6. @?/rdbms/admin/prvtlmrd.plb
  7. @?/rdbms/admin/prvtlms.plb
  8. @?/rdbms/admin/prvtlms2.plb
  9. @?/rdbms/admin/catpstr.sql

  10. @?/rdbms/admin/utlrp.sql
复制代码
按照你的方法,执行后问题依然存在:
  1. SQL> @?/rdbms/admin/dbmslsby.sql

  2. Package created.

  3. No errors.

  4. Synonym created.


  5. PL/SQL procedure successfully completed.


  6. Grant succeeded.

  7. CREATE ROLE logstdby_administrator
  8.             *
  9. ERROR at line 1:
  10. ORA-01921: role name 'LOGSTDBY_ADMINISTRATOR' conflicts with another user or
  11. role name



  12. Grant succeeded.


  13. Grant succeeded.


  14. Grant succeeded.


  15. Library created.


  16. SQL> @?/rdbms/admin/prvtlsby.plb

  17. Warning: Package Body created with compilation errors.

  18. Errors for PACKAGE BODY SYS.DBMS_LOGSTDBY:

  19. LINE/COL ERROR
  20. -------- -----------------------------------------------------------------
  21. 646/3    PL/SQL: SQL Statement ignored
  22. 647/10   PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors
  23. grant select on dba_logstdby_progress to select_catalog_role
  24.                 *
  25. ERROR at line 1:
  26. ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors


  27. grant select on dba_logstdby_log to select_catalog_role
  28.                 *
  29. ERROR at line 1:
  30. ORA-04063: view "SYS.DBA_LOGSTDBY_LOG" has errors



  31. 0 rows updated.


  32. Commit complete.

  33. SQL>


  34. SQL> @?/rdbms/admin/utlrp.sql

  35. TIMESTAMP
  36. --------------------------------------------------------------------------------
  37. COMP_TIMESTAMP UTLRP_BGN  2012-12-22 20:20:47

  38. DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
  39. DOC>   objects in the database. Recompilation time is proportional to the
  40. DOC>   number of invalid objects in the database, so this command may take
  41. DOC>   a long time to execute on a database with a large number of invalid
  42. DOC>   objects.
  43. DOC>
  44. DOC>   Use the following queries to track recompilation progress:
  45. DOC>
  46. DOC>   1. Query returning the number of invalid objects remaining. This
  47. DOC>      number should decrease with time.
  48. DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
  49. DOC>
  50. DOC>   2. Query returning the number of objects compiled so far. This number
  51. DOC>      should increase with time.
  52. DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
  53. DOC>
  54. DOC>   This script automatically chooses serial or parallel recompilation
  55. DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
  56. DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
  57. DOC>   On RAC, this number is added across all RAC nodes.
  58. DOC>
  59. DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
  60. DOC>   recompilation. Jobs are created without instance affinity so that they
  61. DOC>   can migrate across RAC nodes. Use the following queries to verify
  62. DOC>   whether UTL_RECOMP jobs are being created and run correctly:
  63. DOC>
  64. DOC>   1. Query showing jobs created by UTL_RECOMP
  65. DOC>         SELECT job_name FROM dba_scheduler_jobs
  66. DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
  67. DOC>
  68. DOC>   2. Query showing UTL_RECOMP jobs that are running
  69. DOC>         SELECT job_name FROM dba_scheduler_running_jobs
  70. DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
  71. DOC>#
  72. DECLARE
  73. *
  74. ERROR at line 1:
  75. ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
  76. ORA-06508: PL/SQL: could not find program unit being called:
  77. "SYS.DBMS_INTERNAL_LOGSTDBY"
  78. ORA-06512: at "SYS.UTL_RECOMP", line 827
  79. ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
  80. ORA-06508: PL/SQL: could not find program unit being called:
  81. "SYS.DBMS_INTERNAL_LOGSTDBY"
  82. ORA-06512: at line 4



  83. TIMESTAMP
  84. --------------------------------------------------------------------------------
  85. COMP_TIMESTAMP UTLRP_END  2012-12-22 20:20:48

  86. DOC> The following query reports the number of objects that have compiled
  87. DOC> with errors (objects that compile with errors have status set to 3 in
  88. DOC> obj$). If the number is higher than expected, please examine the error
  89. DOC> messages reported with each object (using SHOW ERRORS) to see if they
  90. DOC> point to system misconfiguration or resource constraints that must be
  91. DOC> fixed before attempting to recompile these objects.
  92. DOC>#

  93. OBJECTS WITH ERRORS
  94. -------------------
  95.                  59

  96. DOC> The following query reports the number of errors caught during
  97. DOC> recompilation. If this number is non-zero, please query the error
  98. DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
  99. DOC> are due to misconfiguration or resource constraints that must be
  100. DOC> fixed before objects can compile successfully.
  101. DOC>#

  102. ERRORS DURING RECOMPILATION
  103. ---------------------------
  104.                           0


  105. Function created.


  106. PL/SQL procedure successfully completed.


  107. Function dropped.

  108. Warning: XDB now invalid, could not find xdbconfig

  109. PL/SQL procedure successfully completed.

  110. SQL> select object_name,status from dba_objects where status like '%INVALID%' and owner='SYS' and object_name like '%DBMS%';

  111. OBJECT_NAME                              STATUS
  112. ---------------------------------------- -------
  113. DBMS_XSTREAM_UTL_IVK                     INVALID
  114. DBMS_XSTREAM_ADM_UTL                     INVALID
  115. DBMS_STREAMS_SM                          INVALID
  116. DBMS_STREAMS_MC                          INVALID
  117. DBMS_STREAMS_AUTO_INT                    INVALID
  118. DBMS_STREAMS_ADM                         INVALID
  119. DBMS_RCVMAN                              INVALID
  120. DBMS_LOGSTDBY                            INVALID
  121. DBMS_LOGREP_UTIL                         INVALID
  122. DBMS_LOGMNR_SESSION                      INVALID
  123. DBMS_LOGMNR_LOGREP_DICT                  INVALID
  124. DBMS_LOGMNR_INTERNAL                     INVALID
  125. DBMS_LOGMNR_FFVTOLOGMNRT                 INVALID
  126. DBMS_INTERNAL_LOGSTDBY                   INVALID
  127. DBMS_CAPTURE_SWITCH_INTERNAL             INVALID
  128. DBMS_CAPTURE_SWITCH_ADM                  INVALID
  129. DBMS_CAPTURE_PROCESS                     INVALID
  130. DBMS_CAPTURE_ADM_INTERNAL                INVALID
  131. DBMS_CAPTURE_ADM                         INVALID
  132. DBMS_APPLY_HANDLER_ADM                   INVALID
  133. DBMS_APPLY_ADM_INTERNAL                  INVALID
  134. DBMS_APPLY_ADM                           INVALID

  135. 22 rows selected.
复制代码

回复 只看该作者 道具 举报

4#
发表于 2012-12-22 20:26:58
@?/rdbms/admin/catproc   ==>注意在downtime做
@dbmslsby.sql
@prvtlsby.plb

回复 只看该作者 道具 举报

5#
发表于 2012-12-22 22:01:26
刘大,尝试跑了下catproc.sql  跑了半个多小时。
过程中任然出现之前的错误信息:
  1. SQL> @?/rdbms/admin/catproc.sql
  2. DOC>######################################################################
  3. DOC>######################################################################
  4. DOC>    The following PL/SQL block will cause an ORA-20000 error and
  5. DOC>    terminate the current SQLPLUS session if the user is not SYS.
  6. DOC>    Disconnect and reconnect with AS SYSDBA.
  7. DOC>######################################################################
  8. DOC>######################################################################
  9. DOC>#

  10. PL/SQL procedure successfully completed.
  11. ……
  12. Package body created.

  13. No errors.

  14. Warning: Package Body created with compilation errors.

  15. Errors for PACKAGE BODY SYS.DBMS_LOGSTDBY:

  16. LINE/COL ERROR
  17. -------- -----------------------------------------------------------------
  18. 646/3    PL/SQL: SQL Statement ignored
  19. 647/10   PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors
  20. grant select on dba_logstdby_progress to select_catalog_role
  21.                 *
  22. ERROR at line 1:
  23. ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors


  24. grant select on dba_logstdby_log to select_catalog_role
  25.                 *
  26. ERROR at line 1:
  27. ORA-04063: view "SYS.DBA_LOGSTDBY_LOG" has errors



  28. 0 rows updated.


  29. Commit complete.


  30. Warning: Package Body created with compilation errors.

  31. Errors for PACKAGE BODY SYS.DBMS_INTERNAL_LOGSTDBY:

  32. LINE/COL ERROR
  33. -------- -----------------------------------------------------------------
  34. 4305/5   PL/SQL: SQL Statement ignored
  35. 4305/50  PL/SQL: ORA-00942: table or view does not exist
  36. 6696/3   PL/SQL: SQL Statement ignored
  37. 6697/10  PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors
  38. 6705/5   PL/SQL: SQL Statement ignored
  39. 6706/12  PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_LOG" has errors
  40. 6720/5   PL/SQL: SQL Statement ignored
  41. 6721/12  PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_LOG" has errors

  42. Package body created.

  43. No errors.

  44. Package body created.

  45. No errors.
  46. ……
复制代码
@?/rdbms/admin/dbmslsby.sql
@?/rdbms/admin/prvtlsby.plb

@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql

重新编译几次后,失效的包体还是原来那些。。怀疑是否SYSAUX表空间问了问题?

另外该库目前测试阶段,未正式上线,有备份数据,是否考虑重建实例来解决问题?

回复 只看该作者 道具 举报

6#
发表于 2012-12-22 22:15:30

SQL> alter view SYS.DBA_LOGSTDBY_PROGRESS compile;

View altered.

SQL> show errors;

回复 只看该作者 道具 举报

7#
发表于 2012-12-22 22:17:15
是不是做stream 把logminer相关的数据字典迁到了别的表空间,而又删除了这个表空间。
可以尝试以下方法:在plsql中查看无效对象涉及到的表是什么,然后在别的数据库中找到该表的create ddl,在问题库中recreate,然后重新编译。

回复 只看该作者 道具 举报

8#
发表于 2012-12-22 23:06:53
感谢大神回复。
我之前已做过类似操作:
参考文章:
https://forums.oracle.com/forums/thread.jspa?threadID=2165317
输出的结果跟我的情况一致
  1. SQL> alter package DBMS_LOGSTDBY compile body;

  2. Warning: Package Body altered with compilation errors.

  3. SQL> show errors
  4. Errors for PACKAGE BODY DBMS_LOGSTDBY:

  5. LINE/COL ERROR
  6. -----------------------------------------------------------------
  7. 646/3 PL/SQL: SQL Statement ignored
  8. 647/10 PL/SQL: ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors
  9. SQL> alter package DBMS_LOGSTDBY compile;

  10. Warning: Package altered with compilation errors.

  11. SQL> show errors
  12. No errors.

  13. SQL> alter view SYS.DBA_LOGSTDBY_PROGRESS compile;

  14. Warning: View altered with compilation errors.

  15. SQL> show error;
  16. No errors.
  17. SQL> select text from dba_errors where name = 'DBA_LOGSTDBY_PROGRESS';

  18. TEXT
  19. ORA-00942: table or view does not exist
复制代码
之前也试过重新compile,重新编译成功,但有错误:

我甚至执行过如下脚本,问题任然存在。
@?/rdbms/admin/catalog.sql;
@@catblock.sql;
@@catproc.sql;
@@catoctk.sql;
@@owminst.plb;

@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql

回复 只看该作者 道具 举报

9#
发表于 2012-12-22 23:11:50

1. 考虑提交SR

2. 需要debug这个compile过程 获得更多信息

3. 目前未找到MOS上类似案例

回复 只看该作者 道具 举报

10#
发表于 2012-12-22 23:12:40
sunnyihui 发表于 2012-12-22 22:17
是不是做stream 把logminer相关的数据字典迁到了别的表空间,而又删除了这个表空间。
可以尝试以下方法:在 ...

问题产生的原因是对的,但目前要定位那些表和视图比较困难,DBMS基本package boby基本是加密的。

回复 只看该作者 道具 举报

11#
发表于 2012-12-24 11:42:59
pssql developer很容易看的啊

回复 只看该作者 道具 举报

12#
发表于 2013-9-10 15:58:15
yehc@epsoft.com 发表于 2012-12-22 23:12
问题产生的原因是对的,但目前要定位那些表和视图比较困难,DBMS基本package boby基本是加密的。 ...

知道原因就好办了,找一个正常的库,把logminer相关的表重建,再把无效对象重新编译

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-17 20:00 , Processed in 0.051964 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569