暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle1

oracle1

原创 yczloveyy 2023-08-28
352
序号 　 环境 　 步骤说明 检查结果 详细操作 GBS 操作人 复核
1 　 OLD 　 统一临时表空间 ALTER USER GBSLOGTMP temporary tablespace TEMP;
ALTER USER GPOLAPETL temporary tablespace TEMP;
ALTER USER EGISNBULOGTMP temporary tablespace TEMP;		 select distinct  du.temporary_tablespace from dba_users du;
都返回temp。
新库新增临时表空间。
create temporary  tablespace I3_ORCL_TMP   tempfile '+DATA_GBS_DG' size 100m autoextend on;		 no 　 　
2 　 关联库 　 关联数据库TNSNAMES.ORA 更新 　 VIP  会切换到新库 ，关联库无需更新IP  　 　 　
3 　 NEW 　 创建X86目标库（新库） 　 0、新库按照最新的19C软件安装手册；
1、SCAN监听配置1526/1527/1528
[grid@cnsz083680 ~]$ srvctl modify scan_listener -p tcp:1526/tcp:1527/tcp:1528      
[grid@cnsz083680 ~]$

2、配置一个1530的静态监听给部署使用(每台机器上都创建一个静态监听)；
GBS1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.33.30.209)(PORT = 1530))
    )
  )
SID_LIST_GBS1 =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /paic/app/oracle/rdbms/19c/19.14.0)
      (SID_NAME = gbs1)
    )
  )
ADR_BASE_GBS1=/paic/stg/oracle/rdbms
SUBSCRIBE_FOR_NODE_DOWN_EVENT_GBS1=OFF

备注
USERS表空间使用新名字 users_19c, 避免与要迁移的数据库一致. 如果不小心建了, 可以通过以下命令重命名:
alter tablespace users rename to users_19c;
3. 新旧库字符集要求一致并复核
select * from v$nls_parameters a where a.PARAMETER in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET') ;
4. 新旧库block size要求一致并复核
select name ,a.VALUE from v$parameter a where a.Name = 'db_block_size';
5. 新旧库安装的组件要求一致并复核（确认旧库未使用的组件可以在新库不安装）
select COMP_NAME from dba_registry order by 1; select * from dba_feature_usage_statistics where LAST_USAGE_DATE is not null;
6. system,sysaux,temp,undo表空间大小，要求不小于旧库并复核
select df.tablespace_name ,round( sum( df.bytes )/1024/1024)||'M' tbs_size, count(1) file_count from dba_data_files df
where df.TABLESPACE_NAME IN( 'SYSTEM','SYSAUX','UNDOTBS1','TEMP','旧库特有TMP和UNDO')
group by df.tablespace_name



1.  在提申请时，需特别说明APPMGR、DBMGR、DMLBAK这3个用户不需要创建。同时要特别提醒，新建目标库只分配VIP即可，不分配不配置域名
2、 新旧库字符集要求一致并复核
3. 新旧库block size要求一致并复核
4. 新旧库安装的组件要求一致并复核（确认旧库未使用的组件可以在新库不安装）
5. system,sysaux,temp,undo、USERS_19C表空间大小，要求不小于旧库并复核；
6、调整DB_FILES、db_cache、shared_pool参数；
7、临时关闭job_queue_processes参数；
8、检查新库patch是否符合规范；
9、检查redo文件位置，大小；
10、复制旧库的tnsnames.ora文件；
11、复制旧库的crontab；
12、重新配置好aud$、unified audit相关  @刘用；
13、复制/etc/paic/下的相关脚本，配置
14、检查monitor库、isw库相关；
15、检查ZABBIX监控相关是否正常；
16、修改统计信息的策略，部署19C新的统计信息程序；  @新淦、陈晨
17、手动创建lugz0.world的service；
18、提前通知存储备份组做好 rman19c_lugz0/rman19c_lugz0@catalog 新用户的配置；
20、提前处理USERS表空间，RENAME为USERS_19C；
21、19C版本上RHP组件是否已删除；


 yes 　 　
4 　 OLD/NEW 　 在源库和目标库主机挂载中转临时NAS卷 　 存储空间评估：
              DBMS_file_transfer传文件：
              源主机  : 一次增备需要的空间
              目标主机:  2次增备需要的空间		 yes 　 　
