17.建立DG

17.1 環境準備

使用前面建立的資料庫作為primary端，確認其處於open，歸檔及forcelogging狀態。

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,log_mode,force_logging from gv$database;

NAME OPEN_MODE LOG_MODE FOR

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

STONE READ WRITE ARCHIVELOG YES

前面只建立了2個虛擬機器，這裡重新再建立一個或者克隆一個虛擬機器，配置好網路，並安裝相同版本的資料庫（11.2.0.4）作為Standby端，不要建立資料庫。

17.2 Oracle網路配置

17.2.1 Primary端監聽配置

採用活動資料庫複製的方法搭建DG，需要配置靜態監聽。在RAC1節點使用grid使用者配置靜態監聽。

修改前的狀態：

[grid@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:36:06

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 07-APR-2016 14:34:24

Uptime 0 days 2 hr. 1 min. 41 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.131)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.133)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "stone" has 1 instance(s).

Instance "stone1", status READY, has 1 handler(s) for this service...

Service "stoneXDB" has 1 instance(s).

Instance "stone1", status READY, has 1 handler(s) for this service...

The command completed successfully

使用netmgr圖形化工具進行修改，結果如下：

[grid@rac1 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stone)

(ORACLE_HOME = /u01/app/11.2.0/grid)

(SID_NAME = stone1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))

)

ADR_BASE_LISTENER_SCAN1 = /u01/app/grid

重啟監聽後，如下：

[grid@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:42:51

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 07-APR-2016 16:41:18

Uptime 0 days 0 hr. 1 min. 33 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.131)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.133)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "stone" has 2 instance(s).

Instance "stone1", status UNKNOWN, has 1 handler(s) for this service...

Instance "stone1", status READY, has 1 handler(s) for this service...

Service "stoneXDB" has 1 instance(s).

Instance "stone1", status READY, has 1 handler(s) for this service...

The command completed successfully

17.2.2 Standby端監聽配置

同樣使用netmgr配置靜態監聽，結果如下：

[oracle@dg1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stone)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = stonedg1)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@dg1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 16:54:35

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.247.136)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 07-APR-2016 16:53:34

Uptime 0 days 0 hr. 1 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.136)(PORT=1521)))

Services Summary...

Service "stone" has 1 instance(s).

Instance "stonedg1", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

17.2.3 Primary端網路服務命名配置

在2個節點使用oracle使用者，使用netmgr配置到Standby端的網路服務命名。結果如下：

[oracle@rac1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stone)

)

STANDBY136 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = stone)

)

[oracle@rac2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

STONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stone)

)

STANDBY136 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = stone)

)

17.2.4 Standby端網路服務命名配置

同樣使用netmgr配置服務命名，結果如下：

[oracle@dg1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY131 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = stone)

)

17.2.5 啟動監聽並進行測試

測試primary到standby的網路：

[oracle@rac1 ~]$ tnsping standby136

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 17:11:01

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.136)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stone)))

OK (50 msec)

測試standby到primary的網路：

[oracle@dg1 ~]$ tnsping primary131

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-APR-2016 17:11:37

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.131)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stone)))

OK (50 msec)

17.3 引數配置

17.3.1 primary端引數配置

修改相關引數：

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(stone,stonedg1)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby136 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stonedg1';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

根據spfile.ora生成pfile.ora：

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

17.3.2 standby端引數配置

將primary生成的pfile複製過來：

[oracle@dg1 ~]$ scp oracle@192.168.247.131:/home/oracle/pfile.ora .

reverse mapping checking getaddrinfo for bogon [192.168.247.131] failed - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.247.131's password:

pfile.ora 100% 1529 1.5KB/s 00:00

進行修改後內容如下：（此處注意：如果主庫是使用OMF管理檔案，則standby端的目錄路徑可以不必和primary端一致，也無需設定DB_FILE_NAME_CONVERT，系統會自動進行轉換；如果不是使用OMF管理檔案，則standby端的目錄路徑需要和primary端保持一致，否則就需要使用DB_FILE_NAME_CONVERT進行轉換。）

