DBA Sensation

November 19, 2008

Comparing SQL Server 2005 High Availability technologies

Filed under: 3. MS SQL Server — zhefeng @ 11:02 am

i found a good chart to compare all sql server HA options (http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx):
comparison

Advertisements

November 12, 2008

Mapping of Fixed Database Roles to Permissions

Filed under: 3. MS SQL Server — zhefeng @ 3:57 pm

db_accessadmin
Granted: ALTER ANY USER, CREATE SCHEMA
Granted: VIEW ANY DATABASE

db_accessadmin
Granted with GRANT option: CONNECT

db_backupoperator
Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
Granted: VIEW ANY DATABASE

db_datareader
Granted: SELECT
Granted: VIEW ANY DATABASE

db_datawriter
Granted: DELETE, INSERT, UPDATE
Granted: VIEW ANY DATABASE

db_ddladmin
Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
Granted: VIEW ANY DATABASE

db_denydatareader
Denied: SELECT
Granted: VIEW ANY DATABASE

db_denydatawriter
Denied: DELETE, INSERT, UPDATE

db_owner
Granted with GRANT option: CONTROL
Granted: VIEW ANY DATABASE

db_securityadmin
Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
Granted: VIEW ANY DATABASE

Bonus:
1. how to check the user’s server permission
select a.* from sys.server_permissions a,sys.server_principals b
where a.grantee_principal_id=b.principal_id
and b.name=’%username%’;

November 7, 2008

Fix orphaned users in SQL Server

Filed under: 3. MS SQL Server — zhefeng @ 11:05 am

Every time I restore a database, I forget how to fix the orphaned users.  So I google, trying to find the magic words that will bring me to that stored procedure that I know exists, but I just can’t remember the name of.  For those of you who don’t know, sp_change_users_login has an ‘Auto_Fix’ action that will map a user in a database to a login of the same name. Since I’m going to forget all that again, I’m posting this little snippet of Books Online.  Maybe Google will help me find it next time!

sp_change_users_login

Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.

Syntax

sp_change_users_login [ @Action = ] action
[ , [ @UserNamePattern = ] user ]
[ , [ @LoginName = ] login ]

Arguments

[@Action =] action

Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Value

Description

Auto_Fix

Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string (”), or not specified.

Report

Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string (”), or not specified.

Update_One

Links the specified user in the current database to login. login must already exist. user and login must be specified.


[@UserNamePattern =] user

Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.

[@LoginName =] login

Is the name of a SQL Server login. login is sysname, with a default of NULL.

Sample Usage:

–Check if there is any orphaned user in the database
use [databasename];
exec sp_change_users_login @action=’report’;

–use auto fix to fix the orphaned user
use [databasename];
exec sp_change_users_login ‘auto_fix’,’username’;

–link the orphaned user to existing login
use [databasename];
exec sp_change_users_login ‘update_one’, ‘username’,’userlogin’;

or

sp_change_users_login @Action=’update_one’, @UserNamePattern='<database_user>’, @LoginName='<login_name>’;

10g RAC installation – copy files to remote node error

Filed under: [Installation] — zhefeng @ 9:36 am

When i installed the database software on two nodes RAC, got error like below:

When copying files to remote nodes during a 10.2 RAC installation you may see the following error on the screen and in the installActions.log file:

INFO: Saving Cluster Inventory
SEVERE: oracle.sysman.oii.oiip.oiipg.OiipgRemoteFileOperationException: Bootstrapping installer to nodes failed.Dir path
is /tmp/OraInstall2005-06-01_04-11-13PM. [PRKC-1002 : All the submitted commands did not execute successfully]
———————————————————————————-
opcbrh2:
/bin/tar: ./common/rulemap.xml: time stamp 2005-06-01 16:11:22 is 609 s in the future
/bin/tar: ./common: time stamp 2005-06-01 16:13:24 is 731 s in the future
/bin/tar: ./cpuinfo.txt: time stamp 2005-06-01 16:13:29 is 736 s in the future
…continued…

That’s because the two nodes have different time. The solution is using NTP on both nodes and sync the time to the same.

1. edit /etc/ntp.conf

add one line:

server ntpdhost.rd.crystald.net  #you can also use other NTP servers like http://www.pool.ntp.org/

2. sync the time with NTP server

#ntpdate -u ntpdhost.rd.crystald.net

3. start the NTP service

#service ntpd start

4.test date for both node

bash-3.1$ date;ssh vanpgvmrac02 date
Fri Nov  7 09:35:33 PST 2008
Fri Nov  7 09:35:33 PST 2008

After the time has been synchronized, the installation could be completed successfully.

November 6, 2008

how to export/import data with LOB type from one user/schema to another user?

Filed under: [backup and recovery] — zhefeng @ 2:05 pm

When you are using exp/imp moving data from one user/schema to another, if the schema contains some table with lob data type, then you will get error like this:

