这两天看完物理STANDBY环境的建立，感觉建立过程还是比较容易的，简单记录一下操作步骤。

由于是第一次建立STANDBY环境，就不搞那么复杂了，主库和从库分别在两台服务器上，而且所有的路径完全一致。

STANDBY数据库一般都是通过主库的备份建立的。这里只介绍DATA GUARD的建立过程，详细的准备工作就不介绍了。比如：两个服务器Oracle数据库软件的安装，以及主库的建立等。

首先需要对主库进行设置，为建立STANDBY进行准备。需要将主库置于FORCE LOGGING状态，并处于归档模式下：

SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

DBID NAME LOG_MODE FOR
---------- --------- ------------ ---
2270762593 TESTDATA NOARCHIVELOG NO

ALTER DATABASE FORCE LOGGING;

Database altered.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/data/oradata/testdata/archive';

System altered.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;

System altered.

ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE SCOPE = SPFILE;

System altered.

SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1175525576 bytes
Fixed Size 452808 bytes
Variable Size 335544320 bytes
Database Buffers 838860800 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

ALTER DATABASE OPEN;

Database altered.

SELECT DBID, NAME, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

DBID NAME LOG_MODE FOR
---------- --------- ------------ ---
2270762593 TESTDATA ARCHIVELOG YES

下面进行STANDBY的建立，首先检查数据库中所有的数据文件和日志文件：

SELECT NAME FROM V$DATAFILE
2 UNION ALL
3 SELECT NAME FROM V$TEMPFILE
4 UNION ALL
5 SELECT MEMBER FROM V$LOGFILE;

NAME
------------------------------------------------------------
/data/oradata/testdata/system01.dbf
/data/oradata/testdata/undotbs01.dbf
/data/oradata/testdata/drsys01.dbf
/data/oradata/testdata/indx01.dbf
/data/oradata/testdata/tools01.dbf
/data/oradata/testdata/users01.dbf
/data/oradata/testdata/xdb01.dbf
.
.
.
/data/oradata/testdata/ndindex1.dbf
/data/oradata/testdata/ndmain01.dbf
/data/oradata/testdata/ndmain02.dbf
/data/oradata/testdata/temp01.dbf
/data/oradata/testdata/redo01.log
/data/oradata/testdata/redo02.log
/data/oradata/testdata/redo03.log

31 rows selected.

然后关闭数据库，将上面查询的所有数据文件和日志文件ftp到STANDBY数据库所在服务器的相同目录下：

SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST
[oracle@demo2 testdata]$ ftp 172.25.88.92
Connected to 172.25.88.92 (172.25.88.92).
220 (vsFTPd 1.1.3)
Name (172.25.88.92:root): oracle
331 Please specify the password.
Password:
230 Login successful. Have fun.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Switching to Binary mode.
ftp> prompt
Interactive mode off.
ftp> cd /data/oradata/testdata
250 Directory successfully changed.
ftp> mput *.dbf *.log
.
.
.
ftp> quit
221 Goodbye.
[oracle@demo2 testdata]$ exit
exit

如果停库时间有限，可以先使用cp拷贝至别的地方，打开数据库，然后再将cp的数据文件等ftp到目的服务器。

启动数据库后建立从库所需的PFILE和CONTROLFILE：

Total System Global Area 1175525576 bytes
Fixed Size 452808 bytes
Variable Size 335544320 bytes
Database Buffers 838860800 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/data/oradata/control01.ctl';

Database altered.

CREATE PFILE = '/data/oradata/inittestdata.ora' FROM SPFILE;

File created.

将上面的控制文件、初始化文件、lk文件和密码文件ftp到从库的指定位置：

