TDE表空间加密

最新推荐文章于 2024-04-18 21:50:07 发布

BaldwinSu

最新推荐文章于 2024-04-18 21:50:07 发布

阅读量452

点赞数

分类专栏： Data Pump 文章标签： Oracle TDE

本文链接：https://blog.csdn.net/sudahai102448567/article/details/114998436

版权

Data Pump 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

环境：RAC+单节点ADG（11204）
现在的环境状态都是ok的

SYS@racdb1> select process, client_process, sequence#,thread#,status from v$managed_standby;

PROCESS 		    CLIENT_PROCESS	      SEQUENCE#    THREAD# STATUS
--------------------------- ------------------------ ---------- ---------- ------------------------------------
ARCH			    ARCH			      9 	 1 CLOSING
ARCH			    ARCH			      0 	 0 CONNECTED
ARCH			    ARCH			      6 	 1 CLOSING
ARCH			    ARCH			      9 	 1 CLOSING
ARCH			    ARCH			     11 	 1 CLOSING
ARCH			    ARCH			     12 	 1 CLOSING
ARCH			    ARCH			     12 	 1 CLOSING
ARCH			    ARCH			     10 	 1 CLOSING
LNS			  	    LNS 			     13 	 1 WRITING

9 rows selected.

SQL> select process, client_process, sequence#,thread#,status from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE#	 THREAD# STATUS
--------- -------- ---------- ---------- ------------
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   13	       2 OPENING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   13	       2 OPENING
ARCH	  ARCH		    0	       0 CONNECTED
ARCH	  ARCH		   13	       2 CLOSING
ARCH	  ARCH		   13	       2 OPENING
RFS	  	  ARCH		    0	       0 IDLE
RFS	  	  ARCH		    0	       0 IDLE
RFS	  	  UNKNOWN	    0	       0 IDLE

PROCESS   CLIENT_P  SEQUENCE#	 THREAD# STATUS
--------- -------- ---------- ---------- ------------
RFS	  LGWR		   14	       2 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  LGWR		   13	       1 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
MRP0  N/A		   13	       1 APPLYING_LOG
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE

PROCESS   CLIENT_P  SEQUENCE#	 THREAD# STATUS
--------- -------- ---------- ---------- ------------
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE
RFS	  UNKNOWN	    0	       0 IDLE

27 rows selected.

下面所有的用户名和表空间都是实验环境，注意修改
回退方案
新建一个单实例，使用Datapump导入，应用修改地址，没有问题再做源库的修改
临时表要单独导入，序列导入之后要重建
新建数据库略…

查看数据库字符集
源库
col parameter for a30
col value for a30
SYS@orclsk1> SELECT * FROM nls_database_parameters;
NLS_CHARACTERSET	       AL32UTF8

新库
SQL> SELECT * FROM nls_database_parameters;
NLS_CHARACTERSET	       AL32UTF8

查看profile
源库
col profile for a30
col LIMIT for a30
col RESOURCE_NAME for a30
set pagesize 1000
set line 1300
SELECT * FROM dba_profiles;
SYS@orclsk1> SELECT * FROM dba_profiles;

PROFILE 		       RESOURCE_NAME		      RESOURCE_TYPE	       LIMIT
------------------------------ ------------------------------ ------------------------ ------------------------------
DEFAULT 		       COMPOSITE_LIMIT		      KERNEL		       UNLIMITED
DEFAULT 		       SESSIONS_PER_USER	      KERNEL		       UNLIMITED
DEFAULT 		       CPU_PER_SESSION		      KERNEL		       UNLIMITED
DEFAULT 		       CPU_PER_CALL		          KERNEL		       UNLIMITED
DEFAULT 		       LOGICAL_READS_PER_SESSION      KERNEL		       UNLIMITED
DEFAULT 		       LOGICAL_READS_PER_CALL	      KERNEL		       UNLIMITED
DEFAULT 		       IDLE_TIME		          KERNEL		       UNLIMITED
DEFAULT 		       CONNECT_TIME		          KERNEL		       UNLIMITED
DEFAULT 		       PRIVATE_SGA		          KERNEL		       UNLIMITED
DEFAULT 		       FAILED_LOGIN_ATTEMPTS	  PASSWORD		       30
DEFAULT 		       PASSWORD_LIFE_TIME	      PASSWORD		       UNLIMITED
DEFAULT 		       PASSWORD_REUSE_TIME	      PASSWORD		       UNLIMITED
DEFAULT 		       PASSWORD_REUSE_MAX	      PASSWORD		       UNLIMITED
DEFAULT 		       PASSWORD_VERIFY_FUNCTION   PASSWORD		       NULL
DEFAULT 		       PASSWORD_LOCK_TIME	      PASSWORD		       1
DEFAULT 		       PASSWORD_GRACE_TIME	      PASSWORD		       7
MONITORING_PROFILE	       COMPOSITE_LIMIT		      KERNEL		       DEFAULT
MONITORING_PROFILE	       SESSIONS_PER_USER	      KERNEL		       DEFAULT
MONITORING_PROFILE	       CPU_PER_SESSION		      KERNEL		       DEFAULT
MONITORING_PROFILE	       CPU_PER_CALL		          KERNEL		       DEFAULT
MONITORING_PROFILE	       LOGICAL_READS_PER_SESSION      KERNEL		       DEFAULT
MONITORING_PROFILE	       LOGICAL_READS_PER_CALL	      KERNEL		       DEFAULT
MONITORING_PROFILE	       IDLE_TIME		      KERNEL		       DEFAULT
MONITORING_PROFILE	       CONNECT_TIME		      KERNEL		       DEFAULT
MONITORING_PROFILE	       PRIVATE_SGA		      KERNEL		       DEFAULT
MONITORING_PROFILE	       FAILED_LOGIN_ATTEMPTS	      PASSWORD		       UNLIMITED
MONITORING_PROFILE	       PASSWORD_LIFE_TIME	      PASSWORD		       DEFAULT
MONITORING_PROFILE	       PASSWORD_REUSE_TIME	      PASSWORD		       DEFAULT
MONITORING_PROFILE	       PASSWORD_REUSE_MAX	      PASSWORD		       DEFAULT
MONITORING_PROFILE	       PASSWORD_VERIFY_FUNCTION       PASSWORD		       DEFAULT
MONITORING_PROFILE	       PASSWORD_LOCK_TIME	      PASSWORD		       DEFAULT
MONITORING_PROFILE	       PASSWORD_GRACE_TIME	      PASSWORD		       DEFAULT

