OracleBlog – 第 18 页 – 笑看数据库江湖的那些事儿……

RMAN-00600 [13200]的处理

2015-01-01 没有评论

在dataguard上，使用rman连接catalog数据库进行备份。备份的时候，rman的日志报错如下：

Starting backup at 2014/04/23-10:02:02
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc
archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc not found or out of sync with catalog
skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc
RMAN-06061: WARNING: skipping archived log compromises recoverability
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/23/2014 10:02:23
RMAN-00600: internal error, arguments [13200] [] [] [] []

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Starting backup at 2014/04/23-10:02:02

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201410_9ofpggpg_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201411_9ofqblsm_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201412_9ofr6px7_.arc

archived log /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc not found or out of sync with catalog

skipping inaccessible file /aabb/qb/mobprd/log/fra/mobprd/archivelog/2014_04_23/o1_mf_1_201413_9ofs2v2m_.arc

RMAN-06061: WARNING: skipping archived log compromises recoverability

released channel: ch00

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 04/23/2014 10:02:23

RMAN-00600: internal error, arguments [13200] [] [] [] []

一开始，根据文档：341337.1，以为造成这个报错的原因是，注册在catalog库中的信息，在备份时被调用，由于备份是在da […]

..experience, Working case

打补丁的时候一定要注意opatch版本

2015-01-01 一条评论

在打patch的时候，特别是有些新申请的merge patch，我们需要注意在opatch的readme文档说明，因为有的时候，使用自带版本的opatch，会发生一些千奇百怪的问题。如下面的这个log中，我们看到，这是一个for 10.2.0.3.3的做db replay的patch，由于db r […]

..experience, Working case

11g库shutdown导致10g库的crsd进程重启

2014-12-05 没有评论

客户有个环境是10g的RAC，由于一次偶尔的需求，需要将一个11g的数据库临时在上面启动，当我们mount了11g的软件卷和datafile 卷之后，11g的数据库能正常启动，但是当11g的数据库shutdown时，导致了10g的crsd进程重启。在10g的crsd的log中，可以看到： [cra […]

..experience, Working case

ORA-06553: PLS-801: internal error [56327]

2014-12-05 没有评论

今天收到一个客户的问题，数据库中的alertlog中报错ORA-06553: PLS-801: internal error [56327]，

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Mon Nov 17 14:11:04 2014
Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]
Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327]

1

2

3

4

5

6

7

8

9

10

11

12

13

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Mon Nov 17 14:11:04 2014

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Errors in file /mydb/db/oradata01/dbsepus/diag/rdbms/dbsepus/dbsepus/trace/dbsepus_cjq0_11056.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

测试export用户或者表，甚至查询dba_segments都会报错ORA-06553: […]

..experience, Working case

rman set newname时报错RMAN-06015

2014-12-05 一条评论

某数据库restore之后，尝试set newname但是报错RMAN-06015。我们可以先手工catalog进去，在set newname。可以看如下的测试案例：

--1.纯粹的set newname是可以的
RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> } 

executing command: SET NEWNAME 

RMAN> 

RMAN> 

RMAN> 

RMAN> 

RMAN> 


--2. 但是switch之后，就不可以。这是因为在你当前控制文件中，找不到该文件（asm上的文件）的信息
RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> switch datafile 6; 
4> } 

executing command: SET NEWNAME 

RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03002: failure of switch command at 11/12/2014 10:36:09 
RMAN-20230: datafile copy not found in the repository 
RMAN-06015: error while looking up datafile copy name: /u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 

RMAN> 

RMAN> 

RMAN> -- 3.将该文件作为datafilecopy，catalog到控制文件中：

RMAN> catalog datafilecopy '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 

cataloged datafile copy 
datafile copy file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 RECID=1 STAMP=863433432 

RMAN> --4. 然后就可以顺利set newname后switch了。

RMAN> run{ 
2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222'; 
3> switch datafile 6; 
4> } 

executing command: SET NEWNAME 

datafile 6 switched to datafile copy 
input datafile copy RECID=1 STAMP=863433432 file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 

RMAN> 

RMAN>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

--1.纯粹的set newname是可以的

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> }

executing command: SET NEWNAME

RMAN>

--2. 但是switch之后，就不可以。这是因为在你当前控制文件中，找不到该文件（asm上的文件）的信息

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> switch datafile 6;

4> }

executing command: SET NEWNAME

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of switch command at 11/12/2014 10:36:09

RMAN-20230: datafile copy not found in the repository

