Oracle11g Data Guard物理備用數(shù)據(jù)庫搭建與配置(第2部分 配置物理備用數(shù)據(jù)庫)

環(huán)境準(zhǔn)備

接著上一篇文章  Oracle11g Data Guard物理備用數(shù)據(jù)庫搭建與配置(第1部分 主數(shù)據(jù)庫實(shí)例創(chuàng)建)

在我的測試環(huán)境中，我準(zhǔn)備了兩臺(tái)CentOS7.4虛擬機(jī)，并同時(shí)都安裝了Oracle11gR2的11.2.0.1.0企業(yè)版的數(shù)據(jù)庫軟件，其中只有主服務(wù)器創(chuàng)建一個(gè)數(shù)據(jù)庫實(shí)例，備用服務(wù)器僅安裝Oracle數(shù)據(jù)庫軟件。

主數(shù)據(jù)庫:

OS: CentOS7.4

Hostname:     hmdb11dg-db1

Oracle Version:  11.2.0.1.0

Oracle SID:     HMDG(使用DBCA工具創(chuàng)建的一個(gè)數(shù)據(jù)庫)

備數(shù)據(jù)庫:

OS: CentOS7.4

Hostname:     hmdb11dg-db2

Oracle Version:  11.2.0.1.0

Oracle SID:    HMDG2

注意: 在開始之前，備用服務(wù)上還沒有將要與主服務(wù)器同步備份的數(shù)據(jù)庫實(shí)例。

一、主服務(wù)器前提配置

1. 啟用歸檔日志

檢查主數(shù)據(jù)是否處于歸檔日志模式

SQL> select log_mode from v$database;
 
LOG_MODE
------------
ARCHIVELOG

如果是NOARCHIVELOG模式，則將其修改為ARCHIVELOG模式

SQL> SHUTDOWN IMMEDIATE; 
SQL> STARTUP MOUNT; 
SQL> ALTER DATABASE ARCHIVELOG; 
SQL> ALTER DATABASE OPEN;

2. 啟用強(qiáng)制日志

SQL> ALTER DATABASE FORCE LOGGING;
 
Database altered.
 
SQL> SELECT NAME,FORCE_LOGGING FROM V$DATABASE;
 
NAME   FOR
--------- ---
HMDG   YES

3. 創(chuàng)建備用重做日志

備用重做日志文件的大小要與當(dāng)前主數(shù)據(jù)庫的在線重做日志文件大小完全匹配。

確定備用重做日志文件組的數(shù)量，建議的數(shù)量：(每個(gè)線程最大的日志數(shù) + 1) * 最大線程數(shù)

查看聯(lián)機(jī)重做日志

SQL> set pagesize 100
SQL> set linesize 200
SQL> col GROUP#  format 99
SQL> col STATUS format a10
SQL> col TYPE format a10
SQL> col MEMBER format a50
SQL> col IS_RECOVERY_DEST_FILE format a10
 
SQL> select * from v$logfile;
 
GROUP# STATUS   TYPE      MEMBER      IS_RECOVER
------ ---------- ---------- -------------------------------------------------- ----------
     1    ONLINE     /u01/app/oracle/oradata/HMDG/redo01.log  NO
     2    ONLINE     /u01/app/oracle/oradata/HMDG/redo02.log  NO
     3    ONLINE     /u01/app/oracle/oradata/HMDG/redo03.log  NO
     4    ONLINE     /u01/app/oracle/oradata/HMDG/redo04.log  NO

這里將創(chuàng)建10組備用重做日志

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/HMDG/redo05.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/HMDG/redo06.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/HMDG/redo07.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/HMDG/redo08.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '/u01/app/oracle/oradata/HMDG/redo09.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '/u01/app/oracle/oradata/HMDG/redo10.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/HMDG/redo11.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/HMDG/redo12.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/HMDG/redo13.log' SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/HMDG/redo14.log' SIZE 500M;

查看備用重做日志

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
 
GROUP#   THREAD#  SEQUENCE# ARC STATUS
------ ---------- ---------- --- ----------
     5  0    0 YES UNASSIGNED
     6  0    0 YES UNASSIGNED
     7  0    0 YES UNASSIGNED
     8  0    0 YES UNASSIGNED
     9  0    0 YES UNASSIGNED
    10  0    0 YES UNASSIGNED
    11  0    0 YES UNASSIGNED
    12  0    0 YES UNASSIGNED
    13  0    0 YES UNASSIGNED
    14  0    0 YES UNASSIGNED
 
10 rows selected.

4. 開啟閃回日志

SQL> alter database flashback on;
SQL> select flashback_on from v$database;
 
FLASHBACK_ON
------------------
YES

二、監(jiān)聽與TNS配置

1. 監(jiān)聽配置

主備數(shù)據(jù)庫必須注冊(cè)靜態(tài)監(jiān)聽服務(wù)(listener.ora)

$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db1)(PORT = 1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = HMDG.DB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = HMDG)
    )
  )
 
ADR_BASE_LISTENER = /u01/app/oracle
 
 
#備數(shù)據(jù)庫
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db2)(PORT = 1521))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = HMDG2.DB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = HMDG2)
    )
  )
 
ADR_BASE_LISTENER = /u01/app/oracle

2. 主備數(shù)據(jù)庫TNS別名連接信息配置

修改兩臺(tái)服務(wù)器上的$ORACLE_HOME/network/admin/tnsnames.ora配置文件，主備使用相同的配置

HMDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HMDG.DB)
    )
  )
 
HMDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hmdb11dg-db2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HMDG2.DB)
    )
  )

3. 測試使用TNS別名連接數(shù)據(jù)庫

[oracle@hmdb11dg-db1 ~]$ sqlplus system/oracle@HMDG
 
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 20:47:28 2018
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>

三、主數(shù)據(jù)庫初始化參數(shù)配置

1.  創(chuàng)建PFILE參數(shù)文件

使用如下語句創(chuàng)建PFILE配置文件，該文件自動(dòng)生成在/u01/app/oracle/product/11.2.0/db_1/dbs目錄下

SQL> CREATE PFILE FROM SPFILE;

2. 修改PFILE配置

使用剛剛生成的PFILE文件，修改如下配置

HMDG.__db_cache_size=1023410176
HMDG.__java_pool_size=16777216
HMDG.__large_pool_size=16777216
HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
HMDG.__pga_aggregate_target=1325400064
HMDG.__sga_target=1962934272
HMDG.__shared_io_pool_size=0
HMDG.__shared_pool_size=872415232
HMDG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/HMDG/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/HMDG/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='HMDG'
 
*.db_unique_name='HMDG'
*.log_archive_config='DG_CONFIG=(HMDG,HMDG2)'
 
LOG_ARCHIVE_DEST_1=
    'LOCATION=/u01/app/oracle/oradata/HMDG/
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
     DB_UNIQUE_NAME=HMDG'
 
LOG_ARCHIVE_DEST_2=
    'SERVICE=HMDG2 ASYNC
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
     DB_UNIQUE_NAME=HMDG2'
 
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=30
 
FAL_SERVER=HMDG2
FAL_CLIENT=HMDG
DB_FILE_NAME_CONVERT='HMDG2','HMDG'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG2/','/u01/app/oracle/oradata/HMDG/'
STANDBY_FILE_MANAGEMENT=AUTO
 
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HMDGXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=3277848576
*.open_cursors=300
*.processes=5000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=5505
*.undo_tablespace='UNDOTBS1'

3. 使用剛剛創(chuàng)建的PFILE文件重新啟動(dòng)數(shù)據(jù)庫，并且重新創(chuàng)建一個(gè)SPFILE文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora';
ORACLE instance started.
 
Total System Global Area 3273641984 bytes
Fixed Size      2217792 bytes
Variable Size   2315258048 bytes
Database Buffers   939524096 bytes
Redo Buffers     16642048 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG.ora';   #創(chuàng)建SPFILE文件
 
