Wednesday, August 15, 2012

Where DEK in SQL TDE stored, and when it created

It's stored in the boot record (page 0 of file 1) of encrypted database. It created when database encryption turn on.

Below is the testing scenarios.

CREATE DATABASE [TDE]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TDE', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TDE_log', FILENAME = N'C:\SQL2012\MSSQL11.PDB\MSSQL\DATA\TDE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
DBCC TRACEON(3604)
DBCC PAGE (TDE, 1, 0, 3);



DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:0)


BUFFER:


BUF @0x0000000003BE0E40

bpage = 0x000000016FCFC000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 2526                        bstat = 0x9
blog = 0x1215a                      bnext = 0x0000000000000000         

PAGE HEADER:


Page @0x000000016FCFC000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7029                    m_freeData = 1161
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -734960185
DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED          

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065                 
Memory Dump @0x000000003C15A060

0000000000000000:   30000800 00000000 31000000 00000000 002e007f  0.......1...........
0000000000000014:   007f0081 00830087 008b008f 0093009d 00a700b1  .....ƒ.‡.‹...“...§.±
0000000000000028:   00b100b5 00b900bd 00c100cb 00e700f1 00fb0005  .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C:   0115011f 012f0133 013d013d 01430153 01530153  ...../.3.=.=.C.S.S.S
0000000000000050:   01530153 01530153 01630163 0163016d 01770193  .S.S.S.S.c.c.c.m.w.“
0000000000000064:   019d01ad 01c901d1 012904c2 98df59fe efd44db3  ...­.É.Ñ.).Â.ßYþïÔM³
0000000000000078:   d0b304a0 913a1301 00010080 010000ff ffffff80  г. ‘:.........ÿÿÿÿ.
000000000000008C:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000A0:   00000000 00000000 00000000 00000000 00800100  ....................
00000000000000B4:   00000000 00ffffff ff000200 00000000 00000000  .....ÿÿÿÿ...........
00000000000000C8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000DC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000F0:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000104:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000118:   00000000 0000004d c664e895 a00048a9 77bd01c9  .......MÆdè• .H©w½.É
000000000000012C:   93d0aa00 00000000 00000000 00000000 00540044  “Ъ..............T.D
0000000000000140:   00450000 00000000 00000000 00000000 00000000  .E..................
0000000000000154:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000168:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000017C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000190:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001A4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001B8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001CC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001E0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001F4:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000208:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000021C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000230:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000244:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000258:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000026C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000280:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000294:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002A8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002BC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002D0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002E4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002F8:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000030C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000320:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000334:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000348:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000035C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000370:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000384:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000398:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003AC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003C0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003D4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003E8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003FC:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000410:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000424:   00000000 00                                   ..... 
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13                         FileIdProp = 1
FileGroupId = 1                     Size = 384                          MaxSize = 65535
Growth = 128                        Perf = 0                            BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0)            OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]
MinSize = 384                       Status = 0                          UserShrinkSize = 65535
SectorSize = 512                    MaxLsn = (0:0:0)                    FirstLsn = (0:0:0)
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (0:0:0)      
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000              FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa                        RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0)       RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]       TxfBackupLsn = [NULL]
FstrContainerSize = [NULL]          MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)              RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000      
RestorePathOrigin

hex (dec) = 0x00000000:00000000:0000 (0:0:0)                            
m_guid = 00000000-0000-0000-0000-000000000000                           
DatabaseEncryptionFileState.m_maxScannedPage = 0                         DatabaseEncryptionFileState.m_keyId = 0

FCBFileDEK

m_dbeStatusBits = 0                 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000                           
m_dtLastModified = 1900-01-01 00:00:00.000                               m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0                         m_algId = 0
m_dwBitLen = 0                      m_cbThumbprint = 0                  m_rgbThumbprint = 0x


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO


PAGE: (1:0)


BUFFER:


BUF @0x0000000003BE0E40

bpage = 0x000000016FCFC000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 2652                        bstat = 0x9
blog = 0x1215a                      bnext = 0x0000000000000000         

PAGE HEADER:


Page @0x000000016FCFC000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7029                    m_freeData = 1161
m_reservedCnt = 0                   m_lsn = (0:0:1)                     m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -734960185
DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED          

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065                 
Memory Dump @0x000000003C15A060

