8.dg搭建_dup方式
说明：
搭建ADG时，备库只需要安装oracle软件即可，不用创建实例
1.开启归档日志（只在主库上做）:
select name,log_mode,force_logging from v$database;
alter database archivelog; --启动归档
alter database force logging;
select name,log_mode,force_logging from v$database;
2.复制密码文件给备库
3.创建redolog日志组（此步骤只在主库上做），通常standby log日志一般比redo log多一组，且standby log日志位置要和redo log日志保持一致,大小也保持一致
alter database add standby logfile group 4 '/home/oracle/u01/app/oracle/oradata/standbylog/std_redo04.log' size 50m;
alter database add standby logfile group 5 '/home/oracle/u01/app/oracle/oradata/standbylog/std_redo05.log' size 50m;
.......
查看standby 日志组的信息：
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
SQL> select group#,member from v$logfile order by group#;
4.修改主库的spfile参数文件,先创建一个pfile文件，然后再pfile中添加如下内容（此步骤只在主库上做）
create pfile from spifle;
以下内容是需要新增加的：
*.db_unique_name='pri'
*.log_archive_config='dg_config=(pri,std)'
*.log_archive_dest_1='location=/home/oracle/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=pri'
*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='std'
*.fal_client='pri'
*.db_file_name_convert='/home/oracle/u01/app/oracle/oradata/std','/home/oracle/u01/app/oracle/oradata/pri'
*.log_file_name_convert='/home/oracle/u01/app/oracle/oradata/std','/home/oracle/u01/app/oracle/oradata/pri'
*.standby_file_management='auto'
create spfile from pfile='?/dbs/init${ORACLE_SID}.ora;
数据库启动到mount状态:
startup mount;
5.添加静态监听（主库、备库都要做）
主库监听配置：
配置listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.92)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pri_DGMGRL)
(ORACLE_HOME = /home/oracle/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = pri)
)
)
ADR_BASE_LISTENER = /home/oracle/u01/app/oracle
6.配置tnsnames.ora
pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.92)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)
std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.95)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
-- 下面两个应该是自动故障转移时用的，可以不用配置
pri_DGMGR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.92)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri_DGMGRL)
)
)
std_DGMGR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.95)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std_DGMGRL)
)
)
启动监听lsnrctl start
7.主库端把pfile拷贝给备库,注意要修改其中某些参数
*.db_unique_name='std'
*.log_archive_config='dg_config=(pri,std)'
*.log_archive_dest_1='location=/home/oracle/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=std'
*.log_archive_dest_2='service=pri valid_for=(online_logfiles,primary_role) db_unique_name=pri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=4
*.fal_server='pri'
*.fal_client='std'
*.db_file_name_convert='/home/oracle/u01/app/oracle/oradata/pri','/home/oracle/u01/app/oracle/oradata/std'
*.log_file_name_convert='/home/oracle/u01/app/oracle/oradata/pri','/home/oracle/u01/app/oracle/oradata/std'
*.standby_file_management='auto'
用修改后的pfile创建一个spfile，用于启动数据库（备库端做）：
数据库启动到nomount状态：
8.利用RMAN在备库上恢复主库（备库端做）：
rman target sys/123123@pri auxiliary sys/123123@std
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
备库启动日志应用：
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session; --这个是实时应用
SQL> select recovery_mode from v$archive_dest_status where dest_id=1;
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
SQL> select sequence#,applied from v$archived_log order by 1;
SEQUENCE# APPLIED
---------- ---------
12 YES
13 YES
14 YES
如上，如果发现有个NO的，也是正常的，说明该日志在主库上还没有归档，可以在主库上运行alter system switch logfile;命令来进行日志切换，再到备库查看日志应用情况
查看standby log是否激活
select group#,sequence#,dbid,status from v$standby_log;
GROUP# SEQUENCE# DBID STATUS
---------- ---------- ---------------------------------------- ----------
4 0 UNASSIGNED UNASSIGNED
5 0 UNASSIGNED UNASSIGNED
6 0 UNASSIGNED UNASSIGNED
7 33 1685545868 ACTIVE
8.分别查看主库和备库最大的归档序列号是否一致：
select max(sequence#) from v$archived_log;
如发现不能同步在备库端进行以下操作(重新启动):
SQL> alter database recover managed standby database cancel;
SQL> shu immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
扩展：
1.启动broken（主库和备库都切换）：
SQL> alter system set dg_broker_start=true;
2.开启闪回（主库和备库都开启）：
主操作：
SQL> alter database flashback on;
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON
-------------------- ---------------- ------------ ------------------
READ WRITE PRIMARY ARCHIVELOG YES
备操作：
SQL> alter system set dg_broker_start=true;
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on;
SQL> recover managed standby database using current logfile disconnect;
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;
OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON
------------------- ---------------- ------------ ------------------
READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG YES
3.创建DGMGRL配置文件，
主库操作：
[oracle@www ~]$ dgmgrl sys/123123@pri_dgmgr
DGMGRL> create configuration dg as primary database is pri connect identifier is pri_dgmgr;
Configuration "dg" created with primary database "pri"
DGMGRL> show configuration;
Configuration - dg
Protection Mode: MaxPerformance
Databases:
pri - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database std as connect identifier is std_DGMGR maintained as physical;
Database "std" added
DGMGRL> show configuration;
Configuration - dg
Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - dg
Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
备库操作：
$dgmgrl sys/123123 "start observer" &
主库操作：
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
DGMGRL>exit
======================================================================================
[oracle@www dbs]$exit
[root@www ~]#reboot
[root@www ~]#su - oracle
[oracle@www dbs]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 10 22:33:32 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup;
SQL> exit;
[oracle@www dbs]$lsnrctl start
======================================================================================
[oracle@www ~]$ dgmgrl sys/123123@pri_dgmgr
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
pri - Primary database
std - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
手动切换到备库测试：
/home/oracle/u01/app/oracle/diag/rdbms/pri/pri/trace日志
DGMGRL> switchover to std
Performing switchover NOW, please wait...
Operation requires a connection to instance "std" on database "std"
Connecting to instance "std"...
Connected.
New primary database "std" is opening...
Operation requires startup of instance "pri" on database "pri"
Starting instance "pri"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "std"
DGMGRL> show configuration
Configuration - dg
Protection Mode: MaxPerformance
Databases:
std - Primary database
pri - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
自动切换功能
begin
DBMS_SERVICE.CREATE_SERVICE (
service_name => 'HAHA',
network_name => 'HAHA',
aq_ha_notifications => TRUE,
failover_method => 'BASIC',
failover_type => 'SELECT',
failover_retries => 30,
failover_delay => 5);
end;
create or replace procedure cmc_taf_service_proc
is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('HAHA');
else
DBMS_SERVICE.STOP_SERVICE('HAHA');
end if;
end;
create or replace TRIGGER cmc_taf_service_trg_startup
after startup on database
begin
cmc_taf_service_proc;
end;
create or replace TRIGGER cmc_taf_manage_trg_rolechange
after db_role_change on database
begin
cmc_taf_service_proc;
end;
SQL> exec cmc_taf_service_proc;
SQL> alter system archive log current;
SQL> show parameter service_names
补充：duplicate target database for standby from active database nofilenamecheck dorecover; 如果这里报错：
This issue is similar to Bug 30776075: RMAN DUPLICATE WITH SRLS ERROR RMAN-05535: WARNING: ALL REDO LOG FILES WERE NOT DEFINED PROPERLY. This bug is awaiting the fix of the base Bug 23127716: SRL IS NOT CONVERTED BY LOG_FILE_NAME_CONVERT ON CASCADED STANDBY which at this time is currently open and under investigation. Solution The workaround for this issue is to rename the redo log files manually. 1. Issue the 'alter database' command to rename the redo log files reported in the alert.log of the standby/auxiliary: For example, if the alert log shows the following: alter database rename file '<PATH1>/<ORACLE_SID>/redo1/redo01.log' to 'broken0' execute the rename like the following: alter database rename file '<ORACLE_HOME>/dbs/broken0' to '<PATH1>/<ORACLE_SID>/redo1/redo01.log'; 2. Confirm the redo log filename have changed using the following query: select v1.thread#, v1.group#, v2.member from v$log v1, v$logfile v2 where v1.group#=v2.group# order by v1.group#; Repeat step #1 above for any redo logfile listed in the above query which has not been renamed. 3. The above steps does not create the redo log file, it just updates the controlfile with a valid location and name. Once all the redo log files have been renamed, you must execute the following to physically create them: alter database clear logfile group <REDO LOG group number>;