32 rows selected.

新库
col profile for a30
col LIMIT for a30
col RESOURCE_NAME for a30
set pagesize 1000
set line 1300
SELECT * FROM dba_profiles;
SQL> SELECT * FROM dba_profiles;

PROFILE 		       RESOURCE_NAME		      RESOURCE_TYPE	       LIMIT
------------------------------ ------------------------------ ------------------------ ------------------------------
DEFAULT 		       COMPOSITE_LIMIT		      KERNEL		       UNLIMITED
DEFAULT 		       SESSIONS_PER_USER	      KERNEL		       UNLIMITED
DEFAULT 		       CPU_PER_SESSION		      KERNEL		       UNLIMITED
DEFAULT 		       CPU_PER_CALL		          KERNEL		       UNLIMITED
DEFAULT 		       LOGICAL_READS_PER_SESSION      KERNEL		       UNLIMITED
DEFAULT 		       LOGICAL_READS_PER_CALL	      KERNEL		       UNLIMITED
DEFAULT 		       IDLE_TIME		          KERNEL		       UNLIMITED
DEFAULT 		       CONNECT_TIME		          KERNEL		       UNLIMITED
DEFAULT 		       PRIVATE_SGA		          KERNEL		       UNLIMITED
DEFAULT 		       FAILED_LOGIN_ATTEMPTS	  PASSWORD		       10
DEFAULT 		       PASSWORD_LIFE_TIME	      PASSWORD		       180
DEFAULT 		       PASSWORD_REUSE_TIME	      PASSWORD		       UNLIMITED
DEFAULT 		       PASSWORD_REUSE_MAX	      PASSWORD		       UNLIMITED
DEFAULT 		       PASSWORD_VERIFY_FUNCTION   PASSWORD		       NULL
DEFAULT 		       PASSWORD_LOCK_TIME	      PASSWORD		       1
DEFAULT 		       PASSWORD_GRACE_TIME	      PASSWORD		       7

已选择16行。

获取建立profile语句并且新库执行

SELECT DBMS_METADATA.GET_DDL('PROFILE','MONITORING_PROFILE') FROM DUAL;
CREATE PROFILE "MONITORING_PROFILE"
    LIMIT 
         COMPOSITE_LIMIT DEFAULT 
         SESSIONS_PER_USER DEFAULT 
         CPU_PER_SESSION DEFAULT 
         CPU_PER_CALL DEFAULT 
         LOGICAL_READS_PER_SESSION DEFAULT 
         LOGICAL_READS_PER_CALL DEFAULT 
         IDLE_TIME DEFAULT 
         CONNECT_TIME DEFAULT 
         PRIVATE_SGA DEFAULT 
         FAILED_LOGIN_ATTEMPTS UNLIMITED 
         PASSWORD_LIFE_TIME DEFAULT 
         PASSWORD_REUSE_TIME DEFAULT 
         PASSWORD_REUSE_MAX DEFAULT 
         PASSWORD_VERIFY_FUNCTION DEFAULT 
         PASSWORD_LOCK_TIME DEFAULT 
         PASSWORD_GRACE_TIME DEFAULT

