i found a good chart to compare all sql server HA options (http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx):
November 19, 2008
Comparing SQL Server 2005 High Availability technologies
November 12, 2008
Mapping of Fixed Database Roles to Permissions
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
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
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?
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
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
