19c ADG部署

因客戶需要部署19c DG環境，以前一直是11g和12c部署，故測試下19c部署

1、DG基礎環境

?

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

?

參數*log_archive_config='dg_config(pri,std)'，以確保主備庫數據庫能夠互相識別對方。

查看歸檔是否有報錯

select status,error from v$archive_dest;

?

?

?

2、修改主庫配置文件initTESTDB.ora

這里現在數據庫里修改相關的參數，與DG的參數就只與幾個參數相關，大概就是日志，文件的位置的轉換，GAP的處理，其實GAP已經會自動的處理，不過這里我們還是介紹配置FAL_SERVER,FAL_CLIENT參數。在修改完之后重新創建了pfile文件

先創建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參數必須重啟數據庫生效

下面是修改好之后重新生成的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端都是按照大寫路徑來傳輸的！

如果用小寫或者大小寫混合，則無法識別路徑，會有問題！

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、確認主庫和備庫都啟動監聽：lsnrctl start

?

?

?

10、主庫設置為歸檔模式

?

alter database archivelog

?

設置主數據庫為日志強制寫狀態

?

alter database force logging;

?

查看狀態日志強制寫狀態為YES

?

select log_mode,force_logging from v$database;

LOG_MODE???? FOR

------------ ---

ARCHIVELOG?? YES

?

?

?

11、查看主庫數據庫的日志組個數與大小，因為我們創建standby日志組的個數是原日志組個數+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、創建standby日志組，個數是原日志組個數+1再與實例數的積，size不能小于原日志文件的大小

注：RAC環境注意實例有幾個，新建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;

?

創建完成后查詢是否成功

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;

?

經資料查詢，發現在duplicate傳輸時，windows下全部是大寫字符，所以，還得修改

DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT參數的路徑，全用大寫

?

*************************錯誤信息*************************

19c測試時由于備庫的環境變量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

?

檢查發現在.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

所以之前檢查時，始終認為環境變量沒有問題。最終將ORACLE_BASE修改為/u01/app/oracle 然后重啟監聽，重啟備庫就好了

總結：還是使用echo來檢查各種環境變量，不要直接cd進去檢查。

------分割線------

注意db_recovery_file_dest_size的大小，上次部署12c生產環境就是大小有問題，修改后解決：

SQL> alter system set db_recovery_file_dest_size=60G;

***************************End***************************

?

?

?

15、open備庫，并恢復到自動恢復狀態

確認備庫狀態：

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

?

把備庫啟動到open only下面：

SQL> alter database open read only;

?

在備庫上啟動數據庫到恢復管理模式，并開始準備從主庫接受歸檔日志的傳輸：

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日志，發現備庫alert日志有如下信息：

PR00 (PID:7481): Please verify that primary database is transporting redo logs to the standby database

根據信息檢查，發現redo根本沒過來，因為在備庫歸檔目錄沒有看到一個歸檔文件！！！

目前定位就是無法傳輸歸檔，于是在主備庫檢查歸檔是否有報錯：

select status,error from v$archive_dest;

?

在主庫發現報錯：

SQL> select status,error from v$archive_dest;

?

STATUS????????? ERROR

--------- -----------------------------------------------------------------

VALID

ERROR????????? ORA-16086: Redo data cannot be written to the standby redo log

?

這種報錯一般也是和配置文件相關，網上也有相關文檔和排錯思路。

經過多次測試發現是和db_unique_name參數沒有配置有關：

檢查發現備庫的db_unique_name在沒有配置的情況下默認也叫DT，和主庫一致。

這就導致了主備庫因為db_unique_name一致而無法區分，進而導致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!

本次部署環境，19c主庫安裝好后，生成的pfile沒有定義db_unique_name，所以也就忽略了這個問題。

?

-------分割線，下面步驟操作有問題，應該先open庫----------

回到原主庫啟動STANDBY開始接收并恢復主庫的日志

alter database recover managed standby database using current logfile disconnect from session;

在mount狀態下輸入上面語句，導致數據庫無法open,所以需要退出這個狀態

alter database recover managed standby database cancel;

?

***************************End***************************

?

?

?

16、主庫檢查LNS進程：

select process,status from v$managed_standby;

查看進程，看有沒有LNS進程，如果沒有則需要檢查DG環境

?

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、檢查主備庫角色和其他信息

在備庫端查看其角色是否已經是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、在主庫新建表空間、用戶、表，并插入數據來測試備庫是否能及時同步數據

