data guard 的部署
tnsname.ora
fal_client
fal_server
data guard 的部署
************************************************************
创建物理standby：
一、准备工作：
1、确保数据库处于归档模式
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4122M
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest_size=1G;
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2231872 bytes
Variable Size 436208064 bytes
Database Buffers 281018368 bytes
Redo Buffers 2908160 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
2、并打开 Forced Logging 模式
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
2、创建密码文件(保持两个节点存在，否则集群切换到另一节点就会找不到密码文件)
orapwd file=$ORACLE_HOME/dbs/orapworc1 password=welcome1 force=y ignorecase=y
3、更改数据库的name（db_unique_name）
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL> alter system set db_unique_name=orcl scope=spfile;
System altered.
4、更改standby_file_management
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
5、修改fal服务器端和客户端配置
SQL> alter system set fal_client=orcl;
System altered.
SQL> alter system set fal_server=orcldg;
System altered.
SQL> show parameter fal;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string ORCL
fal_server string ORCLDG
6、修改log_archive_config参数
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
SQL> alter system set log_archive_config='dg_config=(orcl,orcldg)';
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(orcl,orcldg)
7、修改log_archive_dest_2参数
SQL> alter system set log_archive_dest_state_2='defer';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
SQL> alter system set log_archive_dest_2='service=orcldg lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=orcldg lgwr sync affir
m valid_for=(online_logfiles,p
rimary_role) db_unique_name=or
cldg
SQL>
9，进入rman进行备份
查看数据库的大小
select sum(bytes)/1024/1024/1024 from v$datafile;
备份控制文件
run {
backup database format '/home/oracle/rman_backup/FULL_%U.bak';
backup archivelog all format '/home/oracle/rman_backup/ARC_%U.bak';
backup format '/home/oracle/rman_backup/standby_%U.ctl' current controlfile for standby;
}
backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby;
恢复控制文件的方法
RMAN> restore standby controlfile from '/home/oracle/rman_backup/standby_0tpk53jq_1_1.ctl'; -----路径为控制文件备份片的路径
10，创建orcldg的pfile
SQL> create pfile='/home/oracle/orcldgpfile.ora' from spfile;
File created.
11、传输orcldg的pfile
[oracle@oracle1 ~]$ scp orcldgpfile.ora oracle@192.168.74.21:/home/oracle
oracle@192.168.74.21's password:
orcldgpfile.ora
12、修改参数文件（备库）
[oracle@oracle ~]$ vim orcldgpfile.ora
[oracle@oracle ~]$ cat orcldgpfile.ora
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/fast_recovery_area/orcldg/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=1073741824
*.db_unique_name='ORCLDG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='dg_config=(orcldg,orcl)'
*.log_archive_dest_2='service=orcl lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.memory_target=725614592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/admin/orcldg/adump
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg/
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/
13、修改注册文件
[oracle@oracle ~]$ cat /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N
orcldg:/u01/app/oracle/product/11.2.0/dbhome_1:N
14、尝试启动数据库到nomount状态并创建spfile从pfile中
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [oracle] ? orcldg
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 3 16:56:59 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/orcldgpfile.ora';
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2231872 bytes
Variable Size 432013760 bytes
Database Buffers 285212672 bytes
Redo Buffers 2908160 bytes
SQL> create spfile from pfile='/home/oracle/orcldgpfile.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
15、启动数据库到nomount
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2231872 bytes
Variable Size 432013760 bytes
Database Buffers 285212672 bytes
Redo Buffers 2908160 bytes
16、修改convert参数
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile;
System altered.
重启实例convert 参数生效
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 722366464 bytes
Fixed Size 2231872 bytes
Variable Size 432013760 bytes
Database Buffers 285212672 bytes
Redo Buffers 2908160 bytes
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/orcl,
/u01/app/oracle/oradata/orcldg
log_file_name_convert string /u01/app/oracle/oradata/orcl,
/u01/app/oracle/oradata/orcldg
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg
18.配置监听服务名
[oracle@oracle1 ~]$ tnsping orcldg-192.168.74.21
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2017 17:37:00
Copyright (c) 1997, 2011, 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.74.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcldg)))
OK (10 msec)
[oracle@oracle1 ~]$
[oracle@oracle ~]$ tnsping orcl-192.168.74.22
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 17:36:51
Copyright (c) 1997, 2011, 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.74.22)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
配置静态监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcldg)
)
(SID_DESC =
(GLOBAL_DBNAME = Oracle8)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
配置服务名（）名称相同
[oracle@oracle admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldg)
)
)
[oracle@oracle1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldg)
)
)
[oracle@oracle admin]$ vim listener.ora
[oracle@oracle admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:36
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
The command completed successfully
[oracle@oracle admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:45
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 03-MAR-2017 18:20:25
Uptime 0 days 0 hr. 22 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Services Summary...
Service "Oracle8" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcldg" has 2 instance(s).
Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcldg", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle admin]$ rman target sys/oracle@orcl-192.168.74.22 auxiliary sys/oracle@orcldg nocatalog;
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 3 18:42:56 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1464936028)
using target database control file instead of recovery catalog
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database;
duplicate target database for standby from active database nofilenamecheck;
restore standby controlfile from '/home/oracle/rman_backup/standby_05u0o7s4_1_1.ctl'
restore database;
配置redologfile
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcldg MOUNTED
select member from v$logfile;
SQL> select bytes/1024/1024 from v$log;
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcldg/redo04.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcldg/redo05.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcldg/redo06.log') size 50M;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcldg/redo07.log') size 50M;
Database altered.
alter database add standby logfile group 4 size 50M ,group 5 size 50M ,group 6 size 50M, group 7 size 50M;
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;
开启日志应用进程：
SQL> alter database recover managed standby database parallel 2 using current logfile disconnect;
Database altered.
开启同步开关配置
SQL> alter system set log_archive_dest_state_2='enable';
System altered.
查看数据库的主角色（保护级别，模式等）
SQL>
DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
查看日志应用
SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
18 YES
SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
18 YES
SQL> /
MAX(SEQUENCE#) APPLIED
-------------- ---------
19 IN-MEMORY
18 YES
SQL> /
MAX(SEQUENCE#) APPLIED
-------------- ---------
19 YES
打开数据库
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select max(sequence#),applied from v$archived_log group by applied;
MAX(SEQUENCE#) APPLIED
-------------- ---------
20 YES
SQL> /
MAX(SEQUENCE#) APPLIED
-------------- ---------
21 YES
SQL> /
MAX(SEQUENCE#) APPLIED
-------------- ---------
21 YES
SQL> select * from t1;
查看数据库的状态
SQL> select name,open_mode,protection_level,database_role,switchover#,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- ----------- --------------------
ORCL READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY 1464842362 NOT ALLOWED
以上active dataguard部署完毕
######################################################################################################################################################################
logcal 数据库（逻辑standby）
3.配置主备监听(必须保证双向监听正常，并且配置RAC集群两个节点)
4、设置初始化参数（primary和standby）
主库参数
*.log_archive_config='dg_config=(kc,dg)'
*.log_archive_dest_2='service=dg db_unique_name=dg lgwr async'
*.log_archive_dest_state_2='ENABLE'
备库参数(config中应该是 net service name,而不是sid或service name)
*.db_unique_name='dg'
*.undo_management='AUTO'
*.log_archive_config='dg_config=(kc,dg)'
*.db_file_name_convert='+DATA01/kc/datafile','+DATA01/dg/datafile'
*.log_file_name_convert='+DATA01/kc/onlinelog','DATA01/dg/onlinelog'
*.standby_file_management=auto
*.fal_client='kc'
*.fal_server='kc'
二、详细步骤：
1、主库创建备份(手工复制数据文件或通过 RMAN)
2、主库创建控制文件
通过下列语句为 standby 数据库创建控制文件
SQL> alter database create standbycontrolfile as 'd:\backup\jsspdg01.ctl';
3、主库创建并修改初始化参数文件
SQL> create pfile='d:\backup\initjsspdg.ora' from spfile;
注意 primary 和 standby 不同角色的属性配置，注意文件路径。
4、复制文件到 standby 服务器
数据文件，控制文件，修改过的初始化参数文件，注意路径。
5、配置 standby 数据库
6、启动 standby
SQL> STARTUP MOUNT;
需要针对每个thread创建3个standby日志组
SQL> alter database add standby logfile thread 1
group 5 ('+DATA01/dg/onlinelog/st_1_5.log')size 50M,
group 6 ('+DATA01/dg/onlinelog/st_1_6.log')size 50M,
group 7 ('+DATA01/dg/onlinelog/st_1_7.log')size 50M;
SQL> alter database add standby logfile thread 2
group 8 ('+DATA01/dg/onlinelog/st_2_8.log')size 50M,
group 9 ('+DATA01/dg/onlinelog/st_2_9.log')size 50M,
group 10 ('+DATA01/dg/onlinelog/st_2_10.log')size 50M;
启动 redo 应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
启动实时应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
7、停止 standby
正常情况下，我们停止也应该是先停止 redo 应用，可以通过下列语句：
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
然后再停止 standby 数据库
SQL> SHUTDOWN IMMEDIATE;
8.常用状态查询：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
select process,status from v$managed_standby;
show parameter instance_name;
select max(sequence#) from v$archived_log;
9.准备切换：
select switchover_status from v$database;
alter database commit to switchover to physical standby;
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/12.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
