oracle 11g DG 搭建

搭建環境: centos 5.6 oracle 11g r2

primary： 192.168.8.132 str1.example.com str1 主庫sid primary

standby: 192.168.8.131 str2.example.com str2 備庫sid standby

oracle 11g 軟體安裝 http://yangcheng.blog.51cto.com/1388193/1209421

primary配置

1.配置靜默安裝檔案

[root@str1 ~]# vi /u01/response/d.rsp

[GENERAL]

RESPONSEFILE_VERSION = "11.2.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "primary"

SID = "primary"

TEMPLATENAME = "General_Purpose.dbc"

[createTemplateFromDB]

SOURCEDB = "myhost:1521:primary"

SYSDBAUSERNAME = "system"

TEMPLATENAME = "My Copy TEMPLATE"

[createCloneTemplate]

SOURCEDB = "primary"

TEMPLATENAME = "My Clone TEMPLATE"

[DELETEDATABASE]

SOURCEDB = "primary"

[generateScripts]

TEMPLATENAME = "New Database"

GDBNAME = "primary"

[CONFIGUREDATABASE]

[ADDINSTANCE]

DB_UNIQUE_NAME = "primary"

NODELIST=

SYSDBAUSERNAME = "sys"

[DELETEINSTANCE]

DB_UNIQUE_NAME = "primary"

INSTANCENAME = "primary"

SYSDBAUSERNAME = "sys"

2.設定oracle 環境變數

[root@str1 ~]# vi /home/oracle/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

umask 022

export ORACLE_BASE=/u01/app

export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=primary

export PATH=$PATH:HOME/bin:$ORACLE_HOME/bin

3.安裝primary 實列

$ORACLE_HOME/bin/dbca -silent -responsefile /u01/response/d.rsp

一、開啟資料庫修改資料庫屬性

[oracle@str1 ~]$ sqlplus / as sysdba;

1）修改主庫屬性

SQL> startup mount

SQL> alter database force logging;

2）查看狀態

SQL> select FORCE_LOGGING from v$database;

3）修改資料庫為歸檔模式

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oradata/primary/' scope=both;

SQL> alter database archivelog;

4）添加 standby logfile (可不加)

備用日誌最少應該比 redo log 多一個

select GROUP#,MEMBERS,BYTES/1024/1024 from v$log;

select GROUP#,MEMBER from v$logfile;

增加 standby logfile

alter database add standby logfile

group 4 ('/u01/app/oradata/primary/redo04.log') size 50m,

group 5 ('/u01/app/oradata/primary/redo05.log') size 50m,

group 6 ('/u01/app/oradata/primary/redo06.log') size 50m,

group 7 ('/u01/app/oradata/primary/redo07.log') size 50m;

---如果要刪除 standby logfile

---　刪除也同樣簡單： SQL> alter database drop standby logfile group 4;

5）建立pfile

SQL> create pfile='/u01/app/backup/initprimary.ora' from spfile;

二、修改initprimary.ora

增加內容

vi /u01/app/backup/initprimary.ora

DB_UNIQUE_NAME='primary'

log_archive_config='DG_CONFIG=(primary,standby)'

log_archive_dest_1='LOCATION=/u01/app/oradata/primary/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=standby

FAL_CLIENT=primary

STANDBY_FILE_MANAGEMENT=AUTO

[root@str1 ~]# vi /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.

primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

EXTPROC_CONNECTION_DATA=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

三、修改監聽

[root@str1 ~]#vi /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 =

(SID_NAME = PLSExtProc)

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

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME= primary)

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

(SID_NAME=primary)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

)

)

四、用pfile啟動再重新建立spfile

startup pfile='/u01/app/backup/initprimary.ora'

create spfile from pfile='/u01/app/backup/initprimary.ora';

五、建立口令檔案

orapwd file='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary' password=letoula entries=10

六、備份控制檔案

alter database create standby controlfile as '/u01/app/backup/controlstb.ctl';

七、傳輸 pfile ， 口令檔案， 控制檔案到 standby

[root@str1 ~]# scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131:/u01/app/backup

[root@str1 ~]# scp /u01/app/backup/controlstb.ctl oracle@192.168.8.131:/u01/app/backup

[root@str1 ~]# scp /u01/app/backup/initprimary.ora oracle@192.168.8.131:/u01/app/backup

