转 DataGuard环境搭建（一主一备一级联）

http://blog.itpub.net/30130773/viewspace-2116985/

1.--------- primary_role / standby_role/ all_roles online_logfile/ standby_logfile/ all_logfiles

--------- 这几个参数的含义注意理解

DG搭建（一主一备一级联）

先说明一下dataguard一主一备一级联，意思是主库将日志传输给备库，然后备库在将日志传输给级联库，主库和级联库其实没有任何关系。另外，关于数据同步问题，后面也做了验证，主库上的操作一般情况下是可以实时同步到备库的，但是级联库必须等备库归档时，才能同步。如果主库切换日志，那么这时级联库也能及时同步。

节点	网络ip地址	数据库名	unique name	数据库实例名	数据文件位置
zyx.test.com（主库）	192.168.11.111	orcl	orcl	test	/u01/app/oracle/oradata/orcl/
orcl.test.com（备库）	192.168.11.22	orcl	orclps	orclps	/u01/app/oracle/oradata/orcl/
dg2.orcl.com（级联库）	192.168.11.23	orcl	orclstd	orclstd	/u01/app/oracle/oradata/orcl/

1.主库设置

1.1 开归档

sys@ORCL> shutdown immediate

sys@ORCL> startup mount

sys@ORCL> alter database archivelog;

sys@ORCL> alter database open;

sys@ORCL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

sys@ORCL> alter database force logging;

Database altered.

1.2 参数设置

sys@ORCL>alter system set log_archive_config='dg_config=(orcl,orclps)';

sys@ORCL>alter system set log_archive_dest_2='service=orclps sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orclps';

#####下面参数是当主库切换为备库时需要的，这里先不设置

alter system set fal_server=orclps;

alter system set fal_client=orcl;

alter system set standby_file_management=auto;

sys@ORCL> create pfile='/home/oracle/pfile.ora' from spfile;

1.3 配置TNS

[oracle@zyx ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.test.com)

)

ORCLPS =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLPS)

)

2.备库orclps配置

----数据库软件安装好，数据库不用创建

2.1 环境变量

[oracle@orcl ~]$ vim .bash_profile

export ORACLE_SID=orclps

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_UNQNAME=orclps

export ORACLE_HOSTNAME=orcl.test.com

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export NLS_LANG=american_america.ZHS16GBK

export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

export EDITOR=vi

export LANG=C

umask 022

[oracle@orcl ~]$ . .bash_profile

2.2 创建必要目录

mkdir -p $ORACLE_BASE/fast_recovery_area/orcl

mkdir -p $ORACLE_BASE/admin/orcl/adump

mkdir -p $ORACLE_BASE/admin/orcl/dpdump

mkdir -p $ORACLE_BASE/admin/orcl/pfile

mkdir -p $ORACLE_BASE/oradata/orcl

2.3 静态监听

[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_listener=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orclps)

(SID_NAME=orclps)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))

[oracle@orcl ~]$ lsnrctl start

2.4 配置TNS

[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.test.com)

)

ORCLPS =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLPS)

)

2.5 参数文件设置

[oracle@zyx ~]$ scp pfile.ora 192.168.11.22:/home/oracle

idle>ho vim /home/oracle/pfile.ora

----可以删除的参数，或修改为下面格式(该参数是指当前数据库为主库时，传输在线日志给orcl)

*.log_archive_dest_2='service=orcl sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orcl'

----追加参数

*.db_unique_name='orclps'

*.fal_client='orclps'

*.fal_server='orcl'

*.standby_file_management=auto

----创建spfile，启动到nomount

idle>create spfile from pfile='/home/oracle/pfile.ora';

idle>startup nomount

2.6 密钥文件创建

[oracle@orcl ~]$ cd $ORACLE_HOME/dbs

[oracle@orcl dbs]$ orapwd file=orapworclps password=sys

3.duplicate到备库

3.1 登录测试

[oracle@dg2 ~]$ tnsping orclps

[oracle@dg2 ~]$ tnsping orcl

[oracle@dg2 ~]$ sqlplus sys/sys@orclps as sysdba

