SQL SERVER SPECIALIST

Property-junction

2013-03-22T00:45:00.001-07:00

Check how much space used by Data file in Sql Server

2012-01-11T05:43:00.001-08:00

select
a.FILEID,
NAME = left(a.NAME,15),
[FILE_SIZE_MB] =
  convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
  convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
  convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
  FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a;

Check when last time server re-start

2012-01-11T05:42:00.001-08:00

SELECT
[ms_ticks] AS ms_since_restart,
[ms_ticks]/1000 AS seconds_since_restart,
CAST([ms_ticks]/1000/60.0 AS DECIMAL(15,2)) AS minutes_since_restart,
CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2)) AS hours_since_restart,
CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2)) AS days_since_restart,
DATEADD(s,((-1)*([ms_ticks]/1000)),GETDATE()) AS time_of_last_restart
FROM sys.[dm_os_sys_info];
=========================================================================
SELECT
DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()) AS last_SERVER_restart,
D.create_date,
(DATEDIFF(s, DATEADD(s,((-1)*(DOSI.[ms_ticks]/1000)),GETDATE()), D.create_date)) AS recovery_time_seconds
FROM sys.[dm_os_sys_info] DOSI
CROSS JOIN sys.[databases] D
WHERE D.[name] = 'tempdb';

How to fine out last DML operation on table in oracle

2011-08-18T21:12:00.001-07:00

SELECT * FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER='schemaname' AND TABLE_NAME='tablename'

How to fine dump file location for alert log of database

2011-08-18T21:10:00.001-07:00

sql> show parameter dump;

please select the bdump location and check the error in latest alter log

How much free space avilable in table spaces into Oracle

2011-08-18T21:08:00.001-07:00

How much free space avilable in table spaces

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Find out table space in auto extend mode or not

select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,status from dba_data_files order by tablespace_name;

How to change open cursor value in Pfile into oracle

2011-08-18T21:07:00.001-07:00

1. Create pfile=’/tmp/initcisora01.ora’ from spfile;

2. shutdown the database;

3. edit the open_cursors=400 in your pfile that is created on /tmp newly and save it.

4. open the database using newly created pfile i.e.

Startup pfile=’ /tmp/initcisora01.ora’;

5. check whether open_cursor is reflecting 400 now.

Show parameter open_cursors;

6. now create new spfile from newly created pfile.

Create spfile from pfile=’/tmp/initcisora01.ora’;

7. normal shutdown and normal startup the database;

How to find overall size of database and actual size of database in oracle

2011-08-18T21:05:00.001-07:00

SQL> select sum(bytes)/1024/1024/1024 "overall size in GB" from dba_data_files;

SQL> select sum(bytes)/1024/1024/1024 "used space size in GB" FROM dba_segments;

update statistics for all objects in one paticular schema into oracle 10 G

2011-08-18T21:02:00.000-07:00

Exec dbms_stats.gather_schema_stats(ownname=>'schemaname',cascade=>true,options=>'gather',degree=>4);

Most usableDMV in SQL SERVER 2008/2005

2011-08-04T02:06:00.001-07:00

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

sys.dm_exec_query_stats

sys.dm_os_wait_stats

sys.dm_db_index_usage_stats

sys.dm_db_index_operational_stats

sys.dm_db_missing_index_details

Please take a close look on all above dmv's

DB Link creation in Oracle

2011-08-02T04:58:00.001-07:00

1 TYPES OF DATABASE LINKS

The type of database links that oracle lets you create are private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database.

1.1 Private

A private link creates a link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.

The syntax for creating a private database link is:

CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>';

e.g.

CREATE DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';

If the above statement is created in scott schema of the local database then only scott user of the local database can use the database link.

1.2 Public

A public link creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

The syntax for creating a public database link is:

CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>';

e.g.

CREATE PUBLIC DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';

All the users in the database inclusive of the user scott can use the database link.

1.3 Global

A Global database link creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. To create Global database links we must ensure that:

§ Global database link names must match database global names

§ Your GLOBAL_NAMES initialization parameter should be set to TRUE

§ You must enable the plug and play setting for your listeners

The initialization parameter file should contain the following entries:

DB_NAME = <DB NAME>

DB_DOMAIN = <DOMAIN NAME>

GLOBAL_NAMES = TRUE

USE_PLUG_AND_PLAY_LISTENER = ON

CREATE DATABASE LINK <database link name> USING '<database name>';

2 Users of Database Links

When you create a database link, you determine which user should connect to the remote database to access the data. There are three different types of users and they are:

§ Connected user

§ Current user

§ Fixed user

2.1 Connected user

This link always connects as the currently connected user. This requires the user using the link to have an ID in both databases. When using a connected user link, your access is in the remote database is defined by the privileges of that ID in the remote database.

CREATE DATABASE LINK <database link name> USING '<remote database>';

e.g.

CREATE PUBLIC DATABASE LINK TEST USING 'BEADMISC';

2.2 Current user

A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link.

Current user links are an aspect of the Oracle Advanced Security option.

CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO CURRENT_USER using '<remote database>';

2.3 Fixed user

This is the normal database link were we provide the credential of the remote database when we create the database link.

CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>';

3 Privileges required

The privileges required to create, drop, alter database link are:

§ Create database link

§ Create public database link

§ Alter database link

§ Drop database link

§ Alter public database link

§ Drop public database link

4 Monitoring database links

The important views that can be used to monitor database links are:

§ ALL_DB_LINKS

§ DBA_DB_LINKS

§ USER_DB_LINKS

§ V$DBLINK

§ GV$DBLINK

The views ALL_DB_LINKS, DBA_DB_LINKS, USER_DB_LINKS lists all database links for the connected user, whole database and schema owner.

The views V$DBLINK and GV$DBLINK show the database link connections that are currently open in your current session:

4.1 Sample create database link

CREATE DATABASE LINK test_db_link CONNECT TO

eserv IDENTIFIED BY vre10g USING 'beadmisc';

4.2 To Check the database link being used

Step 1:

Access the remote database using the link:

select * from dba_db_links;

Step 2:

select * from v$dblink;

The output will be displayed as in the below screen shot.

Some of the important columns are:

DB_LINK	The name of the database link.
HETROGENEOUS	If yes that means the database link is pointing to a database other than oracle.
OPEN_CURSORS	Number of remote connections established or queries that are executed.
IN_TRANSACTION	If Y it means the transaction has not been committed.
LOGGED_ON	Is the current session open.

Step 3:

Issue a COMMIT;

Run the below query:

select db_link,in_transaction from v$dblink;

You will see that in_transaction column has value of NO.

5 Closing database links

If you access a database link in a session, then the link remains open until you close the session. For example:

§ If 5 users/ schemas access the same public link in a local database, then 5 database link connections are open are open in the remote database.

§ If 5 users/ schemas and each user access a private link, then 5 database link connections are open.

§ If one user accesses 5 different links, then 5 database link connections are open.

When you close a session, the links that were active in the session are automatically closed.

To close the database link manually please execute the below command.

