Script to find locks in a postgres database.

SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.GRANTED;

Script to find locks in SQL Server

SET NOCOUNT ON
GO

-- Count the locks

IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            WHERE   name LIKE '#Hold_sp_lock%' )
--If So Drop it
    DROP TABLE #Hold_sp_lock
GO
CREATE TABLE #Hold_sp_lock
    (
      spid INT,
      dbid INT,
      ObjId INT,
      IndId SMALLINT,
      Type VARCHAR(20),
      Resource VARCHAR(50),
      Mode VARCHAR(20),
      Status VARCHAR(20)
    )
INSERT  INTO #Hold_sp_lock
        EXEC sp_lock
SELECT  COUNT(spid) AS lock_count,
        SPID,
        Type,
        Cast(DB_NAME(DBID) as varchar(30)) as DBName,
        mode
FROM    #Hold_sp_lock
GROUP BY SPID,
        Type,
        DB_NAME(DBID),
        MODE
Order by lock_count desc,
        DBName,
        SPID,
        MODE

--Show any blocked or blocking processes

IF EXISTS ( SELECT  Name
            FROM    tempdb..sysobjects
            Where   name like '#Catch_SPID%' )
--If So Drop it
    DROP TABLE #Catch_SPID
GO
Create Table #Catch_SPID
    (
      bSPID int,
      BLK_Status char(10)
    )
GO
Insert  into #Catch_SPID
        Select Distinct
                SPID,
                'BLOCKED'
        from    master..sysprocesses
        where   blocked <> 0
        UNION
        Select Distinct
                blocked,
                'BLOCKING'
        from    master..sysprocesses
        where   blocked <> 0

DECLARE @tSPID int
DECLARE @blkst char(10)
SELECT TOP 1
        @tSPID = bSPID,
        @blkst = BLK_Status
from    #Catch_SPID

WHILE( @@ROWCOUNT > 0 )
    BEGIN

        PRINT 'DBCC Results for SPID ' + Cast(@tSPID as varchar(5)) + '( '
            + rtrim(@blkst) + ' )'
        PRINT '-----------------------------------'
        PRINT ''
        DBCC INPUTBUFFER(@tSPID)


        SELECT TOP 1
                @tSPID = bSPID,
                @blkst = BLK_Status
        from    #Catch_SPID
        WHERE   bSPID > @tSPID
        Order by bSPID

    END

Comparison of Oracle 9i and 10G

Parameter / Metrics	Oracle 9i	Oracle 10G / 11G
SQL Optimizer	Both Cost Based and Rule Based Optimizers can be used	Cost Based Optimizer is used as a Default option. Rule Based cannot be used.
Data Export and Import	Can be done with the help of Export / Import feature	Comes with an additional feature of Oracle Data Pump.
Job Scheduling	Done by DBMS_JOB	Done with the help of DBMS_SCHEDULER
Table Space Point In Time Recovery(TSPITR)	Auxiliary Instance needs to be created Manually	Auxiliary Instance Automatically created while creation of Database or by using ReecoveryManager.
Database or Table space Roll back	Done with the Help of HOT / COLD backup of the database and recovery is required.	Roll back of the database or the table space can be done by enabling the Flashback database feature
Backup and Retention periods	The Old Backups are deleted manually or with the help of third party backup software.	The old Backups can be deleted automatically with the help of RMAN by setting the BAACKUP RETENTION POLICY PERIOD
Size of Backup Sets	Backup set compression cannot be done	Backup set can be reduced with the help of RMAN compression
Check point and UNDO tuning	These Parameters needs to be tuned based on the Performance of the Database manually. SQL tuning is done with the help of PGA_AGGREGATE_TARGET	Automatic tuning and SQL Tuning Advisories are available in 10G.
Database Performance Monitoring and tuning	Can be done with the help of STATSPACK and manually interpreting the same.	New feature called ADDM (Automatic Database Diagnostic Monitor) can be used for the collection of DBSTATS.
SGA and PGA Tuning	This is done manually based on the workload. Buffer Cache advisory needs to be turned ON to know the optimal value of the Buffer Cache.	Adjusted automatically based on workload.
Movement of Data files	Cannot be done.	Available and can be moved across platforms.
Segment Space Management	Use ASSM (Automatic Segment Space Management).Free space within the blocks is managed by bit-maps stored in the bitmap blocks in the given segment. Improves the performance in case of concurrent DML operations because respective operations can access different bitmap blocks reducing the contention on segment header.	ASSM available in 10g. Features are introduced which are built on ASSM e.g. qualifying segments for reclaiming free space, shrinking segments. These are useful features for avoiding the wastage of space resulted due to frequent DELETE operations.
Database Reorg	a) Move the tables within the table space or across table spaces b) using Exp/Imp.   Limitation of moving the object is that new object is created first and then the old object is dropped so in case of 10GB table, 10GB of free space must be present. Time is a major constraint for Exp/Imp operation.	No new object is created. The rows are physically moved to the blocks having adequate free space provided Automatic Segment Space Management is being used. This is less time consuming and needs no extra space.
Execute OS commands / Job Scheduling	Use JAVA stored procedures to execute OS command.	No need of developing JAVA stored procedures. New API i.e. DBMS_SCHEDULER has been introduced.
Rename table space.	Not Available.	Table space can be Renamed

