sp_cycle_errorlog

Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.

Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on. sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.

http://technet.microsoft.com/en-us/library/ms182512%28v=sql.100%29.aspx

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Got login errors in the SQL Server 2005 databases. The following link solves the issue.

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients.   In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem.  To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written.  An example of an entry is:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user ''. [CLIENT: ]

n

The key to the message is the 'State' which the server will accurately set to reflect the source of the problem.  In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password.  The common error states and their descriptions are provided in the following table:

 

ERROR STATE	ERROR DESCRIPTION
2 and 5	Invalid userid
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid password
11 and 12	Valid login but server access failure
13	SQL Server service paused
18	Change password required

ArcSDE Operating system authentication

Operating system authentication

Release 9.3

http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/operati1778508573.htm

Using Windows-authenticated users or groups in SQL Server

Release 9.3

http://webhelp.esri.com/arcgisserver/9.3/java/geodatabases/using_wi-29016506.htm

SQL Server Catalog Views

Link: http://msdn.microsoft.com/en-us/library/ms174365.aspx

The catalog views in SQL Server have been organized into the following categories:

AlwaysOn Availability Group Catalog Views	Linked Servers Catalog Views
Change Tracking Catalog Views	Messages (For Errors) Catalog Views
CLR Assembly Catalog Views	Object Catalog Views
Databases and Files Catalog Views	Partition Function Catalog Views
Database Mail Views	Policy-Based Management Views
Database Mirroring Catalog Views	Resource Governor Catalog Views
Data Collector Views	Scalar Types Catalog Views
Data Spaces	Schemas Catalog Views
Endpoints Catalog Views	Security Catalog Views
Extended Events Catalog Views	Service Broker Catalog Views
Extended Properties Catalog Views	Server-wide Configuration Catalog Views
FileTable Catalog Views	XML Schemas (XML Type System) Catalog Views
Full-Text Search and Semantic Search Catalog Views

ORA-16038 ORA-19809 ORA-00312

Got the error when starting an Oracle database:

SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size                  2045480 bytes
Variable Size             385878488 bytes
Database Buffers          603979776 bytes
Redo Buffers               18923520 bytes
Database mounted.

ORA-16038: log 3 sequence# 42 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u02/oradata/DB/redo03.log'

Solution: 

To solve ORA-19809 error, increase the value of db_recovery_file_dest_size parameter. And start the database:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=3G SCOPE=BOTH;

SQL> shutdown abort

SQL> startup

Server Performance and Activity Monitoring How-to Topics

SQL Server performance info:

http://msdn.microsoft.com/en-us/library/ms191511(v=sql.90).aspx

SQL Server SYNONYM

First introduced in SQL Server 2005.

SQL Server SYNONYM's can be very useful and can be created for

• Tables
• Views
• Assembly Stored Procedures, Table Valued Functions, Aggregations
• SQL Scalar Functions
• SQL Stored Procedures
• SQL Table Valued Functions
• SQL Inline-Table-Valued Functions
• Local and Global Temporary Tables
• Replication-filter-procedures
• Extended Stored Procedures

Benefits:

• SYNONYMs provide a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations:

• SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
• Obviously consumes possible object names, as you can not create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
• SYNONYM can not be referenced in a DDL statement

If we use this option frequently, then keping in mind the facts that:

• you can delete a SYNONYM without getting any warning that it is being referenced by any other database object
• the object for which the SYNONYM is being created is checked at run time. It is not checked at creation time.
• It makes it imperitive that we should plan and monitor synonyms bit more than other objects/references in SQL Server.
• An additional limitation is that you can't use TRUNCATE TABLE on a synonym.
• The SSIS Data Profiling task cannot see synonyms

SQL Server synonym links:
http://www.mssqltips.com/sqlservertip/1576/benefits-and-limitations-of-using-synonyms-in-sql-server-2005/
http://connect.microsoft.com/SQLServer/feedback/details/311079/expand-synonym-to-other-entities-database-linked-server
http://msdn.microsoft.com/en-us/library/ms187552.aspx

Remote Diagnostic Agent (RDA)

1. Remote Diagnostic Agent (RDA) is a command-line diagnostic tool that is executed by an engine written in the Perl programming language. RDA provides a unified package of support diagnostics tools and preventive solutions. The data captured provides Oracle Support with a comprehensive picture of the customer's environment which aids in problem diagnosis.

2. Oracle document: Remote Diagnostic Agent (RDA) 4 - Getting Started [ID 314422.1]

3. RDA supports different platforms: Windows, UNIX, Linux

4. RDA supports lots of Oracle products: Oracle RDBMS Server (Standard and Enterprise Editions), etc

5. RDA download: a zip file for specific platform

6. Installation on Solaris: unzip

7. run RDA
--check perl version: perl -V
--rda.sh - Use this command if Perl is not available.
--rda.pl - Use this command if Perl is available.
--./rda.pl -S: set up setup.cfg
--run RDA report: ./rda.pl -v
  --ask for password for system to connect to the database

