DataGuard环境搭建
来源：互联网 发布：湖南网络系统集成商 编辑：程序博客网 时间：2024/06/13 09:09
自己做的工作一直涉及的是单实例数据库，对容灾也没有特别高的要求，平时基本上是靠rman备份或是手工逻辑备份，所以一直想做一些关于dataguard的实验去验证一些感觉很棒的操作，本文参照了“三思”的一些文档搭建了一套这样的环境，很是兴奋，虽然对理论知识不是太理解，但是用实践来验证理论会更好的理解吧；下面是自己搭建dataguard环境时的一些简要记录：
创建主库，即在一台物理机上安装数据库软件及创建数据库作为主库，并启动到force logging 状态（alter database force logging;）；
创建备库，在另一台物理机上只安装数据库软件作为备库，所有路径与主库一致；
在主库上创建备库的控制文件：SQL> alter database create standbycontrolfile as 'd:\backup\control.ctl';
关闭主库，把主库所有数据文件（如果备库没有相关目录，那么连带目录一并），拷贝到备库的相应位置，把创建的备库控制文件拷贝到拷贝到相应位置然后复制与主库一样的份数及名称；
拷贝主库的密码文件到备库的相应文件中；
修改主库的spfile 文件，主要是添加下列内容，修改的时候创建pfile，修改后再创建spfile：
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=F:\flash_recover_area\ORCL\ARCHIVELOGVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_dest_2='SERVICE=standbyarch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
修改主库的tns,添加下列内容:
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =45394bcd93e1473)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.217.130)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
当然备库的spfile也得修改，主要添加下列内容：
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=F:\flash_recover_area\ORCL\ARCHIVELOGVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primaryarch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_CLIENT='standby'
*.FAL_SERVER='primary'
修改备库的tns,添加下列内容:
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.1.106.57)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.217.130)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
由于备库没有实力服务，所以在备库中创建实例的服务：
1.用oradim工具创建备库orcl实例
oradim-new -sid orcl -startmode m
oradim-edit -sid orcl -startmode a
其实到这里配置基本上就结束了，下面就是启动和验证是否可用了；
启动及关闭顺序，启动时先启动备库，再主库；关闭时，先关闭主库，再备库；
在备库将实例启动到mount 状态：
SQL> startup nomount;
SQL>alter database mount standby database ;
SQL>alter database recover managed standby database disconnect from session;
SQL>alter database recover managed standby database cancel;
备库启监听：
$lsnrctl start
主库启实例：
SQL> startup;
主库启监听：
$lsnrctl start
在主库验证归档目录是否有效：
SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
如果有错误，要排查原因。
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
主备切换
--主库
SQL> select switchover_status fromv$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> alter database commit to switchoverto physical standby with session shutdown ;
数据库已更改。
SQL> alter database commit to switchoverto physical standby;
alter database commit to switchover tophysical standby
*
第 1 行出现错误:
ORA-01507: 未装载数据库
SQL> shutdown immediate;
ORA-01507: 未装载数据库
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 836976640 bytes
Fixed Size 1377812 bytes
Variable Size 687868396 bytes
Database Buffers 142606336 bytes
Redo Buffers 5124096 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database commit to switchoverto physical standby;
数据库已更改。
SQL> select switchover_status fromv$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 836976640 bytes
Fixed Size 1377812 bytes
Variable Size 687868396 bytes
Database Buffers 142606336 bytes
Redo Buffers 5124096 bytes
SQL> alter database mount standbydatabase;
数据库已更改。
SQL> alter database recover managedstandby database disconnect from session;
数据库已更改。
---备库
SQL> select switchover_status fromv$database;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
SQL> alter database commit to switchoverto primary;
alter database commit to switchover toprimary
*
ERROR at line 1:
ORA-16139: media recovery required
开始介质恢复
SQL> alter database recover managedstandby database finish;
Database altered.
SQL> alter database commit to switchoverto primary;
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1377812 bytes
Variable Size 687868396 bytes
Database Buffers 142606336 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> select switchover_status fromv$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
以下是介质恢复时的alert 日志：
alterdatabase commit to switchover to primary
Maximumwait for role transition is 15 minutes.
Databasenot available for switchover
End-Of-REDO archived log file has beenreceived
End-Of-REDO archived log file has not beenrecovered
Archived log files detected beyondEnd-Of-REDO
Incomplete recovery SCN:7:-536647664 archiveSCN:7:-536628763
Databasenot available for switchover
End-Of-REDO archived log file has beenreceived
End-Of-REDO archived log file has not beenrecovered
Archived log files detected beyondEnd-Of-REDO
Incomplete recovery SCN:7:-536647664 archiveSCN:7:-536628763
Switchover:Media recovery required - standby not in limbo
ORA-16139signalled during: alter database commit to switchover to primary...
WedSep 04 11:00:45 2013
alterdatabase recover managed standby database finish
SerialMedia Recovery started
ManagedStandby Recovery not using Real Time Apply
WARNING!Recovering data file 6 from a fuzzy file. If not the current file
itmight be an online backup taken without entering the begin backup command.
WARNING!Recovering data file 7 from a fuzzy file. If not the current file
itmight be an online backup taken without entering the begin backup command.
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000066823188844.ARC
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000067823188844.ARC
WedSep 04 11:00:59 2013
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000068823188844.ARC
WedSep 04 11:01:12 2013
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000069823188844.ARC
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000070823188844.ARC
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000071823188844.ARC
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000072823188844.ARC
WedSep 04 11:01:22 2013
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000073823188844.ARC
WedSep 04 11:01:33 2013
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000074823188844.ARC
MediaRecovery Log F:\FLASH_RECOVER_AREA\ORCL\ARCHIVELOG\00010000000075823188844.ARC
IdentifiedEnd-Of-Redo for thread 1 sequence 75
Resettingstandby activation ID 1350943370 (0x5085c28a)
MediaRecovery End-Of-Redo indicator encountered
MediaRecovery Applied through change 33823109605
Completed:alter database recover managed standby database finish
好像是当时主库传过来的归档日志都没有应用；另外注意到，原始主库发出归档命令时，先归档到主库中，后归档到备库中，切换之后，现在的主库发出归档命令后还是先归档到原来的主库（即现在的备库），然后才是自己；
看来理论知识还得好好搞一搞；
- 搭建DataGuard环境
- DataGuard环境搭建
- Oracle dataguard一主两备环境搭建
- DataGuard之DG环境搭建
- Oracle dataguard一主两备环境搭建
- Oracle10g RAC环境下DataGuard备库搭建实例
- ORACLE RAC ASM环境下DATAGUARD搭建步骤
- 快速搭建dataguard
- oracle dataguard搭建
- dataguard搭建实验
- rac dataguard搭建过程
- Dataguard搭建灾备库操作
- Windows 下DataGuard 搭建
- DataGuard搭建 （一主两备）
- DATAGUARD原理及搭建
- Oracle 11g DataGuard Physical Standby 测试环境搭建 For windows
- 高级复制RMAN搭建DATAGUARD
- oracle 11g dataguard 搭建
- 数字万用表测量二极管、三极管
- 茴香防治口臭？美国公布9个神奇的健康偏方
- 11.网络编程之四
- 04-应用程序域
- S3C2410 实验十—— SPI（串口外设接口）实验
- DataGuard环境搭建
- 外观模式解析
- Chrome DevTools
- C++ 中 关于指针内存
- 12.网络编程之五
- Android 匿名共享内存C++接口分析
- Ubuntu 12.04上搭建Hadoop环境(一）
- SQLServer判断数据库，表，列名，存储过程是否存在
- POJ DP分类
