【DG】之创建Logical Standby Database

最新推荐文章于 2019-09-15 16:02:00 发布
最新推荐文章于 2019-09-15 16:02:00 发布
阅读量142 收藏
点赞数
文章标签： 数据库 操作系统 网络

1     环境说明

两台数据库，实例名分别为BEIJING和SHANGHAI，配置为Logical Standby Database。并进行主备切换测试。两台数据库结构相同也可以不同。

 

说明:主备数据库是靠控制文件来识别，因此用主库创建备库的控制文件是必须要做的工作。同时也说明无论是物理还是逻辑备库，都是基于主库而生成的。并且在创建逻辑备库之前，备库首先是工作在物理备库状态下的，可以从alert中体现出来。

2     设置Primary Database

2.1    启用Forced Logging

检查是否启动Forced Logging

SYS@BEIJING> col FORCE_LOGGING for a15

SYS@BEIJING> select FORCE_LOGGING from v$database;

FORCE_LOGGING

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

NO

启用Forced Logging

SYS@BEIJING> alter database force logging;

Database altered.

2.2    检查密码文件

[oracle@secdb1 dbs]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/

hc_BEIJING.dat  init.ora      lkPROD     spfilePROD.ora

initdw.ora   initPROD.ora  orapwPROD  sqlnet.log

保证所有节点的sys密码必须一致

2.3    配置Standby Redo Log

查看现有日日志组

SYS@BEIJING> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                             IS_

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

         1         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo01.log      NO

         2         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo02.log      NO

         3         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo03.log      NO

         1         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo01_02.log   NO

         2         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo02_02.log   NO

         3         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo03_02.log   NO

         4         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo04_01.log   NO

         4         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo04_02.log   NO

         5         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo05_01.log   NO

         5         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo05_02.log   NO

 

10 rows selected.

增加standby redo log日志组，组数量等于online redo log group +1

SYS@BEIJING> alter database add standby logfile group 6

('/u01/app/oracle/oradata/BEIJING/disk1/stredo06.log') size 100m;

Database altered.

 

SYS@BEIJING> alter database add standby logfile group 7

('/u01/app/oracle/oradata/BEIJING/disk1/stredo07.log') size 100m;

Database altered.

 

SYS@BEIJING> alter database add standby logfile group 8

('/u01/app/oracle/oradata/BEIJING/disk1/stredo08.log') size 100m;

Database altered.

 

SYS@BEIJING> alter database add standby logfile group 9

('/u01/app/oracle/oradata/BEIJING/disk1/stredo09.log') size 100m;

Database altered.

 

SYS@BEIJING> alter database add standby logfile group 10

('/u01/app/oracle/oradata/BEIJING/disk1/stredo10.log') size 100m;

Database altered.

 

SYS@BEIJING> alter database add standby logfile group 11

('/u01/app/oracle/oradata/BEIJING/disk1/stredo11.log') size 100m;

Database altered.

检查配置结果

SYS@BEIJING> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                             IS_

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

         1         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo01.log      NO

         2         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo02.log      NO

         3         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo03.log      NO

         1         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo01_02.log   NO

         2         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo02_02.log   NO

         3         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo03_02.log   NO

         4         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo04_01.log   NO

         4         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo04_02.log   NO

         5         ONLINE  /u01/app/oracle/oradata/BEIJING/disk1/redo05_01.log   NO

         5         ONLINE  /u01/app/oracle/oradata/BEIJING/disk2/redo05_02.log   NO

         6         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo06.log    NO

         7         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo07.log    NO

         8         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo08.log    NO

         9         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo09.log    NO

        10         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo10.log    NO

        11         STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo11.log    NO

 

16 rows selected.

2.4    配置参数文件

生成pfile

SYS@BEIJING> create pfile from spfile;

File created.

在参数文件中增加如下部分

DB_NAME=BEIJING

DB_UNIQUE_NAME=BEIJING

LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/app/oracle/arch1/BEIJING/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_2=

'SERVICE=SHANGHAI LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_3=

'LOCATION=/u01/app/oracle/arch2/BEIJING/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=SHANGHAI

