How to make user to view they jobs in sql 2005

i tried to make user able to see their own jobs in sql 2005. First thing you need to assign job owner as user’s account.

Second thing, sql 2005 server has some new server roles (on MSDB database user only!) for managing sql agent stuff. Here is the screenshot:

With SQL Server 2005, a user must be a member of one of the new fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. After this basic introduction let us identify what are the specific uses of each role.

SQLAgentUserRole

This is the least privileged role among new three new fixed server roles. This role has permissions only on operators, local jobs, and job schedules. These types of users can only view the operator but they can’t create, modify or delete operators. They cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. These users cannot delete job history.

SQLAgentReaderRole

SQLAgentReaderRole has all the options that SQLAgentUserRole has. In addition, it does have permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.

SQLAgentOperatorRole

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server.

Link: http://www.sqlservercentral.com/articles/Administration/sqlserveragent2005/2376/

How to connection sql server named instance SSIS service

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:

"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."

The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.

The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

The default contents of the file:

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).

Modify the configuration file to point to the named instance as follows.

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.\SQL2005</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

After making the change you MUST restart the SSIS service for the change to take effect.

Another normal issue to connect to SSIS in sqlserver 2005 is “access is denied”. The following workground can resolve this issue(http://msdn.microsoft.com/en-us/library/aa337083.aspx):

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5. Right-click on MsDtsServer and select Properties.
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9. Click OK to close the dialog box.
10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
11. Close the MMC snap-in.
12. Restart the Integration Services service.

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

MySQL disable safe update mode

when you update a table and in where clause the column name is not the primary key, you will get error said:

You are using safe update mode and you tried to update a table without…etc.”

It should be noted that even simple applications of UPDATE can conflict with the ’safe mode’ setting of the mysql daemon. Many server admins default the MySQL daemon to ’safe mode’.

If UPDATE gives an error like this:

“You are using safe update mode and you tried to update a table without…etc.”

…then it may be that your .cnf file must be edited to disable safemode. This worked for me. In order for the change in the .cnf file to take effect, you must have permission to restart mysqld in the server OS environment. There is a page in the online documentation that explains safe mode entitled ’safe Server Startup Script’.

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!

Unix File and Directory Permissions and Modes

Unix File and Directory Permissions and Modes

© 2001–2006 by Wayne Pollock, Tampa Florida USA.
Overview:

Each file or directory contains 12 settable permission (or mode) bits, which means there are 2**12 = 4096 possible permission settings!  The 12 bits are either on (set to 1) or off (set to zero).  Each can be changed independently.

Unix doesn’t support the idea of inherited permissions.  So, unlike other systems, setting read permission on a directory for some user does not give that user read permission on the files within that directory.

Note that permissions do not grant users the right to run certain programs, rather they grant the right to use certain system calls (of the Unix API).  A command such as cat or more is written using the read() system call, and only files and directories that have the “r” permission for a user permit the use of this system call.  This is why a user can’t use more, vi, etc., on any file on which they don’t have “r” permission.  Similarly, a user must have “w” permission to write() a file or directory, which is the system call used to modify files and directories (which are files too).  The “x” permission permits a user to exec() a file, which means to execute it as a program.  (In Unix, a program or application is just a file that has execute (“x”) permission.)

In short any program makes one or more system calls to access files and directories.  A user process must have been granted the appropriate permissions (one or more of “r” for read, “w for write”, or “x” for execute) or the access will fail.  Note that other system calls (such as stat()) will also fail if the right permissions aren’t granted.

To see all the system calls a given program uses, you can use the strace command.  (This may produce a lot of output!)  Once you know which system call is used, you can check the man pages for that system call to see what permissions are needed to use it.  (See note.)
Classes of Users

The basic permissions of “r”, “w”, and “x”, are applied to three different categories of users.  Note that every file and directory in Unix is identified with an owner and a group.  The categories are owner (occasionally referred to as the file’s user or user owner), group (or group owner), and others.  (See note.)

In addition to these nine mode bits (“r”, “w”, and “x”, for each of three categories of owner, group, and others), there are three others: the set User ID (SUID or setuid), the set Group ID (SGID or setgid), and the sticky (or text) bit.  The effect of these three bits depends on what other modes are set, and differs for files and directories.

If the person (*) attempting to read, write, or execute a file is the same as the owner, the first set of permissions is used and the remaining six bits (three for group and three for others) are ignored.  But if the person is not the same as the owner, the system will check the group of the file against all the groups the person is a member of.  If there is a match then the second set of permissions are used.  If the person is not the owner and not a member of the group for the file, then the third set of permissions is used to determine what access the person is allowed.

To see the permissions for files and directories use the ls -l filename command.  (On a directory, use the ls -ld directoryname command since otherwise ls gives information on the files within that directory and not on the directory itself.)  To illustrate, suppose the permissions for a file named foo are listed as follows:

-rw-r—–    1 Hymie   staff         78 Aug 14 13:08 foo

The first dash indicates an ordinary file.  On a directory you would see a “d” instead.  The next nine characters tell what permissions have been granted.  The first three (“rw-”) indicate what permissions have been granted to the owner (“Hymie”) of the file.  In this case the owner has been granted read and write permission, but not execute permission.  The next three show what permissions have been granted to members of the file’s group.  Here (“r–”) they show that group “Staff” members have read access only.  The last three characters show that the others (i.e., not the owner “Hymie” and not members of group “Staff”) have no permissions granted.

Note the SUID, SGID, and sticky bits have no columns of their own in an ls output, but if turned on they show up as special characters (that is, not “x” or “-”) in the execute columns for the owner, group, and others.  The SUID bit displays as an “S” in the owner’s execute column of the output.  If the execute bit is also set then an “s” is used.  The SGID bit appears similarly in the group’s execute column.  The sticky bit appears in the others’ execute column, as a “T” or as a “t” if the others execute bit is also set.

An example:

-rwsr-S–t    1 Hymie   staff         78 Aug 14 13:08 foo

Here, the owner (Hymie) is granted read, write, and execute permission, the group members (staff) are granted read permission, and others are granted execute permission.  In addition, the SUID, SGID, and sticky bits are all set.
Files

Suppose some user attempts to read a file with some Unix command such as cat.  The system call read() is used and the “r” permission is required.  The system checks to see if the user is in fact the owner of the file.  If so, the access is permitted if the owner has been granted “r” permission.  If not, the system check to see if the user is a member of the group of the file.  If so, access is permitted if the group has been granted “r” permission.

If the user is neither the owner nor a member of the file’s group then the access is permitted if others has been granted “r” permission.

The same logic holds for attempts to modify the file (write) or to run it (execute).
Special Considerations on Files:
Execute permission and scripts

If a user has execute (“x”) permission on some file but not read (“r”) permission, he or she can execute the file.  In other words, the file is an application program.  However if the user doesn’t also have read permission he cannot copy the file, since the cp command requires read (“r”) permission to work.

On the other hand, a shell script file with execute permission only will not run!  This is because any script file (including Perl scripts) cannot be executed directly by the system with an exec() system call.  Instead the proper script interpreter (usually shell or Perl) is actually executed.  This interpreter in turn attempts to read the script file.  (It is possible to run a shell script or Perl script without execute permission, by entering “perl script” or “sh script”.)

The proper permissions on a script are both read and execute.  Setting the execute bit on causes the kernel to start up the shell (*) which reads the script.  This is one reason why scripts are less secure than compiled programs; scripts must be readable and executable but compiled programs need only be executable.

The remaining three mode bits also affect access to files.  Their effects depend on the other permissions set.

SUID on a file

If any category of user is granted execute permission, then this bit causes the owner of the resulting process to be that of the file, and not of the user running the program.  So if the program attempts to read() something, the permissions that apply would be for the owner of the file and not the user of the program.

For example, suppose user Jane runs the command “view memo.txt”, and the permissions on the view command and the file memo.txt are as follows:

-rwx–x–x    1 root    bin         4515 Aug 14 13:08 view
-rw——-    1 root    bin          218 Aug 14 13:08 memo.txt

Jane has permission to run view, but not permission to read memo.txt.  So when this view program attempts to read() the file a “permission denied” error will occur.

Suppose we change the view program to have the SUID bit on:

-rws–x–x    1 root    bin         4515 Aug 14 13:08 view

Now, when Jane runs this SUID program, the access to memo.txt is permitted.  When view attempts to read() the file, the system doesn’t think Jane is attempting to read, it thinks “root” is the user.  So the access is allowed.

A similar substitution occurs if the SGID bit is set and any execute bits are set.  The group ID checked is not the current user, but the group of the program.

Technically, every process has a real user (RUID) and a real group (RGID).  These are the user and group of the person who started the process by running some program.  Every process also has an effective user id EUID and EGID.  By default these are the same.  But if you run a program that has the SUID or SGID bits on, the effective UID or effective GID become those of the file, not of the person.
SUID and DLLs

Note that today, many (too many if you ask me) executables use dynamic link libraries.  (DLLs have the extension .so or .so.number on Unix and Linux, where the “so” stands for shared object.)  Such a program controls which libraries to link to at runtime.  This process uses configuration files in /etc but those system-wide defaults can be over-ridden by setting certain environment variables.  This could be a very dangerous security hole for SUID or SGID programs (I write an evil library, then set the environment variables so that your SUID program runs using my evil code), so when the EUID or EGID differ from the RUID or RGID, a suid program ignores the environment variables (e.g., “LD_LIBRARY_PATH”) and only uses DLLs from the standard, preconfigured locations.
SUID and GGID on non-executable files

If the SUID bit is set on a file with no execute bits set the SUID has no effect.  However, if the SGID bit is set on a file without any execute bits set, then some sort of file and/or record locking may be enabled.  This means that if one process has that file open, any other attempts to open it will block.  In Linux and System V systems, when SGID is set on a file that does not have group execute privileges, this indicates a file that is subject to mandatory locking during access (if the filesystem is mounted to support mandatory locking with mount -o mand).  This overload of meaning surprises many and is not universal across Unix-like systems.  In fact, the Open Group’s Single Unix Specification version 2 for chmod(3) permits systems to ignore requests to turn on SGID for files that aren’t executable if such a setting has no meaning.

WARNING:   SUID and SGID programs can be dangerous.  They are not usually needed.  SUID and SGID scripts are incredibly dangerous and can easily allow evil-doers super-user access to your system!!  *Never* allow a SUID or SGID writable program on you system for even a minute!

The Sticky (a.k.a. text) Bit

The sticky bit has no real purpose on files anymore.  It was used to keep a program in memory, so that the next time a user starts that program it would start faster.  This is obsolete on modern systems.  This is a very common Unix extension and is specified in The Open Group’s ( www.opengroup.org external link) Single Unix Specification version 2.  Old versions of Unix called this the save program text bit and used this to indicate executable files that should stay in memory.  Systems that did this ensured that only root could set this bit (otherwise users could have crashed systems by forcing everything into memory).  In Linux, this bit has no effect on ordinary files and ordinary users can modify this bit on the files they own: Linux’s virtual memory management makes this old use irrelevant.

Final note:  Changing the name of a file or deleting it completely are not tasks that require the write() system call.  So a user doesn’t need read (“r”) or write (“w”) permission to rename or delete a file.  You don’t even have to be the owner of a file to delete it (*)!  However, when using mv to move a file to another directory on another disk, (e.g., “mv foo /floppy”) the system must copy the file to the other disk and therefore does need read permission.
Directories

Directories in Unix are just files.  They contain little information, just the name of a file and its inode number.  All information about a file is kept in the file’s inode.  (Only the owner can modify the information in the inode, such as the permission bits and group.  Also, only the super-user root can change the owner of a file on most Unix and Linux systems.)

The permission bits on directories control access to different system calls than for regular files:
Read and Write Permission for Directories

To read a directory using read(), opendir(), or readdir() requires read permission.  Note the ls command needs this.

To modify the contents of a directory requires write permission.  If you have write permission on some directory, you can add files to it, rename and delete files from it (this requires execute permission too, as discussed below).

Note you don’t have to be the owner of a file or have write permission on it to rename or delete it!  You only need write permission on the directory that contains the file.
Execute Permission for Directories

The chdir() system call requires execute permission on a directory.  Of course a directory isn’t really a program that you can run, even if it has execute permission.  The execute bit is reused rather than waste space with additional permission bits.

Besides controlling a user’s ability to cd into some directory, the execute permission is required on a directory to use the stat() system call on files within that directory.  The stat() system called is used to access the information in a file’s inode, and must be done before you can open or delete (via the unlink() system call) that file.  (See Note.)

Because of its role in file access the execute bit on a directory is sometimes called search permission.  For example, to read a file foo/bar, you must have read permission for the file itself, but before the file can be accessed you must first search the directory foo for the inode of file bar.  This requires search (“x”) permission on the directory foo.  (Note you don’t need read permission on the directory in this case!  You only need read permission on a directory to list its contents.)
Special Considerations on Directories:
Execute Permission

The use of the execute permission on a directory has some non-obvious effects on file access.  Note that if execute permission is required for a directory, it is usually required for each directory component on the full pathname of that directory.

Without execute permission on a directory, a user can’t access files in a directory even if they own them and have all permissions on them.

With read but not execute, you can do ls someDir but not ls -l someDir.  With execute but not read permission, you can ls -l someDir/file but not ls someDir or ls -l someDir.  Thinking of the system calls involved (read and stat) may help clarify this.  Also, make sure ls isn’t aliased to something such as ls –color or ls -F, since these options change the listing to identify directories, links, and executables by using stat.  (Try /bin/ls each time, or unalias ls.)

Remember that to use ls -l file, or on some systems ls -i dir (i.e., to use stat() system call), you must have execute on the directory, the directory’s parent, and all ancestor directories up to and including “/” (the root directory).

With execute but not read permission on a directory, users cannot list the contents of the directory but can access files within it if they know about them.

A common situation illustrating all this is user web sites.  If a user’s web page is /home/auser/public_html/index.htm, then ‘x’ permission is needed for everyone on /, /home, /home/auser, and /home/auser/public_html, and the file index.htm needs ‘r’ permission for everyone (‘x’ is not needed for the file.)

To delete a file requires both write (to modify the directory itself) and execute (to stat() the file’s inode) on a directory.  Note a user needs no permissions on a file nor be the file’s owner to delete it!

To put or create a file in a directory required both ‘w’ and ‘x’ permissions.  Write permission is needed because you are modifying the directory with a new hard link, and execute permission is needed in order to use stat, open, and creat system calls.  (Creating a file involves trying to open the file first to see if it already exists and stat if it does, and using either ln to create a new hard link or creat to create a new file.)
SUID and SGID for Directories

The SUID bit has no effect on directories.

In Linux and Solaris, when SGID is set on a directory files created in that directory will have their GID automatically reset to that of the directory’s GID.  This means that setting the SGID bit on a directory causes any new files or directories created within to inherit the group identity of that directory rather than that of the user.  Also, new sub-directories will inherit the SGID bit as well.

The purpose of this approach is to support project directories: users can save files into such SGID directories and the group identity of the file automatically changes.  This is useful for example on the Document Root of a website or other directories containing a set of files worked on by a specific group of users.  (It works especially well if each user’s primary group (*) is a private group for that user, and the umask (*) setting is 002).  However, setting the setgid bit on directories is not specified by standards such as the Single Unix Specification [Open Group external link 1997].
Sticky bit on Directories

The sticky bit is used on directories that are writable by group or others.  As noted earlier a user doesn’t have to be the owner of a file to delete it, nor have been granted any permissions on that file.  A user needs only write and execute permission on the directory to delete any file contained within it.  However if the sticky bit is also set on the directory, only the owner of a file (and the super-user) will be able to delete that file.  Public directories such as /tmp use this feature.  (Not all versions of Unix support this use of the sticky bit, unfortunately.)
Other Permission Information:
ACLs

Some Unixes (notably HP-UX and Solaris) support the idea of file and directory ACLs (Access control lists), which are a means of granting sets of individual users permissions.  You can think of it this way:  Normally a file is associated with a single user (the user owner) and a single group (the group owner).  With ACLs a file can be associated with multiple users and groups, not just the owner user and group.  Each of these groups and users can be granted any of the normal permissions (read, write, or execute).  Note that only the real file owner can change permissions, just as before.

Some ACL implementations do support inheritance of permissions.  However these ACLs are independent of the standard methods described here, which use the 12 permission bits.  (There is a POSIX standard for ACLs but it is not widely used except on Linux.)

If you set the “default” ACL on a directory, then any subsequently created files/directories will also have their ACL set to a copy of this default ACL.  (This is spoken of as new files “inheriting” the default ACL of their parent directory, but this term can be confusing; subsequently changing the default ACL on the parent directory will not change the ACLs of any existing files within that directory.)

Try this (using POSIX ACLs):

cd
mkdir test
touch test/foo
setfacl -d -m user:nobody:r– test
touch test/bar
getfacl -R test

(-d means to change the default ACL,-m means to modify)

You should see that bar is readable by “nobody”, but that foo is not.  When changing the default ACL of a directory, there is a recursive option for setfacl you can use to change the ACLs of existing files as well.  (In the example above, use:

setfacl -R -m user:nobody:r– test

ACLs can be used to solve the per-directory umask problem.  In a highly secure system, umask is set to 077.  But creating a new file in a project workgroup directory, that is a directory holding a group’s project’s files, this is the wrong value since you would want new files to be accessible by group members.  For directories the value is also wrong since you normally want those to have group read, write, and executable permissions.  On a web site, new files need to be group accessible and also read by others; new subdirectories need execute by others too.

Keeping umask set to a highly secure value and setting a default ACL on a directory to add the desired extra group and other permissions per directory works well, especially if the SetGID is also set on that directory.
Rootly Powers

Some operations don’t use the permission (mode) bits to allow or deny access.  In some cases permission solely depends on who is making the request.  For example, only the user owner (or root) can change the permissions.  Other operations require the user to be root.  Examples include halting the system and starting daemons (servers) that listen on “privileged ports” (i.e., TCP and UDP port numbers below 1024).  The kernel simply checks the UID of the process to see if it is “0″ (root), and grants or denies access accordingly.

While common in Unix and Linux systems this scheme is flawed, in that many programs must be run as root.  Thus, if an attacker finds some exploit in such a program then that user has gained complete control!  This meant that a web server, print server, DNS server, etc., would all run as root.  Many times in the past this has indeed led to security problems.

In some modern systems (notably Solaris and Linux), internally the rootly powers have been split up into about a dozen separate privileges (the term used on Solaris) or capabilities (the term used on Linux).  This internal change is invisible to most users—root gets all these rights and regular users get none, so the system works exactly as before.

Where it gets interesting is that a program that was started by root (and thus has all rootly power) can selectively give up the rights it doesn’t need.  All modern server programs thus start as root, give up the rights it doesn’t need, uses those rights, and finally sets the UID to a completely non-privileged user.  In this way, even if an attacker finds an exploit in some server daemon, there is very little privilege they can exploit.

You can view a process’s privileges on Solaris using the ppriv command, and on Linux using the getpcaps command.  On either system one can also use the /proc system to see this information.  On Linux for example:

cat /proc/pid/status | grep Cap

MAC and DAC

The twelve permission bits (or mode bits) discussed above, the three special bits (SUID, SGID, text) and the three groups of user, group, and other permissions, can be changed on any file or directory at the discretion of the owner (or by root).  For this reason such permissions are called discretionary access controls (or “DACs”).  DACs can be considered weak because if an attacker gains access to your system they can change these permissions and do whatever they want.

In modern versions of Unix and Linux an alternative can be used.  A separate permission system can be enabled that loads a policy at boot time that determines who can do what.  This policy cannot be modified without a reboot.  (See Note.) Because the system will require a process to have these permissions to proceed with some operation, this system is called mandatory access controls (or “MACs”).

If MAC is enabled, both MAC and DAC systems must allow some operation.  For example, if the DAC permissions allow some user to read a file but the MAC policy doesn’t, or if the MAC policy does allow a user to read some file but the DAC doesn’t, then access is denied.

Several MAC systems are available.  For Linux, consider using SELinux external link or LIDS external link.

How complicated a windows share could be?

Setting up a windows shares is pretty straighforward, just right clicking the folder and give the proper permissions for sharing/security then you done!
But when things come together if you are using a VM machine plus dns alias for machine name, to access the share is not that easy any more. Lots of tricky part need to be done
in the windows registry.

Here is the full story about that.

Background:
Machine name:        vanpgvmwin056 (windows 2003 server VM on ESX server)
DNS alias(cname):    vdcdbrepl
share folder path:    C:\vdcdbrepl_source
Share name:        \\vdcdbrepl\vdcdbrepl_source
domain name:        product
Account name:        nsd.service

It seems everything is ok and you can access \\vdcdbrepl\vdcdbrepl_source. But life is not that easy as you expected.

###Issue 1: when you try to connect the shared folder remotely, you may get some invalid account error information EVEN you’ve given the correct account/password.
This will happen especially when you did some rollbacks for your VM images. if you take a look at the share permission tab,
the account here will be some weird number like: s178453728.

The reason is in windows 2003 domain, the workstation and PDC exchange password every 7 days. if you rollback the VM machine back, the password are not sync up any more so
you’re screwed.

Solution(MS reference:http://support.microsoft.com/default.aspx?scid=kb;en-us;q175468):
1). quit the domain on your workstation and re-join the domain, in this way you will get a new copy from PDC so they are sync up again.
2). in order to avoiding this happend again, disable the passwd changing by editing this registry entry to:
HKEY_LOCAL_MACHINES\SYSTEM\CurrentControlSet\Services\Netlogon\Parameters\DisablePasswordChange=1 (REG_DWORD)

###Issue 2: You get error like “Access denied” or “No network provider accepted the given network path”
The reason is because Windows Server 2003 (starts from SP1) includes a new security feature named loopback check functionality. By default, loopback check functionality is turned on in Windows Server 2003 SP1, and the value of the DisableLoopbackCheck registry entry is set to 0 (zero).

Solution(MS reference:http://support.microsoft.com/kb/926642):
Re-enable the behaviour that exists in Windows Server 2003 by setting the DisableLoopbackCheck registry entry in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry subkey to 1. To set the DisableLoopbackCheck registry entry to 1, follow these steps on the client computer:
1).Click Start, click Run, type regedit, and then click OK.
2).Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3).Right-click Lsa, point to New, and then click DWORD Value.
4).Type DisableLoopbackCheck, and then press ENTER.
5).Right-click DisableLoopbackCheck, and then click Modify.
6).In the Value data box, type 1, and then click OK.
7).Exit Registry Editor.