0000000000000000:   30000800 00000000 31000000 00000000 002e007f  0.......1...........
0000000000000014:   007f0081 00830087 008b008f 0093009d 00a700b1  .....ƒ.‡.‹...“...§.±
0000000000000028:   00b100b5 00b900bd 00c100cb 00e700f1 00fb0005  .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C:   0115011f 012f0133 013d013d 01430153 01530153  ...../.3.=.=.C.S.S.S
0000000000000050:   01530153 01530153 01630163 0163016d 01770193  .S.S.S.S.c.c.c.m.w.“
0000000000000064:   019d01ad 01c901d1 012904c2 98df59fe efd44db3  ...­.É.Ñ.).Â.ßYþïÔM³
0000000000000078:   d0b304a0 913a1301 00010080 010000ff ffffff80  г. ‘:.........ÿÿÿÿ.
000000000000008C:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000A0:   00000000 00000000 00000000 00000000 00800100  ....................
00000000000000B4:   00000000 00ffffff ff000200 00000000 00000000  .....ÿÿÿÿ...........
00000000000000C8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000DC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000F0:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000104:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000118:   00000000 0000004d c664e895 a00048a9 77bd01c9  .......MÆdè• .H©w½.É
000000000000012C:   93d0aa00 00000000 00000000 00000000 00540044  “Ъ..............T.D
0000000000000140:   00450000 00000000 00000000 00000000 00000000  .E..................
0000000000000154:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000168:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000017C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000190:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001A4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001B8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001CC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001E0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001F4:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000208:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000021C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000230:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000244:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000258:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000026C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000280:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000294:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002A8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002BC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002D0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002E4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000002F8:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000030C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000320:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000334:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000348:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000035C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000370:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000384:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000398:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003AC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003C0:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003D4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003E8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000003FC:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000410:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000424:   00000000 00                                   ..... 
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13                         FileIdProp = 1
FileGroupId = 1                     Size = 384                          MaxSize = 65535
Growth = 128                        Perf = 0                            BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0)            OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]
MinSize = 384                       Status = 0                          UserShrinkSize = 65535
SectorSize = 512                    MaxLsn = (0:0:0)                    FirstLsn = (0:0:0)
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (0:0:0)      
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000              FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa                        RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0)       RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]       TxfBackupLsn = [NULL]
FstrContainerSize = [NULL]          MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)              RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000      
RestorePathOrigin

hex (dec) = 0x00000000:00000000:0000 (0:0:0)                            
m_guid = 00000000-0000-0000-0000-000000000000                           
DatabaseEncryptionFileState.m_maxScannedPage = 0                         DatabaseEncryptionFileState.m_keyId = 0

FCBFileDEK

m_dbeStatusBits = 0                 m_dtCreated = 1900-01-01 00:00:00.000
m_dtLastRegenerated = 1900-01-01 00:00:00.000                           
m_dtLastModified = 1900-01-01 00:00:00.000                               m_dtLastSet = 1900-01-01 00:00:00.000
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 0                         m_algId = 0
m_dwBitLen = 0                      m_cbThumbprint = 0                  m_rgbThumbprint = 0x


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


ALTER DATABASE TDE
SET ENCRYPTION ON;
GO
DBCC PAGE (TDE, 1, 0, 3);
GO


PAGE: (1:0)


BUFFER:


BUF @0x0000000003BE0E40

bpage = 0x000000016FCFC000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 2801                        bstat = 0x9
blog = 0x7a7a9a7a                   bnext = 0x0000000000000000         

PAGE HEADER:


Page @0x000000016FCFC000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7029                    m_freeData = 7551
m_reservedCnt = 0                   m_lsn = (41:453:2)                  m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -26638716
DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED          

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1065                 
Memory Dump @0x000000003F8AB956