[oracle@dg2 dbs]$ sqlplus sys/sys@orcl as sysdba

3.2 duplicate复制数据库到orclps

[oracle@orcl ~]$ rman target sys/sys@orcl auxiliary sys/sys@orclps

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 25 18:30:21 2016

connected to target database: ORCL (DBID=1437652505)

connected to auxiliary database: ORCL (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

--------如果复制数据库时，备库的路径和原库一致，就需要加nofilenamecheck，不然会报错

3.3 备库orclps配置

----查看当前状态

idle>select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

orclps MOUNTED

----如果需要手动启动备用数据库：

---- startup nomount

---- alter database mount standby database;

----创建srl日志（比主库redo多一组，大小一样）

idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;

idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;

idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;

idle>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;

----应用日志，开启redoapply

idle> alter database recover managed standby database using current logfile disconnect;

---------取消日志应用 recover managed standby database cancel;

# 主库添加srl日志,转为备库时需要

# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;

# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;

# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;

# alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;

4.查看主/备应用日志情况

4.1主库切换日志

sys@ORCL>alter system switch logfile;

4.2备库出现新归档

----备库orclps出现新的归档

idle>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

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

7 YES

8 YES

9 IN-MEMORY

----备库orclps传输模式

idle> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

----主库上最大性能改为最大可用

sys@ORCL>alter database set standby database to maximize availability;

sys@ORCL>show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string service=orclps sync affirm net

_timeout=10 valid_for=(online_

logfile,primary_role) db_uniqu

e_name=orclps

sys@ORCL>select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

4.3主库上查看备库应用情况（可以把name字段也加上）

sys@ORCL>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

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

6 NO

7 NO

8 NO

8 YES

9 YES

9 NO

10 YES

10 NO

11 YES

11 NO

12 YES

12 NO

13 YES

13 NO

14 NO

16 rows selected.

------上面applied应用：NO的代表本地归档，是不需要应用的，YES的代表备库传输后已经应用。orclps备库是从8号归档开始应用

4.4 查看备库数据文件存放位置

idle>select name from v$controlfile;

idle>select name from v$datafile;

idle>select name from v$tempfile;

idle>select group#,member,type from v$logfile;

GROUP# MEMBER TYPE

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

3 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_3_ckvxlzto_.log ONLINE

2 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_2_ckvxlydr_.log ONLINE

1 /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_1_ckvxlwvd_.log ONLINE

4 /u01/app/oracle/oradata/orcl/srl01.log STANDBY

5 /u01/app/oracle/oradata/orcl/srl02.log STANDBY

6 /u01/app/oracle/oradata/orcl/srl03.log STANDBY

7 /u01/app/oracle/oradata/orcl/srl04.log STANDBY

7 rows selected.

------后续转为主库后，redo log可以自己调整一下，上面是duplicate主库到备库时，自动生成的redo log

5.级联配置之备库设置

5.1 备库开启ADG模式

------备库不是一定要开启ADG，备库在mount下也是可以的完成级联库搭建的

idle>alter database recover managed standby database cancel;

idle>alter database open;

sys@ORCL>alter database recover managed standby database using current logfile disconnect;

sys@ORCL>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

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

12 YES

13 YES

14 YES

8 rows selected.

sys@ORCL>select database_role,switchover_status,db_unique_name from v$database;

DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

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

PHYSICAL STANDBY NOT ALLOWED orclps

5.2 备库参数设置

sys@ORCL>select log_mode,force_logging from v$database;

LOG_MODE FOR

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

ARCHIVELOG YES

sys@ORCL>alter system set log_archive_config='dg_config=(orcl,orclps,orclstd)';

sys@ORCL>alter system set log_archive_dest_3='service=orclstd sync affirm net_timeout=10 valid_for=(standby_logfile,standby_role) db_unique_name=orclstd';

--------- primary_role / standby_role/ all_roles online_logfile/ standby_logfile/ all_logfiles

--------- 这几个参数的含义注意理解

sys@ORCL>create pfile='/home/oracle/orclstd.ora' from spfile;

5.3 备库TNS配置追加

[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCLSTD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclstd)

)