5 　 NEW 　 重命名USERS表空间 　 alter tablespace users rename to users_19c;
select name from v$tablespace;
如果迁移USERS表空间, 需要在新库创建一个 users_19c的表空间. 
create tablespace users_19c ;		 yes 　 　
6 　 NEW 　 建目标端DIR 　 create or replace directory DESTDIR  as '+DATA_GBS_DG/GBS/DATAFILE';
select owner,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DESTDIR';		 yes 　 　
7 　 NEW 　 新库的表空间扩容 system 19个--2个
sysaux 26个--6个
undo 42个--4*15
temp 45个		 alter tablespace system add datafile '+DATA_GBS_DG' size 1g autoextend on maxsize unlimited;

alter tablespace sysaux add datafile '+DATA_GBS_DG' size 1g autoextend on maxsize unlimited;

alter tablespace undotbs1 add datafile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;
alter tablespace undotbs2 add datafile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;
alter tablespace undotbs3 add datafile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;
alter tablespace undotbs4 add datafile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;

alter tablespace temp add tempfile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;

create tablespace I3_ORCL datafile '+DATA_GBS_DG' size 10m autoextend on maxsize unlimited;
create temporary  tablespace I3_ORCL_TMP   tempfile '+DATA_GBS_DG' size 100m autoextend on maxsize unlimited;		 yes 　 　
8 　 OLD 　 旧库初始化升级用户 MIGUPS
旧库创建表空间：DBADATA		 　 cd /paic/tmp/nasshare/licw/t0gbs/source/script/MIGUPS_init  --- 需要根据情况调整脚本
@00_migups.sql     -- 默认dbadata表空间
@01_src_create_config_info.sql
@02_init_tbs_info.sql
@03_init_user_info.sql		 yes 　 　
9 　 NEW 　 新库初始化环境 不能初始化是因为glogin配置 先修改sys密码：
alter user sys identified by paic1234 account unlock;
然后再tnsname.ora里添加本实例的tnsname。
新库初始化环境
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
cd /paic/tmp/nasshare3/licw/gbs/target/db_init/1
按照readme的步骤执行   ./db_init.sh egis		 yes 　 　
10 　 NEW 　 新库安排DROP掉如始化过程中产生的用户； 　 DROP user APPMGR cascade;
DROP user DBQINTF cascade;
DROP user DBMGR cascade;
DROP user DMLBAK cascade;
DROP user DBMONOPR cascade;
DROP user RSMSMONOPR cascade;
DROP user AGOPR cascade;
DROP user DEPLOYOP cascade;
DROP user OVSEE cascade;
DROP user DBQRY cascade;
DROP user DBCONN cascade;
DROP user DBMON cascade;
DROP user DBQUA cascade;		 yes 　 　
11 　 NEW 　 重建VIEW 19c的BUG 需要重建修复 1 新库执行: 重建DBA_JOBS VIEW
 CREATE OR REPLACE   VIEW "SYS"."DBA_JOBS" ("JOB", "LOG_USER", "PRIV_USER", "SCHEMA_USER", "LAST_DATE", "LAST_SEC", "THIS_DATE", "THIS_SEC",
"NEXT_DATE", "NEXT_SEC", "TOTAL_TIME", "BROKEN", "INTERVAL", "FAILURES", "WHAT"
, "NLS_ENV", "MISC_ENV", "INSTANCE") AS
select  m.dbms_job_number JOB, j.creator LOG_USER, u.name PRIV_USER,
u.name SCHEMA_USER, j.last_start_date LAST_DATE,
substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8) LAST_SEC,
DECODE(BITAND(j.job_status,2), 2, j.last_start_date, NULL) THIS_DATE,
DECODE(BITAND(j.job_status,2), 2,
substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8), NULL) THIS_SEC,
j.next_run_date NEXT_DATE,
substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_SEC,
(CASE WHEN j.last_end_date>j.last_start_date THEN
( CAST( j.last_end_date AS DATE ) - CAST( j.last_start_date AS DATE ) ) * 86400 ELSE 0 END)
TOTAL_TIME, -- Scheduler does not track total time
DECODE(BITAND(j.job_status,1),0,'Y','N') BROKEN,
DECODE(BITAND(j.flags,1024+4096+134217728),
0, j.schedule_expr, NULL) INTERVAL,
j.failure_count FAILURES, j.program_action WHAT,
j.nls_env NLS_ENV, j.env MISC_ENV, NVL(j.instance_id, 0) INSTANCE
from  sys.scheduler$_dbmsjob_map m
left outer join sys.obj$ o on (o.name = m.job_name)
left outer join sys.user$ u on (u.name = m.job_owner)
left outer join sys.scheduler$_job j on (j.obj# = o.obj#)
where o.owner# = u.user# ;
 yes 　 　
　 　 NEW 　 重建vm_concat 不涉及 CREATE OR REPLACE TYPE APPMGR."WM_CONCAT_IMPL"                                          AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/


CREATE OR REPLACE TYPE BODY APPMGR."WM_CONCAT_IMPL" IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
    RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
                                       P1   IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL,
                                     SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/


CREATE OR REPLACE FUNCTION APPMGR.WM_CONCAT(P1 VARCHAR2) RETURN clob AGGREGATE USING WM_CONCAT_IMPL;
/


create public SYNONYM WM_CONCAT for APPMGR.WM_CONCAT;

grant EXECUTE on APPMGR.WM_CONCAT_IMPL to PUBLIC;
grant EXECUTE on APPMGR.WM_CONCAT to PUBLIC;
 　 　 　
12 　 NEW 　 db_files设置高于8000 　 show parameter db_files yes 　 　 　 　
13 　 NEW 　 新库主库，同城，远程新建asm目录 (目录名要一致，不能用db_unique_name) ls +DATA_GBS_DG/GBS/backupondest
ls +DATA_GBS_DG/GBS/DATAFILE		 ls +DATA_<db_name>_DG/<db_name>/DATAFILE/backupondest
ls +DATA_<db_name>_DG/<db_name>/DATAFILE		 yes 　 　 　 　
14 　 NEW 　 全库备份X86 　 backup as compressed backupset database format '/paic/tmp/nasshare3/licw/rman/GBS_%U' include current controlfile plus archivelog; yes 　 　 　 　
15 　 NEW 　 Service创建 　 srvctl add service -d gbs -s srvp_gbs_egis_hsp  -r gbs1,gbs2,gbs3,gbs4  -P basic -e SELECT -m basic -z 200 -w 10 -l primary
srvctl start service -d gbs -s srvp_gbs_egis_hsp
 show parameter name
 show parameter db_domain		 yes 　 　
16 　 OLD/NEW 　 检查旧库和新库是否有app卷以及子目录goldengate是否有，如果没有需提request尽快创建 　 　 　 　 　
17 　 NEW 　 为新库（目标）安装配置好GG软件 　 　 　 　 　
18 　 OLD 　 原生产库启动XTTS专用监听 　 配置一个1521的静态监听；提前测试防火墙；
SID_LIST_xttsgbs=
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME =/paic/gbs/rdbms/oracle/product/12.1.0)
      (SID_NAME = gbs)
    )
  )