IMP-00017: following statement failed with ORACLE error 1950:
“CREATE TABLE “TEST” (“ID” NUMBER, “NAME” CLOB) ……..
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace ‘USERS’

So how to export/import data with LOB type from one user/schema to another user?

Here are the solutions:

1. for oracle 10g and 11g, directly using datapump can avoid this issue. Datapump support lob pretty well:

export:

expdp jehan directory=DATA_PUMP_DIR schemas=(LOUVRE_121307,DENALI10G) dumpfile=cart_dp.dmp logfile=cart_expdp.log

import:

impdp system DIRECTORY=DATA_PUMP_DIR DUMPFILE=CART_DP.DMP LOGFILE=cart_impdp.log REMAP_SCHEMA=LOUVRE_121307:cartesis_LOUVRE_121307 REMAP_SCHEMA=DENALI10G:cartesis_DENALI10G REMAP_TABLESPACE=users:cartesis REMAP_TABLESPACE=TBS_DATA_DENALI10G:cartesis REMAP_TABLESPACE=CARTESIS_DATA:cartesis REMAP_TABLESPACE=TBS_DATA_LOUVRE_121307:cartesis

2. for 9i, you have to extract the lob table DDL and create it manually, then do the importing

step1: Import with INDEXFILE parameter to generate the script that can be used to modify the LOB’s tablespace clause.
$imp system/manager fromuser=user1 tables=\(test\) indexfile=create_lob_table

step2: Edit the generated script file create_lob_table.sql:

REM CREATE TABLE “USER1″.”TEST” (“ID” VARCHAR2(8) NOT NULL
REM ENABLE, “FIC_CIRCUL” BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM 255 LOGGING STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
REM 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE “USERS” LOB (“FIC_CIRCUL”) STORE AS (TABLESPACE “USERS”
REM ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE LOGGING
REM STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM … 0 rows

> Remove the REM comments
> Change the owner USER1 by the new owner USER2
> Change the tablespace USERS by the TOUSER’s tablespace USER2 in the whole statement.

step3. Run the script create_lob_table.sql to create the USER2.TEST table.

step4. Import the data only in the created table, ignoring the CREATE TABLE statement failure.

$imp user2/*** FILE=exp.dmp LOG=imp_tab.log FROMUSER=user1 TOUSER=user2 TABLES=test IGNORE=y

Bypassing syscheck when install oracle 10g on Redhat EL5

Filed under: [Installation] — zhefeng @ 1:17 pm

When you do oracle 10g installation on Redhat EL5, you may failed to pass the syscheck because 10g is not certified on EL5. The eaiest way to bypass it is adding a switch:

$runInstaller -ignoreSysPrereqs

Error when Execute Sqlplus as non-Oracle User on UNIX

Filed under: [client related] — zhefeng @ 1:10 pm

When execute ‘sqlplus’ as a user outside of the dba or Oracle group, you get the following errors.

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

According to Metalink, this is a bug for oracle 10g by setting wrong file/folder permissions.

Solution:

I.  Logged in as the Oracle user (or the user that installed the 10gR2 software), manually change the permissions on the client.  For example:

chmod -R 755 <client_home>

In our case:

chmod -R 755 $ORACLE_HOME/sqlplus

II.  If doing a recursive permissions command is not acceptable, then you will need to pinpoint exactly what files the client is reading at the time of execution, and manually change permissions only on those files.  In our case, we need to pinpoint what files are being accessed by SQL*Plus.   To implement this workaround, please execute the following steps:

1. As the non-Oracle user, run the truss utility to find out which files are being accessed.  Sample command:
truss -aefo /tmp/truss_sqlplus.out sqlplus username/password

2. Use this truss_sqlplus.out trace file to see what files have error “EACCES” when attempting to access.   In our case, the truss_sqlplus.out showed a problem accessing the following file:
$ORACLE_HOME/sqlplus/mesg/sp1us.msb

Another possible error to search for in the truss output is ENOENT.  For example:
9775: open(“./sqlplus/mesg/sp1us.msb”, O_RDONLY) Err#2 ENOENT

3. Logged in as the Oracle user, change permissions on folders leading up to, and including sp1us.msb:

chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 755 $ORACLE_HOME/sqlplus/mesg/sp1us.msb

4. After making above permission changes, a different error may appear when executing sqlplus as non-Oracle user, such as:

$ sqlplus username/password
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
$

5. At this point, you need to re-run the truss (as non-Oracle) to see what other files are trying to be accessed.  In our case, the following files were trying to get accessed, but showed “EACCES” failure:

$ORACLE_HOME/nls/data/lx1boot.nlb
$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

6. Logged in as the Oracle user, change permissions on these files and the directories leading up to these files.

chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 755 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 755 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

7. Now, invoking sqlplus as a non-Oracle user was successful in our case.


Create a free website or blog at WordPress.com.