1,--rac备库,转换时,只有一个实例打开,其它实例关闭,转变角色后吗,呵呵,其它实例会自动进行角色转换哟,oracle我爱你
For a Real Application Clusters database, only one RAC instance on the standby database can be online during the role transition.
Shut down all other instances before starting the role transition.
Then, after the role transition completes, bring these instances back online.
Even though only one RAC instance on the standby database is open during the switchover, all other standby database
instances will automatically undergo a transition to their new role correctly when they are opened.
2
Data Guard provides the V$DATAGUARD_STATS view that can be used to estimate the viability of each standby database in terms of the currency
of the data in the standby database,
and the time it will take to perform. a role transition if all available redo data is applied to the standby database.
3 --花更多时间哟,如果备库打开模式(read only)
If the physical standby database is open for read-only access, the switchover still will take place, but will require additional time.
4
If possible, before performing a failover, you should transfer as much of the available and unapplied primary database redo data as possible to the standby database.
5 ---如果备库处理最大保护模式,为了转换角色,必须转换为最大性能模式,转换后,你可以再切换回来了
If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode by issuing the following statement on the standby database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
6
Oracle recommends you use only the failover steps and commands described in the following sections to perform. a failover. Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE to perform. a failover, because this statement may cause data loss.
逻辑备库切换出错
到这一步不成了
Step 4 Ensure the current primary database is ready for the future primary database's redo stream.
Before you can complete the role transition of the primary database to the logical standby role, verify the LogMiner Multiversioned Data Dictionary was received by the primary database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. Without the receipt of the LogMiner Multiversioned Data Dictionary, the switchover cannot proceed, because the current primary database will not be able to interpret the redo records sent from the future primary database. The SWITCHOVER_STATUS column shows the progress of the switchover.
When the query returns the TO LOGICAL STANDBY value, you can proceed with Step 5. For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO LOGICAL STANDBY
1 row selected
逻辑备库之switchover
1,在主库查看是否可以切换为物理备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--STANDBY or SESSIONS ACTIVE 表示可以切换,若为其它值,请检查log_archive_dest_n相关配置是否正确;如值为not prepare,另急,稍侯,就可以了
2,在主库准备切换为物理备库
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
3,在主库查看
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--其值应为PREPARING SWITCHOVER;这个阶段主库已经作好切换为备库的准备了,可以接受来自其它备库的重作日志传输
4,在逻辑备库上,作好准备切换到主库的工作
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;--此步骤会发送重作日志到其它备库和现在的主库(原来的主库)
5,在逻辑备库查看
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--PREPARING DICTIONARY(刚开始第4步时:LogMiner Multiversioned Data Dictionary is being recorded in the redo stream),第四步完成变换为PREPARING SWITCHOVER.
6,在主库查看它已经作好切换到逻辑备库的准备了
SELECT SWITCHOVER_STATUS FROM V$DATABASE; --若值为其下,说明可以正常切换了;看到其他值,请查看逻辑备库相关log_archive_dest配置,我就是查看其值一直为PREPARING SWITCHOVER,后在备库添加log_archive_dest_3='SERVICE=centos db_unique_name=centos';因为逻辑备库会切换为主库角色,他要把产生的归档传到新的备库(也就是原主库)
TO LOGICAL STANDBY
插补:
You can cancel the switchover operation by issuing the following statements in the following order:
1.
Cancel switchover on the primary database:
SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
2.
Cancel the switchover on the logical standby database:
SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
7,实施切换主库到逻辑备库(在主库)
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
8,在逻辑备库查看可以切换为新的主库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--若值为TO PRIMARY
9,在逻辑备库,切换它为主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
10,在新的备库(原主库)开启sql应用
ALTER DATABASE START LOGICAL STANDBY APPLY;
逻辑备库之failover
1,把主库在逻辑备库missing lag的归档日志,全部cp到备库,然后注册
alter database register logical logfile 'missing lag archived log';---在逻辑备库
2,确认所有的归档在逻辑备库全部应用
select applied_scn,latest_scn from v$logstdby_progress;--二列值同,就ok
note:如备库未开启sql apply,打开此功能
ALTER DATABASE START LOGICAL STANDBY APPLY FINISH;
3,把逻辑备库激活为新的主库
ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;--加finish选项就是转换角色前,把相关的归档全然在备库上apply
4,若有其它的备库配置,可以把他们加入新的dg环境中,方法如下
在其它每个备库上
alter session disable guard;
create database link 新主库数据库名 connect to username identified by password using '到新主库的net service name';--构建到新主库的数据库链接
alter session enable guard;--开启guard配置
select * from dba_logstdby_parameters@以上创建的数据库链接名
5,在每个逻辑备库上,实行sql apply
alter database start logical standby apply new primary 新主库数据库名;
若sql apply出现ora-16109,根据新主库的备份重建出错的逻辑备库,然后把它加入到dg环境中
6,可选步,在failover后,马上到新的主库进行备份
7,原出故障的主库恢复为新dg环境下的备库,可采用flashback database或其它
物理备库之switchover
1,先插述一段关于v$database中switchover_status所有列的相关解释,相当实用
SWITCHOVER_STATUS VARCHAR2(20) Indicates whether switchover is allowed:
*
NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases.
*
SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated.
*
SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
*
SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
*
TO PRIMARY - This is a standby database and is allowed to switch over to a primary database.
*
TO STANDBY - This is a primary database and is allowed to switch over to a standby database.
*
RECOVERY NEEDED - This is a standby database that has not received the switchover request.
*
PREPARING SWITCHOVER - Either this is a primary database that is accepting redo data from a logical standby database in preparation for switch over to the logical standby database role, or it is a logical standby database sending redo data to a primary database and other standby databases in preparation for switch over to the primary database role. In the latter case, a completed dictionary has already been sent to the primary database and other standby databases.
*
PREPARING DICTIONARY - This is a logical standby database that is sending redo data to a primary database and other standby databases in the configuration in preparation for switch over to the primary database role.
*
TO LOGICAL STANDBY - This is a primary database that has received a complete dictionary from a logical standby database.
2,在作switchover前,最好对原主库作个rman或全库备份,万一switchover失败,也好恢复原状,不然就麻烦了,成二吊子了
3,在作switchover前,在原物理务库配置log_archive_dest_2(SERVICE),用于传送归档到新的备库(原主库)
4,若switchover失败,再次根据失败切换的主库再构建备库,就会出错,目前没有找到原因
For a Real Application Clusters database, only one RAC instance on the standby database can be online during the role transition.
Shut down all other instances before starting the role transition.
Then, after the role transition completes, bring these instances back online.
Even though only one RAC instance on the standby database is open during the switchover, all other standby database
instances will automatically undergo a transition to their new role correctly when they are opened.
2
Data Guard provides the V$DATAGUARD_STATS view that can be used to estimate the viability of each standby database in terms of the currency
of the data in the standby database,
and the time it will take to perform. a role transition if all available redo data is applied to the standby database.
3 --花更多时间哟,如果备库打开模式(read only)
If the physical standby database is open for read-only access, the switchover still will take place, but will require additional time.
4
If possible, before performing a failover, you should transfer as much of the available and unapplied primary database redo data as possible to the standby database.
5 ---如果备库处理最大保护模式,为了转换角色,必须转换为最大性能模式,转换后,你可以再切换回来了
If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode by issuing the following statement on the standby database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
6
Oracle recommends you use only the failover steps and commands described in the following sections to perform. a failover. Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE to perform. a failover, because this statement may cause data loss.
逻辑备库切换出错
到这一步不成了
Step 4 Ensure the current primary database is ready for the future primary database's redo stream.
Before you can complete the role transition of the primary database to the logical standby role, verify the LogMiner Multiversioned Data Dictionary was received by the primary database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. Without the receipt of the LogMiner Multiversioned Data Dictionary, the switchover cannot proceed, because the current primary database will not be able to interpret the redo records sent from the future primary database. The SWITCHOVER_STATUS column shows the progress of the switchover.
When the query returns the TO LOGICAL STANDBY value, you can proceed with Step 5. For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO LOGICAL STANDBY
1 row selected
逻辑备库之switchover
1,在主库查看是否可以切换为物理备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--STANDBY or SESSIONS ACTIVE 表示可以切换,若为其它值,请检查log_archive_dest_n相关配置是否正确;如值为not prepare,另急,稍侯,就可以了
2,在主库准备切换为物理备库
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
3,在主库查看
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--其值应为PREPARING SWITCHOVER;这个阶段主库已经作好切换为备库的准备了,可以接受来自其它备库的重作日志传输
4,在逻辑备库上,作好准备切换到主库的工作
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;--此步骤会发送重作日志到其它备库和现在的主库(原来的主库)
5,在逻辑备库查看
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--PREPARING DICTIONARY(刚开始第4步时:LogMiner Multiversioned Data Dictionary is being recorded in the redo stream),第四步完成变换为PREPARING SWITCHOVER.
6,在主库查看它已经作好切换到逻辑备库的准备了
SELECT SWITCHOVER_STATUS FROM V$DATABASE; --若值为其下,说明可以正常切换了;看到其他值,请查看逻辑备库相关log_archive_dest配置,我就是查看其值一直为PREPARING SWITCHOVER,后在备库添加log_archive_dest_3='SERVICE=centos db_unique_name=centos';因为逻辑备库会切换为主库角色,他要把产生的归档传到新的备库(也就是原主库)
TO LOGICAL STANDBY
插补:
You can cancel the switchover operation by issuing the following statements in the following order:
1.
Cancel switchover on the primary database:
SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
2.
Cancel the switchover on the logical standby database:
SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
7,实施切换主库到逻辑备库(在主库)
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
8,在逻辑备库查看可以切换为新的主库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;--若值为TO PRIMARY
9,在逻辑备库,切换它为主库
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
10,在新的备库(原主库)开启sql应用
ALTER DATABASE START LOGICAL STANDBY APPLY;
逻辑备库之failover
1,把主库在逻辑备库missing lag的归档日志,全部cp到备库,然后注册
alter database register logical logfile 'missing lag archived log';---在逻辑备库
2,确认所有的归档在逻辑备库全部应用
select applied_scn,latest_scn from v$logstdby_progress;--二列值同,就ok
note:如备库未开启sql apply,打开此功能
ALTER DATABASE START LOGICAL STANDBY APPLY FINISH;
3,把逻辑备库激活为新的主库
ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;--加finish选项就是转换角色前,把相关的归档全然在备库上apply
4,若有其它的备库配置,可以把他们加入新的dg环境中,方法如下
在其它每个备库上
alter session disable guard;
create database link 新主库数据库名 connect to username identified by password using '到新主库的net service name';--构建到新主库的数据库链接
alter session enable guard;--开启guard配置
select * from dba_logstdby_parameters@以上创建的数据库链接名
5,在每个逻辑备库上,实行sql apply
alter database start logical standby apply new primary 新主库数据库名;
若sql apply出现ora-16109,根据新主库的备份重建出错的逻辑备库,然后把它加入到dg环境中
6,可选步,在failover后,马上到新的主库进行备份
7,原出故障的主库恢复为新dg环境下的备库,可采用flashback database或其它
物理备库之switchover
1,先插述一段关于v$database中switchover_status所有列的相关解释,相当实用
SWITCHOVER_STATUS VARCHAR2(20) Indicates whether switchover is allowed:
*
NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases.
*
SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated.
*
SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.
*
SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.
*
TO PRIMARY - This is a standby database and is allowed to switch over to a primary database.
*
TO STANDBY - This is a primary database and is allowed to switch over to a standby database.
*
RECOVERY NEEDED - This is a standby database that has not received the switchover request.
*
PREPARING SWITCHOVER - Either this is a primary database that is accepting redo data from a logical standby database in preparation for switch over to the logical standby database role, or it is a logical standby database sending redo data to a primary database and other standby databases in preparation for switch over to the primary database role. In the latter case, a completed dictionary has already been sent to the primary database and other standby databases.
*
PREPARING DICTIONARY - This is a logical standby database that is sending redo data to a primary database and other standby databases in the configuration in preparation for switch over to the primary database role.
*
TO LOGICAL STANDBY - This is a primary database that has received a complete dictionary from a logical standby database.
2,在作switchover前,最好对原主库作个rman或全库备份,万一switchover失败,也好恢复原状,不然就麻烦了,成二吊子了
3,在作switchover前,在原物理务库配置log_archive_dest_2(SERVICE),用于传送归档到新的备库(原主库)
4,若switchover失败,再次根据失败切换的主库再构建备库,就会出错,目前没有找到原因
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-625734/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-625734/