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