ORACLE 11g dataguard配置練習

兩臺伺服器資訊如下

主資料庫 10.1.1.21

安裝資料庫軟體、建立資料庫

standby資料庫10.1.1.23

準備工作

1、主資料庫啟用歸檔，經檢查已經啟用

2、standby資料庫上面安裝oracle 11g資料庫軟體，無需建立資料庫例項

3、standby上面建立必要的目錄，並賦予許可權

[oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/admin/sap/adump

[oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/oradata/sap/

[oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/flash_recovery_area/sap/

[oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/admin/standby/adump

4、在備用資料庫上面配置啟用監聽器

主資料庫配置檔案如下

[oracle@standby admin]$ cat listener.ora

# listener.ora Network Configuration File: /home/oracle/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 = standby)

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

(SID_NAME = standby)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

ADR_BASE_LISTENER = /home/oracle/app/oracle

備用資料庫listener配置如下

[oracle@standby admin]$ cat listener.ora

# listener.ora Network Configuration File: /home/oracle/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 = standby)

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

(SID_NAME = standby)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

ADR_BASE_LISTENER = /home/oracle/app/oracle

5、為每個系統配置tnsnames檔案

配置如下

[oracle@sap admin]$ cat tnsnames.ora

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

# Generated by Oracle configuration tools.

SAP =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = sap)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

使用tnsping測試

在主資料庫上

[oracle@sap admin]$ tnsping sap

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 21:50:22

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (10 msec)

[oracle@sap admin]$ tnsping standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 21:50:28

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (10 msec)

在standby資料庫上面

[oracle@standby bin]$ tnsping sap

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 22:00:43

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (10 msec)

[oracle@standby bin]$ tnsping standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 22:00:49

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:

/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (0 msec)

[oracle@standby bin]$

配置standby資料庫

1、建立引數檔案

[oracle@standby dbs]$ echo 'DB_NAME=standby' > ./initstandby.ora

[oracle@standby dbs]$ cat ./initstandby.ora

DB_NAME=standby

2、建立密碼檔案

將主資料庫的密碼檔案scp到standby資料庫響應目錄

3、啟動standby資料庫到nomount

[oracle@standby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:55:13 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size                 2211928 bytes

Variable Size                 159387560 bytes

Database Buffers         50331648 bytes

Redo Buffers                 5226496 bytes

SQL>

配置primary資料庫

1、建立standby logfile

SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby02.log' size 50m;

Database altered.

SQL>

SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby04.log' size 50m;

Database altered.

SQL>

2、rman duplicate 建立備用資料庫

使用rman連線兩個資料庫

[oracle@sap admin]$ rman target / auxiliary sys/sys@standby

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 12 23:29:40 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: SAP (DBID=3375191994)

connected to auxiliary database (not started)

執行duplicate使用以下指令碼

run{

allocate channel prmy1 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'sap','standby'

set 'db_unique_name'='standby'

set control_files='/home/oracle/app/oracle/oradata/sap/control.ctl'

set db_create_online_log_dest_1='/home/oracle/app/oracle/oradata/sap/'

set db_create_online_log_dest_2='/home/oracle/app/oracle/oradata/sap/'

set db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area/'

set db_recovery_file_dest_size='10G'

nofilenamecheck;

}

allocated channel: prmy1

channel prmy1: SID=191 device type=DISK

allocated channel: stby1

channel stby1: SID=129 device type=DISK

Starting Duplicate Db at 13-SEP-14

contents of Memory Script:

{

backup as copy reuse

targetfile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsap' auxiliary format

'/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby' ;

}

executing Memory Script

Starting backup at 13-SEP-14

Finished backup at 13-SEP-14

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/home/oracle/app/oracle/oradata/sap/control.ctl';

}

executing Memory Script

Starting backup at 13-SEP-14

channel prmy1: starting datafile copy

copying standby control file

output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_sap.f tag=TAG20140913T030248 RECID=28 STAMP=858135769

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

Finished backup at 13-SEP-14

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 tempfile 1 to

"/home/oracle/app/oracle/oradata/sap/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/home/oracle/app/oracle/oradata/sap/system01.dbf";

set newname for datafile 2 to

"/home/oracle/app/oracle/oradata/sap/sysaux01.dbf";

set newname for datafile 3 to

"/home/oracle/app/oracle/oradata/sap/undotbs01.dbf";

set newname for datafile 4 to

"/home/oracle/app/oracle/oradata/sap/users01.dbf";

set newname for datafile 5 to

"/home/oracle/app/oracle/oradata/sap/example01.dbf";

set newname for datafile 6 to

"/home/oracle/app/oracle/oradata/sap/lztest.dbf";

set newname for datafile 7 to

"/home/oracle/app/oracle/oradata/sap/trans_test.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/home/oracle/app/oracle/oradata/sap/system01.dbf" datafile

2 auxiliary format

"/home/oracle/app/oracle/oradata/sap/sysaux01.dbf" datafile

3 auxiliary format

"/home/oracle/app/oracle/oradata/sap/undotbs01.dbf" datafile

4 auxiliary format

"/home/oracle/app/oracle/oradata/sap/users01.dbf" datafile

5 auxiliary format

"/home/oracle/app/oracle/oradata/sap/example01.dbf" datafile

6 auxiliary format

"/home/oracle/app/oracle/oradata/sap/lztest.dbf" datafile

7 auxiliary format

"/home/oracle/app/oracle/oradata/sap/trans_test.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/app/oracle/oradata/sap/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 13-SEP-14

channel prmy1: starting datafile copy

input datafile file number=00001 name=/home/oracle/app/oracle/oradata/sap/system01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/system01.dbf tag=TAG20140913T030258

channel prmy1: datafile copy complete, elapsed time: 00:00:45

channel prmy1: starting datafile copy

input datafile file number=00002 name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf tag=TAG20140913T030258

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

channel prmy1: starting datafile copy

input datafile file number=00003 name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf tag=TAG20140913T030258

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

channel prmy1: starting datafile copy

input datafile file number=00005 name=/home/oracle/app/oracle/oradata/sap/example01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/example01.dbf tag=TAG20140913T030258

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

channel prmy1: starting datafile copy

input datafile file number=00006 name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

output file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf tag=TAG20140913T030258

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

channel prmy1: starting datafile copy

input datafile file number=00007 name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf

output file name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf tag=TAG20140913T030258

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

channel prmy1: starting datafile copy

input datafile file number=00004 name=/home/oracle/app/oracle/oradata/sap/users01.dbf

output file name=/home/oracle/app/oracle/oradata/sap/users01.dbf tag=TAG20140913T030258

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

Finished backup at 13-SEP-14

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=28 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=29 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=30 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=31 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=32 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=33 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=34 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf

Finished Duplicate Db at 13-SEP-14

released channel: prmy1

released channel: stby1

RMAN>

3、檢視standby資料庫當前狀態

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select name from v$datafile;

NAME

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

/home/oracle/app/oracle/oradata/sap/system01.dbf

/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

/home/oracle/app/oracle/oradata/sap/users01.dbf

/home/oracle/app/oracle/oradata/sap/example01.dbf

/home/oracle/app/oracle/oradata/sap/lztest.dbf

/home/oracle/app/oracle/oradata/sap/trans_test.dbf

7 rows selected.

4、檢視logfile

SQL> select type,member from v$logfile;

TYPE

-------

MEMBER

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

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_3_b185ppnf_.log

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_2_b185pl8b_.log

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_1_b185pg4v_.log

TYPE

-------

MEMBER

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

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_1_b185pg9t_.log

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_2_b185plcj_.log

ONLINE

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_3_b185pqhx_.log

TYPE

-------

MEMBER

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

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_4_b185pvwm_.log

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_4_b185pw0n_.log

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_5_b185q0o4_.log

TYPE

-------

MEMBER

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

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_6_b185q4xb_.log

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_7_b185qcyq_.log

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_5_b185q0qr_.log

TYPE

-------

MEMBER

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

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_6_b185q95r_.log

STANDBY

/home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_7_b185qd7s_.log

14 rows selected.

配置data guard

1、將備用和主角色新增到standby資料庫

SQL> alter system set fal_server = sap;

System altered.

SQL> alter system set fal_client=standby;

System altered.

SQL> alter system set log_archive_config='dg_config=(sap,standby)';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter system set log_archive_dest_2='service=sap async db_unique_name=sap valid_for=(primary_role,online_logfile)';

System altered.

2、在standby資料庫上啟動log應用程式

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

Database altered.

3、返回主資料庫，配置重做傳輸

SQL>

SQL> alter system set log_archive_config='dg_config=(sap,standby)';

System altered.

SQL>

SQL> alter system set log_archive_dest_2='service=standby async db_unique_name=standby valid_for=(primary_role,online_logfile)';

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system set fal_server = standby;

System altered.

SQL> alter system set fal_client=sap;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

至此配置完成。

dataguard啟動停止步驟

停止過程

1、standby資料庫上停止redo日誌應用

alter database recover managed standby database cancel;

2、關閉之資料庫

3、關閉standby資料庫

啟動過程

1、啟動standby資料庫

2、啟動主資料庫

3、standby資料庫上執行redo日誌應用

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