查看Temp
源库
SELECT *
       FROM v$tempfile

在这里插入图片描述
新库

查看源数据库有哪些表空间，新库创建对应表空间
set linesize 300 pagesize 2000
col File_Name format a60
col TableSpace_Name format a33
col Status format a20
col Auto format a20
col Size_MB format a20
col Free_Size_MB format a20
col Used_MB format a20
col Useds format a20

SELECT a.tablespace_name "TableSpace_Name",
       a.file_Name "File_Name",
       a.status "Status",
       a.autoextensible "Auto",
       TO_CHAR(NVL(a.bytes/1024/1024,0),'99G999G990D900') "Size_MB",
       TO_CHAR(NVL(f.bytes/1024/1024,0),'99G999G990D900') Free_Size_MB
  FROM dba_data_files a,
  (SELECT file_id,
          sum(bytes) bytes
     FROM dba_free_space group by File_id) f
   WHERE a.file_id=f.file_id(+)
   ORDER BY a.tablespace_name,a.file_id;
   
TableSpace_Name 		  File_Name						                     Status		    Auto	 Size_MB
--------------- ---------------------------------------------------- -------------------- -------------------- --------------------
AUDIT_TBS			  +DATA/orclsk/datafile/audit_tbs.291.1039887259	       AVAILABLE	    YES 		      30,767.000	   15,918.750
BAPHIS				  +DATA/orclsk/datafile/baphis.264.1036423129		       AVAILABLE	    NO			      30,767.000		9.938
BAPHIS				  +DATA/orclsk/datafile/baphis.271.1036423163		       AVAILABLE	    NO			      30,767.000		4.188
BAPHIS				  +DATA/orclsk/datafile/baphis.272.1036423193		       AVAILABLE	    NO			      30,767.000		0.313
BAPHIS				  +DATA/orclsk/datafile/baphis.273.1036423223		       AVAILABLE	    NO			      30,767.000		5.000
BAPHIS				  +DATA/orclsk/datafile/baphis.274.1036423251		       AVAILABLE	    NO			      30,767.000		0.000
BAPHIS				  +DATA/orclsk/datafile/baphis.275.1036423281		       AVAILABLE	    NO			      30,767.000		1.375
BAPHIS				  +DATA/orclsk/datafile/baphis.276.1036423311		       AVAILABLE	    NO			      30,767.000		2.500
BAPHIS				  +DATA/orclsk/datafile/baphis.277.1036423341		       AVAILABLE	    NO			      30,767.000		7.375
BAPHIS				  +DATA/orclsk/datafile/baphis.278.1036423375		       AVAILABLE	    NO			      30,767.000		3.063
BAPHIS				  +DATA/orclsk/datafile/baphis.279.1036423405		       AVAILABLE	    NO			      30,767.000		3.875
BAPHIS				  +DATA/orclsk/datafile/baphis.292.1042813931		       AVAILABLE	    YES 			 100.000		0.000
BAPHIS				  +DATA/orclsk/datafile/baphis.293.1042814331		       AVAILABLE	    NO			      30,767.000		1.563
BAPHIS				  +DATA/orclsk/datafile/baphis.296.1043771805		       AVAILABLE	    NO			      30,767.000		1.000
BAPHIS				  +DATA/orclsk/datafile/baphis.298.1047571457		       AVAILABLE	    NO			      30,767.000	       16.125
BAPHIS				  +DATA/orclsk/datafile/baphis.299.1048073567		       AVAILABLE	    NO			      30,767.000	      265.125
BAPHIS				  +DATA/orclsk/datafile/baphis.302.1051290897		       AVAILABLE	    NO			      30,767.000	    9,354.188
BAPHIS				  +DATA/orclsk/datafile/baphis.303.1053343003		       AVAILABLE	    NO			      30,767.000	   12,916.063
BAPHIS				  +DATA/orclsk/datafile/baphis.304.1055692707		       AVAILABLE	    NO			      30,767.000	   15,729.625
BAPHIS				  +DATA/orclsk/datafile/baphis.305.1057829393		       AVAILABLE	    NO			      30,767.000	   18,195.000
BAPHIS				  +DATA/orclsk/datafile/baphis.306.1060619825		       AVAILABLE	    NO			      30,767.000	   21,868.188
BAPHIS				  +DATA/orclsk/datafile/baphis.308.1063968649		       AVAILABLE	    NO			      30,767.000	   26,135.563
BSSZSK_BA			  +DATA/orclsk/datafile/bsszsk_ba.281.1036660915	       AVAILABLE	    YES 		       1,024.000	    1,023.000
SOURCEDB			  +DATA/orclsk/datafile/sourcedb.282.1036662987 	       AVAILABLE	    YES 		       1,024.000	    1,019.313
SSDEV				  +DATA/orclsk/datafile/ssdev.280.1036585519		       AVAILABLE	    YES 		      10,240.000	    9,894.313
SYSAUX				  +DATA/orclsk/datafile/sysaux.256.1036422459		       AVAILABLE	    YES 		      20,680.000		0.000
SYSAUX				  +DATA/orclsk/datafile/sysaux.307.1060619911		       AVAILABLE	    YES 		      10,240.000	    3,396.938
SYSTEM				  +DATA/orclsk/datafile/system.262.1036422459		       AVAILABLE	    YES 		       4,096.000	    3,297.938
UNDOTBS1			  +DATA/orclsk/datafile/undotbs1.270.1036422459 	       AVAILABLE	    YES 		       1,450.000	      955.375
UNDOTBS1			  +DATA/orclsk/datafile/undotbs1.294.1043249451 	       AVAILABLE	    NO			      30,720.000	   30,217.000
UNDOTBS1			  +DATA/orclsk/datafile/undotbs1.300.1049881169 	       AVAILABLE	    NO			      30,767.000	   30,228.000
UNDOTBS2			  +DATA/orclsk/datafile/undotbs2.261.1036422619 	       AVAILABLE	    YES 		       2,375.000	    2,009.938
UNDOTBS2			  +DATA/orclsk/datafile/undotbs2.295.1043249547 	       AVAILABLE	    NO			      30,720.000	   30,371.750
USERS				  +DATA/orclsk/datafile/users.269.1036422459		       AVAILABLE	    YES 		      20,480.000	   15,115.375

