当前位置: 首页 > 数据库 > oracle >

Oracle11gR2 ADG搭建记录

 
一:实验环境介绍
 
虚拟机系统:    RHEL Linux 6.4(64位)
数据库版本:    Oracle 11gR2 11.2.0.4 (64位)
 
IP地址规划:
 
主数据库 17.6.0.11 主机名 mydb
SID:pri
db_name:pri
db_unique_name:pri 主机名 mydbs
 
备份数据库 17.6.0.12
SID:std
db_name:pri
db_unique_name:std
 
二、搭建
 
1. 安装两台虚拟机,配置好网络,关闭防火墙、selinux,并分别安装oracle软件
 
2.主库、备库配置环境变量、监听
 
vim .bash_profile
 
export ORACLE_SID=pri
export ORACLE_BASE=/u01/app/oracle  
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1  
export TNS_ADMIN=$ORACLE_HOME/network/admin   
export PATH=/usr/sbin:$PATH  
export PATH=$ORACLE_HOME/bin:$PATH  
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib  
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib  
export LANG=en_US  
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK  
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'  
umask 022
 
netca 配置监听
 
3.主库操作 
 
dbca 主库创建实例pri
 
[oracle@mydb ~]$ sqlplus / as sysdba
 
-- 开启强制日志模式
 
SQL> alter database force logging;
 
Database altered.
 
SQL> select name,log_mode,force_logging from v$database;
 
NAME   LOG_MODE     FOR
--------- ------------ ---
PRI   NOARCHIVELOG YES
 
-- 创建密码文件
 
cd $ORACLE_HOME/dbs
$ ls
hc_pri.dat  init.ora  initpri.ora  lkPRI  orapwpri  snapcf_pri.f  spfilepri.ora 
$ orapwd file=orapwpri password=oracle force=y
#这条命令可以手动生成密码文件,force=y的意思是强制覆盖当前已有的密码文件(如果有可以不建立)
#将主库的密码文件copy给备库,并重命名
$ scp orapwpri 17.6.0.12:$ORACLE_HOME/dbs/orapwstd
 
 
-- 创建standby redolog日志组  
 
 
原则:
1:standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2:standby redo log日志文件组的个数依照下面的原则进行计算:
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
假如只有一个节点,这个节点有三组redolog,所以Standby redo log组数>=(3+1)*1 == 4
所以至少需要创建4组Standby redo log
 
# 查看当前线程与日志组的对应关系及日志组的大小
SQL>  select thread#,group#,bytes/1024/1024 from v$log;  
   THREAD#     GROUP# BYTES/1024/1024
---------- ---------- ---------------
1     1    50
1     2    50
1     3    50
 
SQL>  col member for a50
SQL>  select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/pri/redo03.log
2 /u01/app/oracle/oradata/pri/redo02.log
1 /u01/app/oracle/oradata/pri/redo01.log
 
 
# 新建4个日志组作为standby redolog日志组,大小与原来的日志组一致。由于已经存在group1-3,,所以group号只能从4开始
SYS@pri>
alter database add standby logfile group 4 '/u01/app/oracle/oradata/pri/std_redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/pri/std_redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/pri/std_redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/pri/std_redo07.log' size 50m; 
 
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
    GROUP#  SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------
4     0 UNASSIGNED       50
5     0 UNASSIGNED       50
6     0 UNASSIGNED       50
7     0 UNASSIGNED       50
SQL>  col member for a60
SQL> select group#,member from v$logfile order by group#;
 
    GROUP# MEMBER
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/pri/redo01.log
2 /u01/app/oracle/oradata/pri/redo02.log
3 /u01/app/oracle/oradata/pri/redo03.log
4 /u01/app/oracle/oradata/pri/std_redo04.log
5 /u01/app/oracle/oradata/pri/std_redo05.log
6 /u01/app/oracle/oradata/pri/std_redo06.log
7 /u01/app/oracle/oradata/pri/std_redo07.log
 
7 rows selected.
 
 
-- 修改主库spfile参数
 
 
SQL> show parameter spfile;
 
NAME TYPE     VALUE
------- ------   -----------
spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepri.ora
 
# 用spfile创建pfile,用于修改
SQL> create pfile from spfile;
 
# 修改主库的pfile:
[oracle@mydb dbs]$ cat initpri.ora 
 