6.级联库orclstd配置

6.1 环境变量

[oracle@dg2 ~]$ vim .bash_profile

export ORACLE_SID=orclstd

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_UNQNAME=orclstd

export ORACLE_HOSTNAME=dg2.orcl.com

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export NLS_LANG=american_america.ZHS16GBK

export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

export EDITOR=vi

export LANG=C

umask 022

[oracle@orcl ~]$ . .bash_profile

6.2 创建必要目录

mkdir -p $ORACLE_BASE/fast_recovery_area/orcl

mkdir -p $ORACLE_BASE/admin/orcl/adump

mkdir -p $ORACLE_BASE/admin/orcl/dpdump

mkdir -p $ORACLE_BASE/admin/orcl/pfile

mkdir -p $ORACLE_BASE/oradata/orcl

6.3 静态监听

[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_listener=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orclstd)

(SID_NAME=orclstd)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))

[oracle@dg2 ~]$ lsnrctl start

6.4 配置TNS

[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCLPS =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCLPS)

)

ORCLSTD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclstd)

)

6.5 参数文件设置

[oracle@orcl ~]$ scp orclstd.ora 192.168.11.23:/home/oracle/

----修改参数

SQL> !vim /home/oracle/orclstd.ora

*.db_name='orcl'

*.db_unique_name='orclstd'

*.fal_client='orclstd'

*.fal_server='orclps'

----暂时删除参数

*.log_archive_dest_3='service=orclstd sync affirm net_timeout=10 valid_for=(standby_logfile,standby_role) db_unique_name=orclstd'

----创建spfile，启动到nomount

idle>create spfile from pfile='/home/oracle/orclstd.ora';

idle>startup nomount

6.6 密钥文件创建

[oracle@dg2 ~]$cd $ORACLE_HOME/dbs

[oracle@dg2 dbs]$ orapwd file=orapworclstd password=sys

7.duplicate到级联库

7.1 登录测试

[oracle@dg2 ~]$ tnsping orclps

[oracle@dg2 ~]$ tnsping orclstd

[oracle@dg2 ~]$ sqlplus sys/sys@orclps as sysdba

[oracle@dg2 dbs]$ sqlplus sys/sys@orclstd as sysdba

7.2 duplicate复制数据库到orclstd

[oracle@orcl ~]$ rman target sys/sys@orclps auxiliary sys/sys@orclstd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 25 19:37:45 2016

connected to target database: ORCL (DBID=1437652505)