ALTER SESSION CLOSE DATABASE LINK <database link name>;

Note that this statement only closes the links that are active in your current session and the user/schema must have the alter session privilege.

6 Drop database links

The statement to drop a database link is given below:

DROP DATABASE LINK <database link name>;

7 Limiting Active Database Link Connections

You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.

The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.

To view the OPEN_LINKS value executes the query below:

select * from v$parameter where upper(name) ='OPEN_LINKS'

The changes can be made to the init<db_name>.ora or can be changed dynamically by the DBAs.

8 Location Transparency

After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. To create local transparency we can use

§ Views

§ Synonyms

§ Procedures

Syntax using synonym:

CREATE synonym <synonym_name> FOR [schema.]object_name[@database_link_name];

e.g.

CREATE SYNONYM test_syn FOR scott.emp@db_link_test;

Similarly we can create synonyms for packages and procedures in the remote database and use them as our local copy.

9 Limitation of database links

The following operations cannot be used using database links:

1. Grant privileges on remote objects.

2. Execute DESCRIBE operations on some remote objects except for the following objects:

§ Tables

§ Views

§ Procedures

§ Functions

3. Analyze remote objects.

4. Define or enforce referential integrity.

5. Grant roles to users in a remote database.

6. Execute hash query joins that use shared server connections.

7. Use a current user link without authentication through SSL, password, or NT native authentication.

10 Two-Phase Commit Mechanism

A database must guarantee that all statements in a transaction, distributed or non-distributed, either commit or roll back as a unit. The effects of an ongoing transaction should be invisible to all other transactions at all nodes; this transparency should be true for transactions that include any type of operation, including queries, updates, or remote procedure calls.

The general mechanisms of transaction control in a non-distributed database are discussed in the Oracle Database Concepts. In a distributed database, the database must coordinate transaction control with the same characteristics over a network and maintain data consistency, even if a network or system failure occurs. The database two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. A two-phase commit mechanism also protects implicit DML operations performed by integrity constraints, remote procedure calls, and triggers.

11 Conclusion

The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database. The database link provides local transparency by creating synonyms for tables, packages and procedures in the remote database. The user can access these objects just as there local objects. Overall database links provide lot of flexibility in distributed computing they must be used judiciously to gain most advantages.

Unic Shell Script for Oracle DBA

2011-08-02T04:56:00.000-07:00

Introduction:

In Unix there are many ways to accomplish a given task. Given a problem to solve, we may be able to get to a solution in any number of ways. Of course, some will be more efficient, be more readable, use less disk space or memory, may or may not give the user feedback on what is going on or give more accurate details and more precision to the result. We are going describe the usage of various commands in Unix in Oracle point of view.

What is a Shell Script?

A shell is an environment in which we can run our commands, programs, and shell scripts. There are different flavors of shells, just as there are different flavors of operating systems. Each flavor of shell has its own set of recognized commands and functions.

The basic concept of a shell script is a list of commands, which are listed in the order of execution. A good shell script will have comments, preceded by a pound sign, #, describing the steps. Unix is case sensitive. Because Unix is case sensitive our shell scripts are also case sensitive.

Shell scripts and functions are both interpreted. This means they are not compiled. Both shell scripts and functions are ASCII text that is read by the Korn shell command interpreter. When we execute a shell script, or function, a command interpreter goes through the ASCII text line by line, loop by loop, test by test and executes each statement, as each line is reached from the top to the bottom.

Unix Special Characters

All of the following characters have a special meaning or function. If they are used in a way that their special meaning is not needed then they must be escaped. To escape, or remove its special function, the character must be immediately preceded with a backslash, \, or enclosed within ‘ ‘ forward tic marks (single quotes).

\ ( ; # $ ? & * ( ) [ ] ` ‘ “ +

Different Types of Shells to Declare

#!/usr/bin/sh OR #!/bin/sh Declares a Bourne shell

#!/usr/bin/ksh OR #!/bin/ksh Declares a Korn shell

#!/usr/bin/csh OR #!/bin/csh Declares a C shell

#!/usr/bin/bash OR #!/bin/bash Declares a Bourne-Again shell

Control Structures

Similar to C language, The following control structures will be used extensively.

if ... then Statement if ... then ... else Statement

if [ test_command ] if [ test_command ]

then then

commands commands

fi else

commands

if ... then ... elif ... (else) Statement

if [ test_command ]

then

commands

elif [ test_command ]

then

commands

elif [ test_command ]

then

commands

else (Optional)

commands

for ... in Statement

for loop_variable in argument_list

commands

done

while Statement

while test_command_is_true

commands

done

Until Statement

until test_command_is_true

commands

done

Case Statement

case $variable in

match_1)

commands_to_execute_for_1

;;

match_2)

commands_to_execute_for_2

;;

match_3)

commands_to_execute_for_3

;;

*) (Optional - any other value)

commands_to_execute_for_no_match

;;

esac

Using break, continue, exit, and return

It is sometimes necessary to break out of a for or while loop, continue in the next block of code, exit completely out of the script, or return a function’s result back to the script that called the function. break is used to terminate the execution of the entire loop, after completing the execution of all of the lines of code up to the break statement. It then steps down to the code following the end of the loop.

continue is used to transfer control to the next set of code, but it continues execution of the loop.

exit will do just what one would expect: It exits the entire script. An integer may be added to an exit command (for example, exit 0), which will be sent as the return code.

return is used in a function to send data back, or return a result, to the calling script.

Shell Script Commands

The basis for the shell script is the automation of a series of commands. We can execute most any command in a shell script that we can execute from the command line.

COMMAND DESCRIPTION

passwd Change user password

pwd Print current directory

cd Change directory

ls List of files in a directory

wildcards * matches any number of characters, ? matches a single character

file Print the type of file

cat Display the contents of a file

pr Display the contents of a file

pg or page Display the contents of a file one page at a time

more Display the contents of a file one page at a time

clear Clear the screen

cp or copy Copy a file

chown Change the owner of a file

chgrp Change the group of a file

chmod Change file modes, permissions

rm Remove a file from the system

mv Rename a file

mkdir Create a directory

rmdir Remove a directory

grep Pattern matching

egrep grep command for extended regular expressions

find Used to locate files and directories

>> Append to the end of a file

> Redirect, create, or overwrite a file

| Pipe, used to string commands together

|| Logical OR—command1 || command2—execute command2 if command1 fails

& Execute in background

&& Logical AND—command1 && command2—execute command2 if command1 succeeds

date Display the system date and time

echo Write strings to standard output

sleep Execution halts for the specified number of seconds

wc Count the number of words, lines, and characters in a file

head View the top of a file

tail View the end of a file

diff Compare two files

sdiff Compare two files side by side (requires 132-character display)

spell Spell checker

lp, lpr, enq, qprt Print a file

lpstat Status of system print queues

enable Enable, or start, a print queue

disable Disable, or stop, a print queue

cal Display a calendar

who Display information about users on the system

w Extended who command

whoami Display $LOGNAME or $USER environment parameter

who am I Display login name, terminal, login date/time, and where logged in