[oracle@dg1 ~]$ cat pfile.ora

stonedg1.__db_cache_size=230686720

stonedg1.__java_pool_size=4194304

stonedg1.__large_pool_size=8388608

stonedg1.__pga_aggregate_target=390070272

stonedg1.__sga_target=444596224

stonedg1.__shared_io_pool_size=0

stonedg1.__shared_pool_size=192937984

stonedg1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/stonedg1/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl','/u01/app/oracle/flash_recovery_area/stonedg1/controlfile/control2.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='stone'

*.db_unique_name='stonedg1'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=8487174144

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stoneXDB)'

*.log_archive_config='DG_CONFIG=(stone,stonedg1)'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/stonedg1/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=stonedg1'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=834666496

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.standby_file_management='auto'

*.fal_server='primary131'

*.undo_tablespace='UNDOTBS1'

根據修改後的pfile.ora生成spfilestonedg1.ora。

[oracle@dg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 7 18:01:17 2016

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/pfile.ora';

File created.

17.4 系統設定

17.4.1 standby端目錄建立

根據引數檔案建立相關目錄。

mkdir -p /u01/app/oracle/admin/stonedg1/{adump,bdump,cdump,udump}

mkdir -p /u01/app/oracle/oradata/stonedg1/{controlfile,datafile,onlinelog,archivelog}

mkdir -p /u01/app/oracle/flash_recovery_area/stonedg1/{controlfile,onlinelog,archivelog}

17.4.2 standby端密碼檔案

將primary端的密碼檔案複製到standby並重新命名為orapwstonedg1.ora。

[oracle@dg1 ~]$ scp oracle@192.168.247.131:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

oracle@192.168.247.131's password:

orapwstone1 100% 1536 1.5KB/s 00:00

[oracle@dg1 ~]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstonedg1

17.5 建立standby資料庫

將standby資料庫啟動到nomount狀態：

[oracle@dg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 7 18:45:09 2016

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 578817144 bytes

Database Buffers 247463936 bytes

Redo Buffers

在primary RAC1節點執行如下命令:

[oracle@rac1 ~]$ rman target / auxiliary sys/123456@standby136

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 7 18:56:14 2016

connected to target database: STONE (DBID=3018705892)

connected to auxiliary database: STONE (not mounted)

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 07-APR-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone1' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstonedg1' ;

}

executing Memory Script

Starting backup at 07-APR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=54 instance=stone1 device type=DISK

Finished backup at 07-APR-16

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl';

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/stonedg1/controlfile/control2.ctl' from

'/u01/app/oracle/oradata/stonedg1/controlfile/control1.ctl';

}

executing Memory Script

Starting backup at 07-APR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_stone1.f tag=TAG20160407T185627 RECID=3 STAMP=908564191

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 07-APR-16

Starting restore at 07-APR-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 07-APR-16

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

switch clone tempfile all;

set newname for clone datafile 1 to new;

set newname for clone datafile 2 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 6 to new;

backup as copy reuse

datafile 1 auxiliary format new

datafile 2 auxiliary format new

datafile 3 auxiliary format new

datafile 4 auxiliary format new

datafile 5 auxiliary format new

datafile 6 auxiliary format new

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_temp_%u_.tmp in control file

executing command: SET NEWNAME

Starting backup at 07-APR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/stone/datafile/system.256.908479415

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_system_03r2f5n9_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/stone/datafile/sysaux.257.908479417

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_sysaux_04r2f5oc_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/stone/datafile/example.264.908479575

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_example_05r2f5p5_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/stone/datafile/undotbs1.258.908479417

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs1_06r2f5pl_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+DATA/stone/datafile/undotbs2.265.908479933

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs2_07r2f5ps_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/stone/datafile/users.259.908479417

output file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_users_08r2f5pv_.dbf tag=TAG20160407T185641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 07-APR-16

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_system_03r2f5n9_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_sysaux_04r2f5oc_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs1_06r2f5pl_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_users_08r2f5pv_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_example_05r2f5p5_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=8 STAMP=908564296 file name=/u01/app/oracle/oradata/STONEDG1/datafile/o1_mf_undotbs2_07r2f5ps_.dbf

