Oracle 灾备 -- dataguard broken

字数 21213阅读 684评论 0赞 2

Maximum protection This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.  
Maximum availability This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode.  
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.  
Maximum performance This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.  
When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.  
The maximum protection and maximum availability modes require that standby redo log files are configured on at least one standby database in the configuration. All three protection modes require that specific log transport attributes be specified on the LOG_ARCHIVE_DEST_n initialization parameter to send redo data to at least one standby database  
  
Maximum Protection Maximum Availability Maximum Performance   
Redo archival process LGWR LGWR LGWR or ARCH   
Network transmission mode SYNC SYNC SYNC or ASYNC when using LGWR process. SYNC if using ARCH process   
Disk write option AFFIRM AFFIRM AFFIRM or NOAFFIRM   
Standby redo log required? Yes Yes No, but it is recommended   
  
  
  
1.1 检查数据库是否支持 dataguard  
[oracle@systexdg1 /]$ sqlplus / as sysdba  
SQL*Plus: Release 10.2.0.5.0 - Production on 星期二 3月 22 20:11:25 2011  
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.  
Connected to:  
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
SQL> archive log list  
Database log mode Archive Mode  
Automatic archival Enabled  
Archive destination /u01/oracle/archive  
Oldest online log sequence 1  
Next log sequence to archive 2  
SQL> startup mount  
ORACLE instance started.  
Total System Global Area 285212672 bytes  
Fixed Size 2095704 bytes  
Variable Size 88081832 bytes  
Database Buffers 188743680 bytes  
Redo Buffers 6291456 bytes  
Database mounted.  
SQL> alter database force logging; 设置for logging 模式  
Database altered.  
SQL> alter database open;  
Database altered.  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1.2 设置primary 库参数  
  
1) 添加standby log Maximum protection Maximum availability模式需要standby log   
  
SQL> alter database add standby logfile thread 1 '/u01/oracle/oradata/systex/redo04.log' size 10m;  
SQL> alter database add standby logfile thread 1 '/u01/oracle/oradata/systex/redo05.log' size 10m;  
SQL> alter database add standby logfile thread 1 '/u01/oracle/oradata/systex/redo06.log' size 10m;  
  
2) 设置归档参数  
  
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5 scope=spfile;  
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=systex_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=systex_standby' scope=spfile;  
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(systex_primary,systex_standby)'scope=spfile;  
SQL> alter system set standby_archive_dest='/u01/oracle/standby_archive' scope=both;  
  
  
3) 设置切换为 standby 时的参数  
  
SQL> alter system set FAL_SERVER=systex_standby scope=spfile;  
SQL> alter system set FAL_CLIENT=systex_primary scope=spfile;  
SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;  
  
4) 设置 db_unique_name  
  
SQL> alter system set db_unique_name='systex_primary' scope=spfile;  
  
5) 创建 pfile standby controlfile   
  
SQL> create pfile='/u01/oracle/initsystex.ora' from spfile;  
SQL> alter database create standby controlfile as '/u01/oracle/standby.ctl';  
  
  
  
  
  
  
  
  
1.3 Listener 设置  
1) 配置主库为静态监听  
修改 listener.ora 参数使数据库运行在静态监听模式下  
SID_LIST_LISTENER=  
(SID_LIST=  
(SID_DESC=  
(SID_NAME=systex)  
(GLOBAL_DBNAME=systex)  
(ORACLE_HOME=/u01/oracle/product/10.2.0/db)  
)  
)  
  
  
[oracle@systexdg1 admin]$ lsnrctl start  
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 23-3月 -2011 09:29:11  
Copyright (c) 1991, 2010, Oracle. All rights reserved.  
Starting /u01/oracle/product/10.2.0/db/bin/tnslsnr: please wait...  
TNSLSNR for Linux: Version 10.2.0.5.0 - Production  
System parameter file is /u01/oracle/product/10.2.0/db/network/admin/listener.ora  
Log messages written to /u01/oracle/product/10.2.0/db/network/log/listener.log  
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=systexdg1)(PORT=1521)))  
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))  
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=systexdg1)(PORT=1521)))  
STATUS of the LISTENER  
------------------------  
Alias LISTENER  
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production  
Start Date 23-3月 -2011 09:29:12  
Uptime 0 days 0 hr. 0 min. 0 sec  
Trace Level off  
Security ON: Local OS Authentication  
SNMP OFF  
Listener Parameter File /u01/oracle/product/10.2.0/db/network/admin/listener.ora  
Listener Log File /u01/oracle/product/10.2.0/db/network/log/listener.log  
Listening Endpoints Summary...  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=systexdg1)(PORT=1521)))  
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))  
Services Summary...  
Service "systex" has 1 instance(s).  
Instance "systex", status UNKNOWN, has 1 handler(s) for this service...  
The command completed successfully  
  
  
2) 配置 tnsnames.ora  
  
  
SYSTEX_PRIMARY =  
(DESCRIPTION =  
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg1)(PORT = 1521))  
)  
(CONNECT_DATA =(SERVICE_NAME = systex)  
)  
)  
  
  
  
