dbconsole can’t be started with ssl error

Got problem like failing to start dbconsole, check the trace file got this:
emdctl.trc
———–
2008-09-15 10:58:20 Thread-4136126688 ERROR http: 8: Unable to initialize ssl connection with
server, aborting connection attempt
2008-09-15 10:59:52 Thread-4136126688 ERROR ssl: nzos_Handshake failed, ret=29024.

After searching the metalink, found just need to unsecure and resecure the dbconsole to renew the expired dbconsole certificate:

1. Unsecure the Dbconsole
- Unsecure database control using
$ORACLE_HOME/bin>emctl unsecure dbconsole

2. Force an upload:

$ORACLE_HOME/bin> emctl upload

3. Also consider Resecuring the Dbconsole
- Secure database control using
$ORACLE_HOME/bin>emctl secure dbconsole

Starting with 10.2.0.4, HTTPS is used by default.

two ways to call Oracle Stored procedure from VB.net

PROCEDURE TEST_C(temp out varchar2,a IN varchar2, b in varchar2)
IS
BEGIN
temp:=a || b;
END;

Solution 1:
add “Imports System.Data.OleDb” at the code beginning

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand

dbConn.ConnectionString = “Provider=MSDAORA;User ID=xxx;Password=xxx;Data Source=xxx;”
dbConn.Open()
dbComm = dbConn.CreateCommand

dbComm.Parameters.Add(“temp”, OleDbType.VarChar, 30).Direction = ParameterDirection.Output
dbComm.Parameters.Add(“a”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“a”).Value = “test “
dbComm.Parameters.Add(“b”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“b”).Value = “OK”

dbComm.CommandText = “TEST_C”
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()

MessageBox.Show(dbComm.Parameters(“temp”).Value)

Solution 2:
add “Imports System.Data.OracleClient” at the code beginning

Dim oraConn As New OracleConnection
Dim oraComm As New OracleCommand

oraConn.ConnectionString = “Data Source=xxx;User Id=xxx;Password=xxx”
oraComm.Connection = oraConn

oraComm.Parameters.Add(“temp”, OracleType.VarChar, 10).Direction = ParameterDirection.Output
oraComm.Parameters.Add(“a”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“a”).Value = “test “
oraComm.Parameters.Add(“b”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“b”).Value = “OK”

oraConn.Open()
oraComm.CommandText = “TEST_C”
oraComm.CommandType = CommandType.StoredProcedure
oraComm.ExecuteNonQuery()
oraConn.Close()

MessageBox.Show(oraComm.Parameters(“temp”).Value)

Note: the first parameter name has to be the same as the oracle stored procedure parameter name;
if there is dblink in the stored procedure, then the solution 1 is the only choice.

if you are trying to call oracle stored procedure by passing clob/lob as parameters, then don’t use odbc solution because it has 32k limitation. Oracle have this metalink note talk about this:
From metalink: Subject-32k Limitation When Passing LOB Parameter Through Stored Procedure Doc ID: 252102.1
https://metalink2.oracle.com/metalink/plsql/f?p=130:14:6916025231277951933::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,126125.1,1,0,1,helvetica

Workaround
~~~~~~~~~~

Workaround 1:

Use the Oracle Provider for OLEDB instead, making sure to set the command
property SPPrmsLOB to TRUE:

objCmd.Properties(“SPPrmsLOB”) = TRUE

Workaround 2:

Instead of passing a CLOB as a parameter to a stored procedure, use a method
that directly interfaces with the database and does not require the use of
a stored procedure to update the CLOBs as in the following example:

Note 126125.1 – ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388).

11g rac could not be started

Today after reboot the rac nodes servers, the RAC 11g couldn’t be started.
Here is the errors and solutions:

Errors:
1.[root@vanpgrepdb03 racg]# crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

[root@vanpgrepdb03 racg]# crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon
Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manager

[root@vanpgrepdb03 racg]# ps -ef|grep -i init.d
root 3895 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.evmd run
root 3896 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.cssd fatal
root 3897 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.crsd run
root 3961 3895 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 4031 3896 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 4123 3897 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 5230 24639 0 12:58 pts/0 00:00:00 grep -i init.d

–check the system log
[root@vanpgrepdb03 racg]# tail -f /var/log/messages
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: create_udp_client: hostname lookup failed: Operation not permitted
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: create_tcp_client: hostname lookup failed: Operation not permitted
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: lookup_mount: exports lookup failed for d
Jun 26 13:15:49 vanpgrepdb03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.4031.
Jun 26 13:15:49 vanpgrepdb03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.4031.

–check the trace file
[root@vanpgrepdb03 racg]# cat /tmp/crsctl.4031
Oracle Cluster Registry initialization failed accessing Oracle Cluster Registry device: PROC-26: Error while accessing the physical storage Operating System error [Permission denied] [13]

–verify the raw file to see if they are binded
[root@vanpgrepdb03 ~]# raw -qa
/dev/raw/raw1: bound to major 8, minor 1
/dev/raw/raw2: bound to major 8, minor 2

–check the permission since the log was mentioning that
[root@vanpgrepdb03 ~]# cd /dev/raw
[root@vanpgrepdb03 raw]# ls -al
total 0
drwxr-xr-x 2 root root 80 Jun 21 07:08 .
drwxr-xr-x 14 root root 3760 Jun 24 08:17 ..
crw——- 1 root root 162, 1 Jun 21 07:08 raw1
crw——- 1 root root 162, 2 Jun 21 07:08 raw2
–looks like the permission is not correct

–change permissions (on both nodes)
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/raw/raw1
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/raw/raw2
[root@vanpgrepdb03 raw]# chmod 660 /dev/raw/raw1
[root@vanpgrepdb03 raw]# chmod 660 /dev/raw/raw2
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/sda1
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/sda2
[root@vanpgrepdb03 raw]# chmod 660 /dev/sda1
[root@vanpgrepdb03 raw]# chmod 660 /dev/sda2

–after that, chheck the init.cssd, it’s up!
[root@vanpgrepdb03 raw]# ps -ef|grep init.d
root 3895 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.evmd run
root 3896 1 0 Jun21 ? 00:00:03 /bin/sh /etc/init.d/init.cssd fatal
root 3897 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.crsd run
root 7588 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd oprocd
root 7606 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd oclsomon
root 7630 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd daemon
root 20251 6701 0 14:15 pts/0 00:00:00 grep init.d

