文章目录
开启主库force logging和归档
--检查归档模式是否开启
archive log list
--检查force logging是否开启
SELECT LOG_MODE,FLASHBACK_on,FORCE_LOGGING FROM V$DATABASE;
alter database force logging;
#停监听
lsnrctl status
lsnrctl stop LISTENER
#关闭数据库
sqlplus / as sysdba
shutdown immediate;
--主库启动到Mount状态
startup mount;
--开启归档
alter database archivelog;
--打开主库
alter database open;
--测试切换归档，确保归档正常
alter system switch logfile;
--观察alert 日志无异常报错ORA-
配置主库参数
alter system set db_unique_name=xsjan scope=both;
alter system set log_archive_config='dg_config=(xsjan,xsjandg)' scope=both;
alter system set log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=xsjan' scope=both sid='*';
alter system set log_archive_dest_state_2='DEFER' scope=both sid='*';
alter system set log_archive_dest_2='service=TNS_SDG LGWR ASYNC NOAFFIRM COMPRESSION=ENABLE delay=0 reopen=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=xsjandg' scope=both sid='*';
alter system set log_archive_max_processes = 8 scope=both;
alter system set remote_login_passwordfile='exclusive' scope=spfile sid='*';
alter system set fal_server=tns_sdg;
alter system set standby_file_management=auto scope=both;
alter system set db_file_name_convert='/oradata/xsjan/','+DATA' scope=spfile;
alter system set log_file_name_convert='/oraarch/xsjan/','+FRA' scope=spfile;
alter system set "_ktb_debug_flags" = 8 scope=both;
| 参数名 | 说明 |
|---|---|
| db_unique_name | 用于区分主库与备库 |
| log_archive_config | 用于控制发送归档日志到远程位置、接收远程归档日志，dg_config不分顺序 |
| log_archive_dest_1 | 用于指定本地归档存放路径、valid_for指定传输的内容，使用all_logfiles,all_roles统一存放standby log,不要设置standby_archive_dest |
| log_archive_dest_2 | 用于指定归档推送目的地，LGWR ASYNC NOAFFIRM表示使用最大性能模式，即使DG库异常也不会影响主库运行 |
| log_archive_dest_state_2 | 用于控制归档推送状态，先设置为defer（不推送） |
| log_archive_max_processes | 用于指定归档进程个数 |
| remote_login_passwordfile | 用于限制管理员远程登录，默认为NONE，需要修改为EXCLUSIVE或SHARED，需要重启数据库生效 |
| fal_server | FAL指获取归档日志(Fetch Archived Log) |
| standby_file_management | 用于控制主库增删文件时，是否自动在备库做出相应的修改 |
| db_file_name_convert | 即使路径一致也建议设置，主备库参数顺序相反，主库参数顺序为’备,‘主’；备库参数顺序为’主’,‘备’，参数内容为绝对路径或者磁盘组名，如果是asm只需要磁盘组名，OMF会使用db_unique_name自动生成文件名。 |
| log_file_name_convert | 即使路径一致也建议设置，主备库参数顺序相反，主库参数顺序为’备,‘主’；备库参数顺序为’主’,‘备’，参数内容为绝对路径或者磁盘组名，如果是asm只需要磁盘组名，OMF会使用db_unique_name自动生成文件名。 |
| _ktb_debug_flags | 用于规避ADG切换导致索引坏块的问题 |
--Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数，大小和redo log一致即可
--检查日志组
select GROUP#,THREAD#,MEMBERS,BYTES/1024/1024 MB,STATUS from v$log;
select GROUP#,MEMBER,STATUS,TYPE from v$logfile;
--创建standby redolog
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 19 '+DATA' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 '+DATA' size 1G;
--检查日志组
select GROUP#,THREAD#,MEMBERS,BYTES/1024/1024 MB,STATUS from v$log;
select GROUP#,MEMBER,STATUS,TYPE from v$logfile;
复制主库密码文件到ADG库
#主库
#确认密码文件位置
su - oracle
srvctl config database -d xsjan
Password file: +DATA/xsjan/PASSWORD/pwdxsjan.256.1071437689
#从ASM复制密码文件到文件系统
su - grid
asmcmd
pwget --dbuniquename xsjan
pwcopy +DATA/xsjan/PASSWORD/pwdxsjan.256.1071437689 /tmp/orapwxsjandg
#从主库文件系统拷贝密码文件到ADG库文件系统
su - oracle
scp /tmp/orapwxsjandg 192.168.1.12:/tmp/
#ADG库
#导入主库密码文件
orapwd input_file='/tmp/orapwxsjandg' file='+DATA/xsjan/PASSWORD/pwdxsjandg' dbuniquename='xsjandg'
#异常处理，若出现OPW-00010: Could not create the password file. This resource has a Password File.错误
#先设置ADG库集群密码位置为空
srvctl modify database -db xsjandg -pwfile ''
#再次导入密码文件
orapwd input_file='/tmp/orapwxsjandg' file='+DATA/xsjan/PASSWORD/pwdxsjandg' dbuniquename='xsjandg'
#修改ADG库OCR中记录的数据库密码文件信息
srvctl modify database -db xsjandg -pwfile '+DATA/xsjan/PASSWORD/pwdxsjandg'
#主库
#确认密码文件位置
su - oracle
srvctl config database -d xsjan
Password file: +DATA/xsjan/PASSWORD/pwdxsjan.256.1071437689
#从ASM复制密码文件到文件系统
su - grid
asmcmd
pwget --dbuniquename xsjan
pwcopy +DATA/xsjan/PASSWORD/pwdxsjan.256.1071437689 /tmp/orapwxsjandg
#从主库文件系统拷贝密码文件到ADG库文件系统
su - oracle
scp /tmp/orapwxsjandg 192.168.1.12:/tmp/
#ADG库
#导入主库密码文件
orapwd input_file='/tmp/orapwxsjandg' file='/u01/app/oracle/product/18.0.0/db_1/dbs/orapwxsjandg' SYS=Y
#复制主库其中一个节点密码文件到备库，并按DG库实例名修改文件名
scp $ORACLE_HOME/dbs/orapwxsjan1 192.168.1.12:/u01/app/oracle/product/18.0.0/db_1/dbs/
mv orapwxsjan1 orapwxsjandg
#若没有密码文件，则用orapwd命令在主库新建，再拷贝到备库
orapwd file=orapwxsjan1
ADG库使用pfile启动到nomount
create pfile='/home/oracle/pfile_dg.ora' from spfile;
#需要修改的参数
audit_file_dest
control_files
db_create_file_dest
db_file_name_convert
log_file_name_convert
db_unique_name
fal_server
local_listener
log_archive_dest_*
log_archive_dest_state_*
remote_listener
standby_archive_dest
#修改完成后创建ADG库pfile文件
cat > /home/oracle/pfile_dg.ora
*._ktb_debug_flags=8
*.audit_file_dest='/u01/app/oracle/admin/xsjan/adump'
*.audit_trail='DB','EXTENDED'
*.compatible='18.0.0'
*.control_files='/oradata/xsjan/control01.ctl','/u01/app/oracle/admin/xsjan/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='/oradata/xsjan/'
*.db_file_name_convert='+DATA','/oradata/xsjan/'
*.db_name='xsjan'
*.db_recovery_file_dest='/oraarch/xsjan/'
*.db_recovery_file_dest_size=536870912000
*.db_unique_name='xsjandg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xsjandgXDB)'
*.fal_server='TNS_PDG'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=32456))'
*.log_archive_config='dg_config=(xsjan,xsjandg)'
*.log_archive_dest_1='location=/oraarch/xsjan valid_for=(all_logfiles,all_roles) db_unique_name=xsjandg'
*.log_archive_dest_2='service=TNS_PDG LGWR ASYNC NOAFFIRM COMPRESSION=ENABLE delay=0 reopen=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=xsjan'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=8
*.log_file_name_convert='+FRA','/oraarch/xsjan/'
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.nls_language='AMERICAN'
*.nls_length_semantics='CHAR'
*.nls_territory='AMERICA'
*.nls_timestamp_format='YYYY-MM-DD HH24:MI:SS:FF'
*.open_cursors=2048
*.optimizer_index_caching=90
*.optimizer_mode='FIRST_ROWS_100'
*.pga_aggregate_target=10240m
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=2048
*.sga_target=89008m
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
mkdir -p /oradata/xsjan
mkdir -p /oraarch/xsjan
mkdir -p /u01/app/oracle/admin/xsjan/adump
sqlplus / as sysdba
startup nomount pfile='/home/oracle/pfile_dg.ora';
启动ADG库监听
cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/18.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#配置监听端口
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xsjan2)(PORT = 32456))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC32456))
)
)
#加入静态注册信息
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = xsjandg)
(ORACLE_HOME = /u01/app/oracle/product/18.0.0/db_1)
(SID_NAME = xsjandg)
)
)
lsnrctl stop
lsnrctl start
vi $ORACLE_HOME/network/admin/tnsnames.ora
TNS_SDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 32456))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xsjandg)
)
)
vi $ORACLE_HOME/network/admin/tnsnames.ora
TNS_PDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 32456))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xsjan)
(FAILOVER_MODE =
(TYPE = session)
(METHOD = BASIC)
)
)
)
TNS_SDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 32456))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xsjandg)
)
)
sqlplus 'sys/"oracle"'@TNS_PDG as sysdba
sqlplus 'sys/"oracle"'@TNS_SDG as sysdba
sqlplus / as sysdba
create spfile from pfile='/home/oracle/pfile_dg.ora';
--重启ADG库
shutdown immediate
exit
sqlplus / as sysdba
startup nomount
#连接主库与ADG库
rman target 'sys/"oracle"'@TNS_PDG auxiliary 'sys/"oracle"'@TNS_SDG
#执行复制
duplicate target database for standby from active database;
select inst_id,sid,serial#,opname,COMPLETE,trunc(((to_char(last_update_time,'dd')-to_char(start_time,'dd'))*60*24+(to_char(last_update_time,'hh24')-to_char(start_time,'hh24'))*60 +(to_char(last_update_time,'mi')-to_char(start_time,'mi')))*(100-complete)/complete) min from
( SELECT inst_id,sid,serial#,opname,ROUND(SOFAR / TOTALWORK * 100, 2) COMPLETE,LAST_UPDATE_TIME,START_TIME FROM gV$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK) t;
主库开启投递归档
alter system set log_archive_dest_state_2='ENABLE' scope=both sid='*';
--检查主库归档投递状态
SELECT DEST_NAME,STATUS,DESTINATION,ERROR FROM V$ARCHIVE_DEST WHERE ROWNUM<=3 ORDER BY DEST_NAME ;
打开ADG库
sqlplus / as sysdba
alter database open;
ADG库开启日志应用
sqlplus / as sysdba
alter database recover managed standby database parallel 8 using current logfile disconnect from session;
检查ADG同步情况
select thread#,max(sequence#) from v$archived_log group by thread# order by 1;
set line 200 pagesize 300
select a.THREAD#,a.last_archived,b.last_applied,a.last_archived-b.last_applied num_lack
from
(SELECT THREAD#, max(SEQUENCE#) last_archived FROM V$ARCHIVED_LOG
group by thread# ) a,
(SELECT THREAD#, max(SEQUENCE#) last_applied FROM V$ARCHIVED_LOG
where applied='YES' group by thread# ) b
where a.THREAD#=b.THREAD#
order by 4,1;
col process for a10
col status for a20
set line 200 pagesize 500
SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;
ADG库启停维护
--停止备库日志应用，在备库执行
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--停止主库投递归档到备库，在主库执行
alter system set log_archive_dest_state_2=defer scope=both;
alter database recover managed standby database parallel 8 using current logfile disconnect from session;
原文始发于微信公众号（醒狮运维）：Oracle数据库实战第二篇--18c单实例ADG搭建
- 左青龙
- 微信扫一扫
-
- 右白虎
- 微信扫一扫
-
评论