当前DataGuard环境（一主一备）

 

reference                   http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

 

 

当前数据库版本

SQL> select action,comments from registry$history;

ACTION               COMMENTS

-------------------- ------------------------------

APPLY                Patchset 11.2.0.2.0

APPLY                Patchset 11.2.0.2.0

VIEW INVALIDATE      view invalidation

UPGRADE              Upgraded from 11.2.0.3.0

APPLY                Patchset 11.2.0.2.0

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

 

         此次数据库升级，由11.2.0.4.0升级到11.2.0.4.8

1.dataguard滚动升级是先升级备库，然后将备库切换为主库。

2.原主库升级完成后，再次切换回主库。

3.备库需要为逻辑备库，而当前备库为物理备库，所以先转为逻辑备，升级完成后再转回物理备库。

4.原主库升级之前，需要闪回到备库升级之前的时间戳。

1. 升级之前环境准备

SQL> select protection_mode, protection_level from v$database;

----滚动升级要求dataguard保护模式为最高可用或最大性能，不能是最大保护

1.1 主库环境

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

recovery_parallelism                 integer     0

SQL> show parameter recover

NAME                        TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest       string /u01/app/oracle/fast_recovery_area

 

SQL> show parameter db_uni

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      orcl

 

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1                   string      location=use_db_recovery_file_

                                                 dest valid_for=(online_logfile

                                                 s,all_roles) db_unique_name=or

                                                 cl

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service=orclps sync affirm net

                                                 _timeout=10 valid_for=(online_

                                                 logfile,primary_role) db_uniqu

                                                 e_name=orclps

SQL> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_3                   string      location=use_db_recovery_file_

                                                 dest valid_for=(standby_logfil

                                                 es,standby_role) db_unique_nam

                                                 e=orcl

 

SQL> show parameter log_archive_dest_state_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2             string      enable

log_archive_dest_state_1             string      enable

log_archive_dest_state_3             string      enable

 

SQL> show parameter fal

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      ORCL

fal_server                           string      ORCLPS

 

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      dg_config=(orcl,orclps)

 

SQL> show parameter standby_file_man

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

1.2 备库环境

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     9

Next log sequence to archive   0

Current log sequence           10

 

SQL> show parameter db_uniq

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      orclps

 

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1                   string      location=use_db_recovery_file_

                                                 dest valid_for=(online_logfile

                                                 s,all_roles) db_unique_name=or

                                                 clps

 

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service=orcl sync affirm net_t

                                                 imeout=10 valid_for=(online_lo

                                                 gfile,primary_role) db_unique_

                                                 name=orcl

 

SQL> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_3                   string      location=use_db_recovery_file_

                                                 dest valid_for=(standby_logfil

                                                 es,standby_role) db_unique_nam

                                                 e=orclps

 

SQL> show parameter log_archive_dest_state_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2             string      enable

log_archive_dest_state_1             string      enable

log_archive_dest_state_3             string      enable

 

SQL> show parameter fal

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      ORCLPS

fal_server                           string      ORCL

 

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      dg_config=(orcl,orclps)

 

SQL> show parameter standby_file_man

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

2. 物理备库转为逻辑备库

2.1 开启flashback

----为了防止在升级过程中升级失败后，快速还原，先开启flashback

 

----主库开启flashback

SQL> shutdown immediate

 

----备库开启flashback

SQL> recover managed standby database cancel;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database flashback on;

 

----主库开启flashback

SQL> startup mount

SQL> alter database flashback on;

SQL> alter database open;

SQL> select name,log_mode,flashback_on from v$database;

SQL> select sequence#, applied from v$archived_log;  

2.2 创建闪回点

----主库

SQL> create restore point pre_psu_1 guarantee flashback database;

 

----备库

SQL> create restore point pre_psu_2 guarantee flashback database;

SQL> recover managed standby database using current logfile disconnect;

SQL> select sequence#, applied from v$archived_log;  

2.3 物理备库转为逻辑备库

----备库

SQL> alter database recover managed standby database cancel;

 

----主库

SQL> execute dbms_logstdby.build;       

--------生成LogMiner字典信息。该过程专门用于生成记录的元数据信息到redo log，这样改动才会被传输到逻辑standby，然后才会被逻辑standby 进行SQL 应用

 

----备库

SQL> shutdown immediate

SQL> startup mount

SQL> alter database recover to logical standby keep identity;

--------逻辑standby是一个全新的数据库，如果当前使用spfile,则数据库会自动修改其中的相关信息，如果使用的是pfile,在下次执行shutdown的时候oracle会提示你去修改db_name初始化参数的值

 

