课程第27次（2018-8-13星期一）

botang

表空间级别的TDE：

1. SQL> select  * from v$encryption_wallet;
   
2. 
   
3. WRL_TYPE
   
4. --------------------
   
5. WRL_PARAMETER
   
6. --------------------------------------------------------------------------------
   
7. STATUS
   
8. ------------------
   
9. file
   
10. /u01/app/oracle/admin/orcl/wallet
    
11. CLOSED
    
12. 
    
13. 
    
14. SQL> alter system set encryption key identified by "oracle123";
    
15. 
    
16. System altered.
    
17. 
    
18. SQL> select  * from v$encryption_wallet;
    
19. 
    
20. WRL_TYPE
    
21. --------------------
    
22. WRL_PARAMETER
    
23. --------------------------------------------------------------------------------
    
24. STATUS
    
25. ------------------
    
26. file
    
27. /u01/app/oracle/admin/orcl/wallet
    
28. OPEN
    
29. 
    
30. 
    
31. SQL> conn hr/oracle_4U
    
32. Connected.
    
33. SQL> create table tnew08_a ( a  number ) ;
    
34. 
    
35. Table created.
    
36. 
    
37. SQL> alter table tnew08_a  modify ( a  encrypt using '3Des168' salt ) ;
    
38. 
    
39. Table altered.
    
40. 
    
41. SQL>
    

复制代码

1. select  object_id from dba_objects o
   
2. where o.object_name='TNEW08_A';

复制代码

	OBJECT_ID
1	74741

1. select  * from dba_encrypted_columns;

复制代码

	OWNER	TABLE_NAME	COLUMN_NAME	ENCRYPTION_ALG	SALT	INTEGRITY_ALG
1	HR	TNEW08_A	A	3 Key Triple DES 168 bits key	YES	SHA-1

1. select  * from enc$;

复制代码

	OBJ#	OWNER#	MKEYID	ENCALG	INTALG	COLKLC	KLCLEN	FLAG
1	74741	1	AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	1	1	41774141414141414141414141414141414141414141432B42426B7A796F3941384366696E417554552F49774137524A6635767174346756556F566F776D6F664F4D733358307565544E664276427A7051676561412B673D	88

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

RMAN TDE就是：

1. [oracle@station36 wallet]$ rman target /
   
2. 
   
3. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 12 20:12:15 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. connected to target database: ORCL (DBID=1507385682)
   
8. 
   
9. RMAN> show all;
   
10. 
    
11. using target database control file instead of recovery catalog
    
12. RMAN configuration parameters for database with db_unique_name ORCL are:
    
13. CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    
14. CONFIGURE BACKUP OPTIMIZATION OFF; # default
    
15. CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    
16. CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
    
17. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
    
18. CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
    
19. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    
20. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    
21. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    
22. CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    
23. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    
24. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
    
25. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    
26. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/acfsmounts/acfs_db1/dbs/snapcf_orcl.f'; # default
    
27. 
    
28. RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;
    
29. 
    
30. new RMAN configuration parameters:
    
31. CONFIGURE ENCRYPTION FOR DATABASE ON;
    
32. new RMAN configuration parameters are successfully stored
    
33. 
    
34. RMAN> backup tablespace users;
    
35. 
    
36. Starting backup at 12-AUG-18
    
37. allocated channel: ORA_DISK_1
    
38. channel ORA_DISK_1: SID=70 device type=DISK
    
39. channel ORA_DISK_1: starting full datafile backup set
    
40. channel ORA_DISK_1: specifying datafile(s) in backup set
    
41. input datafile file number=00004 name=+DATA/orcl/datafile/users.263.981998613
    
42. channel ORA_DISK_1: starting piece 1 at 12-AUG-18
    
43. channel ORA_DISK_1: finished piece 1 at 12-AUG-18
    
