Dataguard单机—>单机

本演示案例所用环境：

	primary	Standby
OS Hostname	CHINA-DB1	CHINA-DB2
OS Version	SUSE Linux Enterprise Server 11 SP4	SUSE Linux Enterprise Server 11 SP4
DB Version	11.2.0.4	11.2.0.4
db_name	hankey	hankey
db_unique_name	hankey	skydbstd
service_names	hankey	skydbstd
instance_name	hankey	skydbstd

 

一、主库配置：

1.1、       主库启用强制归档

sqlplus / as sysdba

select force_logging from v$database;

alter database force logging;

1.2、配置客户端的网络服务

1.2.1、修改配置文件

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

HANKEY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hankey)

    )

  )

 

SKYDBSTD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = skydbstd)

    )

  )

1.2.2、验证

tnsping命令解析网络服务名,命令须返回OK.

$ tnsping hankey

 

1.3、密码文件

若无密码文件，则创建密码文件。格式：orapw<ORACLE_SID>

cd $ORACLE_HOME/dbs

orapwd file=orapwhankey password=oracle ignorecase=y force=y

此处的password=oracle是sys的密码

1.4、修改redo大小

1.4.1、查当前环境的logfile

 

如果需要更改redo的大小可参考，否则忽略该步骤

 

set lines 200 pages 300
col member for a60
select a.thread#,a.group#,b.member,b.type,a.status,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type, a.status,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#;

   THREAD#     GROUP# MEMBER                               TYPE    STATUS            MB
---------- ---------- ------------------------------------------------------------ ------- ---------------- ----------
     1        3 /home/oracle/oradata/orcl/redo03.log               ONLINE  CURRENT            50
     1        2 /home/oracle/oradata/orcl/redo02.log               ONLINE  INACTIVE            50
     1        1 /home/oracle/oradata/orcl/redo01.log               ONLINE  INACTIVE            50

SQL> 

1.4.2、添加新的redo

为了避免后期日志切换太频繁，此处增加在线日志文件大小为200M，然后删除之前50M的文件。

alter database add logfile
     group 4 '/opt/oradata/hankey/redo04.log' size 200M,
     group 5 '/opt/oradata/hankey/redo05.log' size 200M,
     group 6 '/opt/oradata/hankey/redo06.log' size 200M;

（只操作状态为inactive的日志组，若一直处于active状态,可重启数据库解决）

若要操作日志组为current时，需先进行日志切换：alter system switch logfile;

alter system switch logfile;

set lines 200 pages 300
col member for a60
select a.thread#,a.group#,b.member,b.type,a.status,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type, a.status,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#;

删掉旧的日志组（1、2、3号redo）

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

1.5、添加备库logfiles

1.5.1、添加备库logfile的要求

l  确保主和备数据库上的日志文件大小是相同的

l  确定备库重做日志文件组的适当数目

Standby logfile数 = （每个线程的logfile数＋1）* 线程数

l  检查create database时指定的MAXLOGFILES和MAXLOGMEMBERS参数，可以通过controlfile出来查看

l  RAC环境创建standby logfile,指定thread#创建

每个thread的standby logfile数 = 每个thread的logfile数 + 1

1.5.2、添加standby logfile

alter database add standby logfile thread 1  
    group 7 '/opt/oradata/hankey/std_redo07.log' size 200M,
    group 8 '/opt/oradata/hankey/std_redo08.log' size 200M,
    group 9 '/opt/oradata/hankey/std_redo09.log' size 200M,
    group 10 '/opt/oradata/hankey/std_redo10.log' size 200M;

1.5.3、查询添加后的结果

set lines 200 pages 300
col member for a60
select a.thread#,a.group#,b.member,b.type,a.status,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type, a.status,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#;

修改主库参数文件

1.6.1备份spfile：

create pfile='/tmp/pfile.bak' from spfile;

1.6.2修改以下参数

其中hankey, skydbstd分别为primary,standby的db_unqiue_name/TNS-Alias,详见参数详解部分：
alter system set log_archive_config='dg_config=(hankey,skydbstd)';

alter system set log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=hankey';

alter system set log_archive_dest_2='service=skydbstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=skydbstd';

show parameter log_archive_max_processes

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes         integer     4
SQL>
alter system set log_archive_max_processes=10; #根据需求调整个数

确认以下参数默认值，如不为以下参数值，则修改：

show parameter remote_login_passwordfile

alter system set remote_login_passwordfile=exclusive scope=spfile;

  # remote_login_passwordfile=exclusive/shared

alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;

log_archive_format参数控制归档文件名称格式，默认值以.dbf结尾，为了与datafile区分，建议修改为.arc结尾：
show parameter log_archive_format

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_format             string     %t_%s_%r.dbf

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

当primary切换为standby角色后，需要增加修改以下参数，建议配置：

alter system set fal_server=skydbstd;
alter system set standby_file_management=auto;

# primary和standby的datafile和logfile存储路径不一样时，设定以下参数：

可下通过sql查询datafile路径：

set lines 300 pages 300
col name for a60
col member for a60
select file#,name from v$datafile
union all
select file#,name from v$tempfile;

设置db_file参数如下：

alter system set db_file_name_convert='/opt/oradata/skydbstd','/opt/oradata/hankey', '/opt/oradata/skydbstd','/home/oracle/oradata' scope=spfile;

可下通过sql查询logfile路径：

select MEMBER from v$logfile;

select MEMBER from v$logfile;
设置log_file参数如下
alter system set log_file_name_convert='/opt/oradata/skydbstd','/opt/oradata/hankey' scope=spfile;

注：以上参数，指定spfile修改的，在实例重启后生效。

shutdown immediate
startup

1.7、打开归档模式

首先确认当前数据库是否是归档模式，如果不是，则打开归档模式。

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter database open;

1.8、备份主库

rman target / <<EOF
run{allocate channel c1 type disk;
allocate channel c2 type disk;
backup full database format '/opt/oradata/backup/full_%U.bak' plus archivelog;
sql 'alter system switch logfile';
backup current controlfile for standby format '/opt/oradata/backup/c_%U.bak';
release channel c1;
release channel c2;
}
EOF

1.9、传送备份文件到备库

  scp -r /opt/oradata/backup/ 10.10.10.20:/opt/oradata/backup/

 

二、备库配置

2.1、配置环境变量(可参考主库)

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11204/dbhome_1
export ORACLE_SID=skydbstd
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_GRID/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:$LD_LIBRARY_PATH
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib

2.2、配置客户端的网络服务

 (1) listener.ora

配置静态监听：

cd $ORACLE_HOME/network/admin

vi listener.ora

LISTENER =
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.20)(PORT=1521))
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=skydbstd)
      (SID_NAME=skydbstd)         
      (ORACLE_HOME=/u01/app/oracle/product/11204/dbhome_1)
      )
)

启用监听并查看监听状态：

lsnrctl start

lsnrctl status

(2) tnsnames.ora

vi tnsnames.ora

HANKEY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hankey)
    )
  )

SKYDBSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = skydbstd)
    )
  )

(3) tnsping命令解析网络服务名,命令须返回OK.

此时，standby端未配置监听，无法解析，可以standby配置后再测试。

tnsping skydbstd

(4) 密码文件

使用主库的密码文件：

scp 10.10.10.10:/u01/app/oracle/product/11gr2/dbhome_1/dbs/orapwhankey $ORACLE_HOME/dbs/orapwskydbstd

3.创建相关目录

mkdir -p $ORACLE_BASE/admin/skydbstd/{adump,dpdump,pfile}
mkdir -p /home/oracle/arch
mkdir -p /opt/oradata/skydbstd

2.4、参数文件