pri.__db_cache_size=452984832
pri.__java_pool_size=16777216
pri.__large_pool_size=33554432
pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
pri.__pga_aggregate_target=520093696
pri.__sga_target=754974720
pri.__shared_io_pool_size=0
pri.__shared_pool_size=218103808
pri.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/pri/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/pri/control01.ctl','/u01/app/oracle/fast_rec
overy_area/pri/control02.ctl'*.db_block_size=8192
*.db_domain=''
*.db_name='pri'
*.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=priXDB)'
*.memory_target=1261436928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
 
新增以下内容:
*.db_unique_name='pri' DG主库和备库的db_name必须一致,db_unique_name不一致
*.log_archive_config='dg_config=(pri,std)' pri主数据库SID,std备份数据库SID
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=pri' 主数据库的归档日志路径和SID
*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std' 备份数据库的SID
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='std' 备份数据库的SID
*.fal_client='pri' 主数据库的SID
*.db_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一个目录是备份数据库数据文件路径(备份服务器上有此目录),第二个是主数据库数据文件路径
*.log_file_name_convert='/u01/app/oracle/oradata/std','/u01/app/oracle/oradata/pri' 第一个目录是备份数据库数据文件路径(备份服务器上有此目录),第二个是主数据库数据文件路径
*.standby_file_management='auto'
 
[oracle@mydb dbs]$ mkdir /u01/app/oracle/archivelog
 
 
-- 用修改过的pfile重新创建一个spfile,用于重启数据库
 
SQL> shutdown immediate
SQL> create spfile from pfile;
# 由于当前数据库已关闭,首先需要把数据库启动到mount状态
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/app/oracle/archivelog
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence        9
 
# 上面归档路径为/u01/app/oracle/arch,证明对pfile的修改已生效
 
-- 查看当前数据库是否使用spfile启动
 
SQL> show parameter spfile;
NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile      string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilepri.ora
 
# 看到spfile的路径,则证明数据库是使用spfile启动的,若没有值,则说明是用pfile启动的。
 
-- 再次确认数据库是否已经启用归档模式和强制日志模式:
 
SQL> select name,log_mode,force_logging from v$database;
NAME    LOG_MODE FOR
---------- ------------ ---
PRI    ARCHIVELOG YES
 
 
4.修改监听文件,添加静态监听,主备均做
 
4.1 主库
 
[oracle@mydb ~]$ cd $ORACLE_HOME/network/admin
[oracle@mydb admin]$ vim listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 17.6.0.11)(PORT = 1521))
    )
  )
# 下面为新加部分开始
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pri)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = pri)
    )
  )
# 新加部分结束
 
ADR_BASE_LISTENER = /u01/app/oracle
 
4.2 备库
 
[oracle@mydbs ~]$ cd $ORACLE_HOME/network/admin
[oracle@mydbs admin]$ vim listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 17.6.0.12)(PORT = 1521))
    )
  )
# 下面为新加部分开始
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = std)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = std)
    )
  )
# 新加部分结束
 
ADR_BASE_LISTENER = /u01/app/oracle
 
 
4.3 重启主库、备库监听
$ lsnrctl stop
$ lsnrctl start
 
 
5. 编辑网络服务名配置文件tnsnames.ora,主备均做
 
-- 主库
 
pri =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 17.6.0.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pri)
    )
  )
 
std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 17.6.0.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std)
    )
  )  
 
$ scp tnsnames.ora 17.6.0.12:$ORACLE_HOME/network/admin
 
-- 配置完后,确保在任意一端上都能tnsping通对方:
[oracle@mydb admin]$ tnsping std
[oracle@mydbs admin]$ tnsping pri
 
 
6. 备库上修改pfile参数文件 
 
[oracle@mydb dbs]$ scp initpri.ora 17.6.0.12:$ORACLE_HOME/dbs/initstd.ora
 
[oracle@mydbs dbs]$ ls
hc_orcl.dat  init.ora  initstd.ora  orapwstd
 
[oracle@mydbs dbs]$ vim initstd.ora 
 
pri.__db_cache_size=452984832
pri.__java_pool_size=16777216
pri.__large_pool_size=33554432
pri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
pri.__pga_aggregate_target=520093696
pri.__sga_target=754974720
pri.__shared_io_pool_size=0
pri.__shared_pool_size=218103808
pri.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/std/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/std/control01.ctl','/u01/app/oracle/fast_recovery_area/std/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pri'
*.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=priXDB)'
*.memory_target=1261436928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
# new
*.db_unique_name='std'
*.log_archive_config='dg_config=(pri,std)'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=std'
*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=pri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='pri'
*.fal_client='std'
*.db_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata/std'
*.log_file_name_convert='/u01/app/oracle/oradata/pri','/u01/app/oracle/oradata/std'
*.standby_file_management='auto'
 