FAL_CLIENT=BEIJING

DB_FILE_NAME_CONVERT='SHANGHAI','BEIJING'

LOG_FILE_NAME_CONVERT='SHANGHAI','BEIJING'

STANDBY_FILE_MANAGEMENT=AUTO

当结构不同时

DB_UNIQUE_NAME=BEIJING

LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/app/oracle/arch1/BEIJING/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_2=

'SERVICE=SHANGHAI LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_3=

'LOCATION=/u01/app/oracle/arch2/BEIJING/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=SHANGHAI

FAL_CLIENT=BEIJING

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk1/'

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk2/'

STANDBY_FILE_MANAGEMENT=AUTO

 

注：LOCATION=不要写成LOCATION =，否则会报如下错误：

ORA-16024: parameter LOG_ARCHIVE_DEST_3 cannot be parsed

LOG_FILE_NAME_CONVERT如果写错，将导致standby log file在备库无法自动生成，需要手工新建。

 

 

关闭数据库以创建spfile

SYS@BEIJING> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@BEIJING> create spfile from pfile;

File created.

2.5    启用归档

SYS@BEIJING> startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              75498896 bytes

Database Buffers          234881024 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@BEIJING> alter database archivelog;

 

Database altered.

 

SYS@BEIJING> alter database open;

 

Database altered.

 

SYS@BEIJING> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/arch2/BEIJING

Oldest online log sequence     2

Next log sequence to archive   6

Current log sequence            6

2.6    检查主库中没有唯一标示的表

SYS@BEIJING> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

WHERE (OWNER, TABLE_NAME) NOT IN

(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)

AND BAD_COLUMN = 'Y';

 

OWNER                          TABLE_NAME

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

TSMSYS                         SRS$

3     设置Standby Database

3.1    创建Primary Database的数据文件备份

备份中不要包括日志文件

SYS@BEIJING> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

备份数据文件：

[oracle@secdb1 oradata]$ tar -zcvf BEIJING.tar *

3.2    创建Standby Database的控制文件

SYS@BEIJING> startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              75498896 bytes

Database Buffers          234881024 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@BEIJING> alter database create standby controlfile as '/home/oracle/controlstb.ctl';

 

Database altered.

 

SYS@BEIJING> alter database open;

 

Database altered.

3.3    创建Standby Database的参数文件

SYS@BEIJING> create pfile from spfile;

 

File created.

编辑参数文件

DB_NAME=BEIJING

DB_UNIQUE_NAME=SHANGHAI

LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/app/oracle/arch1/SHANGHAI/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_2=

'SERVICE=BEIJING LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_3=

'LOCATION=/u01/app/oracle/arch2/SHANGHAI/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=BEIJING

FAL_CLIENT=SHANGHAI

DB_FILE_NAME_CONVERT='BEIJING','SHANGHAI'

LOG_FILE_NAME_CONVERT='BEIJING','SHANGHAI'

STANDBY_FILE_MANAGEMENT=AUTO

或者当结构不同时

DB_NAME=BEIJING

DB_UNIQUE_NAME=SHANGHAI

LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/app/oracle/oradata/arch1/SHANGHAI/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_2=

'SERVICE=BEIJING LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=BEIJING'

LOG_ARCHIVE_DEST_3=

'LOCATION=/u01/app/oracle/oradata/arch2/SHANGHAI/

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=SHANGHAI'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=BEIJING

FAL_CLIENT=SHANGHAI

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/'

LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk2/','/u01/app/oracle/oradata/SHANGHAI/'

STANDBY_FILE_MANAGEMENT=AUTO

3.4    拷贝数据库文件，控制文件，参数文件到Standby Database

 

[oracle@secdb1 ~]$ scp initPROD.ora BEIJING.tar tnsnames.ora listener.ora control01.ctl oracle@secdb2:/home/oracle

The authenticity of host 'secdb2 (192.168.80.102)' can't be established.

RSA key fingerprint is 90:77:13:80:91:dd:8c:42:c0:24:ee:f7:06:36:1c:7f.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'secdb2,192.168.80.102' (RSA) to the list of known hosts.

