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;

Oracle 11g Data guard 物理主備庫正常切換(switchover)流程

相關文章