搭建环境:
OS :RHEL 5.4 Oracle版本:10g R2 ORACLE_HOME=/u01/app/oracle
虚拟机1 hostname :dg1 IP:192.168.0.103
虚拟机2 hostname :dg2 IP:192.168.0.104
参数规划
数据库 db_name sid instance_name service_names db_unique_name fal_server fal_client
主 库 dg1 dg1 dg1 dg1 dg1 dg2 dg1
备库 dg1 dg1 dg1 dg2 dg2 dg1 dg2
主库上的操作
将主库至于foce logging状态
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 402653184 bytes
Fixed Size 1219664 bytes
Variable Size 113247152 bytes
Database Buffers 285212672 bytes
Redo Buffers 2973696 bytes
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
Database altered.
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode
并处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oradata/archive
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
查看需要拷贝的数据文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/dg1/system01.dbf
/u01/app/oradata/dg1/undotbs01.dbf
/u01/app/oradata/dg1/sysaux01.dbf
/u01/app/oradata/dg1/users01.dbf
/u01/app/oradata/dg1/example01.dbf
查看需要拷贝的临时文件
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE#
---------- ---------------- ------------------ ---------- ----------
STATUS ENABLED BYTES BLOCKS
--------------------- ------------------------------ ---------- ----------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------
1 446136 23-JAN-11 3 1
ONLINE READ WRITE 20971520 2560
20971520 8192
/u01/app/oradata/dg1/temp01.dbf
利用主库的控制文件创建备库的控制文件
SQL> alter database create standby controlfile as '/u01/app/oracle/dbs/standby.ctl';
Database altered.
利用主库的spfile创建pfile,主要是为了便于参数的修改
SQL> create pfile from spfile;
File created.
关闭主库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
在主库的pfile增加如下几行,其中红色的需要特别注意
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1 '
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2 '
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg1 '
*.db_name='dg1 '
*.db_unique_name='dg1 '
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB )'
*.fal_client='dg1 '
*.fal_server='dg2 '
将所需文件拷贝到dg2上,注意这里数据库一定要处于关闭状态
[oracle@dg1 dbs]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 dg1 localhost.localdomain localhost
192.168.0.104 dg2
192.168.0.103 dg1
拷贝数据文件和日志文件
[oracle@dg1 oradata]$ pwd
/u01/app/oradata
[oracle@dg1 oradata]$ ls
archive dg1
[oracle@dg1 oradata]$ scp -r dg1/ dg2:/u01/app/oradata/
拷贝admin下的文件,主要是为了拷贝这个目录的目录结构
[oracle@dg1 app]$ pwd
/u01/app
[oracle@dg1 app]$ ls
admin flash_recovery_area oracle oradata oraInventory
[oracle@dg1 app]$ scp -r admin/ dg2:/u01/app/
在主库上创建口令文件
[oracle@dg1 app]$ orapwd file=/u01/app/oracle/dbs/orapw password=oracle entries=5
拷贝口令文件,参数文件到备库上
[oracle@dg1 oracle]$ scp orapw initdg1 dg2:/u01/app/oracle/dbs
配置主库的listener.ora和tnsname.ora如下
listener.ora
[oracle@dg1 admin]$ pwd
/u01/app/oracle/network/admin
[oracle@dg1 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg1 )
(ORACLE_HOME = /u01/app/oracle )
# (PROGRAM = extproc)
(GLOBAL_DBNAME=dg1 )
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1 )(PORT = 1521))
)
)
tnsname.ora
DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.103 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg1 )
(INSTANCE_NAME = dg1 )
)
)
DG2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = dg1 )
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
利用pfile创建spfile
[oracle@dg1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 25 19:56:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
至此在主库上的操作告一段落
备库操作如下
修改pfile
*.control_files='/u01/app/oradata/dg1/standby.ctl' ##确保控制文件置于该目录下
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2 ' ##确保 /u01/app/oradata/archive该路径存在
*.log_archive_dest_2='SERVICE=dg2 LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1 '
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_start=true
*.service_names='dg2 '
*.db_name='dg1 '
*.db_unique_name='dg2 '
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB )'
*.fal_client='dg2 '
*.fal_server='dg1 '
配置备库上的listener.ora和tnsname.ora
lstener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg1 )
(GLOBAL_DBNAME= dg1 )
(ORACLE_HOME = /u01/app/oracle )
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.104 )(PORT = 1521))
)
)
tnsname.ora和主库上的一模一样,建议直接从主库上拷贝避免出错
重启监听程序
[oracle@dg1 admin]$ lsnrctl stop
[oracle@dg1 admin]$ lsnrctl start
至此备库上的操作完成
测试主库产生的归档日志是否能正常传送到归档日志
依次打开主库和备库
主库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
备库:
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
在主库上
SQL>archive log list;
SQL>alter system archive log current;
SQL>archive log list;
主库进行日志切换:
SQL>Alter system switch logfile;
然后分别查看主库和备库的 目录下是否产生了同样的归档日志文件。
执行如下SQL
select max(sequence#) from v$archived_log;
select max(sequence#) from v$log_history;
select group#,sequence#,archived,status from v$log;
select name,sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
亦可在主库进行一些DML操作然后在备库上进行验证,具体操作如下
主库
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
2 czm
3 czm
4 czm
SQL> insert into dg values(5,'czm');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
2 czm
3 czm
4 czm
5 czm
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
备库上
SQL> alter database recover managed standby database cancel;
SQL> select * from dg;
ID NAME
---------- ---------------------------------------------------------------------
1 czm
2 czm
3 czm
4 czm
5 czm
至此DG顺利搭建成功