1.环境
主库 | 备库 |
---|---|
主机名:zzw | zzwdg |
操作系统:centos7.7 | centos7.7 |
IP:192.168.198.146 | 192.168.198.145 |
数据库版本:12.2.0.1 | 12.2.0.1 |
$ORACLE_BASE:/z01/app/oracle | /z01/app/oracle |
$ORACLE_HOME:/z01/app/oracle/product/12.0.1/ db_1 | /z01/app/oracle/product/12.0.1/ db_1 |
$ORACLE_SID:zzw | zzw |
数据库安装:安装数据库软件,建立监听,创建库 | 只安装数据库软件,建立监听 |
归档模式:开启 | 无数据库 |
2.主库配置
2.1数据库开启归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /z01/app/oracle/product/12.0.1/db_1/dbs/arch
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
图中为开启归档模式
方法可参照启动或关闭oracle的归档https://blog.csdn.net/weixin_45833902/article/details/105208260
2.2强制日志模式
SQL> alter database force logging;
SQL> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FORCE_LOGGING
--------- ------------ ---------------------------------------
ZZW ARCHIVELOG YES
2.3创建standby redolog日志组
这里要注意standby redolog日志组要与redo日志组文件大小相同,数量多一个用来冗余
SQL> select * from v$log
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------
1 1 17 209715200 512 1 YES INACTIVE
2 1 18 209715200 512 1 YES INACTIVE
3 1 19 209715200 512 1 NO CURRENT
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /z01/app/oracle/oradata/zzwdg/redo03.log
2 ONLINE /z01/app/oracle/oradata/zzwdg/redo02.log
1 ONLINE /z01/app/oracle/oradata/zzwdg/redo01.log
因为redo log日志组有3组所以要至少创建4个standby redolog日志组
SQL> alter database add standby logfile group 4('/z01/app/oracle/oradata/zzw/standbyredo01.log') size 512m;
Database altered.
SQL> alter database add standby logfile group 5('/z01/app/oracle/oradata/zzw/standbyredo02.log') size 512m;
Database altered.
SQL> alter database add standby logfile group 6('/z01/app/oracle/oradata/zzw/standbyredo03.log') size 512m;
Database altered.
SQL> alter database add standby logfile group 7('/z01/app/oracle/oradata/zzw/standbyredo04.log') size 512m;
Database altered.
创建成功后查看standby 日志组的信息
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /z01/app/oracle/oradata/zzwdg/redo03.log
2 ONLINE /z01/app/oracle/oradata/zzwdg/redo02.log
1 ONLINE /z01/app/oracle/oradata/zzwdg/redo01.log
4 STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo01.log
5 STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo02.log
6 STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo03.log
7 STANDBY /z01/app/oracle/oradata/zzwdg/standbyredo04.log
7 rows selected.
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
GROUP# SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------
4 0 UNASSIGNED 512
5 0 UNASSIGNED 512
6 0 UNASSIGNED 512
7 0 UNASSIGNED 512
2.4主库创建密码文件
su - oracle
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y
2.5通过spfile创建一个pfile文件
SQL> create pfile='/tmp/init.ora' from spfile;
2.6修改监听文件
[oracle@zzw ~]$ vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzw)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = zzw)
(ORACLE_HOME = /z01/app/oracle/product/12.0.1/db_1)
(SID_NAME = zzw)
)
)
重启监听服务
[oracle@zzw ~]$ lsnrctl stop
[oracle@zzw ~]$ lsnrctl start
2.7编辑网路服务名配置文件
[oracle@zzw ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
zzwdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zzw)
)
)
zzwpr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zzw)
)
)
LISTENER_ZZW =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzw)(PORT = 1521))
通过tnsping来测试
[oracle@zzw ~]$ tnsping zzwpr
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 08:42:12
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
[oracle@zzw ~]$ tnsping zzwdg
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 08:42:16
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
2.8修改参数文件
这里可以通过直接修改刚刚生成的pfile文件,重启数据库至mount状态,通过修改后的pfile文件来启动数据库
凡是添加的字段后面都会使用##来标识
[oracle@zzw tmp]$ vi /tmp/init.ora
zzw.__data_transfer_cache_size=0
zzw.__db_cache_size=322961408
zzw.__inmemory_ext_roarea=0
zzw.__inmemory_ext_rwarea=0
zzw.__java_pool_size=4194304
zzw.__large_pool_size=8388608
zzw.__oracle_base='/z01/app/oracle'#ORACLE_BASE set from environment
zzw.__pga_aggregate_target=188743680
zzw.__sga_target=553648128
zzw.__shared_io_pool_size=25165824
zzw.__shared_pool_size=180355072
zzw.__streams_pool_size=0
*.audit_file_dest='/z01/app/oracle/admin/zzw/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/z01/app/oracle/oradata/zzw/control01.ctl','/z01/app/oracle/oradata/zzw/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' ##db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映(目标数据库数据文件目录,本地数据库数据文件目录)
*.db_name='zzw'
*.db_unique_name='zzwpr' ##代表主库的唯一名,主备库都要有各自的唯一名,可自定义
*.diagnostic_dest='/z01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zzwXDB)'
*.fal_client='zzwpr' ##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name,clinent代表本地Oracle Net Service Name
*.fal_server='zzwdg' ##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name,server是目标端Oracle Net Service Name
*.local_listener='LISTENER_ZZW'
*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)' ##列出主备库上的DB_UNIQUE_NAME 参数,默认情况下,定义该参数能确保主备库数据库能够互相识别对方
*.log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog' ##本地归档路径,主库与备库都需要定义各自的online redo log的归档地址,以系统实际的存放路径为准
*.log_archive_dest_2='SERVICE=zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg' ##该参数仅当数据库角色为主库时生效,指定primary归档redo log到该参数定义的standby database上,其中SERVICE=zzwdg里面SERVICE为上面的fal_server,DB_UNIQUE_NAME=zzwdg为备库的DB_UNIQUE_NAME
*.log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' ##主数据库和备用数据库的日志文件转换目录对映(目标数据库数据文件目录,本地数据库数据文件目录)
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=177m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=528m
*.standby_file_management='AUTO' ##该参数值设置为AUTO,主库执行的表空间创建操作也会被传播到物理Standby数据库上执行
*.undo_tablespace='UNDOTBS1
之后
SQL> shutdown immediate;
SQL> create spfile from pfile='/tmp/init.ora';
SQL> startup;
通过该参数文件启动数据库
以上的参数修改也可以通过命令方式在数据库内修改
*.db_unique_name='zzwpr'
SQL> alter system set db_unique_name='zzwpr' scope=spfile;
System altered.
*.fal_client='zzwpr'
SQL> alter system set fal_client='zzwpr' scope=both;
System altered.
*.fal_server='zzwdg'
QL> alter system set fal_server='zzwdg' scope=both;
System altered.
*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)'
SQL> alter system set log_archive_config='DG_CONFIG=(zzwpr,zzwdg)' scope=both;
System altered.
*.log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog'
SQL> alter system set log_archive_dest_1='location=/z01/app/oracle/zzwdg_archivelog' scope=both;
System altered.
*.log_archive_dest_2='SERVICE=zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg'
SQL> alter system set log_archive_dest_2='SERVICE=tns_zzwdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwdg' scope=both;
System altered.
*.standby_file_management='AUTO'
SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.
*.db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'
SQL> alter system set db_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' scope=spfile;
System altered.
*.log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw'
SQL> alter system set log_file_name_convert='/z01/app/oracle/oradata/zzwdg','/z01/app/oracle/oradata/zzw' scope=spfile;
System altered.
3.备库配置
3.1先通过scp把主库的密码文件,监听文件,pfile文件copy到备库
密码文件:
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/dbs/orapworcl oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/dbs/
监听文件(listener.ora ,tnsnames.ora):
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/network/admin/
[oracle@zzw ~]$ scp /z01/app/oracle/product/12.0.1/db_1/network/admin/tnsnames.ora oracle@192.168.198.145:/z01/app/oracle/product/12.0.1/db_1/network/admin/
pfile文件:
[oracle@zzw ~]$ scp /tmp/init.ora oracle@192.168.198.145:/tmp/init.ora
3.2修改监听文件
这里只需要修改listener.ora里面的静态监听
不用修改tnsnames.ora
[oracle@zzwdg ~]$ vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /z01/app/oracle/product/12.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzwdg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = zzw)
(ORACLE_HOME = /z01/app/oracle/product/12.0.1/db_1)
(SID_NAME = zzw)
)
)
重启监听
[oracle@zzwdg ~]$ lsnrctl stop
[oracle@zzwdg ~]$ lsnrctl start
3.3tnping测试
[oracle@zzwdg ~]$ tnsping zzwpr
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 13:06:25
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.146)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
[oracle@zzwdg ~]$ tnsping zzwdg
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 06-AUG-2020 13:06:27
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.198.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zzw)))
OK (0 msec)
3.4创建主备需要的目录
[root@zzwdg ~]# su - oracle
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/adump
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/dbdump
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/admin/zzwdg/pfile
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/oradata/zzwdg
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/fast_recovery_area/zzwdg
[oracle@zzwdg ~]$ mkdir -p /z01/app/oracle/oradata/zzwdg/archivelog
3.5修改pfile参数文件
[oracle@zzwdg ~]$ vi /tmp/init.ora
zzw.__data_transfer_cache_size=0
zzw.__db_cache_size=356515840
zzw.__inmemory_ext_roarea=0
zzw.__inmemory_ext_rwarea=0
zzw.__java_pool_size=4194304
zzw.__large_pool_size=8388608
zzw.__oracle_base='/z01/app/oracle'#ORACLE_BASE set from environment
zzw.__pga_aggregate_target=188743680
zzw.__sga_target=553648128
zzw.__shared_io_pool_size=0
zzw.__shared_pool_size=171966464
zzw.__streams_pool_size=0
*.audit_file_dest='/z01/app/oracle/admin/zzwdg/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/z01/app/oracle/oradata/zzwdg/control01.ctl','/z01/app/oracle/oradata/zzwdg/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_file_name_convert='/z01/app/oracle/oradata/zzw','/z01/app/oracle/oradata/zzwdg'
*.db_name='zzw'
*.db_unique_name='zzwdg'
*.diagnostic_dest='/z01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zzwXDB)'
*.fal_client='zzwdg'
*.fal_server='zzwpr'
*.local_listener='LISTENER_ZZW'
*.log_archive_config='DG_CONFIG=(zzwpr,zzwdg)'
*.log_archive_dest_2='SERVICE=zzwpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzwpr'
*.log_file_name_convert='/z01/app/oracle/oradata/zzw','/z01/app/oracle/oradata/zzwdg'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=177m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=528m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1
3.6 启动备库到nomount
SQL> shutdown immediate;
SQL> startup nomount;
4.利用RMAN在主库上执行,将主库恢复到备库上
这一步要在主库上面执行
[oracle@zzw ~]$ rman target sys/123456@zzwpr auxiliary sys/123456@zzwdg
RMAN> duplicate target database for standby from active database nofilenamecheck;
回复完成后备库处于mount状态
SQL> select status from v$instance;
STATUS
------------
MOUNTED
备库启动日志应用
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> set pagesize 100;
SQL> select sequence#,applied from v$archived_log order by 1;
SEQUENCE# APPLIED
---------- ---------
76 YES
77 YES
78 YES
到此已经实现了备库对主库的数据追平
要想实现主备数据实时同步还要再备库执行以下操作
SQL>alter database recover managed standby database using current logfile disconnect from session;
查看DG是否是实时应用
SQL> select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status
DEST_NAME STATUS RECOVERY_MODE
------------------------------ --------- -----------------------
LOG_ARCHIVE_DEST_1 VALID MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_2 VALID IDLE
LOG_ARCHIVE_DEST_3 INACTIVE IDLE
LOG_ARCHIVE_DEST_4 INACTIVE IDLE
LOG_ARCHIVE_DEST_5 INACTIVE IDLE
LOG_ARCHIVE_DEST_6 INACTIVE IDLE
LOG_ARCHIVE_DEST_7 INACTIVE IDLE
LOG_ARCHIVE_DEST_8 INACTIVE IDLE
LOG_ARCHIVE_DEST_9 INACTIVE IDLE
LOG_ARCHIVE_DEST_10 INACTIVE IDLE
LOG_ARCHIVE_DEST_11 INACTIVE IDLE
5.主备切换
5.1 将主库切换成物理备库
切换前先查看主备库状态情况
主库:
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
备库:
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICAL STANDB
SQL> alter database commit to switchover to physical standby with session shutdown;
切换为备份数据库后先关闭数据库,启至mount状态
SQL> shutdown immediate
SQL> startup mount;
5.2备库执行切换成主库,完成后数据库是mount
SQL> alter database commit to switchover to primary with session shutdown;
将备库切换为主库,此时数据库处于mount状态,再启库
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
5.3打开现在的备库(原主库)的归档应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
5.4检查切换后的主备库的角色
主库:
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY
备库:
SQL> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICA