文档章节

Duplicate复制数据库并创建物理StandBy(pfile版本)

小强斋太
 小强斋太
发布于 2016/11/09 20:08
字数 4195
阅读 0
收藏 0
点赞 0
评论 0

1设定环境如下:

Primary数据库

IP 172.17.22.16
SID orcl

Standby数据库

IP 172.17.22.17
SID orcl

设置提示,以区分操作的位置

primary数据库 

set SQLPROMPT Primary>

standby数据库

set SQLPROMPT StandBy>

1、Primary端设置 归档模式+强制日志

确保primary数据库运行在归档模式

Primary>archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7
Primary>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Primary>startup mount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
Primary>alter database archivelog;

Database altered.

Primary>alter database open;

Database altered.


Primary>archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
Primary>

开启强制日志

Primary>select force_logging from v$database;

FOR
---
NO

Primary>alter database force logging;

Database altered.

2、standby端创建相关目录

为了和Primary库保存相同的结构,我们需要在Standby数据库建立相同的目录,首先查询现有Primary数据库的相关目录

Primary>col name for a30
Primary>col value for a100
Primary>select name ,value from v$parameter  where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;

NAME                   VALUE
------------------------------ ---------------------------------------------------------------------------------------------audit_file_dest            /usr/oracle/app/admin/orcl/adump
background_dump_dest    /usr/oracle/app/diag/rdbms/orcl/orcl/trace
control_files           /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl
core_dump_dest          /usr/oracle/app/diag/rdbms/orcl/orcl/cdump
user_dump_dest          /usr/oracle/app/diag/rdbms/orcl/orcl/trace

在standby数据库服务器创建相同的目录

[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace

3、创建辅助实例密钥文件

方法一: 直接从Primary数据库复制密钥文件过来

[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
oracle@172.17.22.17's password: 
orapworcl                                     100% 1536     1.5KB/s   00:00    
[oracle@oracledb dbs]$

方法二: orapwd生成

orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;

4、生成standby端的pfile

在Primary端根据spfile生产pfile

Primary>create pfile from spfile;

File created.

修改Primary端的pfile内容如下

orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1325400064
orcl.__sga_target=1979711488
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=436207616
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=3299868672
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'




*.DB_UNIQUE_NAME=db_primary
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby'
*.LOG_ARCHIVE_DEST_STATE_2=DEFER 


*.FAL_SERVER=tns_standby
*.FAL_CLIENT=tns_primary
*.STANDBY_FILE_MANAGEMENT=AUTO

Primary端重新启动

Primary>create spfile from pfile;

File created.

Primary>startup
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
Database opened.

Primary>show parameter fal

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
fal_client                 string            tns_primary
fal_server                 string            tns_standby

复制生成的initorcl.ora到standby的$ORACLE_HOME/dbs目录下,

[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs
[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
The authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.
RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.
oracle@172.17.22.17's password: 
initorcl.ora                                  100% 1291     1.3KB/s   00:00    
[oracle@oracledb dbs]$

修改standbyd端的initorcl.ora文件,内容如下

orcl.__db_cache_size=1476395008
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/usr/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1325400064
orcl.__sga_target=1979711488
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=436207616
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/oracle/app/oradata/orcl/control01.ctl','/usr/oracle/app/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/usr/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/usr/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=3299868672
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'




*.DB_UNIQUE_NAME=db_standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_primary,db_standby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary'
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE


*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO

通过复制的pfile创建Standby数据库的spfile

StandBy> create spfile from pfile;

File created.

启动到nomount环境

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes

5、配置监听服务

Primary端监听

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /usr/oracle/app

StandBy端监听(配置了静态监听 服务名GLOBAL_DBNAME = StandBy

# listener.ora Network Configuration File: /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = StandBy)
      (ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /usr/oracle/app

启动standby端监听

[oracle@oracledb admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:15

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

Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-DEC-2015 15:48:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))
Services Summary...
Service "StandBy" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6、配置网络服务名,并测试互通性

Primary端和StandBy端都要进行如下配置:

tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db_primary)
    )
  )


tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =StandBy )
    )
  )

***********************************************************************************************

SERVICE_NAME 的值参考lsnrctl的输出,确保用sqlplus工具或者navicact工具能够连接SERVICE_NAME

