..experience – 第 7 页

STAT table 字段含义说明

2016-08-25 没有评论

当我们用DBMS_STATS.CREATE_STAT_TABLE备份统计信息的时候，我们可以看对应的备份统计信息表各个字段的含义。根据Type有分T=table，I=index，C=column，S=system。具体含义见下：

desc of the STATS table (11.2.0.3)

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30)
 C2                                                 VARCHAR2(30)
 C3                                                 VARCHAR2(30)
 C4                                                 VARCHAR2(30)
 C5                                                 VARCHAR2(30)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000)
 CL1                                                CLOB

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

desc of the STATS table (11.2.0.3)

Name Null? Type

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

STATID VARCHAR2(30)

TYPE CHAR(1)

VERSION NUMBER

FLAGS NUMBER

C1 VARCHAR2(30)

C2 VARCHAR2(30)

C3 VARCHAR2(30)

C4 VARCHAR2(30)

C5 VARCHAR2(30)

N1 NUMBER

N2 NUMBER

N3 NUMBER

N4 NUMBER

N5 NUMBER

N6 NUMBER

N7 NUMBER

N8 NUMBER

N9 NUMBER

N10 NUMBER

N11 NUMBER

N12 NUMBER

D1 DATE

R1 RAW(32)

R2 RAW(32)

CH1 VARCHAR2(1000)

CL1 CLOB

参 […]

..experience, Working case

RMAN active duplicate hanging on restore control file

2016-08-22 没有评论

12.1.0.2之后，duplicate target database for standby from active database的时候，总是hang死在restore controlfile的情况。这个由于Bug 19664695引起。（Bug22468652和Bug 20721271最终 […]

..experience, Working case

升级11.2.0.3到11.2.0.4你可能不知道的事情

2016-07-27 2条评论

升级patchset是小事？不是，patchset的升级从来都不是小事。如12.1.0.1到12.1.0.2就多了一个引人注目的in-memory options。今天说一个你可能不知道的从11.2.0.3升级到11.2.0.4的变化。（Solaris操作系统）在高并发的连接时，如果listene […]

..experience, Working case

利用DMU修改数据库字符集

2016-06-27 没有评论

和oracle字符集相关的参数是nls_lang。NLS_LANG的格式是：language_territory.client_charset，如AMERICAN_AMERICA.ZHS16GBK，那么第一位AMERICAN表示语言，第二位AMERICA表示日期和数字格式，第三位ZHS16GBK表示 […]

..experience, Working case

12c的cluster中，dbs下指定spfile不生效

2016-06-24 没有评论

在11g的cluster中，我们往往用dbs下的初始化文件（pfile），来指定asm中实际存在的spfile

[oracle@ol6-112-rac1 dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@ol6-112-rac1 dbs]$ ls
hc_acfsdb.dat  hc_cdbrac_1.dat  hc_cdbrac_2.dat  initcdbrac_1.ora  initcdbrac_1.ora.orig  init.ora  orapwacfsdb  spfileacfsdb.ora
[oracle@ol6-121-rac1 dbs]$ cat initcdbrac_1.ora
SPFILE='+DATA/cdbrac/spfilecdbrac.ora'
[oracle@ol6-112-rac1 dbs]$

1

2

3

4

5

6

[oracle@ol6-112-rac1 dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs

[oracle@ol6-112-rac1 dbs]$ ls

hc_acfsdb.dat hc_cdbrac_1.dat hc_cdbrac_2.dat initcdbrac_1.ora initcdbrac_1.ora.orig init.ora orapwacfsdb spfileacfsdb.ora

[oracle@ol6-121-rac1 dbs]$ cat initcdbrac_1.ora

SPFILE='+DATA/cdbrac/spfilecdbrac.ora'

[oracle@ol6-112-rac1 dbs]$

但是在12c中，如果我去修改dbs下的pfile指定spfile，这个设置就不会生效了，如我将文件指定到/tmp目录下，你 […]

..experience, Working case

asm如何更换ocr和votedisk到新存储

2016-06-17 没有评论

一个问题会牵涉出更多问题。呵呵。最初的问题是，asm如何更换ocr和votedisk到新存储？（一）首先官方推荐了OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) (Doc ID 428681.1) （1） OCR […]

..experience, Working case

WMSYS.WM_CONCAT不出现在12c中

2016-06-13 没有评论