SQL> shutdown immediate

SQL> startup    ----alter database open resetlogs;

SQL> alter database start logical standby apply immediate;     ----立即开始应用逻辑备库

-------- alter database stop logical standby apply immediate;     ----停止应用逻辑备库

 

SQL> execute dbms_logstdby.apply_set('log_auto_delete','false');

-------- 逻辑备库会自动删除已经应用的日志，这里把自动删除应用归档关闭

 

SQL> select session_id,state from v$logstdby_state;   -------Wait for this query to display “IDLE”:

SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log; ------- 查看日志应用情况

SQL> select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

 

 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> alter database stop logical standby apply;

SQL>  select session_id,state from v$logstdby_state;                   ----日志应用状态

SESSION_ID STATE

---------- ----------------------------------------------------------------

         1 SQL APPLY NOT ON

SQL> alter database start logical standby apply immediate;

SQL>  select session_id,state from v$logstdby_state;

SESSION_ID STATE

---------- ----------------------------------------------------------------

         1 IDLE

 

----不支持逻辑同步的用户

select owner,name from DBA_LOGSTDBY_SKIP;

----检查逻辑备库不支持的表

select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;

----精确查表表的哪一列

select owner,table_name,column_name,data_type from dba_logstdby_unsupported where table_name='';

 

----在oracle11g中，逻辑备用数据库本身不支持一下的数据类型：

     BFILE  VARRAYS  ROWID  嵌套表  UROWID  存储为对象关系的XML类型

    用户定义类型   二进制XML

    多媒体数据类型（例如空间数据、图像数据、oracle文本）

 

----查看主数据库中不含有主键或唯一键索引的表：

select owner,table_name,bad_column from dba_logstdby_not_unique where table_name not in (select table_name from dba_logstdby_unsupported) and bad_column='Y';

 

SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;                                  -------查看日志应用情况

SQL> select session_id,state from v$logstdby_state;                                  --------查看日志应用状态

SQL> select event_time,status,event from dba_logstdby_events order by event_timestamp;                    --------查看报错。说明：如果status报错，event列有详细的触发操作语句，常见的多数是主库有新的DDL语句

SQL> select THREAD#,SEQUENCE#,USED,ARCHIVED,STATUS,FIRST_TIME,LAST_TIME from v$standby_log;                        --------查看逻辑standbylog

SQL> select * from v$archive_gap;                   -------查看是否有没有应用的日志

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;                          --------查看各进程状态

 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3. 升级备库

----主库

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      service=orclps sync affirm net

                                                 _timeout=10 valid_for=(online_

                                                 logfile,primary_role) db_uniqu

                                                 e_name=orclps

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

 

----备库

SQL> alter database stop logical standby apply;

SQL> create restore point pre_psu_3 guarantee flashback database;           ----升级之前再创建一个闪回点

 

------关闭备库db组件

SQL> shutdown immediate

[oracle@dg2 database]$ lsnrctl stop

[oracle@dg2 database]$ emctl stop dbconsole

 

 

3.1上传11.2.0.4.8补丁包到备库，解压

[oracle@dg2 install]$ unzip p21352635_112040_Linux-x86-64\(11.2.0.4.8_db\).zip

3.2 查看补丁升级官方文档说明

[oracle@dg2 21352635]$ firefox README.html

仔细阅读文档，了解升级前的注意事项。其中文档中要求OPatch版本在11.2.0.3.6以上应用这个补丁

You must use the OPatch utility version 11.2.0.3.6 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.

For information about OPatch documentation, including any known issues, see My Oracle Support Document 293369.1 OPatch documentation list.

3.3 检查OPatach版本，并升级OPatch

[oracle@dg2 install]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch version

Invoking OPatch 11.2.0.1.7

OPatch Version: 11.2.0.1.7

OPatch succeeded.

----版本过低，需要先升级OPatch版本，按照文档要求，官方找到p6880880包下载

 

----下载后，上传并解压

[oracle@dg2 install]$ unzip p6880880_112000_Linux-x86-64.zip

[oracle@dg2 OPatch]$ pwd

/install/OPatch

[oracle@dg2 OPatch]$ more README.txt     ------查看说明文档

How to install the utility:

---------------------------

To install this patch, Please extract the file "zipped file" using unzip or winzip,

depending upon the platform. You should extract the zip file directly under the

ORACLE_HOME. Please follow the following steps for extracting the zip file of OPatch.