File created.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 3273641984 bytes
Fixed Size      2217792 bytes
Variable Size   2315258048 bytes
Database Buffers   939524096 bytes
Redo Buffers     16642048 bytes
Database mounted.
Database opened.

注意：一旦由于PFILE參數(shù)配置信息錯(cuò)誤或者使用PFILE文件啟動(dòng)報(bào)錯(cuò)需要修改PFILE文件參數(shù)的時(shí)候，都必須要重新創(chuàng)建SPFILE文件。(主備數(shù)據(jù)庫都是一樣)

4. 備份數(shù)據(jù)庫(在這里是非必須的，因?yàn)樵诒敬尾僮髦形沂褂玫氖荝MAN復(fù)制到備用數(shù)據(jù)庫)

[oracle@hmdb11dg-db1 dbs]$ rman target = /
 
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 22 21:12:06 2018
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: HMDG (DBID=787192145)
 
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

5. 創(chuàng)建備用數(shù)據(jù)庫控制文件

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/HMDG2.ctl';

6. 創(chuàng)建備用數(shù)據(jù)庫的PFILE文件

SQL> CREATE PFILE='/tmp/initHMDG2.ora' FROM SPFILE;

7. 創(chuàng)建備用數(shù)據(jù)的密碼文件

[oracle@hmdb11dg-db1 ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG /tmp/orapwHMDG2

四、備用數(shù)據(jù)庫服務(wù)器設(shè)置

1. 備用數(shù)據(jù)庫上創(chuàng)建必要的數(shù)據(jù)庫文件目錄

$ mkdir /u01/app/oracle/admin
$ mkdir /u01/app/oracle/admin/HMDG2
$ mkdir /u01/app/oracle/admin/HMDG2/{adump,dpdump,pfile,scripts}
$ mkdir -p /u01/app/oracle/oradata/HMDG2
$ mkdir -p /u01/app/oracle/flash_recovery_area/HMDG2

2. 從主數(shù)據(jù)庫上拷貝配置文件到備用數(shù)據(jù)庫(備用服務(wù)器上操作)

將控制文件、參數(shù)文件和密碼文件從主服務(wù)器上復(fù)制到備用服務(wù)器上

$ scp oracle@hmdb11dg-db1:/tmp/HMDG2.ctl /u01/app/oracle/oradata/HMDG2/control01.ctl
$ cp /u01/app/oracle/oradata/HMDG2/control01.ctl /u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl 
$ scp oracle@hmdb11dg-db1:/tmp/orapwHMDG2 /u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2
$ scp oracle@hmdb11dg-db1:/tmp/initHMDG2.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora

3. 修改備用數(shù)據(jù)庫初始化參數(shù)

修改備用服務(wù)器的PFILE文件$ORACLE_HOME/dbs/initHMDG2.ora

HMDG.__db_cache_size=939524096
HMDG.__java_pool_size=16777216
HMDG.__large_pool_size=16777216
HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
HMDG.__pga_aggregate_target=1325400064
HMDG.__sga_target=1962934272
HMDG.__shared_io_pool_size=0
HMDG.__shared_pool_size=956301312
HMDG.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/HMDG2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/HMDG2/control01.ctl','/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.DB_FILE_NAME_CONVERT='HMDG','HMDG2'
*.db_name='HMDG'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.db_unique_name='HMDG2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HMDGXDB)'
*.FAL_CLIENT='HMDG2'
*.FAL_SERVER='HMDG'
*.log_archive_config='DG_CONFIG=(HMDG,HMDG2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/HMDG2/
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
     DB_UNIQUE_NAME=HMDG2'
*.LOG_ARCHIVE_DEST_2='SERVICE=HMDG ASYNC
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
     DB_UNIQUE_NAME=HMDG'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG/','/u01/app/oracle/oradata/HMDG2/'
*.memory_target=3277848576
*.open_cursors=300
*.processes=5000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=5505
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

4. 使用剛剛創(chuàng)建的PFILE參數(shù)文件啟動(dòng)備用數(shù)據(jù)庫

[oracle@hmdb11dg-db2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 22 21:42:24 2018
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora';
ORACLE instance started.
 
Total System Global Area 3273641984 bytes
Fixed Size      2217792 bytes
Variable Size   2197817536 bytes
Database Buffers  1056964608 bytes
Redo Buffers     16642048 bytes

5. 創(chuàng)建SPFILE文件

SQL> CREATE SPFILE FROM PFILE;

File created.

注意：一旦由于PFILE參數(shù)配置信息錯(cuò)誤或者使用PFILE文件啟動(dòng)報(bào)錯(cuò)需要修改PFILE文件參數(shù)的時(shí)候，都必須要重新使用該語句創(chuàng)建SPFILE文件。(主備數(shù)據(jù)庫都是一樣)

五、在備用數(shù)據(jù)庫上使用rman恢復(fù)數(shù)據(jù)到備用數(shù)據(jù)庫(DUPLICATE)

1. 建立連接

[oracle@hmdb11dg-db2 ~]$ rman TARGET sys/oracle@HMDG AUXILIARY sys/oracle@HMDG2
 
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 22 21:46:42 2018
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: HMDG (DBID=787192145)
connected to auxiliary database: HMDG (not mounted)

2. 將主數(shù)據(jù)庫復(fù)制到備用數(shù)據(jù)庫

 在RMAN模式下，發(fā)出以下語句將主數(shù)據(jù)庫復(fù)制到備數(shù)據(jù)庫

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

[oracle@hmdb11dg-db2 dbs]$ rman TARGET sys/oracle@HMDG AUXILIARY sys/oracle@HMDG2

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 23 09:50:19 2018

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

connected to target database: HMDG (DBID=787214401)

connected to auxiliary database: HMDG (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

Starting Duplicate Db at 23-MAR-18

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=4708 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG' auxiliary format

 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2'   ;

}

executing Memory Script

Starting backup at 23-MAR-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3773 device type=DISK

Finished backup at 23-MAR-18

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/HMDG2/control01.ctl';

   restore clone controlfile to  '/u01/app/oracle/flash_recovery_area/HMDG2/control02.ctl' from

 '/u01/app/oracle/oradata/HMDG2/control01.ctl';

}

executing Memory Script

Starting backup at 23-MAR-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_HMDG.f tag=TAG20180323T095223 RECID=3 STAMP=971517146

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

Finished backup at 23-MAR-18

Starting restore at 23-MAR-18

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 23-MAR-18

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

 "/u01/app/oracle/oradata/HMDG2/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/HMDG2/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/HMDG2/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/HMDG2/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/HMDG2/users01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/HMDG2/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/HMDG2/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/HMDG2/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/HMDG2/users01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HMDG2/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-MAR-18

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/HMDG/undotbs01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf tag=TAG20180323T095246

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/HMDG/system01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/system01.dbf tag=TAG20180323T095246

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/HMDG/sysaux01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf tag=TAG20180323T095246

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/HMDG/users01.dbf

output file name=/u01/app/oracle/oradata/HMDG2/users01.dbf tag=TAG20180323T095246

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

Finished backup at 23-MAR-18

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=3 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=971517318 file name=/u01/app/oracle/oradata/HMDG2/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=971517319 file name=/u01/app/oracle/oradata/HMDG2/users01.dbf

Finished Duplicate Db at 23-MAR-18

如果復(fù)制過程中沒有報(bào)錯(cuò)，就可以立即開啟日志重做應(yīng)用

3. 開啟日志重做應(yīng)用

 發(fā)出如下命令指示備庫開始使用備用日志進(jìn)行在備庫上同步數(shù)據(jù)(恢復(fù)數(shù)據(jù))

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
#或者
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
 
#取消申請(qǐng)重做(該命令用于停止恢復(fù))
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4. 測試備用數(shù)據(jù)的存檔操作

默認(rèn)情況下，當(dāng)在線重做日志文件變滿時(shí)，會(huì)發(fā)送日志切換。要強(qiáng)制進(jìn)行日志切換以便立即傳輸重做數(shù)據(jù)，則在主數(shù)據(jù)庫上使用如下語句強(qiáng)制日志切換

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

5. 在備用數(shù)據(jù)庫上查詢現(xiàn)有的歸檔重做日志文件

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 
 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
19 23-MAR-18 23-MAR-18
20 23-MAR-18 23-MAR-18
21 23-MAR-18 23-MAR-18
22 23-MAR-18 23-MAR-18

6. 再次在數(shù)據(jù)庫上強(qiáng)制日志切換

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

7. 驗(yàn)證在備用數(shù)據(jù)庫中是否接收了新的重做日志

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 
 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
19 23-MAR-18 23-MAR-18
20 23-MAR-18 23-MAR-18
21 23-MAR-18 23-MAR-18
22 23-MAR-18 23-MAR-18
23 23-MAR-18 23-MAR-18

8. 驗(yàn)證備用數(shù)據(jù)庫是否應(yīng)用了新的重做日志

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 
 SEQUENCE# APPLIED
---------- ---------
19 YES
20 YES
21 YES
22 YES
23 YES

9. 查詢主備狀態(tài)

#在當(dāng)前主數(shù)據(jù)庫中查詢
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
 
#在當(dāng)前備用數(shù)據(jù)庫中查詢
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

此時(shí)，說明當(dāng)前主備數(shù)據(jù)庫狀態(tài)都為正常。其中主數(shù)據(jù)狀態(tài)TO STANDBY說明可以隨時(shí)切換為備用數(shù)據(jù)庫。

五、手動(dòng)切換測試(主備切換)

在主數(shù)據(jù)庫(DB1)上操作

在當(dāng)前主數(shù)據(jù)庫中查詢主備狀態(tài)

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY

當(dāng)主數(shù)據(jù)庫的狀態(tài)為TO STANDBY時(shí)，表示可以切換到備用數(shù)據(jù)庫

在當(dāng)前主數(shù)據(jù)庫(DB1)上發(fā)出切換到備數(shù)據(jù)庫角色申請(qǐng)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
 
Database altered.

在備數(shù)據(jù)庫(DB2)上操作

此時(shí)查看備數(shù)據(jù)庫(DB2)的狀態(tài)

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY

當(dāng)此時(shí)備數(shù)據(jù)庫的狀態(tài)為TO PRIMARY時(shí)，表示可以切換到主數(shù)據(jù)庫角色

在當(dāng)前備數(shù)據(jù)庫(DB2)上發(fā)出切換到主數(shù)據(jù)庫角色申請(qǐng)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
 
Database altered.

繼續(xù)在備數(shù)據(jù)(DB2)上完成以下指令

SQL> ALTER DATABASE OPEN;
-- 或者
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

在原來的主數(shù)據(jù)(DB1)上繼續(xù)執(zhí)行以下命令

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
 
Total System Global Area 3273641984 bytes
Fixed Size      2217792 bytes
Variable Size   2264926400 bytes
Database Buffers   989855744 bytes
Redo Buffers     16642048 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
 
Database altered.

發(fā)出如下命令讓當(dāng)前備庫(DB1)開始使用備用日志進(jìn)行同步數(shù)據(jù)(恢復(fù)數(shù)據(jù))

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
 
Database altered.

最后查看切換以后的主備狀態(tài)與主備角色

-- DB2上
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
 
SQL> select database_role from v$database;
 
DATABASE_ROLE
----------------
PHYSICAL STANDBY

-- DB1上
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
 
SQL> select database_role from v$database;
 
DATABASE_ROLE
----------------
PRIMARY

以上狀態(tài)說明主備角色已經(jīng)正常切換

注意：在正常切換后，主數(shù)據(jù)庫狀態(tài)可能需要等待幾分鐘時(shí)間才顯示為TO STANDBY狀態(tài)，這是因?yàn)橹鲾?shù)據(jù)在執(zhí)行日志切換，需要等待切換完成

在當(dāng)前主數(shù)據(jù)庫(DB2)上強(qiáng)制日志切換

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

至此主備切換測試完成