RMAN-06015: error while looking up datafile copy name: /u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222

RMAN>

RMAN> -- 3.将该文件作为datafilecopy，catalog到控制文件中：

RMAN> catalog datafilecopy '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

cataloged datafile copy

datafile copy file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222 RECID=1 STAMP=863433432

RMAN> --4. 然后就可以顺利set newname后switch了。

RMAN> run{

2> set newname for datafile 6 to '/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222';

3> switch datafile 6;

4> }

executing command: SET NEWNAME

datafile 6 switched to datafile copy

input datafile copy RECID=1 STAMP=863433432 file name=/u01/oracle11gR2/oracle/oradata/ora11g/U_NOLOB2.dbf222

RMAN>

..experience, It's my life, Working case

基于Solaris sparc的Oracle调优

2014-09-28 没有评论

一、RAC 中cluster wait time高问题 1.设置LMS进程为FX 60，不要过多调整lms进程的数量注：在Solaris 10 Update 10之后，以及Solaris 11，才可以设置进程的优先级。可以通过看/etc/release看其版本。如Oracle Solaris 10 […]

Study note

v$archived_log中显示creator是rman

2014-09-28 没有评论

Q：$archived_log中显示creator有多种，如果是FGRD，这可能是alter system archive log current，那么还有看到是RMAN，这是怎么产生的？ A：可能是这些archive之前被rman catalog过。见下面的testcase： [crayon-67 […]

..experience, Working case

SQL执行时反复一慢两快的问题

2014-09-14 没有评论

SQL执行的时间，在正常情况下应该是稳定的。如果第一次快，第二次慢，那么可能就是由于cardinality feedback的缘故，我们可以设置”_OPTIMIZER_USE_FEEDBACK”= false来规避。但是这次遇到的问题却是执行过程两快一慢，执行过程是慢->快- […]

..experience, Working case

统计信息收集后不生效的问题

2014-09-14 2条评论

客户的某系统升级到11g之后，收集统计信息却不生效，查dba_tables看不到其LAST_ANALYZED。这其实是因为11g的一个新特性，延时发布统计信息。我们看下面的测试案例：

--建立一个测试表：
SQL> create table t_test as select * from dual;

Table created.

--检查这个publish属性，我们可以查系统级的熟悉和表级别的属性。在默认情况下，是true：即收集后立即发布：
--系统级的publish属性是true，收集后立即发布：
SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')
--------------------------------------------------------------------------------
TRUE

--表级的publish属性是true，收集后立即发布：
SQL> select dbms_stats.get_prefs('PUBLISH', 'TEST', 'T_TEST') publish from dual;

PUBLISH
--------------------------------------------------------------------------------
TRUE

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

--建立一个测试表：

SQL> create table t_test as select * from dual;

Table created.

--检查这个publish属性，我们可以查系统级的熟悉和表级别的属性。在默认情况下，是true：即收集后立即发布：

--系统级的publish属性是true，收集后立即发布：

SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')

--------------------------------------------------------------------------------

TRUE

--表级的publish属性是true，收集后立即发布：

SQL> select dbms_stats.get_prefs('PUBLISH', 'TEST', 'T_TEST') publish from dual;

PUBLISH

--------------------------------------------------------------------------------

TRUE

SQL>

我们可以修改表的publish属性： [cray […]

..experience, Working case

关于带lob对象的分区表，移动表空间的问题

2014-09-12 2条评论

客户有个带lob对象的表空间，希望做表空间的move，可是等move之后，发现在dba_lobs里面查到的lob对象的表空间还是在原来的地方。

CREATE TABLE SCES1INPUTS
(
  CODREQUEST            VARCHAR2(9 BYTE)        NOT NULL,
  LOBS1INPUT            CLOB                    NOT NULL,
  CODLAYOUT             VARCHAR2(20 BYTE)       NOT NULL,
  DATINSERTION          DATE                    DEFAULT SYSDATE               NOT NULL,
  CODINSERTIONUSER      VARCHAR2(10 BYTE)       NOT NULL,
  CODINSERTIONFUNCTION  VARCHAR2(5 BYTE)        NOT NULL,
  DATHISTORY            DATE                    DEFAULT SYSDATE               NOT NULL,
  LOBS1INPUT_GZ         BLOB
)
LOB (LOBS1INPUT) STORE AS LOB1_SCES1INPUTS
LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS
PARTITION BY RANGE ( DATINSERTION )
(
 PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy')) ,
 PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy')) ,
 PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy')) ,
 PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy')) ,
 PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','ddmmyyyy'))
)
/



SQL> SELECT table_name,column_name,segment_name,tablespace_name,index_name 
  2  from Dba_Lobs  WHERE table_name='SCES1INPUTS';

TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME      INDEX_NAME
-------------------- -------------------- ------------------------------ -------------------- ----------------------------------------
SCES1INPUTS          LOBS1INPUT           LOB1_SCES1INPUTS               USERS                SYS_IL0000018502C00002$$
SCES1INPUTS          LOBS1INPUT_GZ        LOB2_SCES1INPUTS               USERS                SYS_IL0000018502C00008$$

SQL> 

--move tablespace：
SQL> alter table SCES1INPUTS
  2  move partition SCES1INPUTS_200508 tablespace USERS
  3  lob (LOBS1INPUT) STORE as  (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )
  4  lob (LOBS1INPUT_GZ) store as  (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );

Table altered.

SQL> 

SQL> SELECT table_name,column_name,segment_name,tablespace_name,index_name 
  2  from Dba_Lobs  WHERE table_name='SCES1INPUTS';

TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME      INDEX_NAME
-------------------- -------------------- ------------------------------ -------------------- ----------------------------------------
SCES1INPUTS          LOBS1INPUT           LOB1_SCES1INPUTS               USERS                SYS_IL0000018502C00002$$
SCES1INPUTS          LOBS1INPUT_GZ        LOB2_SCES1INPUTS               USERS                SYS_IL0000018502C00008$$

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

CREATE TABLE SCES1INPUTS

(

CODREQUEST VARCHAR2(9 BYTE) NOT NULL,

LOBS1INPUT CLOB NOT NULL,

CODLAYOUT VARCHAR2(20 BYTE) NOT NULL,

DATINSERTION DATE DEFAULT SYSDATE NOT NULL,

CODINSERTIONUSER VARCHAR2(10 BYTE) NOT NULL,

CODINSERTIONFUNCTION VARCHAR2(5 BYTE) NOT NULL,

DATHISTORY DATE DEFAULT SYSDATE NOT NULL,

LOBS1INPUT_GZ BLOB

)

LOB (LOBS1INPUT) STORE AS LOB1_SCES1INPUTS

LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS

PARTITION BY RANGE ( DATINSERTION )

(

PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','ddmmyyyy'))

)

/

SQL> SELECT table_name,column_name,segment_name,tablespace_name,index_name

2 from Dba_Lobs WHERE table_name='SCES1INPUTS';

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME

-------------------- -------------------- ------------------------------ -------------------- ----------------------------------------

SCES1INPUTS LOBS1INPUT LOB1_SCES1INPUTS USERS SYS_IL0000018502C00002$$

SCES1INPUTS LOBS1INPUT_GZ LOB2_SCES1INPUTS USERS SYS_IL0000018502C00008$$

SQL>

--move tablespace：

SQL> alter table SCES1INPUTS

2 move partition SCES1INPUTS_200508 tablespace USERS

3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );

Table altered.

SQL>

SQL> SELECT table_name,column_name,segment_name,tablespace_name,index_name

2 from Dba_Lobs WHERE table_name='SCES1INPUTS';

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME

-------------------- -------------------- ------------------------------ -------------------- ----------------------------------------

SCES1INPUTS LOBS1INPUT LOB1_SCES1INPUTS USERS SYS_IL0000018502C00002$$

SCES1INPUTS LOBS1INPUT_GZ LOB2_SCES1INPUTS USERS SYS_IL0000018502C00008$$

SQL>

这里其实存在一个误区，对于分区表的lob对象，我们不应该去查user_lobs，而是应该去 […]

..experience, Working case

迁移数据库后启动报错ora-600[25025]

2014-09-12 没有评论

迁移脚本的日志中报错RMAN-06571: datafile 78 does not have recoverable copy，经查看发现78号文件曾经被offline drop掉。于是重建控制文件，在控制文件中把78号文件去掉，重建控制后，数据库能够mount，mount后数据文件是一致，但是o […]

..experience, Working case

修复missing的datafile

2014-09-08 没有评论

在一次迁移中，原来的数据库中存在一些missing的datafile，如MISSING00006这样的datafile，这些数据文件经查已经在os上不存在，且该数据文件上的信息也已经不需要。一般情况下，我们是将仍旧需要表从这个表空间move到另外的表空间，再将这整个表空间drop掉。但是由于表空间中 […]