Oracle数据库之dataguard部署

鱼鱼不愚与

已于 2024-01-10 15:29:24 修改

阅读量1.3k

点赞数 37

文章标签： oracle 数据库

于 2024-01-10 15:18:32 首次发布

本文链接：https://blog.csdn.net/2301_79096508/article/details/135504240

版权

信息规划

	IP	数据库实例名	数据库unique名称
主库	192.168.200.18	cc	cc
备库	192.168.200.125	cc	dr_cc

一、配置监听和服务名解析

配置$ORACLE_HOME/network/admin/listener.ora：(数据库已开始就已经有了）

LISTENER_CC =
(DESCRIPTION_LIST =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.18)(PORT = 30921))
       )
    )
)

SID_LIST_LISTENER_CC =
(SID_LIST =
     (SID_DESC =
           (GLOBAL_DBNAME = cc)
           (ORACLE_HOME = /oracle/product/19c)
           (SID_NAME = cc)
      )
)

重启监听使配置生效：（在有没有oracle数据库前后都可以部署）

lsnrctl start LISTENER_CC

lsnrctl status LISTENER_CC

配置$ORACLE_HOME/network/admin/tnsnames.ora：（修改这个不需要重启监听）

CC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.18)(PORT = 30921))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
      (SERVICE_NAME = cc)
    )
  )



DR_CC = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.125)(PORT = 30921))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cc)
    )
  )

验证服务名解析：

tnsping cc
tnsping dr_cc


oracle@db01[/oracle/product/19c/network/admin]$ tnsping cc

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-DEC-2023 10:51:09

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.18)(PORT = 30921))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc)))
OK (10 msec)
oracle@db01[/oracle/product/19c/network/admin]$ tnsping dr_cc

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-DEC-2023 10:51:20

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.125)(PORT = 30921)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dr_cc)))
TNS-12541: TNS:no listener



第一个输出是成功的连接测试，显示了以下信息：

使用的参数文件：没有指定特定的参数文件。
使用了TNSNAMES适配器来解析别名。
尝试连接的目标是 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 30921))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc)))。
第二个输出是连接测试失败的结果，显示了以下信息：

使用的参数文件：没有指定特定的参数文件。
使用了TNSNAMES适配器来解析别名。
尝试连接的目标是 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 30921)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dr_cc)))。
错误消息：TNS-12541: TNS:no listener。这意味着在指定的主机和端口上没有监听器正在运行，无法建立连接。

这个时候是因为备机上的cc实例还没有起来，等起来了就变了。

二、查看主库归档和附加日志配置

1、如下图，均未开启

sqlplus / as sysdba
SQL> select log_mode,force_logging from v$database;

2、开启归档模式

SQL> shutdown immediate; (立即关闭数据库)
SQL> startup mount; (启动实例并加载数据库，但不打开)
SQL> alter database archivelog; (更改数据库为归档模式)
SQL> alter database open; (打开数据库)
SQL> alter system archive log start; (启用自动归档)
SQL> select log_mode,force_logging from v$database; （查看）

3、开启主库附加日志

SQL> alter database force logging;
SQL> select log_mode,force_logging from v$database; （查看）

alter database set standby nologging for data availability; --高可用模式
alter database set standby nologging for load performance; --性能模式

4、创建standby日志组

在主库上添加standby日志组，日志大小与online日志保持一致，数量要比online日志多一组。

set lines 200
col member for a80
--查看日志文件
select * from v$logfile;
--查看日志组数量及大小
select thread#, group#, bytes/1024/1024 size_mb from v$log;

THREAD#   GROUP#    SIZE_MB
-------   ------    -------
     1        1        2048
     1        2        2048
     1        3        2048

select group#,status,type,member from V$logfile

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

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         1         ONLINE
/ccredo/CC/redo1_1.dbf

         1         ONLINE
/ccredo/CC/redo1_2.dbf

         2         ONLINE
/ccredo/CC/redo2_1.dbf


    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         2         ONLINE
/ccredo/CC/redo2_2.dbf

         3         ONLINE
/ccredo/CC/redo3_1.dbf

         3         ONLINE
/ccredo/CC/redo3_2.dbf


6 rows selected.

根据上面SQL的结果可知当前实例有3个日志组，所以至少需要创建4个standby日志组。

# 添加附加日志
SQL> alter database add standby logfile group 4 ('/ccredo/CC/stredo04.log') size 2048M;;
SQL> alter database add standby logfile group 5 ('/ccredo/CC/stredo05.log') size 2048M;;
SQL> alter database add standby logfile group 6 ('/ccredo/CC/stredo06.log') size 2048M;;
SQL> alter database add standby logfile group 7 ('/ccredo/CC/stredo07.log') size 2048M;;

alter database add standby logfile thread 1 group 4 ('/ccredo/CC/redo4_1','/ccredo/CC/redo4_2') size 2048M;
alter database add standby logfile thread 1 group 5 ('/ccredo/CC/redo5_1','/ccredo/CC/redo5_2') size 2048M;
alter database add standby logfile thread 1 group 6 ('/ccredo/CC/redo6_1','/ccredo/CC/redo6_2') size 2048M;
alter database add standby logfile thread 1 group 7 ('/ccredo/CC/redo7_1','/ccredo/CC/redo7_2') size 2048M;

再次检查日志文件和standby日志：

select * from v$logfile;
select thread#, group#, sequence#, archived, status from v$standby_log;
--archived列的值应为YES，status列的值为UNASSIGNED1

从Oracle Database 18c开始，引入了以下两个新的nologging子句，它们可以执行非日志记录操作，同时可以使ActiveData Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。

STANDBY NOLOGGING FOR DATA AVAILABILITY 模式使批量加载操作通过其自身与备用数据库的连接将加载的数据发送到每个备用数据库。提交会延迟，直到所有ActiveData Guard备用数据库通过 recover 方式将数据应用完成。

SQL> alterdatabase set standby nologging for data availability;

SQL> selectlog_mode,force_logging from v$database;

STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。

SQL> alterdatabase set standby nologging for load performance;

SQL> selectlog_mode,force_logging from v$database;

三、修改主库参数

1、列出主备库的DB_UNIQUE_NAME参数

默认情况下，定义该参数能确保主备库数据库能够互相识别对方（通过show parameter name查询）


--配置DG主备库
alter system set log_archive_config='DG_CONFIG=(cc,dr_cc)' scope=both;

--配置本地归档路径(因为archive log list;命令显示的是这个路径)这个路径等开启归档后检查一下
--确定一下这个路径是不是足够大，这个目录会增长
alter system set log_archive_dest_1='LOCATION=/archcc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cc' scope=both;

--配置备库归档
alter system set log_archive_dest_2='SERVICE=dr_cc LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dr_cc' scope=both;

alter system set log_archive_dest_state_1=ENABLE scope=both;
alter system set log_archive_dest_state_2=ENABLE scope=both;

alter system set FAL_SERVER=dr_cc scope=both;
alter system set FAL_CLIENT=cc scope=both;

alter system set standby_file_management=auto;

--配置主备库数据文件名称转换关系 
alter system set db_file_name_convert='/ccdata/DR_CC/', '/ccdata/CC/' scope=spfile;

--配置主备库日志文件名称转换关系
alter system set log_file_name_convert='/ccredo/DR_CC/', '/ccredo/CC/' scope=spfile;

2、生成参数文件和密码文件：



SQL> create pfile='/oracle/initcc.ora' from spfile;

orapwd file=$ORACLE_HOME/dbs/orapwcc password="1jian8Shu)" force=y

3、将参数文件和密码文件拷贝到备库

scp /oracle/initcc.ora oracle@192.168.。。:$ORACLE_HOME/dbs/
scp $ORACLE_HOME/dbs/orapwcc oracle@192.168..:$ORACLE_HOME/dbs/

四、备库配置

在standby服务器上仅安装Oracle软件（不安装数据库实例）。

安装Oracle软件

1、vi /etc/profile

新增如下：

# set PS1 environment
if [ $UID -eq 0 ]; then
export PS1=$LOGNAME@`hostname`['$PWD']#" " 
else
export PS1=$LOGNAME@`hostname`['$PWD']'$'" "
fi
# set LANG
export LANG=C
# set histimeformat environment
export HISTTIMEFORMAT='%F %T '

# Record logging and command
export PROMPT_COMMAND='{ z=`history 1 | { read x y; echo $y; }`; echo -n $z ::; who am i; } >> /var/log/cmdlog/`id -un`_`who am i 2>/dev/null | awk -F[\(\)] "{if(\\$2)print \\$2;else print \"local\"}"`_`date +%Y%m%d`.log'
readonly PROMPT_COMMAND

# set CORE NO SHM
echo  0x1 > /proc/self/coredump_filter

# set oracle environment
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 65536 -n 65536
fi
umask 027
fi

