Oracle 11g Data guard 物理主备库正常切换(switchover)流程

1、在主库上面，查看主库是否可以切换，当查询结果为”TO STANDBY”或” SESSIONS ACTIVE”，则说明主库可以切换到备库

sqlplus / as sysdba   SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;   SWITCHOVER_STATUS  -----------------  TO STANDBY  1 row selected

 

2、将主库切换成物理备库，并重启数据库，将数据库打开到MOUNT状态

sqlplus / as sysdba ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;   SHUTDOWN ABORT; STARTUP MOUNT;

alert 日志如下

2361 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
2362 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 13903] (fire)
2363 Tue Dec 01 13:29:06 2015
2364 Thread 1 advanced to log sequence 16 (LGWR switch)
2365   Current log# 1 seq# 16 mem# 0: /ORADATA/oradata/fire/redo01.log
2366 Tue Dec 01 13:29:06 2015
2367 Stopping background process CJQ0
2368 Stopping background process QMNC
2369 Tue Dec 01 13:29:06 2015
2370 Archived Log entry 17 added for thread 1 sequence 15 ID 0x36b20d37 dest 1:
2371 All dispatchers and shared servers shutdown
2372 CLOSE: killing server sessions.
2373 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2374 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2375 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2376 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2377 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2378 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2379 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2380 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2381 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2382 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2383 Active process 16571 user 'oracle' program 'oracle@ATFDB1 (W000)'
2411 CLOSE: all sessions shutdown successfully.
2412 Waiting for all non-current ORLs to be archived...
2413 All non-current ORLs have been archived.
2414 Waiting for all FAL entries to be archived...
2415 All FAL entries have been archived.
2416 Waiting for potential Physical Standby switchover target to become synchronized...
2417 Active, synchronized Physical Standby switchover target has been identified
2418 Switchover End-Of-Redo Log thread 1 sequence 16 has been fixed
2419 Switchover: Primary highest seen SCN set to 0x0.0x108640
2420 ARCH: Noswitch archival of thread 1, sequence 16
2421 ARCH: End-Of-Redo Branch archival of thread 1 sequence 16
2422 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
2423 ARCH: Standby redo logfile selected for thread 1 sequence 16 for destination LOG_ARCHIVE_DEST_2
2424 Archived Log entry 18 added for thread 1 sequence 16 ID 0x36b20d37 dest 1:
2425 ARCH: Archive log thread 1 sequence 16 available in 120 minute(s)
2426 ARCH: Archiving is disabled due to current logfile archival
2427 Primary will check for some target standby to have received alls redo
2428 Final check for a synchronized target standby. Check will be made once.
2429 LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
2430 Active, synchronized target has been identified
2431 Target has also received all redo
2432 Backup controlfile written to trace file /u02/app/oracle/diag/rdbms/fire/fire/trace/fire_ora_13903.trc
2433 Clearing standby activation ID 917638455 (0x36b20d37)
2434 The primary database controlfile was created using the
2435 'MAXLOGFILES 16' clause.

2436 There is space for up to 13 standby redo logfiles
2437 Use the following SQL commands on the standby database to create
2438 standby redo logfiles that match the primary database:
2439 ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
2440 ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
2441 ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
2442 ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
2443 Archivelog for thread 1 sequence 16 required for standby recovery
2444 Switchover: Primary controlfile converted to standby controlfile succesfully.
2445 Switchover: Complete - Database shutdown required
2446 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

3、在备库上面，查看备库是否已准备好切换到主备，当查询结果为” TO PRIMARY”或” SESSIONS ACTIVE”，则说明备库已经做好了切换到主库的准备

sqlplus / as sysdba SELECT SWITCHOVER_STATUS FROM V$DATABASE;   SWITCHOVER_STATUS ----------------- TO PRIMARY 1 row selected

 

4、将物理备库切换到主库，并将数据库打开

sqlplus / as sysdba   ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;   ALTER DATABASE OPEN;

 
后台 alert 日志如下

1815 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
1816 ALTER DATABASE SWITCHOVER TO PRIMARY (fire)
1817 Maximum wait for role transition is 15 minutes.
1818 Database not available for switchover
1819   End-Of-REDO archived log file has not been recovered
1820   Incomplete recovery SCN:0:1056248 archive SCN:0:1082944
1821 Database not available for switchover
1822   End-Of-REDO archived log file has not been recovered
1823   Incomplete recovery SCN:0:1056248 archive SCN:0:1082944
1824 Switchover: Media recovery required - standby not in limbo
1825 ORA-16139 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN...
1826 Tue Dec 01 13:32:59 2015
1827 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION
1828 Attempt to start background Managed Standby Recovery process (fire)
1829 Tue Dec 01 13:32:59 2015
1830 MRP0 started with pid=52, OS id=3194
1831 MRP0: Background Managed Standby Recovery process started (fire)
1832  started logmerger process
1833 Tue Dec 01 13:33:04 2015
1834 Managed Standby Recovery not using Real Time Apply
1835 Parallel Media Recovery started with 24 slaves
1836 Waiting for all non-current ORLs to be archived...
1837 All non-current ORLs have been archived.
1838 Media Recovery Log /ORADATA/arc/1_14_897300089.dbf
1839 Media Recovery Log /ORADATA/arc/1_15_897300089.dbf
1840 Media Recovery Log /ORADATA/arc/1_16_897300089.dbf
1841 Identified End-Of-Redo (switchover) for thread 1 sequence 16 at SCN 0x0.108640
1842 Resetting standby activation ID 917638455 (0x36b20d37)

1861 Backup controlfile written to trace file /u02/app/oracle/diag/rdbms/flame/fire/trace/fire_ora_3075.trc
1862 SwitchOver after complete recovery through change 1082944
1863 Online log /ORADATA/oradata/fire/redo01.log: Thread 1 Group 1 was previously cleared
1864 Online log /ORADATA/oradata/fire/redo02.log: Thread 1 Group 2 was previously cleared
1865 Online log /ORADATA/oradata/fire/redo03.log: Thread 1 Group 3 was previously cleared
1866 Standby became primary SCN: 1082942
1867 Switchover: Complete - Database mounted as primary
1868 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

5、在新的物理备库上，打开日志实时应用

sqlplus / as sysdba ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE  DISCONNECT FROM SESSION;

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

转载于:http://blog.itpub.net/26506993/viewspace-1850664/