说明
oracle 12c 单例模式,在centOs下搭建Dataguard主备。
数据库静默安装
参照:https://blog.csdn.net/wenlaishiwo/article/details/132663783
主库 | 备库 |
---|---|
3个响应文件全部执行 | 只执行 runInstall, netca 2份响应文件;dbca -createDatabase 不执行 |
SID, Service_Name, DB_Unique_Name,Db_Name, Global_DbName
键 | 主库 | 备库 | 备注 |
---|---|---|---|
SID | orcl | orcl | 主备库sid相同 |
Service_Name | orcl | stdorcl | tns使用service_name,与DB_Unique_Name相同 |
DB_Unique_Name | orcl | stdorcl | dataguard要求二者不同,与Service_Name相同 |
Db_Name | orcl | orcl | dataguard要求相同,与SID相同 |
Global_DbName | orcl | stdorcl | listener使用,与Service_Name相同 |
compatible | 12.2.0.1.0 | 12.2.0.1.0 | 主备都务必与数据库版本完全一致,否则故障恢复相对麻烦 |
log_archive_dest_2 | SERVICE=stdorcl | SERVICE=orcl | 备库要有此参数,否则switchover备库将与主库关联不上 |
主备库sid相同
http://www.itpub.net/thread-1808433-1-1.html
你要明白 dg是保护数据库的 是多个数据库一个实例 所以sid一样
rac 是保护实例 是多个实例一个数据库 sid 不一样
DB_UNIQUE_NAME:
①在DataGuard中,主备库拥有相同的DB_NAME,为了区别,就必须有不同的DB_UNIQUE_NAME
②DB_UNIQUE_NAME在DG中会影响动态注册的SERVICE_NAME,即如果采用的是动态注册,则注册的SERVICE_NAME为DB_UNIQUE_NAME,但是实例还是INSTANCE_NAME,即SID
https://blog.csdn.net/christ1208/article/details/108597441
Hosts
127.0.0.1 oracle standby localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 oracle localhost localhost.localdomain localhost6 localhost6.localdomain6
21.12.8.34 oracle standby
21.12.8.33 primary
listener 和 tnsnames
listener.ora
listener.ora是服务器端用的…如果要想局域网中的其他人,能够访问我本地的oracle,要把我本机的地址写进去,如10.11.19.19(https://blog.csdn.net/panhaigang123/article/details/79147587)
配置静态注册
https://www.cnblogs.com/lkj371/p/16303850.html
静态服务(SID_LIST_LISTENER)可用于服务外部连接,无论是否在服务器上运行实例,连接非常有用到空闲或 NOMOUNT 数据库
路径:$ORACLE_HOME/network/admin/listener.ora
主库
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u12/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u12/app/oracle
备库
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdorcl)
(ORACLE_HOME = /u12/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
ADR_BASE_LISTENER = /u12/app/oracle
tnsnames.ora
主备库相同, 路径: $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STDORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdorcl)
)
)
主备互 tnsping 对方
tnsping orcl
tnsping stdorcl
主库创建redo日志文件
在主库增加standby redologfile文件(如果redo log有N组,standby redo log则需要N+1组。)
https://blog.csdn.net/kiral07/article/details/86916367
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
GROUP# THREAD# SEQUENCE# MB MEMBERS ARCHIVED STATUS
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------
1 1 4 200 1 YES INACTIVE
2 1 5 200 1 NO CURRENT
3 1 3 200 1 YES INACTIVE
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
select group#,member from v$logfile order by group#;
-- If Oracle Managed Files (OMF) is not used.
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('/u12/app/oracle/oradata/orcl/standby_redo01.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/u12/app/oracle/oradata/orcl/standby_redo02.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u12/app/oracle/oradata/orcl/standby_redo03.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u12/app/oracle/oradata/orcl/standby_redo04.log') SIZE 200M;
spfile 和 pfile
数据库从spfile启动(主备都是)。
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
spfile string /u12/app/oracle/product/12.2.0
/dbhome_1/dbs/spfileorcl.ora
若能看到spfile的路径,则证明数据库是使用spfile启动的,若没有值,则说明是用pfile启动的。
https://blog.csdn.net/qq_41944882/article/details/110548000
主库
从spfile创建pfile
create pfile from spfile;
修改pfile,路径: $ORACLE_HOME/dbs/initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=7818182656
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=67108864
orcl.__large_pool_size=100663296
orcl.__oracle_base='/u12/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=3355443200
orcl.__sga_target=9999220736
orcl.__shared_io_pool_size=536870912
orcl.__shared_pool_size=1442840576
orcl.__streams_pool_size=0
*.audit_file_dest='/u12/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/u12/app/oracle/oradata/orcl/control01.ctl','/u12/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u12/app/oracle/oradata/stdorcl/','/u12/app/oracle/oradata/orcl/'
*.db_name='orcl'
*.db_recovery_file_dest='/u12/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4227858432
*.db_unique_name='orcl'
*.diagnostic_dest='/u12/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl'
*.fal_server='stdorcl'
*.log_archive_config='DG_CONFIG=(orcl,stdorcl)'
*.log_archive_dest_1='LOCATION=/u12/app/arch/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=stdorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdorcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/u12/app/oracle/oradata/stdorcl/','/u12/app/oracle/oradata/orcl/'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.parallel_execution_message_size=8192
*.pga_aggregate_target=3177m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=9530m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
创建目录
mkdir /u12/app/arch/orcl -pv
mkdir /u12/app/oracle/fast_recovery_area
从spfile启动数据库
shutdown immediate;
create spfile from pfile;
startup mount;
alter database archivelog; #启用归档模式
alter database open;
archive log list; #查看是否启用归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u12/app/arch/orcl #确认归档路径与上文修改一致
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
确认启用强日志模式
SQL> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FORCE_LOGGING
--------------------------- ------------------------------------ ---------------------------------------------------------------------------------------------------------------------
ORCL ARCHIVELOG YES
若不是,执行
ALTER DATABASE FORCE LOGGING;
备库
复制主库pfile(实例名相同,不需重命名),修改如下:
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=7818182656
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=67108864
orcl.__large_pool_size=100663296
orcl.__oracle_base='/u12/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=3355443200
orcl.__sga_target=9999220736
orcl.__shared_io_pool_size=536870912
orcl.__shared_pool_size=1442840576
orcl.__streams_pool_size=0
*.audit_file_dest='/u12/app/oracle/admin/stdorcl/adump'
*.audit_trail='NONE'
*.compatible='12.2.0.1.0'
*.control_files='/u12/app/oracle/oradata/stdorcl/control01.ctl','/u12/app/oracle/oradata/stdorcl/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u12/app/oracle/oradata/orcl/','/u12/app/oracle/oradata/stdorcl/'
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='stdorcl'
*.diagnostic_dest='/u12/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='stdorcl'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(stdorcl,orcl)'
*.log_archive_dest_1='LOCATION=/u12/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdorcl'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/u12/app/oracle/oradata/orcl/','/u12/app/oracle/oradata/stdorcl/'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.parallel_execution_message_size=8192
*.pga_aggregate_target=3177m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=9530m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
上传到备库
创建目录
mkdir -p /u12/app/oracle/admin/stdorcl/adump
mkdir -p /u12/app/oracle/diag/rdbms/std/stdorcl/trace
mkdir -p /u12/app/arch/stdorcl
mkdir -p /u12/app/oracle/oradata/stdorcl
mkdir -p /u12/app/oracle/oradata/standbylog
mkdir -p /u12/app/oracle/diag/rdbms/stdorcl/trace
启动数据库nomount
create spfile from pfile;
startup nomount;
密码文件(orapwd)
主备库分别单独生成,密码一致。(笔者在主库生成,复制到备库始终报用户名密码错误。备库在startup nomount 之后执行)
orapwd file=/u12/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl password=YOURPASSWORD force=y
rman duplicate
在备库上操作
rman target sys/YOURPASSWORD@orcl auxiliary sys/YOURPASSWORD@stdorcl
(主库若未启动,则进行启动。若提示tns not reachable, 重启监听)
从主库复制
duplicate target database for standby from active database nofilenamecheck;
复制完成,数据库自动到mount状态, open数据库后再应用standby。
select status from v$instance;
alter database open;
备库启动日志应用
alter database recover managed standby database disconnect from session;
--或者
alter database recover managed standby database using current logfile disconnect;
--查看db_name,打开模式,数据库角色,保护模式,保护级别
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
switch_status
To Standby 主库正常状态
Not Allowed 备库正常状态(主库切换到standby时,会变更为 To Primary; log_archive_dest_2是关键参数,务必设置正确,否则switch over将不能同步)
--若打开模式不对,可能未open数据库即应用了standby,关闭重开
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
同步验证
日志
#主库(多次)
alter system switch logfile;
#主备库分别执行
archive log list;
select thread#, max (sequence#) from v$log_history group by thread#;
表
#主库
create table test as select * from dba_objects where rownum < 101;
drop table test purge;
#备库查询验证
select * from test;
#主备库验证
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
参考
- https://oracle-base.com/articles/12c/data-guard-setup-using-broker-12cr1
- https://blog.csdn.net/kiral07/article/details/86916367
- https://www.cnblogs.com/andy6/p/6533635.html
- https://blog.csdn.net/qq_41944882/article/details/110548000
- http://www.itpub.net/thread-1808433-1-1.html
- https://blog.csdn.net/haibusuanyun/article/details/11210681
- https://blog.csdn.net/christ1208/article/details/108597441