host
[oracle@demo2 oracle]$ cd /data/oradata
[oracle@demo2 oradata]$ ftp 172.25.88.92
Connected to 172.25.88.92 (172.25.88.92).
220 (vsFTPd 1.1.3)
Name (172.25.88.92:root): oracle
331 Please specify the password.
Password:
230 Login successful. Have fun.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Switching to Binary mode.
ftp> prompt
Interactive mode off.
ftp> cd /data/oradata/testdata
250 Directory successfully changed.
ftp> put control01.ctl
local: control01.ctl remote: control01.ctl
227 Entering Passive Mode (172,25,88,92,51,183)
150 Ok to send data.
226 File receive OK.
1892352 bytes sent in 0.164 secs (1.1e+04 Kbytes/sec)
ftp> cd /opt/ora9/admin/testdata/pfile
250 Directory successfully changed.
ftp> put inittestdata.ora
local: inittestdata.ora remote: inittestdata.ora
227 Entering Passive Mode (172,25,88,92,25,11)
150 Ok to send data.
226 File receive OK.
1222 bytes sent in 2.2e-05 secs (5.4e+04 Kbytes/sec)
ftp> lcd /opt/ora9/product/9.2/dbs
Local directory now /opt/ora9/product/9.2/dbs
ftp> cd /opt/ora9/product/9.2/dbs
250 Directory successfully changed.
ftp> put orapwtestdata
local: orapwtestdata remote: orapwtestdata
227 Entering Passive Mode (172,25,88,92,155,149)
150 Ok to send data.
226 File receive OK.
1536 bytes sent in 5.2e-05 secs (2.9e+04 Kbytes/sec)
ftp> put lkTESTDATA
local: lkTESTDATA remote: lkTESTDATA
227 Entering Passive Mode (172,25,88,92,182,5)
150 Ok to send data.
226 File receive OK.
24 bytes sent in 1.9e-05 secs (1.2e+03 Kbytes/sec)
ftp> quit
221 Goodbye.
[oracle@demo2 oradata]$ exit
exit

登陆从库，将控制文件复制三份：

[oracle@testzone oracle]$ cd /data/oradata/testdata
[oracle@testzone testdata]$ cp control01.ctl control02.ctl
[oracle@testzone testdata]$ cp control01.ctl control03.ctl

建立主库归档日志的目的地：

[oracle@testzone testdata]$ mkdir standbyarchive

修改从库的初始化参数，需要修改或添加下面的记录：

standby_archive_dest='/data/oradata/testdata/standbyarchive'
standby_file_management=AUTO
remote_archive_enable=TRUE

修改从库中的$ORACLE_HOME/network/admin目录下的listener.ora文件，在SID_LIST中添加下列信息，并重启监听：

(SID_DESC =
(GLOBAL_DBNAME = testdata)
(ORACLE_HOME = /opt/ora9/product/9.2)
(SID_NAME = testdata)
)

[oracle@testzone admin]$ lsnrctl stop
[oracle@testzone admin]$ lsnrctl start

修改从库中的$ORACLE_HOME/network/admin目录下的sqlnet.ora文件，添加下列参数，用以检测死连接：

SQLNET.EXPIRE_TIME=2

修改主库和从库中的$ORACLE_HOME/network/admin目录下的tnsnames.ora文件，添加下面的配置：

TESTDATA_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.88.92)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdata)
)
)

[oracle@testzone oracle]$ export ORACLE_SID=testdata
[oracle@testzone oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on

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

Connected to an idle instance.

CREATE SPFILE FROM PFILE = '/opt/ora9/admin/testdata/pfile/inittestdata.ora';

File created.

STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1175525576 bytes
Fixed Size 452808 bytes
Variable Size 335544320 bytes
Database Buffers 838860800 bytes
Redo Buffers 667648 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

从库现在已经启动为应用归档模式，下面还需要设置一下主库参数：

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=TESTDATA_STANDBY';

System altered.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

System altered.

参数修改完毕，尝试归档当前的日志：

ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME
2 FROM V$ARCHIVED_LOG
3 WHERE FIRST_TIME > SYSDATE - 1
4 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIME NEXT_TIME
---------- ---------- ------------------- -------------------
1819 1 2007-02-27 04:47:56 2007-02-27 05:03:45
1820 1 2007-02-27 05:03:45 2007-02-27 09:00:02
1821 1 2007-02-27 09:00:02 2007-02-27 09:23:35
1822 1 2007-02-27 09:23:35 2007-02-27 18:25:33
1822 2 2007-02-27 09:23:35 2007-02-27 18:25:33

ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME
2 FROM V$ARCHIVED_LOG
3 WHERE FIRST_TIME > SYSDATE - 1
4 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIME NEXT_TIME
---------- ---------- ------------------- -------------------
1819 1 2007-02-27 04:47:56 2007-02-27 05:03:45
1820 1 2007-02-27 05:03:45 2007-02-27 09:00:02
1821 1 2007-02-27 09:00:02 2007-02-27 09:23:35
1822 1 2007-02-27 09:23:35 2007-02-27 18:25:33
1822 2 2007-02-27 09:23:35 2007-02-27 18:25:33
1823 1 2007-02-27 18:25:33 2007-02-27 18:51:19
1823 2 2007-02-27 18:25:33 2007-02-27 18:51:19

7 rows selected.

登陆从库检查：

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;