因为fal_server 会使用服务名,如果配置不正确,这归档日志无法正常发送

***********************************************************************************************

在primary端和standby端都进行测试(为了角色切换)

[oracle@oracledb admin]$ tnsping tns_primary

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:44

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

Used parameter files:
/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@oracledb admin]$ tnsping tns_standby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-DEC-2015 15:48:49

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

Used parameter files:
/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

7、duplicate standby

rman连接两个数据库

[oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:11:51 2015

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

connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)

开始复制

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 23-DEC-15
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2015 16:13:38
RMAN-05541: no archived logs found in target database

primary端切换下日志,重写执行rman duplicate即可

SYS@orcl>alter system switch logfile;

System altered.

SYS@orcl>quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracledb admin]$ rman target sys/wangshengzhuang@tns_primary auxiliary sys/wangshengzhuang@tns_standby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 16:14:59 2015

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

connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format 
 '/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl'   ;
}
executing Memory Script

Starting backup at 23-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
Finished backup at 23-DEC-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/usr/oracle/app/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/usr/oracle/app/flash_recovery_area/orcl/control02.ctl' from 
 '/usr/oracle/app/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151223T161504 RECID=1 STAMP=899223305
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

Starting restore at 23-DEC-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-DEC-15

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 
 "/usr/oracle/app/oradata/orcl/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/usr/oracle/app/oradata/orcl/system01.dbf";
   set newname for datafile  2 to 
 "/usr/oracle/app/oradata/orcl/sysaux01.dbf";
   set newname for datafile  3 to 
 "/usr/oracle/app/oradata/orcl/undotbs01.dbf";
   set newname for datafile  4 to 
 "/usr/oracle/app/oradata/orcl/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/usr/oracle/app/oradata/orcl/system01.dbf"   datafile 
 2 auxiliary format 
 "/usr/oracle/app/oradata/orcl/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/usr/oracle/app/oradata/orcl/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/usr/oracle/app/oradata/orcl/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /usr/oracle/app/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbf
output file name=/usr/oracle/app/oradata/orcl/system01.dbf tag=TAG20151223T161512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
output file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf tag=TAG20151223T161512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
output file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf tag=TAG20151223T161512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/usr/oracle/app/oradata/orcl/users01.dbf
output file name=/usr/oracle/app/oradata/orcl/users01.dbf tag=TAG20151223T161512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_8_c7np1mbd_.arc" auxiliary format 
 "/usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=2 STAMP=899223347
output file name=/usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_06qpi3pj_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_06qpi3pj_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_06qpi3pj_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=899223348 file name=/usr/oracle/app/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=899223348 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=899223348 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=899223348 file name=/usr/oracle/app/oradata/orcl/users01.dbf

