-
準備工作
- Primary Server IP:192.168.217.9
- Standby Server IP:192.168.217.23
- Primary Net Service Name: primary
- Standby Net Service Name: standby
- Primary ORACLE_SID=primary
- Standby ORACLE_SID=standby
- ORACLE_BASE=/opt/ora9
- ORACLE_HOME=/opt/ora9/product/9.2
- Primary 已經存在一個資料庫，Standby只要灌好Oracle軟體，不需要建立資料庫。
- Primary DataBase已經開啟於Archive Log模式
- Primary DB、Standby DB位於兩台不同的機器上
- Primary DB與Standby DB的目錄結構都相同
- (primary)開啟Force Logging模式
SQL> SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 17:51:14 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter database force logging;
- (primary)造出最新的pfile
SQL> create pfile from spfile;
- (primary)修改初始化參數($ORACLE_HOME\dbs\initprimary.ora)
*.INSTANCE_NAME='primary'
*.DB_NAME='primary'
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '-->(Maximum Performance 模式)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
*.REMOTE_ARCHIVE_ENABLE=TRUE*.FAL_SERVER=standby
*.FAL_CLIENT=primary
*. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
*.STANDBY_FILE_MANAGEMENT=AUTO
*._log_archive_callout='LOCAL_FIRST=TRUE'-->避免尖峰時間,switch log太快，系統hang住
*.archive_lag_target=1800
- (primary)複製參數檔密碼檔至Standby DB
$ scp $ORACLE_HOME/dbs/initprimary.ora oracle@192.168.217.23:$ORACLE_HOME/dbs/initstandby.ora
$ scp $ORACLE_HOME/dbs/orapwprimary oracle@192.168.217.23:$ORACLE_HOME/dbs/orapwstandby
- (Standby)修改 Standby DB 之參數檔($ORACLE_HOME\dbs\initstandby.ora)
*.INSTANCE_NAME='standby'
*.DB_NAME='primary' -->需要跟primary db相同
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '-->(Maximum Performance 模式)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=DEFER -->暫時關閉，等Switchover時才開啟。
*.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
*.REMOTE_ARCHIVE_ENABLE=TRUE*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log
*.STANDBY_FILE_MANAGEMENT=AUTO
*._log_archive_callout='LOCAL_FIRST=TRUE'
*.archive_lag_target=1800
- (Standby)創建Standby DB上的相關目錄
#su - oracle
$ mkdir -p $ORACLE_BASE/backup -->用於 Rman備份
$ mkdir -p $ORACLE_BASE/oradata/primary -->資料庫目錄
$ mkdir -p $ORACLE_BASE/oradata/archive_log
$ mkdir -p $ORACLE_BASE/oradata/standby_archive_log
$ mkdir -p $ORACLE_BASE/admin/primary/bdump
$ mkdir -p $ORACLE_BASE/admin/primary/cdump
$ mkdir -p $ORACLE_BASE/admin/primary/create
$ mkdir -p $ORACLE_BASE/admin/primary/pfile
$ mkdir -p $ORACLE_BASE/admin/primary/udump
- (Primary)修改$ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
( SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora9/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /opt/ora9/product/9.2)
(SID_NAME = primary)
)
)
- (Standby)修改$ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
)
)
)SID_LIST_LISTENER =
( SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora9/product/9.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /opt/ora9/product/9.2)
(SID_NAME = standby)
)
)
- (Standby&Primary)修改$ORACLE_HOME/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME = 2
- (Primary)修改$ORACLE_HOME/network/admin/tnsnames.ora
MASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master)
)
)STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
- (Standby)修改$ORACLE_HOME/network/admin/tnsnames.ora
MASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master)
)
)STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)
- (Primary)(Standby)若有修改listner.ora 需重新啟動listener
#su - oracle
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
- (Standby)由pfile創出spfile，並且開啟Standby DB於nomount模式
[oracle@standby dbs]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 23:41:33 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;File created.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL> exit
- (Primary)使用Rman備份Primary資料庫,準備Duplicate至Standby DB
[oracle@primary ora9]$ rman target / nocatalog
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: MASTER (DBID=2710930473)
using target database controlfile instead of recovery catalogRMAN> backup filesperset 5 database format '/opt/ora9/backup/%d_t%t_s%s_p%p' include current controlfile for standby;
Starting backup at 02-4月 -08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/opt/ora9/oradata/primary/DBS3.dbf
input datafile fno=00006 name=/opt/ora9/oradata/primary/indx01.dbf
input datafile fno=00009 name=/opt/ora9/oradata/primary/users01.dbf
input datafile fno=00004 name=/opt/ora9/oradata/primary/drsys01.dbf
input datafile fno=00008 name=/opt/ora9/oradata/primary/tools01.dbf
...... including current SPFILE in backupset
including standby controlfile in backupset
......RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current
RMAN> backup archivelog all ;
Starting backup at 02-4月 -08
current log archived
using channel ORA_DISK_1
.....
- (Primary)copy 剛剛的備份至Standby DB
$ scp $ORACLE_BASE/backup/* oracle@192.168.217.23:/$ORACLE_BASE/backup
- (Primary)Duplicate 剛剛的備份至Standby DB
RMAN> connect auxiliary sys@standby;
auxiliary database Password:
connected to auxiliary database: master (not mounted)RMAN> duplicate target database for standby nofilenamecheck dorecover;
Starting Duplicate Db at 02-4月 -08
using channel ORA_AUX_DISK_1printing stored script. Memory Script
{
restore clone standby controlfile to clone_cf; -->恢復Standby control file
replicate clone controlfile from clone_cf;
sql clone 'alter database mount standby database'; -->原本Standby 開啟於nomount模式'，Rman將其轉變成mount 模式
}
executing script. Memory ScriptStarting restore at 02-4月 -08
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=/opt/ora9/oradata/master/control_sdby01.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
.....
- (Primary)重新啟動Primary，使用剛剛修改過的initprimary.ora
[oracle@baksrv dbs]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 4月 2 11:41:05 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile; -->做出新的spfileFile created.
SQL> startup;
ORACLE instance started.Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;-->switch log一次
System altered.
SQL> select * from v$log; -->目前的log sequence# 為148,最新的archived log為147GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 146 104857600 1 YES INACTIVE 2178807 02-4月 -08
2 1 147 104857600 1 YES ACTIVE 2178808 02-4月 -08
3 1 148 104857600 1 NO CURRENT 2179548 02-4月 -08
8 1 145 104857600 1 YES INACTIVE 2176247 02-4月 -08
- (Standby)檢查Standby 是否有接收到Archived Log
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
146 NO
145 NO
147 NO -->已經接收到147...Ｄata Guard已經大致上完成了^_^SQL> alter database recover managed standby database disconnect from session; -->開始做自動 standby recover
Database altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
146 YES
145 YES
147 YES -->原本NO的狀態，已經變成YES
- (Standby)加入Standby Redo Log..為了Switchover..建議Primary也加入
SQL> alter database recover managed standby database cancel;
SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo01.log' size 100M;
SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo02.log' size 100M;
SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo03.log' size 100M;SQL> alter database recover managed standby database disconnect from session;
- (Standby)檢查Standby
SQL> select process,status,client_process,sequence#,block# from v$managed_standby;
SQL> select * from v$standby_log; -->檢查Standby Redo log是否啟用。目前log sequence為150
GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------- ------------ ----------
4 1 150 104857600 0 YES ACTIVE 2180477 02-4月 -08 0
5 0 0 104857600 1024 YES UNASSIGNED 0 0
6 0 0 104857600 1024 YES UNASSIGNED 0 0SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/35489/viewspace-561364/ ，如需转载，请注明出处，否则将追究法律责任。
