课程第27次（2018-8-13星期一）
表空间级别的TDE：SQL> select* from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
CLOSED
SQL> alter system set encryption key identified by "oracle123";
System altered.
SQL> select* from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
OPEN
SQL> conn hr/oracle_4U
Connected.
SQL> create table tnew08_a ( anumber ) ;
Table created.
SQL> alter table tnew08_amodify ( aencrypt using '3Des168' salt ) ;
Table altered.
SQL>
selectobject_id from dba_objects o
where o.object_name='TNEW08_A';
OBJECT_ID
174741
select* from dba_encrypted_columns;
OWNERTABLE_NAMECOLUMN_NAMEENCRYPTION_ALGSALTINTEGRITY_ALG
1HRTNEW08_AA3 Key Triple DES 168 bits keyYESSHA-1
select* from enc$;
OBJ#OWNER#MKEYIDENCALGINTALGCOLKLCKLCLENFLAG
1747411AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1141774141414141414141414141414141414141414141432B42426B7A796F3941384366696E417554552F49774137524A6635767174346756556F566F776D6F664F4D733358307565544E664276427A7051676561412B673D88
TDE的原理就是 master key 来加密 AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA， AX5cR+8gOE8YvwjehRgtkkkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 再来加密tnew08_a。
RMAN TDE就是：
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 12 20:12:15 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
connected to target database: ORCL (DBID=1507385682)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/acfsmounts/acfs_db1/dbs/snapcf_orcl.f'; # default
RMAN> CONFIGURE ENCRYPTION FOR DATABASE on;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored
RMAN> backup tablespace users;
Starting backup at 12-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.263.981998613
channel ORA_DISK_1: starting piece 1 at 12-AUG-18
channel ORA_DISK_1: finished piece 1 at 12-AUG-18
piece handle=+FRA/orcl/backupset/2018_08_12/nnndf0_tag20180812t201252_0.305.983995973 tag=TAG20180812T201252 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 12-AUG-18
RMAN>
主密钥 来 加密备份集头部里的密钥 ， 备份集头部里的密钥再来加密备份集。
要想改主密钥：
SQL> create tablespace tbsenc datafile size 5M encryptiondefault storage(encrypt );
Tablespace created.
SQL> alter table hr.tnew08_a move tablespace tbsenc;
Table altered.
SQL> alter table hr.t_nocompressionmove tablespace tbsenc;
Table altered.
SQL> conn / as sysdba
Connected.
SQL> alter system set encryption wallet close identified by "oracle456";
System altered.
SQL> conn hr/oracle_4U
Connected.
SQL> select* from hr.tnew08_a;
select * from hr.tnew08_a
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> select* from hr.t_nocompression;
select * from hr.t_nocompression
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>
select* from V$ENCRYPTED_TABLESPACES;
TS#ENCRYPTIONALGENCRYPTEDTS
113AES128YES
select* from v$tablespace where ts#=13;
TS#NAMEINCLUDED_IN_DATABASE_BACKUPBIGFILEFLASHBACK_ONENCRYPT_IN_BACKUP
113TBSENCYESNOYES
非标准路径下的TDE钱包：
编辑sqlnet.ora：
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/wallet)
)
)
ADDM的命令行定制：
能够加的ADDM过滤器：
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：
SQL> conn / as sysdba
Connected.
SQL> show parameter work_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters string
listener_networks string
workarea_size_policy string AUTO
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> show parameter sort_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 65536
SQL> alter session set sort_area_size=524288000;
Session altered.
SQL>
页:
[1]