Oracle 11g Administrator’s Guide笔记2:如何手工创建、删除11gR2数据库?

在前一篇文章中提到阅读Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时,简单描述了DDL_LOCK_TIMEOUT参数。在Oracle 11gR2 Administrator‘s Guide文档的chapter 2 Creating and Configuring an Oracle Database章节时,提到手工建库,本文简单记录一下,如何在OEL 5.5 X86_64位环境下,手工创建及删除11.2.0.1.0的数据库,将文件放到文件系统上存放。当然,利用DBCA来建库、删库比较简单,就不再赘述了。具体操作步骤如下:

1 前提条件,操作系统上已经安装好Oracle 11gR2 的软件,其中环境变量ORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[root@OCM11g ~]# su - oracle
OCM11g-> env | grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
OCM11g->

2 确认环境变量,本例中选择ORACLE_SID=manualdb,ORACLE_HOME选用原有的/u01/app/oracle/product/11.2.0/db_1

OCM11g-> id
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
OCM11g-> uname -rm
2.6.18-194.el5 x86_64
OCM11g-> export ORACLE_SID=manualdb
OCM11g-> env | grep ORA
ORACLE_SID=manualdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
OCM11g->

3 创建初始化参数文件,这里将参数文件放到系统默认的路径下,即$ORACLE_HOME/dbs,命名为initmanualdb.ora,其内容如下:

OCM11g-> cat /u01/app/oracle/product/11.2.0/db_1/dbs/initmanualdb.ora 
db_name=manualdb
memory_target=600m
OCM11g->

简单起见,我们只在初始化参数文件中指定了db_name,memory_target这两个参数。我们知道,启动数据库实例的最少参数只需要一个db_name即可,这里选择db_name=manualdb,附加一个memory_target=600m,这个是11g的新参数,用于控制SGA+PGA的总大小。当然,也可以在参数文件中指定control_files,如果该参数被忽略的话,那么Oracle会自动在$ORACLE_HOME/dbs路径下创建一个名为cntrl$ORACLE_SID.dbf的控制文件。

4 准备将来存放数据文件、日志文件的路径。这两类文件的路径如下:

datafile:

OCM11g-> mkdir -p /u02/manualdb/oradata

online log:

OCM11g-> mkdir -p /u02/manualdb/onlinelog
OCM11g-> ll /u02/manualdb/
total 8
drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 onlinelog
drwxr-xr-x 2 oracle oinstall 4096 Mar 30 10:56 oradata
OCM11g->

5 启动manualdb实例到NOMOUNT状态,其实,在该环境下,我们的控制文件尚未生成,实例至多也只能加载到NOMOUNT状态:

OCM11g-> id
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
OCM11g-> env | grep ORA
ORACLE_SID=manualdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
OCM11g-> sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 30 10:59:32 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             373297144 bytes
Database Buffers          243269632 bytes
Redo Buffers                7544832 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL>

上述报错ORA-00205提示控制文件找不到,正是我们所预见的,因为当前控制文件还未生成,所以数据库没法MOUNT,说明数据库是NOMOUNT状态。这时,也可以看到相关的后台进程已经启动:

SQL> !
OCM11g-> ps -ef | grep ora_|grep -v grep
oracle   14885     1  0 11:02 ?        00:00:00 ora_pmon_manualdb
oracle   14887     1  0 11:02 ?        00:00:00 ora_vktm_manualdb
oracle   14891     1  0 11:02 ?        00:00:00 ora_gen0_manualdb
oracle   14893     1  0 11:02 ?        00:00:00 ora_diag_manualdb
oracle   14895     1  0 11:02 ?        00:00:00 ora_dbrm_manualdb
oracle   14897     1  0 11:02 ?        00:00:00 ora_psp0_manualdb
oracle   14899     1  0 11:02 ?        00:00:00 ora_dia0_manualdb
oracle   14903     1  6 11:02 ?        00:00:27 ora_mman_manualdb
oracle   14905     1  0 11:02 ?        00:00:01 ora_dbw0_manualdb
oracle   14907     1  0 11:02 ?        00:00:00 ora_lgwr_manualdb
oracle   14909     1  0 11:02 ?        00:00:00 ora_ckpt_manualdb
oracle   14911     1  0 11:02 ?        00:00:00 ora_smon_manualdb
oracle   14913     1  0 11:03 ?        00:00:00 ora_reco_manualdb
oracle   14915     1  0 11:03 ?        00:00:00 ora_mmon_manualdb
oracle   14917     1  0 11:03 ?        00:00:00 ora_mmnl_manualdb
OCM11g->

6 执行下述CREATE DATABASE的命令,开始手工创建Oracle 数据库:

SQL> conn / as sysdba;
Connected.
SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-01507: database not mounted

