Online configuration Oracle DataGuard 11.2.0.4.0 with oracle linux 6.10


11gR2搭建dataguard环境,此篇文章是利用rman搭建dataguard,这样的好处是primary不用停机,当然,前提条件是primary已经开启归档。
在primary搭建数据库软件,建立lsnrctl监听,采用dbca搭建实例,在standby上搭建数据库软件,建立监听,但是不需要采用dbca建立实例。
相对于可以停机,零停机传送数据文件的方式,不能冷备然后拷贝数据文件,只能利用rman技术备份,并恢复至standby中,当然rman备份恢复我只用了最简单的一种,一条命令备份,两条命令恢复。


环境信息介绍:
primary(正在运行,已经开启归档,可正常交易):
OS:OEL6.10
database:11.2.0.4.0
hostname:node150
172.16.1.150
ORACLE_SID=orcl
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
db_unique_name=orcl


standby(装好database软件,建立监听,没有创建数据库):
OS:OEL6.10
database:11.2.0.4.0
hostname:node151
172.16.1.151
ORACLE_SID=orcl
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
db_unique_name=orcldg

两边oracle用户的环境变量:
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
 
1.  primary已经处于归档状态(归档目录/u01/arch),然后打开force logging:
SQL> archive log list
Database log mode               Archive Mode
Automatic archival               Enabled
Archive destination               USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence               26
SQL>alter database force logging;

2. 配置listener与tnsnames,然后传至standby
(primary可以是动态监听,但是standby需要静态监听,因为动态监听需要数据库启动至mount状态才能监听得到。standby目前只有软件,没有实例,所以动态监听无法向外提供服务,以至于primary无法连接至standby的库,导致无法测试连通性,并向向standby传递归档)
node150
listner.ora:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
          (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
  )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node150)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle




node151:
listner.ora:
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
          (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
  )
)


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node151)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle
 


node150,node151 tnsnames.ora文件一样
tnsnames.ora:
# tnsnames.ora Network Configuration File: /u01/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.150)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
orcldg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 

3.两边打开监听
lsnrctl start
测试tnsnames.ora配置是否正确:
tnsping orcl
tnsping orcldg

此时可以测试两边是否连通
sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcldg as sysdba

4.配置primary参数,并添加standby logfile,以用来将来主备切换,standby logfile要比redo多一个组
此时的primary参数文件,里面没有相关dg的参数。
sql>create pfile='/tmp/pfile.ora' from spfile;
cat /tmp/pfile.ora
orcl.__db_cache_size=318767104
orcl.__java_pool_size=4194304
orcl.__large_pool_size=12582912
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=314572800
orcl.__sga_target=465567744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=117440512
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=780140544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
运行如下命令:具体参数含义在文章最后的附录表中,也可以查询官方文档,解释的更详细。
(不用设置primary的db_unique_name,默认就有)
alter system set log_archive_config= 'DG_CONFIG=(orcl,orcldg)';
alter system set log_archive_dest_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
alter system set log_archive_dest_2= 'SERVICE=orcldg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set fal_server=orcldg;
alter system set fal_client=orcl;
alter system set standby_file_management=AUTO;
添加standby redo: 
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/standby04.log')size 50m, group 5 ('/u01/app/oracle/oradata/orcl/standby05.log')size 50m, group 6 ('/u01/app/oracle/oradata/orcl/standby06.log')size 50m, group 7 ('/u01/app/oracle/oradata/orcl/standby07.log')size 50m;
 

5.创建standby controlfile、pfile
alter database create standby controlfile as '/u01/app/oracle/control01.ctl';
create pfile from spfile;
 
6.利用rman备份primary,做全备即可
[root@node150 ~]# mkdir -p /u01/backup  
[root@node150 ~]# chown oracle.oinstall /u01/backup
rman target /
backup database format '/u01/backup/full_%U'; 
 

配置SSH免密码登录
为oracle用户配置SSH:
生成用户的公匙和私匙,在每个节点上:
node150:
[root@node150 ~]# su - oracle
[oracle@node150 ~]# mkdir ~/.ssh
[oracle@node150 ~]#cd ~/.ssh
[oracle@node150 ~]# ssh-keygen -t rsa
[oracle@node150 ~]# ssh-keygen -t dsa
node151:
[root@node151 ~]# su - oracle
[oracle@node151 ~]# mkdir ~/.ssh
[oracle@node151 ~]#cd ~/.ssh
[oracle@node151 ~]# ssh-keygen -t rsa
[oracle@node151 ~]# ssh-keygen -t dsa
在节点1 上,把所有节点的authorized_keys文件合成一个,再用这个文件覆盖各个节点.ssh 下的同名文件:
[oracle@node150 ~]# touch authorized_keys
[oracle@node150 ~]# ssh node150 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
[oracle@node150 ~]# ssh node151 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
[oracle@node150 ~]# ssh node150 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
[oracle@node150 ~]# ssh node151 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys
[oracle@node150 ~]# scp authorized_keys node151:/home/oracle/.ssh/

