1,环境
1.1,primary
ip: 192.168.12.210
db_name: orcl
db_unique_name: orcl
数据库版本: oracle 11g R2 32
操作系统: centos 6.2 (查询命令cat/etc/redhat-release)
1.2,standby
ip: 192.168.12.211
db_name: orcl
db_unique_name: orcl2
数据库版本: oracle 11g R2 32
操作系统: centos 6.2 (查询命令cat/etc/redhat-release)
2,准备工作
2.1,将主库至于force_logging
alter database force logging
查询是否设置成功:
SQL> select force_logging from v$database;
FOR
---
YES
2.2,将主库至于归档模式
shutdown immediateàstartup mountàalter database archivelogàalter database open
查询是否设置成功:
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/orcl2/standbylog
Oldest online log sequence 20
Next log sequence to archive 0
Current log sequence 21
2.3,修改监听(动态，静态任选一种,tnsname配置略)
建议设置静态监听
2.3.1,设置监听为动态监听模式
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =oracleTest211.domain)(PORT = 1521))
)
)
ADR_BASE_LISTENER1 = /u01/app/oracle
2.3.2设置为静态监听模式
(这种模式主要是能在nomount模式中以服务名的方式连接数据库)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = oracleTest210.domain)(PORT = 1521))
)
)
)
2.4打开远程访问
2.4.1检查修改sqlnet.ora文件
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (ALL)
说明:
none : 表示关闭操作系统认证，只能密码认证
all : 用于linux或unix平台，本机操作系统认证，异机使用密码文件认证
nts : 用于windows平台
2.4.2检查修改参数remote_login_passwordfile
alter system set remote_login_passwordfile= exclusive
说明:
none : 不使用密码文件认证
exclusive :要密码文件认证，自己独占使用(默认值)
shared ：要密码文件认证，不同实例dba用户可以共享密码文件
3,修改添加Primary初始化参数
说明:这里使用sql 语句修改的方式避免静态文件修改可能的误输入
alter system set db_unique_name='orcl' scope=spfile;
alter system setlog_archive_config='DG_CONFIG=(orcl,orcl2)';
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/archvalid_for=(all_logfiles,primary_role) db_unique_name=orcl';
alter system set log_archive_dest_2='SERVICE=orcl2 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orcl2';
alter system setlog_archive_dest_3='LOCATION=/u01/app/oracle/oradata/orcl/standbylogvalid_for=(standby_logfile,standby_role) db_unique_name=orcl' scope=spfile;
--其实可以不要log_archive_dest_3的配置，直接将log_archive_dest_1中的primary_role改为all_role也可以
--等同于设置STANDBY_ARCHIVE_DEST
alter system setlog_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/archvalid_for=(all_logfiles,all_role) db_unique_name=orcl';
alter system set db_file_name_convert='orcl2','orcl'scope=spfile;
--表示当本机变成standby时将primary的路径中的字符串“orcl2”替换成“orcl”
alter system set log_file_name_convert='orcl2','orcl'scope=spfile;
alter system set fal_client='orcl';
alter system set fal_server='orcl2';
alter system set standby_file_management=auto;
4,Primary中添加standbylog
alter database add standby logfile group 4 '/u01/app/oracle/oradata/orcl/standby04.log' size 50M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/orcl/standby05.log' size 50M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/orcl/standby06.log' size 50M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/orcl/standby07.log' size 50M;
5, Primary上创建静态初始化文件，密码文件
5.1创建pfile
createpfile='$ORACLE_HOME/dbs/initorclnew.ora' from spfile;
5.2创建密码文件
orapwd file='$ORACLE_HOME/dbs/orapworcl'password=abc123 entries=5 force=y;
查询密码文件中的用户
select * from v$pwfile_users;
6,SCP传输参数文件和密码文件到standby，并修改
6.1传输
scp initorclnew.oraorapworcl
6.2 standby上重命名参数文件和密码文件
mv initorclnew.ora initorcl2.ora
mv orapworcl orapworcl2
6.3,standby机器上新建合适的文件夹
mkdir/u01/app/oracle/oradata/orcl2/mkdir /u01/app/oracle/oradata/orcl2/arch
mkdir /u01/app/oracle/oradata/orcl2/standbylog
6.4,修改standby参数文件
vi initorcl2.ora
orcl.__db_cache_size=167772160
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
orcl.__pga_aggregate_target=201326592
orcl.__sga_target=293601280
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl2/control01.ctl','/u01/app/oracle/oradata/orcl2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='orcl2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.fal_client='orcl2'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcl,orcl2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl2/archvalid_for=(all_logfiles,primary_role) db_unique_name=orcl2'
*.log_archive_dest_2='SERVICE=orcl lgwr syncvalid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/orcl2/standbylogvalid_for=(standby_logfile,standby_role) db_unique_name=orcl2'
*.memory_target=492830720
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='orcl','orcl2'
*.log_file_name_convert='orcl','orcl2'
7两种恢复standby的方式
7.1rman duplicate操作复制数据库
rman target sys/abc123@orcl auxiliary sys/abc123@orcl2
duplicate target database for standby nofilenamecheck from activedatabase;
7.2复制数据库文件方式
7.2.1 Primary上创建standby控制文件
alter database create standby controlfile as '/u01/control01.ctl';
7.2.2 传输standby控制文件
scp /u01/control01.ctl oracle@192.168.12.211:/u01/app/oracle/oradata/orcl2/
7.2.3 关闭primary，传输数据文件
shutdown immediate
scp redo* standby0* system01.dbf undotbs01.dbf sysaux01.dbftemp01.dbf users01.dbf oracle@192.168.12.211:/u01/app/oracle/oradata/orcl2
8,将备库置于activedataguard模式下
8.1开始recover
primary startupàstandby startup standby 执行：
alter database recover managed standby database using current logfile disconnect from session;
sision;
8.2查询standby_log
select status from v$standby_log;
8.3修改dataguard的数据保护模式为最高可用性模式
主备库都要运行
alter database set standby database to maximize availability;
查询当前状态
select switchover_status,open_mode,database_role,db_unique_name,protection_mode,
protection_level from v$database
将备库shutdown后，主库的PROTECTION_LEVEL将变为RESYNCHRONIZATION
9.测试
主库:create sequence seq_test
create table test(id int)
insert into test values(seq_test.nextval)
commit;
备库:select * from test;
我的博客：