SQL> CREATE DATABASE manualdb
  2     USER SYS IDENTIFIED BY oracle
  3     USER SYSTEM IDENTIFIED BY oracle
  4     LOGFILE GROUP 1 ('/u02/manualdb/onlinelog/redo01a.log') SIZE 50M BLOCKSIZE 512,
  5             GROUP 2 ('/u02/manualdb/onlinelog/redo02a.log') SIZE 50M BLOCKSIZE 512
  6     MAXLOGFILES 5
  7     MAXLOGMEMBERS 5
  8     MAXLOGHISTORY 1
  9     MAXDATAFILES 100
 10     CHARACTER SET AL32UTF8
 11     NATIONAL CHARACTER SET AL16UTF16
 12     EXTENT MANAGEMENT LOCAL
 13     DATAFILE '/u02/manualdb/oradata/system01.dbf' SIZE 300M REUSE
   SYSAUX DATAFILE '/u02/manualdb/oradata/sysaux01.dbf' SIZE 300M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u02/manualdb/oradata/users01.dbf'
 17        SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
 18     DEFAULT TEMPORARY TABLESPACE tempts1
 19        TEMPFILE '/u02/manualdb/oradata/temp01.dbf'
 20        SIZE 20M REUSE
 21     UNDO TABLESPACE undotbs
 22        DATAFILE '/u02/manualdb/oradata/undotbs01.dbf'
 23        SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
MANUALDB  READ WRITE

SQL>

7 创建数据字典视图,分别执行下述脚本来完成:

SQL> @?/rdbms/admin/catalog.sql
......
......
SQL> @?/rdbms/admin/catproc.sql
......
......
SQL> @?/sqlplus/admin/pupbld.sql
......
......

上述3个脚本的说明见下表:

The Scripts and descriptions

Script

Description

CATALOG.SQL

Creates the views of the data dictionary tables,

the dynamic performance views,and public synonyms

for many of the views.Grants PUBLIC access to the synonyms.

CATPROC.SQL

Runs all scripts required for or used with PL/SQL.

PUPBLD.SQL

Required for SQL*Plus. Enables SQL*Plus to disable

commands by user.

8 至此,我们完成了手工创建Oracle 数据库。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
MANUALDB  READ WRITE

1 row selected.

SQL> select name from v$datafile
  2  union
  3  select name from v$controlfile
  4  union
  5  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlmanualdb.dbf
/u02/manualdb/onlinelog/redo01a.log
/u02/manualdb/onlinelog/redo02a.log
/u02/manualdb/oradata/sysaux01.dbf
/u02/manualdb/oradata/system01.dbf
/u02/manualdb/oradata/undotbs01.dbf
/u02/manualdb/oradata/users01.dbf

7 rows selected.

SQL>

9 最后,如果该数据库不需要的话。我们可以执行手工删除数据库,当然必须要将数据库启动到MOUNT RESTRICT状态来删除

如何确认实例是否是RESTRICTED MODE:

SQL> select instance_name,status,startup_time,logins from v$instance;

INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
--------------- ------------ ------------------- ----------
manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED

1 row selected.

SQL> alter system enable restricted session;

System altered.

SQL> select instance_name,status,startup_time,logins from v$instance;

INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
--------------- ------------ ------------------- ----------
manualdb        MOUNTED      2012/03/30 13:38:52 RESTRICTED

1 row selected.

SQL> alter system disable restricted session;

System altered.

SQL> select instance_name,status,startup_time,logins from v$instance;

INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
--------------- ------------ ------------------- ----------
manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED

1 row selected.

SQL>

通过查看V$INSTANCE动态性能视图的LOGINS字段,如果该值为ALLOWED,说明实例是正常启动,并未进入RESTRICTED MODE,普通用户可以正常访问;如果该值为RESTRICTED说明是RESTRICTED MODE,即需要具有RESTRICTED SESSION系统权限的用户方可访问;

手工删除数据库:

SQL> select instance_name,status,startup_time,logins from v$instance;

INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
--------------- ------------ ------------------- ----------
manualdb        MOUNTED      2012/03/30 13:38:52 ALLOWED

1 row selected.

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL> alter system enable restricted session;

System altered.

SQL> select instance_name,status,startup_time,logins from v$instance;

INSTANCE_NAME   STATUS       STARTUP_TIME        LOGINS
--------------- ------------ ------------------- ----------
manualdb        MOUNTED      2012/03/30 13:38:52 RESTRICTED

1 row selected.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

此时,Oracle自动删除该数据库的所有数据文件、联机重做日志文件、控制文件以及初始化参数文件。从alert日志文件里可以看到下述信息:

Fri Mar 30 13:45:33 2012
drop database
Deleted file /u02/manualdb/oradata/system01.dbf
Deleted file /u02/manualdb/oradata/sysaux01.dbf
Deleted file /u02/manualdb/oradata/undotbs01.dbf
Deleted file /u02/manualdb/oradata/users01.dbf
Deleted file /u02/manualdb/onlinelog/redo01a.log
Deleted file /u02/manualdb/onlinelog/redo02a.log
Deleted file /u02/manualdb/oradata/temp01.dbf
Deleted file /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_manualdb.f

10 简单总结,可见手工创建、删除数据库也很简单。当然,在第6步中执行手工建库的命令中还有诸多其它选项,比如选择OMF,这时,只要在参数文件中指定DB_CREATE_FILE_DEST参数,那么建库的命令将更加简单。

奇怪的ORA-04043错误及解决方法

           在查询数据字典表,DBA_DATA_FILES时,遇见一则有趣的ORA-04043错误,错误重现及解决办法见下述:

           1数据库版本,正常情况下,可以查看DBA_DATA_FILES数据字典表:

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> 

           2重现错误:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

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


SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

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


SQL> 

           3解决办法:

SQL> show user;
USER is "SYS"
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

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


SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> 

           或者,先shutdown,然后startup,也可以避免ORA-04043错误

           描述:当数据库处于MOUNT状态时,如果试图去访问DBA_之类的数据字典表时,数据库会报出ORA-04043错误!即使,接下来,ALTER DATABASE OPEN,将数据库置于OPEN状态,再尝试去查看DBA_之类的数据字典表时,数据库依然会报出ORA-04043错误!解决的办法有两个:

  •           ① 重启数据库;
  •           ② 刷出共享池:ALTER SYSTEM FLUSH SHARED_POOL

          补充,经测试,在数据库MOUNT状态尝试去查看SYS用户的普通表的话,也会报出同样的错误,看来不单是数据字典表了,具体见下述:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


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


SQL> desc hr.t;
ERROR:
ORA-04043: object hr.t does not exist


SQL> alter database open;

Database altered.

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


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


SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> desc test_04043
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)

SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> select * from test_04043;

        ID NAME
---------- ------------------------------
         1 The F**king ORA-04043 Error.
         2 WWW.OracleOnLinux.CN

SQL> 

                  从上,可以看出,在数据库MOUNT状态下,试图去查看数据字典表和SYS用户下的普通表,会报ORA-04043错误,将数据库OPEN之后,再去查看这些对象时,依然会报错,而普通用户的对象却不会出现这个错误!

                  究其原因,这是个Oracle的bug,Bug 2365821,可见Metalink上的ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1],受影响的数据库版本:Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.1 – Release: 8.1.7 to 11.2,This problem can occur on any platform.

Oracle 11g Automatic Maintenance Tasks

            从Oracle 11g开始,Oracle的自动化维护任务更智能化了,默认情况下,在安装数据库的过程中,如果启用自动化维护任务的话,数据库会在工作日的每晚22:00到第二天的凌晨2:00,周末的凌晨6:00到第二天的凌晨2:00,自动对数据库进行诸如优化器的统计信息收集、自动SQL的优化。且在自动化维护的过程中,数据库会使用较少的CPU资源,以防止自动化维护任务使用到过多的资源而影响到用户的正常使用。

           其一,在使用DBCA建库的时候,我们可以看到这一自动维护任务的选项:

           其二,如果启用了自动化维护任务的话,也可以从数据库的alert日志里看到下述信息:

Fri Mar 23 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3007]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Mar 23 22:00:00 2012
Starting background process VKRM
Fri Mar 23 22:00:00 2012
VKRM started with pid=24, OS id=10743
Fri Mar 23 22:00:33 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:01:32 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:05:56 2012
Thread 1 cannot allocate new log, sequence 8
Private strand flush not complete
  Current log# 1 seq# 7 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 7 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 8 (LGWR switch)
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 02:00:00 2012
Clearing Resource Manager plan via parameter
Sat Mar 24 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Mar 24 06:00:00 2012
Starting background process VKRM
Sat Mar 24 06:00:01 2012
VKRM started with pid=29, OS id=15277
Sat Mar 24 06:00:21 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:00:49 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:02:05 2012
Thread 1 cannot allocate new log, sequence 9
Private strand flush not complete
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 3 seq# 9 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 9 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sat Mar 24 08:56:14 2012
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sat Mar 24 14:06:51 2012
Thread 1 cannot allocate new log, sequence 11
Private strand flush not complete
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 11 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 18:11:25 2012
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 12 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 02:00:00 2012
Clearing Resource Manager plan via parameter
Sun Mar 25 02:00:30 2012
Thread 1 advanced to log sequence 13 (LGWR switch)
  Current log# 1 seq# 13 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 13 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sun Mar 25 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Mar 25 06:00:00 2012
Starting background process VKRM
Sun Mar 25 06:00:00 2012
VKRM started with pid=29, OS id=21059
Sun Mar 25 06:00:15 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 06:00:33 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 08:56:21 2012
Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sun Mar 25 14:08:09 2012
Thread 1 cannot allocate new log, sequence 15
Private strand flush not complete
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 22:08:35 2012 
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 16 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Mon Mar 26 02:00:00 2012 
Clearing Resource Manager plan via parameter
Mon Mar 26 10:14:25 2012 
Thread 1 advanced to log sequence 17 (LGWR switch)
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Mon Mar 26 22:00:00 2012 
Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Mar 26 22:00:00 2012 
Starting background process VKRM 
Mon Mar 26 22:00:00 2012 
VKRM started with pid=24, OS id=9542 
Mon Mar 26 22:00:30 2012 
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:00:59 2012 
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:02:17 2012 
Thread 1 cannot allocate new log, sequence 18
Private strand flush not complete
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 18 (LGWR switch)
  Current log# 3 seq# 18 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 18 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Tue Mar 27 02:00:00 2012 
Clearing Resource Manager plan via parameter

           从上,上周五晚上22:00(Fri Mar 23 22:00:00 2012)到上周六凌晨2:00(Sat Mar 24 02:00:00 2012)数据库执行自动化维护任务,上周六、日凌晨6:00到次日凌晨2:00数据库执行自动化维护任务,本周一自动化维护任务的窗口又开始回到晚上22:00到次日凌晨2:00点之间。可见,Oracle 11g相比以前版本的数据库更加自动化、智能化。

