前言：此文属个人记录总结，以自身认为有必要的内容进行记录。 具体内容还是请参考官方文档。
一.准备工作
在standby端准备好目录alter database force logging;
可以先将primary端改为 maximize availability，不影响结果
为了实时应用，必须在standby使用standby redo，member尺寸与online redo一致，并且要多一组,可以现在primary端建好.
这些信息仅仅会追加至 controlfile中（从trace看到的），而不是出现在controlfile的主体创建语句中间
alter database add standby logfile group 4 ('/u02/test3/standby_redo04a.log','/u03/test3/standby_redo04b.log') size 50m;
alter database add standby logfile group 5 ('/u02/test3/standby_redo05a.log','/u03/test3/standby_redo05b.log') size 50m;
alter database add standby logfile group 6 ('/u02/test3/standby_redo06a.log','/u03/test3/standby_redo06b.log') size 50m;
alter database add standby logfile group 7 ('/u02/test3/standby_redo07a.log','/u03/test3/standby_redo07b.log') size 50m;
backup as compressed backupset database format '/u01/rman/test3/db_%u_%T.bk' include current controlfile for standby;
我的脚本也是可以用的，但是要改改，且不能加delete noprompt obsolete,否则standby controlfile就被删掉了-_-!，而且归档日志是完全不用再这里备份的
run
{
backup incremental level=0 as compressed backupset database format '/u01/rman/test3/db_%u_%T.bk' include current controlfile for standby
plus archivelog format '/u01/rman/test3/arc_%u_%T.bk' delete all input;
}
list copy;
list backup;
或者单独创建standby控制文件
backup current controlfile for standby format '/u01/rman/test3/standby_control.ctl';
问题：standby controlfile 有什么区别？ 至少我查看创建的trace，没发现与原controlfile有什么不同。
二.传输backupset&standby control, online redo&standby redo，口令文件
[oracle@redhat ~]$ scp /u01/rman/test3/* oracle@10.0.0.21:/u01/rman/test3/
oracle@10.0.0.21's password:
arc_2pnl34jo_20120913.bk 100% 564KB 564.0KB/s 00:00
arc_2snl34ll_20120913.bk 100% 7168 7.0KB/s 00:00
c-915537947-20120913-00 100% 7552KB 7.4MB/s 00:00
db_2qnl34jq_20120913.bk 100% 111MB 27.7MB/s 00:04
db_2rnl34lh_20120913.bk 100% 1104KB 1.1MB/s 00:00
oracle@10.0.0.21's password:
standby_control.ctl 100% 6944KB 6.8MB/s 00:00
[oracle@redhat test3]$ scp /u02/test3/*.log oracle@10.0.0.21:/u02/test3/
oracle@10.0.0.21's password:
redo01a.log 100% 50MB 25.0MB/s 00:02
redo02a.log 100% 50MB 25.0MB/s 00:02
redo03a.log 100% 50MB 4.2MB/s 00:12
standby_redo04a.log 100% 50MB 25.0MB/s 00:02
standby_redo05a.log 100% 50MB 25.0MB/s 00:02
standby_redo06a.log 100% 50MB 16.7MB/s 00:03
standby_redo07a.log 100% 50MB 25.0MB/s 00:02
[oracle@redhat test3]$ scp /u03/test3/*.log oracle@10.0.0.21:/u03/test3/
oracle@10.0.0.21's password:
redo01b.log 100% 50MB 2.4MB/s 00:21
redo02b.log 100% 50MB 2.3MB/s 00:22
redo03b.log 100% 50MB 3.9MB/s 00:13
standby_redo04b.log 100% 50MB 25.0MB/s 00:02
standby_redo05b.log 100% 50MB 16.7MB/s 00:03
standby_redo06b.log 100% 50MB 25.0MB/s 00:02
standby_redo07b.log 100% 50MB 25.0MB/s 00:02
[oracle@redhat ~]$ scp /u01/app/oracle/product/10.2.0.5/dbhome_1/dbs/orapwtest3 oracle@10.0.0.21:/u01/app/oracle/product/10.2.0.5/dbhome_1/dbs/
oracle@10.0.0.21's password:
orapwtest3 100% 1536 1.5KB/s 00:00
三.编辑initSID.ora, 注意增加的部分需要符合角色互换的要求
编辑两端linstener.ora,两端tnsname.ora.两端文件最好完全一致,不要使用相对含义命名（如primary standby），会比较规范！
做好口令文件，保证两端sys口令一致
edit init parameter file
create pfile='/u01/ininttest3.ora' from spfile;
==add to test3_A
#注意service_name在不手动指定的情况下，会自动继承db_unique_name,并注册在监听中的service。因此db_unique_name可能会直接影响tnsname.ora的连接字符串；而instance_name是继承的ORACLE_SID，也等同于参数文件的名字。不受db_unique_name影响。并会注册在监听的instance中
service_names=test3,test3_A #这样两个service_names在自动注册时均会以service出现在lsnrctl status中
log_archive_config='dg_config=(test3_A,test3_B)'
log_archive_dest_1='location=/u01/archivelogs/test3 valid_for=(all_logfiles,all_roles) db_unique_name=test3_A'
log_archive_dest_2='service=test3_B lgwr sync AFFIRM delay=0 reopen=300 net_timeout=15 valid_for=(online_logfiles,primary_roles) db_unique_name=test3_B'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_client=test3_A
fal_server=test3_B
undo_management=auto
STANDBY_FILE_MANAGEMENT=auto
log_file_name_convert='/u02/test3/','/u02/test3/','/u03/test3/','/u03/test3/'
db_file_name_convert='/u01/oradata/test3/','/u01/oradata/test3/'
主要是提示在主备同机情况下避免影响到P机的数据文件与在线日志的
==add to test3_B
service_names=test3,test3_B
log_archive_config='dg_config=(test3_A,test3_B)'
log_archive_dest_1='location=/u01/archivelogs/test3 valid_for=(all_logfiles,all_roles) db_unique_name=test3_B'
log_archive_dest_2='service=test3_A lgwr sync AFFIRM delay=0 reopen=300 net_timeout=15 valid_for=(online_logfiles,primary_roles) db_unique_name=test3_A' #注意这里写的本地tnsname.ora文件中的服务名
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_client=test3_B
fal_server=test3_A
undo_management=auto
STANDBY_FILE_MANAGEMENT=auto
log_file_name_convert='/u02/test3/','/u02/test3/','/u03/test3/','/u03/test3/'
db_file_name_convert='/u01/oradata/test3/','/u01/oradata/test3/'
==edit tnsname.ora then transfer it to HOST_B
[oracle@redhat ~]$ scp /u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/tnsnames.ora oracle@10.0.0.21:/u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/
oracle@10.0.0.21's password:
tnsnames.ora 100% 761 0.7KB/s 00:00
tnsname.ora示例
[oracle@redhat ~]$ cat /u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST3_A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test3_A)
)
)
TEST3_B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test3_B)
)
)
监听要静态注册本机实例,静态注册的sid_name要与本地数据库sid一致。而手动指定的 GLOBAL_DBNAME 将会体现在监听的service。
如果GLOBAL_DBNAME与db_unique_name不一致，则会在监听出体现为两条service，但是其下的instance均是sid
listener.ora示例
[oracle@redhat1 ~]$ cat /u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0.5/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.5/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = test3_B)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.5/dbhome_1)
(SID_NAME = test3)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.localdomain)(PORT = 1521))
)
[oracle@redhat1 admin]$ lsnrctl status
...................................
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test3" has 1 instance(s).
Instance "test3", status UNKNOWN, has 1 handler(s) for this service...
Service "test3XDB" has 1 instance(s).
Instance "test3", status READY, has 1 handler(s) for this service...
Service "test3_A" has 1 instance(s).
Instance "test3", status READY, has 1 handler(s) for this service...
The command completed successfully
注：
1. 一定要知道监听status中的service与tnsname.ora中SERVICE_NAME是相对应的。（从lsnrctl status来看，监听器使用service对外服务，使用instance对内连接实例，承前启后的作用）
2. 必须明确监听状态中的instance只可能且必须与实例的SID字符串相同。
3. 静态注册时，如果在listener.ora定义了GLOBAL_DBNAME，实例会优先将GLOBAL_DBNAME字符串注册到监听器的service，否则注册初始参数instance_name字符串到监听器的service。
4. 动态注册时，实例会将service_names字符串注册到监听的service，由于service_names参数在不手动指定的情况下，会自动继承db_unique_name，因此字符串与db_unique_name相同。 如果在非DG环境中db_unique_name没有指定，则service_names会自动继承instance_name,来自于环境变量ORACLE_SID.
5. 动态注册时， 特别注意listener.ora中的host必须使用主机名而不能使用本机IP，否则会导致实例不能自动注册到监听器。
6. 不论是动态注册还是静态注册，lisnrctl status中的service字符串都必须来自于service_names。 可以为service_names同时指定多个值，来满足不同的tnsname.ora的连接需求。
Oracle的data guard有三种保护模式，分别为最大保护模式、最大可用模式、最大性能模式。
Minimum Requirements for Maximum Protection Mode
Redo Archival Process LGWR
Network Transmission Mode SYNC
Disk Write Option AFFIRM
Standby Redo Logs? Yes
Standby Database Type Physical Only
Minimum Requirements for Maximum Availability Mode
Redo Archival Process LGWR
Network Transmission Mode SYNC
Disk Write Option AFFIRM
Standby Redo Logs? Required for physical standby databases only. Standby redo logs are not supported for logical standby databases.
Standby Database Type Physical or Logical
Minimum Requirements for Maximum Performance Mode
Redo Archival Process LGWR or ARCH
Network Transmission Mode ASYNC when using LGWR process. Not applicable when using the ARCH process.
Disk Write Option NOAFFIRM
Standby Redo Logs? Required for physical standby databases using the LGWR process.
Standby Database Type Physical or Logical
四.正式创建DG
[oracle@redhat ~]$ rman target / auxiliary sys/oracle@test3_B
duplicate target database for standby nofilenamecheck;
（这里可以不用做 alter database mount standby database;）
alter database recover managed standby database disconnect from session;
或者启动实时应用，强烈建议
alter database recover managed standby database using current logfile disconnect from session;
（注意启动或者停止实时日志应用时，警告日志不应该出现任何ora-）
停止redo replay
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
注意，如果有活动会话，会显示出 "session active"
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
转换保护模式：
alter database set standby database to maximize availability,protection,performance;
五.Switchover 测试：
P: alter database commit to switchover to physical standby; (只能在open的状态下做)
shutdown immediate (此时P shutdown完毕，S 就会 to primary)
startup mount (此时P mount完毕，P 也会 to primary,但是我们接下来只能去继续做S的调整工作，让其最终变成primary，先按流程来)
alter database recover managed standby database using current logfile disconnect from session; （如果不做这一步，状态会是recovery needed,而不会是session_active）
注意在这里session_active并不意味着不对，而是因为确实有从新P过来的两个public用户，进行日志同步。当新P断开时，可以看到新S是to primary
注：开始apply redo时，会自动做 Clearing online redo
S: alter database commit to switchover to physical primary;
alter database open
注：standby 第一次switchover to physical primary时，会自动Re-creating tempfile
当对方已经完成shutdown 及mount后，即“ARC1: Standby redo logfile selected for thread 1 sequence 114 for destination LOG_ARCHIVE_DEST_2” 成功
此刻，新的primary才会变成to_standby，否则是not allowed
六.failover 测试：
此时primary已经不是DG的一部分，以下查询在s执行之前，如果可能，也在P查看一下
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
该语句取得当前数据库各线程已归档文件最大序号，如果primary 与standby 最大序号不相同，必须将多出的序号对应的归档文件复制到待转换的standby 服务器（不做此步不会影响failover）。
不过既然是failover，有可能primary 数据库此时已经无法打开，甚至无法访问，丢一点数据是完全有可能的
S: alter database recover managed standby database finish force; （force表示立刻停止RFS进程）
alter database commit to switchover to primary;
alter database open;
最后尝试看看能否恢复原primary 数据库，将其改造为新的standby 服务器。具体操作方式可以分为二类：1.重建2.备份恢复
七.会用到的一些视图，临时记录一下，具体还是建议看文档吧
select database_role,switchover_status,protection_mode,protection_level,open_mode from v$database;
注：PROTECTION_LEVEL 为 RESYNCHRONIZATION 意味着日志不能传输到standby，可以通过v$archive_dest查看错误。或者表明在log_archive_dest_n 中没有使用与当前保护模式匹配的参数。如果是standby finish后，通常看到的错误是ORA-16143: RFS connections not allowed during or after terminal recovery
或 ORA-16009: remote archive log destination must be a STANDBY database
set linesize 150
col dest_name for a30
select dest_id,dest_name,status,error,valid_now,VALID_TYPE,VALID_ROLE from v$archive_dest;
select process,PID,status,client_process,sequence# from v$managed_standby;
--MRP0 状态为 APPLYING_LOG 表示正常状态，没gap
--RFS其client process为 LGWR 的sequence# 为primary DB 当前日志序列号，表示正常
select * from v$archived_log where standby_dest='YES' and applied='NO'
select * from V$DATAGUARD_STATUS order by timestamp desc
select name,value from v$dataguard_stats;
select * from v$archive_gap
八.两个错误，知道原因就好
ORA-16143: RFS connections not allowed during or after terminal recovery
[oracle@redhat1 ~]$ oerr ora 16143
16143, 00000, "RFS connections not allowed during or after terminal recovery"
// *Cause: An attempt was made, by an RFS process, to access a standby online
// log file during or after a terminal recovery.
// *Action: The primary must not attempt to archive to the standby
// after a terminal recovery.
ORA-16137: No terminal recovery is required
[oracle@redhat1 ~]$ oerr ora 16137
16137, 00000, "No terminal recovery is required"
// *Cause: All archived logs have been applied, and there are no current
// logs to be applied. Terminal recovery is not required.
// *Action: No action is necessary. The standby database may be activated as
// a new primary or may continue as a standby.
[oracle@redhat1 ~]$ oerr ora 00261
00261, 00000, "log %s of thread %s is being archived or modified"
// *Cause: The log is either being archived by another process or an
// administrative command is modifying the log. Operations that
// modify the log include clearing, adding a member, dropping a
// member, renaming a member, and dropping the log.
// *Action: Wait for the current operation to complete and try again.
九 .RMAN中会用到的归档日志删除策略
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
1757
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