connected to auxiliary database: ORCL (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

--------如果复制数据库时，备库的路径和原库一致，就需要加nofilenamecheck，不然会报错

7.3 级联库orclstd调整srl日志

----查看当前状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

orclstd MOUNTED

----如果需要手动启动备用数据库：

---- startup nomount

---- alter database mount standby database;

----查看是否有srl日志

set linesize 200

set pagesize 999

col member for a80

select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

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

3 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_3_cl7h802v_.log

2 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_2_cl7h7z1l_.log

1 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_1_cl7h7y17_.log

4 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_4_cl7h8102_.log

5 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_5_cl7h8291_.log

6 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_6_cl7h83dn_.log

7 STANDBY /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_7_cl7h84r2_.log

7 rows selected.

------有redo和srl日志，不用再添加，当然这些日志都可以自己再手动调整位置

------redo日志需要转为主库才能调整，现在先调整srl日志

SQL> alter database drop standby logfile group 4;

SQL> alter database drop standby logfile group 5;

SQL> alter database drop standby logfile group 6;

SQL> alter database drop standby logfile group 7;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

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

3 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_3_cl7hf6kq_.log

2 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_2_cl7hf4mo_.log

1 ONLINE /u01/app/oracle/fast_recovery_area/ORCLSTD/onlinelog/o1_mf_1_cl7hf33q_.log

4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log

5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log

6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log

7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log

7 rows selected.

7.4开启级联库redoapply

SQL> alter database recover managed standby database using current logfile disconnect;

---------取消日志应用 recover managed standby database cancel;

8.查看备库/级联库应用日志情况

8.1 级联库日志应用情况

----主库切换日志

sys@ORCL>alter system switch logfile;

----级联库出现新日志

SQL> select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

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

15 IN-MEMORY

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

----备库orclps日志应用情况

sys@ORCL>select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED

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

12 YES

13 YES

14 YES

15 YES

10 rows selected.

8.2 主库上创建表，级联库查看

----先开启级联库ADG

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect;

----主库orcl上创建表，并插入数据

sys@ORCL>create table shall(shall int);

begin

for i in 1..100000 loop

insert into shall values(i);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

----备库orclps能实时查询到数据

sys@ORCL>select count(*) from shall;

COUNT(*)

----------

100000

----级联库orclstd不能查询到数据

SQL> select count(*) from shall;

select count(*) from shall

*

ERROR at line 1:

ORA-00942: table or view does not exist

----主库orcl切换日志

sys@ORCL>alter system switch logfile;

----此时级联库orclstd可以查询到数据

SQL> select count(*) from shall;

COUNT(*)

----------

0

SQL> select count(*) from shall;

COUNT(*)

----------

100000

----------备库是实时数据，而级联库需要等备库归档后才能同步

2.备份删除策略

http://www.laoxiong.net/oracle-11g-data-guard-archived-log-managemen.html

幸运的是，在11g环境里面，上述的几点很容易就满足，那就是只需要做到以下几点。

使用快速恢复区(fast recovery area)，在10g版本的文档中称为闪回恢复区（flash recovery area），老实说，一直不太明白为什么取名叫闪回恢复区，难道是因为10g有了数据库闪回功能？在RAC中，毫无疑问快速恢复区最好是置放在ASM上。
为快速恢复区指定合适的空间。首先我们需要预估一个合理的归档保留时间长。比如由于备份系统问题或Data Guard备库问题、维护等，需要归档保留的时间长度。假设是24小时，再评估一下在归档量最大的24小时之内，会有多少量的归档？一般来说是在批量数据处理的时候归档量最大，假设这24小时之内归档最大为200G。注意对于RAC来说是所有节点在这24小时的归档量之和。最后为快速恢复区指定需要的空间量，比通过参数db_recovery_file_dest_size指定快速恢复区的大小。这里同样假设快速恢复区们存放归档日志。
在备库上指定快速恢复区以及为快速恢复区指定合适的大小，在备库上指定快速恢复区的大小主要考虑的是：切换成为主库后归档日志容量；如果主库归档容量压力大，备库能否存储更多的归档日志以便可以通过备库来备份归档日志。
对主库和备份使用RMAN配置归档删除策略：CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

完成了上述几个步骤，那么归档管理的要求基本上就达到了。通过这样的设置，实现的效果如下：

归档日志如果没有应用到备库，那么在RMAN中使用backup .... delete inputs all和delete archivelog all不会将归档日志删除。但但是请注意如果是使用delete force命令则会删除掉归档，不管归档有没有被应用到备库。
如果归档日志已经应用到了备库，那么在RMAN中使用backup .... delete inputs all和delete archivelog all可以删除归档日志，在正常情况下，由于归档日志可能很快应用到Data Guard，所以在RMAN备份之后可以正常删除归档日志。RMAN也不需要使用特别的备份脚本，也不必担心人为不小心使用。delete archivelog all命令删除了归档。
备库的归档日志存储到快速恢复区中，备库的快速恢复区空间紧张时，会自动删除已经应用过的较早的归档日志以释放空间，这样便可以实现备库的归档日志完全自动管理。
如果由于备份异常或Data Guard异常，在快速恢复区空间紧张时，Oracle在切换日志时，会自动删除掉已经应用过的归档日志，以释放空间。但是如果归档日志没有应用到Data Guard，那么归档日志不会被删除。这种情况下，快速恢复区的归档可能会增加到空间耗尽，最后就会出现数据库不能归档，数据库挂起的问题。

Two queries you can run to look at space usage in the FRA are:

Files being deleted in the flash recovery area, messages in the alert log Deleted Oracle managed file <filename> (文档 ID 1369341.1)

select * from V$FLASH_RECOVERY_AREA_USAGE;

SELECT substr(name, 1, 30) name
      , space_limit/(1073741824) AS Quota_GB
      , space_used/(1073741824) AS Used_GB
      , space_reclaimable/(1073741824) AS Reclaimable_GB
      , number_of_files AS files
FROM V$RECOVERY_FILE_DEST ;

#########

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDB;

Files being deleted in the flash recovery area, messages in the alert log Deleted Oracle managed file <filename> (文档 ID 1369341.1) ，With Oracle 11.2 and onwards, Oracle will start to purge the files in the FRA when the SPACE_USED reaches 80% of SPACE_LIMIT.

####3

##############1
How is the space pressure managed in the Flash Recovery Area - An Example. (文档 ID 315098.1)

To make space reclaimable we have the following options:

1) The space occupied by archivelogs created in the Flash Recovery Area
would become reclaimable in 2 circumstances:

