ORACLE DG 的搭建（方法一）

說明：

備庫生成原理：

1 安裝資料庫軟體 ( 和主庫目錄一致 )

2 主庫透過 rman 進行一次全備，併傳送到備庫

3 備庫透過 rman 恢復成和主庫一樣的資料庫

4 更改備庫相應的配置（主備庫 sid 不同）

主機

IP: 192.168.1.181

主機名 : chen01

OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)

oracle: 11.2.0.1.0

SID: orcl

備機

IP ： 192.168.1.183

主機名 : chen03

OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)

oracle: 11.2.0.1.0

SID: db01

一：主機 , 備機分別配置 hosts 檔案

192.168.1.181

[root@chen01 ~]# vim /etc/hosts

192.168.1.181 chen01

192.168.1.183 chen03

192.168.1.183

[root@chen03 ~]# vim /etc/hosts

192.168.1.183 chen03

192.168.1.181 chen01

二：主機修改為歸檔模式，強制寫日誌

192.168.1.181

SQL> startup mount

SQL> alter database archivelog;

SQL> select force_logging from v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR

---

YES

三：主機建立密碼檔案

192.168.1.181

[oracle@chen01 ~]$ cd $ORACLE_HOME/network/admin

[oracle@chen01 admin]$ orapwd file=orapworcl password=oracle

四：主機配置 standby redolog （最佳效能模式可以忽略，如果將來變成備庫且要轉為其它兩種模式則要建立）

192.168.1.181

standby redolog 的組數參考公式： (online redolog 組數 + 1) * 資料庫執行緒數；單機執行緒數為 1 ， RAC 一般為 2 。

standby redolog 的組成員數和大小也儘量和 online redolog 一樣。

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO

SQL> select group#,members,bytes/1024/1024||'M' M from v$log;

GROUP# MEMBERS M

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

1 1 50M

2 1 50M

3 1 50M

SQL> alter database add standby logfile

2 group 4 ('/u01/app/oracle/oradata/orcl/stdby_redo04.log') size 50M,

3 group 5 ('/u01/app/oracle/oradata/orcl/stdby_redo05.log') size 50M,

4 group 6 ('/u01/app/oracle/oradata/orcl/stdby_redo06.log') size 50M;

Database altered.

SQL> select * from v$logfile order by group#;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO

2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO

3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO

4 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo04.log NO

5 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo05.log NO

6 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo06.log NO

6 rows selected.

SQL> select group#,members,bytes/1024/1024||'M' M from v$log;

GROUP# MEMBERS M

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

1 1 50M

2 1 50M

3 1 50M

SQL> select group#,bytes/1004/1024||'M' M from v$standby_log;

GROUP# M

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

4 50.9960159362549800796812749003984063745M

5 50.9960159362549800796812749003984063745M

6 50.9960159362549800796812749003984063745M

五：設定主庫初始化引數

192.168.1.181

1 啟動 db 接受或傳送 redo data ，包括所有庫的 db_unique_name

SQL> show parameter db_unique_name

NAME TYPE VALUE

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

db_unique_name string orcl

SQL> alter system set log_archive_config='dg_config=(orcl,db01)' scope=spfile;

System altered.

2 主庫歸檔目的地

[oracle@chen01 ~]$ mkdir arch_orcl

[oracle@chen01 ~]$ mkdir arch_db01

SQL> alter system set log_archive_dest_1='location=/home/oracle/arch_orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_2='service=db_db01 LGWR ASYNC valid_for=(online_logfiles,primary_roles) db_unique_name=db01' scope=spfile;

System altered.

( 當該庫充當主庫角色時，設定物理備庫 redo data 的傳輸目的地 )

3 最大 ARCn 程式數

SQL> alter system set log_archive_max_processes=5 scope=spfile;

System altered.

4

SQL> alter system set log_archive_dest_state_1=ENABLE scope=spfile;

System altered.

SQL> alter system set log_archive_dest_state_2=ENABLE scope=spfile;

System altered.

5 exclusive or shared ，所有庫 sys 密碼要一致，預設是 exclusive

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

以下

是主庫切換為備庫，充當備庫角色時的一些引數設定，如果不打算做資料庫切換就不用設定了

6 配置網路服務名，假如轉換為備庫角色時，從這裡獲取丟失的歸檔檔案