SYSTEX_STANDBY =  
(DESCRIPTION =  
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg2)(PORT = 1521))  
)  
(CONNECT_DATA =(SERVICE_NAME = systex)  
)  
)  
  
  
  
  
  
  
  
  
  
  
  
1.4 备份primary   
1) 采用冷备方式  
  
SQL> shutdown immediate  
Database closed.  
Database dismounted.  
ORACLE instance shut down.  
  
2) Copy datafile listener.ora tnsnames.ora initsystex.ora standby.ctl orapwsystex 到standby 主机  
[oracle@systexdg1 oracle]$ scp -r oradata/ systexdg2:/u01/oracle/  
[oracle@systexdg1 oracle]$ scp initsystex.ora systexdg2:/u01/oracle/  
scp product/10.2.0/db/network/admin/listener.ora systexdg2:/u01/oracle/product/10.2.0//db/network/admin/  
  
[oracle@systexdg1 oracle]$ scp product/10.2.0/db/network/admin/listenertnsnames.ora systexdg2:/u01/oracle/product/10.2.0  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1.5 准备 standby init 参数  
  
1) 修改归档参数  
standby_archive_dest='/u01/oracle/standby_archive'   
log_archive_dest_2='location=/u01/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=systex_primary'   
  
2) 修改 fal 参数  
FAL_SERVER=systex_standby   
FAL_CLIENT=systex_primary   
STANDBY_FILE_MANAGEMENT=auto   
  
3) 修改 db_unique_name  
db_unique_name='systex_primary'   
  
4) 修改 listener.ora   
主机名改为 systexdg2   
  
5) 修改 controlfile 参数  
  
*.control_files='/u01/oracle/standby.ctl'  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1.6 建立 standby 库  
1) 创建相关目录  
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump  
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump  
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump  
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump  
mkdir -p /u01/oracle/archive  
mkdir -p /u01/oracle/standby_archive  
  
2) 启动standby 库 创建监听  
  
SQL> startup mount pfile=/u01/oracle/initsystex.ora  
ORACLE instance started.  
Total System Global Area 285212672 bytes  
Fixed Size 2095704 bytes  
Variable Size 88081832 bytes  
Database Buffers 188743680 bytes  
Redo Buffers 6291456 bytes  
Database mounted.  
  
  
[oracle@systexdg2 oracle]$ lsnrctl start  
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 23-3月 -2011 13:07:51  
Copyright (c) 1991, 2010, Oracle. All rights reserved.  
Starting /u01/oracle/product/10.2.0/db/bin/tnslsnr: please wait...  
TNSLSNR for Linux: Version 10.2.0.5.0 - Production  
System parameter file is /u01/oracle/product/10.2.0/db/network/admin/listener.ora  
Log messages written to /u01/oracle/product/10.2.0/db/network/log/listener.log  
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=systexdg2)(PORT=1521)))  
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))  
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=systexdg2)(PORT=1521)))  
STATUS of the LISTENER  
------------------------  
Alias LISTENER  
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production  
Start Date 23-3月 -2011 13:07:52  
Uptime 0 days 0 hr. 0 min. 0 sec  
Trace Level off  
Security ON: Local OS Authentication  
SNMP OFF  
Listener Parameter File /u01/oracle/product/10.2.0/db/network/admin/listener.ora  
Listener Log File /u01/oracle/product/10.2.0/db/network/log/listener.log  
Listening Endpoints Summary...  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=systexdg2)(PORT=1521)))  
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))  
Services Summary...  
Service "systex" has 1 instance(s).  
Instance "systex", status UNKNOWN, has 1 handler(s) for this service...  
The command completed successfully  
  
测试两边使用 tnsname 互信能连通。  
3) 在standby 启用 redo apply  
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;  
  
  
至此 dataguard 创建完毕。  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1.7 Dataguard switchover  
A switchover is a role reversal between the primary database and one of its standby databases. A switchover ensures no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-create either database.  
B failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss.  
  
1) Primary switchover standby   
因为创建primary 的时候 standby 参数已经改过了,可以直接切换  
查看状态  
select switchover_status from v$database;  
切换  
alter database commit to switchover to physical standby with session shutdown;  
重启数据库  
alter database recover managed standby database disconnect from session;  
  
2) standby switchover primary  
select switchover_status from v$database;  
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;  
  
  
  
  
  
  
  
  
  
1.8 dataguard failover  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
1.9 dataguard broken 配置  
1) broken前提条件  
1.The primary and standby databases must be running Oracle Database 10g (10.2) and each can be installed in either a single-instance or multi-instance environment. The database must be licensed for Oracle Enterprise Edition or Personal Edition.  
2.You must use a server parameter file (SPFILE) to ensure the broker can persistently reconcile values between broker properties and any related initialization parameter values. See Section 4.3.2 for more information.  
3.The value of the DG_BROKER_START parameter must be set to TRUE. See Section 3.3 for more information. (Enterprise Manager sets this parameter automatically.)  
4.If any of the databases in the configuration is a RAC database, you must set up the DG_BROKER_CONFIG_FILEn initialization parameters for that database such that they point to the same shared files for all instances of that database. The shared files could be files on a cluster file system, if available, on raw devices, or stored using Automatic Storage Management (ASM).  
5.If you are using a nondefault port number (for example, something other than port 1521) for the local listener address, you must set the LOCAL_LISTENER initialization parameter on each instance that is part of a broker configuration to a listener address that is reachable by all members of the configuration. See the Oracle Database Reference and the Oracle Database Net Services Administrator's Guide for additional information.  
6.To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAMEattribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the Any database, including instances of the database, managed by the broker must be mounted.  
If any of the databases in the configuration is a RAC database, the START_OPTIONS for that database must be set to MOUNT in the Oracle Cluster Repository   
7.The primary database must be opened in ARCHIVELOG mode.  
8.You must set the COMPATIBLE initialization parameter to 9.2.0.1.0 or higher for both the primary and standby databases. However, if you want to take advantage of new Oracle Database 10g features, set theCOMPATIBLE parameter to 10.2.0.0.  
Ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, redo transport services may be unable to transmit redo data from the primary database to the standby databases.  
2) 启动broken   
Primary standby 分别操作  
SQL> alter system set dg_broker_start=true scope=both;  
SQL> alter system set db_domain='com' scope=spfile;  
SQL> alter system set global_names=true scope=both;  
SQL> alter system set dg_broker_config_file2='/u01/oracle/product/10.2.0/db/dbs/dr2.dat' scope=both;  
SQL> alter system set dg_broker_config_file1='/u01/oracle/product/10.2.0/db/dbs/dr1.dat' scope=both;  
  
[oracle@systexdg1 admin]$ ps -ef | grep dmon  
oracle 4457 1 0 09:55 ? 00:00:00 ora_dmon_systex  
oracle 6621 4383 0 10:16 pts/2 00:00:00 grep dmon  
3) 配置 listener  
Systex_primary listener.ora  
SID_LIST_LISTENER=  
(SID_LIST=  
(SID_DESC=  
(SID_NAME=systex)  
(GLOBAL_DBNAME=systex_primary_DGMGRL.com)  
(ORACLE_HOME=/u01/oracle/product/10.2.0/db)  
)  
)  
  
LISTENER =  
(DESCRIPTION_LIST =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg1)(PORT = 1521))  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))  
)  
)  
  
