【TTS】AIX->Linux--基於RMAN(真實環境)--續
【TTS】AIX平臺資料庫遷移到Linux--基於RMAN(真實環境)
本篇接上文：【TTS】AIX平臺資料庫遷移到Linux--基於RMAN(真實環境) http://blog.itpub.net/26736162/viewspace-1987971/
1 target端轉換位元組序
[oracle@rhel6_lhr dbca]$ rman target /
恢復管理器: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:24:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已連線到目標資料庫: ORASKY (DBID=4027046368)
RMAN> CONVERT DATAFILE
2> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf",
3> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf",
4> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf",
5> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf",
6> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf",
7> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf",
8> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf",
9> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf",
10> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf",
11> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf",
12> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf",
13> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf"
14> TO PLATFORM="Linux x86 64-bit"
15> FROM PLATFORM="AIX-Based Systems (64-bit)"
16> FORMAT '+DATA';
啟動 conversion at target 於 2016-02-03 00:24:09
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=147 裝置型別=DISK
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/users.280.902795051
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:45
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/test_user1.278.902795095
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:25
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.277.902795121
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.276.902795121
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.275.902795123
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:02
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.270.902795125
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.267.902795125
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.268.902795127
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.281.902795127
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.296.902795129
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:04
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.297.902795133
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入檔名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf
已轉換的資料檔案 = +DATA/orasky/datafile/xpaddata.298.902795133
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:01
完成 conversion at target 於 2016-02-03 00:25:34
RMAN>
[grid@rhel6_lhr ~]$ asmcmd
[grid@rhel6_lhr asmdisk]$ cd
[grid@rhel6_lhr ~]$ asmcmd
ASMCMD> cd +data/ORASKY/datafile
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.298.902795133
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.297.902795133
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.296.902795129
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.281.902795127
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.277.902795121
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.276.902795121
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.275.902795123
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.270.902795125
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.268.902795127
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.267.902795125
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y USERS.292.902793265
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y USERS.280.902795051
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y UNDOTBS1.293.902793263
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y TEST_USER1.278.902795095
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y SYSTEM.295.902793257
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y SYSAUX.294.902793261
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y EXAMPLE.274.902793775
ASMCMD>
ASMCMD>
一.2 開始匯入
一.2.1 建立source庫的需要遷移的3個使用者並賦許可權(前邊的指令碼已經生成，直接拿過來執行)
如果不建立使用者會報如下的錯誤：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_APP1 does not exist in the database
create user TEST1 identified by TEST1 TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO TEST1;
GRANT CONNECT TO TEST1;
GRANT RESOURCE TO TEST1;
GRANT WRITE ON SYS.TEST_DIR TO TEST1;
GRANT READ ON SYS.TEST_DIR TO TEST1;
GRANT WRITE ON SYS.TEST_LOG TO TEST1;
GRANT READ ON SYS.TEST_LOG TO TEST1;
create user XPADAD identified by XPADAD TEMPORARY TABLESPACE TEMP;
GRANT CREATE VIEW TO XPADAD;
GRANT UNLIMITED TABLESPACE TO XPADAD;
GRANT CREATE DATABASE LINK TO XPADAD;
GRANT DBA TO XPADAD;
GRANT CONNECT TO XPADAD;
GRANT RESOURCE TO XPADAD;
create user T identified by T default TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO T;
GRANT RESOURCE TO T;
GRANT CONNECT TO T;
GRANT WRITE ON SYS.TT TO T;
GRANT READ ON SYS.TT TO T;
一.2.2 開始匯入
[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.292.902793265','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log
Import: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:35:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功載入/解除安裝了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.292.902793265,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-29349: 表空間 'USERS' 已存在
作業 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 00:35:50 停止
users表空間已經存在了，這裡把target端的users表空間重新命名一下就可以了：
[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:36:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@oraSKY > alter tablespace users rename to users01;
表空間已更改。
SYS@oraSKY > exit
從 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options 斷開
[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.280.902795051','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log
Import: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:40:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功載入/解除安裝了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.280.902795051,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
ORA-39151: 表 "SCOTT"."EMP" 已存在。由於跳過了 table_exists_action, 將跳過所有相關後設資料和資料。
處理物件型別 TRANSPORTABLE_EXPORT/INDEX/INDEX
處理物件型別 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作業 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已經完成, 但是有 1 個錯誤 (於 00:40:51 完成)
[oracle@rhel6_lhr dbca]$
[oracle@rhel6_lhr dbca]$
[ZFXDESKDB2:oracle]:/oracle>
一.2.2.1 報錯：source和target的compatible引數不同引起ora-00721錯誤
[oracle@rhel6_lhr dbs]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/orclasm/datafile/app1tbs.271.90278175','+DATA/orclasm/datafile/APP2TBS.276.902781757','+DATA/orclasm/datafile/IDXTBS.279.902781761' LOGFILE=impdp_tts_20160202.log version=latest
Import: Release 11.2.0.3.0 - Production on 星期二 2月 2 21:04:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功載入/解除安裝了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/orclasm/datafile/app1tbs.271.90278175,+DATA/orclasm/datafile/APP2TBS.276.902781757,+DATA/orclasm/datafile/IDXTBS.279.902781761 LOGFILE=impdp_tts_20160202.log version=latest
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-00721: 發行版 11.2.0.4.0 中的更改無法用於發行版 11.2.0.3.0
作業 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 21:04:37 停止
[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 2 21:04:58 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
21:04:58 SYS@orclasm > show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
commit_logging string
commit_point_strength integer 1
commit_wait string
commit_write string
compatible string 11.2.0.3.0
nls_comp string BINARY
plsql_v2_compatibility boolean FALSE
21:05:03 SYS@orclasm >
解決辦法：保持source和target的版本一致，或source端小於等於target端，若版本一致，則修改target端的compatible引數和source端一致。
一.2.3 檢視目標平臺資訊
[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:42:23 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@oraSKY > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS01 ONLINE
EXAMPLE ONLINE
TEST_USER1 READ ONLY
USERS READ ONLY
XPADDATA READ ONLY
已選擇9行。
SYS@oraSKY > alter tablespace TEST_USER1 read write;
表空間已更改。
SYS@oraSKY > alter tablespace USERS read write;
表空間已更改。
SYS@oraSKY > alter tablespace XPADDATA read write;
表空間已更改。
SYS@oraSKY > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS01 ONLINE
EXAMPLE ONLINE
TEST_USER1 ONLINE
USERS ONLINE
XPADDATA ONLINE
已選擇9行。
一.3 匯入完成後的結果校驗
一.3.1 校驗使用者情況（密碼、預設表空間、角色和許可權，需遷移的schema物件大小、個數、列表）
一.3.1.1 校驗使用者
SELECT d.username,
d.default_tablespace,
D.temporary_tablespace,
d.account_status
FROM dba_users d
WHERE d.account_status = 'OPEN'
and d.username in ('T','TEST1','XPADAD');
![wps5BF1.tmp[4]](http://img.blog.itpub.net/blog/attachment/201602/12/26736162_14552498629z3M.jpg "wps5BF1.tmp[4]")
SQL> alter user T default tablespace users;
User altered.
SQL> alter user XPADAD default tablespace XPADDATA;
User altered.
SQL> alter user TEST1 default tablespace TEST_USER1;
User altered.
SQL>
![wps5BF2.tmp[4]](http://img.blog.itpub.net/blog/attachment/201602/12/26736162_14552498634uD4.jpg "wps5BF2.tmp[4]")
一.3.1.2 使用者物件個數
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
![wps5C03.tmp[4]](http://img.blog.itpub.net/blog/attachment/201602/12/26736162_1455249863iNAl.jpg "wps5C03.tmp[4]")
SELECT D.OWNER, D.OBJECT_TYPE, COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1
FROM DBA_RECYCLEBIN B
WHERE B.object_name = D.OBJECT_NAME
AND D.OWNER = B.owner)
GROUP BY D.OWNER, D.OBJECT_TYPE
ORDER BY D.OWNER;
![wps5C04.tmp[4]](http://img.blog.itpub.net/blog/attachment/201602/12/26736162_14552498647938.jpg "wps5C04.tmp[4]")
一.3.1.3 物件詳細資訊
---- 以下資料匯出到excel表格備份
SELECT d.OWNER, d.OBJECT_NAME, d.SUBOBJECT_NAME, d.OBJECT_TYPE,d.status
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
ORDER BY D.OWNER ;
|
|
OWNER |
OBJECT_NAME |
SUBOBJECT_NAME |
OBJECT_TYPE |
STATUS |
|
1 |
T |
T1_IND |
|
INDEX |
VALID |
|
2 |
T |
TTT |
|
TABLE |
VALID |
|
3 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
VALID |
|
4 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
VALID |
|
5 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
VALID |
|
6 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
VALID |
|
7 |
T |
MONTH_PART |
|
TABLE |
VALID |
|
8 |
T |
T1 |
|
TABLE |
VALID |
|
9 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
VALID |
|
10 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
VALID |
|
11 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
VALID |
|
12 |
T |
PT1 |
|
TABLE |
VALID |
|
13 |
T |
PT1_IND1 |
|
INDEX |
VALID |
|
14 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
VALID |
|
15 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
VALID |
|
16 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
VALID |
|
17 |
T |
PT2 |
|
TABLE |
VALID |
|
18 |
T |
PT2_IND1 |
|
INDEX |
VALID |
|
19 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
VALID |
|
20 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
VALID |
|
21 |
TEST1 |
TEST |
|
TABLE |
VALID |
|
22 |
TEST1 |
TEST_TABLE |
|
TABLE |
VALID |
|
23 |
XPADAD |
WH_CONCAT_IMPL_LHR |
|
TYPE BODY |
VALID |
|
24 |
XPADAD |
WH_CONCAT_IMPL_LHR |
|
TYPE |
VALID |
|
25 |
XPADAD |
TEST |
|
TABLE |
VALID |
|
26 |
XPADAD |
WH_CONCAT_LHR |
|
FUNCTION |
VALID |
SELECT d.owner,
d.segment_name,
d.partition_name,
d.segment_type,
d.tablespace_name,
d.BYTES
FROM dba_segments d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name AND D.OWNER=B.owner)
ORDER BY D.OWNER ;
|
|
OWNER |
SEGMENT_NAME |
PARTITION_NAME |
SEGMENT_TYPE |
TABLESPACE_NAME |
BYTES |
|
1 |
T |
T1 |
|
TABLE |
USERS |
65536 |
|
2 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
|
3 |
T |
PT1_IND1 |
|
INDEX |
USERS |
65536 |
|
4 |
T |
PT2_IND1 |
|
INDEX |
USERS |
65536 |
|
5 |
T |
TTT |
|
TABLE |
USERS |
65536 |
|
6 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
|
7 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
|
8 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
|
9 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
|
10 |
T |
T1_IND |
|
INDEX |
USERS |
65536 |
|
11 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
|
12 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
USERS |
8388608 |
|
13 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
USERS |
8388608 |
|
14 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
USERS |
8388608 |
|
15 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
USERS |
8388608 |
|
16 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
USERS |
8388608 |
|
17 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
USERS |
8388608 |
|
18 |
TEST1 |
TEST |
|
TABLE |
TEST_USER1 |
9437184 |
|
19 |
TEST1 |
TEST_TABLE |
|
TABLE |
TEST_USER1 |
65536 |
|
20 |
XPADAD |
TEST |
|
TABLE |
XPADDATA |
9437184 |
一.3.2 無效物件情況
SELECT owner owner,
count(1)
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
AND D.OWNER NOT IN ('PUBLIC')
group by d.OWNER
ORDER BY owner;
SELECT owner owner,
object_name,
object_type,
status,
'alter ' || decode(object_type,
'PACKAGE BODY',
'PACKAGE',
'TYPE BODY',
'TYPE',
object_type) || ' ' || owner || '.' ||
object_name || ' ' ||
decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' hands_on
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
ORDER BY owner, object_name;
一.3.3 索引情況
SELECT D.OWNER,COUNT(1)
FROM dba_indexes d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.index_name AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
![wps5C14.tmp[4]](http://img.blog.itpub.net/blog/attachment/201602/12/26736162_14552498652IE9.jpg "wps5C14.tmp[4]"){kind=small}
一.4 遷移後續收尾工作
確保資料已經完全遷移到新的主機上後，接下來就是一些瑣碎的收尾工作，包括sys密碼，監聽，job，crontab等等工作。
-------------------------------------------------------------------------------------------------------------
一.5 總結
到此所有的處理算是基本完畢，過程很簡單，但是不同的場景處理方式有很多種，我們應該學會靈活變通。
一.6 About Me
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1987971/
本文pdf版： （提取碼：ed9b）
QQ：642808185 若加QQ請註明您所正在讀的文章標題
於 2016-01-26 10:00~ 2016-02-06 19:00 在中行完成
<版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1987974/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【TTS】傳輸表空間AIX->linux基於rmanTTSAILinux
- 【TTS】傳輸表空間Linux ->AIX 基於rmanTTSLinuxAI
- 基於Jenkins快速搭建持續整合環境Jenkins
- 基於 Docker 打造前端持續整合開發環境Docker前端開發環境
- 花椒前端基於 Docker 的 SSR 持續開發整合環境實踐前端Docker
- 真實業務環境-需求分析思路(一)
- 真實業務環境-需求分析思路(二)
- 基於Docker搭建LNMP環境DockerLNMP
- Backup And Recovery User's Guide-RMAN架構-關於RMAN環境GUIIDE架構
- 基於CentOS、docker搭建jenkins內網前端可持續整合環境CentOSDockerJenkins內網前端
- RMAN之環境配置（一）
- 基於ECS快速搭建Docker環境Docker
- K均值演算法基於CUDA環境的實現演算法
- rman 設定環境變數變數
- 基於Docker的LNMP開發環境DockerLNMP開發環境
- 基於ubuntu如何搭建TensorFlow環境Ubuntu
- 基於Webpack搭建React開發環境WebReact開發環境
- RMAN環境變數的設定變數
- hacmp環境下的rman備份ACM
- 基於 Laradock 環境 Project 的總結Project
- 搭建基於 Mac 的 Flutter 開發環境MacFlutter開發環境
- 基於 Xcode 搭建 OpenCV 開發環境XCodeOpenCV開發環境
- 基於 Vagrant 構建 PHP 開發環境PHP開發環境
- 部署基於Dragonwell的Java執行環境GoJava
- 基於滴滴雲搭建 Ceph 開發環境開發環境
- 搭建基於以太坊的私有鏈環境
- 基於Linux的 工作環境配置方法Linux
- 基於vscode搭建freertos環境VSCode
- 基於 webpack 的前後端分離開發環境實踐Web後端開發環境
- 搭建基於netfilter/iptables的防火牆實驗環境(轉)Filter防火牆
- 【K8S】基於Docker+K8S+GitLab/SVN+Jenkins+Harbor搭建持續整合交付環境（環境搭建篇）K8SDockerGitlabJenkins
- 環境互動設計：向佈局中注入真實感
- Redis叢集環境下的-RedLock(真分散式鎖) 實踐Redis分散式
- 基於Docker環境構建redis單機容器DockerRedis
- 如何構建基於 docker 的開發環境Docker開發環境
- 基於docker 搭建redis環境—redis單機版DockerRedis
- 基於 Webpack4 搭建 Vue 開發環境WebVue開發環境
- Day2——基於ECS快速搭建Docker環境Docker