oracle同步数据adg_oracle11g ADG主备切换

1、主库操作

将主库切换成备库

点击(此处)折叠或打开

select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;

alter system switch logfile;

alter system archive log current;

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

如果swtichover_status状态为session active，就需要在命令中加入with session shutdown子句。

执行后，我们发现Primary ora11g已经关闭。

点击(此处)折叠或打开

shutdown abort

主库切换为备库时的alter日志输出：

alter database commit to switchover to physical standby with session shutdown

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5444] (bims)

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Waiting for all FAL entries to be archived...

All FAL entries have been archived.

Waiting for potential Physical Standby switchover target to become synchronized...

Active, synchronized Physical Standby switchover target has been identified

Switchover End-Of-Redo Log thread 1 sequence 14647 has been fixed

Switchover: Primary highest seen SCN set to 0xc3d.0xc405995f

ARCH: Noswitch archival of thread 1, sequence 14647

ARCH: End-Of-Redo Branch archival of thread 1 sequence 14647

ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH: Standby redo logfile selected for thread 1 sequence 14647 for destination LOG_ARCHIVE_DEST_2

Archived Log entry 4013 added for thread 1 sequence 14647 ID 0xffffffff891d432e dest 1:

ARCH: Archiving is disabled due to current logfile archival

Primary will check for some target standby to have received alls redo

Final check for a synchronized target standby. Check will be made once.

LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target

Active, synchronized target has been identified

Target has also received all redo

Backup controlfile written to trace file /orahome/oracle/diag/rdbms/bims/bims/trace/bims_ora_5444.trc

Clearing standby activation ID 2300396334 (0x891d432e)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 10 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 2147483648;

ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 2147483648;

Archivelog for thread 1 sequence 14647 required for standby recovery

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

USER (ospid: 5444): terminating the instance

Instance terminated by USER, pid = 5444

Completed: alter database commit to switchover to physical standby with session shutdown

Shutting down instance (abort)

License high water mark = 458

Tue Jul 19 22:45:46 2016

Instance shutdown complete

重新启动数据库到open状态作为只读备库：

点击(此处)折叠或打开

startup mount

select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

alter database open;

Database altered.

select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

2、备库操作

查看备库 switchover 状态

将备库切换为主库：

点击(此处)折叠或打开

select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO PRIMARY

alter database commit to switchover to primary with session shutdown ;

Database altered.

备库切换为主库时的alter日志输出：

Tue Jul 19 22:45:29 2016

RFS[4]: Assigned to RFS process 16695

RFS[4]: Possible network disconnect with primary database

Tue Jul 19 22:45:29 2016

RFS[2]: Possible network disconnect with primary database

Tue Jul 19 22:45:29 2016

Resetting standby activation ID 2300396334 (0x891d432e)

Media Recovery End-Of-Redo indicator encountered

Media Recovery Continuing

Media Recovery Waiting for thread 1 sequence 14648

Tue Jul 19 22:48:45 2016

alter database commit to switchover to primary with session shutdown

ALTER DATABASE SWITCHOVER TO PRIMARY (bims)

Maximum wait for role transition is 15 minutes.

Switchover: Media recovery is still active

Role Change: Canceling MRP - no more redo to apply

Tue Jul 19 22:48:46 2016

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /orahome/oracle/diag/rdbms/bimsstb/bims/trace/bims_pr00_19765.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Tue Jul 19 22:48:48 2016

MRP0: Background Media Recovery process shutdown (bims)

Role Change: Canceled MRP

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

Active process 15351 user 'oracle' program 'oracle@bimse-nbu'

Active process 15351 user 'oracle' program 'oracle@bimse-nbu'

CLOSE: all sessions shutdown successfully.

Tue Jul 19 22:48:48 2016

SMON: disabling cache recovery

Backup controlfile written to trace file /orahome/oracle/diag/rdbms/bimsstb/bims/trace/bims_ora_16385.trc

SwitchOver after complete recovery through change 13459421239647

Online log /orahome/oracle/oradata/bims/redo01.log: Thread 1 Group 1 was previously cleared

Online log /orahome/oracle/oradata/bims/redo02.log: Thread 1 Group 2 was previously cleared

Online log /orahome/oracle/oradata/bims/redo03.log: Thread 1 Group 3 was previously cleared

Online log /orahome/oracle/oradata/bims/redo04.log: Thread 1 Group 4 was previously cleared

Online log /orahome/oracle/oradata/bims/redo05.log: Thread 1 Group 5 was previously cleared

Online log /orahome/oracle/oradata/bims/redo06.log: Thread 1 Group 6 was previously cleared

Standby became primary SCN: 13459421239645

Switchover: Complete - Database mounted as primary

Completed: alter database commit to switchover to primary with session shutdown

点击(此处)折叠或打开

select open_mode from v$database;

OPEN_MODE

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

MOUNTED

select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

alter database open;

Database altered.

3、在备库启用日志实时应用模式

在现在的从库bimsd上(之前的主库上执行)：