f, finger Information about logged-in users including the users .plan and .project

talk Two users have a split screen conversation

write Display a message on a user’s screen

wall Display a message on all logged-in users’ screens

rwall Display a message to all users on a remote host

rsh or remsh Execute a command, or log in, on a remote host

df Filesystems statistics

ps Information on currently running processes

netstat Show network status

vmstat Show virtual memory status

iostat Show input/output status

uname Name of the current operating system, as well as machine information

sar System activity report

basename Base filename of a string parameter

man Display the on-line reference manual

su Switch to another user, also known as super-user

cut Write out selected characters

awk Programming language to parse characters

sed Programming language for character substitution

vi Start the vi editor

emacs Start the emacs editor

Variables

Avariable is a character string to which we assign a value. The value assigned could be a number, text, filename, device, or any other type of data. A variable is nothing more than a pointer to the actual data.

Symbol Commands

( ) Run the enclosed command in a sub-shell

(( )) Evaluate and assign value to variable and do math in a shell

$(( )) Evaluate the enclosed expression

[ ] Same as the test command

[[ ]] Used for string comparison

$( ) Command substitution

`command` Command substitution

Math in a Shell Script

We can do arithmetic in a shell script easily. The Korn shell let command and the ((expr)) command expressions are the most commonly used methods to evaluate an integer expression. Later we will also cover the bc function to do floating-point arithmetic.

Cron Tables

A cron table is a system file that is read every minute by the system and will execute any entry that is scheduled to execute in that minute. By default, any user can create a cron table with the crontab -e command, but the Systems Administrator can control which users are allowed to create and edit cron tables with the cron.allow and cron.deny files. When a user creates his or her own cron table the commands, programs, or scripts will execute in that user’s environment. It is the same thing as running the user’s $HOME/.profile before executing the command.

The crontab -e command starts the default text editor, vi or emacs, on the user’s cron table.

How to use ftp in a shell script:

Sometimes we want to FTP a file from one machine to another. Usually, we can do the transfer interactively, but every so often, a shell script do the file transfer.

The problem we always encountered in scripting ftp transfers involved getting a password to the ftp server. Typical ftp client programs under Unix, Linux, Solaris and NetBSD all read the ftp password from /dev/tty.

Example Working Script

#!/bin/sh

HOST='ftp.users.qwest.net'

USER='yourid'

PASSWD='yourpw'

FILE='file.txt'

ftp -n $HOST <<END_SCRIPT

quote USER $USER

quote PASS $PASSWD

put $FILE

quit

END_SCRIPT

exit 0

The Tricks

Getting the password to the ftp server without having the ftp client program read the password from /dev/tty requires two tricks:

Using the -n option on the ftp client program to prevent the ftp client from trying to log in immediately. That way, the ftp client does not ask for a user ID and password. No use of /dev/tty.

Use the ftp client program command quote to send user ID and password to the ftp server.

Further Refinements

The above sh script will spew lots of ftp client output to standard output. Even if everything works perfectly, the user running the above script will see lots of incomprehensible text scrolling by quite rapidly. One refinement would send output to different places:

ftp -n $HOST > /tmp/ftp.worked 2> /tmp/ftp.failed <<END_SCRIPT

One could further refine error handling by acting on the ftp client program's exit status:

ftp -n $HOST > /tmp/ftp.worked 2> /tmp/ftp.failed <<END_SCRIPT

blah blah

END_SCRIPT

EXITSTATUS=$?

if [ $EXITSTATUS != "0" ]

then

# handle the error...

Except that the above doesn't always work - most FTP clients always exit with a status of 0. This leads to ugly "false negatives": the file transfer fails, but the script doesn't detect the problem.

One way to verify that a file transfer took place - transfer it back:

#!/bin/sh

ftp -n << END_SCRIPT

open $1

user $2 $3

put $4

get $4 retrieval.$$

bye

END_SCRIPT

if [ -f retrieval.$$ ]

then

echo "FTP of $4 to $1 worked"

rm -f retrieval.$$

else

echo "FTP of $4 did not work"

Regular FTPs there and back of large files can consume a lot of time.

Control of ftp by a shell script

One obvious improvement would have the ftp client program controlled by the shell script. I don't think that would comprise an impossible task, but we also don't think that it would have much value. Scripting ftp transfer using expect might cause you less pain.

How to Submit Concurrent Program Using CONCSUB:

Syntax:

CONCSUB <ORACLE ID>

CONCURRENT

[START=<Requested Start Date>]

[REPEAT_DAYS=<Repeat Interval>]

[REPEAT_END=<Request Resubmission End Date>]

Example to shutdown the concurrent manager

CONCSUB APPLSYS/FND SYSADMIN 'System Administrator' SYSADMIN [WAIT={Y|N}]

CONCURRENT FND [SHUTDOWN|ABORT]

ORACLE ID: Enter the username and password of the ORACLE ID you want your concurrent program to

run in. Enter the username and password without any spaces and separated by a slash ("/").

Responsibility Application Short Name: Enter the short name of the application for your responsibility.

This name, along with your responsibility name, will be used to select a responsibility for

your concurrent request to run in.

Responsibility Name: Enter the name of your responsibility. This name, along with your responsibility

application short name, will be used to select a responsibility for your concurrent request to

run in.

User Name: Enter the name of your Application ObjectLibrary user. This name will be used to update the

Who information for any data your concurrent program changes and to create report output

file name for this request.

Concurrent Program Application Short Name: Enter the short name of the application for your concurrent

program. This name, along with the concurrent program name, uniquely identifies the

concurrent program for your concurrent request.

Concurrent Program Name: Enter the name of your concurrent program. This name, along with the

concurrent program application short name, uniquely identifies the concurrent program for your

concurrent request.

Start Date: Specify the token START and enter the date you want the concurrent request to run, in DD-

MON-YY HH24:MI:SS format. Omit this argument if you want to run your concurrent request

immediately.^M^M<Repeat Interval> Specify the token REPEAT_DAYS and enter the time

interval, in days, for request resubmission. Use fractional number for intervals less than a day.

Omit this argument if you want to run your concurrent request only once.

Request Resubmission End Date: Specify the token REPEAT_END and enter the date you want the request

resubmission to stop, in DD-MON-YY HH24:MI:SS format. This argument is used in conjunction

with "Repeat Interval". Omit this argument if you want your request to repeat forever at an interval

specified by REPEAT_DAYS.

Concurrent Program Arguments: Enter the arguments for your concurrent program.

Sample HOST File to Submit Two concurrent Programs Using CONCSUB:

########################################################################

# Set the variables for running concurrent program.

########################################################################

CONCPRG="SQL_CONC_TEST"

CONCAPP="SEAR"

RESPAPP="SEAR"

RESP="SMK-AR-AFFILIATE"

USERNAM=`echo $1 | cut -d ' ' -f 5 | cut -d '"' -f 2`

echo $CONCPRG

echo $CONCAPP

echo $RESPAPP

echo $RESP

echo $USERNAM

########################################################################

