Setting up 11g Active Dataguard(ADG)
admin
2023-05-10 19:01:57
0次

 

环境：

主：

IP: 172.18.130.55    DB:11.2.0.1 SID: wsqtest  

ORACLE_BASE: /apps/oracle 

ORACLE_HOME:/apps/oracle/product/11.2.0.1

备：

IP:  172.18.130.52  DB:11.2.0.1  SID:wsqteststd

ORACLE_BASE:/apps/oracle

ORACLE_HOME:/apps/oracle/product/11.2.0.1

 

55上安装好oracle软件、建好数据库,52上装好oracle软件，不装库

 

1、主库55上，开启归档，force logging

SQL> conn /as sysdba

Connected.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     20

Next log sequence to archive   22

Current log sequence           22

SQL> alter database force logging;

 

Database altered.

 

2、配置监听：

主：

--静态注册

listener.ora:

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

    (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /apps/oracle/product/11.2.0.1)

         (PROGRAM = extproc)

   )

  (SID_DESC =

   (GLOBAL_DBNAME =wsqtest)

     (ORACLE_HOME = /apps/oracle/product/11.2.0.1)

     (SID_NAME =wsqtest)

  )

)

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = wsqtest)(PORT = 1521))

    )

  )

 

--tnsnames.ora:

WSQTEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.130.55)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = wsqtest)

    )

  )

 

WSQTESTSTD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.130.52)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = wsqteststd)

    )

  )

 

 

3、配置参数文件：

主库添加以下参数：

*.log_archive_config='dg_config=(wsqtest,wsqteststd)'

*.log_archive_dest_1='location=/apps/oracle/flash_recovery_area/wsqtest/archivelogs valid_for=(all_logfiles,all_roles) db_unique_name=wsqtest'

*.log_archive_dest_2='service=wsqteststd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=wsqteststd'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='wsqteststd'

*.fal_client='wsqtest'

*.db_unique_name='wsqtest'

 

4、备库端创建密码文件

$cd $ORACLE_HOME/dbswho

$orapwd  file=orapwwsqteststd password=oracle entries=5

 

5、创建相应的目录

mkdir /apps/oracle/oradata/wsqteststd

mkdir /apps/oracle/oradata/wsqteststd/onlinelogs  /apps/oracle/oradata/wsqteststd/datafiles /apps/oracle/oradata/wsqteststd/controlfiles

mkdir /apps/oracle/admin/wsqteststd

mkdir /apps/oracle/admin/wsqteststd/adump  /apps/oracle/admin/wsqteststd/bdump /apps/oracle/admin/wsqteststd/cdump /apps/oracle/admin/wsqteststd/udump

mkdir /apps/oracle/flash_recovery_area/wsqteststd

 

6、修改备库参数文件

从主库参数文件复制过来，修改响应的路径，添加响应的参数：

*.log_archive_config='dg_config=(wsqteststd,wsqtest)'

*.log_archive_dest_1='location=/apps/oracle/flash_recovery_area/wsqteststd/archivelogs valid_for=(all_logfiles,all_roles) db_unique_name=wsqteststd'

*.log_archive_dest_2='service=wsqtest reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=wsqtest'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='wsqtest'

*.fal_client='wsqteststd'

*.log_file_name_convert='/apps/oracle/oradata/wsqtest/onlinelogs/','/apps/oracle/oradata/wsqteststd/onlinelogs/'

*.db_file_name_convert='/apps/oracle/oradata/wsqtest/datafiles/','/apps/oracle/oradata/wsqteststd/datafiles/'

 

 

7、备库启动到nomount

[oracle@localhost dbs]$ env | grep ORACLE_SID

ORACLE_SID=wsqteststd

[oracle@localhost dbs]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 4 14:43:21 2013

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area  450953216 bytes

Fixed Size                  2214256 bytes

Variable Size             142608016 bytes

Database Buffers          301989888 bytes

Redo Buffers                4141056 bytes

SQL>

 

8、备库开始复制数据库：

[oracle@localhost ~]$ rman target sys/oracle@wsqtest auxiliary sys/oracle@wsqteststd

 

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 4 14:54:06 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: WSQTEST (DBID=1260868535)

connected to auxiliary database: WSQTEST (not mounted)

 

RMAN> duplicate target database for standby from active database;

 

Starting Duplicate Db at 2013-12-04 14:55:18

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

 

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/apps/oracle/product/11.2.0.1/dbs/orapwwsqtest' auxiliary format

 '/apps/oracle/product/11.2.0.1/dbs/orapwwsqteststd'   ;

}

