当前DataGuard环境（一主一备）

 

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

 

 

当前数据库版本

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

 

         此次数据库升级，由11.2.0.3.0升级到11.2.0.4.0。

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> alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=orcl';

SQL> alter system set log_archive_dest_3='location=use_db_recovery_file_dest valid_for=(standby_logfiles,standby_role) db_unique_name=orcl';

 

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_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> alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=orclps';

 

 

SQL> alter system set log_archive_dest_3='location=use_db_recovery_file_dest valid_for=(standby_logfiles,standby_role) db_unique_name=orclps';

 

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_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_upgrade_1 guarantee flashback database;

 

----备库

SQL> create restore point pre_upgrade_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 session_id,state from v$logstdby_state;

SESSION_ID STATE

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

         1 INITIALIZING

SQL> select session_id,state from v$logstdby_state;

SESSION_ID STATE

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

         1 LOADING DICTIONARY

SQL> select session_id,state from v$logstdby_state;

SESSION_ID STATE

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

         1 APPLYING

SQL>  select session_id,state from v$logstdby_state;

SESSION_ID STATE

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

         1 IDLE

---- SQL> select sequence#, applied from v$archived_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_upgrade_3 guarantee flashback database;            ----升级之前再创建一个闪回点

 

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

SQL> shutdown immediate

[oracle@dg2 database]$ lsnrctl stop

[oracle@dg2 database]$ emctl stop dbconsole

 

[oracle@dg2 dbs]$ vim /home/oracle/.bash_profile

修改 export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

[oracle@dg2 dbs]$ . /home/oracle/.bash_profile

 

--------在执行DBUA迁移数据库时，报无法找到数据库启动路径，需要在

# vim /etc/oratab     添加启动。（注意看注释内容，添加的是SID）

orclps:/u01/app/oracle/product/11.2.0/db_1:Y

 

--------上传11.2.0.4安装包到备库，解压进行升级

--------安装11.2.0.4时选择upgrade，如果数据迁移失败，查找原因，然后手动执行dbua迁移，迁移时最好先不要覆盖原数据文件也就是选择move database，这样再升级后测试发现有问题，还可以启用旧库，但是路径变了的话，记得在参数文件中添加db_file_name_convert路径转换，主备库的数据文件路径不一致。我这里测试就选择覆盖了。EM升级会失败，因为是备库的，可以不管。

[oracle@dg2 database]$ ./runInstaller

 

----主库

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

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

SQL> create table test(test int);

SQL> insert into test 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 shall(shall int);

begin

for i in 1..100000 loop

insert into shall 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 shall values(i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

 

SQL> select count(*) from shall;

  COUNT(*)

----------

    101234

 

----备库升级的同时，主库可以安装新版本软件，选择全新安装，不要选择更新，也不要创建数据库

--------上传11.2.0.4安装包到主库，解压进行安装

--------安装11.2.0.4时选择 Install database software only

 

 

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

----备库升级完成后

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

 

------备库升级完成后还需要配置监听和tns，可以直接去原家目录拷贝

[oracle@dg2 ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin/

[oracle@dg2 admin]$ cp /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora .

[oracle@dg2 admin]$ cp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora .

[oracle@dg2 admin]$ lsnrctl start

 

----备库

SQL> alter database start logical standby apply immediate;

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

 

------- 查看日志应用情况

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;

         ----主库上查看应用情况  select sequence#, applied from v$archived_log;

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

 

----此时查看升级期间主库上的数据操作是否同步

SQL> select * from test;

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';

no rows selected

 

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

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 * from test;

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                          SHALL

 

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

  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_upgrade_1;

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

[oracle@orcl database]$ lsnrctl stop

[oracle@orcl database]$ emctl stop dbconsole

 

----原主库切换新家目录

[oracle@orcl ~]$ vim .bash_profile

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

[oracle@orcl ~]$ . .bash_profile

[oracle@orcl dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin/

[oracle@orcl admin]$ cp /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora .

[oracle@orcl admin]$ cp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora .

[oracle@orcl dbs]$ cd $ORACLE_HOME/dbs

[oracle@orcl dbs]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora .

[oracle@orcl dbs]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl .

[oracle@orcl ~]$ lsnrctl start

[oracle@orcl ~]$ emctl start dbconsole

 

------启动库到mount，退出当前窗口，重新登录一次，初始化环境变量

[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 25 19:48:01 2016

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

Connected to an idle instance.

SQL> startup mount

 

----原备库

------在原主库切换新家目录期间，在原备库做一些操作

SQL> show user

USER is "SYS"

SQL> create table zhong(x int);

SQL> conn shall/shall

SQL> select count(*) from shall;

  COUNT(*)

----------

    101234

SQL> insert into shall 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> 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;

 

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

SQL> select * from test;

select * from test

              *

ERROR at line 1:

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

 

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

  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_upgrade_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_upgrade_2;

SQL> drop restore point pre_upgrade_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   就说明日志没有应用

 

select status,error from v$archive_dest;

show parameter db_unique_name

show parameter log_archive_dest_2

 

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