ADG配置（主备库环境）

mac2022-06-30 112

@font-face { font-family: "Courier New"; }@font-face { font-family: "宋体"; }@font-face { font-family: "Cambria Math"; }@font-face { font-family: "@宋体"; }@font-face { font-family: "Calibri"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; text-align: justify; font-size: 10.5pt; font-family: "Calibri", sans-serif; }p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph { margin: 0cm 0cm 0.0001pt; text-align: justify; text-indent: 21pt; font-size: 10.5pt; font-family: "Calibri", sans-serif; }.MsoChpDefault { font-size: 10pt; font-family: "Calibri", sans-serif; }div.WordSection1 { }ol { margin-bottom: 0cm; }ul { margin-bottom: 0cm; }

--主库

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> col dest_name for a30

SQL> col db_unique_name for a20

SQL> SELECT DEST_NAME,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME,STATUS FROM V$ARCHIVE_DEST_STATUS where rownum <3;

--备库

SQL> set linesize 1000

SQL> set pagesize 1000

SQL> col dest_name for a30

SQL> col db_unique_name for a20

SQL> SELECT DEST_NAME,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME,STATUS FROM V$ARCHIVE_DEST_STATUS where rownum <3;

确认参数log_archive_dest_2

主库

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=fpyjbak sync lgwr vali

d_for=(online_logfiles,primary

_role) db_unique_name=fpyjbak

备库：

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string service=fpyj sync lgwr valid_f

or=(all_logfiles,all_roles) db

_unique_name=fpyj

修改备库参数

SQL> alter system set log_archive_dest_2 ='service=fpyj sync lgwr valid_for=(online_logfiles,primary_role) db_unique_name=fpyj' scope=spfile;

System altered.

--------------------------------主库备库参数篇--------------------------------------------

db_name

db_unique_name

global_names

instance_name

service_names

主库

fpyj

备库

fpyj

Fpyjbak

Fpyj

fpyj

主库：

备库：

主库初始化参数

fpyj.__db_cache_size=5200936960

fpyj.__java_pool_size=67108864

fpyj.__large_pool_size=83886080

fpyj.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

fpyj.__pga_aggregate_target=2147483648

fpyj.__sga_target=6442450944

fpyj.__shared_io_pool_size=0

fpyj.__shared_pool_size=1040187392

fpyj.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/fpyj/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/fpyj/controlfile/control01.ctl','/oradata/fpyj/controlfile/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/oradata/fpyj/datafile','/oradata/fpyj/datafile'

*.db_name='fpyj'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=fpyjXDB)'

*.fal_client='FPYJ'

*.fal_server='FPYJBAK'

*.log_archive_config='DG_CONFIG=(fpyj,fpyjbak)'

*.log_archive_dest_1='LOCATION=/oradata/fpyj/arch_dir VALID_FOR=(all_logfiles,all_roles) db_unique_name=fpyj'

*.log_archive_dest_2='service=fpyjbak sync lgwr valid_for=(online_logfiles,primary_role) db_unique_name=fpyjbak'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=10

*.log_file_name_convert='/oradata/fpyj/logfile','/oradata/fpyj/logfile'

*.open_cursors=300

*.pga_aggregate_target=2147483648

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=6442450944

*.undo_tablespace='UNDOTBS1'

备库初始化参数

fpyj.__db_cache_size=5200936960

fpyj.__java_pool_size=67108864

fpyj.__large_pool_size=83886080

fpyj.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

fpyj.__pga_aggregate_target=2147483648

fpyj.__sga_target=6442450944

fpyj.__shared_io_pool_size=0

fpyj.__shared_pool_size=1040187392

fpyj.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/fpyj/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/fpyj/controlfile/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/oradata/fpyj/datafile','/oradata/fpyj/datafile'

*.db_name='fpyj'

*.db_unique_name='FPYJBAK'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=fpyjXDB)'

*.fal_client='FPYJBAK'

*.fal_server='FPYJ'

*.log_archive_config='DG_CONFIG=(fpyj,fpyjbak)'

*.log_archive_dest_1='location=/oradata/fpyj/logfile valid_for=(all_logfiles,all_roles) db_unique_name=fpyjbak'

*.log_archive_dest_2='service=fpyj sync lgwr valid_for=(online_logfiles,primary_role) db_unique_name=fpyj'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=10

*.log_file_name_convert='/oradata/fpyj/logfile','/oradata/fpyj/logfile'

*.open_cursors=300

*.pga_aggregate_target=2147483648

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='FPYJ'

*.sga_max_size=8053063680

*.sga_target=6442450944

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

duplicate.sql脚本如下：

文本如下：

duplicate target database for standby nofilenamecheck

from active database

dorecover

spfile

set db_unique_name='fpyj'

set log_archive_dest_1='location=/oradata/fpyj/logfile valid_for=(all_logfiles,all_roles) db_unique_name=fpyj'

set log_archive_dest_2='service=fpyj197 sync lgwr valid_for=(online_logfiles,primary_role) db_unique_name=fpyj'

set standby_file_management='auto'

#set service_name='fpyj'

set fal_server='fpyj197'

set fal_client='fpyj26'

set control_files='/oradata/fpyj/controlfile/control01.ctl'

#set memory_target='4400m'

set sga_max_size='8053063680'

set diagnostic_dest='/u01/app/oracle'

set audit_file_dest='/u01/app/oracle/admin/fpyj/adump'

set DB_FILE_NAME_CONVERT='/oradata/fpyj/datafile','/oradata/fpyj/datafile'

set LOG_FILE_NAME_CONVERT='/oradata/fpyj/logfile','/oradata/fpyj/logfile';

指定 db_unique_name=fpyjbak，备库信息如下：

备库的service_name 变为 fpyjbak，和主库不符。不可取

指定 db_unique_name=fpyj，备库信息如下：

备库的service_name 仍为 fpyj，和主库相符。可取

检查主、备库同步状态及日志的应用状态

col dest_name for a20

col status for a8

col database_mode for a15

col recovery_mode for a15

col protection_mode for a20

col destination for a15

col sdb_log_count for 99999

col sdb_log_act for 99999

col archived_seq# for 99999

col applied_seq# for 99999

col error for a20

select dest_id, dest_name, status,database_mode,recovery_mode ,protection_mode ,destination ,standby_logfile_count sdb_log_count, standby_logfile_active sdb_log_act ,archived_seq# sdb_log_act,applied_seq# ,error , synchronization_status sync_status , gap_status from v$archive_dest_status where rownum <3;

转载于:https://www.cnblogs.com/iyoume2008/p/7992570.html

相关资源：（绝对干货）Oracle ADG数据库完整部署、切换、备份、运维手册

最新回复(0)