ADG配置（主备库环境）

@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	fpyj	fpyj	fpyj
备库	fpyj	Fpyjbak	Fpyj	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';

 

 

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

 

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

 

1. 指定 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;