compute sum of size_gb ON report break on report on SIZE_GB
select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024 size_gb from dba_segments
where TABLESPACE_name not in(SYSTEM,SYSAUX) group by TABLESPACE_NAME;
set line 200 pages 999
select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
from dba_users
where USERNAME not in (select * from DBA_USERS_WITH_DEFPWD) and USERNAME not in(SYST
EM,SYS,OUTLN,MGMT_VIEW,DBSNMP,ANONYMOUS,CTXSYS,SYSMAN,MDSYS,ORDPLUG
INS,SI_INFORMTN_SCHEMA,OLAPSYS,DMSYS,XDB,EXFSYS,ORDSYS,WMSYS,SCOTT,
ORACLE_OCM,DIP,TSMSYS,MDDATA,APEX_030200,APPQOSSYS,OWBSYS,ORDDATA,FLO
WS_FILES,OWBSYS_AUDIT,APEX_PUBLIC_USER);
(要注意生产的表空间的一天的增量备份会有多大,增量备份周期越短越好)
select dbtimezone from dual;
如果源和目标不一致,则检查有没有列类型为timezone的表。
Data Pump TTS ImportFails With ORA-39002 And ORA-39322 DueTo TIMEZONE Conflict (Doc ID 1275433.1)
set linesize 200 pages 999
col OWNERfor a30
col OBJECT_NAME for a30
col OBJECT_TYPE for a25
select owner,object_name,object_type ,status from all_objects where status = INVAL ID;
select owner, index_name, status from dba_indexes where status=UNUSABLE order by 1, 2;
no rows selected
select i.owner, i.index_name, p.partition_name, p.status from dba_ind_partitions p,db a_indexes i where p.index_name=i.index_name and p.status=UNUSABLE order by 1,2,3;
no rows selected
select i.owner,i.index_name,s.subpartition_name,s.status from dba_ind_subpartitions s ,dba_indexes i
where s.index_name=i.index_name and s.status=UNUSABLE order by 1,2,3;
no rows selected
select owner,queue_table,recipients,compatible
from dba_queue_tables
where recipients=MULTIPLE and compatible like %8.0%;
set line 200 pages 999
col opatch for a40
col comments for a80
select opatch,comments from dba_registry_history;
Patch 19023822,修复目标端使用dbms_file_transfer.get_file包获取源端数据文件出现ORA-03106的情况。
col file_name for a45
select file_name, bytes / 1024 / 1024 / 1024 G
from dba_data_files
order by 1;
select count(*)
from (select file_name, bytes / 1024 / 1024 / 1024 G
from dba_data_files
order by 1);
select count(*)
from (select distinct file_name from dba_data_files order by 1);
select count(*) from (
select distinct(substr(file_name,26)) from dba_data_files 3 )
col OWNER for a25
col LOC for a15
col SCHEMA_URL for a150
set line 200 pages 9999
SELECT OWNER, LOCAL, SCHEMA_URL FROM DBA_XML_SCHEMAS;
create database link ttslink connect to db****** identified by xxx using ****;
create public database link XTTSLINK connect to perfstat identified by perfstat using
XTTSSRE;
select rownum, tablespace_name, type from ( select s.tablespace_name, l.encrypted type from dba_tablespaces@XTTSLINK s,
dba_tablespaces L where s.tablespace_name = l.tablespace_name(+) and l.tablespace_name is null and s.contents=PERMANENT order by 1 );
ROWNUM TABLESPACE_NAME TYP ---------- ------------------------------ ---
1 DBDATA
2 DBIDX
3 PART201211
4 PART201211_IDX
5 PART201212
6 PART201212_IDX
7 PART201301
8 PART201301_IDX
9 PART201302
10 PART201302_IDX
11 PERFSTAT
ROWNUM TABLESPACE_NAME TYP ---------- ------------------------------ ---
12 USERS
--除不参与传输的表空间外(SYSTEM,SYSAUX,PERFSTAT,XDB等),不能有重名表空间。
--提前检查+实施前检查。
select rownum, tablespace_name, type
from (
select s.tablespace_name, l.encrypted type
from dba_tablespaces@xttslink s, dba_tablespaces L
where s.tablespace_name = l.tablespace_name and s.contents=PERMANENT
order by 1);
set line 200 pages 999
col owner for a20;
col segment_name for a40;
col tablespace_name for a40;
col segment_type for a30;
select distinct owner ,segment_name, tablespace_name,segment_type
from dba_segments@xttslink s, dba_users@xttslink u
where s.owner=u.username and u.default_tablespace not in (SYSAUX, SYSTEM) and s.t ablespace_name in (SYSTEM, SYSAUX);
declare
checklist clob;
i number := 0;
begin for ts in (select tablespace_name from dba_tablespaces where tablespace_name not in (SYSTEM,SYSAUX) and contents = PERMANENT) loop if (i=0) then checklist := ts.tablespace_name;
else checklist := checklist||,||ts.tablespace_name; end if; i := 1;
end loop;
dbms_tts.transport_set_check(checklist,TRUE,TRUE);
end;
/--检查是否存在记录,如不存在,则正确。
select * from transport_set_violations;
SELECT T.ROLE
FROM (SELECT S.ROLE ROLE,L.ROLE DIFFER_ROLE,S.PASSWORD_REQUIRED,L.AUTHENTICATION_TYPE FROM DBA_ROLES@XTTSLINK S, DBA_ROLES L WHERE S.ROLE = L.ROLE(+)) T
WHERE T.DIFFER_ROLE IS NULL;
create role ROLE_OPER;
grant SELECT ANY DICTIONARY,SELECT ANY TABLEto ROLE_OPER;
select * from dba_sys_privs where grantee =ROLE_OPER;
GRANTEE PRIVILEGE ADM ------------------------------ -----------------------------
ROLE_OPER SELECT ANY DICTIONARY NO
ROLE_OPER SELECT ANY TABLE NO
select distinct(t.pro) from
(select s.profile pro, l.profile pro2
from dba_profiles@xttslink s, dba_profiles l
where s.profile = l.profile(+)) t where t.pro2 is null
order by t.pro;
PRO
---------------------------------------------
LIMIT_USER
MUSIC_BASE
create profile LIMIT_USER limit PASSWORD_GRACE_TIME 7;
create profile MUSIC_BASE limit PASSWORD_GRACE_TIME 7;
set line 200 pages 999
col PROFILE for a25
col LIMIT for a45
col RESOURCE_NAME for a30
col RESOURCE_TYPE for a25
select * from dba_profiles@xttslink where PROFILE=upper(&pro_name) and LIMIT not in (DEFAULT);
select ALTER profile ||PROFILE || limit || RESOURCE_NAME|| ||LIMIT ||; f rom dba_profiles@xttslink where PROFILE=upper(&pro_name) and LIMIT not in (DEFAULT );
set line 200 pages 999
select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
from dba_users@xttslink
where username in (select s.USERNAME name
from dba_users@xttslink s, dba_users l
where s.username = l.username(+)
and l.username is null);
select alter user || username ||
default tablespace || default_tablespace || temporary tablespace ||
temporary_tablespace || ;
from dba_users@xttslink
where username in (select s.USERNAME name from dba_users@xttslink s, dba_users l where s.username = l.username(+) and l.username is null);
alter user DBMBOPR default tablespace DATAWEB temporary tablespace TEMP;
alter user YANGHUAN default tablespace USERS temporary tablespace TEMP;
alter user YD_CHENSHUAI default tablespace USERS temporary tablespace TEMP;
alter user YINQI default tablespace TBS_OPER temporary tablespace TEMP;
CREATE TEMPORARY TABLESPACE "TEMP2" TEMPFILE +data;
select s.USERNAME name
from dba_users@xttslink s, dba_users l
where s.username = l.username(+)
and l.username is null
order by name;
--创建用户并使用原密码
先查询出源密码再创建用户:
set line 300 pages 0
select alter user || name || identified by values || ||
PASSWORD || ;
from sys.user$@xttslink
where name in (select s.USERNAME name
from dba_users@xttslink s, dba_users l
where s.username = l.username(+)
and l.username is null);
set line 200 pages 999
select create user || USERNAME || identified by oracle; from dba_users@xttslink s
where s.username in (select s.USERNAME name
from dba_users@xttslink s, dba_users l
where s.username = l.username(+)
and l.username is null);
select OWNER||.||TABLE_NAME from dba_tables where OWNER in(xx) and NUM_ROWS=0;
set line 200 pgaes 9999
select alter table || OWNER||.||TABLE_NAME || allocate extent; from dba_tables where num_rows=0 and OWNER in(xx)
若存在,deferred_segment_creation参数需修改后true,迁移完成后修为false。
set head off
set linesize 500 pages 999999
spool tts_sys_privs.sql
-- Grant Table privileges
select grant || privilege || on " || owner || "." || table_name ||
" to " || grantee || " ||
decode(grantable, YES, with grant option ) ||
decode(hierarchy, YES, with hierarchy option ) || ;
from dba_tab_privs a
where owner in
(select name from system.logstdby$skip_support where action = 0)
and grantee in
(select username
from dba_users
where username not in (select name
from system.logstdby$skip_support
where action = 0))
and a.table_name not in (select DIRECTORY_NAME from dba_directories);
-- Grant Column privileges
select grant || privilege || ( || column_name || ) || on ||
owner || . || table_name || to || grantee || ||
decode(grantable, YES, WITH Grant option) || ;
from dba_col_privs
where owner in
(select name from system.logstdby$skip_support where action = 0) and grantee in
(select username from dba_users where username not in
(select name from system.logstdby$skip_support where action = 0));
--Grant directories privileges. select grant || privilege || on directory " || owner || "." || table_name || " to " || grantee || " || decode(grantable, YES, with grant option ) || decode(hierarchy, YES, with hierarchy option ) || ; from dba_tab_privs a where owner in (select name from system.logstdby$skip_support where action = 0) and grantee in (select username from dba_users where username not in (select name from system.logstdby$skip_support where action = 0)) and a.table_name in (select DIRECTORY_NAME from dba_directories); select GRANT ||PRIVILEGE|| TO ||GRANTEE||; from dba_sys_privs where grantee in (SRPT,WEBSW);
--注意修改对应的schema spool off set head on
set line 200 pages 999
col NAME for a35 col VALUE$ for a35
col COMMENT$ for a75
select * from props$ where name like %CHARACTERSET%;
NAMEVALUE$ COMMENT$
------------------------------------- ---------------------
NLS_CHARACTERSETZHS16GBK Character set NLS_NCHAR_CHARACTERSETAL16UTF16 NCHAR Character set
select * fromsys.props$@XTTSLINK where name like %CHARACTERSET%;
tablespaces= {需要传输的表空间}
platformid= {源端平台号,select * from v$transportable_platform order by platform_id 语句查询得到)}
srcdir= {当使用dbms_file_transfer时使用,表示源库存放数据文件的路径(数据库里的dir,不是os上的 dir)}
dstdir= {当使用dbms_file_transfer时使用,表示目标库存放数据文件的路径(数据库里的dir,不是os上的dir)}
srclink= {从目标端指向源端的dblink,当使用dbms_file_transfer时使用}
dfcopydir= {源端datafile convert保存路径}
backupformat= {源端增量备份保存路径}
stageondest= {目标端备份文件,建议和backupondest设置为同一路径}
backupondest= {目标端备份文件保存路径,建议和stageondest设置为同一路径}
storageondest= {目标端恢复到数据库的数据文件保存位置,如果是diskgroup则需要注意还需要对应的数据库名}
cnvinst_home= {新环境$ORACLE_HOME}
cnvinst_sid= {新环境ORACLE_SID}
asm_home= {如果是直接恢复到diskgroup,则需要指定对应的grid_home和ASM实例}
asm_sid= {如果是直接恢复到diskgroup,则需要指定对应的grid_home和ASM实例}
parallel= {设置并行,建议设置为6或者8,根据CPU情况设置}
rollparallel= {roll forward 并行,如果数据库大的话建议设置为8}
getfileparallel= {getfile 并行度,建议设置为6或者8,根据CPU情况设置}
desttmpdir= {设置目标端tempdir目录,建议指向xtts目录)}
tablespaces=test1,test2
platformid=13
srcdir=SREDIR,DIR02 --如果有多个用逗号分隔
dstdir=TARDIR
srclink=ttslink
dfcopydir=/stage_source
backupformat=/backup_data
stageondest=/backup_data
backupondest=/backup_data storageondest=+DATA/ora11g/datafile
cnvinst_home=/u01/app/oracle/product/11.2/db_1 cnvinst_sid=ora11g
asm_home=/u01/app/11.2.0/grid
asm_sid=+ASM
parallel=3
rollparallel=2
getfileparallel=4
确定迁移表空间。
create directory SREDIR as +data/ZHDB/datafile;
create directory TARDIR as +data/ZHDB/datafile;
注意源端DIR与目标端DIR需要满足以下要求中的一个:
export TMPDIR=/backup
export XTTDEBUG=1
perl xttdriver.pl -S
perl xttdriver.pl -G
【source system】, logged in as the oracle user with
the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database,
run the create inc remental step as follows:
$ su – oracle
$ export XTTDEBUG=1 (打开debug功能)
$ TMPDIR=/backup <--必须
$ export TMPDIR
$ cd $TMPDIR
$ nohup perl xttdriver.pl -i > xttdriver.pl_i.out
scp incrbackups.txt tsbkupmap.txt xttplan.txt ora11204:/home/oracle/xtts
scp `cat incrbackups.txt` ora11204:/home/oracle/xtts
4.2【源库端】将增量备份文件和关键文件传到目标端
scp `cat incrbackups.txt` oracle@dest:/stage_dest
$ export XTTDEBUG=1 (打开debug功能)
$ export TMPDIR=/backup <--必须
$ env|grep ORA
$ cd $TMPDIR
nohup perl xttdriver.pl -r > xttdriver.pl_r.out &
perl xttdriver.pl -s
【源库端】第2-N次增量备份
视具体情况,重复进行增量备份恢复。
srvctl stop listener
alter tablespace USERS read only;
alter tablespace PERFSTAT read only;
alter tablespace DBDATA read only;
alter tablespace DBIDX read only;
alter tablespace PART201211 read only;
alter tablespace PART201212 read only;
alter tablespace PART201301 read only;
alter tablespace PART201302 read only;
alter tablespace PART201211_IDX read only;
alter tablespace PART201212_IDX read only;
alter tablespace PART201301_IDX read only;
alter tablespace PART201302_IDX read only;
select TABLESPACE_NAME,status from dba_tablespaces;
select tablespace_name ,status from dba_tablespaces
where tablespace_name not in (sel ect tablespace_name
from dba_temp_files) order by 2;
su – oracle
export XTTDEBUG=1 (打开debug功能)
TMPDIR=/xxxx/xxxxx/xxxx <--必须export TMPDIR
cd $TMPDIR
nohup perl xttdriver.pl -i > xttdriver.pl_i.out
scp incrbackups.txt oracle@dest: /home/oracle/xtt
scp xttplan.txt oracle@dest: /home/oracle/xtt
scp tsbkupmap.txt oracle@dest: /home/oracle/xtt
su – oracle
cd $TMPDIR
env|grep ORA (检查ORA*环境变量)
env|grep TMP (检查TMPDIR设置 )
export XTTDEBUG=1 (打开debug功能)
nohup perl xttdriver.pl -r > xttdriver.pl_r.out &
CONVERTED BACKUP PIECE/ogg/11204/xtts/xib_0jpuu017_1_1_5
PL/SQL procedure successfully completed.
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
alter database mount
alter database open
(以上步骤是脚本自动将目标库重启,不需要人工干预,如果出现到mount状态出现异常,根据情况手工执行后续命令)
CREATE USER "PERFSTAT" IDENTIFIED BY VALUES AC98877DE1297365
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP"
PROFILE "JKPROFILE";
CREATE USER "CMS_QK" IDENTIFIED BY VALUES 77BA35F33A8487D6
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "HTWH" IDENTIFIED BY VALUES 87FFC382FE7C1DA2
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "MONITOR" IDENTIFIED BY VALUES A3A1625C0BBFBB11
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "CMS_QK_MON" IDENTIFIED BY VALUES 363F33F53D8E54BD
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "CMS_WEB" IDENTIFIED BY VALUES 64AA14665800955C
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "GGMGR" IDENTIFIED BY VALUES 0282C015C1E7863A
DEFAULT TABLESPACE "PERFSTAT"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "CP_CMS" IDENTIFIED BY VALUES 2D31CF8737D9DF4A
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "RWPS" IDENTIFIED BY VALUES C414AE2DF79E4D88
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "QA_ADM" IDENTIFIED BY VALUES 96E1D9DBE01FE02A
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "TONGJI" IDENTIFIED BY VALUES AC21D229C2D4B335
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "LIPING" IDENTIFIED BY VALUES 10FA98B59CE40EAC
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "CMS_KARA" IDENTIFIED BY VALUES A54B542ECE4941CB
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "TMP" IDENTIFIED BY VALUES 03F30EDF384785D9
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "MIGUER" IDENTIFIED BY VALUES 8BA29466C43A2BA5
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "ZHENGPQ" IDENTIFIED BY VALUES DA33E25E0F999444
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "TAOCHAO" IDENTIFIED BY VALUES FFAE2FBDA1C6B120
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "MIGU" IDENTIFIED BY VALUES 19C0D04708B4AA31
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP"
PROFILE "LIMIT_USER";
CREATE USER "ZABBIX" IDENTIFIED BY VALUES S:3EBE9EF3CF34E469BD2C1BD04EA25AAD8B95BA89 A2D1E9C2B7E080AE827B;9A31F4B8D0743A01
DEFAULT TABLESPACE "USERS_OLD"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "GGMGR" IDENTIFIED BY VALUES 0282C015C1E7863A
DEFAULT TABLESPACE " USERS_OLD
TEMPORARY TABLESPACE "TEMP";
Select table_name,owner from dba_tables where TEMPORARY=Y; Set long 9999
Select dbms_metadata.get_ddl(table,table_name,owner) from dual;
1 perl xttdriver.pl -e
cat xttplugin.txt
impdp directory=
logfile=log>
network_link=
transport_full_check=no
transport_tablespaces=TTS1,TTS2,TTS3
transport_datafiles=+DATA/orcl/datafile/tts1_267_946218825,+DATA/orcl/datafile/tts 2_268_946218837,+DATA/orcl/datafile/tts3_269_946218841
userid=/ as sysdba
directory=mydir
dumpfile=xtts.dmp
TRANSPORT_TABLESPACES=TTS2,TTS1,TTS3 --从xttplugin.txt文件里取
transport_full_check=no
exclude=STATISTICS,INDEX_STATISTICS,TABLE_STATISTICS --仅仅只写STATISTICS,仍然会导出统计信息
userid=/ as sysdba
directory=mydir
dumpfile=xtts.dmp
transport_datafiles=+data/orcl/DATAFILE/tts1_267_946218825,+data/orcl/DATAFILE/tts
2_268_946218837,+data/orcl/DATAFILE/tts3_269_946218841
cluster=n ----从xttplugin.txt文件里取
userid=/ as sysdba
directory=mydir
dumpfile=metadata.dmp
schemas=zly1,zly2,zly3
content=metadata_only exclude=index,table,constraint,STATISTICS
parallel=1
exclude=STATISTICS
userid=/ as sysdba
directory=mydir
dumpfile=metadata.dmp
parallel=1
alter tablespace USERS read write;
alter tablespace PERFSTAT read write;
alter tablespace DBDATA read write;
alter tablespace DBIDX read write;
alter tablespace PART201211 read write;
alter tablespace PART201212 read write;
alter tablespace PART201301 read write;
alter tablespace PART201302 read write;
alter tablespace PART201211_IDX read write;
alter tablespace PART201212_IDX read write;
alter tablespace PART201301_IDX read write; alter tablespace PART201302_IDX read write;
set line 230 pages 999
SELECT CREATE PUBLIC SYNONYM ||SYNONYM_NAME|| FOR ||TABLE_OWNER||.||TABLE_NAME|
|;
FROM DBA_SYNONYMS WHERE TABLE_OWNER in (**,test) AND OWNER = PUBLIC;
(create_synonym.txt)
SQL>@?/rdbms/admin/utlrp.sql
grant execute on sys.DBMS_AQ to QA_ADM;
alter user MIGU default tablespace USERS ;
alter user PERFSTAT default tablespace DBDATA ;
alter user GGMGR default tablespace PERFSTAT ;
alter user MIGUER default tablespace DBDATA ;
alter user TONGJI default tablespace DBDATA ;
alter user LIPING default tablespace DBDATA ;
alter user ZABBIX default tablespace USERS ;
alter user MONITOR default tablespace DBDATA ;
alter user RWPS default tablespace DBDATA ;
alter user CMS_QK_MON default tablespace DBDATA ;
alter user TMP default tablespace USERS ;
alter user CMS_KARA default tablespace DBDATA ;
alter user CMS_QK default tablespace DBDATA ;
alter user QA_ADM default tablespace DBDATA ;
alter user CMS_WEB default tablespace DBDATA ;
alter user CP_CMS default tablespace DBDATA ;
alter user TAOCHAO default tablespace USERS ;
alter user HTWH default tablespace DBDATA ;
alter user ZHENGPQ default tablespace USERS ;
set linesize 200 pages 999 long 999999
select dbms_metadata.get_ddl
(TABLE,upper(t.TABLE_NAME),upper(t.OWNER)) from dba_ta bles t where TEMPORARY=Y and owner=RPT;
purge recyclebin;
select r.owner, r.segment_type, r.remote_cnt Source_Cnt, l.local_cnt Target_Cnt
from ( select owner, segment_type, count(owner) remote_cnt
from dba_segments@xttslink
where owner not in
(select name
from system.logstdby$skip_support
where action=0)
AND SEGMENT_NAME NOT LIKE BIN% group by owner, segment_type ) r, ( select owner, segment_type, count(owner) local_cnt
from dba_segments
where owner not in
(select name
from system.logstdby$skip_support
where action=0) AND SEGMENT_NAME NOT LIKE BIN% group by owner, segment_type ) l
where l.owner (+) = r.owner
and l.segment_type (+) = r.segment_type
and nvl(l.local_cnt,-1) != r.remote_cnt
order by 1, 3 desc
10. 验证新旧环境object_type
select r.owner, r.object_type, r.remote_cnt Source_Cnt, l.local_cnt Target_Cnt
from ( select owner, object_type, count(owner) remote_cnt
from dba_objects@xttslink
where owner not in
(select name
from system.logstdby$skip_support
where action=0) group by owner, object_type ) r
, ( select owner, object_type, count(owner) local_cnt
from dba_objects
where owner not in
(select name
from system.logstdby$skip_support
where action=0) group by owner, object_type ) l
where l.owner (+) = r.owner
and l.object_type (+) = r.object_type
and nvl(l.local_cnt,-1) != r.remote_cnt
order by 1, 3 desc;
select owner,object_name,SUBOBJECT_NAME,object_type from dba_objects@xttslink
where status = VALID and owner in (XXXX)
minus
select owner,object_name ,SUBOBJECT_NAME,object_type from dba_objects
where status = VALID AND owner in (XXXX); 注意修改
select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,
TABLE_TYPE,STATUS from DBA_indexes where
INDEX_NAME=SYS_IL0000886564C00010$$
select
OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,
TABLE_TYPE,STATUS from DBA_indexes where
TABLE_NAME=IVR_CALL_20160627;
alter session set nls_date_format = yyyy-mm-dd hh24:mi:ss;
select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from
dba_objects where OBJECT_NAME=SYS_TEMP_FBT;
select l.owner, l.object_type, r.remote_cnt Source_Cnt, l.local_cnt Target_Cnt
from ( select owner, object_type, count(owner) remote_cnt
from dba_objects@xttslink
where owner not in
(select name
from system.logstdby$skip_support
where action=0) and status=INVALID
group by owner, object_type ) r
, ( select owner, object_type, count(owner) local_cnt
from dba_objects
where owner not in
(select name
from system.logstdby$skip_support
where action=0) and status=INVALID
group by owner, object_type ) l
where l.owner = r.owner (+)
and l.object_type = r.object_type (+)
and l.local_cnt != nvl(r.remote_cnt,-1)
order by 1, 3 desc
1 crsctl stop crs
[oracle@mhdb1 backup]$ srvctl config nodeapps -n mhdb1 -a
-n option has been deprecated.
Network exists: 1/10.25.136.0/255.255.255.0/bond0, type static
VIP exists: /mhdb1-vip/10.25.136.12/10.25.136.0/255.255.255.0/bond0, hosting node mhd b1
[oracle@mhdb1 backup]$ srvctl config nodeapps -n mhdb2 -a
-n option has been deprecated.
Network exists: 1/10.25.136.0/255.255.255.0/bond0, type static
VIP exists: /mhdb2-vip/10.25.136.14/10.25.136.0/255.255.255.0/bond0, hosting node mhd b2
srvctl stop database –d zhdb
srvctl stop vip –n mhdb1 -f
srvctl stop vip –n mhdb2 -f
10.25.136.37 mhdb1-vip
10.25.136.38 mhdb2-vip
10.25.136.240 mhdb-scanip
srvctl modify nodeapps -n mhdb1 -A mhdb1-vip/255.255.255.0/ bond0
srvctl modify nodeapps -n mhdb2 -A mhdb2-vip/255.255.255.0/ bond0
srvctl config nodeapps -a
srvctl stop scan_listener
srvctl stop scan
srvctl modify scan –n mhdb-scanip srvctl config scan
srvctl start scan
srvctl start scan_listener
[oracle@mhdb1 backup]$ srvctl config scan
SCAN name: mhdb-scanip, Network: 1/10.XX.136.0/255.255.255.0/bond0 SCAN VIP name: scan1, IP: /mhdb-scanip/10.25.136.16
./crsctl stop crs –f
./crsctl start crs
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ZHDB 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 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 TRU
E ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /oracle/app/oracle/product/11.2.0/db/dbs/snap
cf_zhdb1.f; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
原因:缺少索引。
解决办法:创建索引。具体参看如下文档:
Conventional And Direct Path Exports/Imports Are Extremely Slow (文档 ID 729248.1)
Cant locate strict.pm in @INC (@INC contains: /u01/oracle/11.2.0/grid/perl/lib/5.10. 0/x86_64-linux-thread-multi /u01/oracle/11.2.0/grid/perl/lib/5.10.0 /u01/oracle/11.2. 0/grid/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/oracle/11.2.0/grid/pe rl/lib/site_perl/5.10.0 /u01/oracle/11.2.0/grid/lib
/u01/oracle/11.2.0/grid/lib/asmcm d
/u01/oracle/11.2.0/grid/rdbms/lib/asmcmd
/u01/oracle/11.2.0/grid/perl/lib/5.10.0/x8 6_64-linux-
thread-multi /u01/oracle/11.2.0/grid/perl/lib/5.10.0
/u01/oracle/11.2.0/gr id/perl/lib/site_perl/5.10.0/x86_64-
linux-thread-multi /u01/oracle/11.2.0/grid/perl/l
ib/site_perl/5.10.0
/u01/oracle/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-
multi
/u01/oracle/11.2.0/grid/perl/lib/5.10.0/x86_64-linux-thread-
multi /u01/oracle/11.2.0/ grid/perl/lib/5.10.0
/u01/oracle/11.2.0/grid/perl/lib/site_perl/5.10.0/x86_64-
linux-t
hread-multi
/u01/oracle/11.2.0/grid/perl/lib/site_perl/5.10.0 /u01/oracle/11.2.0/grid
/perl/lib/site_perl .) at
/u01/oracle/11.2.0/grid/bin/asmcmdcore line 143.
BEGIN failed--compilation aborted at
/u01/oracle/11.2.0/grid/bin/asmcmdcore line 143. Cant Run ASMCMD As Non-Product Owning User (文档 ID 1668244.1)
su - grid
chmod -R 751 $ORACLE_HOME/perl
chmod 770 -R $ORACLE_HOME/log/diag/asmcmd chmod 1777 $ORACLE_HOME/log/diag
chmod 660 $ORACLE_HOME/lib/libexpat.so.1
dir=/data/backup
tfile=`ls ${dir}/*.trc`
while true
do
echo ************************* completed files *****************************echo " `cat ${tfile}|grep input datafile|wc -l` file backups com
pleted"
echo
echo ************************* filesystem used *****************************
bdf
echo
echo ******************************************** rman backup process ************************************************
sqlplus -S / as sysdba< SET linesize 250 PAGES 0 FEEDBACK OFF VERIFY OFF HEADING OFF
col OPNAME for a45
SELECT SID,
OPNAME,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE RMAN%
AND OPNAME NOT LIKE %aggregate%
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
order by "%_COMPLETE" desc ;
disconnect;
exit;
EOF
echo
echo
sleep 60
dones
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129235.html
摘要:摘要第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是,阿里云给出了上面的解决方案。 摘要: 2018第九届中国数据库技术大会,阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统,面对Oracle迁移上云的壁垒,阿里云如何能够打破它呢?本文提出了Oracle 到云数据库P...
摘要:客户可以通过一款移动应用监控其迁移状态。表示,随着产品在后续版本中不断调整,将转向自主交付。然而认为,提供了更加全面的解决方案,包括咨询和教育服务。现在已经支持向和迁移的以及客户。最终将把产品扩展到支持向转移的和客户,以及向转移的客户。Oracle Soar将一系列自动迁移工具与专业服务相结合,所有这些都由Oracle提供——这是一套完整的内部迁移解决方案。这种半自动化的解决方案,也让Ora...
阅读 1249·2023-01-11 13:20
阅读 1557·2023-01-11 13:20
阅读 1011·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3971·2023-01-11 13:20
阅读 2519·2023-01-11 13:20
阅读 1310·2023-01-11 13:20
阅读 3486·2023-01-11 13:20