CONCMSG=`noarg CONCSUB $FCP_LOGIN \

$RESPAPP \

$RESP \

$USERNAM \

WAIT=10 \

CONCURRENT \

$CONCAPP \

$CONCPRG`

########################################################################

# Set the variables for running concurrent program.

########################################################################

CONCPRG="PLSQL_CONC_TEST"

########################################################################

CONCMSG=`noarg CONCSUB $FCP_LOGIN \

$RESPAPP \

$RESP \

$USERNAM \

WAIT=10 \

CONCURRENT \

$CONCAPP \

$CONCPRG`

reqid=""

reqid=`echo $CONCMSG | cut -d " " -f 3`

CONCMSG=`echo $CONCMSG | cut -d'"' -f3`

y=""

y=`echo $reqid | grep "^[0-9]"`

if [ $? -ne 0 ]

then

echo "Failed to schedule SMARCONSS thru Control-M option ...\n"

exit 1

else

exit 0

In the above script we have taken two concurrent programs named SQL_CONC_TEST and PL/SQL_CONC_TEST both consists simple PL/SQL code. These are assigned to “SMK-AR-AFFILIATE” responsibility and its short name is “SEAR”. Here we have taken two CONCSUB commands to submit the concurrent programs.

Mailing using UNIX Shell Script:

mailx - interactive message processing system

SYNOPSIS

mailx [-BdeHiInNURvV~] [ -f [file | +folder] ] [-T file] [-u user]

mailx [-BdFintUv~] [-b bcc] [-c cc] [-h number] [-r address] [-s subject] recipient...

/usr/ucb/mail ...

/usr/ucb/Mail ...

DESCRIPTION

The mail utilities listed above provide a comfortable, flexible environment for sending and receiving mail messages electronically.

When reading mail, the mail utilities provide commands to facilitate saving, deleting, and responding to messages. When sending mail, the mail utilities allow editing, reviewing and other modification of the message as it is entered. Incoming mail is stored in a standard file for each user, called the mailbox for that user. When the mail utilities are called to read messages, the mailbox is the default place to find them. As messages are read, they are marked to be moved to a secondary file for storage, unless specific action is taken, so that the messages need not be seen again. This secondary file is called the mbox and is normally located in the user's HOME directory (see MBOX in ENVIRONMENT VARIABLES for a description of this file). Messages can be saved in other secondary files named by the user. Messages remain in a secondary file until forcibly removed.

The user can access a secondary file by using the -f option. Messages in the secondary file can then be read or otherwise processed using the same Commands as in the primary mailbox. This gives rise within these pages to the notion of a current mailbox.

OPTIONS

On the command line options start with a dash (-). Any other arguments are taken to be destinations (recipients). If no recipients are specified, mailx attempts to read messages from the mailbox.

-B Do not buffer standard input or standard output.

-b bcc Set the blind carbon copy list to bcc. Bcc should be enclosed in quotes if it

contains more than one name.

-c cc Set the carbon copy list to cc. cc should be enclosed in quotes if it contains more

than one name.

-d Turn on debugging output. (Neither particularly interesting nor recommended.)

-e Test for the presence of mail. mailx prints nothing and exits with a successful

return code if there is mail to read.

-F Record the message in a file named after the first recipient. Overrides the record

variable, if set (see Internal Variables).

-f [file] Read messages from file instead of mailbox. If no file is specified, the mbox

is used.

-f [ +folder] Use the file folder in the folder directory (same as the folder command).

The name of this directory is listed in the folder variable.

-H Print header summary only.

-h number The number of network "hops" made so far. This is provided for network

software to avoid infinite delivery loops. This option and its argument are passed

to the delivery program.

-I Include the newsgroup and article-id header lines when printing mail messages.

This option requires the –f option to be specified.

-i Ignore interrupts. See also ignore in Internal Variables.

-N Do not print initial header summary.

-n Do not initialize from the system default mailx.rc or Mail.rc file.

See USAGE.: Set the Subject header field to subject. Subject should be enclosed in quotes if it contains embedded white space.

-T file Message-id and article-id header lines are recorded in file after the message is

read. This option also sets the -I option.

-t Scan the input for To:, Cc:, and Bcc: fields. Any recipients on the command

line will be ignored.

-U Convert UUCP-style addresses to internet standards. Overrides the conv

environment variable.

-u user

Read user's mailbox. This is only effective if user's mailbox is not read protected.

-V Print the mailx version number and exit.

-v Pass the -v flag to send mail (1M).

-~ Interpret tilde escapes in the input even if not reading from a tty.

INT_CODE=`echo $1 | cut -f1 -d' '`

INT_REQID=`echo $1 | cut -f2 -d' ' | cut -f2 -d'='`

INT_REQID=`expr $INT_REQID \* 1`

USERNAME=`echo $1 | cut -f5 -d' ' | cut -f2 -d'"'`

FUSER=`echo $USERNAME | cut -c1-8`

PER_NAME=`echo $1 | cut -f9 -d' ' | cut -f2 -d '"'`

echo $INT_CODE

echo $INT_REQID

echo $LOGIN

echo $USERNAME

echo $FUSER

echo $PER_NAME

var=`echo $INT_CODE $INT_REQID $LOGIN $USERNAME $FUSER $PER_NAME`

mailx -m -s $var giridhar.gedela@wipro.com

Reference:

Mastering Unix Shell Scripting by Randal K. Michael

Unix for Oracle DBA

2011-08-02T04:54:00.000-07:00

The Article is a quick reference describing, how to create complex one-line Unix commands in addition to some most often used Unix commands used by Oracle database administrators.

Building Unix Commands

One of the most confounding things for the Unix neophyte is being confronted with a complex Unix command. The cryptic nature of Unix is such that even the most seasoned Unix professional may have trouble deciphering such a command.

Regarding the terms commands and scripts, you should note that any command may become a script if it is encapsulated into a file for execution. Hence, find . –print can be a command if executed from the prompt, or a script if placed into a file.

Building a Complex Unix Command from Scratch

A complex, one-line script is really composed of several simpler commands connected by the pipe operator (|). We will start by how to build a one-line script to kill all the Oracle processes on your server. Then we will see how to build one-line script to find files that contain a specific text string.

A script to kill all Oracle processes

This is a common Unix script used by Oracle DBAs when a database is locked up, and Server Manager cannot be used to stop the database in more “gentle“ fashion.

To begin, the Unix kill command is used to kill a process. The basic format of the kill command is as follows:

Kill –9 PID1 PID2 PID3 …PIDn

The trick is to be able to identify and kill only the Oracle processes. That’s done by stringing several commands together. The resulting one-line script looks like this:

ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9

We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command:

ps –ef

If we execute ps –ef on our server, we’ ll see a long list of processes - both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Database. The grep command can be used to do this. Oracle background process names always begin with “ora_”, so piping the output of ps –ef through grep “ora_” will remove all but the Oracle background processes. For example:

>ps –ef |grep “ora_”

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 12789 20202 0 12:10:55 0:00 grep ora_

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

oracle 10403 1 0 Dec 06 … ora_dbwr_TEST