###Issue 3: After these tuned up above, you think you are ok to go, but you may still get error like this “A duplicate name exists on the network”
The reason is windows 2003 has problem with cname access.This problem can occur when you try to connect to the server by using a CNAME alias that is created in the DNS zone.

Solution(MS reference:http://support.microsoft.com/kb/281308):
To resolve this problem in Windows Server 2003, complete the following steps:
Create the CNAME record for the file server on the appropriate DNS server, if the CNAME record is not already present.
Apply the following registry change to the file server. To do so, follow these steps:
1).Start Registry Editor (Regedt32.exe).
2).Locate and click the following key in the registry:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
3).On the Edit menu, click Add Value, and then add the following registry value:
Value name: DisableStrictNameChecking
Data type: REG_DWORD
Radix: Decimal
Value: 1
4).Quit Registry Editor.
5).Restart your compute

Now everything is really ok. Enjoy the sharing:

C:\>net use w: \\vdcdbrepl\vdcdbrepl_source /user:product\nsd.service *
Type the password for \\vdcdbrepl\vdcdbrepl_source:
The command completed successfully.

C:\>dir w:
Volume in drive W has no label.
Volume Serial Number is 98DF-E535

Directory of W:\

02/24/2009  11:20 AM    <DIR>          .
02/24/2009  11:20 AM    <DIR>          ..
02/24/2009  11:20 AM                 7 firstlevel_file.txt
02/17/2009  10:11 AM    <DIR>          test
02/24/2009  11:19 AM    <DIR>          testfolder200902231150am
1 File(s)              7 bytes
4 Dir(s)  29,964,087,296 bytes free