Oracle changePerm.sh script in versions 9.2.0.8, Oracle 10g

changePerm.sh is a script that allows users that do not belong to the "dba" group to access and execute files in the ORACLE_HOME directory.


The script is located in the ORACLE_HOME/install directory on Unix and Linux systems (there is no equivalent for Windows). This script ships with Oracle Database versions 9.2.0.8, Oracle 10g.

The changePerm.sh file is no more available nor necessary for Oracle database server 11.1.0.x because in 11g, permissions under the Oracle Home (including "others") are set correctly/relaxed. (oracle note 834626.1)


===================================
Before change:
$ ls -ld $ORACLE_HOME/lib
drwxr-x---   3 oracle   dba         5632 Nov 27 09:30 /fs/oracle/product/10204ee/lib

Running script:

$ cd $ORACLE_HOME/install

$ ls -l

total 964

-rwxr-xr-x   1 oracle   dba        11380 Nov 22  2007 changePerm.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed1.sh

-rw-r-----   1 oracle   dba          618 Nov 26 14:19 envVars.properties

-rw-r-----   1 oracle   dba          131 Nov 26 13:51 envVars.properties.bak

-rwxr-xr-x   1 oracle   dba           38 Apr 19  2005 install.excl

drwxr-x---   2 oracle   dba          512 Nov 26 11:55 jlib

-rw-r-----   1 oracle   dba       437788 Nov 26 14:19 make.log

-rw-r-----   1 oracle   dba          102 Nov 26 14:19 portlist.ini

-rw-r--r--   1 oracle   dba          124 Nov 26 14:19 readme.txt

-rw-r--r--   1 oracle   dba         7609 Jan 12  2007 restrict.lst

-rwxr-xr-x   1 oracle   dba          822 Nov 26 14:16 rootdeletenode.sh

-rw-r--r--   1 oracle   dba        10031 Nov 26 14:16 rootlocaladd

-rw-r-----   1 oracle   dba            0 Jun  7  2005 seed.log

-rw-r--r--   1 oracle   dba         2808 Jul 14  2005 templocal

drwxr-x---   2 oracle   dba          512 Nov 26 14:19 unix

drwxr-x---   2 oracle   dba          512 Nov 26 14:13 utl

$ ./changePerm.sh

-------------------------------------------------------------------------------

Disclaimer: The purpose of this script is to relax permissions on some of the

files in the database Oracle Home so that all clients can access them.

Please note that Oracle Corporation recommends using the most restrictive file

permissions as possible for your given implementation.  Running this script

should be done only after considering all security ramifications.

-------------------------------------------------------------------------------

-n Do you wish to continue (y/n) [n]:

y

Spooling the error log /tmp/changePerm_err.log...

Finished running the script successfully

After change:

$ ls -ld $ORACLE_HOME/lib
drwxr-xr-x   3 oracle   dba         5632 Nov 27 09:40 /fs/oracle/product/10204ee/lib

===================================
Reference: http://www.orafaq.com/wiki/ChangePerm_sh

EXCEL tips

1. How to enter soft break in EXCEL
ALT+ENTER

2. How to fill an Excel column with consecutive numbers without typing each one separately?

Put 1 in the first row, first column.
Put 2 in the 2nd row, first column.
Highlight both. Then drag your cursor down. They will auto-number themselves.
or
Click and drag the first value that enter into the first cell.

Then go to Edit / Fill / series .. and click on "step value" and enter 1.

Oracle Database Automatic Startup and Shutdown on UNIX

Reference:
http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php

• Automating Shutdown and Startup (11.2)
• Automating Shutdown and Startup (10.2)
• Automating Startup and Shutdown (10.1)
• Automating Database Startup and Shutdown (9.2)

Oracle 10gR2 and RMAN duplicate and errors: ORA-19870, ORA-19505, ORA-27037

When coping backup files from target database server to auxiliary database server, then trying to duplicate a database on a new server using Oracle RMAN 10gR2, the following error occurred:

channel ORA_AUX_DISK_1: reading from backup piece /backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck

ORA-19870: error reading backup piece /backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck

ORA-19505: failed to identify file "/backup/DB/rman/DB_dbf_s2076_p1_t793477838_cold_disk.bck"

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

failover to previous backup

Solution: Add backup files to the Recovery Catalog

Cataloging Older Files in the Recovery Catalog

If you have datafile copies, backup pieces or archive logs on disk, you can catalog them in the recovery catalog using the CATALOG command. When using a recovery catalog, cataloging older backups that have aged out of the control file lets RMAN use the older backups during restore operations. 

For example:

RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';
RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', 
     '/disk1/arch_logs/archive1_732.dbf';
RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

You can also catalog multiple backup files in a directory at once, using the CATALOG START WITH command, as shown in this example:

RMAN> CATALOG START WITH '/disk1/backups/';

Reference: http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmrepos.htm#1006296

Principals - SQL Server

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

Windows-level principals

• Windows Domain Login
• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User
• Database Role
• Application Role