oracle 10401 1 0 Dec 06 … ora_lgwr_TEST

In the above output as we can see it includes the process that’s running grep command. Pipe this output through grep –v grep to remove the grep command, so you don’t kill your own process. The –v option makes grep work in a way that’s opposite its usual manner. Whereas grep finds and includes strings, grep –v excludes strings. In this next example, we’ ll see that the grep line is now missing from the output:

>ps –ef |grep “ora_”|grep –v grep

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

oracle 10403 1 0 Dec 06 … ora_dbwr_TEST

oracle 10401 1 0 Dec 06 … ora_lgwr_TEST

Next, we should filter out all processes except those for the current ORACLE_SID. That way we delete the background processes only for that one instance instead of for all instances (if there are multiple database instance running). Do that by grepping for the SID name:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID

oracle 12011 1 0 Dec 06 … ora_dbwr_JAP

oracle 13202 1 0 Dec 06 … ora_smon_JAP

oracle 14983 1 0 Dec 06 … ora_arch_JAP

oracle 10209 1 0 Dec 06 … ora_pmon_JAP

oracle 2090 1 0 Dec 06 … ora_reco_JAP

oracle 10404 1 0 Dec 06 … ora_lgwr_JAP

Now that we have an accurate list of processes that you want to kill, you can use the awk command to get the process ID (PID) for each of these processes. The PID is in the second column, so we will use the awk ‘{print $2}’ command to display only that column:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’

12011

13202

14983

10209

2090

10404

Now we have a list of process Id numbers for the Oracle background processes. For the last step, we use the xargs command to pipe the list of PIDs to the kill command. For example:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|
awk ‘{print $2}’|xargs kill –9

Now that we’ve created this compound command, we can assign it to a Unix alias or we can put it in a file and make it a shell script so that we can execute it with a single short command.

Note: Not all shells support aliases. For example, if we are using the Bourne shell we will not be able to use aliases.

The following command assigns the new compound command to an alias named kill_oracle:

alias kill_oracle = “ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9”

By placing the command to create the alias in your .profile file, we’ll have it available every time you sign on to Unix. By using an alias, you encapsulate the command without the burden of placing the command into a script file. Now, entering the alias kill_oracle at the command prompt will cause your command to run, which will kill all Oracle background processes for the instance to which $ORACLE_SID points.

A script to find all files containing a specific string

In Unix, it is not easy to find files that contain specific strings. Now we will explore a way to quickly build a command that will allow us to find a file that contains a particular character string.

Using commands such as xargs, we can quickly generate Unix scripts to perform many useful tasks. Suppose that we have created a script to create database TEST. Unfortunately, we have completely forgotten the name and location of the script file, and we need a Unix command to locate it. The example here demonstrates how you can leverage the xargs command to quickly create a complex command that searches for our lost file.

We will begin by writing a command that will display all filenames on the server. This syntax is quite simple in Unix, as the find command can be used to return a list of every file on the server starting from your current directory:

>find . –print

/home/oracle/wylie/sqlnet.log

/home/oracle/wylie/abc.sql

/home/oracle/wylie/tablespace.sql

/home/oracle/wylie/create1.sql

/home/oracle/wylie/export.dmp

/home/oracle/wylie/create2.sql

/home/oracle/wylie/create3.sql

/home/oracle/wylie/a.txt

We now have a complete list of all the Unix files under our current directory. The next step is to pipe this list of filenames to the grep command to search for files containing the string CREATE_DB_TEST. Because the grep command accepts a filename as an argument, you can use xargs to execute a grep command to search each file for the string we need:

find . –print|xargs grep –i create_db_test

The –i option tells grep to ignore case. We can execute this new command at the Unix prompt, and we’ll see that it quickly finds the file we are seeking:

>find . –print|xargs grep –i create_db_test

This ability to take a basic Unix command and pipe the output into another command is a fundamental principle of Unix shell programming for Oracle.

Unix Server Environment

Here are some handy Unix commands that will make it easier for use to navigate in our Unix environment. Firstly we will see at commands that can be automatically executed when we sign on to Unix as the Oracle user. There is a special file in our home directory in which we can place Unix commands that we want automatically executed when we sign on to the system. If we use Korn shell, this file is named .profile. if we use C shell, it will be called .cshrc.

We will also see how to create a standard Unix prompt, wrap SQL in Unix script, and write a utility to quickly change all files in a directory.

Set a Standard Unix Prompt

Placing the following code snippet in our .profile file will give you a Unix prompt that identifies our current server name, database name, and working directory. This can help prevent you from accidentally running a command against the wrong database. Note that I have my prompt go to a new line after displaying the information, so that I have a full 79 characters in which to type my Unix commands.

#*********************************************************************

# Standard Unix Prompt

#*********************************************************************

PS1=”

`hostname`*\${ORACLE_SID}-\${PWD}

>”

Here is what the prompt looks like after we have executed the PS1 command shown in the previous example. Note now the prompt changes when you change directories.

agj1*JAP-/home/oracle

>pwd

/home/oracle

agj1*JAP-/home/oracle

>cd /home2/dmp/treasury

agj1*JAP-/home2/dmp/treasury

Create Useful Unix Aliases for Oracle

Here we will see how we can place a list of helpful Unix aliases in the .profile file of a Unix Oracle User.

An alias is a Unix shortcut whereby we can define a short name to use in place of long Unix command. For example, we can create a shortcut called “log” that would execute the Unix cd (change directory) command to take us to the Unix directory where our alert log is located:

alias log = ‘cd $DBA/$ORACLE_SID/bdump’

The following example shows how aliases such as these can be used in place of typing a long command:

$log

$pwd

/home/oracle/app/oracle/admin/JAP/bdump

Any alias can be removed easily with the Unix unalias command. For example, to remove the log alias, we would enter the command unalias log.

Place a SQL*Plus Script in a Unix Shell Wrapper

Novice to Unix often find it convenient to execute SQL commands directly from the Unix prompt, without having to enter SQL*Plus each time. The following script shows how to create a Unix shell wrapper for any set of SQL*Plus commands. The Unix script in this example is named run_sql.ksh, and it invokes SQL*Plus to execute a SELECT statement followed by the SQL*Plus script contained in the file /home/oracle/abc.sql:

$cat run_sql.ksh

#!/bin/ksh

#First, we must set the environment . . . . .

ORACLE_SID=mysid

export ORACLE_SID

ORACLE_HOME=\`cat /etc/oratab|grep ^$OARCLE_SIDKcut –f2 –d’:’`

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

$ORACLE_HOME/bin/sqlplus system/passwd<<!

SELECT * FROM v\$database;

@/home/oracle/abc.sql

exit

We can also execute a script directly from the command line, provided we have set ORACLE_HOME and ORACLE_SID in our Unix environment. For example:

$sqlplus system/manager @abc

Step by Step Log Shiping Configuration

2011-07-21T02:21:00.000-07:00

SQL Server Built-in Functionality

There are two options for configuring log shipping using the updated feature in SQL Server 2005.

You can either use SQL Server Management Studio or stored procedures. Using the stored procedures