oracle@secdb2's password:

initPROD.ora                                        100% 1765     1.7KB/s   00:00   

BEIJING.tar                                            100%  156MB   4.2MB/s   00:37   

tnsnames.ora                                        100%  593     0.6KB/s   00:00   

listener.ora                                        100%  569     0.6KB/s   00:00   

control01.ctl                                       100% 6896KB   3.4MB/s   00:02   

[oracle@secdb2 oradata]$ tar -zxvf BEIJING.tar

[oracle@secdb2 disk2]$ mv ../disk1/control01.ctl .

 [oracle@secdb2 ~]$ mv initPROD.ora /u01/app/oracle/PRODuct/10.2.0/db_1/dbs/initSHANGHAI.ora

Standby Database中不存在的目录要提前创建，包括oradata，admin，flash_recovery_area，arch

 

注意：不用拷贝redo log文件，

否则会报错

SQL> alter database recover to logical standby SHANGHAI;

alter database recover to logical standby SHANGHAI

*

ERROR at line 1:

ORA-00344: unable to re-create online log

'/u01/app/oracle/oradata/BEIJING/disk1/redo01.log'

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

3.5    创建密码文件

[oracle@secdb2 dbs]$ orapwd file=orapwSHANGHAI password=oracle entries=30

保证密码和主库一致

3.6    编辑监听文件

[oracle@secdb2 ~]$ mv listener.ora /u01/app/oracle/PRODuct/10.2.0/db_1/network/admin

[oracle@secdb2 ~]$ mv tnsnames.ora /u01/app/oracle/PRODuct/10.2.0/db_1/network/admin

[oracle@secdb2 ~]$ cd /u01/app/oracle/PRODuct/10.2.0/db_1/network/admin/

[oracle@secdb2 admin]$ vi listener.ora

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=SHANGHAI)

      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME=SHANGHAI))

    (SID_DESC=

      (SID_NAME=plsextproc)

      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM=extproc)))

 

[oracle@secdb2 admin]$ vi tnsnames.ora

BEIJING=

 (DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))

   (CONNECT_DATA=

      (SERVER=dedicated)  

      (SERVICE_NAME=BEIJING)))

SHANGHAI=

 (DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))

   (CONNECT_DATA=

      (SERVER=dedicated)  

      (SERVICE_NAME=SHANGHAI)))

BEIJING_S=

 (DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1526)))

   (CONNECT_DATA=

      (SERVER=shared)  

      (SERVICE_NAME=BEIJING)))

3.7    启动监听器

[oracle@secdb2 admin]$ lsnrctl start

 

LSNRCTL for Linux: Version 10.2.0.1.0 - production on 19-JAN-2013 21:40:16

 

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

 

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 10.2.0.1.0 - production

System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - production

Start Date                19-JAN-2013 21:40:17

Uptime                    0 days 0 hr. 0 min. 1 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Services Summary...

Service "SHANGHAI" has 1 instance(s).

  Instance "SHANGHAI", status UNKNOWN, has 1 handler(s) for this service...

Service "plsextproc" has 1 instance(s).

  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

3.8    启动Standby Database到mount阶段

[oracle@secdb2 admin]$ sqlplus sys/oracle@SHANGHAI as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - production on Sat Jan 19 21:58:27 2013

 

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

 

Connected to an idle instance.

 

SQL> create spfile from pfile;

 

File created.

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

3.9    在主库中创建Redo Data 数据字典

SYS@BEIJING> exec dbms_logstdby.build;

 

PL/SQL procedure successfully completed.

3.10  转换为Logical Standby Database

SYS@SHANGHAI> alter database recover to logical standby SHANGHAI;

 

Database altered.

SYS@SHANGHAI> shutdown immediate

此操作会修改db_name参数的名字。

 

