oracle for linux雙機熱備實戰完全手冊

10.0.0.11是我主伺服器的ip，10.0.0.111是我從伺服器的ip, testdbase是資料庫的sid號，
作業系統的版本是RedHat Linux 8.0 資料庫版本是Oracle 9.2.0.1

1． 建立oracle使用者
[root@test2 etc]#groupadd oinstall
[root@test2 etc]#groupadd dba
[root@test2 etc]#useradd –g oinstall –G dba oracle

2． 設定oracle使用者環境變數 #主從伺服器的環境變數最好一致
以下是一個oracle使用者環境變數範本
[oracle@test2 oracle]$ more .bash_profile
export LD_ASSUME_KERNEL=2.4.1 #RedHat AS 3.0必須加此參數
export ORACLE_BASE=/opt/oracle #根據實際情況設定ORACLE_BASE
export ORACLE_HOME=/opt/oracle/product/9.2.0 #根據實際情況設定ORACLE_HOME
export ORACLE_SID=testdbase
export ORACLE_TERM=xterm
export NLS_LANG=american_america. ZHS16GBK; #繁體中文字元集是ZHT16BIG5
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib:$ORACLE_HOME/jdbc/lib/classes12.jar
export CLASSPATH
JAVA_HOME=/usr/java/j2sdk1.4.1_02 #根據實際情況設定JAVA_HOME的路徑
export JAVA_HOME
PATH=$PATH:$JAVA_HOME:$JAVA_HOME/bin:$JAVA_HOME/jre:$ORACLE_HOME/bin
export PATH
umask 022

3． 安裝java
4． 建立.bash_profile相關檔案夾
[root@test2 etc]#chown oracle.oinstall –Rf /opt #將/opt目錄的屬主改為oracle.oinstall
[root@test2 etc]# su – oracle
[oracle@test2 oracle]$ mkdir -p /opt/oracle/product/9.2.0/ocommon/nls/admin/data
[oracle@test2 oracle]$ mkdir -p /opt/oracle/product/9.2.0/lib
[oracle@test2 oracle]$ mkdir -p /opt/oracle/product/9.2.0/network/jlib
[oracle@test2 oracle]$ mkdir -p /opt/oracle/product/9.2.0/jdbc/lib/

5． 在待命伺服器上安裝oracle
待命伺服器在安裝的時候不需要建庫，只需安裝Software
如果安裝的是oracle9201版本，在安裝到84%的時候，會有一個編譯錯誤"Error in invoking target install of makefile /opt/oracle/product/9.2.0/ctx/lib/ins_ctx.mk"，此時開啟這個文
件：$ORACLE_HOME/ctx/lib/env_ctx.mk，把"$(LDLIBFLAG)dl"加到如下位置：
INSO_LINK = -L$(CTXLIB) $(LDLIBFLAG)m $(LDLIBFLAG)dl $(LDLIBFLAG)sc_ca
$(LDLIBFLAG)sc_fa $(LDLIBFLAG)sc_ex $(LDLIBFLAG)sc_da $(LDLIBFLAG)sc_ut
$(LDLIBFLAG)sc_ch $(LDLIBFLAG)sc_fi $(LLIBCTXHX) $(LDLIBFLAG)c
-Wl,-rpath,$(CTXHOME)lib $(CORELIBS)然後按重試，就可以繼續安裝下去了。。。
如果主伺服器的作業系統版本和目錄結構和待命伺服器一模一樣，並且主伺服器的ORACLE沒有建庫，可以直接將主伺服器的$ORACLE_BASE、$ ORACLE_HOME拷貝到待命伺服器
6． 查看主伺服器資料庫是否使用spfile #這一步可有可無
[oracle@cqcncdb oracle]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 11:54:42 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora

7． 在主伺服器上將主要資料庫的資料檔案、redo log、temp檔案拷貝到待命伺服器上（冷備份方式）
[oracle@cqcncdb oracle]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 12:03:58 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba;
Connected.
SQL> select * from v$dbfile;
FILE#----------NAME--------------------------------------------------------------------------------
10
/opt/oracle/oradata/testdbase/xdb01.dbf
9
/opt/oracle/oradata/testdbase/users01.dbf
2
/opt/oracle/oradata/testdbase/undotbs01.dbf
FILE#----------NAME--------------------------------------------------------------------------------
8
/opt/oracle/oradata/testdbase/tools01.dbf
1
/opt/oracle/oradata/testdbase/system01.dbf
7
/opt/oracle/oradata/testdbase/odm01.dbf

FILE#----------NAME--------------------------------------------------------------------------------
6
/opt/oracle/oradata/testdbase/indx01.dbf
5
/opt/oracle/oradata/testdbase/example01.dbf
4
/opt/oracle/oradata/testdbase/drsys01.dbf
FILE#----------NAME--------------------------------------------------------------------------------
3
/opt/oracle/oradata/testdbase/cwmlite01.dbf
11
/opt/oracle/oradata/testdbase/TONG.dbf
12
/opt/oracle/oradata/testdbase/SPAPP.dbf
FILE#----------NAME--------------------------------------------------------------------------------
13
/opt/oracle/oradata/testdbase/WTSPALL.dbf
14
/opt/oracle/oradata/testdbase/SZJLT.dbf
15
/opt/oracle/oradata/testdbase/JLTGAME.dbf
15 rows selected.
SQL> select * from v$logfile;
GROUP# STATUS TYPE---------- ------- -------MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/opt/oracle/oradata/testdbase/redo03.log
2 ONLINE
/opt/oracle/oradata/testdbase/redo02.log
1 ONLINE
/opt/oracle/oradata/testdbase/redo01.log

SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 0 2 1 ONLINE READ WRITE
225443840 27520 41943040 8192
/opt/oracle/oradata/testdbase/temp01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
將以上列出的檔案拷貝到待命伺服器相應的目錄下，或者直接將/opt/oracle/oradata/testdbase/目錄下所有的檔案直接拷貝到待命伺服器的/opt/oracle/oradata/testdbase/目錄下

8． 在主伺服器開啟主庫資料庫，修改為歸檔方式 （如果主要資料庫已經是歸檔方式，則不用修改了）
手工在主伺服器建立歸檔目錄
$cd $ORACLE_BASE #$ORACLE_BASE 所指向的目錄是/opt/oracle/，可參看第4步的ORACLE使用者環境變數的具體設定
$mkdir -p oradata/testdbase/archive
[oracle@cqcncdb testdbase]$ sqlplus /nolog
SQL> conn / as sysdba;
Connected.
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log start;
SQL> archive log list;
SQL> alter database open;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION= /opt/oracle/oradata/testdbase/archive';
SQL> alter system set log_archive_format='%t_%s.dbf' scope=spfile;
SQL> alter system set log_archive_start=true scope=spfile;
重新啟動資料庫，使修改結果生效
SQL> shutdown immediate;
察看歸檔模式
SQL> startup
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/testdbase/archive
Oldest online log sequence 565
Next log sequence to archive 567
Current log sequence 567

9． 在主庫上製作從庫control file
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/opt/oracle/product/9.2.0/dbs/control01.ctl';
Database altered.
將/opt/oracle/product/9.2.0/dbs/control01.ctl拷貝到待命伺服器的/opt/oracle/oradata/testdbase/目錄下，
10． 配置主庫和從庫的tnsnames.ora
將主庫的tnsnames.ora拷貝到從庫相應的目錄下
[oracle@test2 admin]$ vi tnsnames.ora #編輯從庫tnsnames.ora
我的主庫和從庫的tnsnames.ora如下：
# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdbase)
)
)

DBPRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdbase)
)
)

TESTDBASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdbase)
)
)
10.0.0.11是我主伺服器的ip，10.0.0.111是我從伺服器的ip, testdbase是資料庫的sid號，

11． 配置從庫的listener.ora
將主庫的listener.ora拷貝到從庫相應的目錄下
我的從庫的listener.ora檔案如下：
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.111)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = testdbase)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = testdbase)
)
)

主庫和從庫的listener.ora內容除了ip不同以外，別的都是一模一樣的

12． 啟動從庫的監聽
[oracle@test2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 21-JUL-2004 14:30:46

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.1.0 - Production
System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.111)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.1.0 - Production
Start Date 21-JUL-2004 14:30:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.111)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "testdbase" has 1 instance(s).
Instance "testdbase", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

13． 在主庫上建立pfile，因為9i預設使用spfile
sqlplus /nolog
SQL> conn / as sysdba;
Connected.
SQL> create pfile='/opt/inittestdbase.ora' from spfile;
14． 將主伺服器產生的/opt/inittestdbase.ora檔案拷貝到從庫的$ORACLE_HOME目錄

15． 修改從庫$ORACLE_HOME/dbs/inittestdbase.ora檔案
在從庫$ORACLE_HOME/dbs/inittestdbase.ora檔案的末尾添加以下參數
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.standby_archive_dest='/opt/oracle/oradata/testdbase/standbyarchive'
*.fal_server='DBPRIMARY'
*.fal_client='DBSTANDBY'
16． copy 主庫的orapwtestdbase到從庫相應的目錄

17． 啟動從庫，將從庫載入為standby database模式
[oracle@test2 standbyarchive]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 15:00:49 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=?/dbs/inittestdbase.ora
ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;

18． 在主伺服器上設定主庫到從庫的歸檔
SQL> alter system set log_archive_dest_2='SERVICE=dbstandby MANDATORY REOPEN=60';

19． 驗證主庫的日誌是否傳送從庫
在主庫上操作
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 572 104857600 1 NO CURRENT
128255951 21-JUL-04

2 1 571 104857600 1 YES ACTIVE
128255339 21-JUL-04

3 1 570 104857600 1 YES INACTIVE
128255209 21-JUL-04

SQL> alter system switch logfile;

System altered.
此時在從伺服器查看從庫的日誌
[oracle@test2 bdump]$ tail -f /opt/oracle/admin/testdbase/bdump/alert_testdbase.log
Completed: alter database recover managed standby database di
Wed Jul 21 15:36:39 2004
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_566.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_567.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_568.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_569.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_570.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_571.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_572.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_573.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_574.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_575.dbf
Media Recovery Log /opt/oracle/oradata/testdbase/standbyarchive/1_576.dbf
Media Recovery Waiting for thread 1 seq# 577

20． 採用Arch 進程傳送歸檔日誌的最大效能模式
在主要資料庫上操作
SQL> alter system set log_archive_dest_2='SERVICE=dbstandby REOPEN=300' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
SQL> alter system archive log current;
System altered.

21． 測試
在主要資料庫上操作
SQL> create user test3 identified by test;
User created.
SQL> grant connect,resource to test3;
Grant succeeded.
SQL> conn test3/test@dbprimary;
Connected.
SQL> create table test3(name varchar2(20));
Table created.
SQL> insert into test3 values('sadf');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.

以唯讀方式開啟從庫查看insert into test3 values('sadf')操作是否生效；
在從庫上操作
[oracle@test2 standbyarchive]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jul 21 16:11:29 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn test3/test
Connected.
SQL> select * from test3;
NAME
--------------------
sadf
SQL> conn / as sysdba
Connected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.