to configure log shipping was not possible in SQL Server 2000. Log shipping is included as

a feature in the following editions of SQL Server 2005: Developer Edition, Enterprise Edition, Standard

Edition, and Workgroup Edition. In SQL Server 2000, log shipping was only available in the

Developer and Enterprise Editions.

Like SQL Server 2000, SQL Server 2005 provides a way to monitor the status of your log shipping

pair by configuring a monitor server during the configuration of log shipping. The section “Monitoring

Log Shipping” later in this chapter shows you how to see the status of log shipping. The only reason

I am mentioning it here is that where you place the monitor is a consideration for installation. It

can be placed on any available SQL Server 2005 instance that supports the log shipping feature. I do

not recommend placing it on your primary, as that would be a single point of failure. I always recommend

putting the log shipping monitor functionality on a completely separate server that is not the

primary or the secondary. The monitor is optional; so if you choose not to deploy one, there are other

ways of monitoring the status of log shipping. One bonus of a monitor that exists on another SQL

Server instance is that multiple log shipping configurations can share one log shipping monitor.

SQL Server Management Studio

The following steps walk you through the process of configuring log shipping via SQL Server Management Studio:

Step 1. Start SQL Server Management Studio. Expand the Databases folder, select the database that will serve as the primary, right-click the database, select Tasks, then Ship Transaction Logs

as shown in Figure 1. You can also select Properties, and then when the properties of

the database are displayed, select the Transaction Log Shipping option. With either method,

Step 3. Click Backup Settings. The Transaction Log Backup Settings dialog will be displayed. In the

Network Path to Backup Folder text box, enter the share name you created on the primary

computer. If you are planning on crossing domains that are not trusted for log shipping, you

will have to use the IP address instead of the server name in the share (e.g., \\172.22.10.1\

backups). If the folder is located on the same server as the SQL Server instance containing

the primary database, enter the path to the local backup path share. This is not necessary if

you are generating backups to another server or share (as I noted previously in the considerations

for log shipping). By default, the log shipping functionality will create a backup job

on the primary with the name LSBackup_dbname, where dbname is the name of the primary

database. If you wish to change the name of the backup job, change Job Name, as

shown in Figure3

The backup job defaults to a frequency of 15 minutes. If you want to

change how often the backup job is executed, click Schedule. You will see a dialog similar

to the one in Figure 4.

Step 4. Click Add to begin the process of adding a secondary. You will see the Secondary Database

Settings dialog as shown in Figure 6

Click Connect. You will be prompted to connect to

the secondary database instance as shown in Figure 7.

Make sure that you enter the

proper name or IP address of the SQL Server 2005 instance that will contain the warm

standby database and click Connect.

Step 5. Select the Initialize Secondary Database tab of the Secondary Database Settings dialog. By

default, the name of the database that will be used on the secondary SQL Server instance is

the same as the one on the primary. You have three options:

• Use the configuration tool to create a full backup of the primary database as shown

in Figure 8

You cannot control the name of the backup that is generated; it will

be dbname.bak, and will be placed in the directory or share you configured in step 3.

If you want to restore the database to a different path on the secondary, click Restore

Options, and enter the new paths for your data and log files in the dialog as shown in

Figure 9

. The paths you enter should be local to the secondary server, such as

C:\SQL Data. If you want to restore the database to a different name, enter a new

name in the Secondary Database drop-down/entry box.

figure 10

• Use an existing backup file and have the process restore it on the secondary server. An

example is shown in Figure 10.

As with the previous option, you can choose to customize

where you will place the data and log files on the secondary. Click Restore Options.

Note Both options for initializing your standby database are controlled by the settings you will configure on the

Restore Transaction Log tab for restoring your transaction log files. For more information, see step 7.

• Back up, copy, and restore the database on the secondary yourself with either

figure 11

NORECOVERY or STANDBY. Select No, the Secondary Database Is Initialized, as shown in

Figure 11

. You must select the database you restored in the Secondary Database

drop-down.

Step 6. Select the Copy Files tab. Enter the share name you created on the secondary to receive

the backup files. The log shipping functionality can

also delete the files copied to the secondary. The default is three days (72 hours). The

default job created on the secondary (so the copy is a pull, not a push) is named

LSCopy_primary_dbname where primary is the name of the primary database instance

and dbname is the name of the database. To modify how often the copy job is run, click

Schedule. You will see a dialog similar to the one displayed in Figure 12.

Click OK

Step 7. Select the Restore Transaction Log tab. The main option you will select here is to tell the

restore job to apply the transaction logs using either WITH NORECOVERY or WITH STANDBY. If

you previously selected the option to initialize the database, whatever you selected for the

restore state will also be used for the restoration of the full database backup. An example is

shown in Figure 14

. The default job created on the secondary (the copy is a pull, not a

push) is named LSRestore_primary_dbname where primary is the name of the primary

database instance and dbname is the name of the database. To modify how often the restore

job is run, click Schedule. You will see a dialog similar to the one displayed in Figure 15.

Click OK when finished modifying the schedule.

Note If you select the WITH STANDBY option for your restore, and the user configuring log shipping does

figure 16

not have the proper rights for both SQL Server instances, you should see a message similar to the one in

Figure 16.

step 8. Click OK on the Secondary Database Settings dialog to return to the main transaction log

shipping screen. To configure a monitor, select the option Use a Monitor Server Instance.

This is optional; however, as noted previously, if you do not create the monitor now, you

cannot add it later. The display will be similar to the one in Figure 17.

If you create a

monitor, click Settings. Otherwise, skip to step 10.

Step 9. On the Log Shipping Monitor Settings dialog shown in Figure 18,

click Connect.

You will

be prompted to connect to the SQL Server instance that will contain the monitor. An example

is shown in Figure 19.

Click Connect. You should now see the dialog updated, as

shown in Figure 20.

Select how the primary and secondary instances will connect to the

monitor. On the monitor, the alert job will be configured with the name LSAlert_InsName

by default, where InsName is the name of your SQL Server instance. You can change this if

you wish. Click OK to close the Log Shipping Monitor Settings dialog.

Step 10. Click OK to start configuring log shipping as shown in the dialog in Figure 21.

11. Once log shipping is successfully configured, you will see a dialog similar to the one in

Figure 22. Click Close.

Figure 22. Successful completed log shipping configuration

Transact-SQL

As with most features in SQL Server 2005, you can set things up either via SQL Server Management

Studio or through Transact-SQL. To configure log shipping, you will use the stored procedures

Stored Procedure Server

sp_add_log_shipping_primary_database Primary

sp_add_schedule Primary

sp_add_log_shipping_primary_secondary Primary

sp_add_log_shipping_alert_job Monitor (optional)

sp_add_log_shipping_secondary_primary Secondary

sp_add_jobschedule Secondary

sp_add_log_shipping_secondary_database Secondary

Note If you look in both the master and msdb databases, you may see other system stored procedures that

relate to log shipping (e.g., sys.sp_processlogshippingmonitorhistory). These procedures are used by log