点击(此处)折叠或打开

select open_mode from v$database;

OPEN_MODE

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

READ ONLY

alter database recover managed standby database using current logfile disconnect from session;

Database altered.

select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

alert日志输出：

Completed: ALTER DATABASE   MOUNT

Tue Jul 19 22:47:26 2016

alter database open

Beginning Standby Crash Recovery.

Serial Media Recovery started

Managed Standby Recovery starting Real Time Apply

Media Recovery Log /archivelog/1_14646_908373870.dbf

Tue Jul 19 22:48:04 2016

Media Recovery Log /archivelog/1_14647_908373870.dbf

Identified End-Of-Redo (switchover) for thread 1 sequence 14647 at SCN 0xc3d.c405995f

Resetting standby activation ID 0 (0x0)

Incomplete Recovery applied until change 13459421239647 time 07/19/2016 22:45:29

Completed Standby Crash Recovery.

Tue Jul 19 22:48:05 2016

SMON: enabling cache recovery

Dictionary check beginning

Dictionary check complete

Database Characterset is ZHS16GBK

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Physical standby database opened for read only access.

Completed: alter database open

Tue Jul 19 22:49:13 2016

Using STANDBY_ARCHIVE_DEST parameter default value as /archivelog

RFS[1]: Assigned to RFS process 5858

RFS[1]: Selected log 7 for thread 1 sequence 14648 dbid -1994533586 branch 908373870

Tue Jul 19 22:49:13 2016

Archived Log entry 4015 added for thread 1 sequence 14648 ID 0xffffffff89991d45 dest 1:

Tue Jul 19 22:49:16 2016

Primary database is in MAXIMUM PERFORMANCE mode

RFS[2]: Assigned to RFS process 5860

RFS[2]: Selected log 7 for thread 1 sequence 14650 dbid -1994533586 branch 908373870

Tue Jul 19 22:49:24 2016

RFS[3]: Assigned to RFS process 5862

RFS[3]: Selected log 8 for thread 1 sequence 14649 dbid -1994533586 branch 908373870

Tue Jul 19 22:49:25 2016

Archived Log entry 4016 added for thread 1 sequence 14649 ID 0xffffffff89991d45 dest 1:

Tue Jul 19 22:49:49 2016

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (bims)

Tue Jul 19 22:49:49 2016

MRP0 started with pid=34, OS id=5893

MRP0: Background Managed Standby Recovery process started (bims)

started logmerger process

Tue Jul 19 22:49:54 2016

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 64 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Clearing online redo logfile 1 /orahome/oracle/oradata/bims/redo01.log

Clearing online log 1 of thread 1 sequence number 14645

Completed: alter database recover managed standby database using current logfile disconnect from session

Tue Jul 19 22:50:06 2016

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /orahome/oracle/oradata/bims/redo02.log

Clearing online log 2 of thread 1 sequence number 14649

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /orahome/oracle/oradata/bims/redo03.log

Clearing online log 3 of thread 1 sequence number 14650

Tue Jul 19 22:50:24 2016

Clearing online redo logfile 3 complete

Clearing online redo logfile 4 /orahome/oracle/oradata/bims/redo04.log

Clearing online log 4 of thread 1 sequence number 14644

Clearing online redo logfile 4 complete

Clearing online redo logfile 5 /orahome/oracle/oradata/bims/redo05.log

Clearing online log 5 of thread 1 sequence number 14642

Tue Jul 19 22:50:42 2016

Clearing online redo logfile 5 complete

Clearing online redo logfile 6 /orahome/oracle/oradata/bims/redo06.log

Clearing online log 6 of thread 1 sequence number 14643

Clearing online redo logfile 6 complete

Tue Jul 19 22:50:51 2016

Media Recovery Log /archivelog/1_14648_908373870.dbf

Media Recovery Log /archivelog/1_14649_908373870.dbf

Media Recovery Waiting for thread 1 sequence 14650 (in transit)

Recovery of Online Redo Log: Thread 1 Group 7 Seq 14650 Reading mem 0

Mem# 0: /orahome/oracle/oradata/bims/sredo7.log

Tue Jul 19 22:51:42 2016

RFS[2]: Selected log 8 for thread 1 sequence 14651 dbid -1994533586 branch 908373870

Tue Jul 19 22:51:42 2016

Media Recovery Waiting for thread 1 sequence 14651 (in transit)

Recovery of Online Redo Log: Thread 1 Group 8 Seq 14651 Reading mem 0

Mem# 0: /orahome/oracle/oradata/bims/sredo8.log

Tue Jul 19 22:51:42 2016

Archived Log entry 4017 added for thread 1 sequence 14650 ID 0xffffffff89991d45 dest 1:

4、检查切换后的主备库状态

1)    主库上查询：

SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);

STATUS    GAP_STATUS

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

VALID

VALID     NO GAP

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SESSIONS ACTIVE

2)    从库信息：

SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);

STATUS    GAP_STATUS

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

VALID

VALID     NO GAP

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

NOT ALLOWED