Bo's Oracle Station

查看: 3197|回复: 0

课程第27次(2018-8-13星期一)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-13 19:59:48 | 显示全部楼层 |阅读模式
表空间级别的TDE:
  1. SQL> select  * from v$encryption_wallet;

  2. WRL_TYPE
  3. --------------------
  4. WRL_PARAMETER
  5. --------------------------------------------------------------------------------
  6. STATUS
  7. ------------------
  8. file
  9. /u01/app/oracle/admin/orcl/wallet
  10. CLOSED


  11. SQL> alter system set encryption key identified by "oracle123";

  12. System altered.

  13. SQL> select  * from v$encryption_wallet;

  14. WRL_TYPE
  15. --------------------
  16. WRL_PARAMETER
  17. --------------------------------------------------------------------------------
  18. STATUS
  19. ------------------
  20. file
  21. /u01/app/oracle/admin/orcl/wallet
  22. OPEN


  23. SQL> conn hr/oracle_4U
  24. Connected.
  25. SQL> create table tnew08_a ( a  number ) ;

  26. Table created.

  27. SQL> alter table tnew08_a  modify ( a  encrypt using '3Des168' salt ) ;

  28. Table altered.

  29. SQL>
复制代码
  1. select  object_id from dba_objects o
  2. where o.object_name='TNEW08_A';
复制代码
   OBJECT_ID
174741

  1. select  * from dba_encrypted_columns;
复制代码
   OWNERTABLE_NAMECOLUMN_NAMEENCRYPTION_ALGSALTINTEGRITY_ALG
1HRTNEW08_AA3 Key Triple DES 168 bits keyYESSHA-1

  1. select  * from enc$;
复制代码
   OBJ#OWNER#MKEYIDENCALGINTALGCOLKLCKLCLENFLAG
1747411AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1141774141414141414141414141414141414141414141432B42426B7A796F3941384366696E417554552F49774137524A6635767174346756556F566F776D6F664F4D733358307565544E664276427A7051676561412B673D88

TDE的原理就是 master key 来加密 AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  , AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 再来加密tnew08_a。

RMAN TDE就是:
  1. [oracle@station36 wallet]$ rman target /

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 12 20:12:15 2018

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

  4. connected to target database: ORCL (DBID=1507385682)

  5. RMAN> show all;

  6. using target database control file instead of recovery catalog
  7. RMAN configuration parameters for database with db_unique_name ORCL are:
  8. CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
  9. CONFIGURE BACKUP OPTIMIZATION OFF; # default
  10. CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
  11. CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
  12. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  13. CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
  14. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  15. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  16. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  17. CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  18. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  19. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  20. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  21. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/acfsmounts/acfs_db1/dbs/snapcf_orcl.f'; # default

  22. RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;

  23. new RMAN configuration parameters:
  24. CONFIGURE ENCRYPTION FOR DATABASE ON;
  25. new RMAN configuration parameters are successfully stored

  26. RMAN> backup tablespace users;

  27. Starting backup at 12-AUG-18
  28. allocated channel: ORA_DISK_1
  29. channel ORA_DISK_1: SID=70 device type=DISK
  30. channel ORA_DISK_1: starting full datafile backup set
  31. channel ORA_DISK_1: specifying datafile(s) in backup set
  32. input datafile file number=00004 name=+DATA/orcl/datafile/users.263.981998613
  33. channel ORA_DISK_1: starting piece 1 at 12-AUG-18
  34. channel ORA_DISK_1: finished piece 1 at 12-AUG-18
  35. piece handle=+FRA/orcl/backupset/2018_08_12/nnndf0_tag20180812t201252_0.305.983995973 tag=TAG20180812T201252 comment=NONE
  36. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
  37. Finished backup at 12-AUG-18

  38. RMAN>
复制代码

主密钥 来 加密备份集头部里的密钥 , 备份集头部里的密钥再来加密备份集。

要想改主密钥:
1.png

  1. SQL> create tablespace tbsenc datafile size 5M encryption  default storage  (encrypt );

  2. Tablespace created.

  3. SQL> alter table hr.tnew08_a move tablespace tbsenc;

  4. Table altered.

  5. SQL> alter table hr.t_nocompression  move tablespace tbsenc;

  6. Table altered.

  7. SQL> conn / as sysdba
  8. Connected.
  9. SQL> alter system set encryption wallet close identified by "oracle456";

  10. System altered.

  11. SQL> conn hr/oracle_4U
  12. Connected.
  13. SQL> select  * from hr.tnew08_a;
  14. select        * from hr.tnew08_a
  15.                   *
  16. ERROR at line 1:
  17. ORA-28365: wallet is not open


  18. SQL> select  * from hr.t_nocompression;
  19. select        * from hr.t_nocompression
  20.                   *
  21. ERROR at line 1:
  22. ORA-28365: wallet is not open


  23. SQL>
复制代码
  1. select  * from V$ENCRYPTED_TABLESPACES;
复制代码
   TS#ENCRYPTIONALGENCRYPTEDTS
113AES128YES

  1. select  * from v$tablespace where ts#=13;