0000000000000000:   30000800 00000000 31000000 00000000 002e007f  0.......1...........
0000000000000014:   007f0081 00830087 008b008f 0093009d 00a700b1  .....ƒ.‡.‹...“...§.±
0000000000000028:   00b100b5 00b900bd 00c100cb 00e700f1 00fb0005  .±.µ.¹.½.Á.Ë.ç.ñ.û..
000000000000003C:   0115011f 012f0133 013d013d 01430153 01530153  ...../.3.=.=.C.S.S.S
0000000000000050:   01530153 01530153 01630163 0163016d 01770193  .S.S.S.S.c.c.c.m.w.“
0000000000000064:   019d01ad 01c901d1 012904c2 98df59fe efd44db3  ...­.É.Ñ.).Â.ßYþïÔM³
0000000000000078:   d0b304a0 913a1301 00010080 020000ff ffffff80  г. ‘:.........ÿÿÿÿ.
000000000000008C:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000A0:   00000000 00000000 00000000 00000000 00800100  ....................
00000000000000B4:   00000000 00ffffff ff000200 00000000 00000000  .....ÿÿÿÿ...........
00000000000000C8:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000DC:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000000F0:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000104:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000118:   00000000 0000004d c664e895 a00048a9 77bd01c9  .......MÆdè• .H©w½.É
000000000000012C:   93d0aa00 00000000 00000000 00000000 00540044  “Ъ..............T.D
0000000000000140:   00450000 00000000 00000000 00000000 00000000  .E..................
0000000000000154:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000168:   00000000 00000000 00000000 00000000 00000000  ....................
000000000000017C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000000000190:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001A4:   00000000 00000000 00000000 00000000 00000000  ....................
00000000000001B8:   00000000 00000000 00000000 00000000 007f0200  ....................
00000000000001CC:   00010000 0023ff94 6f010000 0001ff94 6f010000  .....#ÿ”o.....ÿ”o...
00000000000001E0:   004917f3 00aea000 004917f3 00aea000 004917f3  .I.ó.® ..I.ó.® ..I.ó
00000000000001F4:   00aea000 00a3d5f3 00aea000 00011e94 6f140000  .® ..£Õó.® ....”o...
0000000000000208:   00398846 e0829a98 1e2b96ef 99a9a14a 26607762  .9.Fà‚š..+–ï.©¡J&`wb
000000000000021C:   91800000 00c7561c 270e5961 ffcc9a71 e061aa6e  ‘....ÇV.'.YaÿÌšqàaªn
0000000000000230:   38b63e24 b7946d6c 9e354e8f a12efe24 5e91f5db  8¶>$·”mlž5N.¡.þ$^‘õÛ
0000000000000244:   559890ee b0b806c1 8f380331 e3cbc65e 4ae05fb7  U..î°¸.Á.8.1ãËÆ^Jà_·
0000000000000258:   863504fe a8f6e963 60c2b263 b3643a18 4479a95d  †5.þ¨öéc`²c³d:.Dy©]
000000000000026C:   367c47d9 c335f285 a7e1bb96 9d30e5c0 a98869ca  6|GÙÃ5ò…§á»–.0åÀ©.iÊ
0000000000000280:   dc807b44 0724be52 29cbbdc5 202a754c b1870d83  Ü.{D.$¾R)˽Š*uL±‡.ƒ
0000000000000294:   9de05bd6 4dcb6cad 4de45248 81400044 6e010000  .à[ÖMËl­MäRH.@.Dn...
00000000000002A8:   00909d94 6f010000 00409c76 eefe0700 00010000  ...”o....@œvîþ......
00000000000002BC:   00010000 00400044 6e010000 00000000 00000000  .....@.Dn...........
00000000000002D0:   00000000 00000000 00803e95 6f010000 00000000  ..........>•o.......
00000000000002E4:   00000000 0040b542 6e010000 00000000 00000000  .....@µBn...........
00000000000002F8:   00030000 000000f0 3f000000 00000000 00807a59  .......ð?.........zY
000000000000030C:   6f010000 00805d95 6f010000 00000000 000000f0  o.....]•o..........ð
0000000000000320:   3f400044 6e010000 00e0a244 6f010000 00007844  ?@.Dn....à¢Do.....xD
0000000000000334:   6e010000 0020a244 6f010000 00c00a77 eefe0700  n.... ¢Do....À.wîþ..
0000000000000348:   00140000 00010000 0040208d 6f010000 0040a044  .........@ .o....@ D
000000000000035C:   6f150000 00150000 00010000 00a01ebc 6f010000  o............ .¼o...
0000000000000370:   00003e00 00fe0700 00000000 0000003f 40000000  ..>..þ.........?@...
0000000000000384:   00000000 00000000 00010000 00010000 00000000  ....................
0000000000000398:   00000000 00010000 00005579 eefe0700 00010000  ..........Uyîþ......
00000000000003AC:   00000000 00400044 6e010000 00100b77 eefe0700  .....@.Dn......wîþ..
00000000000003C0:   00010000 00000000 00400044 6e010000 00000a77  .........@.Dn......w
00000000000003D4:   ee030000 00040000 00000000 00209994 6f010000  î............ .”o...
00000000000003E8:   00109f79 eefe0700 00010000 00000000 00400044  ..Ÿyîþ...........@.D
00000000000003FC:   6e010000 00000000 00030000 00040000 00000000  n...................
0000000000000410:   00f06692 6f010000 00400044 6e000000 000e6600  .ðf’o....@.Dn.....f.
0000000000000424:   00800000 00                                   ..... 
BindingID = 59df98c2-effe-4dd4-b3d0-b304a0913a13                         FileIdProp = 1
FileGroupId = 1                     Size = 640                          MaxSize = 65535
Growth = 128                        Perf = 0                            BackupLsn = (0:0:0)
FirstUpdateLsn = (0:0:0)            OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]
MinSize = 384                       Status = 0                          UserShrinkSize = 65535
SectorSize = 512                    MaxLsn = (0:0:0)                    FirstLsn = (0:0:0)
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (0:0:0)      
DifferentialBaseGuid = 00000000-0000-0000-0000-000000000000              FileOfflineLsn = (0:0:0)
FileIdGuid = e864c64d-a095-4800-a977-bd01c993d0aa                        RestoreStatus = 0
RestoreRedoStartLsn = (0:0:0)       RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]       TxfBackupLsn = [NULL]
FstrContainerSize = [NULL]          MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)              RestoreDifferentialBaseLsn = (0:0:0)
RestoreDifferentialBaseGuid = 00000000-0000-0000-0000-000000000000      
RestorePathOrigin