shipping and are called within some of the stored procedures used for installing, running, and maintaining log

shipping. These are not documented since you would never actually use them yourself.

The easiest way to see how log shipping is scripted is to do it through SQL Server Management

Studio once and then generate a script. The stored procedures listed earlier .

in SQL Server 2005 Books Online, and going through them here does not make a lot of sense.

Custom Log Shipping

As noted earlier, you can also code your own log shipping process. I have written scripts for you to

use or use as an example; they can be found on the Apress site in the Source Code/Download section.

The log shipping process I wrote is a tad different than but similar to Microsoft’s built-in

feature. One of the main problems I have with canned features such as the built-in implementation

in SQL Server 2005 is that it requires you to change any maintenance you may have in place already.

You cannot just hook into an existing transaction log backup that is already being done. The scripts

on the Apress site allow you to use your existing transaction log backup job. You also do not have as

much control over the copy process since it is pretty much a black box. My scripts are a combination

of SQL Server stored procedures and VBScript that is used to perform the copying.

Postconfiguration Tasks

After you configure log shipping, you will need to ensure that all objects that live outside of the primary

database are also configured on your secondary (or secondaries if you are log shipping to more

than one server). If you do not do this, your database may not function properly if you ever need to

bring it online. The easiest way to do most tasks is to create scripts, modify them if necessary, and

apply them to the secondary instance.

Once you select the object to script, you will have three options: script to a query window,

script to a file, or script to the clipboard. The easiest option is to script the object directly to a file,

but if you are trying to create a master script to create all objects, you may want to script it to the

clipboard or to a query window and manipulate it that way.

If an object cannot be scripted from Management Studio, such as an SSIS package, you will

need to find what the best method is for getting that object over to the secondary. The easiest way is

always to get it into some sort of file in Windows and then copy it to the other server and apply it.

Administering Log Shipping

Once you get log shipping set up, you will need to administer and maintain it. There are two methods

for hiding the server name change in a log shipping role change: Network Load Balancing and

configuring a DNS alias. Since these techniques apply to more than just log shipping, they are covered.

configured on the same hardware as a failover cluster; so if any of the instances are nodes of a

failover cluster, you cannot use this method to abstract the instance name change.

Monitoring Log Shipping

One major change from SQL Server 2000 Enterprise Edition is the way you monitor log shipping.

SQL Server 2000 shipped with a Log Shipping Monitor that when installed showed you in an interface

the status of your log shipped pair. SQL Server 2005 removes the graphic interface, but you

can query tables or use stored procedures.

Job Status

Arguably the simplest way to first check the status of log shipping for most administrators will be

to see the status of the SQL Server Agent jobs involved with log shipping. Those will tell you if the

configured jobs are actually running successfully or are failing.

If you do not want to use any of the available stored procedures to monitor your log shipping configuration,

you can query the system tables (log shipping–specific tables, tables relating to jobs, etc.)

that contain information about log shipping directly. All of the tables are located in msdb. The tables

specific to log shipping are the following:

• msdb.dbo.log_shipping_monitor_alert: This table contains only one column, which is the

SQL Server Agent job ID of the log shipping alert job.

• msdb.dbo.log_shipping_monitor_error_detail: This table contains the specific SQL Server

Agent job errors encountered during log shipping.

• msdb.dbo.log_shipping_monitor_history_detail: This table contains the history details for

the SQL Server Agent jobs associated with the log shipping plans.

• msdb.dbo.log_shipping_monitor_primary: This table contains configuration information

about the primary database, and there is only one record per primary.

• msdb.dbo.log_shipping_monitor_secondary: Similar to the previous table, this contains one

record per secondary and has information about the configuration of the secondary.

• msdb.dbo.log_shipping_primaries: This table contains the monitor information for each log

shipping plan.

• msdb.dbo.log_shipping_primary_databases: This table contains one record per primary, and

has information about the primary database.

• msdb.dbo.log_shipping_primary_secondaries: This table contains one record per primary,

and has information about the secondary database.

• msdb.dbo.log_shipping_secondaries: This table has the mappings from the log shipping

plan to the secondary instance and database.

• msdb.dbo.log_shipping_secondary: This table contains information about the secondary and

its status. There is one record per database.

• msdb.dbo.log_shipping_secondary_databases: This table contains configuration information

about the secondary database. There is one record per configuration

Newly created SQL object Details for last Week

2011-07-20T05:05:00.002-07:00

PRINT '=========================='
PRINT ' SQL objectDetails '
PRINT '=========================='
select CONVERT(VARCHAR(35), name)"Name" ,xtype , crdate from sysobjects where crdate >= getdate() -7

Find out any Login created or updated in last week

2011-07-20T05:05:00.000-07:00

PRINT '=========================='
print '   LOGIN UPDATE DETAILS   '
PRINT '=========================='
select CONVERT(VARCHAR(15), loginname) loginname,CONVERT(VARCHAR(10), dbname) dbname, updatedate from master..syslogins   where convert(varchar(8),updatedate,112)>   convert(varchar(8),getdate()- 7,112) and createdate <> updatedate
PRINT '=========================='
print '       LOGIN DETAILS      '
PRINT '=========================='
select CONVERT(VARCHAR(15), loginname) loginname, CONVERT(VARCHAR(10), dbname) dbname, createdate from master..syslogins   where convert(varchar(8),createdate,112) > convert(varchar(8),getdate()- 7,112)

find out New created Maintanance plan&Jobs in week

2011-07-20T05:03:00.002-07:00

PRINT '===================================='
PRINT 'New created Maintanance plan in week'
PRINT '===================================='
select CONVERT(VARCHAR(25), plan_name) plan_name,date_created
from msdb..sysdbmaintplans
where convert(varchar(8),date_created,112)> convert(varchar(8),getdate()- 7,112)
PRINT '=========================='
PRINT ' New created Job in week '
PRINT '=========================='
select * from msdb..sysjobs where date_created > convert(varchar,getdate()-7,101)

Backup time details for database in Sql Server

2011-07-20T05:03:00.000-07:00

PRINT '================================'
PRINT ' Backup time for database'
PRINT '================================'
SELECT database_name,backup_size,backup_start_date,backup_finish_date,convert(decimal(5,2),datediff(ss,backup_start_date,backup_finish_date)/60.00) "Backup time"
from msdb.dbo.backupset
where convert(varchar,backup_start_date,101)=convert(varchar,getdate()-1,101)
order by backup_start_date desc

'TOP TABLES IN EACH DATABASE in Sql Server

2011-07-20T05:01:00.002-07:00