source /etc/profile

2、关闭selinux和关闭防火墙

vi /etc/selinux/config
SELINUX=disabled
systemctl stop firewalld
systemctl disable firewalld

3、配置系统参数文件

检查共享内存参数
vi /etc/sysctl.conf
kernel.shmmax=physical RAM #通过free -b查询可得
kernel.shmmni=4096
kernel.shmall = physical RAM size / pagesize
kernel.panic_on_oops=1 #Oracle Linux 7安装oracle12cR2需要设置
sysctl -p

4、创建oracle用户和oracle所需要的文件系统

groupadd -g 1501 dba
useradd -u 1501 -g dba -d /oracle oracle
passwd oracle

--示例--
lvcreate -L 10G -n lvexpcc vgdata  
mkfs.xfs /dev/vgdata/lvexpcc
echo "/dev/mapper/vgdata-lvexpcc /expcc xfs defaults 0 0" >>/etc/fstab
mkdir /expcc
mount /expcc
--权限--
chown -R oracle:dba /oracle 

--扩容--
lvextend -L +15G /dev/mapper/vgdata-lvoracle
xfs_growfs /dev/mapper/vgdata-lvoracle

5、编辑oracle用户环境变量

su - oracle
vi /home/oracle/.bash_profile

export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19c
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
stty erase ^h
umask 027

6、安装依赖

yum -y install binutils.x86_64 compat-libcap1.x86_64 compat-libstdc++-33.i686 
yum -y install compat-libstdc++-33.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh.x86_64 

yum -y install  libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libX11.i686 libX11.x86_64 libXau.i686 libXau.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 
yum -y install libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686  libXtst.x86_64 libxcb.i686 libxcb.x86_64 
yum -y install make.x86_64 sysstat.x86_64 nfs-utils.x86_64 net-tools.x86_64 smartmontools.x86_64 perl libnsl glibc-devel psmisc

7、tar包安装数据库软件

1、上传tar包并解压
tar -xzf oracle19.tar.gz -C /oracle
(解压完后会多一层oracle目录，需将里面的内容移出来)

8、检查是否安装成功

su - oracle 
sqlplus #需要确认命令存在

创建数据库目录

这边根据自己的参数文件创建（数据文件） /ccdata/DR_CC/

mkdir -p /oradata/BANGKOKDG/onlinelog (日志） /ccredo/DR_CC

配置监听和服务名解析

配置$ORACLE_HOME/network/admin/listener.ora：

LISTENER_CC =
(DESCRIPTION_LIST =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.125)(PORT = 30921))
       )
    )
)

SID_LIST_LISTENER_CC =
(SID_LIST =
     (SID_DESC =
           (GLOBAL_DBNAME = cc)
           (ORACLE_HOME = /oracle/product/19c)
           (SID_NAME = cc)
      )
)

配置$ORACLE_HOME/network/admin/tnsnames.ora：

CC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.18)(PORT = 30921))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
      (SERVICE_NAME = cc)
    )
  )


DR_CC = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.125)(PORT = 30921))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cc)
    )
  )

修改参数文件

修改从主库拷贝过来的参数文件$ORACLE_HOME/dbs/initbangkok.ora。主要是对调主备库名位置。下面是有改动的部分。


*.db_file_name_convert='/oradata/BANGKOK/datafile','/oradata/BANGKOKDG/datafile'
*.db_name='cc'
*.db_unique_name='dr_cc'

*.fal_client='dr_cc'
*.fal_server='cc'

*.log_archive_config='DG_CONFIG=(cc,dr_cc)'
*.log_archive_dest_1='LOCATION=/archcc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dr_cc'
*.log_archive_dest_2='SERVICE=cc LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cc'


从目标到本地
*.log_file_name_convert='/ccredo/CC/','/ccredo/DR_CC/'

确保你的oracle备机中有以下文件涉及到的目录，再启动：