主庫創建測試表空間：

SQL> create tablespace test datafile '/u01/app/oradata/DT/dt01.dbf' size 50m;

?

主備庫查看數據文件狀態

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> 創建用戶

create user dgtest default tablespace test identified by oracle;

grant dba to dgtest;

?

2> 切換用戶

sqlplus dgtest/oracle

?

3> dgtest用戶下創建表和插入測試數據

--創建表

create table dgtest (

??? id number(9) not null primary key,

??? classname varchar2(40) not null

??? );

?

--插入數據

insert into dgtest values(28,'class one');

insert into dgtest values(29,'detest one');

commit;

?

?

在備庫執行查詢：

select * from dgtest.dgtest;

SQL> select * from dgtest.dgtest;

?

ID CLASSNAME

------ ----------------------------------------

28 class one

29 detest one

?

刪除測試數據：

drop tablespace test including contents and datafiles;

drop user dgtest cascade;

?

到此DG環境部署完畢，數據可以正常同步。

?

?

--------------------------------------------------------------

------------------------主備庫切換測試------------------------

--------------------------------------------------------------

#切換的兩種方式switchover和failover

switchover 切換：主庫與備庫數據同步正常情況下的切換，主要用于主備維護、切換演練等；

failover 切換：主庫與備庫在數據未同步情況下的強制切換，主要用于主庫宕機、故障情況下切換；

?

一、switchover

Oracle 物理DG切換

在進行DATA GUARD的物理STANDBY切換前需要注意：

確認主庫和備庫間網絡連接通暢；

確認沒有活動的會話連接在數據庫中；

確保STANDBY數據庫處于ARCHIVELOG模式；

如果設置了REDO應用的延遲，那么將這個設置去掉；

確保配置了主庫和備庫的初始化參數，使得切換完成后，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

則執行下面語句殺掉：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是當前會話，可以不用理會。

?

?

#正式切換：

主庫與備庫數據同步，且正常運行，將主庫與備庫的角色互換，也可以互換回來。

切換前，務必檢查當前主庫與備庫的歸檔是否是同步的，確認同步后再執行切換

?

主庫切備庫

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，這是備庫的正常狀態。

在主庫首先切換后，我們再查詢備庫狀態會發現SWITCHOVER_STATUS有改變

?

?

2、對主庫進行切換

SQL> alter database commit to switchover to physical standby with session shutdown;

此時主庫已經開始切換到備庫的過程了，而備庫的SWITCHOVER_STATUS已經變成TO PRIMARY

?

3、此時主庫已經關閉，打到read only或同步狀態

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、再查主庫的角色，確認已經切換為備庫

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狀態，且SWITCHOVER_STATUS都為TO PRIMARY。

此時已經沒有真正主庫存在的了，主庫已經切換為備庫，備庫還未切換成主庫。

也就是說此時主備庫都能切換為真正的主庫

?

5、在備庫上啟動數據庫到恢復管理模式，并開始準備從主庫接受歸檔日志的傳輸。

SQL> alter database recover managed standby database using current logfile disconnect from session;

注：這一步不執行的話，OPEN_MODE狀態始終是READ ONLY，無法變成正常的READ ONLY WITH APPLY

?

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

查資料顯示，12c之后使用上面這個語句來APPLY，黃色部分為和11gR2的區別

?

6、再查主庫的角色，確認已經切換為備庫，且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、確認備庫已經是mount狀態

SQL> select status,instance_name from v$instance;

?

STATUS???????????? INSTANCE_NAME

------------ ----------------

MOUNTED????? dgtest

?

4、打到OPEN狀態

SQL> ALTER DATABASE OPEN;

現在的主庫切換幾次日志文件，備庫可以同步日志文件。

注：只有在備庫OPEN完成切換，成為新主庫之后，原主庫的SWITCHOVER_STATUS狀態才會由TO PRIMARY變為NOT ALLOWED

?

5、再查備庫的角色，確認已經切換為主庫

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過程已經完成-----

--------------------------------------------------------------

?

?

二、failover災難切換

主庫宕機，無法啟動，緊急啟用備庫。直接在備庫上操作，將備庫轉換為主庫角色

備庫上執行下面四條命令即可：

SQL > alter database recover managed standby database finish;

SQL > alter database commit to switchover to primary;

SQL > shutdown immediate;

SQL > startup;

?

注：此操作不可逆，ARCH從1開始計數，要重新做全備和部署DG