PRINT '================================'
PRINT 'TOP TABLES IN EACH DATABASE '
PRINT '================================'
SET NOCOUNT ON
DECLARE @CMD NVARCHAR(4000)
PRINT 'Successful Jobs in the Instance'
set @CMD='select CONVERT(VARCHAR(35), b.name), run_date, a.step_id from msdb..sysjobhistory a,msdb..sysjobs b where a.job_id = b.job_id and a.run_date = convert(varchar(8),getdate()- 1,112) and run_status = 0 and b.job_id in(select job_id from msdb..sysjobs)'
EXEC SP_EXECUTESQL @CMD
GO
DECLARE NM CURSOR LOCAL STATIC FOR
SELECT NAME FROM SYSDATABASES
DECLARE @DN VARCHAR(50)
DECLARE @CMD1 NVARCHAR(4000)
DECLARE @CMD2 NVARCHAR(4000)
DECLARE @CMD3 NVARCHAR(4000)
PRINT ''
OPEN NM
FETCH NEXT FROM NM INTO @DN
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Top Object for Database ' + @DN
SET @CMD1='select top 10 CONVERT(VARCHAR(35), name) "Tablename", (convert(bigint,sum(reserved))*8192/1024/1024) "Reserved Size(MB)", sum(rows) "Total Rows" from '+@DN+'..sysindexes where indid in (0,1,255) group by name order by 3 desc'
EXEC SP_EXECUTESQL @CMD1
FETCH NEXT FROM NM INTO @DN
END
CLOSE NM
DEALLOCATE NM
GO

'SPACE DETAILS OF DATABASES' in Sql Server

2011-07-20T05:01:00.000-07:00

PRINT '=========================='
PRINT 'SPACE DETAILS OF DATABASES'
PRINT '=========================='
EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

PRINT '=========================='
PRINT ' DATABASE DETAILS '
PRINT '=========================='
SET NOCOUNT ON
DECLARE NM CURSOR LOCAL STATIC FOR
SELECT NAME FROM SYSDATABASES
DECLARE @DN VARCHAR(50)
DECLARE @CMD1 NVARCHAR(4000)
PRINT ''
OPEN NM
FETCH NEXT FROM NM INTO @DN
WHILE @@FETCH_STATUS=0
BEGIN
print 'DB Details ' + @DN
set @CMD1='select CONVERT(VARCHAR(35), a.name)Name,CONVERT(VARCHAR(8),filegroup_name(a.groupid)),(a.size * 8)total_size ,b.spaceused from '+@DN+'..sysfiles a, ( select groupid, sum(reserved) spaceused from '+@DN+'..sysindexes where indid in (0,1,255) group by groupid ) b where a.groupid = b.groupid'
EXEC SP_EXECUTESQL @CMD1
FETCH NEXT FROM NM INTO @DN
END
CLOSE NM
DEALLOCATE NM
GO

Sql Server Database Backup types :

2011-07-20T02:57:00.000-07:00

Sql Server Database Backup types :

Sql Server Has mainly Four Types Of Data Backups :

(1). Full Backup.

(2).Differential Backup.

(3). File and File group Backup.

(4).Log File Backup.

(1) Full Backup:- includes the all Data files and log files in a database. it is a complete set of data need to restore and recover the database in consist stat .

you can say that full backup is a complete set of file and files groups is equal to that full backup.

(2). Differential Backup :- includes all the changes made since last full backup .differential backup in not a stand alone backup. when ever you required to restore or recover that data from differential backup then first you need to restore the full backup .if you have been taken multiple differential backups then you need to restore only latest one backup except that all backup.

(3)File or File groups backup :- Includes the backup of particular file and file groups that specified. Most of time you take currently used file and file groups backup that as default file groups with a particular database .this type of backup you take only when database size in tera bytes

(4)Log File Backup:-includes all the data recorded in log file after taken the full backup or last log backup.

When you take log backup first time then it take backup of all log records that changed after that full backup.

If you take backup second or third time and so on then it start always last log backup.

Sql Server DBA Interview questions

2011-07-13T23:09:00.000-07:00

Please complete all below questions series after that attend any interview for Sql DBA

1).what is the default Recovery model of a newly created database ?

2).How many types of recovery model in Sql Server ?

3.)How many system databases in Sql Server 2005 ?

4). How you can say that Tempdb Database is different from other system and user databases ?

5).What is different between simple and full recovery model ?

6.)How to create a Database with 1 mdf,1ndf and 1 log file by T-Sql syntax ?

7).How to Add one more log file into a database by T-Sql syntax ?

8).How to add a file group to a Database by T-Sql syntax?

9).How to add a log file with a test file group by T-Sql syntax ?

10).How to find out database size , physical file location and database status as well ?

11) How to rename a Database by using T-Sql syntax?

12)How many types of backup in sql Server 2008/2005/2000 ?

13) What is the usage of mssqlsystemresource database in sql server ?

14) How many types of challenges you face in your project ?

15 ) what is the different between differential and log backup ?

16) How to shrink the log file of database ?

17) what is the different between shrink commend with truncate_only or no_truncate clause ?

18) backup log DB name with truncate_only is taking any backup or not if not ? why ?

19 ) How to take filegroup file backup of database ?

20) how to perform point in time recovery ?

21) How many types of replication in sql server 2008/2005/2000 ?

22) Which backup strategy (policy ) implement in our current project ?

23)How you configured Log Shipping or Database mirroring in current project if yes then please justify and how many types of issues you faced regarding log shipping and Database mirroring ?

24 ) Have u configured clustering on current project if yes then what king of clustering you configured and how ?

25) Tell me about your daily , weekly and monthly activities ?

26 ) How you enable log file in memory for sql server ?

27) how to find out blocking process and deadlock on sql server ?

28 )How to move Master database from one location to another location ?

29)How to restore master database backup on sql server ?

30)what is different between windows authentication and sql server authentication ?which one better then other ? why ?

31)How to find out currently running process on sql server and which one process taking long time to execute ?

32)Which process or job taking high C.P.U. Utilization ?

33)IF Sql server going hang again and again what are steps to follow to resolved problem ?

Attach Database with MDF file only

2011-07-13T21:26:00.000-07:00

Use master
go
create database test on
(name=logicalfilename,
filename='physical path of mdf file ')
for attach_rebuild_log
go

Above commend create one log file for this test DB and location for physical file as same as mdf file .
this commend is very useful if you lost the ldf file of any user database.

How to attach and deattached database in sql server by T-sql ?

2011-07-13T02:53:00.000-07:00

Please use the below query :

For Attach :

USE
GO [master]CREATE DATABASE [abc] ON (
( FILENAME = N'D:\sql_data\abc.mdf' ), FILENAME = N'D:\sql_data\abc_log.ldf' )
GO

For Deattach :

USE
GO [master]EXEC master.dbo.sp_detach_db @dbname = N'abc', @keepfulltextindexfile=N'true'GO

After that refersh the database tab and check your currently attached data base.FOR ATTACH

How to move database physical_files from one drive to another drive (location

2011-07-12T22:42:00.000-07:00

Please follow the below steps :

1) Alter database abc (file=logical file name for data file ,
filename='new location or path');
Alter database abc (file=logical file name for log file ,
filename='new location or path');
for example
Alter database abc (file=abc,
filename='d:\abc.mdf');
Alter database abc (file=abclog,
filename='e:\abc_log.ldf');

2) Stop the sql server services.

3) Move the physical files (.MDF and LDF) from old location to new location .

4).Restart the Sql Server Services .

5) Veryify new location by using below command

select * from sys.master_files ;