WINDOWS下的DG
1、按照以下条件创建相应的文件夹
主库online redo目录为 D:\oracle\product\10.2.0\oradata\cloudsea
备库online redo目录为 D:\oracle\product\10.2.0\oradata\cloudsea_std
主库oracle_base='D:\oracle\product\10.2.0'
备库oracle_base='D:\oracle\product\10.2.0'
主库和备库的log_archive_dest_1全都设置一样，log_archive_dest_1='LOCATION=E:\archivelog'
SQL>alter system set log_archive_dest_1='location=E:\archivelog' scope=spfile;
主库和备库的rman备份路径也都设置一样，control,datafile,archivelog分别为
E:\backup\control\controlfile\
E:\backup\data\
E:\backup\archivelog\
在备库中创建： D:\oracle\product\10.2.0\oradata
D:\oracle\product\10.2.0\oradata\cloudsea_std
D:\oracle\product\10.2.0\admin
D:\oracle\product\10.2.0\admin\cloudsea
D:\oracle\product\10.2.0\admin\cloudsea\adump
D:\oracle\product\10.2.0\admin\cloudsea\bdump
D:\oracle\product\10.2.0\admin\cloudsea\cdump
D:\oracle\product\10.2.0\admin\cloudsea\dpdump
D:\oracle\product\10.2.0\admin\cloudsea\pfile
D:\oracle\product\10.2.0\admin\cloudsea\udump
D:\oracle\product\10.2.0\flash_recovery_area\CLOUDSEA_STD
E:\cloudsea_data
2、修改主备库listener和tnsname
主库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=cloudsea)
(SID_NAME=cloudsea)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
)
)
主库tnsnames.ora
cloudsea_std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.72)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cloudsea_std)
)
)
cloudsea_pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cloudsea)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
主库中创建pfile
SQL>shutdown immediate;
SQL>startup;
SQL> create pfile='D:\oracle\product\10.2.0\admin\cloudsea\pfile\init.ora.09' from spfile;
修改主库pfile
增加以下内容
db_file_name_convert=('D:\oracle\product\10.2.0\oradata\cloudsea_std','D:\oracle\product\10.2.0\oradata\cloudsea')
log_file_name_convert=('D:\oracle\product\10.2.0\oradata\cloudsea_std','D:\oracle\product\10.2.0\oradata\cloudsea')
db_unique_name=cloudsea_pri
log_archive_config='DG_CONFIG=(cloudsea_pri,cloudsea_std)'
log_archive_dest_2='SERVICE=cloudsea_std arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cloudsea_std'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
FAL_SERVER=cloudsea_pri
FAL_CLIENT=cloudsea_std
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=AUTO
修改以下内容
*.log_archive_dest_1='location=E:\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cloudsea_pri'
先用netmanager创建一个监听和一个服务 ，备库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = cloudsea_std)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = cloudsea)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.72)(PORT = 1521))
)
)
备库tnsnames.ora
CLOUDSEA_PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.71)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cloudsea_pri)
)
)
CLOUDSEA_STD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.72)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cloudsea_std)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
修改备库pfile
增加
db_file_name_convert=('D:\oracle\product\10.2.0\oradata\cloudsea','D:\oracle\product\10.2.0\oradata\cloudsea_std')
log_file_name_convert=('D:\oracle\product\10.2.0\oradata\cloudsea','D:\oracle\product\10.2.0\oradata\cloudsea_std')
control_files=("D:\oracle\product\10.2.0\oradata\cloudsea_std\control01.ctl", "D:\oracle\product\10.2.0\oradata\cloudsea_std\control02.ctl", "D:\oracle\product\10.2.0\oradata\cloudsea_std\control03.ctl")
db_unique_name=cloudsea_std
log_archive_config='DG_CONFIG=(cloudsea_pri,cloudsea_std)'
log_archive_dest_2='SERVICE=cloudsea_pri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cloudsea_pri'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
FAL_SERVER=cloudsea_std
FAL_CLIENT=cloudsea_pri
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=AUTO
修改
*.log_archive_dest_1='location=E:\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
修改主库属性：
SQL>alter database force logging; --查看状态 select FORCE_LOGGING from v$database;
SQL>archive log list;
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>create spfile from pfile='D:\oracle\product\10.2.0\admin\cloudsea\pfile\init.ora.09';
SQL>shutdown immediate;
SQL>startup;
备份主库
run {
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to 'E:\BACKUP\control\controlfile_%F';
configure backup optimization on;
allocate channel dev1 type disk;
BACKUP DATABASE FORMAT 'E:\BACKUP\data\datafile_%U' PLUS ARCHIVELOG format 'E:\backup\archivelog\archivelog_%U';
release channel dev1;
}
创建主库standby control file
SQL>alter database create standby controlfile as 'E:\backup\cloudsea_std.ctl';
备库上
在windows下用oradim创建新实例、密码文件、重启监听
cmd>oradim -new -sid cloudsea
cmd>set oracle_sid=cloudsea
cmd>orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDcloudsea.ora password=root entries=30
cmd>lsnrctl reload
SQL>create spfile from pfile='D:\oracle\product\10.2.0\admin\cloudsea\pfile\init.ora.09';
SQL>startup nomount;
用RMAN duplicate复制数据库 将备份的文件拷贝到备库
C:\Documents and Settings\Administrator>rman
RMAN> connect auxiliary /
RMAN> connect target sys/root@cloudsea_pri;
RMAN> duplicate target database for standby nofilenamecheck;
修改备库处于应用归档状态
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
--应用归档
SQL>alter database recover managed standby database disconnect from session;
--查看备库角色
SQL> select DATABASE_ROLE from v$database;
--查看归档是否被应用，查看时注意，会略有延迟
SQL> select sequence#, applied from v$archived_log;
--在主库切换日志，同时观察归档是否被应用，如果是yes，则表明成功同步
SQL> alter system switch logfile;
切换测试
--查看是否可以切换
在主库端
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
如果是to standby表示可以正常切换，不过这种状态应该是主库的状态未open的情况，如果主库已经打开，一般的状态是SESSIONS ACTIVE
如果是to standby
直接执行 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
在备库端
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
如果是primary表示可以正常切换，不过还会遇到NOT ALLOWED和PENDING或者LATENT，实际操作下来如果备库的switchover_status为not allowed或者to primary都可以正常切换
如果是to_primary
执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
具体切换的顺序：先从主库到备用，再从备库到主库
主库
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
备库
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL>shutdown immediate;
SQL>startup;
如果实际操作没有成功或者跟上述情况不同
SQL>select message from v$dataguard_status;
原因可能有几种
确保主库和备库登录对方sys用户成功
SQL>connect sys/root@cloudsea_pri as sysdba
日后维护工作
1.正确的开关机顺序是：
启动的时候，先备库的listener,再启动备库,再启动主库的listener,再启动主库。
关闭的时候，先关闭主库，再关闭备库(先alter database recover managed standby database cancel,（提示控制文件不是备用控制文件）再shutdown immediate(提示ORA=01109)。
2.备库启动方法:startup nomount==>alter database mount standby database==>alter database recover managed standby database disconnect from session; --应用归档
3.如果两边的备库相对主库缺少archivelog,可以通过select * from v$archive_gap显示差距的arch log然后
alter database register physical logfile 'd:oracleARC00048_0660047105.001'; --物理注册
alter database recover managed standby database disconnect from session; --应用归档
4.查看select sequence#, applied from v$archived_log;备库归档应用信息如果都没有应用请检查LOG_ARCHIVE_DEST_2参数是否正确
5.如何打开standby:
alter database recover managed standby database cancel;--暂停redo应用以便open 注意，此时只是暂时redo应用，并不是停止Standby数据库，standby仍会保持接收只不过不会再应用接收到的归档，直到你再次启动redo应用为止。
alter database open read only; --打开做报表,此时数据不会实时更新,standby仍会保持接收只不过不会再应用接收到的归档，直到你再次启动redo应用为止(此时open模式)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;--启动应用(此时恢复成mount模式)
如果报错ORA-01153: an incompatible media recovery is active
可以执行 alter database recover managed standby database finish;(failover)
或者cancel掉, alter database recover managed standby database cancel;
6.设置新的数据保护模式并重启数据库 --primary数据库操作
SQL> alter database set standby database to maximize availability;
提示：maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分别对应最大保护，最高可用性及最高性能。
Down掉数据库，重新启动
7.打开备用库恢复进程
recover managed standby database diconnect from session;
执行上面这条语句，备用库会在主库日志文件切换(备用日志文件切换)归档后，使用归档文件恢复数据库。
recover managed standby database using current logfile disconnect from session;
这条语句与上面不同的是，备用日志文件切换，生成归档日志前，先恢复数据库。
recover managed standby database finish;
这条语句是在做切换时，尽量多的保护数据。从备用日志文件中恢复数据
recover managed standby database cancel;
取消备用库自动恢复
---------------------------------------------相关视图
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database; --查看数据库的状态
select message from v$dataguard_status; --查看相关错误信息
alter system switch logfile; --切换日志
select DATABASE_ROLE from v$database; --查看当前数据库的角色 Pri/Std
select sequence#, applied from v$archived_log; --查看日志是否被应用
SELECT sid,status,username,action FROM V$SESSION; --查看当前系统的进程
select * from v$logfile;--查看redolog
select first_time,first_change#,next_change#,sequence# from v$log_history; --查询standby库中所有已被应用的归档文件信息(不论该归档文件是否还存在)
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#; --最后一个被应用的log
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#); --在primary server上查询有哪些日志没有被传输到Standby
------------------------------------------------------------------
二.Failovers的过程：
查询没有应用的日志：
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
Cp过来并register
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
如果備庫有配置standby log,可使用如下命令處理：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
如果備庫沒有配置standby log,可使用如下命令處理：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
2> SKIP STANDBY LOGFILE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> ALTER DATABASE OPEN;
一旦主数据故障无法使用，就可以迅速激活镜像备份：
1、如果可能，归档主服务器当前重做日志，传送到镜像服务器;
2、使用主服务器的归档日志文件做恢复：
SQL>recover standby database;
3、激活并打开数据库：
SQL>alter database activate standby database;
SQL>alter database mount;
SQL>alter database open;
三.将Failover之后的老的主库 flashback成新primary的standby库：
1. 在新主库查出failover时的SCN：
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
172313
2. 在old primary库上：
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN1503389;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Switchover_Status值
说 明
NOT ALLOWED
当前的数据库不是带有备用数据库的主数据库
PREPARING DICTIONARY
该逻辑备用数据库正在向一个主数据库和其他备用数据库发送它的重做数据，以便为切换做准备
PREPARING SWITCHOVER
接受用于切换的重做数据时，逻辑备用配置会使用它
RECOVERY NEEDED
备用数据库还没有接收到切换请求
SESSIONS ACTIVE
在主数据库中存在活动的SQL会话；在继续执行之前必须断开这些会话
SWITCHOVER PENDING
适用于那些已收到主数据库切换请求但是还没有处理该请求的备用数据库
SWITCHOVER LATENT
切换没有完成并返回到主数据库
TO LOGICAL STANDBY
主数据库已经收到了来自逻辑备用数据库的完整的字典
TO PRIMARY
该备用数据库可以转换为主数据库
TO STANDBY
该主数据库可以转换为备用数据库
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21158541/viewspace-627017/ ，如需转载，请注明出处，否则将追究法律责任。