34 rows selected.

源库

获取建表空间语句
select dbms_metadata.get_ddl('TABLESPACE','BAWSJ')  from dual;
  CREATE TABLESPACE "BAWSJ" DATAFILE 
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792,
  SIZE 32261537792
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

新库创建对应的表空间

CREATE TABLESPACE BAWSJ DATAFILE '/u01/app/oradata/orclskts/BAWSJ01.dbf' size 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ02.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ03.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ04.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ05.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ06.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ07.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ08.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ09.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ10.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ11.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ12.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ13.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ14.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ15.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ16.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ17.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ18.dbf' SIZE 30767M AUTOEXTEND OFF;
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ19.dbf' SIZE 30767M AUTOEXTEND OFF;  
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ20.dbf' SIZE 30767M AUTOEXTEND OFF;  
ALTER TABLESPACE BAWSJ ADD DATAFILE '/u01/app/oradata/orclskts/BAWSJ21.dbf' SIZE 30767M AUTOEXTEND OFF;

查看DBA_ROLE_PIVS（业务用户有哪些角色）
源库

SELECT * FROM dba_role_privs WHERE grantee IN ('WJJ');

在这里插入图片描述
查看DBA_SYS_PRIVS（业务用户有哪些系统权限）
源库

SELECT * FROM dba_sys_privs WHERE grantee IN ('WJJ') ORDER BY grantee;

在这里插入图片描述
查看role

SELECT role FROM dba_roles;

在这里插入图片描述
查看OBJECTS

SELECT owner,
       object_type,
       count(*)
   FROM dba_objects
  WHERE owner IN ('WJJ')
 GROUP BY owner,object_type;

在这里插入图片描述
查看INVAILD OBJECT

SELECT owner,
       object_type,
       object_name,
       status
   FROM dba_objects
  WHERE status = 'INVALID'
  AND owner IN ('WJJ');

在这里插入图片描述
查看DISABLED TRIGGER

SELECT owner,
       trigger_name,
       table_name,
       status
   FROM dba_triggers
  WHERE status = 'ENABLED'
  AND owner in ('WJJ')

在这里插入图片描述
查看User default tablespace&default temporary talbespace

SELECT username,
       default_tablespace,
       temporary_tablespace
   FROM dba_users
  WHERE username IN ('WJJ');

在这里插入图片描述
查看是否开启审计

源库
SYS@orclsk1> show parameter audit;

NAME				     TYPE			       VALUE
--------------------- ---------------- ------------------------------
audit_file_dest 		string		   /u01/app/oracle/admin/orclsk/adump
audit_sys_operations	boolean		   FALSE
audit_syslog_level		string		  (null)
audit_trail			    string		  NONE

新库
SQL> show parameter audit;

NAME				     TYPE			       VALUE
--------------------- -------------------  ------------------------------
audit_file_dest 		 string			     /u01/app/oracle/admin/orclskts/adump
audit_sys_operations	 boolean			 FALSE
audit_syslog_level		 string
audit_trail			     string			       DB

查看public 同义词
源库

  SELECT owner,
       table_owner
   FROM dba_synonyms
  WHERE table_owner IN ('BSSZSK_BANEW')

在这里插入图片描述
查看public dblink

 SELECT owner,
       db_link,
       username,
       host
   FROM all_db_links

在这里插入图片描述
锁用户、重启数据库

alter user WJJ account lock;
shu immedaite
startup

源库使用expdp导出

