月归档：三月 2012

通过修改基表(link$)让非public dblink变为public

发表于 2012 年 3 月 29 日 由 惜分飞

有些朋友创建了一个非public的dblink，现在该数据库的其他用户需要去使用该dblink，在正常情况下无访问权限，需要重新建一个dblink，或者将原dblink修改为public。但是由于忘记了原dblink的目标段的密码，使得创建或者修改dblink的步骤无法进行下去。这里通过修改基表(link$)，解决该问题。

创建dblink

SQL> show user;
USER is "SYS"
SQL> create database link "xff_dblink"
  2  connect to TEST
  3  identified by "test"
  4  using '11.1.1.1:1521/mcrm';

Database link created.

SQL> select * from dba_db_links where db_link like 'XFF_DBLINK%';

OWNER DB_LINK                                     USERN HOST               CREATED
----- ------------------------------------------- ----- ------------------ --------
SYS   XFF_DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM  TEST  11.1.1.1:1521/mcrm 29-MAR-12

SQL> select sysdate from dual@xff_dblink;

SYSDATE
---------
29-MAR-12

SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;
SELECT SYSDATE FROM DUAL@XFF_DBLINK
                         *
ERROR at line 1:
ORA-02019: connection description for remote database not found
--该dblink不是public的，所以test用户无权访问

dblink变为public类型

SQL> CONN / AS SYSDBA
Connected.
SQL> set long 1000
SQL> select  text from dba_views where view_name='DBA_DB_LINKS';

TEXT
-------------------------------------------------------------------
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
--查询出dblink相关的基表有link$和user$

SQL> desc sys.link$
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER#                        NOT NULL NUMBER
 NAME                          NOT NULL VARCHAR2(128)
 CTIME                         NOT NULL DATE
 HOST                                   VARCHAR2(2000)
 USERID                                 VARCHAR2(30)
 PASSWORD                               VARCHAR2(30)
 FLAG                                   NUMBER
 AUTHUSR                                VARCHAR2(30)
 AUTHPWD                                VARCHAR2(30)
 PASSWORDX                              RAW(128)
 AUTHPWDX                               RAW(128)

SQL> select owner# from sys.link$ where name like 'XFF_DBLINK%';

    OWNER#
----------
         0
--XFF_DBLINK对应的用户标识记录在link$.owner#中

SQL> SELECT USER#,NAME FROM USER$ WHERE name in ('SYS','PUBLIC');

     USER# NAME
---------- ------------------------------
         1 PUBLIC
         0 SYS
--现link$.owner#值为0，表示该dblink所属用户为SYS，现在让该dblink变为public
--现需要让该dblink变为public，需要做的是修改link$.owner#的值为1

SQL> UPDATE LINK$ SET OWNER#=1 WHERE name like 'XFF_DBLINK%';

1 row updated.

SQL> COMMIT;

Commit complete.

--需要刷新shared_pool
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

--查看dblink所属者，已经修改为public
SQL> select owner from dba_db_links where db_link like 'XFF_DBLINK%';

OWNER
----------
PUBLIC

--测试dblink是否成功
SQL> CONN TEST/TEST
Connected.
SQL> SELECT SYSDATE FROM DUAL@XFF_DBLINK;

SYSDATE
---------
29-MAR-12
发表在 Oracle | 一条评论

ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

发表于 2012 年 3 月 29 日 由 惜分飞

朋友在数据库软件从11.1.0.6升级到11.1.0.7后，发现数据库无法打开，不能继续下一步升级
数据库启动

SQL> startup upgrade
ORA-03113: end-of-file on communication channel

alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilecenterdb.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  memory_target            = 12864M
  control_files            = "/u01/app/oracle/oradata/centerdb/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=centerdbXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/centerdb/adump"
  audit_trail              = "DB"
  db_name                  = "centerdb"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Thu Mar 29 15:47:06 2012