hex (dec) = 0x00000000:00000000:0000 (0:0:0)                            
m_guid = 00000000-0000-0000-0000-000000000000                           
DatabaseEncryptionFileState.m_maxScannedPage = 639                       DatabaseEncryptionFileState.m_keyId = 1

FCBFileDEK

m_dbeStatusBits = 35                m_dtCreated = 2012-08-15 14:45:04.030
m_dtLastRegenerated = 2012-08-15 14:45:04.030                           
m_dtLastModified = 2012-08-15 14:45:04.030                               m_dtLastSet = 2012-08-15 14:47:46.463
m_dtOpened = 1900-01-01 00:00:00.000m_algId = 26126                     m_algId = AES
m_dwBitLen = 128                    m_cbThumbprint = 20                
m_rgbThumbprint = 0x398846E0829A981E2B96EF99A9A14A2660776291            


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Monday, August 13, 2012

Sample script to test OGG "DBOPTIONS SUPPRESSTRIGGERS"

create table hr.tbl_trigger_test
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_pk primary key(id))
;

create or replace trigger hr.trg_trigger_test
before insert on hr.tbl_trigger_test
for each row
begin
:new.updated_host:=sys_context('userenv','host');
:new.updated_user:=sys_context('userenv','session_user');
:new.updated_time:=systimestamp;
end;
/

create table hr.tbl_trigger_test_2
(id number,
updated_host varchar2(100),
updated_user varchar2(30),
updated_time timestamp,
constraint tbl_trigger_test_2_pk primary key(id))
;

create or replace trigger hr.trg_trigger_test_2
after insert on hr.tbl_trigger_test
REFERENCING NEW AS newRow
for each row
begin
   insert into hr.tbl_trigger_test_2 values(
 :newRow.id,:newRow.updated_host,:newRow.updated_user,:newRow.updated_time);
end;
/


insert into hr.tbl_trigger_test values ('1',null,null,null);
insert into hr.tbl_trigger_test values ('2',null,null,null);
insert into hr.tbl_trigger_test values ('3',null,null,null);
insert into hr.tbl_trigger_test values ('4',null,null,null);
insert into hr.tbl_trigger_test values ('5',null,null,null);
commit;

drop trigger hr.trg_trigger_test_2;
drop trigger hr.trg_trigger_test;
drop table hr.tbl_trigger_test_2 purge;
drop table hr.tbl_trigger_test purge;