利用DG 閃回?cái)?shù)據(jù)庫(kù)特性 - 模擬災(zāi)難演練
?
一、背景
客戶想利用停線機(jī)會(huì)，進(jìn)行災(zāi)難演練，模擬正式庫(kù)出問(wèn)題后，將應(yīng)用切換至DG環(huán)境，驗(yàn)證DG架構(gòu)。
環(huán)境：11.2.0.4 單機(jī) + DG （單機(jī)）
?
二、演練思路
-
將正式環(huán)境關(guān)閉，模擬主庫(kù)不可用，將DG激活成主庫(kù)給應(yīng)用使用。但是如果直接將DG Failover成主庫(kù)，會(huì)破壞原有的DG架構(gòu)，需要重新搭建DG，這是我們不愿意的，通過(guò)DG閃回?cái)?shù)據(jù)庫(kù)特性，可以解決該問(wèn)題。
-
將DG Failover成主庫(kù)測(cè)試完成后，重新將DG閃回到激活主庫(kù)之前的狀態(tài)，重新同步應(yīng)用日志。
?
三、災(zāi)難演練步驟
1. 開(kāi)啟DG閃回?cái)?shù)據(jù)庫(kù)
--1) 設(shè)置閃回區(qū)
SQL> alter system set db_recovery_file_dest='/oradata1/fast_recovery_area' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=100G scope=spfile;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size 2264856 bytes
Variable Size 2348810472 bytes
Database Buffers 1.0167E+10 bytes
Redo Buffers 8658944 bytes
Database mounted.
Database opened.
--2) 開(kāi)啟閃回?cái)?shù)據(jù)庫(kù)
SQL> alter database flashback on;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
--3) 檢查閃回?cái)?shù)據(jù)庫(kù)是否已開(kāi)啟
SQL> select open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ ONLY WITH APPLY YES
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1/fast_recovery_area
db_recovery_file_dest_size big integer 100G
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
2. 創(chuàng)建保證還原點(diǎn)
創(chuàng)建保證還原點(diǎn)，確保我們測(cè)試完成后，可以還原到該還原點(diǎn)。
--1) 檢查DG同步情況
SQL> set line222
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 1 347109
RFS IDLE 0 0
RFS IDLE 1 347110
RFS IDLE 0 0
MRP0 APPLYING_LOG 1 347110
8 rows selected.
--2) 創(chuàng)建保證還原點(diǎn)
SQL> create restore point before_application_test guarantee flashback database;
create restore point before_application_test guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_APPLICATION_TEST'.
ORA-01153: an incompatible media recovery is active
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point before_application_test guarantee flashback database;
Restore point created.
SQL> set numwidth 20
SQL> set line222
SQL> col name for a30
SQL> col time for a40
SQL> select scn,name,guarantee_flashback_database,time from v$restore_point;
SCN NAME GUA TIME
-------------------- ------------------------------ --- ----------------------------------------
6474039336229 BEFORE_APPLICATION_TEST YES 26-DEC-22 01.02.16.000000000 PM
3. 關(guān)閉主庫(kù)，模擬主庫(kù)宕機(jī)
--1) 關(guān)閉監(jiān)聽(tīng)
[oracle@MESDB backup]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 13:04:47
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MESDB)(PORT=1521)))
The command completed successfully
--2) 關(guān)閉數(shù)據(jù)庫(kù)實(shí)例
[oracle@MESDB backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 13:04:51 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
4. 激活DG為主庫(kù)
SQL> alter database recover managed standby database finish force;
Database altered.
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
mesdb READ ONLY PHYSICAL STANDBY TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
mesdb READ WRITE PRIMARY RESOLVABLE GAP
5. 應(yīng)用測(cè)試
此時(shí)，已經(jīng)將DG激活成主庫(kù)，應(yīng)用可以通過(guò)修改tns 或者 新的主庫(kù)修改ip、service_name等方式，連接新的主庫(kù)進(jìn)行應(yīng)用測(cè)試。
--1) 這里我們選擇增加一個(gè)service_name，應(yīng)用修改tns ip地址即可
SQL> alter system set service_names='mesdg','mesdb';
System altered.
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@MESDG ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 13:07:59
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MESDG)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-OCT-2022 21:25:54
Uptime 55 days 15 hr. 42 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/MESDG/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDG)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "mesdb" has 1 instance(s).
Instance "mesdg", status READY, has 1 handler(s) for this service...
Service "mesdbXDB" has 1 instance(s).
Instance "mesdg", status READY, has 1 handler(s) for this service...
Service "mesdg" has 2 instance(s).
Instance "mesdg", status UNKNOWN, has 1 handler(s) for this service...
Instance "mesdg", status READY, has 1 handler(s) for this service...
The command completed successfully
6. 閃回至DG模式
應(yīng)用測(cè)試完成后，將數(shù)據(jù)庫(kù)重新閃回至DG模式。
--1) 刪除多余的service name
SQL> alter system set service_names='mesdg';
System altered.
--2) 閃回到保證還原點(diǎn)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size 2264856 bytes
Variable Size 2348810472 bytes
Database Buffers 1.0167E+10 bytes
Redo Buffers 8658944 bytes
Database mounted.
SQL>
SQL> flashback database to restore point before_application_test;
Flashback complete.
--3）注意：雖然，此時(shí)我們已經(jīng)成功將將數(shù)據(jù)庫(kù)閃回到了測(cè)試前狀態(tài)，但是數(shù)據(jù)庫(kù)角色仍然是primary database，需要進(jìn)行角色轉(zhuǎn)化。
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size 2264856 bytes
Variable Size 2348810472 bytes
Database Buffers 1.0167E+10 bytes
Redo Buffers 8658944 bytes
SQL> alter database mount ;
Database altered.
SQL> alter database convert to physical standby;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
--3) 刪除保證還原點(diǎn)，否則會(huì)導(dǎo)在閃回區(qū)不能自動(dòng)刪除，DG同步受到影響
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size 2264856 bytes
Variable Size 2348810472 bytes
Database Buffers 1.0167E+10 bytes
Redo Buffers 8658944 bytes
SQL> alter database mount ;
Database altered.
SQL> drop restore point before_application_test;
Restore point dropped.
SQL> alter database open;
Database altered.
--4）啟動(dòng)MRP日志應(yīng)用進(jìn)程
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
--4）查看數(shù)據(jù)庫(kù)狀態(tài)，角色為PHYSICAL STANDBY
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
mesdb READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
7. 開(kāi)啟主庫(kù)
--1) 啟動(dòng)監(jiān)聽(tīng)
[oracle@MESDB backup]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-DEC-2022 07:27:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/mesdb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDB)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 26-DEC-2022 15:03:16
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/mesdb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MESDB)(PORT=1521)))
The listener supports no services
The command completed successfully
--2) 啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例
[oracle@MESDB backup]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 15:04:51 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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> startup
ORACLE instance started.
Total System Global Area 1.2527E+10 bytes
Fixed Size 2264856 bytes
Variable Size 2348810472 bytes
Database Buffers 1.0167E+10 bytes
Redo Buffers 8658944 bytes
Database mounted.
Database opened.
8. 檢查DG同步情況
-- 檢查DG同步情況
SQL> set line222
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 1 347117
MRP0 APPLYING_LOG 1 347114
9 rows selected.
9. 關(guān)閉DG閃回功能
這里，我們沒(méi)有關(guān)閉DG 閃回功能， 如果空間夠用，建議DG開(kāi)啟閃回?cái)?shù)據(jù)庫(kù)，這樣的話，如果主庫(kù)出現(xiàn)誤刪除數(shù)據(jù)，且undo也沒(méi)辦法進(jìn)行閃回查詢，此時(shí)我們就可以利用DG閃回?cái)?shù)據(jù)庫(kù)特性，將DG閃回到誤刪除之前，查找到被誤刪除的數(shù)據(jù)，之后再已同樣的方式恢復(fù)DG同步架構(gòu)，要比我們通過(guò)備份恢復(fù)快很多。
如果需要關(guān)閉，可以通過(guò)腳本進(jìn)行關(guān)閉：
SQL> alter database flashback off;
最后修改時(shí)間：2023-01-02 22:02:19
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來(lái)源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