PMON started with pid=2, OS id=16324 
Thu Mar 29 15:47:06 2012
VKTM started with pid=3, OS id=16326 at elevated priority
VKTM running at (20)ms precision
Thu Mar 29 15:47:06 2012
DIAG started with pid=4, OS id=16330 
Thu Mar 29 15:47:06 2012
DBRM started with pid=5, OS id=16332 
Thu Mar 29 15:47:06 2012
PSP0 started with pid=6, OS id=16334 
Thu Mar 29 15:47:06 2012
DIA0 started with pid=7, OS id=16336 
Thu Mar 29 15:47:06 2012
MMAN started with pid=8, OS id=16338 
Thu Mar 29 15:47:06 2012
DBW0 started with pid=9, OS id=16340 
Thu Mar 29 15:47:06 2012
DBW1 started with pid=10, OS id=16342 
Thu Mar 29 15:47:06 2012
DBW2 started with pid=11, OS id=16344 
Thu Mar 29 15:47:06 2012
DBW3 started with pid=12, OS id=16346 
Thu Mar 29 15:47:06 2012
DBW4 started with pid=13, OS id=16348 
Thu Mar 29 15:47:06 2012
DBW5 started with pid=14, OS id=16350 
Thu Mar 29 15:47:06 2012
LGWR started with pid=15, OS id=16352 
Thu Mar 29 15:47:06 2012
CKPT started with pid=16, OS id=16354 
Thu Mar 29 15:47:06 2012
SMON started with pid=17, OS id=16356 
Thu Mar 29 15:47:06 2012
RECO started with pid=18, OS id=16358 
Thu Mar 29 15:47:06 2012
MMON started with pid=19, OS id=16360 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Mar 29 15:47:06 2012
MMNL started with pid=20, OS id=16362 
starting up 1 shared server(s) ...
Errors in file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc:
ORA-27103: internal error
Additional information: -1
Additional information: 1
MMAN (ospid: 16338): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 16338

这里可以发现memory_target在12g以上

trace文件内容

[oracle@fcdb trace]$ more /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Trace file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      fcdb
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: centerdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 8
Unix process pid: 16338, image: oracle@fcdb (MMAN)


*** 2012-03-29 15:47:06.865
*** SESSION ID:(1648.1) 2012-03-29 15:47:06.865
*** CLIENT ID:() 2012-03-29 15:47:06.865
*** SERVICE NAME:() 2012-03-29 15:47:06.865
*** MODULE NAME:() 2012-03-29 15:47:06.865
*** ACTION NAME:() 2012-03-29 15:47:06.865
 
error 27103 detected in background process
ORA-27103: internal error
Additional information: -1
Additional information: 1

*** 2012-03-29 15:47:06.865
MMAN (ospid: 16338): terminating the instance due to error 27103

结合alert和trace文件查询MOS，发现ORA-27103 when Memory target parameter is set to more than 3 GB [ID 743012.1]描述相符，是由于Bug:7272646引起.
鉴于朋友的数据库还升级过程中，所以给出的处理建议是先把memory_target改为2.8G，执行完升级操作，然后打上Patch:7272646
同时官方还给出了另一种解决方案:设置SHMMAX小于4G，个人不推荐；如果系统内存比较大，会出现多个内存段，影响系统性能

发表在 ORA-xxxxx | 标签为 | 一条评论

模拟ORA-04043并解决

发表于 2012 年 3 月 29 日 由 惜分飞

创建两张模拟表

SQL> select * from v$version;

BANNER
------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production


SQL> create table sys_xifenfei as           
  2  select * from dba_tables;

Table created.

SQL> create table chf.chf_xifenfei as
  2  select * from dba_tables;

Table created.

启动数据库到mount状态查询表

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist


SQL> desc sys_xifenfei
ERROR:
ORA-04043: object sys_xifenfei does not exist


SQL> desc chf.chf_xifenfei
ERROR:
ORA-04043: object chf.chf_xifenfei does not exist

打开数据库查询

SQL> alter database open;

Database altered.


SQL> select count(*) from sys_xifenfei;
select count(*) from sys_xifenfei
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from chf.chf_xifenfei;

  COUNT(*)
----------
       868

SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

解决问题

SQL> alter system flush shared_pool;

System altered.


SQL> select count(*) from dba_tables;

  COUNT(*)
----------
       869

SQL> select count(*) from sys_xifenfei;

  COUNT(*)
----------
       867

MOS解释
ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]

Available workarounds are:
1) Don't describe the dba_* views at mount stage.
OR
2) If you issue DESC of any DBA_*views at mount stage,
    then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command.

在10g中open库后提示也为类此ORA-04043: object dba_tables does not exist

发表在 ORA-xxxxx | 标签为 | 评论关闭