2011年11月 – 第4页

议rman的crosscheck和obsolete

今天有朋友对于crosscheck和obsolete理解的不太清楚，网上查找了一些资料，也发现很多错误，其中典型的理解就是:crosscheck 可以检测/删除违背(obsolete)备份策略的备份集

--登录rman
[oracle@node1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Nov 8 13:39:17 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ECP (DBID=1669273445)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/db_1/dbs/snapcf_ecp.f'; # default
--发现配置的策略是保留1份
--全库备份
RMAN> list backup summary;
--当前数据库无备份集备份
RMAN> backup database format '/opt/backup/ecp_full_%U';
Starting backup at 2011-11-08 13:22:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=534 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/opt/oracle/oradata/ecp/users01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/ecp/sysaux01.dbf
input datafile fno=00008 name=/opt/oracle/oradata/ecp/TS_Public_1.003.dbf
input datafile fno=00009 name=/opt/oracle/oradata/ecp/TS_Index_Base.001.dbf
input datafile fno=00010 name=/opt/oracle/oradata/ecp/TS_Index_Base.002.dbf
input datafile fno=00011 name=/opt/oracle/oradata/ecp/TS_Index_Base.003.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/opt/oracle/oradata/ecp/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
input datafile fno=00012 name=/opt/oracle/oradata/ecp/OGG.001.dbf
input datafile fno=00005 name=/opt/oracle/oradata/ecp/example01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/ecp/TS_Public_1.001.dbf
input datafile fno=00007 name=/opt/oracle/oradata/ecp/TS_Public_1.002.dbf
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:22
piece handle=/opt/backup/ecp_full_11mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:23
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:24
piece handle=/opt/backup/ecp_full_13mr52ce_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:25
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:26
piece handle=/opt/backup/ecp_full_14mr52cg_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:22:41
piece handle=/opt/backup/ecp_full_12mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:34
Finished backup at 2011-11-08 13:22:41
--备份sysdata01.dbf文件
RMAN> backup datafile 1 format '/opt/backup/ecp_system_%U';
Starting backup at 2011-11-08 13:23:44
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:46
piece handle=/opt/backup/ecp_system_16mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:47
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:48
piece handle=/opt/backup/ecp_system_17mr52f2_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:24:13
piece handle=/opt/backup/ecp_system_15mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 2011-11-08 13:24:13
--注：所有备份system空间，都是会自动备份spfile和控制文件
RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  A DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--查看所有备份集情况
RMAN> crosscheck backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
--查看全部有效
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--因为system01.dbf备份了两次，所以违背了备份策略
drwxr-xr-x  2 root    root      4096 07-12 15:54 vmdir
[root@node1 opt]# cd /opt/backup/
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
[root@node1 backup]# mv ecp_system_15mr52f1_1_1 ecp_system_15mr52f1_1_1_bak
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1_bak
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
--对备份集中的其中一个文件重命名
RMAN> crosscheck backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 6 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Crosschecked 1 objects
--发现一个无效的备份集
RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  X DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--也标志为无效'X'
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--策略还是显示这两个备份集违背规则
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
deleted backup piece
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
Deleted 2 objects
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
--删除违法策略的备份集
RMAN> delete  NOPROMPT  expired backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
34      34      1   1   EXPIRED     DISK        /opt/backup/ecp_system_15mr52f1_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Deleted 1 EXPIRED objects
RMAN> crosscheck backup;
using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 4 objects
--删除无效的备份集

通过实验说明：crosscheck只能够检测备份集是否有效(最常见的情况就是物理上是否还存在),并且可以用它来删除失效(expired)的备份集，而不是用来删除违背备份策略(obsolete)的备份集，如果要删除违背备份策略(废弃)的备份集，需要使用obsolete操作。出现这个问题的主要原因应该是expired和obsolete翻译成中文的时候理解的出入导致。

清空schema中所有表的comment信息

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：清空schema中所有表的comment信息

今天中午一朋友问我怎么清空一个用户下面所有的表的comment信息（估计是系统要发布或者买出去，不想让人知道表结构的含义），我当时的感觉就是直接去基表中去修改，这样可以一次性实现，于是就做了下面试验，并给他提供了相关sql语句

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 8 12:17:24 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--我们可以通过DBA_COL_COMMENTS视图（或者同义词）查询到表的comment信息
--那么我们通过这个表找到comment的基表是什么表
SQL> set long 100000
SQL> set pages 0
SQL> SELECT DBMS_METADATA.get_ddl('VIEW','DBA_COL_COMMENTS','SYS') FROM DUAL;
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_COL_COMMENTS" ("OWNER", "TABLE_NAME",
  select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
  and o.type# in (2, 4)
  and o.obj# = c.obj#
  and c.obj# = co.obj#(+)
  and c.intcol# = co.col#(+)
  and bitand(c.property, 32) = 0 /* not hidden column */
--通过上面的语句，我们发现col$是存储commet的基表
SQL> desc sys.com$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 COL#                                               NUMBER
 COMMENT$                                           VARCHAR2(4000)
--查询CHF用户下面表的commet情况
SQL> col comment$ for a30
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;
      OBJ#       COL# COMMENT$
---------- ---------- ------------------------------
     67405          1 xifenfei1
     67405          2 xifenfei2
     67405          3 xifenfei3
     67405          8 惜分飞
     67405         13 chf
     67405         17 xifenfei88
     71926          1 feifei
     71926          2 chf
     71926          3 xff
     70870          1 xifenfei
10 rows selected.
--更新基表的comment$的信息为null
SQL> UPDATE SYS.COM$
  2     SET COMMENT$ = NULL
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = OBJ#)
  8     AND COMMENT$ IS NOT NULL;
10 rows updated.
SQL> commit;
Commit complete.
--验证更新成功，chf下面的所有comment信息都变成了null
SQL> SELECT *
  2    FROM SYS.COM$ A
  3   WHERE EXISTS (SELECT 1
  4            FROM DBA_OBJECTS
  5           WHERE OWNER = 'CHF'
  6             AND OBJECT_TYPE LIKE 'TABLE%'
  7             AND OBJECT_ID = A.OBJ#)
  8     AND COMMENT$ IS NOT NULL;
no rows selected
SQL> SELECT * FROM DBA_COL_COMMENTS WHERE comments IS NOT NULL AND owner='CHF';
no rows selected

暴力破解Oracle数据库密码

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：暴力破解Oracle数据库密码

一、验证不能通过修改用户的password实现登录不知道密码的用户

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 12:22:46 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> grant create session to xff identified by xifenfei;
Grant succeeded.
SQL> conn xff/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> grant create session to chf identified by xifenfei;
Grant succeeded.
SQL> conn chf/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> desc user$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            1B60F4BFF1DAB500
SQL> alter user xff identified by values 'F3CF2F0CB35CB6CA';
User altered.
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            F3CF2F0CB35CB6CA
XFF                            F3CF2F0CB35CB6CA
SQL> conn xff/xifenfei
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
Warning: You are no longer connected to ORACLE.
SQL> conn chf/xifenfei
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by values '1B60F4BFF1DAB500';
User altered.
SQL> conn xff/xifenfei
Connected.

注：这个实验使用11g证明，其实10g也是同样的结果；在oracle 9i中可以通过修改password的values值实现登录
二、使用orabf破解数据库密码
1、修改数据库密码

SQL> conn / as sysdba
Connected.
SQL> alter user xff identified by xff01;
User altered.
SQL> alter user chf identified by chf00;
User altered.
SQL> select name,password from user$ where name in('XFF','CHF');
NAME                           PASSWORD
------------------------------ ------------------------------
CHF                            05BD6F8AB28BD8CA
XFF                            A51B3879056B3DDD

2、orabf使用

C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
usage: orabf [hash]:[username] [options]
options:
-c [num]  complexity: a number in [1..6] or a filename
   -      read words from stdin
   [file] read words from file
   1      numbers
   2      alpha
   3      alphanum
   4      standard oracle (alpha)(alpha,num,_,#,$)... (default)
   5      entire keyspace (' '..'~')
   6      custom (charset read from first line of file: charset.orabf)
-m [num]  max pwd len: must be in the interval [1..14] (default: 14)
-n [num]  min pwd len: must be in the interval [1..14] (default: 1)
-r        resume: tries to resume a previous session
C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf A51B3879056B3DDD:XFF
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done
Starting brute force session using charset:
#$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_
press 'q' to quit. any other key to see status
current password: D9X50
9229361 passwords tried. elapsed time 00:00:13. t/s:697938
current password: HI0QJ
18967617 passwords tried. elapsed time 00:00:27. t/s:698403
current password: OB#QD
34743632 passwords tried. elapsed time 00:00:49. t/s:698844
password found: XFF:XFF01
55826385 passwords tried. elapsed time 00:01:19. t/s:704047
C:\Users\XIFENFEI\Downloads\orabf-v0.7.6>orabf 05BD6F8AB28BD8CA:CHF -c 3 -n 4 -m 6
orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...done
Starting brute force session using charset:
0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
press 'q' to quit. any other key to see status
password found: CHF:CHF00
22647601 passwords tried. elapsed time 00:00:31. t/s:719113

说明：-c 6不能正常运行，不清楚是不是因为我的win 7系统原因导致
三、使用ops_sse2破解数据库密码
1、sys用户的password

SQL> select password from user$ where name='SYS';
PASSWORD
------------------------------
18698BFD1A045BCC

2、ops_sse2使用

C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
Usage:
  ops_sse2.exe --hashlist=filename.txt
    [--min=min_password_length] [--max=max_password_length]
    [--first_symbol_charset=characters] [--charset=characters]
    [--results=filename.txt]
hashlist file format:
username:hash:comment_or_SID
By default, results are dumped to stdout.
This can be changed by setting --results option
Default values:
  min_password_length=1
  max_password_length=8
  first_symbol_charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ
  charset=ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789#$_
#ops_file.txt内容
SYS:18698BFD1A045BCC:xff
C:\Users\XIFENFEI\Downloads\ops_SIMD_win32>ops_sse2 --hashlist=ops_file.txt --min=6  --charset=CDEFNHITX
Oracle passwords (DES) solver 0.3 (SSE2) -- Dennis Yurichev <dennis@conus.info>
Compiled @ Apr  5 2011 12:13:15
Demo version, supporting only SYS usernames.
username=SYS: 1 unsolved hash(es) left
Checking 6-symbol passwords for username SYS
overall progress=  0%
username=SYS: 1 unsolved hash(es) left
Checking 7-symbol passwords for username SYS
overall progress= 98% / time remaining:
time elapsed: 12s, ~ 1160449 passwords/hashes per second
username=SYS: 1 unsolved hash(es) left
Checking 8-symbol passwords for username SYS
overall progress= 91% / time remaining: 8s
time elapsed: 1m31s, ~ 1248875 passwords/hashes per second
SYS/xff: Found password: XIFENFEI
SYS:XIFENFEI:xff

说明：Demo version只能使用于破解sys用户的密码，而且秘密长度不能超过8.

综合说明的试验，虽然都有缺陷，但是相对而已还是orabf破解更加的给力点
orabf-v0.7.6下载
 ops_SIMD_win32
ops_SIMD_linux86
参考：忘记oracle 用户密码怎么办？

ORA-600 [12235]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 [12235]

今天早上例行对各个区域数据库服务器进行检查，发现内蒙古电信的数据库服务器出现ORA-600 [12235]错误
一、错误现象

alert_txzldb.log日志：
Sun Nov  6 09:34:57 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_8253.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
txzldb_ora_8253.trc内容：
Oracle program name: oracle@database.localdomain
*** 2011-11-06 09:34:57.530
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+269         call     ksedst()+0           0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksfdmp()+14          call     ksedmp()+0           3 ? BFFFECA0 ? 98584A4 ?
                                                   AD58F60 ? 3 ? A4B929C ?
kgeriv()+188         call     ksfdmp()+0           AD58F60 ? 3 ?
kgesiv()+113         call     kgeriv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ?
ksesic0()+39         call     kgesiv()+0           AD58F60 ? 0 ? 2FCB ? 0 ?
                                                   BFFFED0C ? 2FCB ? 0 ?
                                                   BFFFED0C ?
opirip()+519         call     ksesic0()+0          2FCB ? AD5903C ? BFFFF6AC ?
                                                   FFFFFFFF ? BFFFF814 ? 1 ?
opidrv()+462         call     opirip()+0           32 ? 0 ? 0 ?
sou2o()+25           call     opidrv()+0           32 ? 0 ? 0 ?
main()+355           call     sou2o()+0            BFFFF814 ? 32 ? 0 ? 0 ?
                                                   BFFFF840 ? 0 ?
__libc_start_main()  call     main()+0             1 ? BFFFF894 ? BFFFF89C ?
+161                                               96DFD4 ? 1 ? 8208E40 ?
--------------------- Binary Stack Dump ---------------------
========== FRAME [1] (ksedmp()+269 -> ksedst()+0) ==========
Dump of memory from 0xBFFFEB64 to 0xBFFFEC64
BFFFEB60          BFFFEC64 0820B6F8 00000000      [d..... .....]
BFFFEB70 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
………………

二、数据库版本

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

三、mos信息
根据mos的建议，这个错误可以忽略，不用过多关注

ORA-600 [12235] "Oracle process has no purpose in life !" [ID 33174.1]
ERROR:
  ORA-600 [12235] [a] [b] [/c] [d] [e]
VERSIONS:
  versions 7.0 to 9.2
DESCRIPTION:
  This error shows up when Oracle detects an Oracle defunct process.
  When an Oracle process starts up, it reads data from the SGA that defines
  what type of process it should become.
  If the process does not locate any valid customization data, it reports
  ORA-600 [12235] and exits.
  On a heavily loaded system, ORA-600 [12235] may be a symptom that the server
  process was too slow in starting. That is, the process that initiated the
  new server may timeout waiting for the new process to start and abandon the
  new server request.  In the new server process it is possible that the new
  server reaches the code to customize its operation before the os request to
  kill the process is actioned.  The messages "ksbsrv: No startup
  acknowledgement from forked process ..." and "Timed out trying to start
  shared server ..." may be reported to trace and alert files.
SUGGESTIONS:
  Ignore the error.
  One of the most common reasons for this error to be reported is that
  someone typed 'oracle' manually at the OS prompt.
  If this error is reported regularly and appears not to be explained
  by comments made in this note, contact Oracle Support Services.

四、错误重现
根据mos中的讲述，在系统级别执行oracle，也会出现该错误提示，使用oracle重现结果

[oracle@database ~]$ oracle
[oracle@database ~]$ oracle
[oracle@database ~]$ tail  $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
Mon Nov  7 11:10:25 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_27108.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
Mon Nov  7 11:10:27 2011
Errors in file /opt/oracle/admin/txzldb/bdump/txzldb_ora_1281.trc:
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []

win平台登录sqlplus报ora-01031错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：win平台登录sqlplus报ora-01031错误解决

今天一朋友告诉我，他的win平台的oracle不能在本地使用sqlplus / as sysdba登录数据库，提示ora-01031的错误，他说是administrator用户，应该不会出现权限不足的情况。我的登录上去一看，果真是这样的情况：

01031, 00000, "insufficient privileges"

// *Cause: An attempt was made to change the current username or password

// without the appropriate privilege. This error also occurs if

// attempting to install a database without the necessary operating

// system privileges.

// When Trusted Oracle is configure in DBMS MAC, this error may occur

// if the user was granted the necessary privilege at a higher label

// than the current login.

// *Action: Ask the database administrator to perform the operation or grant

// the required privileges.

// For Trusted Oracle users getting this error although granted the

// the appropriate privilege at a higher label, ask the database

// administrator to regrant the privilege at the appropriate label.
根据这个错误提示，我的第一反应就是当前的用户不属于ora_dba用户组，通过计算机管理–>本地用户和组–>administrator用户属性–>隶属于中只有一个administrators，果然没有ora_dba组

接下来的事情，就是添加ora_dba组到administrator用户中
点击刚刚隶属于下面的添加–>高级–>立即查找–选择ora_dba–点击确定–>再点击选择组中的确定–>点击用户属性的确定

添加把ora_dba添加到administrator用户所属组中，再尝试登录

ok，登录成功了，看来在win系统中，要想使用sqlplus / as sysdba 登录数据库，必须要隶属于ora_dba组，就算administrators组也不能越俎代庖。

ORA-00845: MEMORY_TARGET not supported on this system

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00845: MEMORY_TARGET not supported on this system

在Oracle 11g中如果采用AMM内存管理，那么当MEMORY_TARGET的值大于/dev/shm的时候，就会报ORA-00845: MEMORY_TARGET not supported on this system错误，解决办法增加/dev/shm大小，在redhat系列系统中，/dev/shm的默认值是系统总内存的一半

1、错误重现

SQL>SELECT * FROM V$VERSION;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

SQL>show parameter memory;

NAME TYPE VALUE

———————————— ———– ——————————

hi_shared_memory_address integer 0

memory_max_target big integer 500M

memory_target big integer 500M

shared_memory_address integer 0

SQL>alter system set memory_max_target=800m;

alter system set memory_max_target=800m

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL>alter system set memory_max_target=800m scope=spfile;

System altered.

SQL>alter system set memory_target=800m scope=spfile;

System altered.

SQL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@xifenfei admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:01:18 2011

Connected to an idle instance.

SQL>startup

ORA-00845: MEMORY_TARGET not supported on this system

SQL>!oerr ora 845

00845, 00000, "MEMORY_TARGET not supported on this system"

// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.

// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

2、修改/dev/shm大小

[root@xifenfei ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_xifenfei-lv_root

17G 13G 3.9G 77% /

tmpfs 590M 0 590M 0% /dev/shm

/dev/sda1 485M 30M 430M 7% /boot

[root@xifenfei ~]# mount -o size=900M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm

[root@xifenfei ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_xifenfei-lv_root

17G 13G 3.9G 77% /

tmpfs 900M 0 900M 0% /dev/shm

/dev/sda1 485M 30M 430M 7% /boot

[root@xifenfei ~]# vi /etc/fstab

# /etc/fstab

# Created by anaconda on Sat Nov 5 02:49:30 2011

# Accessible filesystems, by reference, are maintained under '/dev/disk'

# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info

/dev/mapper/vg_xifenfei-lv_root / ext4 defaults 1 1

UUID=7ace6c04-d232-43ac-9ef5-70ea92fe49bd /boot ext4 defaults 1 2

/dev/mapper/vg_xifenfei-lv_swap swap swap defaults 0 0

tmpfs /dev/shm tmpfs defaults,size=900M 0 0

devpts /dev/pts devpts gid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

3、启动数据库验证

[oracle@xifenfei admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 19:03:51 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@XFF>show parameter memory;

NAME TYPE VALUE

———————————— ———– ——————————

hi_shared_memory_address integer 0

memory_max_target big integer 800M

memory_target big integer 800M

shared_memory_address integer 0

4、官方解释
Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer. If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.

5、解决问题建议
5.1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size.

5.2. If configuring AMM is not possible due to lack of space on /dev/shm mount point, you can configure ASMM instead of AMM, i.e. set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET instead of MEMORY_TARGET.

RedHat 6.1安装Oracle 11.2.0.3

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：RedHat 6.1安装Oracle 11.2.0.3

一、操作系统版本
[root@xifenfei stage]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.1 (Santiago)
[root@xifenfei stage]# uname -a
Linux xifenfei 2.6.32-131.0.15.el6.i686 #1 SMP Tue May 10 15:42:28 EDT 2011 i686 i686 i386 GNU/Linux

二、安装数据库版本（安装后之后贴出来的）
[oracle@xifenfei database]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 5 05:50:34 2011

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

三、系统配置
1、检查相应的rpm包的情况(等于或者高于下面版本）
rpm –qa | grep pagename(grep是搜索)
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel 4.1.2
make-3.81
numactl-devel-0.9.8.x86_64
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11 (32 bit)
unixODBC-devel-2.2.11
unixODBC-devel-2.2.11 (32 bit)
pdksh-5.2.14-1 –这个包需要下载，安装前需要卸载掉ksh-20100621-6.el6包
rpm –e rpm -e ksh-20100621-6.el6

安装缺少的rpm包
rpm –ivh pagename(根据提示，如果缺少依赖包，也需要加上去)

2、添加组和用户
/usr/sbin/groupadd oinstall（添加oinstall组）
/usr/sbin/groupadd -g 502 dba（添加dba组）
/usr/sbin/useradd -u 502 -g oinstall -G dba oracle（添加用户oracle，主组oinstall，辅助组dba）
passwd oracle（修改oracle用户密码）

3、创建文件夹并修改组和用户所属关系和相关权限
mkdir -p /opt/oracle(创建文件u01/oracle)
chown -R oracle:oinstall /opt/oracle(使得/opt/oracle文件夹输入oracle用户和oinstall组)
chmod -R 775 /opt/oracle(修改u01文件夹访问权限)—可选（最好执行下）

4、添加/etc/hosts中dns解析信息
192.168.1.60 xifenfei

5、在/etc/security/limits.conf中添加用户限制信息
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle hard stack 10240

6、修改或者添加/etc/sysctl.conf中信息
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 1048576
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

注意注释掉
# Disable netfilter on bridges.
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0
—/sbin/sysctl –p（重新加载这些参数，不用重启系统）

7、编辑vi /etc/profile
if [ $USER = "oracle" ]; then
   if [ $SHELL = "/bin/ksh" ];    then
     ulimit -p 16384
     ulimit -n 65536
   else
     ulimit -u 16384 -n 65536
   fi
fi
–注意空格，在用户切换时使用su – username，这样才会加载后面的环境变量的配置信息（不要使用su username切换）

8、配置环境变量
在oracle用户下的.bash_profile文件中添加下面信息
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=xff
PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH NLS_LANG

四、安装过程
1、执行 runInstaller –注意路径，选择只安装数据库软件
2、执行 netca 创建监听
3、执行 dbca 创建数据库

Oracle 10.2.0.x升级到11.2.0.3

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 10.2.0.x升级到11.2.0.3

一、环境描述
[oracle@node1 ~]$ export ORACLE_SID=chf

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Nov 4 08:59:58 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name;

NAME TYPE VALUE

———————————— ———– ——————————

db_file_name_convert string

db_name string chf

db_unique_name string chf

global_names boolean FALSE

instance_name string chf

lock_name_space string

log_file_name_convert string

service_names string chf

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@node1 ~]$ source .bash_profile

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Nov 4 09:01:25 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter name;

NAME TYPE VALUE

———————————— ———– ——————————

db_file_name_convert string

db_name string ecp

db_unique_name string ecp

global_names boolean FALSE

instance_name string ecp

lock_name_space string

log_file_name_convert string

service_names string ecp

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@node1 ~]$ source ora11g.sh

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 09:00:42 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> show parameter name;

NAME TYPE VALUE

———————————— ———– ——————————

db_file_name_convert string

db_name string ora11g

db_unique_name string ora11g

global_names boolean FALSE

instance_name string ora11g

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string ora11g

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

通过上面的资料可以看出，Oracle 10g中有两个实例分别是chf和ecp，Oracle 11g中有一个实例为ora11g，现在需要做的操作是升级Oracle 10g中的chf为11g

二、升级过程
1、执行11g中的dbua

[oracle@node1 ~]$ export DISPLAY=192.168.9.215:0.0

[oracle@node1 ~]$ dbua

选择需要升级数据库，这里选择chf

升级前可能存在问题，需要处理

Oracle Database Vault没有关闭(关闭后重新运行dbua），具体操作见:Disable/Enable Oracle Database Vault

相关选项

因为em配置需要监听，因为我不用em，所以忽略这些

提示升级间关闭归档

选择升级过程中是否移动数据文件和存储类型

dbua升级汇总

开始升级

提示em没有关闭，因为不用，不管它，最好是升级前关闭这些

升级完成

三、检查结果

[oracle@node1 ~]$ export ORACLE_SID=chf

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 11:07:27 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter name;

NAME TYPE VALUE

———————————— ———– ——————————

db_file_name_convert string

db_name string chf

db_unique_name string chf

global_names boolean FALSE

instance_name string chf

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string chf

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

四、Oracle 10.2.0.5升级到11.2.0.3过程
1、在Oracle 10.2.0.5数据库所在的服务器上安装11.2.0.3数据库软件
2、在11.2.0.3的环境变量下，执行dbua，执行图形化数据库升级操作
3、后续工作：修改spfile中的compatible等操作

Disable/Enable Oracle Database Vault

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Disable/Enable Oracle Database Vault

一、Disable Oracle Database Vault

[oracle@node1 ~]$ sqlplus sys/xifenfei@ora11g as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Nov 4 09:09:00 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault TRUE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ emctl stop dbconsole

[oracle@node1 ~]$ lsnrctl stop

[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@node1 lib]$ pwd

/opt/oracle/product/11.2.0/db_1/rdbms/lib

[oracle@node1 lib]$ make -f ins_rdbms.mk dv_off ioracle

/usr/bin/ar d /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a kzvidv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzvndv.o

chmod 755 /opt/oracle/product/11.2.0/db_1/bin

– Linking Oracle

rm -f /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle

gcc -o /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle -m64 -L/opt/oracle/product/11.2.0/db_1/rdbms/lib/ -L/opt/oracle/product/11.2.0/db_1/lib/ -L/opt/oracle/product/11.2.0/db_1/lib/stubs/ -Wl,-E /opt/oracle/product/11.2.0/db_1/rdbms/lib/opimai.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/ssoraed.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/ttcsoi.o -Wl,–whole-archive -lperfsrv11 -Wl,–no-whole-archive /opt/oracle/product/11.2.0/db_1/lib/nautab.o /opt/oracle/product/11.2.0/db_1/lib/naeet.o /opt/oracle/product/11.2.0/db_1/lib/naect.o /opt/oracle/product/11.2.0/db_1/lib/naedhs.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/config.o -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11 -lrt -lplp11 -lserver11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 `if [ -f /opt/oracle/product/11.2.0/db_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /opt/oracle/product/11.2.0/db_1/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11 -lrt -lplp11 -ljavavm11 -lserver11 -lwwg `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/opt/oracle/product/11.2.0/db_1/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio `cat /opt/oracle/product/11.2.0/db_1/lib/sysliblist` -Wl,-rpath,/opt/oracle/product/11.2.0/db_1/lib -lm `cat /opt/oracle/product/11.2.0/db_1/lib/sysliblist` -ldl -lm -L/opt/oracle/product/11.2.0/db_1/lib

test ! -f /opt/oracle/product/11.2.0/db_1/bin/oracle ||\

mv -f /opt/oracle/product/11.2.0/db_1/bin/oracle /opt/oracle/product/11.2.0/db_1/bin/oracleO

mv /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle /opt/oracle/product/11.2.0/db_1/bin/oracle

chmod 6751 /opt/oracle/product/11.2.0/db_1/bin/oracle

[oracle@node1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 09:21:39 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 1241516232 bytes

Database Buffers 889192448 bytes

Redo Buffers 4947968 bytes

Database mounted.

Database opened.

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault FALSE

二、Enable Oracle Database Vault

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 12:50:40 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@node1 lib]$ make -f ins_rdbms.mk dv_on lbac_on ioracle

/usr/bin/ar d /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a kzvndv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzvidv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzlilbac.o

chmod 755 /opt/oracle/product/11.2.0/db_1/bin

– Linking Oracle

rm -f /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle

test ! -f /opt/oracle/product/11.2.0/db_1/bin/oracle ||\

mv -f /opt/oracle/product/11.2.0/db_1/bin/oracle /opt/oracle/product/11.2.0/db_1/bin/oracleO

mv /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle /opt/oracle/product/11.2.0/db_1/bin/oracle

chmod 6751 /opt/oracle/product/11.2.0/db_1/bin/oracle

[oracle@node1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 12:52:51 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2230912 bytes

Variable Size 201328000 bytes

Database Buffers 411041792 bytes

Redo Buffers 7548928 bytes

Database mounted.

Database opened.

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault TRUE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 lib]$ lsnrctl start

[oracle@node1 lib]$ emctl start dbconsole

三、Oracle Database Vault启用关闭总机
1、关闭实例/EM/监听
2、修改Oracle Database Vault
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle –关闭
make -f ins_rdbms.mk dv_on lbac_on ioracle –开启
3、开启实例/EM/监听

Linux之pmap命令

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Linux之pmap命令

观察数据库发现，ora_dbw进程占用内存非常高，感觉很奇怪，然后询问高手，通过pmap命令发现，该进程是几乎具有了整个sga的句柄，所以显示占用的内存非常大，其实自己私有的很小

[oracle@DB1 ~]$ top -c

top – 23:06:02 up 553 days, 21:46, 1 user, load average: 0.62, 0.58, 0.47

Tasks: 365 total, 1 running, 364 sleeping, 0 stopped, 0 zombie

Cpu(s): 0.7%us, 0.3%sy, 0.0%ni, 98.3%id, 0.5%wa, 0.0%hi, 0.2%si, 0.0%st

Mem: 24666528k total, 24504356k used, 162172k free, 9192k buffers

Swap: 24579440k total, 3864056k used, 20715384k free, 18728188k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

2270 oracle 15 -1 20.1g 1.1g 1.1g S 2.7 4.7 147:10.01 oracleora9i (LOCAL=NO)

4334 oracle 14 -1 20.1g 1.7g 1.7g S 1.3 7.4 3:08.38 oracleora9i (LOCAL=NO)

5491 oracle 15 -1 20.2g 14g 14g S 1.3 62.1 699:54.55 ora_dbw1_ora9i

9682 oracle 15 0 12868 1296 820 R 1.3 0.0 0:00.14 top -c

18552 oracle 14 -1 20.1g 1.7g 1.7g S 1.3 7.2 2:43.74 oracleora9i (LOCAL=NO)

29977 oracle 14 -1 20.1g 2.1g 2.1g S 1.3 9.1 4:50.87 oracleora9i (LOCAL=NO)

SQL> show parameter sga;

NAME TYPE VALUE

———————————— ———– ——————————

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 20G

sga_target big integer 20G

[oracle@DB1 ~]$ pmap

Usage: pmap [-x | -d] [-q] pid…

-x show details

-d show offset and device number

-q quiet; less header/footer info

-V show the version number

[oracle@DB1 ~]$ pmap -d 5491

5491: ora_dbw1_ora9i

Address Kbytes Mode Offset Device Mapping

0000000000400000 100412 r-x– 0000000000000000 008:00005 oracle

000000000680f000 544 rwx– 000000000620f000 008:00005 oracle

0000000006897000 148 rwx– 0000000006897000 000:00000 [ anon ]

000000001f2f7000 596 rwx– 000000001f2f7000 000:00000 [ anon ]

0000000060000000 20971520 rwxs- 0000000000000000 000:00009 [ shmid=0x0 ]

0000000580000000 2048 rwxs- 0000000000000000 000:00009 [ shmid=0x8001 ]

0000003b44a00000 112 r-x– 0000000000000000 008:00001 ld-2.5.so

……

00002b293c72b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c73b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c74b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c75b000 3840 —– 0000000000b2a000 000:00011 zero

00007fff710f7000 84 rwx– 00007ffffffea000 000:00000 [ stack ]

ffffffffff600000 8192 —– 0000000000000000 000:00000 [ anon ]

mapped: 21145976K writeable/private: 19316K shared: 20973572K

[oracle@DB1 ~]$ pmap -x 5491

5491: ora_dbw1_ora9i

Address Kbytes RSS Anon Locked Mode Mapping

0000000000400000 100412 – – – r-x– oracle

000000000680f000 544 – – – rwx– oracle

0000000006897000 148 – – – rwx– [ anon ]

000000001f2f7000 596 – – – rwx– [ anon ]

0000000060000000 20971520 – – – rwxs- [ shmid=0x0 ]

0000000580000000 2048 – – – rwxs- [ shmid=0x8001 ]

……

00002b293c73b000 64 – – – rwx– zero

00002b293c74b000 64 – – – rwx– zero

00002b293c75b000 3840 – – – —– zero

00007fff710f7000 84 – – – rwx– [ stack ]

ffffffffff600000 8192 – – – —– [ anon ]

—————- —— —— —— ——

total kB 21145976 – – –

Address:进程所占的地址空间

Kbytes 该虚拟段的大小

RSS 设备号（主设备：次设备）

Anon 设备的节点号，0表示没有节点与内存相对应

Locked 是否允许swapped

Mode 权限：r=read, w=write, x=execute, s=shared, p=private(copy on write)

Mapping: bash 对应的映像文件名

月度归档：11月 2011