(1) Please take a backup of $ORACLE_HOME/OPatch into a dedicated backup

location.

(2) Please remove the contents of $ORACLE_HOME/OPatch directory (Please do not

remove $ORACLE_HOME/OPatch directory itself)

(3) Please unzip the OPatch downloaded zip into $ORACLE_HOME directory.

To check the version of the opatch utility installed in the above step,

go to the $ORACLE_HOME/OPatch directory and run "opatch version".

----文档中说，可以先将$ORACLE_HOME/OPatch下的文件拷贝到其他路径备份，然后将下载的新版本覆盖该目录

[oracle@dg2 install]$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME

Archive:  p6880880_112000_Linux-x86-64.zip

replace /u01/app/oracle/product/11.2.0.4/db_1/OPatch/jlib/oracle.opatch.classpath.jar? [y]es, [n]o, [A]ll, [N]one, [r]ename: A

 

[oracle@dg2 install]$  $ORACLE_HOME/OPatch/opatch version

OPatch Version: 11.2.0.3.6

OPatch succeeded.

 

3.4 开始升级

----检测PSU补丁是否与当前环境冲突

[oracle@dg2 install]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

----在补丁目录下应用补丁

[oracle@dg2 21352635]$  $ORACLE_HOME/OPatch/opatch apply   

 

----应用补丁成功之后，执行catbundle.sql将补丁信息反射到数据库中，使数据库知道你最后一次打了什么补丁或者回滚了哪些补丁：

SQL> startup

SQL> @?/rdbms/admin/catbundle.sql psu apply

 

----完成后，启动监听、EM

SQL> ho lsnrctl start

SQL> ho emctl start dbconsole

 

SQL> select action,comments from registry$history;

ACTION     COMMENTS

---------- ------------------------------

APPLY      Patchset 11.2.0.2.0

APPLY      Patchset 11.2.0.2.0

VIEW INVAL view invalidation

IDATE

UPGRADE    Upgraded from 11.2.0.3.0

APPLY      Patchset 11.2.0.2.0

APPLY      PSU 11.2.0.4.8

6 rows selected.

 

SQL> select * from dba_registry_history;

$ $ORACLE_HOME/OPatch/opatch lspatches

21352635;Database Patch Set Update : 11.2.0.4.8 (21352635)

$ cd /u01/app/oracle/product/11.2.0/db_1/OPatch/

$ ./opatch lsinventory                         

 

----主库

------备库升级期间，在主库做一些操作

----sys用户下创建表，sys下的数据是不会同步到逻辑备库的，通过DBA_LOGSTDBY_SKIP视图可以看到跳过同步的用户

SQL> create table test1(test int);

SQL> insert into test1 values(11);

SQL> commit ;

 

----shall用户的数据

SQL> create user shall identified by shall;

SQL> grant connect,resource to shall;

SQL> conn shall/shall

 

SQL> create table shallz(shall int);

begin

for i in 1..100000 loop