Backup and Restore of ASM instance

Introduction:

This document provides a high level overview and steps to restore a ASM database .

Steps:

1.      Take a backup of the database or else use the last successful backup from tape / disk

2.      Created the parameter file with the bare minimum parameters for the database to startup.

vi initTEST1.ora

*.audit_file_dest='/u01/app/oracle/admin/TEST1/adump'

*.audit_trail='db'

*.compatible='11.2.0.2.0'

*.control_files='+DATA','+FRA'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_create_online_log_dest_1='+DATA'

*.db_name='RAC'

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_dest_1='LOCATION=+FRA'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1658847232

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.cluster_database=false

3.      Start the database in NOMOUNT stage for restore and recovery from the backup:

[oracle@MYHOST1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 1 05:58:30 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='initTEST1.ora'

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                  2213896 bytes

Variable Size             956303352 bytes

Database Buffers          687865856 bytes

Redo Buffers                7135232 bytes

SQL> exit

4.      Connect to the Oracle Recovery Manager and restore the  control file associated with the recent backup that is planned to be restored.

Connect to RMAN:

[oracle@MYHOST1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 1 05:59:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (not mounted)

RMAN> restore controlfile from autobackup

Starting restore at 17-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=29 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

output file name=+DATA/TEST1/controlfile/current.236.7445645

output file name=+FRA/TEST1/controlfile/current.235.7445645

Finished restore at 17-JUN-14

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

5.      Initiate a restore of the database once the control file is restored and the DB is in mount state as shown below.

RMAN> restore database;

Starting restore at 17-JUN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=29 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA/TEST1/system.356.1123822

channel ORA_DISK_1: restoring datafile 00004 to +DATA/TEST1/users. 356.1223822

channel ORA_DISK_1: reading from backup piece

 channel ORA_DISK_1: piece

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to +DATA/rac/datafile/undotbs1.314.822277039

channel ORA_DISK_1: restoring datafile 00005 to +DATA/rac/datafile/undotbs2.315.822277039

channel ORA_DISK_1: reading from backup piece channel ORA_DISK_1: piece

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00002 to +DATA/ TEST1/sysaux.311.822277081

channel ORA_DISK_1: reading from backup piece

channel ORA_DISK_1: piece

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 17-JUN-14

Recover the database

RMAN> recover database;

Starting recover at 17-JUN-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file +FRA/TEST1/2014_06_17/thread_1_seq_8.412.822282953

archived log for thread 2 with sequence 6 is already on disk as file +FRA/TEST1/2014_06_17/thread_2_seq_6.413.822282955

archived log file name=+FRA/TEST1/2014_06_17/thread_1_seq_8.412.822282953 thread=1 sequence=8

archived log file name=+FRA/TEST1/2014_06_17/thread_2_seq_6.413.822282955 thread=2 sequence=6

archived log file name=+FRA/TEST1/2014_06_17/thread_1_seq_9.415.822287039 thread=1 sequence=9

archived log file name=+FRA/TEST1/2014_06_17/thread_2_seq_7.414.822286061 thread=2 sequence=7

unable to find archived log

archived log thread=2 sequence=8

6.      Open the database with resetlogs:

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

7.      Start the database

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2220200 bytes

Variable Size             616566616 bytes

Database Buffers          444596224 bytes

Redo Buffers                5554176 bytes

Database mounted.

Database opened.

8.      Once the DB is started, add the entries and start using the server control utility.