备库failover升级

1.centos 6.9 single06 --> centos7.9 single06std

11.2.0.4

搭建上面的dg

2.adg上打补丁psu：31537677

3.centos 7.9 上安装19c软件，并打补丁33515361

4.备库上创建保证还原点

[oracle@single01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 27 17:56:56 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create restore point before_upgrade guarantee flashback database;
create restore point before_upgrade guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_UPGRADE'.
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point before_upgrade guarantee flashback database;
create restore point before_upgrade guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_UPGRADE'.
ORA-38786: Recovery area is not enabled.


SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/recovery';
alter system set db_recovery_file_dest='/u01/app/oracle/recovery'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_recovery_file_dest destination string cannot be
translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory


SQL> alter system set db_recovery_file_dest='/u01/app/oracle/recovery';

System altered.

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
SQL> col time for a35
SQL> set linesize 200
SQL>  select scn, guarantee_flashback_database, storage_size, time, name from v$restore_point;

       SCN GUA STORAGE_SIZE TIME                                NAME
---------- --- ------------ ----------------------------------- -----------------
   1054616 YES     52428800 27-OCT-22 08.48.32.000000000 PM     BEFORE_UPGRADE

5.备库做failover，备库变主库

SQL> --停止日志应用
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active


SQL> --关闭standby日志传输
alter database recover managed standby database finish force;

Database altered.

SQL> --备库通过failover切换为主库（破坏了主备关系，dg要重做）
alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> --检查数据库状态
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;

NAME       OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- -------------------- ---------------- --------------------
SINGLE06   MOUNTED              MAXIMUM PERFORMANCE  PRIMARY          NOT ALLOWED

SQL> --重启数据库到open状态
alter database open;

Database altered.

SQL> set linesize 200
SQL> --检查数据库状态
select name,open_mode,protection_mode,database_role,switchover_status from v$database;

NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
SINGLE06  READ WRITE           MAXIMUM PERFORMANCE  PRIMARY          FAILED DESTINATION

6.failover后的主库做dbua升级

SQL> --检查无效对象和组件
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2022-10-27 18:20:03

SQL> --时区应小于或等于目标数据库时区版本，19C为 32
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14

SQL> --升级之前，请确保对源数据库进行有效备份。
SQL> --禁用将在DDL语句之前/之后执行的所有自定义触发器。 升级后重新启用。
SQL> --升级数据库之前，请检查数据库服务器升级/降级兼容性列表。
SQL> --开启日志归档功能。
SQL> --清空回收站
SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

--检查用户当前不区分大小写的密码版本。
SQL> alter system set "_optimizer_cartesian_enabled"=TRUE;

System altered.

SQL> alter system set sga_max_size=4g scope=spfile;

System altered.

SQL>
SQL> alter system set sga_target=2g scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=1g;

dbua升级时候注意事项：

1.archivelog和flashback

Cause : DB_RECOVERY_FILE_DEST_SIZE is set at 1024 MB. There is currently 924 MB of free space remaining, which may not be adequate for the upgrade. 

Currently: Fast recovery area : /u01/app/oracle/recovery

Limit : 1024 MB

Used : 100 MB

Available : 924 MB

Action : Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 3165 MB. Check alert log during the upgrade to ensure there is remaining free space available in the recovery area.

alter system set db_recovery_file_dest_size=4g;

选择升级选项时都没选

 /u01/app/oracle/cfgtoollogs/dbua/upgrade2022-10-27_09-24-22PM/single06

[oracle@single01 single06]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    10-27-2022 23:02:0
Database Name: SINGLE06

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED     19.14.0.0.0  00:22:12
JServer JAVA Virtual Machine           UPGRADED     19.14.0.0.0  00:04:14
Oracle XDK                             UPGRADED     19.14.0.0.0  00:01:34
Oracle Database Java Packages          UPGRADED     19.14.0.0.0  00:00:17
OLAP Analytic Workspace                UPGRADED     19.14.0.0.0  00:00:56
OLAP Catalog                         OPTION OFF      11.2.0.4.0  00:00:00
Oracle Text                            UPGRADED     19.14.0.0.0  00:01:32
Oracle Workspace Manager               UPGRADED     19.14.0.0.0  00:01:23
Oracle Real Application Clusters     OPTION OFF     19.14.0.0.0  00:00:00
Oracle XML Database                    UPGRADED     19.14.0.0.0  00:04:57
Oracle Multimedia                      UPGRADED     19.14.0.0.0  00:03:25
Spatial                                UPGRADED     19.14.0.0.0  00:14:33
Oracle OLAP API                        UPGRADED     19.14.0.0.0  00:00:44
Datapatch                                                        00:13:34
Final Actions                                                    00:16:07
Post Upgrade                                                     00:02:22

Total Upgrade Time: 01:21:01

Database time zone version is 14. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:1h:28m:2s]

遇到的问题：

source database显示不出目标数据库：

vi /etc/oratab

添加如下：single06:/u01/app/oracle/product/19c/db_1:N

7. 用还原点还原数据库

在闪回时，必须在19C的ORACLE_HOME下完成闪回操作，并关闭数据库。
source 19cenv
SQL> startup mount;
SQL> flashback database to restore point before_upgrade;
在旧 ORACLE_HOME(11G)下先mount再alter database open resetlogs。
source 11gcenv
SQL> startup mount;
SQL> alter database open resetlogs;
如果打开失败，则用alter database open resetlogs upgrade;

--检查数据库状态，并通知应用连接测试。
SQL> select name,open_mode from v$database;