ora11_node_dg（1）DG搭建过程

最新推荐文章于 2023-03-28 16:31:29 发布

cuiyong8723

最新推荐文章于 2023-03-28 16:31:29 发布

阅读量172

点赞数

文章标签：数据库操作系统网络

Oracle 11g Active DataGuard 搭建配置过程
一、环境介绍:
我在 ora11-node1 和 ora11-node2 两台Linux虚拟机上首先分别安装了一套数据库软件，
在 ora11-node1 主机上创建了名为zzdb的数据库。
ora11-node1 10.0.0.31
ora11-node2 10.0.0.32
[oracle@ora11-node2 ~]$ uname -a
Linux ora11-node2 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[oracle@ora11-node2 ~]$ cat /etc/redhat-release
CentOS release 6.7 (Final)
二、11g ADG部署：
1、primary端配置监听
cat > $ORACLE_HOME/network/admin/listener.ora << EOF
# listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = zzdb)
(SID_NAME = zzdb )
(ORACLE_HOME = /oracle/product/11.2.0/db_1 )
)
)
ADR_BASE_LISTENER = /oracle/product
EOF
cat > $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
zzdb_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11-node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zzdb_dg)
)
)
zzdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11-node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zzdb)
)
)
EOF
2、修改primary端初始化参数文件
startup mount;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive' scope=both;
alter database force logging;
alter database open;
alter system set log_archive_config = 'DG_CONFIG=(zzdb,zzdb_dg)' scope=both;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zzdb' scope=both;
alter system set log_archive_dest_2 = 'SERVICE=zzdb_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzdb_dg' scope=both;
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set fal_server=zzdb_dg scope=both;
alter system set fal_client=zzdb scope=both;
alter system set standby_file_management=AUTO scope=both;
alter system set db_file_name_convert='/oracle/product/oradata/zzdb','/oracle/product/oradata/zzdb' scope=spfile;
alter system set log_file_name_convert='/oracle/product/oradata/zzdb','/oracle/product/oradata/zzdb' scope=spfile;
3、在primary端pfile参数文件和密码文件，并且拷贝到standby段相应位置
SQL> create pfile from spfile;
File created.
[oracle@ora11-node1 oracle]$ cd $ORACLE_HOME/dbs
[oracle@ora11-node1 dbs]$ scp initzzdb.ora orapwzzdb ora11-node2:$ORACLE_HOME/dbs
cd $ORACLE_BASE
[oracle@ora11-node1 product]$ scp -r admin/ diag/ ora11-node2:$ORACLE_BASE
4、修改standby端的监听文件及初始化参数文件
--启动监听
[oracle@ora11-node2 dbs]$ lsnrctl start
--修改参数文件
[oracle@ora11-node2 ~]$ sqlplus / as sysdba
SQL> startup nomount;
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initzzdb.ora';
SQL> shutdown abort;
SQL> startup nomount;
SQL>
alter system set db_unique_name=zzdb_dg scope=spfile;
alter system set log_archive_config='DG_CONFIG=(zzdb,zzdb_dg)' scope=both;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zzdb_dg' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=zzdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzdb' scope=spfile;
alter system set fal_server=zzdb scope=spfile;
alter system set fal_client=zzdb_dg scope=spfile;
SQL> shutdown abort;
SQL> startup nomount;
5、在primary端通过Rman创建备库,在 ora11-node1 上执行如下命令
#primary备份数据库
mkdir -p /oracle/db_backup
rman target / <<!
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
backup database format '/oracle/db_backup/%d_%s_%p_%t.%T';
}
!
# primary生成备库控制文件
rman target / <<!
run {
allocate channel c1 device type disk format '/oracle/db_backup/CON_%U';
backup current controlfile for standby;
}
!
# cp备份文件和控制文件到standby
cd /oracle/db_backup
[oracle@ora11-node1 db_backup]$ scp * ora11-node2:/oracle/db_backup
#standby节点,创建目录
mkdir -p /oracle/db_backup
mkdir -p /oracle/product/oradata/zzdb
[oracle@ora11-node2 db_backup]$ ll /oracle/db_backup
总用量 1213996
-rw-r----- 1 oracle oinstall 9797632 12月 29 10:23 CON_03qq19bv_1_1
-rw-r----- 1 oracle oinstall 1223499776 12月 29 10:24 ZZDB_1_1_899720408.20151229
-rw-r----- 1 oracle oinstall 9830400 12月 29 10:24 ZZDB_2_1_899720556.20151229
[oracle@ora11-node2 db_backup]$
#standby节点，恢复控制文件
rman target / <<!
run {
restore controlfile from '/oracle/db_backup/CON_03qq19bv_1_1';
alter database mount;
}
!
#standby节点，通过rman进行恢复
rman target / <<!
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
restore database;
!
#至此已经恢复完成
6、在 primary 和 standby 端添加 standby日志
SQL> alter database add standby logfile
group 4 ('/oracle/product/oradata/zzdb/zzdb_dgredo04.log') size 50m,
group 5 ('/oracle/product/oradata/zzdb/zzdb_dgredo05.log') size 50m,
group 6 ('/oracle/product/oradata/zzdb/zzdb_dgredo06.log') size 50m,
group 7 ('/oracle/product/oradata/zzdb/zzdb_dgredo07.log') size 50m;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
7、在standby端开启实时日志应用
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session;
---11g 的新功能，它允许物理备库在应用日志时处于只读打开状态，执行以下步骤
startup mount
alter database open;
alter database recover managed standby database using current logfile disconnect;
三、测试ADG
8、执行日志切换测试（在pri端切换归档，在节点二上检查是否也发生了切换）
alter system switch logfile;
archive log list;
9、查看standby启动的DG进程
SQL> select process,client_process,sequence#,status from v$managed_standby;
10、查看数据库的保护模式：
#primary端查看，我们可以看到数据库的保护模式为最大性能
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
#standby 端查看，也是一样的。
11、查看DG的日志信息
SQL> select * from v$dataguard_status;
12、在standby端， Open Read Only standby数据库并且开启实时日志应用
SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> alter database recover managed standby database using current logfile disconnect from session;
13、解锁scott用户，添加数据，验证数据是否能同步
#在primary端创建解锁scott用户并创建测试表t1，插入20000行数据
#primary 端操作如下内容
SQL> set line 100
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
SQL> alter user scott account unlock identified by tiger;
SQL> conn scott/tiger
SQL> show user
USER is "SCOTT"
SQL> create table t1 (id int primary key, birth char(8));
SQL>
declare
i int;
v_birth varchar2(8);
begin
delete t1;
commit;
for i in 1..20000
loop
insert into t1(id,birth)
values (i ,to_char(to_date('19850101','yyyymmdd')+i,'yyyymmdd'));
commit;
end loop;
end ;
/
#standby端查询scott用户是否解锁，以及t1表是否创建并且插入了10000行数据：
SQL> conn scott/tiger;
SQL> select count(*) from t1;
#至此Oracle 11g ADG就已经配置完成了