The SQL Server sa Login

---

The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.

public Database Role

---

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted topublic on that securable.

INFORMATION_SCHEMA and sys

Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and they cannot be modified or dropped.

Certificate-based SQL Server Logins

---

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

• ##MS_SQLResourceSigningCertificate##
• ##MS_SQLReplicationSigningCertificate##
• ##MS_SQLAuthenticatorCertificate##
• ##MS_AgentSigningCertificate##
• ##MS_PolicyEventProcessingLogin##
• ##MS_PolicySigningCertificate##
• ##MS_PolicyTsqlExecutionLogin##

Client and Database Server

---

By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.

For more information about the SQL Server implementation of Kerberos support, see Kerberos Authentication and SQL Server.

from: http://msdn.microsoft.com/en-us/library/ms181127(v=sql.105).aspx

BUILTIN\Administrator and SQL Server 2008

BUILTIN\Administrator

By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.

If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server. Accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group.

Refer to: http://msdn.microsoft.com/en-us/library/cc280562(v=sql.105).aspx

Permissions of SQL Server Fixed Database Roles

Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server. 

Fixed database roles are provided for convenience and backward compatibility. 

All fixed database roles are granted with Server-level permission VIEW ANY DATABASE.

Fixed database role	Database-level permission
db_accessadmin	Granted: ALTER ANY USER, CREATE SCHEMA Granted with GRANT option: CONNECT
db_backupoperator	Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader	Granted: SELECT
db_datawriter	Granted: DELETE, INSERT, UPDATE
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
db_denydatareader	Denied: SELECT
db_denydatawriter	Denied: DELETE, INSERT, UPDATE
db_owner	Granted with GRANT option: CONTROL
db_securityadmin	Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
dbm_monitor	Granted: VIEW most recent status in Database Mirroring Monitor Important The dbm_monitor fixed database role is created in the msdb database when the first database is registered in Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role.

For more info: http://msdn.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

Changing SQL Server Database Owner

Changing the SQL Server Database Owner

In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database.

Ownership of the system databases cannot be changed.

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals.

ALTER AUTHORIZATION ON DATABASE::[my_db] TO [sa];

Reference:

Changing the Database Owner: http://msdn.microsoft.com/en-us/library/ms190909(v=sql.105).aspx

ALTER AUTHORIZATION (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187359(v=sql.105).aspx

guest user should not be disabled in the msdb database in SQL Server

guest user should not be disabled in the msdb database in SQL Server

SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. This recommendation does not apply to master, msdb, and tempb system databases.

In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database.

More info: http://support.microsoft.com/kb/2539091

Viewing Database Metadata in SQL Server 2008 R2

You can view database, file, partition, and filegroup properties using a variety of catalog views, system functions, and system stored procedures.

The following table lists the catalog views, system functions, and system stored procedures that return information about databases, files, and filegroups.

Views	Functions	Stored procedures and other statements
sys.databases	DATABASE_PRINCIPAL_ID	sp_databases
sys.database_files	DATABASEPROPERTYEX	sp_helpdb
sys.data_spaces	DB_ID	sp_helpfile
sys.filegroups	DB_NAME	sp_helpfilegroup
sys.allocation_units	FILE_ID	sp_spaceused
sys.master_files	FILE_IDEX	DBCC SQLPERF
sys.partitions	FILE_NAME
sys.partition_functions	FILEGROUP_ID
sys.partition_parameters	FILEGROUP_NAME
sys.partition_range_values	FILEGROUPPROPERTY
sys.partition_schemes	FILEPROPERTY
sys.dm_db_partition_stats	fn_virtualfilestats
sys.dm_db_file_space_usage (Transact-SQL) (tempdb only)
sys.dm_db_session_space_usage (Transact-SQL)(tempdb only)
sys.dm_db_task_space_usage (Transact-SQL) (tempdb only)

Some columns in the sys.databases catalog view and properties in the DATABASEPROPERTYEX function may return a NULL value if the specified database is not available. For example, to return the collation name of a database, the database must be accessed. If the database is not online, or the AUTO_CLOSE option is set to ON, the collation name cannot be returned.

SQL Server Version Components and Numbering

SQL Server Version Components and Numbering

The standard product version format for SQL Server is MM.nn.bbbb.rr where each segment is defined as:

• MM - Major version
• nn - Minor version
• bbbb - Build number
• rr - Build revision number

In each major or minor release of SQL Server, there is an increment to the version number to differentiate it from earlier versions. This change to the version is used for many purposes. This includes displaying version information in the user interface, controlling how files are replaced during upgrade, applying service packs, and also as a mechanism for functional differentiation between the successive versions.

For example, SQL Server 2008 R2 is a minor upgrade of SQL Server 2008. This means that it shares the same major version number with SQL Server 2008, and has an increment to the minor version. 

The product version for SQL Server 2008 R2 is 10.50.bbbb.rr. Compare this with the product version for SQL Server 2008 which is 10.0.bbbb.rr.