44. piece handle=+FRA/orcl/backupset/2018_08_12/nnndf0_tag20180812t201252_0.305.983995973 tag=TAG20180812T201252 comment=NONE
    
45. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
    
46. Finished backup at 12-AUG-18
    
47. 
    
48. RMAN>
    

复制代码

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

要想改主密钥：

1. SQL> create tablespace tbsenc datafile size 5M encryption  default storage  (encrypt );
   
2. 
   
3. Tablespace created.
   
4. 
   
5. SQL> alter table hr.tnew08_a move tablespace tbsenc;
   
6. 
   
7. Table altered.
   
8. 
   
9. SQL> alter table hr.t_nocompression  move tablespace tbsenc;
   
10. 
    
11. Table altered.
    
12. 
    
13. SQL> conn / as sysdba
    
14. Connected.
    
15. SQL> alter system set encryption wallet close identified by "oracle456";
    
16. 
    
17. System altered.
    
18. 
    
19. SQL> conn hr/oracle_4U
    
20. Connected.
    
21. SQL> select  * from hr.tnew08_a;
    
22. select        * from hr.tnew08_a
    
23.                   *
    
24. ERROR at line 1:
    
25. ORA-28365: wallet is not open
    
26. 
    
27. 
    
28. SQL> select  * from hr.t_nocompression;
    
29. select        * from hr.t_nocompression
    
30.                   *
    
31. ERROR at line 1:
    
32. ORA-28365: wallet is not open
    
33. 
    
34. 
    
35. SQL>
    

复制代码

1. select  * from V$ENCRYPTED_TABLESPACES;

复制代码

	TS#	ENCRYPTIONALG	ENCRYPTEDTS
1	13	AES128	YES

1. select  * from v$tablespace where ts#=13;

复制代码

	TS#	NAME	INCLUDED_IN_DATABASE_BACKUP	BIGFILE	FLASHBACK_ON	ENCRYPT_IN_BACKUP
1	13	TBSENC	YES	NO	YES

非标准路径下的TDE钱包：


编辑sqlnet.ora：

1. ENCRYPTION_WALLET_LOCATION =
   
2. (SOURCE =
   
3. (METHOD = FILE)
   
4. (METHOD_DATA =
   
5. (DIRECTORY = /u01/app/oracle/wallet)
   
6. )
   
7. )
   
8. 
   

复制代码

ADDM的命令行定制：
能够加的ADDM过滤器：

1. select  * from DBA_ADVISOR_FINDING_NAMES;

复制代码

数据库的粗略的83个问题：

	ID	ADVISOR_NAME	FINDING_NAME