[oracle@racba01 tde]$ vim dumpparameter.sql
directory='TDE'
parallel=32
logfile='EXPDP0324.log'
dumpfile=TDE%U.oraexpdp
schemas='WJJ'
cluster=n
exclude=statistics

[oracle@racba01 tde]$ expdp \"/ as sysdba\" parfile=dumpparameter.sql

导出完成查看log是否正常
源库生成建序列语句稍后新库impdp之后要重建序列用

spool recreate_sequence.sql
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+(INCREMENT_BY*CACHE_SIZE)*2)||
        ' CACHE '||
        CACHE_SIZE||
      CASE WHEN ORDER_FLAG='N' 
       THEN ' NOORDER ' 
       ELSE ' ORDER ' 
       END ||
      CASE WHEN CYCLE_FLAG='N' 
       THEN ' NOCYCLE;' 
       ELSE ' CYCLE;' 
       END SQL 
     FROM dba_SEQUENCEs 
    WHERE SEQUENCE_OWNER in ('WJJ')
    AND CACHE_SIZE<>0
UNION ALL
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+1)||
        ' NOCACHE '||
     CASE WHEN ORDER_FLAG='N' 
      THEN ' NOORDER ' 
      ELSE ' ORDER ' 
      END ||
     CASE WHEN CYCLE_FLAG='N' 
      THEN ' NOCYCLE;' 
      ELSE ' CYCLE;' 
      END SQL 
    FROM dba_SEQUENCEs 
   WHERE SEQUENCE_OWNER in ('WJJ') 
   AND CACHE_SIZE=0
ORDER BY 1;
spool off;

把@recreate_sequence.sql传到新库
源库导出临时表建表语句，然后新库执行建临时表语句

源库执行
set long 9999 linesize 300 pagesize9999 longc9999
SELECT dbms_metadata.get_ddl('TABLE',table_name,owner)||';'
  FROM dba_tables t 
 WHERE t.temporary='Y'
 AND owner IN ('WJJ');
 
CREATE GLOBAL TEMPORARY TABLE "WJJ"."SYS_TEMP_FBT"
   (	"SCHEMA" VARCHAR2(32),
	"OBJECT_NAME" VARCHAR2(32),
	"OBJECT#" NUMBER,
	"RID" UROWID (4000),
	"ACTION" CHAR(1)
   ) ON COMMIT PRESERVE ROWS ;

 CREATE GLOBAL TEMPORARY TABLE "WJJ"."QUEST_SL_TEMP_EXPLAIN1"
   (	"STATEMENT_ID" VARCHAR2(30),
	"PLAN_ID" NUMBER,
	"TIMESTAMP" DATE,
	"REMARKS" VARCHAR2(4000),
	"OPERATION" VARCHAR2(30),
	"OPTIONS" VARCHAR2(255),
	"OBJECT_NODE" VARCHAR2(128),
	"OBJECT_OWNER" VARCHAR2(30),
	"OBJECT_NAME" VARCHAR2(30),
	"OBJECT_ALIAS" VARCHAR2(65),
	"OBJECT_INSTANCE" NUMBER(*,0),
	"OBJECT_TYPE" VARCHAR2(30),
	"OPTIMIZER" VARCHAR2(255),
	"SEARCH_COLUMNS" NUMBER,
	"ID" NUMBER(*,0),
	"PARENT_ID" NUMBER(*,0),
	"DEPTH" NUMBER(*,0),
	"POSITION" NUMBER(*,0),
	"COST" NUMBER(*,0),
	"CARDINALITY" NUMBER(*,0),
	"BYTES" NUMBER(*,0),
	"OTHER_TAG" VARCHAR2(255),
	"PARTITION_START" VARCHAR2(255),
	"PARTITION_STOP" VARCHAR2(255),
	"PARTITION_ID" NUMBER(*,0),
	"OTHER" LONG,
	"OTHER_XML" CLOB,
	"DISTRIBUTION" VARCHAR2(30),
	"CPU_COST" NUMBER(*,0),
	"IO_COST" NUMBER(*,0),
	"TEMP_SPACE" NUMBER(*,0),
	"ACCESS_PREDICATES" VARCHAR2(4000),
	"FILTER_PREDICATES" VARCHAR2(4000),
	"PROJECTION" VARCHAR2(4000),
	"TIME" NUMBER(*,0),
	"QBLOCK_NAME" VARCHAR2(30)
   ) ON COMMIT PRESERVE ROWS ;

新库执行上述建临时表语句
把dump文件传到新库
新库导入

vim impdpparameter.sql
directory='PUMP_DIR'
schemas='WJJ'
logfile='impdp0324.log'
dumpfile='tde%U.oraexpdp'
cluster=n
parallel=32

impdp \"/ as sysdba\" parfile=impdpparameter.sql

重建序列
新库先删除序列
新库生成删除脚本并执行：