–check the crs service is also working now
[root@vanpgrepdb03 db]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy

–bring up the rac resources again by using srvctl

Reference:
“why my oracle cluster could not start” http://surachartopun.com/2009/04/why-my-oracle-cluster-could-not-start.html

Oracle connection idle timeout with firewall

Since our oracle 10g RAC has been moved behind firewall, we always get disconnected/timeout by firewall if the connection was idle.
By searching the metalink i found this article is really useful:

Resolving Problems with Connection Idle Timeout With Firewall

An Overview

Firewall(FW) has become common in today’s networking to protect the network environment. The firewall recognizes the TCP protocol and it records the client server socket end-points. Also, FW recognize the TCP connection closure, and then will release the resources allocated for recording the opening connection. For every end-point pairs , the firewall must also allocate some resources(may be small).

When the client or server closes the communication it sends TCP FIN type packet, this is a normal socket closure. However, it is not uncommon that the client server communication abruptly ending without closing the end points properly by sending FIN packet, for example, when the client or server crashed, power down or a network error which prevents sending the closure packet to the other end. In that cases, the firewall will not know that the end-points will no longer use the opened channel. As a passive intermediary, it had no way to determine if the endpoints are still active. As is it not possible to maintain resources forever, and also, it is a security threat keeping a port open for undefined time. So, firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time.

Initially FW were designed to protect the application servers, network and then to protect client/server connection. With these in mind, a time-out in terms of hours (1 hour is the default for most FW) is reasonable. With the advent of more complex security schemes, FW are not only between client and server, but also between different application servers ( intranet, demilitarized zone (DMZ) , and such) and database servers. So, the horizon of 1 hour idle time for communication between servers maybe not be appropriate.

Idle connections can be expected from an application server. There is the case of J2EE using pooled JDBC connections. The pool usually returns the first available connection to the requester, so the first connections of the pool list are the most likely to be active. The last one, which are at the end of the list, are only used at peek loads, and most of the time it will be inactive.

Other cases are the connections established from a HTTP Server, either SQL connections from mod_plsql, or AJP connections from mod_oc4j.

Blackout

One of the inconvenience of theses blackout, is that they are passive. None of the endpoints will be notified that the communication was banned . Only when the client or server tries to contact its peer, it comes to know that the peer end is no more active and the communication has already been broken.

The worst of all scenarios are the so called “passive listeners” . They will never know. Because, passive listeners are those processes at an endpoint that are simply waiting for commands to arrive from the other end. A typical example of this are the backend database server processes, which are reading from the socket looking new SQL statements to execute , and after the request is answered, they return to their passive state. When a blackout occurs, they will stay forever in this reading state, unless some of the following techniques are applied.

Resolving problems with connection idle time-out

TCP KeepAlive

You can enable TCP KeepAlive option at the Operating System(OS) level. Once TCP keepalive option is enabled and configured, a small probe packet will be sent to the other end at every predefined in-activity interval and it expects an ACK from the other end. And ACK will be returned only when the other end is alive and is reachable. If ACK is not returned, then after some retry, the OS will close the end points and will release the resources allocated for that. The application which is listening on that particular socket will recieve the error, so that application can take necessary action upon receiving the error signal from the OS.

When a communication is blacked out by the firewall, the probe will not reach its other end, and then the OS will close the socket end points and the application will be notified of the exception.

Steps to configure TCP KeepAlive depends on a specific Operating Systems. You will have to refer the appropriate OS documentation for it.

It is common to enable TCP KeepAlive option at the server end. Because server is the one which holds many resources for a communication, it any communication is broken, then those resources at the server will be released than holding it for indefinite time. By default TCP KeepAlive is not enabled at the OS.

TCP KeepAlive is applicable for all network applications running on that particular Operating System.

DCD for DataBase Servers

For database connections, one of the endpoints is a passive listener, either is a dedicated process or a dispatcher process. If the connection becomes blacked out , this backend will never know that client cannot send any more requests, and then will lock important resources as database sessions, locks , and at least , a file descriptor used for maintaining the socket.

A solution is to make this backend “not so” passive, using the DCD (dead connection detection) to figure out if the communication is still possible.

Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10 (10 minutes, for example). With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released.

There are two benefits with this DCD
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall will consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive.

