ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g (windows)主备库切换

Oracle 10g (windows)主备库切换

原创 Linux操作系统 作者:lq8234331 时间:2011-07-08 17:33:49 0 删除 编辑

 1. switchover

 一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。 在进行DATA GUARD的物理STANDBY切换前需要注意:

 

确认主库和从库间网络连接通畅;

 

确认没有活动的会话连接在数据库中;

 

PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;

 

确保STANDBY数据库处于ARCHIVELOG模式;

 

如果设置了REDO应用的延迟,那么将这个设置去掉;

 

确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。

 

 

主库:

 

1. 查看switchover 状态

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

      to standby

 

附: A:switchover_status出现session active/not allowed

 

        当出现session active的时候表示还有活动的session,则转换时运行

 

         Alter database commit to switchover to physical standby with session shutdown;

 

      当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了,如果大家在测试不成功的时候再和我说,让我看看在什么情况下会不成功。

 

  

 

     B.ora- 01153: an incompatible media recovery is active

 

        运行下面代码

 

        Alter database recover managed standby database finish;

 

        或者Alter database recover managed standby database finish force;

 

        Alter database recover managed standby database disconnect from session;

 

2 切换成备库

 

SQL>Alter database commit to switchover to physical standby with session shutdown;

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

 

     Database altered.

 

 

3 启动到mount和应用日志状态

 

SQL> SHUTDOWN IMMEDIATE

 

SQL> startup nomount;

 

SQL> alter database mount standby database;

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

 

4. 查看数据库模式

 

SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;

 

SQL>select status,database_mode from v$archive_dest_status;

 

 

在备库:

 

 

1.查看switchover状态

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

    TO PRIMARY

 

附:若不是用此语句切换:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

 

2. 切换成主库

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown ;

 

Database altered.

 

SQL> shutdown immediate;

 

SQL> startup;

 

SQL> alter system switch logfile;

 

3. 查看数据库模式

 

SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;

 

SQL>select status,database_mode from v$archive_dest_status;

 

 

注意地方:

 

如果做了switchover,主库参数设置成以下方式,会触发ora-16009错误

 

Alert system set log_archive_dest_2=’service=primary

 

DB_UNIQUE_NAME=orcl’ scope=spfile;

 

然后再alert_orcl.log 日志中会出现以下错误内容

 

Thu Nov 27 10:19:12 2008

 

Redo Shipping Client Connect

 

-- Connected User is Valid

 

RFS[2]: Assigned to RFS process 1292

 

RFS[2]: Database mount ID mismatch [0x4781d95f:0x47823be1]

 

RFS[2]: Client instance is standby database i

 

RFS[2]: Not using real app

 

Thu Nov 27 10:19Errors in file

 

d:\oracle\product\10.2.0\admin\orcl\udump\orc

 

ORA-16009: 远程归档日

 

metalink上查到:

 

* fact: Oracle Server - Enterprise Edition 9

 

* symptom: Errors appears in alert.log on primary database

 

* symptom: RFS: client instance is standby database instead

 

* symptom: RFS: Not using real application clusters

 

* symptom: Errors appear in alert.log on standby database

 

* symptom:

 

database

 

standby database

 

primary database

 

* symptom: Standby redo log files are defined on the standby database

 

* cause: The standby redo log files are synchronously filled with redo

 

from the primary database. When a logswitch occur on the primary database,

 

those files are archived on the standby database before being applyed on

 

it. The archiving process on the standby database should only archive to

 

the local disks on tprimarfix:

 

Disable the remote archiving on the standby databasExample: alter system set log_archive_dest_2 = ''

 

是因为没有把standby 上的log_archive_dest_2 清空导致的。

 

另外也有可

 

bug 4676659

 

Standby may not be recognised (ORA-16009)

 

When the log transport is LGWR ASYNC and logical standby has

 

LOG_ARCHIVE_DEST setting VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)

 

ORA-16009 is reportedregular interWorkaround:

 

There is no workaround to prevent ORA-16009 from appearing in alert logs.

 

 

