ORACLE DG 的搭建 ( 方法二 )

說明：

備庫生成原理：

1 配置好主庫後將資料庫安裝檔案 /u01 ，備庫控制檔案，歸檔檔案打包傳送到備庫

2 備庫新增 oracle 使用者，組，附加組，解壓主庫的壓縮檔案

3 修改 SID ，目錄結構，引數檔案，網路檔案等相關配置

4 啟動備庫 mount(主備庫 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

[root@chen01 ~]# vim /etc/sysconfig/network

192.168.1.183

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

192.168.1.183 chen03

192.168.1.181 chen01

[root@chen01 ~]# vim /etc/sysconfig/network

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

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

備份引數檔案

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

File created.

1

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.

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

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

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

System altered.

( 最大 ARCn 程式數 )

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

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

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

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

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

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='/u01/app/oracle/oradata/db01/','/u01/app/oracle/oradata/orcl/' scope=spfile;

System altered.

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

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/db01/','/u01/app/oracle/oradata/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

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

System altered.

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

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

六 備份主庫資料檔案

192.168.1.181

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

SQL> shutdown immediate

[root@chen01 ~]# tar -zcvf arch.tar arch_orcl/

[root@chen01 ~]# tar -zcvf uu.tar /u01

[root@chen01 ~]# scp arch.tar 192.168.1.183:/root

[root@chen01 ~]# scp uu.tar 192.168.1.183:/root

[root@chen01 ~]# scp stdby_control01.ctl 192.168.1.183:/root

七 配置備庫 - 將 orcl 資料庫改成 db01 資料庫

[root@chen03 ~]# tar -zxvf arch.tar

[root@chen03 ~]# tar -zxvf uu.tar

[root@chen03 ~]# mkdir /u01

[root@chen03 ~]# mkdir arch_db01

[root@chen03 ~]# mkdir arch_cc

[root@chen03 ~]# mv u01/* /u01

[root@chen03 ~]# mv arch_orcl/* arch_db01/

[oracle@chen03 dbs]$ mv /u01/app/oracle/oradata/orcl /u01/app/oracle/oradata/db01/

[oracle@chen03 dbs]$ mv /u01/app/oracle/flash_recovery_area/orcl /u01/app/oracle/flash_recovery_area/db01/

[oracle@chen03 rdbms]$ mv /u01/app/oracle/diag/rdbms/orcl/ /u01/app/oracle/diag/rdbms/db01/

[oracle@chen03 rdbms]$ mv /u01/app/oracle/diag/rdbms/db01/orcl /u01/app/oracle/diag/rdbms/db01/db01

[root@chen03 ~]# cp stdby_control01.ctl stdby_control02.ctl

[root@chen03 ~]# cp stdby_control01.ctl /u01/app/oracle/oradata/db01

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

[root@chen03 ~]# chown -R oracle.oinstall /u01

[root@chen03 ~]# yum -y install compat-libstdc++-33-3.2.3 elfutils-libelf gcc gcc-c++* glibc glibc-common glibc-common glibc-devel libaio libaio-devel libgcc libstdc++-* libstdc++-devel make sysstat

建立一個使用者 oracle 要求主租 oinstall 附加組 dba

[root@chen03 ~]# groupdel oinstall

[root@chen03 ~]# groupdel dba

[root@chen03 ~]# groupadd -g 1000 oinstall

[root@chen03 ~]# groupadd -g 1001 dba

[root@chen03 ~]# useradd -u 1000 -g oinstall -G dba oracle

[oracle@chen03 ~]$ vim .bash_profile

export PATH

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0

export ORACLE_SID=db01

export PATH=$ORACLE_HOME/bin:$PATH

[oracle@chen03 ~]$ source .bash_profile

安全設定

[root@chen03 ~]# vim /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

核心設定

[root@chen03 ~]# vim /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

更新

[root@test1 orcl]# sysctl -p

配置備庫引數

[oracle@chen03 ~]$ cd $ORACLE_HOME/dbs

[oracle@chen03 ~]$ mv initorcl.ora initdb01.ora

[oracle@chen03 ~]$ mv orapworcl orapwdb01

[oracle@chen03 dbs]$ vim initdb01.ora

db01.__db_cache_size=176160768

db01.__java_pool_size=4194304

db01.__large_pool_size=4194304

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

db01.__pga_aggregate_target=239075328

db01.__sga_target=352321536

db01.__shared_io_pool_size=0

db01.__shared_pool_size=155189248

db01.__streams_pool_size=0

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

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

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/db01/'

*.db_name='orcl'

*.fal_client='DB_DB01'

*.fal_server='DB_ORCL'

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

*.log_archive_dest_1='location=/home/oracle/arch_db01 valid_for=(all_logfiles,all_roles) db_unique_name=db01'

*.log_archive_dest_2='service=db_orcl LGWR ASYNC valid_for=(online_logfiles,primary_roles) db_unique_name=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=5

*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/db01/'

*.processes=150*.remote_login_passwordfile='EXCLUSIVE'

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

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name='db01'

八 配置網路

xhost +

netmgr

或者

192.168.1.183

192.168.1.181 （同 183 ）

[oracle@chen03 admin]$ pwd

/u01/app/oracle/product/11.2.0/network/admin

[oracle@chen03 admin]$ ls

listener.ora orapworcl samples shrept.lst sqlnet.ora tnsnames.ora

[oracle@chen03 admin]$ vim listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = db01)

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

(SID_NAME = DB01)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@chen03 admin]$ vim tnsnames.ora

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

# Generated by Oracle configuration tools.

LISTENER_ORCL =

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

DB_DB01 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = db01)

)

DB_ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

[oracle@chen03 admin]$ tnsping db_orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 22:28:16

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 = (PROTOCOL = TCP)(HOST = 192.168.1.181)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (0 msec)

[oracle@chen03 admin]$ tnsping db_db01

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 22:28:19

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 = (PROTOCOL = TCP)(HOST = 192.168.1.183)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db01)))

OK (0 msec)

九 啟動 DG

1

啟用備用資料庫

SQL > STARTUP NOMOUNMT;

SQL >alter database mount standby database;

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

2

啟動主庫

SQL> startup

192.168.1.183

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

STANDBY_ARCHIVE_DEST VALID

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL

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

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

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

DG_安裝二

DG_安裝二

相關文章