2. If the SQLNET.EXPIRE_TIME (let’s say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection.

The first case is recommended when the connection comes from another application server , and the second makes sense for client applications.

DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10 then do not expect that the connections will be closed exactly after 10 minutes of the blackout or network outage. The TCP timeout and TCP retransmission values also adds to this time.
Please note that some latest firewalls may not see DCD packets as a valid traffic, and thus the DCD may not be useful. In this case, firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall.

AJP Connections

It is not a default behavior in 9.0.2, but if Patch 2862660 is installed, the connection between and OHS server process and the J2EE can be maintained for more than a single request. If the parameter Oc4jConnTimeout is set, the OHS will maintain the connection for at least that time. The problem is that the child process may became inactive before that time-out occurs, and then the connection will remain open. While the child process is inactive, the connection will be idle, and there is chance to be blackout by the FW.

If this happens, the first thing that the child will do is to close it when it becomes active. But at this time, the TCP socket closing cannot be completed, due the blackout. Although the http child process can simply ignore the closing failure and continue the creation of a new connection, the passive listener at the
j2ee side (the worker thread) will be hook without a chance for the resources to be released.

To solve this , the Patch 3151686 must be installed and the java-option
-Dajp.keepalive=true
must be enabled.

After this, the blackout detection will rely on the TCP KeepAlive provided by the operating system.

As DCD , this process consist in send probes -empty packages- when a socket had been inactive for a period of time. If there is no response, the socket will be closed, and then, even the passive listener, will receive and exception or signal to let him know that the no further communication is possible.

CONCLUSION

As the firewalls extend their functionality , and are now are placed in between application servers, some tuning and parameter adjusting must be made to overcome the default rules established for client/server communications.

Note id: 257650.1
Link: https://metalink2.oracle.com/metalink/plsql/f?p=130:14:9497610239050450074::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,257650.1,1,1,1,helvetica

export name case-sensitive tables in oracle

exp xtreme file=xtreme_tables.dmp tables=(‘\”Product\”‘,’\”Supplier\”‘,CUSTOMER,ORDERS)

keep data consistency when using oracle exp/expdp

When we were using old oracle exp, we usually will set exp consistent=y (default is n) to ensure the data consistency (the image taken of the data in the tables being exported represents the committed state of the table data at the same single point-in-time for all of the tables being exported.)

However, started from 10g they decomission this parameter. Today i happened to have a requirement for this and i searched meta link and found this useful piece:

The versions 10gR1 and 10gR2 additionally put the message in the expdp header:

FLASHBACK automatically enabled to preserve database integrity

Does this guarantee export consistency to a single point of time?

Cause
The message:

FLASHBACK automatically enabled to preserve database integrity

only means that some of the tables will be assigned special SCNs (needed for Streams and Logical Standby). There is no consistency guaranteed between exported tables.

The next example demonstrates this:

1. Create the environment

connect / as sysdba

create or replace directory flash as ‘/tmp’;
grant read, write on directory flash to system;

drop user usr001 cascade;
purge dba_recyclebin;

create user usr001 identified by usr001 default tablespace users temporary tablespace temp;
grant connect, resource to usr001;

connect usr001/usr001
create table part001
(
col001 number,
col002 date,
col003 varchar2(1000)
)
partition by range (col001)
(
partition p001 values less than (500001),
partition p002 values less than (1000001)
);

2. Populate the partitioned table: partition P001 contains 500000 rows and partition P002 contains 10 rows

connect usr001/usr001
begin
for i in 1..500010 loop
insert into part001 values (i, sysdate, lpad (to_char(i), 1000, ‘0′));
end loop;
commit;
end;

3. Start expd

#> expdp system/passwd directory=flsh dumpfile=user001_1.dmp logfile =user001_1.log schemas=usr001

4. During point 3. is running, run in a separate session:

connect usr001/usr001
delete from part001 where col001 in (500001, 500002, 500003, 500004, 500005);
commit;

This will delete 5 rows in partition P002.

5. Expdp completes with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 13:59:59

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_1.dmp logfile=exp_usr001_1.log schemas=usr001
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001″.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001″.”PART001″:”P002″ 10.50 KB 5 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_1.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:02:47

=> From partition P002 only 5 rows were exported, so the written export dump is not consistent.
Solution
To generate consistent Data Pump’s database or schema export similar to exports generated with exp parameter CONSISTENT=Y, use Data Pump parameters FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

Conform with the example above, running expdp with:

#> expdp system/passwd directory=flsh dumpfile=user001_2.dmp logfile =user001_2.log schemas=usr001 flashback_time=\”TO_TIMESTAMP \(TO_CHAR \(SYSDATE, \’YYYY-MM-DD HH24:MI:SS\’\), \’YYYY-MM-DD HH24:MI:SS\’\)\”

This will end with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 14:15:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_2.dmp logfile=exp_usr001_2.log schemas=usr001 flashback_time=”to_timestamp (to_char (sysdate, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001″.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001″.”PART001″:”P002″ 15.48 KB 10 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_2.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:16:55

=> Partition P002 contains all 10 rows, though 5 rows were deleted during expdp time. The parameter FLASHBACK_TIME guarantees the consistency.

Link:  “Doc ID: 377218.1″

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4374876471460387797::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,377218.1,1,0,1,helvetica

Oracle XML practise

A very good Oracle XML practise from this link: http://www.itpub.net/viewthread.php?tid=899445&highlight=xml

quoted here for future reference.

1、判断一下数据库中xml功能是否已经安装成功

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on 星期一 11月 26 10:35:23 2007

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

idle>conn sys/sys@doscdb as sysdba
已连接。

DOSCDB(sys)>select * from v$version;

BANNER
———————————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

DOSCDB(sys)>select username from dba_users;

USERNAME
———————————————————–
DIP
TSMSYS
DBSNMP
SYSMAN
MDSYS
ORDSYS
CTXSYS
ANONYMOUS
EXFSYS
DMSYS
WMSYS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
MGMT_VIEW
SYS
SYSTEM
OUTLN
UOA
UBASE
UWF
UOCEAN
UBI
UTEMP

已选择24行。

–存在XDB用户

DOSCDB(sys)>DESCRIBE RESOURCE_VIEW;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
RES                                                            XMLTYPE(XMLSchema “http://xmlns.orac
le.com/xdb/XDBResource.xsd” Element
“Resource”)
ANY_PATH                                                       VARCHAR2(4000)
RESID                                                          RAW(16)

–从RESOURCE_VIEW 信息中可以判断XDB功能已经安装完成

2、准备xml_test.xml文件，并放在e:\testxml目录下

–xml_test.xml文件内容

<xwork>
<include file=”webwork-default.xml”/>
<package name=”default” extends=”webwork-default”>
<Refpeng>PENG XML_TEST </Refpeng>
<result-types>
<result-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/>
<result-type2 name=”excelReport” class=”com.hisoft.web.view.ExcelReportResult”/>
</result-types>

<interceptors>
<interceptor name=”auth” class=”com.hisoft.web.interceptor.RoleInterceptor” />
<interceptor name=”XData” class=”com.hisoft.web.interceptor.XDataInterceptor” />
<interceptor-stack name=”roleStack”>
<interceptor-ref name=”defaultStack”/>
<interceptor-ref name=”auth”/>
</interceptor-stack>
</interceptors>

<!– global results –>
<global-results>
<result name=”error” type=”freemarker”>/WEB-INF/pages/globalError.ftl</result>
<result name=”redirect” type=”freemarker”>/WEB-INF/pages/redirect.ftl</result>
<result name=”login” type=”freemarker”>/WEB-INF/pages/login.ftl</result>
</global-results>

<action name=”login” class=”com.hisoft.web.action.LoginAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/globalredirect.ftl</result>
<result name=”input” type=”freemarker”>/WEB-INF/pages/login.ftl</result>
<interceptor-ref name=”validationWorkflowStack” />
</action>

<action name=”index” class=”com.opensymphony.xwork.ActionSupport”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/index.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”left” class=”com.hisoft.web.action.LeftMenuAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”top” class=”com.hisoft.web.action.TopAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/top.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”main”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”>
<result name=”success” type=”excel”>
<param name=”filename”>product.</param>
</result>
</action>

<action name=”exportExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”>
<result name=”success” type=”excelReport”>
<param name=”filename”>product</param>
<param name=”template”>/WEB-INF/pages/product.xls</param>
</result>
</action>
</package>

</xwork>

3、创建xml表，用来存储xml 数据

DOSCDB(sys)>create table xml_test of xmltype;

表已创建。

DOSCDB(sys)>desc xml_test;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
TABLE of XMLTYPE

DOSCDB(sys)>CREATE DIRECTORY xmldir AS ‘e:\testxml\’;

目录已创建。

DOSCDB(sys)>INSERT INTO xml_test VALUES (XMLType(bfilename(‘XMLDIR’, ‘testxml.xml’),
2   nls_charset_id(‘AL32UTF8′)));

已创建 1 行。

DOSCDB(sys)>commit;

提交完成。

4、如何使用呢，既然存储进去了，当然是为了更好的使用了

DOSCDB(sys)>desc user_xml_tables;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
TABLE_NAME                                                     VARCHAR2(30)
XMLSCHEMA                                                      VARCHAR2(700)
SCHEMA_OWNER                                                   VARCHAR2(30)
ELEMENT_NAME                                                   VARCHAR2(2000)
STORAGE_TYPE                                                   VARCHAR2(17)

DOSCDB(sys)>select table_name from user_xml_tables;

TABLE_NAME
————————————————————
XML_TEST

–取得所有文档的内容

DOSCDB(sys)>SELECT OBJECT_VALUE from xml_test;

OBJECT_VALUE
—————————————————————————————————-
<xwork>
<include file=”webwork-default.xml”/>
<package name=”default” extends=”webwork-default”>
<Refpeng>PENG XML_TEST </Refpeng>
<result-types>
<result-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/>
<result-type2 name=”excelReport” class=”com.hisoft.web.view.ExcelReportResult”/>
</result-types>
<interceptors>
<interceptor name=”auth” class=”com.hisoft.web.interceptor.RoleInterceptor”/>
<interceptor name=”XData” class=”com.hisoft.web.interceptor.XDataInterceptor”/>
<interceptor-stack name=”roleStack”>
<interceptor-ref name=”defaultStack”/>

<action name=”left” class=”com.hisoft.web.action.LeftMenuAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”top” class=”com.hisoft.web.action.TopAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/top.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”main”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”>
<result name=”success” type=”excel”>
<param name=”filename”>product.</param>
</result>
</action>
<action name=”exportExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”>
<result name=”success” type=”excelReport”>
<param name=”filename”>product</param>
<param name=”template”>/WEB-INF/pages/product.xls</param>
</result>
</action>
</package>
</xwork>

–取得<xwork>内的内容

DOSCDB(sys)>SET LONG 10000
DOSCDB(sys)>SET PAGESIZE 100
DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork’)
2    FROM xml_test;

DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork/package’)
2  from xml_test;

EXTRACT(OBJECT_VALUE,’/XWORK/PACKAGE’)
—————————————————————————————————-
<package name=”default” extends=”webwork-default”><Refpeng>PENG XML_TEST </Refpeng><result-types><re
sult-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/><result-type2 name=”excelReport” cl
ass=”com.hisoft.web.view.ExcelReportResult”/></result-types><interceptors><interceptor name=”auth” c
lass=”com.hisoft.web.interceptor.RoleInterceptor”/><interceptor name=”XData” class=”com.hisoft.web.i
/pages/login.ftl</result><interceptor-ref name=”valida
tionWorkflowStack”/></action><action name=”index” class=”com.opensymphony.xwork.ActionSupport”><resu
lt name=”success” type=”freemarker”>/WEB-INF/pages/index.ftl</result><interceptor-ref name=”roleStac
k”/></action><action name=”left” class=”com.hisoft.web.action.LeftMenuAction”><result name=”success”
type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result><interceptor-ref name=”roleStack”/></action><
action name=”top” class=”com.hisoft.web.action.TopAction”><result name=”success” type=”freemarker”>/
WEB-INF/pages/top.ftl</result><interceptor-ref name=”roleStack”/></action><action name=”main”><resul
t name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result><interceptor-ref name=”roleSta
ck”/></action><action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”><result nam
e=”success” type=”excel”><param name=”filename”>product.</param></result></action><action name=”expo
rtExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”><result name=”success” type=”exce
lReport”><param name=”filename”>product</param><param name=”template”>/WEB-INF/pages/product.xls</pa
ram></result></action></package>

5、牛刀小试
–简单访问

DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork/package/Refpeng’)
2  from xml_test;

