Monthly Archives: 十月 2011

清除离线数据文件记录

Posted on by

测试前提：数据文件离线，系统上删除了该文件，需要删除在数据字典中，关于这条离线数据文件记录

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--数据库是归档模式

SQL> col file_name for a40
SQL>  select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/oradata/test/odu03.chf                            

10 rows selected.

SQL> col error for a20
SQL>  select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE NOT FOUND                0

SQL> !ls /opt/oracle/oradata/test/odu03.chf
ls: /opt/oracle/oradata/test/odu03.chf: No such file or directory
--说明该数据文件已经从硬盘上删除

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

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
        ,'/opt/oracle/oradata/test/odu03.chf'     --文件不存在，创建控制文件这条记录需要除掉
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.


SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010     --系统默认创建了自定义的数据文件名称

10 rows selected.

SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE MISSING                  0  
--提示该文件是离线状态，需要恢复，结果同开始时候状态

SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1;

     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          1          2
         3          2          2          3
         4          2          4          4
         5          2          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        10          2          7         10
        11          1

11 rows selected.

SQL> delete from file$ where file#=10;    ---重要的就是这个操作

1 row deleted.

SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1;

     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          1          2
         3          2          2          3
         4          2          4          4
         5          2          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        11          1

10 rows selected.

SQL> col name for a40
SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010

10 rows selected.
--需要重建控制文件，删除不存在的数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600

9 rows selected.

SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf

9 rows selected.

补充说明：非归档模式下，NOARCHIVELOG创建控制文件，其他无太大区别
测试来源：itpub:数据文件物理性删除相关问题疑惑?
参考blog：roger:如何彻底删除已经不存在的数据文件？

设置oracle 含特殊字符密码

Posted on by

oracle 修改设置密码复杂度渐增方法：
1、修改为常见密码（无特殊字符）

SQL> alter user chf identified by xifenfei;

User altered.

2、修改含一般特殊字符（如：$, %等）
SQL>
SQL> alter user chf identified by “xi%,fenfei”;

User altered.

SQL> conn chf/xi%,fenfei
Connected.

3、修改含”的特殊字符
3.1）修改制定用户密码（sys用户操作）
SQL> password chf
Changing password for chf
New password:
Retype new password:
Password changed
SQL> conn chf/aa””bb
Connected.

3.2）修改当前用户密码（需要有修改密码权限）
SQL> password
Changing password for CHF
Old password:
New password:
Retype new password:
Password changed
SQL>
注：因为一般特殊字符可以使用双引号处理，但是如果密码中含有双引号，就不能用双引号处理，可以直接使用password修改密码

sys用户密码含$ sqlplus登录数据库诡异事件分析

Posted on by 
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:37:51 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

SQL> alter user sys identified by "ab$";

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$ as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:38:53 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

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$abc as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:39:05 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

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:40:06 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

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc11 as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:44:11 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

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL>

通过以上sql发现，把sys的密码改为ab$后，无论是什么tns都可以登录数据库，而且都是本地数据库，是不是感觉很诡异，其实你仔细观察发现，密码中有了$，使得$@的操作都变成了无效的，其实就是sqlplus sys/123(随意) as sysdba方式登录本地数据库

[oracle@ECP-UC-DB1 ~]$ sqlplus sys/123 as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:48:23 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

SQL>

Oracle 小升级 opatch apply使用

Posted on by

这里记录的是Oracle 升级到10.2.0.4后，出现EM不能正常启动情况下，Oracle提示需要打上Patch 8350262，下面是记录打补丁过程

[oracle@ocp ~]$ unzip ~/p8350262_10204_Generic.zip 
Archive:  /home/oracle/p8350262_10204_Generic.zip
   creating: 8350262/
  inflating: 8350262/killDBConsole   
   creating: 8350262/files/
   creating: 8350262/files/sysman/
   creating: 8350262/files/sysman/jlib/
   creating: 8350262/files/sysman/jlib/emCORE.jar/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class  
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class  
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class  
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class  
   creating: 8350262/files/sysman/jlib/emd_java.jar/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class  
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class  
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class  
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class  
  inflating: 8350262/killDBConsole.pl  
  inflating: 8350262/README.txt      
   creating: 8350262/etc/
   creating: 8350262/etc/xml/
  inflating: 8350262/etc/xml/ShiphomeDirectoryStructure.xml  
  inflating: 8350262/etc/xml/GenericActions.xml  
   creating: 8350262/etc/config/
  inflating: 8350262/etc/config/inventory  
  inflating: 8350262/etc/config/actions  

[oracle@ocp ~]cd 8350262
[oracle@ocp 8350262]$ $ORACLE_HOME/OPatch/opatch apply     --Note：$ORACLE_HOME/OPatch/opatch路径
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/oracle/app/10.2.0/db_1/oui
Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-11-40PM.log

ApplySession applying interim patch '8350262' to OH '/opt/oracle/app/10.2.0/db_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '8350262' for restore. This might take a while...
Backing up files affected by the patch '8350262' for rollback. This might take a while...

Patching component oracle.sysman.agent.core, 10.2.0.4.0a...
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class"
ApplySession adding interim patch '8350262' to inventory

Verifying the update...
Inventory check OK: Patch ID 8350262 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8350262 are present in Oracle Home.

OPatch succeeded.

[oracle@ocp 8350262]$ ../opatch lsinventory  --Note检测是否安装成功
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/oracle/app/10.2.0/db_1/oui
Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-18-34PM.log

Lsinventory Output file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-10-17_18-18-34PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2): 

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch  8350262      : applied on Mon Oct 17 18:17:28 CST 2011
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262



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

OPatch succeeded.

p8350262_10204_Generic
p8350262_10205_Generic