當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle不一致性关闭下次,Oracle DataGuard

發(fā)布時間：2024/9/19 编程问答 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle不一致性关闭下次,Oracle DataGuard 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

```1、文檔介紹

本實施方案主要對Oracle DataGuard實施部署作相應的說明。以便實施人員能根據當前業(yè)務特點，規(guī)劃、建設符合高可用、高可靠的數據庫集群系統。具體由Oracle DG環(huán)境拓撲、Oracle單機數據庫規(guī)劃部分構成！

2 、Oracle Data Guard 介紹

2.1 Data Guard環(huán)境拓展

2.2 Data Guard特點

數據庫服務器采用DATAGUARD災備模式，可以滿足對可用性有特殊需求的應

用，具備以下特點：

1、需要冗余的服務器設備。該模式需要有冗余的服務器硬件。硬件成本較高。

2、需要冗余的存儲設備。主機和備機都需要同樣的存儲空間，成本較高。

3、安裝配置比較復雜。該模式比單節(jié)點、單實例的模式配置復雜一些，需要

更多的配置步驟。

4、管理維護成本高。該模式對維護人員的要求較高，維護成本高。

5、具備一定的容災特性。當主機整個數據庫系統不可用并短期內無法恢復

時，可以把數據庫系統切換到備機上，具備容災的功能。

6、備機可以用作只讀查詢。備機可以切換到只讀狀態(tài)供報表之類的查詢操

作，減輕主機的壓力。

3、Oracle DataGurad單實例部署

3.1安裝環(huán)境

在主機1上安裝數據庫軟件，并建監(jiān)聽和實例，

在主機2上安裝數據庫軟件，并建監(jiān)聽，但不建實例。

主機1(主庫) 主機2(備庫)

操作系統 Centos6.5 64位 Centos6.5 64位

主機名 orcl orclstd

IP 192.168.168.186 192.168.168.187

數據庫軟件版本 11.2.0.4 11.2.0.4

ORACLE_BASE /u01/app/oracle /u01/app/oracle

ORACLE_HOME /u01/app/oracle/product/11.2/db_1 /u01/app/oracle/product/11.2/db_1

ORACLE_SID orcl

閃回區(qū) 開啟

歸檔開啟

3.2主數據庫配置

3.2.1 設置數據庫歸檔

查看數據庫是否運行在歸檔模式：

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled #未開啟歸檔

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 117

Current log sequence 119

SQL>

備注：如果數據庫已經開啟歸檔，下面的操縱可以忽略。

如上所示未開啟歸檔，可按下面方法開啟數據庫歸檔

SQL> shutdown immediate #關閉數據庫

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount; #啟動到mount狀態(tài)

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> alter database archivelog; #開啟歸檔

Database altered.

SQL> alter database open; #open數據庫

Database altered.

SQL> alter system set log_archive_dest_1='location=/data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'; #設置歸檔路徑，此處DB_UNIQUE_NAME要與此實例中指定的DB_UNIQUE_NAME保持一致

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/ORCL/archivelog

Oldest online log sequence 119

Next log sequence to archive 121

Current log sequence 121

SQL>

3.2.2 設置數據庫閃回

驗證是否開啟閃回

SQL> select flashback_on from v$database;

FLASHBACK_ON

SQL>

備注：如果數據庫已經開啟flashback，那么下面步驟可忽略。

如上顯示，該數據庫未開啟flashback，可按下面方法開啟。

SQL> alter system set db_recovery_file_dest='/home/oracle/flashdata'; #設置閃回去路徑 (忽略)

-----------此處就是歸檔路徑

System altered.

SQL> altersystem set db_recovery_file_dest_size='15G'; #設置閃回區(qū)大小 (忽略)

System altered.

SQL> shutdown immediate; #關閉數據庫

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> alter database flashback on; #開啟閃回

Database altered.

SQL> alter database open; #open數據庫

Database altered.

SQL> select flashback_on from v$database; #驗證是否開啟

FLASHBACK_ON

YES

SQL>

3.2.3 設置數據庫強制歸檔

驗證是否開啟focelogging

SQL>select force_logging from v$database;

FOR

如果數據庫已經開啟force logging，那么下面步驟可忽略。

如上可以看出數據庫未開啟，則按下面步驟執(zhí)行：

SQL>alter database force logging; #開啟force logging

Database altered.

SQL>select force_logging from v$database; #驗證

FOR

YES

SQL>

3.2.4 添加STANDBY 日志文件

在備庫，當RFS進程接受到日志后，就將其寫入Standby日志文件里，備庫的Standby日志文件可以看做是主庫在線日志文件的一個鏡像，當主庫做日志切換時，備庫的Standby日志也做相應的切換，切換后的Standby日志由備庫的ARCH進程歸檔。

Oracle規(guī)定備庫的Standby日志文件大小不能小于主庫在線日志文件最大的一個，一般情況下，為了管理方便，最好把所有的在線日志和Standby日志大小設為一樣。

通過下面語句可以查詢主庫在線日志的大小和組數：

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024

1 50

2 50

3 50

SQL>

通過下面的語句可以查詢備庫Standby日志的大小和組數：

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

創(chuàng)建standby logfile

創(chuàng)建原則

1、確保standby redo log 的大小與主庫online redo log 的大小保持一致

2、如主庫為單實例數據庫：standby redo log組數=主庫日志組總數

3、如果主庫是RAC數據庫：standby redo log組數=(每線程的日志組數 1)*最大線程數

4、不建議復用standby redo log，避免增加額外的I/O以及延緩重做傳輸

SQL> alter database add standby logfile group 11 ' /u01/app/oracle/flash_recovery_area/redo11_stb01.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 12 '/u01/app/oracle/flash_recovery_area/redo12_stb01.log'size 50M;

Database altered.

SQL> alter database add standby logfile group 13 ' /u01/app/oracle/flash_recovery_area/redo13_stb01.log'size 50M;

Database altered.

SQL> alter database add standby logfile group 14 '/u01/app/oracle/flash_recovery_area/redo14_stb01.log' size 50M;

Database altered.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024

11 50

12 50

13 50

14 50

SQL>

3.2.5 修改參數文件

SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orclstd)'; ---主備庫唯一名，無先后順序

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=orclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd' scope=spfile;

------此處的SERVICE是指到從庫的TNS名

--如果搭建延遲DG，加上DELAY=minutes，默認30分鐘，啟動方式也不同

alter database recover managed standby database delay 1440 disconnect from session;

System altered.

SQL> alter system set log_archive_dest_state_1='enable';

System altered.

SQL> alter system set log_archive_dest_state_2='enable';

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl' scope=spfile;

------- db_file_name_convert是指數據文件路徑,此處設置了一樣的路徑,路徑名最后必須加上/

----------如果是集群的磁盤組就不用，例： DATA

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/' scope=spfile;

---------日志路徑也設置成一致，路徑名最后必須加上/

----------如果是集群的磁盤組就不用，例： DATA

System altered.

SQL> alter system set fal_server='orclstd'; --備庫tns

-------與備庫設置相反

System altered.

SQL> alter system set fal_client='orcl'; --主庫tns

-------與備庫設置相反

System altered.

SQL> alter system set standby_file_management='AUTO';

System altered.

注意：上面修改的參數有的需要重啟數據庫才能生效，下面為重啟數據庫步驟

SQL> shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SQL> startup

ORACLEinstance started.

TotalSystem Global Area 688959488 bytes

Fixed Size 2256432 bytes

VariableSize 566231504 bytes

DatabaseBuffers 117440512 bytes

RedoBuffers 3031040 bytes

Databasemounted.

Databaseopened.

SQL>

ADR_BASE_LISTENER = /u01/app/oracle

注意：如果主庫上的監(jiān)聽之前已經配置過，這里可以忽略該步驟。

3.2.7 修改TNS配置文件

orcl:/home/oracle@orcl>vi/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

orcl =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

orclstd =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orclstd)

)

3.2.8 重啟監(jiān)聽服務

orcl:/home/oracle@orcl>lsnrctl stop

orcl:/home/oracle@orcl>lsnrctl start

3.2.9 拷貝參數文件

利用spfile創(chuàng)建pfile

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

將pfile拷貝到備庫

scp /home/oracle/pfile.ora 192.168.168.187:/u01/app/oracle/product/11.2.0/db_1/dbs

重命名pfile.ora為initorclstd.ora

3.2.10 拷貝密碼文件

/u01/app/oracle/product/11.2.0/db_1/dbs

scp orapworcl 192.168.168.187:/u01/app/oracle/product/11.2.0/db_1/dbs

3.3備庫配置

3.3.1 修改參數文件

以下是主庫參數文件：

orcl.db_cache_size=2113929216

orcl.java_pool_size=33554432

orcl.large_pool_size=50331648

orcl.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.pga_aggregate_target=922746880

orcl.sga_target=2768240640

orcl.shared_io_pool_size=0

orcl.shared_pool_size=536870912

orcl.streams_pool_size=0

.audit_file_dest='/u01/app/oracle/admin/orcl/adump'.audit_trail='db'

.compatible='11.2.0.4.0'.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

.db_block_size=8192.db_domain=''

.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'.db_name='orcl'

.db_recovery_file_dest='/home/oracle/flashdata'.db_recovery_file_dest_size=16106127360

.db_unique_name='ORCL'.diagnostic_dest='/u01/app/oracle'

.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'.fal_client='ORCL'

.fal_server='ORCLSTD'.log_archive_config='DG_CONFIG=(orcl,orclstd)'

.log_archive_dest_2='SERVICE=orclstd LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd'.log_archive_dest_state_1='enable'

.log_archive_dest_state_2='enable'.log_archiveformat='%t%s_%r.dbf'

.log_file_name_convert='/home/oracle/flashdata/orclstd/onlinelog','/home/oracle/flashdata/orcl/onlinelog'.open_cursors=300

.pga_aggregate_target=917504000.processes=150

.remote_login_passwordfile='EXCLUSIVE'.sga_target=2752512000

.standby_file_management='AUTO'.undo_tablespace='UNDOTBS1'

以下是備庫參數文件：

orclstd.db_cache_size=3439329280

orclstd.java_pool_size=16777216

orclstd.large_pool_size=33554432

orclstd.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orclstd.pga_aggregate_target=922746880

orclstd.sga_target=4294967296

orclstd.shared_io_pool_size=0

orclstd.shared_pool_size=771751936

orclstd.streams_pool_size=0

.audit_file_dest='/u01/app/oracle/admin/orclstd/adump'.audit_trail='db'

.compatible='11.2.0.4.0'.control_files='/u01/app/oracle/oradata/orclstd/control01.ctl','/u01/app/oracle/fast_recovery_area/orclstd/control02.ctl'

.db_block_size=8192.db_domain=''

.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'.db_name='orcl'

.db_recovery_file_dest='/home/oracle/flashdata'.db_recovery_file_dest_size=16106127360

.db_unique_name='ORCLSTD'.diagnostic_dest='/u01/app/oracle'

.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'.fal_client='ORCLSTD'

.fal_server='ORCL'.log_archive_config='DG_CONFIG=(orcl,orclstd)'

.log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'.log_archive_dest_state_1='enable'

.log_archive_dest_state_2='enable'.log_archiveformat='%t%s_%r.dbf'

.log_file_name_convert='/home/oracle/flashdata/orcl/onlinelog','/home/oracle/flashdata/orclstd/onlinelog'.open_cursors=300

.pga_aggregate_target=917504000.processes=150

.remote_login_passwordfile='EXCLUSIVE'.sga_max_size=4294967296

.sga_target=4294967296.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

3.3.2 修改密碼文件

修改之前從主庫拷貝過來的密碼文件，具體如下：

orclstd:/u01/app/oracle/product/11.2.0/db_1/dbs@orclstd>mv orapworcl orapworclstd

3.3.3 創(chuàng)建相應的文件目錄

根據上面修改的參數文件，為備庫創(chuàng)建相應的文件目錄

orclstd:/home/oracle@orclstd>mkdir -p /u01/app/oracle/admin/orclstd/adump

orclstd:/home/oracle@orclstd>mkdir -p /u01/app/oracle/oradata/orclstd/

orclstd:/home/oracle@orclstd>mkdir -p /u01/app/oracle/oradata/orcl/

orclstd:/home/oracle@orclstd>mkdir -p /home/oracle/flashdata

3.3.4 修改監(jiān)聽配置文件

orclstd:/u01/app/oracle/product/11.2.0/db_1/network/admin@orclstd>vi listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orclstd)

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

(SID_NAME = orclstd)

)

3.3.5 修改TNS配置文件

orclstd:/u01/app/oracle/product/11.2.0/db_1/network/admin@orclstd>vi tnsnames.ora

具體如下：

orcl =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

orclstd =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orclstd)

)

3.3.6 重啟監(jiān)聽服務

orclstd:/data/ORCLSTD@orclstd>lsnrctl stop

orclstd:/data/ORCLSTD@orclstd>lsnrctl start

3.3.7 啟動數據庫到nomount狀態(tài)

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorclstd.ora';

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

SQL> create spfile from pfile; #創(chuàng)建spfile

File created.

再次重啟數據庫

3.3.8 驗證監(jiān)聽和TNS配置

主庫上驗證:

orcl:/home/oracle@orcl>sqlplus sys/oracle@orcl as sysdba

SQLPlus: Release 11.2.0.4.0 Production onTue Jun 6 10:34:42 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

orcl:/home/oracle@orcl>sqlplus sys/oracle@orclstd as sysdba

SQLPlus: Release 11.2.0.4.0 Production onTue Jun 6 10:34:50 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>

備庫上驗證：

orclstd:/home/oracle@orclstd>sqlplus sys/oracle@orcl as sysdba

SQLPlus: Release 11.2.0.4.0 Production onTue Jun 6 11:05:01 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

orclstd:/home/oracle@orclstd>sqlplus sys/oracle@orclstd as sysdba

SQLPlus: Release 11.2.0.4.0 Production onTue Jun 6 11:05:05 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>

注意：該步驟一定要在主備庫上都能通過才能執(zhí)行下面步驟

3.3.9 恢復數據庫

在備庫執(zhí)行

rman target sys/oracle@主庫tns auxiliary sys/oracle@備庫tns

orclstd:/home/oracle@orclstd>rman target sys/oracle@orcl auxiliary sys/oracle@orclstd

Recovery Manager: Release 11.2.0.4.0 -Production on Tue Jun 6 11:13:43 2017

connected to target database: ORCL(DBID=3677012495)

connected to auxiliary database: ORCL (not mounted)

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

具體執(zhí)行過程顯示如下：

Starting Duplicate Db at 2017/06/06 11:14:26

using target database control file insteadof recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 devicetype=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworclstd' ;

}

executing Memory Script

Starting backup at 2017/06/06 11:14:29

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 device type=DISK

Finished backup at 2017/06/06 11:14:31

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/data/ORCLSTD/controlfile/o1_mfdm1flhj3.ctl';

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/data/ORCLSTD/controlfile/o1_mfdm1flhj3.ctl'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Starting backup at 2017/06/06 11:14:32

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output filename=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.ftag=TAG20170606T105032 RECID=5 STAMP=945946234

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

Finished backup at 2017/06/06 11:14:36

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (notstarted)

Oracle instance started

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

sql statement: alter system set control_files = ''/data/ORCLSTD/controlfile/o1_mfdm1flhj3.ctl''comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (notstarted)

Oracle instance started

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standbydatabase

contents of Memory Script:

{

set newname for tempfile 1 to

"/data/ORCLSTD/datafile/o1_mf_tempdm1fp4bs.tmp";

switch clone tempfile all;

set newname for datafile 1 to

"/data/ORCLSTD/datafile/o1_mf_systemdm1flxkw.dbf";

set newname for datafile 2 to

"/data/ORCLSTD/datafile/o1_mf_sysauxdm1fnw5v.dbf";

set newname for datafile 3 to

"/data/ORCLSTD/datafile/o1_mf_undotbs1dm1foow9.dbf";

set newname for datafile 4 to

"/data/ORCLSTD/datafile/o1_mf_usersdm1fqcrp.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/data/ORCLSTD/datafile/o1_mf_systemdm1flxkw.dbf" datafile

2auxiliary format

"/data/ORCLSTD/datafile/o1_mf_sysauxdm1fnw5v.dbf" datafile

3auxiliary format

"/data/ORCLSTD/datafile/o1_mf_undotbs1dm1foow9.dbf" datafile

4auxiliary format

"/data/ORCLSTD/datafile/o1_mf_usersdm1fqcrp.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to/data/ORCLSTD/datafile/o1_mf_tempdm1fp4bs.tmp in control file

executing command: SET NEWNAME

Starting backup at 2017/06/06 11:15:06

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001name=/data/ORCL/datafile/o1_mf_systemdm1flxkw.dbf

output filename=/data/ORCLSTD/datafile/o1_mf_systemdm1flxkw.dbf tag=TAG20170606T105107

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002name=/data/ORCL/datafile/o1_mf_sysauxdm1fnw5v.dbf

output filename=/data/ORCLSTD/datafile/o1_mf_sysauxdm1fnw5v.dbf tag=TAG20170606T105107

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003name=/data/ORCL/datafile/o1_mf_undotbs1dm1foow9.dbf

output file name=/data/ORCLSTD/datafile/o1_mf_undotbs1dm1foow9.dbftag=TAG20170606T105107

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004name=/data/ORCL/datafile/o1_mf_usersdm1fqcrp.dbf

output filename=/data/ORCLSTD/datafile/o1_mf_usersdm1fqcrp.dbf tag=TAG20170606T105107

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

Finished backup at 2017/06/06 11:18:07

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=5 STAMP=945947890file name=/data/ORCLSTD/datafile/o1_mf_systemdm1flxkw.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=945947890file name=/data/ORCLSTD/datafile/o1_mf_sysauxdm1fnw5v.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=945947890file name=/data/ORCLSTD/datafile/o1_mf_undotbs1dm1foow9.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=945947890file name=/data/ORCLSTD/datafile/o1_mf_usersdm1fqcrp.dbf

Finished Duplicate Db at2017/06/06 11:18:25

RMAN>

3.3.10 備庫開啟實時同步

SQL> alter database open;

Database altered.

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

Database altered.

如果是延遲DG，啟動語句，這里是延遲1440分鐘

alter database recover managed standby database delay 1440 disconnect from session;

3.3.11 開啟flashback

上面DataGuard搭建好之后，千萬不要忘了把備庫的flashback打開，具體如下：

SQL> alter database recover managed standby database cancel; #取消實時同步

Database altered.

SQL> shutdownimmediate #關閉數據庫

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startupmount #打開到mount狀態(tài)

ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> alterdatabase flashback on; #開始flashback

Database altered.

SQL> alter database open; #open數據庫

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session; #開啟實時同步

Database altered.

3.4驗證DG同步

上面已經把DataGurad搭建完，下面介紹如何驗證DataGuard是否能實時同步

通過查看archive_log_dest_2列是否有error報錯，如果有報錯，則需要先根據報錯內容解決問題

SQL>col dest_name format a30

SQL>col error format a20

SQL>select dest_name,error from v$archive_dest;

DEST_NAME ERROR

LOG_ARCHIVE_DEST_1

LOG_ARCHIVE_DEST_2

LOG_ARCHIVE_DEST_3

LOG_ARCHIVE_DEST_4

LOG_ARCHIVE_DEST_5

LOG_ARCHIVE_DEST_6

LOG_ARCHIVE_DEST_7

LOG_ARCHIVE_DEST_8

LOG_ARCHIVE_DEST_9

LOG_ARCHIVE_DEST_10

LOG_ARCHIVE_DEST_11

DEST_NAME ERROR

LOG_ARCHIVE_DEST_12

LOG_ARCHIVE_DEST_13

LOG_ARCHIVE_DEST_14

LOG_ARCHIVE_DEST_15

LOG_ARCHIVE_DEST_16

LOG_ARCHIVE_DEST_17

LOG_ARCHIVE_DEST_18

LOG_ARCHIVE_DEST_19

LOG_ARCHIVE_DEST_20

LOG_ARCHIVE_DEST_21

LOG_ARCHIVE_DEST_22

DEST_NAME ERROR

LOG_ARCHIVE_DEST_23

LOG_ARCHIVE_DEST_24

LOG_ARCHIVE_DEST_25

LOG_ARCHIVE_DEST_26

LOG_ARCHIVE_DEST_27

LOG_ARCHIVE_DEST_28

LOG_ARCHIVE_DEST_29

LOG_ARCHIVE_DEST_30

LOG_ARCHIVE_DEST_31

31rows selected.

SQL>

注意：上面顯示沒有報錯

查詢主庫最大歸檔序號，一致即歸檔同步成功。

主庫上執(zhí)行：

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

MAX(SEQUENCE#)

132

備庫上操作：

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

MAX(SEQUENCE#)

132

主庫上執(zhí)行日志切換

SQL>alter system archive log current;

Systemaltered.

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

MAX(SEQUENCE#)

133

SQL>

備庫上再次驗證：

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

MAX(SEQUENCE#)

133

SQL>

查看主備庫狀態(tài)

主庫上執(zhí)行：

SQL>select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

TOSTANDBY PRIMARY

備庫上執(zhí)行：

SQL>select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

NOTALLOWED PHYSICAL STANDBY

可以創(chuàng)建一個table進行測試

主庫上操作：

SQL>create table dg(id number);

Tablecreated.

SQL>insert into dg values(1);

1row created.

SQL>commit;

Commit complete.

SQL>select * from dg;

備庫上操作：

SQL>select * from dg;

SQL>

上面說明DG是同步的。

3.5DG切換與恢復

我們配置DG的目的就是為了在主庫出現故障時，備庫能夠提供服務，保證業(yè)務的正常運行。DG的故障切換分為switchover和failover兩種:

3.5.1 switchover

switchover是用戶有計劃的進行停機切換，能夠保證不丟失數據，下面我們來看下switchover是怎樣操作的：

主庫上操作：

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

TO STANDBY PRIMARY

SQL>

注意：上面查詢結果為TO STANDBY 或 SESSIONS ACTIVE表明可以進行切換

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

Database altered.

SQL> startup mount

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> select database_role from v$database;

DATABASE_ROLE

PHYSICAL STANDBY

備庫上操作：

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

TO PRIMARY PHYSICAL STANDBY

SQL>

注意：上面查詢結果顯示為TO PRIMARY 或 SESSIONS ACTIVE表明可以切換成主庫；

現在可以把備庫切換成主庫：

SQL> alter database commit to switchover to primary with session shutdown;-------- SESSIONS ACTIVE

Database altered.

SQL> alter database open;

Database altered.

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

SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE

SESSIONS ACTIVE PRIMARY READ WRITE

記住：這時候需要在現在的備庫(原先的主庫)開啟實時同步

SQL> alter database open;

Database altered.

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

Database altered.

到此DG switover切換完成，驗證方法同上。

3.5.2 failover

failover是當主庫真正出現嚴重系統故障，如數據庫宕機，軟硬件故障導致主庫不能支持服務，從而進行的切換動作。

注意：為了能夠在failover后能夠恢復DG,需要在主庫上開啟flashback，如果不開啟flashback的話，DG就可能需要重新搭建

由于主庫已經不可訪問，下面所有的操作都在備庫完成：

SQL> alter database recover managed standby database cancel; #停止實時同步

Database altered.

SQL> alter database recover managed standby database finish force;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE

PHYSICAL STANDBY

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

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

SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE

RESOLVABLE GAP PRIMARY READ WRITE

至此failover操作完成，原來的備庫已經切換為主庫，可以給業(yè)務提供服務了。

3.5.2 failover恢復

備注：如果未開啟flashback，需要使用rman備份重新搭建ADG

上面提到了failover，這種情形是當主庫真正出現異常之后，才會執(zhí)行的操作，那么我們執(zhí)行過failover 之后，如何在重新構建DG，這里我們利用flashback database來重構，具體方法如下：

在新的主庫上執(zhí)行：

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

977458

在之前的主庫上，也就是現在的備庫上執(zhí)行下面的操作:

SQL> startup mount

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> flashback database to scn 977458; #這個值為在新主庫上查詢到的SCN值

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

Database opened.

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

Database altered.

SQL>

到此failover 恢復已經完成，關于如何驗證DG方法如上，這里不在介紹。

來源：https://www.icode9.com/content-2-685501.html

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的oracle不一致性关闭下次,Oracle DataGuard的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。