Systex_standby listener.ora  
SID_LIST_LISTENER=  
(SID_LIST=  
(SID_DESC=  
(SID_NAME=systex)  
(GLOBAL_DBNAME=systex_standby_DGMGRL.com)  
(ORACLE_HOME=/u01/oracle/product/10.2.0/db)  
)  
)  
  
  
LISTENER =  
(DESCRIPTION_LIST =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg2)(PORT = 1521))  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))  
)  
)  
  
Systex_primary tnsnames.ora  
SYSTEX_STANDBY =  
(DESCRIPTION =  
(ADDRESS_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg2)(PORT = 1521))  
)  
(CONNECT_DATA =  
(SERVICE_NAME = systex_standby_DGMGRL.com)  
)  
  
  
Systex_standby tnsnames.ora   
SYSTEX_PRIMARY =  
(DESCRIPTION =  
(ADDRESS_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = systexdg1)(PORT = 1521))  
)  
(CONNECT_DATA =  
(SERVICE_NAME = systex_primary_DGMGRL.com)  
)  
)  
Systex_primary lsnrctl   
Services Summary...  
Service "systexXDB.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_primary.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_primary_DGB.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_primary_DGMGRL.com" has 1 instance(s).  
Instance "systex", status UNKNOWN, has 1 handler(s) for this service...  
Service "systex_primary_XPT.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
The command completed successfully  
  
Systex_standby lsnrctl   
Services Summary...  
Service "systexXDB.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_standby.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_standby_DGB.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
Service "systex_standby_DGMGRL.com" has 1 instance(s).  
Instance "systex", status UNKNOWN, has 1 handler(s) for this service...  
Service "systex_standby_XPT.com" has 1 instance(s).  
Instance "systex", status READY, has 1 handler(s) for this service...  
The command completed successfully  
4) 配置broken   
  
[oracle@systexdg1 admin]$ dgmgrl sys/lihua@systex_primary  
DGMGRL for Linux: Version 10.2.0.5.0 - 64bit Production  
Copyright (c) 2000, 2005, Oracle. All rights reserved.  
Welcome to DGMGRL, type "help" for information.  
Connected.  
DGMGRL> create configuration 'systex' as primary database is 'systex_primary' connect identifier is systex_primary;  
Configuration "systex" created with primary database "systex_primary"  
DGMGRL> add database 'systex_standby' as connect identifier is systex_standby maintained as physical;  
Database "systex_standby" added  
DGMGRL> show configuration  
Configuration  
Name: systex  
Enabled: NO  
Protection Mode: MaxPerformance  
Fast-Start Failover: DISABLED  
Databases:  
systex_primary - Primary database  
systex_standby - Physical standby database  
Current status for "systex":  
DISABLED  
DGMGRL> enable configuration  
Enabled.  
DGMGRL> DGMGRL>   
DGMGRL> show configuration  
Configuration  
Name: systex  
Enabled: YES  
Protection Mode: MaxPerformance  
Fast-Start Failover: DISABLED  
Databases:  
systex_primary - Primary database  
systex_standby - Physical standby database  
Current status for "systex":  
Enable configuration 之前请确定 primary standby 两边 归档路径设置一直否则enable 会失败  
5) Switchover 测试  
  
DGMGRL> switchover to systex_standby  
Performing switchover NOW, please wait...  
Operation requires shutdown of instance "systex" on database "systex_primary"  
Shutting down instance "systex"...  
ORA-01109: database not open  
  
Database dismounted.  
ORACLE instance shut down.  
Operation requires shutdown of instance "systex" on database "systex_standby"  
Shutting down instance "systex"...  
ORA-01109: database not open  
  
Database dismounted.  
ORACLE instance shut down.  
Operation requires startup of instance "systex" on database "systex_primary"  
Starting instance "systex"...  
ORACLE instance started.  
Database mounted.  
Operation requires startup of instance "systex" on database "systex_standby"  
Starting instance "systex"...  
ORACLE instance started.  
Database mounted.  
  
Switchover succeeded, new primary is "systex_standby"  
DGMGRL> show configuration  
  
Configuration  
Name: systex  
Enabled: YES  
Protection Mode: MaxPerformance  
Fast-Start Failover: DISABLED  
Databases:  
systex_primary - Physical standby database  
systex_standby - Primary database  
  
Current status for "systex":  
  
6)

Oracle

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Oracle 灾备 -- dataguard broken

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料