cc.__db_cache_size=10603200512
cc.__oracle_base='/oracle'#ORACLE_BASE set from environment
cc.__shared_io_pool_size=134217728
*._cursor_obsolete_threshold=1024
*._optimizer_use_feedback=FALSE
*._resource_manager_always_off=TRUE
*._undo_autotune=FALSE
*.archive_lag_target=1200
*.audit_file_dest='/oracle/admin/dr_cc/adump'
*.audit_trail='NONE'
*.compatible='19.0.0'
*.control_file_record_keep_time=31
*.control_files='/ccdata/DR_CC/control01.dbf','/ccdata/DR_CC/control02.dbf','/ccredo/DR_CC/control03.dbf'
*.db_block_size=8192
*.db_cache_size=10240m
*.db_file_name_convert='/ccdata/CC/','/ccdata/DR_CC/'
*.db_files=8192
*.db_name='cc'
*.db_unique_name='dr_cc'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ccXDB)'
*.event='10949 trace name context forever:28401 trace name context forever,level 1:44951 trace name context forever, level 1024'
*.fal_client='DR_CC'
*.fal_server='CC'
*.fast_start_mttr_target=300
*.fast_start_parallel_rollback='HIGH'
*.filesystemio_options='SETALL'
*.java_pool_size=64m
*.large_pool_size=512m
*.log_archive_config='DG_CONFIG=(cc,dr_cc)'
*.log_archive_dest_1='LOCATION=/archcc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dr_cc'
*.log_archive_dest_2='SERVICE=cc LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cc'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='cc_%t_%s_%r.arc'
*.log_buffer=268435456
*.log_checkpoints_to_alert=TRUE
*.log_file_name_convert='/ccredo/CC/','/ccredo/DR_CC/'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=2000
*.optimizer_adaptive_plans=FALSE
*.parallel_execution_message_size=16384
*.pga_aggregate_target=4096m
*.processes=6000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=200
*.session_max_open_files=20
*.shared_pool_size=4096m
*.standby_file_management='AUTO'
*.streams_pool_size=536870912
*.temp_undo_enabled=FALSE
*.undo_retention=18000
*.undo_tablespace='UNDOTBS1'

重命名参数文件

参数文件在/orcle下

使用参数文件启动数据库为NOMOUNT

利用上面修改好的参数文件，启动备库到NOMOUNT状态：

$ export ORACLE_SID=cc
$ sqlplus / as sysdba
SQL> create spfile from pfile='/oracle/product/19c/dbs/initcc.ora';
--启动备库至nomount
SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1.6475E+10 bytes
Fixed Size                 12454592 bytes
Variable Size            5435817984 bytes
Database Buffers         1.0737E+10 bytes
Redo Buffers              289533952 bytes

启动监听：

lsnrctl start  LISTENER_CC
lsnrctl status LISTENER_CC

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.200.125)(PORT=30921)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_CC
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-DEC-2023 15:54:04
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/19c/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/openstack_droracle/listener_cc/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.200.125)(PORT=30921)))
Services Summary...
Service "cc" has 1 instance(s).
  Instance "cc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

验证服务名解析：

tnsping cc
tnsping dr_cc 
--在rman中会用到---

oracle@openstack_droracle[/oracle/product/19c/network/admin]$ tnsping cc
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-DEC-2023 15:55:37
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.18)(PORT = 30921))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc)))
OK (0 msec)
oracle@openstack_droracle[/oracle/product/19c/network/admin]$ tnsping dr_cc
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-DEC-2023 15:55:45

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.125)(PORT = 30921)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc)))
OK (0 msec)

主备测试一下
$ sqlplus sys/"1jian8Shu)"@cc as sysdba
$ sqlplus sys/'1jian8Shu)'@dr_cc as sysdba    #@之后是tns的名字

五、使用RMAN duplicate主库到备库

检查数据库名称：

show parameter name