Oracle 11g Administrator’s Guide笔记1:DDL_LOCK_TIMEOUT初始化参数介绍

           今天在看Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时,在第102页读到一个新的初始化参数:DDL_LOCK_TIMEOUT该参数是从11g Release 1开始已经引入的,用来控制执行DDL语句时需要获取独占锁(exclusive locks)的延时时间,如果在该参数指定的时间范围内获取不到独占锁的话,那么DDL语句就会失败。

           DDL_LOCK_TIMEOUT参数的取值范围是0~1000000秒,是一个整形参数,既可以在会话级别(ALTER SESSION)也可以在系统级别(ALTER SYSTEM)动态调整。其默认值是0,也就是说,执行DDL语句是要求立即获取独占锁资源,如果无法获取,那么所执行的DDL语句将立即报错。

           我们也知道,执行DML语句时需要获取2个锁资源:

  •            针对正在更新的一行或者多行的一个行级独占锁EXCLUSIVE,防止在事务结束之前有其它会话修改了被锁定行的数据,也叫排他锁;
  •            ② 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话锁定整个表(可能会删除、截断该表,或修改了该表的表结构),也叫共享锁;

          正是由于锁的特征,结合DDL_LOCK_TIMEOUT初始化参数,实验如下:

          会话1,创建一张表,并在该表上执行DML事务,且不提交,使该DML事务获取上述的两个锁资源:

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter ddl_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL> conn hr/hR;
Connected.
SQL> create table t(id number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

        ID
----------
         1

SQL> 

          会话2,会话级别修改DDL_LOCK_TIMEOUT初始化参数超出0~1000000范围值,直接报错:

SQL> conn hr/hR;
Connected.
SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000


SQL> 

          会话2,执行DDL语句,使之需要获取独占锁,立即抛出错误,因为DDL_LOCK_TIMEOUT初始化参数默认值是0:

SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> 

          会话2,调整DDL_LOCK_TIMEOUT初始化参数为30秒,在30秒内获取不到独占锁,最终抛出错误:

SQL> alter session set ddl_lock_timeout=30;

Session altered.

SQL> set time on; 
17:00:26 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


17:00:58 SQL> 

          会话2重新执行DDL,当会话1提交事务后,会话2在30秒内获取到独占锁,DDL执行成功:

17:03:13 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';

Table altered.

17:03:21 SQL> select * from t;

        ID NAME
---------- --------------------
         1 www.OracleOnLinux.cn

17:03:32 SQL> 

         

         Oracle 11gR2 Administrator's Guide,仍然需要多读多看,多做实验。

如何禁用Oracle 11g口令大小写?

        我们知道,从Oracle 11g开始,默认情况下,数据库用户的口令严格区分大小写,这有别于以前版本的口令不区分大小写。

        11g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 

        10g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> conn HUMAN/hr;
Connected.
SQL> conn HUMAN/HR;
Connected.
SQL> 

        而且,在DBA_USERS数据字典表的PASSWORD列中已经不再存储加密的口令,在11g以前版本的数据库中可以直接从DBA_USERS数据字典表中获取用户的加密口令,那么在11g数据库里如何查看用户加密后的口令呢?答案是需要查看查看USER$字典表,当然在10g版本的数据库中,也可以从USER$字典表中查看用户的加密口令:

        11g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select username,password from dba_users where username='HR';

USERNAME                       PASSWORD
------------------------------ ------------------------------
HR

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> 

        10g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select username,password from dba_users where username='HUMAN';

USERNAME                       PASSWORD
------------------------------ ------------------------------
HUMAN                          CBFA7677C00F9AE1

SQL> select name,password from user$ where name='HUMAN';

NAME                           PASSWORD
------------------------------ ------------------------------
HUMAN                          CBFA7677C00F9AE1

SQL> 

        Oracle为什么会这么做呢?应该是为了保护数据库的安全性,才这么做的吧。在11g中,需要拥有SELECT ANY DICTIONARY的角色才可用查看USER$字典表。当然,我们不建议将SYS.USER$表的访问权限给普通用户,也不建议将SELECT ANY DICTIONARY的角色给普通用户。

        那么,在11g数据库中如何禁用口令大小写呢?

SQL> show parameter sensitive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba;
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> show parameter sensitive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
Connected.
SQL> 

        最后,从下,我们可以看到虽然用户的口令是大小写区分的,但是存放用户口令的密文却是一样的

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> alter user hr identified by Hr;

User altered.

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> alter user hr identified by hR;

User altered.

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> 

          从上,我们可以看到,在11g中,不管用户的口令是大写还是小写,最终存放在数据库中的密文口令都是相同的。应该是,Oracle在对口令加密之前,统一转换为大写或者是小写后,然后开始对口令加密,最后形成加密口令。那么究竟是大写还是小写呢,或者是什么其他手段就不得而知了,还有就是Oracle采用的是什么加密算法,我想这些oracle是绝对不会对外公布的^_^

解决11gR2 RAC主库SWITCHOVER_STATUS为FAILED DESTINATION案例一则

简单记录一则处理11gR2 RAC主库的SWITCHOVER_STATUS为FAILED DESTINATION的案例

主库为OEL 5.5 X86_64位的11gR2的双节点RAC,物理备库是OEL 5.5 X86_64位的单实例库,在主库的V$DATABASE动态性能表中看到节点2的SWITCHOVER_STATUS状态为FAILED DESTINATION:

SQL> select inst_id,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

INST_ID CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE FOR OPEN_MODE  SWITCHOVER_STATUS
------- ----------- -------------------- ------------- --- ---------- --------------------
      2    16961718 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE FAILED DESTINATION
      1    16961719 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE RESOLVABLE GAP

SQL>

并且在备库的alert文件里看到下述错误信息:

 

.....
Fri Mar 16 09:29:11 2012
Error 1031 received logging on to the standby
FAL[client, ARC1]: Error 1031 connecting to devdb2 for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc:
ORA-01031: insufficient privileges
Errors in file /u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc:
ORA-01031: insufficient privileges
Fri Mar 16 09:33:06 2012
...

同样看到/u01/app/oracle/diag/rdbms/phydb/phydb/trace/phydb_arc1_3897.trc里也有类似的信息:

 

*** 2012-03-16 09:29:11.683
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
*** 2012-03-16 09:29:11.738 4132 krsh.c
Error 1031 received logging on to the standby
*** 2012-03-16 09:29:11.741 4132 krsh.c
FAL[client, ARC1]: Error 1031 connecting to devdb2 for fetching gap sequence
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges

基本可以判断出是物理备库无法连接主库的节点2,提示权限不够,极有可能是口令文件的配置造成问题的原因。

果然,在主库上重新修改SYS用户口令之后,并将口令文件SCP至物理备库后,过一段时间后,物理备库alert文件出现下述正常信息:

Fri Mar 16 09:45:44 2012
RFS[5]: Assigned to RFS process 4212
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 6051
RFS[5]: Opened log for thread 2 sequence 239 dbid 676938241 branch 772208260
Archived Log entry 496 added for thread 2 sequence 239 rlc 772208260 ID 0x28b1d7da dest 2:
RFS[5]: Opened log for thread 2 sequence 240 dbid 676938241 branch 772208260
Archived Log entry 497 added for thread 2 sequence 240 rlc 772208260 ID 0x28b1d7da dest 2:
RFS[5]: Opened log for thread 2 sequence 241 dbid 676938241 branch 772208260
Archived Log entry 498 added for thread 2 sequence 241 rlc 772208260 ID 0x28b1d7da dest 2:
Fri Mar 16 09:45:48 2012

到主库上重新查看SWITCHOVER_STATUS状态信息:

 

SQL> select inst_id,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

INST_ID CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE FOR OPEN_MODE  SWITCHOVER_STATUS
------- ----------- -------------------- ------------- --- ---------- --------------------
      2    16961718 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE TO STANDBY
      1    16961719 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE LOG SWITCH GAP

SQL>

备库执行Redo Apply:

 

SQL> alter database recover managed standby database using current logfile disconnect from session;

最后,主库双节点日志全部传输至物理备库,且已经全部被应用:

 

SQL> select name,thread#,sequence#,applied from v$archived_log ;
NAME                                                            THREAD#  SEQUENCE# APPLIED
------------------------------------------------------------ ---------- ---------- ---------
/home/oracle/arch/ARC_1_0000000335_772208260.arc                      1        335 YES
/home/oracle/arch/ARC_2_0000000233_772208260.arc                      2        233 YES
/home/oracle/arch/ARC_2_0000000234_772208260.arc                      2        234 YES
/home/oracle/arch/ARC_2_0000000235_772208260.arc                      2        235 YES
/home/oracle/arch/ARC_2_0000000236_772208260.arc                      2        236 YES
/home/oracle/arch/ARC_1_0000000336_772208260.arc                      1        336 YES
/home/oracle/arch/ARC_1_0000000338_772208260.arc                      1        338 YES
/home/oracle/arch/ARC_1_0000000337_772208260.arc                      1        337 YES
/home/oracle/arch/ARC_1_0000000339_772208260.arc                      1        339 YES
/home/oracle/arch/ARC_2_0000000239_772208260.arc                      2        239 YES
/home/oracle/arch/ARC_2_0000000240_772208260.arc                      2        240 YES

NAME                                                            THREAD#  SEQUENCE# APPLIED
------------------------------------------------------------ ---------- ---------- ---------
/home/oracle/arch/ARC_2_0000000241_772208260.arc                      2        241 YES
/home/oracle/arch/ARC_1_0000000340_772208260.arc                      1        340 IN-MEMORY
/home/oracle/arch/ARC_2_0000000238_772208260.arc                      2        238 YES
/home/oracle/arch/ARC_2_0000000237_772208260.arc                      2        237 YES
/home/oracle/arch/ARC_2_0000000242_772208260.arc                      2        242 YES

至此,解决问题。在本案例中导致物理备库的SWITCHOVER_STATUS为FAILED DESTINATION的原因是口令文件出错,导致的,简单记录一下

如何修改11gR2 RAC PRIVATE IP

           在一套数据库系统搭建之前,本应该认真规划存储、网络、主机名等基本配置信息的,一旦确定下来,就不要轻易去改诸如hostname、IP地址等配置信息。除非不得已或规划有问题的前提下,我们需要不得已而为之。这不,自己就犯下了这个错误:之前在OEL 5.5 X86_64位系统上搭建了一套11gR2 的双节点RAC做测试用,用完之后,就连两台服务器都shutdown。后来,又搭建了一套新的OEL 5.5 X86_64+11gR2 RAC的开发系统,由于自己的疏忽,这套开发用的RAC机器的私有IP(private IP)同原来的那套测试RAC私有IP地址冲突,现在想用原来的那套RAC问题就来了,根本就无法使用,因为测试RAC根本启不来!!!自己搬石头砸自己脚啊,不过呢,好在自己给这套开发的RAC系统搭建了一套物理dataguard,于是乎,先将开发RAC系统作一个switchover先切换至物理备库,然后停开发RAC系统,启动测试RAC系统,修改测试RAC系统的私有IP,这样,改完Private IP之后,两套RAC就都可以正常使用了。绕弯了,呵呵,谁让自己前期没有规划好了,算是给自己一个教训。

           本文就简单记录一下在OEL 5.5 X86_64+11gR2 RAC系统下如何修改私有IP?

          1 修改Private IP前,服务器IP配置信息:

Source IP Configuration
hostname  type    IP Address Domain Name Interface
rac1 public IP 172.16.0.191 rac1.localdomain  eth0
rac1 private IP 192.168.93.1 rac1-priv.localdomain eth1
rac1 virtual IP 172.16.0.193 rac1-vip.localdomain eth0:1
rac2 public IP 172.16.0.192 rac2.localdomain eth0
rac2 private IP 192.168.93.2 rac2-priv.localdomain eth1
rac2 virtual IP 172.16.0.194 rac2-vip.localdomain eth0:1
SCAN SCAN IP 172.16.0.203 rac-scan.localdomain rac-scan

 

              欲将Private IP修改为下述目标配置:

 

Target IP configuration
hostname  type    IP Address Domain Name Interface
rac1 public IP 172.16.0.191 rac1.localdomain  eth0
rac1 private IP 192.168.94.1 rac1-priv.localdomain eth1
rac1 virtual IP 172.16.0.193 rac1-vip.localdomain eth0:1
rac2 public IP 172.16.0.192 rac2.localdomain eth0
rac2 private IP 192.168.94.2 rac2-priv.localdomain eth1
rac2 virtual IP 172.16.0.194 rac2-vip.localdomain eth0:1
SCAN SCAN IP 172.16.0.203 rac-scan.localdomain rac-scan

 

          2 修改Private IP前,节点1服务器/etc/hosts配置文件内容:

[root@rac1 ~]# uname -rm
2.6.18-194.el5xen x86_64
[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1              localhost.localdomain localhost
127.0.0.1               localhost
::1             localhost6.localdomain6 localhost6
#172.16.0.191           rac1.localdomain rac1

172.16.0.191            rac1.localdomain        rac1
172.16.0.193            rac1-vip.localdomain    rac1-vip
192.168.93.1            rac1-priv.localdomain   rac1-priv

172.16.0.192            rac2.localdomain        rac2
172.16.0.194            rac2-vip.localdomain    rac2-vip
192.168.93.2            rac2-priv.localdomain   rac2-priv

172.16.0.203            rac-scan.localdomain    rac-scan
[root@rac1 ~]#

          3 修改Private IP前,服务器RAC集群服务信息:

[root@rac1 ~]# su - grid
rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   ONLINE    ONLINE    rac1        
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac2        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
rac1-> 

          4 用oifcfg开始修改Private IP:

             修改Private IP之前,grid用户用olsnodes确认节点状态

rac1-> olsnodes -n -s   
rac1    1       Active
rac2    2       Active
rac1-> oifcfg getif
eth0  172.16.0.0  global  public
eth1  192.168.93.0  global  cluster_interconnect
rac1-> 

             修改Private IP,grid用户用oifcfg命令修改,修改之后,并删掉之前的那条配置信息

rac1-> oifcfg setif -global eth1/192.168.94.0:cluster_interconnect
rac1-> oifcfg getif
eth0  172.16.0.0  global  public
eth1  192.168.93.0  global  cluster_interconnect
eth1  192.168.94.0  global  cluster_interconnect
rac1-> oifcfg delif -global eth1/192.168.93.0                     
rac1-> oifcfg getif
eth0  172.16.0.0  global  public
eth1  192.168.94.0  global  cluster_interconnect
rac1-> 

             修改Private IP之后,记得确认:

rac1-> oifcfg getif
eth0  172.16.0.0  global  public
eth1  192.168.94.0  global  cluster_interconnect
rac1-> 

          5 关闭数据库、集群服务:

rac1-> srvctl stop database -d devdb

         

          root用户停集群服务:

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac2'
CRS-2673: Attempting to stop 'ora.GRIDDG.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac2'
CRS-2673: Attempting to stop 'ora.DATADG.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.gsd' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.GRIDDG.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac1'
CRS-2673: Attempting to stop 'ora.DATADG.dg' on 'rac1'
CRS-2673: Attempting to stop 'ora.gsd' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac2'
CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'rac2' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.rac1.vip' on 'rac1'
CRS-2677: Stop of 'ora.gsd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.rac1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'rac1' succeeded
CRS-2677: Stop of 'ora.GRIDDG.dg' on 'rac2' succeeded
CRS-2677: Stop of 'ora.GRIDDG.dg' on 'rac1' succeeded
CRS-2677: Stop of 'ora.DATADG.dg' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.DATADG.dg' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'rac1'
CRS-2673: Attempting to stop 'ora.ons' on 'rac1'
CRS-2677: Stop of 'ora.ons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'
CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac2' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac2'
CRS-2673: Attempting to stop 'ora.eons' on 'rac2'
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed
CRS-2677: Stop of 'ora.ons' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'
CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac2'
CRS-2677: Stop of 'ora.diskmon' on 'rac1' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac2' succeeded
[root@rac1 ~]# 

          6 修改双节点/etc/hosts配置文件

[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1              localhost.localdomain localhost
127.0.0.1               localhost
::1             localhost6.localdomain6 localhost6

172.16.0.191            rac1.localdomain        rac1
172.16.0.193            rac1-vip.localdomain    rac1-vip
192.168.94.1            rac1-priv.localdomain   rac1-priv

172.16.0.192            rac2.localdomain        rac2
172.16.0.194            rac2-vip.localdomain    rac2-vip
192.168.94.2            rac2-priv.localdomain   rac2-priv

172.16.0.203            rac-scan.localdomain    rac-scan
[root@rac1 ~]# 

          7 修改双节点Private IP:

             记得一定要修改双节点/etc/sysconfig/network-scripts/ifcfg-eth1配置文件,因为,不修改的话,下次重启服务器后,Private IP又变成了旧的Private IP,因为ifconfig 配置的IP地址只是临时生效,这步骤切记!

[root@rac1 ~]# ifconfig eth1 192.168.94.1 netmask 255.255.255.0

             修改节点1 /etc/sysconfig/network-scripts/ifcfg-eth1:

[root@rac1 ~]# cat /etc/sysconfig/network-scripts/ifcfg-eth1 
# Intel Corporation 82545EM Gigabit Ethernet Controller (Copper)
DEVICE=eth1
BOOTPROTO=none
BROADCAST=192.168.93.255
HWADDR=00:0C:29:E8:15:5E
IPADDR=192.168.93.1
NETMASK=255.255.255.0
NETWORK=192.168.93.0
ONBOOT=yes
TYPE=Ethernet
[root@rac1 ~]# 

             修改节点1 /etc/sysconfig/network-scripts/ifcfg-eth1:

[root@rac1 ~]# cat /etc/sysconfig/network-scripts/ifcfg-eth1 
# Intel Corporation 82545EM Gigabit Ethernet Controller (Copper)
DEVICE=eth1
BOOTPROTO=none
BROADCAST=192.168.94.255
HWADDR=00:0C:29:E8:15:5E
IPADDR=192.168.94.1
NETMASK=255.255.255.0
NETWORK=192.168.94.0
ONBOOT=yes
TYPE=Ethernet
[root@rac1 ~]# 

             节点2如法炮制,不再赘述。

         8 启动集群服务,启动数据库。至此,完成在OEL 5.5 X86_64+11gR2 RAC系统下修改私有IP。

如何处理11gR2 RAC下oc4j和gsd服务为OFFLINE状态?

        在Oracle Enterprise Linux 5.5 X86_64位的机器上配置完11gR2 RAC后,grid用户执行crs_stat -t查看服务状态时,我们会看到有一些服务是OFFLINE状态,其中就包括ora.oc4jora.gsd这两类服务,具体状态,我们看下面的示例输出:

[root@rac1 ~]# uname -rm
2.6.18-194.el5xen x86_64
[root@rac1 ~]# su - grid
rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE               
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
rac1-> 

        那么,是我们的RAC配置有问题吗?这两类服务没有ONLINE会不会影响RAC数据库呢?我们又该如何启动这些服务呢?

        其实,我们的RAC配置是没有问题的,也不会影响数据库正常运行。在默认情况下,其中的ora.gsd服务状态是OFFLINE状态,我们可不予理会,除非需要在11g Cluster上配置Oracle 9iR2的RAC。但凡以grid用户执行crs_stat -t命令,看到Target和State这两列的状态均为OFFLINE,我们均可忽略。但是,我们该如何使之启动呢?

        1 启动ora.oc4j服务:

rac1-> srvctl start oc4j   
OC4J could not be started
PRCR-1079 : Failed to start resource ora.oc4j
CRS-2501: Resource 'ora.oc4j' is disabled
rac1-> srvctl enable oc4j -h

Enable OC4J instance for Oracle Clusterware management.

Usage: srvctl enable oc4j [-n ] [-v]
    -n            Node name
    -v                       Verbose output
    -h                       Print usage
rac1-> srvctl enable oc4j   
rac1-> srvctl start oc4j -v
OC4J has been started
rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
rac1-> 

        2 启动ora.gsd服务:

rac1-> srvctl status nodeapps   
VIP rac1-vip is enabled
VIP rac1-vip is running on node: rac1
VIP rac2-vip is enabled
VIP rac2-vip is running on node: rac2
Network is enabled
Network is running on node: rac1
Network is running on node: rac2
GSD is disabled
GSD is not running on node: rac1
GSD is not running on node: rac2
ONS is enabled
ONS daemon is running on node: rac1
ONS daemon is running on node: rac2
eONS is enabled
eONS daemon is running on node: rac1
eONS daemon is running on node: rac2
rac1-> 

       从上,我们看到GSD服务状态是disabled,接下来enable GSD服务:

rac1-> srvctl enable nodeapps -g
rac1-> srvctl status nodeapps 
VIP rac1-vip is enabled
VIP rac1-vip is running on node: rac1
VIP rac2-vip is enabled
VIP rac2-vip is running on node: rac2
Network is enabled
Network is running on node: rac1
Network is running on node: rac2
GSD is enabled
GSD is not running on node: rac1
GSD is not running on node: rac2
ONS is enabled
ONS daemon is running on node: rac1
ONS daemon is running on node: rac2
eONS is enabled
eONS daemon is running on node: rac1
eONS daemon is running on node: rac2
rac1-> 

       然后,启动GSD服务

rac1-> srvctl start nodeapps -g
PRKO-2002 : Invalid command line option: -g
rac1-> srvctl start nodeapps   
PRKO-2421 : Network resource is already started on node(s): rac1,rac2
PRKO-2420 : VIP is already started on node(s): rac1,rac2
PRKO-2420 : VIP is already started on node(s): rac1,rac2
PRKO-2422 : ONS is already started on node(s): rac1,rac2
PRKO-2423 : eONS is already started on node(s): rac1,rac2

rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   ONLINE    ONLINE    rac1        
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
rac1-> 

          最后,我们看到所有的服务状态都是ONLINE状态!

AIX高手挑战赛,这道题难倒你了吗?

        记得,之前在某论坛在线参加了一次AIX高手挑战赛,成绩不太理想,顺手把其中的某道题以txt文本方式记录下来了,刚才整理机器的时候,看到该题目,就整理在这儿了,那么这道题目也难倒你了吗?

        Oracle10g或者11g的Real Application Cluster(RAC)版本,在AIX操作系统上是使用User Datagram Protocol(UDP)协议作为interprocess通讯的。为了提高Oracle的性能,需要调整UDP参数的设置。需要使用no命令来修改udp_sendspace和udp_recvspace参数。如:将udp_sendspace参数设置成为:[(DB_BLOCK_SIZE*DB_FILE_MULTIBLOCK_READ_COUNT)+4096],并且不小于65536; 将udp_recvspace参数设置为>=4*udp_sendspace; 如果需要,增加sb_max参数(缺省是1048576),因为sb_max必须>=udp_recvspace. 为了确定udp_recvspace是否设置的合适,可以使用如下哪条命令:

A  netstat -s | grep "socket buffer overflows" 

B  lsattr -El udp_recvspace 

C  no -a | grep udp_recvspace 

D  以上都不是 

        答案应该选哪个呢?各位网友,可以回复给出你的答案…..

解决11gR2 RAC 客户端ORA-12545错误一则

在一套Linux环境下X86_64的11gR2 RAC数据库集群中,客户端通过SCAN IP访问数据库时遇到下述错误:

C:\Users\huangw.GILLION>sqlplus hr/hr@11grac

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 13 10:54:55 2012

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

其中,客户端的tnsnames.ora文件配置如下,其中172.16.0.203为SCAN IP,并且客户端ping SCAN IP一切正常 :

11grac =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST=172.16.0.203)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

ping SCAN IP:

C:\Users\huangw.GILLION>ping 172.16.0.203

Pinging 172.16.0.203 with 32 bytes of data:
Reply from 172.16.0.203: bytes=32 time=1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64
Reply from 172.16.0.203: bytes=32 time<1ms TTL=64

Ping statistics for 172.16.0.203:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms

服务器端网络配置信息如下:

[root@rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1              localhost.localdomain localhost
127.0.0.1               localhost
::1             localhost6.localdomain6 localhost6

172.16.0.191            rac1.localdomain        rac1
172.16.0.193            rac1-vip.localdomain    rac1-vip
192.168.93.1            rac1-priv.localdomain   rac1-priv

172.16.0.192            rac2.localdomain        rac2
172.16.0.194            rac2-vip.localdomain    rac2-vip
192.168.93.2            rac2-priv.localdomain   rac2-priv

172.16.0.203            rac-scan.localdomain    rac-scan
[root@rac1 ~]# 

服务器端SCAN IP信息如下:

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl config scan
SCAN name: 172.16.0.203, Network: 1/172.16.0.0/255.255.240.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.localdomain/172.16.0.203
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
[root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac2
[root@rac1 ~]# 

服务器端集群信息如下:

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATADG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.GRIDDG.dg  ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.devdb.db   ora....se.type ONLINE    ONLINE    rac1        
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE               
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora....ry.acfs ora....fs.type ONLINE    ONLINE    rac1        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac2        
[root@rac1 ~]# 

通过查找MetaLink:ORA-12545 or ORA-12537 While Connecting to RAC through SCAN name [ID 970619.1]得到解决方法,修改数据库的local_listener参数:

修改之前:

[root@rac1 ~]# su - oracle
rac1-> sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 13 10:42:58 2012

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


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

SQL> set line 160
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/devdb/spfiledevdb.ora
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      devdb1
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=rac1
                                                 -vip)(PORT=1521))))
remote_listener                      string      rac-scan:1521
SQL> 

修改local_listener参数:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.193)(PORT=1521))))' sid='devdb1';

System altered.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.194)(PORT=1521))))' sid='devdb2';

System altered.

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=172.
                                                 16.0.193)(PORT=1521))))
remote_listener                      string      rac-scan:1521
SQL> 

修改之后,客户端连接正常:

C:\Users\huangw.GILLION>sqlplus hr/hr@11grac

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 13 11:04:00 2012

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


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

SQL> show user;
USER is "HR"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

8 rows selected.

SQL>

至此,问题解决。

产生问题原因:

Client is able to resolve all Fully Qualified Domain Name (FQDN) SCAN and VIP name but not short ones(without domain name) as its in different domain; even though FQDN names were specified during Grid Infrastructure setup, due to bug 9150053 by default DBCA set database parameter local_listener to short node VIP name while database is created.

解决方法除了上述之外,我们也可以按照下面的方法解决,其实,都是换汤不换药,个人倾向于直接在监听里配置IP地址:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.localdomain)(PORT=1521))))' sid='devdb1';

System altered.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip.localdomain)(PORT=1521))))' sid='devdb2';

System altered.

SQL> alter system register;

System altered.

SQL>