EXTRACT(OBJECT_VALUE,’/XWORK/PACKAGE/REFPENG’)
—————————————————————————————————-
<Refpeng>PENG XML_TEST </Refpeng>

–访问一下属性值看看

DOSCDB(sys)>SELECT extractvalue(OBJECT_VALUE, ‘/xwork/package/Refpeng’)
2  from xml_test;

EXTRACTVALUE(OBJECT_VALUE,’/XWORK/PACKAGE/REFPENG’)
—————————————————————————————————-
PENG XML_TEST

–小试 where

DOSCDB(sys)>SELECT extractValue(OBJECT_VALUE, ‘/xwork/package/Refpeng’) “Peng Test”
2  FROM xml_test
3  WHERE existsNode(OBJECT_VALUE,
4                     ’/xwork/package[Refpeng="PENG XML_TEST "]‘) = 1;

Peng Test
———————————————————————————————
PENG XML_TEST

–小试update

DOSCDB(sys)>UPDATE xml_test
2    SET OBJECT_VALUE =
3          updateXML(OBJECT_VALUE,
4                    ’/xwork/package/Refpeng/text()’,
5                    ’PENG XML_UPDATE_TEST’
6                    )
7    WHERE existsNode(OBJECT_VALUE,
8                       ’/xwork/package[Refpeng="PENG XML_TEST "]‘) = 1;

已更新 1 行。

DOSCDB(sys)>COMMIT;

提交完成。

DOSCDB(sys)>SELECT extractValue(OBJECT_VALUE, ‘/xwork/package/Refpeng’) “Peng Test”
2  FROM xml_test
3  /

Peng Test
—————————————————————————————————-
PENG XML_UPDATE_TEST

dbms_stats has error when gather statistics on case sensitive objects

i was trying to use dbms_stats gather the statistics for a table named: “XTREME_EN”.”Financials”, but got this error:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘Financials’);
BEGIN dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname => ‘Financials’); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE “XTREME_EN”.”FINANCIALS”, insufficient privil
eges or does not
exist
ORA-06512: at “SYS.DBMS_STATS”, line 13427
ORA-06512: at “SYS.DBMS_STATS”, line 13457
ORA-06512: at line 1

However, if i use traditional analyze command, it works:

sys@FUN10U09> ANALYZE TABLE “XTREME_EN”.”Financials” COMPUTE STATISTICS;

Table analyzed.

Is that because dbms_stats stupider than analyze? Of course not, Oracle is always encouraging you to use “dbms_stats” instead of using “analyze”.

After search on the metalink, i found this doc: “DBMS_STATS Reports ORA-20000 and ORA-06512 On Case Sensitive Object Names”  Doc ID: 343355.1

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4774819970862237887::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,343355.1,1,1,1,helvetica

According to this doc, we have to mention the object name in double quotes.This is very similar to how SQL supports mixed cases.

For our case:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘”Financials”‘);

PL/SQL procedure successfully completed.

Works perfectly!

Detailed steps for removing a node from 10gR2 3-nodes RAC

Removing Node from Oracle 10gR2 RAC

Background:

we have to remove a node from Oracle 10gR2 RAC.

OS: Redhat EL4

Node name:

vanpgprodb01

vanpgrprodb02

vanpgprodb05

Storage: ASM (instance name +ASM)

The most important 3 steps that need to be followed are;

A.         Remove the instance using DBCA.

B.         Remove the node from the cluster.

C.         Reconfigure the OS and remaining hardware.

Here is a breakdown of the above steps.

A. Remove the instance using DBCA.

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

1. Verify that you have a good backup of the OCR (Oracle Configuration  Repository) using ocrconfig -showbackup or dd command.

Run the backup manually with dd for OCR and Voting disk:

[oracle@vanpgprodb01 ocr_voting]$ pwd

/databases/oracle/backup/ocr_voting

[oracle@vanpgprodb01 ocr_voting]$ dd if=/dev/raw/raw1 of=before_del_3rdnode.ocr

[oracle@vanpgprodb01 ocr_voting]$ dd if=/dev/raw/raw2 of=before_del_3rdnode.vote

2. Run DBCA from one of the nodes you are going to keep (vanpgprodb01).  Leave the database up and also leave the departing instance up and running.

3. Choose “Instance Management”

4. Choose “Delete an instance”

5. On the next screen, select the cluster database (p10c) from which you will delete an instance.  Supply the system privilege username and password.

6. On the next screen, a list of cluster database instances will appear.  Highlight the instance you would like to delete then click next.

7.         If you have services configured, reassign the services.  Modify the

services so that each service can run on one of the remaining

instances.  Set “not used” for each service regarding the instance

that is to be deleted.  Click Finish.

8.         If your database is in archive log mode you may encounter the

following errors (10gR2 doesn’t have this issue):

ORA-350

ORA-312

This may occur because the DBCA cannot drop the current log, as

it needs archiving.  This issue is fixed in the 10.1.0.3

patchset. But previous to this patchset you should click the

ignore button and when the DBCA completes, manually archive

the logs for the deleted instance and dropt the log group.

SQL>  alter system archive log all;

SQL>  alter database drop logfile group 2;

9.         Verify that the dropped instance’s redo thread has been removed by

querying v$log.  If for any reason the redo thread is not disabled

then disable the thread.

SQL> alter database disable thread 2;

10. Verify that the instance was removed from the OCR (Oracle Configuration Repository) with the following commands:

srvctl config database -d <db_name>

[oracle@vanpgprodb01 dbca]$ srvctl config database -d p10c

vanpgprodb01 p10c1 /databases/oracle/db

vanpgprodb02 p10c2 /databases/oracle/db

cd <CRS_HOME>/bin/crs_stat

11. If this node had an ASM instance and the node will no longer be a part of the cluster you will now need to remove the ASM instance with:

srvctl stop asm -n <nodename>

[oracle@vanpgprodb01 dbca]$ srvctl stop asm -n vanpgprodb05

srvctl remove asm -n <nodename>

[oracle@vanpgprodb01 dbca]$ srvctl remove asm -n vanpgprodb05

Verify that asm is removed with:

srvctl config asm -n <nodename>

[oracle@vanpgprodb01 dbca]$ srvctl config asm -n vanpgprodb05  –the output is nothing instead of show you the ASM instance name and asm home

Delete the ASM folders on deleting node:

rm -r $ORACLE_BASE/admin/+ASM
[root@vanpgprodb05 admin]# rm -rf +ASM

