1、在主数据库(primary database)上启用强制生成日志
SQL> alter database force logging;
2、在数据库上启用归档模式
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_DEST_1='LOCATION=/opt/app/oracle/oradata/or2/archive
MANDATORY' 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/l';
6、在主数据库准备初始化参数文件
SQL> create
pfile='/opt/app/a' from spfile;
7、拷贝文件到备用服务器(包含：数据文件拷贝、备用控制文件、初始化参数文件和口令文件)
8、在备用服务器上修改初始化参数文件
修改如下行：
control_files='/opt/app/oracle/oradata/l','/opt/app/oracle/oradata/l'
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或直接修改a文件
11、在备用服务器上允许连接死锁检测
修改SQLNET.ORA文件，增加如下行：
SQLNET.EXPIRE_TIME=2
12、在主、备服务器上创建Oracle Net服务名
使用Oracle Net Manager或直接修改a文件
13、在备用服务器上创建SPFILE
SQL> create spfile from
pfile='/opt/app/oracle/product/9.2.0/a';
14、启动备用数据库
SQL> startup nomount;
SQL> alter database mount standby database;
15、在备用服务器上启动日志传送服务
SQL> alter database recover managed standby database
disconnect from session;
16、在主数据库上启动归档到备用数据库
1)设置归档初始化参数
SQL> alter system set
LOG_ARCHIVE_DEST_2='SERVICE=standby ' scope=both;
SQL> alter system set
LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
2)启动远程归档
SQL> alter system archive log current;
17、检验物理备用数据库
1)在备用服务器上查看已有归档日志
SQL> select sequence#,first_time,next_time from
v$archived_log order by sequence#;
2)在主服务器上归档当前日志
SQL> alter system archive log current;
3)在备用数据库上验证新归档日志已收到
SQL> select sequence#,first_time,next_time from
v$archived_log order by sequence#;
4)在备用数据库上验证新归档日志已应用
SQL> select sequence#,applied from v$archived_log
order by sequence#;
select sequence#,first_time,next_time,applied from
v$archived_log order by sequence#;
======================================================================
18、创建Standby Redo Logs
1)检验Standby redo logs的方法
SQL> select * from v$standby_log;
或
SQL> select * from v$logfile where
type='STANDBY';
2)在主、备数据库分别创建比联机日志多至少一组的Standby redo log(在主机作standby redo log
是为了作swichover时候用);
standby redo log的大小应该跟online
redo log的大小相等(select GROUP#,BYTES,MEMBERS,STATUS from v$log;)
注意：若备用库已进入自动恢复模式，需要先停止才能正确执行以上语句
SQL> alter database recover
managed standby database cancel;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog01.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog04.tdo') size 10M;
查看加入的standby数据库是否开始使用的方法(在maximize PERFORMANCE下不用该日志)：
在primary库上调用log switch(ALTER SYSTEM SWITCH
LOGFILE)，然后在备用库上查看V$STANDBY_LOG视图。
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM
V$STANDBY_LOG;
3)如果控制文件中的MAXLOGFILES参数值太小，无法增加足够的日志组的话，需要重建数据库或控制文件。
重建控制文件的方法如下:
SQL> alter
database backup controlfile to trace;
将在admin/orcl/udump目录下生成新的trace文件，打开该文件可获得在主/备数据库上分别重建控制文件的脚本。按照其指示执行即可。
19、在主数据库修改数据保护模式
1)设置初始化参数
SQL> alter system set
LOG_ARCHIVE_DEST_2='SERVICE=standby.tjhosue 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> select
DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
21、监视重作日志归档情况
1)确定当前重作日志顺序号
SQL> select thread#,sequence#,archived,status from
v$log;
2)确定最近的归档重作日志
SQL> select max(sequence#) from
v$archived_log;
3)确定每个目的地的最近归档日志
SQL> select
destination,status,archived_thread#,archived_seq# from
v$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 where
dest_id=1)
local where
local.sequence# not in
(select sequence# from v$archived_log where dest_id=2 and
thread#=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 standby
apply;
SQL> alter database stop logical standby
apply;
2)确认重做日志已应用
·V$LOGSTDBY
SQL> column status format A50
SQL> column type format A12
SQL> select type,high_scn,status from
v$logstdby;
·DBA_LOGSTDBY_PROGRESS
SQL> select applied_scn,newest_scn from
dba_logstdby_progress;
3)修补归档间隙
在备用服务器上
SQL> select * from v$archive_gap;
在主服务器
SQL> select name from v$archived_log where thread#=1
dest_id=1 and sequence# between 7 and 10;
拷贝归档日志到备用服务器，然后执行
SQL> alter database register logfile
'/physical_standby1/thread1_dest/arcr_1_7.arc';
然后重启管理恢复操作
24、监视物理备用数据库日志应用服务
SQL> select
process,status,thread#,sequence#,block#,blocks from
v$managed_standby;
SQL> select
archived_thread#,archived_seq#,applied_thread#,applied_seq# from
v$archive_dest_status;
SQL> select
registrar,creator,thread#,sequence#,first_change#,next_change# from
v$archived_log;
SQL> select
thread#,sequence#,first_change#,next_change# from
v$log_history;
SQL> select message from v$dataguard_status;
SQL> select
file_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 from
dba_logstdby_progress;
SQL> alter session set NLS_DATA_FORMAT='DD-MON-YY
HH24:MI:SS';
SQL> select l.sequence#,l.first_time,(case when
<_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 from
v$logstdby;
SQL> column name format a35
SQL> column value format a35
SQL> select name,value from v$logstdby_status where
name like 'coordinatior%' or name like 'transactions%';
25、修改主服务器启动参数
SQL> alter system set remote_archive_enable=send
scope=spfile;
SQL> alter system set fal_server=standby_orcl;
SQL> alter system set fal_client=orcl;
SQL> alter system set
standby_archive_dest='d:oracleoradataorclarchive';
SQL> alter system set
standby_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=receive
scope=spfile;
SQL> alter system set
log_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 switchover
operation.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
2 Initiate the switchover operation on the primary
database.
SQL> alter database commit to switchover to physical
standby with session shutdown ;
3 Shut down and restart the former primary instance.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
4 Verify the switchover status in the V$DATABASE view.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
SWITCHOVER PENDING
1 row selected
5 Switch the physical standby database role to the primary
role.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE CANCEL;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY
DATBASE FINISH;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO
PRIMARY;
6 Shut down and restart the new primary database.
Shut down the target standby instance and restart it using the
appropriate
initialization parameters for the primary role:
SQL> SHUTDOWN;
SQL> STARTUP;
ORA-16072: a minimum of one standby database destination is
required
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Or log_archive_dest_2='SERVICE=primary LGWR
MANDATORY REOPEN=60 '
7 Start managed recovery operations and log apply
services.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;
8 Begin sending redo data to the standby databases.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> select
database_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 92
for thread 1. If
possible, copy all of the identified missing archived redo logs to
the target standby
database from the primary database or from another standby database
and 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 from
V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 147
Copy any archived redo logs from the other available databases that
contain
sequence numbers higher than the highest sequence number available
on 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 where
thread#=1 and sequence#=147;)
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE
’filespec1’;
Step 3 Repeat steps 1 and 2.
Step 4 Initiate the failover operation on the target physical
standby database.
If your target standby database was configured with standby redo
logs and you
have not manually registered any partial archived redo logs, issue
the following
statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
FINISH;
Otherwise, you must issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
FINISH SKIP STANDBY LOGFILE;
Step 5 Convert the physical standby database to the primary
role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO
PRIMARY;
(
注意：
如果执行了 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
FINISH;再执行本sql报
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO
PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
的时候，可以 再把数据库置于managed recovery 的状态
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM
SESSION；
然后再执行 SQL> ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE FINISH SKIP STANDBY LOGFILE;
成功以后再执行 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO
PRIMARY; 就可以了。
)
Step 6 Prepare to receive redo logs from the new primary
database.
Step 7 Shut down and restart the new primary database.
Step 8 Optionally, back up the new primary database.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
日常操作
@@增加数据文件或者表空间
STANDBY_FILE_MANAGEMENT Is Set to AUTO，备用机自动建立
@@删除表空间或者数据文件
1 P
SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
2 S
SQL> SELECT PROCESS, STATUS FROM
V$MANAGED_STANDBY;
保证有MRP or MRP0 process,
3 S
删除数据文件
4 P
删除数据文件
@@ rename 数据文件
1 P
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
% mv tbs_4.dbf tbs_x.dbf
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE
'tbs_4.dbf' TO 'tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;
2 S
Connect to the standby database and make sure that all the logs are
applied;
then stop managed recovery operations:
SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED FROM
V$ARCHIVED_LOG;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
CANCEL;
SQL> SHUTDOWN;
% mv tbs_4.dbf tbs_x.dbf
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RENAME FILE 'tbs_4.dbf' TO
'tbs_x.dbf';
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;
@@增加或者删除在线重做日志
在parimary上，删除或者重新建立redo组或者文件不会影响standby库，但是如果进行切换以后，就会产生影响。
1. If managed recovery is on, you must cancel it before you can
change the logs.
2. If the STANDBY_FILE_MANAGEMENT initialization parameter is set
to AUTO,change the value to MANUAL.
3. Add or drop an online redo log:
u0001 To add an online redo log, use a SQL
statement such as this:
SQL> ALTER DATABASE ADD STANDBY
LOGFILE 'prmy3.log' SIZE 100K;
u0001 To drop an online redo log, use a SQL
statement such as this:
SQL> ALTER DATABASE DROP STANDBY
LOGFILE 'prmy3.log’;
4. Repeat the statement you used in step 3 on each standby
database.
5. Restore the STANDBY_FILE_MANAGEMENT initialization parameter and
the
managed recovery options to their original states.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
======================================================================
一些检测脚本
在primary上检测归档是否成功。
SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM
V$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 FROM
V$MANAGED_STANDBY;
查看datagurad状态
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
Monitoring the Process Activities
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM
V$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 FROM
V$ARCHIVED_LOG;
Viewing the Archive Log History
SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM
V$LOG_HISTORY;
Determining Which Logs Were Applied to the Standby
Database
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS
"LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
or
SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM
V$ARCHIVED_LOG;
Determining Which Logs Were Not Received by the Standby
Site
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# FROM
V$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 LGWR SYNC AFFIRM
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog01.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size
10M;
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') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog02.tdo') size
10M;
SQL> alter database add standby logfile
('/opt/app/oracle/oradata/or2/standby/stdlog03.tdo') size
10M;
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
本文发布于:2024-02-01 20:22:20，感谢您对本站的认可！
本文链接：https://www.4u4v.net/it/170679014139210.html
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
| 留言与评论（共有 0 条评论） |