-- 在备库端手工创建所需的目录
 
mkdir -pv /u01/app/oracle/admin/std/adump
mkdir -pv /u01/app/oracle/diag/rdbms/std/std/trace
mkdir -pv /u01/app/oracle/archivelog
mkdir -pv /u01/app/oracle/oradata/std
mkdir -pv /u01/app/oracle/fast_recovery_area/std
 
-- 用修改后的pfile创建spfile,然后启动备库
 
[oracle@mydbs ~]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount
 
 
7. 使用rman在备库上复制主库
 
[oracle@mydbs ~]$ rman target sys/oracle@pri auxiliary sys/oracle@std
 
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Mar 15 23:07:37 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PRI (DBID=965805966)
connected to auxiliary database: PRI (not mounted)
 
RMAN> duplicate target database for standby from active database nofilenamecheck;
# 这条命令可以直接恢复数据文件,standby控制文件,standby日志组,非常霸道
 
RMAN> exit
 
[oracle@mydbs ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 15 23:16:04 2020
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 status from v$instance;
 
STATUS
------------
MOUNTED #RMAN恢复完直接就是mount状态
 
SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/app/oracle/archivelog
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence    0
 
# 备库启动日志应用(停止日志应用的命令alter database recover managed standby database cancel;)
SQL> alter database recover managed standby database disconnect from session;
 
SQL> set pagesize 100
SQL> select sequence#,applied from v$archived_log order by 1;
 
 SEQUENCE# APPLIED
---------- ---------
11 YES
12 YES
13 YES
 
此时主库状态,处于OPEN,可读写,默认为最大性能模式:
 SQL>  select database_role, open_mode from v$database;
 
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
 
SQL> select status from v$instance;
 
STATUS
------------
OPEN
 
SQL> select PROTECTION_MODE from v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
 
8. 分别查看主库和备库的归档序列号是否一致
 
主库:
 
SQL> alter system switch logfile;
 
SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/app/oracle/archivelog
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence        15
 
备库:
 
SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/app/oracle/archivelog
Oldest online log sequence     13
Next log sequence to archive   0
Current log sequence        15
 
# 结果完全一致,至此,DataGuard的搭建完成。
 
四、查看
 
1.查看standby启动的DG进程
 
SQL> select process,client_process,sequence#,status from v$managed_standby; 
 
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH    15 CLOSING
ARCH   ARCH     0 CONNECTED //归档进程
ARCH   ARCH     0 CONNECTED
ARCH   ARCH    14 CLOSING
RFS   ARCH     0 IDLE
RFS   UNKNOWN     0 IDLE
RFS   UNKNOWN     0 IDLE
RFS   LGWR    16 IDLE //归档传输进程
MRP0   N/A    16 WAIT_FOR_LOG //日志应用进程
 
2.查看数据库的保护模式
 
# 主库
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
 
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE
 
# 备库
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
 
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  MOUNTED
 
3.备库查看DG的日志信息
 
SQL> select FACILITY,ERROR_CODE,CALLOUT,TIMESTAMP,MESSAGE from v$dataguard_status;
 
FACILITY ERROR_CODE CAL TIMESTAMP     MESSAGE
------------   ----------   --- --------   ---------------
Log Transport Services       16058 NO 2020/03/15 23:05:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:06:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:07:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:08:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:09:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:10:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:11:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:12:07 destination database instance is 'started' not 'mounted'
Log Transport Services       16058 NO 2020/03/15 23:13:07 destination database instance is 'started' not 'mounted'
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC0: Archival started
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC1: Archival started
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC2: Archival started
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC1: Becoming the 'no FAL' ARCH
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC2: Becoming the heartbeat ARCH
Log Transport Services   0 NO 2020/03/15 23:13:17 ARC2: Becoming the active heartbeat ARCH
Log Transport Services   0 NO 2020/03/15 23:13:18 ARC3: Archival started
Remote File Server   0 NO 2020/03/15 23:19:08 RFS[1]: Assigned to RFS process 3155
Remote File Server   0 NO 2020/03/15 23:19:08 RFS[2]: Assigned to RFS process 3157
Log Transport Services   0 YES 2020/03/15 23:19:11 ARC3: Beginning to archive thread 1 sequence 13 (1017653-1018062)
Log Transport Services   0 YES 2020/03/15 23:19:11 ARC3: Completed archiving thread 1 sequence 13 (0-0)
Remote File Server   0 NO 2020/03/15 23:19:11 Primary database is in MAXIMUM PERFORMANCE mode
Remote File Server   0 NO 2020/03/15 23:19:11 RFS[3]: Assigned to RFS process 3159
Log Apply Services   0 YES 2020/03/15 23:34:25 Attempt to start background Managed Standby Recovery process
Log Apply Services   0 YES 2020/03/15 23:34:25 MRP0: Background Managed Standby Recovery process started
Log Apply Services   0 NO 2020/03/15 23:34:30 Managed Standby Recovery not using Real Time Apply
Log Apply Services   0 NO 2020/03/15 23:34:31 Media Recovery Log /u01/app/oracle/archivelog/1_11_1035148752.dbf
Log Apply Services   0 NO 2020/03/15 23:34:31 Media Recovery Log /u01/app/oracle/archivelog/1_12_1035148752.dbf
Log Apply Services   0 NO 2020/03/15 23:34:32 Media Recovery Log /u01/app/oracle/archivelog/1_13_1035148752.dbf
Log Apply Services   0 NO 2020/03/15 23:34:32 Media Recovery Waiting for thread 1 sequence 14 (in transit)
Log Transport Services   0 YES 2020/03/15 23:37:53 ARC3: Beginning to archive thread 1 sequence 14 (1018062-1020259)
Log Transport Services   0 YES 2020/03/15 23:37:54 ARC3: Completed archiving thread 1 sequence 14 (0-0)
Log Apply Services   0 NO 2020/03/15 23:37:57 Media Recovery Log /u01/app/oracle/archivelog/1_14_1035148752.dbf
Log Apply Services   0 NO 2020/03/15 23:37:57 Media Recovery Waiting for thread 1 sequence 15 (in transit)
Log Transport Services   0 YES 2020/03/15 23:40:26 ARC0: Beginning to archive thread 1 sequence 15 (1020259-1020498)
Log Transport Services   0 YES 2020/03/15 23:40:26 ARC0: Completed archiving thread 1 sequence 15 (0-0)
Log Apply Services   0 NO 2020/03/15 23:40:27 Media Recovery Log /u01/app/oracle/archivelog/1_15_1035148752.dbf
Log Apply Services   0 NO 2020/03/15 23:40:27 Media Recovery Waiting for thread 1 sequence 16 (in transit)
 
37 rows selected.
 
4. Open Read Only standby数据库并且开启实时日志应用
SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database; 
DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL    OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ ONLY
#此时数据库状态时READ ONLY WITH APPLY,即只读且实时应用日志,通常所说的ADG,Active Data Guard。
 
SQL>  select process,client_process,sequence#,status from v$managed_standby; 
 
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH     0 CONNECTED
ARCH   ARCH     0 CONNECTED
ARCH   ARCH    16 CLOSING
ARCH   ARCH     0 CONNECTED
RFS   ARCH     0 IDLE
RFS   LGWR    17 IDLE
RFS   UNKNOWN    0 IDLE
 
 
SQL>  recover managed standby database using current logfile disconnect from session;
SQL>  select process,client_process,sequence#,status from v$managed_standby; 
 
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH     0 CONNECTED
ARCH   ARCH     0 CONNECTED
ARCH   ARCH    16 CLOSING
ARCH   ARCH     0 CONNECTED
RFS   ARCH     0 IDLE
RFS   LGWR    17 IDLE
RFS UNKNOWN     0 IDLE
MRP0   N/A    17 APPLYING_LOG
 
 
五、实验测试
 
1. 主从创建表、插入数据,从库查看
 
-- 主库
 
SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger 
SQL> select tname from tab;
 
TNAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
 
SQL> create table scott.cntest (name varchar2(20),passwd varchar2(30));
SQL> insert into scott.cntest values('cntest','123456');
SQL> commit;
 
 
-- 从库
 
SQL> conn scott/tiger
 
SQL> select tname from tab;
 
TNAME
------------------------------
BONUS
DEPT
EMP
HEFEI
SALGRADE
 
SQL> select *  from hefei;
 
NAME      PASSWD
-------------------- ------------------------------
hefei      123456
 
2. snapshot standby特性
 
在Oracle 11g中,data guard最吸引人的,除了active data guard的实时查询特性(即可以以只读方式打开物理standby数据库的同时MRP进程能继续做recover),快照备用数据库这个特性也是不错,比较适用于快速部署一个临时的与线上环境相同的测试数据库.它是通过还原点(restore point)和闪回数据库的原理(flashback database),可以以读/写方式打开物理备用数据库,对数据库进行修改,之后再根据还原点,恢复到物理备用数据库。
 
 
-- 切换物理备用数据库到快照备用数据库
 
SQL> select open_mode,database_role from v$database;
 
OPEN_MODE          DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
 
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to snapshot standby; //转换物理standby到快照standby
SQL> select status from v$instance; 
STATUS
-------
MOUNTED
 
SQL> alter database open;
SQL> select open_mode,database_role from v$database;
 
OPEN_MODE      DATABASE_ROLE
-------------------- ----------------
READ WRITE      SNAPSHOT STANDBY //查看当前备用数据库的角色
 
SQL> select name,storage_size from v$restore_point;
 
NAME STORAGE_SIZE
-----------------------------------------------  ------------
SNAPSHOT_STANDBY_REQUIRED_03/18/2020 12:34:07    52428800 //可以看到,oracle 有建了一个还原点
 
 
# 测试是否可以修改数据库,创建表
 
SQL> conn scott/tiger
SQL> create table anhui as select * from hefei;
SQL> select count(*) from anhui;
 
  COUNT(*)
----------
1
 
SQL> insert into anhui values('anhui','888888');
SQL> select count(*) from anhui;
 
  COUNT(*)
----------
2
 
 
SQL> commit;
Commit complete.
 
-- 切换快照备用数据库到物理备用数据库
 
SQL> select open_mode,database_role from v$database;
 
OPEN_MODE      DATABASE_ROLE
-------------------- ----------------
READ WRITE      SNAPSHOT STANDBY
 
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup
SQL> select status from v$instance;
 
STATUS
------------
OPEN
 
SQL> select open_mode,database_role from v$database;
 
OPEN_MODE      DATABASE_ROLE
-------------------- ----------------
READ ONLY      PHYSICAL STANDBY
 
 
SQL> alter database recover managed standby database disconnect;
SQL> select open_mode,database_role from v$database;
 
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
 
SQL> conn scott/tiger
Connected.
SQL> select tname from tab;
 
TNAME
------------------------------
BONUS
DEPT
EMP
HEFEI
SALGRADE
 
SQL> select *  from anhui;
select *  from anhui
               *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> select *  from hefei;
 
NAME      PASSWD
-------------------- ------------------------------
hefei      123456
 
 
//物理standby是最高保护模式(maximum protection),是不能转换为snapshot standby的.
 
3. 手工切换日志
 
# 主库,通过主库手工切换日志,来看看备库是否实时接收应用了日志,可以看出此时备库的最新日志SEQUENCE#是24号
 
SQL>  alter system archive log current;
 
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;
 
 SEQUENCE# FIRST_TIME        NEXT_TIME
---------- ------------------- -------------------
20 2020/03/18 12:33:32 2020/03/18 12:39:56
20 2020/03/18 12:33:32 2020/03/18 12:39:56
21 2020/03/18 12:39:56 2020/03/18 12:45:24
21 2020/03/18 12:39:56 2020/03/18 12:45:24
22 2020/03/18 12:45:24 2020/03/18 12:48:06
22 2020/03/18 12:45:24 2020/03/18 12:48:06
23 2020/03/18 12:48:06 2020/03/18 12:49:42
23 2020/03/18 12:48:06 2020/03/18 12:49:42
24 2020/03/18 12:49:42 2020/03/18 12:55:23
24 2020/03/18 12:49:42 2020/03/18 12:55:23
 
 
# 备库,此时查看备库,已经应用24号日志:
 
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;
 
 SEQUENCE# FIRST_TIME        NEXT_TIME
---------- ------------------- -------------------
15 2020/03/15 23:37:53 2020/03/15 23:40:25
16 2020/03/15 23:40:25 2020/03/15 23:53:59
17 2020/03/15 23:53:59 2020/03/15 23:57:12
18 2020/03/15 23:57:12 2020/03/18 12:29:51
19 2020/03/18 12:29:51 2020/03/18 12:33:32
20 2020/03/18 12:33:32 2020/03/18 12:39:56
21 2020/03/18 12:39:56 2020/03/18 12:45:24
22 2020/03/18 12:45:24 2020/03/18 12:48:06
23 2020/03/18 12:48:06 2020/03/18 12:49:42
24 2020/03/18 12:49:42 2020/03/18 12:55:23
 
 
4. DG切换与恢复
 
//配置DG的目的就是为了在主库出现故障时,备库能够提供服务,保证业务的正常运行。DG的故障切换分为switchover和failover两种:
 
4.1 switchover
 
# 主库
SQL> select switchover_status,database_role from v$database; 
 
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
SESSIONS ACTIVE      PRIMARY
 
//注意:上面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换
//如果是TO STANDBY表示可以正常切换,可以直接进入步骤b
//如果是SESSIONS ACTIVE表示当前有会话处于ACTIVE状态,进入步骤a
 
-- SWITCHOVER_STATUS 的值为TO STANDBY:
SQL> alter database commit to switchover to physical standby;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select database_role from v$database;
 
-- SWITCHOVER_STATUS 的值为SESSIONS ACTIVE:
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> startup mount;
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
 
当Primary Database收到这条switchover命令后,会发生这几件事情:
(1)这条命令执行完毕之后,主库上就不会产生Redo,所有DML相关的Cursor都会失效,用户也将不能再执行事务。
(2)每个日志线程的当前日志被归档,并在接下来的每个Thread新的日志头记录一个特殊的切换标准EOR(End of Redo),然后再次归档,其结果就是把EOR发送给所有Standby Database,Primary Database 转换成了Standby。
(3)在这个旧的Primary Database 上,MRP(Managed Recovery Process)进程会自动启动,,并应用最后一个归档日志,也就是EOR这个日志,一旦这个EOR应用完成,数据库就会Dismounted,并必须启动成一个Standby Database。
 
 
 
# 备库
 
SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
 
//将备库转换为主库
 
-- SWITCHOVER_STATUS 的值为TO PRIMARY,则:
SQL> alter database commit to switchover to primary;
 
-- SWITCHOVER_STATUS 的值为SESSIONS ACTIVE,则:
SQL> alter database commit to switchover to primary with session shutdown;
 
SQL> shutdown immediate;
SQL> startup
 
SQL> select open_mode,switchover_status,database_role from v$database;
 
OPEN_MODE      SWITCHOVER_STATUS   DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE      NOT ALLOWED   PRIMARY
 
 
# 在现在备库(原来主库)上启动日志传送进程
 
SQL> alter database recover managed standby database disconnect;
SQL> select open_mode,switchover_status,database_role from v$database;
 
OPEN_MODE      SWITCHOVER_STATUS   DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED      TO PRIMARY   PHYSICAL STANDBY
 
SQL> shutdown immediate
SQL> startup
SQL> select open_mode,switchover_status,database_role from v$database;
 
OPEN_MODE      SWITCHOVER_STATUS   DATABASE_ROLE
-------------------- -------------------- ----------------
READ ONLY      SESSIONS ACTIVE   PHYSICAL STANDBY
 
SQL> SQL> alter database recover managed standby database using current logfile disconnect from session;
 
 
4.2 FAILOVER
 
 
 
 
 
 
 
 
 
 
 
六、设置归档删除策略
 
可以设置主库的归档日志应用至备库后再删除的策略,一方面保证了归档日志传输接收,另一方面可以有效控制归档日志文件的产生量大小。默认是没有任何删除策略:
 
[oracle@mydb ~]$ rman target /
 
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 18 13:01:23 2020
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: PRI (DBID=965805966)
 
RMAN> show all;
 
RMAN configuration parameters for database with db_unique_name PRI are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_pri.f'; # default
 
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
 
//这里设置日志应用后,才删除归档。对于Oracle Data Guard 的Maximum Availability和 Maximum Performance两种模式下的主库归档文件的删除,必须是在归档文件在备库应用以后才可以删除。
 
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
 
 
 
相关知识补充:
 
1:DataGuard的三种数据保护模式:
 
(1)MAXIMIZE PROTECTION(最大保护模式):
 
最大数据保护与无数据分歧,LGWR将同时传送到备用节点,在主节点事务确认之前,备用节点也必须完全收到日志数据。如果网络不好,引起LGWR不能传送数据,将引起严重的性能问题,导致主节点DOWN机。
 
(2)MAXIMIZE AVAILABILITY(最大可用模式):
 
无数据丢失模式,允许数据分歧,允许异步传送。正常情况下运行在最大保护模式,在主节点与备用节点的网络断开或连接不正常时,自动切换到最大性能模式,主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。
 
(3)MAXIMIZE PERFORMANCE(最大性能模式):
 
这种模式应当可以说是从8i继承过来的备用服务器模式,异步传送,无数据同步检查,可能丢失数据,但是能获得主节点的最大性能。
 
 
 
 
 
 
------分隔线----------------------------
  • 收藏
  • 挑错
  • 推荐
  • 打印