rm -f $ORACLE_HOME/dbs/*ASM*

[root@vanpgprodb05 dbs]# rm -rf *ASM*

Remove the ASM library on node3:

[root@vanpgprodb05 db]# /etc/init.d/oracleasm stop

Unmounting ASMlib driver filesystem: [  OK  ]

Unloading module “oracleasm”: [  OK  ]

[root@vanpgprodb05 db]# rpm -qa | grep oracleasm

oracleasmlib-2.0.2-1

oracleasm-2.6.9-55.ELsmp-2.0.3-1

oracleasm-support-2.0.3-1

[root@vanpgprodb05 db]# rpm -ev oracleasm-support-2.0.3-1 oracleasm-2.6.9-55.ELsmp-2.0.3-1 oracleasmlib-2.0.2-1

warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave

[root@vanpgprodb05 db]# rm -f /etc/sysconfig/oracleasm.rpmsave

[root@vanpgprodb05 db]# rm -f /etc/rc.d/init.d/oracleasm

[root@vanpgprodb05 db]# rm -f /etc/rc0.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc1.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc2.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc3.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc4.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc5.d/*oracleasm*

[root@vanpgprodb05 db]# rm -f /etc/rc6.d/*oracleasm*

B.         Remove the Node from the Cluster

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

Once the instance has been deleted, the process of removing the node from the cluster is a manual process. This is accomplished by running scripts on the deleted node to remove the CRS install, as well as scripts on the remaining nodes to update the node list.  The following steps assume that the node to be removed is still functioning.

1. Remove the listener and nodeapp on deleting node:

1). To delete node first stop and remove the nodeapps on the node you are removing.  Assuming that you have removed the ASM instance as the root user on a remaining node;

# srvctl stop nodeapps -n <nodename>

[oracle@vanpgprodb01 dbs]$ srvctl stop nodeapps -n vanpgprodb05

[oracle@vanpgprodb01 dbs]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    vanp…db02

ora….c1.inst application    ONLINE    ONLINE    vanp…db01

ora….c2.inst application    ONLINE    ONLINE    vanp…db02

ora….SM1.asm application    ONLINE    ONLINE    vanp…db01

ora….01.lsnr application    ONLINE    ONLINE    vanp…db01

ora….b01.gsd application    ONLINE    ONLINE    vanp…db01

ora….b01.ons application    ONLINE    ONLINE    vanp…db01

ora….b01.vip application    ONLINE    ONLINE    vanp…db01

ora….SM2.asm application    ONLINE    ONLINE    vanp…db02

ora….02.lsnr application    ONLINE    ONLINE    vanp…db02

ora….b02.gsd application    ONLINE    ONLINE    vanp…db02

ora….b02.ons application    ONLINE    ONLINE    vanp…db02

ora….b02.vip application    ONLINE    ONLINE    vanp…db02

ora….05.lsnr application    OFFLINE   OFFLINE

ora….b05.gsd application    OFFLINE   OFFLINE

ora….b05.ons application    OFFLINE   OFFLINE

ora….b05.vip application    OFFLINE   OFFLINE

2). Run netca.  Choose “Cluster Configuration”.

3). Only select the node you are removing and click next.

4). Choose “Listener Configuration” and click next.

5). To delete the listener: Choose “Delete” and delete any listeners configured on the node you are removing.

6).  Run <CRS_HOME>/bin/crs_stat.  Make sure that all database resources are running on nodes that are going to be kept.  For example:

NAME=ora.<db_name>.db

TYPE=application

TARGET=ONLINE

STATE=ONLINE on <node2>

[oracle@vanpgprodb05 db]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    vanp…db02

ora….c1.inst application    ONLINE    ONLINE    vanp…db01

ora….c2.inst application    ONLINE    ONLINE    vanp…db02

ora….SM1.asm application    ONLINE    ONLINE    vanp…db01

ora….01.lsnr application    ONLINE    ONLINE    vanp…db01

ora….b01.gsd application    ONLINE    ONLINE    vanp…db01

ora….b01.ons application    ONLINE    ONLINE    vanp…db01

ora….b01.vip application    ONLINE    ONLINE    vanp…db01

ora….SM2.asm application    ONLINE    ONLINE    vanp…db02

ora….02.lsnr application    ONLINE    ONLINE    vanp…db02

ora….b02.gsd application    ONLINE    ONLINE    vanp…db02

ora….b02.ons application    ONLINE    ONLINE    vanp…db02

ora….b02.vip application    ONLINE    ONLINE    vanp…db02

ora….b05.gsd application    OFFLINE   OFFLINE

ora….b05.ons application    OFFLINE   OFFLINE

ora….b05.vip application    OFFLINE   OFFLINE

Ensure that this resource is not running on a node that will be removed (this step hasn’t been done in our case since crs stats shows everything correct).  Use <CRS_HOME>/bin/crs_relocate to perform this.

Example:  crs_relocate ora.<db_name>.db

7).  As the root user, remove the nodeapps on the node you are removing.

# srvctl remove nodeapps -n <nodename>

[root@vanpgprodb05 db]# srvctl remove nodeapps -n vanpgprodb05

Please confirm that you intend to remove the node-level applications on node vanpgprodb05 (y/[n]) y

[root@vanpgprodb05 db]# crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    vanp…db02

ora….c1.inst application    ONLINE    ONLINE    vanp…db01

ora….c2.inst application    ONLINE    ONLINE    vanp…db02

ora….SM1.asm application    ONLINE    ONLINE    vanp…db01

ora….01.lsnr application    ONLINE    ONLINE    vanp…db01

ora….b01.gsd application    ONLINE    ONLINE    vanp…db01

ora….b01.ons application    ONLINE    ONLINE    vanp…db01

ora….b01.vip application    ONLINE    ONLINE    vanp…db01

ora….SM2.asm application    ONLINE    ONLINE    vanp…db02

ora….02.lsnr application    ONLINE    ONLINE    vanp…db02

ora….b02.gsd application    ONLINE    ONLINE    vanp…db02

ora….b02.ons application    ONLINE    ONLINE    vanp…db02

ora….b02.vip application    ONLINE    ONLINE    vanp…db02

2. Remove the Oracle Database Software from the Node to be Deleted

1). On node3 make sure you have correct ORACLE_HOME

[oracle@vanpgprodb05 db]$ echo $ORACLE_HOME

/databases/oracle/db

2). Update Node List for Oracle Database Software – (Remove node3):

[oracle@vanpgprodb05 bin]$ export DISPLAY=10.50.133.143:0

[oracle@vanpgprodb05 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=”" -local

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

Note: Although the OUI does not launch an installer GUI, the DISPLAY  environment variable still needs to be set!

3). De-install Oracle Database Software

Next, run the OUI from the node to be deleted (linux3) to de-install the Oracle Database software. Make certain that you choose the home to be removed and not just the products under that home.

4). Update Node List for Remaining Nodes in the Cluster (on any of remaining nodes)

[oracle@vanpgprodb01 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME “CLUSTER_NODES={vanpgprodb01,vanpgprodb02}”

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

3. Remove the Node to be Deleted from Oracle Clusterware

1). Remove Node-Specific Interface Configuration

[oracle@vanpgprodb05 db]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@vanpgprodb05 db]$ grep ‘^remoteport’ $ORA_CRS_HOME/opmn/conf/ons.config

remoteport=6200

[oracle@vanpgprodb05 bin]$ ./racgons remove_config vanpgprodb05:6200

racgons: Existing key value on vanpgprodb05 = 4948.

WARNING: vanpgprodb05:6200 does not exist.

[oracle@vanpgprodb05 bin]$ ./racgons remove_config vanpgprodb05:4948

racgons: Existing key value on vanpgprodb05 = 4948.

racgons: vanpgprodb05:4948 removed from OCR.

[oracle@vanpgprodb05 bin]$ ./oifcfg delif -node vanpgprodb05

PROC-4: The cluster registry key to be operated on does not exist.

PRIF-11: cluster registry error

Note: this error has been approved ok.

4. Disable Oracle Clusterware Applications

Running this script will stop the CRS stack and delete the ocr.loc  file on the node to be removed. The nosharedvar option assumes the ocr.loc file is not on a shared file sytem.

While logged into node3 as the root user account, run the following:

[root@vanpgprodb05 install]# ./rootdelete.sh local nosharedvar nosharedhome

CRS-0210: Could not find resource ‘ora.vanpgprodb05.LISTENER_VANPGPRODB05.lsnr’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.ons’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.vip’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.gsd’.

Shutting down Oracle Cluster Ready Services (CRS):

Feb 17 15:24:19.153 | INF | daemon shutting down

Stopping resources. This could take several minutes.

Successfully stopped CRS resources.

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

Shutdown has begun. The daemons should exit soon.

Checking to see if Oracle CRS stack is down…

Checking to see if Oracle CRS stack is down…

Oracle CRS stack is not running.

Oracle CRS stack is down now.

Removing script for Oracle Cluster Ready services

Updating ocr file for downgrade

Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

5. Delete Node from Cluster and Update OCR

Upon successful completion of the rootdelete.sh script, run the rootdeletenode.sh script to delete the node (linux3) from the Oracle cluster and to update the Oracle Cluster Registry (OCR). This script should be run from a pre-existing / available node in the cluster (node1) as the root user account:

Before executing rootdeletenode.sh, we need to know the node number associated with the node name to be deleted from the cluster. To determine the node number, run the following command as the oracle user account from node1:

[oracle@vanpgprodb01 bin]$ pwd

/databases/oracle/crs/bin

[oracle@vanpgprodb01 bin]$ olsnodes -n

vanpgprodb01    1

vanpgprodb02    2

vanpgprodb05    3

Note: notice the node # from result, we need to use it for removing node.

[root@vanpgprodb01 install]# pwd

/databases/oracle/crs/install

[root@vanpgprodb01 install]# ./rootdeletenode.sh vanpgprodb05,3

CRS-0210: Could not find resource ‘ora.vanpgprodb05.LISTENER_VANPGPRODB05.lsnr’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.ons’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.vip’.

CRS-0210: Could not find resource ‘ora.vanpgprodb05.gsd’.

CRS-0210: Could not find resource ora.vanpgprodb05.vip.

CRS nodeapps are deleted successfully

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

Successfully deleted 14 values from OCR.

Key SYSTEM.css.interfaces.nodevanpgprodb05 marked for deletion is not there. Ignoring.

Successfully deleted 5 keys from OCR.

Node deletion operation successful.

‘vanpgprodb05,3′ deleted successfully

[root@vanpgprodb01 install]# ../bin/olsnodes -n

vanpgprodb01    1

vanpgprodb02    2

6. Update Node List for Oracle Clusterware Software – (Remove node3)

From Node3 as Oracle user:

[oracle@vanpgprodb05 bin]$ pwd

/databases/oracle/crs/oui/bin

[oracle@vanpgprodb05 bin]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@vanpgprodb05 bin]$ export DISPLAY=10.50.133.143:0

[oracle@vanpgprodb05 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME CLUSTER_NODES=”" -local CRS=true

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

7. De-install Oracle Clusterware Software

[oracle@vanpgprodb05 bin]$ pwd

/databases/oracle/crs/oui/bin

[oracle@vanpgprodb05 bin]$ ./runInstaller

After delete the crs software, the directory will be deleted as well.

8. Update Node List for Remaining Nodes in the Cluster

[oracle@vanpgprodb01 bin]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@vanpgprodb01 bin]$ export DISPLAY=10.50.133.143:0

[oracle@vanpgprodb01 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME “CLUSTER_NODES={vanpgprodb01,vanpgprodb02}” CRS=true

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

C.         Reconfigure the OS and remaining hardware.

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

1. Check the tnsnames.ora on the rest of nodes if exists.

2. Delete oracle_home and crs_home

3. Next, as root, from the deleted node, verify that all init scripts and soft links are removed:

For Linux:

rm -f /etc/init.d/init.cssd

rm -f /etc/init.d/init.crs

rm -f /etc/init.d/init.crsd

rm -f /etc/init.d/init.evmd

rm -f /etc/rc2.d/K96init.crs

rm -f /etc/rc2.d/S96init.crs

rm -f /etc/rc3.d/K96init.crs

rm -f /etc/rc3.d/S96init.crs

rm -f /etc/rc5.d/K96init.crs

rm -f /etc/rc5.d/S96init.crs

rm -Rf /etc/oracle

rm -rf /etc/ora.tab

Reference:

1. Removing a Node from an Oracle RAC 10g Release 2 Cluster on Linux – (CentOS 4.5 / iSCSI)

by Jeff Hunter http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_23.shtml

2. How to delete a node from 3 node RAC in 10GR2

http://www.oraclefaq.net/2007/06/21/how-to-delete-a-node-from-3-node-rac-in-10gr2/

3. 10 Adding and Deleting Nodes and Instances on UNIX-Based Systems

Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2) Part Number B14197-09

http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/adddelunix.htm#BEIJAJHH

4. Removing a Node from a 10g RAC Cluster  Doc ID: 269320.1

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4196773229713543167::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,269320.1,1,1,1,helvetica

Change IP address for oracle RAC public and VIP interfaces

My company is doing massive re-ip project. So our Oracle RAC ip address has to be changed as well. Fortunately, we don’t need to change hostname, otherwise the story will be more complicated.

1. Current Environment
1). Machine IP:
Node1: vanpgvmrac01
Node2: vanpgvmrac02

## eth1-Public
10.50.96.101 vanpgvmrac01 vanpgvmrac01.product.com
10.50.96.102 vanpgvmrac02 vanpgvmrac02.product.com
## eth0-Private
192.168.199.1 vanpgvmracprv01 vanpgvmracprv01.product.com
192.168.199.2 vanpgvmracprv02 vanpgvmracprv02.product.com
## VIP
10.50.96.103 vanpgvmracvip01 vanpgvmracvip01.product.com
10.50.96.104 vanpgvmracvip02 vanpgvmracvip02.product.com

2). cluster information:
cluster name – vm10cls
database name – v10c
Instance 1 – v10c1
Instance 2 – v10c2
Node1 – vanpgvmrac01
Node2 – vanpgvmrac02

2. New IP changing map(different subnet mask too):
10.50.96.101/255.255.255.0 vanpgvmrac01 –> 10.50.99.41/255.255.252.0
10.50.96.102/255.255.255.0 vanpgvmrac02 –> 10.50.99.42/255.255.252.0
10.50.96.103/255.255.255.0 vanpgvmracvip01 –> 10.50.99.43/255.255.252.0
10.50.96.104/255.255.255.0 vanpgvmracvip02 –> 10.50.99.44/255.255.252.0

3. steps 1 — change RAC IP settings
1). bring service down, make sure everything was offline except css daemon
bash-3.1$ srvctl stop database -d v10c
bash-3.1$ srvctl stop nodeapps -n vanpgvmrac01
bash-3.1$ srvctl stop nodeapps -n vanpgvmrac02
bash-3.1$ crs_stat -t
Name Type Target State Host
————————————————————
ora.v10c.db application OFFLINE OFFLINE
ora….c1.inst application OFFLINE OFFLINE
ora….c2.inst application OFFLINE OFFLINE
ora….SM1.asm application OFFLINE OFFLINE
ora….01.lsnr application OFFLINE OFFLINE
ora….c01.gsd application OFFLINE OFFLINE
ora….c01.ons application OFFLINE OFFLINE
ora….c01.vip application OFFLINE OFFLINE
ora….SM2.asm application OFFLINE OFFLINE
ora….02.lsnr application OFFLINE OFFLINE
ora….c02.gsd application OFFLINE OFFLINE
ora….c02.ons application OFFLINE OFFLINE
ora….c02.vip application OFFLINE OFFLINE

2). backup OCR and Voting disks
bash-3.1$ ocrcheck|grep -i file
Device/File Name : /dev/raw/raw1
bash-3.1$ crsctl query css votedisk
0. 0 /dev/raw/raw2
located 1 votedisk(s).

#dd if=/dev/raw/raw1 of=/database/temp/ocr_vote_bk/ocr.bak
#dd if=/dev/raw/raw2 of=/database/temp/ocr_vote_bk/vote.bak

3). get current config:
bash-3.1$ oifcfg getif
eth0 10.50.96.0 global public –current network for public
eth1 192.168.199.0 global cluster_interconnect –we are not going to change this

4). delete current public ip:
bash-3.1$ oifcfg delif -global eth0

5). change to new network:
bash-3.1$ oifcfg setif -global eth0/10.50.99.0:public

6). change vip address:
a. check current one
bash-3.1$ srvctl config nodeapps -n vanpgvmrac01 -a
VIP exists.: /vanpgvmracvip01/10.50.96.103/255.255.255.0/eth0
bash-3.1$ srvctl config nodeapps -n vanpgvmrac02 -a
VIP exists.: /vanpgvmracvip02/10.50.96.104/255.255.255.0/eth0
b. Modify VIP component (has to be the css owner, “root” usually)
#srvctl modify nodeapps -n vanpgvmrac01 -A 10.50.99.43/255.255.252.0/eth0
#srvctl modify nodeapps -n vanpgvmrac02 -A 10.50.99.44/255.255.252.0/eth0
c. double verify the changes
bash-3.1$ srvctl config nodeapps -n vanpgvmrac01 -a
VIP exists.: /vanpgvmracvip01/10.50.99.43/255.255.252.0/eth0
bash-3.1$ srvctl config nodeapps -n vanpgvmrac02 -a
VIP exists.: /vanpgvmracvip02/10.50.99.44/255.255.252.0/eth0

7). change the hosts file(on both nodes):
## eth1-Public
10.50.99.41 vanpgvmrac01 vanpgvmrac01.product.com
10.50.99.42 vanpgvmrac02 vanpgvmrac02.product.com
## eth0-Private
192.168.199.1 vanpgvmracprv01 vanpgvmracprv01.product.com
192.168.199.2 vanpgvmracprv02 vanpgvmracprv02.product.com
## VIP
10.50.99.43 vanpgvmracvip01 vanpgvmracvip01.product.com
10.50.99.44 vanpgvmracvip02 vanpgvmracvip02.product.com

8). if the listener is using any IP address, it also needs to be changed.

4. Steps 2 — change OS IP settings
1). change IP
[root@vanpgvmrac01]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
BOOTPROTO=static
IPADDR=10.50.99.41
NETMASK=255.255.252.0
HWADDR=00:50:56:BD:05:14
ONBOOT=yes

[root@vanpgvmrac02]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
BOOTPROTO=static
IPADDR=10.50.99.42
NETMASK=255.255.252.0
HWADDR=00:50:56:BD:3E:08
ONBOOT=yes

2). change the default gateway on both nodes (if needed, here since they are in same vlan so i didn’t change them)
[root@vanpgvmrac01 ~]# cat /etc/sysconfig/network
NETWORKING_IPV6=yes
HOSTNAME=vanpgvmrac01
NETWORKING=yes
NISDOMAIN=vanrdnis
GATEWAY=10.50.96.1 <– here is the default gateway to be changed

3) Change the IP Address’es on the known_hosts ssh config files for oracle user
$ su – oracle
$ cd .ssh
$ cp known_hosts known_hosts.bak
$ modify the old IP’s to the new IP’s

4). restart network (on both node)
#service network restart

5). restart crs daemon (on both node)
#crsctl stop crs
#crsctl start crs

5. Step3 — verify everything

reference:
1. "How to Change Interconnect/Public Interface IP or Subnet in Oracle Clusterware", Doc ID: 283684.1
2. "Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node", DOC ID: 276434.1
3. "How to change Public and VIP component address in case of RAC?" http://orcl-experts.info/index.php?name=FAQ&id_cat=9