NFS，空间不足用来挂载目录
服务端
mkdir /datadump chown oracle:oinstall /datadump/ cat /etc/exports /datadump 192.168.0.*(rw,sync,no_root_squash,insecure) systemctl start nfs systemctl start rpcbind
客户端
# linux
mkdir /expdp
mount -t nfs -o nolock,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 192.168.0.112:/datadump /expdp
chown -R oracle:oinstall /expdp
vi /etc/fstab
192.168.0.112:/datadump /expdp nfs nolock,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 0 0
#AIX
# vi /etc/filesystems
/arc/xtts_bak:
dev = "/arc/xtts_bak"
vfs = nfs
nodename = 192.168.0.112
mount = true
options = cio,rw,bg,hard,intr,rsize=32768,wsize=32768,timeo=600,vers=3,proto=tcp,noac,sec=sys
account = false
# mount /arc/xtts_bak
# Solaris
# mkdir -p /data/xtts
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,vers=3,suid 192.168.0.112:/data/xtts /data/xtts
mount 加上 noac 导致传输慢
Solaris NFS Mount Is extremely Slow while writing files to an NFS mount. (文档 ID 2171414.1)
Thu Jul 15 01:58:30 2021
WARNING:NFS mount of file /xtts_bak/aa/AA01_8.tf on filesystem /xtts_bak done with incorrect options:rw,suid,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,vers=3,xattr,dev=63c0009
WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,noac/actimeo=0
# mount -o rw,bg,hard,actimeo=0,nointr,rsize=32768,wsize=32768,proto=tcp,vers=3,suid 192.168.0.112:/xtts_bak /xtts_bak
Linux客户端卸载
# umount /expdp
umount.nfs: /expdp: device is busy
umount.nfs: /expdp: device is busy
# fuser -m /expdp
^C
# umount -lf /expdp
借用共享存储划分空间
当源端和目标端的本地空间都不够，而此时 还有未使用的共享存储，可以借用共享存储作为备份文件目录空间
注意： 此操作具有破坏性，请仔细核对共享存储磁盘路径
磁盘分区：
# parted /dev/asm-data08
mklabel gpt
print
mkpart primary 0 537GB # 根据实际情况划分大小
set 1 lvm on
挂载目录：
pvcreate /dev/sdk1 /dev/sdl1 vgcreate -s 512M backup_vg /dev/sdk1 /dev/sdl1 vgdisplay lvcreate -l 12282 -n backup_lv backup_vg mkfs.ext4 /dev/backup_vg/backup_lv mkdir /datadump mount /dev/backup_vg/backup_lv /datadump
根据上文使用 NFS 进行远程挂载
还原划分的存储
systemctl stop nfs
umount /datadump
lvremove backup_lv backup_vg
vgremove backup_vg
pvremove /dev/sdk1 /dev/sdl1
parted /dev/sdk
rm 1
dd if=/dev/zero of=/dev/asm-data08 bs=1k count=3000
SQL> alter diskgroup data add disk '/dev/asm-data08','/dev/asm-data09';
alter diskgroup data add disk '/dev/asm-data08','/dev/asm-data09'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15075: disk /dev/asm-data09 is not visible on instance number 2
ORA-15075: disk /dev/asm-data08 is not visible on instance number 2
lrwxrwxrwx 1 root root 4 Jul 12 10:57 /dev/asm-data09 -> sdl1 <<<<<<<<<<<<<<<<<<<节点2没变回来
lrwxrwxrwx 1 root root 4 Jul 12 10:57 /dev/asm-data08 -> sdm1
# parted /dev/sdm <<<<<<<<<<<<<<<<< parted重新识别一下
创建目录
> sqlplus / as sysdba
create directory PUMP_DIR as '/expdp';
grant read,write on directory PUMP_DIR to system;
col DIRECTORY_PATH for a100
set line 300
SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;
注意： datadump 不导 directory，如果源端有特殊的 directory ，需要手动在目标端创建
tnsnames.ora
检查源端 dblink 是否引用本地的连接串，提前将源端的 tnsnames.ora 里的连接串复制到目标端的 tnsnames.ora 中，并tnsping测试连通性。
创建临时 监听(其他端口)和DBLINK，用于调试或数据对比
$ vi /u01/app/11.2.0/grid/network/admin/listener.ora
LISTENER_1523 =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.112)(PORT=1523))
)
)
SID_LIST_LISTENER_1523 =
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=orcl1)
)
)
$ lsnrctl start LISTENER_1523
--目标端创建dblink连接源端
create public database link qianyi_dblink connect to system identified by oracle using '192.168.0.112:1523/orcl';
select name from v$database@qianyi_dblink;
整理角色，按用户导入不会导入角色的创建语句，只会导入角色授权语句
select *
from dba_roles
where ROLE in (select distinct GRANTED_ROLE
from dba_role_privs
where GRANTEE in
(select username
from dba_users
where ACCOUNT_STATUS not like 'EXPIRED%'
and USERNAME not in ('SYS', 'SYSTEM'))
and GRANTED_ROLE not in
('DBA',
'RESOURCE',
'CONNECT',
'OEM_MONITOR',
'SELECT_CATALOG_ROLE'));
select 'CREATE ROLE "' || GRANTED_ROLE || '";' CREATE_ROLE_DDL
from (select distinct GRANTED_ROLE
from dba_role_privs
where GRANTEE in
(select username
from dba_users
where ACCOUNT_STATUS not like 'EXPIRED%'
and USERNAME not in ('SYS', 'SYSTEM'))
and GRANTED_ROLE not in
('DBA',
'RESOURCE',
'CONNECT',
'OEM_MONITOR',
'SELECT_CATALOG_ROLE'));
整理PROFILE
目标端按用户导入，会自动创建用户，创建用户的语句指定了自定义的PROFILE，当PROFILE在目标端不存在时 用户将自动创建失败，从而导致后面的导入失败。
column username for a25
column account_status for a20
column temporary_tablespace for a20
col PROFILE for a20
select username,account_status,LOCK_DATE,EXPIRY_DATE,PROFILE,default_tablespace,temporary_tablespace,CREATED,AUTHENTICATION_TYPE from dba_users order by CREATED;
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED AUTHENTI
------------------------- -------------------- ------------------- ------------------- -------------------- ------------------------------ -------------------- ------------------- --------
SYSTEM OPEN DEFAULT SYSTEM TEMP 2016-09-07 10:15:38 PASSWORD
SYS OPEN DEFAULT SYSTEM TEMP 2016-09-07 10:15:38 PASSWORD
OUTLN EXPIRED & LOCKED 2016-09-07 10:15:39 2016-09-07 10:15:39 DEFAULT SYSTEM TEMP 2016-09-07 10:15:39 PASSWORD
DIP EXPIRED & LOCKED 2016-09-07 10:17:19 2016-09-07 10:17:19 DEFAULT USERS TEMP 2016-09-07 10:17:19 PASSWORD
... ...
APP_ADM OPEN SEC_PROFILE USERS TEMP 2018-04-23 16:35:17 PASSWORD
APP_APP OPEN SEC_PROFILE USERS TEMP 2018-04-23 16:35:17 PASSWORD
select * from dba_profiles where PROFILE='SEC_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
SEC_PROFILE COMPOSITE_LIMIT KERNEL UNLIMITED
SEC_PROFILE SESSIONS_PER_USER KERNEL UNLIMITED
SEC_PROFILE CPU_PER_SESSION KERNEL UNLIMITED
SEC_PROFILE CPU_PER_CALL KERNEL UNLIMITED
SEC_PROFILE LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
SEC_PROFILE LOGICAL_READS_PER_CALL KERNEL UNLIMITED
SEC_PROFILE IDLE_TIME KERNEL UNLIMITED
SEC_PROFILE CONNECT_TIME KERNEL UNLIMITED
SEC_PROFILE PRIVATE_SGA KERNEL UNLIMITED
SEC_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 10
SEC_PROFILE PASSWORD_LIFE_TIME PASSWORD UNLIMITED
SEC_PROFILE PASSWORD_REUSE_TIME PASSWORD UNLIMITED
SEC_PROFILE PASSWORD_REUSE_MAX PASSWORD UNLIMITED
SEC_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
SEC_PROFILE PASSWORD_LOCK_TIME PASSWORD 1
SEC_PROFILE PASSWORD_GRACE_TIME PASSWORD 10
这里的PROFILE使用了密码复杂度检查函数 VERIFY_FUNCTION_11G，这个函数可以使用脚本 $ORACLE_HOME/rdbms/admin/utlpwdmg.sql 创建。
SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> create profile SEC_PROFILE limit
composite_limit unlimited
sessions_per_user unlimited
cpu_per_session unlimited
cpu_per_call unlimited
logical_reads_per_session unlimited
logical_reads_per_call unlimited
idle_time unlimited
connect_time unlimited
private_sga unlimited
failed_login_attempts 10
password_life_time UNLIMITED
password_reuse_time UNLIMITED
password_reuse_max UNLIMITED
password_verify_function VERIFY_FUNCTION_11G
password_lock_time 1
password_grace_time 10;
整理表空间的创建语句，导入前需要提前创建表空间
select tablespace_name,round(sum(bytes)/1024/1024,2) tbs_size_mb from dba_data_files group by tablespace_name order by tbs_size_mb;
CREATE TABLESPACE TBS DATAFILE '+DATA' SIZE 32760M AUTOEXTEND OFF;
ALTER TABLESPACE TBS ADD DATAFILE '+DATA' SIZE 32760M AUTOEXTEND OFF;
DROP TABLESPACE <TS_NAME> INCLUDING CONTENTS AND DATAFILES;
alter database datafile '+DATA/.../.../DATAFILE/system.425.1077813511' resize 32760M;
alter database datafile '+DATA/.../.../DATAFILE/system.425.1077813511' autoextend off;
alter database datafile '+DATA/.../.../DATAFILE/sysaux.426.1077813511' resize 32760M;
alter database datafile '+DATA/.../.../DATAFILE/sysaux.426.1077813511' autoextend off;
alter database tempfile '+DATA/.../.../TEMPFILE/temp.427.1077813515' resize 32760M;
alter database tempfile '+DATA/.../.../TEMPFILE/temp.427.1077813515' autoextend off;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 32760M autoextend off;
整理LOB比较大的表，粗略的判断
set line 300 pages 1000
col OWNER for a20
col SEGMENT_NAME for a30
select ds.OWNER,
dl.TABLE_NAME,
ds.SEGMENT_NAME,
ds.SEGMENT_TYPE,
ds.TABLESPACE_NAME,
ds.BYTES / 1024 / 1024 as mb
from dba_segments ds, dba_lobs dl
where ds.SEGMENT_NAME = dl.SEGMENT_NAME
and SEGMENT_TYPE like 'LOB%'
and BYTES / 1024 / 1024 > 1024
order by MB;
精准的LOB占用空间计算：How to Compute the Size of a Table containing Outline CLOBs and BLOBs (Doc ID 118531.1)
# vi lob_table_size.sql
ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name: '
SELECT
(SELECT SUM(S.BYTES) / 1024 / 1024 -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('&SCHEMA') AND
(S.SEGMENT_NAME = UPPER('&TABNAME'))) +
(SELECT SUM(S.BYTES) / 1024 / 1024 -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('&SCHEMA') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
(SELECT SUM(S.BYTES) / 1024 / 1024 -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('&SCHEMA') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
"TOTAL TABLE SIZE MB"
FROM DUAL;
SQL> @lob_table_size.sql
Table Owner: LOB_USER
Table Name: LOB_TABLE
old 4: WHERE S.OWNER = UPPER('&SCHEMA') AND
new 4: WHERE S.OWNER = UPPER('LOB_USER') AND
old 5: (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
new 5: (S.SEGMENT_NAME = UPPER('LOB_TABLE'))) +
old 8: WHERE S.OWNER = UPPER('&SCHEMA') AND
new 8: WHERE S.OWNER = UPPER('LOB_USER') AND
old 9: (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
new 9: (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('LOB_TABLE') AND L.OWNER = UPPER('LOB_USER'))) +
old 12: WHERE S.OWNER = UPPER('&SCHEMA') AND
new 12: WHERE S.OWNER = UPPER('LOB_USER') AND
old 13: (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
new 13: (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('LOB_TABLE') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LOB_USER')))
TOTAL TABLE SIZE MB
-------------------
16662
停数据库服务
停crontab
srvctl stop listener --停监听
srvctl stop scan_listener
select OWNER,TYPE,count(1) from dba_recyclebin group by OWNER,TYPE order by OWNER;
purge dba_recyclebin;
show parameter job --1000
alter system set job_queue_processes=0; --停job
--两个节点 备份sql_plan
create table sql_plan_bak_node1 as select * from v$sql_plan;
create table sql_plan_bak_node2 as select * from v$sql_plan;
ps -ef |grep "LOCAL=NO"|grep -v grep |awk '{print "kill -9 " $2}'|sh
--停分布式事务"
select local_tran_id,state from dba_2pc_pending; --kill
alter system archive log current;
alter system checkpoint;
--检查是否还有幸存的会话
[root@rac1 scripts]# cat kill.sql
select 'alter system kill session '''||sid||','||serial#||''' immediate -- '
||username||'@'||machine||' ('||program||');' commands_to_verify_and_run
from v$session
where &1
and sid != (select sid from v$mystat where rownum = 1)
/
SQL> @kill TYPE='USER' --确保只有一个本身会话
expdp 导出
- 正常全库导出
expdp sys/xxx dumpfile=orcl%U_20211230.dmp directory=PUMP_DIR full=y parallel=16 logfile=expdp_orcl_20211230.log cluster=N EXCLUDE=STATISTICS / as sysdba
- 排除表导出，不能指定用户，如果多个用户下有相同表名，都会被排除掉
expdp sys/xxx dumpfile=orcl%U_20211230.dmp directory=PUMP_DIR full=y parallel=16 logfile=expdp_orcl_20211230.log cluster=N EXCLUDE=STATISTICS exclude=TABLE:\"IN\(\'TB_TABLE\',\'LOG_TABLE\',\'FORM_TABLE\'\)\"
或者使用parfile，不需要转义
# vi expdp_orcl.par
directory=PUMP_DIR
dumpfile=orcl%U_20211230.dmp
parallel=16
logfile=expdp_orcl_20211230.log
EXCLUDE=STATISTICS
cluster=N
full=y
EXCLUDE=TABLE:"IN('TB_TABLE','LOG_TABLE','FORM_TABLE')"
# expdp sys/xxx parfile=expdp_yxdb.par
select OWNER,TABLE_NAME from dba_tables where TABLE_NAME in ('TB_TABLE','LOG_TABLE','FORM_TABLE');
- 指定表导出，LOB比较大的或数据量特别大的表考虑单独导
# vi expdp_orcl_lob.par
directory=PUMP_DIR
dumpfile=orcl_lob_20211230_%U.dmp
parallel=16
logfile=orcl_lob_20211230.log
EXCLUDE=STATISTICS
cluster=N
TABLES=TB.TB_TABLE,LOG.LOG_TABLE,FORM.FORM_TABLE
# expdp sys/xxx parfile=expdp_orcl_lob.par
- 排除用户导出
expdp sys/xxx dumpfile=orcl%U_20211230.dmp directory=PUMP_DIR full=y parallel=16 logfile=expdp_orcl_20211230.log cluster=N EXCLUDE=STATISTICS EXCLUDE=SCHEMA:\"=\'HR\'\"
或者使用parfile，不需要转义
# vi expdp_orcl.par
directory=PUMP_DIR
dumpfile=orcl%U_20211230.dmp
parallel=16
logfile=expdp_orcl_20211230.log
EXCLUDE=STATISTICS
cluster=N
full=y
EXCLUDE=SCHEMA:"IN('TB','LOG','FORM')"
# EXCLUDE=SCHEMA:"='HR'"
# expdp sys/xxx parfile=expdp_orcl.par
- 指定用户导出
expdp sys/xxx dumpfile=orcl_hr%U_20211230.dmp directory=PUMP_DIR SCHEMAS='HR','TT' parallel=16 logfile=expdp_orcl_hr_20211230.log cluster=N EXCLUDE=STATISTICS
- 高版本数据库导入到低版本数据库，指定低版本数据库的版本号，参数 version
expdp sys/xxx dumpfile=full%U_20151117.dmp directory=dir_dp full=y parallel=4 logfile=dump20151117.log cluster=N EXCLUDE=STATISTICS version=10.2.0.4
IMPDP导入
- 如果数据量比较大且目标端是新库，可以考虑导入前关闭归档模式，导入完成后再启用归档模式，19C 可以使用TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y在不关闭归档模式的情况下导入时不 产生归档日志，下文有说明。
srvctl stop database -d orcl -o immediate
startup mount
alter database noarchivelog;
alter database open;
- 按用户导入，导入前确保提前创建了表空间，角色，PROFILE
impdp sys/xxx dumpfile=orcl%U_20220104.dmp directory=PUMP_DIR schemas='ADM','HR' parallel=16 logfile=impdp_orcl_20220104_2.log cluster=N
- 按表导入，导入前确保存在用户
impdp sys/xxx directory=DUMP_DIR dumpfile=LOB_20211211_%U.dmp parallel=16 TABLES=HR.LOB_HR logfile=impdp_LOB_20211211.log cluster=N
- 如果数据量比较大，索引比较多，可以考虑排除索引导入 EXCLUDE=INDEX，再生成索引创建语句配置参数，设置并行，执行手动创建索引。
impdp sys/xxx dumpfile=orcl%U_20220104.dmp directory=PUMP_DIR schemas='ADM','HR' cluster=N include=index sqlfile=orcl_20220104.sql
vi /datadump/orcl_20220104.sql
--替换并行度
:%s/PARALLEL 1/PARALLEL 32/g
--将以下参数放到脚本的开头，能一定程度加快索引的创建
alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events '10351 trace name context forever, level 128';
alter session set sort_area_size=734003200;
alter session set "_sort_multiblock_read_count"=128;
alter session enable parallel ddl;
--如果存在索引和表不是同一个用户的情况下，也可以在此处更正，例如有的客户将索引创建在SYSTEM用户下，表空间使用的是SYSTEM表空间。
- 导入系统授权和PUBLIC对象
impdp sys/xxx dumpfile=orcl%U_20220104.dmp directory=PUMP_DIR logfile=impdp_orcl_public_20220104.log cluster=N include=SYSTEM_GRANT,ROLE_GRANT,DB_LINK,PUBLIC_SYNONYM full=y
- 19C 在不关闭归档模式的情况下导入时 不产生归档日志
alter database no force logging;
impdp system/'oracle'@192.168.0.2:1521/pdb dumpfile=orcl%U_20210805.dmp directory=PUMP_DIR schemas='ADM','HR' TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y parallel=16 logfile=impdp_orcl_20210805.log cluster=N logtime=all exclude=index
impdp system/'oracle'@192.168.0.2:1521/pdb dumpfile=orcl%U_20210805.dmp directory=PUMP_DIR schemas='ADM','HR' cluster=N include=index sqlfile=orcl_index_20210805.sql
# 创建索引后开启force logging
alter database force logging;
删除用户重新导入
SQL> drop user TEST cascade;
drop user TEST cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> select INST_ID,SID,USERNAME,MACHINE,MODULE,STATUS from gv$session where USERNAME='NWTLCS_TEST';
INST_ID SID USERNAME MACHINE MODULE STATUS
---------- ---------- ----------- ---------- -------------------- --------
2 3698 TEST jdbcclient JDBC Thin Client INACTIVE
2 5811 TEST jdbcclient JDBC Thin Client INACTIVE
SQL> @kill USERNAME='TEST'
编译无效对象
col OWNER for a20
col STATUS for a15
col object_name for a50
col object_id for 99999999
col object_type for a30
SELECT OWNER,OBJECT_NAME, OBJECT_ID, OBJECT_TYPE,to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') CREATED,to_char(LAST_DDL_TIME,'yyyy-mm-dd,hh24:mi:ss') LAST_DDL_TIME,STATUS,TEMPORARY,GENERATED
FROM dba_objects where status<>'VALID' and owner in (select username from dba_users where ACCOUNT_STATUS in ('OPEN','EXPIRED') and username not in ('SYSTEM','SYS') and DEFAULT_TABLESPACE not in ('SYSTEM','SYSAUX'))
order by owner,object_name,OBJECT_TYPE;
@?/rdbms/admin/utlrp.sql
收集统计信息
nohup sqlplus / as sysdba <<EOF > stats.log & exec dbms_stats.gather_database_stats(estimate_percent => 10, degree => 32, granularity => 'ALL', cascade => TRUE); EOF
源端和目标端对比对象
对比对象个数
set line 300 pages 100
col object_type for a40
select owner,object_type,count(*) from dba_objects where owner in ('ADM','APP','HR') group by owner,object_type order by owner,count(*);
对比对象有效性
col OBJECT_NAME for a40
select owner,object_name,object_type,status old_status from dba_objects@qianyi_dblink t where status='VALID'
and OBJECT_NAME not like 'SYS_%$$'
and OBJECT_NAME not like 'BIN$%==%'
and owner in (select username from dba_users where ACCOUNT_STATUS in ('OPEN','LOCKED') and username not in ('SYSTEM','SYS'))
and not exists(select 1 from dba_objects where owner=t.owner and object_name=t.object_name and object_type=t.object_type)
order by object_name;
OWNER OBJECT_NAME OBJECT_TYPE OLD_STA
------------------------------ ---------------------------------------- ------------------- -------
HR AGENT_COPYX INDEX VALID
HR IDX_P_ET INDEX VALID
HR IDX_RAMSGL INDEX VALID
HR PK_BAGCK1 INDEX VALID
HR PK_BAGCK2 INDEX VALID
HR PK_BFLUX INDEX VALID
HR PK_BORIDRAW INDEX VALID
HR PK_MSTDAILY INDEX VALID
HR SYS_C0076347 INDEX VALID
HR SYS_C0076383 INDEX VALID
--DATAPUMP导入建表语句可能会自带约束，自动命名为 SYS_C*，对比下目标库是否存在相同列的 UNIQUE 索引
select OWNER,INDEX_NAME,INDEX_TYPE,UNIQUENESS from dba_indexes where OWNER='HR'
and INDEX_NAME in ('AGENT_COPYX','IDX_P_ET','IDX_RAMSGL','PK_BAGCK1','PK_BAGCK2','PK_BFLUX','PK_BORIDRAW','PK_MSTDAILY')
OWNER INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ ------------------------------ --------------------------- ---------
HR AGENT_COPYX NORMAL UNIQUE
HR IDX_P_ET NORMAL NONUNIQUE
HR IDX_RAMSGL NORMAL UNIQUE
HR PK_BAGCK1 NORMAL UNIQUE
HR PK_BAGCK2 NORMAL UNIQUE
HR PK_BFLUX NORMAL UNIQUE
HR PK_BORIDRAW NORMAL UNIQUE
HR PK_MSTDAILY NORMAL UNIQUE
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE from DBA_CONSTRAINTS where OWNER='HR' and CONSTRAINT_TYPE='U';
SQL> @ddl HR.IDX_P_ET
CREATE INDEX "HR"."IDX_P_ET" ON "HR"."P_ET" ("FILE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 731906048 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ET_DEX" ;
导入遇到过的问题
创建 UNIQUE 索引使用并行会出现以下报错：
SQL> CREATE UNIQUE INDEX "APP"."IDX_PK_ID" ON "APP"."TABLE" ('FD_ID') ... TABLESPACE "TBS" PARALLEL 16 ;
ERROR at line 1:
ORA-00600: internal error code, arguments: [15803], [15], [0], [], [], [], [], [], [], [], [], []
SQL> CREATE UNIQUE INDEX "APP"."IDX_PK_ID" ON "APP"."TABLE" ('FD_ID') ... TABLESPACE "TBS" PARALLEL 1 ;
Index created.
用户缺少创建dblink的权限
ORA-31685: Object type DB_LINK:"HR"."HR.ORACLE.COM" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "HR.ORACLE.COM" CONNECT TO "AA" IDENTIFIED BY VALUES ':1' USING 'aa'
SQL> grant CREATE DATABASE LINK to hr;
Grant succeeded.
修改RAC集群的 public IP地址（相同网段）
可能存在这种情况，当把数据迁移完成后，需要将目标环境的IP地址与原环境的IP地址进行对调，这样应用程序就不需要修改连接数据库的IP地址。
# 源端 down public 网卡
ifdown bond0
# 目标端修改 /etc/hosts
vi /etc/hosts
# 目标端检查 VIP 配置
export PATH=/u01/app/11.2.0/grid/bin:$PATH
crsctl status res -t
srvctl config vip -n rac1 # 此时 因修改了/etc/hosts，VIP的配置可能也会自动修改
srvctl config vip -n rac2
# 目标端停止监听和VIP
srvctl stop listener
srvctl stop vip -n rac1
srvctl stop vip -n rac2
crsctl status res -t
# 目标端如果VIP没有自动修改，可以使用以下方式修改
srvctl modify nodeapps -A 192.168.0.3/255.255.255.0/bond0 -n rac1
srvctl modify nodeapps -A 192.168.0.5/255.255.255.0/bond0 -n rac2
# 目标库修改 local_listener
alter system set local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))' sid='orcl1';
alter system set local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.5)(PORT = 1521))' sid='orcl2';
# 目标端检查 SCAN 配置
srvctl config scan
# 目标端停止SCAN
srvctl stop scan_listener
srvctl stop scan
crsctl status res -t
# 目标端修改SCAN
srvctl modify scan -n db-scan
srvctl config scan
# 目标端RAC依次关闭CRS，修改物理IP地址
crsctl stop crs
vi /etc/sysconfig/network-scripts/ifcfg-bond0
DEVICE=bond0
BOOTPROTO=none
ONBOOT=yes
IPADDR=192.168.0.2
NETMASK=255.255.255.0
GATEWAY=192.168.0.1
TYPE=Ethernet
BONDING_OPTS="mode=1 miimon=100"
# 目标端重启网卡
ifdown bond0
ifup bond0
# 目标端启动CRS
crsctl start crs
#检查
crsctl status res -t
lsnrctl status
lsnrctl status LISTENER_SCAN1
目标端修改完成后，可以安装以上方法再修改源端IP。
最后修改时间：2023-03-24 13:11:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