A) If the free space becomes less then 15% in the Flash Recovery Area then all
the archivelogs in the Flash Recovery Area which are not needed for recovery
by the current backups in the FRA will become obsolete and the space occupied
will be shown in the SPACE RECLAIMABLE column of V$RECOVERY_FILE_DEST.

RMAN> report obsolete;

B) If the archivelogs have been backed up (either to the Flash Recovery Area itself
or to another destination) and the archivelog not deleted. Then the space occupied
by that archivelog will be marked as reclaimable.

#############2

CAUSE
The log_archive_dest_1 parameter was set as:

log_archive_dest_1=LOCATION=+FRA_01/EAPRD/ARCHIVELOG_2
which points to flash recovery area. Files will get generated but will not be considered by FRA algorithm for cleaning up when space pressure mounts up.

These logs will not participate in the FRA algorithms. They will just exist in the same diskgroup that houses the FRA. Reason for an "ORA-15041: diskgroup space exhausted" error is the disk group itself had run out of space.
If the FRA had been in use for archive logs (setting log_archive_dest_1='location=use_db_recovery_file_dest'), then Oracle will check check for files whose space is eligible for reclaiming.

Any archive logs that are not managed via the FRA (v$archived_log.IS_RECOVERY_DEST_FILE=NO) should be manually managed, i.e. back them up and remove them to free up space if necessary.
Archive log files managed by the FRA will be governed by the rules mentioned in Note 315098.1.

Use next query to verify this

select dest_id, IS_RECOVERY_DEST_FILE, count(*)
from v$archived_log
group by dest_id, IS_RECOVERY_DEST_FILE;

DEST_ID IS_ COUNT(*)
---------- --- ----------
1 NO 3477
1 YES 413

原文地址：https://www.cnblogs.com/feiyun8616/p/8534012.html

转 DataGuard环境搭建 （一主一备一级联）

DG搭建（一主一备一级联）

1.主库设置

1.1 开归档

1.2 参数设置

1.3 配置TNS

2.备库orclps配置

2.1 环境变量

2.2 创建必要目录

2.3 静态监听

2.4 配置TNS

2.5 参数文件设置

2.6 密钥文件创建

3.duplicate到备库

3.1 登录测试

3.2 duplicate复制数据库到orclps

3.3 备库orclps配置

4.查看主/备应用日志情况

4.1主库切换日志

4.2备库出现新归档

4.3主库上查看备库应用情况（可以把name字段也加上）

4.4 查看备库数据文件存放位置

5.级联配置之备库设置

5.1 备库开启ADG模式

5.2 备库参数设置

5.3 备库TNS配置追加

6.级联库orclstd配置

6.1 环境变量

6.2 创建必要目录

6.3 静态监听

6.4 配置TNS

6.5 参数文件设置

6.6 密钥文件创建

7.duplicate到级联库

7.1 登录测试

7.2 duplicate复制数据库到orclstd

7.3 级联库orclstd调整srl日志

7.4开启级联库redoapply

8.查看备库/级联库应用日志情况

8.1 级联库日志应用情况

8.2 主库上创建表，级联库查看

转 DataGuard环境搭建（一主一备一级联）