一、primary (实例名:jzh) standby(实例名:jyp)
IP地址:192.168.1.200 192.168.1.199
主机名:orcl.oracle.com orc2.oracle.com
二、主库操作
1.开启归档
SYS@jzh>alter database archivelog;
Database altered.
SYS@jzh>alter database force logging;
2.添加standby logfile
alter database add standby logfile group 4 ('/u01/oracle/oradata/jzh/redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/oracle/oradata/jzh/redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/oracle/oradata/jzh/redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/oracle/oradata/jzh/redo07.log') size 50m
3.修改listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = jzh)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
(SID_DESC =
(GLOBAL_DBNAME = jzh_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/oracle
4.修改tnsnames.ora
jzh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jzh)
)
)
jyp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyp)
)
)
5.修改初始化参数文件
添加以下内容:
DB_NAME=jzh
DB_UNIQUE_NAME=jzh
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jzh,jyp)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_2=
'SERVICE=jyp ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jyp
FAL_CLIENT=jzh
DB_FILE_NAME_CONVERT='jyp','jzh'
LOG_FILE_NAME_CONVERT='jyp','jzh'
STANDBY_FILE_MANAGEMENT=AUTO
6.数据库启动至mount状态,创建spfile
SYS@jzh>create spfile from pfile;
7.创建standby controlfile,打开数据库
SYS@jzh>alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
SYS@jzh>alter database open;
8.将密码文件,listener.ora,tnsnames.ora传到备库
scp: %ORACLE_HOME/dbs/: No such file or directory
[oracle@orcl dbs]$ scp orapwjzh 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
orapwjzh 100% 1536 1.5KB/s 00:00
9.控制文件、初始化参数文件传到备库
[oracle@orcl dbs]$ scp initjzh.ora 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
initjzh.ora 100% 1468 1.4KB/s 00:00
三、备库操作
1.修改初始化参数文件
DB_NAME=jzh
DB_UNIQUE_NAME=jyp
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jyp,jzh)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_2=
'SERVICE=jzh ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jzh
FAL_CLIENT=jyp
2.解压缩包
[oracle@orc2 oradata]$ tar -xvf jzh.tar
jzh/
jzh/redo07.log
jzh/system01.dbf
jzh/redo03.log
jzh/undotbs01.dbf
jzh/redo01.log
jzh/redo02.log
jzh/users01.dbf
jzh/redo04.log
jzh/redo05.log
jzh/redo06.log
jzh/control01.ctl
jzh/temp01.dbf
jzh/sysaux01.dbf
3.修改路径
[oracle@orc2 oradata]$ mv jzh jyp
[oracle@orc2 oradata]$ ls
jyp jzh.tar
4.启动备库到mount状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
Database mounted.
5.应用日志
SQL> recover managed standby database disconnect from session;
Media recovery complete.
6.日志应用
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
13 YES
14 YES
15 YES
17 YES
16 YES
12 YES
18 YES
DATA GURAD至此搭建完成
四、配置dg_broker
两边实例设置dg_broker_start=true;
primary :
alter system set dg_broker_start=true;
standby :
alter system set dg_broker_start=true;
[oracle@orcl software]$ dgmgrl sys/oracle@jzh
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create CONFIGURATION macdb as primary database is jzh connect identifier is jzh;
Configuration "jzhdb" created with primary database "jzh"
DGMGRL> add database jyp as CONNECT IDENTIFIER IS jyp MAINTAINED AS PHYSICAL;
Database "jyp" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - jzhdb
Protection Mode: MaxPerformance
Databases:
jzh - Primary database
jyp - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to jyp
Performing switchover NOW, please wait...
New primary database "jyp" is opening...
Operation requires shutdown of instance "jzh" on database "jzh"
Shutting down instance "jzh"...
ORACLE instance shut down.
Operation requires startup of instance "jzh" on database "jzh"
Starting instance "jzh"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "jyp"
五、测试ADG
SYS@jzh>create table test as select * from dba_objects;
Table created.
SYS@jzh>select count(*) from test;
COUNT(*)
----------
74513
standby:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
74513
IP地址:192.168.1.200 192.168.1.199
主机名:orcl.oracle.com orc2.oracle.com
二、主库操作
1.开启归档
SYS@jzh>alter database archivelog;
Database altered.
SYS@jzh>alter database force logging;
2.添加standby logfile
alter database add standby logfile group 4 ('/u01/oracle/oradata/jzh/redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/oracle/oradata/jzh/redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/oracle/oradata/jzh/redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/oracle/oradata/jzh/redo07.log') size 50m
3.修改listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = jzh)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
(SID_DESC =
(GLOBAL_DBNAME = jzh_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/oracle
4.修改tnsnames.ora
jzh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jzh)
)
)
jyp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyp)
)
)
5.修改初始化参数文件
添加以下内容:
DB_NAME=jzh
DB_UNIQUE_NAME=jzh
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jzh,jyp)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_2=
'SERVICE=jyp ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jyp
FAL_CLIENT=jzh
DB_FILE_NAME_CONVERT='jyp','jzh'
LOG_FILE_NAME_CONVERT='jyp','jzh'
STANDBY_FILE_MANAGEMENT=AUTO
6.数据库启动至mount状态,创建spfile
SYS@jzh>create spfile from pfile;
7.创建standby controlfile,打开数据库
SYS@jzh>alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
SYS@jzh>alter database open;
8.将密码文件,listener.ora,tnsnames.ora传到备库
scp: %ORACLE_HOME/dbs/: No such file or directory
[oracle@orcl dbs]$ scp orapwjzh 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
orapwjzh 100% 1536 1.5KB/s 00:00
9.控制文件、初始化参数文件传到备库
[oracle@orcl dbs]$ scp initjzh.ora 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
initjzh.ora 100% 1468 1.4KB/s 00:00
三、备库操作
1.修改初始化参数文件
DB_NAME=jzh
DB_UNIQUE_NAME=jyp
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jyp,jzh)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_2=
'SERVICE=jzh ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jzh
FAL_CLIENT=jyp
2.解压缩包
[oracle@orc2 oradata]$ tar -xvf jzh.tar
jzh/
jzh/redo07.log
jzh/system01.dbf
jzh/redo03.log
jzh/undotbs01.dbf
jzh/redo01.log
jzh/redo02.log
jzh/users01.dbf
jzh/redo04.log
jzh/redo05.log
jzh/redo06.log
jzh/control01.ctl
jzh/temp01.dbf
jzh/sysaux01.dbf
3.修改路径
[oracle@orc2 oradata]$ mv jzh jyp
[oracle@orc2 oradata]$ ls
jyp jzh.tar
4.启动备库到mount状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
Database mounted.
5.应用日志
SQL> recover managed standby database disconnect from session;
Media recovery complete.
6.日志应用
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
13 YES
14 YES
15 YES
17 YES
16 YES
12 YES
18 YES
DATA GURAD至此搭建完成
四、配置dg_broker
两边实例设置dg_broker_start=true;
primary :
alter system set dg_broker_start=true;
standby :
alter system set dg_broker_start=true;
[oracle@orcl software]$ dgmgrl sys/oracle@jzh
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create CONFIGURATION macdb as primary database is jzh connect identifier is jzh;
Configuration "jzhdb" created with primary database "jzh"
DGMGRL> add database jyp as CONNECT IDENTIFIER IS jyp MAINTAINED AS PHYSICAL;
Database "jyp" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - jzhdb
Protection Mode: MaxPerformance
Databases:
jzh - Primary database
jyp - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to jyp
Performing switchover NOW, please wait...
New primary database "jyp" is opening...
Operation requires shutdown of instance "jzh" on database "jzh"
Shutting down instance "jzh"...
ORACLE instance shut down.
Operation requires startup of instance "jzh" on database "jzh"
Starting instance "jzh"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "jyp"
五、测试ADG
SYS@jzh>create table test as select * from dba_objects;
Table created.
SYS@jzh>select count(*) from test;
COUNT(*)
----------
74513
standby:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
74513
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10271187/viewspace-1264698/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10271187/viewspace-1264698/