1	0	Default Advisor	normal, successful completion
2	1	ADDM	"Administrative" Wait Class
3	2	ADDM	"Application" Wait Class
4	3	ADDM	"Cluster" Wait Class
5	4	ADDM	"Concurrency" Wait Class
6	5	ADDM	"Configuration" Wait Class
7	6	ADDM	"Network" Wait Class
8	7	ADDM	"Other" Wait Class
9	8	ADDM	"Scheduler" Wait Class
10	9	ADDM	"User I/O" wait Class
11	10	ADDM	Buffer Busy
12	11	ADDM	Buffer Cache Latches
13	12	ADDM	Checkpoints Due to DROP or TRUNCATE
14	13	ADDM	Checkpoints Due to Log File Size
15	14	ADDM	Checkpoints Due to MTTR
16	15	ADDM	Checkpoints Due to Parallel Queries
17	16	ADDM	Checkpoints Due to Tablespace DDL
18	17	ADDM	Commits and Rollbacks
19	18	ADDM	CPU Usage
20	19	ADDM	DBMS_LOCK Usage
21	20	ADDM	DBMS_PIPE Usage
22	21	ADDM	Excessive Rebinds
23	22	ADDM	Free Buffer Waits
24	23	ADDM	Hard Parse
25	24	ADDM	Hard Parse Due to Invalidations
26	25	ADDM	Hard Parse Due to Literal Usage
27	26	ADDM	Hard Parse Due to Parse Errors
28	27	ADDM	Hard Parse Due to Sharing Criteria
29	28	ADDM	High Watermark Waits
30	29	ADDM	I/O Throughput
31	30	ADDM	Index Block Split
32	31	ADDM	Global Cache Busy
33	32	ADDM	Global Cache Congestion
34	33	ADDM	Interconnect Latency
35	34	ADDM	Global Cache Lost Blocks
36	35	ADDM	Global Cache Messaging
37	36	ADDM	Global Cache Multiblock Requests
38	37	ADDM	ITL Waits
39	38	ADDM	Java Execution
40	39	ADDM	Latch Free Waits
41	40	ADDM	Log File Switches
42	41	ADDM	PL/SQL Compilation
43	42	ADDM	PL/SQL Execution
44	43	ADDM	RMAN I/O
45	44	ADDM	Row Lock Waits
46	45	ADDM	Sequence Usage
47	46	ADDM	Session Connect and Disconnect
48	47	ADDM	Session Slot Scheduling
49	48	ADDM	Shared Pool Latches
50	49	ADDM	Slow Archivers
51	50	ADDM	Soft Parse
52	51	ADDM	Space Transaction Waits
53	52	ADDM	Streams Flow Control
54	53	ADDM	Table Locks
55	54	ADDM	Temp Space Contention
56	55	ADDM	Top Segments by I/O
57	56	ADDM	Top SQL by "Cluster" Wait
58	57	ADDM	Top SQL by DB Time
59	58	ADDM	Top SQL By I/O
60	59	ADDM	Undersized Buffer Cache
61	60	ADDM	Undersized PGA
62	61	ADDM	Undersized Redo Log Buffer
63	62	ADDM	Undersized SGA
64	63	ADDM	Undersized Shared Pool
65	64	ADDM	Undersized Streams Pool
66	65	ADDM	Undo I/O
67	66	ADDM	Unusual "Administrative" Wait Event
68	67	ADDM	Unusual "Application" Wait Event
69	68	ADDM	Unusual "Cluster" Wait Event
70	69	ADDM	Unusual "Commit" Wait Event
71	70	ADDM	Unusual "Concurrency" Wait Event
72	71	ADDM	Unusual "Configuration" Wait Event
73	72	ADDM	Unusual "Network" Wait Event
74	73	ADDM	Unusual "Other" Wait Event
75	74	ADDM	Unusual "Scheduler" Wait Event
76	75	ADDM	Unusual "User I/O" Wait Event
77	76	ADDM	Virtual Memory Paging
78	77	ADDM	Unusual "Queueing" Wait Event
79	78	ADDM	"Queueing" Wait Class
80	79	ADDM	Undersized instance memory
81	80	ADDM	Top SQL Statements
82	81	ADDM	Top Segments by "User I/O" and "Cluster"
83	82	ADDM	Buffer Busy - Hot Block
84	83	ADDM	Buffer Busy - Hot Objects

某个会话可以临时向操作系统借用排序空间500M：

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> show parameter work_
   
4. 
   
5. NAME                                     TYPE         VALUE
   
6. ------------------------------------ ----------- ------------------------------
   
7. fileio_network_adapters              string
   
8. listener_networks                     string
   
9. workarea_size_policy                     string         AUTO
   
10. SQL> alter session set workarea_size_policy=manual;
    
11. 
    
12. Session altered.
    
13. 
    
14. SQL> show parameter sort_
    
15. 
    
16. NAME                                     TYPE         VALUE
    
17. ------------------------------------ ----------- ------------------------------
    
18. sort_area_retained_size              integer         0
    
19. sort_area_size                             integer         65536
    
20. SQL> alter session set sort_area_size=524288000;
    
21. 
    
22. Session altered.
    
23. 
    
24. SQL>
    

复制代码