Finished Duplicate Db at 07-APR-16

17.6 standby端啟動redo實時應用

17.6.1 檢視standby端恢復模式

SQL> select recovery_mode from v$archive_dest_status where dest_id=1;

RECOVERY_MODE

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

IDLE

Standby端沒有啟動redo應用前，恢復模式為IDLE。

17.6.2 在standby端啟動redo應用

SQL> alter database recover managed standby database disconnect from session;

資料庫已更改。

SQL> select recovery_mode from v$archive_dest_status where dest_id=1;

RECOVERY_MODE

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

MANAGED

Standby端啟動redo應用後，恢復模式為MANAGED。

SQL> alter database recover managed standby database cancel;

資料庫已更改。

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

alter database recover managed standby database using current logfile disconnect

from session

*

ERROR at line 1:

ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

由於沒有standby redologs，故使用standby redologs日誌啟動redo實時應用失敗。

17.6.3 在standby端建立standby redologs

日誌組數量6組，比兩個節點加起來的online redologs數量多2組。

SQL> alter database add standby logfile group 11 size 50m;

Database altered.

SQL> alter database add standby logfile group 12 size 50m;

Database altered.

SQL> alter database add standby logfile group 13 size 50m;

Database altered.

SQL> alter database add standby logfile group 14 size 50m;

Database altered.

SQL> alter database add standby logfile group 15 size 50m;

Database altered.

SQL> alter database add standby logfile group 16 size 50m;

Database altered.

17.6.4 在standby端啟動redo實時應用

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

Database altered.

SQL> select recovery_mode from v$archive_dest_status where dest_id=1;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

17.6.5 啟用standby redolog

在standby端查詢standby redologs狀態顯示未使用。

SQL> select group#, sequence#, dbid,status from v$standby_log;

GROUP# SEQUENCE# DBID STATUS

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

4 0 UNASSIGNED UNASSIGNED

5 0 UNASSIGNED UNASSIGNED

6 0 UNASSIGNED UNASSIGNED

7 0 UNASSIGNED UNASSIGNED

在primary端切換日誌將standby redologs啟用。

SQL> alter system switch logfile;

System altered.

再次在standby端查詢standby redologs狀態顯示啟用。

SQL> select group#, sequence#, dbid,status from v$standby_log;

GROUP# SEQUENCE# DBID STATUS

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

11 28 3018705892 ACTIVE

12 0 UNASSIGNED UNASSIGNED

13 0 UNASSIGNED UNASSIGNED

14 0 UNASSIGNED UNASSIGNED

15 0 UNASSIGNED UNASSIGNED

16 0 UNASSIGNED UNASSIGNED

6 rows selected.

主備庫最大歸檔序號相同：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

32

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

32

兩個primary節點的日誌歸檔狀態如下：

SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;

DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN

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

2 VALID 32 1303371

SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;

DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN

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

2 VALID 24 1311729

17.7 standby端啟動實時查詢

檢視當前資料庫開啟模式。

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

直接開啟資料庫將報錯。

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in progress

先暫停redo應用，然後開啟資料庫，再重啟redo應用。

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

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

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL> select recovery_mode from v$archive_dest_status where dest_id=1;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

進行測試：

在primary端節點1建立表並插入資料

SQL> conn hr/hr

Connected.

SQL> create table emp as select * from employees;

Table created.

在standby端查詢：

SQL> conn hr/hr

Connected.

SQL> select count(*) from emp;

COUNT(*)

----------

107

在primary端節點2建立表並插入資料

SQL> create table hr.emp1 as select * from hr.employees;

Table created.

在standby端查詢：

SQL> select count(*) from hr.emp1;

COUNT(*)

----------

107

OEL6.3 64位部署ORACLE 11gR2(11.2.0.4) RAC+DG（9）建立DG

相關文章