WMSYS.WM_CONCAT不出现在12c中，原因是WMSYS.WM_CONCAT是一个oracle supported的internal函数，是在oracle workspace manager组件（wmsys用户）中包含的，这个函数是for workspace manager内部使用。这意味 […]

..experience, Working case

12c的Data guard中将废弃使用using current logfile

2016-06-12 没有评论

问题起源于客户的一个12c的数据库，需要启动到非real time apply的模式，但是发现执行： alter database recover managed standby database cancel; alter database recover managed standby data […]

..experience, Working case

12c数据泵导入报错KUP-11014

2016-05-21 没有评论

将10.2.0.5的一个大表导入到12.1.0.2的时候，导出参数是： [oracle10g@testdb tmp]$ cat expdp.par userid=’/ as sysdba’ DIRECTORY=DUMPDIR dumpfile=mytable_%U.dmp tables=schem […]

..experience, Working case

drop user cascade报错ora-69

2016-05-19 没有评论

1. 现象：

SQL> drop user test cascade;
drop user test cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for T1


SQL> conn test/test
Connected.
SQL> 
SQL> 
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  DATE

SQL> 
SQL> 
SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

SQL> drop user test cascade;

drop user test cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-00069: cannot acquire lock -- table locks disabled for T1

SQL> conn test/test

Connected.

SQL>

SQL> desc t1

Name Null? Type

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

A DATE

SQL>

SQL> drop table t1;

drop table t1

*

ERROR at line 1:

ORA-00069: cannot acquire lock -- table locks disabled for T1

2. 原因：

SQL> !
oer[oracle@rac1 ~]$ oerr ora 69
00069, 00000, "cannot acquire lock -- table locks disabled for %s"
// *Cause: A command was issued that tried to lock the table indicated in
//         the message. Examples of commands that can lock tables are:
//         LOCK TABLE, ALTER TABLE ... ADD (...), and so on.
// *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry
//          the command.
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 19 11:29:19 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL>  select owner,table_name,table_lock from dba_tables where table_name='T1'
SQL> /

OWNER                          TABLE_NAME                     TABLE_LO
------------------------------ ------------------------------ --------
TEST                           T1                             DISABLED

SQL> 
SQL> 
SQL> 
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

SQL> !

oer[oracle@rac1 ~]$ oerr ora 69

00069, 00000, "cannot acquire lock -- table locks disabled for %s"

// *Cause: A command was issued that tried to lock the table indicated in

// the message. Examples of commands that can lock tables are:

// LOCK TABLE, ALTER TABLE ... ADD (...), and so on.

// *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry

// the command.

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 19 11:29:19 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> select owner,table_name,table_lock from dba_tables where table_name='T1'

SQL> /

OWNER TABLE_NAME TABLE_LO

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

TEST T1 DISABLED

SQL>

3. 解决方法：

SQL> alter table test.t1 enable table lock;

Table altered.

SQL> 
SQL> select owner,table_name,table_lock from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TABLE_LO
------------------------------ ------------------------------ --------
TEST                           T1                             ENABLED

SQL> 

SQL> drop user test cascade;

User dropped.

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SQL> alter table test.t1 enable table lock;

Table altered.

SQL>

SQL> select owner,table_name,table_lock from dba_tables where table_name='T1';

OWNER TABLE_NAME TABLE_LO

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

TEST T1 ENABLED

SQL>

SQL> drop user test cascade;

User dropped.

SQL>

再进一步查，发现是DBA误将table_lock认为是看 […]

..experience, Working case

RMAN备份和检查逻辑坏块

2016-05-16 一条评论

1. RMAN备份时是默认检查物理坏块。 2. 如果要检查逻辑坏块，可以用如下语句：

$ rman target / 
RMAN> backup check logical validate database;

1 2	$ rman target / RMAN> backup check logical validate database;

注上述语句，只是检查，不会备份的。 3. 如果要在备份的同时，进行逻辑坏块检查，可以用： [crayon-6634102b1b47809100597 […]

..experience, Working case

闲聊sharding database架构

2016-05-14 4条评论

今天我们来闲聊一下sharding的架构。在开始闲聊之前，如果你没阅读我前面两篇的文章，推荐你先阅读一下这两篇，以便对oracle sharding database有个初步的了解。『Oracle sharding database的一些概念』和『创建Oracle sharding databas […]