热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

Oracle11gActiveDataGuardstepbystep:HowtocreateaP

DatabaseIPDB_UNIQUE_NAMEOracleNetServiceNamePrimary192.168.8.180prodProdStandby192.168.8.190standbystandbyDatabaseArchive_log_dest_1Archive_log_dest_2Primaryu01prodServicestandbyStandbyu01standbyServiceprod1.

Database IP DB_UNIQUE_NAME Oracle Net Service Name Primary 192.168.8.180 prod Prod Standby 192.168.8.190 standby standby Database Archive_log_dest_1 Archive_log_dest_2 Primary /u01/prod Service=standby Standby /u01/standby Service=prod 1.

Database

IP

DB_UNIQUE_NAME

Oracle Net Service Name

Primary

192.168.8.180

prod

Prod

Standby

192.168.8.190

standby

standby

Database

Archive_log_dest_1

Archive_log_dest_2

Primary

/u01/prod

Service=standby

Standby

/u01/standby

Service=prod

1. Preparing the Primary Database forStandby Database Creation

1.1 Enable Forced Logging

SQL> alter database force logging;

1.2 Enable Forced Logging

[oracle@prod dbs]$mkdir -p /u01/arch1/prod

[oracle@prod dbs]$mkdir -p /u01/arch2/standby

SQL> alter systemset db_recovery_file_dest='/u01/arch1/prod';

SQL> shutdownimmediate;

SQL> startupmount;

SQL> alterdatabase archivelog;

SQL> alterdatabase open;

SQL> archive loglist

Database logmode Archive Mode

Automaticarchival Enabled

Archivedestination USE_DB_RECOVERY_FILE_DEST

Oldest online logsequence 54

Next log sequence toarchive 56

Current logsequence 56

1.3 Configure Redo TransportAuthentication Using Remote Login Password File

[oracle@prod dbs]$orapwd file=orapwprod password=oracle entries=5 force=y

[oracle@prod dbs]$ls -lt orapwprod

-rw-r----- 1 oracleoinstall 2048 Jun 9 09:48 orapwprod

[oracle@prod dbs]$scp orapwprod 192.168.8.190:$ORACLE_HOME/dbs

[oracle@standby ~]$cd $ORACLE_HOME/dbs

[oracle@standbydbs]$ ls -lt orapwprod

-rw-r----- 1 oracleoinstall 2048 Jun 9 09:49 orapwprod

[oracle@standbydbs]$ mv orapwprod orapwstandby

1.4 Configure Standby Redo Log ForPrimary Database

The synchronous and asynchronous redo transport modes require that aredo transport destination have a standby redo log. A standby redo log is usedto store redo received from another Oracle database. Standby redo logs arestructurally identical to redo logs, and are created and managed using the sameSQL statements used to create and manage redo logs.

Redo received from another Oracle database via redo transport iswritten to the current standby redo log group by an RFS foreground process.When a log switch occurs on the redo source database, incoming redo is thenwritten to the next standby redo log group, and the previously used standbyredo log group is archived by an ARCn foreground process.

standby redo log file must be at least as large as the largest redolog file in the redo log of the redo source database. For administrative ease,Oracle recommends that all redo log files in the redo log at the redo sourcedatabase and the standby redo log at a redo transport destination be of thesame size.

The standby redo log must have at least one more redo log group thanthe redo log at the redo source database, for each redo thread at the redosource database. At the redo source database, query the V$LOG view to determinehow many redo log groups are in the redo log at the redo source database andquery the V$THREAD view to determine how many redo threads exist at the redosource database.

SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog01.log') size 10m;

SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog02.log') size 10m;

SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog03.log') size 10m;

SQL>alter database add standby logfile ('/u01/app/oracle/oradata/PROD/stblog04.log') size 10m;

1.5 Set Primary DatabaseInitialization Parameters

SQL> createpfile='/tmp/initprod.ora' from spfile;

Addthe following entries into the pfile '/tmp/initprod.ora':

Primary Database:Primary Role Initialization Parameters

DB_NAME=prod

DB_UNIQUE_NAME=prod

LOG_ARCHIVE_COnFIG='DG_COnFIG=(prod,standby)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/arch1/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_2=

'SERVICE=standby LGWR SYNC AFFIRM

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)

DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database:Standby Role Initialization Parameters

FAL_SERVER=standby

DB_FILE_NAME_COnVERT='/u01/app/oracle/oradata/STANDBY','/u01/app/oracle/oradata/PROD'

LOG_FILE_NAME_COnVERT='/u01/arch1/STANDBY','/u01/arch1/PROD','/u01/arch2/STANDBY','/u01/arch2/PROD'

STANDBY_FILE_MANAGEMENT=auto

1.6 Set Standby DatabaseInitialization Parameters

[oracle@prod prod]$scp /tmp/initprod.ora 192.168.8.190:$ORACLE_HOME/dbs

[oracle@standbydbs]$ mv initprod.ora initstandby.ora

*.__db_cache_size=41943040

*.__java_pool_size=4194304

*.__large_pool_size=4194304

*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

*.__pga_aggregate_target=109051904

*.__sga_target=155189248

*.__shared_io_pool_size=0

*.__shared_pool_size=92274688

*.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/standby/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/STANDBY/controlfile/control0l.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='prod'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='/u01/arch1'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=prodXDB)'

*.memory_target=262144000

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_NAME=prod

DB_UNIQUE_NAME=standby

LOG_ARCHIVE_COnFIG='DG_COnFIG=(prod,standby)'

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/arch1/STANDBY VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_2=

'SERVICE=standby LGWR SYNC AFFIRM

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)

DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=prod

DB_FILE_NAME_COnVERT='/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/STANDBY'

LOG_FILE_NAME_COnVERT='/u01/arch1/PROD','/u01/arch1/STANDBY','/u01/arch2/PROD','/u01/arch2/STANDBY'

STANDBY_FILE_MANAGEMENT=auto

[oracle@standbydbs]$ mkdir -p /u01/app/oracle/admin/standby/adump

[oracle@standbydbs]$ chown -R oracle:oinstall /u01/app/oracle/admin/standby/adump

[oracle@standbydbs]$ chmod -R 775 /u01/app/oracle/admin/standby/adump

[oracle@standbyu01]$ mkdir -p /u01/arch1/standby

[oracle@standbyu01]$ mkdir -p /u01/arch2/prod

1.7 Configure Listener for Primaryand Standby Database

Primary:NETCA

Standby:static listener ( userd forrman duplicate)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_NAME =standby)

(ORAACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = standby)

)

)

1.7 Configure Net Service Name forPrimary and Standby Database

PROD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =prod.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

)

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =standby.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = standby)

)

)

1.8 Duplicat Standby database fromPrimary database using RMAN

On standby

[oracle@standby ~]$echo $ORACLE_SID

Standby

SQL> startupnomount

SQL> createspfile from pfile;

Connect to the primary and standby db using rman onprimary or on standby

On primary

[oracle@prod prod]$rman target / auxiliary sys/oracle@standby

connected to targetdatabase: PROD (DBID=229264367)

connected toauxiliary database: PROD (not mounted)

Onstandby

[oracle@standby ~]$rman target sys/oracle@prod auxiliary sys/oracle@standby

connected to targetdatabase: PROD (DBID=229264367)

connected toauxiliary database: PROD (not mounted)

Create standby controlfile on the Primary Databaseserver using RMAN

sqlplus

1. startup mount the primary database;

2. alter database create standby controlfileas '/tmp/control0l.ctl';

3. alter primary database open;

rman

RMAN> copy current controlfile for standby to'/home/oracle/control0l.ctl';

Copy standby controlfile from Primary to Standby 'spath which is specified by the control_files intialization parameter of standby

[oracle@standbydbs]$ mkdir -p /u01/app/oracle/oradata/STANDBY/controlfile

[oracle@prod ~]$ scpcontrol0l.ctl standby:/u01/app/oracle/oradata/STANDBY/controlfile/control0l.ctl