contents of Memory Script:
{
   set until scn  1028421;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-DEC-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_06qpi3pj_.arc
archived log file name=/usr/oracle/app/flash_recovery_area/DB_STANDBY/archivelog/2015_12_23/o1_mf_1_8_06qpi3pj_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-DEC-15
Finished Duplicate Db at 23-DEC-15

RMAN>

8、启用日志传送

Primary>show parameter LOG_ARCHIVE_DEST_STATE_2

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_state_2         string            DEFER
log_archive_dest_state_20         string            enable
log_archive_dest_state_21         string            enable
log_archive_dest_state_22         string            enable
log_archive_dest_state_23         string            enable
log_archive_dest_state_24         string            enable
log_archive_dest_state_25         string            enable
log_archive_dest_state_26         string            enable
log_archive_dest_state_27         string            enable
log_archive_dest_state_28         string            enable
log_archive_dest_state_29         string            enable
Primary>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

8、验证结果

查询primary数据库角色

Primary>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

查询standby数据库角色

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

StandBy>

primary端插入一条数据

Primary> INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('12', 'OPERATIONS', 'OPERATIONS');

1 row created.

Primary>commit;

Commit complete.

Primary>alter system switch logfile;

System altered.

standby端:启动redo应用、暂停redo应用 打开数据库,查询数据是否被同步过来了

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

Database altered.

StandBy>alter database recover managed standby database cancel;

Database altered.

StandBy>alter database open;

Database altered.

StandBy>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ ONLY

StandBy>

见证奇迹的时刻:

StandBy>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
    10 ACCOUNTING            NEW YORK
    20 RESEARCH            DALLAS
    30 SALES            CHICAGO
    40 OPERATIONS            BOSTON
    12 OPERATIONS            OPERATIONS

9、启用实时应用redo

添加redo log

首先查询当前redo log的大小、位置

Primary>col group# for 9
Primary>col status for a10
Primary>col type for a10
Primary>col member for a50;
Primary>col is_rec for a10
Primary>select * from v$logfile;

GROUP# STATUS      TYPE         MEMBER                        IS_REC
------ ---------- ---------- -------------------------------------------------- ------
     3          ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
     2          ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
     1          ONLINE     /usr/oracle/app/oradata/orcl/redo01.log        NO

Primary>select * from v$log;

GROUP#      THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIV STATUS    FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ---------- ---------- ---------- ------ ---------- ------------- ------------ ------------ ------------
     1        1      10   52428800        512        1 NO     CURRENT          1029234 23-DEC-15      2.8147E+14
     2        1       8   52428800        512        1 YES    INACTIVE          1028340 23-DEC-15     1028421 23-DEC-15
     3        1       9   52428800        512        1 YES    INACTIVE          1028421 23-DEC-15     1029234 23-DEC-15

 

当前有三组、每组1个member、大小为50M, 我们增加四组,每组1个member,大小为50M

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE  50 M;

Database altered.

Primary>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE  50 M;

Database altered.

查看结果:

Primary>select * from v$logfile;

GROUP# STATUS      TYPE         MEMBER                        IS_REC
------ ---------- ---------- -------------------------------------------------- ------
     3          ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
     2          ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
     1          ONLINE     /usr/oracle/app/oradata/orcl/redo01.log        NO
     4          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog4a.log    NO
     5          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog5a.log    NO
     6          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog6a.log    NO
     7          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog7a.log    NO

7 rows selected.

同理在standby端做相同的操作

StandBy>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 4('/usr/oracle/app/oradata/orcl/stbyredolog4a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 5('/usr/oracle/app/oradata/orcl/stbyredolog5a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 6('/usr/oracle/app/oradata/orcl/stbyredolog6a.log') SIZE  50 M;

Database altered.

StandBy>ALTER DATABASE ADD STANDBY   LOGFILE GROUP 7('/usr/oracle/app/oradata/orcl/stbyredolog7a.log') SIZE  50 M;

Database altered.
StandBy>select * from v$logfile;

GROUP# STATUS      TYPE         MEMBER                                                  IS_REC
------ ---------- ---------- ---------------------------------------------------------------------------------------------------- ------
     3          ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_3_c7np1p97_.log              YES
     2          ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_2_c7np1osm_.log              YES
     1          ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_1_c7np1od8_.log              YES
     4          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog4a.log                              NO
     5          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog5a.log                              NO
     6          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog6a.log                              NO
     7          STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog7a.log                              NO

 

更改primary和standby端的log_archive_dest_2、

Primary>show parameter log_archive_dest_2

NAME                     TYPE    VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2             string    SERVICE=tns_standby ARCH VALID
                        _FOR=(ONLINE_LOGFILES,PRIMARY_
                        ROLE) DB_UNIQUE_NAME=db_standb
                        y
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string
Primary>alter system set log_archive_dest_2='SERVICE=tns_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_standby';

System altered.

Primary>show parameter log_archive_dest_2
NAME                     TYPE    VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2             string    SERVICE=tns_standby LGWR VALID
                        _FOR=(ONLINE_LOGFILES,PRIMARY_
                        ROLE) DB_UNIQUE_NAME=db_standb
                        y
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string
Primary>

更改standby端的log_archive_dest_2

StandBy>alter system set log_archive_dest_2='SERVICE=tns_primary LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db_primary';

启用实时StandBy端redo应用

StandBy>alter database recover managed standby database using current logfile  disconnect from session;

验证:

首先在Primay端插入一条数据:

Primary>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
    10 ACCOUNTING            NEW YORK
    20 RESEARCH            DALLAS
    30 SALES            CHICAGO
    40 OPERATIONS            BOSTON
    12 OPERATIONS            OPERATIONS

Primary>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('13', 'OPERATIONS', 'OPERATIONS');

1 row created.

Primary>commit;

Commit complete.

standby端查看

StandBy>select * from scott.dept;
    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
    10 ACCOUNTING            NEW YORK
    20 RESEARCH            DALLAS
    30 SALES            CHICAGO
    40 OPERATIONS            BOSTON
    12 OPERATIONS            OPERATIONS
  13 OPERATIONS OPERATIONS

6 rows selected.

10、switchover

备库暂停 redo应用

StandBy>alter database recover managed standby database cancel;

Database altered.

查询主库是否支持switchover操作

Primary> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO STANDBY

查询备库是否支持switchover操作

StandBy> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED

not allowed是因为主库还未变切换为standby

switchover,primary切换为物理standby,切换后查看数据库角色、打开模式、

Primary>alter database commit to switchover to physical standby;

Database altered.

Primary>shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
Primary>startup
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
Database opened.
Primary>select open_mode  from v$database;

OPEN_MODE
----------------------------------------
READ ONLY

Primary>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

Primary>

物理standby切换为primary

StandBy>select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY

StandBy> alter database commit to switchover to primary;

Database altered.

StandBy>alter database open;

Database altered.

StandBy>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

StandBy>

测试同上(略)

常用查询

 

1、查询进程的活动状态

select process,client_process,sequence#,status from v$managed_standby;

2、查询redo应用进度

select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status ='VALID';

3、查询归档文件路径及创建信息

select name,creator,sequence#,applied,completion_time from v$archived_log;

4、查询归档历史

select first_time,first_change#,next_change#,sequence# from v$log_history;

select thread#,sequence#,applied from v$archived_log;

5、参看数据库的基本信息

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

6、查询redo应用及redo传输服务的活动状态

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

7、检查应用模式

StandBy>select recovery_mode from v$archive_dest_status where dest_id=2;

RECOVERY_MODE
----------------------------------------------
IDLE

取值

idle

managed:

managed real_time_apply:

© 著作权归作者所有

共有 人打赏支持
小强斋太
粉丝 0
博文 181
码字总数 0
作品 0
广州
Physical Standby Database (11g)-ADG

一、主库打开forcelogging startup mount; alter database force logging; 二、主 备 库配置监听 主:listener.oraSIDLISTLISTENER = (SIDLIST = (SIDDESC = (GLOBALDBNAME = primary) (ORA......

Vimeo ⋅ 2016/02/21 ⋅ 0

DG实施方案(主库为双节点rac)--rman duplicate方式

环境信息: 操作系统版本: AIX6.1 数据库版本: ORACLE 11.2.0.3(psu5) 主库为双节点rac 1.检查数据库是否支持Data Guard(只有企业版才支持DG) SQL> select * from v$option where para...

shaochenshuo ⋅ 2014/05/14 ⋅ 0

搭建 Oracle Data Guard

用 rman duplicate 进行创建 Standby 库 主 备 主机名 node1 node2 ip 192.168.0.191 192.168.0.192 dbname moxi moxi instancename moxi moxi dbuniquename mth atlas service_names moxi m......

ohio ⋅ 2013/04/07 ⋅ 0

Oracle 11g DataGuard物理standby配置1

Oracle dataguard 简介 Dataguard分为2类: 物理standby: 此类型分为redo应用、read-only模式、read-write模式,当处于redo应用的时候,不能进行相关的查询操作,当处于read-only时,不能应用red...

wiliiwin ⋅ 2010/07/05 ⋅ 0

RMAN简明教程之五——RMAN还原和恢复

一、常规还原与恢复 RMAN 的整个恢复过程可以分为还原(restore)与恢复(recover) ,他们在含义上是有很大差别的,一个是指物理意义的文件的还原与拷贝,一个是指数据库一致性的恢复,所以...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

同一环境下新建Standby RAC库

需求:在同一个环境下新建Standby RAC库,即和Primary RAC在相同的磁盘组。 说明:生产环境一般不建议这样配置DG,因为存储层面是相同磁盘组,灾备的实际意义不大。我这里是用作读写分离。 ...

xmgdc ⋅ 2017/06/29 ⋅ 0

数据库迁移方案之DG方式迁移

一:环境信息 1)主库(单实例) 主机平台:AIX6.1 数据库版本:11.2.0.3(psu5) 2)备库(部署了crs) 主机平台:AIX6.1 数据库版本:11.2.0.3(psu5) 二:方案背景 由于业务量增加,数据库...

shaochenshuo ⋅ 2014/04/09 ⋅ 0

ORACLE 11G 之Rman Duplicating a database

Oracle 11 g2 版本下,duplicating a database 有两种方法: 1. Active database duplication 2. Backup-based duplication 通过duplicate 一个数据库的好处在于: Test backup and recovery......

天真花语 ⋅ 2013/08/13 ⋅ 0

oracle11g dataguard 完全手册

一、前言: 网络上关于dataguard的配置文章很多,但是很多打着oracle11g的文章实际都是只能在9 10 上运行,比如FAL_CLIENT在11g中已经废弃,但是现在网络上的文章都是没有标注这一点。而且对...

技术小甜 ⋅ 2017/11/22 ⋅ 0

Oracle 11g RAC到单实例ASM的物理Standby搭建

一、DG环境配置 此次搭建Standby的主库为:” RedHat 6.5+11G+RAC+ASM安装与配置(三节点)”,11g单实例ASM安装使用ASMLib的方式,不在使用UDEV方式,磁盘为本机磁盘,非远程挂载的磁盘。本次...

朱wei ⋅ 2016/05/04 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Java 后台判断是否为ajax请求

/** * 是否是Ajax请求 * @param request * @return */public static boolean isAjax(ServletRequest request){return "XMLHttpRequest".equalsIgnoreCase(((HttpServletReques......

JavaSon712 ⋅ 31分钟前 ⋅ 0

Redis 单线程 为何却需要事务处理并发问题

Redis是单线程处理,也就是命令会顺序执行。那么为什么会存在并发问题呢? 个人理解是,虽然redis是单线程,但是可以同时有多个客户端访问,每个客户端会有 一个线程。客户端访问之间存在竞争...

码代码的小司机 ⋅ 今天 ⋅ 0

到底会改名吗?微软GVFS 改名之争

微软去年透露了 Git Virtual File System(GVFS)项目,GVFS 是 Git 版本控制系统的一个开源插件,允许 Git 处理 TB 规模的代码库,比如 270 GB 的 Windows 代码库。该项目公布之初就引发了争...

linux-tao ⋅ 今天 ⋅ 0

笔试题之Java基础部分【简】【二】

1.静态变量和实例变量的区别 在语法定义上的区别:静态变量前要加static关键字,而实例变量前则不加。在程序运行时的区别:实例变量属于某个对象的属性,必须创建了实例对象,其中的实例变...

anlve ⋅ 今天 ⋅ 0

Lombok简单介绍及使用

官网 通过简单注解来精简代码达到消除冗长代码的目的 优点 提高编程效率 使代码更简洁 消除冗长代码 避免修改字段名字时忘记修改方法名 4.idea中安装lombnok pom.xml引入 <dependency> <grou...

to_ln ⋅ 今天 ⋅ 0

【转】JS浮点数运算Bug的解决办法

37.5*5.5=206.08 (JS算出来是这样的一个结果,我四舍五入取两位小数) 我先怀疑是四舍五入的问题,就直接用JS算了一个结果为:206.08499999999998 怎么会这样,两个只有一位小数的数字相乘,怎...

NickSoki ⋅ 今天 ⋅ 0

table eg

user_id user_name full_name 1 zhangsan 张三 2 lisi 李四 `` ™ [========] 2018-06-18 09:42:06 星期一½ gdsgagagagdsgasgagadsgdasgagsa...

qwfys ⋅ 今天 ⋅ 0

一个有趣的Java问题

先来看看源码: public class TestDemo { public static void main(String[] args) { Integer a = 10; Integer b = 20; swap(a, b); System.out......

linxyz ⋅ 今天 ⋅ 0

十五周二次课

十五周二次课 17.1mysql主从介绍 17.2准备工作 17.3配置主 17.4配置从 17.5测试主从同步 17.1mysql主从介绍 MySQL主从介绍 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主...

河图再现 ⋅ 今天 ⋅ 0

docker安装snmp rrdtool环境

以Ubuntu16:04作为基础版本 docker pull ubuntu:16.04 启动一个容器 docker run -d -i -t --name flow_mete ubuntu:16.04 bash 进入容器 docker exec -it flow_mete bash cd ~ 安装基本软件 ......

messud4312 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部