通過RMAN Duplicate建立Oracle物理standby備庫

關於Oracle10g Data Gurad的搭建，雖然官方和網上有很多資料，但是基於物理冷備份後複製到備庫的搭建方式。在生產環境中我們可能更多要關注主庫的可用性，這是Rman的高階備份恢復功能完全勝任這些需求。

主庫伺服器名：dbserv ip:192.168.2.3 主庫DB_UNIQUE_NAME='testdb'

備庫伺服器名：dbservdg ip:192.168.2.4 備庫db_unique_name='testdbDG'

資料庫名：testdb

DG安裝過成大致分為如下幾個重要步驟：

1、備庫安裝資料庫軟體（僅安裝軟體，不必建立資料庫）

2、主庫通過Rman進行全備份,備份指令碼如下：

run{

DELETE NOPROMPT expired archivelog ALL;

allocate channel d1 TYPE disk maxpiecesize=30G;

allocate channel d2 TYPE disk maxpiecesize=30G;

backup DATABASE include CURRENT controlfile for standby format '/backup/rman_full/data_%d_%T_%s_%p' plus archivelog format '/backup/rman_full/arch_%d_%T_%s' DELETE ALL INPUT;

release channel d1;

release channel d2;

crosscheck backup;

DELETE noprompt obsolete REDUNDANCY 2;

}

[@more@]

3、將主庫Rman全備份的遷移至備庫伺服器對應的目錄（在備庫執行）。

