SQL> alter database force logging;
2、在数据库上启用归档模式
SQL> ALTER SYSTEM SETLOG_ARCHIVE_DEST_1='LOCATION=/opt/app/oracle/oradata/or2/archiveMANDATORY' SCOPE=BOTH;
3、获取主数据库数据文件信息
SQL> select name from v$datafile;
4、拷贝主数据库数据文件
1）停止主数据库
SQL> shutdown immediate;
2）拷贝数据文件到临时位置
3）重启主数据库
SQL> startup;
5、在主数据库为备用数据库创建控制文件
SQL> alter database create standby controlfile as'/opt/app/oraclestand/standbycontrol.ctl';
6、在主数据库准备初始化参数文件
SQL> createpfile='/opt/app/oraclestand/initorcl.ora' from spfile;
7、拷贝文件到备用服务器（包含：数据文件拷贝、备用控制文件、初始化参数文件和口令文件）
8、在备用服务器上修改初始化参数文件
修改如下行：
control_files='/opt/app/oracle/oradata/or2/standbycontrol.ctl','/opt/app/oracle/oradata/or2/standbycontro2.ctl'
standby_archive_dest='/opt/app/oracle/admin/or2/archive'
standby_file_management=AUTO
remote_archive_enable=TRUE
9、在备用服务器上创建Oracle服务（for windows）
c:\> oradim -NEW -SID orcl -STARTMODE manual
10、在主、备服务器上创建监听，指定监控数据库
使用Oracle Net Manager或直接修改listener.ora文件
11、在备用服务器上允许连接死锁检测
修改SQLNET.ORA文件，增加如下行：
SQLNET.EXPIRE_TIME=2
12、在主、备服务器上创建Oracle Net服务名
使用Oracle Net Manager或直接修改tnsname.ora文件
13、在备用服务器上创建SPFILE
SQL> create spfile frompfile='/opt/app/oracle/product/9.2.0/dbs/initorcl.ora';
14、启动备用数据库
SQL> startup nomount;
SQL> alter database mount standby database;
15、在备用服务器上启动日志传送服务
SQL> alter database recover managed standby databasedisconnect from session;
16、在主数据库上启动归档到备用数据库
1）设置归档初始化参数
SQL> alter system setLOG_ARCHIVE_DEST_2='SERVICE=standby ' scope=both;
SQL> alter system setLOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
2）启动远程归档
SQL> alter system archive log current;
17、检验物理备用数据库
1）在备用服务器上查看已有归档日志
SQL> select sequence#,first_time,next_time fromv$archived_log order by sequence#;
2）在主服务器上归档当前日志
SQL> alter system archive log current;
3）在备用数据库上验证新归档日志已收到
SQL> select sequence#,first_time,next_time fromv$archived_log order by sequence#;
4）在备用数据库上验证新归档日志已应用
SQL> select sequence#,applied from v$archived_logorder by sequence#;
select sequence#,first_time,next_time,applied fromv$archived_log order by sequence#;
======================================================================
18、创建Standby Redo Logs
1）检验Standby redo logs的方法
SQL> select * from v$standby_log;
或
SQL> select * from v$logfile wheretype='STANDBY';
2）在主、备数据库分别创建比联机日志多至少一组的Standby redo log(在主机作standby redo log是为了作swichover时候用);
standby redo log的大小应该跟onlineredo log的大小相等（select GROUP#,BYTES,MEMBERS,STATUS from v$log;）
注意：若备用库已进入自动恢复模式，需要先停止才能正确执行以上语句
SQL> alter database recovermanaged standby database cancel;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog01.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog04.tdo') size 10M;
查看加入的standby数据库是否开始使用的方法(在maximize PERFORMANCE下不用该日志)：
在primary库上调用log switch（ALTER SYSTEM SWITCHLOGFILE），然后在备用库上查看V$STANDBY_LOG视图。
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROMV$STANDBY_LOG;
3）如果控制文件中的MAXLOGFILES参数值太小，无法增加足够的日志组的话，需要重建数据库或控制文件。
重建控制文件的方法如下:
SQL> alterdatabase backup controlfile to trace;
将在admin/orcl/udump目录下生成新的trace文件，打开该文件可获得在主/备数据库上分别重建控制文件的脚本。按照其指示执行即可。
19、在主数据库修改数据保护模式
1）设置初始化参数
SQL> alter system setLOG_ARCHIVE_DEST_2='SERVICE=standby.tjhosue.com LGWR SYNC AFFIRM'scope=both;
2）数据库重新打开为排他模式装载
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount exclusive;
3）修改保护模式（任选一种模式）
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE{PROTECTION | AVAILABILITY | PERFORMANCE}
4）打开数据库
SQL> alter database open;
20、确认数据库的保护模式
SQL> selectDATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
21、监视重作日志归档情况
1）确定当前重作日志顺序号
SQL> select thread#,sequence#,archived,status fromv$log;
2）确定最近的归档重作日志
SQL> select max(sequence#) fromv$archived_log;
3）确定每个目的地的最近归档日志
SQL> selectdestination,status,archived_thread#,archived_seq# fromv$archive_dest_status
where status <> 'DEFERRED'and status<>'INACTIVE';
4）查看某特殊位置日志是否收到
SQL> select dest_id from v$archive_dest;
SQL> select local.thread#,local.sequence# from
(select thread#,sequence# from v$archived_log wheredest_id=1)
local where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 andthread#=local.thread#);
5）跟踪备用站点的归档日志进程
在主、备数据库设置初始化参数log_archive_trace
22、监视恢复进程
检查是否已正确设置日志应用服务
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY;
23、日志应用服务
1）启动/停止日志应用服务
SQL> alter database start logical standbyapply;
SQL> alter database stop logical standbyapply;
2）确认重做日志已应用
·V$LOGSTDBY
SQL> column status format A50
SQL> column type format A12
SQL> select type,high_scn,status fromv$logstdby;
·DBA_LOGSTDBY_PROGRESS
SQL> select applied_scn,newest_scn fromdba_logstdby_progress;
3）修补归档间隙
在备用服务器上
SQL> select * from v$archive_gap;
在主服务器
SQL> select name from v$archived_log where thread#=1dest_id=1 and sequence# between 7 and 10;
拷贝归档日志到备用服务器，然后执行
SQL> alter database register logfile'/physical_standby1/thread1_dest/arcr_1_7.arc';
然后重启管理恢复操作
24、监视物理备用数据库日志应用服务
SQL> selectprocess,status,thread#,sequence#,block#,blocks fromv$managed_standby;
SQL> selectarchived_thread#,archived_seq#,applied_thread#,applied_seq# fromv$archive_dest_status;
SQL> selectregistrar,creator,thread#,sequence#,first_change#,next_change# fromv$archived_log;
SQL> selectthread#,sequence#,first_change#,next_change# fromv$log_history;
SQL> select message from v$dataguard_status;
SQL> selectfile_name,sequence#,first_change#,next_change#,timestamp,dict_begin,dict_end,thread#from dba_logstdby_log order by sequence#;
SQL> select applied_scn,newest_scn fromdba_logstdby_progress;
SQL> alter session set NLS_DATA_FORMAT='DD-MON-YYHH24:MI:SS';
SQL> select l.sequence#,l.first_time,(case whenl.next_change# < p.read_scn then 'yes'
when l.first_change# < p.applied_scn then'current'
else 'no' end) applied
from dba_logstdby_log l, dba_logstdby_progress p
order by sequence#;
SQL
> column status format a50
SQL> column type format a12
SQL> select type,high_scn,status fromv$logstdby;
SQL> column name format a35
SQL> column value format a35
SQL> select name,value from v$logstdby_status wherename like 'coordinatior%' or name like 'transactions%';
25、修改主服务器启动参数
SQL> alter system set remote_archive_enable=sendscope=spfile;
SQL> alter system set fal_server=standby_orcl;
SQL> alter system set fal_client=orcl;
SQL> alter system setstandby_archive_dest='d:\oracle\oradata\orcl\archive';
SQL> alter system setstandby_file_management=auto;
26、修改备用服务器启动参数
SQL> alter system set fal_server=primary_orcl;
SQL> alter system set fal_client=orcl;
SQL> alter system set remote_archive_enable=receivescope=spfile;
SQL> alter system setlog_archive_dest_2='service=primary_orcl LGWR SYNC AFFIRM'scope=spfile;
27、切换管理角色
将原standby数据库改为MAXIMIZE PERFORMANCE
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
1 Verify that it is possible to perform a switchoveroperation.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
2 Initiate the switchover operation _disibledevent=>
7 Start managed recovery operations and log applyservices.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASEDISCONNECT FROM SESSION;
8 Begin sending redo data to the standby databases.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> selectdatabase_role,protection_mode,protection_level from v$database;
28、失败接管步骤
Step 1 Identify and resolve any archived redo log gaps.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92
In this example the gap comprises archive logs 90, 91, and 92for thread 1. If
possible, copy all of the identified missing archived redo logs tothe target standby
database from the primary database or from another standby databaseand register
them. This must be done for each thread.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE’filespec1’;
Step 2 Copy any other missing archived redo logs.
SQL> SELECT UNIQUE THREAD# AS THREAD,MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST fromV$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 147
Copy any archived redo logs from the other available databases thatcontain
sequence numbers higher than the highest sequence number availableon the target
standby database to the target standby database and register them.This must be
done for each for each thread.
（SQL> select name from v$archived_log wherethread#=1 and sequence#=147;）
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE’filespec1’;
Step 3 Repeat steps 1 and 2.
Step 4 Initiate the failover operation _disibledevent=>
一些检测脚本
在primary上检测归档是否成功。
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROMV$ARCHIVE_DEST_STATUS WHERE STATUS <>'DEFERRED' AND STATUS <>'INACTIVE';
从recover managed模式切换出来
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
监控managed recovery进程
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROMV$MANAGED_STANDBY;
查看datagurad状态
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
Monitoring the Process Activities
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROMV$MANAGED_STANDBY;
Determining the Progress of Managed Recovery Operations
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
Determining the Location and Creator of Archived Redo Logs
SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROMV$ARCHIVED_LOG;
Viewing the Archive Log History
SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROMV$LOG_HISTORY;
Determining Which Logs Were Applied to the StandbyDatabase
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS"LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
or
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROMV$ARCHIVED_LOG;
Determining Which Logs Were Not Received by the StandbySite
SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROMV$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
======================================================================
注意：
如果需要作switch的话，
两边设置要一样，都有如下几项：
主：
log_archive_dest_1 string LOCATION=/opt/app/oracle/oradata/or2/archive MANDATORY
log_archive_dest_2 string SERVICE=standby.tjhosue.com LGWR SYNC AFFIRM
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog01.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog04.tdo') size 10M;
*.standby_archive_dest='/opt/app/oracle/admin/or2/archive'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
从：
log_archive_dest_1 string LOCATION=/opt/app/oracle/oradata/or2/archive MANDATORY
log_archive_dest_2 string SERVICE=or2.TJHOUSE.COM LGWR SYNC AFFIRM
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog01.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size10M;
SQL> alter database add standby logfile('/opt/app/oracle/oradata/or2/standby/stdlog04.tdo') size 10M;
*.standby_archive_dest='/opt/app/oracle/admin/or2/archive'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
最新评论