八、傳輸資料檔案

[root@str1 ~]# scp /u01/app/oradata/primary/* root@192.168.8.131:/u01/app/oradata/primary/ --standby建立primary目錄

主要資料庫暫時配置完畢

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

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

standby 配置

oracle使用者登入到standby伺服器

1、修改oracle環境變數

[root@str2 primary]# vi /home/oracle/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

umask 022

export ORACLE_BASE=/u01/app

export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1

export ORACLE_SID=standby

export PATH=$PATH:HOME/bin:$ORACLE_HOME/bin

2、將控制檔案複製到對應的目錄：cp /u01/app/backup/controlstb.ctl /u01/app/oradata/primary/control01.ctl

3、將密碼檔案複製到對應的目錄：cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary oracle@192.168.8.131:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprimary

4、待命伺服器相應目錄建立

mkdir -p $ORACLE_BASE/flash_recovery_area/primary

mkdir -p $ORACLE_BASE/oradata/primary

cd $ORACLE_BASE/admin/primary

[oracle@str2 standby]$ mkdir adump

m[oracle@str2 standby]$ mkdir bdump

[oracle@str2 standby]$ mkdir cdump

[oracle@str2 standby]$ mkdir dpdump

[oracle@str2 standby]$ mkdir pfile

[oracle@str2 standby]$ mkdir udump

5、修改初始化參數

[root@str2 primary]# vi /u01/app/backup/initstandby.ora

standby.__db_cache_size=197132288

standby.__java_pool_size=4194304

standby.__large_pool_size=4194304

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

standby.__pga_aggregate_target=104857600

standby.__sga_target=314572800

standby.__shared_io_pool_size=0

standby.__shared_pool_size=100663296

standby.__streams_pool_size=0

*.audit_file_dest='/u01/app/admin/primary/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oradata/primary/control01.ctl','/u01/app/flash_recovery_area/primary/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='primary'

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

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app'

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

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=314572800

*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME='standby'

log_archive_config='DG_CONFIG=(primary,standby)'

log_archive_dest_1='LOCATION=/u01/app/oradata/primary/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=primary

FAL_CLIENT=standby

STANDBY_FILE_MANAGEMENT=AUTO

6、修改待命伺服器tns.ora listen.ora資訊

[root@str2 primary]# vi /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.

primary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = primary)

)

)

standby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

EXTPROC_CONNECTION_DATA=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

7、重啟監聽 lsnrctl stop

lsnrctl start

8、測試監聽是否正常(也是判別密碼檔案是否正確傳到standby伺服器的好東東，避免許可權問題))

在主庫上面使用 sqlplus sys/oracle@standby as sysdba

sqlplus sys/oracle@primary as sysdba

9、用建立的備份伺服器參數啟動資料庫到nomount

startup pfile='/u01/app/backup/initstandby.ora' nomount

alter database mount standby database;

建立spfile檔案

create spfile from pfile='/u01/app/backup/initstandby.ora';

修改資料庫處於應用歸檔狀態

alter database recover managed standby database disconnect from session;

通過下列語句暫停redo應用。

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

唯讀模式開啟資料庫

alter database open read only;

配置完成

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

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

判斷配置是否成功，主要通過查看主要資料庫歸檔日誌的sequence是否一致 查看方法

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

MAX(SEQUENCE#)

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

55

通過在主庫執行alter system switch logfile; 切換日誌，可以觀察到備庫會自動應用通過主庫傳過來的日誌

SQL> alter system switch logfile;

查詢資料庫狀態 SQL> select status from v$instance;

查詢歸檔日誌是否應用應用需要點時間）SQL> select sequence#,applied from v$archived_log where applied='YES' order by sequence#;

查看主庫DG狀態：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

日誌測試：

在primary庫上執行

SQL> conn scott/tiger

Connected.

SQL> create table y(y int);

Table created.

SQL> insert into y values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba;

Connected.

SQL> alter system switch logfile;

System altered.

在standby 庫上執行

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

唯讀模式開啟資料庫

alter database open read only;

SQL> conn scott/tiger

Connected.

SQL> select * from y;

Y

----------

1

本文出自 “哇哢哢” 部落格，請務必保留此出處http://yangcheng.blog.51cto.com/1388193/1297625