xttsgbs=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.31.44)(PORT = 1521))
    )
  )
lsnrctl start xttsgbs
lsnrctl status xttsgbs		 yes 　 　
19 　 NEW 　 在X86新库创建到Solaris主备库的dblink（4个） 　 GBS
create public database link DBLINK_MIGUP  connect to MIGUPS identified by  MKCI_eCI39_C
  using '(description =(address = (protocol = tcp)(host = 10.33.31.44)(port = 1526))(connect_data = (service_name = gbs)))';
 
---GLDG(GBS_TMP)
create public database link DBLINK_MIGUP_GL  connect to MIGUPS identified by MKCI_eCI39_C
  using '(description =(address = (protocol = tcp)(host = 30.88.5.1)(port = 1526))(connect_data = (service_name = gbs)))';

---GMDG(同城)
create public database link DBLINK_MIGUP_GM  connect to MIGUPS identified by MKCI_eCI39_C
  using '(description =(address = (protocol = tcp)(host = 30.88.2.77)(port = 1526))(connect_data = (service_name = gbs)))';

---WGQDG(远程)
create public database link DBLINK_MIGUP_WGQ  connect to MIGUPS identified by MKCI_eCI39_C
  using '(description =(address = (protocol = tcp)(host = 10.44.44.71)(port = 1526))(connect_data = (service_name = gbs)))';