set line 200 pagesize 500
spool drop_sequence.sql
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME ||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('WJJ')
 AND cache_size<>0 
 AND max_value!=cache_size
UNION ALL
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('WJJ')
 AND cache_size=0 
 AND max_value!=cache_size
ORDER BY 1;
spool off

新库执行@drop_sequence.sql
然后执行源库传过来的recreate_sequence.sql
所有的完成之后去对比DBA_ROLE_PIVS、DBA_SYS_PRIVS、ROLE、OBJECTS、INVAILD OBJECT、DISABLED TRIGGER、public 同义词、public dblink
如果无效对象增多，就执行@$ORACLE_HOME/rdbms/admin/utlrp.sql
手动收集统计信息

exec dbms_stats.gather_schema_stats('WJJ',estimate_percent=>20,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,method_opt=>'for all columns size auto',force=>TRUE,cascade=>TRUE,degree=>15);
或者
exec dbms_stats.gather_schema_stats(ownname=>'WJJ',options=>'GATHER AUTO',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for all columns size auto',degree=>15);

叫开发同事修改数据库链接，测试是否正常，一切正常之后开始做TDE
开始修改源库做TDE
查看默认wallet(钱夹)目录以及指定位置

RAC执行
SELECT *
       FROM gv$encryption_wallet

这个结果明显是指定的一个ORACLE_HOME中的路径
在这里插入图片描述
这里要注意的是gv$encryption_wallet和v$encryption_wallet的目录路径居然不同
上一个plsql执行（就是保证查询的是同一个instance）

SELECT *
	           FROM v$encryption_wallet

这个结果明显指定的是一个ORACLE_BASE中的路径
在这里插入图片描述
为什么会有不同呢？
原因

Oracle如果没有在sqlnet.ora中指定wallet的路径，那么就可以自动在ORACLE_BASE或者ORACLE_HOME中自动生成，通常情况是在ORACLE_BASE，因为wallet生成的过程中会去找操作系统参数，比如如果不在Oracle用户的.bash_profile中指定ORACLE_BASE参数，那么就会在ORACLE_HOME中生成
关于RAC
数据库启动的时候可以使用startup或者srvctl，这两个命令调用的wallet文件是不同的，startup调用的是ORACLE_BASE下的文件，srvctl调用的是ORACLE_HOME下的文件；
在没有sqlnet.ora指定路径的情况下，startup启动时v$encryption_wallet和gv$encryption_wallet显示的就是ORACLE_BASE下的路径，srvctl启动gv$encryption_wallet和v$encryption_wallet显示的就是ORACLE_HOME下的路径
这里就会有一个问题，如果wallet生成在了ORACLE_BASE下，那么srvctl启动的时候就无法启动wallet
为了避免上述情况
1）可以在sqlnet.ora明确指定wallet的路径，这样gv$encryption_wallet和v$encryption_wallet路径也会相同（无论什么启动方式）
2）向数据库中添加oracle_base参数（root用户执行）
srvctl setenv database -d 数据库名 -T “ORACLE_BASE=XXXXX”
查看是否添加成功
srvctl getenv database -d 数据库名 -t “ORACLE_BASE”
再使用srvctl命令启停
查看gv$encryptionh_wallet和v$encryption_wallet都是指向了ORACLE_BASE
关于单机多实例
如果是在一个单机ORACLE_HOME上有多个实例时如何设置的问题。
为了保证两个实例可以分别使用各自不同密码的wallet文件，一定不要在sqlnet.ora文件指定实例名，
而是直接用“$ORACLE_SID”来代替。这样就可以保证两个实例可以使用不同的加密文件。
例：

--是Oracle用户下的sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=(DIRECTORY=/home/oracle/wallet/$ORACLE_SID)))

在两个实例创建对应的目录

[oracle@racba01~]$ ls /u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet
[oracle@racba01~]$ mkdir /u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet
[oracle@racba02~]$ ls /u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet
[oracle@racba02~]$ mkdir /u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet

设置wallet密码

alter system set encryption key identified by "TDE123456";

将主密钥文件拷贝到其他节点的对应目录下
节点一

[oracle@racba01 wallet]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet
[oracle@racba01 wallet]$ ls
ewallet.p12

[oracle@racba01 wallet]$ scp ewallet.p12 racba02:/u01/app/oracle/product/11.2.0.4/db_1/admin/orclsk/wallet
ewallet.p12

打开Oracle wallet（任一节点）
（1）先尝试关闭

SYS@racdb1> alter system set encryption wallet close identified by "TDE123456";

System altered.

（2）打开Oracle wallet

SYS@racdb1> alter system set encryption wallet open identified by "TDE123456";

System altered.

（3）检验 Oracle wallet 状态

SELECT *
       FROM gv$encryption_wallet

在这里插入图片描述
DG备库操作
查看钱包默认存放路径