注:请在转换之前，检查standby log file文件的路径是否已经转换成备库的路径，否则转换后将导致standby log file丢失。

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                             IS_

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

         1         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo01.log            NO

         2         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo02.log            NO

         3         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo03.log            NO

         1 INVALID ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo01_02.log         NO

         2 INVALID ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo02_02.log         NO

         3 INVALID ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo03_02.log         NO

         4         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo04_01.log         NO

         4         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo04_02.log         NO

         5         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo05_01.log         NO

         5         ONLINE  /u01/app/oracle/oradata/SHANGHAI/redo05_02.log         NO

         6         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo06.log       NO

         7         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo07.log       NO

         8         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo08.log       NO

         9         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo09.log       NO

        10         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo10.log       NO

        11         STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo11.log       NO

3.11  打开Logical Standby Database

ORACLE instance shut down.

 

SYS@SHANGHAI> startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@SHANGHAI> alter database open resetlogs;

 

Database altered.

 

SYS@SHANGHAI> alter database start logical standby apply immediate;

 

Database altered.

 

SYS@SHANGHAI>

3.12  测试Standby Database的归档操作

在主库中切换日志组

SYS@BEIJING> alter system switch logfile;

 

System altered.

 

在备库中查看归档日志是否正常

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

 

 SEQUENCE# FIRST_TIM NEXT_TIME

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

         4 17-JAN-13 19-JAN-13

         5 19-JAN-13 19-JAN-13

         6 19-JAN-13 19-JAN-13

         7 19-JAN-13 19-JAN-13

         8 19-JAN-13 19-JAN-13

         9 19-JAN-13 19-JAN-13

        10 19-JAN-13 19-JAN-13

        11 19-JAN-13 19-JAN-13

 

8 rows selected.

在备库中查看归档日志是否以应用

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

 

 SEQUENCE# APP

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

         4 NO

         5 YES

         6 YES

         7 YES

         8 YES

         9 YES

        10 YES

        11 YES

 

8 rows selected.

4     主备机切换

4.1    检查主库切换的可行性

SYS@BEIJING> select switchover_status from v$database;

 

SWITCHOVER_STATUS

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

TO STANDBY

4.2    准备当前主库的switchover操作

SYS@BEIJING> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

 

Database altered.

4.3    准备目标备库

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

 

Database altered.

4.4    确认主库已经可以进行switchover操作

SYS@BEIJING> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

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

TO LOGICAL STANDBY

4.5    切换原主库为备库

SYS@BEIJING> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

 

Database altered.

4.6    检查原备库是否可以进行switchover

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

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

TO PRIMARY

4.7    切换原备库为主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

4.8        启动实时SQL Apply

SYS@BEIJING> ALTER DATABASE START LOGICAL STANDBY APPLY immediate;

 

Database altered.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12710778/viewspace-753686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12710778/viewspace-753686/

