溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
因客戶需要部署19c DG環(huán)境，以前一直是11g和12c部署，故測試下19c部署
1、DG基礎環(huán)境
?
ORACLE 主庫IP：111.111.111.203? SID:DT??? db_name='DT'??? 主機名：test19c
?
ORACLE 備庫IP：111.111.111.204? SID:dgtest?? db_name='DT'??? 主機名：testdg19c
?
主庫歸檔目錄物理路徑：
SQL> archive log list
?
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
?
?
主庫datafile物理路徑
/u01/app/oradata/
/u01/app/oradata/DT
?
主庫redo物理路徑
/u01/app/oradata/DT
?
參數(shù)*log_archive_config='dg_config(pri,std)'，以確保主備庫數(shù)據(jù)庫能夠互相識別對方。
查看歸檔是否有報錯
select status,error from v$archive_dest;
?
?
?
2、修改主庫配置文件initTESTDB.ora
這里現(xiàn)在數(shù)據(jù)庫里修改相關的參數(shù)，與DG的參數(shù)就只與幾個參數(shù)相關，大概就是日志，文件的位置的轉換，GAP的處理，其實GAP已經(jīng)會自動的處理，不過這里我們還是介紹配置FAL_SERVER,FAL_CLIENT參數(shù)。在修改完之后重新創(chuàng)建了pfile文件
先創(chuàng)建spfile，修改完后重新生成pfile
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DT,dgtest)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=dgtest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set FAL_SERVER=dgtest;
SQL> alter system set FAL_CLIENT=DT;
SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> create pfile from spfile;
DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT參數(shù)必須重啟數(shù)據(jù)庫生效
下面是修改好之后重新生成的pfile文件
DT.__data_transfer_cache_size=0
DT.__db_cache_size=247463936
DT.__inmemory_ext_roarea=0
DT.__inmemory_ext_rwarea=0
DT.__java_pool_size=4194304
DT.__large_pool_size=20971520
DT.__oracle_base='/u01/app'#ORACLE_BASE set from environment
DT.__pga_aggregate_target=293601280
DT.__sga_target=549453824
DT.__shared_io_pool_size=16777216
DT.__shared_pool_size=243269632
DT.__streams_pool_size=0
DT.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/admin/DT/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oradata/DT/control01.ctl','/u01/app/oradata/DT/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'
*.db_name='DT'
*.db_unique_name='DT'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DTXDB)'
*.fal_client='DT'
*.fal_server='dgtest'
*.local_listener='LISTENER_DT'
*.log_archive_config='DG_CONFIG=(DT,dgtest)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT'
*.log_archive_dest_2='SERVICE=dgtest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'
*.memory_max_target=903741824
*.memory_target=839524096
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
?
?
?
3、? 修改備庫的配置文件為：initdgtest.ora
DT.__data_transfer_cache_size=0
DT.__db_cache_size=339738624
DT.__inmemory_ext_roarea=0
DT.__inmemory_ext_rwarea=0
DT.__java_pool_size=4194304
DT.__large_pool_size=20971520
DT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DT.__pga_aggregate_target=201326592
DT.__sga_target=641728512
DT.__shared_io_pool_size=12582912
DT.__shared_pool_size=247463936
DT.__streams_pool_size=0
DT.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/admin/dgtest/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oradata/dgtest/control01.ctl','/u01/app/oradata/dgtest/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'
*.db_name='DT'
*.db_unique_name='dgtest'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgtestXDB)'
*.fal_client='dgtest'
*.fal_server='DT'
*.local_listener='LISTENER_dgtest'
*.log_archive_config='DG_CONFIG=(DT,dgtest)'
*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest'
*.log_archive_dest_2='SERVICE=DT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DT'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'
*.memory_max_target=903741824
*.memory_target=839524096
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
?
注意：
1> Linux端配置的pfile文件中，所有windows的路徑都要用大寫，因為在duplication過程中，windows端都是按照大寫路徑來傳輸?shù)模?/span>
如果用小寫或者大小寫混合，則無法識別路徑，會有問題！
2> 在duplication過程中，雖然是在主庫操作，但是datafile和logfile的路徑轉換卻認的是備庫的pfile文件中的轉換路徑！
?
?
?
4、修改主庫的listener.ora文件? ---如果不配置會報錯rman-04006 ora-12514
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
?
LISTENER =
? (DESCRIPTION_LIST =
??? (DESCRIPTION =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
????? (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
??? )
? )
?
SID_LIST_LISTENER =
? (SID_LIST =
??? (SID_DESC =
????? (GLOBAL_DBNAME = DT)
????? (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
????? (SID_NAME = DT)
??? )
? )
?
?
ADR_BASE_LISTENER = D:\app\Administrator
?
?
?
5、修改主庫的tnsnames.ora文件
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
?
LISTENER_DT =
? (ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
?
?
DT =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = DT)
??? )
? )
?
dgtest =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = dgtest)
??? )
? )
?
?
?
6、修改備庫的listener.ora文件
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
?
LISTENER =
? (DESCRIPTION_LIST =
??? (DESCRIPTION =
????? (ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
????? (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
??? )
? )
?
SID_LIST_LISTENER =
? (SID_LIST =
??? (SID_DESC =
????? (SID_NAME = CLRExtProc)
????? (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
????? (PROGRAM = extproc)
????? #(ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/product/19.2.0/dbhome_1/oraclr11.dll")
??? )
??? (SID_DESC =
????? (GLOBAL_DBNAME = dgtest)
????? (ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
????? (SID_NAME = dgtest)
??? )
? )
?
?
?
7、修改備庫的tnsnames.ora文件
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
?
LISTENER_DT =
? (ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
?
?
DT =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = DT)
??? )
? )
?
dgtest =
? (DESCRIPTION =
??? (ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
??? (CONNECT_DATA =
????? (SERVER = DEDICATED)
????? (SERVICE_NAME = dgtest)
??? )
? )
?
?
?
8、備庫生成orapwd文件
orapwd file=orapwdgtest password=oracle
?
注：為防止密碼問題導致無法訪問，最好直接把主庫的orapw文件拷貝過來然后更名orapwSID就可以了
?
?
?
9、確認主庫和備庫都啟動監(jiān)聽：lsnrctl start
?
?
?
10、主庫設置為歸檔模式
?
alter database archivelog
?
設置主數(shù)據(jù)庫為日志強制寫狀態(tài)
?
alter database force logging;
?
查看狀態(tài)日志強制寫狀態(tài)為YES
?
select log_mode,force_logging from v$database;
LOG_MODE???? FOR
------------ ---
ARCHIVELOG?? YES
?
?
?
11、查看主庫數(shù)據(jù)庫的日志組個數(shù)與大小，因為我們創(chuàng)建standby日志組的個數(shù)是原日志組個數(shù)+1再與thread的積，size不能小于原日志文件的大小。
?
SQL> select group#,THREAD#,bytes/1024/1024 from v$log;
?
??? GROUP#??? THREAD# BYTES/1024/1024
---------- ---------- ---------------
?1??????????? 1????????????????? 200
?2??????????? 1????????????????? 200
?3??????????? 1????????????????? 200
?
SQL> select member from v$logfile;
?
MEMBER
--------------------------------------
/u01/app/oradata/DT/redo03.log
/u01/app/oradata/DT/redo02.log
/u01/app/oradata/DT/redo01.log
?
?
?
12、創(chuàng)建standby日志組，個數(shù)是原日志組個數(shù)+1再與實例數(shù)的積，size不能小于原日志文件的大小
注：RAC環(huán)境注意實例有幾個，新建standby日志組的路徑可與原日志組相同。
?
SQL> alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m;
Database altered.
?
alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby02.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby03.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby04.log' size 200m;
?
創(chuàng)建完成后查詢是否成功
SQL> select group#,status,type,member from v$logfile;
?
??? GROUP# STATUS? TYPE??? MEMBER
---------- ------- ------- --------------------------------------------------
?3?????????? ONLINE? /u01/app/oradata/DT/redo03.log
?2?????????? ONLINE? /u01/app/oradata/DT/redo02.log
?1?????????? ONLINE? /u01/app/oradata/DT/redo01.log
?4?????????? STANDBY /u01/app/oradata/DT/standby01.log
?5?????????? STANDBY /u01/app/oradata/DT/standby02.log
?6?????????? STANDBY /u01/app/oradata/DT/standby03.log
?7?????????? STANDBY /u01/app/oradata/DT/standby04.log
?
7 rows selected.
?
?
?
13、啟動備庫到NOMOUNT
$ sqlplus / as sysdba
?
SQL> startup nomount pfile='/home/oracle/backup/INITtestdb.ORA';
注：如果pfile文件放在默認路徑，且文件名正確的話。不需要指定pfile路徑，直接startup nomount即可。
?
?
?
14、duplicate開始
在主庫上通過rman進行復制備庫(注意在這一步之前必須退出備庫的所有連接,否則會報錯)
rman target sys/oracle auxiliary sys/oracle@dgtest
?
rman> duplicate target database for standby nofilenamecheck from active database;
?
經(jīng)資料查詢，發(fā)現(xiàn)在duplicate傳輸時，windows下全部是大寫字符，所以，還得修改
DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT參數(shù)的路徑，全用大寫
?
*************************錯誤信息*************************
19c測試時由于備庫的環(huán)境變量ORACLE_HOME配置有誤，導致始終無法連接到備庫，報錯如下：
?
[oracle@test19c admin]$ rman target sys/oracle auxiliary sys/oracle@dgtest
?
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 17 18:13:10 2019
Version 19.2.0.0.0
?
Copyright (c) 1982, 2019, Oracle and/or its affiliates.? All rights reserved.
?
connected to target database: DT (DBID=1254913786)
connected to auxiliary database (not started)
?
RMAN> exit
?
檢查發(fā)現(xiàn)在.bash_profile文件中，ORACLE_BASE的路徑最后加了一個/，導致在ORACLE_HOME中多了一個/
ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE?? ---/u01/app/oracle不該多那個/啊。。。
ORACLE_HOME=$ORACLE_BASE/product/19.2.0/dbhome_1; export ORACLE_HOME
?
最終使用echo $ORACLE_HOME時看到的路徑如下：
/u01/app/oracle//product/19.2.0/dbhome_1
但是最無奈的是你輸入cd $ORACLE_HOME的時候卻可以進入正確的路徑，然后pwd會顯示：/u01/app/oracle/product/19.2.0/dbhome_1
所以之前檢查時，始終認為環(huán)境變量沒有問題。最終將ORACLE_BASE修改為/u01/app/oracle 然后重啟監(jiān)聽，重啟備庫就好了
總結：還是使用echo來檢查各種環(huán)境變量，不要直接cd進去檢查。
------分割線------
注意db_recovery_file_dest_size的大小，上次部署12c生產(chǎn)環(huán)境就是大小有問題，修改后解決：
SQL> alter system set db_recovery_file_dest_size=60G;
***************************End***************************
?
?
?
15、open備庫，并恢復到自動恢復狀態(tài)
確認備庫狀態(tài)：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
?
把備庫啟動到open only下面：
SQL> alter database open read only;
?
在備庫上啟動數(shù)據(jù)庫到恢復管理模式，并開始準備從主庫接受歸檔日志的傳輸：
SQL> alter database recover managed standby database using current logfile disconnect from session;
?
?
?
*************************錯誤信息*************************
備庫啟動報錯：
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oradata/dgtest/system01.dbf'
?
在主備庫上查看alert日志，發(fā)現(xiàn)備庫alert日志有如下信息：
PR00 (PID:7481): Please verify that primary database is transporting redo logs to the standby database
根據(jù)信息檢查，發(fā)現(xiàn)redo根本沒過來，因為在備庫歸檔目錄沒有看到一個歸檔文件?。。?/span>
目前定位就是無法傳輸歸檔，于是在主備庫檢查歸檔是否有報錯：
select status,error from v$archive_dest;
?
在主庫發(fā)現(xiàn)報錯：
SQL> select status,error from v$archive_dest;
?
STATUS????????? ERROR
--------- -----------------------------------------------------------------
VALID
ERROR????????? ORA-16086: Redo data cannot be written to the standby redo log
?
這種報錯一般也是和配置文件相關，網(wǎng)上也有相關文檔和排錯思路。
經(jīng)過多次測試發(fā)現(xiàn)是和db_unique_name參數(shù)沒有配置有關：
檢查發(fā)現(xiàn)備庫的db_unique_name在沒有配置的情況下默認也叫DT，和主庫一致。
這就導致了主備庫因為db_unique_name一致而無法區(qū)分，進而導致redo無法傳輸，也就沒有歸檔了。
SQL> show parameter name
?
NAME???????????????????????????????????????? TYPE???????????? VALUE
---------------------------- ----------- ------------------------------
cdb_cluster_name???????????????????? string
cell_offloadgroup_name? ? ? ? ? ? ? ?string
db_file_name_convert???????????????? string????????????? /u01/app/oradata/DT, /u01/app/oradata/dgtest
db_name? ? ? ? ? ? ? ? ? ? ? ? ? ? ? string????????????? DT
db_unique_name? ? ? ? ? ? ? ? ? ? ? ?string????????????? DT
global_names? ? ? ? ? ? ? ? ? ? ? ? ?boolean? ? ? ? ? ? ?FALSE
instance_name? ? ? ? ? ? ? ? ? ? ? ? string????????????? dgtest
lock_name_space ???????????????????? string
log_file_name_convert? ? ? ? ? ? ? ? string????????????? /u01/app/oradata/DT, /u01/app/oradata/dgtest
pdb_file_name_convert? ? ? ? ? ? ? ? string
processor_group_name???????????????? string
service_names? ? ? ? ? ? ? ? ? ? ? ? string????????????? DT
SQL>
上面的信息可以看到instance_name是dgtest，但是db_unique_name還是DT，所以導致redo無法傳輸。
最后在備庫上修改pfile文件，加上db_unique_name的配置就解決問題了。
最好是主備庫都在pfile中定義一下db_unique_name!
本次部署環(huán)境，19c主庫安裝好后，生成的pfile沒有定義db_unique_name，所以也就忽略了這個問題。
?
-------分割線，下面步驟操作有問題，應該先open庫----------
回到原主庫啟動STANDBY開始接收并恢復主庫的日志
alter database recover managed standby database using current logfile disconnect from session;
在mount狀態(tài)下輸入上面語句，導致數(shù)據(jù)庫無法open,所以需要退出這個狀態(tài)
alter database recover managed standby database cancel;
?
***************************End***************************
?
?
?
16、主庫檢查LNS進程：
select process,status from v$managed_standby;
查看進程，看有沒有LNS進程，如果沒有則需要檢查DG環(huán)境
?
SQL> select process,status from v$managed_standby;
?
PROCESS?? STATUS
--------- ------------
DGRD????????? ALLOCATED
ARCH????????? CLOSING
DGRD????????? ALLOCATED
ARCH????????? OPENING
ARCH????????? CONNECTED
ARCH????????? CONNECTED
LNS????????? OPENING
DGRD????????? ALLOCATED
LNS????????? WRITING
?
9 rows selected.
?
?
?
17、檢查主備庫角色和其他信息
在備庫端查看其角色是否已經(jīng)是physical standby,檢查當前備庫的模式PROTECTION_MODE
SQL> select DATABASE_ROLE,protection_mode,open_mode from v$database;
?
DATABASE_ROLE???????? PROTECTION_MODE????? OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE? READ ONLY WITH APPLY
?
在主庫查看其角色
SQL> select DATABASE_ROLE,open_mode from gv$database;
?
DATABASE_ROLE???????? OPEN_MODE
---------------- --------------------
PRIMARY ???????????? READ WRITE
?
在備庫查看data guard為哪種日志接受方式
SQL> select process,client_process,sequence#,status from v$managed_standby;
?
PROCESS?? CLIENT_P? SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH????????? ARCH? ? ? ? ? ? ? ? 0 CONNECTED
DGRD????????? N/A? ? ? ? ? ? ? ? ?0 ALLOCATED
DGRD????????? N/A? ? ? ? ? ? ? ? ?0 ALLOCATED
ARCH????????? ARCH? ? ? ? ? ? ? ? 0 CONNECTED
ARCH????????? ARCH? ? ? ? ? ? ? ? 0 CONNECTED
ARCH????????? ARCH? ? ? ? ? ? ? ? 0 CONNECTED
RFS? ? ? ? ? ?Archival? ? ? ? ? ? 0 IDLE
RFS? ? ? ? ? ?LGWR? ? ? ? ? ? ? ?31 IDLE
RFS? ? ? ? ? ?UNKNOWN? ? ? ? ? ? ?0 IDLE
MRP0????????? N/A? ? ? ? ? ? ? ? 31 APPLYING_LOG
?
10 rows selected.
?
?
?
?
18、檢查主備庫sequence#
select max(sequence#) from v$archived_log;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where??archived='YES' order by sequence# desc;
?
主庫：
SQL> select max(sequence#) from v$archived_log;
?
MAX(SEQUENCE#)
--------------
??? 30
?
備庫
SQL> select max(sequence#) from v$archived_log;
?
MAX(SEQUENCE#)
--------------
??? 30
?
?
?
19、測試日志隊列是否能正常傳輸
在備庫查看日志的隊列情況
SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
?
?SEQUENCE# APPLIED?? FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
30 YES???????????? 18-JUN-19 18-JUN-19
?
在主庫進行強制歸檔
ALTER SYSTEM ARCHIVE LOG CURRENT;
alter system switch logfile;
?
在備庫查看日志的隊列情況,看新的歸檔日志有沒有正常傳輸過來
SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
?
?SEQUENCE# APPLIED?? FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
30 YES???????????? 18-JUN-19 18-JUN-19
31 NO???????????? 18-JUN-19 18-JUN-19
32 IN-MEMORY 18-JUN-19 18-JUN-19
?
檢查下兩邊的日志同步情況
select sequence# from v$archived_log where applied='YES';
?
看看有沒有寫的redo log:
select sequence#,applied from v$archived_log;
?
?
?
20、查看DG是否正常工作，這一步主要看歸檔有無報錯
select dest_id,error,status from v$archive_dest where status='ERROR';
SQL> select dest_id,error,status from v$archive_dest where status='ERROR';
?
no rows selected
也可以直接查看所有歸檔目錄信息：
select dest_id,error,status from v$archive_dest
?
?
?
21、在主庫新建表空間、用戶、表，并插入數(shù)據(jù)來測試備庫是否能及時同步數(shù)據(jù)
主庫創(chuàng)建測試表空間：
SQL> create tablespace test datafile '/u01/app/oradata/DT/dt01.dbf' size 50m;
?
主備庫查看數(shù)據(jù)文件狀態(tài)
select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
主庫：
SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
?
?FILE#? CREATION_ STATUS? NAME???????????????????????????????????????????????????????????????? BYTES
------- --------- ------- ------------------------------------------ ----------
1 ????????04-FEB-19 SYSTEM? /u01/app/oradata/DT/system01.dbf???????????????????????? 943718400
3 ????????04-FEB-19 ONLINE? /u01/app/oradata/DT/sysaux01.dbf???????????????????????? 555745280
4 ????????04-FEB-19 ONLINE? /u01/app/oradata/DT/undotbs01.dbf????????????????????????? 68157440
5 ????????18-JUN-19 ONLINE? /u01/app/oradata/DT/dt01.dbf????????????????????????????? 52428800
7 ????????04-FEB-19 ONLINE? /u01/app/oradata/DT/users01.dbf?????????????????????????? 5242880
?
備庫：
SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
?
FILE#?? CREATION_ STATUS? NAME???????????????????????????????????????????????????????????????? BYTES
------- --------- ------- ------------------------------------------ ----------
1 ????????04-FEB-19 SYSTEM? /u01/app/oradata/dgtest/system01.dbf???????????????? 943718400
3 ????????04-FEB-19 ONLINE? /u01/app/oradata/dgtest/sysaux01.dbf???????????????? 555745280
4 ????????04-FEB-19 ONLINE? /u01/app/oradata/dgtest/undotbs01.dbf????????????????? 68157440
5 ????????18-JUN-19 ONLINE? /u01/app/oradata/dgtest/dt01.dbf????????????????????????? 52428800
7 ????????04-FEB-19 ONLINE? /u01/app/oradata/dgtest/users01.dbf?????????????????? 5242880
?
主庫操作：
1> 創(chuàng)建用戶
create user dgtest default tablespace test identified by oracle;
grant dba to dgtest;
?
2> 切換用戶
sqlplus dgtest/oracle
?
3> dgtest用戶下創(chuàng)建表和插入測試數(shù)據(jù)
--創(chuàng)建表
create table dgtest (
??? id number(9) not null primary key,
??? classname varchar2(40) not null
??? );
?
--插入數(shù)據(jù)
insert into dgtest values(28,'class one');
insert into dgtest values(29,'detest one');
commit;
?
?
在備庫執(zhí)行查詢：
select * from dgtest.dgtest;
SQL> select * from dgtest.dgtest;
?
ID CLASSNAME
------ ----------------------------------------
28 class one
29 detest one
?
刪除測試數(shù)據(jù)：
drop tablespace test including contents and datafiles;
drop user dgtest cascade;
?
到此DG環(huán)境部署完畢，數(shù)據(jù)可以正常同步。
?
?
--------------------------------------------------------------
------------------------主備庫切換測試------------------------
--------------------------------------------------------------
#切換的兩種方式switchover和failover
switchover 切換：主庫與備庫數(shù)據(jù)同步正常情況下的切換，主要用于主備維護、切換演練等；
failover 切換：主庫與備庫在數(shù)據(jù)未同步情況下的強制切換，主要用于主庫宕機、故障情況下切換；
?
一、switchover
Oracle 物理DG切換
在進行DATA GUARD的物理STANDBY切換前需要注意：
確認主庫和備庫間網(wǎng)絡連接通暢；
確認沒有活動的會話連接在數(shù)據(jù)庫中；
確保STANDBY數(shù)據(jù)庫處于ARCHIVELOG模式；
如果設置了REDO應用的延遲，那么將這個設置去掉；
確保配置了主庫和備庫的初始化參數(shù)，使得切換完成后，DATA GUARD機制可以順利的運行。
?
#準備工作：
確認當前主庫只有當前會話連接:
set pages 100 linesize 1000;
select SWITCHOVER_STATUS from v$database;
SQL> select SWITCHOVER_STATUS from v$database;
?
SWITCHOVER_STATUS
--------------------
TO STANDBY
?
select count(*) from v$session where username is not null;
SQL> select count(*) from v$session where username is not null;
?
? COUNT(*)
----------
?2
若有多于一個會話則：
select sid,serial# from v$session where username is not null;
SQL> select sid,serial# from v$session where username is not null;
?
? SID SERIAL#
----- ----------
2 10746
? 621 13864
?
select userenv('sid') from dual;
SQL> select userenv('sid') from dual;
?
USERENV('SID')
--------------
?? 621
則執(zhí)行下面語句殺掉：alter system kill session 'sid,serial#';
SQL> alter system kill session '621,13864';
alter system kill session '621,13864'
*
ERROR at line 1:
ORA-00027: cannot kill current session
?
原來621是當前會話，可以不用理會。
?
?
#正式切換：
主庫與備庫數(shù)據(jù)同步，且正常運行，將主庫與備庫的角色互換，也可以互換回來。
切換前，務必檢查當前主庫與備庫的歸檔是否是同步的，確認同步后再執(zhí)行切換
?
主庫切備庫
1、查看庫的角色
主庫：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
CURRENT READ WRITE???????????? PRIMARY????????????? TO STANDBY
?
SWITCHOVER_STATUS為TO STANDBY，說明主庫可以切換為備庫
?
備庫：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
STANDBY READ ONLY???????????? PHYSICAL STANDBY NOT ALLOWED
?
SWITCHOVER_STATUS為NOT ALLOWED，這是備庫的正常狀態(tài)。
在主庫首先切換后，我們再查詢備庫狀態(tài)會發(fā)現(xiàn)SWITCHOVER_STATUS有改變
?
?
2、對主庫進行切換
SQL> alter database commit to switchover to physical standby with session shutdown;
此時主庫已經(jīng)開始切換到備庫的過程了，而備庫的SWITCHOVER_STATUS已經(jīng)變成TO PRIMARY
?
3、此時主庫已經(jīng)關閉，打到read only或同步狀態(tài)
SQL> startup mount;
ORACLE instance started.
?
Total System Global Area? 905967800 bytes
Fixed Size??????????????????? 8902840 bytes
Variable Size????????????????? 788529152 bytes
Database Buffers????????? 100663296 bytes
Redo Buffers??????????????????? 7872512 bytes
Database mounted.
SQL> alter database open read only;
?
Database altered.
?
4、再查主庫的角色，確認已經(jīng)切換為備庫
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
STANDBY READ ONLY???????????? PHYSICAL STANDBY TO PRIMARY
?
注：這一步操作完成后，此時主備庫都是open read only狀態(tài)，且SWITCHOVER_STATUS都為TO PRIMARY。
此時已經(jīng)沒有真正主庫存在的了，主庫已經(jīng)切換為備庫，備庫還未切換成主庫。
也就是說此時主備庫都能切換為真正的主庫
?
5、在備庫上啟動數(shù)據(jù)庫到恢復管理模式，并開始準備從主庫接受歸檔日志的傳輸。
SQL> alter database recover managed standby database using current logfile disconnect from session;
注：這一步不執(zhí)行的話，OPEN_MODE狀態(tài)始終是READ ONLY，無法變成正常的READ ONLY WITH APPLY
?
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
查資料顯示，12c之后使用上面這個語句來APPLY，黃色部分為和11gR2的區(qū)別
?
6、再查主庫的角色，確認已經(jīng)切換為備庫，且OPEN_MODE為READ ONLY WITH APPLY
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- -------------------- ---------------- --------------------
STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
?
?
備庫切主庫
1、查看備庫角色：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- -------------------- ---------------- --------------------
STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
?
SWITCHOVER_STATUS為TO PRIMARY，說明備庫可以切換為主庫
?
2、備切主命令
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
?
3、確認備庫已經(jīng)是mount狀態(tài)
SQL> select status,instance_name from v$instance;
?
STATUS???????????? INSTANCE_NAME
------------ ----------------
MOUNTED????? dgtest
?
4、打到OPEN狀態(tài)
SQL> ALTER DATABASE OPEN;
現(xiàn)在的主庫切換幾次日志文件，備庫可以同步日志文件。
注：只有在備庫OPEN完成切換，成為新主庫之后，原主庫的SWITCHOVER_STATUS狀態(tài)才會由TO PRIMARY變?yōu)?/span>NOT ALLOWED
?
5、再查備庫的角色，確認已經(jīng)切換為主庫
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
?
CONTROL OPEN_MODE???????????? DATABASE_ROLE??? SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
CURRENT READ WRITE???????????? PRIMARY????????????? TO STANDBY
?
--------------------------------------------------------------
-----到此，主備庫切換完畢，standby to primary過程已經(jīng)完成-----
--------------------------------------------------------------
?
?
二、failover災難切換
主庫宕機，無法啟動，緊急啟用備庫。直接在備庫上操作，將備庫轉換為主庫角色
備庫上執(zhí)行下面四條命令即可：
SQL > alter database recover managed standby database finish;
SQL > alter database commit to switchover to primary;
SQL > shutdown immediate;
SQL > startup;
?
注：此操作不可逆，ARCH從1開始計數(shù)，要重新做全備和部署DG
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內(nèi)容。