executing Memory Script

 

Starting backup at 2013-12-04 14:55:20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=15 device type=DISK

Finished backup at 2013-12-04 14:55:21

 

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/apps/oracle/oradata/wsqteststd/controlfiles/control01.ctl';

   restore clone controlfile to  '/apps/oracle/flash_recovery_area/wsqteststd/controlfiles/control02.ctl' from

 '/apps/oracle/oradata/wsqteststd/controlfiles/control01.ctl';

}

executing Memory Script

 

Starting backup at 2013-12-04 14:55:21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/apps/oracle/product/11.2.0.1/dbs/snapcf_wsqtest.f tag=TAG20131204T145308 RECID=1 STAMP=833295189

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2013-12-04 14:55:22

 

Starting restore at 2013-12-04 14:55:22

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 2013-12-04 14:55:23

 

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

 

sql statement: alter database mount standby database

 

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/apps/oracle/oradata/wsqteststd/datafiles/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf";

   set newname for datafile  2 to

 "/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf";

   set newname for datafile  3 to

 "/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf";

   set newname for datafile  4 to

 "/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf"   datafile

 2 auxiliary format

 "/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf"   datafile

 3 auxiliary format

 "/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf"   datafile

 4 auxiliary format

 "/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /apps/oracle/oradata/wsqteststd/datafiles/temp01.dbf in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting backup at 2013-12-04 14:55:29

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/apps/oracle/oradata/wsqtest/datafiles/undotbs01.dbf

output file name=/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf tag=TAG20131204T145316

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/apps/oracle/oradata/wsqtest/datafiles/system01.dbf

output file name=/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf tag=TAG20131204T145316

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/apps/oracle/oradata/wsqtest/datafiles/sysaux01.dbf

output file name=/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf tag=TAG20131204T145316

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/apps/oracle/oradata/wsqtest/datafiles/data01.dbf

output file name=/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf tag=TAG20131204T145316

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 2013-12-04 14:55:57

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf

Finished Duplicate Db at 2013-12-04 14:55:59

 

RMAN>

 

 

9、备库添加standbylog：比主库redolog组数多一组

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo01.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo02.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo03.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo04.log') size 50m;

 

Database altered.

 

10、主库添加standbylog：

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo01.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo02.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo03.log') size 50m;

 

Database altered.

 

SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo04.log') size 50m;

 

Database altered.

 

 

11、开启redo real-time apply

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01531: a database already open by the instance

 

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY

 

SQL> alter database recover managed standby database using current logfile disconnect;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY WITH APPLY

 

 

 

 

 

 

上一篇：采用存储过程的方式批量更新数据

下一篇：最先告知德扑之星插件程序辅助！一起宁德钓蟹作弊是有挂的

相关内容

热门资讯

空调连续工作多久需要停机休息 空调连续工作的时间取决于多个因素，包括空调的型号、功率、使用环境和维护情况等。一般来说，空调可以连续...
空调哪个符号是除湿的 空调除湿功能通常用一个水滴形状的符号表示。不同品牌和型号的空调遥控器可能会有所差异，但一般来说，除湿...
格力空调多长时间加一次氟利昂 1、一般情况下，空调对氟利昂的使用量是非常小的，所以在空调使用了四年左右的时候再考虑加也没问题。2、...
空调用多长时间需要加氟一次 一般情况下是五年。当出现以下这几种情况时，空调才需要加氟。1、空调的室内机和室外机各有两个接口，当接...
格调生活·C标思享荟：卡萨帝在... 9月12日的郑州龙湖金融岛，夜风轻拂，灯光璀璨。“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑...
品牌价值破千亿、高端份额持续领... 9月12日，“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑州站”在郑州龙湖金融岛举办。2026...
空调主板坏了修800还是换新的 空调主板坏了是修还是换新，需要考虑多个因素，以下是一些建议：1. 维修成本：如果维修费用较低，比如 ...
空调开多久需要休息多长时间 问题：空调开多久需要休息多长时间回答：1、在使用空调的过程中，一般空调开7~8个小时左右需要休息2~...
中央空调多长时间清洗一次 1、一般空调开机前清洗一次即可，空调开机中间时段再清洗一次，空调关机时清洗一次，这样比较合理。2、空...
中央空调氟利昂多久加一次 看空调制热和制冷程度，很多人的观点认为氟利昂必须一年放一次，其实这个观点是错误的，理解上面有点偏差，...