Clone Oracle databse

STEP 1: 

SQL> create pfile from spfile;

SQL > alter database backup controlfile to trace;

Get the last trace file from udump and edit as below and save as control.ctl

Use the Second part

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;

 

STEP 2: Shutdown the old database

STEP 3:  copy datafiles, tempfiles,redo to new location, Also copy password file,initOra.file.

 STEP 4: Create the bdump, udump and cdump directories

             

STEP 5: Edit controlfile per new location and also change below term.

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" RESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 6: Edit pfile as per  new location. 

STEP 7: follow below steps to start database.

Check $ORACLE_SID(new sid)

Delete spfileNewSID if exist.

SQL> startup nomount;

SQL> @control.ctl

SQL>  alter database open resetlogs;

n  Add temporary tablespace ( use traced controlfile to get the syntax)

SQL> create spfile from pfile;

SQL > startup force;

Delete Unsent Email from Sql server 2008 database mail

Below are the steps to clear Unsent email from Database mail table.

I had situation where sql server dbmail generated millions of email for one of my Insufficient Resource Error and keep sending me thousands of email in every second.

To resolve this issue I have stopped that "Insufficient Resource" Alert from sql server agent.

But it was keep sending me email because All those email are generated before I have stopped the alert.

Now the question was How to clean that earlier generated mail from DatabaseMail table.

I found the following solution for this issue. I could see from the "unsent mail" table that it would stop sending me email after certain time. But rather waiting for email to be cleared why would I not delete all the email.

Therefore I have followed below step to delete all unsent email from the table.

And I succeed.

SELECT * FROM msdb.dbo.sysmail_event_log;

-- to see number email remaining to be sent

select count(*) from msdb.dbo.sysmail_unsentitems;

-- delete all that remaining emails

delete from msdb.dbo.sysmail_unsentitems;

-- table is cleared and I have not received any single mail after that.

select * from msdb.dbo.sysmail_unsentitems;

--- additional info

o solve the issue, please follow these steps:

1.Enabled the mail service queue:
USE msdb
GO
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
2.Delete the undeliverable emails.
DELETE FROM sysmail_unsentitems

Job Done!

Taking a SQL Server Database Off-Line :: BlackWasp Software Development

Taking a SQL Server Database Off-Line :: BlackWasp Software Development

SQL Profiler Access in SQL SERVER 2005/2008

In SQL Server 2005/2008 the good feature is a SQL User can run the PROFILER or Trace without having SYSADMIN Privilege.

As per Volume Data policy Developer doen’t have SYSADMIN priviledge on UAT&LIVE SQL Instances.

At the same time few developers might be interested to perform same using SQL Server Management Studio (SSMS).

Below are the steps to accomplish this task without giving SYSADMIN privilege.

SSMS àunder Security tab à Logins à right click name to modify select properties à securables tab à Click Search à Select the instance you want to add the permissions to. à Grant ALTER TRACE permission.

The permissions appear in the Effective Permissions list

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

But this privilege revoked automatically in some cases it's a bug.

That’s it Job done!

SQL Server Backup Solution:

SQL Server Backup Solution:

This is the traditional backup solution by running backup script on regular intervals.

SQLServerMaintenanceSolution.sql . This script is used for Sql Server Backup, Integrity Check and Index Optimization.

The Solution is supported on SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

To implement this solution run this script once in the SQL Instance with the default Master database. Before you run the script, you have to modify following variables for the SQL backup location.

SET @BackupDirectory = N'C:\Backup' -- <== Change this to your backup directory.

SET @CreateJobs = 'Y' -- <== should jobs be created, 'Y' or 'N'?

This Script creates following Objects in Master Database:

Temporary Table:

1. #Config

Procedures:

1. dbo.CommandExecute

2. dbo.DatabaseBackup

3. dbo.DatabaseIntegrityCheck

4. dbo.IndexOptimize

Functions:

1. dbo.DatabaseSelect

Schedular Jobs:

1. DatabaseBackup - SYSTEM_DATABASES– FULL

2. DatabaseBackup - USER_DATABASES – FULL

3. DatabaseBackup - USER_DATABASES – DIFF

4. DatabaseBackup - USER_DATABASES-LOG

5. DatabaseIntegrityCheck - SYSTEM_DATABASES

6. DatabaseIntegrityCheck - USER_DATABASES

7. IndexOptimize - USER_DATABASES

8. Output File Cleanup

After running above scirpt schedule the Database backup/other job as per company’s backup policy.

That’s it, Job Done!

Display only given number of records per page in SQL Server Reporting Services 2008 (SSRS)

If you want to display only 20 rows per report page, it is not easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports.

Let us say, you want to have only 20 rows per page, add a group to the report with following expression.

=int((RowNumber(Nothing)-1)/20)

FYI = sometimes excel have limitation of 65k rows per sheet . so you can use this solution for those issues as well.

reports. Add following lines to "Group" option of table "properties" section with "Page break at end".

job done!

Regards

Hemesh.

SQL SERVER Disk Space Alert

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to "FreeSpaceAlertMails"

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON
DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = 'E'

SET @alertMessage = '(host:jshah.sqldbpool.com)E:\ Disk Space Critical. Free Space Available on E:\ Drive is ' + CAST(@availableSpace AS VARCHAR) + 'GB'

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'FreeSpaceAlertMails',
@recipients = 'jshah143@gmail.com',
@body = @alertMessage,
@importance = 'High',
@subject = 'host:jshah.sqldbpool.com Disk Critical E Drive' ;
END

DROP TABLE #tbldiskSpace

END

Step 3: Create the job which will execute the above procedure at every 2 hours interval.