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

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认为是看 […]

Working case

如何挑选一个优质IP进行解析的绑定

2016-05-16 没有评论

大型公司的服务器往往会部署到多个地区，有多个IP，如baiduc.com这个域名，在中国是被解析成123.125.114.144，在美国是被解析成180.149.132.47，在德国是被解析成111.13.101.208。咱们公司的内网VPN，域名是myaccess.oraclevpn.com，有 […]

..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-68453fecb8ea663845770 […]

..experience, Working case

闲聊sharding database架构

2016-05-14 4条评论

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

..experience, Working case

创建Oracle sharding database

2016-05-13 4条评论

本文继『Oracle sharding database的一些概念』后，介绍如下搭建一个oracle sharding database的环境，以及可能在搭建过程中可能会遇到的known issue（有很多坑，且在mos上还没有解决方案，都是一个一个自己摸索解决的。）。你在本文中可以看到：（一） […]

..experience, Working case

Oracle sharding database的一些概念

2016-05-12 没有评论

2016年2月，oracle出了12.2的beta2版本，并且在4月更新了相关文档，如Concepts，Administrator’s Guide，Global Data Services Concepts and Administration Guide等等。这个版本的文档，比之前20 […]

..experience, Working case

关于RAC环境需要完整down库才能修改的参数

2016-05-09 没有评论

有哪些RAC环境中需要完整down库(Not rolling fashion)才能修改的参数，这个问题，在oracle的文档中也没有很好的说明。我们处理的时候，一般都是case by case。之前队友们有过讨论，究竟哪些参数是需要完整down库的。猜想一：ISINSTANCE_MODIFIAB […]

..experience, Working case

service漂不漂，是个问题

2016-05-06 一条评论

service的起停，要看START_DEPENDENCIES和STOP_DEPENDENCIES：我们用如下命令建立一个service：

srvctl add service -d ora11g -s srv_di_1 -r node1 -a node2 -P basic -e SELECT -m basic -z 180 -w 5

1	srvctl add service -d ora11g -s srv_di_1 -r node1 -a node2 -P basic -e SELECT -m basic -z 180 -w 5

我们具体看看这个service情况： [crayon-68453fecbd394728359 […]

..experience, Working case

db replay设置scale_up_multiplier不生效

2016-03-29 没有评论

设置scale_up_multiplier：

BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (scale_up_multiplier => 10);
END;

1

2

3

BEGIN

DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (scale_up_multiplier => 10);

END;

但是设置之后，在DBA_WORKLOAD_REPLAYS.SCALE_UP_MULTIPLIER检查发现，这个值始终是1。这是因为scale_up_multiplier不支持基于obje […]

..experience, Working case

12.1.0.2开始废弃使用crsctl对ora resource的修改

2016-03-29 没有评论

在12.1.0.2之后，如果使用crsctl进行ora resource的修改，启动，关闭，会遭遇CRS-4995的错误。要求你使用srvctl命名来进行操作

[oracle@12102-rac1 ~]$ crsctl stop resource ora.cdbrac.db
CRS-4995:  The command 'Stop  resource' is invalid in crsctl. Use srvctl for this command.
[oracle@12102-rac1 ~]$

1

2

3

[oracle@12102-rac1 ~]$ crsctl stop resource ora.cdbrac.db

CRS-4995: The command 'Stop resource' is invalid in crsctl. Use srvctl for this command.

[oracle@12102-rac1 ~]$

参考下面3个文档： Online Document：Clusterware […]

..experience, Working case

alter package时包含drop操作

2016-03-15 没有评论

一般情况下，我们如果alter操作，是不会触发drop操作。但是在某些特别的情况下，alter package的操作在递归SQL中，是能看到drop操作的。我们这个环境中有trigger，一旦有drop操作的时候，是会报错ORA-20008，且被阻拦的。我们看到下面，我们只是alter pack […]