复制代码
   TS#NAMEINCLUDED_IN_DATABASE_BACKUPBIGFILEFLASHBACK_ONENCRYPT_IN_BACKUP
113TBSENCYESNOYES

非标准路径下的TDE钱包:
2.png

编辑sqlnet.ora:
  1. ENCRYPTION_WALLET_LOCATION =
  2. (SOURCE =
  3. (METHOD = FILE)
  4. (METHOD_DATA =
  5. (DIRECTORY = /u01/app/oracle/wallet)
  6. )
  7. )

复制代码


ADDM的命令行定制:
能够加的ADDM过滤器:
  1. select  * from DBA_ADVISOR_FINDING_NAMES;
复制代码
数据库的粗略的83个问题:
   IDADVISOR_NAMEFINDING_NAME
10Default Advisornormal, successful completion
21ADDM"Administrative" Wait Class
32ADDM"Application" Wait Class
43ADDM"Cluster" Wait Class
54ADDM"Concurrency" Wait Class
65ADDM"Configuration" Wait Class
76ADDM"Network" Wait Class
87ADDM"Other" Wait Class
98ADDM"Scheduler" Wait Class
109ADDM"User I/O" wait Class
1110ADDMBuffer Busy
1211ADDMBuffer Cache Latches
1312ADDMCheckpoints Due to DROP or TRUNCATE
1413ADDMCheckpoints Due to Log File Size
1514ADDMCheckpoints Due to MTTR
1615ADDMCheckpoints Due to Parallel Queries
1716ADDMCheckpoints Due to Tablespace DDL
1817ADDMCommits and Rollbacks
1918ADDMCPU Usage
2019ADDMDBMS_LOCK Usage
2120ADDMDBMS_PIPE Usage
2221ADDMExcessive Rebinds
2322ADDMFree Buffer Waits
2423ADDMHard Parse
2524ADDMHard Parse Due to Invalidations
2625ADDMHard Parse Due to Literal Usage
2726ADDMHard Parse Due to Parse Errors
2827ADDMHard Parse Due to Sharing Criteria
2928ADDMHigh Watermark Waits
3029ADDMI/O Throughput
3130ADDMIndex Block Split
3231ADDMGlobal Cache Busy
3332ADDMGlobal Cache Congestion
3433ADDMInterconnect Latency
3534ADDMGlobal Cache Lost Blocks
3635ADDMGlobal Cache Messaging
3736ADDMGlobal Cache Multiblock Requests
3837ADDMITL Waits
3938ADDMJava Execution
4039ADDMLatch Free Waits
4140ADDMLog File Switches
4241ADDMPL/SQL Compilation
4342ADDMPL/SQL Execution
4443ADDMRMAN I/O
4544ADDMRow Lock Waits
4645ADDMSequence Usage
4746ADDMSession Connect and Disconnect
4847ADDMSession Slot Scheduling
4948ADDMShared Pool Latches
5049ADDMSlow Archivers
5150ADDMSoft Parse
5251ADDMSpace Transaction Waits
5352ADDMStreams Flow Control
5453ADDMTable Locks
5554ADDMTemp Space Contention
5655ADDMTop Segments by I/O
5756ADDMTop SQL by "Cluster" Wait
5857ADDMTop SQL by DB Time
5958ADDMTop SQL By I/O
6059ADDMUndersized Buffer Cache
6160ADDMUndersized PGA
6261ADDMUndersized Redo Log Buffer
6362ADDMUndersized SGA
6463ADDMUndersized Shared Pool
6564ADDMUndersized Streams Pool
6665ADDMUndo I/O
6766ADDMUnusual "Administrative" Wait Event
6867ADDMUnusual "Application" Wait Event
6968ADDMUnusual "Cluster" Wait Event
7069ADDMUnusual "Commit" Wait Event
7170ADDMUnusual "Concurrency" Wait Event
7271ADDMUnusual "Configuration" Wait Event
7372ADDMUnusual "Network" Wait Event
7473ADDMUnusual "Other" Wait Event
7574ADDMUnusual "Scheduler" Wait Event
7675ADDMUnusual "User I/O" Wait Event
7776ADDMVirtual Memory Paging
7877ADDMUnusual "Queueing" Wait Event
7978ADDM"Queueing" Wait Class
8079ADDMUndersized instance memory
8180ADDMTop SQL Statements
8281ADDMTop Segments by "User I/O" and "Cluster"
8382ADDMBuffer Busy - Hot Block
8483ADDMBuffer Busy - Hot Objects


某个会话可以临时向操作系统借用排序空间500M:
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> show parameter work_

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. fileio_network_adapters              string
  7. listener_networks                     string
  8. workarea_size_policy                     string         AUTO
  9. SQL> alter session set workarea_size_policy=manual;

  10. Session altered.

  11. SQL> show parameter sort_

  12. NAME                                     TYPE         VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. sort_area_retained_size              integer         0
  15. sort_area_size                             integer         65536
  16. SQL> alter session set sort_area_size=524288000;

  17. Session altered.

  18. SQL>
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 16:31 , Processed in 0.050019 second(s), 27 queries .

快速回复 返回顶部 返回列表