分别在每个节点上执行检验操作:
[oracle@node150 ~]# ssh node150 date
[oracle@node150 ~]# ssh node151 date
[oracle@node150 ~]# ssh-agent $SHELL
[oracle@node150 ~]# ssh-add


7.把相关文件从primary传递至standby

监听文件:listener.ora、tnsnames.ora --$ORACLE_HOME/network/admin(在之前已经传过去了)
cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
scp listener.ora tnsnames.ora oracle@node151:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

参数文件:initorcl.ora  --$ORACLE_HOME/dbs
cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp initorcl.ora oracle@node151:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

密码文件:orapworcl  --$ORACLE_HOME/dbs
cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp orapworcl oracle@node151:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

standby控制文件:control01.ctl  --$ORACLE_BASE/oradata/orcl  
cd /u01/app/oracle/
node151: 
mkdir -p /u01/app/oracle/oradata/orcl/
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
node150:
scp control01.ctl oracle@node151:/u01/app/oracle/oradata/orcl/
scp control01.ctl oracle@node151:/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

在node151上创建日志目录 :
日志目录(或直接创建文件夹):
mkdir -p $ORACLE_BASE/admin
mkdir -p $ORACLE_BASE/diag
mkdir -p $ORACLE_BASE/flash_recovery_area
mkdir -p $ORACLE_BASE/admin/orcl/adump

备份文件:/u01/backup/* --/u01/backup/
node151:
mkdir -p /u01/backup/
chown oracle.oinstall /u01/backup/
node150:
scp /u01/backup/* oracle@node151:/u01/backup/


8.到standby数据库上修改standby参数文件
vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
orcl.__db_cache_size=314572800
orcl.__java_pool_size=4194304
orcl.__large_pool_size=12582912
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=314572800
orcl.__sga_target=465567744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=121634816
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcldg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcldg'
*.fal_server='orcl‘
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=550502400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
 
9.在standby服务器,启动至mount,然后rman恢复
[oracle@node151 2021_03_08]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 8 16:28:05 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  551165952 bytes
Fixed Size                  2255112 bytes
Variable Size             385877752 bytes
Database Buffers          155189248 bytes
Redo Buffers                7843840 bytes
SQL> alter database mount;
Database altered.
SQL> exit

[oracle@node151 dbs]$ rman target /
[oracle@node151 2021_03_08]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Mar 8 16:27:23 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1594426384)

RMAN> catalog start with '/u01/backup/';
Starting implicit crosscheck backup at 08-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 08-MAR-21

Starting implicit crosscheck copy at 08-MAR-21
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 08-MAR-21

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /u01/backup/

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/full_03pcdktt_1_1
File Name: /u01/backup/full_04pcdkvl_1_1
Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/full_03pcdktt_1_1
File Name: /u01/backup/full_04pcdkvl_1_1

RMAN> restore database;
Starting restore at 08-MAR-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_03pcdktt_1_1
channel ORA_DISK_1: piece handle=/u01/backup/full_03pcdktt_1_1 tag=TAG20140702T202533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-MAR-21
 
此时standby的/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/会出现归档。
可以在primary进行alter system switch logfile测试,归档必须同时出现在两端,这点很重要,不然standby无法进行恢复。



10.在standby数据库上进行standby日志的应用:
SQL>alter database recover managed standby database using current logfile disconnect from session;


11.验证
观察primary与standby的/u01/app/oracle/fast_recovery_area/ORCLDG/archivelog/
primary:alter system switch logfile;
SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
select sequence#,status from v$archived_log;--主备一致


14.standby可以选择是否启动read only with apply
                开启active dataguard(ADG)功能:
如果启动可以在standby库进行查询,同时从primary日志恢复(此为11g特性,10g如果read only打开standby库,不可以同时恢复)
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
到这里可以算是完成了,可以正常登录主库、备库进行数据查询。 



15.此时会发现一个现象,v$logfile中的文件可以查询得到,但是文件并不存在,这种情况可以忽略,主备互转的时候会自动创建。
SQL> col member for a30
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log   NO
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log   NO
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log   NO
         4         STANDBY /u01/app/oracle/oradata/orcl/redo04.log   NO
         5         STANDBY /u01/app/oracle/oradata/orcl/redo05.log   NO
         6         STANDBY /u01/app/oracle/oradata/orcl/redo06.log   NO
         7         STANDBY /u01/app/oracle/oradata/orcl/redo07.log   NO
7 rows selected.
 如果这时查看Standby数据库的切换状态为NOT ALLOWED,没有关系,做两次主从切换就会变成正常了
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

做了两次主从切换后:
主库:
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY

从库:
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

测试数据同步情况:
主库:
SQL> select * from rsc;
        ID
----------
         1
         2
         3
         4
         5
SQL> insert into rsc values(6);
1 row created.

SQL> commit;
Commit complete.

从库:
SQL> select * from rsc;
        ID
----------
         1
         2
         3
         4
         5
SQL> select * from rsc;
        ID
----------
         1
         2
         3
         4
         5
         6
6 rows selected.
主从数据同步正常:

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS