API tools faq
paste
ExaGridDba

12.2 non-cdb on-prem decrypt a tablespace

ExaGridDba
Nov 14th, 2017
130
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.46 KB | None | 0 0
raw download clone embed print report
  1. 12.2
  2. On prem
  3. non-cdb
  4. decrypt a tablespace
  5.  
  6. [oracle@stormking db12201 ~]$ export TNS_ADMIN=/u01/app/oracle/product/12.2.0/dbhome_1/network/admin
  7. [oracle@stormking db12201 ~]$ cd $TNS_ADMIN
  8. [oracle@stormking db12201 admin]$ cat sqlnet.ora
  9.  
  10. encryption_wallet_location =
  11. (source =
  12. (method = file)
  13. (method_data =
  14. (directory = /u01/app/oracle/wallet/$ORACLE_SID/wallet)))
  15.  
  16.  
  17. [oracle@stormking db12201 admin]$ mkdir -p /u01/app/oracle/wallet/$ORACLE_SID/wallet
  18.  
  19. SQL> administer key management create keystore '/u01/app/oracle/wallet/db12201/wallet' identified by kspw;
  20.  
  21. keystore altered.
  22.  
  23. SQL> administer key management set keystore open identified by kspw;
  24.  
  25. keystore altered.
  26.  
  27. SQL> administer key management set key identified by kspw with backup;
  28.  
  29. keystore altered.
  30.  
  31. SQL> create table tcr ( a varchar2(30)) tablespace users;
  32.  
  33. Table created.
  34.  
  35.  
  36. SQL> insert into tcr ( a ) values ( rpad('A',30,'A' ) );
  37.  
  38. 1 row created.
  39.  
  40.  
  41. SQL> commit;
  42.  
  43. Commit complete.
  44.  
  45. SQL> select rowid, a from tcr;
  46.  
  47. ROWID A
  48. ------------------ ------------------------------
  49. AAAR9nAAHAAAACEAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  50.  
  51. [oracle@stormking db12201 buffercache]$ cat dump.rowid.block.sql
  52. define rowid=&&1
  53. column file_id new_value file_id
  54. column block_id new_value block_id
  55.  
  56. set linesize 100
  57. set trimspool on
  58.  
  59. select
  60. trim(dbms_rowid.rowid_relative_fno('&&rowid')) file_id,
  61. trim(dbms_rowid.rowid_block_number('&&rowid')) block_id
  62. from dual;
  63.  
  64. alter system checkpoint;
  65.  
  66. alter session set tracefile_identifier="dumpfno&&file_id._block&&block_id";
  67.  
  68. alter system dump datafile &&file_id block &&block_id;
  69.  
  70. @ tracefile.sql
  71.  
  72. [oracle@stormking db12201 buffercache]$ less /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_7820_dumpfno7_block132.trc
  73.  
  74. block_row_dump:
  75. tab 0, row 0, @0x1f76
  76. tl: 34 fb: --H-FL-- lb: 0x1 cc: 1
  77. col 0: [30]
  78. 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
  79. 41 41 41 41 41
  80. end_of_block_dump
  81.  
  82. SQL> create tablespace ts encryption using 'aes256' default storage ( encrypt );
  83.  
  84. Tablespace created.
  85.  
  86. SQL> alter table tcr move tablespace ts;
  87.  
  88. Table altered.
  89.  
  90. SQL> select rowid, a from tcr;
  91.  
  92. ROWID A
  93. ------------------ ------------------------------
  94. AAAR9oAAFAAAACDAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  95.  
  96.  
  97. [oracle@stormking db12201 buffercache]$ sysdba @ dump.rowid.block.sql AAAR9oAAFAAAACDAAA
  98.  
  99. SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 15 00:26:38 2017
  100.  
  101. Copyright (c) 1982, 2016, Oracle. All rights reserved.
  102.  
  103.  
  104. Connected to:
  105. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  106.  
  107. old 2: trim(dbms_rowid.rowid_relative_fno('&&rowid')) file_id,
  108. new 2: trim(dbms_rowid.rowid_relative_fno('AAAR9oAAFAAAACDAAA')) file_id,
  109. old 3: trim(dbms_rowid.rowid_block_number('&&rowid')) block_id
  110. new 3: trim(dbms_rowid.rowid_block_number('AAAR9oAAFAAAACDAAA')) block_id
  111.  
  112. FILE_ID BLOCK_ID
  113. ---------------------------------------- ----------------------------------------
  114. 5 131
  115.  
  116.  
  117. System altered.
  118.  
  119. old 1: alter session set tracefile_identifier="dumpfno&&file_id._block&&block_id"
  120. new 1: alter session set tracefile_identifier="dumpfno5_block131"
  121.  
  122. Session altered.
  123.  
  124. old 1: alter system dump datafile &&file_id block &&block_id
  125. new 1: alter system dump datafile 5 block 131
  126.  
  127. System altered.
  128.  
  129.  
  130. /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_8419_dumpfno5_block131.trc
  131. ...
  132. flags: encrypted_on_disk only_sequential_access block_written_once
  133. ...
  134. Block dump from disk:
  135. Decrypting encrypted buffer before dump.
  136. ...
  137. block_row_dump:
  138. tab 0, row 0, @0x1f5e
  139. tl: 34 fb: --H-FL-- lb: 0x0 cc: 1
  140. end_of_block_dump
  141.  
  142. SQL> select file_name from dba_data_files where tablespace_name = 'TS';
  143.  
  144. FILE_NAME
  145. --------------------------------------------------------------------------------
  146. +DATA/DB12201/DATAFILE/ts.257.960077917
  147.  
  148. SQL> alter tablespace ts encryption decrypt;
  149.  
  150. Tablespace altered.
  151.  
  152. SQL> select file_name from dba_data_files where tablespace_name = 'TS';
  153.  
  154. FILE_NAME
  155. --------------------------------------------------------------------------------
  156. +DATA/DB12201/DATAFILE/ts.295.960079179
  157.  
  158. /u01/app/oracle/diag/rdbms/db12201/db12201/trace/alert_db12201.log
  159.  
  160. 2017-11-15T00:39:37.937822-05:00
  161. alter tablespace ts encryption decrypt
  162. 2017-11-15T00:39:38.042792-05:00
  163. About to decrypt tablespace TS (tsn 0/6)
  164. Rekeying datafile +DATA/DB12201/DATAFILE/ts.257.960077917 (5) to +DATA
  165. 2017-11-15T00:39:50.518138-05:00
  166. Rekey operation committed for file +DATA/DB12201/DATAFILE/ts.295.960079179
  167. 2017-11-15T00:39:52.934430-05:00
  168. About to zero out original file "+DATA/DB12201/DATAFILE/ts.257.960077917"
  169. 2017-11-15T00:39:58.045933-05:00
  170. Successfully zero'ed out original file "+DATA/DB12201/DATAFILE/ts.257.960077917"
  171. Successfully deleted original file "+DATA/DB12201/DATAFILE/ts.257.960077917"
  172. Completed rekey for tablespace TS (tsn 0/6) from key version 0 to 1.
  173. Completed: alter tablespace ts encryption decrypt
  174.  
  175. [oracle@stormking db12201 buffercache]$ sysdba @ dump.rowid.block.sql AAAR9oAAFAAAACDAAA
  176.  
  177.  
  178. SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 15 00:44:10 2017
  179.  
  180. Copyright (c) 1982, 2016, Oracle. All rights reserved.
  181.  
  182.  
  183. Connected to:
  184. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  185.  
  186. old 2: trim(dbms_rowid.rowid_relative_fno('&&rowid')) file_id,
  187. new 2: trim(dbms_rowid.rowid_relative_fno('AAAR9oAAFAAAACDAAA')) file_id,
  188. old 3: trim(dbms_rowid.rowid_block_number('&&rowid')) block_id
  189. new 3: trim(dbms_rowid.rowid_block_number('AAAR9oAAFAAAACDAAA')) block_id
  190.  
  191. FILE_ID BLOCK_ID
  192. ---------------------------------------- ----------------------------------------
  193. 5 131
  194.  
  195. Notes:
  196. The rowid is the same across the decrypt.
  197. That implies that the file number remains unchanged.
  198. The ASM file name has changed.
  199.  
  200. System altered.
  201.  
  202. old 1: alter session set tracefile_identifier="dumpfno&&file_id._block&&block_id"
  203. new 1: alter session set tracefile_identifier="dumpfno5_block131"
  204.  
  205. Session altered.
  206.  
  207. old 1: alter system dump datafile &&file_id block &&block_id
  208. new 1: alter system dump datafile 5 block 131
  209.  
  210. System altered.
  211.  
  212.  
  213.  
  214. tab 0, row 0, @0x1f5e
  215. tl: 34 fb: --H-FL-- lb: 0x0 cc: 1
  216. col 0: [30]
  217. 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
  218. 41 41 41 41 41
  219. end_of_block_dump
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!