Duplicate複製資料庫並創建物理StandBy（pfile版本）

来源:https://www.cnblogs.com/vmsysjack/archive/2020/01/01/12130366.html
-Advertisement-
Play Games

1設定環境如下： Primary資料庫 IP 172.17.22.16 SID orcl Standby資料庫 IP 172.17.22.17 SID orcl 設置提示，以區分操作的位置 primary資料庫 set SQLPROMPT Primary> standby資料庫 set SQLPRO ...


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
[email protected]'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.
[email protected]'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>

常用查詢

 

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：


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Arcgis runtime sdk .net 二次開發
    前段時間研究了下 arcgis runtime sdk .net 二次開發··這裡做個筆記 runtime版本為100.6 基於WPF 開發 命名空間引入 基本只用到了mapview這個控制項和相關的對象，下邊把預設的背景網格去了，改了背景色,mapview的map對象是依賴屬，性支持綁定的，可直接綁 ...
  • .NetCore3.0短網址項目
    Wei.TinyUrl 基於.NetCore3.0 + Mysql開發的短網址項目 項目地址：https://github.com/a34546/Wei.TinyUrl 演示效果： 快速開始 1. 修改連接字元串 appsettings.Development.json中ConnectionStri ...
  • 從上到下列印二叉樹，同節點的從左到右。
    public class TreeNode(){ public int val; publicTreeNode Left; public TreeNode Right; public TreeNode(int x){ val=x } } 局部變數temp獲取當前結點，queue獲取此結點下的左右結點 ...
  • 定時任務調度自動提醒企業微信工具
    採用C#開發的WPF應用，基於.net frameWork4.5.2, 先配置企業微信應用、自定義資料庫列表， 再新增定時需要提醒的計劃任務，自動將定製的信息發送至企業微信。 支持主流MySql/SqlServer/Oracle資料庫 下載鏈接:https://download.csdn.net/d ...
  • FTP伺服器的搭建
    FTP Server 作用：提供文件共用服務 一、FTP基礎 FTP: 文件傳輸協議軟體包: vsftpdFTP埠: 控制埠 command 21/tcp 數據埠 data 20/tcp (主動模式)配置文件: /etc/vsftpd/vsftpd.conf 二、vsftpd預設配置 FTP ...
  • 【E20200101-1】Centos 7.x 關閉防火牆（firewall）和SELinux
    ## 一、準備工作### 1.1、伺服器準備操作系統：centos 7.x### 1.2、安裝好用的文本編輯工具nano```linux# yum -y install nano```## 二、關閉SELinux### 2.1、查看SELinux狀態#### 2.1.1、第一種：查看SELinux狀... ...
  • windows定時重啟
    先準備好腳本restart.bat 新建一個txt，寫入shutdown shutdown -s -t 10 十秒後重啟，更改尾碼為.bat批處理文件，切記編輯好尾碼千萬不要直接點開，否則就會直接調用然後重啟，一定要註意這個，所以這個文件的路徑最好放的深一點 如果不是測試，點開就會重啟，可以右鍵編輯 ...
  • Chocolaty
    原文是用markdown格式寫的，稍微改了下發了博客，格式可能會很奇怪。。 Chocolaty官網 Chocolaty是一款Windows平臺的包管理工具，類似於centos的yum或ubuntu的apt。通過Chocolaty，可以實現軟體，尤其是各式開源軟體的一鍵安裝與一鍵升級。 安裝Choco ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類