SQL> col WRL_PARAMETER for a50
SQL> select * from v$encryption_wallet;

WRL_TYPE	     WRL_PARAMETER					STATUS
-------------------- -------------------------------------------------- ------------------
file		     /u01/app/oracle/admin/rac_dg/wallet		CLOSED

创建上述目录

[oracle@adg backup]$ ls /u01/app/oracle/admin/rac_dg/wallet
ls: 无法访问/u01/app/oracle/admin/rac_dg/wallet: 没有那个文件或目录
[oracle@adg backup]$ mkdir /u01/app/oracle/admin/rac_dg/wallet -p

或者是修改备库sqlnet.ora指定wallet路径
把主密钥copy过来

[oracle@racbj01 wallet]$ scp ewallet.p12 10.107.174.173:/u01/app/oracle/admin/rac_dg/wallet/
oracle@10.107.174.173's password: 
ewallet.p12

备库使用sys用户登录备库，打开钱包

SQL> alter system set encryption wallet open identified by "TDE123456";

System altered.
SQL> col WRL_PARAMETER for a50
SQL> select * from v$encryption_wallet;

WRL_TYPE	     WRL_PARAMETER					STATUS
-------------------- -------------------------------------------------- ------------------
file		     /u01/app/oracle/admin/rac_dg/wallet		OPEN

使用datadump迁移普通表空间到加密表空间大概步骤

使用“dbms_metadata.get_ddl”提取用于创建原始应用表空间的 DDL 并将输出另存为一个 SQL 脚本。
在每个“create tablespace”语句中添加“ENCRYPTION DEFAULT STORAGE(ENCRYPT)”（无需更改
SIZE 参数，因为 TDE 表空间加密不会增加存储需求）用 Data Pump (expdp) 导出整个数据库，或拥有应用表空间的模式
用“with contents and datafiles”删除应用表空间。运行 SQL 脚本创建加密应用表空间，其他特性保持不变。用
Data Pump (impdp) 导入转储文件。

查看DBA_ROLE_PIVS

SELECT * FROM dba_role_privs WHERE grantee IN ('BSSZSK_BANEW');

在这里插入图片描述
查看DBA_SYS_PRIVS

SELECT * FROM dba_sys_privs WHERE grantee IN ('BSSZSK_BANEW') ORDER BY grantee;

在这里插入图片描述
查看ROLE

SELECT role FROM dba_roles;

在这里插入图片描述
查看OBJECTS

SELECT owner,
       object_type,
       count(*)
   FROM dba_objects
  WHERE owner IN ('WJJ')
 GROUP BY owner,object_type;

在这里插入图片描述
查看INVAILD OBJECT

SELECT owner,
       object_type,
       object_name,
       status
   FROM dba_objects
  WHERE status = 'INVALID'
  AND owner IN ('WJJ');

在这里插入图片描述
查看DISABLED TRIGGER

SELECT owner,
       trigger_name,
       table_name,
       status
   FROM dba_triggers
  WHERE status = 'ENABLED'
  AND owner in ('WJJ')

在这里插入图片描述
查看User default tablespace&default temporary talbespace

SELECT username,
       default_tablespace,
       temporary_tablespace
   FROM dba_users
  WHERE username IN ('WJJ');

在这里插入图片描述
查看是否开启审计

SYS@racdb1> show parameter audit;

NAME				     TYPE			       VALUE
------------------------ --------------- ------------------------------
audit_file_dest 		  string		 /u01/app/oracle/admin/racdb/adump							       
audit_sys_operations	  boolean			       FALSE
audit_syslog_level		  string
audit_trail			      string			       DB

查看public 同义词

SELECT owner,
       table_owner
   FROM dba_synonyms
  WHERE table_owner IN ('WJJ')

在这里插入图片描述

查看public dblink

SELECT owner,
       db_link,
       username,
       host
   FROM all_db_links

在这里插入图片描述
查看一下现在有几个数据文件

SYS@racdb1> select file_name from dba_data_files where tablespace_name='BAWSJ';

FILE_NAME
-----------------------------------------------------------------
+DATA/racdb/datafile/bawsj.286.1067595893
+DATA/racdb/datafile/bawsj.287.1067595895
+DATA/racdb/datafile/bawsj.288.1067595897
+DATA/racdb/datafile/bawsj.289.1067595901
+DATA/racdb/datafile/bawsj.290.1067595903

dbms_metadata.get_ddl获取建表空间语句

select dbms_metadata.get_ddl('TABLESPACE','BAWSJ')  from dual;

修改语句一会执行（主要是添加了ENCRYPTION DEFAULT STORAGE(ENCRYPT)）

  CREATE TABLESPACE "BAWSJ" DATAFILE
  SIZE 10737418240
  AUTOEXTEND ON NEXT 1 ENCRYPTION DEFAULT STORAGE(ENCRYPT)