检查:
select instance_name,host_name from v$instance@DBLINK_MIGUP;
select instance_name,host_name from v$instance@DBLINK_MIGUP_GL;
select instance_name,host_name from v$instance@DBLINK_MIGUP_GM;
select instance_name,host_name from v$instance@DBLINK_MIGUP_WGQ;		 yes 　 　
20 　 OLD 　 源端提前预扩表空间.  　 　 　 　 　
21 　 OLD 　 在源库创建Directory对象
指向各自的数据文件放置目录		 20个目录  select 'create or replace directory SRCDIR'||rownum||' as '''||filename||''';' from (select distinct  substr(replace(file_name, ' ', ''),
              0,instr(replace(file_name, ' ', ''), '/', -1, 1)-1 ) filename   from dba_data_files);
运行输出结果
select owner,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like 'SRCDIR%';		 yes 　 　
22 　 OLD 　 检查和记录失效对象，发给开发、运营确认处理 　 col owner for a20
col object_name for a50
col object_type for a20
set pagesize 10000
set linesize 200
select  owner,object_name,object_type   from dba_objects   where status='INVALID';     
select  owner,object_type,count(*) from dba_objects  where status='INVALID' group by owner,object_type; 		 48 确认失效对象没用清理掉。 　
23 　 OLD 　 检查外部表 删除外部表 外部表检查
set serveroutput on
declare
external boolean;
begin
external := sys.dbms_tdb.check_external;
end;
/		 The following external tables exist in the database:
FOGLIGHT.GGSERR_LOG, FOGLIGHT.QSI_ALERT_TAIL_1430600976683, FOGLIGHT.QSI_ALERT_TAIL_1433642334003, FOGLIGHT.QSI_ALERT_TAIL_1445334952031, FOGLIGHT.Q_ALERT_LOG_GBS,
FOGLIGHT.QSI_ALERT_TAIL_1449073723913, FOGLIGHT.OSFS
The following directories exist in the database:
SYS.GL_DIR, SYS.BDUMP_DIR, SYS.GJN0DUMP, SYS.DUMP, SYS.GQD0DUMP, SYS.GHF0DUMP, SYS.QUEST_SOO_ADUMP_DIR, SYS.QUEST_SOO_BDUMP_DIR, SYS.QUEST_SOO_CDUMP_DIR, SYS.QUEST_SOO_UDUMP_DIR, SYS.GWQ0DUMP
24 　 OLD 　 检查SYSTEM、SYSAUX对象情况) 　 SYS检查
select  owner, segment_name,segment_type,tablespace_name
  from dba_segments a
  where a.tablespace_name in ('TEMP', 'SYSAUX', 'SYSTEM')
  and a.owner  in   (select username from migups.s_user_list);		 yes 　 　
25 　 OLD 　 检查重名的数据文件
提前做MV与RENAME
（每日监控）		 　 1. 登录同城, 远程DG库.
运行以下SQL, 得到file.txt SPOOL文件
set linesize 1000
set pagesize 0
spool file.txt
select 'ls -l '||replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf'),
'mv '||replace(file_name,' ','\ ')||' '||replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf'),
'ALTER DATABASE RENAME FILE ''' || file_name ||'''  TO ''' || replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf') ||''';'
  FROM dba_data_files where file_name like '% %'
  union
