AIX5L 上Oracle 10gR2 Data Guard 搭建測試記錄

.............[@more@]

Oracle 10g data guard 實驗記錄

環境：
OS：AIX5.3ML05
DB: Oracle 10.2.0.2

命名規則
Host Database Type DB_UNIQUE_NAME TNS Alias
test192 Primary zhulch zhulch_test192
test148 Physical Standby naonao naonao_test148

1.建立源資料庫

- 安裝SW
- 建立監聽和資料庫
2.確認 主資料庫的歸檔模式

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradg/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

3. 建立密碼檔案
$cd $ORACLE_HOME/dbs
$orapwd file=orapwzhulch password=Not4u
SQL> show parameter remote

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE

4. 啟動強制日誌記錄
SQL> alter database force logging;

Database altered.

5. 建立備用資料庫的重做日誌
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 2 52428800 1 NO CURRENT
571986 09-NOV-07

2 1 0 52428800 1 YES UNUSED
0

3 1 1 52428800 1 YES INACTIVE
565658 09-NOV-07

SQL> alter database add standby logfile thread 1 ('/oradg/app/oracle/oradata/zh
ulch/standby.log') size 52428800;

Database altered.

6.配置主資料庫的初始化引數

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oradg/app/oracle/product/10.2
.0/dbs/spfilezhulch.ora
SQL> create pfile from spfile;

File created.

#Primary Role Parameter ##
*.DB_UNIQUE_NAME=zhulch
*.SERVICE_NAMES=zhulch
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(zhulch,naonao)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradg/zhulch/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zhulch'
*.LOG_ARCHIVE_DEST_2='SERVICE=naonao_test148 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=naonao'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=DEFER
##Standby Role Parameters ##
DB_FILE_NAME_CONVERT=('/oradg/zhulch/','/oradg/naonao/')
*.LOG_FILE_NAME_CONVERT=('/oradg/zhulch/','/oradg/naonao/')
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=naonao_test148
*.FAL_CLIENT=zhulch_test192

7.建立主資料庫的備份

tar 或者RMAN

8. 建立備用資料庫的控制檔案

alter database create standby controlfile as '/oradg/control_naonao.ctl';
RAMN>backup current controlfile for standby;

9.為備用資料庫建立初始化引數檔案

*control_files=（"/oradg/app/oracle/oradata/naonao/control_naonao.ctl")
*.DB_UNIQUE_NAME=naonao
*.SERVICE_NAMES=naonao
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(zhulch,naonao)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradg/naonao/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=naonao'
*.LOG_ARCHIVE_DEST_2='SERVICE=zhulch_test192 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zhulch'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
##Standby Role Parameters ##
*.DB_FILE_NAME_CONVERT=('/oradg/naonao/','/oradg/zhulch/')
*.LOG_FILE_NAME_CONVERT=('/oradg/naonao/','/oradg/zhulch/')
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=zhulch_test192
*.FAL_CLIENT=naonao_test148

10.將檔案傳送到備用資料庫

11. 配置備用資料庫主機

12. 建立備用資料庫的密碼檔案

$cd $ORACLE_HOME/dbs
$orapwd file=orapwnaonao password=Not4u


13.配置ORACLE NET 元件

NAONAO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test148)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = naonao)
)
)

LISTENER_NAONAO =
(ADDRESS = (PROTOCOL = TCP)(HOST = test148)(PORT = 1528))

zhulch_test192 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test192)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhulch)
)
)

naonao_test148 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test148)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = naonao)
)
)

14.啟動備用資料庫

/oradg/app/oracle/oradata
alter database create standby controlfile as '/oradg/app/oracle/oradata/zhulch/control_naonao.ctl'

SQL> startup mount
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size 2070912 bytes
Variable Size 318768768 bytes
Database Buffers 905969664 bytes
Redo Buffers 14704640 bytes
ORA-01103: database name 'ZHULCH' in control file is not 'NAONAO'

修改INITNAONAO.ORA 檔案

*.db_name='naonao' - >*.db_name='zhulch'

15.將主資料庫的重做日誌傳送給備用資料庫
alter system set log_archive_dest_state_2=enable scope=both;

alter system switch logfile;
select status,error from v$archive_dest where dest_id=2;

SQL> select status,error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -----------------------------------------------------------------
ERROR ORA-12545: Connect failed because target host or object does not
exist

zhulch:oradg] /oradg/app/oracle/oradata> tnsping naonao_test148

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production o
n 09-NOV-2007 17:17:19

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test148
)(PORT = 1528)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = naonao)))
TNS-12545: Connect failed because target host or object does not exist

test192
/etc/hosts
10.96.13.148 test148

test148
/etc/hosts
10.96.13.192 test192

zhulch:oradg] /home/oradg> tnsping naonao_test148

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production o
n 09-NOV-2007 17:18:48

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test148
)(PORT = 1528)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = naonao)))
OK (130 msec)

SQL> alter system switch logfile;

System altered.

SQL> select status,error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -----------------------------------------------------------------
VALID