签到成功

知道了

CNDBA社区CNDBA社区

Oracle 12c DG physical standby转换成logical standby

2017-08-24 16:42 2191 0 原创 Oracle 12c Data Guard
作者： leo
本文链接：http://www.cndba.cn/cndba/leo1990/article/2167
1 使用以下语句查询，确保物理备份与主数据库同步 
[[email protected] ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 24 14:48:27 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied" 
FROM  
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY  2    3   THREAD#)) ARCH,  
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL  
WHERE  
ARCH.THREAD# = APPL.THREAD#  
ORDER BY 1;  4    5    6    7  

    Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
	 1			2		      2

Last Sequence Received和Last Sequence Applied ，不应该有任何差异。
2 停止在物理备用数据库上应用 
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.
--查看数据库状态
SQL> set line 300
SQL> SELECT SWITCHOVER_STATUS,
       DATABASE_ROLE,
       OPEN_MODE,
       PROTECTION_MODE,
       PROTECTION_LEVEL
  FROM V$DATABASE;  2    3    4    5    6  

SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE 	   PROTECTION_MODE	PROTECTION_LEVEL
-------------------- ---------------- -------------------- -------------------- --------------------
NOT ALLOWED	     PHYSICAL STANDBY READ ONLY 	   MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE
3在主服务器上设置逻辑备用参数 
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 24 14:47:50 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
--查看数据库状态
SQL> set line 300
SQL> SELECT SWITCHOVER_STATUS,
       DATABASE_ROLE,
       OPEN_MODE,
       PROTECTION_MODE,
       PROTECTION_LEVEL
  FROM V$DATABASE;  2    3    4    5    6  

SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE 	   PROTECTION_MODE	PROTECTION_LEVEL
-------------------- ---------------- -------------------- -------------------- --------------------
TO STANDBY	     PRIMARY	      READ WRITE	   MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE
--另外之前的LOG_ARCHIVE_DEST_1是valid for (ALL_LOGFILES,ALL_ROLES)， 需要修改为(ONLINE_LOGFILES,ALL_ROLES)
SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(ONLINE_LOGFILES,all_roles) db_unique_name=pdbcndba_p' scope=both;

System altered.
--LOG_ARCHIVE_DEST_2 不做修改这里是确保和备库的参数保持一致
SQL> alter system set log_archive_dest_2='service=pdbcndba_s lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s' scope=both; 

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pdbcndba_p' scope=both;

System altered.

注意:LOG_ARCHIVE_DEST_3只在主数据库转换成逻辑备用角色时生效
4 在主数据库的重做数据中建立字典  
DBMS_LOGSTDBY.BUILD存储过程等待所有现有事务完成。在主数据库上执行的长时间运行的事务将影响此命令的时效性。
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.
5 将物理备库转换到逻辑备用数据库  
对于db_name，指定一个数据库名称来标识新的逻辑备用数据库。如果您正在使用一个备用的spfile，那么命令将更新db_name参数，否则它会发出一个消息，提醒您在关闭数据库后设置db_name参数的名称。
因为要做滚动升级，所以用原来的db_name,详细信息可参考
http://docs.oracle.com/database/121/SBYDB/rollup.htm#SBYDB4884

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.
6 将逻辑备库启动到mount 状态 
SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1560281088 bytes
Fixed Size		    2924784 bytes
Variable Size		  939527952 bytes
Database Buffers	  603979776 bytes
Redo Buffers		   13848576 bytes
Database mounted.
7 修改逻辑备库参数 
--另外之前的LOG_ARCHIVE_DEST_1是valid for (ALL_LOGFILES,ALL_ROLES)， 需要修改为(ONLINE_LOGFILES,ALL_ROLES)
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdbcndba_s' scope=both;  

System altered.
----LOG_ARCHIVE_DEST_2 不做修改这里是确保和主库的参数保持一致
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=pdbcndba_p lgwr affirm sync VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdbcndba_p' scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pdbcndba_s' scope=both; 

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;  

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; 

System altered.
8 打开逻辑备库 
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> ALTER DATABASE OPEN;          

Database altered.
9 启动逻辑应用于待机状态 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
*
ERROR at line 1:
ORA-16239: IMMEDIATE option not available without standby redo logs

--这里要创建创建Standby Redologs
SQL> alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo05.log'  size 200M;


Database altered.

SQL> alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo06.log'  size 200M;

Database altered.

SQL> alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo07.log'  size 200M;

Database altered.

SQL> alter  database add  standby  logfile '/u01/app/oracle/oradata/cndba/stdredo08.log'  size 200M;

Database altered.

a.禁止在逻辑备库端删除外籍日志 

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');

PL/SQL procedure successfully completed.

b.执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED', DBMS_LOGSTDBY.MAX_EVENTS);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');

PL/SQL procedure successfully completed.

c.启动在逻辑备库上的SQL APPLY:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

SQL> set lines 300
SQL> SELECT SWITCHOVER_STATUS,
       DATABASE_ROLE,
       OPEN_MODE,
       PROTECTION_MODE,
       PROTECTION_LEVEL
  FROM V$DATABASE;  2    3    4    5    6  

SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE 	   PROTECTION_MODE	PROTECTION_LEVEL
-------------------- ---------------- -------------------- -------------------- --------------------
NOT ALLOWED	     LOGICAL STANDBY  READ WRITE	   MAXIMUM PERFORMANCE	MAXIMUM PERFORMANCE
验证数据 
1） 主库创建表
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 24 14:47:50 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn [email protected]
Connected.
SQL> create table test(i number(10));

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;

System altered.
SQL> alter system switch logfile;

System altered.
2）备库查询
[[email protected] ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 24 16:31:25 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn [email protected]
Connected.

SQL> select * from test;

	 I
----------
	 1
创建逻辑备库详细文档参考：
http://www.cndba.cn/cndba/leo1990/article/2167 http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167 http://www.cndba.cn/cndba/leo1990/article/2167 http://www.cndba.cn/cndba/leo1990/article/2167
http://www.cndba.cn/cndba/leo1990/article/2167

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：765189次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