scp oracle@192.168.2.3:/backup/rman_full/* /backup/rman_full/

為了Rman恢復順利進行，主庫將全備份最好傳輸到相同目錄結構。加入主庫儲存目錄為/backup/rman_full，則在備庫也要建立目錄相同的目錄並給於相應許可權。

如果無法建立相同目錄，則可以通過建立OS連線命令來處理，如下假如將備份傳到備庫/backup/rman/full目錄的具體配置過程：

在備庫：

mkdir –p /backup/rman_full

chown –R oracle:oinstall /backup/rman_full

ln –s /backup/rman/full/* /backup/rman_full

4、確認主庫處於歸檔模式，如果不是改為歸檔模式

SQL> archive log list

5、 設定主庫強制logging

SQL> alter database force logging;

6、配置主備庫監聽和TNS配置檔案

主庫監聽和TNS配置：

[oracle@dbserv admin]$ pwd

/opt/oracle/product/10.2.0/db_1/network/admin

[oracle@dbserv admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBANAME = testdb)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = testdb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

[oracle@dbserv admin]$

[oracle@dbserv admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

testdb =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

)

)

testdb.STD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

[oracle@dbserv admin]$

備庫監聽和TNS配置:

[oracle@dbservdg admin]$ pwd

/opt/oracle/product/10.2.0/db_1/network/admin

[oracle@dbservdg admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBANAME = testdb)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = testdb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

[oracle@dbservdg admin]$

[oracle@dbservdg admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

testdb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = testdb)

)

)

testdb.STD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = testdb)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

[oracle@dbservdg admin]$

7、 複製密碼檔案到Standby端

scp oracle@192.168.2.3:/opt/oracle/product/10.2.0/db_1/dbs/orapwtestdb /opt/oracle/product/10.2.0/db_1/dbs/orapwtestdb

8、 在Standby端建立目錄結構

su – root

mkdir -p /opt/oracle/admin/testdb/adump

mkdir -p /opt/oracle/admin/testdb/bdump

mkdir -p /opt/oracle/admin/testdb/cdump

mkdir -p /opt/oracle/admin/testdb/udump

mkdir -p /oradata/testdb/

mkdir–p /bakup/archive/

chown –R oracle:oinstall /opt/oracle/admin/testdb/

chown –R oracle:oinstall /oradata/testdb/

chown –R oracle:oinstall /bakup/archive/

9、 手工建立並配置備庫初始化引數檔案

主庫初始化檔案的匯出並修改，以便用於備庫初始化個檔案：

[oracle@dbserv ~]$ cd /opt/oracle/product/10.2.0/db_1/dbs/

[oracle@dbserv dbs]$ ls

alert_testdb.log hc_testdb.dat initdw.ora inittestdb.ora0717.bak orapwtestdb spfiletestdb.ora

init.ora inittestdb.ora lktestdb snapcf_testdb.f spfiletestdb_bak.ora

[oracle@dbserv dbs]$ sqlplus / as sysdba

SQL> create pfile='/tmp/inittestdb.ora' from spfile;

File created.

scp oracle@192.168.2.3:/tmp/inittestdb.ora /opt/oracle/product/10.2.0/db_1/dbs/

修改從主庫傳過來的初始化檔案，將其符合備庫初始化檔案的要求：

[oracle@dbservdg dbs] cd /opt/oracle/product/10.2.0/db_1/dbs/

[oracle@dbservdg dbs]$ vi inittestdb.ora

testdb.__db_cache_size=2952790016

testdb.__java_pool_size=50331648

testdb.__large_pool_size=16777216

testdb.__shared_pool_size=704643072

testdb.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/testdb/adump'

*.background_dump_dest='/opt/oracle/admin/testdb/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/oradata/testdb/control01.ctl','/oradata/testdb/control02.ctl','/oradata/testdb/control03.ctl'#Restore Controlfile

*.core_dump_dest='/opt/oracle/admin/testdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/oradata/testdb','/oradata/testdb'

*.db_name='testdb'

*.db_recovery_file_dest='/bakup/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='testdbDG'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.fal_client='testdb.STD'

*.fal_server='testdb'

*.job_queue_processes=10

*.log_archive_config='dg_config=(testdb,testdbDG)'

*.log_archive_dest_1='location=/bakup/archive LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdbDG'

*.log_archive_dest_2='SERVICE=testdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive'

*.open_cursors=300

*.pga_aggregate_target=1244659712

*.processes=600

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=665

*.sga_target=3735027712

*.standby_archive_dest='/bakup/archive'

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/oracle/admin/testdb/udump'

sqlplus / as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

10、 使用rman duplicate 恢復standby資料庫

(在備庫上執行)

rman target sys/abc123@testdb auxiliary /

rman> duplicate target database for standby;

11、如果要實現real time apply 需要在備庫建立STANDBY LOGFILE,否則可以忽略此步驟

在單節點主庫環境下，如果主庫有四組日誌檔案組，因此備庫應新增（4+1）*1=5組standby日誌檔案組。

TANDBY LOGFILE的大小必須（不能小於）等於主庫日誌檔案大小。

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11

'/oradata/testdb/standby_redo01.log' size 524288000;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12

'/oradata/testdb/standby_redo02.log' size 524288000;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13

'/oradata/testdb/standby_redo03.log' size 524288000;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14

'/oradata/testdb/standby_redo04.log' size 524288000;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15

'/oradata/testdb/standby_redo05.log' size 524288000;

SQL> SELECT * FROM V$STANDBY_LOG;

12、 啟動Standby

SQL>ALTER DATABASE recover managed standby database disconnect from session;

如果用real time apply則（可選）

SQL>ALTER DATABASE recover managed standby database using current logfile disconnect;

SQL>alter system set log_archive_dest_2='SERVICE=testdb.STD LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG';

13、配置Primary，通過spfile檔案動態修改方法可以保證主庫可用性並業務不受影響

SQL>alter system set log_archive_config='DG_CONFIG=(testdb,testdbDG)';

SQL>alter system set log_archive_dest_1='location=/bakup/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdb';

SQL>alter system set log_archive_dest_2='SERVICE=testdb.STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG';

SQL>alter system set db_file_name_convert=''/oradata/testdb','/oradata/testdb' ;

SQL>alter system set log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive';

SQL>alter system set standby_file_management=auto;

SQL>alter system set fal_server='testdb.STD';

SQL>alter system set fal_client='testdb';

SQL>alter system set log_archive_dest_state_1='ENABLE';

SQL>alter system set log_archive_dest_state_2='ENABLE';

檢視主庫完整初始化檔案：

[oracle@dbserv dbs]$ sqlplus / as sysdba

SQL> create pfile='/tmp/inittestdb.ora' from spfile;

File created.

[oracle@dbserv ~]$ cd /tmp

[oracle@dbserv tmp]$ more inittestdb.ora

testdb.__db_cache_size=6492782592

testdb.__java_pool_size=16777216

testdb.__large_pool_size=16777216

testdb.__shared_pool_size=973078528

testdb.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/testdb/adump'

*.background_dump_dest='/opt/oracle/admin/testdb/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/oradata/testdb/control01.ctl','/oradata/testdb/control02.ctl','/oradata/testdb/control03.ctl'

*.core_dump_dest='/opt/oracle/admin/testdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/oradata/testdb','/oradata/testdb'

*.db_name='testdb'

*.db_recovery_file_dest='/bakup/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.DB_UNIQUE_NAME='testdb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.FAL_CLIENT='testdb'

*.FAL_SERVER='testdb.STD'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(testdb,testdbDG)'

*.log_archive_dest_1='location=/bakup/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=testdb'

*.log_archive_dest_2='SERVICE=testdb.STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbDG'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_file_name_convert='/oradata/testdb','/oradata/testdb','/bakup/archive','/bakup/archive'

*.open_cursors=1000

*.pga_aggregate_target=2503999488

*.processes=600

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=665

*.sga_target=7514095616

*.standby_archive_dest='/bakup/archive'

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/oracle/admin/testdb/udump'

[oracle@dbserv tmp]$

14、檢查備庫狀態

SQL>SELECT DATABASE_ROLE, PROTECTION_MODE, SWITCHOVER_STATUS, FLASHBACK_ON FROM v$database;

SQL>SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

SQL>SELECT DEST_ID,ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

SQL>Select max(SEQUENCE#) from v$archived_log al,v$database db

2>where al.RESETLOGS_CHANGE#=db.RESETLOGS_CHANGE#

2>and al.APPLIED='YES';

15、驗證

Standby端：

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

Primary端：

SQL>alter database set standby database to maximize AVAILABILITY;

SQL>conn system/onewave

SQL>create table t tablespace users as select * from dba_objects;

SQL>alter system archive log current;

Standby端：

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

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE OPEN;

select count(*) from system.t;

表中有記錄，說明備庫已應用日誌。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18841027/viewspace-1059101/，如需轉載，請註明出處，否則將追究法律責任。