首页 > 资讯 > 数据库 >Duplicate复制数据库并创建物理StandBy（pfile版本）

687

分享到

Duplicate复制数据库并创建物理StandBy（pfile版本）

Duplicate复制数据库并创建物理StandBy（pfile版本） 2016-10-14 20:10:16 687人浏览才女

摘要

1设定环境如下： Primary数据库 IP 172.17.22.16 SID orcl Standby数据库 IP 172.17.22.17 SID orcl 设置提示，以区分操作的位置 prim

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" #arch表示同步已归档的日志
*.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  #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"  #ARCH为已归档日志
*.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.


#红色字体为静态监听,后面duplicate需要用到静态监听注册
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/primary_password@tns_primary auxiliary sys/standby_password@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即可

View Code

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");
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; #standby端应用redo

Database altered.

StandBy>alter database recover managed standby database cancel; #取消应用redo

Database altered.

StandBy>alter database open;  #打开数据库

Database altered.

StandBy>select open_mode from v$database;   #standby是只读状态

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

StandBy>

见证奇迹的时刻

StandBy>select * from scott.dept;

DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING             NEW YORK
RESEARCH               DALLAS
SALES                  CHICAGo
OPERATIONS             BOSTON
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
------ ---------- ---------- -------------------------------------------------- ------
         ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
         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      10   52428800        512        1 NO     CURRENT          1029234 23-DEC-15      2.8147E+14
       1       8   52428800        512        1 YES    INACTIVE          1028340 23-DEC-15     1028421 23-DEC-15
       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
------ ---------- ---------- -------------------------------------------------- ------
         ONLINE     /usr/oracle/app/oradata/orcl/redo03.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo02.log        NO
         ONLINE     /usr/oracle/app/oradata/orcl/redo01.log        NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog4a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog5a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog6a.log    NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog7a.log    NO
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
------ ---------- ---------- ---------------------------------------------------------------------------------------------------- ------
         ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_3_c7np1p97_.log              YES
         ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_2_c7np1osm_.log              YES
         ONLINE     /usr/oracle/app/flash_recovery_area/DB_STANDBY/onlinelog/o1_mf_1_c7np1od8_.log              YES
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog4a.log                              NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog5a.log                              NO
         STANDBY    /usr/oracle/app/oradata/orcl/stbyredolog6a.log                              NO
         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";
                                                            #以前此处为ARCH,
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;   #因为更改成了LGWR来传递,所以需要使用实时

验证：

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

Primary>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH              DALLAS
SALES                 CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS

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

Primary>commit;

Commit complete.

standby端查看

StandBy>select * from scott.dept;
    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH               DALLAS
SALES            　　  CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS
OPERATIONS            OPERATIONS
rows selected.

10、switchover

备库暂停 redo应用

StandBy>alter database recover managed standby database cancel; #取消redo应用

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;  #将当前primary角色切换到物理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;   #standby切换到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>

常用查询

查询进程的活动状态

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

查询redo应用进度

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

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

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

查询归档历史

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

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

参看数据库的基本信息

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

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

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

检查应用模式

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

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

取值

idle

managed：

managed real_time_apply：

您可能感兴趣的文档:

--结束END--

本文标题: Duplicate复制数据库并创建物理StandBy（pfile版本）

本文链接: https://lsjlt.com/news/3558.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

关于SQL建表语句使用详解

2024-10-23

HBase在大数据审计与合规性追踪中的应用

2024-10-22

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

2024-10-22

HBase的Region Server之间的网络通信优化

2024-10-22

HBase在大数据监控与告警系统中的实时数据处理能力

2024-10-22

MySQL与HBase在大数据安全策略中的实现与对比

2024-10-22

HBase的分布式事务处理在复杂业务场景中的应用

2024-10-22

MySQL与HBase在混合存储架构中的整合策略

2024-10-22

HBase如何支持高效的二级索引查询

2024-10-22

MySQL与HBase在物联网数据收集与处理中的协作模式

2024-10-22

热门问答

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

Duplicate复制数据库并创建物理StandBy（pfile版本）

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

2、standby端创建相关目录

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

4、生成standby端的pfile

5、配置监听服务

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

7、duplicate standby

8、启用日志传送

8、验证结果

9、启用实时应用redo

10、switchover

常用查询

Duplicate复制数据库并创建物理StandBy（pfile版本）

Duplicate复制数据库并创建物理StandBy(spfile+不同实例名+不同路径)

创建mysq数据库，并创建管理用户

【12c】DataGuard物理备用数据库（DUPLICATE方式）搭建详细教程

如何创建 PHP 函数库并使其支持不同的 PHP 版本？

Oracle11g Data Guard物理备用数据库搭建与配置(第1部分主数据库实例创建)

windows 批处理bat连接本地mysql 创建制定数据并执行sql文件

第77讲：二进制方式搭建MySQL数据库5.7版本以及错误日志管理

SQLServer 错误 1406 无法安全地强制执行服务。请删除数据库镜像并恢复数据库 “%.*ls” 以获得访问权。故障处理修复支持远程

SQLServer 错误 41350 警告：在为加密启用的数据库中创建了具有持续性 SCHEMA_AND_DATA 的内存优化表。不会对内存优化表中的数据加密。故障处理修复支持远程

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式

Duplicate复制数据库并创建物理StandBy（pfile版本）

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

2、standby端创建相关目录

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

4、生成standby端的pfile

5、配置监听服务

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

7、duplicate standby

8、启用日志传送

8、验证结果

9、启用实时应用redo

10、switchover

常用查询

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