select 'ls -l '||replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf'),
'mv '||replace(file_name,' ','\ ')||' '||replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf'),
'ALTER DATABASE RENAME FILE ''' || file_name ||'''  TO ''' || replace(replace(file_name,' ',''), '.dbf', '_'||file_id||'.dbf') ||''';'
  FROM dba_data_files
 WHERE upper(substr(replace(file_name, ' ', ''), instr(replace(file_name, ' ', ''), '/', -1, 1) + 1)) IN
       (select upper(substr(replace(file_name, ' ', ''), instr(replace(file_name, ' ', ''), '/', -1, 1) + 1)) filename
          from dba_data_files   group by upper(substr(replace(file_name, ' ', ''),instr(replace(file_name, ' ', ''), '/', -1, 1) + 1))
        having count(1) > 1) ;
spool off;
2. CAT SPOOL文件得到脚本;
cat file.txt |grep "ls -l /paic"  >ls.sh
cat file.txt |grep "mv /paic">mv.sh
cat file.txt |grep "ALTER DATABASE RENAME FILE '/paic">alter.sql
chmod +x ls.sh
chmod +x mv.sh
3. 运行ls.sh                      ./ls.sh       确保返回最后都是:  No such file or directory
4. 停止MRG                     recover managed standby database cancel;
5. 关闭数据库                  shutdown immediate;
运行mv.sh       ./mv.sh
6. 启动数据库到mount, 并更改数据文件名
startup mount;
alter system set standby_file_management=manual;
@alter
alter system set standby_file_management=auto;
alter database open read only;
recover managed standby database using current logfile disconnect from session;		 yes 　 　
26 　 OLD 　 检查迁移的数据库是否含有spatial index 　 select * from dba_indexes a
where upper(a.index_type)='DOMAIN' or a.ityp_name='SPATIAL_INDEX'
   and a.owner in  (select username from migups.s_user_list);		 yes 　 　
27 　 OLD 　 检查待迁移表中是否有索引组织表。 　    select owner,table_name from dba_tables ab where upper(ab.iot_type)  like '%IOT%'  and ab.owner  in (select username from migups.s_user_list); yse 　 　
28 　 OLD 　 检查是否有业务对象在不迁移的表空间上      select owner,segment_name,segment_type  from dba_segments a
  where a.tablespace_name not in (select tbsname from migups.s_tbs_list )
    and a.owner in (  select username from migups.s_user_list);		 yes 　 　
29 　 OLD 　 检查迁移对象是否是闭包的 　 1：检查迁移用户引用非迁移用户的对象，如果引用的是系统表酌情处理；如果引用的是非系统表则要整改。
col owner for a15
col referenced_owner for a15
col REFERENCED_NAME for a30
col REFERENCED_TYPE for a20
set pagesize 1000
set linesize 200
select owner,referenced_owner,count(*)  from dba_dependencies dd
 where dd.owner in (select username from migups.s_user_list)
   and dd.referenced_owner not in (select username from migups.s_user_list)  and dd.referenced_owner not in ('SYS','SYSTEM','WMSYS','XDB','APPQOSSYS','LBACSYS','AURORA$JIS$UTILITY$','TOAD','DBQUA')
   and dd.referenced_owner<>'PUBLIC' and dd.referenced_type<>'SYNONYM' group by owner,referenced_owner;
   ---用户EGISINTF不存在，无需处理
2：检查非迁移用户引用迁移用户的对象，如果存在则与开发运营讨论处理方法。
col owner for a15
col referenced_owner for a15
col REFERENCED_NAME for a30
col REFERENCED_TYPE for a20
set pagesize 1000
set linesize 200
    select owner,referenced_owner,count(*)   from dba_dependencies dd
   where dd.type <> 'SYNONYM'
     and dd.owner not in (select username from migups.s_user_list)
     and dd.referenced_owner in (select username from migups.s_user_list)
     and dd.referenced_owner<>'PUBLIC'
     group by owner,referenced_owner
     order by 3 desc;		 yes 　 　
30 　 OLD 　 表空间自包含检查 　 SET SERVEROUTPUT ON
declare
spaces_names varchar2(4000);
begin
for ts in (select tbsname  from migups.s_tbs_list) loop
  spaces_names :=spaces_names||','||ts.tbsname;
   end loop;
   dbms_output.put_line(substr(spaces_names,2));
   spaces_names :=substr(spaces_names,2);
   sys.dbms_tts.transport_set_check(spaces_names, true);
end;
/
select * from sys.transport_set_violations;		 　 　 　
31 　 OLD 　 DBA_LINK检查(确保连到旧库都要使用域名) 　 　 　 　 　
32 　 OLD 　 检查迁移schema的trigger是否是闭包的 　 1:检查触发器的owner<>table_owner且owner不在迁移列表而table_owner在迁移用户列表的情况，这种触发器需要手工迁移
select *  from dba_triggers  where owner <> table_owner
   and owner not in (select username from migups.s_user_list)
   and table_owner in (select username from migups.s_user_list);
2:检查触发器的owner<>table_owner且owner在迁移用户列表而table_owner不在迁移用户列表的情况，这种触发器视情况决定是否迁移
select OWNER,TRIGGER_NAME,TABLE_OWNER,TABLE_NAME,TRIGGER_TYPE
  from dba_triggers  where owner <> table_owner
   and owner in (select username from migups.s_user_list)
   and table_owner not in (select username from migups.s_user_list)
   and table_owner not like 'COW%'   and base_object_type<>'DATABASE';
3:检查触发器的triggering_event owner'LOGON '且owner在迁移用户列表或table_owner在迁移用户列表的情况，这种触发器expdp不能导出需要手工迁移；
select OWNER,TRIGGER_NAME,TABLE_OWNER,TABLE_NAME,TRIGGER_TYPE
  from dba_triggers
 where triggering_event = 'LOGON '
   and (owner in (select username from migups.s_user_list) or
       table_owner in (select username from migups.s_user_list))
       and table_owner not like 'COW%';		 　 　 　
33 　 OLD 　 检查旧库中是否有业务scheduler存在，这些scheduler也要进行迁移，如果存在有，本步要把迁移scheduler的脚本准备好 　 select owner,job_name  from dba_scheduler_jobs where owner not in ('SYS','DBMGR'); yes 　 　
34 　 OLD 　 检查生产升级时停库时刻是否有时间长的job正要跑
询问运营升级前是否有大批量变更		 　 col elapsed_min for 9999.9
col sid for 99999999999
col serial# for a20
col this_date for a30
col username for a20
select * from (select  round((sysdate - a.this_date) * 24 * 60, 2) elapsed_min,
       a.sid, b.serial#,a.job, a.this_date, b.username    
  From dba_jobs_running a, v$session b
 where a.sid = b.sid and a.this_date is not null   order by 1 desc ) where rownum<5;		 　 　 　
35 　 NEW 　 提request给服务交付组，提前预约请他收到邮件通知其立即将DNS切换到新VIP上，并清理缓存 　 　 　 　 　
36 　 OLD/NEW 　 提request给服务交付组，提前预约迁移升级过程后  对旧库冷备和新库rman full备份， 如果旧库是小机，还要预约冻结vcs资源。 　 　 　 　 　
37 　 OLD 　 旧库打FIX导出慢的PATCH 　 　 　 　 　
38 　 OLD 　 确保TIGGER都是有属主的 　 　 　 　 　
39 　 OLD/NEW 　 提前导入XTTS固化计划SPM 　 　 　 　 　
40 　 NEW 　 提前导入演练过程中固化的SPM 　 　 　 　 　
41 　 NEW 　 检查应用到新库的防火墙是否开通 　 　 　 　 　
42 　 OLD 　 检查观澜DG、光明DG、外高桥DG的BCT是否已经开启 alter database enable block change tracking using file '/paic/EGIS/EGIS/backup/EGIS_block_change_track.log'  ;  验证BCT是否开启：
select * from v$block_change_tracking;		 　 　 　
43 　 OLD/NEW 　 调整RMAN的默认并行度、调整ARCHIVELOG 清理策略 　 需要检查  DEVICE TYPE 是 DISK ， 备份集不能使用压缩模式(compressed) , 合理的并行度减少RMAN备份时间
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 16;		 　 　 　
44 　 OLD 　 【每天】冻结生产变更，检查是否有新增DB对象
【新增对象处理】-GG检查是否支持GG同步，同步方案确认，回滚方案确认
【新增对象处理】-GG补添加trandata
【新增对象处理】-GG同步参数文件修改（应该是写成动态脚本）
【新增对象处理】-是否支持XTTS（xTTS迁移前的检查再做一次）		 　 　 　 　 　
45 　 OLD 　 【每天】检查表空间容量、扩容添加文件按照预案处理
（传输数据文件开始后禁止新建表空间添加数据文件）		 　 　 　 　 　
46 　 OLD 　 检查DG环境数据库文件是否异常 　 以下SQL应返回0行:
set linesize 1000
col name for a20
col DNAME for a40
col fname for a30
col CHECKPOINT_CHANGE# for 999999999999999
col STATUS for a30
SELECT TS#, NAME, DF.DNAME DNAME, DF.FNAME, FILE#, CHECKPOINT_CHANGE#,dF.STATUS
  FROM (SELECT /*+
            LEADING(t.x$kccts)
            USE_HASH(d.df)
            FULL(t.x$kccts)
            FULL(d.df)
            USE_HASH(d.fe)
            USE_HASH(d.fn)
            USE_HASH(d.fh)
            LEADING(d.fe d.fn d.fh)
          */
     ROW_NUMBER() OVER(PARTITION BY D.TS# ORDER BY FILE#) RN,
     MIN(CASE
       WHEN (ENABLED = 'READ WRITE' OR 'Y' = 'Y') AND STATUS = 'ONLINE' THEN
        D.TS#
       ELSE
        -D.TS#
     END) OVER(PARTITION BY D.TS#) TS#,
     T.NAME,
     REGEXP_REPLACE(D.NAME, '(.*)/(.*)', '\1') DNAME,
     REGEXP_REPLACE(D.NAME, '(.*)/(.*)', '\2') FNAME,
     FILE#,
     MIN(CHECKPOINT_CHANGE#) OVER(PARTITION BY D.TS#) CHECKPOINT_CHANGE#,d.STATUS
      FROM GV$DATAFILE D, V$TABLESPACE T
     WHERE D.TS# = T.TS#
       AND D.INST_ID = USERENV('INSTANCE')
       ) DF
 WHERE ts#<0
 ORDER BY TS#, RN;		 yes 　 　
47 　 OLD/NEW 　 【每天】检查新旧生产、同城、远程DG同步正常 　 验证PROCES是否正常. : select process,status,sequence# from v$managed_standby;
验证间隔:  select  INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE#   ,DELAY_MINS from gv\$managed_standby where process like 'MRP%';		 　 　 　
48 　 OLD/NEW 　 【每天】XTTS增备前检查旧生产、同城、远程DG同步正常，lag及时处理  　 验证PROCES是否正常. : select process,status,sequence# from v$managed_standby;
验证间隔:  select  INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE#   ,DELAY_MINS from gv\$managed_standby where process like 'MRP%';		 　 　 　
49 　 OLD/NEW 　 【每天】3套环境XTTS增备正常、记录增备时长、异常及时升级 　 　 　 　 　
50 　 OLD/NEW 　 复核OGG迁移、回退相关的链路、脚本都正常运行 　 　 　 　 　
51 　 OLD 　 更改旧库隐含参数, 避免增量备份过慢 　 alter system set "_disable_primary_bitmap_switch"=true scope=both; 　 　 　
52 　 OLD 　 　 　 安排变更对生产进行一次全库坏块检查
@rman_validate.sh
3) select * from V$DATABASE_BLOCK_CORRUPTION ;
4) If V$DATABASE_BLOCK_CORRUPTION contains rows please run this query to find the objects that contains the corrupted blocks:
SELECT e.owner,
       e.segment_type,
       e.segment_name,
       e.partition_name,
       c.file#,
       greatest(e.block_id, c.block#) corr_start_block#,
       least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
       least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
       greatest(e.block_id, c.block#) + 1 blocks_corrupted,
       null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner,
       s.segment_type,
       s.segment_name,
       s.partition_name,
       c.file#,
       header_block corr_start_block#,
       header_block corr_end_block#,
       1 blocks_corrupted,
       'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner,
       null segment_type,
       null segment_name,
       null partition_name,
       c.file#,
       greatest(f.block_id, c.block#) corr_start_block#,
       least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
       least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
       greatest(f.block_id, c.block#) + 1 blocks_corrupted,
       'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
 order by file#, corr_start_block#;
SELECT tablespace_name, segment_type, owner, segment_name
  FROM dba_extents
 WHERE file_id = &fileid
   and &blockid between block_id AND block_id + blocks - 1;		 　 　 　
53 　 OLD 　 检查各表空间的使用率，及时添加数据文件 　 SET LINESIZE 200
SET PAGESIZE 100
COLUMN TOTAL_BYTES FORMAT 999,999,999,999
COLUMN USED FORMAT 999,999,999,999
COLUMN FREE FORMAT 999,999,999,999
SELECT A.TABLESPACE_NAME,ROUND(FILE_USAGE/1024/1024) TOTAL_M,ROUND(NVL(FILE_USAGE-FREESPACE,0)/1024/1024) USED_M,
ROUND(NVL(FREESPACE,0)/1024/1024) FREE_M,ROUND(NVL((FILE_USAGE-FREESPACE),0)/A.FILE_USAGE*100,2) USED_RATE,
ROUND(E.EXTENDABLE_SPACE/1024/1024) EXTENDABLE_M,ROUND(EXTENDABLE_SPACE/FILE_USAGE*100,2) EXTANDABLE_RATE,
ROUND((FREESPACE+EXTENDABLE_SPACE)/FILE_USAGE*100,2)   ALL_FREE_RATE
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) FREESPACE
      FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
      ) F,     (     SELECT  TABLESPACE_NAME,SUM(BYTES) FILE_USAGE
     FROM SYS.DBA_DATA_FILES  GROUP BY TABLESPACE_NAME
     ) A,     (SELECT TABLESPACE_NAME ,CASE WHEN (SUM(MAXBYTES)-SUM(BYTES))<0 THEN 0 ELSE (SUM(MAXBYTES)-SUM(BYTES)) END EXTENDABLE_SPACE
      FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE='YES' GROUP BY TABLESPACE_NAME) E
     WHERE E.TABLESPACE_NAME(+)=F.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=A.TABLESPACE_NAME
UNION ALL
SELECT NULL ,SUM(FILE_USAGE)/1024/1024 TOTAL_M,SUM(NVL(FILE_USAGE-FREESPACE,0))/1024/1024 USED_M,SUM(NVL(FREESPACE,0))/1024/1024 FREE_M,ROUND(SUM(NVL((FILE_USAGE-FREESPACE),0))/SUM(A.FILE_USAGE)*100,2) USED_PCT,NULL,NULL
,NULL   ALL_FREE_RATE
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) FREESPACE
      FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
      ) F,     (     SELECT  TABLESPACE_NAME,SUM(BYTES) FILE_USAGE
     FROM SYS.DBA_DATA_FILES  GROUP BY TABLESPACE_NAME
     ) A     WHERE F.TABLESPACE_NAME(+)=A.TABLESPACE_NAME  ORDER BY ALL_FREE_RATE;		 　 　 　
54 　 OLD/NEW 　 准备XTTS脚本 　 观澜, 光明: 解压到  /paic/tmp/nasshare3/licw/gbs
外高桥解压到  /paic/tmp/nasshare3/licw/gbs		 　 　 　
55 　 OLD 　 执行准备脚本, 生成DBMS_file_transfer传文件脚本
/bin/rm -rf /paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/FAILED		 　 cd /paic/tmp/nasshare3/licw/gbs
./s.sh   ./sgm.sh  ./swgq.sh
选择  xtts_prepare     3
cd   /paic/tmp/nasshare3/licw/gbs/source/xtts/xtts
vi prepare_transfer.sh
TMPDIR=/paic/tmp/nasshare3/licw/gbs/source/tmpdir
XTTDEBUG=1
export TMPDIR XTTDEBUG
cd  ./xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -S
nohup sh  prepare_transfer.sh >prepare_transfer.log 2>&1 &

在TMPDIR目录下产生两个后续要用到的文件: xttnewdatafiles.txt,getfile.sql
cp /paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/getfile.sql /paic/tmp/nasshare3/licw/gbs/target/tmpdir/getfile.sql
cp /paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/xttnewdatafiles.txt /paic/tmp/nasshare3/licw/gbs/target/tmpdir/xttnewdatafiles.txt 		 　 　 　
56 　 NEW 　 DBMS_file_transfer传文件
/bin/rm -rf /paic/tmp/nasshare3/licw/gbs/target/tmpdir/FAILED 		 　 cd /paic/tmp/nasshare3/licw/gbs
./t.sh   ./tgm.sh  ./twgq.sh
选择  xtts_getfiles       1

mkdir -p /paic/tmp/nasshare3/licw/gbs/target/tmpdir
cd /paic/tmp/nasshare3/licw/gbs/target/xtts
vi  do_getfile.sh
TMPDIR=/paic/tmp/nasshare3/licw/gbs/target/tmpdir
XTTDEBUG=1
export TMPDIR XTTDEBUG
cd ./xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -G

nohup sh  do_getfile.sh >do_getfile.log 2>&1 &		 　 　 　
57 　 NEW 　 检查传输进度情况 　 cd /paic/tmp/nasshare3/licw/gbs
./t.sh   ./tgm.sh  ./twgq.sh
选择  check_xtts_getfiles     2		 　 　 　
58 　 OLD 　 检查是否开启BCT
检查RMAN的并行度		 　 SQL> select * from v$block_change_tracking;
使用如下开始BCT （目录自行创建好）：
SQL> alter database  enable block change tracking using file ;
RMAN> show all;         （源端、目标端都要调整）
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 16;		 　 　 　
59 　 OLD 　 在源库执行增备 　 cd /paic/tmp/nasshare3/licw/gbs
./s.sh   ./sgm.sh  ./swgq.sh
选择 xtts_incr_backup      5
检查进度: check_xtts_incr_backup       6
确认增备完成后
选择 xtts_incr_update_from_scn 以更新下次增备的起点		 　 　 　
60 　 NEW 　 /bin/rm -rf /paic/tmp/nasshare3/licw/gbs/target/tmpdir/FAILED
X86目标库做应用增备恢复
/bin/rm -rf /paic/tmp/nasshare3/licw/gbs/source/xtts/incr/*
/bin/rm -rf /paic/tmp/nasshare3/licw/gbs/source/xtts/incr/*		 　 X86目标库做应用增备恢复
cp /paic/tmp/nasshare3/licw/gbs/source/xtts/tmpdir/tsbkupmap.txt /paic/tmp/nasshare3/licw/gbs/target/tmpdir/tsbkupmap.txt
cp tsbkupmap.txt /paic/tmp/nasshare3/licw/gbs/target/tmpdir
cp /paic/tmp/nasshare3/licw/gbs/source/xtts/incr/* /paic/tmp/nasshare3/licw/gbs/source/xtts/incr

cd   /paic/tmp/nasshare3/licw/gbs
./t.sh ./tgm.sh ./twgq.sh
选择:  apply_incr_backup      3
检查:  check_apply_incr_backup       4
cd /paic/tmp/nasshare3/licw/gbs/target/xtts
vi apply_incr.sh
TMPDIR=/paic/tmp/nasshare3/licw/gbs/target/tmpdir
XTTDEBUG=1
export TMPDIR  XTTDEBUG
cd  ./xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -r
nohup sh apply_incr.sh >apply_incr.log 2>&1 &		 apply_incr_backup 目标生产库无法执行
---无法挂载同一个nas卷		 　 　
oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论