SQL> alter system set fal_server=db_db01 scope=spfile;

System altered.

SQL> alter system set fal_client=db_orcl scope=spfile;

System altered.

（配置網路服務名， fal_server 複製丟失的歸檔檔案到這裡）

7 前為切換後的主庫路徑，後為切換後的備庫路徑，如果主備庫目錄結構完全一樣，則無需設定

SQL> alter system set db_file_name_convert='db01','orcl' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='db01','orcl' scope=spfile;

System altered.

同上，這兩個名字轉換引數是主備庫的路徑對映關係，可能會是路徑全名，看情況而定

8

SQL> alter system set standby_file_management=auto scope=spfile;

System altered.

（ auto 後當主庫的 datafiles 增刪時備庫也同樣自動操作，且會把日誌傳送到備庫 standby_archive_dest 引數指定的目錄下，

確保該目錄存在，如果你的儲存採用檔案系統沒有問題，但是如果採用了裸裝置，你就必須將該引數設定為 manual ）

9 一般和 LOG_ARCHIVE_DEST_1 的位置一樣，如果備庫採用 ARCH 傳輸方式，那麼主庫會把歸檔日誌傳到該目錄下

SQL> alter system set standby_archive_dest='location=/home/oracle/arch_orcl' scope=spfile;

System altered.

有了以上引數設定，則無論該庫充當主庫角色還是備庫角色都無需再修改了。

11 重啟資料庫，使引數生效

192.168.1.181

SQL> shutdown immediate;

SQL> startup

六 備份主庫資料檔案 ( 可以冷備，熱備 rman 備等 )

192.168.1.181

[oracle@chen01 ~]$ mkdir backup

[oracle@chen01 ~]$ lsnrctl stop

[oracle@chen01 ~]$ rman target /

RMAN> backup full database format '/home/oracle/backup/backup_%T_%s_%p.bak';

RMAN> sql "alter system archive log current";

RMAN> backup archivelog all format='/home/oracle/backup/arch_%T_%s_%p.bak';

七 主庫上建立備庫的控制檔案（控制檔案通常需要有多份，手工將檔案複製幾份）

192.168.1.181

SQL> alter database create standby controlfile as '/home/oracle/backup/stdby_control01.ctl';

Database altered.

[oracle@chen01 backup]$ cp stdby_control01.ctl stdby_control02.ctl

八 為備庫準備 init 引數檔案

備註：主庫和備庫的db_name 必須相同，為orcl

主庫和備庫的db_unique_name 必須不同，分別為orcl 和db01

192.168.1.181

1

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

File created.

2

[oracle@chen01 backup]$ unset LANG

[oracle@chen01 backup]$ vim initdb01.ora

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

*.control_files='/u01/app/oracle/oradata/db01/stdby_control01.ctl','/u01/app/ora

cle/flash_recovery_area/db01/stdby_control02.ctl'

*.db_name='orcl'

*.db_unique_name=’db01’

*.log_archive_config='dg_config=(db01,orcl)'

*.log_archive_dest_1='location=/home/oracle/arch_db01 valid_for=(all_logfiles,al

l_roles) db_unique_name=db01'

*.log_archive_dest_2='service=db_orcl LGWR ASYNC valid_for=(online_logfiles,prim

ary_roles) db_unique_name=orcl'

*.fal_client='DB_DB01'

*.fal_server='DB_ORCL'

*.log_file_name_convert='orcl','db01'

*.db_file_name_convert='orcl','db01'

*.standby_archive_dest='location=/home/oracle/arch_db01'

另外，如果備庫將來要開啟成只讀模式，需要確認 audit_trail 引數不是含 db ，應該設成 os 或 none 。

九 複製上面生成的檔案 backup_%T.bak 、 stdby_control01/02/03.ctl 、 initdb01.ora 到備庫所在主機

192.168.1.181