用primary的pfile加以修改，以保证某些参数与primary保持一致，注意主备库内存的大小适当的调整内存相关参数。

scp 10.10.10.10:/tmp/pfile.bak $ORACLE_HOME/dbs/initskydbstd.ora

修改pfile为需要的standby pfile,并创建spfile.特别注意以下事例的参数设定：
vi $ORACLE_HOME/dbs/initskydbstd.ora

*.audit_file_dest='/u01/app/oracle/admin/skydbstd/adump'
*.audit_trail='db'
*.control_files='/opt/oradata/skydbstd/control01.ctl','/opt/oradata/skydbstd/control02.ctl','/opt/oradata/skydbstd/control03.ctl'
*.db_name='skydbstd'
*.db_unique_name='skydbstd'
*.instance_name='skydbstd'
*.service_names='skydbstd'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='dg_config=(skydbstd,hankey)'
*.log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=skydbstd'
*.log_archive_dest_2='service=hankey lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hankey'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_1='enable'
*.log_archive_max_processes=30 #根据需求调整个数
*.fal_server='hankey'
*.standby_file_management=auto
*.remote_login_passwordfile='EXCLUSIVE'
#主、备库日志文件和数据文件存放路径不一致，设定以下参数：
#下面这两个参数和数库路径位置正好相反
*.db_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd','/home/oracle/oradata','/opt/oradata/skydbstd'
*. log_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd'

本次实际修改后的pfile如下

skydbstd.__db_cache_size=276824064
skydbstd.__java_pool_size=4194304
skydbstd.__large_pool_size=12582912
skydbstd.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
skydbstd.__pga_aggregate_target=306184192
skydbstd.__sga_target=478150656
skydbstd.__shared_io_pool_size=0
skydbstd.__shared_pool_size=163577856
skydbstd.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/skydbstd/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/opt/oradata/skydbstd/control01.ctl','/opt/oradata/skydbstd/control02.ctl','/opt/oradata/skydbstd/control03.ctl'
*.db_block_size=8192
*.db_domain=''
# db_name长度不要超过8个字符，同主库
*.db_name= hankey
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=781189120
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#主、备库日志文件和数据文件存放路径不一致，设定以下参数：
#下面这两个参数和数库路径位置正好相反
*.db_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd','/home/oracle/oradata','/opt/oradata/skydbstd'
*.log_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd'
*.db_unique_name='skydbstd'
*.instance_name='skydbstd'
*.service_names='skydbstd'
*.log_archive_config='dg_config=(skydbstd,hankey)'
*.log_archive_dest_1='location=/home/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=skydbstd'
*.log_archive_dest_2='service=hankey lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hankey'
*.log_archive_dest_state_1='enable'
#按需调整
*.log_archive_max_processes=30*.fal_server='hankey'
*.standby_file_management=auto
*.remote_login_passwordfile='EXCLUSIVE'

#主、备库日志文件和数据文件存放路径不一致，设定以下参数：
#下面这两个参数和数库路径位置正好相反
*.db_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd','/home/oracle/oradata','/opt/oradata/skydbstd'
*.log_file_name_convert='/opt/oradata/hankey','/opt/oradata/skydbstd'

创建spfile：

sqlplus / as sysdba
create spfile from pfile;

2.5、恢复standby controlfile

startup nomount;

rman target /
restore standby controlfile from '/home/oracle/backup/c_08rlrfc2_1_1.bak';

2.6、恢复数据文件

启动数据库到mount阶段，恢复数据文件。

 

(1)standby 与primary数据文件存放路径一致:

 

RMAN> sql 'alter database mount standby database';

run
{allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
recover database;
release channel c1;
release channel c2;
}

执行之后，要查看打印出来的目录在备库是否都存在，不存在的话，创建对应目录。

(2) standby 与primary数据文件存放路径不一致：

查询主库的数据文件信息：

