【作者简介】
本人医疗行业DBA，精通oracle底层；会文件、触发器加密的勒索恢复、asm磁盘组不能mount及各种灾难恢复，后面将利用空闲时间将自己工作中遇到的问题及整理的资料分享给更多愿意学习和提升自己的dba。作者微信号：Oray_123
【正文】
一、方案编写
二、dg搭建
主库现有一个单机的备库，我们计划搭建第二个逻辑备库来进行升级，升级完成后处理后续的一系列操作，最后到停机时间直接进行失败切换，停掉原rac scan漂移ip，新主库挂上ip即可对外提供业务，整个事情就圆满完成。
搭建adg大致步骤：
1）主库修改：
alter system set log_archive_config='DG_CONFIG=(orcl,oral,orcldg1)';
alter system set log_archive_dest_3='service=tnsorcldg1 LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=orcldg1';
alter system set log_archive_dest_state_3='ENABLE';
alter system set fal_server='STANDBY','tnsorcldg1';
2）备1修改：
备库安装或者上传一个11204的安装目录，用11204的目录来搭建dg
3）其他和搭建dg一样，密码文件、静态监听..这里就不列出来了；
备库执行克隆动作：
rman target sys/his@ORCL1 auxiliary sys/his@tnsorcldg1
duplicate target database for standby from active database;
启动备1：
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
测试主库和备1数据同步：
a）主库上操作
create table cs0529 (i int);
/
begin
for i in 1..10000 loop
insert into cs0529 values (i);
commit;
dbms_lock.sleep(2);
end loop;
end;
/
alter system switch logfile;
archive log list;
b）备库上查看
select * from cs0529;
archive log list;
三、物理dg转逻辑dg
1）检备库不受支持的表
select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;
2）查看主库中不含主键和唯一索引的表
select owner,table_name,bad_column from dba_logstdby_not_unique
注意BAD_COLUMN列值，该列有两个值：
Y ：表示该表中有采用大数据类型的字段，比如LONG啦，CLOB啦之类。如果表中除log列某些行记录完全匹配，则该表无法成功应用于逻辑standby。
standby会尝试维护这些表，不过你必须保证应用不允许。
N ：表示该表拥有足够的信息，能够支持在逻辑standby的更新，不过仍然建议你为该表创建一个主键或者唯一索引/约束以提高log应用效率
假设在某张表中你可以确认数据是唯一的，但是基于效率方面的考虑，不想为其创建主键或唯一约束，怎么办呢？没关系，Oracle早想到了这一点，你可以创建一个DISABLE的Primary-Key Rely约束。
ALTER TABLE zlhis.医保调用日志 ADD PRIMARY KEY (序号,病人id) RELY DISABLE;
3）将物理备库转换为逻辑备库
备库操作：
alter database recover managed standby database cancel;
shutdown immediate;
startup nomount;
alter database mount standby database;
主库操作：
之后需要在Primary Database中创建Logminer dictionary日志挖掘字典：
SQL> drop user zldhc cascade;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
--如果一直卡住处理长事物
with transaction_details as
( select inst_id
, ses_addr
, sysdate - start_date as diff
from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
|| ' days, '
|| to_char(trunc(mod(t.diff * 24,24)))
|| ' hours, '
|| to_char(trunc(mod(t.diff * 24 * 60,24)))
|| ' minutes, '
|| to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
|| ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
,'ALTER SYSTEM KILL SESSION '||''''||s.SID||','||s.SERIAL#||',@'||s.inst_id||''''||' IMMEDIATE;'
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
备库操作：
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
SQL> alter database open;
我们还需要完成一系列逻辑备库的配置:
a.在逻辑备库中禁用自动删除外部存档日志
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'FALSE' ); #BUG 12353543 version 11.2
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
如果报错：
/* 缺少DBMS_LOGSTDBY包，可以从以下脚本创建 */
SQL> @?/rdbms/admin/dbmslsby.sql
b.执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS);
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');
c.启动在逻辑备库上的SQL APPLY:（因为windows打了补丁，所以不需要马上验证同步情况，因为linux没有windows那些补丁，开启下面的同步，数据也不会过来。会报找不到xx补丁的错误。所以我们直接跳过。开始升级，升级到12c后再打开同步即可。）----（不执行该步骤）
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
3）物理转换成逻辑dg后，表空间不能再使用db_file_name_convert了，需要单独的过程处理：
备库执行：
SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
--NEW_STMT := REPLACE(OLD_STMT, 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\','/oradata/orcldg/');
NEW_STMT := REPLACE(REPLACE(OLD_STMT,'+DATA/orcl/datafile/','/u01/app/oracle/oradata/ORCL/'),'+DATA/orcl/tempfile/','/u01/app/oracle/oradata/ORCL/');
--NEW_STMT := REPLACE(OLD_STMT, '/u01/app/oracle/oradata/orcl/','/oradata/orcldg/');
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL;
/
执行DBMS_LOGSTDBY.SKIP过程，将编写的过程注册到表空间处理的SQL应用中：
SQL> EXEC DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE',proc_name => 'sys.handle_tbs_ddl');
这里也要借助DBMS_LOGSTDBY.SKIP过程实现。该过程功能非常强大，而且操作非常灵活。
重启SQL应用：
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
四、逻辑dg升级操作
1、 主库停止传输
select name,thread#,sequence#,first_time,first_change#,next_time,next_change# from v$archived_log where dest_id=1 and name is not null order by sequence# asc;
通过查询看到主库归档保留了7天；
SQL> alter system set log_archive_dest_state_3 = DEFER scope=both;
2、 备库停止日志应用
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
3、 备库执行升级前工具（11204数据库打开的情况下操作）
[oracle@his1 arch]$ u02/app/oracle/product/11.2.0/db_1/jdk/bin/java -jar u01/app/oracle/product/19c/dbhome_1/rdbms/admin/preupgrade.jar
==================
PREUPGRADE SUMMARY
==================
u02/app/oracle/cfgtoollogs/orcldg/preupgrade/preupgrade.log
u02/app/oracle/cfgtoollogs/orcldg/preupgrade/preupgrade_fixups.sql
u02/app/oracle/cfgtoollogs/orcldg/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u02/app/oracle/cfgtoollogs/orcldg/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u02/app/oracle/cfgtoollogs/orcldg/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2023-07-13T23:25:14
4. 备库预升级检查
Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
19c ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
[oracle@new-his-01 db_1]$ cp u01/app/oracle/product/19c/dbhome_1/rdbms/admin/emremove.sql u02/app/oracle/product/11.2.0/db_1/rdbms/admin/
sqlplus as sysdba
SQL> alter database guard none; --不执行
SQL> alter session disable guard;
@/u02/app/oracle/product/11.2.0/db_1/rdbms/admin/emremove.sql
--11g home执行
@$ORACLE_HOME/olap/admin/catnoamd.sql
5、旧库执行
SQL> @/u02/app/oracle/cfgtoollogs/orcldg1/preupgrade/preupgrade_fixups.sql --直接执行
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 11
Generated on: 2022-06-14 09:49:52
For Source Database: ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. two_pc_txn_exist NO Manual fixup required.
2. invalid_usr_tabledata YES None.
3. invalid_sys_tabledata YES None.
4. parameter_obsolete NO Manual fixup recommended.
5. em_present YES None.
6. invalid_objects_exist NO Manual fixup recommended.
7. amd_exists YES None.
8. default_resource_limit NO Manual fixup recommended.
9. network_acl_priv NO Manual fixup recommended.
10. exclusive_mode_auth NO Manual fixup recommended.
11. case_insensitive_auth NO Manual fixup recommended.
12. streams_setup NO Manual fixup recommended.
13. mv_refresh NO Manual fixup recommended.
14. hidden_params NO Informational only.
Further action is optional.
15. dictionary_stats YES None.
16. trgowner_no_admndbtrg YES None.
17. component_info NO Informational only.
Further action is optional.
18. parameter_deprecated NO Informational only.
Further action is optional.
19. exf_rul_exists NO Informational only.
Further action is optional.
20. min_archive_dest_size NO Informational only.
Further action is optional.
21. rman_recovery_version NO Informational only.
Further action is optional.
22. invalid_all_obj_info NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
5、 备库关监听和库
SQL> shutdown immediate
[oracle@new-his-01 db_1]$ lsnrctl stop
6、 备库oracle用户切换环境变量到19c
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19c/dbhome_1
#export ORACLE_BASE=/u02/app/oracle;
#export ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1;
7、 dbca先把之前实例删除
[oracle@new-his-01 oradata]$ dbca -silent -deleteDatabase -sourceDB orcl -sysDBAUserName sys -sysDBAPassword his -forceArchiveLogDeletion
8、 备库rac1 在oracle_home/dbs下新建参数文件
[oracle@his1 ~]$ cd $ORACLE_HOME/dbs
[oracle@his1 dbs]# vi initorcl.ora
[oracle@his1 dbs]$ mkdir -p u01/app/oracle/admin/orcldg1/adump
[oracle@his1 dbs]$ mkdir -p u01/app/oracle/fast_recovery_area
9、 备库rac1启动到nomount并创建spfile到asm
SQL> create spfile from pfile;
SQL> startup mount;
10、 备库19c 密码文件处理
将/u02下的dbs/orapworcl 拷贝到 u01目录下
[oracle@his1dbs]$cp u02/app/oracle/product/11.2.0/db_1/dbs/orapworcl u01/app/oracle/product/19c/dbhome_1/dbs/
11、 备库his 添加tnsnames.ora sqlnet.ora处理
oracle用户添加：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.92.170.104)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oral)
)
)
PG=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.92.170.98)(PORT=1521))
(CONNECT_DATA=(SID=PG))
(HS=OK)
)
tnsorcldg1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wshis)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[oracle@his1 admin]$ cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
12、备库19c 添加静态监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = u01/app/oracle/product/19c/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wshis)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = u01/app/oracle
注意ORACLE_HOME路径为RDBMS的oraclehome路径
两个节点分别stop start监听
[grid@his2 admin]$ lsnrctl stop
[grid@his2 admin]$ lsnrctl start
13、 备库his升级到19c
SQL> SHUTDOWN IMMEDIATE
SQL> startup upgrade;
--19c oracle目录
[oracle@his1 dbs]$ cd $ORACLE_HOME/rdbms/admin
[oracle@his1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
+++++++升级脚本介绍
4 (默认) – 最大: 8个并行进程
中断可继续运行:
$ dbupgrade -R -l tmp/logs
封装脚本:
catctl.pl
$ perl catctl.pl -n 4 -l tmp/logs catupgrd.sql
+++++++
[oracle@his1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 8
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.19.0.0.0DBRU_LINUX.X64_230321.1]
/u01/app/oracle/product/19c/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19c/dbhome_1]
/u01/app/oracle/product/19c/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19c/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c/dbhome_1]
Analyzing file u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20230713234617]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20230713234617/catupgrd_catcon_92205.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20230713234617/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20230713234617/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 32
Database Name = orcldg
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618/catupgrd_catcon_92205.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618]
Parallel SQL Process Count = 8
Components in [orcldg]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2023_07_13 23:46:19]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcldg] Files:1 Time: 71s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcldg] Files:5 Time: 41s
Restart Phase #:2 [orcldg] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcldg] Files:19 Time: 21s
Restart Phase #:4 [orcldg] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcldg] Files:7 Time: 15s
***************** Catproc Start ****************
Serial Phase #:6 [orcldg] Files:1 Time: 9s
***************** Catproc Types ****************
Serial Phase #:7 [orcldg] Files:2 Time: 7s
Restart Phase #:8 [orcldg] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcldg] Files:71 Time: 12s
Restart Phase #:10 [orcldg] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [orcldg] Files:1 Time: 51s
Restart Phase #:12 [orcldg] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcldg] Files:95 Time: 2s
Restart Phase #:14 [orcldg] Files:1 Time: 0s
Parallel Phase #:15 [orcldg] Files:122 Time: 4s
Restart Phase #:16 [orcldg] Files:1 Time: 0s
Serial Phase #:17 [orcldg] Files:26 Time: 2s
Restart Phase #:18 [orcldg] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcldg] Files:32 Time: 6s
Restart Phase #:20 [orcldg] Files:1 Time: 1s
Serial Phase #:21 [orcldg] Files:3 Time: 8s
Restart Phase #:22 [orcldg] Files:1 Time: 0s
Parallel Phase #:23 [orcldg] Files:25 Time: 104s
Restart Phase #:24 [orcldg] Files:1 Time: 0s
Parallel Phase #:25 [orcldg] Files:12 Time: 65s
Restart Phase #:26 [orcldg] Files:1 Time: 1s
Serial Phase #:27 [orcldg] Files:1 Time: 0s
Serial Phase #:28 [orcldg] Files:7 Time: 3s
Serial Phase #:29 [orcldg] Files:1 Time: 0s
Restart Phase #:30 [orcldg] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcldg] Files:1 Time: 0s
Restart Phase #:32 [orcldg] Files:1 Time: 0s
Serial Phase #:34 [orcldg] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcldg] Files:297 Time: 20s
Serial Phase #:36 [orcldg] Files:1 Time: 0s
Restart Phase #:37 [orcldg] Files:1 Time: 0s
Serial Phase #:38 [orcldg] Files:10 Time: 4s
Restart Phase #:39 [orcldg] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcldg] Files:3 Time: 37s
Restart Phase #:41 [orcldg] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcldg] Files:13 Time: 63s
Restart Phase #:43 [orcldg] Files:1 Time: 1s
Parallel Phase #:44 [orcldg] Files:11 Time: 3s
Restart Phase #:45 [orcldg] Files:1 Time: 1s
Parallel Phase #:46 [orcldg] Files:3 Time: 0s
Restart Phase #:47 [orcldg] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [orcldg] Files:1 Time: 17s
Restart Phase #:49 [orcldg] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcldg] Files:1 Time: 23s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcldg] Files:1 Time: 0s
Restart Phase #:52 [orcldg] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcldg] Files:2 Time: 264s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcldg] Files:1 Time: 0s
Serial Phase #:56 [orcldg] Files:3 Time: 14s
Serial Phase #:57 [orcldg] Files:3 Time: 1s
Parallel Phase #:58 [orcldg] Files:10 Time: 1s
Parallel Phase #:59 [orcldg] Files:25 Time: 2s
Serial Phase #:60 [orcldg] Files:4 Time: 4s
Serial Phase #:61 [orcldg] Files:1 Time: 0s
Serial Phase #:62 [orcldg] Files:32 Time: 2s
Serial Phase #:63 [orcldg] Files:1 Time: 0s
Parallel Phase #:64 [orcldg] Files:6 Time: 5s
Serial Phase #:65 [orcldg] Files:2 Time: 12s
Serial Phase #:66 [orcldg] Files:3 Time: 49s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcldg] Files:1 Time: 0s
Serial Phase #:69 [orcldg] Files:1 Time: 1s
Parallel Phase #:70 [orcldg] Files:2 Time: 21s
Restart Phase #:71 [orcldg] Files:1 Time: 0s
Parallel Phase #:72 [orcldg] Files:2 Time: 0s
Serial Phase #:73 [orcldg] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcldg] Files:1 Time: 1s
Serial Phase #:76 [orcldg] Files:1 Time: 19s
Serial Phase #:77 [orcldg] Files:2 Time: 1s
Restart Phase #:78 [orcldg] Files:1 Time: 0s
Serial Phase #:79 [orcldg] Files:1 Time: 10s
Restart Phase #:80 [orcldg] Files:1 Time: 0s
Parallel Phase #:81 [orcldg] Files:3 Time: 13s
Restart Phase #:82 [orcldg] Files:1 Time: 0s
Serial Phase #:83 [orcldg] Files:1 Time: 3s
Restart Phase #:84 [orcldg] Files:1 Time: 0s
Serial Phase #:85 [orcldg] Files:1 Time: 5s
Restart Phase #:86 [orcldg] Files:1 Time: 0s
Parallel Phase #:87 [orcldg] Files:4 Time: 37s
Restart Phase #:88 [orcldg] Files:1 Time: 0s
Serial Phase #:89 [orcldg] Files:1 Time: 0s
Restart Phase #:90 [orcldg] Files:1 Time: 0s
Serial Phase #:91 [orcldg] Files:2 Time: 5s
Restart Phase #:92 [orcldg] Files:1 Time: 0s
Serial Phase #:93 [orcldg] Files:1 Time: 0s
Restart Phase #:94 [orcldg] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcldg] Files:1 Time: 11s
Restart Phase #:96 [orcldg] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [orcldg] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcldg] Files:1 Time: 72s
******************* Migration ******************
Serial Phase #:99 [orcldg] Files:1 Time: 30s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcldg] Files:1 Time: 0s
Serial Phase #:101 [orcldg] Files:1 Time: 0s
Serial Phase #:102 [orcldg] Files:1 Time: 41s
***************** Post Upgrade *****************
Serial Phase #:103 [orcldg] Files:1 Time: 35s
**************** Summary report ****************
Serial Phase #:104 [orcldg] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcldg] Files:1 Time: 0s
Serial Phase #:106 [orcldg] Files:1 Time: 0s
Serial Phase #:107 [orcldg] Files:1 Time: 27s
------------------------------------------------------
Phases [0-107] End Time:[2023_07_14 00:07:43]
------------------------------------------------------
Grand Total Time: 1285s
LOG FILES: (/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19c/dbhome_1/cfgtoollogs/orcldg/upgrade20230713234618/upg_summary.log
Grand Total Upgrade Time: [0d:0h:21m:25s]
14、 升级后处理
set line 200
col PROPERTY_NAME format a30
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
select * from v$timezone_file;
--升级后脚本执行
SQL> @/u02/app/oracle/cfgtoollogs/orcldg1/preupgrade/postupgrade_fixups.sql
SQL> @?/rdbms/admin/utlrp.sql
升级后查询确认：
select * from DBA_REGISTRY_HISTORY;
select comp_name,status,version from dba_server_registry;
select comp_id,version,status from dba_registry;
select * from v$version;
show parameter compatible
升级后的检查：
select * from utl_recomp_errors;
五、逻辑dg升级后操作
1、统计信息收集
exec dbms_stats.set_param('DEGREE','8');
select dbms_stats.get_param('DEGREE') from dual;
exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_system_stats(gathering_mode => 'noworkload');
exec dbms_stats.gather_fixed_objects_stats;
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYS')
exec dbms_stats.set_param('DEGREE','0');
业务表的统计信息收集方式就不发出来了；
2、wm_concat函数增加
alter session disable guard;
alter user wmsys account unlock;
alter user wmsys account unlock identified by wmsys;
grant connect to wmsys;
grant dba to wmsys;
登陆wmsys执行如下语句：
alter session disable guard;
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
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 VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/
--定义类型body:
CREATE OR REPLACE TYPE BODY 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 VARCHAR2,
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 wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/
--三.创建同义词并授权
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
3、业务相关授权
4、业务相关表处理
5、重新开启同步
在逻辑备库上启用SQL APPLY（rac1开启即可）
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
主库开启日志传输
SQL> alter system set log_archive_dest_state_3 = ENABLE scope=both;
如果备库应用日志慢，可以考虑合理调整logical standby上apply进程的数量
三个参数设置推荐如下等式关系：
APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3
比如，设置max_servers在1~20之间，则分配一个进程作为PREPARER，其余进程作为applers；如果max_servers值在21~40之间，则分配2个进程作为PREPARER，其余进程作为applers。
SELECT * FROM V$LOGSTDBY_STATS WHERE NAME in('number of preparers','number of appliers','maximum SGA for LCR cache (MB)');
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> begin DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 24);end;
SQL> begin DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 1);end;
SQL> begin DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);end;
begin DBMS_LOGSTDBY.APPLY_SET('MAX_SGA',1024); end; --100
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
6、检查同步进度
set line 200 pages 2222
col file_name for a50
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,NEXT_CHANGE# AS N_SCN#, TIMESTAMP, DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR#, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
检查实时应用的语句
select ls.serial# "Apply Process"
, sas.state "State"
, sas.sid SID
, s.sql_address "SQL Address"
, s.sql_hash_value "SQL Hash Value"
,s.SQL_ID
, sa.sql_text "SQL Text"
from v$logstdby ls
, v$streams_apply_server sas
, v$session s
, v$sqlarea sa
where ls.type = 'APPLIER'
and sas.state != 'IDLE'
and sas.serial# = ls.serial#
and s.sid = sas.sid
and sa.address (+) = s.sql_address
and sa.hash_value (+) = s.sql_hash_value;
检查目前应用到哪个时段
SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
检查备库进程相关状态
select * from V$LOGSTDBY_STATS;
SELECT SID,SERIAL#,SPID,TYPE,STATUS,HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;
7、检查和处理未应用或者报错的表（备库执行）
SELECT XIDUSN, XIDSLT, XIDSQN , status , EVENT_TIME,to_char(EVENT), STATUS FROM DBA_LOGSTDBY_EVENTS
where EVENT not like '%SYSMAN%' and EVENT not like '%SYSLS%' and to_char(EVENT) is not null ORDER BY EVENT_TIMESTAMP;
SELECT distinct trunc(event_time),to_char(EVENT) FROM DBA_LOGSTDBY_EVENTS where EVENT not like '%SYSMAN%' and EVENT not like '%SYSLS%' and to_char(EVENT) is not null;
select STATEMENT_OPT,OWNER,NAME from DBA_LOGSTDBY_SKIP;
--跳过表的应用
alter database stop logical standby apply;
begin DBMS_LOGSTDBY.SKIP('DML', 'SYS', 'JOB$'); end;
begin DBMS_LOGSTDBY.SKIP('DML', 'ZLHIS', '体检任务结果'); end;
begin DBMS_LOGSTDBY.SKIP('DML', 'SYS', 'aud$'); end;
select STATEMENT_OPT,OWNER,NAME from DBA_LOGSTDBY_SKIP;
alter database start logical standby apply immediate;
--跳过某个事物
alter database stop logical standby apply;
begin dbms_logstdby.skip_transaction (19,10,306300); end;
alter database start logical standby apply immediate;
--查看跳过的表是否有其他表引用，并且是级联删除或者级联更新,如果没有则直接可以导入，如果有要先禁用级联更新、删除再导入
--a) 从DBA_LOGSTDBY_SKIP获取
select pk.OWNER,pk.table_name,fk_col.constraint_name, fk_col.table_name, fk_col.column_name,fk.DELETE_RULE
from dba_constraints pk, dba_constraints fk,dba_cons_columns fk_col
where pk.table_name in (select NAME from DBA_LOGSTDBY_SKIP)
and pk.constraint_type = 'P'
and fk.r_constraint_name = pk.constraint_name
and fk_col.constraint_name = fk.constraint_name
order by 2, 3;
--b) 从DBA_LOGSTDBY_EVENTS获取
select pk.OWNER,pk.table_name,fk_col.constraint_name, fk_col.table_name, fk_col.column_name,fk.DELETE_RULE
from dba_constraints pk, dba_constraints fk,dba_cons_columns fk_col
where pk.table_name in ( 'TEST_GETDATA_XML','ZLMOBILEMSLOG','病人医嘱执行','病区公告栏样式','ZLMBDEVICELIST','ZLMBIF_CALL_LOG')
and pk.constraint_type = 'P'
and fk.r_constraint_name = pk.constraint_name
and fk_col.constraint_name = fk.constraint_name
order by 2, 3;
--重新初始化被跳过的表
select STATEMENT_OPT,OWNER,NAME from DBA_LOGSTDBY_SKIP;
OWNER NAME
ZLHIS YGJC_上传记录
ZLHIS 体检任务结果
ZLHIS 医保调用日志
ZLHIS 检验复查明细
SYS AUD$
drop public database link TO_PRIMARY;
create public database link TO_PRIMARY connect to zlhis identified by "!744B018D9" using '10.92.170.101/orcl';
alter database stop logical standby apply;
begin dbms_logstdby.unskip('SCHEMA_DDL', 'ZLHIS','检验复查明细');end;
begin dbms_logstdby.unskip('DML', 'ZLGS','YGJC_上传记录');end;
begin dbms_logstdby.instantiate_table('ZLGS','YGJC_上传记录','TO_PRIMARY'); end;
alter database start logical standby apply immediate;
alter table ZLHIS.体检任务结果 add constraint 体检任务结果_FK_清单id foreign key(任务id,病人id,清单id)
references zlhis.体检任务发送(任务id,病人id,清单id) on delete cascade enable novalidate;
8、his业务系统报表修改、相关处理
六、停机窗口
1、rac添加白名单，只允许rac和dg和调试的客户端连接，其他都不允许进入
su - grid
vi /g01/app/11.2.0/grid/network/admin/sqlnet.ora
tcp.validnode_checking=yes
tcp.invited_nodes=(10.92.170.102,10.92.170.103,10.92.170.104,10.92.170.105,10.0.0.2,10.0.0.3,10.92.170.101,192.168.35.7,10.92.170.164,127.0.0.1,192.168.1.9)
ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|wc -l
ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs kill -9
rac重启监听
srvctl stop listener
srvctl start listener
rac停止scan和scan_listener
/g01/app/11.2.0/grid/bin/srvctl stop scan_listener
/g01/app/11.2.0/grid/bin/srvctl stop scan
2、数据检查
3、如有大对象之前跳过了，这里批量并行分片导出
//////////////////11g导出
create or replace directory dmp as '/backup/';
grant read,write on directory dmp to public;
#!/bin/bash
chunk=9
for ((i=0;i<=8;i++));
do
expdp zlhis/his TABLES=电子病历格式 QUERY=电子病历格式:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, ${chunk}\) = ${i}\" directory=DMP dumpfile=BL_${i}.DMP logfile=BL_${i}.log &
echo $i
sleep 10
done
sh expbl.sh &
scp /backup/BL* 172.16.106.188:/backup/
//////////////////19c导出
create or replace directory dmp as '/backup/';
grant read,write on directory dmp to public;
truncate table zlhis.电子病历格式; --19c库执行
--脚本导入
#!/bin/bash
for ((i=0;i<=8;i++));
do
#echo dumpfile=BL_${i}.DMP
impdp \"/ as sysdba\" directory=dmp dumpfile=BL_${i}.DMP logfile=impdp_BL_${i}.log DATA_OPTIONS=DISABLE_APPEND_HINT TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y CONTENT=DATA_ONLY &
sleep 10
done
4、主库摘掉第二个dg
--主库先执行
alter system set log_archive_config='DG_CONFIG=(orcl,oral)';
alter system set log_archive_dest_3='';
alter system reset log_archive_dest_3 scope=spfile;
5、备库清理spfile参数
--备库(先备份现有spfile，然后再删除dg参数)
create pfile from spfile;
alter system set db_unique_name='orcl' scope=spfile;
alter system set log_archive_config='';
alter system reset log_archive_config scope=spfile;
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ORCLARCH/' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system reset log_archive_dest_2 scope=spfile;
alter system set log_archive_dest_3='' scope=both;
alter system reset log_archive_dest_3 scope=spfile;
alter system reset standby_file_management scope=spfile;
alter system reset db_file_name_convert scope=spfile;
alter system reset log_file_name_convert scope=spfile;
alter system set fal_client='' scope=both;
alter system reset fal_client scope=spfile;
alter system set fal_server='' scope=both;
alter system reset fal_server scope=spfile;
shutdown immediate;
startup;
6、备库执行激活
alter database stop logical standby apply;
alter database activate logical standby database ;
select db_unique_name,open_mode,switchover_status,dataguard_broker,guard_status,database_role,force_logging,
log_mode,protection_mode,protection_level,name,controlfile_type,fs_failover_status from v$database;
7、新主库修改ip
vi /etc/sysconfig/network-scripts/ifcfg-bond1
nmcli con up bound
8、业务恢复
9、配置oracle pg的dblink
七、总结
1）本次项目预计30分钟停机，实际上我只用了7分钟，信息科都感到意外。还没开始就结束了；
2）由于涉及到部分用户信息，有部分内容未完全发出来，微信公众号格式不太好调整，大家将就看；有疑问欢迎加我微信沟通；
3）该方法适用于相同字节序可以做dg环境的升级、迁移、打补丁操作，如linux到linux，windows到linux；理论上支持dg的都可以；