insert into shallz values(i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

 

SQL> conn / as sysdba

SQL> alter system switch logfile;

SQL> conn shall/shall

 

begin

for i in 1..1234 loop

insert into shallz values(i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

 

SQL> select count(*) from shallz;

  COUNT(*)

----------

    101234

 

4. 备库升级完成后，主备切换一次

----备库

SQL> alter database start logical standby apply immediate;

------ alter database stop logical standby apply;

 

----主库开启日住传输

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

SQL> select sequence#, applied from v$archived_log;

------等待应用完成

 

----备库再次查看数据

SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;

SQL> select session_id,state from v$logstdby_state;   -------Wait for this query to display “IDLE”:

 

SQL> select * from test1;

select * from test

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> select owner,table_name from dba_tables where owner='SHALL';

OWNER                          TABLE_NAME

------------------------------ ------------------------------

SHALL                          SHALLZ

 

SQL> select count(*) from shall.shallz;

  COUNT(*)

----------

    101234

 

----主/备库切换

----主库

SQL> select sequence#, applied from v$archived_log;

SQL> select status,gap_status from v$archive_dest_status where dest_id in (2);

STATUS    GAP_STATUS

--------- ------------------------

VALID     NO GAP

SQL> select database_role,switchover_status,db_unique_name  from v$database;      -----等待 to standby

SQL> alter database commit to switchover to logical standby;    ----to standby后  切换

 

----备库

SQL> select database_role,switchover_status,db_unique_name  from v$database;       ----等待TO PRIMARY

SQL> alter database commit to switchover to logical primary;

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

 

5. 升级原主库

----原主库（先闪回到备库升级之前）

SQL> shutdown immediate;

SQL> startup mount

SQL> flashback database to restore point pre_psu_1;

SQL> shutdown immediate;   ---关闭旧的数据库组件

[oracle@orcl database]$ lsnrctl stop

[oracle@orcl database]$ emctl stop dbconsole

 

----整个升级过程和备库升级一致

<略>

----应用补丁成功之后，不必再执行catbundle.sql，将数据库启动到mount即可

 

----原备库

------在原主库升级期间，在原备库做一些操作

SQL> show user

USER is "SYS"

SQL> create table zhong(x int);

SQL> conn shall/shall

SQL> select count(*) from shallz;

  COUNT(*)

----------

    101234

SQL> insert into shallz values(1);

SQL> commit;

6. 再次切换主/备库

----原主库转为物理备

SQL> alter database convert to physical standby;

SQL> shut immediate

SQL> startup mount

SQL> recover managed standby database using current logfile disconnect;

 

----原备库

SQL> alter system set log_archive_dest_state_2=enable;

 

SQL> select sequence#, applied from v$archived_log;

SQL> select status,gap_status from v$archive_dest_status where dest_id in (2);

SQL> select protection_mode,protection_level from v$database;    ---- RESOLVABLE GAP 看原主库告警日志看日志应用情况

 

----原主库

SQL> select sequence#, applied from v$archived_log; ----查看日志应用情况

 

 

---主/备库再次切换

----原备库

SQL> select database_role,switchover_status,db_unique_name  from v$database;  ----当日志传送完必后就可用做切换工作。to standby；此时原主库还未应用完日志

SQL> alter database commit to switchover to standby with session shutdown;

SQL> shutdown immediate

 

----原主库

------Wait for this query to display “TO PRIMARY” or proceedto switchover with session disconnect:

SQL> select database_role,switchover_status,db_unique_name  from v$database;

SQL> select sequence#, applied from v$archived_log;           ----等待应用完毕

 

SQL> alter database commit to switchover to primary;

SQL> alter database open;

 

SQL> select action,comments from registry$history;

ACTION               COMMENTS

-------------------- ------------------------------

APPLY                Patchset 11.2.0.2.0

APPLY                Patchset 11.2.0.2.0

VIEW INVALIDATE      view invalidation

UPGRADE              Upgraded from 11.2.0.3.0

APPLY                Patchset 11.2.0.2.0

APPLY                PSU 11.2.0.4.8

6 rows selected.

 

----备库

SQL> startup

SQL> recover managed standby database using current logfile disconnect;

SQL> select database_role,switchover_status,db_unique_name  from v$database;

 

----主库

SQL> alter system switch logfile;

 

----备库

SQL> select sequence#,applied from v$archived_log;   ----物理备

SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;    ----逻辑备

 

7. 查看升级期间数据，及扫尾工作

SQL> select * from test1;

select * from test1

              *

ERROR at line 1:

ORA-00942: table or view does not exist         ----该表是在备库升级期间主库sys用户创建的表，因为备库为逻辑备库，跳过sys用户应用，所以这部分丢失

 

SQL> select count(*) from shall.shallz;

  COUNT(*)

----------

    101235    ----shall用户下的表在逻辑备库升级期间为101234条数据，在主库转为物理备库，逻辑备库转为主库后，添加了一条数据，这里没有问题

 

SQL> select * from zhong; 

no rows selected   ----逻辑备库转为主库后，在sys用户下创建的表，同步到物理备库，这里没有问题

 

 

----最后扫尾工作，删除创建的快照还原点

----主库

SQL> select name from v$restore_point where guarantee_flashback_database='YES';

SQL> drop restore point pre_psu_1;

----备库

SQL> recover managed standby database cancel;

SQL> shut immediate

SQL> startup mount

SQL> select name from v$restore_point where guarantee_flashback_database='YES';

SQL> drop restore point pre_psu_2;

SQL> drop restore point pre_psu_3;

 

SQL> alter database open;

SQL> recover managed standby database using current logfile disconnect;

SQL> select sequence#, applied from v$archived_log;            ----最后检查数据应用情况

 

----最后检查失效对象，并重新编译

 

 

=========================================

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

备库中

alter database recover managed standby database disconnect from session; ------开启MRP日志恢复进程

select process,status,sequence# from v$managed_standby; --备库日志     ---如果没有MRP0    WAIT_FOR_LOG   就说明日志没有应用

 ----另外整个升级期间，一定要保证db_recovery_file_dest_size的大小