环境:
centos 6.4 + oracle 11.2
DG配置:
主库
主机名:dg1
数据库:test
unique name: ptest
ip:192.168.190.111
备库:
主机名:dg2
数据库:test
unique name: stest
ip:192.168.190.112
系统配置:
①配置主机名和hosts解析文件
主库和备库都要设置
主库:
[root@dg1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.190.111 dg1
192.168.190.112 dg2
[root@dg1 ~]# cat /etc/sysconfig/network
network networking/ network-scripts/
[root@dg1~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=dg1
备库:
[root@dg2~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.190.111 dg1
192.168.190.112 dg2
[root@dg2 ~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=dg2
②selinux和ipstables
主库
[root@dg1 ~]# getenforce
Permissive
[root@dg1~]# service iptables status
iptables:未运行防火墙。
备库:
[root@dg2~]# getenforce
Permissive
[root@dg2 ~]# service iptables status
iptables:未运行防火墙。
③网络互通
[root@dg1~]# ping dg2
PING dgstandby (192.168.190.112) 56(84) bytes of data.
64 bytes from dgstandby (192.168.190.112): icmp_seq=1 ttl=64 time=0.645 ms
64 bytes from dgstandby (192.168.190.112): icmp_seq=2 ttl=64 time=0.535 ms
[root@dg2 ~]# ping dg1
PING dgmaster (192.168.190.111) 56(84) bytes of data.
64 bytes from dgmaster (192.168.190.111): icmp_seq=1 ttl=64 time=1.73 ms
64 bytes from dgmaster (192.168.190.111): icmp_seq=2 ttl=64 time=1.19 ms
环境变量(主库,备库都一样):
[oracle@dg1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11g
export ORACLE_SID=test
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
主库配置:
1.设置主库db_unique_name:
SQL> alter system set db_unique_name=ptest scope=spfile;
2.设置主库为强制记录日志:
SQL> alter database force logging;
3.设置standy_file_management:
SQL> alter system set standby_file_management =’AUTO’;
4.创建standbylog:
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/test/redo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/test/redo05.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/test/redo06.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/test/redo07.log' size 50m;
Database altered.
5.开启DGbroker:
SQL> alter system set DG_BROKER_START=TRUE;
System altered.
6.设置db_file_name_convert ,log_file_name_convert参数
SQL> alter system set db_file_name_convert='/u01/oracle/oradata/test','/u01/oracle/oradata/test','/u01/oracle/fast_recovery_area/test','/u01/oracle/fast_recovery_area/test' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/oracle/oradata/test','/u01/oracle/oradata/test','/u01/oracle/fast_recovery_area/test','/u01/oracle/fast_recovery_area/test' scope=spfile;
6.开启归档
[oracle@dg1 test]$ mkdir archivelog
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set log_archive_dest_1='location=/u01/oracle/oradata/test/archivelog';
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/oradata/test/archivelog
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
7.生成pfile
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/oracle/11g/dbs/spfiletest
.ora
SQL> create pfile from spfile;
File created.
8.设置监听listener和tnsnames文件
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = ptest)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = ptest_DGMGRL)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
)
ADR_BASE_LISTENER = /u01/oracle
[oracle@dg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
PTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
STEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
监听状态:
Service "ptest" has 2 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Instance "test", status READY, has 1 handler(s) for this service...
Service "ptest_DGMGRL" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
备库配置
1.检查ORACLE_SID:
[oracle@dg2 ~]
echo
e
c
h
o
ORACLE_SID
test
2.拷贝主库必要文件及文件夹到备库
监听文件:
[oracle@dg2 ~]$ cd /u01/oracle/11g/network/admin/
[oracle@dg2 admin]$ scp -r 192.168.190.111:/u01/oracle/11g/network/admin/listener.ora ./
[oracle@dg2 admin]$ scp -r 192.168.190.111:/u01/oracle/11g/network/admin/tnsnames.ora ./
必要的文件及目录创建和copy:
[oracle@dg2 admin]$ cd /u01/oracle/11g/dbs/
[oracle@dg2 dbs]$ scp -r 192.168.190.111:/u01/oracle/11g/dbs/inittest.ora ./
[oracle@dg2 dbs]$ scp -r 192.168.190.111:/u01/oracle/11g/dbs/orapwtest ./
[oracle@dg2 dbs]$ cd /u01/oracle/
[oracle@dg2 oracle]$ scp -r 192.168.190.111:/u01/oracle/admin ./
[oracle@dg2 oracle]$ scp -r 192.168.190.111:/u01/oracle/fast_recovery_area ./
[oracle@dg2 oracle]$ mkdir -pv oradata/test/archivelog/
3.设置listener和tnsnames文件
[oracle@dg2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = stest)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = stest_DGMGRL)
(ORACLE_HOME = /u01/oracle/11g)
(SID_NAME = test)
)
)
ADR_BASE_LISTENER = /u01/oracle
[oracle@dg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
PTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
STEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
4.修改参数文件inittest.ora里的参数db_unique_name 为stest,启动备库到nomount阶段,且启用监听
SQL>create spfile from pfile;
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 427820072 bytes
Database Buffers 188743680 bytes
Redo Buffers 7507968 bytes
SQL> show parameter db_un
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STEST
[oracle@dg2 dbs]$ lsnrctl start
Services Summary...
Service "stest" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "stest_DGMGRL" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
5.rman 克隆数据库
[oracle@dg2 dbs]$ rman target sys/123456@ptest auxiliary sys/123456@stest nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 20 15:05:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2210486398)
using target database control file instead of recovery catalog
connected to auxiliary database: TEST (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
......
......
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=912352058 file name=/u01/oracle/oradata/test/users01.dbf
Finished Duplicate Db at 20-MAY-16
6.检查数据库状态
[oracle@dg2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 20 15:08:24 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST MOUNTED PHYSICAL STANDBY
SQL> show parameter db_un
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STEST
主库dg_broker操作:
主库:
[oracle@dg1 admin]$ dgmgrl /
DGMGRL> create configuration dg_test as primary database is ptest connect identifier is ptest;
Configuration "dg_test" created with primary database "ptest"
DGMGRL> add database stest as connect identifier is stest maintained as physical;
Database "stest" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> enable database ptest;
Enabled.
DGMGRL> enable database stest;
Enabled.
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
ptest - Primary database
stest - Physical standby database
Error: ORA-16797: database is not using a server parameter file
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
备库操作:
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
SQL> startup
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST STEST READ ONLY WITH APPLY PHYSICAL STANDBY
回到主库dgmgrl:
再查看状态
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
ptest - Primary database
stest - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL> show database smobaov3 statusreport;
Object "smobaov3" was not found
DGMGRL> show database stest statusreport;
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
test WARNING ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
test WARNING ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
test WARNING ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
DGMGRL> EDIT DATABASE stest SET PROPERTY LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated
DGMGRL> EDIT DATABASE stest SET PROPERTY LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated
DGMGRL> EDIT DATABASE stest SET PROPERTY ArchiveLagTarget=0;
Property "archivelagtarget" updated
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
ptest - Primary database
stest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
开始切换:
[oracle@dg1 admin]$ dgmgrl sys/123456@ptest -----必须这样的登陆方式才可以
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
ptest - Primary database
stest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to stest
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "stest"
Connecting to instance "test"...
Connected.
New primary database "stest" is opening...
Operation requires startup of instance "test" on database "ptest"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "stest"
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
stest - Primary database
ptest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to ptest
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "ptest"
Connecting to instance "test"...
Connected.
New primary database "ptest" is opening...
Operation requires startup of instance "test" on database "stest"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ptest"
DGMGRL> show configuration
Configuration - dg_test
Protection Mode: MaxPerformance
Databases:
ptest - Primary database
stest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
补充
备库执行:
SQL> alter database open; ———–11g ADG特性 启动到 read-only状态
Database altered.
SQL> recover managed standby database using current logfile disconnect from session; ———-备库启用日志应用功能
Media recovery complete.
备库注册归档:
alter database register physical logfile ‘/home/oracle/log_1_7354_895934230.arc’;
备库查看DG进程
select process from v$managed_standby;