cqh07516368311
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
创建逻辑备用库(Logical Standby Database)
hayyon的专栏
11-24 1425
下面的是Logical Standby Database的配置步骤.(下面用到的一些文件的位置都是临时性的,还得做一定的修改)将主数据库置为FORCE LOGGING模式.在主数据库创建之后做如下操作:SQL>ALTER DATABASE FORCE LOGGING; 确认主数据库是归档的并定义好本地归档.如下:SQL >ALTER SYSTEM SET LOG_ARCHIVE
The logical standby database
msdnchina的专栏@JiNan,ShanDong
02-22 1080
The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.
Data Guard之Logical Standby Database
crcp36898的博客
03-02 132
1、首先搭建Physical Standby Database，具体可参考"Data Guard之Physical Standby Database" 2、查询列出主数据库表中逻辑数据库不支持的表的方案名和表名 SQL&...
Logical standby database 搭建(配置)
weixin_34378045的博客
03-15 123
说明 Logical standby 数据库是通过Physical standby数据库转换的。本Logical standby是通过之前创建的Physical standby转换的。 Physical standby搭建可参考下列链接： http://www.cnblogs.com/zhenxing/p/5260693.html 逻辑DG支持的数据类型 在从物理DG转换...
配置DG为逻辑standby模式
还不算晕的专栏
09-10 1850
1. 停止physical standby备库的redo apply SQL> alter database recover managed standby database cancel; 2. 在主库运行dbms_logstdby.build SQL> execute dbms_logstdby.build; 3. 在主库上切换物理备库转化为Logical standby S
运用logical standby技术实现Oracle数据库的读写分离
最新发布
09-09
在MySQL作为应用系统的后台数据库时，我们常常见到这样的架构，一拖二、一拖三等等。这是用MySQL的读写分离技术，实现...我们这里介绍最后一个方法，利用dataguard技术中的logical standby实现Oracle数据库的读写分离。
Database Logical
08-02
database logical script
Usage-Driven Database Design From Logical Data Modeling through Physical 无水印pdf
09-29
Usage-Driven Database Design From Logical Data Modeling through Physical Schema Definition 英文无水印pdf pdf所有页面使用FoxitReader和PDF-XChangeViewer测试都可以打开 本资源转载自网络，如有侵权，请...
DATABASE MODELING AND DESIGN--Logical Design Fifth Edition
06-18
一本值得一读的、数据库设计的好书！对本书若需涉及版权的使用，请与本书作者联系并获得相关授权！
Logical Replication
07-03
Logical Replication属于逻辑复制，适用于数据库实例的部分(单个数据库或者某些表)的复制，目前只支持表复制。 最大的不同就是可以向下游节点写入数据，也可以将多个数据库实例的数据，同步到一个目标数据库等等
创建Data guard logical standby database须知
cps9988的博客
09-27 82
数据类型1.1 Logical standby database 支持的数据类型CHARNCHARVARCHAR2 and VARCHARNVARCHAR2NUMBERDATETIMESTAMPTIMESTAMP WITH TI...
logical standby database问题解决---转
cnmcsdn8786的博客
07-31 92
工程师check logstdby数据库状态报告发现自10月29号来logical standby database就没有应用日志了. select * from dba_logstdby_log;查看结果发现真是如此. ...
创建logical standby步骤
loryliu的专栏
07-20 675
10.2.0.5 on windows 2008 64bit 1 创建一个physical
Attempt to fetch logical page (...) in database 2 failed. It belongs to allocation unit xxxx not to ...
weixin_34149796的博客
07-29 298
今天一个同事说在一个生产库执行某个存储过程，遇到了错误： Fatal error 605 occurred at jul 29 2014 我试着执行该存储过程，结果出现下面错误，每次执行该存储过程，得到的错误内容都不一样（page变化，还有就是allocation unit）变化 消息 605，级别 21，状态 3，过程 usp_xxxxxxxxx，第 228 行 Attempt to ...
Attempt to fetch logical page (1:8019) in database 5 failed. It belongs to allocation unit 720575947...
weixin_30826761的博客
12-03 430
重新生成出错表的索引就可以了 转载于:https://www.cnblogs.com/wudingfeng/p/4140480.html
oracle Architecture (instance and database) 第一章 实例与数据库 Logical Storage Structures （表空间，段）
卜维的csdn技术博客
09-07 174
oracle Architecture (instance and database) 第一章 实例与数据库 Logical Storage Structures 4 逻辑存储存储概述 概述： Oracle数据库为数据库中的所有数据分配逻辑空间。数据库空间分配的逻辑单元是数据块，区，段和表空间。在物理层面，数据存储在磁盘上的数据文件中（下一文章“物理存储结构”）。数据文件中的数据存储在操作系统块中...
如何查看mysql库中表的创建语句
dggwtsab530713的博客
09-15 8685
直接上方法 第一种方法：SQL(这种方法适用于在mysql实例可以正常启动的情况下) show create table test2; 第二种方法：客户端工具(这种方法适用于在mysql实例可以正常启动的情况下) SQLyog 或 Navicat 第三种方法：使用工具(这种方法适用于在mysql实例无法正常启动的情况下) mysql-utilities #工...
用touch创建logical
05-25
如果你想要创建一个名为logical的文件，可以在终端输入以下命令： ``` touch logical ``` 这将在当前目录中创建一个名为logical的空文件。如果该文件已经存在，则它的时间戳将被更新为当前时间。请注意，这是一个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值