Oracle11gphysicaldataguard之快照備用

在oracle 10g要準(zhǔn)備一個讀寫備用的數(shù)據(jù)庫還是很繁瑣的，準(zhǔn)備好dataguard后得手動創(chuàng)建還原點，手動停日志傳送，手動激活并強制打開，測試完了，如果主備的SCN差太多，你還得做增量備份追，統(tǒng)計了下需15步，和搭一個physical standby的步驟差不多了，所以用的極少。到11g里終于解放了，啟用快照備庫只需3步(當(dāng)然中間重啟的次數(shù)不算),恢復(fù)到實時應(yīng)用備用也只需2步，日志還是繼續(xù)傳，需要鏡像庫測試的朋友，可以放心用了(用dataguard borker更簡單)。當(dāng)然轉(zhuǎn)換成Snapshot Standby，是有些附加條件的(沒有的參照前文去搭建一個)：
1 數(shù)據(jù)庫閃回得打開；
2 db_recovery_file_dest_size還是要有足夠的空間的；
3 如果使用的保護模式是Maximum Protection模式，必須有其他的Standby與之相匹配,否則小心主庫宕機。
手動做的步驟如下：
１檢查閃回

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站制作、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的祁門網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

 SQL> select flashback_on,database_role,open_mode from v$database;  
FLASHBACK_ON       DATABASE_ROLE    OPEN_MODE
------------------ ---------------- --------------------
NO                 PHYSICAL STANDBY READ ONLY WITH APPLY

當(dāng)前Standby狀態(tài)是只讀Apply狀態(tài)，這個時候需要終止Apply過程，并且切換回mount狀態(tài)。否則是不允許進行convert動作的。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

報錯了，這個錯誤好解決：

 SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 51000M
SQL> alter system set db_recovery_file_dest='/data';
SQL> alter database flashback on;
SQL> select flashback_on from v$database; 
FLASHBACK_ON
------------------
YES

2 轉(zhuǎn)換

SQL> alter database convert to snapshot standby;
SQL> alter  database open;

有興趣的可以看下alert_sid.log
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1974538
Resetting resetlogs activation ID 1662850232 (0x631d14b8)
Online log /data/db/onlinelog/group_1.261.899048765: Thread 1 Group 1 was previously cleared
Online log /data/db/onlinelog/group_2.260.899048765: Thread 1 Group 2 was previously cleared
Online log /data/db/onlinelog/group_3.277.899049819: Thread 2 Group 3 was previously cleared
Online log /data/db/onlinelog/group_4.278.899049819: Thread 2 Group 4 was previously cleared
Online log /data/db/onlinelog/group_5.280.908381663: Thread 1 Group 5 was previously cleared
Online log /data/db/onlinelog/group_6.281.908381749: Thread 1 Group 6 was previously cleared
Online log /data/db/onlinelog/group_7.282.908381877: Thread 1 Group 7 was previously cleared
檢查下當(dāng)前數(shù)據(jù)庫狀態(tài)：

 SQL> select open_mode, database_role, protection_mode from v$database;

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
READ WRITE           SNAPSHOT STANDBY MAXIMUM AVAILABILITY

已經(jīng)變成可寫狀態(tài)，查詢flash_back開始的SCN:

 SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASH
-------------------- ------------
             1974537 17-MAY-17

從這里開始可以對備庫進行任何操作：

SQL> create table  test  as select * from all_objects; 
Table created. 
SQL> select count(*) from test; 
  COUNT(*)
----------
     14629 
SQL> drop table STAGE_TERADATA_OFFLINE_PKEYS purge; 
Table dropped.

切回：
1 關(guān)庫，切換

 SQL>shutdown immediate
SQL>startup mount;
SQL> alter database convert to physical standby;

這里查看alert_sid.log可以看到
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
刪除了還原點
2 關(guān)庫，關(guān)閃回，啟用real time apply

SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database flashback off;
SQL>alter database open; 
SQL>RECOVER  managed standby database using current logfile disconnect from session  
SQL>select open_mode, database_role, protection_mode,current_SCN from v$database;
OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      CURRENT_SCN
-------------------- ---------------- -------------------- -----------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY     1977350

檢查下剛才測試的數(shù)據(jù)：

 [oracle@ora9-2 data]$ sqlplus scott/test 
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 17 08:42:08 2017 
Copyright (c) 1982, 2013, Oracle.  All rights reserved. 
ERROR:
ORA-28002: the password will expire within 18446744073709551614 days  
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from test; 
select count(*) from test
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from STAGE_TERADATA_OFFLINE_PKEYS; 
no rows selected

該有的還在，不該有的也沒有了，挺好。

當(dāng)前標(biāo)題：Oracle11gphysicaldataguard之快照備用
轉(zhuǎn)載注明：http://muchs.cn/article22/ipgjcc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、域名注冊、品牌網(wǎng)站建設(shè)、做網(wǎng)站、App設(shè)計、動態(tài)網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容