1、環境

System

Version

Hostname

IP

Oracle

Version

DB_NAME

DB_UNIQUE_NAME

Primary

RHEL6.2

dg1

192.168.230.129

11.2.0.1.0

dg1

Standby

RHEL6.2

dg2

192.168.230.130

11.2.0.1.0

dg1

dg2

Primary端資料庫處於open狀態、歸檔狀態及強制歸檔狀態，透過DBCA建立，已經有密碼檔案；standby端僅安裝資料庫軟體，沒有建立資料庫。

2、網路配置

2.1 primary監聽配置

[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 = dg1)

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

(SID_NAME = dg1)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2.2 standby監聽配置

[oracle@dg2 ~]$ 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 = dg2)

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

(SID_NAME = dg2)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2.3 primary網路服務名配置

[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.

DG_129 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg1)

)

DG_130 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg2)

)

2.4 standby網路服務名配置

[oracle@dg2 ~]$ 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.

DG_129 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg1)

)

DG_130 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = dg2)

)

2.5 啟動監聽並進行測試

注意要進行防火牆放行或者關閉防火牆。

[oracle@dg1 ~]$ tnsping dg_130

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-FEB-2015 17:46:08

Used parameter files:

Used TNSNAMES adapter to resolve the alias

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

OK (0 msec)

[oracle@dg2 ~]$ tnsping dg_129

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-FEB-2015 17:46:25

Used parameter files:

Used TNSNAMES adapter to resolve the alias

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

OK (10 msec)

3、引數配置

3.1 primary引數配置

根據spfile.ora生成pfile.ora。

[oracle@dg1 ~]$ cat pfile.ora

dg1.__db_cache_size=58720256

dg1.__java_pool_size=4194304

dg1.__large_pool_size=4194304

dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg1.__pga_aggregate_target=197132288

dg1.__sga_target=247463936

dg1.__shared_io_pool_size=0

dg1.__shared_pool_size=167772160

dg1.__streams_pool_size=4194304

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/DG1/controlfile/o1_mf_bdkgbx5j_.ctl','/u01/app/oracle/flash_recovery_area/DG1/controlfile/o1_mf_bdkgbxcc_.ctl'

*.db_block_size=8192

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

*.db_domain=''

*.db_name='dg1'

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

*.db_recovery_file_dest_size=4070572032

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

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

*.log_archive_config='dg_config=(dg1,dg2)'

*.log_archive_dest_2='service=dg_130 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg2'

*.log_archive_dest_state_2='ENABLE'

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

*.memory_target=444596224

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

3.2 standby引數配置

將primary的pfile.ora複製到standby，修改後生成spfiledg2.ora。

[oracle@dg2 ~]$ cat pfile.ora

dg1.__db_cache_size=100663296

dg2.__db_cache_size=155189248

dg1.__java_pool_size=4194304

dg2.__java_pool_size=4194304

dg1.__large_pool_size=4194304

dg2.__large_pool_size=4194304

dg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dg1.__pga_aggregate_target=155189248

dg2.__pga_aggregate_target=180355072

dg1.__sga_target=289406976

dg2.__sga_target=264241152

dg1.__shared_io_pool_size=0

dg2.__shared_io_pool_size=0

dg1.__shared_pool_size=167772160

dg2.__shared_pool_size=92274688

dg1.__streams_pool_size=4194304

dg2.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl','/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'#Set by RMAN

*.db_block_size=8192

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

*.db_domain=''

*.db_name='dg1'

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

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='dg2'

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

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

*.log_archive_config='dg_config=(dg1,dg2)'

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

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

*.memory_target=444596224

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

4、系統設定

4.1 standby目錄建立

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

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

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

mkdir /u01/app/oracle/flash_recovery_area

4.2 standby密碼檔案

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

scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1 oracle@192.168.233.130:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdg2

5、建立standby資料庫

將standby資料庫啟動到nomount狀態，在primary端執行如下命令：

[oracle@dg1 ~]$ rman target / auxiliary sys/123456@dg_130

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Feb 1 16:22:59 2015

connected to target database: DG1 (DBID=1851568413)

connected to auxiliary database: DG1 (not mounted)

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

Starting Duplicate Db at 01-FEB-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

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

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

}

executing Memory Script

Starting backup at 01-FEB-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

Finished backup at 01-FEB-15

contents of Memory Script:

{

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment=

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

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

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl' from

'/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl';

sql clone "alter system set control_files =

''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment=

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

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 01-FEB-15

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_dg1.f tag=TAG20150201T162457 RECID=4 STAMP=870539108

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

Finished backup at 01-FEB-15

Starting restore at 01-FEB-15

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 01-FEB-15

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/dg2/controlfile/o1_mf_bdkgbx5j_.ctl'', ''/u01/app/oracle/flash_recovery_area/dg2/controlfile/o1_mf_bdkgbxcc_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 442601472 bytes

Fixed Size 2214176 bytes

Variable Size 281020128 bytes

Database Buffers 155189248 bytes

Redo Buffers 4177920 bytes

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;

set newname for clone tempfile 2 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;

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

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

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

renamed tempfile 2 to /u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_%u_.tmp in control file

executing command: SET NEWNAME

Starting backup at 01-FEB-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_system_bdkg85cs_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_system_0dpu6nuh_.dbf tag=TAG20150201T162657

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_sysaux_bdkg85ft_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_sysaux_0epu6o0i_.dbf tag=TAG20150201T162657

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/DG1/datafile/n6_data01.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_0fpu6o2a_.dbf tag=TAG20150201T162657

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_undotbs1_bdkg85g2_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_undotbs1_0gpu6o3d_.dbf tag=TAG20150201T162657

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/DG1/datafile/o1_mf_users_bdkg85gt_.dbf

output file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_users_0hpu6o46_.dbf tag=TAG20150201T162657

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

Finished backup at 01-FEB-15

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=4 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_system_0dpu6nuh_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_sysaux_0epu6o0i_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_undotbs1_0gpu6o3d_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_users_0hpu6o46_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=870539415 file name=/u01/app/oracle/oradata/DG2/datafile/o1_mf_n6datasp_0fpu6o2a_.dbf

Finished Duplicate Db at 01-FEB-15

6、standby端啟動redo應用

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

資料庫已更改。

7、驗證

7.1 primary端切換日誌

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)

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

15

7.2 standby端檢視日誌

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

MAX(SEQUENCE#)

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

15

Linux下ORACLE 11G DATA GUARD搭建（用於實時備份）

相關文章