C:\>

sql server cluster re-ip best practise

Changing IP addresses for sql server 2005/2000 cluster basically bases on two steps:
1. changing virtual IP addresses in cluster administrator ( and registry for sql server 2000)
2. changing physical IP addresses

1. Changing Virutal IP addresses in cluster administrator
1).connect to cluster administrator and take all the group resource offline.
2).changing the virtual IP addresses by right clicking every item with ip address resource type, then “property”->”parameter” to change.
Usually for an active-active sql cluster environment, you need at least change three virtual ip address:
Two for each node’s virtual IP, one for cluster management IP.

Caution: for sql server 2005 cluster, after you changing the IP address in cluster administrator, the registry entries for these ip also get updated automatically.
But for sql server 2000 cluster, you have to update these in registry manually in these locations on both nodes:
For a default instance of virtual SQL Server, the following registry value points to a previous IP address-> HKLM\Software\Microsoft\MSSQLServer\Cluster
For a named instance of virtual SQL Server, the following registry value points to a previous IP address->HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Cluster\ClusterIpAddr

Reference:
“when you change the IP address on a SQL Server failover cluster node: “Bind failed”" http://support.microsoft.com/kb/319578/EN-US/

2. changing physical IP addresses in windows network properties.
Nothing new here just regular IP changing process.

3. reboot the server.

4. bring the resource online.

5. Verify all the database connections and applications.
Tips: to verify the port binding after ip changing, you can do port scan for the virutal server name of both nodes to see if the designated port are opened for connection.
For unix: nmap -p1433 hostname
For windows: netstat

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