RMAN> duplicatetarget database for standby from active database nofilenamecheck;

datafile 1 switchedto datafile copy

input datafile copyRECID=7 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_8v8y9m2x_.dbf

datafile 2 switchedto datafile copy

input datafile copyRECID=8 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_sysaux_8v8y9m91_.dbf

datafile 3 switchedto datafile copy

input datafile copyRECID=9 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_undotbs1_8v8y9mb8_.dbf

datafile 4 switchedto datafile copy

input datafile copyRECID=10 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_users_8v8y9md4_.dbf

datafile 5 switchedto datafile copy

input datafile copyRECID=11 STAMP=817682385 filename=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_example_8v8yglfz_.dbf

Finished DuplicateDb at 2013-06-09 21:59:48

Delete static listener configuration and reconfig thedynamic listener when duplicate finished

Check the instance status of standby when duplicatefinished

SQL> selectinstance_name,status from v$instance;

INSTANCE_NAME STATUS

----------------------------------------------------

standby MOUNTED

Open the standby instance in open read-only mode

SQL> alterdatabase open read only;

alter database openread only

*

ERROR at line 1:

ORA-10458: standbydatabase requires recovery

ORA-01152: file 1was not restored from a sufficiently old backup

ORA-01110: data file1:

'/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_8v8y9m2x_.dbf'

SQL> alterdatabase recover managed standby database disconnect from session;

SQL> alterdatabase open read only;

alter database openread only

*

ERROR at line 1:

ORA-10456: cannotopen standby database; media recovery session may be in

progress

SQL> alterdatabase recover managed standby database cancel;

Database altered.

alterdatabase recover managed standby database using current logfiledisconnect from session;

SQL> selectthread#,dest_id,max(sequence#) from

2 v$archived_log

3 group by thread#,dest_id

4 order by thread#;

THREAD# DEST_ID MAX(SEQUENCE#)

-------------------- --------------

1 1 39

selectthread#,dest_id,max(sequence#) from v$archived_loggroup by thread#,dest_idorder by thread#;

Primary:

SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# ARC STATUS

-------------------- ---------- --- ----------------

1 1 43 YES ACTIVE

2 1 44 YES ACTIVE

3 1 45 NO CURRENT

SQL> alter systemarchive log current;

System altered.

SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$log;

GROUP# THREAD# SEQUENCE# ARC STATUS

-------------------- ---------- --- ----------------

1 1 46 NO CURRENT

2 1 44 YES ACTIVE

3 1 45 YES ACTIVE

SQL> selectGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;

GROUP# THREAD# SEQUENCE# ARC STATUS

-------------------- ---------- --- ----------

4 1 0 NO UNASSIGNED

5 1 46 YES ACTIVE

6 0 0 YES UNASSIGNED

7 0 0 YES UNASSIGNED

Note: the sequence of current online redo log of Primary Database is equal to the sequence ofthe Active standby log of Standby Database,

It shows that the Real -TimeRedo Transport is enabled.

Data Guard Real Time SynchronizationTest

Primary

SQL> selectusername,account_status from dba_users where username = 'SCOTT';

USERNAME ACCOUNT_STATUS

--------------------------------------------------------------

SCOTT EXPIRED & LOCKED

Standby

SQL> select username,account_status from dba_userswhere username = 'SCOTT';

USERNAME ACCOUNT_STATUS

--------------------------------------------------------------

SCOTT EXPIRED & LOCKED

Primary

SQL> alter userscott account unlock;

User altered.

SQL> selectusername,account_status from dba_users where username = 'SCOTT';

USERNAME ACCOUNT_STATUS

--------------------------------------------------------------

SCOTT EXPIRED

Standby

SQL> select username,account_status from dba_userswhere username = 'SCOTT';

USERNAME ACCOUNT_STATUS

--------------------------------------------------------------

SCOTT EXPIRED

Primary

SQL> alter userscott identified by tiger;

User altered.

Standby

SQL> connscott/tiger;

Connected.


作者:xiangsir

QQ:444367417

MSN:xiangsir@hotmail.com

推荐阅读
  • 本文深入探讨了SQL数据库中常见的面试问题,包括如何获取自增字段的当前值、防止SQL注入的方法、游标的作用与使用、索引的形式及其优缺点,以及事务和存储过程的概念。通过详细的解答和示例,帮助读者更好地理解和应对这些技术问题。 ... [详细]
  • 本文详细探讨了 org.apache.hadoop.ha.HAServiceTarget 类中的 checkFencingConfigured 方法,包括其功能、应用场景及代码示例。通过实际代码片段,帮助开发者更好地理解和使用该方法。 ... [详细]
  • JavaScript 中创建对象的多种方法
    本文详细介绍了 JavaScript 中创建对象的几种常见方式,包括对象字面量、构造函数和 Object.create 方法,并提供了示例代码和属性描述符的解释。 ... [详细]
  • 本文详细介绍了优化DB2数据库性能的多种方法,涵盖统计信息更新、缓冲池调整、日志缓冲区配置、应用程序堆大小设置、排序堆参数调整、代理程序管理、锁机制优化、活动应用程序限制、页清除程序配置、I/O服务器数量设定以及编入组提交数调整等方面。通过这些技术手段,可以显著提升数据库的运行效率和响应速度。 ... [详细]
  • 本文介绍如何使用SAS根据输入数据集自动生成并执行SQL查询,其中CASE语句依据另一个数据集中的观测值动态调整。 ... [详细]
  • Oracle中NULL、空字符串和空格的处理与区别
    本文探讨了在Oracle数据库中使用NULL、空字符串('')和空格('_')时可能遇到的问题及解决方案。重点解释了它们之间的区别,以及在查询和函数中的行为。 ... [详细]
  • Oracle 数据导出为 SQL 脚本的详细步骤
    本文介绍如何使用 PL/SQL Developer 工具将 Oracle 数据库中的数据导出为 SQL 脚本,包括详细的步骤和注意事项。 ... [详细]
  • 简化报表生成:EasyReport工具的全面解析
    本文详细介绍了EasyReport,一个易于使用的开源Web报表工具。该工具支持Hadoop、HBase及多种关系型数据库,能够将SQL查询结果转换为HTML表格,并提供Excel导出、图表显示和表头冻结等功能。 ... [详细]
  • 1.介绍有时候我们需要一些模拟数据来进行测试,今天简单记录下如何用存储过程生成一些随机数据。2.建表我们新建一张学生表和教师表如下:CREATETABLEstudent(idINT ... [详细]
  • 在漫长的人生旅程中,谁能声称自己一路顺遂,毫无波折?谁又能断言未来不会遭遇挫折与挑战?成功并非一蹴而就,它背后往往隐藏着无数的艰辛与磨难。本文探讨了如何面对挫折、坚持不懈,最终实现梦想。 ... [详细]
  • 1.执行sqlsever存储过程,消息:SQLServer阻止了对组件“AdHocDistributedQueries”的STATEMENT“OpenRowsetOpenDatas ... [详细]
  • 在Fedora 31上部署PostgreSQL 12
    本文详细介绍如何在Fedora 31操作系统上安装和配置PostgreSQL 12数据库。包括环境准备、安装步骤、配置优化以及安全设置,确保数据库能够稳定运行并提供高效的性能。 ... [详细]
  • 本文介绍了解决在Windows操作系统或SQL Server Management Studio (SSMS) 中遇到的“microsoft.ACE.oledb.12.0”提供程序未注册问题的方法,特别针对Access Database Engine组件的安装。 ... [详细]
  • PostgreSQL 最新动态 —— 2022年4月6日
    了解 PostgreSQL 社区的最新进展和技术分享 ... [详细]
  • 本文详细介绍了MySQL数据库中的Bin Log和Redo Log,阐述了它们在日志记录机制、应用场景以及数据恢复方面的区别。通过对比分析,帮助读者更好地理解这两种日志文件的作用和特性。 ... [详细]
author-avatar
朗月清风的海角
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有