导出数据
临时表要单独导入

set long 9999 linesize 300 pagesize9999 longc9999
SELECT dbms_metadata.get_ddl('TABLE',table_name,owner)||';'
  FROM dba_tables t 
 WHERE t.temporary='Y'
 AND owner IN ('WJJ');

序列要重建
源库先获得创建序列脚本，impdp之后删除之后再执行

spool recreate_sequence.sql
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+(INCREMENT_BY*CACHE_SIZE)*2)||
        ' CACHE '||
        CACHE_SIZE||
      CASE WHEN ORDER_FLAG='N' 
       THEN ' NOORDER ' 
       ELSE ' ORDER ' 
       END ||
      CASE WHEN CYCLE_FLAG='N' 
       THEN ' NOCYCLE;' 
       ELSE ' CYCLE;' 
       END SQL 
     FROM dba_SEQUENCEs 
    WHERE SEQUENCE_OWNER in ('WJJ')
    AND CACHE_SIZE<>0
UNION ALL
SELECT 'CREATE SEQUENCE '||
        SEQUENCE_OWNER||
        '.'||
        SEQUENCE_NAME||
        ' MINVALUE '||
        to_char(MIN_VALUE)||
        ' MAXVALUE '||
        to_char(MAX_VALUE)||
        ' INCREMENT BY '||
        to_char(INCREMENT_BY)||
        ' START WITH '||
        to_char(LAST_NUMBER+1)||
        ' NOCACHE '||
     CASE WHEN ORDER_FLAG='N' 
      THEN ' NOORDER ' 
      ELSE ' ORDER ' 
      END ||
     CASE WHEN CYCLE_FLAG='N' 
      THEN ' NOCYCLE;' 
      ELSE ' CYCLE;' 
      END SQL 
    FROM dba_SEQUENCEs 
   WHERE SEQUENCE_OWNER in ('WJJ') 
   AND CACHE_SIZE=0
ORDER BY 1;
spool off;

SYS@racdb1> select * from dba_directories;

OWNER		     DIRECTORY_NAME		    DIRECTORY_PATH
-------------------- ------------------------------ ------------------------------------------------------------
SYS		     QUEST_SOO_UDUMP_DIR	    /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/
SYS		     QUEST_SOO_CDUMP_DIR	    /u01/app/oracle/diag/rdbms/racdb/racdb1/cdump/
SYS		     QUEST_SOO_ADUMP_DIR	    /u01/app/oracle/admin/racdb/adump/
SYS		     PUMP_DIR			   	    /u01/pumpdir
SYS		     QUEST_SOO_BDUMP_DIR	    /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/
SYS		     XMLDIR			            /u01/app/oracle/product/11.2.0/db_1/rdbms/xml
SYS		     ORACLE_OCM_CONFIG_DIR	    /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/racbj01/state
SYS		     DATA_PUMP_DIR		        /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
SYS		     ORACLE_OCM_CONFIG_DIR2	    /u01/app/oracle/product/11.2.0/db_1/ccr/state

执行导出

expdp \"/ as sysdba\" parfile=test.sql
##test.sql内容如下
directory='目录对象名'
parallel=6
logfile='日志文件名'
dumpfile=test%U.oraexpdp
tablespaces='BAWSJ'
cluster=n
exclude=table:"in('table_name01','table_name02')"
exclude=statistics

删除表空间

drop tablespace bawsj including contents and datafiles

执行上面的建表空间的语句，注意数据文件个数和加密表空间的参数

impdp \"/ as sysdba \" directory='目录对象' schemas='业务用户名' logfile='日志文件名' dumpfile=test%U.oraexpdp cluster=n remap_tablespace='原表空间名字':'新表空间名字' parallel=6

删除序列

set line 200 pagesize 500
spool drop_sequence.sql
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME ||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('WJJ')
 AND cache_size<>0 
 AND max_value!=cache_size
UNION ALL
SELECT 'DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';'
  FROM dba_sequences
 WHERE sequence_owner IN ('WJJ')
 AND cache_size=0 
 AND max_value!=cache_size
ORDER BY 1;
spool off
-----目标数据库执行【sqlplus / as sysdba】【导入完成后才能执行/脚本内容需要编辑修改】
@drop_sequence.sql

执行@recreate_sequence.sql
完成之后去对比DBA_ROLE_PIVS、DBA_SYS_PRIVS、ROLE、OBJECTS、INVAILD OBJECT、DISABLED TRIGGER、public 同义词、public dblink

BaldwinSu

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
TDE表空间加密

环境：RAC+单节点ADG（11204）现在的环境状态都是ok的SYS@racdb1> select process, client_process, sequence#,thread#,status from v$managed_standby;PROCESS CLIENT_PROCESS SEQUENCE# THREAD# STATUS--------------------------- ------------------------ ---------
复制链接

扫一扫

专栏目录