Row compression is used to minimize storage at the row level by compressing fixed-length data types.
Page compression is used to minimize storage for redundant data stored in pages.
Wednesday, December 21, 2011
Thursday, December 8, 2011
Wednesday, December 7, 2011
Thursday, December 1, 2011
XML Query in SQL Server
DECLARE @prodList xml =''
'';
WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products')
SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./ @Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products')
SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./ @Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
Transactions statements in SQL Server
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.
Tuesday, November 29, 2011
Isolation Level of SSIS
ReadUncommited: Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.
Chaos: Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
ReadCommitted: Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction.
RepeatableRead: Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.
Serializable: Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.
Snapshot: The data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read.
Chaos: Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
ReadCommitted: Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction.
RepeatableRead: Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.
Serializable: Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.
Snapshot: The data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read.
Thursday, November 24, 2011
CUBE, ROLLUP, GROUPING SETS
Grouping Sets in SQL Server 2008
http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
Aggregation WITH ROLLUP
http://blogs.msdn.com/b/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
Aggregation WITH CUBE
http://blogs.msdn.com/b/craigfr/archive/2007/09/27/aggregation-with-cube.aspx
http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
Aggregation WITH ROLLUP
http://blogs.msdn.com/b/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
Aggregation WITH CUBE
http://blogs.msdn.com/b/craigfr/archive/2007/09/27/aggregation-with-cube.aspx
Thursday, November 3, 2011
SQL Server Error logs
11/03/2011 05:43:44,spid9s,Unknown,
SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on
file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb2.ndf] in database [tempdb] (2).
The OS file handle is 0x0000000000001358. The offset of the latest long I/O is: 0x0000002e040000
SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on
file [S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb2.ndf] in database [tempdb] (2).
The OS file handle is 0x0000000000001358. The offset of the latest long I/O is: 0x0000002e040000
Wednesday, November 2, 2011
About SSMS of SQL Server
SSMS 2005 can NOT connect to SSIS 2008
SSMS 2008 can NOT connect to SSIS 2005
SSMS 2008 can NOT connect to SSIS 2005
Tuesday, September 13, 2011
Restore Transaction Log Backup in SQL Server 2005
DECLARE @i Int
DECLARE @String NVARCHAR(1000)
DECLARE @String1 NVARCHAR(1000)
Declare @str NVarchar(1000)
SET NOCOUNT ON
set @i=1100
While @i<=1119
begin
set @String1='RESTORE LOG [Hermes7] FROM DISK = N''D:\Temp\Hermes\Corporate_Hermes_backup_201109'
Set @string='30.trn'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 '
set @Str=@string1+Cast(@i as Nchar(4))+@string
set @i=@i+1
print @Str
EXECUTE sp_executesql @Str
end
DECLARE @String NVARCHAR(1000)
DECLARE @String1 NVARCHAR(1000)
Declare @str NVarchar(1000)
SET NOCOUNT ON
set @i=1100
While @i<=1119
begin
set @String1='RESTORE LOG [Hermes7] FROM DISK = N''D:\Temp\Hermes\Corporate_Hermes_backup_201109'
Set @string='30.trn'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 '
set @Str=@string1+Cast(@i as Nchar(4))+@string
set @i=@i+1
print @Str
EXECUTE sp_executesql @Str
end
Thursday, September 8, 2011
Vi
Skip Navigation
University of Washington
Search | Directories | Reference Tools
UW Home > Discover UW > IT Connect > Unix
How to Use the vi Editor*
*Copyright 1991 by Simon Fraser University. Reprinted with permission.
The vi editor is available on almost all Unix systems. vi can be used from any type of terminal because it does not depend on arrow keys and function keys--it uses the standard alphabetic keys for commands.
vi (pronounced "vee-eye") is short for "vi"sual editor. It displays a window into the file being edited that shows 24 lines of text. vi is a text editor, not a "what you see is what you get" word processor. vi lets you add, change, and delete text, but does not provide such formatting capabilities as centering lines or indenting paragraphs.
This help note explains the basics of vi:
opening and closing a file
moving around in a file
elementary editing
vi has many other commands and options not described here. The following resources can help you get started using the vi editor, and are available at the UW University Book Store:
"vi Tutorial." Specialized Systems Consultants (SSC).
"vi Reference." Specialized Systems Consultants (SSC).
"Learning the vi Editor." Linda Lamb, 1990.
Starting vi
You may use vi to open an already existing file by typing
vi filename
where "filename" is the name of the existing file. If the file is not in your current directory, you must use the full pathname.
Or you may create a new file by typing
vi newname
where "newname" is the name you wish to give the new file.
To open a new file called "testvi," enter
vi testvi
On-screen, you will see blank lines, each with a tilde (~) at the left, and a line at the bottom giving the name and status of the new file:
~
"testvi" [New file]
vi Modes
vi has two modes:
command mode
insert mode
In command mode, the letters of the keyboard perform editing functions (like moving the cursor, deleting text, etc.). To enter command mode, press the escape key.
In insert mode, the letters you type form words and sentences. Unlike many word processors, vi starts up in command mode.
Entering Text
In order to begin entering text in this empty file, you must change from command mode to insert mode. To do this, type
i
Nothing appears to change, but you are now in insert mode and can begin typing text. In general, vi's commands do not display on the screen and do not require the Return key to be pressed.
Type a few short lines and press at the end of each line. If you type a long line, you will notice the vi does not word wrap, it merely breaks the line unceremoniously at the edge of the screen.
If you make a mistake, pressing or may remove the error, depending on your terminal type.
Moving the Cursor
To move the cursor to another position, you must be in command mode. If you have just finished typing text, you are still in insert mode. Go back to command mode by pressing. If you are not sure which mode you are in, press once or twice until you hear a beep. When you hear the beep, you are in command mode.
The cursor is controlled with four keys: h, j, k, l.
Key Cursor Movement
--- ---------------
h left one space
j down one line
k up one line
l right one space
When you have gone as far as possible in one direction, the cursor stops moving and you hear a beep. For example, you cannot use l to move right and wrap around to the next line, you must use j to move down a line. See the section entitled "Moving Around in a File" for ways to move more quickly through a file.
Basic Editing
Editing commands require that you be command mode. Many of the editing commands have a different function depending on whether they are typed as upper- or lowercase. Often, editing commands can be preceded by a number to indicate a repetition of the command.
Deleting Characters
To delete a character from a file, move the cursor until it is on the incorrect letter, then type
x
The character under the cursor disappears. To remove four characters (the one under the cursor and the next three) type
4x
To delete the character before the cursor, type
X (uppercase)
Deleting Words
To delete a word, move the cursor to the first letter of the word, and type
dw
This command deletes the word and the space following it.
To delete three words type
3dw
Deleting Lines
To delete a whole line, type
dd
The cursor does not have to be at the beginning of the line. Typing dd deletes the entire line containing the cursor and places the cursor at the start of the next line. To delete two lines, type
2dd
To delete from the cursor position to the end of the line, type
D (uppercase)
Replacing Characters
To replace one character with another:
Move the cursor to the character to be replaced.
Type r
Type the replacement character.
The new character will appear, and you will still be in command mode.
Replacing Words
To replace one word with another, move to the start of the incorrect word and type
cw
The last letter of the word to be replaced will turn into a $. You are now in insert mode and may type the replacement. The new text does not need to be the same length as the original. Press to get back to command mode. To replace three words, type
3cw
Replacing Lines
To change text from the cursor position to the end of the line:
Type C (uppercase).
Type the replacement text.
Press.
Inserting Text
To insert text in a line:
Position the cursor where the new text should go.
Type i
Enter the new text.
The text is inserted BEFORE the cursor.
4. Press to get back to command mode.
Appending Text
To add text to the end of a line:
Position the cursor on the last letter of the line.
Type a
Enter the new text.
This adds text AFTER the cursor.
4. Press to get back to command mode.
Opening a Blank Line
To insert a blank line below the current line, type
(lowercase)
To insert a blank line above the current line, type
O (uppercase)
Joining Lines
To join two lines together:
Put the cursor on the first line to be joined.
Type J
To join three lines together:
Put the cursor on the first line to be joined.
Type 3J
Undoing
To undo your most recent edit, type
u
To undo all the edits on a single line, type
U (uppercase)
Undoing all edits on a single line only works as long as the cursor stays on that line. Once you move the cursor off a line, you cannot use U to restore the line.
Moving Around in a File
There are shortcuts to move more quickly though a file. All these work in command mode.
Key Movement
--- --------
w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
f scroll forward one screen
b scroll backward one screen
d scroll down one-half screen
u scroll up one-half screen
Moving by Searching
To move quickly by searching for text, while in command mode:
Type / (slash).
Enter the text to search for.
Press.
The cursor moves to the first occurrence of that text.
To repeat the search in a forward direction, type
n
To repeat the search in a backward direction, type
N
Closing and Saving a File
With vi, you edit a copy of the file, rather than the original file. Changes are made to the original only when you save your edits.
To save the file and quit vi, type
ZZ
The vi editor editor is built on an earler Unix text editor called ex. ex commands can be used within vi. ex commands begin with a : (colon) and end with a. The command is displayed on the status line as you type. Some ex commands are useful when saving and closing files.
To save the edits you have made, but leave vi running and your file open:
Press.
Type :w
Press.
To quit vi, and discard any changes your have made since last saving:
Press.
Type :q!
Press.
Command Summary
STARTING vi
vi filename edit a file named "filename"
vi newfile create a new file named "newfile"
ENTERING TEXT
i insert text left of cursor
a append text right of cursor
MOVING THE CURSOR
h left one space
j down one line
k up one line
l right one space
BASIC EDITING
x delete character
nx delete n characters
X delete character before cursor
dw delete word
ndw delete n words
dd delete line
ndd delete n lines
D delete characters from cursor to end of line
r replace character under cursor
cw replace a word
ncw replace n words
C change text from cursor to end of line
o insert blank line below cursor
(ready for insertion)
O insert blank line above cursor
(ready for insertion)
J join succeeding line to current cursor line
nJ join n succeeding lines to current cursor line
u undo last change
U restore current line
MOVING AROUND IN A FILE
w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
f scroll forward one screen
b scroll backward one screen
d scroll down one-half screen
u scroll up one-half screen
n repeat last search in same direction
N repeat last search in opposite direction
CLOSING AND SAVING A FILE
ZZ save file and then quit
:w save file
:q! discard changes and quit file
UW Logo
UW Information Technology
UW Information Technology
help@uw.edu
Modified: October 3, 2007
Privacy Terms
University of Washington
Search | Directories | Reference Tools
UW Home > Discover UW > IT Connect > Unix
How to Use the vi Editor*
*Copyright 1991 by Simon Fraser University. Reprinted with permission.
The vi editor is available on almost all Unix systems. vi can be used from any type of terminal because it does not depend on arrow keys and function keys--it uses the standard alphabetic keys for commands.
vi (pronounced "vee-eye") is short for "vi"sual editor. It displays a window into the file being edited that shows 24 lines of text. vi is a text editor, not a "what you see is what you get" word processor. vi lets you add, change, and delete text, but does not provide such formatting capabilities as centering lines or indenting paragraphs.
This help note explains the basics of vi:
opening and closing a file
moving around in a file
elementary editing
vi has many other commands and options not described here. The following resources can help you get started using the vi editor, and are available at the UW University Book Store:
"vi Tutorial." Specialized Systems Consultants (SSC).
"vi Reference." Specialized Systems Consultants (SSC).
"Learning the vi Editor." Linda Lamb, 1990.
Starting vi
You may use vi to open an already existing file by typing
vi filename
where "filename" is the name of the existing file. If the file is not in your current directory, you must use the full pathname.
Or you may create a new file by typing
vi newname
where "newname" is the name you wish to give the new file.
To open a new file called "testvi," enter
vi testvi
On-screen, you will see blank lines, each with a tilde (~) at the left, and a line at the bottom giving the name and status of the new file:
~
"testvi" [New file]
vi Modes
vi has two modes:
command mode
insert mode
In command mode, the letters of the keyboard perform editing functions (like moving the cursor, deleting text, etc.). To enter command mode, press the escape
In insert mode, the letters you type form words and sentences. Unlike many word processors, vi starts up in command mode.
Entering Text
In order to begin entering text in this empty file, you must change from command mode to insert mode. To do this, type
i
Nothing appears to change, but you are now in insert mode and can begin typing text. In general, vi's commands do not display on the screen and do not require the Return key to be pressed.
Type a few short lines and press
If you make a mistake, pressing
Moving the Cursor
To move the cursor to another position, you must be in command mode. If you have just finished typing text, you are still in insert mode. Go back to command mode by pressing
The cursor is controlled with four keys: h, j, k, l.
Key Cursor Movement
--- ---------------
h left one space
j down one line
k up one line
l right one space
When you have gone as far as possible in one direction, the cursor stops moving and you hear a beep. For example, you cannot use l to move right and wrap around to the next line, you must use j to move down a line. See the section entitled "Moving Around in a File" for ways to move more quickly through a file.
Basic Editing
Editing commands require that you be command mode. Many of the editing commands have a different function depending on whether they are typed as upper- or lowercase. Often, editing commands can be preceded by a number to indicate a repetition of the command.
Deleting Characters
To delete a character from a file, move the cursor until it is on the incorrect letter, then type
x
The character under the cursor disappears. To remove four characters (the one under the cursor and the next three) type
4x
To delete the character before the cursor, type
X (uppercase)
Deleting Words
To delete a word, move the cursor to the first letter of the word, and type
dw
This command deletes the word and the space following it.
To delete three words type
3dw
Deleting Lines
To delete a whole line, type
dd
The cursor does not have to be at the beginning of the line. Typing dd deletes the entire line containing the cursor and places the cursor at the start of the next line. To delete two lines, type
2dd
To delete from the cursor position to the end of the line, type
D (uppercase)
Replacing Characters
To replace one character with another:
Move the cursor to the character to be replaced.
Type r
Type the replacement character.
The new character will appear, and you will still be in command mode.
Replacing Words
To replace one word with another, move to the start of the incorrect word and type
cw
The last letter of the word to be replaced will turn into a $. You are now in insert mode and may type the replacement. The new text does not need to be the same length as the original. Press
3cw
Replacing Lines
To change text from the cursor position to the end of the line:
Type C (uppercase).
Type the replacement text.
Press
Inserting Text
To insert text in a line:
Position the cursor where the new text should go.
Type i
Enter the new text.
The text is inserted BEFORE the cursor.
4. Press
Appending Text
To add text to the end of a line:
Position the cursor on the last letter of the line.
Type a
Enter the new text.
This adds text AFTER the cursor.
4. Press
Opening a Blank Line
To insert a blank line below the current line, type
(lowercase)
To insert a blank line above the current line, type
O (uppercase)
Joining Lines
To join two lines together:
Put the cursor on the first line to be joined.
Type J
To join three lines together:
Put the cursor on the first line to be joined.
Type 3J
Undoing
To undo your most recent edit, type
u
To undo all the edits on a single line, type
U (uppercase)
Undoing all edits on a single line only works as long as the cursor stays on that line. Once you move the cursor off a line, you cannot use U to restore the line.
Moving Around in a File
There are shortcuts to move more quickly though a file. All these work in command mode.
Key Movement
--- --------
w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
Moving by Searching
To move quickly by searching for text, while in command mode:
Type / (slash).
Enter the text to search for.
Press
The cursor moves to the first occurrence of that text.
To repeat the search in a forward direction, type
n
To repeat the search in a backward direction, type
N
Closing and Saving a File
With vi, you edit a copy of the file, rather than the original file. Changes are made to the original only when you save your edits.
To save the file and quit vi, type
ZZ
The vi editor editor is built on an earler Unix text editor called ex. ex commands can be used within vi. ex commands begin with a : (colon) and end with a
To save the edits you have made, but leave vi running and your file open:
Press
Type :w
Press
To quit vi, and discard any changes your have made since last saving:
Press
Type :q!
Press
Command Summary
STARTING vi
vi filename edit a file named "filename"
vi newfile create a new file named "newfile"
ENTERING TEXT
i insert text left of cursor
a append text right of cursor
MOVING THE CURSOR
h left one space
j down one line
k up one line
l right one space
BASIC EDITING
x delete character
nx delete n characters
X delete character before cursor
dw delete word
ndw delete n words
dd delete line
ndd delete n lines
D delete characters from cursor to end of line
r replace character under cursor
cw replace a word
ncw replace n words
C change text from cursor to end of line
o insert blank line below cursor
(ready for insertion)
O insert blank line above cursor
(ready for insertion)
J join succeeding line to current cursor line
nJ join n succeeding lines to current cursor line
u undo last change
U restore current line
MOVING AROUND IN A FILE
w forward word by word
b backward word by word
$ to end of line
0 (zero) to beginning of line
H to top line of screen
M to middle line of screen
L to last line of screen
G to last line of file
1G to first line of file
n repeat last search in same direction
N repeat last search in opposite direction
CLOSING AND SAVING A FILE
ZZ save file and then quit
:w save file
:q! discard changes and quit file
UW Logo
UW Information Technology
UW Information Technology
help@uw.edu
Modified: October 3, 2007
Privacy Terms
For each in SQL Server
Exec sp_MSforeachdb 'Select ''[?]'',* From ?..sysobjects where name like ''form%'''
Monday, August 22, 2011
Solaris 10 Certificates
Oracle Solaris 10 System Administrator
1Z0-877
Oracle Solaris 10 System Administrator Certified Professional Exam, Part I (SA-200-S10)
1Z0-878
Oracle Solaris 10 System Administrator Certified Professional Exam, Part II (SA-202-S10)
Oracle Certified Expert, Oracle Solaris 10 Security Administrator
1Z0-881
Oracle Solaris 10 Security Administrator Certified Expert Exam
Oracle Certified Expert, Oracle Solaris 10 Network Administrator
1Z0-880
Oracle Solaris 10 Network Administrator Certified Expert (SA-300-S10)
Solaris 10 ZFS Administration (SA-2290-S10)
+ Oracle Solaris 10 System Administrator
Friday, August 12, 2011
Solaris10 + Oracle 11gR2 64bit ---dbshut script
#!/bin/sh
#
# $Id: dbshut.sh 22-may-2008.05:19:31 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#
###################################
#
# usage: dbshut $ORACLE_HOME
#
# This script is used to shutdown ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will shutdown all databases listed in the oratab file
# whose third field is a "Y" or "W". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
# Oracle Net Listener is also shutdown using this script.
#
# The progress log for each instance shutdown is logged in file
# $ORACLE_HOME/shutdown.log.
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be shutdown
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
#####################################
trap 'exit' 1 2 3
case $ORACLE_TRACE in
T) set -x ;;
esac
# Set path if path not set (if called from /etc/rc)
case $PATH in
"") PATH=/bin:/usr/bin:/etc
export PATH ;;
esac
# Save LD_LIBRARY_PATH
SAVE_LLP=$LD_LIBRARY_PATH
# The this to bring down Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log
# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER
# Stop Oracle Net Listener
if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
else
echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi
# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi
# Stops an instance
stopinst() {
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH
# add for bug 652997
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi
case $VERSION in
"6") sqldba command=shutdown ;;
"internal") $SQLDBA <
shutdown immediate
EOF
;;
*) $SQLDBA <
shutdown immediate
quit
EOF
;;
esac
if test $? -eq 0 ; then
echo "${INST} \"${ORACLE_SID}\" shut down."
else
echo "${INST} \"${ORACLE_SID}\" not shut down."
fi
}
#
# Loop for every entry in oratab file and and try to shut down
# that ORACLE
#
# Following loop shuts down 'Database Instance[s]' with 'Y' entry
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop shuts down 'Database Instance[s]' with 'W' entry
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop shuts down 'ASM Instance[s]'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
# Entries whose last field is not Y or N are not DB entry, ignore them.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# $Id: dbshut.sh 22-may-2008.05:19:31 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#
###################################
#
# usage: dbshut $ORACLE_HOME
#
# This script is used to shutdown ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will shutdown all databases listed in the oratab file
# whose third field is a "Y" or "W". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
# Oracle Net Listener is also shutdown using this script.
#
# The progress log for each instance shutdown is logged in file
# $ORACLE_HOME/shutdown.log.
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be shutdown
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
#####################################
trap 'exit' 1 2 3
case $ORACLE_TRACE in
T) set -x ;;
esac
# Set path if path not set (if called from /etc/rc)
case $PATH in
"") PATH=/bin:/usr/bin:/etc
export PATH ;;
esac
# Save LD_LIBRARY_PATH
SAVE_LLP=$LD_LIBRARY_PATH
# The this to bring down Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log
# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER
# Stop Oracle Net Listener
if [ -f $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Stoping Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl stop >> $LOG 2>&1 &
else
echo "Failed to auto-stop Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi
# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi
# Stops an instance
stopinst() {
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH
# add for bug 652997
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi
case $VERSION in
"6") sqldba command=shutdown ;;
"internal") $SQLDBA <
shutdown immediate
EOF
;;
*) $SQLDBA <
shutdown immediate
quit
EOF
;;
esac
if test $? -eq 0 ; then
echo "${INST} \"${ORACLE_SID}\" shut down."
else
echo "${INST} \"${ORACLE_SID}\" not shut down."
fi
}
#
# Loop for every entry in oratab file and and try to shut down
# that ORACLE
#
# Following loop shuts down 'Database Instance[s]' with 'Y' entry
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop shuts down 'Database Instance[s]' with 'W' entry
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y' or 'W'
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop shuts down 'ASM Instance[s]'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# NULL SID - ignore
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
# Entries whose last field is not Y or N are not DB entry, ignore them.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
LOG=$ORACLE_HOME/shutdown.log
echo "Processing $INST \"$ORACLE_SID\": log file $LOG"
stopinst >> $LOG 2>&1
fi
fi
;;
esac
done
Solaris10 + Oracle 11gR2 64bit ---dbstart script
#!/bin/sh
#
# $Id: dbstart.sh 22-may-2008.05:05:45 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#
###################################
#
# usage: dbstart $ORACLE_HOME
#
# This script is used to start ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will start all databases listed in the oratab file
# whose third field is a "Y". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# If ASM instances are to be started with this script, it cannot
# be used inside an rc*.d directory, and should be invoked from
# rc.local only. Otherwise, the CSS service may not be available
# yet, and this script will block init from completing the boot
# cycle.
#
# If you want dbstart to auto-start a single-instance database that uses
# an ASM server that is auto-started by CRS (this is the default behavior
# for an ASM cluster), you must change the database's ORATAB entry to use
# a third field of "W" and the ASM's ORATAB entry to use a third field of "N".
# These values specify that dbstart auto-starts the database only after
# the ASM instance is up and running.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
#
# The progress log for each instance bringup plus Error and Warning message[s]
# are logged in file $ORACLE_HOME/startup.log. The error messages related to
# instance bringup are also logged to syslog (system log module).
# The Listener log is located at $ORACLE_HOME_LISTNER/listener.log
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be started up
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
# Overall algorithm:
# 1) Bring up all ASM instances with 'Y' entry in status field in oratab entry
# 2) Bring up all Database instances with 'Y' entry in status field in
# oratab entry
# 3) If there are Database instances with 'W' entry in status field
# then
# iterate over all ASM instances (irrespective of 'Y' or 'N') AND
# wait for all of them to be started
# fi
# 4) Bring up all Database instances with 'W' entry in status field in
# oratab entry
#
#####################################
LOGMSG="logger -puser.alert "
trap 'exit' 1 2 3
# for script tracing
case $ORACLE_TRACE in
T) set -x ;;
esac
# Set path if path not set (if called from /etc/rc)
SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATH
SAVE_LLP=$LD_LIBRARY_PATH
# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log
# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER
# Start Oracle Net Listener
if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`
export VER10LIST
else
echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi
# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi
# Checks Version Mismatch between Listener and Database Instance.
# A version 10 listener is required for an Oracle Database 10g database.
# Previous versions of the listener are not supported for use with an Oracle
# Database 10g database. However, it is possible to use a version 10 listener
# with previous versions of the Oracle database.
checkversionmismatch() {
if [ $VER10LIST ] ; then
VER10INST=`sqlplus -V | grep "Release " | cut -d' ' -f3 | cut -d'.' -f1`
if [ $VER10LIST -lt $VER10INST ] ; then
$LOGMSG "Listener version $VER10LIST NOT supported with Database version $VER10INST"
$LOGMSG "Restart Oracle Net Listener using an alternate ORACLE_HOME_LISTNER:"
$LOGMSG "lsnrctl start"
fi
fi
}
# Starts a Database Instance
startinst() {
# Called programs use same database ID
export ORACLE_SID
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:${SAVE_PATH} ; export PATH
# add for bug # 652997
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${SAVE_LLP} ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
SPFILE1=${ORACLE_HOME}/dbs/spfile.ora
echo ""
echo "$0: Starting up database \"$ORACLE_SID\""
date
echo ""
checkversionmismatch
# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi
STATUS=1
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then
STATUS="-1"
fi
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.ora ] ; then
STATUS="-1"
fi
pmon=`ps -ef | grep -w "ora_pmon_$ORACLE_SID" | grep -v grep`
if [ "$pmon" != "" ] ; then
STATUS="-1"
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" already started."
fi
if [ $STATUS -eq -1 ] ; then
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" possibly left running when system went down (system crash?)."
$LOGMSG "Action: Notify Database Administrator."
case $VERSION in
"6") sqldba "command=shutdown abort" ;;
"internal") $SQLDBA $args <
shutdown abort
EOF
;;
*) $SQLDBA $args <
shutdown abort
quit
EOF
;;
esac
if [ $? -eq 0 ] ; then
STATUS=1
else
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi
if [ $STATUS -eq 1 ] ; then
if [ -e $SPFILE -o -e $SPFILE1 -o -e $PFILE ] ; then
case $VERSION in
"6") sqldba command=startup ;;
"internal") $SQLDBA <
startup
EOF
;;
*) $SQLDBA <
startup
quit
EOF
;;
esac
if [ $? -eq 0 ] ; then
echo ""
echo "$0: ${INST} \"${ORACLE_SID}\" warm started."
else
$LOGMSG ""
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
else
$LOGMSG ""
$LOGMSG "No init file found for ${INST} \"${ORACLE_SID}\"."
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi
}
# Starts an ASM Instance
startasminst() {
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# For ASM instances, we have a dependency on the CSS service.
# Wait here for it to become available before instance startup.
# Is the 10g install intact? Are all necessary binaries present?
if [ ! -x $ORACLE_HOME/bin/crsctl ]; then
$LOGMSG "$ORACLE_HOME/bin/crsctl not found when attempting to start"
$LOGMSG " ASM instance $ORACLE_SID."
else
COUNT=0
$ORACLE_HOME/bin/crsctl check css
RC=$?
while [ "$RC" != "0" ];
do
COUNT=`expr $COUNT + 1`
if [ $COUNT = 15 ] ; then
# 15 tries with 20 sec interval => 5 minutes timeout
$LOGMSG "Timed out waiting to start ASM instance $ORACLE_SID"
$LOGMSG " CSS service is NOT available."
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CSS service to be available before starting "
$LOGMSG " ASM instance $ORACLE_SID. Wait $COUNT."
sleep 20
$ORACLE_HOME/bin/crsctl check css
RC=$?
done
fi
startinst
}
# Start of dbstartup script
#
# Loop for every entry in oratab file and and try to start
# that ORACLE.
#
# ASM instances need to be started before 'Database instances'
# ASM instance is identified with '+' prefix in ORACLE_SID
# Following loop brings up ASM instance[s]
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If ASM instances
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startasminst >> $LOG 2>&1
fi
fi
;;
esac
done
# exit if there was any trouble bringing up ASM instance[s]
if [ "$?" != "0" ] ; then
exit 2
fi
#
# Following loop brings up 'Database instances'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If non-ASM instances
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop brings up 'Database instances' that have wait state 'W'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
W_ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
# DB instances with 'W' (wait state) have a dependency on ASM instances via CRS.
# Wait here for 'all' ASM instances to become available.
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
if [ -x $ORACLE_HOME/bin/srvctl ] ; then
COUNT=0
NODE=`olsnodes -l`
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
while [ "$RC" != "0" ]; # wait until this comes up!
do
COUNT=$((COUNT+1))
if [ $COUNT = 5 ] ; then
# 5 tries with 60 sec interval => 5 minutes timeout
$LOGMSG "Error: Timed out waiting on CRS to start ASM instance $ORACLE_SID"
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CRS service to start ASM instance $ORACLE_SID"
$LOGMSG "Wait $COUNT."
sleep 60
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
done
else
$LOGMSG "Error: \"${W_ORACLE_SID}\" has dependency on ASM instance \"${ORACLE_SID}\""
$LOGMSG "Error: Need $ORACLE_HOME/bin/srvctl to check this dependency"
fi
fi # asm instance
;;
esac
done # innner while
fi
;;
esac
done # outer while
# by now all the ASM instances have come up and we can proceed to bring up
# DB instance with 'W' wait status
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
INST="Database instance"
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started"
$LOGMSG "Error: incorrect usage: 'W' not allowed for ASM instances"
continue
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
;;
esac
done
#
# $Id: dbstart.sh 22-may-2008.05:05:45 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#
###################################
#
# usage: dbstart $ORACLE_HOME
#
# This script is used to start ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will start all databases listed in the oratab file
# whose third field is a "Y". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
#
# This script requires that ASM ORACLE_SID's start with a +, and
# that non-ASM instance ORACLE_SID's do not start with a +.
#
# If ASM instances are to be started with this script, it cannot
# be used inside an rc*.d directory, and should be invoked from
# rc.local only. Otherwise, the CSS service may not be available
# yet, and this script will block init from completing the boot
# cycle.
#
# If you want dbstart to auto-start a single-instance database that uses
# an ASM server that is auto-started by CRS (this is the default behavior
# for an ASM cluster), you must change the database's ORATAB entry to use
# a third field of "W" and the ASM's ORATAB entry to use a third field of "N".
# These values specify that dbstart auto-starts the database only after
# the ASM instance is up and running.
#
# Note:
# Use ORACLE_TRACE=T for tracing this script.
#
# The progress log for each instance bringup plus Error and Warning message[s]
# are logged in file $ORACLE_HOME/startup.log. The error messages related to
# instance bringup are also logged to syslog (system log module).
# The Listener log is located at $ORACLE_HOME_LISTNER/listener.log
#
# On Solaris
# ORATAB=/var/opt/oracle/oratab
#
# To configure, update ORATAB with Instances that need to be started up
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:
# An example entry:
# main:/usr/lib/oracle/emagent_10g:Y
#
# Overall algorithm:
# 1) Bring up all ASM instances with 'Y' entry in status field in oratab entry
# 2) Bring up all Database instances with 'Y' entry in status field in
# oratab entry
# 3) If there are Database instances with 'W' entry in status field
# then
# iterate over all ASM instances (irrespective of 'Y' or 'N') AND
# wait for all of them to be started
# fi
# 4) Bring up all Database instances with 'W' entry in status field in
# oratab entry
#
#####################################
LOGMSG="logger -puser.alert "
trap 'exit' 1 2 3
# for script tracing
case $ORACLE_TRACE in
T) set -x ;;
esac
# Set path if path not set (if called from /etc/rc)
SAVE_PATH=/bin:/usr/bin:/etc:${PATH} ; export PATH
SAVE_LLP=$LD_LIBRARY_PATH
# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
echo "Usage: $0 ORACLE_HOME"
else
LOG=$ORACLE_HOME_LISTNER/listener.log
# Set the ORACLE_HOME for the Oracle Net Listener, it gets reset to
# a different ORACLE_HOME for each entry in the oratab.
export ORACLE_HOME=$ORACLE_HOME_LISTNER
# Start Oracle Net Listener
if [ -x $ORACLE_HOME_LISTNER/bin/tnslsnr ] ; then
echo "$0: Starting Oracle Net Listener" >> $LOG 2>&1
$ORACLE_HOME_LISTNER/bin/lsnrctl start >> $LOG 2>&1 &
VER10LIST=`$ORACLE_HOME_LISTNER/bin/lsnrctl version | grep "LSNRCTL for " | cut -d' ' -f5 | cut -d'.' -f1`
export VER10LIST
else
echo "Failed to auto-start Oracle Net Listener using $ORACLE_HOME_LISTNER/bin/tnslsnr"
fi
fi
# Set this in accordance with the platform
ORATAB=/var/opt/oracle/oratab
if [ ! $ORATAB ] ; then
echo "$ORATAB not found"
exit 1;
fi
# Checks Version Mismatch between Listener and Database Instance.
# A version 10 listener is required for an Oracle Database 10g database.
# Previous versions of the listener are not supported for use with an Oracle
# Database 10g database. However, it is possible to use a version 10 listener
# with previous versions of the Oracle database.
checkversionmismatch() {
if [ $VER10LIST ] ; then
VER10INST=`sqlplus -V | grep "Release " | cut -d' ' -f3 | cut -d'.' -f1`
if [ $VER10LIST -lt $VER10INST ] ; then
$LOGMSG "Listener version $VER10LIST NOT supported with Database version $VER10INST"
$LOGMSG "Restart Oracle Net Listener using an alternate ORACLE_HOME_LISTNER:"
$LOGMSG "lsnrctl start"
fi
fi
}
# Starts a Database Instance
startinst() {
# Called programs use same database ID
export ORACLE_SID
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:${SAVE_PATH} ; export PATH
# add for bug # 652997
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${SAVE_LLP} ; export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
SPFILE1=${ORACLE_HOME}/dbs/spfile.ora
echo ""
echo "$0: Starting up database \"$ORACLE_SID\""
date
echo ""
checkversionmismatch
# See if it is a V6 or V7 database
VERSION=undef
if [ -f $ORACLE_HOME/bin/sqldba ] ; then
SQLDBA=sqldba
VERSION=`$ORACLE_HOME/bin/sqldba command=exit | awk '
/SQL\*DBA: (Release|Version)/ {split($3, V, ".") ;
print V[1]}'`
case $VERSION in
"6") ;;
*) VERSION="internal" ;;
esac
else
if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA=svrmgrl
VERSION="internal"
else
SQLDBA="sqlplus /nolog"
fi
fi
STATUS=1
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.dbf ] ; then
STATUS="-1"
fi
if [ -f $ORACLE_HOME/dbs/sgadef${ORACLE_SID}.ora ] ; then
STATUS="-1"
fi
pmon=`ps -ef | grep -w "ora_pmon_$ORACLE_SID" | grep -v grep`
if [ "$pmon" != "" ] ; then
STATUS="-1"
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" already started."
fi
if [ $STATUS -eq -1 ] ; then
$LOGMSG "Warning: ${INST} \"${ORACLE_SID}\" possibly left running when system went down (system crash?)."
$LOGMSG "Action: Notify Database Administrator."
case $VERSION in
"6") sqldba "command=shutdown abort" ;;
"internal") $SQLDBA $args <
shutdown abort
EOF
;;
*) $SQLDBA $args <
shutdown abort
quit
EOF
;;
esac
if [ $? -eq 0 ] ; then
STATUS=1
else
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi
if [ $STATUS -eq 1 ] ; then
if [ -e $SPFILE -o -e $SPFILE1 -o -e $PFILE ] ; then
case $VERSION in
"6") sqldba command=startup ;;
"internal") $SQLDBA <
startup
EOF
;;
*) $SQLDBA <
startup
quit
EOF
;;
esac
if [ $? -eq 0 ] ; then
echo ""
echo "$0: ${INST} \"${ORACLE_SID}\" warm started."
else
$LOGMSG ""
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
else
$LOGMSG ""
$LOGMSG "No init file found for ${INST} \"${ORACLE_SID}\"."
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started."
fi
fi
}
# Starts an ASM Instance
startasminst() {
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# For ASM instances, we have a dependency on the CSS service.
# Wait here for it to become available before instance startup.
# Is the 10g install intact? Are all necessary binaries present?
if [ ! -x $ORACLE_HOME/bin/crsctl ]; then
$LOGMSG "$ORACLE_HOME/bin/crsctl not found when attempting to start"
$LOGMSG " ASM instance $ORACLE_SID."
else
COUNT=0
$ORACLE_HOME/bin/crsctl check css
RC=$?
while [ "$RC" != "0" ];
do
COUNT=`expr $COUNT + 1`
if [ $COUNT = 15 ] ; then
# 15 tries with 20 sec interval => 5 minutes timeout
$LOGMSG "Timed out waiting to start ASM instance $ORACLE_SID"
$LOGMSG " CSS service is NOT available."
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CSS service to be available before starting "
$LOGMSG " ASM instance $ORACLE_SID. Wait $COUNT."
sleep 20
$ORACLE_HOME/bin/crsctl check css
RC=$?
done
fi
startinst
}
# Start of dbstartup script
#
# Loop for every entry in oratab file and and try to start
# that ORACLE.
#
# ASM instances need to be started before 'Database instances'
# ASM instance is identified with '+' prefix in ORACLE_SID
# Following loop brings up ASM instance[s]
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If ASM instances
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startasminst >> $LOG 2>&1
fi
fi
;;
esac
done
# exit if there was any trouble bringing up ASM instance[s]
if [ "$?" != "0" ] ; then
exit 2
fi
#
# Following loop brings up 'Database instances'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'Y'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "Y" ] ; then
# If non-ASM instances
if [ `echo $ORACLE_SID | cut -b 1` != '+' ]; then
INST="Database instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
fi
;;
esac
done
#
# Following loop brings up 'Database instances' that have wait state 'W'
#
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
W_ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
# DB instances with 'W' (wait state) have a dependency on ASM instances via CRS.
# Wait here for 'all' ASM instances to become available.
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
INST="ASM instance"
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
if [ -x $ORACLE_HOME/bin/srvctl ] ; then
COUNT=0
NODE=`olsnodes -l`
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
while [ "$RC" != "0" ]; # wait until this comes up!
do
COUNT=$((COUNT+1))
if [ $COUNT = 5 ] ; then
# 5 tries with 60 sec interval => 5 minutes timeout
$LOGMSG "Error: Timed out waiting on CRS to start ASM instance $ORACLE_SID"
exit $COUNT
fi
$LOGMSG "Waiting for Oracle CRS service to start ASM instance $ORACLE_SID"
$LOGMSG "Wait $COUNT."
sleep 60
RNODE=`srvctl status asm -n $NODE | grep "$ORACLE_SID is running"`
RC=$?
done
else
$LOGMSG "Error: \"${W_ORACLE_SID}\" has dependency on ASM instance \"${ORACLE_SID}\""
$LOGMSG "Error: Need $ORACLE_HOME/bin/srvctl to check this dependency"
fi
fi # asm instance
;;
esac
done # innner while
fi
;;
esac
done # outer while
# by now all the ASM instances have come up and we can proceed to bring up
# DB instance with 'W' wait status
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
# Proceed only if last field is 'W'.
if [ "`echo $LINE | awk -F: '{print $NF}' -`" = "W" ] ; then
INST="Database instance"
if [ `echo $ORACLE_SID | cut -b 1` = '+' ]; then
$LOGMSG "Error: ${INST} \"${ORACLE_SID}\" NOT started"
$LOGMSG "Error: incorrect usage: 'W' not allowed for ASM instances"
continue
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
# Called scripts use same home directory
export ORACLE_HOME
# file for logging script's output
LOG=$ORACLE_HOME/startup.log
touch $LOG
chmod a+r $LOG
echo "Processing $INST \"$ORACLE_SID\": log file $ORACLE_HOME/startup.log"
startinst >> $LOG 2>&1
fi
;;
esac
done
Thursday, August 11, 2011
Monday, August 8, 2011
Step by Step installing Oracle 11g R2 on Oracle Solaris 10
http://kamranagayev.wordpress.com/2011/03/27/step-by-step-installing-oracle-11gr2-on-oracle-solaris-10/
Thursday, August 4, 2011
Kill busy device for umount
# umount /dev/dsk/c0t2d0s7
The following command will display all processes and their associated users that are using files/directories on a specified disk on a Sun Solaris system:
# fuser -u /dev/dsk/c0t2d0s7
You can use the -k option to kill all processes using the specified file or filesystem
# fuser -k /dev/dsk/c0t2d0s7
The following command will display all processes and their associated users that are using files/directories on a specified disk on a Sun Solaris system:
# fuser -u /dev/dsk/c0t2d0s7
You can use the -k option to kill all processes using the specified file or filesystem
# fuser -k /dev/dsk/c0t2d0s7
Wednesday, August 3, 2011
Step by Step installing Oracle 11g R2 on Oracle Solaris 10
http://kamranagayev.wordpress.com/2011/03/27/step-by-step-installing-oracle-11gr2-on-oracle-solaris-10/
Thursday, July 28, 2011
Oracle ASM 10g R2
Tool of ASM in Windows NT Platform.
asmtoolg.exe
http://www.databasejournal.com/features/oracle/article.php/3571371/Oracle-10g-Automatic-Storage-Management-ASM-Part-2-Sample-Implementation.htm
asmtoolg.exe
http://www.databasejournal.com/features/oracle/article.php/3571371/Oracle-10g-Automatic-Storage-Management-ASM-Part-2-Sample-Implementation.htm
Tuesday, July 26, 2011
how to enable advanced compression option in Oracle 11g
1. Your database version is 11.1.0.6 or higher.
2. Your client software supports 11.1.0.6 or higher.
If the above two conditions are met you don't need SecureFiles, you don't need any init parameters, you just need the following:
CREATE TABLE regtab
AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'
CREATE TABLE comptab
COMPRESS AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');
SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
2. Your client software supports 11.1.0.6 or higher.
If the above two conditions are met you don't need SecureFiles, you don't need any init parameters, you just need the following:
CREATE TABLE regtab
AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'
CREATE TABLE comptab
COMPRESS AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
exec dbms_stats.gather_table_stats(USER, 'REGTAB');
exec dbms_stats.gather_table_stats(USER, 'COMPTAB');
SELECT table_name, blocks
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
Wednesday, July 13, 2011
SQL Reporting Services Error- Maximum request length exceeded
When trying to deploy a large report to your SSRS server, you may run into an error like this:
Error 2 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded.
You could, quite honestly, run into this error in a lot of situations involving a web app, but we're talking specifically about SSRS in this post.
The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept. Hence, it is throwing an error, and simply saying "no!"
It's an easy fix though! You've got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer
Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this
Now just add a max request length attribute in there to fix the problem, adjust your size as needed. This is 5meg.
And now you'll need to restart IIS. start->run->"iisreset"
Good luck!
Error 2 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded.
You could, quite honestly, run into this error in a lot of situations involving a web app, but we're talking specifically about SSRS in this post.
The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept. Hence, it is throwing an error, and simply saying "no!"
It's an easy fix though! You've got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer
Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this
Now just add a max request length attribute in there to fix the problem, adjust your size as needed. This is 5meg.
And now you'll need to restart IIS. start->run->"iisreset"
Good luck!
Tuesday, July 5, 2011
Disable/Enable all scheduled jobs in SQL Server 2005
Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs
WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 0 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END
Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs
WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 1 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs
WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 0 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END
Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs
WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 1 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END
Wednesday, June 8, 2011
Package0
The asynchronous_file_target holds the raw format Event data in a proprietary binary file format that persists beyond server restarts and can be provided to another person via ftp or email for remote disconnected analysis of the events.
The bucketizer performs grouping of Events as they are processed by the target into buckets based on the Event data and the Targets configuration. There are two bucketizer targets in Extended Events; a synchronous_bucketizer and an asynchronous_bucketizer.
The bucketizer performs grouping of Events as they are processed by the target into buckets based on the Event data and the Targets configuration. There are two bucketizer targets in Extended Events; a synchronous_bucketizer and an asynchronous_bucketizer.
Wednesday, May 11, 2011
Dirty Pages
Clean pages can be fl ushed from cache using dbcc dropcleanbuffers, which can be handy when you’re troubleshooting development and test environments because it forces subsequent reads to be fulfi lled from disk, rather than cache, but doesn’t touch any dirty pages.
You can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:
SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC
You can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:
SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC
Buffer Pool
The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV.
For example, the following query will return the amount of data cache usage in MB per database:
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure
MSSQL$:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool.
MSSQL$:Buffer Manager\Page Life Expectancy: This is the amount of time, in
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.
For example, the following query will return the amount of data cache usage in MB per database:
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure
MSSQL$
size of the buffer pool.
MSSQL$
size for the buffer pool.
MSSQL$
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.
Tuesday, April 12, 2011
open the firewall port for SQL Server on Windows Server 2008/R2
How to: Configure a Windows Firewall for Integration Services
http://msdn.microsoft.com/en-us/library/ms141198.aspx
How do I open the firewall port for SQL Server on Windows Server 2008?
http://support.microsoft.com/kb/968872
http://msdn.microsoft.com/en-us/library/ms141198.aspx
How do I open the firewall port for SQL Server on Windows Server 2008?
http://support.microsoft.com/kb/968872
Thursday, March 31, 2011
Wednesday, March 30, 2011
Seven tiers of disaster recovery
1 Tier 0: No off-site data – Possibly no recovery
2 Tier 1: Data backup with no hot site
3 Tier 2: Data backup with a hot site
4 Tier 3: Electronic vaulting
5 Tier 4: Point-in-time copies
6 Tier 5: Transaction integrity
7 Tier 6: Zero or near-Zero data loss
8 Tier 7: Highly automated, business integrated solution
http://en.wikipedia.org/wiki/Seven_tiers_of_disaster_recovery
Tuesday, March 22, 2011
user's permissions in SQL Server 2005
Example - list current login user's permissions
select object_name(major_id) as object_name,permission_name,state_desc
from sys.database_permissions
where major_id >0 and grantee_principal_id !=0
Example - SQL Server Instance Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions(NULL, 'SERVER');
GO
Example - Database Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions('AdventureWorks', 'DATABASE');
GO
Example - Table Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO
select object_name(major_id) as object_name,permission_name,state_desc
from sys.database_permissions
where major_id >0 and grantee_principal_id !=0
Example - SQL Server Instance Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions(NULL, 'SERVER');
GO
Example - Database Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions('AdventureWorks', 'DATABASE');
GO
Example - Table Rights
USE AdventureWorks;
SELECT *
FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO
Thursday, March 10, 2011
Reporting Services Scripter for SQL Server 2000/2005/2008
http://www.sqldbatips.com/showarticle.asp?ID=62
Tuesday, March 8, 2011
Limit Concurrent Database Connections by SQL Server User Account
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'tfs4dba' FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'tfs4dba' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'tfs4dba') > 3
ROLLBACK; END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'tfs4dba' FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'tfs4dba' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'tfs4dba') > 3
ROLLBACK; END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
Thursday, March 3, 2011
SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date
This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'uspUpdateEmployeeHireInfo'
GO
USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'uspUpdateEmployeeHireInfo'
GO
Tuesday, March 1, 2011
Oracle Database AL32UTF8
AL32UTF8 is the Oracle Database character set that is appropriate for
XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which
supports all valid XML characters.
Do not confuse Oracle Database database character set UTF8 (no hyphen) with
database character set AL32UTF8 or with character encoding UTF-8. Database
character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data.
UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML
characters. AL32UTF8 has no such limitation.
Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually "?")is substituted for it. This will terminate parsing and raise an exception.
XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which
supports all valid XML characters.
Do not confuse Oracle Database database character set UTF8 (no hyphen) with
database character set AL32UTF8 or with character encoding UTF-8. Database
character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data.
UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML
characters. AL32UTF8 has no such limitation.
Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually "?")is substituted for it. This will terminate parsing and raise an exception.
Tuesday, February 22, 2011
SQL Server [Version and Edition Upgrades ] And [Backward Compatibility]
SQL Server Version and Edition Upgrades
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx
SQL Server Backward Compatibility
http://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx
SQL Server Backward Compatibility
http://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx
Tuesday, February 15, 2011
Copy file using T-SQL in SQL Server 2005
sp_configure 'Ole Automation Procedures',1
go
reconfigure
DECLARE @hr int
DECLARE @ole_FileSystem int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
raiserror('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end
SET @source = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1'
SET @dest = 'D:\Backups\ErrorLogs\'+ Convert(varchar(16), Getdate(), 106)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4) +'ERRORLOG.txt'
EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile',null, @source, @dest
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
exec sp_OADestroy @ole_FileSystem
raiserror('Method Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end
go
reconfigure
DECLARE @hr int
DECLARE @ole_FileSystem int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
raiserror('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end
SET @source = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1'
SET @dest = 'D:\Backups\ErrorLogs\'+ Convert(varchar(16), Getdate(), 106)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4) +'ERRORLOG.txt'
EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile',null, @source, @dest
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
exec sp_OADestroy @ole_FileSystem
raiserror('Method Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end
Monday, February 14, 2011
Update SQL Server Agent Error Log location in SQL Server 2005
USE [msdb]
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'
GO
Monday, February 7, 2011
how to host php on IIS 7.5
http://technetnepal.net/blogs/gandip/archive/2010/02/21/how-to-host-php-on-iis-7-5.aspx
Backup SSAS Database as a SQL Agent job in SQL Server 2008
SQL Server Analysis Services Command ( SQL Server Agent)
Monday, January 24, 2011
install SQL SSRS 2005 on Windows 2008 R2 (Reporting Services Configuration Manager)
The Web Service identity's ASP. NET Service Account item is grey out.
So the identity of Classic .NET AppPool of IIS 7.5 should be LocalSystem User.
So the identity of Classic .NET AppPool of IIS 7.5 should be LocalSystem User.
Tuesday, January 18, 2011
How to: Install and Configure Reporting Services of SQL Server 2005 on Windows Server 2008
http://msdn.microsoft.com/en-us/library/bb839480%28SQL.90%29.aspx
Tuesday, January 11, 2011
Installing SQL Server 2005 SP3 on a Failover Cluster Instance
Note the following information before you install SQL Server 2005 SP3 on a failover cluster instance:
Do not stop the cluster service before you run the SP3 Setup program, or while the installation program is running.
Do not end any running processes before you run the SP3 Setup program.
Do not take the SQL Server service off line before you run the SP3 Setup program. The SP3 Setup program will stop and start the SQL Server service.
Run the SP3 Setup program on the primary node of the failover cluster instance.
You must restart all failover cluster nodes after SP3 is finished installing.
Rolling upgrade is not supported for SP3.
Failover Cluster Installation
The following information applies only to SQL Server 2005 components that are part of a failover cluster.
To install the service pack on a failover cluster:
To ensure that SQL Server 2005 SP3 will be installed on all nodes of a failover cluster instance, verify that the TASK Scheduler is running on all passive nodes of your failover cluster instance.
If any resources have been added that have dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SQL Server 2005 SP3. If you do not remove the dependencies, the installation of SP3 will take those resources offline.
Note:
When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.
Run the SQL Server 2005 SP3 executable package file from the active node that owns the group containing the failover cluster instance that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP3 from any passive nodes in the failover cluster.
On the Feature Selection page of the SQL Server 2005 SP3 Setup Wizard, select the failover cluster instance that you plan to upgrade. Setup will prompt you for the login credentials that are used to connect to other nodes in the cluster.
Note:
Keep all nodes of the cluster online during Setup. This ensures that the upgrade is applied to each cluster node.
If you removed dependencies or took resources offline in step 1, restore the dependencies, or bring the resources online.
Note:
Setup might require you to restart the failover cluster nodes. This restart replaces the files that were in use during Setup.
Do not stop the cluster service before you run the SP3 Setup program, or while the installation program is running.
Do not end any running processes before you run the SP3 Setup program.
Do not take the SQL Server service off line before you run the SP3 Setup program. The SP3 Setup program will stop and start the SQL Server service.
Run the SP3 Setup program on the primary node of the failover cluster instance.
You must restart all failover cluster nodes after SP3 is finished installing.
Rolling upgrade is not supported for SP3.
Failover Cluster Installation
The following information applies only to SQL Server 2005 components that are part of a failover cluster.
To install the service pack on a failover cluster:
To ensure that SQL Server 2005 SP3 will be installed on all nodes of a failover cluster instance, verify that the TASK Scheduler is running on all passive nodes of your failover cluster instance.
If any resources have been added that have dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SQL Server 2005 SP3. If you do not remove the dependencies, the installation of SP3 will take those resources offline.
Note:
When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.
Run the SQL Server 2005 SP3 executable package file from the active node that owns the group containing the failover cluster instance that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP3 from any passive nodes in the failover cluster.
On the Feature Selection page of the SQL Server 2005 SP3 Setup Wizard, select the failover cluster instance that you plan to upgrade. Setup will prompt you for the login credentials that are used to connect to other nodes in the cluster.
Note:
Keep all nodes of the cluster online during Setup. This ensures that the upgrade is applied to each cluster node.
If you removed dependencies or took resources offline in step 1, restore the dependencies, or bring the resources online.
Note:
Setup might require you to restart the failover cluster nodes. This restart replaces the files that were in use during Setup.
Thursday, January 6, 2011
Replications of SQL Server
Snapshot Replication
This form of replication is appropriate for small data sets, infrequent
update periods (or for a one-time replication operation), or management simplicity.
Transactional Replication
Transactional replication can typically keep databases in sync within about five seconds of latency, depending on the underlying network infrastructure.
Merge Replication
Merge replication allows data to be modified by the subscribers and synchronized at a later time. This synchronization could be as soon as a few seconds, or it could be a day later.
Immediate Updating
Immediate updating allows a replication target to immediately modify data at the source.This task is accomplished by using a trigger to run a distributed transaction.
This form of replication is appropriate for small data sets, infrequent
update periods (or for a one-time replication operation), or management simplicity.
Transactional Replication
Transactional replication can typically keep databases in sync within about five seconds of latency, depending on the underlying network infrastructure.
Merge Replication
Merge replication allows data to be modified by the subscribers and synchronized at a later time. This synchronization could be as soon as a few seconds, or it could be a day later.
Immediate Updating
Immediate updating allows a replication target to immediately modify data at the source.This task is accomplished by using a trigger to run a distributed transaction.
Subscribe to:
Posts (Atom)