2. Failovers

 

FAILOVER切换一般是PRIMARY数据库发生故障后的切换,这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后,可能会造成数据的丢失。而且这个过程不是可逆的,DATA GUARD环境会被破坏。

 

由于PRIMARY数据库已经无法启动,所以FAILOVER切换所需的条件并不多,只要检查STANDBY是否运行在最大保护模式下,如果是的话,需要将其置为最大性能模式,否则切换到PRIMARY角色也无法启动。

 

 

 

1.查看是否有日志GAP,没有应用的日志:

 

    SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

 

  SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 

  如果有,则拷贝过来并且注册

 

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径';

 

重复查看直到没有应用的日志:

 

2. 然后停止应用归档:

 

  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

  Database altered.

 

3. 下面将STANDBY数据库切换为PRIMARY数据库:

 

  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

 

  Database altered.

 

  SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

 

  DATABASE_ROLE

 

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

 

  PHYSICAL STANDBY

 

  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

 

  Database altered.

 

    SQL> ALTER DATABASE OPEN; 或者 shutdown immediate+startup

 

  Database altered.

 

 

  检查数据库是否已经切换成功:

 

  SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

 

  DATABASE_ROLE

 

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

 

  PRIMARY

 

  至此,FAILOVER切换完成。这个时候应该马上对新的PRIMARY数据库进行备份。

 

数据库模式查看:

 

1. 首先查看当前的保护模式   ---primary数据库操作

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

 

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

 

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

 

 

2. 设置新的数据保护模式并重启数据库    --primary数据库操作

 

 

 

当保护模式更改顺序:

 

maximize protection --->  maximize availability ----> maximize performance

 

当在把dataguard的保护级别按这上面的顺序减低的时候, 不需要primary库在mount状态,否则primary 必须在mount 状态。

 

 

 

如:

SQL>  alter database set standby database to maximize availability;

 

 alter database set standby database to maximize availability

 

*

 

ERROR at line 1:

 

ORA-01126: database must be mounted in this instance and not open in any

 

instance

 

 

 

SQL> shutdown immediate

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL> startup mount;

 

ORACLE instance started.

 

 

 

Total System Global Area  167772160 bytes

 

Fixed Size                  1218316 bytes

 

Variable Size              79694068 bytes

 

Database Buffers           83886080 bytes

 

Redo Buffers                2973696 bytes

 

Database mounted.

 

SQL> alter database set standby database to maximize availability;

 

Database altered.

 

SQL> alter database open;

 

alter database open

 

*

 

ERROR at line 1:

 

ORA-03113: end-of-file on communication channel

 

 

 

报错了,这是因为最大可用性需要先修改日志传送方式为lgwr同步方式,否则,数据库是无法open.

 

 

 

Maximum protection/AVAILABILITY模式必须满足以下条件

 

Redo Archival Process: LGWR

 

Network Tranmission mode: SYNC

 

Disk Write Option: AFFIRM

 

Standby Redo Logs: Yes

 

standby database type: Physical Only

 

 

 

SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync AFFIRM';

 

System altered.

 

SQL> shutdown immediate

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL> startup mount;

 

ORACLE instance started.

 

Total System Global Area  167772160 bytes

 

Fixed Size                  1218316 bytes

 

Variable Size              79694068 bytes

 

Database Buffers           83886080 bytes

 

Redo Buffers                2973696 bytes

 

Database mounted.

 

SQL> alter database set standby database to maximize availability;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

 

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

 

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

 

 

 

 

 

alter database set standby database to maximize performance;

 

提示:maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE},分别对应最大保护,最高可用性及最高性能。

 

 

 

在最大保护模式下,直接关闭备库是不行的,如果在备库上关闭数据库,会有如下提示:

SQL> shutdown immediate

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL>

在最大保护模式下,备库是不允许关闭的,此时首先关闭主库,然后备库就可以顺利关闭了。

 

注意: 主库的保护模式修改之后,备库的模式也会改变,和主库保持一致。

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-07-08

  • 博文量
    3
  • 访问量
    2460