连接RMAN并duplicate主库到备库:(主库操作）

su - oracle
rman target sys/"password"@cc auxiliary sys/"password"@dr_cc nocatalog
--rman表示要运行RMAN工具，target sys/"password"@cc表示指定目标数据库的连接信息，其中sys是用户名，"password"是密码，
--cc是目标数据库的服务名。auxiliary sys/"password"@dr_cc表示指定辅助数据库的连接信息，
--其中sys是用户名，"password"是密码，dr_cc是辅助数据库的服务名tns文件设置的。
--nocatalog表示不使用RMAN目录进行备份和恢复操作。
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Dec 15 16:06:02 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CC (DBID=2357588639)
using target database control file instead of recovery catalog
connected to auxiliary database: CC (not mounted)

RMAN> 

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


--开始数据复制进程----
  datafile 24 switched to datafile copy
input datafile copy RECID=24 STAMP=1155658526 file name=/ccdata/DR_CC/idx_cpc_01.dbf
datafile 25 switched to datafile copy
input datafile copy RECID=25 STAMP=1155658526 file name=/ccdata/DR_CC/idx_crm_01.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=26 STAMP=1155658526 file name=/ccdata/DR_CC/tab_spc_002.dbf
datafile 27 switched to datafile copy
input datafile copy RECID=27 STAMP=1155658526 file name=/ccdata/DR_CC/idx_spc_002.dbf
datafile 28 switched to datafile copy
input datafile copy RECID=28 STAMP=1155658526 file name=/ccdata/DR_CC/tab_spc_003.dbf
Finished Duplicate Db at 15-DEC-23
--看到这个就代表进程正常结束

六、验证同步

复制完成后检查备库状态，进入备库验证角色信息

archive log list;
--归档模式已打开

select database_role, protection_mode, protection_level, open_mode from v$database;
--数据库角色应为PHYSICAL STANDBY，打开模式为MOUNTED

SQL> select database_role from v$database;
SQL> select instance_name from v$instance;


开启日志应用进程：
SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;

PROCESS       BLOCK#     BLOCKS STATUS        SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH               0          0 CONNECTED             0
DGRD               0          0 ALLOCATED             0
DGRD               0          0 ALLOCATED             0
ARCH               0          0 CONNECTED             0
ARCH               0          0 CONNECTED             0
ARCH               0          0 CONNECTED             0
RFS                0          0 IDLE                  0
RFS             1824          2 IDLE              15600
RFS                0          0 IDLE                  0
MRP0            1825    4194304 APPLYING_LOG      15600

--主库的ARCH进程负责向备库发送日志；
--备库的RFS进程负责接收日志，没有此进程则备库无法接收，说明dataguard没有搭建成功；
--MRP0是将接收到的日志应用到数据库中的进程。主库切换日志后，MRP0的序列号会变，主
--库的LNS进程序列号与备库的MRP0一致

此时主库状态：
SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;

PROCESS       BLOCK#     BLOCKS STATUS        SEQUENCE#
--------- ---------- ---------- ------------ ----------
DGRD               0          0 ALLOCATED             0
ARCH               0          0 CONNECTED             0
DGRD               0          0 ALLOCATED             0
ARCH               1       2790 CLOSING           15599
ARCH           10240       1653 CLOSING           15598
ARCH               1       1860 CLOSING           15596
LNS                0          0 OPENING           15597
DGRD               0          0 ALLOCATED             0
LNS             1909          2 WRITING           15600

9 rows selected.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archcc
Oldest online log sequence     15598
Next log sequence to archive   15600
Current log sequence           15600  ***这里和MRP0的序列号相同

主库切换归档日志验证同步

主库 sqlplus>alter system switch logfile;
SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archcc
Oldest online log sequence     15599
Next log sequence to archive   15601
Current log sequence           15601

--那么备库此时状态应该是：

SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;

PROCESS       BLOCK#     BLOCKS STATUS        SEQUENCE#
--------- ---------- ---------- ------------ ----------
ARCH               0          0 CONNECTED             0
DGRD               0          0 ALLOCATED             0
DGRD               0          0 ALLOCATED             0
ARCH               0          0 CONNECTED             0
ARCH               0          0 CONNECTED             0
ARCH            2048        368 CLOSING           15600
RFS                0          0 IDLE                  0
RFS              145          2 IDLE              15601
RFS                0          0 IDLE                  0
MRP0             146    4194304 APPLYING_LOG      15601

七、清理dataguard配置和停掉dr环境的实例

主库：

alter system set log_archive_dest_state_2 = 'defer';
alter system reset db_file_name_convert scope=spfile;
alter system reset log_file_name_convert scope=spfile;

备库：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

#主库没有LNS###
SQL> select process,block#,blocks ,status ,sequence# from v$managed_standby;

PROCESS       BLOCK#     BLOCKS STATUS        SEQUENCE#
--------- ---------- ---------- ------------ ----------
DGRD               0          0 ALLOCATED             0
ARCH            2048       1814 CLOSING           15940
DGRD               0          0 ALLOCATED             0
ARCH           10240       1284 CLOSING           15938
ARCH           51200       1847 CLOSING           15939
ARCH            2048       2021 CLOSING           15936
DGRD               0          0 ALLOCATED             0
DGRD               0          0 ALLOCATED             0

8 rows selected.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      defer
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable

删除实例：

sqlplus / as sysdba
SQL> shutdown immediate;

--确认数据库实例已停止：使用以下命令检查数据库实例是否已完全停止--
ps -ef | grep pmon

--删除---
find $ORACLE_BASE/ -name cc
find $ORACLE_BASE/ -name dr_dr_cc