set lines 300 pages 300
col name for a60
col member for a60
select file#,name from v$datafile
union all
select file#,name from v$tempfile;

路径根据备库参数db_file_name_convert处理，
*.db_file_name_convert='+DG_ORA/ora11g/datafile','/opt/oradata/webcc','+DG_ORA/ora11g','/opt/oradata/webcc','+DG_INDEX','/opt/oradata/webcc/datafile','+DG_DATA/ora11g','/opt/oradata/webcc'

例如将+DG_ORA/ora11g/datafile换为 /opt/oradata/webcc

注意datafile和tempfile

run
{
set newname for datafile 1 to '/opt/oradata/skydbstd/system01.dbf';
set newname for datafile 2 to '/opt/oradata/skydbstd/sysaux01.dbf';
set newname for datafile 3 to '/opt/oradata/skydbstd/undotbs01.dbf';
set newname for datafile 4 to '/opt/oradata/skydbstd/users01.dbf';
set newname for datafile 5 to '/opt/oradata/skydbstd/tbs_njdf_dat01.dbf';
set newname for datafile 6 to '/opt/oradata/skydbstd/tbs_njdf_idx01.dbf';
set newname for datafile 7 to '/opt/oradata/skydbstd/ctxsys01.dbf';
set newname for datafile 8 to '/opt/oradata/skydbstd/tbs_uccbiz_dat01.dbf';
set newname for datafile 9 to '/opt/oradata/skydbstd/tbs_ucchis_dat01.dbf';
set newname for datafile 10 to '/opt/oradata/skydbstd/tbs_uccdata_dat01.dbf';
set newname for datafile 11 to '/opt/oradata/skydbstd/tbs_ucc_idx01.dbf';
set newname for datafile 12 to '/opt/oradata/skydbstd/tbs_ucc_part_dat01.dbf';
set newname for datafile 13 to '/opt/oradata/skydbstd/tbs_ucc_part_idx01.dbf';
set newname for tempfile 1 to '/opt/oradata/skydbstd/temp01.dbf';
set newname for tempfile 2 to '/opt/oradata/skydbstd/tbs_njdf_temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

Starting recover at 19-SEP-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 31 is already on disk as file /home/oracle/arch/1_31_928863514.dbf
archived log for thread 1 with sequence 32 is already on disk as file /home/oracle/arch/1_32_928863514.dbf
archived log for thread 1 with sequence 33 is already on disk as file /home/oracle/arch/1_33_928863514.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11gr2/dbhome_1/dbs/07rlrfc1_1_1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u01/app/oracle/product/11gr2/dbhome_1/dbs/07rlrfc1_1_1
ORA-19505: failed to identify file "/u01/app/oracle/product/11gr2/dbhome_1/dbs/07rlrfc1_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/19/2018 17:14:26
RMAN-20506: no backup of archived log found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 29 and starting SCN of 303581 found to restore

RMAN>
上述错误不用管，属于正常现象

查看备库 log file:

set lines 200 pages 300
col member for a60
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB
from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB
from v$standby_log a,v$logfile b where a.group#=b.group#;

2.7、应用日志

应用archive log：
sqlplus / as sysdba
recover managed standby database disconnect;
select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
RFS      IDLE
RFS      IDLE
RFS      IDLE
RFS      IDLE
MRP0      WAIT_FOR_LOG
RFS      IDLE
RFS      IDLE

37 rows selected.

查看在备库查看接收到的被应用的redo

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# APPLIED
---------- ---------
    29 YES
    30 YES
    31 YES
    32 YES
    33 YES

查看主备归档：

archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /home/oracle/arch
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34
SQL>

2.8、取消应用日志：

recover managed standby database cancel;

2.9、应用redo logfile：

recover managed standby database using current logfile disconnect;

ORA-01153: an incompatible media recovery is active

ORA-16136: Managed Standby Recovery not active

如果报这个错，就是因为数据库在恢复阶段，没有执行这一步的取消应用日志命令导致

--主库端查询v$archived_log视图，确认日志是否被应用：

set lines 300 pages 300
col name for a20
select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log
where standby_dest='YES'
order by thread#,sequence#;

NAME            DEST_ID    THREAD#  SEQUENCE# STA APPLIED   REGISTR COMPLETIO
-------------------- ---------- ---------- ---------- --- --------- ------- ---------
orclstd               2      1       35 YES YES        ARCH    04-APR-19
orclstd               2      1       36 YES YES        ARCH    04-APR-19
orclstd               2      1       36 YES YES        ARCH    04-APR-19
orclstd               2      1       36 YES YES        ARCH    04-APR-19
orclstd               2      1       37 YES YES        ARCH    04-APR-19
orclstd               2      1       37 YES YES        ARCH    04-APR-19
orclstd               2      1       38 YES YES        ARCH    04-APR-19
orclstd               2      1       39 YES YES        LGWR    04-APR-19

2.10、取消应用日志：

recover managed standby database cancel;

2.11、打开数据库至read only模式并开始实时复制：

alter database open read only;
recover managed standby database using current logfile disconnect;

11g Physical standby database可打开至read only模式，也就是常说的Active Dataguard.可迁移查询的业务至Active DG上，减小primary DB的压力。

2.12、观察日志传输

跟踪primary,standby database的alert log,观察是否有错误发生，也可以看到应用日志的相关信息。

确认standby是否应用日志：

--primary端多切几次日志，观察alert log信息。

alter system switch logfile;
alter system switch logfile;

--观察主备库日志序列号

archive log list;

2.13、在primary端查询v$archived_log视图，确认日志是否被应用：

set lines 300 pages 300
col name for a20
select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log
where standby_dest='YES'
order by thread#,sequence#;

NAME DEST_ID THREAD# SEQUENCE# STA APPLIED REGISTR COMPLETIO
-------------------- ---------- ---------- ---------- --- --------- ------- ---------
orclstd 2 1 35 YES YES ARCH 04-APR-19
orclstd 2 1 36 YES YES ARCH 04-APR-19
orclstd 2 1 36 YES YES ARCH 04-APR-19
orclstd 2 1 36 YES YES ARCH 04-APR-19
orclstd 2 1 37 YES YES ARCH 04-APR-19
orclstd 2 1 37 YES YES ARCH 04-APR-19
orclstd 2 1 38 YES YES ARCH 04-APR-19
orclstd 2 1 39 YES YES LGWR 04-APR-19
orclstd 2 1 40 YES YES LGWR 04-APR-19
orclstd 2 1 41 YES NO ARCH 04-APR-19

10 rows selected.

2.14、在主库查询primary,standby的最大日志序列号是否一致：

select 'Primary :' "DB Role",thread#,max(sequence#)
from v$archived_log
where standby_dest='NO'
group by thread#
union
select 'Standby :' "DB Role",thread#,max(sequence#)
from v$archived_log
where standby_dest='YES' and applied='YES'
group by thread#
order by thread#;

DB Role THREAD# MAX(SEQUENCE#)
--------- ---------- --------------
Primary : 1 41
Standby : 1 40

2.15、在备库查看接收到的被应用的redo

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
40 YES
41 IN-MEMORY

8 rows selected.

2.16、验证

在主库建测试表

SQL> create table t2019 as select * from dba_users;

然后在备库查看

SQL> select count(*) from t2019;

  COUNT(*)
----------
    35

SQL> 

 至此，dataguard搭建完成。

三、如果需要清理备库环境重新配置，请参考以下步骤：

3.1、停库：
3.2、删除归档：
3.3、删除数据文件、在线日志文件
3.4、删除控制文件
3.5、删除adump dpdump pfile里面的文件
3.6、在dbs目录下只保留initstandby.ora orapwstandby

-----------------------