[oracle@chen01 ~]$ scp backup/* 192.168.1.183:/home/oracle/backup/

[oracle@chen01 backup]$ scp initdb01.ora 192.168.1.183:$ORACLE_HOME/dbs/

十 建立備庫的資料庫軟體，目錄及環境變數

192.168.1.183

[oracle@chen03 ~]$ /database/runInstaller

刪除資料庫orcl,db01

新建資料庫，只建立資料庫軟體

[oracle@chen03 ~]$ export ORACLE_BASE=/u01/app/oracle

[oracle@chen03 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0

[oracle@chen03 ~]$ export ORACLE_SID=db01

[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

[oracle@chen03 ~]$ mkdir -p /u01/app/oracle/oradata/$ORACLE_SID

[oracle@chen03 ~]$ cp backup/* /u01/app/oracle/oradata/db01/

[oracle@chen03 ~]$ cp backup/stdby_control02.ctl /u01/app/oracle/flash_recovery_area/db01/

十一 建立主庫監聽和主備庫的網路服務名（必須是 dedicated 的），並啟動監聽

192.168.1.181 192.168.1.183

[root@chen01 ~]# xhost +

[root@chen01 ~]# su - oracle

[oracle@chen01 ~]$ netmgr

刪除監聽 -- 增加監聽

刪除服務 -- 新建服務 網路服務名： db_orcl(192.168.1.181),db_db01(192.168.1.183)

關閉防火牆

iptables -F

sentenforce 0

確保主機和備機可以互通

[oracle@chen01 ~]$ tnsping db_orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 19:56:18

Used parameter files:

/u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (0 msec)

[oracle@chen01 ~]$ tnsping db_db01

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 19:56:21

Used parameter files:

/u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

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

OK (10 msec)

十二 配置並啟動備庫

192.168.1.183

[oracle@chen03 dbs]$ orapwd file=orapwdb01 password=oracle -- 和主機密碼必須相同

SQL> create spfile from pfile;

File created.

SQL> startup nomount

SQL> alter database mount standby database;

Database altered.

[oracle@chen03 dbs]$ rman target /

RMAN> restore database; --- 主庫，備庫目錄一致才能保證恢復成功

RMAN> restore archivelog all;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

db01 MOUNTED

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

db01 MOUNTED

十三 檢查主庫備庫是否配置成功

192.168.1.181

SQL> select dest_name,status from v$archive_dest_status;

DEST_NAME STATUS

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

LOG_ARCHIVE_DEST_1 VALID ------1 和 2 必須確保都是 VALID

LOG_ARCHIVE_DEST_2 VALID

如何不是 VALID 可能的原因有：

1 防火牆 iptables 和 selinux 沒有關

2 監聽沒有啟動或配置不合理

3 引數配置不合理

192.168.1.181

SQL> col dest_name for a25

SQL> select dest_name,status from v$archive_dest_status;

DEST_NAME STATUS

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 VALID

十四 在備庫上 , 啟動 redo apply --- --- 啟動管理恢復程式

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

Database altered.

十五 主庫歸檔測試

主庫：

192.168.1.181

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/arch_orcl

Oldest online log sequence 26

Next log sequence to archive 28

Current log sequence 28

備庫

192.168.1.183

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/arch_db01

Oldest online log sequence 11

Next log sequence to archive 0

Current log sequence 28

主庫歸檔後

192.168.1.181

SQL> alter system switch logfile;-- 對單例項資料庫或 RAC 中的當前例項執行日誌切換

System altered.

SQL> alter system archive log current; -- 對資料庫中的所有例項執行日誌切換

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/arch_orcl

Oldest online log sequence 28

Next log sequence to archive 30

Current log sequence 30

此時備庫

192.168.1.183

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/arch_db01

Oldest online log sequence 28

Next log sequence to archive 0

Current log sequence 30

===============================================================================

啟動備庫失敗及解決辦法

192.168.1.183

[oracle@chen03 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 15 01:52:25 2014

Enter user-name: /as sysdba

ERROR:

ORA-01075:

[oracle@chen03 dbs]$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}'

[oracle@chen03 dbs]$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9

[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}'

851986

884755

917524

[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm

resource(s) deleted

[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}'

[oracle@chen03 dbs]$ ps -ef | grep ora_

oracle 9425 9125 0 02:02 pts/1 00:00:00 grep ora_

http://blog.csdn.net/wyzxg/article/details/7280223

SQL> create pfile from spfile;

File created.

SQL> alter database mount;

alter database mount

*

? 1 ?????:

ORA-00205: ?????????, ??????, ???????

SQL> alter session set nls_language=american;

Session altered.

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-00205: error in identifying control file, check alert log for more info

===============================================================================

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

DG_安裝一

DG_安裝一

相關文章